# Auditoria historica y esquema actual

El snapshot inicial se conserva completo. El esquema vigente es el anexo de DATABASE_CHANGELOG, con 53 tablas y 16 migraciones.

# Auditoria completa PostgreSQL y modelo historico

Exportacion para revision: 05/11/2026, Europe/Madrid.
Contenido completo de los informes fuente, no resumen. No se alteran los originales.
Los nombres personales no imprescindibles se sustituyen por etiquetas; los ejemplos
tecnicos y la estructura se conservan. No se exportan credenciales ni evidencias crudas.
Las conclusiones, contadores y autorizaciones de cada informe son HISTORICOS:
no habilitan operaciones. El encargo actual prioriza exclusivamente enumeracion por fechas.
La fecha mas reciente y el suplemento de esta revision prevalecen sobre planes anteriores.

## Fuentes completas incluidas

| Fuente original en VM | SHA-256 original |
|---|---|
| [RUTA INTERNA OMITIDA] | 3ffe59689f0d98368d6d2d676202850cb6397976ef63b3a046761a4d34824ad3 |
| [RUTA INTERNA OMITIDA] | 3218f8f0c1878b8a8d4b274c65c0de121268b1ea1b8a5b2dfdae5926bb6ae02a |
| [RUTA INTERNA OMITIDA] | 3144a6404b3812ec7801f1c5ddd8de27576dbeb05a77cb3a95023e0f2da07cf9 |

---

## Documento fuente: 06_POSTGRESQL_AUDIT.md

# Auditoria PostgreSQL real

05/11/2026. SELECT en transacciones read-only; sin DDL, INSERT/UPDATE,
migraciones, importaciones ni reconciliaciones de indices documentales.

## Inventario

PostgreSQL 15.19 Debian, base transport_hub de 10 MB, esquema de usuario
public. 39 tablas, 386 columnas, 59 indices y 103 restricciones catalogadas.
13 migraciones aplicadas, desde 001_phase_0_5_schema hasta
013_iclientes_production. Inventario completo por columna/tipo/null/default,
indices y restricciones en el anexo 16; JSON de evidencia estructural sin
filas de secretos o datos comerciales en el dossier tecnico.

| Grupo | Tablas y filas reales |
|---|---|
| Usuarios/config | admin_users 2 (ambos admin activos), app_config 26, connector_credentials 1, iclientes_credentials 2 |
| Auditoria | api_requests 21, api_usage_daily 7, connection_tests 23, app_logs 2, technical_lessons 8 |
| Catalogos/plataforma | carriers 6, carrier_capabilities 8, known_api_endpoints 9, gls_event_codes 336, gls_product_matrix 24, gls_environments 3 |
| Documentacion | documents 31, knowledge_articles 31, official_documents 16 |
| Laboratorio | iclientes_lab_trials 3 (TEST aceptada, TEST borrador, PRODUCCION aceptada), queries 2, labels 4 |
| Impresion | print_printers 1, print_jobs 6, print_derivatives 5 |
| Politicas | cache_resources 7, enrichment_profiles 4, api_quota_policies 1 |
| Nucleo vacio | shipments 0, parcels 0, tracking_events 0, synchronizations 0 |
| Futuro vacio | alerts, cod_records, cod_settlements, invoices, invoice_lines, bank_movements, gls_allowed_services_cache: 0 |

Los auxiliares puntuales generaron evidencia/API requests sin crear queries
del laboratorio: queries=2 no es todo el historial de consultas. Ninguna
fila shipments confirma una expedicion porque el repositorio aun no se ingiere.

## Relaciones e indices: fortalezas y carencias

Carriers -> shipments/parcels/events/synchronizations; parcels -> shipments;
tracking_events -> parcels. Hay FKs y PKs, pero tracking_events no tiene
shipment_id directo y las claves carrier son nullable con ON DELETE SET NULL.
Eso no basta para historico inmutable con ambito de cuenta/entorno.

Shipments solo tiene id, carrier_id, external_reference, status, source_system
y timestamps. No hay cuentas, numero operativo, IDs tipados, cuatro ejes de
estado, destinatario, datos comerciales o revisiones. No hay unicidad fuerte
carrier/cuenta/entorno/tipo/valor, ni deduplicacion de eventos.

Shipments/tracking_events solo tienen indice PK. api_requests tiene indice
provider/product/environment/occurred_at, pero no carrier_account_id. Las
cuotas/agregados tampoco discriminan cuenta. No declarar el modelo definitivo
ya preparado por existir nombres de tablas.

Documents y knowledge_articles son indices de articulos tecnicos por slug/path,
no almacenes de POD/envios. No sobrecargarlos sin distinguir semantica. El
catalogo oficial separa originales del proveedor con hashes/procedencia.
La KB se descubre en ficheros: 54 articulos antes de esta entrega, frente
a 31 en SQL. Es una deuda de indexacion, no perdida de esos ficheros.

## Acceso y operacion

Conexion de app por socket local y peer como transport-hub. Rol no superuser,
sin CREATEDB ni CREATEROLE. El arranque ejecuta apply_migrations con ese rol;
propiedad/permisos DDL deben separarse del rol runtime en fase autorizada.
No se han probado permisos destructivos para verificarlo.

Listen localhost, SSL activado para TCP local; puerto 5432 solo loopback.
max_connections=100, shared_buffers=128 MiB, autovacuum on, wal_level replica,
archive_mode off. Son valores observados, no recomendacion de tuning ni
prueba de backup/replicacion. No se midio rendimiento de cargas grandes.

## Reutilizacion propuesta

Extender nucleo existente mediante futuras migraciones revisadas; mantener
usuarios/config/catalogos/auditoria y evidencias. Anadir solo conceptos que
faltan: cuentas, IDs/referencias, snapshots/comercial, observaciones/revisiones,
documentos de envio y operaciones/jobs segun diseno. Evitar otro nucleo GLS.
Laboratorio no debe convertirse en tabla operativa mediante UPDATEs ad hoc.

Antes de poblar: backup restaurable, reglas de identidad y estados, migracion
reversible, pruebas con datos sinteticos e ingesta offline de evidencia
autorizada. Ninguna de estas acciones se realiza en esta auditoria.


---

## Documento fuente: 08_HISTORICAL_REPOSITORY_DESIGN.md

# Repositorio historico persistente: propuesta

No DDL ni implementacion. Evolucionar la unica base transport_hub y el
nucleo existente. No limitar a 100 registros ni aplicar purgas automaticas
por antiguedad. Conservacion comercial completa, con decisiones explicitas
de gobierno de datos/obligaciones aplicables; capacidad indefinida no es
una orden de conservar PII eternamente sin revision.

## Agrupaciones y relaciones necesarias

| Conceptos | Entidad propuesta / reutilizacion |
|---|---|
| Carrier | carriers existente, sin implementar otros proveedores. |
| Cuenta/entorno/secretos | carrier_accounts nueva; IDs de cuenta y secret_ref, nunca UID/Secret en claro; adapter al mecanismo actual. |
| Expedicion | Extender shipments, no tabla GLS paralela. ID TH estable; carrier_account_id, environment y numero externo visible. |
| Identificadores | shipment_identifiers con namespace/tipo/valor/cuenta; indices de lookup y unicidad solo de IDs fuertes en su ambito. |
| Referencias comerciales | shipment_references: pedido, factura, ERP, TH y C/0/S/otros GLS, con tipo/valor/fuente. Multiples referencias por envio y viceversa. |
| Remitente/destinatario/contactos | Snapshot por rol ligado a envio, no libreta de clientes paralela a intranet. Centros de expedicion configurables futuros, snapshot inmutable de lo usado. |
| Servicio/condiciones | Codigos/literales originales y snapshot comercial dentro del agregado; tablas de catalogo solo donde eviten duplicacion real. |
| Bultos | parcels existente, IDs por bulto, pesos/medidas declarados y observados con fuente. |
| Eventos/incidencias/entregas | tracking_events ampliada con shipment_id, tipo/namespace y revisiones; vistas/proyecciones, no tabla por cada literal. |
| Reembolso | cod_records existente ampliable; importes declarados/moneda/procedencia. No inferir cobro/liquidacion por entrega. |
| Documentos envio/POD/etiquetas | shipment_documents como metadata/relacion a activos privados; reutilizar cifrado/hash/lectura de originales. documents/knowledge_articles actuales siguen siendo indices tecnicos, no otro nucleo de envios. |
| Operaciones administrativas | shipment_operations: intencion/aceptacion/rechazo/desconocido y evidencias; separadas de api_requests (una operacion puede tener varios HTTP). |
| Observaciones | shipment_observations append-only, snapshot normalizado versionado y referencia al original cifrado. |
| Sincronizacion/importacion | Extender synchronizations + items/checkpoints/leases si hacen falta; un motor comun, no nuevo sistema por conector. |
| Cuotas/auditoria/errores | api_requests/api_usage_daily/api_quota_policies/alerts existentes, con cuenta/origen/coste y estado de captura/ingesta. |

