Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/EdgarJr30/proyecto-de-grado-cms/llms.txt

Use this file to discover all available pages before exploring further.

​
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

Set secrets for the Edge Function:
npx supabase secrets set \
  VAPID_PUBLIC_KEY="BKxW8z..." \
  VAPID_PRIVATE_KEY="y3dK..." \
  VAPID_SUBJECT="mailto:admin@yourdomain.com" \
  --project-ref <your-project-ref>

​
Notification Categories

The system organizes notifications into five categories, each controllable via user preferences:
CategoryEvent TypesDefault
assignmentsticket.assigned, ticket.unassigned✓
commentsticket.comment_added✓
status_changesticket.status_changed, ticket.accepted, ticket.finalized, ticket.priority_changed, ticket.urgent_changed✓
deadlinesticket.deadline_set, ticket.deadline_changed, ticket.due_soon, ticket.overdue✓
admin_systemAll 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

Build docs developers (and LLMs) love

Get started for freeTalk to us