Skip to main content

DATA_MODEL — inventory-service

Sibling: DOMAIN_MODEL · APPLICATION_LOGIC · DEPLOYMENT_TOPOLOGY · SECURITY_MODEL

Strategic anchors: 06 Data Models · 02 §10 Persistence · standards/NAMING §db

inventory-service persists in the shared Cloud SQL Postgres 15 instance under the inventory schema, with tenant-scoped RLS, monthly partitioning for the hot room_type_inventory_daily table, and advisory locks for atomic allocation. The schema is migrated via node-pg-migrate; every migration is backwards-compatible per the platform expand→contract policy.

1. Identity strategy​

ULID-based, prefixed per standards/NAMING:

AggregateID prefix
RoomAllocationinv_
InventoryBlockblk_
AvailabilityCalendarcal_
RoomTypeInventoryrti_
OverbookingPolicyobp_
Group holdghd_
Import jobimj_

IDs generated by the application via @ghasi/domain-primitives factories.

2. Schema overview​

inventory.
├── room_type_inventory_daily   -- partitioned monthly; the hot table
├── room_allocations            -- the assignment ledger
├── inventory_blocks
├── availability_calendars      -- day-bucket projection
├── overbooking_policies
├── group_holds
├── import_jobs
├── outbox                      -- transactional outbox
├── inbox_processed             -- inbox dedupe
└── _meta_partitions            -- partition rotation bookkeeping

3. DDL​

3.1 room_type_inventory_daily (partitioned by month)​

CREATE TABLE inventory.room_type_inventory_daily (
  id              text NOT NULL,                  -- 'rti_…'
  tenant_id       text NOT NULL,
  property_id     text NOT NULL,
  room_type_id    text NOT NULL,
  stay_date       date NOT NULL,
  total           integer NOT NULL DEFAULT 0,
  held            integer NOT NULL DEFAULT 0,
  committed       integer NOT NULL DEFAULT 0,
  oos_blocked     integer NOT NULL DEFAULT 0,
  bed_total       integer,
  bed_held        integer,
  bed_committed   integer,
  stop_sell       boolean NOT NULL DEFAULT false,
  overbooking_cap integer NOT NULL DEFAULT 0,
  version         integer NOT NULL DEFAULT 0,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, property_id, room_type_id, stay_date),
  CONSTRAINT rti_counters_nonneg CHECK (held >= 0 AND committed >= 0 AND oos_blocked >= 0),
  CONSTRAINT rti_no_oversell CHECK (held + committed <= total + overbooking_cap),
  CONSTRAINT rti_bed_consistency CHECK (
    (bed_total IS NULL AND bed_held IS NULL AND bed_committed IS NULL)
    OR (bed_total IS NOT NULL AND bed_held >= 0 AND bed_committed >= 0
        AND bed_held + bed_committed <= bed_total)
  )
) PARTITION BY RANGE (stay_date);

ALTER TABLE inventory.room_type_inventory_daily ENABLE ROW LEVEL SECURITY;
CREATE POLICY room_type_inventory_daily_tenant_isolation ON inventory.room_type_inventory_daily
  USING (tenant_id = current_setting('app.tenant_id', true));

CREATE INDEX rti_search_idx
  ON inventory.room_type_inventory_daily (tenant_id, property_id, stay_date, room_type_id)
  INCLUDE (total, held, committed, oos_blocked, stop_sell);

CREATE INDEX rti_property_window_idx
  ON inventory.room_type_inventory_daily (tenant_id, property_id, stay_date);

Partition rotation​

-- Naming: rti_y2026m05
CREATE TABLE inventory.rti_y2026m05 PARTITION OF inventory.room_type_inventory_daily
  FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE inventory.rti_default PARTITION OF inventory.room_type_inventory_daily DEFAULT;

A nightly Cloud Run job rotate-partitions ensures partitions exist for the next 18 months and detaches partitions older than 24 months (then drops, with prior export to BigQuery long-term storage). Recorded in _meta_partitions.

