DATA_MODEL — pricing-service

Sibling: DOMAIN_MODEL · APPLICATION_LOGIC · SECURITY_MODEL · SYNC_CONTRACT

Strategic anchors: 06 Data Models · 02 §6 Data Architecture · Standards / NAMING — DB

The pricing-service uses two physical data stores:

Cloud SQL Postgres 16 (melmastoon-prod-pricing-pg) — schemas: pricing (durable rate definitions) and pricing_quote (short-lived PriceQuote records, sized + tuned independently because of high churn and TTL deletion).
Memorystore (Redis 7) — read-through cache for RatePlan, TaxRule, FeeRule, latest FxRateSnapshot. Cache only; not authoritative.

Replication: 2 read replicas, regional HA primary, daily PITR snapshots, 35-day retention. CMEK on disk via Cloud KMS.

All identifiers are text ULIDs with the prefixes from DOMAIN_MODEL §1. All money columns are numeric(20,0) micro-units with a paired text ISO-4217 currency column.

1. Schema overview

pricing.rate_plans                   ← aggregate root
pricing.rate_plan_room_types         ← linkage to property-service room types
pricing.rate_rules                   ← child of rate_plan
pricing.discounts                    ← attached to rate_plan
pricing.promotions                   ← tenant-wide promo codes
pricing.promotion_redemptions        ← race-safe redemption ledger
pricing.tax_rules                    ← per-jurisdiction
pricing.fee_rules                    ← per-property
pricing.fx_rate_snapshots            ← per (base, quote) pair
pricing.dynamic_suggestions          ← AI advisory artefacts
pricing.outbox_messages              ← transactional outbox
pricing.inbox_messages               ← idempotency dedupe
pricing.idempotency_keys             ← API-level dedupe (24h TTL)
pricing_quote.price_quotes           ← short-lived (TTL 30m soft, 24h hard)
pricing_quote.quote_locks            ← reservation-pinned quote locks

2. Rate plans

CREATE TABLE pricing.rate_plans (
  id                       text        PRIMARY KEY CHECK (id LIKE 'rate_%'),
  tenant_id                text        NOT NULL,
  property_id              text        NOT NULL,
  code                     text        NOT NULL,
  display_name             jsonb       NOT NULL,                    -- BCP47 → string
  category                 text        NOT NULL CHECK (category IN ('BAR','weekly','government','corporate','non_refundable','package','group')),
  channel_scope            text        NOT NULL CHECK (channel_scope IN ('direct','ota','corporate','walk_in','group','all')),
  currency                 text        NOT NULL CHECK (currency ~ '^[A-Z]{3}$'),
  refundability            jsonb       NOT NULL,
  deposit_policy           jsonb       NOT NULL,
  advance_purchase_days    int         NOT NULL DEFAULT 0 CHECK (advance_purchase_days >= 0),
  min_los                  int         NOT NULL DEFAULT 1 CHECK (min_los >= 1),
  max_los                  int         NOT NULL CHECK (max_los >= min_los),
  sharia_compliant         boolean     NOT NULL DEFAULT false,
  package_inclusions       jsonb       NOT NULL DEFAULT '[]'::jsonb,
  status                   text        NOT NULL CHECK (status IN ('draft','published','archived')),
  version                  int         NOT NULL DEFAULT 0,
  created_at               timestamptz NOT NULL,
  created_by               jsonb       NOT NULL,
  updated_at               timestamptz NOT NULL,
  updated_by               jsonb       NOT NULL,
  archived_at              timestamptz,
  CONSTRAINT uq_rate_plans_code UNIQUE (tenant_id, property_id, code)
);

CREATE INDEX idx_rate_plans_tenant_property_status ON pricing.rate_plans (tenant_id, property_id, status);
CREATE INDEX idx_rate_plans_tenant_status_channel ON pricing.rate_plans (tenant_id, status, channel_scope) WHERE status = 'published';

RLS:

ALTER TABLE pricing.rate_plans ENABLE ROW LEVEL SECURITY;
CREATE POLICY rate_plans_tenant_isolation ON pricing.rate_plans
  USING (tenant_id = current_setting('app.tenant_id', true));

3. Rate plan ↔ room types

CREATE TABLE pricing.rate_plan_room_types (
  rate_plan_id   text NOT NULL REFERENCES pricing.rate_plans(id) ON DELETE CASCADE,
  tenant_id      text NOT NULL,
  room_type_id   text NOT NULL,
  enabled        boolean NOT NULL DEFAULT true,
  base_micro     numeric(20,0) NOT NULL CHECK (base_micro >= 0),
  currency       text NOT NULL,
  notes          text,
  PRIMARY KEY (rate_plan_id, room_type_id)
);
CREATE INDEX idx_rprt_tenant_room ON pricing.rate_plan_room_types (tenant_id, room_type_id);
ALTER TABLE pricing.rate_plan_room_types ENABLE ROW LEVEL SECURITY;
CREATE POLICY rprt_tenant_isolation ON pricing.rate_plan_room_types USING (tenant_id = current_setting('app.tenant_id', true));

The rate_plan_room_types row carries the per-room-type base so a rate plan can serve heterogeneous room types under the same plan code. The RateRule.scope.roomTypeIds then narrows further per date/day-of-week.

4. Rate rules

CREATE TABLE pricing.rate_rules (
  id                  text PRIMARY KEY CHECK (id LIKE 'rru_%'),
  tenant_id           text NOT NULL,
  rate_plan_id        text NOT NULL REFERENCES pricing.rate_plans(id) ON DELETE CASCADE,
  priority            int  NOT NULL,
  scope               jsonb NOT NULL,                  -- { dateRange, daysOfWeek?, roomTypeIds?, occupancyBands? }
  base_micro          numeric(20,0) NOT NULL,
  currency            text  NOT NULL,
  multiplier          numeric(8,4) NOT NULL DEFAULT 1.0 CHECK (multiplier >= 0),
  surcharge_micro     numeric(20,0) NOT NULL DEFAULT 0,
  los_min_nights      int,
  los_discount_pct    numeric(6,3),
  source              text NOT NULL CHECK (source IN ('manual','ai_accepted','import')),
  ai_provenance       jsonb,
  version             int  NOT NULL DEFAULT 0,
  created_at          timestamptz NOT NULL,
  created_by          jsonb NOT NULL,
  updated_at          timestamptz NOT NULL,
  updated_by          jsonb NOT NULL,
  retired_at          timestamptz
);

CREATE INDEX idx_rate_rules_plan_priority ON pricing.rate_rules (rate_plan_id, priority DESC) WHERE retired_at IS NULL;
CREATE INDEX idx_rate_rules_tenant_plan ON pricing.rate_rules (tenant_id, rate_plan_id);
CREATE INDEX idx_rate_rules_scope_gin ON pricing.rate_rules USING gin (scope jsonb_path_ops);

ALTER TABLE pricing.rate_rules ENABLE ROW LEVEL SECURITY;
CREATE POLICY rate_rules_tenant_isolation ON pricing.rate_rules USING (tenant_id = current_setting('app.tenant_id', true));

scope is GIN-indexed for fast containment queries (scope @> '{"roomTypeIds":["rmt_…"]}').

5. Discounts

CREATE TABLE pricing.discounts (
  id              text PRIMARY KEY CHECK (id LIKE 'dsc_%'),
  tenant_id       text NOT NULL,
  rate_plan_id    text NOT NULL REFERENCES pricing.rate_plans(id) ON DELETE CASCADE,
  kind            text NOT NULL CHECK (kind IN ('los','advance_purchase','last_minute','loyalty','corporate')),
  config          jsonb NOT NULL,
  priority        int  NOT NULL,
  enabled         boolean NOT NULL DEFAULT true,
  created_at      timestamptz NOT NULL,
  updated_at      timestamptz NOT NULL
);
CREATE INDEX idx_discounts_plan ON pricing.discounts (rate_plan_id, priority DESC) WHERE enabled = true;
ALTER TABLE pricing.discounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY discounts_tenant_isolation ON pricing.discounts USING (tenant_id = current_setting('app.tenant_id', true));

