feat(findings): suggestions engine, finding actions, alerts and health checks

Fourteen rules read their db_perf_rule row and produce deduplicated,
ranked findings with evidence and ready-to-commit Liquibase snippets
(never applied). Acknowledge, Mark resolved, Ignore and Reopen are
standard record actions. Collection failures, query spikes, connection
saturation and blocked chains raise and resolve keyed QAlerts; the
collector, stats access and connections register core health checks.

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

src/main/java/com/kingsrook/qbits/dbperformance/DbPerfQBitProducer.java

@@ -138,6 +138,8 @@ public class DbPerfQBitProducer implements QBitMetaDataProducer<DbPerfQBitConfig
       DbPerfMetaData.addTo(instance, qBitConfig);
       new DbPerfRuntime(instance, qBitConfig).addSelfToInstance(instance);
       com.kingsrook.qbits.dbperformance.processes.DbPerfProcesses.addTo(instance, qBitConfig);
+      com.kingsrook.qbits.dbperformance.alerts.SnapshotEvaluator.register(instance, qBitConfig);
+      com.kingsrook.qbits.dbperformance.health.DbPerfHealthChecks.register(instance);
       return (new MetaDataProducerMultiOutput());
    }
 }

src/main/java/com/kingsrook/qbits/dbperformance/alerts/SnapshotEvaluator.java

@@ -0,0 +1,214 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2026.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dbperformance.alerts;
+
+
+import java.time.Instant;
+import java.util.Comparator;
+import java.util.List;
+import java.util.Map;
+import java.util.stream.Collectors;
+import com.kingsrook.qbits.dbperformance.DbPerfQBitConfig;
+import com.kingsrook.qbits.dbperformance.DbPerfRuntime;
+import com.kingsrook.qbits.dbperformance.analyze.RuleContext;
+import com.kingsrook.qbits.dbperformance.collect.DbPerfStore;
+import com.kingsrook.qbits.dbperformance.normalize.SqlNormalizer;
+import com.kingsrook.qqq.backend.core.alerts.AlertRecordRef;
+import com.kingsrook.qqq.backend.core.alerts.AlertSeverity;
+import com.kingsrook.qqq.backend.core.alerts.AlertSignal;
+import com.kingsrook.qqq.backend.core.alerts.AlertsInstanceMetaData;
+import com.kingsrook.qqq.backend.core.alerts.QAlertTypeMetaData;
+import com.kingsrook.qqq.backend.core.alerts.QAlerts;
+import com.kingsrook.qqq.backend.core.logging.QLogger;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
+
+
+/*******************************************************************************
+ ** Optional QAlerts signals from committed observations. Alert delivery cannot
+ ** turn a successfully collected snapshot into a failed collection.
+ *******************************************************************************/
+public class SnapshotEvaluator
+{
+   public static final String COLLECTION_FAILED = "dbperf.collection-failed";
+   public static final String QUERY_SPIKE = "dbperf.query-spike";
+   public static final String CONNECTION_SATURATION = "dbperf.connection-saturation";
+   public static final String BLOCKED_CHAIN = "dbperf.blocked-chain";
+   private static final QLogger LOG = QLogger.getLogger(SnapshotEvaluator.class);
+   private final DbPerfRuntime runtime;
+
+
+   /*******************************************************************************
+    ** Instance-bound evaluator; the core resolves the optional alert sink.
+    *******************************************************************************/
+   public SnapshotEvaluator(DbPerfRuntime runtime)
+   {
+      this.runtime = runtime;
+   }
+
+
+   /*******************************************************************************
+    ** Alert types are discoverable even without an installed Alerts QBit.
+    *******************************************************************************/
+   public static void register(QInstance instance, DbPerfQBitConfig config)
+   {
+      Map<String, String> labels = Map.of(COLLECTION_FAILED, "Database performance collection failed", QUERY_SPIKE, "Database query time spike",
+         CONNECTION_SATURATION, "Database connections saturated", BLOCKED_CHAIN, "Database blocking chain");
+      Map<String, String> descriptions = Map.of(COLLECTION_FAILED, "Collection failed; resolves after the next OK snapshot.",
+         QUERY_SPIKE, "Interval query time exceeds its seven-day median and configured floor.",
+         CONNECTION_SATURATION, "Observed connections reached the configured percentage of capacity.",
+         BLOCKED_CHAIN, "A blocked session exceeded the configured duration.");
+      for(String type : List.of(COLLECTION_FAILED, QUERY_SPIKE, CONNECTION_SATURATION, BLOCKED_CHAIN))
+      {
+         AlertsInstanceMetaData.ofOrWithNew(instance).withAlertType(new QAlertTypeMetaData().withName(type).withLabel(labels.get(type))
+            .withDescription(descriptions.get(type)).withSourceQBitName("db-performance")
+            .withDefaultSeverity(type.equals(COLLECTION_FAILED) ? AlertSeverity.ERROR : AlertSeverity.WARNING)
+            .withSubjectTableName(type.equals(QUERY_SPIKE) ? "db_perf_statement" : "db_perf_snapshot")
+            .withVisibilityPermission(config.getPermissionBaseName() + ".view"));
+      }
+   }
+
+
+   /*******************************************************************************
+    ** A failed evaluation is logged without SQL, credentials or exception text.
+    *******************************************************************************/
+   public void evaluate(QRecord snapshot)
+   {
+      try
+      {
+         runtime.asSystem(() ->
+         {
+            evaluateStored(snapshot);
+            return (null);
+         });
+      }
+      catch(Exception e)
+      {
+         LOG.warn("Database performance alert evaluation could not read stored observations");
+      }
+   }
+
+
+   /*******************************************************************************
+    ** Read the current settings every pass. Failed/partial collection cannot
+    ** clear connection, blocking or query signals without fresh measurements.
+    *******************************************************************************/
+   private void evaluateStored(QRecord snapshot) throws Exception
+   {
+      String backend = snapshot.getValueString("backendName");
+      QRecord settings = DbPerfStore.first("db_perf_settings", DbPerfStore.eq("backendName", backend));
+      if(settings == null || !Boolean.TRUE.equals(settings.getValueBoolean("alertsEnabled")))
+      {
+         return;
+      }
+      String status = snapshot.getValueString("status");
+      if("FAILED".equals(status))
+      {
+         signal(COLLECTION_FAILED, backend, null, true, "Database performance collection failed", snapshot.getValueString("errorMessage"), snapshot, "db_perf_snapshot");
+         return;
+      }
+      if("OK".equals(status))
+      {
+         signal(COLLECTION_FAILED, backend, null, false, "Collection recovered", "An OK snapshot was collected", snapshot, "db_perf_snapshot");
+      }
+      else if(!"PARTIAL".equals(status) || snapshot.getValue("connectionsTotal") == null)
+      {
+         return;
+      }
+      double maximum = RuleContext.number(snapshot, "maxConnections");
+      if(maximum > 0)
+      {
+         double used = 100 * RuleContext.number(snapshot, "connectionsTotal") / maximum;
+         signal(CONNECTION_SATURATION, backend, null, used >= RuleContext.number(settings, "connectionAlertPercent"),
+            "Database connection capacity", used + "% of " + maximum + " connections", snapshot, "db_perf_snapshot");
+      }
+      Instant now = snapshot.getValueInstant("capturedAt");
+      RuleContext ctx = new RuleContext(runtime, backend, new QRecord(), settings, now);
+      List<QRecord> activity = DbPerfStore.query("db_perf_activity_sample", DbPerfStore.eq("snapshotId", snapshot.getValueInteger("id")));
+      long blocked = activity.stream().filter(r -> "BLOCKED".equals(r.getValueString("reason")))
+         .filter(r -> Math.max(RuleContext.number(r, "querySeconds"), RuleContext.number(r, "xactSeconds")) >= RuleContext.number(settings, "blockedAlertSeconds")).count();
+      signal(BLOCKED_CHAIN, backend, null, blocked > 0, "Database blocking chain", blocked + " blocked sessions exceed the duration threshold", snapshot, "db_perf_snapshot");
+      if("OK".equals(status) || "NO_READ_ALL_STATS".equals(settings.getValueString("statsAccess")))
+      {
+         spikes(ctx, snapshot);
+      }
+   }
+
+
+   /*******************************************************************************
+    ** A baseline excludes the current observation and needs twelve prior samples.
+    ** Missing top-N entries clear after one hour, including after a restart.
+    *******************************************************************************/
+   private void spikes(RuleContext ctx, QRecord snapshot) throws Exception
+   {
+      Map<Integer, List<QRecord>> history = ctx.window("query_snapshot", 7).stream().collect(Collectors.groupingBy(r -> r.getValueInteger("statementId")));
+      for(QRecord statement : ctx.identities("statement"))
+      {
+         List<QRecord> samples = history.getOrDefault(statement.getValueInteger("id"), List.of());
+         QRecord current = samples.stream().filter(r -> snapshot.getValueInteger("id").equals(r.getValueInteger("snapshotId"))).findFirst().orElse(null);
+         if(current == null)
+         {
+            Instant last = samples.stream().map(r -> r.getValueInstant("capturedAt")).max(Comparator.naturalOrder()).orElse(Instant.EPOCH);
+            if(!last.plusSeconds(3600).isAfter(ctx.now))
+            {
+               signal(QUERY_SPIKE, ctx.backend, statement.getValueString("id"), false, "Query absent", "Statement absent from snapshots for at least one hour", statement, "db_perf_statement");
+            }
+            continue;
+         }
+         List<Double> previous = samples.stream().filter(r -> r.getValueInstant("capturedAt").isBefore(ctx.now))
+            .map(r -> RuleContext.number(r, "totalMs")).sorted().toList();
+         if(previous.size() < 12)
+         {
+            if(RuleContext.number(current, "totalMs") < RuleContext.number(ctx.settings, "spikeMinTotalMs"))
+            {
+               signal(QUERY_SPIKE, ctx.backend, statement.getValueString("id"), false, "Query recovered", "Interval time is below the configured floor", statement, "db_perf_statement");
+            }
+            continue;
+         }
+         int middle = previous.size() / 2;
+         double median = previous.size() % 2 == 0 ? (previous.get(middle - 1) + previous.get(middle)) / 2 : previous.get(middle);
+         double time = RuleContext.number(current, "totalMs");
+         boolean spike = time >= RuleContext.number(ctx.settings, "spikeMinTotalMs") && time >= median * RuleContext.number(ctx.settings, "spikeFactor");
+         String normalized = SqlNormalizer.normalize(statement.getValueString("queryText"));
+         String text = normalized.substring(0, Math.min(200, normalized.length()));
+         signal(QUERY_SPIKE, ctx.backend, statement.getValueString("id"), spike, "Database query time spike",
+            "Statement " + statement.getValueInteger("id") + ": " + time + " ms; prior median " + median + " ms. " + text, statement, "db_perf_statement");
+      }
+   }
+
+
+   /*******************************************************************************
+    ** Stable backend/subject keys make raises and resolutions restart-safe.
+    *******************************************************************************/
+   private static void signal(String type, String backend, String subject, boolean raised, String title, String summary, QRecord row, String table)
+   {
+      String key = type.replace("dbperf.", "db_perf.").replace('-', '_') + ":" + backend + (subject == null ? "" : ":" + subject);
+      if(raised)
+      {
+         QAlerts.raise(new AlertSignal(type).withDedupKey(key).withTitle(title).withSummary(summary)
+            .withRecordRef(AlertRecordRef.of(table, row.getValueInteger("id"))));
+      }
+      else
+      {
+         QAlerts.resolve(type, key, "Condition no longer detected");
+      }
+   }
+}

src/main/java/com/kingsrook/qbits/dbperformance/analyze/AnalyzeService.java

@@ -0,0 +1,102 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2026.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dbperformance.analyze;
+
+
+import java.time.Clock;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.List;
+import com.kingsrook.qbits.dbperformance.DbPerfRuntime;
+import com.kingsrook.qbits.dbperformance.collect.DbPerfSettingsService;
+import com.kingsrook.qbits.dbperformance.collect.DbPerfStore;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+
+
+/*******************************************************************************
+ ** Each rule has its own failure boundary and atomic finding reconciliation.
+ *******************************************************************************/
+public class AnalyzeService
+{
+   private final DbPerfRuntime runtime;
+   private final Clock clock;
+   private final List<DbPerfRule> rules;
+
+
+   /*******************************************************************************
+    ** All seeded rules, using the production clock.
+    *******************************************************************************/
+   public AnalyzeService(DbPerfRuntime runtime)
+   {
+      this(runtime, Clock.systemUTC(), Arrays.asList(StoredRule.values()));
+   }
+
+
+   /*******************************************************************************
+    ** Explicit rules permit isolation tests and future provider-independent rules.
+    *******************************************************************************/
+   public AnalyzeService(DbPerfRuntime runtime, Clock clock, List<DbPerfRule> rules)
+   {
+      this.runtime = runtime;
+      this.clock = clock;
+      this.rules = List.copyOf(rules);
+   }
+
+
+   /*******************************************************************************
+    ** Error text is sanitized so no database exception leaks SQL or literals.
+    *******************************************************************************/
+   public record Result(String ruleCode, FindingService.Counts counts, String error)
+   {
+   }
+
+
+   /*******************************************************************************
+    ** Disabled rules are neither evaluated nor reconciled.
+    *******************************************************************************/
+   public List<Result> run(String backend) throws Exception
+   {
+      if(!runtime.getConfig().getBackendName().equals(backend))
+      {
+         throw (new IllegalArgumentException("Backend is not configured for this runtime"));
+      }
+      QRecord settings = new DbPerfSettingsService(runtime).ensure(backend);
+      List<Result> results = new ArrayList<>();
+      for(DbPerfRule rule : rules)
+      {
+         try
+         {
+            QRecord row = runtime.asSystem(() -> DbPerfStore.first("db_perf_rule", DbPerfStore.eq("code", rule.code())));
+            if(row == null || !Boolean.TRUE.equals(row.getValueBoolean("enabled")))
+            {
+               continue;
+            }
+            List<FindingDraft> drafts = runtime.asSystem(() -> rule.evaluate(new RuleContext(runtime, backend, row, settings, clock.instant())));
+            results.add(new Result(rule.code(), new FindingService(runtime, clock).apply(backend, rule.code(), drafts), null));
+         }
+         catch(Exception e)
+         {
+            results.add(new Result(rule.code(), new FindingService.Counts(0, 0, 0), "Evaluation failed (" + e.getClass().getSimpleName() + ")"));
+         }
+      }
+      return (results);
+   }
+}

src/main/java/com/kingsrook/qbits/dbperformance/analyze/DbPerfRule.java

@@ -0,0 +1,42 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2026.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dbperformance.analyze;
+
+
+import java.util.List;
+
+
+/*******************************************************************************
+ ** A suggestion reads collected evidence only; it never probes the monitored DB.
+ *******************************************************************************/
+public interface DbPerfRule
+{
+   /*******************************************************************************
+    ** Seeded rule identity.
+    *******************************************************************************/
+   String code();
+
+
+   /*******************************************************************************
+    ** Evaluate the configured thresholds against stored observations.
+    *******************************************************************************/
+   List<FindingDraft> evaluate(RuleContext ctx) throws Exception;
+}

src/main/java/com/kingsrook/qbits/dbperformance/analyze/FindingDraft.java

@@ -0,0 +1,68 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2026.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dbperformance.analyze;
+
+
+import java.math.BigDecimal;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+
+
+/*******************************************************************************
+ ** Immutable proposal. The lifecycle service owns identity, timestamps and state.
+ *******************************************************************************/
+public record FindingDraft(String ruleCode, String dedupeKey, String severity, double impact,
+                           String title, String summary, String evidence, String recommendation,
+                           String migrationSnippet, String subjectTable, Integer statementId,
+                           Integer tableStatId, Integer indexStatId, String unitName)
+{
+   /*******************************************************************************
+    ** Rank even small findings, with impact clamped to a finite fraction.
+    *******************************************************************************/
+   public BigDecimal score()
+   {
+      int weight = switch(severity)
+      {
+         case "INFO" -> 1;
+         case "WARNING" -> 2;
+         case "ERROR" -> 3;
+         case "CRITICAL" -> 4;
+         default -> throw new IllegalArgumentException("Unknown finding severity");
+      };
+      if(!Double.isFinite(impact))
+      {
+         throw (new IllegalArgumentException("Impact must be finite"));
+      }
+      return (BigDecimal.valueOf(weight * (0.1 + Math.max(0, Math.min(1, impact)))));
+   }
+
+
+   /*******************************************************************************
+    ** Persist only the generated evidence; do not overwrite human status fields.
+    *******************************************************************************/
+   public QRecord record()
+   {
+      return (new QRecord().withValue("ruleCode", ruleCode).withValue("dedupeKey", dedupeKey)
+         .withValue("severity", severity).withValue("score", score()).withValue("title", title)
+         .withValue("summary", summary).withValue("evidence", evidence).withValue("recommendation", recommendation)
+         .withValue("migrationSnippet", migrationSnippet).withValue("subjectTable", subjectTable)
+         .withValue("statementId", statementId).withValue("tableStatId", tableStatId).withValue("indexStatId", indexStatId).withValue("unitName", unitName));
+   }
+}

src/main/java/com/kingsrook/qbits/dbperformance/analyze/FindingService.java