No se decide crear 25 tablas por 25 conceptos. El detalle fisico y migracion
se revisara tras aprobar identidad, privacidad y consultas principales.

## Identidad y unicidad

Numero completo GLS es la primera columna operativa y campo de API, no la
PK. Si GLS lo devuelve se preserva literal; no construir agencia+CODEXP
para cualquier envio sin regla validada. Codigos como cadenas, ceros intactos.
Clave fuerte: carrier/cuenta/entorno/tipo/valor; CODEXP no globalmente unico.
GUID, barcode y numero de bulto se tipan y correlacionan con evidencia.

No fusionar por nombre, direccion, albaran o referencia no unica. Devoluciones
pueden heredar referencias y tener ID propio. Relacion padre/hija tipada,
no sobrescribir el original. Conflictos de identificadores van a revision,
no upsert silencioso. Cuenta anterior nunca comparte UID por suposicion.

## Campos funcionales y comerciales

Shipment: fecha original expedicion y observaciones, servicio/horario/portes,
bultos/peso/moneda, pais, centro remitente, origen transport_hub/gls_extranet/
imported/unknown y grado de confianza. Lookup API no demuestra canal de alta.

Snapshot de destinatario: nombre, direccion, CP, poblacion, provincia, pais,
telefono, movil, correo y observaciones, todos con fuente y campos presentes.
Null/desconocido/no devuelto no equivale a cadena vacia o cero. Nunca inventar
contactos que no entregue GLS/intranet. Direccion snapshot no cambia al
editar despues un centro o cliente en intranet.

Referencias pedido/factura/ERP no crean facturas ni conciliacion; intranet
conserva autoridad sobre sus datos comerciales. Prioridad de fuentes
configurable por campo, sin sobreescribir ERP con una respuesta parcial GLS.
Reembolso declarado con decimal exacto y moneda original; estado de cobro,
importe cobrado y conciliacion desconocidos hasta evidencia especifica.

## Cuatro dimensiones de estado

| Dimension | Valores conceptuales y reglas |
|---|---|
| Administrativo | creada, anulacion_solicitada, anulacion_aceptada, anulacion_rechazada, desconocida. No confundir estado del request HTTP con resultado de negocio. |
| Logistico | grabada, recogida, en_transito, en_reparto, entregada, incidencia, devuelta, desconocido; derivados de codigos/tipos/versiones acreditados. |
| Ciclo | abierta, finalizada, cerrada, desconocida; terminalidad respaldada por fuente/motivo, no por antiguedad. |
| Resultado | entregada, anulada, devuelta, otros, pendiente/desconocido. Cierre definitivo no asigna entregada. |

TH-P-001 valida: admin aceptada por Anula0; logistica API inmediata grabada;
resultado anulada y ciclo cerrado comunicados desde Extranet despues.
Almacenar cada observacion y su nivel de evidencia, no fabricar evento API5.
La reduccion de bultos/peso a 0 comunicada de Extranet es snapshot posterior,
no borrado del valor declarado original 1/1 ni prueba de ausencia de costes.

## Acontecimientos y correcciones

Conservar tipo, codigo y descripcion originales, API/catalogo/namespace,
fecha literal y timezone conocida o incertidumbre, agencia/instalacion,
atributos complementarios, fecha de recuperacion e ID externo si existe.
ESTADO, ENTREGA, POD, INCIDENCIA, SOLUCION, FACTURA y demas tipos no comparten
un espacio numerico. LocationCode no es estado. Evento administrativo posterior
no devuelve una entregada a pendiente por ser el mas reciente.

ID externo estable permite enlazar revisiones. Si falta, huella canonica
por envio/tipo/codigo/fecha/instalacion identifica ocurrencia candidata y
hash de contenido distingue cambios. No deduplicar solo por codigo.
Una correccion genera revision/supersedes y nueva observacion, no UPDATE
destructivo del acontecimiento historico. Si dos ocurrencias pueden coincidir
sin ID estable, conservar incertidumbre/original y evitar fusion automatica.

Los primeros/ultimos vistos, fecha efectiva, fecha carrier y fecha de ingesta
son distintos. Versionar parser/mapeo y reprocesar originales localmente
sin volver a gastar llamadas. Desconocidos conservan datos, no se descartan.

Una recuperacion nueva de un evento antiguo no reabre automaticamente un
envio cerrado: ordenar por evidencia/fecha efectiva, no solo fecha de HTTP.
TH-P-001 exige conservar Anula0 y el cierre Extranet posterior aunque una
respuesta inmediata aun repita el evento GRABADO del dia anterior. Prioridad
entre fuentes y resolucion de conflictos debe ser explicita y versionada.

## Captura, ingesta e integridad

HTTP real -> captura original cifrada duradera -> auditoria -> validacion/
parser tipado -> transaccion de agregado/eventos/referencias/snapshot.
Si falla BD/parser, inbox duradera para reprocesado LOCAL; no repetir HTTP
para obtener de nuevo una respuesta ya conservada. Registrar recibido,
procesado, aplicado y error por separado. Toda respuesta util autorizada
alimenta el repositorio, incluidas altas/consultas manuales, con completitud.

No ingestar desde la copia sanitizada: el sanitizador actual limita listas
y oculta PII/fechas que si son necesarias al modelo. Original cifrado es
la evidencia autorizada; log/copia tecnica no es fuente completa de negocio.
Vacio de lookup no prueba borrado; no eliminar una observacion positiva previa.

## Activos privados

Original bytes inmutable, SHA-256, MIME detectado, tamano, cuenta/envio,
operacion/fuente, fechas, formato, numero de paginas/dimensiones cuando aplica,
estado de integridad, clave/version de cifrado y ruta privada. Nunca ruta
arbitraria proporcionada por cliente ni descarga publica del filesystem.

Deduplicar binarios por hash dentro de dominio autorizado; multiples vinculos
metadata al mismo activo sin filtrar existencia entre cuentas. Version nueva
de POD/etiqueta crea activo nuevo, no reemplaza original. Referencias URL
privadas/firmadas no se confunden con POD descargado/verificado.

## Indices, privacidad y retencion

Indices futuros sobre cuenta/entorno/numero, IDs fuertes, fecha, estado,
terminalidad, next_sync_at y eventos envio/fecha. Referencias tipadas con
indice de busqueda, no unicidad universal. Paginacion local estable por
fecha+ID. Particionar solo con volumen/medicion, no una tabla por mes ahora.

Telefonos/movil/email y direccion sensible requieren cifrado/acceso por campo;
nombre/CP/poblacion para busqueda parcial necesitan una decision explicita:
propuesta de columnas operativas minimas indexables con roles restringidos,
cifrado de volumen/backups, resto sensible cifrado. Fernet no permite ILIKE
sobre su ciphertext. No prometer busqueda parcial cifrada sin diseno aparte.

Historico sin purga automatica. TTL de frescura no borra registros ni prueba
retencion de GLS. Gobierno de datos define revisiones, anonimizar/eliminar
si corresponde y evidencia de la decision, sin ocultar trazabilidad tecnica.


---

## Documento fuente: 16_POSTGRESQL_SCHEMA_INVENTORY.md

# Inventario completo PostgreSQL observado

05/11/2026. Solo lectura de information_schema/pg_catalog. 39 tablas,386 columnas,59 indices,103 restricciones; definiciones documentales, NO script de migracion.

## admin_users

