# Phase 2: Multi-Tenant Systeemarchitectuur — HSEQ Intelligence Dashboard

| Veld        | Waarde                                        |
|-------------|-----------------------------------------------|
| **Project** | 2026-hseq-saas-multitenant                    |
| **Type**    | Systeemarchitectuur Ontwerp                   |
| **Auteur**  | Software Architect Agent                      |
| **Versie**  | 1.0                                           |
| **Datum**   | 2026-05-26                                    |
| **Status**  | Design — geen codewijzigingen                 |

---

## 1. Samenvatting

Dit document definieert de multi-tenant systeemarchitectuur voor het HSEQ Intelligence Dashboard. Het ontwerp is gebaseerd op de Phase 1 reconnaissance [1] en database-analyse [2], en adresseert de 8 geïdentificeerde gaps (G1–G8). De kernbeslissingen zijn:

1. **Database migratie**: SQLite → PostgreSQL met Row-Level Security (RLS) [3]
2. **Tenant-isolatie**: Gedeelde database, gedeeld schema, `company_id` FK + RLS [4]
3. **Module toggle**: Database-gestuurde feature flags per tenant
4. **Auth & autorisatie**: Session-based auth uitgebreid met tenant-context en RBAC [5]

> **Dit is een design-document. Er worden geen codewijzigingen voorgesteld of uitgevoerd.**

---

## 2. Architecturele Beslissingen

### 2.1 Beslissingsmatrix

| #   | Beslissing                                         | Keuze                                      | Rationale                                                                 |
|-----|----------------------------------------------------|--------------------------------------------|---------------------------------------------------------------------------|
| ADR-1 | Database-type                                    | **PostgreSQL 16+**                         | RLS native, JSONB, connection pooling, productie-grade concurrency [3]   |
| ADR-2 | Tenant-isolatiestrategie                         | **Shared DB, shared schema, `company_id` FK + RLS** | Beste balans isolatie/beheer; BRZO-data vereist robuuste scheiding [4]  |
| ADR-3 | ORM                                               | **SQLAlchemy 2.x** (behouden)              | Huidige stack gebruikt waarschijnlijk al SQLAlchemy; migratie-pad aanwezig |
| ADR-4 | Authenticatie                                     | **Session-based** (behouden, uitbreiden)   | Huidige Flask-sessies; geen JWT-complexiteit nodig voor SaaS-toepassing  |
| ADR-5 | Module toggle                                     | **Database feature flags**                 | Centraal beheerbaar, runtime configureerbaar, geen redeploy nodig        |
| ADR-6 | API-versieing                                     | **URL-prefix `/api/v2/`**                  | Multi-tenant endpoints naast legacy; geleidelijke migratie               |

---

## 3. Tenant-Isolatie Strategie

### 3.1 Drie-Lagen Model

```
┌─────────────────────────────────────────────────────────────┐
│                    LAYER 1: APPLICATION                      │
│  Tenant-context middleware resolvet company_id per request   │
│  Alle queries automatisch gefilterd via TenantMixin          │
├─────────────────────────────────────────────────────────────┤
│                    LAYER 2: DATABASE (RLS)                   │
│  PostgreSQL Row-Level Security policies per tenant           │
│  Defensieve laag — bij applicatiefout geen data-leak         │
├─────────────────────────────────────────────────────────────┤
│                    LAYER 3: API                               │
│  Tenant-aware endpoints met session-context                  │
│  Rate limiting en quota per tenant                           │
└─────────────────────────────────────────────────────────────┘
```

### 3.2 Layer 1 — Application-Level Isolation

#### 3.2.1 Tenant-Context Middleware

Request flow:

```
HTTP Request
  → [Session Lookup] → session['company_id']
  → [Tenant Resolution Middleware]
      ├── Haalt company_id uit session
      ├── Valideert dat company actief is
      ├── Slaat op in Flask g.tenant_id
      └── Slaat tenant-config op in g.tenant_config (modules, branding)
  → [DB Session] → SET app.current_tenant = g.tenant_id
  → [Route Handler] → queries automatisch gefilterd
```

**Middleware principe (pseudo-code):**

