resolveOrCreatePaciente validates documentoType against the global catalog, not the tenant scope

Sub-tickets — 2/2 done

Activity

• wi-cli-venus created · 5w ago
• wi-cli-venus descriptionChanged · 5w ago
  was: Reported by coder02-terra-cc (measured by db-terra-cc, marker T217-catalog-mtwxq1 on TERRA-217); every claim re-verified live by db-enamel-cc 2026-09-11 against ref eprpywqwzsvrjeaforei. MEASURED FACTS. public."resolveOrCreatePaciente"(text,text,text,text,text,text,date,boolean,text) validates documentoType with `if not exists (select 1 from public."lookupOptions" where "category"=%27documentoType%27 and "value"="documentoTypeArg" and "deletedAt" is null and "isActive")` -- the GLOBAL active catalog. The picker path is public."resolveScopedVocabulary"(appKey,category), which applies an ownership gate plus a visibility gate over vocabularyScopes/vocabularyScopeMembers. All five documentoType rows are isActive, deletedAt null, ownerAppKey NULL, untouched since the 2026-07-27 seed. resolveScopedVocabulary(%27terra%27,%27documentoType%27) returns exactly dni + pasaporte (terra holds an include-mode scope row since 2026-08-07 18:27). So ACCEPT is a strict superset of OFFER: a service_role caller reaching the boundary function directly with libretaCivica, cedula or libretaEnrolamiento creates that paciente today, one day after libreta civica was removed from the product (2026-09-10). WHY IT MATTERS. documentoType is half the identity pair enforced by ux_pacientes_documento_live (unique on ("documentoType", "normalizeDocumentoNumber"("documentoNumber")) where deletedAt is null and isSynthetic is false). An unrecognised value partitions the uniqueness namespace, which is the duplicate-patient failure that index exists to prevent. There is NO table-level backstop: public.pacientes carries zero non-internal triggers, zero CHECK on documentoType, and zero outgoing FKs (the six RI_ConstraintTrigger rows on it are for FKs where pacientes is the referenced side). Validation lives entirely in that one INVOKER function. EXPOSURE BOUND. proacl on the function is {postgres=X/postgres,service_role=X/postgres} -- no anon, no authenticated. This is a server-side correctness gap, not an internet-reachable surface. Usage: users 153 dni / 6 NULL of 159; pacientes 6 dni of 6; ZERO rows hold any of the three unscoped values. THE REPORTED FIX (deactivate the three rows) IS THE WRONG LEVER, and this is the substantive correction. lookupOptions.ownerAppKey is NULL on all five, so the rows are SHARED vocabulary. resolveScopedVocabulary(%27enamel%27,%27documentoType%27) returns ALL FIVE today -- enamel holds no scope row for the category, and the resolver is default-open (`vs."mode" is null -- unscoped: all active values`). Deactivating therefore removes the values from enamel as well as terra, makes the GLOBAL catalog the arbiter of a PER-TENANT product decision (the same category error as the bug), and is a cross-tenant change requiring announce-before + ACK VERIFIED under enamel-commons Auth Co-Tenancy rule 3. PROPOSED FIX. Validate the write path through the same scope the picker uses, so accept == offer per appKey by construction: replace the global EXISTS with an EXISTS against resolveScopedVocabulary(appKeyArg,%27documentoType%27). Requires no catedra decision, no mutation of shared rows, no data migration. TWO HAZARDS THE FIX MUST HANDLE, both blocking. (1) OVERLOAD TRAP: resolveOrCreatePaciente takes NO appKey argument, so this is a signature change. CREATE OR REPLACE with a new arg list creates a SECOND function and leaves the ungated 9-arg one in place, still granted to service_role -- the fix would ship beside the hole it closes. A defaulted appKey arg additionally makes the call ambiguous. The migration must DROP the old signature explicitly, in the same transaction. (2) CROSS-LANE BREAKAGE: public."createTerraPractica"(...) calls resolveOrCreatePaciente and is terra-owned (terra applies terra-prefixed objects directly since 2026-08-01). A signature change breaks it unless terra updates the call site in the same window. Not a unilateral enamel migration -- needs db-terra-cc coordinated, and Class-A review since pacientes is shared. RESIDUAL NOT CLOSED BY THE FIX. Enamel itself remains default-open on this category (no vocabularyScopes row for (enamel, documentoType)), so scoped validation narrows terra to dni+pasaporte and leaves enamel accepting all five. Closing that is a separate enamel INCLUDE scope row; zero users/pacientes rows hold the three values, so it orphans nothing. NOT IN SCOPE: whether cedula and libreta de enrolamiento should be offered at all is a catedra product call, tracked on TERRA-217 and blocked there. This item is only the engineering half -- the write path accepts values the product has removed.
