PostgreSQL

Notification system database schema

A schema for a multi-channel notification system: templates, per-user preferences, in-app inbox, email, push. Tracks delivery status and de-duplicates by event key.

Start for free

Database schema previewNotification system database schema

Recreate this in Raltey

Copy this prompt

Paste this into Raltey's AI agent to generate the same diagram, then tweak it on the canvas.

Prompt

Design a PostgreSQL database schema for: Notification system database schema.

A schema for a multi-channel notification system: templates, per-user preferences, in-app inbox, email, push. Tracks delivery status and de-duplicates by event key.

Include these tables: notification_template, notification_preference, notification_event, notification_delivery.

Use proper primary keys, foreign keys, and indexes. Export SQL DDL for PostgreSQL.

Context

- One event can fan out to multiple channels (in-app, email, push).
- Per-user preferences gate each channel per template.
- Idempotent: same event key cannot deliver twice.

Patterns

- Idempotency via unique event_key column.
- Template-driven: copy lives outside the code path.
- Per-user, per-template channel preferences.

Tables

The schema, table by table

notification_template

DBML - notification_template

Table notification_template {
  id           uuid pk
  key          text unique not null   // mention, comment_reply, ...
  title_tpl    text not null
  body_tpl     text not null
  default_channels text[] not null    // [\'in_app\', \'email\']
}

notification_preference

DBML - notification_preference

Table notification_preference {
  user_id     uuid [ref: > user_account.id]
  template_id uuid [ref: > notification_template.id]
  channels    text[] not null
  pk (user_id, template_id)
}

notification_event

One row per logical event before fan-out.

DBML - notification_event

Table notification_event {
  id           uuid pk
  template_id  uuid [ref: > notification_template.id, not null]
  recipient_id uuid [ref: > user_account.id, not null]
  actor_id     uuid [ref: > user_account.id]
  payload      jsonb
  event_key    text unique          // idempotency
  created_at   timestamptz default `now()`
}

notification_delivery

One row per (event, channel) attempt.

DBML - notification_delivery

Table notification_delivery {
  id        uuid pk
  event_id  uuid [ref: > notification_event.id, not null]
  channel   text not null
  status    text not null   // queued, sent, failed, read
  sent_at   timestamptz
  read_at   timestamptz
  error     text
}

Relationships

From	To	Type
notification_template	notification_event	1:N
notification_template	notification_preference	1:N
notification_event	notification_delivery	1:N

Notes from the field

Index notification_event(recipient_id, created_at DESC) for the in-app inbox.
Index notification_delivery(status, sent_at) for retry workers.
Compose the event_key from "{template}:{actor}:{target}:{day}" to dedupe naturally.

Frequently asked

How do I prevent duplicate notifications?

Compose an event_key that captures the logical event (template + actor + target + optional time bucket). The UNIQUE constraint on notification_event.event_key rejects duplicates at insert time.

Should I have one delivery row per channel?

Yes. Splitting by channel keeps retry state per channel (an email can fail while in-app already succeeded).

More templates

FlowchartSaaS onboarding email flow FlowchartCustomer support runbook flow FlowchartPayment webhook retry and error flow FlowchartGitHub PR review and merge flow FlowchartLogin with magic link flow ArchitectureThree-tier SaaS architecture on AWS ArchitectureMicroservices architecture on Kubernetes ArchitectureReal-time data pipeline on GCP

Open this template in Raltey

Edit it on the canvas, ask the AI agent to extend it, then share or export.