Merge origin/develop into feature/bootstrap-subscription-once

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

pom.xml

@@ -24,8 +24,6 @@
       <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>
@@ -106,31 +104,6 @@
          <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>

src/main/java/com/kingsrook/qbits/alerts/AlertsPreferences.java

@@ -17,6 +17,8 @@
 package com.kingsrook.qbits.alerts;
 
 
+import java.io.Serializable;
+import java.util.List;
 import java.util.Set;
 import java.util.concurrent.ConcurrentHashMap;
 import com.kingsrook.qbits.alerts.engine.AlertsDb;
@@ -26,7 +28,12 @@ import com.kingsrook.qbits.alerts.model.AlertsPossibleValues.ScopeType;
 import com.kingsrook.qbits.alerts.model.AlertsPossibleValues.SubscriberType;
 import com.kingsrook.qbits.alerts.model.AlertsSubscription;
 import com.kingsrook.qbits.alerts.model.AlertsUserPreference;
+import com.kingsrook.qqq.backend.core.actions.tables.DeleteAction;
+import com.kingsrook.qqq.backend.core.context.QContext;
 import com.kingsrook.qqq.backend.core.exceptions.QException;
+import com.kingsrook.qqq.backend.core.model.actions.tables.delete.DeleteInput;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QCriteriaOperator;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QFilterOrderBy;
 import com.kingsrook.qqq.backend.core.model.data.QRecord;
 import com.kingsrook.qqq.backend.core.model.session.QSession;
 
