DATA_MODEL — bff-backoffice-service

Sibling: DOMAIN_MODEL · APPLICATION_LOGIC · SECURITY_MODEL · DEPLOYMENT_TOPOLOGY

Cross-cutting: 06 Data Models · Standards · NAMING

This BFF is stateless on the hot path. Memorystore (Redis) carries hot session and cache state; Cloud SQL Postgres carries the durable backing for outbox, idempotency, audit ledger, sync-cursor cache mirror, activity ledger, alert/ai-decision logs, and operator preferences mirror. No domain data is stored.

1. Storage posture

Tier	Tech	Purpose	Sizing target
Hot — sessions	Memorystore Redis 7 (standard, HA)	`BackofficeSession`, `MfaAttestation`, SSE bus	3 GiB; `noeviction`
Hot — cache	Memorystore Redis 7 (standard, HA)	`DashboardSnapshot`, `WorkbenchView`, `AISuggestionInbox` mirror, `AlertInbox` mirror, `OperatorPreferences` mirror, single-flight, idempotency-stat	5 GiB; `allkeys-lru`
Durable	Cloud SQL Postgres 16 (regional HA + cross-region replica)	`outbox`, `inbox`, `idempotency`, `operator_activity`, `device_sync_status`, `operator_preferences`, `alert_ack_log`, `ai_decision_log`, `lock_action_proxy_audit`, `offline_action_queue_hints`, `schema_migrations`	db-custom-2-8192
Pub/Sub	Pub/Sub	Event transport	n/a
Secret Manager	Secret Manager	Secrets	n/a
Audit lake	BigQuery	7y export of regulated tables	partitioned monthly

2. ID prefixes

Per NAMING:

Aggregate	Prefix
`BackofficeSession`	`bos_`
`DashboardSnapshot`	`dsh_`
`WorkbenchView`	`wbv_`
`AISuggestionInboxEntry`	`aim_`
`AlertInboxEntry`	`ali_`
`OperatorActivityRow`	`act_`
`DeviceSyncStatus`	`dsy_`
`OperatorPreferences`	`opr_`
`OfflineActionQueueHint`	`oaq_`
`LockActionProxyAudit`	`lap_`
`MfaAttestation`	`mfa_`
`SuggestionDecisionLog`	`sdl_`

3. Memorystore key schemas

3.1 Sessions tier (`bff-backoffice-session-*`)

Key	TTL	Purpose	Eviction
`session:bos_{ULID}`	12 h	Session blob	none (`noeviction`)
`session:by-device:{tenantId}:{deviceId}`	12 h	secondary index	none
`mfa:{mfa_ULID}`	5 min	Single-use attestation	none
`sse:bus:{deviceId}:channel:{name}`	n/a	Pub/sub channel for SSE multiplex	none

3.2 Cache tier (`bff-backoffice-cache-*`)

Key	TTL	Purpose	Eviction
`dash:{tenantId}:{propertyId}:{role}:{cacheKeyVersion}`	30 s	DashboardSnapshot	LRU
`wb:today:{tenantId}:{propertyId}`	15 s	TodayView	LRU
`wb:arr:{tenantId}:{propertyId}:{date}`	15 s	ArrivalsView	LRU
`wb:dep:{tenantId}:{propertyId}:{date}`	15 s	DeparturesView	LRU
`wb:ih:{tenantId}:{propertyId}`	15 s	InHouseView	LRU
`wb:hk:{tenantId}:{propertyId}`	15 s	HousekeepingBoardView	LRU
`wb:mt:{tenantId}:{propertyId}`	15 s	MaintenanceBoardView	LRU
`ai:inbox:{tenantId}:{propertyId}`	60 s	AISuggestionInbox mirror	LRU
`alerts:inbox:{tenantId}:{propertyId}`	30 s	AlertInbox mirror	LRU
`prefs:{tenantId}:{operatorId}`	5 min	OperatorPreferences mirror	LRU
`kbm:{tenantId}:{themeVersion}`	1 h	KeyboardShortcutMap	LRU
`cursor:{tenantId}:{deviceId}`	30 s	Sync cursor mirror	LRU
`sf:lock:{key}`	5 s	Single-flight leader lock	LRU
`cache:flag:bff-backoffice-flags`	30 s	Feature flags	LRU

Cache version (cacheKeyVersion) bumps when an upstream schema changes; old keys naturally evict.

4. Postgres schema

CREATE SCHEMA IF NOT EXISTS bff_backoffice;

Connection: workload-identity-bound bff-backoffice-sa to Cloud SQL via Cloud SQL Auth Proxy (private IP). Per-request session sets app.tenant_id for RLS.

4.1 `outbox`

