{"record":{"id":"79413c5d8f5fe758","repo":"gastownhall/beads","slug":"legacy-sqlite-schema-drift-in-s","errorCode":null,"errorMessage":"legacy SQLite schema drift in %s","messagePattern":"legacy SQLite schema drift in (.+?)","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"error","filePath":"internal/migration/legacysqlite/reader.go","lineNumber":355,"sourceCode":"\t\tvar notNull, pk int\n\t\tvar defaultValue sql.NullString\n\t\tif err := rows.Scan(&cid, &name, &typ, &notNull, &defaultValue, &pk, &hidden); err != nil {\n\t\t\treturn err\n\t\t}\n\t\tif hidden != 0 {\n\t\t\treturn fmt.Errorf(\"legacy SQLite schema drift in %s hidden column\", table)\n\t\t}\n\t\tdefaultText := \"-\"\n\t\tif defaultValue.Valid {\n\t\t\tdefaultText = defaultValue.String\n\t\t}\n\t\tgot = append(got, fmt.Sprintf(\"%s|%s|%d|%s|%d\", name, typ, notNull, defaultText, pk))\n\t}\n\tif err := rows.Err(); err != nil {\n\t\treturn err\n\t}\n\tif strings.Join(got, \" \") != want {\n\t\treturn fmt.Errorf(\"legacy SQLite schema drift in %s\", table)\n\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\" {","sourceCodeStart":337,"sourceCodeEnd":373,"githubUrl":"https://github.com/gastownhall/beads/blob/71377f276968b452ee607177637970a4ff888584/internal/migration/legacysqlite/reader.go#L337-L373","documentation":"verifyTable builds a canonical description of each table's columns — name|type|notNull|default|pk joined per row — and compares the joined string to an exact expected string from the package's schema map. Any difference in column names, order, types, nullability, defaults, or primary-key flags produces 'legacy SQLite schema drift in <table>'. This enforces the package's promise to migrate only exact, audited layouts.","triggerScenarios":"Export -> read -> verify -> verifyTable: strings.Join(got, \" \") != want for one of metadata, issues, dependencies, labels, comments. Any added/dropped/reordered column, changed type or NOT NULL/default/PK attribute triggers it.","commonSituations":"Migrating a database created by a different bd release whose schema differed; manual ALTER TABLE / column additions; ORM or migration tools that touched the legacy database; reproducing the schema by hand with slightly different types or defaults.","solutions":["Diff your schema against the expected one: run PRAGMA table_xinfo(<table>) for each table and compare column-by-column (name, type, notnull, dflt_value, pk, order)","Restore the database from a backup created by the supported bd release","Rebuild the drifted table with the exact legacy schema and copy the rows across, then retry","Use a bd build that accepts your database's actual legacy version (see the bd_version check) instead of hand-adjusting the schema"],"exampleFix":"-- find the drift\nsqlite3 beads.db \"PRAGMA table_xinfo(dependencies);\"\n-- e.g. shows an extra column 'thread_id TEXT' added by a tool\n-- fix: rebuild without it\nsqlite3 beads.db \"CREATE TABLE dependencies_new (issue_id TEXT NOT NULL, depends_on_id TEXT NOT NULL, type TEXT NOT NULL DEFAULT 'blocks', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, created_by TEXT NOT NULL, metadata TEXT, thread_id TEXT, PRIMARY KEY(issue_id, depends_on_id, type)); INSERT INTO dependencies_new SELECT issue_id,depends_on_id,type,created_at,created_by,metadata,thread_id FROM dependencies; DROP TABLE dependencies; ALTER TABLE dependencies_new RENAME TO dependencies;\"","handlingStrategy":"validation","validationCode":"// pre-flight: compare each table's shape to the expected legacy contract\nfunc checkSchemaShape(dbPath string) error {\n\tdb, err := sql.Open(\"sqlite3\", dbPath+\"?mode=ro\"); if err != nil { return err }\n\tdefer db.Close()\n\tfor _, t := range []string{\"metadata\",\"issues\",\"dependencies\",\"labels\",\"comments\"} {\n\t\trows, err := db.Query(\"PRAGMA table_xinfo(\" + t + \")\"); if err != nil { return err }\n\t\tvar cols []string\n\t\tfor rows.Next() {\n\t\t\tvar cid, hidden, nn, pk int; var name, typ string; var d sql.NullString\n\t\t\tif err := rows.Scan(&cid, &name, &typ, &nn, &d, &pk, &hidden); err != nil { rows.Close(); return err }\n\t\t\tcols = append(cols, name+\"|\"+typ)\n\t\t}\n\t\trows.Close()\n\t\tfmt.Printf(\"%s: %s\\n\", t, strings.Join(cols, \" \")) // diff this against the documented schema\n\t}\n\treturn nil\n}","typeGuard":null,"tryCatchPattern":"if err := legacysqlite.Export(ctx, src, out, os.Stdout); err != nil {\n\tif strings.Contains(err.Error(), \"schema drift in\") && !strings.Contains(err.Error(), \"hidden column\") {\n\t\treturn fmt.Errorf(\"table layout differs from the supported legacy contract; restore from a backup or rebuild the table with the canonical schema\")\n\t}\n\treturn err\n}","preventionTips":["Don't ALTER TABLE legacy databases that will be migrated; migrate first, change later","Create databases only with the supported bd release so the schema matches the audited contract","Keep backups taken by the original release for restoration if drift appears","Compare PRAGMA table_info output against the documented schema before scheduling migration"],"tags":["sqlite","migration","schema","drift"],"backgroundTag":"schema-drift","analyzedSha":"71377f276968b452ee607177637970a4ff888584","analyzedAt":"2026-08-30T18:55:39.744Z","schemaVersion":2},"datasetVersion":"2026-08-30T23:17:21.991Z"}