Prompt #132

Variables

{'name': 'source_db', 'description': 'path'}, {'name': 'dest_db', 'description': 'path'}, {'name': 'topic', 'description': 'slug for script names'}, {'name': 'table_name', 'description': 'per-table'}, {'name': 'tables', 'description': 'list'}, {'name': 'reason', 'description': 'why migrate'}

Tags

stack-aware,sqlite,migration,wave-8c,backup

Source

research-2026-05-01-stack-aware-handcrafted

Use count

0

Created

2026-05-01T18:30:35.938632+00:00

Updated

2026-05-01T18:30:35.938632+00:00

Content

Migrate data from `{source_db}` to `{dest_db}` following Wave-8c (rag-stack#50) idempotent + verifiable pattern.

Use `bin/migrate-lessons-from-glug.py` as reference (already in rag-stack repo). The pattern:

1. **Pre-flight safety**:
   - `sqlite-pre-write-check {source_db}` (refuses if another writer holds it)
   - Operator coordinates a quiesce window if there are active writers
   - `sqlite3 {source_db} 'PRAGMA integrity_check'` MUST be ok

2. **Auto-snapshot** dest before any write:
   `cp {dest_db} {dest_db}.bak.$(date +%s)` if dest exists

3. **Dry-run mode** prints expected counts, no writes:
   `python3 bin/migrate-{topic}.py --source {source_db} --dest {dest_db} --dry-run`

4. **Schema bootstrap** in dest (if empty): apply CREATE TABLE IF NOT EXISTS for each target table. Match source columns + indexes + triggers. Re-create FTS5 virtual tables (don't naively copy — they need rebuild via `INSERT INTO fts(fts) VALUES('rebuild')`).

5. **Cross-DB copy** via SQLite ATTACH:
```sql
ATTACH DATABASE '{source_db}' AS src;
INSERT OR IGNORE INTO {table_name} SELECT * FROM src.{table_name};
DETACH DATABASE src;
```

6. **Verify-only mode** post-migration: compare COUNT(*) + MIN/MAX(id) between source and dest. Print MATCH or MISMATCH per table.

7. **Rollback script** `bin/rollback-{topic}-migration.sh`: locates latest .bak.<ts>, mv current → .failed.<ts>, mv .bak.<ts> → current.

Tables to migrate: {tables}
Reason: {reason}