DATA_MODEL — bff-tenant-booking-service

Sibling: DOMAIN_MODEL · APPLICATION_LOGIC · SECURITY_MODEL

Cross-cutting: 06 Data Models · Standards · NAMING

1. Storage posture

This BFF is stateless on the hot path. Hot session and draft state lives in Memorystore (Redis); durable backing rows live in Cloud SQL Postgres for analytics outbox, draft cold-mirror, idempotency, handoff replay log, and standard inbox. There is no domain table owned by this service.

Memorystore (hot)                    Cloud SQL (durable)
──────────────────────               ─────────────────────
tnt_session:<sessionId>              outbox
booking_draft:<bdr_>                 inbox
tenant-bootstrap:<...>               idempotency
slug:<tenantSlug>                    booking_draft_snapshots
availability:<...>                   handoff_arrival_log
cheapest:<...>                       schema_migrations
confirmation:<tenantId>:<rsv_>
bff-tenant-flags

2. ID prefixes (declared here)

Prefix	Entity	Notes
`bdr_`	BookingDraft	session-scoped, short-lived
`bha_`	BookingHandoffArrival	single-use ledger entry
`bds_`	BookingDraftSnapshot	cold mirror in Postgres

These are added to the canonical registry in Standards · NAMING §ID prefixes in the same PR.

3. Memorystore key schema

Key	Value	TTL	Purpose
`tnt_session:<sessionId>`	JSON `SessionBlob`	30 d (sliding)	Anonymous tenant-booking session
`booking_draft:<draftId>`	JSON `BookingDraft`	dynamic (max 30 min)	Hot booking draft
`tenant-bootstrap:<tenantId>:<locale>:<currency>`	JSON `TenantBootstrap`	5 min	Bootstrap composition cache
`slug:<tenantSlug>`	JSON `{ tenantId, status }`	1 h	Slug → tenantId resolution cache
`availability:<tenantId>:<propertyId>:<dateRangeHash>:<currency>`	JSON `AvailabilityVM`	30 s	Availability fanout cache
`cheapest:<tenantId>:<propertyId>:<dateRangeHash>:<currency>`	JSON `RoomRateMap`	60 s	Cheapest rate per room type
`confirmation:<tenantId>:<reservationId>`	JSON `ConfirmationView`	5 min	Confirmation page cache
`bff-tenant-flags`	JSON config	30 s (poll)	Feature flags + sample rates
`single-flight:<keyHash>`	leader marker	5 s	Stampede protection
`idempotency:<key>`	JSON `IdempotencyEntry`	24 h	Mutating-route idempotency

3.1 SessionBlob

interface SessionBlob {
  sessionId: string;            // tnt_<ulid>
  tenantId: TenantId;
  createdAt: string;
  lastSeenAt: string;
  localePreference: string;
  displayCurrency: string;
  marketingAttribution: MarketingAttribution;
  loyaltyContext: LoyaltyContext | null;     // Phase 2+
  activeBookingDraftId: BookingDraftId | null;
  consentFlags: { telemetry: boolean; functional: boolean };
  fingerprintHash: string;
  ipHash: string;
  deviceClass: 'browser-desktop' | 'browser-mobile' | 'mobile-app-ios' | 'mobile-app-android';
}

3.2 Memorystore configuration

Engine: Redis 7
Tier: Standard (HA with replica)
Memory: 5 GiB (raised to 10 GiB in flashSale mode)
Eviction policy: allkeys-lru for caches; noeviction for keys prefixed tnt_session: and booking_draft: via tagged tier (separate Memorystore instance for hot durable state to avoid eviction)
Auth: AUTH token + TLS-in-transit
Maxmemory-policy alert at 80% utilization

4. Postgres schema

Schema name: bff_tenant_booking. RLS enabled where applicable (no per-tenant tables in this service except booking_draft_snapshots; outbox carries tenant_id for routing but is service-scoped).

4.1 `outbox`

CREATE TABLE bff_tenant_booking.outbox (
  id              uuid          PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id        text          NOT NULL UNIQUE,
  tenant_id       uuid          NOT NULL,
  subject         text          NOT NULL,
  payload         jsonb         NOT NULL,
  envelope        jsonb         NOT NULL,
  occurred_at     timestamptz   NOT NULL,
  enqueued_at     timestamptz   NOT NULL DEFAULT now(),
  published_at    timestamptz,
  attempts        int           NOT NULL DEFAULT 0,
  next_attempt_at timestamptz,
  status          text          NOT NULL DEFAULT 'pending'
);

