Merge origin/develop into feature/foreign-keys

Keeps both changelog entries. H2 tests and the demo server load
src/test/resources/webhooks-h2.sql (FKs in H2 syntax), never the Postgres DDL
or the pg_temp upgrade script.

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

CHANGELOG.md

@@ -5,6 +5,15 @@ The format follows [Keep a Changelog](https://keepachangelog.com/en/1.1.0/) and
 
 ## [Unreleased]
 
+### Added
+- Foreign keys between all five tables in `docs/ddl/webhooks-postgres.sql`, named `fk_<table>_<column>`: an endpoint's secrets and subscriptions are deleted with it (CASCADE), deliveries and attempts keep their endpoint (RESTRICT: cancel and Delete Deliveries before deleting an endpoint with history), a delivery outlives its subscription (SET NULL) and attempts go with their delivery (CASCADE). New index `webhooks_delivery_subscription_ix`.
+- `docs/ddl/webhooks-foreign-keys-upgrade.sql` for existing databases: records orphan rows in `qqq_fk_quarantine` (nulls nullable references, deletes rows with a missing required parent and keeps them as JSON), replaces the NO ACTION endpoint keys, and is idempotent.
+- `ForeignKeyGuardrailTest` applies the DDL to PostgreSQL 17 (Testcontainers) and fails when a join or table possible-value field between webhooks tables has no foreign key.
+- Every table explains itself: a help bundle (`help/qbit-webhooks.yaml`, QQQ help-bundle format) says what each `webhooks_*` table is for and how its rows get created, including where event types come from. It shows on the table screen and when the table is empty. Applied by `WebhooksHelp` (STAND-IN for QRun-IO/qqq#1198); a host's own help is kept.
+- A Setup card on the Webhooks home lists event types, encryption key, endpoints, signing secrets and subscriptions with their state and the next step.
+- `WebhooksTablesGuardrailTest` (each table is used at runtime, seeded or explained, and has help) and a test that a previous secret stops signing at its Signs Until date even before the maintenance job retires it.
+- Nothing is seeded, deliberately: the tables hold the owner's receivers and secrets, or logs.
+
 ### Changed
 - Require QQQ 4.1.0-RC.5 and use its webhook signer, verifier, secret and verification exception (QRun-IO/qqq#1160, F11). Removed the internal signing package; Java consumers must import `com.kingsrook.qqq.backend.core.utils.webhooks` instead.
 - Signing wire format, timestamp tolerance, rotation, endpoint management, encrypted secret storage and delivery behavior are unchanged.

README.md

@@ -50,6 +50,12 @@ Set `WEBHOOKS_SECRET_KEY` to the base64 of 32 random bytes, so the qbit can gene
 
 Deliveries and attempts are written only by the qbit. People act on them through the actions below.
 
+## Setup and empty states
+
+Nothing in this qbit is seeded: endpoints, secrets and subscriptions are your choices, and deliveries and attempts are logs. Instead the app home has a **Setup** card (event types, encryption key, endpoints, signing secrets, subscriptions, each with its state and next step), and every table has help (`help/qbit-webhooks.yaml`) that explains how its rows get created and shows when the table is empty.
+
+The event types you can subscribe to come from code, not from a table: the host's `tableEventSources`, `processEventSources` and ESB settings, custom events it fires with `Webhooks.fire`, and a built-in Test event, all held in the `WebhooksRegistry`.
+
 ## Permissions
 
 | Permission | Grants |

UPGRADE.md

@@ -0,0 +1,5 @@
+# Upgrading
+
+## Unreleased
+
+No action is needed. The qbit adds help to its tables and a Setup card to its app home when it is produced; it adds no tables, columns or data, and a host's own table or app help is kept. Nothing is seeded.

docs/ddl/webhooks-foreign-keys-upgrade.sql

@@ -0,0 +1,68 @@
+-- qbit-webhooks: add the foreign keys to an existing PostgreSQL schema
+-- (one created from webhooks-postgres.sql before they were declared there).
+--
+-- Safe to run more than once: a foreign key that already exists with the
+-- right ON DELETE (under any name) is left alone. Before each key is added,
+-- rows that point at a missing parent are recorded in qqq_fk_quarantine and
+-- then set to NULL (nullable column) or deleted (NOT NULL column; the whole
+-- row is kept in row_data). An existing key on the column with a different
+-- ON DELETE (endpoint_secret and subscription -> endpoint were NO ACTION) is
+-- replaced by the named one.
+--
+-- Order matters: deliveries of a missing endpoint are removed before their
+-- attempts are checked.
+
+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);
+
+CREATE OR REPLACE FUNCTION pg_temp.qqq_add_fk(p_table TEXT, p_column TEXT, p_parent TEXT, p_on_delete TEXT) RETURNS VOID LANGUAGE plpgsql AS
+$fn$
+DECLARE
+   v_name      TEXT    := 'fk_' || p_table || '_' || p_column;
+   v_rule      "char"  := CASE p_on_delete WHEN 'CASCADE' THEN 'c' WHEN 'SET NULL' THEN 'n' WHEN 'RESTRICT' THEN 'r' ELSE 'a' END;
+   v_nullable  BOOLEAN;
+   v_changeset TEXT    := 'qbit-webhooks:webhooks-foreign-keys-upgrade.sql';
+   v_old       RECORD;
+BEGIN
+   IF EXISTS (SELECT 1 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 = p_table::regclass AND cardinality(c.conkey) = 1 AND a.attname = p_column
+                AND c.confrelid = p_parent::regclass AND c.confdeltype = v_rule) THEN
+      RETURN;
+   END IF;
+
+   FOR v_old IN SELECT c.conname 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 = p_table::regclass AND cardinality(c.conkey) = 1 AND a.attname = p_column LOOP
+      EXECUTE format('ALTER TABLE %I DROP CONSTRAINT %I', p_table, v_old.conname);
+   END LOOP;
+
+   SELECT is_nullable = 'YES' INTO v_nullable FROM information_schema.columns
+   WHERE table_schema = current_schema() AND table_name = p_table AND column_name = p_column;
+
+   IF v_nullable THEN
+      EXECUTE format('INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, changeset) '
+         || 'SELECT %L, %L, t.id::TEXT, t.%I::TEXT, ''NULLED'', %L FROM %I t WHERE t.%I IS NOT NULL AND NOT EXISTS (SELECT 1 FROM %I p WHERE p.id = t.%I)',
+         p_table, p_column, p_column, v_changeset, p_table, p_column, p_parent, p_column);
+      EXECUTE format('UPDATE %I t SET %I = NULL WHERE t.%I IS NOT NULL AND NOT EXISTS (SELECT 1 FROM %I p WHERE p.id = t.%I)',
+         p_table, p_column, p_column, p_parent, p_column);
+   ELSE
+      EXECUTE format('INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) '
+         || 'SELECT %L, %L, t.id::TEXT, t.%I::TEXT, ''DELETED'', row_to_json(t)::TEXT, %L FROM %I t WHERE NOT EXISTS (SELECT 1 FROM %I p WHERE p.id = t.%I)',
+         p_table, p_column, p_column, v_changeset, p_table, p_parent, p_column);
+      EXECUTE format('DELETE FROM %I t WHERE NOT EXISTS (SELECT 1 FROM %I p WHERE p.id = t.%I)', p_table, p_parent, p_column);
+   END IF;
+
+   EXECUTE format('ALTER TABLE %I ADD CONSTRAINT %I FOREIGN KEY (%I) REFERENCES %I (id) ON DELETE %s', p_table, v_name, p_column, p_parent, p_on_delete);
+END
+$fn$;
+
+SELECT pg_temp.qqq_add_fk('webhooks_endpoint_secret', 'endpoint_id', 'webhooks_endpoint', 'CASCADE');
+SELECT pg_temp.qqq_add_fk('webhooks_subscription', 'endpoint_id', 'webhooks_endpoint', 'CASCADE');
+SELECT pg_temp.qqq_add_fk('webhooks_delivery', 'endpoint_id', 'webhooks_endpoint', 'RESTRICT');
+SELECT pg_temp.qqq_add_fk('webhooks_delivery', 'subscription_id', 'webhooks_subscription', 'SET NULL');
+SELECT pg_temp.qqq_add_fk('webhooks_delivery_attempt', 'delivery_id', 'webhooks_delivery', 'CASCADE');
+SELECT pg_temp.qqq_add_fk('webhooks_delivery_attempt', 'endpoint_id', 'webhooks_endpoint', 'RESTRICT');
+
+CREATE INDEX IF NOT EXISTS webhooks_delivery_subscription_ix ON webhooks_delivery (subscription_id);
+
+COMMIT;

docs/ddl/webhooks-postgres.sql

@@ -7,6 +7,18 @@
 -- an event being queued twice, and (delivery_id, attempt_number) on
 -- webhooks_delivery_attempt is the claim that stops two replicas sending the
 -- same attempt.
+--
+-- Foreign keys (named fk_<table>_<column>):
+--   endpoint_secret.endpoint_id, subscription.endpoint_id -> endpoint  CASCADE:
+--     an endpoint's secrets and subscriptions are its own configuration, so
+--     deleting the endpoint (webhooks.admin) removes them.
+--   delivery.endpoint_id, delivery_attempt.endpoint_id -> endpoint      RESTRICT:
+--     delivery history keeps its endpoint; cancel and Delete Deliveries first.
+--   delivery.subscription_id -> subscription                           SET NULL:
+--     a delivery outlives the subscription that queued it.
+--   delivery_attempt.delivery_id -> delivery                           CASCADE:
+--     attempts belong to their delivery (Retention deletes both).
+-- Existing databases: run webhooks-foreign-keys-upgrade.sql.
 
 CREATE TABLE webhooks_endpoint
 (
@@ -40,7 +52,7 @@ CREATE TABLE webhooks_endpoint_secret
    id SERIAL PRIMARY KEY,
    create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    modify_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
-   endpoint_id INTEGER NOT NULL REFERENCES webhooks_endpoint (id),
+   endpoint_id INTEGER NOT NULL CONSTRAINT fk_webhooks_endpoint_secret_endpoint_id REFERENCES webhooks_endpoint (id) ON DELETE CASCADE,
    status VARCHAR(20) DEFAULT 'ACTIVE',
    source VARCHAR(20) NOT NULL,
    secret_ref VARCHAR(100),
@@ -59,7 +71,7 @@ CREATE TABLE webhooks_subscription
    id SERIAL PRIMARY KEY,
    create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    modify_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
-   endpoint_id INTEGER NOT NULL REFERENCES webhooks_endpoint (id),
+   endpoint_id INTEGER NOT NULL CONSTRAINT fk_webhooks_subscription_endpoint_id REFERENCES webhooks_endpoint (id) ON DELETE CASCADE,
    event_type_name VARCHAR(200) NOT NULL,
    active_status VARCHAR(20) DEFAULT 'ACTIVE',
    description VARCHAR(250),
@@ -83,8 +95,8 @@ CREATE TABLE webhooks_delivery
    modify_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    event_id VARCHAR(40) NOT NULL,
    event_type_name VARCHAR(200) NOT NULL,
-   endpoint_id INTEGER NOT NULL,
-   subscription_id INTEGER,
+   endpoint_id INTEGER NOT NULL CONSTRAINT fk_webhooks_delivery_endpoint_id REFERENCES webhooks_endpoint (id) ON DELETE RESTRICT,
+   subscription_id INTEGER CONSTRAINT fk_webhooks_delivery_subscription_id REFERENCES webhooks_subscription (id) ON DELETE SET NULL,
    source VARCHAR(20),
    source_table VARCHAR(100),
    source_record_id VARCHAR(100),
@@ -113,14 +125,16 @@ CREATE INDEX webhooks_delivery_status_ix ON webhooks_delivery (status, modify_da
 
 CREATE INDEX webhooks_delivery_delivered_ix ON webhooks_delivery (delivered_date);
 
+CREATE INDEX webhooks_delivery_subscription_ix ON webhooks_delivery (subscription_id);
+
 
 CREATE TABLE webhooks_delivery_attempt
 (
    id SERIAL PRIMARY KEY,
    create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    modify_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
-   delivery_id INTEGER NOT NULL,
-   endpoint_id INTEGER NOT NULL,
+   delivery_id INTEGER NOT NULL CONSTRAINT fk_webhooks_delivery_attempt_delivery_id REFERENCES webhooks_delivery (id) ON DELETE CASCADE,
+   endpoint_id INTEGER NOT NULL CONSTRAINT fk_webhooks_delivery_attempt_endpoint_id REFERENCES webhooks_endpoint (id) ON DELETE RESTRICT,
    attempt_number INTEGER NOT NULL,
    outcome VARCHAR(20) NOT NULL,
    claimed_by VARCHAR(100),

pom.xml

@@ -115,6 +115,19 @@
          <version>${h2.version}</version>
          <scope>test</scope>
       </dependency>
+      <!-- the foreign key guardrail test: docs/ddl on a real PostgreSQL (src/test only) -->
+      <dependency>
+         <groupId>org.postgresql</groupId>
+         <artifactId>postgresql</artifactId>
+         <version>42.7.9</version>
+         <scope>test</scope>
+      </dependency>
+      <dependency>
+         <groupId>org.testcontainers</groupId>
+         <artifactId>testcontainers-postgresql</artifactId>
+         <version>2.0.3</version>
+         <scope>test</scope>
+      </dependency>
       <dependency>
          <groupId>org.junit.jupiter</groupId>
          <artifactId>junit-jupiter</artifactId>

src/main/java/com/kingsrook/qbits/webhooks/WebhooksQBitProducer.java

@@ -28,6 +28,7 @@ import java.util.Map;
 import com.kingsrook.qbits.webhooks.app.WebhooksApp;
 import com.kingsrook.qbits.webhooks.events.WebhooksProcessListener;
 import com.kingsrook.qbits.webhooks.events.WebhooksRecordChangeListener;
+import com.kingsrook.qbits.webhooks.metadata.WebhooksHelp;
 import com.kingsrook.qbits.webhooks.metadata.WebhooksTableCustomizers;
 import com.kingsrook.qbits.webhooks.metadata.WebhooksTables;
 import com.kingsrook.qbits.webhooks.processes.WebhooksProcesses;
@@ -209,6 +210,7 @@ public class WebhooksQBitProducer implements QBitMetaDataProducer<WebhooksQBitCo
       {
          qInstance.withProcessLifecycleListener(new QCodeReference(WebhooksProcessListener.class));
       }
+      WebhooksHelp.apply(qInstance, qBitConfig);
       return (new MetaDataProducerMultiOutput());
    }
 

src/main/java/com/kingsrook/qbits/webhooks/app/WebhooksHomeData.java

@@ -72,6 +72,7 @@ public final class WebhooksHomeData
    private final List<QRecord> attention   = new ArrayList<>();
    private final List<String>  attentionWhy = new ArrayList<>();
    private final List<QRecord> recentDeadLetters = new ArrayList<>();
+   private List<WebhooksSetupStatus.Step> setup = new ArrayList<>();
 
 
 
@@ -150,6 +151,7 @@ public final class WebhooksHomeData
 
       data.recentDeadLetters.addAll(query(WebhooksTables.DELIVERY, new QQueryFilter(new QFilterCriteria("status", QCriteriaOperator.EQUALS, DeliveryStatus.DEAD_LETTER.name()))
          .withOrderBy(new QFilterOrderBy("id", false)).withLimit(5)));
+      data.setup = WebhooksSetupStatus.load();
       return (data);
    }
 
@@ -346,4 +348,15 @@ public final class WebhooksHomeData
    {
       return (recentDeadLetters);
    }
+
+
+
+
+   /*******************************************************************************
+    ** The setup steps (state and next step of each thing an admin configures).
+    *******************************************************************************/
+   public List<WebhooksSetupStatus.Step> getSetup()
+   {
+      return (setup);
+   }
 }

src/main/java/com/kingsrook/qbits/webhooks/app/WebhooksHomeView.java

@@ -85,6 +85,7 @@ public final class WebhooksHomeView
    public static String render(WebhooksHomeData data, String tablePrefix)
    {
       StringBuilder html = new StringBuilder("<div style=\"" + HOME + "\">");
+      setup(html, data);
       kpis(html, data, tablePrefix);
       volume(html, data);
       attention(html, data, tablePrefix);
@@ -94,6 +95,24 @@ public final class WebhooksHomeView
 
 
 
+   /*******************************************************************************
+    ** The Setup card: each thing to configure, its state and the next step.
+    *******************************************************************************/
+   private static void setup(StringBuilder html, WebhooksHomeData data)
+   {
+      html.append("<div style=\"").append(CARD).append("\"><h3 style=\"").append(TITLE).append("\">Setup</h3>");
+      for(WebhooksSetupStatus.Step step : data.getSetup())
+      {
+         String label = step.href() == null ? esc(step.label()) : "<a href=\"" + esc(step.href()) + "\">" + esc(step.label()) + "</a>";
+         String state = WebhooksSetupStatus.NEEDS_SETUP.equals(step.state()) ? "<span style=\"color:" + FAIL_COLOR + "\">" + esc(step.state()) + "</span>"
+            : WebhooksSetupStatus.SET_UP.equals(step.state()) ? "<span style=\"color:" + OK_COLOR + "\">" + esc(step.state()) + "</span>" : esc(step.state());
+         html.append("<div style=\"").append(ROW).append("\">").append(cell("Step", label)).append(cell("State", state)).append(cell("Next step", esc(step.next()))).append("</div>");
+      }
+      html.append("</div>");
+   }
+
+
+
    /*******************************************************************************
     ** The KPI tiles.
     *******************************************************************************/

src/main/java/com/kingsrook/qbits/webhooks/app/WebhooksSetupStatus.java

@@ -0,0 +1,146 @@
+/*
+ * 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.webhooks.app;
+
+
+import java.util.ArrayList;
+import java.util.List;
+import com.kingsrook.qbits.webhooks.WebhooksQBitConfig;
+import com.kingsrook.qbits.webhooks.WebhooksRuntime;
+import com.kingsrook.qbits.webhooks.metadata.WebhooksEnums.ActiveStatus;
+import com.kingsrook.qbits.webhooks.metadata.WebhooksTables;
+import com.kingsrook.qbits.webhooks.processes.WebhooksProcesses;
+import com.kingsrook.qbits.webhooks.registry.WebhookEventType;
+import com.kingsrook.qbits.webhooks.registry.WebhooksRegistry;
+import com.kingsrook.qbits.webhooks.secrets.EndpointSecrets;
+import com.kingsrook.qqq.backend.core.actions.tables.CountAction;
+import com.kingsrook.qqq.backend.core.actions.tables.QueryAction;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.exceptions.QException;
+import com.kingsrook.qqq.backend.core.model.actions.tables.count.CountInput;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QueryInput;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+
+
+/*******************************************************************************
+ ** The Setup card on the Webhooks home: each thing an administrator configures
+ ** (event types, encryption key, endpoints, secrets, subscriptions), its state
+ ** and the next step. Nothing here is seeded: every step is the owner's choice.
+ *******************************************************************************/
+public final class WebhooksSetupStatus
+{
+   public static final String SET_UP      = "Set up";
+   public static final String NEEDS_SETUP = "Needs setup";
+   public static final String OPTIONAL    = "Optional";
+
+
+
+   /*******************************************************************************
+    ** One line of the card.
+    *******************************************************************************/
+   public record Step(String label, String state, String next, String href)
+   {
+   }
+
+
+
+   /*******************************************************************************
+    ** Utility class.
+    *******************************************************************************/
+   private WebhooksSetupStatus()
+   {
+   }
+
+
+
+   /*******************************************************************************
+    ** Read the current state, as the viewer.
+    *******************************************************************************/
+   public static List<Step> load() throws QException
+   {
+      WebhooksQBitConfig config     = WebhooksRuntime.config();
+      int                eventTypes = 0;
+      for(WebhookEventType type : WebhooksRegistry.of(QContext.getQInstance()).getAll())
+      {
+         if(!WebhooksProcesses.TEST_EVENT_TYPE.equals(type.getName()))
+         {
+            eventTypes++;
+         }
+      }
+
+      int          endpoints     = count(WebhooksTables.ENDPOINT);
+      int          subscriptions = count(WebhooksTables.SUBSCRIPTION);
+      int blocked = 0;
+      for(QRecord endpoint : new QueryAction().execute(new QueryInput(WebhooksTables.ENDPOINT)).getRecords())
+      {
+         ///////////////////////////////////////////////////////////////////////////////////
+         // exactly DeliveryEngine's test: active, signing required, and no usable secret //
+         ///////////////////////////////////////////////////////////////////////////////////
+         if(ActiveStatus.ACTIVE.name().equals(endpoint.getValueString("activeStatus")) && !Boolean.TRUE.equals(endpoint.getValueBoolean("allowUnsigned"))
+            && EndpointSecrets.signingKeys(endpoint.getValueInteger("id")).isEmpty())
+         {
+            blocked++;
+         }
+      }
+      return (steps(config.getTablePathPrefix(), config.getEncryptionKeyEnvName(), eventTypes, EndpointSecrets.canGenerate(), endpoints, blocked, subscriptions));
+   }
+
+
+
+   /*******************************************************************************
+    ** The steps, from the numbers (pure, for tests).
+    *******************************************************************************/
+   static List<Step> steps(String prefix, String keyEnvName, int eventTypes, boolean canGenerate, int endpoints, int blockedEndpoints, int subscriptions)
+   {
+      String     endpointHref     = prefix + "/" + WebhooksTables.ENDPOINT;
+      String     subscriptionHref = prefix + "/" + WebhooksTables.SUBSCRIPTION;
+      List<Step> steps            = new ArrayList<>();
+      steps.add(eventTypes > 0
+         ? new Step("Event types", SET_UP, eventTypes + " available to subscribe to (from the code-side registry)", subscriptionHref)
+         : new Step("Event types", NEEDS_SETUP, "The host lists tables or processes that raise events in the qbit config (tableEventSources, processEventSources), or fires custom events", null));
+      steps.add(canGenerate
+         ? new Step("Encryption key", SET_UP, "Generated signing secrets can be stored encrypted", null)
+         : new Step("Encryption key", OPTIONAL, "Set " + keyEnvName + " to generate secrets here, or use environment-variable secrets", null));
+      steps.add(endpoints > 0
+         ? new Step("Endpoints", SET_UP, endpoints + (endpoints == 1 ? " endpoint" : " endpoints"), endpointHref)
+         : new Step("Endpoints", NEEDS_SETUP, "Add an endpoint: the URL that receives events", endpointHref));
+      steps.add(endpoints > 0 && blockedEndpoints == 0
+         ? new Step("Signing secrets", SET_UP, "Every endpoint signs its deliveries", endpointHref)
+         : new Step("Signing secrets", NEEDS_SETUP, endpoints == 0 ? "Add an endpoint first, then give it a secret"
+            : blockedEndpoints + " active endpoint(s) have no usable signing secret, so their deliveries wait: open the endpoint and add one", endpointHref));
+      steps.add(subscriptions > 0
+         ? new Step("Subscriptions", SET_UP, subscriptions + (subscriptions == 1 ? " subscription" : " subscriptions"), subscriptionHref)
+         : new Step("Subscriptions", NEEDS_SETUP, "Subscribe an endpoint to an event type, or nothing is ever sent", subscriptionHref));
+      return (steps);
+   }
+
+
+
+   /*******************************************************************************
+    ** Count as the viewer.
+    *******************************************************************************/
+   private static int count(String table) throws QException
+   {
+      Integer count = new CountAction().execute(new CountInput(table)).getCount();
+      return (count == null ? 0 : count);
+   }
+
+}

src/main/java/com/kingsrook/qbits/webhooks/metadata/WebhooksHelp.java

@@ -0,0 +1,132 @@
+/*
+ * 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.webhooks.metadata;
+
+
+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.webhooks.WebhooksQBitConfig;
+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-webhooks.yaml (the QQQ help-bundle format) to the
+ ** Webhooks app and its webhooks_* tables: what each table is for and how its
+ ** rows get created, shown on the table screen and when it is empty.
+ ** STAND-IN(core=QRun-IO/qqq#1198, removeBy=4.1.0-RC.8): replace with HelpBundles.register once the pinned
+ ** QQQ release includes it; the bundle file stays the same.
+ *******************************************************************************/
+public final class WebhooksHelp
+{
+   public static final String RESOURCE = "/help/qbit-webhooks.yaml";
+   public static final String SLOT     = "help";
+
+
+
+   /*******************************************************************************
+    ** Utility class.
+    *******************************************************************************/
+   private WebhooksHelp()
+   {
+   }
+
+
+
+   /*******************************************************************************
+    ** The bundle's entries by key, validated (fails startup if malformed).
+    *******************************************************************************/
+   public static Map<String, JsonNode> entries() throws QException
+   {
+      try(InputStream stream = WebhooksHelp.class.getResourceAsStream(RESOURCE))
+      {
+         if(stream == null)
+         {
+            throw (new IOException("missing resource"));
+         }
+         JsonNode bundle = new ObjectMapper(new YAMLFactory()).readTree(stream);
+         if(!"qbit-webhooks".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 webhooks_* table in the instance.
+    ** 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, WebhooksQBitConfig config) throws QException
+   {
+      Map<String, JsonNode> entries = entries();
+      for(QTableMetaData table : qInstance.getTables().values())
+      {
+         JsonNode entry = entries.get("table:" + table.getName() + ";slot:" + SLOT);
+         if(entry != null && (table.getHelpContent() == null || !table.getHelpContent().containsKey(SLOT)))
+         {
+            table.withHelpContent(SLOT, content(entry));
+         }
+      }
+      QAppMetaData app      = qInstance.getApp(config.getAppName());
+      JsonNode     appEntry = entries.get("app:webhooks;slot:" + SLOT);
+      if(app != null && appEntry != null && (app.getHelpContent() == null || !app.getHelpContent().containsKey(SLOT)))
+      {
+         app.withHelpContent(SLOT, content(appEntry));
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** 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/resources/help/qbit-webhooks.yaml

@@ -0,0 +1,38 @@
+bundle: qbit-webhooks
+version: 1
+layer: QBIT
+# Help for the Webhooks app and every webhooks_* 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. WebhooksHelp applies it until
+# the core HelpBundles registry ships in a QQQ release (a stand-in until that ships).
+# Summaries stay under 120 characters (WebhooksTablesGuardrailTest).
+entries:
+  - key: app:webhooks;slot:help
+    summary: "Send signed HTTP notifications to other systems when things happen in this application."
+    content: |
+      The Setup card on the home lists each step with its state and the next step. Nothing is seeded: you choose where events go. Add an endpoint (a URL), give it a signing secret, then subscribe it to event types. The event types you can pick come from the code: the host lists the tables and processes that raise events in the qbit's configuration (`tableEventSources`, `processEventSources`, ESB topics) and can fire custom ones, and the qbit adds a Test event.
+
+  - key: table:webhooks_endpoint;slot:help
+    summary: "A receiver URL that gets webhooks: its status, health, rate limit, timeout and unsigned or internal-network switches."
+    content: |
+      **How rows get created:** an administrator adds one with New (a name and an `https` URL). Then add a signing secret and a subscription. Send Test Event checks it end to end. Health moves on its own (Healthy, Probation, Unhealthy) as deliveries succeed or fail, and an endpoint that keeps failing on probation is disabled.
+
+  - key: table:webhooks_endpoint_secret;slot:help
+    summary: "The signing secrets for an endpoint: generated and encrypted, or the name of an environment variable."
+    content: |
+      **How rows get created:** an administrator adds one on the endpoint, either a generated secret (needs the encryption key variable, `WEBHOOKS_SECRET_KEY` by default) or the name of an environment variable that holds it. The Rotate Signing Secret action adds a new one and keeps the old one signing until Signs Until, so receivers can switch over. Secrets past Signs Until stop signing and are retired by the maintenance job. Secret values are never shown here, only fingerprints.
+
+  - key: table:webhooks_subscription;slot:help
+    summary: "Which events an endpoint receives: event type, optional record filter, detail level and field limits."
+    content: |
+      **How rows get created:** an administrator adds one on the endpoint or with New: pick the endpoint and an event type. The event types listed come from the code-side registry: `<table>.inserted|updated|deleted` for each table in `tableEventSources`, `process.<name>.completed|failed` for `processEventSources`, ESB topics, custom events the host fires, and the Test event. A record filter limits which records fire it.
+
+  - key: table:webhooks_delivery;slot:help
+    summary: "One row per event per endpoint: the stored payload, status, attempts, next attempt and last error."
+    content: |
+      **How rows get created:** written only by the qbit, when an event matches a subscription. Empty means no subscribed event has happened yet. Use Send Now, Cancel Delivery or Replay on dead letters to act on them; the Deliver Webhooks job sends them and retries on a schedule.
+
+  - key: table:webhooks_delivery_attempt;slot:help
+    summary: "One row per HTTP attempt to an endpoint: outcome, status code, duration, response excerpt and error."
+    content: |
+      **How rows get created:** written only by the qbit each time it tries to send a delivery (the claim that stops two replicas sending the same attempt). Empty means nothing has been sent yet. Old rows are purged by the retention job.

src/test/java/com/kingsrook/qbits/webhooks/ForeignKeyGuardrailTest.java

@@ -0,0 +1,431 @@
+/*
+ * 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.webhooks;
+
+
+import java.nio.charset.StandardCharsets;
+import java.nio.file.Files;
+import java.nio.file.Path;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map;
+import java.util.TreeMap;
+import java.util.TreeSet;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
+import com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldMetaData;
+import com.kingsrook.qqq.backend.core.model.metadata.joins.JoinOn;
+import com.kingsrook.qqq.backend.core.model.metadata.joins.QJoinMetaData;
+import com.kingsrook.qqq.backend.core.model.metadata.possiblevalues.QPossibleValueSource;
+import com.kingsrook.qqq.backend.core.model.metadata.possiblevalues.QPossibleValueSourceType;
+import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
+import org.junit.jupiter.api.AfterAll;
+import org.junit.jupiter.api.AfterEach;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Assumptions;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+import org.testcontainers.DockerClientFactory;
+import org.testcontainers.postgresql.PostgreSQLContainer;
+import static org.assertj.core.api.Assertions.assertThat;
+import static org.assertj.core.api.Assertions.assertThatThrownBy;
+
+
+/*******************************************************************************
+ ** Referential integrity guardrail: docs/ddl/webhooks-postgres.sql, applied to
+ ** a real PostgreSQL, has a foreign key for every join and every table
+ ** possible-value field between the qbit's tables (or the relation is in
+ ** {@link #EXEMPT} with a reason), each with the intended ON DELETE. A new
+ ** table or join without its key fails here. The upgrade script is checked
+ ** too: it quarantines orphans, adds the same keys, and is idempotent.
+ *******************************************************************************/
+class ForeignKeyGuardrailTest
+{
+   private static final Path DDL     = Path.of("docs/ddl/webhooks-postgres.sql");
+   private static final Path UPGRADE = Path.of("docs/ddl/webhooks-foreign-keys-upgrade.sql");
+
+   /////////////////////////////////////////////////////////////////////////////
+   // child_table.child_column -> parent_table : ON DELETE (pg confdeltype)    //
+   // c = CASCADE, n = SET NULL, r = RESTRICT                                  //
+   /////////////////////////////////////////////////////////////////////////////
+   private static final Map<String, String> EXPECTED = new TreeMap<>(Map.of(
+      "webhooks_endpoint_secret.endpoint_id -> webhooks_endpoint", "c",
+      "webhooks_subscription.endpoint_id -> webhooks_endpoint", "c",
+      "webhooks_delivery.endpoint_id -> webhooks_endpoint", "r",
+      "webhooks_delivery.subscription_id -> webhooks_subscription", "n",
+      "webhooks_delivery_attempt.delivery_id -> webhooks_delivery", "c",
+      "webhooks_delivery_attempt.endpoint_id -> webhooks_endpoint", "r"));
+
+   /////////////////////////////////////////////////////////////////////////////
+   // relations that are deliberately not foreign keys, with the reason       //
+   /////////////////////////////////////////////////////////////////////////////
+   private static final Map<String, String> EXEMPT = Map.of();
+
+   private static PostgreSQLContainer postgres;
+
+
+
+   /*******************************************************************************
+    ** Start PostgreSQL once (a missing Docker fails in CI, skips locally).
+    *******************************************************************************/
+   @BeforeAll
+   static void startPostgres()
+   {
+      boolean docker;
+      try
+      {
+         docker = DockerClientFactory.instance().isDockerAvailable();
+      }
+      catch(RuntimeException e)
+      {
+         docker = false;
+      }
+      if(!docker && System.getenv("CI") != null)
+      {
+         Assertions.fail("Docker is required for the foreign key guardrail in CI");
+      }
+      Assumptions.assumeTrue(docker, "Docker is not available");
+      postgres = new PostgreSQLContainer("postgres:17-alpine").withDatabaseName("webhooks").withUsername("qqq").withPassword("qqq");
+      postgres.start();
+   }
+
+
+
+   /*******************************************************************************
+    ** Stop PostgreSQL.
+    *******************************************************************************/
+   @AfterAll
+   static void stopPostgres()
+   {
+      if(postgres != null)
+      {
+         postgres.stop();
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** A clean schema from the reference DDL.
+    *******************************************************************************/
+   @BeforeEach
+   void setUp() throws Exception
+   {
+      try(Connection c = connect(); Statement st = c.createStatement())
+      {
+         st.execute("DROP SCHEMA public CASCADE");
+         st.execute("CREATE SCHEMA public");
+         st.execute(Files.readString(DDL, StandardCharsets.UTF_8));
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Clear the QContext the metadata setup opened.
+    *******************************************************************************/
+   @AfterEach
+   void tearDown()
+   {
+      QContext.clear();
+   }
+
+
+
+   /*******************************************************************************
+    ** Every declared relation is a foreign key (or exempt), with the intended
+    ** ON DELETE, and every foreign key column leads an index.
+    *******************************************************************************/
+   @Test
+   void everyRelationHasAForeignKey() throws Exception
+   {
+      TreeSet<String> declared = declaredRelations(WebhooksTestSupport.setUp());
+      Map<String, String> keys = foreignKeys();
+
+      List<String> missing = new ArrayList<>();
+      for(String relation : declared)
+      {
+         if(!keys.containsKey(relation) && !EXEMPT.containsKey(relation))
+         {
+            missing.add(relation);
+         }
+      }
+      assertThat(missing).as("relations declared in metadata without a foreign key in " + DDL + " (add one, or exempt it with a reason)").isEmpty();
+      assertThat(declared).as("every expected relation is still declared").containsAll(EXPECTED.keySet());
+      assertThat(keys).as("foreign keys and their ON DELETE").isEqualTo(EXPECTED);
+      assertThat(unindexedForeignKeys()).as("foreign key columns that lead no index").isEmpty();
+   }
+
+
+
+   /*******************************************************************************
+    ** The upgrade script adds the same keys to a schema without them, recording
+    ** and removing orphans first, and is a no-op the second time.
+    *******************************************************************************/
+   @Test
+   void upgradeQuarantinesOrphansAndIsIdempotent() throws Exception
+   {
+      try(Connection c = connect(); Statement st = c.createStatement())
+      {
+         dropForeignKeys(st);
+         st.execute("""
+            INSERT INTO webhooks_endpoint (id, name, url) VALUES (1, 'one', 'https://one.example');
+            INSERT INTO webhooks_subscription (id, endpoint_id, event_type_name) VALUES (1, 1, 'person.inserted');
+            INSERT INTO webhooks_delivery (id, event_id, event_type_name, endpoint_id, subscription_id, dedup_key, status)
+               VALUES (1, 'e1', 'person.inserted', 1, 1, 'd1', 'PENDING'),
+                      (2, 'e2', 'person.inserted', 1, 99, 'd2', 'PENDING'),
+                      (3, 'e3', 'person.inserted', 98, NULL, 'd3', 'PENDING');
+            INSERT INTO webhooks_delivery_attempt (id, delivery_id, endpoint_id, attempt_number, outcome)
+               VALUES (1, 1, 1, 1, 'SUCCESS'), (2, 3, 1, 1, 'FAILURE');
+            """);
+
+         st.execute(Files.readString(UPGRADE, StandardCharsets.UTF_8));
+         assertThat(foreignKeys()).isEqualTo(EXPECTED);
+         assertThat(quarantine(st)).containsExactlyInAnyOrder(
+            "webhooks_delivery.subscription_id row 2 parent 99 NULLED",
+            "webhooks_delivery.endpoint_id row 3 parent 98 DELETED",
+            "webhooks_delivery_attempt.delivery_id row 2 parent 3 DELETED");
+         assertThat(count(st, "SELECT COUNT(*) FROM webhooks_delivery")).isEqualTo(2);
+         assertThat(count(st, "SELECT COUNT(*) FROM webhooks_delivery WHERE id = 2 AND subscription_id IS NULL")).isEqualTo(1);
+
+         st.execute(Files.readString(UPGRADE, StandardCharsets.UTF_8));
+         assertThat(foreignKeys()).isEqualTo(EXPECTED);
+         assertThat(quarantine(st)).hasSize(3);
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Deleting an endpoint takes its secrets and subscriptions with it, and is
+    ** refused while it has delivery history.
+    *******************************************************************************/
+   @Test
+   void endpointDeleteCascadesConfigurationButNotHistory() throws Exception
+   {
+      try(Connection c = connect(); Statement st = c.createStatement())
+      {
+         st.execute("""
+            INSERT INTO webhooks_endpoint (id, name, url) VALUES (1, 'one', 'https://one.example'), (2, 'two', 'https://two.example');
+            INSERT INTO webhooks_endpoint_secret (endpoint_id, source) VALUES (1, 'GENERATED'), (2, 'GENERATED');
+            INSERT INTO webhooks_subscription (id, endpoint_id, event_type_name) VALUES (1, 1, 'person.inserted'), (2, 2, 'person.inserted');
+            INSERT INTO webhooks_delivery (id, event_id, event_type_name, endpoint_id, subscription_id, dedup_key, status)
+               VALUES (1, 'e1', 'person.inserted', 2, 2, 'd1', 'DELIVERED');
+            INSERT INTO webhooks_delivery_attempt (delivery_id, endpoint_id, attempt_number, outcome) VALUES (1, 2, 1, 'SUCCESS');
+            """);
+
+         st.execute("DELETE FROM webhooks_endpoint WHERE id = 1");
+         assertThat(count(st, "SELECT COUNT(*) FROM webhooks_endpoint_secret WHERE endpoint_id = 1")).isZero();
+         assertThat(count(st, "SELECT COUNT(*) FROM webhooks_subscription WHERE endpoint_id = 1")).isZero();
+
+         assertThatThrownBy(() -> st.execute("DELETE FROM webhooks_endpoint WHERE id = 2")).isInstanceOf(SQLException.class).hasMessageContaining("fk_webhooks_delivery");
+
+         st.execute("DELETE FROM webhooks_subscription WHERE id = 2");
+         assertThat(count(st, "SELECT COUNT(*) FROM webhooks_delivery WHERE id = 1 AND subscription_id IS NULL")).isEqualTo(1);
+
+         st.execute("DELETE FROM webhooks_delivery WHERE id = 1");
+         assertThat(count(st, "SELECT COUNT(*) FROM webhooks_delivery_attempt")).isZero();
+         st.execute("DELETE FROM webhooks_endpoint WHERE id = 2");
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** child_table.child_column -> parent_table for every join and table PVS
+    ** field between two of the qbit's tables.
+    *******************************************************************************/
+   static TreeSet<String> declaredRelations(QInstance instance)
+   {
+      TreeSet<String> relations = new TreeSet<>();
+      for(QTableMetaData table : instance.getTables().values())
+      {
+         if(!isQbitTable(table))
+         {
+            continue;
+         }
+         for(QFieldMetaData field : table.getFields().values())
+         {
+            QPossibleValueSource pvs = field.getPossibleValueSourceName() == null ? null : instance.getPossibleValueSource(field.getPossibleValueSourceName());
+            if(pvs != null && pvs.getType() == QPossibleValueSourceType.TABLE && isQbitTable(instance.getTable(pvs.getTableName())))
+            {
+               relations.add(relation(table, field, instance.getTable(pvs.getTableName())));
+            }
+         }
+      }
+      for(QJoinMetaData join : instance.getJoins().values())
+      {
+         QTableMetaData left  = instance.getTable(join.getLeftTable());
+         QTableMetaData right = instance.getTable(join.getRightTable());
+         if(!isQbitTable(left) || !isQbitTable(right))
+         {
+            continue;
+         }
+         for(JoinOn on : join.getJoinOns())
+         {
+            if(on.getLeftField().equals(left.getPrimaryKeyField()))
+            {
+               relations.add(relation(right, right.getField(on.getRightField()), left));
+            }
+            else if(on.getRightField().equals(right.getPrimaryKeyField()))
+            {
+               relations.add(relation(left, left.getField(on.getLeftField()), right));
+            }
+         }
+      }
+      return (relations);
+   }
+
+
+
+   /*******************************************************************************
+    ** Whether a table is one of the qbit's.
+    *******************************************************************************/
+   private static boolean isQbitTable(QTableMetaData table)
+   {
+      return (table != null && table.getName().startsWith("webhooks_"));
+   }
+
+
+
+   /*******************************************************************************
+    ** A relation key.
+    *******************************************************************************/
+   private static String relation(QTableMetaData child, QFieldMetaData field, QTableMetaData parent)
+   {
+      String column = field.getBackendName() != null ? field.getBackendName() : field.getName();
+      return (child.getName() + "." + column + " -> " + parent.getName());
+   }
+
+
+
+   /*******************************************************************************
+    ** The single-column foreign keys in the database, with their ON DELETE.
+    *******************************************************************************/
+   private static Map<String, String> foreignKeys() throws SQLException
+   {
+      Map<String, String> keys = new TreeMap<>();
+      try(Connection c = connect(); Statement st = c.createStatement(); ResultSet rs = st.executeQuery("""
+         SELECT c.conrelid::regclass::text AS child, a.attname AS col, c.confrelid::regclass::text AS parent, c.confdeltype::text AS rule
+         FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
+         WHERE c.contype = 'f' AND cardinality(c.conkey) = 1
+         """))
+      {
+         while(rs.next())
+         {
+            keys.put(rs.getString("child") + "." + rs.getString("col") + " -> " + rs.getString("parent"), rs.getString("rule"));
+         }
+      }
+      return (keys);
+   }
+
+
+
+   /*******************************************************************************
+    ** Foreign key columns that are not the first column of any index.
+    *******************************************************************************/
+   private static List<String> unindexedForeignKeys() throws SQLException
+   {
+      List<String> list = new ArrayList<>();
+      try(Connection c = connect(); Statement st = c.createStatement(); ResultSet rs = st.executeQuery("""
+         SELECT c.conrelid::regclass::text || '.' || a.attname AS col
+         FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
+         WHERE c.contype = 'f'
+           AND NOT EXISTS (SELECT 1 FROM pg_index i WHERE i.indrelid = c.conrelid AND i.indkey[0] = c.conkey[1])
+         """))
+      {
+         while(rs.next())
+         {
+            list.add(rs.getString("col"));
+         }
+      }
+      return (list);
+   }
+
+
+
+   /*******************************************************************************
+    ** Drop every foreign key (a schema as it was before they were declared).
+    *******************************************************************************/
+   private static void dropForeignKeys(Statement st) throws SQLException
+   {
+      List<String> drops = new ArrayList<>();
+      try(ResultSet rs = st.executeQuery("SELECT conrelid::regclass::text AS t, conname FROM pg_constraint WHERE contype = 'f'"))
+      {
+         while(rs.next())
+         {
+            drops.add("ALTER TABLE " + rs.getString("t") + " DROP CONSTRAINT " + rs.getString("conname"));
+         }
+      }
+      for(String drop : drops)
+      {
+         st.execute(drop);
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** The quarantine rows, as text.
+    *******************************************************************************/
+   private static List<String> quarantine(Statement st) throws SQLException
+   {
+      List<String> rows = new ArrayList<>();
+      try(ResultSet rs = st.executeQuery("SELECT table_name, column_name, row_id, missing_parent_id, action FROM qqq_fk_quarantine ORDER BY id"))
+      {
+         while(rs.next())
+         {
+            rows.add(rs.getString(1) + "." + rs.getString(2) + " row " + rs.getString(3) + " parent " + rs.getString(4) + " " + rs.getString(5));
+         }
+      }
+      return (rows);
+   }
+
+
+
+   /*******************************************************************************
+    ** A count.
+    *******************************************************************************/
+   private static int count(Statement st, String sql) throws SQLException
+   {
+      try(ResultSet rs = st.executeQuery(sql))
+      {
+         rs.next();
+         return (rs.getInt(1));
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** A connection.
+    *******************************************************************************/
+   private static Connection connect() throws SQLException
+   {
+      return (DriverManager.getConnection(postgres.getJdbcUrl(), "qqq", "qqq"));
+   }
+}

src/test/java/com/kingsrook/qbits/webhooks/WebhooksTablesGuardrailTest.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.webhooks;
+
+
+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.kingsrook.qbits.webhooks.metadata.WebhooksHelp;
+import com.kingsrook.qbits.webhooks.metadata.WebhooksTables;
+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 webhooks_* table:
+ ** - runtime code (not just metadata or the home screen) reads or writes it;
+ ** - it is either seeded by shipped defaults, or listed here with why it starts
+ **   empty (nothing in this qbit is seeded: endpoints, secrets and
+ **   subscriptions are the owner's choices, deliveries and attempts are logs);
+ ** - it has bundle help that says 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 WebhooksTablesGuardrailTest
+{
+   private static final Path MAIN = Path.of("src", "main", "java", "com", "kingsrook", "qbits", "webhooks");
+
+   /*******************************************************************************
+    ** Tables that legitimately start empty, and where their rows come from.
+    *******************************************************************************/
+   static final Map<String, String> STARTS_EMPTY = Map.of(
+      WebhooksTables.ENDPOINT, "host-specific: an administrator adds each receiver URL",
+      WebhooksTables.SECRET, "secrets are never seeded: an administrator generates one or names an environment variable",
+      WebhooksTables.SUBSCRIPTION, "an administrator picks the event types an endpoint receives",
+      WebhooksTables.DELIVERY, "log: written when an event matches a subscription",
+      WebhooksTables.ATTEMPT, "log: written on every HTTP attempt");
+
+
+
+   /*******************************************************************************
+    ** Every table is read or written by runtime code (delivery, events, secrets,
+    ** processes), not just declared in metadata or shown on the home.
+    *******************************************************************************/
+   @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("metadata")) && !p.startsWith(MAIN.resolve("app")))
+            .map(WebhooksTablesGuardrailTest::read).toList();
+      }
+      for(String table : WebhooksTables.ALL)
+      {
+         String constant = "WebhooksTables." + constantFor(table);
+         assertThat(runtimeSource.stream().anyMatch(source -> source.contains(constant)))
+            .as(table + " must be read or written by runtime code, not only declared").isTrue();
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Every table is seeded (none are) or explained.
+    *******************************************************************************/
+   @Test
+   void everyTableIsSeededOrExplained()
+   {
+      assertThat(STARTS_EMPTY.keySet()).containsExactlyInAnyOrderElementsOf(WebhooksTables.ALL);
+   }
+
+
+
+   /*******************************************************************************
+    ** Every table and the app have bundle help that says how rows get created.
+    *******************************************************************************/
+   @Test
+   void everyTableHasEmptyStateHelp() throws Exception
+   {
+      QInstance instance = WebhooksTestSupport.setUp();
+      assertThat(WebhooksHelp.entries()).allSatisfy((key, entry) -> assertThat(entry.path("summary").asText().length()).as(key).isLessThanOrEqualTo(120));
+      for(String table : WebhooksTables.ALL)
+      {
+         List<QHelpContent> help = instance.getTable(table).getHelpContent() == null ? null : instance.getTable(table).getHelpContent().get(WebhooksHelp.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("webhooks").getHelpContent()).containsKey(WebhooksHelp.SLOT);
+      assertThat(WebhooksHelp.entries().get("table:webhooks_subscription;slot:help").path("content").asText()).contains("registry");
+      assertThat(WebhooksHelp.entries().keySet()).allMatch(key -> !key.startsWith("table:") || instance.getTable(key.substring(6, key.indexOf(';'))) != null,
+         "help names only real tables");
+   }
+
+
+
+   /*******************************************************************************
+    ** The WebhooksTables constant name of a table.
+    *******************************************************************************/
+   private static String constantFor(String table)
+   {
+      return (switch(table)
+      {
+         case WebhooksTables.ENDPOINT -> "ENDPOINT";
+         case WebhooksTables.SECRET -> "SECRET";
+         case WebhooksTables.SUBSCRIPTION -> "SUBSCRIPTION";
+         case WebhooksTables.DELIVERY -> "DELIVERY";
+         case WebhooksTables.ATTEMPT -> "ATTEMPT";
+         default -> throw new IllegalStateException("no constant for " + table);
+      });
+   }
+
+
+
+   /*******************************************************************************
+    ** Read a file.
+    *******************************************************************************/
+   private static String read(Path path)
+   {
+      try
+      {
+         return (Files.readString(path, StandardCharsets.UTF_8));
+      }
+      catch(Exception e)
+      {
+         throw (new IllegalStateException(e));
+      }
+   }
+
+
+   /*******************************************************************************
+    ** A host's own help in the slot is kept; the bundle only fills empty slots.
+    *******************************************************************************/
+   @Test
+   void hostHelpIsKept() throws Exception
+   {
+      QInstance instance = WebhooksTestSupport.setUp();
+      instance.getTable(WebhooksTables.ENDPOINT).withHelpContent(WebhooksHelp.SLOT, new QHelpContent().withContentAsMarkdown("host text"));
+      instance.getApp("webhooks").withHelpContent(WebhooksHelp.SLOT, new QHelpContent().withContentAsMarkdown("host app text"));
+      WebhooksHelp.apply(instance, WebhooksRuntime.config(instance));
+      assertThat(instance.getTable(WebhooksTables.ENDPOINT).getHelpContent().get(WebhooksHelp.SLOT)).singleElement().extracting(QHelpContent::getContent).isEqualTo("host text");
+      assertThat(instance.getApp("webhooks").getHelpContent().get(WebhooksHelp.SLOT)).singleElement().extracting(QHelpContent::getContent).isEqualTo("host app text");
+   }
+
+}

src/test/java/com/kingsrook/qbits/webhooks/app/WebhooksHomeTest.java

@@ -90,7 +90,7 @@ class WebhooksHomeTest
    void emptyHome() throws Exception
    {
       String html = home();
-      assertThat(html).contains("No endpoints yet").contains("/admin/webhooks/webhooks_endpoint").contains("No delivery attempts in the last 24 hours")
+      assertThat(html).contains("Setup").contains("Needs setup").contains("No endpoints yet").contains("/admin/webhooks/webhooks_endpoint").contains("No delivery attempts in the last 24 hours")
          .contains("No dead letters").contains("None yet");
    }
 

src/test/java/com/kingsrook/qbits/webhooks/app/WebhooksSetupStatusTest.java

@@ -0,0 +1,132 @@
+/*
+ * 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.webhooks.app;
+
+
+import java.time.Duration;
+import java.util.List;
+import com.kingsrook.qbits.webhooks.WebhooksTestSupport;
+import com.kingsrook.qbits.webhooks.app.WebhooksSetupStatus.Step;
+import com.kingsrook.qbits.webhooks.metadata.WebhooksTables;
+import com.kingsrook.qbits.webhooks.secrets.EndpointSecrets;
+import com.kingsrook.qbits.webhooks.support.SystemWrites;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+import org.junit.jupiter.api.Test;
+import static org.assertj.core.api.Assertions.assertThat;
+
+
+/*******************************************************************************
+ ** The Setup card: states follow what is configured, and it shows on the home.
+ *******************************************************************************/
+class WebhooksSetupStatusTest
+{
+   /*******************************************************************************
+    ** A fresh install: everything the administrator chooses needs setup, each
+    ** with a next step.
+    *******************************************************************************/
+   @Test
+   void freshInstallNeedsSetup()
+   {
+      List<Step> steps = WebhooksSetupStatus.steps("/admin/webhooks", "WEBHOOKS_SECRET_KEY", 0, false, 0, 0, 0);
+      assertThat(steps).extracting(Step::label).containsExactly("Event types", "Encryption key", "Endpoints", "Signing secrets", "Subscriptions");
+      assertThat(steps).extracting(Step::state).containsExactly("Needs setup", "Optional", "Needs setup", "Needs setup", "Needs setup");
+      assertThat(steps).allSatisfy(step -> assertThat(step.next()).isNotBlank());
+      assertThat(steps.get(1).next()).contains("WEBHOOKS_SECRET_KEY");
+   }
+
+
+
+   /*******************************************************************************
+    ** A secretless endpoint is called out; a complete setup is all set up.
+    *******************************************************************************/
+   @Test
+   void progressIsReflected()
+   {
+      List<Step> partial = WebhooksSetupStatus.steps("/p", "K", 3, true, 2, 1, 0);
+      assertThat(partial).extracting(Step::state).containsExactly("Set up", "Set up", "Set up", "Needs setup", "Needs setup");
+      assertThat(partial.get(3).next()).contains("1 active endpoint(s) have no usable signing secret");
+
+      List<Step> done = WebhooksSetupStatus.steps("/p", "K", 3, true, 2, 0, 4);
+      assertThat(done).extracting(Step::state).containsOnly("Set up");
+   }
+
+
+
+   /*******************************************************************************
+    ** The live card: an empty install needs endpoints, and an endpoint moves it
+    ** forward.
+    *******************************************************************************/
+   @Test
+   void liveStatusFollowsTheTables() throws Exception
+   {
+      WebhooksTestSupport.setUp();
+      List<Step> empty = WebhooksSetupStatus.load();
+      assertThat(empty).filteredOn(step -> step.label().equals("Endpoints")).extracting(Step::state).containsExactly("Needs setup");
+      assertThat(empty).filteredOn(step -> step.label().equals("Event types")).extracting(Step::state).containsExactly("Set up");
+
+      WebhooksTestSupport.endpoint("crm", "https://example.com/hook");
+      assertThat(WebhooksSetupStatus.load()).filteredOn(step -> step.label().equals("Endpoints")).extracting(Step::state).containsExactly("Set up");
+   }
+
+
+
+   /*******************************************************************************
+    ** The secrets step counts blocked endpoints exactly as DeliveryEngine does:
+    ** active, signing required, no usable secret. An allowUnsigned endpoint, a
+    ** paused endpoint and one whose only secret has expired are judged by the
+    ** runtime's rules.
+    *******************************************************************************/
+   @Test
+   void secretsStepMatchesTheRuntime() throws Exception
+   {
+      WebhooksTestSupport.setUp();
+      Integer unsigned = WebhooksTestSupport.insert(WebhooksTables.ENDPOINT, new QRecord().withValue("name", "u").withValue("url", "https://example.com/u").withValue("allowUnsigned", true)).getValueInteger("id");
+      assertThat(secretsState()).as("allowUnsigned with no secret is not blocked").isEqualTo("Set up");
+
+      Integer paused = WebhooksTestSupport.insert(WebhooksTables.ENDPOINT, new QRecord().withValue("name", "p").withValue("url", "https://example.com/p").withValue("activeStatus", "PAUSED")).getValueInteger("id");
+      assertThat(paused).isNotNull();
+      assertThat(secretsState()).as("a paused endpoint with no secret is not blocked").isEqualTo("Set up");
+
+      Integer rotating = WebhooksTestSupport.endpoint("r", "https://example.com/r");
+      assertThat(secretsState()).as("a secret signs").isEqualTo("Set up");
+      WebhooksTestSupport.CLOCK.advance(Duration.ofHours(25));
+      EndpointSecrets.rotate(rotating, "again");
+      WebhooksTestSupport.CLOCK.advance(Duration.ofHours(25));
+      assertThat(unsigned).isNotNull();
+      assertThat(EndpointSecrets.signingKeys(rotating)).hasSize(1);
+
+      Integer onlyExpired = WebhooksTestSupport.insert(WebhooksTables.ENDPOINT, new QRecord().withValue("name", "e").withValue("url", "https://example.com/e")).getValueInteger("id");
+      SystemWrites.insertOne(WebhooksTables.SECRET, new QRecord().withValue("endpointId", onlyExpired).withValue("status", "PREVIOUS").withValue("source", "REFERENCE")
+         .withValue("secretRef", "WEBHOOKS_TEST_NOPE").withValue("expiresDate", WebhooksTestSupport.CLOCK.instant().minus(Duration.ofHours(1))));
+      assertThat(EndpointSecrets.signingKeys(onlyExpired)).isEmpty();
+      assertThat(secretsState()).as("an endpoint whose only secret expired is blocked").isEqualTo("Needs setup");
+   }
+
+
+
+   /*******************************************************************************
+    ** The secrets step state.
+    *******************************************************************************/
+   private static String secretsState() throws Exception
+   {
+      return (WebhooksSetupStatus.load().stream().filter(step -> step.label().equals("Signing secrets")).findFirst().orElseThrow().state());
+   }
+}

src/test/java/com/kingsrook/qbits/webhooks/delivery/SignedDeliveryTest.java

@@ -175,6 +175,25 @@ class SignedDeliveryTest
 
 
 
+   /*******************************************************************************
+    ** A previous secret stops signing when its Signs Until date passes, even
+    ** before the maintenance job retires it (expiresDate is enforced when
+    ** signing, not only when retiring).
+    *******************************************************************************/
+   @Test
+   void expiredSecretStopsSigningBeforeItIsRetired() throws Exception
+   {
+      Integer endpointId = WebhooksTestSupport.endpoint("crm", receiver.url("/hook"));
+      EndpointSecrets.rotate(endpointId, "expiry test");
+      assertThat(EndpointSecrets.signingKeys(endpointId)).hasSize(2);
+
+      WebhooksTestSupport.CLOCK.advance(Duration.ofHours(25));
+      assertThat(WebhooksTestSupport.all(WebhooksTables.SECRET)).extracting(r -> r.getValueString("status")).contains("PREVIOUS");
+      assertThat(EndpointSecrets.signingKeys(endpointId)).hasSize(1);
+   }
+
+
+
    /*******************************************************************************
     ** A retry keeps the same webhook-id (the idempotency key) and payload.
     *******************************************************************************/

src/test/resources/webhooks-h2.sql

@@ -49,7 +49,7 @@ CREATE TABLE webhooks_endpoint_secret
    id INT AUTO_INCREMENT PRIMARY KEY,
    create_date TIMESTAMP NOT NULL DEFAULT now(),
    modify_date TIMESTAMP NOT NULL DEFAULT now(),
-   endpoint_id INTEGER NOT NULL REFERENCES webhooks_endpoint (id),
+   endpoint_id INTEGER NOT NULL CONSTRAINT fk_webhooks_endpoint_secret_endpoint_id REFERENCES webhooks_endpoint (id) ON DELETE CASCADE,
    status VARCHAR(20) DEFAULT 'ACTIVE',
    source VARCHAR(20) NOT NULL,
    secret_ref VARCHAR(100),
@@ -68,7 +68,7 @@ CREATE TABLE webhooks_subscription
    id INT AUTO_INCREMENT PRIMARY KEY,
    create_date TIMESTAMP NOT NULL DEFAULT now(),
    modify_date TIMESTAMP NOT NULL DEFAULT now(),
-   endpoint_id INTEGER NOT NULL REFERENCES webhooks_endpoint (id),
+   endpoint_id INTEGER NOT NULL CONSTRAINT fk_webhooks_subscription_endpoint_id REFERENCES webhooks_endpoint (id) ON DELETE CASCADE,
    event_type_name VARCHAR(200) NOT NULL,
    active_status VARCHAR(20) DEFAULT 'ACTIVE',
    description VARCHAR(250),
@@ -92,8 +92,8 @@ CREATE TABLE webhooks_delivery
    modify_date TIMESTAMP NOT NULL DEFAULT now(),
    event_id VARCHAR(40) NOT NULL,
    event_type_name VARCHAR(200) NOT NULL,
-   endpoint_id INTEGER NOT NULL,
-   subscription_id INTEGER,
+   endpoint_id INTEGER NOT NULL CONSTRAINT fk_webhooks_delivery_endpoint_id REFERENCES webhooks_endpoint (id) ON DELETE RESTRICT,
+   subscription_id INTEGER CONSTRAINT fk_webhooks_delivery_subscription_id REFERENCES webhooks_subscription (id) ON DELETE SET NULL,
    source VARCHAR(20),
    source_table VARCHAR(100),
    source_record_id VARCHAR(100),
@@ -122,14 +122,16 @@ CREATE INDEX webhooks_delivery_status_ix ON webhooks_delivery (status, modify_da
 
 CREATE INDEX webhooks_delivery_delivered_ix ON webhooks_delivery (delivered_date);
 
+CREATE INDEX webhooks_delivery_subscription_ix ON webhooks_delivery (subscription_id);
+
 
 CREATE TABLE webhooks_delivery_attempt
 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    create_date TIMESTAMP NOT NULL DEFAULT now(),
    modify_date TIMESTAMP NOT NULL DEFAULT now(),
-   delivery_id INTEGER NOT NULL,
-   endpoint_id INTEGER NOT NULL,
+   delivery_id INTEGER NOT NULL CONSTRAINT fk_webhooks_delivery_attempt_delivery_id REFERENCES webhooks_delivery (id) ON DELETE CASCADE,
+   endpoint_id INTEGER NOT NULL CONSTRAINT fk_webhooks_delivery_attempt_endpoint_id REFERENCES webhooks_endpoint (id) ON DELETE RESTRICT,
    attempt_number INTEGER NOT NULL,
    outcome VARCHAR(20) NOT NULL,
    claimed_by VARCHAR(100),