From the review on #125.
saveChildren reconciles practitioner_credentials and practitioner_services as four to five separate PostgREST requests — delete, delete, update, insert, insert. There is no transaction across them, so a failure part way through leaves whatever already ran committed.
The reachable case does not need a tampered payload: the services insert can raise 23514 from practitioner_services_cap after the credential deletes have already gone through. The practitioner then holds fewer credentials than they did before pressing Save, on the table the badge attests to.
What #125 already did
It stopped the message lying about it. saveChildren now tracks whether any statement committed and the action says "changed only in part before this failed … reload the page to see what Bluehex now holds" rather than "your credentials and services were not saved". That is honest and it is not a fix — the rows are still half-written.
What this issue is
A security definer function taking the practitioner id and the two arrays, doing the whole reconciliation inside one statement, and therefore one transaction. set_credential_verified() is the shape to follow — the RPC is the door, and the grants stay as they are.
Two things to get right, and both are the reason this is not a quick change:
verified must survive the round trip exactly as credentials_guard decides. The function must not become a second place where the clearing rule is written down: it inserts, updates and deletes, and the trigger decides what happens to the check. A delete plus insert of an unchanged credential would clear a check the guard would have kept, which is why the plan in profile-plan.ts skips unchanged rows and why that has to hold inside the function too.- It runs as the definer, so the predicate is the control. Every row it touches has to be joined back to
practitioners.user_id = auth.uid() — the same argument 20260822050002_profile_own_reads.sql makes for the two read functions.
Worth deciding at the same time whether the profile and contact writes join it. They are two more statements outside any transaction, and the two-request creation problem the spec accepts — an abandoned contact row — is the same shape.
From the review on #125.
saveChildrenreconcilespractitioner_credentialsandpractitioner_servicesas four to five separate PostgREST requests — delete, delete, update, insert, insert. There is no transaction across them, so a failure part way through leaves whatever already ran committed.The reachable case does not need a tampered payload: the services insert can raise
23514frompractitioner_services_capafter the credential deletes have already gone through. The practitioner then holds fewer credentials than they did before pressing Save, on the table the badge attests to.What #125 already did
It stopped the message lying about it.
saveChildrennow tracks whether any statement committed and the action says "changed only in part before this failed … reload the page to see what Bluehex now holds" rather than "your credentials and services were not saved". That is honest and it is not a fix — the rows are still half-written.What this issue is
A
security definerfunction taking the practitioner id and the two arrays, doing the whole reconciliation inside one statement, and therefore one transaction.set_credential_verified()is the shape to follow — the RPC is the door, and the grants stay as they are.Two things to get right, and both are the reason this is not a quick change:
verifiedmust survive the round trip exactly ascredentials_guarddecides. The function must not become a second place where the clearing rule is written down: it inserts, updates and deletes, and the trigger decides what happens to the check. Adeleteplusinsertof an unchanged credential would clear a check the guard would have kept, which is why the plan inprofile-plan.tsskips unchanged rows and why that has to hold inside the function too.practitioners.user_id = auth.uid()— the same argument20260822050002_profile_own_reads.sqlmakes for the two read functions.Worth deciding at the same time whether the profile and contact writes join it. They are two more statements outside any transaction, and the two-request creation problem the spec accepts — an abandoned contact row — is the same shape.