Enterprise SQL Kernel Row-Level Security, Session Injection & Multi-Tenant Isolation Specification

1. Multi-Tenant Threat Model & Architectural Strategy

Multi-tenant SaaS architectures typically choose between three database tenancy patterns:

1. Database-per-Tenant: Maximum isolation, but creates unsustainable connection pool overhead and complex migration maintenance at scale.
2. Schema-per-Tenant: Moderate isolation, but generates migration drift and excessive schema metadata bloat as tenant counts expand.
3. Shared Database & Shared Schema with Kernel RLS: Single unified database schema where every tenant-scoped table includes a tenantId column, with access policies enforced directly by the relational database kernel.

Orbit adopts the third model, fortified by Enterprise SQL Row-Level Security (RLS) configured in database migrations 001_enable_rls_tenant_tables.sql and 048_enable_rls_missing_tables.sql. Even if an application server query omits tenant filters entirely (such as SELECT * FROM orders;), the database engine evaluates kernel policies and returns strictly the active tenant's records.

[Application Query Request: "SELECT * FROM orders;"]
                      |
                      v
       [Enterprise SQL Relational Engine]
                      |
                      v
       [Row-Level Security Evaluation Gate]
        - Reads: current_setting('app.current_tenant', true)
        - Executes: current_tenant_id()
        - Invariant: (is_rls_bypassed() OR "tenantId" = current_tenant_id())
                      |
        +-------------+-------------+
        |                           |
 [MATCHES TENANT]            [MISMATCH OR NULL]
        |                           |
 [Rows Returned]             [Silently Filtered: 0 Rows]

2. Interactive Database Security Inspector

Authorized database administrators and security compliance auditors inspect active kernel policies and session parameters via the administrative security interface:

3. Kernel Functions & Context Evaluation

The isolation layer uses two core procedural functions compiled into the relational database engine:

3.1 current_tenant_id() Session Extractor

Defined in 001_enable_rls_tenant_tables.sql, this function extracts the active tenant identifier from the transaction session settings:

CREATE OR REPLACE FUNCTION current_tenant_id() RETURNS uuid AS $$
DECLARE
    setting_val text;
    tenant_uuid uuid;
BEGIN
    setting_val := NULLIF(current_setting('app.current_tenant', true), '');
    IF setting_val IS NULL THEN
        RETURN NULL;
    END IF;

    BEGIN
        tenant_uuid := setting_val::uuid;
        RETURN tenant_uuid;
    EXCEPTION
        WHEN invalid_text_representation THEN
            SELECT id INTO tenant_uuid FROM tenants WHERE slug = setting_val LIMIT 1;
            RETURN tenant_uuid;
        WHEN OTHERS THEN
            RETURN NULL;
    END;
END;
$$ LANGUAGE plpgsql STABLE;

Key Design Decisions:

• current_setting('app.current_tenant', true): The secondary boolean argument true ensures that if the setting has not been defined in the current session, the call returns NULL rather than throwing a runtime SQL exception.
• UUID or Slug Interoperability: If the incoming session string is not a valid UUID (such as when an application passes a tenant slug like "dha-phase5"), the exception handler looks up the primary tenant UUID from the tenants table.
• STABLE Volatility Marker: Declaring the function as STABLE informs the relational query optimizer that within a single table scan or transaction, the function output will not fluctuate, enabling index optimization.

3.2 is_rls_bypassed() Super-Admin Guard

For scheduled platform maintenance, multi-tenant analytics rollups, or global database backups, background workers can explicitly enable administrative bypass mode:

CREATE OR REPLACE FUNCTION is_rls_bypassed() RETURNS boolean AS $$
BEGIN
    RETURN current_setting('app.rls_bypass', true) = 'on';
EXCEPTION
    WHEN OTHERS THEN RETURN false;
END;
$$ LANGUAGE plpgsql STABLE;

Unless the session variable app.rls_bypass is set to 'on', this function returns false.

4. Mandatory Policy Synthesis & FORCE ROW LEVEL SECURITY

