tenant-service — DATA_MODEL

Companion: DOMAIN_MODEL · APPLICATION_LOGIC · SECURITY_MODEL · Platform: 06 Data Models · 07 Security & Tenancy

This document is the source of truth for the persistent shape of tenant-service. Schema is materialized in Cloud SQL Postgres 16, schema name tenant. Every aggregate maps to one or more relational tables. Row-Level Security is mandatory on every tenant-scoped table; the tenants table itself is platform-scoped.

1. Database Topology

Property	Value
Engine	Cloud SQL Postgres 16, regional HA, PITR 14 days
Schema	`tenant`
Extensions	`ltree`, `pgcrypto`, `pg_trgm`, `pgaudit`, `uuid-ossp`
Connection pool	PgBouncer (`transaction` mode), 20 client conns per Cloud Run instance
Replication	Synchronous standby (HA); async read replica `tenant-ro` for query handlers
Backup	Daily snapshot to GCS (CMEK), 35 day retention; PITR enabled
Encryption	At rest: CMEK via Cloud KMS, key `projects/melmastoon-prod/locations/global/keyRings/data/cryptoKeys/tenant-db`; in transit: TLS 1.3 only

2. Entity Relationship Diagram

                     +------------------+
                     |     tenants      |   (platform-scoped, no RLS row policy)
                     +------------------+
                              |
            +-----------------+-----------------+-----------------+--------------------+
            |                 |                 |                 |                    |
            v                 v                 v                 v                    v
   +-----------------+ +---------------+ +-----------------+ +----------------+ +-------------------+
   | tenant_configs  | |  org_units    | |  memberships    | | billing_contacts| | feature_flag_     |
   | (1:1)           | |  (ltree tree) | |  (1:N)          | | (1:1)           | | overrides (1:N)   |
   +-----------------+ +---------------+ +-----------------+ +----------------+ +-------------------+
                              |                 |
                              |                 +--------+
                              |                          |
                              v                          v
                     +-------------------+       +---------------------+
                     | role_assignments  |<----->|       roles         |
                     +-------------------+       +---------------------+
                              ^                          ^
                              |                          |
                              |                  +-------------------+
                              |                  | role_permissions  |
                              |                  +-------------------+
                              |
                     +-------------------+
                     |    invitations    |
                     +-------------------+

   +------------------+   +-----------------+
   |   audit_events   |   |     outbox      |
   +------------------+   +-----------------+

3. DDL — Tables

3.1 `tenants`

CREATE TABLE tenant.tenants (
  id                TEXT PRIMARY KEY                         -- 'tnt_<ULID>'
                    CHECK (id ~ '^tnt_[0-9A-HJKMNP-TV-Z]{26}$'),
  slug              TEXT NOT NULL UNIQUE
                    CHECK (slug ~ '^[a-z][a-z0-9-]{2,30}[a-z0-9]$'),
  legal_name        TEXT NOT NULL,
  country           CHAR(2) NOT NULL CHECK (country ~ '^[A-Z]{2}$'),
  residency_region  TEXT NOT NULL CHECK (residency_region IN ('me-central1','asia-south1','europe-west1')),
  status            TEXT NOT NULL CHECK (status IN ('pending','active','suspended','closed')),
  plan_ref          TEXT,
  suspension_reason TEXT,
  closed_at         TIMESTAMPTZ,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  version           BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX ix_tenants_status     ON tenant.tenants(status);
CREATE INDEX ix_tenants_residency  ON tenant.tenants(residency_region);

-- No RLS policy: only the platform service account (`platform.super_admin` JWT)
-- bypasses RLS via 'SET LOCAL row_security = off'. Direct reads from operator
-- staff happen through the BFF, which re-projects the row.

3.2 `tenant_configs`

CREATE TABLE tenant.tenant_configs (
  id                            TEXT PRIMARY KEY CHECK (id ~ '^tcg_'),
  tenant_id                     TEXT NOT NULL REFERENCES tenant.tenants(id) ON DELETE RESTRICT,
  currencies                    TEXT[] NOT NULL CHECK (cardinality(currencies) >= 1),
  locales                       JSONB  NOT NULL,         -- [{value:'fa-AF', isRtl:true}, ...]
  time_zone                     TEXT   NOT NULL,
  tax_model                     JSONB  NOT NULL,         -- {inclusive, defaultRateBasisPoints, registrationNumber}
  default_check_in              TEXT   NOT NULL CHECK (default_check_in ~ '^([01][0-9]|2[0-3]):[0-5][0-9]$'),
  default_check_out             TEXT   NOT NULL CHECK (default_check_out ~ '^([01][0-9]|2[0-3]):[0-5][0-9]$'),
  breakfast_included_default    BOOLEAN NOT NULL DEFAULT false,
  smoking_policy                TEXT NOT NULL CHECK (smoking_policy IN ('allowed','designated','forbidden')),
  child_policy                  JSONB NOT NULL,
  cancellation_default          JSONB NOT NULL,
  business_hours                JSONB,
  version                       BIGINT NOT NULL DEFAULT 1,
  updated_by                    TEXT NOT NULL,
  updated_at                    TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id)
);

