Skip to content

Multi-Tenancy Architecture

Document Information

Attribute Value
Version 1.0.0
Last Updated January 6, 2026
Status Production Ready
Audience Platform Engineers, Security Team, Database Administrators

Table of Contents

  1. Executive Summary
  2. Multi-Tenancy Architecture Overview
  3. PostgreSQL Row-Level Security Implementation
  4. Tenant Context Propagation
  5. Data Isolation Guarantees
  6. Cross-Tenant Queries (Admin Only)
  7. Tenant Provisioning
  8. Security Considerations
  9. Performance Implications
  10. Testing Tenant Isolation
  11. Appendix: RLS Policy Reference

Executive Summary

Fulcrum implements a shared database, shared schema multi-tenancy model with PostgreSQL Row-Level Security (RLS) as the foundational isolation mechanism. This architecture provides:

  • Hard isolation at the database layer that cannot be bypassed by application code bugs
  • Performance efficiency through shared infrastructure and connection pooling
  • Operational simplicity with a single schema to maintain and migrate
  • Cost effectiveness for SaaS deployments serving many tenants

Every database query automatically filters data based on the current tenant context, which is established through gRPC metadata and propagated via PostgreSQL session variables.

Key Design Decisions

Decision Rationale
Shared database + shared schema Operational simplicity, easier migrations, cost-effective
RLS over application-level filtering Defense in depth, immune to application bugs
Session-variable-based context Per-transaction isolation, connection pool friendly
Granular API keys with scopes Fine-grained access control per integration

Multi-Tenancy Architecture Overview

Tenancy Model Comparison

Fulcrum uses Model B: Shared Database, Shared Schema:

+-------------------+-------------------+-------------------+
|     Model A       |     Model B       |     Model C       |
| Separate Database | Shared DB/Schema  | Shared Schema     |
|   per Tenant      | with RLS (Fulcrum)| App-Level Filter  |
+-------------------+-------------------+-------------------+
| + Full isolation  | + Cost effective  | + Simple impl     |
| + Custom configs  | + Easy migrations | - Security risk   |
| - Expensive       | + Connection pool | - Bug-prone       |
| - Complex ops     | + RLS enforcement | - No DB safety    |
+-------------------+-------------------+-------------------+

Architecture Diagram

                    +------------------+
                    |   gRPC Request   |
                    |  x-api-key: ...  |
                    +--------+---------+
                             |
                             v
              +-----------------------------+
              |     Auth Interceptor        |
              |  - Validate API key hash    |
              |  - Extract tenant_id        |
              |  - Inject into context      |
              +-------------+---------------+
                            |
                            v
              +-----------------------------+
              |    Service Layer (Go)       |
              |  - Business logic           |
              |  - runWithRLS() wrapper     |
              +-------------+---------------+
                            |
                            v
              +-----------------------------+
              |   PostgreSQL Transaction    |
              |  SET fulcrum.current_tenant |
              |  = 'tenant-uuid'            |
              +-------------+---------------+
                            |
                            v
              +-----------------------------+
              |   RLS Policy Evaluation     |
              |  WHERE tenant_id =          |
              |  current_setting(...)::uuid |
              +-----------------------------+

Tenant Isolation Strategy

  1. Authentication Layer: API key validated and mapped to tenant
  2. Context Injection: Tenant ID stored in Go context
  3. Database Layer: RLS session variable set per transaction
  4. Query Execution: All queries automatically filtered by RLS policies

PostgreSQL Row-Level Security Implementation

RLS-Enabled Tables

The following tables have RLS enabled with tenant isolation policies:

Table RLS Policy Isolation Key
tenants tenant_isolation_tenants id
budgets tenant_isolation_budgets tenant_id
policies tenant_isolation_policies tenant_id
policy_sets tenant_isolation_policy_sets tenant_id
envelopes tenant_isolation_envelopes tenant_id
policy_evaluations tenant_isolation_evaluations via envelope_id join
policy_approvals tenant_isolation_approvals via evaluation_id join
policy_audit_logs tenant_isolation_policy_audit_logs via evaluation_id join
subscriptions subscriptions_tenant_isolation tenant_id
billing_events billing_events_tenant_isolation tenant_id
audit_logs audit_logs_tenant_isolation org_id
incidents incidents_tenant_isolation tenant_id
retention_policies retention_policies_read Global (read-only)
attack_patterns attack_patterns_access Global (system-wide learning)

