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

PostgresIntegrationTest applies docs/ddl/postgres.sql on PostgreSQL and
checks that every join and every table possible-value reference between
the workbench's tables has a foreign key, or an exemption with a reason.

The only exemption is sql_workbench_execution.notebook_id, by design: the
execution audit is append-only and outlives its notebook. CASCADE would
delete audit rows, RESTRICT would block deleting any notebook that ever ran
(NotebookTableCustomizer deletes cells, versions, pinned results and shares,
not the audit), SET NULL would rewrite audit rows, and the engine records the
client's notebook id even when that notebook was deleted meanwhile, so the
audit insert must never fail on it.

The existing notebook foreign keys stay NO ACTION: NotebookTableCustomizer
deletes the children through QQQ (audits, locks) before the notebook.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>

docs/ddl/postgres.sql

@@ -74,6 +74,8 @@ CREATE TABLE IF NOT EXISTS sql_workbench_result_snapshot (
    CONSTRAINT pk_sql_workbench_result_snapshot PRIMARY KEY (id),
    CONSTRAINT fk_sql_workbench_result_snapshot_notebook FOREIGN KEY (notebook_id) REFERENCES sql_workbench_notebook (id)
 );
+-- the notebook page lists its snapshots, and deleting a notebook deletes them, by notebook_id
+CREATE INDEX IF NOT EXISTS ix_sql_workbench_result_snapshot_notebook ON sql_workbench_result_snapshot (notebook_id);
 
 -- Shares (1.0.0-RC.2): who, besides the owner, may read a notebook.
 -- audience_key is "user:<id>", "group:<name>" or "all" (the shared library);
@@ -133,3 +135,5 @@ CREATE TABLE IF NOT EXISTS sql_workbench_execution (
 );
 CREATE INDEX IF NOT EXISTS ix_sql_workbench_execution_user ON sql_workbench_execution (user_id, id);
 CREATE INDEX IF NOT EXISTS ix_sql_workbench_execution_start ON sql_workbench_execution (start_time);
+-- the notebook page's "Runs" list joins executions by notebook_id
+CREATE INDEX IF NOT EXISTS ix_sql_workbench_execution_notebook ON sql_workbench_execution (notebook_id);

src/test/java/com/kingsrook/qbits/sqlworkbench/PostgresIntegrationTest.java

@@ -203,6 +203,106 @@ class PostgresIntegrationTest
 
 
 
