Modelo de Datos
Modelo entidad-relación completo para Certexi — 15 tablas principales, 25 índices, registro inmutable de eventos y patrones de derivación de estado computado.
Última actualización: 2025-02-18
Modelo de Datos
Certexi usa PostgreSQL con Drizzle ORM para acceso tipo-seguro a la base de datos. El esquema sigue principios de event-sourcing — el estado mutable siempre se deriva de un registro inmutable de eventos.
Visión General Entidad-Relación
Tablas Principales
operators
Los usuarios autenticados que interactúan con el sistema.
| Columna | Tipo | Descripción |
|---|---|---|
operator_id | text (PK) | Identificador único |
name | text | Nombre para mostrar |
email | text | Dirección de correo electrónico |
role | enum | operator, supervisor, admin |
nextcloud_id | text | Cuenta Nextcloud vinculada |
created_at | timestamp | Hora de creación de la cuenta |
last_active | timestamp | Marca de tiempo de última actividad |
events (Registro Inmutable)
Solo-Adición
La tabla de eventos es el registro inmutable. Las filas NUNCA se actualizan ni eliminan. Todo el estado se deriva de reproducir eventos en orden cronológico.
| Columna | Tipo | Descripción |
|---|---|---|
id | serial (PK) | ID auto-incremental |
event_type | enum | PLACED, REMOVED, VERIFIED, DISPUTED |
slot_id | int (FK) | Slot destino |
asset_barcode | text | Identificador del activo |
operator | text (FK) | Quién realizó la acción |
timestamp | timestamptz | Cuándo ocurrió |
photo_url | text | URL de la foto de evidencia |
scale_weight_kg | float | Peso al momento del evento |
evidence_hash | text | Hash SHA-256 del paquete de evidencia |
merkle_root | text | Raíz del árbol Merkle diario |
cctv_clip_url | text | Clip CCTV vinculado (opcional) |
work_order_ref | text | Referencia de orden de trabajo (opcional) |
workflow_stage_history
Rastrea cada transición de etapa para unidades de transporte.
| Columna | Tipo | Descripción |
|---|---|---|
id | serial (PK) | ID auto-incremental |
transport_unit_id | int (FK) | Referencia a unidad de transporte |
from_stage | text | Etapa anterior |
to_stage | text | Nueva etapa |
operator | text (FK) | Quién activó la transición |
evidence | json | Paquete de evidencia |
hash | text | Hash SHA-256 de la transición |
created_at | timestamptz | Hora de la transición |
Índices
El esquema incluye 25 índices optimizados para patrones de consulta comunes:
| Tabla | Índice | Columnas | Propósito |
|---|---|---|---|
| events | idx_events_slot_time | slot_id, timestamp | Consultas de historial de slot |
| events | idx_events_asset | asset_barcode | Búsqueda de ubicación de activo |
| events | idx_events_operator | operator, timestamp | Actividad del operador |
| events | idx_events_type_time | event_type, timestamp | Consultas filtradas por tipo |
| events | idx_events_time_geo | timestamp, location | Consultas de mapa por ventana de tiempo |
| slots | idx_slots_zone | zone | Listado de slots por zona |
| transport_units | idx_tu_stage | current_stage | Consultas Kanban |
| transport_units | idx_tu_operator | assigned_operator | Carga de trabajo del operador |
| workflow_history | idx_wh_tu | transport_unit_id | Historial de la unidad |
Derivación de Estado
El estado actual nunca se almacena directamente — se computa desde el registro de eventos:
Ocupación de Slot
-- Obtener la ocupación actual de un slot
SELECT DISTINCT ON (slot_id)
slot_id, event_type, asset_barcode, timestamp
FROM events
WHERE slot_id = $1
ORDER BY slot_id, timestamp DESC;
-- Si event_type = 'PLACED' → ocupado
-- Si event_type = 'REMOVED' → vacío
Ubicación de Activo
-- Encontrar dónde está ubicado actualmente un activo
SELECT DISTINCT ON (asset_barcode)
slot_id, event_type, timestamp
FROM events
WHERE asset_barcode = $1
ORDER BY asset_barcode, timestamp DESC;
Utilización de Zona
-- Calcular utilización por zona
WITH latest_events AS (
SELECT DISTINCT ON (slot_id)
s.zone, e.event_type
FROM events e
JOIN slots s ON s.id = e.slot_id
ORDER BY slot_id, e.timestamp DESC
)
SELECT zone,
COUNT(*) FILTER (WHERE event_type = 'PLACED') AS occupied,
COUNT(*) AS total
FROM latest_events
GROUP BY zone;
Migraciones
Drizzle ORM gestiona las migraciones del esquema con control de versiones:
# Generar migración a partir de cambios en el esquema
pnpm drizzle-kit generate:pg
# Aplicar migraciones pendientes
pnpm drizzle-kit push:pg
# Ver estado de migraciones
pnpm drizzle-kit status