Database Schema Extension

This skill guides you through extending the database schema using declarative schema files, Supabase migrations, and automatic type generation.

Quick Reference

Key Commands:

• bun db:diff <migration_name>

  - Generate migration from schema changes

• bun migrate:up

  - Apply migrations to local database

• bun gen:types

  - Regenerate TypeScript types and Zod schemas

• bun db:reset

  - Reset database (destructive!)

Schema File Organization:

supabase/schemas/
├── 00-extensions.sql    # PostgreSQL extensions
├── 01-schema.sql        # Tables, enums, indexes
├── 02-policies.sql      # Row Level Security policies
└── 03-functions.sql     # Database functions and triggers

Complete Workflow

Step 1: Modify Declarative Schema Files

The project uses declarative schema files in

supabase/schemas/

. These files define the desired state of your database, and Supabase CLI generates migrations by comparing them with your local database.

Step 2: Follow SQL Style Guidelines

Naming Conventions:

• Tables:

  snake_case

  , plural (e.g.,

  todos

  ,

  user_profiles

  )
• Columns:

  snake_case

  , singular (e.g.,

  user_id

  ,

  created_at

  )
• Enums:

  snake_case

  , singular (e.g.,

  priority_level

  ,

  user_role

  ). Always prefer enums over text for fixed sets.
• Foreign keys:

  {singular_table_name}_id

  (e.g.,

  user_id

  references

  users

  )
• Indexes:

  {table}_{column}_idx

  (e.g.,

  todos_user_id_idx

  )
• Policies: Descriptive text in quotes (e.g.,

  "Users can view their own todos"

  ). Keep them short and clear.

SQL Standards:

• All SQL keywords in lowercase (e.g.,

  create table

  ,

  select

  ,

  where

  )
• Always use

  public

  schema prefix (e.g.,

  public.todos

  )
• Add table comments:

  comment on table public.todos is 'User todo items'

• Add column comments for enums:

  comment on column public.todos.priority is 'Priority level: low, medium, or high'

• Always prefer enums over text for fixed sets.
• Use

  timestamptz

  for timestamps (includes timezone)
• Default timestamps:

  created_at timestamptz default now() not null

• Use

  uuid

  for primary keys:

  id uuid default gen_random_uuid() primary key

Example Table Creation:

-- Priority enum type
create type public.priority_level as enum ('low', 'medium', 'high');

-- Todos table
create table public.todos (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references public.profiles(id) on delete cascade not null,
  title text not null,
  description text,
  completed boolean default false not null,
  priority public.priority_level,
  due_date timestamptz,
  created_at timestamptz default now() not null,
  updated_at timestamptz default now() not null
);

-- Indexes for performance
create index todos_user_id_idx on public.todos(user_id);
create index todos_completed_idx on public.todos(completed);
create index todos_due_date_idx on public.todos(due_date);

-- Comments for documentation
comment on table public.todos is 'User todo items';
comment on column public.todos.priority is 'Priority level: low, medium, or high';

Step 3: Add Row Level Security (RLS)

Critical RLS Rules:

1. Always enable RLS on new tables (even public tables)
2. Create separate policies for each operation (select, insert, update, delete)
3. Specify roles explicitly using

   to authenticated

   or

   to anon

4. Add indexes on columns used in policies (usually

   user_id

   )

Policy Structure:

-- Enable RLS
alter table public.todos enable row level security;

-- SELECT policy
create policy "Users can view their own todos"
  on public.todos for select
  to authenticated
  using (auth.uid() = user_id);

-- INSERT policy
create policy "Users can create their own todos"
  on public.todos for insert
  to authenticated
  with check (auth.uid() = user_id);

-- UPDATE policy
create policy "Users can update their own todos"
  on public.todos for update
  to authenticated
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

-- DELETE policy
create policy "Users can delete their own todos"
  on public.todos for delete
  to authenticated
  using (auth.uid() = user_id);

Key Policy Guidelines:

• SELECT policies: Use

  using

  only (not

  with check

  )
• INSERT policies: Use

  with check

  only (not

  using

  )
• UPDATE policies: Use both

  using

  and

  with check

• DELETE policies: Use

  using

  only (not

  with check

  )
• Never use

  FOR ALL

  : Always separate into individual policies
• Avoid joins: Rewrite policies to use

  IN

  or

  ANY

  instead

Public Access Example:

-- Public read access
create policy "Avatar media is viewable by everyone"
  on public.media for select
  to authenticated, anon
  using (media_type = 'avatar');

Step 4: Add SQL Functions and Triggers

SQL Function Best Practices:

