Supabase Migration Writer

Context Files (Read First)

For schema and Supabase layout, read from Docs/context/:

Docs/context/db-schema-short.md - Database schema overview
Docs/context/supabase-map.md - Edge Functions, migrations, access matrix

Cross-cutting learnings: See .claude/LEARNINGS.md → "Supabase/Database" section for RLS+GRANT patterns, RPC gotchas, and CHECK constraints.

Quick Reference

Project ID: iryqgmjauybluwnqhxbg
Migrations: supabase/migrations/
Edge Functions: supabase/functions/

Migration File Convention

supabase/migrations/YYYYMMDDHHMMSS_description.sql

Example: 20250124120000_add_user_notes_table.sql

CRITICAL: MCP `apply_migration` stamps the version at RUN TIME

mcp__plugin_supabase_supabase__apply_migration does not use your filename's timestamp. It records the version as the moment it ran. Write 20260728120000_commentary_sections.sql, apply it via MCP, and the DB history gets 20260728104344 — the same SQL now exists twice under two different version numbers, and supabase db push will try to run your file again forever.

This is how the July 2026 drift happened (366 remote-only versions, 241 local-only files, db push broken for months, ALLOWED_DUPES allowlist as a workaround). Reconciled 30.7.2026 in cdbd5be39. Do not restart it.

Rule — after every apply_migration call:

Read the version the call actually recorded:

SELECT version, name FROM supabase_migrations.schema_migrations
ORDER BY version DESC LIMIT 3;

Name the local file with that version, or repair the history to accept yours:
```
supabase migration repair --status applied <your-version>
```
Verify before finishing: supabase db push --dry-run must not list the migration you just applied.

Prefer supabase db push over apply_migration when Docker/CLI access is available — push writes the filename's version, so no repair step is needed. Reach for apply_migration when you need the change live immediately.

If drift already exists

Never run supabase migration repair --status reverted, even though the CLI suggests it by name for every remote-only version. Those migrations really ran; deleting the rows makes the history lie and db reset stops reproducing the database.
supabase migration fetch reconstructs local .sql files from schema_migrations.statements — this is the way back, provided statements is populated (check for NULL/empty first).
Before marking anything --status applied, prove its effect exists in the DB. Query pg_proc, not to_regproc — to_regproc returns NULL for overloaded functions and gives a false negative.
Re-running a migration is not automatically a no-op: seed/UPDATE migrations (e.g. one that sets ai_feature_bindings.ai_model) will regress live config.

CRITICAL: a fix applied with `execute_sql` is invisible drift

A different and quieter failure than the version-stamping one above. There, db push --dry-run shouts at you. Here it says "Remote database is up to date" and is still wrong.

mcp__plugin_supabase_supabase__execute_sql (and supabase db query -f) change the database without writing anything to schema_migrations. Do that to fix something live, and:

the production DB is correct
the repo is correct
db push --dry-run is clean
db reset produces a different database

How it bit us (30.7.2026, fixed in 20260730230000). Prompt v2 expanded its PITUUS section to rules 15–19, so the KIELI block that followed at 17–19 collided. The renumbering went to production via execute_sql and was never a migration — it only reached the repo file. Meanwhile apply_migration stamped the run-time versions, which put the files in this order:

20260729221618  v2 created              → KIELI 17-19 (collision)
20260729222743  v3 created FROM v2      → inherits the collision
20260730110000  v2 renumbered           → v3 untouched

Production ran them in the real order (create → renumber → derive), so v3 is right there. A fresh rebuild derives v3 before the renumbering, so the active prompt would ship with rules 17,18,19 twice.

The trap is derived state. A migration that reads existing rows (SELECT system_prompt INTO … WHERE version = 2, a backfill from another table, anything computed from current data) bakes in whatever the DB happened to hold at that moment. Order matters, and execute_sql silently removes a step from the order.

Rules:

Any change you intend to keep is a migration. execute_sql is for reading, for one-off investigation, and for data repair you are willing to lose on rebuild. If you catch yourself fixing schema or configuration with it, write the migration instead — or immediately after, before moving on.
db push --dry-run does not detect this. The check that does is asking: if I rebuilt from migrations alone, would I get this same database? Reason about it explicitly whenever a migration derives values from existing rows.
Fix forward, never re-order. Stamped history is immutable. Add a later migration that converges the two states, make it idempotent (guard on the broken pattern so it is a no-op where the fix already landed), and scope it precisely — in our case version >= 2, because v1's PITUUS is 15–16 so its KIELI 17–19 was correct and must not be touched.

