Design notification system architecture (in-app + email)

[eswan18]

Overview

We need a notification system that can handle both in-app notifications (shown in UI) and email notifications. This requires careful architectural planning before implementation.

Use Cases

Immediate Notifications

• User added to competition → notify the added user
• Prop resolved → notify all forecasters who predicted on it
• Competition ended → notify all members with final scores

Scheduled/Reminder Notifications

• Deadline approaching → "7 days left to forecast" / "1 day left"
• Weekly digest → summary of activity (optional, lower priority)

Channels

Channel	Characteristics
In-app	Stored in DB, shown in UI bell icon, marked as read, persisted
Email	Sent via email service, fire-and-forget or tracked, requires templates, may need queue

User Preferences (Future)

Users should eventually be able to control:

• Which notification types they receive
• Which channel(s) for each type (in-app only, email only, both, neither)

Requires a preferences table like:

notification_preferences (
  user_id,
  notification_type,  -- e.g., 'prop_resolved', 'deadline_reminder'
  in_app_enabled,     -- boolean
  email_enabled       -- boolean
)

Architectural Considerations

Why not just "action → INSERT notification"?

A simple approach where each action directly inserts a notification works for immediate, single-user notifications but breaks down for:

1. Fan-out scenarios: "Prop resolved" might notify 50 users. Inline inserts would slow down the resolve action.

2. Scheduled notifications: "7 days until deadline" can't be triggered by an action—it needs a scheduled job.

3. Batched/digest emails: "Weekly summary" aggregates multiple events into one notification.

4. Dual-channel delivery: If an event triggers both in-app AND email, they need coordination.

Recommended Architecture: Event-Driven

Action (e.g., resolveProp)
    ↓
Emit Event (e.g., { type: 'prop_resolved', propId, userIds })
    ↓
Event Handler / Job Queue
    ↓
    ├── Check user preferences
    ├── Create in-app notification(s)
    └── Queue email notification(s)

Benefits:

• Actions stay fast (just emit event)
• Fan-out handled asynchronously
• Preferences checked in one place
• Easy to add new notification types
• Scheduled jobs can emit the same events

Components Needed

1. Event types - Enum/constants for all notification triggers
2. app_notifications table - In-app notification storage
3. email_notifications table (or use email service tracking) - Email delivery tracking
4. notification_preferences table - User channel preferences
5. Event emitter - Could be simple function calls initially, or a proper queue (pg-boss, BullMQ) later
6. Notification service - Central logic for routing events to channels
7. Email service integration - Resend, SendGrid, or similar
8. Email templates - HTML/text templates for each notification type
9. Scheduled job runner - For deadline reminders, digests

Schema Sketch

app_notifications

CREATE TABLE app_notifications (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  type VARCHAR(50) NOT NULL,
  title VARCHAR(255) NOT NULL,
  message TEXT NOT NULL,
  read BOOLEAN NOT NULL DEFAULT FALSE,
  -- Optional references for deep linking
  competition_id INTEGER REFERENCES competitions(id) ON DELETE CASCADE,
  prop_id INTEGER REFERENCES props(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

notification_preferences

CREATE TABLE notification_preferences (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  notification_type VARCHAR(50) NOT NULL,
  in_app_enabled BOOLEAN NOT NULL DEFAULT TRUE,
  email_enabled BOOLEAN NOT NULL DEFAULT TRUE,
  UNIQUE(user_id, notification_type)
);

Notification Types (Initial)

type NotificationType =
  | 'competition_member_added'  // You were added to a competition
  | 'prop_resolved'             // A prop you forecasted was resolved
  | 'competition_ended'         // Competition finished, scores available
  | 'deadline_reminder_7d'      // 7 days until forecasting closes
  | 'deadline_reminder_1d';     // 1 day until forecasting closes

Implementation Phases

Phase 1: Foundation

• Design event type system
• Create app_notifications table
• Create notification_preferences table with sensible defaults
• Build notification service with preference checking
• Build UI (bell icon, dropdown, mark as read)

Phase 2: Email Infrastructure

• Choose and integrate email service (Resend recommended for Next.js)
• Create email templates
• Add email sending to notification service
• Consider job queue for reliability (pg-boss works well with Postgres)

Phase 3: Scheduled Notifications

• Set up scheduled job runner
• Implement deadline reminder logic
• Add jobs for checking upcoming deadlines

Phase 4: User Preferences UI

• Settings page for notification preferences
• Per-type toggles for in-app and email

Questions to Resolve

1. Job queue: pg-boss (Postgres-native) vs BullMQ (Redis) vs Vercel Cron + edge functions?
2. Email service: Resend vs SendGrid vs AWS SES?
3. Event persistence: Store events in DB for replay/debugging, or fire-and-forget?
4. Digest frequency: Daily? Weekly? User-configurable?

References

• Current member addition: lib/db_actions/competition-members.ts → addCompetitionMemberById()
• Prop resolution: lib/db_actions/props.ts → resolveProp()
• No existing email infrastructure in codebase
• Uses external IDP for auth (no self-hosted email for password reset)

---

This issue captures architectural planning from a discussion about adding "user added to competition" notifications. We decided to design the full system before building incrementally.