Database Setup

Overview

The MLM CMMS database is structured using modular SQL scripts that must be executed in a specific order. The database setup includes core CMMS functionality, RBAC (Role-Based Access Control), notifications, assets management, and optional inventory modules.

Execute all SQL scripts in the exact order specified. Dependencies between modules can cause errors if executed out of sequence.

Module Structure

The SQL modules are organized under sql/modules/:

core_cmms

Core CMMS functionality including RBAC, tickets, work orders, RLS policies, storage, and notifications

assets

Fixed assets management and preventive maintenance scheduling

inventory

Inventory and spare parts management (optional, isolated module)

Execution Order

Phase 1: Core CMMS Module

Execute these scripts in order through the Supabase SQL Editor:

Extensions

File: sql/modules/core_cmms/00_extensions.sqlInstalls required PostgreSQL extensions:

create extension if not exists pg_trgm;
create extension if not exists unaccent;
create extension if not exists pgcrypto;
create extension if not exists pg_cron;

pg_trgm: Trigram-based text search
unaccent: Remove accents for search normalization
pgcrypto: Cryptographic functions
pg_cron: Scheduled job execution

Enums

File: sql/modules/core_cmms/01_enums.sqlCreates enum types:

priority_enum: Ticket priorities (Baja, Media, Alta)
assignee_section_enum: Assignee categories
assignee_role_enum: Primary/Secondary roles

Permission Actions

File: sql/modules/core_cmms/02_permission_action.sqlDefines permission action enum for RBAC system.

Tables

File: sql/modules/core_cmms/03_tables.sqlCreates core database tables:

users: User profiles linked to auth.users
roles: User roles
permissions: Permission definitions
role_permissions: Role-permission mappings
user_roles: User-role assignments
tickets: Maintenance tickets/work requests
assignees: Maintenance personnel
locations: Service locations
societies: Company/organization entities
work_order_assignees: Work order assignments
special_incidents: Incident categories
app_settings: Application configuration
announcements: System announcements

Functions & Triggers

File: sql/modules/core_cmms/04_functions_triggers.sqlImplements business logic functions and automated triggers.

Foreign Keys

File: sql/modules/core_cmms/05_fk.sqlEstablishes referential integrity constraints between tables.

Views

File: sql/modules/core_cmms/06_views.sqlCreates database views for simplified data access.

Indexes

File: sql/modules/core_cmms/07_indexes.sqlAdds performance indexes for frequently queried columns.

Row-Level Security (RLS)

File: sql/modules/core_cmms/08_rls.sqlEnables RLS on all public tables to enforce data access control.

Security Policies

File: sql/modules/core_cmms/09_policies.sqlDefines RLS policies based on RBAC permissions.

Admin Permissions Seed

File: sql/modules/core_cmms/10_seed_admin_permissions.sqlSeeds initial permissions for system administrators.

Bootstrap Data

File: sql/modules/core_cmms/11_seed_bootstrap.sqlLoads initial data:

Default roles (Admin, Technician, Requester)
System locations
Incident categories
Application settings

Schema Updates

File: sql/modules/core_cmms/12_updates.sqlApplies incremental schema updates and migrations.

Realtime Configuration

File: sql/modules/core_cmms/13_realtime.sqlConfigures Supabase Realtime for live data synchronization:

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

Storage Policies

File: sql/modules/core_cmms/14_storage.sqlConfigures Supabase Storage buckets and policies for file uploads:

Ticket attachments
User avatars
Asset photos

Auth Grants

File: sql/modules/core_cmms/15_grants_auth.sqlGrants necessary permissions to auth schema for user management.

Notifications System

File: sql/modules/core_cmms/16_notifications.sqlImplements complete notifications infrastructure:

notification_deliveries: In-app notifications
ticket_comments: Comment system with automatic notifications
notification_outbox: Push notification queue
Outbox pattern with retry logic and deduplication
Admin test tools
RPC functions for frontend integration

Phase 2: Assets Module (Optional)

If you need fixed assets and preventive maintenance:

Fixed Assets Tables

File: sql/modules/assets/01_activos_fijos.sqlCreates tables for asset tracking and management.

Assets-Tickets Integration

File: sql/modules/assets/02_ticket_assets_policies_patch.sqlLinks assets to tickets with appropriate RLS policies.

Preventive Maintenance

File: sql/modules/assets/03_preventive_maintenance.sqlImplements preventive maintenance scheduling and tracking.

Phase 3: Inventory Module (Optional)

The inventory module is isolated and should only be executed if you need full inventory management. It may conflict with core_cmms if both define similar objects (e.g., warehouses, uoms).

Inventory Schema

File: sql/Inventario&RepuestosMantenimiento.sqlExecute only if you need comprehensive inventory and spare parts management.