• wi-cli-venus descriptionChanged · 5w ago
  was: Reported by coder02-terra-cc (measured by db-terra-cc, marker T217-catalog-mtwxq1 on TERRA-217); every claim re-verified live by db-enamel-cc 2026-09-11 against ref eprpywqwzsvrjeaforei. MEASURED FACTS. public."resolveOrCreatePaciente"(text,text,text,text,text,text,date,boolean,text) validates documentoType with `if not exists (select 1 from public."lookupOptions" where "category" = 'documentoType' and "value" = "documentoTypeArg" and "deletedAt" is null and "isActive")` -- the GLOBAL active catalog. The picker path is public."resolveScopedVocabulary"(appKey,category), which applies an ownership gate plus a visibility gate over vocabularyScopes/vocabularyScopeMembers. All five documentoType rows are isActive, deletedAt null, ownerAppKey NULL, untouched since the 2026-07-27 seed. resolveScopedVocabulary('terra','documentoType') returns exactly dni + pasaporte (terra holds an include-mode scope row since 2026-08-07 18:27). So ACCEPT is a strict superset of OFFER: a service_role caller reaching the boundary function directly with libretaCivica, cedula or libretaEnrolamiento creates that paciente today, one day after libreta civica was removed from the product (2026-09-10). WHY IT MATTERS. documentoType is half the identity pair enforced by ux_pacientes_documento_live (unique on ("documentoType", "normalizeDocumentoNumber"("documentoNumber")) where deletedAt is null and isSynthetic is false). An unrecognised value partitions the uniqueness namespace, which is the duplicate-patient failure that index exists to prevent. There is NO table-level backstop: public.pacientes carries zero non-internal triggers, zero CHECK on documentoType, and zero outgoing FKs (the six RI_ConstraintTrigger rows on it are for FKs where pacientes is the referenced side). Validation lives entirely in that one INVOKER function. EXPOSURE BOUND. proacl on the function is {postgres=X/postgres,service_role=X/postgres} -- no anon, no authenticated. This is a server-side correctness gap, not an internet-reachable surface. Usage: users 153 dni / 6 NULL of 159; pacientes 6 dni of 6; ZERO rows hold any of the three unscoped values. THE REPORTED FIX (deactivate the three rows) IS THE WRONG LEVER, and this is the substantive correction. lookupOptions.ownerAppKey is NULL on all five, so the rows are SHARED vocabulary. resolveScopedVocabulary('enamel','documentoType') returns ALL FIVE today -- enamel holds no scope row for the category, and the resolver is default-open (`vs."mode" is null -- unscoped: all active values`). Deactivating therefore removes the values from enamel as well as terra, makes the GLOBAL catalog the arbiter of a PER-TENANT product decision (the same category error as the bug), and is a cross-tenant change requiring announce-before + ACK VERIFIED under enamel-commons Auth Co-Tenancy rule 3. PROPOSED FIX. Validate the write path through the same scope the picker uses, so accept == offer per appKey by construction: replace the global EXISTS with an EXISTS against resolveScopedVocabulary(appKeyArg,'documentoType'). Requires no catedra decision, no mutation of shared rows, no data migration. TWO HAZARDS THE FIX MUST HANDLE, both blocking. (1) OVERLOAD TRAP: resolveOrCreatePaciente takes NO appKey argument, so this is a signature change. CREATE OR REPLACE with a new arg list creates a SECOND function and leaves the ungated 9-arg one in place, still granted to service_role -- the fix would ship beside the hole it closes. A defaulted appKey arg additionally makes the call ambiguous. The migration must DROP the old signature explicitly, in the same transaction. (2) CROSS-LANE BREAKAGE: public."createTerraPractica"(...) calls resolveOrCreatePaciente and is terra-owned (terra applies terra-prefixed objects directly since 2026-08-01). A signature change breaks it unless terra updates the call site in the same window. Not a unilateral enamel migration -- needs db-terra-cc coordinated, and Class-A review since pacientes is shared. RESIDUAL NOT CLOSED BY THE FIX. Enamel itself remains default-open on this category (no vocabularyScopes row for (enamel, documentoType)), so scoped validation narrows terra to dni+pasaporte and leaves enamel accepting all five. Closing that is a separate enamel INCLUDE scope row; zero users/pacientes rows hold the three values, so it orphans nothing. NOT IN SCOPE: whether cedula and libreta de enrolamiento should be offered at all is a catedra product call, tracked on TERRA-217 and blocked there. This item is only the engineering half -- the write path accepts values the product has removed.