Filas observadas: 2.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('admin_users_id_seq'::regclass) |
| username | text | NO | - |
| password_hash | text | NO | - |
| created_at | timestamp with time zone | NO | now() |
| last_login_at | timestamp with time zone | YES | - |
| display_name | text | NO | ''::text |
| email | text | NO | ''::text |
| role | text | NO | 'admin'::text |
| active | boolean | NO | true |
| updated_at | timestamp with time zone | NO | now() |

Indices:
- admin_users_pkey: `CREATE UNIQUE INDEX admin_users_pkey ON public.admin_users USING btree (id)`
- admin_users_username_key: `CREATE UNIQUE INDEX admin_users_username_key ON public.admin_users USING btree (username)`
- idx_admin_users_active_role: `CREATE INDEX idx_admin_users_active_role ON public.admin_users USING btree (active, role)`

Restricciones:
- admin_users_pkey [p]: `PRIMARY KEY (id)`
- admin_users_username_key [u]: `UNIQUE (username)`

## alerts

Filas observadas: 0.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('alerts_id_seq'::regclass) |
| severity | text | NO | 'info'::text |
| scope | text | NO | 'system'::text |
| title | text | NO | - |
| message | text | NO | - |
| status | text | NO | 'open'::text |
| created_at | timestamp with time zone | NO | now() |
| resolved_at | timestamp with time zone | YES | - |

Indices:
- alerts_pkey: `CREATE UNIQUE INDEX alerts_pkey ON public.alerts USING btree (id)`

Restricciones:
- alerts_pkey [p]: `PRIMARY KEY (id)`

## api_quota_policies

Filas observadas: 1.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| product | text | NO | - |
| environment | text | NO | - |
| period | text | NO | - |
| limit_count | integer | NO | - |
| limit_source | text | NO | - |
| reserve_count | integer | NO | 0 |
| warn_thresholds | jsonb | NO | '[50, 75, 90, 100]'::jsonb |
| active | boolean | NO | true |
| updated_at | timestamp with time zone | NO | now() |

Indices:
- api_quota_policies_pkey: `CREATE UNIQUE INDEX api_quota_policies_pkey ON public.api_quota_policies USING btree (product, environment, period)`

Restricciones:
- api_quota_policies_pkey [p]: `PRIMARY KEY (product, environment, period)`

## api_requests

Filas observadas: 21.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('api_requests_id_seq'::regclass) |
| provider | text | NO | - |
| product | text | NO | - |
| environment | text | NO | - |
| endpoint_logical | text | NO | - |
| method | text | NO | - |
| occurred_at | timestamp with time zone | NO | now() |
| http_code | integer | YES | - |
| duration_ms | integer | NO | 0 |
| result | text | NO | - |
| items_requested | integer | NO | 0 |
| items_returned | integer | NO | 0 |
| origin | text | NO | - |
| actor | text | YES | - |
| correlation_id | uuid | YES | - |
| quota_computed | numeric | YES | - |
| error_sanitized | text | YES | - |
| details_json | jsonb | NO | '{}'::jsonb |

Indices:
- api_requests_pkey: `CREATE UNIQUE INDEX api_requests_pkey ON public.api_requests USING btree (id)`
- idx_api_requests_usage: `CREATE INDEX idx_api_requests_usage ON public.api_requests USING btree (provider, product, environment, occurred_at DESC)`

Restricciones:
- api_requests_pkey [p]: `PRIMARY KEY (id)`

## api_usage_daily

Filas observadas: 7.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| usage_date | date | NO | - |
| product | text | NO | - |
| environment | text | NO | - |
| requests | integer | NO | 0 |
| successes | integer | NO | 0 |
| errors | integer | NO | 0 |
| items_processed | integer | NO | 0 |
| avg_duration_ms | numeric | NO | 0 |
| updated_at | timestamp with time zone | NO | now() |

Indices:
- api_usage_daily_pkey: `CREATE UNIQUE INDEX api_usage_daily_pkey ON public.api_usage_daily USING btree (usage_date, product, environment)`

Restricciones:
- api_usage_daily_pkey [p]: `PRIMARY KEY (usage_date, product, environment)`

## app_config

Filas observadas: 26.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| key | text | NO | - |
| value | text | NO | - |
| type | text | NO | 'string'::text |
| updated_at | timestamp with time zone | NO | now() |

Indices:
- app_config_pkey: `CREATE UNIQUE INDEX app_config_pkey ON public.app_config USING btree (key)`

Restricciones:
- app_config_pkey [p]: `PRIMARY KEY (key)`

## app_logs

Filas observadas: 2.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('app_logs_id_seq'::regclass) |
| level | text | NO | - |
| event | text | NO | - |
| message | text | NO | - |
| user_id | bigint | YES | - |
| details_json | jsonb | NO | '{}'::jsonb |
| created_at | timestamp with time zone | NO | now() |

Indices:
- app_logs_pkey: `CREATE UNIQUE INDEX app_logs_pkey ON public.app_logs USING btree (id)`

Restricciones:
- app_logs_pkey [p]: `PRIMARY KEY (id)`
- app_logs_user_id_fkey [f]: `FOREIGN KEY (user_id) REFERENCES admin_users(id) ON DELETE SET NULL`

## app_migrations

Filas observadas: 13.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| version | text | NO | - |
| applied_at | timestamp with time zone | NO | now() |

Indices:
- app_migrations_pkey: `CREATE UNIQUE INDEX app_migrations_pkey ON public.app_migrations USING btree (version)`

Restricciones:
- app_migrations_pkey [p]: `PRIMARY KEY (version)`

## bank_movements

Filas observadas: 0.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('bank_movements_id_seq'::regclass) |
| movement_date | date | YES | - |
| concept | text | YES | - |
| amount | numeric | YES | - |
| currency | text | NO | 'EUR'::text |
| matched_status | text | NO | 'unmatched'::text |
| created_at | timestamp with time zone | NO | now() |

Indices:
- bank_movements_pkey: `CREATE UNIQUE INDEX bank_movements_pkey ON public.bank_movements USING btree (id)`

Restricciones:
- bank_movements_pkey [p]: `PRIMARY KEY (id)`

## cache_resources

Filas observadas: 7.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('cache_resources_id_seq'::regclass) |
| resource_key | text | NO | - |
| carrier_code | text | NO | 'generic'::text |
| resource_name | text | NO | - |
| origin | text | NO | 'CACHE'::text |
| ttl_seconds | integer | NO | 0 |
| policy | text | NO | 'manual'::text |
| last_obtained_at | timestamp with time zone | YES | - |
| expires_at | timestamp with time zone | YES | - |
| enabled | boolean | NO | true |
| updated_at | timestamp with time zone | NO | now() |

Indices:
- cache_resources_pkey: `CREATE UNIQUE INDEX cache_resources_pkey ON public.cache_resources USING btree (id)`
- cache_resources_resource_key_key: `CREATE UNIQUE INDEX cache_resources_resource_key_key ON public.cache_resources USING btree (resource_key)`

Restricciones:
- cache_resources_pkey [p]: `PRIMARY KEY (id)`
- cache_resources_resource_key_key [u]: `UNIQUE (resource_key)`

## carrier_capabilities

Filas observadas: 8.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('carrier_capabilities_id_seq'::regclass) |
| carrier_id | bigint | NO | - |
| capability | text | NO | - |
| status | text | NO | 'planned'::text |
| depends_on | text | NO | ''::text |
| notes | text | NO | ''::text |
| updated_at | timestamp with time zone | NO | now() |

Indices:
- carrier_capabilities_carrier_id_capability_key: `CREATE UNIQUE INDEX carrier_capabilities_carrier_id_capability_key ON public.carrier_capabilities USING btree (carrier_id, capability)`
- carrier_capabilities_pkey: `CREATE UNIQUE INDEX carrier_capabilities_pkey ON public.carrier_capabilities USING btree (id)`

Restricciones:
- carrier_capabilities_carrier_id_capability_key [u]: `UNIQUE (carrier_id, capability)`
- carrier_capabilities_carrier_id_fkey [f]: `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE CASCADE`
- carrier_capabilities_pkey [p]: `PRIMARY KEY (id)`

## carriers

Filas observadas: 6.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('carriers_id_seq'::regclass) |
| code | text | NO | - |
| name | text | NO | - |
| status | text | NO | 'planned'::text |
| notes | text | NO | ''::text |
| created_at | timestamp with time zone | NO | now() |
| updated_at | timestamp with time zone | NO | now() |