CREATE TABLE bff_backoffice.outbox (
  id            uuid          PRIMARY KEY DEFAULT gen_random_uuid(),
  subject       text          NOT NULL,
  payload       jsonb         NOT NULL,
  envelope      jsonb         NOT NULL,
  enqueued_at   timestamptz   NOT NULL DEFAULT now(),
  published_at  timestamptz,
  attempts      int           NOT NULL DEFAULT 0,
  next_attempt_at timestamptz NOT NULL DEFAULT now(),
  dlq           boolean       NOT NULL DEFAULT false,
  last_error    text
);

CREATE INDEX ix_outbox_pending
  ON bff_backoffice.outbox (next_attempt_at)
  WHERE published_at IS NULL AND dlq = false;

CREATE INDEX ix_outbox_dlq
  ON bff_backoffice.outbox (enqueued_at DESC)
  WHERE dlq = true;

Drained by outbox-relay worker — at-least-once with idempotent consumer guarantee.

4.2 `inbox`

CREATE TABLE bff_backoffice.inbox (
  message_id    text          PRIMARY KEY,
  subject       text          NOT NULL,
  received_at   timestamptz   NOT NULL DEFAULT now(),
  processed_at  timestamptz,
  attempts      int           NOT NULL DEFAULT 0,
  last_error    text
);

CREATE INDEX ix_inbox_unprocessed
  ON bff_backoffice.inbox (received_at)
  WHERE processed_at IS NULL;

Used to deduplicate consumed events (cache invalidation, force-logout broadcast).

4.3 `idempotency`

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

CREATE INDEX ix_idem_expiry ON bff_backoffice.idempotency (expires_at);
CREATE INDEX ix_idem_tenant ON bff_backoffice.idempotency (tenant_id, created_at DESC);

ALTER TABLE bff_backoffice.idempotency ENABLE ROW LEVEL SECURITY;
CREATE POLICY idem_tenant_iso ON bff_backoffice.idempotency
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Cleanup job nightly removes expired rows.

4.4 `device_sync_status`

