task-orchestrator: Skill for Claude Code

Migration Review Framework

Evaluate database migration safety for SQLite-specific constraints. This project uses Flyway migrations with SQLite, which has significant limitations compared to PostgreSQL/MySQL.

Step 1: Identify Schema Changes

Read the changed files and migration SQL to determine:

• New columns — ALTER TABLE ADD COLUMN works in SQLite
• Modified columns — SQLite has NO ALTER COLUMN. Requires table recreation:
  1. Create new table with desired schema
  2. Copy data from old table
  3. Drop old table
  4. Rename new table
• New tables — a Flyway migration is the only place a table is defined (the Exposed table is a query mapping); there is no second table list to update
• Index changes — CREATE INDEX / DROP INDEX work normally

Step 2: SQLite Constraint Check

Verify against known SQLite limitations:

• No ALTER COLUMN — if modifying existing columns, table recreation pattern is used
• No DROP COLUMN in older SQLite versions — check if the Docker image's SQLite supports it
• Foreign key constraints — Flyway's connection runs with foreign_keys = OFF (see the template below), so a DROP TABLE does not cascade; the application connection runs with it ON
• TEXT affinity — SQLite stores all strings as TEXT regardless of declared type
• No concurrent write transactions — migrations must be sequential

Step 3: Data Migration Strategy

For migrations that modify existing data:

• Existing rows handled — default values for new columns, or explicit data migration
• Null safety — new NOT NULL columns require a DEFAULT or data backfill
• Large table performance — SQLite locks the entire database during writes
• Runtime-dependent guarantees are probed, not assumed — see below

Guarantees that rest on engine runtime behavior need a multi-row probe. When the assessment claims an at-rest, security, or data-integrity guarantee that depends on a specific runtime/engine behavior (e.g. SQLite PRAGMA secure_delete, VACUUM page reuse, WAL checkpoint timing, FTS5 shadow-table residue), the assessment MUST specify a multi-row/multi-write empirical probe as part of the required test strategy — not a single-row assertion, which can read clean whether or not the mechanism works. It MUST NOT phrase the guarantee as absolute ("scrubbed", "removed") when only a probabilistic or partial mechanism is available ("reduces residue", "partial scrub"); once the probe exists, state the measured bound (e.g. "3–14 of 200 rows survive with the pragma, 200+ without").