supabase-migration-writer

name	supabase-migration-writer
description	Expert assistant for Supabase database operations in the KR92 Bible Voice project. Use when (1) creating database migrations, (2) adding/modifying tables or RLS policies, (3) creating RPC functions, (4) querying Supabase configuration (secrets, Edge Functions, schemas), (5) writing rollback scripts, or (6) answering questions about database schema and configuration.

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:

1. Read the version the call actually recorded:

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

2. Name the local file with that version, or repair the history to accept yours:

   supabase migration repair --status applied <your-version>
   

3. 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.

Aja tämä tarkistus — älä luota siihen että muistat säännön

Yllä oleva ohje on ollut tässä tiedostossa koko ajan, ja historia ajautui silti 17 migraatiolla 30.7.–7.8. kahden eri agentin toimesta. Manuaalinen askel unohtuu, koska unohtaminen ei tunnu miltään. Tarkista joukkovertailulla — sekunneissa, ilman supabase linkiä:

SELECT count(*), md5(string_agg(version, E'\n' ORDER BY version))
FROM supabase_migrations.schema_migrations;

ls supabase/migrations/*.sql | sed -E 's#.*/([0-9]{14}).*#\1#' | sort \
  | awk 'BEGIN{ORS=""} {print sep $0; sep="\n"}' | md5

Sama luku + sama tarkiste = ei versiodriftiä. Eri → comm -23 / comm -13 kertoo kummalta puolelta puuttuu mitä.

⚠ Supabase Migration Health -workflow EI havaitse driftiä. check-supabase-health.sh step 6 on sen ainoa drift-askel ja se ohittaa itsensä CI-tilassa (ok "Skipped in CI mode") → vihreä. Ehto on vanhentunut: workflow linkittää projektin juuri ennen skriptin ajoa. Migration Health oli vihreä samoilla committeilla joilla Supabase Sync kaatui driftiin. Älä lue sen vihreyttä todisteeksi. Yksityiskohdat + korjaustapa: references/learnings.md → "Migraatiot pysyvät synkassa vain MEKAANISESTI".

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:

1. 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.
2. 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.
3. 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.
4. 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;
   

5. 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(*)  …  broken )   
       RAISE EXCEPTION , …;
      IF;
    $$;
   

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 --schema public > 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.