# Phase 2: Multi-Tenant Database Architectuur

| Veld        | Waarde                                            |
|-------------|---------------------------------------------------|
| **Project** | 2026-hseq-saas-multitenant                        |
| **Type**    | Database Architectuur Design                      |
| **Auteur**  | Database Administrator Agent                      |
| **Versie**  | 1.0                                               |
| **Datum**   | 2026-05-26                                        |
| **Status**  | Design — geen database-wijzigingen uitgevoerd     |

---

## 1. Samenvatting

Dit document definieert de multi-tenant database architectuur voor het HSEQ SaaS platform, gebaseerd op de schema-analyse uit Fase 1 [1]. De architectuur maakt gebruik van PostgreSQL met Row-Level Security (RLS) als isolatiemechanisme, één gedeelde database met `company_id` foreign key op tenant-specifieke tabellen, en een expliciet onderscheid tussen globale en tenant-gescopeerde data. [2][3]

**Kernbeslissingen:**
- PostgreSQL als doel-database (vervanging van huidige SQLite) [4]
- Shared database, shared schema met `company_id` FK + RLS [3]
- 18 tabellen geclassificeerd: 12 tenant-specifiek, 6 globaal
- Migratiestrategie in 4 fasen (zero-downtime benadering)

---

## 2. Tenant Management Structuur

### 2.1 `companies` — Tenant Registry

De centrale tabel voor tenant-beheer. Elke rij vertegenwoordigt één bedrijf (tenant).

```sql
CREATE TABLE companies (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug            TEXT NOT NULL UNIQUE,          -- URL-safe identifier (bijv. "acme-chemie")
    name            TEXT NOT NULL,
    legal_name      TEXT,
    kvk_number      TEXT,                          -- Kamer van Koophandel nummer
    brzo_tier       TEXT CHECK (brzo_tier IN ('lower', 'upper', 'none')),
    address         JSONB,                         -- {street, city, postal_code, country}
    contact_email   TEXT NOT NULL,
    contact_phone   TEXT,
    logo_url        TEXT,
    plan            TEXT NOT NULL DEFAULT 'starter' CHECK (plan IN ('starter', 'professional', 'enterprise')),
    status          TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'churned', 'trial')),
    settings        JSONB NOT NULL DEFAULT '{}',   -- Tenant-specifieke configuratie
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_companies_slug ON companies (slug);
CREATE INDEX idx_companies_status ON companies (status);
CREATE INDEX idx_companies_plan ON companies (plan);
```

**Ontwerpbeslissingen:**
- `UUID` primary key — voorkomt orde-lek tussen tenants bij auto-increment [2]
- `slug` voor URL-routing en API-namespacing
- `brzo_tier` voor Seveso-classificatie (bepaalt module-beschikbaarheid)
- `settings` als JSONB voor flexibele per-tenant configuratie zonder schema-wijzigingen

### 2.2 `company_users` — User-Company Mapping

Koppelt gebruikers aan bedrijven. Ondersteunt multi-company gebruikers (bijv. auditors, consultants).

```sql
CREATE TABLE company_users (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    company_id      UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    role            TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'manager', 'user', 'viewer')),
    status          TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'invited', 'deactivated')),
    invited_at      TIMESTAMPTZ,
    joined_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (user_id, company_id)
);

CREATE INDEX idx_company_users_company ON company_users (company_id);
CREATE INDEX idx_company_users_user ON company_users (user_id);
CREATE INDEX idx_company_users_role ON company_users (company_id, role);
```

### 2.3 `company_modules` — Module Toggles per Tenant

Bepaalt welke HSEQ-modules beschikbaar zijn per tenant. Standaard ingeschakelde modules zijn afhankelijk van het abonnement.

```sql
CREATE TABLE company_modules (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id      UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    module_key      TEXT NOT NULL,                  -- bijv. 'compliance', 'incidents', 'rie', 'ptw', 'moc'
    enabled         BOOLEAN NOT NULL DEFAULT TRUE,
    config          JSONB DEFAULT '{}',             -- Module-specifieke configuratie
    enabled_at      TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (company_id, module_key)
);

CREATE INDEX idx_company_modules_company ON company_modules (company_id);
CREATE INDEX idx_company_modules_enabled ON company_modules (company_id, enabled);
```

**Module keys (initieel):**

| Module Key          | Beschrijving                        | Starter | Professional | Enterprise |
|---------------------|-------------------------------------|---------|--------------|------------|
| `compliance`        | Compliance-items & deadlines        | ✅      | ✅           | ✅         |
| `incidents`         | Incident-management & AI-analyse    | ✅      | ✅           | ✅         |
| `risk_assessment`   | RIE / Risicoscenario's              | ✅      | ✅           | ✅         |
| `ptw`               | Work permits (PTW)                  | ✅      | ✅           | ✅         |
| `moc`               | Management of Change                | ✅      | ✅           | ✅         |
| `training`          | Training & certificering            | ✅      | ✅           | ✅         |
| `environment`       | Milieumetingen                      | —       | ✅           | ✅         |
| `brzo`              | BRZO / Seveso module                | —       | ✅           | ✅         |
| `intelligence`      | Regulatory intelligence alerts      | —       | ✅           | ✅         |
| `agents`            | AI-agent taken                      | —       | —            | ✅         |
| `knowledge`         | RAG kennisbank                      | —       | —            | ✅         |

### 2.4 `company_permissions` — RBAC per Tenant

Rol-gebaseerd toegangsbeheer per tenant. Ondersteunt both coarse-grained (module-level) en fine-grained (actie-level) permissies.

