POSTGRESQL REFERENCE · WORKING SOURCE
docs/architecture.md
This file ships in the runnable source archive. Synthetic fixtures and demonstration policies are identified in the README.
Download the complete project ↓# svc_warehouse architecture and product contract
## Product contract
This is a local, synthetic demonstration of the question: **Can this warehouse accept these pallets for this interval, what commitments does that create, and can the eventual bill be explained from recorded operations?**
The first acceptance gate is one complete persisted workflow:
**Request → quote → hold → confirmed reservation → partial receipt → close receiving → partial release → final release → invoice preview.**
The customer owns the goods. A separate operator organization controls its warehouses and offers new commercial capacity to customer organizations listed in its permitted relationship table. Existing custody remains accessible to the operating organization even if that commercial relationship is later revoked: goods still need to be released and their charges explained. The application derives both roles and organization access from a server-authenticated session. Client-supplied object identifiers select a resource; they never confer permission.
The initial service sells compatible **pallet positions over an interval**. One accepted pallet consumes one rack position, with no stacking. Standard positions accept a **1219 × 1016 mm** pallet, maximum **1800 mm** overall height and **1000 kg** gross weight, under `ambient` conditions. Supported goods are sealed, dry, nonhazardous books, textiles and household merchandise (`packaged_dry`). Food, pharmaceuticals and liquids are excluded. Pueblo's light zone accepts the same footprint but limits height to **1500 mm** and weight to **600 kg**. The service does not exchange square feet, volume and pallet count as equivalent units.
All organizations, locations, inventory, movements and rates are demonstration data. Thursday is a scope constraint for a credible, explainable prototype, not an assumed production release time.
## Implementation boundary
The service is a modular monolith: Deno 2.9.6 and TypeScript, a native `Deno.serve` HTTP entry point, a framework-free browser application, node-postgres `pg` 8.23.1, and PostgreSQL as the authority for commitments and custody. The small UI does not require a component framework or bundler.
There is no pre-existing application in the task checkout to migrate. The development helper uses `embedded-postgres` 18.4.0-beta.17 to start a real **PostgreSQL 18.4** process when a system PostgreSQL/Docker runtime is absent. The Deno and PostgreSQL binaries reported versions 2.9.6 and 18.4 respectively. The wrapper is a development provisioning choice, not an in-memory simulation. Application verification and persistence checks are reported separately. The deliverable runs locally: hosted Sites' different server/storage model is not used to weaken the requested PostgreSQL transaction semantics, and no production deployment is implied.
| Module | Owns | Must not assume |
| --- | --- | --- |
| Access control | Authentication, sessions, memberships, customer/operator relationships and resource ownership checks | Possession of an ID proves access |
| Capacity | Compatibility, position eligibility, interval overlap, blocked capacity and allocation serialization | Expected departure proves physical departure |
| Booking | Quote snapshots, hold expiry, confirmation, cancellation, unreceived commitment and amendments | A quote consumes capacity or a booking cancellation removes inventory |
| Custody | Receiving, current position, relocation, release and immutable movement evidence | Commercial state is a reliable substitute for physical state |
| Billing | Accepted rates, integer-cent quantities and event-linked invoice previews | The current rate card may replace an accepted historical rate |
No cache, queue, outbox or separate service is required for this workflow. No background integration is implemented; adding delivery machinery without a delivery requirement would add failure modes without improving the acceptance gate.
## Four separate ledgers of meaning
1. **Capacity** describes usable compatible physical positions and blocks over time.
2. **Commitment** describes promises: unexpired holds and unfulfilled reservation positions.
3. **Custody** describes accepted pallets that have not been physically released.
4. **Billing** describes the economic consequence of actual custody and completed operations under the accepted tariff.
A pallet moving from commitment to custody changes which category accounts for its position. It does not create a second unit of demand. A pallet moving past an expected departure date remains in custody; it creates an exception to a forecast rather than an invented release.
## Data relationships
These diagrams describe the logical model. A reservation may be represented as a confirmed booking row; a quoted request may be retained in that row rather than requiring a separate request table. Physical SQL names are defined by the migrations.
```mermaid
erDiagram
direction TB
ORGANIZATION ||--o{ MEMBERSHIP : includes
USER ||--o{ MEMBERSHIP : has
ORGANIZATION ||--o{ WAREHOUSE : operates
ORGANIZATION ||--o{ OPERATOR_CUSTOMER : permits
WAREHOUSE ||--o{ ZONE : contains
ZONE ||--o{ POSITION : contains
POSITION ||--o{ CAPACITY_BLOCK : has
```
```mermaid
erDiagram
direction TB
ORGANIZATION ||--o{ QUOTE : requests
RATE_VERSION ||--o{ QUOTE : prices
QUOTE ||--o| HOLD : supports
HOLD ||--o| RESERVATION : confirms
RESERVATION ||--o{ ALLOCATION : promises
POSITION ||--o{ ALLOCATION : supplies
```
```mermaid
erDiagram
direction TB
RESERVATION ||--o{ PALLET : receives
PALLET ||--o{ MOVEMENT : records
PALLET ||--o{ CUSTODY : enters
POSITION ||--o{ CUSTODY : locates
RESERVATION ||--o{ RELEASE_REQUEST : authorizes
MOVEMENT ||--o{ CHARGE_LINE : supports
RATE_VERSION ||--o{ CHARGE_LINE : prices
```
The implementation additionally persists sessions, idempotency outcomes and audit records. Capability constraints belong to the zone/position profile. The operator/customer relationship must be checked independently of a customer's booking ownership.
## Interval and clock semantics
Bookings use half-open intervals **`[start,end)`** with **one-hour input granularity** and a maximum requested interval of **2160 elapsed hours (90 × 24 hours)**. New requested start/end values must be exact hours and include a timezone offset. The start is included, the end excluded. Actual receiving and release events retain their precise occurrence times. An explicitly approved early-arrival variance can start operational occupancy at that precise time rather than inventing an earlier rounded receipt. A later amendment can retain that unchanged precise start while setting an exact-hour end; it does not force an impossible rounding of recorded history. Two promises that meet exactly at a boundary are commercially adjacent; the second pallet can still only be physically received after the first has actually departed. The hourly display is a projection; allocation eligibility uses interval overlap and all intersecting claims. Billing granularity is a different concept: calendar days in the warehouse's IANA timezone.
Operational instants use timezone-aware timestamps. Each warehouse has an explicit IANA timezone. `America/Denver` is used for the required billing fixture. A UTC timestamp, a warehouse-local date and elapsed hours are three different values and must not be interchanged.
The controllable demo clock begins **October 26, 2026 at 08:00 Denver** (`2026-10-26T14:00:00Z`). Only the operator can advance it, and it moves forward. Clock changes lock every warehouse in sorted ID order before the clock row. Operational occurrence times come from this clock; `recorded_at` retains actual system recording time. A reset is a CLI action isolated from operational APIs and intentionally rebuilds only demonstration state. No caller may supply a timestamp to quietly defeat expiry or capacity checking.
The first fixture is **Denver**, `zone-denver-a`, with positions `DEN-A-01` through `DEN-A-08`. After the complete first workflow passes, the second fixture adds **Pueblo**: four standard positions and two positions in a light zone. Both use `America/Denver`. A quote lasts 30 demo minutes; a hold lasts 10 demo minutes, capped by its quote's expiry. A hold cannot be created or confirmed after the quoted start has elapsed: obtain a new quote with a current/future start. This is an additional eligibility condition, not permission to replace the quoted rate before expiry. It keeps new commitments within the projection's supported time horizon.
## Capacity equation and interval eligibility
For a compatible position `p` at instant `t`, let:
- `O(p,t)` be 1 when a linked custody record consumes that position under the selected physical or projected view.
- `H(p,t)` be the count of active unexpired hold claims.
- `U(p,t)` be the count of **unfulfilled** reservation claims. A received claim is absent from this category.
- `B(p,t)` be 1 when the position is blocked.
Its demand is:
`D(p,t) = O(p,t) + H(p,t) + U(p,t) + B(p,t)`
A position is allocatable exactly when `D(p,t) = 0`. For a request interval `I`, it must be compatible and allocatable for **every** instant of `I`. The count of acceptable positions is therefore:
`A(I) = count(p compatible with request AND no active demand interval for p overlaps I)`
This per-position equation avoids assuming that two free fragments on different positions together constitute one continuously available position. Checking only the interval's start, its end, or a single daily sample is insufficient. Direct half-open interval-overlap checks examine all intersecting commitments and blocks.
For a conflict-free snapshot, the aggregate equation reduces to:
`available = total compatible positions − blocked − occupied/projected occupied − held − unfulfilled committed`
That convenient subtraction is not a repair strategy when actual events conflict with promises. If `D(p,t) > 1`, preserve the records and expose the excess claims or blockage as an exception. Report physically free positions and threatened promises explicitly rather than clamping a negative planning balance to zero and concealing its cause. Overlapping occupied and blocked status can describe one physically real position; the count of distinct unavailable positions uses a union, while the conflict indicator retains both facts.
### Reservation-to-receipt proof
Suppose a zone has eight compatible positions and no blocks or other bookings. Six positions are reserved for the fixture interval.
| Step | Occupied pallets | Unreceived commitment | Held | Total consumed | Projected available |
| --- | ---: | ---: | ---: | ---: | ---: |
| Before the request | 0 | 0 | 0 | 0 | 8 |
| Quote created | 0 | 0 | 0 | 0 | 8 |
| Six-position hold | 0 | 0 | 6 | 6 | 2 |
| Hold confirmed | 0 | 6 | 0 | 6 | 2 |
| Five pallets received | 5 | 1 | 0 | 6 | 2 |
| Receiving closed, unused position released | 5 | 0 | 0 | 5 | 3 |
| Two pallets released | 3 | 0 | 0 | 3 | 5 |
| Three pallets released | 0 | 0 | 0 | 0 | 8 |
The eight-position example matches Denver's standard zone. The significant transition is **six committed → five occupied plus one unreceived → five occupied**. Adding all six original reservation positions to the five pallets would falsely claim eleven positions. Counting only five pallets before closing receiving would surrender the still-promised sixth position prematurely.
### Physical versus projected availability
The physical view asks which positions are empty **now**, based on unreleased custody and current blocks. It does not clear goods at their expected departure.
The projected view additionally includes holds and unfulfilled reservations, and may assume a currently present pallet departs at its accepted forecast. The application displays that assumption with the offer. Once the departure is overdue, the old forecast is no longer an adequate basis for a new promise. Keep the pallet present, expose any affected future booking, and require an operator action such as recording the real release, finding a compatible alternative, or contacting the affected customer to amend the promise. No automatic movement is inferred.
The timeline projects **current records** onto hourly cells. It is not a historical reconstruction: released custody is absent from current demand, and a currently occupied pallet is represented at its current position. Use the movement/audit trail and charge evidence to inspect what happened earlier. `minimumAvailable` counts compatible positions free continuously across the entire requested interval; it does not assume an unimplemented sequence of pallet relocations could combine free fragments on different positions. A new amendment cannot backdate its start into elapsed time earlier than its existing start.
## Concurrency protocol
All allocation-affecting operations use a PostgreSQL transaction and acquire the selected warehouse row with `SELECT ... FOR UPDATE`. This is intentionally coarse. One warehouse's allocation changes serialize; different warehouses can progress independently.
The transaction's order is:
1. Acquire the warehouse lock. Where additional locks are necessary, follow one stable order.
2. Read the authoritative current time **after** waiting for that lock.
3. Read the current commitment/occupancy/block state in subsequent SQL statements.
4. Validate authorization, compatibility, transition, interval capacity and idempotency.
5. Write the commercial or physical change and its audit/billing evidence atomically.
6. Commit and return the persisted outcome.
At READ COMMITTED isolation, subsequent statements see newly committed state. A single unlocked read followed by a later insert cannot enforce the invariant. A same-statement CTE that obtains the lock and reads availability can also retain a snapshot taken before a lock wait; the separate read is deliberate. PostgreSQL's locking and isolation contracts are recorded in [research.md](research.md).
Capacity protection covers hold creation and confirmation, cancellation or expiry cleanup, receipt and excess-delivery variance, closing receiving, amendments/extensions, release, relocation and capacity blocks. An amendment is evaluated as a replacement of the old claim within the same transaction. It does not cancel the old promise first and attempt to reacquire it afterward. Failed validation leaves the prior state intact.
Retryable database failures (`40001` serialization failure, `40P01` deadlock and `55P03` lock timeout) restart the **entire transaction**, with jitter and up to three attempts. The transaction uses a five-second lock timeout. Exhaustion returns a retryable HTTP 503; business conflicts return a documented conflict response. A retry never commits movement evidence separately from inventory. The current-position uniqueness index is a database backstop to the transaction protocol.
## State transitions
Commercial state and physical state evolve separately:
| Record | Valid progression | Rejected or specially handled transition |
| --- | --- | --- |
| Quote | Created with tariff/terms snapshot → usable for 30 demo minutes → new quote if expired | Expired quote silently repriced or accepted |
| Hold | Active → confirmed, or logically expired when the server clock reaches its expiry | Confirmation after expiry; consuming capacity twice on replay |
| Reservation | `confirmed` → `completed`, `cancelled` or `expired`; receiving is independently `open`, `closed_released` or `closed_retained` | Cancellation deleting goods; receipt without protected capacity validation |
| Pallet custody | Received at one position → optional relocation → physically released | Two current positions; receipt twice; release of absent goods |
| Release request | Customer request → operator executes against present authorized pallets | Executing a quantity or pallet list beyond current custody |
| Exception | Derived from current overstay/block conflicts → resolved by a recorded release, relocation, amendment or unblock | Hiding an overstay or silently moving inventory |
| Movement correction | Completed event → linked append-only note/financial adjustment | Rewriting completed timestamps, quantities or physical custody in place |
Closing receiving releases or explicitly retains the unreceived balance. Expiry and cancellation remove only unreceived reservation commitments while leaving present pallets in custody. A booking's original expected end never acts as an automatic inventory delete. Hold expiry is evaluated from its timestamp during protected reads/changes; it needs no background sweeper, and there is no hold-cancel endpoint in this release. Exceptions are derived, actionable views with specific suggested operator actions; assignment, case ownership and resolution case management are not implemented. Resolution evidence is the underlying operation and audit history.
Receipt is operator-only and normally uses the agreed declaration. Any supplied actual dimension, weight, condition or goods override is checked against the zone. Early arrivals and excess deliveries require literal `approveVariance: true` plus a reason; receipt after the booked end additionally requires an exact-hour future expected end, no more than 90 elapsed days after receipt. Every path revalidates capacity. Customer release requests identify exact pallets and do not end custody until the operator executes the release. The API contract documents every endpoint and wire shape in [api.md](api.md).
## Tariff and evidence
Each quote preserves the offered tariff version, currency, terms, estimated quantity and expiry. Confirmation accepts that snapshot. Rate-card versions are immutable inputs to the agreement; publishing a later version affects new quotes.
The initial tariff charges actual pallet-days and completed receiving/release operations. Unreceived reserved positions have no fee. A quote is an estimate, whereas the invoice preview reconstructs actual charges from custody and movement evidence.
Amounts are integer cents. Integer-cent unit rates multiplied by integer quantities require no fractional-cent rounding; the display formats two decimal places. No taxes, minimums or unmentioned surcharges are introduced. Fractional-unit tariffs, taxes and alternate rounding methods require a future tariff version and explicit policy.
For pallet custody interval `[received_at,released_at)`, each warehouse-local calendar day with positive overlap contributes one unit. Storage on a pallet released exactly at midnight ends in the previous day. A DST day still contributes one calendar date even when it is 23 or 25 hours long.
The required fixture is:
- Five pallets received October 26, 2026 at 09:00 in Denver, against six reserved positions.
- Receiving closes and the unused sixth position is released.
- Two pallets depart October 27 at 16:00: `2 × 2 = 4` pallet-days.
- Three depart October 28 at 10:00: `3 × 3 = 9` pallet-days.
- Storage: `13 × USD 2.00 = USD 26.00`.
- Receiving: `5 × USD 4.00 = USD 20.00`.
- Release: `5 × USD 3.00 = USD 15.00`.
- **Invoice preview total: USD 61.00.**
Each charge line identifies its source pallet/event or custody interval and the accepted rate version. Recalculation is deterministic and does not append duplicate charges. Replayed operational requests return the original persisted result for the same idempotency key and canonical payload; a different payload under that key is a conflict.
## Access-control boundary
Seeded users authenticate at the server. The session resolves a membership and organization, sends an HttpOnly session cookie, and returns a CSRF token required on mutations. Customers can create and inspect their own quotes, holds, reservations, pallets, release requests and invoices. New customer warehouse access requires the configured operator/customer relationship. Operators can perform custody operations only for warehouses they operate; they retain access to existing goods and records if the commercial relationship is revoked. There is no relationship-management API in this release. Customer identifiers are derived from the session when creating customer-owned records. Capacity responses may reveal aggregate demand and anonymous position state, never another customer's pallet or reservation identifiers.
Resource access checks include nested IDs. Owning reservation A does not permit a request to release a pallet from reservation B. Charge and movement drill-downs obey the same ownership boundary as their parent booking. Idempotency records do not become a side channel for another tenant's result.
The local demo uses synthetic seeded identities and must be hardened before external access. Authentication/session transport, credential management, rate limiting, stronger tenant isolation at the database boundary and organization administration are future production work. The executed cross-tenant tests determine what was verified in this release.
## Deferred production work
The following work is deliberately outside the first release:
- External WMS reconciliation, inventory cycle counts, discrepancy investigation and integration retry ownership.
- Operational resolution of damaged pallets, hazardous or undeclared contents, lost goods, complex retroactive corrections, disputes, carrier handoffs and multi-party approvals.
- Public authentication hardening: managed identity, credential recovery, MFA, secure deployment, session revocation and appropriate abuse controls.
- Contractual billing policies: reserved-capacity fees, minimums, taxes, currencies, grace periods, liability, payment collection and legally operative invoices.
- Cold chain, hazardous goods, floor storage, stacking, picking, mixed storage units and certified physical capability validation.
- Dock appointments, carrier integrations, warehouse marketplace onboarding and network optimization.
- Production migrations/rollouts, least-privilege database roles, backup/restore drills, high availability, reconciliation monitoring and operating service levels.
The immediate evidence threshold is a complete customer/operator workflow plus focused tests against the real database. The verification report distinguishes executed tests and browser demonstrations from remaining work; this document is the design explanation, not a substitute for those results.