Indices:
- carriers_code_key: `CREATE UNIQUE INDEX carriers_code_key ON public.carriers USING btree (code)`
- carriers_pkey: `CREATE UNIQUE INDEX carriers_pkey ON public.carriers USING btree (id)`

Restricciones:
- carriers_code_key [u]: `UNIQUE (code)`
- carriers_pkey [p]: `PRIMARY KEY (id)`

## cod_records

Filas observadas: 0.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('cod_records_id_seq'::regclass) |
| shipment_id | bigint | YES | - |
| parcel_id | bigint | YES | - |
| carrier_id | bigint | YES | - |
| amount | numeric | YES | - |
| currency | text | NO | 'EUR'::text |
| status | text | NO | 'pending'::text |
| created_at | timestamp with time zone | NO | now() |
| updated_at | timestamp with time zone | NO | now() |

Indices:
- cod_records_pkey: `CREATE UNIQUE INDEX cod_records_pkey ON public.cod_records USING btree (id)`

Restricciones:
- cod_records_carrier_id_fkey [f]: `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL`
- cod_records_parcel_id_fkey [f]: `FOREIGN KEY (parcel_id) REFERENCES parcels(id) ON DELETE SET NULL`
- cod_records_pkey [p]: `PRIMARY KEY (id)`
- cod_records_shipment_id_fkey [f]: `FOREIGN KEY (shipment_id) REFERENCES shipments(id) ON DELETE SET NULL`

## cod_settlements

Filas observadas: 0.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('cod_settlements_id_seq'::regclass) |
| carrier_id | bigint | YES | - |
| settlement_reference | text | YES | - |
| period_start | date | YES | - |
| period_end | date | YES | - |
| amount | numeric | YES | - |
| status | text | NO | 'pending'::text |
| created_at | timestamp with time zone | NO | now() |
| updated_at | timestamp with time zone | NO | now() |

Indices:
- cod_settlements_pkey: `CREATE UNIQUE INDEX cod_settlements_pkey ON public.cod_settlements USING btree (id)`

Restricciones:
- cod_settlements_carrier_id_fkey [f]: `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL`
- cod_settlements_pkey [p]: `PRIMARY KEY (id)`

## connection_tests

Filas observadas: 23.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('connection_tests_id_seq'::regclass) |
| test_type | text | NO | - |
| provider | text | NO | - |
| environment | text | NO | - |
| started_at | timestamp with time zone | NO | - |
| finished_at | timestamp with time zone | NO | - |
| result | text | NO | - |
| http_code | integer | YES | - |
| sanitized_message | text | NO | - |
| duration_ms | integer | NO | - |
| details_json | jsonb | NO | '{}'::jsonb |
| launched_by | bigint | YES | - |
| endpoint_logical | text | YES | - |
| diagnostic_run_id | uuid | YES | - |
| classification | text | YES | - |

Indices:
- connection_tests_pkey: `CREATE UNIQUE INDEX connection_tests_pkey ON public.connection_tests USING btree (id)`
- idx_connection_tests_recent: `CREATE INDEX idx_connection_tests_recent ON public.connection_tests USING btree (provider, environment, started_at DESC)`

Restricciones:
- connection_tests_launched_by_fkey [f]: `FOREIGN KEY (launched_by) REFERENCES admin_users(id) ON DELETE SET NULL`
- connection_tests_pkey [p]: `PRIMARY KEY (id)`

## connector_credentials

Filas observadas: 1.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| provider | text | NO | - |
| environment | text | NO | - |
| client_id_ciphertext | text | NO | - |
| client_secret_ciphertext | text | NO | - |
| created_at | timestamp with time zone | NO | now() |
| updated_at | timestamp with time zone | NO | now() |
| last_validated_at | timestamp with time zone | YES | - |
| validation_status | text | NO | 'configured_not_tested'::text |

Indices:
- connector_credentials_pkey: `CREATE UNIQUE INDEX connector_credentials_pkey ON public.connector_credentials USING btree (provider, environment)`

Restricciones:
- connector_credentials_pkey [p]: `PRIMARY KEY (provider, environment)`

## documents

Filas observadas: 31.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('documents_id_seq'::regclass) |
| slug | text | NO | - |
| title | text | NO | - |
| path | text | NO | - |
| document_type | text | NO | 'markdown'::text |
| category | text | NO | 'General'::text |
| checksum | text | YES | - |
| updated_at | timestamp with time zone | NO | now() |

Indices:
- documents_pkey: `CREATE UNIQUE INDEX documents_pkey ON public.documents USING btree (id)`
- documents_slug_key: `CREATE UNIQUE INDEX documents_slug_key ON public.documents USING btree (slug)`

Restricciones:
- documents_pkey [p]: `PRIMARY KEY (id)`
- documents_slug_key [u]: `UNIQUE (slug)`

## enrichment_profiles

Filas observadas: 4.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('enrichment_profiles_id_seq'::regclass) |
| carrier_code | text | NO | 'generic'::text |
| level | text | NO | - |
| description | text | NO | ''::text |
| estimated_extra_calls | integer | NO | 0 |
| rules_json | jsonb | NO | '{}'::jsonb |
| active | boolean | NO | true |
| updated_at | timestamp with time zone | NO | now() |

Indices:
- enrichment_profiles_carrier_code_level_key: `CREATE UNIQUE INDEX enrichment_profiles_carrier_code_level_key ON public.enrichment_profiles USING btree (carrier_code, level)`
- enrichment_profiles_pkey: `CREATE UNIQUE INDEX enrichment_profiles_pkey ON public.enrichment_profiles USING btree (id)`

Restricciones:
- enrichment_profiles_carrier_code_level_key [u]: `UNIQUE (carrier_code, level)`
- enrichment_profiles_pkey [p]: `PRIMARY KEY (id)`

## gls_allowed_services_cache

Filas observadas: 0.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('gls_allowed_services_cache_id_seq'::regclass) |
| origin | text | NO | - |
| destination | text | NO | - |
| contact_id | text | YES | - |
| product | text | YES | - |
| service | text | YES | - |
| queried_at | timestamp with time zone | NO | now() |
| response_hash | text | NO | - |
| active | boolean | NO | true |

Indices:
- gls_allowed_services_cache_origin_destination_contact_id_pr_key: `CREATE UNIQUE INDEX gls_allowed_services_cache_origin_destination_contact_id_pr_key ON public.gls_allowed_services_cache USING btree (origin, destination, contact_id, product, service)`
- gls_allowed_services_cache_pkey: `CREATE UNIQUE INDEX gls_allowed_services_cache_pkey ON public.gls_allowed_services_cache USING btree (id)`

Restricciones:
- gls_allowed_services_cache_origin_destination_contact_id_pr_key [u]: `UNIQUE (origin, destination, contact_id, product, service)`
- gls_allowed_services_cache_pkey [p]: `PRIMARY KEY (id)`

## gls_environments

Filas observadas: 3.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| code | text | NO | - |
| name | text | NO | - |
| label | text | NO | - |
| active | boolean | NO | false |
| locked | boolean | NO | true |
| state | text | NO | 'sin_validar'::text |
| last_validated_at | timestamp with time zone | YES | - |
| oauth_urls | jsonb | NO | '{}'::jsonb |
| base_urls | jsonb | NO | '{}'::jsonb |
| enabled_apis | jsonb | NO | '[]'::jsonb |
| backend_authorization | jsonb | NO | '{}'::jsonb |
| limits_json | jsonb | NO | '{}'::jsonb |
| config_json | jsonb | NO | '{}'::jsonb |
| observations | text | NO | ''::text |
| created_at | timestamp with time zone | NO | now() |
| updated_at | timestamp with time zone | NO | now() |

Indices:
- gls_environments_pkey: `CREATE UNIQUE INDEX gls_environments_pkey ON public.gls_environments USING btree (code)`

Restricciones:
- gls_environments_pkey [p]: `PRIMARY KEY (code)`

## gls_event_codes

Filas observadas: 336.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| code | text | NO | - |
| language | text | NO | - |
| description | text | NO | - |
| first_seen_at | timestamp with time zone | NO | now() |
| last_seen_at | timestamp with time zone | NO | now() |
| active | boolean | NO | true |
| raw_hash | text | NO | - |

