feat(schema): foreign keys on every relation between the qbit tables

ObjectStorageSchema now declares a foreign key for each join and table
possible value (fk_<table>_<column>): capabilities, folders, objects,
versions, references and usage cascade from their bucket or object;
object.folder_id and object_reference.object_id are set null; a bucket's
connection, its header and protection rules and the operation log
restrict. Each key is added by an idempotent DO block that first records
orphans in qqq_fk_quarantine and clears (nullable) or deletes (required)
them, so the same statements upgrade an existing schema. Three new
indexes back keys that had none.

PostgresIndexTest gains the guardrail (every relation has a database
foreign key with its rule, every key column leads an index), the
upgrade path with orphans run twice, and the delete paths the scan uses.

Builds on #19 (feature/object-id-indexes).

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

.circleci/config.yml

@@ -1,7 +1,7 @@
 version: 2.1
 
 orbs:
-   qqq-orb: kingsrook/qqq-orb@0.6.8
+   qqq-orb: kingsrook/qqq-orb@0.6.9
 
 commands:
    configure-package-install:

CHANGELOG.md

@@ -5,6 +5,7 @@ All notable changes to this project are documented here. The format follows Keep
 ## [0.1.0] - Unreleased
 
 ### Added
+- `StorageBucket.maxUploadBytes()` in the `api` package: the bucket's upload limit in bytes (null when none), so other qbits need not call `config()`, which returns an internal type.
 - **The service (P1).** `ObjectStorage.bucket(alias)` and `ObjectStorage.asSystem(caller, runId)`: put (multipart above the part size), get with ranges, head, copy (in parts for very large objects), header and metadata changes, delete (delete marker, private trash, or permanent), restore, list, presigned GET, PUT and POST, public URLs, tags. Key building and URL encoding, the header policy (`object_storage_header_rule`), `PublicUrlResolver`, `StorageChangeListener`, `StorageReferenceProvider`, `StorageAlertSink`.
 - Configuration in code, upserted into visible `object_storage_*` tables; secret references with an allow-list; per-connection rate limits; per-bucket and per-connection record security locks.
 - The capability model and the Test connection probe (read-only by default, write tests on request, clock skew, presign host), with seeded provider defaults.
@@ -13,6 +14,7 @@ All notable changes to this project are documented here. The format follows Keep
 - **The index (P2).** `object_storage_object` and `object_storage_folder` kept in step with each indexed bucket: resumable full scan with a mass-removal safety stop, HEAD backlog, write-through on every service write, soft-deleted rows, folder rollups. Orphaned and missing objects from `FieldReferenceProvider`, daily usage snapshots, quota and freshness alerts, the nightly job.
 - **The Storage app (P3, P4).** Admin -> Storage with a responsive home; record screens with sections, child lists and a thumbnail preview; actions Rescan bucket, Check configuration, Refresh from bucket, Apply header policy (replaces app-specific backfills), Change headers and metadata, Edit tags, Copy, Move or rename, Delete, Restore, Create download link, Create upload link, folder rename and delete, Upload files, Test connection; every one a standard Review and Result with a bulk cap.
 - `ObjectStorageSchema`: PostgreSQL DDL generated from the table metadata.
+- Foreign keys on every relation between the qbit tables (`fk_<table>_<column>`), generated by `ObjectStorageSchema`: owned observed state (capabilities, folders, objects, versions, usage) cascades from its bucket or object, `object.folder_id` and `object_reference.object_id` are set null, and a bucket's connection, header and protection rules and operation log restrict. Each key is added by an idempotent block that first records rows whose parent is gone in `qqq_fk_quarantine` and clears (nullable column) or deletes (required column) them, so it also upgrades an existing schema. New indexes `ix_object_storage_bucket_1` (connection_id), `ix_object_storage_header_rule_1` and `ix_object_storage_protection_rule_1` (bucket_id) back the keys. Upgrade: run the generated statements again (or the host's copy of them).
 - Conformance suite against MinIO and Garage (Testcontainers), service, index and action tests, and the index and actions on PostgreSQL.
 
 ### Changed

pom.xml

@@ -18,6 +18,8 @@
 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 
    <properties>
+      <qbit.guardrails.enforce>true</qbit.guardrails.enforce>
+      <qbit.guardrails.strictUiApiRoutes>true</qbit.guardrails.strictUiApiRoutes>
       <revision>0.1.0-SNAPSHOT</revision>
       <maven.compiler.source>21</maven.compiler.source>
       <maven.compiler.target>21</maven.compiler.target>
