Database Migrations

# 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

```sql
-- 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

```sql
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

```sql
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:

```bash
# 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

```bash
# 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:

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

This runs:

```bash
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):

```bash
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:

```typescript
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:

```sql
-- 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 v7 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:

```bash
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