go-sqlite-schema-mirror-drift

Answer

Root cause (two independent bugs stacking on a fresh install):

1. isNew never true — EnsureSQLiteSchema hardcoded isNew := false, so seedSQLiteDefaults only ran in a branch that was dead code. Fresh DBs had zero rows in memory_groups, so every insert referencing a group died with SQLite extended error 787 = SQLITE_CONSTRAINT_FOREIGNKEY.
2. Schema mirror drift — the hand-written CREATE TABLE mirror diverged from the goose migrations: memory_entries listed idempotency_key in INSERTs but not in the mirror DDL; memory_groups was missing normalized_tags_json; memory_group_members had shape drift.

Fix — remove isNew entirely, make seeding unconditional and idempotent, heal drifted tables in place, and enforce mirror↔INSERT parity by test:

// sqlite.go — boot path, same function the serve handler calls
func EnsureSQLiteSchema(db *sql.DB) error {
    if _, err := db.Exec(mirrorTables); err != nil {          // CREATE TABLE IF NOT EXISTS
        return fmt.Errorf("apply schema mirror: %w", err)
    }
    // CREATE IF NOT EXISTS never alters an existing table: heal pre-drifted
    // databases by adding the columns the old mirror omitted.
    if err := ensureColumn(db, "memory_entries", "idempotency_key", "idempotency_key TEXT"); err != nil {
        return err
    }
    if err := ensureColumn(db, "memory_groups", "normalized_tags_json",
        "normalized_tags_json TEXT NOT NULL DEFAULT '[]'"); err != nil {
        return err
    }
    if _, err := db.Exec(mirrorIndexes); err != nil {          // partial unique index, after columns exist
        return fmt.Errorf("apply schema indexes: %w", err)
    }
    return seedSQLiteDefaults(db)                             // unconditional; INSERT OR IGNORE is a no-op on existing DBs
}

func seedSQLiteDefaults(db *sql.DB) error {
    _, err := db.Exec(
        `INSERT OR IGNORE INTO memory_groups (name, normalized_tags_json)
         VALUES ('default', '[]'), ('trash', '[]')`)
    return err
}

// ensureColumn is a no-op when the column exists, ALTERs when it does not.
func ensureColumn(db *sql.DB, table, column, ddl string) error {
    rows, err := db.Query(fmt.Sprintf("PRAGMA table_info(%s)", table))
    if err != nil { return fmt.Errorf("pragma table_info(%s): %w", table, err) }
    defer rows.Close()
    for rows.Next() {
        var cid, notnull, pk int
        var name, ctype string
        var dflt sql.NullString
        if err := rows.Scan(&cid, &name, &ctype, &notnull, &dflt, &pk); err != nil { return err }
        if name == column { return nil }
    }
    if err := rows.Err(); err != nil { return err }
    if _, err := db.Exec(fmt.Sprintf("ALTER TABLE %s ADD COLUMN %s", table, ddl)); err != nil {
        return fmt.Errorf("add column %s.%s: %w", table, column, err)
    }
    return nil
}

The mirror itself (order matters: tables → column migration → indexes, because a partial index cannot reference a column that doesn't exist on disk yet):

-- mirrorTables
CREATE TABLE IF NOT EXISTS memory_groups (
    id                    INTEGER PRIMARY KEY AUTOINCREMENT,
    name                  TEXT NOT NULL UNIQUE,
    normalized_tags_json  TEXT NOT NULL DEFAULT '[]',      -- was missing from mirror
    created_at            TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at            TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS memory_entries (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    group_id        INTEGER NOT NULL REFERENCES memory_groups(id) ON DELETE CASCADE,
    content         TEXT NOT NULL,
    idempotency_key TEXT,                                   -- was missing from mirror
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at      TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS memory_group_members (
    group_id   INTEGER NOT NULL REFERENCES memory_groups(id) ON DELETE CASCADE,
    member_id  INTEGER NOT NULL REFERENCES memory_entries(id) ON DELETE CASCADE,
    role       TEXT NOT NULL DEFAULT 'member',
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    PRIMARY KEY (group_id, member_id)
);
-- mirrorIndexes (applied after ensureColumn)
CREATE UNIQUE INDEX IF NOT EXISTS idx_memory_entries_idempotency_key
    ON memory_entries (idempotency_key)
    WHERE idempotency_key IS NOT NULL;                      -- NULL keys stay non-unique

Every INSERT list in sqlite.go was audited against the mirror — memory_entries (group_id, content, idempotency_key), memory_groups (name, normalized_tags_json), memory_group_members (group_id, member_id, role) — and a regex-based parity test (test 6) now fails the build if any INSERT names a column the mirror lacks.