```python
@app.before_request
def resolve_tenant():
    company_id = session.get('company_id')
    if not company_id:
        return redirect(url_for('auth.login'))

    company = db.session.query(Company).filter_by(
        id=company_id, active=True
    ).first()
    if not company:
        session.clear()
        return redirect(url_for('auth.login'))

    g.tenant_id = company_id
    g.tenant_config = company.config  # JSONB: modules, branding, settings
    db.session.execute(text("SET app.current_tenant = :cid"), {"cid": company_id})
```

#### 3.2.2 TenantMixin — Automatische Query Filtering

```python
class TenantMixin:
    """Mixin die company_id toevoegt aan tenant-scoped modellen"""
    company_id = Column(Integer, ForeignKey('companies.id'), nullable=False, index=True)

@event.listens_for(db.session, 'before_flush')
def auto_set_tenant(session, flush_context, instances):
    for obj in session.new:
        if isinstance(obj, TenantMixin) and hasattr(g, 'tenant_id'):
            if obj.company_id is None:
                obj.company_id = g.tenant_id
```

Alle queries op TenantMixin-modellen filteren automatisch op `company_id == g.tenant_id`.

### 3.3 Layer 2 — Database-Level Isolation (PostgreSQL RLS)

#### 3.3.1 RLS Policy Design

RLS biedt een defensieve tweede laag. Bij een applicatiefout (vergeten filter) blokkeert PostgreSQL toegang tot andere tenants.

```sql
-- Per tenant-scoped tabel:
ALTER TABLE compliance_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE incidents ENABLE ROW LEVEL SECURITY;
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;
ALTER TABLE moc_requests ENABLE ROW LEVEL SECURITY;
ALTER TABLE ptw_permits ENABLE ROW LEVEL SECURITY;
ALTER TABLE environment_metrics ENABLE ROW LEVEL SECURITY;
-- ... alle P0/P1 tabellen

-- Policy (één patroon, herhaal per tabel):
CREATE POLICY tenant_isolation ON compliance_items
    USING (company_id = current_setting('app.current_tenant')::INTEGER);
```

#### 3.3.2 RLS Matrix

| Categorie          | Tabellen                                                   | RLS Policy       | Rationale                |
|--------------------|------------------------------------------------------------|------------------|--------------------------|
| **Tenant-scoped (P0)** | compliance_items, incidents, employees, contractors, certifications, competencies, moc_requests, ptw_permits, environment_metrics, compliance_deadlines | `USING (company_id = current_tenant)` | Bedrijfsspecifieke data [2] |
| **Tenant-scoped (P1)** | risk_scenarios, risk_controls, rie_sections, vbs_elements, brzo_safety_reports, brzo_mapp, brzo_inspections, agent_tasks | `USING (company_id = current_tenant)` | Semi-bedrijfsdata [2] |
| **Tenant-config**  | intelligence_alerts, intelligence_actions                  | `USING (company_id = current_tenant)` | Alerts per tenant [2]    |
| **Global**         | scraper_sources, scraper_runs, scraped_items               | SELECT vrijgegeven | Wetgeving is landelijk [1] |
| **System**         | companies, users, tenant_modules, module_catalog          | Beperkte policies | Beheer-tabellen          |

#### 3.3.3 PostgreSQL Role-structuur

```sql
CREATE ROLE app_admin;   -- migrations, schema-wijzigingen (RLS bypass)
CREATE ROLE app_user;    -- applicatie-connecties (RLS van toepassing)

-- PgBouncer: transaction-mode pooling
-- SET app.current_tenant geldt per transactie
-- Na COMMIT/ROLLBACK wordt context gereset
```

### 3.4 Layer 3 — API-Level Isolation

#### 3.4.1 Endpoint Structuur

```
Huidig (single-tenant):         Nieuw (multi-tenant):
──────────────────────          ────────────────────
/api/auth/login                 /api/v2/auth/login
/api/auth/logout                /api/v2/auth/logout
/api/compliance                 /api/v2/compliance
/api/incidents/dashboard        /api/v2/incidents/dashboard
/api/training/dashboard         /api/v2/training/dashboard
/api/scraper/status             /api/v2/scraper/status         (global)
/api/intelligence/v2/alerts     /api/v2/intelligence/alerts
/api/knowledge/chat             /api/v2/knowledge/chat
/api/notifications              /api/v2/notifications
```

