{"id":"POSTGRESQL_AUDIT","title":"Auditoria PostgreSQL","version":"nav88-20261106-v1","date":"2026-11-06T13:00:28.017898+01:00","classification":"PUBLIC_REVIEW","sha256":"741339ce67ee9ba32c3cae42ed61eabc1622b9140314e300eae1bc8938b5be54","read_url":"/api/review/POSTGRESQL_AUDIT","total_bytes":61698,"fragment_count":1,"document_sha256":"741339ce67ee9ba32c3cae42ed61eabc1622b9140314e300eae1bc8938b5be54","fragmented":false,"content_markdown":"# Auditoria historica y esquema actual\n\nEl snapshot inicial se conserva completo. El esquema vigente es el anexo de DATABASE_CHANGELOG, con 53 tablas y 16 migraciones.\n\n# Auditoria completa PostgreSQL y modelo historico\n\nExportacion para revision: 05/11/2026, Europe/Madrid.\nContenido completo de los informes fuente, no resumen. No se alteran los originales.\nLos nombres personales no imprescindibles se sustituyen por etiquetas; los ejemplos\ntecnicos y la estructura se conservan. No se exportan credenciales ni evidencias crudas.\nLas conclusiones, contadores y autorizaciones de cada informe son HISTORICOS:\nno habilitan operaciones. El encargo actual prioriza exclusivamente enumeracion por fechas.\nLa fecha mas reciente y el suplemento de esta revision prevalecen sobre planes anteriores.\n\n## Fuentes completas incluidas\n\n| Fuente original en VM | SHA-256 original |\n|---|---|\n| [RUTA INTERNA OMITIDA] | 3ffe59689f0d98368d6d2d676202850cb6397976ef63b3a046761a4d34824ad3 |\n| [RUTA INTERNA OMITIDA] | 3218f8f0c1878b8a8d4b274c65c0de121268b1ea1b8a5b2dfdae5926bb6ae02a |\n| [RUTA INTERNA OMITIDA] | 3144a6404b3812ec7801f1c5ddd8de27576dbeb05a77cb3a95023e0f2da07cf9 |\n\n---\n\n## Documento fuente: 06_POSTGRESQL_AUDIT.md\n\n# Auditoria PostgreSQL real\n\n05/11/2026. SELECT en transacciones read-only; sin DDL, INSERT/UPDATE,\nmigraciones, importaciones ni reconciliaciones de indices documentales.\n\n## Inventario\n\nPostgreSQL 15.19 Debian, base transport_hub de 10 MB, esquema de usuario\npublic. 39 tablas, 386 columnas, 59 indices y 103 restricciones catalogadas.\n13 migraciones aplicadas, desde 001_phase_0_5_schema hasta\n013_iclientes_production. Inventario completo por columna/tipo/null/default,\nindices y restricciones en el anexo 16; JSON de evidencia estructural sin\nfilas de secretos o datos comerciales en el dossier tecnico.\n\n| Grupo | Tablas y filas reales |\n|---|---|\n| Usuarios/config | admin_users 2 (ambos admin activos), app_config 26, connector_credentials 1, iclientes_credentials 2 |\n| Auditoria | api_requests 21, api_usage_daily 7, connection_tests 23, app_logs 2, technical_lessons 8 |\n| Catalogos/plataforma | carriers 6, carrier_capabilities 8, known_api_endpoints 9, gls_event_codes 336, gls_product_matrix 24, gls_environments 3 |\n| Documentacion | documents 31, knowledge_articles 31, official_documents 16 |\n| Laboratorio | iclientes_lab_trials 3 (TEST aceptada, TEST borrador, PRODUCCION aceptada), queries 2, labels 4 |\n| Impresion | print_printers 1, print_jobs 6, print_derivatives 5 |\n| Politicas | cache_resources 7, enrichment_profiles 4, api_quota_policies 1 |\n| Nucleo vacio | shipments 0, parcels 0, tracking_events 0, synchronizations 0 |\n| Futuro vacio | alerts, cod_records, cod_settlements, invoices, invoice_lines, bank_movements, gls_allowed_services_cache: 0 |\n\nLos auxiliares puntuales generaron evidencia/API requests sin crear queries\ndel laboratorio: queries=2 no es todo el historial de consultas. Ninguna\nfila shipments confirma una expedicion porque el repositorio aun no se ingiere.\n\n## Relaciones e indices: fortalezas y carencias\n\nCarriers -> shipments/parcels/events/synchronizations; parcels -> shipments;\ntracking_events -> parcels. Hay FKs y PKs, pero tracking_events no tiene\nshipment_id directo y las claves carrier son nullable con ON DELETE SET NULL.\nEso no basta para historico inmutable con ambito de cuenta/entorno.\n\nShipments solo tiene id, carrier_id, external_reference, status, source_system\ny timestamps. No hay cuentas, numero operativo, IDs tipados, cuatro ejes de\nestado, destinatario, datos comerciales o revisiones. No hay unicidad fuerte\ncarrier/cuenta/entorno/tipo/valor, ni deduplicacion de eventos.\n\nShipments/tracking_events solo tienen indice PK. api_requests tiene indice\nprovider/product/environment/occurred_at, pero no carrier_account_id. Las\ncuotas/agregados tampoco discriminan cuenta. No declarar el modelo definitivo\nya preparado por existir nombres de tablas.\n\nDocuments y knowledge_articles son indices de articulos tecnicos por slug/path,\nno almacenes de POD/envios. No sobrecargarlos sin distinguir semantica. El\ncatalogo oficial separa originales del proveedor con hashes/procedencia.\nLa KB se descubre en ficheros: 54 articulos antes de esta entrega, frente\na 31 en SQL. Es una deuda de indexacion, no perdida de esos ficheros.\n\n## Acceso y operacion\n\nConexion de app por socket local y peer como transport-hub. Rol no superuser,\nsin CREATEDB ni CREATEROLE. El arranque ejecuta apply_migrations con ese rol;\npropiedad/permisos DDL deben separarse del rol runtime en fase autorizada.\nNo se han probado permisos destructivos para verificarlo.\n\nListen localhost, SSL activado para TCP local; puerto 5432 solo loopback.\nmax_connections=100, shared_buffers=128 MiB, autovacuum on, wal_level replica,\narchive_mode off. Son valores observados, no recomendacion de tuning ni\nprueba de backup/replicacion. No se midio rendimiento de cargas grandes.\n\n## Reutilizacion propuesta\n\nExtender nucleo existente mediante futuras migraciones revisadas; mantener\nusuarios/config/catalogos/auditoria y evidencias. Anadir solo conceptos que\nfaltan: cuentas, IDs/referencias, snapshots/comercial, observaciones/revisiones,\ndocumentos de envio y operaciones/jobs segun diseno. Evitar otro nucleo GLS.\nLaboratorio no debe convertirse en tabla operativa mediante UPDATEs ad hoc.\n\nAntes de poblar: backup restaurable, reglas de identidad y estados, migracion\nreversible, pruebas con datos sinteticos e ingesta offline de evidencia\nautorizada. Ninguna de estas acciones se realiza en esta auditoria.\n\n\n---\n\n## Documento fuente: 08_HISTORICAL_REPOSITORY_DESIGN.md\n\n# Repositorio historico persistente: propuesta\n\nNo DDL ni implementacion. Evolucionar la unica base transport_hub y el\nnucleo existente. No limitar a 100 registros ni aplicar purgas automaticas\npor antiguedad. Conservacion comercial completa, con decisiones explicitas\nde gobierno de datos/obligaciones aplicables; capacidad indefinida no es\nuna orden de conservar PII eternamente sin revision.\n\n## Agrupaciones y relaciones necesarias\n\n| Conceptos | Entidad propuesta / reutilizacion |\n|---|---|\n| Carrier | carriers existente, sin implementar otros proveedores. |\n| Cuenta/entorno/secretos | carrier_accounts nueva; IDs de cuenta y secret_ref, nunca UID/Secret en claro; adapter al mecanismo actual. |\n| Expedicion | Extender shipments, no tabla GLS paralela. ID TH estable; carrier_account_id, environment y numero externo visible. |\n| Identificadores | shipment_identifiers con namespace/tipo/valor/cuenta; indices de lookup y unicidad solo de IDs fuertes en su ambito. |\n| Referencias comerciales | shipment_references: pedido, factura, ERP, TH y C/0/S/otros GLS, con tipo/valor/fuente. Multiples referencias por envio y viceversa. |\n| 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. |\n| Servicio/condiciones | Codigos/literales originales y snapshot comercial dentro del agregado; tablas de catalogo solo donde eviten duplicacion real. |\n| Bultos | parcels existente, IDs por bulto, pesos/medidas declarados y observados con fuente. |\n| Eventos/incidencias/entregas | tracking_events ampliada con shipment_id, tipo/namespace y revisiones; vistas/proyecciones, no tabla por cada literal. |\n| Reembolso | cod_records existente ampliable; importes declarados/moneda/procedencia. No inferir cobro/liquidacion por entrega. |\n| 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. |\n| Operaciones administrativas | shipment_operations: intencion/aceptacion/rechazo/desconocido y evidencias; separadas de api_requests (una operacion puede tener varios HTTP). |\n| Observaciones | shipment_observations append-only, snapshot normalizado versionado y referencia al original cifrado. |\n| Sincronizacion/importacion | Extender synchronizations + items/checkpoints/leases si hacen falta; un motor comun, no nuevo sistema por conector. |\n| Cuotas/auditoria/errores | api_requests/api_usage_daily/api_quota_policies/alerts existentes, con cuenta/origen/coste y estado de captura/ingesta. |\n\nNo se decide crear 25 tablas por 25 conceptos. El detalle fisico y migracion\nse revisara tras aprobar identidad, privacidad y consultas principales.\n\n## Identidad y unicidad\n\nNumero completo GLS es la primera columna operativa y campo de API, no la\nPK. Si GLS lo devuelve se preserva literal; no construir agencia+CODEXP\npara cualquier envio sin regla validada. Codigos como cadenas, ceros intactos.\nClave fuerte: carrier/cuenta/entorno/tipo/valor; CODEXP no globalmente unico.\nGUID, barcode y numero de bulto se tipan y correlacionan con evidencia.\n\nNo fusionar por nombre, direccion, albaran o referencia no unica. Devoluciones\npueden heredar referencias y tener ID propio. Relacion padre/hija tipada,\nno sobrescribir el original. Conflictos de identificadores van a revision,\nno upsert silencioso. Cuenta anterior nunca comparte UID por suposicion.\n\n## Campos funcionales y comerciales\n\nShipment: fecha original expedicion y observaciones, servicio/horario/portes,\nbultos/peso/moneda, pais, centro remitente, origen transport_hub/gls_extranet/\nimported/unknown y grado de confianza. Lookup API no demuestra canal de alta.\n\nSnapshot de destinatario: nombre, direccion, CP, poblacion, provincia, pais,\ntelefono, movil, correo y observaciones, todos con fuente y campos presentes.\nNull/desconocido/no devuelto no equivale a cadena vacia o cero. Nunca inventar\ncontactos que no entregue GLS/intranet. Direccion snapshot no cambia al\neditar despues un centro o cliente en intranet.\n\nReferencias pedido/factura/ERP no crean facturas ni conciliacion; intranet\nconserva autoridad sobre sus datos comerciales. Prioridad de fuentes\nconfigurable por campo, sin sobreescribir ERP con una respuesta parcial GLS.\nReembolso declarado con decimal exacto y moneda original; estado de cobro,\nimporte cobrado y conciliacion desconocidos hasta evidencia especifica.\n\n## Cuatro dimensiones de estado\n\n| Dimension | Valores conceptuales y reglas |\n|---|---|\n| Administrativo | creada, anulacion_solicitada, anulacion_aceptada, anulacion_rechazada, desconocida. No confundir estado del request HTTP con resultado de negocio. |\n| Logistico | grabada, recogida, en_transito, en_reparto, entregada, incidencia, devuelta, desconocido; derivados de codigos/tipos/versiones acreditados. |\n| Ciclo | abierta, finalizada, cerrada, desconocida; terminalidad respaldada por fuente/motivo, no por antiguedad. |\n| Resultado | entregada, anulada, devuelta, otros, pendiente/desconocido. Cierre definitivo no asigna entregada. |\n\nTH-P-001 valida: admin aceptada por Anula0; logistica API inmediata grabada;\nresultado anulada y ciclo cerrado comunicados desde Extranet despues.\nAlmacenar cada observacion y su nivel de evidencia, no fabricar evento API5.\nLa reduccion de bultos/peso a 0 comunicada de Extranet es snapshot posterior,\nno borrado del valor declarado original 1/1 ni prueba de ausencia de costes.\n\n## Acontecimientos y correcciones\n\nConservar tipo, codigo y descripcion originales, API/catalogo/namespace,\nfecha literal y timezone conocida o incertidumbre, agencia/instalacion,\natributos complementarios, fecha de recuperacion e ID externo si existe.\nESTADO, ENTREGA, POD, INCIDENCIA, SOLUCION, FACTURA y demas tipos no comparten\nun espacio numerico. LocationCode no es estado. Evento administrativo posterior\nno devuelve una entregada a pendiente por ser el mas reciente.\n\nID externo estable permite enlazar revisiones. Si falta, huella canonica\npor envio/tipo/codigo/fecha/instalacion identifica ocurrencia candidata y\nhash de contenido distingue cambios. No deduplicar solo por codigo.\nUna correccion genera revision/supersedes y nueva observacion, no UPDATE\ndestructivo del acontecimiento historico. Si dos ocurrencias pueden coincidir\nsin ID estable, conservar incertidumbre/original y evitar fusion automatica.\n\nLos primeros/ultimos vistos, fecha efectiva, fecha carrier y fecha de ingesta\nson distintos. Versionar parser/mapeo y reprocesar originales localmente\nsin volver a gastar llamadas. Desconocidos conservan datos, no se descartan.\n\nUna recuperacion nueva de un evento antiguo no reabre automaticamente un\nenvio cerrado: ordenar por evidencia/fecha efectiva, no solo fecha de HTTP.\nTH-P-001 exige conservar Anula0 y el cierre Extranet posterior aunque una\nrespuesta inmediata aun repita el evento GRABADO del dia anterior. Prioridad\nentre fuentes y resolucion de conflictos debe ser explicita y versionada.\n\n## Captura, ingesta e integridad\n\nHTTP real -> captura original cifrada duradera -> auditoria -> validacion/\nparser tipado -> transaccion de agregado/eventos/referencias/snapshot.\nSi falla BD/parser, inbox duradera para reprocesado LOCAL; no repetir HTTP\npara obtener de nuevo una respuesta ya conservada. Registrar recibido,\nprocesado, aplicado y error por separado. Toda respuesta util autorizada\nalimenta el repositorio, incluidas altas/consultas manuales, con completitud.\n\nNo ingestar desde la copia sanitizada: el sanitizador actual limita listas\ny oculta PII/fechas que si son necesarias al modelo. Original cifrado es\nla evidencia autorizada; log/copia tecnica no es fuente completa de negocio.\nVacio de lookup no prueba borrado; no eliminar una observacion positiva previa.\n\n## Activos privados\n\nOriginal bytes inmutable, SHA-256, MIME detectado, tamano, cuenta/envio,\noperacion/fuente, fechas, formato, numero de paginas/dimensiones cuando aplica,\nestado de integridad, clave/version de cifrado y ruta privada. Nunca ruta\narbitraria proporcionada por cliente ni descarga publica del filesystem.\n\nDeduplicar binarios por hash dentro de dominio autorizado; multiples vinculos\nmetadata al mismo activo sin filtrar existencia entre cuentas. Version nueva\nde POD/etiqueta crea activo nuevo, no reemplaza original. Referencias URL\nprivadas/firmadas no se confunden con POD descargado/verificado.\n\n## Indices, privacidad y retencion\n\nIndices futuros sobre cuenta/entorno/numero, IDs fuertes, fecha, estado,\nterminalidad, next_sync_at y eventos envio/fecha. Referencias tipadas con\nindice de busqueda, no unicidad universal. Paginacion local estable por\nfecha+ID. Particionar solo con volumen/medicion, no una tabla por mes ahora.\n\nTelefonos/movil/email y direccion sensible requieren cifrado/acceso por campo;\nnombre/CP/poblacion para busqueda parcial necesitan una decision explicita:\npropuesta de columnas operativas minimas indexables con roles restringidos,\ncifrado de volumen/backups, resto sensible cifrado. Fernet no permite ILIKE\nsobre su ciphertext. No prometer busqueda parcial cifrada sin diseno aparte.\n\nHistorico sin purga automatica. TTL de frescura no borra registros ni prueba\nretencion de GLS. Gobierno de datos define revisiones, anonimizar/eliminar\nsi corresponde y evidencia de la decision, sin ocultar trazabilidad tecnica.\n\n\n---\n\n## Documento fuente: 16_POSTGRESQL_SCHEMA_INVENTORY.md\n\n# Inventario completo PostgreSQL observado\n\n05/11/2026. Solo lectura de information_schema/pg_catalog. 39 tablas,386 columnas,59 indices,103 restricciones; definiciones documentales, NO script de migracion.\n\n## admin_users\n\nFilas observadas: 2.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('admin_users_id_seq'::regclass) |\n| username | text | NO | - |\n| password_hash | text | NO | - |\n| created_at | timestamp with time zone | NO | now() |\n| last_login_at | timestamp with time zone | YES | - |\n| display_name | text | NO | ''::text |\n| email | text | NO | ''::text |\n| role | text | NO | 'admin'::text |\n| active | boolean | NO | true |\n| updated_at | timestamp with time zone | NO | now() |\n\nIndices:\n- admin_users_pkey: `CREATE UNIQUE INDEX admin_users_pkey ON public.admin_users USING btree (id)`\n- admin_users_username_key: `CREATE UNIQUE INDEX admin_users_username_key ON public.admin_users USING btree (username)`\n- idx_admin_users_active_role: `CREATE INDEX idx_admin_users_active_role ON public.admin_users USING btree (active, role)`\n\nRestricciones:\n- admin_users_pkey [p]: `PRIMARY KEY (id)`\n- admin_users_username_key [u]: `UNIQUE (username)`\n\n## alerts\n\nFilas observadas: 0.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('alerts_id_seq'::regclass) |\n| severity | text | NO | 'info'::text |\n| scope | text | NO | 'system'::text |\n| title | text | NO | - |\n| message | text | NO | - |\n| status | text | NO | 'open'::text |\n| created_at | timestamp with time zone | NO | now() |\n| resolved_at | timestamp with time zone | YES | - |\n\nIndices:\n- alerts_pkey: `CREATE UNIQUE INDEX alerts_pkey ON public.alerts USING btree (id)`\n\nRestricciones:\n- alerts_pkey [p]: `PRIMARY KEY (id)`\n\n## api_quota_policies\n\nFilas observadas: 1.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| product | text | NO | - |\n| environment | text | NO | - |\n| period | text | NO | - |\n| limit_count | integer | NO | - |\n| limit_source | text | NO | - |\n| reserve_count | integer | NO | 0 |\n| warn_thresholds | jsonb | NO | '[50, 75, 90, 100]'::jsonb |\n| active | boolean | NO | true |\n| updated_at | timestamp with time zone | NO | now() |\n\nIndices:\n- api_quota_policies_pkey: `CREATE UNIQUE INDEX api_quota_policies_pkey ON public.api_quota_policies USING btree (product, environment, period)`\n\nRestricciones:\n- api_quota_policies_pkey [p]: `PRIMARY KEY (product, environment, period)`\n\n## api_requests\n\nFilas observadas: 21.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('api_requests_id_seq'::regclass) |\n| provider | text | NO | - |\n| product | text | NO | - |\n| environment | text | NO | - |\n| endpoint_logical | text | NO | - |\n| method | text | NO | - |\n| occurred_at | timestamp with time zone | NO | now() |\n| http_code | integer | YES | - |\n| duration_ms | integer | NO | 0 |\n| result | text | NO | - |\n| items_requested | integer | NO | 0 |\n| items_returned | integer | NO | 0 |\n| origin | text | NO | - |\n| actor | text | YES | - |\n| correlation_id | uuid | YES | - |\n| quota_computed | numeric | YES | - |\n| error_sanitized | text | YES | - |\n| details_json | jsonb | NO | '{}'::jsonb |\n\nIndices:\n- api_requests_pkey: `CREATE UNIQUE INDEX api_requests_pkey ON public.api_requests USING btree (id)`\n- idx_api_requests_usage: `CREATE INDEX idx_api_requests_usage ON public.api_requests USING btree (provider, product, environment, occurred_at DESC)`\n\nRestricciones:\n- api_requests_pkey [p]: `PRIMARY KEY (id)`\n\n## api_usage_daily\n\nFilas observadas: 7.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| usage_date | date | NO | - |\n| product | text | NO | - |\n| environment | text | NO | - |\n| requests | integer | NO | 0 |\n| successes | integer | NO | 0 |\n| errors | integer | NO | 0 |\n| items_processed | integer | NO | 0 |\n| avg_duration_ms | numeric | NO | 0 |\n| updated_at | timestamp with time zone | NO | now() |\n\nIndices:\n- api_usage_daily_pkey: `CREATE UNIQUE INDEX api_usage_daily_pkey ON public.api_usage_daily USING btree (usage_date, product, environment)`\n\nRestricciones:\n- api_usage_daily_pkey [p]: `PRIMARY KEY (usage_date, product, environment)`\n\n## app_config\n\nFilas observadas: 26.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| key | text | NO | - |\n| value | text | NO | - |\n| type | text | NO | 'string'::text |\n| updated_at | timestamp with time zone | NO | now() |\n\nIndices:\n- app_config_pkey: `CREATE UNIQUE INDEX app_config_pkey ON public.app_config USING btree (key)`\n\nRestricciones:\n- app_config_pkey [p]: `PRIMARY KEY (key)`\n\n## app_logs\n\nFilas observadas: 2.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('app_logs_id_seq'::regclass) |\n| level | text | NO | - |\n| event | text | NO | - |\n| message | text | NO | - |\n| user_id | bigint | YES | - |\n| details_json | jsonb | NO | '{}'::jsonb |\n| created_at | timestamp with time zone | NO | now() |\n\nIndices:\n- app_logs_pkey: `CREATE UNIQUE INDEX app_logs_pkey ON public.app_logs USING btree (id)`\n\nRestricciones:\n- app_logs_pkey [p]: `PRIMARY KEY (id)`\n- app_logs_user_id_fkey [f]: `FOREIGN KEY (user_id) REFERENCES admin_users(id) ON DELETE SET NULL`\n\n## app_migrations\n\nFilas observadas: 13.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| version | text | NO | - |\n| applied_at | timestamp with time zone | NO | now() |\n\nIndices:\n- app_migrations_pkey: `CREATE UNIQUE INDEX app_migrations_pkey ON public.app_migrations USING btree (version)`\n\nRestricciones:\n- app_migrations_pkey [p]: `PRIMARY KEY (version)`\n\n## bank_movements\n\nFilas observadas: 0.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('bank_movements_id_seq'::regclass) |\n| movement_date | date | YES | - |\n| concept | text | YES | - |\n| amount | numeric | YES | - |\n| currency | text | NO | 'EUR'::text |\n| matched_status | text | NO | 'unmatched'::text |\n| created_at | timestamp with time zone | NO | now() |\n\nIndices:\n- bank_movements_pkey: `CREATE UNIQUE INDEX bank_movements_pkey ON public.bank_movements USING btree (id)`\n\nRestricciones:\n- bank_movements_pkey [p]: `PRIMARY KEY (id)`\n\n## cache_resources\n\nFilas observadas: 7.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('cache_resources_id_seq'::regclass) |\n| resource_key | text | NO | - |\n| carrier_code | text | NO | 'generic'::text |\n| resource_name | text | NO | - |\n| origin | text | NO | 'CACHE'::text |\n| ttl_seconds | integer | NO | 0 |\n| policy | text | NO | 'manual'::text |\n| last_obtained_at | timestamp with time zone | YES | - |\n| expires_at | timestamp with time zone | YES | - |\n| enabled | boolean | NO | true |\n| updated_at | timestamp with time zone | NO | now() |\n\nIndices:\n- cache_resources_pkey: `CREATE UNIQUE INDEX cache_resources_pkey ON public.cache_resources USING btree (id)`\n- cache_resources_resource_key_key: `CREATE UNIQUE INDEX cache_resources_resource_key_key ON public.cache_resources USING btree (resource_key)`\n\nRestricciones:\n- cache_resources_pkey [p]: `PRIMARY KEY (id)`\n- cache_resources_resource_key_key [u]: `UNIQUE (resource_key)`\n\n## carrier_capabilities\n\nFilas observadas: 8.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('carrier_capabilities_id_seq'::regclass) |\n| carrier_id | bigint | NO | - |\n| capability | text | NO | - |\n| status | text | NO | 'planned'::text |\n| depends_on | text | NO | ''::text |\n| notes | text | NO | ''::text |\n| updated_at | timestamp with time zone | NO | now() |\n\nIndices:\n- carrier_capabilities_carrier_id_capability_key: `CREATE UNIQUE INDEX carrier_capabilities_carrier_id_capability_key ON public.carrier_capabilities USING btree (carrier_id, capability)`\n- carrier_capabilities_pkey: `CREATE UNIQUE INDEX carrier_capabilities_pkey ON public.carrier_capabilities USING btree (id)`\n\nRestricciones:\n- carrier_capabilities_carrier_id_capability_key [u]: `UNIQUE (carrier_id, capability)`\n- carrier_capabilities_carrier_id_fkey [f]: `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE CASCADE`\n- carrier_capabilities_pkey [p]: `PRIMARY KEY (id)`\n\n## carriers\n\nFilas observadas: 6.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('carriers_id_seq'::regclass) |\n| code | text | NO | - |\n| name | text | NO | - |\n| status | text | NO | 'planned'::text |\n| notes | text | NO | ''::text |\n| created_at | timestamp with time zone | NO | now() |\n| updated_at | timestamp with time zone | NO | now() |\n\nIndices:\n- carriers_code_key: `CREATE UNIQUE INDEX carriers_code_key ON public.carriers USING btree (code)`\n- carriers_pkey: `CREATE UNIQUE INDEX carriers_pkey ON public.carriers USING btree (id)`\n\nRestricciones:\n- carriers_code_key [u]: `UNIQUE (code)`\n- carriers_pkey [p]: `PRIMARY KEY (id)`\n\n## cod_records\n\nFilas observadas: 0.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('cod_records_id_seq'::regclass) |\n| shipment_id | bigint | YES | - |\n| parcel_id | bigint | YES | - |\n| carrier_id | bigint | YES | - |\n| amount | numeric | YES | - |\n| currency | text | NO | 'EUR'::text |\n| status | text | NO | 'pending'::text |\n| created_at | timestamp with time zone | NO | now() |\n| updated_at | timestamp with time zone | NO | now() |\n\nIndices:\n- cod_records_pkey: `CREATE UNIQUE INDEX cod_records_pkey ON public.cod_records USING btree (id)`\n\nRestricciones:\n- cod_records_carrier_id_fkey [f]: `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL`\n- cod_records_parcel_id_fkey [f]: `FOREIGN KEY (parcel_id) REFERENCES parcels(id) ON DELETE SET NULL`\n- cod_records_pkey [p]: `PRIMARY KEY (id)`\n- cod_records_shipment_id_fkey [f]: `FOREIGN KEY (shipment_id) REFERENCES shipments(id) ON DELETE SET NULL`\n\n## cod_settlements\n\nFilas observadas: 0.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('cod_settlements_id_seq'::regclass) |\n| carrier_id | bigint | YES | - |\n| settlement_reference | text | YES | - |\n| period_start | date | YES | - |\n| period_end | date | YES | - |\n| amount | numeric | YES | - |\n| status | text | NO | 'pending'::text |\n| created_at | timestamp with time zone | NO | now() |\n| updated_at | timestamp with time zone | NO | now() |\n\nIndices:\n- cod_settlements_pkey: `CREATE UNIQUE INDEX cod_settlements_pkey ON public.cod_settlements USING btree (id)`\n\nRestricciones:\n- cod_settlements_carrier_id_fkey [f]: `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL`\n- cod_settlements_pkey [p]: `PRIMARY KEY (id)`\n\n## connection_tests\n\nFilas observadas: 23.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('connection_tests_id_seq'::regclass) |\n| test_type | text | NO | - |\n| provider | text | NO | - |\n| environment | text | NO | - |\n| started_at | timestamp with time zone | NO | - |\n| finished_at | timestamp with time zone | NO | - |\n| result | text | NO | - |\n| http_code | integer | YES | - |\n| sanitized_message | text | NO | - |\n| duration_ms | integer | NO | - |\n| details_json | jsonb | NO | '{}'::jsonb |\n| launched_by | bigint | YES | - |\n| endpoint_logical | text | YES | - |\n| diagnostic_run_id | uuid | YES | - |\n| classification | text | YES | - |\n\nIndices:\n- connection_tests_pkey: `CREATE UNIQUE INDEX connection_tests_pkey ON public.connection_tests USING btree (id)`\n- idx_connection_tests_recent: `CREATE INDEX idx_connection_tests_recent ON public.connection_tests USING btree (provider, environment, started_at DESC)`\n\nRestricciones:\n- connection_tests_launched_by_fkey [f]: `FOREIGN KEY (launched_by) REFERENCES admin_users(id) ON DELETE SET NULL`\n- connection_tests_pkey [p]: `PRIMARY KEY (id)`\n\n## connector_credentials\n\nFilas observadas: 1.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| provider | text | NO | - |\n| environment | text | NO | - |\n| client_id_ciphertext | text | NO | - |\n| client_secret_ciphertext | text | NO | - |\n| created_at | timestamp with time zone | NO | now() |\n| updated_at | timestamp with time zone | NO | now() |\n| last_validated_at | timestamp with time zone | YES | - |\n| validation_status | text | NO | 'configured_not_tested'::text |\n\nIndices:\n- connector_credentials_pkey: `CREATE UNIQUE INDEX connector_credentials_pkey ON public.connector_credentials USING btree (provider, environment)`\n\nRestricciones:\n- connector_credentials_pkey [p]: `PRIMARY KEY (provider, environment)`\n\n## documents\n\nFilas observadas: 31.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('documents_id_seq'::regclass) |\n| slug | text | NO | - |\n| title | text | NO | - |\n| path | text | NO | - |\n| document_type | text | NO | 'markdown'::text |\n| category | text | NO | 'General'::text |\n| checksum | text | YES | - |\n| updated_at | timestamp with time zone | NO | now() |\n\nIndices:\n- documents_pkey: `CREATE UNIQUE INDEX documents_pkey ON public.documents USING btree (id)`\n- documents_slug_key: `CREATE UNIQUE INDEX documents_slug_key ON public.documents USING btree (slug)`\n\nRestricciones:\n- documents_pkey [p]: `PRIMARY KEY (id)`\n- documents_slug_key [u]: `UNIQUE (slug)`\n\n## enrichment_profiles\n\nFilas observadas: 4.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('enrichment_profiles_id_seq'::regclass) |\n| carrier_code | text | NO | 'generic'::text |\n| level | text | NO | - |\n| description | text | NO | ''::text |\n| estimated_extra_calls | integer | NO | 0 |\n| rules_json | jsonb | NO | '{}'::jsonb |\n| active | boolean | NO | true |\n| updated_at | timestamp with time zone | NO | now() |\n\nIndices:\n- enrichment_profiles_carrier_code_level_key: `CREATE UNIQUE INDEX enrichment_profiles_carrier_code_level_key ON public.enrichment_profiles USING btree (carrier_code, level)`\n- enrichment_profiles_pkey: `CREATE UNIQUE INDEX enrichment_profiles_pkey ON public.enrichment_profiles USING btree (id)`\n\nRestricciones:\n- enrichment_profiles_carrier_code_level_key [u]: `UNIQUE (carrier_code, level)`\n- enrichment_profiles_pkey [p]: `PRIMARY KEY (id)`\n\n## gls_allowed_services_cache\n\nFilas observadas: 0.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('gls_allowed_services_cache_id_seq'::regclass) |\n| origin | text | NO | - |\n| destination | text | NO | - |\n| contact_id | text | YES | - |\n| product | text | YES | - |\n| service | text | YES | - |\n| queried_at | timestamp with time zone | NO | now() |\n| response_hash | text | NO | - |\n| active | boolean | NO | true |\n\nIndices:\n- 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)`\n- gls_allowed_services_cache_pkey: `CREATE UNIQUE INDEX gls_allowed_services_cache_pkey ON public.gls_allowed_services_cache USING btree (id)`\n\nRestricciones:\n- gls_allowed_services_cache_origin_destination_contact_id_pr_key [u]: `UNIQUE (origin, destination, contact_id, product, service)`\n- gls_allowed_services_cache_pkey [p]: `PRIMARY KEY (id)`\n\n## gls_environments\n\nFilas observadas: 3.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| code | text | NO | - |\n| name | text | NO | - |\n| label | text | NO | - |\n| active | boolean | NO | false |\n| locked | boolean | NO | true |\n| state | text | NO | 'sin_validar'::text |\n| last_validated_at | timestamp with time zone | YES | - |\n| oauth_urls | jsonb | NO | '{}'::jsonb |\n| base_urls | jsonb | NO | '{}'::jsonb |\n| enabled_apis | jsonb | NO | '[]'::jsonb |\n| backend_authorization | jsonb | NO | '{}'::jsonb |\n| limits_json | jsonb | NO | '{}'::jsonb |\n| config_json | jsonb | NO | '{}'::jsonb |\n| observations | text | NO | ''::text |\n| created_at | timestamp with time zone | NO | now() |\n| updated_at | timestamp with time zone | NO | now() |\n\nIndices:\n- gls_environments_pkey: `CREATE UNIQUE INDEX gls_environments_pkey ON public.gls_environments USING btree (code)`\n\nRestricciones:\n- gls_environments_pkey [p]: `PRIMARY KEY (code)`\n\n## gls_event_codes\n\nFilas observadas: 336.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| code | text | NO | - |\n| language | text | NO | - |\n| description | text | NO | - |\n| first_seen_at | timestamp with time zone | NO | now() |\n| last_seen_at | timestamp with time zone | NO | now() |\n| active | boolean | NO | true |\n| raw_hash | text | NO | - |\n\nIndices:\n- gls_event_codes_pkey: `CREATE UNIQUE INDEX gls_event_codes_pkey ON public.gls_event_codes USING btree (code, language)`\n\nRestricciones:\n- gls_event_codes_pkey [p]: `PRIMARY KEY (code, language)`\n\n## gls_product_matrix\n\nFilas observadas: 24.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| product | text | NO | - |\n| environment | text | NO | - |\n| status | text | NO | 'sin_validar'::text |\n| last_test_id | bigint | YES | - |\n| evidence | text | NO | ''::text |\n| pending | text | NO | ''::text |\n| credential_ref_provider | text | YES | - |\n| credential_ref_environment | text | YES | - |\n| updated_at | timestamp with time zone | NO | now() |\n\nIndices:\n- gls_product_matrix_pkey: `CREATE UNIQUE INDEX gls_product_matrix_pkey ON public.gls_product_matrix USING btree (product, environment)`\n\nRestricciones:\n- gls_product_matrix_environment_fkey [f]: `FOREIGN KEY (environment) REFERENCES gls_environments(code)`\n- gls_product_matrix_last_test_id_fkey [f]: `FOREIGN KEY (last_test_id) REFERENCES connection_tests(id) ON DELETE SET NULL`\n- gls_product_matrix_pkey [p]: `PRIMARY KEY (product, environment)`\n\n## iclientes_credentials\n\nFilas observadas: 2.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| environment | text | NO | - |\n| secret_ref | text | NO | - |\n| description | text | NO | ''::text |\n| created_at | timestamp with time zone | NO | now() |\n| updated_at | timestamp with time zone | NO | now() |\n\nIndices:\n- iclientes_credentials_pkey: `CREATE UNIQUE INDEX iclientes_credentials_pkey ON public.iclientes_credentials USING btree (environment)`\n- iclientes_credentials_secret_ref_key: `CREATE UNIQUE INDEX iclientes_credentials_secret_ref_key ON public.iclientes_credentials USING btree (secret_ref)`\n\nRestricciones:\n- iclientes_credentials_environment_check [c]: `CHECK ((environment = ANY (ARRAY['test'::text, 'production'::text])))`\n- iclientes_credentials_pkey [p]: `PRIMARY KEY (environment)`\n- iclientes_credentials_secret_ref_key [u]: `UNIQUE (secret_ref)`\n\n## iclientes_lab_labels\n\nFilas observadas: 4.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | uuid | NO | - |\n| trial_id | uuid | NO | - |\n| mime_type | text | NO | - |\n| size_bytes | integer | NO | - |\n| sha256 | text | NO | - |\n| encrypted_path | text | NO | - |\n| created_at | timestamp with time zone | NO | now() |\n| image_width | integer | YES | - |\n| image_height | integer | YES | - |\n| declared_dpi | jsonb | YES | - |\n| source_metadata | jsonb | NO | '{}'::jsonb |\n\nIndices:\n- iclientes_lab_labels_pkey: `CREATE UNIQUE INDEX iclientes_lab_labels_pkey ON public.iclientes_lab_labels USING btree (id)`\n\nRestricciones:\n- iclientes_lab_labels_pkey [p]: `PRIMARY KEY (id)`\n- iclientes_lab_labels_trial_id_fkey [f]: `FOREIGN KEY (trial_id) REFERENCES iclientes_lab_trials(id)`\n\n## iclientes_lab_queries\n\nFilas observadas: 2.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | uuid | NO | - |\n| trial_id | uuid | NO | - |\n| created_by | integer | NO | - |\n| nonce_hash | text | NO | - |\n| state | text | NO | 'sending'::text |\n| created_at | timestamp with time zone | NO | now() |\n| finished_at | timestamp with time zone | YES | - |\n| api_request_id | bigint | YES | - |\n| http_status | integer | YES | - |\n| duration_ms | integer | YES | - |\n| evidence_original | text | YES | - |\n| evidence_sanitized | text | YES | - |\n| summary | jsonb | NO | '{}'::jsonb |\n| operation | text | NO | 'GetExpCli'::text |\n\nIndices:\n- iclientes_lab_queries_pkey: `CREATE UNIQUE INDEX iclientes_lab_queries_pkey ON public.iclientes_lab_queries USING btree (id)`\n- 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)`\n\nRestricciones:\n- iclientes_lab_queries_api_request_id_fkey [f]: `FOREIGN KEY (api_request_id) REFERENCES api_requests(id)`\n- iclientes_lab_queries_created_by_fkey [f]: `FOREIGN KEY (created_by) REFERENCES admin_users(id)`\n- iclientes_lab_queries_operation_check [c]: `CHECK ((operation = ANY (ARRAY['GetExpCli'::text, 'EtiquetaEnvioV2'::text])))`\n- iclientes_lab_queries_pkey [p]: `PRIMARY KEY (id)`\n- iclientes_lab_queries_state_check [c]: `CHECK ((state = ANY (ARRAY['sending'::text, 'complete'::text, 'unknown'::text, 'not_sent'::text])))`\n- iclientes_lab_queries_trial_id_fkey [f]: `FOREIGN KEY (trial_id) REFERENCES iclientes_lab_trials(id)`\n- iclientes_lab_queries_trial_id_nonce_hash_key [u]: `UNIQUE (trial_id, nonce_hash)`\n\n## iclientes_lab_trials\n\nFilas observadas: 3.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | uuid | NO | - |\n| environment | text | NO | 'test'::text |\n| reference | text | NO | - |\n| created_by | integer | NO | - |\n| form_ciphertext | text | NO | - |\n| confirm_hash | text | NO | - |\n| state | text | NO | 'draft'::text |\n| created_at | timestamp with time zone | NO | now() |\n| submitted_at | timestamp with time zone | YES | - |\n| finished_at | timestamp with time zone | YES | - |\n| api_request_id | bigint | YES | - |\n| http_status | integer | YES | - |\n| duration_ms | integer | YES | - |\n| result_return | text | YES | - |\n| guid | text | YES | - |\n| codexp | text | YES | - |\n| barcode | text | YES | - |\n| evidence_original | text | YES | - |\n| evidence_sanitized | text | YES | - |\n| summary | jsonb | NO | '{}'::jsonb |\n\nIndices:\n- iclientes_lab_trials_pkey: `CREATE UNIQUE INDEX iclientes_lab_trials_pkey ON public.iclientes_lab_trials USING btree (id)`\n- idx_iclientes_lab_environment: `CREATE INDEX idx_iclientes_lab_environment ON public.iclientes_lab_trials USING btree (environment, created_at DESC)`\n- idx_iclientes_lab_reference: `CREATE UNIQUE INDEX idx_iclientes_lab_reference ON public.iclientes_lab_trials USING btree (lower(reference))`\n\nRestricciones:\n- iclientes_lab_trials_api_request_id_fkey [f]: `FOREIGN KEY (api_request_id) REFERENCES api_requests(id)`\n- iclientes_lab_trials_created_by_fkey [f]: `FOREIGN KEY (created_by) REFERENCES admin_users(id)`\n- iclientes_lab_trials_environment_check [c]: `CHECK ((environment = ANY (ARRAY['test'::text, 'production'::text])))`\n- iclientes_lab_trials_pkey [p]: `PRIMARY KEY (id)`\n- iclientes_lab_trials_state_check [c]: `CHECK ((state = ANY (ARRAY['draft'::text, 'sending'::text, 'accepted'::text, 'rejected'::text, 'unknown'::text, 'not_sent'::text])))`\n\n## invoice_lines\n\nFilas observadas: 0.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('invoice_lines_id_seq'::regclass) |\n| invoice_id | bigint | YES | - |\n| shipment_id | bigint | YES | - |\n| description | text | YES | - |\n| amount | numeric | YES | - |\n| details_json | jsonb | NO | '{}'::jsonb |\n\nIndices:\n- invoice_lines_pkey: `CREATE UNIQUE INDEX invoice_lines_pkey ON public.invoice_lines USING btree (id)`\n\nRestricciones:\n- invoice_lines_invoice_id_fkey [f]: `FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE`\n- invoice_lines_pkey [p]: `PRIMARY KEY (id)`\n- invoice_lines_shipment_id_fkey [f]: `FOREIGN KEY (shipment_id) REFERENCES shipments(id) ON DELETE SET NULL`\n\n## invoices\n\nFilas observadas: 0.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('invoices_id_seq'::regclass) |\n| carrier_id | bigint | YES | - |\n| invoice_number | text | YES | - |\n| invoice_date | date | YES | - |\n| amount | numeric | YES | - |\n| status | text | NO | 'pending'::text |\n| created_at | timestamp with time zone | NO | now() |\n| updated_at | timestamp with time zone | NO | now() |\n\nIndices:\n- invoices_pkey: `CREATE UNIQUE INDEX invoices_pkey ON public.invoices USING btree (id)`\n\nRestricciones:\n- invoices_carrier_id_fkey [f]: `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL`\n- invoices_pkey [p]: `PRIMARY KEY (id)`\n\n## knowledge_articles\n\nFilas observadas: 31.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('knowledge_articles_id_seq'::regclass) |\n| slug | text | NO | - |\n| title | text | NO | - |\n| path | text | NO | - |\n| category | text | NO | 'General'::text |\n| updated_at | timestamp with time zone | NO | now() |\n\nIndices:\n- knowledge_articles_pkey: `CREATE UNIQUE INDEX knowledge_articles_pkey ON public.knowledge_articles USING btree (id)`\n- knowledge_articles_slug_key: `CREATE UNIQUE INDEX knowledge_articles_slug_key ON public.knowledge_articles USING btree (slug)`\n\nRestricciones:\n- knowledge_articles_pkey [p]: `PRIMARY KEY (id)`\n- knowledge_articles_slug_key [u]: `UNIQUE (slug)`\n\n## known_api_endpoints\n\nFilas observadas: 9.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('known_api_endpoints_id_seq'::regclass) |\n| carrier_code | text | NO | 'generic'::text |\n| product | text | NO | - |\n| endpoint_key | text | NO | - |\n| method | text | NO | - |\n| path | text | NO | - |\n| environment | text | NO | 'sandbox'::text |\n| authentication | text | NO | 'OAuth 2.0 client_credentials'::text |\n| description | text | NO | ''::text |\n| parameters_json | jsonb | NO | '{}'::jsonb |\n| examples_json | jsonb | NO | '{}'::jsonb |\n| limitations | text | NO | ''::text |\n| quota_known | text | NO | ''::text |\n| current_status | text | NO | 'sin_validar'::text |\n| last_test_id | bigint | YES | - |\n| updated_at | timestamp with time zone | NO | now() |\n\nIndices:\n- 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)`\n- known_api_endpoints_pkey: `CREATE UNIQUE INDEX known_api_endpoints_pkey ON public.known_api_endpoints USING btree (id)`\n\nRestricciones:\n- known_api_endpoints_carrier_code_endpoint_key_environment_key [u]: `UNIQUE (carrier_code, endpoint_key, environment)`\n- known_api_endpoints_last_test_id_fkey [f]: `FOREIGN KEY (last_test_id) REFERENCES connection_tests(id) ON DELETE SET NULL`\n- known_api_endpoints_pkey [p]: `PRIMARY KEY (id)`\n\n## official_documents\n\nFilas observadas: 16.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('official_documents_id_seq'::regclass) |\n| carrier_code | text | NO | 'generic'::text |\n| integration_family | text | NO | - |\n| original_name | text | NO | - |\n| mime_type | text | NO | - |\n| size_bytes | bigint | NO | - |\n| sha256 | text | NO | - |\n| incorporated_at | timestamp with time zone | NO | now() |\n| version | text | YES | - |\n| document_date | date | YES | - |\n| provenance | text | NO | - |\n| description | text | NO | ''::text |\n| status | text | NO | 'incorporado'::text |\n| physical_path | text | NO | - |\n| is_vendor_official | boolean | NO | true |\n| original_or_derivative | text | NO | 'original'::text |\n| derivative_of_id | bigint | YES | - |\n| downloadable | boolean | NO | true |\n| created_at | timestamp with time zone | NO | now() |\n| updated_at | timestamp with time zone | NO | now() |\n\nIndices:\n- 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)`\n- official_documents_physical_path_key: `CREATE UNIQUE INDEX official_documents_physical_path_key ON public.official_documents USING btree (physical_path)`\n- official_documents_pkey: `CREATE UNIQUE INDEX official_documents_pkey ON public.official_documents USING btree (id)`\n\nRestricciones:\n- official_documents_derivative_of_id_fkey [f]: `FOREIGN KEY (derivative_of_id) REFERENCES official_documents(id) ON DELETE SET NULL`\n- official_documents_physical_path_key [u]: `UNIQUE (physical_path)`\n- official_documents_pkey [p]: `PRIMARY KEY (id)`\n\n## parcels\n\nFilas observadas: 0.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('parcels_id_seq'::regclass) |\n| shipment_id | bigint | YES | - |\n| carrier_id | bigint | YES | - |\n| tracking_number | text | YES | - |\n| carrier_tracking_id | text | YES | - |\n| status | text | NO | 'unknown'::text |\n| last_event_at | timestamp with time zone | YES | - |\n| created_at | timestamp with time zone | NO | now() |\n| updated_at | timestamp with time zone | NO | now() |\n\nIndices:\n- parcels_pkey: `CREATE UNIQUE INDEX parcels_pkey ON public.parcels USING btree (id)`\n\nRestricciones:\n- parcels_carrier_id_fkey [f]: `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL`\n- parcels_pkey [p]: `PRIMARY KEY (id)`\n- parcels_shipment_id_fkey [f]: `FOREIGN KEY (shipment_id) REFERENCES shipments(id) ON DELETE CASCADE`\n\n## print_derivatives\n\nFilas observadas: 5.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | uuid | NO | - |\n| label_id | uuid | NO | - |\n| method | text | NO | - |\n| original_sha256 | text | NO | - |\n| payload_sha256 | text | NO | - |\n| payload_bytes | integer | NO | - |\n| encrypted_path | text | NO | - |\n| recipe | jsonb | NO | - |\n| created_at | timestamp with time zone | NO | now() |\n\nIndices:\n- print_derivatives_pkey: `CREATE UNIQUE INDEX print_derivatives_pkey ON public.print_derivatives USING btree (id)`\n\nRestricciones:\n- print_derivatives_label_id_fkey [f]: `FOREIGN KEY (label_id) REFERENCES iclientes_lab_labels(id)`\n- print_derivatives_method_check [c]: `CHECK ((method = ANY (ARRAY['cups'::text, 'epl2'::text])))`\n- print_derivatives_pkey [p]: `PRIMARY KEY (id)`\n\n## print_jobs\n\nFilas observadas: 6.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | uuid | NO | - |\n| label_id | uuid | NO | - |\n| trial_id | uuid | NO | - |\n| printer_id | text | NO | - |\n| derivative_id | uuid | YES | - |\n| method | text | NO | - |\n| copies | integer | NO | - |\n| nonce_hash | text | NO | - |\n| created_by | integer | YES | - |\n| actor | text | NO | - |\n| original_sha256 | text | NO | - |\n| parameters | jsonb | NO | - |\n| state | text | NO | 'preparing'::text |\n| created_at | timestamp with time zone | NO | now() |\n| started_at | timestamp with time zone | YES | - |\n| finished_at | timestamp with time zone | YES | - |\n| duration_ms | integer | YES | - |\n| backend_id | text | YES | - |\n| error | text | YES | - |\n| physical_result | text | NO | 'unreviewed'::text |\n| reviewed_at | timestamp with time zone | YES | - |\n| reviewed_by | integer | YES | - |\n\nIndices:\n- print_jobs_nonce_hash_key: `CREATE UNIQUE INDEX print_jobs_nonce_hash_key ON public.print_jobs USING btree (nonce_hash)`\n- print_jobs_pkey: `CREATE UNIQUE INDEX print_jobs_pkey ON public.print_jobs USING btree (id)`\n\nRestricciones:\n- print_jobs_copies_check [c]: `CHECK (((copies >= 1) AND (copies <= 5)))`\n- print_jobs_created_by_fkey [f]: `FOREIGN KEY (created_by) REFERENCES admin_users(id)`\n- print_jobs_derivative_id_fkey [f]: `FOREIGN KEY (derivative_id) REFERENCES print_derivatives(id)`\n- print_jobs_label_id_fkey [f]: `FOREIGN KEY (label_id) REFERENCES iclientes_lab_labels(id)`\n- print_jobs_method_check [c]: `CHECK ((method = ANY (ARRAY['cups'::text, 'epl2'::text])))`\n- print_jobs_nonce_hash_key [u]: `UNIQUE (nonce_hash)`\n- print_jobs_physical_result_check [c]: `CHECK ((physical_result = ANY (ARRAY['unreviewed'::text, 'good'::text, 'bad'::text])))`\n- print_jobs_pkey [p]: `PRIMARY KEY (id)`\n- print_jobs_printer_id_fkey [f]: `FOREIGN KEY (printer_id) REFERENCES print_printers(id)`\n- print_jobs_reviewed_by_fkey [f]: `FOREIGN KEY (reviewed_by) REFERENCES admin_users(id)`\n- print_jobs_state_check [c]: `CHECK ((state = ANY (ARRAY['preparing'::text, 'sending'::text, 'submitted'::text, 'transmitted'::text, 'error'::text, 'unknown'::text])))`\n- print_jobs_trial_id_fkey [f]: `FOREIGN KEY (trial_id) REFERENCES iclientes_lab_trials(id)`\n\n## print_printers\n\nFilas observadas: 1.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | text | NO | - |\n| name | text | NO | - |\n| host | text | NO | - |\n| port | integer | NO | - |\n| queue | text | NO | - |\n| enabled | boolean | NO | true |\n| max_copies | integer | NO | 5 |\n| threshold | integer | NO | 128 |\n| default_method | text | NO | 'cups'::text |\n| updated_at | timestamp with time zone | NO | now() |\n\nIndices:\n- print_printers_pkey: `CREATE UNIQUE INDEX print_printers_pkey ON public.print_printers USING btree (id)`\n\nRestricciones:\n- print_printers_default_method_check [c]: `CHECK ((default_method = ANY (ARRAY['cups'::text, 'epl2'::text])))`\n- print_printers_host_check [c]: `CHECK ((host = '[IP INTERNA OMITIDA]'::text))`\n- print_printers_max_copies_check [c]: `CHECK (((max_copies >= 1) AND (max_copies <= 5)))`\n- print_printers_pkey [p]: `PRIMARY KEY (id)`\n- print_printers_port_check [c]: `CHECK ((port = 9100))`\n- print_printers_queue_check [c]: `CHECK ((queue = 'Zebra_LP2844'::text))`\n- print_printers_threshold_check [c]: `CHECK (((threshold >= 1) AND (threshold <= 254)))`\n\n## shipments\n\nFilas observadas: 0.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('shipments_id_seq'::regclass) |\n| carrier_id | bigint | YES | - |\n| external_reference | text | YES | - |\n| status | text | NO | 'draft'::text |\n| source_system | text | YES | - |\n| created_at | timestamp with time zone | NO | now() |\n| updated_at | timestamp with time zone | NO | now() |\n\nIndices:\n- shipments_pkey: `CREATE UNIQUE INDEX shipments_pkey ON public.shipments USING btree (id)`\n\nRestricciones:\n- shipments_carrier_id_fkey [f]: `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL`\n- shipments_pkey [p]: `PRIMARY KEY (id)`\n\n## synchronizations\n\nFilas observadas: 0.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('synchronizations_id_seq'::regclass) |\n| carrier_id | bigint | YES | - |\n| sync_type | text | NO | - |\n| status | text | NO | 'pending'::text |\n| started_at | timestamp with time zone | YES | - |\n| finished_at | timestamp with time zone | YES | - |\n| summary_json | jsonb | NO | '{}'::jsonb |\n| created_at | timestamp with time zone | NO | now() |\n\nIndices:\n- synchronizations_pkey: `CREATE UNIQUE INDEX synchronizations_pkey ON public.synchronizations USING btree (id)`\n\nRestricciones:\n- synchronizations_carrier_id_fkey [f]: `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL`\n- synchronizations_pkey [p]: `PRIMARY KEY (id)`\n\n## technical_lessons\n\nFilas observadas: 8.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('technical_lessons_id_seq'::regclass) |\n| lesson_key | text | NO | - |\n| title | text | NO | - |\n| body | text | NO | - |\n| severity | text | NO | 'info'::text |\n| carrier_code | text | NO | 'generic'::text |\n| source | text | NO | 'system'::text |\n| evidence_test_id | bigint | YES | - |\n| created_at | timestamp with time zone | NO | now() |\n\nIndices:\n- technical_lessons_lesson_key_key: `CREATE UNIQUE INDEX technical_lessons_lesson_key_key ON public.technical_lessons USING btree (lesson_key)`\n- technical_lessons_pkey: `CREATE UNIQUE INDEX technical_lessons_pkey ON public.technical_lessons USING btree (id)`\n\nRestricciones:\n- technical_lessons_evidence_test_id_fkey [f]: `FOREIGN KEY (evidence_test_id) REFERENCES connection_tests(id) ON DELETE SET NULL`\n- technical_lessons_lesson_key_key [u]: `UNIQUE (lesson_key)`\n- technical_lessons_pkey [p]: `PRIMARY KEY (id)`\n\n## tracking_events\n\nFilas observadas: 0.\n\n| Columna | Tipo | Nullable | Default |\n|---|---|---|---|\n| id | bigint | NO | nextval('tracking_events_id_seq'::regclass) |\n| parcel_id | bigint | YES | - |\n| carrier_id | bigint | YES | - |\n| event_code | text | YES | - |\n| normalized_status | text | YES | - |\n| event_at | timestamp with time zone | YES | - |\n| raw_hash | text | YES | - |\n| details_json | jsonb | NO | '{}'::jsonb |\n| created_at | timestamp with time zone | NO | now() |\n\nIndices:\n- tracking_events_pkey: `CREATE UNIQUE INDEX tracking_events_pkey ON public.tracking_events USING btree (id)`\n\nRestricciones:\n- tracking_events_carrier_id_fkey [f]: `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL`\n- tracking_events_parcel_id_fkey [f]: `FOREIGN KEY (parcel_id) REFERENCES parcels(id) ON DELETE CASCADE`\n- tracking_events_pkey [p]: `PRIMARY KEY (id)`\n\n## Migraciones aplicadas\n\n- 001_phase_0_5_schema; 2026-07-15 11:29:35.809436+00:00\n- 002_default_config; 2026-07-15 11:29:35.809436+00:00\n- 003_phase_0_6_gls_diagnostics; 2026-07-15 14:16:51.137166+00:00\n- 004_phase_0_7_gls_explorer; 2026-07-16 06:18:46.913838+00:00\n- 005_phase_1_platform_base; 2026-07-16 07:29:43.721628+00:00\n- 006_phase_1_documents_metadata; 2026-07-16 07:37:37.716754+00:00\n- 007_official_provider_documents; 2026-09-01 15:30:41.931247+00:00\n- 008_user_management; 2026-09-01 15:37:24.826622+00:00\n- 009_iclientes_secret_reference; 2026-10-19 15:33:25.817489+00:00\n- 010_iclientes_test_laboratory; 2026-11-02 13:09:27.891434+00:00\n- 011_label_printing; 2026-11-02 19:41:20.867136+00:00\n- 012_native_epl_labels; 2026-11-03 07:42:54.416640+00:00\n- 013_iclientes_production; 2026-11-03 11:17:10.698983+00:00\n\nNo hay DDL ejecutado. Para reutilizacion/carencias ver informe06; para modelo futuro informe08.\n\n\n\n---\n\n# Suplemento: aptitud para fechas y conciliacion\n\n05/11/2026. Diseno, no implementacion ni DDL. El anexo anterior contiene el\nesquema REAL completo, no el modelo propuesto. 39 tablas / 386 columnas /\n59 indices / 103 restricciones / 13 migraciones. Ninguna migracion nueva.\n\n## Evaluacion por necesidad\n\n| Necesidad | Situacion actual | Ajuste propuesto para siguiente fase |\n|---|---|---|\n| Fechas operativas | shipments solo created_at/updated_at TH; no fecha GLS | Separar fechas y procedencia; indices por cuenta/fecha/ID. |\n| Destinatario + fecha aproximada | No snapshot operativo en shipments | Snapshot inmutable, texto de busqueda autorizado/indexado y control de acceso. |\n| Estado e incidencias | status generico sin mapping versionado | Estados administrativos/logisticos/resultados separados y eventos originales. |\n| Historico completo | Nucleo vacio; laboratorio/evidencias no son todo el historico | Ingesta trazable, cobertura por ventana y retencion gobernada, sin purga automatica. |\n| Varias cuentas GLS | No carrier_account_id en nucleo/cuotas | carrier_accounts con entorno/agencia/cliente/secret_ref; claves scoped. |\n| Varios carriers | carriers existe, FK nullable | Reutilizar core y adapters; no crear repositorio paralelo GLS. |\n| IDs y deduplicacion | external_reference y tracking_number sin unicidad de cuenta | IDs tipados y unicidad carrier/cuenta/entorno/tipo/valor donde este acreditada. |\n| Incremental | synchronizations vacia, sin checkpoints/completitud | Items/runs/cobertura/watermarks por tipo y cuenta; solape y replay local. |\n| Eventos | Solo parcel_id, sin unicidad de ocurrencia robusta | shipment_id, namespace, version, fecha efectiva/captura e identidad de ocurrencia. |\n| POD progresivo | documents indexa Markdown; labels laboratorio independientes | Activos privados, hashes, enlaces a envio/cuenta y estado de recuperacion/integridad. |\n\nNo cabe afirmar que las tablas existentes ya soporten la busqueda operativa completa.\nSi se comienza despues de autorizacion, reutilizar identidad/core y ampliar sin\ndependencia de un nombre SOAP concreto: el adapter produce resultados/cobertura.\n\n## Fechas que deben distinguirse\n\n| Campo conceptual propuesto | Significado / fuente | Tratamiento |\n|---|---|---|\n| th_created_at | Alta del registro en TH | No usar como fecha GLS ni para un backfill historico. |\n| gls_recorded_at | Grabacion efectiva en GLS | Solo poblar si hay campo/evento inequívoco documentado. |\n| shipment_date | Fecha expedicion/servicio | Conservar literal, tipo DATE si solo fecha y semantica de origen. |\n| picked_up_at | Recogida | Evento acreditado; no deducir de etiqueta/GRABADO. |\n| first_movement_at | Primer movimiento logistico | Evento acreditado; excluir eventos meramente administrativos. |\n| delivered_at | Entrega efectiva | No confundir prevista FPEntrega con efectiva ni entrega de retorno con ida. |\n| cancelled_at | Anulacion efectiva | Diferente de solicitud/aceptacion HTTP y posterior cierre definitivo. |\n| last_event_at | Ultimo acontecimiento efectivo | No significa entrega ni fecha de consulta; puede ser FACTURA. |\n| last_successful_sync_at | Ultima aplicacion valida de datos remotos | Un intento fallido no actualiza esta fecha de frescura. |\n| last_sync_attempt_at | Ultimo intento, tambien fallido | Separado para diagnostico/error/reanudacion. |\n\nCada dato lleva fuente, campo original, observacion, precision y timezone/calidad.\nUn campo sin dato permanece NULL/desconocido, no se rellena con now() ni con otra fecha.\nGuardar instantes como timestamptz UTC y presentar Europe/Madrid; si solo hay fecha\nsin hora no inventar medianoche como instante real. Una hora sin zona requiere\npolitica explicita y conservar incertidumbre, incluidos cambios de horario.\n\n**Filtro por defecto propuesto: Fecha de expedicion**, si GLS acredita su campo;\nalternativa de grabacion si esa es la semantica real del contrato. La UI debe nombrar\nel criterio y mostrar origen/ausencias, nunca un COALESCE silencioso entre fechas.\nPermitir criterios explicitos de grabacion, recogida, entrega, ultimo evento o alta TH.\nPara datos sin fecha operativa ofrecer vista separada, no esconderlos como si no existieran.\n\nPara un intervalo de fechas inclusivas en la UI, convertir localmente al rango\n[inicio del primer dia, inicio del dia siguiente al ultimo) en Europe/Madrid.\nEjemplo de contraste: 04/11/2025-04/12/2025 seria [2025-11-04T00:00+01:00,\n2025-12-05T00:00+01:00). Esto es una propuesta SQL local, NO un payload GLS\nni afirmacion de que Extranet o GetEnvios utilicen esos extremos.\nPara agosto el offset cambia; calcular con zona IANA, no restar 24 horas fijas.\n\n## Indices y consultas futuras\n\n- Btree cuenta/entorno/fecha_expedicion/id para periodo con paginacion estable.\n- Btree cuenta/estado/fecha y next_sync_at para activos/incidencias; separar resultado de ciclo.\n- Indice de IDs scoped y referencias tipadas; no hacer unico el albaran/referencia libre sin prueba.\n- Indice envio/event_at e identidad revisable del evento, preservando codigo y namespace originales.\n- Busqueda de nombre normalizado/CP/poblacion con indices apropiados (trigram solo si se aprueba\n  la extension); datos minimizados, roles y decision de cifrado, no ILIKE sobre Fernet.\n\nNo se ejecuta CREATE INDEX/EXTENSION ni se optimiza una carga inexistente.\n\n## Conciliacion de conjuntos sin duplicados\n\nCrear en una fase futura un concepto de ejecucion/ventana: cuenta, entorno, fuente,\ncriterio temporal, inicio/fin, timezone, filtros de estados/canales, timestamp,\nnumero de paginas, cursor/total, hash de IDs, version de contrato y grado de cobertura.\nCobertura = desconocida / parcial / completa / fallo; un HTTP 200 no implica completa.\n\nR = IDs de GLS de una ventana completamente recuperada; L = IDs locales del mismo\nambito y criterio. R-L = pendientes de importar; R interseccion L = candidatos a\nrefresco/verificacion; L-R = discrepancias que deben investigarse, NO bajas automaticas.\nSi R es parcial/desconocido, L-R no permite afirmar ausencia remota. No usar un limite\nde pagina o una cache vencida como conjunto completo. Los 61 de Extranet son referencia\nde contraste, no contenido de R hasta recibir los IDs por una fuente autorizada.\n\nClasificar tambien registro incompleto (faltan ficha/eventos/fechas), estado obsoleto\n(segun politica SLA y ultima sync correcta), documento pendiente y conflicto de identidad.\nDeduplicar por ID fuerte scoped; relacionar devoluciones, sin fusion por nombre/referencia.\nToda ingesta atomica e idempotente; parser aplicable a originales cifrados, no copias\nsanitizadas truncadas. Un fallo posterior a la captura se reejecuta localmente sin HTTP.\n\nUn checkpoint se avanza solo al consolidar una ventana completa. Distinguir watermark\nde descubrimiento, de refresco y de POD. Reservar solape retrospectivo configurable para\npublicaciones tardias/cambios, sin fijar arbitrariamente cuantos dias bastan. Si GLS solo\nfiltra fecha de expedicion, refrescar tambien activos antiguos fuera de ayer; no confiar\nen ese filtro como feed de modificaciones ni omitir incidencias antiguas.\n\n## Condiciones para comenzar sin redisenar despues\n\nModelo, consultas PostgreSQL, deduplicacion, parser offline, capturas/replay y estados\nde completitud pueden desarrollarse tras otra aprobacion. Antes: backup/restauracion\nprobados, migracion revisada, semantica de identidad/fechas, datos sinteticos y privacidad.\nEl adapter debe distinguir `discover(window)` (capacidad pendiente) de\n`refresh_known(ids)` (capacidad documentada), con resultado y cobertura explicitos.\nNo habilitar discover ni prometer historico completo hasta contrato/prueba GLS.\nNo implementar ahora; la peticion actual se limita a evaluar y documentar.\n\n\n## Actualizacion aplicada de etapa D\n\n# DATABASE_CHANGELOG\n\n## Etapa D - migracion 018_carrier_execution\n\nEsquema aditivo, sin borrar ni duplicar el repositorio existente.\n- sender_centers: codigo unico, etiqueta, datos cifrados, revision, activo y auditoria.\n- carrier_account_senders: relacion N:M, claves foraneas y un habitual por cuenta.\n- shipment_drafts: sender_center_id y sender_revision, snapshot dentro del cifrado.\n- shipments: provider_acknowledged_at, distinto de grabacion/recogida GLS.\n- synchronizations: pause_requested y run_count, independientes del presupuesto\n  de fallos/leases ya existente.\n- synchronization_items: execution_id, receipt_ciphertext, evidence_ref, HTTP y\n  duracion. Reutiliza los checkpoints previos, sin otro repositorio de trabajos.\n- app_config: flags nuevos de creacion operativa y trabajador conocidos a false.\n\n17 a 18 probado con cuatro migradores concurrentes y filas anteriores intactas.\nBackup previo restaurado en base temporal: 54 tablas, 111 indices, hashes y\nrecuentos coincidentes. Tras la migracion se han verificado 56 tablas y 115 indices.\nEl resultado aplicado se comprueba en STAGE_D_REPORT y evidencia privada.\nRollback de aplicacion al codigo anterior, conservando el esquema aditivo.\nNo ejecutar DOWN ni restaurar la BD activa sin revision y autorizacion.\n\n"}