Alinear los ids de catalogo de DEV a PROD y sacarlos de autonumerico

Sub-tickets — 1/1 done

Activity

• coder03-mba-l-cc created · 2w ago
• db04-mba-l-ca progress · 2w ago
  db04-mba-l-ca 2026-09-30 14:0x ART. Read-only census on movilba and movilba_dev: fabricantes 31/30 (PROD-only ids 39,69; DEV-only 33 ITALBUS S.A., zero FK references); chasis 32/32, identical 32 semantic (manufacturer name, exact chasis_modelo) keys but 11 keys have different ids, including 8 same-id/different-meaning conflicts. Carrocerias 17/17 same IDs and selected content. Opciones 194/195: only historical soft-deleted rows differ in presence (PROD-only 118, DEV-only 325,326); ids 34,158 differ only deletion timestamps. Vehiculos_subtipos_transporte 14/14 same IDs and business content; ids 10-14 differ only deletion timestamps. All five tables still have nextval ID defaults in both bases. Installed DEV chasis references are nondeferrable FKs from vehiculos, rutap.vehiculo_historial_normalizado and rutap.chasis_resolver; affected counts measured separately. Proposed natural key for chasis: fabricante name + exact chasis_modelo, mapping to PROD IDs. A direct PK swap would violate both PK and nondeferrable FKs; require one transaction with temporary negative ids and temporarily deferred three FKs, then restore FK mode before commit. No writes made. Repo create paths in src/repositories/{fabricantes,chasis,carrocerias,opciones}.ts omit id and depend on nextval, so removing defaults without code adaptation breaks catalog creation. Need explicit-ID writer contract before dropping defaults.
• db04-mba-l-ca progress · 2w ago
  db04-mba-l-ca 2026-09-30. Additional DEV-only dependency found: public.vehiculos_archive.chasis_id has no FK and 590 rows currently carry one of the 11 chassis IDs that must remap. public.vehiculos has a BEFORE UPDATE archive trigger, so remapping its 284 current rows will create more archive rows; after that, all archived chasis_id values must be remapped in the same transaction or historical rows point to the wrong chassis. The imported-history update trigger protects raw/date fields, not chasis_id. No write or rehearsal yet. The earlier progress timestamp containing 14:0x was a typo; this event corrects it without implying a completed apply.
• db04-mba-l-ca progress · 2w ago
  db04-mba-l-ca 2026-09-30 14:03 ART. Scope decision before apply: chasis natural key is (fabricantes.fabricante, chasis.chasis_modelo) with exact text, not current fabricante_id alone; full-key duplicates=0 in both bases and all 32 keys match, so remap 11 DEV IDs to PROD IDs without inserting/deleting chassis. DEV FK rows to remap: public.vehiculos 284, rutap.vehiculo_historial_normalizado 583, rutap.chasis_resolver 25; public.vehiculos_archive already has 590 matching rows and the vehiculos UPDATE archives 284 more old versions, so update archive IDs after current-row remap in one transaction. Use temporary negative IDs for the 11 chassis and temporarily DEFERRABLE+DEFERRED the 3 installed FKs, then assert final FK validity and restore NOT DEFERRABLE before COMMIT; no trigger disabling. Add PROD-only fabricantes 39/69 to DEV by explicit IDs; preserve DEV-only unreferenced ITALBUS id33 pending provenance because catalog DELETE soft-deletes and DEV trial data may be intentional. Carrocerias have no ID or semantic drift; opciones only retired-row presence/timestamp differences; subtipos only deleted timestamps: neither needs an ID remap. Do not drop nextval defaults yet: 4 live repository create paths omit id, so a code-owned explicit-ID contract must ship before defaults are removed on both bases. PROD catalog data is not to be changed.
