Notification System Setup

Complete setup guide for the MLM CMMS notification system including in-app notifications, push notifications, and VAPID configuration

Overview

The MLM CMMS notification system provides real-time delivery through two channels:

• In-App Notifications: Always-on notification center accessible from any page
• Push Notifications: Web Push API integration for native browser/OS notifications

The system uses a robust outbox pattern for reliable push delivery with retry logic, exponential backoff, and automatic subscription cleanup.

Architecture

1. Event Generation

   Ticket lifecycle triggers (create, assign, comment, status change) generate notification_events via database triggers.

2. Delivery Creation

   Events create notification_deliveries for each recipient, respecting user preferences and permission-based routing.

3. Outbox Processing

   Push-enabled deliveries enter notification_outbox for async processing by the Edge Function worker.

4. Push Delivery

   The send-push-from-outbox Edge Function claims pending rows, sends to subscribed endpoints, and updates status.

Database Setup

Prerequisites

• Supabase project with service role access
• SQL editor access for executing setup scripts
• pg_net extension enabled (for immediate webhook dispatch)

Execute SQL Modules

Run the core CMMS SQL modules in order. The notifications module is included in the standard bootstrap:

# From Supabase SQL Editor, execute in order:
sql/modules/core_cmms/00_extensions.sql
sql/modules/core_cmms/01_enums.sql
# ... (modules 02-15)
sql/modules/core_cmms/16_notifications.sql  # ← Notification system

The 16_notifications.sql module includes:

• Core notification tables (notification_events, notification_deliveries, notification_preferences)
• Push infrastructure (push_subscriptions, notification_outbox)
• Comments system (ticket_comments)
• Triggers for all ticket lifecycle events
• Admin test tools
• Concurrency-safe outbox claiming
• RLS policies and realtime configuration

Verify Realtime Configuration

Ensure these tables are published for realtime updates:

-- Check realtime publication
SELECT schemaname, tablename 
FROM pg_publication_tables 
WHERE pubname = 'supabase_realtime'
  AND tablename IN ('notification_deliveries', 'ticket_comments');

-- Verify replica identity (required for realtime)
SELECT schemaname, tablename, relreplident 
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relname IN ('notification_deliveries', 'ticket_comments')
  AND nspname = 'public';
-- Expected: relreplident = 'f' (FULL)

The SQL module sets this automatically, but verify in deployed environments:

ALTER TABLE public.notification_deliveries REPLICA IDENTITY FULL;
ALTER TABLE public.ticket_comments REPLICA IDENTITY FULL;

VAPID Keys Generation

VAPID keys are cryptographic credentials. Never commit private keys to version control.

Generate a VAPID key pair for Web Push authentication:

npx web-push generate-vapid-keys --json

Output example:

{
  "publicKey": "BKxW8z...",
  "privateKey": "y3dK..."
}

Store Keys Securely

Supabase Edge Function Secrets

Set secrets for the Edge Function:

npx supabase secrets set \
  VAPID_PUBLIC_KEY="BKxW8z..." \
  VAPID_PRIVATE_KEY="y3dK..." \
  VAPID_SUBJECT="mailto:[e-mail]" \
  --project-ref <your-project-ref>

Frontend Environment

Add the public key only to your frontend .env:

VITE_WEB_PUSH_PUBLIC_KEY=BKxW8z...

Only the public key is safe for client-side code. The private key must remain server-side.

Notification Categories

The system organizes notifications into five categories, each controllable via user preferences:

Category	Event Types	Default
assignments	ticket.assigned, ticket.unassigned	✓
comments	ticket.comment_added	✓
status_changes	ticket.status_changed, ticket.accepted, ticket.finalized, ticket.priority_changed, ticket.urgent_changed	✓
deadlines	ticket.deadline_set, ticket.deadline_changed, ticket.due_soon, ticket.overdue	✓
admin_system	All non-ticket events (e.g., system.test_notification)	✓

Users can disable categories individually via /notificaciones settings.

Channel Mask System

Notifications support multiple delivery channels via bitmask:

• Bit 0 (1): In-app notification center
• Bit 1 (2): Push notification
• Bit 2 (4+): Reserved for future channels (email, SMS, etc.)

Example:

-- In-app only
CREATE_NOTIFICATION_EVENT(..., channel_mask => 1)

-- In-app + push
CREATE_NOTIFICATION_EVENT(..., channel_mask => 3)

The system respects user preferences when resolving the final channel mask. See src/services/notificationCenterService.ts:115 for category-based filtering.

Recipient Routing

Recipients are determined by ticket relationships and permissions:

Ticket Creator

Always receives notifications for their own tickets (except when notify_actor=false)

Assigned Technicians

Users linked via work_order_assignees → assignees.user_id

Admin Permissions

Users with work_orders:full_access, work_orders:approve, or work_requests:full_access

Actor Exclusion

Event actor is excluded unless notify_actor=true or event type is ticket.created

See sql/modules/core_cmms/16_notifications.sql:183 for the get_ticket_recipient_user_ids function.

Testing the Setup

Smoke Test Script

Run the automated smoke test to verify end-to-end flow:

SUPABASE_URL=https://your-project.supabase.co \
SUPABASE_SERVICE_ROLE_KEY=your-service-role-key \
TEST_RECIPIENT_USER_ID=user-uuid \
TEST_ACTOR_USER_ID=actor-uuid \
node scripts/notifications-smoke.mjs

The script validates:

• Event creation
• Delivery generation
• Outbox entry creation
• Permission-based routing
• Category filtering

Manual UI Testing

1. Navigate to Notification Center

   Visit /notificaciones and verify the bell icon shows unread count

2. Create Test Notification

   Use the “Prueba Admin” button (requires users:full_access or rbac:manage_permissions)

3. Verify In-App Delivery

   Notification should appear immediately in the list with realtime updates

4. Test Read/Unread Toggle

   Click notification or swipe to toggle read status

5. Test Navigation

   Click a ticket notification to verify navigation to /tickets/:ticketId

Verify Database State

-- Check recent deliveries
SELECT 
  d.id,
  e.event_type,
  d.recipient_user_id,
  d.channel_mask,
  d.read_at,
  d.delivered_at,
  d.created_at
FROM notification_deliveries d
JOIN notification_events e ON e.id = d.event_id
ORDER BY d.created_at DESC
LIMIT 20;

-- Check outbox status
SELECT status, COUNT(*) 
FROM notification_outbox 
GROUP BY status;

-- Verify critical privileges
SELECT
  has_function_privilege('authenticated', 'public.create_notification_event(text,uuid,text,text,jsonb,uuid[],integer)', 'EXECUTE') AS auth_can_create,
  has_function_privilege('service_role', 'public.create_notification_event(text,uuid,text,text,jsonb,uuid[],integer)', 'EXECUTE') AS service_can_create,
  has_function_privilege('authenticated', 'public.send_self_test_notification(text,text,boolean)', 'EXECUTE') AS auth_can_test;

Next Steps

Deploy Edge Function

Set up the push notification worker for async delivery

Configure PWA

Enable service worker and configure push subscriptions