Session Variable Configuration

RLS policies use the PostgreSQL session variable fulcrum.current_tenant:

-- Setting the tenant context
SELECT set_config('fulcrum.current_tenant', 'tenant-uuid', true);

-- Reading the tenant context
SELECT current_setting('fulcrum.current_tenant')::uuid;

The true parameter makes the setting local to the current transaction, ensuring it is automatically reset when the transaction completes.

Policy Definition Examples

Direct tenant_id column (most tables):

-- From: infra/migrations/postgres/000002_enable_rls.up.sql
CREATE POLICY tenant_isolation_budgets ON fulcrum.budgets
    FOR ALL
    USING (tenant_id = current_setting('fulcrum.current_tenant')::uuid);

Join-based isolation (nested data):

-- Policy evaluations isolated via envelope relationship
CREATE POLICY tenant_isolation_evaluations ON fulcrum.policy_evaluations
    FOR ALL
    USING (
        envelope_id IN (
            SELECT id FROM fulcrum.envelopes
            WHERE tenant_id = current_setting('fulcrum.current_tenant')::uuid
        )
    );

Multi-level join (deeply nested data):

-- Policy approvals isolated via evaluation -> envelope chain
CREATE POLICY tenant_isolation_approvals ON fulcrum.policy_approvals
    FOR ALL
    USING (
        evaluation_id IN (
            SELECT id FROM fulcrum.policy_evaluations
            WHERE envelope_id IN (
                SELECT id FROM fulcrum.envelopes
                WHERE tenant_id = current_setting('fulcrum.current_tenant')::uuid
            )
        )
    );

RLS Bypass for System Operations

Some tables allow global access for system-level operations:

-- Attack patterns are shared system-wide for collective learning
CREATE POLICY attack_patterns_access ON attack_patterns
    FOR ALL USING (true);

-- Retention policies are global configuration
CREATE POLICY retention_policies_read ON fulcrum.retention_policies
    FOR SELECT
    USING (true);  -- Readable by all authenticated users

Tenant Context Propagation

Request Flow

1. Client sends gRPC request with x-api-key header
2. AuthInterceptor extracts and validates API key
3. Tenant ID injected into Go context
4. Service methods call runWithRLS() for database operations
5. PostgreSQL session variable set per transaction
6. RLS policies automatically filter all queries

gRPC Middleware Implementation

Location: internal/middleware/auth.go

The AuthInterceptor is responsible for:

  1. Extracting the x-api-key header from gRPC metadata
  2. Hashing the key with SHA-256
  3. Looking up the tenant via tenant.Store
  4. Injecting tenant ID and scopes into the context
// From: internal/middleware/auth.go
func (i *AuthInterceptor) Unary() grpc.UnaryServerInterceptor {
    return func(ctx context.Context, req interface{}, info *grpc.UnaryServerInfo, handler grpc.UnaryHandler) (interface{}, error) {
        // Skip public methods (health checks, reflection)
        if isPublicMethod(info.FullMethod) {
            return handler(ctx, req)
        }

        // Extract API key from metadata
        md, ok := metadata.FromIncomingContext(ctx)
        if !ok {
            return nil, status.Error(codes.Unauthenticated, "metadata is required")
        }

        keys := md.Get("x-api-key")
        if len(keys) == 0 {
            return nil, status.Error(codes.Unauthenticated, "x-api-key is required")
        }

        // Hash the API key
        hash := sha256.Sum256([]byte(keys[0]))
        hashStr := hex.EncodeToString(hash[:])

        // Look up tenant
        t, scopes, err := i.store.GetTenantByAPIKeyHash(ctx, hashStr)
        if err != nil {
            if errors.Is(err, tenant.ErrTenantNotFound) {
                return nil, status.Error(codes.Unauthenticated, "invalid api key")
            }
            return nil, status.Errorf(codes.Internal, "auth error: %v", err)
        }

        // Inject tenant context
        newCtx := context.WithValue(ctx, TenantIDKey, t.ID)
        newCtx = context.WithValue(newCtx, ScopesKey, scopes)
        return handler(newCtx, req)
    }
}