3.2 room_allocations​

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TYPE inventory.allocation_status AS ENUM ('held','committed','released','reassigned');

CREATE TABLE inventory.room_allocations (
  id                      text PRIMARY KEY,        -- 'inv_…'
  tenant_id               text NOT NULL,
  property_id             text NOT NULL,
  room_type_id            text NOT NULL,
  room_id                 text,
  bed_id                  text,
  reservation_id          text NOT NULL,
  reservation_item_id     text NOT NULL,
  stay_check_in           date NOT NULL,
  stay_check_out          date NOT NULL,
  stay_range              daterange GENERATED ALWAYS AS (daterange(stay_check_in, stay_check_out, '[)')) STORED,
  status                  inventory.allocation_status NOT NULL,
  mode                    text NOT NULL CHECK (mode IN ('auto_pick','specific_room','group_member','walk_in')),
  held_until              timestamptz,
  assignment_source       text NOT NULL CHECK (assignment_source IN ('system','staff','guest_request')),
  group_hold_id           text,
  notes                   text,
  release_reason_code     text,
  version                 integer NOT NULL DEFAULT 0,
  created_at              timestamptz NOT NULL DEFAULT now(),
  committed_at            timestamptz,
  released_at             timestamptz,
  updated_at              timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT alloc_held_has_until CHECK (status <> 'held' OR held_until IS NOT NULL),
  CONSTRAINT alloc_committed_has_at CHECK (status <> 'committed' OR committed_at IS NOT NULL),
  CONSTRAINT alloc_released_has_reason CHECK (status <> 'released'
      OR (released_at IS NOT NULL AND release_reason_code IS NOT NULL)),
  CONSTRAINT alloc_check_out_after_in CHECK (stay_check_out > stay_check_in)
);

ALTER TABLE inventory.room_allocations ENABLE ROW LEVEL SECURITY;
CREATE POLICY room_allocations_tenant_isolation ON inventory.room_allocations
  USING (tenant_id = current_setting('app.tenant_id', true));

-- I3: a specific room cannot be double-allocated for overlapping nights
ALTER TABLE inventory.room_allocations
  ADD CONSTRAINT room_no_overlap_excl
  EXCLUDE USING gist (
    tenant_id WITH =,
    room_id   WITH =,
    stay_range WITH &&
  ) WHERE (room_id IS NOT NULL AND status IN ('held','committed'));

-- Bed-level analog for dorms
ALTER TABLE inventory.room_allocations
  ADD CONSTRAINT bed_no_overlap_excl
  EXCLUDE USING gist (
    tenant_id WITH =,
    bed_id    WITH =,
    stay_range WITH &&
  ) WHERE (bed_id IS NOT NULL AND status IN ('held','committed'));

CREATE INDEX alloc_by_reservation_idx ON inventory.room_allocations (tenant_id, reservation_id);
CREATE INDEX alloc_by_reservation_item_idx ON inventory.room_allocations (tenant_id, reservation_item_id);
CREATE INDEX alloc_held_until_idx ON inventory.room_allocations (held_until)
  WHERE status = 'held';
CREATE INDEX alloc_active_room_idx ON inventory.room_allocations (tenant_id, property_id, room_id, stay_check_in)
  WHERE status IN ('held','committed');

3.3 inventory_blocks​

CREATE TYPE inventory.block_status AS ENUM ('active','released');

CREATE TABLE inventory.inventory_blocks (
  id                  text PRIMARY KEY,                -- 'blk_…'
  tenant_id           text NOT NULL,
  property_id         text NOT NULL,
  room_id             text,
  room_type_id        text,
  stay_check_in       date NOT NULL,
  stay_check_out      date NOT NULL,
  stay_range          daterange GENERATED ALWAYS AS (daterange(stay_check_in, stay_check_out, '[)')) STORED,
  reason              text NOT NULL CHECK (reason IN ('ooo','oos','maintenance','event','other')),
  reason_text         text,
  source_kind         text NOT NULL CHECK (source_kind IN ('staff','system')),
  source_event_id     text,
  source_actor_id     text,
  status              inventory.block_status NOT NULL DEFAULT 'active',
  version             integer NOT NULL DEFAULT 0,
  created_at          timestamptz NOT NULL DEFAULT now(),
  released_at         timestamptz,
  updated_at          timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT block_target CHECK (room_id IS NOT NULL OR room_type_id IS NOT NULL),
  CONSTRAINT block_check_out_after_in CHECK (stay_check_out > stay_check_in)
);

