# 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.

## Esquema e inventario previos completos

## Etapa C: migracion 017 aplicada

Version: 017_operational_workbench. Actualizacion aditiva desde 16 versiones,
sin eliminar ni modificar filas anteriores. Comprobacion por clave primaria y
hash de las columnas preexistentes en TODAS las tablas antes de arrancar.

### Esquema

Nueva shipment_drafts: UUID interno, cuenta prevista, propietario, creation_key,
revision CAS, referencia, payload cifrado, estado, operacion vinculada y fechas
de alta, actualizacion y revision. FK a cuentas, usuarios y operaciones.
Unicidad owner_id + creation_key evita doble guardado; una clave reutilizada
con otro contenido se rechaza. La edicion de un intento preparado no se permite.

shipments y shipment_operations incorporan data_mode live/simulation. Las
filas previas conservan live; una operacion y su observacion deben coincidir.
La simulacion usa un transportista/cuenta LOCAL sin referencias de secretos.
No se crea otra tabla de expediciones ni se copia el laboratorio a negocio.

Indices nuevos: PK/unique/propietario-actualizacion del borrador, modo-fecha
de expediciones y eventos de acceso operativo. Resultado: 54 tablas,
111 indices y 17 migraciones.

Se reutilizan shipment_operations, observaciones, identificadores, bultos,
acontecimientos, documentos, cuotas y synchronizations de etapa B.
Los trabajos administrativos se preparan pausados; no se activa scheduler.

### Datos y seguridad

No hay borradores, expediciones, operaciones, eventos ni documentos de prueba
insertados por el agente en la base activa. Solo metadatos del simulador y su
presupuesto LOCAL. No se alteran cuentas/UID ni consumo GLS existente.
Las pruebas de datos se realizaron en bases temporales nuevas y eliminadas.

### Backup y rollback

Backup previo C: [RUTA INTERNA OMITIDA]
Dump: 225399 bytes; SHA-256
`d175214bc17949371acf68764f2c6dfa3e75642cd56fa9d747fe4ff6433a2e76`.
Archivo aplicacion/configuracion: 8530563 bytes; SHA-256
`2169a86d1ee2c5cf2e26c800576340fe4c0fb1683fc967f49661d79ba7c3ca2c`.
Restauracion efectiva: 53 tablas y 106 indices, filas/hashes iguales; temporal
eliminada. El archivo privado incluye material sensible y NO se publica ni
se debe transferir sin cifrado adicional y control de acceso.

Rollback: detener/deshabilitar solo el nuevo servicio 80, restaurar codigo y
unidad anteriores desde el manifiesto del despliegue, reiniciar 88 y 85.
Dejar 017 aditiva sin uso; no ejecutar DOWN ni restaurar toda la base sobre
datos posteriores. Procedimiento privado STAGE_C_ROLLBACK.md.

El anexo de estructura se regenera desde el esquema final, sin filas de negocio.

## Registro historico: etapa B

## Etapa B aplicada

Migraciones nuevas, transaccionales y serializadas por advisory lock:

| Version | Objetivo | Tablas nuevas |
|---|---|---|
| 014_operational_repository | Repositorio por cuenta, snapshots, identidades, eventos y documentos | carrier_accounts, shipment_identifiers, shipment_references, shipment_parties, shipment_observations, shipment_documents |
| 015_operations_and_jobs | Intentos unicos, conciliacion y trabajos duraderos | shipment_operations, synchronization_items |
| 016_central_quota_core | Ambitos, ventanas, reservas y excepciones | api_quota_scopes, api_quota_scope_accounts, api_quota_windows, api_quota_exceptions, api_quota_reservations, api_quota_reservation_items |

Se reutilizan shipments, parcels, tracking_events, cod_records,
synchronizations, api_requests, api_usage_daily y api_quota_policies.
No hay segundo repositorio ni tabla paralela de expediciones de negocio.
Las tablas de laboratorio existentes permanecen intactas como evidencia.

### Fechas

Se distinguen fecha de alta local existente, shipment_date,
provider_recorded_at, picked_up_at, first_movement_at, delivered_at,
cancelled_at, last_event_at, last_observed_at, last_sync_attempt_at y
last_successful_sync_at. date_provenance conserva la fuente semantica.
No se deduce una fecha de entrega a partir del cierre administrativo.

La busqueda local inicial usa shipment_date; si falta no inventa una fecha
a partir del alta en TH. La etapa C mostrara expresamente registros sin
fecha y permitira elegir otros criterios cuando el contrato los acredite.

### Integridad y unicidad

- shipment/account y shipment/carrier tienen claves compuestas referenciables.
- Los identificadores se conservan como texto: no pierden ceros iniciales.
- Unicidad account + namespace + kind + value; no se une por nombre o CP.
- Una observacion identifica source + hash de contenido + timestamp capturado.
- Los eventos con ID estable admiten revisiones; sin ID estable se colapsan
  solo duplicados exactos. Nunca se descarta un evento por compartir estado.
- Los documentos se deduplican por shipment + kind + hash, tras validar cuenta.
- El borrador de creacion se vincula a account + operation + idempotency_key.
- La misma reserva solo puede producir una fila api_requests y un incremento
  de la agregacion diaria.

Los campos nuevos admiten las filas antiguas con valores NULL. La migracion
no asigna retroactivamente una cuenta desconocida a expediciones previas.
Se conservan todas las columnas anteriores: la comprobacion tras aplicar
solo encontro cambios previstos en app_migrations, app_config y politicas.

### Backup y rollback

Backup privado: `[RUTA INTERNA OMITIDA]`.
Dump 158246 bytes; SHA-256
`4352e5e81468f634164caba912e225cd4fd64fad1dee2d87ae678d461e378ed3`.
Archivo de aplicacion/configuracion 8275927 bytes; SHA-256
`5c41db07077c84079cd7312d6282e36a0bbdc136c9ea15a9241dfe4e685bb879`.
Restauracion efectiva: 39 tablas y 59 indices, filas/hashes iguales,
base temporal eliminada. No se publico el backup ni su contenido.

Rollback preferente: restaurar codigo previo y dejar el esquema aditivo
sin uso. No ejecutar DROP ni restaurar toda la base sobre datos nuevos.
Procedimiento completo en STAGE_B_ROLLBACK.md, conservado privadamente.
El borrador antiguo de 014 en infra/migrations/pending es evidencia de
etapa A; no se ejecuta ni sustituye las migraciones registradas actuales.

