DATA_MODEL — reservation-service

Sibling: DOMAIN_MODEL · APPLICATION_LOGIC · SYNC_CONTRACT · SECURITY_MODEL

Strategic anchors: 06 Data Models · 02 §6 Storage · standards/NAMING §6

reservation-service persists to the platform-shared Cloud SQL Postgres instance under the schema reservation. Multi-tenancy uses the platform-default shared schema + tenant_id Row-Level Security pattern (ADR-0002). All writes go through the transactional outbox (04 §6) so domain events and aggregate state commit atomically.

1. Identity strategy

Every aggregate identifier is a ULID with a service-specific prefix per NAMING §6.

Aggregate	Prefix	Type alias
Reservation	`rsv_`	`ReservationId`
Booking (transient)	`bkg_`	`BookingId`
Guest	`gst_`	`GuestId`
Quote	`qte_`	`QuoteId`
Hold	`hld_`	(composite)
Modification	`mod_`	(composite, ULID)
Special request	`spr_`	(composite, ULID)
Reservation item	`rit_`	(composite, ULID)
Additional guest	`agst_`	(composite, ULID)
Reservation code (human)	`GM-XXXXXX`	unique per tenant

Branded TypeScript types are declared in DOMAIN_MODEL §1. The repository layer accepts only branded types; raw strings are rejected at the from* factory.

2. Storage placement

Store	What lives here
Cloud SQL Postgres (`reservation` schema)	All authoritative reservation aggregates, items, guests, additional guests, special requests, modifications, holds, outbox, inbox dedupe
Memorystore (Redis)	Hot read caches (`rsv:arrivals:`, `rsv:in_house:`); short-TTL keys only
SQLite (Electron desktop)	Active + upcoming + recent reservations + items + guests + special requests; replicated via `sync-service`
Firestore (`tenants/{tid}/devices/{did}/sync`)	Per-device sync cursors and pending push queue, owned by sync-service
Cloud Storage	None (media owned by `media-service`; we hold only `mediaId` references for guest documents)
BigQuery (`events_regulated`, `events_operational`)	Event archive sink; PII tokenized at sink

We never embed PHI/PII directly in event payloads — emails and phones are tokenized and hashed; raw values stay in Postgres tables under field-level encryption (see SECURITY_MODEL §6).

3. Postgres schema (canonical DDL)

All tables live under the reservation schema; the connection middleware sets SET app.tenant_id = '…' per request, and RLS policies bind every row to that GUC.

3.1 `reservation.reservations`

CREATE TABLE reservation.reservations (
  id                       text PRIMARY KEY,                -- rsv_<ulid>
  tenant_id                text NOT NULL,                   -- tnt_<ulid>
  property_id              text NOT NULL,                   -- ppt_<ulid>
  reservation_code         text NOT NULL,                   -- GM-XXXXXX (unique per tenant)
  status                   text NOT NULL CHECK (status IN (
                              'quoted','held','expired_hold','confirmed',
                              'check_in_started','checked_in','checkout_started',
                              'checked_out','cancelled','no_show')),
  channel                  text NOT NULL CHECK (channel IN (
                              'direct','meta','walk_in','phone_by_staff','ota')),
  channel_partner_id       text,
  channel_campaign_ref     text,
  channel_captured_at      timestamptz NOT NULL,

  primary_guest_id         text NOT NULL,                   -- gst_<ulid>
  rate_plan_id             text NOT NULL,
  rate_plan_snapshot_name  text NOT NULL,

  stay_start               date NOT NULL,
  stay_end                 date NOT NULL,
  nights                   int  NOT NULL CHECK (nights >= 1),

  subtotal_micro           bigint NOT NULL CHECK (subtotal_micro >= 0),
  discount_total_micro     bigint NOT NULL DEFAULT 0,
  tax_total_micro          bigint NOT NULL DEFAULT 0,
  grand_total_micro        bigint NOT NULL CHECK (grand_total_micro >= 0),
  currency                 char(3) NOT NULL,
  in_tenant_currency_micro bigint,
  tenant_currency          char(3),

  fx_base                  char(3),
  fx_quote                 char(3),
  fx_rate                  numeric(20,8),
  fx_source                text CHECK (fx_source IN ('tenant_pinned','pricing_service','manual_override')),
  fx_captured_at           timestamptz,

  payment_method           text CHECK (payment_method IN ('paypal','card','cash_on_arrival','mfs','mixed')),
  payment_status           text NOT NULL DEFAULT 'none'
                                CHECK (payment_status IN ('none','pending_intent','pending_capture',
                                                          'pending_cash','captured','partially_refunded','refunded','failed')),
  payment_total_captured_micro  bigint NOT NULL DEFAULT 0,
  payment_total_refunded_micro  bigint NOT NULL DEFAULT 0,
  payment_intent_ids       text[] NOT NULL DEFAULT '{}',

  folio_id                 text,                            -- fol_<ulid>, reference to billing-service
  key_credential_ids       text[] NOT NULL DEFAULT '{}',
  requires_manual_key      boolean NOT NULL DEFAULT false,

  pending_saga_step        text CHECK (pending_saga_step IN
                              ('await_inventory','await_payment','await_lock','await_refund','await_release')),
  pending_saga_started_at  timestamptz,
  pending_saga_deadline    timestamptz,
  pending_saga_attempts    int NOT NULL DEFAULT 0,

  created_at               timestamptz NOT NULL DEFAULT now(),
  created_by_type          text NOT NULL,
  created_by_id            text NOT NULL,
  confirmed_at             timestamptz,
  checked_in_at            timestamptz,
  checked_out_at           timestamptz,
  cancelled_at             timestamptz,
  cancellation_reason      text,
  cancellation_reason_code text,

  version                  int NOT NULL DEFAULT 0,           -- OCC
  vector_clock             jsonb NOT NULL DEFAULT '{}'::jsonb,
  updated_at               timestamptz NOT NULL DEFAULT now(),
  updated_by_type          text NOT NULL,
  updated_by_id            text NOT NULL,

  CONSTRAINT reservations_stay_window_chk CHECK (stay_end > stay_start),
  CONSTRAINT reservations_code_unique     UNIQUE (tenant_id, reservation_code)
);

