<!--
Supabase Migration Discipline Playbook

Part of the free Claude Code template library from BILT AI — https://biltai.io/claude-templates

These are the actual playbooks used to build and ship production SaaS apps with
Claude Code. They're written to be handed to an agent as-is: point Claude at one
and it has the full pattern, the schema, the gotchas, and the "don't do this"
list. Paths, project refs, and org names have been replaced with placeholders
(<your-app>, <supabase-project-ref>, <your-github-org>) — search for "<" and
fill in your own before running.

Free to use, modify, and ship. No attribution required.
-->

# Supabase Migration Discipline Playbook

**Portable spec for managing Supabase schema migrations across a new SaaS app without painting yourself into a corner.**

Built from the BKE / Kompozy migration history (99+ migrations, zero rollbacks). Reference repo: `reference app`.

---

## 0. Use this spec

**Inputs:**
- Supabase project ref (`<PROJECT_REF>`) + `SUPABASE_ACCESS_TOKEN` (personal access token, NOT the anon/service-role keys)
- Whether the Supabase MCP server is available in this session (`mcp__supabase__apply_migration`)
- A memory file path where applied-migration status will be tracked

**Outputs:**
- `supabase/migrations/` directory with strictly-sequenced SQL files
- Applied-migration tracking doc that survives across agent sessions
- A repeatable workflow for: write → review → apply → record → never re-apply

---

## 1. File naming convention (load-bearing)

Format: `00NNN_<descriptive_slug>.sql`

- **Zero-padded 5-digit prefix.** `00001`, `00042`, `00099`, `00100`. Sorts lexicographically forever.
- **Slug:** lowercase, underscore-separated, describes the change. `credit_atomic_spend`, `personas_multi_and_script_previews`, `scheduled_posts_dedup`.
- **Sequence is immutable.** Once a migration ships, NEVER renumber it. Other agent sessions, branches, and the applied-tracking doc all key off the prefix.
- **Next number = max(existing) + 1.** Always check the live directory before writing a new file — parallel sessions may have claimed your slot.

BKE reference: `supabase/migrations/` currently runs from `00000_initial_schema.sql` through `00098_*` with no gaps.

---

## 2. Idempotency is mandatory

Every migration must be safe to re-apply against a DB where it has already run. Real-world reason: applied-state tracking drifts; you WILL re-run a migration by accident; the migration must no-op gracefully instead of crashing.

**Required guards:**

```sql
-- Tables
create table if not exists user_settings (
  user_id uuid primary key references profiles(id) on delete cascade,
  prefs jsonb not null default '{}'::jsonb
);

-- Columns
alter table profiles add column if not exists onboarding_completed_at timestamptz;

-- Indexes
create index if not exists ix_scheduled_posts_user_status
  on scheduled_posts (user_id, status);

-- Triggers / functions (drop+recreate is the idempotent pattern)
drop trigger if exists on_credit_spend on credit_ledger;
create trigger on_credit_spend
  after insert on credit_ledger
  for each row execute function handle_credit_spend();

-- Policies (drop+recreate)
drop policy if exists "users read own" on user_settings;
create policy "users read own" on user_settings
  for select using (auth.uid() = user_id);

-- Enums (use add value if not exists — Postgres 12+)
alter type pipeline_stage add value if not exists 'reviewing';
```

**Hard rule:** if a migration cannot be made idempotent (rare — usually data backfills), wrap it in a guard that checks a sentinel:

```sql
do $$
begin
  if not exists (select 1 from schema_migrations_local where name = '00042_backfill_credits') then
    -- destructive backfill here
    insert into schema_migrations_local (name) values ('00042_backfill_credits');
  end if;
end $$;
```

---

## 3. Application: how to actually run a migration

### ⛔ Do NOT use `supabase db push`

BKE's remote `migration_history` table is EMPTY despite 99 applied migrations. They were applied via the dashboard SQL editor, the Management API, and the Supabase MCP — none of which populate `migration_history`. Running `supabase db push` would attempt to re-apply EVERY migration, and the non-idempotent ones (or any with `add value` on a new enum) would crash.

### ✅ Option A — Supabase MCP (preferred when available)

```
mcp__supabase__apply_migration(
  name: "00042_descriptive_slug",
  sql: "<full migration SQL>"
)
```

Records the migration in the MCP-side tracking. Returns success/failure with the exact PG error if any.

### ✅ Option B — Management API (always works)

```bash
curl -X POST https://api.supabase.com/v1/projects/<PROJECT_REF>/database/query \
  -H "Authorization: Bearer $SUPABASE_ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"query": "<SQL HERE>"}'
```