Inventario final: 53 tablas, 106 indices, 16 migraciones. El anexo de
estructura generado desde information_schema acompana este documento.

## Registro historico: etapa A

## Etapa A - sin migraciones aplicadas

Se mantienen las 13 migraciones existentes y las 39 tablas del inventario.
El interruptor del canal se conserva en un archivo de control separado;
el proceso publico no tiene conexion ni rol PostgreSQL.

Las acciones administrativas de habilitar/deshabilitar generan eventos
en app_logs mediante el mecanismo existente. No se alteran expediciones,
identificadores, cuentas, credenciales ni contadores API.

La comprobacion real del interruptor genero dos eventos identificados
como aceptacion del despliegue. El arranque habitual del 88 actualiza la
metadata de los originales conocidos; no modifica sus bytes ni descarga fuentes.

## Migracion 014 preparada, NO registrada ni ejecutada

Borrador incremental de cuentas, identificadores, referencias, snapshots
de partes, fechas operativas diferenciadas e indices. Reutiliza shipments.
No contiene DROP ni elimina datos. Las cuentas nacen inactivas.

No se incorpora a MIGRATIONS en esta etapa porque el arranque actual aplica
automaticamente las migraciones registradas. Revision y pruebas de ingesta
offline precederan a su activacion en la etapa B.

Las migraciones de eventos/documentos, trabajos y cuotas se detallaran con
sus pruebas antes de ejecutarlas. No se afirma que el modelo definitivo
ya exista ni se crean repositorios paralelos.

## Recuperacion

Backup custom y archivo privado de configuracion/codigo preservados con
SHA-256. Restauracion comprobada sobre snapshot consistente, no solo
lectura del indice del dump. Procedimiento operativo completo en el manual
privado AUDIT85_OPERATIONS. No se publican dumps ni secretos en este canal.


## Anexo generado: esquema actual completo
Estructura consultada en PostgreSQL tras 017; sin valores de negocio.

### admin_users
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| username | text | NO |
| password_hash | text | NO |
| created_at | timestamp with time zone | NO |
| last_login_at | timestamp with time zone | YES |
| display_name | text | NO |
| email | text | NO |
| role | text | NO |
| active | boolean | NO |
| updated_at | timestamp with time zone | NO |

Restricciones:
- `PRIMARY KEY (id)`
- `UNIQUE (username)`

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

### alerts
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| severity | text | NO |
| scope | text | NO |
| title | text | NO |
| message | text | NO |
| status | text | NO |
| created_at | timestamp with time zone | NO |
| resolved_at | timestamp with time zone | YES |

Restricciones:
- `PRIMARY KEY (id)`

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

### api_quota_exceptions
| Columna | Tipo | NULL |
|---|---|---|
| id | uuid | NO |
| scope_id | bigint | NO |
| actor | text | NO |
| reason_code | text | NO |
| note_ciphertext | text | YES |
| max_extra_cost | integer | NO |
| used_extra_cost | integer | NO |
| expires_at | timestamp with time zone | NO |
| created_at | timestamp with time zone | NO |

Restricciones:
- `CHECK (((used_extra_cost >= 0) AND (used_extra_cost <= max_extra_cost)))`
- `CHECK ((max_extra_cost > 0))`
- `PRIMARY KEY (id)`
- `FOREIGN KEY (scope_id) REFERENCES api_quota_scopes(id)`

Indices:
- `CREATE UNIQUE INDEX api_quota_exceptions_pkey ON public.api_quota_exceptions USING btree (id)`

### api_quota_policies
| Columna | Tipo | NULL |
|---|---|---|
| product | text | NO |
| environment | text | NO |
| period | text | NO |
| limit_count | integer | YES |
| limit_source | text | NO |
| reserve_count | integer | NO |
| warn_thresholds | jsonb | NO |
| active | boolean | NO |
| updated_at | timestamp with time zone | NO |
| provider | text | NO |
| internal_percent | numeric | NO |
| internal_limit | integer | YES |

Restricciones:
- `CHECK ((internal_limit > 0))`
- `CHECK (((internal_percent >= (0)::numeric) AND (internal_percent <= (100)::numeric)))`
- `PRIMARY KEY (product, environment, period)`

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

### api_quota_reservation_items
| Columna | Tipo | NULL |
|---|---|---|
| reservation_id | uuid | NO |
| window_id | bigint | NO |
| cost | integer | NO |
| exception_id | uuid | YES |
| extra_cost | integer | NO |

Restricciones:
- `CHECK ((cost > 0))`
- `FOREIGN KEY (exception_id) REFERENCES api_quota_exceptions(id)`
- `CHECK ((extra_cost >= 0))`
- `PRIMARY KEY (reservation_id, window_id)`
- `FOREIGN KEY (reservation_id) REFERENCES api_quota_reservations(id)`
- `FOREIGN KEY (window_id) REFERENCES api_quota_windows(id)`

Indices:
- `CREATE UNIQUE INDEX api_quota_reservation_items_pkey ON public.api_quota_reservation_items USING btree (reservation_id, window_id)`
- `CREATE INDEX idx_quota_items_window ON public.api_quota_reservation_items USING btree (window_id, reservation_id)`

### api_quota_reservations
| Columna | Tipo | NULL |
|---|---|---|
| id | uuid | NO |
| request_key | text | NO |
| fingerprint | text | NO |
| carrier_account_id | bigint | YES |
| actor | text | NO |
| priority | integer | NO |
| cost | integer | NO |
| state | text | NO |
| exception_ids | jsonb | NO |
| created_at | timestamp with time zone | NO |
| sent_at | timestamp with time zone | YES |
| finished_at | timestamp with time zone | YES |
| http_code | integer | YES |
| error_code | text | YES |

Restricciones:
- `FOREIGN KEY (carrier_account_id) REFERENCES carrier_accounts(id)`
- `CHECK ((cost > 0))`
- `PRIMARY KEY (id)`
- `CHECK (((priority >= 0) AND (priority <= 3)))`
- `UNIQUE (request_key)`
- `CHECK ((state = ANY (ARRAY['reserved'::text, 'sent'::text, 'settled'::text, 'released'::text, 'uncertain'::text])))`

