feat(help): ship the qbit-object-storage help bundle through QQQ help bundles, with coverage gate

Host-neutral help (deployment-specific facts removed), registered with
HelpBundles, gated by HelpBundleAssertions. QQQ 4.1.0-RC.7.

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

CHANGELOG.md

@@ -5,7 +5,8 @@ All notable changes to this project are documented here. The format follows Keep
 ## [0.1.0] - Unreleased
 
 ### Added
-- Every table explains itself: a help bundle (`help/qbit-object-storage.yaml`, QQQ help-bundle format) says what each `object_storage_*` table is for and how its rows get created; it shows on the table screen and when the table is empty. Applied by `ObjectStorageHelp` (STAND-IN for QRun-IO/qqq#1198); a host's own help is kept.
+- Expanded the host-neutral in-app help bundle to cover storage fields, actions, widgets and possible values, with live references and a `HelpBundleAssertions` coverage gate. Registered it for QQQ startup sync and HOST overrides; retained inline table/app help for hosts without help-content tables.
+- Every table explains itself: a help bundle (`help/qbit-object-storage.yaml`, QQQ help-bundle format) says what each `object_storage_*` table is for and how its rows get created; it shows on the table screen and when the table is empty. Applied inline by `ObjectStorageHelp` for hosts without help-content tables; a host's own help is kept.
 - A Setup card on the Storage home (connections, buckets, header rules, protection rules) with the state and next step of each, resolved the way the runtime resolves them.
 - A starter changelog `db/liquibase/qbit-object-storage/object-storage-defaults.changelog.sql`: a disabled `default-mutable` header rule. Insert-only, run through real Liquibase in `DefaultsChangelogTest`.
 - `ObjectStorageTablesGuardrailTest` (each table is used at runtime, seeded or explained, and has help).
@@ -20,12 +21,14 @@ 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
+- Raised the QQQ BOM baseline to released `4.1.0-RC.7` for the core help-bundle registry and coverage assertions.
 - Raised the QQQ BOM baseline to released `4.1.0-RC.5` and delegated S3 client construction to core `S3ClientFactory.buildClient`. Removed local HTTP client and trust-manager implementations; existing endpoint, region, addressing, checksum, TLS/CA, credentials and timeout settings remain supported.
 - Clients remain owned by each connection runtime and are rebuilt on reset/reinstall. Retry behavior now follows core/SDK defaults instead of forcing `ADAPTIVE_V2`; hosts can select the SDK retry mode through `AWS_RETRY_MODE` or `aws.retryMode`.
-- Retained local presigning (including a separate endpoint/key). A request-credentials bridge preserves static session tokens because RC.5 core metadata/factory has no session-token field or credentials-provider hook. This bridge and the existing `${file.PATH}` resolver are marked for removal by RC.7 under issue #10.
+- Retained local presigning (including a separate endpoint/key). A request-credentials bridge preserves static session tokens because RC.5 core metadata/factory has no session-token field or credentials-provider hook. This bridge and the existing `${file.PATH}` resolver remain necessary in released RC.7 (no session-token/provider hook or file-reference interpreter); their review deadline is now RC.8 under issue #10.
 - Added the filesystem module with its unused SFTP/SSH stack excluded. Resolved dependencies otherwise only change QQQ RC.4 to RC.5 and jsoup 1.23.1 to 1.23.2; see [dependency details](docs/S3-FACTORY-MIGRATION.md).
 
 ### Notes

README.md

@@ -15,7 +15,7 @@ S3-compatible storage as a capability for any QQQ app, and an admin app to run i
 ## Requirements
 
 - Java 21.
-- QQQ `4.1.0-RC.5` (released BOM), including `qqq-backend-module-filesystem`. `ConnectionRuntime` maps connection settings to core's `S3BackendMetaData` and uses `S3ClientFactory.buildClient`; core owns HTTP, TLS and checksum setup. The unused SFTP/SSH dependency is excluded. See the [dependency diff](docs/S3-FACTORY-MIGRATION.md).
+- QQQ `4.1.0-RC.7` (released BOM), including `qqq-backend-module-filesystem`. `ConnectionRuntime` maps connection settings to core's `S3BackendMetaData` and uses `S3ClientFactory.buildClient`; core owns HTTP, TLS and checksum setup. The unused SFTP/SSH dependency is excluded. See the [dependency diff](docs/S3-FACTORY-MIGRATION.md).
 - A QQQ backend for the qbit's tables (PostgreSQL is the intended store). `ObjectStorageSchema.postgresScript(config)` generates the DDL from the same metadata the app runs on, for your Liquibase or Flyway migration.
 
 ## Install
@@ -47,6 +47,20 @@ Connections and buckets come from this code only. Secrets are **references** (`$
 
 Client settings take effect when the qbit configuration is reinstalled or the runtime is reset. Presigning keeps its separate public endpoint and credentials. Retry behavior now follows core/SDK defaults; hosts requiring adaptive retries can set `AWS_RETRY_MODE=adaptive` or `aws.retryMode=adaptive`. Static session tokens remain supported through a small local bridge pending core support.
 
+## In-app help
+
+The producer registers [help/qbit-object-storage.yaml](src/main/resources/help/qbit-object-storage.yaml)
+as a `QBIT` bundle. QQQ syncs it when the host has the help-content tables installed;
+registration is safe without them, and inline table/app help still explains empty lists.
+The bundle version is the literal POM version; update both when releasing.
+
+Hosts can register their own `layer: HOST` bundle with `HelpBundles.register(qInstance, "help/my-host.yaml")`.
+Use the same entry key and role to override qbit text, keeping live `{{...}}` references where useful.
+Hosts that rename the optional Storage app should provide an app entry for their configured name.
+`ObjectStorageHelpBundleTest` checks all owned tables, fields, processes, apps, widgets and
+possible-value sources using `HelpBundleAssertions`, including valid keys, live references and
+plain summaries of at most 120 characters. It runs with `./mvnw verify`.
+
 ## Use
 
 ```java

pom.xml

@@ -255,14 +255,14 @@
    <profiles>
       <!-- Built against qqq 4.1 (QBitMetaDataProducer). Until qbit-build-parent  -->
       <!-- imports the released 4.1 BOM, this profile is active by default and     -->
-      <!-- imports qqq-bom-pom 4.1.0-RC.5 ahead of the parent's 4.0.0 BOM.         -->
+      <!-- imports qqq-bom-pom 4.1.0-RC.7 ahead of the parent's 4.0.0 BOM.         -->
       <profile>
          <id>qqq-snapshot</id>
          <activation>
             <activeByDefault>true</activeByDefault>
          </activation>
          <properties>
-            <qqq.snapshot.version>4.1.0-RC.6</qqq.snapshot.version>
+            <qqq.snapshot.version>4.1.0-RC.7</qqq.snapshot.version>
          </properties>
          <dependencyManagement>
             <dependencies>

src/main/java/com/kingsrook/qbits/objectstorage/ObjectStorageQBitProducer.java

@@ -34,6 +34,7 @@ import com.kingsrook.qqq.backend.core.exceptions.QException;
 import com.kingsrook.qqq.backend.core.model.metadata.MetaDataProducerMultiOutput;
 import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
 import com.kingsrook.qqq.backend.core.model.metadata.dashboard.QWidgetMetaData;
+import com.kingsrook.qqq.backend.core.model.metadata.help.bundles.HelpBundles;
 import com.kingsrook.qqq.backend.core.model.metadata.joins.QJoinMetaData;
 import com.kingsrook.qqq.backend.core.model.metadata.layout.QAppMetaData;
 import com.kingsrook.qqq.backend.core.model.metadata.possiblevalues.QPossibleValueSource;
@@ -194,6 +195,8 @@ public class ObjectStorageQBitProducer implements QBitMetaDataProducer<ObjectSto
          ///////////////////////////////////////////////////////////////////////////////
          qInstance.getApp(qBitConfig.getParentAppName()).withChild(qInstance.getApp(qBitConfig.getAppName()));
       }
+      // Registration is metadata-only; core skips startup sync without a helpContent table.
+      HelpBundles.register(qInstance, "help/qbit-object-storage.yaml");
       ObjectStorageHelp.apply(qInstance, qBitConfig);
       return (new MetaDataProducerMultiOutput());
    }