Indices:
- gls_event_codes_pkey: `CREATE UNIQUE INDEX gls_event_codes_pkey ON public.gls_event_codes USING btree (code, language)`

Restricciones:
- gls_event_codes_pkey [p]: `PRIMARY KEY (code, language)`

## gls_product_matrix

Filas observadas: 24.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| product | text | NO | - |
| environment | text | NO | - |
| status | text | NO | 'sin_validar'::text |
| last_test_id | bigint | YES | - |
| evidence | text | NO | ''::text |
| pending | text | NO | ''::text |
| credential_ref_provider | text | YES | - |
| credential_ref_environment | text | YES | - |
| updated_at | timestamp with time zone | NO | now() |

Indices:
- gls_product_matrix_pkey: `CREATE UNIQUE INDEX gls_product_matrix_pkey ON public.gls_product_matrix USING btree (product, environment)`

Restricciones:
- gls_product_matrix_environment_fkey [f]: `FOREIGN KEY (environment) REFERENCES gls_environments(code)`
- gls_product_matrix_last_test_id_fkey [f]: `FOREIGN KEY (last_test_id) REFERENCES connection_tests(id) ON DELETE SET NULL`
- gls_product_matrix_pkey [p]: `PRIMARY KEY (product, environment)`

## iclientes_credentials

Filas observadas: 2.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| environment | text | NO | - |
| secret_ref | text | NO | - |
| description | text | NO | ''::text |
| created_at | timestamp with time zone | NO | now() |
| updated_at | timestamp with time zone | NO | now() |

Indices:
- iclientes_credentials_pkey: `CREATE UNIQUE INDEX iclientes_credentials_pkey ON public.iclientes_credentials USING btree (environment)`
- iclientes_credentials_secret_ref_key: `CREATE UNIQUE INDEX iclientes_credentials_secret_ref_key ON public.iclientes_credentials USING btree (secret_ref)`

Restricciones:
- iclientes_credentials_environment_check [c]: `CHECK ((environment = ANY (ARRAY['test'::text, 'production'::text])))`
- iclientes_credentials_pkey [p]: `PRIMARY KEY (environment)`
- iclientes_credentials_secret_ref_key [u]: `UNIQUE (secret_ref)`

## iclientes_lab_labels

Filas observadas: 4.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | uuid | NO | - |
| trial_id | uuid | NO | - |
| mime_type | text | NO | - |
| size_bytes | integer | NO | - |
| sha256 | text | NO | - |
| encrypted_path | text | NO | - |
| created_at | timestamp with time zone | NO | now() |
| image_width | integer | YES | - |
| image_height | integer | YES | - |
| declared_dpi | jsonb | YES | - |
| source_metadata | jsonb | NO | '{}'::jsonb |

Indices:
- iclientes_lab_labels_pkey: `CREATE UNIQUE INDEX iclientes_lab_labels_pkey ON public.iclientes_lab_labels USING btree (id)`

Restricciones:
- iclientes_lab_labels_pkey [p]: `PRIMARY KEY (id)`
- iclientes_lab_labels_trial_id_fkey [f]: `FOREIGN KEY (trial_id) REFERENCES iclientes_lab_trials(id)`

## iclientes_lab_queries

Filas observadas: 2.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | uuid | NO | - |
| trial_id | uuid | NO | - |
| created_by | integer | NO | - |
| nonce_hash | text | NO | - |
| state | text | NO | 'sending'::text |
| created_at | timestamp with time zone | NO | now() |
| finished_at | timestamp with time zone | YES | - |
| api_request_id | bigint | YES | - |
| http_status | integer | YES | - |
| duration_ms | integer | YES | - |
| evidence_original | text | YES | - |
| evidence_sanitized | text | YES | - |
| summary | jsonb | NO | '{}'::jsonb |
| operation | text | NO | 'GetExpCli'::text |

Indices:
- iclientes_lab_queries_pkey: `CREATE UNIQUE INDEX iclientes_lab_queries_pkey ON public.iclientes_lab_queries USING btree (id)`
- iclientes_lab_queries_trial_id_nonce_hash_key: `CREATE UNIQUE INDEX iclientes_lab_queries_trial_id_nonce_hash_key ON public.iclientes_lab_queries USING btree (trial_id, nonce_hash)`

Restricciones:
- iclientes_lab_queries_api_request_id_fkey [f]: `FOREIGN KEY (api_request_id) REFERENCES api_requests(id)`
- iclientes_lab_queries_created_by_fkey [f]: `FOREIGN KEY (created_by) REFERENCES admin_users(id)`
- iclientes_lab_queries_operation_check [c]: `CHECK ((operation = ANY (ARRAY['GetExpCli'::text, 'EtiquetaEnvioV2'::text])))`
- iclientes_lab_queries_pkey [p]: `PRIMARY KEY (id)`
- iclientes_lab_queries_state_check [c]: `CHECK ((state = ANY (ARRAY['sending'::text, 'complete'::text, 'unknown'::text, 'not_sent'::text])))`
- iclientes_lab_queries_trial_id_fkey [f]: `FOREIGN KEY (trial_id) REFERENCES iclientes_lab_trials(id)`
- iclientes_lab_queries_trial_id_nonce_hash_key [u]: `UNIQUE (trial_id, nonce_hash)`

## iclientes_lab_trials

Filas observadas: 3.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | uuid | NO | - |
| environment | text | NO | 'test'::text |
| reference | text | NO | - |
| created_by | integer | NO | - |
| form_ciphertext | text | NO | - |
| confirm_hash | text | NO | - |
| state | text | NO | 'draft'::text |
| created_at | timestamp with time zone | NO | now() |
| submitted_at | timestamp with time zone | YES | - |
| finished_at | timestamp with time zone | YES | - |
| api_request_id | bigint | YES | - |
| http_status | integer | YES | - |
| duration_ms | integer | YES | - |
| result_return | text | YES | - |
| guid | text | YES | - |
| codexp | text | YES | - |
| barcode | text | YES | - |
| evidence_original | text | YES | - |
| evidence_sanitized | text | YES | - |
| summary | jsonb | NO | '{}'::jsonb |

Indices:
- iclientes_lab_trials_pkey: `CREATE UNIQUE INDEX iclientes_lab_trials_pkey ON public.iclientes_lab_trials USING btree (id)`
- idx_iclientes_lab_environment: `CREATE INDEX idx_iclientes_lab_environment ON public.iclientes_lab_trials USING btree (environment, created_at DESC)`
- idx_iclientes_lab_reference: `CREATE UNIQUE INDEX idx_iclientes_lab_reference ON public.iclientes_lab_trials USING btree (lower(reference))`

Restricciones:
- iclientes_lab_trials_api_request_id_fkey [f]: `FOREIGN KEY (api_request_id) REFERENCES api_requests(id)`
- iclientes_lab_trials_created_by_fkey [f]: `FOREIGN KEY (created_by) REFERENCES admin_users(id)`
- iclientes_lab_trials_environment_check [c]: `CHECK ((environment = ANY (ARRAY['test'::text, 'production'::text])))`
- iclientes_lab_trials_pkey [p]: `PRIMARY KEY (id)`
- iclientes_lab_trials_state_check [c]: `CHECK ((state = ANY (ARRAY['draft'::text, 'sending'::text, 'accepted'::text, 'rejected'::text, 'unknown'::text, 'not_sent'::text])))`

## invoice_lines

Filas observadas: 0.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('invoice_lines_id_seq'::regclass) |
| invoice_id | bigint | YES | - |
| shipment_id | bigint | YES | - |
| description | text | YES | - |
| amount | numeric | YES | - |
| details_json | jsonb | NO | '{}'::jsonb |

Indices:
- invoice_lines_pkey: `CREATE UNIQUE INDEX invoice_lines_pkey ON public.invoice_lines USING btree (id)`

Restricciones:
- invoice_lines_invoice_id_fkey [f]: `FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE`
- invoice_lines_pkey [p]: `PRIMARY KEY (id)`
- invoice_lines_shipment_id_fkey [f]: `FOREIGN KEY (shipment_id) REFERENCES shipments(id) ON DELETE SET NULL`

## invoices