ALTER TABLE tenant.tenant_configs ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_configs ON tenant.tenant_configs
  USING (tenant_id = current_setting('app.tenant_id', true));

3.3 `organization_units`

CREATE TABLE tenant.organization_units (
  id            TEXT PRIMARY KEY CHECK (id ~ '^org_'),
  tenant_id     TEXT NOT NULL REFERENCES tenant.tenants(id) ON DELETE RESTRICT,
  kind          TEXT NOT NULL CHECK (kind IN ('chain','region','property')),
  parent_id     TEXT REFERENCES tenant.organization_units(id),
  path          LTREE NOT NULL,
  name          TEXT NOT NULL,
  property_id   TEXT,                                                   -- 'ppt_<ULID>'; only when kind='property'
  archived      BOOLEAN NOT NULL DEFAULT false,
  version       BIGINT NOT NULL DEFAULT 1,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (nlevel(path) <= 5),
  CHECK ((kind = 'property' AND property_id IS NOT NULL) OR (kind <> 'property' AND property_id IS NULL))
);

CREATE UNIQUE INDEX ux_org_root_per_tenant   ON tenant.organization_units(tenant_id) WHERE parent_id IS NULL;
CREATE UNIQUE INDEX ux_org_property_per_tnt  ON tenant.organization_units(tenant_id, property_id) WHERE property_id IS NOT NULL;
CREATE INDEX ix_org_path_gist                ON tenant.organization_units USING GIST (path);
CREATE INDEX ix_org_tenant_kind              ON tenant.organization_units(tenant_id, kind);

ALTER TABLE tenant.organization_units ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_org ON tenant.organization_units
  USING (tenant_id = current_setting('app.tenant_id', true));

3.4 `memberships`

CREATE TABLE tenant.memberships (
  id              TEXT PRIMARY KEY CHECK (id ~ '^mbr_'),
  tenant_id       TEXT NOT NULL REFERENCES tenant.tenants(id) ON DELETE RESTRICT,
  user_id         TEXT NOT NULL,                                        -- 'usr_<ULID>' from iam-service
  status          TEXT NOT NULL CHECK (status IN ('pending','active','suspended','removed')),
  display_name    TEXT NOT NULL,
  property_scope  TEXT[] NOT NULL DEFAULT '{}',                         -- org_<ULID>[] of kind='property'
  invited_by      TEXT,
  invited_at      TIMESTAMPTZ,
  joined_at       TIMESTAMPTZ,
  removed_at      TIMESTAMPTZ,
  version         BIGINT NOT NULL DEFAULT 1,
  UNIQUE (tenant_id, user_id)
);

CREATE INDEX ix_memberships_user        ON tenant.memberships(user_id);
CREATE INDEX ix_memberships_status      ON tenant.memberships(tenant_id, status);
CREATE INDEX ix_memberships_propscope   ON tenant.memberships USING GIN (property_scope);

ALTER TABLE tenant.memberships ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_mbr ON tenant.memberships
  USING (tenant_id = current_setting('app.tenant_id', true));

3.5 `roles` and `role_permissions`

