> ## Documentation Index > Fetch the complete documentation index at: https://help.stralos.app/llms.txt > Use this file to discover all available pages before exploring further. # Organisation security > Tenant-isolation architecture across Stralos services 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](https://help.stralos.app/internal/frontend-architecture.md#organisation-switcher-org-users-directory-m3). | | 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](https://help.stralos.app/internal/admin-provisioning.md). | | 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](https://help.stralos.app/internal/idp-m1-oidc-exchange.md). | | 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](https://help.stralos.app/internal/admin-provisioning.md#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](https://help.stralos.app/internal/admin-provisioning.md#directory-replacement-outbox-idp-m3b). | | 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](https://help.stralos.app/internal/frontend-architecture.md#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](https://help.stralos.app/internal/org-security.md#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](https://help.stralos.app/internal/frontend-architecture.md#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](https://help.stralos.app/internal/admin-provisioning.md#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](https://help.stralos.app/api/global.md#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](#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](https://help.stralos.app/api/global.md#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](https://help.stralos.app/api/global.md#put-/org/users/userid) and [DELETE /org/users/:userId](https://help.stralos.app/api/global.md#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-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](https://help.stralos.app/api/global.md#organisation-user-self-service). | | 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#`, 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](https://help.stralos.app/api/global.md#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](https://help.stralos.app/api/global.md#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](#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](#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](https://help.stralos.app/api/global.md#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](#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](https://help.stralos.app/api/global.md#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](#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-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](#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](https://help.stralos.app/internal/auth-token-architecture.md) and [Service token authentication](https://help.stralos.app/internal/service-token-auth.md) 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: ```sql theme={null} 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: ```sql theme={null} SELECT set_config('app.current_org', '', 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`: ```sql theme={null} 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. ## App-layer filters 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: ```sql theme={null} SELECT set_config('app.project_ids', '', 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](https://help.stralos.app/internal/runbooks.md#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: ```sql theme={null} SELECT set_config('app.project_ids', '', true) ``` A new `project_scope_allows(p_project uuid)` SQL function tests whether a given project UUID appears in that JSON array: ```sql theme={null} 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](https://help.stralos.app/api/estimate.md#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](https://help.stralos.app/internal/document-ai-pipeline.md#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](#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_` 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](https://help.stralos.app/internal/document-ai-pipeline.md#project-membership-enforcement) for the worker runtime and [Documents API: project-membership migration](https://help.stralos.app/internal/runbooks.md#documents-api-project-membership-migration-api-documents) 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: 1. fills any remaining null values using the existing single-organisation assumption; 2. sets every tenant organisation column to `NOT NULL`; 3. installs composite foreign keys and organisation-scoped unique indexes; 4. enables and forces RLS; 5. creates the tenant policies; and 6. 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: 1. sets `organisation_id` to `NOT NULL` on both tables (assumes the backfill has stamped every row); 2. 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; 3. recreates the project-override unique index with `organisation_id` as the leading column so uniqueness is org-scoped; 4. enables and forces RLS on `rate_prices` and `project_rate_overrides`; 5. creates `tenant_isolation` policies on both tables using the `app.current_org` GUC; and 6. 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: ```sql theme={null} 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](#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: 1. **Wave 1** — migration `0008` adds nullable `organisation_id` columns, composite `(id, organisation_id)` parent uniques, and indexes. Non-destructive. 2. **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. 3. **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 > [!NOTE] > > 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): 1. Creates `organisation_memberships` with role and status check constraints, `(user_id, organisation_id)` unique constraint, and btree indexes on both FKs. 2. Renames `users.organisation_id` → `users.home_organisation_id` (non-destructive; FK and index are renamed to match). 3. Drops `NOT NULL` on `users.home_organisation_id`. 4. Backfills every existing user to exactly one membership row (copying `home_organisation_id`, `role`, `status`). The insert is idempotent via `ON CONFLICT DO NOTHING`. 5. Runs an inline parity gate that raises if `count(memberships) ≠ count(users)`. **Migration `0016_orgusers_m1_membership_constrain_rls.sql`** (wave 2): 1. Enables and forces RLS on `organisation_memberships` (no policy installed — runtime role returns zero rows; platform BYPASSRLS role bypasses). 2. 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 | `--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#` | A company's billing entitlement. | | `LINK#` | A contractor company link's state, read on every request from a contractor person. See [Contractor billing guard](#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: ```sql theme={null} 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: ```sql theme={null} SELECT set_config('app.project_ids', '', 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: 1. The home organisation (`users.home_organisation_id`) is selected if it has an active membership. 2. Otherwise the earliest-created active membership is used (ordered by `created_at`, then `id`). 3. 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: 1. Read-only session validation — proves the caller owns a current session; does not consume the token. 2. User lookup — a missing user returns `401`. 3. Membership gate against the canonical admin tables — a failed check returns `403` and leaves the refresh token usable. 4. Session rotation — the token is consumed and the session's held active organisation is re-pointed to the target. 5. Post-rotation atomic re-check — the role is re-read after rotation; a failure here returns `401` because the token has already been consumed. 6. 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](#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. ### Login database role 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. ### Login service activation Console-provisioned users begin with `status = 'invited'`. After successful authentication, the login service performs this compare-and-set: ```sql theme={null} 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. ### Login role postcondition 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 `--billing`, populated by `stralos-api-billing`. The Global API Lambda receives a `dynamodb:GetItem` grant scoped to `ORG#*` and `LINK#*` partition keys. `ORG#` holds a company's billing entitlement; `LINK#` 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#` row and the contractor company's own `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](https://help.stralos.app/internal/auth-token-architecture.md#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](https://help.stralos.app/api/global.md#project-access) for the full request and response contracts. ## Organisation slug The organisation slug is the tenant key that makes `login./t/` 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](https://help.stralos.app/api/global.md#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/` 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](https://help.stralos.app/internal/data-retention.md). | 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 `, `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: 1. Deletes the user's regional DynamoDB rows: every item in `SESSIONS_TABLE` (`-sessions`, partition key `userId`), covering refresh sessions and family revocation tombstones, and every item in `OTP_DYNAMODB_TABLE` (`-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. 2. Anonymises the `users` row (name `Erased user`, email `erased-@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. 3. 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 (`-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](https://help.stralos.app/api/global.md#contractor-companies), 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](https://help.stralos.app/internal/contractor-copy.md). | 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. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.