• db04-mba-l-ca progress · 2w ago
  db04-mba-l-ca 2026-09-30 14:03 ART. DEV-only SQL draft outside checkout: /data-lezama/movilba/migration-evidence/MOVILBA-620-dev-catalog-id-remap.sql SHA256 659f64d68c2fa934303885dde24889f517a9ff91c273e58d427ed2abbb225029. ROLLBACK rehearsal on movilba_dev exited 0: 11 chassis keys remapped in transaction, 284 public.vehiculos FK rows, 583 normalized-history FK rows, 25 resolver FK rows, 874 archive rows (590 existing + 284 vehicle-update archives), plus two explicit-ID manufacturers 39/69. Postcondition passed, including same semantic FK counts, unchanged non-chassis vehicle fields, and deferred FKs restored to NOT DEFERRABLE. No trigger disabled. Post-rollback read confirmed original DEV chassis/archives and absent manufacturers remain. Evidence: /data-lezama/movilba/evidencia/MOVILBA-620/2026-09-30-dev-id-remap-rehearsal.md. No persistent DB write. Pending audit and separate explicit-ID writer contract before removing nextval defaults.
• db04-mba-l-ca progress · 2w ago
  db04-mba-l-ca 2026-09-30 14:xx ART. coder03 filed MOVILBA-625 as child, assigned coder04, for explicit-ID catalog writer contract. Sequence/default removal in either base is ordered after 625 reaches main. DEV chassis ID remap design, ROLLBACK rehearsal, and per-FK counts are recorded in preceding 620 events; audit review requested on the draft. No database apply yet.
• db04-mba-l-ca progress · 2w ago
  db04-mba-l-ca 2026-09-30 14:11 ART. Correction: the immediately preceding event's 14:xx is a timestamp placeholder typo. MOVILBA-625 child is the explicit-ID writer-contract dependency; defer removal of five catalog nextval defaults until 625 is shipped to main. DEV remap remains ROLLBACK-rehearsed only, awaiting audit verdict.
• audit-mba-l-cc audited · 2w ago
  audit pass: Static review of MOVILBA-620-dev-catalog-id-remap.sql (SHA256 659f64d6..., 165 lines): preflight guards, 2-phase negative-staging ID shuffle, FK deferral ordering, archive-trigger accounting (only vehiculos_archive applies), full-row-image postcondition, constraint restore.
  payload

  {"scope":"Static review of MOVILBA-620-dev-catalog-id-remap.sql (SHA256 659f64d6..., 165 lines): preflight guards, 2-phase negative-staging ID shuffle, FK deferral ordering, archive-trigger accounting (only vehiculos_archive applies), full-row-image postcondition, constraint restore.","outcome":"pass","evidence":"Static SQL review only, no DB creds from this lane - relies on db04's reported movilba_dev ROLLBACK rehearsal PASS. Confirmed via schema grep: no archive table for vehiculo_historial_normalizado/chasis_resolver. NOT verified: a 4th un-named FK vs public.chasis (would abort, not corrupt, if present).","reportRef":null}

• db04-mba-l-ca progress · 2w ago
  db04-mba-l-ca 2026-09-30 14:14 ART. New governing MOVILBA-625 rule from coder03: PROD alone assigns shared catalog IDs; DEV-created test catalog rows must use a reserved high band PROD will never allocate. Existing DEV-only fabricante id33 (ITALBUS S.A.) is live, unreferenced, and in the low band, so the current 620 draft preserves a pre-existing exception and cannot be the final catalog-alignment migration. Hold apply pending 625's chosen high band/explicit-ID contract. The 11-chassis ROLLBACK rehearsal remains valid as a scoped measurement only; once 625 defines the band, revise the draft to handle id33 and re-rehearse/re-audit. PROD data remains untouched.
• db04-mba-l-ca progress · 2w ago
  2026-10-02 sequencing dependency from MOVILBA-625 owner: 625 is still inProgress and its explicit-ID writer has not reached main; no DB sequence/default change made. After 625 is live, and before enabling the DEV app allocator, keep all five sequences (fabricantes, chasis, carrocerias, opciones, vehiculos_subtipos_transporte). On PROD cap each sequence at MAXVALUE 999999999; on DEV set/restart each sequence inside reserved 1000000000..2147483647. Recheck live sequence ownership, current values and occupied IDs immediately before DDL; prior 2026-09-30 census found int4, current candidates <=334, and zero IDs in the high band in either base. Sequence bounds alone do not prevent explicit PROD inserts into the DEV band: 625 writer/range guard must enforce that separately. No nextval default is removed before the 625 explicit-ID code is live and verified. Then revise the DEV remap to handle existing DEV-only fabricante id33, rehearse under ROLLBACK, audit, apply PROD-side bounds first and DEV-side band/remap after. PROD catalog rows remain authoritative and are not remapped.
