Stralos Help Centre

The organisation-security work establishes asymmetric identity and database-enforced tenant isolation. This page describes the implemented M1a, M1b.1, M1b.2, and M2–M5 controls.

Implemented milestones

Milestone	Source implementation
M1a	Dual HS256 and EdDSA verification, strict user and service claim schemas, public JWKS, and bounded service-token minting.
M1b.1	New user access tokens use EdDSA; feature clients delegate 401 refreshes to the shell. The legacy HS256 verification branch remains.
M1b.2	Global, Estimate, Activities, Documents, Rates, and Assistant removed their legacy HS256 branches and rollout flags. Scheduler was already EdDSA-only. All seven runtime services enforce EdDSA/JWKS verification on protected routes.
M2	Five Global API tenant tables carry `organisation_id`, enforce FORCE RLS, and use separate owner, runtime, and platform database connections.
M3	All Estimate API tenant tables carry `organisation_id`, enforce FORCE RLS, and use the same three-connection pattern. The subcontractor portal resolves organisation context via a SECURITY DEFINER function owned by the platform role.
M4	All twelve Documents service tenant tables carry `organisation_id` and enforce FORCE RLS. Composite foreign keys prevent child rows from binding to a parent in a different organisation. Project membership enforcement adds a second restrictive RLS layer scoped to the caller’s accessible project IDs, verified per request from the Global API.
M5	Rates prices and project overrides, plus the Activities recipe library, are tenant-isolated with FORCE RLS and composite ownership constraints. Activities writes require an organisation admin.
Org-catalogue M2	Global API rate categories and setting-list vocabulary keys are organisation-owned. Mixed setting-list rows preserve suite-global, platform-managed values. Two of the original vocabulary keys (`company_locations`, `labour_rate_multipliers`) are now retired. Migration `0053` adds `rate_group_labels` for organisation-specific rate-group display names.
orgcat-m1	Rate items are org-scoped. Each organisation owns its own rate catalogue. FORCE RLS is enabled on `rates_rate_items`; writes require the org `admin` role; composite foreign keys bind prices and overrides to a same-organisation item.
orgusers-m1	A new `organisation_memberships` table records each user’s membership of an organisation (role, status). The `users.organisation_id` column is renamed to `home_organisation_id` and made nullable. Existing users are backfilled to exactly one membership. `GET /users` scopes its result set to active membership holders via a SECURITY DEFINER function. Token minting still reads from `home_organisation_id` (no behaviour flip until M2).
Org-users M2	Mint-from-membership: OTP verify, token refresh, and the new switch-org endpoint source `(org, role)` from `organisation_memberships` rather than the legacy `users.role` / `home_organisation_id` columns. Every mint site performs an atomic re-check before signing. The session model is bumped to version 2; all prior sessions are invalidated on their next refresh.
Org-users-directory M3	The shell’s always-present organisation switcher fetches the signed-in user’s live organisation list from the Global API. It shows the active organisation and each role, then uses the auth bridge’s `switchOrg` seam and reloads after a successful switch. Persisted memberships remain a cache and current-organisation label input rather than the switcher’s list source. See Frontend architecture — org switcher.
Admin-events M4	The Global API verifies signed admin provisioning events, applies versioned projections idempotently, exposes a replica-read contract, and sends user-directory changes through its outbox worker. See Admin provisioning.
IDP-M1	`POST /api/global/auth/oidc/exchange` — server-side OAuth2 authorisation-code exchange with the regional identity provider. The API redeems the code, verifies the ID token, binds the provider subject to the user row (`users.idp_subject`), applies at-login activation and membership-acceptance transitions, and returns the same session shape as OTP verify. See IDP-M1 — OIDC exchange.
IDP-M2a	The runtime→admin report channel (`POST /reports/runtime`). The runtime reports user activations and membership acceptances as signed JWS commands. Admin validates each report against its own state, applies the transition on its own authority, and emits a corrective event for every rejection it can describe. The `membership.status_changed` event type was added to carry acceptance transitions. Every membership event now carries `invitationGeneration` to prevent stale acceptance reports from reinstating revoked access. Reconciliation gained the §6.2 asymmetric corrective rule, which suppresses reversions of in-flight acceptances and alerts with a named diagnosis instead. See Admin provisioning — Report channel.
IDP-M3b (api-global)	The `directory.regions_replaced` event is added to the admin-events contract on its own `directory` aggregate. Receiving this event enqueues a row in `directory_replacement_outbox`. The `directory-flush` Lambda is replaced: it now drains that queue with versioned `TransactWriteItems` into the userId-keyed DynamoDB user-directory table. The old email-keyed claim/lease pipeline and `global_directory_outbox` table are removed. The replica route adds `directory` as a supported aggregate type; only the directory-writer region answers it. The writer region is identified by the new `DIRECTORY_WRITER_REGION_KEY` environment variable. See Admin provisioning — directory replacement outbox.
IDP-M4a	Originally added the personal `/settings/security` page and a separate regional OAuth settings ceremony. The regional page, ceremony, Cognito client, and `__STRALOS_SECURITY__` bridge were subsequently removed. The personal security surface now runs on the login host through `stralos-login-web` and `stralos-api-login`; sign out everywhere uses the security broker’s live step-up cookie and CSRF echo. This is not an organisation SSO configuration surface. See Frontend architecture — Personal security settings.
IDP-M7	Adds `users.idp_subject` (nullable `text`) to the admin database with a partial unique index `WHERE idp_subject IS NOT NULL`. The login service binds the value at first authentication using a compare-and-swap from `NULL`. The reconciler and sweeper void the binding when Cognito recreates a user account. Migration `0015` also permits the login service to activate an invited canonical user after successful authentication. See IDP-M7: IdP subject binding.
IDP-M7a	Adds explicit `"identity"` and `"org"` session kinds and session model version 3. Central refresh rejects an identity session with typed `403 identity_session_cannot_refresh`; central logout and switch-org accept it.
IDP-M7c (shell receiver)	Registers `/auth/handoff`, scrubs its single-use ticket before observability starts, redeems the parked session through `api-login`, and persists the result. See Frontend architecture — IDP-M7c hand-off receiver.
Admin API resource policy	Adds IAM-authorised method chains and principal-restricted resource-policy guards for the admin report, signup, and organisation machine-to-machine paths. See Admin provisioning — API Gateway resource policy.
PRD 07 — organisation slug consumer	Adds a nullable slug projection to the Global API for compatibility with pre-slug events and exposes `GET /api/global/auth/tenant/:slug`. Current admin-api organisation events carry the slug; older events may omit it without clearing a projected value. See Tenant lookup.
PRD 07 — organisation slug producer	Adds `organisations.slug` to the admin store: globally unique, NOT NULL, immutable once issued, and carried on every `organisation.*` event. `POST /organisations` derives a slug from the organisation name or accepts an explicit `slug` field. The backfill migration (`0025`) stamps pre-existing organisations so the runtime can resolve them. See Organisation slug.
Org self-service relay	The Global API now relays organisation user-management requests to `stralos-admin-api`. The shell’s Users panel calls `/api/global/org/`; the Global API forwards each request to `stralos-admin-api`’s `/api/admin/org/` over SigV4. The caller’s token travels in `x-stralos-user-authorization` because SigV4 claims the `Authorization` header. `stralos-admin-api` verifies the token against the region’s JWKS and enforces all membership rules; the relay adds only the transport credential. Readable responses retain their upstream status and body. Empty or unreadable responses receive safe replacement bodies, and an unreadable successful response becomes `502`. See Organisation user self-service.
Org membership role and removal relay	`PUT /org/users/:userId` now relays membership role changes in addition to name updates. `DELETE /org/users/:userId` is added to relay member removal. Both requests are forwarded to `stralos-admin-api`’s corresponding `/api/admin/org/users/:userId` routes over SigV4. `stralos-admin-api` owns the membership record and enforces all removal and role-change rules; the relay adds only the transport credential. The billing entitlement check applies to both routes when billing enforcement is enabled. See Organisation user self-service — PUT /org/users/:userId and DELETE /org/users/:userId.
Org admin self-rename	`PUT /api/admin/org/organisation` lets an organisation administrator rename their own organisation. The route is in its own Fastify plugin under `registerOrgAdminAuth` (separate from the member-accessible `GET /org/organisations` switcher read) so Fastify encapsulation keeps the rename inaccessible to ordinary members. Authority is re-asserted inside the write transaction under the membership share lock. The organisation row is taken FOR UPDATE to serialise against concurrent operator updates. Only `name` is accepted; `timezone`, `homeRegionKey`, `slug`, and `status` are absent from the schema and stripped by Zod. The handler enqueues an `organisation.updated` event targeting the organisation’s home region so the organisation’s home-region api-global projection picks up the new name. See Organisation admin self-rename.
Organisation rename relay	`PUT /org/organisation` is added to the self-service relay. It forwards to `stralos-admin-api`’s `PUT /api/admin/org/organisation`. The rename requires an organisation administrator; `stralos-admin-api` owns the authority check. The body is passed through unvalidated so `stralos-admin-api`’s schema is the single enforcement point. The rename is applied immediately in admin-api and reaches this region’s `organisations` projection through an admin event, so `GET /organisation-settings` may report the previous name for a short time. See Organisation user self-service — PUT /org/organisation.
Billing access enforcement	The Global API auth plugin now registers the billing access guard (`registerBillingAccessGuard` from `@stralos-co-nz/api-common/billing`) for authenticated user write operations. The guard checks a DynamoDB billing entitlements table, keyed by `ORG#<organisationId>`, before allowing writes to proceed. The check is gated by the `enable_billing_enforcement` Terraform variable (default: `false`); when the variable is `false`, the Lambda execution role receives no DynamoDB grant and the guard is inert. When enabled, writes that fail the entitlement check are refused. The entitlements table name is derived from the `project_name` and `environment` Terraform variables.
Organisation name on settings	`GET /organisation-settings` and `PUT /organisation-settings` now include `organisationName` in their response. The field is a projection read from the `organisations` table inside the same transaction as the settings row; it is not a setting and cannot be changed through this endpoint. `null` means the organisation has not yet been projected into this region. See Organisation settings — GET /organisation-settings.
Organisation country code	Migration `0058_organisation_country.sql` adds a nullable `country_code` (ISO 3166-1 alpha-2) column to `organisations`. The admin-events organisation payload accepts `countryCode` as an optional field; the apply step stores it when present and preserves the stored value when absent. `GET /organisation-settings` and `PUT /organisation-settings` now include `countryCode` in their response as a read-only projection alongside `organisationName`. `null` means the country has not been set or projected into this region. See Organisation settings — GET /organisation-settings.
PRD 04 M6 — organisation default location ID	Adds `default_location_id` (UUID FK) to `organisation_settings`, backfills unambiguous label-to-location matches, and exposes only `defaultLocationId` through the API. The guarded CONTRACT migration refuses to proceed while a legacy label lacks a stable ID, then drops `default_location_label`; all current consumers resolve by location ID. Retired: the organisation default location gave way to a per-user default office, and migration 0082 dropped `default_location_id`. `defaultLocationId` is still on the wire as a deprecated key: GET always answers `null` and PUT ignores it.
Billing access guard	`registerBillingAccessGuard` is wired into the Global API auth plugin. When a billing grace period expires, the guard blocks all product access for the organisation. Two recovery endpoints are exempted: `GET /org/billing-seat-usage` (admin seat count) and `GET /org/organisations` (organisation switcher). See Billing access guard.
Org admin membership management	`PUT /api/admin/org/users/:userId/role` and `POST /api/admin/org/users/:userId/remove` allow an organisation administrator to change a member’s role or revoke their access. Both routes are registered under `registerOrgMembershipChanges` inside the `orgUserRoutes` plugin (already protected by `registerOrgAdminAuth`). Authority is re-checked inside the write transaction via `assertOrgAdminInTx` under the residency and per-user derivation locks. Demoting or removing the last active administrator is refused with `409 last_organisation_admin`. A revoke disables the membership row and, when the target’s `defaultOrganisationId` was this organisation, clears that field and replicates a `user.updated` event. A role change emits `membership.role_changed`; a revoke emits `membership.revoked`. Both operations write an audit row.
Rates project membership RLS	All Rates database tables carrying a `project_id` column now enforce a `project_membership` FORCE RLS policy. The policy reads `app.project_ids` — a per-transaction GUC set by `runScoped` after fetching the user’s accessible project IDs from api-global’s `/api/global/projects/access-scope` endpoint — and allows access only to rows whose `project_id` is null (library items) or present in that list. See Rates project membership access control.
Project membership	Migration `0060_project_membership.sql` adds explicit project-level membership tables: `project_role_definitions`, `project_members`, `project_member_roles`, `project_access_requests`, `project_access_events`, and `project_lead_departures`. Users must hold a project membership to enter a project; the roles are `member` and `lead`. Organisation administrators can manage any project, but still require a membership row to enter it. `project_lead_departures` carries FORCE RLS with an owner-only policy so the sweeper can read pending departure intents using the migration owner connection. See Project access for the API contract.
Project history RLS	Migration `0061_project_history_rls.sql` activates FORCE RLS on `project_status_history`. The table carries no `organisation_id` column; its tenant policy resolves the owning organisation by joining through `projects` via `public.project_org()`. The migration owner receives an unrestricted owner policy so the lifecycle sweeper’s pending-intent discovery path continues to work. See Project history RLS policy.
Admin project list only (0068)	Migration `0068_admin_project_list_only.sql` corrects an over-widening introduced by migration `0067`. `0067` broadened the `projects` SELECT policy and the four child tables’ read policies to `project_can_read()`, which gave an unassigned organisation administrator full read access to every project route, not only the list. `0068` narrows those policies back to `project_has_access()` (membership-required) and introduces `project_register`, a `security_barrier` view owned by the migration role. The view applies `project_can_read()` — which includes active org admins — but only for listing: `GET /projects` reads the view; all other project reads (`GET /projects/:id`, contract profile, stats, statuses, and location routes) continue to go through `projects` directly, where RLS requires project membership. `SELECT` is the only grant on the view to prevent writes from running under the migration role’s identity. See Project access — Access rules.
Data retention and user erasure (0072)	Migration `0072_org_purge_and_user_erasure.sql` adds the `org_offboarding` purge clock, the `user_erasures` audit table, and a nullable `organisations.purged_at` column, and grants the platform role the `DELETE` authority the purge needs. The retention Lambda runs the weekly organisation purge and the `erase-user-lookup` and `erase-user` events. See Data retention and user erasure.
Contractor companies (0086)	Migration `0086_contractor_companies.sql` adds the identity-classified `company_contractor_links` projection, `organisation_memberships.via_link_id`, `organisations.created_by_organisation_id`, copy provenance columns on seven tables, and the tenant-owned `contractor_copy_jobs` and `contractor_copy_job_steps` tables. It also splits the organisation name unique index in two. See Contractor companies.
Contractor project visibility (0088)	Migration `0088_contractor_project_visibility.sql` adds `project_actor_contractor_guest()` and narrows the project helpers so a contractor person sees only the projects they are on, except in a company their contractor created and still manages. See Contractor project visibility.

