POSTGRESQL REFERENCE · WORKING SOURCE

README.md

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

# svc_warehouse

**Compatible positions. Protected promises. Recorded custody. Explainable charges.**

A local Deno/TypeScript and PostgreSQL prototype for selling physical warehouse storage by capacity and time. It implements one complete customer/operator workflow:

**Request → quote → temporary hold → confirmed reservation → receive → store → partial release → final release → invoice preview.**

All customers, warehouses, pallets and prices are synthetic. The application is a runnable engineering demonstration, with no employer data, production credentials, external WMS connection or payment processing.

## Quick start

Requirements: Node.js 22 or later (tested with 24.22.1), npm, and a browser. The pinned dependencies install Deno 2.9.6 and full native PostgreSQL 18.4 binaries. This is a real PostgreSQL server, not an in-memory substitute. macOS/Linux development is the intended setup; the supplied environment was Linux x64.

From the extracted project:

```bash
cd svc_warehouse
npm ci
cp .env.example .env
npm run db:start
```

Leave this first terminal open. It starts PostgreSQL on **127.0.0.1:55432**, creates `warehouse_demo` and the separate `warehouse_test` database, and keeps its files under `.data/postgres`. Ctrl-C stops the server; the data directory is retained. When run as a normal local user, no system account changes are needed. In the provided root container, the helper uses the existing unprivileged UID 65534; it does not create a new system user.

`PG_DATA_DIR` can select a different dedicated development data directory, through the shell or local `.env`. The final managed-environment verification used that option because the workspace's relation-file ownership changed during execution; ordinary desktop use keeps the default. The observed failure and the successful clean shutdown/reopen verification are documented in `docs/verification.md`. Temporary storage is not a production database-volume recommendation.

In a second terminal:

```bash
cd svc_warehouse
npm run migrate
npm run seed -- --second-warehouse
npm run dev
```

Open **http://127.0.0.1:8000**. `npm run dev` runs Deno; no frontend build is needed. The application binds to loopback by default. It serves its own HTML, CSS, JavaScript and JSON API.

If Deno is already installed, equivalent application commands are:

```bash
deno task migrate
deno task seed --second-warehouse
deno task start
```

Use the pinned version when reproducing the reported results. The npm commands select the project-local binary automatically.

### Sign in

| Persona | Email | Demonstration password |
| --- | --- | --- |
| Cedar shipper | `shipper@cedar.demo` | `warehouse-demo` |
| Juniper shipper | `shipper@juniper.demo` | `warehouse-demo` |
| Warehouse operator | `operator@warehouse.demo` | `warehouse-demo` |

These are actual server-authenticated sessions. Choosing a different account performs login; changing a browser variable does not change permissions. The application uses an HttpOnly, SameSite cookie, persisted session records, hashed passwords and a session CSRF token for mutations. These public synthetic credentials are deliberately for local use. External access needs the hardening listed below.

### Using an existing PostgreSQL server

Skip `npm run db:start`, create two dedicated databases, and set `DATABASE_URL` and `TEST_DATABASE_URL` in your local `.env`. The application uses ordinary PostgreSQL connections and SQL; it has no runtime dependency on the development launcher. Never point the test or reset commands at a database containing real records. The test harness refuses database names other than `warehouse_test`; the reset command additionally requires loopback, `DEMO_MODE=true` and explicit confirmation.

## Reproduce the presentation

The demo clock begins at **October 26, 2026, 08:00 America/Denver**. It is persisted and forward-only. All received/released timestamps come from that clock; `recorded_at` separately retains the actual system time when evidence was stored.

Use a clean synthetic fixture for a repeat performance. Stop the app before a reset, and leave PostgreSQL running:

```bash
npm run reset -- --confirm-demo-reset --second-warehouse
npm run dev
```

The reset is a deliberately destructive CLI operation confined to the named local demonstration database. There is no reset endpoint available to browser users.

The UI walkthrough is in [docs/demo.md](docs/demo.md). Its principal steps are:

1. Sign in as Cedar. In Booking, accept the defaults: six standard pallets, Denver Rack A, October 26 09:00 to October 29 00:00. Create the quote, create its ten-minute hold, and confirm before advancing the clock.
2. Sign in as operator. Use the presentation clock's receipt checkpoint. Receive five pallets. Inspect **five occupied plus one unreceived**, then close receiving with **release unused capacity**.
3. Advance to October 27 at 16:00. Select two pallets and record their release.
4. Advance to October 28 at 10:00. Release the remaining three.
5. Open Charges. Inspect **13 pallet-days and USD 61.00**, then expand the evidence and accepted rates.

The customer can also submit a release request for selected pallets. That request is not physical departure: the operator must record the release.

### API-driven scenario runners

With the app running and a fresh demo database:

```bash
npm run demo -- main
```

This performs the same sequence through authenticated HTTP endpoints and prints its resulting reservation and invoice summary. It refuses to overwrite an existing demonstration. It can write a reusable evidence artifact without session tokens:

```bash
npm run demo -- main --output=test-results/main-scenario.json
```

For the independent overstay demonstration, reset first, restart the app, then run:

```bash
npm run demo -- overstay --output=test-results/overstay-scenario.json
```

This leaves a real overdue pallet in custody, exposes the threatened future booking and tests a rejected extension. The scenario deliberately leaves the exception visible for inspection. It does not fabricate a release or move inventory automatically. To use a different app port, set `API_ORIGIN` to its loopback URL.

## What the application contains

| Workspace | Customer | Operator |
| --- | --- | --- |
| Capacity | Compatible zone limits, physical vacancy, full-interval bookable positions and departure assumptions | Same capacity view plus blocked positions and actionable exceptions |
| Booking | Storage request, itemized quote, hold expiry, confirmation, own bookings and amendments | Existing customer commitments and their operational state |
| Operations | Own pallets, departure history and release requests | Receiving, variances, position assignment, closing the balance, selected partial releases, relocation and exceptions |
| Charges | Own estimated and actual charges with evidence | Same evidence, audit view and linked note/financial adjustments |

The application includes loading, empty, validation, conflict and retry states. Successful operations refresh persisted state. Idempotent browser retries retain their original payload and key.

### Physical scope

| Fixture | Positions | Limits |
| --- | ---: | --- |
| Denver Rack A | 8 | 1219 × 1016 mm footprint; height ≤1800 mm; gross weight ≤1000 kg |
| Pueblo Rack A | 4 | Same standard profile |
| Pueblo Light rack | 2 | Same footprint; height ≤1500 mm; gross weight ≤600 kg |

One accepted pallet occupies one selective-rack position. There is no stacking or conversion between square footage, cubic volume and pallet count. Supported goods are sealed dry nonhazardous books, textiles and household merchandise, under ambient conditions. Food, pharmaceuticals, liquids, hazardous goods and temperature-controlled storage are excluded by this demo's capability policy.

Bookings use `[start,end)` and exact-hour inputs. Maximum requested duration is 2160 elapsed hours. Actual operational events retain their precise time. An approved early arrival may establish a precise operational start; a later amendment can preserve that start. A changed booking start and new expected end remain subject to the documented granularity and capacity checks.

## The four architectural concepts

**Capacity** is the supply of usable compatible positions. **Commitment** is a hold or unfulfilled reservation that consumes that supply over an interval. **Custody** is physical acceptance until recorded release. **Billing** is the result of those events under the agreed tariff.

These concepts have separate records and transitions. Their separation is why the service can explain both an empty promised position and an overdue pallet that still occupies space.

```mermaid
flowchart TB
    UI["Customer / operator browser"] --> API["Deno HTTP + session authorization"]
    API --> Booking["Booking + capacity"]
    API --> Custody["Custody + movements"]
    API --> Billing["Billing + evidence"]
    Booking --> DB["PostgreSQL authority"]
    Custody --> DB
    Billing --> DB
```

The code is a modular monolith. It has no queues, caches, outbox, AI decision maker or distributed transaction. There is no background integration to deliver, so no outbox is needed.

### Protected capacity