@@ -0,0 +1,176 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2026.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dbperformance.analyze;
+
+
+import java.time.Clock;
+import java.time.Instant;
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+import com.kingsrook.qbits.dbperformance.DbPerfRuntime;
+import com.kingsrook.qbits.dbperformance.collect.DbPerfStore;
+import com.kingsrook.qqq.backend.core.actions.tables.QueryAction;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QCriteriaOperator;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QFilterCriteria;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QueryInput;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QueryLockMode;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+import com.kingsrook.qqq.backend.module.rdbms.model.metadata.RDBMSBackendMetaData;
+
+
+/*******************************************************************************
+ ** Atomic reconciliation of one rule. Human acknowledgement and ignore survive
+ ** recurrence; only resolved findings reopen. Disabled rules are entirely inert.
+ *******************************************************************************/
+public class FindingService
+{
+   private final DbPerfRuntime runtime;
+   private final Clock clock;
+
+
+   /*******************************************************************************
+    ** Production clock.
+    *******************************************************************************/
+   public FindingService(DbPerfRuntime runtime)
+   {
+      this(runtime, Clock.systemUTC());
+   }
+
+
+   /*******************************************************************************
+    ** Testable observation time.
+    *******************************************************************************/
+   public FindingService(DbPerfRuntime runtime, Clock clock)
+   {
+      this.runtime = runtime;
+      this.clock = clock;
+   }
+
+
+   /*******************************************************************************
+    ** Counts describe actual writes, not the size of the input list.
+    *******************************************************************************/
+   public record Counts(int created, int updated, int resolved)
+   {
+   }
+
+
+   /*******************************************************************************
+    ** Validate scope before any mutations; a failed rule never clears findings.
+    *******************************************************************************/
+   public Counts apply(String backend, String ruleCode, List<FindingDraft> drafts) throws Exception
+   {
+      if(!runtime.getConfig().getBackendName().equals(backend))
+      {
+         throw (new IllegalArgumentException("Backend is not configured for this runtime"));
+      }
+      synchronized(runtime)
+      {
+         return (runtime.transaction(() -> reconcile(backend, ruleCode, drafts)));
+      }
+   }
+
+
+   /*******************************************************************************
+    ** Serialize analyzer and human status writes across host instances. A busy
+    ** rule reports a retryable error instead of overwriting a concurrent Ignore.
+    ** Call inside the runtime transaction and synchronized(runtime) boundary.
+    *******************************************************************************/
+   public static QRecord lockRule(DbPerfRuntime runtime, String code) throws Exception
+   {
+      RDBMSBackendMetaData backend = (RDBMSBackendMetaData) runtime.getInstance().getBackend(runtime.getConfig().getBackendName());
+      if(backend.getActionStrategy().supportsForUpdateSkipLocked())
+      {
+         List<QRecord> rows = new QueryAction().execute(new QueryInput("db_perf_rule").withFilter(DbPerfStore.eq("code", code))
+            .withTransaction(QContext.getQBackendTransaction()).withLockMode(QueryLockMode.FOR_UPDATE_SKIP_LOCKED)).getRecords();
+         if(rows.isEmpty() && DbPerfStore.first("db_perf_rule", DbPerfStore.eq("code", code)) != null)
+         {
+            throw (new IllegalStateException("Rule is being updated; retry analysis or the finding action"));
+         }
+         return (rows.isEmpty() ? null : rows.getFirst());
+      }
+      return (DbPerfStore.first("db_perf_rule", DbPerfStore.eq("code", code)));
+   }
+
+
+   /*******************************************************************************
+    ** Re-read the enabled flag in the transaction, preserving administrator edits.
+    *******************************************************************************/
+   private Counts reconcile(String backend, String ruleCode, List<FindingDraft> drafts) throws Exception
+   {
+      QRecord rule = lockRule(runtime, ruleCode);
+      if(rule == null || !Boolean.TRUE.equals(rule.getValueBoolean("enabled")))
+      {
+         return (new Counts(0, 0, 0));
+      }
+      Map<String, QRecord> existing = new HashMap<>();
+      for(QRecord row : RuleContext.read("db_perf_finding", DbPerfStore.eq("backendName", backend)
+         .withCriteria(new QFilterCriteria("ruleCode", QCriteriaOperator.EQUALS, ruleCode))))
+      {
+         existing.put(row.getValueString("dedupeKey"), row);
+      }
+      Instant now = clock.instant();
+      Set<String> seen = new HashSet<>();
+      int created = 0;
+      int updated = 0;
+      int resolved = 0;
+      for(FindingDraft draft : drafts)
+      {
+         if(!ruleCode.equals(draft.ruleCode()) || !draft.dedupeKey().startsWith(ruleCode + ":" + backend + ":") || !seen.add(draft.dedupeKey()))
+         {
+            throw (new IllegalArgumentException("Invalid or duplicate finding identity"));
+         }
+         QRecord old = existing.get(draft.dedupeKey());
+         QRecord row = draft.record().withValue("backendName", backend).withValue("ruleId", rule.getValueInteger("id"))
+            .withValue("lastSeenAt", now);
+         if(old == null)
+         {
+            row.withValue("status", "OPEN").withValue("firstSeenAt", now).withValue("occurrences", 1);
+            DbPerfStore.insert("db_perf_finding", List.of(row));
+            created++;
+         }
+         else
+         {
+            row.withValue("id", old.getValueInteger("id")).withValue("occurrences", (int) RuleContext.number(old, "occurrences") + 1);
+            if("RESOLVED".equals(old.getValueString("status")))
+            {
+               row.withValue("status", "OPEN").withValue("resolvedAt", null).withValue("statusNote", "Detected again").withValue("statusChangedBy", null);
+            }
+            DbPerfStore.update("db_perf_finding", List.of(row));
+            updated++;
+         }
+      }
+      for(QRecord old : existing.values())
+      {
+         if(!seen.contains(old.getValueString("dedupeKey")) && List.of("OPEN", "ACKNOWLEDGED").contains(old.getValueString("status")))
+         {
+            DbPerfStore.update("db_perf_finding", List.of(new QRecord().withValue("id", old.getValueInteger("id"))
+               .withValue("status", "RESOLVED").withValue("statusNote", "No longer detected").withValue("resolvedAt", now).withValue("statusChangedBy", null)));
+            resolved++;
+         }
+      }
+      return (new Counts(created, updated, resolved));
+   }
+}

src/main/java/com/kingsrook/qbits/dbperformance/analyze/MigrationSnippets.java

@@ -0,0 +1,125 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2026.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dbperformance.analyze;
+
+
+import java.time.Instant;
+import java.time.ZoneOffset;
+import java.time.format.DateTimeFormatter;
+import java.util.List;
+import java.util.Locale;
+import java.util.Set;
+import java.util.stream.Collectors;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+
+
+/*******************************************************************************
+ ** Reviewable Liquibase text only. These suggestions are never executed.
+ *******************************************************************************/
+public final class MigrationSnippets
+{
+   private static final Set<String> RESERVED = Set.of("user", "order", "group", "select", "table", "index", "where", "from", "to", "primary", "constraint", "limit", "offset", "references", "column", "default", "check", "unique", "all", "and", "or", "not", "null", "true", "false", "case", "when", "end", "join", "on", "in", "as", "by", "values", "grant", "current_user", "authorization", "returning",
+      "analyse", "analyze", "array", "asc", "asymmetric", "both", "cast", "collate", "collation", "concurrently", "current_catalog", "current_date", "current_role", "current_schema", "current_time", "current_timestamp",
+      "desc", "distinct", "do", "else", "except", "fetch", "for", "foreign", "freeze", "ilike", "initially", "intersect", "into", "is", "isnull", "lateral", "leading", "like", "localtime", "localtimestamp", "natural", "notnull",
+      "only", "placing", "session_user", "similar", "some", "symmetric", "system_user", "tablesample", "then", "trailing", "union", "using", "variadic", "verbose", "window", "with");
+
+
+   /*******************************************************************************
+    ** Utility class.
+    *******************************************************************************/
+   private MigrationSnippets()
+   {
+   }
+
+
+   /*******************************************************************************
+    ** Quote identifiers with punctuation, mixed case, or reserved spelling.
+    *******************************************************************************/
+   public static String quote(String name)
+   {
+      return (name.matches("[a-z_][a-z0-9_]*") && !RESERVED.contains(name) ? name : "\"" + name.replace("\"", "\"\"") + "\"");
+   }
+
+
+   /*******************************************************************************
+    ** ASCII names are byte-bounded as well as character-bounded for PostgreSQL.
+    *******************************************************************************/
+   public static String indexName(String table, List<String> columns)
+   {
+      String raw = "idx_" + table + "_" + String.join("_", columns);
+      String safe = raw.toLowerCase(Locale.ROOT).replaceAll("[^a-z0-9_]", "_");
+      return (safe.length() <= 63 && safe.equals(raw) ? safe : safe.substring(0, Math.min(54, safe.length())) + "_" + String.format("%08x", raw.hashCode()));
+   }
+
+
+   /*******************************************************************************
+    ** Create concurrently, with a matching schema-qualified rollback.
+    *******************************************************************************/
+   public static String create(Instant now, QRecord table, List<String> columns, String reason)
+   {
+      String name = indexName(table.getValueString("tableName"), columns);
+      String schema = quote(table.getValueString("schemaName"));
+      return (header(now, name, "MISSING_INDEX", reason)
+         + "CREATE INDEX CONCURRENTLY IF NOT EXISTS " + quote(name) + " ON " + schema + "." + quote(table.getValueString("tableName"))
+         + " (" + columns.stream().map(MigrationSnippets::quote).collect(Collectors.joining(", ")) + ");\n"
+         + "-- rollback DROP INDEX CONCURRENTLY IF EXISTS " + schema + "." + quote(name) + ";\n");
+   }
+
+
+   /*******************************************************************************
+    ** Preserve the collected definition in rollback; no guessed index structure.
+    *******************************************************************************/
+   public static String drop(Instant now, QRecord index, String rule, String reason)
+   {
+      String definition = index.getValueString("definition");
+      if(definition == null || !definition.matches("(?is)^CREATE\\s+(UNIQUE\\s+)?INDEX\\s+.*"))
+      {
+         return (null);
+      }
+      String rollback = definition.replaceFirst("(?i)^(CREATE\\s+(?:UNIQUE\\s+)?INDEX)\\s+(?:CONCURRENTLY\\s+)?", "$1 CONCURRENTLY ").replaceAll("[\\r\\n]+", " ").replaceAll(";+$", "");
+      return (header(now, index.getValueString("indexName"), rule, reason)
+         + "DROP INDEX CONCURRENTLY IF EXISTS " + quote(index.getValueString("schemaName")) + "." + quote(index.getValueString("indexName")) + ";\n"
+         + "-- rollback " + rollback + ";\n");
+   }
+
+
+   /*******************************************************************************
+    ** Optional tuning suggestion; rollback resets the override to server defaults.
+    *******************************************************************************/
+   public static String vacuum(Instant now, QRecord table, String reason)
+   {
+      String target = quote(table.getValueString("schemaName")) + "." + quote(table.getValueString("tableName"));
+      return (header(now, table.getValueString("tableName"), "BLOAT", reason)
+         + "ALTER TABLE " + target + " SET (autovacuum_vacuum_scale_factor = 0.05);\n"
+         + "-- rollback ALTER TABLE " + target + " RESET (autovacuum_vacuum_scale_factor);\n");
+   }
+
+
+   /*******************************************************************************
+    ** A stable daily changeset identity and a single-line evidence comment.
+    *******************************************************************************/
+   private static String header(Instant now, String subject, String rule, String reason)
+   {
+      String date = DateTimeFormatter.ofPattern("yyyyMMdd").withZone(ZoneOffset.UTC).format(now);
+      return ("-- liquibase formatted sql\n-- changeset db-performance:" + date + "-" + rule.toLowerCase(Locale.ROOT) + "-" + subject.replaceAll("[^a-zA-Z0-9_-]", "_")
+         + " runInTransaction:false\n-- comment: Suggested by Database Performance (" + rule + "): " + reason.replaceAll("[\\r\\n]+", " ") + "\n");
+   }
+}

src/main/java/com/kingsrook/qbits/dbperformance/analyze/PredicateParser.java

@@ -0,0 +1,189 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2026.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dbperformance.analyze;
+
+
+import java.util.ArrayList;
+import java.util.LinkedHashMap;
+import java.util.LinkedHashSet;
+import java.util.List;
+import java.util.Locale;
+import java.util.Map;
+import java.util.Set;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+import com.kingsrook.qbits.dbperformance.normalize.SqlNormalizer;
+
+
+/*******************************************************************************
+ ** Conservative tokenizer for simple WHERE equality/IN and equijoins. Complex
+ ** subqueries and expressions are deliberately left for the Query plan action.
+ *******************************************************************************/
+public final class PredicateParser
+{
+   private static final Pattern TOKEN = Pattern.compile("\"(?:\"\"|[^\"])*\"|[a-zA-Z_][a-zA-Z_0-9$]*|\\?|[^\\s]");
+   private static final Set<String> KEYWORDS = Set.of("where", "join", "inner", "outer", "left", "right", "full", "cross", "on", "group", "order", "limit", "having", "set", "returning", "union");
+
+
+   /*******************************************************************************
+    ** Utility class.
+    *******************************************************************************/
+   private PredicateParser()
+   {
+   }
+
+
+   /*******************************************************************************
+    ** Decode SQL identifiers; unquoted names fold to lower case.
+    *******************************************************************************/
+   public static String identifier(String token)
+   {
+      return (token.startsWith("\"") ? token.substring(1, token.length() - 1).replace("\"\"", "\"") : token.toLowerCase(Locale.ROOT));
+   }
+
+
+   /*******************************************************************************
+    ** Only simple column indexes can be compared as prefixes.
+    *******************************************************************************/
+   public static List<String> indexColumns(String columns)
+   {
+      if(columns == null || columns.isBlank())
+      {
+         return (List.of());
+      }
+      List<String> result = new ArrayList<>();
+      Matcher matcher = TOKEN.matcher(columns);
+      boolean wantColumn = true;
+      while(matcher.find())
+      {
+         String token = matcher.group();
+         if(wantColumn && isIdentifier(token))
+         {
+            result.add(identifier(token));
+         }
+         else if(wantColumn || !token.equals(","))
+         {
+            return (List.of());
+         }
+         wantColumn = !wantColumn;
+      }
+      return (wantColumn ? List.of() : result);
+   }
+
+
+   /*******************************************************************************
+    ** Resolve aliases before collecting predicates so joined tables do not lend
+    ** their columns to the main table's suggested index.
+    *******************************************************************************/
+   public static List<String> candidates(String sql, String schema, String table)
+   {
+      List<String> tokens = new ArrayList<>();
+      Matcher matcher = TOKEN.matcher(SqlNormalizer.normalize(sql));
+      while(matcher.find())
+      {
+         tokens.add(matcher.group());
+      }
+      if(tokens.stream().filter(t -> t.equals("select")).count() > 1 || tokens.contains("union"))
+      {
+         return (List.of());
+      }
+      Map<String, Boolean> aliases = new LinkedHashMap<>();
+      for(int i = 0; i + 1 < tokens.size(); i++)
+      {
+         if(Set.of("from", "join", "update").contains(tokens.get(i)) && isIdentifier(tokens.get(i + 1)))
+         {
+            int end = i + 2;
+            String relation = identifier(tokens.get(i + 1));
+            boolean schemaMatches = true;
+            if(end + 1 < tokens.size() && tokens.get(end).equals("."))
+            {
+               schemaMatches = relation.equals(schema);
+               relation = identifier(tokens.get(end + 1));
+               end += 2;
+            }
+            boolean target = schemaMatches && relation.equals(table);
+            aliases.put(relation, target);
+            if(end < tokens.size() && tokens.get(end).equals("as"))
+            {
+               end++;
+            }
+            if(end < tokens.size() && isIdentifier(tokens.get(end)) && !KEYWORDS.contains(tokens.get(end)))
+            {
+               aliases.put(identifier(tokens.get(end)), target);
+            }
+         }
+      }
+      Set<String> result = new LinkedHashSet<>();
+      boolean predicate = false;
+      for(int i = 0; i < tokens.size(); i++)
+      {
+         String token = tokens.get(i);
+         if(token.equals("where") || token.equals("on"))
+         {
+            predicate = true;
+            continue;
+         }
+         if(KEYWORDS.contains(token) && !token.equals("on"))
+         {
+            predicate = false;
+         }
+         if(!predicate || !isIdentifier(token) || i > 0 && tokens.get(i - 1).equals("."))
+         {
+            continue;
+         }
+         int end = i + 1;
+         String column = identifier(token);
+         boolean belongs = !aliases.isEmpty() && aliases.values().stream().allMatch(Boolean::booleanValue);
+         if(end + 1 < tokens.size() && tokens.get(end).equals("."))
+         {
+            belongs = Boolean.TRUE.equals(aliases.get(column));
+            column = identifier(tokens.get(end + 1));
+            end += 2;
+         }
+         if(end + 1 >= tokens.size())
+         {
+            continue;
+         }
+         String operator = tokens.get(end);
+         String right = tokens.get(end + 1);
+         boolean joinedColumn = end + 3 < tokens.size() && tokens.get(end + 2).equals(".") && aliases.containsKey(identifier(right));
+         if(belongs && (operator.equals("in") && right.equals("(") || operator.equals("=") && (right.equals("?") || joinedColumn)))
+         {
+            result.add(column);
+         }
+         if(operator.equals("=") && end + 3 < tokens.size() && tokens.get(end + 2).equals(".")
+            && Boolean.TRUE.equals(aliases.get(identifier(right))))
+         {
+            result.add(identifier(tokens.get(end + 3)));
+         }
+      }
+      return (List.copyOf(result));
+   }
+
+
+   /*******************************************************************************
+    ** Tokens are already balanced quoted identifiers or simple words.
+    *******************************************************************************/
+   private static boolean isIdentifier(String token)
+   {
+      return (token.startsWith("\"") || token.matches("[a-zA-Z_][a-zA-Z_0-9$]*"));
+   }
+}

src/main/java/com/kingsrook/qbits/dbperformance/analyze/RuleContext.java

