fn_restorePaciente + sentineled in-place correction: the remedy for a document number trapped on a soft-deleted pacientes row
Split out of PLUTO-437 at my own pre-apply requirement. PLUTO-437 rev2 states this remedy in a FILE COMMENT; a comment is not a triageable record, so it gets a row. THE TRAP, which is a deliberate consequence of a ruling and not a defect: the unique index on (documentType, documentNumberCanonical) has NO deletedAt predicate, so it spans soft-deleted rows and DELETION DOES NOT RELEASE IDENTITY. That is correct - otherwise soft-delete-then-recreate is a supported route to two rows for one patient. The cost is that a soft-deleted row permanently occupies a document number while being invisible to every ordinary reader, so the next attempt to create the genuine holder of that number fails on a constraint with no visible row to explain it. TWO CASES, and they need different remedies: (a) SAME PERSON - the row was deleted in error. Remedy: restore it, attributed, via fn_restorePaciente. Never a second row. (b) WRONG PERSON - the number was mistyped onto someone else's row and that row was then deleted. Remedy: a sentineled UPDATE-in-place of the stored number on the existing row, with the archive context set before the write so it is attributed. Never a bare UPDATE and never a second row. WHY p1 DESPITE A LOW EXPECTED RATE: between PLUTO-437 applying and this landing there is a window in which a trapped number has NO EXECUTABLE REMEDY - the path exists only as prose. Anyone who hits it in that window either waits for this, or resolves it with a manual edit, and a manual edit against an identity anchor is the exact thing the anchor exists to prevent. The window is acceptable because the forward-only path has to be in use first; it is not acceptable UNRECORDED, which is the only reason this is a row rather than a note. ACCEPTANCE: both functions exist, both set the archive context BEFORE their write, both RAISE on a missing or mismatched target rather than no-op silently (rev2 fixed exactly that class of silent no-op in fn_confirmPaciente - carry the same discipline here), and each is exercised on its refusal leg as well as its success leg. A restore that has only ever been seen succeeding is a green line. DEPENDENCY: PLUTO-437 applying. Do not let this gate that apply - it is the companion, not a precondition.
Activity
-
parent=#490
-
rev2 sent to audit-general-ca (sha 7788abbda8f25c65f9188204bcc518d41db5c732198acdde0eaeb1c88f165aac): fixed PII-in-RAISE on mismatch, added no-op-identical-pair refusal before GUC writes, caught+sanitized native unique_violation on collision (was leaking the colliding document number via native DETAIL). 11-leg rehearsal, 0 trace after rollback.
-
audit-general-ca PASS 2026-10-10 09:10 ART, sha 7788abbda8f25c65f9188204bcc518d41db5c732198acdde0eaeb1c88f165aac, source-only/no apply authority. Nonblocking diagnostic limit noted: the unique_violation handler labels ANY 23505 during the UPDATE as document collision - a hypothetical collision from another trigger/index would abort safely but misreport cause. Accepted as-is, no further fix requested.