fix(help): the help bundle carries the artifact version (Maven-filtered) instead of a hardcoded SNAPSHOT

On release branches the bundle's version and the producer's differed, so the bundle test that asserts they match failed, and released bundles recorded SNAPSHOT as their provenance. help/*.yaml is now filtered: version: ${project.version}. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
Merge pull request #38 from QRun-IO/feature/help-bundle
2026-10-14 22:02:04 -05:00 · 2026-10-14 20:29:38 -05:00 · 2026-10-14 20:01:53 -05:00 · 2026-10-14 18:51:03 -05:00 · 2026-10-14 18:48:28 -05:00 · 2026-10-14 18:00:42 -05:00
16 changed files with 2695 additions and 268 deletions
@@ -8,8 +8,9 @@ All notable changes to this project are documented here. The format follows Keep
 - Record-view child lists (e.g. a notification's Recipients) failed with "Error in JSON Serialization": the child table's meta-data includes the qbit config, and `AlertsQBitConfig.getRuntime()` points at a runtime that points back at the config, so Jackson recursed to its 1000-level nesting limit. `getRuntime()` and `getAudienceResolver()` are now `@JsonIgnore`, as in the other qbits' configs. A regression test renders and serializes every child-list widget.

 ### Added
+- Ship the full host-neutral Alerts help bundle through QQQ 4.1.0-RC.7 core registration, replacing the temporary loader. Cover owned tables, fields, actions, jobs, widgets and possible-value sources with a bundle coverage gate; support HOST overrides and hosts without help tables.
 - Starter defaults changelog `alerts-defaults.changelog.sql` (insert-only, guarded): the `in-app` and `email` channels and the `alerts` and `other` groups, equal to what the runtime creates on first use. Tested through real Liquibase on H2.
- Help for the Alerts app and every `alerts_*` table (`help/qbit-alerts.yaml`, STAND-IN core#1198): what the table is for and how its rows get created, shown when it is empty.
+- Help for the Alerts app and every `alerts_*` table (`help/qbit-alerts.yaml`): what the table is for and how its rows get created, shown when it is empty.
 - A Setup card first on the Alerts home (alerts admins): each configuration table, its state and the next step.
 - `AlertsTablesGuardrailTest`: every table is used by runtime code, seeded or listed as starting empty with a reason, and has help.

@@ -47,6 +48,7 @@ All notable changes to this project are documented here. The format follows Keep
 - Apply reminders and escalation (#27, part 2): a minute-based hidden job sends due reminders, advances ordered USER/TEAM/CHANNEL escalation steps, and caps final-step repeats. Acknowledgement, snooze, resolution and mutes stop both; reopening restarts escalation. Delivery, events and progress commit together, with a persisted same-minute checkpoint.
 - Add admin escalation policy and step tables, child step lists, and type/group policy links in guarded Liquibase changesets 23–26. Type policies override group policies. CRITICAL reminders default to 60 minutes; explicit zero turns them off.
 - Keep EXPIRED reserved and remove it from selectable resolution values because the design specifies no expiration rule.
+- Per-notification email opt-in via recipient email status `REQUESTED`: immediate delivery without a standing category preference. It still honours the category email minimum severity, the user pause (SECURITY bypasses) and quiet hours (held until they end, for up to a day), is skipped when no address is known, and is never emailed for ALERT notifications.
 - Optional external channels:
  - Slack (an Incoming Webhook, Block Kit)
  - Microsoft Teams (a Workflows webhook, an Adaptive Card)
@@ -280,6 +280,23 @@ assigned automatically. The Resolve action continues to record MANUAL.
 - **Alertmanager sink:** open alerts are sent with an `endsAt` a week out, not re-posted.
 - **Not built yet:** Micrometer metrics, mirrored lock columns and a configurable table namespace. These are the rest of design phases P4 to P6.

+## In-app help
+
+The producer registers [`help/qbit-alerts.yaml`](src/main/resources/help/qbit-alerts.yaml)
+with QQQ's help-bundle registry. It covers the Alerts app, tables and fields,
+actions, scheduled jobs, widgets and possible-value sources. QQQ syncs the bundle
+when the host installs the help-content tables; registration is harmless without them.
+
+Hosts can override individual entries by registering their own bundle with
+`layer: HOST`, using the same entry `key` and `role` (default `ALL_SCREENS`).
+Register it with `HelpBundles.register(qInstance, "help/my-host.yaml")`. Host
+bundles take precedence over this `QBIT` bundle, and edits made by users remain protected.
+
+`AlertsTablesGuardrailTest` runs `HelpBundleAssertions` against the produced instance
+to check owned-object coverage, summary length and live references. It also requires
+widget and possible-value-source help and preserves the table empty-state guidance.
+When releasing, keep the bundle's literal version aligned with the POM's revision.
+
 ## Build

 ```bash
@@ -24,6 +24,8 @@
      <jacoco.version>0.8.15</jacoco.version>
      <!-- qbit-email from Maven Central -->
      <qbit-email.version>0.1.0-RC.2</qbit-email.version>
+      <liquibase.version>4.33.0</liquibase.version>
+      <testcontainers.version>2.0.5</testcontainers.version>
      <qbit.guardrails.enforce>true</qbit.guardrails.enforce>
      <qbit.guardrails.strictUiApiRoutes>true</qbit.guardrails.strictUiApiRoutes>
   </properties>
@@ -104,6 +106,31 @@
         <version>2.2.220</version>
         <scope>test</scope>
      </dependency>
+      <!-- AlertsForeignKeysTest applies the shipped changelog with Liquibase to a real PostgreSQL (Testcontainers) -->
+      <dependency>
+         <groupId>org.liquibase</groupId>
+         <artifactId>liquibase-core</artifactId>
+         <version>${liquibase.version}</version>
+         <scope>test</scope>
+      </dependency>
+      <dependency>
+         <groupId>org.postgresql</groupId>
+         <artifactId>postgresql</artifactId>
+         <version>42.7.14</version>
+         <scope>test</scope>
+      </dependency>
+      <dependency>
+         <groupId>org.testcontainers</groupId>
+         <artifactId>testcontainers-postgresql</artifactId>
+         <version>${testcontainers.version}</version>
+         <scope>test</scope>
+      </dependency>
+      <dependency>
+         <groupId>org.testcontainers</groupId>
+         <artifactId>testcontainers-junit-jupiter</artifactId>
+         <version>${testcontainers.version}</version>
+         <scope>test</scope>
+      </dependency>

      <!-- AlertsDefaultsChangelogTest runs the shipped seed changelog through real Liquibase on H2. -->
      <dependency>
@@ -126,6 +153,23 @@
   </dependencies>

   <build>
+      <resources>
+         <!-- help bundles carry the artifact version (QQQ help bundle provenance) -->
+         <resource>
+            <directory>src/main/resources</directory>
+            <filtering>true</filtering>
+            <includes>
+               <include>help/*.yaml</include>
+            </includes>
+         </resource>
+         <resource>
+            <directory>src/main/resources</directory>
+            <filtering>false</filtering>
+            <excludes>
+               <exclude>help/*.yaml</exclude>
+            </excludes>
+         </resource>
+      </resources>
      <plugins>
         <plugin>
            <groupId>org.apache.maven.plugins</groupId>
@@ -204,7 +248,7 @@
   <profiles>
      <!-- Built against qqq 4.1 (QBitMetaDataProducer). Until qbit-build-parent  -->
      <!-- imports the released 4.1 BOM, this profile is active by default and     -->
-      <!-- imports qqq-bom-pom 4.1.0-RC.6 ahead of the parent's 4.0.0 BOM.         -->
+      <!-- imports qqq-bom-pom 4.1.0-RC.7 ahead of the parent's 4.0.0 BOM.         -->
      <profile>
         <id>qqq-snapshot</id>
         <activation>
@@ -24,7 +24,6 @@ import com.kingsrook.qbits.alerts.engine.AlertsProcessListener;
 import com.kingsrook.qbits.alerts.engine.AlertsSinkAdapter;
 import com.kingsrook.qbits.alerts.engine.MonitorEvaluator;
 import com.kingsrook.qbits.alerts.engine.QbitAlertsSink;
-import com.kingsrook.qbits.alerts.metadata.AlertsHelp;
 import com.kingsrook.qbits.alerts.metadata.AlertsTableMetaDataHelper;
 import com.kingsrook.qbits.alerts.model.AlertsChannel;
 import com.kingsrook.qbits.alerts.model.AlertsMonitor;
@@ -38,6 +37,7 @@ import com.kingsrook.qqq.backend.core.logging.QLogger;
 import com.kingsrook.qqq.backend.core.model.metadata.MetaDataProducerMultiOutput;
 import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
 import com.kingsrook.qqq.backend.core.model.metadata.code.QCodeReference;
+import com.kingsrook.qqq.backend.core.model.metadata.help.bundles.HelpBundles;
 import com.kingsrook.qqq.backend.core.model.metadata.qbits.QBitMetaData;
 import com.kingsrook.qqq.backend.core.model.metadata.qbits.QBitMetaDataProducer;
 import static com.kingsrook.qqq.backend.core.logging.LogUtils.logPair;
@@ -174,7 +174,8 @@ public class AlertsQBitProducer implements QBitMetaDataProducer<AlertsQBitConfig

      MetaDataProducerMultiOutput output = QBitMetaDataProducer.super.produce(qInstance);
      output.addSelfToInstance(qInstance);
-      AlertsHelp.apply(qInstance);
+      // Registration is metadata-only; core guards startup sync when helpContent is absent.
+      HelpBundles.register(qInstance, "help/qbit-alerts.yaml");
      return (new MetaDataProducerMultiOutput());
   }

@@ -1,127 +0,0 @@
-/*
- * Copyright 2026 Kingsrook, LLC
- *
- * 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
- *
- *     http://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.alerts.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.alerts.AlertsQBitConfig;
-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-alerts.yaml (the QQQ help-bundle format) to the
- ** Alerts app and its alerts_* 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 AlertsHelp
-{
-   public static final String RESOURCE = "/help/qbit-alerts.yaml";
-   public static final String SLOT     = "help";
-
-
-
-   /*******************************************************************************
-    ** Utility class.
-    *******************************************************************************/
-   private AlertsHelp()
-   {
-   }
-
-
-
-   /*******************************************************************************
-    ** The bundle's entries by key, validated (fails startup if malformed).
-    *******************************************************************************/
-   public static Map<String, JsonNode> entries() throws QException
-   {
-      try(InputStream stream = AlertsHelp.class.getResourceAsStream(RESOURCE))
-      {
-         if(stream == null)
-         {
-            throw (new IOException("missing resource"));
-         }
-         JsonNode bundle = new ObjectMapper(new YAMLFactory()).readTree(stream);
-         if(!"qbit-alerts".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 alerts_* table in the instance.
-    ** A host's own help on a table is kept (the bundle only fills the slot).
-    *******************************************************************************/
-   public static void apply(QInstance qInstance) 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(AlertsQBitConfig.APP_NAME);
-      JsonNode     appEntry = entries.get("app:" + AlertsQBitConfig.APP_NAME + ";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()));
-   }
-
-}
@@ -290,7 +290,7 @@ public final class AlertsPossibleValues
    *******************************************************************************/
   public enum EmailStatus
   {
-      NONE, PENDING, SENT, DIGESTED, SKIPPED, FAILED
+      NONE, PENDING, REQUESTED, SENT, DIGESTED, SKIPPED, FAILED
   }


@@ -29,11 +29,14 @@ import java.util.HashMap;
 import java.util.LinkedHashMap;
 import java.util.List;
 import java.util.Map;
+import java.util.Set;
+import java.util.stream.Collectors;
 import com.kingsrook.qbits.alerts.AlertsQBitConfig;
 import com.kingsrook.qbits.alerts.AlertsRuntime;
 import com.kingsrook.qbits.alerts.delivery.AlertRedactor;
 import com.kingsrook.qbits.alerts.email.AlertsEmail;
 import com.kingsrook.qbits.alerts.email.AlertsEmailSender;
+import com.kingsrook.qbits.alerts.engine.AlertTiming;
 import com.kingsrook.qbits.alerts.engine.AlertsDb;
 import com.kingsrook.qbits.alerts.metadata.AlertsProcessesMetaDataProducer;
 import com.kingsrook.qbits.alerts.model.AlertsNotification;
@@ -43,6 +46,7 @@ import com.kingsrook.qbits.alerts.model.AlertsPossibleValues.EmailMode;
 import com.kingsrook.qbits.alerts.model.AlertsPossibleValues.EmailStatus;
 import com.kingsrook.qbits.alerts.model.AlertsUserPreference;
 import com.kingsrook.qqq.backend.core.actions.messaging.SendMessageAction;
+import com.kingsrook.qqq.backend.core.alerts.AlertSeverity;
 import com.kingsrook.qqq.backend.core.alerts.NotificationCategory;
 import com.kingsrook.qqq.backend.core.alerts.NotificationSeverity;
 import com.kingsrook.qqq.backend.core.exceptions.QException;
@@ -67,6 +71,11 @@ import static com.kingsrook.qqq.backend.core.logging.LogUtils.logPair;
 ** - IMMEDIATE: one email per notification at or above the minimum severity;
 ** - DAILY_DIGEST: one email at the user's digest hour (in their time zone,
 **   from My Alert Settings) listing the day's unread notifications.
+ ** A recipient with email status REQUESTED (a per-notification opt-in, e.g. by
+ ** a job) is emailed immediately without a standing category preference. It
+ ** still honours the category's email minimum severity when one is set, the
+ ** user's pause (SECURITY bypasses) and quiet hours (held, not dropped), is
+ ** skipped without a known address, and is only eligible for a day.
 ** Sent through qbit-email when it is produced in the instance (one message
 ** per recipient; templates alerts_notification and alerts_digest,
 ** NOTIFICATION), else through core SendMessageAction. Inert without either
@@ -137,12 +146,19 @@ public class NotificationEmailer
         {
            preferences.computeIfAbsent(preference.getValueString("userId"), k -> new HashMap<>()).put(preference.getValueString("category"), preference);
         }
-         if(preferences.isEmpty())
+
+         QQueryFilter eligible = new QQueryFilter().withBooleanOperator(QQueryFilter.BooleanOperator.OR)
+            .withSubFilter(new QQueryFilter()
+               .withCriteria("emailStatus", QCriteriaOperator.EQUALS, EmailStatus.REQUESTED.name())
+               .withCriteria("deliveredAt", QCriteriaOperator.GREATER_THAN_OR_EQUALS, now.minus(1, ChronoUnit.DAYS)));
+         if(!preferences.isEmpty())
         {
-            return (new Result(0, 0, 0, 0));
+            eligible.withSubFilter(new QQueryFilter()
+               .withCriteria("userId", QCriteriaOperator.IN, new ArrayList<>(preferences.keySet()))
+               .withCriteria("emailStatus", QCriteriaOperator.IN, List.of(EmailStatus.NONE.name(), EmailStatus.PENDING.name())));
         }

-         Map<String, QRecord> settings = userSettings(preferences.keySet());
+         Map<String, QRecord> settings = new HashMap<>();
         int sent = 0;
         int skipped = 0;
         int failed = 0;
@@ -151,8 +167,7 @@ public class NotificationEmailer
         while(true)
         {
            List<QRecord> recipients = AlertsDb.query(AlertsNotificationRecipient.TABLE_NAME, new QQueryFilter()
-               .withCriteria("userId", QCriteriaOperator.IN, new ArrayList<>(preferences.keySet()))
-               .withCriteria("emailStatus", QCriteriaOperator.IN, List.of(EmailStatus.NONE.name(), EmailStatus.PENDING.name()))
+               .withSubFilter(eligible)
               .withCriteria("id", QCriteriaOperator.GREATER_THAN, afterId)
               .withOrderBy(new QFilterOrderBy("id", true)).withLimit(SCAN));
            if(recipients.isEmpty())
@@ -161,25 +176,43 @@ public class NotificationEmailer
            }
            afterId = recipients.get(recipients.size() - 1).getValueInteger("id");
            Map<Integer, QRecord> notifications = notifications(recipients, now);
+            Set<String> pageUsers = recipients.stream().map(recipient -> recipient.getValueString("userId")).collect(Collectors.toSet());
+            settings.putAll(userSettings(pageUsers));
+            Map<String, Map<String, QRecord>> requestedPreferences = new HashMap<>();
+            for(QRecord preference : AlertsDb.query(AlertsNotificationPreference.TABLE_NAME, new QQueryFilter()
+               .withCriteria("userId", QCriteriaOperator.IN, new ArrayList<>(pageUsers))))
+            {
+               requestedPreferences.computeIfAbsent(preference.getValueString("userId"), k -> new HashMap<>()).put(preference.getValueString("category"), preference);
+            }
            for(QRecord recipient : recipients)
            {
               QRecord notification = notifications.get(recipient.getValueInteger("notificationId"));
               String  userId       = recipient.getValueString("userId");
-               QRecord preference   = notification == null ? null : preferences.get(userId).get(notification.getValueString("category"));
-               if(notification == null || preference == null || NotificationCategory.ALERT.name().equals(notification.getValueString("category"))
-                  || !atLeast(notification.getValueString("severity"), preference.getValueString("emailMinSeverity")))
+               boolean requested    = EmailStatus.REQUESTED.name().equals(recipient.getValueString("emailStatus"));
+               String  category     = notification == null ? null : notification.getValueString("category");
+               QRecord preference   = category == null ? null : (requested ? requestedPreferences : preferences).getOrDefault(userId, Map.of()).get(category);
+               QRecord setting      = settings.get(userId);
+               if(notification == null || NotificationCategory.ALERT.name().equals(category)
+                  || (requested ? (!StringUtils.hasContent(address(setting)) || (preference != null && StringUtils.hasContent(preference.getValueString("emailMinSeverity"))
+                  && !atLeast(notification.getValueString("severity"), preference.getValueString("emailMinSeverity"))))
+                  : (preference == null || !atLeast(notification.getValueString("severity"), preference.getValueString("emailMinSeverity")))))
               {
                  mark(recipient, EmailStatus.SKIPPED, null);
                  skipped++;
               }
-               else if(!NotificationCategory.SECURITY.name().equals(notification.getValueString("category"))
-                  && settings.get(userId) != null && settings.get(userId).getValueInstant("pausedUntil") != null
-                  && settings.get(userId).getValueInstant("pausedUntil").isAfter(now))
+               else if(!NotificationCategory.SECURITY.name().equals(category)
+                  && setting != null && setting.getValueInstant("pausedUntil") != null
+                  && setting.getValueInstant("pausedUntil").isAfter(now))
               {
                  // Leave the recipient pending so a later run can deliver it.
                  skipped++;
               }
-               else if(EmailMode.IMMEDIATE.name().equals(preference.getValueString("emailMode")))
+               else if(requested && AlertTiming.isQuiet(setting, AlertSeverity.fromLabel(notification.getValueString("severity"), AlertSeverity.WARNING), now))
+               {
+                  // Quiet hours hold a requested email; it stays REQUESTED until the window ends or it ages out.
+                  skipped++;
+               }
+               else if(requested || EmailMode.IMMEDIATE.name().equals(preference.getValueString("emailMode")))
               {
                  EmailStatus status = sendImmediate(address(settings.get(userId)), notification);
                  mark(recipient, status, status == EmailStatus.SENT ? now : null);
@@ -8,6 +8,10 @@
 -- Rules for this file: the host runs it, the qbit never runs DDL; additive only
 -- (nothing is ever dropped or rewritten, except the one pre-GA rename in changesets 16-17);
 -- every statement is idempotent (IF NOT EXISTS, or a precondition). Column names are the snake_case form of the field names.
+-- Foreign keys (changesets 30-53): one changeset per constraint, named fk_<child_table>_<child_column> and guarded by a
+-- precondition on that name, so a host that already added it (e.g. from its own changelog) marks it ran. Before adding the
+-- constraint each one records orphan rows in qqq_fk_quarantine (the same DDL in every qbit), then nulls a nullable column
+-- or deletes a row whose column is NOT NULL (row_data keeps the full row), so the migration never fails on old data.

 -- changeset qbit-alerts:1
 -- comment: A category of alerts for people ("Backups", "CDN"), optionally nested. The Alerts home groups by these, subscriptions target them, and every alert type belongs to one.
@@ -519,3 +523,255 @@ CREATE INDEX ix_alerts_notification_recipient_slack_status ON alerts_notificatio
 -- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.columns WHERE LOWER(table_name) = 'alerts_notification_preference' AND LOWER(column_name) = 'in_app_min_severity'
 -- comment: Optional minimum severity for in-app notifications (security always arrives).
 ALTER TABLE alerts_notification_preference ADD COLUMN in_app_min_severity VARCHAR(16);
+
+-- changeset qbit-alerts:30
+-- comment: Indexes for the join child columns: a per-record monitor finds its open alerts by (monitor_id, status) on every evaluation (MonitorEvaluator), and the record pages list a type's monitors (alert_type_id) and a group's types (group_id).
+CREATE INDEX IF NOT EXISTS ix_alerts_alert_monitor_id_status ON alerts_alert (monitor_id, status);
+CREATE INDEX IF NOT EXISTS ix_alerts_monitor_alert_type_id ON alerts_monitor (alert_type_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_type_group_id ON alerts_type (group_id);
+
+-- changeset qbit-alerts:31
+-- comment: Indexes for the foreign-key columns no earlier index leads with, so FK checks and ON DELETE actions do not scan the child table.
+CREATE INDEX IF NOT EXISTS ix_alerts_alert_group_id ON alerts_alert (group_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_alert_assigned_team_id ON alerts_alert (assigned_team_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_type_escalation_policy_id ON alerts_type (escalation_policy_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_group_parent_group_id ON alerts_group (parent_group_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_group_owner_team_id ON alerts_group (owner_team_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_group_escalation_policy_id ON alerts_group (escalation_policy_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_delivery_alert_event_id ON alerts_delivery (alert_event_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_delivery_subscription_id ON alerts_delivery (subscription_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_subscription_team_id ON alerts_subscription (team_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_subscription_channel_id ON alerts_subscription (channel_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_escalation_step_team_id ON alerts_escalation_step (team_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_escalation_step_channel_id ON alerts_escalation_step (channel_id);
+-- rollback DROP INDEX IF EXISTS ix_alerts_alert_group_id; DROP INDEX IF EXISTS ix_alerts_alert_assigned_team_id; DROP INDEX IF EXISTS ix_alerts_type_escalation_policy_id; DROP INDEX IF EXISTS ix_alerts_group_parent_group_id; DROP INDEX IF EXISTS ix_alerts_group_owner_team_id; DROP INDEX IF EXISTS ix_alerts_group_escalation_policy_id; DROP INDEX IF EXISTS ix_alerts_delivery_alert_event_id; DROP INDEX IF EXISTS ix_alerts_delivery_subscription_id; DROP INDEX IF EXISTS ix_alerts_subscription_team_id; DROP INDEX IF EXISTS ix_alerts_subscription_channel_id; DROP INDEX IF EXISTS ix_alerts_escalation_step_team_id; DROP INDEX IF EXISTS ix_alerts_escalation_step_channel_id;
+
+-- changeset qbit-alerts:32 dbms:postgresql
+-- comment: alerts_type.group_id -> alerts_group ON DELETE SET NULL. A deleted group leaves its types ungrouped; AlertTypeSync puts them back in their declared (or the default) group at the next boot. Orphans are recorded in qqq_fk_quarantine and nulled first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_type_group_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_type', 'group_id', CAST(c.id AS VARCHAR(100)), CAST(c.group_id AS VARCHAR(100)), 'NULLED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:32' FROM alerts_type c WHERE c.group_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_group p WHERE p.id = c.group_id);
+UPDATE alerts_type c SET group_id = NULL WHERE c.group_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_group p WHERE p.id = c.group_id);
+ALTER TABLE alerts_type ADD CONSTRAINT fk_alerts_type_group_id FOREIGN KEY (group_id) REFERENCES alerts_group (id) ON DELETE SET NULL;
+-- rollback ALTER TABLE alerts_type DROP CONSTRAINT IF EXISTS fk_alerts_type_group_id;
+
+-- changeset qbit-alerts:33 dbms:postgresql
+-- comment: alerts_type.escalation_policy_id -> alerts_escalation_policy ON DELETE SET NULL. A deleted escalation policy leaves the type on its group's policy (or none). Orphans are recorded in qqq_fk_quarantine and nulled first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_type_escalation_policy_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_type', 'escalation_policy_id', CAST(c.id AS VARCHAR(100)), CAST(c.escalation_policy_id AS VARCHAR(100)), 'NULLED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:33' FROM alerts_type c WHERE c.escalation_policy_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_escalation_policy p WHERE p.id = c.escalation_policy_id);
+UPDATE alerts_type c SET escalation_policy_id = NULL WHERE c.escalation_policy_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_escalation_policy p WHERE p.id = c.escalation_policy_id);
+ALTER TABLE alerts_type ADD CONSTRAINT fk_alerts_type_escalation_policy_id FOREIGN KEY (escalation_policy_id) REFERENCES alerts_escalation_policy (id) ON DELETE SET NULL;
+-- rollback ALTER TABLE alerts_type DROP CONSTRAINT IF EXISTS fk_alerts_type_escalation_policy_id;
+
+-- changeset qbit-alerts:34 dbms:postgresql
+-- comment: alerts_group.parent_group_id -> alerts_group ON DELETE SET NULL. A deleted parent group makes its child groups top-level. Orphans are recorded in qqq_fk_quarantine and nulled first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_group_parent_group_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_group', 'parent_group_id', CAST(c.id AS VARCHAR(100)), CAST(c.parent_group_id AS VARCHAR(100)), 'NULLED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:34' FROM alerts_group c WHERE c.parent_group_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_group p WHERE p.id = c.parent_group_id);
+UPDATE alerts_group c SET parent_group_id = NULL WHERE c.parent_group_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_group p WHERE p.id = c.parent_group_id);
+ALTER TABLE alerts_group ADD CONSTRAINT fk_alerts_group_parent_group_id FOREIGN KEY (parent_group_id) REFERENCES alerts_group (id) ON DELETE SET NULL;
+-- rollback ALTER TABLE alerts_group DROP CONSTRAINT IF EXISTS fk_alerts_group_parent_group_id;
+
+-- changeset qbit-alerts:35 dbms:postgresql
+-- comment: alerts_group.owner_team_id -> alerts_team ON DELETE SET NULL. A deleted team leaves the group without an owner. Orphans are recorded in qqq_fk_quarantine and nulled first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_group_owner_team_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_group', 'owner_team_id', CAST(c.id AS VARCHAR(100)), CAST(c.owner_team_id AS VARCHAR(100)), 'NULLED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:35' FROM alerts_group c WHERE c.owner_team_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_team p WHERE p.id = c.owner_team_id);
+UPDATE alerts_group c SET owner_team_id = NULL WHERE c.owner_team_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_team p WHERE p.id = c.owner_team_id);
+ALTER TABLE alerts_group ADD CONSTRAINT fk_alerts_group_owner_team_id FOREIGN KEY (owner_team_id) REFERENCES alerts_team (id) ON DELETE SET NULL;
+-- rollback ALTER TABLE alerts_group DROP CONSTRAINT IF EXISTS fk_alerts_group_owner_team_id;
+
+-- changeset qbit-alerts:36 dbms:postgresql
+-- comment: alerts_group.escalation_policy_id -> alerts_escalation_policy ON DELETE SET NULL. A deleted escalation policy leaves the group without one. Orphans are recorded in qqq_fk_quarantine and nulled first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_group_escalation_policy_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_group', 'escalation_policy_id', CAST(c.id AS VARCHAR(100)), CAST(c.escalation_policy_id AS VARCHAR(100)), 'NULLED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:36' FROM alerts_group c WHERE c.escalation_policy_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_escalation_policy p WHERE p.id = c.escalation_policy_id);
+UPDATE alerts_group c SET escalation_policy_id = NULL WHERE c.escalation_policy_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_escalation_policy p WHERE p.id = c.escalation_policy_id);
+ALTER TABLE alerts_group ADD CONSTRAINT fk_alerts_group_escalation_policy_id FOREIGN KEY (escalation_policy_id) REFERENCES alerts_escalation_policy (id) ON DELETE SET NULL;
+-- rollback ALTER TABLE alerts_group DROP CONSTRAINT IF EXISTS fk_alerts_group_escalation_policy_id;
+
+-- changeset qbit-alerts:37 dbms:postgresql
+-- comment: alerts_monitor.alert_type_id -> alerts_type ON DELETE RESTRICT. A monitor needs its type; types are never deleted (TABLE_DELETE is off, AlertTypeSync only adds and updates). Orphans are recorded in qqq_fk_quarantine and deleted first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_monitor_alert_type_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_monitor', 'alert_type_id', CAST(c.id AS VARCHAR(100)), CAST(c.alert_type_id AS VARCHAR(100)), 'DELETED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:37' FROM alerts_monitor c WHERE c.alert_type_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_type p WHERE p.id = c.alert_type_id);
+DELETE FROM alerts_monitor c WHERE c.alert_type_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_type p WHERE p.id = c.alert_type_id);
+ALTER TABLE alerts_monitor ADD CONSTRAINT fk_alerts_monitor_alert_type_id FOREIGN KEY (alert_type_id) REFERENCES alerts_type (id) ON DELETE RESTRICT;
+-- rollback ALTER TABLE alerts_monitor DROP CONSTRAINT IF EXISTS fk_alerts_monitor_alert_type_id;
+
+-- changeset qbit-alerts:38 dbms:postgresql
+-- comment: alerts_alert.alert_type_id -> alerts_type ON DELETE RESTRICT. An alert keeps its type; types are never deleted. Orphans are recorded in qqq_fk_quarantine and nulled first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_alert_alert_type_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_alert', 'alert_type_id', CAST(c.id AS VARCHAR(100)), CAST(c.alert_type_id AS VARCHAR(100)), 'NULLED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:38' FROM alerts_alert c WHERE c.alert_type_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_type p WHERE p.id = c.alert_type_id);
+UPDATE alerts_alert c SET alert_type_id = NULL WHERE c.alert_type_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_type p WHERE p.id = c.alert_type_id);
+ALTER TABLE alerts_alert ADD CONSTRAINT fk_alerts_alert_alert_type_id FOREIGN KEY (alert_type_id) REFERENCES alerts_type (id) ON DELETE RESTRICT;
+-- rollback ALTER TABLE alerts_alert DROP CONSTRAINT IF EXISTS fk_alerts_alert_alert_type_id;
+
+-- changeset qbit-alerts:39 dbms:postgresql
+-- comment: alerts_alert.monitor_id -> alerts_monitor ON DELETE SET NULL. A deleted monitor leaves its alerts (history) in place, without the monitor. Orphans are recorded in qqq_fk_quarantine and nulled first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_alert_monitor_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_alert', 'monitor_id', CAST(c.id AS VARCHAR(100)), CAST(c.monitor_id AS VARCHAR(100)), 'NULLED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:39' FROM alerts_alert c WHERE c.monitor_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_monitor p WHERE p.id = c.monitor_id);
+UPDATE alerts_alert c SET monitor_id = NULL WHERE c.monitor_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_monitor p WHERE p.id = c.monitor_id);
+ALTER TABLE alerts_alert ADD CONSTRAINT fk_alerts_alert_monitor_id FOREIGN KEY (monitor_id) REFERENCES alerts_monitor (id) ON DELETE SET NULL;
+-- rollback ALTER TABLE alerts_alert DROP CONSTRAINT IF EXISTS fk_alerts_alert_monitor_id;
+
+-- changeset qbit-alerts:40 dbms:postgresql
+-- comment: alerts_alert.group_id -> alerts_group ON DELETE SET NULL. A deleted group leaves its alerts in place, ungrouped. Orphans are recorded in qqq_fk_quarantine and nulled first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_alert_group_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_alert', 'group_id', CAST(c.id AS VARCHAR(100)), CAST(c.group_id AS VARCHAR(100)), 'NULLED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:40' FROM alerts_alert c WHERE c.group_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_group p WHERE p.id = c.group_id);
+UPDATE alerts_alert c SET group_id = NULL WHERE c.group_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_group p WHERE p.id = c.group_id);
+ALTER TABLE alerts_alert ADD CONSTRAINT fk_alerts_alert_group_id FOREIGN KEY (group_id) REFERENCES alerts_group (id) ON DELETE SET NULL;
+-- rollback ALTER TABLE alerts_alert DROP CONSTRAINT IF EXISTS fk_alerts_alert_group_id;
+
+-- changeset qbit-alerts:41 dbms:postgresql
+-- comment: alerts_alert.assigned_team_id -> alerts_team ON DELETE SET NULL. A deleted team unassigns its alerts. Orphans are recorded in qqq_fk_quarantine and nulled first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_alert_assigned_team_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_alert', 'assigned_team_id', CAST(c.id AS VARCHAR(100)), CAST(c.assigned_team_id AS VARCHAR(100)), 'NULLED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:41' FROM alerts_alert c WHERE c.assigned_team_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_team p WHERE p.id = c.assigned_team_id);
+UPDATE alerts_alert c SET assigned_team_id = NULL WHERE c.assigned_team_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_team p WHERE p.id = c.assigned_team_id);
+ALTER TABLE alerts_alert ADD CONSTRAINT fk_alerts_alert_assigned_team_id FOREIGN KEY (assigned_team_id) REFERENCES alerts_team (id) ON DELETE SET NULL;
+-- rollback ALTER TABLE alerts_alert DROP CONSTRAINT IF EXISTS fk_alerts_alert_assigned_team_id;
+
+-- changeset qbit-alerts:42 dbms:postgresql
+-- comment: alerts_record_ref.alert_id -> alerts_alert ON DELETE CASCADE. Record references belong to their alert. Orphans are recorded in qqq_fk_quarantine and deleted first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_record_ref_alert_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_record_ref', 'alert_id', CAST(c.id AS VARCHAR(100)), CAST(c.alert_id AS VARCHAR(100)), 'DELETED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:42' FROM alerts_record_ref c WHERE c.alert_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_alert p WHERE p.id = c.alert_id);
+DELETE FROM alerts_record_ref c WHERE c.alert_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_alert p WHERE p.id = c.alert_id);
+ALTER TABLE alerts_record_ref ADD CONSTRAINT fk_alerts_record_ref_alert_id FOREIGN KEY (alert_id) REFERENCES alerts_alert (id) ON DELETE CASCADE;
+-- rollback ALTER TABLE alerts_record_ref DROP CONSTRAINT IF EXISTS fk_alerts_record_ref_alert_id;
+
+-- changeset qbit-alerts:43 dbms:postgresql
+-- comment: alerts_event.alert_id -> alerts_alert ON DELETE CASCADE. The timeline belongs to its alert. Orphans are recorded in qqq_fk_quarantine and deleted first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_event_alert_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_event', 'alert_id', CAST(c.id AS VARCHAR(100)), CAST(c.alert_id AS VARCHAR(100)), 'DELETED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:43' FROM alerts_event c WHERE c.alert_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_alert p WHERE p.id = c.alert_id);
+DELETE FROM alerts_event c WHERE c.alert_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_alert p WHERE p.id = c.alert_id);
+ALTER TABLE alerts_event ADD CONSTRAINT fk_alerts_event_alert_id FOREIGN KEY (alert_id) REFERENCES alerts_alert (id) ON DELETE CASCADE;
+-- rollback ALTER TABLE alerts_event DROP CONSTRAINT IF EXISTS fk_alerts_event_alert_id;
+
+-- changeset qbit-alerts:44 dbms:postgresql
+-- comment: alerts_delivery.alert_id -> alerts_alert ON DELETE CASCADE. Delivery log rows of an alert go with the alert. Orphans are recorded in qqq_fk_quarantine and nulled first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_delivery_alert_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_delivery', 'alert_id', CAST(c.id AS VARCHAR(100)), CAST(c.alert_id AS VARCHAR(100)), 'NULLED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:44' FROM alerts_delivery c WHERE c.alert_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_alert p WHERE p.id = c.alert_id);
+UPDATE alerts_delivery c SET alert_id = NULL WHERE c.alert_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_alert p WHERE p.id = c.alert_id);
+ALTER TABLE alerts_delivery ADD CONSTRAINT fk_alerts_delivery_alert_id FOREIGN KEY (alert_id) REFERENCES alerts_alert (id) ON DELETE CASCADE;
+-- rollback ALTER TABLE alerts_delivery DROP CONSTRAINT IF EXISTS fk_alerts_delivery_alert_id;
+
+-- changeset qbit-alerts:45 dbms:postgresql
+-- comment: alerts_delivery.alert_event_id -> alerts_event ON DELETE SET NULL. The timeline event that caused a delivery (AlertRouter); the log row outlives it. Orphans are recorded in qqq_fk_quarantine and nulled first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_delivery_alert_event_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_delivery', 'alert_event_id', CAST(c.id AS VARCHAR(100)), CAST(c.alert_event_id AS VARCHAR(100)), 'NULLED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:45' FROM alerts_delivery c WHERE c.alert_event_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_event p WHERE p.id = c.alert_event_id);
+UPDATE alerts_delivery c SET alert_event_id = NULL WHERE c.alert_event_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_event p WHERE p.id = c.alert_event_id);
+ALTER TABLE alerts_delivery ADD CONSTRAINT fk_alerts_delivery_alert_event_id FOREIGN KEY (alert_event_id) REFERENCES alerts_event (id) ON DELETE SET NULL;
+-- rollback ALTER TABLE alerts_delivery DROP CONSTRAINT IF EXISTS fk_alerts_delivery_alert_event_id;
+
+-- changeset qbit-alerts:46 dbms:postgresql
+-- comment: alerts_delivery.subscription_id -> alerts_subscription ON DELETE SET NULL. A deleted subscription keeps its delivery history. Orphans are recorded in qqq_fk_quarantine and nulled first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_delivery_subscription_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_delivery', 'subscription_id', CAST(c.id AS VARCHAR(100)), CAST(c.subscription_id AS VARCHAR(100)), 'NULLED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:46' FROM alerts_delivery c WHERE c.subscription_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_subscription p WHERE p.id = c.subscription_id);
+UPDATE alerts_delivery c SET subscription_id = NULL WHERE c.subscription_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_subscription p WHERE p.id = c.subscription_id);
+ALTER TABLE alerts_delivery ADD CONSTRAINT fk_alerts_delivery_subscription_id FOREIGN KEY (subscription_id) REFERENCES alerts_subscription (id) ON DELETE SET NULL;
+-- rollback ALTER TABLE alerts_delivery DROP CONSTRAINT IF EXISTS fk_alerts_delivery_subscription_id;
+
+-- changeset qbit-alerts:47 dbms:postgresql
+-- comment: alerts_delivery.channel_id -> alerts_channel ON DELETE RESTRICT. A channel with delivery history is disabled, not deleted. Orphans are recorded in qqq_fk_quarantine and nulled first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_delivery_channel_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_delivery', 'channel_id', CAST(c.id AS VARCHAR(100)), CAST(c.channel_id AS VARCHAR(100)), 'NULLED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:47' FROM alerts_delivery c WHERE c.channel_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_channel p WHERE p.id = c.channel_id);
+UPDATE alerts_delivery c SET channel_id = NULL WHERE c.channel_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_channel p WHERE p.id = c.channel_id);
+ALTER TABLE alerts_delivery ADD CONSTRAINT fk_alerts_delivery_channel_id FOREIGN KEY (channel_id) REFERENCES alerts_channel (id) ON DELETE RESTRICT;
+-- rollback ALTER TABLE alerts_delivery DROP CONSTRAINT IF EXISTS fk_alerts_delivery_channel_id;
+
+-- changeset qbit-alerts:48 dbms:postgresql
+-- comment: alerts_subscription.team_id -> alerts_team ON DELETE RESTRICT. A team subscription needs its team (AlertsSubscriptionCustomizer); remove the subscriptions before the team. Orphans are recorded in qqq_fk_quarantine and nulled first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_subscription_team_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_subscription', 'team_id', CAST(c.id AS VARCHAR(100)), CAST(c.team_id AS VARCHAR(100)), 'NULLED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:48' FROM alerts_subscription c WHERE c.team_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_team p WHERE p.id = c.team_id);
+UPDATE alerts_subscription c SET team_id = NULL WHERE c.team_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_team p WHERE p.id = c.team_id);
+ALTER TABLE alerts_subscription ADD CONSTRAINT fk_alerts_subscription_team_id FOREIGN KEY (team_id) REFERENCES alerts_team (id) ON DELETE RESTRICT;
+-- rollback ALTER TABLE alerts_subscription DROP CONSTRAINT IF EXISTS fk_alerts_subscription_team_id;
+
+-- changeset qbit-alerts:49 dbms:postgresql
+-- comment: alerts_subscription.channel_id -> alerts_channel ON DELETE RESTRICT. A channel subscription needs its channel (AlertsSubscriptionCustomizer); remove the subscriptions before the channel. Orphans are recorded in qqq_fk_quarantine and nulled first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_subscription_channel_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_subscription', 'channel_id', CAST(c.id AS VARCHAR(100)), CAST(c.channel_id AS VARCHAR(100)), 'NULLED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:49' FROM alerts_subscription c WHERE c.channel_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_channel p WHERE p.id = c.channel_id);
+UPDATE alerts_subscription c SET channel_id = NULL WHERE c.channel_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_channel p WHERE p.id = c.channel_id);
+ALTER TABLE alerts_subscription ADD CONSTRAINT fk_alerts_subscription_channel_id FOREIGN KEY (channel_id) REFERENCES alerts_channel (id) ON DELETE RESTRICT;
+-- rollback ALTER TABLE alerts_subscription DROP CONSTRAINT IF EXISTS fk_alerts_subscription_channel_id;
+
+-- changeset qbit-alerts:50 dbms:postgresql
+-- comment: alerts_team_member.team_id -> alerts_team ON DELETE CASCADE. Members belong to their team. Orphans are recorded in qqq_fk_quarantine and deleted first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_team_member_team_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_team_member', 'team_id', CAST(c.id AS VARCHAR(100)), CAST(c.team_id AS VARCHAR(100)), 'DELETED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:50' FROM alerts_team_member c WHERE c.team_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_team p WHERE p.id = c.team_id);
+DELETE FROM alerts_team_member c WHERE c.team_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_team p WHERE p.id = c.team_id);
+ALTER TABLE alerts_team_member ADD CONSTRAINT fk_alerts_team_member_team_id FOREIGN KEY (team_id) REFERENCES alerts_team (id) ON DELETE CASCADE;
+-- rollback ALTER TABLE alerts_team_member DROP CONSTRAINT IF EXISTS fk_alerts_team_member_team_id;
+
+-- changeset qbit-alerts:51 dbms:postgresql
+-- comment: alerts_notification_recipient.notification_id -> alerts_notification ON DELETE CASCADE. Recipient rows belong to their notification (NotificationService.purge deletes them first; this covers any other delete). Orphans are recorded in qqq_fk_quarantine and deleted first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_notification_recipient_notification_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_notification_recipient', 'notification_id', CAST(c.id AS VARCHAR(100)), CAST(c.notification_id AS VARCHAR(100)), 'DELETED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:51' FROM alerts_notification_recipient c WHERE c.notification_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_notification p WHERE p.id = c.notification_id);
+DELETE FROM alerts_notification_recipient c WHERE c.notification_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_notification p WHERE p.id = c.notification_id);
+ALTER TABLE alerts_notification_recipient ADD CONSTRAINT fk_alerts_notification_recipient_notification_id FOREIGN KEY (notification_id) REFERENCES alerts_notification (id) ON DELETE CASCADE;
+-- rollback ALTER TABLE alerts_notification_recipient DROP CONSTRAINT IF EXISTS fk_alerts_notification_recipient_notification_id;
+
+-- changeset qbit-alerts:52 dbms:postgresql
+-- comment: alerts_escalation_step.policy_id -> alerts_escalation_policy ON DELETE CASCADE. Steps belong to their policy. Orphans are recorded in qqq_fk_quarantine and deleted first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_escalation_step_policy_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_escalation_step', 'policy_id', CAST(c.id AS VARCHAR(100)), CAST(c.policy_id AS VARCHAR(100)), 'DELETED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:52' FROM alerts_escalation_step c WHERE c.policy_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_escalation_policy p WHERE p.id = c.policy_id);
+DELETE FROM alerts_escalation_step c WHERE c.policy_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_escalation_policy p WHERE p.id = c.policy_id);
+ALTER TABLE alerts_escalation_step ADD CONSTRAINT fk_alerts_escalation_step_policy_id FOREIGN KEY (policy_id) REFERENCES alerts_escalation_policy (id) ON DELETE CASCADE;
+-- rollback ALTER TABLE alerts_escalation_step DROP CONSTRAINT IF EXISTS fk_alerts_escalation_step_policy_id;
+
+-- changeset qbit-alerts:53 dbms:postgresql
+-- comment: alerts_escalation_step.team_id -> alerts_team ON DELETE RESTRICT. A team step needs its team (ck_alerts_escalation_step_target forbids a NULL), so an orphan step is removed, not nulled. Orphans are recorded in qqq_fk_quarantine and deleted first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_escalation_step_team_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_escalation_step', 'team_id', CAST(c.id AS VARCHAR(100)), CAST(c.team_id AS VARCHAR(100)), 'DELETED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:53' FROM alerts_escalation_step c WHERE c.team_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_team p WHERE p.id = c.team_id);
+DELETE FROM alerts_escalation_step c WHERE c.team_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_team p WHERE p.id = c.team_id);
+ALTER TABLE alerts_escalation_step ADD CONSTRAINT fk_alerts_escalation_step_team_id FOREIGN KEY (team_id) REFERENCES alerts_team (id) ON DELETE RESTRICT;
+-- rollback ALTER TABLE alerts_escalation_step DROP CONSTRAINT IF EXISTS fk_alerts_escalation_step_team_id;
+
+-- changeset qbit-alerts:54 dbms:postgresql
+-- comment: alerts_escalation_step.channel_id -> alerts_channel ON DELETE RESTRICT. A channel step needs its channel (ck_alerts_escalation_step_target forbids a NULL), so an orphan step is removed, not nulled. Orphans are recorded in qqq_fk_quarantine and deleted first.
+-- preconditions onFail:MARK_RAN onError:HALT
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.table_constraints WHERE LOWER(constraint_name) = 'fk_alerts_escalation_step_channel_id'
+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);
+INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) SELECT 'alerts_escalation_step', 'channel_id', CAST(c.id AS VARCHAR(100)), CAST(c.channel_id AS VARCHAR(100)), 'DELETED', CAST(row_to_json(c) AS TEXT), 'qbit-alerts:54' FROM alerts_escalation_step c WHERE c.channel_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_channel p WHERE p.id = c.channel_id);
+DELETE FROM alerts_escalation_step c WHERE c.channel_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM alerts_channel p WHERE p.id = c.channel_id);
+ALTER TABLE alerts_escalation_step ADD CONSTRAINT fk_alerts_escalation_step_channel_id FOREIGN KEY (channel_id) REFERENCES alerts_channel (id) ON DELETE RESTRICT;
+-- rollback ALTER TABLE alerts_escalation_step DROP CONSTRAINT IF EXISTS fk_alerts_escalation_step_channel_id;
@@ -0,0 +1,419 @@
+/*
+ * Copyright 2026 Kingsrook, LLC
+ *
+ * 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
+ *
+ *     http://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.alerts;
+
+
+import java.io.InputStream;
+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.Set;
+import java.util.TreeMap;
+import java.util.TreeSet;
+import com.kingsrook.qbits.alerts.metadata.AlertsTableMetaDataHelper;
+import com.kingsrook.qqq.backend.core.instances.QInstanceEnricher;
+import com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldMetaData;
+import com.kingsrook.qqq.backend.core.model.metadata.joins.QJoinMetaData;
+import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
+import liquibase.Contexts;
+import liquibase.LabelExpression;
+import liquibase.Liquibase;
+import liquibase.database.Database;
+import liquibase.database.DatabaseFactory;
+import liquibase.database.jvm.JdbcConnection;
+import liquibase.resource.ClassLoaderResourceAccessor;
+import liquibase.resource.DirectoryResourceAccessor;
+import liquibase.resource.ResourceAccessor;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.extension.ExtendWith;
+import org.junit.jupiter.api.io.TempDir;
+import org.testcontainers.containers.PostgreSQLContainer;
+import org.testcontainers.junit.jupiter.Container;
+import org.testcontainers.junit.jupiter.Testcontainers;
+import static org.assertj.core.api.Assertions.assertThat;
+import static org.assertj.core.api.Assertions.assertThatThrownBy;
+
+
+/*******************************************************************************
+ ** Referential integrity guardrail: the shipped changelog, applied by Liquibase
+ ** to PostgreSQL, gives every relation the metadata declares (one-to-many joins
+ ** and table possible-value fields) a foreign key with the decided ON DELETE
+ ** rule, led by an index; old orphan rows are quarantined, not lost; and a host
+ ** that already added a constraint under the same name skips it.
+ *******************************************************************************/
+@ExtendWith(RequiresDockerCondition.class)
+@Testcontainers
+class AlertsForeignKeysTest
+{
+   private static final String CHANGELOG = "db/liquibase/qbit-alerts/alerts.changelog.sql";
+
+   /////////////////////////////////////////////////////////////////////////////////////
+   // child_table.child_column -> parent_table and ON DELETE rule (pg_constraint       //
+   // confdeltype: c CASCADE, n SET NULL, r RESTRICT). Add a row here with the new     //
+   // changeset when a table gains a relation; reasons are in the changeset comments. //
+   /////////////////////////////////////////////////////////////////////////////////////
+   private static final Map<String, String> EXPECTED = new TreeMap<>(Map.ofEntries(
+      Map.entry("alerts_type.group_id", "alerts_group n"),
+      Map.entry("alerts_type.escalation_policy_id", "alerts_escalation_policy n"),
+      Map.entry("alerts_group.parent_group_id", "alerts_group n"),
+      Map.entry("alerts_group.owner_team_id", "alerts_team n"),
+      Map.entry("alerts_group.escalation_policy_id", "alerts_escalation_policy n"),
+      Map.entry("alerts_monitor.alert_type_id", "alerts_type r"),
+      Map.entry("alerts_alert.alert_type_id", "alerts_type r"),
+      Map.entry("alerts_alert.monitor_id", "alerts_monitor n"),
+      Map.entry("alerts_alert.group_id", "alerts_group n"),
+      Map.entry("alerts_alert.assigned_team_id", "alerts_team n"),
+      Map.entry("alerts_record_ref.alert_id", "alerts_alert c"),
+      Map.entry("alerts_event.alert_id", "alerts_alert c"),
+      Map.entry("alerts_delivery.alert_id", "alerts_alert c"),
+      Map.entry("alerts_delivery.alert_event_id", "alerts_event n"),
+      Map.entry("alerts_delivery.subscription_id", "alerts_subscription n"),
+      Map.entry("alerts_delivery.channel_id", "alerts_channel r"),
+      Map.entry("alerts_subscription.team_id", "alerts_team r"),
+      Map.entry("alerts_subscription.channel_id", "alerts_channel r"),
+      Map.entry("alerts_team_member.team_id", "alerts_team c"),
+      Map.entry("alerts_notification_recipient.notification_id", "alerts_notification c"),
+      Map.entry("alerts_escalation_step.policy_id", "alerts_escalation_policy c"),
+      Map.entry("alerts_escalation_step.team_id", "alerts_team r"),
+      Map.entry("alerts_escalation_step.channel_id", "alerts_channel r")
+   ));
+
+   //////////////////////////////////////////////////////////////////////////////////////
+   // declared relations that deliberately have no foreign key, with the reason. None //
+   // today: the polymorphic references (alerts_alert.subject_table_name/record_id,   //
+   // alerts_record_ref.table_name/record_id, alerts_notification.source_table_name/  //
+   // source_record_id, subscription and mute scope_value/scope_record_id) and the    //
+   // id lists (channel_override_ids, default_channel_ids) are not declared as joins  //
+   // or table possible values, so they never reach this check.                       //
+   //////////////////////////////////////////////////////////////////////////////////////
+   private static final Map<String, String> EXEMPT = Map.of();
+
+   @Container
+   static final PostgreSQLContainer<?> POSTGRES = new PostgreSQLContainer<>("postgres:17-alpine");
+
+   @TempDir
+   Path tempDir;
+
+
+
+   /*******************************************************************************
+    ** Start every test from an empty schema.
+    *******************************************************************************/
+   @BeforeEach
+   void emptySchema() throws Exception
+   {
+      try(Connection connection = connect(); Statement statement = connection.createStatement())
+      {
+         statement.execute("DROP SCHEMA public CASCADE");
+         statement.execute("CREATE SCHEMA public");
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Every declared relation has its foreign key with the decided rule, there
+    ** are no undecided ones, and every foreign-key column leads an index.
+    *******************************************************************************/
+   @Test
+   void testEveryRelationHasForeignKey() throws Exception
+   {
+      update(new ClassLoaderResourceAccessor());
+      Map<String, String> actual = foreignKeys();
+
+      for(String relation : declaredRelations().keySet())
+      {
+         if(EXEMPT.containsKey(relation))
+         {
+            continue;
+         }
+         assertThat(actual).as("a foreign key (or an EXEMPT entry with its reason) for " + relation + " -> " + declaredRelations().get(relation)).containsKey(relation);
+         assertThat(actual.get(relation)).as("foreign key " + relation + " references " + declaredRelations().get(relation))
+            .startsWith(declaredRelations().get(relation) + " ");
+      }
+      assertThat(actual).as("foreign keys and their ON DELETE rules").isEqualTo(EXPECTED);
+      assertThat(unindexedForeignKeys()).as("foreign-key columns no index leads with").isEmpty();
+   }
+
+
+
+   /*******************************************************************************
+    ** Orphans from before the constraints: recorded, then nulled (nullable
+    ** column) or deleted (NOT NULL column, full row kept); valid rows untouched.
+    ** Running the changelog again changes nothing.
+    *******************************************************************************/
+   @Test
+   void testOrphansAreQuarantined() throws Exception
+   {
+      update(preForeignKeyChangelog());
+      try(Connection connection = connect(); Statement statement = connection.createStatement())
+      {
+         statement.execute("INSERT INTO alerts_team (id, name, label) VALUES (1, 'ops', 'Ops')");
+         statement.execute("INSERT INTO alerts_team_member (id, team_id, user_id) VALUES (1, 1, 'kept'), (2, 999, 'orphan')");
+         statement.execute("INSERT INTO alerts_type (id, name, group_id) VALUES (1, 'fk.test', 999)");
+         statement.execute("INSERT INTO alerts_monitor (id, name, alert_type_id, kind) VALUES (1, 'orphan monitor', 999, 'TABLE')");
+         statement.execute("INSERT INTO alerts_alert (id, alert_type_id, monitor_id, group_id, assigned_team_id) VALUES (1, 1, 1, 999, 1)");
+         statement.execute("INSERT INTO alerts_event (id, alert_id, event_type) VALUES (1, 1, 'RAISED'), (2, 999, 'RAISED')");
+         statement.execute("INSERT INTO alerts_delivery (id, alert_id, alert_event_id, channel_id) VALUES (1, 1, 2, 999)");
+         statement.execute("INSERT INTO alerts_notification_recipient (id, notification_id, user_id) VALUES (1, 999, 'u')");
+         statement.execute("INSERT INTO alerts_escalation_policy (id, name) VALUES (1, 'p')");
+         statement.execute("INSERT INTO alerts_escalation_step (id, policy_id, step_order, after_minutes, target_type, team_id) VALUES (1, 1, 1, 0, 'TEAM', 999), (2, 1, 2, 5, 'TEAM', 1)");
+      }
+
+      update(new ClassLoaderResourceAccessor());
+      Set<String> expected = Set.of(
+         "alerts_type.group_id 1 999 NULLED",
+         "alerts_monitor.alert_type_id 1 999 DELETED",
+         "alerts_alert.monitor_id 1 1 NULLED",
+         "alerts_alert.group_id 1 999 NULLED",
+         "alerts_event.alert_id 2 999 DELETED",
+         "alerts_delivery.alert_event_id 1 2 NULLED",
+         "alerts_delivery.channel_id 1 999 NULLED",
+         "alerts_team_member.team_id 2 999 DELETED",
+         "alerts_notification_recipient.notification_id 1 999 DELETED",
+         "alerts_escalation_step.team_id 1 999 DELETED");
+      assertThat(quarantined()).isEqualTo(expected);
+
+      try(Connection connection = connect(); Statement statement = connection.createStatement())
+      {
+         assertThat(queryRow(statement, "SELECT alert_type_id, monitor_id, group_id, assigned_team_id FROM alerts_alert WHERE id = 1")).containsExactly("1", null, null, "1");
+         assertThat(queryRow(statement, "SELECT alert_id, alert_event_id, channel_id FROM alerts_delivery WHERE id = 1")).containsExactly("1", null, null);
+         assertThat(queryRow(statement, "SELECT COUNT(*) FROM alerts_team_member WHERE user_id = 'kept'")).containsExactly("1");
+         assertThat(queryRow(statement, "SELECT COUNT(*) FROM alerts_escalation_step WHERE id = 2")).containsExactly("1");
+         assertThat(queryRow(statement, "SELECT row_data FROM qqq_fk_quarantine WHERE table_name = 'alerts_monitor'").get(0)).contains("\"orphan monitor\"");
+      }
+
+      update(new ClassLoaderResourceAccessor());
+      assertThat(quarantined()).as("a second run quarantines nothing more").isEqualTo(expected);
+      assertThat(foreignKeys()).isEqualTo(EXPECTED);
+   }
+
+
+
+   /*******************************************************************************
+    ** A host that added a constraint itself (same name) marks that changeset
+    ** ran instead of failing on a duplicate constraint.
+    *******************************************************************************/
+   @Test
+   void testExistingConstraintIsSkipped() throws Exception
+   {
+      update(preForeignKeyChangelog());
+      try(Connection connection = connect(); Statement statement = connection.createStatement())
+      {
+         statement.execute("ALTER TABLE alerts_event ADD CONSTRAINT fk_alerts_event_alert_id FOREIGN KEY (alert_id) REFERENCES alerts_alert (id) ON DELETE CASCADE");
+      }
+      update(new ClassLoaderResourceAccessor());
+      assertThat(foreignKeys()).isEqualTo(EXPECTED);
+   }
+
+
+
+   /*******************************************************************************
+    ** The rules at work: an alert takes its timeline, records and delivery log
+    ** with it; a used channel and the type of a monitor cannot be deleted; a
+    ** deleted team unassigns its alerts and takes its members.
+    *******************************************************************************/
+   @Test
+   void testOnDeleteRules() throws Exception
+   {
+      update(new ClassLoaderResourceAccessor());
+      try(Connection connection = connect(); Statement statement = connection.createStatement())
+      {
+         statement.execute("INSERT INTO alerts_type (id, name) VALUES (1, 't')");
+         statement.execute("INSERT INTO alerts_monitor (id, name, alert_type_id, kind) VALUES (1, 'm', 1, 'TABLE')");
+         statement.execute("INSERT INTO alerts_team (id, name, label) VALUES (1, 'ops', 'Ops')");
+         statement.execute("INSERT INTO alerts_team_member (team_id, user_id) VALUES (1, 'u')");
+         statement.execute("INSERT INTO alerts_channel (id, name, label, channel_type) VALUES (1, 'c', 'C', 'EMAIL')");
+         statement.execute("INSERT INTO alerts_alert (id, alert_type_id, monitor_id, assigned_team_id) VALUES (1, 1, 1, 1)");
+         statement.execute("INSERT INTO alerts_event (id, alert_id) VALUES (1, 1)");
+         statement.execute("INSERT INTO alerts_record_ref (alert_id) VALUES (1)");
+         statement.execute("INSERT INTO alerts_delivery (alert_id, alert_event_id, channel_id) VALUES (1, 1, 1)");
+
+         assertThatThrownBy(() -> statement.execute("DELETE FROM alerts_channel WHERE id = 1")).isInstanceOf(SQLException.class);
+         assertThatThrownBy(() -> statement.execute("DELETE FROM alerts_type WHERE id = 1")).isInstanceOf(SQLException.class);
+
+         statement.execute("DELETE FROM alerts_team WHERE id = 1");
+         assertThat(queryRow(statement, "SELECT assigned_team_id FROM alerts_alert WHERE id = 1")).containsExactly((String) null);
+         assertThat(queryRow(statement, "SELECT COUNT(*) FROM alerts_team_member")).containsExactly("0");
+
+         statement.execute("DELETE FROM alerts_alert WHERE id = 1");
+         assertThat(queryRow(statement, "SELECT (SELECT COUNT(*) FROM alerts_event) + (SELECT COUNT(*) FROM alerts_record_ref) + (SELECT COUNT(*) FROM alerts_delivery)")).containsExactly("0");
+         statement.execute("DELETE FROM alerts_channel WHERE id = 1");
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** child_table.child_column -> parent_table for every one-to-many join and
+    ** every field whose possible values are an alerts table.
+    *******************************************************************************/
+   private static Map<String, String> declaredRelations() throws Exception
+   {
+      Map<String, String> relations = new TreeMap<>();
+      Set<String>         tables    = new TreeSet<>(AlertsQBitConfig.allTableNames());
+      for(QTableMetaData table : AlertsTableMetaDataHelper.buildAllTables(new AlertsQBitConfig()))
+      {
+         for(QFieldMetaData field : table.getFields().values())
+         {
+            if(field.getPossibleValueSourceName() != null && tables.contains(field.getPossibleValueSourceName()))
+            {
+               relations.put(table.getName() + "." + QInstanceEnricher.inferBackendName(field.getName()), field.getPossibleValueSourceName());
+            }
+         }
+      }
+      for(QJoinMetaData join : AlertsTableMetaDataHelper.buildJoins())
+      {
+         relations.put(join.getRightTable() + "." + QInstanceEnricher.inferBackendName(join.getJoinOns().get(0).getRightField()), join.getLeftTable());
+      }
+      return (relations);
+   }
+
+
+
+   /*******************************************************************************
+    ** The changelog up to (not including) the foreign-key changesets, at the
+    ** same path, so the full changelog later continues where it stopped.
+    *******************************************************************************/
+   private ResourceAccessor preForeignKeyChangelog() throws Exception
+   {
+      String changelog;
+      try(InputStream in = getClass().getClassLoader().getResourceAsStream(CHANGELOG))
+      {
+         changelog = new String(in.readAllBytes(), StandardCharsets.UTF_8);
+      }
+      Path file = tempDir.resolve(CHANGELOG);
+      Files.createDirectories(file.getParent());
+      Files.writeString(file, changelog.substring(0, changelog.indexOf("-- changeset qbit-alerts:31")));
+      return (new DirectoryResourceAccessor(tempDir));
+   }
+
+
+
+   /*******************************************************************************
+    ** Run Liquibase update with the changelog from the given accessor.
+    *******************************************************************************/
+   private static void update(ResourceAccessor accessor) throws Exception
+   {
+      Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(connect()));
+      try(Liquibase liquibase = new Liquibase(CHANGELOG, accessor, database))
+      {
+         liquibase.update(new Contexts(), new LabelExpression());
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** child_table.child_column -> "parent_table rule" for every foreign key.
+    *******************************************************************************/
+   private static Map<String, String> foreignKeys() throws Exception
+   {
+      Map<String, String> keys = new TreeMap<>();
+      try(Connection connection = connect(); Statement statement = connection.createStatement();
+         ResultSet result = statement.executeQuery("""
+            SELECT c.conrelid::regclass::text, a.attname, c.confrelid::regclass::text, c.confdeltype
+            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 'alerts%'"""))
+      {
+         while(result.next())
+         {
+            keys.put(result.getString(1) + "." + result.getString(2), result.getString(3) + " " + result.getString(4));
+         }
+      }
+      return (keys);
+   }
+
+
+
+   /*******************************************************************************
+    ** Foreign keys whose column does not lead any index on the child table.
+    *******************************************************************************/
+   private static List<String> unindexedForeignKeys() throws Exception
+   {
+      List<String> missing = new ArrayList<>();
+      try(Connection connection = connect(); Statement statement = connection.createStatement();
+         ResultSet result = statement.executeQuery("""
+            SELECT c.conname FROM pg_constraint c
+            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(result.next())
+         {
+            missing.add(result.getString(1));
+         }
+      }
+      return (missing);
+   }
+
+
+
+   /*******************************************************************************
+    ** The quarantine log as "table.column rowId missingParentId action".
+    *******************************************************************************/
+   private static Set<String> quarantined() throws Exception
+   {
+      Set<String> rows = new TreeSet<>();
+      try(Connection connection = connect(); Statement statement = connection.createStatement();
+         ResultSet result = statement.executeQuery("SELECT table_name, column_name, row_id, missing_parent_id, action, changeset FROM qqq_fk_quarantine"))
+      {
+         while(result.next())
+         {
+            assertThat(result.getString(6)).startsWith("qbit-alerts:");
+            rows.add(result.getString(1) + "." + result.getString(2) + " " + result.getString(3) + " " + result.getString(4) + " " + result.getString(5));
+         }
+      }
+      return (rows);
+   }
+
+
+
+   /*******************************************************************************
+    ** The first row of a query, as strings.
+    *******************************************************************************/
+   private static List<String> queryRow(Statement statement, String sql) throws SQLException
+   {
+      try(ResultSet result = statement.executeQuery(sql))
+      {
+         assertThat(result.next()).as(sql).isTrue();
+         List<String> values = new ArrayList<>();
+         for(int i = 1; i <= result.getMetaData().getColumnCount(); i++)
+         {
+            values.add(result.getString(i));
+         }
+         return (values);
+      }
+   }
+
+
+
+   /*******************************************************************************
+    **
+    *******************************************************************************/
+   private static Connection connect() throws SQLException
+   {
+      return (DriverManager.getConnection(POSTGRES.getJdbcUrl(), POSTGRES.getUsername(), POSTGRES.getPassword()));
+   }
+}
@@ -283,7 +283,12 @@ class AlertsQBitProducerTest extends BaseAlertsTest
      {
         changelog = new String(in.readAllBytes(), StandardCharsets.UTF_8);
      }
-      String sql = changelog.lines().filter(line -> !line.stripLeading().startsWith("--")).collect(Collectors.joining("\n"));
+      ////////////////////////////////////////////////////////////////////////////////////
+      // the foreign-key changesets (31+) use PostgreSQL functions; AlertsForeignKeysTest //
+      // runs the whole changelog on PostgreSQL                                           //
+      ////////////////////////////////////////////////////////////////////////////////////
+      String preForeignKeys = changelog.substring(0, changelog.indexOf("-- changeset qbit-alerts:31"));
+      String sql            = preForeignKeys.lines().filter(line -> !line.stripLeading().startsWith("--")).collect(Collectors.joining("\n"));
      try(Connection connection = DriverManager.getConnection("jdbc:h2:mem:alerts_digest_migration"); Statement statement = connection.createStatement())
      {
         for(String command : sql.split(";"))
@@ -413,4 +418,35 @@ class AlertsQBitProducerTest extends BaseAlertsTest
         com.kingsrook.qqq.backend.core.actions.permissions.TablePermissionSubType.INSERT)).isTrue();
   }

+
+
+   /*******************************************************************************
+    ** Every one-to-many join's child column leads an index in the shipped
+    ** changelog, so record pages, child lists and per-parent lookups do not scan
+    ** the child table.
+    *******************************************************************************/
+   @Test
+   void testJoinChildColumnsAreIndexed() throws Exception
+   {
+      String changelog;
+      try(InputStream in = getClass().getClassLoader().getResourceAsStream("db/liquibase/qbit-alerts/alerts.changelog.sql"))
+      {
+         changelog = new String(in.readAllBytes(), StandardCharsets.UTF_8);
+      }
+      for(com.kingsrook.qqq.backend.core.model.metadata.joins.QJoinMetaData join : AlertsTableMetaDataHelper.buildJoins())
+      {
+         String table  = join.getRightTable();
+         String column = join.getJoinOns().get(0).getRightField().replaceAll("([a-z0-9])([A-Z])", "$1_$2").toLowerCase();
+         java.util.regex.Pattern index = java.util.regex.Pattern.compile("(?i)CREATE (UNIQUE )?INDEX (IF NOT EXISTS )?\\w+ ON " + table + " \\(" + column + "[,)]");
+         java.util.regex.Pattern unique = java.util.regex.Pattern.compile("(?is)CREATE TABLE IF NOT EXISTS " + table + " \\((?:(?!\\);).)*UNIQUE \\(" + column + "[,)]");
+         //////////////////////////////////////////////////////////////////////
+         // alerts_delivery was renamed from the RC.1 alerts_notification;   //
+         // its indexes came along by ALTER INDEX ... RENAME TO ix_<t>_<col> //
+         //////////////////////////////////////////////////////////////////////
+         boolean renamed = changelog.contains("RENAME TO ix_" + table + "_" + column + ";") || changelog.contains("RENAME TO ix_" + table + "_" + column + "_");
+         assertThat(index.matcher(changelog).find() || unique.matcher(changelog).find() || renamed)
+            .as("index leading with " + table + "." + column + " for join " + join.getName())
+            .isTrue();
+      }
+   }
 }
@@ -26,9 +26,10 @@ import java.util.Map;
 import java.util.regex.Matcher;
 import java.util.regex.Pattern;
 import java.util.stream.Stream;
-import com.kingsrook.qbits.alerts.metadata.AlertsHelp;
-import com.kingsrook.qqq.backend.core.model.metadata.help.QHelpContent;
+import com.kingsrook.qbits.alerts.metadata.AlertsProcessesMetaDataProducer;
+import com.kingsrook.qqq.backend.core.model.metadata.help.bundles.HelpBundles;
 import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
+import com.kingsrook.qqq.backend.core.testing.HelpBundleAssertions;
 import org.junit.jupiter.api.Test;
 import static org.assertj.core.api.Assertions.assertThat;

@@ -58,7 +59,7 @@ class AlertsTablesGuardrailTest extends BaseAlertsTest
      Map.entry("alerts_event", "runtime: the alert timeline"),
      Map.entry("alerts_subscription", "people's own choices; the first alerts admin gets one by the bootstrap rule (AlertsPreferences)"),
      Map.entry("alerts_user_preference", "user content: created per person on their first visit (AlertsPreferences)"),
-      Map.entry("alerts_team", "the host's own people (kof22 seeds its team)"),
+      Map.entry("alerts_team", "the host's own people"),
      Map.entry("alerts_team_member", "the host's own people"),
      Map.entry("alerts_mute", "user content: maintenance windows and mutes people create"),
      Map.entry("alerts_delivery", "runtime log: every delivery attempt"),
@@ -122,31 +123,44 @@ class AlertsTablesGuardrailTest extends BaseAlertsTest
   @Test
   void everyTableHasEmptyStateHelp() throws Exception
   {
-      assertThat(AlertsHelp.entries()).allSatisfy((key, entry) -> assertThat(entry.path("summary").asText().length()).as(key).isLessThanOrEqualTo(120));
+      // Core adds bulk CRUD processes during validation; the qbit owns its declared actions and jobs.
+      var processes = AlertsProcessesMetaDataProducer.buildProcesses(config).stream().map(process -> process.getName()).toList();
+      HelpBundleAssertions.forInstance(qInstance)
+         .bundles("help/qbit-alerts.yaml")
+         .owning(target -> "process".equals(target.kind()) ? processes.contains(target.name()) : target.name().startsWith("alerts"))
+         .assertComplete();
+      var entries = HelpBundles.of(qInstance).getBundles().get(0).getEntries();
+      // Core's gate does not yet require widget and possible-value-source help.
+      var keys = entries.stream().map(entry -> entry.getKey()).toList();
+      qInstance.getWidgets().keySet().stream().filter(name -> name.startsWith("alerts"))
+         .forEach(name -> assertThat(keys).contains("widget:" + name + ";slot:help"));
+      qInstance.getPossibleValueSources().keySet().stream().filter(name -> name.startsWith("alerts"))
+         .forEach(name -> assertThat(keys).contains("possibleValueSource:" + name + ";slot:help"));
      for(String table : alertsTables())
      {
-         List<QHelpContent> help = qInstance.getTable(table).getHelpContent() == null ? null : qInstance.getTable(table).getHelpContent().get(AlertsHelp.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(entries).anySatisfy(entry ->
+         {
+            assertThat(entry.getKey()).isEqualTo("table:" + table + ";slot:help");
+            assertThat(entry.getContent()).contains("How rows get created");
+         });
      }
-      assertThat(qInstance.getApp(AlertsQBitConfig.APP_NAME).getHelpContent()).containsKey(AlertsHelp.SLOT);
-      assertThat(AlertsHelp.entries().keySet()).allMatch(key -> !key.startsWith("table:") || qInstance.getTable(key.substring(6, key.indexOf(';'))) != null,
-         "help names only real tables");
   }



   /*******************************************************************************
-    ** A host's own help on a table is kept; the bundle fills only empty slots.
+    ** Bundle registration needs no help table; core safely skips startup sync.
    *******************************************************************************/
   @Test
-   void hostHelpIsNotOverridden() throws Exception
+   void hostWithoutHelpTableIsSupported()
   {
-      QTableMetaData table = qInstance.getTable("alerts_channel");
-      table.getHelpContent().remove(AlertsHelp.SLOT);
-      table.withHelpContent(AlertsHelp.SLOT, new QHelpContent().withContentAsMarkdown("host text"));
-      AlertsHelp.apply(qInstance);
-      assertThat(table.getHelpContent().get(AlertsHelp.SLOT)).singleElement().matches(c -> "host text".equals(c.getContent()));
+      assertThat(qInstance.getTable("helpContent")).isNull();
+      assertThat(HelpBundles.of(qInstance).getBundles()).singleElement().satisfies(bundle ->
+      {
+         assertThat(bundle.getId()).isEqualTo("qbit-alerts");
+         assertThat(bundle.getVersion()).isEqualTo("0.1.0-SNAPSHOT");
+      });
+      HelpBundles.syncOnStartup(qInstance);
   }


@@ -0,0 +1,58 @@
+/*
+ * Copyright 2026 Kingsrook, LLC
+ *
+ * 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
+ *
+ *     http://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.alerts;
+
+
+import org.junit.jupiter.api.extension.ConditionEvaluationResult;
+import org.junit.jupiter.api.extension.ExecutionCondition;
+import org.junit.jupiter.api.extension.ExtensionContext;
+import org.testcontainers.DockerClientFactory;
+
+
+/*******************************************************************************
+ ** Runs a PostgreSQL (Testcontainers) test when Docker is available; skips it
+ ** on a workstation without Docker, and fails when CI is set but Docker is not.
+ *******************************************************************************/
+public class RequiresDockerCondition implements ExecutionCondition
+{
+
+   /*******************************************************************************
+    **
+    *******************************************************************************/
+   @Override
+   public ConditionEvaluationResult evaluateExecutionCondition(ExtensionContext context)
+   {
+      boolean available;
+      try
+      {
+         available = DockerClientFactory.instance().isDockerAvailable();
+      }
+      catch(Exception | LinkageError e)
+      {
+         available = false;
+      }
+      if(available)
+      {
+         return (ConditionEvaluationResult.enabled("Docker is available"));
+      }
+      if(System.getenv("CI") != null)
+      {
+         throw new IllegalStateException("Docker is required when CI is set");
+      }
+      return (ConditionEvaluationResult.disabled("Docker is unavailable; the PostgreSQL schema tests need it"));
+   }
+}
@@ -172,6 +172,232 @@ class NotificationEmailAndNoticesTest extends BaseAlertsTest



+   /*******************************************************************************
+    ** A requested email does not require a standing category preference.
+    *******************************************************************************/
+   @Test
+   void testRequestedWithoutPreference() throws Exception
+   {
+      addressOnly();
+      requested("JOB");
+      assertThat(new NotificationEmailer(runtime).run().sent()).isEqualTo(1);
+      assertThat(emailStatus("viewer-1")).isEqualTo("SENT");
+      assertThat(new NotificationEmailer(runtime).run().sent()).isZero();
+      assertThat(SENT).hasSize(1);
+   }
+
+
+
+   /*******************************************************************************
+    ** An explicit request and an immediate preference still send only once.
+    *******************************************************************************/
+   @Test
+   void testRequestedWithImmediatePreference() throws Exception
+   {
+      emailSettings("viewer-1", "JOB", "IMMEDIATE", "INFO");
+      requested("JOB");
+      assertThat(new NotificationEmailer(runtime).run().sent()).isEqualTo(1);
+      assertThat(new NotificationEmailer(runtime).run().sent()).isZero();
+      assertThat(SENT).hasSize(1);
+   }
+
+
+
+   /*******************************************************************************
+    ** A request overrides digest timing (not the category's minimum severity).
+    *******************************************************************************/
+   @Test
+   void testRequestedWithDigestPreference() throws Exception
+   {
+      emailSettings("viewer-1", "JOB", "DAILY_DIGEST", "INFO");
+      requested("JOB");
+      NotificationEmailer.Result result = new NotificationEmailer(runtime).run();
+      assertThat(result.sent()).isEqualTo(1);
+      assertThat(result.digests()).isZero();
+      assertThat(SENT).hasSize(1);
+   }
+
+
+
+   /*******************************************************************************
+    ** Knowing an address alone does not opt a notification into email.
+    *******************************************************************************/
+   @Test
+   void testNotRequestedWithoutPreference() throws Exception
+   {
+      addressOnly();
+      runtime.getNotifications().notify(new NotificationSignal("Done").withCategory(NotificationCategory.JOB)
+         .withAudience(NotificationAudience.user("viewer-1")));
+      assertThat(new NotificationEmailer(runtime).run().sent()).isZero();
+      assertThat(emailStatus("viewer-1")).isEqualTo("NONE");
+      assertThat(SENT).isEmpty();
+   }
+
+
+
+   /*******************************************************************************
+    ** Missing addresses are skipped rather than counted as delivery failures.
+    *******************************************************************************/
+   @Test
+   void testRequestedWithoutAddress() throws Exception
+   {
+      requested("JOB");
+      NotificationEmailer.Result result = new NotificationEmailer(runtime).run();
+      assertThat(result.skipped()).isEqualTo(1);
+      assertThat(result.failed()).isZero();
+      assertThat(emailStatus("viewer-1")).isEqualTo("SKIPPED");
+      assertThat(SENT).isEmpty();
+   }
+
+
+
+   /*******************************************************************************
+    ** Alert subscriptions own alert emails, even with an explicit request.
+    *******************************************************************************/
+   @Test
+   void testRequestedAlertNeverEmailed() throws Exception
+   {
+      emailSettings("viewer-1", "ALERT", "IMMEDIATE", "INFO");
+      requested("ALERT");
+      assertThat(new NotificationEmailer(runtime).run().skipped()).isEqualTo(1);
+      assertThat(emailStatus("viewer-1")).isEqualTo("SKIPPED");
+      assertThat(SENT).isEmpty();
+   }
+
+
+
+   /*******************************************************************************
+    ** Requests older than the delivery window are not emailed.
+    *******************************************************************************/
+   @Test
+   void testRequestedOutsideDeliveryWindow() throws Exception
+   {
+      addressOnly();
+      requested("JOB");
+      clock.set(clock.instant().plusSeconds(86401));
+      assertThat(new NotificationEmailer(runtime).run().sent()).isZero();
+      assertThat(emailStatus("viewer-1")).isEqualTo("REQUESTED");
+      assertThat(SENT).isEmpty();
+   }
+
+
+
+   /*******************************************************************************
+    ** Provider failures remain terminal and do not cause repeated sends.
+    *******************************************************************************/
+   @Test
+   void testRequestedDeliveryFailure() throws Exception
+   {
+      addressOnly();
+      requested("JOB");
+      runtime.getConfig().withMessagingProviderName("missing-provider");
+      NotificationEmailer.Result result = new NotificationEmailer(runtime).run();
+      assertThat(result.failed()).isEqualTo(1);
+      assertThat(emailStatus("viewer-1")).isEqualTo("FAILED");
+      assertThat(new NotificationEmailer(runtime).run().failed()).isZero();
+      assertThat(SENT).isEmpty();
+   }
+
+
+
+   /*******************************************************************************
+    ** A request still honours the category's email minimum severity.
+    *******************************************************************************/
+   @Test
+   void testRequestedBelowMinSeverity() throws Exception
+   {
+      emailSettings("viewer-1", "JOB", "OFF", "ERROR");
+      requested("JOB");
+      NotificationEmailer.Result result = new NotificationEmailer(runtime).run();
+      assertThat(result.skipped()).isEqualTo(1);
+      assertThat(emailStatus("viewer-1")).isEqualTo("SKIPPED");
+      assertThat(SENT).isEmpty();
+   }
+
+
+
+   /*******************************************************************************
+    ** A user's pause or quiet hours hold a requested email until they end.
+    *******************************************************************************/
+   @Test
+   void testRequestedHeldByPauseAndQuietHours() throws Exception
+   {
+      clock.set(Instant.parse("2026-10-12T12:00:00Z"));
+      addressOnly();
+      requested("JOB");
+      setUserPreference(new QRecord().withValue("pausedUntil", clock.instant().plusSeconds(3600)));
+      assertThat(new NotificationEmailer(runtime).run().sent()).isZero();
+      assertThat(emailStatus("viewer-1")).isEqualTo("REQUESTED");
+
+      clock.set(Instant.parse("2026-10-12T13:30:00Z"));
+      setUserPreference(new QRecord().withValue("timeZone", "UTC").withValue("quietHoursStart", "13:00").withValue("quietHoursEnd", "14:00"));
+      assertThat(new NotificationEmailer(runtime).run().sent()).isZero();
+      assertThat(emailStatus("viewer-1")).isEqualTo("REQUESTED");
+
+      clock.set(Instant.parse("2026-10-12T14:00:00Z"));
+      assertThat(new NotificationEmailer(runtime).run().sent()).isEqualTo(1);
+      assertThat(emailStatus("viewer-1")).isEqualTo("SENT");
+      assertThat(SENT).hasSize(1);
+   }
+
+
+
+   /*******************************************************************************
+    ** A pause does not hold a requested SECURITY email.
+    *******************************************************************************/
+   @Test
+   void testRequestedSecurityBypassesPause() throws Exception
+   {
+      addressOnly();
+      requested("SECURITY");
+      setUserPreference(new QRecord().withValue("pausedUntil", clock.instant().plusSeconds(3600)));
+      assertThat(new NotificationEmailer(runtime).run().sent()).isEqualTo(1);
+   }
+
+
+
+   /*******************************************************************************
+    ** Update the user's alert settings row.
+    *******************************************************************************/
+   private void setUserPreference(QRecord values) throws QException
+   {
+      runtime.asSystem(() ->
+      {
+         QRecord setting = AlertsDb.first(AlertsUserPreference.TABLE_NAME, AlertsDb.eq("userId", "viewer-1"));
+         return (AlertsDb.update(AlertsUserPreference.TABLE_NAME, values.withValue("id", setting.getValue("id"))));
+      });
+   }
+
+
+
+   /*******************************************************************************
+    ** Store an address without opting into any category.
+    *******************************************************************************/
+   private void addressOnly() throws QException
+   {
+      runtime.asSystem(() -> AlertsDb.insert(AlertsUserPreference.TABLE_NAME,
+         new QRecord().withValue("userId", "viewer-1").withValue("emailAddress", "viewer-1@example.com")));
+   }
+
+
+
+   /*******************************************************************************
+    ** Set up a recipient explicitly requesting email for worker tests.
+    *******************************************************************************/
+   private void requested(String category) throws QException
+   {
+      Integer id = runtime.getNotifications().notify(new NotificationSignal("Done").withCategory(NotificationCategory.valueOf(category))
+         .withAudience(NotificationAudience.user("viewer-1")));
+      runtime.asSystem(() ->
+      {
+         QRecord recipient = AlertsDb.first(AlertsNotificationRecipient.TABLE_NAME, AlertsDb.eq("notificationId", id));
+         return (AlertsDb.update(AlertsNotificationRecipient.TABLE_NAME,
+            new QRecord().withValue("id", recipient.getValue("id")).withValue("emailStatus", "REQUESTED")));
+      });
+   }
+
+
+
   /*******************************************************************************
    ** The daily digest waits for the user's hour, then lists the unread ones.
    *******************************************************************************/
@@ -1,25 +1,25 @@
-DROP TABLE IF EXISTS alerts_escalation_step;
-DROP TABLE IF EXISTS alerts_escalation_policy;
+DROP TABLE IF EXISTS alerts_escalation_step CASCADE;
+DROP TABLE IF EXISTS alerts_escalation_policy CASCADE;
 -- H2 version of src/main/resources/db/liquibase/qbit-alerts/alerts.changelog.sql, for unit tests (generated together).

-DROP TABLE IF EXISTS alerts_heartbeat;
-DROP TABLE IF EXISTS alerts_inbox_item;
-DROP TABLE IF EXISTS alerts_notification_preference;
-DROP TABLE IF EXISTS alerts_notification_recipient;
-DROP TABLE IF EXISTS alerts_notification;
-DROP TABLE IF EXISTS alerts_delivery;
-DROP TABLE IF EXISTS alerts_mute;
-DROP TABLE IF EXISTS alerts_channel;
-DROP TABLE IF EXISTS alerts_team_member;
-DROP TABLE IF EXISTS alerts_team;
-DROP TABLE IF EXISTS alerts_user_preference;
-DROP TABLE IF EXISTS alerts_subscription;
-DROP TABLE IF EXISTS alerts_event;
-DROP TABLE IF EXISTS alerts_record_ref;
-DROP TABLE IF EXISTS alerts_alert;
-DROP TABLE IF EXISTS alerts_monitor;
-DROP TABLE IF EXISTS alerts_type;
-DROP TABLE IF EXISTS alerts_group;
+DROP TABLE IF EXISTS alerts_heartbeat CASCADE;
+DROP TABLE IF EXISTS alerts_inbox_item CASCADE;
+DROP TABLE IF EXISTS alerts_notification_preference CASCADE;
+DROP TABLE IF EXISTS alerts_notification_recipient CASCADE;
+DROP TABLE IF EXISTS alerts_notification CASCADE;
+DROP TABLE IF EXISTS alerts_delivery CASCADE;
+DROP TABLE IF EXISTS alerts_mute CASCADE;
+DROP TABLE IF EXISTS alerts_channel CASCADE;
+DROP TABLE IF EXISTS alerts_team_member CASCADE;
+DROP TABLE IF EXISTS alerts_team CASCADE;
+DROP TABLE IF EXISTS alerts_user_preference CASCADE;
+DROP TABLE IF EXISTS alerts_subscription CASCADE;
+DROP TABLE IF EXISTS alerts_event CASCADE;
+DROP TABLE IF EXISTS alerts_record_ref CASCADE;
+DROP TABLE IF EXISTS alerts_alert CASCADE;
+DROP TABLE IF EXISTS alerts_monitor CASCADE;
+DROP TABLE IF EXISTS alerts_type CASCADE;
+DROP TABLE IF EXISTS alerts_group CASCADE;

 CREATE TABLE IF NOT EXISTS alerts_group (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
@@ -441,3 +441,40 @@ ALTER TABLE alerts_group ADD COLUMN escalation_policy_id INTEGER;
 -- preconditions onFail:MARK_RAN onError:HALT
 -- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.columns WHERE LOWER(table_name) = 'alerts_alert' AND LOWER(column_name) = 'timed_notification_checked_at'
 ALTER TABLE alerts_alert ADD COLUMN timed_notification_checked_at TIMESTAMP;
+
+-- Foreign keys and their indexes (changesets 30-53).
+CREATE INDEX IF NOT EXISTS ix_alerts_alert_group_id ON alerts_alert (group_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_alert_assigned_team_id ON alerts_alert (assigned_team_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_type_escalation_policy_id ON alerts_type (escalation_policy_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_group_parent_group_id ON alerts_group (parent_group_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_group_owner_team_id ON alerts_group (owner_team_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_group_escalation_policy_id ON alerts_group (escalation_policy_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_delivery_alert_event_id ON alerts_delivery (alert_event_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_delivery_subscription_id ON alerts_delivery (subscription_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_subscription_team_id ON alerts_subscription (team_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_subscription_channel_id ON alerts_subscription (channel_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_escalation_step_team_id ON alerts_escalation_step (team_id);
+CREATE INDEX IF NOT EXISTS ix_alerts_escalation_step_channel_id ON alerts_escalation_step (channel_id);
+ALTER TABLE alerts_type ADD CONSTRAINT fk_alerts_type_group_id FOREIGN KEY (group_id) REFERENCES alerts_group (id) ON DELETE SET NULL;
+ALTER TABLE alerts_type ADD CONSTRAINT fk_alerts_type_escalation_policy_id FOREIGN KEY (escalation_policy_id) REFERENCES alerts_escalation_policy (id) ON DELETE SET NULL;
+ALTER TABLE alerts_group ADD CONSTRAINT fk_alerts_group_parent_group_id FOREIGN KEY (parent_group_id) REFERENCES alerts_group (id) ON DELETE SET NULL;
+ALTER TABLE alerts_group ADD CONSTRAINT fk_alerts_group_owner_team_id FOREIGN KEY (owner_team_id) REFERENCES alerts_team (id) ON DELETE SET NULL;
+ALTER TABLE alerts_group ADD CONSTRAINT fk_alerts_group_escalation_policy_id FOREIGN KEY (escalation_policy_id) REFERENCES alerts_escalation_policy (id) ON DELETE SET NULL;
+ALTER TABLE alerts_monitor ADD CONSTRAINT fk_alerts_monitor_alert_type_id FOREIGN KEY (alert_type_id) REFERENCES alerts_type (id) ON DELETE RESTRICT;
+ALTER TABLE alerts_alert ADD CONSTRAINT fk_alerts_alert_alert_type_id FOREIGN KEY (alert_type_id) REFERENCES alerts_type (id) ON DELETE RESTRICT;
+ALTER TABLE alerts_alert ADD CONSTRAINT fk_alerts_alert_monitor_id FOREIGN KEY (monitor_id) REFERENCES alerts_monitor (id) ON DELETE SET NULL;
+ALTER TABLE alerts_alert ADD CONSTRAINT fk_alerts_alert_group_id FOREIGN KEY (group_id) REFERENCES alerts_group (id) ON DELETE SET NULL;
+ALTER TABLE alerts_alert ADD CONSTRAINT fk_alerts_alert_assigned_team_id FOREIGN KEY (assigned_team_id) REFERENCES alerts_team (id) ON DELETE SET NULL;
+ALTER TABLE alerts_record_ref ADD CONSTRAINT fk_alerts_record_ref_alert_id FOREIGN KEY (alert_id) REFERENCES alerts_alert (id) ON DELETE CASCADE;
+ALTER TABLE alerts_event ADD CONSTRAINT fk_alerts_event_alert_id FOREIGN KEY (alert_id) REFERENCES alerts_alert (id) ON DELETE CASCADE;
+ALTER TABLE alerts_delivery ADD CONSTRAINT fk_alerts_delivery_alert_id FOREIGN KEY (alert_id) REFERENCES alerts_alert (id) ON DELETE CASCADE;
+ALTER TABLE alerts_delivery ADD CONSTRAINT fk_alerts_delivery_alert_event_id FOREIGN KEY (alert_event_id) REFERENCES alerts_event (id) ON DELETE SET NULL;
+ALTER TABLE alerts_delivery ADD CONSTRAINT fk_alerts_delivery_subscription_id FOREIGN KEY (subscription_id) REFERENCES alerts_subscription (id) ON DELETE SET NULL;
+ALTER TABLE alerts_delivery ADD CONSTRAINT fk_alerts_delivery_channel_id FOREIGN KEY (channel_id) REFERENCES alerts_channel (id) ON DELETE RESTRICT;
+ALTER TABLE alerts_subscription ADD CONSTRAINT fk_alerts_subscription_team_id FOREIGN KEY (team_id) REFERENCES alerts_team (id) ON DELETE RESTRICT;
+ALTER TABLE alerts_subscription ADD CONSTRAINT fk_alerts_subscription_channel_id FOREIGN KEY (channel_id) REFERENCES alerts_channel (id) ON DELETE RESTRICT;
+ALTER TABLE alerts_team_member ADD CONSTRAINT fk_alerts_team_member_team_id FOREIGN KEY (team_id) REFERENCES alerts_team (id) ON DELETE CASCADE;
+ALTER TABLE alerts_notification_recipient ADD CONSTRAINT fk_alerts_notification_recipient_notification_id FOREIGN KEY (notification_id) REFERENCES alerts_notification (id) ON DELETE CASCADE;
+ALTER TABLE alerts_escalation_step ADD CONSTRAINT fk_alerts_escalation_step_policy_id FOREIGN KEY (policy_id) REFERENCES alerts_escalation_policy (id) ON DELETE CASCADE;
+ALTER TABLE alerts_escalation_step ADD CONSTRAINT fk_alerts_escalation_step_team_id FOREIGN KEY (team_id) REFERENCES alerts_team (id) ON DELETE RESTRICT;
+ALTER TABLE alerts_escalation_step ADD CONSTRAINT fk_alerts_escalation_step_channel_id FOREIGN KEY (channel_id) REFERENCES alerts_channel (id) ON DELETE RESTRICT;
@@ -0,0 +1,17 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<Configuration status="WARN">
+   <Appenders>
+      <Console name="Console" target="SYSTEM_OUT">
+         <!-- Core can emit diagnostic events at OFF; exclude those as well. -->
+         <LevelRangeFilter minLevel="FATAL" maxLevel="WARN" onMatch="NEUTRAL" onMismatch="DENY"/>
+         <PatternLayout pattern="%d{HH:mm:ss} %-5level %logger{36} - %msg%n"/>
+      </Console>
+   </Appenders>
+   <Loggers>
+      <Logger name="org.testcontainers" level="WARN"/>
+      <Logger name="com.github.dockerjava" level="WARN"/>
+      <Logger name="org.apache.hc" level="WARN"/>
+      <Logger name="tc" level="WARN"/>
+      <Root level="WARN"><AppenderRef ref="Console"/></Root>
+   </Loggers>
+</Configuration>
Author	SHA1	Message	Date
james.maesandClaude Opus 5.5	e5d31991eb	fix(help): the help bundle carries the artifact version (Maven-filtered) instead of a hardcoded SNAPSHOT On release branches the bundle's version and the producer's differed, so the bundle test that asserts they match failed, and released bundles recorded SNAPSHOT as their provenance. help/*.yaml is now filtered: version: ${project.version}. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-14 22:02:04 -05:00
james.maes	fc04d0a4fb	Merge pull request #38 from QRun-IO/feature/help-bundle feat(help): Alerts help bundle via QQQ help bundles, with coverage gate	2026-10-14 20:29:38 -05:00
james.maesandClaude Opus 5.5	50e1b52a4d	feat(help): ship the Alerts help bundle through QQQ help bundles (419 entries, coverage gate) Host-neutral text (kof22-specific facts removed), registered with HelpBundles in the producer, gated by HelpBundleAssertions. Replaces the temporary AlertsHelp loader. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-14 20:01:53 -05:00
james.maes	2dcd6cd3e4	Merge pull request #37 from QRun-IO/feature/notify-email-requested-v2 feat(notifications): email REQUESTED recipients immediately (per-job opt-in), v2	2026-10-14 18:51:03 -05:00
james.maesandClaude Opus 5.5	04104ad4aa	feat(notifications): email REQUESTED recipients immediately (per-job opt-in), v2 Merges feature/notify-email-requested (#22) onto current develop and reconciles it with the paginated emailer, user pause, quiet hours and the notification preferences. REQUESTED email honours the category email minimum severity, pause (SECURITY bypasses) and quiet hours. No schema change, so no new changeset. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-14 18:48:28 -05:00
james.maes	c37d5d3f2e	Merge pull request #35 from QRun-IO/feature/foreign-keys feat(schema): foreign keys for every alerts relation (changesets 30-53, after #32)	2026-10-14 18:00:42 -05:00
james.maes	0143caf4c9	Merge pull request #32 from QRun-IO/feature/alerts-join-indexes fix(schema): index the join child columns monitor_id, alert_type_id and group_id	2026-10-14 18:00:34 -05:00
james.maes	8a4791508c	Merge pull request #33 from QRun-IO/feature/bootstrap-subscription-once fix(preferences): create the bootstrap subscription once per user	2026-10-14 18:00:27 -05:00
james.maesandClaude Opus 5.5	adb7fd7cec	Merge feature/alerts-join-indexes (now with develop) into feature/foreign-keys develop owns qbit-alerts:29 and the join indexes are now :30, so the foreign-key changesets move from 30-53 to 31-54. Constraint and index names are unchanged. Changesets 32-54 use PostgreSQL-only SQL (row_to_json), so they carry dbms:postgresql and the H2 tests skip them. The H2 digest test and AlertsForeignKeysTest's pre-FK cut now stop before :31. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-14 17:52:37 -05:00
james.maesandClaude Opus 5.5	28545fa126	Merge origin/develop into feature/alerts-join-indexes develop now owns qbit-alerts:29 (in_app_min_severity), so the join-index changeset becomes qbit-alerts:30. Index names are unchanged. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-14 17:50:41 -05:00
james.maesandClaude Opus 5.5	6e70e02623	test(schema): PostgreSQL guardrail for foreign keys on every declared relation AlertsForeignKeysTest applies the shipped changelog with Liquibase to PostgreSQL 17 (Testcontainers) and asserts every one-to-many join and table possible-value field has a foreign key with the decided ON DELETE rule (or an exemption with a reason), every FK column leads an index, orphans are quarantined then nulled or deleted, re-runs are no-ops, and a constraint a host already added is skipped. The H2 digest-migration test stops before the PostgreSQL-only FK changesets. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-14 16:18:31 -05:00
james.maesandClaude Opus 5.5	2e1e22cdd4	feat(schema): foreign keys for every alerts relation, orphan-safe (changesets 30-53) Changeset 30 indexes the FK columns no earlier index leads with. Changesets 31-53 add one constraint each, fk_<child_table>_<child_column>, guarded by a precondition on the name so a host that already added it marks it ran. Each first records orphans in qqq_fk_quarantine, then nulls a nullable column or deletes a row whose column is NOT NULL (full row kept in row_data). ON DELETE: CASCADE for owned detail rows (timeline, record refs, delivery log of an alert, team members, notification recipients, escalation steps); SET NULL for optional pointers (group, parent group, owner/assigned team, monitor, subscription, alert event, escalation policy); RESTRICT for config that must not vanish under live data (alert type, a channel or team that a subscription, delivery or escalation step uses). The H2 test schema carries the same constraints and indexes. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-14 16:18:30 -05:00
james.maesandClaude Opus 5.5	b54d8d103c	fix(schema): index the join child columns monitor_id, alert_type_id and group_id Changeset 29 adds alerts_alert (monitor_id, status), alerts_monitor (alert_type_id) and alerts_type (group_id). MonitorEvaluator looks up a per-record monitor's open alerts by monitor_id + status on every evaluation, and the type and group record pages list children by these columns. A new test checks that every one-to-many join's child column leads an index in the shipped changelog. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-14 15:18:11 -05:00
james.maesandClaude Opus 5.5	2202037c00	feat(notifications): email REQUESTED recipients immediately (per-job opt-in) A recipient with emailStatus REQUESTED is emailed at once, without a standing category preference or minimum severity, and only once even when an IMMEDIATE preference also applies. No address: SKIPPED. ALERT notifications are still never emailed here. This is the delivery half of the durable-jobs "Email me when done" opt-in (QRun-IO/qqq durable jobs spec 2.11); marking recipients REQUESTED from core's NotificationSignal attribute emailRequested lands with the RC.6 core notify() adoption. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-13 22:14:23 -05:00