Database Schema

Complete reference of the MLM CMMS database structure, tables, and relationships

Database Schema

The MLM CMMS database is built on PostgreSQL and organized into modular SQL scripts for deterministic bootstrapping. This document provides a comprehensive reference of the database structure.

Module Organization

The database schema is organized into three main modules:

core_cmms

Foundation module with RBAC, tickets, users, notifications, and core functionality

assets

Asset management, categories, and preventive maintenance

inventory

Inventory, parts, warehouses, stock movements, and reservations

Execution Order

The SQL modules must be executed in the exact order shown below. Do not reorder files without an explicit migration plan.

Core CMMS Module

1. Extensions

   00_extensions.sql - PostgreSQL extensions (e.g., uuid-ossp, pg_cron)

2. Enums

   01_enums.sql - Enumerated types (priority, assignee sections, etc.)

3. Permission Actions

   02_permission_action.sql - Permission action enum type

4. Tables

   03_tables.sql - Core table definitions

5. Functions & Triggers

   04_functions_triggers.sql - Stored procedures and triggers

6. Foreign Keys

   05_fk.sql - Foreign key constraints

7. Views

   06_views.sql - Database views

8. Indexes

   07_indexes.sql - Performance indexes

9. RLS

   08_rls.sql - Enable Row-Level Security

10. Policies

    09_policies.sql - RLS policies

11. Admin Permissions

    10_seed_admin_permissions.sql - Permission registry seed data

12. Bootstrap Data

    11_seed_bootstrap.sql - Initial roles and system data

13. Updates

    12_updates.sql - Schema updates and migrations

14. Realtime

    13_realtime.sql - Supabase Realtime configuration

15. Storage

    14_storage.sql - Supabase Storage buckets and policies

16. Grants

    15_grants_auth.sql - Schema grants and authentication setup

17. Notifications

    16_notifications.sql - Notification system tables and functions

Core Tables

RBAC Tables

roles

Purpose: User role definitions

Column	Type	Constraints	Description
id	serial	PRIMARY KEY	Role identifier
name	text	NOT NULL, UNIQUE	Role name
description	varchar	NULL	Role description
is_system	boolean	NOT NULL, DEFAULT false	System role flag (non-deletable)
created_at	timestamptz	DEFAULT now()	Creation timestamp

Row-Level Security: Enabled

Source: sql/modules/core_cmms/03_tables.sql:18

permissions

Purpose: Permission registry

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY, DEFAULT gen_random_uuid()	Permission identifier
resource	text	NOT NULL	Resource name (e.g., ‘tickets’, ‘users’)
action	permission_action	NOT NULL	Action type (create, read, update, delete, etc.)
code	text	NOT NULL, UNIQUE	Permission code (e.g., ‘tickets:create’)
label	text	NOT NULL	Human-readable label
description	text	NULL	Detailed description
is_active	boolean	NOT NULL, DEFAULT true	Active status
created_at	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp

Row-Level Security: Enabled

Source: sql/modules/core_cmms/03_tables.sql:45

Permission codes must match those defined in src/rbac/permissionRegistry.ts for frontend-backend consistency.

role_permissions

Purpose: Many-to-many relationship between roles and permissions

Column	Type	Constraints	Description
role_id	int	FK → roles.id, CASCADE DELETE	Role identifier
permission_id	uuid	FK → permissions.id, CASCADE DELETE	Permission identifier
PRIMARY KEY		(role_id, permission_id)	Composite key

Row-Level Security: Enabled

Source: sql/modules/core_cmms/03_tables.sql:56

user_roles

Purpose: Many-to-many relationship between users and roles

Column	Type	Constraints	Description
user_id	uuid	FK → auth.users.id, CASCADE DELETE	User identifier
role_id	int	FK → roles.id, CASCADE DELETE	Role identifier
PRIMARY KEY		(user_id, role_id)	Composite key

Row-Level Security: Enabled

Source: sql/modules/core_cmms/03_tables.sql:62

User Management

users

Purpose: Public user profile (extends auth.users)

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY, FK → auth.users.id	User identifier (matches auth.users)
rol_id	bigint	NULL, FK → roles.id	Legacy role reference
name	text	NOT NULL	First name
last_name	text	NOT NULL	Last name
location_id	bigint	NULL, FK → locations.id	User’s location
email	text	NULL	Email address
phone	text	NULL	Phone number
is_active	boolean	NOT NULL, DEFAULT true	Active status
created_at	timestamp	NOT NULL, DEFAULT now()	Creation timestamp
updated_at	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp
created_by	uuid	NULL, DEFAULT auth.uid()	Creator user ID
updated_by	uuid	NULL	Last updater user ID
password_reset_at	timestamptz	NULL	Password reset timestamp
password_reset_by	uuid	NULL	Who initiated password reset

