QuickandClaude Fable 5
df95e3cb95
Adds email as a notification channel built on the shared SMTP core (mail.Sender). Email mode is a per-account preference (off, daily digest, or per-episode) stored in notification_email_prefs; delivery is an account-watermark sweep over notification_deliveries that dedupes cross-profile duplicates, advancing the watermark only after a successful send. Admin controls cover the channel kill switch, the per-episode allowance (off coerces those accounts to the digest), digest hour, and an external URL for deep links inside emails. Availability is advertised through /notifications/capability and the user settings page gains an Email section for opt-in. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
35 lines
1.5 KiB
SQL
35 lines
1.5 KiB
SQL
-- +goose Up
|
|
-- +goose StatementBegin
|
|
-- Email notification channel (docs/superpowers/plans/notifications/06,
|
|
-- item 3). Email addresses live on login accounts (users), not profiles, so
|
|
-- the mode and dispatch state are account-level. The email worker sweeps
|
|
-- notification_deliveries per user and advances the watermark only after a
|
|
-- successful SMTP send, so a crash or SMTP outage re-sends instead of
|
|
-- dropping; the watermark is initialized to now() whenever the channel is
|
|
-- enabled so history never floods a fresh opt-in.
|
|
CREATE TABLE public.notification_email_prefs (
|
|
user_id integer PRIMARY KEY,
|
|
mode text NOT NULL DEFAULT 'off'
|
|
CHECK (mode IN ('off', 'per_episode', 'daily_digest')),
|
|
watermark_created_at timestamptz NOT NULL DEFAULT now(),
|
|
watermark_id text NOT NULL DEFAULT '',
|
|
last_digest_at timestamptz,
|
|
last_attempt_at timestamptz,
|
|
consecutive_failures integer NOT NULL DEFAULT 0,
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
-- Deliberately no FK to users: notification tables stay FK-free toward
|
|
-- account/profile storage (see 20260611100000). Rows for deleted accounts
|
|
-- drop out of the recipient join and are inert.
|
|
|
|
-- The email sweep reads deliveries by account, not profile.
|
|
CREATE INDEX notification_deliveries_user_created_idx
|
|
ON public.notification_deliveries (user_id, created_at, id);
|
|
-- +goose StatementEnd
|
|
|
|
-- +goose Down
|
|
-- +goose StatementBegin
|
|
DROP INDEX IF EXISTS public.notification_deliveries_user_created_idx;
|
|
DROP TABLE IF EXISTS public.notification_email_prefs;
|
|
-- +goose StatementEnd
|