@@ -0,0 +1,202 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2026.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dbperformance.analyze;
+
+
+import java.time.Duration;
+import java.time.Instant;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+import java.util.stream.Collectors;
+import com.kingsrook.qbits.dbperformance.DbPerfRuntime;
+import com.kingsrook.qbits.dbperformance.collect.DbPerfStore;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QCriteriaOperator;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QFilterCriteria;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QFilterOrderBy;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QQueryFilter;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+
+
+/*******************************************************************************
+ ** Backend-scoped, time-bounded reads of the qbit's own tables. Ratios in storage
+ ** are percentages. Attribution belongs to the single configured backend.
+ *******************************************************************************/
+public class RuleContext
+{
+   public final DbPerfRuntime runtime;
+   public final String backend;
+   public final QRecord rule;
+   public final QRecord settings;
+   public final Instant now;
+   private final Map<String, List<QRecord>> cache = new HashMap<>();
+
+
+   /*******************************************************************************
+    ** An explicit observation time makes windows repeatable in tests and runs.
+    *******************************************************************************/
+   public RuleContext(DbPerfRuntime runtime, String backend, QRecord rule, QRecord settings, Instant now)
+   {
+      if(!runtime.getConfig().getBackendName().equals(backend))
+      {
+         throw (new IllegalArgumentException("Backend is not configured for this runtime"));
+      }
+      this.runtime = runtime;
+      this.backend = backend;
+      this.rule = rule;
+      this.settings = settings;
+      this.now = now;
+   }
+
+
+   /*******************************************************************************
+    ** Seeded thresholds remain editable by the administrator.
+    *******************************************************************************/
+   public double threshold(int ordinal)
+   {
+      return (number(rule, "threshold" + ordinal));
+   }
+
+
+   /*******************************************************************************
+    ** Page by primary key rather than silently accepting backend row limits.
+    *******************************************************************************/
+   public static List<QRecord> read(String table, QQueryFilter filter) throws Exception
+   {
+      List<QRecord> rows = new ArrayList<>();
+      int last = 0;
+      while(true)
+      {
+         List<QRecord> page = DbPerfStore.query(table, new QQueryFilter().withSubFilter(filter)
+            .withCriteria(new QFilterCriteria("id", QCriteriaOperator.GREATER_THAN, last))
+            .withOrderBy(new QFilterOrderBy("id", true)).withLimit(DbPerfStore.BATCH_SIZE));
+         rows.addAll(page);
+         if(page.size() < DbPerfStore.BATCH_SIZE)
+         {
+            return (rows);
+         }
+         last = page.getLast().getValueInteger("id");
+      }
+   }
+
+
+   /*******************************************************************************
+    ** Current identities, filtered by backend. Cached only for this evaluation.
+    *******************************************************************************/
+   public List<QRecord> identities(String suffix) throws Exception
+   {
+      String key = "identity:" + suffix;
+      if(!cache.containsKey(key))
+      {
+         cache.put(key, read("db_perf_" + suffix, DbPerfStore.eq("backendName", backend)));
+      }
+      return (cache.get(key));
+   }
+
+
+   /*******************************************************************************
+    ** Read observations inside (now-days, now], excluding other backends through
+    ** their snapshot foreign key. Partial snapshots still contain useful data.
+    *******************************************************************************/
+   public List<QRecord> window(String suffix, int days) throws Exception
+   {
+      String key = suffix + ":" + days;
+      if(!cache.containsKey(key))
+      {
+         String time = Set.of("attribution", "repeat_pattern").contains(suffix) ? "windowEnd" : "capturedAt";
+         QQueryFilter filter = new QQueryFilter(new QFilterCriteria(time, QCriteriaOperator.GREATER_THAN, now.minusSeconds(days * 86400L)),
+            new QFilterCriteria(time, QCriteriaOperator.LESS_THAN_OR_EQUALS, now));
+         if(suffix.equals("snapshot"))
+         {
+            filter.withCriteria(new QFilterCriteria("backendName", QCriteriaOperator.EQUALS, backend));
+         }
+         List<QRecord> rows = read("db_perf_" + suffix, filter);
+         if(!Set.of("snapshot", "attribution", "repeat_pattern").contains(suffix))
+         {
+            Set<Integer> ids = window("snapshot", days).stream()
+               .filter(r -> List.of("OK", "PARTIAL").contains(r.getValueString("status")))
+               .map(r -> r.getValueInteger("id")).collect(Collectors.toSet());
+            rows = rows.stream().filter(r -> ids.contains(r.getValueInteger("snapshotId"))).toList();
+         }
+         cache.put(key, rows);
+      }
+      return (cache.get(key));
+   }
+
+
+   /*******************************************************************************
+    ** Latest inventory only: retired tables and dropped indexes cannot recur.
+    *******************************************************************************/
+   public List<QRecord> inventory(String suffix) throws Exception
+   {
+      Instant latest = settings.getValueInstant("lastTableSnapshotAt");
+      return (identities(suffix).stream().filter(r -> latest == null || latest.equals(r.getValueInstant("lastSeenAt"))).toList());
+   }
+
+
+   /*******************************************************************************
+    ** Scale observed table deltas to a day when the observation window is short.
+    ** Table sampling is independent of the faster database snapshot interval.
+    *******************************************************************************/
+   public double perDay(QRecord table, String field) throws Exception
+   {
+      List<QRecord> rows = window("table_snapshot", 1).stream()
+         .filter(r -> table.getValueInteger("id").equals(r.getValueInteger("tableStatId"))).toList();
+      if(rows.isEmpty())
+      {
+         return (0);
+      }
+      Instant first = rows.stream().map(r -> r.getValueInstant("capturedAt")).min(Instant::compareTo).orElseThrow();
+      Instant last = rows.stream().map(r -> r.getValueInstant("capturedAt")).max(Instant::compareTo).orElseThrow();
+      double seconds = Math.min(86400, Duration.between(first, last).getSeconds() + Math.max(60, number(settings, "tableSnapshotMinutes") * 60));
+      return (rows.stream().mapToDouble(r -> number(r, field)).sum() * 86400 / seconds);
+   }
+
+
+   /*******************************************************************************
+    ** Minimum age of the current database statistics, including retained history.
+    *******************************************************************************/
+   public double statsAgeDays() throws Exception
+   {
+      QRecord latest = DbPerfStore.first("db_perf_snapshot", DbPerfStore.eq("backendName", backend)
+         .withCriteria(new QFilterCriteria("rawStatsReset", QCriteriaOperator.IS_NOT_BLANK))
+         .withOrderBy(new QFilterOrderBy("capturedAt", false)));
+      Instant start = latest == null ? null : latest.getValueInstant("rawStatsReset");
+      if(start == null)
+      {
+         QRecord first = DbPerfStore.first("db_perf_snapshot", DbPerfStore.eq("backendName", backend)
+            .withOrderBy(new QFilterOrderBy("capturedAt", true)));
+         start = first == null ? now : first.getValueInstant("capturedAt");
+      }
+      return (Math.max(0, Duration.between(start, now).toSeconds() / 86400.0));
+   }
+
+
+   /*******************************************************************************
+    ** Null counters mean no observed activity, never an invented measurement.
+    *******************************************************************************/
+   public static double number(QRecord row, String field)
+   {
+      return (row == null || row.getValue(field) == null ? 0 : row.getValueBigDecimal(field).doubleValue());
+   }
+}

src/main/java/com/kingsrook/qbits/dbperformance/analyze/StoredRule.java