Context Key Types

// From: internal/middleware/auth.go
type contextKey string

const (
    TenantIDKey contextKey = "tenant_id"
    ScopesKey   contextKey = "scopes"
)

Helper Functions

// GetTenantID extracts the tenant ID from the context
func GetTenantID(ctx context.Context) (string, bool) {
    id, ok := ctx.Value(TenantIDKey).(string)
    return id, ok
}

// GetScopes extracts scopes from context
func GetScopes(ctx context.Context) ([]string, bool) {
    scopes, ok := ctx.Value(ScopesKey).([]string)
    return scopes, ok
}

// HasScope checks if the context has a specific scope (or admin *)
func HasScope(ctx context.Context, scope string) bool {
    scopes, ok := GetScopes(ctx)
    if !ok {
        return false
    }
    for _, s := range scopes {
        if s == "*" || s == scope {
            return true
        }
    }
    return false
}

Database Context Injection

Location: internal/costengine/storage.go

The runWithRLS pattern ensures every database operation sets the tenant context:

// From: internal/costengine/storage.go
func (s *Service) runWithRLS(ctx context.Context, tenantID string, fn func(tx *sql.Tx) error) error {
    tx, err := s.db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer func() { _ = tx.Rollback() }()

    // Set RLS variable (local to transaction)
    if tenantID != "" {
        _, err := tx.ExecContext(ctx,
            "SELECT set_config('fulcrum.current_tenant', $1, true)", tenantID)
        if err != nil {
            return fmt.Errorf("failed to set RLS context: %w", err)
        }
    }

    if err := fn(tx); err != nil {
        return err
    }

    return tx.Commit()
}

Data Isolation Guarantees

What Data Is Isolated

Data Type Isolation Level Notes
Budgets Per-tenant Cost limits, spend tracking
Policies Per-tenant Custom governance rules
Policy Sets Per-tenant Grouped policies
Envelopes Per-tenant Execution tracking
Evaluations Per-tenant Policy decision logs
Approvals Per-tenant Human review records
Audit Logs Per-tenant Compliance trail
Subscriptions Per-tenant Billing data
Incidents Per-tenant Security events

What Data Is Shared

Data Type Sharing Model Rationale
Attack Patterns System-wide Collective threat intelligence
Retention Policies Global config Infrastructure settings
Context Templates Optional sharing Templates can be tenant-specific or global

Audit Logging Per Tenant

Two audit logging tables exist:

  1. policy_audit_logs: Tracks actions on policy evaluations (APPROVED, DENIED, ESCALATED, etc.)
  2. audit_logs: General-purpose compliance audit trail

Both are isolated by tenant through RLS policies:

-- Policy audit logs isolated via evaluation chain
CREATE POLICY tenant_isolation_policy_audit_logs ON fulcrum.policy_audit_logs
    FOR ALL
    USING (
        evaluation_id IN (
            SELECT pe.id FROM fulcrum.policy_evaluations pe
            JOIN fulcrum.envelopes e ON pe.envelope_id = e.id
            WHERE e.tenant_id = current_setting('fulcrum.current_tenant')::uuid
        )
    );

-- General audit logs isolated by org_id
CREATE POLICY audit_logs_tenant_isolation ON fulcrum.audit_logs
    FOR ALL
    USING (org_id = current_setting('fulcrum.current_tenant', true));

Cross-Tenant Prevention Mechanisms

  1. Database Layer: RLS policies filter all SELECT, INSERT, UPDATE, DELETE operations
  2. Application Layer: Context validation in middleware
  3. API Layer: API key tied to specific tenant
  4. Testing: Automated isolation tests verify boundaries

Cross-Tenant Queries (Admin Only)

When Allowed

Cross-tenant queries are only permitted for:

  • System administration tasks
  • Aggregated analytics (anonymized)
  • Platform-wide security operations (attack pattern learning)
  • Database maintenance and migrations

Safe Bypass Methods

Method 1: Superuser Bypass

PostgreSQL superusers bypass RLS automatically. This should only be used for: - Schema migrations - Emergency maintenance - System-level reporting