Indices:
- `CREATE UNIQUE INDEX api_quota_reservations_pkey ON public.api_quota_reservations USING btree (id)`
- `CREATE UNIQUE INDEX api_quota_reservations_request_key_key ON public.api_quota_reservations USING btree (request_key)`
- `CREATE INDEX idx_quota_reservations_time ON public.api_quota_reservations USING btree (created_at, state)`

### api_quota_scope_accounts
| Columna | Tipo | NULL |
|---|---|---|
| scope_id | bigint | NO |
| carrier_account_id | bigint | NO |

Restricciones:
- `FOREIGN KEY (carrier_account_id) REFERENCES carrier_accounts(id)`
- `PRIMARY KEY (scope_id, carrier_account_id)`
- `FOREIGN KEY (scope_id) REFERENCES api_quota_scopes(id)`

Indices:
- `CREATE UNIQUE INDEX api_quota_scope_accounts_pkey ON public.api_quota_scope_accounts USING btree (scope_id, carrier_account_id)`

### api_quota_scopes
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| provider | text | NO |
| api | text | NO |
| environment | text | NO |
| scope_key | text | NO |
| policy_product | text | NO |
| policy_environment | text | NO |
| policy_period | text | NO |
| enabled | boolean | NO |
| window_mode | text | NO |
| reset_timezone | text | NO |
| reset_verified | boolean | NO |
| official_limit_override | integer | YES |
| official_limit_is_overridden | boolean | NO |
| limit_source_override | text | YES |
| internal_limit_override | integer | YES |
| internal_percent_override | numeric | YES |
| reserve_override | integer | YES |
| hours_json | jsonb | NO |
| cooldown_until | timestamp with time zone | YES |
| created_at | timestamp with time zone | NO |

Restricciones:
- `CHECK ((internal_limit_override > 0))`
- `CHECK (((internal_percent_override >= (0)::numeric) AND (internal_percent_override <= (100)::numeric)))`
- `CHECK ((official_limit_override > 0))`
- `PRIMARY KEY (id)`
- `FOREIGN KEY (policy_product, policy_environment, policy_period) REFERENCES api_quota_policies(product, environment, period)`
- `UNIQUE (provider, api, environment, scope_key, policy_period)`
- `CHECK ((reserve_override >= 0))`
- `CHECK ((window_mode = ANY (ARRAY['rolling'::text, 'calendar'::text])))`

Indices:
- `CREATE UNIQUE INDEX api_quota_scopes_pkey ON public.api_quota_scopes USING btree (id)`
- `CREATE UNIQUE INDEX api_quota_scopes_provider_api_environment_scope_key_policy__key ON public.api_quota_scopes USING btree (provider, api, environment, scope_key, policy_period)`

### api_quota_windows
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| scope_id | bigint | NO |
| starts_at | timestamp with time zone | NO |
| ends_at | timestamp with time zone | NO |
| used_cost | bigint | NO |
| reserved_cost | bigint | NO |

Restricciones:
- `CHECK ((ends_at > starts_at))`
- `PRIMARY KEY (id)`
- `CHECK ((reserved_cost >= 0))`
- `FOREIGN KEY (scope_id) REFERENCES api_quota_scopes(id)`
- `UNIQUE (scope_id, starts_at)`
- `CHECK ((used_cost >= 0))`

Indices:
- `CREATE UNIQUE INDEX api_quota_windows_pkey ON public.api_quota_windows USING btree (id)`
- `CREATE UNIQUE INDEX api_quota_windows_scope_id_starts_at_key ON public.api_quota_windows USING btree (scope_id, starts_at)`

### api_requests
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| provider | text | NO |
| product | text | NO |
| environment | text | NO |
| endpoint_logical | text | NO |
| method | text | NO |
| occurred_at | timestamp with time zone | NO |
| http_code | integer | YES |
| duration_ms | integer | NO |
| result | text | NO |
| items_requested | integer | NO |
| items_returned | integer | NO |
| origin | text | NO |
| actor | text | YES |
| correlation_id | uuid | YES |
| quota_computed | numeric | YES |
| error_sanitized | text | YES |
| details_json | jsonb | NO |
| carrier_account_id | bigint | YES |
| quota_reservation_id | uuid | YES |

Restricciones:
- `FOREIGN KEY (carrier_account_id) REFERENCES carrier_accounts(id)`
- `PRIMARY KEY (id)`
- `FOREIGN KEY (quota_reservation_id) REFERENCES api_quota_reservations(id)`

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

### api_usage_daily
| Columna | Tipo | NULL |
|---|---|---|
| usage_date | date | NO |
| product | text | NO |
| environment | text | NO |
| requests | integer | NO |
| successes | integer | NO |
| errors | integer | NO |
| items_processed | integer | NO |
| avg_duration_ms | numeric | NO |
| updated_at | timestamp with time zone | NO |

Restricciones:
- `PRIMARY KEY (usage_date, product, environment)`

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

### app_config
| Columna | Tipo | NULL |
|---|---|---|
| key | text | NO |
| value | text | NO |
| type | text | NO |
| updated_at | timestamp with time zone | NO |

Restricciones:
- `PRIMARY KEY (key)`

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

### app_logs
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| level | text | NO |
| event | text | NO |
| message | text | NO |
| user_id | bigint | YES |
| details_json | jsonb | NO |
| created_at | timestamp with time zone | NO |

Restricciones:
- `PRIMARY KEY (id)`
- `FOREIGN KEY (user_id) REFERENCES admin_users(id) ON DELETE SET NULL`

Indices:
- `CREATE UNIQUE INDEX app_logs_pkey ON public.app_logs USING btree (id)`
- `CREATE INDEX idx_ops_login_events ON public.app_logs USING btree (event, created_at DESC) WHERE (event = 'ops_login_attempt'::text)`

### app_migrations
| Columna | Tipo | NULL |
|---|---|---|
| version | text | NO |
| applied_at | timestamp with time zone | NO |

Restricciones:
- `PRIMARY KEY (version)`

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

### bank_movements
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| movement_date | date | YES |
| concept | text | YES |
| amount | numeric | YES |
| currency | text | NO |
| matched_status | text | NO |
| created_at | timestamp with time zone | NO |

Restricciones:
- `PRIMARY KEY (id)`

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

