DATA_MODEL — bff-consumer-service

Sibling: DOMAIN_MODEL · APPLICATION_LOGIC · API_CONTRACTS · SECURITY_MODEL

Cross-cutting: 06 Data Models · Standards · NAMING

Important. This BFF has no domain database. The bulk of state lives in Memorystore (Redis). A small Cloud SQL Postgres schema (bff_consumer) holds the analytics outbox, handoff replay log, anonymous wishlist mirror, bot-score audit, idempotency cache, and inbox dedupe table. There is no tenant_id column on any owned row (the BFF is cross-tenant) and therefore no Postgres RLS policy.

1. Storage map

Store	Use	Why this store
Memorystore (Redis, Standard tier, 1 GB Phase 1)	`GuestSession`, `SearchSession`, brand-peek cache, search-list cache, hotel-detail cache, facet cache, single-flight locks, idempotency cache, rate-limit token buckets	Sub-ms reads, native TTL, Lua scripting for atomic cap-and-push, Redis Replication for regional HA
Cloud SQL Postgres (shared `bff_consumer` schema, db `melmastoon-bff`)	`outbox`, `inbox`, `handoff_replay_log`, `wishlist_anonymous`, `bot_score_log`, `idempotency_keys` (mirrored for crash recovery)	Transactional outbox with the wishlist/handoff inserts; durable audit
Pub/Sub (no storage of our own)	Outbound telemetry events	Backbone

2. ID prefix declarations

Per NAMING §ID prefixes, this service introduces:

Prefix	Entity	Notes
`gms_`	GuestSession	Anonymous session cookie value
`srs_`	SearchSession	TTL 1 h
`wsh_`	Wishlist	Mirrored row
`bhd_`	BookingHandoff	Single-use, signed
`mpv_`	MetaPageView	Append-only
`cfe_`	ConversionFunnelEvent	Append-only

These are added to the canonical registry in the same PR that lands this bundle (per the rule in NAMING.md).

3. Postgres schema (`bff_consumer.*`)

3.1 `outbox`

CREATE TABLE bff_consumer.outbox (
  id              ulid        PRIMARY KEY,
  topic           text        NOT NULL,
  payload         jsonb       NOT NULL,
  headers         jsonb       NOT NULL,
  retention_class text        NOT NULL CHECK (retention_class IN ('operational','audit')),
  created_at      timestamptz NOT NULL DEFAULT now(),
  published_at    timestamptz,
  attempts        integer     NOT NULL DEFAULT 0,
  last_error      text
);

CREATE INDEX ix_outbox_unpublished ON bff_consumer.outbox (created_at)
  WHERE published_at IS NULL;

Drained by outbox-relay worker. Rows older than 30 days with published_at IS NOT NULL are pruned nightly.

3.2 `inbox`

CREATE TABLE bff_consumer.inbox (
  event_id     ulid        PRIMARY KEY,
  subject      text        NOT NULL,
  received_at  timestamptz NOT NULL DEFAULT now(),
  processed_at timestamptz,
  digest       bytea       NOT NULL
);

CREATE INDEX ix_inbox_received ON bff_consumer.inbox (received_at);

7-day retention.

3.3 `handoff_replay_log`

CREATE TABLE bff_consumer.handoff_replay_log (
  id                ulid        PRIMARY KEY,             -- bhd_<ulid>
  guest_session_id  text        NOT NULL,                 -- gms_<ulid>
  tenant_id         uuid        NOT NULL,                 -- target tenant
  property_id       uuid        NOT NULL,                 -- target property
  check_in          date        NOT NULL,
  check_out         date        NOT NULL,
  adults            smallint    NOT NULL,
  children          smallint    NOT NULL,
  rooms             smallint    NOT NULL,
  currency          char(3)     NOT NULL,
  locale            text        NOT NULL,
  source_campaign   jsonb,
  hmac_key_id       text        NOT NULL,
  fingerprint_hash  bytea       NOT NULL,
  ip_hash           bytea       NOT NULL,
  minted_at         timestamptz NOT NULL,
  expires_at        timestamptz NOT NULL,
  consumed          boolean     NOT NULL DEFAULT false,
  consumed_at       timestamptz,
  consumed_by       text,                                  -- bff-tenant-booking instance id
  CONSTRAINT chk_dates CHECK (check_out > check_in),
  CONSTRAINT chk_ttl   CHECK (expires_at > minted_at AND expires_at - minted_at <= interval '30 minutes')
);