Post-SQL Configuration

After executing all SQL modules, complete these additional configurations:

Realtime Verification

Verify that critical tables are enabled for Realtime:

Via Supabase Dashboard
Via SQL Query

Go to Database > Replication
Verify these tables are in the publication:
- public.notification_deliveries
- public.ticket_comments
Check that both tables have REPLICA IDENTITY FULL

-- Check replica identity
SELECT 
  schemaname, 
  tablename, 
  pg_class.relreplident
FROM pg_tables
JOIN pg_class ON pg_tables.tablename = pg_class.relname
WHERE schemaname = 'public'
  AND tablename IN ('notification_deliveries', 'ticket_comments');

-- 'f' = FULL replica identity (required)

Verify Database Setup

Run these verification queries to ensure proper setup:

-- Check critical permissions
SELECT
  has_function_privilege('authenticated', 'public.create_notification_event(text,uuid,text,text,jsonb,uuid[],integer)', 'EXECUTE') 
    AS auth_can_create_event,
  has_function_privilege('service_role', 'public.create_notification_event(text,uuid,text,text,jsonb,uuid[],integer)', 'EXECUTE') 
    AS service_can_create_event,
  has_function_privilege('authenticated', 'public.send_self_test_notification(text,text,boolean)', 'EXECUTE') 
    AS auth_can_self_test;

-- Check RLS is enabled on critical tables
SELECT 
  schemaname, 
  tablename, 
  rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
  AND tablename IN ('users', 'tickets', 'roles', 'permissions')
ORDER BY tablename;

-- rowsecurity should be 't' (true) for all

-- Check outbox status (if notifications are configured)
SELECT status, COUNT(*) 
FROM public.notification_outbox 
GROUP BY status 
ORDER BY status;

Creating the First Admin User

After database setup, create your first admin user:

node scripts/create-first-admin.mjs

This script:

Prompts for admin credentials
Creates the user in auth.users
Assigns the admin role
Grants full system permissions

The script requires SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY environment variables.

Database Backup Recommendations

Enable Point-in-Time Recovery

Configure automatic backups in Supabase dashboard under Settings > Database > Backups.

Schedule Regular Backups

Set up daily automated backups with at least 7-day retention.

Test Restore Process

Periodically test backup restoration to verify data integrity.

Troubleshooting

”Permission denied” Errors

Ensure you’re using a user with postgres role privileges
Some extensions require superuser access
Contact Supabase support if you cannot install required extensions

Foreign Key Constraint Errors

Verify all previous scripts completed successfully
Check that referenced tables exist before creating foreign keys
Review execution order

RLS Policy Errors

Ensure the RBAC permission functions are created before policies
Verify that public.me_has_permission() function exists
Check that admin permissions were seeded properly

Outbox Not Processing

-- Check for stuck messages
SELECT id, status, attempts, last_error, created_at, next_attempt_at
FROM public.notification_outbox
WHERE status IN ('pending', 'processing')
ORDER BY created_at DESC
LIMIT 20;

Architecture

Database

Frontend

Push Notifications

Deployment

Overview

Module Structure

core_cmms

assets

inventory

Execution Order

Phase 1: Core CMMS Module

Phase 2: Assets Module (Optional)

Phase 3: Inventory Module (Optional)

Post-SQL Configuration

Realtime Verification

Verify Database Setup

Creating the First Admin User

Database Backup Recommendations

Troubleshooting

”Permission denied” Errors

Foreign Key Constraint Errors

RLS Policy Errors

Outbox Not Processing

Next Steps

Supabase Configuration

Production Build

Build docs developers (and LLMs) love

Architecture

Database

Frontend

Push Notifications

Deployment

Documentation Index

​Overview

​Module Structure

core_cmms

assets

inventory

​Execution Order

​Phase 1: Core CMMS Module

​Phase 2: Assets Module (Optional)

​Phase 3: Inventory Module (Optional)

​Post-SQL Configuration

​Realtime Verification

​Verify Database Setup

​Creating the First Admin User

​Database Backup Recommendations

​Troubleshooting

​”Permission denied” Errors

​Foreign Key Constraint Errors

​RLS Policy Errors

​Outbox Not Processing

​Next Steps

Supabase Configuration

Production Build

Build docs developers (and LLMs) love

Overview

Module Structure

Execution Order

Phase 1: Core CMMS Module

Phase 2: Assets Module (Optional)

Phase 3: Inventory Module (Optional)

Post-SQL Configuration

Realtime Verification

Verify Database Setup

Creating the First Admin User

Database Backup Recommendations

Troubleshooting

”Permission denied” Errors

Foreign Key Constraint Errors

RLS Policy Errors

Outbox Not Processing

Next Steps