ci: use the shared branch-aware CodeQL worker

Co-Authored-By: Codex <noreply@openai.com>

.circleci/config.yml

@@ -1,65 +1,14 @@
 version: 2.1
-
+setup: true
 orbs:
-  qqq-orb: kingsrook/qqq-orb@0.6.8
-
-commands:
-  configure-package-install:
-    steps:
-      - run:
-          name: Keep package installation unattended
-          command: |
-            # These build machines are discarded after the job.
-            sudo install -d /etc/needrestart/conf.d
-            printf '%s\n' '$nrconf{restart} = "l";' | sudo tee /etc/needrestart/conf.d/99-qqq-ci.conf >/dev/null
-
+  qqq-orb: kingsrook/qqq-orb@0.7.12
 workflows:
-
-  test_only:
+  setup:
     jobs:
-      - qqq-orb/mvn_test_only:
-          pre-steps:
-            - configure-package-install
-          context: [ qqq-maven-registry-credentials ]
-          filters:
-            branches:
-              ignore: /(develop|release/.*|integration.*)/
-            tags:
-              only: []
-
-  publish_snapshot:
-    jobs:
-      - qqq-orb/mvn_publish:
-          pre-steps:
-            - configure-package-install
-          context: [ qqq-maven-registry-credentials ]
-          branch_type: snapshot
-          filters:
-            branches:
-              only:
-                - develop
-
-  publish_release_candidate:
-    jobs:
-      - qqq-orb/mvn_publish:
-          pre-steps:
-            - configure-package-install
-          context: [ qqq-maven-registry-credentials ]
-          branch_type: release_candidate
-          filters:
-            branches:
-              only:
-                - /release\/.*/
-
-  publish_release:
-    jobs:
-      - qqq-orb/mvn_publish:
-          pre-steps:
-            - configure-package-install
-          context: [ qqq-maven-registry-credentials ]
-          branch_type: release
-          filters:
-            branches:
-              ignore: /.*/
-            tags:
-              only: [ /^v\d+\.\d+\.\d+$/ ]
+    - qqq-orb/generate_pipeline:
+        orb_version: 0.7.12
+        filters:
+          branches:
+            ignore: automation/qbit-rc-pending
+          tags:
+            only: /^v\d+\.\d+\.\d+$/

.github/workflows/codeql.yml