### cache_resources
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| resource_key | text | NO |
| carrier_code | text | NO |
| resource_name | text | NO |
| origin | text | NO |
| ttl_seconds | integer | NO |
| policy | text | NO |
| last_obtained_at | timestamp with time zone | YES |
| expires_at | timestamp with time zone | YES |
| enabled | boolean | NO |
| updated_at | timestamp with time zone | NO |

Restricciones:
- `PRIMARY KEY (id)`
- `UNIQUE (resource_key)`

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

### carrier_accounts
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| carrier_id | bigint | NO |
| code | text | NO |
| environment | text | NO |
| integration_family | text | NO |
| agency_code | text | YES |
| customer_code | text | YES |
| credential_ref | text | YES |
| active | boolean | NO |
| outbound_enabled | boolean | NO |
| create_enabled | boolean | NO |
| created_at | timestamp with time zone | NO |

Restricciones:
- `UNIQUE (carrier_id, code, environment)`
- `FOREIGN KEY (carrier_id) REFERENCES carriers(id)`
- `CHECK ((environment = ANY (ARRAY['test'::text, 'sandbox'::text, 'production'::text])))`
- `UNIQUE (id, carrier_id)`
- `PRIMARY KEY (id)`

Indices:
- `CREATE UNIQUE INDEX carrier_accounts_carrier_id_code_environment_key ON public.carrier_accounts USING btree (carrier_id, code, environment)`
- `CREATE UNIQUE INDEX carrier_accounts_id_carrier_id_key ON public.carrier_accounts USING btree (id, carrier_id)`
- `CREATE UNIQUE INDEX carrier_accounts_pkey ON public.carrier_accounts USING btree (id)`

### carrier_capabilities
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| carrier_id | bigint | NO |
| capability | text | NO |
| status | text | NO |
| depends_on | text | NO |
| notes | text | NO |
| updated_at | timestamp with time zone | NO |

Restricciones:
- `UNIQUE (carrier_id, capability)`
- `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE CASCADE`
- `PRIMARY KEY (id)`

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

### carriers
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| code | text | NO |
| name | text | NO |
| status | text | NO |
| notes | text | NO |
| created_at | timestamp with time zone | NO |
| updated_at | timestamp with time zone | NO |

Restricciones:
- `UNIQUE (code)`
- `PRIMARY KEY (id)`

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

### cod_records
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| shipment_id | bigint | YES |
| parcel_id | bigint | YES |
| carrier_id | bigint | YES |
| amount | numeric | YES |
| currency | text | NO |
| status | text | NO |
| created_at | timestamp with time zone | NO |
| updated_at | timestamp with time zone | NO |
| observation_id | bigint | YES |

Restricciones:
- `FOREIGN KEY (shipment_id, carrier_id) REFERENCES shipments(id, carrier_id)`
- `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL`
- `FOREIGN KEY (observation_id) REFERENCES shipment_observations(id)`
- `FOREIGN KEY (parcel_id) REFERENCES parcels(id) ON DELETE SET NULL`
- `PRIMARY KEY (id)`
- `FOREIGN KEY (shipment_id) REFERENCES shipments(id) ON DELETE SET NULL`

Indices:
- `CREATE UNIQUE INDEX cod_records_pkey ON public.cod_records USING btree (id)`
- `CREATE UNIQUE INDEX idx_cod_observation ON public.cod_records USING btree (shipment_id, observation_id) WHERE (observation_id IS NOT NULL)`

### cod_settlements
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| carrier_id | bigint | YES |
| settlement_reference | text | YES |
| period_start | date | YES |
| period_end | date | YES |
| amount | numeric | YES |
| status | text | NO |
| created_at | timestamp with time zone | NO |
| updated_at | timestamp with time zone | NO |

Restricciones:
- `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL`
- `PRIMARY KEY (id)`

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

### connection_tests
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| 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 |
| launched_by | bigint | YES |
| endpoint_logical | text | YES |
| diagnostic_run_id | uuid | YES |
| classification | text | YES |

Restricciones:
- `FOREIGN KEY (launched_by) REFERENCES admin_users(id) ON DELETE SET NULL`
- `PRIMARY KEY (id)`

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

### connector_credentials
| Columna | Tipo | NULL |
|---|---|---|
| provider | text | NO |
| environment | text | NO |
| client_id_ciphertext | text | NO |
| client_secret_ciphertext | text | NO |
| created_at | timestamp with time zone | NO |
| updated_at | timestamp with time zone | NO |
| last_validated_at | timestamp with time zone | YES |
| validation_status | text | NO |

Restricciones:
- `PRIMARY KEY (provider, environment)`

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

### documents
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| slug | text | NO |
| title | text | NO |
| path | text | NO |
| document_type | text | NO |
| category | text | NO |
| checksum | text | YES |
| updated_at | timestamp with time zone | NO |

Restricciones:
- `PRIMARY KEY (id)`
- `UNIQUE (slug)`

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

### enrichment_profiles
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| carrier_code | text | NO |
| level | text | NO |
| description | text | NO |
| estimated_extra_calls | integer | NO |
| rules_json | jsonb | NO |
| active | boolean | NO |
| updated_at | timestamp with time zone | NO |

Restricciones:
- `UNIQUE (carrier_code, level)`
- `PRIMARY KEY (id)`

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

### gls_allowed_services_cache
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| origin | text | NO |
| destination | text | NO |
| contact_id | text | YES |
| product | text | YES |
| service | text | YES |
| queried_at | timestamp with time zone | NO |
| response_hash | text | NO |
| active | boolean | NO |

Restricciones:
- `UNIQUE (origin, destination, contact_id, product, service)`
- `PRIMARY KEY (id)`

Indices:
- `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)`
- `CREATE UNIQUE INDEX gls_allowed_services_cache_pkey ON public.gls_allowed_services_cache USING btree (id)`

### gls_environments
| Columna | Tipo | NULL |
|---|---|---|
| code | text | NO |
| name | text | NO |
| label | text | NO |
| active | boolean | NO |
| locked | boolean | NO |
| state | text | NO |
| last_validated_at | timestamp with time zone | YES |
| oauth_urls | jsonb | NO |
| base_urls | jsonb | NO |
| enabled_apis | jsonb | NO |
| backend_authorization | jsonb | NO |
| limits_json | jsonb | NO |
| config_json | jsonb | NO |
| observations | text | NO |
| created_at | timestamp with time zone | NO |
| updated_at | timestamp with time zone | NO |

