DATA_MODEL — notification-service

Sibling: DOMAIN_MODEL · APPLICATION_LOGIC · SECURITY_MODEL · SYNC_CONTRACT

Strategic anchors: 06 Data Models · 07 Security/Compliance/Tenancy · 02 Enterprise Architecture §6 Data

1. Storage placement

Concern	Store	Rationale
Authoritative aggregates (Notification, Template, Recipient, Suppression, Channel, WebhookInbound, DispatchBatch)	Cloud SQL Postgres 16 (regional HA)	Strong consistency, RLS, JSONB for snapshots, partitioning
Renderable assets (rendered HTML, raw vendor webhook bodies, attachments)	Cloud Storage (GCS) with CMEK	Cheap, immutable, signed URLs
Hot read state (recent feed, suppression set, rate-limit counters, channel-health status, trigger-map cache)	Memorystore Redis (HA)	Sub-ms reads at dispatch time
Long-term audit/analytics (delivery_attempts, opened/clicked, suppression history)	BigQuery via Datastream CDC; cold partitions exported as Parquet to GCS	Analytical queries; cheap retention
Outbox + inbox + scheduled work	Cloud SQL in same DB	Transactional with aggregates
Secrets (vendor API keys, HMAC signing keys)	Secret Manager	Rotation, audit, IAM-bound

Database name: melmastoon_notification. Logical schema: notification. RLS enabled on all tenant-scoped tables; platform-global tables (e.g., platform templates) live in schema notification_platform with no tenant column.

2. Identity strategy

ULID with service-specific prefixes (per NAMING §6.4):

Aggregate	Prefix
Notification	`ntf_`
Template	`tpl_`
TemplateVersion	`tpv_`
Recipient	`rcp_`
RecipientPreferences	`rpf_`
DeliveryAttempt	`dat_`
SuppressionRecord	`sup_`
Channel	`ch_`
ChannelCredential	`chc_`
WebhookInbound	`whi_`
DispatchBatch	`dbt_`
OptOutToken	`oot_`

ULIDs are 26-char Crockford-base32; prefix segregates aggregates lexically and inside logs.

3. Postgres schema (core DDL)

All timestamps TIMESTAMPTZ DEFAULT now(). All money fields are BIGINT micros + TEXT currency. All addresses are stored only as address_hash CHAR(71) (sha256: + hex) plus an envelope-encrypted ciphertext in a separate column. JSONB blobs are validated at the application boundary; no DB-side JSON Schema enforcement.

3.1 `tenants_local` (read-only projection)

A small projection of tenant-service data we reference. Not authoritative; refreshed by tenant-projection-consumer.

CREATE TABLE notification.tenants_local (
  tenant_id        TEXT PRIMARY KEY,
  region           TEXT NOT NULL,
  status           TEXT NOT NULL,                -- 'active'|'suspended'|'deleting'
  default_locale   TEXT NOT NULL DEFAULT 'en-US',
  default_timezone TEXT NOT NULL DEFAULT 'UTC',
  notification_policy JSONB NOT NULL DEFAULT '{}'::jsonb,
  theme_etag       TEXT NOT NULL,
  updated_at       TIMESTAMPTZ NOT NULL
);

3.2 `notifications` (PARTITIONED by RANGE on `created_at`, monthly)