CREATE INDEX ix_handoff_consumed_at ON bff_consumer.handoff_replay_log (consumed_at) WHERE consumed = true;
CREATE INDEX ix_handoff_expires_at  ON bff_consumer.handoff_replay_log (expires_at)  WHERE consumed = false;
CREATE INDEX ix_handoff_session     ON bff_consumer.handoff_replay_log (guest_session_id, minted_at DESC);

The consumed flag is updated by a small RPC the receiving bff-tenant-booking-service calls (POST /internal/handoff/{id}/consume); we accept this side-write because the trust boundary is the HMAC, and the flag exists only as a defense-in-depth replay guard. Rows older than 24 h are pruned by a nightly job.

3.4 `wishlist_anonymous`

CREATE TABLE bff_consumer.wishlist_anonymous (
  id                ulid        PRIMARY KEY,             -- wsh_<ulid>
  guest_session_id  text        NOT NULL,                 -- gms_<ulid>
  tenant_id         uuid        NOT NULL,
  property_id       uuid        NOT NULL,
  source            text        NOT NULL CHECK (source IN ('detail','list','map','recently-viewed')),
  note              text        CHECK (char_length(note) <= 280),
  added_at          timestamptz NOT NULL DEFAULT now(),
  removed_at        timestamptz,
  CONSTRAINT uq_wishlist_session_property UNIQUE (guest_session_id, property_id)
);

CREATE INDEX ix_wishlist_session ON bff_consumer.wishlist_anonymous (guest_session_id);
CREATE INDEX ix_wishlist_tenant_property ON bff_consumer.wishlist_anonymous (tenant_id, property_id);

Hard-deleted on POST /session/clear. Soft-deleted (set removed_at) on DELETE /wishlist/{propertyId}; pruned after 30 days.

3.5 `bot_score_log`