Restricciones:
- `PRIMARY KEY (code)`

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

### gls_event_codes
| Columna | Tipo | NULL |
|---|---|---|
| code | text | NO |
| language | text | NO |
| description | text | NO |
| first_seen_at | timestamp with time zone | NO |
| last_seen_at | timestamp with time zone | NO |
| active | boolean | NO |
| raw_hash | text | NO |

Restricciones:
- `PRIMARY KEY (code, language)`

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

### gls_product_matrix
| Columna | Tipo | NULL |
|---|---|---|
| product | text | NO |
| environment | text | NO |
| status | text | NO |
| last_test_id | bigint | YES |
| evidence | text | NO |
| pending | text | NO |
| credential_ref_provider | text | YES |
| credential_ref_environment | text | YES |
| updated_at | timestamp with time zone | NO |

Restricciones:
- `FOREIGN KEY (environment) REFERENCES gls_environments(code)`
- `FOREIGN KEY (last_test_id) REFERENCES connection_tests(id) ON DELETE SET NULL`
- `PRIMARY KEY (product, environment)`

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

### iclientes_credentials
| Columna | Tipo | NULL |
|---|---|---|
| environment | text | NO |
| secret_ref | text | NO |
| description | text | NO |
| created_at | timestamp with time zone | NO |
| updated_at | timestamp with time zone | NO |

Restricciones:
- `CHECK ((environment = ANY (ARRAY['test'::text, 'production'::text])))`
- `PRIMARY KEY (environment)`
- `UNIQUE (secret_ref)`

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

### iclientes_lab_labels
| Columna | Tipo | NULL |
|---|---|---|
| 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 |
| image_width | integer | YES |
| image_height | integer | YES |
| declared_dpi | jsonb | YES |
| source_metadata | jsonb | NO |

Restricciones:
- `PRIMARY KEY (id)`
- `FOREIGN KEY (trial_id) REFERENCES iclientes_lab_trials(id)`

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

### iclientes_lab_queries
| Columna | Tipo | NULL |
|---|---|---|
| id | uuid | NO |
| trial_id | uuid | NO |
| created_by | integer | NO |
| nonce_hash | text | NO |
| state | text | NO |
| created_at | timestamp with time zone | NO |
| 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 |
| operation | text | NO |

Restricciones:
- `FOREIGN KEY (api_request_id) REFERENCES api_requests(id)`
- `FOREIGN KEY (created_by) REFERENCES admin_users(id)`
- `CHECK ((operation = ANY (ARRAY['GetExpCli'::text, 'EtiquetaEnvioV2'::text])))`
- `PRIMARY KEY (id)`
- `CHECK ((state = ANY (ARRAY['sending'::text, 'complete'::text, 'unknown'::text, 'not_sent'::text])))`
- `FOREIGN KEY (trial_id) REFERENCES iclientes_lab_trials(id)`
- `UNIQUE (trial_id, nonce_hash)`

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

### iclientes_lab_trials
| Columna | Tipo | NULL |
|---|---|---|
| id | uuid | NO |
| environment | text | NO |
| reference | text | NO |
| created_by | integer | NO |
| form_ciphertext | text | NO |
| confirm_hash | text | NO |
| state | text | NO |
| created_at | timestamp with time zone | NO |
| 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 |

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

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

### invoice_lines
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| invoice_id | bigint | YES |
| shipment_id | bigint | YES |
| description | text | YES |
| amount | numeric | YES |
| details_json | jsonb | NO |

Restricciones:
- `FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE`
- `PRIMARY KEY (id)`
- `FOREIGN KEY (shipment_id) REFERENCES shipments(id) ON DELETE SET NULL`

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

### invoices
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| carrier_id | bigint | YES |
| invoice_number | text | YES |
| invoice_date | date | YES |
| amount | numeric | YES |
| status | text | NO |
| created_at | timestamp with time zone | NO |
| updated_at | timestamp with time zone | NO |

Restricciones:
- `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL`
- `PRIMARY KEY (id)`

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

### knowledge_articles
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| slug | text | NO |
| title | text | NO |
| path | text | NO |
| category | text | NO |
| updated_at | timestamp with time zone | NO |

Restricciones:
- `PRIMARY KEY (id)`
- `UNIQUE (slug)`

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

### known_api_endpoints
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| carrier_code | text | NO |
| product | text | NO |
| endpoint_key | text | NO |
| method | text | NO |
| path | text | NO |
| environment | text | NO |
| authentication | text | NO |
| description | text | NO |
| parameters_json | jsonb | NO |
| examples_json | jsonb | NO |
| limitations | text | NO |
| quota_known | text | NO |
| current_status | text | NO |
| last_test_id | bigint | YES |
| updated_at | timestamp with time zone | NO |

Restricciones:
- `UNIQUE (carrier_code, endpoint_key, environment)`
- `FOREIGN KEY (last_test_id) REFERENCES connection_tests(id) ON DELETE SET NULL`
- `PRIMARY KEY (id)`

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

### official_documents
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| carrier_code | text | NO |
| 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 |
| version | text | YES |
| document_date | date | YES |
| provenance | text | NO |
| description | text | NO |
| status | text | NO |
| physical_path | text | NO |
| is_vendor_official | boolean | NO |
| original_or_derivative | text | NO |
| derivative_of_id | bigint | YES |
| downloadable | boolean | NO |
| created_at | timestamp with time zone | NO |
| updated_at | timestamp with time zone | NO |

Restricciones:
- `FOREIGN KEY (derivative_of_id) REFERENCES official_documents(id) ON DELETE SET NULL`
- `UNIQUE (physical_path)`
- `PRIMARY KEY (id)`

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

### parcels
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| shipment_id | bigint | YES |
| carrier_id | bigint | YES |
| tracking_number | text | YES |
| carrier_tracking_id | text | YES |
| status | text | NO |
| last_event_at | timestamp with time zone | YES |
| created_at | timestamp with time zone | NO |
| updated_at | timestamp with time zone | NO |
| external_key | text | YES |
| weight | numeric | YES |

Restricciones:
- `FOREIGN KEY (shipment_id, carrier_id) REFERENCES shipments(id, carrier_id)`
- `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL`
- `UNIQUE (id, shipment_id)`
- `PRIMARY KEY (id)`
- `FOREIGN KEY (shipment_id) REFERENCES shipments(id) ON DELETE CASCADE`
- `CHECK ((weight >= (0)::numeric))`

