property-service — DATA_MODEL

Companion: SERVICE_OVERVIEW · DOMAIN_MODEL · API_CONTRACTS · EVENT_SCHEMAS · SYNC_CONTRACT · SECURITY_MODEL · ../../docs/06-data-models.md · ../../docs/standards/NAMING.md

This document is the canonical Postgres + cache + projection schema for property-service. It defines tables, indexes, RLS policies, ID prefixes, integrity rules, soft-delete posture, audit shape, and the read models projected to downstream consumers (search, BFFs, desktop SQLite). When the service-bundle and the platform 06 Data Models disagree, the platform document is authoritative for cross-cutting decisions; this document is authoritative for the property domain shape.

1. Storage Strategy

Surface	Store	Why
Authoritative state	Cloud SQL Postgres 15 — schema `property`, RLS by `tenant_id`	Strong consistency; PostGIS for geo; mature ecosystem
Geo column	`geography(POINT, 4326)` via PostGIS 3.x	Bounding-box and great-circle queries with GIST index
Hot reads	Memorystore (Redis 7) — keys `prop:{tenantId}:{propertyId}`, `prop:{tenantId}:{propertyId}:rooms`, `prop:{tenantId}:{propertyId}:amenities`, `prop:bbox:{geohash5}`	Sub-ms reads for tenant-booking BFF and consumer meta BFF
Photos (binary)	Cloud Storage via `file-storage-service`	This service stores only `MediaRef` (id + variant + signed-url cache key)
Outbox	Same Postgres schema (`outbox` table)	Transactional with the aggregate write
Inbox / dedup	Same Postgres schema (`inbox` table)	Idempotent consumption of upstream events
Read model (search)	Projected via events to `search-aggregation-service`	Cross-tenant read store outside this service
Desktop replica	SQLite subset under `bff-backoffice-service` sync façade	Offline-first room status + room metadata

The schema is named property. All tables live inside it; cross-schema references are forbidden.

2. ID Strategy

All IDs are ULIDs prefixed with the service-chosen short code. Branded TypeScript types (declared in DOMAIN_MODEL §2) compile-time-enforce the type discipline; at runtime the storage column is text with a CHECK regex.

Prefix	Entity	Owning aggregate	Notes
`ppt_`	`Property`	Property	Globally unique slug per tenant
`rmt_`	`RoomType`	Property	Code unique per property
`rmu_`	`Room`	Property	Number unique per property
`pht_`	`Photo` (MediaRef)	Property / RoomType	References `med_` in `file-storage-service`
`pol_`	`Policy` (override map)	Property	One row per `(property_id, kind)`
`amn_`	`Amenity` (catalog ref)	platform-canonical registry	Codes are stable strings; row id is informational
`rgp_`	`RoomGroup`	Property	Floor / building / wing

ID format: <prefix>_<26-char Crockford ULID>. CHECK constraint: id ~ '^<prefix>_[0-9A-HJKMNP-TV-Z]{26}$'.

3. Tenant Isolation

Three layers; defense in depth.

Domain. Every aggregate constructor requires tenantId: TenantId. Cross-tenant references throw CrossTenantReferenceError before persistence.
Application. The RequestContextMiddleware reads the tenant_id claim from the verified JWT, asserts it matches X-Tenant-Id, and binds it via AsyncLocalStorage.
Database. Every connection executes SET LOCAL app.tenant_id = '<uuid>' after checkout. RLS policies on every multi-tenant table filter using current_setting('app.tenant_id')::uuid. Policies are FORCEd so the application service-account cannot bypass them.

Outbox events are gated: the writer asserts payload.tenantId == current_setting('app.tenant_id')::uuid inside the same transaction; mismatch raises MELMASTOON.GENERAL.CROSS_TENANT_REFERENCE and aborts.

4. Schema (DDL)

4.1 `properties`

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE property.properties (
  id              text PRIMARY KEY CHECK (id ~ '^ppt_[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id       uuid NOT NULL,
  slug            text NOT NULL,
  status          text NOT NULL CHECK (status IN ('draft','published','unpublished','archived')),
  star_rating     int CHECK (star_rating BETWEEN 1 AND 5),
  default_locale  text NOT NULL,
  enabled_locales text[] NOT NULL DEFAULT ARRAY['en']::text[],
  timezone        text NOT NULL,                                    -- IANA, e.g. 'Asia/Kabul'
  geo             geography(POINT, 4326),                            -- nullable until publish
  geo_source      text CHECK (geo_source IN ('manual','geocoded','ai_fallback')),
  contact         jsonb NOT NULL DEFAULT '{}'::jsonb,                -- { phone, email, web }
  hero_photo_id   text,                                               -- pht_…
  published_at    timestamptz,
  unpublished_at  timestamptz,
  archived_at     timestamptz,
  deleted_at      timestamptz,
  version         int NOT NULL DEFAULT 1,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, slug)
);

