Migration Guide

Database schema changes are managed through SQL migration files in supabase/migrations/. As of February 2026, there are 108 migration files.

Migration File Location

supabase/
├── config.toml
├── migrations/
│   ├── 20260101000000_initial_schema.sql
│   ├── 20260101000001_add_clients.sql
│   ├── ...
│   └── 20260210200001_convert_disposition_to_text.sql
└── functions/
    └── ... (39 edge functions)

Naming Convention

YYYYMMDDHHMMSS_description.sql

• Timestamp: 14-digit timestamp ensuring chronological ordering
• Description: Snake_case description of the change
• The timestamp guarantees migrations run in the correct order

Examples:

• 20260203000000_add_markets_table.sql
• 20260208100000_add_internal_sales.sql
• 20260210200001_convert_disposition_to_text.sql

Key Migration Timeline

Date	Migration	Description
2026-01-xx	Initial migrations	Core tables: clients, campaigns, contacts, calls, meetings
2026-02-03	Markets + signals	markets, market_attorneys, signals tables
2026-02-04	Scheduling	calendar_connections, booking_links tables
2026-02-06	Seed AmLaw firms	Insert 200 AmLaw firms into firms table
2026-02-08 (100000)	Internal sales	sales_prospects, sales_calls tables
2026-02-08 (120000)	Dual-mode booking	user_calendar_connections, sales_meetings, nullable client_id on booking_links, availability_settings on user_profiles
2026-02-10 (100000)	GHL integrations	ghl_integrations, ghl_import_staging, ghl_import_batches tables, GHL fields on sales_prospects
2026-02-10 (200001)	Disposition to TEXT	Convert calls.disposition from call_disposition enum to TEXT type

Not All Tables Are in Migrations

The attorneys table was created via the Supabase dashboard and does not appear in any migration file. If you need to recreate the database from scratch, this table must be created manually or a migration must be added retroactively.

Creating a New Migration

Step 1: Create the migration file

supabase migration new description_of_change

This creates a new file in supabase/migrations/ with the current timestamp.

Step 2: Write the SQL

-- Example: Add a new column
ALTER TABLE sales_prospects
ADD COLUMN priority text DEFAULT 'normal';

-- Example: Create a new table
CREATE TABLE IF NOT EXISTS new_table (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  created_at timestamptz DEFAULT now()
);

-- Example: Enable RLS
ALTER TABLE new_table ENABLE ROW LEVEL SECURITY;

-- Example: Add RLS policies
CREATE POLICY "internal_select" ON new_table
  FOR SELECT USING (is_internal_user());

Always Enable RLS

Every new table must have ALTER TABLE ... ENABLE ROW LEVEL SECURITY and at least one policy. Tables without RLS are accessible to anyone with the anon key.

Step 3: Apply locally

# Reset local DB and reapply all migrations
supabase db reset

# Or push only new migrations
supabase db push

Step 4: Apply to production

# Push migrations to the remote Supabase project
supabase db push

Step 5: Update TypeScript types

If the migration changes table structure, update the types in src/integrations/supabase/types.ts manually. These types are not auto-generated.

Migration Best Practices

DO

• Use IF NOT EXISTS / IF EXISTS for idempotent migrations
• Include ENABLE ROW LEVEL SECURITY for new tables
• Add RLS policies in the same migration as the table creation
• Use gen_random_uuid() for primary key defaults
• Add created_at timestamptz DEFAULT now() to all tables
• Test migrations locally with supabase db reset before pushing to production

DO NOT

• Modify existing migration files -- always create a new migration
• Drop tables or columns in production without a data backup plan
• Use CASCADE on foreign key drops without understanding the impact
• Forget to update TypeScript types after schema changes

Example Migration: Adding a New Table

-- Migration: 20260212000000_add_example_table.sql

-- Create table
CREATE TABLE IF NOT EXISTS example_items (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  client_id uuid REFERENCES clients(id) ON DELETE CASCADE,
  name text NOT NULL,
  status text DEFAULT 'active',
  metadata jsonb,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- Enable RLS
ALTER TABLE example_items ENABLE ROW LEVEL SECURITY;

-- Admin: full access
CREATE POLICY "example_items_admin_all" ON example_items
  FOR ALL USING (has_role('admin'));

-- BDR: read access
CREATE POLICY "example_items_bdr_select" ON example_items
  FOR SELECT USING (has_role('bdr'));

-- Client: own data only
CREATE POLICY "example_items_client_select" ON example_items
  FOR SELECT USING (client_id = get_user_client_id());

-- Index for common queries
CREATE INDEX IF NOT EXISTS idx_example_items_client_id
  ON example_items(client_id);

Troubleshooting

Migration fails on push

If a migration fails when pushing to production:

1. Check the error message in the Supabase dashboard logs
2. Fix the SQL in a new migration (do not edit the failed one)
3. If the failed migration partially applied, you may need to manually clean up via the SQL editor

Migration order conflicts

If two developers create migrations with overlapping timestamps:

1. Rename one migration to have a later timestamp
2. Ensure the renamed migration still works in the new order
3. Test with supabase db reset locally

Checking applied migrations

# List all migrations and their status
supabase migration list