Multi-tenancy

Ovahol is multi-tenant at the facility level. Every facility owns its own PostgreSQL schema, and the tenancy middleware switches the connection into that schema for the length of one request.

Schemas

Schema	Holds	Scope
lookup	Reference data: device types, categories, failure codes, roles, subscription plans	Shared, read-mostly
core_public	Users, organizations, facilities, memberships, invitations, billing, notifications, audit	Shared system tables
public	PostgreSQL extensions only	Shared
tenant_facility_<uuid>	Device records, work orders, fault reports, PM schedules, lifecycle events, departments, locations	One per facility

The tenant schema name is derived from the facility UUID by replacing dashes with underscores:

facility 6f1c…  ->  tenant_facility_6f1c…

public is only in the path so extensions resolve; business tables are never created there.

The request pipeline

Tenant routes (/api/v1/tenant/...) pass through this chain, in order:

JWT auth
  -> authenticated rate limit
  -> subscription access      (402 when the org may not use tenant resources)
  -> facility context         (resolves X-Facility-ID and membership)
  -> facility access          (423 when the plan does not cover this facility)
  -> tenant schema            (SET LOCAL search_path, opens the transaction)
  -> handler

Each middleware can abort the chain. TenantSchemaMiddleware does the following, in one function, because the ordering matters:

1. Derives the schema name from the facility in the request context.
2. Calls EnsureTenantSchema, which lazily creates the schema and applies any missing tenant migrations on first access.
3. Begins a database transaction.
4. Runs SET LOCAL search_path = "<tenant schema>", public.
5. Puts the transaction, facility ID, schema name, and user ID into the request context.
6. Calls the handler, then commits only when the response status is below 400. A failing handler therefore rolls back everything it wrote.

Several routes deliberately live outside this chain because they act on a facility chosen by the caller rather than the caller's ambient context: cross-facility transfers, and the admin data-interchange endpoints on /api/v1/core/admin. Those authorize the target facility in the service layer.

Repositories need the transaction

Tenant repositories never reach for a pool. They read the transaction out of the context:

tx := tenantctx.MustTx(ctx) // panics when the middleware did not run

This is intentional. A missing transaction means the request escaped the tenancy chain, and a panic surfaces that immediately instead of quietly querying the wrong schema — or the shared one.

Shared tables are written through sqlc-generated queries, which address core_public. and lookup. explicitly and therefore do not depend on the search path.

Tenant queries

SET LOCAL search_path makes unqualified table names resolve to the tenant schema, so most tenant SQL is written without a schema prefix.

Some queries need to reach a different facility's schema (org-level rollups, cross-facility reporting). Those are hand-written .sql files under internal/modules/<module>/repository/sql/, embedded at compile time with //go:embed sql/*.sql and loaded by the module's QueryLoader. The loader substitutes with the quoted schema name:

SELECT count(*) FROM {{tenant_schema}}.device_record;

These queries are not sqlc-generated, because the schema name is only known per request. That also means sqlc cannot type-check them: treat the .sql file and the Go scan code as one unit.

Tenant migrations

Tenant schema migrations are separate from the shared migrations:

	Shared	Tenant
Location	backend/repository/migrations/	backend/internal/database/tenant_migrations/versions/
Applied by	golang-migrate (make migrate)	TenantMigrator, embedded at compile time
Naming	NNN_description.up.sql / .down.sql	NNN_description.sql

Tenant migrations are applied when a schema is first created and brought up to date by EnsureTenantSchema on the next request that touches it. The API also runs MigrateAllTenants at startup, so a deploy brings every existing facility schema forward without waiting for traffic. A new tenant migration therefore ships with the binary and needs no separate deploy step.

Because they run lazily, a migration must be safe to apply to a schema that is in use: prefer additive changes and backfills that can run while requests are being served.

Rules of thumb

• Never open a second connection inside a tenant request; use the context transaction so writes stay in one unit of work.
• Never qualify a tenant table with public or core_public.
• Never read another facility's schema without going through the explicit cross-facility path that authorizes the target facility.
• A new facility does not need a migration run: the schema is provisioned on first access.