Tenant-context wordt via session doorgegeven, niet via URL-segment. Dit voorkomt tenant-spoofing via URL-manipulatie.

#### 3.4.2 API-Response Enrichment

```json
{
  "tenant": {
    "company_id": 1,
    "company_name": "JvG Consultancy",
    "modules": ["compliance", "incidents", "training"]
  },
  "data": { },
  "pagination": { "page": 1, "per_page": 25, "total": 49 }
}
```

#### 3.4.3 Rate Limiting per Tenant

| Plan         | Requests/min | Data-opslag | Concurrent users |
|--------------|-------------|-------------|------------------|
| Starter      | 60          | 1 GB        | 5                |
| Professional | 200         | 10 GB       | 25               |
| Enterprise   | Onbeperkt   | Onbeperkt   | Onbeperkt        |

Implementatie via Flask-Limiter met `key_func` op `g.tenant_id`.

---

## 4. Module Toggle Systeem

### 4.1 Datamodel

```sql
-- Module catalogus (systeem-breed, niet per tenant)
CREATE TABLE module_catalog (
    id          SERIAL PRIMARY KEY,
    code        VARCHAR(50) UNIQUE NOT NULL,
    name        VARCHAR(100) NOT NULL,
    description TEXT,
    category    VARCHAR(50) NOT NULL,     -- 'core', 'advanced', 'premium'
    version     VARCHAR(20) DEFAULT '1.0',
    created_at  TIMESTAMP DEFAULT NOW()
);

-- Tenant-module koppeling
CREATE TABLE tenant_modules (
    id          SERIAL PRIMARY KEY,
    company_id  INTEGER NOT NULL REFERENCES companies(id),
    module_code VARCHAR(50) NOT NULL REFERENCES module_catalog(code),
    enabled     BOOLEAN DEFAULT FALSE,
    config      JSONB DEFAULT '{}',       -- Module-specifieke configuratie
    expires_at  TIMESTAMP,                -- Licentie-verloop
    created_at  TIMESTAMP DEFAULT NOW(),
    updated_at  TIMESTAMP DEFAULT NOW(),
    UNIQUE(company_id, module_code)
);
```

### 4.2 Moduledefinitie

| Code                    | Naam                        | Categorie  | Standaard  |
|-------------------------|-----------------------------|------------|------------|
| `dashboard`             | Dashboard & KPI's           | core       | ✅ Aan      |
| `compliance`            | Compliance Management       | core       | ✅ Aan      |
| `incidents`             | Incident Management         | core       | ✅ Aan      |
| `training`              | Training & Certificering    | core       | ✅ Aan      |
| `reports`               | Rapportage Engine           | core       | ✅ Aan      |
| `rie_hazop`             | RI&E / HAZOP                | advanced   | ⬜ Uit      |
| `moc_ptw`               | MoC & Werkvergunningen      | advanced   | ⬜ Uit      |
| `risk_management`       | Risicobeheer                | advanced   | ⬜ Uit      |
| `environment`           | Milieumetingen              | advanced   | ⬜ Uit      |
| `vbs_elements`          | VBS 7 Elementen             | advanced   | ⬜ Uit      |
| `quickscan`             | Quick-Scan Tools            | advanced   | ⬜ Uit      |
| `intelligence`          | Intelligence & Alerts        | premium    | ⬜ Uit      |
| `scraper`               | Wetgeving Scraper           | premium    | ⬜ Uit      |
| `knowledge_ai`          | AI Kennisbank               | premium    | ⬜ Uit      |
| `brzo_seveso`           | BRZO / Seveso Module        | premium    | ⬜ Uit      |
| `lms`                   | LMS Academy                 | premium    | ⬜ Uit      |
| `agents`                | AI Agent Launchpad          | premium    | ⬜ Uit      |

### 4.3 Backend Module Filtering

#### 4.3.1 Module Decorator