CREATE INDEX ix_outbox_status_next_attempt ON bff_tenant_booking.outbox (status, next_attempt_at)
  WHERE status IN ('pending', 'retrying');
CREATE INDEX ix_outbox_subject ON bff_tenant_booking.outbox (subject);
CREATE INDEX ix_outbox_tenant ON bff_tenant_booking.outbox (tenant_id);

4.2 `inbox`

CREATE TABLE bff_tenant_booking.inbox (
  message_id   text         PRIMARY KEY,
  subject      text         NOT NULL,
  received_at  timestamptz  NOT NULL DEFAULT now(),
  processed_at timestamptz
);

4.3 `idempotency`

CREATE TABLE bff_tenant_booking.idempotency (
  key            text         PRIMARY KEY,
  tenant_id      uuid         NOT NULL,
  request_hash   text         NOT NULL,
  response       jsonb        NOT NULL,
  status_code    int          NOT NULL,
  created_at     timestamptz  NOT NULL DEFAULT now(),
  expires_at     timestamptz  NOT NULL DEFAULT (now() + interval '24 hours')
);

CREATE INDEX ix_idempotency_tenant_expires ON bff_tenant_booking.idempotency (tenant_id, expires_at);

4.4 `booking_draft_snapshots`

Cold mirror for analytics + abandoned-cart recovery (Phase 2+).

CREATE TABLE bff_tenant_booking.booking_draft_snapshots (
  id                  uuid          PRIMARY KEY DEFAULT gen_random_uuid(),
  draft_id            text          NOT NULL,
  tenant_id           uuid          NOT NULL,
  session_id          text          NOT NULL,
  reservation_id      text,
  property_id         text,
  room_type_id        text,
  rate_plan_id        text,
  final_state         text          NOT NULL CHECK (final_state IN ('converted','abandoned','failed')),
  created_at          timestamptz   NOT NULL,
  finalized_at        timestamptz   NOT NULL DEFAULT now(),
  total_minor         bigint,
  currency            text,
  guest_email_hash    text,
  guest_phone_hash    text,
  marketing           jsonb,
  flow_state_history  jsonb         NOT NULL DEFAULT '[]'::jsonb,
  error_trail         jsonb         NOT NULL DEFAULT '[]'::jsonb,
  payment_method      text,
  payment_provider    text,
  device_class        text,
  locale              text,
  display_currency    text
);

CREATE INDEX ix_drafts_tenant_finalized ON bff_tenant_booking.booking_draft_snapshots (tenant_id, finalized_at DESC);
CREATE INDEX ix_drafts_state ON bff_tenant_booking.booking_draft_snapshots (final_state, finalized_at DESC);
CREATE INDEX ix_drafts_session ON bff_tenant_booking.booking_draft_snapshots (session_id, finalized_at DESC);

ALTER TABLE bff_tenant_booking.booking_draft_snapshots ENABLE ROW LEVEL SECURITY;

CREATE POLICY booking_draft_snapshots_tenant_isolation ON bff_tenant_booking.booking_draft_snapshots
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Retention: 30 days; pruned by daily Cloud Scheduler job. PII fields are hashed (no raw email/phone).

4.5 `handoff_arrival_log`

CREATE TABLE bff_tenant_booking.handoff_arrival_log (
  id                          text          PRIMARY KEY,             -- bha_<ulid>
  tenant_id                   uuid          NOT NULL,
  consumer_session_id         text          NOT NULL,
  property_id                 text          NOT NULL,
  received_at                 timestamptz   NOT NULL DEFAULT now(),
  expires_at                  timestamptz   NOT NULL,
  consumed                    boolean       NOT NULL DEFAULT false,
  consumed_at                 timestamptz,
  payload                     jsonb         NOT NULL,
  hmac_signature_fingerprint  text          NOT NULL,
  booking_draft_id            text,
  signing_key_id              text          NOT NULL,
  remote_ip_hash              text          NOT NULL,
  user_agent_class            text          NOT NULL
);

CREATE INDEX ix_handoff_tenant_received ON bff_tenant_booking.handoff_arrival_log (tenant_id, received_at DESC);
CREATE INDEX ix_handoff_consumer_session ON bff_tenant_booking.handoff_arrival_log (consumer_session_id);
CREATE INDEX ix_handoff_consumed ON bff_tenant_booking.handoff_arrival_log (consumed, expires_at);

ALTER TABLE bff_tenant_booking.handoff_arrival_log ENABLE ROW LEVEL SECURITY;