CREATE TABLE bff_consumer.bot_score_log (
  id                ulid        PRIMARY KEY,
  guest_session_id  text,                                  -- nullable; pre-bootstrap requests have no session
  fingerprint_hash  bytea       NOT NULL,
  ip_hash           bytea       NOT NULL,
  user_agent_hash   bytea       NOT NULL,
  score             real        NOT NULL CHECK (score >= 0 AND score <= 1),
  verdict           text        NOT NULL CHECK (verdict IN ('human','suspect','bot')),
  signals           jsonb       NOT NULL,                  -- BotSignal[]
  action_taken      text        NOT NULL CHECK (action_taken IN ('none','challenge','hard-block')),
  evaluated_at      timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX ix_botscore_evaluated ON bff_consumer.bot_score_log (evaluated_at);
CREATE INDEX ix_botscore_fingerprint ON bff_consumer.bot_score_log (fingerprint_hash, evaluated_at DESC);

7-day retention; insert-only.

3.6 `idempotency_keys`

CREATE TABLE bff_consumer.idempotency_keys (
  composite_key     text        PRIMARY KEY,             -- sha256(guest_session_id|route|key)
  guest_session_id  text        NOT NULL,
  route             text        NOT NULL,
  request_digest    bytea       NOT NULL,                 -- sha256(canonicalized request body)
  response_status   smallint    NOT NULL,
  response_body     jsonb       NOT NULL,
  created_at        timestamptz NOT NULL DEFAULT now(),
  expires_at        timestamptz NOT NULL                  -- created_at + 24h
);

CREATE INDEX ix_idempotency_expires ON bff_consumer.idempotency_keys (expires_at);

Memorystore is the primary; Postgres is a crash-recovery mirror written in the same transaction as the protected mutation.

3.7 `tenant_suspended_cache` (materialized lookup)

CREATE TABLE bff_consumer.tenant_suspended_cache (
  tenant_id     uuid        PRIMARY KEY,
  suspended_at  timestamptz NOT NULL,
  reason        text        NOT NULL,
  refreshed_at  timestamptz NOT NULL DEFAULT now()
);

Refreshed by the tenant.suspended.v1 / tenant.reinstated.v1 consumers. Hot reads use Memorystore; this table is the durable mirror for service restarts.

4. Indexes summary

Index	Selectivity rationale
`ix_outbox_unpublished`	Drains hundreds of unpublished rows quickly; partial index keeps it tiny
`ix_handoff_session(guest_session_id, minted_at DESC)`	Per-session handoff history for fraud analysis
`ix_handoff_expires_at` (partial)	Sweeper for un-consumed expiry
`ix_wishlist_session`	List wishlist by session in `O(log n)`
`ix_wishlist_tenant_property`	Future tenant-side analytics (most-wishlisted properties)
`ix_botscore_fingerprint(..., evaluated_at DESC)`	Cadence detector reads recent scores per fingerprint
`ix_idempotency_expires`	Sweeper for expired keys

5. Memorystore (Redis) keyspace

All keys are prefixed by environment ({env}:). Examples below assume prod:.

Key pattern	Type	TTL	Purpose
`prod:bff-consumer:session:{gms_id}`	Hash	30 d	`GuestSession` blob
`prod:bff-consumer:session:{gms_id}:wishlist`	List (capped 100)	30 d	Mirror of `WishlistRef[]`
`prod:bff-consumer:session:{gms_id}:recent`	List (capped 50)	30 d	`RecentlyViewedEntry[]`
`prod:bff-consumer:session:{gms_id}:history`	List (capped 25)	30 d	`SearchHistoryEntry[]`
`prod:bff-consumer:srs:{srs_id}`	Hash	1 h	`SearchSession`
`prod:bff-consumer:cache:search:list:{queryHash}`	String (JSON)	60 s (campaign mode 5 min)	List-view VM result
`prod:bff-consumer:cache:search:map:{queryHash}`	String (JSON)	60 s	Map-view pins
`prod:bff-consumer:cache:detail:{propertyId}:{locale}:{currency}`	String (JSON)	5 min	`HotelDetailVM`
`prod:bff-consumer:cache:availability:{propertyId}:{from}:{to}:{occupancy}`	String (JSON)	60 s	Light availability
`prod:bff-consumer:cache:facets:{country}:{region}`	String (JSON)	1 h	`FacetCatalog`
`prod:bff-consumer:cache:brand-peek:{tenantId}`	String (JSON)	15 min	`BrandPeekVM`
`prod:bff-consumer:cache:property:{propertyId}`	String (JSON)	5 min	Raw `PropertyDetail`
`prod:bff-consumer:lock:{cacheKey}`	String	5 s	Single-flight lock
`prod:bff-consumer:idem:{compositeKey}`	String (JSON)	24 h	Idempotency-Key cache
`prod:bff-consumer:rl:{bucketKey}`	Token bucket (custom)	1 min	Rate-limit
`prod:bff-consumer:tenant-suspended`	Set	n/a (event-driven)	In-memory suspended-tenant set
`prod:bff-consumer:bot:cadence:{fingerprint}`	List (sliding window)	60 s	Last-60s request timestamps
`prod:bff-consumer:bot:fp-collisions:{fingerprint}`	Sorted Set (sessions)	24 h	Sessions sharing fingerprint

5.1 Atomic operations (Lua scripts)

-- session:wishlist:append
-- KEYS[1] = session wishlist list key
-- ARGV[1] = wishlistRef JSON
-- ARGV[2] = max length (100)
local len = redis.call('LLEN', KEYS[1])
if len >= tonumber(ARGV[2]) then return -1 end
redis.call('RPUSH', KEYS[1], ARGV[1])
redis.call('EXPIRE', KEYS[1], 2592000)  -- 30 d
return len + 1

-- recently-viewed:push
-- Cap-and-push with FIFO eviction
redis.call('LPUSH', KEYS[1], ARGV[1])
redis.call('LTRIM', KEYS[1], 0, tonumber(ARGV[2]) - 1)
redis.call('EXPIRE', KEYS[1], 2592000)
return redis.call('LLEN', KEYS[1])

-- single-flight:acquire
-- Returns 1 if acquired, 0 if a leader is already populating
return redis.call('SET', KEYS[1], ARGV[1], 'NX', 'EX', ARGV[2])

6. TypeScript ↔ Postgres mapping

// src/infrastructure/db/schema/handoff-replay-log.ts (Drizzle)
export const handoffReplayLog = pgTable('handoff_replay_log', {
  id:               text('id').primaryKey(),                      // bhd_<ulid>
  guestSessionId:   text('guest_session_id').notNull(),
  tenantId:         uuid('tenant_id').notNull(),
  propertyId:       uuid('property_id').notNull(),
  checkIn:          date('check_in').notNull(),
  checkOut:         date('check_out').notNull(),
  adults:           smallint('adults').notNull(),
  children:         smallint('children').notNull(),
  rooms:            smallint('rooms').notNull(),
  currency:         char('currency', { length: 3 }).notNull(),
  locale:           text('locale').notNull(),
  sourceCampaign:   jsonb('source_campaign'),
  hmacKeyId:        text('hmac_key_id').notNull(),
  fingerprintHash:  bytea('fingerprint_hash').notNull(),
  ipHash:           bytea('ip_hash').notNull(),
  mintedAt:         timestamp('minted_at', { withTimezone: true }).notNull(),
  expiresAt:        timestamp('expires_at', { withTimezone: true }).notNull(),
  consumed:         boolean('consumed').notNull().default(false),
  consumedAt:       timestamp('consumed_at', { withTimezone: true }),
  consumedBy:       text('consumed_by'),
});

Money is not persisted in this BFF (we don't compute money). When a RateSnapshotVM is cached, the cheapestNightlyMinor and totalForStayMinor are stored as text inside the JSON blob (Drizzle JSONB) to avoid bigint truncation.

7. Migrations

drizzle-kit per service. Initial migration ordering:

Ordinal	File
0001	`0001_init_schema.sql` (CREATE SCHEMA bff_consumer)
0002	`0002_create_outbox_inbox.sql`
0003	`0003_create_handoff_replay_log.sql`
0004	`0004_create_wishlist_anonymous.sql`
0005	`0005_create_bot_score_log.sql`
0006	`0006_create_idempotency_keys.sql`
0007	`0007_create_tenant_suspended_cache.sql`

Forward-only migrations. Rollback by re-applying a corrective migration (per docs/06-data-models.md).

8. Backups + recovery

Cloud SQL Postgres: point-in-time recovery (PITR) enabled, 7-day window.
Memorystore: Redis Standard tier with auto-failover; data is best-effort. On Memorystore loss the BFF self-heals: cache rebuilds on demand, sessions are re-issued (cookie still valid, but blob is empty), wishlist is re-hydrated from wishlist_anonymous Postgres mirror, idempotency cache is re-hydrated from idempotency_keys Postgres mirror.
Outbox: any unpublished rows are retried on restart; no events are lost.

9. Capacity sizing

Table	Phase 1 row estimate	Index footprint
`outbox`	< 50k unpublished at peak; 2M/day produced	< 200 MB
`handoff_replay_log`	100k/day	< 100 MB
`wishlist_anonymous`	1M (Phase 1)	< 50 MB
`bot_score_log`	50k/day (only suspect+)	< 50 MB
`idempotency_keys`	100k active	< 100 MB

Memorystore working set: 1 GB Phase 1, 4 GB campaign mode.

10. Open questions

Question	Owner	Target decision
Phase 2 authenticated upgrade — where do we persist user-linked wishlist?	Frontend Platform + IAM	Phase 1.5
Cross-region session replication — Redis Replication HA vs. eventual sync via Pub/Sub	SRE	Phase 1 readiness
Bot-score model versioning — store model id on each row?	Security	Phase 2
Should `handoff_replay_log` move to Cloud Spanner if cross-region writes become hot?	SRE	Phase 3

1. Storage map​

2. ID prefix declarations​

3. Postgres schema (bff_consumer.*)​

3.1 outbox​

3.2 inbox​

3.3 handoff_replay_log​

3.4 wishlist_anonymous​

3.5 bot_score_log​

3.6 idempotency_keys​

3.7 tenant_suspended_cache (materialized lookup)​

4. Indexes summary​

5. Memorystore (Redis) keyspace​

5.1 Atomic operations (Lua scripts)​

6. TypeScript ↔ Postgres mapping​

7. Migrations​

8. Backups + recovery​

9. Capacity sizing​

10. Open questions​