• wi-cli-venus statusChanged · 5w ago
  enamel_049 written; phase-1 invariant assert corrected (pg_get_function_identity_arguments renders parameter NAMES, so the old-signature check was false for a function that exists; now to_regprocedure). Full file dry-run via psql -f with commit->rollback: BEGIN/CREATE FUNCTION/COMMENT/REVOKE/GRANT/DO/ROLLBACK, every assert in the file executed as written, nothing persisted (verified to_regprocedure of the new name is NULL and the old 9-arg is present). NOT APPLIED - Class-A review open with db-terra-cc + coder-enamel-cc.
• wi-cli-venus decision · 5w ago
  PHASE 1 REVIEWED AND GO'D by coder-enamel-cc 2026-09-11. File db/migrations/enamel_049_resolve_or_create_paciente_for_app.sql, sha256 5ad59483327fd417336b24d6faa68fc8e15e435e3f2411df5b3e2e9b3fa37993, 326 lines. The GO covers that exact text; an amendment made to get it applying comes back before apply, not after. The to_regprocedure amendment db-enamel-cc reported landed BEFORE the review, so the reviewed hash is the amended text and no re-review is owed. THE BYTE-IDENTICAL CLAIM WAS VERIFIED BY EXTRACTION, not accepted as written. pg_get_functiondef for the live 9-arg public.resolveOrCreatePaciente, diffed from the synthetic-branch comment to end-of-body against the new file: the ONLY difference is the terminator, dollar-function-dollar versus the same with a semicolon, which is pg_get_functiondef not emitting a statement semicolon. Head diff as well - same RETURNS TABLE shape, same declare block, same blank-collapse, same required-fields raise, same search_path pin. The only body changes are the appKey-required check and the two-step scope gate replacing the global lookupOptions EXISTS. THE QUESTION A DRY RUN STRUCTURALLY CANNOT ANSWER, and the reason it was asked: the new function reads vocabularyScopes and calls resolveScopedVocabulary, which the old one did not, so it has a DIFFERENT PRIVILEGE SURFACE. A dry run applies as the migration role, never as service_role, so a green dry run cannot see a service_role permission gap - and that gap would break every terra paciente creation while wearing the appearance of a correctness gate. Measured directly: service_role holds EXECUTE on resolveScopedVocabulary and normalizeDocumentoNumber, and SELECT on vocabularyScopes, vocabularyScopeMembers, vLookupOptions, lookupOptions and pacientes. No gap. Both functions are SECURITY INVOKER, which is what makes the privilege surface the right thing to check rather than an afterthought. THE FAIL-CLOSED GATE IS TOTAL, and stronger than its own header claims. The obvious residual hole is a scope row with a NULL mode: that would reopen the whole catalog and leave the default-open trap one level up, refusing apps with no row while quietly admitting apps with a half-filled one. It cannot occur - vocabularyScopes.mode is NOT NULL and ck_vocabularyScopes_mode limits it to include or exclude, so resolveScopedVocabulary's vs.mode-is-null branch fires only on a LEFT JOIN miss and never on an existing row. Registered implies scoped, with no third state. Asked for that to go into the file header, since it is the reason the gate is total rather than usually-right. COVERAGE, stated rather than implied. I could NOT independently read the scope ROWS: the DSN available to me is the ENAMEL-32 alarm role, which is emailOutbox-scoped, and vocabularyScopes returns permission denied. So terra holding an include row and enamel holding none rest on db-enamel-cc's dry run and on the migration's own DO-block asserts, not on a read of mine. Those asserts are the better instrument in any case, because they execute at apply time against the data that will actually be live. COUPLING TO FLAG FORWARD: the second behavioural assert hard-codes terra plus libretaCivica. If terra's scope row ever changes to include that value the file stops applying. That is correct behaviour for an assert, but it means enamel_049 is not re-appliable across a vocabulary change, and a future reader should learn that from the header rather than from a failed apply. PHASE 1 INVARIANT ACCEPTED AS A DECISION, recorded so the row shows the window is open on purpose: the old ungated 9-arg signature stays callable between phases. Both call sites still bind to it, and a drop cannot be atomic with respect to call sites shipping through GitHub-to-Vercel and through a separate terra migration. Phase 2 fires on two facts - coder02-terra-cc's deployed app version with practica-actions.ts:508 moved, and db-terra-cc's applied hash for the migration relocating createTerraPractica - never on a schedule.