CREATE TABLE notification.notifications (
  id                   TEXT NOT NULL,
  tenant_id            TEXT NOT NULL REFERENCES notification.tenants_local(tenant_id),
  template_id          TEXT NOT NULL,
  template_version_id  TEXT NOT NULL,
  template_key         TEXT NOT NULL,
  template_semver      TEXT NOT NULL,
  channel              TEXT NOT NULL CHECK (channel IN ('email','sms','whatsapp','push','inapp','voice')),
  category             TEXT NOT NULL CHECK (category IN ('transactional','operational','security','reminder','marketing','system')),
  priority             TEXT NOT NULL DEFAULT 'normal' CHECK (priority IN ('urgent','normal','low')),
  locale               TEXT NOT NULL,
  recipient_id         TEXT NOT NULL,
  address_hash         CHAR(71) NOT NULL,
  address_ciphertext   BYTEA NOT NULL,                                 -- envelope-encrypted via KMS
  sender               JSONB NOT NULL,                                  -- frozen Sender VO
  render_snapshot      JSONB NOT NULL,                                  -- subject/preheader/body-format/checksum + bodyRef (uri)
  variables_hash       CHAR(71) NOT NULL,
  ai_provenance        JSONB,                                           -- when AI personalised
  source_event         JSONB,                                           -- { id, type, producedAt }
  batch_id             TEXT,
  status               TEXT NOT NULL CHECK (status IN ('queued','scheduled','dispatched','delivered','failed','suppressed')),
  scheduled_for        TIMESTAMPTZ,
  expires_at           TIMESTAMPTZ NOT NULL,
  read_at              TIMESTAMPTZ,
  queued_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
  dispatched_at        TIMESTAMPTZ,
  delivered_at         TIMESTAMPTZ,
  failed_at            TIMESTAMPTZ,
  suppressed_at        TIMESTAMPTZ,
  suppression_reason   TEXT,
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  version              INTEGER NOT NULL DEFAULT 1,
  PRIMARY KEY (created_at, id)                                          -- partition pruning friendly
) PARTITION BY RANGE (created_at);

-- Unique constraint for trigger-fanout idempotency (over partitions, enforced via global unique index)
CREATE UNIQUE INDEX notifications_uniq_source_recipient
  ON notification.notifications (tenant_id, ((source_event->>'id')), template_key, recipient_id, channel)
  WHERE source_event IS NOT NULL;

CREATE INDEX notifications_tenant_status_sched ON notification.notifications (tenant_id, status, scheduled_for) WHERE status = 'scheduled';
CREATE INDEX notifications_tenant_status_queued ON notification.notifications (tenant_id, status) WHERE status = 'queued';
CREATE INDEX notifications_tenant_recipient_feed ON notification.notifications (tenant_id, recipient_id, created_at DESC) WHERE channel = 'inapp';
CREATE INDEX notifications_tenant_unread_inapp ON notification.notifications (tenant_id, recipient_id) WHERE channel = 'inapp' AND read_at IS NULL;
CREATE INDEX notifications_batch ON notification.notifications (tenant_id, batch_id) WHERE batch_id IS NOT NULL;

ALTER TABLE notification.notifications ENABLE ROW LEVEL SECURITY;
CREATE POLICY notifications_tenant_iso ON notification.notifications
  USING (tenant_id = current_setting('app.tenant_id', true));

pg_partman keeps 14 future + 24 past monthly partitions in hot storage. Older partitions are detached and exported to GCS Parquet (BigQuery cold tier).

3.3 `delivery_attempts` (PARTITIONED by RANGE on `started_at`, monthly)

CREATE TABLE notification.delivery_attempts (
  id                 TEXT NOT NULL,
  notification_id    TEXT NOT NULL,
  tenant_id          TEXT NOT NULL,
  attempt_number     INTEGER NOT NULL,
  channel            TEXT NOT NULL,
  vendor             TEXT NOT NULL,
  vendor_message_id  TEXT,
  outcome            TEXT NOT NULL CHECK (outcome IN ('accepted','rejected_retryable','rejected_terminal','timeout','delivered','bounced','complaint','opened','clicked','failed')),
  http_status        INTEGER,
  latency_ms         INTEGER NOT NULL DEFAULT 0,
  error_code         TEXT,
  error_message      TEXT,
  retry_after_ms     INTEGER,
  raw_response_ref   TEXT,                          -- gs://… URI
  started_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  finished_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (started_at, id)
) PARTITION BY RANGE (started_at);

CREATE INDEX delivery_attempts_notif ON notification.delivery_attempts (tenant_id, notification_id, attempt_number);
CREATE INDEX delivery_attempts_vendor_msgid ON notification.delivery_attempts (vendor, vendor_message_id) WHERE vendor_message_id IS NOT NULL;

ALTER TABLE notification.delivery_attempts ENABLE ROW LEVEL SECURITY;
CREATE POLICY delivery_attempts_tenant_iso ON notification.delivery_attempts
  USING (tenant_id = current_setting('app.tenant_id', true));

3.4 `templates` and `template_versions`

Templates exist in two flavours:

Platform templates in notification_platform.templates (no tenant_id).
Tenant overrides in notification.templates (tenant_id NOT NULL, RLS-bound).

CREATE TABLE notification.templates (
  id              TEXT PRIMARY KEY,
  tenant_id       TEXT NOT NULL REFERENCES notification.tenants_local(tenant_id),
  key             TEXT NOT NULL,
  channel         TEXT NOT NULL,
  category        TEXT NOT NULL,
  status          TEXT NOT NULL CHECK (status IN ('active','archived')),
  active_version_id TEXT,
  default_locale  TEXT NOT NULL,
  fallback_chain  TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  renderer_profile TEXT NOT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  version         INTEGER NOT NULL DEFAULT 1,
  UNIQUE (tenant_id, key, channel)
);
ALTER TABLE notification.templates ENABLE ROW LEVEL SECURITY;
CREATE POLICY templates_tenant_iso ON notification.templates
  USING (tenant_id = current_setting('app.tenant_id', true));

CREATE TABLE notification.template_versions (
  id                 TEXT PRIMARY KEY,
  template_id        TEXT NOT NULL,
  tenant_id          TEXT NOT NULL,
  semver             TEXT NOT NULL,
  status             TEXT NOT NULL CHECK (status IN ('draft','active','archived')),
  source             TEXT NOT NULL CHECK (source IN ('platform','tenant','ai_drafted')),
  ai_provenance      JSONB,
  approver_user_id   TEXT,
  variables_schema   JSONB NOT NULL,
  fallback_chain     TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  whatsapp_template_refs JSONB,
  preview_samples    JSONB,
  locales            JSONB NOT NULL,                                    -- { 'ps-AF': { subject, preheader, body, bodyFormat, direction }, ... }
  published_at       TIMESTAMPTZ,
  archived_at        TIMESTAMPTZ,
  created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  version            INTEGER NOT NULL DEFAULT 1,
  UNIQUE (template_id, semver)
);
ALTER TABLE notification.template_versions ENABLE ROW LEVEL SECURITY;
CREATE POLICY template_versions_tenant_iso ON notification.template_versions
  USING (tenant_id = current_setting('app.tenant_id', true));

The platform-global mirrors live in schema notification_platform with the same DDL minus tenant_id and RLS. Resolution at runtime: tenant override wins by (key, channel); otherwise platform-global.

3.5 `recipients` and `notification_preferences`

CREATE TABLE notification.recipients (
  id            TEXT PRIMARY KEY,
  tenant_id     TEXT NOT NULL REFERENCES notification.tenants_local(tenant_id),
  identity_type TEXT CHECK (identity_type IN ('guest','user','vendor','anonymous')),
  identity_id   TEXT,
  display_name  TEXT,
  primary_locale TEXT NOT NULL DEFAULT 'en-US',
  primary_timezone TEXT NOT NULL DEFAULT 'UTC',
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  version       INTEGER NOT NULL DEFAULT 1,
  UNIQUE (tenant_id, identity_type, identity_id)
);
ALTER TABLE notification.recipients ENABLE ROW LEVEL SECURITY;
CREATE POLICY recipients_tenant_iso ON notification.recipients
  USING (tenant_id = current_setting('app.tenant_id', true));

CREATE TABLE notification.recipient_addresses (
  id                 TEXT PRIMARY KEY,
  tenant_id          TEXT NOT NULL,
  recipient_id       TEXT NOT NULL REFERENCES notification.recipients(id) ON DELETE CASCADE,
  channel            TEXT NOT NULL CHECK (channel IN ('email','sms','whatsapp','push','inapp','voice')),
  address_hash       CHAR(71) NOT NULL,
  address_ciphertext BYTEA NOT NULL,
  verified           BOOLEAN NOT NULL DEFAULT false,
  status             TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','suppressed','revoked')),
  metadata           JSONB,
  created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, channel, address_hash)
);
CREATE INDEX recipient_addresses_recipient ON notification.recipient_addresses (tenant_id, recipient_id);
ALTER TABLE notification.recipient_addresses ENABLE ROW LEVEL SECURITY;
CREATE POLICY recipient_addresses_tenant_iso ON notification.recipient_addresses
  USING (tenant_id = current_setting('app.tenant_id', true));

