Define alert types, included metrics, and notification frequency for the alert system

## Context

Issue #34 covers building the external notification system. Before implementation begins, the scope of the alert system needs to be clearly defined — what types of alerts exist, which metrics trigger them, and how often they fire. Without this, we risk building a system that either spams or under-alerts.

## What needs to be decided

### 1. Alert types

| Alert type | Trigger | BigQuery | Status |
|---|---|---|---|
| Archival recommended — predictive | Projected growth trend crosses `order_itemmeta` threshold within ~4 weeks (based on `db_size_history`/QET history growth rate) | Yes | To be defined |
| Archival recommended — reactive | `order_itemmeta_size >= threshold` AND `orders_older_than_threshold > 0` AND current QET exceeds baseline by a configurable percentage | Yes | Defined in #34 (threshold-only version); QET condition to be added |
| Abnormal DB size growth | Week-over-week DB size growth exceeds N× the rolling average weekly growth | Yes — `total_db_size_mb` collected in #11 | Defined in #34 |
| Abnormal QET growth | Current week's average QET exceeds last week's average QET by a configurable percentage | Yes — `query_execution_ms` collected pre-#11 | To be defined |
| High hook count | Total registered hook callbacks exceed a configurable threshold (suggested: 8,000) | Yes — collected in #11 | Defined in #34 |
| High active plugin count | Active plugin count exceeds a configurable threshold (suggested: 40) | Yes — collected in #11 | Defined in #34 |
| High inactive plugin count | Inactive plugin count exceeds a configurable threshold | Yes — collected in #11 | To be defined |
| Unexpected DB table growth | A non-WooCommerce table exceeds a configurable size threshold — flags data bloat from logging plugins or custom code, not orders | Yes — collected in #11 | To be defined |
| Plugin vulnerability detected | One or more active plugins have a known CVE (via WP.org vulnerability API) | No — alert only | Split out to #41 (wishlist, not yet approved) |

**Why the new alert types matter:**

These alerts exist because DB size alone doesn't tell you *why* a site is slow. Without them, every slow site gets the same response: check the orders, recommend archival. With them, the diagnosis becomes systematic:

- **Archival recommended (predictive)** — the DB hasn't crossed the threshold yet, but the current growth trend says it will in about a month. This gives the team a heads-up to start planning archival before it becomes urgent, instead of reacting the day the threshold is crossed. Root-cause detection (which factor is driving the growth — plugin count, order volume, etc.) is deliberately out of scope here; that's tracked separately as a future smart-alerts capability.
- **Archival recommended (reactive)** — the DB has crossed the threshold *and* performance has actually degraded (QET above baseline). This is the "act now" tier, distinct from the predictive one which is just a heads-up.
- **Abnormal DB size growth** — DB crossed no threshold yet, but it grew 10× faster than usual this week. That's a signal worth investigating *now*, not in three months when performance drops. When slowness eventually hits, the team already has a dated record of when the spike happened — pointing straight to a sales event, a plugin dump, or runaway logging — without manual forensics.
- **Abnormal QET growth** — query execution time itself spiked week-over-week, independent of whether DB size grew. Catches performance regressions caused by something other than data volume (e.g. a plugin update that added an expensive query, an index that got dropped).
- **High hook count** — DB is healthy but site is slow → the problem is code overhead, not data. Archiving orders won't help. The engineer audits the plugin stack instead.
- **High active plugin count** — 60+ active plugins means fundamental overhead that no amount of DB optimisation will fully fix. This is a conversation you can have with the client on day one.
- **High inactive plugin count** — inactive plugins still take up DB rows (options, postmeta, etc.) and represent dead weight the client may not know exists. A high inactive count is a cheap win: uninstalling cleans up the DB with zero functional risk.
- **Unexpected DB table growth** — DB is large but WooCommerce tables are fine. A logging plugin or badly written custom feature is filling the database. Without table-level sizes, you'd archive all the orders, the site stays slow, and the client is confused.

