housekeeping-service — DATA_MODEL

Storage: Cloud SQL Postgres 15 (regional HA), single shared schema housekeeping, tenant_id column on every row, RLS enforced. Outbox/inbox/audit colocated. Monthly partitioning on housekeeping_tasks.

This document is the canonical Postgres schema. DDL here MUST match migrations/*.sql in the service repo. Any drift is caught by tests/integration/db-schema.spec.ts.

1. ID prefixes

Aggregate	Prefix	Example
`HousekeepingTask`	`hkt_`	`hkt_01J0AB…`
`CleaningChecklist`	`chl_`	`chl_01J0AB…`
`Inspection`	`ins_`	`ins_01J0AB…`
`LinenInventory`	`lin_`	`lin_01J0AB…`
`LostAndFound`	`laf_`	`laf_01J0AB…`
`RoomBlock`	`blk_`	`blk_01J0AB…`
`StaffShiftAssignment`	`sft_`	`sft_01J0AB…`
`Outbox event`	`evt_`	`evt_01J0AB…`

All IDs are ULIDs with the prefix prepended (NAMING §6). Stored as TEXT NOT NULL.

2. Storage placement

Data	Where	Why
All aggregates	Postgres `housekeeping` schema	ACID with the outbox
Inspection photos	GCS via `media-service`	Object store, lifecycle policy
Tenant settings cache	In-process LRU (TTL 60 s); source of truth = `tenant-service`	Hot read
Property/room metadata	Read-through cache to `property-service` Firestore mirror	Hot read
Staff/shift metadata	Read-through cache to `staff-service`	Hot read
Audit	`audit_events` table here + fan-out to `audit-service`	Local fast access + central archive

3. Schema

CREATE SCHEMA IF NOT EXISTS housekeeping;
SET search_path = housekeeping, public;

-- Common helpers
CREATE OR REPLACE FUNCTION current_tenant_id() RETURNS TEXT AS $$
  SELECT current_setting('app.tenant_id', true)
$$ LANGUAGE SQL STABLE;

3.1 `housekeeping_tasks` (partitioned monthly)

CREATE TABLE housekeeping.housekeeping_tasks (
  id                   TEXT        NOT NULL,
  tenant_id            TEXT        NOT NULL,
  property_id          TEXT        NOT NULL,
  room_id              TEXT        NOT NULL,
  reservation_id       TEXT,
  kind                 TEXT        NOT NULL CHECK (kind IN
    ('turnover','mid_stay_clean','deep_clean','post_maintenance','post_renovation','inspection')),
  status               TEXT        NOT NULL CHECK (status IN
    ('pending','assigned','in_progress','paused','completed','failed','cancelled','requires_maintenance')),
  priority             TEXT        NOT NULL CHECK (priority IN ('low','normal','high','urgent')),
  assignee_staff_id    TEXT,
  checklist_id         TEXT,
  checklist_version    INTEGER,
  scheduled_for        TIMESTAMPTZ,
  started_at           TIMESTAMPTZ,
  paused_at            TIMESTAMPTZ,
  pause_reason         TEXT,
  completed_at         TIMESTAMPTZ,
  duration_minutes     INTEGER,
  failure_reason       TEXT,
  failure_note         TEXT,
  outcome_results      JSONB,                 -- ChecklistItemResult[]
  linen_issued         INTEGER,
  linen_returned       INTEGER,
  locale_hint          TEXT,
  source               TEXT        NOT NULL DEFAULT 'manual',
  source_event_id      TEXT,
  version              INTEGER     NOT NULL DEFAULT 1,
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Monthly partitions managed by pg_partman
SELECT partman.create_parent('housekeeping.housekeeping_tasks',
                             'created_at', 'native', 'monthly');

CREATE INDEX hkt_tenant_property_status_idx
  ON housekeeping.housekeeping_tasks (tenant_id, property_id, status, priority, scheduled_for);
CREATE INDEX hkt_tenant_room_open_idx
  ON housekeeping.housekeeping_tasks (tenant_id, room_id)
  WHERE status IN ('pending','assigned','in_progress','paused');
CREATE INDEX hkt_tenant_assignee_open_idx
  ON housekeeping.housekeeping_tasks (tenant_id, assignee_staff_id)
  WHERE status IN ('assigned','in_progress','paused');
CREATE INDEX hkt_reservation_idx
  ON housekeeping.housekeeping_tasks (tenant_id, reservation_id)
  WHERE reservation_id IS NOT NULL;

-- RLS
ALTER TABLE housekeeping.housekeeping_tasks ENABLE ROW LEVEL SECURITY;
CREATE POLICY hkt_tenant_isolation ON housekeeping.housekeeping_tasks
  USING (tenant_id = current_tenant_id())
  WITH CHECK (tenant_id = current_tenant_id());

Hot reads MUST include created_at >= … (partition pruning); the API derives this from scheduledFor/updatedSince filters.

3.2 `room_status` (singleton per room)

CREATE TABLE housekeeping.room_status (
  tenant_id        TEXT NOT NULL,
  property_id      TEXT NOT NULL,
  room_id          TEXT NOT NULL,
  status           TEXT NOT NULL CHECK (status IN
    ('clean','dirty','cleaning','cleaned','inspected','ready','out_of_order','out_of_service')),
  last_task_id     TEXT,
  last_flipped_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_flipped_by  JSONB        NOT NULL,    -- { type, id }
  last_cause       TEXT         NOT NULL,
  version          INTEGER      NOT NULL DEFAULT 1,
  PRIMARY KEY (tenant_id, property_id, room_id)
);
ALTER TABLE housekeeping.room_status ENABLE ROW LEVEL SECURITY;
CREATE POLICY rs_tenant_isolation ON housekeeping.room_status
  USING (tenant_id = current_tenant_id()) WITH CHECK (tenant_id = current_tenant_id());

CREATE INDEX rs_tenant_status_idx
  ON housekeeping.room_status (tenant_id, property_id, status);

3.3 `room_status_audit` (append-only)

CREATE TABLE housekeeping.room_status_audit (
  id              BIGSERIAL PRIMARY KEY,
  tenant_id       TEXT NOT NULL,
  property_id     TEXT NOT NULL,
  room_id         TEXT NOT NULL,
  previous_status TEXT NOT NULL,
  status          TEXT NOT NULL,
  cause           TEXT NOT NULL,
  task_id         TEXT,
  flipped_by      JSONB NOT NULL,
  flipped_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX rsa_tenant_room_idx
  ON housekeeping.room_status_audit (tenant_id, property_id, room_id, flipped_at DESC);
ALTER TABLE housekeeping.room_status_audit ENABLE ROW LEVEL SECURITY;
CREATE POLICY rsa_tenant_isolation ON housekeeping.room_status_audit
  USING (tenant_id = current_tenant_id()) WITH CHECK (tenant_id = current_tenant_id());

3.4 `cleaning_checklists` (versioned, immutable)

CREATE TABLE housekeeping.cleaning_checklists (
  id            TEXT NOT NULL,
  tenant_id     TEXT NOT NULL,
  kind          TEXT NOT NULL CHECK (kind IN
    ('turnover','mid_stay_clean','deep_clean','post_maintenance','post_renovation','inspection')),
  version       INTEGER NOT NULL,
  items         JSONB   NOT NULL,    -- ChecklistItem[]
  published_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  published_by  JSONB        NOT NULL,
  PRIMARY KEY (id),
  UNIQUE (tenant_id, kind, version)
);
ALTER TABLE housekeeping.cleaning_checklists ENABLE ROW LEVEL SECURITY;
CREATE POLICY cl_tenant_isolation ON housekeeping.cleaning_checklists
  USING (tenant_id = current_tenant_id()) WITH CHECK (tenant_id = current_tenant_id());

CREATE INDEX cl_tenant_kind_latest_idx
  ON housekeeping.cleaning_checklists (tenant_id, kind, version DESC);

3.5 `inspections`

CREATE TABLE housekeeping.inspections (
  id                  TEXT NOT NULL PRIMARY KEY,
  tenant_id           TEXT NOT NULL,
  task_id             TEXT NOT NULL,
  inspector_staff_id  TEXT NOT NULL,
  checklist_id        TEXT NOT NULL,
  checklist_version   INTEGER NOT NULL,
  results             JSONB NOT NULL,
  outcome             TEXT  NOT NULL CHECK (outcome IN ('pass','fail')),
  reason              TEXT,
  failed_item_keys    TEXT[],
  performed_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ins_task_idx ON housekeeping.inspections (tenant_id, task_id);
ALTER TABLE housekeeping.inspections ENABLE ROW LEVEL SECURITY;
CREATE POLICY ins_tenant_isolation ON housekeeping.inspections
  USING (tenant_id = current_tenant_id()) WITH CHECK (tenant_id = current_tenant_id());

3.6 `linen_inventory`

CREATE TABLE housekeeping.linen_inventory (
  id              TEXT NOT NULL PRIMARY KEY,
  tenant_id       TEXT NOT NULL,
  property_id     TEXT NOT NULL,
  line            TEXT NOT NULL,
  on_hand         INTEGER NOT NULL DEFAULT 0 CHECK (on_hand >= 0),
  low_watermark   INTEGER NOT NULL DEFAULT 0 CHECK (low_watermark >= 0),
  version         INTEGER NOT NULL DEFAULT 1,
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, property_id, line)
);
ALTER TABLE housekeeping.linen_inventory ENABLE ROW LEVEL SECURITY;
CREATE POLICY li_tenant_isolation ON housekeeping.linen_inventory
  USING (tenant_id = current_tenant_id()) WITH CHECK (tenant_id = current_tenant_id());

3.7 `linen_movements` (append-only)

CREATE TABLE housekeeping.linen_movements (
  id           BIGSERIAL PRIMARY KEY,
  tenant_id    TEXT NOT NULL,
  line_id      TEXT NOT NULL REFERENCES housekeeping.linen_inventory(id),
  task_id      TEXT,
  direction    TEXT NOT NULL CHECK (direction IN ('issue','return','adjust')),
  count        INTEGER NOT NULL,
  occurred_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  actor        JSONB NOT NULL
);
CREATE INDEX lm_line_idx ON housekeeping.linen_movements (tenant_id, line_id, occurred_at DESC);
ALTER TABLE housekeeping.linen_movements ENABLE ROW LEVEL SECURITY;
CREATE POLICY lm_tenant_isolation ON housekeeping.linen_movements
  USING (tenant_id = current_tenant_id()) WITH CHECK (tenant_id = current_tenant_id());

3.8 `lost_and_found`

CREATE TABLE housekeeping.lost_and_found (
  id                TEXT NOT NULL PRIMARY KEY,
  tenant_id         TEXT NOT NULL,
  property_id       TEXT NOT NULL,
  room_id           TEXT NOT NULL,
  reservation_id    TEXT,
  description       TEXT NOT NULL,
  photo_media_ids   TEXT[],
  storage_location  TEXT,
  finder_staff_id   TEXT NOT NULL,
  status            TEXT NOT NULL CHECK (status IN ('recorded','matched','disposed','returned'))
                    DEFAULT 'recorded',
  matched_at        TIMESTAMPTZ,
  matched_by_staff  TEXT,
  claimant_name     TEXT,
  claimant_phone    TEXT,
  disposed_at       TIMESTAMPTZ,
  dispose_method    TEXT,
  recorded_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  version           INTEGER NOT NULL DEFAULT 1
);
CREATE INDEX laf_tenant_status_idx ON housekeeping.lost_and_found
  (tenant_id, property_id, status, recorded_at DESC);
CREATE INDEX laf_reservation_idx ON housekeeping.lost_and_found
  (tenant_id, reservation_id) WHERE reservation_id IS NOT NULL;
ALTER TABLE housekeeping.lost_and_found ENABLE ROW LEVEL SECURITY;
CREATE POLICY laf_tenant_isolation ON housekeeping.lost_and_found
  USING (tenant_id = current_tenant_id()) WITH CHECK (tenant_id = current_tenant_id());

3.9 `room_blocks`

CREATE TABLE housekeeping.room_blocks (
  id           TEXT NOT NULL PRIMARY KEY,
  tenant_id    TEXT NOT NULL,
  property_id  TEXT NOT NULL,
  room_id      TEXT NOT NULL,
  reason       TEXT NOT NULL CHECK (reason IN ('cleaning','inspection','maintenance','oos_echo','oos')),
  owner        TEXT NOT NULL CHECK (owner IN ('housekeeping','maintenance','property')),
  blocked_until TIMESTAMPTZ,
  note         TEXT,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  cleared_at   TIMESTAMPTZ
);
CREATE INDEX rb_tenant_room_open_idx ON housekeeping.room_blocks
  (tenant_id, property_id, room_id) WHERE cleared_at IS NULL;
ALTER TABLE housekeeping.room_blocks ENABLE ROW LEVEL SECURITY;
CREATE POLICY rb_tenant_isolation ON housekeeping.room_blocks
  USING (tenant_id = current_tenant_id()) WITH CHECK (tenant_id = current_tenant_id());

3.10 `staff_shift_assignments` (read-side projection)

CREATE TABLE housekeeping.staff_shift_assignments (
  id                  TEXT NOT NULL PRIMARY KEY,
  tenant_id           TEXT NOT NULL,
  property_id         TEXT NOT NULL,
  staff_id            TEXT NOT NULL,
  shift_id            TEXT NOT NULL,
  shift_starts_at     TIMESTAMPTZ NOT NULL,
  shift_ends_at       TIMESTAMPTZ NOT NULL,
  capacity_minutes    INTEGER NOT NULL,
  languages           TEXT[],
  skills              TEXT[],
  active_task_count   INTEGER NOT NULL DEFAULT 0,
  load_minutes        INTEGER NOT NULL DEFAULT 0,
  ended_at            TIMESTAMPTZ
);
CREATE INDEX ssa_tenant_active_idx ON housekeeping.staff_shift_assignments
  (tenant_id, property_id, shift_starts_at, shift_ends_at) WHERE ended_at IS NULL;
ALTER TABLE housekeeping.staff_shift_assignments ENABLE ROW LEVEL SECURITY;
CREATE POLICY ssa_tenant_isolation ON housekeeping.staff_shift_assignments
  USING (tenant_id = current_tenant_id()) WITH CHECK (tenant_id = current_tenant_id());

3.11 `outbox`, `inbox`, `idempotency_keys`, `audit_events`

CREATE TABLE housekeeping.outbox (
  id            BIGSERIAL PRIMARY KEY,
  event_id      TEXT NOT NULL UNIQUE,
  tenant_id     TEXT NOT NULL,
  subject       TEXT NOT NULL,
  payload       JSONB NOT NULL,
  envelope_meta JSONB NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  published_at  TIMESTAMPTZ
);
CREATE INDEX outbox_unpublished_idx
  ON housekeeping.outbox (id) WHERE published_at IS NULL;

CREATE TABLE housekeeping.inbox (
  topic         TEXT NOT NULL,
  message_id    TEXT NOT NULL,
  tenant_id     TEXT NOT NULL,
  received_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (topic, message_id)
);

CREATE TABLE housekeeping.idempotency_keys (
  tenant_id    TEXT NOT NULL,
  route        TEXT NOT NULL,
  key          TEXT NOT NULL,
  response_hash TEXT NOT NULL,
  response     JSONB NOT NULL,
  status_code  INTEGER NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, route, key)
);

CREATE TABLE housekeeping.audit_events (
  id            BIGSERIAL PRIMARY KEY,
  tenant_id     TEXT NOT NULL,
  aggregate_id  TEXT NOT NULL,
  aggregate_kind TEXT NOT NULL,
  event_id      TEXT NOT NULL,
  subject       TEXT NOT NULL,
  payload       JSONB NOT NULL,
  occurred_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  actor         JSONB NOT NULL
);
CREATE INDEX audit_aggregate_idx ON housekeeping.audit_events
  (tenant_id, aggregate_kind, aggregate_id, occurred_at DESC);

RLS policies on outbox, inbox, idempotency_keys, audit_events mirror the others.

4. Read-side views

4.1 `room_blocks_view`

Composite view across our room_blocks rows and maintenance-service's rows (replicated via the inbox handler that maintains a local mirror table). Used by the board read.

4.2 `board_snapshot_mat` (refreshed on demand)

Materialised projection used by GET /board first paint. Refreshed:

on every room.status_changed.v1, task.*, linen_inventory change for the affected (tenant, property).
by a cron-ticker every 60 s as a safety net.

Backed by a per-(tenant, property) cached JSON in Redis (5-second TTL) for hot board polling; falls back to live SQL if cache miss.

5. Migration policy

node-pg-migrate with expand → contract in two PRs.
Backfills for partition-touching changes are run as Cloud Run Jobs with throttling (1k rows/s default).
See MIGRATION_PLAN.md.

6. Capacity & retention

Table	Retention	Notes
`housekeeping_tasks`	18 months hot, archive to GCS Parquet via export job	18-month-old partitions detached + dropped after export
`room_status_audit`	24 months	Partition by quarter (added later)
`inspections`	24 months	Photo refs only; photos lifecycled by `media-service`
`linen_movements`	12 months	Old months archived to GCS
`lost_and_found`	per tenant policy (default 12 months after `disposed_at`)
`room_blocks`	indefinite while open; cleared rows archived after 90 days
`outbox`	7 days hot (delete after publish + 7d)
`inbox`	30 days	Vacuumed weekly
`idempotency_keys`	24 hours	Cron job
`audit_events`	24 months hot, then GCS Parquet	Mirrored to `audit-service` real-time

Estimated row counts for a 50-room tenant doing 25 turnovers/day: 9k tasks/year, ~30k room-status-audit/year, ~9k inspections/year — well within Postgres comfort with monthly partitioning.

7. Tenant-isolation acceptance test

tests/integration/tenant-isolation.spec.ts runs every CI build:

SET app.tenant_id = 'tnt_A';
INSERT INTO housekeeping.housekeeping_tasks (...) VALUES (...);
SET app.tenant_id = 'tnt_B';
SELECT count(*) FROM housekeeping.housekeeping_tasks;     -- expect 0
UPDATE housekeeping.housekeeping_tasks SET priority='urgent';  -- expect 0 rows

8. Cross-link

DDL ↔ aggregates: DOMAIN_MODEL.md.
Outbox ↔ event subjects: EVENT_SCHEMAS.md.
RLS rationale: docs/07-security-compliance-tenancy.md.
Migration mechanics: MIGRATION_PLAN.md.

1. ID prefixes​

2. Storage placement​

3. Schema​

3.1 housekeeping_tasks (partitioned monthly)​

3.2 room_status (singleton per room)​

3.3 room_status_audit (append-only)​

3.4 cleaning_checklists (versioned, immutable)​

3.5 inspections​

3.6 linen_inventory​

3.7 linen_movements (append-only)​

3.8 lost_and_found​

3.9 room_blocks​

3.10 staff_shift_assignments (read-side projection)​

3.11 outbox, inbox, idempotency_keys, audit_events​

4. Read-side views​

4.1 room_blocks_view​

4.2 board_snapshot_mat (refreshed on demand)​

5. Migration policy​

6. Capacity & retention​

7. Tenant-isolation acceptance test​

8. Cross-link​