fix(db/routers): concurrency lock for replacing a router's model selection
Migration 20260818_01 is already MERGED, so it may have run on a real
deployment. A migration that has run is history: editing it in place changes
nothing on the databases that already applied it, while quietly disagreeing
with what they actually contain. The lock therefore ships FORWARD as a new
dated step, 20260819_01_harden_replace_user_router_models.sql, which re-runs
`create or replace function` with the hardened body. 20260818_01 is left
byte-for-byte as merged. schema.sql - the fresh-install path - carries the
hardened definition directly, so both real installation paths converge and
the schema-drift check stays green.
1. ADVISORY TRANSACTION LOCK IN replace_user_router_models
WHAT AN ADVISORY TRANSACTION LOCK IS: Postgres lets an application
take a lock on an arbitrary number it chooses (pg_advisory_xact_lock)
rather than on a table or row. Sessions that pick the same number
queue behind each other; everyone else is untouched; the lock
releases itself at commit/rollback, so it cannot leak.
WHY IT'S NEEDED: the function replaces a selection by delete+insert.
Two overlapping PATCHes for the same user+router could interleave -
both delete, both insert - and the second insert dies on the
(user_id, router, model_id) unique constraint as a 500. Locking on
hashtext(user_id:router) serializes exactly those two requests
(last writer wins) with zero effect on other users or routers.
2. THE LEGACY DATA COPY IS LEFT ALONE, DELIBERATELY
An earlier draft of this fix also sanitized 20260818_01's copy of the
old user_profiles.openrouter_models array (btrim, drop values the new
CHECK constraints reject, de-duplicate, cap at 50). That improvement
cannot be shipped forward and is no longer reachable:
- It only ever mattered as a way to stop 20260818_01 itself from
aborting on messy legacy data. 20260818_01 now runs first on every
deployment regardless, so a sanitized re-run in a later migration
could only execute AFTER the unsanitized one had already succeeded
- where it is a guaranteed no-op - or never, because a failed
migration stops the deploy before any later file is reached.
- The copy is guarded by `if exists (... column openrouter_models)`,
and no migration in this repository ever creates that column. It is
a shim for an unreleased intermediate state, so on every normal
deployment the guard is false and the block does nothing at all.
Residual risk, recorded rather than hidden: a database that really does
carry the legacy column with values the new constraints reject will
fail on 20260818_01, and that can now only be resolved by cleaning the
data - not by us. No such database is known to exist.
3. HONEST 400 FOR OVERSIZED PAYLOADS
A >50-model PATCH used to be truncated by the normalizer and then
reported as "invalid or duplicate model ID". The route now checks
the cap first and says "can include at most 50 models".
Validation: the hardened function body is unchanged from the version that
was executed against a live Postgres inside a rolled-back transaction when
this fix was first written - only its delivery vehicle moved, from an
in-place edit of 20260818_01 to a new migration. The function block in the
new migration and in schema.sql is diffed byte-identical. The cap message is
pinned by
userRouterModels.test.ts ("reports the 50-model cap"), which fails on the
ported code with the misleading message.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
| Repository | open-legal-products/mike |
|---|---|
| Author | Amal <mamalanand3@gmail.com> |
| Authored | |
| Committed | |
| Parents | 3d43970e |
| Stats | 4 files changed , +124 |
| Part of | Harden model-router selection and failure handling |
Capture this commit into my fork
Download a Markdown prompt that tells Claude how to port this
exact commit into your working tree. Run it via
claude -p < capture-commit-f465ea32.md
from inside the repo you want the change in.