+   /*******************************************************************************
+    ** The shipped DDL gives every foreign-key column and the notebook_id the
+    ** "Runs" list joins on a leading index, so notebook pages and notebook
+    ** deletes do not scan the child tables.
+    *******************************************************************************/
+   @Test
+   void testShippedDdlIndexesNotebookChildColumns() throws Exception
+   {
+      String sql = """
+         SELECT c.conrelid::regclass::text || '.' || a.attname
+         FROM pg_constraint c
+         JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
+         WHERE c.contype = 'f' AND c.conrelid::regclass::text LIKE 'sql_workbench_%'
+           AND NOT EXISTS (SELECT 1 FROM pg_index i WHERE i.indrelid = c.conrelid AND i.indkey[0] = c.conkey[1])
+         """;
+      try(Connection connection = java.sql.DriverManager.getConnection(POSTGRES.getJdbcUrl(), POSTGRES.getUsername(), POSTGRES.getPassword());
+          Statement statement = connection.createStatement())
+      {
+         java.util.List<String> unindexed = new java.util.ArrayList<>();
+         try(java.sql.ResultSet result = statement.executeQuery(sql))
+         {
+            while(result.next())
+            {
+               unindexed.add(result.getString(1));
+            }
+         }
+         assertThat(unindexed).isEmpty();
+         try(java.sql.ResultSet result = statement.executeQuery("SELECT 1 FROM pg_indexes WHERE indexname = 'ix_sql_workbench_execution_notebook'"))
+         {
+            assertThat(result.next()).isTrue();
+         }
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Referential integrity guardrail: every join and every table possible-value
+    ** reference between the workbench's tables has a foreign key in the shipped
+    ** DDL, or an exemption with its reason. A new reference without a
+    ** constraint fails here.
+    *******************************************************************************/
+   @Test
+   void testShippedDdlHasAForeignKeyForEveryReference() throws Exception
+   {
+      ///////////////////////////////////////////////////////////////////////////
+      // "child.column" -> why it is deliberately not a foreign key           //
+      ///////////////////////////////////////////////////////////////////////////
+      Map<String, String> exempt = Map.of(
+         "sql_workbench_execution.notebook_id", "the execution audit is append-only and outlives its notebook: no CASCADE into it, "
+            + "RESTRICT would block deleting any notebook that ever ran, SET NULL would rewrite audit rows, and an execution records "
+            + "the client's notebook id even if the notebook was deleted meanwhile (the audit insert must never fail)");
+
+      java.util.Set<String> references = new java.util.TreeSet<>();
+      for(com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData table : qInstance.getTables().values())
+      {
+         for(com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldMetaData field : table.getFields().values())
+         {
+            if(field.getPossibleValueSourceName() != null && qInstance.getTable(field.getPossibleValueSourceName()) != null)
+            {
+               references.add(table.getName() + "." + field.getBackendName());
+            }
+         }
+      }
+      for(com.kingsrook.qqq.backend.core.model.metadata.joins.QJoinMetaData join : qInstance.getJoins().values())
+      {
+         ////////////////////////////////////////////////////////////////////////
+         // the referencing side is the one whose field is not its primary key //
+         ////////////////////////////////////////////////////////////////////////
+         com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData left  = qInstance.getTable(join.getLeftTable());
+         com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData right = qInstance.getTable(join.getRightTable());
+         join.getJoinOns().forEach(on -> references.add(on.getRightField().equals(right.getPrimaryKeyField())
+            ? left.getName() + "." + left.getField(on.getLeftField()).getBackendName()
+            : right.getName() + "." + right.getField(on.getRightField()).getBackendName()));
+      }
+      assertThat(references).as("the workbench's references").contains("sql_workbench_cell.notebook_id", "sql_workbench_execution.notebook_id");
+
+      java.util.Set<String> foreignKeys = new java.util.HashSet<>();
+      try(Connection connection = java.sql.DriverManager.getConnection(POSTGRES.getJdbcUrl(), POSTGRES.getUsername(), POSTGRES.getPassword());
+          Statement statement = connection.createStatement();
+          ResultSet result = statement.executeQuery("""
+             SELECT c.conrelid::regclass::text || '.' || a.attname
+             FROM pg_constraint c
+             JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
+             WHERE c.contype = 'f'
+             """))
+      {
+         while(result.next())
+         {
+            foreignKeys.add(result.getString(1));
+         }
+      }
+
+      java.util.List<String> missing = references.stream().filter(r -> !foreignKeys.contains(r) && !exempt.containsKey(r)).toList();
+      assertThat(missing).as("references without a foreign key (add one to docs/ddl/postgres.sql, or an exemption with a reason)").isEmpty();
+      assertThat(foreignKeys).as("exemptions that now have a foreign key").doesNotContainAnyElementsOf(exempt.keySet());
+   }
+
+
+
    /*******************************************************************************
     ** The database itself refuses writes in a workbench read-only session,
     ** even for SQL the classifier would never let through.

src/test/resources/sql-workbench-h2.sql

@@ -70,6 +70,8 @@ CREATE TABLE IF NOT EXISTS sql_workbench_result_snapshot (
    CONSTRAINT pk_sql_workbench_result_snapshot PRIMARY KEY (id),
    CONSTRAINT fk_sql_workbench_result_snapshot_notebook FOREIGN KEY (notebook_id) REFERENCES sql_workbench_notebook (id)
 );
+-- the notebook page lists its snapshots, and deleting a notebook deletes them, by notebook_id
+CREATE INDEX IF NOT EXISTS ix_sql_workbench_result_snapshot_notebook ON sql_workbench_result_snapshot (notebook_id);
 
 CREATE TABLE IF NOT EXISTS sql_workbench_notebook_share (
    id INTEGER AUTO_INCREMENT NOT NULL,
@@ -117,6 +119,8 @@ CREATE TABLE IF NOT EXISTS sql_workbench_execution (
 );
 CREATE INDEX IF NOT EXISTS ix_sql_workbench_execution_user ON sql_workbench_execution (user_id, id);
 CREATE INDEX IF NOT EXISTS ix_sql_workbench_execution_start ON sql_workbench_execution (start_time);
+-- the notebook page's "Runs" list joins executions by notebook_id
+CREATE INDEX IF NOT EXISTS ix_sql_workbench_execution_notebook ON sql_workbench_execution (notebook_id);
 
 CREATE TABLE person (
    id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,