db-schema（数据库模式）

Database Schema Design

Design a Postgres database schema from product requirements. If target is provided, scope to that feature. Otherwise, ask the user what they're building.

Step 1: Identify Entities and Relationships

Read the requirements (PRD, user stories, or conversation). Extract:

• Nouns = candidate tables (users, projects, comments, invoices)
• Verbs = candidate relationships (creates, belongs to, has many)
• Adjectives/states = candidate columns or enums (active, draft, published)

Ask clarifying questions if the requirements are ambiguous. Don't guess at business logic.

Step 2: Design Tables

Follow these conventions on every table:

Postgres Type Recommendations

Step 3: Define Relationships

One-to-Many

Put the foreign key on the "many" side:

create table posts (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references users(id) on delete cascade not null,
  title text not null,
  created_at timestamptz default now() not null,
  updated_at timestamptz default now() not null
);

One-to-One

Same as one-to-many but add a unique constraint:

create table user_profiles (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references users(id) on delete cascade not null unique,
  bio text,
  avatar_url text,
  created_at timestamptz default now() not null,
  updated_at timestamptz default now() not null
);

Many-to-Many

Use a junction table. Name it <table_a>_<table_b> in alphabetical order, or use a domain name if clearer:

create table project_members (
  project_id uuid references projects(id) on delete cascade not null,
  user_id uuid references users(id) on delete cascade not null,
  role text not null default 'member' check (role in ('owner','admin','member','viewer')),
  joined_at timestamptz default now() not null,
  primary key (project_id, user_id)
);

Step 4: Add Indexes

Create indexes for:

Don't over-index. Each index costs write performance. Start with foreign keys and obvious query patterns. Add more based on actual query plans.

Step 5: Write the Migration

Put everything in a Supabase migration:

npx supabase migration new create_initial_schema

Structure the migration file:

-- 1. Create enums (if using Postgres enums)
-- 2. Create tables (parent tables first, then children)
-- 3. Create indexes
-- 4. Enable RLS on all tables
-- 5. Create RLS policies
-- 6. Create functions and triggers (e.g., updated_at trigger)

-- Updated_at trigger function (create once, reuse)
create or replace function update_updated_at()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

-- Example table
create table users (
  id uuid default gen_random_uuid() primary key,
  email text not null unique,
  full_name text,
  avatar_url text,
  created_at timestamptz default now() not null,
  updated_at timestamptz default now() not null
);

-- Apply updated_at trigger
create trigger set_updated_at
  before update on users
  for each row execute function update_updated_at();

-- Enable RLS
alter table users enable row level security;

-- Indexes
create index idx_users_email on users(email);

Step 6: Supabase-Specific Guidance

Auto-create profile trigger

create or replace function public.handle_new_user()
returns trigger as $$
begin
  insert into public.profiles (user_id, email, full_name, avatar_url)
  values (
    new.id,
    new.email,
    new.raw_user_meta_data->>'full_name',
    new.raw_user_meta_data->>'avatar_url'
  );
  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();

Schema Review Checklist