Supabase Basics

Supabase คือ Backend as a Service ที่ให้คุณสร้าง backend ได้เร็วโดยไม่ต้องเขียนโค้ดเยอะ

What is Supabase?

Core Features

Supabase คือ:
├─ PostgreSQL Database
├─ Authentication & Authorization
├─ Real-time Subscriptions
├─ Edge Functions
├─ File Storage
└─ Auto-generated APIs

เปรียบเสมือน:
├─ Firebase แต่ใช้ PostgreSQL
├─ Backend แบบ open source
├─ สามารถ self-host ได้
└─ มี features ครบครัน

Why Supabase for Thai Businesses?

✅ ข้อดี:
├─ Free tier ใช้งานได้จริง
├─ PostgreSQL แข็งแกร่ง
├─ Real-time สำหรับ chat applications
├─ Auto-generated APIs ช่วยลดเวลา
├─ Row Level Security ปลอดภัย
└─ ใช้งานง่ายกว่า Firebase

❌ ข้อเสีย:
├─ ยังใหม่กว่า competitors
├─ Community เล็กกว่า
└─ บาง features ยังไม่สมบูรณ์

Getting Started

1. Create Project

ไปที่ supabase.com
คลิก "Start your project"
ล็อกอินด้วย GitHub
สร้าง organization ใหม่

สร้าง project:

Project Name: shantilink-automation
Database Password: [strong password]
Region: Singapore (closest to Thailand)

2. Get Connection Details

ไปที่ Project Settings → Database:

Connection string:
postgresql://postgres:[password]@db.[project-id].supabase.co:5432/postgres

API URL:
https://[project-id].supabase.co

Anon Key:
eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

Service Role Key:
eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

3. Install Client Libraries

# JavaScript/TypeScript
npm install @supabase/supabase-js

# Python
pip install supabase

# CLI (for local development)
npm install -g supabase

Database Operations

Create Tables

-- Customers table for LINE users
CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  line_user_id VARCHAR(255) UNIQUE NOT NULL,
  display_name VARCHAR(255),
  picture_url TEXT,
  phone VARCHAR(20),
  email VARCHAR(255),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Orders table
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  order_id VARCHAR(50) UNIQUE NOT NULL,
  customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE,
  items JSONB NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  status VARCHAR(50) DEFAULT 'pending',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Appointments table
CREATE TABLE appointments (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE,
  appointment_date DATE NOT NULL,
  appointment_time TIME NOT NULL,
  service_type VARCHAR(100) NOT NULL,
  notes TEXT,
  status VARCHAR(50) DEFAULT 'scheduled',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for performance
CREATE INDEX idx_customers_line_user_id ON customers(line_user_id);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_appointments_customer_id ON appointments(customer_id);
CREATE INDEX idx_appointments_date ON appointments(appointment_date);

Enable Row Level Security (RLS)

-- Enable RLS on all tables
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE appointments ENABLE ROW LEVEL SECURITY;

-- Customers can only see their own data
CREATE POLICY "Users can view own profile" ON customers
  FOR SELECT USING (auth.uid()::text = line_user_id);

CREATE POLICY "Users can update own profile" ON customers
  FOR UPDATE USING (auth.uid()::text = line_user_id);

-- Users can only see their own orders
CREATE POLICY "Users can view own orders" ON orders
  FOR SELECT USING (
    auth.uid()::text IN (
      SELECT line_user_id FROM customers WHERE id = customer_id
    )
  );

-- Service role can do everything (for backend)
CREATE POLICY "Service role can manage all" ON customers
  FOR ALL USING (auth.role() = 'service_role');

CREATE POLICY "Service role can manage all orders" ON orders
  FOR ALL USING (auth.role() = 'service_role');

Authentication

// auth.js
import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.SUPABASE_URL,
  process.env.SUPABASE_ANON_KEY
);

async function signInWithLINE(userId, displayName, pictureUrl) {
  try {
    // Check if user exists
    const { data: existingUser } = await supabase
      .from('customers')
      .select('*')
      .eq('line_user_id', userId)
      .single();

    if (existingUser) {
      // Update existing user
      const { data, error } = await supabase
        .from('customers')
        .update({
          display_name: displayName,
          picture_url: pictureUrl,
          updated_at: new Date().toISOString()
        })
        .eq('line_user_id', userId)
        .select()
        .single();

      return { data, error };
    } else {
      // Create new user
      const { data, error } = await supabase
        .from('customers')
        .insert({
          line_user_id: userId,
          display_name: displayName,
          picture_url: pictureUrl
        })
        .select()
        .single();

      return { data, error };
    }
  } catch (error) {
    console.error('LINE auth error:', error);
    return { data: null, error };
  }
}