@@ -0,0 +1,23 @@
+name: CodeQL
+'on':
+  push:
+    branches:
+    - main
+    - develop
+    - release/**
+    - hotfix/**
+  pull_request: {}
+  schedule:
+  - cron: 41 16 * * 0
+  workflow_dispatch: {}
+permissions:
+  contents: read
+  actions: read
+  security-events: write
+  packages: read
+concurrency:
+  group: codeql-${{ github.event.pull_request.number || github.ref }}
+  cancel-in-progress: ${{ github.event_name == 'pull_request' }}
+jobs:
+  analyze:
+    uses: QRun-IO/qqq-orb/.github/workflows/qbit-codeql.yml@c288a8864fe9054f68410e1d78f5863aea4c4d22

.github/workflows/release-rc.yml

@@ -1,25 +1,74 @@
-# 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:
+'on':
   workflow_dispatch:
     inputs:
       dry_run:
-        description: Print the plan without merging or creating branches
+        description: Plan a cut without changing release state
         type: boolean
         default: false
+      finalize:
+        description: Retry finalization of an existing cut; do not cut another RC
+        type: boolean
+        default: false
+      recover:
+        description: Recover the same published RC after a deployment timeout; never
+          reupload
+        type: boolean
+        default: false
+      circle_build:
+        description: Original Circle publisher build number (recovery only)
+        type: string
+        default: ''
+      merge_sha:
+        description: Existing cut merge_sha (finalize retry only)
+        type: string
+        default: ''
+      bump_sha:
+        description: Existing cut bump_sha (finalize retry only)
+        type: string
+        default: ''
+      branch:
+        description: Existing cut branch (finalize retry only)
+        type: string
+        default: ''
+      version:
+        description: Existing cut version (finalize retry only)
+        type: string
+        default: ''
   pull_request:
-    types: [closed]
-    branches: [develop]
-
+    types:
+    - closed
+    branches:
+    - develop
+  repository_dispatch:
+    types:
+    - qbit-rc-published
 permissions:
   contents: write
-
+  actions: read
+  checks: read
 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
+    if: github.event_name == 'workflow_dispatch' || github.event_name == 'repository_dispatch'
+      || (github.event_name == 'pull_request' && github.event.pull_request.merged
+      == true && github.event.pull_request.base.ref == 'develop' && contains(github.event.pull_request.labels.*.name,
+      'release:rc'))
+    uses: QRun-IO/qqq-orb/.github/workflows/qbit-release-rc.yml@cc30f599e63c8e41629c99d23395bac1adb8bc44
     with:
-      dry_run: ${{ github.event_name == 'workflow_dispatch' && inputs.dry_run }}
+      dry_run: ${{ github.event_name == 'workflow_dispatch' && inputs.dry_run || false
+        }}
+      finalize: ${{ github.event_name == 'repository_dispatch' || (github.event_name
+        == 'workflow_dispatch' && inputs.finalize) }}
+      recover: ${{ github.event_name == 'workflow_dispatch' && inputs.recover || false
+        }}
+      circle_build: ${{ github.event_name == 'workflow_dispatch' && inputs.circle_build
+        || '' }}
+      merge_sha: ${{ github.event_name == 'repository_dispatch' && github.event.client_payload.merge_sha
+        || inputs.merge_sha || '' }}
+      bump_sha: ${{ github.event_name == 'repository_dispatch' && github.event.client_payload.bump_sha
+        || inputs.bump_sha || '' }}
+      branch: ${{ github.event_name == 'repository_dispatch' && github.event.client_payload.branch
+        || inputs.branch || '' }}
+      version: ${{ github.event_name == 'repository_dispatch' && github.event.client_payload.version
+        || inputs.version || '' }}
+    secrets: inherit

.qqq-ci.yml

@@ -0,0 +1,16 @@
+profile: qbit
+orb_version: 0.7.12
+default_branch: develop
+java_version: '21'
+browsers: true
+api_checks: false
+docs_only: false
+release_on_main: false
+release_tag_policy: stable
+test_branch_policy: non-release-no-integration
+publish:
+  snapshot: true
+  release_candidate: true
+  release: true
+  asciidoc: false
+release_completion: true

CHANGELOG.md

@@ -4,8 +4,24 @@ All notable changes are documented here, following [Keep a Changelog](https://ke
 
 ## [Unreleased]
 
+### Fixed
+
+- `worm-audit:13` failed with `permission denied for function worm_audit_purge` when the migrating role owns the function but had revoked its own EXECUTE (a runtime role that owns the schema, as on CNPG; PostgreSQL refuses `CREATE OR REPLACE` then). The changeset now grants EXECUTE back to the migrating role only if it lacks it, replaces the function, and revokes it again in the same transaction. Found by Website-Backend's CNPG-shaped changelog test.
+- `worm-audit:13` had a comment line starting with `-- changeset`, which Liquibase formatted-SQL parsing rejects as a malformed directive. The comment is reworded, and `CanonicalAndCryptoTest` now requires every `-- changeset` line to be a real directive.
+- Chain writer database load. Each 30-second tick re-read the whole late-detail lookback (24 h of `audit_detail`) and probed the GIN array index once per detail (18.4 s and 1.78M buffer hits per tick on a 120k-detail production database), and re-scanned every settled audit header for the unchained anti-join. Coverage now lives in `worm_audit_chain_detail` (`worm-audit:13`): one insert-only row per covered detail, written by the chain trigger in the same statement as its link and backfilled from the existing chain, so the late-detail check is a primary-key probe. Each tick also reads only ids above per-JVM sweep floors (rows settled since the last complete sweep); after a restart and once an hour a tick sweeps everything, as before. Link hash inputs, `detail_ids`, the verifier and exports are unchanged.
+- The chain trigger's overlap check (`EXISTS ... && ...`) ran as a cached generic plan that sequentially scanned all of `worm_audit_chain` for every inserted link (55k sequential scans, 1.2B tuples read in production, about 80 ms per link and growing). The trigger now inserts the coverage rows and lets their primary key reject a detail covered twice.
+- Verification counts detail coverage by aggregating the chain's own detail lists once instead of one index probe per detail, and breaks when the coverage table disagrees with the chain.
+
+- Startup on hosts whose default authentication requires a token (OAuth2, Auth0): `produce()` read the settings singleton under a plain tokenless `QSession`, which failed session validation (`JWTDecodeException: The token is null`) and stopped the host. The startup read now runs as the core `QSystemUserSession`, still honoring the persisted schedules. `TokenRequiredHostTest` runs production under a token-required test authentication module.
+
 ### Added
 
+- Foreign keys (`worm-audit:11-12`), `ON DELETE RESTRICT` only: chain link -> core audit header, sealed export -> its first and last checkpoints, export verification -> its export, with their indexes. Link references (ranges, break link, purge anchor) stay unconstrained because the retention purge deletes the links they outlive. Pre-existing orphans are copied to `qqq_fk_quarantine` (`KEPT`), never modified, and leave that constraint `NOT VALID`. Re-apply the rendered DDL (idempotent).
+- Guided empty states: `help/qbit-worm-audit.yaml` (QQQ help-bundle format) gives the app and all six tables a summary and a "How rows get created" section, applied by the `WormAuditHelp` stand-in (`STAND-IN(core=QRun-IO/qqq#1198, removeBy=4.1.0-RC.7)`) without replacing host help. `WormAuditTablesGuardrailTest` pins runtime use, seeding or a documented reason, and help for every table; `WormAuditSettingsSeedTest` runs the script's settings seed on H2.
+- RC.2 settings: editable, FIELD-audited singleton with insert-only defaults, validation, separate edit permission and a 30-second effective-settings cache. Schedules load at startup.
+- GOVERNANCE exports with 30-day default retention; per-export retention verification, administrator-bypass diagnostics, home warnings and documented COMPLIANCE transition.
+- New `worm-audit:10` settings migration; historical changesets remain unchanged.
+
 - Phase (c): configurable WORM Audit app home, full-width summary widget and optional host health card; permission-aware navigation and content-free, responsive light/dark summaries.
 - Chain backlog/settling counts, verification and export record links, sealed-range gaps/lag, retention eligibility and recent purge/refusal history.
 - PostgreSQL guard/ownership/purge-function diagnostics, signing/trust warnings, tri-state Object Lock capability and stale daily-verification warnings.
@@ -22,3 +38,7 @@ All notable changes are documented here, following [Keep a Changelog](https://ke
 ### Fixed
 
 - Keep runtime signing/trust objects and storage suppliers out of serialized metadata so signed-chain record child lists render successfully.
+
+### Deprecated
+
+- `WormAuditSettings.seed(config)` is deprecated for PostgreSQL hosts, whose database script (`worm-audit:10`) already seeds row 1; it remains supported for memory and H2 hosts. `read()` falls back to the configured values while the row is absent.

README.md

@@ -3,8 +3,8 @@
 Phases (a)–(c) provide PostgreSQL append-only enforcement, an asynchronous audit hash chain,
 Ed25519 checkpoints, live and sealed-export verification actions, and daily jobs with core QAlerts signals.
 It hashes exactly the audit values core stored; it does not read application records or
-secured-data keys. Sealed JSONL exports use qbit-object-storage 0.1.0-RC.2 and COMPLIANCE
-Object Lock. The WORM Audit app home summarizes chain health, exports, retention and setup.
+secured-data keys. Sealed JSONL exports use qbit-object-storage 0.1.0-RC.2 and configurable
+Object Lock (GOVERNANCE for 30 days by default). The WORM Audit app home summarizes chain health, exports, retention and setup.
 
 Requires Java 21, Maven wrapper 3.9.11 and QQQ 4.1.0-RC.5. PostgreSQL provides enforcement;
 memory/H2 support tamper-evidence tests without database enforcement.
@@ -12,8 +12,9 @@ memory/H2 support tamper-evidence tests without database enforcement.
 ## Tables and permissions
 
 All sidecar tables use the configured host backend, snake_case fields, BIGINT identifiers,
-record sections, and full table-name permissions. They do not audit themselves. The app lists
-Verifications, Sealed Exports, Checkpoints, Audit Chain, and Purges in that order.
+record sections, and full table-name permissions. Immutable sidecars do not audit themselves.
+The editable Settings singleton uses an INTEGER ID and FIELD-level core audit. The app lists
+Verifications, Sealed Exports, Checkpoints, Audit Chain, Purges, and Settings in that order.
 
 | Table | Purpose | Permission |
 |---|---|---|
@@ -22,18 +23,19 @@ Verifications, Sealed Exports, Checkpoints, Audit Chain, and Purges in that orde
 | `worm_audit_checkpoint` | Signed ranges and Merkle roots | `worm_audit_checkpoint.read` |
 | `worm_audit_chain` | Contiguous links and explicit detail coverage | `worm_audit_chain.read` |
 | `worm_audit_purge` | Refusals, removal counts and retention anchors | `worm_audit_purge.read` |
+| `worm_audit_settings` | Editable singleton; every edit is audited and chained | `worm_audit_settings.read`; editing needs `worm_audit_settings.edit` **and** `worm_audit.settings` |
 
 `worm_audit.verify` authorizes the standard streamed ETL **Verify audit chain** action.
 It also authorizes **Verify sealed export** on selected export records. `worm_audit.export`
 authorizes **Export now** on the export table. Processes are table/record actions or schedules;
 none is a navigation entry.
-Insert/update/delete capabilities are absent on sidecar tables; update/delete capabilities
+Insert/update/delete capabilities are absent on immutable sidecar tables; Settings allows updates only; update/delete capabilities
 are removed from all four core audit tables. Internal appends use an explicit permission bypass.
 Checkpoint, export and verification record screens have a bounded chain-link child list;
 range endpoints and export/checkpoint references link to their records.
 
 The table metadata customizer can configure host ESB publications. Quick Search registration
-is host-owned through `QuickSearchQBitConfig.searchableTables`; add all five tables.
+is host-owned through `QuickSearchQBitConfig.searchableTables`; add the desired tables.
 Verification and export inserts use QQQ's record-change listener SPI,
 so an installed ESB publisher receives those events when the host configures its destinations
 on these two tables. The qbit defines no destination and configures no chain-link publication.
@@ -70,19 +72,84 @@ to verification. It does not itself break the chain. Signing requires a matching
 Rotate by adding a new pin and changing the supplier; retain old pins for old checkpoints.
 Never store the private key in a database row or checked-in configuration.
 
+## Settings
+
+Open **WORM Audit → Settings**, record **1** (`worm_audit_settings`, labelled WORM Audit Settings).
+This is a standard editable QQQ table. New records and deletion are disabled; a pre-insert
+check and database constraint require ID 1. Every edit uses core FIELD-level auditing, so
+the change enters the audit chain. `modify_date` and `modified_by` are stamped by the server.
+Keep notes free of sensitive data; notes are audited too.
+
+### Starter defaults and empty screens
+
+The only seeded table is `worm_audit_settings`, by the database script (`WormAuditDdl.postgres(config)`, changeset `worm-audit:10`): `INSERT ... SELECT ... WHERE NOT EXISTS`, so it adds row 1 from the host's configured values only when absent and never overwrites an edit. There is no separate defaults changelog and no extra include line. If the row is absent the qbit runs on the configured values (`WormAuditSettings.read()` falls back), so nothing stops, but run the script to make the values visible and editable. The chain, checkpoint, export, verification and purge tables are logs written by the qbit's jobs and the database owner's purge function, and start empty.
+
+Every table, and the app, ships help (`help/qbit-worm-audit.yaml`, the QQQ help-bundle format) saying what it is for and how its rows get created; the Next UI shows it as the empty state. A host's own help is never replaced. `WormAuditTablesGuardrailTest` and `WormAuditSettingsSeedTest` pin this.
+
+| Field | Default | Validation / effect |
+|---|---|---|
+| `audit_retention_years` | 6 | 1–100; home next eligible purge date |
+| `export_lock_mode` | GOVERNANCE | GOVERNANCE or COMPLIANCE; new exports |
+| `export_retention_days` | 30 | 1–36500; new exports |
+| `checkpoint_every_links` | 10000 | Positive integer; new checkpoint ranges |
+| `checkpoint_every_minutes` | 60 | Positive integer; checkpoint cadence |
+| `chain_interval_seconds` | 30 | Positive integer; **takes effect after restart** |
+| `settle_horizon_seconds` | 60 | Nonnegative integer; chaining and verification |
+| `late_detail_lookback_hours` | 24 | Positive integer; late-detail polling |
+| `export_cron` | `0 0 2 * * ?` | Quartz cron, UTC; **takes effect after restart** |
+| `verify_cron` | `0 0 3 * * ?` | Quartz cron, UTC; **takes effect after restart** |
+| `notes` | Empty | Administrative notes |
+| `modify_date`, `modified_by` | Server supplied | Last edit time and user |
+
+Config values supply defaults for missing rows or null fields; a missing settings table also
+falls back to config. Other database failures fail closed. `exportLockMode` and
+`exportRetentionDays` are the corresponding config defaults. Runtime reads cache the row
+for at most 30 seconds per application instance; local QQQ edits invalidate that cache.
+Each run reads effective settings. Startup schedules read the existing row, or config when
+absent. Historical checkpoints remain valid after changing their creation cadence.
+
+Changeset `worm-audit:10` creates and seeds the table with `INSERT ... SELECT ... WHERE NOT
+EXISTS`; rerunning it never overwrites an administrator change. The renderer seeds from the
+host config. Memory and H2 hosts, whose settings table cannot take generic inserts, can still call
+`WormAuditSettings.seed(config)` for the same insert-only behavior during single-threaded
+startup (deprecated for PostgreSQL hosts, which get the row from the script).
+
+`audit_retention_years` does **not** rewrite the database purge function. The home warns if
+it differs from the compiled interval; changing that interval requires a reviewed migration.
+The home also warns when export retention is shorter than audit retention and while
+GOVERNANCE permits administrator bypass deletion. The runtime role receives SELECT, INSERT
+and UPDATE on Settings, with no DELETE or TRUNCATE grant and no append-only triggers.
+
+## Switching sealed exports to COMPLIANCE
+
+Edit `export_lock_mode` to **COMPLIANCE** in Settings. New exports use COMPLIANCE once the
+settings cache refreshes (within 30 seconds); existing GOVERNANCE objects keep their mode
+unless an administrator explicitly extends their retention and upgrades their mode. The
+verifier continues to compare each object with its export row's recorded mode and date, so
+an out-of-band mode change is reported as a mismatch. The bucket default retention should
+match the chosen mode and retention days, although the exporter always sends explicit values.
+The settings change is audited and chained. **COMPLIANCE cannot be undone**: protected
+versions cannot be deleted or have retention shortened until expiry, including by administrators.
+
 ## App home
 
 Open `/app/worm_audit` (or `/app/admin/worm_audit` with `parentAppName="admin"`).
 The full-width `wormAuditHome` HTML widget uses the standard `/qqq/v1/widget` API.
 Hosts can add `wormAuditHealthCard` to their own dashboard. Both widgets and the app
 require read permission on at least one WORM table, using the host's table permission rules.
+To embed WORM in an existing host app, set `withIncludeApp(false)` and configure
+`appName`, `appLabel` and optional `parentAppName` for that host app. The host owns
+its navigation, home widget placement and app help; the qbit does not create or replace
+the app or fill its help. Both widgets and all table help, permissions, guards,
+processes and schedules remain registered. The health card links to the configured
+host app. Standalone app creation remains enabled by default.
 The aggregate home shows only identifiers, counts, times, shortened hashes and reasons;
 it never renders audit messages or field values. Record links retain the target table's permissions.
 
 - Chain status: live link count, latest link time, settled unchained backlog and settling
   count at the configured horizon, latest verification and first open chain break.
   A subsequent full scheduled CHAIN PASS clears a reported break; a selected USER PASS does not.
-- Export status: latest sealed range, COMPLIANCE retention, link/time lag, coverage holes
+- Export status: current lock mode and retention days, latest sealed range and its recorded retention, link/time lag, coverage holes
   between the purge anchor and sealed head, and bucket Object Lock capability. The unsealed
   trailing suffix is lag, not a gap. Provider failures show UNKNOWN with a value-free reason.
 - Retention: configured years, purge anchor, oldest live audit and its next eligible purge date.
@@ -207,8 +274,11 @@ Install qbit-object-storage and configure `exportBucketAlias` (default `worm_aud
 `BucketRole.BACKUP` bucket. **Object Lock must be enabled when the bucket is created**.
 The exporter requires the bucket's `OBJECT_LOCK` capability to be `SUPPORTED`; run the
 object-storage read-only capability probe during setup. Unknown, partial and unsupported
-capabilities all refuse sealing. Every object receives explicit **COMPLIANCE** retention
-until the sealing time plus `retentionYears` (six by default), checked by HEAD after PUT.
+capabilities all refuse sealing. Every object receives the effective `export_lock_mode` and retention until sealing time plus
+`export_retention_days` (GOVERNANCE and 30 days by default), checked by HEAD after PUT. The
+export row records both values. Verification checks that historical mode and retain-until,
+regardless of subsequent settings changes. Missing GOVERNANCE objects are reported as breaks
+with a possible administrator bypass-removal explanation.
 
 The runtime IAM policy needs `s3:PutObject`, `s3:GetObject`, `s3:ListBucket`,
 `s3:GetObjectRetention`, `s3:PutObjectRetention`, and
@@ -284,8 +354,8 @@ $env:CI = 'true'
 ./mvnw.cmd -o verify
 ```
 
-MinIO export acceptance uses the same `RequiresDockerCondition`. It checks COMPLIANCE
-retention, denied version deletion, overwrite version preservation, export verification,
+MinIO export acceptance uses the same `RequiresDockerCondition`. It checks GOVERNANCE
+with 30-day retention and a COMPLIANCE export, denied version deletion (without bypass for GOVERNANCE), overwrite version preservation, export verification,
 exact live-tamper findings through the record action, and refusal of a bucket without lock:
 
 ```powershell

UPGRADE.md

@@ -0,0 +1,11 @@
+# Upgrading qbit-worm-audit
+
+## Detail coverage table (0.1.0-RC.4)
+
+Apply changeset `worm-audit:13` from the rendered DDL (or your copy of it) before or with the new jar. It takes the chain advisory lock, creates `worm_audit_chain_detail` with its append-only guards and grants, backfills it from the existing chain, and replaces `worm_audit_reject()`, `worm_audit_detail_coverage()` and `worm_audit_purge()`. The jar requires the table: its chain writer reads it every tick. The GIN index `worm_audit_chain_detail_coverage` is no longer used but is kept so an RC.3 writer still running during a rolling deploy keeps an indexed probe; a later changeset drops it. Memory and H2 hosts need no migration beyond creating the new table (the writer fills it).
+
+## Table help bundle (unreleased)
+
+Nothing to do. The qbit applies `help/qbit-worm-audit.yaml` at the end of `produce()`, filling only help slots the host has not set. No schema or seed changes: the settings row is still seeded by the database script (changeset `worm-audit:10`) and nothing else is seeded.
+
+`WormAuditSettings.seed(config)` is now `@Deprecated` for PostgreSQL hosts (the script seeds row 1). It still works and remains the way for memory and H2 hosts to create row 1. The settings reader falls back to the configured values while the row is absent.

docs/DESIGN.md

@@ -111,6 +111,17 @@ file into their changelog (kof22: `changesets/features/YYYY-MM-DD-worm-audit.sql
 qbit tables, the functions, the triggers, the purger role (`CREATE ROLE ... NOLOGIN` when it is
 missing) and the grants. It is idempotent (`CREATE OR REPLACE`, `DROP TRIGGER IF EXISTS`).
 
+Foreign keys (`worm-audit:11-12`) are `ON DELETE RESTRICT` only, so nothing cascades into or out
+of evidence: `worm_audit_chain.audit_id` -> the core audit header (the purge deletes details, then
+links, then headers, in that order), `worm_audit_export.from/to_checkpoint_id` -> checkpoints and
+`worm_audit_verification.export_id` -> exports (neither is ever deleted). References to chain links
+(checkpoint, export and verification ranges, `first_break_link_id`, the purge anchor) are not
+foreign keys: the purge deletes the sealed prefix those rows outlive. On upgrade, rows whose parent
+is already missing are evidence and are never changed: they are copied to `qqq_fk_quarantine`
+(action `KEPT`) and that constraint stays `NOT VALID` (new rows are still checked). With the
+foreign key, a plain `TRUNCATE` of `audit` (or of checkpoints or exports) is refused by PostgreSQL
+before the guard trigger; `TRUNCATE ... CASCADE` reaches the guards, which refuse it.
+
 ### 4.5 Threat model summary
 
 | Actor | Can modify audit rows? | Detected by |
@@ -181,7 +192,13 @@ chain would need the canonical form in SQL. The design instead uses **one chaine
   `kind = LATE_DETAIL`, covering exactly those detail ids. Verification flags any detail not
   covered by exactly one link.
 - Unique constraints back the lock: `UNIQUE (audit_id) WHERE kind = 'AUDIT'`, and the detail
-  coverage.
+  coverage: `worm_audit_chain_detail (detail_id PRIMARY KEY, chain_id)`, one append-only row per
+  covered detail, written by the chain insert trigger in the same statement as the link. The
+  writer finds uncovered details with a primary-key probe of it; verification still counts
+  coverage from the links' own `detail_ids` and breaks if the table disagrees.
+- Each tick reads only rows above per-JVM sweep floors: the highest audit and detail ids seen at
+  least (settle horizon + 5 minutes) before the last complete sweep. After a restart and once an
+  hour a tick ignores the floors, so a row whose transaction commits even later is still chained.
 
 Why not per-partition chains: they make each partition's chain shorter but multiply heads,
 checkpoints and verification state, and they still need a writer per partition. One chainer
@@ -228,6 +245,10 @@ installed the alerts go to the log.
 ## 6. Sealed exports
 
 ### 6.1 Export job
+
+**Default superseded by Owner decisions (2026-10-14) below:** lock mode and export retention
+now come from Settings, defaulting to GOVERNANCE and 30 days. The original decision text is
+retained for history.
 Daily at 02:00 (configurable), and by the table action "Export now" on `worm_audit_export`:
 1. Range: links after the last export's `to_link_id`, up to the latest checkpoint. Exports are
    contiguous by construction. The app home flags a gap (a range not covered) and lag.
@@ -386,3 +407,33 @@ registers as a `MiddlewareSpecExtension` under `/qqq/v1/qbit/worm-audit/...`
 
 These are owner-run (Praetor and cluster-gitops are applied by the owner). The qbit only
 documents them.
+
+## Owner decisions (2026-10-14)
+
+The 2026-10-12 decisions above remain the historical design. These decisions supersede the
+COMPLIANCE/six-year export default in §6.1 and the corresponding default assumptions in
+§§2, 4.5, 9, 11, 12 and the §14 infrastructure proposal.
+
+- Settings live in the application as the standard QQQ `worm_audit_settings` table, record
+  ID 1, labelled WORM Audit Settings and navigated as Settings after Purges. It allows edits,
+  has no append-only triggers, and uses FIELD-level core audit so every change is chained.
+  Reading needs `worm_audit_settings.read`; editing additionally needs
+  `worm_audit_settings.edit` and `worm_audit.settings`. New IDs and deletion are refused.
+- Defaults are seeded insert-only in new changeset `worm-audit:10`; existing changesets are
+  unchanged. An administrator's edits survive subsequent seeding. Config supplies defaults
+  when a row/field/table is absent. Runtime reads cache at most 30 seconds, scoped to the
+  QInstance. Chain interval and UTC Quartz export/verify schedules take effect after restart.
+- kof22 starts with **GOVERNANCE, 30 days** for sealed exports. An administrator with
+  `s3:BypassGovernanceRetention` can remove those copies. This is deliberately reversible;
+  it does not provide COMPLIANCE's irreversible retention. Runtime credentials still receive
+  no delete or bypass permission. Bucket defaults should match the selected settings.
+- Changing Settings to COMPLIANCE affects new exports. Existing GOVERNANCE versions retain
+  their original mode unless an administrator extends/upgrades them. Verification checks
+  the catalog's recorded mode and date, so external mode changes are reported as mismatches.
+  COMPLIANCE cannot be undone. The settings change itself is audited and chained.
+- Audit retention defaults to six years independently of export retention. The home warns
+  about a shorter export horizon and a mismatch with the purge function's compiled interval.
+  Updating the setting does not modify the purge function; that still requires a migration.
+
+The README Settings table specifies all fields, defaults, bounds and timing. Signing keys,
+public-key pins, bucket alias, prefix and backend remain host configuration, never settings.

docs/ddl/worm-audit-postgres.sql

@@ -324,3 +324,233 @@ CREATE INDEX IF NOT EXISTS worm_audit_chain_detail_coverage ON public.worm_audit
 DROP TRIGGER IF EXISTS worm_audit_detail_coverage_guard ON public.worm_audit_chain;
 CREATE TRIGGER worm_audit_detail_coverage_guard BEFORE INSERT ON public.worm_audit_chain
    FOR EACH ROW EXECUTE FUNCTION public.worm_audit_detail_coverage();
+
+-- changeset worm-audit:10
+-- Editable singleton: deliberately excluded from append-only triggers.
+CREATE TABLE IF NOT EXISTS public.worm_audit_settings (
+   id integer PRIMARY KEY CHECK (id = 1),
+   audit_retention_years integer NOT NULL DEFAULT 6 CHECK (audit_retention_years BETWEEN 1 AND 100),
+   export_lock_mode text NOT NULL DEFAULT 'GOVERNANCE' CHECK (export_lock_mode IN ('GOVERNANCE', 'COMPLIANCE')),
+   export_retention_days integer NOT NULL DEFAULT 30 CHECK (export_retention_days BETWEEN 1 AND 36500),
+   checkpoint_every_links integer NOT NULL DEFAULT 10000 CHECK (checkpoint_every_links > 0),
+   checkpoint_every_minutes integer NOT NULL DEFAULT 60 CHECK (checkpoint_every_minutes > 0),
+   chain_interval_seconds integer NOT NULL DEFAULT 30 CHECK (chain_interval_seconds > 0),
+   settle_horizon_seconds integer NOT NULL DEFAULT 60 CHECK (settle_horizon_seconds >= 0),
+   late_detail_lookback_hours integer NOT NULL DEFAULT 24 CHECK (late_detail_lookback_hours > 0),
+   export_cron text NOT NULL DEFAULT '0 0 2 * * ?',
+   verify_cron text NOT NULL DEFAULT '0 0 3 * * ?',
+   notes text,
+   modify_date timestamptz NOT NULL DEFAULT now(),
+   modified_by text NOT NULL DEFAULT current_user
+);
+REVOKE DELETE, TRUNCATE ON public.worm_audit_settings FROM "app";
+GRANT SELECT, INSERT, UPDATE ON public.worm_audit_settings TO "app";
+INSERT INTO public.worm_audit_settings
+   (id, audit_retention_years, export_lock_mode, export_retention_days, checkpoint_every_links,
+    checkpoint_every_minutes, chain_interval_seconds, settle_horizon_seconds, late_detail_lookback_hours,
+    export_cron, verify_cron)
+SELECT 1, 6, 'GOVERNANCE', 30, 10000, 60, 30, 60, 24, '0 0 2 * * ?', '0 0 3 * * ?'
+WHERE NOT EXISTS (SELECT 1 FROM public.worm_audit_settings WHERE id = 1);
+
+-- changeset worm-audit:11
+-- comment: Index the foreign key columns added by worm-audit:12 (worm_audit_chain.audit_id already leads worm_audit_chain_audit_all).
+CREATE INDEX IF NOT EXISTS worm_audit_export_from_checkpoint ON public.worm_audit_export(from_checkpoint_id);
+CREATE INDEX IF NOT EXISTS worm_audit_export_to_checkpoint ON public.worm_audit_export(to_checkpoint_id);
+CREATE INDEX IF NOT EXISTS worm_audit_verification_export ON public.worm_audit_verification(export_id);
+
+-- changeset worm-audit:12 splitStatements:false
+-- comment: Foreign keys, ON DELETE RESTRICT only: nothing cascades into or out of audit evidence. A chain link needs its audit header (the purge deletes links before headers); a sealed export needs its signed checkpoints and an export verification its export (neither is ever deleted). Links referenced by checkpoints, exports, verifications and purges are not foreign keys: the purge deletes the sealed prefix those rows outlive. Rows whose parent is already missing are evidence and are never changed: they are copied to qqq_fk_quarantine (action KEPT) and the constraint is left NOT VALID (enforced for new rows only).
+DO $$
+DECLARE
+   fk record;
+   orphans bigint;
+BEGIN
+   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);
+   FOR fk IN SELECT * FROM (VALUES
+      ('fk_worm_audit_chain_audit_id', 'worm_audit_chain', 'audit_id', '"audit"'),
+      ('fk_worm_audit_export_from_checkpoint_id', 'worm_audit_export', 'from_checkpoint_id', 'public.worm_audit_checkpoint'),
+      ('fk_worm_audit_export_to_checkpoint_id', 'worm_audit_export', 'to_checkpoint_id', 'public.worm_audit_checkpoint'),
+      ('fk_worm_audit_verification_export_id', 'worm_audit_verification', 'export_id', 'public.worm_audit_export')
+   ) AS t(name, child, col, parent) LOOP
+      IF NOT EXISTS (SELECT FROM pg_constraint WHERE conname = fk.name) THEN
+         EXECUTE format('INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset)'
+            || ' SELECT %L, %L, c.id::text, c.%I::text, ''KEPT'', row_to_json(c)::text, ''worm-audit:12'' FROM public.%I c'
+            || ' WHERE c.%I IS NOT NULL AND NOT EXISTS (SELECT FROM %s p WHERE p.id = c.%I)', fk.child, fk.col, fk.col, fk.child, fk.col, fk.parent, fk.col);
+         GET DIAGNOSTICS orphans = ROW_COUNT;
+         EXECUTE format('ALTER TABLE public.%I ADD CONSTRAINT %I FOREIGN KEY (%I) REFERENCES %s (id) ON DELETE RESTRICT NOT VALID', fk.child, fk.name, fk.col, fk.parent);
+         IF orphans = 0 THEN
+            EXECUTE format('ALTER TABLE public.%I VALIDATE CONSTRAINT %I', fk.child, fk.name);
+         END IF;
+      END IF;
+   END LOOP;
+END;
+$$;
+
+-- changeset worm-audit:13 splitStatements:false
+-- comment: Normalized detail coverage. One insert-only row per audit_detail id covered by a chain link, written by the coverage trigger in the same statement as the link, so the chain writer probes a primary key instead of the GIN array index. Append-only like the chain, with no foreign keys (the owner cannot UPDATE WORM tables, so FK checks would be denied). The advisory lock keeps a running chainer from appending between the backfill and the new trigger body.
+DO $$
+BEGIN
+   PERFORM pg_advisory_xact_lock(86753090421);
+END;
+$$;
+CREATE TABLE IF NOT EXISTS public.worm_audit_chain_detail (
+   detail_id bigint PRIMARY KEY,
+   chain_id bigint NOT NULL,
+   CHECK (detail_id >= 0),
+   CHECK (chain_id > 0)
+);
+CREATE INDEX IF NOT EXISTS worm_audit_chain_detail_chain ON public.worm_audit_chain_detail(chain_id);
+-- The first link wins if a tampered chain covers a detail twice; the verifier still reports the duplicate from the chain itself.
+INSERT INTO public.worm_audit_chain_detail(detail_id, chain_id)
+   SELECT x.id::bigint, c.id FROM public.worm_audit_chain c CROSS JOIN LATERAL unnest(string_to_array(c.detail_ids, ',')) AS x(id)
+   WHERE c.detail_ids <> '' ORDER BY c.id
+   ON CONFLICT (detail_id) DO NOTHING;
+
+CREATE OR REPLACE FUNCTION public.worm_audit_reject() RETURNS trigger
+LANGUAGE plpgsql SET search_path = pg_catalog, public, pg_temp SET timezone = 'UTC' AS $fn$
+DECLARE
+   old_time timestamptz;
+   function_owner name;
+   stack text;
+   row_identity text := 'all';
+BEGIN
+   IF TG_OP = 'DELETE' AND current_setting('worm_audit.purging', true) = 'on' THEN
+      SELECT pg_get_userbyid(proowner) INTO function_owner FROM pg_proc
+         WHERE oid = 'public.worm_audit_purge(timestamptz)'::regprocedure;
+      GET DIAGNOSTICS stack = PG_CONTEXT;
+      IF current_user = function_owner AND position('function worm_audit_purge(timestamp with time zone)' in stack) > 0 THEN
+         IF TG_RELID = '"audit"'::regclass THEN
+            old_time := OLD.timestamp;
+         ELSIF TG_RELID = '"audit_detail"'::regclass THEN
+            SELECT timestamp INTO old_time FROM "audit" WHERE id = OLD.audit_id;
+         ELSIF TG_RELID = 'public.worm_audit_chain'::regclass THEN
+            SELECT timestamp INTO old_time FROM "audit" WHERE id = OLD.audit_id;
+         ELSIF TG_RELID = 'public.worm_audit_chain_detail'::regclass THEN
+            SELECT a.timestamp INTO old_time FROM public.worm_audit_chain c JOIN "audit" a ON a.id = c.audit_id WHERE c.id = OLD.chain_id;
+         END IF;
+         IF old_time < now() - interval '6 years' THEN
+            RETURN OLD;
+         END IF;
+      END IF;
+   END IF;
+   IF TG_LEVEL = 'ROW' THEN
+      -- Record fields resolve per statement, so each table's key is read in its own branch.
+      IF TG_RELID = 'public.worm_audit_chain_detail'::regclass THEN
+         row_identity := OLD.detail_id::text;
+      ELSE
+         row_identity := OLD.id::text;
+      END IF;
+   END IF;
+   RAISE EXCEPTION USING ERRCODE = 'P0001',
+      MESSAGE = format('worm_audit: audit rows are write-once; %s of "%s" is not allowed (id=%s). Expired rows are removed only by worm_audit_purge().',
+         TG_OP, TG_TABLE_NAME, row_identity),
+      HINT = 'see qbit-worm-audit docs/DESIGN.md section 4';
+END;
+$fn$;
+
+CREATE OR REPLACE FUNCTION public.worm_audit_detail_coverage() RETURNS trigger
+LANGUAGE plpgsql SET search_path = pg_catalog, public, pg_temp AS $fn$
+BEGIN
+   -- Serialize even direct SQL appends, then record coverage in the same statement as the link.
+   -- The primary key rejects a detail covered twice, within this link or by an earlier one. The
+   -- previous EXISTS overlap check ran as a cached generic plan that chose a sequential scan of
+   -- the whole chain for every inserted link.
+   PERFORM pg_advisory_xact_lock(86753090421);
+   IF NEW.detail_ids = '' THEN
+      RETURN NEW;
+   END IF;
+   BEGIN
+      INSERT INTO public.worm_audit_chain_detail(detail_id, chain_id)
+         SELECT d, NEW.id FROM unnest(string_to_array(NEW.detail_ids, ',')::bigint[]) AS d;
+   EXCEPTION WHEN unique_violation THEN
+      RAISE EXCEPTION USING ERRCODE = '23505', MESSAGE = 'worm_audit: audit detail is already covered by the chain';
+   END;
+   RETURN NEW;
+END;
+$fn$;
+
+-- The array index worm_audit_chain_detail_coverage is no longer used by this release. It stays until
+-- a later migration drops it, so a previous-release writer still running during a rolling deploy keeps its indexed probe.
+
+DROP TRIGGER IF EXISTS worm_audit_row_guard ON public.worm_audit_chain_detail;
+CREATE TRIGGER worm_audit_row_guard BEFORE UPDATE OR DELETE ON public.worm_audit_chain_detail
+   FOR EACH ROW EXECUTE FUNCTION public.worm_audit_reject();
+DROP TRIGGER IF EXISTS worm_audit_truncate_guard ON public.worm_audit_chain_detail;
+CREATE TRIGGER worm_audit_truncate_guard BEFORE TRUNCATE ON public.worm_audit_chain_detail
+   FOR EACH STATEMENT EXECUTE FUNCTION public.worm_audit_reject();
+REVOKE UPDATE, DELETE, TRUNCATE ON public.worm_audit_chain_detail FROM "app";
+GRANT SELECT, INSERT ON public.worm_audit_chain_detail TO "app";
+
+-- Same function as worm-audit:5, except that coverage rows are locked and purged before their links.
+-- The migrating role may have revoked its own EXECUTE (a runtime role that owns the schema, as on CNPG);
+-- PostgreSQL then refuses CREATE OR REPLACE. Grant it back for this statement only, then restore.
+DO $$
+BEGIN
+   IF NOT has_function_privilege(current_user, 'public.worm_audit_purge(timestamptz)', 'EXECUTE') THEN
+      EXECUTE format('GRANT EXECUTE ON FUNCTION public.worm_audit_purge(timestamptz) TO %I', current_user);
+      PERFORM set_config('worm_audit.purge_regrant', 'on', true);
+   END IF;
+END;
+$$;
+CREATE OR REPLACE FUNCTION public.worm_audit_purge(p_cutoff timestamptz) RETURNS void
+LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public, pg_temp SET timezone = 'UTC' AS $fn$
+DECLARE
+   boundary bigint;
+   smaller bigint;
+   anchor text;
+   audits_count bigint;
+   details_count bigint;
+   links_count bigint;
+BEGIN
+   IF p_cutoff IS NULL OR p_cutoff > now() - interval '6 years' THEN
+      INSERT INTO public.worm_audit_purge(requested_at, requested_by, cutoff, retention_interval, outcome, reason)
+         VALUES(now(), session_user, p_cutoff, '6 years', 'REFUSED', 'Cutoff is within retention or null');
+      RETURN;
+   END IF;
+   -- Same lock as the Java chainer; table locks also exclude concurrent audit inserts.
+   PERFORM pg_advisory_xact_lock(86753090421);
+   LOCK TABLE "audit", "audit_detail", public.worm_audit_chain, public.worm_audit_chain_detail IN SHARE ROW EXCLUSIVE MODE;
+   SELECT coalesce(min(c.id) - 1, (SELECT max(id) FROM public.worm_audit_chain)) INTO boundary
+      FROM public.worm_audit_chain c LEFT JOIN "audit" a ON a.id = c.audit_id
+      WHERE a.id IS NULL OR a.timestamp IS NULL OR a.timestamp >= p_cutoff;
+   -- Shrink to a prefix closed over every audit's links AND all its stored details.
+   LOOP
+      SELECT min(c.id) - 1 INTO smaller FROM public.worm_audit_chain c
+         WHERE c.id <= boundary AND (
+            EXISTS (SELECT FROM public.worm_audit_chain later WHERE later.audit_id = c.audit_id AND later.id > boundary)
+            OR EXISTS (SELECT FROM "audit_detail" d WHERE d.audit_id = c.audit_id AND NOT EXISTS (
+               SELECT FROM public.worm_audit_chain covered WHERE covered.audit_id = c.audit_id
+                  AND d.id::text = ANY(string_to_array(covered.detail_ids, ',')))));
+      EXIT WHEN smaller IS NULL;
+      boundary := smaller;
+   END LOOP;
+   SELECT link_hash INTO anchor FROM public.worm_audit_chain WHERE id = boundary;
+   IF anchor IS NULL THEN
+      INSERT INTO public.worm_audit_purge(requested_at, requested_by, cutoff, retention_interval, outcome, reason)
+         VALUES(now(), session_user, p_cutoff, '6 years', 'REFUSED', 'No complete expired chain prefix');
+      RETURN;
+   END IF;
+   PERFORM set_config('worm_audit.purging', 'on', true);
+   DELETE FROM "audit_detail" d WHERE EXISTS
+      (SELECT FROM public.worm_audit_chain c WHERE c.audit_id = d.audit_id AND c.id <= boundary);
+   GET DIAGNOSTICS details_count = ROW_COUNT;
+   -- Coverage rows go before their links: the guard finds their age through the link.
+   DELETE FROM public.worm_audit_chain_detail WHERE chain_id <= boundary;
+   -- Keep ids locally because links must be deleted before their audit headers.
+   WITH removed AS (DELETE FROM public.worm_audit_chain WHERE id <= boundary RETURNING audit_id),
+      removed_audits AS (DELETE FROM "audit" WHERE id IN (SELECT audit_id FROM removed) RETURNING id)
+      SELECT (SELECT count(*) FROM removed), (SELECT count(*) FROM removed_audits) INTO links_count, audits_count;
+   PERFORM set_config('worm_audit.purging', 'off', true);
+   INSERT INTO public.worm_audit_purge(requested_at, requested_by, cutoff, retention_interval, outcome,
+      audits_deleted, details_deleted, links_deleted, anchor_link_id, anchor_hash)
+      VALUES(now(), session_user, p_cutoff, '6 years', 'PURGED', audits_count, details_count, links_count, boundary, anchor);
+END;
+$fn$;
+DO $$
+BEGIN
+   IF current_setting('worm_audit.purge_regrant', true) = 'on' THEN
+      EXECUTE format('REVOKE EXECUTE ON FUNCTION public.worm_audit_purge(timestamptz) FROM %I', current_user);
+      PERFORM set_config('worm_audit.purge_regrant', 'off', true);
+   END IF;
+END;
+$$;

docs/screenshots/README.md

@@ -1,5 +1,14 @@
 # WORM Audit screenshots
 
+## RC.2 settings captures (2026-10-14)
+Home (GOVERNANCE, 30-day exports) and the WORM Audit Settings record at 390 x 844 and 1440 x 1000,
+light and dark, captured with Playwright against `WormAuditDemoServer` on port 8099:
+`worm-audit-home-{390,1440}-{light,dark}.jpg` (replaced) and
+`worm-audit-settings-{390,1440}-{light,dark}.jpg` (new). Overflow check on all eight: no page
+horizontal overflow, no inner horizontal scrollers, no double scrollbars.
+
+## Earlier captures
+
 Refreshed after record-screen polish **2026-10-13 (America/Chicago)** from the rebuilt test-scope demo using
 packaged Next UI 1.0.0-RC.14 and connected Edge through the browser screenshot
 and viewport tools. All files are JPEG viewport captures, below 300 KB each.

src/main/java/com/kingsrook/qbits/wormaudit/WormAuditQBitConfig.java

@@ -45,6 +45,10 @@ public class WormAuditQBitConfig implements QBitConfig
    {
    }
 
+   private String exportLockMode = "GOVERNANCE";
+   private int exportRetentionDays = 30;
+   private final WormAuditSettings settings = new WormAuditSettings(this);
+   private boolean includeApp = true;
    private String appName = "worm_audit";
    private String appLabel = "WORM Audit";
    private String parentAppName;
@@ -71,7 +75,83 @@ public class WormAuditQBitConfig implements QBitConfig
 
 
    /*******************************************************************************
-    ** Read the app name.
+    ** Read exportLockMode.
+    *******************************************************************************/
+   public String getExportLockMode()
+   {
+      return (exportLockMode);
+   }
+
+
+   /*******************************************************************************
+    ** Configure exportLockMode.
+    *******************************************************************************/
+   public WormAuditQBitConfig withExportLockMode(String exportLockMode)
+   {
+      this.exportLockMode = exportLockMode;
+      return (this);
+   }
+
+
+   /*******************************************************************************
+    ** Read exportRetentionDays.
+    *******************************************************************************/
+   public int getExportRetentionDays()
+   {
+      return (exportRetentionDays);
+   }
+
+
+   /*******************************************************************************
+    ** Configure exportRetentionDays.
+    *******************************************************************************/
+   public WormAuditQBitConfig withExportRetentionDays(int exportRetentionDays)
+   {
+      this.exportRetentionDays = exportRetentionDays;
+      return (this);
+   }
+
+
+   /*******************************************************************************
+    ** Read effective settings through the instance-scoped 30-second cache.
+    *******************************************************************************/
+   @JsonIgnore
+   public WormAuditSettings.Values effectiveSettings()
+   {
+      return (settings.read());
+   }
+
+
+   /*******************************************************************************
+    ** Discard this instance's cached row after a successful local settings write.
+    *******************************************************************************/
+   public void invalidateSettings()
+   {
+      settings.invalidate();
+   }
+
+
+   /*******************************************************************************
+    ** Whether to create the standalone app. Widgets remain available when false.
+    *******************************************************************************/
+   public boolean getIncludeApp()
+   {
+      return (includeApp);
+   }
+
+
+   /*******************************************************************************
+    ** Disable standalone app creation when the host owns the Audit app and help.
+    *******************************************************************************/
+   public WormAuditQBitConfig withIncludeApp(boolean includeApp)
+   {
+      this.includeApp = includeApp;
+      return (this);
+   }
+
+
+   /*******************************************************************************
+    ** Read the app name (the host-owned destination when includeApp is false).
     *******************************************************************************/
    public String getAppName()
    {
@@ -527,6 +607,7 @@ public class WormAuditQBitConfig implements QBitConfig
       {
          throw (new IllegalArgumentException("Export bucket, prefix and schedule must be configured"));
       }
+      WormAuditSettings.validate(WormAuditSettings.defaults(this));
       if(backendName == null || backendName.isBlank() || retentionYears < 1 || chainIntervalSeconds < 1
          || lateDetailLookbackHours < 1 || settleHorizonSeconds < 0 || batchSize < 1 || checkpointEveryLinks < 1 || checkpointEveryMinutes < 1)
       {

src/main/java/com/kingsrook/qbits/wormaudit/WormAuditQBitProducer.java

@@ -26,12 +26,14 @@ import com.kingsrook.qbits.wormaudit.app.WormAuditApp;
 import com.kingsrook.qbits.wormaudit.ddl.WormAuditDdl;
 import com.kingsrook.qbits.wormaudit.guard.AppendOnlyGuard;
 import com.kingsrook.qbits.wormaudit.model.LinkRangeWidget;
+import com.kingsrook.qbits.wormaudit.model.WormAuditHelp;
 import com.kingsrook.qbits.wormaudit.model.WormAuditTables;
 import com.kingsrook.qbits.wormaudit.processes.ExportProcesses;
 import com.kingsrook.qbits.wormaudit.processes.WormAuditProcesses;
 import com.kingsrook.qqq.backend.core.alerts.AlertDefaultMonitor;
 import com.kingsrook.qqq.backend.core.alerts.AlertSeverity;
 import com.kingsrook.qqq.backend.core.alerts.QAlertTypeMetaData;
+import com.kingsrook.qqq.backend.core.context.QContext;
 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;
@@ -42,6 +44,7 @@ import com.kingsrook.qqq.backend.core.model.metadata.qbits.QBitMetaData;
 import com.kingsrook.qqq.backend.core.model.metadata.qbits.QBitMetaDataProducer;
 import com.kingsrook.qqq.backend.core.model.metadata.scheduleing.quartz.QuartzSchedulerMetaData;
 import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
+import com.kingsrook.qqq.backend.core.model.session.QSystemUserSession;
 
 
 /*******************************************************************************
@@ -182,21 +185,39 @@ public class WormAuditQBitProducer implements QBitMetaDataProducer<WormAuditQBit
          output.add(new QWidgetMetaData().withName(name + "_links").withLabel("Chain links").withType("childRecordList").withGridColumns(12)
             .withCodeReference(new QCodeReference(LinkRangeWidget.class)).withDefaultValue("parentTable", name));
       }
-      for(var process : WormAuditProcesses.processes(qBitConfig))
+      List<QTableMetaData> producedTables = output.getEach(QTableMetaData.class);
+      output.addSelfToInstance(qInstance);
+      output = new MetaDataProducerMultiOutput();
+      var previousInstance = QContext.getQInstance();
+      var previousSession = QContext.getQSession();
+      var previousTransaction = QContext.getQBackendTransaction();
+      try
       {
-         process.setSourceQBitName(qBit.getName());
-         output.add(process);
+         ////////////////////////////////////////////////////////////////////////////
+         // Production is still assembling the host metadata. Do not validate it   //
+         // mid-build. The startup settings read is system work: run it as the     //
+         // core system user. A plain tokenless QSession fails session validation  //
+         // under token-based host authentication (OAuth2, Auth0).                 //
+         ////////////////////////////////////////////////////////////////////////////
+         QContext.setQInstance(qInstance);
+         QContext.setQSession(new QSystemUserSession(qInstance));
+         QContext.setTransaction(null);
+         addProcesses(output, qBit);
       }
-      for(var process : ExportProcesses.processes(qBitConfig))
+      finally
       {
-         process.setSourceQBitName(qBit.getName());
-         output.add(process);
+         QContext.setQInstance(previousInstance);
+         QContext.setQSession(previousSession);
+         QContext.setTransaction(previousTransaction);
       }
       if(qInstance.getSchedulers() == null || !qInstance.getSchedulers().containsKey(qBitConfig.getSchedulerName()))
       {
          qInstance.addScheduler(new QuartzSchedulerMetaData().withName(qBitConfig.getSchedulerName()));
       }
-      output.add(WormAuditApp.app(qBitConfig, output.getEach(QTableMetaData.class)));
+      if(qBitConfig.getIncludeApp())
+      {
+         output.add(WormAuditApp.app(qBitConfig, producedTables));
+      }
       for(QWidgetMetaData widget : WormAuditApp.widgets())
       {
          output.add(widget);
@@ -219,11 +240,30 @@ public class WormAuditQBitProducer implements QBitMetaDataProducer<WormAuditQBit
             .withDefaultMonitor(AlertDefaultMonitor.heartbeat(name, name, 24 * 3600, 2 * 3600)));
       }
       output.addSelfToInstance(qInstance);
+      WormAuditHelp.apply(qInstance, qBitConfig);
       denyAssistant(qInstance);
       return (new MetaDataProducerMultiOutput());
    }
 
 
+   /*******************************************************************************
+    ** Build schedules from settings available at producer startup.
+    *******************************************************************************/
+   private void addProcesses(MetaDataProducerMultiOutput output, QBitMetaData qBit)
+   {
+      for(var process : WormAuditProcesses.processes(qBitConfig))
+      {
+         process.setSourceQBitName(qBit.getName());
+         output.add(process);
+      }
+      for(var process : ExportProcesses.processes(qBitConfig))
+      {
+         process.setSourceQBitName(qBit.getName());
+         output.add(process);
+      }
+   }
+
+
    /*******************************************************************************
     ** RC.5 compatibility: replace with a direct call when the qbit builds on RC.6 (qqq#1141, #1163).
     *******************************************************************************/

src/main/java/com/kingsrook/qbits/wormaudit/WormAuditSettings.java

@@ -0,0 +1,253 @@
+/*
+ * 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.wormaudit;
+
+
+import java.sql.SQLException;
+import java.time.Clock;
+import java.time.Instant;
+import com.kingsrook.qbits.wormaudit.chain.AuditStore;
+import com.kingsrook.qbits.wormaudit.model.WormAuditTables;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
+import org.quartz.CronExpression;
+
+
+/*******************************************************************************
+ ** Singleton settings merged over host defaults. Only absent rows/tables fall
+ ** back; database outages and invalid persisted settings fail closed.
+ *******************************************************************************/
+public class WormAuditSettings
+{
+   private final WormAuditQBitConfig config;
+   private final Clock clock;
+   private QInstance instance;
+   private QRecord cached;
+   private Instant expires = Instant.MIN;
+
+   /*******************************************************************************
+    ** An immutable snapshot for one run.
+    *******************************************************************************/
+   public record Values(int audit_retention_years,
+      String export_lock_mode,
+      int export_retention_days,
+      int checkpoint_every_links,
+      int checkpoint_every_minutes,
+      int chain_interval_seconds,
+      int settle_horizon_seconds,
+      int late_detail_lookback_hours,
+      String export_cron,
+      String verify_cron)
+   {
+   }
+
+
+   /*******************************************************************************
+    ** Use the production clock.
+    *******************************************************************************/
+   public WormAuditSettings(WormAuditQBitConfig config)
+   {
+      this(config, Clock.systemUTC());
+   }
+
+
+   /*******************************************************************************
+    ** Inject a clock for cache boundary tests.
+    *******************************************************************************/
+   public WormAuditSettings(WormAuditQBitConfig config, Clock clock)
+   {
+      this.config = config;
+      this.clock = clock;
+   }
+
+
+   /*******************************************************************************
+    ** Invalidate after a local edit; other application instances refresh within 30 seconds.
+    *******************************************************************************/
+   public synchronized void invalidate()
+   {
+      expires = Instant.MIN;
+   }
+
+
+   /*******************************************************************************
+    ** Seed the singleton only when absent; never overwrite an administrator edit.
+    ** The database script (changeset worm-audit:10) is the normal seed; this remains
+    ** for memory and H2 hosts, whose settings table cannot take generic inserts.
+    **
+    ** @deprecated for PostgreSQL hosts, which get row 1 from the database script;
+    ** still the supported way to create row 1 on memory and H2 hosts. Reads already
+    ** fall back to the configured values while the row is absent.
+    *******************************************************************************/
+   @Deprecated
+   public static void seed(WormAuditQBitConfig config) throws com.kingsrook.qqq.backend.core.exceptions.QException
+   {
+      var store = new AuditStore(null);
+      if(store.get(WormAuditTables.SETTINGS, 1) == null)
+      {
+         store.insert(WormAuditTables.SETTINGS, java.util.List.of(defaults(config)));
+      }
+      config.invalidateSettings();
+   }
+
+
+   /*******************************************************************************
+    ** Read at most once per 30 seconds, isolated by QInstance identity.
+    *******************************************************************************/
+   public synchronized Values read()
+   {
+      QInstance current = QContext.getQInstance();
+      if(current != instance || !clock.instant().isBefore(expires))
+      {
+         QRecord row = null;
+         if(current != null && current.getTable(WormAuditTables.SETTINGS) != null)
+         {
+            try
+            {
+               row = new AuditStore(null).get(WormAuditTables.SETTINGS, 1);
+            }
+            catch(Exception e)
+            {
+               if(!missingTable(e))
+               {
+                  throw (new IllegalStateException("Cannot read WORM Audit Settings", e));
+               }
+            }
+         }
+         cached = row;
+         instance = current;
+         expires = row == null ? Instant.MIN : clock.instant().plusSeconds(30);
+      }
+      QRecord row = defaults(config);
+      if(cached != null)
+      {
+         for(String field : row.getValues().keySet())
+         {
+            if(cached.getValue(field) != null)
+            {
+               row.setValue(field, cached.getValue(field));
+            }
+         }
+      }
+      validate(row);
+      return (new Values(row.getValueInteger("audit_retention_years"),
+         row.getValueString("export_lock_mode"),
+         row.getValueInteger("export_retention_days"),
+         row.getValueInteger("checkpoint_every_links"),
+         row.getValueInteger("checkpoint_every_minutes"),
+         row.getValueInteger("chain_interval_seconds"),
+         row.getValueInteger("settle_horizon_seconds"),
+         row.getValueInteger("late_detail_lookback_hours"),
+         row.getValueString("export_cron"),
+         row.getValueString("verify_cron")));
+   }
+
+
+   /*******************************************************************************
+    ** Build insert-only seed values from the host defaults.
+    *******************************************************************************/
+   public static QRecord defaults(WormAuditQBitConfig config)
+   {
+      return (new QRecord().withValue("id", 1)
+         .withValue("audit_retention_years", config.getRetentionYears())
+         .withValue("export_lock_mode", config.getExportLockMode())
+         .withValue("export_retention_days", config.getExportRetentionDays())
+         .withValue("checkpoint_every_links", config.getCheckpointEveryLinks())
+         .withValue("checkpoint_every_minutes", config.getCheckpointEveryMinutes())
+         .withValue("chain_interval_seconds", config.getChainIntervalSeconds())
+         .withValue("settle_horizon_seconds", config.getSettleHorizonSeconds())
+         .withValue("late_detail_lookback_hours", config.getLateDetailLookbackHours())
+         .withValue("export_cron", config.getExportCron())
+         .withValue("verify_cron", config.getVerifyCron()));
+   }
+
+
+   /*******************************************************************************
+    ** Validate supplied fields; partial updates leave omitted fields untouched.
+    *******************************************************************************/
+   public static void validate(QRecord row)
+   {
+      if(row.getValue("id") != null && !Integer.valueOf(1).equals(row.getValueInteger("id")))
+      {
+         throw (new IllegalArgumentException("WORM Audit Settings has one row only (ID 1)."));
+      }
+      range(row, "audit_retention_years", "Audit retention years", 1, 100);
+      range(row, "export_retention_days", "Export retention days", 1, 36500);
+      range(row, "checkpoint_every_links", "Checkpoint every links", 1, 2147483647);
+      range(row, "checkpoint_every_minutes", "Checkpoint every minutes", 1, 2147483647);
+      range(row, "chain_interval_seconds", "Chain interval seconds", 1, 2147483647);
+      range(row, "settle_horizon_seconds", "Settle horizon seconds", 0, 2147483647);
+      range(row, "late_detail_lookback_hours", "Late detail lookback hours", 1, 2147483647);
+      if(row.getValues().containsKey("export_lock_mode") && !java.util.Arrays.asList("GOVERNANCE", "COMPLIANCE").contains(row.getValueString("export_lock_mode")))
+      {
+         throw (new IllegalArgumentException("Export lock mode must be GOVERNANCE or COMPLIANCE."));
+      }
+      for(String field : java.util.List.of("export_cron", "verify_cron"))
+      {
+         if(row.getValues().containsKey(field) && (row.getValueString(field) == null || !CronExpression.isValidExpression(row.getValueString(field))))
+         {
+            throw (new IllegalArgumentException((field.equals("export_cron") ? "Export" : "Verify") + " schedule must be a valid Quartz cron expression (UTC)."));
+         }
+      }
+   }
+
+
+   /*******************************************************************************
+    ** Reject null, fractional and out-of-range numbers with a plain field label.
+    *******************************************************************************/
+   private static void range(QRecord row, String field, String label, int minimum, int maximum)
+   {
+      if(!row.getValues().containsKey(field))
+      {
+         return;
+      }
+      try
+      {
+         long value = Long.parseLong(String.valueOf(row.getValue(field)));
+         if(value >= minimum && value <= maximum)
+         {
+            return;
+         }
+      }
+      catch(NumberFormatException ignored)
+      {
+         // Use the same clear message for invalid numbers and invalid ranges.
+      }
+      throw (new IllegalArgumentException(label + " must be a whole number from " + minimum + " to " + maximum + "."));
+   }
+
+
+   /*******************************************************************************
+    ** Recognize PostgreSQL/H2 missing-table errors without hiding other failures.
+    *******************************************************************************/
+   private static boolean missingTable(Throwable error)
+   {
+      for(Throwable cause = error; cause != null; cause = cause.getCause())
+      {
+         if(cause instanceof SQLException sql && ("42P01".equals(sql.getSQLState()) || "42S02".equals(sql.getSQLState()) || "42S04".equals(sql.getSQLState())))
+         {
+            return (true);
+         }
+      }
+      return (false);
+   }
+}

src/main/java/com/kingsrook/qbits/wormaudit/app/PostgresSetupWarnings.java

@@ -49,6 +49,11 @@ public final class PostgresSetupWarnings
    public static List<String> load(QBackendTransaction transaction, WormAuditQBitConfig config)
    {
       List<String> warnings = new ArrayList<>();
+      int years = config.effectiveSettings().audit_retention_years();
+      if(years != config.getRetentionYears())
+      {
+         warnings.add("Audit retention setting differs from the configured database purge interval (" + config.getRetentionYears() + " years). A reviewed migration is required.");
+      }
       if(!(transaction instanceof RDBMSTransaction rdbms))
       {
          return (warnings);
@@ -60,8 +65,10 @@ public final class PostgresSetupWarnings
          {
             return (warnings);
          }
+         // The live function is authoritative on PostgreSQL, rather than the renderer defaults.
+         warnings.clear();
          List<String> tables = new ArrayList<>(config.getCoreAuditTableNames().values());
-         tables.addAll(WormAuditTables.ALL);
+         tables.addAll(WormAuditTables.ALL.stream().filter(t -> !WormAuditTables.SETTINGS.equals(t)).toList());
          for(String table : tables)
          {
             for(String trigger : List.of("worm_audit_row_guard", "worm_audit_truncate_guard"))
@@ -93,6 +100,17 @@ public final class PostgresSetupWarnings
                }
             }
          }
+         try(var statement = connection.createStatement(); var result = statement.executeQuery("SELECT pg_get_functiondef(to_regprocedure('public.worm_audit_purge(timestamp with time zone)'))"))
+         {
+            if(result.next() && result.getString(1) != null)
+            {
+               var interval = java.util.regex.Pattern.compile("(?i)interval\\s+'([0-9]+) years'").matcher(result.getString(1));
+               if(interval.find() && Integer.parseInt(interval.group(1)) != years)
+               {
+                  warnings.add("Audit retention setting differs from the purge function's compiled interval (" + interval.group(1) + " years). A reviewed migration is required.");
+               }
+            }
+         }
          try(var statement = connection.createStatement(); var result = statement.executeQuery("SELECT to_regprocedure('public.worm_audit_purge(timestamp with time zone)') IS NOT NULL"))
          {
             result.next();

src/main/java/com/kingsrook/qbits/wormaudit/app/WormAuditApp.java

@@ -88,7 +88,8 @@ public final class WormAuditApp
       for(String name : WormAuditTables.NAV)
       {
          QTableMetaData table = tables.stream().filter(t -> t.getName().equals(name)).findFirst().orElseThrow();
-         app.withSectionOfChildren(new QAppSection().withName(name).withLabel(table.getLabel()), table);
+         app.withSectionOfChildren(new QAppSection().withName(name).withLabel(WormAuditTables.navigationLabel(name)),
+            WormAuditTables.SETTINGS.equals(name) ? table.clone().withLabel("Settings") : table);
       }
       return (app);
    }

src/main/java/com/kingsrook/qbits/wormaudit/app/WormAuditHomeData.java

@@ -54,7 +54,7 @@ import com.kingsrook.qqq.backend.module.rdbms.actions.RDBMSTransaction;
 public record WormAuditHomeData(long links, Instant lastLinkAt, long backlog, long settling, int settleSeconds,
    QRecord lastVerification, QRecord openBreak, QRecord lastExport, long exportLag, Long exportAgeHours,
    List<String> exportGaps, String bucketAlias, String lockCapability, String lockReason,
-   int retentionYears, QRecord anchor, Instant oldestAudit, Instant nextPurge,
+   String exportLockMode, int exportRetentionDays, int retentionYears, QRecord anchor, Instant oldestAudit, Instant nextPurge,
    List<QRecord> recentPurges, List<String> warnings)
 {
    /*******************************************************************************
@@ -71,7 +71,16 @@ public record WormAuditHomeData(long links, Instant lastLinkAt, long backlog, lo
     *******************************************************************************/
    public static WormAuditHomeData load(WormAuditQBitConfig config, SealedStore storage, Instant now) throws QException
    {
+      var settings = config.effectiveSettings();
       List<String> warnings = new ArrayList<>();
+      if("GOVERNANCE".equals(settings.export_lock_mode()))
+      {
+         warnings.add("Sealed exports use GOVERNANCE mode: an administrator with s3:BypassGovernanceRetention can delete them. Switch to COMPLIANCE for write-once copies.");
+      }
+      if(now.plus(Duration.ofDays(settings.export_retention_days())).isBefore(now.atZone(ZoneOffset.UTC).plusYears(settings.audit_retention_years()).toInstant()))
+      {
+         warnings.add("Export retention is shorter than audit retention.");
+      }
       String capability = "UNKNOWN";
       String reason = "Object Lock capability could not be confirmed";
       try
@@ -113,7 +122,7 @@ public record WormAuditHomeData(long links, Instant lastLinkAt, long backlog, lo
             AuditStore store = new AuditStore(transaction);
             QRecord lastLink = store.last(WormAuditTables.CHAIN);
             long links = store.count(WormAuditTables.CHAIN, new QQueryFilter());
-            Instant cutoff = now.minusSeconds(config.getSettleHorizonSeconds());
+            Instant cutoff = now.minusSeconds(settings.settle_horizon_seconds());
             long backlog = store.unchainedCount(cutoff, true);
             long settling = store.unchainedCount(cutoff, false);
             QRecord verification = latest(store, WormAuditTables.VERIFICATION, new QQueryFilter(), "finished_at");
@@ -147,10 +156,10 @@ public record WormAuditHomeData(long links, Instant lastLinkAt, long backlog, lo
             warnings.addAll(PostgresSetupWarnings.load(transaction, config));
             transaction.commit();
             return (new WormAuditHomeData(links, lastLink == null ? null : lastLink.getValueInstant("chained_at"), backlog, settling,
-               config.getSettleHorizonSeconds(), verification, openBreak, export, lag,
+               settings.settle_horizon_seconds(), verification, openBreak, export, lag,
                export == null || export.getValueInstant("sealed_at") == null ? null : Math.max(0, Duration.between(export.getValueInstant("sealed_at"), now).toHours()),
-               List.copyOf(gaps), config.getExportBucketAlias(), capability, reason, config.getRetentionYears(), anchorId == 0 ? null : anchor,
-               oldestAt, oldestAt == null ? null : oldestAt.atZone(ZoneOffset.UTC).plusYears(config.getRetentionYears()).toInstant(), List.copyOf(purges), List.copyOf(warnings)));
+               List.copyOf(gaps), config.getExportBucketAlias(), capability, reason, settings.export_lock_mode(), settings.export_retention_days(), settings.audit_retention_years(), anchorId == 0 ? null : anchor,
+               oldestAt, oldestAt == null ? null : oldestAt.atZone(ZoneOffset.UTC).plusYears(settings.audit_retention_years()).toInstant(), List.copyOf(purges), List.copyOf(warnings)));
          }
          catch(Exception e)
          {

src/main/java/com/kingsrook/qbits/wormaudit/app/WormAuditHomeView.java

@@ -82,6 +82,8 @@ public final class WormAuditHomeView
       }
       close(html);
       open(html, "Export status");
+      row(html, "Export lock mode", data.exportLockMode());
+      row(html, "Export retention", data.exportRetentionDays() + " days");
       QRecord export = data.lastExport();
       if(export == null)
       {
@@ -93,7 +95,7 @@ public final class WormAuditHomeView
          row(html, "Link range", export.getValueLong("from_link_id") + "–" + export.getValueLong("to_link_id"));
          row(html, "Sealed at", when(export.getValueInstant("sealed_at"), "Not recorded"));
          row(html, "Retain until", when(export.getValueInstant("retain_until"), "Not recorded"));
-         row(html, "Lock mode", export.getValueString("lock_mode"));
+         row(html, "Last export lock mode", export.getValueString("lock_mode"));
       }
       row(html, "Export lag", data.exportLag() + " links; " + (data.exportAgeHours() == null ? "no previous export time" : data.exportAgeHours() + " h since last export"));
       row(html, "Gaps in sealed ranges", data.exportGaps().isEmpty() ? (export == null ? "No sealed ranges to check" : "None") : String.join(", ", data.exportGaps()));

src/main/java/com/kingsrook/qbits/wormaudit/chain/AuditStore.java

@@ -68,9 +68,21 @@ import com.kingsrook.qqq.backend.module.rdbms.model.metadata.RDBMSTableBackendDe
  *******************************************************************************/
 public class AuditStore
 {
+   /*******************************************************************************
+    ** Every (detail id, link id) pair the chain's own detail lists cover.
+    *******************************************************************************/
+   private static final String CHAIN_PAIRS = "SELECT x.id::bigint AS detail_id, c.id AS chain_id FROM worm_audit_chain c"
+      + " CROSS JOIN LATERAL unnest(string_to_array(c.detail_ids, ',')) AS x(id) WHERE c.detail_ids <> ''";
+
+   /*******************************************************************************
+    ** Chain coverage counted per detail id, aggregated in one pass.
+    *******************************************************************************/
+   private static final String CHAIN_COVERAGE = "SELECT detail_id AS id, count(*) AS n FROM (" + CHAIN_PAIRS + ") pairs GROUP BY detail_id";
+
    private final QBackendTransaction transaction;
    private final Map<String, String> names = new LinkedHashMap<>(16, 0.75F, true);
    private Boolean timestampWithZone;
+   private Boolean postgres;
 
 
    /*******************************************************************************
@@ -268,12 +280,23 @@ public class AuditStore
     ** memory uses QQQ queries and the same ordering and cutoff semantics.
     *******************************************************************************/
    public List<QRecord> unchained(Instant cutoff, int limit) throws QException
+   {
+      return (unchained(cutoff, limit, 0L));
+   }
+
+
+   /*******************************************************************************
+    ** Settled unchained headers above a swept audit id floor (RDBMS only); every
+    ** settled header at or below the floor is already known to be chained.
+    *******************************************************************************/
+   public List<QRecord> unchained(Instant cutoff, int limit, long floor) throws QException
    {
       if(isRdbms())
       {
-         String sql = "SELECT a." + column("audit", "id") + " FROM " + physical("audit") + " a WHERE a." + column("audit", "timestamp")
-            + " < ?" + unchainedPredicate() + " ORDER BY a." + column("audit", "timestamp") + ", a." + column("audit", "id") + " LIMIT " + limit;
-         Map<Long, QRecord> rows = index(in("audit", "id", sqlIds(sql, cutoff)));
+         String sql = "SELECT a." + column("audit", "id") + " FROM " + physical("audit") + " a WHERE a." + column("audit", "id") + " > ? AND a."
+            + column("audit", "timestamp") + " < ?" + unchainedPredicate() + " ORDER BY a." + column("audit", "timestamp") + ", a." + column("audit", "id")
+            + " LIMIT " + limit;
+         Map<Long, QRecord> rows = index(in("audit", "id", sqlIds(sql, floor, cutoff)));
          return (rows.values().stream().sorted(Comparator.comparing((QRecord r) -> r.getValueInstant("timestamp"))
             .thenComparing(r -> r.getValueLong("id"))).toList());
       }
@@ -380,15 +403,26 @@ public class AuditStore
     ** Poll uncovered details in a bounded time window; memory retains its simple fallback.
     *******************************************************************************/
    public List<QRecord> lateDetails(Instant cutoff, int hours, int limit) throws QException
+   {
+      return (lateDetails(cutoff, hours, limit, 0L));
+   }
+
+
+   /*******************************************************************************
+    ** Uncovered details above a swept detail id floor (RDBMS only). Coverage is a
+    ** primary-key probe of the normalized coverage table, never an array scan.
+    *******************************************************************************/
+   public List<QRecord> lateDetails(Instant cutoff, int hours, int limit, long floor) throws QException
    {
       if(isRdbms())
       {
-         String sql = "SELECT d." + column("auditDetail", "id") + " FROM " + physical("auditDetail") + " d JOIN " + physical("audit")
+         String detailId = "d." + column("auditDetail", "id");
+         String sql = "SELECT " + detailId + " FROM " + physical("auditDetail") + " d JOIN " + physical("audit")
             + " a ON a." + column("audit", "id") + "=d." + column("auditDetail", "auditId")
-            + " WHERE a." + column("audit", "timestamp") + " < ? AND a." + column("audit", "timestamp") + " >= ?"
+            + " WHERE " + detailId + " > ? AND a." + column("audit", "timestamp") + " < ? AND a." + column("audit", "timestamp") + " >= ?"
             + " AND EXISTS (SELECT 1 FROM worm_audit_chain h WHERE h.kind='AUDIT' AND h.audit_id=a." + column("audit", "id") + ")"
-            + " AND NOT EXISTS (SELECT 1 FROM worm_audit_chain c WHERE " + containsDetail() + ") ORDER BY d." + column("auditDetail", "id") + " LIMIT " + limit;
-         return (in("auditDetail", "id", sqlIds(sql, cutoff, cutoff.minusSeconds(hours * 3600L))));
+            + " AND NOT EXISTS (SELECT 1 FROM " + WormAuditTables.CHAIN_DETAIL + " x WHERE x.detail_id=" + detailId + ") ORDER BY " + detailId + " LIMIT " + limit;
+         return (in("auditDetail", "id", sqlIds(sql, floor, cutoff, cutoff.minusSeconds(hours * 3600L))));
       }
       List<QRecord> links = all(WormAuditTables.CHAIN);
       Set<Long> headers = links.stream().filter(r -> "AUDIT".equals(r.getValueString("kind"))).map(r -> r.getValueLong("audit_id")).collect(Collectors.toSet());
@@ -409,11 +443,89 @@ public class AuditStore
       String time = "a." + column("audit", "timestamp");
       String headers = "SELECT " + auditId + " AS id, (SELECT count(*) FROM worm_audit_chain c WHERE c.kind='AUDIT' AND c.audit_id=" + auditId
          + ") AS coverage FROM " + physical("audit") + " a WHERE " + time + " < ? OR " + time + " IS NULL";
-      String details = "SELECT d." + column("auditDetail", "id") + " AS id, (SELECT count(*) FROM worm_audit_chain c WHERE " + containsDetail()
-         + ") AS coverage FROM " + physical("auditDetail") + " d LEFT JOIN " + physical("audit") + " a ON " + auditId + "=d."
-         + column("auditDetail", "auditId") + " WHERE " + time + " < ? OR " + time + " IS NULL";
+      String detailId = "d." + column("auditDetail", "id");
+      String scope = " FROM " + physical("auditDetail") + " d LEFT JOIN " + physical("audit") + " a ON " + auditId + "=d."
+         + column("auditDetail", "auditId");
+      String details;
+      if(isPostgres())
+      {
+         ///////////////////////////////////////////////////////////////////////
+         // Count coverage from the chain itself (the hashed source of truth), //
+         // aggregating every link's ids once rather than probing per detail.  //
+         ///////////////////////////////////////////////////////////////////////
+         details = "SELECT " + detailId + " AS id, coalesce(cv.n, 0) AS coverage" + scope + " LEFT JOIN (" + CHAIN_COVERAGE
+            + ") cv ON cv.id=" + detailId + " WHERE " + time + " < ? OR " + time + " IS NULL";
+      }
+      else
+      {
+         details = "SELECT " + detailId + " AS id, (SELECT count(*) FROM worm_audit_chain c WHERE POSITION(',' || CAST(" + detailId
+            + " AS varchar) || ',' IN ',' || c.detail_ids || ',') > 0) AS coverage" + scope + " WHERE " + time + " < ? OR " + time + " IS NULL";
+      }
       reportCoverage(headers, "audit", cutoff, result);
       reportCoverage(details, "audit_detail", cutoff, result);
+      coverageTable(result);
+   }
+
+
+   /*******************************************************************************
+    ** The coverage table must hold exactly the chain's own detail lists. A
+    ** mismatch would let the writer skip or re-chain a detail, so it is a break.
+    *******************************************************************************/
+   private void coverageTable(VerificationResult result) throws QException
+   {
+      String table = "SELECT detail_id, chain_id FROM " + WormAuditTables.CHAIN_DETAIL;
+      String sql = isPostgres()
+         ? "SELECT detail_id, count(*) OVER() FROM ((" + CHAIN_PAIRS + " EXCEPT " + table + ") UNION ALL (" + table + " EXCEPT " + CHAIN_PAIRS
+            + ")) mismatch ORDER BY detail_id LIMIT 100"
+         : "SELECT x.detail_id, count(*) OVER() FROM " + WormAuditTables.CHAIN_DETAIL + " x WHERE NOT EXISTS (SELECT 1 FROM worm_audit_chain c"
+            + " WHERE c.id=x.chain_id AND POSITION(',' || CAST(x.detail_id AS varchar) || ',' IN ',' || c.detail_ids || ',') > 0) ORDER BY x.detail_id LIMIT 100";
+      try(PreparedStatement statement = ((RDBMSTransaction) transaction).getConnection().prepareStatement(sql); ResultSet rows = statement.executeQuery())
+      {
+         long total = 0;
+         int reported = 0;
+         while(rows.next())
+         {
+            total = rows.getLong(2);
+            result.add(null, null, "audit_detail " + com.kingsrook.qbits.wormaudit.verify.WormAuditVerifier.number(rows.getLong(1))
+               + " has a detail coverage row that disagrees with the chain");
+            reported++;
+         }
+         result.omitted(total - reported);
+      }
+      catch(Exception e)
+      {
+         throw (AppendOnlyGuard.map(e));
+      }
+   }
+
+
+   /*******************************************************************************
+    ** Highest id in a core audit table, or 0 when it is empty (RDBMS only).
+    *******************************************************************************/
+   public long maxId(String table) throws QException
+   {
+      return (sqlIds("SELECT coalesce(max(" + column(table, "id") + "), 0) FROM " + physical(table)).getFirst());
+   }
+
+
+   /*******************************************************************************
+    ** PostgreSQL maintains detail coverage in its chain trigger; other backends
+    ** have no trigger, so the writer appends coverage rows itself.
+    *******************************************************************************/
+   public boolean isPostgres() throws QException
+   {
+      if(postgres == null)
+      {
+         try
+         {
+            postgres = isRdbms() && "PostgreSQL".equals(((RDBMSTransaction) transaction).getConnection().getMetaData().getDatabaseProductName());
+         }
+         catch(Exception e)
+         {
+            throw (AppendOnlyGuard.map(e));
+         }
+      }
+      return (postgres);
    }
 
 
@@ -453,36 +565,22 @@ public class AuditStore
 
 
    /*******************************************************************************
-    ** PostgreSQL's containment expression matches the GIN expression index.
+    ** Read ids from an anti-join, binding instants by column type and ids as longs.
     *******************************************************************************/
-   private String containsDetail() throws QException
-   {
-      try
-      {
-         String id = "d." + column("auditDetail", "id");
-         if("PostgreSQL".equals(((RDBMSTransaction) transaction).getConnection().getMetaData().getDatabaseProductName()))
-         {
-            return ("string_to_array(c.detail_ids, ',') @> ARRAY[" + id + "::text]");
-         }
-         return ("POSITION(',' || CAST(" + id + " AS varchar) || ',' IN ',' || c.detail_ids || ',') > 0");
-      }
-      catch(Exception e)
-      {
-         throw (AppendOnlyGuard.map(e));
-      }
-   }
-
-
-   /*******************************************************************************
-    ** Read ids from an anti-join, binding all instants according to column type.
-    *******************************************************************************/
-   private List<Long> sqlIds(String sql, Instant... cutoffs) throws QException
+   private List<Long> sqlIds(String sql, Object... parameters) throws QException
    {
       try(PreparedStatement statement = ((RDBMSTransaction) transaction).getConnection().prepareStatement(sql))
       {
-         for(int i = 0; i < cutoffs.length; i++)
+         for(int i = 0; i < parameters.length; i++)
          {
-            bindCutoff(statement, i + 1, cutoffs[i]);
+            if(parameters[i] instanceof Instant instant)
+            {
+               bindCutoff(statement, i + 1, instant);
+            }
+            else
+            {
+               statement.setLong(i + 1, (Long) parameters[i]);
+            }
          }
          List<Long> ids = new ArrayList<>();
          try(ResultSet rows = statement.executeQuery())

src/main/java/com/kingsrook/qbits/wormaudit/chain/SweepFloors.java

@@ -0,0 +1,159 @@
+/*
+ * 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.wormaudit.chain;
+
+
+import java.time.Duration;
+import java.time.Instant;
+import java.util.ArrayDeque;
+import java.util.Collections;
+import java.util.Deque;
+import java.util.Map;
+import java.util.WeakHashMap;
+import com.kingsrook.qbits.wormaudit.WormAuditQBitConfig;
+
+
+/*******************************************************************************
+ ** Per-JVM id floors for the chain writer's two sweeps, so a tick reads only
+ ** audit and detail rows newer than the last complete sweep instead of the whole
+ ** settle window or late-detail lookback.
+ **
+ ** Each tick records the highest audit and audit_detail ids it can see. After a
+ ** sweep that returned fewer rows than its limit, every row allocated before
+ ** (settle horizon + MARGIN) ago is chained, on the same assumption the settle
+ ** horizon already makes: a row whose id was allocated that long ago has
+ ** committed. The floor moves to the highest id observed at or before that time.
+ ** A row whose transaction commits later than that is still chained: once an
+ ** hour (and after every restart) a tick ignores the floors and sweeps
+ ** everything, exactly as before floors existed. Floors live in memory only; a
+ ** second replica keeps its own. The daily verification checks full coverage
+ ** independently of all of this.
+ *******************************************************************************/
+public final class SweepFloors
+{
+   public static final Duration MARGIN = Duration.ofMinutes(5);
+   public static final Duration FULL_SWEEP_EVERY = Duration.ofHours(1);
+
+   private static final Map<WormAuditQBitConfig, SweepFloors> BY_CONFIG = Collections.synchronizedMap(new WeakHashMap<>());
+
+   private final Deque<Observation> observations = new ArrayDeque<>();
+   private long audit;
+   private long detail;
+   private Instant lastFullSweep;
+
+   /*******************************************************************************
+    ** The highest ids visible at one tick.
+    *******************************************************************************/
+   public record Observation(Instant at, long audit, long detail)
+   {
+   }
+
+
+   /*******************************************************************************
+    ** Floors for one installed qbit (config identity), created on first use.
+    *******************************************************************************/
+   public static SweepFloors of(WormAuditQBitConfig config)
+   {
+      return (BY_CONFIG.computeIfAbsent(config, c -> new SweepFloors()));
+   }
+
+
+   /*******************************************************************************
+    ** Audit ids at or below this are known chained.
+    *******************************************************************************/
+   public synchronized long audit()
+   {
+      return (audit);
+   }
+
+
+   /*******************************************************************************
+    ** Detail ids at or below this are known covered (or outside the lookback).
+    *******************************************************************************/
+   public synchronized long detail()
+   {
+      return (detail);
+   }
+
+
+   /*******************************************************************************
+    ** Whether this tick must ignore the floors: first tick, an hour since the last
+    ** complete full sweep, or a clock that moved backwards.
+    *******************************************************************************/
+   public synchronized boolean fullSweepDue(Instant now)
+   {
+      return (lastFullSweep == null || now.isBefore(lastFullSweep) || !now.isBefore(lastFullSweep.plus(FULL_SWEEP_EVERY)));
+   }
+
+
+   /*******************************************************************************
+    ** Record the highest visible ids. Ids below a floor mean rows were removed
+    ** wholesale (a purge of everything, or a reset test database): start over.
+    *******************************************************************************/
+   public synchronized Observation observe(Instant now, long maxAudit, long maxDetail)
+   {
+      if(maxAudit < audit || maxDetail < detail)
+      {
+         observations.clear();
+         audit = 0;
+         detail = 0;
+      }
+      Observation observation = new Observation(now, maxAudit, maxDetail);
+      observations.addLast(observation);
+      return (observation);
+   }
+
+
+   /*******************************************************************************
+    ** After a tick, raise each completely swept floor to the newest observation
+    ** made at or before the horizon, then drop observations no longer needed. A
+    ** full sweep (fullSweepAt not null) counts only when both sweeps completed.
+    *******************************************************************************/
+   public synchronized void swept(Observation current, Instant horizon, boolean auditsComplete, boolean detailsComplete, Instant fullSweepAt)
+   {
+      if(fullSweepAt != null && auditsComplete && detailsComplete)
+      {
+         lastFullSweep = fullSweepAt;
+      }
+      Observation settled = null;
+      for(Observation observation : observations)
+      {
+         if(!observation.at().isAfter(horizon) && (settled == null || !observation.at().isBefore(settled.at())))
+         {
+            settled = observation;
+         }
+      }
+      if(settled == null)
+      {
+         return;
+      }
+      if(auditsComplete)
+      {
+         audit = Math.max(audit, Math.min(settled.audit(), current.audit()));
+      }
+      if(detailsComplete)
+      {
+         detail = Math.max(detail, Math.min(settled.detail(), current.detail()));
+      }
+      Observation keep = settled;
+      observations.removeIf(observation -> observation != keep && !observation.at().isAfter(horizon));
+   }
+}

src/main/java/com/kingsrook/qbits/wormaudit/chain/WormAuditChainer.java

@@ -31,6 +31,7 @@ import java.util.TreeMap;
 import java.util.concurrent.locks.ReentrantLock;
 import java.util.stream.Collectors;
 import com.kingsrook.qbits.wormaudit.WormAuditQBitConfig;
+import com.kingsrook.qbits.wormaudit.WormAuditSettings;
 import com.kingsrook.qbits.wormaudit.checkpoint.Checkpoints;
 import com.kingsrook.qbits.wormaudit.guard.AppendOnlyGuard;
 import com.kingsrook.qbits.wormaudit.model.WormAuditTables;
@@ -99,7 +100,7 @@ public class WormAuditChainer
                   }
                }
             }
-            Counts counts = append(new AuditStore(transaction), now);
+            Counts counts = append(new AuditStore(transaction), now, config.effectiveSettings());
             transaction.commit();
             return (counts);
          }
@@ -119,15 +120,18 @@ public class WormAuditChainer
    /*******************************************************************************
     ** Append settled headers and uncovered detail chunks, then checkpoint.
     *******************************************************************************/
-   private Counts append(AuditStore store, Instant now) throws Exception
+   private Counts append(AuditStore store, Instant now, WormAuditSettings.Values settings) throws Exception
    {
       QRecord tail = store.last(WormAuditTables.CHAIN);
       QRecord anchor = store.anchor();
       long nextId = tail == null ? anchor.getValueLong("anchor_link_id") + 1 : tail.getValueLong("id") + 1;
       String previous = tail == null ? anchor.getValueString("anchor_hash") : tail.getValueString("link_hash");
       List<QRecord> appended = new ArrayList<>();
-      Instant cutoff = now.minusSeconds(config.getSettleHorizonSeconds());
-      List<QRecord> audits = store.unchained(cutoff, config.getBatchSize());
+      Instant cutoff = now.minusSeconds(settings.settle_horizon_seconds());
+      SweepFloors floors = SweepFloors.of(config);
+      SweepFloors.Observation observed = store.isRdbms() ? floors.observe(now, store.maxId("audit"), store.maxId("auditDetail")) : null;
+      boolean full = floors.fullSweepDue(now);
+      List<QRecord> audits = store.unchained(cutoff, config.getBatchSize(), full ? 0L : floors.audit());
       Map<Long, QRecord> details = AuditStore.index(store.in("auditDetail", "auditId", audits.stream().map(r -> r.getValueLong("id")).toList()));
       Map<Long, List<QRecord>> grouped = details.values().stream().collect(Collectors.groupingBy(r -> r.getValueLong("auditId")));
       int lateDetails = 0;
@@ -149,7 +153,7 @@ public class WormAuditChainer
       insertLinks(store, appended);
       int linkCount = appended.size();
       appended.clear();
-      details = AuditStore.index(store.lateDetails(cutoff, config.getLateDetailLookbackHours(), config.getBatchSize()));
+      details = AuditStore.index(store.lateDetails(cutoff, settings.late_detail_lookback_hours(), config.getBatchSize(), full ? 0L : floors.detail()));
       grouped = details.values().stream().collect(Collectors.groupingBy(r -> r.getValueLong("auditId"), TreeMap::new, Collectors.toList()));
       Map<Long, QRecord> headers = AuditStore.index(store.in("audit", "id", grouped.keySet()));
       for(var entry : grouped.entrySet())
@@ -165,12 +169,19 @@ public class WormAuditChainer
          }
       }
       insertLinks(store, appended);
-      return (new Counts(audits.size(), lateDetails, linkCount + appended.size(), checkpoint(store, now), false));
+      if(observed != null)
+      {
+         floors.swept(observed, now.minusSeconds(settings.settle_horizon_seconds()).minus(SweepFloors.MARGIN),
+            audits.size() < config.getBatchSize(), details.size() < config.getBatchSize(), full ? now : null);
+      }
+      return (new Counts(audits.size(), lateDetails, linkCount + appended.size(), checkpoint(store, now, settings), false));
    }
 
 
    /*******************************************************************************
-    ** Persist bounded insert batches inside the writer transaction.
+    ** Persist bounded insert batches inside the writer transaction. PostgreSQL's
+    ** chain trigger writes the detail coverage rows; elsewhere the writer does,
+    ** in the same transaction, so coverage never diverges from the chain.
     *******************************************************************************/
    private void insertLinks(AuditStore store, List<QRecord> links) throws QException
    {
@@ -178,6 +189,18 @@ public class WormAuditChainer
       {
          store.insert(WormAuditTables.CHAIN, links.subList(start, Math.min(start + config.getBatchSize(), links.size())));
       }
+      if(!store.isPostgres())
+      {
+         List<QRecord> coverage = new ArrayList<>();
+         for(QRecord link : links)
+         {
+            AuditStore.detailIds(link).forEach(id -> coverage.add(new QRecord().withValue("detail_id", id).withValue("chain_id", link.getValueLong("id"))));
+         }
+         for(int start = 0; start < coverage.size(); start += config.getBatchSize())
+         {
+            store.insert(WormAuditTables.CHAIN_DETAIL, coverage.subList(start, Math.min(start + config.getBatchSize(), coverage.size())));
+         }
+      }
    }
 
 
@@ -195,7 +218,7 @@ public class WormAuditChainer
    /*******************************************************************************
     ** Checkpoint the uncheckpointed suffix by count or elapsed time.
     *******************************************************************************/
-   private int checkpoint(AuditStore store, Instant now) throws Exception
+   private int checkpoint(AuditStore store, Instant now, WormAuditSettings.Values settings) throws Exception
    {
       if(config.getSigningKey() == null)
       {
@@ -206,13 +229,13 @@ public class WormAuditChainer
       int count = 0;
       while(true)
       {
-         List<QRecord> pending = store.page(WormAuditTables.CHAIN, lastId, config.getCheckpointEveryLinks());
+         List<QRecord> pending = store.page(WormAuditTables.CHAIN, lastId, settings.checkpoint_every_links());
          if(pending.isEmpty())
          {
             return (count);
          }
          Instant since = latest == null ? pending.getFirst().getValueInstant("chained_at") : latest.getValueInstant("created_at");
-         if(pending.size() < config.getCheckpointEveryLinks() && now.isBefore(since.plusSeconds(config.getCheckpointEveryMinutes() * 60L)))
+         if(pending.size() < settings.checkpoint_every_links() && now.isBefore(since.plusSeconds(settings.checkpoint_every_minutes() * 60L)))
          {
             return (count);
          }

src/main/java/com/kingsrook/qbits/wormaudit/ddl/WormAuditDdl.java

@@ -102,6 +102,13 @@ public final class WormAuditDdl
          {
             sql = sql.replace("${" + entry.getValue() + "}", identifier(names.get(entry.getKey())));
          }
+         var defaults = com.kingsrook.qbits.wormaudit.WormAuditSettings.defaults(config);
+         com.kingsrook.qbits.wormaudit.WormAuditSettings.validate(defaults);
+         String values = java.util.List.of("id", "audit_retention_years", "export_lock_mode", "export_retention_days", "checkpoint_every_links",
+            "checkpoint_every_minutes", "chain_interval_seconds", "settle_horizon_seconds", "late_detail_lookback_hours", "export_cron", "verify_cron")
+            .stream().map(field -> defaults.getValue(field) instanceof Number ? defaults.getValue(field).toString()
+               : "'" + defaults.getValueString(field).replace("'", "''") + "'").collect(java.util.stream.Collectors.joining(", "));
+         sql = sql.replace("${settings_values}", values);
          return (sql.replace("${app_role}", identifier(config.getAppRole())).replace("${retention}", config.getRetentionYears() + " years"));
       }
       catch(IOException e)

src/main/java/com/kingsrook/qbits/wormaudit/export/ExportFiles.java

@@ -152,9 +152,9 @@ public class ExportFiles implements AutoCloseable
    /*******************************************************************************
     ** A lock echo is required even if a provider advertises the capability.
     *******************************************************************************/
-   public static boolean locked(StoredObject object, java.time.Instant until)
+   public static boolean locked(StoredObject object, String mode, java.time.Instant until)
    {
-      return (object != null && "COMPLIANCE".equals(object.getLockMode()) && object.getLockRetainUntil() != null
+      return (object != null && java.util.Objects.equals(mode, object.getLockMode()) && object.getLockRetainUntil() != null
          && !object.getLockRetainUntil().isBefore(until));
    }
 

src/main/java/com/kingsrook/qbits/wormaudit/export/ObjectStorageSealedStore.java

@@ -29,7 +29,6 @@ import java.time.Instant;
 import com.kingsrook.qbits.objectstorage.api.Capability;
 import com.kingsrook.qbits.objectstorage.api.CapabilityStatus;
 import com.kingsrook.qbits.objectstorage.api.ListRequest;
-import com.kingsrook.qbits.objectstorage.api.LockMode;
 import com.kingsrook.qbits.objectstorage.api.ObjectStorage;
 import com.kingsrook.qbits.objectstorage.api.ObjectSummary;
 import com.kingsrook.qbits.objectstorage.api.Page;
@@ -84,12 +83,12 @@ public class ObjectStorageSealedStore implements SealedStore
     ** Stream a bounded upload and close the source even when the provider fails.
     *******************************************************************************/
    @Override
-   public String put(String key, Path file, Instant retainUntil) throws Exception
+   public String put(String key, Path file, com.kingsrook.qbits.objectstorage.api.LockMode mode, Instant retainUntil) throws Exception
    {
       try(InputStream input = Files.newInputStream(file))
       {
          return (bucket.put(PutRequest.of(key, input).withContentLength(Files.size(file))
-            .withObjectLock(LockMode.COMPLIANCE, retainUntil)).getVersionId());
+            .withObjectLock(mode, retainUntil)).getVersionId());
       }
    }
 

src/main/java/com/kingsrook/qbits/wormaudit/export/SealedExportVerifier.java

@@ -94,6 +94,7 @@ public class SealedExportVerifier
     *******************************************************************************/
    public Report verify(QRecord export, String trigger) throws QException
    {
+      config.effectiveSettings();
       VerificationResult failure = new VerificationResult();
       failure.scope(0, export.getValueLong("from_link_id"), export.getValueLong("to_link_id"));
       Report report;
@@ -121,22 +122,34 @@ public class SealedExportVerifier
                   throw (new IOException("Missing sealed object version"));
                }
                var head = storage.head(key);
+               if(head == null)
+               {
+                  failure.add(null, null, "object " + key + " is missing" + ("GOVERNANCE".equals(export.getValueString("lock_mode"))
+                     ? "; possible GOVERNANCE bypass removal by an administrator" : ""));
+               }
                if(head != null && !version.equals(head.getVersionId()))
                {
                   failure.add(null, null, "object " + name + " was overwritten after sealing (current version " + head.getVersionId()
                      + ", sealed version " + version + " is still retained under Object Lock)");
                }
-               if(!ExportFiles.locked(head, export.getValueInstant("retain_until")))
+               if(!ExportFiles.locked(head, export.getValueString("lock_mode"), export.getValueInstant("retain_until")))
                {
-                  failure.add(null, null, "object " + name + " lacks the recorded COMPLIANCE retention");
+                  failure.add(null, null, "object " + name + " lacks the recorded " + export.getValueString("lock_mode") + " retention");
                }
             }
             for(String name : ExportFiles.NAMES)
             {
-               try(InputStream input = storage.getVersion(export.getValueString("object_prefix") + name, (String) versions.get(name)))
+               String key = export.getValueString("object_prefix") + name;
+               try(InputStream input = storage.getVersion(key, (String) versions.get(name)))
                {
                   Files.copy(input, files.file(name));
                }
+               catch(Exception e)
+               {
+                  failure.add(null, null, "object " + key + " sealed version cannot be read" + ("GOVERNANCE".equals(export.getValueString("lock_mode"))
+                     ? "; if removed, possible GOVERNANCE bypass removal by an administrator" : ""));
+                  throw (new IOException("Cannot read sealed object version"));
+               }
             }
             report = inspect(files, export, new AuditStore(transaction));
             for(var item : failure.getBreaks())
@@ -189,7 +202,7 @@ public class SealedExportVerifier
          || !Objects.equals(manifest.get("fromLinkId"), from) || !Objects.equals(manifest.get("toLinkId"), to)
          || !Objects.equals(manifest.get("linkCount"), to - from + 1)
          || !Objects.equals(manifest.get("createdAt"), export.getValueInstant("sealed_at").toString())
-         || !"COMPLIANCE".equals(export.getValueString("lock_mode"))
+         || !java.util.Set.of("GOVERNANCE", "COMPLIANCE").contains(export.getValueString("lock_mode"))
          || !Objects.equals(export.getValueString("manifest_key"), export.getValueString("object_prefix") + "manifest.json"))
       {
          result.add(null, null, "Manifest metadata does not match the export row");
@@ -256,7 +269,7 @@ public class SealedExportVerifier
             long start = checkpoint.getValueLong("from_link_id");
             long count = checkpoint.getValueLong("link_count");
             if(start != cursor + 1 || end < start || end > to || count != end - start + 1
-               || count > config.getCheckpointEveryLinks() || checkpointId <= lastCheckpoint)
+               || count > Integer.MAX_VALUE || checkpointId <= lastCheckpoint)
             {
                result.add(null, null, label + "invalid or incomplete checkpoint range");
                break;

src/main/java/com/kingsrook/qbits/wormaudit/export/SealedExporter.java

@@ -243,7 +243,9 @@ public class SealedExporter
       {
          throw (new ExportFailure("Export refused: bucket does not support Object Lock"));
       }
-      Instant until = now.atZone(ZoneOffset.UTC).plusYears(config.getRetentionYears()).toInstant();
+      var settings = config.effectiveSettings();
+      var mode = com.kingsrook.qbits.objectstorage.api.LockMode.valueOf(settings.export_lock_mode());
+      Instant until = now.plus(settings.export_retention_days(), ChronoUnit.DAYS);
       String prefix = config.getExportPrefix() + "/" + DateTimeFormatter.ofPattern("yyyy/MM/dd").withZone(ZoneOffset.UTC).format(now)
          + "/" + scope.from() + "-" + scope.to() + "/";
       QRecord row;
@@ -270,7 +272,7 @@ public class SealedExporter
             .withValue("bucket_alias", config.getExportBucketAlias()).withValue("object_prefix", prefix)
             .withValue("manifest_key", prefix + "manifest.json").withValue("manifest_sha256", ExportFiles.sha256(files.file("manifest.json")))
             .withValue("prev_manifest_sha256", previous).withValue("object_count", 3L)
-            .withValue("lock_mode", "COMPLIANCE").withValue("retain_until", until).withValue("sealed_at", now);
+            .withValue("lock_mode", mode.name()).withValue("retain_until", until).withValue("sealed_at", now);
          if(!new SealedExportVerifier(config, storage).inspect(files, row, store).result().passed())
          {
             throw (new ExportFailure("Export refused: source does not match the signed chain"));
@@ -279,15 +281,15 @@ public class SealedExporter
          Map<String, String> versions = new LinkedHashMap<>();
          for(String name : ExportFiles.NAMES)
          {
-            String version = storage.put(prefix + name, files.file(name), until);
+            String version = storage.put(prefix + name, files.file(name), mode, until);
             if(!ExportFiles.validVersion(version))
             {
                throw (new ExportFailure("Export refused: object " + name + " returned no version id"));
             }
             var headObject = storage.head(prefix + name);
-            if(!ExportFiles.locked(headObject, until))
+            if(!ExportFiles.locked(headObject, mode.name(), until))
             {
-               throw (new ExportFailure("Export refused: object " + name + " did not echo COMPLIANCE retention"));
+               throw (new ExportFailure("Export refused: object " + name + " did not echo " + mode.name() + " retention"));
             }
             if(!version.equals(headObject.getVersionId()))
             {

src/main/java/com/kingsrook/qbits/wormaudit/export/SealedStore.java

@@ -50,9 +50,9 @@ public interface SealedStore
    }
 
    /*******************************************************************************
-    ** Upload a file with COMPLIANCE retention.
+    ** Upload a file with the selected Object Lock mode and retention.
     *******************************************************************************/
-   String put(String key, Path file, Instant retainUntil) throws Exception;
+   String put(String key, Path file, com.kingsrook.qbits.objectstorage.api.LockMode mode, Instant retainUntil) throws Exception;
 
    /*******************************************************************************
     ** Read current object metadata, or null for an absent object.

src/main/java/com/kingsrook/qbits/wormaudit/guard/AppendOnlyGuard.java

@@ -45,6 +45,13 @@ public class AppendOnlyGuard implements MetaDataCustomizerInterface<QTableMetaDa
    @Override
    public QTableMetaData customizeMetaData(QInstance instance, QTableMetaData table)
    {
+      if(com.kingsrook.qbits.wormaudit.model.WormAuditTables.SETTINGS.equals(table.getName()))
+      {
+         table.withoutCapabilities(Capability.TABLE_INSERT, Capability.TABLE_DELETE);
+         table.withAuditRules(new com.kingsrook.qqq.backend.core.model.metadata.audits.QAuditRules()
+            .withAuditLevel(com.kingsrook.qqq.backend.core.model.metadata.audits.AuditLevel.FIELD));
+         return (table);
+      }
       if(CORE_TABLES.contains(table.getName()) || table.getName().startsWith("worm_audit_"))
       {
          table.withoutCapabilities(Capability.TABLE_UPDATE, Capability.TABLE_DELETE);

src/main/java/com/kingsrook/qbits/wormaudit/model/SettingsCustomizer.java

@@ -0,0 +1,116 @@
+/*
+ * 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.wormaudit.model;
+
+
+import java.time.Instant;
+import java.util.List;
+import java.util.Optional;
+import com.kingsrook.qbits.wormaudit.WormAuditSettings;
+import com.kingsrook.qqq.backend.core.actions.customizers.TableCustomizerInterface;
+import com.kingsrook.qqq.backend.core.actions.permissions.PermissionsHelper;
+import com.kingsrook.qqq.backend.core.actions.permissions.TablePermissionSubType;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.exceptions.QException;
+import com.kingsrook.qqq.backend.core.exceptions.QUserFacingException;
+import com.kingsrook.qqq.backend.core.model.actions.tables.delete.DeleteInput;
+import com.kingsrook.qqq.backend.core.model.actions.tables.insert.InsertInput;
+import com.kingsrook.qqq.backend.core.model.actions.tables.update.UpdateInput;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+
+
+/*******************************************************************************
+ ** Validate the editable singleton, including internal insert-only seeds.
+ *******************************************************************************/
+public class SettingsCustomizer implements TableCustomizerInterface
+{
+   /*******************************************************************************
+    ** Only the fixed singleton may be inserted by the internal seed path.
+    *******************************************************************************/
+   @Override
+   public List<QRecord> preInsert(InsertInput input, List<QRecord> records, boolean preview) throws QException
+   {
+      if(records.size() != 1 || !Integer.valueOf(1).equals(records.getFirst().getValueInteger("id")))
+      {
+         throw (new QUserFacingException("WORM Audit Settings has one row only (ID 1)."));
+      }
+      if(new com.kingsrook.qbits.wormaudit.chain.AuditStore(input.getTransaction()).get(WormAuditTables.SETTINGS, 1) != null)
+      {
+         throw (new QUserFacingException("WORM Audit Settings already exists. Edit record 1 instead of adding another record."));
+      }
+      return (validate(records));
+   }
+
+
+   /*******************************************************************************
+    ** Validate partial edits without replacing the existing settings.
+    *******************************************************************************/
+   @Override
+   public List<QRecord> preUpdate(UpdateInput input, List<QRecord> records, boolean preview, Optional<List<QRecord>> oldRecords) throws QException
+   {
+      PermissionsHelper.checkTablePermissionThrowing(input, TablePermissionSubType.EDIT);
+      return (validate(records));
+   }
+
+
+   /*******************************************************************************
+    ** Reject deletion even for direct core calls where capability checks are off.
+    *******************************************************************************/
+   @Override
+   public List<QRecord> preDelete(DeleteInput input, List<QRecord> records, boolean preview) throws QException
+   {
+      throw (new QUserFacingException("WORM Audit Settings cannot be deleted."));
+   }
+
+
+   /*******************************************************************************
+    ** Refresh local settings after persistence; remote JVMs retain the bounded cache.
+    *******************************************************************************/
+   @Override
+   public List<QRecord> postUpdate(UpdateInput input, List<QRecord> records, Optional<List<QRecord>> oldRecords) throws QException
+   {
+      com.kingsrook.qbits.wormaudit.processes.VerificationRuns.config().invalidateSettings();
+      return (records);
+   }
+
+
+   /*******************************************************************************
+    ** Stamp the editor and time on every valid write; core writes FIELD audits.
+    *******************************************************************************/
+   private List<QRecord> validate(List<QRecord> records) throws QException
+   {
+      for(QRecord row : records)
+      {
+         try
+         {
+            WormAuditSettings.validate(row);
+         }
+         catch(IllegalArgumentException e)
+         {
+            throw (new QUserFacingException(e.getMessage()));
+         }
+         row.setValue("modify_date", Instant.now());
+         row.setValue("modified_by", QContext.getQSession() == null || QContext.getQSession().getUser() == null
+            ? "system" : QContext.getQSession().getUser().getIdReference());
+      }
+      return (records);
+   }
+}

src/main/java/com/kingsrook/qbits/wormaudit/model/SettingsPermissions.java

@@ -0,0 +1,55 @@
+/*
+ * 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.wormaudit.model;
+
+
+import com.kingsrook.qqq.backend.core.actions.permissions.CustomPermissionChecker;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.exceptions.QPermissionDeniedException;
+import com.kingsrook.qqq.backend.core.model.actions.AbstractActionInput;
+import com.kingsrook.qqq.backend.core.model.actions.tables.delete.DeleteInput;
+import com.kingsrook.qqq.backend.core.model.actions.tables.insert.InsertInput;
+import com.kingsrook.qqq.backend.core.model.actions.tables.update.UpdateInput;
+import com.kingsrook.qqq.backend.core.model.metadata.permissions.MetaDataWithPermissionRules;
+
+
+/*******************************************************************************
+ ** Reading uses the full table name; editing additionally needs settings access.
+ *******************************************************************************/
+public class SettingsPermissions implements CustomPermissionChecker
+{
+   /*******************************************************************************
+    ** Enforce table permission and the separate settings permission.
+    *******************************************************************************/
+   @Override
+   public void checkPermissionsThrowing(AbstractActionInput input, MetaDataWithPermissionRules metadata) throws QPermissionDeniedException
+   {
+      var session = QContext.getQSession();
+      boolean edit = input instanceof UpdateInput;
+      if(input instanceof InsertInput || input instanceof DeleteInput || session == null
+         || !session.hasPermission(WormAuditTables.SETTINGS + (edit ? ".edit" : ".read"))
+         || (edit && !session.hasPermission("worm_audit.settings")))
+      {
+         throw (new QPermissionDeniedException(edit ? "Editing WORM Audit Settings requires worm_audit_settings.edit and worm_audit.settings."
+            : "WORM Audit Settings requires read access; adding or deleting settings is not allowed."));
+      }
+   }
+}

src/main/java/com/kingsrook/qbits/wormaudit/model/WormAuditHelp.java

@@ -0,0 +1,139 @@
+/*
+ * 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.wormaudit.model;
+
+
+import java.io.IOException;
+import java.io.InputStream;
+import java.util.LinkedHashMap;
+import java.util.Map;
+import com.fasterxml.jackson.databind.JsonNode;
+import com.fasterxml.jackson.databind.ObjectMapper;
+import com.fasterxml.jackson.dataformat.yaml.YAMLFactory;
+import com.kingsrook.qbits.wormaudit.WormAuditQBitConfig;
+import com.kingsrook.qqq.backend.core.exceptions.QException;
+import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
+import com.kingsrook.qqq.backend.core.model.metadata.help.QHelpContent;
+import com.kingsrook.qqq.backend.core.model.metadata.layout.QAppMetaData;
+import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
+
+
+/*******************************************************************************
+ ** Applies help/qbit-worm-audit.yaml (the QQQ help-bundle format) to the WORM
+ ** Audit app and its worm_audit_* tables: what each table is for and how its
+ ** rows get created, shown on the table screen and when it is empty.
+ ** The bundle's app entry is keyed to the default app name; a host that renames
+ ** the app gets the same text.
+ ** STAND-IN(core=QRun-IO/qqq#1198, removeBy=4.1.0-RC.7): replace with
+ ** HelpBundles.register once the pinned QQQ release includes it; the bundle
+ ** file stays the same.
+ *******************************************************************************/
+public final class WormAuditHelp
+{
+   public static final String RESOURCE = "/help/qbit-worm-audit.yaml";
+   public static final String SLOT     = "help";
+
+
+
+   /*******************************************************************************
+    ** Utility class.
+    *******************************************************************************/
+   private WormAuditHelp()
+   {
+   }
+
+
+
+   /*******************************************************************************
+    ** The bundle's entries by key, validated (fails startup if malformed).
+    *******************************************************************************/
+   public static Map<String, JsonNode> entries() throws QException
+   {
+      try(InputStream stream = WormAuditHelp.class.getResourceAsStream(RESOURCE))
+      {
+         if(stream == null)
+         {
+            throw (new IOException("missing resource"));
+         }
+         JsonNode bundle = new ObjectMapper(new YAMLFactory()).readTree(stream);
+         if(!"qbit-worm-audit".equals(bundle.path("bundle").asText()) || !"QBIT".equals(bundle.path("layer").asText()))
+         {
+            throw (new IOException("invalid bundle header"));
+         }
+         Map<String, JsonNode> entries = new LinkedHashMap<>();
+         for(JsonNode entry : bundle.path("entries"))
+         {
+            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)
+            {
+               throw (new IOException("invalid or duplicate entry: " + key));
+            }
+         }
+         return (entries);
+      }
+      catch(IOException | RuntimeException e)
+      {
+         throw (new QException("Could not load " + RESOURCE, e));
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Apply the help to the app and every worm_audit_* table. A host's own help on
+    ** a table or the app is kept (the bundle only fills the slot).
+    *******************************************************************************/
+   public static void apply(QInstance qInstance, WormAuditQBitConfig config) throws QException
+   {
+      for(Map.Entry<String, JsonNode> entry : entries().entrySet())
+      {
+         String key = entry.getKey();
+         if(key.startsWith("table:"))
+         {
+            QTableMetaData table = qInstance.getTable(key.substring("table:".length(), key.indexOf(';')));
+            if(table != null && (table.getHelpContent() == null || !table.getHelpContent().containsKey(SLOT)))
+            {
+               table.withHelpContent(SLOT, content(entry.getValue()));
+            }
+         }
+         else if(config.getIncludeApp() && key.startsWith("app:"))
+         {
+            QAppMetaData app = qInstance.getApp(config.getAppName());
+            if(app != null && (app.getHelpContent() == null || !app.getHelpContent().containsKey(SLOT)))
+            {
+               app.withHelpContent(SLOT, content(entry.getValue()));
+            }
+         }
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Summary as the first paragraph, then the content, as markdown.
+    *******************************************************************************/
+   static QHelpContent content(JsonNode entry)
+   {
+      return (new QHelpContent().withContentAsMarkdown(entry.path("summary").asText().strip() + "\n\n" + entry.path("content").asText().strip()));
+   }
+
+}

src/main/java/com/kingsrook/qbits/wormaudit/model/WormAuditTables.java

@@ -24,16 +24,21 @@ package com.kingsrook.qbits.wormaudit.model;
 import java.util.ArrayList;
 import java.util.List;
 import com.kingsrook.qbits.wormaudit.WormAuditQBitConfig;
+import com.kingsrook.qbits.wormaudit.WormAuditSettings;
+import com.kingsrook.qqq.backend.core.actions.customizers.TableCustomizers;
 import com.kingsrook.qqq.backend.core.model.metadata.audits.AuditLevel;
 import com.kingsrook.qqq.backend.core.model.metadata.audits.QAuditRules;
+import com.kingsrook.qqq.backend.core.model.metadata.code.QCodeReference;
 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.help.QHelpContent;
 import com.kingsrook.qqq.backend.core.model.metadata.layout.QIcon;
 import com.kingsrook.qqq.backend.core.model.metadata.permissions.PermissionLevel;
 import com.kingsrook.qqq.backend.core.model.metadata.permissions.QPermissionRules;
 import com.kingsrook.qqq.backend.core.model.metadata.possiblevalues.QPossibleValue;
 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.Capability;
 import com.kingsrook.qqq.backend.core.model.metadata.tables.QFieldSection;
 import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
 import com.kingsrook.qqq.backend.core.model.metadata.tables.Tier;
@@ -47,12 +52,14 @@ import com.kingsrook.qqq.backend.module.rdbms.model.metadata.RDBMSTableBackendDe
 public final class WormAuditTables
 {
    public static final String CHAIN = "worm_audit_chain";
+   public static final String CHAIN_DETAIL = "worm_audit_chain_detail";
    public static final String CHECKPOINT = "worm_audit_checkpoint";
    public static final String EXPORT = "worm_audit_export";
    public static final String VERIFICATION = "worm_audit_verification";
    public static final String PURGE = "worm_audit_purge";
-   public static final List<String> ALL = List.of(CHAIN, CHECKPOINT, EXPORT, VERIFICATION, PURGE);
-   public static final List<String> NAV = List.of(VERIFICATION, EXPORT, CHECKPOINT, CHAIN, PURGE);
+   public static final String SETTINGS = "worm_audit_settings";
+   public static final List<String> ALL = List.of(CHAIN, CHAIN_DETAIL, CHECKPOINT, EXPORT, VERIFICATION, PURGE, SETTINGS);
+   public static final List<String> NAV = List.of(VERIFICATION, EXPORT, CHECKPOINT, CHAIN, CHAIN_DETAIL, PURGE, SETTINGS);
 
    /*******************************************************************************
     ** Utility class.
@@ -69,14 +76,79 @@ public final class WormAuditTables
    {
       List<QTableMetaData> tables = new ArrayList<>();
       tables.add(table(config, CHAIN, "Audit Chain Link", "id:L kind:S audit_id:L detail_ids:T prev_hash:S link_hash:S chained_at:D"));
+      // Keyed by the covered audit_detail id: one row per detail, written with its link.
+      tables.add(table(config, CHAIN_DETAIL, "Detail Coverage", "detail_id:L chain_id:L").withPrimaryKeyField("detail_id")
+         .withRecordLabelFormatAndFields("Detail #%s (link %s)", "detail_id", "chain_id"));
       tables.add(table(config, CHECKPOINT, "Checkpoint", "id:L from_link_id:L to_link_id:L link_count:L head_hash:S merkle_root:S algorithm:S key_id:S public_key:T signature:T created_at:D"));
       tables.add(table(config, EXPORT, "Sealed Export", "id:L from_link_id:L to_link_id:L from_checkpoint_id:L to_checkpoint_id:L bucket_alias:S object_prefix:T object_versions:T manifest_key:T manifest_sha256:S prev_manifest_sha256:S object_count:L byte_count:L lock_mode:S retain_until:D sealed_at:D"));
       tables.add(table(config, VERIFICATION, "Verification", "id:L kind:S trigger:S started_at:D finished_at:D from_link_id:L to_link_id:L links_checked:L outcome:S break_count:L first_break_link_id:L first_break_audit_id:L first_break_reason:T export_id:L run_by:S"));
       tables.add(table(config, PURGE, "Purge", "id:L requested_at:D requested_by:S cutoff:D retention_interval:S outcome:S reason:T audits_deleted:L details_deleted:L links_deleted:L anchor_link_id:L anchor_hash:S"));
+      tables.add(settings(config));
       return (tables);
    }
 
 
+   /*******************************************************************************
+    ** Editable, FIELD-audited singleton with standard QQQ record sections.
+    *******************************************************************************/
+   public static QTableMetaData settings(WormAuditQBitConfig config)
+   {
+      QTableMetaData table = new QTableMetaData().withName(SETTINGS).withLabel("WORM Audit Settings")
+         .withBackendName(config.getBackendName()).withBackendDetails(new RDBMSTableBackendDetails().withTableName(SETTINGS))
+         .withPrimaryKeyField("id").withRecordLabelFields("id").withRecordLabelFormat("WORM Audit Settings")
+         .withAuditRules(new QAuditRules().withAuditLevel(AuditLevel.FIELD))
+         .withPermissionRules(new QPermissionRules().withLevel(PermissionLevel.READ_INSERT_EDIT_DELETE_PERMISSIONS)
+            .withPermissionBaseName(SETTINGS).withCustomPermissionChecker(new QCodeReference(SettingsPermissions.class)))
+         .withoutCapabilities(Capability.TABLE_INSERT, Capability.TABLE_DELETE)
+         .withCustomizer(TableCustomizers.PRE_INSERT_RECORD, new QCodeReference(SettingsCustomizer.class))
+         .withCustomizer(TableCustomizers.PRE_UPDATE_RECORD, new QCodeReference(SettingsCustomizer.class))
+         .withCustomizer(TableCustomizers.POST_UPDATE_RECORD, new QCodeReference(SettingsCustomizer.class))
+         .withCustomizer(TableCustomizers.PRE_DELETE_RECORD, new QCodeReference(SettingsCustomizer.class))
+         .withField(new QFieldMetaData("id", QFieldType.INTEGER).withLabel("ID").withBackendName("id").withIsEditable(false).withDefaultValue(1));
+      var defaults = WormAuditSettings.defaults(config);
+      table.withField(new QFieldMetaData("audit_retention_years", QFieldType.INTEGER).withBackendName("audit_retention_years").withLabel("Audit retention years")
+         .withIsRequired(true).withDefaultValue(defaults.getValue("audit_retention_years")));
+      table.withField(new QFieldMetaData("export_lock_mode", QFieldType.STRING).withBackendName("export_lock_mode").withLabel("Export lock mode")
+         .withIsRequired(true).withDefaultValue(defaults.getValue("export_lock_mode")));
+      table.withField(new QFieldMetaData("export_retention_days", QFieldType.INTEGER).withBackendName("export_retention_days").withLabel("Export retention days")
+         .withIsRequired(true).withDefaultValue(defaults.getValue("export_retention_days")));
+      table.withField(new QFieldMetaData("checkpoint_every_links", QFieldType.INTEGER).withBackendName("checkpoint_every_links").withLabel("Checkpoint every links")
+         .withIsRequired(true).withDefaultValue(defaults.getValue("checkpoint_every_links")));
+      table.withField(new QFieldMetaData("checkpoint_every_minutes", QFieldType.INTEGER).withBackendName("checkpoint_every_minutes").withLabel("Checkpoint every minutes")
+         .withIsRequired(true).withDefaultValue(defaults.getValue("checkpoint_every_minutes")));
+      table.withField(new QFieldMetaData("chain_interval_seconds", QFieldType.INTEGER).withBackendName("chain_interval_seconds").withLabel("Chain interval seconds")
+         .withIsRequired(true).withDefaultValue(defaults.getValue("chain_interval_seconds")));
+      table.withField(new QFieldMetaData("settle_horizon_seconds", QFieldType.INTEGER).withBackendName("settle_horizon_seconds").withLabel("Settle horizon seconds")
+         .withIsRequired(true).withDefaultValue(defaults.getValue("settle_horizon_seconds")));
+      table.withField(new QFieldMetaData("late_detail_lookback_hours", QFieldType.INTEGER).withBackendName("late_detail_lookback_hours").withLabel("Late detail lookback hours")
+         .withIsRequired(true).withDefaultValue(defaults.getValue("late_detail_lookback_hours")));
+      table.withField(new QFieldMetaData("export_cron", QFieldType.STRING).withBackendName("export_cron").withLabel("Export schedule (UTC)")
+         .withIsRequired(true).withDefaultValue(defaults.getValue("export_cron")));
+      table.withField(new QFieldMetaData("verify_cron", QFieldType.STRING).withBackendName("verify_cron").withLabel("Verify schedule (UTC)")
+         .withIsRequired(true).withDefaultValue(defaults.getValue("verify_cron")));
+      table.getField("export_lock_mode").withInlinePossibleValueSource(new QPossibleValueSource().withName(SETTINGS + "_lock_mode")
+         .withType(QPossibleValueSourceType.ENUM).withIdType(QFieldType.STRING).withEnumValues(List.of(
+            new QPossibleValue<>("GOVERNANCE", "GOVERNANCE"), new QPossibleValue<>("COMPLIANCE", "COMPLIANCE"))));
+      for(String field : List.of("export_cron", "verify_cron", "chain_interval_seconds"))
+      {
+         table.getField(field).withHelpContent(new QHelpContent("Takes effect after restart. Cron schedules use UTC and Quartz syntax."));
+      }
+      table.getField("audit_retention_years").withHelpContent(new QHelpContent("Used for the next eligible purge date. The database purge interval changes only through a reviewed migration."));
+      table.withField(new QFieldMetaData("notes", QFieldType.TEXT).withLabel("Notes").withBackendName("notes"));
+      table.withField(new QFieldMetaData("modify_date", QFieldType.DATE_TIME).withLabel("Modified").withBackendName("modify_date").withIsEditable(false));
+      table.withField(new QFieldMetaData("modified_by", QFieldType.STRING).withLabel("Modified by").withBackendName("modified_by").withIsEditable(false));
+      table.withSection(new QFieldSection("retention", "Retention", new QIcon("lock"), Tier.T1,
+         List.of("id", "audit_retention_years", "export_lock_mode", "export_retention_days")).withGridColumns(12));
+      table.withSection(new QFieldSection("cadence", "Chain and checkpoints", new QIcon("link"), Tier.T2,
+         List.of("checkpoint_every_links", "checkpoint_every_minutes", "settle_horizon_seconds", "late_detail_lookback_hours")).withGridColumns(12));
+      table.withSection(new QFieldSection("schedules", "Schedules (after restart)", new QIcon("schedule"), Tier.T2,
+         List.of("chain_interval_seconds", "export_cron", "verify_cron")).withGridColumns(12));
+      table.withSection(new QFieldSection("changes", "Changes", new QIcon("edit_note"), Tier.T2,
+         List.of("notes", "modify_date", "modified_by")).withGridColumns(12));
+      return (table);
+   }
+
+
    /*******************************************************************************
     ** Common metadata for a process-written table.
     *******************************************************************************/
@@ -99,7 +171,7 @@ public final class WormAuditTables
          };
          table.withField(new QFieldMetaData(parts[0], type).withBackendName(parts[0]).withLabel(fieldLabel(parts[0])));
       }
-      for(String reference : List.of("from_link_id", "to_link_id", "first_break_link_id", "anchor_link_id"))
+      for(String reference : List.of("from_link_id", "to_link_id", "first_break_link_id", "anchor_link_id", "chain_id"))
       {
          if(table.getFields().containsKey(reference))
          {
@@ -169,7 +241,9 @@ public final class WormAuditTables
    {
       return (switch(name)
       {
+         case SETTINGS -> "Settings";
          case CHAIN -> "Audit Chain";
+         case CHAIN_DETAIL -> "Detail Coverage";
          case CHECKPOINT -> "Checkpoints";
          case EXPORT -> "Sealed Exports";
          case VERIFICATION -> "Verifications";
@@ -189,6 +263,8 @@ public final class WormAuditTables
          case "kind" -> "Kind";
          case "audit_id" -> "Audit";
          case "detail_ids" -> "Detail ids";
+         case "detail_id" -> "Audit detail";
+         case "chain_id" -> "Link";
          case "prev_hash" -> "Previous hash";
          case "link_hash" -> "Link hash";
          case "chained_at" -> "Chained";

src/main/java/com/kingsrook/qbits/wormaudit/processes/ExportProcesses.java

@@ -84,6 +84,7 @@ public final class ExportProcesses
     *******************************************************************************/
    public static List<QProcessMetaData> processes(WormAuditQBitConfig config)
    {
+      var settings = config.effectiveSettings();
       Map<String, Serializable> values = new HashMap<>();
       values.put(StreamedETLWithFrontendProcess.FIELD_SOURCE_TABLE, WormAuditTables.EXPORT);
       values.put(StreamedETLWithFrontendProcess.FIELD_DESTINATION_TABLE, WormAuditTables.EXPORT);
@@ -97,7 +98,7 @@ public final class ExportProcesses
       QProcessMetaData daily = new QProcessMetaData().withName(DAILY).withLabel("Daily sealed audit export")
          .withStep(new QBackendStepMetaData().withName("export").withCode(new QCodeReference(Daily.class)))
          .withPermissionRules(permission(PERMISSION)).withSchedule(new QScheduleMetaData().withSchedulerName(config.getSchedulerName())
-            .withCronExpression(config.getExportCron()).withCronTimeZoneId("UTC"));
+            .withCronExpression(settings.export_cron()).withCronTimeZoneId("UTC"));
       return (List.of(export, verify, daily));
    }
 

src/main/java/com/kingsrook/qbits/wormaudit/processes/VerificationRuns.java

@@ -24,6 +24,7 @@ package com.kingsrook.qbits.wormaudit.processes;
 import java.time.Instant;
 import java.util.List;
 import java.util.Set;
+import java.util.stream.Collectors;
 import com.kingsrook.qbits.wormaudit.WormAuditQBitConfig;
 import com.kingsrook.qbits.wormaudit.chain.AuditStore;
 import com.kingsrook.qbits.wormaudit.model.WormAuditTables;
@@ -43,6 +44,11 @@ import com.kingsrook.qqq.backend.core.model.data.QRecord;
  *******************************************************************************/
 public final class VerificationRuns
 {
+   /** How many break reasons the alert summary spells out; the title carries the total. */
+   private static final int ALERT_SUMMARY_REASONS = 3;
+
+
+
    /*******************************************************************************
     ** Utility class.
     *******************************************************************************/
@@ -79,8 +85,15 @@ public final class VerificationRuns
             }
             else
             {
+               long problems = result.getBreakCount();
+               String summary = result.getBreaks().stream().limit(ALERT_SUMMARY_REASONS).map(b -> b.reason()).collect(Collectors.joining("; "));
+               if(problems > ALERT_SUMMARY_REASONS)
+               {
+                  summary += "; and " + (problems - ALERT_SUMMARY_REASONS) + " more";
+               }
                QAlerts.raise(new AlertSignal("wormaudit.chain.break").withDedupKey("chain")
-                  .withTitle(result.getBreaks().getFirst().reason()).withRecordRef(new AlertRecordRef(WormAuditTables.VERIFICATION, row.getValue("id"))));
+                  .withTitle("Audit chain verification found " + problems + (problems == 1 ? " problem" : " problems")).withSummary(summary)
+                  .withRecordRef(new AlertRecordRef(WormAuditTables.VERIFICATION, row.getValue("id"))));
             }
          }
          return (result);

src/main/java/com/kingsrook/qbits/wormaudit/processes/WormAuditProcesses.java

@@ -89,6 +89,7 @@ public final class WormAuditProcesses
     *******************************************************************************/
    public static List<QProcessMetaData> processes(WormAuditQBitConfig config)
    {
+      var settings = config.effectiveSettings();
       Map<String, Serializable> values = new HashMap<>();
       values.put(StreamedETLWithFrontendProcess.FIELD_SOURCE_TABLE, WormAuditTables.CHAIN);
       values.put(StreamedETLWithFrontendProcess.FIELD_DESTINATION_TABLE, WormAuditTables.CHAIN);
@@ -100,11 +101,11 @@ public final class WormAuditProcesses
       QProcessMetaData chain = new QProcessMetaData().withName(CHAIN).withLabel("Chain settled audit records")
          .withStep(new QBackendStepMetaData().withName("chain").withCode(new QCodeReference(ChainTick.class)))
          .withPermissionRules(permission()).withSchedule(new QScheduleMetaData().withSchedulerName(config.getSchedulerName())
-            .withRepeatSeconds(config.getChainIntervalSeconds()).withInitialDelaySeconds(config.getChainIntervalSeconds()));
+            .withRepeatSeconds(settings.chain_interval_seconds()).withInitialDelaySeconds(settings.chain_interval_seconds()));
       QProcessMetaData daily = new QProcessMetaData().withName(DAILY).withLabel("Daily audit chain verification")
          .withStep(new QBackendStepMetaData().withName("verify").withCode(new QCodeReference(Daily.class)))
          .withPermissionRules(permission()).withSchedule(new QScheduleMetaData().withSchedulerName(config.getSchedulerName())
-            .withCronExpression(config.getVerifyCron()).withCronTimeZoneId("UTC"));
+            .withCronExpression(settings.verify_cron()).withCronTimeZoneId("UTC"));
       return (List.of(verify, chain, daily));
    }
 

src/main/java/com/kingsrook/qbits/wormaudit/verify/WormAuditVerifier.java

@@ -133,6 +133,7 @@ public class WormAuditVerifier
     *******************************************************************************/
    VerificationResult verify(AuditStore store, Set<Long> selection, Instant now) throws QException
    {
+      var settings = config.effectiveSettings();
       VerificationResult result = new VerificationResult(now);
       if(config.getSigningKey() == null)
       {
@@ -214,7 +215,7 @@ public class WormAuditVerifier
       }
       if(selection == null)
       {
-         Instant cutoff = now.minusSeconds(config.getSettleHorizonSeconds());
+         Instant cutoff = now.minusSeconds(settings.settle_horizon_seconds());
          if(store.isRdbms())
          {
             store.completeness(cutoff, result);
@@ -500,7 +501,7 @@ public class WormAuditVerifier
             result.add(null, null, prefix + "signature does not verify");
          }
          if(from < 1 || to < from || checkpoint.getValueLong("link_count") != to - from + 1
-            || to - from + 1 > config.getCheckpointEveryLinks())
+            || to - from + 1 > Integer.MAX_VALUE)
          {
             result.add(null, null, prefix + "invalid link range or count");
             return;

src/main/resources/com/kingsrook/qbits/wormaudit/ddl/postgres/worm-audit.sql

@@ -324,3 +324,233 @@ CREATE INDEX IF NOT EXISTS worm_audit_chain_detail_coverage ON public.worm_audit
 DROP TRIGGER IF EXISTS worm_audit_detail_coverage_guard ON public.worm_audit_chain;
 CREATE TRIGGER worm_audit_detail_coverage_guard BEFORE INSERT ON public.worm_audit_chain
    FOR EACH ROW EXECUTE FUNCTION public.worm_audit_detail_coverage();
+
+-- changeset worm-audit:10
+-- Editable singleton: deliberately excluded from append-only triggers.
+CREATE TABLE IF NOT EXISTS public.worm_audit_settings (
+   id integer PRIMARY KEY CHECK (id = 1),
+   audit_retention_years integer NOT NULL DEFAULT 6 CHECK (audit_retention_years BETWEEN 1 AND 100),
+   export_lock_mode text NOT NULL DEFAULT 'GOVERNANCE' CHECK (export_lock_mode IN ('GOVERNANCE', 'COMPLIANCE')),
+   export_retention_days integer NOT NULL DEFAULT 30 CHECK (export_retention_days BETWEEN 1 AND 36500),
+   checkpoint_every_links integer NOT NULL DEFAULT 10000 CHECK (checkpoint_every_links > 0),
+   checkpoint_every_minutes integer NOT NULL DEFAULT 60 CHECK (checkpoint_every_minutes > 0),
+   chain_interval_seconds integer NOT NULL DEFAULT 30 CHECK (chain_interval_seconds > 0),
+   settle_horizon_seconds integer NOT NULL DEFAULT 60 CHECK (settle_horizon_seconds >= 0),
+   late_detail_lookback_hours integer NOT NULL DEFAULT 24 CHECK (late_detail_lookback_hours > 0),
+   export_cron text NOT NULL DEFAULT '0 0 2 * * ?',
+   verify_cron text NOT NULL DEFAULT '0 0 3 * * ?',
+   notes text,
+   modify_date timestamptz NOT NULL DEFAULT now(),
+   modified_by text NOT NULL DEFAULT current_user
+);
+REVOKE DELETE, TRUNCATE ON public.worm_audit_settings FROM ${app_role};
+GRANT SELECT, INSERT, UPDATE ON public.worm_audit_settings TO ${app_role};
+INSERT INTO public.worm_audit_settings
+   (id, audit_retention_years, export_lock_mode, export_retention_days, checkpoint_every_links,
+    checkpoint_every_minutes, chain_interval_seconds, settle_horizon_seconds, late_detail_lookback_hours,
+    export_cron, verify_cron)
+SELECT ${settings_values}
+WHERE NOT EXISTS (SELECT 1 FROM public.worm_audit_settings WHERE id = 1);
+
+-- changeset worm-audit:11
+-- comment: Index the foreign key columns added by worm-audit:12 (worm_audit_chain.audit_id already leads worm_audit_chain_audit_all).
+CREATE INDEX IF NOT EXISTS worm_audit_export_from_checkpoint ON public.worm_audit_export(from_checkpoint_id);
+CREATE INDEX IF NOT EXISTS worm_audit_export_to_checkpoint ON public.worm_audit_export(to_checkpoint_id);
+CREATE INDEX IF NOT EXISTS worm_audit_verification_export ON public.worm_audit_verification(export_id);
+
+-- changeset worm-audit:12 splitStatements:false
+-- comment: Foreign keys, ON DELETE RESTRICT only: nothing cascades into or out of audit evidence. A chain link needs its audit header (the purge deletes links before headers); a sealed export needs its signed checkpoints and an export verification its export (neither is ever deleted). Links referenced by checkpoints, exports, verifications and purges are not foreign keys: the purge deletes the sealed prefix those rows outlive. Rows whose parent is already missing are evidence and are never changed: they are copied to qqq_fk_quarantine (action KEPT) and the constraint is left NOT VALID (enforced for new rows only).
+DO $$
+DECLARE
+   fk record;
+   orphans bigint;
+BEGIN
+   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);
+   FOR fk IN SELECT * FROM (VALUES
+      ('fk_worm_audit_chain_audit_id', 'worm_audit_chain', 'audit_id', '${audit}'),
+      ('fk_worm_audit_export_from_checkpoint_id', 'worm_audit_export', 'from_checkpoint_id', 'public.worm_audit_checkpoint'),
+      ('fk_worm_audit_export_to_checkpoint_id', 'worm_audit_export', 'to_checkpoint_id', 'public.worm_audit_checkpoint'),
+      ('fk_worm_audit_verification_export_id', 'worm_audit_verification', 'export_id', 'public.worm_audit_export')
+   ) AS t(name, child, col, parent) LOOP
+      IF NOT EXISTS (SELECT FROM pg_constraint WHERE conname = fk.name) THEN
+         EXECUTE format('INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset)'
+            || ' SELECT %L, %L, c.id::text, c.%I::text, ''KEPT'', row_to_json(c)::text, ''worm-audit:12'' FROM public.%I c'
+            || ' WHERE c.%I IS NOT NULL AND NOT EXISTS (SELECT FROM %s p WHERE p.id = c.%I)', fk.child, fk.col, fk.col, fk.child, fk.col, fk.parent, fk.col);
+         GET DIAGNOSTICS orphans = ROW_COUNT;
+         EXECUTE format('ALTER TABLE public.%I ADD CONSTRAINT %I FOREIGN KEY (%I) REFERENCES %s (id) ON DELETE RESTRICT NOT VALID', fk.child, fk.name, fk.col, fk.parent);
+         IF orphans = 0 THEN
+            EXECUTE format('ALTER TABLE public.%I VALIDATE CONSTRAINT %I', fk.child, fk.name);
+         END IF;
+      END IF;
+   END LOOP;
+END;
+$$;
+
+-- changeset worm-audit:13 splitStatements:false
+-- comment: Normalized detail coverage. One insert-only row per audit_detail id covered by a chain link, written by the coverage trigger in the same statement as the link, so the chain writer probes a primary key instead of the GIN array index. Append-only like the chain, with no foreign keys (the owner cannot UPDATE WORM tables, so FK checks would be denied). The advisory lock keeps a running chainer from appending between the backfill and the new trigger body.
+DO $$
+BEGIN
+   PERFORM pg_advisory_xact_lock(86753090421);
+END;
+$$;
+CREATE TABLE IF NOT EXISTS public.worm_audit_chain_detail (
+   detail_id bigint PRIMARY KEY,
+   chain_id bigint NOT NULL,
+   CHECK (detail_id >= 0),
+   CHECK (chain_id > 0)
+);
+CREATE INDEX IF NOT EXISTS worm_audit_chain_detail_chain ON public.worm_audit_chain_detail(chain_id);
+-- The first link wins if a tampered chain covers a detail twice; the verifier still reports the duplicate from the chain itself.
+INSERT INTO public.worm_audit_chain_detail(detail_id, chain_id)
+   SELECT x.id::bigint, c.id FROM public.worm_audit_chain c CROSS JOIN LATERAL unnest(string_to_array(c.detail_ids, ',')) AS x(id)
+   WHERE c.detail_ids <> '' ORDER BY c.id
+   ON CONFLICT (detail_id) DO NOTHING;
+
+CREATE OR REPLACE FUNCTION public.worm_audit_reject() RETURNS trigger
+LANGUAGE plpgsql SET search_path = pg_catalog, public, pg_temp SET timezone = 'UTC' AS $fn$
+DECLARE
+   old_time timestamptz;
+   function_owner name;
+   stack text;
+   row_identity text := 'all';
+BEGIN
+   IF TG_OP = 'DELETE' AND current_setting('worm_audit.purging', true) = 'on' THEN
+      SELECT pg_get_userbyid(proowner) INTO function_owner FROM pg_proc
+         WHERE oid = 'public.worm_audit_purge(timestamptz)'::regprocedure;
+      GET DIAGNOSTICS stack = PG_CONTEXT;
+      IF current_user = function_owner AND position('function worm_audit_purge(timestamp with time zone)' in stack) > 0 THEN
+         IF TG_RELID = '${audit}'::regclass THEN
+            old_time := OLD.timestamp;
+         ELSIF TG_RELID = '${audit_detail}'::regclass THEN
+            SELECT timestamp INTO old_time FROM ${audit} WHERE id = OLD.audit_id;
+         ELSIF TG_RELID = 'public.worm_audit_chain'::regclass THEN
+            SELECT timestamp INTO old_time FROM ${audit} WHERE id = OLD.audit_id;
+         ELSIF TG_RELID = 'public.worm_audit_chain_detail'::regclass THEN
+            SELECT a.timestamp INTO old_time FROM public.worm_audit_chain c JOIN ${audit} a ON a.id = c.audit_id WHERE c.id = OLD.chain_id;
+         END IF;
+         IF old_time < now() - interval '${retention}' THEN
+            RETURN OLD;
+         END IF;
+      END IF;
+   END IF;
+   IF TG_LEVEL = 'ROW' THEN
+      -- Record fields resolve per statement, so each table's key is read in its own branch.
+      IF TG_RELID = 'public.worm_audit_chain_detail'::regclass THEN
+         row_identity := OLD.detail_id::text;
+      ELSE
+         row_identity := OLD.id::text;
+      END IF;
+   END IF;
+   RAISE EXCEPTION USING ERRCODE = 'P0001',
+      MESSAGE = format('worm_audit: audit rows are write-once; %s of "%s" is not allowed (id=%s). Expired rows are removed only by worm_audit_purge().',
+         TG_OP, TG_TABLE_NAME, row_identity),
+      HINT = 'see qbit-worm-audit docs/DESIGN.md section 4';
+END;
+$fn$;
+
+CREATE OR REPLACE FUNCTION public.worm_audit_detail_coverage() RETURNS trigger
+LANGUAGE plpgsql SET search_path = pg_catalog, public, pg_temp AS $fn$
+BEGIN
+   -- Serialize even direct SQL appends, then record coverage in the same statement as the link.
+   -- The primary key rejects a detail covered twice, within this link or by an earlier one. The
+   -- previous EXISTS overlap check ran as a cached generic plan that chose a sequential scan of
+   -- the whole chain for every inserted link.
+   PERFORM pg_advisory_xact_lock(86753090421);
+   IF NEW.detail_ids = '' THEN
+      RETURN NEW;
+   END IF;
+   BEGIN
+      INSERT INTO public.worm_audit_chain_detail(detail_id, chain_id)
+         SELECT d, NEW.id FROM unnest(string_to_array(NEW.detail_ids, ',')::bigint[]) AS d;
+   EXCEPTION WHEN unique_violation THEN
+      RAISE EXCEPTION USING ERRCODE = '23505', MESSAGE = 'worm_audit: audit detail is already covered by the chain';
+   END;
+   RETURN NEW;
+END;
+$fn$;
+
+-- The array index worm_audit_chain_detail_coverage is no longer used by this release. It stays until
+-- a later migration drops it, so a previous-release writer still running during a rolling deploy keeps its indexed probe.
+
+DROP TRIGGER IF EXISTS worm_audit_row_guard ON public.worm_audit_chain_detail;
+CREATE TRIGGER worm_audit_row_guard BEFORE UPDATE OR DELETE ON public.worm_audit_chain_detail
+   FOR EACH ROW EXECUTE FUNCTION public.worm_audit_reject();
+DROP TRIGGER IF EXISTS worm_audit_truncate_guard ON public.worm_audit_chain_detail;
+CREATE TRIGGER worm_audit_truncate_guard BEFORE TRUNCATE ON public.worm_audit_chain_detail
+   FOR EACH STATEMENT EXECUTE FUNCTION public.worm_audit_reject();
+REVOKE UPDATE, DELETE, TRUNCATE ON public.worm_audit_chain_detail FROM ${app_role};
+GRANT SELECT, INSERT ON public.worm_audit_chain_detail TO ${app_role};
+
+-- Same function as worm-audit:5, except that coverage rows are locked and purged before their links.
+-- The migrating role may have revoked its own EXECUTE (a runtime role that owns the schema, as on CNPG);
+-- PostgreSQL then refuses CREATE OR REPLACE. Grant it back for this statement only, then restore.
+DO $$
+BEGIN
+   IF NOT has_function_privilege(current_user, 'public.worm_audit_purge(timestamptz)', 'EXECUTE') THEN
+      EXECUTE format('GRANT EXECUTE ON FUNCTION public.worm_audit_purge(timestamptz) TO %I', current_user);
+      PERFORM set_config('worm_audit.purge_regrant', 'on', true);
+   END IF;
+END;
+$$;
+CREATE OR REPLACE FUNCTION public.worm_audit_purge(p_cutoff timestamptz) RETURNS void
+LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public, pg_temp SET timezone = 'UTC' AS $fn$
+DECLARE
+   boundary bigint;
+   smaller bigint;
+   anchor text;
+   audits_count bigint;
+   details_count bigint;
+   links_count bigint;
+BEGIN
+   IF p_cutoff IS NULL OR p_cutoff > now() - interval '${retention}' THEN
+      INSERT INTO public.worm_audit_purge(requested_at, requested_by, cutoff, retention_interval, outcome, reason)
+         VALUES(now(), session_user, p_cutoff, '${retention}', 'REFUSED', 'Cutoff is within retention or null');
+      RETURN;
+   END IF;
+   -- Same lock as the Java chainer; table locks also exclude concurrent audit inserts.
+   PERFORM pg_advisory_xact_lock(86753090421);
+   LOCK TABLE ${audit}, ${audit_detail}, public.worm_audit_chain, public.worm_audit_chain_detail IN SHARE ROW EXCLUSIVE MODE;
+   SELECT coalesce(min(c.id) - 1, (SELECT max(id) FROM public.worm_audit_chain)) INTO boundary
+      FROM public.worm_audit_chain c LEFT JOIN ${audit} a ON a.id = c.audit_id
+      WHERE a.id IS NULL OR a.timestamp IS NULL OR a.timestamp >= p_cutoff;
+   -- Shrink to a prefix closed over every audit's links AND all its stored details.
+   LOOP
+      SELECT min(c.id) - 1 INTO smaller FROM public.worm_audit_chain c
+         WHERE c.id <= boundary AND (
+            EXISTS (SELECT FROM public.worm_audit_chain later WHERE later.audit_id = c.audit_id AND later.id > boundary)
+            OR EXISTS (SELECT FROM ${audit_detail} d WHERE d.audit_id = c.audit_id AND NOT EXISTS (
+               SELECT FROM public.worm_audit_chain covered WHERE covered.audit_id = c.audit_id
+                  AND d.id::text = ANY(string_to_array(covered.detail_ids, ',')))));
+      EXIT WHEN smaller IS NULL;
+      boundary := smaller;
+   END LOOP;
+   SELECT link_hash INTO anchor FROM public.worm_audit_chain WHERE id = boundary;
+   IF anchor IS NULL THEN
+      INSERT INTO public.worm_audit_purge(requested_at, requested_by, cutoff, retention_interval, outcome, reason)
+         VALUES(now(), session_user, p_cutoff, '${retention}', 'REFUSED', 'No complete expired chain prefix');
+      RETURN;
+   END IF;
+   PERFORM set_config('worm_audit.purging', 'on', true);
+   DELETE FROM ${audit_detail} d WHERE EXISTS
+      (SELECT FROM public.worm_audit_chain c WHERE c.audit_id = d.audit_id AND c.id <= boundary);
+   GET DIAGNOSTICS details_count = ROW_COUNT;
+   -- Coverage rows go before their links: the guard finds their age through the link.
+   DELETE FROM public.worm_audit_chain_detail WHERE chain_id <= boundary;
+   -- Keep ids locally because links must be deleted before their audit headers.
+   WITH removed AS (DELETE FROM public.worm_audit_chain WHERE id <= boundary RETURNING audit_id),
+      removed_audits AS (DELETE FROM ${audit} WHERE id IN (SELECT audit_id FROM removed) RETURNING id)
+      SELECT (SELECT count(*) FROM removed), (SELECT count(*) FROM removed_audits) INTO links_count, audits_count;
+   PERFORM set_config('worm_audit.purging', 'off', true);
+   INSERT INTO public.worm_audit_purge(requested_at, requested_by, cutoff, retention_interval, outcome,
+      audits_deleted, details_deleted, links_deleted, anchor_link_id, anchor_hash)
+      VALUES(now(), session_user, p_cutoff, '${retention}', 'PURGED', audits_count, details_count, links_count, boundary, anchor);
+END;
+$fn$;
+DO $$
+BEGIN
+   IF current_setting('worm_audit.purge_regrant', true) = 'on' THEN
+      EXECUTE format('REVOKE EXECUTE ON FUNCTION public.worm_audit_purge(timestamptz) FROM %I', current_user);
+      PERFORM set_config('worm_audit.purge_regrant', 'off', true);
+   END IF;
+END;
+$$;

src/main/resources/help/qbit-worm-audit.yaml

@@ -0,0 +1,50 @@
+bundle: qbit-worm-audit
+version: 1
+layer: QBIT
+# Help for the WORM Audit app and every worm_audit_* table, in the QQQ help-bundle
+# format (qqq#1198). Each table entry says what the table is for and how its rows
+# get created, so an empty table explains itself. WormAuditHelp applies it until
+# the core HelpBundles registry ships in a QQQ release
+# (STAND-IN(core=QRun-IO/qqq#1198, removeBy=4.1.0-RC.7)). Summaries stay under 120
+# characters. Only the settings table is seeded (by the database script, see the
+# README); every other table is written by the qbit's jobs and starts empty.
+entries:
+  - key: app:worm_audit;slot:help
+    summary: "Tamper-evident audit: a hash chain over every audit record, signed checkpoints, and sealed exports."
+    content: |
+      Install the database script (the Setup warnings on the home list what is missing) and the one settings row is created with it. Everything else fills by itself once the first audit record has settled: the chain job links records every 30 seconds by default, checkpoints follow, the daily job verifies and exports. Use Verify audit chain to check at any time.
+
+  - key: table:worm_audit_settings;slot:help
+    summary: "The one settings row: retention, export lock, checkpoint and chain cadence, and the job schedules."
+    content: |
+      **How rows get created:** the database script inserts the one row (ID 1) with the host's configured values, only when it is absent; it can never be added to or deleted. Edit record 1 to change a value; changes are audited and take effect within 30 seconds, except the schedules and chain interval, which need a restart. If the row is missing the qbit runs on the host's configured values, so nothing stops, but run the script to make them visible and editable here. Audit retention here is the next purge date only; the purge interval in the database changes through a reviewed migration.
+
+  - key: table:worm_audit_chain;slot:help
+    summary: "One link per core audit record: a hash that includes the previous link, so any change breaks the chain."
+    content: |
+      **How rows get created:** the Chain settled audit records job appends links for audit records older than the settle horizon (every 30 seconds by default, set in Settings). Nobody adds, edits or deletes a link; the database refuses. An empty table means no audit record has settled yet, or the chain job is not running.
+
+  - key: table:worm_audit_chain_detail;slot:help
+    summary: "Which chain link covers each audit detail: one row per detail, written with the link."
+    content: |
+      **How rows get created:** whenever the chain job appends a link, the database writes one row here for every audit detail the link covers, in the same transaction (the database script also fills it for links that existed before this table). It lets the chain job find details that are not chained yet without scanning the chain. Nobody adds, edits or deletes a row; the database refuses, and verification checks that it matches the chain exactly. An empty table means no audit detail has been chained yet.
+
+  - key: table:worm_audit_checkpoint;slot:help
+    summary: "Signed ranges of links (Ed25519), proving the chain's head at a point in time."
+    content: |
+      **How rows get created:** the chain job writes one after the configured number of links or minutes (Settings), signing the range. Sealed exports and verification check against them. An empty table means the chain is too short for a first checkpoint yet.
+
+  - key: table:worm_audit_export;slot:help
+    summary: "Sealed exports of chain ranges to object-lock storage, with their retention and manifest."
+    content: |
+      **How rows get created:** the Daily sealed audit export job (schedule in Settings) and the Export now action seal the links since the last export into the configured bucket, then record it here. Verify sealed export re-reads one. An empty table means no export has run: check the bucket setup warning on the home.
+
+  - key: table:worm_audit_verification;slot:help
+    summary: "Every run of the chain verification: what was checked and whether it passed or broke."
+    content: |
+      **How rows get created:** the Verify audit chain action and the Daily audit chain verification job (schedule in Settings) each add a row, with the first break found, if any. A break also raises an alert. An empty table means verification has not run yet.
+
+  - key: table:worm_audit_purge;slot:help
+    summary: "Record of each retention purge: the cutoff, what was removed and the new anchor link."
+    content: |
+      **How rows get created:** only the database's owner role can purge, by calling the purge function after the retention date has passed; the function writes this row. The application can never delete audit rows. An empty table means nothing has been purged, which is normal until records pass retention.

src/test/java/com/kingsrook/qbits/wormaudit/CanonicalAndCryptoTest.java

@@ -167,6 +167,9 @@ class CanonicalAndCryptoTest
                   java.nio.file.Files.readString(
                         java.nio.file.Path.of("docs/ddl/worm-audit-postgres.sql")));
       assertThat(sql).doesNotContain("${");
+      // Liquibase parses every line starting with "-- changeset" as a directive, comments included.
+      assertThat(sql.lines().filter(line -> line.startsWith("-- changeset")))
+            .allMatch(line -> line.matches("-- changeset worm-audit:[0-9]+( splitStatements:false)?"));
       assertThat(sql).contains("GET DIAGNOSTICS stack = PG_CONTEXT", "function worm_audit_purge(timestamp with time zone)", "SET timezone = 'UTC'");
       // The documented migration contract deliberately uses physical snake_case columns.
       assertThat(sql).contains("OLD.audit_id", "OLD.timestamp", "NEW.name", "GRANT UPDATE (label, create_date, modify_date)");

src/test/java/com/kingsrook/qbits/wormaudit/MinioExportAcceptanceTest.java

@@ -78,7 +78,7 @@ import static org.assertj.core.api.Assertions.assertThatThrownBy;
 class MinioExportAcceptanceTest
 {
    /*******************************************************************************
-    ** Production adapter, COMPLIANCE retention, version preservation and UI findings.
+    ** Production adapter, GOVERNANCE and COMPLIANCE retention, version preservation and UI findings.
     *******************************************************************************/
    @Test
    void lockedRoundTripVersionRetentionLiveTamperAndUnsupportedBucket() throws Exception
@@ -130,17 +130,17 @@ class MinioExportAcceptanceTest
             {
                String key = row.getValueString("object_prefix") + name;
                var head = storage.head(key);
-               assertThat(head.getLockMode()).isEqualTo("COMPLIANCE");
-               assertThat(Duration.between(now.atZone(java.time.ZoneOffset.UTC).plusYears(6).toInstant(), head.getLockRetainUntil()).abs()).isLessThan(Duration.ofSeconds(2));
+               assertThat(head.getLockMode()).isEqualTo("GOVERNANCE");
+               assertThat(Duration.between(now.plus(Duration.ofDays(30)), head.getLockRetainUntil()).abs()).isLessThan(Duration.ofSeconds(2));
                String originalVersion = head.getVersionId();
                assertThat(originalVersion).isNotBlank();
                assertThat(ExportFiles.parse(row.getValueString("object_versions").getBytes(java.nio.charset.StandardCharsets.UTF_8)))
                   .containsEntry(name, originalVersion);
-               assertThatThrownBy(() -> s3.deleteObject(b -> b.bucket("worm-locked").key(key).versionId(originalVersion)))
+               assertThatThrownBy(() -> s3.deleteObject(b -> b.bucket("worm-locked").key(key).versionId(originalVersion).bypassGovernanceRetention(false)))
                   .isInstanceOf(S3Exception.class);
                byte[] original = s3.getObjectAsBytes(b -> b.bucket("worm-locked").key(key).versionId(originalVersion)).asByteArray();
                // S3 overwrites create new versions; they cannot replace the locked version.
-               var replacement = s3.putObject(b -> b.bucket("worm-locked").key(key).objectLockMode("COMPLIANCE")
+               var replacement = s3.putObject(b -> b.bucket("worm-locked").key(key).objectLockMode("GOVERNANCE")
                   .objectLockRetainUntilDate(row.getValueInstant("retain_until")), RequestBody.fromString("different replacement bytes"));
                assertThat(replacement.versionId()).isNotEqualTo(originalVersion);
                assertThat(s3.getObjectAsBytes(b -> b.bucket("worm-locked").key(key).versionId(originalVersion)).asByteArray()).isEqualTo(original);
@@ -163,11 +163,26 @@ class MinioExportAcceptanceTest
             tamper("auditDetail", 1, "newValue", "new");
             audit(2);
             new WormAuditChainer(config).chainOnce();
+            config.withExportLockMode("COMPLIANCE");
+            var compliance = new SealedExporter(config).run("USER", Instant.now());
+            assertThat(compliance.failed()).isFalse();
+            assertThat(compliance.row().getValueString("lock_mode")).isEqualTo("COMPLIANCE");
+            assertThat(verifier.verify(compliance.row(), "USER").result().passed()).isTrue();
+            for(String name : ExportFiles.NAMES)
+            {
+               String key = compliance.row().getValueString("object_prefix") + name;
+               var head = storage.head(key);
+               assertThat(head.getLockMode()).isEqualTo("COMPLIANCE");
+               assertThatThrownBy(() -> s3.deleteObject(b -> b.bucket("worm-locked").key(key).versionId(head.getVersionId())))
+                  .isInstanceOf(S3Exception.class);
+            }
+            audit(3);
+            new WormAuditChainer(config).chainOnce();
             config.withExportBucketAlias("plain");
             var failed = new SealedExporter(config).run("SCHEDULE", Instant.now());
             assertThat(failed.failed()).isTrue();
             assertThat(failed.message()).contains("does not support Object Lock");
-            assertThat(STORE.all(WormAuditTables.EXPORT)).hasSize(1);
+            assertThat(STORE.all(WormAuditTables.EXPORT)).hasSize(2);
             assertThat(ProcessesAndAlertsTest.Sink.raised).hasSize(1);
          }
          finally

src/test/java/com/kingsrook/qbits/wormaudit/PostgresAcceptanceTest.java

@@ -113,7 +113,6 @@ class PostgresAcceptanceTest
    void database() throws Exception
    {
       config = ChainVerificationTest.signed().withSettleHorizonSeconds(0);
-      instance = RdbmsTestSupport.instance(POSTGRES.getJdbcUrl(), "app", "fixture", config, true);
       try(var c = owner())
       {
          sql(
@@ -124,6 +123,7 @@ class PostgresAcceptanceTest
                c,
                "DO $$ BEGIN IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname='app') THEN"
                   + " CREATE ROLE app LOGIN PASSWORD 'fixture'; END IF; END $$");
+         instance = RdbmsTestSupport.instance(POSTGRES.getJdbcUrl(), "app", "fixture", config, true);
          RdbmsTestSupport.createTables(c, instance, true);
          String ddl = WormAuditDdl.postgres(config);
          sql(c, ddl);
@@ -142,9 +142,17 @@ class PostgresAcceptanceTest
    {
       var storage = new com.kingsrook.qbits.wormaudit.demo.MemorySealedStore();
       var clean = com.kingsrook.qbits.wormaudit.app.WormAuditHomeData.load(config, storage, java.time.Instant.now());
-      assertThat(clean.warnings()).containsExactly("Daily verification has never run");
+      assertThat(clean.warnings()).contains("Daily verification has never run").noneMatch(w -> w.contains("guard") && w.contains("settings"));
       try(var connection = owner())
       {
+         sql(connection, "UPDATE worm_audit_settings SET audit_retention_years=7 WHERE id=1");
+         config.invalidateSettings();
+         assertThat(com.kingsrook.qbits.wormaudit.app.WormAuditHomeData.load(config, storage, java.time.Instant.now()).warnings())
+            .anyMatch(w -> w.contains("purge function's compiled interval (6 years)"));
+         sql(connection, WormAuditDdl.postgres(config));
+         assertThat(count(connection, "SELECT audit_retention_years FROM worm_audit_settings WHERE id=1")).isEqualTo(7);
+         sql(connection, "UPDATE worm_audit_settings SET audit_retention_years=6 WHERE id=1");
+         config.invalidateSettings();
          sql(connection, "DROP TRIGGER worm_audit_row_guard ON audit_detail");
          sql(connection, "ALTER TABLE worm_audit_chain DISABLE TRIGGER worm_audit_truncate_guard");
          sql(connection, "DROP FUNCTION public.worm_audit_purge(timestamptz)");
@@ -221,11 +229,21 @@ class PostgresAcceptanceTest
                               assertThat(((SQLException) e).getSQLState())
                                     .isEqualTo("42501"));
             sql(c, "GRANT UPDATE, DELETE, TRUNCATE ON " + table + " TO app");
-            for(String operation :
-                  List.of(
-                        "UPDATE " + table + " SET id=id",
-                        "DELETE FROM " + table,
-                        "TRUNCATE " + table))
+            // A table a foreign key references (audit, checkpoint, export) refuses TRUNCATE before
+            // its guard runs; TRUNCATE ... CASCADE (owner) reaches the guards, which refuse it.
+            if(count(c, "SELECT count(*) FROM pg_constraint WHERE contype='f' AND confrelid='" + table + "'::regclass") > 0)
+            {
+               assertThatThrownBy(() -> sql(runtime, "TRUNCATE " + table))
+                  .isInstanceOf(SQLException.class)
+                  .hasMessageContaining("referenced in a foreign key constraint");
+               assertThatThrownBy(() -> sql(c, "TRUNCATE " + table + " CASCADE"))
+                  .isInstanceOf(SQLException.class)
+                  .hasMessageContaining("worm_audit:");
+            }
+            boolean referenced = count(c, "SELECT count(*) FROM pg_constraint WHERE contype='f' AND confrelid='" + table + "'::regclass") > 0;
+            for(String operation : referenced
+               ? List.of("UPDATE " + table + " SET id=id", "DELETE FROM " + table)
+               : List.of("UPDATE " + table + " SET id=id", "DELETE FROM " + table, "TRUNCATE " + table))
             {
                assertThatThrownBy(() -> sql(runtime, operation))
                         .isInstanceOf(SQLException.class)
@@ -310,6 +328,11 @@ class PostgresAcceptanceTest
       assertThat(purge.getValueLong("details_deleted")).isEqualTo(2);
       assertThat(purge.getValueLong("links_deleted")).isEqualTo(2);
       assertThat(purge.getValueString("anchor_hash")).isEqualTo(anchor);
+      try(var c = owner())
+      {
+         assertThat(count(c, "SELECT count(*) FROM worm_audit_chain_detail WHERE chain_id <= 2")).as("coverage purged with its links").isZero();
+         assertThat(count(c, "SELECT count(*) FROM worm_audit_chain_detail")).isZero();
+      }
       assertThat(new WormAuditVerifier(config).verify().getBreaks()).isEmpty();
       detail(3, 3);
       new WormAuditChainer(config).chainOnce();
@@ -338,7 +361,7 @@ class PostgresAcceptanceTest
    void prefixCannotSplitAnAuditAndAdvisoryLockSkipsTick() throws Exception
    {
       // This retention fixture deliberately polls a detail for a six-year-old header.
-      config.withLateDetailLookbackHours(24 * 365 * 10);
+      setting("late_detail_lookback_hours", 24 * 365 * 10);
       audit(1);
       new WormAuditChainer(config).chainOnce();
       try(var c = owner())
@@ -512,7 +535,7 @@ class PostgresAcceptanceTest
    void chainerUsesUtcCutoffInChicago() throws Exception
    {
       var original = java.util.TimeZone.getDefault();
-      config.withSettleHorizonSeconds(60);
+      setting("settle_horizon_seconds", 60);
       try(var connection = owner())
       {
          sql(connection, "ALTER TABLE audit ALTER COLUMN timestamp TYPE timestamp without time zone USING timestamp AT TIME ZONE 'UTC'");
@@ -543,7 +566,7 @@ class PostgresAcceptanceTest
    @Test
    void postgresLateDetailWindowAndCompleteness() throws Exception
    {
-      config.withSettleHorizonSeconds(60);
+      setting("settle_horizon_seconds", 60);
       var now = java.time.Instant.now().truncatedTo(java.time.temporal.ChronoUnit.SECONDS);
       var cutoff = now.minusSeconds(60);
       try(var connection = owner(); var statement = connection.prepareStatement(
@@ -574,4 +597,166 @@ class PostgresAcceptanceTest
       assertThat(new WormAuditVerifier(config).verify().getBreaks()).anyMatch(b -> b.reason().contains("audit_detail 1 is not covered"));
    }
 
+
+   /*******************************************************************************
+    ** The chain trigger writes one coverage row per detail with its link; the
+    ** table is write-once, backfilled from the chain by the DDL, purged with its
+    ** links, and verification breaks if it ever disagrees with the chain.
+    *******************************************************************************/
+   @Test
+   void detailCoverageTableIsWrittenByTheTriggerBackfilledAndWriteOnce() throws Exception
+   {
+      // The fixture's audit headers are years old; poll them as late-detail candidates.
+      setting("late_detail_lookback_hours", 24 * 365 * 10);
+      audit(1);
+      detail(1, 1);
+      detail(2, 1);
+      audit(2);
+      detail(3, 2);
+      var writer = new WormAuditChainer(config);
+      assertThat(writer.chainOnce().links()).isEqualTo(2);
+      detail(4, 1);
+      assertThat(writer.chainOnce().lateDetails()).isEqualTo(1);
+      assertThat(writer.chainOnce().links()).isZero();
+      String pairs = "SELECT string_agg(detail_id || '@' || chain_id, ',' ORDER BY detail_id) FROM worm_audit_chain_detail";
+      try(var c = owner(); var runtime = app(); var s = c.createStatement())
+      {
+         try(var r = s.executeQuery(pairs))
+         {
+            r.next();
+            assertThat(r.getString(1)).isEqualTo("1@1,2@1,3@2,4@3");
+         }
+         assertThatThrownBy(() -> sql(runtime, "INSERT INTO worm_audit_chain_detail(detail_id,chain_id) VALUES(4,1)"))
+            .isInstanceOf(SQLException.class).satisfies(e -> assertThat(((SQLException) e).getSQLState()).isEqualTo("23505"));
+         for(String privilege : List.of("UPDATE", "DELETE", "TRUNCATE"))
+         {
+            assertThat(count(c, "SELECT has_table_privilege('app','worm_audit_chain_detail','" + privilege + "')::int")).isZero();
+         }
+         sql(c, "GRANT UPDATE, DELETE, TRUNCATE ON worm_audit_chain_detail TO app");
+         for(String operation : List.of("UPDATE worm_audit_chain_detail SET chain_id=chain_id", "DELETE FROM worm_audit_chain_detail", "TRUNCATE worm_audit_chain_detail"))
+         {
+            assertThatThrownBy(() -> sql(runtime, operation)).isInstanceOf(SQLException.class).hasMessageContaining("worm_audit:")
+               .satisfies(e -> assertThat(((SQLException) e).getSQLState()).isEqualTo("P0001"));
+         }
+
+         ///////////////////////////////////////////////////////////////////////
+         // An install that predates the table: the DDL backfills it from the  //
+         // chain, and the writer then finds nothing left to chain.            //
+         ///////////////////////////////////////////////////////////////////////
+         sql(c, "DROP TABLE worm_audit_chain_detail");
+         sql(c, WormAuditDdl.postgres(config));
+         try(var r = s.executeQuery(pairs))
+         {
+            r.next();
+            assertThat(r.getString(1)).isEqualTo("1@1,2@1,3@2,4@3");
+         }
+         assertThat(writer.chainOnce().links()).isZero();
+         assertThat(new WormAuditVerifier(config).verify().getBreaks()).isEmpty();
+
+         sql(c, "ALTER TABLE worm_audit_chain_detail DISABLE TRIGGER ALL; UPDATE worm_audit_chain_detail SET chain_id=1 WHERE detail_id=3;"
+            + " ALTER TABLE worm_audit_chain_detail ENABLE TRIGGER ALL");
+      }
+      assertThat(new WormAuditVerifier(config).verify().getBreaks()).anyMatch(b -> b.reason().contains("audit_detail 3 has a detail coverage row that disagrees"));
+   }
+
+
+
+   /*******************************************************************************
+    ** Referential integrity guardrail: every table reference of the WORM tables
+    ** (possible-value fields, and the chain's audit header) has a RESTRICT
+    ** foreign key, or an exemption with its reason. Nothing may cascade into or
+    ** out of audit evidence.
+    *******************************************************************************/
+   @Test
+   void everyReferenceHasARestrictForeignKeyOrAReason() throws Exception
+   {
+      String links = "the retention purge deletes the sealed prefix of chain links; this evidence row outlives the links it names";
+      java.util.Map<String, String> exempt = java.util.Map.of(
+         "worm_audit_checkpoint.from_link_id", links,
+         "worm_audit_checkpoint.to_link_id", links,
+         "worm_audit_export.from_link_id", links,
+         "worm_audit_export.to_link_id", links,
+         "worm_audit_verification.from_link_id", links,
+         "worm_audit_verification.to_link_id", links,
+         "worm_audit_verification.first_break_link_id", links + "; a break may name a link that is missing",
+         "worm_audit_purge.anchor_link_id", "the anchor is the last link the same purge deleted",
+         "worm_audit_chain_detail.chain_id", "the chain is WORM: the owner has no UPDATE, so a foreign key check into it is denied;"
+            + " the coverage trigger writes the row with its link and the purge deletes it first");
+
+      java.util.Set<String> references = new java.util.TreeSet<>(List.of("worm_audit_chain.audit_id"));
+      for(var table : instance.getTables().values())
+      {
+         if(!table.getName().startsWith("worm_audit_"))
+         {
+            continue;
+         }
+         for(var field : table.getFields().values())
+         {
+            if(field.getPossibleValueSourceName() != null && instance.getTable(field.getPossibleValueSourceName()) != null)
+            {
+               references.add(table.getName() + "." + field.getBackendName());
+            }
+         }
+      }
+
+      java.util.Map<String, String> foreignKeys = new java.util.HashMap<>();
+      try(var c = owner(); var s = c.createStatement(); var r = s.executeQuery("""
+         SELECT c.conrelid::regclass::text || '.' || a.attname, c.confdeltype::text || c.convalidated::text
+         FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
+         WHERE c.contype = 'f' AND c.conrelid::regclass::text LIKE 'worm_audit_%'"""))
+      {
+         while(r.next())
+         {
+            foreignKeys.put(r.getString(1), r.getString(2));
+         }
+      }
+      assertThat(references.stream().filter(ref -> !foreignKeys.containsKey(ref) && !exempt.containsKey(ref)).toList())
+         .as("references without a foreign key (add one to worm-audit.sql, or an exemption with a reason)").isEmpty();
+      assertThat(foreignKeys).as("RESTRICT and validated").containsOnlyKeys("worm_audit_chain.audit_id", "worm_audit_export.from_checkpoint_id",
+         "worm_audit_export.to_checkpoint_id", "worm_audit_verification.export_id").allSatisfy((k, v) -> assertThat(v).isEqualTo("rtrue"));
+   }
+
+
+
+   /*******************************************************************************
+    ** Upgrading a schema whose chain names a missing audit header: the link is
+    ** evidence, so it is copied to qqq_fk_quarantine (KEPT), never changed, and
+    ** the constraint is added NOT VALID (new links are still checked). A re-run
+    ** changes nothing.
+    *******************************************************************************/
+   @Test
+   void orphanEvidenceIsKeptAndTheConstraintIsNotValidated() throws Exception
+   {
+      audit(1);
+      new WormAuditChainer(config).chainOnce();
+      try(var c = owner())
+      {
+         sql(c, "ALTER TABLE worm_audit_chain DROP CONSTRAINT fk_worm_audit_chain_audit_id");
+         sql(c, "ALTER TABLE audit DISABLE TRIGGER worm_audit_row_guard; DELETE FROM audit WHERE id = 1; ALTER TABLE audit ENABLE TRIGGER worm_audit_row_guard");
+         sql(c, WormAuditDdl.postgres(config));
+         sql(c, WormAuditDdl.postgres(config));
+         assertThat(count(c, "SELECT count(*) FROM qqq_fk_quarantine WHERE table_name = 'worm_audit_chain' AND column_name = 'audit_id'"
+            + " AND row_id = '1' AND missing_parent_id = '1' AND action = 'KEPT' AND row_data LIKE '%\"link_hash\"%'")).isEqualTo(1);
+         assertThat(count(c, "SELECT count(*) FROM qqq_fk_quarantine")).isEqualTo(1);
+         assertThat(count(c, "SELECT count(*) FROM worm_audit_chain WHERE id = 1 AND audit_id = 1")).isEqualTo(1);
+         assertThat(count(c, "SELECT count(*) FROM pg_constraint WHERE conname = 'fk_worm_audit_chain_audit_id' AND NOT convalidated")).isEqualTo(1);
+         assertThatThrownBy(() -> sql(c, "INSERT INTO worm_audit_chain(id,kind,audit_id,detail_ids,prev_hash,link_hash,chained_at)"
+            + " VALUES(99,'AUDIT',404,'',repeat('0',64),repeat('0',64),now())"))
+            .hasMessageContaining("fk_worm_audit_chain_audit_id");
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** The settings row (seeded by the DDL) overrides config: change it there.
+    *******************************************************************************/
+   private void setting(String column, int value) throws Exception
+   {
+      try(var connection = owner())
+      {
+         sql(connection, "UPDATE worm_audit_settings SET " + column + "=" + value + " WHERE id=1");
+      }
+      config.invalidateSettings();
+   }
 }

src/test/java/com/kingsrook/qbits/wormaudit/ProcessesAndAlertsTest.java

@@ -142,7 +142,8 @@ class ProcessesAndAlertsTest
       assertThat(Sink.raised.getFirst().getDedupKey()).isEqualTo("chain");
       assertThat(Sink.raised.getFirst().getRecordRefs().getFirst().getTableName())
             .isEqualTo(WormAuditTables.VERIFICATION);
-      assertThat(Sink.raised.getFirst().getTitle()).contains("Break at link 1");
+      assertThat(Sink.raised.getFirst().getTitle()).isEqualTo("Audit chain verification found 1 problem");
+      assertThat(Sink.raised.getFirst().getSummary()).contains("Break at link 1");
       var first = STORE.all(WormAuditTables.VERIFICATION).getFirst();
       assertThat(first.getValueLong("first_break_link_id")).isEqualTo(1);
       assertThat(first.getValueLong("first_break_audit_id")).isEqualTo(1);

src/test/java/com/kingsrook/qbits/wormaudit/RdbmsChainTest.java

@@ -23,6 +23,7 @@ package com.kingsrook.qbits.wormaudit;
 import java.sql.DriverManager;
 import java.util.Map;
 import java.util.UUID;
+import com.kingsrook.qbits.wormaudit.chain.SweepFloors;
 import com.kingsrook.qbits.wormaudit.chain.WormAuditChainer;
 import com.kingsrook.qbits.wormaudit.model.WormAuditTables;
 import com.kingsrook.qbits.wormaudit.verify.WormAuditVerifier;
@@ -209,4 +210,55 @@ class RdbmsChainTest
       }
    }
 
+
+   /*******************************************************************************
+    ** Without a coverage trigger the writer records coverage itself, and the
+    ** sweep floors advance only past settled ids: a later header and a late
+    ** detail for an old, already chained header are both still chained.
+    *******************************************************************************/
+   @Test
+   void coverageRowsAndSweepFloors() throws Exception
+   {
+      String url = "jdbc:h2:mem:floors_" + UUID.randomUUID() + ";DB_CLOSE_DELAY=-1;DATABASE_TO_LOWER=TRUE";
+      var config = new WormAuditQBitConfig().withBatchSize(500);
+      var instance = RdbmsTestSupport.instance(url, "sa", "", config, false);
+      try(var connection = DriverManager.getConnection(url, "sa", ""); var statement = connection.createStatement())
+      {
+         RdbmsTestSupport.createTables(connection, instance, false);
+         QContext.init(instance, new QSession());
+         lookups();
+         statement.execute("INSERT INTO audit(id,audit_table_id,audit_user_id,timestamp) VALUES(1,1,1,CURRENT_TIMESTAMP - INTERVAL '2' MINUTE)");
+         detail(10, 1);
+         detail(20, 1);
+         var writer = new WormAuditChainer(config);
+         var floors = SweepFloors.of(config);
+         var now = java.time.Instant.now();
+         assertThat(writer.chainOnce(now).audits()).isEqualTo(1);
+         assertThat(STORE.query(WormAuditTables.CHAIN_DETAIL, new com.kingsrook.qqq.backend.core.model.actions.tables.query.QQueryFilter())).extracting(r -> r.getValueLong("detail_id") + "@" + r.getValueLong("chain_id"))
+            .containsExactlyInAnyOrder("10@1", "20@1");
+         assertThat(floors.audit()).isZero();
+         assertThat(writer.chainOnce(now.plusSeconds(600)).links()).isZero();
+         assertThat(floors.audit()).isEqualTo(1);
+         assertThat(floors.detail()).isEqualTo(20);
+         detail(30, 1);
+         statement.execute("INSERT INTO audit(id,audit_table_id,audit_user_id,timestamp) VALUES(2,1,1,CURRENT_TIMESTAMP - INTERVAL '2' MINUTE)");
+         detail(40, 2);
+         var counts = writer.chainOnce(now.plusSeconds(1200));
+         assertThat(counts.audits()).isEqualTo(1);
+         assertThat(counts.lateDetails()).isEqualTo(1);
+         assertThat(STORE.query(WormAuditTables.CHAIN_DETAIL, new com.kingsrook.qqq.backend.core.model.actions.tables.query.QQueryFilter())).hasSize(4);
+         assertThat(writer.chainOnce(now.plusSeconds(1800)).links()).isZero();
+         assertThat(floors.audit()).isEqualTo(2);
+         assertThat(floors.detail()).isEqualTo(40);
+         // A detail whose id sits below the floor (its transaction committed very late)
+         // is skipped by floored ticks and chained by the next hourly full sweep.
+         statement.execute("INSERT INTO audit_detail(id,audit_id,field_name,new_value) VALUES(5,2,'title','late')");
+         assertThat(writer.chainOnce(now.plusSeconds(2400)).lateDetails()).isZero();
+         assertThat(writer.chainOnce(now.plus(SweepFloors.FULL_SWEEP_EVERY)).lateDetails()).isEqualTo(1);
+         assertThat(new WormAuditVerifier(config).verify().getBreaks()).isEmpty();
+         statement.execute("DELETE FROM worm_audit_chain_detail WHERE detail_id=30");
+         statement.execute("INSERT INTO worm_audit_chain_detail(detail_id,chain_id) VALUES(30,1)");
+         assertThat(new WormAuditVerifier(config).verify().getBreaks()).anyMatch(b -> b.reason().contains("audit_detail 30 has a detail coverage row that disagrees"));
+      }
+   }
 }

src/test/java/com/kingsrook/qbits/wormaudit/RdbmsTestSupport.java

@@ -127,10 +127,10 @@ class RdbmsTestSupport
                      case DATE_TIME -> "timestamp(6) with time zone";
                      default -> "text";
                   };
-            if(field.getName().equals("id"))
+            if(field.getName().equals(table.getPrimaryKeyField()))
             {
                type +=
-                        table.getName().equals("worm_audit_chain")
+                        table.getName().equals("worm_audit_chain") || table.getName().equals("worm_audit_chain_detail")
                               ? " PRIMARY KEY"
                               : " GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY";
             }

src/test/java/com/kingsrook/qbits/wormaudit/RecordIntegrationTest.java

@@ -53,9 +53,9 @@ class RecordIntegrationTest
    {
       var instance = init(new WormAuditQBitConfig());
       Map<String, String> formats = Map.of(
-         WormAuditTables.CHAIN, "Link #%s", WormAuditTables.CHECKPOINT, "Checkpoint #%s",
+         WormAuditTables.CHAIN, "Link #%s", WormAuditTables.CHAIN_DETAIL, "Detail #%s (link %s)", WormAuditTables.CHECKPOINT, "Checkpoint #%s",
          WormAuditTables.EXPORT, "Export #%s (links %s–%s)", WormAuditTables.VERIFICATION, "Verification #%s (%s)",
-         WormAuditTables.PURGE, "Purge #%s (%s)");
+         WormAuditTables.PURGE, "Purge #%s (%s)", WormAuditTables.SETTINGS, "WORM Audit Settings");
       for(String name : WormAuditTables.ALL)
       {
          var table = instance.getTable(name);

src/test/java/com/kingsrook/qbits/wormaudit/SealedExportTest.java

@@ -84,7 +84,7 @@ class SealedExportTest
       boolean omitVersion;
       boolean racePut;
       boolean supported = true;
-      String echoMode = "COMPLIANCE";
+      String echoMode;
       long shortenSeconds;
       int failPut;
 
@@ -95,7 +95,7 @@ class SealedExportTest
       }
 
       @Override
-      public String put(String key, Path file, Instant until) throws Exception
+      public String put(String key, Path file, com.kingsrook.qbits.objectstorage.api.LockMode mode, Instant until) throws Exception
       {
          if(failPut > 0 && objects.size() + 1 == failPut)
          {
@@ -108,7 +108,7 @@ class SealedExportTest
          String version = omitVersion ? missingVersion : "v" + ++nextVersion;
          objects.put(key, Files.readAllBytes(file));
          heads.put(key, new StoredObject().withKey(key).withVersionId(racePut ? "replacement" : version)
-            .withLockMode(echoMode).withLockRetainUntil(until.minusSeconds(shortenSeconds)));
+            .withLockMode(echoMode == null ? mode.name() : echoMode).withLockRetainUntil(until.minusSeconds(shortenSeconds)));
          return (version);
       }
 
@@ -197,7 +197,7 @@ class SealedExportTest
       assertThat(row.getValues()).containsKeys("from_checkpoint_id", "to_checkpoint_id", "bucket_alias", "manifest_key", "manifest_sha256",
          "prev_manifest_sha256", "object_count", "byte_count", "lock_mode", "retain_until", "sealed_at");
       assertThat(row.getValueLong("byte_count")).isEqualTo(storage.objects.values().stream().mapToLong(b -> b.length).sum());
-      assertThat(row.getValueInstant("retain_until")).isEqualTo(Instant.parse("2032-10-14T02:00:00Z"));
+      assertThat(row.getValueInstant("retain_until")).isEqualTo(NOW.plusSeconds(30 * 86400L));
       assertThat(verify(row).result().passed()).isTrue();
       QRecord verification = STORE.last(WormAuditTables.VERIFICATION);
       assertThat(verification.getValueString("kind")).isEqualTo("SEALED_EXPORT");
@@ -224,7 +224,7 @@ class SealedExportTest
       {
          String linksKey = row.getValueString("object_prefix") + "links.jsonl";
          Files.write(files.file("links.jsonl"), storage.objects.get(linksKey));
-         storage.put(linksKey, files.file("links.jsonl"), row.getValueInstant("retain_until"));
+         storage.put(linksKey, files.file("links.jsonl"), com.kingsrook.qbits.objectstorage.api.LockMode.valueOf(row.getValueString("lock_mode")), row.getValueInstant("retain_until"));
          var identical = verify(row);
          assertThat(identical.result().passed()).isFalse();
          assertThat(identical.result().getLinksChecked()).isEqualTo(1);
@@ -234,7 +234,7 @@ class SealedExportTest
          {
             String key = row.getValueString("object_prefix") + name;
             Files.writeString(files.file(name), "different bytes");
-            String current = storage.put(key, files.file(name), row.getValueInstant("retain_until"));
+            String current = storage.put(key, files.file(name), com.kingsrook.qbits.objectstorage.api.LockMode.valueOf(row.getValueString("lock_mode")), row.getValueInstant("retain_until"));
             var report = verify(row);
             assertThat(report.result().passed()).isFalse();
             assertThat(report.result().getLinksChecked()).isEqualTo(1);
@@ -330,9 +330,9 @@ class SealedExportTest
       assertThat(new SealedExporter(config, storage).run("SCHEDULE", NOW).message()).contains("does not support Object Lock");
       assertThat(storage.objects).isEmpty();
       storage.supported = true;
-      storage.echoMode = "GOVERNANCE";
-      assertThat(new SealedExporter(config, storage).run("USER", NOW).message()).contains("did not echo COMPLIANCE");
       storage.echoMode = "COMPLIANCE";
+      assertThat(new SealedExporter(config, storage).run("USER", NOW).message()).contains("did not echo GOVERNANCE");
+      storage.echoMode = null;
       storage.shortenSeconds = 1;
       assertThat(new SealedExporter(config, storage).run("USER", NOW).failed()).isTrue();
       storage.shortenSeconds = 0;
@@ -358,9 +358,9 @@ class SealedExportTest
       storage.objects.put(key, "tampered".getBytes(StandardCharsets.UTF_8));
       assertThat(findings(first)).contains("object links.jsonl hash does not match the manifest");
       storage.objects.put(key, original);
-      storage.heads.get(key).withLockMode("GOVERNANCE");
-      assertThat(findings(first)).contains("lacks the recorded COMPLIANCE retention");
       storage.heads.get(key).withLockMode("COMPLIANCE");
+      assertThat(findings(first)).contains("lacks the recorded GOVERNANCE retention");
+      storage.heads.get(key).withLockMode(first.getValueString("lock_mode"));
       audit(2);
       QRecord second = seal();
       tamper(WormAuditTables.EXPORT, first.getValueLong("id"), "manifest_sha256", "f".repeat(64));

src/test/java/com/kingsrook/qbits/wormaudit/TokenRequiredAuthenticationModule.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.wormaudit;
+
+
+import java.util.Map;
+import java.util.Set;
+import java.util.concurrent.ConcurrentHashMap;
+import com.kingsrook.qqq.backend.core.exceptions.QAuthenticationException;
+import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
+import com.kingsrook.qqq.backend.core.model.session.QSession;
+import com.kingsrook.qqq.backend.core.model.session.QSystemUserSession;
+import com.kingsrook.qqq.backend.core.modules.authentication.QAuthenticationModuleInterface;
+
+
+/*******************************************************************************
+ ** Test authentication module that behaves like the token-based core modules
+ ** (OAuth2, Auth0): a QSystemUserSession is always valid, a session it created
+ ** is valid, and any other session has no token and fails validation with the
+ ** same message the OAuth2 module produces (JWTDecodeException: The token is
+ ** null).
+ *******************************************************************************/
+public class TokenRequiredAuthenticationModule implements QAuthenticationModuleInterface
+{
+   private static final Set<String> ISSUED = ConcurrentHashMap.newKeySet();
+
+
+
+   /*******************************************************************************
+    ** Issue a session that carries a token.
+    *******************************************************************************/
+   @Override
+   public QSession createSession(QInstance qInstance, Map<String, String> context) throws QAuthenticationException
+   {
+      QSession session = new QSession();
+      ISSUED.add(session.getUuid());
+      return (session);
+   }
+
+
+
+   /*******************************************************************************
+    ** Accept the system user and issued sessions; reject tokenless sessions.
+    *******************************************************************************/
+   @Override
+   public boolean isSessionValid(QInstance instance, QSession session)
+   {
+      if(session instanceof QSystemUserSession || ISSUED.contains(session.getUuid()))
+      {
+         return (true);
+      }
+      throw (new IllegalStateException("The token is null"));
+   }
+}

src/test/java/com/kingsrook/qbits/wormaudit/TokenRequiredHostTest.java

@@ -0,0 +1,153 @@
+/*
+ * 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.wormaudit;
+
+
+import java.util.List;
+import com.kingsrook.qbits.wormaudit.chain.AuditStore;
+import com.kingsrook.qbits.wormaudit.model.WormAuditTables;
+import com.kingsrook.qbits.wormaudit.processes.ExportProcesses;
+import com.kingsrook.qbits.wormaudit.processes.WormAuditProcesses;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+import com.kingsrook.qqq.backend.core.model.metadata.QAuthenticationType;
+import com.kingsrook.qqq.backend.core.model.metadata.QBackendMetaData;
+import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
+import com.kingsrook.qqq.backend.core.model.metadata.authentication.QAuthenticationMetaData;
+import com.kingsrook.qqq.backend.core.model.metadata.code.QCodeReference;
+import com.kingsrook.qqq.backend.core.model.session.QSession;
+import com.kingsrook.qqq.backend.core.modules.backend.implementations.memory.MemoryBackendModule;
+import com.kingsrook.qqq.backend.core.modules.backend.implementations.memory.MemoryRecordStore;
+import org.junit.jupiter.api.AfterEach;
+import org.junit.jupiter.api.Test;
+import static org.assertj.core.api.Assertions.assertThat;
+import static org.assertj.core.api.Assertions.assertThatThrownBy;
+
+
+/*******************************************************************************
+ ** Regression: production must work on a host whose default authentication
+ ** rejects tokenless sessions (kof22 uses OAuth2). produce() reads the settings
+ ** singleton for the startup schedules; under a plain new QSession() that read
+ ** failed with "The token is null" and the host could not start.
+ *******************************************************************************/
+class TokenRequiredHostTest
+{
+   /*******************************************************************************
+    ** Clear the context and the memory store.
+    *******************************************************************************/
+   @AfterEach
+   void tearDown()
+   {
+      MemoryRecordStore.fullReset();
+      QContext.clear();
+   }
+
+
+
+   /*******************************************************************************
+    ** The test module really rejects a tokenless session, so the tests below
+    ** prove the qbit does not use one.
+    *******************************************************************************/
+   @Test
+   void moduleRejectsTokenlessSession() throws Exception
+   {
+      MemoryRecordStore.fullReset();
+      QInstance instance = tokenRequiredHost(new WormAuditQBitConfig());
+      QContext.init(instance, new QSession());
+      assertThatThrownBy(() -> new AuditStore(null).get(WormAuditTables.SETTINGS, 1))
+         .hasStackTraceContaining("The token is null");
+   }
+
+
+
+   /*******************************************************************************
+    ** produce() succeeds and still honors the persisted settings row for the
+    ** startup schedules, read as the system user.
+    *******************************************************************************/
+   @Test
+   void produceReadsPersistedSettingsAsSystemUser() throws Exception
+   {
+      ////////////////////////////////////////////////////////////////////
+      // first start: an anonymous host stores an edited settings row //
+      ////////////////////////////////////////////////////////////////////
+      MemoryRecordStore.fullReset();
+      WormAuditQBitConfig seedConfig = new WormAuditQBitConfig();
+      QInstance seedHost = WormAuditTestSupport.core(memory());
+      new WormAuditQBitProducer().withQBitConfig(seedConfig.withBackendName("memory")).produce(seedHost);
+      QContext.init(seedHost, new QSession());
+      QRecord row = WormAuditSettings.defaults(seedConfig)
+         .withValue("chain_interval_seconds", 45)
+         .withValue("verify_cron", "0 15 4 * * ?")
+         .withValue("export_cron", "0 45 1 * * ?");
+      new AuditStore(null).insert(WormAuditTables.SETTINGS, List.of(row));
+      QContext.clear();
+
+      //////////////////////////////////////////////////////////////////////
+      // restart on a token-required host, with no session in context, as //
+      // when a host builds its instance before any request arrives       //
+      //////////////////////////////////////////////////////////////////////
+      QInstance instance = tokenRequiredHost(new WormAuditQBitConfig());
+
+      assertThat(instance.getProcess(WormAuditProcesses.CHAIN).getSchedule().getRepeatSeconds()).isEqualTo(45);
+      assertThat(instance.getProcess(WormAuditProcesses.DAILY).getSchedule().getCronExpression()).isEqualTo("0 15 4 * * ?");
+      assertThat(instance.getProcess(ExportProcesses.DAILY).getSchedule().getCronExpression()).isEqualTo("0 45 1 * * ?");
+      assertThat(QContext.getQSession()).isNull();
+   }
+
+
+
+   /*******************************************************************************
+    ** With no settings row, production falls back to the configured values.
+    *******************************************************************************/
+   @Test
+   void produceFallsBackToConfigWithoutRow() throws Exception
+   {
+      MemoryRecordStore.fullReset();
+      QInstance instance = tokenRequiredHost(new WormAuditQBitConfig().withChainIntervalSeconds(17));
+      assertThat(instance.getProcess(WormAuditProcesses.CHAIN).getSchedule().getRepeatSeconds()).isEqualTo(17);
+   }
+
+
+
+   /*******************************************************************************
+    ** Build a host whose default authentication requires a token, and produce.
+    *******************************************************************************/
+   private static QInstance tokenRequiredHost(WormAuditQBitConfig config) throws Exception
+   {
+      QInstance instance = WormAuditTestSupport.core(memory());
+      instance.withInstanceDefaultAuthentication(new QAuthenticationMetaData()
+         .withName("token-required")
+         .withType(QAuthenticationType.MOCK)
+         .withModuleCodeReference(new QCodeReference(TokenRequiredAuthenticationModule.class)));
+      new WormAuditQBitProducer().withQBitConfig(config.withBackendName("memory")).produce(instance);
+      return (instance);
+   }
+
+
+
+   /*******************************************************************************
+    ** The memory backend.
+    *******************************************************************************/
+   private static QBackendMetaData memory()
+   {
+      return (new QBackendMetaData().withName("memory").withBackendType(MemoryBackendModule.class));
+   }
+}

src/test/java/com/kingsrook/qbits/wormaudit/WormAuditHomeTest.java

@@ -84,7 +84,7 @@ class WormAuditHomeTest
       assertThat(data.oldestAudit()).isNull();
       assertThat(data.nextPurge()).isNull();
       assertThat(data.exportAgeHours()).isNull();
-      assertThat(data.warnings()).containsExactly("No signing key configured", "No trusted public keys configured", "Daily verification has never run");
+      assertThat(data.warnings()).contains("No signing key configured", "No trusted public keys configured", "Daily verification has never run");
       String html = WormAuditHomeView.render(data, "/app");
       assertThat(html).contains("Chain status", "Export status", "Retention", "Recent purges", "Setup warnings",
          "No links chained yet", "No verification has run yet", "Unknown until verification runs", "No sealed exports yet",
@@ -132,9 +132,9 @@ class WormAuditHomeTest
       VerificationRuns.save(new WormAuditVerifier(config).verify(null, now.plusSeconds(67)), "SCHEDULE", null, now.plusSeconds(68));
       data = WormAuditHomeData.load(config, new MemorySealedStore(), now.plusSeconds(69));
       assertThat(data.openBreak()).isNull();
-      assertThat(data.warnings()).isEmpty();
-      assertThat(WormAuditHomeView.render(data, "/app")).doesNotContain("Setup warnings");
-      assertThat(WormAuditHomeView.renderHealthCard(data, "/app/worm_audit")).contains("No open chain breaks reported");
+      assertThat(data.warnings()).hasSize(2);
+      assertThat(WormAuditHomeView.render(data, "/app")).contains("Setup warnings", "GOVERNANCE", "30 days");
+      assertThat(WormAuditHomeView.renderHealthCard(data, "/app/worm_audit")).contains("Setup needs attention");
    }
 
 
@@ -164,8 +164,8 @@ class WormAuditHomeTest
       assertThat(data.exportGaps()).isEmpty();
       assertThat(data.lockCapability()).isEqualTo("SUPPORTED");
       assertThat(data.bucketAlias()).isEqualTo("worm_audit");
-      assertThat(data.lastExport().getValueString("lock_mode")).isEqualTo("COMPLIANCE");
-      assertThat(WormAuditHomeView.render(data, "/app")).contains("/app/worm_audit_export/1", "COMPLIANCE", "Retain until");
+      assertThat(data.lastExport().getValueString("lock_mode")).isEqualTo("GOVERNANCE");
+      assertThat(WormAuditHomeView.render(data, "/app")).contains("/app/worm_audit_export/1", "GOVERNANCE", "Retain until");
       // Corruption fixture only: the real exporter never creates this hole.
       tamper(WormAuditTables.EXPORT, 1, "from_link_id", 3L);
       assertThat(WormAuditHomeData.load(config, storage, now).exportGaps()).containsExactly("1–2");
@@ -270,7 +270,7 @@ class WormAuditHomeTest
       assertThat(instance.getApp("evidence").getWidgets()).containsExactly(WormAuditApp.HOME_WIDGET);
       assertThat(instance.getApp("evidence").getChildren()).extracting(c -> c.getName()).containsExactlyElementsOf(WormAuditTables.NAV);
       assertThat(instance.getApp("evidence").getChildren()).extracting(c -> c.getLabel())
-         .containsExactly("Verifications", "Sealed Exports", "Checkpoints", "Audit Chain", "Purges");
+         .containsExactly("Verifications", "Sealed Exports", "Checkpoints", "Audit Chain", "Detail Coverage", "Purges", "Settings");
       assertThat(instance.getWidget(WormAuditApp.HOME_WIDGET).getGridColumns()).isEqualTo(12);
       assertThat(instance.getWidget(WormAuditApp.HOME_WIDGET).getIsCard()).isFalse();
       assertThat(instance.getWidget(WormAuditApp.HEALTH_CARD_WIDGET).getIsCard()).isTrue();
@@ -278,6 +278,29 @@ class WormAuditHomeTest
    }
 
 
+   /*******************************************************************************
+    ** Embedded widgets retain direct authorization without a standalone app.
+    *******************************************************************************/
+   @Test
+   void embeddedWidgetsRequireReadPermission() throws Exception
+   {
+      var config = ChainVerificationTest.signed().withIncludeApp(false).withAppName("audit").withAppLabel("Audit");
+      var instance = init(config);
+      assertThat(instance.getApp("worm_audit")).isNull();
+      assertThat(instance.getApp("audit")).isNull();
+      var input = new RenderWidgetInput().withWidgetMetaData(instance.getWidget(WormAuditApp.HEALTH_CARD_WIDGET));
+      QContext.getQSession().withPermissions("admin.hasAccess");
+      assertThatThrownBy(() -> new WormAuditApp.HealthCardRenderer().render(input)).isInstanceOf(QPermissionDeniedException.class);
+      assertThatThrownBy(() -> new WormAuditApp.HomeRenderer().render(input)).isInstanceOf(QPermissionDeniedException.class);
+      QContext.getQSession().withPermissions(WormAuditTables.CHAIN + ".read");
+      String card = ((RawHTML) new WormAuditApp.HealthCardRenderer().render(input).getWidgetData()).getHtml();
+      assertThat(card).contains("href=\"/app/audit\"");
+      assertThat(new WormAuditApp.HomeRenderer().render(input).getWidgetData()).isInstanceOf(RawHTML.class);
+      QContext.setQSession(new QSession());
+      assertThatThrownBy(() -> new WormAuditApp.HealthCardRenderer().render(input)).isInstanceOf(QPermissionDeniedException.class);
+   }
+
+
    /*******************************************************************************
     ** Exercise the real SQL anti-join without Docker, including its exact boundary.
     *******************************************************************************/
@@ -300,7 +323,7 @@ class WormAuditHomeTest
          assertThat(data.links()).isEqualTo(1);
          assertThat(data.backlog()).isEqualTo(1);
          assertThat(data.settling()).isEqualTo(1);
-         assertThat(data.warnings()).containsExactly("Daily verification has never run");
+         assertThat(data.warnings()).contains("Daily verification has never run");
       }
    }
 
@@ -325,7 +348,7 @@ class WormAuditHomeTest
       assertThat(data.exportLag()).isEqualTo(5);
       assertThat(data.backlog()).isEqualTo(2);
       assertThat(data.settling()).isEqualTo(1);
-      assertThat(data.warnings()).isEmpty();
+      assertThat(data.warnings()).hasSize(2);
       assertThat(data.recentPurges()).extracting(r -> r.getValueString("outcome")).containsExactly("REFUSED", "PURGED");
       assertThat(STORE.where(WormAuditTables.VERIFICATION, "outcome", "FAIL")).hasSize(1).allSatisfy(row ->
       {

src/test/java/com/kingsrook/qbits/wormaudit/WormAuditQBitProducerTest.java

@@ -21,10 +21,15 @@
 package com.kingsrook.qbits.wormaudit;
 
 import java.util.List;
+import com.kingsrook.qbits.wormaudit.app.WormAuditApp;
+import com.kingsrook.qbits.wormaudit.model.WormAuditHelp;
 import com.kingsrook.qbits.wormaudit.ddl.WormAuditDdl;
 import com.kingsrook.qbits.wormaudit.model.WormAuditTables;
 import com.kingsrook.qbits.wormaudit.processes.WormAuditProcesses;
 import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
+import com.kingsrook.qqq.backend.core.model.metadata.QBackendMetaData;
+import com.kingsrook.qqq.backend.core.model.metadata.layout.QAppMetaData;
+import com.kingsrook.qqq.backend.core.modules.backend.implementations.memory.MemoryBackendModule;
 import com.kingsrook.qqq.backend.core.model.metadata.tables.Capability;
 import org.junit.jupiter.api.Test;
 import static org.assertj.core.api.Assertions.assertThat;
@@ -43,6 +48,7 @@ class WormAuditQBitProducerTest
    {
       var config = new WormAuditQBitConfig();
       QInstance instance = WormAuditTestSupport.init(config);
+      assertThat(config.getIncludeApp()).isTrue();
       assertThat(config.getRetentionYears()).isEqualTo(6);
       assertThat(config.getTableMetaDataCustomizer()).isNull();
       assertThat(config.getSigningKey()).isNull();
@@ -160,4 +166,36 @@ class WormAuditQBitProducerTest
                   () -> WormAuditDdl.resolveNames(new WormAuditQBitConfig(), new QInstance()))
             .isInstanceOf(IllegalArgumentException.class);
    }
+
+
+   /*******************************************************************************
+    ** Embedded production keeps the host app intact and registers all evidence metadata.
+    *******************************************************************************/
+   @Test
+   void embeddedMetadataPreservesHostApp() throws Exception
+   {
+      var config = new WormAuditQBitConfig().withBackendName("memory").withIncludeApp(false).withAppName("audit");
+      var instance = WormAuditTestSupport.core(new QBackendMetaData().withName("memory").withBackendType(MemoryBackendModule.class));
+      var host = new QAppMetaData().withName("audit").withLabel("Combined Audit").withWidgets(List.of("hostSummary"));
+      instance.addApp(host);
+      new WormAuditQBitProducer().withQBitConfig(config).produce(instance);
+      assertThat(config.getIncludeApp()).isFalse();
+      assertThat(instance.getApp("worm_audit")).isNull();
+      assertThat(instance.getApp("audit")).isSameAs(host);
+      assertThat(host.getLabel()).isEqualTo("Combined Audit");
+      assertThat(host.getWidgets()).containsExactly("hostSummary");
+      assertThat(host.getHelpContent()).isNullOrEmpty();
+      assertThat(instance.getWidget(WormAuditApp.HEALTH_CARD_WIDGET)).isNotNull();
+      assertThat(instance.getWidget(WormAuditApp.HOME_WIDGET)).isNotNull();
+      for(String name : WormAuditTables.ALL)
+      {
+         assertThat(instance.getTable(name).getHelpContent()).containsKey(WormAuditHelp.SLOT);
+         assertThat(instance.getTable(name).getPermissionRules().getPermissionBaseName()).isEqualTo(name);
+      }
+      assertThat(instance.getTable(WormAuditTables.CHAIN).getDisabledCapabilities()).contains(Capability.TABLE_UPDATE, Capability.TABLE_DELETE, Capability.TABLE_INSERT);
+      assertThat(instance.getTable("audit").getDisabledCapabilities()).contains(Capability.TABLE_UPDATE, Capability.TABLE_DELETE);
+      assertThat(instance.getProcess(WormAuditProcesses.VERIFY)).isNotNull();
+      assertThat(instance.getProcess(WormAuditProcesses.CHAIN).getSchedule().getRepeatSeconds()).isEqualTo(config.getChainIntervalSeconds());
+      assertThat(instance.getProcess(WormAuditProcesses.DAILY).getSchedule().getCronExpression()).isEqualTo(config.getVerifyCron());
+   }
 }

src/test/java/com/kingsrook/qbits/wormaudit/WormAuditSettingsSeedTest.java

@@ -0,0 +1,145 @@
+/*
+ * 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.wormaudit;
+
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.Statement;
+import java.util.UUID;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+import com.kingsrook.qbits.wormaudit.ddl.WormAuditDdl;
+import org.junit.jupiter.api.Test;
+import static org.assertj.core.api.Assertions.assertThat;
+
+
+/*******************************************************************************
+ ** The settings row the shipped database script seeds (ddl/postgres/worm-audit.sql,
+ ** changeset worm-audit:10, rendered with the host's configured values).
+ ** The whole script is PostgreSQL-only (triggers, roles, plpgsql), so it cannot
+ ** run on H2; this runs the settings table's CREATE and its seed statement as
+ ** the script renders them, on H2 in PostgreSQL mode: the row appears with the
+ ** host's values, a second run inserts nothing, and an edit survives.
+ ** WormAuditSettings.seed() is deprecated (nothing in src/main calls it) but still
+ ** works for memory and H2 hosts; WormAuditSettings.read() falls back to the
+ ** configured values while the row is absent.
+ *******************************************************************************/
+class WormAuditSettingsSeedTest
+{
+
+   /*******************************************************************************
+    ** Fresh table, seed twice, edit survives, values follow the host config.
+    *******************************************************************************/
+   @Test
+   void seedStatementIsInsertOnlyAndUsesHostValues() throws Exception
+   {
+      var config = new WormAuditQBitConfig().withRetentionYears(8).withExportRetentionDays(45).withCheckpointEveryLinks(12);
+      String ddl = WormAuditDdl.postgres(config);
+      String create = statement(ddl, "CREATE TABLE IF NOT EXISTS public.worm_audit_settings");
+      String seed = statement(ddl, "INSERT INTO public.worm_audit_settings");
+
+      try(Connection connection = DriverManager.getConnection("jdbc:h2:mem:" + UUID.randomUUID() + ";MODE=PostgreSQL;DB_CLOSE_DELAY=-1");
+         Statement sql = connection.createStatement())
+      {
+         sql.execute(create.replace("timestamptz", "timestamp with time zone").replace("now()", "CURRENT_TIMESTAMP").replace("current_user", "'app'"));
+         sql.execute(seed);
+         assertThat(scalar(sql, "SELECT COUNT(*) FROM public.worm_audit_settings")).isEqualTo("1");
+         assertThat(scalar(sql, "SELECT audit_retention_years FROM public.worm_audit_settings")).isEqualTo("8");
+         assertThat(scalar(sql, "SELECT export_retention_days FROM public.worm_audit_settings")).isEqualTo("45");
+         assertThat(scalar(sql, "SELECT checkpoint_every_links FROM public.worm_audit_settings")).isEqualTo("12");
+         assertThat(scalar(sql, "SELECT export_lock_mode FROM public.worm_audit_settings")).isEqualTo(config.getExportLockMode());
+         assertThat(scalar(sql, "SELECT export_cron FROM public.worm_audit_settings")).isEqualTo(config.getExportCron());
+
+         sql.execute("UPDATE public.worm_audit_settings SET export_retention_days = 90 WHERE id = 1");
+         sql.execute(seed);
+         assertThat(scalar(sql, "SELECT COUNT(*) FROM public.worm_audit_settings")).isEqualTo("1");
+         assertThat(scalar(sql, "SELECT export_retention_days FROM public.worm_audit_settings")).isEqualTo("90");
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** The deprecated public seed() still creates row 1 once and keeps edits.
+    *******************************************************************************/
+   @Test
+   @SuppressWarnings("deprecation")
+   void deprecatedSeedStillWorksForMemoryHosts() throws Exception
+   {
+      var config = new WormAuditQBitConfig().withRetentionYears(8);
+      WormAuditTestSupport.init(config);
+      WormAuditSettings.seed(config);
+      assertThat(WormAuditTestSupport.STORE.get("worm_audit_settings", 1).getValueInteger("audit_retention_years")).isEqualTo(8);
+      WormAuditSettings.seed(config);
+      assertThat(WormAuditTestSupport.STORE.get("worm_audit_settings", 1)).isNotNull();
+   }
+
+
+
+   /*******************************************************************************
+    ** The script seeds nothing but the settings table: its only top-level INSERT is the
+ ** settings seed (the indented purge INSERTs are the purge function writing its log).
+    *******************************************************************************/
+   @Test
+   void onlySettingsAreSeeded()
+   {
+      String ddl = WormAuditDdl.postgres(new WormAuditQBitConfig());
+      Matcher matcher = Pattern.compile("(?m)^INSERT INTO\\s+(\\S+)").matcher(ddl);
+      while(matcher.find())
+      {
+         //////////////////////////////////////////////////////////////////////////
+         // worm-audit:13 backfills detail coverage from links that already exist: //
+         // derived from the chain, never seeded content.                         //
+         //////////////////////////////////////////////////////////////////////////
+         assertThat(matcher.group(1)).as("every top-level INSERT in the script targets the settings table, not a log")
+            .isIn("public.worm_audit_settings", "public.worm_audit_chain_detail(detail_id,");
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** From the start text up to the statement's closing semicolon.
+    *******************************************************************************/
+   private static String statement(String ddl, String start)
+   {
+      int from = ddl.indexOf(start);
+      assertThat(from).as(start).isGreaterThanOrEqualTo(0);
+      return (ddl.substring(from, ddl.indexOf(";", from)));
+   }
+
+
+
+   /*******************************************************************************
+    **
+    *******************************************************************************/
+   private static String scalar(Statement sql, String query) throws Exception
+   {
+      try(ResultSet result = sql.executeQuery(query))
+      {
+         assertThat(result.next()).isTrue();
+         return (result.getString(1));
+      }
+   }
+
+}

src/test/java/com/kingsrook/qbits/wormaudit/WormAuditSettingsTest.java

@@ -0,0 +1,307 @@
+/*
+ * 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.wormaudit;
+
+
+import java.time.Clock;
+import java.time.Instant;
+import java.time.ZoneId;
+import java.time.ZoneOffset;
+import java.util.List;
+import java.util.Set;
+import com.kingsrook.qbits.wormaudit.app.WormAuditHomeData;
+import com.kingsrook.qbits.wormaudit.app.WormAuditHomeView;
+import com.kingsrook.qbits.wormaudit.chain.WormAuditChainer;
+import com.kingsrook.qbits.wormaudit.export.SealedExporter;
+import com.kingsrook.qbits.wormaudit.export.SealedExportVerifier;
+import com.kingsrook.qbits.wormaudit.model.SettingsCustomizer;
+import com.kingsrook.qbits.wormaudit.model.WormAuditTables;
+import com.kingsrook.qbits.wormaudit.processes.WormAuditProcesses;
+import com.kingsrook.qbits.wormaudit.processes.ExportProcesses;
+import com.kingsrook.qbits.wormaudit.verify.WormAuditVerifier;
+import com.kingsrook.qqq.backend.core.actions.tables.InsertAction;
+import com.kingsrook.qqq.backend.core.actions.tables.UpdateAction;
+import com.kingsrook.qqq.backend.core.actions.tables.DeleteAction;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.model.actions.tables.insert.InsertInput;
+import com.kingsrook.qqq.backend.core.model.actions.tables.update.UpdateInput;
+import com.kingsrook.qqq.backend.core.model.actions.tables.delete.DeleteInput;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+import com.kingsrook.qqq.backend.core.model.metadata.audits.AuditLevel;
+import com.kingsrook.qqq.backend.core.model.metadata.tables.Capability;
+import com.kingsrook.qqq.backend.core.model.session.QSession;
+import org.junit.jupiter.api.Test;
+import static com.kingsrook.qbits.wormaudit.WormAuditTestSupport.*;
+import static org.assertj.core.api.Assertions.*;
+
+
+/*******************************************************************************
+ ** Editable settings, bounded cache, audited changes and historical lock modes.
+ *******************************************************************************/
+class WormAuditSettingsTest
+{
+   static final String TABLE = WormAuditTables.SETTINGS;
+
+   /*******************************************************************************
+    ** Deterministic clock for the exact 30-second cache boundary.
+    *******************************************************************************/
+   static class MutableClock extends Clock
+   {
+      Instant now = Instant.parse("2026-10-14T02:00:00Z");
+      /*******************************************************************************
+       ** Verify get zone.
+       *******************************************************************************/
+      public ZoneId getZone()
+      {
+         return (ZoneOffset.UTC);
+      }
+      /*******************************************************************************
+       ** Verify with zone.
+       *******************************************************************************/
+      public Clock withZone(ZoneId zone)
+      {
+         return (this);
+      }
+      /*******************************************************************************
+       ** Verify instant.
+       *******************************************************************************/
+      public Instant instant()
+      {
+         return (now);
+      }
+   }
+
+   /*******************************************************************************
+    ** Verify editor.
+    *******************************************************************************/
+   static void editor()
+   {
+      QContext.getQInstance().withEnforcePermissionsInCoreActions(true);
+      QContext.getQSession().withPermissions(Set.of(TABLE + ".read", TABLE + ".edit", "worm_audit.settings"));
+   }
+
+   /*******************************************************************************
+    ** Verify update.
+    *******************************************************************************/
+   static void update(String field, java.io.Serializable value) throws Exception
+   {
+      var result = new UpdateAction().execute(new UpdateInput(TABLE).withRecords(List.of(new QRecord().withValue("id", 1).withValue(field, value))));
+      assertThat(result.getRecords()).allSatisfy(row -> assertThat(row.getErrors()).isNullOrEmpty());
+   }
+
+   /*******************************************************************************
+    ** Verify fallback overrides cache and insert only seed.
+    *******************************************************************************/
+   @Test
+   void fallbackOverridesCacheAndInsertOnlySeed() throws Exception
+   {
+      var config = new WormAuditQBitConfig().withRetentionYears(8).withExportRetentionDays(45).withCheckpointEveryLinks(12);
+      var instance = init(config);
+      var clock = new MutableClock();
+      var reader = new WormAuditSettings(config, clock);
+      assertThat(reader.read().audit_retention_years()).isEqualTo(8);
+      assertThat(reader.read().export_retention_days()).isEqualTo(45);
+      WormAuditTestSupport.seedSettings(config);
+      editor();
+      update("export_retention_days", 90);
+      assertThat(reader.read().export_retention_days()).isEqualTo(90);
+      update("export_retention_days", 120);
+      clock.now = clock.now.plusSeconds(29);
+      assertThat(reader.read().export_retention_days()).isEqualTo(90);
+      clock.now = clock.now.plusSeconds(1);
+      assertThat(reader.read().export_retention_days()).isEqualTo(120);
+      WormAuditTestSupport.seedSettings(config);
+      assertThat(STORE.get(TABLE, 1).getValueInteger("export_retention_days")).isEqualTo(120);
+      assertThat(reader.read().checkpoint_every_links()).isEqualTo(12);
+      instance.getTables().remove(TABLE);
+      reader.invalidate();
+      assertThat(reader.read().export_retention_days()).isEqualTo(45);
+   }
+
+   /*******************************************************************************
+    ** Verify partial null row uses config and invalid persisted settings fail closed.
+    *******************************************************************************/
+   @Test
+   void partialNullRowUsesConfigAndInvalidPersistedSettingsFailClosed() throws Exception
+   {
+      var config = new WormAuditQBitConfig().withExportRetentionDays(42);
+      init(config);
+      com.kingsrook.qqq.backend.core.modules.backend.implementations.memory.MemoryRecordStore.getInstance().insert(
+         new InsertInput(TABLE).withRecord(new QRecord().withValue("id", 1).withValue("export_lock_mode", "COMPLIANCE")), false);
+      var reader = new WormAuditSettings(config);
+      assertThat(reader.read().export_retention_days()).isEqualTo(42);
+      assertThat(reader.read().export_lock_mode()).isEqualTo("COMPLIANCE");
+      com.kingsrook.qqq.backend.core.modules.backend.implementations.memory.MemoryRecordStore.getInstance().update(
+         new UpdateInput(TABLE).withRecords(List.of(new QRecord().withValue("id", 1).withValue("export_retention_days", 0))), false);
+      reader.invalidate();
+      assertThatThrownBy(reader::read).hasMessageContaining("Export retention days");
+   }
+
+   /*******************************************************************************
+    ** Verify missing physical table falls back.
+    *******************************************************************************/
+   @Test
+   void missingPhysicalTableFallsBack() throws Exception
+   {
+      var config = new WormAuditQBitConfig().withExportRetentionDays(81);
+      var instance = RdbmsTestSupport.instance("jdbc:h2:mem:settings_missing;DB_CLOSE_DELAY=-1;DATABASE_TO_LOWER=TRUE", "sa", "", config, false);
+      QContext.init(instance, new QSession());
+      assertThat(config.effectiveSettings().export_retention_days()).isEqualTo(81);
+   }
+
+   /*******************************************************************************
+    ** Verify validation and capabilities.
+    *******************************************************************************/
+   @Test
+   void validationAndCapabilities() throws Exception
+   {
+      var config = new WormAuditQBitConfig();
+      var instance = init(config);
+      var table = instance.getTable(TABLE);
+      assertThat(table.getLabel()).isEqualTo("WORM Audit Settings");
+      assertThat(table.getAuditRules().getAuditLevel()).isEqualTo(AuditLevel.FIELD);
+      assertThat(table.isCapabilityEnabled(instance.getBackendForTable(TABLE), Capability.TABLE_UPDATE)).isTrue();
+      assertThat(table.isCapabilityEnabled(instance.getBackendForTable(TABLE), Capability.TABLE_INSERT)).isFalse();
+      assertThat(table.isCapabilityEnabled(instance.getBackendForTable(TABLE), Capability.TABLE_DELETE)).isFalse();
+      WormAuditTestSupport.seedSettings(config);
+      editor();
+      assertThatThrownBy(() -> new DeleteAction().execute(new DeleteInput(TABLE).withPrimaryKeys(List.of(1)))).isInstanceOf(Exception.class);
+      assertThatThrownBy(() -> new InsertAction().execute(new InsertInput(TABLE).withRecord(new QRecord().withValue("id", 2)))).isInstanceOf(Exception.class);
+      assertThatThrownBy(() -> new SettingsCustomizer().preInsert(new InsertInput(TABLE), List.of(new QRecord().withValue("id", 2)), false)).hasMessageContaining("one row only (ID 1)");
+      assertThatThrownBy(() -> new SettingsCustomizer().preInsert(new InsertInput(TABLE), List.of(WormAuditSettings.defaults(config)), false))
+         .hasMessageContaining("already exists");
+      assertThatThrownBy(() -> new SettingsCustomizer().preDelete(new DeleteInput(TABLE), List.of(STORE.get(TABLE, 1)), false))
+         .hasMessageContaining("cannot be deleted");
+      for(String field : List.of("audit_retention_years", "export_retention_days", "checkpoint_every_links", "checkpoint_every_minutes", "chain_interval_seconds", "late_detail_lookback_hours"))
+      {
+         assertThatThrownBy(() -> WormAuditSettings.validate(new QRecord().withValue(field, 0))).hasMessageContaining("whole number");
+      }
+      assertThatThrownBy(() -> WormAuditSettings.validate(new QRecord().withValue("settle_horizon_seconds", -1))).hasMessageContaining("Settle horizon seconds");
+      assertThatThrownBy(() -> WormAuditSettings.validate(new QRecord().withValue("audit_retention_years", 101))).hasMessageContaining("100");
+      assertThatThrownBy(() -> WormAuditSettings.validate(new QRecord().withValue("export_retention_days", 36501))).hasMessageContaining("36500");
+      assertThatThrownBy(() -> WormAuditSettings.validate(new QRecord().withValue("export_retention_days", "1.5"))).hasMessageContaining("whole number");
+      assertThatThrownBy(() -> WormAuditSettings.validate(new QRecord().withValue("export_lock_mode", "NONE"))).hasMessageContaining("GOVERNANCE or COMPLIANCE");
+      assertThatThrownBy(() -> WormAuditSettings.validate(new QRecord().withValue("export_lock_mode", null))).hasMessageContaining("GOVERNANCE or COMPLIANCE");
+      for(String field : List.of("export_cron", "verify_cron"))
+      {
+         assertThatThrownBy(() -> new SettingsCustomizer().preUpdate(new UpdateInput(TABLE), List.of(new QRecord().withValue("id", 1).withValue(field, "bad")), false, java.util.Optional.empty()))
+            .hasMessageContaining("valid Quartz cron");
+      }
+      assertThatThrownBy(() -> new UpdateAction().execute(new UpdateInput(TABLE).withRecords(List.of(new QRecord().withValue("id", 1).withValue("export_cron", "bad")))))
+         .hasMessageContaining("valid Quartz cron");
+      assertThat(STORE.get(TABLE, 1).getValueString("export_cron")).isEqualTo(config.getExportCron());
+   }
+
+   /*******************************************************************************
+    ** Verify edit requires both permissions and produces field audit that chains.
+    *******************************************************************************/
+   @Test
+   void editRequiresBothPermissionsAndProducesFieldAuditThatChains() throws Exception
+   {
+      var config = ChainVerificationTest.signed().withSettleHorizonSeconds(0);
+      init(config);
+      WormAuditTestSupport.seedSettings(config);
+      QContext.getQInstance().withEnforcePermissionsInCoreActions(true);
+      QContext.getQSession().withPermissions(Set.of(TABLE + ".read", TABLE + ".edit"));
+      assertThatThrownBy(() -> update("export_lock_mode", "COMPLIANCE")).hasMessageContaining("worm_audit.settings");
+      QContext.getQSession().setPermissions(new java.util.HashSet<>(Set.of("worm_audit.settings")));
+      assertThatThrownBy(() -> update("export_lock_mode", "COMPLIANCE")).hasMessageContaining("worm_audit_settings.edit");
+      editor();
+      update("export_lock_mode", "COMPLIANCE");
+      assertThat(STORE.all("audit")).hasSize(1);
+      assertThat(STORE.where("auditDetail", "fieldName", "export_lock_mode")).hasSize(1);
+      assertThat(STORE.get(TABLE, 1).getValueInstant("modify_date")).isNotNull();
+      assertThat(STORE.get(TABLE, 1).getValueString("modified_by")).isEqualTo("system");
+      new WormAuditChainer(config).chainOnce(Instant.now().plusSeconds(1));
+      assertThat(STORE.all(WormAuditTables.CHAIN)).hasSize(1);
+      assertThat(new WormAuditVerifier(config).verify(null, Instant.now().plusSeconds(1)).passed()).isTrue();
+   }
+
+   /*******************************************************************************
+    ** Verify startup schedules and home use effective settings.
+    *******************************************************************************/
+   @Test
+   void startupSchedulesAndHomeUseEffectiveSettings() throws Exception
+   {
+      var config = ChainVerificationTest.signed();
+      var instance = init(config);
+      WormAuditTestSupport.seedSettings(config);
+      editor();
+      update("chain_interval_seconds", 45);
+      update("export_cron", "0 15 4 * * ?");
+      update("verify_cron", "0 30 5 * * ?");
+      update("audit_retention_years", 7);
+      assertThat(instance.getProcess(WormAuditProcesses.CHAIN).getSchedule().getRepeatSeconds()).isEqualTo(30);
+      var restarted = WormAuditTestSupport.core(new com.kingsrook.qqq.backend.core.model.metadata.QBackendMetaData().withName("memory")
+         .withBackendType(com.kingsrook.qqq.backend.core.modules.backend.implementations.memory.MemoryBackendModule.class));
+      new WormAuditQBitProducer().withQBitConfig(config).produce(restarted);
+      assertThat(QContext.getQInstance()).isSameAs(instance);
+      instance = restarted;
+      QContext.init(instance, new QSession());
+      assertThat(instance.getProcess(WormAuditProcesses.CHAIN).getSchedule().getRepeatSeconds()).isEqualTo(45);
+      assertThat(instance.getProcess(ExportProcesses.DAILY).getSchedule().getCronExpression()).isEqualTo("0 15 4 * * ?");
+      assertThat(instance.getProcess(WormAuditProcesses.DAILY).getSchedule().getCronExpression()).isEqualTo("0 30 5 * * ?");
+      assertThat(instance.getProcess(WormAuditProcesses.DAILY).getSchedule().getCronTimeZoneId()).isEqualTo("UTC");
+      audit(50);
+      var home = WormAuditHomeData.load(config, new SealedExportTest.MemorySealedStore(), Instant.now());
+      assertThat(home.nextPurge()).isEqualTo(OLD.plusSeconds(50).atZone(ZoneOffset.UTC).plusYears(7).toInstant());
+      assertThat(home.warnings()).anyMatch(w -> w.contains("differs from") && w.contains("purge interval"));
+      assertThat(WormAuditHomeView.render(home, "/app")).contains("GOVERNANCE", "s3:BypassGovernanceRetention", "Switch to COMPLIANCE", "30 days", "Export retention is shorter");
+   }
+
+   /*******************************************************************************
+    ** Verify switch affects new exports only and old checkpoint cadence remains valid.
+    *******************************************************************************/
+   @Test
+   void switchAffectsNewExportsOnlyAndOldCheckpointCadenceRemainsValid() throws Exception
+   {
+      var config = ChainVerificationTest.signed().withCheckpointEveryLinks(2).withSettleHorizonSeconds(0);
+      init(config);
+      WormAuditTestSupport.seedSettings(config);
+      var storage = new SealedExportTest.MemorySealedStore();
+      audit(1);
+      audit(2);
+      Instant now = Instant.now().plusSeconds(1);
+      new WormAuditChainer(config).chainOnce(now);
+      var exporter = new SealedExporter(config, storage);
+      var first = exporter.run("USER", now).row();
+      assertThat(first).isNotNull();
+      assertThat(first.getValueString("lock_mode")).isEqualTo("GOVERNANCE");
+      assertThat(first.getValueInstant("retain_until")).isEqualTo(now.truncatedTo(java.time.temporal.ChronoUnit.MILLIS).plusSeconds(30 * 86400L));
+      assertThat(storage.heads.values()).allSatisfy(h -> assertThat(h.getLockMode()).isEqualTo("GOVERNANCE"));
+      var verifier = new SealedExportVerifier(config, storage);
+      assertThat(verifier.verify(first, "USER").result().passed()).isTrue();
+      editor();
+      update("export_lock_mode", "COMPLIANCE");
+      update("checkpoint_every_links", 1);
+      new WormAuditChainer(config).chainOnce(Instant.now().plusSeconds(2));
+      var second = exporter.run("USER", Instant.now().plusSeconds(2)).row();
+      assertThat(second.getValueString("lock_mode")).isEqualTo("COMPLIANCE");
+      assertThat(verifier.verify(first, "USER").result().passed()).isTrue();
+      assertThat(verifier.verify(second, "USER").result().passed()).isTrue();
+      assertThat(new WormAuditVerifier(config).verify(null, Instant.now().plusSeconds(2)).passed()).isTrue();
+      String key = first.getValueString("object_prefix") + "links.jsonl";
+      storage.heads.get(key).withLockMode("COMPLIANCE");
+      assertThat(verifier.verify(first, "USER").result().getBreaks()).anyMatch(b -> b.reason().contains("lacks the recorded GOVERNANCE retention"));
+      storage.heads.remove(key);
+      assertThat(verifier.verify(first, "USER").result().getBreaks()).anyMatch(b -> b.reason().contains(key) && b.reason().contains("GOVERNANCE bypass removal"));
+   }
+}

src/test/java/com/kingsrook/qbits/wormaudit/WormAuditTablesGuardrailTest.java

@@ -0,0 +1,170 @@
+/*
+ * 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.wormaudit;
+
+
+import java.nio.charset.StandardCharsets;
+import java.nio.file.Files;
+import java.nio.file.Path;
+import java.util.List;
+import java.util.Map;
+import java.util.stream.Stream;
+import com.fasterxml.jackson.databind.JsonNode;
+import com.kingsrook.qbits.wormaudit.ddl.WormAuditDdl;
+import com.kingsrook.qbits.wormaudit.model.WormAuditHelp;
+import com.kingsrook.qbits.wormaudit.model.WormAuditTables;
+import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
+import com.kingsrook.qqq.backend.core.model.metadata.help.QHelpContent;
+import org.junit.jupiter.api.Test;
+import static org.assertj.core.api.Assertions.assertThat;
+
+
+/*******************************************************************************
+ ** Guardrail (owner requirement 2026-10-14: "all tables should be populated and
+ ** the qbit should be fully using them"). For every worm_audit_* table:
+ ** - runtime code (not just its model, metadata or home screen) reads or writes
+ **   it;
+ ** - it is either seeded by the shipped database script, or listed here with
+ **   why it starts empty;
+ ** - it has bundle help that says what it is for and how its rows get created
+ **   (shown on the table screen and when it is empty).
+ ** A new table fails this test until it is wired, seeded or explained.
+ *******************************************************************************/
+class WormAuditTablesGuardrailTest
+{
+   private static final Path MAIN = Path.of("src", "main", "java", "com", "kingsrook", "qbits", "wormaudit");
+
+   /*******************************************************************************
+    ** Tables the shipped database script (ddl/postgres/worm-audit.sql) seeds.
+    *******************************************************************************/
+   static final List<String> SEEDED = List.of(WormAuditTables.SETTINGS);
+
+   /*******************************************************************************
+    ** Tables that legitimately start empty, and where their rows come from.
+    *******************************************************************************/
+   static final Map<String, String> STARTS_EMPTY = Map.of(
+      WormAuditTables.CHAIN, "log: the chain job links each settled audit record",
+      WormAuditTables.CHAIN_DETAIL, "log: the chain trigger (or writer) records each detail a new link covers",
+      WormAuditTables.CHECKPOINT, "log: the chain job signs a range every N links or minutes",
+      WormAuditTables.EXPORT, "log: the daily export or Export now seals a range",
+      WormAuditTables.VERIFICATION, "log: every verification run, manual or daily",
+      WormAuditTables.PURGE, "log: the database owner's purge function, after retention");
+
+
+
+   /*******************************************************************************
+    ** Every table is used by runtime code.
+    *******************************************************************************/
+   @Test
+   void everyTableIsUsedAtRuntime() throws Exception
+   {
+      List<String> runtimeSource;
+      try(Stream<Path> files = Files.walk(MAIN))
+      {
+         runtimeSource = files.filter(p -> p.toString().endsWith(".java"))
+            .filter(p -> !p.startsWith(MAIN.resolve("model")) && !p.startsWith(MAIN.resolve("app")) && !p.startsWith(MAIN.resolve("ddl")))
+            .filter(p -> !p.endsWith("WormAuditQBitProducer.java"))
+            .map(WormAuditTablesGuardrailTest::read).toList();
+      }
+      for(String table : WormAuditTables.ALL)
+      {
+         String constant = "WormAuditTables." + table.substring("worm_audit_".length()).toUpperCase();
+         assertThat(runtimeSource.stream().anyMatch(source -> source.contains(constant)))
+            .as(table + " (" + constant + ") must be read or written by runtime code, not only declared").isTrue();
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Every table is seeded by the script or explained.
+    *******************************************************************************/
+   @Test
+   void everyTableIsSeededOrExplained() throws Exception
+   {
+      String ddl = WormAuditDdl.postgres(new WormAuditQBitConfig());
+      for(String table : WormAuditTables.ALL)
+      {
+         boolean seeded = SEEDED.contains(table);
+         assertThat(seeded || STARTS_EMPTY.containsKey(table)).as(table + " must be seeded by the database script or listed in STARTS_EMPTY").isTrue();
+         assertThat(seeded && STARTS_EMPTY.containsKey(table)).as(table + " cannot be both seeded and starting empty").isFalse();
+         assertThat(ddl.contains("INSERT INTO public." + table + "\n") || ddl.contains("INSERT INTO public." + table + " ")).as(table + " seeded iff the script inserts it").isEqualTo(seeded);
+      }
+      assertThat(STARTS_EMPTY.keySet()).allMatch(WormAuditTables.ALL::contains, "STARTS_EMPTY names only real tables");
+   }
+
+
+
+   /*******************************************************************************
+    ** Every table and the app have bundle help that says how rows get created.
+    *******************************************************************************/
+   @Test
+   void everyTableHasEmptyStateHelp() throws Exception
+   {
+      QInstance instance = WormAuditTestSupport.init(new WormAuditQBitConfig());
+      assertThat(WormAuditHelp.entries()).allSatisfy((key, entry) -> assertThat(entry.path("summary").asText().length()).as(key).isLessThanOrEqualTo(120));
+      for(String table : WormAuditTables.ALL)
+      {
+         List<QHelpContent> help = instance.getTable(table).getHelpContent() == null ? null : instance.getTable(table).getHelpContent().get(WormAuditHelp.SLOT);
+         assertThat(help).as(table + " table help").isNotEmpty();
+         assertThat(help.get(0).getContent()).as(table + " help says how rows get created").contains("How rows get created");
+      }
+      assertThat(instance.getApp("worm_audit").getHelpContent()).containsKey(WormAuditHelp.SLOT);
+      for(Map.Entry<String, JsonNode> entry : WormAuditHelp.entries().entrySet())
+      {
+         String key = entry.getKey();
+         assertThat(key.startsWith("app:") || WormAuditTables.ALL.contains(key.substring(6, key.indexOf(';')))).as("help names only real tables: " + key).isTrue();
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** A host's own help is never replaced.
+    *******************************************************************************/
+   @Test
+   void hostHelpIsKept() throws Exception
+   {
+      QInstance instance = WormAuditTestSupport.init(new WormAuditQBitConfig());
+      instance.getTable(WormAuditTables.CHAIN).getHelpContent().put(WormAuditHelp.SLOT, List.of(new QHelpContent().withContentAsMarkdown("host text")));
+      WormAuditHelp.apply(instance, new WormAuditQBitConfig());
+      assertThat(instance.getTable(WormAuditTables.CHAIN).getHelpContent().get(WormAuditHelp.SLOT)).hasSize(1);
+      assertThat(instance.getTable(WormAuditTables.CHAIN).getHelpContent().get(WormAuditHelp.SLOT).get(0).getContent()).isEqualTo("host text");
+   }
+
+
+
+   /*******************************************************************************
+    **
+    *******************************************************************************/
+   private static String read(Path path)
+   {
+      try
+      {
+         return (Files.readString(path, StandardCharsets.UTF_8));
+      }
+      catch(Exception e)
+      {
+         throw (new IllegalStateException(e));
+      }
+   }
+
+}

src/test/java/com/kingsrook/qbits/wormaudit/WormAuditTestSupport.java

@@ -37,7 +37,7 @@ import com.kingsrook.qqq.backend.core.modules.backend.implementations.memory.Mem
 import java.time.Instant;
 import java.util.List;
 
-class WormAuditTestSupport {
+public class WormAuditTestSupport {
     static final Instant OLD = Instant.parse("2020-01-02T03:04:05.123456Z");
     static final AuditStore STORE = new AuditStore(null);
 
@@ -130,6 +130,16 @@ class WormAuditTestSupport {
                         false);
     }
 
+    /** What the shipped database script does for the singleton: insert row 1 from the host config, only when absent. */
+    public static void seedSettings(WormAuditQBitConfig config) throws Exception {
+        if (STORE.get(com.kingsrook.qbits.wormaudit.model.WormAuditTables.SETTINGS, 1) == null) {
+            STORE.insert(
+                    com.kingsrook.qbits.wormaudit.model.WormAuditTables.SETTINGS,
+                    List.of(WormAuditSettings.defaults(config)));
+        }
+        config.invalidateSettings();
+    }
+
     static void remove(String table, long id) throws Exception {
         MemoryRecordStore.getInstance().delete(new DeleteInput(table).withPrimaryKeys(List.of(id)));
     }

src/test/java/com/kingsrook/qbits/wormaudit/chain/SweepFloorsTest.java

@@ -0,0 +1,104 @@
+/*
+ * 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.wormaudit.chain;
+
+
+import java.time.Instant;
+import com.kingsrook.qbits.wormaudit.WormAuditQBitConfig;
+import org.junit.jupiter.api.Test;
+import static org.assertj.core.api.Assertions.assertThat;
+
+
+/*******************************************************************************
+ ** Floors move only after complete sweeps, only to ids observed before the
+ ** horizon, and reset when the tables shrink below them.
+ *******************************************************************************/
+class SweepFloorsTest
+{
+   private static final Instant T0 = Instant.parse("2026-10-15T00:00:00Z");
+
+
+
+   /*******************************************************************************
+    ** Floors follow the newest observation at or before the horizon.
+    *******************************************************************************/
+   @Test
+   void floorsAdvanceOnlyPastSettledObservationsOfCompleteSweeps()
+   {
+      SweepFloors floors = SweepFloors.of(new WormAuditQBitConfig());
+      var first = floors.observe(T0, 10, 100);
+      floors.swept(first, T0.minusSeconds(1), true, true, null);
+      assertThat(floors.audit()).as("nothing observed before the horizon yet").isZero();
+
+      var second = floors.observe(T0.plusSeconds(30), 12, 130);
+      floors.swept(second, T0.plusSeconds(10), false, true, null);
+      assertThat(floors.audit()).as("the header sweep hit its limit").isZero();
+      assertThat(floors.detail()).isEqualTo(100);
+
+      var third = floors.observe(T0.plusSeconds(60), 15, 160);
+      floors.swept(third, T0.plusSeconds(45), true, false, null);
+      assertThat(floors.audit()).isEqualTo(12);
+      assertThat(floors.detail()).as("the late-detail sweep hit its limit").isEqualTo(100);
+
+      var fourth = floors.observe(T0.plusSeconds(90), 15, 160);
+      floors.swept(fourth, T0.plusSeconds(90), true, true, null);
+      assertThat(floors.audit()).isEqualTo(15);
+      assertThat(floors.detail()).isEqualTo(160);
+
+      var shrunk = floors.observe(T0.plusSeconds(120), 3, 7);
+      assertThat(floors.audit()).as("tables emptied below the floor: start over").isZero();
+      assertThat(floors.detail()).isZero();
+      floors.swept(shrunk, T0.plusSeconds(100), true, true, null);
+      assertThat(floors.audit()).isZero();
+   }
+
+
+
+   /*******************************************************************************
+    ** A full sweep is due first, then hourly after each complete one.
+    *******************************************************************************/
+   @Test
+   void fullSweepIsDueFirstAndHourly()
+   {
+      SweepFloors floors = SweepFloors.of(new WormAuditQBitConfig());
+      assertThat(floors.fullSweepDue(T0)).isTrue();
+      var first = floors.observe(T0, 1, 1);
+      floors.swept(first, T0, true, false, T0);
+      assertThat(floors.fullSweepDue(T0.plusSeconds(30))).as("an incomplete full sweep repeats").isTrue();
+      var second = floors.observe(T0.plusSeconds(30), 1, 1);
+      floors.swept(second, T0, true, true, T0.plusSeconds(30));
+      assertThat(floors.fullSweepDue(T0.plusSeconds(60))).isFalse();
+      assertThat(floors.fullSweepDue(T0.plusSeconds(30).plus(SweepFloors.FULL_SWEEP_EVERY))).isTrue();
+      assertThat(floors.fullSweepDue(T0)).as("clock moved backwards").isTrue();
+   }
+
+
+
+   /*******************************************************************************
+    ** Each installed config has its own floors.
+    *******************************************************************************/
+   @Test
+   void floorsArePerConfig()
+   {
+      var config = new WormAuditQBitConfig();
+      assertThat(SweepFloors.of(config)).isSameAs(SweepFloors.of(config)).isNotSameAs(SweepFloors.of(new WormAuditQBitConfig()));
+   }
+}

src/test/java/com/kingsrook/qbits/wormaudit/demo/MemorySealedStore.java

@@ -57,7 +57,7 @@ public class MemorySealedStore implements SealedStore
     ** Refuse overwrites and retain the exact bytes.
     *******************************************************************************/
    @Override
-   public String put(String key, Path file, Instant until) throws Exception
+   public String put(String key, Path file, com.kingsrook.qbits.objectstorage.api.LockMode mode, Instant until) throws Exception
    {
       if(objects.containsKey(key))
       {
@@ -65,7 +65,7 @@ public class MemorySealedStore implements SealedStore
       }
       String version = "v" + (objects.size() + 1);
       objects.put(key, Files.readAllBytes(file));
-      heads.put(key, new StoredObject().withKey(key).withVersionId(version).withLockMode("COMPLIANCE").withLockRetainUntil(until));
+      heads.put(key, new StoredObject().withKey(key).withVersionId(version).withLockMode(mode.name()).withLockRetainUntil(until));
       return (version);
    }
 

src/test/java/com/kingsrook/qbits/wormaudit/demo/WormAuditDemoServer.java

@@ -176,6 +176,7 @@ public class WormAuditDemoServer extends AbstractQQQApplication
    public void seed(QInstance instance) throws Exception
    {
       QContext.init(instance, new QSession());
+      com.kingsrook.qbits.wormaudit.WormAuditTestSupport.seedSettings(config);
       AuditStore store = new AuditStore(null);
       Instant today = Instant.now().minus(Duration.ofHours(2));
       try