Proposal: database offline / unhealthy-state alert (baseline-deviation)

[jakemorgangit]

Hi Erik — I've built a database-state alert in my fork and, per CONTRIBUTING, wanted to check whether you'd welcome it upstream (and how you'd want it shaped) before opening a PR.

The gap

There's currently no alert when a monitored database leaves a healthy state. database_config captures state_desc, but it's a load-time snapshot (frequency 0), so a database going OFFLINE / SUSPECT / RECOVERY_PENDING / RESTORING after a server is added is never surfaced.

Proposed approach: baseline-deviation (not "anything ≠ ONLINE")

A naive "alert on any non-ONLINE state" is too noisy — log-shipping secondaries sit permanently in RESTORING, and some databases are intentionally OFFLINE. Instead:

• A lightweight per-minute database_states collector captures sys.databases.state_desc as a time series (a few rows against in-memory catalog metadata).
• Each database's expected state is captured automatically on first observation (a baseline), stored per (server, database).
• The alert fires when current state ≠ expected. Severity CRITICAL for SUSPECT / RECOVERY_PENDING / EMERGENCY, WARNING otherwise. Per-database cooldown + resolution, routed through the existing mute rules and delivery channels.
• Operators can override the expected state per database (or set (ignore)) to kill false positives. A log-shipping secondary baselines at RESTORING and stays quiet with zero config; an intentionally-offline database stays quiet too.

Validated against a live SQL Server: baseline capture on first observation, deviation firing with the correct severity, the (ignore) override suppressing a database, and recovery/resolution all worked as intended.

Scope (as built in my fork)

• Shared: new database_states collector (catalog + schedule defaults), an AlertEngine check, a read-adapter method + settings flag, and the per-metric severity mapping.
• Stores: Lite DuckDB (auto-created tables) and Darling Postgres — one additive migration: the collector table, a per-(server, database) expected-state config table, and a master-toggle column on the alert-settings row.
• UI: a settings enable toggle plus a per-database override editor window in both Lite and the Darling Viewer; the web viewer shows fired alerts automatically; the MCP get_alert_settings / update_alert_settings tools expose the toggle.

Questions

1. Would you want this upstream?
2. If so, anything about the approach you'd want done differently before I open a PR?

Happy to adjust to fit the project's direction — thanks for taking a look.

[erikdarlingdata]

Yes, I'd take this upstream. It's a real gap: database_config is a load-time snapshot on purpose, so a state change after onboarding is invisible today. Your baseline-deviation shape is the right call. A plain not-ONLINE alert would page every log shipping secondary on earth.

One design hole to close before the PR: first-observation baselining poisons itself if the first observation is already bad. Add a server while a database sits SUSPECT and the engine learns SUSPECT as expected, stays quiet through the outage, then fires when the database recovers. Auto-accept a baseline only for non-critical states (ONLINE, RESTORING, OFFLINE). Treat a critical first observation (SUSPECT, RECOVERY_PENDING, EMERGENCY) as an immediate alert, with the baseline held pending until an operator confirms it or the state goes healthy.

Some house logistics your fork can't know about, so the PR doesn't fight CI:

• Claim the Darling migration number at PR time, not now. Two collectors landed this week and the numbers moved. The applier requires ascent, so a stale number silently skips on upgraded stores. Same goes for the Lite DuckDB schema version.
• New collector tax: an entry in CollectorScheduleDefaults, membership in the pinned collector set and all six schedule preset tables (two pin tests enforce this), and a +1 bump to the CI worker sizing in build.yml and nightly.yml. It derives from hypertable count, and a test enforces that too.
• Your expected-state config table needs grants in Darling/tools/provision-roles.sql. The viewer role reads it, and your override editor decides who writes it.
• Azure SQL DB: follow how the existing per-database collectors gate there.
• Base the PR on dev, add a CHANGELOG entry with a bottom link ref, and put the tests in the same PR.

On your question 2: the approach is what I'd have built. Close the first-observation hole, pick up the list above, and open the PR.

[erikdarlingdata]

Shipped by @jakemorgangit in #2002 (merged to dev). Validated end-to-end on a live rig pre-merge: V49 migration on a real v48 store, per-minute state collection, auto-baseline seeding, the two-sample debounce firing a Warning ~105s after ALTER DATABASE SET OFFLINE with the Expected→Current detail, and the per-database recovery notice on SET ONLINE. Ships in the next nightly; production dogfood on the monitor box follows the usual loop.