src/main/java/com/kingsrook/qbits/objectstorage/internal/ConnectionRuntime.java

@@ -291,8 +291,9 @@ public class ConnectionRuntime
 
 
    /*******************************************************************************
-    ** STAND-IN(core=QRun-IO/qqq#1165, removeBy=4.1.0-RC.7)
-    ** RC.5 metadata/factory lacks a session token or credentials-provider hook.
+    ** STAND-IN(core=QRun-IO/qqq#1165, removeBy=4.1.0-RC.8)
+    ** RC.7 S3BackendMetaData and S3ClientFactory still lack a session token or
+    ** credentials-provider hook (verified against the released JAR).
     ** Override request credentials only; core still owns the client and transport.
     *******************************************************************************/
    private static class SessionCredentialsClient extends DelegatingS3Client

src/main/java/com/kingsrook/qbits/objectstorage/internal/Db.java

@@ -40,8 +40,8 @@ import com.kingsrook.qqq.backend.core.model.actions.tables.query.QueryInput;
 import com.kingsrook.qqq.backend.core.model.actions.tables.update.UpdateInput;
 import com.kingsrook.qqq.backend.core.model.data.QRecord;
 import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
-import com.kingsrook.qqq.backend.core.model.metadata.security.QSecurityKeyType;
 import com.kingsrook.qqq.backend.core.model.session.QSession;