@@ -42,6 +49,9 @@ import com.kingsrook.qqq.backend.core.model.session.QSession;
  *******************************************************************************/
 public final class AlertsPreferences
 {
+   /** marks the bootstrap rule's subscription (createdByUserId). */
+   static final String BOOTSTRAP_CREATOR = "alerts (bootstrap rule)";
+
    private static final Set<String> ENSURED = ConcurrentHashMap.newKeySet();
 
 
@@ -69,36 +79,72 @@ public final class AlertsPreferences
 
       boolean admin    = session.hasPermission(AlertsQBitConfig.PERMISSION_ADMIN);
       String  fullName = session.getUser() == null ? null : session.getUser().getFullName();
-      runtime.asSystem(() ->
-      {
-         if(AlertsDb.first(AlertsUserPreference.TABLE_NAME, AlertsDb.eq("userId", userId)) == null)
-         {
-            AlertsDb.insert(AlertsUserPreference.TABLE_NAME, new QRecord()
-               .withValue("userId", userId)
-               .withValue("userLabel", fullName == null ? userId : fullName)
-               .withValue("inAppEnabled", true)
-               .withValue("emailEnabled", true)
-               .withValue("quietBypassSeverity", "CRITICAL")
-               .withValue("digestHour", 8));
-         }
 
-         if(admin && runtime.getConfig().getBootstrapMinSeverity() != null && AlertsDb.first(AlertsSubscription.TABLE_NAME, AlertsDb.eq("userId", userId)) == null)
+      ////////////////////////////////////////////////////////////////////////////
+      // A page load fires the home widgets and the bell together, so first     //
+      // calls for one user race. Serialize them in this JVM; across replicas,  //
+      // the clean-up below keeps one bootstrap subscription (the lowest id).   //
+      ////////////////////////////////////////////////////////////////////////////
+      synchronized(AlertsPreferences.class)
+      {
+         if(ENSURED.contains(userId))
          {
-            QRecord inApp = runtime.getChannels().defaultChannel(ChannelType.IN_APP);
-            AlertsDb.insert(AlertsSubscription.TABLE_NAME, new QRecord()
-               .withValue("subscriberType", SubscriberType.USER.name())
-               .withValue("userId", userId)
-               .withValue("scopeType", ScopeType.ALL.name())
-               .withValue("minSeverity", runtime.getConfig().getBootstrapMinSeverity().name())
-               .withValue("events", "RAISED,ESCALATED,REMINDER,RESOLVED")
-               .withValue("deliveryMode", DeliveryMode.IMMEDIATE.name())
-               .withValue("channelOverrideIds", String.valueOf(inApp.getValue("id")))
-               .withValue("enabled", true)
-               .withValue("createdByUserId", "alerts (bootstrap rule)"));
+            return;
          }
-         return (null);
-      });
-      ENSURED.add(userId);
+         runtime.asSystem(() ->
+         {
+            if(AlertsDb.first(AlertsUserPreference.TABLE_NAME, AlertsDb.eq("userId", userId)) == null)
+            {
+               AlertsDb.insert(AlertsUserPreference.TABLE_NAME, new QRecord()
+                  .withValue("userId", userId)
+                  .withValue("userLabel", fullName == null ? userId : fullName)
+                  .withValue("inAppEnabled", true)
+                  .withValue("emailEnabled", true)
+                  .withValue("quietBypassSeverity", "CRITICAL")
+                  .withValue("digestHour", 8));
+            }
+
+            if(admin && runtime.getConfig().getBootstrapMinSeverity() != null && AlertsDb.first(AlertsSubscription.TABLE_NAME, AlertsDb.eq("userId", userId)) == null)
+            {
+               QRecord inApp = runtime.getChannels().defaultChannel(ChannelType.IN_APP);
+               AlertsDb.insert(AlertsSubscription.TABLE_NAME, new QRecord()
+                  .withValue("subscriberType", SubscriberType.USER.name())
+                  .withValue("userId", userId)
+                  .withValue("scopeType", ScopeType.ALL.name())
+                  .withValue("minSeverity", runtime.getConfig().getBootstrapMinSeverity().name())
+                  .withValue("events", "RAISED,ESCALATED,REMINDER,RESOLVED")
+                  .withValue("deliveryMode", DeliveryMode.IMMEDIATE.name())
+                  .withValue("channelOverrideIds", String.valueOf(inApp.getValue("id")))
+                  .withValue("enabled", true)
+                  .withValue("createdByUserId", BOOTSTRAP_CREATOR));
+            }
+
+            removeDuplicateBootstrapSubscriptions(userId);
+            return (null);
+         });
+         ENSURED.add(userId);
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Keep only the first bootstrap subscription of a user. Two replicas can
+    ** each see no subscription and insert one; every later first call (and the
+    ** losers' own calls) delete the extra copies, so the user is not notified
+    ** twice per alert. Subscriptions a person made are never touched.
+    *******************************************************************************/
+   static void removeDuplicateBootstrapSubscriptions(String userId) throws QException
+   {
+      List<QRecord> bootstrap = AlertsDb.query(AlertsSubscription.TABLE_NAME, AlertsDb.eq("userId", userId)
+         .withCriteria("createdByUserId", QCriteriaOperator.EQUALS, BOOTSTRAP_CREATOR)
+         .withOrderBy(new QFilterOrderBy("id")));
+      if(bootstrap.size() < 2)
+      {
+         return;
+      }
+      List<Serializable> extra = bootstrap.subList(1, bootstrap.size()).stream().map(r -> (Serializable) r.getValue("id")).toList();
+      new DeleteAction().execute(new DeleteInput(AlertsSubscription.TABLE_NAME).withPrimaryKeys(extra).withTransaction(QContext.getQBackendTransaction()));
    }
 
 

src/main/resources/db/liquibase/qbit-alerts/alerts.changelog.sql

@@ -8,10 +8,6 @@
 -- 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.
@@ -523,255 +519,3 @@ 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;

src/test/java/com/kingsrook/qbits/alerts/AlertsForeignKeysTest.java

@@ -1,419 +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;
-
-
-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()));
-   }
-}

src/test/java/com/kingsrook/qbits/alerts/AlertsQBitProducerTest.java

@@ -283,12 +283,7 @@ class AlertsQBitProducerTest extends BaseAlertsTest
       {
          changelog = new String(in.readAllBytes(), StandardCharsets.UTF_8);
       }
-      ////////////////////////////////////////////////////////////////////////////////////
-      // 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"));
+      String sql = changelog.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(";"))
@@ -418,35 +413,4 @@ 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();
-      }
-   }
 }

src/test/java/com/kingsrook/qbits/alerts/RequiresDockerCondition.java

