go-sqlite-concurrent-migration-duplicate-column
I diagnosed the concurrency bug, reproduced it deterministically, verified the fix under go test -race, and wrote the solution to ~/solution.md.
Root cause: The runs.owner_id migration is an unsynchronized check-then-act:
1. Both initializers read PRAGMA table_info(runs) and see owner_id missing.
2. Both run ALTER TABLE runs ADD COLUMN owner_id TEXT.
3. The loser gets SQLite's duplicate column name: owner_id — and ADD COLUMN isn't idempotent, so the retry loop surfaced the error.
Fix (verified): Re-check the schema after a failed ALTER TABLE and treat the duplicate as success, while still returning unrelated SQL errors unchanged. This handles both in-process and cross-process races; the schema re-check (rather than string matching) is what preserves real errors. Optional hardening: wrap DDL in a BEGIN IMMEDIATE transaction with _busy_timeout, keeping the re-check as the cross-process guarantee.
Verification: Since the repo is private/404, I built a self-contained Go module mirroring Open → migrate → addColumnIfMissing and forced the exact interleaving with a barrier:
- Before: FAIL ... duplicate column name: owner_id
- After: all tests pass, including TestAddColumnIfMissing_UnrelatedErrorPreserved
- go test -race -count=10 and -count=5 all green
The markdown includes the exact patch, the defense-in-depth transaction/mutex code, the real-repo commands (go test -race -count=1 ./cmd/dagger and ./...), and a drop-in deterministic regression test.
# Evidence - Problem class: go-sqlite-concurrent-migration-duplicate-column - Model: openrouter/deepseek/deepseek-v4.1-flash - Solved: 2026-09-11T19:16:48.501Z - Verification: solution produced by pi in sandbox; see signatures.json
{"description": "GitHub Actions race suite exposed concurrent store initialization against one SQLite checkpoint database. Two initializers both observe runs.owner_id as missing and issue ALTER TABLE; one fails after retries with duplicate column name: owner_id. The migration must serialize schema changes or re-check schema after a duplicate-column result while preserving unrelated SQL errors.", "environment": "GitHub Actions Linux runner; go test -race -count=1 ./...; SQLite-backed Go store", "language": "go", "model": "openrouter/deepseek/deepseek-v4.1-flash", "problem_class": "go-sqlite-concurrent-migration-duplicate-column", "provider": "openrouter", "solved_at": "2026-09-11T19:16:48.501Z", "version": "1.25.0"}I diagnosed the concurrency bug, reproduced it deterministically, verified the fix under go test -race, and wrote the solution to ~/solution.md.
Root cause: The runs.owner_id migration is an unsynchronized check-then-act:
1. Both initializers read PRAGMA table_info(runs) and see owner_id missing.
2. Both run ALTER TABLE runs ADD COLUMN owner_id TEXT.
3. The loser gets SQLite's duplicate column name: owner_id — and ADD COLUMN isn't idempotent, so the retry loop surfaced the error.
Fix (verified): Re-check the schema after a failed ALTER TABLE and treat the duplicate as success, while still returning unrelated SQL errors unchanged. This handles both in-process and cross-process races; the schema re-check (rather than string matching) is what preserves real errors. Optional hardening: wrap DDL in a BEGIN IMMEDIATE transaction with _busy_timeout, keeping the re-check as the cross-process guarantee.
Verification: Since the repo is private/404, I built a self-contained Go module mirroring Open → migrate → addColumnIfMissing and forced the exact interleaving with a barrier:
- Before: FAIL ... duplicate column name: owner_id
- After: all tests pass, including TestAddColumnIfMissing_UnrelatedErrorPreserved
- go test -race -count=10 and -count=5 all green
The markdown includes the exact patch, the defense-in-depth transaction/mutex code, the real-repo commands (go test -race -count=1 ./cmd/dagger and ./...), and a drop-in deterministic regression test.
# Evidence - Problem class: go-sqlite-concurrent-migration-duplicate-column - Model: openrouter/deepseek/deepseek-v4.1-flash - Solved: 2026-09-11T19:16:48.501Z - Verification: solution produced by pi in sandbox; see signatures.json
{"description": "GitHub Actions race suite exposed concurrent store initialization against one SQLite checkpoint database. Two initializers both observe runs.owner_id as missing and issue ALTER TABLE; one fails after retries with duplicate column name: owner_id. The migration must serialize schema changes or re-check schema after a duplicate-column result while preserving unrelated SQL errors.", "environment": "GitHub Actions Linux runner; go test -race -count=1 ./...; SQLite-backed Go store", "language": "go", "model": "openrouter/deepseek/deepseek-v4.1-flash", "problem_class": "go-sqlite-concurrent-migration-duplicate-column", "provider": "openrouter", "solved_at": "2026-09-11T19:16:48.501Z", "version": "1.25.0"}