@@ -0,0 +1,516 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2026.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dbperformance.analyze;
+
+
+import java.net.URLEncoder;
+import java.nio.charset.StandardCharsets;
+import java.time.Duration;
+import java.time.Instant;
+import java.util.ArrayList;
+import java.util.Comparator;
+import java.util.LinkedHashMap;
+import java.util.LinkedHashSet;
+import java.util.List;
+import java.util.Locale;
+import java.util.Map;
+import java.util.Objects;
+import java.util.Set;
+import java.util.stream.Collectors;
+import com.kingsrook.qbits.dbperformance.normalize.SqlNormalizer;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+
+
+/*******************************************************************************
+ ** The fourteen seeded rules. Every decision is based on stored collector rows;
+ ** administrators control severities and thresholds through db_perf_rule.
+ *******************************************************************************/
+public enum StoredRule implements DbPerfRule
+{
+   SLOW_QUERY, MISSING_INDEX, UNUSED_INDEX, DUPLICATE_INDEX, N_PLUS_ONE, HOT_JOB,
+   OVER_AUDITED, BLOAT, VACUUM_STALE, POOL_SIZING, IDLE_IN_TRANSACTION,
+   CACHE_HIT_LOW, TEMP_SPILL, DEADLOCKS;
+
+
+   /*******************************************************************************
+    ** Seed identity.
+    *******************************************************************************/
+   @Override
+   public String code()
+   {
+      return (name());
+   }
+
+
+   /*******************************************************************************
+    ** Disabled evaluation is inert even when called outside AnalyzeService.
+    *******************************************************************************/
+   @Override
+   public List<FindingDraft> evaluate(RuleContext ctx) throws Exception
+   {
+      if(!Boolean.TRUE.equals(ctx.rule.getValueBoolean("enabled")))
+      {
+         return (List.of());
+      }
+      return (switch(this)
+      {
+         case SLOW_QUERY -> slow(ctx);
+         case MISSING_INDEX, OVER_AUDITED, BLOAT, VACUUM_STALE -> tables(ctx);
+         case UNUSED_INDEX, DUPLICATE_INDEX -> indexes(ctx);
+         case N_PLUS_ONE -> repeats(ctx);
+         case HOT_JOB -> jobs(ctx);
+         case POOL_SIZING -> pools(ctx);
+         case IDLE_IN_TRANSACTION -> idle(ctx);
+         case CACHE_HIT_LOW, DEADLOCKS, TEMP_SPILL -> database(ctx);
+      });
+   }
+
+
+   /*******************************************************************************
+    ** Mean and share use the collector's 24-hour aggregates, excluding stale rows.
+    *******************************************************************************/
+   private List<FindingDraft> slow(RuleContext ctx) throws Exception
+   {
+      List<FindingDraft> findings = new ArrayList<>();
+      for(QRecord row : ctx.identities("statement"))
+      {
+         if(row.getValueInstant("lastSeenAt") == null || row.getValueInstant("lastSeenAt").isBefore(ctx.now.minusSeconds(86400)))
+         {
+            continue;
+         }
+         double share = n(row, "shareOfDbTime24h");
+         if(n(row, "meanMs24h") >= ctx.threshold(1) || share >= ctx.threshold(2))
+         {
+            String sql = URLEncoder.encode("EXPLAIN " + row.getValueString("queryText"), StandardCharsets.UTF_8).replace("+", "%20");
+            String recommendation = "Open the [Query plan action](/record/db_perf_statement/" + row.getValueInteger("id")
+               + ") to inspect this query, or [Explain in SQL Workbench](" + ctx.runtime.getConfig().getSqlWorkbenchRunnerPath() + "?sql=" + sql + "). Review scan and join costs before changing indexes.";
+            findings.add(draft(ctx, row.getValueString("id"), share / 100, "High query execution cost over 24 hours",
+               evidence("Calls", n(row, "calls24h"), "Mean ms", n(row, "meanMs24h"), "Total ms", n(row, "totalMs24h"), "DB time %", share), recommendation, null, row, "statement", null));
+         }
+      }
+      return (findings);
+   }
+
+
+   /*******************************************************************************
+    ** Inventory-based rules share the same current table list and daily deltas.
+    *******************************************************************************/
+   private List<FindingDraft> tables(RuleContext ctx) throws Exception
+   {
+      List<FindingDraft> findings = new ArrayList<>();
+      for(QRecord table : ctx.inventory("table_stat"))
+      {
+         String name = table.getValueString("tableName");
+         String subject = table.getValueString("schemaName") + "." + name;
+         if(this == MISSING_INDEX)
+         {
+            double reads = ctx.perDay(table, "seqTupRead");
+            if(n(table, "seqScanShare") < ctx.threshold(1) || reads < ctx.threshold(2))
+            {
+               continue;
+            }
+            Set<String> columns = new LinkedHashSet<>();
+            for(QRecord statement : ctx.identities("statement").stream()
+               .filter(s -> name.equals(s.getValueString("mainTable")) || subject.equals(s.getValueString("mainTable")))
+               .filter(s -> n(s, "totalMs24h") > 0 && s.getValueInstant("lastSeenAt") != null && !s.getValueInstant("lastSeenAt").isBefore(ctx.now.minusSeconds(86400)))
+               .sorted(Comparator.comparingDouble((QRecord s) -> n(s, "totalMs24h")).reversed()).limit(20).toList())
+            {
+               columns.addAll(PredicateParser.candidates(statement.getValueString("queryText"), table.getValueString("schemaName"), name));
+            }
+            for(QRecord index : ctx.inventory("index_stat"))
+            {
+               List<String> indexed = PredicateParser.indexColumns(index.getValueString("columns"));
+               if(sameTable(table, index) && Boolean.TRUE.equals(index.getValueBoolean("isValid")) && !indexed.isEmpty() && plainIndex(index))
+               {
+                  columns.remove(indexed.getFirst());
+               }
+            }
+            if(!columns.isEmpty())
+            {
+               String reason = reads + " sequential tuples/day; " + n(table, "seqScanShare") + "% sequential scans";
+               findings.add(draft(ctx, subject, n(table, "seqScanShare") / 100, reason,
+                  evidence("Sequential scan %", n(table, "seqScanShare"), "Tuples/day", reads, "Candidate columns", String.join(", ", columns)),
+                  "Check Query plan and selectivity for " + String.join(", ", columns) + ". Review the suggested column order and migration before committing it.",
+                  MigrationSnippets.create(ctx.now, table, List.copyOf(columns), reason), table, "table", null));
+            }
+         }
+         else if(this == BLOAT && n(table, "deadRatio") >= ctx.threshold(1) && n(table, "nDeadTup") >= ctx.threshold(2))
+         {
+            String reason = n(table, "nDeadTup") + " dead tuples (" + n(table, "deadRatio") + "%)";
+            findings.add(draft(ctx, subject, n(table, "deadRatio") / 100, reason,
+               evidence("Dead tuples", n(table, "nDeadTup"), "Live tuples", n(table, "nLiveTup"), "Dead %", n(table, "deadRatio")),
+               "Review long transactions and autovacuum capacity. Consider a lower per-table vacuum scale factor; the optional rollback resets it to the server default.",
+               MigrationSnippets.vacuum(ctx.now, table, reason), table, "table", null));
+         }
+         else if(this == VACUUM_STALE)
+         {
+            Instant vacuum = table.getValueInstant("lastVacuum");
+            Instant auto = table.getValueInstant("lastAutovacuum");
+            if(vacuum == null || auto != null && auto.isAfter(vacuum))
+            {
+               vacuum = auto;
+            }
+            double age = vacuum == null ? ctx.statsAgeDays() : Duration.between(vacuum, ctx.now).toSeconds() / 86400.0;
+            double writes = n(table, "nTupIns") + n(table, "nTupUpd") + n(table, "nTupDel");
+            if(writes > 0 && age > ctx.threshold(1) || n(table, "freezeAge") >= ctx.threshold(2))
+            {
+               findings.add(draft(ctx, subject, Math.min(1, n(table, "freezeAge") / Math.max(1, ctx.threshold(2))), "Vacuum maintenance needs attention",
+                  evidence("Days since vacuum (or observation age)", age, "Writes", writes, "Freeze age", n(table, "freezeAge")),
+                  "Review autovacuum settings and long transactions preventing vacuum. Schedule maintenance and investigate transaction freeze age.", null, table, "table", null));
+            }
+         }
+         else if(this == OVER_AUDITED && name.toLowerCase(Locale.ROOT).matches("(?:audit.*|.*_audit.*)"))
+         {
+            double inserts = ctx.perDay(table, "nTupIns");
+            if(inserts >= ctx.threshold(1))
+            {
+               Map<String, Double> units = new LinkedHashMap<>();
+               for(QRecord row : ctx.window("attribution", 1))
+               {
+                  if((name.equals(row.getValueString("tableName")) || subject.equals(row.getValueString("tableName"))) && "INSERT".equalsIgnoreCase(row.getValueString("operation")))
+                  {
+                     units.merge(String.valueOf(row.getValueString("unitName")), n(row, "statements"), Double::sum);
+                  }
+               }
+               String contributors = units.entrySet().stream().sorted(Map.Entry.<String, Double>comparingByValue().reversed()).limit(5)
+                  .map(e -> e.getKey() + " (" + e.getValue() + " inserts)").collect(Collectors.joining(", "));
+               Map<String, Double> sources = new LinkedHashMap<>();
+               for(QRecord row : ctx.window("attribution", 1))
+               {
+                  String source = row.getValueString("tableName");
+                  if(source != null && !source.toLowerCase(Locale.ROOT).matches("(?:audit.*|.*_audit.*)")
+                     && units.containsKey(row.getValueString("unitName")) && Set.of("INSERT", "UPDATE", "DELETE").contains(String.valueOf(row.getValueString("operation")).toUpperCase(Locale.ROOT)))
+                  {
+                     sources.merge(source, n(row, "statements"), Double::sum);
+                  }
+               }
+               String sourceTables = sources.entrySet().stream().sorted(Map.Entry.<String, Double>comparingByValue().reversed()).limit(5)
+                  .map(e -> e.getKey() + " (" + e.getValue() + " writes)").collect(Collectors.joining(", "));
+               findings.add(draft(ctx, subject, Math.min(1, inserts / Math.max(1, ctx.threshold(1) * 10)), "High audit insert volume",
+                  evidence("Audit inserts/day", inserts, "Attributed units", contributors.isEmpty() ? "Unavailable" : contributors),
+                  "Lower AuditLevel on noisy source tables after reviewing audit requirements. " + (contributors.isEmpty() ? "Source-table attribution is unavailable." : "Largest audit-producing units: " + contributors + ".")
+                     + (sourceTables.isEmpty() ? "" : " Source tables with the most writes in those units: " + sourceTables + ". These are correlated writes, not measured per-source audit row counts."),
+                  null, table, "table", null));
+            }
+         }
+      }
+      return (findings);
+   }
+
+
+   /*******************************************************************************
+    ** Never propose dropping unique, primary, invalid or structurally incomparable
+    ** indexes. Equivalent peers choose one survivor deterministically.
+    *******************************************************************************/
+   private List<FindingDraft> indexes(RuleContext ctx) throws Exception
+   {
+      List<QRecord> indexes = ctx.inventory("index_stat");
+      double bytes = indexes.stream().mapToDouble(r -> n(r, "sizeBytes")).sum();
+      List<FindingDraft> findings = new ArrayList<>();
+      for(QRecord index : indexes)
+      {
+         if(Boolean.TRUE.equals(index.getValueBoolean("isUnique")) || Boolean.TRUE.equals(index.getValueBoolean("isPrimary")) || !Boolean.TRUE.equals(index.getValueBoolean("isValid")))
+         {
+            continue;
+         }
+         String reason = null;
+         if(this == UNUSED_INDEX && n(index, "idxScan") == 0 && ctx.statsAgeDays() >= ctx.threshold(1) && n(index, "sizeBytes") / 1048576 >= ctx.threshold(2))
+         {
+            reason = "Zero scans over " + ctx.statsAgeDays() + " days; " + n(index, "sizeBytes") / 1048576 + " MB";
+         }
+         if(this == DUPLICATE_INDEX && plainIndex(index))
+         {
+            List<String> columns = PredicateParser.indexColumns(index.getValueString("columns"));
+            for(QRecord other : indexes)
+            {
+               List<String> covering = PredicateParser.indexColumns(other.getValueString("columns"));
+               if(!index.getValueInteger("id").equals(other.getValueInteger("id")) && Objects.equals(index.getValueInteger("tableStatId"), other.getValueInteger("tableStatId"))
+                  && Boolean.TRUE.equals(other.getValueBoolean("isValid")) && plainIndex(other) && !columns.isEmpty() && covering.size() >= columns.size()
+                  && covering.subList(0, columns.size()).equals(columns))
+               {
+                  boolean exact = columns.equals(covering);
+                  boolean unique = Boolean.TRUE.equals(other.getValueBoolean("isUnique")) || Boolean.TRUE.equals(other.getValueBoolean("isPrimary"));
+                  if(exact && (unique || other.getValueInteger("id") < index.getValueInteger("id")) || !exact && !unique)
+                  {
+                     reason = "Redundant with " + other.getValueString("indexName") + ": " + other.getValueString("definition");
+                     break;
+                  }
+               }
+            }
+         }
+         if(reason != null)
+         {
+            findings.add(draft(ctx, index.getValueString("schemaName") + "." + index.getValueString("indexName"), n(index, "sizeBytes") / Math.max(1, bytes), reason,
+               evidence("Index bytes", n(index, "sizeBytes"), "Scans", n(index, "idxScan"), "Write cost", n(index, "writeCost"), "Definition", index.getValueString("definition")),
+               "Review rare workloads and dependencies before dropping this index. Commit the migration only after verifying the replacement and rollback.",
+               MigrationSnippets.drop(ctx.now, index, code(), reason), index, "index", null));
+         }
+      }
+      return (findings);
+   }
+
+
+   /*******************************************************************************
+    ** Column prefixes alone cannot prove equivalence for partial/expression or
+    ** non-btree indexes. Such structures need human review, not a drop snippet.
+    *******************************************************************************/
+   private static boolean plainIndex(QRecord index)
+   {
+      String definition = index.getValueString("definition");
+      if(definition == null)
+      {
+         return (false);
+      }
+      String lower = definition.toLowerCase(Locale.ROOT);
+      return (!PredicateParser.indexColumns(index.getValueString("columns")).isEmpty() && !lower.contains(" where ") && !lower.contains(" include ")
+         && (!lower.contains(" using ") || lower.contains(" using btree ")));
+   }
+
+
+   /*******************************************************************************
+    ** Aggregate pods/windows before applying the minimum affected-unit threshold.
+    *******************************************************************************/
+   private List<FindingDraft> repeats(RuleContext ctx) throws Exception
+   {
+      Map<String, List<QRecord>> groups = group(ctx.window("repeat_pattern", 1), "unitKind", "unitName", "fingerprint");
+      List<FindingDraft> findings = new ArrayList<>();
+      for(var entry : groups.entrySet())
+      {
+         List<QRecord> rows = entry.getValue();
+         double max = rows.stream().mapToDouble(r -> n(r, "maxRepeats")).max().orElse(0);
+         double units = rows.stream().mapToDouble(r -> n(r, "unitsAffected")).sum();
+         if(max >= ctx.threshold(1) && units >= ctx.threshold(2))
+         {
+            QRecord row = rows.getFirst();
+            findings.add(draft(ctx, entry.getKey(), Math.min(1, max / Math.max(1, ctx.threshold(1) * 10)), "Repeated queries in " + row.getValueString("unitKind") + " " + row.getValueString("unitName"),
+               evidence("Max repeats", max, "Units affected", units, "Total repeats", rows.stream().mapToDouble(r -> n(r, "totalRepeats")).sum()),
+               "Batch lookups on " + row.getValueString("tableName") + " in " + row.getValueString("unitName") + " into one query with IN (...), a QQQ QueryJoin, or a record pre-fetch.", null, row, "unit", row.getValueString("unitName")));
+         }
+      }
+      return (findings);
+   }
+
+
+   /*******************************************************************************
+    ** Share is relative to all attributed work, including HTTP routes.
+    *******************************************************************************/
+   private List<FindingDraft> jobs(RuleContext ctx) throws Exception
+   {
+      List<QRecord> rows = ctx.window("attribution", 1);
+      double total = rows.stream().mapToDouble(r -> n(r, "totalMs")).sum();
+      List<FindingDraft> findings = new ArrayList<>();
+      for(var entry : group(rows, "unitKind", "unitName").entrySet())
+      {
+         QRecord row = entry.getValue().getFirst();
+         double time = entry.getValue().stream().mapToDouble(r -> n(r, "totalMs")).sum();
+         double share = total == 0 ? 0 : time / total;
+         if(List.of("SCHEDULED_JOB", "PROCESS").contains(row.getValueString("unitKind")) && total > 0 && share * 100 >= ctx.threshold(1))
+         {
+            findings.add(draft(ctx, entry.getKey(), share, "Expensive " + row.getValueString("unitKind") + " " + row.getValueString("unitName"),
+               evidence("Total ms", time, "Attributed time %", share * 100), "Review the schedule and batch database operations in " + row.getValueString("unitName") + ".", null, row, "unit", row.getValueString("unitName")));
+         }
+      }
+      return (findings);
+   }
+
+
+   /*******************************************************************************
+    ** Per-application peaks sum users/states within each snapshot first.
+    *******************************************************************************/
+   private List<FindingDraft> pools(RuleContext ctx) throws Exception
+   {
+      double peak = 0;
+      double idle = 0;
+      double maxConnections = 0;
+      for(QRecord row : ctx.window("snapshot", 1))
+      {
+         if(n(row, "maxConnections") > 0)
+         {
+            peak = Math.max(peak, n(row, "connectionsTotal") / n(row, "maxConnections") * 100);
+            maxConnections = Math.max(maxConnections, n(row, "maxConnections"));
+         }
+         if(n(row, "connectionsTotal") > 0)
+         {
+            idle = Math.max(idle, n(row, "connectionsIdle") / n(row, "connectionsTotal") * 100);
+         }
+      }
+      if(maxConnections == 0 || peak < ctx.threshold(1) && idle < ctx.threshold(2))
+      {
+         return (List.of());
+      }
+      Map<String, Double> peaks = new LinkedHashMap<>();
+      Map<String, Double> activePeaks = new LinkedHashMap<>();
+      for(List<QRecord> rows : group(ctx.window("connection_sample", 1), "snapshotId", "applicationName").values())
+      {
+         String app = String.valueOf(rows.getFirst().getValueString("applicationName"));
+         peaks.merge(app, rows.stream().mapToDouble(r -> n(r, "connectionCount")).sum(), Math::max);
+         activePeaks.merge(app, rows.stream().filter(r -> "active".equals(r.getValueString("state"))).mapToDouble(r -> n(r, "connectionCount")).sum(), Math::max);
+      }
+      double desired = activePeaks.values().stream().mapToDouble(v -> Math.max(1, Math.ceil(v * 1.2))).sum();
+      double scale = Math.min(1, maxConnections * 0.8 / Math.max(1, desired));
+      String sizes = activePeaks.entrySet().stream().map(e -> e.getKey() + ": combined pool cap " + Math.max(1, (int) Math.floor(Math.max(1, Math.ceil(e.getValue() * 1.2)) * scale))
+         + " across replicas (peak active " + e.getValue().intValue() + ")").collect(Collectors.joining("; "));
+      String applications = peaks.entrySet().stream().map(e -> e.getKey() + ": peak " + e.getValue().intValue()).collect(Collectors.joining("; "));
+      return (List.of(draft(ctx, "database", Math.max(peak, idle) / 100, "Review connection pool sizes",
+         evidence("Peak used %", peak, "Peak idle %", idle, "Max connections", maxConnections, "Application peaks", applications),
+         "Starting pool sizes from observed active demand: " + sizes + ". Validate under representative load and divide combined caps across replicas; reserve capacity for administration and other applications. Observed application peaks: " + applications,
+         null, null, "database", null)));
+   }
+
+
+   /*******************************************************************************
+    ** One finding per application, retaining the worst observed transaction age.
+    *******************************************************************************/
+   private List<FindingDraft> idle(RuleContext ctx) throws Exception
+   {
+      List<QRecord> rows = ctx.window("activity_sample", 1).stream().filter(r -> "IDLE_IN_TRANSACTION".equals(r.getValueString("reason"))).toList();
+      List<FindingDraft> findings = new ArrayList<>();
+      for(var entry : group(rows, "applicationName").entrySet())
+      {
+         double seconds = entry.getValue().stream().mapToDouble(r -> n(r, "xactSeconds")).max().orElse(0);
+         if(seconds >= ctx.threshold(1))
+         {
+            String application = String.valueOf(entry.getValue().getFirst().getValueString("applicationName"));
+            findings.add(draft(ctx, entry.getKey(), Math.min(1, seconds / Math.max(1, ctx.threshold(1) * 10)), "Idle transaction in " + application,
+               evidence("Maximum transaction seconds", seconds, "Samples", entry.getValue().size()),
+               "Fix transaction scope in " + application + "; consider idle_in_transaction_session_timeout.", null, null, "database", application));
+         }
+      }
+      return (findings);
+   }
+
+
+   /*******************************************************************************
+    ** Database counters are already deltas; temporary blocks are 8 KiB each.
+    *******************************************************************************/
+   private List<FindingDraft> database(RuleContext ctx) throws Exception
+   {
+      List<QRecord> snapshots = ctx.window("snapshot", 1).stream().filter(r -> "OK".equals(r.getValueString("status")) || this != CACHE_HIT_LOW && "PARTIAL".equals(r.getValueString("status"))).toList();
+      List<FindingDraft> findings = new ArrayList<>();
+      if(this == CACHE_HIT_LOW)
+      {
+         var average = snapshots.stream().filter(r -> r.getValue("cacheHitRatio") != null).mapToDouble(r -> n(r, "cacheHitRatio")).average();
+         if(average.isPresent() && average.getAsDouble() < ctx.threshold(1))
+         {
+            findings.add(draft(ctx, "database", 1 - average.getAsDouble() / 100, "Low average database cache hit ratio", evidence("Average hit %", average.getAsDouble(), "OK snapshots", snapshots.size()),
+               "Review query scans and the working set before increasing database memory.", null, null, "database", null));
+         }
+      }
+      else if(this == DEADLOCKS)
+      {
+         double count = snapshots.stream().mapToDouble(r -> n(r, "deadlocks")).sum();
+         if(!snapshots.isEmpty() && count >= ctx.threshold(1))
+         {
+            findings.add(draft(ctx, "database", Math.min(1, count / Math.max(1, ctx.threshold(1) * 10)), "Database deadlocks detected", evidence("Deadlocks in 24h", count),
+               "Use consistent lock ordering and shorter transactions. Review affected application operations.", null, null, "database", null));
+         }
+      }
+      else
+      {
+         double databaseMb = snapshots.stream().mapToDouble(r -> n(r, "tempBytes")).sum() / 1048576;
+         Map<Integer, QRecord> statements = ctx.identities("statement").stream().collect(Collectors.toMap(r -> r.getValueInteger("id"), r -> r));
+         for(var entry : group(ctx.window("query_snapshot", 1), "statementId").entrySet())
+         {
+            double mb = entry.getValue().stream().mapToDouble(r -> n(r, "tempBlksWritten")).sum() / 128;
+            if(mb >= ctx.threshold(1))
+            {
+               QRecord statement = statements.get(entry.getValue().getFirst().getValueInteger("statementId"));
+               findings.add(draft(ctx, entry.getKey(), mb / Math.max(1, databaseMb), "Query spilled temporary data to disk",
+                  evidence("Query temp MB", mb, "Database temp MB", databaseMb), "Inspect sorts and hash joins in Query plan. Tune the query and review work_mem per operation, accounting for concurrent queries.", null, statement, "statement", null));
+            }
+         }
+         if(databaseMb >= ctx.threshold(1))
+         {
+            findings.add(draft(ctx, "database", Math.min(1, databaseMb / Math.max(1, ctx.threshold(1) * 10)), "Database temporary disk spill",
+               evidence("Database temp MB in 24h", databaseMb), "Review spilling queries, including those outside the stored top-N. Size work_mem with concurrency in mind.", null, null, "database", null));
+         }
+      }
+      return (findings);
+   }
+
+
+   /*******************************************************************************
+    ** Delimit parts with lengths to avoid collisions between unit names.
+    *******************************************************************************/
+   private static Map<String, List<QRecord>> group(List<QRecord> rows, String... fields)
+   {
+      Map<String, List<QRecord>> groups = new LinkedHashMap<>();
+      for(QRecord row : rows)
+      {
+         String key = java.util.Arrays.stream(fields).map(f -> String.valueOf(row.getValue(f))).map(s -> s.length() + ":" + s).collect(Collectors.joining("|"));
+         groups.computeIfAbsent(key, ignored -> new ArrayList<>()).add(row);
+      }
+      return (groups);
+   }
+
+
+   /*******************************************************************************
+    ** Shared foreign-key comparison.
+    *******************************************************************************/
+   private static boolean sameTable(QRecord table, QRecord index)
+   {
+      return (table.getValueInteger("id").equals(index.getValueInteger("tableStatId")));
+   }
+
+
+   /*******************************************************************************
+    ** Numeric convenience keeps units visible at the comparison sites.
+    *******************************************************************************/
+   private static double n(QRecord row, String field)
+   {
+      return (RuleContext.number(row, field));
+   }
+
+
+   /*******************************************************************************
+    ** Small Markdown table with escaped cell delimiters.
+    *******************************************************************************/
+   private static String evidence(Object... cells)
+   {
+      StringBuilder text = new StringBuilder("| Measurement | Value |\n| --- | --- |\n");
+      for(int i = 0; i < cells.length; i += 2)
+      {
+         text.append("| ").append(cells[i]).append(" | ").append(String.valueOf(cells[i + 1]).replace("|", "\\|").replace("\n", " ")).append(" |\n");
+      }
+      return (text.toString());
+   }
+
+
+   /*******************************************************************************
+    ** Centralize persisted links and severity; no rule invents a fixed severity.
+    *******************************************************************************/
+   private FindingDraft draft(RuleContext ctx, String subject, double impact, String summary, String evidence, String recommendation,
+                              String snippet, QRecord row, String kind, String unit)
+   {
+      String table = row == null ? null : row.getValueString(kind.equals("statement") ? "mainTable" : "tableName");
+      Integer id = row == null ? null : row.getValueInteger("id");
+      String key = code() + ":" + ctx.backend + ":" + subject;
+      if(key.length() > 300)
+      {
+         key = code() + ":" + ctx.backend + ":" + SqlNormalizer.fingerprint(subject);
+      }
+      String title = ctx.rule.getValueString("label") + ": " + (unit == null ? subject : unit);
+      return (new FindingDraft(code(), key, ctx.rule.getValueString("severity"), impact,
+         title.substring(0, Math.min(300, title.length())), summary.substring(0, Math.min(2000, summary.length())), evidence, recommendation, snippet, table,
+         kind.equals("statement") ? id : null, kind.equals("table") ? id : kind.equals("index") ? row.getValueInteger("tableStatId") : null,
+         kind.equals("index") ? id : null, unit));
+   }
+}

src/main/java/com/kingsrook/qbits/dbperformance/collect/SnapshotCollector.java

@@ -143,6 +143,7 @@ public class SnapshotCollector
                }
             }
          }
+         new com.kingsrook.qbits.dbperformance.alerts.SnapshotEvaluator(runtime).evaluate(snapshot);
          return (snapshot);
       }
    }

src/main/java/com/kingsrook/qbits/dbperformance/health/DbPerfHealthChecks.java

