Multi-Tenancy Architecture

Note

This page covers features that require PostgreSQL. Row-Level Security and JSONB are not available in MySQL, SQLite, or SQL Server.

FraiseQL supports multi-tenancy through PostgreSQL Row-Level Security (RLS) and the JWT inject= pattern. The tenant context comes from a verified JWT claim — the Rust runtime forwards it to PostgreSQL as a session variable, and your RLS policies enforce isolation automatically.

Note

Architecture reminder: FraiseQL Python is compile-time only. There is no info.context, no @fraiseql.middleware, no async def mutations that call the database. Tenant isolation is implemented at the PostgreSQL layer via RLS, not in application code.

---

Strategy Comparison

Strategy	Isolation	Cost	Best For
Row-Level Security	Rows filtered by RLS policy	Lowest	High-volume SaaS, many tenants
Schema-per-Tenant	Separate PostgreSQL schemas	Medium	Mid-market SaaS
Database-per-Tenant	Dedicated database per tenant	Highest	Enterprise / regulated industries

This guide focuses on Row-Level Security, which is the most common pattern and integrates directly with FraiseQL’s inject= mechanism.

---

Row-Level Security with JWT Injection

How It Works

JWT (tenant_id claim) → Rust runtime → SET LOCAL app.tenant_id = '...' → PostgreSQL RLS policies fire

The Rust runtime reads the tenant_id claim from the verified JWT, sets app.tenant_id as a PostgreSQL session variable via SET LOCAL (transaction-scoped) before each query, and your RLS policies filter rows to the current tenant automatically.

This mechanism is transport-agnostic. Whether the request arrives as a GraphQL query, a REST call, or a gRPC RPC, the Rust runtime performs the same JWT extraction and SET LOCAL injection before the SQL executes. Tenant isolation is enforced at the PostgreSQL layer, not at the transport layer.

TOML Configuration

Configure session variable injection in fraiseql.toml:

fraiseql.toml

[[session_variables.variables]]
pg_name = "app.tenant_id"
source = "jwt"
claim = "tenant_id"

See TOML Configuration → session_variables for the full reference.

Python SDK: tenant_scoped

The Python SDK provides a shorthand — tenant_scoped=True on @fraiseql.type auto-injects tenant_id on all queries and mutations for the type:

@fraiseql.type(tenant_scoped=True, crud=True)
class Project:
    """Tenant-isolated project — no manual inject= needed."""
    id: ID
    name: str
    created_at: DateTime

For project-wide injection without per-type decoration, use [inject_defaults] in TOML:

fraiseql.toml

[inject_defaults]
tenant_id = "jwt:tenant_id"

1. Database Schema

Use the trinity pattern: every base table has pk_ (BIGINT identity), id (UUID), and identifier (TEXT). Add a tenant_id column to tenant-scoped tables.

db/schema/01_tables/tb_post.sql