CREATE TABLE bff_backoffice.device_sync_status (
  id                       uuid          PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id                uuid          NOT NULL,
  device_id                text          NOT NULL,
  property_id              uuid,
  operator_id              text,
  last_heartbeat_at        timestamptz,
  last_sync_handshake_at   timestamptz,
  last_pull_cursor         text,
  last_push_ack            text,
  cursor_version           int           NOT NULL DEFAULT 0,
  app_version              text,
  app_platform             text,
  installer_channel        text          NOT NULL DEFAULT 'stable',
  network_profile          text,
  status                   text          NOT NULL DEFAULT 'online',
  pending_hints            jsonb         NOT NULL DEFAULT '[]'::jsonb,
  updated_at               timestamptz   NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX ux_device_sync_status_device
  ON bff_backoffice.device_sync_status (tenant_id, device_id);

CREATE INDEX ix_device_sync_status_status_heartbeat
  ON bff_backoffice.device_sync_status (status, last_heartbeat_at DESC);

ALTER TABLE bff_backoffice.device_sync_status ENABLE ROW LEVEL SECURITY;
CREATE POLICY device_sync_iso ON bff_backoffice.device_sync_status
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

4.5 `operator_preferences`

CREATE TABLE bff_backoffice.operator_preferences (
  id            uuid          PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id     uuid          NOT NULL,
  operator_id   text          NOT NULL,
  property_id   uuid,
  payload       jsonb         NOT NULL,
  version       int           NOT NULL DEFAULT 1,
  updated_at    timestamptz   NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX ux_prefs_op_prop
  ON bff_backoffice.operator_preferences (tenant_id, operator_id, COALESCE(property_id, '00000000-0000-0000-0000-000000000000'::uuid));

ALTER TABLE bff_backoffice.operator_preferences ENABLE ROW LEVEL SECURITY;
CREATE POLICY prefs_iso ON bff_backoffice.operator_preferences
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Mirror of tenant-service.operatorPreferences; we re-fetch on read-cache miss; mutations proxy + mirror.

4.6 `operator_activity` (ledger)

CREATE TABLE bff_backoffice.operator_activity (
  id              uuid         PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       uuid         NOT NULL,
  property_id     uuid,
  operator_id     text         NOT NULL,
  device_id       text         NOT NULL,
  session_id      text         NOT NULL,
  occurred_at     timestamptz  NOT NULL,
  category        text         NOT NULL,
  action          text         NOT NULL,
  resource_kind   text,
  resource_id     text,
  outcome         text         NOT NULL,
  latency_ms      int,
  error_code      text,
  context_hash    text
);

CREATE INDEX ix_activity_tenant_time
  ON bff_backoffice.operator_activity (tenant_id, occurred_at DESC);

CREATE INDEX ix_activity_operator_time
  ON bff_backoffice.operator_activity (operator_id, occurred_at DESC);

CREATE INDEX ix_activity_action_time
  ON bff_backoffice.operator_activity (action, occurred_at DESC);

ALTER TABLE bff_backoffice.operator_activity ENABLE ROW LEVEL SECURITY;
CREATE POLICY activity_iso ON bff_backoffice.operator_activity
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Retention: 90 d hot; export to BigQuery monthly partition for audit lake.

4.7 `alert_ack_log`

CREATE TABLE bff_backoffice.alert_ack_log (
  id              uuid         PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       uuid         NOT NULL,
  property_id     uuid,
  alert_id        text         NOT NULL,
  category        text         NOT NULL,
  severity        text         NOT NULL,
  raised_at       timestamptz  NOT NULL,
  acknowledged_at timestamptz  NOT NULL DEFAULT now(),
  acknowledged_by text         NOT NULL,
  device_id       text         NOT NULL,
  notes_truncated text
);

CREATE UNIQUE INDEX ux_alert_ack ON bff_backoffice.alert_ack_log (alert_id, acknowledged_by);
CREATE INDEX ix_alert_ack_tenant_time ON bff_backoffice.alert_ack_log (tenant_id, acknowledged_at DESC);

ALTER TABLE bff_backoffice.alert_ack_log ENABLE ROW LEVEL SECURITY;
CREATE POLICY alert_ack_iso ON bff_backoffice.alert_ack_log
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Retention: 365 d hot; export to BigQuery for 7 y.

4.8 `ai_decision_log`

CREATE TABLE bff_backoffice.ai_decision_log (
  id                uuid         PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id         uuid         NOT NULL,
  property_id       uuid,
  suggestion_id     text         NOT NULL,
  inbox_entry_id    text         NOT NULL,
  category          text         NOT NULL,
  severity          text         NOT NULL,
  outcome           text         NOT NULL CHECK (outcome IN ('accepted','rejected','modified')),
  modified_delta    jsonb,
  notes_truncated   text,
  decided_at        timestamptz  NOT NULL DEFAULT now(),
  decided_by        text         NOT NULL,
  device_id         text         NOT NULL,
  provenance        jsonb        NOT NULL
);

CREATE UNIQUE INDEX ux_ai_decision ON bff_backoffice.ai_decision_log (suggestion_id, decided_by);
CREATE INDEX ix_ai_decision_tenant_time ON bff_backoffice.ai_decision_log (tenant_id, decided_at DESC);

ALTER TABLE bff_backoffice.ai_decision_log ENABLE ROW LEVEL SECURITY;
CREATE POLICY ai_decision_iso ON bff_backoffice.ai_decision_log
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Retention: 7 y (regulated AI provenance).

4.9 `lock_action_proxy_audit`

CREATE TABLE bff_backoffice.lock_action_proxy_audit (
  id                    uuid         PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id             uuid         NOT NULL,
  property_id           uuid         NOT NULL,
  operator_id           text         NOT NULL,
  device_id             text         NOT NULL,
  reservation_id        text         NOT NULL,
  action                text         NOT NULL CHECK (action IN ('issue','revoke','rebind','extend')),
  vendor                text         NOT NULL,
  occurred_at           timestamptz  NOT NULL DEFAULT now(),
  outcome               text         NOT NULL CHECK (outcome IN ('success','failure')),
  error_code            text,
  mfa_attestation_id    text,
  upstream_request_id   text         NOT NULL,
  upstream_latency_ms   int          NOT NULL
);

CREATE INDEX ix_lock_audit_tenant_time ON bff_backoffice.lock_action_proxy_audit (tenant_id, occurred_at DESC);
CREATE INDEX ix_lock_audit_property_time ON bff_backoffice.lock_action_proxy_audit (property_id, occurred_at DESC);
CREATE INDEX ix_lock_audit_operator_time ON bff_backoffice.lock_action_proxy_audit (operator_id, occurred_at DESC);
CREATE INDEX ix_lock_audit_reservation ON bff_backoffice.lock_action_proxy_audit (reservation_id, occurred_at DESC);

ALTER TABLE bff_backoffice.lock_action_proxy_audit ENABLE ROW LEVEL SECURITY;
CREATE POLICY lock_audit_iso ON bff_backoffice.lock_action_proxy_audit
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Retention: 7 y (regulated).

4.10 `offline_action_queue_hints`

CREATE TABLE bff_backoffice.offline_action_queue_hints (
  id            uuid         PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id     uuid         NOT NULL,
  device_id     text         NOT NULL,
  operator_id   text         NOT NULL,
  reported_at   timestamptz  NOT NULL DEFAULT now(),
  payload       jsonb        NOT NULL,
  app_version   text         NOT NULL
);

CREATE INDEX ix_oaq_device_time ON bff_backoffice.offline_action_queue_hints (device_id, reported_at DESC);
CREATE INDEX ix_oaq_tenant_time ON bff_backoffice.offline_action_queue_hints (tenant_id, reported_at DESC);

ALTER TABLE bff_backoffice.offline_action_queue_hints ENABLE ROW LEVEL SECURITY;
CREATE POLICY oaq_iso ON bff_backoffice.offline_action_queue_hints
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Retention: 7 d. Informational only.

4.11 `schema_migrations`

Drizzle-managed.

5. RLS posture

Every tenant-scoped table has RLS enabled. The application-layer connection sets app.tenant_id per request (resolved from device-bound JWT subject). Cross-tenant access fails at the database driver, not just the controller.

6. Connection pool sizing

Pool	Connections	Notes
Postgres pool	12 per Cloud Run instance	`pg-pool`
Redis cache pool	16	`ioredis`
Redis session pool	16	`ioredis`
Pub/Sub publisher	8	`@google-cloud/pubsub`

Capacity for 30 instances × 12 = 360 concurrent Postgres connections; Cloud SQL configured for 500 max.

7. Capacity planning

Resource	Steady (50 RPS / region)	Peak (300 RPS / region)
Memorystore session tier	< 600 MiB	< 2 GiB
Memorystore cache tier	< 2 GiB	< 4 GiB
Cloud SQL (rows/day)	~ 2M activity rows/day	~ 8M / day
Cloud SQL storage	80 GiB	240 GiB
Outbox depth	< 200	< 5,000 (incident)
Pub/Sub egress	~ 30 GB/day	~ 100 GB/day

8. Backup + retention

Asset	Backup	Retention
Cloud SQL	Daily snapshot + 7 d PITR	35 d
Pub/Sub messages	Retained 7 d on topic; 14 d on DLQ	n/a
Memorystore	Standby replica (HA); no formal backup (ephemeral)	n/a
Audit lake (BigQuery)	Auto-snapshotted by GCP	7 y

9. Cleanup jobs

Job	Cron	Notes
`idempotency-cleanup`	hourly	Delete expired
`outbox-archival`	daily	Move 30 d+ rows to archive table
`outbox-dlq-prune`	weekly	Compact DLQ rows older than 90 d
`device-status-sweep`	every 5 min	Mark devices `offline` if `last_heartbeat_at < now()-5min`; emit alert
`activity-export`	daily	Export to BigQuery, prune local > 90 d
`alert-ack-export`	daily	Export to BigQuery, prune local > 365 d
`ai-decision-export`	daily	Export to BigQuery (regulated)
`lock-audit-export`	daily	Export to BigQuery (regulated)
`oaq-hints-prune`	hourly	Delete > 7 d

All jobs invoked via Cloud Scheduler → Cloud Run jobs (bff-backoffice-jobs).

10. Migrations

Drizzle ORM migrations in services/bff-backoffice-service/drizzle/migrations/.
Standards: forward-only; reversible where reasonable; deterministic; CI runs against ephemeral Postgres before merge.
Migration naming: NNNN_<descriptor>.sql per SERVICE_TEMPLATE.

11. PII handling

Field	Stored	Form
Operator name	No (in BFF)	Resolved from `tenant-service` per request; not persisted
Operator email	No	Same
Guest name in audit / activity	No	We log resource ids only
Notes (alert ack, AI decision)	Truncated 200 chars; no proactive PII redaction	Reviewed by data steward; truncation reduces blast radius
IP, UA	hashed (`ip_hash`, `fingerprint_hash`) in session blob	sha256 with pepper

Activity rows reference resources by id, not by PII. The audit lake export carries only id-references.

12. Capacity test gate

load:peak-300rps k6 profile must pass without:

Postgres connection exhaustion
Memorystore latency > 5 ms p95
Outbox lag > 60 s
Cloud Run cold-start spike beyond +20% latency

before any release that touches DATA_MODEL.

1. Storage posture​

2. ID prefixes​

3. Memorystore key schemas​

3.1 Sessions tier (bff-backoffice-session-*)​

3.2 Cache tier (bff-backoffice-cache-*)​

4. Postgres schema​

4.1 outbox​

4.2 inbox​

4.3 idempotency​

4.4 device_sync_status​

4.5 operator_preferences​

4.6 operator_activity (ledger)​

4.7 alert_ack_log​

4.8 ai_decision_log​

4.9 lock_action_proxy_audit​

4.10 offline_action_queue_hints​

4.11 schema_migrations​

5. RLS posture​

6. Connection pool sizing​

7. Capacity planning​

8. Backup + retention​

9. Cleanup jobs​

10. Migrations​

11. PII handling​

12. Capacity test gate​