Database Schema Extension

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