Notification architecture (Advanced)

Technical deep-dive: event queue, rules, dispatchers, and notification delivery for developers.

Notification system architecture

For technical users, integrators, and advanced admins. Understand how Teamwins dispatches notifications at scale.

---

System overview

Teamwins uses a multi-tier event-driven architecture for notifications:

`` ┌─────────────────────────────────────────────────────────┐ │ 1. APPLICATION CODE │ │ └─> Writes event to event_queue table │ └────────────────┬────────────────────────────────────────┘ │ ▼ ┌─────────────────────────────────────────────────────────┐ │ 2. EVENT QUEUE (Postgres table) │ │ - Stores all application events │ │ - Processed flag tracks dispatch status │ │ - Payload contains event data (JSON) │ └────────────────┬────────────────────────────────────────┘ │ ▼ ┌─────────────────────────────────────────────────────────┐ │ 3. NOTIFICATION RULES (Postgres table) │ │ - Defines when/how to notify │ │ - Instant vs digest │ │ - Audience targeting (user, admins, reviewers) │ │ - Template selection │ └────────────────┬────────────────────────────────────────┘ │ ▼ ┌─────────────────────────────────────────────────────────┐ │ 4. DISPATCHERS (Supabase Edge Functions) │ │ ├─> notify-dispatch-instant (cron: every minute) │ │ │ └─> Matches events to instant rules │ │ └─> notify-dispatch-digests (cron: hourly) │ │ └─> Aggregates events for digest sending │ └────────────────┬────────────────────────────────────────┘ │ ▼ ┌─────────────────────────────────────────────────────────┐ │ 5. DELIVERY CHANNELS │ │ ├─> In-app (notifications table + Realtime) │ │ └─> Email (email-send function → Resend API) │ └─────────────────────────────────────────────────────────┘ ``

---

Component 1: Event Queue

Table: event_queue

Schema: ``sql create table event_queue ( id uuid primary key, type text not null, -- e.g., 'assist.approval_required' payload jsonb not null, -- Event data occurred_at timestamp with time zone, processed boolean default false, created_at timestamp with time zone ); ``

Example event: ``json { "type": "assist.approval_required", "payload": { "approval_id": "abc123", "user_id": "user_xyz", "reviewer_email": "admin@company.com", "workspace_id": "ws_123", "assist_name": "Refer New Lead", "outcome_label": "Meeting Booked", "points": 80 }, "occurred_at": "2025-01-15T14:32:00Z", "processed": false } ``

Writing events: Application code inserts rows when actions occur: ``typescript await supabase.from('event_queue').insert({ type: 'assist.approval_required', payload: { approval_id, user_id, workspace_id, ... }, occurred_at: new Date().toISOString() }); ``

Processing:

Dispatchers query WHERE processed = false
After sending notifications, sets processed = true
Events retained indefinitely (audit trail)

---

Component 2: Notification Rules

Table: notification_rules

Schema: ``sql create table notification_rules ( id uuid primary key, key text unique not null, -- e.g., 'approval.new_request' mode text not null, -- 'instant' | 'daily_digest' | 'weekly_digest' trigger_type text not null, -- 'event' | 'daily' | 'weekly' | 'every_n_hours' trigger_value jsonb, -- Config: { event_type, time, timezone } audience text not null, -- 'user' | 'workspace_admins' | 'reviewers_of_pending' template_key text not null, -- Email template to render throttle jsonb, -- Rate limiting config min_items integer, -- Minimum events before sending digest enabled boolean default true ); ``

Example rule: ``json { "key": "approval.new_request", "mode": "instant", "trigger_type": "event", "trigger_value": { "event_type": "assist.approval_required" }, "audience": "reviewer", "template_key": "approvals.new_request", "throttle": null, "min_items": 1, "enabled": true } ``

Matching logic: 1. Dispatcher fetches enabled rules 2. Filters by mode (instant vs digest) 3. Matches event type to trigger_value 4. Resolves audience (who receives notification) 5. Renders template with event payload 6. Sends via channel

---

Component 3: Dispatchers

#### Dispatcher A: notify-dispatch-instant

Cron: Every 1 minute

Logic: ```typescript // 1. Fetch unprocessed events const events = await supabase .from('event_queue') .select('*') .eq('processed', false) .order('occurred_at', { ascending: true });

// 2. Fetch instant rules const rules = await supabase .from('notification_rules') .select('*') .eq('enabled', true) .eq('mode', 'instant') .eq('trigger_type', 'event');

// 3. Match events to rules for (const event of events) { const matchingRules = rules.filter(r => r.trigger_value.event_type === event.type );

for (const rule of matchingRules) { // 4. Resolve recipients const recipients = await resolveAudience(rule.audience, event.payload);

// 5. Render template const { subject, body } = await renderTemplate(rule.template_key, event.payload);

// 6. Send email await supabase.functions.invoke('email-send', { body: { to: recipients, subject, body } });

// 7. Create in-app notification await supabase.from('notifications').insert({ recipient_user_id: event.payload.user_id, type: event.type, title: subject, body, meta: event.payload }); }

// 8. Mark processed await supabase.from('event_queue') .update({ processed: true }) .eq('id', event.id); } ```

