Database Schema

Complete PostgreSQL database schema documentation.

Overview

The Notification Service uses PostgreSQL 15+ as its primary data store for notification records, status tracking, and audit logs.

Database Diagram

┌─────────────────────────────────┐
│     notification_statuses       │
├─────────────────────────────────┤
│ id (PK)          SERIAL          │
│ name             VARCHAR(50)     │
└───────────┬─────────────────────┘
            │
            │ 1:N
            │
┌───────────▼─────────────────────┐
│        notifications            │
├─────────────────────────────────┤
│ id (PK)          UUID            │
│ userId           UUID            │
│ type             VARCHAR(50)     │
│ channel          VARCHAR(255)    │
│ subject          VARCHAR(500)    │
│ content          TEXT            │
│ statusId (FK)    INTEGER         │
│ errorMessage     TEXT            │
│ retryCount       INTEGER         │
│ metadata         JSONB           │
│ createdAt        TIMESTAMPTZ     │
│ updatedAt        TIMESTAMPTZ     │
│ sentAt           TIMESTAMPTZ     │
└─────────────────────────────────┘
Copy

Tables

notification_statuses

Enumeration table for notification status values.

Columns:

Data:

Status Lifecycle:

queued → sending → sent
  │         │
  │         └──► retrying → sending → sent
  │                  │
  └──────────────────┴──► failed
Copy

SQL:

CREATE TABLE IF NOT EXISTS notification_statuses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

INSERT INTO notification_statuses (name) VALUES
    ('queued'),
    ('sending'),
    ('sent'),
    ('failed'),
    ('retrying')
ON CONFLICT (name) DO NOTHING;
Copy

notifications

Primary table for notification records.

Columns:

Indexes:

Foreign Keys:

• statusId → notification_statuses(id)

Triggers:

1. update_updated_at - Automatically updates updatedAt on row modification
2. update_sent_at - Automatically sets sentAt when status changes to 'sent' (id=3)

SQL:

CREATE TABLE IF NOT EXISTS notifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    "userId" UUID NOT NULL,
    "type" VARCHAR(50) NOT NULL,
    channel VARCHAR(255) NOT NULL,
    subject VARCHAR(500) NOT NULL,
    content TEXT,
    "statusId" INTEGER NOT NULL DEFAULT 1,
    "errorMessage" TEXT,
    "retryCount" INTEGER DEFAULT 0,
    metadata JSONB,
    "createdAt" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    "updatedAt" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    "sentAt" TIMESTAMPTZ,
    
    CONSTRAINT fk_notifications_status
        FOREIGN KEY ("statusId")
        REFERENCES notification_statuses(id)
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_notifications_user_id 
    ON notifications("userId");
    
CREATE INDEX IF NOT EXISTS idx_notifications_status_id 
    ON notifications("statusId");
    
CREATE INDEX IF NOT EXISTS idx_notifications_created_at 
    ON notifications("createdAt");
    
CREATE INDEX IF NOT EXISTS idx_notifications_type 
    ON notifications("type");
    
CREATE INDEX IF NOT EXISTS idx_notifications_user_created 
    ON notifications("userId", "createdAt" DESC);

-- Trigger: Auto-update updatedAt
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW."updatedAt" = NOW();
    RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER update_notifications_updated_at
    BEFORE UPDATE ON notifications
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Trigger: Auto-set sentAt when status becomes 'sent'
CREATE OR REPLACE FUNCTION update_sent_at_on_status_change()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW."statusId" = 3 AND (OLD."statusId" IS NULL OR OLD."statusId" != 3) THEN
        NEW."sentAt" = NOW();
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER update_notifications_sent_at
    BEFORE UPDATE ON notifications
    FOR EACH ROW
    WHEN (NEW."statusId" = 3 AND (OLD."statusId" IS NULL OR OLD."statusId" != 3))
    EXECUTE FUNCTION update_sent_at_on_status_change();
Copy

Common Queries

Insert Notification

INSERT INTO notifications (
    "userId",
    "type",
    channel,
    subject,
    content,
    metadata
) VALUES (
    '123e4567-e89b-12d3-a456-426614174000',
    'email',
    'user@example.com',
    'Verify your email',
    'Click the link to verify...',
    '{"verificationLink": "https://example.com/verify?token=abc"}'::jsonb
) RETURNING *;
Copy

Get Notification by ID

SELECT 
    n.*,
    ns.name AS status
