Skip to content

moonlight-lupin/agent-skills / plugins/dev-pipeline/skills/review-lessons

review-lessons

Verified

Library of lessons from past build-review loops — not a stage. Use when a review or fix round hits a specific case: a reviewer BLOCKING that contradicts the brief, a test that passes but checks nothing, a feature dead in production, SQLite/WAL migrations, MCP tool surfaces, REST API authz, app ↔ external-source sync, Android/APK verification, fill-template extraction, CLI/git mechanics, reviewer outages. Load the one file the case needs; never load the whole library.

FileKindSize
SKILL.mdexecutableskill md5.5 KB
references/architecture-refactor-test-updates.mdexecutablescript2.9 KB
references/beta-gate-release-hardening.mdexecutablescript3.3 KB
references/ci-and-repo-hygiene.mdexecutablescript3.7 KB
references/confirm-loop-lessons.mdexecutablescript24.2 KB
references/cross-model-parallel-review.mdexecutablescript4.8 KB
references/data-integrity-principles.mdexecutablescript7.3 KB
references/delegate-skills-adaptation.mdexecutablescript2.1 KB
references/evidence-pairs.mdexecutablescript2.5 KB
references/extraction-from-filled-docs.mdexecutablescript6.1 KB
references/field-briefing-integration.mdexecutablescript5.2 KB
references/full-module-replacement-scoping.mdexecutablescript6.3 KB
references/generated-frontmatter-and-reviewer-outages.mdexecutablescript3.0 KB
references/loop-pitfalls.mdexecutablescript27.9 KB
references/mcp-category-tool-implementation.mdexecutablescript5.3 KB
references/mcp-design-review-patterns.mdexecutablescript5.7 KB
references/minified-bundle-selector-research.mdexecutablescript4.4 KB
references/mobile-app-verification.mdexecutablescript4.8 KB
references/multi-batch-sequencing.mdexecutablescript5.7 KB
references/parallel-dual-review.mdexecutablescript10.8 KB
references/pitfalls-dispatch-semantics.mdexecutablescript6.3 KB
references/pitfalls-fix-round-verification.mdexecutablescript6.6 KB
references/planning-with-files-adaptation.mdexecutablescript2.6 KB
references/plugin-gates-and-fix-rounds.mdexecutablescript8.2 KB
references/production-reachability-review.mdexecutablescript5.0 KB
references/reconciliation-review-findings.mdexecutablescript3.1 KB
references/rest-api-review-checklist.mdexecutablescript13.0 KB
references/review-findings-adjudication.mdexecutablescript34.4 KB
references/scope-test-in-process-execution.mdexecutablescript4.2 KB
references/security-property-tests.mdexecutablescript14.2 KB
references/sqlite-migration-pitfalls.mdexecutablescript4.1 KB
references/sqlite-wal-review-findings.mdexecutablescript5.0 KB
references/sync-pipeline-review-patterns.mdexecutablescript13.2 KB
references/tool-mechanics-pitfalls.mdexecutablescript44.1 KB
references/upgrade-path-test-pattern.mdexecutablescript3.0 KB

references/sqlite-migration-pitfalls.md

View on GitHub
# SQLite Migration Pitfalls

Learned from one migration build (Aug 2026). Codex review caught a BLOCKING data-loss bug in migration 055.

## Contents

- PRAGMA foreign_keys=OFF Inside a Transaction
- CHECK Constraint Changes Require Table Rebuild
- PostgreSQL Migrations Are Different

## PRAGMA foreign_keys=OFF Inside a Transaction

### The Problem

The migration runner (`_migrate_sqlite` in `core/db.py`) wraps each migration file in `BEGIN`/`COMMIT`:

```python
conn.execute("BEGIN")
for stmt in statements:
    conn.execute(stmt)
conn.execute("COMMIT")
```

`PRAGMA foreign_keys=OFF` has **no effect** inside an active transaction. SQLite ignores it. So when a migration does:

```sql
PRAGMA foreign_keys=OFF;           -- NO-OP (inside BEGIN)
CREATE TABLE journal_entries_new (...);
INSERT INTO journal_entries_new SELECT * FROM journal_entries;
DROP TABLE journal_entries;        -- CASCADES to journal_lines!
ALTER TABLE journal_entries_new RENAME TO journal_entries;
PRAGMA foreign_keys=ON;            -- NO-OP (inside BEGIN)
```

