Phase 2: Database Design + REST API

Mục tiêu

Thiết kế database schema và sử dụng auto-generated REST API từ Supabase.

Thời gian ước tính: 4 giờ

---

Step 1: Create Database Migrations

1.1 Migration: Profiles

supabase migration new create_profiles

-- supabase/migrations/20240101000001_create_profiles.sql

-- Profiles table (extends auth.users)
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,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

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

-- Policies
CREATE POLICY "Users can view all profiles"
  ON profiles FOR SELECT
  USING (true);

CREATE POLICY "Users can update own profile"
  ON profiles FOR UPDATE
  USING (auth.uid() = id)
  WITH CHECK (auth.uid() = id);

-- Trigger: 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();

-- Trigger: Update timestamp
CREATE TRIGGER set_profiles_updated_at
  BEFORE UPDATE ON profiles
  FOR EACH ROW
  EXECUTE FUNCTION extensions.moddatetime(updated_at);

1.2 Migration: Organizations (Workspaces)

supabase migration new create_organizations

-- supabase/migrations/20240101000002_create_organizations.sql

-- Organizations table
CREATE TABLE public.organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  logo_url TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Organization members
CREATE TABLE public.organization_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member')),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(organization_id, user_id)
);

-- Indexes
CREATE INDEX idx_org_members_org ON organization_members(organization_id);
CREATE INDEX idx_org_members_user ON organization_members(user_id);
CREATE INDEX idx_org_slug ON organizations(slug);

-- Enable RLS
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE organization_members ENABLE ROW LEVEL SECURITY;

-- Helper function: Check if user is org member
CREATE OR REPLACE FUNCTION is_org_member(org_id UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM organization_members
    WHERE organization_id = org_id
    AND user_id = auth.uid()
  );
$$ LANGUAGE sql STABLE SECURITY DEFINER;

-- Helper function: Check if user is org admin
CREATE OR REPLACE FUNCTION is_org_admin(org_id UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM organization_members
    WHERE organization_id = org_id
    AND user_id = auth.uid()
    AND role IN ('owner', 'admin')
  );
$$ LANGUAGE sql STABLE SECURITY DEFINER;

-- Policies: Organizations
CREATE POLICY "Members can view org"
  ON organizations FOR SELECT
  USING (is_org_member(id));

CREATE POLICY "Admins can update org"
  ON organizations FOR UPDATE
  USING (is_org_admin(id))
  WITH CHECK (is_org_admin(id));

-- Policies: Organization Members
CREATE POLICY "Members can view other members"
  ON organization_members FOR SELECT
  USING (is_org_member(organization_id));

CREATE POLICY "Admins can manage members"
  ON organization_members FOR ALL
  USING (is_org_admin(organization_id))
  WITH CHECK (is_org_admin(organization_id));

-- Trigger: Update timestamp
CREATE TRIGGER set_orgs_updated_at
  BEFORE UPDATE ON organizations
  FOR EACH ROW
  EXECUTE FUNCTION extensions.moddatetime(updated_at);

1.3 Migration: Projects

supabase migration new create_projects

-- supabase/migrations/20240101000003_create_projects.sql

-- Projects table
CREATE TABLE public.projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  status TEXT DEFAULT 'active' CHECK (status IN ('active', 'archived')),
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_projects_org ON projects(organization_id);
CREATE INDEX idx_projects_status ON projects(status);

-- Enable RLS
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Policies
CREATE POLICY "Org members can view projects"
  ON projects FOR SELECT
  USING (is_org_member(organization_id));

CREATE POLICY "Org members can create projects"
  ON projects FOR INSERT
  WITH CHECK (is_org_member(organization_id) AND auth.uid() = created_by);

CREATE POLICY "Org admins can update projects"
  ON projects FOR UPDATE
  USING (is_org_admin(organization_id))
  WITH CHECK (is_org_admin(organization_id));

CREATE POLICY "Org admins can delete projects"
  ON projects FOR DELETE
  USING (is_org_admin(organization_id));

-- Trigger
CREATE TRIGGER set_projects_updated_at
  BEFORE UPDATE ON projects
  FOR EACH ROW
  EXECUTE FUNCTION extensions.moddatetime(updated_at);

1.4 Migration: Tasks

supabase migration new create_tasks

-- supabase/migrations/20240101000004_create_tasks.sql

