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:
@@ -22,7 +22,7 @@ workflows:
               context: [qqq-maven-registry-credentials]
               filters:
                  branches:
-                    ignore: /(develop|release\/.*|hotfix\/.*)/
+                    ignore: /(develop|release\/.*)/
 
    publish_snapshot:
       jobs:
@@ -58,14 +58,3 @@ workflows:
                     ignore: /.*/
                  tags:
                     only: [/^v\d+\.\d+\.\d+$/]
-
-   publish_hotfix_release:
-      jobs:
-         - qqq-orb/mvn_publish:
-              pre-steps:
-                 - configure-package-install
-              context: [qqq-maven-registry-credentials]
-              branch_type: hotfix
-              filters:
-                 branches:
-                    only: [/hotfix\/.*/]

.github/workflows/release-rc.yml

@@ -0,0 +1,25 @@
+# Cut the next release candidate (QRun-IO/qqq docs/QRUN-QBIT-REPO-STANDARD.md, C6):
+# run this workflow by hand, or merge a develop PR labelled `release:rc`. It merges develop into
+# the release line and the release/* push runs CircleCI publish_release_candidate.
+name: Release candidate
+
+on:
+  workflow_dispatch:
+    inputs:
+      dry_run:
+        description: Print the plan without merging or creating branches
+        type: boolean
+        default: false
+  pull_request:
+    types: [closed]
+    branches: [develop]
+
+permissions:
+  contents: write
+
+jobs:
+  release-rc:
+    if: github.event_name == 'workflow_dispatch' || (github.event.pull_request.merged == true && contains(github.event.pull_request.labels.*.name, 'release:rc'))
+    uses: QRun-IO/.github/.github/workflows/qbit-release-rc.yml@main
+    with:
+      dry_run: ${{ github.event_name == 'workflow_dispatch' && inputs.dry_run }}

AGENTS.md

@@ -35,7 +35,7 @@ This file provides guidance to Codex when working with code in this repository.
 ## Dependencies
 
 - Java 21.
-- Parent `com.kingsrook:qbit-build-parent:2.0.0` (qqq 4.0.0). The default-active `qqq-snapshot` profile imports `qqq-bom-pom:4.1.0-RC.4` ahead of it, as in qbit-quick-search (ADR-0007). Remove that activation when the parent moves to 4.1.
+- Parent `com.kingsrook:qbit-build-parent:2.0.0` (qqq 4.0.0). The default-active `qqq-snapshot` profile imports `qqq-bom-pom:4.1.0-RC.5` ahead of it, as in qbit-quick-search (ADR-0007). Remove that activation when the parent moves to 4.1.
 - Tests: JUnit 5, AssertJ.
 
 ## Release

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,8 +14,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.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.
+- 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
-- Until QQQ core's S3 module on AWS SDK v2 (qqq#1095, RC.5) is released, the qbit uses a small internal SDK v2 client factory.
 - Deliberate differences from the design text: Test connection is two actions (read-only, and with write tests) so the write variant can require `object_storage.admin`; a folder record has no subfolder child list (core does not allow a self join as an exposed join); there is no `withTableNamePrefix` (the `object_storage_` prefix is fixed by the naming rule).

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 (built against `4.1.0-RC.4`). Until QQQ core's S3 module on AWS SDK v2 (qqq#1095, RC.5) is released, the qbit carries a small internal SDK v2 client factory (`ConnectionRuntime`); it will be replaced by core's `S3ClientFactory` without changing the public API.
+- 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).
 - 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
@@ -45,6 +45,8 @@ new ObjectStorageQBitProducer()
 
 Connections and buckets come from this code only. Secrets are **references** (`${env.X}`, `${prop.X}`, `${file.PATH}`) that must match `allowedSecretRefPatterns` (default `env.S3_*`, `env.STORAGE_*`, `file./var/run/secrets/storage/*`); no table stores a secret value. At startup the qbit upserts one `object_storage_connection` and one `object_storage_bucket` row per entry, so the tables always show the real configuration.
 