ALTER TABLE inventory.inventory_blocks ENABLE ROW LEVEL SECURITY;
CREATE POLICY inventory_blocks_tenant_isolation ON inventory.inventory_blocks
  USING (tenant_id = current_setting('app.tenant_id', true));

CREATE INDEX block_active_room_idx ON inventory.inventory_blocks
  (tenant_id, property_id, room_id, stay_check_in) WHERE status = 'active';
CREATE INDEX block_active_type_idx ON inventory.inventory_blocks
  (tenant_id, property_id, room_type_id, stay_check_in) WHERE status = 'active';

3.4 availability_calendars (day-bucket projection)​

CREATE TABLE inventory.availability_calendars (
  id                  text NOT NULL,                   -- 'cal_…'
  tenant_id           text NOT NULL,
  property_id         text NOT NULL,
  stay_date           date NOT NULL,
  total_rooms         integer NOT NULL,
  available_rooms     integer NOT NULL,
  held_rooms          integer NOT NULL,
  committed_rooms     integer NOT NULL,
  blocked_rooms       integer NOT NULL,
  stop_sell           boolean NOT NULL DEFAULT false,
  version             integer NOT NULL DEFAULT 0,
  last_modified_at    timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, property_id, stay_date)
);

ALTER TABLE inventory.availability_calendars ENABLE ROW LEVEL SECURITY;
CREATE POLICY availability_calendars_tenant_isolation ON inventory.availability_calendars
  USING (tenant_id = current_setting('app.tenant_id', true));

Recomputed on every commit and reconciled nightly.

3.5 overbooking_policies​

CREATE TABLE inventory.overbooking_policies (
  id                  text PRIMARY KEY,           -- 'obp_…'
  tenant_id           text NOT NULL UNIQUE,
  enabled             boolean NOT NULL DEFAULT false,
  cap                 integer NOT NULL DEFAULT 0,
  room_type_ids       text[] NOT NULL DEFAULT '{}',
  alert_routes        text[] NOT NULL DEFAULT '{}',
  effective_from      timestamptz NOT NULL DEFAULT now(),
  effective_until     timestamptz,
  version             integer NOT NULL DEFAULT 0,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT obp_cap_nonneg CHECK (cap >= 0),
  CONSTRAINT obp_routes_when_enabled CHECK (NOT enabled OR cardinality(alert_routes) > 0)
);

ALTER TABLE inventory.overbooking_policies ENABLE ROW LEVEL SECURITY;
CREATE POLICY overbooking_policies_tenant_isolation ON inventory.overbooking_policies
  USING (tenant_id = current_setting('app.tenant_id', true));

3.6 group_holds​