-- As superuser, RLS is bypassed
SELECT * FROM fulcrum.budgets;  -- Returns all tenants' data

Method 2: Explicit Policy Bypass (Not Recommended)

-- Temporarily disable RLS (requires superuser)
ALTER TABLE fulcrum.budgets DISABLE ROW LEVEL SECURITY;
-- ... perform operation ...
ALTER TABLE fulcrum.budgets ENABLE ROW LEVEL SECURITY;

Method 3: Dedicated Admin Role

Create a role that bypasses RLS:

CREATE ROLE fulcrum_admin BYPASSRLS;
GRANT SELECT ON ALL TABLES IN SCHEMA fulcrum TO fulcrum_admin;

Production Best Practices

  1. Never expose admin credentials to application code
  2. Audit all cross-tenant access with dedicated logging
  3. Use separate connections for admin operations
  4. Implement approval workflows for cross-tenant queries
  5. Time-bound access using temporary credentials

Tenant Provisioning

Creating New Tenants

Step 1: Insert Tenant Record

INSERT INTO fulcrum.tenants (id, name, api_key_hash, settings, created_at, updated_at)
VALUES (
    gen_random_uuid(),
    'New Tenant Name',
    encode(sha256('initial-api-key'::bytea), 'hex'),
    '{"tier": "developer"}',
    NOW(),
    NOW()
) RETURNING id;

Step 2: Generate Initial API Key

// From: internal/tenant/store.go
func (s *Store) CreateAPIKey(ctx context.Context, key *APIKey) error {
    query := `
        INSERT INTO fulcrum.api_keys (
            id, tenant_id, key_hash, name, key_hint, scopes, expires_at, created_at, updated_at
        ) VALUES ($1, $2, $3, $4, $5, $6, $7, NOW(), NOW())
    `
    _, err := s.db.ExecContext(ctx, query,
        key.ID,
        key.TenantID,
        key.KeyHash,
        key.Name,
        key.KeyHint,
        pq.Array(key.Scopes),
        key.ExpiresAt,
    )
    return err
}

API Key Structure

Location: infra/migrations/postgres/000005_granular_api_keys.up.sql