Every allocation-changing path takes the same PostgreSQL warehouse row lock in a transaction. It then reads the authoritative clock and current state in separate statements, validates the complete interval and commits changes plus evidence atomically. This includes holds, confirmation, receiving and variances, release, relocation, closing receiving, amendments, cancellation/expiry and capacity blocks. Transient database failures retry the entire transaction up to three times; retry exhaustion returns HTTP 503.

For compatible position `p` at time `t`:

```text
demand(p,t) = occupied/projected occupancy + active hold + unreceived commitment + blocked
```

`blocked` is a boolean even when several block reasons overlap. A position is bookable only when its demand is zero throughout the entire requested interval. The fixed-position policy intentionally does not assume future relocations could assemble availability from fragments on different positions.

For the eight-position Denver fixture:

| State | Held | Unreceived | Occupied | Bookable positions |
| --- | ---: | ---: | ---: | ---: |
| Quote only | 0 | 0 | 0 | 8 |
| Held | 6 | 0 | 0 | 2 |
| Confirmed | 0 | 6 | 0 | 2 |
| Five received | 0 | 1 | 5 | 2 |
| Receiving closed, unused position released | 0 | 0 | 5 | 3 |
| Two depart | 0 | 0 | 3 | 5 |
| Remaining three depart | 0 | 0 | 0 | 8 |

Receiving changes the accounting representation of a unit; it does not add inventory to the full original reservation count. Unique indexes independently prevent two active pallets in one position or two active custody records for a pallet.

### Physical reality versus forecasts

Physical availability counts actual unreleased custody and current blocks. Future offers assume an on-time pallet leaves at its expected departure. Once overdue, it remains occupied with no assumed departure, and the service identifies threatened future reservations. A newly blocked position similarly preserves existing promises and custody while exposing the conflict.

The timeline projects current records; it is not a reconstruction of historic rack utilization. Movement evidence and the invoice explain earlier events. A failed extension or amendment leaves original allocations in place. Canceling or expiring a booking removes only unreceived commitments; goods already accepted remain in custody and can still be released.

### Tenant isolation and tariffs

Organization and role come from the authenticated session. Every resource is checked for customer ownership or operator warehouse ownership, including nested pallet and release-request IDs. New customer warehouse access additionally requires the operator/customer relationship. Existing goods remain accessible to the operator if that commercial relationship is revoked, so it can discharge custody obligations. This release does not implement relationship administration.

Quotes and rate-card versions are immutable. Confirmation freezes the quoted version and currency; a later rate version affects new quotes. Quotes expire after 30 demo minutes, holds after 10 minutes capped by quote expiry. A quoted start that has elapsed also makes a new hold/confirmation ineligible; obtain a new quote rather than promise historical capacity.

Every handling charge is recorded with the operation. Final storage charges are recorded on release. Open-custody storage is a clearly labeled provisional accrual as of the clock. Preview reads do not append duplicate charges. Repeating confirmation, receiving or release with the same idempotency key and payload returns the saved outcome; changing the payload under the same key returns a conflict.

## Explain the invoice

All amounts use integer cents and integer quantities. This tariff has no fractional cents to round; presentation always shows two decimal places. No taxes, minimums or additional fees are applied. These rates are invented for demonstration, not market prices.

| Component | Calculation | Amount |
| --- | --- | ---: |
| Storage, two pallets released Oct 27 at 16:00 | 2 pallets × 2 local calendar days × USD 2.00 | USD 8.00 |
| Storage, three released Oct 28 at 10:00 | 3 pallets × 3 local calendar days × USD 2.00 | USD 18.00 |
| Receiving | 5 × USD 4.00 | USD 20.00 |
| Release | 5 × USD 3.00 | USD 15.00 |
| **Total** | **13 pallet-days plus completed handling** | **USD 61.00** |

The original six-pallet estimate is USD 78.00: six pallets over three local dates, plus receiving and release. The actual bill differs because only five arrive and two leave earlier. The unused sixth commitment costs zero under this tariff.

A pallet-day is a warehouse-local date with positive overlap with `[received_at,released_at)`. Release exactly at midnight excludes the new date. A two-minute interval straddling midnight covers two dates. A 25-hour autumn day and a 23-hour spring day each count once. Billing enumerates local date boundaries in PostgreSQL; it does not divide elapsed hours by 24.