CREATE TABLE notification.notification_preferences (
  id              TEXT PRIMARY KEY,
  tenant_id       TEXT NOT NULL,
  recipient_id    TEXT NOT NULL UNIQUE REFERENCES notification.recipients(id) ON DELETE CASCADE,
  locale          TEXT NOT NULL,
  timezone        TEXT NOT NULL,
  channels        JSONB NOT NULL,                  -- per-channel allow + categories
  quiet_hours     JSONB,
  marketing_consent JSONB NOT NULL,
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  version         INTEGER NOT NULL DEFAULT 1
);
ALTER TABLE notification.notification_preferences ENABLE ROW LEVEL SECURITY;
CREATE POLICY notification_preferences_tenant_iso ON notification.notification_preferences
  USING (tenant_id = current_setting('app.tenant_id', true));

3.6 `suppressions`

CREATE TABLE notification.suppressions (
  id              TEXT PRIMARY KEY,
  tenant_id       TEXT NOT NULL,
  channel         TEXT NOT NULL CHECK (channel IN ('email','sms','whatsapp','push','voice')),
  address_hash    CHAR(71) NOT NULL,
  reason          TEXT NOT NULL CHECK (reason IN ('hard_bounce','complaint','invalid_address','opt_out','manual','compliance','rate_limit')),
  source_notification_id TEXT,
  notes           TEXT,
  expires_at      TIMESTAMPTZ,                                          -- null = permanent
  released_at     TIMESTAMPTZ,
  released_by     TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, channel, address_hash) WHERE released_at IS NULL
);
CREATE INDEX suppressions_lookup ON notification.suppressions (tenant_id, channel, address_hash) WHERE released_at IS NULL;
ALTER TABLE notification.suppressions ENABLE ROW LEVEL SECURITY;
CREATE POLICY suppressions_tenant_iso ON notification.suppressions
  USING (tenant_id = current_setting('app.tenant_id', true));

3.7 `channels` and `channel_credentials`

CREATE TABLE notification.channels (
  id                  TEXT PRIMARY KEY,
  tenant_id           TEXT NOT NULL,
  channel             TEXT NOT NULL CHECK (channel IN ('email','sms','whatsapp','push','inapp','voice')),
  vendor              TEXT NOT NULL,
  fallback_vendor     TEXT,
  sender              JSONB NOT NULL,                                   -- Sender VO
  branding_overrides  JSONB,
  rate_limit          JSONB NOT NULL,
  status              TEXT NOT NULL CHECK (status IN ('active','degraded','down','disabled')),
  health              JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  version             INTEGER NOT NULL DEFAULT 1,
  UNIQUE (tenant_id, channel)
);
ALTER TABLE notification.channels ENABLE ROW LEVEL SECURITY;
CREATE POLICY channels_tenant_iso ON notification.channels
  USING (tenant_id = current_setting('app.tenant_id', true));