+import com.kingsrook.qqq.backend.core.model.session.QSystemUserSession;
 
 
 /*******************************************************************************
@@ -131,30 +131,15 @@ public final class Db
 
 
    /*******************************************************************************
-    ** A session that holds every permission and every all-access security key of
-    ** the given instance (computed per instance, never cached across instances).
+    ** The core system-user session (every permission and every all-access
+    ** security key of the given instance, read per instance). It must be a
+    ** QSystemUserSession: token-based authentication modules (OAuth2, Auth0)
+    ** accept that class without a token and reject any other tokenless session,
+    ** which made every system read and write fail on such hosts.
     *******************************************************************************/
    private static QSession systemSession(QInstance instance)
    {
-      QSession session = new QSession()
-      {
-         /*******************************************************************************
-          ** The system user holds every permission.
-          *******************************************************************************/
-         @Override
-         public boolean hasPermission(String permissionName)
-         {
-            return (true);
-         }
-      };
-      for(QSecurityKeyType keyType : instance.getSecurityKeyTypes().values())
-      {
-         if(keyType.getAllAccessKeyName() != null)
-         {
-            session.withSecurityKeyValue(keyType.getAllAccessKeyName(), true);
-         }
-      }
-      return (session);
+      return (new QSystemUserSession(instance));
    }
 
 

src/main/java/com/kingsrook/qbits/objectstorage/internal/SecretRefs.java

@@ -36,7 +36,8 @@ import com.kingsrook.qqq.backend.core.utils.StringUtils;
  ** the allow-list. Resolved values live only in memory, never in a record, a log
  ** line or a process value.
  **
