Notification Service — Data Model

Status: populated Owner: Platform Engineering Last updated: 2026-04-18 Companion: DOMAIN_MODEL

Schema: notif. Owned exclusively by notification-service.

1. Tables

notif.notification_log

CREATE TABLE notif.notification_log (
  notification_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id            UUID NOT NULL,
  account_id           UUID NOT NULL,
  recipient_id         TEXT NOT NULL,
  recipient_address    TEXT NOT NULL,               -- masked at app layer before logging
  channel              TEXT NOT NULL,               -- 'EMAIL' | 'SMS'
  category             TEXT NOT NULL,
  template_id          UUID REFERENCES notif.notification_templates(template_id),
  source_event_type    TEXT NOT NULL,
  source_event_id      TEXT NOT NULL,               -- Nats-Msg-Id
  status               TEXT NOT NULL,               -- 'PENDING' | 'SENT' | 'FAILED' | 'SUPPRESSED'
  provider_message_id  TEXT,
  error_message        TEXT,
  attempt_count        SMALLINT NOT NULL DEFAULT 1,
  sent_at              TIMESTAMPTZ,
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);
-- Monthly partitions, 90-day retention

Indexes:

• ix_notification_log_account_id_created_at on (account_id, created_at DESC)
• ix_notification_log_source_event_id on (source_event_id) — dedup check
• ix_notification_log_status on (status) WHERE status = 'FAILED'

notif.notification_preferences

CREATE TABLE notif.notification_preferences (
  preference_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id      UUID NOT NULL,
  category        TEXT NOT NULL,
  channel         TEXT NOT NULL,
  opted_out       BOOLEAN NOT NULL DEFAULT false,
  updated_by      TEXT NOT NULL,
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (account_id, category, channel)
);

notif.notification_templates

CREATE TABLE notif.notification_templates (
  template_id        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type               TEXT NOT NULL,
  channel            TEXT NOT NULL,
  subject            TEXT,                         -- Email only
  body_html          TEXT NOT NULL,                -- Handlebars+Mjml for email; Handlebars text for SMS
  body_text          TEXT NOT NULL,                -- Plain-text fallback
  variables_schema   JSONB NOT NULL DEFAULT '{}',  -- JSON Schema for validation
  is_active          BOOLEAN NOT NULL DEFAULT true,
  version            INTEGER NOT NULL DEFAULT 1,
  updated_by         TEXT NOT NULL,
  updated_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_at         TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX ix_notification_templates_active
  ON notif.notification_templates (type, channel)
  WHERE is_active = true;

2. Redis Keys

Key	TTL	Purpose
notif:recipients:platform_admins	300s	Cached platform.admin recipient list
notif:template:{type}:{channel}	300s	Active template cache (optional optimization)

3. TypeScript Interfaces

export interface NotificationLogRow {
  notificationId: string;
  tenantId: string;
  accountId: string;
  recipientId: string;
  recipientAddress: string;
  channel: 'EMAIL' | 'SMS';
  category: string;
  templateId: string | null;
  sourceEventType: string;
  sourceEventId: string;
  status: 'PENDING' | 'SENT' | 'FAILED' | 'SUPPRESSED';
  providerMessageId: string | null;
  errorMessage: string | null;
  attemptCount: number;
  sentAt: Date | null;
  createdAt: Date;
}

export interface NotificationTemplateRow {
  templateId: string;
  type: string;
  channel: 'EMAIL' | 'SMS';
  subject: string | null;
  bodyHtml: string;
  bodyText: string;
  variablesSchema: Record<string, unknown>;
  isActive: boolean;
  version: number;
  updatedBy: string;
  updatedAt: Date;
}

4. Partitioning & Retention

• notification_log: monthly range partitions on created_at; 90-day retention then drop.
• No archival needed — notification log is operational audit, not financial record.