-- Tasks table
CREATE TABLE public.tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  description TEXT,
  status TEXT DEFAULT 'todo' CHECK (status IN ('todo', 'in_progress', 'review', 'done')),
  priority TEXT DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high', 'urgent')),
  assignee_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  due_date DATE,
  position INTEGER DEFAULT 0,
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Task comments
CREATE TABLE public.task_comments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id UUID REFERENCES tasks(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Task attachments
CREATE TABLE public.task_attachments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id UUID REFERENCES tasks(id) ON DELETE CASCADE,
  file_name TEXT NOT NULL,
  file_path TEXT NOT NULL,
  file_size INTEGER,
  mime_type TEXT,
  uploaded_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_tasks_project ON tasks(project_id);
CREATE INDEX idx_tasks_status ON tasks(status);
CREATE INDEX idx_tasks_assignee ON tasks(assignee_id);
CREATE INDEX idx_tasks_due_date ON tasks(due_date);
CREATE INDEX idx_task_comments_task ON task_comments(task_id);
CREATE INDEX idx_task_attachments_task ON task_attachments(task_id);

-- Enable RLS
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE task_comments ENABLE ROW LEVEL SECURITY;
ALTER TABLE task_attachments ENABLE ROW LEVEL SECURITY;

-- Helper function: Check project access
CREATE OR REPLACE FUNCTION has_project_access(p_id UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM projects p
    WHERE p.id = p_id
    AND is_org_member(p.organization_id)
  );
$$ LANGUAGE sql STABLE SECURITY DEFINER;

-- Policies: Tasks
CREATE POLICY "Project members can view tasks"
  ON tasks FOR SELECT
  USING (has_project_access(project_id));

CREATE POLICY "Project members can create tasks"
  ON tasks FOR INSERT
  WITH CHECK (has_project_access(project_id) AND auth.uid() = created_by);

CREATE POLICY "Project members can update tasks"
  ON tasks FOR UPDATE
  USING (has_project_access(project_id))
  WITH CHECK (has_project_access(project_id));

CREATE POLICY "Project members can delete own tasks"
  ON tasks FOR DELETE
  USING (has_project_access(project_id) AND auth.uid() = created_by);

-- Policies: Comments
CREATE POLICY "Project members can view comments"
  ON task_comments FOR SELECT
  USING (has_project_access((SELECT project_id FROM tasks WHERE id = task_id)));

CREATE POLICY "Project members can create comments"
  ON task_comments FOR INSERT
  WITH CHECK (
    has_project_access((SELECT project_id FROM tasks WHERE id = task_id))
    AND auth.uid() = user_id
  );

CREATE POLICY "Users can update own comments"
  ON task_comments FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can delete own comments"
  ON task_comments FOR DELETE
  USING (auth.uid() = user_id);

-- Policies: Attachments
CREATE POLICY "Project members can view attachments"
  ON task_attachments FOR SELECT
  USING (has_project_access((SELECT project_id FROM tasks WHERE id = task_id)));

CREATE POLICY "Project members can add attachments"
  ON task_attachments FOR INSERT
  WITH CHECK (
    has_project_access((SELECT project_id FROM tasks WHERE id = task_id))
    AND auth.uid() = uploaded_by
  );

-- Triggers
CREATE TRIGGER set_tasks_updated_at
  BEFORE UPDATE ON tasks
  FOR EACH ROW
  EXECUTE FUNCTION extensions.moddatetime(updated_at);

CREATE TRIGGER set_comments_updated_at
  BEFORE UPDATE ON task_comments
  FOR EACH ROW
  EXECUTE FUNCTION extensions.moddatetime(updated_at);

1.5 Apply migrations locally

# Reset local database and apply all migrations
supabase db reset

# Check migration status
supabase migration list

---

Step 2: Generate TypeScript Types

2.1 Generate types

# Generate from local database
supabase gen types typescript --local > src/types/supabase.ts

# Or from remote
supabase gen types typescript --project-ref YOUR_PROJECT_REF > src/types/supabase.ts

2.2 Create typed client

// src/lib/supabase/client.ts
import { createBrowserClient } from '@supabase/ssr';
import { Database } from '@/types/supabase';

export function createClient() {
  return createBrowserClient<Database>(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  );
}

// src/lib/supabase/server.ts
import { createServerClient, type CookieOptions } from '@supabase/ssr';
import { cookies } from 'next/headers';
import { Database } from '@/types/supabase';

export async function createClient() {
  const cookieStore = await cookies();

  return createServerClient<Database>(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        getAll() {
          return cookieStore.getAll();
        },
        setAll(cookiesToSet) {
          try {
            cookiesToSet.forEach(({ name, value, options }) =>
              cookieStore.set(name, value, options)
            );
          } catch {
            // Server Component
          }
        },
      },
    }
  );
}

---

Step 3: Test REST API

3.1 Create seed data

-- supabase/seed.sql

-- Create test user (run after signup flow is ready)
-- Or manually create via Supabase Dashboard → Authentication

-- Create test organization
INSERT INTO organizations (id, name, slug) VALUES
  ('00000000-0000-0000-0000-000000000001', 'Test Organization', 'test-org');