```python
def require_module(module_code: str):
    """Decorator die module-toegang controleert per tenant"""
    def decorator(f):
        @wraps(f)
        def wrapper(*args, **kwargs):
            if not is_module_enabled(g.tenant_id, module_code):
                return jsonify({
                    "error": "Module not available",
                    "module": module_code,
                    "message": "Contact your administrator"
                }), 403
            return f(*args, **kwargs)
        return wrapper
    return decorator

# Gebruik:
@app.route('/api/v2/incidents/dashboard')
@require_tenant
@require_module('incidents')
def incidents_dashboard():
    ...
```

#### 4.3.2 Module Cache

```python
# Tenant-config gecached (TTL 5 minuten)
def is_module_enabled(company_id: int, module_code: str) -> bool:
    config = get_tenant_config(company_id)
    module = config.get('modules', {}).get(module_code)
    if not module or not module.get('enabled'):
        return False
    if module.get('expires_at') and module['expires_at'] < datetime.utcnow():
        return False
    return True
```

### 4.4 Frontend Dynamic Routing

#### 4.4.1 Sidebar Generatie

De sidebar wordt dynamisch opgebouwd op basis van actieve modules:

```
Flow:
1. Login → POST /api/v2/auth/login
2. Response bevat: user + tenant + modules + navigation
3. Frontend filtert sidebar items op enabled modules
4. Frontend registreert alleen routes voor actieve modules
```

**Login-response:**

```json
{
  "user": { "id": 1, "email": "admin@jvg.nl", "role": "admin" },
  "tenant": {
    "company_id": 1,
    "company_name": "JvG Consultancy",
    "branding": { "primary_color": "#2563eb", "logo_url": "/static/tenants/1/logo.png" }
  },
  "modules": {
    "dashboard": { "enabled": true },
    "compliance": { "enabled": true },
    "incidents": { "enabled": true },
    "intelligence": { "enabled": true }
  },
  "navigation": [
    { "section": "overview", "items": ["dashboard", "intelligence", "scraper"] },
    { "section": "vbs", "items": ["compliance", "incidents", "training"] }
  ]
}
```

#### 4.4.2 Route Guard

```javascript
// Client-side route protectie
router.beforeEach((to, from, next) => {
    const module = to.meta?.module;
    if (module && !appState.modules[module]?.enabled) {
        next('/dashboard');
    } else {
        next();
    }
});
```

---

## 5. Authenticatie & User Management

### 5.1 Datamodel

```sql
-- Bedrijven (tenants)
CREATE TABLE companies (
    id                SERIAL PRIMARY KEY,
    name              VARCHAR(200) NOT NULL,
    slug              VARCHAR(100) UNIQUE NOT NULL,
    domain            VARCHAR(200),
    plan              VARCHAR(50) DEFAULT 'starter',
    active            BOOLEAN DEFAULT TRUE,
    branding          JSONB DEFAULT '{}',
    settings          JSONB DEFAULT '{}',
    max_users         INTEGER DEFAULT 5,
    storage_quota_mb  INTEGER DEFAULT 1024,
    created_at        TIMESTAMP DEFAULT NOW(),
    updated_at        TIMESTAMP DEFAULT NOW()
);

-- Gebruikers
CREATE TABLE users (
    id              SERIAL PRIMARY KEY,
    company_id      INTEGER NOT NULL REFERENCES companies(id),
    email           VARCHAR(255) NOT NULL,
    password_hash   VARCHAR(255) NOT NULL,
    first_name      VARCHAR(100),
    last_name       VARCHAR(100),
    role            VARCHAR(50) NOT NULL DEFAULT 'user',
    active          BOOLEAN DEFAULT TRUE,
    last_login_at   TIMESTAMP,
    created_at      TIMESTAMP DEFAULT NOW(),
    updated_at      TIMESTAMP DEFAULT NOW(),
    UNIQUE(company_id, email)
);

-- Rollen per tenant
CREATE TABLE roles (
    id          SERIAL PRIMARY KEY,
    company_id  INTEGER NOT NULL REFERENCES companies(id),
    name        VARCHAR(50) NOT NULL,
    permissions JSONB NOT NULL DEFAULT '[]',
    created_at  TIMESTAMP DEFAULT NOW(),
    UNIQUE(company_id, name)
);

-- Pre-gedefinieerde rollen per tenant
-- Bij aanmaken nieuw bedrijf worden deze automatisch gegenereerd:
--   - owner:     Volledige toegang + user management + billing
--   - admin:     Volledige module-toegang, geen billing
--   - manager:   CRUD op alle modules, geen admin
--   - user:      Read + beperkte write per module
--   - viewer:    Alleen lezen
```