```sql
CREATE TABLE company_permissions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    role            TEXT NOT NULL,                   -- bijv. 'admin', 'manager', 'user', 'viewer'
    resource        TEXT NOT NULL,                   -- bijv. 'incidents', 'ptw', 'compliance'
    action          TEXT NOT NULL,                   -- 'create', 'read', 'update', 'delete', 'export', 'approve'
    allowed         BOOLEAN NOT NULL DEFAULT TRUE,
    company_id      UUID REFERENCES companies(id) ON DELETE CASCADE,  -- NULL = globale default
    UNIQUE (role, resource, action, company_id)
);

CREATE INDEX idx_company_permissions_lookup ON company_permissions (role, resource, company_id);
```

**Default permissie-matrix:**

| Rol       | compliance | incidents | ptw    | moc    | rie    | environment | brzo   |
|-----------|-----------|-----------|--------|--------|--------|-------------|--------|
| `owner`   | CRUD+X    | CRUD+X    | CRUD+X | CRUD+X | CRUD+X | CRUD+X      | CRUD+X |
| `admin`   | CRUD+X    | CRUD+X    | CRUD+X | CRUD+X | CRUD+X | CRUD+X      | CRUD+X |
| `manager` | CRU       | CRU       | CRUD   | CRU    | CRU    | R           | R      |
| `user`    | CR        | CR        | CR     | CR     | R      | R           | R      |
| `viewer`  | R         | R         | R      | R      | R      | R           | R      |

*X = export, goedkeuren. R = read-only. CRUD = create/read/update/delete.*

---

## 3. Tenant-Classificatie per Tabel

### 3.1 Classificatieoverzicht

Elke tabel uit Fase 1 [1] is geclassificeerd op basis van data-eigenaarschap en scope:

| #  | Tabel                     | Classificatie    | `company_id` | Rationale                                                           |
|----|---------------------------|------------------|:------------:|---------------------------------------------------------------------|
| 1  | `compliance_items`        | 🏢 Tenant        | ✅           | Bedrijfspecifieke compliance-eisen en status                        |
| 2  | `incidents`               | 🏢 Tenant        | ✅           | Bedrijfspecifieke incidenten — BRZO/Seveso gevoelig [5]            |
| 3  | `employees`               | 🏢 Tenant        | ✅           | Persoonsgegevens — AVG/privacy-criticaal [6]                       |
| 4  | `contractors`             | 🏢 Tenant        | ✅           | Contractrelaties zijn bedrijfspecifiek                              |
| 5  | `certifications`          | 🏢 Tenant        | ✅           | Certificeringen behoren tot medewerkers van één bedrijf             |
| 6  | `risk_scenarios`          | ⚠️ Gemengd       | ✅*          | Basisrisico's globaal (CAS-nummers), bedrijfspecifieke toevoegingen |
| 7  | `risk_controls`           | 🏢 Tenant        | ✅           | Maatregelen zijn bedrijfspecifieke implementaties                   |
| 8  | `rie_sections`            | 🌐 Global        | —           | Standaard RIE-secties (ARBO-wetgeving) — gedeeld                    |
| 9  | `moc_requests`            | 🏢 Tenant        | ✅           | Wijzigingsverzoeken zijn bedrijfspecifiek                           |
| 10 | `ptw_permits`             | 🏢 Tenant        | ✅           | Vergunningen behoren tot één bedrijf                                |
| 11 | `scraper_sources`         | 🌐 Global        | —           | Brondefinities zijn platform-breed                                  |
| 12 | `scraper_runs`            | 🌐 Global        | —           | Scraping-resultaten zijn platform-breed                             |
| 13 | `scraped_items`           | 🌐 Global        | —           | Gescrapte regelgeving is gedeelde kennis                            |
| 14 | `intelligence_alerts`     | ⚠️ Gemengd       | ✅*          | Alerts gegenereerd uit globale data, maar gelezen-status per tenant  |
| 15 | `intelligence_actions`    | ⚠️ Gemengd       | ✅*          | Acties gekoppeld aan alerts, per tenant gedefinieerd                |
| 16 | `environment_metrics`     | 🏢 Tenant        | ✅           | Milieumetingen zijn bedrijfsspecifiek                               |
| 17 | `vbs_elements`            | 🌐 Global        | —           | VBS-elementen zijn wettelijk gedefinieerd                           |
| 18 | `compliance_deadlines`    | ⚠️ Gemengd       | ✅*          | Standaard deadlines globaal, bedrijfspecifieke overrides            |
| 19 | `agent_tasks`             | 🏢 Tenant        | ✅           | AI-taken zijn bedrijfspecifiek                                      |
| 20 | `brzo_safety_reports`     | 🏢 Tenant        | ✅           | BRZO-rapportages zijn bedrijfspecifiek                              |
| 21 | `brzo_mapp`               | 🏢 Tenant        | ✅           | MAPP-documenten zijn bedrijfspecifiek                               |
| 22 | `brzo_inspections`        | 🏢 Tenant        | ✅           | Inspecties behoren tot één bedrijf                                  |

*\* Gemengd: basisdata is globaal, maar bedrijfspecifieke records hebben `company_id`. Zie §4 voor detail.*

### 3.2 P0 Tabellen (Kritiek — `company_id` verplicht)

| Tabel                  | Query-patroon                              | Impact bij ontbreken `company_id`              |
|------------------------|--------------------------------------------|------------------------------------------------|
| `compliance_items`     | Dashboard per bedrijf, RAG-status          | Data-leak: bedrijf A ziet items van bedrijf B  |
| `incidents`            | Incident-register, AI-analyse, BRZO-rapportage | Kritiek: ongevaldata lekt tussen bedrijven |
| `employees`            | Medewerker-lijst, gap-analyse, certificering | AVG-overtreding: persoonsgegevens lekken    |
| `contractors`          | Aannemer-overzicht, toewijzing             | Commercieel risico: contractdata zichtbaar     |
| `certifications`       | Certificering-tracking, vervaldatum-alerts | Compliance-risico: verkeerde cert-info         |
| `moc_requests`         | MoC-workflow per bedrijf                   | Proces-verstoring: wijzigingsverzoeken gemengd |
| `ptw_permits`          | Vergunning-workflow per bedrijf            | Veiligheidsrisico: verkeerde PTW zichtbaar     |
| `environment_metrics`  | Milieu-dashboard, emissie-monitoring       | Rapportage-fout: verkeerde meetdata            |
| `risk_controls`        | Maatregelen-registry per bedrijf           | Implementatie-verwarring                        |
| `agent_tasks`          | AI-taken per bedrijf                       | Resource-leak: taken van andere tenant          |