The `DROP TABLE journal_entries` triggers `ON DELETE CASCADE` on `journal_lines.entry_id`, deleting ALL journal lines.

### The Fix: Rename Dependent Tables Aside

Instead of relying on `PRAGMA foreign_keys=OFF`, rename dependent tables out of the way before dropping the parent:

```sql
-- Step 1: Rename journal_lines out of the way (breaks the FK reference)
ALTER TABLE journal_lines RENAME TO journal_lines_temp_055;

-- Step 2: Rebuild journal_entries with the new CHECK constraint
CREATE TABLE journal_entries_fxr_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ...
    entry_type TEXT NOT NULL DEFAULT 'general'
        CHECK(entry_type IN (..., 'fx_revaluation')),
    ...
);

-- Step 3: Copy all data
INSERT INTO journal_entries_fxr_new (...) SELECT ... FROM journal_entries;

-- Step 4: Drop old table (no CASCADE — journal_lines was renamed)
DROP TABLE journal_entries;

-- Step 5: Rename new table
ALTER TABLE journal_entries_fxr_new RENAME TO journal_entries;

-- Step 6: Recreate indexes
CREATE INDEX IF NOT EXISTS idx_je_company ON journal_entries(company_id);
...

-- Step 7: Rename journal_lines back (FK now points to rebuilt table)
ALTER TABLE journal_lines_temp_055 RENAME TO journal_lines;
```

### Why This Works

- `ALTER TABLE ... RENAME TO` breaks the FK reference from `journal_lines` to `journal_entries`
- `DROP TABLE journal_entries` has no children to cascade to
- `ALTER TABLE ... RENAME TO` on the temp table re-creates the FK reference to the rebuilt parent
- No data loss, no dependency on PRAGMA behavior

### Detection

Codex reproduced this by running the migration against an in-memory SQLite DB with existing entries and lines:

```python
c.executescript('''CREATE TABLE journal_entries(...); CREATE TABLE journal_lines(... REFERENCES journal_entries(id) ON DELETE CASCADE); INSERT ...; INSERT ...;''')
c.execute('BEGIN')
# Execute migration 055 statements...
print('journal lines after rebuild=', c.execute('select count(*) from journal_lines').fetchone()[0])
# Output: 0  ← DATA LOSS
```

### General Rule

Any SQLite migration that rebuilds a table with `ON DELETE CASCADE` children must use the rename-aside pattern. Never rely on `PRAGMA foreign_keys=OFF` inside a transaction-wrapped migration runner.

## CHECK Constraint Changes Require Table Rebuild

SQLite cannot `ALTER` a CHECK constraint in place. To add a new value to a CHECK constraint:

1. Create a new table with the updated CHECK
2. Copy all data from the old table
3. Drop the old table
4. Rename the new table

This is the standard SQLite table-rebuild pattern. The rename-aside fix above is the safe variant when the table has CASCADE children.

## PostgreSQL Migrations Are Different

PostgreSQL can `ALTER TABLE ... DROP CONSTRAINT` and `ADD CONSTRAINT` without rebuilding the table. The PG variant of migration 055 is simpler:

```sql
ALTER TABLE journal_entries DROP CONSTRAINT IF EXISTS journal_entries_entry_type_check;
ALTER TABLE journal_entries ADD CONSTRAINT journal_entries_entry_type_check
    CHECK (entry_type IN (..., 'fx_revaluation'));
```

No data loss risk, no table rebuild needed.
finishing-a-development-branchobra/superpowersUse when implementation is complete, all tests pass, and you need to decide how to integrate the workStars: 322kUpdated Claude CodeCodexVerifiedInstall
systematic-debuggingobra/superpowersUse when encountering any bug, test failure, or unexpected behavior, before proposing fixesStars: 322kUpdated Claude CodeCodexVerifiedInstall
test-driven-developmentobra/superpowersUse when implementing any feature or bugfix, before writing implementation codeStars: 322kUpdated Claude CodeCodexVerifiedInstall
skill-creatoranthropics/skillsCreate new skills, modify and improve existing skills, and measure skill performance. Use when users want to create a skill from scratch, edit, or optimize an existing skill, run evals to test a skill, benchmark skill performance with variance analysis, or optimize a skill's description for better triggering accuracy.Stars: 198kUpdated Claude CodeCodexVerifiedInstall