1. Default to

   security invoker

   (run with caller's permissions)
2. Set

   search_path = ''

   and use fully qualified names
3. Use explicit typing for parameters and return values
4. Declare as

   immutable

   or

   stable

   when possible for optimization

Common Pattern:

updated_at

Trigger

-- Reuse existing function for updated_at
create trigger my_table_updated_at
  before update on public.my_table
  for each row
  execute function public.handle_updated_at();

The project already has

public.handle_updated_at()

function - just create the trigger!

Custom Function Example:

-- RPC function example
create or replace function public.get_user_stats(user_uuid uuid)
returns table (
  user_id uuid,
  total_items bigint,
  completed_items bigint
) as $$
begin
  return query
  select
    user_uuid as user_id,
    count(*) as total_items,
    count(*) filter (where completed = true) as completed_items
  from public.todos
  where user_id = user_uuid;
end;
$$ language plpgsql security invoker set search_path = '';

Step 5: Generate Migration

After modifying schema files, generate a migration:

bun db:diff add_bookings_table

What This Does:

1. Compares

   supabase/schemas/*.sql

   with your local database
2. Generates SQL migration in

   supabase/migrations/YYYYMMDDHHMMSS_add_bookings_table.sql

3. Shows you the diff for review

Important: Always review the generated migration SQL before applying!

Step 6: Review and Apply Migration

1. Review the migration file in

   supabase/migrations/

2. Check for:

   • Destructive operations (drop, truncate, alter column types)
   • Missing RLS policies
   • Correct foreign key relationships
   • Proper indexes

3. Apply the migration:

bun migrate:up

Step 7: Regenerate Types

Critical Final Step: Always regenerate types after schema changes!

bun gen:types

What This Does:

1. Runs

   bun db:types

   - Generates TypeScript types from database
2. Runs

   bun db:types:zod

   - Generates Zod schemas from TypeScript types
3. Runs

   bun remove:public:prefix

   - Cleans up schema names

Generated Files:

• types/database.types.ts

  - TypeScript types for all tables, enums, functions

• schemas/database.schema.ts

  - Zod schemas for validation

Usage in Code:

// Import types
import type { Database } from "@/types/database.types";

// Use table types
type Booking = Database["public"]["Tables"]["bookings"]["Row"];
type BookingInsert = Database["public"]["Tables"]["bookings"]["Insert"];
type BookingUpdate = Database["public"]["Tables"]["bookings"]["Update"];

// Import Zod schemas
import { bookingsInsertSchema } from "@/schemas/database.schema";

// Use in server actions
const { data: validatedData, success } = bookingsInsertSchema.safeParse(input);

Troubleshooting

Migration Conflicts

If

bun db:diff

shows unexpected changes:

1. Check if local database is out of sync:

   bun db:reset  # Resets local DB to match migrations + seed data
   

2. Check if you have unapplied migrations:

   bun migrate:up
   

Type Generation Fails

If

bun gen:types

fails:

1. Ensure local database is running:

   bun db:start
   

2. Check for SQL syntax errors in schema files

3. Verify all migrations are applied:

   bun migrate:up
   

Policy Not Working

Common issues:

1. RLS not enabled:

   alter table public.my_table enable row level security;

2. Missing role specification: Add

   to authenticated

   or

   to anon

3. Missing index: Add index on

   user_id

   or columns used in policy
4. Function not wrapped in select: Use

   (select auth.uid())

   not

   auth.uid()

Workflow Checklist

When extending the database schema, follow this checklist:

• Modify appropriate schema file (

  00-extensions.sql

  ,

  01-schema.sql

  ,

  02-policies.sql

  , or

  03-functions.sql

  )
• Follow SQL style guide (lowercase, snake_case, schema prefix)
• Add table and column comments
• Create indexes for foreign keys and frequently queried columns
• Enable RLS on new tables
• Create separate policies for select/insert/update/delete
• Add

  updated_at

  trigger if table has

  updated_at

  column
• Run

  bun db:diff <migration_name>

  to generate migration
• Review generated migration SQL
• Run

  bun migrate:up

  to apply migration
• Run

  bun gen:types

  to regenerate TypeScript/Zod types
• Test new schema in application code

Best Practices Summary

1. Always work declaratively - Edit schema files, let Supabase generate migrations
2. One migration per logical change - Don't bundle unrelated changes
3. Review before applying - Always check generated SQL
4. Regenerate types immediately - Run

   bun gen:types

   after every schema change
5. Enable RLS by default - Security first, even for "public" tables
6. Index foreign keys - Always add indexes on reference columns
7. Use timestamps - Add

   created_at

   and

   updated_at

   to most tables
8. Comment everything - Future you will thank present you
9. Test locally first - Use local database, never modify production directly
10. Follow naming conventions - Consistency makes collaboration easier

References

• Project package.json scripts:

  /package.json

• Existing schema examples:

  /supabase/schemas/01-schema.sql

• RLS policy examples:

  /supabase/schemas/02-policies.sql

• Function examples:

  /supabase/schemas/03-functions.sql