Database Schema & Models
Relevant source files
The following files were used as context for generating this wiki page:
The database for the ELMEC Mobile App v2 is built on Supabase (PostgreSQL), serving as the backbone for user management, service requests, real-time messaging, and system notifications. The schema is designed to support a multi-role environment comprising Administrators, Agents, and Customers, ensuring data integrity through relational constraints and Row Level Security (RLS) policies.
This documentation covers the core tables, their relationships, and the specific field requirements identified during technical audits and module validations. The architecture prioritizes auditability with timestamps and flexibility through JSONB metadata fields.
Sources: README.md, supabase/setup-instructions.md, REPORTE-EJECUCION-PRUEBAS.md
Core Data Architecture
The system's data model is centered around five primary entities: Users, Requests, Chat Rooms, Messages, and Notifications. Additional utility tables support calculator sessions and system logs.
Entity Relationship Overview
The following diagram illustrates the primary relationships between the core database entities.
The system uses UUIDs as primary keys across all tables to ensure unique identification across distributed environments.
Sources: scripts/setup-supabase-complete.js:192-205, REPORTE_CHAT_MODULE.md
Table Definitions
1. Users Table
Manages application identity and profile information. It distinguishes users by role to control access levels within the app.
| Field | Type | Description |
|---|---|---|
| id | UUID (PK) | Unique identifier (linked to Auth.users) |
| TEXT | User email address | |
| nombre | TEXT | First name |
| apellido_paterno | TEXT | Paternal last name |
| apellido_materno | TEXT | Maternal last name |
| rol | TEXT | User role: 'admin', 'agent', or 'customer' |
| activo | BOOLEAN | Status indicator for the user account |
| foto | TEXT | (Optional) URL to profile picture |
Sources: scripts/setup-supabase-complete.js:46-77, INSTRUCCIONES_CHAT_SQL.md:144-150
2. Requests Table
Core table for managing service or support tickets. It tracks the lifecycle of a request from creation to resolution.
| Field | Type | Constraints | Description |
|---|---|---|---|
| id | UUID (PK) | Default: gen_random_uuid() | Unique request ID |
| usuario_id | UUID (FK) | References Users(id) | The customer who created the request |
| agente_id | UUID (FK) | References Users(id) | The assigned agent |
| titulo | TEXT | 5-200 characters | Brief summary of the issue |
| mensaje | TEXT | Min 10 characters | Detailed description |
| estatus | TEXT | CHECK: nuevo, asignado, en_proceso, pausado, resuelto | Current state of the request |
| prioridad | TEXT | CHECK: baja, media, alta, urgente | Severity level |
| tipo | INTEGER | Category of the request | |
| archivos | TEXT[] | Default: NULL | Array of Supabase Storage URLs |
| feedback | TEXT | Customer's closing comments | |
| rating | INTEGER | CHECK: 1-5 | Numeric satisfaction score |
| metadata | JSONB | Extensible field for app-specific data |
Sources: REPORTE_PRUEBAS_REQUESTS_CRUD.md:58-94, INSTRUCCIONES_SQL.md:31-64, REPORTE-EJECUCION-PRUEBAS.md:73-84
3. Messaging System
The chat module relies on two tightly coupled tables to handle real-time communication.
Chat Rooms
Defines a conversation context, often linked to a specific request.
- Participants: A JSONB or Array field containing the UUIDs of users allowed in the room.
- Type: Constrained by a CHECK to 'support', 'sales', or 'general'.
- is_active: Boolean flag to enable/disable the room.
Messages
Stores individual interactions within a room.
- reply_to: Self-referencing UUID for threaded replies.
- type: Enum-like TEXT field for 'text', 'image', 'file', or 'audio'.
- read_by: JSONB object tracking which participants have seen the message.
- file_name / file_size / audio_duration: Metadata for non-text content.
Sources: REPORTE_CHAT_MODULE.md:189-216, INSTRUCCIONES_CHAT_SQL.md:31-57, REPORTE-EJECUCION-PRUEBAS.md:92-105
4. Notifications Table
Stores in-app alerts for users regarding request updates or new messages.
| Field | Type | Description |
|---|---|---|
| id | UUID (PK) | Unique notification ID |
| user_id | UUID (FK) | Recipient of the notification |
| type | TEXT | CHECK: request_update, new_message, assignment, reminder, system |
| title | TEXT | Short summary for the alert |
| content | TEXT | Detailed notification message |
| read | BOOLEAN | Read status (Default: false) |
Sources: REPORTE-EJECUCION-PRUEBAS.md:112-127, scripts/setup-supabase-complete.js:192-205
Database Constraints and Logic
The schema implements several CHECK constraints to ensure data validity at the database level. Attempting to insert values outside these ranges results in a PGRST116 or check constraint violation error.
Audit and Maintenance
Every primary table includes created_at and updated_at timestamps. The updated_at field is typically managed via a database trigger that updates the timestamp whenever a row modification occurs.
Sources: REPORTE-EJECUCION-PRUEBAS.md:143-186, INSTRUCCIONES_SQL.md:46-51
Conclusion
The ELMEC database schema is a relational model optimized for service management and real-time collaboration. By leveraging specialized PostgreSQL types like JSONB for metadata and TEXT[] for file arrays, the system remains extensible while maintaining strict integrity through SQL CHECK constraints and UUID-based relationships. This structure ensures that modules like Chat and Requests can interact seamlessly while enforcing role-based data access.
Sources: REPORTE_CHAT_MODULE.md, VALIDACION-COMPLETA.md