Building a Scalable User Notification System: Architecture and Database Design

Multi-channel notification systems fail when teams model them as template-plus-send pipelines. A notification is a per-user routing decision: it may fan out, coalesce, suppress, or defer across email, SMS, push, and in-app channels. Without an explicit router layer, delivery guarantees collapse, user preferences get ignored, and compliance auditability becomes impossible.

The default worth starting from is an event-driven pipeline with a dedicated channel router. Producers emit events, the engine resolves preferences and rate limits, and the router owns every channel-specific decision. Three supporting pieces cannot be skipped: a PostgreSQL schema that separates events from delivery attempts, preference resolution with timezone-aware quiet hours, and a correlation ID on every record so failures stay debuggable before they reach the user.

The Hidden Complexity of “Simple” Notifications

The initial mental model for notifications is: trigger event → send message → done. The production reality is a complex orchestration of user preferences, delivery channels, rate limiting, retry logic, template management, analytics tracking, and regulatory compliance.

The complexity becomes apparent during a first major product launch. With 10,000 users suddenly receiving welcome emails, password resets, and activity notifications simultaneously, the email service starts throttling, the database connection pool maxes out, and users start complaining about duplicate notifications.

System Architecture

The architecture below separates event intake, policy resolution, and channel delivery. Each component maps to a distinct failure mode: the event bus absorbs bursts, the preference manager and rate limiter reject work before it reaches a provider, and the router keeps a failure in one channel from touching the others.

Event-Driven Architecture

Notifications are not request-response operations. They are fire-and-forget events that require asynchronous processing. The following event structure works reliably across multiple systems:

interface NotificationEvent {
  id: string;
  userId: string;
  type: NotificationType;
  templateId?: string;
  data: Record<string, any>;
  priority: 'low' | 'normal' | 'high' | 'critical';
  scheduledAt?: Date;
  expiresAt?: Date;
  metadata: {
    source: string;
    correlationId: string;
    retryCount: number;
    maxRetries: number;
  };
}

enum NotificationType {
  PROJECT_UPDATE = 'project_update',
  SECURITY_ALERT = 'security_alert', 
  FEATURE_ANNOUNCEMENT = 'feature_announcement',
  SYSTEM_MAINTENANCE = 'system_maintenance',
  USER_ACTIVITY = 'user_activity',
  INTEGRATION_UPDATE = 'integration_update'
}

The metadata section is crucial: the correlation ID enables tracing notification flows across distributed systems and is essential for debugging delivery failures.

The Notification Engine: Heart of the System

The notification engine is where most of the complexity lives. In the implementation below, the order of the checks matters as much as the checks themselves: cheap rejections happen before any template is rendered or any provider is called.

class NotificationEngine {
  constructor(
    private eventBus: EventBus,
    private templateService: TemplateService,
    private preferenceManager: PreferenceManager,
    private rateLimiter: RateLimiter,
    private channelRouter: ChannelRouter,
    private analytics: AnalyticsService
  ) {}

  async processEvent(event: NotificationEvent): Promise<void> {
    try {
      // Check if user exists and is active
      const user = await this.getUserWithPreferences(event.userId);
      if (!user?.isActive) {
        await this.analytics.trackSkipped(event.id, 'user_inactive');
        return;
      }

      // Apply user preferences filtering
      const enabledChannels = await this.preferenceManager
        .getEnabledChannels(event.userId, event.type);
      
      if (enabledChannels.length === 0) {
        await this.analytics.trackSkipped(event.id, 'all_channels_disabled');
        return;
      }

      // Rate limiting check
      const rateLimitResult = await this.rateLimiter
        .checkLimits(event.userId, event.type);
      
      if (!rateLimitResult.allowed) {
        await this.scheduleRetry(event, rateLimitResult.retryAfter);
        return;
      }

      // Process each enabled channel
      const deliveryPromises = enabledChannels.map(channel => 
        this.processChannel(event, channel, user)
      );

      const results = await Promise.allSettled(deliveryPromises);
      await this.analytics.trackDeliveryResults(event.id, results);

    } catch (error) {
      await this.handleProcessingError(event, error);
    }
  }

  private async processChannel(
    event: NotificationEvent,
    channel: NotificationChannel,
    user: User
  ): Promise<DeliveryResult> {
    // Template rendering with user data
    const template = await this.templateService.getTemplate(
      event.type,
      channel,
      user.locale
    );

    const renderedContent = await this.templateService.render(
      template,
      { ...event.data, user }
    );

    // Route to appropriate channel handler
    return await this.channelRouter.deliver(
      channel,
      user,
      renderedContent,
      event.metadata
    );
  }
}