• db04-mba-l-ca progress · 2w ago
  2026-10-02 11:26 ART live read-only recheck before sequence DDL: 625 code is on main, but its CATALOG_ID_SEQUENCES names public.fabricantes_id_seq, chasis_id_seq, carrocerias_id_seq, opciones_id_seq; all four are absent in BOTH movilba and movilba_dev (to_regclass NULL). Installed id defaults instead use fabricantes_new_id_seq, chasis_new_id_seq, carrocerias_new_id_seq, opciones_new_id_seq1; subtype uses vehiculos_subtipos_transporte_id_seq and matches code. PROD last_value: 76/144/80/332/18; DEV: 75/146/91/333/18 in table order fabricantes/chasis/carrocerias/opciones/subtipos. All five MAXVALUE 2147483647, both bases; no IDs >=1000000000; table max IDs PROD 69/144/71/331/18, DEV 61/146/71/331/18. The explicit-ID code would fail on nextval(regclass) for four tables. HOLD all sequence caps/restarts and DEV remap until 625 writer corrects names and is shipped; no DB write made.
• db04-mba-l-ca progress · 2w ago
  2026-10-02 11:26 ART MOVILBA-625 hybrid missing-registry workflow (from writer): an unregistered RUTAP catalog value must fail the first import loudly; do not allocate an independent DEV ID during import. Operator creates the catalog row via PROD's create path, exports the PROD catalog registry, imports the same literal ID into DEV, then retries the RUTAP import. Sequence-boundary work must preserve this order. Separate live blocker measured just before this note: four sequence names in the current writer do not exist in either base; no DB sequence DDL or DEV remap until 625 corrects and ships those names.
• db04-mba-l-ca progress · 2w ago
  2026-10-02 11:34 ART. After patch 141 fixed pg_get_serial_sequence lookup, drafted PROD cap and DEV high-band sequence SQL outside checkout at /data-lezama/movilba/migration-evidence/MOVILBA-620-{prod-catalog-sequence-cap,dev-catalog-sequence-band}.sql. Both rehearsed against their actual bases under default ROLLBACK: PROD five MAXVALUEs became 999999999; DEV five MINVALUE/START/RESTART became 1000000000 and MAXVALUE stayed int4 max; all postconditions passed, transaction rolled back. DEV rehearsal first exposed that START must be raised with MINVALUE, then that pg_sequences.last_value is NULL until first nextval after RESTART; final draft checks direct sequence state last_value=1000000000,is_called=false without consuming an ID. No persistent DB changes. Audit review next; DEV chassis/manufacturer ID remap remains separate and unapplied.
• audit-mba-l-cc audited · 2w ago
  audit pass: MOVILBA-620: both SQL files, checksums confirmed (d5b4a0a0/543c4d44). Ownership guard (pg_get_serial_sequence vs expected name), 5-table coverage, transactional ROLLBACK-default, pre/postcondition DO blocks reviewed.
  payload

  {"scope":"MOVILBA-620: both SQL files, checksums confirmed (d5b4a0a0/543c4d44). Ownership guard (pg_get_serial_sequence vs expected name), 5-table coverage, transactional ROLLBACK-default, pre/postcondition DO blocks reviewed.","outcome":"pass","evidence":"Sequence names match MOVILBA-625 fix exactly (fabricantes_new_id_seq, opciones_new_id_seq1 etc). Pre-flight excludes already-banded data both sides. DB EXECUTION NOT PERFORMED BY AUDIT - relying on db04 claimed rehearsal under ROLLBACK.","reportRef":null}

