{"record":{"id":"9c59a6b7a2c8634e","repo":"gastownhall/beads","slug":"legacy-sqlite-foreign-key-drift-in-s","errorCode":null,"errorMessage":"legacy SQLite foreign-key drift in %s","messagePattern":"legacy SQLite foreign-key drift in (.+?)","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"error","filePath":"internal/migration/legacysqlite/reader.go","lineNumber":374,"sourceCode":"\t}\n\treturn nil\n}\n\nfunc verifyFKs(ctx context.Context, db *sql.Tx, table string) error {\n\trows, err := db.QueryContext(ctx, \"PRAGMA foreign_key_list(\"+table+\")\")\n\tif err != nil {\n\t\treturn err\n\t}\n\tdefer rows.Close()\n\tcount := 0\n\tfor rows.Next() {\n\t\tvar id, seq int\n\t\tvar target, from, to, update, deleteAction, match string\n\t\tif err := rows.Scan(&id, &seq, &target, &from, &to, &update, &deleteAction, &match); err != nil {\n\t\t\treturn err\n\t\t}\n\t\tif table == \"issues\" || table == \"metadata\" || target != \"issues\" || from != \"issue_id\" || to != \"id\" || update != \"NO ACTION\" || deleteAction != \"CASCADE\" || match != \"NONE\" {\n\t\t\treturn fmt.Errorf(\"legacy SQLite foreign-key drift in %s\", table)\n\t\t}\n\t\tcount++\n\t}\n\tif err := rows.Err(); err != nil {\n\t\treturn err\n\t}\n\tif table != \"issues\" && table != \"metadata\" && count != 1 {\n\t\treturn fmt.Errorf(\"legacy SQLite foreign-key drift in %s\", table)\n\t}\n\treturn nil\n}\n\n// loadIssuesProjection is the SELECT list feeding issueops.ScanIssueFrom in\n// loadIssues. It must emit exactly the canonical issueops.IssueSelectColumns\n// prefix — columns the legacy schema lacks are projected as NULL/0 — followed\n// by the legacy trailing columns scanned via (*legacyExtras).scanDests. That\n// canonical prefix is positional (ScanIssueFrom scans it slot-for-slot), so a\n// new column in issueops.IssueSelectColumns needs a matching placeholder here;","sourceCodeStart":356,"sourceCodeEnd":392,"githubUrl":"https://github.com/gastownhall/beads/blob/71377f276968b452ee607177637970a4ff888584/internal/migration/legacysqlite/reader.go#L356-L392","documentation":"During legacy SQLite database verification, verifyFKs reads every row of PRAGMA foreign_key_list for a table and requires the foreign key to exactly match the canonical shape (target 'issues', from 'issue_id', to 'id', ON UPDATE NO ACTION, ON DELETE CASCADE, MATCH NONE), except for the 'issues' and 'metadata' tables which are exempt from this shape check. Any deviation is reported as foreign-key drift, meaning the legacy schema is not the shape the migration reader expects and proceeding could corrupt or mis-import relational data.","triggerScenarios":"Running verify (which calls verifyFKs) against a legacy SQLite beads database whose non-issues/metadata table has a foreign key with a different target table, column names, ON UPDATE/ON DELETE actions, or MATCH clause than the canonical 'issues(id)' CASCADE/NO ACTION/NONE shape.","commonSituations":"Hand-edited or externally modified legacy databases; databases created by an older or forked beads version with different FK definitions; schema-migration tools that rebuild tables and normalize or drop FK clauses; third-party SQLite editors that rewrite DDL.","solutions":["Compare the FK definition with PRAGMA foreign_key_list(<table>) and restore the canonical shape: REFERENCES issues(id) ON UPDATE NO ACTION ON DELETE CASCADE MATCH NONE with child column issue_id.","Check which beads version created the legacy DB and re-create or upgrade it with the expected schema before migrating.","If the table genuinely should not have that FK, verify whether it belongs in the exempt set (issues/metadata) or whether it is spurious and should be dropped.","Restore the legacy database from a known-good backup and retry the migration."],"exampleFix":"-- before (drifted schema)\nCREATE TABLE labels (issue_id TEXT REFERENCES issues(id) ON DELETE SET NULL);\n-- after (canonical shape)\nCREATE TABLE labels (issue_id TEXT REFERENCES issues(id) ON UPDATE NO ACTION ON DELETE CASCADE MATCH NONE);","handlingStrategy":"validation","validationCode":"rows, _ := db.Query(\"SELECT id, \"table\", \"from\", \"to\", \"on_update\", \"on_delete\", \"match\" FROM pragma_foreign_key_list(?)\", tbl)\nfor rows.Next() { /* ensure target==\"issues\", from==\"issue_id\", to==\"id\", on_update==\"NO ACTION\", on_delete==\"CASCADE\", match==\"NONE\" */ }","typeGuard":null,"tryCatchPattern":"if err := verifyFKs(db, table); err != nil {\n  if strings.Contains(err.Error(), \"foreign-key drift\") {\n    // repair or reject the legacy DB before migrating\n  }\n  return err\n}","preventionTips":["Never hand-edit legacy SQLite schemas; use the version's own migration/upgrade paths.","Run verify against a copy of the legacy DB before migrating the original.","Schema-diff the legacy DB against the canonical DDL before starting migration.","Keep third-party SQLite editors away from production legacy databases."],"tags":["sqlite","migration","schema-validation","foreign-keys"],"backgroundTag":"legacy-schema-drift","analyzedSha":"71377f276968b452ee607177637970a4ff888584","analyzedAt":"2026-08-30T18:55:39.744Z","schemaVersion":2},"datasetVersion":"2026-08-30T23:17:21.991Z"}