CREATE TABLE tenant.roles (
  id            TEXT PRIMARY KEY CHECK (id ~ '^rol_'),
  tenant_id     TEXT REFERENCES tenant.tenants(id) ON DELETE RESTRICT,  -- NULL ⇒ platform-template
  code          TEXT NOT NULL,                                          -- 'tenant.owner', 'tenant.gm', ...
  display_name  TEXT NOT NULL,
  is_system     BOOLEAN NOT NULL DEFAULT false,
  version       BIGINT NOT NULL DEFAULT 1,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX ux_roles_code_per_tenant ON tenant.roles(tenant_id, code);
CREATE INDEX ix_roles_system                 ON tenant.roles(is_system);

CREATE TABLE tenant.role_permissions (
  role_id    TEXT NOT NULL REFERENCES tenant.roles(id) ON DELETE CASCADE,
  resource   TEXT NOT NULL,
  action     TEXT NOT NULL,
  PRIMARY KEY (role_id, resource, action)
);

CREATE INDEX ix_role_perms_resource ON tenant.role_permissions(resource, action);

ALTER TABLE tenant.roles ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_role ON tenant.roles
  USING (tenant_id IS NULL OR tenant_id = current_setting('app.tenant_id', true));

3.6 `role_assignments`

CREATE TABLE tenant.role_assignments (
  id              TEXT PRIMARY KEY CHECK (id ~ '^rla_'),
  tenant_id       TEXT NOT NULL REFERENCES tenant.tenants(id) ON DELETE RESTRICT,
  membership_id   TEXT NOT NULL REFERENCES tenant.memberships(id) ON DELETE CASCADE,
  role_id         TEXT NOT NULL REFERENCES tenant.roles(id) ON DELETE RESTRICT,
  property_scope  TEXT[] NOT NULL DEFAULT '{}',
  granted_by      TEXT NOT NULL,
  granted_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  revoked_at      TIMESTAMPTZ,
  version         BIGINT NOT NULL DEFAULT 1,
  UNIQUE (membership_id, role_id, property_scope)
);

CREATE INDEX ix_rla_tenant_role ON tenant.role_assignments(tenant_id, role_id) WHERE revoked_at IS NULL;
CREATE INDEX ix_rla_membership  ON tenant.role_assignments(membership_id) WHERE revoked_at IS NULL;

ALTER TABLE tenant.role_assignments ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_rla ON tenant.role_assignments
  USING (tenant_id = current_setting('app.tenant_id', true));

3.7 `invitations`

CREATE TABLE tenant.invitations (
  id              TEXT PRIMARY KEY CHECK (id ~ '^inv_'),
  tenant_id       TEXT NOT NULL REFERENCES tenant.tenants(id) ON DELETE RESTRICT,
  email           CITEXT NOT NULL,
  token_hash      TEXT NOT NULL,                          -- sha256 hex (64 chars)
  status          TEXT NOT NULL CHECK (status IN ('pending','accepted','expired','revoked')),
  roles_proposed  TEXT[] NOT NULL DEFAULT '{}',
  property_scope  TEXT[] NOT NULL DEFAULT '{}',
  invited_by      TEXT NOT NULL,
  invited_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at      TIMESTAMPTZ NOT NULL,
  accepted_at     TIMESTAMPTZ,
  accepted_by     TEXT,
  attempt_count   INT NOT NULL DEFAULT 1,
  CHECK (expires_at > invited_at)
);

CREATE UNIQUE INDEX ux_invite_pending_email ON tenant.invitations(tenant_id, email) WHERE status = 'pending';
CREATE INDEX ix_invite_expiring             ON tenant.invitations(expires_at) WHERE status = 'pending';
CREATE INDEX ix_invite_token_hash           ON tenant.invitations(token_hash);

ALTER TABLE tenant.invitations ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_inv ON tenant.invitations
  USING (tenant_id = current_setting('app.tenant_id', true));

3.8 `billing_contacts`

CREATE TABLE tenant.billing_contacts (
  id          TEXT PRIMARY KEY CHECK (id ~ '^bcn_'),
  tenant_id   TEXT NOT NULL UNIQUE REFERENCES tenant.tenants(id) ON DELETE RESTRICT,
  full_name   TEXT NOT NULL,
  email       CITEXT NOT NULL,
  phone       TEXT,
  address     JSONB NOT NULL,                             -- PostalAddress; CMEK column-encrypted
  tax_id_enc  BYTEA,                                      -- pgcrypto, sym key in Secret Manager
  version     BIGINT NOT NULL DEFAULT 1,
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE tenant.billing_contacts ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_bcn ON tenant.billing_contacts
  USING (tenant_id = current_setting('app.tenant_id', true));

3.9 `feature_flag_overrides`

CREATE TABLE tenant.feature_flag_overrides (
  id                   TEXT PRIMARY KEY CHECK (id ~ '^flg_'),
  tenant_id            TEXT NOT NULL REFERENCES tenant.tenants(id) ON DELETE RESTRICT,
  flag_key             TEXT NOT NULL,
  enabled              BOOLEAN NOT NULL,
  rollout_basis_points INT NOT NULL CHECK (rollout_basis_points BETWEEN 0 AND 10000),
  updated_by           TEXT NOT NULL,
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  version              BIGINT NOT NULL DEFAULT 1,
  UNIQUE (tenant_id, flag_key)
);

ALTER TABLE tenant.feature_flag_overrides ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_flg ON tenant.feature_flag_overrides
  USING (tenant_id = current_setting('app.tenant_id', true));

3.10 `audit_events`

CREATE TABLE tenant.audit_events (
  id            TEXT PRIMARY KEY CHECK (id ~ '^aud_'),
  tenant_id     TEXT NOT NULL,
  actor_user_id TEXT,
  action        TEXT NOT NULL,                            -- 'tenant.suspend', 'membership.role_change', ...
  subject_type  TEXT NOT NULL,
  subject_id    TEXT NOT NULL,
  before        JSONB,
  after         JSONB,
  request_id    TEXT,
  trace_id      TEXT,
  occurred_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX ix_audit_tenant_time   ON tenant.audit_events(tenant_id, occurred_at DESC);
CREATE INDEX ix_audit_subject       ON tenant.audit_events(subject_type, subject_id);

ALTER TABLE tenant.audit_events ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_aud ON tenant.audit_events
  USING (tenant_id = current_setting('app.tenant_id', true));

3.11 `outbox` and `inbox`

CREATE TABLE tenant.outbox (
  id            TEXT PRIMARY KEY CHECK (id ~ '^obx_'),
  tenant_id     TEXT NOT NULL,
  topic         TEXT NOT NULL,                            -- 'melmastoon.tenant.created.v1'
  ordering_key  TEXT NOT NULL,                            -- usually tenant_id
  payload       JSONB NOT NULL,
  attempts      INT NOT NULL DEFAULT 0,
  status        TEXT NOT NULL CHECK (status IN ('pending','dispatched','dead')),
  enqueued_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  dispatched_at TIMESTAMPTZ,
  last_error    TEXT
);

CREATE INDEX ix_outbox_pending ON tenant.outbox(enqueued_at) WHERE status = 'pending';

CREATE TABLE tenant.inbox (
  consumer_name TEXT NOT NULL,
  event_id      TEXT NOT NULL,
  tenant_id     TEXT NOT NULL,
  consumed_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (consumer_name, event_id)
);

4. Indexing Strategy

Table	Hot query	Index
`memberships`	"list staff at property"	`ix_memberships_propscope` (GIN)
`memberships`	"user's tenants"	`ix_memberships_user`
`organization_units`	ancestor / descendant lookup	`ix_org_path_gist` (GIST on `ltree`)
`role_assignments`	"all owners of tenant"	`ix_rla_tenant_role WHERE revoked_at IS NULL`
`invitations`	scheduler `expireIfDue`	`ix_invite_expiring`
`outbox`	poller batch	`ix_outbox_pending`
`audit_events`	tenant audit trail	`ix_audit_tenant_time DESC`

All indexes reviewed quarterly; pg_stat_statements + slow-log on dev.

5. Row-Level Security

All tables except tenants, outbox, inbox enable RLS with the policy:

USING (tenant_id = current_setting('app.tenant_id', true))

The application sets app.tenant_id via SET LOCAL at the start of every transaction (NestJS interceptor). Background jobs use a service role that runs SET LOCAL row_security = off and must explicitly carry tenant_id in queries (linted by a custom ESLint rule).

The two-tenant simulator (see TESTING_STRATEGY) executes every read/write twice with conflicting app.tenant_id and asserts zero leakage on every PR.

6. Seed Data

Seeded on tenant provision (transactional with tenant.created):

System role `code`	Permissions (sample)
`tenant.owner`	`:` (all canonical permissions)
`tenant.gm`	`tenant.config:read`, `membership:`, `reservation:`, `property:*`, `report:run`
`tenant.front_desk`	`reservation:create`, `reservation:check_in`, `reservation:check_out`, `folio:read`, `key_credential:issue`
`tenant.housekeeping_lead`	`housekeeping:*`, `property:read`
`tenant.housekeeping`	`housekeeping:task:read`, `housekeeping:task:complete`
`tenant.maintenance`	`maintenance:*`, `property:read`
`tenant.finance`	`folio:*`, `report:run`, `billing_contact:read`
`tenant.marketing`	`theme_config:read`, `report:run`, `pricing:read`
`chain.operator`	scoped across multiple tenants in the chain account

The canonical (resource, action) registry is seeded via Flyway migration V005__seed_permission_registry.sql.

7. Migrations

Tool: Flyway (migrations/V###__name.sql).
Online-safe rules (additive columns nullable, indexes CONCURRENTLY, no rewrites > 30 s).
Backfill jobs run as a separate Cloud Run Job, never inline with the app deploy.
tenant.config schema changes that add a required field include a backfill statement defaulting to current_value and a constraint added in a follow-up migration.

See MIGRATION_PLAN for evolution policy.

8. Sensitive Columns

Column	Classification	At-rest control
`billing_contacts.email`	PII	CMEK at table level
`billing_contacts.tax_id_enc`	PII / financial	Column-level pgcrypto symmetric key, rotated yearly
`invitations.email`	PII	CMEK at table level; redacted in logs
`audit_events.before/after`	mixed	CMEK; redaction filter strips known PII fields before write

Never log raw rows. Logs reference id only.

1. Database Topology​

2. Entity Relationship Diagram​

3. DDL — Tables​

3.1 tenants​

3.2 tenant_configs​

3.3 organization_units​

3.4 memberships​

3.5 roles and role_permissions​

3.6 role_assignments​

3.7 invitations​

3.8 billing_contacts​

3.9 feature_flag_overrides​

3.10 audit_events​

3.11 outbox and inbox​

4. Indexing Strategy​

5. Row-Level Security​

6. Seed Data​

7. Migrations​

8. Sensitive Columns​