CREATE TABLE notification.channel_credentials (
  id                TEXT PRIMARY KEY,
  channel_id        TEXT NOT NULL REFERENCES notification.channels(id) ON DELETE CASCADE,
  tenant_id         TEXT NOT NULL,
  vendor            TEXT NOT NULL,
  secret_ref        TEXT NOT NULL,                                       -- secretmanager://...
  status            TEXT NOT NULL CHECK (status IN ('active','superseded','revoked')),
  rotated_at        TIMESTAMPTZ,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE notification.channel_credentials ENABLE ROW LEVEL SECURITY;
CREATE POLICY channel_credentials_tenant_iso ON notification.channel_credentials
  USING (tenant_id = current_setting('app.tenant_id', true));

3.8 `webhook_inbound` and `webhook_inbound_events`

CREATE TABLE notification.webhook_inbound (
  id              TEXT PRIMARY KEY,
  vendor          TEXT NOT NULL,
  received_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  signature_valid BOOLEAN NOT NULL,
  raw_body_ref    TEXT NOT NULL,                  -- gs://…
  status          TEXT NOT NULL CHECK (status IN ('received','applied','rejected','dlq')),
  reason          TEXT
);
CREATE INDEX webhook_inbound_recent ON notification.webhook_inbound (vendor, received_at DESC);

CREATE TABLE notification.webhook_inbound_events (
  id                  TEXT PRIMARY KEY,
  webhook_inbound_id  TEXT NOT NULL REFERENCES notification.webhook_inbound(id) ON DELETE CASCADE,
  tenant_id           TEXT,                       -- nullable until correlated
  vendor              TEXT NOT NULL,
  vendor_message_id   TEXT NOT NULL,
  type                TEXT NOT NULL,
  occurred_at         TIMESTAMPTZ NOT NULL,
  payload             JSONB NOT NULL,
  status              TEXT NOT NULL CHECK (status IN ('queued','applied','late_correlate','dlq')),
  applied_at          TIMESTAMPTZ,
  UNIQUE (vendor, vendor_message_id, type, occurred_at)
);
CREATE INDEX webhook_inbound_events_pending ON notification.webhook_inbound_events (status, occurred_at) WHERE status IN ('queued','late_correlate');

webhook_inbound_events is intentionally NOT under RLS — the row's tenant_id may be unknown at insert time. Read access is restricted to platform engineers and to the application's service account (which scopes by tenant_id after correlation).

3.9 `dispatch_batches`

CREATE TABLE notification.dispatch_batches (
  id              TEXT PRIMARY KEY,
  tenant_id       TEXT NOT NULL,
  name            TEXT NOT NULL,
  category        TEXT NOT NULL,
  template_key    TEXT NOT NULL,
  channel         TEXT NOT NULL,
  recipient_query JSONB NOT NULL,
  variables       JSONB NOT NULL,
  scheduled_for   TIMESTAMPTZ,
  rate_limit      JSONB,
  status          TEXT NOT NULL CHECK (status IN ('draft','scheduled','running','completed','cancelled','failed')),
  totals          JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_by      TEXT NOT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  started_at      TIMESTAMPTZ,
  completed_at    TIMESTAMPTZ
);
ALTER TABLE notification.dispatch_batches ENABLE ROW LEVEL SECURITY;
CREATE POLICY dispatch_batches_tenant_iso ON notification.dispatch_batches
  USING (tenant_id = current_setting('app.tenant_id', true));

3.10 `notification_scheduled` (work projection)

CREATE TABLE notification.notification_scheduled (
  id              TEXT PRIMARY KEY,
  tenant_id       TEXT NOT NULL,
  kind            TEXT NOT NULL,                  -- 'pre_arrival_reminder'|'post_stay_thank_you'|'dunning_step'|'survey'|...
  source_event_id TEXT NOT NULL,
  payload         JSONB NOT NULL,                 -- enqueue payload to use
  run_after       TIMESTAMPTZ NOT NULL,
  obsolete        BOOLEAN NOT NULL DEFAULT false,
  processed_at    TIMESTAMPTZ,
  attempt_count   INTEGER NOT NULL DEFAULT 0,
  last_error      TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX notification_scheduled_due ON notification.notification_scheduled (run_after) WHERE processed_at IS NULL AND obsolete = false;
ALTER TABLE notification.notification_scheduled ENABLE ROW LEVEL SECURITY;
CREATE POLICY notification_scheduled_tenant_iso ON notification.notification_scheduled
  USING (tenant_id = current_setting('app.tenant_id', true));

3.11 `notification_trigger_map`

CREATE TABLE notification.notification_trigger_map (
  id                  TEXT PRIMARY KEY,
  scope               TEXT NOT NULL CHECK (scope IN ('platform','tenant')),
  tenant_id           TEXT,                        -- null for platform
  consumed_event_type TEXT NOT NULL,
  channel             TEXT NOT NULL,
  template_key        TEXT NOT NULL,
  recipient_resolver  TEXT NOT NULL,               -- 'primary_guest'|'tenant_admins'|'iam_subject'|'vendor_assignee'|...
  priority            TEXT NOT NULL DEFAULT 'normal',
  category            TEXT NOT NULL,
  schedule_offset_seconds INTEGER NOT NULL DEFAULT 0,
  schedule_anchor     TEXT,                        -- 'event_produced_at'|'stay_start'|'checked_out_at'|'invoice_due_date'
  enabled             BOOLEAN NOT NULL DEFAULT true,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (scope, tenant_id, consumed_event_type, channel, template_key)
);

3.12 Outbox / inbox / idempotency

CREATE TABLE notification.outbox (
  id            TEXT PRIMARY KEY,                  -- ULID = event id
  tenant_id     TEXT NOT NULL,
  subject       TEXT NOT NULL,
  payload       JSONB NOT NULL,
  envelope      JSONB NOT NULL,
  ordering_key  TEXT NOT NULL,
  enqueued_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  published_at  TIMESTAMPTZ,
  publish_attempts INTEGER NOT NULL DEFAULT 0,
  last_error    TEXT
);
CREATE INDEX outbox_pending ON notification.outbox (enqueued_at) WHERE published_at IS NULL;

CREATE TABLE notification.consumed_events (
  consumer_name TEXT NOT NULL,
  event_id      TEXT NOT NULL,
  consumed_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (consumer_name, event_id)
) PARTITION BY RANGE (consumed_at);
-- 7-day rolling partitions

CREATE TABLE notification.idempotency_keys (
  tenant_id    TEXT NOT NULL,
  scope        TEXT NOT NULL,                      -- e.g. 'notifications.create'
  key          TEXT NOT NULL,
  request_hash TEXT NOT NULL,
  response     JSONB NOT NULL,
  status       INTEGER NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at   TIMESTAMPTZ NOT NULL DEFAULT now() + INTERVAL '24 hours',
  PRIMARY KEY (tenant_id, scope, key)
);
CREATE INDEX idempotency_keys_expiry ON notification.idempotency_keys (expires_at);

3.13 `opt_out_tokens`

CREATE TABLE notification.opt_out_tokens (
  id           TEXT PRIMARY KEY,
  tenant_id    TEXT NOT NULL,
  recipient_id TEXT NOT NULL REFERENCES notification.recipients(id) ON DELETE CASCADE,
  channel      TEXT NOT NULL,
  token_hash   CHAR(71) NOT NULL UNIQUE,           -- sha256:<hex>
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at   TIMESTAMPTZ NOT NULL,
  consumed_at  TIMESTAMPTZ
);
CREATE INDEX opt_out_tokens_lookup ON notification.opt_out_tokens (token_hash) WHERE consumed_at IS NULL;

4. Redis (Memorystore) key layout

Key	Type	TTL	Purpose
`notif:supp:{tenantId}:{channel}`	SET of `address_hash`	60 s (refreshed)	dispatch-time suppression check
`notif:rl:tenant:{tenantId}:{channel}:{minute}`	INCR + EXPIRE	120 s	per-tenant per-minute rate limit
`notif:rl:tenant:{tenantId}:{channel}:{day}`	INCR + EXPIRE	26 h	per-tenant per-day rate limit
`notif:rl:rcp:{tenantId}:{recipientId}:{channel}:{day}`	INCR + EXPIRE	26 h	per-recipient per-day rate limit
`notif:tpl:{tenantId}:{key}:{channel}`	HASH	30 s	template + active version cache
`notif:tm:{tenantId}`	JSON string	30 s	trigger map cache
`notif:ch:{tenantId}:{channel}`	HASH	60 s	channel config + health cache
`notif:ws:{tenantId}:{recipientId}`	SET of WS server ids	live	WebSocket fan-out routing
`notif:ootkn:{tokenHash}`	string `recipientId:channel:tenantId`	until expiry	opt-out token cache
`notif:webhook_dedupe:{vendor}:{vendorMessageId}:{type}`	string `1`	7 d	webhook short-window dedupe (defence-in-depth; DB has the canonical unique constraint)

All keys carry a melm-prod- or melm-stg- prefix in the actual Memorystore deployment.

5. Cloud Storage (GCS) layout

Bucket melmastoon-notifications-{env} (CMEK, regional, dual-region for prod):

gs://melmastoon-notifications-prod/
  tnt_{tenantId}/
    rendered/{yyyy}/{mm}/{dd}/ntf_{id}.{html|txt|json}
    attachments/{yyyy}/{mm}/{dd}/{notificationId}/{filename}
    webhooks/{vendor}/{yyyy}/{mm}/{dd}/whi_{id}.json
  _platform/
    template-assets/{templateVersionId}/{filename}            # static images, logos for MJML

Lifecycle:

rendered/** → COLDLINE after 90 days, ARCHIVE after 365 days, delete after 7 years (regulated).
attachments/** → COLDLINE after 30 days; deletion follows the source aggregate's retention policy (e.g., invoices retain 7 years).
webhooks/** → COLDLINE after 90 days, delete after 18 months.

Object metadata always carries tenantId, notificationId (or webhookInboundId), and a checksum for tamper detection.

6. BigQuery analytics dataset

Dataset melmastoon_notification_analytics_{env}. CDC stream from notifications and delivery_attempts is sunk daily; raw event topics also fan-out via Pub/Sub → BigQuery.

Table	Partition	Cluster	Purpose
`notifications_facts`	DATE(created_at)	`tenant_id, channel, category`	per-notification snapshot for analytics
`delivery_attempts_facts`	DATE(started_at)	`tenant_id, channel, vendor`	latency / vendor scoring
`events_*`	per platform sink	per platform sink	published event audit
`cost_model_v1`	DATE	`tenant_id, channel, vendor`	unit-cost model joining vendor invoices

Notification rendered bodies are NEVER copied into BigQuery; only metadata.

7. Migrations

We use Drizzle migrations (drizzle-kit) with a strict forward-only policy. File naming: <timestamp>_<short_slug>.sql. Each migration includes:

DDL for the change.
A pg_partman re-config call when partitioned tables change.
A back-fill SQL block guarded by IF NOT EXISTS.
A README pointer to the MIGRATION_PLAN row.

CI gates:

drizzle-kit check (no drift).
pgtap smoke tests on the migration in an ephemeral Postgres.
dry-run of the migration on a snapshot of the staging DB; failure blocks merge.

8. Retention & deletion

Data	Hot	Cold	Delete
`notifications` (transactional)	24 months in Postgres	5 years in BigQuery	7 years total
`notifications` (marketing)	6 months in Postgres	18 months in BigQuery	24 months
`delivery_attempts`	24 months in Postgres	5 years in BigQuery	7 years total
`webhook_inbound` (raw GCS)	90 days	18 months coldline	18 months
Rendered HTML/MJML output	90 days hot, 12 months cold	—	per category
`recipients`, `notification_preferences`	until consent revoked	—	crypto-shred on `iam.user.deleted.v1`
`suppressions`	indefinite	—	hard-bounce/complaint suppressions persist for compliance
Templates	indefinite	—	archived versions retained for forensic audit

GDPR / data-subject deletion: iam.user.deleted.v1 triggers crypto-shredding of address_ciphertext for affected recipient_addresses; the address_hash is retained for suppression continuity but is unjoinable to plaintext after key destruction.

9. Multi-tenant isolation guarantees

RLS on every tenant-scoped table; the application sets app.tenant_id per connection (Drizzle middleware).
Service account holds pg_role melmastoon_notification_app which has no BYPASSRLS.
Long-running workers set app.tenant_id per work-item — never operate cross-tenant in a single statement.
Cross-tenant operator queries require pg_role melmastoon_notification_dba which is grantable only via break-glass workflow (audit-tagged in audit-service).

See SECURITY_MODEL §4 for full RLS/role matrix.

1. Storage placement​

2. Identity strategy​

3. Postgres schema (core DDL)​

3.1 tenants_local (read-only projection)​

3.2 notifications (PARTITIONED by RANGE on created_at, monthly)​

3.3 delivery_attempts (PARTITIONED by RANGE on started_at, monthly)​

3.4 templates and template_versions​

3.5 recipients and notification_preferences​

3.6 suppressions​

3.7 channels and channel_credentials​

3.8 webhook_inbound and webhook_inbound_events​

3.9 dispatch_batches​

3.10 notification_scheduled (work projection)​

3.11 notification_trigger_map​

3.12 Outbox / inbox / idempotency​

3.13 opt_out_tokens​

4. Redis (Memorystore) key layout​

5. Cloud Storage (GCS) layout​

6. BigQuery analytics dataset​

7. Migrations​

8. Retention & deletion​

9. Multi-tenant isolation guarantees​