go-api-delete-namespace-guard-pattern
Done. The deliverable is ~/solution.md (self-contained, 331 lines). Note: no scheduler repo was mounted in this environment, so instead of patching code I built a scratch module (/tmp/godelete) and a raw SQLite probe (/tmp/fkprobe) and empirically verified every claim before writing the doc.
SQLite probes (CLI, 92k tick rows):
- DELETE with foreign_keys=ON → FOREIGN KEY constraint failed — the NO ACTION tick blocker is real
- PRAGMA foreign_keys=OFF inside a transaction → silent no-op (prints 1, still ON) — the purge window must run in autocommit
- Wrong order (DELETE first under FK-off) → dangling members: 1 — proves SET NULL is skipped while OFF, hence "unassign before the window"
- Soft delete (retained row) → members still point at it (1) — retained row never fires SET NULL; explicit UPDATE required
- FK enforcement restored after the window → subsequent bare delete blocked again (no pool leak)
Go integration test (httptest, go-sqlite3, go vet/gofmt clean) — 6/6 pass:
- 404-first on unknown id, 400 without confirm=true, 409 listing ["p-1"]
- Soft delete: 204, row retained with deleted_at, 0 members
- Purge: 204 through the FK-off window with 92k ticks, 0 dangling members; FK back ON afterwards
DELETE /api/v1/namespaces/{id} with guards: 404-first existence → confirm gate (400) → 409 listing enabled member projects → purge/soft branch (DOGFOOD-009 pattern).UPDATE projects SET namespace_id=NULL in one tx — never rely on ON DELETE SET NULL for a retained row.*sql.Conn (PRAGMA is per-connection — pooled db.Exec would silently no-op), unassign members before PRAGMA foreign_keys=OFF, DELETE, then deferred restore of FK, plus _foreign_keys=on DSN hardening.Two extra insights the tests surfaced: the :memory: per-connection trap in Go tests (switched to temp-file DB), and the fact that your first instinct to purge ns-1 gets a legit 409 — the enabled member must be disabled first, which is exactly how the guard should behave.
# Evidence - Problem class: go-api-delete-namespace-guard-pattern - Model: openrouter/deepseek/deepseek-v4-flash-0731 - Solved: 2026-09-03T03:51:29.229Z - Verification: solution produced by pi in sandbox; see signatures.json
{"description": "Exposed DELETE /api/v1/namespaces/{id} in the Go scheduler: DB soft-delete existed but the HTTP layer 405d. Mirror the DOGFOOD-009 project-delete guard pattern: 404-first existence check, confirm=true gate (400), 409 listing enabled member projects, purge=true hard delete with PRAGMA foreign_keys=OFF around the DELETE (namespace_ticks FK NO ACTION + 92k rows blocks it otherwise; PurgeProject precedent). Soft delete must explicitly UPDATE projects SET namespace_id=NULL \u2014 a retained row never fires ON DELETE SET NULL. Unassign members BEFORE the FK-disable window (SET NULL is skipped while foreign_keys=OFF).", "environment": "", "language": "", "model": "openrouter/deepseek/deepseek-v4-flash-0731", "problem_class": "go-api-delete-namespace-guard-pattern", "provider": "openrouter", "solved_at": "2026-09-03T03:51:29.229Z", "version": ""}Done. The deliverable is ~/solution.md (self-contained, 331 lines). Note: no scheduler repo was mounted in this environment, so instead of patching code I built a scratch module (/tmp/godelete) and a raw SQLite probe (/tmp/fkprobe) and empirically verified every claim before writing the doc.
SQLite probes (CLI, 92k tick rows):
- DELETE with foreign_keys=ON → FOREIGN KEY constraint failed — the NO ACTION tick blocker is real
- PRAGMA foreign_keys=OFF inside a transaction → silent no-op (prints 1, still ON) — the purge window must run in autocommit
- Wrong order (DELETE first under FK-off) → dangling members: 1 — proves SET NULL is skipped while OFF, hence "unassign before the window"
- Soft delete (retained row) → members still point at it (1) — retained row never fires SET NULL; explicit UPDATE required
- FK enforcement restored after the window → subsequent bare delete blocked again (no pool leak)
Go integration test (httptest, go-sqlite3, go vet/gofmt clean) — 6/6 pass:
- 404-first on unknown id, 400 without confirm=true, 409 listing ["p-1"]
- Soft delete: 204, row retained with deleted_at, 0 members
- Purge: 204 through the FK-off window with 92k ticks, 0 dangling members; FK back ON afterwards
DELETE /api/v1/namespaces/{id} with guards: 404-first existence → confirm gate (400) → 409 listing enabled member projects → purge/soft branch (DOGFOOD-009 pattern).UPDATE projects SET namespace_id=NULL in one tx — never rely on ON DELETE SET NULL for a retained row.*sql.Conn (PRAGMA is per-connection — pooled db.Exec would silently no-op), unassign members before PRAGMA foreign_keys=OFF, DELETE, then deferred restore of FK, plus _foreign_keys=on DSN hardening.Two extra insights the tests surfaced: the :memory: per-connection trap in Go tests (switched to temp-file DB), and the fact that your first instinct to purge ns-1 gets a legit 409 — the enabled member must be disabled first, which is exactly how the guard should behave.
# Evidence - Problem class: go-api-delete-namespace-guard-pattern - Model: openrouter/deepseek/deepseek-v4-flash-0731 - Solved: 2026-09-03T03:51:29.229Z - Verification: solution produced by pi in sandbox; see signatures.json
{"description": "Exposed DELETE /api/v1/namespaces/{id} in the Go scheduler: DB soft-delete existed but the HTTP layer 405d. Mirror the DOGFOOD-009 project-delete guard pattern: 404-first existence check, confirm=true gate (400), 409 listing enabled member projects, purge=true hard delete with PRAGMA foreign_keys=OFF around the DELETE (namespace_ticks FK NO ACTION + 92k rows blocks it otherwise; PurgeProject precedent). Soft delete must explicitly UPDATE projects SET namespace_id=NULL \u2014 a retained row never fires ON DELETE SET NULL. Unassign members BEFORE the FK-disable window (SET NULL is skipped while foreign_keys=OFF).", "environment": "", "language": "", "model": "openrouter/deepseek/deepseek-v4-flash-0731", "problem_class": "go-api-delete-namespace-guard-pattern", "provider": "openrouter", "solved_at": "2026-09-03T03:51:29.229Z", "version": ""}