### 5.2 User → Company → Role Mapping

```
User (id=1, email=admin@jvg.nl)
  └── company_id = 1 (JvG Consultancy)
       └── role = "admin"
            └── permissions = [
                 "compliance:read", "compliance:write",
                 "incidents:read", "incidents:write", "incidents:delete",
                 "training:read", "training:write",
                 "admin:users", "admin:settings"
               ]
```

### 5.3 Session-Based Auth (Multi-Tenant)

#### 5.3.1 Login Flow

```
1. POST /api/v2/auth/login
   Body: { "email": "admin@jvg.nl", "password": "..." }

2. Server zoekt user op met email
   → JOIN companies ON users.company_id = companies.id
   → Controleert company.active = TRUE
   → Verifieert wachtwoord (bcrypt/argon2)

3. Session wordt gevuld:
   session['user_id'] = user.id
   session['company_id'] = user.company_id
   session['role'] = user.role
   session['permissions'] = user.role.permissions

4. Response bevat user + tenant + modules (zie §4.4.1)
```

#### 5.3.2 Session Structuur

```python
# Flask session data na login:
{
    'user_id': 1,
    'company_id': 1,
    'role': 'admin',
    'permissions': ['compliance:read', 'compliance:write', ...],
    'login_at': '2026-05-26T16:45:00Z'
}
```

#### 5.3.3 Permission Decorator

```python
def require_permission(permission: str):
    def decorator(f):
        @wraps(f)
        def wrapper(*args, **kwargs):
            if permission not in session.get('permissions', []):
                return jsonify({"error": "Forbidden"}), 403
            return f(*args, **kwargs)
        return wrapper
    return decorator

# Gebruik:
@app.route('/api/v2/incidents', methods=['POST'])
@require_tenant
@require_module('incidents')
@require_permission('incidents:write')
def create_incident():
    ...
```

### 5.4 Admin User Management

| Endpoint                                  | Methode | Permission       | Beschrijving                    |
|-------------------------------------------|---------|------------------|---------------------------------|
| `/api/v2/admin/users`                     | GET     | `admin:users`    | Lijst gebruikers in tenant      |
| `/api/v2/admin/users`                     | POST    | `admin:users`    | Nieuwe gebruiker aanmaken       |
| `/api/v2/admin/users/{id}`                | PUT     | `admin:users`    | Gebruiker wijzigen              |
| `/api/v2/admin/users/{id}`                | DELETE  | `admin:users`    | Gebruiker deactiveren           |
| `/api/v2/admin/roles`                     | GET     | `admin:roles`    | Rollen ophalen                  |
| `/api/v2/admin/modules`                   | GET     | `admin:modules`  | Module-status ophalen           |
| `/api/v2/admin/modules/{code}`            | PUT     | `admin:modules`  | Module aan/uit zetten           |

---

## 6. SQLite → PostgreSQL Migratie

### 6.1 Migratie Fases

| Fase | Actie                                                         | Risico   | Afhankelijkheid |
|------|---------------------------------------------------------------|----------|-----------------|
| M1   | PostgreSQL instantie inrichten (connection string)            | Laag     | Infra           |
| M2   | Alembic migration: schema aanmaken op PostgreSQL              | Medium   | M1              |
| M3   | `company_id` kolommen toevoegen aan alle tenant-scoped tabellen | Hoog   | M2              |
| M4   | Default tenant (JvG Consultancy) aanmaken + backfill `company_id = 1` | Hoog | M3          |
| M5   | RLS policies inschakelen                                     | Laag     | M4              |
| M6   | Data-migratie: SQLite → PostgreSQL (bestaande data)          | Medium   | M5              |
| M7   | Applicatie omschakelen naar PostgreSQL DSN                   | Laag     | M6              |
| M8   | Dual-run periode: validate data-integriteit                  | Medium   | M7              |
| M9   | Cutover: SQLite naar read-only archive                       | Laag     | M8              |

### 6.2 Schema-Migratie Strategie