Indices:
- `CREATE UNIQUE INDEX idx_parcels_external_key ON public.parcels USING btree (shipment_id, external_key) WHERE (external_key IS NOT NULL)`
- `CREATE UNIQUE INDEX parcels_id_shipment_unique ON public.parcels USING btree (id, shipment_id)`
- `CREATE UNIQUE INDEX parcels_pkey ON public.parcels USING btree (id)`

### print_derivatives
| Columna | Tipo | NULL |
|---|---|---|
| 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 |

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

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

### print_jobs
| Columna | Tipo | NULL |
|---|---|---|
| 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 |
| created_at | timestamp with time zone | NO |
| 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 |
| reviewed_at | timestamp with time zone | YES |
| reviewed_by | integer | YES |

Restricciones:
- `CHECK (((copies >= 1) AND (copies <= 5)))`
- `FOREIGN KEY (created_by) REFERENCES admin_users(id)`
- `FOREIGN KEY (derivative_id) REFERENCES print_derivatives(id)`
- `FOREIGN KEY (label_id) REFERENCES iclientes_lab_labels(id)`
- `CHECK ((method = ANY (ARRAY['cups'::text, 'epl2'::text])))`
- `UNIQUE (nonce_hash)`
- `CHECK ((physical_result = ANY (ARRAY['unreviewed'::text, 'good'::text, 'bad'::text])))`
- `PRIMARY KEY (id)`
- `FOREIGN KEY (printer_id) REFERENCES print_printers(id)`
- `FOREIGN KEY (reviewed_by) REFERENCES admin_users(id)`
- `CHECK ((state = ANY (ARRAY['preparing'::text, 'sending'::text, 'submitted'::text, 'transmitted'::text, 'error'::text, 'unknown'::text])))`
- `FOREIGN KEY (trial_id) REFERENCES iclientes_lab_trials(id)`

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

### print_printers
| Columna | Tipo | NULL |
|---|---|---|
| id | text | NO |
| name | text | NO |
| host | text | NO |
| port | integer | NO |
| queue | text | NO |
| enabled | boolean | NO |
| max_copies | integer | NO |
| threshold | integer | NO |
| default_method | text | NO |
| updated_at | timestamp with time zone | NO |

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

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

### shipment_documents
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| shipment_id | bigint | NO |
| carrier_account_id | bigint | NO |
| kind | text | NO |
| format | text | YES |
| asset_id | uuid | NO |
| sha256 | text | NO |
| mime_type | text | NO |
| size_bytes | bigint | NO |
| source | text | NO |
| source_locator_ciphertext | text | YES |
| observed_at | timestamp with time zone | NO |
| created_at | timestamp with time zone | NO |

Restricciones:
- `PRIMARY KEY (id)`
- `CHECK ((sha256 ~ '^[0-9a-f]{64}$'::text))`
- `FOREIGN KEY (shipment_id, carrier_account_id) REFERENCES shipments(id, carrier_account_id)`
- `UNIQUE (shipment_id, kind, sha256)`
- `CHECK ((size_bytes >= 0))`

Indices:
- `CREATE UNIQUE INDEX shipment_documents_pkey ON public.shipment_documents USING btree (id)`
- `CREATE UNIQUE INDEX shipment_documents_shipment_id_kind_sha256_key ON public.shipment_documents USING btree (shipment_id, kind, sha256)`

### shipment_drafts
| Columna | Tipo | NULL |
|---|---|---|
| id | uuid | NO |
| carrier_account_id | bigint | NO |
| owner_id | bigint | NO |
| creation_key | uuid | NO |
| revision | integer | NO |
| reference | text | NO |
| payload_ciphertext | text | NO |
| state | text | NO |
| operation_id | uuid | YES |
| created_at | timestamp with time zone | NO |
| updated_at | timestamp with time zone | NO |
| reviewed_at | timestamp with time zone | YES |

Restricciones:
- `FOREIGN KEY (carrier_account_id) REFERENCES carrier_accounts(id)`
- `FOREIGN KEY (operation_id) REFERENCES shipment_operations(id)`
- `UNIQUE (owner_id, creation_key)`
- `FOREIGN KEY (owner_id) REFERENCES admin_users(id)`
- `PRIMARY KEY (id)`
- `CHECK ((revision > 0))`
- `CHECK ((state = ANY (ARRAY['editing'::text, 'review'::text, 'processing'::text, 'completed'::text, 'reconciliation_required'::text, 'failed'::text])))`

Indices:
- `CREATE INDEX idx_drafts_owner_updated ON public.shipment_drafts USING btree (owner_id, updated_at DESC)`
- `CREATE UNIQUE INDEX shipment_drafts_owner_id_creation_key_key ON public.shipment_drafts USING btree (owner_id, creation_key)`
- `CREATE UNIQUE INDEX shipment_drafts_pkey ON public.shipment_drafts USING btree (id)`

### shipment_identifiers
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| shipment_id | bigint | NO |
| carrier_account_id | bigint | NO |
| parcel_id | bigint | YES |
| namespace | text | NO |
| kind | text | NO |
| value | text | NO |
| first_seen_at | timestamp with time zone | NO |
| last_seen_at | timestamp with time zone | NO |

Restricciones:
- `UNIQUE (carrier_account_id, namespace, kind, value)`
- `FOREIGN KEY (parcel_id, shipment_id) REFERENCES parcels(id, shipment_id)`
- `PRIMARY KEY (id)`
- `FOREIGN KEY (shipment_id, carrier_account_id) REFERENCES shipments(id, carrier_account_id)`
- `CHECK (((length(value) >= 1) AND (length(value) <= 200)))`

Indices:
- `CREATE UNIQUE INDEX shipment_identifiers_carrier_account_id_namespace_kind_valu_key ON public.shipment_identifiers USING btree (carrier_account_id, namespace, kind, value)`
- `CREATE UNIQUE INDEX shipment_identifiers_pkey ON public.shipment_identifiers USING btree (id)`

### shipment_observations
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| shipment_id | bigint | NO |
| carrier_account_id | bigint | NO |
| source | text | NO |
| payload_hash | text | NO |
| payload_ciphertext | text | NO |
| evidence_ref | text | YES |
| operation_id | uuid | YES |
| observed_at | timestamp with time zone | NO |
| created_at | timestamp with time zone | NO |