- ** STAND-IN(core=QRun-IO/qqq#1165, removeBy=4.1.0-RC.7)
+ ** STAND-IN(core=QRun-IO/qqq#1165, removeBy=4.1.0-RC.8)
+ ** RC.7 QMetaDataVariableInterpreter still supports env/prop, not file references.
  ** ${file.PATH} moves to QMetaDataVariableInterpreter in QQQ core
  ** (spec section 8.2); then this class delegates entirely.
  *******************************************************************************/

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

@@ -40,9 +40,8 @@ import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
  ** Applies help/qbit-object-storage.yaml (the QQQ help-bundle format) to the
  ** Storage app and its object_storage_* tables: what each table is for and how
  ** its rows get created, shown on the table screen and when it is empty.
- ** STAND-IN(core=QRun-IO/qqq#1198, removeBy=4.1.0-RC.8): replace with
- ** HelpBundles.register once the pinned QQQ release includes it; the bundle file
- ** stays the same.
+ ** Inline defaults preserve empty-state help for hosts without helpContent.
+ ** The producer also registers the bundle for core sync and HOST overrides.
  *******************************************************************************/
 public final class ObjectStorageHelp
 {
@@ -81,7 +80,7 @@ public final class ObjectStorageHelp
          {
             String key     = entry.path("key").asText();
             String summary = entry.path("summary").asText();
-            if(key.isBlank() || summary.isBlank() || summary.length() > 120 || entry.path("content").asText().isBlank() || entries.put(key, entry) != null)
+            if(key.isBlank() || summary.isBlank() || summary.length() > 120 || entries.put(key, entry) != null)
             {
                throw (new IOException("invalid or duplicate entry: " + key));
             }

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/ObjectStorageHelpBundleTest.java

@@ -0,0 +1,76 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2025.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.objectstorage;
+
+
+import java.util.Set;
+import java.util.stream.Collectors;
+import com.kingsrook.qbits.objectstorage.testsupport.StorageTestSupport;
+import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
+import com.kingsrook.qqq.backend.core.model.metadata.help.bundles.HelpBundles;
+import com.kingsrook.qqq.backend.core.testing.HelpBundleAssertions;
+import org.junit.jupiter.api.AfterEach;
+import org.junit.jupiter.api.Test;
+import static org.assertj.core.api.Assertions.assertThat;
+
+
+/*******************************************************************************
+ ** Coverage of every object owned by the storage qbit, including its optional app.
+ *******************************************************************************/
+class ObjectStorageHelpBundleTest
+{
+   /*******************************************************************************
+    ** Release the installed runtime after each test.
+    *******************************************************************************/
+   @AfterEach
+   void tearDown()
+   {
+      StorageTestSupport.tearDown();
+   }
+
+
+
+   /*******************************************************************************
+    ** Register safely without help tables and cover all owned metadata.
+    *******************************************************************************/
+   @Test
+   void bundleIsCompleteWithoutHelpTables() throws Exception
+   {
+      QInstance instance = StorageTestSupport.setUp(new ObjectStorageQBitConfig()
+         .withBackendName(StorageTestSupport.MEMORY).withBucketScoping(false).withOpenWhenAnonymous(true).withAdminApp(true));
+
+      assertThat(instance.getTable("helpContent")).isNull();
+      assertThat(HelpBundles.of(instance).getBundles()).singleElement()
+         .satisfies(bundle -> assertThat(bundle.getId()).isEqualTo("qbit-object-storage"));
+      HelpBundles.syncOnStartup(instance);
+      Set<String> keys = HelpBundles.of(instance).getBundles().get(0).getEntries().stream()
+         .map(entry -> entry.getKey()).collect(Collectors.toSet());
+      // RC.7 validates these entries but only requires app/table/process/field coverage.
+      instance.getWidgets().keySet().stream().filter(name -> name.startsWith("object_storage_") || name.startsWith("objectStorage"))
+         .forEach(name -> assertThat(keys).contains("widget:" + name + ";slot:label"));
+      instance.getPossibleValueSources().keySet().stream().filter(name -> name.startsWith("object_storage_"))
+         .forEach(name -> assertThat(keys).contains("possibleValueSource:" + name + ";slot:help"));
+      HelpBundleAssertions.forInstance(instance)
+         .bundles("help/qbit-object-storage.yaml")
+         .owning(target -> target.name().startsWith("object_storage_") || target.name().startsWith("objectStorage"))
+         .assertComplete();
+   }
+}

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/DbSystemSessionTest.java

@@ -0,0 +1,72 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2025.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.objectstorage.internal;
+
+
+import com.kingsrook.qbits.objectstorage.ObjectStorageQBitConfig;
+import com.kingsrook.qbits.objectstorage.testsupport.StorageTestSupport;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.model.metadata.security.QSecurityKeyType;
+import com.kingsrook.qqq.backend.core.model.session.QSession;
+import com.kingsrook.qqq.backend.core.model.session.QSystemUserSession;
+import org.junit.jupiter.api.AfterEach;
+import org.junit.jupiter.api.Test;
+import static org.assertj.core.api.Assertions.assertThat;
+
+
+/*******************************************************************************
+ ** Db.asSystem runs work as the core system user. Token-based authentication
+ ** modules (OAuth2, Auth0) accept a QSystemUserSession without a token and
+ ** reject any other tokenless session, so the class matters, not just the
+ ** permissions.
+ *******************************************************************************/
+class DbSystemSessionTest
+{
+   /*******************************************************************************
+    ** Reset the runtime and context after each test.
+    *******************************************************************************/
+   @AfterEach
+   void tearDown()
+   {
+      StorageTestSupport.tearDown();
+   }
+
+
+
+   /*******************************************************************************
+    ** The work sees a QSystemUserSession with every all-access key, and the
+    ** caller's session is restored afterwards.
+    *******************************************************************************/
+   @Test
+   void testAsSystemUsesCoreSystemUserSession() throws Exception
+   {
+      ObjectStorageQBitConfig config = new ObjectStorageQBitConfig().withBackendName(StorageTestSupport.MEMORY).withBucketScoping(false);
+      StorageTestSupport.setUp(config, instance -> instance.addSecurityKeyType(new QSecurityKeyType().withName("tenant").withAllAccessKeyName("tenantAllAccess")));
+      QSession caller = QContext.getQSession();
+
+      QSession inside = Db.asSystem(QContext::getQSession);
+
+      assertThat(inside).isInstanceOf(QSystemUserSession.class);
+      assertThat(inside.hasPermission("anything")).isTrue();
+      assertThat(inside.hasSecurityKeyValue("tenantAllAccess", true)).isTrue();
+      assertThat(QContext.getQSession()).isSameAs(caller);
+   }
+}

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

@@ -218,6 +218,11 @@ public class DefaultsChangelogTest
       {
          for(String sql : ObjectStorageSchema.postgresStatements(config()))
          {
+            if(sql.startsWith("DO $$"))
+            {
+               // the foreign-key blocks are PL/pgSQL (Postgres only); PostgresIndexTest runs them on real Postgres
+               continue;
+            }
             statement.execute(sql);
          }
       }

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