DATA_MODEL — staff-service

Sibling: DOMAIN_MODEL · SECURITY_MODEL · SYNC_CONTRACT

Strategic anchors: 06 Data Models · 07 Security & Tenancy · standards/NAMING §11 ID prefixes

PostgreSQL is the system of record. Redis (Memorystore) is purely opportunistic — any value cached MUST be re-derivable from Postgres. Firestore stores per-device sync cursors only and is owned by sync-service on our behalf.

1. Storage Choices

Store	Engine	Use
Primary	Cloud SQL PostgreSQL 16 (regional HA)	All durable aggregates, outbox, inbox, audit
Cache	Memorystore Redis 7	PIN failure counter, capacity snapshot, idempotency-key index, position label translation cache
Sync state	Firestore (multi-region)	Per-device sync cursors (read by `sync-service`)
Object storage	Cloud Storage	Certification documents (signed-URL upload), attendance CSV exports
Crypto	Cloud KMS (regional)	PIN HMAC pepper, emergency-contact envelope key
Secrets	Secret Manager	Reserved future use (e.g., webhook signing keys)
Cold export	BigQuery (federated via Datastream)	Read-only analytics: attendance facts, certification expiry funnel

2. ID Conventions

All IDs follow NAMING §11 — <prefix>_<26-char Crockford ULID>. Prefixes owned by staff-service:

Aggregate / Entity	Prefix	Sample
`Staff`	`stf_`	`stf_01HZA2B3C4D5E6F7G8H9J0K1L2`
`Position`	`pos_`	`pos_01HZA2B3C4D5E6F7G8H9J0K1L2`
`Department`	`dpt_`	`dpt_01HZ…`
`ShiftPattern`	`shp_`	`shp_01HZ…`
`Shift`	`shf_`	`shf_01HZ…`
`ShiftAssignment`	`sha_`	`sha_01HZ…`
`ClockEntry`	`clk_`	`clk_01HZ…`
`LeaveRequest`	`lvr_`	`lvr_01HZ…`
`StaffSkill` (catalog row)	`skl_`	`skl_01HZ…`
`StaffCertification`	`crt_`	`crt_01HZ…`
`HandoffNote`	`hno_`	`hno_01HZ…`
`ShiftSuggestion` (advisory)	`sgg_`	`sgg_01HZ…`

Foreign IDs (ten_…, ppt_…, usr_…, dev_…) are treated as opaque.

3. Schema Layout

All tables live in the dedicated Postgres schema staff. Created by Flyway migration V001__init.sql.

CREATE SCHEMA IF NOT EXISTS staff;
CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA pg_catalog;
CREATE EXTENSION IF NOT EXISTS citext   WITH SCHEMA pg_catalog;
CREATE EXTENSION IF NOT EXISTS btree_gist WITH SCHEMA pg_catalog;  -- for tstzrange overlap exclusion

Encoding UTF-8, time zone UTC, all timestamps timestamptz.

4. Tables

4.1 `staff.staff`