@@ -27,7 +29,7 @@
    <parent>
       <groupId>com.kingsrook</groupId>
       <artifactId>qbit-build-parent</artifactId>
-      <version>2.0.0</version>
+      <version>2.1.0</version>
    </parent>
 
    <groupId>com.kingsrook.qbits</groupId>
@@ -246,7 +248,7 @@
             <activeByDefault>true</activeByDefault>
          </activation>
          <properties>
-            <qqq.snapshot.version>4.1.0-RC.5</qqq.snapshot.version>
+            <qqq.snapshot.version>4.1.0-RC.6</qqq.snapshot.version>
          </properties>
          <dependencyManagement>
             <dependencies>

src/main/java/com/kingsrook/qbits/objectstorage/api/StorageBucket.java

@@ -128,6 +128,18 @@ public final class StorageBucket
 
 
 
+   /*******************************************************************************
+    ** The largest object this bucket accepts on upload, in bytes, or null when
+    ** the bucket sets no limit.  Callers outside this qbit use this instead of
+    ** config(), which returns an internal type.
+    *******************************************************************************/
+   public Long maxUploadBytes()
+   {
+      return (bucket.getMaxUploadBytes());
+   }
+
+
+
    /*******************************************************************************
     ** The bucket configuration.
     *******************************************************************************/

src/main/java/com/kingsrook/qbits/objectstorage/model/ObjectStorageSchema.java

@@ -38,7 +38,9 @@ import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
  **
  ** The statements are idempotent (CREATE ... IF NOT EXISTS) and include the
  ** unique constraints and the browse indexes the design calls for
- ** ((bucket, key) unique; bucket with folder, last modified, size, content type).
+ ** ((bucket, key) unique; bucket with folder, last modified, size, content type),
+ ** then the foreign keys, each in a block that also upgrades an existing schema
+ ** (orphans recorded in qqq_fk_quarantine first; see addForeignKey).
  *******************************************************************************/
 public final class ObjectStorageSchema
 {
@@ -52,14 +54,79 @@ public final class ObjectStorageSchema
       ObjectStorageTables.OBJECT, List.of(List.of("bucketId", "objectKey")),
       ObjectStorageTables.USAGE_DAILY, List.of(List.of("bucketId", "snapshotDate")));
 
-   /** Browse and lookup indexes: table to column lists. */
+   /** Browse and lookup indexes: table to column lists. New entries go at the end of
+    ** a table's list, so the generated names (ix_<table>_<n>) of existing ones never change.
+    ** The child lists on record pages (folder -> objects, object -> versions and references)
+    ** filter on the parent id alone, so those columns lead an index of their own. */
    private static final Map<String, List<List<String>>> INDEXES = Map.of(
       ObjectStorageTables.OBJECT, List.of(List.of("bucketId", "folderId"), List.of("bucketId", "lastModified"), List.of("bucketId", "size"), List.of("bucketId", "contentType"),
-         List.of("bucketId", "deleted", "referenceCount"), List.of("bucketId", "headersKnown", "deleted")),
-      ObjectStorageTables.OBJECT_REFERENCE, List.of(List.of("bucketId", "objectId"), List.of("bucketId", "missing")),
+         List.of("bucketId", "deleted", "referenceCount"), List.of("bucketId", "headersKnown", "deleted"), List.of("folderId")),
+      ObjectStorageTables.OBJECT_REFERENCE, List.of(List.of("bucketId", "objectId"), List.of("bucketId", "missing"), List.of("objectId")),
       ObjectStorageTables.OPERATION, List.of(List.of("bucketId", "occurredAt"), List.of("objectKey", "occurredAt")),
       ObjectStorageTables.FOLDER, List.of(List.of("parentFolderId")),