Promise.allSettled is deliberate. A failing channel must not cancel the others, and each channel result is recorded separately, so a bounced email and a delivered push for the same event stay distinguishable in the delivery table.

Database Design: Schema and Indexing Strategy

The schema splits into three concerns: what to send (events and templates), who wants it (preferences), and what happened (deliveries and metrics). Keeping the third separate from the first is what makes per-channel retries and audit queries cheap.

Core Tables

-- Users table (assuming it exists)
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    phone VARCHAR(20),
    locale VARCHAR(10) DEFAULT 'en',
    timezone VARCHAR(50) DEFAULT 'UTC',
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- The preference system - this gets complex fast
CREATE TABLE notification_preferences (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    notification_type VARCHAR(100) NOT NULL,
    channel VARCHAR(50) NOT NULL,
    enabled BOOLEAN DEFAULT true,
    frequency VARCHAR(20) DEFAULT 'immediate', -- immediate, daily, weekly
    quiet_hours_start TIME DEFAULT '22:00:00',
    quiet_hours_end TIME DEFAULT '08:00:00',
    metadata JSONB DEFAULT '{}', -- for channel-specific settings
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(user_id, notification_type, channel)
);

-- Template management - localization is crucial
CREATE TABLE notification_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    notification_type VARCHAR(100) NOT NULL,
    channel VARCHAR(50) NOT NULL,
    locale VARCHAR(10) DEFAULT 'en',
    subject VARCHAR(500),
    body TEXT NOT NULL,
    variables JSONB DEFAULT '{}', -- expected variables
    is_active BOOLEAN DEFAULT true,
    version INTEGER DEFAULT 1,
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(notification_type, channel, locale, version)
);

Event Storage and Tracking

Event storage is the area most prone to scaling surprises. The schema below addresses the most common failure modes:

-- Main event table - this gets HUGE
CREATE TABLE notification_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    notification_type VARCHAR(100) NOT NULL,
    template_id UUID REFERENCES notification_templates(id),
    priority VARCHAR(20) DEFAULT 'normal',
    data JSONB DEFAULT '{}',
    scheduled_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE,
    status VARCHAR(20) DEFAULT 'pending',
    processed_at TIMESTAMP WITH TIME ZONE,
    correlation_id VARCHAR(255), -- for tracing
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- PostgreSQL has no inline INDEX clause; indexes are separate statements
CREATE INDEX idx_notification_events_correlation
ON notification_events(correlation_id);

-- Delivery tracking - separate for performance
CREATE TABLE notification_deliveries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    event_id UUID REFERENCES notification_events(id) ON DELETE CASCADE,
    channel VARCHAR(50) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending', -- pending, sent, delivered, failed, bounced
    attempt_count INTEGER DEFAULT 0,
    max_attempts INTEGER DEFAULT 3,
    next_retry_at TIMESTAMP WITH TIME ZONE,
    sent_at TIMESTAMP WITH TIME ZONE,
    delivered_at TIMESTAMP WITH TIME ZONE,
    failed_at TIMESTAMP WITH TIME ZONE,
    error_code VARCHAR(50),
    error_message TEXT,
    provider_id VARCHAR(255), -- external provider message ID
    provider_response JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_deliveries_event_channel
ON notification_deliveries(event_id, channel);

-- Analytics aggregation table - direct queries time out at millions of events
CREATE TABLE notification_metrics (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    date DATE NOT NULL,
    hour SMALLINT NOT NULL, -- 0-23
    notification_type VARCHAR(100) NOT NULL,
    channel VARCHAR(50) NOT NULL,
    status VARCHAR(20) NOT NULL,
    count INTEGER DEFAULT 1,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(date, hour, notification_type, channel, status)
);

Indexing Strategy

The indexes below follow the three query patterns the system runs most: user timelines, the processing queue, and the retry queue.

-- Critical indexes based on query patterns
CREATE INDEX idx_events_user_type_created 
ON notification_events(user_id, notification_type, created_at DESC);

CREATE INDEX idx_events_processing_queue 
ON notification_events(status, scheduled_at) 
WHERE status IN ('pending', 'retry');