Returns HTTP 201 on success. Use heredoc + `jq -Rs .` to safely escape multi-line SQL.

### ✅ Option C — Dashboard SQL editor

Manual fallback. Paste, run, copy any error. Less reproducible — only for one-offs.

---

## 4. Applied-migration tracking

Because none of the above writes to `supabase_migrations.schema_migrations` consistently, **maintain your own tracking doc.** BKE uses `bke_supabase_migrations_applied.md`.

**Format:**

```markdown
# <APP> Supabase Migrations — Applied Status

Last verified: 2026-05-20 against project <PROJECT_REF>

## Applied
- 00000_initial_schema.sql — applied 2026-01-12 via dashboard
- 00001_full_schema.sql — applied 2026-01-15 via dashboard
- 00042_credit_atomic_spend.sql — applied 2026-04-29 via MCP
...

## Pending (in repo, not yet live)
- 00099_<slug>.sql — written 2026-05-19, awaiting approval

## Skipped (intentionally not applied)
- 00037_genvideo_enterprise_slots.sql — superseded by 00041, do NOT apply
```

Before writing any new migration: re-read this file, then run `mcp__supabase__list_tables` to verify the live schema matches what tracking claims.

---

## 5. RLS hygiene — non-negotiable

Every table holding user-owned data must have RLS enabled with explicit per-operation policies. Default-deny is the posture.

```sql
alter table user_widgets enable row level security;

create policy "users select own widgets" on user_widgets
  for select using (auth.uid() = user_id);
create policy "users insert own widgets" on user_widgets
  for insert with check (auth.uid() = user_id);
create policy "users update own widgets" on user_widgets
  for update using (auth.uid() = user_id) with check (auth.uid() = user_id);
create policy "users delete own widgets" on user_widgets
  for delete using (auth.uid() = user_id);

-- Admin escape hatch (separate policy, NEVER bypass with security definer in user-callable functions)
create policy "admins read all widgets" on user_widgets
  for select using (
    exists (select 1 from profiles p where p.id = auth.uid() and p.role = 'admin')
  );
```

**Service-role bypasses RLS** automatically — server-side admin actions use the service-role client. Never expose the service-role key to the browser. See `src/lib/supabase-server.ts` for the dual-client pattern.

---

## 6. Never destructive on user-owned data

**Rule born from BKE 2026-04-23 incident:** a bulk-replace sync routine ran `DELETE WHERE user_id = X` followed by re-inserts. A race wiped `creator_profiles` rows; user had to restore from PITR backups.

**Hard bans:**
- ❌ `DELETE WHERE id NOT IN (<api response ids>)` — bulk-replace sync
- ❌ Destructive JSONB rebuilds from API responses (BKE 2026-04-28 FB pageId wipe) — merge-only via `||` or `jsonb_set`
- ❌ Sync routines that delete then insert — always upsert via `on conflict do update`
- ❌ `TRUNCATE` on any table containing user data, ever

**Deletes are allowed ONLY:**
- From an explicit user-initiated UI action ("delete this post")
- From cascade on parent deletion (auth.users → profile cascade)
- From hard-coded retention sweeps on transient tables (audit logs > 90d, etc.) where the data is explicitly defined as ephemeral

Memory reference: `feedback_never_destructive_db_writes.md`.

---

## 7. Schema patterns that age well

### Generated columns for derived values

```sql
alter table credit_ledger add column credits_charged numeric
  generated always as (round(credits_base * multiplier, 2)) stored;
```

Cross-reference: CREDIT-METERING-PLAYBOOK uses this for charge calculations. Never store + compute in app code when PG can do it deterministically.

### CHECK constraints, not ENUMs, for status fields

```sql
-- ✅ Easy to extend
alter table scheduled_posts
  add column status text not null default 'queued'
  check (status in ('queued','submitted','published','failed','cancelled'));

-- Add a new value later:
alter table scheduled_posts drop constraint scheduled_posts_status_check;
alter table scheduled_posts add constraint scheduled_posts_status_check
  check (status in ('queued','submitted','published','failed','cancelled','retry'));
```

