Skip to main content

06 — Data Models

Companion: 02 Enterprise Architecture · 03 Microservices · 04 Event-Driven Architecture · 05 API Design · 07 Security & Tenancy · 08 AI Architecture · 12 Desktop Spec · ADR-0002 Multi-Tenancy · ADR-0003 Electron Offline-First · Naming

This is the canonical data-model document for Ghasi Melmastoon. It defines the storage strategy across cloud and edge, the ID strategy and branded-type contract, the multi-tenant isolation pattern, the schemas of the 12 most important aggregates (TypeScript + Postgres + RLS + invariants + edge cases), the SQLite shape that ships with the Electron desktop app, the Firestore documents that hold sync state, the pgvector layout for embeddings, the read-model projections, audit storage, the encryption posture, soft-delete + GDPR erasure, and migration strategy.

When in doubt, the rule is: each service owns its tables, the domain layer enforces invariants, the database enforces tenant isolation, and the desktop is a replicated subset — not a different model.

1. Storage Strategy​

SurfaceStoreWhyUsed by
Authoritative transactional statePostgres on Cloud SQL — per-service schema, RLS for tenant isolationStrong consistency; mature ecosystem; RLS is the second line of defense on top of the domain layerAll 22 services except bff-* (BFFs are stateless)
Schema-per-tenant carve-outPostgres on Cloud SQL — tenant_<uuid>_billing, tenant_<uuid>_paymentsPCI scope minimization, clean financial audit, clean offboarding (pg_dump + DROP SCHEMA) — see ADR-0002billing-service, payment-gateway-service
Desktop offline storeSQLite via better-sqlite3 (Node 20 main process), WAL mode, encrypted via SQLCipherSync-or-die; offline-first front desk and housekeeping must keep working without networkElectron desktop backoffice
Sync state + outbox cursorsFirestore (regional, single-doc updates, low ops)Cheap, regional, no instance management; one document per device per scopesync-service, iam-service (device pairing), Electron app (write through API)
Media + invoices + theme assetsCloud Storage (Standard for hot, Nearline ≥90d, Coldline for compliance archives)Object storage is the right primitive; signed URLs are scope-bound to tenant prefixfile-storage-service, billing-service (PDF invoices), theme-config-service
Hot reads + rate-limit countersMemorystore (Redis Standard tier)Sub-ms latency for tenant settings, theme tokens, room/rate snapshots, idempotency keysAll services that have a hot-read path
Embeddings (RAG, semantic)pgvector extension inside the ai-orchestrator-service Postgres schemaNo separate vector DB billing; HNSW is good enough at our scale; per-tenant namespace via tenant_id columnai-orchestrator-service only
Cross-tenant read modelPostgres + OpenSearch hybrid inside search-aggregation-servicePostgres holds the source projection (RLS off — this is the only legitimate cross-tenant store); OpenSearch indexes facets, geo, full-textsearch-aggregation-service, bff-consumer-service (read)
Warm + cold analyticsBigQuery sink fed by Pub/Sub → Cloud Functions → tableCheap aggregation; partitioned by event_date; clustered by tenant_idanalytics-service, reporting-service, audit-service (audit dataset)
SecretsSecret ManagerKMS-backed; per-service service account; rotation policy; never in env files in gitAll services
Envelope keysCloud KMSCMEK on Cloud SQL; DEKs wrapped per record where field-level encryption is neededbilling-service, payment-gateway-service, lock-integration-service, iam-service

Rule of one writer. Every aggregate has exactly one service that may write its authoritative store. Every other consumer reads through events, not through the database. There are no cross-service joins, ever.

2. ID Strategy​

2.1 Branded TypeScript types​

All IDs are branded strings. The branding is purely a compile-time discipline; at runtime they are ULIDs (sortable, time-prefixed) prefixed with the entity short code.

type Branded<T, B extends string> = T & { readonly __brand: B };

type TenantId          = Branded<string, 'TenantId'>;
type UserId            = Branded<string, 'UserId'>;
type MembershipId      = Branded<string, 'MembershipId'>;
type DeviceId          = Branded<string, 'DeviceId'>;
type PropertyId        = Branded<string, 'PropertyId'>;
type RoomTypeId        = Branded<string, 'RoomTypeId'>;
type RoomId            = Branded<string, 'RoomId'>;
type RatePlanId        = Branded<string, 'RatePlanId'>;
type RateRuleId        = Branded<string, 'RateRuleId'>;
type InventoryAllocId  = Branded<string, 'InventoryAllocId'>;
type ReservationId     = Branded<string, 'ReservationId'>;
type ReservationItemId = Branded<string, 'ReservationItemId'>;
type GuestId           = Branded<string, 'GuestId'>;
type FolioId           = Branded<string, 'FolioId'>;
type FolioChargeId     = Branded<string, 'FolioChargeId'>;
type InvoiceId         = Branded<string, 'InvoiceId'>;
type PaymentId         = Branded<string, 'PaymentId'>;
type PaymentIntentId   = Branded<string, 'PaymentIntentId'>;
type RefundId          = Branded<string, 'RefundId'>;
type HousekeepingTaskId = Branded<string, 'HousekeepingTaskId'>;
type MaintenanceTicketId = Branded<string, 'MaintenanceTicketId'>;
type KeyCredentialId   = Branded<string, 'KeyCredentialId'>;
type LockDeviceId      = Branded<string, 'LockDeviceId'>;
type StaffId           = Branded<string, 'StaffId'>;
type ShiftId           = Branded<string, 'ShiftId'>;
type ThemeId           = Branded<string, 'ThemeId'>;
type MediaAssetId      = Branded<string, 'MediaAssetId'>;
type EventId           = Branded<string, 'EventId'>;
type RequestId         = Branded<string, 'RequestId'>;
type DecisionId        = Branded<string, 'DecisionId'>;       // AI HITL acceptance

2.2 ID format and prefixes​

  • Format: <prefix>_<ULID> (or UUIDv7 interchangeably for sortability — both are time-prefixed; ULID is preferred for human readability).
  • Generation: in @ghasi/domain-primitives via IdGenerator port; the default adapter uses ulid (Node) and crypto.randomUUID (browser fallback when v7 not available).
  • Storage: Postgres column type text with CHECK (id ~ '^<prefix>_[0-9A-Z]{26}$'). UUIDv7 columns use uuid and a separate id_prefix is implied by table.

The canonical prefix table (subset most relevant to this document) — full list in Naming:

PrefixEntityOwning service
tnt_Tenanttenant-service
usr_Useriam-service
mbr_Membershiptenant-service
dev_Deviceiam-service
ppt_Propertyproperty-service
rmt_RoomTypeproperty-service
rmu_Roomproperty-service
rsv_Reservationreservation-service
rsi_ReservationItemreservation-service
gst_Guestreservation-service
rate_RatePlanpricing-service
rrl_RateRulepricing-service
inv_InventoryAllocationinventory-service
fol_Foliobilling-service
chg_FolioChargebilling-service
pay_Paymentpayment-gateway-service
pyi_PaymentIntentpayment-gateway-service
rfd_Refundpayment-gateway-service
hkt_HousekeepingTaskhousekeeping-service
key_KeyCredentiallock-integration-service
lck_LockDevicelock-integration-service
stf_StaffMemberstaff-service
thm_Themetheme-config-service
med_MediaAssetfile-storage-service
dec_Decision (AI HITL)ai-orchestrator-service

Why ULID and not auto-increment?

  1. Globally unique → safe for sync (no collision when offline devices generate IDs).
  2. Time-prefixed → naturally clustered on disk in insertion order (good for Postgres B-Tree).
  3. Opaque to clients → cannot enumerate.
  4. Prefixed → debuggable in logs without schema lookup.

3. Tenant Isolation Pattern​

Every tenant-scoped table has tenant_id uuid NOT NULL as a first-class column with an index appropriate to its access pattern. Three layers cooperate:

3.1 Connection middleware​

NestJS RequestContextMiddleware reads tenant_id from the verified JWT claim and binds it to AsyncLocalStorage. The repository layer's connection-checkout hook executes:

SET app.tenant_id = '<uuid from JWT claim>';

PgBouncer in transaction mode runs this init on every checkout. Cloud SQL roles used by the app are non-superuser and cannot bypass FORCE ROW LEVEL SECURITY.

3.2 RLS policy template​

Applied to every shared-tier table (billing-service and payment-gateway-service add the same as defense in depth on top of schema-per-tenant):