module.exports = { signInWithLINE };

Custom Auth for n8n

// n8n-auth.js
async function authenticateUser(userId) {
  const { data, error } = await supabase
    .from('customers')
    .select('*')
    .eq('line_user_id', userId)
    .single();

  if (error || !data) {
    return null;
  }

  return data;
}

// Use in n8n Function node
const userData = await authenticateUser($json.userId);
if (!userData) {
  return [{
    json: {
      error: 'User not found',
      replyToken: $json.replyToken,
      response: 'กรุณาลงทะเบียนก่อนใช้งานครับ'
    }
  }];
}

Real-time Features

// real-time.js
import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.SUPABASE_URL,
  process.env.SUPABASE_ANON_KEY
);

// Listen for new orders
const orderSubscription = supabase
  .channel('orders')
  .on('postgres_changes', 
    { 
      event: 'INSERT', 
      schema: 'public', 
      table: 'orders' 
    },
    (payload) => {
      console.log('New order:', payload.new);
      // Send notification to admin
      sendAdminNotification(payload.new);
    }
  )
  .subscribe();

// Listen for appointment updates
const appointmentSubscription = supabase
  .channel('appointments')
  .on('postgres_changes',
    {
      event: 'UPDATE',
      schema: 'public',
      table: 'appointments'
    },
    (payload) => {
      console.log('Appointment updated:', payload.new);
      // Send confirmation to customer
      sendAppointmentConfirmation(payload.new);
    }
  )
  .subscribe();

Real-time Dashboard

// dashboard.js
class RealtimeDashboard {
  constructor() {
    this.stats = {
      totalOrders: 0,
      pendingOrders: 0,
      todayAppointments: 0,
      newCustomers: 0
    };
    
    this.setupSubscriptions();
  }
  
  async setupSubscriptions() {
    // Orders
    supabase
      .channel('dashboard-orders')
      .on('postgres_changes',
        { event: '*', schema: 'public', table: 'orders' },
        () => this.updateOrderStats()
      )
      .subscribe();
      
    // Appointments
    supabase
      .channel('dashboard-appointments')
      .on('postgres_changes',
        { event: '*', schema: 'public', table: 'appointments' },
        () => this.updateAppointmentStats()
      )
      .subscribe();
      
    // Initial load
    await this.loadInitialStats();
  }
  
  async updateOrderStats() {
    const { data: orders } = await supabase
      .from('orders')
      .select('status, created_at');
      
    this.stats.totalOrders = orders.length;
    this.stats.pendingOrders = orders.filter(o => o.status === 'pending').length;
    
    this.render();
  }
  
  async updateAppointmentStats() {
    const today = new Date().toISOString().split('T')[0];
    const { data: appointments } = await supabase
      .from('appointments')
      .select('*')
      .eq('appointment_date', today);
      
    this.stats.todayAppointments = appointments.length;
    
    this.render();
  }
  
  render() {
    // Update UI with new stats
    document.getElementById('total-orders').textContent = this.stats.totalOrders;
    document.getElementById('pending-orders').textContent = this.stats.pendingOrders;
    document.getElementById('today-appointments').textContent = this.stats.todayAppointments;
  }
}

Edge Functions

Create Custom API

// supabase/functions/order-processing/index.ts
import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

const corsHeaders = {
  'Access-Control-Allow-Origin': '*',
  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
}

serve(async (req) => {
  if (req.method === 'OPTIONS') {
    return new Response('ok', { headers: corsHeaders })
  }

  try {
    const { orderId, action } = await req.json()
    
    const supabase = createClient(
      Deno.env.get('SUPABASE_URL') ?? '',
      Deno.env.get('SUPABASE_ANON_KEY') ?? ''
    )

    if (action === 'confirm') {
      const { data, error } = await supabase
        .from('orders')
        .update({ status: 'confirmed' })
        .eq('order_id', orderId)
        .select()
        .single()

      if (error) throw error

      // Trigger n8n workflow
      await fetch('https://your-n8n-instance.com/webhook/order-confirmed', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify(data)
      })

      return new Response(
        JSON.stringify({ success: true, order: data }),
        { headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
      )
    }

  } catch (error) {
    return new Response(
      JSON.stringify({ error: error.message }),
      { headers: { ...corsHeaders, 'Content-Type': 'application/json' }, status: 400 }
    )
  }
})

