container
Inventory Database
PostgreSQL database that is the system of record for stock levels and reservations.
Databasepostgres@16Residency: eu-west-1Retention: 3yAccess Mode: readWrite
What is this?
The Inventory Database is the authoritative store for stock levels at Acme Inc. The Inventory ServiceInventory ServiceServicev1.0.0
The system of record for stock. Reserves and releases inventory, serves stock-level queries, and publishes events when s...Publishesinventory-reserved, inventory-unavailableSubscribesreserve-inventory, release-inventory +1Ownerfulfilment-platformMapRepoView docs reads from and writes to it as stock is reserved and released.
What does it store?
- Stock — one row per product: available quantity and reserved quantity.
- Reservations — one row per reservation: id, order, items and status.
Schema
-- Inventory Database — system of record for stock levels and reservations
CREATE TABLE stock ( product_id UUID PRIMARY KEY, available INTEGER NOT NULL DEFAULT 0 CHECK (available >= 0), reserved INTEGER NOT NULL DEFAULT 0 CHECK (reserved >= 0), updated_at TIMESTAMPTZ NOT NULL DEFAULT now());
CREATE TABLE reservations ( reservation_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), order_id UUID, cart_id UUID, status TEXT NOT NULL DEFAULT 'HELD' CHECK (status IN ('HELD', 'RELEASED', 'CONSUMED')), created_at TIMESTAMPTZ NOT NULL DEFAULT now());
CREATE TABLE reservation_items ( reservation_id UUID NOT NULL REFERENCES reservations (reservation_id) ON DELETE CASCADE, product_id UUID NOT NULL, quantity INTEGER NOT NULL CHECK (quantity > 0), PRIMARY KEY (reservation_id, product_id));
CREATE INDEX idx_reservations_order ON reservations (order_id);