Database Schema and Migrations
Relevant source files
The following files were used as context for generating this wiki page:The ElmecV2 application utilizes a Supabase (PostgreSQL) backend to manage all its data, including user authentication, service requests, real-time chat, and notifications. The database schema is designed to support a multi-role system (admin, agent, customer) with robust data relationships.
Database setup and modifications are handled through a series of manual SQL scripts. These scripts are essential for initializing the schema and for adding new features or fixing schema deficiencies identified during development and testing. The integrity and full functionality of the application are critically dependent on the correct application of these migrations.
Database Schema
The core of the application revolves around users, their requests, and the communication channels (chat) associated with those requests.
Entity-Relationship Diagram
This diagram shows the primary tables and their relationships within the Supabase database.
Key Tables
requests Table
This table stores all service requests created by users. It is central to the application's workflow.
| Column | Data Type | Constraints / Description |
|---|---|---|
id | uuid | Primary Key |
usuario_id | uuid | Foreign Key to users.id |
agente_id | uuid | Foreign Key to users.id (nullable) |
titulo | text | Title of the request |
mensaje | text | Detailed message of the request |
estatus | text | Status, e.g., 'nuevo', 'asignado', 'resuelto' |
prioridad | text | Priority, e.g., 'baja', 'media', 'alta' |
tipo | integer | Type of request |
archivos | TEXT[] | (Missing) Array of URLs for file attachments |
feedback | TEXT | (Missing) User feedback on resolution |
rating | INTEGER | (Missing) 1-5 star rating |
created_at | timestamp | Auto-generated timestamp |
updated_at | timestamp | Auto-updated on change |
Sources: REPORTE_PRUEBAS_REQUESTS_CRUD.md:92-109, INSTRUCCIONES_SQL.md:16-24
messages Table
This table stores all chat messages exchanged between users. It supports text, files, replies, and other features.
| Column | Data Type | Constraints / Description |
|---|---|---|
id | uuid | Primary Key |
chat_room_id | uuid | Foreign Key to chat_rooms.id |
sender_id | uuid | Foreign Key to users.id |
message | text | The content of the message |
file_url | text | URL of an attached file (if any) |
reply_to | UUID | (Missing) FK to messages.id for replies |
file_name | TEXT | (Missing) Original name of the attached file |
file_size | INTEGER | (Missing) Size of the file in bytes |
audio_duration | INTEGER | (Missing) Duration of voice notes in seconds |
edited_at | TIMESTAMP | (Missing) Timestamp of the last edit |
read_by | JSONB | (Missing) JSON object tracking who read the message |
created_at | timestamp | Auto-generated timestamp |
Sources: REPORTE_CHAT_MODULE.md:213-233, INSTRUCCIONES_CHAT_SQL.md:24-49
Migrations and Setup
The database setup is a manual process that involves creating a Supabase project and then running a series of SQL scripts in the Supabase SQL Editor. An automated script exists to create demo users after the schema is in place.
Setup and Migration Flow
The following diagram illustrates the end-to-end process for setting up the database.
Manual SQL Migrations
Migrations are not automated via the Supabase CLI. Instead, developers must manually copy and execute SQL scripts in the Supabase Dashboard's SQL Editor. This is particularly important for addressing schema deficiencies found during testing.
Identified Schema Deficiencies
Testing has revealed that several columns required by the application code are missing from the base schema. Applying the provided SQL scripts is mandatory for full functionality.
| Table | Missing Columns | Blocked Functionality |
|---|---|---|
requests | archivos, feedback, rating | File attachments, user feedback, and service ratings. |
messages | reply_to, file_name, file_size, audio_duration, edited_at, read_by | Replying to messages, file metadata, voice notes, editing messages, and read receipts. |
Sources: REPORTE_PRUEBAS_REQUESTS_CRUD.md:73-89, REPORTE_CHAT_MODULE.md:60-120
Example Migration Script (requests table)
This script adds the missing columns for file attachments and user feedback to the requests table.
-- 1. Agregar columna para archivos adjuntos
ALTER TABLE requests
ADD COLUMN IF NOT EXISTS archivos TEXT[] DEFAULT NULL;
-- 2. Agregar columnas para feedback del usuario
ALTER TABLE requests
ADD COLUMN IF NOT EXISTS feedback TEXT DEFAULT NULL,
ADD COLUMN IF NOT EXISTS rating INTEGER DEFAULT NULL;
-- 3. Agregar constraint para validar rating (1-5)
ALTER TABLE requests
ADD CONSTRAINT check_rating_range
CHECK (rating IS NULL OR (rating >= 1 AND rating <= 5));
Sources: INSTRUCCIONES_SQL.md:20-37
Example Migration Script (messages table)
This script adds multiple columns to the messages table to enable advanced chat features like replies, file metadata, and read receipts.
-- 1. Agregar columna reply_to (para responder mensajes)
ALTER TABLE messages
ADD COLUMN IF NOT EXISTS reply_to UUID REFERENCES messages(id) ON DELETE SET NULL;
-- 2. Agregar columnas para archivos adjuntos
ALTER TABLE messages
ADD COLUMN IF NOT EXISTS file_name TEXT DEFAULT NULL,
ADD COLUMN IF NOT EXISTS file_size INTEGER DEFAULT NULL;
-- 4. Agregar columna para rastrear ediciones
ALTER TABLE messages
ADD COLUMN IF NOT EXISTS edited_at TIMESTAMP DEFAULT NULL;
Sources: INSTRUCCIONES_CHAT_SQL.md:28-45
Automated User Setup
After the schema is correctly migrated, the scripts/setup-supabase-complete.js script can be run to populate the database with demo users. This script uses the SUPABASE_SERVICE_ROLE_KEY to interact with Supabase Auth.
setup-supabase-complete.js Execution Flow
This sequence diagram shows how the script creates demo users.
The script creates users with different roles (admin, agent, customer) and pre-defined credentials, which are essential for testing the application's role-based access control.
Sources: scripts/setup-supabase-complete.js:47-86
Conclusion
The database architecture for ElmecV2 is well-defined but relies on manual intervention for setup and migration. The provided SQL scripts in INSTRUCCIONES_SQL.md and INSTRUCCIONES_CHAT_SQL.md are not optional; they are critical patches to align the database schema with the application's feature set. Failure to apply these migrations will result in a significant portion of the application, particularly the chat and request modules, being non-functional. The setup-supabase-complete.js script provides a convenient way to populate the system with test data once the schema is correctly configured.