CREATE INDEX ix_properties_tenant_status   ON property.properties (tenant_id, status) WHERE deleted_at IS NULL;
CREATE INDEX ix_properties_geo_gist        ON property.properties USING GIST (geo)   WHERE status='published';
CREATE INDEX ix_properties_published_at    ON property.properties (tenant_id, published_at DESC) WHERE status='published';

ALTER TABLE property.properties ENABLE ROW LEVEL SECURITY;
ALTER TABLE property.properties FORCE  ROW LEVEL SECURITY;
CREATE POLICY properties_tenant_isolation ON property.properties
  USING      (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

4.2 `property_translations`

CREATE TABLE property.property_translations (
  property_id   text NOT NULL REFERENCES property.properties(id) ON DELETE CASCADE,
  tenant_id     uuid NOT NULL,
  locale        text NOT NULL,                                       -- BCP-47 (ps-AF, fa-IR, tg-TJ, en, ar, fr, ...)
  name          text NOT NULL,
  description   text,
  short_tagline text,
  address_line1 text NOT NULL,
  address_line2 text,
  native_script_line1 text,                                          -- Pashto/Dari/Persian/Tajik native script
  native_script_line2 text,
  city          text NOT NULL,
  region        text,
  country_code  text NOT NULL,                                       -- ISO 3166-1 alpha-2
  postal_code   text,
  PRIMARY KEY (property_id, locale)
);
CREATE INDEX ix_property_translations_tenant ON property.property_translations (tenant_id);
ALTER TABLE property.property_translations ENABLE ROW LEVEL SECURITY;
ALTER TABLE property.property_translations FORCE  ROW LEVEL SECURITY;
CREATE POLICY property_translations_tenant_isolation ON property.property_translations
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

Why a side table and not jsonb? Translations are queried per locale by the consumer meta BFF and projected into multilingual analyzers in search-aggregation-service (pashto, dari, arabic, english, french). A normalized table makes per-locale indexes possible and keeps the canonical row small.

4.3 `room_types`

CREATE TABLE property.room_types (
  id                text PRIMARY KEY CHECK (id ~ '^rmt_[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id         uuid NOT NULL,
  property_id       text NOT NULL REFERENCES property.properties(id) ON DELETE RESTRICT,
  code              text NOT NULL,                                   -- 'KING','TWIN','SUITE','FAMILY','DORM','APT'
  category          text NOT NULL CHECK (category IN ('king','twin','suite','family','dorm','apartment')),
  max_occupancy     int  NOT NULL CHECK (max_occupancy BETWEEN 1 AND 16),
  default_bed_config text NOT NULL CHECK (default_bed_config IN ('king','queen','twin','double','bunk','family','dorm-mixed','dorm-male','dorm-female')),
  size_sqm          numeric(6,2) CHECK (size_sqm IS NULL OR size_sqm > 0),
  amenities         text[] NOT NULL DEFAULT '{}',
  accessibility_flags text[] NOT NULL DEFAULT '{}',                  -- ['step_free','grab_rails','visual_alarm']
  archived_at       timestamptz,
  version           int NOT NULL DEFAULT 1,
  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now(),
  UNIQUE (property_id, code)
);
CREATE INDEX ix_room_types_tenant_property ON property.room_types (tenant_id, property_id) WHERE archived_at IS NULL;
ALTER TABLE property.room_types ENABLE ROW LEVEL SECURITY;
ALTER TABLE property.room_types FORCE  ROW LEVEL SECURITY;
CREATE POLICY room_types_tenant_isolation ON property.room_types
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

4.4 `room_type_translations`

CREATE TABLE property.room_type_translations (
  room_type_id text NOT NULL REFERENCES property.room_types(id) ON DELETE CASCADE,
  tenant_id    uuid NOT NULL,
  locale       text NOT NULL,
  name         text NOT NULL,
  description  text,
  PRIMARY KEY (room_type_id, locale)
);
ALTER TABLE property.room_type_translations ENABLE ROW LEVEL SECURITY;
ALTER TABLE property.room_type_translations FORCE  ROW LEVEL SECURITY;
CREATE POLICY rtt_tenant_isolation ON property.room_type_translations
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

4.5 `bed_configs` (per RoomType, multi-config support)

CREATE TABLE property.bed_configs (
  id           text PRIMARY KEY CHECK (id ~ '^bcf_[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id    uuid NOT NULL,
  room_type_id text NOT NULL REFERENCES property.room_types(id) ON DELETE CASCADE,
  label        text NOT NULL,                                          -- '1 King + Sofa Bed'
  beds         jsonb NOT NULL,                                          -- [{type:'king',count:1},{type:'sofa',count:1}]
  sleeps       int  NOT NULL CHECK (sleeps BETWEEN 1 AND 16),
  is_default   boolean NOT NULL DEFAULT false
);
CREATE INDEX ix_bed_configs_room_type ON property.bed_configs (room_type_id);
CREATE UNIQUE INDEX ux_bed_configs_default ON property.bed_configs (room_type_id) WHERE is_default;
ALTER TABLE property.bed_configs ENABLE ROW LEVEL SECURITY;
ALTER TABLE property.bed_configs FORCE  ROW LEVEL SECURITY;
CREATE POLICY bed_configs_tenant_isolation ON property.bed_configs
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

4.6 `rooms`

CREATE TABLE property.rooms (
  id              text PRIMARY KEY CHECK (id ~ '^rmu_[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id       uuid NOT NULL,
  property_id     text NOT NULL REFERENCES property.properties(id) ON DELETE RESTRICT,
  room_type_id    text NOT NULL REFERENCES property.room_types(id),
  number          text NOT NULL,                                       -- '101', '1A', 'Garden Suite'
  number_normalized text NOT NULL,                                     -- lowercased + trimmed
  floor           int,
  room_group_id   text REFERENCES property.room_groups(id) DEFERRABLE INITIALLY DEFERRED,
  status          text NOT NULL CHECK (status IN ('active','out_of_order','out_of_service','archived')),
  status_reason   text,                                                 -- e.g. 'plumbing_repair'
  status_changed_at timestamptz,
  status_changed_by text,                                               -- usr_… or 'system'
  features        text[] NOT NULL DEFAULT '{}',                         -- ['balcony','sea_view','garden_view']
  accessibility_flags text[] NOT NULL DEFAULT '{}',
  lock_device_id  text,                                                 -- lck_… set by lock-integration event
  notes           text,
  vector_clock    jsonb NOT NULL DEFAULT '{}'::jsonb,
  version         int NOT NULL DEFAULT 1,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now(),
  archived_at     timestamptz,
  UNIQUE (property_id, number_normalized)
);
CREATE INDEX ix_rooms_tenant_property_status ON property.rooms (tenant_id, property_id, status);
CREATE INDEX ix_rooms_room_type              ON property.rooms (room_type_id);
CREATE INDEX ix_rooms_lock_device            ON property.rooms (lock_device_id) WHERE lock_device_id IS NOT NULL;
ALTER TABLE property.rooms ENABLE ROW LEVEL SECURITY;
ALTER TABLE property.rooms FORCE  ROW LEVEL SECURITY;
CREATE POLICY rooms_tenant_isolation ON property.rooms
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

Room physical lifecycle (active / out_of_order / out_of_service / archived) lives here. Operational housekeeping state (clean / dirty / inspected) lives in housekeeping-service. Per-night availability lives in inventory-service. The three views are kept consistent via events, never via cross-service joins.

4.7 `room_groups`

CREATE TABLE property.room_groups (
  id           text PRIMARY KEY CHECK (id ~ '^rgp_[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id    uuid NOT NULL,
  property_id  text NOT NULL REFERENCES property.properties(id) ON DELETE CASCADE,
  kind         text NOT NULL CHECK (kind IN ('floor','wing','building','section')),
  label        text NOT NULL,
  sort_order   int NOT NULL DEFAULT 0,
  archived_at  timestamptz,
  UNIQUE (property_id, kind, label)
);
CREATE INDEX ix_room_groups_property ON property.room_groups (property_id);
ALTER TABLE property.room_groups ENABLE ROW LEVEL SECURITY;
ALTER TABLE property.room_groups FORCE  ROW LEVEL SECURITY;
CREATE POLICY room_groups_tenant_isolation ON property.room_groups
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

4.8 `amenities` (canonical) + `property_amenities`

-- Canonical amenity catalog. Not RLS-scoped — read by every tenant; write only by platform admin.
CREATE TABLE property.amenities (
  code         text PRIMARY KEY,                                         -- 'wifi','halal_kitchen','prayer_room',...
  category     text NOT NULL CHECK (category IN ('connectivity','food','wellness','accessibility','safety','family','utility','transport','religious','dorm','region')),
  default_label jsonb NOT NULL,                                          -- { "en": "...", "ps": "...", ... }
  icon_token   text,                                                     -- design-token name
  is_regional  boolean NOT NULL DEFAULT false,                           -- true for halal_kitchen, prayer_room, ...
  deprecated_at timestamptz
);

-- Per-property selection.
CREATE TABLE property.property_amenities (
  tenant_id    uuid NOT NULL,
  property_id  text NOT NULL REFERENCES property.properties(id) ON DELETE CASCADE,
  amenity_code text NOT NULL REFERENCES property.amenities(code),
  source       text NOT NULL CHECK (source IN ('staff','ai_suggested')),
  decision_id  text,                                                     -- dec_… for AI HITL acceptance
  set_at       timestamptz NOT NULL DEFAULT now(),
  set_by       text NOT NULL,                                            -- usr_… or 'system'
  PRIMARY KEY (property_id, amenity_code)
);
CREATE INDEX ix_property_amenities_tenant ON property.property_amenities (tenant_id);
CREATE INDEX ix_property_amenities_code   ON property.property_amenities (amenity_code);
ALTER TABLE property.property_amenities ENABLE ROW LEVEL SECURITY;
ALTER TABLE property.property_amenities FORCE  ROW LEVEL SECURITY;
CREATE POLICY pa_tenant_isolation ON property.property_amenities
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

Seeded canonical amenity codes (excerpt). wifi, parking, breakfast, pool, spa, gym, air_conditioning, heating, room_service, laundry, airport_shuttle, bus_pickup, family_room, kids_play_area, restaurant, bar, business_center, safe, elevator, regional: halal_kitchen, prayer_room, women_only_floor, male_dorm, female_dorm, mixed_dorm, generator_backup, hot_water_24h, hot_water_scheduled, borderless_payment, noise_insulation, step_free_access.

4.9 `photos`

CREATE TABLE property.photos (
  id            text PRIMARY KEY CHECK (id ~ '^pht_[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id     uuid NOT NULL,
  property_id   text NOT NULL REFERENCES property.properties(id) ON DELETE CASCADE,
  room_type_id  text REFERENCES property.room_types(id) ON DELETE CASCADE,   -- nullable: property-level if NULL
  media_asset_id text NOT NULL,                                                -- med_… in file-storage-service
  alt_text      jsonb NOT NULL DEFAULT '{}'::jsonb,                            -- I18nString
  tags          text[] NOT NULL DEFAULT '{}',                                  -- ['front_facade','room_interior',...]
  status        text NOT NULL CHECK (status IN ('uploaded','ready','quarantined','archived')),
  scan_result   jsonb,                                                          -- { verdict, scannedAt, scanner }
  width_px      int,
  height_px     int,
  bytes         int,
  sort_order    int NOT NULL DEFAULT 0,
  is_hero       boolean NOT NULL DEFAULT false,
  uploaded_by   text NOT NULL,
  uploaded_at   timestamptz NOT NULL DEFAULT now(),
  ai_provenance jsonb,                                                          -- present iff tags or alt_text were AI-suggested
  CHECK (room_type_id IS NULL OR sort_order >= 0)
);
CREATE INDEX ix_photos_property_sort ON property.photos (property_id, sort_order);
CREATE INDEX ix_photos_room_type     ON property.photos (room_type_id);
CREATE UNIQUE INDEX ux_photos_property_hero ON property.photos (property_id) WHERE is_hero;
ALTER TABLE property.photos ENABLE ROW LEVEL SECURITY;
ALTER TABLE property.photos FORCE  ROW LEVEL SECURITY;
CREATE POLICY photos_tenant_isolation ON property.photos
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

4.10 `policies` and `policy_overrides`

-- One row per property; the canonical set of resolved policy values.
CREATE TABLE property.policies (
  property_id     text PRIMARY KEY REFERENCES property.properties(id) ON DELETE CASCADE,
  tenant_id       uuid NOT NULL,
  resolved        jsonb NOT NULL DEFAULT '{}'::jsonb,    -- materialized merge of tenant defaults + property overrides
  resolved_at     timestamptz NOT NULL DEFAULT now(),
  version         int NOT NULL DEFAULT 1
);
ALTER TABLE property.policies ENABLE ROW LEVEL SECURITY;
ALTER TABLE property.policies FORCE  ROW LEVEL SECURITY;
CREATE POLICY policies_tenant_isolation ON property.policies
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

-- Per-kind overrides (sparse).
CREATE TABLE property.policy_overrides (
  property_id  text NOT NULL REFERENCES property.properties(id) ON DELETE CASCADE,
  tenant_id    uuid NOT NULL,
  kind         text NOT NULL CHECK (kind IN (
    'check_in_time','check_out_time','cancellation','child','smoking','pets','deposit','id_required','quiet_hours','prepayment','minimum_age'
  )),
  value        jsonb NOT NULL,
  set_by       text NOT NULL,
  set_at       timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (property_id, kind)
);
ALTER TABLE property.policy_overrides ENABLE ROW LEVEL SECURITY;
ALTER TABLE property.policy_overrides FORCE  ROW LEVEL SECURITY;
CREATE POLICY policy_overrides_tenant_isolation ON property.policy_overrides
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

Inheritance order: tenant defaults (in tenant-service) → property override (policy_overrides) → rate-plan override (pricing-service). The policies.resolved row is a materialized snapshot rebuilt on every override change or upstream tenant.settings.changed.v1.

4.11 `outbox`, `inbox`, `audit_events`

CREATE TABLE property.outbox (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id    uuid NOT NULL,
  topic        text NOT NULL,
  payload      jsonb NOT NULL,
  headers      jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at   timestamptz NOT NULL DEFAULT now(),
  published_at timestamptz,
  attempts     int NOT NULL DEFAULT 0
);
CREATE INDEX ix_outbox_unpublished ON property.outbox (created_at) WHERE published_at IS NULL;

CREATE TABLE property.inbox (
  message_id   text PRIMARY KEY,                       -- Pub/Sub messageId (idempotency key)
  topic        text NOT NULL,
  tenant_id    uuid NOT NULL,
  received_at  timestamptz NOT NULL DEFAULT now(),
  processed_at timestamptz
);
CREATE INDEX ix_inbox_processed ON property.inbox (processed_at) WHERE processed_at IS NULL;

CREATE TABLE property.audit_events (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id     uuid NOT NULL,
  actor_user_id text,
  actor_kind    text NOT NULL CHECK (actor_kind IN ('user','system','integration','ai')),
  resource_type text NOT NULL,
  resource_id   text NOT NULL,
  action        text NOT NULL,
  before_hash   text,
  after_hash    text,
  diff          jsonb,
  request_id    text,
  trace_id      text,
  occurred_at   timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_audit_tenant_time ON property.audit_events (tenant_id, occurred_at DESC);
ALTER TABLE property.audit_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE property.audit_events FORCE  ROW LEVEL SECURITY;
CREATE POLICY audit_tenant_isolation ON property.audit_events
  USING (tenant_id = current_setting('app.tenant_id')::uuid);
CREATE RULE no_update_audit AS ON UPDATE TO property.audit_events DO INSTEAD NOTHING;
CREATE RULE no_delete_audit AS ON DELETE TO property.audit_events DO INSTEAD NOTHING;

5. Cache Keys (Memorystore)

Key pattern	TTL	Invalidator
`prop:{tenantId}:{propertyId}`	60 s	`property.updated.v1`, `property.published.v1`, `property.unpublished.v1`
`prop:{tenantId}:{propertyId}:rooms`	30 s	`property.room.*.v1`
`prop:{tenantId}:{propertyId}:amenities`	300 s	`property.amenity_set.updated.v1`
`prop:{tenantId}:{propertyId}:photos`	60 s	`property.photo.*.v1`
`prop:bbox:{geohash5}:{filtersHash}`	30 s	`search.index.refreshed.v1` (cross-tenant)
`prop:nearby:{geohash6}:{radiusKm}`	30 s	same
`idempotency:{routeHash}:{tenantId}:{key}`	24 h	n/a

Memorystore namespace per tenant: keys are prefixed t:{tenantId}: to allow per-tenant FLUSHDB-equivalent on tenant offboarding.

6. Indexes Summary

Index	Purpose
`ix_properties_tenant_status`	Lists by tenant filtered by status (the dominant tenant-booking BFF read)
`ix_properties_geo_gist` (PostGIS GIST)	Bounding-box & nearby (great-circle) for `GET /properties/geo/*`
`ix_room_types_tenant_property`	RoomType list per property
`ix_rooms_tenant_property_status`	Room board, availability prefetch, OOO/OOS filters
`ix_rooms_lock_device`	Reverse lookup from `lock-integration-service` events
`ix_property_amenities_code`	Amenity-faceted search projection refresh
`ix_photos_property_sort`	Ordered photo galleries
`ux_photos_property_hero`	Exactly-one-hero invariant
`ix_outbox_unpublished`	Outbox publisher hot path
`ix_audit_tenant_time`	Audit drill-down by tenant

7. Read Models / Projections

7.1 `search-aggregation-service` projection

Built by event consumers (property.published.v1, property.updated.v1, property.amenity_set.updated.v1, property.photo.*.v1, property.room.created.v1, property.room.archived.v1). The projected document is the only cross-tenant view of property data.

{
  "id": "ppt_01H...",
  "tenantId": "tnt_01H...",
  "displayName": { "en": "...", "ps": "...", "fa": "...", "tg": "..." },
  "city": "Kabul",
  "countryCode": "AF",
  "geo": { "lat": 34.5553, "lng": 69.2075 },
  "starRating": 3,
  "amenities": ["wifi","halal_kitchen","prayer_room","generator_backup"],
  "heroImageUrl": "https://cdn.melmastoon.ghasi.io/...",
  "minPriceMicro": "12500000",
  "maxPriceMicro": "85000000",
  "currency": "AFN",
  "roomTypeCount": 5,
  "publishedAt": "2026-04-22T..."
}

7.2 Desktop SQLite mirror (subset)

Replicated under bff-backoffice-service /sync/v1/pull for the operator's tenant + property scope: properties, room_types, rooms, room_groups, policies.resolved. See SYNC_CONTRACT.md for per-aggregate conflict policy.

properties, room_types, rooms, room_groups, photos carry archived_at / deleted_at. Reads exclude them by default.
A soft-delete is reversible for 30 days, then eligible for a hard purge job that respects open obligations (open reservations referenced by inventory-service).
GDPR erasure requests do not delete property records (these are not personal data) but do strip uploaded photos that depict guests (operator must tag contains_guest_likeness=true at upload; the platform does not auto-detect this).
Tenant-wide closure (tenant.deleted.v1): cascade soft-delete then schedule hard purge after the legally required retention window.

9. Money & Locale Conventions

Currency never appears on properties directly; it is derived from the tenant-service setting at publish time and frozen on each pricing-service rate plan. (property-service does not own money.)
Locales stored as BCP-47 strings. Default tenant locale comes from tenant-service; per-property default_locale is allowed to differ (e.g., a tenant default en with a Kabul property ps-AF default).

10. Migration Conventions

Migrations live in services/property-service/src/infrastructure/migrations/NNNN_<description>.sql (zero-padded ordinal).
Every new table must include tenant_id, an RLS policy named <table>_tenant_isolation, and the _tenant_isolation.spec.ts integration test.
Every breaking schema change follows expand → backfill → contract (see MIGRATION_PLAN.md).

Cross-references: aggregate behaviour and invariants in DOMAIN_MODEL; REST shapes in API_CONTRACTS; event subjects + payloads in EVENT_SCHEMAS; RLS posture and field encryption in SECURITY_MODEL.

1. Storage Strategy​

2. ID Strategy​

3. Tenant Isolation​

4. Schema (DDL)​

4.1 properties​

4.2 property_translations​

4.3 room_types​

4.4 room_type_translations​

4.5 bed_configs (per RoomType, multi-config support)​

4.6 rooms​

4.7 room_groups​

4.8 amenities (canonical) + property_amenities​

4.9 photos​

4.10 policies and policy_overrides​

4.11 outbox, inbox, audit_events​

5. Cache Keys (Memorystore)​

6. Indexes Summary​

7. Read Models / Projections​

7.1 search-aggregation-service projection​

7.2 Desktop SQLite mirror (subset)​

8. Soft Delete & GDPR Erasure​

9. Money & Locale Conventions​

10. Migration Conventions​