6. Promotions and redemptions

CREATE TABLE pricing.promotions (
  id                          text PRIMARY KEY CHECK (id LIKE 'prm_%'),
  tenant_id                   text NOT NULL,
  code                        text NOT NULL,
  discount_kind               text NOT NULL CHECK (discount_kind IN ('percent','flat')),
  discount_pct                numeric(6,3),
  discount_flat_micro         numeric(20,0),
  currency                    text,
  applicable_rate_plan_ids    text[] NOT NULL DEFAULT '{}',
  applicable_channels         text[] NOT NULL DEFAULT '{}',
  valid_from                  date NOT NULL,
  valid_to                    date NOT NULL,
  usage_cap                   int  NOT NULL DEFAULT 0,        -- 0 = unlimited
  usage_count                 int  NOT NULL DEFAULT 0,
  status                      text NOT NULL CHECK (status IN ('draft','active','inactive','expired')),
  created_at                  timestamptz NOT NULL,
  updated_at                  timestamptz NOT NULL,
  CONSTRAINT uq_promotions_code UNIQUE (tenant_id, code)
);
CREATE INDEX idx_promotions_active ON pricing.promotions (tenant_id, status, valid_from, valid_to);

ALTER TABLE pricing.promotions ENABLE ROW LEVEL SECURITY;
CREATE POLICY promotions_tenant_isolation ON pricing.promotions USING (tenant_id = current_setting('app.tenant_id', true));

CREATE TABLE pricing.promotion_redemptions (
  id              text PRIMARY KEY,
  tenant_id       text NOT NULL,
  promotion_id    text NOT NULL REFERENCES pricing.promotions(id) ON DELETE CASCADE,
  quote_id        text NOT NULL,
  reservation_id  text,
  redeemed_at     timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT uq_promo_redemption_quote UNIQUE (promotion_id, quote_id)
);
CREATE INDEX idx_promo_redemptions_tenant_promo ON pricing.promotion_redemptions (tenant_id, promotion_id);
ALTER TABLE pricing.promotion_redemptions ENABLE ROW LEVEL SECURITY;
CREATE POLICY promo_redemptions_tenant_isolation ON pricing.promotion_redemptions USING (tenant_id = current_setting('app.tenant_id', true));

Race-safe redemption is implemented as a single INSERT … RETURNING paired with a conditional update on promotions.usage_count:

WITH inserted AS (
  INSERT INTO pricing.promotion_redemptions (id, tenant_id, promotion_id, quote_id)
  VALUES ($1, $2, $3, $4)
  ON CONFLICT DO NOTHING
  RETURNING id
)
UPDATE pricing.promotions p
   SET usage_count = usage_count + 1,
       updated_at  = now()
  FROM inserted i
 WHERE p.id = $3
   AND (p.usage_cap = 0 OR p.usage_count < p.usage_cap)
RETURNING p.id, p.usage_count, p.usage_cap;

If the RETURNING row is empty, the cap was hit. The dedicated (promotion_id, quote_id) unique constraint provides idempotency per quote.

7. Tax rules