+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.
+
 ## Use
 
 ```java

docs/S3-FACTORY-MIGRATION.md

@@ -0,0 +1,86 @@
+# Core S3 factory migration (issue #10, F3)
+
+Core source was inspected at local QRun-IO/qqq tag `v4.1.0-RC.5`:
+`qqq-backend-module-filesystem/src/main/java/com/kingsrook/qqq/backend/module/filesystem/s3/{utils/S3ClientFactory,model/metadata/S3BackendMetaData}.java`.
+
+## Ownership and host behavior
+
+`ConnectionRuntime` resolves secret/setting references and maps endpoint, region,
+path style, checksum mode, TLS verification, extra CA PEM, static keys, and both
+HTTP timeouts into `S3BackendMetaData`. Default-chain and web-identity modes leave
+keys unset so core uses the SDK default credentials chain. Provider-specific qbit
+defaults and the existing 5-second connect / 60-second read timeouts are preserved.
+Core handles custom CA trust in addition to the JVM trust store.
+
+The qbit calls uncached `buildClient`, because it owns and closes each client.
+`ObjectStorageRuntime` caches by connection name and resets clients on
+configuration install/reset; `settingsHash` is an informational table value,
+not the cache key. This migration keeps that lifecycle and avoids sharing a
+core-cached client that the qbit would later close. Configuration changes take
+effect on reinstall/reset, not through mutation of an already cached client.
+
+Core does not expose retry configuration. Requests now use core/SDK retry
+defaults instead of the former forced `ADAPTIVE_V2`. Hosts needing adaptive
+retries can set the SDK's `AWS_RETRY_MODE=adaptive` or `aws.retryMode=adaptive`.
+The public qbit configuration API is unchanged.
+
+## Remaining local code
+
+- Core RC.5 has no session-token field or credentials-provider customization
+  in `S3BackendMetaData` / `S3ClientFactory.buildClient`. The narrow
+  `SessionCredentialsClient` bridge adds session credentials to requests while
+  retaining their overrides; core still creates the client and HTTP transport.
+  Core needs session-token metadata (including enrichment and cache-key support)
+  and `AwsSessionCredentials` construction, or a caller-provided credentials hook.
+  Marker: `STAND-IN(core=QRun-IO/qqq#1165, removeBy=4.1.0-RC.7)`.
+- Core has no presigner factory. The local presigner retains its separate public
+  endpoint and least-privilege keys, along with existing session-token handling.
+- `SecretRefs` file resolution is unchanged and carries the same RC.7 marker.
+
+## Resolved dependency diff
+
+Captured using the Maven 3.9.11 wrapper's `-B dependency:tree` before and after
+this change. Coordinates below are normalized to omit tree indentation and
+parent ordering. Full raw trees are in `target/dependency-tree-{before,after}.txt`
+for this working session (generated build files, not committed).
+
+```diff
+- com.kingsrook.qqq:qqq-backend-core:jar:4.1.0-RC.4:compile
+- com.kingsrook.qqq:qqq-backend-module-postgres:jar:4.1.0-RC.4:test
+- com.kingsrook.qqq:qqq-backend-module-rdbms:jar:4.1.0-RC.4:test
+- com.kingsrook.qqq:qqq-middleware-javalin:jar:4.1.0-RC.4:test
+- com.kingsrook.qqq:qqq-openapi:jar:4.1.0-RC.4:test
+- org.jsoup:jsoup:jar:1.23.1:compile
++ com.kingsrook.qqq:qqq-backend-core:jar:4.1.0-RC.5:compile
++ com.kingsrook.qqq:qqq-backend-module-filesystem:jar:4.1.0-RC.5:compile
++ com.kingsrook.qqq:qqq-backend-module-postgres:jar:4.1.0-RC.5:test
++ com.kingsrook.qqq:qqq-backend-module-rdbms:jar:4.1.0-RC.5:test
++ com.kingsrook.qqq:qqq-middleware-javalin:jar:4.1.0-RC.5:test
++ com.kingsrook.qqq:qqq-openapi:jar:4.1.0-RC.5:test
++ org.jsoup:jsoup:jar:1.23.2:compile
+```
+
+The only new resolved artifact is `qqq-backend-module-filesystem`. It declares
+`org.apache.sshd:sshd-sftp:2.16.0`, which would bring `sshd-core` and `sshd-common`;
+that unused SFTP stack is excluded at the qbit dependency. No SSH libraries or
+AWS SDK v1 artifacts are present in the resulting tree. AWS SDK v2 remains
+`2.41.34`; `commons-lang3:3.20.0` was already present and is unchanged. The
+filesystem module's test dependencies do not become runtime dependencies.
+
+## Verification in this worktree
+
+Java 21.0.12.1, Maven wrapper 3.9.11; `./mvnw.cmd -B verify`:
+
+- Checkstyle: zero violations; production and test compilation succeeded.
+- Surefire: 119 tests reported, 32 passed, 87 skipped, zero failures/errors.
+  All eight new `ConnectionRuntimeTest` tests passed, including actual HTTP
+  signing with a session token on GET, PUT and pagination.
+- Docker Desktop access is denied at `npipe:////./pipe/dockerDesktopLinuxEngine`.
+  MinIO, Garage, standalone-service and PostgreSQL integration coverage could
+  not run. Testcontainers also reported its default `docker_engine` pipe unavailable.
+- The full gate **failed** at JaCoCo: instructions 33.68% (required 70%), classes
+  70.75% (required 90%). Coverage thresholds and Docker test policy are unchanged.
+- No unrelated RC.5 compatibility fixes were needed. `git diff --check` passed.
+
+Rerun the full verification gate in an environment with Docker access before
+merging. No changes were committed or pushed.

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>
@@ -72,7 +74,7 @@
          <dependency>
             <groupId>software.amazon.awssdk</groupId>
             <artifactId>bom</artifactId>
-            <version>2.41.10</version>
+            <version>2.41.34</version>
             <type>pom</type>
             <scope>import</scope>
          </dependency>
@@ -80,6 +82,17 @@
    </dependencyManagement>
 
    <dependencies>
+      <dependency>
+         <groupId>com.kingsrook.qqq</groupId>
+         <artifactId>qqq-backend-module-filesystem</artifactId>
+         <exclusions>
+            <!-- Only the S3 factory is used; do not ship the SFTP backend's SSH stack. -->
+            <exclusion>
+               <groupId>org.apache.sshd</groupId>
+               <artifactId>sshd-sftp</artifactId>
+            </exclusion>
+         </exclusions>
+      </dependency>
       <dependency>
          <groupId>software.amazon.awssdk</groupId>
          <artifactId>s3</artifactId>
@@ -228,14 +241,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.4 ahead of the parent's 4.0.0 BOM.         -->
+      <!-- imports qqq-bom-pom 4.1.0-RC.5 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.4</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/internal/ConnectionRuntime.java

@@ -21,57 +21,38 @@
 package com.kingsrook.qbits.objectstorage.internal;
 
 
-import javax.net.ssl.TrustManager;
-import javax.net.ssl.TrustManagerFactory;
-import javax.net.ssl.X509TrustManager;
-import java.io.ByteArrayInputStream;
 import java.net.URI;
-import java.nio.charset.StandardCharsets;
-import java.security.KeyStore;
-import java.security.cert.Certificate;
-import java.security.cert.CertificateException;
-import java.security.cert.CertificateFactory;
-import java.security.cert.X509Certificate;
-import java.time.Duration;
-import java.util.Collection;
+import java.util.function.Function;
 import com.kingsrook.qbits.objectstorage.config.AddressingStyle;
-import com.kingsrook.qbits.objectstorage.config.ChecksumMode;
 import com.kingsrook.qbits.objectstorage.config.CredentialsMode;
 import com.kingsrook.qbits.objectstorage.config.StorageConnectionConfig;
-import com.kingsrook.qqq.backend.core.logging.QLogger;
 import com.kingsrook.qqq.backend.core.utils.StringUtils;
+import com.kingsrook.qqq.backend.module.filesystem.s3.model.metadata.S3BackendMetaData;
+import com.kingsrook.qqq.backend.module.filesystem.s3.model.metadata.S3ChecksumMode;
+import com.kingsrook.qqq.backend.module.filesystem.s3.utils.S3ClientFactory;
 import software.amazon.awssdk.auth.credentials.AwsBasicCredentials;
 import software.amazon.awssdk.auth.credentials.AwsCredentialsProvider;
 import software.amazon.awssdk.auth.credentials.AwsSessionCredentials;
 import software.amazon.awssdk.auth.credentials.DefaultCredentialsProvider;
 import software.amazon.awssdk.auth.credentials.StaticCredentialsProvider;
-import software.amazon.awssdk.core.checksums.RequestChecksumCalculation;
-import software.amazon.awssdk.core.checksums.ResponseChecksumValidation;
-import software.amazon.awssdk.core.retry.RetryMode;
-import software.amazon.awssdk.http.SdkHttpClient;
-import software.amazon.awssdk.http.SdkHttpConfigurationOption;
-import software.amazon.awssdk.http.apache.ApacheHttpClient;
+import software.amazon.awssdk.awscore.AwsRequestOverrideConfiguration;
 import software.amazon.awssdk.regions.Region;
+import software.amazon.awssdk.services.s3.DelegatingS3Client;
 import software.amazon.awssdk.services.s3.S3Client;
-import software.amazon.awssdk.services.s3.S3ClientBuilder;
 import software.amazon.awssdk.services.s3.S3Configuration;
+import software.amazon.awssdk.services.s3.model.S3Request;
 import software.amazon.awssdk.services.s3.presigner.S3Presigner;
-import software.amazon.awssdk.utils.AttributeMap;
-import static com.kingsrook.qqq.backend.core.logging.LogUtils.logPair;
 
 
 /*******************************************************************************
  ** One connection's live state: the settings, one cached SDK v2 S3Client and
  ** S3Presigner, and the rate limiter.
  **
- ** STAND-IN: this is the small internal client factory used until QQQ core's
- ** S3ClientFactory (qqq #1095, RC.5) is released; replace it then. Nothing
- ** outside this package depends on how the client is built.
+ ** Core owns S3 client construction. This runtime owns its uncached client and
+ ** closes it on reset/reinstall; the presigner remains local.
  *******************************************************************************/
 public class ConnectionRuntime
 {
-   private static final QLogger LOG = QLogger.getLogger(ConnectionRuntime.class);
-
    private final StorageConnectionConfig config;
    private final RateLimiter             rateLimiter;
    private final String                  settingsHash;
@@ -119,7 +100,7 @@ public class ConnectionRuntime
 
 
    /*******************************************************************************
-    ** Hash of the settings that shape the client (clients rebuild when it changes).
+    ** Display hash of connection settings; runtime reset/reinstall rebuilds clients.
     *******************************************************************************/
    public String getSettingsHash()
    {
@@ -217,36 +198,41 @@ public class ConnectionRuntime
     *******************************************************************************/
    private S3Client buildClient()
    {
-      S3ClientBuilder builder = S3Client.builder()
-         .credentialsProvider(credentials(config.getAccessKeyRef(), config.getSecretKeyRef()))
-         .httpClient(httpClient())
-         .forcePathStyle(pathStyle())
-         .overrideConfiguration(o -> o.retryStrategy(RetryMode.ADAPTIVE_V2));
-
-      String endpoint = SecretRefs.resolveSetting(config.getEndpoint());
-      if(StringUtils.hasContent(endpoint))
+      S3BackendMetaData backend = backendMetaData();
+      String token = config.getCredentialsMode() == CredentialsMode.STATIC_REFS ? SecretRefs.resolve(config.getSessionTokenRef()) : null;
+      AwsCredentialsProvider sessionCredentials = null;
+      if(token != null && backend.getAccessKey() != null && backend.getSecretKey() != null)
       {
-         builder.endpointOverride(URI.create(endpoint));
-      }
-      String region = effectiveRegion();
-      if(region != null)
-      {
-         builder.region(Region.of(region));
+         sessionCredentials = StaticCredentialsProvider.create(AwsSessionCredentials.create(backend.getAccessKey(), backend.getSecretKey(), token));
       }
 
-      if(config.effectiveChecksumMode() == ChecksumMode.WHEN_REQUIRED)
-      {
-         builder.requestChecksumCalculation(RequestChecksumCalculation.WHEN_REQUIRED);
-         builder.responseChecksumValidation(ResponseChecksumValidation.WHEN_REQUIRED);
-      }
-      else
-      {
-         builder.requestChecksumCalculation(RequestChecksumCalculation.WHEN_SUPPORTED);
-         builder.responseChecksumValidation(ResponseChecksumValidation.WHEN_SUPPORTED);
-      }
+      S3Client coreClient = S3ClientFactory.buildClient(backend);
+      return (sessionCredentials == null ? coreClient : new SessionCredentialsClient(coreClient, sessionCredentials));
+   }
 
-      LOG.debug("Building S3 client", logPair("connection", config.getName()), logPair("endpoint", endpoint), logPair("checksumMode", config.effectiveChecksumMode()));
-      return (builder.build());
+
+
+   /*******************************************************************************
+    ** Resolve references before passing the connection settings to core. No bucket
+    ** is mapped: one connection serves multiple buckets. Presigning stays local.
+    *******************************************************************************/
+   S3BackendMetaData backendMetaData()
+   {
+      S3BackendMetaData backend = new S3BackendMetaData()
+         .withEndpoint(SecretRefs.resolveSetting(config.getEndpoint()))
+         .withRegion(effectiveRegion())
+         .withPathStyleAccess(pathStyle())
+         .withChecksumMode(S3ChecksumMode.valueOf(config.effectiveChecksumMode().name()))
+         .withTlsVerify(config.getTlsVerify())
+         .withCaCertificatesPem(SecretRefs.resolve(config.getCaBundleRef()))
+         .withConnectTimeoutMillis(config.getConnectTimeoutMs())
+         .withSocketTimeoutMillis(config.getReadTimeoutMs());
+      if(config.getCredentialsMode() == CredentialsMode.STATIC_REFS)
+      {
+         backend.withAccessKey(SecretRefs.resolve(config.getAccessKeyRef()))
+            .withSecretKey(SecretRefs.resolve(config.getSecretKeyRef()));
+      }
+      return (backend);
    }
 
 
@@ -305,139 +291,41 @@ public class ConnectionRuntime
 
 
    /*******************************************************************************
-    ** Apache HTTP client with the connection's timeouts and TLS settings.
+    ** 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.
+    ** Override request credentials only; core still owns the client and transport.
     *******************************************************************************/
-   private SdkHttpClient httpClient()
+   private static class SessionCredentialsClient extends DelegatingS3Client
    {
-      ApacheHttpClient.Builder builder = ApacheHttpClient.builder()
-         .connectionTimeout(Duration.ofMillis(config.getConnectTimeoutMs()))
-         .socketTimeout(Duration.ofMillis(config.getReadTimeoutMs()));
-
-      if(Boolean.FALSE.equals(config.getTlsVerify()))
-      {
-         LOG.warn("TLS verification is disabled for a storage connection; use only for development", logPair("connection", config.getName()));
-         return (builder.buildWithDefaults(AttributeMap.builder().put(SdkHttpConfigurationOption.TRUST_ALL_CERTIFICATES, Boolean.TRUE).build()));
-      }
-
-      String caPem = SecretRefs.resolve(config.getCaBundleRef());
-      if(StringUtils.hasContent(caPem))
-      {
-         TrustManager[] trustManagers = trustManagers(caPem);
-         builder.tlsTrustManagersProvider(() -> trustManagers);
-      }
-      return (builder.build());
-   }
+      private final AwsCredentialsProvider credentialsProvider;
 
 
 
-   /*******************************************************************************
-    ** Trust the given PEM bundle in addition to the JVM default trust store.
-    *******************************************************************************/
-   static TrustManager[] trustManagers(String pem)
-   {
-      try
-      {
-         CertificateFactory                certificateFactory = CertificateFactory.getInstance("X.509");
-         Collection<? extends Certificate> certificates       = certificateFactory.generateCertificates(new ByteArrayInputStream(pem.getBytes(StandardCharsets.UTF_8)));
-         if(certificates.isEmpty())
-         {
-            throw (new IllegalArgumentException("The CA bundle contains no certificates"));
-         }
-         KeyStore keyStore = KeyStore.getInstance(KeyStore.getDefaultType());
-         keyStore.load(null, null);
-         int index = 0;
-         for(Certificate certificate : certificates)
-         {
-            keyStore.setCertificateEntry("object-storage-ca-" + index++, certificate);
-         }
-         X509TrustManager custom   = x509(keyStore);
-         X509TrustManager fallback = x509(null);
-         return (new TrustManager[] { new Composite(custom, fallback) });
-      }
-      catch(IllegalArgumentException e)
-      {
-         throw (e);
-      }
-      catch(Exception e)
-      {
-         throw (new IllegalArgumentException("Could not load the CA bundle for a storage connection", e));
-      }
-   }
-
-
-
-   /*******************************************************************************
-    ** The X509 trust manager of a key store (null: the JVM default).
-    *******************************************************************************/
-   private static X509TrustManager x509(KeyStore keyStore) throws Exception
-   {
-      TrustManagerFactory factory = TrustManagerFactory.getInstance(TrustManagerFactory.getDefaultAlgorithm());
-      factory.init(keyStore);
-      for(TrustManager trustManager : factory.getTrustManagers())
-      {
-         if(trustManager instanceof X509TrustManager x)
-         {
-            return (x);
-         }
-      }
-      throw (new IllegalStateException("No X509TrustManager available"));
-   }
-
-
-
-   /*******************************************************************************
-    ** Trusts a chain if either the private-CA or the default trust manager does.
-    *******************************************************************************/
-   private record Composite(X509TrustManager custom, X509TrustManager fallback) implements X509TrustManager
-   {
       /*******************************************************************************
-       ** Client certs are not used.
+       ** Wrap a core-built client with the connection's session credentials.
        *******************************************************************************/
-      @Override
-      public void checkClientTrusted(X509Certificate[] chain, String authType) throws CertificateException
+      private SessionCredentialsClient(S3Client delegate, AwsCredentialsProvider credentialsProvider)
       {
-         try
-         {
-            custom.checkClientTrusted(chain, authType);
-         }
-         catch(CertificateException e)
-         {
-            fallback.checkClientTrusted(chain, authType);
-         }
+         super(delegate);
+         this.credentialsProvider = credentialsProvider;
       }
 
 
 
       /*******************************************************************************
-       ** Server certs.
+       ** Preserve request overrides and the concrete request type while adding the
+       ** session token to signing, including streaming and paginated operations.
        *******************************************************************************/
       @Override
-      public void checkServerTrusted(X509Certificate[] chain, String authType) throws CertificateException
+      @SuppressWarnings("unchecked") // toBuilder().build() retains the concrete request type T.
+      protected <T extends S3Request, ReturnT> ReturnT invokeOperation(T request, Function<T, ReturnT> operation)
       {
-         try
-         {
-            custom.checkServerTrusted(chain, authType);
-         }
-         catch(CertificateException e)
-         {
-            fallback.checkServerTrusted(chain, authType);
-         }
-      }
-
-
-
-      /*******************************************************************************
-       ** Accepted issuers of both.
-       *******************************************************************************/
-      @Override
-      public X509Certificate[] getAcceptedIssuers()
-      {
-         X509Certificate[] a = custom.getAcceptedIssuers();
-         X509Certificate[] b = fallback.getAcceptedIssuers();
-         X509Certificate[] all = new X509Certificate[a.length + b.length];
-         System.arraycopy(a, 0, all, 0, a.length);
-         System.arraycopy(b, 0, all, a.length, b.length);
-         return (all);
+         AwsRequestOverrideConfiguration.Builder overrides = request.overrideConfiguration()
+            .map(AwsRequestOverrideConfiguration::toBuilder).orElseGet(AwsRequestOverrideConfiguration::builder);
+         T signedRequest = (T) request.toBuilder()
+            .overrideConfiguration(overrides.credentialsProvider(credentialsProvider).build())
+            .build();
+         return (operation.apply(signedRequest));
       }
    }
 }

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: ${file.PATH} moves to QMetaDataVariableInterpreter in QQQ core
+ ** STAND-IN(core=QRun-IO/qqq#1165, removeBy=4.1.0-RC.7)
+ ** ${file.PATH} moves to QMetaDataVariableInterpreter in QQQ core
  ** (spec section 8.2); then this class delegates entirely.
  *******************************************************************************/
 public final class SecretRefs

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

@@ -0,0 +1,320 @@
+/*
+ * 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 java.net.InetSocketAddress;
+import java.net.URI;
+import java.nio.charset.StandardCharsets;
+import java.nio.file.Files;
+import java.nio.file.Path;
+import java.time.Duration;
+import java.util.ArrayList;
+import java.util.List;
+import com.kingsrook.qbits.objectstorage.config.AddressingStyle;
+import com.kingsrook.qbits.objectstorage.config.ChecksumMode;
+import com.kingsrook.qbits.objectstorage.config.CredentialsMode;
+import com.kingsrook.qbits.objectstorage.config.ProviderType;
+import com.kingsrook.qbits.objectstorage.config.StorageConnectionConfig;
+import com.kingsrook.qqq.backend.module.filesystem.s3.model.metadata.S3BackendMetaData;
+import com.kingsrook.qqq.backend.module.filesystem.s3.model.metadata.S3ChecksumMode;
+import com.sun.net.httpserver.Headers;
+import com.sun.net.httpserver.HttpServer;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.io.TempDir;
+import software.amazon.awssdk.auth.credentials.AwsCredentialsProvider;
+import software.amazon.awssdk.auth.credentials.DefaultCredentialsProvider;
+import software.amazon.awssdk.core.checksums.RequestChecksumCalculation;
+import software.amazon.awssdk.core.checksums.ResponseChecksumValidation;
+import software.amazon.awssdk.core.sync.RequestBody;
+import software.amazon.awssdk.regions.Region;
+import software.amazon.awssdk.services.s3.S3Client;
+import software.amazon.awssdk.services.s3.model.GetObjectRequest;
+import software.amazon.awssdk.services.s3.model.ListObjectsV2Request;
+import static org.assertj.core.api.Assertions.assertThat;
+import static org.assertj.core.api.Assertions.assertThatThrownBy;
+
+
+/*******************************************************************************
+ ** Mapping and lifecycle checks plus real SDK signing against a loopback server.
+ *******************************************************************************/
+class ConnectionRuntimeTest
+{
+   /*******************************************************************************
+    ** Every core-supported setting is mapped after resolving references. Session
+    ** tokens and presign settings have no core fields and are exercised below.
+    *******************************************************************************/
+   @Test
+   void testBackendMetaDataCarriesEverySetting(@TempDir Path dir) throws Exception
+   {
+      StorageConnectionConfig config = new StorageConnectionConfig("mapping")
+         .withEndpoint(reference(dir, "endpoint", "https://storage.example.test"))
+         .withRegion(reference(dir, "region", "eu-west-1"))
+         .withAddressingStyle(AddressingStyle.VIRTUAL_HOST)
+         .withChecksumMode(ChecksumMode.WHEN_SUPPORTED)
+         .withTlsVerify(false)
+         .withCaBundleRef(reference(dir, "ca", "test-pem-bundle"))
+         .withCredentialsMode(CredentialsMode.STATIC_REFS)
+         .withAccessKeyRef(reference(dir, "access", "test-access"))
+         .withSecretKeyRef(reference(dir, "secret", "test-secret"))
+         .withConnectTimeoutMs(1234)
+         .withReadTimeoutMs(5678);
+      S3BackendMetaData backend = new ConnectionRuntime(config).backendMetaData();
+      assertThat(backend.getEndpoint()).isEqualTo("https://storage.example.test");
+      assertThat(backend.getRegion()).isEqualTo("eu-west-1");
+      assertThat(backend.getPathStyleAccess()).isFalse();
+      assertThat(backend.getChecksumMode()).isEqualTo(S3ChecksumMode.WHEN_SUPPORTED);
+      assertThat(backend.getTlsVerify()).isFalse();
+      assertThat(backend.getCaCertificatesPem()).isEqualTo("test-pem-bundle");
+      assertThat(backend.getAccessKey()).isEqualTo("test-access");
+      assertThat(backend.getSecretKey()).isEqualTo("test-secret");
+      assertThat(backend.getConnectTimeoutMillis()).isEqualTo(1234);
+      assertThat(backend.getSocketTimeoutMillis()).isEqualTo(5678);
+      assertThat(backend.getBucketName()).isNull();
+   }
+
+
+
+   /*******************************************************************************
+    ** Provider defaults remain the qbit's defaults, including its 5s/60s timeouts.
+    *******************************************************************************/
+   @Test
+   void testBackendDefaults()
+   {
+      S3BackendMetaData compatible = new ConnectionRuntime(new StorageConnectionConfig("compatible")
+         .withEndpoint("http://localhost:9000")).backendMetaData();
+      assertThat(compatible.getRegion()).isEqualTo("us-east-1");
+      assertThat(compatible.getPathStyleAccess()).isTrue();
+      assertThat(compatible.getChecksumMode()).isEqualTo(S3ChecksumMode.WHEN_REQUIRED);
+      assertThat(compatible.getTlsVerify()).isTrue();
+      assertThat(compatible.getCaCertificatesPem()).isNull();
+      assertThat(compatible.getConnectTimeoutMillis()).isEqualTo(5000);
+      assertThat(compatible.getSocketTimeoutMillis()).isEqualTo(60000);
+
+      S3BackendMetaData aws = new ConnectionRuntime(new StorageConnectionConfig("aws").withProviderType(ProviderType.AWS_S3)).backendMetaData();
+      assertThat(aws.getEndpoint()).isNull();
+      assertThat(aws.getRegion()).isNull();
+      assertThat(aws.getPathStyleAccess()).isFalse();
+      assertThat(aws.getChecksumMode()).isEqualTo(S3ChecksumMode.WHEN_SUPPORTED);
+   }
+
+
+
+   /*******************************************************************************
+    ** Default-chain and web-identity modes must ignore configured static refs.
+    *******************************************************************************/
+   @Test
+   void testChainCredentialsIgnoreStaticRefs(@TempDir Path dir) throws Exception
+   {
+      for(CredentialsMode mode : List.of(CredentialsMode.DEFAULT_CHAIN, CredentialsMode.WEB_IDENTITY))
+      {
+         ConnectionRuntime runtime = new ConnectionRuntime(config(dir).withCredentialsMode(mode)
+            .withSessionTokenRef(reference(dir, "token", "test-session")));
+         try
+         {
+            assertThat(runtime.backendMetaData().getAccessKey()).isNull();
+            assertThat(runtime.backendMetaData().getSecretKey()).isNull();
+            assertThat(runtime.client().serviceClientConfiguration().credentialsProvider()).isInstanceOf(DefaultCredentialsProvider.class);
+         }
+         finally
+         {
+            runtime.close();
+         }
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Missing static refs preserve the prior fallback to the SDK default chain.
+    *******************************************************************************/
+   @Test
+   void testMissingStaticCredentialsFallBack(@TempDir Path dir) throws Exception
+   {
+      ConnectionRuntime runtime = new ConnectionRuntime(config(dir).withSecretKeyRef(null));
+      try
+      {
+         assertThat(runtime.client().serviceClientConfiguration().credentialsProvider()).isInstanceOf(DefaultCredentialsProvider.class);
+      }
+      finally
+      {
+         runtime.close();
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Clients belong to a runtime, never core's shared cache; close rebuilds from
+    ** changed settings and does not affect another connection with equal settings.
+    *******************************************************************************/
+   @Test
+   void testClientOwnershipAndRebuild(@TempDir Path dir) throws Exception
+   {
+      StorageConnectionConfig config = config(dir);
+      ConnectionRuntime runtime = new ConnectionRuntime(config);
+      ConnectionRuntime sibling = new ConnectionRuntime(config);
+      try
+      {
+         S3Client first = runtime.client();
+         assertThat(runtime.client()).isSameAs(first);
+         assertThat(sibling.client()).isNotSameAs(first);
+         assertThat(first.serviceClientConfiguration().endpointOverride()).contains(URI.create("http://localhost:9000"));
+         assertThat(first.serviceClientConfiguration().region()).isEqualTo(Region.US_EAST_1);
+         assertThat(((AwsCredentialsProvider) first.serviceClientConfiguration().credentialsProvider()).resolveCredentials().accessKeyId()).isEqualTo("test-access");
+         assertThat(first.serviceClientConfiguration().requestChecksumCalculation()).isEqualTo(RequestChecksumCalculation.WHEN_REQUIRED);
+         assertThat(first.serviceClientConfiguration().responseChecksumValidation()).isEqualTo(ResponseChecksumValidation.WHEN_REQUIRED);
+         runtime.close();
+         config.withRegion("eu-west-1").withChecksumMode(ChecksumMode.WHEN_SUPPORTED);
+         assertThat(runtime.client()).isNotSameAs(first);
+         assertThat(runtime.client().serviceClientConfiguration().region()).isEqualTo(Region.EU_WEST_1);
+         assertThat(runtime.client().serviceClientConfiguration().requestChecksumCalculation()).isEqualTo(RequestChecksumCalculation.WHEN_SUPPORTED);
+         assertThat(sibling.client().serviceClientConfiguration().region()).isEqualTo(Region.US_EAST_1);
+      }
+      finally
+      {
+         runtime.close();
+         sibling.close();
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Session credentials sign streaming reads/writes and paginator requests.
+    ** Existing request headers survive the bridge's credentials override.
+    *******************************************************************************/
+   @Test
+   void testSessionSigningAndRequestOverrides(@TempDir Path dir) throws Exception
+   {
+      List<Headers> requests = new ArrayList<>();
+      HttpServer server = HttpServer.create(new InetSocketAddress("127.0.0.1", 0), 0);
+      server.createContext("/", exchange ->
+      {
+         requests.add(exchange.getRequestHeaders());
+         exchange.getRequestBody().readAllBytes();
+         byte[] response = (exchange.getRequestURI().getQuery() != null && exchange.getRequestURI().getQuery().contains("list-type")
+            ? "<ListBucketResult><IsTruncated>false</IsTruncated></ListBucketResult>" : "data").getBytes(StandardCharsets.UTF_8);
+         exchange.sendResponseHeaders(200, response.length);
+         exchange.getResponseBody().write(response);
+         exchange.close();
+      });
+      server.start();
+      ConnectionRuntime runtime = new ConnectionRuntime(config(dir)
+         .withEndpoint("http://127.0.0.1:" + server.getAddress().getPort())
+         .withSessionTokenRef(reference(dir, "token", "test-session")));
+      try
+      {
+         GetObjectRequest request = GetObjectRequest.builder().bucket("bucket").key("key")
+            .overrideConfiguration(o -> o.putHeader("x-test-preserved", "yes")).build();
+         assertThat(runtime.client().getObjectAsBytes(request).asUtf8String()).isEqualTo("data");
+         runtime.client().putObject(o -> o.bucket("bucket").key("key"), RequestBody.fromString("upload"));
+         assertThat(runtime.client().listObjectsV2Paginator(ListObjectsV2Request.builder().bucket("bucket").build()).stream().count()).isEqualTo(1);
+         assertThat(requests).hasSize(3);
+         for(Headers headers : requests)
+         {
+            assertThat(headers.getFirst("x-amz-security-token")).isEqualTo("test-session");
+            assertThat(headers.getFirst("Authorization")).contains("Credential=test-access/");
+         }
+         assertThat(requests.getFirst().getFirst("x-test-preserved")).isEqualTo("yes");
+         assertThat(request.overrideConfiguration().orElseThrow().credentialsProvider()).isEmpty();
+      }
+      finally
+      {
+         runtime.close();
+         server.stop(0);
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Presigning keeps its separate public endpoint/key and session-token support.
+    *******************************************************************************/
+   @Test
+   void testPresignerRemainsLocal(@TempDir Path dir) throws Exception
+   {
+      ConnectionRuntime runtime = new ConnectionRuntime(config(dir)
+         .withPresignEndpoint("https://public.example.test")
+         .withPresignAccessKeyRef(reference(dir, "presign-access", "presign-access"))
+         .withPresignSecretKeyRef(reference(dir, "presign-secret", "presign-secret"))
+         .withSessionTokenRef(reference(dir, "token", "test-session")));
+      try
+      {
+         assertThat(runtime.presigner()).isSameAs(runtime.presigner());
+         String url = runtime.presigner().presignGetObject(p -> p.signatureDuration(Duration.ofMinutes(5))
+            .getObjectRequest(g -> g.bucket("bucket").key("key"))).url().toString();
+         assertThat(url).startsWith("https://public.example.test/bucket/key?")
+            .contains("X-Amz-Credential=presign-access%2F", "X-Amz-Security-Token=test-session");
+         assertThat(runtime.presignCredentials()).containsExactly("presign-access", "presign-secret", "us-east-1", "https://public.example.test");
+      }
+      finally
+      {
+         runtime.close();
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Core handles invalid CA bundles and the explicit TLS-verification opt-out.
+    *******************************************************************************/
+   @Test
+   void testTlsSettingsReachCore(@TempDir Path dir) throws Exception
+   {
+      StorageConnectionConfig config = config(dir).withCaBundleRef(reference(dir, "ca", "invalid-pem"));
+      ConnectionRuntime runtime = new ConnectionRuntime(config);
+      assertThatThrownBy(runtime::client).isInstanceOf(IllegalArgumentException.class).hasMessageContaining("caCertificatesPem");
+      config.withTlsVerify(false);
+      try
+      {
+         assertThat(runtime.client()).isNotNull();
+      }
+      finally
+      {
+         runtime.close();
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Deterministic test connection without any external credentials or service.
+    *******************************************************************************/
+   private static StorageConnectionConfig config(Path dir) throws Exception
+   {
+      return (new StorageConnectionConfig("test").withEndpoint("http://localhost:9000")
+         .withAccessKeyRef(reference(dir, "access", "test-access"))
+         .withSecretKeyRef(reference(dir, "secret", "test-secret")));
+   }
+
+
+
+   /*******************************************************************************
+    ** Mounted-file reference for test data, scoped to JUnit's temporary directory.
+    *******************************************************************************/
+   private static String reference(Path dir, String name, String value) throws Exception
+   {
+      Path file = dir.resolve(name);
+      Files.writeString(file, value);
+      return ("${file." + file + "}");
+   }
+}

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