POSTGRESQL REFERENCE · WORKING SOURCE

docs/verification.md

This file ships in the runnable source archive. Synthetic fixtures and demonstration policies are identified in the README.

# Verification report

**Final result: the implemented API workflow and all 17 PostgreSQL integration steps passed.
Application-process restart and a clean PostgreSQL shutdown/reopen preserved the same invoice and recorded
identities. Browser interaction remains unverified in this environment.**

Execution date: October 29, 2026. Runtime observed: Deno 2.9.6, TypeScript 6.0.3, native PostgreSQL 18.4, and
the pinned `pg` 8.23.1 client. These checks used actual database transactions and authenticated HTTP requests;
no persistence or capacity outcomes were mocked.

## Complete workflow gate

The first acceptance gate ran against one warehouse and passed before the second warehouse fixture was
enabled. It exercised a quote that consumes no capacity, a six-position hold, confirmation, five receipts,
closure of the unused sixth commitment, customer release requests, release of two pallets, release of the
remaining three, and the invoice preview.

The final synthetic invoice contains:

| Component            |  Quantity | Unit rate |         Total |
| -------------------- | --------: | --------: | ------------: |
| Occupied pallet-days |        13 |  USD 2.00 |     USD 26.00 |
| Completed receiving  | 5 pallets |  USD 4.00 |     USD 20.00 |
| Completed release    | 5 pallets |  USD 3.00 |     USD 15.00 |
| **Total**            |           |           | **USD 61.00** |

The original quotation was USD 78.00 for six pallets over three overlapping local dates. The actual charges
contain five receiving lines, five release lines, and thirteen daily storage lines. The unused position has no
charge. Each line retains the accepted tariff version and source movement references. See
`test-results/main-scenario.json` for the complete machine-readable result.

## Tests actually run

```sh
TEST_SCOPE=core npm test
npm test
npm run check
```

The final full suite reported **1 test, 17 steps, 0 failures**, with type checking enabled. `npm run check`
also passed independently. The detailed scenario matrix is in `tests/README.md`; the captured final output is
`test-results/full-integration.log`.

The suite covers final-position competition; hold expiry during an observed PostgreSQL row-lock wait;
concurrent idempotent confirmation/receiving/release behavior; payload conflicts; compatibility and explicitly
approved excess delivery; partial receipt and receiving closure; cancellation and expiry preserving custody;
overstay with threatened future bookings; blocks and relocation; early/late receipt and atomic amendment;
midnight and both daylight-saving transitions; frozen tariff versions and quote expiry; tenant isolation, role
enforcement, sessions and CSRF; linked adjustments; and the second warehouse's lower-capability zone.

The final review added specific regressions for JSON string `"false"` masquerading as variance approval, null
receipt entries, actual footprint/condition/goods overrides, extension after a precise 08:15 early receipt, a
newly backdated reservation start, duplicate overlapping block records, and elapsed quoted starts. All passed
after the corresponding fixes.

Tests use `warehouse_test`. The test reset helper refuses another database name. An explicit demonstration
reset is separate from these tests and requires the documented confirmation flag.

## Separate overstay demonstration

The `overstay` scenario CLI was actually executed through the public API. Cedar reserved and received one
pallet. Juniper then had eight positions committed immediately after Cedar's expected departure. Advancing the
clock one hour beyond that departure left Cedar's pallet physically present.

The response showed one occupied position, seven physically empty positions, an eight-position future
commitment, and a one-position deficit. It identified Juniper's threatened reservation and supplied a concrete
operator action. Receiving Juniper's eight-pallet load returned **HTTP 409 `CAPACITY_CONFLICT`**, with seven
available versus eight requested; no Juniper pallet was accepted. The complete output is
`test-results/overstay-scenario.json`.

The scenario command refuses a database with existing operational activity or a changed starting clock. It
never silently resets the demonstration. This refusal was also exercised and verified to leave business
records unchanged.

```sh
# The service must already be running against a deliberately fresh demo fixture.
npm run demo -- main --output=test-results/main-scenario.json
npm run demo -- overstay --output=test-results/overstay-scenario.json
```

Run each scenario against a separate fresh fixture, using the explicit reset command between them when
intended. The two commands are alternatives for a given initial fixture, not an instruction to append one
scenario onto the other.

## Actual restart verification

The separate probe started an external Deno application process, ran the complete main scenario, captured its
records, terminated that process, and started another Deno process against the same PostgreSQL database. The
two recorded operating-system process IDs were **4373** and **4383**. The original authenticated client
remained valid across the restart without logging in again.

The probe compared the full reservation and invoice objects and the exact identity sets before and after
restart:

| Record category        | Preserved count |
| ---------------------- | --------------: |
| Reservations           |               1 |
| Pallets                |               5 |
| Recorded movements     |              10 |
| Persisted charge lines |              23 |

The result remained thirteen pallet-days and USD 61.00. Cookies and CSRF tokens stayed in process memory and
were not written to the result artifacts.

PostgreSQL was then shut down cleanly, restarted from the same data directory, and queried through a newly
started application. The invoice and all reservation/pallet/movement/charge identities were again identical. A
second clean PostgreSQL shutdown completed successfully. Both shutdown logs contain completed checkpoints and
the normal `database system is shut down` message. Evidence is in `test-results/restart-verification.json`,
`test-results/postgres-verification.log`, and `test-results/postgres-reopen-verification.log`.

The application-process probe is reproducible with a running database and a deliberately fresh fixture. It
owns both application lifecycles, requires port 8000 to be free, and leaves the completed fixture persisted:

```sh
deno run -A --env-file=.env tests/restart_probe.ts
```

## Environment limitation and its resolution

An earlier development cluster stored inside this managed workspace failed during checkpoint with
`could not fsync file ... Permission denied`, then failed interrupted recovery. Inspection found affected
relation files owned by UID 0 with mode 0600 while PostgreSQL ran as UID 65534 and its parent data directories
remained owned by UID 65534. The ownership mismatch is observed evidence. Workspace synchronization or
rematerialization is a plausible explanation, but the responsible infrastructure process was not established.

The interrupted synthetic cluster was preserved rather than repaired or deleted. The final verification used
the launcher's `PG_DATA_DIR` override to place an execution-only cluster outside the synchronized workspace.
All seventeen tests and both scenarios were rerun there. The explicit application restart, database reopen,
identical-record comparison, and two clean database shutdowns passed. This confirms the prototype's recorded
behavior under the verified local PostgreSQL environment; it does not establish the reliability of the managed
workspace as a database volume.

The normal local launcher still defaults to the project's `.data/postgres` directory. The temporary-directory
override is an accommodation for this managed execution environment, not a recommendation to put production
data in temporary storage. Production needs operated PostgreSQL storage, backups, recovery testing, and
suitable filesystem ownership.

## Browser and production verification limits

The browser workflow was **not successfully demonstrated** in this environment. The managed preview attempt
encountered an execution-process failure, so HTTP/API verification must not be described as browser
verification. The implementation includes the four operational workspaces, but visual interaction, tablet
layout, and browser-specific retry behavior require a successful browser pass on the local application.

No production deployment, live warehouse integration, load/soak test, disaster-recovery exercise, or
accounting-system reconciliation was performed. The production work list is in `docs/production-work.md`. This
report distinguishes those future checks from the allocation, custody, billing, authorization, and persistence
behavior that was actually exercised.