-      ObjectStorageTables.OBJECT_VERSION, List.of(List.of("bucketId", "objectKey")));
+      ObjectStorageTables.OBJECT_VERSION, List.of(List.of("bucketId", "objectKey"), List.of("objectId")),
+      ObjectStorageTables.BUCKET, List.of(List.of("connectionId")),
+      ObjectStorageTables.HEADER_RULE, List.of(List.of("bucketId")),
+      ObjectStorageTables.PROTECTION_RULE, List.of(List.of("bucketId")));
+
+   /** The table that records the rows an upgrade changed so a foreign key could be added
+    ** (identical DDL in every qbit, so whichever runs first creates it). */
+   public static final String QUARANTINE_DDL = "CREATE TABLE IF NOT EXISTS qqq_fk_quarantine (id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, "
+      + "table_name VARCHAR(100) NOT NULL, column_name VARCHAR(100) NOT NULL, row_id VARCHAR(100), missing_parent_id VARCHAR(100), action VARCHAR(20) NOT NULL, "
+      + "row_data TEXT, changeset VARCHAR(200), quarantined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";
+
+   /** Every parent/child relation, enforced in the database. The qbit never deletes a
+    ** connection, bucket or folder row (ConfigSync only upserts; folders are only
+    ** created), and its only deletes are object rows (scan removal, trash retention,
+    ** hard-delete write-through) and reference rows (rebuilt by each reference scan),
+    ** through QQQ deletes with no associations. So:
+    ** - observed state that belongs to a parent (capabilities, folders, objects, versions,
+    **   usage) CASCADEs: an object delete takes its versions, and an admin deleting a
+    **   bucket row takes its index with it;
+    ** - optional pointers into the index (object.folderId, reference.objectId) SET NULL:
+    **   the next scan sets the folder again, and a reference to a removed object is
+    **   missing until the next reference scan rebuilds the rows;
+    ** - admin-authored config (header and protection rules), the operation audit log and
+    **   a bucket's connection RESTRICT (NO ACTION): deleting a bucket row that has rules
+    **   or history, or a connection that buckets use, is refused rather than silently
+    **   dropping protection or audit rows (ConfigSync would re-create a configured bucket
+    **   under a new id, with its rules gone).
+    ** Inserts are parents first everywhere (connections before buckets, a folder's parent
+    ** before the folder, folders before objects), so nothing needs to be deferred.
+    ** New entries go at the end. */
+   private static final List<ForeignKey> FOREIGN_KEYS = List.of(
+      new ForeignKey(ObjectStorageTables.BUCKET, "connectionId", ObjectStorageTables.CONNECTION, "NO ACTION"),
+      new ForeignKey(ObjectStorageTables.CONNECTION_CAPABILITY, "connectionId", ObjectStorageTables.CONNECTION, "CASCADE"),
+      new ForeignKey(ObjectStorageTables.BUCKET_CAPABILITY, "bucketId", ObjectStorageTables.BUCKET, "CASCADE"),
+      new ForeignKey(ObjectStorageTables.HEADER_RULE, "bucketId", ObjectStorageTables.BUCKET, "NO ACTION"),
+      new ForeignKey(ObjectStorageTables.PROTECTION_RULE, "bucketId", ObjectStorageTables.BUCKET, "NO ACTION"),
+      new ForeignKey(ObjectStorageTables.OPERATION, "bucketId", ObjectStorageTables.BUCKET, "NO ACTION"),
+      new ForeignKey(ObjectStorageTables.USAGE_DAILY, "bucketId", ObjectStorageTables.BUCKET, "CASCADE"),
+      new ForeignKey(ObjectStorageTables.FOLDER, "bucketId", ObjectStorageTables.BUCKET, "CASCADE"),
+      new ForeignKey(ObjectStorageTables.FOLDER, "parentFolderId", ObjectStorageTables.FOLDER, "CASCADE"),
+      new ForeignKey(ObjectStorageTables.OBJECT, "bucketId", ObjectStorageTables.BUCKET, "CASCADE"),
+      new ForeignKey(ObjectStorageTables.OBJECT, "folderId", ObjectStorageTables.FOLDER, "SET NULL"),
+      new ForeignKey(ObjectStorageTables.OBJECT_VERSION, "bucketId", ObjectStorageTables.BUCKET, "CASCADE"),
+      new ForeignKey(ObjectStorageTables.OBJECT_VERSION, "objectId", ObjectStorageTables.OBJECT, "CASCADE"),
+      new ForeignKey(ObjectStorageTables.OBJECT_REFERENCE, "bucketId", ObjectStorageTables.BUCKET, "CASCADE"),
+      new ForeignKey(ObjectStorageTables.OBJECT_REFERENCE, "objectId", ObjectStorageTables.OBJECT, "SET NULL"));
+
+
+
+   /*******************************************************************************
+    ** One foreign key: the child table and field, the parent table (always its id)
+    ** and the ON DELETE rule.
+    *******************************************************************************/
+   public record ForeignKey(String table, String field, String parent, String onDelete)
+   {
+      /*******************************************************************************
+       ** The constraint name: fk_<child table>_<child column>.
+       *******************************************************************************/
+      public String name()
+      {
+         return ("fk_" + table + "_" + column(field));
+      }
+   }
 
 
 
