outbound_log + watchdog

outbound_log is the single table joining all three outbound channels into one observable row-per-send view.

Schema

CREATE TABLE outbound_log (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  channel         TEXT NOT NULL,   -- email | sms | beds24_message
  purpose         TEXT,            -- heads_up, t2_reminder, etc.
  booking_id      INTEGER,
  recipient       TEXT,            -- email, phone, or "thread"
  message_id      TEXT,            -- Mailgun id, Twilio Sid, Beds24 new.id
  sent_at         TEXT NOT NULL DEFAULT (datetime('now')),
  delivered_at    TEXT,            -- set by webhook handler on terminal-success
  failed_at       TEXT,            -- set by webhook handler on terminal-failure
  failure_reason  TEXT,
  watchdog_alerted_at TEXT         -- set by watchdog cron after alerting Bill
);

Write paths

Channel	Where inserted
`email`	`sendEmail` in `src/mailgun/client.ts` (uses `recordOutbound`)
`sms`	`sendSMS` in `src/twilio/client.ts`
`beds24_message`	`sendBookingMessage` in `src/beds24/client.ts`

Each call records the message_id returned by the upstream service so we can correlate later.

Update paths

Trigger	Function	Column updated
Mailgun `delivered` event	`markOutboundDelivered`	`delivered_at`
Mailgun `failed` / `rejected` event	`markOutboundFailed`	`failed_at`, `failure_reason`
Twilio `delivered` status	`markOutboundDelivered`	`delivered_at`
Twilio `failed` / `undelivered`	`markOutboundFailed`	`failed_at`, `failure_reason`
Beds24 thread reconciliation	`markOutboundDelivered`	`delivered_at`
Watchdog after alerting	(inline UPDATE)	`watchdog_alerted_at`

Watchdog (delivery)

runDeliveryWatchdog (details) sweeps every 15 min for rows >10min old with neither delivered_at nor failed_at set. SMSes Bill once per stale row, sets watchdog_alerted_at for dedup.

Cross-channel queries

Because all three channels write to the same table, joining is trivial:

-- All sends for booking 86704187, any channel, last 24h
SELECT channel, purpose, recipient, sent_at, delivered_at, failed_at
  FROM outbound_log
  WHERE booking_id = 86704187 AND sent_at >= datetime('now', '-1 day')
  ORDER BY id DESC;

This is what powers /admin/delivery-status?booking={id}.

Source

src/lib/outbound-log.ts — recordOutbound, markOutboundDelivered, markOutboundFailed
migrations/0024_delivery_observability.sql
Tests cover the integration end-to-end across mailgun/twilio/beds24 paths