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          │
            └──────────────┘                    └────────────────────┘

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 with unique 96-bit nonce per encryption
• Envelope encryption with KMS-backed KEK
• Storage format: nonce:ciphertext:tag (Base64)

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'

notification_deliveries

Audit log for external channel deliveries.

Enums:

CREATE TYPE delivery_status AS ENUM (
  'pending',     -- Queued for send
  'processing',  -- Currently sending
  'sent',        -- Provider accepted
  'failed',      -- Provider rejected (after retries)
  'skipped'      -- Channel inactive, user setting off
);

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

Indexes:

• notification_id - lookup deliveries for a notification
• Partial index on (status, created_at) WHERE status = 'failed' - for retry job
• Partial index on (status, created_at) WHERE status = 'pending' - for processing

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.

Why columns, not junction table?

• Fixed set of channels (3) and types (4) = 12 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
CREATE INDEX notification_deliveries_pending_idx
ON notification_deliveries (status, created_at)
WHERE status = 'pending';

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 true,
ADD COLUMN telegram_comment BOOLEAN NOT NULL DEFAULT false,
ADD COLUMN telegram_security BOOLEAN NOT NULL DEFAULT true,
ADD COLUMN telegram_system BOOLEAN NOT NULL DEFAULT true,
ADD COLUMN discord_mention BOOLEAN NOT NULL DEFAULT true,
ADD COLUMN discord_comment BOOLEAN NOT NULL DEFAULT false,
ADD COLUMN discord_security BOOLEAN NOT NULL DEFAULT true,
ADD COLUMN discord_system BOOLEAN NOT NULL DEFAULT true;

Migration 4: Delivery Tracking

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

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

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,
  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 (status, created_at)
WHERE status = 'pending';

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