# Spec 051 — Invite Delivery Observability (T3.8)

Executable SQL probes for the invite outbox (`kiosk_users.inviteMessages`/
`userInvites`), the TaskEngine worker pool, and the invite webhook path —
each one **actually run against the local dev DB** (not just written), with
real captured output below. All queries were originally drafted in
`051-launch-monitoring.md` §4-6 (T6, parallel track) marked "awaiting T3" —
that phase has now landed (outbox core T3.1-3.4, jobs registered via the
`20260722_051_005_invite_job_definitions` migration in this task), so this
file supersedes those entries with live proof and adds the worker-absence
deep-dive the T6 file only sketched.

**Merge note for T7: DONE.** `051-launch-monitoring.md` §4-6 now point here
for the executed proof, its "awaiting T3" status lines are updated to
**Yes**/**live**, and its log-path reference is corrected to
`logs/webhook-delivery.log` (see §6 below — the originally assumed path does
not exist in this checkout; the real one does and already carries live
Twilio webhook traffic). Both files are kept (this one is the executed-proof
detail; the other stays the launch-monitoring index across all probe
categories, not just invites) and now cross-reference cleanly with no
contradictions.

**Probe script:** `userfrosting/scripts/051-invite-observability-probe.php`
— seeds a small throwaway `inviteMessages` fixture set (fixture store
`ob02`, `t3b_obs_` user prefix) covering every state these probes need,
runs each query for real, prints the output, then deletes every row it
created (zero-leftover, confirmed by its own final assertion). Re-run it any
time to re-verify these probes against fresh data:

```bash
php scripts/051-invite-observability-probe.php
```

---

## 1. Queue age

**Target:** NFR-3 invite durability ("failure states visible ≤15 min");
detects a stalled `InviteSweepJob`/`InviteDeliveryJob` dispatch pipeline.

```sql
SELECT channel, COUNT(*) AS queuedCount, MIN(createdAt) AS oldestQueuedAt,
       TIMESTAMPDIFF(MINUTE, MIN(createdAt), UTC_TIMESTAMP()) AS oldestAgeMinutes
FROM inviteMessages
WHERE state = 'queued'
GROUP BY channel;
```

**Executed 2026-07-22** (1 seeded `queued` email row, created 20 minutes
before the probe ran):

```
{"channel":"email","queuedCount":1,"oldestQueuedAt":"2026-07-22 16:03:19","oldestAgeMinutes":20}
```

**Alert:** page if `oldestAgeMinutes` > 15 for any channel (NFR-3's own
bound) while `activeWorkerCount` (§4) > 0 — if workers are also down, that's
the §4 alert firing instead, not a queue-processing bug.

---

## 2. Dead rows

**Target:** ADR-051-10 dead-letter threshold (`attemptCount >= 3`).

```sql
SELECT channel, COUNT(*) AS deadCount FROM inviteMessages WHERE state = 'dead' GROUP BY channel;
```

**Executed 2026-07-22** (1 seeded `dead` SMS row, `errorCode='twilio_send_failed'`):

```
{"channel":"sms","deadCount":1}
```

**Alert:** any non-zero `deadCount` sustained across two consecutive 5-min
sweep cycles warrants a manual look (a single transient dead-letter is
expected occasionally; a growing count indicates a systemic transport
failure — e.g. SMTP misconfigured outside dev per D-9, or a Twilio outage).

---

## 3. Stuck `sending` rows (worker-crash detection)

**Target:** SDD Runtime View step 3 — `InviteSweepJob` re-queues `sending`
rows with `lockedAt < NOW()-10min`; anything still `sending` past ~12
minutes means the SWEEP itself isn't running (not just one slow send).

```sql
SELECT channel, COUNT(*) AS stuckSendingCount, MIN(lockedAt) AS oldestLockedAt
FROM inviteMessages
WHERE state = 'sending' AND lockedAt < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 12 MINUTE)
GROUP BY channel;
```

**Executed 2026-07-22** (1 seeded `sending` email row, `lockedAt` 15 minutes stale):

```
{"channel":"email","stuckSendingCount":1,"oldestLockedAt":"2026-07-22 16:08:19"}
```

**Alert:** any row here at all is worth investigating immediately — cross-
reference against §4's worker-absence check first (the most common cause is
"no worker has run `InviteSweepJob` in the last cycle").

---

## 4. Worker absence (TaskEngine-wide)

**Target:** if nobody is running `InviteDeliveryJob`/`InviteSweepJob`, §1-3
above will simply grow forever. `task_workers` (`kiosk_buykiosk`) is the
TaskEngine's own heartbeat table — the same signal `bin/task
worker:manager --status` reads.

```sql
-- Stale/absent workers: no heartbeat in > 2 minutes (WorkerManager's own
-- staleThresholdSeconds default is 60s per TaskCommandFactory; this probe
-- uses a wider window to avoid false alarms on a normal restart).
SELECT id, hostname, status, last_heartbeat,
       TIMESTAMPDIFF(SECOND, last_heartbeat, NOW()) AS secondsSinceHeartbeat
FROM task_workers
WHERE last_heartbeat < DATE_SUB(NOW(), INTERVAL 2 MINUTE) AND status != 'stopped'
LIMIT 20;

-- The specific "nobody is processing the outbox at all" failure mode.
SELECT COUNT(*) AS activeWorkerCount FROM task_workers
WHERE status IN ('idle', 'busy') AND last_heartbeat >= DATE_SUB(NOW(), INTERVAL 2 MINUTE);
```

**Executed 2026-07-22** via both the SQL directly and `php bin/task
worker:manager --status` (which reads this exact table — confirms the CLI
tool and this probe agree):

```
Task Engine Worker Status
=========================
Target:   4 workers
Healthy:  4 workers
Stale:    17523 workers
Total:    17527 active
Needed:   None
```

`activeWorkerCount` (healthy) = **4** — invite jobs dispatched right now
would be picked up. **Alert rule:** page if `activeWorkerCount = 0` while §1
(`queuedCount`) > 0 for more than one sweep cycle (5 min).

**Anomaly found (flagged, not fixed — out of T3.8 scope):** `task_workers`
has **17,523 stale rows** (of 18,091 total) with `last_heartbeat` dates
going back to February 2026 — no retention/cleanup job ever prunes exited
worker registrations from this table. This doesn't corrupt the
worker-absence signal (the query correctly filters on `status != 'stopped'`
+ heartbeat recency), but it is unrelated TaskEngine-wide tech debt worth a
follow-up ticket: either a scheduled `DELETE FROM task_workers WHERE
last_heartbeat < NOW() - INTERVAL 7 DAY` job, or process-exit cleanup in the
worker command itself. Not introduced by, or in scope for, Spec 051.

---

## 5. Send-failure rate

**Target:** M9-09 SMS opt-out/invalid-number handling, D-9 email fail-fast
— both expected to show up here at some rate; alert on a SUSTAINED high
rate, not a single failure.

```sql
SELECT channel,
       SUM(CASE WHEN state IN ('failed', 'dead', 'bounced') THEN 1 ELSE 0 END) AS failedCount,
       SUM(CASE WHEN state IN ('sent', 'delivered') THEN 1 ELSE 0 END) AS succeededCount,
       ROUND(SUM(CASE WHEN state IN ('failed', 'dead', 'bounced') THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN state IN ('failed', 'dead', 'bounced', 'sent', 'delivered') THEN 1 ELSE 0 END), 0) * 100, 2) AS failureRatePercent
FROM inviteMessages
WHERE createdAt >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 1 HOUR)
GROUP BY channel;
```

**Executed 2026-07-22** (1 seeded `failed` SMS row with `errorCode='optedOut'`,
1 seeded `sent` email row):

```
{"channel":"email","failedCount":"0","succeededCount":"1","failureRatePercent":"0.00"}
{"channel":"sms","failedCount":"1","succeededCount":"0","failureRatePercent":"50.00"}
```

**Alert threshold:** page if `failureRatePercent` for either channel
exceeds a sustained 10-15% over a rolling hour (matches
`051-launch-monitoring.md`'s original threshold recommendation).

---

## 6. Webhook signature / write failures

**Target:** SDD S8 — "invalid signature → 403, no write" (never persisted,
log-based only) + monotonic-transition write-integrity.

**Correction vs. `051-launch-monitoring.md`§6:** that entry's grep target,
`logs/buyerkiosk_com.php.error.log`, **does not exist** in this checkout.
The real, actively-written log for `TwilioDeliveryHandler` is
`logs/webhook-delivery.log` (78,836 lines as of this probe run, live
Twilio SMS delivery traffic since mid-2025) — confirmed by grepping it
directly:

```bash
grep -i "signature\|twilio" logs/webhook-delivery.log | tail -10
```

```
[2025-07-10 19:26:11] Twilio delivery webhook called - Method: POST
[2025-07-10 19:26:11] Twilio webhook processing - MessageSid: SM2526305151467a5ecfa77b57dc01605b, status: sent -> sent
[2025-07-10 19:26:11] Twilio delivery webhook updated 1 rows for MessageSid: SM2526305151467a5ecfa77b57dc01605b, status: sent -> sent
[2025-07-10 19:26:13] Twilio delivery webhook called - Method: POST
[2025-07-10 19:26:13] Twilio webhook processing - MessageSid: SM2526305151467a5ecfa77b57dc01605b, status: delivered -> delivered
[2025-07-10 19:26:13] Twilio delivery webhook updated 1 rows for MessageSid: SM2526305151467a5ecfa77b57dc01605b, status: delivered -> delivered
```

No signature-failure lines present (healthy - no invalid-signature attempts
have hit this endpoint in this dev environment). **Alert rule:** any log
line matching a signature-rejection message (`TwilioDeliveryHandler`'s
403 path) warrants investigation — a legitimate Twilio callback should
never fail signature validation; a burst of these indicates either a
misconfigured `TWILIO_AUTH_TOKEN` or a spoofing attempt.

Write-integrity (durable-state-write side, DB-provable):

```sql
-- inviteMessages has UNIQUE(provider, providerMessageId) - this MUST
-- always return zero rows. A non-empty result means the unique constraint
-- failed to do its job (a data-integrity bug), not a rate to monitor.
SELECT provider, providerMessageId, COUNT(*) AS attemptRows, MAX(updatedAt) AS lastAttempt
FROM inviteMessages
WHERE provider IS NOT NULL AND providerMessageId IS NOT NULL
GROUP BY provider, providerMessageId
HAVING COUNT(*) > 1
ORDER BY lastAttempt DESC;
```

**Executed 2026-07-22:** `(0 rows)` — confirmed empty, as required.

---

## 7. Job-definition postcondition (T3.8 follow-up gap, closed)

`InviteDeliveryJob`'s own class docblock flagged that no
`task_job_definitions` seed row existed for `invite:delivery`/`invite:sweep`/
`invite:expire` — "until a follow-up migration inserts [these rows] ...
NOTHING actually drains the outbox in a running deployment." This is now
closed by `migrations/input/20260722_051_005_invite_job_definitions.json`
(applied via `scripts/apply-051-invite-job-definitions.php`, run-twice
verified idempotent).

```sql
SELECT name, schedule, isEnabled, queue, scope FROM task_job_definitions
WHERE name IN ('invite:delivery', 'invite:sweep', 'invite:expire') ORDER BY name;
```

**Executed 2026-07-22:**

```
{"name":"invite:delivery","schedule":null,"isEnabled":1,"queue":"default","scope":"global"}
{"name":"invite:expire","schedule":"0 3 * * *","isEnabled":1,"queue":"low","scope":"global"}
{"name":"invite:sweep","schedule":"*\/5 * * * *","isEnabled":1,"queue":"default","scope":"global"}
```

`invite:delivery` correctly has `schedule=NULL` (on-demand dispatch only,
per its own docblock); `invite:sweep` fires every 5 minutes; `invite:expire`
daily at 03:00 UTC. All three `isEnabled=1`.

---

## Summary: live status as of this phase (T3)

| Probe | Data source | Live today? |
|---|---|---|
| Queue age | `inviteMessages.state='queued'` | **Yes** (seeded + verified this phase) |
| Dead rows | `inviteMessages.state='dead'` | **Yes** |
| Stuck-sending / worker-crash detection | `inviteMessages.state='sending'` | **Yes** |
| Worker absence | `task_workers` + `worker:manager --status` | **Yes** (4 healthy workers confirmed running) |
| Send-failure rate | `inviteMessages` | **Yes** |
| Webhook signature failures | `logs/webhook-delivery.log` (path corrected) | **Yes** (log-based) |
| Webhook write-integrity | `inviteMessages` UNIQUE constraint | **Yes** (confirmed empty/healthy) |
| Job-definition registration | `task_job_definitions` | **Yes** (T3.8 follow-up migration closes the gap) |

Every probe above ran against the live schema with real (seeded, then
cleaned up) data — none of this is theoretical/"once T3 lands" anymore.