### 3.3 P1 Tabellen (Belangrijk — `company_id` aanbevolen)

| Tabel                  | Rationale voor classificatie               |
|------------------------|--------------------------------------------|
| `risk_scenarios`       | Basis CAS-gegevens globaal, bedrijfspecifieke scenario's hebben `company_id` |
| `intelligence_alerts`  | Alert-inhoud globaal, maar `is_read` en acties per tenant |
| `intelligence_actions` | Acties per tenant, gekoppeld aan alerts    |
| `compliance_deadlines` | Standaard deadlines globaal, bedrijfspecifieke frequency/owner overrides |
| `brzo_safety_reports`  | Rapportages specifiek per BRZO-bedrijf     |
| `brzo_mapp`            | MAPP-document specifiek per bedrijf        |
| `brzo_inspections`     | Inspectieresultaten per bedrijf            |
| `rie_sections`         | Zie §4 — template is globaal, kopie kan tenant-scoped |

---

## 4. Global vs. Tenant Content Model

### 4.1 Architectuurpatroon

```
┌─────────────────────────────────────────────────────────────┐
│                    POSTGRESQL DATABASE                       │
│                                                             │
│  ┌──────────────────────────────────────────────────────┐  │
│  │                 GLOBAL DATA LAYER                     │  │
│  │                                                      │  │
│  │  scraper_sources    scraped_items    vbs_elements     │  │
│  │  scraper_runs       rie_sections     knowledge_docs   │  │
│  │  (geen company_id — platform-brede content)          │  │
│  └──────────────────────────────────────────────────────┘  │
│                          │                                  │
│                     READ-ONLY                              │
│                          │                                  │
│  ┌──────────────────────────────────────────────────────┐  │
│  │              TENANT DATA LAYER (RLS)                  │  │
│  │                                                      │  │
│  │  company_id = current_setting('app.company_id')      │  │
│  │                                                      │  │
│  │  compliance_items    incidents         employees      │  │
│  │  contractors         certifications    moc_requests   │  │
│  │  ptw_permits         risk_controls     agent_tasks    │  │
│  │  environment_metrics brzo_*            risk_scenarios │  │
│  │  intelligence_alerts intelligence_actions              │  │
│  │  compliance_deadlines                                  │  │
│  └──────────────────────────────────────────────────────┘  │
│                          │                                  │
│  ┌──────────────────────────────────────────────────────┐  │
│  │              TENANT MANAGEMENT                        │  │
│  │                                                      │  │
│  │  companies    company_users                           │  │
│  │  company_modules    company_permissions               │  │
│  └──────────────────────────────────────────────────────┘  │
└─────────────────────────────────────────────────────────────┘
```

### 4.2 Globale Data (Geen `company_id`)

Deze tabellen bevatten platform-brede content die voor alle tenants gelijk is:

| Tabel               | Inhoud                                         | Waarom globaal                                    |
|---------------------|------------------------------------------------|---------------------------------------------------|
| `scraper_sources`   | 29 wetgevingsbronnen (Arboportaal, PGS, etc.)  | Regelgeving is niet bedrijfsspecifiek              |
| `scraper_runs`      | Scraping-history (~2977 runs)                  | Platform-infrastructuur, geen bedrijfsdata         |
| `scraped_items`     | Gescrapte wetgeving-artikelen                  | Gedeelde kennisbron                                |
| `vbs_elements`      | 7 VBS-elementen (BRZO-wettelijk)              | Wetlijk kader is identiek voor alle BRZO-bedrijven |
| `rie_sections`      | RIE-secties (ARBO-standaard)                   | Standaardindeling is wetgegeven                    |
| `knowledge_documents`| RAG-index voor AI-chatbot                     | Platform-brede kennisbank (initieel)               |

**Toegangspatroon:** Read-only voor tenants. Alleen platform-admin kan muteren.

### 4.3 Tenant-Specifieke Data (`company_id` verplicht)

| Tabel               | Tenant-data reden                               |
|---------------------|-------------------------------------------------|
| `compliance_items`  | Elk bedrijf heeft eigen compliance-traject      |
| `incidents`         | Ongevallen zijn bedrijfsspecifiek (BRZO/AVG)    |
| `employees`         | Persoonsgegevens — AVG-art.5 minimalisatie [6]  |
| `contractors`       | Contractrelaties zijn commercieel gevoelig      |
| `certifications`    | VCA/HF-certificeringen per medewerker           |
| `risk_controls`     | Maatregelen verschillen per bedrijf             |
| `moc_requests`      | Wijzigingsverzoeken per bedrijfsproces          |
| `ptw_permits`       | Vergunningen per bedrijfslocatie                |
| `environment_metrics`| Emissies per emissiepunt/bedrijf               |
| `brzo_safety_reports`| BRZO-rapportage per bedrijf                   |
| `brzo_mapp`         | MAPP-document per Seveso-inrichting             |
| `brzo_inspections`  | Inspecties per inrichting                       |
| `agent_tasks`       | AI-taken per bedrijfssessie                     |

### 4.4 Gemengde Data (Hybride Model)

Voor gemengde tabellen wordt het volgende patroon toegepast:

```
┌─────────────────────────────────────────────────────┐
│  Template data (company_id = NULL)                  │
│  → Globale defaults, alle tenants kunnen kopiëren   │
│  → Read-only via app-layer                          │
├─────────────────────────────────────────────────────┤
│  Tenant overrides (company_id = UUID)               │
│  → Bedrijfspecifieke aanpassingen                   │
│  → Read/write via RLS                               │
└─────────────────────────────────────────────────────┘
```

| Tabel                  | `company_id = NULL` (Template)              | `company_id = UUID` (Tenant)                  |
|------------------------|---------------------------------------------|-----------------------------------------------|
| `risk_scenarios`       | Standaard CAS-stofdata, H-zinnen            | Bedrijfsspecifieke HAZOP-scenario's           |
| `intelligence_alerts`  | Alert gegenereerd (global content)          | `is_read`, actie-status per tenant            |
| `intelligence_actions` | —                                           | Tenant-defines acties op globale alerts       |
| `compliance_deadlines` | Wettelijke deadlines (frequentie, titel)    | Bedrijfsspecifieke owner, status, overrides   |

**Alternatief voor `intelligence_alerts`:** Overweeg een koppeltabel `company_alert_reads(company_id, alert_id, is_read, action_taken)` om de alert-inhoud globaal te houden en alleen de interactie-status per tenant op te slaan. Dit reduceert data-duplicatie.

---

## 5. Row-Level Security (RLS) Strategie

### 5.1 PostgreSQL RLS Basisconfiguratie

PostgreSQL RLS biedt database-level tenant-isolatie. Bij correcte configuratie is het onmogelijk voor een query om data van een andere tenant te benaderen, zelfs bij applicatie-bugs. [3]

```sql
-- Stap 1: Company context instellen per sessie
-- Applicatie-laag roept dit aan na authenticatie:
SET app.company_id = '550e8400-e29b-41d4-a716-446655440000';

-- Stap 2: RLS policy template (per tenant-tabel)
CREATE POLICY tenant_isolation ON compliance_items
    USING (company_id::uuid = current_setting('app.company_id')::uuid);

-- Stap 3: RLS inschakelen
ALTER TABLE compliance_items ENABLE ROW LEVEL SECURITY;

-- Stap 4: Superuser/admin uitzondering (platform-beheer)
CREATE POLICY admin_all_access ON compliance_items
    USING (current_setting('app.is_admin', TRUE) = 'true');
```

### 5.2 Helper-functie voor Tenant Context

```sql
CREATE OR REPLACE FUNCTION current_company_id() RETURNS UUID AS $$
BEGIN
    RETURN current_setting('app.company_id', TRUE)::uuid;
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;
```

### 5.3 RLS Policies per Tabel

#### Tenant-tabellen (volledige RLS)

```sql
-- compliance_items
ALTER TABLE compliance_items ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON compliance_items
    USING (company_id = current_company_id());

-- incidents
ALTER TABLE incidents ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON incidents
    USING (company_id = current_company_id());

-- employees
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON employees
    USING (company_id = current_company_id());

-- contractors
ALTER TABLE contractors ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON contractors
    USING (company_id = current_company_id());

-- certifications
ALTER TABLE certifications ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON certifications
    USING (company_id = current_company_id());

-- risk_controls
ALTER TABLE risk_controls ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON risk_controls
    USING (company_id = current_company_id());

-- moc_requests
ALTER TABLE moc_requests ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON moc_requests
    USING (company_id = current_company_id());

-- ptw_permits
ALTER TABLE ptw_permits ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON ptw_permits
    USING (company_id = current_company_id());

-- environment_metrics
ALTER TABLE environment_metrics ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON environment_metrics
    USING (company_id = current_company_id());

-- agent_tasks
ALTER TABLE agent_tasks ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON agent_tasks
    USING (company_id = current_company_id());

-- brzo_safety_reports
ALTER TABLE brzo_safety_reports ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON brzo_safety_reports
    USING (company_id = current_company_id());

-- brzo_mapp
ALTER TABLE brzo_mapp ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON brzo_mapp
    USING (company_id = current_company_id());

-- brzo_inspections
ALTER TABLE brzo_inspections ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON brzo_inspections
    USING (company_id = current_company_id());
```

#### Gemengde tabellen (conditionele RLS)

```sql
-- risk_scenarios: globale templates + eigen data
ALTER TABLE risk_scenarios ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_or_global ON risk_scenarios
    USING (company_id IS NULL OR company_id = current_company_id());

-- intelligence_alerts: globale alerts + eigen status
ALTER TABLE intelligence_alerts ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_or_global ON intelligence_alerts
    USING (company_id IS NULL OR company_id = current_company_id());

-- intelligence_actions: alleen eigen acties
ALTER TABLE intelligence_actions ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON intelligence_actions
    USING (company_id = current_company_id());

-- compliance_deadlines: globale defaults + eigen overrides
ALTER TABLE compliance_deadlines ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_or_global ON compliance_deadlines
    USING (company_id IS NULL OR company_id = current_company_id());
```

#### Globale tabellen (geen RLS — read-only voor tenants)

Globale tabellen (`scraper_sources`, `scraper_runs`, `scraped_items`, `vbs_elements`, `rie_sections`, `knowledge_documents`) krijgen geen RLS. Toegangscontrole wordt afgehandeld op applicatie-niveau: tenants hebben alleen `SELECT` rechten, platform-admin heeft `INSERT/UPDATE/DELETE`.

### 5.4 Beveiligingslagen

| Laag             | Mechanisme                    | Verantwoordelijkheid       |
|------------------|-------------------------------|----------------------------|
| **L1: Database** | RLS policies                  | PostgreSQL (laatste verdedigingslijn) |
| **L2: Applicatie** | `company_id` in queries     | SQLAlchemy ORM filters     |
| **L3: API**      | JWT-token met `company_id`    | Middleware validatie        |
| **L4: Router**   | Tenant-resolutie uit subdomain/header | Nginx/Flask routing  |

