Notifications Database Schema

Database tables and functions for the notification system in MLM CMMS

Notifications Database Schema

The notification system in MLM CMMS is implemented through a set of database tables, functions, and triggers defined in sql/modules/core_cmms/16_notifications.sql.

Architecture Overview

The notification system uses a multi-stage pipeline:

1. Events → Create notification events with metadata
2. Deliveries → One delivery record per recipient (in-app)
3. Outbox → Push notification queue with retry logic
4. Subscriptions → Push endpoint registration per device

Core Tables

notification_events

Central event table storing notification metadata.

Column	Type	Description
id	uuid	Primary key
category	text	Category (assignments, comments, status_changes, deadlines, admin_system)
actor_user_id	uuid	User who triggered the event
title	text	Notification title
body	text	Notification body text
data	jsonb	Additional metadata (ticket_id, etc.)
channel_mask	integer	Delivery channels (1=in-app, 2=push, 3=both)
created_at	timestamptz	Event creation timestamp

Indexes:

• idx_notification_events_created_at on (created_at)
• idx_notification_events_category on (category)

Source: sql/modules/core_cmms/16_notifications.sql

notification_deliveries

Per-recipient delivery records for in-app notifications.

Column	Type	Description
id	uuid	Primary key
event_id	uuid	Foreign key to notification_events
recipient_user_id	uuid	Foreign key to auth.users
status	text	Delivery status (delivered, seen, read)
delivered_at	timestamptz	Delivery timestamp
seen_at	timestamptz	First seen timestamp
read_at	timestamptz	Marked as read timestamp

Indexes:

• idx_notification_deliveries_recipient on (recipient_user_id, status)
• idx_notification_deliveries_event on (event_id)

Unique Constraint: (event_id, recipient_user_id) to prevent duplicates

RLS Policies:

-- Users can only see their own notifications
CREATE POLICY "notification_deliveries_select_policy"
  ON public.notification_deliveries FOR SELECT
  USING (recipient_user_id = auth.uid());

-- Users can update their own notification status
CREATE POLICY "notification_deliveries_update_policy"
  ON public.notification_deliveries FOR UPDATE
  USING (recipient_user_id = auth.uid());

Source: sql/modules/core_cmms/16_notifications.sql

notification_outbox

Push notification queue with retry and deduplication.

Column	Type	Description
id	uuid	Primary key
event_id	uuid	Foreign key to notification_events
recipient_user_id	uuid	Foreign key to auth.users
subscription_id	uuid	Foreign key to notification_push_subscriptions
status	text	Status (pending, processing, sent, failed, cancelled)
attempts	integer	Retry attempt count
last_error	text	Last error message
next_attempt_at	timestamptz	Next retry timestamp
sent_at	timestamptz	Successful send timestamp
created_at	timestamptz	Entry creation timestamp
processing_lease_until	timestamptz	Claimed by worker until this time

Indexes:

• idx_notification_outbox_status_next_attempt on (status, next_attempt_at)
• idx_notification_outbox_recipient on (recipient_user_id)
• idx_notification_outbox_subscription on (subscription_id)

Unique Constraint: (event_id, subscription_id) for deduplication

Key Features:

• Deduplication: Same event + subscription = single push
• Retry logic: Exponential backoff on failure (attempts 1, 2, 3…)
• Lease mechanism: Workers claim rows with processing_lease_until to prevent concurrent processing
• Status tracking: pending → processing → sent/failed

Source: sql/modules/core_cmms/16_notifications.sql

notification_push_subscriptions

Push notification endpoint registration.

Column	Type	Description
id	uuid	Primary key
user_id	uuid	Foreign key to auth.users
endpoint	text	Push service endpoint URL
keys_p256dh	text	P256DH encryption key (base64)
keys_auth	text	Auth secret (base64)
user_agent	text	Browser/device user agent
created_at	timestamptz	Registration timestamp
last_used_at	timestamptz	Last successful push timestamp

Indexes:

• idx_notification_push_subscriptions_user on (user_id)

Unique Constraint: (user_id, endpoint) - one subscription per device per user

Source: sql/modules/core_cmms/16_notifications.sql

Key Functions

create_notification_event

Creates a notification event and triggers deliveries.

CREATE OR REPLACE FUNCTION public.create_notification_event(
  p_category text,
  p_actor_user_id uuid,
  p_title text,
  p_body text,
  p_data jsonb DEFAULT '{}'::jsonb,
  p_recipient_user_ids uuid[] DEFAULT NULL,
  p_channel_mask integer DEFAULT 3
)
RETURNS uuid

Parameters:

• p_category: One of assignments, comments, status_changes, deadlines, admin_system
• p_actor_user_id: User who performed the action
• p_title: Notification title (e.g., “New Assignment”)
• p_body: Notification body (e.g., “You have been assigned to Ticket #123”)
• p_data: JSON metadata (e.g., {"ticket_id": 123, "url": "/tickets/123"})
• p_recipient_user_ids: Array of user UUIDs to notify
• p_channel_mask: 1=in-app only, 2=push only, 3=both (default)

Returns: UUID of created event

Example Usage:

const { data, error } = await supabase.rpc('create_notification_event', {
  p_category: 'assignments',
  p_actor_user_id: currentUserId,
  p_title: 'New Assignment',
  p_body: `You have been assigned to Ticket #${ticketId}`,
  p_data: { ticket_id: ticketId, url: `/tickets/${ticketId}` },
  p_recipient_user_ids: [assigneeUserId],
  p_channel_mask: 3 // Both in-app and push
});

Trigger: After inserting event, handle_notification_event() trigger creates deliveries and outbox entries.

Source: sql/modules/core_cmms/16_notifications.sql

send_self_test_notification

Admin function to send a test notification to yourself.

CREATE OR REPLACE FUNCTION public.send_self_test_notification(
  p_title text DEFAULT 'Test Notification',
  p_body text DEFAULT 'This is a test notification',
  p_push_only boolean DEFAULT false
)
RETURNS uuid

Parameters:

• p_title: Test notification title
• p_body: Test notification body
• p_push_only: If true, only send push (no in-app)

Returns: UUID of created event

Usage: Test notification delivery and push configuration

Source: sql/modules/core_cmms/16_notifications.sql

Triggers

handle_notification_event

Automatically creates deliveries and outbox entries after event creation.

CREATE TRIGGER notification_event_trigger
  AFTER INSERT ON public.notification_events
  FOR EACH ROW
  EXECUTE FUNCTION public.handle_notification_event();

Logic:

1. Insert one notification_deliveries row per recipient
2. If channel_mask includes push (2 or 3), insert notification_outbox rows for each recipient’s active subscriptions
3. Deduplicate using UPSERT with ON CONFLICT DO NOTHING

Source: sql/modules/core_cmms/16_notifications.sql

handle_ticket_comment_added

Creates notification events when comments are added to tickets.

CREATE TRIGGER ticket_comment_notification_trigger
  AFTER INSERT ON public.ticket_comments
  FOR EACH ROW
  EXECUTE FUNCTION public.handle_ticket_comment_added();

Logic:

1. Determine recipients (ticket assignees, requester, previous commenters)
2. Call create_notification_event() with category=‘comments’
3. Include ticket_id and comment_id in data payload

Source: sql/modules/core_cmms/16_notifications.sql

Notification Categories

Category	Description	Typical Recipients
assignments	Work order assigned to technician	Assigned technicians
comments	New comment on ticket	Ticket assignees, requester, other commenters
status_changes	Ticket status changed	Assigned technicians, requester
deadlines	Deadline approaching or overdue	Assigned technicians, manager
admin_system	System announcements	All users or specific roles

Channel Mask

The channel_mask field controls delivery channels using bitwise flags:

Value	Binary	In-App	Push	Use Case
1	01	✅	❌	Low-priority, in-app only
2	10	❌	✅	Push only (mobile alerts)
3	11	✅	✅	Both (default)

Retry Logic

The outbox uses exponential backoff for failed push attempts:

Attempt 1: Immediate (next_attempt_at = now)
Attempt 2: +30 seconds
Attempt 3: +60 seconds
Attempt 4: +120 seconds (max backoff)
Attempt 5+: +120 seconds (max backoff)
Max attempts: 5 (configurable via Edge Function env var)

After max attempts, status changes to failed and retries stop.

Subscription Cleanup

The Edge Function automatically removes invalid subscriptions:

• HTTP 404/410: Endpoint no longer exists → delete subscription
• HTTP 401: Invalid credentials → delete subscription
• Successful push: Update last_used_at timestamp

Database Views

v_notification_feed

Denormalized view joining events and deliveries for efficient querying.

CREATE VIEW public.v_notification_feed AS
SELECT
  d.id AS delivery_id,
  d.recipient_user_id,
  d.status,
  d.delivered_at,
  d.seen_at,
  d.read_at,
  e.id AS event_id,
  e.category,
  e.actor_user_id,
  e.title,
  e.body,
  e.data,
  e.created_at
FROM public.notification_deliveries d
JOIN public.notification_events e ON d.event_id = e.id
ORDER BY e.created_at DESC;

Usage: Frontend notification center queries this view for user’s notifications

Source: sql/modules/core_cmms/16_notifications.sql

Admin Queries

Check outbox status

SELECT status, count(*)
FROM public.notification_outbox
GROUP BY status
ORDER BY status;

Recent failed pushes

SELECT id, recipient_user_id, attempts, last_error, created_at
FROM public.notification_outbox
WHERE status = 'failed'
ORDER BY created_at DESC
LIMIT 20;

User’s active subscriptions

SELECT id, endpoint, user_agent, created_at, last_used_at
FROM public.notification_push_subscriptions
WHERE user_id = 'user-uuid-here';

Notification delivery stats

SELECT
  e.category,
  COUNT(DISTINCT d.id) AS total_deliveries,
  COUNT(DISTINCT d.id) FILTER (WHERE d.status = 'read') AS read_count,
  COUNT(DISTINCT d.id) FILTER (WHERE d.status = 'seen') AS seen_count
FROM public.notification_events e
JOIN public.notification_deliveries d ON e.id = d.event_id
WHERE e.created_at > now() - interval '7 days'
GROUP BY e.category;

Security Considerations

Important:

• create_notification_event() is SECURITY DEFINER but blocked for client roles
• Only Edge Functions and triggers should call it
• RLS policies prevent users from seeing other users’ notifications
• Push subscription endpoints are user-scoped (RLS enforced)

Next Steps

Push Setup Guide

Configure push notifications

Edge Functions

Deploy the push worker

PWA Integration

Implement frontend push

Core Tables

Review main database schema