Migrations

Note: This is mock/placeholder content for demonstration purposes.

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

Creating a Migration

To create a new migration, use the following command:

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.

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

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.