Restricciones:
- `FOREIGN KEY (operation_id, carrier_account_id) REFERENCES shipment_operations(id, carrier_account_id)`
- `PRIMARY KEY (id)`
- `FOREIGN KEY (shipment_id, carrier_account_id) REFERENCES shipments(id, carrier_account_id)`
- `UNIQUE (shipment_id, source, payload_hash, observed_at)`

Indices:
- `CREATE UNIQUE INDEX shipment_observations_pkey ON public.shipment_observations USING btree (id)`
- `CREATE UNIQUE INDEX shipment_observations_shipment_id_source_payload_hash_obser_key ON public.shipment_observations USING btree (shipment_id, source, payload_hash, observed_at)`

### shipment_operations
| Columna | Tipo | NULL |
|---|---|---|
| id | uuid | NO |
| carrier_account_id | bigint | NO |
| operation | text | NO |
| idempotency_key | text | NO |
| request_hash | text | NO |
| request_ciphertext | text | NO |
| reference | text | NO |
| state | text | NO |
| attempts | integer | NO |
| shipment_id | bigint | YES |
| evidence_ref | text | YES |
| receipt_ciphertext | text | YES |
| result_json | jsonb | NO |
| error_code | text | YES |
| created_at | timestamp with time zone | NO |
| started_at | timestamp with time zone | YES |
| finished_at | timestamp with time zone | YES |
| data_mode | text | NO |

Restricciones:
- `CHECK (((attempts >= 0) AND (attempts <= 1)))`
- `FOREIGN KEY (carrier_account_id) REFERENCES carrier_accounts(id)`
- `UNIQUE (carrier_account_id, operation, idempotency_key)`
- `CHECK ((data_mode = ANY (ARRAY['live'::text, 'simulation'::text])))`
- `UNIQUE (id, carrier_account_id)`
- `CHECK (((length(idempotency_key) >= 1) AND (length(idempotency_key) <= 128)))`
- `PRIMARY KEY (id)`
- `FOREIGN KEY (shipment_id, carrier_account_id) REFERENCES shipments(id, carrier_account_id)`
- `CHECK ((state = ANY (ARRAY['prepared'::text, 'sending'::text, 'completed'::text, 'rejected'::text, 'not_sent'::text, 'ambiguous'::text, 'reconciliation_required'::text])))`

Indices:
- `CREATE INDEX idx_operations_reconciliation ON public.shipment_operations USING btree (state, started_at)`
- `CREATE UNIQUE INDEX shipment_operations_carrier_account_id_operation_idempotenc_key ON public.shipment_operations USING btree (carrier_account_id, operation, idempotency_key)`
- `CREATE UNIQUE INDEX shipment_operations_id_carrier_account_id_key ON public.shipment_operations USING btree (id, carrier_account_id)`
- `CREATE UNIQUE INDEX shipment_operations_pkey ON public.shipment_operations USING btree (id)`

### shipment_parties
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| shipment_id | bigint | NO |
| role | text | NO |
| revision | integer | NO |
| display_name | text | YES |
| search_name | text | YES |
| locality | text | YES |
| postal_code | text | YES |
| country_code | text | YES |
| snapshot_hash | text | NO |
| private_snapshot_ciphertext | text | NO |
| observation_id | bigint | YES |
| source | text | NO |
| observed_at | timestamp with time zone | NO |

Restricciones:
- `FOREIGN KEY (observation_id) REFERENCES shipment_observations(id)`
- `PRIMARY KEY (id)`
- `CHECK ((revision > 0))`
- `CHECK ((role = ANY (ARRAY['sender'::text, 'recipient'::text])))`
- `FOREIGN KEY (shipment_id) REFERENCES shipments(id)`
- `UNIQUE (shipment_id, role, observation_id)`
- `UNIQUE (shipment_id, role, revision)`

Indices:
- `CREATE INDEX idx_shipment_parties_search ON public.shipment_parties USING btree (role, search_name, postal_code)`
- `CREATE UNIQUE INDEX shipment_parties_pkey ON public.shipment_parties USING btree (id)`
- `CREATE UNIQUE INDEX shipment_parties_shipment_id_role_observation_id_key ON public.shipment_parties USING btree (shipment_id, role, observation_id)`
- `CREATE UNIQUE INDEX shipment_parties_shipment_id_role_revision_key ON public.shipment_parties USING btree (shipment_id, role, revision)`

### shipment_references
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| shipment_id | bigint | NO |
| reference_type | text | NO |
| value | text | NO |
| source | text | NO |
| created_at | timestamp with time zone | NO |

Restricciones:
- `PRIMARY KEY (id)`
- `FOREIGN KEY (shipment_id) REFERENCES shipments(id)`
- `UNIQUE (shipment_id, reference_type, value, source)`

Indices:
- `CREATE INDEX idx_shipment_references_value ON public.shipment_references USING btree (reference_type, value)`
- `CREATE UNIQUE INDEX shipment_references_pkey ON public.shipment_references USING btree (id)`
- `CREATE UNIQUE INDEX shipment_references_shipment_id_reference_type_value_source_key ON public.shipment_references USING btree (shipment_id, reference_type, value, source)`

### shipments
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| carrier_id | bigint | YES |
| external_reference | text | YES |
| status | text | NO |
| source_system | text | YES |
| created_at | timestamp with time zone | NO |
| updated_at | timestamp with time zone | NO |
| carrier_account_id | bigint | YES |
| external_number | text | YES |
| shipment_date | date | YES |
| provider_recorded_at | timestamp with time zone | YES |
| picked_up_at | timestamp with time zone | YES |
| first_movement_at | timestamp with time zone | YES |
| delivered_at | timestamp with time zone | YES |
| cancelled_at | timestamp with time zone | YES |
| last_event_at | timestamp with time zone | YES |
| last_observed_at | timestamp with time zone | YES |
| last_sync_attempt_at | timestamp with time zone | YES |
| last_successful_sync_at | timestamp with time zone | YES |
| date_provenance | jsonb | NO |
| raw_status_code | text | YES |
| administrative_status | text | YES |
| lifecycle_status | text | YES |
| outcome_status | text | YES |
| terminal | boolean | YES |
| incident_active | boolean | YES |
| service_code | text | YES |
| service_name | text | YES |
| schedule_code | text | YES |
| parcel_count | integer | YES |
| total_weight | numeric | YES |
| cod_present | boolean | YES |
| data_mode | text | NO |