@@ -1,58 +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;
-
-
-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"));
-   }
-}

src/test/java/com/kingsrook/qbits/alerts/engine/RoutingAndDeliveryTest.java

@@ -135,6 +135,76 @@ class RoutingAndDeliveryTest extends BaseAlertsTest
 
 
 
+   /*******************************************************************************
+    ** First calls for one user race (the home widgets and the bell load
+    ** together): only one bootstrap subscription is created.
+    *******************************************************************************/
+   @Test
+   void testConcurrentFirstCallsBootstrapOnce() throws Exception
+   {
+      com.kingsrook.qqq.backend.core.context.CapturedContext context = QContext.capture();
+      java.util.concurrent.ExecutorService pool = java.util.concurrent.Executors.newFixedThreadPool(4);
+      try
+      {
+         java.util.concurrent.CountDownLatch start = new java.util.concurrent.CountDownLatch(1);
+         List<java.util.concurrent.Future<Object>> futures = new java.util.ArrayList<>();
+         for(int i = 0; i < 4; i++)
+         {
+            futures.add(pool.submit(() ->
+            {
+               QContext.init(context);
+               start.await();
+               AlertsPreferences.ensure(runtime, QContext.getQSession());
+               return (null);
+            }));
+         }
+         start.countDown();
+         for(java.util.concurrent.Future<Object> future : futures)
+         {
+            future.get();
+         }
+      }
+      finally
+      {
+         pool.shutdownNow();
+      }
+      assertThat(all(AlertsSubscription.TABLE_NAME)).hasSize(1);
+      assertThat(all(AlertsUserPreference.TABLE_NAME)).hasSize(1);
+   }
+
+
+
+   /*******************************************************************************
+    ** Another replica raced and left a second bootstrap subscription: the next
+    ** first call keeps the oldest and leaves a person's own subscriptions alone.
+    *******************************************************************************/
+   @Test
+   void testDuplicateBootstrapSubscriptionsAreRemoved() throws Exception
+   {
+      for(String creator : List.of("alerts (bootstrap rule)", "alerts (bootstrap rule)", ADMIN))
+      {
+         runtime.asSystem(() -> AlertsDb.insert(AlertsSubscription.TABLE_NAME, new QRecord()
+            .withValue("subscriberType", "USER")
+            .withValue("userId", ADMIN)
+            .withValue("scopeType", "ALL")
+            .withValue("minSeverity", "ERROR")
+            .withValue("events", "RAISED")
+            .withValue("deliveryMode", "IMMEDIATE")
+            .withValue("enabled", true)
+            .withValue("createdByUserId", creator)));
+      }
+      List<QRecord> before = all(AlertsSubscription.TABLE_NAME);
+      assertThat(before).hasSize(3);
+
+      AlertsPreferences.ensure(runtime, QContext.getQSession());
+
+      List<QRecord> after = all(AlertsSubscription.TABLE_NAME);
+      assertThat(after).hasSize(2);
+      assertThat(after).extracting(r -> r.getValueInteger("id")).containsExactlyInAnyOrder(before.get(0).getValueInteger("id"), before.get(2).getValueInteger("id"));
+   }
+
+
+
    /*******************************************************************************
     ** No subscriptions: nobody is notified. The bootstrap rule gives admins an
     ** in-app subscription to ERROR and worse on first visit.

src/test/resources/alerts-h2.sql

@@ -1,25 +1,25 @@
-DROP TABLE IF EXISTS alerts_escalation_step CASCADE;
-DROP TABLE IF EXISTS alerts_escalation_policy CASCADE;
+DROP TABLE IF EXISTS alerts_escalation_step;
+DROP TABLE IF EXISTS alerts_escalation_policy;
 -- H2 version of src/main/resources/db/liquibase/qbit-alerts/alerts.changelog.sql, for unit tests (generated together).
 
-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;
+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;
 
 CREATE TABLE IF NOT EXISTS alerts_group (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
@@ -441,40 +441,3 @@ 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;

src/test/resources/log4j2-test.xml

@@ -1,17 +0,0 @@
-<?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>