@@ -0,0 +1,153 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2026.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dbperformance.health;
+
+
+import java.time.Clock;
+import java.time.Duration;
+import java.util.List;
+import com.kingsrook.qbits.dbperformance.DbPerfRuntime;
+import com.kingsrook.qbits.dbperformance.analyze.RuleContext;
+import com.kingsrook.qbits.dbperformance.collect.DbPerfStore;
+import com.kingsrook.qqq.backend.core.health.HealthCategory;
+import com.kingsrook.qqq.backend.core.health.HealthCheckKind;
+import com.kingsrook.qqq.backend.core.health.HealthCheckMetaData;
+import com.kingsrook.qqq.backend.core.health.HealthCheckOutcome;
+import com.kingsrook.qqq.backend.core.health.HealthChecksMetaData;
+import com.kingsrook.qqq.backend.core.health.HealthDetails;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QCriteriaOperator;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QFilterCriteria;
+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.metadata.QInstance;
+import com.kingsrook.qqq.backend.module.rdbms.model.metadata.RDBMSBackendMetaData;
+
+
+/*******************************************************************************
+ ** Passive core health checks. No live database probes and no SQL in details.
+ *******************************************************************************/
+public class DbPerfHealthChecks
+{
+   private final DbPerfRuntime runtime;
+   private final Clock clock;
+
+
+   /*******************************************************************************
+    ** Bind observations to this runtime and clock.
+    *******************************************************************************/
+   public DbPerfHealthChecks(DbPerfRuntime runtime, Clock clock)
+   {
+      this.runtime = runtime;
+      this.clock = clock;
+   }
+
+
+   /*******************************************************************************
+    ** Register the three central database dependency checks.
+    *******************************************************************************/
+   public static void register(QInstance instance)
+   {
+      DbPerfHealthChecks checks = new DbPerfHealthChecks(DbPerfRuntime.of(instance), Clock.systemUTC());
+      for(String name : List.of("collector", "stats_access", "connections"))
+      {
+         HealthChecksMetaData.ofOrWithNew(instance).withCheck(new HealthCheckMetaData().withName("db_performance." + name)
+            .withLabel("Database performance " + name.replace('_', ' ')).withComponent("db_performance")
+            .withCategory(HealthCategory.DATABASE).withKind(HealthCheckKind.DEPENDENCY).withIntervalSeconds(60).withTimeoutMs(5000)
+            .withCheckInstance(input -> checks.check(name)));
+      }
+   }
+
+
+   /*******************************************************************************
+    ** Errors expose only a remedy, never a JDBC exception or query text.
+    *******************************************************************************/
+   public HealthCheckOutcome check(String name)
+   {
+      try
+      {
+         return (runtime.asSystem(() -> evaluate(name)));
+      }
+      catch(Exception e)
+      {
+         return (HealthCheckOutcome.down("Unable to read Database Performance health; check connectivity and migrations"));
+      }
+   }
+
+
+   /*******************************************************************************
+    ** Missing settings never cause a write during a health request.
+    *******************************************************************************/
+   private HealthCheckOutcome evaluate(String name) throws Exception
+   {
+      String backend = runtime.getConfig().getBackendName();
+      QRecord settings = DbPerfStore.first("db_perf_settings", DbPerfStore.eq("backendName", backend));
+      if(settings == null || !Boolean.TRUE.equals(settings.getValueBoolean("enabled")))
+      {
+         return (HealthCheckOutcome.unknown("Database performance collection is disabled or not configured"));
+      }
+      if(name.equals("stats_access"))
+      {
+         String access = settings.getValueString("statsAccess");
+         if(access == null)
+         {
+            return (HealthCheckOutcome.unknown("No statistics access observation yet"));
+         }
+         String user = ((RDBMSBackendMetaData) runtime.getInstance().getBackend(backend)).getUsername();
+         HealthCheckOutcome outcome = switch(access)
+         {
+            case "FULL" -> HealthCheckOutcome.up("Full statistics access");
+            case "NO_READ_ALL_STATS" -> HealthCheckOutcome.degraded("grant pg_read_all_stats to " + user);
+            case "NO_PG_STAT_STATEMENTS" -> HealthCheckOutcome.down("Enable pg_stat_statements in shared_preload_libraries and create the extension");
+            case "UNSUPPORTED" -> HealthCheckOutcome.down("Use a supported PostgreSQL version (13 or newer) and statistics provider");
+            default -> HealthCheckOutcome.unknown("Statistics access has not been classified");
+         };
+         String safe = List.of("FULL", "NO_READ_ALL_STATS", "NO_PG_STAT_STATEMENTS", "UNSUPPORTED").contains(access) ? access : "UNKNOWN";
+         return (outcome.withDetails(new HealthDetails().withValue("statsAccess", safe)));
+      }
+      var filter = DbPerfStore.eq("backendName", backend).withOrderBy(new QFilterOrderBy("capturedAt", false));
+      if(name.equals("collector"))
+      {
+         filter.withCriteria(new QFilterCriteria("status", QCriteriaOperator.EQUALS, "OK"));
+      }
+      QRecord snapshot = DbPerfStore.first("db_perf_snapshot", filter);
+      if(snapshot == null)
+      {
+         return (name.equals("collector") ? HealthCheckOutcome.down("No successful snapshot; run Collect now and check statistics access") : HealthCheckOutcome.unknown("No connection sample yet"));
+      }
+      if(name.equals("collector"))
+      {
+         long age = Math.max(0, Duration.between(snapshot.getValueInstant("capturedAt"), clock.instant()).getSeconds());
+         double interval = Math.max(1, RuleContext.number(settings, "snapshotIntervalMinutes")) * 60;
+         HealthCheckOutcome outcome = age <= 3 * interval ? HealthCheckOutcome.up("Collector is current")
+            : age <= 6 * interval ? HealthCheckOutcome.degraded("Collector is delayed") : HealthCheckOutcome.down("Collector is stale; check the collection schedule");
+         return (outcome.withDetails(new HealthDetails().withValue("ageSeconds", age).withValue("intervalSeconds", interval)));
+      }
+      double maximum = RuleContext.number(snapshot, "maxConnections");
+      if(maximum <= 0 || snapshot.getValue("connectionsTotal") == null)
+      {
+         return (HealthCheckOutcome.unknown("Latest snapshot has no connection capacity measurement"));
+      }
+      double used = 100 * RuleContext.number(snapshot, "connectionsTotal") / maximum;
+      HealthCheckOutcome outcome = used >= 95 ? HealthCheckOutcome.down("Connection usage is at least 95%")
+         : used >= RuleContext.number(settings, "connectionAlertPercent") ? HealthCheckOutcome.degraded("Connection usage reached the alert threshold") : HealthCheckOutcome.up("Connection capacity is available");
+      return (outcome.withDetails(new HealthDetails().withValue("usedPercent", used).withValue("connectionsTotal", snapshot.getValueInteger("connectionsTotal")).withValue("maxConnections", snapshot.getValueInteger("maxConnections"))));
+   }
+}

src/main/java/com/kingsrook/qbits/dbperformance/processes/DbPerfAnalyzeStep.java

@@ -0,0 +1,76 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2026.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dbperformance.processes;
+
+
+import java.util.ArrayList;
+import com.kingsrook.qbits.dbperformance.DbPerfRuntime;
+import com.kingsrook.qbits.dbperformance.analyze.AnalyzeService;
+import com.kingsrook.qqq.backend.core.actions.processes.BackendStep;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.exceptions.QException;
+import com.kingsrook.qqq.backend.core.model.actions.processes.ProcessSummaryLine;
+import com.kingsrook.qqq.backend.core.model.actions.processes.ProcessSummaryLineInterface;
+import com.kingsrook.qqq.backend.core.model.actions.processes.RunBackendStepInput;
+import com.kingsrook.qqq.backend.core.model.actions.processes.RunBackendStepOutput;
+import com.kingsrook.qqq.backend.core.model.actions.processes.Status;
+
+
+/*******************************************************************************
+ ** Scheduled analysis and Analyze now use the same stored-data evaluation path.
+ *******************************************************************************/
+public class DbPerfAnalyzeStep implements BackendStep
+{
+   /*******************************************************************************
+    ** Include per-rule counts and failures in the standard Result screen.
+    *******************************************************************************/
+   @Override
+   public void run(RunBackendStepInput input, RunBackendStepOutput output) throws QException
+   {
+      DbPerfRuntime runtime = DbPerfRuntime.of(QContext.getQInstance());
+      if(QContext.getQSession() == null || !QContext.getQSession().hasPermission(runtime.getConfig().getPermissionBaseName() + ".manage_settings"))
+      {
+         throw (new QException("Managing settings permission is required"));
+      }
+      ArrayList<ProcessSummaryLineInterface> lines = new ArrayList<>();
+      try
+      {
+         for(AnalyzeService.Result result : new AnalyzeService(runtime).run(runtime.getConfig().getBackendName()))
+         {
+            if(result.error() != null)
+            {
+               lines.add(new ProcessSummaryLine(Status.ERROR, null, result.ruleCode() + ": " + result.error()));
+            }
+            else
+            {
+               lines.add(new ProcessSummaryLine(Status.INFO, result.counts().created(), result.ruleCode() + " findings created"));
+               lines.add(new ProcessSummaryLine(Status.INFO, result.counts().updated(), result.ruleCode() + " findings updated"));
+               lines.add(new ProcessSummaryLine(Status.INFO, result.counts().resolved(), result.ruleCode() + " findings resolved"));
+            }
+         }
+      }
+      catch(Exception e)
+      {
+         throw (new QException("Unable to analyze database performance", e));
+      }
+      output.addValue("processResults", lines);
+   }
+}

src/main/java/com/kingsrook/qbits/dbperformance/processes/DbPerfProcesses.java

@@ -21,6 +21,7 @@
 package com.kingsrook.qbits.dbperformance.processes;
 
 
+import java.util.List;
 import com.kingsrook.qbits.dbperformance.DbPerfPermissions;
 import com.kingsrook.qbits.dbperformance.DbPerfQBitConfig;
 import com.kingsrook.qqq.backend.core.jobs.DurableProcessMetaData;
@@ -39,6 +40,8 @@ import com.kingsrook.qqq.backend.core.model.metadata.processes.QFunctionInputMet
 import com.kingsrook.qqq.backend.core.model.metadata.processes.QFunctionOutputMetaData;
 import com.kingsrook.qqq.backend.core.model.metadata.processes.QProcessMetaData;
 import com.kingsrook.qqq.backend.core.model.metadata.scheduleing.QScheduleMetaData;