Deploy Edge Function

# Install Supabase CLI
npm install -g supabase

# Link to project
supabase link --project-ref your-project-id

# Deploy function
supabase functions deploy order-processing

Storage

File Upload

// storage.js
async function uploadCustomerFile(customerId, file) {
  const fileExt = file.name.split('.').pop();
  const fileName = `${customerId}/${Date.now()}.${fileExt}`;
  
  const { data, error } = await supabase.storage
    .from('customer-files')
    .upload(fileName, file);
    
  if (error) throw error;
  
  // Get public URL
  const { data: { publicUrl } } = supabase.storage
    .from('customer-files')
    .getPublicUrl(fileName);
    
  return publicUrl;
}

// Use in n8n Function node
const fileUrl = await uploadCustomerFile(customerId, $json.file);
return [{
  json: {
    ...$json,
    fileUrl: fileUrl
  }
}];

Best Practices

Database Design

-- Use appropriate data types
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name_th VARCHAR(255) NOT NULL,  -- Thai name
  name_en VARCHAR(255),           -- English name
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  stock INTEGER DEFAULT 0,
  category_id INTEGER REFERENCES categories(id),
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Use constraints
ALTER TABLE products ADD CONSTRAINT positive_price CHECK (price >= 0);
ALTER TABLE products ADD CONSTRAINT non_negative_stock CHECK (stock >= 0);

-- Use proper indexes
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_active ON products(is_active) WHERE is_active = true;

Security

// Always validate input
async function createOrder(orderData) {
  // Validate required fields
  if (!orderData.customerId || !orderData.items || orderData.items.length === 0) {
    throw new Error('Invalid order data');
  }
  
  // Validate amounts
  const totalAmount = orderData.items.reduce((sum, item) => {
    if (item.price < 0 || item.quantity <= 0) {
      throw new Error('Invalid item price or quantity');
    }
    return sum + (item.price * item.quantity);
  }, 0);
  
  // Use service role for backend operations
  const serviceClient = createClient(
    process.env.SUPABASE_URL,
    process.env.SUPABASE_SERVICE_KEY
  );
  
  const { data, error } = await serviceClient
    .from('orders')
    .insert({
      order_id: generateOrderId(),
      customer_id: orderData.customerId,
      items: orderData.items,
      total_amount: totalAmount
    })
    .select();
    
  return { data, error };
}

Performance

// Use database functions for complex queries
const { data } = await supabase
  .rpc('get_customer_order_summary', { customer_id: customerId });

// Use pagination for large datasets
const { data: orders } = await supabase
  .from('orders')
  .select('*')
  .range(0, 49)  // First 50 orders
  .order('created_at', { ascending: false });

// Use select specific columns
const { data: customers } = await supabase
  .from('customers')
  .select('id, display_name, created_at')
  .limit(100);

Common Issues

Connection Problems

✅ ตรวจสอบว่า project ไม่ได้ paused
✅ ตรวจสอสอบ connection string
✅ ตรวจสอบ network connectivity

RLS Issues

✅ ตรวจสอบว่าเปิด RLS แล้ว
✅ ตรวจสอบ policies ที่สร้าง
✅ ใช้ service role สำหรับ backend operations

Performance Issues

✅ สร้าง indexes ให้เหมาะสม
✅ ใช้ pagination
✅ เลือกเฉพาะ columns ที่ต้องการ

Next Steps

Vercel Deployment - Deploy frontend
APIs & Webhooks - เชื่อมต่อระบบ
Client Examples - ดูตัวอย่างจริง

ต้องการความช่วยเหลือ? ติดต่อเราได้ที่ ShantiLink.com 💬

Cloud Services Vercel Deployment

What is Supabase?

Core Features

Why Supabase for Thai Businesses?

Getting Started

1. Create Project

2. Get Connection Details

3. Install Client Libraries

Database Operations

Create Tables

Enable Row Level Security (RLS)

Authentication

LINE Login Integration

Custom Auth for n8n

Real-time Features

Subscribe to Data Changes

Real-time Dashboard

Edge Functions

Create Custom API

Deploy Edge Function

Storage

File Upload

Best Practices

Database Design

Security

Performance

Common Issues

Connection Problems

RLS Issues

Performance Issues

Next Steps