> **Principe:** Defense-in-depth. Elke laag moet tenant-isolatie garanderen, ook als een hogere laag faalt. [3]

---

## 6. Index Strategie

### 6.1 Primaire Indexen (Tenant-Isolatie)

Elke tenant-tabel krijgt een composite primary key of index die `company_id` als eerste kolom heeft. Dit garandeert dat RLS-queries efficiënt index-scannen in plaats van seq-scan.

```sql
-- Patroon: company_id als eerste kolom in alle tenant-indexen
-- Dit maakt partitionering mogelijk en optimaliseert RLS-filtering
```

### 6.2 Index-definities per Tabel

#### `compliance_items`

```sql
-- PK wijziging: composite met company_id
-- (migratie: bestaande id behouden, company_id toevoegen)
CREATE UNIQUE INDEX idx_compliance_items_pk
    ON compliance_items (company_id, id);
CREATE INDEX idx_compliance_items_status
    ON compliance_items (company_id, status);
CREATE INDEX idx_compliance_items_category
    ON compliance_items (company_id, category);
CREATE INDEX idx_compliance_items_priority
    ON compliance_items (company_id, priority);
CREATE INDEX idx_compliance_items_deadline
    ON compliance_items (company_id, deadline)
    WHERE deadline IS NOT NULL;
```

**Query impact:** `WHERE company_id = $1 AND status = 'open'` → Index Scan op `idx_compliance_items_status` in plaats van Seq Scan + Filter. Verwachte verbetering: factor 10-50x bij 100+ tenants. [7]

#### `incidents`

```sql
CREATE UNIQUE INDEX idx_incidents_pk
    ON incidents (company_id, id);
CREATE INDEX idx_incidents_status
    ON incidents (company_id, status);
CREATE INDEX idx_incidents_severity
    ON incidents (company_id, severity, date_occurred DESC);
CREATE INDEX idx_incidents_domain
    ON incidents (company_id, incident_domain);
CREATE INDEX idx_incidents_date
    ON incidents (company_id, date_occurred DESC);
CREATE INDEX idx_incidents_code
    ON incidents (company_id, incident_code);
```

**Query impact:** Dashboard-aggregatie (`COUNT(*) GROUP BY severity WHERE company_id = $1`) → Index-Only Scan. Critical path voor incident-dashboard.

#### `employees`

```sql
CREATE UNIQUE INDEX idx_employees_pk
    ON employees (company_id, id);
CREATE INDEX idx_employees_department
    ON employees (company_id, department);
CREATE INDEX idx_employees_active
    ON employees (company_id, active);
CREATE INDEX idx_employees_number
    ON employees (company_id, employee_number);
```

**Query impact:** Gap-analyse queries die employees joinen met certifications → Nested Loop met Index Scan. AVG-compliance: alleen eigen medewerkers zichtbaar.

#### `certifications`

```sql
CREATE UNIQUE INDEX idx_certifications_pk
    ON certifications (company_id, id);
CREATE INDEX idx_certifications_employee
    ON certifications (company_id, employee_id);
CREATE INDEX idx_certifications_expiry
    ON certifications (company_id, expiry_date)
    WHERE expiry_date IS NOT NULL;
CREATE INDEX idx_certifications_status
    ON certifications (company_id, status);
```

**Query impact:** "Expiring within 30 days" query → Index Range Scan op `idx_certifications_expiry`. Performance-critical voor alerting.

#### `risk_scenarios` (gemengd)

```sql
CREATE INDEX idx_risk_scenarios_tenant
    ON risk_scenarios (company_id, id)
    WHERE company_id IS NOT NULL;
CREATE INDEX idx_risk_scenarios_global
    ON risk_scenarios (id)
    WHERE company_id IS NULL;
CREATE INDEX idx_risk_scenarios_cas
    ON risk_scenarios (cas_number)
    WHERE cas_number IS NOT NULL;
```

**Query impact:** RLS policy `company_id IS NULL OR company_id = $1` → twee Index Scans (partial indexes), samengevoegd via BitmapOr. [7]

#### `risk_controls`

```sql
CREATE UNIQUE INDEX idx_risk_controls_pk
    ON risk_controls (company_id, id);
CREATE INDEX idx_risk_controls_scenario
    ON risk_controls (company_id, scenario_id);
CREATE INDEX idx_risk_controls_ahs
    ON risk_controls (company_id, ahs_level, effectiveness_score DESC)
    WHERE ahs_level IS NOT NULL;
CREATE INDEX idx_risk_controls_pgs15
    ON risk_controls (company_id, pgs15_code)
    WHERE pgs15_code IS NOT NULL;
```

#### `moc_requests`

```sql
CREATE UNIQUE INDEX idx_moc_requests_pk
    ON moc_requests (company_id, id);
CREATE INDEX idx_moc_requests_status
    ON moc_requests (company_id, status);
CREATE INDEX idx_moc_requests_priority
    ON moc_requests (company_id, priority);
CREATE INDEX idx_moc_requests_code
    ON moc_requests (company_id, moc_code);
CREATE INDEX idx_moc_requests_date
    ON moc_requests (company_id, created_at DESC);
```

#### `ptw_permits`

```sql
CREATE UNIQUE INDEX idx_ptw_permits_pk
    ON ptw_permits (company_id, id);
CREATE INDEX idx_ptw_permits_status
    ON ptw_permits (company_id, status);
CREATE INDEX idx_ptw_permits_type
    ON ptw_permits (company_id, permit_type);
CREATE INDEX idx_ptw_permits_code
    ON ptw_permits (company_id, permit_code);
CREATE INDEX idx_ptw_permits_employee
    ON ptw_permits (company_id, assigned_employee_id)
    WHERE assigned_employee_id IS NOT NULL;
CREATE INDEX idx_ptw_permits_date
    ON ptw_permits (company_id, start_date DESC);
```