Filas observadas: 0.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('invoices_id_seq'::regclass) |
| carrier_id | bigint | YES | - |
| invoice_number | text | YES | - |
| invoice_date | date | YES | - |
| amount | numeric | YES | - |
| status | text | NO | 'pending'::text |
| created_at | timestamp with time zone | NO | now() |
| updated_at | timestamp with time zone | NO | now() |

Indices:
- invoices_pkey: `CREATE UNIQUE INDEX invoices_pkey ON public.invoices USING btree (id)`

Restricciones:
- invoices_carrier_id_fkey [f]: `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL`
- invoices_pkey [p]: `PRIMARY KEY (id)`

## knowledge_articles

Filas observadas: 31.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('knowledge_articles_id_seq'::regclass) |
| slug | text | NO | - |
| title | text | NO | - |
| path | text | NO | - |
| category | text | NO | 'General'::text |
| updated_at | timestamp with time zone | NO | now() |

Indices:
- knowledge_articles_pkey: `CREATE UNIQUE INDEX knowledge_articles_pkey ON public.knowledge_articles USING btree (id)`
- knowledge_articles_slug_key: `CREATE UNIQUE INDEX knowledge_articles_slug_key ON public.knowledge_articles USING btree (slug)`

Restricciones:
- knowledge_articles_pkey [p]: `PRIMARY KEY (id)`
- knowledge_articles_slug_key [u]: `UNIQUE (slug)`

## known_api_endpoints

Filas observadas: 9.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('known_api_endpoints_id_seq'::regclass) |
| carrier_code | text | NO | 'generic'::text |
| product | text | NO | - |
| endpoint_key | text | NO | - |
| method | text | NO | - |
| path | text | NO | - |
| environment | text | NO | 'sandbox'::text |
| authentication | text | NO | 'OAuth 2.0 client_credentials'::text |
| description | text | NO | ''::text |
| parameters_json | jsonb | NO | '{}'::jsonb |
| examples_json | jsonb | NO | '{}'::jsonb |
| limitations | text | NO | ''::text |
| quota_known | text | NO | ''::text |
| current_status | text | NO | 'sin_validar'::text |
| last_test_id | bigint | YES | - |
| updated_at | timestamp with time zone | NO | now() |

Indices:
- known_api_endpoints_carrier_code_endpoint_key_environment_key: `CREATE UNIQUE INDEX known_api_endpoints_carrier_code_endpoint_key_environment_key ON public.known_api_endpoints USING btree (carrier_code, endpoint_key, environment)`
- known_api_endpoints_pkey: `CREATE UNIQUE INDEX known_api_endpoints_pkey ON public.known_api_endpoints USING btree (id)`

Restricciones:
- known_api_endpoints_carrier_code_endpoint_key_environment_key [u]: `UNIQUE (carrier_code, endpoint_key, environment)`
- known_api_endpoints_last_test_id_fkey [f]: `FOREIGN KEY (last_test_id) REFERENCES connection_tests(id) ON DELETE SET NULL`
- known_api_endpoints_pkey [p]: `PRIMARY KEY (id)`

## official_documents

Filas observadas: 16.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('official_documents_id_seq'::regclass) |
| carrier_code | text | NO | 'generic'::text |
| integration_family | text | NO | - |
| original_name | text | NO | - |
| mime_type | text | NO | - |
| size_bytes | bigint | NO | - |
| sha256 | text | NO | - |
| incorporated_at | timestamp with time zone | NO | now() |
| version | text | YES | - |
| document_date | date | YES | - |
| provenance | text | NO | - |
| description | text | NO | ''::text |
| status | text | NO | 'incorporado'::text |
| physical_path | text | NO | - |
| is_vendor_official | boolean | NO | true |
| original_or_derivative | text | NO | 'original'::text |
| derivative_of_id | bigint | YES | - |
| downloadable | boolean | NO | true |
| created_at | timestamp with time zone | NO | now() |
| updated_at | timestamp with time zone | NO | now() |

Indices:
- idx_official_documents_carrier_family: `CREATE INDEX idx_official_documents_carrier_family ON public.official_documents USING btree (carrier_code, integration_family, original_or_derivative)`
- official_documents_physical_path_key: `CREATE UNIQUE INDEX official_documents_physical_path_key ON public.official_documents USING btree (physical_path)`
- official_documents_pkey: `CREATE UNIQUE INDEX official_documents_pkey ON public.official_documents USING btree (id)`

Restricciones:
- official_documents_derivative_of_id_fkey [f]: `FOREIGN KEY (derivative_of_id) REFERENCES official_documents(id) ON DELETE SET NULL`
- official_documents_physical_path_key [u]: `UNIQUE (physical_path)`
- official_documents_pkey [p]: `PRIMARY KEY (id)`

## parcels

Filas observadas: 0.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('parcels_id_seq'::regclass) |
| shipment_id | bigint | YES | - |
| carrier_id | bigint | YES | - |
| tracking_number | text | YES | - |
| carrier_tracking_id | text | YES | - |
| status | text | NO | 'unknown'::text |
| last_event_at | timestamp with time zone | YES | - |
| created_at | timestamp with time zone | NO | now() |
| updated_at | timestamp with time zone | NO | now() |

Indices:
- parcels_pkey: `CREATE UNIQUE INDEX parcels_pkey ON public.parcels USING btree (id)`

Restricciones:
- parcels_carrier_id_fkey [f]: `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL`
- parcels_pkey [p]: `PRIMARY KEY (id)`
- parcels_shipment_id_fkey [f]: `FOREIGN KEY (shipment_id) REFERENCES shipments(id) ON DELETE CASCADE`

## print_derivatives

Filas observadas: 5.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | uuid | NO | - |
| label_id | uuid | NO | - |
| method | text | NO | - |
| original_sha256 | text | NO | - |
| payload_sha256 | text | NO | - |
| payload_bytes | integer | NO | - |
| encrypted_path | text | NO | - |
| recipe | jsonb | NO | - |
| created_at | timestamp with time zone | NO | now() |

Indices:
- print_derivatives_pkey: `CREATE UNIQUE INDEX print_derivatives_pkey ON public.print_derivatives USING btree (id)`

Restricciones:
- print_derivatives_label_id_fkey [f]: `FOREIGN KEY (label_id) REFERENCES iclientes_lab_labels(id)`
- print_derivatives_method_check [c]: `CHECK ((method = ANY (ARRAY['cups'::text, 'epl2'::text])))`
- print_derivatives_pkey [p]: `PRIMARY KEY (id)`

## print_jobs

Filas observadas: 6.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | uuid | NO | - |
| label_id | uuid | NO | - |
| trial_id | uuid | NO | - |
| printer_id | text | NO | - |
| derivative_id | uuid | YES | - |
| method | text | NO | - |
| copies | integer | NO | - |
| nonce_hash | text | NO | - |
| created_by | integer | YES | - |
| actor | text | NO | - |
| original_sha256 | text | NO | - |
| parameters | jsonb | NO | - |
| state | text | NO | 'preparing'::text |
| created_at | timestamp with time zone | NO | now() |
| started_at | timestamp with time zone | YES | - |
| finished_at | timestamp with time zone | YES | - |
| duration_ms | integer | YES | - |
| backend_id | text | YES | - |
| error | text | YES | - |
| physical_result | text | NO | 'unreviewed'::text |
| reviewed_at | timestamp with time zone | YES | - |
| reviewed_by | integer | YES | - |

Indices:
- print_jobs_nonce_hash_key: `CREATE UNIQUE INDEX print_jobs_nonce_hash_key ON public.print_jobs USING btree (nonce_hash)`
- print_jobs_pkey: `CREATE UNIQUE INDEX print_jobs_pkey ON public.print_jobs USING btree (id)`