-- Create test project
INSERT INTO projects (id, organization_id, name, description) VALUES
  ('00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000001', 'Test Project', 'A test project');

-- Note: organization_members will be created after user signup

3.2 Test queries in Supabase Studio

-- Test SELECT
SELECT * FROM tasks WHERE project_id = 'xxx';

-- Test INSERT
INSERT INTO tasks (project_id, title, status, priority, created_by)
VALUES ('xxx', 'Test Task', 'todo', 'medium', 'user-id')
RETURNING *;

-- Test UPDATE
UPDATE tasks SET status = 'in_progress' WHERE id = 'xxx'
RETURNING *;

-- Test DELETE
DELETE FROM tasks WHERE id = 'xxx';

3.3 Test from Next.js

// src/app/test-api/page.tsx
import { createClient } from '@/lib/supabase/server';

export default async function TestAPIPage() {
  const supabase = await createClient();

  // Test projects query
  const { data: projects, error: projectsError } = await supabase
    .from('projects')
    .select(`
      id,
      name,
      description,
      organization:organizations(name),
      tasks(count)
    `)
    .eq('status', 'active');

  // Test tasks query
  const { data: tasks, error: tasksError } = await supabase
    .from('tasks')
    .select(`
      id,
      title,
      status,
      priority,
      due_date,
      assignee:profiles!assignee_id(full_name, avatar_url),
      project:projects(name)
    `)
    .order('created_at', { ascending: false })
    .limit(10);

  return (
    <div className="p-8">
      <h1 className="text-2xl font-bold mb-4">REST API Test</h1>

      <section className="mb-8">
        <h2 className="text-xl font-semibold mb-2">Projects</h2>
        {projectsError ? (
          <p className="text-red-600">{projectsError.message}</p>
        ) : (
          <pre className="bg-gray-100 p-4 rounded overflow-auto">
            {JSON.stringify(projects, null, 2)}
          </pre>
        )}
      </section>

      <section>
        <h2 className="text-xl font-semibold mb-2">Tasks</h2>
        {tasksError ? (
          <p className="text-red-600">{tasksError.message}</p>
        ) : (
          <pre className="bg-gray-100 p-4 rounded overflow-auto">
            {JSON.stringify(tasks, null, 2)}
          </pre>
        )}
      </section>
    </div>
  );
}

---

Step 4: Create Helper Types

// src/types/index.ts
import { Database } from './supabase';

export type Tables = Database['public']['Tables'];

// Row types
export type Profile = Tables['profiles']['Row'];
export type Organization = Tables['organizations']['Row'];
export type OrganizationMember = Tables['organization_members']['Row'];
export type Project = Tables['projects']['Row'];
export type Task = Tables['tasks']['Row'];
export type TaskComment = Tables['task_comments']['Row'];
export type TaskAttachment = Tables['task_attachments']['Row'];

// Insert types
export type ProfileInsert = Tables['profiles']['Insert'];
export type TaskInsert = Tables['tasks']['Insert'];
export type TaskCommentInsert = Tables['task_comments']['Insert'];

// Update types
export type TaskUpdate = Tables['tasks']['Update'];
export type ProfileUpdate = Tables['profiles']['Update'];

// Custom types for queries with relations
export type TaskWithRelations = Task & {
  assignee: Pick<Profile, 'id' | 'full_name' | 'avatar_url'> | null;
  project: Pick<Project, 'id' | 'name'>;
  comments?: TaskComment[];
};

export type ProjectWithStats = Project & {
  organization: Pick<Organization, 'id' | 'name'>;
  task_count: number;
};

---

Step 5: Push to Remote

5.1 Push migrations to staging

# Push all pending migrations
supabase db push

# Verify
supabase migration list

5.2 Verify in Supabase Dashboard

1. Go to Table Editor - check all tables exist
2. Go to Authentication → Policies - verify RLS policies
3. Go to API Docs - see auto-generated documentation

---

Verification Checklist

• [ ] All migrations created and applied locally
• [ ] RLS policies working (test with different users)
• [ ] TypeScript types generated
• [ ] REST API queries working from Next.js
• [ ] Migrations pushed to staging
• [ ] Data relationships working (foreign keys, joins)

---

Common Issues

Issue: RLS blocking all queries

Error: new row violates row-level security policy

Solution: Check that user is properly authenticated and is a member of the organization.

Issue: Type mismatch

Type 'string' is not assignable to type 'UUID'

Solution: Regenerate types with supabase gen types typescript --local

Issue: Foreign key constraint

Error: insert or update on table "tasks" violates foreign key constraint

Solution: Ensure referenced records exist (project, user, etc.)

---

Next Phase

Chuyển sang Phase 3: Authentication để implement authentication flow.