#### `environment_metrics`

```sql
CREATE UNIQUE INDEX idx_environment_metrics_pk
    ON environment_metrics (company_id, id);
CREATE INDEX idx_environment_metrics_type
    ON environment_metrics (company_id, metric_type);
CREATE INDEX idx_environment_metrics_compliance
    ON environment_metrics (company_id, compliance_status);
CREATE INDEX idx_environment_metrics_date
    ON environment_metrics (company_id, measurement_date DESC);
```

#### `agent_tasks`

```sql
CREATE UNIQUE INDEX idx_agent_tasks_pk
    ON agent_tasks (company_id, id);
CREATE INDEX idx_agent_tasks_status
    ON agent_tasks (company_id, status);
CREATE INDEX idx_agent_tasks_agent
    ON agent_tasks (company_id, agent_id);
```

#### `intelligence_alerts` (gemengd)

```sql
CREATE INDEX idx_intel_alerts_tenant
    ON intelligence_alerts (company_id, id)
    WHERE company_id IS NOT NULL;
CREATE INDEX idx_intel_alerts_global
    ON intelligence_alerts (id)
    WHERE company_id IS NULL;
CREATE INDEX idx_intel_alerts_priority
    ON intelligence_alerts (company_id, priority, created_at DESC)
    WHERE company_id IS NOT NULL;
```

#### `intelligence_actions`

```sql
CREATE UNIQUE INDEX idx_intel_actions_pk
    ON intelligence_actions (company_id, id);
CREATE INDEX idx_intel_actions_status
    ON intelligence_actions (company_id, status);
CREATE INDEX idx_intel_actions_item
    ON intelligence_actions (company_id, scraped_item_id);
```

#### `compliance_deadlines` (gemengd)

```sql
CREATE INDEX idx_comp_deadlines_tenant
    ON compliance_deadlines (company_id, id)
    WHERE company_id IS NOT NULL;
CREATE INDEX idx_comp_deadlines_global
    ON compliance_deadlines (id)
    WHERE company_id IS NULL;
CREATE INDEX idx_comp_deadlines_due
    ON compliance_deadlines (company_id, due_date)
    WHERE company_id IS NOT NULL;
CREATE INDEX idx_comp_deadlines_status
    ON compliance_deadlines (company_id, status)
    WHERE company_id IS NOT NULL;
```

#### `contractors`

```sql
CREATE UNIQUE INDEX idx_contractors_pk
    ON contractors (company_id, id);
CREATE INDEX idx_contractors_active
    ON contractors (company_id, active);
```

#### `brzo_safety_reports`, `brzo_mapp`, `brzo_inspections`

```sql
-- Elk: composite PK met company_id
CREATE UNIQUE INDEX idx_brzo_reports_pk ON brzo_safety_reports (company_id, id);
CREATE UNIQUE INDEX idx_brzo_mapp_pk ON brzo_mapp (company_id, id);
CREATE UNIQUE INDEX idx_brzo_inspections_pk ON brzo_inspections (company_id, id);
```

### 6.3 Index-strategie Samenvatting

| Patroon                                     | Toepassing                                      |
|---------------------------------------------|-------------------------------------------------|
| `(company_id, id)` — composite PK           | Alle tenant-tabellen: RLS + lookup              |
| `(company_id, status)`                      | Filtering op status per tenant                  |
| `(company_id, date DESC)`                   | Tijd-geordende queries per tenant               |
| `(company_id, FK)`                          | Join-optimalisatie binnen tenant                |
| Partial index `WHERE company_id IS NULL`    | Globale data in gemengde tabellen               |
| Partial index `WHERE company_id IS NOT NULL`| Tenant-data in gemengde tabellen                |

---

## 7. SQLite → PostgreSQL Migratiestrategie

### 7.1 Migratie-overzicht

De migratie van SQLite naar PostgreSQL verloopt in 4 fasen, ontworpen voor minimale downtime:

```
Fase A          Fase B           Fase C            Fase D
Schema          Dual-Write       Cutover           Cleanup
Setup           (Shadow)         (Atomic)          (Validate)
──────────────────────────────────────────────────────────────
Week 1-2        Week 3-4         Week 5            Week 6
```

### 7.2 Fase A: Schema Setup

| Stap | Actie                                                      | Risico |
|------|------------------------------------------------------------|--------|
| A1   | PostgreSQL instantie inrichten (managed: RDS/Supabase/Aura) | Laag   |
| A2   | Schema aanmaken met `company_id` kolommen en UUID PKs       | Laag   |
| A3   | RLS policies aanmaken maar nog niet activeren               | Laag   |
| A4   | Indexen aanmaken (pre-create, voordat data geladen wordt)   | Laag   |
| A5   | Migratie-scripts ontwikkelen en testen in staging           | Medium |

**Schema-conversie mapping:**

| SQLite             | PostgreSQL                    | Notities                          |
|--------------------|-------------------------------|-----------------------------------|
| `INTEGER PK`       | `UUID DEFAULT gen_random_uuid()` | Auto-increment → UUID          |
| `TEXT`             | `TEXT` of `JSONB`             | JSON-velden → native JSONB       |
| `DATETIME (string)`| `TIMESTAMPTZ`                 | String-dates → native timestamps |
| `INTEGER (0/1)`    | `BOOLEAN`                     | Boolean-velden → native type     |
| `REAL`             | `NUMERIC` of `DOUBLE PRECISION`| Metingen → NUMERIC voor precisie|
| Geen FK enforcement| `REFERENCES ... ON DELETE`    | FK-constraints activeren         |

### 7.3 Fase B: Dual-Write (Shadow Mode)