Restricciones:
- print_jobs_copies_check [c]: `CHECK (((copies >= 1) AND (copies <= 5)))`
- print_jobs_created_by_fkey [f]: `FOREIGN KEY (created_by) REFERENCES admin_users(id)`
- print_jobs_derivative_id_fkey [f]: `FOREIGN KEY (derivative_id) REFERENCES print_derivatives(id)`
- print_jobs_label_id_fkey [f]: `FOREIGN KEY (label_id) REFERENCES iclientes_lab_labels(id)`
- print_jobs_method_check [c]: `CHECK ((method = ANY (ARRAY['cups'::text, 'epl2'::text])))`
- print_jobs_nonce_hash_key [u]: `UNIQUE (nonce_hash)`
- print_jobs_physical_result_check [c]: `CHECK ((physical_result = ANY (ARRAY['unreviewed'::text, 'good'::text, 'bad'::text])))`
- print_jobs_pkey [p]: `PRIMARY KEY (id)`
- print_jobs_printer_id_fkey [f]: `FOREIGN KEY (printer_id) REFERENCES print_printers(id)`
- print_jobs_reviewed_by_fkey [f]: `FOREIGN KEY (reviewed_by) REFERENCES admin_users(id)`
- print_jobs_state_check [c]: `CHECK ((state = ANY (ARRAY['preparing'::text, 'sending'::text, 'submitted'::text, 'transmitted'::text, 'error'::text, 'unknown'::text])))`
- print_jobs_trial_id_fkey [f]: `FOREIGN KEY (trial_id) REFERENCES iclientes_lab_trials(id)`

## print_printers

Filas observadas: 1.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | text | NO | - |
| name | text | NO | - |
| host | text | NO | - |
| port | integer | NO | - |
| queue | text | NO | - |
| enabled | boolean | NO | true |
| max_copies | integer | NO | 5 |
| threshold | integer | NO | 128 |
| default_method | text | NO | 'cups'::text |
| updated_at | timestamp with time zone | NO | now() |

Indices:
- print_printers_pkey: `CREATE UNIQUE INDEX print_printers_pkey ON public.print_printers USING btree (id)`

Restricciones:
- print_printers_default_method_check [c]: `CHECK ((default_method = ANY (ARRAY['cups'::text, 'epl2'::text])))`
- print_printers_host_check [c]: `CHECK ((host = '[IP INTERNA OMITIDA]'::text))`
- print_printers_max_copies_check [c]: `CHECK (((max_copies >= 1) AND (max_copies <= 5)))`
- print_printers_pkey [p]: `PRIMARY KEY (id)`
- print_printers_port_check [c]: `CHECK ((port = 9100))`
- print_printers_queue_check [c]: `CHECK ((queue = 'Zebra_LP2844'::text))`
- print_printers_threshold_check [c]: `CHECK (((threshold >= 1) AND (threshold <= 254)))`

## shipments

Filas observadas: 0.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('shipments_id_seq'::regclass) |
| carrier_id | bigint | YES | - |
| external_reference | text | YES | - |
| status | text | NO | 'draft'::text |
| source_system | text | YES | - |
| created_at | timestamp with time zone | NO | now() |
| updated_at | timestamp with time zone | NO | now() |

Indices:
- shipments_pkey: `CREATE UNIQUE INDEX shipments_pkey ON public.shipments USING btree (id)`

Restricciones:
- shipments_carrier_id_fkey [f]: `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL`
- shipments_pkey [p]: `PRIMARY KEY (id)`

## synchronizations

Filas observadas: 0.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('synchronizations_id_seq'::regclass) |
| carrier_id | bigint | YES | - |
| sync_type | text | NO | - |
| status | text | NO | 'pending'::text |
| started_at | timestamp with time zone | YES | - |
| finished_at | timestamp with time zone | YES | - |
| summary_json | jsonb | NO | '{}'::jsonb |
| created_at | timestamp with time zone | NO | now() |

Indices:
- synchronizations_pkey: `CREATE UNIQUE INDEX synchronizations_pkey ON public.synchronizations USING btree (id)`

Restricciones:
- synchronizations_carrier_id_fkey [f]: `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL`
- synchronizations_pkey [p]: `PRIMARY KEY (id)`

## technical_lessons

Filas observadas: 8.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('technical_lessons_id_seq'::regclass) |
| lesson_key | text | NO | - |
| title | text | NO | - |
| body | text | NO | - |
| severity | text | NO | 'info'::text |
| carrier_code | text | NO | 'generic'::text |
| source | text | NO | 'system'::text |
| evidence_test_id | bigint | YES | - |
| created_at | timestamp with time zone | NO | now() |

Indices:
- technical_lessons_lesson_key_key: `CREATE UNIQUE INDEX technical_lessons_lesson_key_key ON public.technical_lessons USING btree (lesson_key)`
- technical_lessons_pkey: `CREATE UNIQUE INDEX technical_lessons_pkey ON public.technical_lessons USING btree (id)`

Restricciones:
- technical_lessons_evidence_test_id_fkey [f]: `FOREIGN KEY (evidence_test_id) REFERENCES connection_tests(id) ON DELETE SET NULL`
- technical_lessons_lesson_key_key [u]: `UNIQUE (lesson_key)`
- technical_lessons_pkey [p]: `PRIMARY KEY (id)`

## tracking_events

Filas observadas: 0.

| Columna | Tipo | Nullable | Default |
|---|---|---|---|
| id | bigint | NO | nextval('tracking_events_id_seq'::regclass) |
| parcel_id | bigint | YES | - |
| carrier_id | bigint | YES | - |
| event_code | text | YES | - |
| normalized_status | text | YES | - |
| event_at | timestamp with time zone | YES | - |
| raw_hash | text | YES | - |
| details_json | jsonb | NO | '{}'::jsonb |
| created_at | timestamp with time zone | NO | now() |

Indices:
- tracking_events_pkey: `CREATE UNIQUE INDEX tracking_events_pkey ON public.tracking_events USING btree (id)`

Restricciones:
- tracking_events_carrier_id_fkey [f]: `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL`
- tracking_events_parcel_id_fkey [f]: `FOREIGN KEY (parcel_id) REFERENCES parcels(id) ON DELETE CASCADE`
- tracking_events_pkey [p]: `PRIMARY KEY (id)`

## Migraciones aplicadas

- 001_phase_0_5_schema; 2026-07-15 11:29:35.809436+00:00
- 002_default_config; 2026-07-15 11:29:35.809436+00:00
- 003_phase_0_6_gls_diagnostics; 2026-07-15 14:16:51.137166+00:00
- 004_phase_0_7_gls_explorer; 2026-07-16 06:18:46.913838+00:00
- 005_phase_1_platform_base; 2026-07-16 07:29:43.721628+00:00
- 006_phase_1_documents_metadata; 2026-07-16 07:37:37.716754+00:00
- 007_official_provider_documents; 2026-09-01 15:30:41.931247+00:00
- 008_user_management; 2026-09-01 15:37:24.826622+00:00
- 009_iclientes_secret_reference; 2026-10-19 15:33:25.817489+00:00
- 010_iclientes_test_laboratory; 2026-11-02 13:09:27.891434+00:00
- 011_label_printing; 2026-11-02 19:41:20.867136+00:00
- 012_native_epl_labels; 2026-11-03 07:42:54.416640+00:00
- 013_iclientes_production; 2026-11-03 11:17:10.698983+00:00

No hay DDL ejecutado. Para reutilizacion/carencias ver informe06; para modelo futuro informe08.



---

# Suplemento: aptitud para fechas y conciliacion

05/11/2026. Diseno, no implementacion ni DDL. El anexo anterior contiene el
esquema REAL completo, no el modelo propuesto. 39 tablas / 386 columnas /
59 indices / 103 restricciones / 13 migraciones. Ninguna migracion nueva.

## Evaluacion por necesidad

| Necesidad | Situacion actual | Ajuste propuesto para siguiente fase |
|---|---|---|
| Fechas operativas | shipments solo created_at/updated_at TH; no fecha GLS | Separar fechas y procedencia; indices por cuenta/fecha/ID. |
| Destinatario + fecha aproximada | No snapshot operativo en shipments | Snapshot inmutable, texto de busqueda autorizado/indexado y control de acceso. |
| Estado e incidencias | status generico sin mapping versionado | Estados administrativos/logisticos/resultados separados y eventos originales. |
| Historico completo | Nucleo vacio; laboratorio/evidencias no son todo el historico | Ingesta trazable, cobertura por ventana y retencion gobernada, sin purga automatica. |
| Varias cuentas GLS | No carrier_account_id en nucleo/cuotas | carrier_accounts con entorno/agencia/cliente/secret_ref; claves scoped. |
| Varios carriers | carriers existe, FK nullable | Reutilizar core y adapters; no crear repositorio paralelo GLS. |
| IDs y deduplicacion | external_reference y tracking_number sin unicidad de cuenta | IDs tipados y unicidad carrier/cuenta/entorno/tipo/valor donde este acreditada. |
| Incremental | synchronizations vacia, sin checkpoints/completitud | Items/runs/cobertura/watermarks por tipo y cuenta; solape y replay local. |
| Eventos | Solo parcel_id, sin unicidad de ocurrencia robusta | shipment_id, namespace, version, fecha efectiva/captura e identidad de ocurrencia. |
| POD progresivo | documents indexa Markdown; labels laboratorio independientes | Activos privados, hashes, enlaces a envio/cuenta y estado de recuperacion/integridad. |