• db04-mba-l-ca progress · 2w ago
  2026-10-02 11:36 ART: audit PASS on both initial sequence drafts. Applied PROD cap under hub lock using /data-lezama/movilba/migration-evidence/MOVILBA-620-prod-catalog-sequence-cap.sql SHA256 d5b4a0a0..., psql apply=true COMMIT. Independent SELECT confirms movilba 5/5 owned sequences MAXVALUE=999999999; table max IDs unchanged (fabricantes 69, chasis 144, carrocerias 71, opciones 331, subtipos 18). DEV draft subsequently revised to atomically move DEV-only unreferenced fabricante ITALBUS S.A. id33 to id1000000000 and restart its sequence at 1000000001; other four restart at 1000000000. Revised DEV SHA256 bf1abed4169f9934a943a3cc5a3fe025c170ecb7573ca97b67e0a3d1da4b3ac5. ROLLBACK rehearsal PASS; separate read confirms DEV still id33 and min_value=1. Revised bytes sent to audit for re-verdict, not applied.
• audit-mba-l-cc audited · 2w ago
  audit pass: MOVILBA-620-dev-catalog-sequence-band.sql revised (sha256 bf1abed4): fabricante id=33 orphan-check, sequence restart values, remap postcondition
  payload

  {"scope":"MOVILBA-620-dev-catalog-sequence-band.sql revised (sha256 bf1abed4): fabricante id=33 orphan-check, sequence restart values, remap postcondition","outcome":"pass","evidence":"Preflight gates UPDATE on zero-reference check across all 4 FK surfaces; postcondition confirms remap both directions; DB EXECUTION NOT PERFORMED BY AUDIT - relying on db04 claimed ROLLBACK rehearsal against movilba_dev.","reportRef":null}

• db04-mba-l-ca progress · 2w ago
  2026-10-02 11:43 ART. Applied revised DEV sequence-band SQL SHA256 bf1abed4 under hub lock; psql COMMIT. Independent read: movilba_dev 5/5 sequences min_value=1000000000/max_value=2147483647; fabricante ITALBUS S.A. moved id33→1000000000; fabricantes_new_id_seq last_value=1000000001,is_called=false, chasis seq 1000000000,false. Rehearsed previously audited chassis remap SHA256 659f64d6 again under ROLLBACK: 11 chassis IDs, 287 vehiculos, 561 normalized-history, 23 resolver, 601 pre-existing archive +287 new archive =888; postconditions passed and ROLLBACK. DID NOT APPLY remap: of those 287 vehiculos, 11 are app_dev-created, and 19 of 276 ssot_import vehicles have audit_log UPDATE records (upper(action) predicate); 2 matching normalized-history rows have origen<>'import'. The script updates all of them, conflicting with Elazar's DEV preserve-created-or-edited rule. audit_log only captures app path, so 19 is a lower bound. Needs redesigned partial alignment preserving user rows or explicit rule interpretation; original all-row migration is not safe to apply as-is. PROD catalog remains authoritative and untouched.
• db04-mba-l-ca progress · 2w ago
  2026-10-02 read-only per-ID DEV remap safety split: candidate old→PROD new with (app_dev-created, ssot_import having app UPDATE, app-origin history): 46→144 (0,0,0), 136→140 (8,15,2), 137→133 (0,0,0), 138→136 (0,0,0), 139→142 (0,0,0), 140→134 (1,2,0), 142→139 (0,1,0), 143→135 (1,0,0), 144→137 (0,0,0), 145→138 (1,0,0), 146→143 (0,1,0). Because target IDs are occupied, only the collision-free chain 137→133, 144→137, 46→144 can move without also moving protected old IDs. This is a candidate partial DEV alignment, not an authorization or proof that no direct SQL edit occurred; no write performed.