```python
# Alembic migration pseudo-structuur:
# migration_001: Create PostgreSQL schema (alle tabellen)
# migration_002: Add company_id to tenant-scoped tables
# migration_003: Create default company (id=1, JvG Consultancy)
# migration_004: Backfill company_id = 1 for all existing rows
# migration_005: Enable RLS policies
# migration_006: Create module_catalog + tenant_modules
# migration_007: Seed default modules + enable core for company 1
```

---

## 7. Doelarchitectuur — Overzichtsdiagram

```
┌─────────────────────────────────────────────────────────────────┐
│                      Cloudflare CDN                              │
│                  (TLS 1.3, HTTP/2, WAF)                          │
└──────────────────────────┬──────────────────────────────────────┘
                           │
┌──────────────────────────▼──────────────────────────────────────┐
│                   Flask Application (Python)                      │
│                                                                   │
│  ┌──────────────────────────────────────────────────────────┐   │
│  │  Tenant Resolution Middleware                             │   │
│  │  • Session → company_id → g.tenant_id                    │   │
│  │  • Validates tenant active status                        │   │
│  │  • Loads tenant config (modules, branding)               │   │
│  │  • SET app.current_tenant on PostgreSQL                   │   │
│  └──────────────────────────────────────────────────────────┘   │
│                           │                                      │
│  ┌────────────────────────▼──────────────────────────────────┐   │
│  │  API Layer (/api/v2/)                                     │   │
│  │  ┌────────────┐ ┌─────────────┐ ┌──────────────┐         │   │
│  │  │ Auth       │ │ Modules     │ │ Admin        │         │   │
│  │  │ login      │ │ compliance  │ │ users        │         │   │
│  │  │ logout     │ │ incidents   │ │ roles        │         │   │
│  │  │ session    │ │ training    │ │ modules      │         │   │
│  │  └────────────┘ │ intelligence│ │ billing      │         │   │
│  │                 │ scraper     │ │ settings     │         │   │
│  │  Decorators:    │ knowledge   │ └──────────────┘         │   │
│  │  @require_tenant│ moc/ptw     │                           │   │
│  │  @require_module│ risk/rie    │                           │   │
│  │  @require_perm  │ environment │                           │   │
│  │                 │ reports     │                           │   │
│  │                 └─────────────┘                           │   │
│  └──────────────────────────────────────────────────────────┘   │
│                           │                                      │
│  ┌────────────────────────▼──────────────────────────────────┐   │
│  │  Business Logic Layer                                     │   │
│  │  • TenantMixin auto-filtering                             │   │
│  │  • Module toggle checks (cached)                          │   │
│  │  • Permission validation                                  │   │
│  │  • AI Modules (RAG chat, gap-analyse, root-cause)         │   │
│  │  • Scraper Engine (global, tenant-agnostic)               │   │
│  └──────────────────────────────────────────────────────────┘   │
│                           │                                      │
│  ┌────────────────────────▼──────────────────────────────────┐   │
│  │  SQLAlchemy 2.x ORM                                      │   │
│  │  • TenantQuery (auto company_id filter)                   │   │
│  │  • TenantMixin (company_id kolom op tenant-scoped models) │   │
│  │  • Alembic migrations                                     │   │
│  └──────────────────────────────────────────────────────────┘   │
│                                                                   │
└──────────────────────────┬──────────────────────────────────────┘
                           │
┌──────────────────────────▼──────────────────────────────────────┐
│               PostgreSQL 16+ (Shared Database)                    │
│                                                                   │
│  ┌─────────────┐ ┌──────────────┐ ┌────────────────────────┐    │
│  │ Global Data │ │ Tenant Data  │ │ System Tables          │    │
│  │             │ │              │ │                        │    │
│  │ scraper_    │ │ compliance_  │ │ companies              │    │
│  │ sources     │ │ items        │ │ users                  │    │
│  │ scraper_    │ │ incidents    │ │ roles                  │    │
│  │ runs        │ │ employees    │ │ module_catalog         │    │
│  │ scraped_    │ │ contractors  │ │ tenant_modules         │    │
│  │ items       │ │ certifications│                        │    │
│  │             │ │ moc_requests │ │                        │    │
│  │ (geen RLS)  │ │ ptw_permits  │ │ (beperkte RLS)        │    │
│  │             │ │ risk_*       │ │                        │    │
│  │             │ │ training_*   │ │                        │    │
│  │             │ │ environment_*│ │                        │    │
│  │             │ │              │ │                        │    │
│  │             │ │ (RLS:       │ │                        │    │
│  │             │ │  company_id │ │                        │    │
│  │             │ │  filter)    │ │                        │    │
│  └─────────────┘ └──────────────┘ └────────────────────────┘    │
│                                                                   │
│  Row-Level Security: app.current_tenant session variable         │
│  PgBouncer: transaction-mode pooling                             │
└──────────────────────────────────────────────────────────────────┘
```

