gastownhall/beads · error

legacy SQLite foreign-key drift in %s

Error message

legacy SQLite foreign-key drift in %s

What it means

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.

Source

Thrown at internal/migration/legacysqlite/reader.go:374

	}
	return nil
}

func verifyFKs(ctx context.Context, db *sql.Tx, table string) error {
	rows, err := db.QueryContext(ctx, "PRAGMA foreign_key_list("+table+")")
	if err != nil {
		return err
	}
	defer rows.Close()
	count := 0
	for rows.Next() {
		var id, seq int
		var target, from, to, update, deleteAction, match string
		if err := rows.Scan(&id, &seq, &target, &from, &to, &update, &deleteAction, &match); err != nil {
			return err
		}
		if table == "issues" || table == "metadata" || target != "issues" || from != "issue_id" || to != "id" || update != "NO ACTION" || deleteAction != "CASCADE" || match != "NONE" {
			return fmt.Errorf("legacy SQLite foreign-key drift in %s", table)
		}
		count++
	}
	if err := rows.Err(); err != nil {
		return err
	}
	if table != "issues" && table != "metadata" && count != 1 {
		return fmt.Errorf("legacy SQLite foreign-key drift in %s", table)
	}
	return nil
}

// loadIssuesProjection is the SELECT list feeding issueops.ScanIssueFrom in
// loadIssues. It must emit exactly the canonical issueops.IssueSelectColumns
// prefix — columns the legacy schema lacks are projected as NULL/0 — followed
// by the legacy trailing columns scanned via (*legacyExtras).scanDests. That
// canonical prefix is positional (ScanIssueFrom scans it slot-for-slot), so a
// new column in issueops.IssueSelectColumns needs a matching placeholder here;

View on GitHub (pinned to 71377f2769)

Solutions

  1. 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.
  2. Check which beads version created the legacy DB and re-create or upgrade it with the expected schema before migrating.
  3. 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.
  4. Restore the legacy database from a known-good backup and retry the migration.

Example fix

-- before (drifted schema)
CREATE TABLE labels (issue_id TEXT REFERENCES issues(id) ON DELETE SET NULL);
-- after (canonical shape)
CREATE TABLE labels (issue_id TEXT REFERENCES issues(id) ON UPDATE NO ACTION ON DELETE CASCADE MATCH NONE);
Defensive patterns

Strategy: validation

Validate before calling

rows, _ := db.Query("SELECT id, "table", "from", "to", "on_update", "on_delete", "match" FROM pragma_foreign_key_list(?)", tbl)
for rows.Next() { /* ensure target=="issues", from=="issue_id", to=="id", on_update=="NO ACTION", on_delete=="CASCADE", match=="NONE" */ }

Try / catch

if err := verifyFKs(db, table); err != nil {
  if strings.Contains(err.Error(), "foreign-key drift") {
    // repair or reject the legacy DB before migrating
  }
  return err
}

Prevention

When it happens

Trigger: 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.

Common situations: 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.

Related errors

AI-assisted analysis of gastownhall/beads@71377f2769 (2026-08-30). Data as JSON: /api/errors/9c59a6b7a2c8634e. Report an issue: GitHub.