# Pi HPT2 PostgreSQL readiness fix and fixture deployment evidence

Status: complete; PostgreSQL readiness defect fixed, dual-backend suites pass, and exact fail-closed fixture release is deployed
Type: evidence
Updated: 2026-08-02
Next Action: use `work/checklists/checklist-hpt2-bounded-stop-recovery-and-regression-measurement.md`; target execution waits on intake of an expendable A/B pair
Campaign Reason: Historical record from campaign 013-close-hpt-media-2-initial-release-qualification; retained independently after campaign closure and requires no separate owner-queue execution.
Campaign: standalone
Project Map: work/maps/map-smi-firmware-stabilization-and-qualification.md
Workpackage: work/wp/active/wp-build-standalone-pi-sd-qualification-operator-application.md

## Trigger and stop-before-target result

After the owner authorized moving forward from fixed-mailbox A/B identity, the
exact deployed PostgreSQL backend was used to rerun the 33 HPT2 mailbox and
durable-orchestration tests. Eleven orchestration cases stopped before any
target access with PostgreSQL error:

```text
FOR UPDATE cannot be applied to the nullable side of an outer join
```

No HPT2 operation, role, endpoint, controller command, SD write, power action,
or physical card action existed or ran. The production database contained zero
card samples, zero CID captures, and zero HPT2 command operations; its only
campaign was the retained fixture-only SMTP test.

## Root cause and correction

The HPT2 preparation queries used `select_for_update()` with `select_related()`
paths that included the campaign's nullable firmware-bundle relationship.
SQLite had treated the row-lock operation as a no-op, while PostgreSQL applied
the default lock scope to every joined table and correctly rejected the
nullable outer-join side.

Pi PR [#30](https://github.com/PC-Redemption/sd-qualification/pull/30) limits
the lock set to the concrete authorization, run, campaign sample, campaign,
operator, and physical sample rows while retaining the surrounding atomic
transaction and read access to the nullable immutable bundle. It also removes
a redundant explicit close of an already-exhausted streaming response; that
close invalidated PostgreSQL's test transaction for later view tests. Readiness
documentation now states accurately that the adapter code is present while
its one-shot tools service remains stopped and disabled.

- Source fix commit: `eb77b48c99640c1a89742f8be75dd475bc9d0ac3`.
- Merge/main commit: `e9edba3979c772a2dab20dbe2da51daaf5b5838b`.
- Pull request state: merged on 2026-08-03 UTC.
- Migrations: no model changes; migration plan empty; one-shot migrate reported
  no migrations to apply.

## Validation

- Focused HPT2 suite on PostgreSQL: `33/33` passed.
- Complete application suite on PostgreSQL: `129/129` passed in `45.662 s` on
  the exact built image.
- Complete application suite on SQLite: `129/129` passed in `44.846 s` on the
  exact built image.
- Django system checks: no issues.
- Storage preflight: pass; atomic rename true, no findings, approximately
  94.7 GB free.
- Git whitespace and clean-source gates: pass.

The full-suite commands explicitly cleared the production-only HPT fixture
execution root and clock-marker values so tests used their repository defaults.
An initial inherited-environment attempt was rejected by the fixture safety
guard and is not counted as a product failure or passing test.

## Backup, release, and deployment

- Pre-upgrade backup:
  `/backups/sd-qualifier-20260803T035813Z.dump`.
- Backup size: `141654` bytes.
- Backup SHA-256:
  `355d296bd4341227b47c5984f6c18fb83996a44c838ba4a2ffe29e2ab301e876`.
- Disposable restore check: pass.
- Release identity: `hpt2-postgres-lock-e9edba3`.
- Application image:
  `sha256:83bad7d83b9f25e0559386eb807832b60a8cbfeebdc2c644d07271fdc3c0249b`.
- Offline release directory:
  `release-media/hpt2-postgres-lock-e9edba3`.
- Release manifest SHA-256:
  `9898796409f85008e6b26b883b3ce14d6e06a34146e271adf39ba7b10ee7a47d`.
- Image archive SHA-256:
  `787565aabdaaf3c6ac238d9a470ec72198c167b846cdca7086f9d9a49b64dd57`.
- SBOM SHA-256:
  `329d1ad9ab912bc610fcbf8604dd6228e38a9319efb3a5d42fc5758016c9528a`.
- Every `SHA256SUMS` member verified.
- Retained rollback image:
  `sha256:e0757fc0bfce085b191b4f02f183ca417fdd07b06c6394ce9599c1eacdda93d8`.

PostgreSQL and Front Door were preserved. Only web, command-worker, and
notification-worker were recreated after the no-op migration. The remote
source checkout is clean at the exact merge commit.

## Final fail-closed state

All three application services run the exact new image. `/sd-qualifier/healthz`
reports status ok, database/storage/clock/workers true, fixture-only true,
contract valid true, and:

```text
live_admitted=false
state_changing_profiles_enabled=false
bounded_stop_supported=false
recovery_supported=false
selected_profile_live_ready=false
```

No HPT2 adapter, controller observer, or controller programmer container
remains. HPT2, read-only, and programming roles/endpoints are empty. Both
J-Link services and the firewall unit are active; the only J-Link firewall rule
is the default non-loopback drop for ports `19120` and `19121`.

The next gate is physical intake, not additional controller authorization:
there is no expendable sample in the Pi system of record. The installed A/B
SanDisk cards remain non-expendable and were not contacted by this slice.
