Data model and tenancy

One database, one schema per domain

Invictus Academy keeps its durable state in a single PostgreSQL database. Each bounded context in the modular monolith owns exactly one schema, and no module reads or writes another module's tables; they integrate only through in-process interfaces. That keeps the blast radius of any change small and makes a future service extraction cheap (see ADR-006).

The schemas track the product's lifecycle, from who is logged in through to how a run is graded and what forensic case it produced. The lab catalogue is the one shared, global domain; everything a tenant generates is scoped to that tenant.

• iam: organisations, memberships, the user projection, roles, invitations and connectors
• catalog: labs, versions, challenges, objectives and assets - the global product content, published as checksum-verified bundles that a publisher may also sign
• runs: live lab runs and their events, range leases and pool members, lab sessions, CTF solves, submitted report deliverables and their instructor verdicts, and each run's grade
• telemetry: normalised events, collected raw events and a dead-letter queue, plus references to preserved raw bytes
• forensics: cases, evidence items and the hash-chained custody ledger, whose head entry also carries the seal signature when signing is configured
• audit: the append-only tenant audit trail of security-relevant actions
• preferences: per-user interface preferences
• platform: the transactional outbox, the global publisher trust set, and migration state

Per-tenant isolation, enforced twice

Every tenanted table carries a non-null tenant_id, and isolation is enforced in two independent layers. The application's authorization layer is the primary control and decides what a request may touch. PostgreSQL Row-Level Security sits underneath as a defence-in-depth backstop, so a query that forgets its tenant filter still cannot leak another tenant's rows (ADR-006). Both layers have to fail before cross-tenant data crosses.

On each request the API reads the validated tenant claim and sets a transaction-local session variable; RLS policies on every tenanted table filter against it. Because the variable is transaction-local, a pooled connection cannot carry one request's tenant into the next, and the application connects as a role that is neither the table owner nor allowed to bypass RLS.

The lab catalogue is deliberately outside this scheme. Labs and their content bundles are shared product content, so they carry no tenant_id; which tenant may run which lab is an entitlement join, not a row filter. Where a bundle was signed by a trusted publisher, the verifying key id is recorded on the lab as provenance.

Identity and authorization are separate concerns

A self-hosted Keycloak proves who a user is; it does not decide what they may do. Keycloak is the OIDC identity provider for both surfaces (the SPA and the API). It is capable of brokering an organisation's own corporate IdP, and no organisation is federated today: the platform holds no identity-provider configuration and its Keycloak credential is deliberately scoped so it could not create one (ADR-051). The API is a pure resource server: it validates the RS256 token against Keycloak's published keys, checks issuer, audience, expiry and realm, and mints no tokens of its own.

Authorization is owned by the application. Keycloak seeds a user's initial roles on first login, but from that point the app is the source of truth for both their roles and an active flag (ADR-042). The per-request check reads the stored app roles rather than the token, so a role change takes effect on the user's next request without any Keycloak administration, and a deactivated user is refused before any route runs.

Access is always role plus tenant: the role answers what capability, the tenant claim answers whose data. ADR-005 splits Keycloak into a staff realm and a tenants realm as a blast-radius boundary, so that a compromised pilot IdP would be contained to tenant identities. Only the tenants realm is built, and no pilot IdP is federated yet, so that boundary is a design the deployment has not needed and does not yet have.

Team management the tenant owns

Because the app owns authorization, a tenant's org_admin manages their own team with no Keycloak administrator in the loop: grant or revoke the org_admin role, deactivate a departed member, reactivate a returning one. Deactivation is an application-level denial rather than a session kill, since the app cannot end someone's Keycloak session; the app simply refuses their requests.

The identity row is resolved before a tenant context exists, so team-management routes are gated on a capability rather than a role name, and carry an explicit tenant filter instead of relying on the RLS backstop; a query for another tenant's user returns a 404. Reading the directory is one capability and changing who is in it is another, which is how a manager opens a colleague's training record without being able to invite, remove or promote anybody.

A safety rail keeps a tenant from ever losing its last active org_admin. An admin cannot change their own roles or deactivate themselves, and concurrent cross-removals are serialised at the database with a per-tenant advisory lock plus a post-update count, so two admins cannot demote each other down to zero.

Where the durable facts live

Postgres is the system of record for relational state. Large bytes live in object storage, and relational rows hold references and hashes rather than the bytes themselves; evidence and raw captures sit on a separate storage surface with its own access boundary (write-once retention is a production-deployment control). Valkey is the cache and async job substrate, not a system of record, so every durable fact still has its home in Postgres or object storage. Scoring is native and lives in this same Postgres: a CTF flag is graded server-side in-process and the grade is written to the run row in the same transaction as completion, so there is no separate scoring platform or second datastore (ADR-053).

The forensic custody ledger is hash-chained and tamper-evident. It is training documentation built to be verifiable, not court-admissible evidence, and that distinction is deliberate.

Trainee personal data lives only in the mutable Postgres store and never in the evidence store. Custody, audit and evidence rows reference pseudonymous actor IDs, so an erasure request is honoured by breaking the link to a real person rather than rewriting the ledger, and the ledger stays verifiable (ADR-023).