Multi-Tenancy Architecture

Each tenant is identified by a UUID. The tenant_id wrapper type provides type-safe handling of tenant identifiers:

namespace ores::utility::uuid {

class tenant_id {
public:
    static tenant_id system();  // Returns system tenant (max UUID)
    static std::optional<tenant_id> from_string(const std::string& s);
    static tenant_id generate();

    const boost::uuids::uuid& value() const;
    std::string to_string() const;

    bool operator==(const tenant_id& other) const;
    auto operator<=>(const tenant_id& other) const;

private:
    explicit tenant_id(boost::uuids::uuid id);
    boost::uuids::uuid id_;
};

}

The ores_iam_tenants_tbl table stores tenant definitions. It has two UUID columns that serve different purposes:

This design follows a key principle: the tenants table itself is managed by the system tenant. All tenant records have tenant_id = system because the system owns and manages the tenant registry.

┌─────────────────────────────────────────────────────────────────────┐
│                      ores_iam_tenants_tbl                           │
├──────────────────────────────┬──────────────────────────────────────┤
│ id (the tenant's own ID)     │ tenant_id (always system tenant)     │
├──────────────────────────────┼──────────────────────────────────────┤
│ 197fae34-...-cfa4            │ ffffffff-ffff-ffff-ffff-ffffffffffff │
│ (Example Corp)               │ (system owns this record)            │
├──────────────────────────────┼──────────────────────────────────────┤
│ b7b302e6-...-c9a9            │ ffffffff-ffff-ffff-ffff-ffffffffffff │
│ (ACME Corp)                  │ (system owns this record)            │
├──────────────────────────────┼──────────────────────────────────────┤
│ ffffffff-ffff-ffff-ffff-fff  │ ffffffff-ffff-ffff-ffff-ffffffffffff │
│ (System tenant itself)       │ (system owns this record)            │
└──────────────────────────────┴──────────────────────────────────────┘

When an account belongs to "Example Corp", its tenant_id column references the tenant's id (not the tenant_id column):

-- Accounts reference tenants.id, not tenants.tenant_id
SELECT a.username, t.name as tenant_name
FROM ores_iam_accounts_tbl a
JOIN ores_iam_tenants_tbl t ON a.tenant_id = t.id  -- Note: t.id, not t.tenant_id
WHERE a.valid_to = ores_utility_infinity_timestamp_fn()
  AND t.valid_to = ores_utility_infinity_timestamp_fn();

This pattern applies to all tenant-scoped tables: they reference tenants.id to identify which tenant owns the record.

The system tenant is defined as the maximum UUID value:

ffffffff-ffff-ffff-ffff-ffffffffffff

The system tenant is used for:

• System-level operations that span all tenants, by acting inside each one; a session in the system tenant does not see them by itself
• SuperAdmin accounts
• Bootstrap and provisioning operations
• Default context when no specific tenant is configured

The max UUID was chosen because:

• It is a valid UUID that will not conflict with randomly generated UUIDs
• It is easily recognizable in logs and debugging
• It avoids issues with nil UUID (all zeros) which some databases treat as NULL

Each tenant has a type that determines its operational characteristics, available features, and the level of controls enforced. Tenant types are stored in the ores_iam_tenant_types_tbl lookup table and validated on tenant creation.

Each tenant contains parties that represent business units. Parties have their own type taxonomy and RLS-based data isolation. See the companion document projects/ores.refdata/modeling/multi_party.org for the full multi-party architecture, including party-level RLS, visible party sets, and the login flow extension.

PostgreSQL RLS policies enforce tenant isolation at the database level. Each transaction sets a transaction-local variable that RLS policies use to filter data:

select set_config('app.current_tenant_id', 'tenant-uuid-here', true);

All queries on tenant-scoped tables automatically filter by this value, preventing cross-tenant data access.

The value comes from the request, not from the service. A request carries an Access Token whose claims state the tenant and the party, and every transaction of the request's context sets the variables from them. A service that calls with its own identity, by Client Credentials, therefore has no party and sees no party rows.

The tenant variable is one mechanism; what a policy does with it is the decision, and not every row belongs to a tenant. A row belongs to a tenant, to the system tenant, or to the installation — the deployment's own facts, which no tenant owns. Three shapes are in use, and only one of them can hand one tenant's rows to another:

Naming the row's owner is what decides which shape it may take, and the difference between the last two is which side of the comparison the session sits on. Read Tenant ownership and the information flow before adding a policy.

Reaching a tenant's data from outside it is not a fourth shape: it is a session inside that tenant, by Token Exchange or with_tenant().

This is a special case. It applies to the accounts table and to no other table. Do not copy it to another entity. A party-owned entity takes the party-isolation shape, which keys on its own party_id column and on the session's visible party set. That shape is the rule, and accounts are the one reviewed exception to it.

An account has no party_id column, because a person works in several parties. The links in ores_iam_account_parties_tbl say which. The party column shape therefore cannot scope accounts, and the visible party set is the wrong scope: it holds the party the session acts for and the parties below it, while an account works in the parties it is linked to. A head of group linked to several parties would lose the parties it does not act for.

The policy is accounts_party_visibility_policy in projects/ores.sql/create/iam/iam_rls_policies_create.sql. It is hand-written, not generated. It is a restrictive policy on reads. It is ANDed with the own-tenant policy, so tenant isolation does not change.

A person whose account has no party links gets no party set, so they see their own account and no other. This includes a tenant administrator: the administrator sees the tenant because its account is linked to every party of the tenant, and not because the policy names the role. A tenant provisioned without party links would show its administrator only the administrator's own account until the links exist.

The function ores_iam_actor_party_ids_fn() returns the parties the actor's account is linked to. The actor is app.current_actor, which every user request sets from the token. The function is security definer. It reads the actor's account to find the links, and without that it would apply the policy to itself. The policy wraps its function calls in a select, so the planner runs each once per query and not once per row.

The shared accounts are the second part of the exception. The service accounts and the super admin belong to the system tenant, and a service writes under its own name into the history of every tenant. The column shared marks the rows that every tenant may read. The select policy accounts_select_policy admits the current tenant and the system tenant rows with shared set. The party rule passes a shared row, because a shared account works in no party. The insert, update and delete policies admit the current tenant only, so no tenant writes a shared row. The super admin is a user account that humans sign in with, and it is shared by its flag and not by its type. The generated repository states :system_tenant_visible: true. The standard that every table read across tenants follows is Data read across tenants.

A record that names an account checks that it exists through ores_iam_account_exists_fn(id). The party rule hides every account from a service, because a service works in no party, so a select on the accounts table fails for the inbox service. The function is security definer and returns yes or no. It takes the tenant from the session and answers for the current tenant and for the shared accounts of the system tenant.

What the exception does not do:

• It does not limit writes. A write needs the write code of the operation, and the own-tenant policy still bounds it.
• It does not reach the other IAM tables. Credentials, contact information, sign-ins and sessions are separate tables. Each has its own policy and its own permission code.
• It does not make iam::accounts:read a read of the tenant. The permission says that a caller may read accounts. The policy says which accounts. Member holds the permission for this reason; see Authorised reads.

A database::context names who a statement acts for. It holds the tenant and, optionally, the party and the parties that party sees, which the row-level security policies read; the actor, the end user behind the request, which modified_by records; and the service account, the service that performs the write, which performed_by records.

class context {
public:
    explicit context(pq::connection_options options,
                     utility::uuid::tenant_id tenant_id,
                     std::string actor = "",
                     std::string service_account = "",
                     pq::open_policy policy = {});

    const utility::uuid::tenant_id& tenant_id() const;

    context with_tenant(utility::uuid::tenant_id tenant_id, std::string actor) const;
    context with_party(utility::uuid::tenant_id tenant_id,
                       boost::uuids::uuid party_id,
                       std::vector<boost::uuids::uuid> visible_party_ids,
                       std::string actor) const;

    pq::pool& pq_pool() const;
    pq::session_context pq_session() const;
};

Key properties:

• The tenant ID is mandatory at construction.
• with_tenant() and with_party() return a light copy for another tenant or party. The copy keeps the service account, the roles, the pool and any bound unit of work, so narrowing a context never widens what it may do.
• Every copy shares one libpq pool, which the context opens on first use.

A pooled connection carries no tenant. Every transaction that a context opens sends its settings with its first statement, in the same pipeline as begin:

select set_config('app.current_tenant_id', $1, true),
       set_config('app.current_party_id', $2, true),
       set_config('app.visible_party_ids', $3, true),
       set_config('app.current_actor', $4, true),
       set_config('app.current_service', $5, true),
       set_config('TimeZone', 'UTC', true);

The third argument, true, makes each setting local to the transaction. PostgreSQL clears them at commit or rollback, so the next transaction on that connection starts with no tenant and cannot read rows of the previous one. The settings cost no extra round trip: they travel in the pipeline with begin and the first statement.

A unit of work binds its context to its transaction. Every repository call made with that context runs on the one transaction, under the one set of settings.

A request names its caller in a JWT bearer token, in the Authorization header of its NATS message. A downstream service forwards the end user's token in X-Delegated-Authorization. The token's claims carry the tenant, and optionally the active party, the parties it may see, the username and the roles.

The tenant is resolved once, at login, and signed into the token. A request cannot change it, because the service verifies the signature before it reads any claim.

ores::service::service::make_request_context() turns a message into a database::context. It verifies the token, then narrows the service's base context to the token's tenant, or to its tenant and party:

if (!claims->party_id || claims->party_id->empty())
    return base_ctx.with_tenant(*tid_result, claims->username.value_or(""))
        .with_roles(claims->roles);

return base_ctx
    .with_party(*tid_result, party_id, std::move(visible_ids),
                claims->username.value_or(""))
    .with_roles(claims->roles);

This ensures:

• Each request operates in the correct tenant context.
• The username becomes the actor, which modified_by records.
• The pool is shared: the context copy is light.
• Tenant context is immutable for the request lifetime.
• Party context sets app.visible_party_ids in each transaction for RLS.

A token that is absent, expired, unsigned or without a tenant is refused as unauthorized. A malformed party falls back to tenant scope, because the party only narrows a context that the tenant already authorises.

For requests from authenticated users:

┌─────────────────────┐
│  Incoming Request   │
└──────────┬──────────┘
           │
           ▼
┌─────────────────────┐
│ verify the bearer   │
│ token               │
└──────────┬──────────┘
           │
           ▼
┌─────────────────────┐
│ read the tenant and │
│ party claims        │
└──────────┬──────────┘
           │
           ▼
┌─────────────────────┐
│ make_request_       │
│ context(ctx, msg)   │
└──────────┬──────────┘
           │
           ▼
┌─────────────────────┐
│ Execute operation   │
│ in tenant context   │
└─────────────────────┘

Example:

auto ctx = ores::service::service::make_request_context(ctx_, msg, verifier_);
if (!ctx) {
    error_reply(nats_, msg, ctx.error());
    return;
}

repository::account_repository repo;
auto accounts = repo.read_latest(*ctx);

For requests before authentication (login, signup, account creation):

┌─────────────────────┐
│  Incoming Request   │
│  (Login/Signup)     │
└──────────┬──────────┘
           │
           ▼
┌─────────────────────┐
│ Parse principal     │
│ user@hostname       │
└──────────┬──────────┘
           │
           ▼
┌─────────────────────┐
│ Has hostname?       │
└──────────┬──────────┘
           │
    ┌──────┴──────┐
    │ Yes         │ No
    ▼             ▼
┌─────────┐  ┌───────────┐
│ Lookup  │  │ Use       │
│ tenant  │  │ handler's │
│ by host │  │ tenant    │
└────┬────┘  └─────┬─────┘
     │             │
     └──────┬──────┘
            │
            ▼
┌─────────────────────┐
│ Execute in resolved │
│ tenant context      │
└─────────────────────┘

Example:

database::context operation_ctx = [&]() {
    if (!hostname.empty()) {
        const auto tenant_id =
            database::service::tenant_context::lookup_by_hostname(
                ctx_, hostname);
        return database::service::tenant_context::with_tenant(
            ctx_, tenant_id.to_string());
    } else {
        return ctx_.with_tenant(ctx_.tenant_id());
    }
}();

Tenants can be associated with hostnames. When a principal includes a hostname (e.g., john@acme.example.com), the system looks up the tenant by that hostname:

tenant_id lookup_by_hostname(const database::context& ctx,
                             const std::string& hostname);

This enables:

• Multi-tenant SaaS deployments where each customer has their own subdomain
• Automatic tenant resolution without explicit tenant selection

When no hostname is provided in the principal:

• In production: uses system tenant for administrative operations
• In testing: uses the test fixture's tenant for isolation

A request pays for one context copy: the tenant, the party list, the actor and a reference to the shared pool. The tenant settings cost no round trip of their own: pq::transaction sends them in the pipeline with begin and the first statement.

• One pool per service process, shared by every tenant and party.
• The pool opens its connections at startup and keeps them. No query opens a connection.
• pgbouncer bounds the server connections of the whole fleet.
• RLS handles isolation at the query level.

• All tenant-scoped tables have RLS policies
• Queries automatically filter by app.current_tenant_id
• Cross-tenant data access is impossible at the SQL level except through a reviewed widening: a policy whose body compares the session to the system tenant admits every tenant's rows to that session. check_rls_system_tenant_widenings.py lists every one and fails a new one, and Read each tenant's cache partition inside its tenant is removing them.

• Each token names exactly one tenant.
• The tenant is signed into the token, so a request cannot change it.
• A refused token gives no context, so no statement runs for it.

• Each request creates its own tenant context
• Contexts are immutable values.
• Tenant settings are local to a transaction, so concurrent requests, and consecutive transactions on one pooled connection, cannot see each other's tenant context.

context_factory::make_context() builds a service's base context from its configuration. The tenant defaults to the system tenant; the service account names the service and becomes the connection's application_name; the open policy says how often and how long the pool retries opening its connections:

auto options = to_connection_options(cfg.database_options);
options.application_name = cfg.service_account;

const pq::open_policy policy{.attempts = cfg.num_attempts,
                             .wait = std::chrono::seconds(cfg.wait_time_in_seconds),
                             .shape = ...};

utility::uuid::tenant_id tenant_id = utility::uuid::tenant_id::system();
if (!cfg.database_options.tenant.empty())
    tenant_id = /* parsed from cfg.database_options.tenant */;

Each request then narrows this base context to its token's tenant.

In tests, ores::testing::database_helper builds the context with the test tenant that the test environment names, and can switch it to party scope. Each test run provisions its own tenant, so runs do not interfere.