The Global API’s POST /organisations remains frozen until M6 and returns 403; organisation creation belongs to the admin API’s operator route. The initial regional Stralos projection was created directly by the seed:auth task.

Identity model

User tokens always carry an organisation UUID. Tenant service tokens also carry an organisation UUID. Platform service tokens instead carry org: null and the platform scope. Global, Estimate, Activities, Documents, Rates, Scheduler, and Assistant verify EdDSA tokens against the Global API JWKS. All except Scheduler accept user or authorised service tokens and require a service token’s audience to include the receiving service’s configured name. Scheduler accepts user tokens only. See Auth token architecture and Service token authentication for the full claim and mint contracts.

Tenant tables

The Global API enables and forces row-level security on:

clients
projects
suppliers
contractors
project_contract_profiles

Every row has a non-null organisation_id. Composite foreign keys prevent a project from referencing a client in another organisation and prevent a contract profile from referencing a project in another organisation.

RLS policy

All five tables use this policy shape:

USING (organisation_id = NULLIF(current_setting('app.current_org', true), '')::uuid)
WITH CHECK (organisation_id = NULLIF(current_setting('app.current_org', true), '')::uuid)

An unset or empty app.current_org resolves to NULL, so the policy matches no tenant rows. For an authenticated tenant request, withTenant opens one transaction and runs:

SELECT set_config('app.current_org', '<organisation-uuid>', true)

The final true makes the setting transaction-local. Every scoped query must use the transaction handle so the organisation context cannot leak through connection pooling.

Project history RLS policy

project_status_history has no organisation_id column. Its tenant policy resolves the owning organisation by joining through projects:

USING (EXISTS (
  SELECT 1 FROM public.projects p
  WHERE p.id = project_id
    AND p.organisation_id = public.project_org()
))
WITH CHECK (EXISTS (
  SELECT 1 FROM public.projects p
  WHERE p.id = project_id
    AND p.organisation_id = public.project_org()
))

public.project_org() is a helper that returns the GUC-bound organisation UUID, equivalent to NULLIF(current_setting('app.current_org', true), '')::uuid. Two policies exist on this table:

Policy	Role	Access
`project_history_owner`	Migration owner (`current_user`)	`USING (true)` — unrestricted. Allows the lifecycle sweeper to discover pending history rows using the owner connection.
`project_history_tenant`	`stralos_runtime_global`	Join-through-projects check above. Runtime queries see only rows whose project belongs to the current organisation.

FORCE RLS is enabled, so the owner policy is also applied when the owner connection queries the table without an advisory bypass.

Request routing

Caller state	Database path	Behaviour
Authenticated with an organisation	RLS-subject runtime connection	Sets the transaction GUC and runs tenant queries under FORCE RLS.
Authenticated without an organisation	None for tenant handlers	Returns `403`.
Anonymous	None for protected handlers	Returns `401`.

Tenant writes reject anonymous requests with 401 and null-organisation service tokens with 403 before a database query runs. Platform scope does not automatically select a database connection. The runtime writers for user provisioning, app configuration, and suite-global reference lists are retired; after authentication and authority gates they return 410 admin_owned, and stralos-admin-api owns those control-plane mutations. A platform token cannot use normal tenant handlers.

Database principals

stralos-core provisions three connection classes for each service database and stores their connection URLs in SSM:

Global database role	`BYPASSRLS`	Purpose
`stralos_global_owner`	No	Drizzle migrations and the organisation backfill.
`stralos_runtime_global`	No	Authenticated request path subject to FORCE RLS.
`stralos_platform_global`	Yes	Explicit platform-scoped operations.

The Global API service stack reads the owner, runtime, and platform SSM parameter names from the core remote state. If a runtime or platform URL is absent locally, the process falls back to the owner connection. That fallback is for local or transitional use; deployed request traffic is expected to receive the dedicated URLs. Tenant queries also include explicit organisation predicates. These produce useful 404 behaviour for a foreign-organisation ID and provide defence in depth. FORCE RLS remains the database security boundary.

Organisation-aware constraints

Business uniqueness is scoped by organisation:

client name;
project reference;
supplier name and address.

The same value may exist in different organisations, while a duplicate within one organisation still conflicts.

M3: Estimate API tenant isolation

At M3, all Estimate API tenant tables received organisation_id, FORCE RLS, and the same three-connection runtime pattern used by the Global API.

Estimate tenant tables

All twenty estimate tenant tables carry a non-null organisation_id:

estimate_estimates
estimate_sections
estimate_takeoff_measurements
estimate_work_items, estimate_work_item_components, estimate_work_item_pricing, estimate_work_item_tags
estimate_subcontract_packages, estimate_subcontract_bid_rates
estimate_programme_items, estimate_programme_dependencies
estimate_tender_clauses
estimate_baselines, estimate_baseline_items
estimate_variations, estimate_variation_lines, estimate_variation_revisions, estimate_variation_events, estimate_variation_assessments, estimate_variation_counters

All twenty tables use FORCE RLS with the same app.current_org GUC policy as the Global API and Rates API. Two tables are deliberately excluded from RLS (estimate_status_history, estimate_revisions): they carry no organisation_id and are reachable only after an org-scoped estimate resolution, so tenancy is enforced at the access path rather than the row level.

Estimate request routing

The estimate service uses the same runScoped runtime layer as the Global API, with one addition. runScoped calls resolveProjectScope before opening the transaction and sets a second transaction-local GUC:

SELECT set_config('app.project_ids', '<json-array-of-project-uuids>', true)

Project membership is resolved from the Global API on every request and is never cached beyond the transaction.

Caller state	Database path	Behaviour
Authenticated with an organisation	RLS-subject runtime connection	Sets `app.current_org` and `app.project_ids`; every query runs under FORCE RLS.
Authenticated without an organisation (platform or null-org service token)	None for tenant handlers	Returns `403`.
Anonymous	None	Returns `401`.

Subcontractor portal