| Stap | Actie                                                      | Risico |
|------|------------------------------------------------------------|--------|
| B1   | Schakel SQLAlchemy in op dual-write: schrijf naar SQLite én PostgreSQL | Medium |
| B2   | Lees blijft op SQLite (cutover pas in Fase C)              | Laag   |
| B3   | Bestaande data migreren: bulk `INSERT INTO postgres SELECT ... FROM sqlite` | Medium |
| B4   | Data-validatie: row-counts, checksums per tabel            | Laag   |
| B5   | RLS policies activeren in test-mode                        | Medium |

**Technische implementatie:**
```python
# SQLAlchemy session factory configuratie
# Dual-write via event listeners of custom session
class DualWriteSession:
    def __init__(self, sqlite_session, pg_session):
        self.sqlite = sqlite_session
        self.pg = pg_session

    def add(self, obj):
        self.sqlite.add(obj)
        self.pg.add(obj.__class__(**obj.__dict__))  # deepcopy
```

### 7.4 Fase C: Cutover (Atomic Switch)

| Stap | Actie                                                      | Risico |
|------|------------------------------------------------------------|--------|
| C1   | Maintenance mode aanzetten (geen nieuwe writes)            | Hoog   |
| C2   | Laatste delta sync: SQLite → PostgreSQL                    | Hoog   |
| C3   | Lees-verkeer omschakelen naar PostgreSQL                   | Hoog   |
| C4   | Write-verkeer omschakelen (enkel PostgreSQL)               | Hoog   |
| C5   | RLS policies volledig activeren                            | Hoog   |
| C6   | Smoke tests: alle API-endpoints verifiëren                 | Medium |
| C7   | Maintenance mode uit                                       | Hoog   |

**Terugdraaiprocedure:** Bij falen in C3-C6: terugvallen op SQLite (read+write). PostgreSQL data wissen en opnieuw migreren. Maximaal 1 uur downtime.

### 7.5 Fase D: Cleanup & Validatie

| Stap | Actie                                                      | Risico |
|------|------------------------------------------------------------|--------|
| D1   | 7 dagen dual-read monitoring (PostgreSQL vs. SQLite)       | Laag   |
| D2   | Performance benchmarks vergelijken                         | Laag   |
| D3   | SQLite backup maken en archiveren                          | Laag   |
| D4   | SQLite uit deployment verwijderen                          | Medium |
| D5   | Tenant-isolatie pentest (RLS bypass-test)                  | Medium |
| D6   | Documentatie bijwerken                                     | Laag   |

### 7.6 Migratie Tooling

| Tool                    | Doel                                    | Alternatief            |
|-------------------------|-----------------------------------------|------------------------|
| `pgloader`              | SQLite → PostgreSQL bulk migratie       | Custom Python script   |
| `Alembic`               | Schema-migratiebeheer                   | —                      |
| `sqlalchemy`            | ORM-laag (al in gebruik, aanpassen)     | —                      |
| `pg_dump` / `pg_restore`| Backup/restore PostgreSQL               | —                      |
| `wal2json`              | Logical decoding voor CDC (optioneel)   | Debezium              |

### 7.7 Risico's en Mitigaties

| Risico                                  | Waarschijnlijkheid | Impact | Mitigatie                                     |
|-----------------------------------------|--------------------|--------|-----------------------------------------------|
| Data-verlies bij migratie               | Laag               | Kritiek| Backup + checksums + dual-write periode       |
| RLS policy fout (data-leak)             | Medium             | Kritiek| Tenant-isolatie pentest (Fase D5)             |
| Performance-degradatie door RLS overhead| Medium             | Medium | Benchmark met productie-data volume           |
| Downtime > 1 uur                        | Laag               | Hoog   | Rehearsal in staging, atomic cutover          |
| UUID-migratie breekt FK-relaties        | Medium             | Hoog   | Mapping-tabel (old_int_id → new_uuid)         |
| JSONB-query compatibiliteit            | Laag               | Laag   | Testen in Fase B                              |

---

## 8. `company_id` Toevoeging — Detail per Tabel

### 8.1 Conversie-patroon

Voor elke tenant-tabel wordt het volgende patroon toegepast:

```sql
-- 1. Kolom toevoegen (nullable eerst — bestaande data)
ALTER TABLE compliance_items ADD COLUMN company_id UUID;

-- 2. Bestaande data koppelen aan default tenant (migratie)
UPDATE compliance_items SET company_id = '550e8400-...' WHERE company_id IS NULL;

-- 3. NOT NULL constraint toevoegen
ALTER TABLE compliance_items ALTER COLUMN company_id SET NOT NULL;

-- 4. FK constraint toevoegen
ALTER TABLE compliance_items ADD CONSTRAINT fk_compliance_items_company
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE;

-- 5. Composite unique index (voor RLS-optimalisatie)
CREATE UNIQUE INDEX idx_compliance_items_pk ON compliance_items (company_id, id);

-- 6. RLS inschakelen
ALTER TABLE compliance_items ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON compliance_items
    USING (company_id = current_company_id());
```

### 8.2 Per-Tabel Overzicht