Audience resolution: ``typescript function resolveAudience(audience: string, payload: any): string[] { switch (audience) { case 'user': return [payload.user_email]; case 'reviewer': return [payload.reviewer_email]; case 'workspace_admins': return fetchAdminEmails(payload.workspace_id); case 'reviewers_of_pending': return fetchReviewerEmailsWithPending(payload.workspace_id); } } ``

---

#### Dispatcher B: notify-dispatch-digests

Cron: Every 1 hour

Logic: ```typescript // 1. Check time windows const now = new Date(); const is9AM = now.getHours() === 9; const isMonday = now.getDay() === 1;

// 2. Fetch digest rules const rules = await supabase .from('notification_rules') .select('*') .eq('enabled', true) .in('mode', ['daily_digest', 'weekly_digest']);

// 3. Filter rules by schedule match const activeRules = rules.filter(r => { if (r.mode === 'daily_digest' && is9AM) return true; if (r.mode === 'weekly_digest' && isMonday && is9AM) return true; return false; });

// 4. For each active digest rule for (const rule of activeRules) { // 5. Aggregate events per recipient const eventsByRecipient = await aggregateEventsByRecipient(rule);

for (const [recipient, events] of Object.entries(eventsByRecipient)) { // 6. Check min_items threshold if (events.length < rule.min_items) continue;

// 7. Render digest template const { subject, body } = await renderDigestTemplate(rule.template_key, { recipient, events, count: events.length });

// 8. Send digest email await supabase.functions.invoke('email-send', { body: { to: [recipient], subject, body } });

// 9. Mark all included events as processed await supabase.from('event_queue') .update({ processed: true }) .in('id', events.map(e => e.id)); } } ```

Aggregation example: ``typescript // Groups pending approval events by reviewer { "admin@company.com": [ { type: "assist.approval_required", payload: {...}, occurred_at: "..." }, { type: "assist.approval_required", payload: {...}, occurred_at: "..." }, { type: "assist.approval_required", payload: {...}, occurred_at: "..." } ] } ``

---

Component 4: In-App Notifications

Table: notifications

Schema: ``sql create table notifications ( id uuid primary key, workspace_id uuid not null, recipient_user_id uuid not null, type text not null, -- e.g., 'approval_request', 'points_awarded' title text not null, body text, meta jsonb, -- { approval_id, claim_id, points, etc. } is_read boolean default false, created_at timestamp with time zone ); ``

Realtime subscription: ``typescript // Client-side (React) useEffect(() => { const channel = supabase .channel('notifications') .on('postgres_changes', { event: 'INSERT', schema: 'public', table: 'notifications', filter: recipient_user_id=eq.${userId}` }, (payload) => { // New notification received setNotifications(prev => [payload.new, ...prev]); setBadgeCount(prev => prev + 1); toast.success(payload.new.title); }) .subscribe();

return () => supabase.removeChannel(channel); }, [userId]); ```

Fetching notifications: ```typescript // Service layer with caching (5 min TTL) export const NotificationService = { async getAllNotificationData(userId, workspaceId) { // 1. Fetch all notifications const { data: notifications } = await supabase .from('notifications') .select('*') .eq('workspace_id', workspaceId) .eq('recipient_user_id', userId) .order('created_at', { ascending: false });

// 2. Cross-check with approvals/claims (filter stale) const validNotifications = await filterStaleNotifications(notifications);

// 3. Deduplicate by thread (approval_id, claim_id) const deduplicated = deduplicateByThread(validNotifications);

// 4. Split into alerts vs inbox const alerts = deduplicated.filter(n => !n.is_read && isActionable(n.type)); const inbox = deduplicated;

return { alerts, inbox, unreadCount: alerts.length }; } }; ```

---

Component 5: Email Delivery

Email function: email-send

Provider: Resend API

Template rendering: ```typescript // Templates stored in email_templates table const template = await supabase .from('email_templates') .select('*') .eq('key', 'approvals.new_request') .single();

// Render with Handlebars or similar const subject = Handlebars.compile(template.subject)(payload); const body = Handlebars.compile(template.body)(payload);

// Send via Resend await fetch('https://api.resend.com/emails', { method: 'POST', headers: { 'Authorization': Bearer ${RESEND_API_KEY}, 'Content-Type': 'application/json' }, body: JSON.stringify({ from: 'noreply@teamwins.com', to: recipients, subject, html: body }) }); ```

Action links (JWT): ```typescript // Generate secure one-time-use token const token = jwt.sign({ action: 'approve', approval_id: 'abc123', reviewer_id: 'reviewer_xyz', exp: Math.floor(Date.now() / 1000) + (7 24 60 * 60) // 7 days }, JWT_SECRET);

const actionUrl = https://app.teamwins.com/api/approval/approve?token=${token};

// Include in email <a href="${actionUrl}" class="btn">Approve</a> ```

