# Arquitectura y Base de Datos ## Arquitectura General ### Diagrama de Componentes ``` ┌─────────────────────────────────────────────────────────────────────────┐ │ CLIENTES │ ├─────────────────────────────────────────────────────────────────────────┤ │ │ │ ┌──────────────────┐ ┌──────────────────┐ ┌──────────────────┐ │ │ │ Navegador Web │ │ Navegador Web │ │ Dispositivos │ │ │ │ (App Principal)│ │ (Panel Carga) │ │ LoRaWAN │ │ │ └────────┬─────────┘ └────────┬─────────┘ └────────┬─────────┘ │ │ │ │ │ │ └────────────┼───────────────────────┼───────────────────────┼────────────┘ │ │ │ ▼ ▼ ▼ ┌─────────────────────────────────────────────────────────────────────────┐ │ CAPA DE PRESENTACION │ ├─────────────────────────────────────────────────────────────────────────┤ │ │ │ ┌──────────────────────────────────────────────────────────────────┐ │ │ │ NGINX (Reverse Proxy) │ │ │ │ - SSL/TLS Termination │ │ │ │ - Load Balancing │ │ │ │ - Static File Serving │ │ │ └──────────────────────────────────────────────────────────────────┘ │ │ │ │ ┌────────────────────┐ ┌────────────────────┐ │ │ │ Frontend React │ │ Upload Panel │ │ │ │ (sistema.grh.com) │ │ (panel.grh.com) │ │ │ │ Puerto: 5173 │ │ Puerto: 5174 │ │ │ └────────────────────┘ └────────────────────┘ │ │ │ └─────────────────────────────────────────────────────────────────────────┘ │ ▼ ┌─────────────────────────────────────────────────────────────────────────┐ │ CAPA DE SERVICIOS │ ├─────────────────────────────────────────────────────────────────────────┤ │ │ │ ┌──────────────────────────────────────────────────────────────────┐ │ │ │ Express.js API Server │ │ │ │ (api.grh.com - Puerto 3000) │ │ │ ├──────────────────────────────────────────────────────────────────┤ │ │ │ │ │ │ │ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ │ │ │ │ Routes │ │ Controllers │ │ Services │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ - auth │ │ - auth │ │ - auth │ │ │ │ │ │ - projects │ │ - project │ │ - project │ │ │ │ │ │ - meters │ │ - meter │ │ - meter │ │ │ │ │ │ - readings │ │ - reading │ │ - reading │ │ │ │ │ │ - users │ │ - user │ │ - user │ │ │ │ │ │ - organismos│ │ - organismo │ │ - organismo │ │ │ │ │ │ - csv-upload│ │ - etc... │ │ - csv-upload│ │ │ │ │ │ - webhooks │ │ │ │ - tts │ │ │ │ │ └─────────────┘ └─────────────┘ └─────────────┘ │ │ │ │ │ │ │ │ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ │ │ │ │ Middleware │ │ Validators │ │ Jobs │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ - auth │ │ - zod │ │ - cron │ │ │ │ │ │ - audit │ │ - schemas │ │ - negative │ │ │ │ │ │ - tts verify│ │ │ │ flow │ │ │ │ │ └─────────────┘ └─────────────┘ └─────────────┘ │ │ │ │ │ │ │ └──────────────────────────────────────────────────────────────────┘ │ │ │ └─────────────────────────────────────────────────────────────────────────┘ │ ▼ ┌─────────────────────────────────────────────────────────────────────────┐ │ CAPA DE DATOS │ ├─────────────────────────────────────────────────────────────────────────┤ │ │ │ ┌──────────────────────────────────────────────────────────────────┐ │ │ │ PostgreSQL Database │ │ │ │ Puerto: 5432 │ │ │ ├──────────────────────────────────────────────────────────────────┤ │ │ │ │ │ │ │ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ │ │ │ │ users │ │ projects │ │concentrators│ │ │ │ │ │ roles │ │ gateways │ │ meters │ │ │ │ │ │ organismos │ │ │ │ │ │ │ │ │ └─────────────┘ └─────────────┘ └─────────────┘ │ │ │ │ │ │ │ │ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ │ │ │ │ readings │ │ devices │ │ audit_logs │ │ │ │ │ │ (meter_ │ │ tts_uplink │ │notifications│ │ │ │ │ │ readings) │ │ _logs │ │ │ │ │ │ │ └─────────────┘ └─────────────┘ └─────────────┘ │ │ │ │ │ │ │ └──────────────────────────────────────────────────────────────────┘ │ │ │ └─────────────────────────────────────────────────────────────────────────┘ │ ▼ ┌─────────────────────────────────────────────────────────────────────────┐ │ SERVICIOS EXTERNOS │ ├─────────────────────────────────────────────────────────────────────────┤ │ │ │ ┌──────────────────────────────────────────────────────────────────┐ │ │ │ The Things Stack (TTS) │ │ │ │ Plataforma LoRaWAN │ │ │ ├──────────────────────────────────────────────────────────────────┤ │ │ │ - Recepcion de uplinks de dispositivos │ │ │ │ - Gestion de dispositivos LoRaWAN │ │ │ │ - Webhooks hacia la API │ │ │ └──────────────────────────────────────────────────────────────────┘ │ │ │ └─────────────────────────────────────────────────────────────────────────┘ ``` --- ## Modelo de Datos ### Diagrama Entidad-Relacion ``` ┌─────────────┐ ┌─────────────────┐ ┌─────────────────┐ │ roles │ │ users │ │ projects │ ├─────────────┤ ├─────────────────┤ ├─────────────────┤ │ id (PK) │──┐ │ id (PK) │ ┌──│ id (PK) │ │ name │ └───▶│ role_id (FK) │ │ │ name │ │ description │ │ project_id (FK) │◀───┤ │ description │ │ permissions │ │ organismo_op_id │──┐ │ │ area_name │ └─────────────┘ │ email │ │ │ │ status │ │ password_hash │ │ │ │ organismo_op_id │──┐ │ name │ │ │ │ created_by (FK) │──▶ users │ is_active │ │ │ │ meter_type_id │──▶ meter_types └─────────────────┘ │ │ └─────────────────┘ │ │ │ │ ┌─────────────────┐ │ │ │ │ organismos_ │◀─┘─┼───────────────────────┘ │ operadores │ │ ├─────────────────┤ │ │ id (PK) │ │ │ name │ │ │ code │ │ │ contact_name │ │ │ contact_email │ │ │ is_active │ │ └─────────────────┘ │ │ │ │ │ ┌─────────────────┐ │ │ │ concentrators │◀───┘ │ ├─────────────────┤ │ │ id (PK) │ │ │ serial_number │ │ │ name │ │ │ project_id (FK) │◀───────────────┘ │ status │ │ ip_address │ └─────────────────┘ │ │ ┌────────┴────────┐ ▼ ▼ ┌─────────────────┐ ┌─────────────────┐ │ gateways │ │ meters │ ├─────────────────┤ ├─────────────────┤ │ id (PK) │ │ id (PK) │ │ gateway_id │ │ serial_number │ │ name │ │ name │ │ project_id (FK) │ │ project_id (FK) │ │ concentrator_id │ │ concentrator_id │ │ status │ │ device_id (FK) │──▶ devices └─────────────────┘ │ type │ │ │ status │ │ │ last_reading │ ▼ └─────────────────┘ ┌─────────────────┐ │ │ devices │ │ ├─────────────────┤ ▼ │ id (PK) │ ┌─────────────────┐ │ dev_eui │ │ meter_readings │ │ name │ ├─────────────────┤ │ project_id (FK) │ │ id (PK) │ │ gateway_id (FK) │ │ meter_id (FK) │ │ status │ │ reading_value │ └─────────────────┘ │ reading_type │ │ │ battery_level │ │ │ signal_strength │ ▼ │ received_at │ ┌─────────────────┐ └─────────────────┘ │ tts_uplink_logs │ ├─────────────────┤ │ id (PK) │ │ device_id (FK) │ │ raw_payload │ │ decoded_payload │ │ processed │ └─────────────────┘ ## Scope Filtering (Control de Acceso por Datos) Todos los servicios del backend aplican filtrado automatico basado en el rol del usuario autenticado: ``` ┌──────────────────────────────────────────────────────────┐ │ Scope Filtering │ ├──────────────────────────────────────────────────────────┤ │ │ │ ADMIN (roleName = 'ADMIN') │ │ └── Sin filtro, ve TODOS los registros │ │ │ │ ORGANISMO_OPERADOR (organismoOperadorId = X) │ │ └── WHERE project_id IN ( │ │ SELECT id FROM projects │ │ WHERE organismo_operador_id = X │ │ ) │ │ │ │ OPERATOR (projectId = Y) │ │ └── WHERE project_id = Y │ │ │ ├──────────────────────────────────────────────────────────┤ │ Utility: water-api/src/utils/scope.ts │ │ Se aplica en: meter, reading, project, user, │ │ concentrator, notification services │ └──────────────────────────────────────────────────────────┘ ``` --- ┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐ │ audit_logs │ │ notifications │ │ meter_types │ ├─────────────────┤ ├─────────────────┤ ├─────────────────┤ │ id (PK) │ │ id (PK) │ │ id (PK) │ │ user_id (FK) │ │ user_id (FK) │ │ name │ │ action │ │ meter_id (FK) │ │ code │ │ table_name │ │ type │ │ description │ │ record_id │ │ title │ │ is_active │ │ old_values │ │ message │ └─────────────────┘ │ new_values │ │ is_read │ └─────────────────┘ └─────────────────┘ ``` --- ## Descripcion de Tablas ### Tablas de Autenticacion y Usuarios #### `roles` | Campo | Tipo | Descripcion | |-------|------|-------------| | id | UUID | Identificador unico | | name | ENUM | ADMIN, ORGANISMO_OPERADOR, OPERATOR | | description | TEXT | Descripcion del rol | | permissions | JSONB | Permisos detallados | #### `users` | Campo | Tipo | Descripcion | |-------|------|-------------| | id | UUID | Identificador unico | | email | VARCHAR | Email unico (login) | | password_hash | VARCHAR | Hash bcrypt de contraseña | | name | VARCHAR | Nombre completo | | role_id | UUID FK | Rol asignado | | project_id | UUID FK | Proyecto asignado (OPERATOR) | | organismo_operador_id | UUID FK | Organismo asignado (ORGANISMO_OPERADOR) | | is_active | BOOLEAN | Estado de la cuenta | | last_login | TIMESTAMP | Ultimo acceso | #### `organismos_operadores` | Campo | Tipo | Descripcion | |-------|------|-------------| | id | UUID | Identificador unico | | name | VARCHAR | Nombre del organismo | | code | VARCHAR | Codigo unico (ej: CESPT-TJ) | | contact_name | VARCHAR | Nombre del contacto | | contact_email | VARCHAR | Email de contacto | | contact_phone | VARCHAR | Telefono de contacto | | is_active | BOOLEAN | Estado activo | | created_at | TIMESTAMP | Fecha de creacion | | updated_at | TIMESTAMP | Fecha de actualizacion | #### `refresh_tokens` | Campo | Tipo | Descripcion | |-------|------|-------------| | id | UUID | Identificador unico | | user_id | UUID FK | Usuario propietario | | token_hash | VARCHAR | Hash del token | | expires_at | TIMESTAMP | Fecha de expiracion | | revoked_at | TIMESTAMP | Fecha de revocacion | --- ### Tablas de Estructura #### `projects` | Campo | Tipo | Descripcion | |-------|------|-------------| | id | UUID | Identificador unico | | name | VARCHAR | Nombre del proyecto | | description | TEXT | Descripcion | | area_name | VARCHAR | Nombre del area | | location | TEXT | Ubicacion | | status | ENUM | ACTIVE, INACTIVE, COMPLETED | | organismo_operador_id | UUID FK | Organismo operador propietario | | meter_type_id | UUID FK | Tipo de medidor por defecto | | created_by | UUID FK | Usuario creador | #### `concentrators` | Campo | Tipo | Descripcion | |-------|------|-------------| | id | UUID | Identificador unico | | serial_number | VARCHAR | Numero de serie unico | | name | VARCHAR | Nombre descriptivo | | project_id | UUID FK | Proyecto asociado | | location | TEXT | Ubicacion fisica | | status | ENUM | Estado del concentrador | | ip_address | VARCHAR | Direccion IP | | firmware_version | VARCHAR | Version de firmware | | last_communication | TIMESTAMP | Ultima comunicacion | #### `gateways` | Campo | Tipo | Descripcion | |-------|------|-------------| | id | UUID | Identificador unico | | gateway_id | VARCHAR | ID unico del gateway | | name | VARCHAR | Nombre descriptivo | | project_id | UUID FK | Proyecto asociado | | concentrator_id | UUID FK | Concentrador asociado | | location | TEXT | Ubicacion | | status | ENUM | Estado | | tts_gateway_id | VARCHAR | ID en The Things Stack | --- ### Tablas de Medicion #### `meters` | Campo | Tipo | Descripcion | |-------|------|-------------| | id | UUID | Identificador unico | | serial_number | VARCHAR | Numero de serie unico | | name | VARCHAR | Nombre descriptivo | | project_id | UUID FK | Proyecto asociado | | concentrator_id | UUID FK | Concentrador asociado | | device_id | UUID FK | Dispositivo LoRaWAN asociado | | area_name | VARCHAR | Nombre del area | | location | TEXT | Ubicacion especifica | | type | VARCHAR | LORA, LORAWAN, GRANDES CONSUMIDORES | | status | ENUM | ACTIVE, INACTIVE, OFFLINE, MAINTENANCE, ERROR | | last_reading_value | NUMERIC | Ultima lectura registrada | | last_reading_at | TIMESTAMP | Fecha de ultima lectura | | installation_date | DATE | Fecha de instalacion | **Campos extendidos:** | Campo | Tipo | Descripcion | |-------|------|-------------| | protocol | VARCHAR | Protocolo de comunicacion | | mac | VARCHAR | Direccion MAC | | voltage | DECIMAL | Voltaje | | signal | INTEGER | Intensidad de senal | | leakage_status | VARCHAR | Estado de fuga | | burst_status | VARCHAR | Estado de ruptura | | current_flow | DECIMAL | Flujo actual | | latitude | DECIMAL | Latitud GPS | | longitude | DECIMAL | Longitud GPS | | data | JSONB | Datos adicionales flexibles | #### `meter_readings` | Campo | Tipo | Descripcion | |-------|------|-------------| | id | UUID | Identificador unico | | meter_id | UUID FK | Medidor asociado | | device_id | UUID FK | Dispositivo origen | | reading_value | NUMERIC | Valor de la lectura | | reading_type | ENUM | AUTOMATIC, MANUAL, SCHEDULED | | battery_level | SMALLINT | Nivel de bateria (0-100) | | signal_strength | SMALLINT | Intensidad de senal (dBm) | | raw_payload | TEXT | Payload crudo del dispositivo | | received_at | TIMESTAMP | Fecha/hora de recepcion | #### `meter_types` | Campo | Tipo | Descripcion | |-------|------|-------------| | id | UUID | Identificador unico | | name | VARCHAR | Nombre del tipo | | code | VARCHAR | Codigo unico | | description | TEXT | Descripcion | | is_active | BOOLEAN | Estado activo | --- ### Tablas de IoT (The Things Stack) #### `devices` | Campo | Tipo | Descripcion | |-------|------|-------------| | id | UUID | Identificador unico | | dev_eui | VARCHAR | DevEUI unico del dispositivo | | name | VARCHAR | Nombre descriptivo | | device_type | VARCHAR | Tipo de dispositivo | | project_id | UUID FK | Proyecto asociado | | gateway_id | UUID FK | Gateway asociado | | status | ENUM | Estado del dispositivo | | tts_device_id | VARCHAR | ID en TTS | | tts_status | VARCHAR | Estado en TTS | | app_key | VARCHAR | Application Key | | join_eui | VARCHAR | Join EUI | #### `tts_uplink_logs` | Campo | Tipo | Descripcion | |-------|------|-------------| | id | UUID | Identificador unico | | device_id | UUID FK | Dispositivo origen | | dev_eui | VARCHAR | DevEUI | | raw_payload | JSONB | Payload completo | | decoded_payload | JSONB | Payload decodificado | | gateway_ids | TEXT[] | IDs de gateways | | rssi | INTEGER | RSSI | | snr | FLOAT | SNR | | processed | BOOLEAN | Indica si fue procesado | | error_message | TEXT | Mensaje de error si aplica | --- ### Tablas de Sistema #### `audit_logs` | Campo | Tipo | Descripcion | |-------|------|-------------| | id | UUID | Identificador unico | | user_id | UUID FK | Usuario que realizo la accion | | user_email | VARCHAR | Email del usuario | | user_name | VARCHAR | Nombre del usuario | | action | ENUM | CREATE, UPDATE, DELETE, LOGIN, etc. | | table_name | VARCHAR | Tabla afectada | | record_id | UUID | ID del registro afectado | | old_values | JSONB | Valores anteriores | | new_values | JSONB | Valores nuevos | | description | TEXT | Descripcion de la accion | | ip_address | VARCHAR | IP del cliente | | user_agent | TEXT | User Agent del navegador | | success | BOOLEAN | Resultado de la operacion | | error_message | TEXT | Mensaje de error si fallo | #### `notifications` | Campo | Tipo | Descripcion | |-------|------|-------------| | id | UUID | Identificador unico | | user_id | UUID FK | Usuario destinatario | | meter_id | UUID FK | Medidor relacionado (opcional) | | notification_type | ENUM | NEGATIVE_FLOW, SYSTEM_ALERT, MAINTENANCE | | title | VARCHAR | Titulo de la notificacion | | message | TEXT | Mensaje detallado | | meter_serial_number | VARCHAR | Serial del medidor (si aplica) | | flow_value | DECIMAL | Valor de flujo (si aplica) | | is_read | BOOLEAN | Estado de lectura | | read_at | TIMESTAMP | Fecha de lectura | --- ## Indices ### Indices Principales ```sql -- Meters CREATE INDEX idx_meters_serial_number ON meters(serial_number); CREATE INDEX idx_meters_project_id ON meters(project_id); CREATE INDEX idx_meters_concentrator_id ON meters(concentrator_id); CREATE INDEX idx_meters_status ON meters(status); CREATE INDEX idx_meters_type ON meters(type); -- Readings CREATE INDEX idx_meter_readings_meter_id ON meter_readings(meter_id); CREATE INDEX idx_meter_readings_received_at ON meter_readings(received_at); CREATE INDEX idx_meter_readings_meter_id_received_at ON meter_readings(meter_id, received_at); -- Devices CREATE INDEX idx_devices_dev_eui ON devices(dev_eui); CREATE INDEX idx_devices_project_id ON devices(project_id); -- Audit CREATE INDEX idx_audit_logs_user_id ON audit_logs(user_id); CREATE INDEX idx_audit_logs_created_at ON audit_logs(created_at); CREATE INDEX idx_audit_logs_table_name ON audit_logs(table_name); -- Notifications CREATE INDEX idx_notifications_user_id ON notifications(user_id); CREATE INDEX idx_notifications_is_read ON notifications(is_read); ``` --- ## Flujo de Datos ### Flujo de Lectura Automatica (TTS) ``` 1. Dispositivo LoRaWAN envia uplink │ ▼ 2. The Things Stack recibe el mensaje │ ▼ 3. TTS envia webhook a /api/webhooks/tts/uplink │ ▼ 4. API verifica firma del webhook (X-Downlink-Apikey) │ ▼ 5. API guarda en tts_uplink_logs │ ▼ 6. API busca device por dev_eui │ ▼ 7. API busca meter asociado al device │ ▼ 8. API crea registro en meter_readings │ ▼ 9. API actualiza last_reading en meters │ ▼ 10. Job de deteccion de flujo negativo evalua la lectura │ ▼ 11. Si detecta anomalia, crea notification ``` ### Flujo de Carga CSV ``` 1. Usuario sube archivo CSV │ ▼ 2. API parsea el CSV │ ▼ 3. Por cada fila: │ ├─▶ Validar campos requeridos │ ├─▶ Buscar concentrador por serial │ ├─▶ Si meter existe: UPDATE │ Si no existe: INSERT │ └─▶ Registrar resultado (exito/error) │ ▼ 4. Retornar resumen de procesamiento ```