CREATE TABLE pricing.tax_rules (
  id                text PRIMARY KEY CHECK (id LIKE 'tax_%'),
  tenant_id         text NOT NULL,
  jurisdiction      jsonb NOT NULL,                       -- { country, region? }
  scope             text  NOT NULL CHECK (scope IN ('room','fee','all')),
  rate_kind         text  NOT NULL CHECK (rate_kind IN ('percent','flat_per_night','flat_per_stay')),
  rate_value        numeric(12,4) NOT NULL,               -- percent or micro per (night|stay)
  currency          text,                                 -- required for flat_*
  inclusive         boolean NOT NULL DEFAULT false,
  valid_from        date NOT NULL,
  valid_to          date,                                 -- null = open-ended
  superseded_by     text REFERENCES pricing.tax_rules(id),
  version           int  NOT NULL DEFAULT 0,
  created_at        timestamptz NOT NULL,
  updated_at        timestamptz NOT NULL,
  EXCLUDE USING gist (
    tenant_id WITH =,
    (jurisdiction->>'country') WITH =,
    COALESCE(jurisdiction->>'region','') WITH =,
    scope WITH =,
    daterange(valid_from, COALESCE(valid_to, 'infinity'::date), '[]') WITH &&
  )
);
CREATE INDEX idx_tax_rules_lookup ON pricing.tax_rules (tenant_id, (jurisdiction->>'country'), (jurisdiction->>'region'), scope, valid_from);
ALTER TABLE pricing.tax_rules ENABLE ROW LEVEL SECURITY;
CREATE POLICY tax_rules_tenant_isolation ON pricing.tax_rules USING (tenant_id = current_setting('app.tenant_id', true));

The EXCLUDE constraint forbids overlapping validity windows for the same (tenant, country, region, scope) tuple — guaranteeing a deterministic tax rate for any date.

8. Fee rules

CREATE TABLE pricing.fee_rules (
  id                  text PRIMARY KEY CHECK (id LIKE 'fee_%'),
  tenant_id           text NOT NULL,
  property_id         text NOT NULL,
  code                text NOT NULL,                              -- 'resort','cleaning','tourism_tax','vat'
  display_name        jsonb NOT NULL,
  rate_kind           text NOT NULL CHECK (rate_kind IN ('percent','flat_per_night','flat_per_stay','flat_per_guest_per_night')),
  rate_value          numeric(20,4) NOT NULL,
  currency            text,
  taxable             boolean NOT NULL DEFAULT true,
  applies_to_rate_plan_ids text[] NOT NULL DEFAULT '{}',          -- empty = all
  sharia_tag          text NOT NULL CHECK (sharia_tag IN ('neutral','riba_forbidden','disclosed')),
  valid_from          date NOT NULL,
  valid_to            date,
  enabled             boolean NOT NULL DEFAULT true,
  version             int  NOT NULL DEFAULT 0,
  created_at          timestamptz NOT NULL,
  updated_at          timestamptz NOT NULL,
  CONSTRAINT uq_fee_rule_code UNIQUE (tenant_id, property_id, code, valid_from)
);
CREATE INDEX idx_fee_rules_property_active ON pricing.fee_rules (tenant_id, property_id, valid_from) WHERE enabled = true;
ALTER TABLE pricing.fee_rules ENABLE ROW LEVEL SECURITY;
CREATE POLICY fee_rules_tenant_isolation ON pricing.fee_rules USING (tenant_id = current_setting('app.tenant_id', true));

9. FX rate snapshots