CREATE TABLE tb_post (
    pk_post    BIGINT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id         UUID    DEFAULT gen_random_uuid() UNIQUE NOT NULL,
    identifier TEXT    UNIQUE NOT NULL,                   -- e.g. slug
    tenant_id  UUID    NOT NULL,
    fk_user BIGINT NOT NULL REFERENCES tb_user(pk_user),
    title      TEXT    NOT NULL,
    content    TEXT    NOT NULL,
    is_published BOOLEAN NOT NULL DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX idx_tb_post_id         ON tb_post (id);
CREATE UNIQUE INDEX idx_tb_post_identifier ON tb_post (tenant_id, identifier);
CREATE        INDEX idx_tb_post_tenant     ON tb_post (tenant_id);
CREATE        INDEX idx_tb_post_fk_user    ON tb_post (fk_user);

db/schema/01_tables/tb_user.sql

CREATE TABLE tb_user (
    pk_user    BIGINT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id         UUID    DEFAULT gen_random_uuid() UNIQUE NOT NULL,
    identifier TEXT    UNIQUE NOT NULL,                   -- e.g. email
    tenant_id  UUID    NOT NULL,
    name       TEXT    NOT NULL,
    email      TEXT    NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX idx_tb_user_id         ON tb_user (id);
CREATE UNIQUE INDEX idx_tb_user_identifier ON tb_user (tenant_id, identifier);
CREATE        INDEX idx_tb_user_tenant     ON tb_user (tenant_id);

2. Row-Level Security Policies

db/schema/01_tables/rls_policies.sql

-- Enable RLS on tenant-scoped tables
ALTER TABLE tb_post ENABLE ROW LEVEL SECURITY;
ALTER TABLE tb_user ENABLE ROW LEVEL SECURITY;

-- Policies read the session variable set by the Rust runtime
CREATE POLICY post_tenant_isolation ON tb_post
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY user_tenant_isolation ON tb_user
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

The Rust runtime sets app.tenant_id before every query using the value from the verified JWT. Your policies fire automatically — no application code needed.

3. Read Views

Views follow the v_ prefix convention and return id + data (JSONB):

db/schema/02_read/v_post.sql

CREATE VIEW v_post AS
SELECT
    p.id,
    jsonb_build_object(
        'id',           p.id::text,
        'identifier',   p.identifier,
        'title',        p.title,
        'content',      p.content,
        'is_published', p.is_published,
        'created_at',   p.created_at
    ) AS data
FROM tb_post p;
-- RLS on tb_post automatically restricts to current tenant

db/schema/02_read/v_user.sql

CREATE VIEW v_user AS
SELECT
    u.id,
    jsonb_build_object(
        'id',         u.id::text,
        'identifier', u.identifier,
        'name',       u.name,
        'email',      u.email
    ) AS data
FROM tb_user u;
-- RLS on tb_user automatically restricts to current tenant

4. Python Schema

schema.py

import fraiseql
from fraiseql.scalars import ID
from fraiseql.scalars import UUID

@fraiseql.type
class Post:
    id: ID
    identifier: str
    title: str
    content: str
    is_published: bool
    created_at: str

@fraiseql.type
class User:
    id: ID
    identifier: str
    name: str
    email: str

# Queries are scoped to the current tenant via RLS automatically.
# No inject= needed here — RLS handles tenant filtering.
@fraiseql.query
def posts(limit: int = 20, offset: int = 0) -> list[Post]:
    """Returns posts for the current tenant (enforced by RLS)."""
    return fraiseql.config(sql_source="v_post")

@fraiseql.query
def post(id: ID) -> Post | None:
    """Look up a post by UUID. RLS ensures it belongs to the current tenant."""
    return fraiseql.config(sql_source="v_post")

@fraiseql.mutation(
    sql_source="fn_create_post",
    operation="CREATE",
    inject={"tenant_id": "jwt:tenant_id"},   # forward tenant from JWT to SQL
)
def create_post(title: str, content: str, identifier: str) -> Post:
    """Create a new post in the current tenant."""
    pass

fraiseql.export_schema("schema.json")

5. Mutation Function

The fn_create_post function receives tenant_id from the Rust runtime (injected from the JWT), never from the client:

db/schema/03_functions/fn_create_post.sql

CREATE OR REPLACE FUNCTION fn_create_post(
    p_title      TEXT,
    p_content    TEXT,
    p_identifier TEXT,
    p_tenant_id  UUID,    -- injected from JWT tenant_id claim
    p_author_id  UUID     -- injected from JWT sub claim
)
RETURNS mutation_response
LANGUAGE plpgsql
AS $$
DECLARE
    v_new_id UUID := gen_random_uuid();
    v_result mutation_response;
BEGIN
    INSERT INTO tb_post (id, identifier, tenant_id, fk_user, title, content)
    SELECT
        v_new_id,
        p_identifier,
        p_tenant_id,
        pk_user,
        p_title,
        p_content
    FROM tb_user
    WHERE id = p_author_id
      AND tenant_id = p_tenant_id;    -- belt-and-suspenders: author must be in same tenant

    IF NOT FOUND THEN
        v_result.status  := 'failed:not_found';
        v_result.message := 'Author not found in tenant';
        RETURN v_result;
    END IF;

    v_result.status    := 'success';
    v_result.entity_id := v_new_id;
    RETURN v_result;
END;
$$;

---

Schema-per-Tenant

Each tenant gets a dedicated PostgreSQL schema. The table structure is identical across schemas; PostgreSQL’s search_path routes queries to the correct one.

Caution

Schema-per-tenant provisioning (creating schemas, running migrations per-schema) is handled outside of FraiseQL. FraiseQL serves queries once the schema is set up; it does not manage schema lifecycle.

Setting Up a Tenant Schema

-- Create schema for a new tenant
CREATE SCHEMA tenant_abc123;

-- Create tables using the trinity pattern
CREATE TABLE tenant_abc123.tb_post (
    pk_post    BIGINT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id         UUID    DEFAULT gen_random_uuid() UNIQUE NOT NULL,
    identifier TEXT    UNIQUE NOT NULL,
    fk_user BIGINT NOT NULL,
    title      TEXT    NOT NULL,
    content    TEXT    NOT NULL,
    is_published BOOLEAN NOT NULL DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX idx_tb_post_id ON tenant_abc123.tb_post (id);
CREATE INDEX idx_tb_post_fk_user   ON tenant_abc123.tb_post (fk_user);

-- Grant permissions to the application role
GRANT USAGE ON SCHEMA tenant_abc123 TO fraiseql_app;
GRANT SELECT, INSERT, UPDATE, DELETE
    ON ALL TABLES IN SCHEMA tenant_abc123 TO fraiseql_app;

For schema-per-tenant routing, the Rust runtime sets search_path = tenant_{id} per request based on the JWT tenant_id claim. Keep a public.tb_tenant table as a registry of valid tenant schemas.

---

Database-per-Tenant

Each tenant has a dedicated PostgreSQL database. Maintain a central registry that maps tenant_id to a connection URL. The Rust runtime resolves the correct connection before executing each request.

Central registry database

CREATE TABLE tb_tenant_database (
    pk_tenant_database BIGINT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id                 UUID    DEFAULT gen_random_uuid() UNIQUE NOT NULL,
    identifier         TEXT    UNIQUE NOT NULL,       -- e.g. tenant slug
    tenant_id          UUID    UNIQUE NOT NULL,
    database_url       TEXT    NOT NULL,
    is_active          BOOLEAN NOT NULL DEFAULT true,
    created_at         TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX idx_tb_tenant_database_id        ON tb_tenant_database (id);
CREATE UNIQUE INDEX idx_tb_tenant_database_tenant    ON tb_tenant_database (tenant_id);
CREATE UNIQUE INDEX idx_tb_tenant_database_identifier ON tb_tenant_database (identifier);

Database provisioning (creating databases, running migrations) is handled by your infrastructure tooling — Terraform, a provisioning service, or a PostgreSQL management script. FraiseQL serves queries once the database is registered.

---

Security: Derive Tenant from JWT, Not Client Input

Caution

Never trust a tenant_id value supplied by the client — as a GraphQL argument, a request header, or any other user-controlled input. Clients can forge these values. Derive the tenant exclusively from a server-verified JWT claim. FraiseQL validates the JWT signature before any query runs.

The correct pattern uses inject= to forward the JWT claim:

schema.py — correct

import fraiseql
from fraiseql.scalars import ID

@fraiseql.mutation(
    sql_source="fn_create_post",
    operation="CREATE",
    inject={
        "tenant_id": "jwt:tenant_id",   # verified JWT claim, not client input
        "author_id": "jwt:sub",
    },
)
def create_post(title: str, content: str, identifier: str) -> Post:
    """Create a post. Tenant and author come from the verified JWT."""
    pass

The anti-pattern to avoid:

schema.py — WRONG: never do this

# Do not accept tenant_id as a GraphQL argument. Any client can supply any value.
@fraiseql.query
def posts(tenant_id: str, limit: int = 20) -> list[Post]:   # WRONG
    return fraiseql.config(sql_source="v_post")

---

Verify Tenant Isolation

Before deploying, confirm that your RLS policies prevent cross-tenant access:

# Request as Tenant A — should return only Tenant A's posts
curl -s http://localhost:8080/graphql \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $TENANT_A_JWT" \
  -d '{"query":"{ posts { id title } }"}' | jq .

# Request as Tenant B — should return only Tenant B's posts, never Tenant A's
curl -s http://localhost:8080/graphql \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $TENANT_B_JWT" \
  -d '{"query":"{ posts { id title } }"}' | jq .

The two responses must contain disjoint sets of IDs. If any ID appears in both responses, your RLS policy is not enforcing isolation correctly.

Test Isolation in Code

Write a test that seeds data for two tenants and asserts neither can see the other’s records:

tests/test_tenant_isolation.py

import pytest

@pytest.mark.asyncio
async def test_tenant_isolation(test_client, tenant_a_token, tenant_b_token):
    # Seed a post for Tenant A
    await test_client.mutate(
        'mutation { createPost(title: "A post", content: "...", identifier: "a-post") { id } }',
        headers={"Authorization": f"Bearer {tenant_a_token}"},
    )

    # Query as Tenant B — must not see Tenant A's post
    result = await test_client.query(
        "{ posts { id title } }",
        headers={"Authorization": f"Bearer {tenant_b_token}"},
    )
    titles = [p["title"] for p in result["posts"]]
    assert "A post" not in titles

What Happens If the Tenant ID Is Missing

If a request arrives without a valid tenant context — no JWT, or a JWT missing the tenant_id claim — the Rust runtime returns 401 Unauthorized before the query reaches PostgreSQL:

{
  "errors": [
    {
      "message": "Missing required JWT claim: tenant_id",
      "extensions": { "code": "UNAUTHORIZED" }
    }
  ]
}

---

Performance

Index Tenant Columns

For Row-Level Security, indexes on tenant_id prevent full-table scans:

-- Already covered by the table definitions above.
-- For composite queries, add covering indexes:
CREATE INDEX idx_tb_post_tenant_created
    ON tb_post (tenant_id, created_at DESC);

CREATE INDEX idx_tb_post_tenant_published
    ON tb_post (tenant_id, is_published)
    WHERE is_published = true;

Connection Pooling

Strategy	Pool Recommendation
Row-Level Security	Single shared pool (PgBouncer pool_mode=transaction)
Schema-per-Tenant	Shared pool; SET search_path per transaction
Database-per-Tenant	Pool per database; size to (expected_concurrent_tenants × 2)

---

Related Guides

Authentication

JWT, OIDC, and field-level scopes

Multi-Database Support

Multi-Database — PostgreSQL, MySQL, SQLite, and SQL Server support

Custom Business Logic

SQL functions as resolvers

Server-Side Injection

All injection mechanisms — decorator inject=, session variables, headers, timestamps

Security

RBAC, input validation, and HTTPS

Naming Conventions

tb_, v_, fn_ prefixes and the trinity pattern