CREATE TABLE staff.staff (
  id                       text PRIMARY KEY
                              CHECK (id ~ '^stf_[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id                text NOT NULL CHECK (tenant_id ~ '^ten_[0-9A-HJKMNP-TV-Z]{26}$'),
  home_property_id         text NOT NULL CHECK (home_property_id ~ '^ppt_[0-9A-HJKMNP-TV-Z]{26}$'),
  user_id                  text         CHECK (user_id IS NULL OR user_id ~ '^usr_[0-9A-HJKMNP-TV-Z]{26}$'),
  staff_code               text NOT NULL,
  given_name               text NOT NULL,
  family_name              text NOT NULL,
  display_name             text,
  preferred_locale         text NOT NULL DEFAULT 'en',
  email                    citext,
  manager_email            citext,
  phone_e164               text,
  emergency_contact_enc    bytea,                       -- envelope-encrypted JSON; KMS key pinned per region
  position_id              text NOT NULL CHECK (position_id ~ '^pos_[0-9A-HJKMNP-TV-Z]{26}$'),
  department_id            text NOT NULL CHECK (department_id ~ '^dpt_[0-9A-HJKMNP-TV-Z]{26}$'),
  employment_type          text NOT NULL CHECK (employment_type IN ('full_time','part_time','temporary','seasonal','family_help','contractor')),
  employment_status        text NOT NULL CHECK (employment_status IN ('pending_invite','active','on_leave','suspended','terminated')),
  employment_started_at    date NOT NULL,
  employment_ended_at      date,
  spoken_languages         jsonb NOT NULL DEFAULT '[]'::jsonb,    -- [{"code":"ps","proficiency":"native"}]
  skill_ids                text[] NOT NULL DEFAULT '{}',
  certification_ids        text[] NOT NULL DEFAULT '{}',
  clock_in_pin_hmac        bytea,
  clock_in_pin_set_at      timestamptz,
  clock_in_pin_failed      smallint NOT NULL DEFAULT 0,
  clock_in_pin_locked_until timestamptz,
  version                  integer NOT NULL DEFAULT 1,
  created_at               timestamptz NOT NULL DEFAULT now(),
  updated_at               timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT staff_contact_required CHECK (email IS NOT NULL OR manager_email IS NOT NULL),
  CONSTRAINT staff_terminated_dates CHECK (employment_status <> 'terminated' OR employment_ended_at IS NOT NULL)
);

CREATE UNIQUE INDEX staff_staff_code_uk    ON staff.staff (tenant_id, staff_code);
CREATE        INDEX staff_tenant_idx       ON staff.staff (tenant_id);
CREATE        INDEX staff_property_status  ON staff.staff (home_property_id, employment_status);
CREATE        INDEX staff_position_idx     ON staff.staff (tenant_id, position_id) WHERE employment_status = 'active';
CREATE        INDEX staff_user_idx         ON staff.staff (user_id) WHERE user_id IS NOT NULL;
CREATE        INDEX staff_email_idx        ON staff.staff (lower(email)) WHERE email IS NOT NULL;

ALTER TABLE staff.staff ENABLE ROW LEVEL SECURITY;
CREATE POLICY staff_tenant_rw ON staff.staff
  USING (tenant_id = current_setting('app.tenant_id', true));
CREATE POLICY staff_platform_admin ON staff.staff
  USING (current_setting('app.role', true) = 'platform_admin');

4.2 `staff.staff_property_access`

Many-to-many for multi-property staff.

CREATE TABLE staff.staff_property_access (
  staff_id     text NOT NULL REFERENCES staff.staff(id) ON DELETE CASCADE,
  property_id  text NOT NULL CHECK (property_id ~ '^ppt_[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id    text NOT NULL,
  granted_at   timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (staff_id, property_id)
);
CREATE INDEX spa_property_idx ON staff.staff_property_access (property_id);
CREATE INDEX spa_tenant_idx   ON staff.staff_property_access (tenant_id);
ALTER TABLE staff.staff_property_access ENABLE ROW LEVEL SECURITY;
CREATE POLICY spa_tenant_rw ON staff.staff_property_access
  USING (tenant_id = current_setting('app.tenant_id', true));

4.3 `staff.positions` and `staff.departments`

CREATE TABLE staff.positions (
  id                    text PRIMARY KEY CHECK (id ~ '^pos_[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id             text NOT NULL,
  department_id         text NOT NULL CHECK (department_id ~ '^dpt_[0-9A-HJKMNP-TV-Z]{26}$'),
  code                  text NOT NULL,
  label                 jsonb NOT NULL,                            -- LocalizedLabel
  capacity_signal_key   text NOT NULL,
  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 UNIQUE INDEX positions_code_uk ON staff.positions (tenant_id, code);
ALTER TABLE staff.positions ENABLE ROW LEVEL SECURITY;
CREATE POLICY positions_tenant_rw ON staff.positions
  USING (tenant_id = current_setting('app.tenant_id', true));

CREATE TABLE staff.departments (
  id           text PRIMARY KEY CHECK (id ~ '^dpt_[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id    text NOT NULL,
  property_id  text NOT NULL CHECK (property_id ~ '^ppt_[0-9A-HJKMNP-TV-Z]{26}$'),
  code         text NOT NULL,
  label        jsonb NOT NULL,
  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 UNIQUE INDEX departments_code_uk ON staff.departments (tenant_id, property_id, code);
ALTER TABLE staff.departments ENABLE ROW LEVEL SECURITY;
CREATE POLICY departments_tenant_rw ON staff.departments
  USING (tenant_id = current_setting('app.tenant_id', true));

4.4 `staff.shift_patterns`

CREATE TABLE staff.shift_patterns (
  id                    text PRIMARY KEY CHECK (id ~ '^shp_[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id             text NOT NULL,
  property_id           text NOT NULL,
  position_id           text NOT NULL,
  name                  text NOT NULL,
  cadence               text NOT NULL CHECK (cadence IN ('weekly','bi_weekly')),
  week_days             text[] NOT NULL,    -- {'mon','tue',…}
  start_local           text NOT NULL,      -- 'HH:mm'
  end_local             text NOT NULL,
  primary_headcount     smallint NOT NULL CHECK (primary_headcount >= 1),
  standby_headcount     smallint NOT NULL DEFAULT 0 CHECK (standby_headcount >= 0),
  effective_from        date NOT NULL,
  effective_to          date,
  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 sp_property_idx ON staff.shift_patterns (tenant_id, property_id, position_id) WHERE active;
ALTER TABLE staff.shift_patterns ENABLE ROW LEVEL SECURITY;
CREATE POLICY sp_tenant_rw ON staff.shift_patterns
  USING (tenant_id = current_setting('app.tenant_id', true));

4.5 `staff.shifts`

CREATE TABLE staff.shifts (
  id                    text PRIMARY KEY CHECK (id ~ '^shf_[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id             text NOT NULL,
  property_id           text NOT NULL,
  position_id           text NOT NULL,
  pattern_id            text REFERENCES staff.shift_patterns(id),
  window_utc            tstzrange NOT NULL,                          -- [start, end)
  local_date            date NOT NULL,
  start_local           text NOT NULL,
  end_local             text NOT NULL,
  tz                    text NOT NULL,
  primary_headcount     smallint NOT NULL CHECK (primary_headcount >= 1),
  standby_headcount     smallint NOT NULL DEFAULT 0,
  status                text NOT NULL CHECK (status IN ('scheduled','in_progress','completed','cancelled')),
  notes                 text,
  started_at            timestamptz,
  ended_at              timestamptz,
  cancelled_at          timestamptz,
  cancel_reason         text,
  staffing_gap_emitted_at timestamptz,
  version               integer NOT NULL DEFAULT 1,
  created_at            timestamptz NOT NULL DEFAULT now(),
  updated_at            timestamptz NOT NULL DEFAULT now(),
  EXCLUDE USING gist (
    tenant_id   WITH =,
    pattern_id  WITH =,
    local_date  WITH =
  ) WHERE (pattern_id IS NOT NULL)
);
CREATE INDEX shifts_property_window_idx ON staff.shifts USING gist (tenant_id, property_id, window_utc);
CREATE INDEX shifts_status_idx          ON staff.shifts (tenant_id, status) WHERE status IN ('scheduled','in_progress');
CREATE INDEX shifts_local_date_idx      ON staff.shifts (tenant_id, property_id, local_date);
ALTER TABLE staff.shifts ENABLE ROW LEVEL SECURITY;
CREATE POLICY shifts_tenant_rw ON staff.shifts
  USING (tenant_id = current_setting('app.tenant_id', true));

The EXCLUDE constraint enforces no two pattern-derived shifts share the same (tenant_id, pattern_id, local_date).

4.6 `staff.shift_assignments`

CREATE TABLE staff.shift_assignments (
  id                          text PRIMARY KEY CHECK (id ~ '^sha_[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id                   text NOT NULL,
  shift_id                    text NOT NULL REFERENCES staff.shifts(id),
  staff_id                    text NOT NULL REFERENCES staff.staff(id),
  role                        text NOT NULL CHECK (role IN ('primary','standby','on_call')),
  source                      text NOT NULL CHECK (source IN ('manual','ai_suggested_accepted','auto_promoted')),
  swapped_from_assignment_id  text,
  acknowledged_at             timestamptz,
  unassigned_at               timestamptz,
  unassign_reason             text,
  version                     integer NOT NULL DEFAULT 1,
  created_at                  timestamptz NOT NULL DEFAULT now(),
  updated_at                  timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX sa_shift_staff_active_uk
  ON staff.shift_assignments (shift_id, staff_id) WHERE unassigned_at IS NULL;
CREATE INDEX sa_staff_idx     ON staff.shift_assignments (tenant_id, staff_id) WHERE unassigned_at IS NULL;
CREATE INDEX sa_shift_role_idx ON staff.shift_assignments (shift_id, role)      WHERE unassigned_at IS NULL;
ALTER TABLE staff.shift_assignments ENABLE ROW LEVEL SECURITY;
CREATE POLICY sa_tenant_rw ON staff.shift_assignments
  USING (tenant_id = current_setting('app.tenant_id', true));

I-Asn-2 (no overlapping primary) is enforced by the application layer — Postgres EXCLUDE over tstzrange joined to shifts is non-trivial across tables; we rely on use-case checks plus a periodic invariant scan.

4.7 `staff.clock_entries` (append-only)

CREATE TABLE staff.clock_entries (
  id                          text PRIMARY KEY CHECK (id ~ '^clk_[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id                   text NOT NULL,
  staff_id                    text NOT NULL,
  property_id                 text NOT NULL,
  shift_id                    text,
  kind                        text NOT NULL CHECK (kind IN ('in','out','break_start','break_end')),
  occurred_at_utc             timestamptz NOT NULL,
  recorded_at_utc             timestamptz NOT NULL DEFAULT now(),
  source                      text NOT NULL CHECK (source IN ('electron_pin','electron_jwt','mobile_jwt','web_jwt','manager_override','offline_replay','system_auto')),
  device_id                   text,
  manager_override_by         text,
  manager_override_reason     text,
  geofenced_ok                boolean,
  offline_queue_age_seconds   integer,
  CONSTRAINT clk_override_consistency CHECK (
    (source <> 'manager_override') OR (manager_override_by IS NOT NULL AND manager_override_reason IS NOT NULL)
  ),
  CONSTRAINT clk_offline_consistency CHECK (
    (source <> 'offline_replay') OR (offline_queue_age_seconds IS NOT NULL)
  )
);

CREATE UNIQUE INDEX clk_dedupe_uk
  ON staff.clock_entries (tenant_id, staff_id, occurred_at_utc, kind);
CREATE INDEX clk_staff_time_idx    ON staff.clock_entries (tenant_id, staff_id, occurred_at_utc DESC);
CREATE INDEX clk_shift_idx         ON staff.clock_entries (shift_id) WHERE shift_id IS NOT NULL;
CREATE INDEX clk_property_time_idx ON staff.clock_entries (tenant_id, property_id, occurred_at_utc DESC);

ALTER TABLE staff.clock_entries ENABLE ROW LEVEL SECURITY;
CREATE POLICY clk_tenant_rw ON staff.clock_entries
  USING (tenant_id = current_setting('app.tenant_id', true));

4.8 `staff.leave_requests`

CREATE TABLE staff.leave_requests (
  id                              text PRIMARY KEY CHECK (id ~ '^lvr_[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id                       text NOT NULL,
  staff_id                        text NOT NULL REFERENCES staff.staff(id),
  type                            text NOT NULL CHECK (type IN ('sick','vacation','unpaid')),
  window_local_from               date NOT NULL,
  window_local_to                 date NOT NULL CHECK (window_local_to >= window_local_from),
  reason                          text,
  status                          text NOT NULL CHECK (status IN ('requested','approved','rejected','cancelled')),
  decided_by                      text,
  decided_at                      timestamptz,
  decision_note                   text,
  force_unassigned_assignment_ids text[] NOT NULL DEFAULT '{}',
  version                         integer NOT NULL DEFAULT 1,
  created_at                      timestamptz NOT NULL DEFAULT now(),
  updated_at                      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX lvr_staff_window_idx ON staff.leave_requests (staff_id, window_local_from, window_local_to);
CREATE INDEX lvr_status_idx       ON staff.leave_requests (tenant_id, status) WHERE status = 'requested';
ALTER TABLE staff.leave_requests ENABLE ROW LEVEL SECURITY;
CREATE POLICY lvr_tenant_rw ON staff.leave_requests
  USING (tenant_id = current_setting('app.tenant_id', true));

4.9 `staff.skills` and `staff.certifications`

CREATE TABLE staff.skills (
  id          text PRIMARY KEY CHECK (id ~ '^skl_[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id   text NOT NULL,
  code        text NOT NULL,
  label       jsonb NOT NULL,
  category    text NOT NULL CHECK (category IN ('language','equipment','soft','other')),
  active      boolean NOT NULL DEFAULT true,
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX skills_code_uk ON staff.skills (tenant_id, code);
ALTER TABLE staff.skills ENABLE ROW LEVEL SECURITY;
CREATE POLICY skills_tenant_rw ON staff.skills
  USING (tenant_id = current_setting('app.tenant_id', true));

CREATE TABLE staff.certifications (
  id                  text PRIMARY KEY CHECK (id ~ '^crt_[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id           text NOT NULL,
  staff_id            text NOT NULL REFERENCES staff.staff(id),
  type                text NOT NULL CHECK (type IN ('food_handling','first_aid','fire_safety','pool_safety','security_license','other')),
  custom_label        jsonb,
  issued_at           date NOT NULL,
  expires_at          date,
  document_ref        text,
  expired_emitted_at  timestamptz,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX cert_expiry_idx ON staff.certifications (expires_at) WHERE expires_at IS NOT NULL AND expired_emitted_at IS NULL;
CREATE INDEX cert_staff_idx  ON staff.certifications (staff_id);
ALTER TABLE staff.certifications ENABLE ROW LEVEL SECURITY;
CREATE POLICY cert_tenant_rw ON staff.certifications
  USING (tenant_id = current_setting('app.tenant_id', true));

4.10 `staff.handoff_notes` (append-only)

CREATE TABLE staff.handoff_notes (
  id              text PRIMARY KEY CHECK (id ~ '^hno_[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id       text NOT NULL,
  property_id     text NOT NULL,
  position_id     text NOT NULL,
  from_staff_id   text NOT NULL,
  from_shift_id   text,
  body            text NOT NULL CHECK (length(body) <= 4000),
  attachments     text[] NOT NULL DEFAULT '{}',
  acknowledged_by jsonb NOT NULL DEFAULT '[]'::jsonb,
  created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX hno_recent_idx ON staff.handoff_notes (tenant_id, property_id, position_id, created_at DESC);
ALTER TABLE staff.handoff_notes ENABLE ROW LEVEL SECURITY;
CREATE POLICY hno_tenant_rw ON staff.handoff_notes
  USING (tenant_id = current_setting('app.tenant_id', true));

4.11 `staff.shift_suggestions` (advisory, AI)

CREATE TABLE staff.shift_suggestions (
  id              text PRIMARY KEY CHECK (id ~ '^sgg_[0-9A-HJKMNP-TV-Z]{26}$'),
  tenant_id       text NOT NULL,
  property_id     text NOT NULL,
  for_local_date  date NOT NULL,
  payload         jsonb NOT NULL,
  ai_provenance   jsonb NOT NULL,
  received_at     timestamptz NOT NULL DEFAULT now(),
  ttl_at          timestamptz NOT NULL DEFAULT (now() + interval '14 days')
);
CREATE INDEX sgg_lookup_idx ON staff.shift_suggestions (tenant_id, property_id, for_local_date);
CREATE INDEX sgg_ttl_idx    ON staff.shift_suggestions (ttl_at);
ALTER TABLE staff.shift_suggestions ENABLE ROW LEVEL SECURITY;
CREATE POLICY sgg_tenant_rw ON staff.shift_suggestions
  USING (tenant_id = current_setting('app.tenant_id', true));

4.12 `staff.outbox` and `staff.inbox_processed`

Per 04 §9–10. Standard shape.

CREATE TABLE staff.outbox (
  id              bigserial PRIMARY KEY,
  occurred_at     timestamptz NOT NULL DEFAULT now(),
  tenant_id       text NOT NULL,
  aggregate_type  text NOT NULL,
  aggregate_id    text NOT NULL,
  topic           text NOT NULL,
  ordering_key    text,
  envelope        jsonb NOT NULL,
  idempotency_key text,
  attempts        integer NOT NULL DEFAULT 0,
  last_error      text,
  published_at    timestamptz,
  CONSTRAINT outbox_idem_uk UNIQUE (tenant_id, idempotency_key)
);
CREATE INDEX outbox_unpublished_idx ON staff.outbox (published_at) WHERE published_at IS NULL;
ALTER TABLE staff.outbox ENABLE ROW LEVEL SECURITY;
CREATE POLICY outbox_tenant_rw ON staff.outbox
  USING (tenant_id = current_setting('app.tenant_id', true));

CREATE TABLE staff.inbox_processed (
  event_id     text PRIMARY KEY,
  topic        text NOT NULL,
  tenant_id    text NOT NULL,
  processed_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX inbox_topic_idx ON staff.inbox_processed (topic, processed_at DESC);

4.13 `staff.audit_events`

CREATE TABLE staff.audit_events (
  id            bigserial PRIMARY KEY,
  occurred_at   timestamptz NOT NULL DEFAULT now(),
  tenant_id     text NOT NULL,
  actor_id      text,
  action        text NOT NULL,           -- 'staff.create', 'shift.assign', 'clock.override', …
  target_type   text NOT NULL,
  target_id     text NOT NULL,
  before        jsonb,
  after         jsonb,
  ip            inet,
  device_id     text,
  trace_id      text
);
CREATE INDEX audit_target_idx ON staff.audit_events (tenant_id, target_type, target_id, occurred_at DESC);
ALTER TABLE staff.audit_events ENABLE ROW LEVEL SECURITY;
CREATE POLICY audit_tenant_ro ON staff.audit_events
  FOR SELECT USING (tenant_id = current_setting('app.tenant_id', true));

Audit rows are written from a Tx-bound trigger function; never updated.

5. Append-Only Triggers

CREATE OR REPLACE FUNCTION staff.tg_forbid_update_delete() RETURNS trigger AS $$
BEGIN
  RAISE EXCEPTION 'append-only table %', TG_TABLE_NAME USING ERRCODE = '0L000';
END $$ LANGUAGE plpgsql;

CREATE TRIGGER clk_no_update BEFORE UPDATE OR DELETE ON staff.clock_entries
  FOR EACH ROW EXECUTE FUNCTION staff.tg_forbid_update_delete();

CREATE TRIGGER hno_no_update BEFORE UPDATE OR DELETE ON staff.handoff_notes
  FOR EACH ROW EXECUTE FUNCTION staff.tg_forbid_update_delete();

CREATE TRIGGER audit_no_update BEFORE UPDATE OR DELETE ON staff.audit_events
  FOR EACH ROW EXECUTE FUNCTION staff.tg_forbid_update_delete();

The migration toolchain is allowed to bypass these via a SET LOCAL app.role = 'platform_admin' plus a DROP TRIGGER block that is reapplied; per DOD, any such migration requires a CR.

6. Retention & PII

Table	Hot retention	Cold retention	PII?
`staff.staff`	indefinite while active	7 y after termination (legal min)	yes (name, email, phone, emergency contact)
`staff.clock_entries`	2 y	7 y in BigQuery export (anonymized hash)	low (timestamps, refs)
`staff.shifts`	2 y	7 y in BigQuery	none
`staff.shift_assignments`	2 y	7 y in BigQuery	none
`staff.leave_requests`	2 y	5 y in BigQuery	low (reason free-text)
`staff.handoff_notes`	1 y	3 y in BigQuery	low (operational)
`staff.audit_events`	90 d in Postgres	7 y in BigQuery	yes (actor IDs, IPs)
`staff.outbox` (published)	7 d	none	low
`staff.inbox_processed`	30 d	none	none
`staff.shift_suggestions`	14 d	none	none

emergency_contact_enc is envelope-encrypted with a per-region KMS key; on staff DSAR or termination + retention exhaustion, the contact column is rotated to a NULL marker and a staff.dsar_event audit row is written (per 07 §8).

7. Indexing & Hot Paths

Hot read	Backing index
Capacity snapshot	`clk_property_time_idx` + `sa_shift_role_idx`
Staff list + filter	`staff_property_status`, `staff_position_idx`
Schedule grid	`shifts_property_window_idx` (gist)
Open clock-in for staff	`clk_staff_time_idx` (DESC, LIMIT 1)
Leave calendar	`lvr_staff_window_idx`
Cert expiry scanner	`cert_expiry_idx` (partial)

The capacity snapshot is also denormalized into Redis (capacity:{propertyId}:{positionCode}) with 30 s TTL and proactive invalidation on every clock.* event publish.

8. Migrations & Versioning

Flyway, immutable scripts. V001__init.sql, V002__add_*.sql, …
Repeatable scripts (R__) are forbidden — schema changes must be linear.
Online (zero-downtime) migrations follow the database-migrations standard: expand → backfill → contract. Every migration ships with a rollback plan validated in staging.

9. Cloud SQL Configuration

Setting	Value
Engine	PostgreSQL 16 (Enterprise Plus)
HA	Regional
Read replicas	2 (M0), 4 (M2)
Backup	Daily, 35 d retention
PITR	7 d
Encryption	CMEK from Cloud KMS
Connection pool	PgBouncer sidecar, 100 active / instance
Statement timeout	5 s OLTP, 60 s reports
`idle_in_transaction_session_timeout`	30 s
`lock_timeout`	2 s

1. Storage Choices​

2. ID Conventions​

3. Schema Layout​

4. Tables​

4.1 staff.staff​

4.2 staff.staff_property_access​

4.3 staff.positions and staff.departments​

4.4 staff.shift_patterns​

4.5 staff.shifts​

4.6 staff.shift_assignments​

4.7 staff.clock_entries (append-only)​

4.8 staff.leave_requests​

4.9 staff.skills and staff.certifications​

4.10 staff.handoff_notes (append-only)​

4.11 staff.shift_suggestions (advisory, AI)​

4.12 staff.outbox and staff.inbox_processed​

4.13 staff.audit_events​

5. Append-Only Triggers​

6. Retention & PII​

7. Indexing & Hot Paths​

8. Migrations & Versioning​

9. Cloud SQL Configuration​