CREATE TABLE pricing.fx_rate_snapshots (
  id              text PRIMARY KEY CHECK (id LIKE 'fxs_%'),
  tenant_id       text,                                  -- null = global, else override
  base            text NOT NULL CHECK (base ~ '^[A-Z]{3}$'),
  quote           text NOT NULL CHECK (quote ~ '^[A-Z]{3}$'),
  rate            numeric(20,10) NOT NULL CHECK (rate > 0),
  provider_ref    text,
  captured_at     timestamptz NOT NULL,
  stale_after     timestamptz NOT NULL,
  hard_expire_at  timestamptz NOT NULL,
  source          text NOT NULL CHECK (source IN ('ecb','xe','manual','tenant_pin')),
  created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_fx_latest ON pricing.fx_rate_snapshots (base, quote, captured_at DESC);
CREATE INDEX idx_fx_tenant_pin ON pricing.fx_rate_snapshots (tenant_id, base, quote, captured_at DESC) WHERE tenant_id IS NOT NULL;
-- RLS: tenant-scoped pins are isolated; global rows are readable by all tenants
ALTER TABLE pricing.fx_rate_snapshots ENABLE ROW LEVEL SECURITY;
CREATE POLICY fx_global_read ON pricing.fx_rate_snapshots FOR SELECT USING (tenant_id IS NULL);
CREATE POLICY fx_tenant_isolation ON pricing.fx_rate_snapshots USING (tenant_id IS NULL OR tenant_id = current_setting('app.tenant_id', true));

10. Dynamic suggestions

CREATE TABLE pricing.dynamic_suggestions (
  id                  text PRIMARY KEY CHECK (id LIKE 'dps_%'),
  tenant_id           text NOT NULL,
  property_id         text NOT NULL,
  room_type_id        text NOT NULL,
  date                date NOT NULL,
  baseline_rate_micro numeric(20,0) NOT NULL,
  low_micro           numeric(20,0) NOT NULL,
  high_micro          numeric(20,0) NOT NULL,
  currency            text NOT NULL,
  signals             jsonb NOT NULL,
  rationale           text,
  status              text NOT NULL CHECK (status IN ('generated','accepted','rejected','expired')),
  accepted_rule_id    text,
  accepted_by         jsonb,
  accepted_at         timestamptz,
  rejected_by         jsonb,
  rejected_at         timestamptz,
  expires_at          timestamptz NOT NULL,
  ai_provenance       jsonb NOT NULL,
  created_at          timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_dyn_open ON pricing.dynamic_suggestions (tenant_id, property_id, status, date) WHERE status IN ('generated');
ALTER TABLE pricing.dynamic_suggestions ENABLE ROW LEVEL SECURITY;
CREATE POLICY dynamic_suggestions_tenant_isolation ON pricing.dynamic_suggestions USING (tenant_id = current_setting('app.tenant_id', true));

11. PriceQuote (separate schema, TTL-managed)

CREATE SCHEMA pricing_quote;

CREATE TABLE pricing_quote.price_quotes (
  id              text PRIMARY KEY CHECK (id LIKE 'qte_%'),
  tenant_id       text NOT NULL,
  property_id     text NOT NULL,
  rate_plan_id    text NOT NULL,
  rate_plan_version int NOT NULL,
  request         jsonb NOT NULL,        -- normalized request snapshot
  derivation      jsonb NOT NULL,        -- full step log
  totals          jsonb NOT NULL,
  fx_snapshot_id  text,
  fx_rate         numeric(20,10),
  promo_id        text,
  promo_code      text,
  promo_redemption_id text,
  status          text NOT NULL CHECK (status IN ('live','locked','expired','revoked')),
  ttl_seconds     int  NOT NULL,
  requested_at    timestamptz NOT NULL,
  expires_at      timestamptz NOT NULL,                 -- soft TTL: 30m
  hard_expires_at timestamptz NOT NULL,                 -- hard delete: 24h
  created_at      timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- daily partitions, dropped after 7 days; pg_partman manages rotation
CREATE INDEX idx_pq_tenant_status_expires ON pricing_quote.price_quotes (tenant_id, status, expires_at);
CREATE INDEX idx_pq_property_dates ON pricing_quote.price_quotes (tenant_id, property_id, ((request->'stayWindow'->>'start')::date));

ALTER TABLE pricing_quote.price_quotes ENABLE ROW LEVEL SECURITY;
CREATE POLICY pq_tenant_isolation ON pricing_quote.price_quotes USING (tenant_id = current_setting('app.tenant_id', true));

CREATE TABLE pricing_quote.quote_locks (
  quote_id        text PRIMARY KEY REFERENCES pricing_quote.price_quotes(id) ON DELETE CASCADE,
  tenant_id       text NOT NULL,
  reservation_id  text NOT NULL,
  lock_token      text NOT NULL,
  locked_at       timestamptz NOT NULL DEFAULT now(),
  expires_at      timestamptz NOT NULL
);
CREATE INDEX idx_ql_tenant_reservation ON pricing_quote.quote_locks (tenant_id, reservation_id);
ALTER TABLE pricing_quote.quote_locks ENABLE ROW LEVEL SECURITY;
CREATE POLICY ql_tenant_isolation ON pricing_quote.quote_locks USING (tenant_id = current_setting('app.tenant_id', true));

A pg_cron job runs every 5 minutes:

-- soft expire
UPDATE pricing_quote.price_quotes
   SET status = 'expired'
 WHERE status = 'live'
   AND expires_at < now()
 RETURNING id;
-- … then publish quote.expired.v1 via outbox for each id

pg_partman drops daily partitions older than 7 days, satisfying the 24h hard TTL with safety margin.

12. Outbox / inbox / idempotency

CREATE TABLE pricing.outbox_messages (
  id            text PRIMARY KEY,
  tenant_id     text NOT NULL,
  aggregate_id  text NOT NULL,
  subject       text NOT NULL,
  payload       jsonb NOT NULL,
  envelope      jsonb NOT NULL,
  ordering_key  text NOT NULL,
  occurred_at   timestamptz NOT NULL,
  published_at  timestamptz,
  attempts      int NOT NULL DEFAULT 0,
  last_error    text
);
CREATE INDEX idx_outbox_unpublished ON pricing.outbox_messages (occurred_at) WHERE published_at IS NULL;

CREATE TABLE pricing.inbox_messages (
  message_id   text PRIMARY KEY,
  subject      text NOT NULL,
  tenant_id    text,
  received_at  timestamptz NOT NULL DEFAULT now(),
  processed_at timestamptz
);
CREATE INDEX idx_inbox_unprocessed ON pricing.inbox_messages (received_at) WHERE processed_at IS NULL;

CREATE TABLE pricing.idempotency_keys (
  key             text PRIMARY KEY,
  tenant_id       text NOT NULL,
  use_case        text NOT NULL,
  request_hash    text NOT NULL,
  response_hash   text,
  http_status     int,
  response_body   jsonb,
  created_at      timestamptz NOT NULL DEFAULT now(),
  expires_at      timestamptz NOT NULL DEFAULT now() + interval '24 hours'
);
CREATE INDEX idx_idem_expires ON pricing.idempotency_keys (expires_at);

13. Migrations

Drizzle is the migration tool. Migrations live in services/pricing-service/migrations/ and are named YYYYMMDDHHmmss__<short>.sql. Forward-only; rollbacks ship as new forward migrations. CI fails if any migration is destructive (drops/renames a column with dependent indexes) without a paired data-migration plan.

Migration	Purpose
`20260101000000__init.sql`	schemas, RLS roles, base tables
`20260115000000__add_dynamic_suggestions.sql`	AI dynamic-pricing tables
`20260201000000__partition_price_quotes.sql`	switch to partitioned table
`20260301000000__add_fee_sharia_tag.sql`	fee_rules.sharia_tag
`20260315000000__add_quote_locks.sql`	inter-service lock for booking saga

14. Capacity & retention

Table	Avg rows/tenant/year	Retention
`rate_plans`	50	indefinite
`rate_rules`	5 000	indefinite
`discounts`	200	indefinite
`promotions`	1 200	indefinite
`promotion_redemptions`	200 000	24 months hot, then BigQuery
`tax_rules`	80	indefinite
`fee_rules`	100	indefinite
`fx_rate_snapshots`	30 000 (daily × pairs)	24 months hot
`dynamic_suggestions`	100 000	12 months hot
`price_quotes`	10–50 M	7 days physical (TTL); BQ mirror 18 months
`outbox/inbox`	matches event volume	30 days hot

The CIDX-friendly access pattern keeps p95 quote-derivation reads ≤ 12 ms, comfortably under the 180 ms quote SLO.

1. Schema overview​

2. Rate plans​

3. Rate plan ↔ room types​

4. Rate rules​

5. Discounts​

6. Promotions and redemptions​

7. Tax rules​

8. Fee rules​

9. FX rate snapshots​

10. Dynamic suggestions​

11. PriceQuote (separate schema, TTL-managed)​

12. Outbox / inbox / idempotency​

13. Migrations​

14. Capacity & retention​