@@ -99,11 +166,56 @@ public final class ObjectStorageSchema
             }
          }
       }
+      statements.add(QUARANTINE_DDL);
+      for(ForeignKey fk : FOREIGN_KEYS)
+      {
+         statements.add(addForeignKey(fk, tables));
+      }
       return (statements);
    }
 
 
 
+   /*******************************************************************************
+    ** The foreign keys the schema declares.
+    *******************************************************************************/
+   public static List<ForeignKey> foreignKeys()
+   {
+      return (FOREIGN_KEYS);
+   }
+
+
+
+   /*******************************************************************************
+    ** One idempotent block that adds a foreign key and upgrades an existing schema
+    ** safely: rows whose parent is gone are first recorded in qqq_fk_quarantine, then
+    ** their reference is cleared (nullable column) or the row is deleted (required
+    ** column; the whole row is recorded), so adding the constraint cannot fail. A
+    ** no-op once the constraint exists.
+    *******************************************************************************/
+   private static String addForeignKey(ForeignKey fk, List<QTableMetaData> tables)
+   {
+      String  col      = column(fk.field());
+      boolean required = tables.stream().filter(t -> t.getName().equals(fk.table())).findFirst()
+         .map(t -> Boolean.TRUE.equals(t.getField(fk.field()).getIsRequired())).orElse(false);
+      String  orphans  = "c." + col + " IS NOT NULL AND NOT EXISTS (SELECT 1 FROM " + fk.parent() + " p WHERE p.id = c." + col + ")";
+      return ("DO $$\n"
+         + "BEGIN\n"
+         + "   IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = '" + fk.name() + "' AND conrelid = '" + fk.table() + "'::regclass) THEN\n"
+         + "      INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset)\n"
+         + "         SELECT '" + fk.table() + "', '" + col + "', c.id::text, c." + col + "::text, '" + (required ? "DELETED" : "NULLED") + "', row_to_json(c)::text, "
+         + "'qbit-object-storage ObjectStorageSchema'\n"
+         + "         FROM " + fk.table() + " c WHERE " + orphans + ";\n"
+         + (required
+            ? "      DELETE FROM " + fk.table() + " c WHERE " + orphans + ";\n"
+            : "      UPDATE " + fk.table() + " c SET " + col + " = NULL WHERE " + orphans + ";\n")
+         + "      ALTER TABLE " + fk.table() + " ADD CONSTRAINT " + fk.name() + " FOREIGN KEY (" + col + ") REFERENCES " + fk.parent() + " (id) ON DELETE " + fk.onDelete() + ";\n"
+         + "   END IF;\n"
+         + "END $$");
+   }
+
+
+
    /*******************************************************************************
     ** The statements as one script.
     *******************************************************************************/

src/test/java/com/kingsrook/qbits/objectstorage/conformance/StandaloneTest.java

@@ -51,6 +51,7 @@ class StandaloneTest
       }
       ObjectStorage.bucket("files").delete(DeleteRequest.of(key));
       assertThat(ObjectStorage.bucket("files").head(key)).isEmpty();
+      assertThat(ObjectStorage.bucket("files").maxUploadBytes()).isEqualTo(ObjectStorage.bucket("files").config().getMaxUploadBytes());
    }
 
 

src/test/java/com/kingsrook/qbits/objectstorage/index/PostgresIndexTest.java

@@ -23,10 +23,14 @@ package com.kingsrook.qbits.objectstorage.index;
 
 import java.sql.Connection;
 import java.sql.DriverManager;
+import java.sql.ResultSet;
 import java.sql.Statement;
+import java.util.ArrayList;
 import java.util.List;
 import java.util.Map;
 import java.util.Set;
+import java.util.TreeMap;
+import java.util.TreeSet;
 import com.kingsrook.qbits.objectstorage.ObjectStorageQBitConfig;
 import com.kingsrook.qbits.objectstorage.api.FieldReferenceProvider;
 import com.kingsrook.qbits.objectstorage.api.ObjectStorage;
