Delivery lock-order correction and precursor release prerequisites

Delivery prepared a native lock-order correction under assignment ADR-0049/delivery/release-compatibility, coordinator 01a0e0a8-093e-7d80-b8ef-5997067ae039 (local). This is a source and isolated PostgreSQL proof package, not deployment approval. No deployment, production mutation, financial replay, admission activation, or ADR-0050 implementation change occurred in this assignment.

Confirmed defect and correction

The exact bounded predecessor 817cf5a22649173ea088a0f94fae78f5ddc5273e deadlocks ordinary writers even with zero closure operations. A row-first transaction holds a lesson row, a second writer holds the new statement-trigger advisory lock while waiting for that row, and the first writer then waits for the advisory lock. Actual PostgreSQL returned 40P01 at 1.013 seconds; both transactions rolled back. A universal installation drain does not correct this conflict after traffic resumes.

Both corrected candidates acquire native pg_advisory_xact_lock(hashtext(compiled_schema),480048) at Prisma transaction entry, before row, logical, or person locks. Compiled model schema annotations must match the lock schema. No new schema, model, function, or ADR-0050 epoch is needed for this acquisition. The app and shared script factories cover callback, array, and implicit Prisma transactions; the one operational raw PostgreSQL row-first repair now takes the same lock immediately after BEGIN. Existing serialization and guard triggers remain intact. Historical private rehearsal factories remain excluded from release use until individually adapted and target-checked.

Admission waits at most five seconds, preserving any stricter configured lock timeout and restoring that setting afterward. Cleanup executes SQL rollback before release, including Prisma's late admission result after maxWait. If rollback transport fails, the pool closes before release so the uncertain transaction cannot reach another borrower. That warm instance may need recycling.

Exact source and proof

Fresh authenticated Vercel observation confirms production alias delivery.sguildswim.com still maps to READY deployment dpl_Cf9U964S6fADECJgvEuiUzAonxZx, source e806e1b4641a6e24bfa4f06fc94d29b83ccba72e.

The minimal precursor is f8207229687f513d05eb5ca730e76fbcdb081e8f, directly on that live source, in C:/Users/jallr/.codex/worktrees/adr0049-lock-precursor/delivery. Its schema, migrations, packages, Vercel configuration and admission flags are unchanged. The final candidate is 51cf142c1e24863fafadc09f5b5541ce068246f0, directly on 817cf5a, in C:/Users/jallr/.codex/worktrees/adr0049-integration/delivery. The six correction files match exactly between the two commits. Both are local and unpushed; no automatic deployment was triggered. Final candidate retains the previously reviewed bounded migration bytes and excludes ADR-0050 code.

Ten actual PostgreSQL cases pass on a fresh isolated loopback database. They exercise actual expiry before installing the new schema, expiry and held/unfunded cancellation in both writer orders, callback rollback and reuse, short Prisma maxWait discard, native five-second timeout, and actual payment-handoff global-before-logical/person lock ordering. The 100ms maxWait case never executes its callback; after blocker release at 500ms an independent observer sees idle/no advisory lock at 547ms and the next autocommit borrower has no transaction ID. The native timeout occurs at 5024ms with clean reuse. Zero residual backends and zero closure operations remain.

This proof executes actual repository functions using a private generated client and corrected adapter, with a transport pause after the real row-lock query. It is not HTTP, authentication, positive bind, Coaching, Revenue, or provider proof. Network fetches are prohibited; local cancellation outbox publishing is real. The isolated audit:locks --source=postgres --dry-run command passes with Revenue explicitly skipped; its default window excludes null-timestamp synthetic reservations, so its zero counts are not financial drift validation.

Final candidate passes 911 Vitest tests plus 8 audit unit tests, with 15 opt-in tests skipped, nonincremental typecheck, and runtime smoke. The precursor independently installs dependencies and generates its unchanged-schema client; it passes 647 Vitest tests plus 8 audit unit tests, with 1 skipped, nonincremental typecheck, and runtime smoke. Independent review identified the shorter-timeout and late-rollback issues, both fixed before final PostgreSQL proof.

Evidence is retained under C:/Users/jallr/Documents/Sguild/repos/delivery/.tmp/adr0049-release-preparation-20261002/. lock-compatibility-release-package.md contains the full source, writer inventory, evidence and release sequence. Baseline result SHA256 is 8a5de78d4021b3b2aea9e80216944b8f79269919ffc2605745a743931745133c. Corrected result bounded-migrations/corrected-writers-877466dc-ef65-4cf7-8cbf-001b2586f7b2/results.json SHA256 is a335de0d8f3b96b9a78931cd545083973a5f2fd708f5771d83d4cb488b68fe92. The corrected adapter file SHA256 is 065307846b274233c8526e0de045242ca325997a7c00522ba63871d6b394afad. Failed and intermediate proof allocations remain preserved.

Release prerequisites retained by coordinator

Promote the unchanged-schema precursor before installing the new guards. Verify that old invocations, direct deployment URLs, dispatcher/cron routing and manual writers cannot continue the old order. Alias promotion alone is insufficient; use actual invocation and runtime-limit evidence. This finite old-writer turnover gate does not imply draining every cross-domain financial continuation. Stop before DDL if old writers cannot be excluded.

The current build override runs schema tooling before app build. The owner must verify precursor migration tooling has no pending DDL or journal discrepancy. Only after writer turnover may the bounded two-migration installation run against the verified target. Any failed or ambiguous result still requires catalog/journal inspection, without automatic retry, resolve or journal rewrite. Promote the final model set only after its columns exist and catalog checks pass. Keep new closure and handoff admission off throughout; activation requires separate accepted evidence. With those admissions still off, the verified precursor is the application rollback candidate after additive DDL, not the original row-first source.

The correction serializes entire transactions, including existing Coaching eligibility waits and unrelated transaction callbacks. Queue latency and timeout rates require observation; this memo makes no throughput claim. No financial or provider operation was moved or replayed to improve concurrency.

ADR-0050's worktree, pinned 3487 listener and joint fixture IDs remain untouched. Shared release sequencing and production decisions remain with the ADR-0049 coordinator; ADR-0050 implementation resumes only after explicit handback.