Row-Level Security: Enabled

Source: sql/modules/core_cmms/03_tables.sql:28

Ticket Management

tickets

Purpose: Work requests and work orders

Column	Type	Constraints	Description
id	bigserial	PRIMARY KEY	Ticket identifier
title	text	NOT NULL	Ticket title
description	text	NOT NULL	Detailed description
is_accepted	boolean	NOT NULL, DEFAULT false	Approval status (work order flag)
is_urgent	boolean	NOT NULL	Urgency flag
priority	priority_enum	NOT NULL	Priority level
requester	text	NOT NULL	Requester name
location_id	bigint	NULL, FK → locations.id	Location reference
assignee	text	NOT NULL	Legacy assignee name
assignee_id	bigint	NULL, FK → assignees.id	New assignee reference
special_incident_id	integer	NULL, FK → special_incidents.id	Special incident type
incident_date	date	NOT NULL	When incident occurred
deadline_date	date	NULL	Due date
is_archived	boolean	NOT NULL, DEFAULT false	Archive status
finalized_at	timestamp	NULL	Completion timestamp
image	text	NOT NULL	Image URL/path
email	text	NULL	Requester email
phone	text	NULL	Requester phone
comments	text	NULL	Legacy comments field
status	text	DEFAULT ‘Pendiente’	Ticket status
created_at	timestamp	NOT NULL, DEFAULT now()	Creation timestamp
updated_at	timestamptz	NULL	Last update timestamp
created_by	uuid	NULL	Creator user ID
updated_by	uuid	NULL	Last updater user ID

Row-Level Security: Enabled with separate policies for work requests vs work orders

Source: sql/modules/core_cmms/03_tables.sql:84

The is_accepted flag distinguishes work requests (false) from work orders (true).

assignees

Purpose: Technical staff who can be assigned to tickets

Column	Type	Constraints	Description
id	bigserial	PRIMARY KEY	Assignee identifier
name	text	NOT NULL	First name
last_name	text	NOT NULL	Last name
section	assignee_section_enum	NOT NULL, DEFAULT ‘SIN ASIGNAR’	Department/section
user_id	uuid	NULL, FK → users.id	Linked user account
email	text	NULL	Email address
phone	text	NULL	Phone number
is_active	boolean	NOT NULL, DEFAULT true	Active status
created_at	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
updated_at	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp
created_by	uuid	DEFAULT auth.uid(), FK → users.id	Creator user ID
updated_by	uuid	NULL, FK → users.id	Last updater user ID
UNIQUE		(name, section)	Prevent duplicate name+section

Row-Level Security: Enabled

Source: sql/modules/core_cmms/03_tables.sql:68

work_order_assignees

Purpose: Multi-assignee support for work orders (replaces single assignee)

Column	Type	Constraints	Description
work_order_id	bigint	NOT NULL, FK → tickets.id, CASCADE DELETE	Ticket identifier
assignee_id	bigint	NOT NULL, FK → assignees.id	Assignee identifier
role	assignee_role_enum	NOT NULL	Role (e.g., ‘primary’, ‘support’)
is_active	boolean	NOT NULL, DEFAULT true	Active assignment flag
assigned_at	timestamp	NOT NULL, DEFAULT now()	Assignment timestamp
unassigned_at	timestamp	NULL	When assignment ended
created_at	timestamp	NOT NULL, DEFAULT now()	Creation timestamp
updated_at	timestamp	NOT NULL, DEFAULT now()	Last update timestamp
created_by	uuid	DEFAULT auth.uid(), FK → users.id	Creator user ID
updated_by	uuid	NULL, FK → users.id	Last updater user ID

Row-Level Security: Enabled

Source: sql/modules/core_cmms/03_tables.sql:177

special_incidents

Purpose: Catalog of special incident types (hurricanes, power outages, etc.)

Column	Type	Constraints	Description
id	serial	PRIMARY KEY	Incident type identifier
name	text	NOT NULL, UNIQUE	Display name
code	text	NOT NULL, UNIQUE	Code/slug for UI
description	text	NULL	Detailed description
is_active	boolean	DEFAULT true	Active status
created_at	timestamp	NOT NULL, DEFAULT now()	Creation timestamp
updated_at	timestamp	NOT NULL, DEFAULT now()	Last update timestamp
created_by	uuid	NOT NULL, FK → users.id	Creator user ID
updated_by	uuid	NULL, FK → users.id	Last updater user ID