FROM notifications n
LEFT JOIN notification_statuses ns ON n."statusId" = ns.id
WHERE n.id = '123e4567-e89b-12d3-a456-426614174000';
Copy

Update Status (Atomic Claim for Processing)

-- Only update if currently in 'queued' or 'retrying' status
UPDATE notifications
SET "statusId" = 2  -- sending
WHERE id = '123e4567-e89b-12d3-a456-426614174000'
  AND "statusId" IN (1, 5)  -- queued or retrying
RETURNING id;

-- Returns row if claimed, empty if already claimed by another worker
Copy

Get User Notifications

SELECT *
FROM notifications
WHERE "userId" = '123e4567-e89b-12d3-a456-426614174000'
ORDER BY "createdAt" DESC
LIMIT 50 OFFSET 0;
Copy

Get User Statistics

SELECT 
    n."type",
    ns.name AS status,
    COUNT(*)::INT AS count
FROM notifications n
LEFT JOIN notification_statuses ns ON n."statusId" = ns.id
WHERE n."userId" = '123e4567-e89b-12d3-a456-426614174000'
GROUP BY n."type", ns.name;
Copy

Metadata Field

The metadata JSONB column stores flexible data that varies by notification type.

Example: Verification Email

{
  "verificationLink": "https://app.com/verify?token=abc123",
  "username": "john_doe",
  "expiresAt": "2026-02-07T10:00:00Z"
}
Copy

Example: Order Confirmation

{
  "orderId": "ORD-123456",
  "orderTotal": 99.99,
  "currency": "USD",
  "items": [
    {"name": "Product A", "quantity": 2},
    {"name": "Product B", "quantity": 1}
  ]
}
Copy

Querying JSONB:

-- Find notifications with specific metadata key
SELECT * FROM notifications
WHERE metadata ? 'orderId';

-- Find by metadata value
SELECT * FROM notifications
WHERE metadata->>'orderId' = 'ORD-123456';

-- Index for JSONB queries (optional)
CREATE INDEX idx_notifications_metadata_gin 
    ON notifications USING GIN (metadata);
Copy

Performance Considerations

Connection Pooling

Default pool size: 10 connections

// config/database.ts
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000
});
Copy

Query Optimization

• Use prepared statements for repeated queries
• Indexes on all frequently filtered columns
• Composite index for common multi-column queries
• EXPLAIN ANALYZE for slow queries

Partitioning (Future)

For high-volume deployments (>10M notifications):

-- Partition by month
CREATE TABLE notifications_2026_02 PARTITION OF notifications
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
Copy

Archival Strategy

Move old notifications to archive table:

-- Archive notifications older than 90 days
INSERT INTO notifications_archive
SELECT * FROM notifications
WHERE "createdAt" < NOW() - INTERVAL '90 days';

DELETE FROM notifications
WHERE "createdAt" < NOW() - INTERVAL '90 days';
Copy

Migrations

Migration Files

Migrations are SQL files in migrations/ directory:

migrations/
├── 01_create_notifications_table.sql
└── 02_create_notification_statuses_table.sql
Copy

Running Migrations

# Using psql
psql $DATABASE_URL -f migrations/01_create_notifications_table.sql
psql $DATABASE_URL -f migrations/02_create_notification_statuses_table.sql

# Or via docker-compose (automatic on startup)
docker-compose up -d db
Copy

Migration Best Practices

• Migrations are idempotent (use IF NOT EXISTS)
• Each migration in separate file
• Numbered sequentially
• Never modify existing migrations
• Test migrations on copy of production data

Backup and Recovery

Backup

# Full database dump
pg_dump $DATABASE_URL > backup.sql

# Schema only
pg_dump --schema-only $DATABASE_URL > schema.sql

# Data only
pg_dump --data-only $DATABASE_URL > data.sql

# Specific table
pg_dump -t notifications $DATABASE_URL > notifications.sql
Copy

Restore

# Full restore
psql $DATABASE_URL < backup.sql

# Specific table
psql $DATABASE_URL < notifications.sql
Copy

Automated Backups

# Daily backup script
#!/bin/bash
BACKUP_DIR="/backups"
DATE=$(date +%Y-%m-%d)
pg_dump $DATABASE_URL | gzip > "$BACKUP_DIR/backup-$DATE.sql.gz"

# Keep last 30 days
find $BACKUP_DIR -name "backup-*.sql.gz" -mtime +30 -delete
Copy

Next Steps

• Message Queue - RabbitMQ configuration
• API Design - REST API structure
• Deployment - Production deployment