Open questions:
- Should autoloaded options size crossing a threshold also trigger an alert?
- Should a "DB health: OK" recovery notification be sent when the archival signal clears after being active?
- What are the right default thresholds for hook count and plugin count (active + inactive)? (Suggested: 8,000 and 40 for active — to be confirmed with real site data from MFM)
- What multiplier defines "abnormal" growth for DB size and QET? (Suggested: 3× rolling average — to be confirmed)
- How many weeks of history should the rolling average use, and how far out should the predictive archival projection look? (Suggested: 4 weeks history, ~4 week lookahead)
- What percentage QET degradation vs baseline should count as "reactive" archival territory?

**Note on plugin vulnerability alerts:**
Split out to #41 as a wishlist item — not yet approved by Kuldeep, not scoped for this milestone.

**Note on root-cause diagnosis:**
Automatically identifying *which* factor (plugin count, order volume, etc.) is driving DB growth is tracked as a separate future issue, not part of this alert system's initial scope.

### 2. Metrics included in each alert

For each alert type, define exactly what data is included in the notification payload:

**Archival recommended — predictive (proposed):**
- Site URL
- Current order itemmeta size (MB)
- Projected date threshold will be crossed at current growth rate
- Configured threshold (MB)

**Archival recommended — reactive (proposed):**
- Site URL
- Current order itemmeta size (MB)
- Configured threshold (MB)
- Number of orders older than retention window
- Last archival date
- Current QET vs baseline QET (ms and % over)

**Abnormal DB size growth (proposed):**
- Site URL
- Current total DB size (MB)
- DB size one week ago (MB)
- Growth this week (MB and %)
- Rolling average weekly growth over last 4 weeks (MB)
- Deviation factor (this week's growth ÷ rolling average)

**Abnormal QET growth (proposed):**
- Site URL
- This week's average QET (ms)
- Last week's average QET (ms)
- Growth (ms and %)

**High hook count (proposed):**
- Site URL
- Current hook callback count
- Configured threshold
- Active plugin count (for context — high hook count often correlates with high plugin count)

**High active plugin count (proposed):**
- Site URL
- Active plugin count
- Inactive plugin count
- Configured threshold

**High inactive plugin count (proposed):**
- Site URL
- Inactive plugin count
- Active plugin count (for context)
- Configured threshold

**Unexpected DB table growth (proposed):**
- Site URL
- Table name
- Current size (MB)
- Configured threshold (MB)
- Total DB size (MB) for context

**What else should be included? What should be left out to keep it readable?**

### 3. Alert frequency

How often should the same alert fire if the condition remains active?

Options to decide between:
- **Once on transition** — fires only when signal goes from inactive → active. No repeat until it clears and re-triggers. (Least noisy)
- **Daily digest** — fires once per day while the condition remains active
- **Weekly digest** — fires once per week while the condition remains active
- **Every push** — fires on every BigQuery push while active (most noisy, not recommended)

Should frequency be configurable per alert type, or a single global setting?

### 4. Silence / snooze

Should there be a way to snooze an alert for X days so it doesn't fire again until a set date? Useful for cases where archival is scheduled but hasn't happened yet.

## Dependency

- #34 — external notification delivery (depends on this issue being resolved first)
- #11 — server-side metrics collection (hook count, plugin count, DB table sizes must be collected before these alerts can fire)
- #41 — vulnerable plugin detection, split out as a wishlist item (not a blocking dependency)

## Acceptance criteria

- [ ] All alert types are listed and agreed upon
- [ ] Archival alert's predictive vs reactive tiers confirmed, including projection lookahead and QET degradation threshold
- [ ] Inactive plugin count threshold confirmed
- [ ] Abnormal QET growth threshold and comparison window confirmed
- [ ] Hook count, plugin count, and unexpected table growth confirmed as BigQuery-tracked signals
- [ ] Abnormal DB/QET growth multiplier and rolling window length confirmed (suggested: 3× over 4 weeks)
- [ ] Default thresholds for hook count and plugin count (active + inactive) confirmed with real site data
- [ ] Each alert type has a defined metric payload (what data is included in the notification)
- [ ] Frequency behaviour is decided for each alert type
- [ ] Snooze/silence requirement is confirmed or ruled out
- [ ] Decisions documented here before #34 implementation begins