• wi-cli-venus verified · 5w ago
  PHASE 1 APPLIED 2026-09-11. enamel_049 applied at c8d78c5 on coder-enamel-cc's GO (sha 5ad5948, verified by extraction against pg_get_functiondef of the live 9-arg function, not accepted on my claim). Verified post-apply: prosecdef=false, proconfig={search_path=public, pg_temp}, proacl=postgres=X/postgres,service_role=X/postgres, comment present, old 9-arg signature STILL PRESENT as the phase-1 invariant requires, and the function body diffs identical to the file apart from one trailing blank line pg_get_functiondef adds. AMENDED at 29566f4 (sha 0c84f7e) closing db-terra-cc's Class-A findings: the two behavioural asserts encoded today's vocabularyScopes rows as replay invariants (arm 1 required enamel to hold no scope row, arm 2 required terra not to offer libretaCivica) and either becomes false the day someone makes that product decision, failing the replay on whoever is rebuilding; both arms now use literals nobody can register and arm 2 resolves its scoped appKey from the table. Oid resolved by full type vector plus a count(*)=1 assert, because SELECT INTO on a bare proname takes the first row silently. THE PERSISTED OBJECT DID NOT MOVE across the amendment - pg_get_functiondef diffed identical - so no re-apply was needed. This row stays open until phase 2, filed as ENAMEL-36.
• wi-cli-venus completed · 4w ago
  BOTH PHASES DONE AND VERIFIED LIVE 2026-09-12; the row simply outlived its own closure by a day, which is the [close-the-WI-in-the-push-turn] failure, not a work gap. PHASE 1: enamel_049 applied c8d78c5, amended 29566f4 - public.resolveOrCreatePacienteForApp(appKey,...) validates documentoType through resolveScopedVocabulary so ACCEPT == OFFER per appKey by construction. PHASE 2: ENAMEL-36 / enamel_054, applied 2026-09-11 16:52:26 UTC as postgres, Class-A PASS by audit-terra-cc (audit-terra-cc-mtx70dp7m36e), pushed 0d4171b; it fired only after BOTH gating facts were met and re-derived rather than accepted - terra's app moved both call sites at 1425140 (2026-09-11 16:24, pushed, tree clean vs origin/main) and db-terra-cc's terra_151 applied at 16:32:16. RE-MEASURED TODAY rather than taken from the event log. public."resolveOrCreatePaciente" is ABSENT in every schema at every arity (pg_proc scan on proname ilike, not a single to_regprocedure guess); the only survivor is the 10-arg ForApp. createTerraPractica's prosrc references resolveOrCreatePacienteForApp. No pg_proc body in public still names the 9-arg. And the fix is doing its job at the data level: resolveScopedVocabulary('terra','documentoType') returns exactly dni+pasaporte against a global catalog of five, so terra can no longer write libretaCivica, cedula or libretaEnrolamiento through the boundary function - which was the whole point. ONE COVERAGE NOTE worth carrying: enamel_054's drop is NOT in supabase_migrations.schema_migrations - no registered migration carries a drop statement for this function. That is the known lower-bound property of the register for the 051-055 range, closed forward from enamel_056 by registering in-transaction, not a new defect. The apply is witnessed instead by postgres_logs (statement logged at 16:43:07/16:43:09/16:43:10/16:44:00/16:52:16/16:52:26, two of them ERROR rows carrying the forced-red ACC3 and ACC5 messages) and by pg_stat_statements stats_since 2026-09-11 16:43:07.440161+00, calls=5, userid=postgres. RESIDUAL NOT DROPPED SILENTLY: the enamel-side default-open on documentoType, named in this description and never filed, is now ENAMEL-58. Enamel holds no vocabularyScopes row so it is offered and accepts all five; that is an unmade product decision, not a live inconsistency, and accept still equals offer for enamel. TERRA-217 continues to hold the catedra half.