Core Tables

Database schema reference for MLM CMMS core tables

Core Database Tables

This page documents the core database tables in the MLM CMMS system, defined in sql/modules/core_cmms/03_tables.sql.

RBAC Tables

roles

User roles for permission management.

Column	Type	Description
id	serial	Primary key
name	text	Role name (unique)
description	varchar	Role description
is_system	boolean	System role flag (cannot be deleted)
created_at	timestamptz	Creation timestamp

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

permissions

Permission definitions for access control.

Column	Type	Description
id	uuid	Primary key
resource	text	Resource name (e.g., ‘work_orders’)
action	permission_action	Action type (create, read, update, etc.)
code	text	Permission code (unique, format: resource:action)
label	text	Human-readable label
description	text	Permission description
is_active	boolean	Active status
created_at	timestamptz	Creation timestamp

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

role_permissions

Many-to-many mapping between roles and permissions.

Column	Type	Description
role_id	int	Foreign key to roles
permission_id	uuid	Foreign key to permissions

Primary Key: (role_id, permission_id)

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

user_roles

Many-to-many mapping between users and roles.

Column	Type	Description
user_id	uuid	Foreign key to auth.users
role_id	int	Foreign key to roles

Primary Key: (user_id, role_id)

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

User Management

users

Public user profiles linked to auth.users.

Column	Type	Description
id	uuid	Primary key (FK to auth.users)
rol_id	bigint	Foreign key to roles (legacy)
name	text	First name
last_name	text	Last name
location_id	bigint	Foreign key to locations
email	text	Email address
phone	text	Phone number
is_active	boolean	Active status
created_at	timestamp	Creation timestamp
updated_at	timestamptz	Last update timestamp
created_by	uuid	User who created this record
updated_by	uuid	User who last updated this record
password_reset_at	timestamptz	Last password reset timestamp
password_reset_by	uuid	User who performed password reset

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

assignees

Technicians and maintenance staff who can be assigned to work orders.

Column	Type	Description
id	bigserial	Primary key
name	text	First name
last_name	text	Last name
section	assignee_section_enum	Section (INTERNOS, TERCEROS, OTROS)
user_id	uuid	Optional link to users table
email	text	Email address
phone	text	Phone number
is_active	boolean	Active status
created_at	timestamptz	Creation timestamp
updated_at	timestamptz	Last update timestamp
created_by	uuid	User who created this record
updated_by	uuid	User who last updated this record

Unique Constraint: (name, section)

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

Ticket Management

tickets

Main table for work requests and work orders.

Column	Type	Description
id	bigserial	Primary key
title	text	Ticket title
description	text	Detailed description
is_accepted	boolean	Accepted as work order flag
is_urgent	boolean	Urgent priority flag
priority	priority_enum	Priority level
requester	text	Requester name
location_id	bigint	Foreign key to locations
assignee	text	Legacy assignee field (visible)
special_incident_id	integer	Foreign key to special_incidents
incident_date	date	Date incident occurred
deadline_date	date	Deadline for completion
is_archived	boolean	Archived status
finalized_at	timestamp	Completion timestamp
image	text	Image URL or path
email	text	Requester email
status	status_enum	Current status
order_index	integer	Manual ordering index
notes	text	Additional notes
created_at	timestamptz	Creation timestamp
updated_at	timestamptz	Last update timestamp

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

work_order_assignees

Many-to-many relationship between tickets and assignees.

Column	Type	Description
id	bigserial	Primary key
ticket_id	bigint	Foreign key to tickets
assignee_id	bigint	Foreign key to assignees
role	wo_assignee_role_enum	Role (PRIMARY, SECONDARY)
assigned_at	timestamptz	Assignment timestamp
assigned_by	uuid	User who made the assignment

Unique Constraint: (ticket_id, assignee_id)

Source: sql/modules/core_cmms/03_tables.sql (reference in DROP statements)

Configuration Tables

locations

Physical locations where maintenance is performed.

Column	Type	Description
id	bigserial	Primary key
name	text	Location name
address	text	Physical address
is_active	boolean	Active status
created_at	timestamptz	Creation timestamp
updated_at	timestamptz	Last update timestamp

Source: Referenced in foreign keys

societies

Company or organization information.

Column	Type	Description
id	bigserial	Primary key
name	text	Company name
logo_url	text	Logo image URL
primary_color	text	Brand primary color
secondary_color	text	Brand secondary color
is_active	boolean	Active status

Source: Referenced in DROP statements

app_settings

Application-wide configuration settings.

Column	Type	Description
key	text	Setting key (primary key)
value	jsonb	Setting value (JSON)
updated_at	timestamptz	Last update timestamp

Source: Referenced in DROP statements

special_incidents

Predefined incident types for categorization.

Column	Type	Description
id	serial	Primary key
name	text	Incident type name
description	text	Description
is_active	boolean	Active status

Source: Referenced in DROP statements

Communication Tables

announcements

System-wide announcements.

Column	Type	Description
id	bigserial	Primary key
title	text	Announcement title
message	text	Announcement content
severity	text	Severity level
is_active	boolean	Active status
created_at	timestamptz	Creation timestamp
expires_at	timestamptz	Expiration timestamp

Source: Referenced in DROP statements

announcement_audience_roles

Roles that should see specific announcements.

Column	Type	Description
announcement_id	bigint	Foreign key to announcements
role_id	int	Foreign key to roles

Primary Key: (announcement_id, role_id)

Source: Referenced in DROP statements

Notification Tables

See Notifications Database Schema for detailed documentation of the notification system tables:

• notification_events
• notification_deliveries
• notification_outbox
• notification_push_subscriptions

Reporting Tables

report_layout_preferences

User preferences for report dashboard layouts.

Column	Type	Description
user_id	uuid	Primary key (FK to users)
layout_data	jsonb	Layout configuration (JSON)
updated_at	timestamptz	Last update timestamp

Source: Referenced in DROP statements

Database Enums

The system uses several PostgreSQL enums defined in sql/modules/core_cmms/01_enums.sql:

• permission_action: create, read, read_own, update, delete, work, import, export, approve, assign, disable, full_access, cancel, manage_roles, manage_permissions
• priority_enum: LOW, MEDIUM, HIGH, CRITICAL
• status_enum: PENDIENTE, EN_EJECUCION, FINALIZADA
• assignee_section_enum: INTERNOS, TERCEROS, OTROS, SIN_ASIGNAR
• wo_assignee_role_enum: PRIMARY, SECONDARY

Next Steps

Database Functions

Learn about stored procedures and triggers

RLS Policies

Understand row-level security implementation

Notifications Schema

Explore the notification system tables

Database Schema Overview

See the complete schema documentation