CREATE INDEX idx_deliveries_retry_queue 
ON notification_deliveries(next_retry_at, status) 
WHERE status = 'pending' AND next_retry_at IS NOT NULL;

-- Append-only history: BRIN stays small where a btree would not
CREATE INDEX idx_events_created_brin
ON notification_events USING BRIN (created_at);

-- For analytics queries
CREATE INDEX idx_metrics_time_type 
ON notification_metrics(date, hour, notification_type);

Two details decide whether these indexes hold up. A partial index predicate has to be immutable, so a rolling window such as NOW() - INTERVAL '7 days' is rejected outright; time-ordered history belongs in a BRIN index instead. The queue indexes stay small only because their WHERE clauses exclude rows that already reached a terminal status.

User Preference Management

User preferences look like a boolean until quiet hours, timezones, and frequency windows enter the picture. The resolver below applies them in a fixed order, so the same event always produces the same channel set:

class PreferenceManager {
  async getEnabledChannels(
    userId: string, 
    notificationType: string
  ): Promise<NotificationChannel[]> {
    
    // Check global user preferences
    const userPrefs = await this.db.query(`
      SELECT np.channel, np.enabled, np.frequency, 
             np.quiet_hours_start, np.quiet_hours_end,
             u.timezone
      FROM notification_preferences np
      JOIN users u ON u.id = np.user_id
      WHERE np.user_id = $1 AND np.notification_type = $2
    `, [userId, notificationType]);

    if (userPrefs.length === 0) {
      // Use default preferences for this notification type
      return this.getDefaultChannels(notificationType);
    }

    const currentTime = new Date();
    const enabledChannels: NotificationChannel[] = [];

    for (const pref of userPrefs) {
      if (!pref.enabled) continue;

      // Check quiet hours
      if (this.isInQuietHours(currentTime, pref)) {
        // Check if this is a critical notification that overrides quiet hours
        if (!this.isCriticalNotification(notificationType)) {
          continue;
        }
      }

      // Check frequency preferences
      if (!this.shouldSendBasedOnFrequency(userId, pref.frequency, notificationType)) {
        continue;
      }

      enabledChannels.push(pref.channel as NotificationChannel);
    }

    return enabledChannels;
  }

  private isInQuietHours(currentTime: Date, pref: any): boolean {
    // Convert current time to user's timezone
    const userTime = moment(currentTime)
      .tz(pref.timezone || 'UTC')
      .format('HH:mm:ss');

    const quietStart = pref.quiet_hours_start;
    const quietEnd = pref.quiet_hours_end;

    // Handle quiet hours that cross midnight
    if (quietStart > quietEnd) {
      return userTime >= quietStart || userTime <= quietEnd;
    }

    return userTime >= quietStart && userTime <= quietEnd;
  }
}

The midnight-crossing branch is the part most implementations miss. When quiet_hours_start is later than quiet_hours_end, the comparison has to use OR rather than AND. Storing the user’s timezone alongside the preference row keeps that check inside a single query.

Template System: Localization and Personalization

Templates decide what the user actually reads, so lookup has to degrade predictably. The service below resolves the requested locale first and falls back to English rather than throwing:

interface Template {
  id: string;
  name: string;
  type: string;
  channel: string;
  locale: string;
  subject?: string;
  body: string;
  variables: Record<string, TemplateVariable>;
  abTest?: ABTestConfig;
}

class TemplateService {
  async getTemplate(
    notificationType: string,
    channel: NotificationChannel,
    locale: string = 'en'
  ): Promise<Template> {
    
    // Try to get localized template first
    let template = await this.db.findTemplate({
      type: notificationType,
      channel,
      locale,
      isActive: true
    });

    // Fallback to English if no localized version
    if (!template && locale !== 'en') {
      template = await this.db.findTemplate({
        type: notificationType,
        channel,
        locale: 'en',
        isActive: true
      });
    }

    if (!template) {
      throw new Error(`No template found for ${notificationType}/${channel}/${locale}`);
    }

    return template;
  }

  async render(template: Template, data: Record<string, any>): Promise<RenderedContent> {
    try {
      // Validate required variables
      await this.validateTemplateData(template, data);

      // Process template with Handlebars or similar
      const subject = template.subject 
        ? await this.renderString(template.subject, data)
        : undefined;

      const body = await this.renderString(template.body, data);

      return {
        subject,
        body,
        templateId: template.id,
        locale: template.locale
      };

    } catch (error) {
      // Log template rendering errors for debugging
      await this.logger.error('Template rendering failed', {
        templateId: template.id,
        error: error.message,
        data: this.sanitizeDataForLogging(data)
      });
      
      throw new TemplateRenderError(`Failed to render template ${template.id}`, error);
    }
  }
}