ENUMs require drop-and-recreate (or `add value if not exists` which can't be undone), and they break PostgREST type generation in subtle ways. Avoid.

### Partial unique indexes for "one active row per group"

```sql
create unique index ux_scheduled_posts_one_active
  on scheduled_posts (user_id, target_platform)
  where status in ('queued','submitted');
```

Allows historical terminal rows (`published`, `failed`) to accumulate while enforcing single-active. BKE uses this for dedup across scheduled posts.

### Soft-link FK pattern (00035 reference)

When a transaction-log row references a user-state row that may not exist yet (debounced client persist), make the FK soft:

```sql
alter table credit_ledger
  add column workspace_id uuid references workspaces(id) on delete set null;

-- Inside the spend function:
if not exists (select 1 from workspaces where id = p_workspace_id) then
  insert into credit_ledger (user_id, workspace_id, amount) values (p_user_id, null, p_amount);
else
  insert into credit_ledger (user_id, workspace_id, amount) values (p_user_id, p_workspace_id, p_amount);
end if;
```

**Do not surface `no_credits` on FK violation** — distinguish missing-parent from out-of-budget. BKE 2026-04-29 lab note: conflating them broke debounced-persist users for 3 hours.

### Float → int gotcha (PostgREST)

PostgREST rejects JS floats against integer columns. ALL filters and the entire fallback chain fail.

```ts
// ❌ Crashes whole query + fallback chain
.eq('credits_remaining', user.credits)  // user.credits is 12.5

// ✅ Coerce before sending
.eq('credits_remaining', Math.ceil(user.credits))
```

Memory reference: `feedback_postgrest_int_columns.md`.

---

## 8. Approval + backup workflow

**Before applying ANY migration that touches user-state tables:**

1. Show the user: the full SQL, a plan summary (what tables/columns change), and an impact assessment (how many rows affected, whether RLS is altered).
2. Wait for explicit "go". Migrations are destructive-by-default risk surface — no auto-apply.
3. For destructive ops (`DROP TABLE`, `DROP COLUMN`, `TRUNCATE`, data backfill), confirm a PITR snapshot exists. Supabase PITR is the recovery path; trigger a manual backup if PITR window doesn't cover.
4. Apply via MCP or Management API.
5. Update the applied-migration tracking doc in the same commit.
6. Run `mcp__supabase__list_tables` + targeted selects to verify the live schema matches expectations.

---

## 9. Common pitfalls

- ❌ **Don't `supabase db push` on a project with untracked applied migrations** — it re-applies everything, duplicates crash.
- ❌ **Don't renumber migration files** — sequence is the only stable identifier across branches/sessions/agents.
- ❌ **Don't write a migration without `IF NOT EXISTS` guards** — re-apply will crash and you WILL re-apply by accident.
- ❌ **Don't enable RLS without policies** — users get locked out of their own data, and the failure mode (empty result set) is silent.
- ❌ **Don't use ENUMs over CHECK constraints** for status-like fields — extension cost compounds.
- ❌ **Don't destructively rebuild JSONB blobs** from API responses — merge with `||` or `jsonb_set` only.
- ❌ **Don't apply a migration without verifying live schema first** — `list_tables`, query `pg_indexes`, confirm assumed columns exist.
- ❌ **Don't bypass RLS with `security definer` functions** unless the function is service-role-only callable.
- ❌ **Don't use `localStorage` for any new persistence surface** — start DB-backed. Memory: `feedback_supabase_first.md`.

---

## 10. Kickoff prompt

> Execute the Supabase Migration Discipline Playbook at `<workspace>/templates/c-tier/SUPABASE-MIGRATION-PLAYBOOK.md` for this new app. Bootstrap the `supabase/migrations/` directory using BKE conventions (`reference app` as the reference).
>
> Inputs:
> - PROJECT_REF: [provided]
> - SUPABASE_ACCESS_TOKEN: [provided in env]
> - Applied-tracking doc path: [provided — typically `~/.claude/projects/.../memory/<app>_supabase_migrations_applied.md`]
> - MCP available: [yes/no]
>
> Pause after the initial schema migration (00000) ships for review. Never apply user-state migrations without explicit approval.

---

**Reference files in BKE:**
- `supabase/migrations/` — full 99-file migration history
- `supabase/migrations/00031_credit_atomic_spend.sql` — RPC + advisory-lock pattern
- `supabase/migrations/00035_credit_spend_soft_gc_link.sql` — soft-link FK reference
- `supabase/migrations/00040_scheduled_posts_dedup.sql` — partial unique index pattern
- `src/lib/supabase-server.ts` — dual-client (anon + service-role)
- Memory: `bke_supabase_migrations_applied.md` — applied-tracking format reference
- Memory: `feedback_never_destructive_db_writes.md` — 2026-04-23 incident report
- Memory: `feedback_supabase_first.md` — no new localStorage rule
- Memory: `feedback_postgrest_int_columns.md` — float→int coercion