Row-Level Security: Enabled

Source: sql/modules/core_cmms/03_tables.sql:191

Configuration & Settings

app_settings

Purpose: Key-value store for application settings

Column	Type	Constraints	Description
key	text	PRIMARY KEY	Setting key
value	jsonb	NOT NULL	Setting value (JSON)
updated_at	timestamp	NOT NULL, DEFAULT now()	Last update timestamp
updated_by	uuid	NULL, FK → users.id	Last updater user ID

Row-Level Security: Enabled

Source: sql/modules/core_cmms/03_tables.sql:111

locations

Purpose: Physical locations/sites catalog

Column	Type	Constraints	Description
id	bigserial	PRIMARY KEY	Location identifier
name	text	NOT NULL, UNIQUE	Location name
code	text	NOT NULL, UNIQUE	URL-friendly slug
description	text	NULL	Description
is_active	boolean	NOT NULL, DEFAULT true	Active status
created_at	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
updated_at	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp
created_by	uuid	NULL, FK → users.id	Creator user ID
updated_by	uuid	NULL, FK → users.id	Last updater user ID

Row-Level Security: Enabled

Source: sql/modules/core_cmms/03_tables.sql:144

societies

Purpose: Multi-tenant company/branding configuration

Column	Type	Constraints	Description
id	bigserial	PRIMARY KEY	Society identifier
name	text	NOT NULL	Company name
logo_url	text	NULL	Logo image URL
login_img_url	text	NULL	Login page background image URL
is_active	boolean	NOT NULL, DEFAULT true	Active status
created_at	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
updated_at	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp

Row-Level Security: Enabled

Public View: societies_public (grants SELECT to anon/authenticated)

Source: sql/modules/core_cmms/03_tables.sql:157

Announcements

announcements

Purpose: System-wide announcements with role-based targeting

Column	Type	Constraints	Description
id	bigint	PRIMARY KEY, GENERATED ALWAYS AS IDENTITY	Announcement identifier
message	text	NOT NULL	Announcement text
level	text	NOT NULL, DEFAULT ‘info’	Severity level (info, warning, danger, success)
url	text	NULL	Optional link URL
is_active	boolean	NOT NULL, DEFAULT true	Active/visible flag
dismissible	boolean	NOT NULL, DEFAULT true	User can dismiss flag
starts_at	timestamptz	DEFAULT now()	Start of validity period
ends_at	timestamptz	NULL	End of validity period
audience_all	boolean	NOT NULL, DEFAULT true	Show to all roles (true) or specific roles (false)
created_at	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
updated_at	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp
created_by	uuid	NULL, FK → users.id	Creator user ID
updated_by	uuid	NULL, FK → users.id	Last updater user ID

Row-Level Security: Enabled

Source: sql/modules/core_cmms/03_tables.sql:118

announcement_audience_roles

Purpose: Role targeting for announcements (when audience_all = false)

Column	Type	Constraints	Description
announcement_id	bigint	NOT NULL, FK → announcements.id, CASCADE DELETE	Announcement identifier
role_id	int	NOT NULL, FK → roles.id, CASCADE DELETE	Role identifier
PRIMARY KEY		(announcement_id, role_id)	Composite key

Row-Level Security: Enabled

Source: sql/modules/core_cmms/03_tables.sql:138

Notifications

The notification system is defined in sql/modules/core_cmms/16_notifications.sql.

notification_outbox

Purpose: Outbox pattern for reliable push notification delivery

Column	Type	Description
id	bigint	Outbox entry identifier
user_id	uuid	Target user
title	text	Notification title
body	text	Notification body
url	text	Click target URL
icon	text	Notification icon URL
status	text	pending / processing / sent / failed
retry_count	int	Number of send attempts
max_retries	int	Maximum retry attempts
scheduled_at	timestamptz	When to send
sent_at	timestamptz	When successfully sent
error	text	Error message if failed
created_at	timestamptz	Creation timestamp

Deduplication: 5-minute window to prevent duplicate notifications

notification_deliveries

Purpose: In-app notification tracking and display

Column	Type	Description
id	bigint	Delivery identifier
user_id	uuid	Target user
title	text	Notification title
body	text	Notification body
url	text	Click target URL
icon	text	Notification icon
is_read	boolean	Read status
read_at	timestamptz	When marked as read
created_at	timestamptz	Creation timestamp

Realtime: Enabled with REPLICA IDENTITY FULL for live badge updates

push_subscriptions

Purpose: Web Push API subscription storage