In relational databases, enabling RLS on a table (ALTER TABLE ... ENABLE ROW LEVEL SECURITY) protects the table against queries executed by standard database roles. However, by default, the table owner (the database user that created the table) and superusers bypass row-level security policies automatically.

To eliminate this vulnerability, the migration executes FORCE ROW LEVEL SECURITY across all tenant-scoped tables:

DO $$
DECLARE
    tbl text;
    tenant_tables text[] := ARRAY[
        'users', 'api_tokens', 'categories', 'items', 'stock_locations', 'stock_movements',
        'customers', 'customer_khatas', 'invoices', 'invoice_items', 'payments',
        'cash_shifts', 'cash_shift_movements', 'dining_tables', 'orders', 'order_items',
        'recipes', 'recipe_ingredients', 'patients', 'patient_visits', 'doctors',
        'doctor_commissions', 'lab_tests', 'lab_test_components', 'lab_test_ranges',
        'lab_reports', 'lab_report_tests', 'lab_report_results', 'reagents', 'reagent_batches',
        'panel_claims', 'schools', 'school_classes', 'class_sections', 'guardians',
        'students', 'attendances', 'fee_heads', 'class_fees', 'exam_terms',
        'exam_subjects', 'exam_results', 'audit_logs'
    ];
BEGIN
    FOREACH tbl IN ARRAY tenant_tables LOOP
        EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY;', tbl);
        EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY;', tbl);
        
        EXECUTE format('DROP POLICY IF EXISTS tenant_isolation_policy ON %I;', tbl);
        
        EXECUTE format('
            CREATE POLICY tenant_isolation_policy ON %I
            FOR ALL
            USING (
                is_rls_bypassed() OR "tenantId" = current_tenant_id()
            )
            WITH CHECK (
                is_rls_bypassed() OR "tenantId" = current_tenant_id()
            );', tbl);
            
        EXECUTE format('CREATE INDEX IF NOT EXISTS idx_%I_tenantId ON %I ("tenantId");', tbl, tbl);
    END LOOP;
END $$;

4.1 Policy Clause Analysis

• FOR ALL: Applies the security policy uniformly across SELECT, INSERT, UPDATE, and DELETE operations.
• USING (...): Determines which rows are visible for reads, updates, and deletions. If tenantId does not match current_tenant_id(), the row is invisible to the query.
• WITH CHECK (...): Validates new or updated rows. An attempt to insert a record with a mismatched tenantId or update an existing record to point to another tenant throws a row-level policy violation exception.
• Index Optimization (idx_{table}_tenantId): Every table receives an index on "tenantId", allowing the database engine to evaluate the RLS filter clause using fast index scans.

5. Application Connection Pool Session Injection

When application servers execute queries through pooled database connections, connections are reused across multiple tenants. To prevent session leakage across requests, the data access layer injects tenant context with transaction-local scoping:

Using SET LOCAL ensures that when the transaction completes (either through COMMIT or ROLLBACK), the session variable is automatically cleared. This prevents connection poisoning when the database connection is returned to the pool.

6. Performance Benchmarks & Query Plan Telemetry

The relational persistence kernel optimizes Row-Level Security checks using indexed filter conditions:

7. Failure Modes & Recovery SOP

The following operational contingency protocols resolve database tenancy exceptions:

8. Cross-Vertical Internal Linkages

Kernel-level row-level security isolates tenant data across all product verticals:

• Customer Khata Debt Ledgers & FIFO Aging — Guarantees financial customer debt ledgers remain strictly confidential per retail merchant.
• Multi-Station KDS Routing & Kitchen Production — Prevents restaurant orders from displaying on kitchen screens of adjacent restaurant tenants.
• Diagnostic Report Lifecycle & Autoverify Rules — Ensures patient laboratory reports are accessible strictly to authorized clinical laboratory organizations.
• Registrar 360 Student Dossier & Clearance Gates — Protects academic student records from cross-campus or cross-district data leakage.
• Offline WAL Outbox Resilience — Mirrors server-side tenant isolation within local embedded client storage.