---

Throttling & Rate Limiting

Per-user throttle: ``json { "throttle": { "max_per_hour": 5, "max_per_day": 20 } } ``

Implementation: ```typescript // Check if user exceeded throttle const recentNotifications = await supabase .from('notifications') .select('id') .eq('recipient_user_id', userId) .eq('type', notificationType) .gte('created_at', oneHourAgo);

if (recentNotifications.length >= rule.throttle.max_per_hour) { console.log('Throttle exceeded, skipping notification'); return; } ```

Prevents spam from high-frequency events (e.g., 10 approval requests in 5 minutes).

---

Digest Batching

Min items threshold: ``json { "min_items": 3, "mode": "daily_digest" } ``

Logic:

Only send digest if ≥3 events accumulated
Prevents "You have 1 pending approval" emails
User gets single digest instead of 3 separate emails

Example digest: ``` Subject: You have 5 pending approvals

Hi Admin,

You have 5 pending approvals waiting for your review:

1. Alice Smith - Refer New Lead - Meeting Booked (+80 pts) 2. Bob Jones - Talent Referral - Candidate Hired (+100 pts) 3. Carol White - Post Share - 3k impressions (+25 pts) 4. David Brown - Account Intel - Strategy Shift (+150 pts) 5. Eve Davis - Partner Door-Opener - Partnership Signed (+2500 pts)

[Review All Approvals →]

Sent daily at 9am if you have pending approvals. ```

---

Monitoring & Debugging

Event queue health: ```sql -- Check unprocessed events SELECT COUNT(*) FROM event_queue WHERE processed = false;

-- Old unprocessed events (stuck) SELECT * FROM event_queue WHERE processed = false AND created_at < NOW() - INTERVAL '1 hour' ORDER BY created_at; ```

Notification delivery: ```sql -- Notification volume by type SELECT type, COUNT() FROM notifications WHERE created_at > NOW() - INTERVAL '1 day' GROUP BY type ORDER BY COUNT() DESC;

-- Unread notifications per user SELECT recipient_user_id, COUNT() FROM notifications WHERE is_read = false GROUP BY recipient_user_id ORDER BY COUNT() DESC; ```

Cron function logs: ```bash # View dispatcher logs npx supabase functions logs notify-dispatch-instant --tail

# Check for errors npx supabase functions logs notify-dispatch-instant --level error ```

---

Extending the system

Add new notification type:

1. Create event type: perk.low_inventory 2. Insert notification rule: ``sql INSERT INTO notification_rules (key, mode, trigger_type, trigger_value, audience, template_key) VALUES ('perk.low_inventory', 'instant', 'event', '{"event_type": "perk.low_inventory"}', 'workspace_admins', 'perks.low_inventory'); ` 3. Create email template in email_templates table 4. Application code writes event when inventory < 5: `typescript await supabase.from('event_queue').insert({ type: 'perk.low_inventory', payload: { perk_id, perk_name, remaining: 3, workspace_id } }); `` 5. Dispatcher automatically picks up and sends notifications

Add Slack channel:

1. Create notify-dispatch-slack function 2. Fetch events from event_queue 3. Match against rules with channel: 'slack' 4. Post to Slack webhook: ``typescript await fetch(SLACK_WEBHOOK_URL, { method: 'POST', body: JSON.stringify({ text: New approval request from ${payload.user_name}, blocks: [...] }) }); `` 5. Mark events as processed

---

Performance considerations

Event queue cleanup:

Processed events retained indefinitely (audit trail)
Consider archiving events older than 1 year
Partition table by month for large volumes

Realtime subscriptions:

Limit to 1 subscription per user
Unsubscribe on component unmount
Consider WebSocket connection pooling for 1000+ concurrent users

Digest aggregation:

Query optimization: Index on (processed, occurred_at) columns
Batch processing: Process 100 events at a time
Parallel dispatch: Send emails concurrently (Resend supports 100 req/s)

---

Security

Email action tokens:

JWT signed with secret key
7-day expiration
One-time use (mark as used after first click)
HTTPS only

RLS policies:

Users can only read their own notifications
Admins can read workspace notifications
Event queue not exposed to clients

Rate limiting:

Per-user throttle prevents spam
Cron functions have max runtime (10 min)
Email provider rate limits (Resend: 100 req/s)

---

FAQs

Why event queue instead of triggers? Decouples notification logic from transaction. Failed email doesn't rollback contribution insert.

Can I disable certain notification types? Yes—set enabled = false in notification_rules table. No UI yet.

How do I add custom templates? Insert into email_templates table with Handlebars syntax. Reference in notification rule.

What if Resend API is down? Events stay in queue with processed = false. Next cron run will retry.

How to test notifications locally? Use npx supabase functions serve notify-dispatch-instant and trigger events manually.

---