CREATE TABLE IF NOT EXISTS fulcrum.api_keys (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES fulcrum.tenants(id) ON DELETE CASCADE,
    key_hash VARCHAR(64) NOT NULL UNIQUE,  -- SHA256 hex string
    key_hint VARCHAR(10),                   -- Last 4 chars for display
    name VARCHAR(255),                      -- Human-readable name
    scopes TEXT[] DEFAULT '{}',             -- E.g. ["policy:read", "policy:approve", "admin"]
    expires_at TIMESTAMPTZ,
    last_used_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

Scope Definitions

Scope Description
* Admin/root access (full permissions)
policy:read Read policy definitions
policy:write Create/update policies
policy:approve Approve pending policy evaluations
envelope:read Read execution envelopes
envelope:write Create/manage envelopes
budget:read Read budget data
budget:write Manage budgets

Initial Policy Templates

When provisioning a new tenant, create default policies:

// Example: Default rate limiting policy
defaultPolicy := &policyv1.Policy{
    TenantId:  tenantID,
    Name:      "Default Rate Limit",
    PolicyType: policyv1.PolicyType_PRE,
    Rules: []Rule{
        {Field: "requests_per_minute", Operator: "<=", Value: "100"},
    },
    Priority: 0,
    Enabled:  true,
}

Security Considerations

Risks and Mitigations

Risk Mitigation
Application bug bypasses RLS RLS enforced at database layer regardless of app code
SQL injection in tenant ID UUID validation before use; parameterized queries
Session variable manipulation Variable set per-transaction, auto-resets
Connection pool tenant bleed is_local=true ensures transaction isolation
Privilege escalation API key scopes checked in middleware
Timing attacks on auth Constant-time comparison for API key validation

SQL Injection Prevention

The tenant ID is always: 1. Validated as a UUID before use 2. Passed via parameterized queries 3. Type-cast in PostgreSQL (::uuid)

// Safe: Parameterized query
_, err := tx.ExecContext(ctx,
    "SELECT set_config('fulcrum.current_tenant', $1, true)", tenantID)

// Safe: UUID type enforcement in policy
USING (tenant_id = current_setting('fulcrum.current_tenant')::uuid)

API Key Security

  1. Hashing: Keys stored as SHA-256 hashes, never plaintext
  2. Expiration: Support for time-limited keys
  3. Scopes: Fine-grained permission control
  4. Hints: Only last 4 characters stored for identification
// From: internal/middleware/auth.go
hash := sha256.Sum256([]byte(keys[0]))
hashStr := hex.EncodeToString(hash[:])

Authentication Attack Prevention

The security test suite (tests/security/api_security_test.go) validates:

  • Authentication bypass attempts
  • SQL injection in API keys
  • Header manipulation attacks
  • Cross-tenant access attempts
  • Timing attack resistance

Performance Implications

RLS Policy Overhead

RLS policies add a WHERE clause to every query. For simple policies (direct tenant_id comparison), overhead is minimal with proper indexing.

Indexes for RLS Performance:

-- All tenant-filtered tables should have tenant_id indexed
CREATE INDEX IF NOT EXISTS idx_budgets_tenant ON budgets(tenant_id);
CREATE INDEX IF NOT EXISTS idx_policies_tenant ON policies(tenant_id);
CREATE INDEX IF NOT EXISTS idx_envelopes_tenant ON envelopes(tenant_id);

Join-Based Policy Performance

Multi-level join policies (like policy_approvals) can be slower. Mitigations:

  1. Denormalization: Consider adding tenant_id directly to nested tables
  2. Materialized Views: For heavy read workloads
  3. Query Optimization: Ensure join columns are indexed

Connection Pooling Compatibility

The set_config(..., true) approach is connection-pool friendly:

  • Setting is scoped to transaction only
  • No persistent connection state
  • Safe for PgBouncer in transaction mode

Benchmarking RLS Impact

-- Compare query plans with and without RLS
EXPLAIN ANALYZE SELECT * FROM fulcrum.budgets WHERE name = 'Test';

-- With RLS, plan shows additional filter:
-- Filter: (tenant_id = 'uuid-here'::uuid)

Testing Tenant Isolation

Automated Test Suite

Location: tests/security/tenant_isolation_test.go

// TestTenantIsolation verifies that RLS policies prevent cross-tenant data access
func TestTenantIsolation(t *testing.T) {
    // Create two test tenants
    tenantA := uuid.New()
    tenantB := uuid.New()

    // Create a budget for Tenant A
    budgetID := uuid.New()
    _, err = db.ExecContext(ctx, `
        INSERT INTO fulcrum.budgets (id, tenant_id, name, max_cost_usd, ...)
        VALUES ($1, $2, 'Test Budget A', 100.00, ...)
    `, budgetID, tenantA)

    t.Run("TenantCanAccessOwnData", func(t *testing.T) {
        // Set tenant context to Tenant A
        tx.ExecContext(ctx, "SELECT set_config('fulcrum.current_tenant', $1, true)", tenantA)

        // Tenant A should see their budget
        var count int
        tx.QueryRowContext(ctx, "SELECT COUNT(*) FROM fulcrum.budgets WHERE id = $1", budgetID).Scan(&count)
        assert.Equal(t, 1, count, "Tenant A should see their own budget")
    })

    t.Run("TenantCannotAccessOtherTenantData", func(t *testing.T) {
        // Set tenant context to Tenant B
        tx.ExecContext(ctx, "SELECT set_config('fulcrum.current_tenant', $1, true)", tenantB)

        // Tenant B should NOT see Tenant A's budget
        var count int
        tx.QueryRowContext(ctx, "SELECT COUNT(*) FROM fulcrum.budgets WHERE id = $1", budgetID).Scan(&count)
        assert.Equal(t, 0, count, "Tenant B should NOT see Tenant A's budget")
    })
}

Test Utility Functions

Location: internal/testutil/db.go

// CreateTestTenant inserts a test tenant and sets RLS context
func (tdb *TestDB) CreateTestTenant(t *testing.T, name string) string {
    var id string
    err := tdb.DB.QueryRow(`
        INSERT INTO tenants (name, api_key_hash, settings)
        VALUES ($1, 'dummy_hash', '{}')
        RETURNING id::text
    `, name).Scan(&id)

    // Set RLS context for subsequent queries
    _, err = tdb.DB.Exec(fmt.Sprintf("SET fulcrum.current_tenant = '%s'", id))
    return id
}

// SetTenantContext sets the RLS tenant context for a connection
func (tdb *TestDB) SetTenantContext(t *testing.T, tenantID string) {
    _, err := tdb.DB.Exec(fmt.Sprintf("SET fulcrum.current_tenant = '%s'", tenantID))
}

Cross-Tenant Write Prevention

// TestCrossTenantWriteAttempt verifies writes to another tenant's data are blocked
func TestCrossTenantWriteAttempt(t *testing.T) {
    t.Run("CannotUpdateOtherTenantBudget", func(t *testing.T) {
        // Set context to Tenant B
        tx.ExecContext(ctx, "SELECT set_config('fulcrum.current_tenant', $1, true)", tenantB)

        // Try to update Tenant A's budget - should affect 0 rows
        result, err := tx.ExecContext(ctx, "UPDATE fulcrum.budgets SET name = 'Hacked' WHERE id = $1", budgetID)

        rowsAffected, _ := result.RowsAffected()
        assert.Equal(t, int64(0), rowsAffected, "Update to other tenant's data should affect 0 rows")
    })

    t.Run("CannotDeleteOtherTenantBudget", func(t *testing.T) {
        // Set context to Tenant B
        tx.ExecContext(ctx, "SELECT set_config('fulcrum.current_tenant', $1, true)", tenantB)

        // Try to delete Tenant A's budget - should affect 0 rows
        result, err := tx.ExecContext(ctx, "DELETE FROM fulcrum.budgets WHERE id = $1", budgetID)

        rowsAffected, _ := result.RowsAffected()
        assert.Equal(t, int64(0), rowsAffected, "Delete of other tenant's data should affect 0 rows")
    })
}

Running Isolation Tests

# Run all security tests (requires running database)
DATABASE_URL="postgres://..." go test -v ./tests/security/...

# Run tenant isolation tests specifically
go test -v -run TestTenantIsolation ./tests/security/

# Run cross-tenant write tests
go test -v -run TestCrossTenantWriteAttempt ./tests/security/

Appendix: RLS Policy Reference

Complete Policy List

Migration Table Policy Name Type
000002 tenants tenant_isolation_tenants Direct ID match
000002 budgets tenant_isolation_budgets Direct tenant_id
000002 policies tenant_isolation_policies Direct tenant_id
000002 policy_sets tenant_isolation_policy_sets Direct tenant_id
000002 envelopes tenant_isolation_envelopes Direct tenant_id
000002 policy_evaluations tenant_isolation_evaluations Join via envelope
000002 policy_approvals tenant_isolation_approvals Multi-join
000006 incidents incidents_tenant_isolation Direct tenant_id
000006 attack_patterns attack_patterns_access Global access
000007 retention_policies retention_policies_read Global read
000010 policy_audit_logs tenant_isolation_policy_audit_logs Multi-join
000011 subscriptions subscriptions_tenant_isolation Direct tenant_id
000011 billing_events billing_events_tenant_isolation Direct tenant_id
000012 audit_logs audit_logs_tenant_isolation Direct org_id

Session Variable Reference

Variable Purpose Scope
fulcrum.current_tenant Primary tenant isolation Transaction-local
app.current_tenant_id Alternative (billing tables) Transaction-local

Migration Files

  • infra/migrations/postgres/000001_initial_schema.up.sql - Base schema with tenant_id columns
  • infra/migrations/postgres/000002_enable_rls.up.sql - Core RLS policies
  • infra/migrations/postgres/000005_granular_api_keys.up.sql - API key table
  • infra/migrations/postgres/000006_immune_system.up.sql - Incidents + attack patterns RLS
  • infra/migrations/postgres/000007_add_retention_policies.up.sql - Retention RLS
  • infra/migrations/postgres/000010_policy_audit_logs.up.sql - Audit log RLS
  • infra/migrations/postgres/000011_billing.up.sql - Billing RLS
  • infra/migrations/postgres/000012_audit_logs.up.sql - General audit RLS


Document Version: 1.0.0 | Last Updated: January 6, 2026