Restricciones:
- `FOREIGN KEY (carrier_account_id, carrier_id) REFERENCES carrier_accounts(id, carrier_id)`
- `FOREIGN KEY (carrier_account_id) REFERENCES carrier_accounts(id)`
- `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL`
- `CHECK ((data_mode = ANY (ARRAY['live'::text, 'simulation'::text])))`
- `UNIQUE (id, carrier_account_id)`
- `UNIQUE (id, carrier_id)`
- `CHECK ((parcel_count >= 0))`
- `PRIMARY KEY (id)`
- `CHECK ((total_weight >= (0)::numeric))`

Indices:
- `CREATE INDEX idx_shipments_account_date ON public.shipments USING btree (carrier_account_id, shipment_date, id)`
- `CREATE INDEX idx_shipments_account_state ON public.shipments USING btree (carrier_account_id, status, shipment_date, id)`
- `CREATE UNIQUE INDEX idx_shipments_external_number ON public.shipments USING btree (carrier_account_id, external_number) WHERE (external_number IS NOT NULL)`
- `CREATE INDEX idx_shipments_mode_date ON public.shipments USING btree (data_mode, shipment_date DESC, id DESC)`
- `CREATE UNIQUE INDEX shipments_id_account_unique ON public.shipments USING btree (id, carrier_account_id)`
- `CREATE UNIQUE INDEX shipments_id_carrier_unique ON public.shipments USING btree (id, carrier_id)`
- `CREATE UNIQUE INDEX shipments_pkey ON public.shipments USING btree (id)`

### synchronization_items
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| synchronization_id | bigint | NO |
| item_key | text | NO |
| status | text | NO |
| shipment_id | bigint | YES |
| attempts | integer | NO |
| error_code | text | YES |
| checkpoint_json | jsonb | NO |
| updated_at | timestamp with time zone | NO |

Restricciones:
- `PRIMARY KEY (id)`
- `FOREIGN KEY (shipment_id) REFERENCES shipments(id)`
- `FOREIGN KEY (synchronization_id) REFERENCES synchronizations(id)`
- `UNIQUE (synchronization_id, item_key)`

Indices:
- `CREATE UNIQUE INDEX synchronization_items_pkey ON public.synchronization_items USING btree (id)`
- `CREATE UNIQUE INDEX synchronization_items_synchronization_id_item_key_key ON public.synchronization_items USING btree (synchronization_id, item_key)`

### synchronizations
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| carrier_id | bigint | YES |
| sync_type | text | NO |
| status | text | NO |
| started_at | timestamp with time zone | YES |
| finished_at | timestamp with time zone | YES |
| summary_json | jsonb | NO |
| created_at | timestamp with time zone | NO |
| carrier_account_id | bigint | YES |
| idempotency_key | text | YES |
| priority | integer | NO |
| payload_ciphertext | text | YES |
| run_after | timestamp with time zone | NO |
| attempts | integer | NO |
| max_attempts | integer | NO |
| lease_owner | uuid | YES |
| lease_until | timestamp with time zone | YES |
| error_code | text | YES |
| coverage | text | NO |

Restricciones:
- `FOREIGN KEY (carrier_account_id) REFERENCES carrier_accounts(id)`
- `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL`
- `CHECK ((coverage = ANY (ARRAY['unknown'::text, 'partial'::text, 'complete'::text, 'failed'::text])))`
- `CHECK (((max_attempts >= 1) AND (max_attempts <= 10)))`
- `PRIMARY KEY (id)`
- `CHECK (((priority >= 0) AND (priority <= 3)))`

Indices:
- `CREATE INDEX idx_sync_claim ON public.synchronizations USING btree (status, priority, run_after, id)`
- `CREATE UNIQUE INDEX idx_sync_idempotency ON public.synchronizations USING btree (carrier_account_id, sync_type, idempotency_key) WHERE (idempotency_key IS NOT NULL)`
- `CREATE UNIQUE INDEX synchronizations_pkey ON public.synchronizations USING btree (id)`

### technical_lessons
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| lesson_key | text | NO |
| title | text | NO |
| body | text | NO |
| severity | text | NO |
| carrier_code | text | NO |
| source | text | NO |
| evidence_test_id | bigint | YES |
| created_at | timestamp with time zone | NO |

Restricciones:
- `FOREIGN KEY (evidence_test_id) REFERENCES connection_tests(id) ON DELETE SET NULL`
- `UNIQUE (lesson_key)`
- `PRIMARY KEY (id)`

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

### tracking_events
| Columna | Tipo | NULL |
|---|---|---|
| id | bigint | NO |
| 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 |
| created_at | timestamp with time zone | NO |
| shipment_id | bigint | YES |
| namespace | text | YES |
| event_type | text | YES |
| source_key | text | YES |
| revision | integer | YES |
| supersedes_id | bigint | YES |
| observation_id | bigint | YES |
| raw_event_at | text | YES |
| details_ciphertext | text | YES |

Restricciones:
- `FOREIGN KEY (shipment_id, carrier_id) REFERENCES shipments(id, carrier_id)`
- `FOREIGN KEY (parcel_id, shipment_id) REFERENCES parcels(id, shipment_id)`
- `FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE SET NULL`
- `FOREIGN KEY (observation_id) REFERENCES shipment_observations(id)`
- `FOREIGN KEY (parcel_id) REFERENCES parcels(id) ON DELETE CASCADE`
- `PRIMARY KEY (id)`
- `FOREIGN KEY (shipment_id) REFERENCES shipments(id)`
- `FOREIGN KEY (supersedes_id) REFERENCES tracking_events(id)`

Indices:
- `CREATE UNIQUE INDEX idx_events_content ON public.tracking_events USING btree (shipment_id, namespace, source_key, raw_hash) WHERE (shipment_id IS NOT NULL)`
- `CREATE UNIQUE INDEX idx_events_revision ON public.tracking_events USING btree (shipment_id, namespace, source_key, revision) WHERE (shipment_id IS NOT NULL)`
- `CREATE INDEX idx_events_timeline ON public.tracking_events USING btree (shipment_id, event_at, id)`
- `CREATE UNIQUE INDEX tracking_events_pkey ON public.tracking_events USING btree (id)`