Prove it on the rebuild path, not just on production. Production is already right; that tells you nothing. Simulate the rebuild state in a transaction, run the fix, assert the result, ROLLBACK:

BEGIN;
  -- put the row back into the state a fresh rebuild would produce
  UPDATE … SET system_prompt = regexp_replace(…, '20\.', '17\.');
  -- run the fix
  UPDATE … WHERE system_prompt ~ 'KIELI\n17\.';
  -- assert: the target converged AND the row that was already correct is untouched
  SELECT version, system_prompt ~ 'KIELI\n20\.' FROM …;
ROLLBACK;

End the forward fix with a self-check that raises, so a future rebuild fails loudly instead of shipping a subtly wrong value:

DO $$ BEGIN
  IF (SELECT count(*) FROM … WHERE <broken pattern>) > 0 THEN
    RAISE EXCEPTION 'KIELI numbering still collides in % versions', …;
  END IF;
END $$;

Essential Patterns

Create Table with RLS

CREATE TABLE IF NOT EXISTS public.table_name (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_table_user_id ON public.table_name(user_id);

ALTER TABLE public.table_name ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own data"
ON public.table_name FOR SELECT TO authenticated
USING (user_id = auth.uid());

CREATE POLICY "Users can insert own data"
ON public.table_name FOR INSERT TO authenticated
WITH CHECK (user_id = auth.uid());

CREATE TRIGGER set_updated_at
BEFORE UPDATE ON public.table_name
FOR EACH ROW EXECUTE FUNCTION public.handle_updated_at();

Add Column

ALTER TABLE public.table_name
ADD COLUMN IF NOT EXISTS new_column TEXT DEFAULT 'value';

COMMENT ON COLUMN public.table_name.new_column IS 'Description';

Create RPC Function

CREATE OR REPLACE FUNCTION public.function_name(
  p_user_id UUID DEFAULT auth.uid(),
  p_limit INT DEFAULT 20
)
RETURNS TABLE (col1 UUID, col2 TEXT)
LANGUAGE sql STABLE SECURITY DEFINER
SET search_path TO 'public', 'bible_schema'
AS $$
  SELECT col1, col2
  FROM table_name
  WHERE user_id = p_user_id
  LIMIT p_limit;
$$;

GRANT EXECUTE ON FUNCTION public.function_name TO authenticated;

Data Types Quick Reference

Use Case	Type
ID	`UUID DEFAULT gen_random_uuid()`
User ref	`UUID REFERENCES auth.users(id)`
Text	`TEXT`
Boolean	`BOOLEAN DEFAULT true`
Timestamp	`TIMESTAMPTZ DEFAULT now()`
Number	`INTEGER`
Decimal	`NUMERIC(10,2)`
JSON	`JSONB DEFAULT '{}'`
Array	`TEXT[] DEFAULT '{}'`
Enum	`TEXT CHECK (col IN ('a', 'b'))`

MCP Tools Available

Use Supabase MCP tools directly:

mcp__supabase__list_tables        # List all tables
mcp__supabase__execute_sql        # Run queries
mcp__supabase__apply_migration    # Apply DDL
mcp__supabase__list_edge_functions
mcp__supabase__get_logs           # Debug issues
mcp__supabase__get_advisors       # Security/perf checks

References

Context docs: Docs/context/db-schema-short.md, Docs/context/supabase-map.md (authoritative)
Secrets & env vars: See references/secrets.md

Testing Migrations

# Apply locally
supabase db push

# Reset and reapply all
supabase db reset

Best Practices Checklist

Use IF NOT EXISTS / IF EXISTS
Add created_at and updated_at timestamps
Enable RLS on all tables
Add indexes for foreign keys and filtered columns
Use SECURITY DEFINER for RPC functions
Set search_path in functions
Add COMMENT ON for documentation
Create rollback script for complex changes
Update TypeScript types after migration (see learnings)

CRITICAL: Type Synchronization

After ANY migration that adds tables, columns, or RPC functions:

npx supabase gen types typescript --project-id iryqgmjauybluwnqhxbg > apps/raamattu-nyt/src/integrations/supabase/types.ts

If types can't be regenerated, manually add to types.ts. See references/learnings.md for patterns and workarounds.

Why this matters: Lovable Cloud uses the committed types.ts file. If types are out of sync, builds fail.

supabase-migration-writer