The completed fixture has **23 persisted charge lines**: 13 storage-day lines, five receiving and five release lines. They reference ten physical movement records, the relevant pallet and the immutable tariff version. Corrections add linked evidence and optional financial adjustments; they do not rewrite completed movements. Complex retroactive physical-history correction is deferred.

## Verification

Run PostgreSQL first. The tests use the separate `warehouse_test` database and never reset `warehouse_demo`:

```bash
npm run check
npm test
```

To reproduce only the original first acceptance gate:

```bash
TEST_SCOPE=core npm test
```

For the separate application-process restart probe, stop the app, deliberately reset its demo fixture, and leave PostgreSQL running:

```bash
npm run reset -- --confirm-demo-reset --second-warehouse
npm run test:restart
```

The probe owns two real Deno process lifecycles, verifies the retained session and exact records, and leaves the completed USD 61 fixture in place.

The full suite exercises independent requests and real PostgreSQL transactions, including a confirmation observed waiting on an actual database row lock. It covers competing last-position holds, expiry races, idempotency and payload conflicts, incompatible/excess delivery, partial receipt, cancellation/expiry with custody, overstay threats, blocks and relocation, early/late arrivals and amendments, midnight/DST arithmetic, later tariff versions, tenant tampering, CSRF, immutable adjustments and the second warehouse. The final audit regressions cover backdated promises, duplicate blocks, precise early arrival extensions and malformed receipt declarations.

See [docs/verification.md](docs/verification.md) for exact executed outcomes, process-restart evidence and limitations. The managed browser preview could not launch Deno because its sandbox lacks `/proc/self/exe`; a browser click-through is **unverified** in that environment. The UI exists and its JavaScript/helper checks were run, but HTTP tests are not presented as browser interaction.

## Project map

| Path | Responsibility |
| --- | --- |
| `src/server.ts` | HTTP routes, session/CSRF boundary, bootstrap, demo clock, static assets |
| `src/access.ts` | Server authentication and tenant-scoped resource access |
| `src/db.ts` | Transactions, warehouse lock, retries, clock and idempotency |
| `src/capacity.ts` | Compatibility, interval claims, physical/projected availability, blocks and exceptions |
| `src/booking.ts` | Quote, hold, confirmation, balance, amendments, cancellation/expiry |
| `src/custody.ts` | Receipt, release requests, physical release, relocation and linked corrections |
| `src/billing.ts` | Integer-cent tariffs, local-date overlap and source-linked invoice |
| `src/contracts.ts` | Typed request contracts and API types |
| `migrations/001_initial.sql` | Relational schema, constraints and immutable-history triggers |
| `scripts/` | Database launcher, migration, seed, guarded reset and real-API scenario runner |
| `public/` | Framework-free operational application |
| `tests/` | Real PostgreSQL/HTTP acceptance and adversarial tests |
| `docs/` | Architecture, research register, API contract, demo, verification and deferred work |

## Further reading and boundaries

- [Architecture and relationship diagrams](docs/architecture.md)
- [API contracts and error behavior](docs/api.md)
- [Versioned source register and decisions](docs/research.md)
- [90-second demo and independent exception demonstration](docs/demo.md)
- [Verification evidence and limits](docs/verification.md)
- [Thursday post draft](docs/thursday-post.md)
- [Production work](docs/production-work.md)

Every pallet has a stable internal `DEMO-PAL-*` ID and may have an unvalidated external reference. A future SSCC adapter can map a validated logistics-unit identifier to that stable pallet without replacing it. This prototype does not claim GS1 label validation or EPCIS conformance; those standards inform the identifier/event boundaries, as documented in the source register.

Before external use: implement warehouse reconciliation and exception case management; harden authentication, session transport and database permissions; agree contractual billing, taxes and liability; add backup/restore and deployment procedures; and define the operational policies for damaged, missing, late or undeclared goods. Cold chain, hazardous goods, floor storage, picking, dock scheduling, carriers, external WMS, payments and marketplace onboarding remain extension points. No production deployment has been performed.