| Tabel                     | `company_id` kolom | Nullable in migratie | Default tenant mapping          |
|---------------------------|:------------------:|:--------------------:|---------------------------------|
| `compliance_items`        | ✅ `UUID NOT NULL` | Nee (alle data → tenant) | Bestaande data → eerste tenant |
| `incidents`               | ✅ `UUID NOT NULL` | Nee                   | Alle incidenten → eerste tenant |
| `employees`               | ✅ `UUID NOT NULL` | Nee                   | Alle medewerkers → eerste tenant |
| `contractors`             | ✅ `UUID NOT NULL` | Nee                   | Alle contracten → eerste tenant |
| `certifications`          | ✅ `UUID NOT NULL` | Nee                   | Via `employee.company_id`       |
| `risk_scenarios`          | ✅ `UUID`          | Ja (`NULL` = global)  | Bedrijfspecifieke → tenant      |
| `risk_controls`           | ✅ `UUID NOT NULL` | Nee                   | Via `scenario.company_id`       |
| `moc_requests`            | ✅ `UUID NOT NULL` | Nee                   | Alle MoC → eerste tenant        |
| `ptw_permits`             | ✅ `UUID NOT NULL` | Nee                   | Alle PTW → eerste tenant        |
| `environment_metrics`     | ✅ `UUID NOT NULL` | Nee                   | Alle metingen → eerste tenant   |
| `intelligence_alerts`     | ✅ `UUID`          | Ja (`NULL` = global)  | Alleen tenant-override records  |
| `intelligence_actions`    | ✅ `UUID NOT NULL` | Nee                   | Alle acties → tenant            |
| `compliance_deadlines`    | ✅ `UUID`          | Ja (`NULL` = global)  | Alleen tenant-override records  |
| `agent_tasks`             | ✅ `UUID NOT NULL` | Nee                   | Alle taken → eerste tenant      |
| `brzo_safety_reports`     | ✅ `UUID NOT NULL` | Nee                   | Alle rapporten → eerste tenant  |
| `brzo_mapp`               | ✅ `UUID NOT NULL` | Nee                   | MAPP → eerste tenant            |
| `brzo_inspections`        | ✅ `UUID NOT NULL` | Nee                   | Inspecties → eerste tenant      |

---

## 9. Applicatie-laag Integratie

### 9.1 SQLAlchemy ORM Aanpassingen

```python
from sqlalchemy import Column, String, create_engine
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import sessionmaker
from flask import g

# Base mixin voor tenant-tabellen
class TenantMixin:
    company_id = Column(UUID(as_uuid=True), nullable=False, index=True)

# Session context: company_id automatisch injecteren
class TenantSession:
    def __init__(self, session_factory):
        self.Session = session_factory

    def __enter__(self):
        self.session = self.Session()
        # Stel PostgreSQL session variable in voor RLS
        company_id = getattr(g, 'company_id', None)
        if company_id:
            self.session.execute(
                text("SET app.company_id = :cid"),
                {"cid": str(company_id)}
            )
        return self.session

    def __exit__(self, *args):
        self.session.close()
```

### 9.2 Flask Middleware (Tenant Resolutie)

```python
from flask import request, g

# Optie 1: Subdomain-gebaseerd (acme.hseq-platform.nl)
# Optie 2: Header-gebaseerd (X-Company-Id)
# Optie 3: JWT-claim (token.company_id)

@app.before_request
def resolve_tenant():
    token = decode_jwt(request.headers.get('Authorization'))
    g.company_id = token.get('company_id')
    g.user_role = token.get('role')
```

### 9.3 API-namespacing

```
Huidig:    /api/compliance           → retourneert ALLE items
Nieuw:     /api/v1/{company_slug}/compliance  → RLS-filtered
           /api/v1/global/scraper/status       → platform-breed
```

---

## 10. Bronverwijzingen

| #  | Bron                                                                                           |
|----|------------------------------------------------------------------------------------------------|
| 1  | Phase 1 Database Analyse Rapport — `phase1_database_analysis_v1.0.md` (2026-05-26)            |
| 2  | PostgreSQL UUID primary keys best practice — postgresql.org/docs/current/datatype-uuid.html    |
| 3  | PostgreSQL Row Security Policies — postgresql.org/docs/current/ddl-rowsecurity.html            |
| 4  | SQLite to PostgreSQL migration guide — pgloader.io                                             |
| 5  | BRZO/Seveso III richtlijn — Besluit risico's zware ongevallen 2015 (Rijksoverheid.nl)        |
| 6  | AVG/GDPR art.5 — Beginselen inzake verwerking (eur-lex.europa.eu)                            |
| 7  | PostgreSQL partial indexes en RLS performance — postgresql.org/docs/current/indexes-partial.html |
| 8  | Multi-tenant SaaS database patterns — AWS Well-Architected Framework (docs.aws.amazon.com)    |
| 9  | SQLAlchemy session events en multi-tenancy — docs.sqlalchemy.org                             |
| 10 | Alembic migration best practices — alembic.sqlalchemy.org                                     |

---

## 11. Verify — TierVerify Log

| Controle-item                                        | Resultaat | Notitie                                                  |
|------------------------------------------------------|-----------|----------------------------------------------------------|
| Geen database-wijzigingen uitgevoerd                 | ✅ PASS   | Uitsluitend design-document                              |
| Alle 18+ tabellen geclassificeerd (tenant/global)    | ✅ PASS   | 12 tenant, 6 global, 4 gemengd                          |
| `company_id` FK gedocumenteerd per tabel             | ✅ PASS   | §8.2: 17 tabellen met company_id mapping                 |
| RLS policies gedefinieerd per tabel                  | ✅ PASS   | §5.3: volledige SQL per tabel                            |
| Index-strategie gedocumenteerd per tabel             | ✅ PASS   | §6.2: composite indexes met company_id eerst             |
| Query impact beschreven                              | ✅ PASS   | Per tabel in §6.2                                        |
| Migratiestrategie SQLite → PostgreSQL                | ✅ PASS   | §7: 4 fasen met risico-mitigatie                         |
| Global vs. Tenant model uitgewerkt                   | ✅ PASS   | §4: inclusief gemengd model                              |
| Bronverwijzingen compleet                            | ✅ PASS   | 10 bronnen gedocumenteerd                                |
| Document-header aanwezig                             | ✅ PASS   | Project, Type, Auteur, Versie, Datum, Status             |
| Output in deliverables/ met versietag                | ✅ PASS   | `phase2_database_architecture_v1.0.md`                   |
| Onzekere claims gemarkeerd                           | ✅ PASS   | Risico's met waarschijnlijkheid/impact matrix            |

---

*Einde Phase 2 Database Architectuur Design — gereed voor volgende instructies.*