Notification Schema

Database tables for multi-channel notification system.

Note: The notifications and notification_settings base tables are defined in ../app-shell/notifications.md. This file covers multi-channel extensions.

Schema Overview

┌──────────────────────┐
│        user          │
└──────────┬───────────┘
           │
     ┌─────┴─────┬────────────────┬──────────────────┐
     │           │                │                  │
     ▼           ▼                ▼                  ▼
┌──────────┐ ┌───────────────┐ ┌────────────────┐ ┌────────────────┐
│user_     │ │user_          │ │notification_   │ │telegram_       │
│telegram_ │ │discord_       │ │settings        │ │verification_   │
│accounts  │ │accounts       │ │(extended)      │ │tokens          │
└──────────┘ └───────────────┘ └────────────────┘ └────────────────┘
                                       │
                                       │
                    ┌──────────────────┴──────────────────┐
                    │                                      │
                    ▼                                      ▼
            ┌──────────────┐                    ┌────────────────────┐
            │notifications │                    │notification_       │
            │  (existing)  │───────────────────▶│deliveries          │
            └──────────────┘                    └────────────────────┘

push_subscriptions hangs off user the same way as user_telegram_accounts/user_discord_accounts (omitted above for width) — except it's N rows per user, not one.

New Tables

user_telegram_accounts

Stores Telegram chat_id for sending messages.

Indexes:

• user_id (unique) - lookup by user
• telegram_chat_id (unique) - prevent duplicate linking

Design decisions:

• One-to-one with user (UNIQUE constraint)
• Soft delete via is_active + unlinked_at for audit trail
• No tokens stored - Telegram uses chat_id only

user_discord_accounts

Stores Discord credentials with OAuth tokens.

Indexes:

• user_id (unique) - lookup by user
• discord_user_id (unique) - prevent duplicate linking
• Partial index on token_expires_at WHERE is_active = true AND token_refresh_failed_at IS NULL - for refresh job

Token encryption:

• AES-256-GCM (Web Crypto) with unique 96-bit nonce per encryption
• Key is a 64-char hex ENCRYPTION_KEY env var (raw 32-byte key, no KMS/KEK)
• Storage format: nonce:ciphertext (Base64) — the GCM auth tag is appended to the ciphertext by Web Crypto, so there is no separate :tag segment

telegram_verification_tokens

Temporary tokens for deep link verification.

Indexes:

• token (unique) - lookup for validation
• Partial index on expires_at WHERE used_at IS NULL - for cleanup job

Cleanup: Delete records where expires_at < NOW() - INTERVAL '1 day'

push_subscriptions

Per-device Web Push subscriptions. Unlike Telegram/Discord, this is N rows per user — one per browser/device — not a one-to-one link.

Indexes:

• user_id - fan-out lookup: all subscriptions for a user, on every push send
• endpoint (unique) - one row per subscription, prevents duplicate registration

Design decisions:

• Capped at 10 subscriptions per user — createPushSubscription (db/notifications/mutations.ts) evicts the oldest (by created_at) past the cap
• Endpoint pruned on 404/410 from the push service (deletePushSubscriptionByEndpoint) — dead subscriptions don't accumulate
• Cascade-deleted with the account — no orphaned subscriptions

notification_deliveries

Audit log for external channel deliveries.

next_attempt_at is NOT NULL on purpose: nullable would force a COALESCE into both the claim's WHERE and its ORDER BY, and btree ASC sorts NULLs last — never-attempted rows would queue behind every retry.

There is no separate claimed_at / lock_expires_at / worker_id. attempted_at is the claim stamp, the lease length is a constant (DELIVERY_CLAIM_LEASE_MS), and attempts is the fence. See architecture/workers.md.

Enums:

CREATE TYPE delivery_status AS ENUM (
  'pending',     -- Queued for send
  'processing',  -- Currently sending
  'sent',        -- Provider accepted
  'failed',      -- Provider says it will never work (403, bad address) — retry is pointless
  'skipped',     -- UNUSED: never written by any code path
  'retrying',    -- UNUSED: a retry is 'pending' with a future next_attempt_at
  'dead'         -- Retryable fault outlived the budget — surfaces in the admin panel
);