ALTER TABLE reservation.reservations ENABLE ROW LEVEL SECURITY;

CREATE POLICY reservations_tenant_isolation ON reservation.reservations
  USING (tenant_id = current_setting('app.tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true));

-- Hot indexes
CREATE INDEX idx_reservations_tenant_property_stay
  ON reservation.reservations (tenant_id, property_id, stay_start, stay_end);

CREATE INDEX idx_reservations_tenant_status_property
  ON reservation.reservations (tenant_id, status, property_id);

CREATE INDEX idx_reservations_tenant_guest
  ON reservation.reservations (tenant_id, primary_guest_id);

CREATE INDEX idx_reservations_pending_saga
  ON reservation.reservations (tenant_id, pending_saga_step, pending_saga_deadline)
  WHERE pending_saga_step IS NOT NULL;

CREATE INDEX idx_reservations_arrivals_today
  ON reservation.reservations (tenant_id, property_id, stay_start)
  WHERE status IN ('confirmed','check_in_started');

3.2 `reservation.reservation_items`

CREATE TABLE reservation.reservation_items (
  id                  text PRIMARY KEY,                     -- rit_<ulid>
  tenant_id           text NOT NULL,
  reservation_id      text NOT NULL REFERENCES reservation.reservations(id) ON DELETE CASCADE,
  room_type_id        text NOT NULL,
  room_id             text,                                 -- assigned by inventory-service
  stay_start          date NOT NULL,
  stay_end            date NOT NULL,
  nights              int  NOT NULL CHECK (nights >= 1),
  occupants_adults    int  NOT NULL CHECK (occupants_adults >= 1),
  occupants_children  int  NOT NULL DEFAULT 0,
  occupants_infants   int  NOT NULL DEFAULT 0,
  nightly_breakdown   jsonb NOT NULL,                       -- [{date, rateMicro, rateCode, taxMicro}]
  item_subtotal_micro bigint NOT NULL CHECK (item_subtotal_micro >= 0),
  currency            char(3) NOT NULL,
  allocation_id       text,                                 -- inv_<ulid> from inventory-service
  notes               text,
  CONSTRAINT items_stay_window_chk CHECK (stay_end > stay_start)
);

ALTER TABLE reservation.reservation_items ENABLE ROW LEVEL SECURITY;
CREATE POLICY reservation_items_tenant_isolation ON reservation.reservation_items
  USING (tenant_id = current_setting('app.tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true));

CREATE INDEX idx_reservation_items_tenant_reservation
  ON reservation.reservation_items (tenant_id, reservation_id);

CREATE INDEX idx_reservation_items_tenant_room_window
  ON reservation.reservation_items (tenant_id, room_id, stay_start, stay_end)
  WHERE room_id IS NOT NULL;

-- Defense-in-depth invariant I3 (overbooking): exclusion constraint over date ranges per room
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE reservation.reservation_items
  ADD CONSTRAINT reservation_items_no_overlap
  EXCLUDE USING gist (
    tenant_id   WITH =,
    room_id     WITH =,
    daterange(stay_start, stay_end, '[)') WITH &&
  )
  WHERE (room_id IS NOT NULL);

3.3 `reservation.guests` and `reservation.additional_guests`

CREATE TABLE reservation.guests (
  id                 text PRIMARY KEY,                      -- gst_<ulid>
  tenant_id          text NOT NULL,
  full_name_given    text NOT NULL,
  full_name_family   text NOT NULL,
  script_hint        text NOT NULL DEFAULT 'latin',
  latin_translit_given  text,
  latin_translit_family text,
  email_enc          bytea,                                 -- AES-256-GCM (KMS DEK)
  email_hash         bytea,                                 -- HMAC-SHA256 for indexed search
  phone_e164_enc     bytea,
  phone_e164_hash    bytea,
  locale             text NOT NULL DEFAULT 'en-US',
  preferences        jsonb NOT NULL DEFAULT '[]'::jsonb,    -- SpecialRequestTag[]
  document_type      text,                                  -- passport / national_id / driving_license
  document_last4     text,
  document_issuer    text,
  loyalty_id         text,
  created_at         timestamptz NOT NULL DEFAULT now(),
  updated_at         timestamptz NOT NULL DEFAULT now(),
  version            int NOT NULL DEFAULT 0,
  erased_at          timestamptz                            -- GDPR right-to-erasure
);

ALTER TABLE reservation.guests ENABLE ROW LEVEL SECURITY;
CREATE POLICY guests_tenant_isolation ON reservation.guests
  USING (tenant_id = current_setting('app.tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true));

CREATE INDEX idx_guests_tenant_email_hash ON reservation.guests (tenant_id, email_hash) WHERE email_hash IS NOT NULL;
CREATE INDEX idx_guests_tenant_phone_hash ON reservation.guests (tenant_id, phone_e164_hash) WHERE phone_e164_hash IS NOT NULL;
CREATE INDEX idx_guests_tenant_loyalty    ON reservation.guests (tenant_id, loyalty_id) WHERE loyalty_id IS NOT NULL;

CREATE TABLE reservation.additional_guests (
  id                 text PRIMARY KEY,                      -- agst_<ulid>
  tenant_id          text NOT NULL,
  reservation_id     text NOT NULL REFERENCES reservation.reservations(id) ON DELETE CASCADE,
  full_name_given    text NOT NULL,
  full_name_family   text NOT NULL,
  age_band           text NOT NULL CHECK (age_band IN ('adult','child','infant')),
  document_type      text,
  document_last4     text,
  is_child_without_id boolean NOT NULL DEFAULT false
);

ALTER TABLE reservation.additional_guests ENABLE ROW LEVEL SECURITY;
CREATE POLICY additional_guests_tenant_isolation ON reservation.additional_guests
  USING (tenant_id = current_setting('app.tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true));

CREATE INDEX idx_additional_guests_tenant_reservation
  ON reservation.additional_guests (tenant_id, reservation_id);

3.4 `reservation.special_requests`

CREATE TABLE reservation.special_requests (
  id              text PRIMARY KEY,                          -- spr_<ulid>
  tenant_id       text NOT NULL,
  reservation_id  text NOT NULL REFERENCES reservation.reservations(id) ON DELETE CASCADE,
  tags            text[] NOT NULL DEFAULT '{}',
  free_text       text,
  locale          text NOT NULL DEFAULT 'en-US',
  source          text NOT NULL CHECK (source IN ('guest','staff','ai_parser')),
  ai_provenance   jsonb,
  created_at      timestamptz NOT NULL DEFAULT now(),
  fulfilled       boolean NOT NULL DEFAULT false,
  fulfilled_at    timestamptz,
  fulfilled_by_type text,
  fulfilled_by_id   text
);

ALTER TABLE reservation.special_requests ENABLE ROW LEVEL SECURITY;
CREATE POLICY special_requests_tenant_isolation ON reservation.special_requests
  USING (tenant_id = current_setting('app.tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true));

CREATE INDEX idx_special_requests_tenant_reservation
  ON reservation.special_requests (tenant_id, reservation_id);
CREATE INDEX idx_special_requests_tenant_open
  ON reservation.special_requests (tenant_id, fulfilled) WHERE fulfilled = false;

3.5 `reservation.reservation_modifications` (append-only audit)

CREATE TABLE reservation.reservation_modifications (
  id                  text PRIMARY KEY,                     -- mod_<ulid>
  tenant_id           text NOT NULL,
  reservation_id      text NOT NULL REFERENCES reservation.reservations(id) ON DELETE CASCADE,
  type                text NOT NULL,                        -- ModificationType
  occurred_at         timestamptz NOT NULL DEFAULT now(),
  actor_type          text NOT NULL,
  actor_id            text NOT NULL,
  before_snapshot     jsonb NOT NULL,
  after_snapshot      jsonb NOT NULL,
  causation_event_id  text,
  reason              text,
  policy_applied_ref  text,
  price_delta_micro   bigint,
  currency            char(3)
);

ALTER TABLE reservation.reservation_modifications ENABLE ROW LEVEL SECURITY;
CREATE POLICY reservation_modifications_tenant_isolation ON reservation.reservation_modifications
  USING (tenant_id = current_setting('app.tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true));

CREATE INDEX idx_modifications_tenant_reservation_time
  ON reservation.reservation_modifications (tenant_id, reservation_id, occurred_at DESC);

This table is append-only by convention; no UPDATE or DELETE privileges are granted to the application role.

3.6 `reservation.reservation_holds` (with TTL)

CREATE TABLE reservation.reservation_holds (
  id                       text PRIMARY KEY,                -- hld_<ulid>
  tenant_id                text NOT NULL,
  reservation_id           text NOT NULL UNIQUE REFERENCES reservation.reservations(id) ON DELETE CASCADE,
  placed_at                timestamptz NOT NULL DEFAULT now(),
  expires_at               timestamptz NOT NULL,
  ttl_seconds              int NOT NULL CHECK (ttl_seconds BETWEEN 120 AND 1800),
  inventory_allocation_ids text[] NOT NULL DEFAULT '{}',
  payment_intent_id        text,
  expired                  boolean NOT NULL DEFAULT false
);

ALTER TABLE reservation.reservation_holds ENABLE ROW LEVEL SECURITY;
CREATE POLICY reservation_holds_tenant_isolation ON reservation.reservation_holds
  USING (tenant_id = current_setting('app.tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true));

CREATE INDEX idx_holds_expiry_sweeper
  ON reservation.reservation_holds (expires_at)
  WHERE expired = false;

The hold-expiry worker scans WHERE expired = false AND expires_at < now() every 30 s in tenant-broadcast mode (it is the only writer that does not set app.tenant_id per row; instead it acquires a session-level role with RLS bypassed for this single table — see SECURITY_MODEL §4.3).

3.7 `reservation.outbox` and `reservation.inbox_processed`

CREATE TABLE reservation.outbox (
  id              text PRIMARY KEY,                         -- ULID
  tenant_id       text NOT NULL,
  aggregate_id    text NOT NULL,
  subject         text NOT NULL,                            -- melmastoon.reservation.…
  ordering_key    text NOT NULL,                            -- <tenantId>:<aggregateId>
  envelope        jsonb NOT NULL,                           -- full EventEnvelope
  created_at      timestamptz NOT NULL DEFAULT now(),
  published_at    timestamptz,
  publish_attempts int NOT NULL DEFAULT 0
);

CREATE INDEX idx_outbox_unpublished
  ON reservation.outbox (created_at)
  WHERE published_at IS NULL;

CREATE TABLE reservation.inbox_processed (
  event_id        text NOT NULL,
  processor_name  text NOT NULL,
  processed_at    timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (event_id, processor_name)
);

The outbox is not RLS-scoped — the relay runs as a privileged role. Tenant isolation is preserved by tenant_id plus the envelope's own routing.

4. Migration strategy

Schema migrations live under services/reservation-service/drizzle/migrations/ and are applied by Drizzle Kit at deploy time.
Every new table in this service must include tenant_id, an RLS policy named <table>_tenant_isolation, and an index leading with tenant_id. The CI migration-check (SERVICE_TEMPLATE) fails the PR otherwise.
Backwards-compatible only: add nullable columns; add new tables; never DROP COLUMN in the same release that removes its writers.

Reservations are never hard-deleted; terminal states (cancelled, no_show, checked_out, expired_hold) are the closest equivalent.
Guest erasure on regulator request: reservation.guests.erased_at is set; PII columns are zeroed; email_hash and phone_e164_hash are kept for ledger integrity (the lookup just returns "erased"). Reservations stay intact for revenue ledger; primary_guest_id becomes a tombstone reference.
Audit modifications are kept indefinitely under regulated retention; erasure does not touch them.

6. Cross-references

Storage placement: 06 §2
ID prefixes: NAMING §6
Outbox/inbox semantics: 04 §6
Field-level encryption: SECURITY_MODEL §6
Sync replication semantics: SYNC_CONTRACT

1. Identity strategy​

2. Storage placement​

3. Postgres schema (canonical DDL)​

3.1 reservation.reservations​

3.2 reservation.reservation_items​

3.3 reservation.guests and reservation.additional_guests​

3.4 reservation.special_requests​

3.5 reservation.reservation_modifications (append-only audit)​

3.6 reservation.reservation_holds (with TTL)​

3.7 reservation.outbox and reservation.inbox_processed​

4. Migration strategy​

5. Soft delete & GDPR erasure​

6. Cross-references​