---

## 8. Tabellen Overzicht — Multi-Tenant Schema

### 8.1 Nieuwe Tabellen

| Tabel               | Doel                                           | RLS         |
|---------------------|-------------------------------------------------|-------------|
| `companies`         | Tenant-registratie                             | Beperkt     |
| `roles`             | RBAC-rollen per tenant                         | Per tenant  |
| `module_catalog`    | Systeem-brede module-definities                 | Geen        |
| `tenant_modules`    | Module aan/uit per tenant                        | Per tenant  |

### 8.2 Bestaande Tabellen — Mutaties

Alle tenant-scoped tabellen uit [2] krijgen een `company_id` kolom (INTEGER FK → companies.id).

| Tabel                    | Mutatie                                        | RLS     |
|--------------------------|-------------------------------------------------|---------|
| `compliance_items`       | + company_id, index                            | Ja      |
| `incidents`              | + company_id, index                            | Ja      |
| `employees`              | + company_id, index                            | Ja      |
| `contractors`            | + company_id, index                            | Ja      |
| `certifications`         | + company_id, index                            | Ja      |
| `competencies`           | + company_id, index                            | Ja      |
| `training_programs`      | + company_id, index                            | Ja      |
| `training_calendar`      | + company_id, index                            | Ja      |
| `moc_requests`           | + company_id, index                            | Ja      |
| `ptw_permits`            | + company_id, index                            | Ja      |
| `environment_metrics`    | + company_id, index                            | Ja      |
| `compliance_deadlines`   | + company_id, index                            | Ja      |
| `risk_scenarios`         | + company_id, index                            | Ja      |
| `risk_controls`          | + company_id, index (via scenario)             | Ja      |
| `rie_sections`           | + company_id, index                            | Ja      |
| `vbs_elements`           | + company_id, index                            | Ja      |
| `agent_tasks`            | + company_id, index                            | Ja      |
| `notifications`          | + company_id, index                            | Ja      |
| `intelligence_alerts`    | + company_id, index                            | Ja      |
| `intelligence_actions`   | + company_id, index                            | Ja      |
| `users`                  | company_id bestaat reeds (uit te breiden)       | Ja      |
| `scraper_sources`        | Geen mutatie — global                          | Nee      |
| `scraper_runs`           | Geen mutatie — global                          | Nee      |
| `scraped_items`          | Geen mutatie — global                          | Nee      |

---

## 9. Beveiligingsoverwegingen

### 9.1 Dreigingsmodel

| Dreiging                        | Mitigatie                                            | Laag     |
|---------------------------------|------------------------------------------------------|----------|
| Tenant data-leak (applicatiefout)| PostgreSQL RLS als defensieve laag                    | DB       |
| Tenant data-leak (query-fout)   | TenantMixin auto-filtering                           | App      |
| Tenant spoofing (URL)           | Session-based tenant, geen URL-segment               | App      |
| Privilege escalation            | RBAC met permissie-decorators                        | App      |
| Module bypass                   | @require_module decorator + 403 response             | App      |
| Brute-force login               | Rate limiting per IP + account lockout               | Infra    |
| Session hijacking               | HTTPS-only, Secure/HttpOnly cookies, session timeout | Infra    |

### 9.2 BRZO/Seveso Specifieke Eisen

Gezien de aard van de data (HF-stoffen, explosiegevaar, ongevalldata) gelden extra eisen [2]:

- **RLS is verplicht**, niet optioneel — voorkomt cross-tenant data-leaks bij BRZO-gevoelige data
- **Audit logging**: Alle mutaties op incidents, moc_requests, ptw_permits worden gelogd met tenant-context
- **Data-retentie**: Per tenant configureerbaar (BRZO vereist minimaal 5 jaar bewaartermijn)
- **Backups**: Tenant-isolatie in backups — per-tenant restore mogelijkheid

---

## 10. Implementatie Volgorde

| Stap | Actie                                              | Duur (schatting) | Afhankelijkheid |
|------|-----------------------------------------------------|-------------------|-----------------|
| 1    | PostgreSQL inrichten + schema migratie              | 1 dag             | Infra           |
| 2    | companies/users/roles tabellen + RLS                | 1 dag             | Stap 1          |
| 3    | company_id toevoegen aan bestaande tabellen         | 0.5 dag           | Stap 2          |
| 4    | Tenant resolution middleware                        | 0.5 dag           | Stap 3          |
| 5    | TenantMixin + auto-query filtering                  | 0.5 dag           | Stap 4          |
| 6    | module_catalog + tenant_modules                     | 0.5 dag           | Stap 2          |
| 7    | @require_module decorator + API endpoints           | 1 dag             | Stap 5, 6       |
| 8    | Login flow uitbreiden (session + tenant context)    | 0.5 dag           | Stap 4          |
| 9    | RBAC decorators (@require_permission)               | 0.5 dag           | Stap 8          |
| 10   | Frontend dynamic routing + module filtering         | 1 dag             | Stap 7          |
| 11   | Admin panel: user/role/module beheer                | 1 dag             | Stap 9          |
| 12   | Data-migratie SQLite → PostgreSQL                   | 0.5 dag           | Stap 1-5        |
| 13   | Testing + staging validatie                         | 1 dag             | Stap 12         |
|      | **Totaal**                                          | **~9 dagen**      |                 |

---

## 11. Bronverwijzingen

| #  | Bron                                                                                                      |
|----|-----------------------------------------------------------------------------------------------------------|
| 1  | Phase 1 Reconnaissance Report — `phase1_recon_report_v1.0.md` (2026-05-26)                               |
| 2  | Phase 1 Database Analyse — `phase1_database_analysis_v1.0.md` (2026-05-26)                               |
| 3  | PostgreSQL RLS documentatie — row-level security als multi-tenant isolatiemechanisme                      |
| 4  | Multi-tenancy isolatiestrategieën: shared DB + RLS vs separate schema vs separate DB (SaaS best-practice) |
| 5  | Flask session-based authentication patterns —安全 session management met tenant-context                    |
| 6  | BRZO/Seveso richtlijnen — data-gevoeligheid van chemische en veiligheidsdata                              |

---

## 12. Verify — TierVerify Log

| Controle-item                                | Resultaat | Notitie                                          |
|----------------------------------------------|-----------|--------------------------------------------------|
| Design-only, geen codewijzigingen            | ✅ PASS   | Document bevat geen uitvoerbare code-wijzigingen |
| Gebaseerd op Phase 1 bevindingen             | ✅ PASS   | Alle gaps (G1-G8) worden addressed              |
| RLS als defensieve laag gedocumenteerd       | ✅ PASS   | Dubbele isolatie: applicatie + database          |
| BRZO/Seveso gevoeligheid meegenomen          | ✅ PASS   | Audit logging, data-retentie, RLS verplicht      |
| SQLite → PostgreSQL migratiepad gedefinieerd | ✅ PASS   | 9 fasen, risico's per fase                       |
| Module toggle architectuur volledig          | ✅ PASS   | Backend decorator + frontend routing + datamodel |
| Auth/RBAC multi-tenant compatible            | ✅ PASS   | Session-based + permission decorators            |
| Bronverwijzingen aanwezig                    | ✅ PASS   | 6 bronnen gedocumenteerd                         |
| Document-header aanwezig                     | ✅ PASS   | Project, Type, Auteur, Versie, Datum, Status     |
| Output in deliverables/ met versietag        | ✅ PASS   | `phase2_system_architecture_v1.0.md`             |

---

*Einde Phase 2 Systeemarchitectuur — gereed voor volgende instructies.*