@@ -46,6 +50,11 @@ import com.kingsrook.qqq.backend.core.model.actions.tables.query.QueryInput;
 import com.kingsrook.qqq.backend.core.model.data.QRecord;
 import com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldMetaData;
 import com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldType;
+import com.kingsrook.qqq.backend.core.model.metadata.joins.JoinOn;
+import com.kingsrook.qqq.backend.core.model.metadata.joins.JoinType;
+import com.kingsrook.qqq.backend.core.model.metadata.joins.QJoinMetaData;
+import com.kingsrook.qqq.backend.core.model.metadata.possiblevalues.QPossibleValueSource;
+import com.kingsrook.qqq.backend.core.model.metadata.possiblevalues.QPossibleValueSourceType;
 import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
 import com.kingsrook.qqq.backend.core.model.session.QSession;
 import com.kingsrook.qqq.backend.core.model.session.QUser;
@@ -270,4 +279,203 @@ class PostgresIndexTest
          .withSecurityKeyValue(ObjectStorageTables.KEY_TYPE_BUCKET, ConfigSync.bucketId("indexed")));
       assertThat(new QueryAction().execute(new QueryInput(ObjectStorageTables.OBJECT)).getRecords()).extracting(r -> r.getValueString("objectKey")).containsExactly("d/ref.txt");
    }
