Migrations

Database migrations allow you to version control your database schema changes and apply them consistently across environments.

In Handlet, migration history is strict and forward-only:

• Existing files in apps/web/supabase/migrations are immutable once they exist on the target branch
• Follow-up database changes must be shipped in a new later-timestamped migration
• apps/web/supabase/schemas holds the current intended schema; migrations/ is the historical record of how it evolved

Creating a Migration

Update the declarative schema first, then generate a new migration:

pnpm --filter web supabase:db:diff

This will generate a new migration file in the apps/web/supabase/migrations directory based on the differences between your local database and the schema files.

If you realize an earlier migration needs to change, do not edit or rename it. Restore the old migration exactly as it exists on the target branch, keep the intended schema update in schemas/, and generate a new migration for the delta.

Applying Migrations

To apply migrations to your local database:

pnpm --filter web supabase migrations up

Migration Best Practices

1. Always test migrations locally first before applying to production
2. Make migrations reversible when possible by including DOWN statements
3. Use transactions to ensure atomic operations
4. Add indexes for foreign keys and frequently queried columns
5. Include RLS policies in the same migration as table creation
6. Never rewrite migration history after a migration exists on the target branch

Local Enforcement

Run the local discipline check before pushing any schema or migration changes:

pnpm run check-db-discipline-local

This resolves the local merge base from your upstream branch, with origin/develop and origin/main as fallbacks, then runs the same migration and RLS guards used in CI.

The repository also installs a managed pre-push hook on pnpm install to run this automatically. In GitHub, branch protection should require the db-discipline status check for main and develop.

Example Migration

-- Create a new table
CREATE TABLE tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  completed BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Add RLS
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

-- Create policies
CREATE POLICY "Users can view their account tasks"
  ON tasks FOR SELECT
  USING (account_id IN (SELECT get_user_accounts(auth.uid())));

Resetting the Database

To completely reset your local database with the latest schema:

pnpm supabase:web:reset

This will drop all tables and reapply all migrations from scratch.