CREATE POLICY handoff_arrival_log_tenant_isolation ON bff_tenant_booking.handoff_arrival_log
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Retention: 30 days; replay protection only needs the lifetime of one mint window (30 min) with safety margin.

4.6 `schema_migrations`

CREATE TABLE bff_tenant_booking.schema_migrations (
  version    text         PRIMARY KEY,
  applied_at timestamptz  NOT NULL DEFAULT now()
);

5. Database connection settings

Setting	Value
Driver	`pg` 8.x via Drizzle
Pool min	2
Pool max	20
Statement timeout	5 s (BFF rarely runs long queries)
Idle in-transaction timeout	10 s
RLS context	`SET LOCAL app.tenant_id = '<uuid>'` per request (in `TenantContextGuard`)
TLS	required, verify-ca

6. Tenant isolation

outbox, inbox, idempotency are service-scoped (no per-tenant access pattern); RLS not applied.
booking_draft_snapshots and handoff_arrival_log carry tenant_id and have RLS policies.
Memorystore keys for tenant-scoped data are namespaced; cross-tenant key collision is impossible by construction.
Integration test tenant-isolation.spec.ts proves: a session belonging to tenantA cannot read draft snapshots or handoff arrivals of tenantB even with direct ID guess.

7. Migrations

Drizzle-managed under services/bff-tenant-booking-service/src/infrastructure/migrations/.

Migration	Description
`0001_init.sql`	Schema, outbox, inbox, idempotency, schema_migrations
`0002_booking_draft_snapshots.sql`	Cold mirror table
`0003_handoff_arrival_log.sql`	Handoff arrival ledger
`0004_indexes_pass_2.sql`	Index tuning after first load test
`0005_rls_policies.sql`	Enable RLS + policies on tenant-scoped tables
`0006_seed_dev_tenants.sql`	(dev/seed)
`0007_outbox_partitioning.sql`	(Phase 2) outbox monthly partitioning when row count > 50M

CI gate: every migration is idempotent (re-runnable) and ships with a rollback file 0NNN_<name>.down.sql where reversible.

8. Capacity planning

Object	Steady-state size	Growth
`tnt_session` keys	5–8 KiB; 100k active sessions = 500 MiB–800 MiB	linear with DAU
`booking_draft` keys	4–10 KiB; 10k active drafts = 40 MiB–100 MiB	linear with conversion-rate × DAU
`tenant-bootstrap` cache	50–80 KiB × 200 tenants × 3 locales × 2 currencies = ~96 MiB	linear with tenants
`availability` cache	5–10 KiB × ~1k hot keys = 10 MiB	linear with concurrent search shape
Postgres `outbox`	~1 KB / row × 100k rows hot = 100 MiB	drained continuously
Postgres `booking_draft_snapshots`	~3 KB / row × 30 d retention	linear with bookings
Postgres `handoff_arrival_log`	~1 KB / row × 30 d	linear with handoffs
Postgres `idempotency`	~2 KB / row × 24 h retention	linear with mutating req rate

Cloud SQL sizing: db-custom-2-8192 (Phase 1); upsize to db-custom-4-16384 when sustained writes > 200/s or storage > 100 GB.

9. Backups

Cloud SQL automated daily backups, 30-day retention.
PITR enabled (write-ahead-log retention 7 d).
Memorystore is not backed up — treated as cache + ephemeral session store. Sessions lost on regional failover are accepted (clients regenerate).

10. Cleanup jobs

Job	Cadence	Action
`outbox-prune`	hourly	Delete `published_at < now() - 7d`
`idempotency-prune`	hourly	Delete `expires_at < now()`
`handoff-arrival-prune`	hourly	Delete `received_at < now() - 30d`
`draft-snapshot-prune`	daily	Delete `finalized_at < now() - 30d`
`inbox-prune`	weekly	Delete `received_at < now() - 30d`
`sweep-abandoned-drafts`	every 60 s	Snapshot + emit + delete expired drafts in Memorystore

1. Storage posture​

2. ID prefixes (declared here)​

3. Memorystore key schema​

3.1 SessionBlob​

3.2 Memorystore configuration​

4. Postgres schema​

4.1 outbox​

4.2 inbox​

4.3 idempotency​

4.4 booking_draft_snapshots​

4.5 handoff_arrival_log​

4.6 schema_migrations​

5. Database connection settings​

6. Tenant isolation​

7. Migrations​

8. Capacity planning​

9. Backups​

10. Cleanup jobs​