3.3 Data architecture

KMP’s schema is split by ownership, not kept in one shared database. The current contract is defined by CakePHP migrations, table classes, validation/build rules, and tests. This page explains the durable model without duplicating every column from those sources.

Database topology

Database	CakePHP connection	Owner
Central platform	`platform`	tenant registry, platform identity, secrets, operations, backup metadata, audit, telemetry
One database per kingdom	physical `tenant`, aliased to `default` in scope	all core and loaded-plugin application data
Test databases	separate application and platform test settings	automated test harness only

Tenant application tables do not have a shared tenant_id. The database chosen by trusted host/platform metadata is the isolation boundary. See Multi-tenant architecture.

Platform schema domains

The platform migration track owns these groups:

Tenancy: tenants, normalized host mappings, status, database metadata, and recorded schema state.
Identity: platform administrators, central sessions, login throttling/TOTP, and recovery controls.
Secrets and escrow: encrypted secret records/metadata plus key-ceremony tracking. Plaintext secret values and recovery material are never columns.
Operations: platform_jobs, platform_schedules, job events/retention, health data, and privacy-safe tenant request aggregates.
Backup: managed tenant backup records, platform database backup records, settings, checksums, wrapped-key metadata, and verification state.
Audit: central operator and lifecycle audit events.

Read the final migration state, not only early Create* migrations. Later migrations intentionally removed speculative feature flags, release controls, shared queue/dead-letter storage, and unused tenant fields. A proposed field is not part of the schema until a forward migration and consuming code both exist.

Core tenant schema domains

Membership and organization

members stores tenant member identity/profile data. branches forms the organizational hierarchy and uses public IDs for external routes. Member/branch relationships, profile documents, quick-login devices, and lifecycle fields remain tenant-local.

Branches use CakePHP tree semantics. Change hierarchy through the table/model APIs so left/right values and related authorization scope remain valid; do not manually rewrite tree coordinates.

RBAC and service principals

Roles, permissions, role-permission joins, member-role assignments, permission policies, service principals, service-principal roles/tokens, and their audit logs implement tenant authorization. Time-bounded assignments use the active window pattern. Policies and query scopes—not raw joins in controllers—interpret these records.

Warrants

Warrants, warrant periods, and rosters remain core tenant records. Historical feature-specific approval tables were migrated into the shared workflow engine and removed. New approval behavior belongs in workflow definitions/instances, not another one-off approval table.

Gatherings and documents

Gatherings, types, activities, attendance, staff, scheduled activities, and join tables support event planning and public calendars. Documents contain tenant metadata and storage references; binary objects are handled by the configured storage service rather than stored as arbitrary webroot files.

Workflow and action items

Workflow definitions and immutable versions describe flows. Instances, approvals, responses, triage state, schedules, tasks, execution logs, and instance-migration records capture runtime state. Action items and action-item logs represent durable follow-up work and may be produced by workflow actions.

Workflow concurrency columns and composite indexes are intentional. Update workflow state through engine services so optimistic/concurrency rules, logs, and domain callbacks remain consistent.

Settings, views, audit, and support

Application settings and email templates are tenant-specific. Grid views and preferences own reusable list configuration. Notes, impersonation logs, and backup/self-service compatibility tables support cross-domain features while remaining in the tenant database.

Plugin schema ownership

Plugin	Principal tenant tables
Activities	activities, groups, member authorizations
Officers	departments, offices, officer assignments
Awards	domains, levels, awards, recommendations, approval runs, feedback requests, bestowals, court agendas, migration records
Waivers	waiver types, gathering waivers/closures, gathering-activity requirements
Queue	tenant `queued_jobs` and queue process state

The exact prefixed table names and associations live in each plugin’s migrations and table classes. Plugin migrations run for every tenant in the fixed loaded plugin order. A plugin must not add its application tables to the central platform connection.

Shared schema conventions

Identifiers

Most records use integer internal primary keys. Selected externally referenced entities also carry unique random public_id values. Public IDs reduce enumeration and stabilize links, but callers still authorize the loaded entity. Platform tenant IDs may use a different identifier shape; do not cast them to member/entity IDs.

Audit columns

Many application tables use created/modified timestamps and actor attribution such as created_by/modified_by. Use project callbacks and Footprint patterns so HTTP and background changes are attributed consistently. Central platform operations use the platform audit service.

Temporal state

Time-bounded relationships use start_on and nullable expires_on, queried by ActiveWindowBehavior. Some domains also store a denormalized status that a scheduled reconciliation command updates. Preserve the inclusive current end boundary documented in Model behaviors.

Text and search

Current PostgreSQL migrations normalize human-facing and platform identifier text for case-insensitive behavior, with later search work adding diacritic-insensitive matching where supported. Hostnames are always normalized lowercase/trailing-dot-free. Do not assume a raw database collation gives every field the desired equality/search semantics; use the owning table’s finder and migration pattern.

JSON

JSON/text-encoded metadata is used for flexible settings and context, including workflow definitions. Query supported JSON through JsonFieldBehavior or a purpose-built PostgreSQL expression. Security decisions, foreign keys, heavily filtered values, and unique data should be normalized columns.

Referential integrity

Use foreign keys, unique constraints, check constraints, and indexes where the supported PostgreSQL migration APIs can express them. CakePHP validation improves messages but does not replace database constraints under concurrent writes. Use nullable foreign keys deliberately and define delete behavior explicitly.

Query and performance rules

Begin collection queries with applyScope() or the equivalent table policy.
Select/contain only what a screen or service needs and avoid template queries.
Add indexes from measured query plans and realistic tenant sizes; include workflow/queue concurrency paths.
Use TenantAwareCache for result caching and include every semantic input.
Never issue a cross-tenant join or construct database names from request data.
Run bulk tenant work one explicit tenant scope at a time with bounded memory and retry behavior.

Finding the authoritative detail

Need	Source
Current fields and constraints	latest core/plugin/platform migrations
Associations and validation	`*Table.php` classes
Entity accessibility/virtual fields	entity classes
Authorization reach	policies and policy tests
Workflow state	workflow engine services and definitions
Seeded local examples	reset/seed code and `app/tests/TestDataReference.md`
Generated class APIs	API reference portal

Do not copy a production schema dump into documentation. It is immediately stale, may disclose operational detail, and cannot describe service-level invariants.