ADR-009: CODITECT Multi-Tenant Architecture

Status

ACCEPTED (2026-02-03)

Context

CODITECT required a multi-tenant SaaS architecture that:

1. Scales from 10 to 10,000+ users across multiple organizations
2. Provides complete data isolation between tenants
3. Supports hierarchical organization (Org → Team → User → Project)
4. Integrates with GCP services (Cloud SQL, Cloud Run, IAP)
5. Enables cost-efficient operations with shared infrastructure

Strategic Requirements

Requirement	Priority	Implementation
Data isolation	Critical	PostgreSQL RLS + django-multitenant
Scalability	High	Citus sharding (when >1,000 users)
Compliance	High	SOC 2, GDPR, HIPAA-ready
Cost efficiency	Medium	Shared infrastructure with isolation
Time to market	High	Django extension (not microservices)

Decision

Architecture: Django Multi-Tenant Extension

Chosen Approach: Monolith-first Django app extension with PostgreSQL Row-Level Security.

┌─────────────────────────────────────────────────────────────────┐
│                    MULTI-TENANT HIERARCHY                        │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│  Platform (AZ1.AI)                                              │
│  └── Tenant (Customer Organization)                             │
│      ├── Teams                                                   │
│      │   └── Team Members (users with roles)                    │
│      ├── Projects                                                │
│      │   └── Project Members (team/user access)                 │
│      └── Users                                                   │
│          └── Sessions → Tasks → Messages                        │
│                                                                  │
├─────────────────────────────────────────────────────────────────┤
│                    ISOLATION MODEL                               │
│                                                                  │
│  Every table includes `tenant_id` column                        │
│  PostgreSQL RLS enforces filtering                              │
│  JWT tokens include tenant claims                               │
│  API requests auto-scoped to user's organization                │
│  Cross-tenant queries prohibited at database level              │
│                                                                  │
└─────────────────────────────────────────────────────────────────┘

Technology Stack

Component	Technology	Justification
Database	PostgreSQL 15 + RLS	70K+ stars, proven multi-tenancy
Multi-tenant	django-multitenant	Auto-isolates by tenant
Authentication	Firebase + Identity Platform	GCP native, SSO support (ADR-011)
API	Django REST Framework	Existing codebase, proven patterns
Future Scale	Citus extension	Clean upgrade path >1,000 users

Database Schema Design

-- Base tenant model (all tables inherit)
CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    settings JSONB DEFAULT '{}'
);

-- Organizations extend tenants
CREATE TABLE organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    name VARCHAR(255) NOT NULL,
    owner_id UUID REFERENCES users(id),
    subscription_tier VARCHAR(50) DEFAULT 'starter',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Row Level Security
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON organizations
    USING (tenant_id = current_setting('app.tenant_id')::UUID);

-- Teams within organizations
CREATE TABLE teams (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    organization_id UUID NOT NULL REFERENCES organizations(id),
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Projects scoped to teams
CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    team_id UUID REFERENCES teams(id),
    name VARCHAR(255) NOT NULL,
    github_repo_url VARCHAR(500),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

API Multi-Tenant Middleware

# Django middleware for tenant isolation
class TenantMiddleware:
    """Sets tenant context from JWT claims."""

    def __call__(self, request):
        # Extract tenant_id from JWT
        tenant_id = request.auth.get('tenant_id')

        # Set PostgreSQL session variable for RLS
        with connection.cursor() as cursor:
            cursor.execute(
                "SET app.tenant_id = %s",
                [str(tenant_id)]
            )

        # Attach to request for application use
        request.tenant_id = tenant_id

        return self.get_response(request)

Subscription Tiers

Tier	Users	Organizations	Features	Price
Starter	1-5	1	Core features	$0
Professional	6-25	1	+ Teams, Projects	$10/user
Business	26-100	3	+ SSO, Audit logs	$20/user
Enterprise	Unlimited	Unlimited	+ Custom, SLA	Custom

Integration with CODITECT Framework

Local Framework (ADR-001):

~/.coditect/                    # Framework installation
~/PROJECTS/.coditect-data/      # User data (tenant-isolated)
    ├── context-storage/
    │   ├── org.db              # Org-level knowledge (ADR-118 Tier 2)
    │   └── sessions.db         # Session data (ADR-118 Tier 3)
    └── session-logs/

Cloud Sync (ADR-044, ADR-045):

LOCAL                           CLOUD (api.coditect.ai)
┌─────────────────┐            ┌─────────────────────────┐
│ SQLite          │            │ PostgreSQL              │
│ org.db          │───sync────▶│ tenant-isolated tables  │
│ sessions.db     │            │ django-multitenant RLS  │
└─────────────────┘            └─────────────────────────┘

Consequences

Positive

1. Complete isolation - RLS ensures no cross-tenant data access
2. Simplified operations - Single database, shared infrastructure
3. Cost efficient - $2.15/user/month operational at scale
4. Time to market - 12 weeks vs 20 weeks for microservices
5. Code reuse - 60% leverage existing Django patterns
6. GCP native - Seamless Cloud SQL, IAP, IAM integration

Negative

1. Single database bottleneck - Mitigated by read replicas
2. Vendor coupling - PostgreSQL + Django specific
3. Scaling ceiling - Requires Citus at >1,000 users

Risks

Risk	Mitigation
RLS bypass bugs	Comprehensive test suite, security audit
Tenant data leak	Database-level enforcement, not app-level
Scale limits	Citus upgrade path documented
Compliance gaps	SOC 2 inherited from GCP

Implementation

Phase 1: Foundation (Weeks 1-8)

• PostgreSQL with Row-Level Security
• django-multitenant integration
• Basic organization/team/user hierarchy
• SQLite migration tooling

Scale Target: 10-100 users | Cost: $450-600/month

Phase 2: Growth (Weeks 9-16)

• Team context sync (ADR-045)
• Project-level isolation
• Read replicas for scalability
• Advanced RBAC policies

Scale Target: 100-1,000 users | Cost: $600-850/month

Phase 3: Enterprise (Weeks 17+)

• Citus sharding migration (if needed)
• SAML/OIDC SSO (ADR-011)
• Audit logging
• SLA guarantees

Scale Target: 1,000-10,000 users | Cost: $1,500-3,000/month

Related

• ADR-001: Framework Architecture
• ADR-010: Cloud Workstations Architecture
• ADR-011: Authentication Strategy
• ADR-012: Data Isolation Strategy
• ADR-044: Custom REST Sync Architecture
• ADR-045: Team/Project Context Sync
• ADR-118: Four-Tier Database Architecture
• multi-tenant/README.md: Detailed multi-tenant research

---

Track: A (Backend API) Task: F.12.2.3