No cabe afirmar que las tablas existentes ya soporten la busqueda operativa completa.
Si se comienza despues de autorizacion, reutilizar identidad/core y ampliar sin
dependencia de un nombre SOAP concreto: el adapter produce resultados/cobertura.

## Fechas que deben distinguirse

| Campo conceptual propuesto | Significado / fuente | Tratamiento |
|---|---|---|
| th_created_at | Alta del registro en TH | No usar como fecha GLS ni para un backfill historico. |
| gls_recorded_at | Grabacion efectiva en GLS | Solo poblar si hay campo/evento inequívoco documentado. |
| shipment_date | Fecha expedicion/servicio | Conservar literal, tipo DATE si solo fecha y semantica de origen. |
| picked_up_at | Recogida | Evento acreditado; no deducir de etiqueta/GRABADO. |
| first_movement_at | Primer movimiento logistico | Evento acreditado; excluir eventos meramente administrativos. |
| delivered_at | Entrega efectiva | No confundir prevista FPEntrega con efectiva ni entrega de retorno con ida. |
| cancelled_at | Anulacion efectiva | Diferente de solicitud/aceptacion HTTP y posterior cierre definitivo. |
| last_event_at | Ultimo acontecimiento efectivo | No significa entrega ni fecha de consulta; puede ser FACTURA. |
| last_successful_sync_at | Ultima aplicacion valida de datos remotos | Un intento fallido no actualiza esta fecha de frescura. |
| last_sync_attempt_at | Ultimo intento, tambien fallido | Separado para diagnostico/error/reanudacion. |

Cada dato lleva fuente, campo original, observacion, precision y timezone/calidad.
Un campo sin dato permanece NULL/desconocido, no se rellena con now() ni con otra fecha.
Guardar instantes como timestamptz UTC y presentar Europe/Madrid; si solo hay fecha
sin hora no inventar medianoche como instante real. Una hora sin zona requiere
politica explicita y conservar incertidumbre, incluidos cambios de horario.

**Filtro por defecto propuesto: Fecha de expedicion**, si GLS acredita su campo;
alternativa de grabacion si esa es la semantica real del contrato. La UI debe nombrar
el criterio y mostrar origen/ausencias, nunca un COALESCE silencioso entre fechas.
Permitir criterios explicitos de grabacion, recogida, entrega, ultimo evento o alta TH.
Para datos sin fecha operativa ofrecer vista separada, no esconderlos como si no existieran.

Para un intervalo de fechas inclusivas en la UI, convertir localmente al rango
[inicio del primer dia, inicio del dia siguiente al ultimo) en Europe/Madrid.
Ejemplo de contraste: 04/11/2025-04/12/2025 seria [2025-11-04T00:00+01:00,
2025-12-05T00:00+01:00). Esto es una propuesta SQL local, NO un payload GLS
ni afirmacion de que Extranet o GetEnvios utilicen esos extremos.
Para agosto el offset cambia; calcular con zona IANA, no restar 24 horas fijas.

## Indices y consultas futuras

- Btree cuenta/entorno/fecha_expedicion/id para periodo con paginacion estable.
- Btree cuenta/estado/fecha y next_sync_at para activos/incidencias; separar resultado de ciclo.
- Indice de IDs scoped y referencias tipadas; no hacer unico el albaran/referencia libre sin prueba.
- Indice envio/event_at e identidad revisable del evento, preservando codigo y namespace originales.
- Busqueda de nombre normalizado/CP/poblacion con indices apropiados (trigram solo si se aprueba
  la extension); datos minimizados, roles y decision de cifrado, no ILIKE sobre Fernet.

No se ejecuta CREATE INDEX/EXTENSION ni se optimiza una carga inexistente.

## Conciliacion de conjuntos sin duplicados

Crear en una fase futura un concepto de ejecucion/ventana: cuenta, entorno, fuente,
criterio temporal, inicio/fin, timezone, filtros de estados/canales, timestamp,
numero de paginas, cursor/total, hash de IDs, version de contrato y grado de cobertura.
Cobertura = desconocida / parcial / completa / fallo; un HTTP 200 no implica completa.

R = IDs de GLS de una ventana completamente recuperada; L = IDs locales del mismo
ambito y criterio. R-L = pendientes de importar; R interseccion L = candidatos a
refresco/verificacion; L-R = discrepancias que deben investigarse, NO bajas automaticas.
Si R es parcial/desconocido, L-R no permite afirmar ausencia remota. No usar un limite
de pagina o una cache vencida como conjunto completo. Los 61 de Extranet son referencia
de contraste, no contenido de R hasta recibir los IDs por una fuente autorizada.

Clasificar tambien registro incompleto (faltan ficha/eventos/fechas), estado obsoleto
(segun politica SLA y ultima sync correcta), documento pendiente y conflicto de identidad.
Deduplicar por ID fuerte scoped; relacionar devoluciones, sin fusion por nombre/referencia.
Toda ingesta atomica e idempotente; parser aplicable a originales cifrados, no copias
sanitizadas truncadas. Un fallo posterior a la captura se reejecuta localmente sin HTTP.

Un checkpoint se avanza solo al consolidar una ventana completa. Distinguir watermark
de descubrimiento, de refresco y de POD. Reservar solape retrospectivo configurable para
publicaciones tardias/cambios, sin fijar arbitrariamente cuantos dias bastan. Si GLS solo
filtra fecha de expedicion, refrescar tambien activos antiguos fuera de ayer; no confiar
en ese filtro como feed de modificaciones ni omitir incidencias antiguas.

## Condiciones para comenzar sin redisenar despues

Modelo, consultas PostgreSQL, deduplicacion, parser offline, capturas/replay y estados
de completitud pueden desarrollarse tras otra aprobacion. Antes: backup/restauracion
probados, migracion revisada, semantica de identidad/fechas, datos sinteticos y privacidad.
El adapter debe distinguir `discover(window)` (capacidad pendiente) de
`refresh_known(ids)` (capacidad documentada), con resultado y cobertura explicitos.
No habilitar discover ni prometer historico completo hasta contrato/prueba GLS.
No implementar ahora; la peticion actual se limita a evaluar y documentar.


## Actualizacion aplicada de etapa D

# DATABASE_CHANGELOG

## Etapa D - migracion 018_carrier_execution

Esquema aditivo, sin borrar ni duplicar el repositorio existente.
- sender_centers: codigo unico, etiqueta, datos cifrados, revision, activo y auditoria.
- carrier_account_senders: relacion N:M, claves foraneas y un habitual por cuenta.
- shipment_drafts: sender_center_id y sender_revision, snapshot dentro del cifrado.
- shipments: provider_acknowledged_at, distinto de grabacion/recogida GLS.
- synchronizations: pause_requested y run_count, independientes del presupuesto
  de fallos/leases ya existente.
- synchronization_items: execution_id, receipt_ciphertext, evidence_ref, HTTP y
  duracion. Reutiliza los checkpoints previos, sin otro repositorio de trabajos.
- app_config: flags nuevos de creacion operativa y trabajador conocidos a false.

17 a 18 probado con cuatro migradores concurrentes y filas anteriores intactas.
Backup previo restaurado en base temporal: 54 tablas, 111 indices, hashes y
recuentos coincidentes. Tras la migracion se han verificado 56 tablas y 115 indices.
El resultado aplicado se comprueba en STAGE_D_REPORT y evidencia privada.
Rollback de aplicacion al codigo anterior, conservando el esquema aditivo.
No ejecutar DOWN ni restaurar la BD activa sin revision y autorizacion.