+import com.kingsrook.qqq.backend.core.processes.implementations.etl.streamedwithfrontend.ExtractViaQueryStep;
+import com.kingsrook.qqq.backend.core.processes.implementations.etl.streamedwithfrontend.StreamedETLWithFrontendProcess;
 
 
 /*******************************************************************************
@@ -46,6 +49,8 @@ import com.kingsrook.qqq.backend.core.model.metadata.scheduleing.QScheduleMetaDa
  *******************************************************************************/
 public final class DbPerfProcesses
 {
+   public static final String ANALYZE = "dbPerfAnalyze";
+   public static final String ANALYZE_NOW = "dbPerfAnalyzeNow";
    public static final String COLLECT = "dbPerfCollectSnapshot";
    public static final String ROLLUP = "dbPerfRollupAndPurge";
    public static final String COLLECT_NOW = "dbPerfCollectNow";
@@ -64,6 +69,31 @@ public final class DbPerfProcesses
     *******************************************************************************/
    public static void addTo(QInstance instance, DbPerfQBitConfig config)
    {
+      instance.addProcess(scheduled(config, ANALYZE, "Analyze database performance", DbPerfAnalyzeStep.class, config.getAnalyzeCron()));
+      instance.addProcess(base(config, ANALYZE_NOW, "Analyze now", DbPerfAnalyzeStep.class, false).withTableName("db_perf_finding")
+         .withStep(new QFrontendStepMetaData().withName("result").withLabel("Result")
+            .withComponent(new QFrontendComponentMetaData().withType(QComponentType.PROCESS_SUMMARY_RESULTS))));
+      for(String status : List.of("ACKNOWLEDGED", "RESOLVED", "IGNORED", "OPEN"))
+      {
+         String label = switch(status)
+         {
+            case "ACKNOWLEDGED" -> "Acknowledge";
+            case "RESOLVED" -> "Mark resolved";
+            case "IGNORED" -> "Ignore";
+            default -> "Reopen";
+         };
+         QProcessMetaData action = StreamedETLWithFrontendProcess.processMetaDataBuilder()
+            .withName("dbPerfFinding" + status).withLabel(label).withIcon(new QIcon("done"))
+            .withTableName("db_perf_finding").withSourceTable("db_perf_finding").withDestinationTable("db_perf_finding")
+            .withExtractStepClass(ExtractViaQueryStep.class).withTransformStepClass(FindingSteps.Transform.class).withLoadStepClass(FindingSteps.Load.class)
+            .withSupportsFullValidation(true).withDoFullValidation(true).withMinInputRecords(1)
+            .withPreviewMessage("Review the selected findings before changing their status.")
+            .withFields(List.of(new QFieldMetaData(FindingSteps.TARGET, QFieldType.STRING).withDefaultValue(status))).getProcessMetaData();
+         action.withPermissionRules(DbPerfPermissions.rules(config.getPermissionBaseName() + ".manage_findings"))
+            .withStep(0, new QFrontendStepMetaData().withName("note").withLabel("Note")
+               .withFormField(new QFieldMetaData(FindingSteps.NOTE, QFieldType.STRING).withLabel("Note").withMaxLength(2000).withIsRequired("IGNORED".equals(status))));
+         instance.addProcess(action);
+      }
       instance.addProcess(scheduled(config, COLLECT, "Collect database snapshot", DbPerfCollectStep.class, config.getSnapshotCron()));
       instance.addProcess(scheduled(config, ROLLUP, "Roll up and purge database performance", DbPerfRollupStep.class, config.getRollupCron()));
       instance.addProcess(base(config, COLLECT_NOW, "Collect now", DbPerfCollectStep.class, true)

src/main/java/com/kingsrook/qbits/dbperformance/processes/FindingSteps.java

@@ -0,0 +1,212 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2026.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dbperformance.processes;
+
+
+import java.time.Instant;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map;
+import com.kingsrook.qbits.dbperformance.DbPerfRuntime;
+import com.kingsrook.qbits.dbperformance.analyze.FindingService;
+import com.kingsrook.qbits.dbperformance.collect.DbPerfStore;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.exceptions.QException;
+import com.kingsrook.qqq.backend.core.model.actions.processes.ProcessSummaryLine;
+import com.kingsrook.qqq.backend.core.model.actions.processes.ProcessSummaryLineInterface;
+import com.kingsrook.qqq.backend.core.model.actions.processes.RunBackendStepInput;
+import com.kingsrook.qqq.backend.core.model.actions.processes.RunBackendStepOutput;
+import com.kingsrook.qqq.backend.core.model.actions.processes.Status;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+import com.kingsrook.qqq.backend.core.processes.implementations.etl.streamedwithfrontend.AbstractLoadStep;
+import com.kingsrook.qqq.backend.core.processes.implementations.etl.streamedwithfrontend.AbstractTransformStep;
+import com.kingsrook.qqq.backend.core.processes.implementations.etl.streamedwithfrontend.ProcessSummaryProviderInterface;
+
+
+/*******************************************************************************
+ ** Selected-record streamed actions. Review does not write; Load rechecks access
+ ** under the caller session before narrowly scoped system writes.
+ *******************************************************************************/
+public final class FindingSteps
+{
+   public static final String NOTE = "statusNote";
+   public static final String TARGET = "findingStatus";
+   private static final Map<String, String> VERBS = Map.of("ACKNOWLEDGED", "acknowledged", "RESOLVED", "marked resolved", "IGNORED", "ignored", "OPEN", "reopened");
+
+
+   /*******************************************************************************
+    ** Utility class.
+    *******************************************************************************/
+   private FindingSteps()
+   {
+   }
+
+
+   /*******************************************************************************
+    ** Both phases validate permission, requested state and Ignore's required note.
+    *******************************************************************************/
+   private static String validate(RunBackendStepInput input) throws QException
+   {
+      DbPerfRuntime runtime = DbPerfRuntime.of(QContext.getQInstance());
+      if(QContext.getQSession() == null || !QContext.getQSession().hasPermission(runtime.getConfig().getPermissionBaseName() + ".manage_findings"))
+      {
+         throw (new QException("Managing findings permission is required"));
+      }
+      String status = input.getValueString(TARGET);
+      if(!VERBS.containsKey(status == null ? "" : status))
+      {
+         throw (new QException("Invalid finding status"));
+      }
+      String note = input.getValueString(NOTE);
+      if("IGNORED".equals(status) && (note == null || note.isBlank()) || note != null && note.length() > 2000)
+      {
+         throw (new QException("Ignore requires a note; notes must be at most 2000 characters"));
+      }
+      return (status);
+   }
+
+
+   /*******************************************************************************
+    ** Standard singular/plural and future/past wording.
+    *******************************************************************************/
+   private static ProcessSummaryLine line(Status status, String future, String past)
+   {
+      ProcessSummaryLine line = new ProcessSummaryLine(status, 0, null);
+      line.setSingularFutureMessage("finding will be " + future);
+      line.setPluralFutureMessage("findings will be " + future);
+      line.setSingularPastMessage("finding was " + past);
+      line.setPluralPastMessage("findings were " + past);
+      return (line);
+   }
+
+
+   /*******************************************************************************
+    ** The Review screen describes precisely the selected records.
+    *******************************************************************************/
+   public static class Transform extends AbstractTransformStep implements ProcessSummaryProviderInterface
+   {
+      private ProcessSummaryLine changes;
+
+
+      /*******************************************************************************
+       ** Validate and forward a bounded page with no mutations.
+       *******************************************************************************/
+      @Override
+      public void runOnePage(RunBackendStepInput input, RunBackendStepOutput output) throws QException
+      {
+         String verb = VERBS.get(validate(input));
+         if(changes == null)
+         {
+            changes = line(Status.OK, verb, verb);
+         }
+         for(QRecord record : input.getRecords())
+         {
+            changes.incrementCountAndAddPrimaryKey(record.getValue("id"));
+            output.addRecord(record);
+         }
+      }
+
+
+      /*******************************************************************************
+       ** The frontend chooses the correct tense and plurality.
+       *******************************************************************************/
+      @Override
+      public ArrayList<ProcessSummaryLineInterface> getProcessSummary(RunBackendStepOutput output, boolean result)
+      {
+         return (changes == null ? new ArrayList<>() : new ArrayList<>(List.of(changes)));
+      }
+   }
+
+
+   /*******************************************************************************
+    ** Persist the status fields only; supplied record data is never trusted.
+    *******************************************************************************/
+   public static class Load extends AbstractLoadStep implements ProcessSummaryProviderInterface
+   {
+      private ProcessSummaryLine changes;
+      private final ProcessSummaryLine errors = line(Status.ERROR, "checked for access", "not updated; unavailable or write failed");
+
+
+      /*******************************************************************************
+       ** Preserve the human user id before entering the system write context.
+       *******************************************************************************/
+      @Override
+      public void runOnePage(RunBackendStepInput input, RunBackendStepOutput output) throws QException
+      {
+         String status = validate(input);
+         String actor = QContext.getQSession().getUser() == null ? null : QContext.getQSession().getUser().getIdReference();
+         DbPerfRuntime runtime = DbPerfRuntime.of(QContext.getQInstance());
+         if(changes == null)
+         {
+            changes = line(Status.OK, VERBS.get(status), VERBS.get(status));
+         }
+         for(QRecord selected : input.getRecords())
+         {
+            try
+            {
+               // Read under the caller context so record security still applies.
+               QRecord visible = DbPerfStore.first("db_perf_finding", DbPerfStore.eq("id", selected.getValueInteger("id")));
+               if(visible == null || !runtime.getConfig().getBackendName().equals(visible.getValueString("backendName")))
+               {
+                  errors.incrementCountAndAddPrimaryKey(selected.getValue("id"));
+                  continue;
+               }
+               QRecord patch = new QRecord().withValue("id", visible.getValueInteger("id")).withValue("status", status)
+                  .withValue("statusChangedBy", actor).withValue("resolvedAt", "RESOLVED".equals(status) ? Instant.now() : null);
+               if(input.getValueString(NOTE) != null && !input.getValueString(NOTE).isBlank())
+               {
+                  patch.withValue(NOTE, input.getValueString(NOTE));
+               }
+               synchronized(runtime)
+               {
+                  runtime.transaction(() ->
+                  {
+                     FindingService.lockRule(runtime, visible.getValueString("ruleCode"));
+                     DbPerfStore.update("db_perf_finding", List.of(patch));
+                     return (null);
+                  });
+               }
+               changes.incrementCountAndAddPrimaryKey(selected.getValue("id"));
+            }
+            catch(Exception e)
+            {
+               errors.incrementCountAndAddPrimaryKey(selected.getValue("id"));
+            }
+         }
+      }
+
+
+      /*******************************************************************************
+       ** Include failures while allowing other selected records to complete.
+       *******************************************************************************/
+      @Override
+      public ArrayList<ProcessSummaryLineInterface> getProcessSummary(RunBackendStepOutput output, boolean result)
+      {
+         ArrayList<ProcessSummaryLineInterface> lines = new ArrayList<>();
+         if(changes != null)
+         {
+            lines.add(changes);
+         }
+         errors.addSelfToListIfAnyCount(lines);
+         return (lines);
+      }
+   }
+}

src/test/java/com/kingsrook/qbits/dbperformance/B1Fixture.java

@@ -0,0 +1,121 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2026.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dbperformance;
+
+
+import java.time.Clock;
+import java.time.Instant;
+import java.time.ZoneOffset;
+import java.util.List;
+import com.kingsrook.qbits.dbperformance.analyze.RuleContext;
+import com.kingsrook.qbits.dbperformance.analyze.StoredRule;
+import com.kingsrook.qbits.dbperformance.collect.DbPerfSettingsService;
+import com.kingsrook.qbits.dbperformance.collect.DbPerfStore;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+
+
+/*******************************************************************************
+ ** Real H2/QRecord fixtures shared by rule, alert and action integration tests.
+ *******************************************************************************/
+class B1Fixture
+{
+   static final Instant NOW = Instant.parse("2026-10-15T15:00:00Z");
+   static final Clock CLOCK = Clock.fixed(NOW, ZoneOffset.UTC);
+   final DbPerfRuntime runtime;
+   final String backend;
+   final QRecord settings;
+   final QRecord snapshot;
+   final QRecord table;
+   final QRecord statement;
+
+
+   /*******************************************************************************
+    ** Evidence crossing every seeded threshold without a live statistics probe.
+    *******************************************************************************/
+   B1Fixture() throws Exception
+   {
+      runtime = DbPerfRuntime.of(DbPerfTestSupport.instance());
+      backend = runtime.getConfig().getBackendName();
+      settings = new DbPerfSettingsService(runtime).ensure(backend);
+      snapshot = insert("snapshot", new QRecord().withValue("backendName", backend).withValue("capturedAt", NOW).withValue("status", "OK")
+         .withValue("rawStatsReset", NOW.minusSeconds(30 * 86400L)).withValue("cacheHitRatio", 90).withValue("deadlocks", 2L)
+         .withValue("connectionsTotal", 90).withValue("connectionsIdle", 60).withValue("maxConnections", 100).withValue("tempBytes", 200 * 1048576L));
+      table = insert("table_stat", new QRecord().withValue("backendName", backend).withValue("schemaName", "public").withValue("tableName", "audit_orders")
+         .withValue("lastSeenAt", NOW).withValue("seqScanShare", 80).withValue("seqScan", 800L).withValue("idxScan", 200L)
+         .withValue("nTupIns", 200000L).withValue("nDeadTup", 30000L).withValue("nLiveTup", 70000L).withValue("deadRatio", 30)
+         .withValue("lastVacuum", NOW.minusSeconds(10 * 86400L)).withValue("freezeAge", 160000000L));
+      insert("table_snapshot", new QRecord().withValue("snapshotId", snapshot.getValueInteger("id")).withValue("tableStatId", table.getValueInteger("id"))
+         .withValue("capturedAt", NOW).withValue("seqTupRead", 100000L).withValue("nTupIns", 10000L));
+      statement = insert("statement", new QRecord().withValue("backendName", backend).withValue("queryId", 1L).withValue("dbUser", "app").withValue("toplevel", true)
+         .withValue("fingerprint", "a".repeat(64)).withValue("queryText", "select * from audit_orders where customer_id = ? and status in (?)")
+         .withValue("mainTable", "audit_orders").withValue("lastSeenAt", NOW).withValue("calls24h", 100L).withValue("meanMs24h", 600)
+         .withValue("totalMs24h", 60000).withValue("shareOfDbTime24h", 25));
+      insert("query_snapshot", new QRecord().withValue("snapshotId", snapshot.getValueInteger("id")).withValue("statementId", statement.getValueInteger("id"))
+         .withValue("capturedAt", NOW).withValue("totalMs", 10000).withValue("tempBlksWritten", 150 * 128L));
+      index("idx_customer", "customer_id", false, 0);
+      index("idx_customer_copy", "customer_id", false, 0);
+      index("idx_customer_wide", "customer_id, created_at", false, 20);
+      insert("repeat_pattern", new QRecord().withValue("windowStart", NOW.minusSeconds(60)).withValue("windowEnd", NOW).withValue("unitKind", "PROCESS")
+         .withValue("unitName", "shipOrders").withValue("tableName", "orders").withValue("fingerprint", "a".repeat(64))
+         .withValue("maxRepeats", 20).withValue("unitsAffected", 3L).withValue("totalRepeats", 60L));
+      insert("repeat_pattern", new QRecord().withValue("windowStart", NOW.minusSeconds(60)).withValue("windowEnd", NOW).withValue("unitKind", "PROCESS")
+         .withValue("unitName", "shipOrders").withValue("tableName", "orders").withValue("fingerprint", "a".repeat(64))
+         .withValue("maxRepeats", 10).withValue("unitsAffected", 3L).withValue("totalRepeats", 30L));
+      insert("attribution", new QRecord().withValue("windowStart", NOW.minusSeconds(60)).withValue("windowEnd", NOW).withValue("unitKind", "PROCESS")
+         .withValue("unitName", "shipOrders").withValue("tableName", "audit_orders").withValue("operation", "INSERT").withValue("totalMs", 1000).withValue("statements", 300L));
+      insert("connection_sample", new QRecord().withValue("snapshotId", snapshot.getValueInteger("id")).withValue("capturedAt", NOW)
+         .withValue("applicationName", "orders-app").withValue("state", "active").withValue("connectionCount", 30));
+      insert("connection_sample", new QRecord().withValue("snapshotId", snapshot.getValueInteger("id")).withValue("capturedAt", NOW)
+         .withValue("applicationName", "orders-app").withValue("state", "idle").withValue("connectionCount", 60));
+      insert("activity_sample", new QRecord().withValue("snapshotId", snapshot.getValueInteger("id")).withValue("capturedAt", NOW)
+         .withValue("applicationName", "orders-app").withValue("reason", "IDLE_IN_TRANSACTION").withValue("xactSeconds", 120));
+   }
+
+
+   /*******************************************************************************
+    ** Consistent index metadata with collected definition.
+    *******************************************************************************/
+   QRecord index(String name, String columns, boolean unique, long scans) throws Exception
+   {
+      return (insert("index_stat", new QRecord().withValue("backendName", backend).withValue("schemaName", "public").withValue("tableName", "audit_orders")
+         .withValue("tableStatId", table.getValueInteger("id")).withValue("indexName", name).withValue("columns", columns)
+         .withValue("definition", "CREATE " + (unique ? "UNIQUE " : "") + "INDEX " + name + " ON public.audit_orders USING btree (" + columns + ")")
+         .withValue("isValid", true).withValue("isUnique", unique).withValue("isPrimary", false).withValue("idxScan", scans).withValue("sizeBytes", 2 * 1048576L).withValue("lastSeenAt", NOW)));
+   }
+
+
+   /*******************************************************************************
+    ** A fresh context observes edits to threshold rows on every call.
+    *******************************************************************************/
+   RuleContext context(StoredRule rule) throws Exception
+   {
+      return (new RuleContext(runtime, backend, DbPerfStore.first("db_perf_rule", DbPerfStore.eq("code", rule.code())), settings, NOW));
+   }
+
+
+   /*******************************************************************************
+    ** Use normal QQQ inserts and return the generated identity.
+    *******************************************************************************/
+   static QRecord insert(String suffix, QRecord row) throws Exception
+   {
+      return (DbPerfStore.insert("db_perf_" + suffix, List.of(row)).getFirst());
+   }
+}

src/test/java/com/kingsrook/qbits/dbperformance/DbPerfHealthTest.java

@@ -0,0 +1,112 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2026.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dbperformance;
+
+
+import java.time.Clock;
+import java.time.ZoneOffset;
+import java.util.List;
+import com.kingsrook.qbits.dbperformance.collect.DbPerfStore;
+import com.kingsrook.qbits.dbperformance.health.DbPerfHealthChecks;
+import com.kingsrook.qqq.backend.core.health.HealthCategory;
+import com.kingsrook.qqq.backend.core.health.HealthCheckKind;
+import com.kingsrook.qqq.backend.core.health.HealthChecksMetaData;
+import com.kingsrook.qqq.backend.core.health.HealthStatus;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+import org.junit.jupiter.api.Test;
+import static org.assertj.core.api.Assertions.assertThat;
+
+
+/*******************************************************************************
+ ** Passive health boundaries, metadata registration and typed detail guarantees.
+ *******************************************************************************/
+class DbPerfHealthTest
+{
+   /*******************************************************************************
+    ** Collector boundaries are inclusive at three and six intervals.
+    *******************************************************************************/
+   @Test
+   void collectorAgeAndDisabled() throws Exception
+   {
+      B1Fixture f = new B1Fixture();
+      assertThat(checks(f, 900).check("collector").getStatus()).isEqualTo(HealthStatus.UP);
+      assertThat(checks(f, 901).check("collector").getStatus()).isEqualTo(HealthStatus.DEGRADED);
+      assertThat(checks(f, 1800).check("collector").getStatus()).isEqualTo(HealthStatus.DEGRADED);
+      assertThat(checks(f, 1801).check("collector").getStatus()).isEqualTo(HealthStatus.DOWN);
+      DbPerfStore.update("db_perf_snapshot", List.of(new QRecord().withValue("id", f.snapshot.getValueInteger("id")).withValue("status", "FAILED")));
+      assertThat(checks(f, 0).check("collector").getStatus()).isEqualTo(HealthStatus.DOWN);
+      DbPerfStore.update("db_perf_settings", List.of(new QRecord().withValue("id", f.settings.getValueInteger("id")).withValue("enabled", false)));
+      assertThat(checks(f, 0).check("collector").getStatus()).isEqualTo(HealthStatus.UNKNOWN);
+      assertThat(HealthChecksMetaData.ofOrWithNew(f.runtime.getInstance()).getChecks()).hasSize(3).allSatisfy(metadata ->
+      {
+         assertThat(metadata.getComponent()).isEqualTo("db_performance");
+         assertThat(metadata.getCategory()).isEqualTo(HealthCategory.DATABASE);
+         assertThat(metadata.getKind()).isEqualTo(HealthCheckKind.DEPENDENCY);
+         assertThat(metadata.getIntervalSeconds()).isBetween(60, 300);
+         assertThat(metadata.getCheckInstance().run(null).getStatus()).isEqualTo(HealthStatus.UNKNOWN);
+      });
+   }
+
+
+   /*******************************************************************************
+    ** Access remedies and connection capacity reflect persisted data only.
+    *******************************************************************************/
+   @Test
+   void accessAndCapacity() throws Exception
+   {
+      B1Fixture f = new B1Fixture();
+      DbPerfHealthChecks checks = checks(f, 0);
+      for(String access : List.of("FULL", "NO_READ_ALL_STATS", "NO_PG_STAT_STATEMENTS", "UNSUPPORTED", "UNKNOWN"))
+      {
+         DbPerfStore.update("db_perf_settings", List.of(new QRecord().withValue("id", f.settings.getValueInteger("id")).withValue("statsAccess", access)));
+         var outcome = checks.check("stats_access");
+         assertThat(outcome.getDetails().getValues()).containsEntry("statsAccess", access).hasSize(1);
+         assertThat(outcome.getStatus()).isEqualTo(switch(access)
+         {
+            case "FULL" -> HealthStatus.UP;
+            case "NO_READ_ALL_STATS" -> HealthStatus.DEGRADED;
+            case "UNKNOWN" -> HealthStatus.UNKNOWN;
+            default -> HealthStatus.DOWN;
+         });
+         if(access.equals("NO_READ_ALL_STATS"))
+         {
+            assertThat(outcome.getMessage()).isEqualTo("grant pg_read_all_stats to sa");
+         }
+      }
+      for(int total : List.of(79, 80, 94, 95))
+      {
+         DbPerfStore.update("db_perf_snapshot", List.of(new QRecord().withValue("id", f.snapshot.getValueInteger("id")).withValue("connectionsTotal", total)));
+         assertThat(checks.check("connections").getStatus()).isEqualTo(total < 80 ? HealthStatus.UP : total < 95 ? HealthStatus.DEGRADED : HealthStatus.DOWN);
+         assertThat(checks.check("connections").getDetails().getValues().values()).allSatisfy(v -> assertThat(v).isInstanceOf(Number.class));
+      }
+      DbPerfStore.update("db_perf_snapshot", List.of(new QRecord().withValue("id", f.snapshot.getValueInteger("id")).withValue("maxConnections", null)));
+      assertThat(checks.check("connections").getStatus()).isEqualTo(HealthStatus.UNKNOWN);
+   }
+
+
+   /*******************************************************************************
+    ** A controllable clock avoids waiting for collection intervals in tests.
+    *******************************************************************************/
+   private static DbPerfHealthChecks checks(B1Fixture f, int seconds)
+   {
+      return (new DbPerfHealthChecks(f.runtime, Clock.fixed(B1Fixture.NOW.plusSeconds(seconds), ZoneOffset.UTC)));
+   }
+}

src/test/java/com/kingsrook/qbits/dbperformance/FindingActionsTest.java

@@ -0,0 +1,148 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2026.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dbperformance;
+
+
+import java.util.List;
+import com.kingsrook.qbits.dbperformance.analyze.FindingService;
+import com.kingsrook.qbits.dbperformance.analyze.StoredRule;
+import com.kingsrook.qbits.dbperformance.collect.DbPerfStore;
+import com.kingsrook.qbits.dbperformance.processes.DbPerfAnalyzeStep;
+import com.kingsrook.qbits.dbperformance.processes.DbPerfProcesses;
+import com.kingsrook.qbits.dbperformance.processes.FindingSteps;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.jobs.DurableProcessMetaData;
+import com.kingsrook.qqq.backend.core.jobs.model.JobSemantics;
+import com.kingsrook.qqq.backend.core.model.actions.processes.ProcessSummaryLine;
+import com.kingsrook.qqq.backend.core.model.actions.processes.RunBackendStepInput;
+import com.kingsrook.qqq.backend.core.model.actions.processes.RunBackendStepOutput;
+import com.kingsrook.qqq.backend.core.model.actions.processes.Status;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QQueryFilter;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+import com.kingsrook.qqq.backend.core.model.metadata.processes.QFrontendStepMetaData;
+import com.kingsrook.qqq.backend.core.model.session.QSession;
+import com.kingsrook.qqq.backend.core.model.session.QUser;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.ValueSource;
+import static org.assertj.core.api.Assertions.assertThat;
+import static org.assertj.core.api.Assertions.assertThatThrownBy;
+
+
+/*******************************************************************************
+ ** Record selection, Review/Result summaries, permission and note enforcement.
+ *******************************************************************************/
+class FindingActionsTest
+{
+   /*******************************************************************************
+    ** Every action updates only selected records and records the human actor.
+    *******************************************************************************/
+   @ParameterizedTest
+   @ValueSource(strings = { "ACKNOWLEDGED", "RESOLVED", "IGNORED", "OPEN" })
+   void selectedRecordAction(String status) throws Exception
+   {
+      B1Fixture f = new B1Fixture();
+      new FindingService(f.runtime).apply(f.backend, "UNUSED_INDEX", StoredRule.UNUSED_INDEX.evaluate(f.context(StoredRule.UNUSED_INDEX)));
+      List<QRecord> findings = DbPerfStore.query("db_perf_finding", new QQueryFilter());
+      f.runtime.getInstance().setEnforcePermissionsInCoreActions(true);
+      QContext.setQSession(new QSession().withPermissions(DbPerfPermissions.VIEW, DbPerfPermissions.MANAGE_FINDINGS).withUser(new QUser().withIdReference("operator-42")));
+      RunBackendStepInput input = new RunBackendStepInput();
+      input.setRecords(List.of(findings.getFirst()));
+      input.addValue(FindingSteps.TARGET, status);
+      input.addValue(FindingSteps.NOTE, "Reviewed by operator");
+      FindingSteps.Transform transform = new FindingSteps.Transform();
+      RunBackendStepOutput preview = new RunBackendStepOutput();
+      transform.runOnePage(input, preview);
+      ProcessSummaryLine line = (ProcessSummaryLine) transform.getProcessSummary(preview, false).getFirst();
+      assertThat(line.getCount()).isEqualTo(1);
+      assertThat(line.getPluralFutureMessage()).startsWith("findings will be ");
+      assertThat(DbPerfStore.first("db_perf_finding", DbPerfStore.eq("id", findings.getFirst().getValueInteger("id"))).getValueString("status")).isEqualTo("OPEN");
+      FindingSteps.Load load = new FindingSteps.Load();
+      RunBackendStepOutput result = new RunBackendStepOutput();
+      load.runOnePage(input, result);
+      ProcessSummaryLine actual = (ProcessSummaryLine) load.getProcessSummary(result, true).getFirst();
+      assertThat(actual.getCount()).isEqualTo(1);
+      assertThat(actual.getPluralPastMessage()).startsWith("findings were ");
+      QRecord changed = DbPerfStore.first("db_perf_finding", DbPerfStore.eq("id", findings.getFirst().getValueInteger("id")));
+      assertThat(changed.getValueString("status")).isEqualTo(status);
+      assertThat(changed.getValueString("statusChangedBy")).isEqualTo("operator-42");
+      assertThat(changed.getValueString("statusNote")).isEqualTo("Reviewed by operator");
+      assertThat(changed.getValueInstant("resolvedAt") != null).isEqualTo(status.equals("RESOLVED"));
+      assertThat(DbPerfStore.first("db_perf_finding", DbPerfStore.eq("id", findings.getLast().getValueInteger("id"))).getValueString("status")).isEqualTo("OPEN");
+      var metadata = f.runtime.getInstance().getProcess("dbPerfFinding" + status);
+      assertThat(metadata.getTableName()).isEqualTo("db_perf_finding");
+      assertThat(metadata.getPermissionRules().getPermissionBaseName()).isEqualTo(DbPerfPermissions.MANAGE_FINDINGS);
+      assertThat(metadata.getStepList()).extracting(step -> step.getLabel()).contains("Review", "Result");
+      QFrontendStepMetaData note = (QFrontendStepMetaData) metadata.getStepList().getFirst();
+      assertThat(note.getFormField(FindingSteps.NOTE).getIsRequired()).isEqualTo(status.equals("IGNORED"));
+   }
+
+
+   /*******************************************************************************
+    ** Direct invocation cannot bypass permission or Ignore validation.
+    *******************************************************************************/
+   @Test
+   void validationAndErrors() throws Exception
+   {
+      B1Fixture f = new B1Fixture();
+      RunBackendStepInput input = new RunBackendStepInput();
+      input.addValue(FindingSteps.TARGET, "IGNORED");
+      RunBackendStepOutput output = new RunBackendStepOutput();
+      assertThatThrownBy(() -> new FindingSteps.Transform().runOnePage(input, output)).hasMessageContaining("note");
+      assertThatThrownBy(() -> new FindingSteps.Load().runOnePage(input, output)).hasMessageContaining("note");
+      input.addValue(FindingSteps.NOTE, "x".repeat(2001));
+      assertThatThrownBy(() -> new FindingSteps.Load().runOnePage(input, output)).hasMessageContaining("2000");
+      input.addValue(FindingSteps.NOTE, "Reviewed");
+      QContext.setQSession(new QSession().withPermissions(DbPerfPermissions.VIEW));
+      assertThatThrownBy(() -> new FindingSteps.Transform().runOnePage(input, output)).hasMessageContaining("permission");
+      assertThatThrownBy(() -> new FindingSteps.Load().runOnePage(input, output)).hasMessageContaining("permission");
+      assertThatThrownBy(() -> new DbPerfAnalyzeStep().run(input, output)).hasMessageContaining("permission");
+      QContext.setQSession(new QSession().withPermissions(DbPerfPermissions.VIEW, DbPerfPermissions.MANAGE_FINDINGS));
+      input.addValue(FindingSteps.TARGET, "BAD_STATUS");
+      assertThatThrownBy(() -> new FindingSteps.Load().runOnePage(input, output)).hasMessageContaining("Invalid");
+      input.addValue(FindingSteps.TARGET, "RESOLVED");
+      input.setRecords(List.of(new QRecord().withValue("id", 999999)));
+      FindingSteps.Load load = new FindingSteps.Load();
+      load.runOnePage(input, output);
+      assertThat(load.getProcessSummary(output, true)).anySatisfy(line ->
+      {
+         assertThat(((ProcessSummaryLine) line).getStatus()).isEqualTo(Status.ERROR);
+         assertThat(((ProcessSummaryLine) line).getCount()).isEqualTo(1);
+      });
+   }
+
+
+   /*******************************************************************************
+    ** Analyzer metadata follows phase A durable scheduling and summary conventions.
+    *******************************************************************************/
+   @Test
+   void analyzeAction() throws Exception
+   {
+      B1Fixture f = new B1Fixture();
+      var process = f.runtime.getInstance().getProcess(DbPerfProcesses.ANALYZE);
+      assertThat(process.getIsHidden()).isTrue();
+      assertThat(DurableProcessMetaData.of(process).getSemantics()).isEqualTo(JobSemantics.ATOMIC);
+      assertThat(f.runtime.getInstance().getProcess(DbPerfProcesses.ANALYZE_NOW).getTableName()).isEqualTo("db_perf_finding");
+      RunBackendStepOutput output = new RunBackendStepOutput();
+      new DbPerfAnalyzeStep().run(new RunBackendStepInput(), output);
+      assertThat((List<?>) output.getValue("processResults")).hasSize(42);
+   }
+}

src/test/java/com/kingsrook/qbits/dbperformance/PredicateAndSnippetTest.java

@@ -0,0 +1,81 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2026.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dbperformance;
+
+
+import java.util.List;
+import com.kingsrook.qbits.dbperformance.analyze.MigrationSnippets;
+import com.kingsrook.qbits.dbperformance.analyze.PredicateParser;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+import org.junit.jupiter.api.Test;
+import static org.assertj.core.api.Assertions.assertThat;
+
+
+/*******************************************************************************
+ ** Best-effort SQL parsing and ready-to-review PostgreSQL migration text.
+ *******************************************************************************/
+class PredicateAndSnippetTest
+{
+   /*******************************************************************************
+    ** Bind values, comments, aliases and quoted columns retain table ownership.
+    *******************************************************************************/
+   @Test
+   void predicates()
+   {
+      assertThat(PredicateParser.candidates("SELECT * FROM public.orders o JOIN customers c ON c.id = o.customer_id WHERE o.status IN ($1, $2) AND c.name = $3", "public", "orders"))
+         .containsExactly("customer_id", "status");
+      assertThat(PredicateParser.candidates("select * from orders where id = ? and status in (?)", "public", "orders")).containsExactly("id", "status");
+      assertThat(PredicateParser.candidates("select * from orders where lower(name) = ? order by created_at", "public", "orders")).isEmpty();
+      assertThat(PredicateParser.candidates("select * from orders where id in (select id from customers)", "public", "orders")).isEmpty();
+      assertThat(PredicateParser.candidates("select * from other.orders where id = ?", "public", "orders")).isEmpty();
+      assertThat(PredicateParser.candidates("select * from \"Order\" where \"Customer ID\" = 'private' /* where fake = ? */", "public", "Order")).containsExactly("Customer ID");
+      assertThat(PredicateParser.indexColumns("\"last,name\", id")).containsExactly("last,name", "id");
+      assertThat(PredicateParser.indexColumns("lower(name)")).isEmpty();
+      assertThat(PredicateParser.indexColumns("id,")).isEmpty();
+      assertThat(PredicateParser.indexColumns(null)).isEmpty();
+   }
+
+
+   /*******************************************************************************
+    ** Concurrent DDL has a transaction opt-out and schema-qualified rollback.
+    *******************************************************************************/
+   @Test
+   void snippets()
+   {
+      QRecord table = new QRecord().withValue("schemaName", "public").withValue("tableName", "order");
+      String create = MigrationSnippets.create(B1Fixture.NOW, table, List.of("Customer ID", "status"), "1000000 reads/day\nreview first");
+      assertThat(create).startsWith("-- liquibase formatted sql\n-- changeset db-performance:20261015-")
+         .contains("runInTransaction:false", "CREATE INDEX CONCURRENTLY IF NOT EXISTS", "ON public.\"order\" (\"Customer ID\", status)", "-- rollback DROP INDEX CONCURRENTLY IF EXISTS public.")
+         .contains("1000000 reads/day review first");
+      String name = MigrationSnippets.indexName("really_long_table_".repeat(8), List.of("one", "two"));
+      assertThat(name.length()).isLessThanOrEqualTo(63);
+      assertThat(name).matches("[a-z_][a-z0-9_]*");
+      assertThat(MigrationSnippets.indexName("orders", List.of("id"))).isEqualTo("idx_orders_id");
+      QRecord index = new QRecord().withValue("schemaName", "sales").withValue("indexName", "idx_orders_id")
+         .withValue("definition", "CREATE INDEX idx_orders_id ON sales.orders USING btree (id)");
+      assertThat(MigrationSnippets.drop(B1Fixture.NOW, index, "UNUSED_INDEX", "0 scans"))
+         .contains("DROP INDEX CONCURRENTLY IF EXISTS sales.idx_orders_id;", "-- rollback CREATE INDEX CONCURRENTLY idx_orders_id ON sales.orders USING btree (id);");
+      assertThat(MigrationSnippets.drop(B1Fixture.NOW, index.withValue("definition", null), "UNUSED_INDEX", "0 scans")).isNull();
+      assertThat(MigrationSnippets.vacuum(B1Fixture.NOW, table, "30% dead"))
+         .contains("SET (autovacuum_vacuum_scale_factor = 0.05)", "-- rollback ALTER TABLE public.\"order\" RESET");
+      assertThat(MigrationSnippets.quote("a\"b")).isEqualTo("\"a\"\"b\"");
+   }
+}

src/test/java/com/kingsrook/qbits/dbperformance/SnapshotEvaluatorTest.java

@@ -0,0 +1,210 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2026.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dbperformance;
+
+
+import java.time.Instant;
+import java.util.ArrayList;
+import java.util.List;
+import com.kingsrook.qbits.dbperformance.alerts.SnapshotEvaluator;
+import com.kingsrook.qbits.dbperformance.collect.DbPerfStore;
+import com.kingsrook.qbits.dbperformance.collect.SnapshotCollector;
+import com.kingsrook.qqq.backend.core.alerts.AlertResolution;
+import com.kingsrook.qqq.backend.core.alerts.AlertSignal;
+import com.kingsrook.qqq.backend.core.alerts.AlertSinkInterface;
+import com.kingsrook.qqq.backend.core.alerts.AlertsInstanceMetaData;
+import com.kingsrook.qqq.backend.core.alerts.QAlertTypeMetaData;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+import com.kingsrook.qqq.backend.core.model.metadata.code.QCodeReference;
+import org.junit.jupiter.api.Test;
+import static org.assertj.core.api.Assertions.assertThat;
+
+
+/*******************************************************************************
+ ** Captured core signals verify stable keys, baseline math, recovery and gating.
+ *******************************************************************************/
+class SnapshotEvaluatorTest
+{
+   /*******************************************************************************
+    ** Core constructs this sink, with captured signals reset between fixtures.
+    *******************************************************************************/
+   public static class CapturingSink implements AlertSinkInterface
+   {
+      static final List<AlertSignal> RAISED = new ArrayList<>();
+      static final List<AlertResolution> RESOLVED = new ArrayList<>();
+
+
+      /*******************************************************************************
+       ** Capture raises.
+       *******************************************************************************/
+      @Override
+      public void raise(AlertSignal signal)
+      {
+         RAISED.add(signal);
+      }
+
+
+      /*******************************************************************************
+       ** Capture resolutions.
+       *******************************************************************************/
+      @Override
+      public void resolve(AlertResolution resolution)
+      {
+         RESOLVED.add(resolution);
+      }
+
+
+      /*******************************************************************************
+       ** These producers do not send heartbeat signals.
+       *******************************************************************************/
+      @Override
+      public void heartbeat(String key, Instant at)
+      {
+         throw (new AssertionError("Unexpected heartbeat"));
+      }
+   }
+
+
+   /*******************************************************************************
+    ** Connection, blocking and collection failure recover on observed good data.
+    *******************************************************************************/
+   @Test
+   void raisesResolvesAndGates() throws Exception
+   {
+      B1Fixture f = fixture();
+      B1Fixture.insert("activity_sample", new QRecord().withValue("snapshotId", f.snapshot.getValueInteger("id")).withValue("capturedAt", B1Fixture.NOW)
+         .withValue("reason", "BLOCKED").withValue("querySeconds", 600));
+      SnapshotEvaluator evaluator = new SnapshotEvaluator(f.runtime);
+      evaluator.evaluate(f.snapshot);
+      assertThat(CapturingSink.RAISED).extracting(AlertSignal::getAlertTypeName).contains(SnapshotEvaluator.CONNECTION_SATURATION, SnapshotEvaluator.BLOCKED_CHAIN);
+      assertThat(CapturingSink.RAISED).allSatisfy(signal -> assertThat(signal.getDedupKey()).startsWith(signal.getAlertTypeName().replace("dbperf.", "db_perf.").replace('-', '_') + ":" + f.backend));
+      QRecord failed = B1Fixture.insert("snapshot", new QRecord().withValue("backendName", f.backend).withValue("capturedAt", B1Fixture.NOW.plusSeconds(300))
+         .withValue("status", "FAILED").withValue("errorMessage", "Collection failed (SQLException). Check connectivity."));
+      evaluator.evaluate(failed);
+      assertThat(CapturingSink.RAISED).anySatisfy(signal ->
+      {
+         assertThat(signal.getAlertTypeName()).isEqualTo(SnapshotEvaluator.COLLECTION_FAILED);
+         assertThat(signal.getSummary()).contains("SQLException");
+      });
+      QRecord healthy = B1Fixture.insert("snapshot", new QRecord().withValue("backendName", f.backend).withValue("capturedAt", B1Fixture.NOW.plusSeconds(600))
+         .withValue("status", "OK").withValue("connectionsTotal", 10).withValue("maxConnections", 100));
+      evaluator.evaluate(healthy);
+      assertThat(CapturingSink.RESOLVED).extracting(AlertResolution::getAlertTypeName).contains(SnapshotEvaluator.COLLECTION_FAILED, SnapshotEvaluator.CONNECTION_SATURATION, SnapshotEvaluator.BLOCKED_CHAIN);
+      CapturingSink.RAISED.clear();
+      CapturingSink.RESOLVED.clear();
+      DbPerfStore.update("db_perf_settings", List.of(new QRecord().withValue("id", f.settings.getValueInteger("id")).withValue("alertsEnabled", false)));
+      evaluator.evaluate(failed);
+      evaluator.evaluate(f.snapshot);
+      assertThat(CapturingSink.RAISED).isEmpty();
+      assertThat(CapturingSink.RESOLVED).isEmpty();
+      for(String type : List.of(SnapshotEvaluator.COLLECTION_FAILED, SnapshotEvaluator.QUERY_SPIKE, SnapshotEvaluator.CONNECTION_SATURATION, SnapshotEvaluator.BLOCKED_CHAIN))
+      {
+         assertThat(QAlertTypeMetaData.of(f.runtime.getInstance(), type).getDescription()).isNotBlank();
+      }
+   }
+
+
+   /*******************************************************************************
+    ** Twelve prior samples, median rather than mean, recovery and absence expiry.
+    *******************************************************************************/
+   @Test
+   void spikeBaselineAndRecovery() throws Exception
+   {
+      B1Fixture f = fixture();
+      SnapshotEvaluator evaluator = new SnapshotEvaluator(f.runtime);
+      for(int i = 1; i <= 12; i++)
+      {
+         Instant at = B1Fixture.NOW.minusSeconds(i * 300L);
+         QRecord snapshot = B1Fixture.insert("snapshot", new QRecord().withValue("backendName", f.backend).withValue("capturedAt", at).withValue("status", "OK"));
+         B1Fixture.insert("query_snapshot", new QRecord().withValue("snapshotId", snapshot.getValueInteger("id")).withValue("statementId", f.statement.getValueInteger("id"))
+            .withValue("capturedAt", at).withValue("totalMs", i == 12 ? 1000000 : 1000));
+         if(i == 11)
+         {
+            evaluator.evaluate(f.snapshot);
+            assertThat(CapturingSink.RAISED).noneSatisfy(signal -> assertThat(signal.getAlertTypeName()).isEqualTo(SnapshotEvaluator.QUERY_SPIKE));
+         }
+      }
+      DbPerfStore.update("db_perf_statement", List.of(new QRecord().withValue("id", f.statement.getValueInteger("id"))
+         .withValue("queryText", "select * from audit_orders where name = 'private patient' and id = 123 " + " and status = ?".repeat(50))));
+      evaluator.evaluate(f.snapshot);
+      String key = "db_perf.query_spike:" + f.backend + ":" + f.statement.getValueInteger("id");
+      AlertSignal spike = CapturingSink.RAISED.stream().filter(signal -> signal.getDedupKey().equals(key)).findFirst().orElseThrow();
+      assertThat(spike.getSummary()).contains("prior median 1000.0 ms", "Statement " + f.statement.getValueInteger("id")).doesNotContain("private patient", "123");
+      assertThat(spike.getSummary().length()).isLessThan(300);
+      CapturingSink.RESOLVED.clear();
+      QRecord recovered = B1Fixture.insert("snapshot", new QRecord().withValue("backendName", f.backend).withValue("capturedAt", B1Fixture.NOW.plusSeconds(300)).withValue("status", "OK"));
+      B1Fixture.insert("query_snapshot", new QRecord().withValue("snapshotId", recovered.getValueInteger("id")).withValue("statementId", f.statement.getValueInteger("id"))
+         .withValue("capturedAt", B1Fixture.NOW.plusSeconds(300)).withValue("totalMs", 100));
+      evaluator.evaluate(recovered);
+      assertThat(CapturingSink.RESOLVED).extracting(AlertResolution::getDedupKey).contains(key);
+      CapturingSink.RESOLVED.clear();
+      QRecord absent = B1Fixture.insert("snapshot", new QRecord().withValue("backendName", f.backend).withValue("capturedAt", B1Fixture.NOW.plusSeconds(3899)).withValue("status", "OK"));
+      evaluator.evaluate(absent);
+      assertThat(CapturingSink.RESOLVED).extracting(AlertResolution::getDedupKey).doesNotContain(key);
+      absent.withValue("capturedAt", B1Fixture.NOW.plusSeconds(3900));
+      new SnapshotEvaluator(f.runtime).evaluate(absent);
+      assertThat(CapturingSink.RESOLVED).extracting(AlertResolution::getDedupKey).contains(key);
+   }
+
+
+   /*******************************************************************************
+    ** Partial statistics still report connection pressure, but cannot clear a
+    ** collection failure. A low query time can recover without a full baseline.
+    *******************************************************************************/
+   @Test
+   void partialSnapshotsAndRecoveryWithoutBaseline() throws Exception
+   {
+      B1Fixture f = fixture();
+      SnapshotEvaluator evaluator = new SnapshotEvaluator(f.runtime);
+      evaluator.evaluate(f.snapshot.withValue("status", "PARTIAL"));
+      assertThat(CapturingSink.RAISED).extracting(AlertSignal::getAlertTypeName).contains(SnapshotEvaluator.CONNECTION_SATURATION);
+      assertThat(CapturingSink.RESOLVED).extracting(AlertResolution::getAlertTypeName).doesNotContain(SnapshotEvaluator.COLLECTION_FAILED);
+      QRecord query = DbPerfStore.first("db_perf_query_snapshot", DbPerfStore.eq("snapshotId", f.snapshot.getValueInteger("id")));
+      DbPerfStore.update("db_perf_query_snapshot", List.of(new QRecord().withValue("id", query.getValueInteger("id")).withValue("totalMs", 1)));
+      evaluator.evaluate(f.snapshot.withValue("status", "OK"));
+      assertThat(CapturingSink.RESOLVED).extracting(AlertResolution::getDedupKey).contains("db_perf.query_spike:" + f.backend + ":" + f.statement.getValueInteger("id"));
+   }
+
+
+   /*******************************************************************************
+    ** Collector invokes evaluation after committing; no sink installation needed.
+    *******************************************************************************/
+   @Test
+   void collectorIntegration() throws Exception
+   {
+      B1Fixture f = fixture();
+      new SnapshotCollector(f.runtime, B1Fixture.CLOCK, null).collect(f.backend, true);
+      assertThat(CapturingSink.RAISED).extracting(AlertSignal::getAlertTypeName).contains(SnapshotEvaluator.COLLECTION_FAILED);
+   }
+
+
+   /*******************************************************************************
+    ** Register a clean capturing sink on the migrated instance.
+    *******************************************************************************/
+   private static B1Fixture fixture() throws Exception
+   {
+      B1Fixture f = new B1Fixture();
+      CapturingSink.RAISED.clear();
+      CapturingSink.RESOLVED.clear();
+      AlertsInstanceMetaData.ofOrWithNew(f.runtime.getInstance()).withSink(new QCodeReference(CapturingSink.class));
+      return (f);
+   }
+}

src/test/java/com/kingsrook/qbits/dbperformance/StoredRuleTest.java

@@ -0,0 +1,221 @@
+/*
+ * QQQ - Low-code Application Framework for Engineers.
+ * Copyright (C) 2021-2026.  Kingsrook, LLC
+ * 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
+ * contact@kingsrook.com
+ * https://github.com/Kingsrook/
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package com.kingsrook.qbits.dbperformance;
+
+
+import java.math.BigDecimal;
+import java.util.Arrays;
+import java.util.List;
+import com.kingsrook.qbits.dbperformance.analyze.AnalyzeService;
+import com.kingsrook.qbits.dbperformance.analyze.DbPerfRule;
+import com.kingsrook.qbits.dbperformance.analyze.FindingDraft;
+import com.kingsrook.qbits.dbperformance.analyze.FindingService;
+import com.kingsrook.qbits.dbperformance.analyze.RuleContext;
+import com.kingsrook.qbits.dbperformance.analyze.StoredRule;
+import com.kingsrook.qbits.dbperformance.collect.DbPerfStore;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QQueryFilter;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.EnumSource;
+import static org.assertj.core.api.Assertions.assertThat;
+import static org.assertj.core.api.Assertions.assertThatThrownBy;
+
+
+/*******************************************************************************
+ ** Every seeded rule is exercised against migrated H2 rows, including lifecycle
+ ** reconciliation and live edits of rule configuration.
+ *******************************************************************************/
+class StoredRuleTest
+{
+   /*******************************************************************************
+    ** Created, deduped, acknowledged, ignored, resolved, reopened and disabled.
+    *******************************************************************************/
+   @ParameterizedTest
+   @EnumSource(StoredRule.class)
+   void ruleLifecycle(StoredRule rule) throws Exception
+   {
+      B1Fixture f = new B1Fixture();
+      List<FindingDraft> drafts = rule.evaluate(f.context(rule));
+      assertThat(drafts).as(rule.code()).isNotEmpty().allSatisfy(d ->
+      {
+         assertThat(d.severity()).isEqualTo("WARNING");
+         assertThat(d.evidence()).contains("| Measurement | Value |");
+         assertThat(d.score()).isBetween(BigDecimal.valueOf(0.2), BigDecimal.valueOf(2.2));
+      });
+      FindingService service = new FindingService(f.runtime, B1Fixture.CLOCK);
+      assertThat(service.apply(f.backend, rule.code(), drafts).created()).isEqualTo(drafts.size());
+      assertThat(service.apply(f.backend, rule.code(), drafts).updated()).isEqualTo(drafts.size());
+      List<QRecord> findings = RuleContext.read("db_perf_finding", new QQueryFilter());
+      assertThat(findings).hasSize(drafts.size()).allSatisfy(r -> assertThat(r.getValueInteger("occurrences")).isEqualTo(2));
+      QRecord first = findings.getFirst();
+      DbPerfStore.update("db_perf_finding", List.of(new QRecord().withValue("id", first.getValueInteger("id")).withValue("status", "ACKNOWLEDGED")));
+      service.apply(f.backend, rule.code(), drafts);
+      assertThat(finding(first).getValueString("status")).isEqualTo("ACKNOWLEDGED");
+      assertThat(service.apply(f.backend, rule.code(), List.of()).resolved()).isEqualTo(drafts.size());
+      assertThat(finding(first).getValueString("statusNote")).isEqualTo("No longer detected");
+      service.apply(f.backend, rule.code(), drafts);
+      assertThat(finding(first).getValueString("status")).isEqualTo("OPEN");
+      assertThat(finding(first).getValue("resolvedAt")).isNull();
+      DbPerfStore.update("db_perf_finding", List.of(new QRecord().withValue("id", first.getValueInteger("id")).withValue("status", "IGNORED").withValue("statusNote", "Accepted")));
+      service.apply(f.backend, rule.code(), drafts);
+      service.apply(f.backend, rule.code(), List.of());
+      assertThat(finding(first).getValueString("status")).isEqualTo("IGNORED");
+      assertThat(finding(first).getValueString("statusNote")).isEqualTo("Accepted");
+      QRecord row = f.context(rule).rule;
+      DbPerfStore.update("db_perf_rule", List.of(new QRecord().withValue("id", row.getValueInteger("id")).withValue("enabled", false)));
+      int occurrences = finding(first).getValueInteger("occurrences");
+      assertThat(rule.evaluate(f.context(rule))).isEmpty();
+      assertThat(service.apply(f.backend, rule.code(), drafts)).isEqualTo(new FindingService.Counts(0, 0, 0));
+      assertThat(finding(first).getValueInteger("occurrences")).isEqualTo(occurrences);
+   }
+
+
+   /*******************************************************************************
+    ** Changing thresholds in the persisted rule row changes the result.
+    *******************************************************************************/
+   @ParameterizedTest
+   @EnumSource(value = StoredRule.class, names = "DUPLICATE_INDEX", mode = EnumSource.Mode.EXCLUDE)
+   void thresholdsAreData(StoredRule rule) throws Exception
+   {
+      B1Fixture f = new B1Fixture();
+      QRecord row = f.context(rule).rule;
+      assertThat(rule.evaluate(f.context(rule))).isNotEmpty();
+      DbPerfStore.update("db_perf_rule", List.of(new QRecord().withValue("id", row.getValueInteger("id"))
+         .withValue("threshold1", rule == StoredRule.CACHE_HIT_LOW ? 0 : 999999999).withValue("threshold2", 999999999)));
+      assertThat(rule.evaluate(f.context(rule))).isEmpty();
+   }
+
+
+   /*******************************************************************************
+    ** One failing rule cannot resolve its findings or abort subsequent rules.
+    *******************************************************************************/
+   @Test
+   void failureIsolationAndSeverity() throws Exception
+   {
+      B1Fixture f = new B1Fixture();
+      DbPerfRule broken = new DbPerfRule()
+      {
+         @Override
+         public String code()
+         {
+            return ("SLOW_QUERY");
+         }
+
+         @Override
+         public List<FindingDraft> evaluate(RuleContext context)
+         {
+            throw (new IllegalStateException("private SQL must not escape"));
+         }
+      };
+      new FindingService(f.runtime).apply(f.backend, "SLOW_QUERY", StoredRule.SLOW_QUERY.evaluate(f.context(StoredRule.SLOW_QUERY)));
+      var result = new AnalyzeService(f.runtime, B1Fixture.CLOCK, List.of(broken, StoredRule.BLOAT)).run(f.backend);
+      assertThat(result.getFirst().error()).contains("IllegalStateException").doesNotContain("private SQL");
+      assertThat(result.getLast().counts().created()).isEqualTo(1);
+      assertThat(DbPerfStore.first("db_perf_finding", DbPerfStore.eq("ruleCode", "SLOW_QUERY")).getValueString("status")).isEqualTo("OPEN");
+      QRecord row = f.context(StoredRule.SLOW_QUERY).rule;
+      DbPerfStore.update("db_perf_rule", List.of(new QRecord().withValue("id", row.getValueInteger("id")).withValue("severity", "CRITICAL")));
+      FindingDraft draft = StoredRule.SLOW_QUERY.evaluate(f.context(StoredRule.SLOW_QUERY)).getFirst();
+      assertThat(draft.score()).isEqualByComparingTo("1.4");
+      assertThat(draft.recommendation()).contains("Query plan", "?sql=EXPLAIN%20");
+      assertThat(new AnalyzeService(f.runtime, B1Fixture.CLOCK, Arrays.asList(StoredRule.values())).run(f.backend)).allSatisfy(r -> assertThat(r.error()).isNull());
+      assertThatThrownBy(() -> new AnalyzeService(f.runtime).run("other")).isInstanceOf(IllegalArgumentException.class);
+   }
+
+
+   /*******************************************************************************
+    ** Invalid scopes and duplicate identities roll back the entire reconciliation.
+    *******************************************************************************/
+   @Test
+   void atomicValidation() throws Exception
+   {
+      B1Fixture f = new B1Fixture();
+      FindingService service = new FindingService(f.runtime);
+      FindingDraft draft = StoredRule.SLOW_QUERY.evaluate(f.context(StoredRule.SLOW_QUERY)).getFirst();
+      assertThatThrownBy(() -> service.apply(f.backend, "SLOW_QUERY", List.of(draft, draft))).isInstanceOf(IllegalArgumentException.class);
+      assertThat(RuleContext.read("db_perf_finding", new QQueryFilter())).isEmpty();
+      assertThatThrownBy(() -> service.apply(f.backend, "BLOAT", List.of(draft))).isInstanceOf(IllegalArgumentException.class);
+      assertThatThrownBy(() -> service.apply("other", "SLOW_QUERY", List.of(draft))).isInstanceOf(IllegalArgumentException.class);
+      assertThat(service.apply(f.backend, "NO_SUCH_RULE", List.of())).isEqualTo(new FindingService.Counts(0, 0, 0));
+   }
+
+
+   /*******************************************************************************
+    ** Unrelated backends, old samples and future samples cannot influence rules.
+    *******************************************************************************/
+   @Test
+   void evidenceWindowsAndIndexSafety() throws Exception
+   {
+      B1Fixture f = new B1Fixture();
+      FindingDraft missing = StoredRule.MISSING_INDEX.evaluate(f.context(StoredRule.MISSING_INDEX)).getFirst();
+      assertThat(missing.migrationSnippet()).contains("(status)").doesNotContain("(customer_id");
+      assertThat(StoredRule.POOL_SIZING.evaluate(f.context(StoredRule.POOL_SIZING)).getFirst().recommendation()).contains("orders-app: peak 90");
+      assertThat(StoredRule.OVER_AUDITED.evaluate(f.context(StoredRule.OVER_AUDITED)).getFirst().recommendation()).contains("shipOrders");
+      QRecord unique = f.index("idx_unique", "status", true, 0);
+      QRecord partial = f.index("idx_partial", "customer_id", false, 1);
+      DbPerfStore.update("db_perf_index_stat", List.of(new QRecord().withValue("id", partial.getValueInteger("id"))
+         .withValue("definition", "CREATE INDEX idx_partial ON public.audit_orders USING btree (customer_id) WHERE status = 1")));
+      assertThat(StoredRule.DUPLICATE_INDEX.evaluate(f.context(StoredRule.DUPLICATE_INDEX)))
+         .noneSatisfy(d -> assertThat(d.indexStatId()).isEqualTo(unique.getValueInteger("id")))
+         .noneSatisfy(d -> assertThat(d.indexStatId()).isEqualTo(partial.getValueInteger("id")));
+      assertThat(StoredRule.MISSING_INDEX.evaluate(f.context(StoredRule.MISSING_INDEX))).isEmpty();
+      QRecord other = B1Fixture.insert("snapshot", new QRecord().withValue("backendName", "another").withValue("capturedAt", B1Fixture.NOW).withValue("status", "OK"));
+      B1Fixture.insert("activity_sample", new QRecord().withValue("snapshotId", other.getValueInteger("id")).withValue("capturedAt", B1Fixture.NOW).withValue("reason", "IDLE_IN_TRANSACTION").withValue("xactSeconds", 99999).withValue("applicationName", "foreign"));
+      assertThat(StoredRule.IDLE_IN_TRANSACTION.evaluate(f.context(StoredRule.IDLE_IN_TRANSACTION))).hasSize(1);
+      f.settings.withValue("lastTableSnapshotAt", B1Fixture.NOW.plusSeconds(60));
+      assertThat(StoredRule.BLOAT.evaluate(f.context(StoredRule.BLOAT))).isEmpty();
+      assertThat(StoredRule.UNUSED_INDEX.evaluate(f.context(StoredRule.UNUSED_INDEX))).isEmpty();
+   }
+
+
+   /*******************************************************************************
+    ** A later failed collection must not hide a recent statistics reset.
+    *******************************************************************************/
+   @Test
+   void resetAgeAndLongUnitIdentity() throws Exception
+   {
+      B1Fixture f = new B1Fixture();
+      B1Fixture.insert("snapshot", new QRecord().withValue("backendName", f.backend).withValue("capturedAt", B1Fixture.NOW.minusSeconds(40 * 86400L)).withValue("status", "OK"));
+      DbPerfStore.update("db_perf_snapshot", List.of(new QRecord().withValue("id", f.snapshot.getValueInteger("id")).withValue("rawStatsReset", B1Fixture.NOW.minusSeconds(86400))));
+      B1Fixture.insert("snapshot", new QRecord().withValue("backendName", f.backend).withValue("capturedAt", B1Fixture.NOW.plusSeconds(1)).withValue("status", "FAILED"));
+      assertThat(StoredRule.UNUSED_INDEX.evaluate(f.context(StoredRule.UNUSED_INDEX))).isEmpty();
+      String unit = "job".repeat(100);
+      B1Fixture.insert("attribution", new QRecord().withValue("windowStart", B1Fixture.NOW.minusSeconds(60)).withValue("windowEnd", B1Fixture.NOW)
+         .withValue("unitKind", "SCHEDULED_JOB").withValue("unitName", unit).withValue("totalMs", 1000000));
+      List<FindingDraft> drafts = StoredRule.HOT_JOB.evaluate(f.context(StoredRule.HOT_JOB));
+      assertThat(drafts).allSatisfy(d ->
+      {
+         assertThat(d.dedupeKey().length()).isLessThanOrEqualTo(300);
+         assertThat(d.title().length()).isLessThanOrEqualTo(300);
+      });
+      assertThat(new FindingService(f.runtime).apply(f.backend, "HOT_JOB", drafts).created()).isPositive();
+      assertThat(StoredRule.POOL_SIZING.evaluate(f.context(StoredRule.POOL_SIZING)).getFirst().recommendation()).contains("orders-app: combined pool cap 36");
+   }
+
+
+   /*******************************************************************************
+    ** Reload lifecycle state after each transaction.
+    *******************************************************************************/
+   private static QRecord finding(QRecord row) throws Exception
+   {
+      return (DbPerfStore.first("db_perf_finding", DbPerfStore.eq("id", row.getValueInteger("id"))));
+   }
+}