Archetype 6 — Inventory / Constrained Resource #

What this archetype is #

A finite resource must not be over-allocated. Correctness depends on guarded decrement or guarded reservation.

Examples: seats, rooms, stock units.

We will use seat inventory as the running example.

Layer 1: Entities and Postgres table design #

InventoryState
  -> source truth for available capacity

create table seat_inventory (
  seat_id bigint primary key,
  event_id bigint not null,
  status text not null default 'AVAILABLE',
  reserved_by bigint,
  version bigint not null default 1,
  updated_at timestamptz not null default now()
);

create index seat_inventory_event_status_idx
on seat_inventory (event_id, status);

Layer 2: Write path mechanics #

Reserve seat #

update seat_inventory
set status = 'RESERVED',
    reserved_by = $2,
    version = version + 1,
    updated_at = now()
where seat_id = $1
  and status = 'AVAILABLE';

Only one transaction can succeed.

Release seat #

update seat_inventory
set status = 'AVAILABLE',
    reserved_by = null,
    version = version + 1,
    updated_at = now()
where seat_id = $1
  and status = 'RESERVED';

Read availability #

select seat_id, status
from seat_inventory
where event_id = $1;

Layer 3: Fault tolerance #

• oversell
• double allocate
• lost release
• stale availability read

Controls:

• status-guarded update
• single-row authoritative truth
• transaction boundaries when combining multiple seats or resources

Layer 4: Scale #

Default hotspots:

• hot inventory key
• concentrated contention on scarce resources
• fragmented capacity

Common mitigations:

• partition by event
• separate fast read projection from source truth
• queue or hold layer in front for flash-sale conditions