CREATE TYPE notification_channel AS ENUM (
  'email',
  'telegram',
  'discord',
  'push'
);

push is a valid enum value (shared with notification_settings and the router), but no row in notification_deliveries ever carries it — push delivery is synchronous and never touches the outbox.

skipped and retrying stay in the enum only because removing a Postgres enum value requires recreating the type. A row being retried is pending with attempts > 0 and a future next_attempt_at — derive the label at read time rather than adding DDL.

Indexes:

• notification_id - lookup deliveries for a notification
• delivery_pending_idx on (next_attempt_at, created_at) WHERE status = 'pending' - the claim's exact WHERE + ORDER BY
• delivery_processing_idx on (attempted_at) WHERE status = 'processing' - the stale-claim reaper
• Partial index on (created_at) WHERE status = 'failed' - failure review
• Partial index WHERE status = 'dead' - for the admin dashboard (getDeadDeliveries)
• (channel, created_at) index - channel health stats (getChannelHealthStats)

Retention:

• sent records: 7 days
• failed records: 30 days
• skipped records: 7 days

Extended Table

notification_settings (add columns)

Extend existing table with per-channel toggles. Notification channel configuration is a Setting (affects functionality) per ../../foundation/user-data.md.

Email covers all 6 notification types (mention, comment, system, success, security, follow); Telegram, Discord, and Push cover only the 4 above. There are deliberately no push_success/push_follow columns — this mirrors the telegram/discord precedent, and the router's key in settings guard means those two types simply never route to push.

Why columns, not junction table?

• Fixed channel/type matrix, stored as flat columns
• Single row per user, no joins needed
• Simpler queries: SELECT telegram_mention FROM notification_settings WHERE user_id = ?
• Adding a new type = add columns (migration)
• Adding a new channel = add columns (migration, but rare)

Junction table alternative considered:

-- Rejected: more complex queries, no clear benefit for small matrix
CREATE TABLE notification_channel_settings (
  user_id TEXT,
  notification_type notification_type_enum,
  channel notification_channel,
  enabled BOOLEAN,
  PRIMARY KEY (user_id, notification_type, channel)
);

Index Strategy

Hot Path Queries

Cold Path Queries

Partial Indexes

Partial indexes reduce size and improve performance for filtered queries:

-- Only active accounts needing refresh
CREATE INDEX user_discord_accounts_needs_refresh_idx
ON user_discord_accounts (token_expires_at)
WHERE is_active = true AND token_refresh_failed_at IS NULL;

-- Only unused verification tokens
CREATE INDEX telegram_verification_tokens_cleanup_idx
ON telegram_verification_tokens (expires_at)
WHERE used_at IS NULL;

-- Only failed deliveries
CREATE INDEX notification_deliveries_failed_idx
ON notification_deliveries (status, created_at)
WHERE status = 'failed';

-- Only pending deliveries — ordered by due-time, which is what the claim scans
CREATE INDEX notification_deliveries_pending_idx
ON notification_deliveries (next_attempt_at, created_at)
WHERE status = 'pending';

-- Claimed-but-unreported rows, for the stale-claim reaper
CREATE INDEX notification_deliveries_processing_idx
ON notification_deliveries (attempted_at)
WHERE status = 'processing';

Migration Plan

Migration 1: Channel Connection Tables

-- user_telegram_accounts
CREATE TABLE user_telegram_accounts (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL UNIQUE REFERENCES "user"(id) ON DELETE CASCADE,
  telegram_chat_id TEXT NOT NULL UNIQUE,
  telegram_username TEXT,
  is_active BOOLEAN NOT NULL DEFAULT true,
  linked_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  unlinked_at TIMESTAMPTZ
);

-- user_discord_accounts
CREATE TABLE user_discord_accounts (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL UNIQUE REFERENCES "user"(id) ON DELETE CASCADE,
  discord_user_id TEXT NOT NULL UNIQUE,
  discord_username TEXT,
  access_token TEXT NOT NULL,
  refresh_token TEXT NOT NULL,
  token_expires_at TIMESTAMPTZ NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT true,
  token_refresh_failed_at TIMESTAMPTZ,
  linked_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  tokens_refreshed_at TIMESTAMPTZ,
  unlinked_at TIMESTAMPTZ
);

