Database Migration Skill

Supabase migration patterns, RLS audit, schema validation. Guides safe DDL operations against the Supabase database.

Quand proposer ce skill

/db-migration [nom]

/db-migration

/db-migration [table_action]

/db-migration create_[module]_tables

Workflow 4 Phases (OBLIGATOIRE)

Phase 1 — Analyse

1. Identifier l'operation : CREATE TABLE, ALTER TABLE (ADD/DROP/RENAME COLUMN), CREATE INDEX, DROP TABLE, RLS policy
2. Evaluer l'impact :
   • Taille de la table cible :

     SELECT pg_size_pretty(pg_total_relation_size('table_name'));

   • Nombre de rows :

     SELECT count(*) FROM table_name;

   • Dependances : FK, triggers, RPC functions, views
3. Classifier le risque :

Phase 2 — Plan

1. Ecrire le SQL de migration dans

   backend/supabase/migrations/YYYYMMDD_description.sql

2. Ecrire le SQL de rollback (voir section Rollback ci-dessous)
3. Preparer les validations before/after (voir section Data Validation)
4. Verifier les safe patterns (IF NOT EXISTS, BEGIN/COMMIT, etc.)

Phase 3 — Execute

1. Executer les queries de validation BEFORE
2. Appliquer la migration via

   mcp__supabase__apply_migration

3. Verifier le succes dans

   mcp__supabase__list_migrations

4. Executer les queries de validation AFTER

Phase 4 — Verify

1. mcp__supabase__list_tables

   → verifier le schema

2. mcp__supabase__get_advisors(type: "security")

   → verifier RLS

3. mcp__supabase__get_advisors(type: "performance")

   → verifier index
4. Tester les queries applicatives impactees

Rollback Templates

Preparer le rollback AVANT d'executer la migration :

-- Rollback: ADD COLUMN
ALTER TABLE my_table DROP COLUMN IF EXISTS new_column;

-- Rollback: DROP COLUMN (IMPOSSIBLE sans backup)
-- ⚠️ Sauvegarder les donnees AVANT :
-- CREATE TABLE _backup_my_table_col AS SELECT id, dropped_col FROM my_table;

-- Rollback: CREATE TABLE
DROP TABLE IF EXISTS my_table;

-- Rollback: CREATE INDEX
DROP INDEX IF EXISTS idx_name;

-- Rollback: ALTER TYPE
ALTER TABLE my_table ALTER COLUMN col TYPE old_type USING col::old_type;

-- Rollback: RLS policy
DROP POLICY IF EXISTS policy_name ON my_table;

Regle : Si le rollback est impossible (DROP COLUMN, DROP TABLE), exiger confirmation utilisateur avant execution.

Backfill Strategy (tables larges)

Pour les tables > 100K rows, utiliser le batching :

-- Backfill par batch de 10K rows
DO $$
DECLARE
  batch_size INT := 10000;
  total_updated INT := 0;
  rows_affected INT;
BEGIN
  LOOP
    UPDATE my_table
    SET new_column = compute_value(old_column)
    WHERE new_column IS NULL
    LIMIT batch_size;

    GET DIAGNOSTICS rows_affected = ROW_COUNT;
    total_updated := total_updated + rows_affected;
    RAISE NOTICE 'Updated % rows (total: %)', rows_affected, total_updated;

    EXIT WHEN rows_affected = 0;
    PERFORM pg_sleep(0.1);  -- Pause pour ne pas saturer
  END LOOP;
END $$;

Index sur tables larges : toujours utiliser

CONCURRENTLY

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_name ON my_table (column);

Data Validation Queries

Executer AVANT et APRES la migration :

-- Row counts
SELECT count(*) AS row_count FROM my_table;

-- Null check sur nouvelle colonne
SELECT count(*) FILTER (WHERE new_col IS NULL) AS nulls,
       count(*) FILTER (WHERE new_col IS NOT NULL) AS filled
FROM my_table;

-- Constraint check
SELECT conname, contype FROM pg_constraint WHERE conrelid = 'my_table'::regclass;

-- Index check
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'my_table';

-- RLS check
SELECT polname, polcmd, polroles FROM pg_policy WHERE polrelid = 'my_table'::regclass;

Migration File Location

• backend/supabase/migrations/YYYYMMDD_description.sql

Pre-Migration Checklist

1. Test SQL in dev environment first (use

   mcp__supabase__execute_sql

   )
2. Verify no breaking changes to existing RPC functions
3. Check RLS impact on existing queries
4. Verify key access patterns — service_role (backend) vs anon (frontend)
5. Run advisors after migration —

   mcp__supabase__get_advisors

   for security + performance

Safe Patterns

-- Always use IF NOT EXISTS / IF EXISTS
CREATE TABLE IF NOT EXISTS my_table (...);
CREATE INDEX IF NOT EXISTS idx_name ON my_table (column);
DROP TABLE IF EXISTS old_table;

-- Wrap multi-statement migrations
BEGIN;
  ALTER TABLE my_table ADD COLUMN new_col TEXT;
  CREATE INDEX idx_new ON my_table (new_col);
COMMIT;

-- Add comments explaining purpose
COMMENT ON TABLE my_table IS 'Description of table purpose';

RLS Audit Patterns

• Every new table MUST have RLS enabled :

  ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

• Verify policies cover : SELECT, INSERT, UPDATE, DELETE
• Backend uses

  service_role

  key (bypasses RLS)
• Frontend uses

  anon

  key (subject to RLS)
• After adding RLS, test with both keys

MCP Tools Available

mcp__supabase__apply_migration

mcp__supabase__execute_sql

mcp__supabase__list_tables

mcp__supabase__get_advisors

mcp__supabase__list_migrations

Anti-Patterns (BLOCK)

• DROP TABLE

  without backup/confirmation

• ALTER TABLE

  with data loss potential (dropping columns with data)
• Disabling RLS on tables with user data
• Running DDL directly via

  execute_sql

  (use

  apply_migration

  for audit trail)
• Missing

  IF NOT EXISTS

  on CREATE statements
• Forgetting to add RLS policies on new tables

Format de Sortie

## Migration Report — [nom_migration]

### Phase 1 — Analyse
- Table(s) cible : [liste]
- Taille : [size] / [row_count] rows
- Operation : [type]
- Risque : Bas / Moyen / Haut / Critique

### Phase 2 — Plan
- SQL migration : [resume]
- SQL rollback : [resume]
- Validations preparees : [N] queries

### Phase 3 — Execution
- Migration appliquee : OUI/NON
- Version : [timestamp]

### Phase 4 — Verification
| Check | Status |
|-------|--------|
| Schema correct | PASS/FAIL |
| RLS advisors | PASS/FAIL |
| Performance advisors | PASS/FAIL |
| Queries applicatives | PASS/FAIL |

Interaction avec Autres Skills

rag-ops

__rag_knowledge

kg_rag_*

seo-content-architect

__seo_*

pieces_gamme

code-review

/code-review

.sql