• db04-mba-l-ca progress · 2w ago
  2026-10-02 partial-remap timebox: revised original DEV draft to only collision-free 3-key chain 46→144,137→133,144→137 and retained PROD-only fabricantes 39/69 inserts. Live protection preflight rejects any selected vehicle with origen_ingreso != ssot_import or audit_log UPDATE, or selected normalized-history row origen != import. ROLLBACK rehearsal PASS: 3 chassis keys; 4 vehicle FKs, 7 normalized-history FKs, 3 resolver FKs, 27 existing archive rows +4 trigger archives =31 after; 2 manufacturers inserted only within rollback. FK orphan count in the three FK-bearing tables was 0 before and after. Whole vehiculos_archive contains 14657 pre-existing orphan chassis IDs and has no FK, so a first postcheck demanding whole-archive zero aborted; scoped archive mapping/count checks remained, global archive assertion removed, and second rehearsal passed. No persistent remap write. Revised SQL at /data-lezama/movilba/migration-evidence/MOVILBA-620-dev-catalog-id-remap.sql; audit re-verdict requested.
• audit-mba-l-cc audited · 2w ago
  audit pass: MOVILBA-620-dev-catalog-id-remap.sql (sha256 94c78af2): 3-way collision-free chassis remap 46/137/144, fabricantes 39/69 insert
  payload

  {"scope":"MOVILBA-620-dev-catalog-id-remap.sql (sha256 94c78af2): 3-way collision-free chassis remap 46/137/144, fabricantes 39/69 insert","outcome":"pass","evidence":"Negative-staging avoids cyclic collision; protected-reference guard excludes user-edited DEV rows; FK-shape guard; pre/post count+JSON-image checks match db04 reported numbers (archive 27->31 = +4 trigger copies). DB EXECUTION NOT PERFORMED BY AUDIT - relying on db04 claimed ROLLBACK rehearsal.","reportRef":null}

• db04-mba-l-ca progress · 2w ago
  2026-10-02 11:53 ART. Applied audited partial DEV remap SHA256 94c78af2... (audit PASS) under hub lock, psql apply=true COMMIT. In-transaction counts: 3 chassis IDs 46→144,137→133,144→137; 4 public.vehiculos FKs, 7 rutap.vehiculo_historial_normalizado FKs, 3 rutap.chasis_resolver FKs, 27 pre-existing archive rows plus 4 new preimages =31; manufacturers 39/69 inserted. Independent read after commit: ids 133/137/144 now AGRALE MT 12.0 LE / MERCEDES BENZ BMO 1618 / MERCEDES BENZ FU-BMO 368 VERSION 1721 L62 CA; no id46; all 3 FK-bearing tables have 0 chassis orphans; moved counts 4/7/3/31; manufacturers 39/69 present. Protected-on-moved-IDs count=0 by app_dev or audit_log UPDATE. Unmoved IDs now have 12 app_dev and 19 ssot_import with app UPDATE (one new app row appeared after earlier 11 count); untouched. DB lock released. Remaining 8 divergent chassis IDs intentionally retain their DEV catalog rows per preservation rule.
• db04-mba-l-ca progress · 2w ago
  Applied SQL archived byte-for-byte under docs/migrations/2026-10-02-movilba-620-catalog-sequence-cap-PROD.sql (SHA d5b4a0a0), ...-catalog-sequence-band-DEV.sql (SHA bf1abed4), and ...-catalog-id-partial-remap-DEV.sql (SHA 94c78af2). All three hashes equal the audited/applied /data-lezama/movilba/migration-evidence originals; no SQL body edited after apply. The file comments still say NOT APPLIED because those are the executed bytes; README/status note must record that each has since COMMITTED. Unrelated dirty tracker protocol/tool files left untouched. SHIP requested to coder03 for only these three paths and README entry.
• coder03-mba-l-cc assigned · 1w ago
  db03-mba-l-cc / db
• db03-mba-l-cc completed · 1w ago
  Shipped: adae68dc/4e23b100 (v5.6.142). PROD 5 sequences capped 999999999; DEV banded to 1e9+ with fabricante ITALBUS id33->1e9; collision-free 3-chassis remap (46/137/144) applied, 0 FK orphans. Remaining 8 divergent chassis ids intentionally retained in DEV per preserve-created-or-edited rule - residual tracked, not a defect. Child MOVILBA-625 (explicit-id write contract) also shipped (62d5da88).