+
+
+
+   /** Relations (child.column -> parent) that are deliberately not database foreign keys,
+    ** each with its reason. Empty: every join and table possible value is enforced. */
+   private static final Map<String, String> FK_EXEMPTIONS = Map.of();
+
+
+
+   /*******************************************************************************
+    ** The guardrail: every join and table possible-value field between the qbit's
+    ** tables is a foreign key in the generated schema (or exempt, with a reason),
+    ** with the declared ON DELETE rule, and every foreign key column leads an index.
+    ** A new relation without a foreign key fails here.
+    *******************************************************************************/
+   @Test
+   void everyRelationIsAForeignKeyWithAnIndex() throws Exception
+   {
+      Set<String> expected = new TreeSet<>();
+      for(QJoinMetaData join : QContext.getQInstance().getJoins().values())
+      {
+         if(ObjectStorageTables.ALL.contains(join.getLeftTable()) && ObjectStorageTables.ALL.contains(join.getRightTable()) && join.getType() == JoinType.ONE_TO_MANY)
+         {
+            for(JoinOn on : join.getJoinOns())
+            {
+               expected.add(join.getRightTable() + "." + ObjectStorageSchemaColumns.of(on.getRightField()) + " -> " + join.getLeftTable());
+            }
+         }
+      }
+      for(String tableName : ObjectStorageTables.ALL)
+      {
+         for(QFieldMetaData field : QContext.getQInstance().getTable(tableName).getFields().values())
+         {
+            QPossibleValueSource pvs = field.getPossibleValueSourceName() == null ? null : QContext.getQInstance().getPossibleValueSource(field.getPossibleValueSourceName());
+            if(pvs != null && pvs.getType() == QPossibleValueSourceType.TABLE && ObjectStorageTables.ALL.contains(pvs.getTableName()))
+            {
+               expected.add(tableName + "." + ObjectStorageSchemaColumns.of(field.getName()) + " -> " + pvs.getTableName());
+            }
+         }
+      }
+      assertThat(expected).as("the qbit declares relations").hasSizeGreaterThan(10);
+
+      Map<String, String> actual  = new TreeMap<>();
+      List<String>        noIndex = new ArrayList<>();
+      try(Connection c = DriverManager.getConnection(postgres.getJdbcUrl(), "qqq", "qqq"); Statement st = c.createStatement();
+         ResultSet rs = st.executeQuery("""
+            SELECT cl.relname AS child, a.attname AS col, pcl.relname AS parent, con.confdeltype AS rule,
+               EXISTS (SELECT 1 FROM pg_index i WHERE i.indrelid = con.conrelid AND i.indkey[0] = con.conkey[1]) AS indexed
+            FROM pg_constraint con
+            JOIN pg_class cl ON cl.oid = con.conrelid
+            JOIN pg_class pcl ON pcl.oid = con.confrelid
+            JOIN pg_attribute a ON a.attrelid = con.conrelid AND a.attnum = con.conkey[1]
+            WHERE con.contype = 'f'"""))
+      {
+         while(rs.next())
+         {
+            String key = rs.getString("child") + "." + rs.getString("col") + " -> " + rs.getString("parent");
+            actual.put(key, rs.getString("rule"));
+            if(!rs.getBoolean("indexed"))
+            {
+               noIndex.add(key);
+            }
+         }
+      }
+
+      List<String> missing = new ArrayList<>();
+      for(String relation : expected)
+      {
+         if(!actual.containsKey(relation) && !FK_EXEMPTIONS.containsKey(relation))
+         {
+            missing.add(relation);
+         }
+      }
+      assertThat(missing).as("relations without a database foreign key (add one to ObjectStorageSchema, or an exemption with a reason)").isEmpty();
+      assertThat(noIndex).as("foreign key columns that do not lead an index").isEmpty();
+
+      Map<String, String> rules = Map.of("NO ACTION", "a", "RESTRICT", "r", "CASCADE", "c", "SET NULL", "n");
+      for(ObjectStorageSchema.ForeignKey fk : ObjectStorageSchema.foreignKeys())
+      {
+         String key = fk.table() + "." + ObjectStorageSchemaColumns.of(fk.field()) + " -> " + fk.parent();
+         assertThat(actual.get(key)).as(fk.name() + " ON DELETE").isEqualTo(rules.get(fk.onDelete()));
+         assertThat(fk.name().length()).as(fk.name()).isLessThanOrEqualTo(63);
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Upgrading a schema created before the foreign keys: orphans are recorded in
+    ** qqq_fk_quarantine and cleared (nullable) or deleted (required), the keys are
+    ** added, and running the statements again changes nothing.
+    *******************************************************************************/
+   @Test
+   void foreignKeyUpgradeQuarantinesOrphansAndIsIdempotent() throws Exception
+   {
+      Integer bucketId = ConfigSync.bucketId("indexed");
+      try(Connection c = DriverManager.getConnection(postgres.getJdbcUrl(), "qqq", "qqq"); Statement st = c.createStatement())
+      {
+         for(ObjectStorageSchema.ForeignKey fk : ObjectStorageSchema.foreignKeys())
+         {
+            st.execute("ALTER TABLE " + fk.table() + " DROP CONSTRAINT " + fk.name());
+         }
+         st.execute("INSERT INTO object_storage_object (bucket_id, object_key, folder_id) VALUES (" + bucketId + ", 'kept.txt', 424242)");
+         st.execute("INSERT INTO object_storage_protection_rule (bucket_id, prefix_glob, mode) VALUES (434343, 'x/*', 'NO_DELETE')");
+         st.execute("INSERT INTO object_storage_protection_rule (bucket_id, prefix_glob, mode) VALUES (" + bucketId + ", 'y/*', 'NO_DELETE')");
+
+         ObjectStorageQBitConfig forSchema = new ObjectStorageQBitConfig().withBackendName("rdbms");
+         for(int pass = 0; pass < 2; pass++)
+         {
+            for(String sql : ObjectStorageSchema.postgresStatements(forSchema))
+            {
+               st.execute(sql);
+            }
+         }
+
+         try(ResultSet rs = st.executeQuery("SELECT table_name, column_name, missing_parent_id, action, row_data FROM qqq_fk_quarantine ORDER BY id"))
+         {
+            List<String> rows = new ArrayList<>();
+            while(rs.next())
+            {
+               rows.add(rs.getString(1) + "." + rs.getString(2) + "=" + rs.getString(3) + " " + rs.getString(4) + (rs.getString(5).contains("x/*") ? " with row" : ""));
+            }
+            assertThat(rows).as("recorded once, although the statements ran twice")
+               .containsExactly("object_storage_protection_rule.bucket_id=434343 DELETED with row", "object_storage_object.folder_id=424242 NULLED");
+         }
+         try(ResultSet rs = st.executeQuery("SELECT folder_id FROM object_storage_object WHERE object_key = 'kept.txt'"))
+         {
+            assertThat(rs.next()).isTrue();
+            assertThat(rs.getObject(1)).isNull();
+         }
+         try(ResultSet rs = st.executeQuery("SELECT prefix_glob FROM object_storage_protection_rule ORDER BY id"))
+         {
+            assertThat(rs.next()).isTrue();
+            assertThat(rs.getString(1)).isEqualTo("y/*");
+            assertThat(rs.next()).isFalse();
+         }
+         try(ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM pg_constraint WHERE contype = 'f' AND conname LIKE 'fk_object_storage_%'"))
+         {
+            rs.next();
+            assertThat(rs.getInt(1)).isEqualTo(ObjectStorageSchema.foreignKeys().size());
+         }
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** The delete paths the qbit uses still work with the keys: removing an object
+    ** row (scan removal, hard-delete write-through) takes its versions and leaves
+    ** its references pointing nowhere, and a bucket row with an audit history or
+    ** rules cannot be deleted.
+    *******************************************************************************/
+   @Test
+   void deletesFollowTheForeignKeyRules() throws Exception
+   {
+      rawPut("f/one.txt", 3);
+      ObjectIndex.scan("indexed");
+      Integer bucketId = ConfigSync.bucketId("indexed");
+      Integer objectId = Db.first(ObjectStorageTables.OBJECT, "objectKey", "f/one.txt").getValueInteger("id");
+      try(Connection c = DriverManager.getConnection(postgres.getJdbcUrl(), "qqq", "qqq"); Statement st = c.createStatement())
+      {
+         st.execute("INSERT INTO object_storage_object_version (bucket_id, object_id, object_key) VALUES (" + bucketId + ", " + objectId + ", 'f/one.txt')");
+      }
+      Db.insert("media", List.of(new QRecord().withValue("storageKey", "f/one.txt")));
+      ReferenceScan.run("indexed");
+      assertThat(Db.first(ObjectStorageTables.OBJECT_REFERENCE, "keyOrPrefix", "f/one.txt").getValueInteger("objectId")).isEqualTo(objectId);
+
+      try(S3Client raw = target.rawClient())
+      {
+         raw.deleteObject(DeleteObjectRequest.builder().bucket(target.indexBucket).key("f/one.txt").build());
+      }
+      assertThat(ObjectIndex.scan("indexed").status()).isEqualTo("OK");
+      assertThat(Db.first(ObjectStorageTables.OBJECT, "objectKey", "f/one.txt")).isNull();
+      assertThat(Db.query(ObjectStorageTables.OBJECT_VERSION, "objectId", objectId)).as("versions cascade").isEmpty();
+      assertThat(Db.first(ObjectStorageTables.OBJECT_REFERENCE, "keyOrPrefix", "f/one.txt").getValueInteger("objectId")).as("reference set null").isNull();
+
+      try(Connection c = DriverManager.getConnection(postgres.getJdbcUrl(), "qqq", "qqq"); Statement st = c.createStatement())
+      {
+         st.execute("INSERT INTO object_storage_operation (bucket_id, bucket_alias, action) VALUES (" + bucketId + ", 'indexed', 'PUT')");
+         org.assertj.core.api.Assertions.assertThatThrownBy(() -> st.execute("DELETE FROM object_storage_bucket WHERE id = " + bucketId))
+            .hasMessageContaining("fk_object_storage_operation_bucket_id");
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Column names as the schema derives them.
+    *******************************************************************************/
+   private static final class ObjectStorageSchemaColumns
+   {
+      /*******************************************************************************
+       ** The snake_case column of a field.
+       *******************************************************************************/
+      static String of(String field)
+      {
+         return (com.kingsrook.qqq.backend.core.instances.QInstanceEnricher.inferBackendName(field));
+      }
+   }
 }

src/test/java/com/kingsrook/qbits/objectstorage/internal/TablesAndConfigTest.java

@@ -129,6 +129,25 @@ class TablesAndConfigTest
 
 
 
+   /*******************************************************************************
+    ** The record-page child lists filter on the parent id alone (folder ->
+    ** objects, object -> versions and references), so the generated schema
+    ** gives each of those columns its own index, appended so existing generated
+    ** index names keep their numbers.
+    *******************************************************************************/
+   @Test
+   void testChildListColumnsAreIndexed()
+   {
+      String schema = String.join("\n", com.kingsrook.qbits.objectstorage.model.ObjectStorageSchema.postgresStatements(new ObjectStorageQBitConfig().withBackendName("rdbms")));
+      assertThat(schema)
+         .contains("ix_object_storage_object_1 ON object_storage_object (bucket_id, folder_id)")
+         .contains("ix_object_storage_object_7 ON object_storage_object (folder_id)")
+         .contains("ix_object_storage_object_version_2 ON object_storage_object_version (object_id)")
+         .contains("ix_object_storage_object_reference_3 ON object_storage_object_reference (object_id)");
+   }
+
+
+
    /*******************************************************************************
     ** Owner naming rules: every table is lowercase snake_case with the full qbit
     ** name as prefix; no table has a field that could hold a secret value; the nav