CREATE INDEX user_discord_accounts_needs_refresh_idx
ON user_discord_accounts (token_expires_at)
WHERE is_active = true AND token_refresh_failed_at IS NULL;

Migration 2: Verification Tokens

CREATE TABLE telegram_verification_tokens (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  token TEXT NOT NULL UNIQUE,
  expires_at TIMESTAMPTZ NOT NULL,
  used_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX telegram_verification_tokens_cleanup_idx
ON telegram_verification_tokens (expires_at)
WHERE used_at IS NULL;

Migration 3: Extended Settings

ALTER TABLE notification_settings
ADD COLUMN telegram_mention BOOLEAN NOT NULL DEFAULT false,
ADD COLUMN telegram_comment BOOLEAN NOT NULL DEFAULT false,
ADD COLUMN telegram_system BOOLEAN NOT NULL DEFAULT false,
ADD COLUMN telegram_security BOOLEAN NOT NULL DEFAULT true,
ADD COLUMN discord_mention BOOLEAN NOT NULL DEFAULT false,
ADD COLUMN discord_comment BOOLEAN NOT NULL DEFAULT false,
ADD COLUMN discord_system BOOLEAN NOT NULL DEFAULT false,
ADD COLUMN discord_security BOOLEAN NOT NULL DEFAULT true,
ADD COLUMN push_mention BOOLEAN NOT NULL DEFAULT false,
ADD COLUMN push_comment BOOLEAN NOT NULL DEFAULT false,
ADD COLUMN push_system BOOLEAN NOT NULL DEFAULT false,
ADD COLUMN push_security BOOLEAN NOT NULL DEFAULT true;

Migration 4: Delivery Tracking

CREATE TYPE delivery_status AS ENUM (
  'pending', 'processing', 'sent', 'failed', 'skipped', 'retrying', 'dead'
);

CREATE TYPE notification_channel AS ENUM (
  'email', 'telegram', 'discord', 'push'
);

CREATE TABLE notification_deliveries (
  id TEXT PRIMARY KEY,
  notification_id TEXT NOT NULL REFERENCES notifications(id) ON DELETE CASCADE,
  channel notification_channel NOT NULL,
  status delivery_status NOT NULL DEFAULT 'pending',
  provider_message_id TEXT,
  error_code TEXT,
  error_message TEXT,
  attempts INTEGER NOT NULL DEFAULT 0,
  next_attempt_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  attempted_at TIMESTAMPTZ,
  sent_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX notification_deliveries_notification_id_idx
ON notification_deliveries (notification_id);

CREATE INDEX notification_deliveries_failed_idx
ON notification_deliveries (status, created_at)
WHERE status = 'failed';

CREATE INDEX notification_deliveries_pending_idx
ON notification_deliveries (next_attempt_at, created_at)
WHERE status = 'pending';

CREATE INDEX notification_deliveries_processing_idx
ON notification_deliveries (attempted_at)
WHERE status = 'processing';

CREATE INDEX notification_deliveries_dead_idx
ON notification_deliveries (status, created_at)
WHERE status = 'dead';

CREATE INDEX notification_deliveries_channel_recent_idx
ON notification_deliveries (channel, created_at);

Migration 5: Push Subscriptions

CREATE TABLE push_subscriptions (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  endpoint TEXT NOT NULL UNIQUE,
  p256dh TEXT NOT NULL,
  auth TEXT NOT NULL,
  user_agent TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_used_at TIMESTAMPTZ
);

CREATE INDEX push_subscriptions_user_id_idx
ON push_subscriptions (user_id);

Related

• ./routing.md - How tables are used in delivery
• ./channels.md - Connection flows that populate tables
• ../db/relational.md - Full database schema
• ../app-shell/notifications.md - Existing notifications table
• ../pwa.md - push_subscriptions lifecycle, payload contract