ALTER TABLE <name> ENABLE ROW LEVEL SECURITY;
ALTER TABLE <name> FORCE ROW LEVEL SECURITY;
CREATE POLICY <name>_tenant_isolation ON <name>
  USING      (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

3.3 Outbox guard​

The outbox writer asserts that the event payload's tenantId matches current_setting('app.tenant_id')::uuid before commit. A mismatch raises MELMASTOON.PLATFORM.CROSS_TENANT_REFERENCE and aborts the transaction.

3.4 Domain guard​

Aggregates carry tenantId: TenantId in their constructor. Cross-aggregate references are checked at construction; a Reservation with propertyId belonging to a different tenant cannot be materialized — the factory throws CrossTenantReferenceError before persistence.

4. Core Aggregates (12)​

For each aggregate: TypeScript interface, Postgres CREATE TABLE, RLS policy, key invariants, brief edge cases.

4.1 Tenant (tenant-service)​

interface Tenant {
  id: TenantId;
  name: string;
  slug: string;                                          // URL-safe, globally unique
  type: 'independent' | 'chain';
  plan: 'starter' | 'growth' | 'plus' | 'enterprise';
  status: 'active' | 'trial' | 'suspended' | 'closed';
  homeRegion: 'me-central1' | 'europe-west1' | 'asia-south1';
  dataResidencyPin?: 'me-central1' | 'europe-west1';     // hard pin (Plus+)
  defaultLocale: Locale;                                 // BCP-47, e.g. 'ps-AF'
  defaultCurrency: ISO4217;                              // e.g. 'AFN', 'USD'
  settings: TenantSettings;                              // JSONB, see § settings
  createdAt: ISODate;
  updatedAt: ISODate;
  deletedAt?: ISODate;
}
CREATE TABLE tenants (
  id              uuid PRIMARY KEY,
  name            text NOT NULL,
  slug            text NOT NULL UNIQUE,
  type            text NOT NULL CHECK (type IN ('independent','chain')),
  plan            text NOT NULL,
  status          text NOT NULL,
  home_region     text NOT NULL,
  residency_pin   text,
  default_locale  text NOT NULL,
  default_currency text NOT NULL,
  settings        jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now(),
  deleted_at      timestamptz
);
CREATE INDEX ix_tenants_status ON tenants(status) WHERE deleted_at IS NULL;
CREATE INDEX ix_tenants_residency_pin ON tenants(residency_pin) WHERE residency_pin IS NOT NULL;

Note: tenants itself is not RLS-scoped — it is the registry of all tenants. Access is gated by IAM (only platform.super_admin can list; tenant users can only SELECT WHERE id = current_setting('app.tenant_id')::uuid, enforced by the tenant_self_only policy).

Invariants: slug is globally unique and matches ^[a-z0-9-]{3,40}$. Once status='closed', no further mutations are accepted by the application; finance schemas are dropped only after the GDPR erasure window expires.

Edge cases: Slug rename emits tenant.slug.changed.v1 and triggers cache invalidation across BFFs and CDN. Region change is not supported in-place; it requires a tenant-migration saga.

4.2 User (iam-service)​

interface User {
  id: UserId;
  primaryEmail: Email;
  emailVerified: boolean;
  status: 'active' | 'locked' | 'disabled' | 'pending_verification';
  homeTenantId?: TenantId;                               // primary tenant for default routing
  passwordHash?: string;                                  // argon2id; null if WebAuthn-only
  webauthnCredentials: WebAuthnPublicKey[];
  mfaFactors: MFAFactor[];
  failedAttempts: number;
  lockedUntil?: ISODate;
  createdAt: ISODate;
  updatedAt: ISODate;
}
CREATE TABLE users (
  id                  uuid PRIMARY KEY,
  primary_email       citext NOT NULL UNIQUE,
  email_verified      boolean NOT NULL DEFAULT false,
  status              text NOT NULL,
  home_tenant_id      uuid REFERENCES tenants(id),
  password_hash       text,
  webauthn_credentials jsonb NOT NULL DEFAULT '[]'::jsonb,
  mfa_factors         jsonb NOT NULL DEFAULT '[]'::jsonb,
  failed_attempts     int NOT NULL DEFAULT 0,
  locked_until        timestamptz,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_users_status ON users(status);
CREATE INDEX ix_users_home_tenant ON users(home_tenant_id);

Users are not tenant-scoped at the table level (a user can be a member of multiple tenants). Tenant membership lives in memberships (in tenant-service), which is RLS-scoped. The IAM-service users table is gated by IAM-internal authorization; only the user themselves and platform.super_admin may read a user row.

Invariants: primary_email is unique platform-wide and case-insensitive (citext). password_hash uses argon2id (m=64MiB, t=3, p=1). failed_attempts ≥ 5 flips status='locked' and sets locked_until = now() + 15min.

Edge cases: Email change is a saga: verify new email → swap → invalidate sessions → notify both addresses.

4.3 Property (property-service)​

interface Property {
  id: PropertyId;
  tenantId: TenantId;
  name: I18nString;
  slug: string;                                          // unique within tenant
  address: AddressLine;
  geo: { lat: number; lng: number };                     // WGS84
  timezone: string;                                       // IANA, e.g. 'Asia/Kabul'
  starRating?: 1|2|3|4|5;
  amenities: string[];                                   // ['wifi','breakfast','parking',...]
  policies: PropertyPolicies;
  photos: MediaRef[];
  status: 'draft' | 'published' | 'closed';
  createdAt: ISODate;
  updatedAt: ISODate;
  deletedAt?: ISODate;
}
CREATE TABLE properties (
  id           uuid PRIMARY KEY,
  tenant_id    uuid NOT NULL,
  name         jsonb NOT NULL,
  slug         text NOT NULL,
  address      jsonb NOT NULL,
  geo          geography(POINT, 4326) NOT NULL,
  timezone     text NOT NULL,
  star_rating  int CHECK (star_rating BETWEEN 1 AND 5),
  amenities    text[] NOT NULL DEFAULT '{}',
  policies     jsonb NOT NULL DEFAULT '{}'::jsonb,
  photos       jsonb NOT NULL DEFAULT '[]'::jsonb,
  status       text NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  updated_at   timestamptz NOT NULL DEFAULT now(),
  deleted_at   timestamptz,
  UNIQUE (tenant_id, slug)
);
CREATE INDEX ix_properties_tenant_id_status ON properties(tenant_id, status);
CREATE INDEX ix_properties_geo ON properties USING GIST (geo);
ALTER TABLE properties ENABLE ROW LEVEL SECURITY;
ALTER TABLE properties FORCE ROW LEVEL SECURITY;
CREATE POLICY properties_tenant_isolation ON properties
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

Invariants: A published property must have ≥1 photo, ≥1 room, ≥1 active rate plan; the application enforces, the DB does not. Geo coordinates within plausible Earth bounds.

Edge cases: Soft-delete: deleted_at set; status flips to closed; search-aggregation-service removes from index on property.archived.v1.

4.4 RoomType and Room (property-service)​

interface RoomType {
  id: RoomTypeId;
  tenantId: TenantId;
  propertyId: PropertyId;
  code: string;                                          // 'KING','TWIN','SUITE'; unique per property
  name: I18nString;
  description: I18nString;
  maxOccupancy: number;
  defaultBedConfig: 'king'|'queen'|'twin'|'bunk'|'family';
  amenities: string[];
  basePriceMicro: bigint;                                // baseline price in micro units of property currency
  baseCurrency: ISO4217;
  photos: MediaRef[];
  archivedAt?: ISODate;
}

interface Room {
  id: RoomId;
  tenantId: TenantId;
  propertyId: PropertyId;
  roomTypeId: RoomTypeId;
  number: string;                                        // human-friendly, unique per property
  floor?: number;
  status: 'clean'|'dirty'|'oo'|'oos'|'inspected';        // out-of-order, out-of-service
  features: string[];                                    // ['balcony','sea-view',...]
  lockDeviceId?: LockDeviceId;
  archivedAt?: ISODate;
  createdAt: ISODate;
  updatedAt: ISODate;
}
CREATE TABLE room_types (
  id                uuid PRIMARY KEY,
  tenant_id         uuid NOT NULL,
  property_id       uuid NOT NULL,
  code              text NOT NULL,
  name              jsonb NOT NULL,
  description       jsonb NOT NULL,
  max_occupancy     int NOT NULL CHECK (max_occupancy >= 1),
  default_bed_config text NOT NULL,
  amenities         text[] NOT NULL DEFAULT '{}',
  base_price_micro  bigint NOT NULL CHECK (base_price_micro > 0),
  base_currency     text NOT NULL,
  photos            jsonb NOT NULL DEFAULT '[]'::jsonb,
  archived_at       timestamptz,
  UNIQUE (property_id, code)
);
CREATE INDEX ix_room_types_tenant_property ON room_types(tenant_id, property_id);
ALTER TABLE room_types ENABLE ROW LEVEL SECURITY;
ALTER TABLE room_types FORCE ROW LEVEL SECURITY;
CREATE POLICY room_types_tenant_isolation ON room_types
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE TABLE rooms (
  id              uuid PRIMARY KEY,
  tenant_id       uuid NOT NULL,
  property_id     uuid NOT NULL,
  room_type_id    uuid NOT NULL REFERENCES room_types(id),
  number          text NOT NULL,
  floor           int,
  status          text NOT NULL CHECK (status IN ('clean','dirty','oo','oos','inspected')),
  features        text[] NOT NULL DEFAULT '{}',
  lock_device_id  uuid,
  archived_at     timestamptz,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now(),
  UNIQUE (property_id, number)
);
CREATE INDEX ix_rooms_tenant_property_status ON rooms(tenant_id, property_id, status);
CREATE INDEX ix_rooms_room_type ON rooms(room_type_id);
ALTER TABLE rooms ENABLE ROW LEVEL SECURITY;
ALTER TABLE rooms FORCE ROW LEVEL SECURITY;
CREATE POLICY rooms_tenant_isolation ON rooms
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

Invariants: Room.roomTypeId must reference a RoomType of the same propertyId. Room status follows the lattice clean ↔ inspected → dirty → clean and * → oo|oos → clean. lockDeviceId may be null while a vendor is being onboarded.

Edge cases: Renumbering a room (e.g., 101 → 1A) is a supported but audited operation; room.renumbered.v1 event is emitted; existing reservations keep referencing the immutable roomId, only number changes.

4.5 RatePlan and RateRule (pricing-service)​

interface RatePlan {
  id: RatePlanId;
  tenantId: TenantId;
  propertyId: PropertyId;
  code: string;                                          // 'BAR','WEEKLY','GOV','CORP-ACME'
  name: I18nString;
  kind: 'BAR'|'weekly'|'corporate'|'government'|'package'|'non-refundable';
  appliesToRoomTypeIds: RoomTypeId[];
  currency: ISO4217;
  basePriceMicro: bigint;
  cancellationPolicyId?: string;
  status: 'draft'|'active'|'archived';
  validFrom?: ISODate;
  validTo?: ISODate;
  createdAt: ISODate;
  updatedAt: ISODate;
}

interface RateRule {
  id: RateRuleId;
  tenantId: TenantId;
  ratePlanId: RatePlanId;
  ruleType: 'multiplier'|'override'|'min_los'|'max_los'|'closed_to_arrival'|'closed_to_departure';
  dateRange: DateRange;
  daysOfWeek?: ('mon'|'tue'|'wed'|'thu'|'fri'|'sat'|'sun')[];
  multiplier?: number;                                    // for 'multiplier'
  overridePriceMicro?: bigint;                            // for 'override'
  losValue?: number;                                       // for min/max LoS
  source: 'manual'|'ai_hint';                             // ai_hint requires HITL acceptance
  acceptedDecisionId?: DecisionId;
  createdAt: ISODate;
}
CREATE TABLE rate_plans (
  id              uuid PRIMARY KEY,
  tenant_id       uuid NOT NULL,
  property_id     uuid NOT NULL,
  code            text NOT NULL,
  name            jsonb NOT NULL,
  kind            text NOT NULL,
  applies_to_room_type_ids uuid[] NOT NULL,
  currency        text NOT NULL,
  base_price_micro bigint NOT NULL CHECK (base_price_micro >= 0),
  cancellation_policy_id text,
  status          text NOT NULL,
  valid_from      date,
  valid_to        date,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now(),
  UNIQUE (property_id, code)
);
CREATE INDEX ix_rate_plans_tenant_property_status ON rate_plans(tenant_id, property_id, status);
ALTER TABLE rate_plans ENABLE ROW LEVEL SECURITY;
ALTER TABLE rate_plans FORCE ROW LEVEL SECURITY;
CREATE POLICY rate_plans_tenant_isolation ON rate_plans
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE TABLE rate_rules (
  id                   uuid PRIMARY KEY,
  tenant_id            uuid NOT NULL,
  rate_plan_id         uuid NOT NULL REFERENCES rate_plans(id) ON DELETE CASCADE,
  rule_type            text NOT NULL,
  date_from            date NOT NULL,
  date_to              date NOT NULL,
  days_of_week         text[],
  multiplier           numeric(6,4),
  override_price_micro bigint,
  los_value            int,
  source               text NOT NULL CHECK (source IN ('manual','ai_hint')),
  accepted_decision_id uuid,
  created_at           timestamptz NOT NULL DEFAULT now(),
  CHECK (date_from <= date_to)
);
CREATE INDEX ix_rate_rules_plan_dates ON rate_rules(rate_plan_id, date_from, date_to);
ALTER TABLE rate_rules ENABLE ROW LEVEL SECURITY;
ALTER TABLE rate_rules FORCE ROW LEVEL SECURITY;
CREATE POLICY rate_rules_tenant_isolation ON rate_rules
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

Invariants: AI-source rate rules with multiplier deviating > 5% from the BAR baseline require acceptedDecisionId to be present. Non-refundable plans require cancellationPolicyId IS NOT NULL.

Edge cases: Overlapping multiplier rules are resolved deterministically: more specific (with days_of_week) wins; ties broken by created_at desc.

4.6 AvailabilityCalendar (inventory-service)​

interface AvailabilitySlot {
  id: InventoryAllocId;
  tenantId: TenantId;
  propertyId: PropertyId;
  roomId: RoomId;                                        // per-room granularity (not per-room-type)
  date: ISODate;                                          // the night this slot represents (check-in date)
  status: 'open'|'held'|'allocated'|'closed';
  reservationId?: ReservationId;
  holdExpiresAt?: ISODate;                                // for 'held'
  version: number;                                         // optimistic concurrency
  updatedAt: ISODate;
}
CREATE TABLE availability_slots (
  id               uuid PRIMARY KEY,
  tenant_id        uuid NOT NULL,
  property_id      uuid NOT NULL,
  room_id          uuid NOT NULL,
  date             date NOT NULL,
  status           text NOT NULL CHECK (status IN ('open','held','allocated','closed')),
  reservation_id   uuid,
  hold_expires_at  timestamptz,
  version          int NOT NULL DEFAULT 0,
  updated_at       timestamptz NOT NULL DEFAULT now(),
  UNIQUE (room_id, date)
);
CREATE INDEX ix_avail_tenant_property_date ON availability_slots(tenant_id, property_id, date);
CREATE INDEX ix_avail_room_date_status ON availability_slots(room_id, date, status);
CREATE INDEX ix_avail_held_expiry ON availability_slots(hold_expires_at)
  WHERE status='held';
ALTER TABLE availability_slots ENABLE ROW LEVEL SECURITY;
ALTER TABLE availability_slots FORCE ROW LEVEL SECURITY;
CREATE POLICY avail_tenant_isolation ON availability_slots
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

Invariants: At most one allocated slot per (room_id, date) — the UNIQUE (room_id, date) enforces it; transitions are guarded by version (optimistic locking). held rows older than hold_expires_at are reaped by a scheduled job emitting inventory.released.v1.

Edge cases: Same-day check-in with a held slot whose hold has just expired: the reaper races with the booking; the booking saga uses SELECT … FOR UPDATE NOWAIT to prevent two parties from claiming the same slot.

4.7 Reservation and ReservationItem (reservation-service)​

interface Reservation {
  id: ReservationId;
  tenantId: TenantId;
  propertyId: PropertyId;
  guestId: GuestId;
  channel: 'direct'|'meta'|'front-desk'|'agent'|'ota';
  state: 'held'|'confirmed'|'checked_in'|'checked_out'|'cancelled'|'no_show';
  checkIn: ISODate;
  checkOut: ISODate;
  adults: number;
  children: number;
  ratePlanId: RatePlanId;
  totalAmountMicro: bigint;
  currency: ISO4217;
  folioId?: FolioId;
  notes?: string;
  specialRequests?: string;
  source: 'guest'|'staff'|'ai_assisted';
  createdAt: ISODate;
  confirmedAt?: ISODate;
  checkedInAt?: ISODate;
  checkedOutAt?: ISODate;
  cancelledAt?: ISODate;
  cancellationReason?: string;
  vectorClock: VectorClock;                               // for sync
  version: number;
}

interface ReservationItem {
  id: ReservationItemId;
  tenantId: TenantId;
  reservationId: ReservationId;
  roomId: RoomId;
  roomTypeId: RoomTypeId;
  nightlyPriceMicro: bigint;
  numberOfNights: number;
  guestNames: string[];
}
CREATE TABLE reservations (
  id                  uuid PRIMARY KEY,
  tenant_id           uuid NOT NULL,
  property_id         uuid NOT NULL,
  guest_id            uuid NOT NULL,
  channel             text NOT NULL,
  state               text NOT NULL,
  check_in            date NOT NULL,
  check_out           date NOT NULL,
  adults              int NOT NULL CHECK (adults >= 1),
  children            int NOT NULL DEFAULT 0,
  rate_plan_id        uuid NOT NULL,
  total_amount_micro  bigint NOT NULL CHECK (total_amount_micro >= 0),
  currency            text NOT NULL,
  folio_id            uuid,
  notes               text,
  special_requests    text,
  source              text NOT NULL,
  created_at          timestamptz NOT NULL DEFAULT now(),
  confirmed_at        timestamptz,
  checked_in_at       timestamptz,
  checked_out_at      timestamptz,
  cancelled_at        timestamptz,
  cancellation_reason text,
  vector_clock        jsonb NOT NULL DEFAULT '{}'::jsonb,
  version             int NOT NULL DEFAULT 0,
  CHECK (check_out > check_in)
);
CREATE INDEX ix_reservations_tenant_property_dates ON reservations(tenant_id, property_id, check_in, check_out);
CREATE INDEX ix_reservations_state ON reservations(tenant_id, state);
CREATE INDEX ix_reservations_guest ON reservations(tenant_id, guest_id);
ALTER TABLE reservations ENABLE ROW LEVEL SECURITY;
ALTER TABLE reservations FORCE ROW LEVEL SECURITY;
CREATE POLICY reservations_tenant_isolation ON reservations
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE TABLE reservation_items (
  id                  uuid PRIMARY KEY,
  tenant_id           uuid NOT NULL,
  reservation_id      uuid NOT NULL REFERENCES reservations(id) ON DELETE CASCADE,
  room_id             uuid NOT NULL,
  room_type_id        uuid NOT NULL,
  nightly_price_micro bigint NOT NULL,
  number_of_nights    int NOT NULL CHECK (number_of_nights >= 1),
  guest_names         text[] NOT NULL DEFAULT '{}'
);
CREATE INDEX ix_reservation_items_reservation ON reservation_items(reservation_id);
ALTER TABLE reservation_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE reservation_items FORCE ROW LEVEL SECURITY;
CREATE POLICY reservation_items_tenant_isolation ON reservation_items
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

Invariants: State transitions are strictly held → confirmed → checked_in → checked_out with cancelled and no_show as terminal exits from held|confirmed. held reservations expire after RESERVATION_HOLD_TTL_SECONDS (default 600). total_amount_micro equals SUM(items.nightly_price_micro * items.number_of_nights) plus taxes from the folio.

Edge cases: Date change after confirmation triggers reservation.dates_changed.v1, which causes inventory-service to re-allocate (release old nights, allocate new) and lock-integration-service to re-issue the key credential. If new allocation fails, the date change is rejected and the original allocation stands.

4.8 Folio and FolioCharge (billing-service)​

Lives in the schema-per-tenant carve-out (tenant_<uuid>_billing). RLS is still on as defense in depth.

interface Folio {
  id: FolioId;
  tenantId: TenantId;
  reservationId: ReservationId;
  propertyId: PropertyId;
  state: 'open'|'partially_paid'|'paid'|'closed'|'in_dispute';
  currency: ISO4217;
  subtotalMicro: bigint;
  taxMicro: bigint;
  totalMicro: bigint;
  paidMicro: bigint;
  balanceMicro: bigint;                                  // total - paid (can be negative for refund credit)
  openedAt: ISODate;
  closedAt?: ISODate;
}

interface FolioCharge {
  id: FolioChargeId;
  tenantId: TenantId;
  folioId: FolioId;
  type: 'room'|'tax'|'service'|'minibar'|'damage'|'discount'|'adjustment'|'refund';
  description: I18nString;
  amountMicro: bigint;                                    // signed; negative for discounts/refunds
  taxRate?: number;
  postedAt: ISODate;
  postedBy: UserId;
  source: 'system'|'staff'|'ai_assisted';
  reversedByChargeId?: FolioChargeId;                     // append-only, no UPDATE; reversal is a new row
}
-- Inside tenant_<uuid>_billing schema:

CREATE TABLE folios (
  id                uuid PRIMARY KEY,
  tenant_id         uuid NOT NULL,
  reservation_id    uuid NOT NULL,
  property_id       uuid NOT NULL,
  state             text NOT NULL,
  currency          text NOT NULL,
  subtotal_micro    bigint NOT NULL DEFAULT 0,
  tax_micro         bigint NOT NULL DEFAULT 0,
  total_micro       bigint NOT NULL DEFAULT 0,
  paid_micro        bigint NOT NULL DEFAULT 0,
  balance_micro     bigint NOT NULL DEFAULT 0,
  opened_at         timestamptz NOT NULL DEFAULT now(),
  closed_at         timestamptz,
  UNIQUE (reservation_id)
);
CREATE INDEX ix_folios_property_state ON folios(property_id, state);

CREATE TABLE folio_charges (
  id                  uuid PRIMARY KEY,
  tenant_id           uuid NOT NULL,
  folio_id            uuid NOT NULL REFERENCES folios(id),
  type                text NOT NULL,
  description         jsonb NOT NULL,
  amount_micro        bigint NOT NULL,
  tax_rate            numeric(6,4),
  posted_at           timestamptz NOT NULL DEFAULT now(),
  posted_by           uuid NOT NULL,
  source              text NOT NULL,
  reversed_by_charge_id uuid
);
CREATE INDEX ix_folio_charges_folio_posted ON folio_charges(folio_id, posted_at);
-- Append-only enforcement:
CREATE RULE no_update_folio_charges AS ON UPDATE TO folio_charges DO INSTEAD NOTHING;
CREATE RULE no_delete_folio_charges AS ON DELETE TO folio_charges DO INSTEAD NOTHING;

Invariants: Charges are append-only; corrections are made by posting an offsetting charge and setting reversed_by_charge_id. folios.total_micro = subtotal + tax; balance = total - paid (re-derived after every charge or payment). A folio in paid state can transition to in_dispute but never back to open.

Edge cases: Currency conversions on cross-currency payments are recorded as a separate adjustment charge with the FX rate in description. Cash overpayment leaves a negative balance recorded for the next stay or refunded explicitly.

4.9 Payment and PaymentRefund (payment-gateway-service)​

Lives in the schema-per-tenant carve-out (tenant_<uuid>_payments). RLS as defense in depth.

interface Payment {
  id: PaymentId;
  tenantId: TenantId;
  intentId: PaymentIntentId;
  folioId: FolioId;
  rail: 'paypal'|'visa_debit'|'cash'|'mfs_easypaisa'|'mfs_jazzcash'|'bank_transfer';
  amountMicro: bigint;
  currency: ISO4217;
  status: 'pending'|'authorized'|'captured'|'failed'|'voided';
  providerToken?: string;                                  // gateway-side token; PAN never stored
  providerReference: string;                               // provider's transaction id
  capturedAt?: ISODate;
  failedAt?: ISODate;
  failureCode?: string;
  metadata: JSONValue;                                     // PII-redacted
  createdAt: ISODate;
}

interface PaymentRefund {
  id: RefundId;
  tenantId: TenantId;
  paymentId: PaymentId;
  amountMicro: bigint;
  currency: ISO4217;
  reason: 'cancellation'|'no_show_penalty_waived'|'damage_dispute'|'service_issue'|'goodwill'|'fraud';
  status: 'pending'|'completed'|'failed';
  initiatedBy: UserId;
  approvedBy?: UserId;                                     // for amounts > policy threshold
  providerReference?: string;
  createdAt: ISODate;
  completedAt?: ISODate;
}
-- Inside tenant_<uuid>_payments schema:

CREATE TABLE payments (
  id                 uuid PRIMARY KEY,
  tenant_id          uuid NOT NULL,
  intent_id          uuid NOT NULL,
  folio_id           uuid NOT NULL,
  rail               text NOT NULL,
  amount_micro       bigint NOT NULL CHECK (amount_micro > 0),
  currency           text NOT NULL,
  status             text NOT NULL,
  provider_token     text,
  provider_reference text NOT NULL,
  captured_at        timestamptz,
  failed_at          timestamptz,
  failure_code       text,
  metadata           jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at         timestamptz NOT NULL DEFAULT now(),
  UNIQUE (provider_reference, rail)
);
CREATE INDEX ix_payments_folio ON payments(folio_id);
CREATE INDEX ix_payments_status_created ON payments(status, created_at);

CREATE TABLE payment_refunds (
  id                 uuid PRIMARY KEY,
  tenant_id          uuid NOT NULL,
  payment_id         uuid NOT NULL REFERENCES payments(id),
  amount_micro       bigint NOT NULL CHECK (amount_micro > 0),
  currency           text NOT NULL,
  reason             text NOT NULL,
  status             text NOT NULL,
  initiated_by       uuid NOT NULL,
  approved_by        uuid,
  provider_reference text,
  created_at         timestamptz NOT NULL DEFAULT now(),
  completed_at       timestamptz
);
CREATE INDEX ix_refunds_payment ON payment_refunds(payment_id);

Invariants: PAN is never stored — only provider tokens. Sum of refunds for a payment never exceeds payments.amount_micro. Cash payments require metadata.received_by and metadata.location for reconciliation.

Edge cases: Idempotency: every capture call carries an Idempotency-Key; provider returning a duplicate token returns the original Payment row. Provider webhooks land via payment-gateway-service and update status once — duplicate webhooks are idempotent on (provider_reference, rail).

4.10 HousekeepingTask (housekeeping-service)​

interface HousekeepingTask {
  id: HousekeepingTaskId;
  tenantId: TenantId;
  propertyId: PropertyId;
  roomId: RoomId;
  reservationId?: ReservationId;                          // null for periodic deep-clean
  type: 'turnover'|'stayover'|'deep_clean'|'inspection'|'maintenance_followup';
  state: 'pending'|'assigned'|'in_progress'|'done'|'flagged'|'skipped';
  priority: 'low'|'normal'|'high'|'urgent';
  assignedTo?: StaffId;
  dueBy: ISODate;
  startedAt?: ISODate;
  completedAt?: ISODate;
  flags?: { kind: 'damage'|'missing_item'|'maintenance'|'safety'; note: string }[];
  vectorClock: VectorClock;
  version: number;
}
CREATE TABLE housekeeping_tasks (
  id              uuid PRIMARY KEY,
  tenant_id       uuid NOT NULL,
  property_id     uuid NOT NULL,
  room_id         uuid NOT NULL,
  reservation_id  uuid,
  type            text NOT NULL,
  state           text NOT NULL,
  priority        text NOT NULL DEFAULT 'normal',
  assigned_to     uuid,
  due_by          timestamptz NOT NULL,
  started_at      timestamptz,
  completed_at    timestamptz,
  flags           jsonb NOT NULL DEFAULT '[]'::jsonb,
  vector_clock    jsonb NOT NULL DEFAULT '{}'::jsonb,
  version         int NOT NULL DEFAULT 0
);
CREATE INDEX ix_hkt_property_state_due ON housekeeping_tasks(tenant_id, property_id, state, due_by);
CREATE INDEX ix_hkt_assigned ON housekeeping_tasks(assigned_to) WHERE state IN ('assigned','in_progress');
ALTER TABLE housekeeping_tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE housekeeping_tasks FORCE ROW LEVEL SECURITY;
CREATE POLICY hkt_tenant_isolation ON housekeeping_tasks
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

Invariants: A flagged task may transition to done only after the linked maintenance ticket (if any) closes. A task in in_progress requires started_at IS NOT NULL.

Edge cases: Conflict resolution per § 8.2 of the architecture doc: assignment uses LWW; completion is append-only; status uses worse-status-wins.

4.11 KeyCredential and LockDevice (lock-integration-service)​

interface KeyCredential {
  id: KeyCredentialId;
  tenantId: TenantId;
  reservationId: ReservationId;
  guestId: GuestId;
  roomIds: RoomId[];                                       // multi-room reservations get one credential covering all
  vendor: 'ttlock'|'salto'|'assa_abloy'|'generic_wiegand';
  vendorCredentialId: string;                               // opaque vendor-side id
  format: 'digital_pin'|'rfid_card'|'mobile_key';
  secretWrappedDek: string;                                 // KMS-wrapped DEK; secret material decrypted only in lock-integration-service
  validFrom: ISODate;
  validTo: ISODate;
  state: 'pending'|'issued'|'updated'|'revoked'|'suspended'|'expired';
  issuedAt?: ISODate;
  revokedAt?: ISODate;
  revokeReason?: 'checkout'|'cancellation'|'lost'|'stolen'|'date_change'|'admin_override';
  auditChain: { at: ISODate; actor: UserId|'system'; event: string; vendorRef?: string }[];
}

interface LockDevice {
  id: LockDeviceId;
  tenantId: TenantId;
  propertyId: PropertyId;
  roomId?: RoomId;                                         // null for common-area devices
  vendor: 'ttlock'|'salto'|'assa_abloy'|'generic_wiegand';
  vendorDeviceId: string;
  firmwareVersion?: string;
  lastSeenAt?: ISODate;
  battery?: number;                                          // 0..100
  status: 'online'|'offline'|'low_battery'|'tamper'|'error';
  installedAt: ISODate;
}
CREATE TABLE key_credentials (
  id                    uuid PRIMARY KEY,
  tenant_id             uuid NOT NULL,
  reservation_id        uuid NOT NULL,
  guest_id              uuid NOT NULL,
  room_ids              uuid[] NOT NULL,
  vendor                text NOT NULL,
  vendor_credential_id  text NOT NULL,
  format                text NOT NULL,
  secret_wrapped_dek    bytea NOT NULL,
  valid_from            timestamptz NOT NULL,
  valid_to              timestamptz NOT NULL,
  state                 text NOT NULL,
  issued_at             timestamptz,
  revoked_at            timestamptz,
  revoke_reason         text,
  audit_chain           jsonb NOT NULL DEFAULT '[]'::jsonb,
  CHECK (valid_to > valid_from)
);
CREATE INDEX ix_key_credentials_reservation ON key_credentials(reservation_id);
CREATE INDEX ix_key_credentials_state_valid_to ON key_credentials(state, valid_to);
ALTER TABLE key_credentials ENABLE ROW LEVEL SECURITY;
ALTER TABLE key_credentials FORCE ROW LEVEL SECURITY;
CREATE POLICY key_credentials_tenant_isolation ON key_credentials
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE TABLE lock_devices (
  id                uuid PRIMARY KEY,
  tenant_id         uuid NOT NULL,
  property_id       uuid NOT NULL,
  room_id           uuid,
  vendor            text NOT NULL,
  vendor_device_id  text NOT NULL,
  firmware_version  text,
  last_seen_at      timestamptz,
  battery           int CHECK (battery BETWEEN 0 AND 100),
  status            text NOT NULL,
  installed_at      timestamptz NOT NULL DEFAULT now(),
  UNIQUE (vendor, vendor_device_id)
);
CREATE INDEX ix_lock_devices_property ON lock_devices(tenant_id, property_id);
ALTER TABLE lock_devices ENABLE ROW LEVEL SECURITY;
ALTER TABLE lock_devices FORCE ROW LEVEL SECURITY;
CREATE POLICY lock_devices_tenant_isolation ON lock_devices
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

Invariants: A KeyCredential is server-authoritative for sync (clients cannot fabricate); audit chain is append-only. secret_wrapped_dek is decrypted only inside lock-integration-service using a KMS key restricted to that service's account. The DEK never leaves memory.

Edge cases: Date change re-issues a new credential and revokes the old one in a saga; on lost key, an immediate revoke + reissue saga executes; on early checkout, revoke is mandatory before reservation.checkout.v1 is accepted as final.

4.12 ThemeConfig (theme-config-service)​

interface ThemeConfig {
  id: ThemeId;
  tenantId: TenantId;
  propertyId?: PropertyId;                                // null = tenant-default; non-null = per-property override
  name: string;
  status: 'draft'|'published';
  tokens: {
    color: { primary: string; primaryFg: string; surface: string; surfaceFg: string; muted: string; danger: string; };
    typography: { fontFamilyHeading: string; fontFamilyBody: string; baseSize: number; };
    radius: { sm: number; md: number; lg: number; };
    spacing: { unit: number; };
    direction: 'ltr'|'rtl'|'auto';                         // auto follows locale
  };
  layoutPresets: { hero: string; rooms: string; about: string; footer: string };
  contentBlocks: { id: string; kind: 'about'|'policies'|'faq'|'custom'; markup: I18nMarkup }[];
  defaultLocale: Locale;
  enabledLocales: Locale[];
  publishedAt?: ISODate;
  publishedBy?: UserId;
  version: number;
  createdAt: ISODate;
  updatedAt: ISODate;
}
CREATE TABLE theme_configs (
  id              uuid PRIMARY KEY,
  tenant_id       uuid NOT NULL,
  property_id     uuid,
  name            text NOT NULL,
  status          text NOT NULL,
  tokens          jsonb NOT NULL,
  layout_presets  jsonb NOT NULL,
  content_blocks  jsonb NOT NULL DEFAULT '[]'::jsonb,
  default_locale  text NOT NULL,
  enabled_locales text[] NOT NULL,
  published_at    timestamptz,
  published_by    uuid,
  version         int NOT NULL DEFAULT 1,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_themes_tenant_property_status ON theme_configs(tenant_id, property_id, status);
ALTER TABLE theme_configs ENABLE ROW LEVEL SECURITY;
ALTER TABLE theme_configs FORCE ROW LEVEL SECURITY;
CREATE POLICY themes_tenant_isolation ON theme_configs
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

Invariants: Exactly one published theme per (tenant_id, property_id) (where property_id IS NULL represents the tenant default). Direction is auto unless explicitly overridden; auto resolves to rtl for ar, ps, fa, ur, he. enabled_locales must include default_locale.

Edge cases: Publishing a theme emits theme.tokens.changed.v1 and triggers BFF + CDN cache invalidation by version; clients fetch the new tokens lazily and re-render.

5. Desktop SQLite Schema​

The Electron desktop app keeps a subset of the cloud state, scoped to the staff member's tenant + property. The shape mirrors the Postgres schema where it overlaps; a few tables are desktop-only (outbox, inbox, sync_cursors, theme_cache, local_user_session, room_status_log).

5.1 SQLite-specific configuration​

PRAGMA journal_mode = WAL;          -- concurrent reads + a single writer
PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;        -- safe with WAL for single-writer
PRAGMA temp_store = MEMORY;
-- SQLCipher (if compiled in):
PRAGMA key = "x'<hex of device-derived key>'";
PRAGMA cipher_page_size = 4096;
PRAGMA kdf_iter = 256000;
  • Engine: better-sqlite3 (synchronous, fast, no callback overhead). Runs in the main Electron process; the renderer never touches the file.
  • Encryption: SQLCipher binding via @journeyapps/sqlcipher or the better-sqlite3 build linked against SQLCipher. Key is derived from (deviceKey ⊕ userPassphrase); deviceKey lives in the OS keychain via keytar. Loss of the passphrase forces re-pair (no recovery — this is intentional).
  • FTS: FTS5 virtual tables for guest name search, reservation note search, and content-block search.

5.2 Replicated tables (subset of Postgres)​

-- Reservations (full per-property scope, last 90 days + future)
CREATE TABLE reservations (
  id              text PRIMARY KEY,
  tenant_id       text NOT NULL,
  property_id     text NOT NULL,
  guest_id        text NOT NULL,
  channel         text NOT NULL,
  state           text NOT NULL,
  check_in        text NOT NULL,                    -- ISO date
  check_out       text NOT NULL,
  adults          integer NOT NULL,
  children        integer NOT NULL,
  rate_plan_id    text NOT NULL,
  total_amount_micro integer NOT NULL,
  currency        text NOT NULL,
  folio_id        text,
  notes           text,
  special_requests text,
  source          text NOT NULL,
  vector_clock    text NOT NULL,                    -- JSON
  version         integer NOT NULL,
  created_at      text NOT NULL,
  confirmed_at    text,
  checked_in_at   text,
  checked_out_at  text,
  cancelled_at    text,
  updated_at      text NOT NULL,
  synced_at       text                              -- last successful pull merge
);
CREATE INDEX ix_reservations_property_dates ON reservations(property_id, check_in, check_out);
CREATE INDEX ix_reservations_state ON reservations(state);

-- Rooms
CREATE TABLE rooms (
  id            text PRIMARY KEY,
  tenant_id     text NOT NULL,
  property_id   text NOT NULL,
  room_type_id  text NOT NULL,
  number        text NOT NULL,
  floor         integer,
  status        text NOT NULL,
  features      text NOT NULL,                       -- JSON array
  lock_device_id text,
  updated_at    text NOT NULL,
  synced_at     text
);
CREATE UNIQUE INDEX ux_rooms_property_number ON rooms(property_id, number);

-- Append-only room status changes (for offline auditability)
CREATE TABLE room_status_log (
  id            text PRIMARY KEY,
  tenant_id     text NOT NULL,
  room_id       text NOT NULL REFERENCES rooms(id),
  from_status   text NOT NULL,
  to_status     text NOT NULL,
  changed_by    text NOT NULL,
  changed_at    text NOT NULL,
  reason        text,
  pushed_at     text                                  -- when synced to cloud
);
CREATE INDEX ix_room_status_log_room ON room_status_log(room_id, changed_at);

-- Housekeeping
CREATE TABLE housekeeping_tasks (
  id            text PRIMARY KEY,
  tenant_id     text NOT NULL,
  property_id   text NOT NULL,
  room_id       text NOT NULL,
  reservation_id text,
  type          text NOT NULL,
  state         text NOT NULL,
  priority      text NOT NULL,
  assigned_to   text,
  due_by        text NOT NULL,
  started_at    text,
  completed_at  text,
  flags         text NOT NULL,                        -- JSON
  vector_clock  text NOT NULL,
  version       integer NOT NULL,
  updated_at    text NOT NULL,
  synced_at     text
);
CREATE INDEX ix_hkt_state_due ON housekeeping_tasks(state, due_by);

-- Snapshot of key credentials (read-only on desktop; lifecycle is server-authoritative)
CREATE TABLE key_credentials_snapshot (
  id              text PRIMARY KEY,
  tenant_id       text NOT NULL,
  reservation_id  text NOT NULL,
  vendor          text NOT NULL,
  format          text NOT NULL,
  valid_from      text NOT NULL,
  valid_to        text NOT NULL,
  state           text NOT NULL,
  issued_at       text,
  revoked_at      text,
  synced_at       text NOT NULL
);
CREATE INDEX ix_keycred_reservation ON key_credentials_snapshot(reservation_id);

-- Folio drafts (offline)
CREATE TABLE folio_drafts (
  id              text PRIMARY KEY,
  tenant_id       text NOT NULL,
  reservation_id  text NOT NULL,
  draft_payload   text NOT NULL,                       -- JSON: { charges:[…], totals: {…} }
  created_by      text NOT NULL,
  created_at      text NOT NULL,
  pushed_at       text
);

-- FTS for guest names + reservation notes
CREATE VIRTUAL TABLE reservations_fts USING fts5(
  reservation_id UNINDEXED,
  guest_name,
  notes,
  special_requests,
  tokenize = 'unicode61 remove_diacritics 2'
);

5.3 Desktop-only tables​

-- Outbox: local mutations awaiting push to /sync/v1/push
CREATE TABLE outbox (
  id                 text PRIMARY KEY,                  -- ULID
  client_mutation_id text NOT NULL UNIQUE,              -- idempotency
  tenant_id          text NOT NULL,
  aggregate          text NOT NULL,                     -- 'reservation' | 'room.status' | 'hkt' | 'folio_draft' | …
  aggregate_id       text NOT NULL,
  op                 text NOT NULL,                     -- 'create' | 'update' | 'append' | 'delete'
  payload            text NOT NULL,                     -- JSON
  base_version       integer,
  vector_clock       text NOT NULL,
  created_at         text NOT NULL,
  attempts           integer NOT NULL DEFAULT 0,
  last_attempt_at    text,
  last_error         text,
  pushed_at          text,
  ack_status         text                                -- 'accepted' | 'conflict' | 'rejected'
);
CREATE INDEX ix_outbox_unpushed ON outbox(pushed_at) WHERE pushed_at IS NULL;

-- Inbox: server deltas observed from /sync/v1/pull (for replay/audit)
CREATE TABLE inbox (
  id            text PRIMARY KEY,                       -- server eventId
  tenant_id     text NOT NULL,
  topic         text NOT NULL,
  payload       text NOT NULL,
  applied_at    text NOT NULL
);
CREATE INDEX ix_inbox_topic_applied ON inbox(topic, applied_at);

-- Sync cursors (per scope)
CREATE TABLE sync_cursors (
  scope         text PRIMARY KEY,                       -- e.g. 'reservation','room','hkt','keycred','theme'
  cursor        text NOT NULL,                          -- opaque server-issued
  last_pull_at  text NOT NULL,
  last_heartbeat_at text
);

-- Theme cache (current published theme + tokens, fetched on session start)
CREATE TABLE theme_cache (
  tenant_id     text NOT NULL,
  property_id   text NOT NULL,
  theme_id      text NOT NULL,
  version       integer NOT NULL,
  payload       text NOT NULL,                          -- JSON snapshot
  fetched_at    text NOT NULL,
  PRIMARY KEY (tenant_id, property_id)
);

-- Local user session (single-active-user model)
CREATE TABLE local_user_session (
  id              text PRIMARY KEY DEFAULT 'singleton',
  user_id         text NOT NULL,
  tenant_id       text NOT NULL,
  property_id     text,
  device_id       text NOT NULL,
  refresh_token_ref text NOT NULL,                       -- pointer to keytar entry, never the token itself
  last_active_at  text NOT NULL,
  CHECK (id = 'singleton')
);

Money fields stored as integer micro units in SQLite as well (SQLite's INTEGER is 64-bit). Same _micro suffix convention as Postgres.

6. Sync State Model (Firestore)​

Authoritative server-side device-sync state lives in Firestore. The Electron desktop maintains its own sync_cursors table; the cloud holds the authoritative copy in Firestore so a re-installed app can resume without resending its full outbox.

device_sync_state/<deviceId>
{
  deviceId: 'dev_01H...',
  tenantId: 'tnt_01H...',
  userId:   'usr_01H...',
  pairedAt: '2026-04-22T...Z',
  scopes: {
    reservation:  { lastCursor: 'cur_…', lastPullAt: '…', heartbeatAt: '…' },
    room:         { lastCursor: 'cur_…', lastPullAt: '…', heartbeatAt: '…' },
    hkt:          { lastCursor: 'cur_…', lastPullAt: '…', heartbeatAt: '…' },
    keycred:      { lastCursor: 'cur_…', lastPullAt: '…', heartbeatAt: '…' },
    theme:        { lastCursor: 'cur_…', lastPullAt: '…' }
  },
  lastPushBatch: {
    batchId: 'bat_01H…',
    submittedAt: '…',
    acceptedCount: 12,
    conflictCount: 1,
    rejectedCount: 0,
    lastResultAt: '…'
  },
  pendingConflictCount: 1,       -- materialized from sync.conflicts collection
  appVersion: '1.4.2',
  osPlatform: 'win32',
  status: 'active' | 'revoked' | 'deprecated_app'
}

sync.conflicts/<conflictId>
{
  conflictId: 'cnf_01H…',
  deviceId, tenantId, userId,
  aggregate: 'reservation' | 'hkt' | 'guest' | …,
  aggregateId,
  detectedAt: '…',
  resolution: 'auto-server' | 'auto-policy' | 'pending-human',
  serverState: { … },           -- snapshot at conflict time
  clientState: { … },
  policyApplied: 'server_authoritative' | 'lww' | 'append_only' | 'max-of',
  resolvedAt?, resolvedBy?
}

device_pairings/<pairingNonce>
{
  pairingNonce, tenantId, userId, expiresAt,
  consumedAt?, deviceId?
}

Why Firestore and not Postgres for these documents:

  • One-document-per-device updates dominate; Firestore's per-doc write semantics are perfect.
  • Regional, no instance management, no migration ceremony.
  • Cheap at the volumes involved (a single 50-property tenant has on the order of dozens of devices).

7. Embeddings & Vector Storage​

Embeddings live in pgvector inside the ai-orchestrator-service Postgres schema. Per-tenant namespacing is enforced by the same RLS pattern as the rest of the platform.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE embeddings_room_descriptions (
  id            uuid PRIMARY KEY,
  tenant_id     uuid NOT NULL,
  property_id   uuid NOT NULL,
  room_type_id  uuid NOT NULL,
  locale        text NOT NULL,
  source_hash   text NOT NULL,               -- SHA-256 of source text; embeddings invalidated on hash change
  embedding     vector(768) NOT NULL,        -- e.g. text-embedding-004 (Vertex AI)
  model         text NOT NULL,
  generated_at  timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, room_type_id, locale, model)
);
CREATE INDEX ix_emb_rooms_tenant ON embeddings_room_descriptions(tenant_id);
CREATE INDEX ix_emb_rooms_hnsw ON embeddings_room_descriptions
  USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);
ALTER TABLE embeddings_room_descriptions ENABLE ROW LEVEL SECURITY;
ALTER TABLE embeddings_room_descriptions FORCE ROW LEVEL SECURITY;
CREATE POLICY emb_rooms_tenant_isolation ON embeddings_room_descriptions
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE TABLE embeddings_review_summaries (
  id            uuid PRIMARY KEY,
  tenant_id     uuid NOT NULL,
  property_id   uuid NOT NULL,
  period_start  date NOT NULL,
  period_end    date NOT NULL,
  source_hash   text NOT NULL,
  embedding     vector(768) NOT NULL,
  model         text NOT NULL,
  generated_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_emb_reviews_tenant ON embeddings_review_summaries(tenant_id);
CREATE INDEX ix_emb_reviews_hnsw ON embeddings_review_summaries
  USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);

CREATE TABLE embeddings_search_queries (
  id            uuid PRIMARY KEY,
  tenant_id     uuid,                          -- null for cross-tenant meta queries
  query_text    text NOT NULL,
  query_locale  text NOT NULL,
  embedding     vector(384) NOT NULL,           -- smaller model, low-latency
  model         text NOT NULL,                  -- e.g. all-MiniLM-L6-v2 (edge or cloud)
  hit_count     int NOT NULL DEFAULT 1,
  last_used_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_emb_queries_hnsw ON embeddings_search_queries
  USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);

HNSW parameters (m=16, ef_construction=64) are a balanced default; tuned per index after observing recall in production. Query-time ef_search is set per call (SET LOCAL hnsw.ef_search = 40) based on recall vs latency trade-off.

Per-tenant namespacing. Every k-NN query includes a WHERE tenant_id = $1 predicate; the RLS policy enforces it as a second line of defense. The cross-tenant embeddings_search_queries table holds anonymous query embeddings only — no PII, no tenant linkage.

8. Read Models / Projections​

8.1 search-aggregation-service — cross-tenant search index​

A single hotels_search_index table (Postgres) plus an OpenSearch mirror for full-text + facets + geo. Built by event subscribers from property.created.v1, property.room.added.v1, inventory.allocated.v1, inventory.released.v1, pricing.calendar.updated.v1.

-- hotels_search_index lives in search-aggregation-service's schema; no RLS,
-- because this is the legitimate cross-tenant read store. Access is
-- gated by the bff-consumer-service only.

CREATE TABLE hotels_search_index (
  id                uuid PRIMARY KEY,         -- = property_id
  tenant_id         uuid NOT NULL,
  property_id       uuid NOT NULL,
  display_name      text NOT NULL,
  geo               geography(POINT, 4326) NOT NULL,
  city              text,
  country_code      text,
  star_rating       int,
  amenities         text[] NOT NULL,
  min_price_micro   bigint,
  max_price_micro   bigint,
  currency          text,
  available_today   boolean,
  next_open_date    date,
  hero_image_url    text,
  search_vector     tsvector,
  updated_at        timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_search_geo ON hotels_search_index USING GIST (geo);
CREATE INDEX ix_search_text ON hotels_search_index USING GIN (search_vector);
CREATE INDEX ix_search_city ON hotels_search_index (country_code, city);

OpenSearch index hotels-v1 mirrors the same shape with multilingual analyzers (pashto, dari, arabic, english, french).

8.2 analytics-service — BigQuery sink​

Pub/Sub → Cloud Function → BigQuery. One dataset per environment (melmastoon_analytics_prod), partitioned daily, clustered by tenant_id.

melmastoon_analytics_prod.bookings_fact
melmastoon_analytics_prod.folios_fact
melmastoon_analytics_prod.payments_fact
melmastoon_analytics_prod.housekeeping_fact
melmastoon_analytics_prod.lock_events_fact
melmastoon_analytics_prod.ai_calls_fact
melmastoon_analytics_prod.dim_property
melmastoon_analytics_prod.dim_tenant
melmastoon_analytics_prod.dim_user

Schema: each _fact table has tenant_id, event_id (idempotent dedup key), event_date (partition), payload_* typed columns, plus a payload_raw_json for backfills. Late-arriving events are merged on event_id.

9. Audit Log​

Two parallel sinks; both authoritative for their use case.

9.1 Per-service audit_events table​

CREATE TABLE audit_events (
  id              uuid PRIMARY KEY,
  tenant_id       uuid NOT NULL,
  actor_user_id   uuid,                      -- null for system events
  actor_kind      text NOT NULL,             -- 'user' | 'system' | 'integration'
  service         text NOT NULL,
  resource_type   text NOT NULL,
  resource_id     uuid NOT NULL,
  action          text NOT NULL,             -- 'create','update','delete','state_change'
  before_hash     text,                      -- SHA-256 of canonicalized JSON before
  after_hash      text,                      -- SHA-256 of canonicalized JSON after
  diff            jsonb,                     -- redacted diff; PII fields hashed
  request_id      uuid,
  trace_id        text,
  occurred_at     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_audit_tenant_occurred ON audit_events(tenant_id, occurred_at DESC);
CREATE INDEX ix_audit_resource ON audit_events(resource_type, resource_id);
ALTER TABLE audit_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_events FORCE ROW LEVEL SECURITY;
CREATE POLICY audit_tenant_isolation ON audit_events
  USING (tenant_id = current_setting('app.tenant_id')::uuid);
-- Append-only:
CREATE RULE no_update_audit_events AS ON UPDATE TO audit_events DO INSTEAD NOTHING;
CREATE RULE no_delete_audit_events AS ON DELETE TO audit_events DO INSTEAD NOTHING;

9.2 Centralized BigQuery melmastoon_audit_log​

Every audit_events row is also published to a Pub/Sub topic that fans out into the melmastoon_audit_log BigQuery dataset. The dataset is the source of truth for compliance queries, DSAR fulfilment, and the daily Merkle anchor.

audit-service runs a daily job that:

  1. Selects yesterday's audit_events rows from BigQuery,
  2. Computes a Merkle root over the canonicalized (tenant_id, occurred_at, after_hash) triples,
  3. Anchors the root to a public timestamp authority (RFC 3161),
  4. Persists the proof in melmastoon_audit_anchors.

Every mutation in every service is required to emit an audit event tagged with actor, tenant, and a (before_hash, after_hash) pair. CI fails any service that performs a state-changing mutation without a corresponding audit emission.

10. Encryption Posture​

ClassAt-restIn-flightNotes
Cloud SQL dataAES-256, CMEK via Cloud KMS (per-environment KEK; per-service DEK)TLS 1.3 from app to Cloud SQL Proxypg_tde not used; CMEK is sufficient for our model
Cloud Storage objectsAES-256, CMEKTLS 1.3; signed URLs scoped per tenant prefixLifecycle rules per object class
MemorystoreAt-rest by default; AUTH on; private IP onlyTLS 1.3 in-clusterNo PII cached
FirestoreAt-rest by defaultTLS 1.3No PII; only sync state
BigQueryCMEKTLS 1.3Audit dataset has separate KMS key
Pub/SubAt-rest by defaultTLS 1.3CMEK enabled on topics carrying PII
Application-level field encryption (PII)pgcrypto AES-256-GCM with KMS-wrapped DEK per record—Used for guest email, phone, ID document numbers stored long-term
Lock vendor secrets (secret_wrapped_dek)KMS-wrapped DEK; AES-GCM payload—Decrypted only inside lock-integration-service process memory
Payment provider tokensApplication-level AES-GCM under payment-gateway-service KMS key—PAN never persisted; only provider tokens
SQLite on desktopSQLCipher (AES-256-CBC) keyed by device-derived key—OS keychain holds the key fragment via keytar

KMS key hierarchy:

KMS keyring: melmastoon-<env>
├── kek-platform                 (rotated annually; wraps service DEKs)
├── kek-billing                  (separate key for finance/payments scope)
├── kek-lock-integration         (separate key for lock vendor credentials)
├── kek-cmek-cloudsql-<service>  (per-service CMEK on Cloud SQL)
└── kek-cmek-storage-<bucket>    (per-bucket CMEK on Cloud Storage)

DEK rotation is quarterly (rolled by background job that re-encrypts records in batches without downtime). KEKs rotate annually with grace overlap.

11. Soft Delete & GDPR Erasure​

11.1 Soft delete​

Tables that benefit from soft delete carry deleted_at timestamptz. Reads exclude rows where deleted_at IS NOT NULL by default. Soft-deleted rows are visible to authorized recovery flows for 30 days and then are eligible for hard purge.

Tables with soft delete: tenants, properties, room_types, rooms, rate_plans, guests, media_assets, theme_configs. Tables explicitly without soft delete: availability_slots, folio_charges, payments, payment_refunds, key_credentials.audit_chain rows, audit_events (all are append-only or financial — they get archived, not deleted).

11.2 GDPR erasure pipeline​

Triggered by tenant.guest.erasure_requested.v1 (raised by audit-service after intake validation):

  1. Verify the data subject's identity and the lawful basis for retention (financial records under tax-retention law are exempt from erasure but must be anonymized, not deleted).
  2. Fan out the erasure event to every owning service via Pub/Sub.
  3. Each service runs its erasure handler:
    • iam-service — anonymize users row (email → hashed pseudonym; password_hash null; webauthn revoked).
    • reservation-service — anonymize guests (name, contact); reservations themselves remain (financial trace) with guest_anonymized_at.
    • billing-service — replace billable contact info with pseudonyms; folios remain for the legally required retention window.
    • payment-gateway-service — purge any non-tokenized PII; provider tokens remain referenced for fraud history.
    • lock-integration-service — revoke all credentials; purge guest name from audit chain (replace with pseudonym).
    • notification-service — purge templates' personalization context; archive delivery records with hashed addresses.
    • analytics-service / BigQuery — overwrite PII columns with hashed pseudonyms in fact tables.
  4. Confirm by publishing audit.dsar.fulfilled.v1 with a Merkle proof of all erasure operations performed.

Tenant-wide erasure (account closure) follows the same pipeline plus the schema-per-tenant offboarding saga that pg_dumps and drops the tenant_<uuid>_billing and tenant_<uuid>_payments schemas.

12. Migration Strategy​

  • Tooling: Drizzle ORM migrations checked into each service under src/infrastructure/migrations/NNNN_<description>.sql (zero-padded ordinal). Drizzle's TypeScript schema is the single source of truth; drizzle-kit generate produces the SQL.
  • Application: Migrations run on container start via a leader-elected job; failed migrations fail the deploy. Cloud Run revisions are gated on migration success.
  • Pattern: expand-then-contract. Every breaking schema change is a sequence of three deploys:
    1. Expand — add the new column / table; both old and new code paths write; reads prefer new with fallback.
    2. Backfill — tenant-aware backfill job copies existing data into the new shape; idempotent and resumable; emits per-tenant progress events.
    3. Contract — remove the old column / path; mark the rollback impossible window; ship.
  • Tenant-aware backfills. Backfills iterate per tenant_id so a slow tenant cannot block others; checkpoints resume mid-tenant. Backfills run under a service account that elevates app.tenant_id per tenant explicitly (audited).
  • Schema-per-tenant migrations. billing-service and payment-gateway-service use the templated migration runner: each new migration is applied to every existing tenant_<uuid>_billing (or _payments) schema in a controlled batch. New tenants run the full chain at provisioning time.
  • Rollback discipline. A migration is reversible if and only if the previous deploy's code can read the new schema. The "contract" step is therefore irreversible and is gated by an explicit ADR or change ticket.
  • CI checks. A PR that touches *.sql migrations must include: a Drizzle schema diff, an explicit expand/contract label, an explanation of dual-write coverage, and (for tenant-scoped tables) an RLS-policy assertion. Missing any of these fails the PR.

Cross-references: per-service deep docs live in services/<service-name>/DATA_MODEL.md. Sync protocol details live in 02 Enterprise Architecture §8. Encryption + KMS specifics live in 07 Security & Tenancy. Vector indexing for AI lives in 08 AI Architecture.