CREATE TABLE inventory.group_holds (
  id                text PRIMARY KEY,             -- 'ghd_…'
  tenant_id         text NOT NULL,
  property_id       text NOT NULL,
  group_id          text NOT NULL,
  reservation_id    text NOT NULL,
  ttl_seconds       integer NOT NULL,
  created_at        timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE inventory.group_holds ENABLE ROW LEVEL SECURITY;
CREATE POLICY group_holds_tenant_isolation ON inventory.group_holds
  USING (tenant_id = current_setting('app.tenant_id', true));

3.7 Outbox + Inbox​

Standard platform shape (04 §6):

CREATE TABLE inventory.outbox (
  id              bigserial PRIMARY KEY,
  tenant_id       text NOT NULL,
  aggregate_kind  text NOT NULL,
  aggregate_id    text NOT NULL,
  subject         text NOT NULL,
  envelope        jsonb NOT NULL,
  ordering_key    text NOT NULL,
  created_at      timestamptz NOT NULL DEFAULT now(),
  published_at    timestamptz
);
CREATE INDEX outbox_pending_idx ON inventory.outbox (created_at) WHERE published_at IS NULL;

CREATE TABLE inventory.inbox_processed (
  event_id        text PRIMARY KEY,
  subject         text NOT NULL,
  tenant_id       text NOT NULL,
  processed_at    timestamptz NOT NULL DEFAULT now()
);

4. RLS roles​

RoleGrants
inventory_appSELECT/INSERT/UPDATE on all inventory.* tables; no BYPASSRLS
inventory_sweeperSELECT/UPDATE on room_allocations; BYPASSRLS only for the sweeper batch (per-row reset to app.tenant_id before write)
inventory_extenderSELECT/INSERT on room_type_inventory_daily, availability_calendars; BYPASSRLS for the calendar-extender job
inventory_adminfull + BYPASSRLS; used only for migrations

The application connection middleware sets SET LOCAL app.tenant_id = '<tenant>' per request. Failure to set raises MELMASTOON.TENANT.MISSING_CONTEXT.

5. Indexing rationale​

  • rti_search_idx covers the hot availability search query: range scan by (tenant, property, date) then sort by room_type_id.
  • alloc_held_until_idx is partial (only status='held'), keeping the sweeper's scan cost ~O(expired holds).
  • alloc_active_room_idx accelerates the reaccommodation lookup on OOO event arrival.
  • EXCLUDE USING gist on (tenant_id, room_id, stay_range) is the last-line database guarantee that no two active allocations share the same physical room and overlapping nights — even if the application had a bug.

6. Advisory lock function​

CREATE OR REPLACE FUNCTION inventory.acquire_alloc_lock(
    p_tenant_id    text,
    p_property_id  text,
    p_room_type_id text,
    p_stay_date    date
) RETURNS bigint LANGUAGE plpgsql AS $$
DECLARE
  v_key bigint;
BEGIN
  v_key := hashtextextended(
      p_tenant_id || ':' || p_property_id || ':' || p_room_type_id || ':' || p_stay_date::text,
      0
  );
  PERFORM pg_advisory_xact_lock(v_key);
  RETURN v_key;
END $$;

The application calls inventory.acquire_alloc_lock per night in canonical order. pg_advisory_xact_lock releases at transaction commit/rollback; no leaked locks possible. The hash includes tenant_id (Invariant I18) so cross-tenant lock collisions are impossible.

A diagnostic view exposes current lock holders for SRE:

CREATE VIEW inventory.v_advisory_lock_holders AS
  SELECT objid AS lock_key, pid, granted, application_name, query_start
  FROM pg_locks WHERE locktype = 'advisory';

7. Materialized views (search support)​

CREATE MATERIALIZED VIEW inventory.mv_property_30day_summary AS
SELECT tenant_id, property_id, stay_date,
       SUM(total) AS total,
       SUM(total - held - committed - oos_blocked) AS available
FROM inventory.room_type_inventory_daily
WHERE stay_date BETWEEN current_date AND current_date + INTERVAL '30 days'
GROUP BY tenant_id, property_id, stay_date;

CREATE INDEX mv_p30_pk ON inventory.mv_property_30day_summary (tenant_id, property_id, stay_date);

Refreshed every 60 s by a scheduled job; serves backoffice dashboards. Booking allocation never reads from a materialized view.

8. Backups, RPO, RTO​

  • Cloud SQL automated PITR enabled; RPO ≤ 5 minutes; RTO 30 minutes.
  • Logical export of inventory.* daily to Cloud Storage (gs://melmastoon-pitr/inventory/) for tenant offboarding.
  • Outbox table excluded from logical backups (cyclic replay risk); reconstructed from BigQuery archive if needed.

9. Cross-references​