Database Functions

Stored procedures, triggers, and RPCs in the MLM CMMS database

Database Functions and Triggers

This page documents the key database functions and triggers defined in sql/modules/core_cmms/04_functions_triggers.sql.

Permission Functions

me_has_permission

Checks if the current authenticated user has a specific permission.

CREATE OR REPLACE FUNCTION public.me_has_permission(perm_code text)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1
    FROM public.user_roles ur
    JOIN public.role_permissions rp ON ur.role_id = rp.role_id
    JOIN public.permissions p ON rp.permission_id = p.id
    WHERE ur.user_id = auth.uid()
      AND p.code = perm_code
      AND p.is_active = true
  );
END;
$$;

Usage: Called in RLS policies to enforce permission-based access control.

Source: sql/modules/core_cmms/04_functions_triggers.sql

Role and Permission Management

set_role_permissions

Sets all permissions for a role (replaces existing permissions).

CREATE OR REPLACE FUNCTION public.set_role_permissions(
  p_role_id int,
  p_permission_ids uuid[]
)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
BEGIN
  -- Delete existing permissions
  DELETE FROM public.role_permissions WHERE role_id = p_role_id;
  
  -- Insert new permissions
  INSERT INTO public.role_permissions (role_id, permission_id)
  SELECT p_role_id, unnest(p_permission_ids);
END;
$$;

Parameters:

• p_role_id: Role ID to update
• p_permission_ids: Array of permission UUIDs to assign

Source: sql/modules/core_cmms/04_functions_triggers.sql

sync_permissions_from_registry

Synchronizes permissions table with the frontend permission registry.

CREATE OR REPLACE FUNCTION public.sync_permissions_from_registry(
  p_permissions jsonb
)
RETURNS integer
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
DECLARE
  v_count integer := 0;
BEGIN
  -- Upsert permissions from registry
  INSERT INTO public.permissions (code, resource, action, label, description, is_active)
  SELECT
    p->>'code',
    p->>'resource',
    (p->>'action')::permission_action,
    p->>'label',
    p->>'description',
    true
  FROM jsonb_array_elements(p_permissions) p
  ON CONFLICT (code) DO UPDATE SET
    resource = EXCLUDED.resource,
    action = EXCLUDED.action,
    label = EXCLUDED.label,
    description = EXCLUDED.description,
    is_active = EXCLUDED.is_active;
    
  GET DIAGNOSTICS v_count = ROW_COUNT;
  RETURN v_count;
END;
$$;

Parameters:

• p_permissions: JSONB array of permission definitions from frontend

Returns: Count of permissions synchronized

Source: sql/modules/core_cmms/04_functions_triggers.sql

User Management Functions

update_user_password

Admin function to reset a user’s password.

CREATE OR REPLACE FUNCTION public.update_user_password(
  p_user_id uuid,
  p_new_password text
)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, auth, pg_temp
AS $$
BEGIN
  -- Update auth.users password
  UPDATE auth.users
  SET encrypted_password = crypt(p_new_password, gen_salt('bf'))
  WHERE id = p_user_id;
  
  -- Record password reset in public.users
  UPDATE public.users
  SET password_reset_at = now(),
      password_reset_by = auth.uid()
  WHERE id = p_user_id;
END;
$$;

Parameters:

• p_user_id: UUID of user to update
• p_new_password: New plaintext password (will be hashed)

Security: SECURITY DEFINER - runs with elevated privileges. Access controlled by RLS.

Source: sql/modules/core_cmms/04_functions_triggers.sql

Ticket Functions

handle_ticket_updated

Trigger function that updates the updated_at timestamp on ticket changes.

CREATE OR REPLACE FUNCTION public.handle_ticket_updated()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER ticket_updated_trigger
  BEFORE UPDATE ON public.tickets
  FOR EACH ROW
  EXECUTE FUNCTION public.handle_ticket_updated();

Source: sql/modules/core_cmms/04_functions_triggers.sql

Notification Functions

create_notification_event

Creates a notification event that triggers notification 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
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
DECLARE
  v_event_id uuid;
BEGIN
  -- Insert notification event
  INSERT INTO public.notification_events (
    category, actor_user_id, title, body, data, channel_mask
  ) VALUES (
    p_category, p_actor_user_id, p_title, p_body, p_data, p_channel_mask
  ) RETURNING id INTO v_event_id;
  
  -- Create deliveries for each recipient
  INSERT INTO public.notification_deliveries (
    event_id, recipient_user_id, status
  )
  SELECT v_event_id, unnest(p_recipient_user_ids), 'delivered';
  
  RETURN v_event_id;
END;
$$;

Parameters:

• p_category: Notification category (assignments, comments, status_changes, etc.)
• p_actor_user_id: User who triggered the notification
• p_title: Notification title
• p_body: Notification body text
• p_data: Additional JSON data
• p_recipient_user_ids: Array of recipient user UUIDs
• p_channel_mask: Delivery channels (1=in-app, 2=push, 3=both)

Returns: UUID of created notification event

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

For more notification functions, see Notifications Database Schema.

View Definitions

v_tickets_compat

Compatibility view for reporting, joins tickets with location and assignee data.

CREATE OR REPLACE VIEW public.v_tickets_compat AS
SELECT
  t.*,
  l.name AS location_name,
  a.name || ' ' || a.last_name AS assignee_name
FROM public.tickets t
LEFT JOIN public.locations l ON t.location_id = l.id
LEFT JOIN public.assignees a ON t.assignee = a.name;

Usage: Used by report service for efficient querying

Source: sql/modules/core_cmms/06_views.sql

Trigger Summary

Trigger	Table	Event	Function	Purpose
ticket_updated_trigger	tickets	BEFORE UPDATE	handle_ticket_updated	Update timestamp
user_updated_trigger	users	BEFORE UPDATE	handle_user_updated	Update timestamp
assignee_updated_trigger	assignees	BEFORE UPDATE	handle_assignee_updated	Update timestamp
notification_event_trigger	notification_events	AFTER INSERT	handle_notification_event	Create deliveries and outbox entries

Function Security

All SECURITY DEFINER functions run with elevated privileges. Access is controlled through:

• RLS policies on the functions themselves
• Permission checks within function logic
• Explicit search_path settings to prevent SQL injection

Best Practices

Use RPC Wrappers

Always call functions through Supabase RPC API, never construct raw SQL

Check Permissions

Verify user permissions before calling admin functions

Handle Errors

Functions may raise exceptions - implement proper error handling

Test Thoroughly

Test functions with different user roles and edge cases

Next Steps

Core Tables

Review the database schema

RLS Policies

Understand row-level security

RBAC System

Learn about the permission system