Table Design¶

Thiết kế Table trong Supabase¶

Nguyên tắc cơ bản¶

┌─────────────────────────────────────────────────────────────┐
│                    TABLE DESIGN PRINCIPLES                   │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  1. UUID cho Primary Keys                                   │
│     - Không đoán được                                       │
│     - Distributed-friendly                                  │
│     - Không cần sequence                                    │
│                                                              │
│  2. Timestamps                                               │
│     - created_at: Khi tạo record                           │
│     - updated_at: Khi sửa record                           │
│                                                              │
│  3. Soft Delete                                              │
│     - deleted_at thay vì xóa thật                          │
│     - Dễ recover                                            │
│                                                              │
│  4. Foreign Keys                                             │
│     - Đảm bảo data integrity                               │
│     - ON DELETE CASCADE/SET NULL                           │
│                                                              │
└─────────────────────────────────────────────────────────────┘

Cấu trúc Table chuẩn¶

Base Table Template¶

-- Template cho mọi table
CREATE TABLE public.table_name (
  -- Primary Key
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Business columns
  -- ... your columns here ...

  -- Foreign Keys
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  deleted_at TIMESTAMPTZ  -- Soft delete
);

-- Auto-update updated_at
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON public.table_name
  FOR EACH ROW
  EXECUTE FUNCTION extensions.moddatetime(updated_at);

-- Enable RLS
ALTER TABLE public.table_name ENABLE ROW LEVEL SECURITY;

Ví dụ: TaskFlow Schema¶

Users Profile Table¶

-- Extend auth.users with profile data
CREATE TABLE public.profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT NOT NULL,
  full_name TEXT,
  avatar_url TEXT,
  role TEXT DEFAULT 'member' CHECK (role IN ('admin', 'member')),

  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

-- Auto-create profile on signup
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, email, full_name)
  VALUES (
    NEW.id,
    NEW.email,
    NEW.raw_user_meta_data->>'full_name'
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION public.handle_new_user();

Projects Table¶

CREATE TABLE public.projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Business data
  name TEXT NOT NULL,
  description TEXT,
  status TEXT DEFAULT 'active' CHECK (status IN ('active', 'archived', 'deleted')),

  -- Owner
  owner_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

  -- Settings (JSONB for flexibility)
  settings JSONB DEFAULT '{}'::JSONB,

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  deleted_at TIMESTAMPTZ
);

-- Indexes
CREATE INDEX idx_projects_owner ON public.projects(owner_id);
CREATE INDEX idx_projects_status ON public.projects(status);

Tasks Table¶

CREATE TABLE public.tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Relations
  project_id UUID NOT NULL REFERENCES public.projects(id) ON DELETE CASCADE,
  assignee_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  parent_id UUID REFERENCES public.tasks(id) ON DELETE CASCADE, -- Subtasks

  -- Task data
  title TEXT NOT NULL,
  description TEXT,
  status TEXT DEFAULT 'todo' CHECK (status IN ('todo', 'in_progress', 'review', 'done')),
  priority INTEGER DEFAULT 0 CHECK (priority >= 0 AND priority <= 3),

  -- Dates
  due_date DATE,
  completed_at TIMESTAMPTZ,

  -- Ordering
  position INTEGER DEFAULT 0,

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

-- Indexes
CREATE INDEX idx_tasks_project ON public.tasks(project_id);
CREATE INDEX idx_tasks_assignee ON public.tasks(assignee_id);
CREATE INDEX idx_tasks_status ON public.tasks(status);
CREATE INDEX idx_tasks_due_date ON public.tasks(due_date);

Data Types Best Practices¶

Choosing Data Types¶

Use Case	Recommended Type	Avoid
ID	UUID	SERIAL
Text (short)	TEXT	VARCHAR(n)
Text (long)	TEXT	VARCHAR
Numbers	INTEGER, BIGINT	SERIAL for non-PK
Decimal	NUMERIC(p,s)	FLOAT
Boolean	BOOLEAN	INTEGER 0/1
Date	DATE	TEXT
Timestamp	TIMESTAMPTZ	TIMESTAMP
JSON	JSONB	JSON, TEXT
Enum-like	TEXT + CHECK	ENUM type

Why TEXT over VARCHAR?¶

-- ❌ AVOID: VARCHAR limit
name VARCHAR(100)  -- What if name > 100 chars?

-- ✅ PREFER: TEXT với CHECK nếu cần
name TEXT CHECK (length(name) <= 100)

-- Lý do:
-- - PostgreSQL xử lý TEXT và VARCHAR tương đương
-- - TEXT linh hoạt hơn
-- - CHECK constraint rõ ràng hơn

JSONB Column Patterns¶

Khi nào dùng JSONB?¶

-- ✅ GOOD: Flexible settings
settings JSONB DEFAULT '{
  "notifications": true,
  "theme": "light",
  "language": "vi"
}'::JSONB

-- ✅ GOOD: Metadata không cần query
metadata JSONB

-- ❌ BAD: Data cần query thường xuyên
-- Nên tách thành columns riêng

Query JSONB¶

-- Access JSONB fields
SELECT
  id,
  settings->>'theme' as theme,
  settings->'notifications' as notifications_enabled
FROM projects
WHERE settings->>'language' = 'vi';

-- Index JSONB
CREATE INDEX idx_projects_settings_theme
  ON projects ((settings->>'theme'));

CHECK Constraints¶

Validate Data at DB Level¶

-- Status enum-like
status TEXT CHECK (status IN ('draft', 'published', 'archived'))

-- Range validation
priority INTEGER CHECK (priority >= 0 AND priority <= 5)

-- Email format (basic)
email TEXT CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')

-- Non-empty string
title TEXT NOT NULL CHECK (length(trim(title)) > 0)

-- Positive number
amount NUMERIC CHECK (amount > 0)

-- Future date only
due_date DATE CHECK (due_date >= CURRENT_DATE)

Indexes Strategy¶

Khi nào cần Index?¶

-- 1. Foreign Keys (thường xuyên JOIN)
CREATE INDEX idx_tasks_project ON tasks(project_id);

-- 2. Columns trong WHERE clause
CREATE INDEX idx_tasks_status ON tasks(status);

-- 3. Columns trong ORDER BY
CREATE INDEX idx_tasks_created ON tasks(created_at DESC);

-- 4. Composite index cho query pattern cụ thể
CREATE INDEX idx_tasks_project_status
  ON tasks(project_id, status);

-- 5. Partial index cho query có filter
CREATE INDEX idx_active_tasks
  ON tasks(project_id)
  WHERE status != 'done';

Kiểm tra Query Performance¶

-- Explain query plan
EXPLAIN ANALYZE
SELECT * FROM tasks
WHERE project_id = 'uuid-here'
  AND status = 'todo'
ORDER BY created_at DESC;

Tổng kết¶

Table Design Checklist¶

✅ UUID Primary Key với gen_random_uuid()
✅ created_at và updated_at timestamps
✅ Foreign Keys với ON DELETE behavior
✅ Enable RLS immediately
✅ Indexes cho columns thường query
✅ CHECK constraints cho validation
✅ JSONB cho flexible data
✅ Soft delete nếu cần recovery

Naming Conventions¶

Tables:      plural, snake_case     (tasks, user_profiles)
Columns:     singular, snake_case   (created_at, project_id)
Indexes:     idx_table_column       (idx_tasks_status)
Constraints: pk/fk/chk_table_desc   (fk_tasks_project)

Q&A¶

Có table nào trong dự án cần redesign?
Đang dùng SERIAL hay UUID?
Có dùng soft delete không?