test(ddl): guardrail that every table reference has a foreign key #24

2026-10-14T21:22:58Z

KofTwentyTwo commented

2026-10-14 21:22:58 +00:00

(Migrated from github.com)

Summary

Referential-integrity audit of the workbench schema. No DDL change is needed: the four notebook children (cell, version, pinned result, share) already have foreign keys to sql_workbench_notebook. This adds a guardrail so a new reference cannot ship without one.

Stacked on #23 (branched from feature/schema-notebook-indexes), so the diff includes #23's index commit; merge #23 first.

PostgresIntegrationTest.testShippedDdlHasAForeignKeyForEveryReference: applies docs/ddl/postgres.sql on PostgreSQL (Testcontainers) and asserts every join and table possible-value reference between the workbench's tables has a foreign key, or an exemption with a reason. It also fails if an exempt reference gains a key, so the list stays accurate.
The only exemption is sql_workbench_execution.notebook_id, by design: the execution audit is append-only (AppendOnlyTableCustomizer) and outlives its notebook. CASCADE would delete audit rows. RESTRICT would block deleting any notebook that ever ran, because NotebookTableCustomizer.preDelete deletes cells, versions, pinned results and shares but not the audit. SET NULL would rewrite audit rows. The engine also records the client-supplied notebook id (SqlExecutionEngine.newAudit), which may name a notebook deleted meanwhile, and the audit insert must never fail.
The existing notebook FKs stay NO ACTION: the customizer deletes the children through QQQ (audits and locks apply) before it deletes the notebook.

Tests

Red: with fk_sql_workbench_cell_notebook removed from the DDL, the guardrail reports sql_workbench_cell.notebook_id. Green with the shipped DDL.
./mvnw verify green locally.

🤖 Generated with Claude Code

## Summary Referential-integrity audit of the workbench schema. No DDL change is needed: the four notebook children (cell, version, pinned result, share) already have foreign keys to `sql_workbench_notebook`. This adds a guardrail so a new reference cannot ship without one. **Stacked on #23** (branched from `feature/schema-notebook-indexes`), so the diff includes #23's index commit; merge #23 first. - `PostgresIntegrationTest.testShippedDdlHasAForeignKeyForEveryReference`: applies `docs/ddl/postgres.sql` on PostgreSQL (Testcontainers) and asserts every join and table possible-value reference between the workbench's tables has a foreign key, or an exemption with a reason. It also fails if an exempt reference gains a key, so the list stays accurate. - The only exemption is `sql_workbench_execution.notebook_id`, by design: the execution audit is append-only (`AppendOnlyTableCustomizer`) and outlives its notebook. CASCADE would delete audit rows. RESTRICT would block deleting any notebook that ever ran, because `NotebookTableCustomizer.preDelete` deletes cells, versions, pinned results and shares but not the audit. SET NULL would rewrite audit rows. The engine also records the client-supplied notebook id (`SqlExecutionEngine.newAudit`), which may name a notebook deleted meanwhile, and the audit insert must never fail. - The existing notebook FKs stay NO ACTION: the customizer deletes the children through QQQ (audits and locks apply) before it deletes the notebook. ## Tests - Red: with `fk_sql_workbench_cell_notebook` removed from the DDL, the guardrail reports `sql_workbench_cell.notebook_id`. Green with the shipped DDL. - `./mvnw verify` green locally. 🤖 Generated with [Claude Code](https://claude.com/claude-code)

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: QRun-IO/qbit-sql-workbench#24