maintenance-service · DATA_MODEL

Cloud SQL Postgres 16 (regional, HA) is the authoritative store. Schema name: maintenance. Migrations via node-pg-migrate in services/maintenance-service/db/migrations/. All tenant-scoped tables enforce RLS on tenant_id.

1. Identity strategy

ULID-prefixed, lexically sortable, generated client-side in NestJS (deterministic per request via Nest's request-scoped IdGen):

Aggregate	Prefix	Notes
WorkOrder	mnt_	Aliased as MaintenanceTicket in NAMING.md; same physical column.
MaintenanceTask	mtk_
PreventiveSchedule	psch_
Asset	ast_
Part	prt_
PartUsage	pus_
Vendor	vnd_
MaintenanceCategory	mcat_
Outbox row	obx_

Add to platform NAMING.md registry (registered in this service's PR): mtk_, psch_, ast_, prt_, pus_, vnd_, mcat_. mnt_ already exists.

2. Storage placement

Concern	Where
Authoritative	Cloud SQL Postgres maintenance schema
Hot read cache (open WO list, vendor dropdown, asset health summary)	Memorystore Redis
Desktop replica (offline-first)	SQLite (Electron) — see SYNC_CONTRACT.md
Long-term event archive	BigQuery dataset melmastoon_events_v1.maintenance_*
Vendor invoice files	GCS bucket melmastoon-vendor-invoices/ (KMS-CMEK)
Schema definitions	GCS event-schemas/maintenance/*

3. Tables and DDL

3.1 maintenance.work_orders

CREATE TABLE maintenance.work_orders (
  id                       text         PRIMARY KEY,
  tenant_id                text         NOT NULL,
  property_id              text         NOT NULL,
  room_id                  text,
  asset_id                 text,
  category                 text         NOT NULL CHECK (category IN
                            ('plumbing','electrical','hvac','lock','generator','water','structural','it','other')),
  custom_category_id       text,
  severity                 text         NOT NULL CHECK (severity IN ('low','normal','high','critical')),
  title                    text         NOT NULL CHECK (length(title) <= 140),
  description              text         NOT NULL CHECK (length(description) <= 4096),
  source                   text         NOT NULL CHECK (source IN
                            ('manual_staff','guest_complaint','housekeeping_flag',
                             'lock_health_alert','preventive_schedule','reservation_relocation_failure')),
  origin_ref               text,
  status                   text         NOT NULL CHECK (status IN
                            ('open','assigned','in_progress','blocked','resolved','verified','cancelled')),
  assignee_kind            text         CHECK (assignee_kind IN ('staff','vendor')),
  assignee_user_id         text,
  assignee_vendor_id       text,
  caused_room_block        boolean      NOT NULL DEFAULT false,
  relocation_required      boolean      NOT NULL DEFAULT false,
  cost_currency            text         NOT NULL,
  cost_amount_micro        bigint       NOT NULL DEFAULT 0,
  sla_target_minutes       integer,
  sla_started_at           timestamptz,
  sla_due_at               timestamptz,
  sla_breached_at          timestamptz,
  sla_breach_count         integer      NOT NULL DEFAULT 0,
  vendor_ack_recorded_by   text,
  vendor_ack_channel       text,
  vendor_ack_note          text,
  vendor_ack_recorded_at   timestamptz,
  vendor_invoice_amount_micro bigint,
  vendor_invoice_currency  text,
  vendor_invoice_number    text,
  vendor_invoice_issued_at date,
  vendor_invoice_due_at    date,
  vendor_invoice_file_ref  text,
  vendor_invoice_posted_to_folio boolean NOT NULL DEFAULT false,
  reopen_count             integer      NOT NULL DEFAULT 0,
  verified_by              text,
  verified_at              timestamptz,
  cancelled_by             text,
  cancellation_reason      text,
  ai_provenance            jsonb        NOT NULL DEFAULT '[]'::jsonb,
  created_by               text         NOT NULL,
  created_at               timestamptz  NOT NULL DEFAULT now(),
  updated_at               timestamptz  NOT NULL DEFAULT now(),
  version                  integer      NOT NULL DEFAULT 1,
  CONSTRAINT chk_assignee_consistency
    CHECK ( (assignee_kind IS NULL AND assignee_user_id IS NULL AND assignee_vendor_id IS NULL)
         OR (assignee_kind = 'staff'  AND assignee_user_id  IS NOT NULL AND assignee_vendor_id IS NULL)
         OR (assignee_kind = 'vendor' AND assignee_vendor_id IS NOT NULL AND assignee_user_id   IS NULL)),
  CONSTRAINT chk_severity_target
    CHECK ( severity <> 'critical' OR (asset_id IS NOT NULL OR room_id IS NOT NULL) )
);

CREATE INDEX ix_work_orders_tenant_property_status
  ON maintenance.work_orders (tenant_id, property_id, status, created_at DESC);

CREATE INDEX ix_work_orders_tenant_property_severity_status
  ON maintenance.work_orders (tenant_id, property_id, severity, status);

CREATE INDEX ix_work_orders_tenant_room_status
  ON maintenance.work_orders (tenant_id, room_id, status)
  WHERE room_id IS NOT NULL;

CREATE INDEX ix_work_orders_tenant_asset_status
  ON maintenance.work_orders (tenant_id, asset_id, status)
  WHERE asset_id IS NOT NULL;

CREATE INDEX ix_work_orders_tenant_assignee_user
  ON maintenance.work_orders (tenant_id, assignee_user_id, status)
  WHERE assignee_user_id IS NOT NULL;

CREATE INDEX ix_work_orders_tenant_assignee_vendor
  ON maintenance.work_orders (tenant_id, assignee_vendor_id, status)
  WHERE assignee_vendor_id IS NOT NULL;

CREATE INDEX ix_work_orders_sla_due
  ON maintenance.work_orders (sla_due_at)
  WHERE status NOT IN ('resolved','verified','cancelled') AND sla_due_at IS NOT NULL;

CREATE UNIQUE INDEX ux_work_orders_one_open_per_asset_category
  ON maintenance.work_orders (tenant_id, asset_id, category)
  WHERE asset_id IS NOT NULL AND status NOT IN ('resolved','verified','cancelled');

ALTER TABLE maintenance.work_orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_work_orders_tenant ON maintenance.work_orders
  USING (tenant_id = current_setting('app.tenant_id'));

3.2 maintenance.maintenance_tasks

CREATE TABLE maintenance.maintenance_tasks (
  id              text         PRIMARY KEY,
  tenant_id       text         NOT NULL,
  work_order_id   text         NOT NULL REFERENCES maintenance.work_orders(id) ON DELETE CASCADE,
  seq             integer      NOT NULL,
  title           text         NOT NULL CHECK (length(title) <= 140),
  status          text         NOT NULL CHECK (status IN ('pending','in_progress','done','skipped')),
  note            text,
  completed_by    text,
  completed_at    timestamptz,
  created_at      timestamptz  NOT NULL DEFAULT now(),
  updated_at      timestamptz  NOT NULL DEFAULT now(),
  UNIQUE (work_order_id, seq)
);
ALTER TABLE maintenance.maintenance_tasks ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_maintenance_tasks_tenant ON maintenance.maintenance_tasks
  USING (tenant_id = current_setting('app.tenant_id'));

3.3 maintenance.preventive_schedules

CREATE TABLE maintenance.preventive_schedules (
  id                  text         PRIMARY KEY,
  tenant_id           text         NOT NULL,
  property_id         text         NOT NULL,
  asset_id            text,
  asset_class         text,
  category            text         NOT NULL,
  title               text         NOT NULL,
  cadence             jsonb        NOT NULL,
  sla_target_minutes  integer      NOT NULL,
  default_severity    text         NOT NULL,
  default_assignee    jsonb,
  notify_channel      text,
  active              boolean      NOT NULL DEFAULT true,
  next_due_at         timestamptz  NOT NULL,
  last_fired_at       timestamptz,
  version             integer      NOT NULL DEFAULT 1,
  created_at          timestamptz  NOT NULL DEFAULT now(),
  updated_at          timestamptz  NOT NULL DEFAULT now(),
  CONSTRAINT chk_schedule_target CHECK (asset_id IS NOT NULL OR asset_class IS NOT NULL)
);

CREATE INDEX ix_preventive_schedules_due
  ON maintenance.preventive_schedules (next_due_at)
  WHERE active = true;
CREATE INDEX ix_preventive_schedules_tenant_property
  ON maintenance.preventive_schedules (tenant_id, property_id, active);
ALTER TABLE maintenance.preventive_schedules ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_preventive_schedules_tenant ON maintenance.preventive_schedules
  USING (tenant_id = current_setting('app.tenant_id'));

3.4 maintenance.preventive_fires (idempotency)

CREATE TABLE maintenance.preventive_fires (
  schedule_id           text        NOT NULL,
  due_at_bucket_hour    timestamptz NOT NULL,
  fired_at              timestamptz NOT NULL DEFAULT now(),
  draft_work_order_id   text        NOT NULL,
  PRIMARY KEY (schedule_id, due_at_bucket_hour)
);

(Tenant scoping is implicit via FK on schedule_id; no RLS needed because writes are server-side only.)

3.5 maintenance.assets

CREATE TABLE maintenance.assets (
  id                       text        PRIMARY KEY,
  tenant_id                text        NOT NULL,
  property_id              text        NOT NULL,
  room_id                  text,
  class                    text        NOT NULL,
  display_name             text        NOT NULL,
  model                    text,
  manufacturer             text,
  serial_number            text,
  installed_at             date,
  last_serviced_at         date,
  run_hours                integer,
  capacity_value           numeric(12,3),
  capacity_unit            text,
  external_ref             text,
  health_index             integer     NOT NULL DEFAULT 100 CHECK (health_index BETWEEN 0 AND 100),
  health_index_updated_at  timestamptz,
  active                   boolean     NOT NULL DEFAULT true,
  version                  integer     NOT NULL DEFAULT 1,
  created_at               timestamptz NOT NULL DEFAULT now(),
  updated_at               timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_assets_tenant_property_class ON maintenance.assets (tenant_id, property_id, class, active);
CREATE INDEX ix_assets_tenant_room ON maintenance.assets (tenant_id, room_id) WHERE room_id IS NOT NULL;
CREATE UNIQUE INDEX ux_assets_external_ref ON maintenance.assets (tenant_id, external_ref) WHERE external_ref IS NOT NULL;
ALTER TABLE maintenance.assets ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_assets_tenant ON maintenance.assets
  USING (tenant_id = current_setting('app.tenant_id'));

3.6 maintenance.parts and maintenance.part_usages

CREATE TABLE maintenance.parts (
  id                  text        PRIMARY KEY,
  tenant_id           text        NOT NULL,
  property_id         text        NOT NULL,
  part_number         text        NOT NULL,
  display_name        text        NOT NULL,
  category            text        NOT NULL,
  on_hand             integer     NOT NULL DEFAULT 0 CHECK (on_hand >= 0),
  reorder_threshold   integer     NOT NULL DEFAULT 0,
  last_unit_cost_currency text,
  last_unit_cost_amount_micro bigint,
  active              boolean     NOT NULL DEFAULT true,
  version             integer     NOT NULL DEFAULT 1,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, property_id, part_number)
);
ALTER TABLE maintenance.parts ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_parts_tenant ON maintenance.parts
  USING (tenant_id = current_setting('app.tenant_id'));

CREATE TABLE maintenance.part_usages (
  id              text        PRIMARY KEY,
  tenant_id       text        NOT NULL,
  work_order_id   text        REFERENCES maintenance.work_orders(id) ON DELETE SET NULL,
  part_id         text        NOT NULL REFERENCES maintenance.parts(id),
  quantity        integer     NOT NULL CHECK (quantity > 0),
  unit_cost_currency       text   NOT NULL,
  unit_cost_amount_micro   bigint NOT NULL,
  total_cost_amount_micro  bigint NOT NULL,
  recorded_by     text        NOT NULL,
  recorded_at     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_part_usages_tenant_work_order ON maintenance.part_usages (tenant_id, work_order_id);
CREATE INDEX ix_part_usages_tenant_part_recorded ON maintenance.part_usages (tenant_id, part_id, recorded_at DESC);
ALTER TABLE maintenance.part_usages ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_part_usages_tenant ON maintenance.part_usages
  USING (tenant_id = current_setting('app.tenant_id'));

3.7 maintenance.vendors

CREATE TABLE maintenance.vendors (
  id                      text        PRIMARY KEY,
  tenant_id               text        NOT NULL,
  display_name            text        NOT NULL,
  categories              text[]      NOT NULL DEFAULT '{}',
  contact_name            text,
  phone_e164              text,
  email                   text,
  whatsapp_e164           text,
  channel_primary         text        NOT NULL CHECK (channel_primary IN ('whatsapp','sms','email','call_only')),
  channel_fallback        text        CHECK (channel_fallback IN ('whatsapp','sms','email')),
  address_free_text       text,
  callback_windows        jsonb       NOT NULL DEFAULT '[]'::jsonb,
  active                  boolean     NOT NULL DEFAULT true,
  version                 integer     NOT NULL DEFAULT 1,
  created_at              timestamptz NOT NULL DEFAULT now(),
  updated_at              timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT chk_vendor_at_least_one_contact
    CHECK (phone_e164 IS NOT NULL OR email IS NOT NULL OR whatsapp_e164 IS NOT NULL)
);
CREATE INDEX ix_vendors_tenant_active ON maintenance.vendors (tenant_id, active);
CREATE INDEX ix_vendors_tenant_categories_gin ON maintenance.vendors USING gin (categories);
ALTER TABLE maintenance.vendors ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_vendors_tenant ON maintenance.vendors
  USING (tenant_id = current_setting('app.tenant_id'));

3.8 maintenance.maintenance_categories

CREATE TABLE maintenance.maintenance_categories (
  id            text        PRIMARY KEY,
  tenant_id     text        NOT NULL,
  code          text        NOT NULL,
  display_name  text        NOT NULL,
  active        boolean     NOT NULL DEFAULT true,
  version       integer     NOT NULL DEFAULT 1,
  created_at    timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, code)
);
ALTER TABLE maintenance.maintenance_categories ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_maintenance_categories_tenant ON maintenance.maintenance_categories
  USING (tenant_id = current_setting('app.tenant_id'));

3.9 maintenance.outbox

CREATE TABLE maintenance.outbox (
  id              text        PRIMARY KEY,
  tenant_id       text        NOT NULL,
  topic           text        NOT NULL,
  partition_key   text        NOT NULL,
  payload         jsonb       NOT NULL,
  envelope        jsonb       NOT NULL,
  correlation_id  text        NOT NULL,
  causation_id    text,
  enqueued_at     timestamptz NOT NULL DEFAULT now(),
  published_at    timestamptz,
  attempts        integer     NOT NULL DEFAULT 0,
  last_error      text
);
CREATE INDEX ix_outbox_unpublished ON maintenance.outbox (enqueued_at) WHERE published_at IS NULL;
CREATE INDEX ix_outbox_tenant ON maintenance.outbox (tenant_id, enqueued_at);

3.10 maintenance.inbox_processed

CREATE TABLE maintenance.inbox_processed (
  message_id      text        PRIMARY KEY,
  event_type      text        NOT NULL,
  tenant_id       text        NOT NULL,
  correlation_id  text,
  processed_at    timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_inbox_tenant_processed_at ON maintenance.inbox_processed (tenant_id, processed_at DESC);

3.11 maintenance.idempotency_keys

CREATE TABLE maintenance.idempotency_keys (
  tenant_id       text        NOT NULL,
  route           text        NOT NULL,
  key             text        NOT NULL,
  request_hash    text        NOT NULL,
  response_status integer     NOT NULL,
  response_body   jsonb       NOT NULL,
  created_at      timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, route, key)
);
CREATE INDEX ix_idempotency_keys_created_at ON maintenance.idempotency_keys (created_at);  -- for TTL sweeper

4. RLS application

The Cloud Run service sets SET LOCAL app.tenant_id = '<tnt_xxx>' at the start of every request transaction. Workers iterate per tenant explicitly. The outbox, inbox_processed, preventive_fires, and idempotency_keys tables are server-side only (no end-user RLS needed) but still carry tenant_id for analytics/cleanup.

5. Retention

Data	Retention	Deletion mechanism
work_orders (closed)	24 months hot, then archive to BigQuery	Nightly archiver job moves rows ≥ 24 mo old
maintenance_tasks	follows parent WO	cascade
part_usages	7 years (regulated; cost data)	annual archive to BigQuery
outbox	rows pruned after published_at + 7 days	hourly sweeper
inbox_processed	30 days	hourly sweeper
idempotency_keys	24 hours	hourly sweeper
preventive_fires	90 days	hourly sweeper
vendor_invoice_files (GCS)	7 years (regulated)	bucket lifecycle

6. Performance & sizing

• Expected row counts at Phase 1 scale: work_orders ~6M (24 mo hot), assets ~200k, vendors ~50k, parts ~500k, part_usages ~10M.
• Largest hot index: ix_work_orders_tenant_property_status (~600 MB at full scale).
• pgvector not used in this service in v1; reserved for AI severity-suggestion embeddings in v2.
• Connection pool: PgBouncer (transaction mode), max 100 connections per Cloud Run instance, 50 effective.

7. Migration patterns

• All migrations are expand-then-contract.
• New columns: nullable + default in the expand step; backfill in a worker; tighten in a later contract step.
• New CHECK constraints: NOT VALID first, validate online.
• Index creation: CONCURRENTLY always.
• See MIGRATION_PLAN.md for legacy data import.

8. Backups & disaster recovery

• Cloud SQL automated backups: daily full + 7-day PITR.
• Cross-region replica in europe-west4 (read-only) with promotion runbook.
• RTO 30 min, RPO 5 min (matches platform standard).