Enterprise Multi-Tenant SaaS Database Engine

A production-grade PostgreSQL architecture for a multi-tenant issue tracking system (similar to Jira or Linear). This project demonstrates kernel-level data isolation, hierarchical data structures, and immutable audit logging.

🚀 Key Features

• Strict Multi-Tenancy (RLS): Implements PostgreSQL Row-Level Security to guarantee complete data isolation between organizations without relying on application-layer filtering.
• Hierarchical Task Engine: Utilizes the ltree extension for blazing-fast traversal of deeply nested tasks (Epic > Task > Subtask), replacing slow recursive CTEs.
• Dynamic Custom Fields: Implements a GIN-indexed JSONB schema definition engine, allowing per-tenant custom fields without altering relational tables.
• Immutable Audit Logging: Uses SECURITY DEFINER database triggers to automatically capture row-level state changes, ensuring SOC2-compliant historical tracking.
• Optimized for Scale: Employs Partial Indexes for active queries and BRIN (Block Range) indexes for infinite-growth audit tables.

🏗️ Architecture Diagram

The System Architecture & Security Flow

flowchart TD
    subgraph Application_Layer [Application Layer]
        Client[Frontend / Client] -->|API Request + JWT| API[Backend API Node.js/Java]
    end

    subgraph PostgreSQL_Database [PostgreSQL Database Engine]
        API -->|1. BEGIN TRANSACTION \n 2. SET LOCAL app.tenant_id \n 3. SELECT / UPDATE| Pool[Connection Pool saas_api_user]
        
        Pool --> RLS{Row-Level Security Gateway}
        RLS --"tenant_id mismatch"--> Deny((Data Blocked))
        
        subgraph Schema_Identity [Schema: identity]
            Tenants[(tenants)]
            Users[(users)]
            Memberships[(tenant_memberships)]
        end

        subgraph Schema_Core [Schema: core]
            Projects[(projects)]
            Sprints[(sprints)]
            Issues[(issues \n ltree, JSONB)]
        end

        subgraph Schema_Audit [Schema: audit]
            History[(issue_history \n BRIN Indexed)]
        end

        RLS --"tenant_id match"--> Schema_Core
        RLS --"tenant_id match"--> Schema_Identity
        
        Issues -->|AFTER INSERT/UPDATE/DELETE \n Trigger SECURITY DEFINER| History
    end

    classDef database fill:#f9f9f9,stroke:#333,stroke-width:2px;
    classDef security fill:#ffe6e6,stroke:#ff0000,stroke-width:2px;
    class RLS security;
    class Schema_Identity,Schema_Core,Schema_Audit database;

Loading

🗄️ Database Schema

Entity-Relationship (ER) Diagram

erDiagram
   TENANTS ||--o{ TENANT_MEMBERSHIPS : has
   USERS ||--o{ TENANT_MEMBERSHIPS : belongs_to
   TENANTS ||--o{ PROJECTS : isolates
   TENANTS ||--o{ ISSUES : isolates
   PROJECTS ||--o{ SPRINTS : contains
   PROJECTS ||--o{ CUSTOM_FIELD_DEFINITIONS : defines
   PROJECTS ||--o{ ISSUES : owns
   SPRINTS ||--o{ ISSUES : groups
   ISSUES ||--o{ ISSUE_HISTORY : triggers

   TENANTS {
       uuid tenant_id PK
       varchar name
       varchar subdomain
       varchar status
   }

   USERS {
       uuid user_id PK
       varchar email
       varchar password_hash
   }

   TENANT_MEMBERSHIPS {
       uuid membership_id PK
       uuid tenant_id FK
       uuid user_id FK
       varchar role
   }

   PROJECTS {
       uuid project_id PK
       uuid tenant_id FK
       varchar name
       varchar project_key
   }

   ISSUES {
       uuid issue_id PK
       uuid tenant_id FK
       uuid project_id FK
       varchar issue_key
       varchar issue_type
       ltree path "Hierarchy (Epic > Task)"
       jsonb custom_fields "Dynamic attributes (GIN Index)"
   }

   ISSUE_HISTORY {
       uuid history_id PK
       uuid issue_id FK
       uuid actor_user_id
       varchar action
       jsonb old_data
       jsonb new_data
   }

Loading

🛠️ Tech Stack

• Database Engine: PostgreSQL 15+
• Extensions: uuid-ossp, ltree, pgcrypto
• Simulation Layer: Node.js (pg client)

⚙️ Installation & Execution

1. Clone the repository:

   git clone [https://github.com/yourusername/enterprise-saas-db.git](https://github.com/yourusername/enterprise-saas-db.git)

Database Setup: Ensure PostgreSQL is running locally.

Execute Migrations: Execute the SQL files in sequential order using psql:

psql -U postgres -d saas_tracker -f 01-schemas-and-roles/01_init_db.sql
# ... run remaining files sequentially

Run the Simulation:

cd backend-simulation
npm install
node index.js

🔐 Security Proof

The included backend simulation proves RLS effectiveness by executing identical SELECT * FROM core.issues queries under different tenant session contexts, successfully returning strictly isolated data.