Column	Type	Description
id	bigint	Subscription identifier
user_id	uuid	Subscriber user
endpoint	text	Push service endpoint URL
p256dh	text	Public key for encryption
auth	text	Auth secret for encryption
user_agent	text	Browser/device info
created_at	timestamptz	Subscription timestamp

Unique: (user_id, endpoint) to prevent duplicate subscriptions

Report Preferences

report_layout_preferences

Purpose: User-specific widget layout for report dashboards

Column	Type	Constraints	Description
user_id	uuid	NOT NULL, FK → users.id, CASCADE DELETE	User identifier
tab_id	text	NOT NULL, CHECK (length > 0)	Tab identifier
widget_order	text[]	NOT NULL, DEFAULT []	Ordered array of widget IDs
created_at	timestamptz	NOT NULL, DEFAULT now()	Creation timestamp
updated_at	timestamptz	NOT NULL, DEFAULT now()	Last update timestamp
PRIMARY KEY		(user_id, tab_id)	Composite key

Row-Level Security: Enabled

Source: sql/modules/core_cmms/03_tables.sql:167

Row-Level Security (RLS)

All tables have RLS enabled. Access is controlled through policies that check public.me_has_permission(code) against the user’s assigned permissions.

Permission Checking Function

The me_has_permission(code text) function:

1. Gets the current user’s ID via auth.uid()
2. Joins through user_roles → role_permissions → permissions
3. Checks if any of the user’s roles grant the specified permission code
4. Returns boolean

Example Policies

-- Read tickets (work orders)
CREATE POLICY tickets_select_work_orders ON public.tickets
  FOR SELECT
  USING (
    is_accepted = true
    AND (
      me_has_permission('work_orders:read')
      OR me_has_permission('work_orders:full_access')
    )
  );

-- Insert tickets (work orders)
CREATE POLICY tickets_insert_rbac ON public.tickets
  FOR INSERT
  WITH CHECK (
    me_has_permission('work_orders:create')
    OR me_has_permission('work_orders:full_access')
  );

See sql/modules/core_cmms/09_policies.sql for all policy definitions.

Enums

Defined in sql/modules/core_cmms/01_enums.sql:

Enum Type	Values	Usage
priority_enum	'BAJA', 'MEDIA', 'ALTA'	Ticket priority
assignee_section_enum	'ELECTRICIDAD', 'MECANICA', 'CIVIL', 'SIN ASIGNAR', etc.	Assignee department
assignee_role_enum	'primary', 'support', 'observer'	Role in work order assignment
permission_action	'create', 'read', 'read_own', 'update', 'delete', 'work', 'import', 'export', 'approve', 'assign', 'disable', 'full_access', 'cancel', 'manage_roles', 'manage_permissions'	Permission action types

The permission_action enum must match the TypeScript PermissionAction type in src/rbac/permissionRegistry.ts:1-16.

Foreign Key Relationships

Key relationships defined in sql/modules/core_cmms/05_fk.sql:

users.id ←→ auth.users.id
users.location_id → locations.id

tickets.location_id → locations.id
tickets.assignee_id → assignees.id
tickets.special_incident_id → special_incidents.id

work_order_assignees.work_order_id → tickets.id (CASCADE DELETE)
work_order_assignees.assignee_id → assignees.id

role_permissions.role_id → roles.id (CASCADE DELETE)
role_permissions.permission_id → permissions.id (CASCADE DELETE)

user_roles.user_id → auth.users.id (CASCADE DELETE)
user_roles.role_id → roles.id (CASCADE DELETE)

Indexes

Performance indexes defined in sql/modules/core_cmms/07_indexes.sql include:

• Ticket filtering: idx_tickets_is_accepted, idx_tickets_status, idx_tickets_created_by
• Permission lookups: idx_permissions_code, idx_permissions_resource_action
• Role assignments: idx_user_roles_user_id, idx_role_permissions_role_id
• Notification queries: idx_notification_deliveries_user_unread

Storage Buckets

Defined in sql/modules/core_cmms/14_storage.sql:

Bucket	Public	Purpose	Policies
branding	Yes	Company logos, login images	Read: public, Write: society:full_access
tickets	No	Ticket attachments/images	RLS-controlled

Realtime Configuration

Enabled for real-time updates (sql/modules/core_cmms/13_realtime.sql):

• notification_deliveries - Badge count updates
• ticket_comments - Live comment streams

Both tables use REPLICA IDENTITY FULL to send complete row data on changes.

Next Steps

RBAC System

Understand how permissions and roles work

Architecture

Review the overall system architecture

Database Functions

Explore available database functions

Database Setup

Initialize a new database