Rate Limiting: Protecting Users and Providers

Rate limiting balances user experience with system stability. The following implementation covers per-user, per-type limits with atomic Redis checks:

interface RateLimitConfig {
  notificationType: string;
  channel: string;
  limits: {
    perMinute: number;
    perHour: number;
    perDay: number;
  };
  burstAllowance: number;
}

class RateLimiter {
  constructor(private redis: Redis, private configs: RateLimitConfig[]) {}

  async checkLimits(
    userId: string, 
    notificationType: string
  ): Promise<RateLimitResult> {
    
    const config = this.getConfig(notificationType);
    if (!config) {
      return { allowed: true, remainingToday: Infinity };
    }

    const now = Date.now();
    const keys = {
      minute: `rate_limit:${userId}:${notificationType}:${Math.floor(now / 60000)}`,
      hour: `rate_limit:${userId}:${notificationType}:${Math.floor(now / 3600000)}`,
      day: `rate_limit:${userId}:${notificationType}:${Math.floor(now / 86400000)}`
    };

    // Use Redis pipeline for atomic checks
    const pipeline = this.redis.pipeline();
    pipeline.incr(keys.minute);
    pipeline.expire(keys.minute, 60);
    pipeline.incr(keys.hour);
    pipeline.expire(keys.hour, 3600);
    pipeline.incr(keys.day);
    pipeline.expire(keys.day, 86400);

    const results = await pipeline.exec();
    const counts = {
      minute: results[0][1] as number,
      hour: results[2][1] as number,
      day: results[4][1] as number
    };

    // Check against limits
    if (counts.minute > config.limits.perMinute ||
        counts.hour > config.limits.perHour ||
        counts.day > config.limits.perDay) {
      
      return {
        allowed: false,
        retryAfter: this.calculateRetryAfter(counts, config),
        remainingToday: Math.max(0, config.limits.perDay - counts.day)
      };
    }

    return {
      allowed: true,
      remainingToday: config.limits.perDay - counts.day
    };
  }
}

Constraints to Design For

Four constraints shape the parts that sit outside the code above. Each is cheaper to design for now than to retrofit:

1. Idempotency: Every notification operation should be idempotent, keyed on the event ID plus the channel. Users notice duplicates more acutely than missing notifications.

2. Observability: Correlation IDs and per-delivery error records are what make a failed send explainable after the fact. Adding them later means backfilling rows that no longer exist.

3. Channel isolation: A notification engine that grows into a monolith becomes hard to scale. Each channel should be independently deployable, so a slow SMS provider cannot delay in-app delivery.

4. Data retention: The events and deliveries tables grow in proportion to traffic, so a retention and archiving policy has to ship with the first migration. Retrofitting one later means deleting rows that an audit query may still need.

This shape earns its cost when notifications cross more than two channels, or when preferences and compliance rules vary per user. A product that sends transactional email and nothing else needs neither a router nor a deliveries table; a provider SDK plus a retry queue is enough. Add a second channel, or a support request asking why one specific user never received one specific message, and the router plus per-delivery records become the cheaper option.

The next part covers real-time delivery: WebSocket connections, push notifications, the channel-specific implementations, and the retry logic and circuit breakers that keep one failing provider from stalling the whole pipeline.

References

• What is Amazon SNS? - AWS Documentation - Overview of Amazon Simple Notification Service as the pub/sub backbone for multi-channel notification fanout architecture
• Common Amazon SNS scenarios - AWS Documentation - Fanout, mobile push, and application-to-application messaging patterns that underpin scalable notification architectures
• Using dead-letter queues in Amazon SQS - AWS Documentation - SQS dead-letter queue configuration for isolating and replaying failed notification deliveries
• Firebase Cloud Messaging - firebase.google.com - Official FCM documentation covering multi-platform push notification delivery for Android, iOS, and web
• Sending notification requests to APNs - Apple Developer - Apple’s official guide for transmitting push notification payloads to iOS and macOS devices via APNs
• CREATE INDEX - PostgreSQL Documentation - Index creation syntax, including the immutability requirement for partial index predicates
• BRIN Indexes - PostgreSQL Documentation - Block range indexes for naturally ordered columns such as append-only timestamp history