Row-Level Security (RLS)

How tenant isolation works in this codebase. Read this before writing any code that touches clinical.*, audit.*, or any cross-tenant aggregation path.

Companion reading: docs/guides/testing/patient-cross-tenant-qa.md covers manual QA for cross-tenant flows. ADR 2026-04-23-002 covers the patient-vs-clinical schema split.

1. The model in one paragraphCopy Anchor Link

Every row in a tenant-scoped table (clinical.*, audit.*, parts of identity.* that hold tenant-scoped resources) carries a tenant_id column. PostgreSQL Row-Level Security policies on those tables let a query see a row only when its tenant_id matches the value of the postgres session variable (GUC) app.current_tenant_id. The application is responsible for setting that GUC at the right boundaries; postgres enforces the rest. There is no "bypass tenancy" application-side filter — if the GUC is wrong or unset, reads return zero rows and writes raise new row violates row-level security policy.

The model leans on three primitives: a request- or job-level mechanism that sets the GUC for the duration of an authenticated unit of work (the TenantContext concern for controllers, TenantAwareJob for Sidekiq); a block-scoped switch (RlsContext.with(tenant_id)) for the cases that legitimately need to read or write across tenants in a single request; and a strict invariant that any code touching an RLS'd table has activated the matching GUC first. The cost of getting the GUC wrong is silent — postgres just hides rows — so this guide leans hard on the "why" of each pattern and the categories of bugs that hide behind a misset or absent GUC.

2. The piecesCopy Anchor Link

2.1 How PostgreSQL enforces RLS, and what a GUC isCopy Anchor Link

RLS is a native PostgreSQL feature (since 9.5), not something the application implements. Once you ALTER TABLE ... ENABLE ROW LEVEL SECURITY on a table and attach a policy, Postgres transparently rewrites every query against that table: it ANDs the policy's USING expression onto the WHERE of every SELECT/UPDATE/DELETE, and evaluates the WITH CHECK expression on every INSERT/UPDATE. The application's SQL doesn't change — Clinical::Encounter.where(patient_id: x) stays exactly that — but the database silently narrows it to the rows the policy permits. That's the entire value: isolation is enforced one layer below the application, so an ORM bug, a forgotten .where, or a raw SQL string can't leak another tenant's rows.

Two role attributes decide whether a given connection is actually subject to that rewriting, and both have bitten us:

• By default the table owner and superusers bypass RLS entirely. ENABLE ROW LEVEL SECURITY on its own protects against other roles but not the owner — which is silent and depends on who happens to own the table. That's why every policy in this codebase is paired with FORCE ROW LEVEL SECURITY (§6.1), which makes the policy apply to the owner too.
• A role carrying the BYPASSRLS attribute (or a superuser) skips all policies. Our application role is intentionally NOSUPERUSER NOBYPASSRLS so it's held to the same rules in dev, staging, and production; the kulcare_rls_test role mirrors that posture so specs exercise the real contract instead of silently passing under a bypass. See rls-dev-setup.md.

The policy expression itself is static SQL — it can't take parameters from the application. So how does "the current tenant" get into it? Through a GUC. GUC stands for Grand Unified Configuration, Postgres's term for a runtime configuration setting. Built-in GUCs are things like work_mem or statement_timeout, but Postgres also lets you define custom, dot-namespaced ones (app.current_tenant_id, app.current_patient_user_id) that behave as per-connection scratch variables. The application writes one with set_config('app.current_tenant_id', tenant.id, is_local) and the policy reads it back with current_setting('app.current_tenant_id', true) — so a single static policy filters differently on every request, depending only on the GUC the connection is currently holding. The is_local flag controls lifetime: true scopes the value to the current transaction, false to the whole session (i.e. the pooled connection). That lifetime distinction is load-bearing — it's the subject of §4.3.

2.2 Where the policies live, and are they per-table?Copy Anchor Link

Yes — RLS is per-table. A policy is a first-class database object stored in the system catalog pg_policy (one row per policy), each attached to exactly one table via pg_class. There is no global or schema-wide RLS rule: you create a policy on clinical.encounters, a separate one on clinical.appointments, and so on. They're created, altered, and dropped with CREATE POLICY / ALTER POLICY / DROP POLICY in migrations (§6.1), and they're dumped into structure.sql alongside the rest of the schema.

To see what a table actually carries:

-- human-readable, in psql:
\d+ clinical.appointments         -- policies appear under a "Policies:" heading
-- or query the catalog view directly:
SELECT polname, cmd, qual AS using_clause, with_check
FROM pg_policies
WHERE schemaname = 'clinical' AND tablename = 'appointments';

A single table can carry more than one policy, and multiple policies combine rather than override:

• Permissive policies (the default, and the only kind this codebase uses) are OR'd together — a row is visible if any permissive policy's USING passes. That's exactly how the public_read carve-out coexists with tenant_isolation (§6.2): the marketplace tables each carry two permissive policies whose USING clauses union.
• Restrictive policies (CREATE POLICY ... AS RESTRICTIVE) are AND'd in as additional constraints. We don't use them.

A policy can also be scoped to specific commands (FOR SELECT, FOR INSERT, …) and to specific roles (TO some_role). We use the command scoping deliberately — public_read is FOR SELECT so it can widen reads without ever widening writes, while tenant_isolation is FOR ALL. We don't use the role (TO) clause: isolation must hold for whichever role connects.

2.3 The RLS policy shapeCopy Anchor Link

Every tenant-scoped table has a policy of this shape:

CREATE POLICY tenant_isolation ON clinical.encounters
  USING       (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
  WITH CHECK  (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);

• USING clause: which rows are visible to SELECT / UPDATE / DELETE.
• WITH CHECK clause: which INSERT / UPDATE writes are allowed.
• current_setting(..., true) — the second arg is missing_ok. Returns NULL (then coerced to '' by NULLIF) if the GUC isn't set.
• When the GUC is empty, tenant_id = NULL is itself NULL — never true. Reads silently return zero rows; writes raise new row violates row-level security policy.

2.4 What is not under RLSCopy Anchor Link

• identity.users, identity.patients (the phone-keyed global identity table), identity.tenants themselves, and a handful of cross-tenant join tables. These are the "tenant-agnostic" surface and intentionally readable without an active tenant context.
• Identity bridge tables that link a user to a tenant — identity.user_memberships, identity.sessions, identity.profile_switches, identity.refresh_tokens, identity.devices. These have a tenant_id column on some rows but they are NOT tenant resources. See §3 below for the rule and §10.1 for why this distinction matters.
• The patient.* schema. Patient-owned data is keyed by user_id, not tenant. See ADR 2026-04-23-002.
• Gateway-webhook correlation rows — billing.subscription_intents, billing.care_package_order_intents, billing.module_addon_order_intents, and the payment-order tables clinical.appointment_orders / clinical.telemedicine_orders. A gateway webhook (Cashfree / Razorpay) arrives with no JWT, so it cannot read the FORCE-RLS resource it needs to update. These rows are stamped at checkout under the buyer's tenant context and carry tenant_id as payload the webhook reads to bootstrap the GUC, not as an isolation key — so they are deliberately NOT RLS-enabled. A tenant_isolation policy here would make the webhook's GUC-less find_by return zero rows and silently drop every event (#2047 / #2023). See §3.
• The legacy parts/ schema. Not under RLS at all — it's being phased out.

3. Deciding what gets RLS — when RLS is the wrong toolCopy Anchor Link

The identity schema is a mix of four kinds of tables, and the right RLS posture is different for each. Getting the classification wrong is the single most common RLS mistake in this codebase — it's category, not capability.

The trap: a row having a tenant_id column does not make it a tenant resource. Apply RLS based on ownership semantics (who would lose the row if the tenant were deleted? the user, the tenant, or no one because the row is the relationship itself?) — never based on column presence alone. user_memberships is the canonical category error: it has tenant_id, which tempted the original migration to RLS-scope it, but its primary read is the auth bootstrap — "what tenants can this user access?" — which has no tenant context yet. The fix was to disable RLS on it; the case study is in §10.1.

Tenant scoping has TWO layers: postgres RLS at the database level AND the Shared::Concerns::TenantScoped Rails concern at the model level (which injects default_scope { where(tenant_id: Shared::Current.tenant.id) }). When fixing a bridge-table category error, BOTH layers must come off together. Removing only RLS leaves the model-level default_scope silently injecting a tenant_id filter — a query that explicitly asks for WHERE tenant_id = target_id gets compounded into WHERE tenant_id = current_tenant AND tenant_id = target_id, and the contradictory predicate returns zero rows. The May 2026 audit hit this exactly: after dropping RLS from user_memberships, profile-switch still 403'd because UserMembership still included TenantScoped. The companion fix is to remove the include and explicitly re-declare the bits the concern provided (belongs_to :tenant, validates :tenant_id, presence: true) — see backend/domains/identity/models/user_membership.rb for the canonical shape.

The companion read direction — "who's a member of this tenant?" — is a legitimately tenant-scoped query, but it's served by Pundit-protected admin endpoints that already scope by current_tenant in application code. RLS would be belt-and-suspenders for that read while actively breaking the user-scoped read. The two reads have different authorization shapes and the bridge table can't carry both.

4. Setting the GUCCopy Anchor Link

4.1 At request / job boundary (the "ambient" tenant)Copy Anchor Link

This is the steady-state case: the request's JWT identifies a tenant, every controller action runs under that tenant's GUC.

Controllers — Shared::Concerns::TenantContext runs before_action :set_rls_context which calls set_config('app.current_tenant_id', current_tenant.id, false) (session-scoped). The matching after_action :clear_rls_context runs another session-scoped set_config('', false) at teardown so the pooled connection doesn't leak the tenant to the next request.

Sidekiq jobs — Shared::Concerns::TenantAwareJob#with_tenant delegates to Shared::Tenancy::RlsContext.with (which opens its own transaction when needed — see §4.2). The session-scoped pattern is mostly avoided for jobs; block-scoped is preferred so the GUC is bounded to the job's actual work.

Public / unauthenticated endpoints — no tenant context. They MUST skip set_rls_context. Reads of identity.* marketplace tables (facilities, organizations, facility_locations, practitioner_roles) work via the public_read carve-out (§6.2) and return rows where the visibility flag matches. Reads of any other tenant-scoped table (the entire clinical.* schema, billing.*, etc.) WILL return zero rows under FORCE-RLS — anonymous controllers must either derive the tenant from a publicly-readable row (e.g. facility.tenant) and then wrap subsequent reads in RlsContext.with(facility.tenant_id), or restrict themselves to the public-marketplace surface. Patient::PublicAppointmentsController is the canonical example: anonymous facility lookup → derive tenant → wrap clinical writes in RlsContext.with.

4.2 Inside a request, for a different tenant (RlsContext.with)Copy Anchor Link

This is the cross-tenant case: a patient appears at multiple clinics; the patient-facing app needs to aggregate their appointments across all of them. The JWT identifies the user but not a single tenant. We iterate each Clinical::Patient row for the user, switching the GUC to that row's tenant before querying.

Shared::Tenancy::RlsContext.with(tenant_b.id) do
  # inside this block, all clinical.* queries see Tenant B's rows
  Clinical::Encounter.where(patient_id: pid).to_a
end
# back to the caller's previous tenant context

There is also each_patient_for(user) that combines the lookup + the block:

Shared::Tenancy::RlsContext.each_patient_for(user) do |patient|
  # `patient` is loaded under that tenant's RLS context
  patient.appointments.includes(:practitioner).to_a
end

A MAX_TENANTS_PER_REQUEST = 10 cap is hard-coded. A patient at >10 tenants gets the first 10 in postgres's default scan order — each_patient_for applies LIMIT without ORDER BY, so the selection is not deterministic. If a per-row priority is needed (e.g. recency, active appointments first) the caller should fetch + order the Clinical::Patient rows themselves and iterate with RlsContext.with(...).

4.3 What set_config actually does — the trapCopy Anchor Link

set_config(name, value, is_local) has two modes:

The trap: set_config(..., true) outside any explicit transaction runs in postgres's implicit single-statement transaction. The value is discarded the instant that one SELECT completes. The next statement reads ''. This is not an acceptable use of local: true — true is valid only inside an explicit tx.

This is why RlsContext.with opens a wrapping transaction when the caller isn't already in one — so local: true actually persists for the block. See backend/domains/shared/services/tenancy/rls_context.rb and PR #1448.

4.4 Order-of-operations trap inside TenantContextCopy Anchor Link

The TenantContext concern that exists to set the GUC must itself be careful about ordering. The before-action chain is:

1. set_tenant_from_session — load tenant and facility from the session record
2. set_rls_context — set app.current_tenant_id and app.current_facility_id GUCs

If step 1 calls session.facility (which queries identity.facilities — RLS-forced), the read happens BEFORE step 2 has set the GUC. Under enforced RLS, that read silently returns nil, @current_facility ends up nil, and any downstream require_facility! check 404s on a perfectly valid facility_id.

The fix is to activate the tenant GUC inside set_tenant_from_session, immediately after loading the tenant (which is safe — identity.tenants is not RLS'd) and before loading the facility:

def set_tenant_from_session
  return unless current_user
  session = current_session
  return unless session

  @current_tenant = session.tenant  # tenants — no RLS, safe

  # Activate GUC NOW so the next read (session.facility) sees rows
  Shared::Tenancy::RlsContext.activate(@current_tenant, local: false) if @current_tenant

  @current_facility = session.facility  # facilities — RLS-forced, needs GUC
  # ...
end

set_rls_context still runs as a separate before-action — its re-set is idempotent and preserves the ability for subclasses to skip one but not the other. This is the third bug of the same family discovered in the May 2026 audit: an RLS'd read happening before its enabling GUC activation. The previous two were in service methods; this one was in the shared concern that exists to set the GUC. If you ever add a new association read to TenantContext, check whether the target table is RLS'd before deciding where in the method to put it.

4.5 Where to CLEAR the GUC — the connection-boundary seam (#1485 item 2)Copy Anchor Link

Setting the GUC is well understood (above). The harder question is cleanup: a session-scoped GUC persists on the pooled connection, so it must be cleared or it leaks to the next borrower. Today cleanup happens in two places at the controller/job layer:

• TenantContext#clear_rls_context (an after_action), plus an in-action rescue scrub (because after_action doesn't run on exceptions) plus a poisoned-connection eviction fallback;
• a separate config/initializers/sidekiq_tenant_middleware.rb doing the equivalent for jobs.

This is the wrong seam. Research into canonical Rails+RLS implementations (notably activerecord-tenant-level-security, and the Postgres/Crunchy/AWS guidance) is consistent: the GUC lifecycle should be tied to the ActiveRecord connection checkout/checkin callbacks, not controller filters. Resetting at connection checkin:

• fires on every checkin, however the unit of work ended (success or exception) — no reliance on a success-only after_action plus an in-action rescue scrub to cover the error path;
• covers every entry point on the shared pool — HTTP requests, Sidekiq jobs, rake, console — so the controller concern and the Sidekiq middleware collapse into one mechanism;
• is exactly where the leak risk lives (a pooled connection isn't handed out without first being reset).

(It is not strictly more robust — see the pool-mutex nuance below — but it is structurally simpler and broader in coverage.)

Spike (Shared::Tenancy::ConnectionGuc, validated in Rails 8.1): ConnectionGuc.reset!(connection) clears the tenant/facility/patient-user GUCs, and Rails 8.1 defines a :checkin callback chain on the connection adapter (define_callbacks :checkout, :checkin in AbstractAdapter), so a set_callback(:checkin, :after) { ConnectionGuc.reset!(self) } resets the GUC when a connection returns to the pool. The contract + the end-to-end checkin behaviour are pinned in domains/shared/spec/services/tenancy/connection_guc_spec.rb.

Recommended item-2 shape: keep the resolution/authorization pieces of TenantContext as focused controller concerns (Sessions::Resolver → token→session→user, Authorization::TenantAccessCheck → membership), but move GUC set+reset onto the connection checkout/checkin callbacks — folding the Sidekiq middleware into the same seam and extending isolation to rake/console for free.

Important nuance the spike surfaced — checkin runs under the pool mutex. The :checkin chain fires inside AbstractAdapter#expire, which ConnectionPool#checkin calls while holding the pool's global mutex, before the connection is added back to the available list. Two consequences:

• It is NOT "strictly more robust" than the after_action. A raising reset! (dead socket, PG::Error, statement timeout) propagates out of checkin before the connection is returned → the connection leaks out of the pool — a worse failure than a request-teardown after_action. So the checkin callback must wrap reset! in a narrow rescue (swallow ConnectionNotEstablished/PG::Error — there is nothing to clean on a connection that's about to be discarded). That single rescue replaces today's two-cleanup-paths (after_action + in-action scrub) and the eviction fallback — it doesn't simply delete them.
• It adds 3 set_config round-trips to a contended path. Same connection, sub-ms, almost certainly negligible — but measure it, since checkout/checkin is hot.

Before implementing item 2, write down/cover these open items (the spike proves the mechanism, not yet the risk claim):

1. Failure-path behaviour — characterize what happens when reset! raises during checkin (under the mutex), and confirm the narrow-rescue keeps the connection returnable.
2. A real leak-characterization test — GUC set → checkin without the callback → re-checkout shows the stale GUC (the bug), vs with the callback → clean. The spike's "leak closure" test only proves reset! clears a value, not that the seam is necessary.
3. A GUCS-drift guard — the reset's completeness rests on a "kept in sync" comment; a future app.current_* GUC would silently leak past it (exactly the audit's recurring bug class). Guard the list with a test against the GUC-setting sites.

Caveat: connection-checkin reset is session-state-dependent and is incompatible with PgBouncer transaction-mode pooling — kulcare runs a standard Rails pool (no transaction-mode pooler), but confirm that at the infra level before rolling this out.

4.6 Why NOT wrap each request in a transaction (#1485 item 3, investigated, not pursued)Copy Anchor Link

A tempting alternative is to wrap every authenticated request in a transaction so SET LOCAL (transaction-scoped) auto-clears — no explicit cleanup at all. This was investigated and rejected, consistent with prevailing Rails practice:

• It makes every controller action atomic. Flows that intentionally commit work and then do something risky (e.g. create a record → call an external API; the record should survive an API failure) would silently roll back — a broad, subtle behaviour change across the whole app.
• It holds a Postgres transaction open for the request's entire duration, including slow external I/O (payments, ABDM, AI) — the documented "idle in transaction" failure mode (holds the MVCC snapshot/locks, blocks VACUUM, fills the pool).
• The Rails-native RLS gems deliberately avoid it and use session-scoped SET + a boundary reset instead — which is the §4.5 conclusion.

The leak-safety item 3 was chasing is delivered by §4.5's connection-checkin reset, without changing transaction semantics.

5. The rulesCopy Anchor Link

5.1 ReadsCopy Anchor Link

• Tenant-scoped tables (clinical.*, audit.*, scoped parts of identity.*) — RLS does the filtering. Do not add where(tenant_id: …) filters in application code; they're redundant at best and misleading at worst.
• Tenant-agnostic tables (identity.users, identity.patients, etc.) — RLS is not enforced. Read freely.
• Cross-tenant aggregation — always use Shared::Tenancy::RlsContext.with(...) or each_patient_for(...). Clinical::Patient.unscoped_by_tenant is a deliberate escape hatch used only by RlsContext.each_patient_for to load the index of (patient, tenant) pairs before switching context per row. It is not a general-purpose "see across tenants" affordance; reading rows under unscoped_by_tenant without subsequently switching the GUC defeats RLS.

5.2 WritesCopy Anchor Link

• INSERT/UPDATE under the wrong (or empty) GUC raises new row violates row-level security policy for table "X". That's the system working — don't catch and silence it.
• The new-tenant wizard creates a tenant under Tenant A's GUC, then must switch to Tenant B's GUC before inserting the org/facility/membership that belong to B. See Identity::Facilities::WizardSetupService for the canonical pattern.
• Read what you write under the same context. When an action wraps a write in RlsContext.with(...), look the target row up inside that block too. A lookup before the block runs under whatever GUC the request carries — for a platform-admin request, a cleared one — and on a table with no public_read or platform-admin branch it finds nothing, so the endpoint 404s only on an RLS-enforcing connection. The default test connection bypasses RLS, so cover the endpoint with with_rls_enforced_role { ... } (see §5.4). Identity::Admin::PractitionerRolesController#update_schedule / #destroy_schedule follow this shape (#2363) — but note they are not a live reproduction of the failure: identity.practitioner_schedules already carries an app.platform_admin branch in both USING and WITH CHECK (#2474), and with_platform_admin_rls! wraps the whole action, so both placements see the row there and the move was for consistency. The failure is reachable on a table whose policy has neither a public_read nor a platform-admin branch.

5.3 CleanupCopy Anchor Link

• TenantContext's after_action :clear_rls_context runs on every controller request. Don't add per-action skips unless you have a specific reason.
• If you ever use RlsContext.activate(tenant, local: false) (session-level), you MUST pair it with RlsContext.clear in an ensure block. The pooled connection will hand the GUC to the next request otherwise.
• RlsContext.with(...) cleans up automatically — the wrapping transaction + ensure block handle it.

5.4 TestsCopy Anchor Link

• Specs that exercise tenant-scoped code paths need to set the GUC explicitly before the assertions. The standard pattern is to use RlsContext.with(tenant.id) do ... end to scope the assertions.
• Database-cleaner runs each spec in a transaction. That transaction holds the GUC for the spec; RlsContext.with inside it nests cleanly (sees the outer tx and uses set_and_yield directly rather than opening another tx).
• See backend/domains/shared/spec/services/tenancy/rls_context_spec.rb for the canonical test patterns.

6. PatternsCopy Anchor Link

The how-to layer: the migration that switches RLS on, the marketplace read carve-out, and the request/job recipes you reach for day to day.

6.1 Migration pattern: ENABLE + FORCECopy Anchor Link

Every migration that enables RLS on a tenant-scoped table must include both:

execute "ALTER TABLE schema.table ENABLE ROW LEVEL SECURITY;"
execute "ALTER TABLE schema.table FORCE ROW LEVEL SECURITY;"

ENABLE turns RLS on. FORCE makes RLS apply to the table owner as well. Without FORCE, the connecting role bypasses RLS whenever it happens to be the table owner — which is silent and depends on whoever ran the last REASSIGN OWNED or ALTER TABLE ... OWNER TO .... PR review should reject any new RLS migration that only does ENABLE. The canonical pattern lives in 20251231000007_setup_identity_rls.rb:

def enable_rls_on_table(table)
  execute "ALTER TABLE identity.#{table} ENABLE ROW LEVEL SECURITY;"
  execute "ALTER TABLE identity.#{table} FORCE ROW LEVEL SECURITY;"
end

Migration 20260520160001_force_rls_on_identity_settings_tables.rb retrofitted FORCE onto three tables whose creation migrations had only ENABLE'd (facility_ai_settings, practitioner_ai_settings, facility_settings). The drift was invisible in normal operation but constituted a real defense-in-depth gap.

Use current_setting('app.current_tenant_id', true) (with the , true missing-OK arg) and NULLIF(..., '') in the policy expression. The unsafe form current_setting('app.current_tenant_id')::uuid raises unrecognized configuration parameter when the GUC is unset, turning what should be a zero-row read into a 500. The same retrofit migration replaced one such policy on facility_settings.

6.2 The public_read carve-out for marketplace-discoverable tablesCopy Anchor Link

Some tenant-scoped identity tables — facilities, organizations, facility_locations, practitioner_roles — hold data that's simultaneously tenant-owned (writes belong to one clinic) and intentionally world-visible (an anonymous marketplace visitor needs to read the facility name, address, doctor list, fees to browse + book). The single tenant_isolation policy can't serve both shapes: tenant isolation says "no GUC, no rows," but the booking-page detail lookup arrives anonymous by construction.

The resolution is a permissive FOR SELECT policy added alongside tenant_isolation, gated on the same visibility flag that drives Typesense indexing:

CREATE POLICY public_read ON identity.facilities
  FOR SELECT
  USING (
    NULLIF(current_setting('app.current_tenant_id', true), '') IS NULL
    AND active = true
  );

Postgres OR's permissive policies, so the table now has two visibility branches:

• Anonymous request (no GUC) — public_read matches active = true rows. tenant_isolation matches none (its NULLIF returns NULL). Union → publicly-listed rows visible.
• Authenticated tenant-X request (GUC set to X) — tenant_isolation matches X's rows. public_read matches none (the NULLIF IS NULL check fails when the GUC is set). Union → tenant X sees all their rows, including deactivated ones — necessary for CMS.

Why FOR SELECT is load-bearing. Anonymous mutations stay impossible because the policy doesn't apply to INSERT / UPDATE / DELETE — those still flow exclusively through tenant_isolation's WITH CHECK, which fails when the GUC is unset. The asymmetry is real: anonymous UPDATE is a silent 0-row no-op (because tenant_isolation.USING matches nothing), anonymous INSERT raises row-level security policy. Tests should pin both branches.

The Typesense alignment is the load-bearing rule. The same active = true (or status = 'active' for practitioner_roles) condition that gates public_read also gates the Typesense indexer's source_scope. Drift between the two surfaces means a clinic deactivated in ops appears in search but 404s in the detail page (or vice versa). The indexer files (facilities_collection.rb, practitioners_collection.rb) carry cross-reference comments pointing at the migration so future changes don't break this. If you add public_read to a new table, you must also confirm the Typesense indexer scope matches.

What you don't do: introduce a BYPASSRLS role, plumb a SECURITY DEFINER function, or accept the architecture by passing tenant_id from the frontend. All of these were considered and rejected in #1517 — the OR'd permissive policy is dramatically simpler (one migration, zero application-code changes) and preserves the existing tenant isolation guarantees on writes. See §10.2 for the full rationale.

Tables already not RLS'd (identity.practitioners, identity.practitioner_schedules, identity.vendor_schedules) don't need a public_read policy — they were classified as cross-tenant entities from the start. Migration 20260523120000_add_public_read_rls_policies_to_identity_tables.rb brought the FORCE-RLS'd marketplace tables into line with that posture.

6.3 A request-scoped read of clinical.* from a CMS controllerCopy Anchor Link

No special handling — the controller's before_action :set_rls_context already set the GUC from the JWT. Just query.

class Clinical::EncountersController < Clinical::BaseController
  def index
    @encounters = Clinical::Encounter.where(patient_id: params[:patient_id])
    # RLS auto-scopes to current_tenant
  end
end

6.4 A patient-app endpoint that needs cross-tenant dataCopy Anchor Link

The patient's JWT is tenant-agnostic. Use the service layer; the service uses RlsContext.each_patient_for.

Patient::Appointments::ListService.call(user: current_user)
# Internally: each_patient_for(current_user) do |patient| ... end

6.5 A new-tenant write flowCopy Anchor Link

You're under Tenant A's GUC (from the JWT). You create a new Tenant B, then need to insert child rows that belong to B.

ActiveRecord::Base.transaction do
  tenant_b = create_tenant   # under Tenant A's GUC; tenants table isn't RLS-scoped
  Shared::Tenancy::RlsContext.with(tenant_b.id) do
    # now under Tenant B's GUC — these inserts will satisfy WITH CHECK
    org = Identity::Organization.create!(tenant: tenant_b, ...)
    fac = Identity::Facility.create!(tenant: tenant_b, organization: org, ...)
  end
end

The wrapping ActiveRecord::Base.transaction do is essential. If with opened a new tx instead of joining yours, raise ActiveRecord::Rollback from inside the block would be swallowed at the savepoint and your outer tenant insert would commit. See Identity::Facilities::WizardSetupService and the regression spec at wizard_setup_service_new_tenant_spec.rb:121.

6.6 A background job that operates on one tenantCopy Anchor Link

TenantAwareJob handles the GUC at perform-boundary. Your job body just queries.

class CleanupStaleEncountersJob < ApplicationJob
  include Shared::Concerns::TenantAwareJob

  def perform(tenant_id)
    # GUC is already set to tenant_id by the included concern
    Clinical::Encounter.where(status: 'cancelled').destroy_all
  end
end

6.7 A background job that fans out across tenantsCopy Anchor Link

The job's "ambient" tenant should be nil; do the per-tenant work inside RlsContext.with. Each tenant gets its own block-scoped GUC activation, so a failure in one tenant doesn't leak its context into the next iteration.

class NightlyConsentAuditJob < ApplicationJob
  def perform
    Identity::Tenant.active.find_each do |tenant|
      Shared::Tenancy::RlsContext.with(tenant.id) do
        # per-tenant scan, audit emit
      end
    end
  end
end

6.8 A cross-tenant write (user accepting an invite into a different tenant)Copy Anchor Link

This is the "writing into not-my-current-tenant" shape. The request has a perfectly valid tenant context — the user is logged in, the session has a tenant_id, the GUC is set — but the target of the write isn't that tenant. The most common case is invite acceptance: User Raj is logged into City Hospital Group (Tenant A) and clicks accept on an invite from MedCare Clinic (Tenant B). His session GUC says A. The PractitionerRole row he's about to create has tenant_id = B. Postgres RLS sees the WITH CHECK predicate (tenant_id = app.current_tenant_id → B = A → false) and rejects the INSERT with new row violates row-level security policy.

The wrong fix is "switch his session to Tenant B before letting him accept" — that's a UX regression and conflates two different operations (accept-invite vs profile-switch). The right fix is to recognize that an invite-accept is fundamentally cross-tenant by design: the user is acting on behalf of their global self, writing into a specific tenant identified by the invite. Wrap the per-tenant work in RlsContext.with(invite.tenant_id):

def call
  ActiveRecord::Base.transaction do
    Shared::Tenancy::RlsContext.with(invite.tenant_id) do
      find_or_create_practitioner       # identity.practitioners — no RLS
      create_practitioner_role          # identity.practitioner_roles — RLS'd
      create_user_membership            # identity.user_memberships — no RLS (bridge table)
      mark_invite_accepted              # identity.practitioner_invites — no RLS
    end
  end
end

See Identity::Invites::AcceptPractitionerInviteService and AcceptStaffInviteService for canonical implementations. Same pattern applies to signup-with-pending-invite (Identity::Users::ProfileOrchestrationService#process_practitioner_invites / process_staff_invites), which iterates a user's pending invites and creates roles in each invite's tenant — one with(invite.tenant_id) block per iteration.

6.9 A bootstrap-path service that needs cross-tenant readsCopy Anchor Link

Some services run before a tenant has been selected — login, profile-switch, 2FA verify. Their entire purpose is to answer "what are this user's options?" by walking the user's tenants and reading something from each one. The standard TenantContext concern has nothing to set the GUC from at this point because the session row's tenant_id is still NULL — the user just authenticated but hasn't picked which tenant to operate as. The result is exactly the same trap as §4.4 in concept but at a different layer: every RLS'd-table read returns zero rows, and the "list of your tenants and their facilities" comes back empty.

The service-layer fix is the same shape as §4.2 — wrap each per-tenant enumeration in a block-scoped RlsContext.with. The block-scoped switch is the legitimate cross-tenant fan-out: iterate the user's tenants, switch the GUC per row, query inside the block, restore the GUC on exit. The restore matters — with captures the prior GUC and puts it back on success OR raise, so the outer no-GUC state is preserved across the loop and the next iteration starts clean.

# Identity::Profiles::GetAvailableProfilesService
def get_admin_facilities(tenant)
  Shared::Tenancy::RlsContext.with(tenant.id) do
    tenant.facilities.operational.map do |facility|
      { id: facility.id, name: facility.name, is_primary: false }
    end
  end
end

This pattern shows up in five services in backend/domains/identity/services/profiles/ and backend/domains/identity/services/invites/ — see §10.1 for the full audit list. The repetition is intentional rather than a smell: each call site has a different target_tenant source (tenant.id from the membership loop, invite.tenant_id from the invite, @target_tenant_id from the controller params), so no shared abstraction can cleanly hide it without leaking parameters or magic. The explicit with is the documented contract — when you see one in a service, you immediately know the service is doing cross-tenant work and which tenant the next read/write is scoped to. If you find yourself writing the eighth or ninth instance of this wrap across the codebase, that's the right moment to push back on the API design; until then, the repetition is paying its way in clarity.

6.10 Cross-tenant read controllers (Connect) — run with an EMPTY GUCCopy Anchor Link

The Connect app (domains/connect) is cross-tenant by design: a practitioner reads a union across every tenant they belong to, and may have no tenant at all (an unaffiliated vendor with only a professional_profile). Its controllers therefore must run with no app.current_tenant_id set — the opposite of a normal tenant-scoped controller. Two things break if a tenant GUC is present:

1. public_read tables collapse to one tenant. Tables like catalog.service_packages, connect.practitioner_roles, and identity.role_specialties carry a public_read policy that fires only when the GUC is unset (§6.2). If the request GUC is pinned to the bearer's single tenant, the permissive tenant_isolation policy takes over and the read silently narrows to that one tenant — e.g. a practitioner's cross-tenant derived_specialties shrinks to a single membership.
2. Tenant-private tables need per-tenant fan-out, not a single GUC. Tables with only tenant_isolation (appointments, referrals) are never visible under an empty GUC; the controller must iterate memberships with RlsContext.with(tenant_id) { … } (§4.2, §7) and union the results. A single ambient GUC would leak exactly one tenant's rows and hide the rest.

The gotcha we hit (#1994 / #2000). Shared::CrossTenantBaseController skipped the legacy set_tenant_context, but the modern TenantContext callbacks — set_tenant_from_session + set_rls_context — still ran on its subclasses and set the request GUC to the bearer's tenant. The base looked cross-tenant but every read was silently single-tenant. Skipping only the legacy filter is not enough.

The contract, hoisted to Connect::BaseController (so every Connect endpoint inherits it):

class BaseController < Shared::CrossTenantBaseController
  skip_before_action :set_tenant_from_session, raise: false
  skip_before_action :set_rls_context,         raise: false
  skip_after_action  :clear_rls_context,       raise: false

  before_action :clear_tenant_context!  # zero any GUC leaked by a prior
                                        # tenant-scoped request on this
                                        # pooled connection

  private

  def clear_tenant_context!
    ActiveRecord::Base.connection.execute(
      "SELECT set_config('app.current_tenant_id', '', false), " \
      "set_config('app.current_facility_id', '', false)"
    )
  rescue ActiveRecord::ConnectionNotEstablished, PG::Error => e
    Rails.logger.error("[Connect] failed to clear RLS context: #{e.class}")
    Shared::Tenancy::RlsContext.evict_current_connection!
  end
end

Notes:

• The clear is session-scoped (set_config(..., false)), because it must undo a session-level GUC set by a prior request on the same pooled connection; a transaction-local clear can't. This is a per-controller instance of the same leak §4.5 addresses at the connection-checkin seam — until that seam lands, cross-tenant controllers clear defensively themselves.
• Clearing is write-safe here: all Connect writes target global, non-tenant-RLS connect.* tables. A controller that clears the GUC and then writes to a tenant_isolation table would violate RLS — don't copy this pattern onto a tenant-scoped controller.
• Aggregator endpoints under /connect/me/* inherit all of this from Connect::BaseController via Connect::PersonalEndpointBase; that base adds only the per-request cross-tenant audit log, not its own GUC handling.

6.11 Party-owned tables (Connect vendor practice): the vendor GUCCopy Anchor Link

Connect practice phase 2 (docs/plans/2026-10-08-feat-connect-practice-phase2-plan.md, section 10) adds the first RLS tables in the connect schema. A vendor has no tenant, so these tables do not use app.current_tenant_id. The key is the party that owns the row: the vendor, the patient user, or both.

The other new connect.* tables (class batches, sessions and enrolments, visit series, meal subscriptions and pauses, client invites, share links, the bio archive) have no RLS. Two parties share those rows, so access comes from for_vendor and for_user scopes plus Pundit. docs/guides/developer/rls-classification.yaml lists each table, and domains/connect/test/models/connect_rls_state_test.rb pins the posture.

Set the vendor GUC with RlsContext.with_vendor(vendor_id) { ... }. It follows the same rules as with_platform_admin:

• It reads the prior value first, so a nested call gives back the outer vendor.
• It sets the GUC and then reads it back. If the set fails, it raises.
• It restores the prior value in one ensure (local: false). If the restore fails, it evicts the connection, so a vendor id does not go to the next pooled request.
• A blank vendor id raises ArgumentError. A nil from a caller is a bug, and must not look like a vendor with no clients.
• Outside a transaction it opens one, because a transaction-local set_config outside a transaction ends with its own SELECT.
• It does not touch Shared::Current. A vendor is not a tenant.

Where the GUC comes from:

• Connect::VendorPracticeController is the base of every /connect/me/* vendor practice endpoint. It refuses a user with no vendor record (403), then wraps each action in with_vendor(current_vendor.id). The vendor id always comes from current_user.vendor, never from the request.
• Connect::VendorClients::Upsert wraps itself in with_vendor, because a booking, a purchase or an enrolment writes a client row from another domain.
• The patient app reads care notes under with_patient_user(subject_user.id) and refund requests under with_patient_user(acting_user.id). The refund goes back to the payer, so the refund read keys on the buyer.
• A job that crosses vendors (the account closure, the staff refund queue) runs under with_platform_admin.

It fails closed. A job, rake task or admin path that forgets the GUC reads zero rows. That is safe, but it can look like lost data. When a vendor practice read returns nothing, check the GUC first (§8.1).

Foreign key actions skip row security. An ON DELETE SET NULL from identity.users clears subject_user_id and buyer_user_id with no GUC. The account closure test (domains/connect/test/services/account_closure_test.rb) proves this under the enforced role.

Test every service that touches these tables under with_rls_enforced_role (§5.4). Run bin/rails db:rls_test_role:provision again after each migration, or the enforced tests skip in silence.

7. Patient cross-tenant access — the fan-out engineCopy Anchor Link

The patient app is the one place in the system that deliberately runs without a tenant. A patient isn't a tenant-scoped actor — they're an Identity::User who happens to be registered as a Clinical::Patient at one clinic, or three, or ten. There is no single app.current_tenant_id that describes them. So every time the patient app reads a FORCE-RLS table — their appointments, their lab results, their prescriptions — it can't just set one GUC and query. It has to visit each tenant the patient belongs to, set that tenant's GUC, read, and move on. This section explains the machinery that does that, and how to extend it when a new table needs to be patient-readable.

7.1 One primitive, two consumersCopy Anchor Link

Everything starts from one method: Clinical::Patient.each_for_user(user, tenant_filter:) (in clinical/models/patient.rb). Conceptually it answers "which clinics is this user a patient at, and run this block once inside each one's RLS context." It works in two steps. First it discovers the user's Clinical::Patient rows across every tenant — a query that survives FORCE RLS only because of the clinical.patients self-access branch added in #1659, which makes a patient's own rows visible without any tenant GUC. Then, for each discovered row, it opens RlsContext.with(patient.tenant_id) (§4.2) and yields the patient. Inside that block app.current_tenant_id is set to that clinic, so any tenant-scoped query — patient.appointments, patient.encounters, anything hanging off the patient — simply works, correctly scoped to that clinic and that patient.

Two things are built on top of this primitive, and almost everything the patient reads goes through one of them.

The first is the clinical-record engine: Clinical::Patients::LongitudinalRecordService, backed by clinical/services/patients/lhr_views/cross_tenant_runner.rb. This is the general-purpose machine for the patient's longitudinal health record. You hand it a transformer — a small object that knows how to read and shape one record type for one patient (there's an encounters_transformer, a medications_transformer, a lab_records_transformer, and so on) — and it runs that transformer through each_for_user, then merges the per-tenant results into one paginated, time-ordered list. The patient's encounters, conditions, medications, labs, vitals, and symptoms are all read this way today. It exposes three entry points: .call for the full timeline, .resource for a collection of one type, and .find_resource(type:, id:) for a single record by id.

The second is a bespoke single-table finder — the pattern introduced for appointments in #1077, Patient::Appointments::OwnedAppointment.find(user, id). It uses the same each_for_user primitive but is hand-written rather than routed through the record engine. Appointments get this treatment for a deliberate reason: they're forward-looking scheduling, not retrospective clinical history, so they sit outside the longitudinal record (the appointments_controller explicitly does not call LongitudinalRecordService). When you find yourself reaching for a finder like this, treat it as a prompt to check whether your table actually belongs in the record engine instead — usually it does.

The patient-app finders and lists outside the record engine share one fan-out: Patient::CrossTenantAggregator (backend/domains/patient/services/cross_tenant_aggregator.rb, #1796). OwnedAppointment and OwnedInvoice use .find_first. Patient::Appointments::ListService and Patient::PackageSubscriptions::ListService use #call. A single-record lookup must be uncapped: .find_first calls each_for_user(user, limit: nil) and stops at the first hit. A lookup that inherits the list cap gives a patient at more than 10 clinics a false 404 on their own record at the 11th or later. The lists stay capped at MAX_TENANTS_PER_REQUEST for cost.

7.2 Which mechanism — a decision guideCopy Anchor Link

When a new RLS-protected table needs to be patient-readable, the real question is "engine or finder," and the answer is almost always "engine." The table below is the quick version; the paragraph after it is the reasoning.

The engine is the default because it has already solved the hard parts — visibility, ownership, consent, merging, pagination, the per-request tenant cap, error aggregation — once, in one place. A bespoke finder re-solves a subset of those by hand. A self-read policy (§7.4) re-solves visibility in the database at the cost of fragmenting everything else. So you reach past the engine only when a table genuinely isn't part of the clinical record (appointments), or when a measured hot path can't afford the fan-out's round-trips.

7.3 Ownership and consent — two app-layer decisions on one primitiveCopy Anchor Link

The fan-out only makes rows visible per tenant. By itself it doesn't decide who is allowed to see what — that's two separate policies layered on top in application code, and it's worth being precise about them because they're easy to conflate.

Ownership is enforced by the association you read through. patient.encounters, patient.appointments, and the like are scoped to patient_id, and the fan-out has already established that this patient row belongs to this user — so the ownership check falls out for free. The one exception is genuinely tenant-less data: vendor appointments live with tenant_id IS NULL and are therefore visible to any query that has no tenant GUC, so RLS can't scope them to a user at all. There, ownership has to be filtered explicitly in the query — attendee_user_id, via Clinical::Appointment.vendor_for_user — rather than relied upon from RLS.

Consent is the part people get backwards: cross-tenant consent is a clinic-facing concern, not a patient-facing one. A patient reading their own records across all their clinics is unconditional — it's their data, and the caller passes tenant_filter: nil. The consent gate exists for the other direction: a clinician at clinic A who wants to see what happened to the patient at clinic B. That cross-clinic read is gated — the caller must restrict to consented tenants via tenant_filter, and CrossTenantRunner will actively raise if asked to fan out across all tenants without consent_verified: true (which itself requires an active Identity::CrossTenantConsent grant). Both of these decisions live in application code on top of the fan-out, and neither can live in a database policy — a static RLS rule has no way to evaluate a consent grant, which is exactly why the engine, not a policy, owns the clinical record.

7.4 When a self-read RLS policy is — and isn't — the answerCopy Anchor Link

The public_read carve-out from §6.2 has a natural sibling on the patient axis: a patient_self_read FOR SELECT policy keyed on a patient-user GUC (app.current_patient_user_id) that lets a single no-tenant query read the user's own rows across tenants, skipping the fan-out entirely. It fires only when the patient GUC is set — which clinic requests never do — so it can't interfere with clinic consent (that runs on the tenant axis).

It's tempting, and it's the right call for exactly one table so far (appointments are well-suited: unconditional self-access, single-record lookups on a hot path). But it should stay the exception. The fan-out engine centralizes merge, pagination, the per-request tenant cap, consent routing, and error aggregation; a per-table policy buys you a single query while fragmenting all of that, and it adds a per-row ownership subquery that runs on the clinic-side read path too. So the bar is high: a table earns a self-read policy only when it is both (a) unconditional self-access and (b) a measured hot path where the fan-out round-trips actually hurt. For the clinical record — many tables, all consent-aware, all already served by the engine — the engine wins every time.

7.5 Recipe: making a new clinical table patient-readableCopy Anchor Link

Suppose you have a new clinical table foo and the patient needs to see their foos. The work is almost entirely "wire it into the engine," because the engine already knows how to fan out, enforce ownership, and gate consent — you're only teaching it how to read and shape one more record type:

1. Give Clinical::Patient a has_many :foos — this is the ownership binding the fan-out relies on.
2. Add lhr_views/foos_transformer.rb that reads clinical_patient.foos and shapes the rows, mirroring an existing transformer such as encounters_transformer.rb.
3. Register the type in LongitudinalRecordService::CLINICAL_RESOURCE_TYPES.
4. The patient surface now gets the list view plus find_resource(type: 'foo', id:) for free, and the clinic surface gets the same data consent-gated through tenant_filter — no extra work for either.
5. Spec it under with_rls_enforced_role: a foreign user's foo must not resolve, and a foo row must not be visible with no tenant GUC set. The bypass test role hides the RLS contract, so an enforced-role spec is the only kind that actually proves the isolation holds.

What's notable is what's absent from that list: no new finder, no migration, no RLS policy. The table is already FORCE-RLS'd like every clinical table, and the engine already does the cross-tenant work — so the cost of making one more table patient-readable is a transformer and a test.

8. DebuggingCopy Anchor Link

8.1 "I'm getting zero rows but I know they exist"Copy Anchor Link

Check the GUC. From a Rails console:

Shared::Tenancy::RlsContext.current_guc
# => "01900000-0000-7000-8000-000000000001"  -- good
# => ""                                       -- GUC unset; RLS is blocking everything

If empty, check whether the controller skipped set_rls_context, or whether the user's session has a tenant_id. The Rails console itself has no controller; you'll need to RlsContext.activate(tenant) before tenant-scoped queries return rows.

8.2 "I'm getting Audit::Tenancy: GUC failed to set"Copy Anchor Link

Either:

• The tenant_id you passed to RlsContext.with doesn't exist (Identity::Tenant.find(tenant_id) raised inside the method).
• Stray ad-hoc connection.execute("SELECT set_config(...)") somewhere is using is_local = true outside a transaction (see §4.3 — the trap). RlsContext.with opens its own transaction now (#1448) so the path inside with is safe; if you see this error in production it means something else is poking the GUC by hand. Grep the codebase for set_config('app.current_tenant_id' to find the offender.

8.3 "INSERT is raising new row violates row-level security policy"Copy Anchor Link

You're trying to insert a row whose tenant_id doesn't match the current GUC. Either:

• You set record.tenant_id = something_other_than_current_tenant. Don't — let before_create :set_tenant_id (or the explicit RlsContext.with(other_tenant.id) do ... end wrapper) handle it.
• You're inside the new-tenant wizard's window where current_tenant is still the old tenant but the inserts belong to the new one. Wrap the inserts in RlsContext.with(new_tenant.id) do ... end.

8.4 "My spec asserts something exists but it doesn't show up"Copy Anchor Link

Most likely the spec's GUC isn't set for the tenant the fixture was created under. Either:

• Use RlsContext.with(fixture_tenant.id) around the assertion.
• Set Shared::Current.tenant AND call set_config in spec setup. The rls_context_spec.rb patterns show both.

8.5 Auditing the RLS surface of a schemaCopy Anchor Link

When you want to see every RLS-enabled table in a schema with its current policies, run:

SELECT
  c.relname AS table_name,
  c.relrowsecurity AS rls_on,
  c.relforcerowsecurity AS rls_forced,
  COALESCE(p.polname, '(no policies)') AS policy,
  COALESCE(p.polcmd::text, '') AS cmd,
  pg_get_expr(p.polqual, p.polrelid) AS using_clause
FROM pg_class c
LEFT JOIN pg_policy p ON p.polrelid = c.oid
WHERE c.relnamespace = '<schema>'::regnamespace
  AND c.relkind = 'r'
ORDER BY c.relname, p.polname;

What to look for in the output:

• rls_on = TRUE, rls_forced = FALSE — defense-in-depth gap. The table owner bypasses RLS. Treat this as a bug to fix (see §6.1).
• A table with tenant_id in the schema but no policy row — either a genuine cross-tenant entity (§3), or a missing policy (rare; usually caught by review).
• A policy using_clause that calls current_setting('app.current_tenant_id') without the , true second arg — unsafe. Will raise instead of filtering when the GUC is unset (§6.1).

This query was how the May 2026 audit surfaced both the user_memberships category error (§10.1) and the missing-FORCE drift on the three *_settings tables. Run it after any non-trivial identity schema change.

9. Why this designCopy Anchor Link

9.1 Why postgres-enforced rather than app-filtered?Copy Anchor Link

The codebase predates the schema split (docs/new-schema-design). The old approach was application-side default_scope { where(tenant_id: Current.tenant_id) }, which had three problems:

1. Easy to bypass. A single Model.unscoped.where(...) in some service-layer code circumvents tenant isolation. We had bugs.
2. Joins leak. A joins(:other_table) that crossed into a table without the default scope would silently include other tenants' rows.
3. Audit queries need to see everything. Application filters made that awkward — audit had to use unscoped and then re-filter, which is the same easy-to-bypass surface.

RLS moves the check below the application. A Clinical::Encounter.unscoped.all is still tenant-scoped. The only way to read across tenants is to explicitly switch the GUC via RlsContext, which leaves an audit log trail.

9.2 Why local: false (session-scoped) at request/job boundary?Copy Anchor Link

Because controller actions and Sidekiq job bodies are NOT wrapped in an explicit transaction. A set_config(..., true) call outside a transaction is scoped to the implicit single-statement transaction of the SELECT itself — postgres discards the value the moment that statement ends, the next query reads back an empty GUC, and RLS silently blocks every tenant-scoped read. PR #1450 flipped TenantContext, TenantAwareJob, Shared::Current's callback, and a few ad-hoc callsites to local: false so the GUC actually persists for the request's many statements. The matching after_action :clear_rls_context (session-scoped) restores the connection on teardown.

9.3 Why does RlsContext.with open its own transaction?Copy Anchor Link

So block-scoped callers don't have to remember. Read paths (the LHR controller, the patient app's appointments list) don't naturally open transactions; without the wrap, local: true would be a no-op and the verification check would raise. The wrap is conditional — when the caller is already in a tx (booking, wizard), we don't open another, because nesting would swallow raise ActiveRecord::Rollback semantics. See PR #1448.

9.4 Why two different modes (local: false for ambient, local: true for blocks)?Copy Anchor Link

They're optimizing for different lifecycles:

• Ambient (request/job) — long-lived, no natural transaction, needs the GUC for many statements. Session-scoped + explicit teardown.
• Block (RlsContext.with) — short-lived, the wrap is the transaction. Transaction-scoped, postgres handles cleanup naturally on commit/rollback. The verify-loud check inside with (the GUC must actually equal what we just set) is the safety net.

10. Case studiesCopy Anchor Link

10.1 The user_memberships category error (May 2026)Copy Anchor Link

The original identity RLS migration (20251231000007_setup_identity_rls.rb) classified tables by a simple rule: "does the row have a tenant_id column?" Every row that did got RLS + a tenant_isolation policy. user_memberships was in that group alongside organizations, facilities, and practitioner_roles.

The mistake was invisible for months. Then the auth flow was redesigned around tokenless login + an explicit profile picker (the JWT no longer carries tenant_id; profile-switch sets it on the session). POST /identity/sessions now needs to read a user's memberships before any tenant context exists — to build the available_profiles array the frontend uses to render the picker.

Under tenant-scoped RLS, that read returned zero rows. The session response included available_profiles: [], the frontend interpreted the empty array as "this user has no tenant memberships," skipped the picker, and redirected to the cross-tenant Connect dashboard with session.tenant_id = NULL. Every subsequent CMS endpoint that called require_tenant! 401'd. From the user's perspective: they owned a tenant and a facility yesterday; today they couldn't reach either.

We worked through three increasingly correct framings before landing on the right fix:

1. First instinct: add a permissive self-read policy — user_id = current_setting('app.current_user_id')::uuid — and a new GUC alongside app.current_tenant_id. Implementation: ~120 lines across an extended RlsContext (new with_user/activate_user/clear_user API), a TenantContext concern change to set the user GUC on every authenticated request, and a service-level wrap on GetAvailableProfilesService. It would have worked.
2. Second pass: trim the speculative parts. Keep only with_user and the service wrap; drop the per-request GUC set/clear. Down to ~50 lines.
3. Third pass (the one we shipped): step further back. The audit query SELECT relname, relrowsecurity, ... FROM pg_class WHERE relnamespace = 'identity'::regnamespace showed that every other user-scoped identity table (sessions, refresh_tokens, profile_switches, users, devices) had no RLS. user_memberships was the outlier, not the rule. The right fix wasn't to invent new RLS machinery — it was to recognize that user_memberships belongs in the no-RLS group with the other user-scoped tables.

Migration 20260520160000_disable_rls_on_user_memberships.rb drops the policy and disables RLS. The fix is one file, no application code changes, no new GUCs, no per-request overhead. Bridge tables (§3) are the rule the original migration was missing; this case study is what the rule was extracted from.

The lesson generalizes. Every time the question "should this table have RLS?" comes up, the right answer comes from the row's ownership semantics, not from the column list. When the answer is non-obvious, find a table in the same category that's already classified and follow its lead. If the new table is structurally identical to sessions (joins a user to a tenant), classify it like sessions. If it's structurally identical to facility_settings (configuration owned by a tenant), classify it like facility_settings. The mistake was mechanical pattern-matching on a column name; the antidote is semantic pattern-matching on what the row represents.

What the audit surfaced once we started lookingCopy Anchor Link

Disabling RLS on user_memberships unblocked login but didn't end the work. The previous dev DB had been operated under a role with BYPASSRLS = true — meaning every query for years had effectively been running RLS-off. The day we transferred ownership to a non-bypassing role, every latent place that read an RLS'd table without a GUC active became visible in sequence as users clicked through the app.

What we found, in the order it broke:

1. Login — GetAvailableProfilesService enumerated facilities via tenant.facilities.operational with no GUC active. Fix: wrap each per-tenant enumeration in RlsContext.with(tenant.id). Service builds an array of "your tenants and their facilities" before any tenant is selected, so it's a textbook cross-tenant fan-out (§6.9).
2. Profile-switch — SwitchProfileService#validate_facility_access read Identity::Facility.find_by(...) and PractitionerRole.find_by(...) to validate the target — but the session is mid-switch and has no tenant_id yet. Fix: wrap the validation in RlsContext.with(@target_tenant_id).
3. Profile-switch (again) — after the validation fix, switch still 403'd. The root cause this time wasn't the database layer at all: Identity::UserMembership still included Shared::Concerns::TenantScoped, which silently injects where(tenant_id: Shared::Current.tenant.id) into every query as a Rails default_scope. A query that explicitly asked for WHERE tenant_id = target_id got compounded into WHERE tenant_id = current_tenant AND tenant_id = target_id — contradictory, zero rows, 403. This is the "two layers" trap of §3: dropping RLS at the database wasn't enough; the application-layer concern had to come off too.
4. Patient uploads endpoint — clicking the facility 404'd on Clinical::PatientUploadsController#status because require_facility! saw @current_facility = nil. The chain: TenantContext#set_tenant_from_session reads session.facility BEFORE set_rls_context activates the GUC. Under enforced RLS, that association read silently returns nil. Fix: hoist RlsContext.activate(@current_tenant) to run inside set_tenant_from_session, immediately after loading the tenant, before reading the facility — see §4.4 for the full anatomy. This is the most subtle of the bugs: the concern that exists to set the GUC was itself reading an RLS'd table without one active.
5. Signup-with-pending-invite — never triggered in dev because the test users didn't have pending invites, but Identity::Users::ProfileOrchestrationService was writing PractitionerRole.create! and StaffRole.create! with no GUC active. With RLS forced and WITH CHECK, those INSERTs would have raised new row violates row-level security policy the next time a signup hit the invite path. Found by proactive audit before users saw it. Same fix as the read paths: wrap each per-invite write in RlsContext.with(invite.tenant_id).
6. Invite-accept after login — Identity::Invites::AcceptPractitionerInviteService and AcceptStaffInviteService allow a user logged into Tenant A to accept an invite for Tenant B. The write must go to B but the GUC says A. Same fix; see §6.8 for the canonical cross-tenant write pattern.

Six distinct bugs, all the same family: an RLS'd table accessed without the matching GUC active. None new; all latent. The audit query (§8.5) listed every identity-schema table's RLS state in seconds — that's the leverage that let us find #5 and #6 before users did, after #1–#4 surfaced reactively.

The audit also caught two adjacent issues unrelated to the bridge-table classification but worth fixing together: three identity tables (facility_ai_settings, practitioner_ai_settings, facility_settings) had RLS enabled but not forced, leaving the table owner bypassing policy; and facility_settings's policy used the unsafe current_setting('app.current_tenant_id')::uuid form that raises rather than filters when the GUC is unset. Both fixed in migration 20260520160001_force_rls_on_identity_settings_tables.rb. See §6.1 for the migration pattern that prevents this drift going forward.

Cost of the BYPASSRLS-in-dev mistakeCopy Anchor Link

The deeper lesson: dev environments where the connecting role bypasses RLS are not testing what production tests. Every one of these six bugs was sitting in the codebase for months, never breaking, because BYPASSRLS=true was effectively a "skip the policy" flag on every query. The moment dev started enforcing RLS the same way prod does, six bugs surfaced inside a single login flow. The retrofit cost would be much lower if the dev role had been non-bypassing from day one. The rake rls:create_test_role task that provisions a non-bypassing role exists precisely so this regression is catchable in spec runs — use it.

Validating RLS fixes locallyCopy Anchor Link

The default dev postgres role typically has rolbypassrls = true (superuser implies bypass). RLS bugs are invisible against a bypassing role. Use the rake rls:create_test_role task (lib/tasks/rls.rake) to provision a non-bypassrls role for validating RLS changes:

bundle exec rake rls:create_test_role
# Then connect with: PGPASSWORD=rls_test_dev psql -h localhost -U kulcare_rls_test -d <db>

Or point your spec suite at the role to verify RLS enforcement under real conditions.

Setting up your dev DB to enforce RLS for realCopy Anchor Link

The create_test_role task above is for one-off validation. To make your everyday dev environment enforce RLS the way staging and production do — so latent bugs surface in dev instead of after deploy — see rls-dev-setup.md. It covers two paths (migrate-in-place if you want to keep your dev data, or recreate-from-scratch) and the verification checklist that confirms you're aligned.

If a colleague says "I can't reproduce that RLS bug" but you can, ask which one of these paths their dev DB is on. Pre-PR-#1480 dev setups silently bypass RLS via superuser ownership; same bug, different visibility.

10.2 The public_appointments / marketplace-discoverability tension (May 2026)Copy Anchor Link

The anonymous booking flow at POST /patient/public_appointments/send_code 404'd in QA (#1511). The frontend was pointed at /patient/* but the backend's send_code action lived at /clinical/*. The obvious hotfix was a one-line constants swap (#1515 did exactly that). The architectural question underneath was harder: why did two route mounts exist, and which domain genuinely owns the OTP-driven anonymous booking flow?

The follow-on collapse (#1516) moved the canonical mount to /patient/public_appointments/* on the argument that the OTP send_code → verify → update dance is fundamentally identity creation — minting an Identity::Patient from an anonymous phone number — which belongs in the patient domain, not the clinical domain (clinical is for actors who already exist). But that surfaced a deeper issue Copilot caught on review: Identity::Facility.find_by(id:) with no GUC silently returns nil under FORCE-RLS, because identity.facilities has the tenant_isolation policy keying off a tenant context the anonymous request can't have set.

The trap was that the same shape already existed in shipped production code (Patient::AppointmentOrders::CreateService, several read paths) — those work today only because the production Rails connection role is effectively BYPASSRLS. The moment that role moved to NOBYPASSRLS, every anonymous facility lookup across the codebase would silently break.

We worked through three increasingly correct framings before landing on the right fix:

1. First instinct: a SECURITY DEFINER Postgres function that does the (id) → row lookup, owned by a privileged role, callable by the app role. Plumbing across every call site, one function per table.
2. Second pass: a dedicated BYPASSRLS connection role + middleware to switch into it for anonymous read paths. Adds operational complexity (role provisioning, pgbouncer routing). Couples app code to DB-role awareness.
3. Third pass (the one we shipped): step further back. Marketplace search already runs through Typesense — which isn't RLS-protected and already serves anonymous traffic in full. The Postgres-RLS question only bites at the handoff: when a visitor clicks into a detail page or starts a booking, with an ID Typesense already exposed. The policy's job isn't to permit unrestricted enumeration. It's to honor what Typesense already exposed. A permissive FOR SELECT policy alongside tenant_isolation, gated on the same active = true flag that already drives the indexer scope, gives exactly that — with zero application code changes, isolation preserved on writes, and a single source of truth (the visibility flag) controlling both surfaces.

Migration 20260523120000_add_public_read_rls_policies_to_identity_tables.rb (#1518) added the public_read carve-out on the four affected tables (§6.2 has the full pattern). The fix landed as a single migration. Patient::PublicAppointmentsController (already merged via #1516) became correct under FORCE-RLS automatically — find_by(id:) resolves the facility under public_read, the service derives tenant.id, and the existing RlsContext.with(tenant.id) wrap handles the clinical writes.

The lesson is structural rather than mechanical. RLS policies aren't binary. When a table's data has both a tenant-private write shape AND a public-discoverability read shape, the right model is two policies expressing both halves — not a workaround that compromises one for the other. The case for invariants applies in reverse: if the marketplace and the RLS policy are gating visibility on the same flag, the system has one truth instead of two surfaces that can drift. The cross-reference comments in the indexer source files (facilities_collection.rb:82, practitioners_collection.rb:183) exist specifically to keep that truth singular.

What the audit didn't catch (yet)Copy Anchor Link

public_read covers the identity-side anonymous reads. There's a separate gap on the clinical-side anonymous reads that Clinical::Practitioners::AvailabilityService does for slot conflict-detection: clinical.encounters, clinical.appointments, clinical.practitioner_unavailability. These contain PHI columns so a row-level public_read carve-out is not the right fix. The right fix is to wrap those reads in RlsContext.with(facility.tenant_id) once the facility resolves under public_read. Tracked in #1671.

Status update (June 2026): staging now runs NOBYPASSRLS. This is no longer a future flip — the staging app role already carries NOBYPASSRLS, so any clinical-side anonymous read here that is not yet wrapped in RlsContext.with(...) is broken on staging right now (returns zero rows / raises on write), not "eventually". Treat the remaining #1671 items as live incidents to verify, not pre-flight cleanup.

There's also a sweep needed across Sidekiq jobs that do Identity::Facility.find_by(id:) for operational reasons (deactivated-facility branches in clinical export jobs, patient file notifications). public_read filters those rows out — under a bypass posture they're silently visible; under NOBYPASSRLS they silently return nil. With staging already on NOBYPASSRLS (above), the role is flipped — sweep these jobs now and wrap the operational lookups in RlsContext.with(target_tenant.id) (the job already knows the target tenant). Also tracked in #1671.

11. ReferencesCopy Anchor Link

• Code: backend/domains/shared/services/tenancy/rls_context.rb
• Code: backend/domains/shared/concerns/tenant_context.rb
• Code: backend/domains/shared/concerns/tenant_aware_job.rb
• Spec patterns: backend/domains/shared/spec/services/tenancy/rls_context_spec.rb
• ADR: docs/adrs/2026-04-23-002-patient-domain-architecture.md — patient vs clinical schema split
• QA: docs/guides/testing/patient-cross-tenant-qa.md — manual cross-tenant test plan
• Code: backend/domains/clinical/models/patient.rb — Clinical::Patient.each_for_user (the fan-out primitive); see §7.
• Code: backend/domains/clinical/services/patients/lhr_views/cross_tenant_runner.rb + longitudinal_record_service.rb — the clinical-record engine (.call / .resource / .find_resource) and the consent_verified gate; see §7.1–7.3.
• Code: backend/domains/patient/services/appointments/owned_appointment.rb — bespoke single-table finder pattern (#1077); see §7.1.
• Issue #1659 — clinical.patients self-access branch + app.current_patient_user_id, the discovery half of the fan-out.
• Issue #1077 — patient appointment 404s; the shared owned-appointment finder; see §7.
• PR #1387 → PR #1443 — RlsContext introduction + new-tenant wizard fix
• PR #1448 — RlsContext.with opens its own transaction when needed
• PR #1450 — session-scope all ambient GUC callsites (TenantContext, TenantAwareJob, Shared::Current, VideoInvitesController) + rake rls:create_test_role
• Migration 20260520160000_disable_rls_on_user_memberships.rb — disables RLS on the user↔tenant bridge table; see §10.1 for the case study.
• Migration 20260520160001_force_rls_on_identity_settings_tables.rb — retrofits FORCE ROW LEVEL SECURITY and the safe NULLIF(..., true) policy pattern onto three follow-up settings tables that drifted from the original convention; see §6.1.
• Migration 20260523120000_add_public_read_rls_policies_to_identity_tables.rb — adds public_read SELECT-only policies to facilities, organizations, facility_locations, practitioner_roles for marketplace discoverability; see §6.2 and §10.2.
• Issue #1517 + PR #1518 — the public_read policy work + 8 :rls_enforced specs.
• Issue #1511 + PRs #1515 (hotfix) and #1516 (collapse) — the public_appointments route consolidation that surfaced the public_read need; see §10.2.
• Code: backend/domains/identity/services/typesense/facilities_collection.rb + practitioners_collection.rb — the indexer source_scope filters that MUST stay aligned with the public_read policy condition.
• Spec: backend/domains/identity/spec/policies/public_read_rls_spec.rb — covers the contract (active visible, inactive hidden, authenticated tenant sees both, anonymous mutations rejected/no-op'd).
• May 2026 audit fixes (see §10.1 for the full chain):
  • backend/domains/identity/services/profiles/get_available_profiles_service.rb — three RlsContext.with(tenant.id) wraps for per-tenant facility enumeration.
  • backend/domains/identity/services/profiles/switch_profile_service.rb — RlsContext.with(@target_tenant_id) around facility + practitioner-role validation.
  • backend/domains/identity/services/users/profile_orchestration_service.rb — per-invite RlsContext.with(invite.tenant_id) for signup-with-pending-invite writes.
  • backend/domains/identity/services/invites/accept_practitioner_invite_service.rb and accept_staff_invite_service.rb — cross-tenant write wrap around the transaction body.
  • backend/domains/identity/models/user_membership.rb — removed include Shared::Concerns::TenantScoped; companion to the RLS-drop migration.
  • backend/domains/shared/concerns/tenant_context.rb — hoisted RlsContext.activate(@current_tenant) inside set_tenant_from_session so it runs before reading session.facility.
  • backend/domains/connect/controllers/personal_endpoint_base.rb — dropped the orphaned .unscoped_by_tenant call after UserMembership left TenantScoped.
• Deferred: docs/plans/2026-05-18-refactor-decouple-shared-current-from-rls-plan.md — remove the Shared::Current#tenant= implicit-GUC-write callback