Platform Admin Service — Data Model

Status: populated Owner: TBD Last updated: 2026-04-18 Companion: NAMING · 13 Security

Schema: platform_admin ID strategy: ULID + prefix for entities with surrogate IDs. Config and flags use natural keys. Note: platform_configs table is NOT tenant-RLS-restricted (PLATFORM scope is global). TENANT-scoped rows are filtered by tenant_id column + guard.

1. ID prefix registry

Prefix	Entity	Table
hsr_	HealthSource	health_sources
hcr_	HealthCheckResult	health_check_results
cph_	ConfigHistory	config_history

Feature flags and configs use natural string keys as primary identifiers.

2. TypeScript interfaces

interface PlatformConfig {
  key: string                // allow-listed config key
  value: string              // serialized value
  scope: 'PLATFORM' | 'TENANT' | 'NODE'
  tenantId: string | null
  description: string | null
  isArchived: boolean
  updatedAt: Date
}

interface FeatureFlag {
  key: string
  description: string
  defaultEnabled: boolean
  enabledTenantIds: string[]
  disabledTenantIds: string[]
  isArchived: boolean
  createdAt: Date
  updatedAt: Date
}

interface HealthSource {
  id: string             // hsr_ prefixed ULID
  serviceId: string      // e.g., 'identity-service'
  healthUrl: string
  lastHeartbeatAt: Date
  stalenessThresholdS: number
}

3. Postgres schema

CREATE SCHEMA IF NOT EXISTS platform_admin;
SET search_path TO platform_admin, public;

CREATE TABLE platform_configs (
  key         VARCHAR(100) NOT NULL,
  scope       VARCHAR(20) NOT NULL DEFAULT 'PLATFORM'
                CHECK (scope IN ('PLATFORM','TENANT','NODE')),
  tenant_id   CHAR(26),                -- NULL for PLATFORM scope
  value       TEXT NOT NULL,
  description TEXT,
  is_archived BOOLEAN NOT NULL DEFAULT FALSE,
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (key, scope, COALESCE(tenant_id, ''))
);
CREATE INDEX ix_pconfig_scope ON platform_configs(scope, tenant_id);

CREATE TABLE config_history (
  id          CHAR(26) PRIMARY KEY,
  config_key  VARCHAR(100) NOT NULL,
  scope       VARCHAR(20) NOT NULL,
  tenant_id   CHAR(26),
  old_value   TEXT,
  new_value   TEXT NOT NULL,
  changed_by  CHAR(26),
  changed_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_chist_key_time ON config_history(config_key, changed_at DESC);

CREATE TABLE feature_flags (
  key                 VARCHAR(100) PRIMARY KEY,
  description         TEXT,
  default_enabled     BOOLEAN NOT NULL DEFAULT FALSE,
  enabled_tenant_ids  TEXT[] NOT NULL DEFAULT '{}',
  disabled_tenant_ids TEXT[] NOT NULL DEFAULT '{}',
  is_archived         BOOLEAN NOT NULL DEFAULT FALSE,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_flags_archived ON feature_flags(is_archived);

CREATE TABLE health_sources (
  id                    CHAR(26) PRIMARY KEY,
  service_id            VARCHAR(100) NOT NULL UNIQUE,
  health_url            TEXT NOT NULL,
  last_heartbeat_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  staleness_threshold_s INT NOT NULL DEFAULT 60
);

CREATE TABLE health_check_results (
  id           CHAR(26) PRIMARY KEY,
  source_id    CHAR(26) NOT NULL REFERENCES health_sources(id),
  status       VARCHAR(20) NOT NULL CHECK (status IN ('healthy','unhealthy','degraded')),
  checked_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  details      JSONB
);
CREATE INDEX ix_hcr_source_time ON health_check_results(source_id, checked_at DESC);

CREATE TABLE outbox (
  id           CHAR(26) PRIMARY KEY,
  tenant_id    CHAR(26),
  subject      VARCHAR(200) NOT NULL,
  payload      JSONB NOT NULL,
  headers      JSONB NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  published_at TIMESTAMPTZ,
  attempt      INT NOT NULL DEFAULT 0,
  last_error   TEXT
);
CREATE INDEX ix_outbox_unpublished ON outbox(published_at) WHERE published_at IS NULL;

CREATE TABLE inbox (
  id           CHAR(26) PRIMARY KEY,
  subject      VARCHAR(200) NOT NULL,
  received_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  processed_at TIMESTAMPTZ
);

4. Seeded allow-list config keys

Key	Type	Scope
global.session_max_absolute_minutes	integer	PLATFORM
global.session_idle_minutes	integer	PLATFORM
global.mfa_required_default	boolean	PLATFORM
global.password_min_length	integer	PLATFORM
global.password_require_symbols	boolean	PLATFORM
global.max_failed_login_attempts	integer	PLATFORM
email.smtp_host	string	PLATFORM
email.smtp_port	integer	PLATFORM
email.from_address	string	PLATFORM

5. Volume estimates

Table	Year-1 rows	Retention
platform_configs	200	Forever
config_history	5 000	7 years
feature_flags	500	Forever
health_sources	30	Forever
health_check_results	5 M	30 days (prune old)
outbox	rolling	7 days