Database Migrations

All schema changes go through Supabase migrations stored in supabase/migrations/. The platform uses a baseline migration plus forward-only migrations.

Migration file structure

supabase/migrations/
  00000000000000_baseline.sql     # Full initial schema
  20260301_001_add_widget.sql     # Forward migration
  20260305_002_add_index.sql      # Another migration

Naming convention

YYYYMMDD_NNN_descriptive_name.sql

• YYYYMMDD -- date the migration was created
• NNN -- sequence number for that day (001, 002, etc.)
• descriptive_name -- what the migration does, in snake_case

Examples:

• 20260315_001_add_comments_table.sql
• 20260315_002_add_entity_type_slug_index.sql
• 20260320_001_create_workflow_runs.sql

Writing a migration

Basic table creation

-- Create the new table
CREATE TABLE IF NOT EXISTS public.widgets (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES public.tenants(id),
  name text NOT NULL,
  config jsonb DEFAULT '{}'::jsonb,
  created_by uuid REFERENCES auth.users(id),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

-- Index the tenant_id (required for RLS performance)
CREATE INDEX IF NOT EXISTS idx_widgets_tenant_id ON public.widgets(tenant_id);

-- Enable RLS
ALTER TABLE public.widgets ENABLE ROW LEVEL SECURITY;

-- RLS policies (use (SELECT auth.uid()) pattern)
CREATE POLICY "Tenant members can read widgets"
  ON public.widgets FOR SELECT
  USING (
    tenant_id IN (
      SELECT tenant_id FROM public.user_tenants
      WHERE user_id = (SELECT auth.uid())
    )
  );

CREATE POLICY "Editors can manage widgets"
  ON public.widgets FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM public.user_permissions
      WHERE user_id = (SELECT auth.uid())
        AND tenant_id = widgets.tenant_id
        AND permission = 'entities.team.create'
    )
  );

-- Enable audit tracking (optional)
SELECT enable_audit_tracking('widgets');

-- Down migration (comment for reference):
-- DROP POLICY IF EXISTS "Editors can manage widgets" ON public.widgets;
-- DROP POLICY IF EXISTS "Tenant members can read widgets" ON public.widgets;
-- DROP TABLE IF EXISTS public.widgets;

Adding columns

ALTER TABLE public.entities
  ADD COLUMN IF NOT EXISTS priority integer DEFAULT 0;

-- Down: ALTER TABLE public.entities DROP COLUMN IF EXISTS priority;

Adding indexes

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_entities_priority
  ON public.entities(tenant_id, priority);

Use CONCURRENTLY for indexes on large tables to avoid locking.

Testing locally

Always test migrations locally before pushing to production:

# Reset local database and re-run all migrations from scratch
pnpm db:reset

# Verify the migration applied correctly
pnpm db:types    # Should regenerate types without errors
pnpm typecheck   # Should pass with the new types
pnpm test        # Should pass
pnpm build       # Should pass

pnpm db:reset drops and recreates the local database, runs all migrations in order, and re-seeds data. This is the definitive test that your migration is correct and compatible with all previous migrations.

Applying to production

# Push pending migrations to the remote Supabase project
pnpm db:push

This connects to the remote project (identified by NEXT_PUBLIC_SUPABASE_URL and SUPABASE_SECRET_KEY) and applies any migrations that haven't been run yet.

After applying:

# Regenerate TypeScript types from the remote schema
pnpm db:types

This runs:

npx supabase gen types typescript --project-id mhfzqccnyqxyteedsrdi > lib/supabase/database.types.ts

Commit the regenerated types file. The build will fail if types are out of sync with the database schema.

Generating a migration from changes

If you've made schema changes directly in the Supabase dashboard (not recommended for production, but useful for prototyping):

pnpm db:diff

This compares the local schema against the remote and generates a migration SQL file. Review the output carefully before committing.

Key gotchas

entity_types.id has no default

The entity_types table does not have a default UUID generator on the id column. You must always provide an explicit ID:

const id = crypto.randomUUID();
await supabase.from("entity_types").insert({ id, slug: "...", name: "..." });

This catches accidental inserts without an ID at the application level rather than silently generating one.

auth.uid() in RLS policies

Always wrap auth.uid() in a subquery:

-- Good: evaluated once, cached
WHERE user_id = (SELECT auth.uid())

-- Bad: re-evaluated per row
WHERE user_id = auth.uid()

user_tenants, not tenant_members

The table is called user_tenants. There is no tenant_members table. Using the wrong name will cause silent failures in RLS policies.

messages, not chat_messages

Chat messages are stored in the messages table, not chat_messages. The messages table has a parts JSONB column for AI SDK v6 rich content.

Tenant-scoped tables

Every new table that holds user data must:

1. Have a tenant_id column with a foreign key to tenants(id)
2. Have an index on tenant_id
3. Have RLS enabled
4. Have RLS policies that check tenant membership via user_tenants

Reversibility

Include down migration SQL as comments at the bottom of every migration file. While there is no automated rollback mechanism, these comments document how to manually reverse the change if needed.

Dumping the schema

To create a fresh baseline dump of the current schema:

pnpm db:dump

This generates supabase/baseline/schema.sql from the current database state. This is useful for documentation but is not used in the migration pipeline.

Migration checklist

Before committing a migration:

• File follows naming convention: YYYYMMDD_NNN_descriptive_name.sql
• pnpm db:reset passes locally (all migrations apply cleanly)
• New tables have tenant_id, indexes, RLS enabled, and RLS policies
• RLS policies use (SELECT auth.uid()) pattern
• Down migration documented in comments
• pnpm db:types regenerates types successfully
• pnpm typecheck passes with new types
• pnpm build passes