The subcontractor portal routes (/api/estimate/subcontract-portals/:token…) require a signed-in user account. Each portal operation runs under runScoped, so membership and tenant scope are checked for every request, and the token only selects the package within the caller’s organisation. The public.resolve_org_for_portal_token(token) SECURITY DEFINER function that once resolved an unauthenticated portal token to an organisation has had no caller since the portal moved to signed-in users. It was retired on 2026-10-11 (api-estimate #448): scripts/drop-portal-resolver.mjs drops it on the platform connection after every db:migrate, because only its owner, the platform role, may drop it.

Estimate database principals

The Estimate service uses the same three-connection pattern as the Global API:

Role	`BYPASSRLS`	Purpose
Owner (migration role)	No (superuser locally)	Drizzle migrations via `scripts/migrate-orgsec-waves.mjs`.
`stralos_runtime_estimate`	No	Authenticated request path, subject to FORCE RLS. Also reads/writes `estimate_status_history` and `estimate_revisions` (no RLS, plain table grants).
`stralos_platform_estimate`	Yes	Platform-scoped operations. Drops the retired `resolve_org_for_portal_token` function after migrations.

Estimate organisation-aware constraints

Uniqueness constraints are scoped by organisation where necessary. The constrain migration (0016) recreates several previously global unique indexes with organisation_id included so the same reference, revision number, or pricing row can exist in different organisations without conflict.

Migration sequence

See Org-security M3 backfill and migration — Estimate API in the Runbooks for the full deployment sequence.

Project membership enforcement (Estimate API)

Migration 0063_project_membership.sql adds a second RLS layer on top of M3 organisation isolation. After this migration, every estimate row and its FK descendants are also gated by the caller’s project membership — not just their organisation.

Estimate membership resolution

runScoped calls resolveProjectScope before opening the transaction. resolveProjectScope fetches the caller’s current project membership from the Global API at GET /api/global/projects/access-scope and returns an array of project UUIDs. It then sets a second transaction-local GUC:

SELECT set_config('app.project_ids', '<json-array-of-uuids>', true)

A new project_scope_allows(p_project uuid) SQL function tests whether a given project UUID appears in that JSON array:

SELECT p_project IS NULL OR p_project::text IN (
  SELECT jsonb_array_elements_text(
    coalesce(nullif(current_setting('app.project_ids', true), ''), '[]')::jsonb
  )
)

A NULL project UUID is treated as unrestricted — rows that carry no project context remain visible within the org. An unset or empty app.project_ids resolves to an empty array, so an authenticated caller who holds no project memberships sees no estimate rows.

RLS policies

Migration 0063 installs project_membership restrictive policies on every table that carries a project_id column. The migration walks FK descendants transitively, so child tables (work items, sections, takeoff, and all other estimate-owned rows) inherit the restriction through parent joins rather than needing their own project_id column. Two tables that were already excluded from organisation RLS (estimate_status_history and estimate_revisions) receive separate permissive policies that derive access through their parent estimate row. An unauthenticated-looking read of history after project membership is revoked returns zero rows. A second set of write-only restrictive policies (project_parent_insert_* and project_parent_update_*) prevents a caller from binding a child row to a parent in a project they cannot see. FK checks bypass RLS, so without these policies a cross-project insert would succeed at the database layer even though the parent is invisible to the caller.

Estimate membership errors

When resolveProjectScope cannot verify project membership, the handler returns an error before any database query:

HTTP	Code	Cause
`401`	`project_access_required`	No bearer token supplied.
`403`	`project_access_required`	Bearer token is valid but project verification failed (for example, the token carries an org that does not match the Global API’s response).
`503`	`project_access_unavailable`	The Global API could not be reached within 5 seconds. The response carries `Retry-After: 5`.

A 42501 PostgreSQL error from the database layer (policy rejection) is caught by the route error handler and returned as 403 with no database details in the body.

Service token path

Service tokens that carry an x-forwarded-user header forward the signed user token to the Global API so project membership is resolved for the acting human. Service tokens without a forwarded user derive the project scope from projectWorkForRequest — the project bound to the current lifecycle work item — and fall back to an empty scope if no project context is available.

Access scope endpoint

GET /api/estimate/access-scope returns the list of estimate IDs visible to the caller under fresh project membership. Use this endpoint to synchronise a client’s estimate list with the server’s current membership view without re-fetching each estimate individually. See Estimate API — Resolve estimate access scope.

M4: Documents service tenant isolation

At M4, the Documents service (stralos-api-documents) joined FORCE RLS. Twelve tables now carry organisation_id and enforce RLS. Nine tenant tables require a non-null organisation; three system-capable tables keep it nullable.

Documents tenant tables

The following tables have FORCE RLS enabled with a tenant_isolation policy on both USING and WITH CHECK:

documents_documents
documents_extractions
documents_area_runs
documents_suggestions
documents_drive_sources
documents_sync_connections
documents_synced_files
documents_sync_folders
documents_sync_runs

Three tables — documents_outbox, documents_processing_events, and documents_variation_assessment_jobs — have FORCE RLS with a USING-only tenant_or_system policy. Their organisation_id is nullable so system-originated rows (those with no document or org context) remain reachable only via the platform BYPASSRLS connection.

Composite foreign keys

Every same-database child table uses a composite (parent_id, organisation_id) foreign key rather than a plain parent_id key. This means a child row physically cannot bind to a parent in a different organisation:

documents_extractions → documents_documents (id, organisation_id)
documents_area_runs → documents_documents (id, organisation_id)
documents_suggestions → documents_documents (id, organisation_id)
documents_suggestions → documents_area_runs (id, organisation_id)
documents_sync_connections → documents_drive_sources (id, organisation_id)
documents_synced_files → documents_sync_connections (id, organisation_id)
documents_synced_files → documents_documents (id, organisation_id) × 2 (current and attempt)
documents_sync_folders → documents_sync_connections (id, organisation_id)
documents_sync_runs → documents_sync_connections (id, organisation_id)

The two synced_files → documents edges use ON DELETE SET NULL (document_id) / (attempt_document_id) (PostgreSQL 15+) so the organisation_id column survives a document delete.

S3 key uniqueness exception

documents_documents.s3_key remains globally unique (not org-composite). Every key embeds a server-generated document UUID, so a cross-organisation collision cannot arise. Keeping the global unique also allows the org-less pipeline worker to resolve exactly one row per S3 key without a GUC set.

Documents request routing

Every tenant route handler — document CRUD, document processing, suggestions, takeoff, and sync admin routes — uses runScoped, which selects the correct connection and pins the org GUC before any query runs.

Caller state	Database path	Behaviour
Authenticated with an organisation	RLS-subject runtime connection	Sets the transaction GUC; every query runs under FORCE RLS.
Authenticated without an organisation (platform or null-org service token)	None for tenant handlers	Returns `403`.
Anonymous	None for protected handlers	Returns `401`.

The owner connection is not used on the authenticated request path. In deployed environments the owner role is RLS-subject (not a superuser), so any route that ran on the owner pool without a GUC set would return empty rows or be rejected by FORCE RLS. All route handlers are bound to runScoped so the GUC is always set for authenticated requests.

Write gate

Tenant writes require an authenticated caller with a non-null organisation UUID. Anonymous callers receive 401; platform or null-org service tokens receive 403. requireCallerOrg enforces this before any database query on write routes. When billing enforcement is active, the billing access guard (registerBillingAccessGuard) runs after token verification and before requireCallerOrg. It checks the caller’s organisation against the BILLING_ENTITLEMENTS_TABLE DynamoDB table. The guard is a no-op when BILLING_ENTITLEMENTS_TABLE is empty (the default). The enable_billing_enforcement Terraform variable in billing-entitlements.tf controls whether the table is provisioned and the variable is seeded. See Authentication in the document AI pipeline page for the full configuration details. The auth plugin also parses the optional plan and via claims. For a contractor person (a token with via), the guard additionally checks the company link and the contractor company’s own billing, as described in Contractor billing guard.

Worker database access

Pipeline workers use a two-pool model distinct from the request-path pools:

Discovery pool (DATABASE_URL, owner connection) — directly reads the stored organisation, project, and estimate routing metadata for the message’s document, connection, job, object key, or Textract job ID. Envelope-provided organisation context is treated as an untrusted candidate and must agree with the stored row.
Runtime pool (RUNTIME_DATABASE_URL, stralos_runtime_documents) — used for all handler work. withWorkScope checks out a connection, sets app.current_org, app.project_ids, app.estimate_ids, and app.worker_scope as transaction-local GUCs, and passes a FORCE-RLS-scoped handle to the handler. For document-keyed messages, the runtime dispatcher independently calls resolve_org_for_document; if the resolver is absent or errors, it logs and drops the message. The GUC cannot leak across jobs or pooled connections.

Maintenance tasks (sweep, sync dispatch) follow the same pattern via runScopedMaintenance, which iterates known tenant and project partitions and scopes each one through the runtime pool. Workers do not use stralos_platform_documents. The resolve_org_for_document function is owned by the BYPASSRLS platform role and is created post-migrate by scripts/bootstrap-doc-resolver.mjs.

Business uniqueness

Organisation-composite unique indexes replace the previous global ones:

(organisation_id, source_key) on documents_drive_sources
(organisation_id, source_id, folder_id, project_id) on documents_sync_connections
(organisation_id, document_id) on documents_extractions
(organisation_id, document_id, area) on documents_area_runs

Documents database principals

The Documents service uses the same three-principal model as the Global API:

Role	`BYPASSRLS`	Purpose
Owner (migration role)	No (superuser locally)	Drizzle migrations and the organisation backfill.
`stralos_runtime_documents`	No	Authenticated request path, subject to FORCE RLS.
`stralos_platform_documents`	Yes	Platform-scoped operations.

Project membership enforcement

Migration 0014_project_membership.sql adds a second layer of row-level security to Documents tables — project membership — on top of the existing organisation isolation. Every table that carries a project_id column, and every table that references one of those tables through a foreign key, is protected by a restrictive project_membership RLS policy scoped to stralos_runtime_documents.

Documents membership resolution

At the start of every authenticated request, runScoped calls the Global API’s GET /api/global/projects/access-scope endpoint and receives the list of project IDs the caller can access. That list is written into the app.project_ids transaction GUC alongside app.current_org. The project_scope_allows(project_id) SQL function (installed by the migration) checks whether a given project UUID appears in that JSON array. When project_id is NULL (company documents), the function returns true so company documents are unaffected by this policy. Direct project_id tables use the function predicate directly. Descendant tables — those that have no project_id column but are reachable through a foreign key from a protected table — use an EXISTS subquery that walks back to the nearest protected parent. The migration builds these descendant policies dynamically by traversing the foreign-key graph. Tables that carry a project_id are the roots; each level of FK descendants receives a project_parent_<fk_oid> policy. Three tables receive hand-written policies rather than the dynamic ones:

documents_processing_events — linked by document_id rather than project_id.
documents_variation_assessment_jobs — linked by estimate_id, using a separate app.estimate_ids GUC set from the Estimate API’s GET /api/estimate/access-scope endpoint.
documents_outbox — ownership is encoded as detail->>'documentId' or detail->>'jobId' in JSON rather than a foreign key column. The policy walks through documents_documents or documents_variation_assessment_jobs to assert ownership.

Worker connections set app.project_ids, app.estimate_ids, and app.worker_scope alongside app.current_org. All GUCs are SET LOCAL so they cannot leak across pooled connections. The project_document_event policy also allows maintenance workers to write processing events for null-project documents when app.worker_scope = 'true'.

Documents membership errors

The project scope check runs before any query. If the check fails, the request is rejected before the database is touched:

Condition	Status	Code
Global API returned `401` or `403`	`403`	`project_access_required`
Global API unreachable or returned an unexpected error	`503` (`Retry-After: 5`)	`project_access_unavailable`

When a query runs and the RLS policy denies the row, the handler catches the 42501 PostgreSQL error code and returns 403 with code: project_access_required and message You do not have access to this project. A 503 with code: project_access_unavailable is transient. Retry the request after the Retry-After interval.

`RUNTIME_DATABASE_URL` requirement

The project membership policies are applied to the stralos_runtime_documents role only. The service startup fails with an explicit error if RUNTIME_DATABASE_URL is not set; a deployed environment must supply this variable for all API and worker Lambdas. See Project-membership enforcement for the worker runtime and Documents API: project-membership migration for the cutover procedure.

M5: Activities service tenant isolation

At M5, the existing Activities service (stralos-api-activities) changed from a suite-wide seed library to a tenant-owned recipe library in its own database.

Activities tenant tables

Both tables carry a non-null organisation_id stamped from the caller’s JWT at write time:

activities_activities
activities_activity_components

FORCE RLS is enabled on both. The policy uses the same app.current_org GUC pattern as the Global API.

Write access gate

Activities and components are a recipe library that members and admins both maintain. Deleting an activity and the rate-sheet writes stay with admins. The write gate differs from the Global API tenant tables:

Role	Read (GET)	Create, edit, and delete components and profiles (POST / PUT / DELETE)	Delete an activity, rate-sheet writes
Org `admin`	Yes	Yes	Yes
Org `member`	Yes	Yes	No, returns `403`
Org-bearing service token	Yes	No, returns `403`	No, returns `403`
Platform / null-org service token	No, returns `403`	No, returns `403`	No, returns `403`
Anonymous	`401`	`401`	`401`

The guards in src/db/tenant.ts run before any database query on write routes. requireOrgMember admits only a user token with the admin or member role; anything else returns 403 Please sign in as a person in this company to change activities. DELETE /activities/:id and the rate-sheet writes use requireOrgAdmin, which returns 403 Only an admin can do this. Please ask your admin. to a member.

Composite foreign key

activities_activity_components.activity_id and organisation_id together form a composite foreign key back to activities_activities(id, organisation_id). This prevents a component from binding to an activity in another organisation at the database layer, not just the application layer.

Soft references and same-org validation

parent_activity_id (on an activity) and child_activity_id (on a component) are bare UUID columns — not foreign keys. The application validates these at write time:

If the referenced activity does not exist in the caller’s organisation, the API returns 422 Referenced activity is not in your organisation.

A missing or foreign-org activity_id on a component (the real FK edge) resolves to zero rows under RLS and returns 404.

Code uniqueness

Activity codes are unique within an organisation. The same code may exist in two different organisations without conflict.

Activities database principals

The Activities database uses the same three-connection pattern as the Global API:

Role	`BYPASSRLS`	Purpose
Owner (migration role)	No (superuser locally)	Drizzle migrations.
`stralos_runtime_activities`	No	Authenticated request path, subject to FORCE RLS.
`stralos_platform_activities`	Yes	Wired for suite parity and admin/debug use; no Activities request handler currently uses it.

Re-home job

The existing Stralos-owned activity seed rows were migrated to the sole Stralos organisation by a one-off re-home job in the pre-RLS deployment window (after migration 0003 added the nullable column, before migration 0004 set NOT NULL and forced RLS). Row IDs were preserved so existing estimate component references remain valid. Every environment has applied 0004, and scripts/rehome-activities-org.mjs has been deleted.

Org-catalogue M2: Global API catalogues

Org-catalogue M2 made rate_categories fully organisation-scoped. Rows carry a non-null organisation_id, writes require an organisation admin, and FORCE RLS uses the standard app.current_org policy. The ordinary unique index on (organisation_id, name) permits the same category name in different organisations. setting_list_items uses a mixed partition. The four active organisation-owned keys are allowances_assumptions, tags_clarifications, takeoff_categories, and rate_group_labels; all other registered keys have a null organisation and remain suite-global and platform-managed. The company_locations and labour_rate_multipliers keys are retired — writes return 410 retired_setting_list. Units are held separately in the immutable unit_catalogue, with read-only grants for runtime/platform roles and no organisation ownership. A database check constraint enforces the settings partition. The runtime role can read global rows and its own organisation rows, but it can mutate only its own organisation rows. The platform role manages only global rows. Changing a row’s listKey across the global/organisation partition returns 422 list_key_partition_change. Global values are unique by (list_key, value); organisation values are unique by (organisation_id, list_key, value). Migrations 0013_orgcat_add_org.sql and 0014_orgcat_constrain_rls.sql add ownership, backfill under the single-organisation assumption, enforce the partition, and enable FORCE RLS. The second migration raises rather than assigning rows if an existing database has more than one organisation. Migration 0051_retire_labour_rate_multipliers.sql removes existing labour_rate_multipliers rows and drops the key from the partition CHECK constraint. Migration 0053_org_rate_group_labels.sql adds rate_group_labels to the organisation partition and constrains that key’s value to the six valid tokens.

Backfill and migration order

Estimate API (M3)

The Estimate API has three migration phases because estimate_estimates.organisation_id is cross-database: the estimate’s organisation comes from the project in the Global API database, not the Estimate database itself. Phase 1 — wave 1 (0015_orgsec_m3_add_org.sql): adds nullable organisation_id columns and supporting indexes to all twenty tenant tables. Applied by pnpm db:migrate (which delegates to scripts/migrate-orgsec-waves.mjs). Phase 2 — stamp check: the migration choreography refuses to apply wave 5 while any row still needs organisation_id. The cross-database scripts/backfill-organisation.mjs that once stamped rows from the Global database is deleted: cross-database migrations are not allowed, and every deployed environment passed wave 5 long ago. A fresh or empty database has nothing to stamp. Phase 3 — wave 5 (0016_orgsec_m3_constrain_rls.sql): sets NOT NULL on all twenty tenant columns, recreates business-unique indexes with organisation_id in scope, enables and forces RLS on all twenty tables, creates tenant_isolation policies, and grants table privileges to the runtime and platform roles. The migration contains an inline gate that raises an exception if any estimate_estimates row still has a null organisation_id, so it cannot accidentally run before the backfill on a populated database. Phase 4 — portal resolver: the resolve_org_for_portal_token SECURITY DEFINER function this phase used to create was retired on 2026-10-11. pnpm db:migrate now runs scripts/drop-portal-resolver.mjs in its place, which drops the function if it exists. scripts/migrate-orgsec-waves.mjs is the canonical db:migrate entrypoint (invoked by pnpm db:migrate). It applies wave 1, refuses if any row is unstamped, then applies wave 5 and any later migrations. Using drizzle-kit migrate directly on a populated, unstamped database causes wave 5’s gate to raise.

Global API (M2)

Migration 0004_orgsec_m2_add_org.sql adds nullable organisation columns, indexes, and supporting composite constraints. The standalone scripts/backfill-organisation.mjs that stamped an existing environment before the constrain migration has been deleted; every environment is past migration 0005. Migration 0005_orgsec_m2_constrain_rls.sql then:

fills any remaining null values using the existing single-organisation assumption;
sets every tenant organisation column to NOT NULL;
installs composite foreign keys and organisation-scoped unique indexes;
enables and forces RLS;
creates the tenant policies; and
grants table privileges to the runtime and platform roles when those roles exist.

The inline backfill keeps fresh local and CI migrations self-contained.

Rates API (M5)

The Rates database has no organisations table. Its pre-RLS backfill ran separately, before the constrain migration, using a cross-database scripts/backfill-organisation.mjs that has since been deleted from stralos-api-rates. Migration 0001_orgsec_m5_add_org.sql adds a nullable organisation_id column to rate_prices and project_rate_overrides and creates supporting indexes. Migration 0002_orgsec_m5_constrain_rls.sql then:

sets organisation_id to NOT NULL on both tables (assumes the backfill has stamped every row);
adds the composite FK (rate_price_id, organisation_id) on project_rate_overrides referencing the corresponding unique constraint on rate_prices — an override cannot bind to a price in another organisation;
recreates the project-override unique index with organisation_id as the leading column so uniqueness is org-scoped;
enables and forces RLS on rate_prices and project_rate_overrides;
creates tenant_isolation policies on both tables using the app.current_org GUC; and
grants table privileges to stralos_runtime_rates and stralos_platform_rates when those roles exist.

rate_items was the suite-global catalogue at M5. It was migrated to an org-scoped catalogue in orgcat-m1 (see below). For a fresh or empty database (local re-create, CI), the tenant tables contain no pre-existing rows, so the constrain migration passes without a prior backfill run.

FORCE-RLS safe backfill windows in later Rates migrations

From migration 0013 onward, the Rates migrations follow a pattern when they need to backfill or mutate rows on a FORCE-RLS table. The deployed migration principal is NOSUPERUSER + NOBYPASSRLS and runs without a tenant GUC set. Without intervention, FORCE RLS returns zero rows to this principal, causing a subsequent SET NOT NULL to fail against what appears to be an empty table. The fix is to bracket the backfill with NO FORCE ROW LEVEL SECURITY and FORCE ROW LEVEL SECURITY inside the same migration transaction:

ALTER TABLE "rates_rate_prices" NO FORCE ROW LEVEL SECURITY;
-- backfill INSERT / UPDATE visible to the owner principal
ALTER TABLE "rates_rate_prices" FORCE ROW LEVEL SECURITY;
ALTER TABLE "rates_rate_prices" ALTER COLUMN "rate_offering_id" SET NOT NULL;

NO FORCE drops the owner-role exemption, granting the owner visibility of every row without a policy check. Non-owner runtime roles remain fully policy-bound throughout. The entire block executes inside one migration transaction, so the backfill and the restoration of FORCE are atomic — the owner window is never left open if the migration rolls back. The SET NOT NULL that follows acts as an executable completeness proof: if any legacy row was missed during the backfill the constraint raises and the migration fails cleanly rather than recording a silent partial success. Migration 0015_rate_options.sql uses the same pattern on rates_project_rate_overrides to clear legacy override rows before the table’s schema changes. Any future migration that needs to read or modify rows on a FORCE-RLS table in the Rates database must apply this same bracket rather than assuming the migration principal can bypass RLS.

Activities API (M5)

For a populated Activities database, the one-off re-home job (now deleted, see Re-home job) ran after migration 0003_orgsec_m5_add_org.sql added nullable organisation columns and before migration 0004_orgsec_m5_constrain_rls.sql applied NOT NULL and FORCE RLS. The job stamped both Activities tables with the sole Stralos organisation while preserving record IDs. Migration 0004_orgsec_m5_constrain_rls.sql then replaces global activity-code uniqueness with (organisation_id, code), adds the composite component-to-activity ownership foreign key, enables and forces RLS on both tables, creates the tenant_isolation policies, and grants privileges to the Activities runtime and platform roles when they exist. Fresh or empty databases require no re-home run.

Documents API (M4)

The Documents service cannot use a plain drizzle-kit migrate for the M4 wave because Drizzle 0.45.2 wraps all pending migrations in a single transaction. On a populated database where both migration 0008_orgsec_m4_add_org.sql (wave 1) and 0009_orgsec_m4_constrain_rls.sql (wave 5) are pending at the same time, wave 5’s backfill guard would raise inside the transaction and roll back wave 1 with it, leaving no columns to backfill. Use pnpm db:migrate (which runs scripts/migrate-orgsec-waves.mjs) instead. The choreography is:

Wave 1 — migration 0008 adds nullable organisation_id columns, composite (id, organisation_id) parent uniques, and indexes. Non-destructive.
Wave 3 gate — the migration runner checks whether any of the nine gated tenant tables contain null organisation_id values. If they do, the run stops before wave 5 with wave 1 committed. The operator must stamp the null rows directly in the documents database, then re-run pnpm db:migrate to resume. A fresh or empty database has nothing to stamp and passes straight through.
Wave 5 — migration 0009 sets the nine gated tenant columns to NOT NULL, drops old single-column foreign keys, adds composite foreign keys, recreates business-unique indexes with organisation_id as the leading column, and enables FORCE RLS on all twelve tables. The migration opens with an in-database gate that raises if any of the nine required columns still contain NULL values.

After the waves, pnpm db:migrate automatically runs scripts/bootstrap-doc-resolver.mjs as a two-connection step. The migration owner temporarily grants the required privileges; the platform connection creates and owns resolve_org_for_document; then the temporary schema privilege is revoked. Workers fail closed when the function is absent. The migrate-orgsec-waves.mjs script is idempotent and resumes from the last recorded migration. An existing environment previously migrated by drizzle-kit migrate continues seamlessly. DATABASE_URL must be set explicitly; there is deliberately no localhost default.

orgcat-m1: Rate items catalogue org-scoping

At orgcat-m1, the rates_rate_items table was reclassified from a suite-global catalogue to an org-scoped tenant table. Each organisation now owns its own rate item catalogue.

Rate items tenant table

rates_rate_items carries a non-null organisation_id stamped from the caller’s JWT at write time. FORCE RLS is enabled with the same app.current_org GUC policy used by other tenant tables.

Write access gate

Members and admins both create and edit rate items and their options. Deleting a rate item, and restoring a deleted one from history, stay with admins:

Role	Read (GET)	Create and update items and options (POST / PUT)	Delete an item, restore a deleted item
Org `admin`	Yes	Yes	Yes
Org `member`	Yes (own org only)	Yes	No, returns `403`
Org-bearing service token	Yes	No, returns `403`	No, returns `403`
Platform / null-org service token	No, returns `403`	No, returns `403`	No, returns `403`
Anonymous	`401`	`401`	`401`

The guards in src/db/tenant.ts run before any database query on write routes. requireOrgMember refuses any service token with 403 This route is for members of the organisation. requireOrgAdmin guards DELETE /rate-items/:id and the other admin-only routes (day-rate schedules and imports, for example). It returns 403 to a member with the message Only an admin for your company can do this. Please ask one of your admins. A route with a more exact refusal passes its own message. A member bringing back a deleted rate item, through POST /history/:id/restore for a deleted event or PUT /rate-items/:id on a deleted item, receives 403 with Only an admin for your company can bring back a deleted rate. Please ask one of your admins. Member-facing refusals say “company” and “admin”, never “organisation” or “administrator”.

Composite foreign keys

Composite (rate_item_id, organisation_id) foreign keys are declared on both child tables:

rates_rate_prices → rates_rate_items(id, organisation_id) — a price cannot bind to a foreign-org catalogue item.
rates_project_rate_overrides → rates_rate_items(id, organisation_id) — an override cannot bind to a foreign-org catalogue item.

These complement the existing (rate_price_id, organisation_id) FK on rates_project_rate_overrides.

Org-composite uniqueness

rates_rate_items has an org-scoped unique index on (organisation_id, code, name). A code is not unique on its own: a rate file may legitimately carry the same code on differently named items (for example PM on Project Manager, Contract Manager, and Senior Project Manager), and those are distinct catalogue entries. The pair (code, name) is the identity. NULL codes are unconstrained — Postgres treats NULL values as distinct, which is what allows a code-less catalogue to exist. A composite UNIQUE constraint on (id, organisation_id) backs the composite FK targets declared on the child tables.

Migration sequence

The orgcat-m1 schema change is implemented by two ordered migrations: Migration 0004_orgcat_add_org.sql: adds a nullable organisation_id column to rates_rate_items, creates supporting indexes, adds the composite (id, organisation_id) unique constraint that the child FK targets require, and grants the runtime role its new rate-item mutation privileges. Migration 0005_orgcat_constrain_rls.sql: performs the authoritative inline backfill by deriving the sole organisation from existing rate-price organisations, raises when that ownership is ambiguous, then sets organisation_id to NOT NULL. It also adds the composite foreign keys, recreates item-code uniqueness as (organisation_id, code) (later extended to (organisation_id, code, name) by migration 0028), and enables and forces RLS with its tenant policy. The normal deployment applies the recorded migrations in order and has no supported pause between them.

orgusers-m1: Organisation membership model

The orgusers-m1 and Org-users M2 sections below record the runtime implementation at those milestones. The current runtime POST /users writer is retired and returns 410 admin_owned after authentication and authority checks; user provisioning now enters through signed admin-api events.

At orgusers-m1, the Global API introduces an explicit organisation_memberships join table that records every user’s membership of an organisation. This is the first milestone in the Org & Users directory (§4 / §5 M1 in the source spec).

What changed

A new organisation_memberships table was added to the Global API database with columns id, user_id, organisation_id, role (admin | member), status (invited | active | disabled), created_at, and updated_at.
users.organisation_id was renamed to users.home_organisation_id and made nullable. All existing data is preserved; the column is just renamed.
Every pre-existing user was backfilled to exactly one membership row (same organisation_id, role, and status as the user row). An inline parity gate raises an exception if any user is not accounted for.
At M1, POST /users provisioned a membership row atomically inside the user-create transaction, so new users had a membership from the moment they were created.
GET /users scopes its results to users with an active membership in the caller’s organisation rather than filtering by home_organisation_id. A user whose membership in the calling org is inactive or disabled is excluded, even if the same user has a different home_organisation_id.
At M1, the external API contract for GET /users and POST /users remained byte-compatible: both returned organisationId mapped from home_organisation_id.

Identity classification

organisation_memberships is classified as an identity table (§4.5). This means:

The runtime database role (stralos_runtime_global) has no direct grant on the table.
FORCE RLS is enabled with no tenant policy — a policy-less FORCE-RLS table returns zero rows to any non-BYPASSRLS role, even if a grant were present.
All runtime access goes through four SECURITY DEFINER functions owned by the BYPASSRLS platform role (stralos_platform_global):

Function	Purpose
`resolve_active_memberships_for_email(email)`	Regional OTP bootstrap — returns the active memberships of active organisations for a given email, before any org context is set.
`resolve_active_membership_for_mint(user_id, org_id)`	Atomically re-checks the user’s active membership and role immediately before token minting.
`provision_membership(user_id, org_id)`	Historically called by runtime `POST /users` inside the create transaction to mirror the user row’s `role` and `status` into a membership row. Idempotent.
`list_active_org_member_ids(org_id)`	Called by `GET /users` — returns the `user_id` values of all users with an active membership in the given organisation.

These functions are created by scripts/bootstrap-membership-resolver.mjs, which runs as a two-connection post-migrate step (owner grants temporary privileges; platform role creates and owns the functions; owner revokes temporary privileges). The same bootstrap runs in the test harness. The resolver fails closed: if the bootstrap has not run, any call to the functions throws an operator-actionable error (42883 / 42P01 / 42501) rather than silently falling back to a direct owner-pool read of the identity table.

Migration sequence

Migration 0015_orgusers_m1_membership_add.sql (wave 1):

Creates organisation_memberships with role and status check constraints, (user_id, organisation_id) unique constraint, and btree indexes on both FKs.
Renames users.organisation_id → users.home_organisation_id (non-destructive; FK and index are renamed to match).
Drops NOT NULL on users.home_organisation_id.
Backfills every existing user to exactly one membership row (copying home_organisation_id, role, status). The insert is idempotent via ON CONFLICT DO NOTHING.
Runs an inline parity gate that raises if count(memberships) ≠ count(users).

Migration 0016_orgusers_m1_membership_constrain_rls.sql (wave 2):

Enables and forces RLS on organisation_memberships (no policy installed — runtime role returns zero rows; platform BYPASSRLS role bypasses).
Grants SELECT and INSERT on organisation_memberships to stralos_platform_global (guarded; safe on fresh CI without the role). No UPDATE or DELETE is granted in M1.

Post-migrate bootstrap (scripts/bootstrap-membership-resolver.mjs): creates the four SECURITY DEFINER functions. This runs automatically via the db:migrate package script and locally under stralos-local. See the code comments for the two-connection ownership transfer sequence.

Behaviour that has NOT changed in M1

Token minting still reads home_organisation_id from the users row. The membership table is not consulted during the OTP / refresh / switch-org flow in M1. That re-pointing happens in M2.
The GET /users and POST /users wire contracts expose organisationId (the home org). This mapping is retained through M1 so UI consumers require no update.
POST /organisations remains frozen and returns 403.

Rates API principals

The Rates service uses the same three-principal model as the Global API, with service-specific role names:

Role	`BYPASSRLS`	Purpose
`stralos_runtime_rates`	No	Authenticated request path — subject to FORCE RLS on rate items, prices, and project overrides.
`stralos_platform_rates`	Yes	Platform-scoped operations — full access to all three tables, bypasses RLS policies.
Owner (migration role)	No (superuser locally)	Drizzle migrations and the organisation backfill.

Rates API billing access guard

The Rates API auth plugin registers a billing access guard (registerBillingAccessGuard from @stralos-co-nz/api-common/billing). When enabled, the guard checks the organisation’s billing entitlement in a DynamoDB table before the request reaches any route handler. Requests from organisations whose billing grace period has expired are rejected. The guard is activated by the enable_billing_enforcement Terraform variable (default: false). When the variable is false, BILLING_ENTITLEMENTS_TABLE is set to an empty string and the guard performs no lookup.

Variable	When `enable_billing_enforcement = false`	When `enable_billing_enforcement = true`
`BILLING_ENTITLEMENTS_TABLE`	Empty string — guard performs no lookup	`<project_name>-<environment>-billing` DynamoDB table name

When enforcement is on, the Lambda role gains dynamodb:GetItem on the billing table scoped to two leading-key prefixes:

Key	Holds
`ORG#<orgId>`	A company’s billing entitlement.
`LINK#<linkId>`	A contractor company link’s state, read on every request from a contractor person. See Contractor billing guard.

Without LINK#*, every request from a contractor person fails the read and the guard returns 503 billing_unavailable before the route handler runs. The guard runs after authentication succeeds, so a request with an invalid token still returns 401 before the billing check.

Rates project membership access control

Migration 0046_project_membership.sql introduces database-enforced project-level access control for all Rates tables that carry a project_id column. This is distinct from organisation-level tenant isolation (FORCE RLS with app.current_org): it restricts which project-scoped rows a given request can see within a single organisation’s data.

The `project_scope_allows` function

The migration creates a stable SQL function project_scope_allows(p_project uuid) that the RLS policies call:

SELECT p_project IS NULL
  OR p_project::text IN (
    SELECT jsonb_array_elements_text(
      coalesce(nullif(current_setting('app.project_ids', true), ''), '[]')::jsonb
    )
  )

A null project_id (library item) is always accessible.
A non-null project_id is accessible only when its value appears in the app.project_ids GUC for the current transaction.

An unset or empty app.project_ids resolves to an empty JSON array, so no project-scoped rows are accessible when the GUC is absent.

Policy coverage

The migration automatically discovers every table in the public schema that has a project_id column and installs a project_membership RESTRICTIVE RLS policy on it for the stralos_runtime_rates role. It then walks all foreign-key edges outward from those tables, adding equivalent project_parent_* policies to every child table that has no direct project_id of its own. Polymorphic ownership tables (rates_pricing_rules, rates_rate_audit_events, rates_rate_options) receive manually crafted supplementary policies. A second pass creates write-only (INSERT and UPDATE) project_parent_insert_* and project_parent_update_* policies for any child table that itself carries a project_id and references a parent that also carries one. These write-check policies prevent attaching a row under a project the caller cannot access, without introducing SELECT-policy recursion.

How `app.project_ids` is set

runScoped in src/db/tenant.ts now calls resolveProjectScope(request) before opening the tenant transaction. resolveProjectScope fetches the authenticated caller’s accessible project IDs from api-global’s /api/global/projects/access-scope endpoint and returns them as a string array. The GUC is then set inside the transaction:

SELECT set_config('app.project_ids', '<json-array>', true)

The true flag makes the setting transaction-local, which is the same pooling-safe pattern used by app.current_org. The two GUCs are set together inside the same transaction so organisation and project scope are always consistent for a given request.

Error behaviour

resolveProjectScope wraps its peer fetch in a 5-second timeout and maps error outcomes as follows:

Condition	Status code
No authentication on the request	`401`
api-global returns `401` or `403`	`403`
Network error or timeout reaching api-global	`503`
api-global returns an unexpected status	`503`
Response organisation does not match the caller’s organisation	`503`
api-global response is not a valid array of UUIDs	`503`

The 503 cases indicate a temporary authority-check failure. The caller should retry. The service never grants access optimistically when project authority cannot be confirmed.

Service tokens

When the incoming token is a service token (typ: "service"), resolveProjectScope uses the x-forwarded-user header rather than the Authorization header to call api-global. This forwards the signed human token to the authority check rather than the service credential, so the project scope reflects the acting user’s membership. A service token without a forwarded user header resolves to an empty project ID list, granting access to library items only.

Scope model

Access request	Result
No `projectId` in the query	Library rows only (`project_id IS NULL`)
`projectId` present and in the caller’s scope	Library rows plus that project’s rows
`projectId` present but not in the caller’s scope	Zero rows (blocked by `project_membership` policy)

The application-layer helpers in src/db/projectScope.ts (libraryOnly, libraryOrProject, sameProjectScope) produce predicates aligned with this model. The RLS policy is the enforcement layer; the app predicates add query efficiency and explicit 404 semantics.

Org-users M2: mint-from-membership and switch-org

Org-users M2 changes how tokens are minted and introduces multi-organisation session switching.

Mint-from-membership

Before M2, user access tokens were minted using users.role and users.home_organisation_id. From M2 those columns are no longer the authority for minting. The retained regional credential and session paths use the regional organisation_memberships projection:

POST /api/global/auth/otp/verify — resolves all active memberships for the email, selects the default organisation (see below), performs an atomic re-check using resolve_active_membership_for_mint, and mints from the membership’s current role.
the legacy Global API refresh and switch-org routes use the same regional resolver before minting.

For those regional paths, resolve_active_membership_for_mint is a SECURITY DEFINER function owned by the BYPASSRLS platform role. The runtime role holds only EXECUTE; it has no direct grant on organisation_memberships. The function fails closed: a missing bootstrap or privilege error is rethrown and never falls back to a direct table read. Current POST /api/login/auth/refresh and POST /api/login/auth/switch-org instead query the canonical admin users, memberships, and organisations tables directly through the login role’s column-enumerated grants. They check that the user, organisation, and membership are all active in one statement, and mint from the returned current role. These central paths do not use the regional SECURITY DEFINER function or regional projection.

Membership resolver functions

Four SECURITY DEFINER functions are created by scripts/bootstrap-membership-resolver.mjs. All are owned by the BYPASSRLS platform role; the runtime role holds EXECUTE only.

Function	Used by	Purpose
`resolve_active_memberships_for_email(email)`	OTP verify	Returns all active memberships for a user’s email across active organisations.
`resolve_active_membership_for_mint(user_id, org_id)`	OTP verify and legacy regional refresh/switch	Atomic re-check: returns the current role iff user, org, and membership are all active; else `NULL`.
`provision_membership(user_id, org_id)`	Historical runtime `POST /users`	Created the user’s initial organisation membership inside the user-create transaction.
`list_active_org_member_ids(org_id)`	`GET /users`	Returns user IDs with an active membership in the given organisation.

Run scripts/bootstrap-membership-resolver.mjs after every db:migrate. It is idempotent. Local and CI flows call it automatically from globalSetup.ts.

Default organisation selection

When a user verifies OTP and belongs to more than one active organisation:

The home organisation (users.home_organisation_id) is selected if it has an active membership.
Otherwise the earliest-created active membership is used (ordered by created_at, then id).
If there are no active memberships the login is denied with 403.

Switch-org flow

POST /api/login/auth/switch-org is authenticated by a refresh token only. The login API registers this route without access-token authentication. Order of operations:

Read-only session validation — proves the caller owns a current session; does not consume the token.
User lookup — a missing user returns 401.
Membership gate against the canonical admin tables — a failed check returns 403 and leaves the refresh token usable.
Session rotation — the token is consumed and the session’s held active organisation is re-pointed to the target.
Post-rotation atomic re-check — the role is re-read after rotation; a failure here returns 401 because the token has already been consumed.
Token mint from the post-rotation role.

This order ensures a failed membership check never consumes the refresh token, and a role change that races the rotation window is always caught before a token is signed.

Session model version 2

SESSION_MODEL_VERSION was bumped from 1 to 2. Any session issued before M2 (version 1, or no version field) is refused with reason stale on its next refresh. Users are prompted to re-login. No active-session migration is required; the version check enforces the cutover passively as sessions are next used. The version was subsequently bumped from 2 to 3 at IDP-M7a. See IDP-M7a: identity-session model below.

Historical runtime user provisioning

At M2, POST /users provisioned a membership in organisation_memberships inside the same transaction as the user row. The provisioned membership mirrored the user row’s role and status. A transaction failure (for example, a duplicate email) rolled back the user row and membership together. The route is now retired as described in the milestone note above. GET /users scopes results to users with an active membership in the caller’s organisation, using list_active_org_member_ids. A user whose home organisation differs from the caller’s org is included if they hold an active membership in the caller’s org. A user with an inactive membership in the caller’s org is excluded.

IDP-M7: IdP subject binding

IDP-M7 (PRD 08 §8.8.5a) adds the infrastructure for the login service to bind a Cognito user account’s sub claim to an admin user record, and fixes a permanent authentication lockout that could arise when Cognito recreates an account.

Schema change

Migration 0011_idp_m7_users_idp_subject.sql adds two objects to the admin database:

A nullable text column idp_subject on the users table. Every existing row starts as NULL and remains NULL until that user authenticates through the IdP for the first time.
A partial unique index users_idp_subject_unique on (idp_subject) WHERE idp_subject IS NOT NULL. The partial predicate keeps unbound rows out of the index and states the core invariant: one Cognito sub may be bound to at most one admin user.

The column is owned and migrated by stralos-admin-api, not stralos-api-login. Login reads and writes the admin database but owns no schema in it; a column-level UPDATE grant naming a non-existent column fails immediately, so this migration must land before the login-service Terraform grant.

Binding lifecycle

The login service (stralos-api-login) is the sole writer that sets idp_subject. It binds the value using a compare-and-swap from NULL at the user’s first successful authentication:

A NULL → sub CAS succeeds: the user is now bound to that Cognito account.
A sub → same sub CAS is idempotent.
A presented sub that differs from a bound value is a hard authentication denial. This is deliberate: a silent rebind is how one identity quietly becomes another. The constraint violation (23505) is the login service’s signal to deny access, not a condition to resolve.

No backfill is performed. A backfilled value would assert a binding that no authentication has proved, and would have to be re-derived every time an account is recreated. Every row stays NULL until its owner authenticates.

Cognito account recreation lockout (fixed)

When Cognito recreates a user account (for example, via the reconciler’s AdminCreateUser repair), Cognito mints a fresh sub. Any existing idp_subject binding named the old, deleted account. Because the bind check has no repair arm, the mismatch was a permanent authentication failure — the create succeeded, the reconcile reported success, and the affected user received a 401 on their next sign-in with no path to recovery. IDP-M7 voids the binding in two places, both necessary: Reconciler (cognito/reconcile.ts): when a create repair is enqueued (because the pool account is missing), the reconciler clears idp_subject to NULL in the same transaction. The guard is an OR condition — provisioning !== 'pending' OR idpSubject !== null — not a nesting: a clear that only ran under an unrelated provisioning condition would be suppressed for users already at pending with a binding still set. Sweeper (cognito/sweeper.ts): when a create operation results in a genuinely minted account (AdminCreateUser succeeds with a fresh sub), the sweeper also clears idp_subject. This covers create operations that were already queued before the reconciler ran. The clear is gated on a genuine mint: a UsernameExistsException re-drive converges an account that was already present, whose sub never changed, so an existing binding for that account is correct and must survive. Neither path ever sets idp_subject — setting it is the login service’s responsibility via the CAS-from-NULL contract. Migration 0012_idp_m7_login_role_grants.sql creates or normalises the stralos_api_login PostgreSQL role. The admin database has no row-level security, so its column-enumerated grants are the database boundary for the login service. The migration enforces these attributes when creating the role: LOGIN, NOSUPERUSER, NOCREATEDB, NOCREATEROLE, NOREPLICATION, NOBYPASSRLS, and NOINHERIT. When the role already exists (re-assert path), only LOGIN, NOCREATEDB, NOCREATEROLE, NOBYPASSRLS, and NOINHERIT are asserted. NOSUPERUSER and NOREPLICATION are omitted from the ALTER ROLE because RDS forbids asserting an attribute the caller does not hold, and the RDS master has neither. Migration 0013’s postcondition verifies all six attributes regardless of which path ran. The migration does not set or replace a password. Whether a passwordless role can authenticate depends on the cluster’s pg_hba.conf; the role attributes alone do not prevent authentication. The role receives CONNECT on the current database, USAGE on the public schema, and these column privileges:

Table	`SELECT` columns	`UPDATE` columns
`users`	`id`, `email`, `name`, `status`, `idp_subject`, `session_generation`	`idp_subject`, `status`, `updated_at`
`organisations`	`id`, `name`, `status`, `home_region_key`	None
`memberships`	`user_id`, `organisation_id`, `role`, `status`, `invitation_generation`, `id`, `created_at`	`status`, `updated_at`

Migration 0012 first revokes direct table, sequence, routine, schema, and database privileges and removes role memberships. It refuses to continue if the role owns relations, schemas, or routines; has a default ACL; carries role-level settings; or owns the current database. Migration 0015 adds users.status:UPDATE for the activation transition described below. Column grants do not enforce row or value scope. UPDATE (status) can target any user or membership row unless the login service supplies the correct predicate. The role cannot insert or delete memberships, change membership roles, or read operator credentials and the admin outbox. Console-provisioned users begin with status = 'invited'. After successful authentication, the login service performs this compare-and-set:

UPDATE users
   SET status = 'active', updated_at = now()
 WHERE id = $1 AND status = 'invited'

The server derives the user ID from the verified identity token. The status = 'invited' predicate prevents this statement from reactivating a disabled user during normal operation. Because the admin database has no row-level security, these application predicates are the only row and transition bounds on the login role’s users.status grant. The postcondition has four successive versions:

0013_idp_m7_login_role_postcondition.sql checks the original boundary from 0012.
0015_idp_m7_login_activate_invited.sql adds users.status:UPDATE and reissues the postcondition for that wider boundary.
0016_login_role_postcondition_v2.sql is retired as the audit instrument from migration 0031 onward. Its expected array does not include users.session_generation:SELECT; re-running it against a correctly configured database raises on SURPLUS and must not be used as an audit after 0031 is applied.
0031_login_role_session_generation_read.sql adds users.session_generation:SELECT (no UPDATE) and reissues the postcondition as v3. This is the current audit instrument.

These are separate migrations because Drizzle does not rerun a migration after it has been recorded. Run the chain in order; rerunning 0012 without the later migrations removes the activation grant. Migration 0031’s postcondition checks the exact effective column privilege set, rejects table-level privileges, and detects grants delivered through PUBLIC or a role membership by inspecting ACL entries. On PostgreSQL 17 and later it also checks the MAINTAIN table privilege; the test is version-gated so the postcondition remains valid on PostgreSQL 16. The postcondition grants nothing and is safe to run independently as an audit — until the next migration widens the boundary.

Known residuals

The following limitations are recorded in the source and are not addressed by IDP-M7:

Stale-token race. Voiding the binding re-opens the NULL slot. A still-unexpired token bearing the old sub can win the CAS from NULL before the new account’s first authentication. This window is bounded by token lifetime and is strictly smaller than the permanent lockout it replaces. The login contract does not currently verify a presented sub against the live Cognito account.
Stale pool observation. The reconciler decides from a pool listing taken before its per-user lock, so a stale “missing” observation can clear a binding that did not need clearing. It does not refresh that observation with AdminGetUser under the lock.
Admin-only scope. This clear applies to the admin database only. stralos-api-global holds its own independently-bound users.idp_subject column and buildUserPayload does not project the field, so a regional replica retains the dead sub until a cross-repo contract carries the clear.

IDP-M7a: identity-session model

IDP-M7a (PRD 08 §10 M7a) introduces a first-class session-kind distinction and establishes the contracts that the post-login organisation chooser requires.

What changed

sessionKind field: every session row minted from this release carries an explicit sessionKind of either "org" or "identity". The field is never inferred; a v3+ row missing sessionKind is malformed and refused. Session model version 3: SESSION_MODEL_VERSION was bumped from 2 to 3. All v2 sessions (which carry no sessionKind) are refused with reason stale on their next refresh, prompting re-login. This bump has no ordering dependency on other IDP milestones. POST /api/login/auth/refresh — typed 403 for identity sessions: if the presented refresh token belongs to an identity session (no organisation selected), the endpoint returns 403 with code: "identity_session_cannot_refresh" and does not consume the token. The caller still holds a usable credential and should direct the user to organisation selection. This is a distinct outcome from a 401 — the credential is valid; what is missing is an active organisation. Routes that accept identity sessions: POST /api/login/auth/logout and POST /api/login/auth/switch-org accept a central identity session. These are the two operations available from an organisation chooser: select an organisation or abandon the login.

Identity session minting

stralos-api-login mints identity sessions and provides the refresh, logout, and organisation-switch contracts. Sign-out-everywhere for central browser sessions is handled by the login service relaying a signed command to stralos-admin-api (PRD 05 §8.4). The retained Global API revoke-all endpoint authenticates only its separate regional session store, so a central refresh token cannot authenticate to that route.

`readSessionOrgContext` versioned reading

readSessionOrgContext encodes the per-version reading rules for the org column:

Session version	`sessionKind`	`activeOrganisationId`	Result
pre-v3	any	non-null	Valid org session
pre-v3	any	null or absent	Malformed — refused
v3+	`"org"`	non-null	Valid org session
v3+	`"org"`	null or absent	Malformed — refused
v3+	`"identity"`	explicit `null`	Valid identity session
v3+	`"identity"`	non-null or absent	Malformed — refused
v3+	absent or other	any	Malformed — refused

The rule is per-version so that a v3 bump for an unrelated reason does not silently re-date the meaning of older rows.

Billing access guard

The Global API enforces billing entitlement via registerBillingAccessGuard from @stralos-co-nz/api-common/billing. The guard runs as a Fastify lifecycle hook after authentication succeeds. When a billing grace period expires and the guard determines that product access should be blocked for the organisation, it returns a billing-blocked error response before the route handler runs.

Recovery requests

Two routes are exempted from the billing block as recovery endpoints. The guard recognises these via the isRecoveryRequest callback, which matches GET requests to the following route patterns:

Route	Purpose
`GET /api/global/org/billing-seat-usage`	Lets an organisation administrator check the occupied seat count so they can adjust their subscription and recover access.
`GET /api/global/org/organisations`	Lets users switch to a different active organisation if they have one, even while the current organisation’s product access is blocked.

Neither recovery endpoint exposes user directory records or permits writes. The seat-usage endpoint is admin-only; the organisations endpoint is available to any authenticated member.

Billing entitlements table

The entitlements table is a DynamoDB table named <project>-<environment>-billing, populated by stralos-api-billing. The Global API Lambda receives a dynamodb:GetItem grant scoped to ORG#* and LINK#* partition keys. ORG#<orgId> holds a company’s billing entitlement; LINK#<linkId> holds a contractor company link’s state. The table is only wired when enable_billing_enforcement is true in Terraform; in other environments the guard is a no-op. See terraform/billing-entitlements.tf in stralos-api-global for the IAM policy and the Terraform variable. Every other product API that registers the guard carries the same two-prefix grant in its own billing-entitlements.tf.

Contractor billing guard

The shared guard in @stralos-co-nz/api-common/billing handles two contractor cases. Every API that registers it needs the LINK#* grant above. Covered companies. A company a contractor created has billing status covered and a coveredByOrgId. resolveBillingAccess follows the cover one hop and gives the company the covering contractor company’s access mode, with no seats of its own. A cover that cannot be followed (no covering ID, no covering row, or a covering row that is itself covered) fails closed as read-only. Contractor people. When a token carries a via claim, the guard reads the LINK#<link> row and the contractor company’s own ORG#<org> row on every request, without caching:

Result	Response
Link row missing, not `active`, or pointing at a different company or contractor	`403 contractor_access_ended`
Contractor company no longer on the `contractor` plan, or its billing blocked	`403 contractor_access_ended`
Contractor company’s billing read-only, on a write	`402 contractor_billing_read_only`
Link or entitlement read fails	`503 billing_unavailable`

These checks run before the token’s billing claim is compared, so an ended link refuses the request rather than asking the client to refresh its token. The plan and via claims are defined in User token claims.

Project membership

Migration 0060_project_membership.sql introduces explicit project membership. Access to a project is now always conferred by a row in project_members, not derived from organisation membership or any other implicit rule.

Data model

Table	Purpose
`project_role_definitions`	Immutable system roles: `member` and `lead` (project lead).
`project_members`	One row per user per project. Carries `provenance`: `creator` for the user who created a new project, `assigned` for rows added through the membership API, and `migration` for rollout rows.
`project_member_roles`	The role(s) held by a member within a project.
`project_access_requests`	Requests submitted by non-members asking for access. States: `pending`, `approved`, `declined`, `withdrawn`.
`project_access_events`	Audit log of all access changes on a project.
`project_lead_departures`	Durable reservation table that prevents a departing user from being appointed as a lead while their account deactivation is in progress.

Database functions

All access operations go through SECURITY DEFINER functions owned by the migration role. The runtime role holds EXECUTE on each function but has no direct grant on the underlying tables.

Function	Purpose
`project_actor()`	Returns `app.current_user` as UUID.
`project_org()`	Returns `app.current_org` as UUID.
`project_actor_role()`	Returns the caller’s organisation role (`admin` or `member`), or `null` when the caller has no active membership.
`project_has_access(project_id)`	Returns `true` when the caller holds a membership row for the project, or when a scoped `app.project_work_id` GUC grants temporary access via the lifecycle sweep.
`project_can_manage(project_id)`	Returns `true` for organisation administrators and for members holding the `lead` role on the project.
`project_set_member(project, user, role, replacement)`	Atomically assigns, updates, or removes a membership. Passing `null` for `role` removes the member. Enforces the last-lead constraint and the lead-departure guard. Raises `42501` when the caller lacks management authority.
`project_directory()`	Returns a computed directory view for the caller: one row per project in the organisation, with `can_enter`, `can_manage`, and `roles` projected from the caller’s memberships.
`project_team(project_id)`	Returns team members for a project. Checks `project_can_manage`; raises `42501` otherwise.
`project_candidates(project_id)`	Returns active organisation users not yet on the team.
`project_request_access(project, reason)`	Creates a pending access request. Refuses if the caller is already a member.
`project_decide_request(project, request, state)`	Approves, declines, or withdraws a request. Approval calls `project_set_member` to add the requester as a member.
`project_escalate_requests(org)`	Marks requests that have been pending for five weekdays (in the organisation’s timezone) as escalated. Called by the inbox function and by the lifecycle sweep.
`project_access_inbox()`	Returns inbox items for the caller: own requests plus pending requests on managed projects, with escalated requests visible to administrators. Runs `project_escalate_requests` as a side effect.

Last-lead constraint

Every project must have at least one active lead at all times (except while it has no members at all). project_set_member enforces this by checking whether the user being removed or demoted is the last active lead before applying the change. When a replacement lead is supplied, that user is promoted first before the demotion proceeds. The project_lead_departures table provides a coordination point for the account-deactivation saga: when a user’s account deactivation begins, a pending departure row is inserted. project_set_member checks project_user_leaving before appointing that user as a lead, refusing the appointment with code 23514 / constraint project_user_deactivating.

Row-level security

project_members, project_member_roles, project_access_requests, project_access_events, and project_lead_departures use FORCE RLS. The runtime role has read grants on the membership, role-definition, request, and event tables, constrained by table-specific RLS policies. Mutations and broader directory/team operations go through the SECURITY DEFINER functions listed above. The migration also installs owner-role policies on the identity tables those functions reference (organisation_memberships, users, organisations, projects, parties).

Migration backfill

The migration seeds every active organisation user onto every existing project in their organisation. Each seeded membership has provenance = 'migration' and the lead role. After cutover, provenance distinguishes these rollout rows (migration) from project creators (creator) and rows assigned through the API (assigned).

API routes

See Project access for the full request and response contracts.

Organisation slug

The organisation slug is the tenant key that makes login.<env>/t/<slug> resolvable to a home region (PRD 07).

Consumer contract (Global API)

Global API migration 0028_idp_org_slug.sql adds a nullable, unique slug column to its organisations projection. The column is nullable so organisation events created before the field existed can still apply. The admin-events organisation payload accepts slug as an optional field. When an update event omits it, the apply step preserves any slug already stored instead of clearing it. The unique index makes a duplicate slug fail visibly rather than allowing the tenant lookup to route one workspace to an arbitrary region. See Global API — tenant lookup for the public response contract.

Producer contract (admin API)

Migration 0025_idp_org_slug.sql adds slug to the organisations table as a NOT NULL, globally unique column. The migration also includes a backfill that derives slugs for organisations created before this migration, so the runtime can resolve all pre-existing tenants after one reconciliation pass. The slug has three load-bearing properties:

Globally unique. An organisation spans regions and the admin store is the only global authority for uniqueness. organisations.name is already globally unique for the same reason.
Immutable once issued. The slug appears in the login URL an organisation hands to its staff. Changing it silently breaks every bookmark and every SSO reply URL derived from it. Immutability is enforced structurally: updateSchema has no slug member and the .set() allowlist for organisation updates does not include it. Both halves must be breached before a slug can change; neither alone is sufficient.
Lowercase kebab. The format survives a URL path segment and a DNS label without escaping. The slug is not a secret — the runtime’s tenant lookup is unauthenticated by design and must never be treated as an access control boundary.

Slug format

Constraint	Value
Minimum length	2 characters
Maximum length	63 characters (DNS label limit)
Pattern	Lowercase letters and digits joined by single hyphens; no leading, trailing, or doubled hyphens
Reserved words	`login`, `auth`, `api`, `admin`, `health`, `t`

Create behaviour

POST /organisations accepts an optional slug field. When the caller omits it, the API derives a slug from the organisation name by normalising Unicode, lowercasing, replacing non-alphanumeric runs with hyphens, and trimming to 63 characters. Derivation refuses rather than inventing a slug when the result is too short or reserved. In that case the API returns 422 org_slug_underivable and the operator must supply an explicit slug. If the derived or supplied slug is already taken, the API returns 409 org_slug_taken. The uniqueness guarantee is ultimately the organisations_slug_unique index; the pre-check improves the error message in the common case but does not replace the constraint.

Event payload

Every organisation.* event now carries slug as a required field. The reconciliation drift set includes slug, so the first reconciliation pass after the backfill migration delivers the slug to the runtime for all pre-existing organisations. This is the mechanism by which the runtime becomes able to resolve /t/<slug> for organisations provisioned before PRD 07.

Organisation admin self-rename

PUT /api/admin/org/organisation is the endpoint that lets an organisation administrator rename their own organisation.

Route isolation

The rename lives in its own Fastify plugin (orgOrganisationAdminRoutes) registered with registerOrgAdminAuth. A separate plugin (orgOrganisationRoutes) registers the switcher read (GET /org/organisations) with registerOrgUserAuth, which applies no role restriction. Fastify encapsulation ensures the admin-only preHandler installed by registerOrgAdminAuth is never present on the read route, and vice versa — a rename registered in the read plugin would silently grant every member the ability to rename. The path is singular (/org/organisation), which distinguishes it from the plural read. It carries no organisation ID in the path or body: the organisation being renamed is always ctx.organisationId from the verified token. A caller cannot supply an ID to rename a different organisation.

Write contract

Request body (all other fields are stripped by Zod’s default strip mode):

Field	Type	Constraints
`name`	string	Required. Trimmed. Minimum 1 character, maximum 200 characters.

Successful response (200):

Field	Description
`organisationId`	The organisation’s UUID.
`organisationName`	The updated display name.

These field names match the switcher read’s response shape. Raw database columns (slug, status, homeRegionKey, updatedAt) are not returned.

Authority model

The preHandler gate from registerOrgAdminAuth produces a clean 403 for any caller who is not an administrator of the organisation in the verified token. This is the first check, but it is not the authorisation. Inside the write transaction, assertOrgAdminInTx re-checks administrator status under the membership share lock. A membership revoke or role downgrade that commits between the gate and the write cannot overtake the rename: the lock serialises both operations in a single committed transition. The organisation row is taken FOR UPDATE before the update, the same as the operator rename route in routes/organisations.ts. This serialises an admin rename against a concurrent operator update or disable.

Fields excluded from self-rename

The rename endpoint accepts name only. These fields are absent from the Zod schema and therefore unreachable:

Field	Reason excluded
`homeRegionKey`	A residency move serialises against membership grants and can strand a member. Operator-only via `routes/organisations.ts`.
`slug`	Frozen once issued. Changing it breaks the organisation’s login URL and all SSO reply URLs derived from it.
`status`	Owned by the provisioning saga, not freely settable even by an operator via this path.
`timezone`	Not a self-service field; managed by the provisioning saga.

Event propagation

Every regional api-global holds organisations as a read-only projection written only by its admin-event applier. Without an outbox event, the name change would apply in the admin database and nowhere a tenant can see it. After a successful write, the handler enqueues an organisation.updated event targeting the organisation’s homeRegionKey. The existing case "organisation" handler in the applier (an onConflictDoUpdate that sets name) consumes this event without any consumer-side change.

Data retention and user erasure

The retention Lambda (src/retention-lambda.ts, terraform/retention.tf) is the only hard-delete path in api-global; user-facing deletes stay soft. It runs as the stralos_platform_global role (BYPASSRLS), so it sees every organisation’s rows without a tenant GUC. Every statement is scoped to one organisation or one user. For the cross-service design, see Data retention.

Event	Action
No `kind` (the weekly EventBridge scheduled event)	Organisation purge sweep
`{ "kind": "erase-user-lookup", "email" }`	Read-only: returns `{ userId, organisationIds }`
`{ "kind": "erase-user", "userId", "requestId", "reports"? }`	Per-user erasure and the `user_erasures` audit row
Any other `kind`	Refused

Organisation purge

api-global reads status from its own organisations table. The sweep covers every organisation whose purged_at is null, using the org_offboarding clock and a DIRECTORY_PURGE_AFTER_DAYS window (default 104: the 90-day policy window plus two weekly runs, so every other service purges first). Any status other than disabled deletes the clock row. purgeOrganisation runs in one transaction. It locks the organisations row and refuses unless the status is disabled and purged_at is null. It then deletes project children and projects, organisation settings and catalogue rows, companies register rows, outbox rows, memberships, and users who belong only to this organisation. A sole user still referenced as a granter or decider on another organisation’s row is anonymised instead of deleted. The organisations row becomes a tombstone rather than being deleted: the name becomes Purged organisation <id>, slug and country_code are nulled, status stays disabled, and purged_at is set. Other services’ status reads and the mint still get a stable disabled answer. The admin-event applier drops any event about a purged organisation, and never rewrites personal data for a user recorded in user_erasures. A run where any organisation fails throws after attempting every organisation, and the retention Errors alarm pages.

Per-user erasure

Support’s scripts/erase-user.mjs in stralos-core calls erase-user-lookup first, fans erase-user out to every other service, and calls api-global last with their reports. api-global then:

Deletes the user’s regional DynamoDB rows: every item in SESSIONS_TABLE (<prefix>-sessions, partition key userId), covering refresh sessions and family revocation tombstones, and every item in OTP_DYNAMODB_TABLE (<prefix>-otp, partition key email). Each table is queried by partition key with a consistent read, then each returned key is deleted, so no other user’s row can match. This runs before the email is overwritten, because the OTP table is keyed by it.
Anonymises the users row (name Erased user, email erased-<id>@erased.invalid, idp_subject and last_login_at nulled, status disabled), sets the user’s project_access_requests.reason to Erased user, and deletes email-keyed global_directory_outbox rows.
Writes the user_erasures row with every service’s report, in the same transaction as step 2.

The user ID is kept, as are memberships, project team rows and audit actor IDs. erase-user refuses to run when SESSIONS_TABLE or OTP_DYNAMODB_TABLE is not configured: a missing table is an operator error, not a safe skip.

Retention IAM role

The retention Lambda has its own role (<prefix>-retention-role) sized to the job: read and KMS-decrypt the platform database URL parameter, and dynamodb:Query and dynamodb:DeleteItem on the regional sessions and OTP tables. It has no other database connection and no S3 access.

Schema (0072)

org_offboarding: organisation_id (primary key), first_disabled_at, and nullable purged_at.
user_erasures: request_id (primary key, must not be blank), user_id, requested_at, completed_at, and reports (JSONB). It stores counts only, never a name or email.
organisations.purged_at: nullable; non-null marks a tombstone.

Both new tables have RLS enabled and are granted only to stralos_platform_global. The migration is additive, and its grants are guarded so it applies where the platform role does not exist.

Contractor companies

A contractor company works inside other companies (its client companies) through a company link. stralos-admin-api owns the links and contractor memberships and projects them to the region through the admin-events stream. The Global API reads the projection, serves the company context and copy routes, and relays every link write to stralos-admin-api.

Schema (0086)

Table or column	Purpose
`company_contractor_links`	One row per link between a client company (`company_id`) and a contractor company (`contractor_org_id`). `origin` is `created` or `invited`; `managed_by` is `contractor` or `company`; `status` is `pending`, `active` or `ended`. Identity-classified: FORCE RLS with no policy and no runtime grant. Only the platform role writes it.
`organisation_memberships.via_link_id`	The link a contractor person’s membership came through. Null for the company’s own people.
`organisations.created_by_organisation_id`	The contractor company that created this company, or null for a company that signed up itself.
`copied_from_org_id`, `copied_from_id`, `copied_at`	Copy provenance on `parties`, `locations`, `disposal_substances`, `rate_categories`, `organisation_rate_group_settings`, `setting_list_items` and `departure_clarifications`.
`contractor_copy_jobs`, `contractor_copy_job_steps`	Copy and refresh jobs, owned by the client company with the standard `tenant_isolation` policy. A partial unique index allows one queued or running job per company. The runtime role may read, insert and update but never delete them. See Contractor copy and refresh.

The migration replaces the single unique index on organisations.name with two partial indexes, created before the old one is dropped:

organisations_name_unique_standard: name is unique among companies that signed up themselves.
organisations_name_unique_per_creator: (created_by_organisation_id, name) is unique, so a created company’s name only needs to be unique among its contractor’s companies.

Runtime reads

The runtime reads company_contractor_links only through two SECURITY DEFINER functions created by scripts/bootstrap-membership-resolver.mjs (wrapped in src/contractor/linkReads.ts):

Function	Returns
`contractor_link_for_org(company, contractor)`	The latest link between a company and one contractor, live links first. Used by the copy authority check.
`links_for_company(company)`	A company’s live (`pending` and `active`) links, with contractor name and people count.

The functions trust their arguments, so every caller passes an organisation ID taken from the verified token. A missing bootstrap fails closed with an operator-actionable error; nothing falls back to a direct read.

Contractor project visibility

Migration 0088_contractor_project_visibility.sql adds project_actor_contractor_guest(). It is true when the caller’s membership in the project’s company came through a link that the contractor does not manage there. A membership whose link row is missing also counts as a guest.

In a company the contractor created and still manages (managed_by = 'contractor'), its people are the company and keep the normal view, including the admin view of every project.
In a company that invited the contractor, or one taken over by its own admins, every contractor person is a guest.

For a guest, project_directory() returns only the projects they are a member of, and project_can_read() and project_can_manage() drop the admin path. project_request_access() and project_self_join() refuse with 42501 Project not available, the same as a project that does not exist. project_access_inbox() does not give a guest admin the escalated requests. GET /project-directory returns canSeeAllProjects: false for a guest. Two helpers keep integrity checks whole. project_non_blocking_ids() gives shared-resource conflict checks the IDs of lost and archived projects without names. project_client_project_count() counts every project for the client-in-use guards on companies, whatever the caller can see. Ending a link does not remove project memberships. A contractor linked again sees its earlier projects again.

⌘I

​Implemented milestones

​Identity model

​Tenant tables

​RLS policy

​Project history RLS policy

​Request routing

​Database principals

​App-layer filters

​Organisation-aware constraints

​M3: Estimate API tenant isolation

​Estimate tenant tables

​Estimate request routing

​Subcontractor portal

​Estimate database principals

​Estimate organisation-aware constraints

​Migration sequence

​Project membership enforcement (Estimate API)

​Estimate membership resolution

​RLS policies

​Estimate membership errors

​Service token path

​Access scope endpoint

​M4: Documents service tenant isolation

​Documents tenant tables

​Composite foreign keys

​S3 key uniqueness exception

​Documents request routing

​Write gate

​Worker database access

​Business uniqueness

​Documents database principals

​Project membership enforcement

​Documents membership resolution

​Documents membership errors

​RUNTIME_DATABASE_URL requirement

​M5: Activities service tenant isolation

​Activities tenant tables

​Write access gate

​Composite foreign key

​Soft references and same-org validation

​Code uniqueness

​Activities database principals

​Re-home job

​Org-catalogue M2: Global API catalogues

​Backfill and migration order

​Estimate API (M3)

​Global API (M2)

​Rates API (M5)

​FORCE-RLS safe backfill windows in later Rates migrations

​Activities API (M5)

​Documents API (M4)

​orgcat-m1: Rate items catalogue org-scoping

​Rate items tenant table

​Write access gate

​Composite foreign keys

​Org-composite uniqueness

​Migration sequence

​orgusers-m1: Organisation membership model

​What changed

​Identity classification

​Migration sequence

​Behaviour that has NOT changed in M1

​Rates API principals

​Rates API billing access guard

​Rates project membership access control

​The project_scope_allows function

​Policy coverage

​How app.project_ids is set

​Error behaviour

​Service tokens

​Scope model

​Org-users M2: mint-from-membership and switch-org

​Mint-from-membership

​Membership resolver functions

​Default organisation selection

​Switch-org flow

​Session model version 2

​Historical runtime user provisioning

​IDP-M7: IdP subject binding

​Schema change

Implemented milestones

Identity model

Tenant tables

RLS policy

Project history RLS policy

Request routing

Database principals

App-layer filters

Organisation-aware constraints

M3: Estimate API tenant isolation

Estimate tenant tables

Estimate request routing

Subcontractor portal

Estimate database principals

Estimate organisation-aware constraints

Migration sequence

Project membership enforcement (Estimate API)

Estimate membership resolution

RLS policies

Estimate membership errors

Service token path

Access scope endpoint

M4: Documents service tenant isolation

Documents tenant tables

Composite foreign keys

S3 key uniqueness exception

Documents request routing

Write gate

Worker database access

Business uniqueness

Documents database principals

Project membership enforcement

Documents membership resolution

Documents membership errors

`RUNTIME_DATABASE_URL` requirement

M5: Activities service tenant isolation

Activities tenant tables

Write access gate

Composite foreign key

Soft references and same-org validation

Code uniqueness

Activities database principals

Re-home job

Org-catalogue M2: Global API catalogues

Backfill and migration order

Estimate API (M3)

Global API (M2)

Rates API (M5)

FORCE-RLS safe backfill windows in later Rates migrations

Activities API (M5)

Documents API (M4)

orgcat-m1: Rate items catalogue org-scoping

Rate items tenant table

Write access gate

Composite foreign keys

Org-composite uniqueness

Migration sequence

orgusers-m1: Organisation membership model

What changed

Identity classification

Migration sequence

Behaviour that has NOT changed in M1

Rates API principals

Rates API billing access guard

Rates project membership access control

The `project_scope_allows` function

Policy coverage

How `app.project_ids` is set

Error behaviour

Service tokens

Scope model

Org-users M2: mint-from-membership and switch-org

Mint-from-membership

Membership resolver functions

Default organisation selection

Switch-org flow

Session model version 2

Historical runtime user provisioning

IDP-M7: IdP subject binding

Schema change