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:

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

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

Restricciones:

• PRIMARY KEY (id)

Indices:

• CREATE UNIQUE INDEX alerts_pkey ON public.alerts USING btree (id)

api_quota_exceptions

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

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

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

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

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

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

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

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

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

Restricciones:

• PRIMARY KEY (key)

Indices:

• CREATE UNIQUE INDEX app_config_pkey ON public.app_config USING btree (key)

app_logs

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

Restricciones:

• PRIMARY KEY (version)

Indices:

• CREATE UNIQUE INDEX app_migrations_pkey ON public.app_migrations USING btree (version)

bank_movements

Restricciones:

• PRIMARY KEY (id)

Indices:

• CREATE UNIQUE INDEX bank_movements_pkey ON public.bank_movements USING btree (id)

cache_resources

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

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

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

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

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

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

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

Restricciones:

• PRIMARY KEY (provider, environment)

Indices:

• CREATE UNIQUE INDEX connector_credentials_pkey ON public.connector_credentials USING btree (provider, environment)

documents

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

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

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

Restricciones:

• PRIMARY KEY (code)

Indices:

• CREATE UNIQUE INDEX gls_environments_pkey ON public.gls_environments USING btree (code)

gls_event_codes

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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)