QuickandClaude Fable 5
ebf3352bda
Re-key the email notification channel from login accounts to profiles. Each profile owns its mode, dispatch watermark, and destination address; there is deliberately no fallback to the account email, so the account holder no longer receives mail for every household profile. A profile receives nothing until its own address is verified. - Genericize the watermark-sweep engine over a recipient key (accountChannel[K]): email keys by profile_id, Discord stays on user_id. Delivery reads move into the channel adapters. - Custom addresses verify via single-use SHA-256-hashed token links served by a public endpoint; enabling the channel requires a verified address, and clearing the address switches the channel off. - Addresses are globally unique (case-insensitive): rejected when verified for another profile or matching another account's email or username. Checked at request time, re-checked at verify time (first-to-verify wins), backstopped by a partial unique index. - Every email carries an RFC 8058 one-click unsubscribe link backed by a per-profile capability token, minted lazily under the claim tx. - Child profiles cannot set addresses (and so receive no email in v1). - Verification sends are rate limited (1/min, 10/day per profile); mail.Message gains custom header support for List-Unsubscribe. - Migration drops the account-level prefs table without carrying opt-ins over, so nobody gets surprise emails post-upgrade. Android/Apple notification settings need follow-up for the new profile-scoped response shape and address-management endpoints. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
84 lines
3.9 KiB
SQL
84 lines
3.9 KiB
SQL
-- +goose Up
|
|
-- +goose StatementBegin
|
|
-- Re-keys the email notification channel from login accounts to profiles.
|
|
-- Each profile now owns its mode, dispatch watermark, and verified
|
|
-- destination address. There is deliberately no fallback to the account
|
|
-- email (it would funnel every profile's mail to the account holder), so
|
|
-- old account-level opt-ins are NOT carried over: a profile receives
|
|
-- nothing until its own address is verified and the mode re-enabled.
|
|
-- user_id is denormalized (no FK, no join to profile storage): profiles may
|
|
-- live in per-user SQLite stores, so Postgres never joins notification
|
|
-- tables against profile tables (see 20260611100000).
|
|
DROP TABLE public.notification_email_prefs;
|
|
|
|
CREATE TABLE public.notification_email_prefs (
|
|
profile_id text PRIMARY KEY,
|
|
user_id integer NOT NULL,
|
|
mode text NOT NULL DEFAULT 'off'
|
|
CHECK (mode IN ('off', 'per_episode', 'daily_digest', 'per_episode_and_digest')),
|
|
-- Verified destination address; '' = fall back to users.email.
|
|
custom_email text NOT NULL DEFAULT '',
|
|
-- In-flight address verification: the candidate address and the SHA-256
|
|
-- hex of its single-use token. Cleared on success or replacement.
|
|
pending_email text NOT NULL DEFAULT '',
|
|
pending_token_hash text NOT NULL DEFAULT '',
|
|
pending_expires_at timestamptz,
|
|
-- Verification-send rate limiting: minimum gap since the last send, plus
|
|
-- a daily cap counted within pending_last_sent_at's UTC day.
|
|
pending_last_sent_at timestamptz,
|
|
verify_sends_today integer NOT NULL DEFAULT 0,
|
|
-- Capability token embedded in every email's unsubscribe link; only
|
|
-- powers "set this profile's mode to off", so stored plaintext. Minted by
|
|
-- the send path right before the first email that embeds it.
|
|
unsubscribe_token text NOT NULL DEFAULT '',
|
|
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()
|
|
);
|
|
|
|
CREATE INDEX notification_email_prefs_user_idx
|
|
ON public.notification_email_prefs (user_id);
|
|
CREATE INDEX notification_email_prefs_pending_token_idx
|
|
ON public.notification_email_prefs (pending_token_hash)
|
|
WHERE pending_token_hash <> '';
|
|
CREATE INDEX notification_email_prefs_unsubscribe_token_idx
|
|
ON public.notification_email_prefs (unsubscribe_token)
|
|
WHERE unsubscribe_token <> '';
|
|
-- One verified destination per profile, globally unique: an address may not
|
|
-- serve two profiles. Backstops the application-level checks against
|
|
-- concurrent verifications.
|
|
CREATE UNIQUE INDEX notification_email_prefs_custom_email_key
|
|
ON public.notification_email_prefs (lower(custom_email))
|
|
WHERE custom_email <> '';
|
|
|
|
-- The per-profile sweep reads deliveries by profile in watermark order.
|
|
CREATE INDEX notification_deliveries_profile_created_idx
|
|
ON public.notification_deliveries (profile_id, created_at, id);
|
|
-- +goose StatementEnd
|
|
|
|
-- +goose Down
|
|
-- +goose StatementBegin
|
|
DROP INDEX IF EXISTS public.notification_deliveries_profile_created_idx;
|
|
|
|
-- Rollback restores the account-keyed schema empty: per-profile state
|
|
-- (verified addresses, watermarks) has no account-level representation, so
|
|
-- accounts re-opt-in. Symmetric with the up migration, which also carries
|
|
-- nothing over.
|
|
DROP TABLE public.notification_email_prefs;
|
|
|
|
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', 'per_episode_and_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()
|
|
);
|
|
-- +goose StatementEnd
|