# Phase 1: HSEQ Dashboard Database Analyse | Veld | Waarde | |-------------|---------------------------------------------------| | **Project** | 2026-hseq-saas-multitenant | | **Type** | Database Analyse Rapport | | **Auteur** | Database Administrator Agent | | **Versie** | 1.0 | | **Datum** | 2026-05-26 | | **Status** | Afgerond — READ-ONLY analyse | --- ## 1. Samenvatting Read-only database-analyse van het HSEQ Intelligence Dashboard op `https://mescalinerabbit.shop/hseq-dashboard/`. Het schema is gereconstrueerd via API-endpoint-respons analyse (reverse engineering). De exacte databasetype kon niet direct worden vastgesteld (Cloudflare verbergt server-headers), maar **sterke indicatoren wijzen naar SQLite** (Flask/Werkzeug-stack, geen server-side DB-config zichtbaar, embedded deployment patroon). **Er zijn geen wijzigingen aangebracht aan de live omgeving.** [1][2] --- ## 2. Database Type Determinatie ### 2.1 Indicatoren | Indicator | Observatie | Conclusie | |------------------------------------------|-------------------------------------------------------------------|----------------------| | Flask/Werkzeug 404-pagina | Standaard Werkzeug error-template [2] | Flask + Python | | Auto-increment integer PKs | Alle `id`-velden zijn sequentiële integers (1, 2, 3...) [1] | Relationele DB | | DateTime-velden als strings | `"2026-04-10 18:47:07"` — string-gebaseerd, geen native datetime-object [1] | SQLite-gedrag | | Geen server-side DB-header | Cloudflare verbergt origin-server; geen `X-Database` of DB-specifieke headers [3] | Onzeker | | Embedded deployment (single-machine) | Flask + scraper + AI op één domein; geen afzonderlijke DB-host [4] | SQLite waarschijnlijk | | NULL-waarden als JSON `null` | Veel velden retourneren `null` — consistent met SQLite/Python dict-serialisatie [1] | Consistent | | Microsecond-timestamps | `"2026-04-22T12:46:30.994803"` — Python datetime met microseconden [5] | Python datetime | | ID-bereik tot ~2977 (scraper runs) | Hoge ID's duiden op lange looptijd; geen tekenen van UUID/ULID [5] | Auto-increment | | Geen transactie-isolatie zichtbaar | API-responses tonen geen DB-specifieke foutmeldingen [1] | Abstractielaag aanwezig | ### 2.2 Conclusie | Aspect | Waarde | |-------------------|-------------------------------------------------------------| | **Databasetype** | Waarschijnlijk **SQLite** (embedded, Flask-compatibel) [6] | | **Alternatief** | PostgreSQL (mogelijk, maar minder waarschijnlijk gezien deployment patroon) | | **ORM** | Waarschijnlijk **SQLAlchemy** (Flask-standaard) | | **Zekerheid** | 75% SQLite / 25% PostgreSQL | > **Aanbeveling:** Bevestig het exacte DB-type via server-toegang in Fase 2 (config-inspectie of `db.session` analyse). [6] --- ## 3. Schema Reconstructie ### 3.1 Overzicht Geïdentificeerde Tabellen Op basis van API-endpoint-respons zijn **25+ tabellen** gereconstrueerd: | # | Tabelnaam (afgeleid) | API-bron | Record count (indicatie) | |----|---------------------------------|--------------------------------------------|--------------------------| | 1 | `compliance_items` | `/api/compliance` | 49 | | 2 | `incidents` | `/api/incidents` | 2 | | 3 | `employees` | `/api/training/dashboard` | 7 | | 4 | `contractors` | `/api/training/dashboard` | 2 | | 5 | `contractor_employees` | `/api/training/gap-analysis` | 3+ | | 6 | `certifications` | `/api/training/fase2/certificates` | 9 | | 7 | `training_programs` | `/api/training/dashboard` | 15 | | 8 | `training_calendar` | `/api/training/fase2/calendar` | 1+ | | 9 | `competencies` | `/api/training/fase2/competencies` | 1+ | | 10 | `toolbox_talks` | `/api/training/fase2/toolbox-talks` | 1+ | | 11 | `scraper_sources` | `/api/scraper/status` | 29 | | 12 | `scraper_runs` | `/api/scraper/history` | ~2977 | | 13 | `scraped_items` | Afgeleid van intelligence alerts | ~205+ | | 14 | `intelligence_alerts` | `/api/intelligence/v2/alerts` | 46+ | | 15 | `intelligence_actions` | `/api/intelligence/v2/actions` | 7 | | 16 | `risk_scenarios` | `/api/rie/scenarios` | 3+ | | 17 | `risk_controls` | `/api/rie/scenarios` (embedded) | 1+ | | 18 | `rie_sections` | `/api/rie/sections` | 7+ | | 19 | `moc_requests` | `/api/moc/requests` | 1 | | 20 | `ptw_permits` | `/api/ptw/permits` | 1 | | 21 | `environment_metrics` | `/api/environment/metrics` | 1+ | | 22 | `compliance_deadlines` | `/api/compliance/deadlines` | 13+ | | 23 | `compliance_gaps` | `/api/compliance/gaps` | Meerdere | | 24 | `vbs_elements` | `/api/brzo/vbs-compliance` | 7 | | 25 | `brzo_safety_reports` | `/api/brzo/safety-report` | 1 | | 26 | `brzo_mapp` | `/api/brzo/mapp` | 1 | | 27 | `brzo_inspections` | `/api/brzo/inspection` | 1 | | 28 | `notifications` | `/api/notifications` | 0 (geleegd) | | 29 | `alerts` | `/api/alerts` | 1+ | | 30 | `agent_tasks` | `/api/agents/tasks` | 20+ | | 31 | `lms_certificates` | `/api/training/fase2/certificates` | 5+ | | 32 | `knowledge_documents` | Afgeleid van `/api/knowledge/chat` | Onbekend | | 33 | `users` | Login-form (email/password) | Onbekend | | 34 | `companies` | `/admin/companies` (auth-required) | Onbekend | | 35 | `compliance_rag` | `/api/compliance/rag` (aggregatie) | Afgeleid | --- ## 4. Gedetailleerde Schema per Tabel ### 4.1 `compliance_items` | Kolom | Type (afgeleid) | Nullable | Beschrijving | |-----------------|------------------|----------|---------------------------------------| | `id` | INTEGER PK | Nee | Auto-increment | | `requirement` | TEXT | Nee | Eisenomschrijving | | `category` | TEXT | Nee | BRZO / Arbowet / VBS / ATEX / etc. | | `status` | TEXT | Nee | open / compliant / in_progress / not_started | | `priority` | TEXT | Nee | critical / high / medium / low | | `notes` | TEXT | Ja | Referenties (AI-xx, regelgeving) | | `owner` | TEXT | Ja | Verantwoordelijke | | `evidence` | TEXT | Ja | Bewijsstukken | | `deadline` | DATETIME | Ja | Uiterste datum | | `article_ref` | TEXT | Ja | Wettelijk artikelreferentie | | `due_soon` | INTEGER | Nee | 0/1 boolean | | `created_date` | DATETIME | Nee | Aanmaakdatum | | `updated_date` | DATETIME | Nee | Wijzigingsdatum | **Enumeraties:** - `category`: `BRZO`, `Arbowet`, `VBS`, `ATEX`, `Brandveiligheid`, `Elektrisch`, `Omgevingswet`, `ISO 14001`, `ISO 45001`, `Overig` - `status`: `compliant`, `in_progress`, `open`, `not_started` - `priority`: `critical`, `high`, `medium`, `low` ### 4.2 `incidents` | Kolom | Type (afgeleid) | Nullable | Beschrijving | |--------------------------------|------------------|----------|---------------------------------------| | `id` | INTEGER PK | Nee | Auto-increment | | `incident_code` | TEXT | Nee | Format: `INC-YYYYMMDD-NNN` | | `title` | TEXT | Nee | Titels | | `description` | TEXT | Nee | Gedetailleerde beschrijving | | `incident_type` | TEXT | Nee | major_incident / significant_incident / etc. | | `incident_domain` | TEXT | Nee | arbo / process_safety / environmental | | `severity` | TEXT | Nee | major / significant / minor / near_miss | | `status` | TEXT | Nee | reported / investigating / closed | | `location` | TEXT | Ja | Fysieke locatie | | `equipment_tag` | TEXT | Ja | Tag-nummer (bijv. `V-201`) | | `date_occurred` | DATETIME | Nee | Tijdstip incident | | `date_reported` | DATETIME | Nee | Tijdstip melding | | `date_detected` | DATETIME | Ja | Detectietijdstip | | `reported_by` | TEXT | Ja | Melder | | `reporting_authority` | TEXT | Ja | Externe meldingsplicht | | `reporting_required` | INTEGER | Nee | 0/1 boolean | | `reporting_deadline` | DATETIME | Ja | Wettelijke meldingstermijn | | `reporting_completed` | INTEGER | Nee | 0/1 boolean | | `reporting_reference` | TEXT | Ja | Referentienummer externe melding | | `mir_required` | INTEGER | Nee | 0/1 — Major Incident Report | | `mir_number` | TEXT | Ja | MIR-referentie | | `first_aid_required` | INTEGER | Nee | 0/1 boolean | | `medical_treatment_required` | INTEGER | Nee | 0/1 boolean | | `hospitalization_required` | INTEGER | Nee | 0/1 boolean | | `lost_time_injury` | INTEGER | Nee | 0/1 boolean | | `lost_time_days` | INTEGER | Nee | Aantal dagen verzuim | | `number_of_casualties` | INTEGER | Nee | Aantal gewonden | | `number_of_fatalities` | INTEGER | Nee | Aantal doden | | `immediate_actions` | TEXT | Ja | Genomen directe maatregelen | | `substance_involved` | INTEGER | Nee | 0/1 boolean | | `related_substance_id` | INTEGER FK | Ja | → substance-tabel | | `substance_quantity_released` | REAL | Ja | Hoeveelheid vrijgekomen | | `substance_quantity_unit` | TEXT | Ja | Eenheid (liter / kg / etc.) | | `spill_contained` | INTEGER | Nee | 0/1 boolean | | `spill_reached_soil` | INTEGER | Nee | 0/1 boolean | | `spill_reached_water` | INTEGER | Nee | 0/1 boolean | | `spill_reached_drain` | INTEGER | Nee | 0/1 boolean | | `air_emission_exceeded` | INTEGER | Nee | 0/1 boolean | | `environmental_impact` | TEXT | Ja | Milieueffect | | `scene_secured` | INTEGER | Nee | 0/1 boolean | | `evidence_preserved` | INTEGER | Nee | 0/1 boolean | | `evidence_files` | TEXT | Ja | Bewijsbestanden | | `photo_urls` | TEXT | Ja | Foto-URLs | | `investigation_required` | INTEGER | Nee | 0/1 boolean | | `investigation_method` | TEXT | Ja | Onderzoeksmethode | | `investigation_lead` | TEXT | Ja | Onderzoeksleider | | `investigation_team` | TEXT | Ja | Onderzoeksteam | | `investigation_start_date` | DATETIME | Ja | Start onderzoek | | `investigation_due_date` | DATETIME | Ja | Deadline onderzoek | | `investigation_completion_date`| DATETIME | Ja | Afronding onderzoek | | `ai_analysis_enabled` | INTEGER | Nee | 0/1 — AI root-cause | | `ai_analysis_status` | TEXT | Ja | pending / completed / failed | | `ai_analysis_result` | TEXT | Ja | AI-analyseresultaat | | `ai_analysis_date` | DATETIME | Ja | AI-analyse datum | | `related_moc_id` | INTEGER FK | Ja | → moc_requests | | `related_ptw_id` | INTEGER FK | Ja | → ptw_permits | | `related_risk_scenario_id` | INTEGER FK | Ja | → risk_scenarios | | `acknowledged_by` | TEXT | Ja | Bevestigd door | | `acknowledged_at` | DATETIME | Ja | Bevestigd op | | `lessons_learned` | TEXT | Ja | Lessons learned | | `lessons_distributed` | INTEGER | Nee | 0/1 boolean | | `procedure_updates` | TEXT | Ja | Procedure-wijzigingen | | `training_required` | INTEGER | Nee | 0/1 boolean | | `anonymous_report` | INTEGER | Nee | 0/1 boolean | | `brzo_major_accident` | INTEGER | Nee | 0/1 — BRZO relevant | | `notes` | TEXT | Ja | Opmerkingen | | `created_date` | DATETIME | Nee | Aanmaakdatum | | `updated_date` | DATETIME | Nee | Wijzigingsdatum | ### 4.3 `employees` | Kolom (afgeleid) | Type | Beschrijving | |---------------------|--------------|-------------------------------| | `id` | INTEGER PK | Auto-increment | | `employee_number` | TEXT | Format: `PM-NNN` | | `first_name` | TEXT | Voornaam | | `last_name` | TEXT | Achternaam | | `role` | TEXT | Functie | | `department` | TEXT | Afdeling | | `active` | INTEGER | 0/1 | *Schema afgeleid van gap-analysis response; 7 actieve medewerkers geïdentificeerd.* [7] ### 4.4 `contractors` | Kolom (afgeleid) | Type | Beschrijving | |---------------------|--------------|-------------------------------| | `id` | INTEGER PK | Auto-increment | | `company_name` | TEXT | Bedrijfsnaam | | `active` | INTEGER | 0/1 | *2 actieve contracten geïdentificeerd (PipeFit Solutions B.V.).* [7] ### 4.5 `certifications` | Kolom (afgeleid) | Type | Beschrijving | |-----------------------|--------------|-------------------------------| | `id` | INTEGER PK | Auto-increment | | `employee_id` | INTEGER FK | → employees | | `cert_type` | TEXT | VCA / HF / EX / etc. | | `training_code` | TEXT | TRN-XX-NN | | `status` | TEXT | valid / expired / expiring | | `expiry_date` | DATE | Vervaldatum | | `last_renewed` | DATE | Laatst vernieuwd | *9 certificeringen, 2 verlopen, 1 vervalt binnen 30 dagen.* [7] ### 4.6 `scraper_sources` | Kolom | Type | Nullable | Beschrijving | |------------------|--------------|----------|---------------------------------------| | `id` | TEXT PK | Nee | String-ID (bijv. `arboportaal`) | | `name` | TEXT | Nee | Weergavenaam | | `url` | TEXT | Nee | Scraping-URL | | `tier` | INTEGER | Nee | 1 (primair) / 2 / 3 (extern) | | `status` | TEXT | Nee | success / error / disabled | | `total_items` | INTEGER | Nee | Totaal gescrapte items | | `items_24h` | INTEGER | Nee | Items afgelopen 24u | | `items_7d` | INTEGER | Nee | Items afgelopen 7 dagen | | `last_run` | DATETIME | Nee | Laatste run-tijdstip | *29 bronnen geïdentificeerd over 3 tiers.* [5] ### 4.7 `scraper_runs` | Kolom | Type | Nullable | Beschrijving | |------------------|--------------|----------|---------------------------------------| | `id` | INTEGER PK | Nee | Auto-increment (~2977+) | | `source_id` | TEXT FK | Nee | → scraper_sources.id | | `source_name` | TEXT | Nee | Denormaliseerd | | `status` | TEXT | Nee | success / error | | `items_found` | INTEGER | Nee | Gevonden items | | `items_new` | INTEGER | Nee | Nieuwe items | | `run_at` | DATETIME | Nee | Run-tijdstip | ### 4.8 `intelligence_alerts` | Kolom | Type | Nullable | Beschrijving | |--------------------|--------------|----------|-------------------------------------| | `id` | INTEGER PK | Nee | Auto-increment | | `title` | TEXT | Nee | Alert-titel | | `message` | TEXT | Nee | AI-gegenereerde beschrijving | | `priority` | TEXT | Nee | HOOG / MEDIUM / LAAG | | `source` | TEXT | Nee | Bron-identificator | | `url` | TEXT | Nee | Originele URL | | `scraped_item_id` | INTEGER FK | Nee | → scraped_items | | `is_read` | INTEGER | Nee | 0/1 boolean | | `created_at` | DATETIME | Nee | Aanmaakdatum | *34 open alerts, laatste gegenereerd op 2026-05-13.* [5] ### 4.9 `intelligence_actions` | Kolom | Type | Nullable | Beschrijving | |--------------------|--------------|----------|-------------------------------------| | `id` | INTEGER PK | Nee | Auto-increment | | `title` | TEXT | Nee | Actie-titel | | `action_type` | TEXT | Nee | training / custom / news | | `action_data` | TEXT (JSON) | Nee | Gelabelde actiedata | | `source` | TEXT | Nee | Bron | | `url` | TEXT | Nee | Originele URL | | `scraped_item_id` | INTEGER FK | Nee | → scraped_items | | `status` | TEXT | Nee | open / completed | | `created_at` | DATETIME | Nee | Aanmaakdatum | ### 4.10 `risk_scenarios` | Kolom | Type | Nullable | Beschrijving | |----------------------------|--------------|----------|---------------------------------| | `id` | INTEGER PK | Nee | Auto-increment | | `description` | TEXT | Nee | Scenario-beschrijving | | `cas_number` | TEXT | Ja | CAS-nummer stof | | `h_phrases` | TEXT | Ja | H-zinnen | | `hazard_class` | TEXT | Ja | Gevaarsklasse | | `is_toxic` | INTEGER | Nee | 0/1 | | `is_corrosive` | INTEGER | Nee | 0/1 | | `is_flammable` | INTEGER | Nee | 0/1 | | `is_explosive` | INTEGER | Nee | 0/1 | | `is_carcinogenic` | INTEGER | Nee | 0/1 | | `is_mutagenic` | INTEGER | Nee | 0/1 | | `is_reprotoxic` | INTEGER | Nee | 0/1 | | `is_oxidizing` | INTEGER | Nee | 0/1 | | `idlh` | REAL | Ja | Immediately Dangerous to Life | | `flash_point` | REAL | Ja | Vlampunt | | `atex_category` | TEXT | Ja | ATEX-categorie | | `atex_group` | TEXT | Ja | ATEX-groep | | `hazop_node` | TEXT | Ja | HAZOP-knoop (bijv. P-101) | | `hazop_guide_word` | TEXT | Ja | HAZOP-gidswort | | `hazop_parameter` | TEXT | Ja | HAZOP-parameter | | `hazop_deviation` | TEXT | Ja | HAZOP-deviatie | | `hazop_cause` | TEXT | Ja | Oorzaak | | `hazop_consequence` | TEXT | Ja | Gevolg | | `first_aid_skin` | TEXT | Ja | EHBO huid | | `first_aid_eye` | TEXT | Ja | EHBO ogen | | `first_aid_inhalation` | TEXT | Ja | EHBO inademing | | `equipment_tag` | TEXT | Ja | Apparaattag | | `mitigated_*` | — | Ja | Meerdere mitigatie-velden | | `created_date` | DATETIME | Nee | Aanmaakdatum | **Embedded relatie:** `controls` (1-to-many, zie 4.11) ### 4.11 `risk_controls` | Kolom | Type | Nullable | Beschrijving | |------------------------------|------------|----------|-------------------------------| | `id` | INTEGER PK | Nee | Auto-increment | | `scenario_id` | INTEGER FK | Nee | → risk_scenarios.id | | `title` | TEXT | Nee | Maatregel-titel | | `description` | TEXT | Ja | Beschrijving | | `control_type` | TEXT | Ja | preventive / mitigating | | `ahs_level` | TEXT | Ja | bron / technisch / organisatorisch / PBM | | `ahs_level_order` | INTEGER | Ja | Sorteervolgorde | | `effectiveness_score` | INTEGER | Ja | Effectiviteit (1-5) | | `reliability` | TEXT | Ja | low / medium / high | | `implementation_status` | TEXT | Ja | planned / active / inactive | | `pgs15_code` | TEXT | Ja | PGS-15 maatregelcode | | `verification_date` | DATE | Ja | Verificatiedatum | | `next_verification_date` | DATE | Ja | Volgende verificatie | | `verified_by` | TEXT | Ja | Verificeerder | | `linked_action_id` | INTEGER FK | Ja | → actions | | `linked_document_id` | INTEGER FK | Ja | → documents | | `linked_pbzo_requirement_id` | INTEGER FK | Ja | → compliance requirements | | `notes` | TEXT | Ja | Opmerkingen | | `created_date` | DATETIME | Nee | Aanmaakdatum | | `updated_date` | DATETIME | Nee | Wijzigingsdatum | ### 4.12 `rie_sections` | Kolom | Type | Nullable | Beschrijving | |------------------|--------------|----------|-------------------------------------| | `id` | INTEGER PK | Nee | Auto-increment | | `section_code` | TEXT | Nee | `RIE-01` t/m `RIE-07` | | `title` | TEXT | Nee | Sectietitel | | `description` | TEXT | Nee | Beschrijving | | `category` | TEXT | Nee | arbo_general / ergonomic / psychosocial / etc. | | `sort_order` | INTEGER | Nee | Volgorde | | `active` | INTEGER | Nee | 0/1 | | `created_date` | DATETIME | Nee | Aanmaakdatum | | `updated_date` | DATETIME | Nee | Wijzigingsdatum | ### 4.13 `moc_requests` | Kolom | Type | Nullable | Beschrijving | |----------------------------|------------|----------|-------------------------------| | `id` | INTEGER PK | Nee | Auto-increment | | `moc_code` | TEXT | Nee | `MOC-YYYY-NNN` | | `title` | TEXT | Nee | Titel | | `proposed_change` | TEXT | Ja | Voorgestelde wijziging | | `change_category` | TEXT | Nee | equipment / process / etc. | | `change_scope` | TEXT | Nee | minor / moderate / major | | `priority` | TEXT | Nee | low / medium / high / critical| | `status` | TEXT | Nee | draft / under_review / approved / rejected / closed | | `related_risk_scenario_id` | INTEGER FK | Ja | → risk_scenarios | | `related_substance_id` | INTEGER FK | Ja | → substances | | `atex_zone_affected` | INTEGER | Nee | 0/1 | | `breaking_containment` | INTEGER | Nee | 0/1 | | `pssr_required` | INTEGER | Nee | 0/1 — Pre-Startup Safety Review | | `training_required` | INTEGER | Nee | 0/1 | | `requested_by` | TEXT | Ja | Aanvrager | | `requested_by_role` | TEXT | Ja | Rol aanvrager | | `department` | TEXT | Ja | Afdeling | | `target_date` | DATE | Ja | Streefdatum | | `closed_at` | DATETIME | Ja | Afsluitdatum | | `closed_by` | TEXT | Ja | Afgesloten door | | `notes` | TEXT | Ja | Opmerkingen | | `created_at` | DATETIME | Nee | Aanmaakdatum | | `updated_at` | DATETIME | Nee | Wijzigingsdatum | ### 4.14 `ptw_permits` | Kolom | Type | Nullable | Beschrijving | |-------------------------------|------------|----------|------------------------------| | `id` | INTEGER PK | Nee | Auto-increment | | `permit_code` | TEXT | Nee | `PTW-YYYYMMDD-NNN` | | `title` | TEXT | Nee | Titel | | `work_description` | TEXT | Ja | Werkomschrijving | | `permit_type` | TEXT | Nee | breaking_containment / hot_work / confined_space / etc. | | `risk_level` | TEXT | Nee | low / medium / high / critical | | `status` | TEXT | Nee | draft / active / completed / cancelled | | `location` | TEXT | Ja | Locatie | | `area_zone` | TEXT | Ja | Zone | | `atex_zone` | TEXT | Ja | ATEX-zone | | `related_substance_id` | INTEGER FK | Ja | → substances | | `requested_by` | TEXT | Ja | Aanvrager | | `assigned_employee_id` | INTEGER FK | Ja | → employees | | `assigned_contractor_employee_id` | INTEGER FK | Ja | → contractor_employees | | `contractor` | TEXT | Ja | Aannemer | | `start_date` | DATETIME | Ja | Start | | `end_date` | DATETIME | Ja | Einde | | `gas_testing_required` | INTEGER | Nee | 0/1 | | `gas_test_toxic_unit` | TEXT | Ja | Eenheid (bijv. `ppm HF`) | | `loto_required` | INTEGER | Nee | 0/1 | | `isolation_verified` | INTEGER | Nee | 0/1 | | `continuous_monitoring` | INTEGER | Nee | 0/1 | | `fire_extinguisher_available` | INTEGER | Nee | 0/1 | | `hf_antidote_available` | INTEGER | Nee | 0/1 | | `competency_check_passed` | INTEGER | Nee | 0/1 | | `ppe_respiratory` | TEXT | Ja | Adembescherming | | `ppe_gloves` | TEXT | Ja | Handschoenen | | `ppe_eye` | TEXT | Ja | Oogbescherming | | `ppe_body` | TEXT | Ja | Lichaamsbescherming | | `emergency_equipment` | TEXT | Ja | Nooduitrusting | | `special_instructions` | TEXT | Ja | Speciale instructies | | `issuer_name` | TEXT | Ja | Vergunningverlener | | `issuer_signed_at` | DATETIME | Ja | Ondertekening verlener | | `closer_name` | TEXT | Ja | Afsluiter | | `closer_signed_at` | DATETIME | Ja | Ondertekening afsluiter | | `completed_at` | DATETIME | Ja | Afronding | | `notes` | TEXT | Ja | Opmerkingen | | `created_at` | DATETIME | Nee | Aanmaakdatum | | `updated_at` | DATETIME | Nee | Wijzigingsdatum | ### 4.15 `environment_metrics` | Kolom | Type | Nullable | Beschrijving | |--------------------------|------------|----------|-------------------------------| | `id` | INTEGER PK | Nee | Auto-increment | | `metric_type` | TEXT | Nee | air_emission / water_discharge / waste / noise | | `parameter_name` | TEXT | Nee | Parameternaam (bijv. "VOS totaal") | | `value` | REAL | Nee | Gemeten waarde | | `unit` | TEXT | Nee | Eenheid (mg/m3, dB, etc.) | | `permit_limit` | REAL | Ja | Vergunningslimiet | | `percentage_of_limit` | REAL | Ja | Percentage van limiet | | `compliance_status` | TEXT | Nee | compliant / warning / non_compliant | | `source_location` | TEXT | Ja | Bronlocatie | | `measurement_date` | DATE | Nee | Meetdatum | | `measured_by` | TEXT | Ja | Meter | | `measurement_method` | TEXT | Ja | Meetmethode | | `measurement_device` | TEXT | Ja | Meetapparaat | | `measurement_period` | TEXT | Ja | Meetperiode | | `emission_point_id` | TEXT | Ja | Emissiepunt | | `process_unit` | TEXT | Ja | Proceseenheid | | `lab_report_number` | TEXT | Ja | Lab-rapportnummer | | `laboratory` | TEXT | Ja | Laboratorium | | `related_incident_id` | INTEGER FK | Ja | → incidents | | `related_substance_id` | INTEGER FK | Ja | → substances | | `alert_threshold` | REAL | Ja | Alert-drempel | | `notes` | TEXT | Ja | Opmerkingen | | `created_date` | DATETIME | Nee | Aanmaakdatum | | `updated_date` | DATETIME | Nee | Wijzigingsdatum | ### 4.16 `vbs_elements` | Kolom | Type | Nullable | Beschrijving | |--------------------|------------|----------|-------------------------------| | `id` | INTEGER PK | Nee | 1-7 | | `code` | TEXT | Nee | VBS-i t/m VBS-vii | | `name_nl` | TEXT | Nee | Elementnaam | | `description` | TEXT | Nee | Beschrijving | | `status` | TEXT | Ja | not_started / partial / compliant | | `compliance_pct` | REAL | Ja | Percentage compliant | | `assessor` | TEXT | Ja | Beoordelaar | | `last_assessment` | DATE | Ja | Laatste beoordeling | | `next_review` | DATE | Ja | Volgende review | | `notes` | TEXT | Ja | Opmerkingen | | `action_items` | TEXT (JSON) | Ja | Gekoppelde acties | ### 4.17 `compliance_deadlines` | Kolom | Type | Nullable | Beschrijving | |--------------------------|------------|----------|-------------------------------| | `id` | INTEGER PK | Nee | Auto-increment | | `title` | TEXT | Nee | Deadline-titel | | `description` | TEXT | Nee | Beschrijving | | `category` | TEXT | Nee | BRZO / Arbo / Seveso / etc. | | `status` | TEXT | Nee | open / in_progress / completed | | `due_date` | DATE | Nee | Uiterste datum | | `frequency` | TEXT | Ja | halfjaarlijks / kwartaal / etc. | | `owner` | TEXT | Ja | Verantwoordelijke | | `last_started` | DATETIME | Ja | Laatst gestart | | `last_completed` | DATETIME | Ja | Laatst afgerond | | `linked_actions_count` | INTEGER | Ja | Aantal gekoppelde acties | | `linked_actions_titles` | TEXT | Ja | Titels gekoppelde acties | | `created_at` | DATETIME | Nee | Aanmaakdatum | ### 4.18 `agent_tasks` | Kolom | Type | Nullable | Beschrijving | |--------------------|------------|----------|-------------------------------| | `id` | INTEGER PK | Nee | Auto-increment (20+) | | `agent_id` | TEXT | Nee | Agent-identificator | | `agent_name` | TEXT | Nee | Weergavenaam agent | | `task_description` | TEXT | Nee | Taakomschrijving | | `status` | TEXT | Nee | pending / running / completed / failed | | `workflow_id` | TEXT | Ja | Workflow-referentie | | `created_at` | DATETIME | Nee | Aanmaakdatum | | `completed_at` | DATETIME | Ja | Afrondingsdatum | ### 4.19 Overige Tabellen (Schema incompleet) De volgende tabellen zijn geïdentificeerd maar konden niet volledig worden gereconstrueerd: | Tabel | Bekende velden | Bron | |---------------------|-------------------------------------------------------------|-------------------------------| | `users` | `email`, `password` (hashed), `role` (user/admin) | Login-form [2] | | `companies` | `id`, `name` — minimaal; authenticatie vereist | Admin-routes (redirect) [2] | | `notifications` | `id`, `message`, `is_read`, `created_at` | `/api/notifications` [1] | | `alerts` | `id`, `title`, `summary`, `status`, `source`, `url`, `detected_at` | `/api/alerts` [1] | | `knowledge_documents`| `id`, `content` (RAG-based) | `/api/knowledge/chat` [8] | | `lms_certificates` | `id`, `employee_id`, `module_id`, `quiz_score`, `passed`, `certificate_ref`, `valid_until` | [7] | | `quickscan_results` | Onbekend | `/api/quickscan/history` (leeg)| | `brzo_arie` | Onbekend (leeg response) | `/api/brzo/arie` | --- ## 5. Relatie Diagram (ER) ``` ┌──────────────┐ ┌───────────────────┐ │ companies │1─────*│ users │ │ (id, name) │ │ (email, password, │ └──────────────┘ │ role, company_id)│ └───────────────────┘ ┌──────────────┐ ┌───────────────────┐ ┌────────────────────┐ │ employees │1─────*│ certifications │ │ training_programs │ │ (id, number,│ │ (employee_id, │*─────1│ (id, code, name) │ │ name, role)│ │ cert_type, │ └────────────────────┘ └──────┬───────┘ │ expiry_date) │ │ └───────────────────┘ │ │1─────*┌───────────────────┐ │ │ competencies │ │ │ (employee_id, │ │ │ competency, │ │ │ level, target) │ │ └───────────────────┘ │ │1─────*┌───────────────────┐ │ │ lms_certificates │ │ │ (employee_id, │ │ │ module_id, │ │ │ quiz_score) │ │ └───────────────────┘ │ │ ┌───────────────────┐ └──────*│ ptw_permits │ │ (assigned_emp_id)│ └───────────────────┘ ┌──────────────────┐ ┌───────────────────┐ │ scraper_sources │1───*│ scraper_runs │ │ (id TEXT PK, │ │ (source_id FK, │ │ tier, url) │ │ run_at, status) │ └──────────────────┘ └───────────────────┘ │ │1─────*┌───────────────────┐ ┌────────────────────┐ └──────*│ scraped_items │1─────*│ intelligence_ │ │ (source_id, │ │ alerts │ │ title, url) │ │ (scraped_item_id) │ └───────────────────┘ └────────────────────┘ │ │ │1─────*┌──────────────┐ │ └──────*│ intelligence_ │ │ │ actions │ │ │ (scraped_ │ │ │ item_id) │ │ └──────────────┘ │ │ ┌──────────────────┐ ┌───────────────────┐ │ │ risk_scenarios │1───*│ risk_controls │ │ │ (id, cas_number,│ │ (scenario_id FK, │ │ │ hazop_node) │ │ ahs_level, │ │ └──────────────────┘ │ effectiveness) │ │ │ └───────────────────┘ │ │ │ │1─────*┌──────────────┐ │ └──────*│ incidents │────────────────────┘ │ (related_ │ (scraped_item_id via intelligence) │ risk_ │ │ scenario_id)│ └──────────────┘ │ │1───*┌──────────────┐ └─────│ capa (leeg) │ └──────────────┘ ┌──────────────────┐ ┌───────────────────┐ │ moc_requests │ │ ptw_permits │ │ (related_risk_ │─────│ (related_ │ │ scenario_id) │ │ substance_id) │ └──────────────────┘ └───────────────────┘ ┌──────────────────┐ │ compliance_ │ ┌───────────────────┐ │ items │ │ compliance_ │ │ (id, category, │ │ deadlines │ │ status, │ │ (id, title, │ │ priority) │ │ due_date, │ └──────────────────┘ │ frequency) │ └───────────────────┘ ┌──────────────────┐ ┌───────────────────┐ │ vbs_elements │ │ compliance_gaps │ │ (id, code, │1───*│ (vbs_code, │ │ compliance_pct)│ │ requirement_id, │ └──────────────────┘ │ linked_action) │ └───────────────────┘ ┌──────────────────┐ ┌───────────────────┐ │ brzo_safety_ │ │ brzo_mapp │ │ reports │ │ (brzo_tier, │ │ (version, │ │ policy_text) │ │ deliverable_id)│ └───────────────────┘ └──────────────────┘ ┌──────────────────┐ ┌───────────────────┐ │ brzo_inspections│ │ agent_tasks │ │ (inspection_ │ │ (agent_id, │ │ type, items, │ │ status, │ │ score) │ │ description) │ └──────────────────┘ └───────────────────┘ ``` --- ## 6. Indexen (Afgeleid) Op basis van query-patronen in API-responsen worden de volgende indexen verwacht: | Tabel | Verwachte Index | Reden | |----------------------|------------------------------------------|------------------------------------| | `compliance_items` | `(status)`, `(category)`, `(priority)` | Filtering in compliance dashboard | | `incidents` | `(status)`, `(severity)`, `(incident_domain)`, `(date_occurred)` | Dashboard-aggregatie | | `scraper_runs` | `(source_id, run_at)` | Historie-per-bron queries | | `intelligence_alerts`| `(is_read)`, `(priority)`, `(scraped_item_id)` | Alert-filtering | | `intelligence_actions`| `(status)`, `(scraped_item_id)` | Actie-filtering | | `risk_scenarios` | `(cas_number)` | Stof-lookup | | `environment_metrics`| `(metric_type)`, `(compliance_status)` | Milieu-dashboard | | `compliance_deadlines`| `(status)`, `(due_date)` | Deadline-tracking | | `agent_tasks` | `(status)`, `(agent_id)` | Task-management | | `certifications` | `(employee_id)`, `(expiry_date)` | Gap-analyse | > **Let op:** Deze indexen zijn afgeleid, niet geverifieerd. De daadwerkelijke indexen kunnen alleen worden geïnspecteerd met directe DB-toegang. [6] --- ## 7. Multi-Tenancy Impact Analyse ### 7.1 Huidige Staat: Geen Tenant-Isolatie | Aspect | Observatie | Risico | |------------------------------|----------------------------------------------------------|--------------------| | Geen `company_id` in tabellen | Alle tabellen missen tenant-referentie | Kritiek — data-leak risico | | Single admin-gebruiker | Login-form kent email/password zonder tenant-context | Hoog | | API-routes zonder scope | `/api/compliance` retourneert álle items | Kritiek | | Scraper-data monolithisch | 29 bronnen, geen tenant-filter | Medium (global data) | | Admin-routes auth-only | `/admin/companies` redirect naar login; geen tenant CRUD | Hoog | ### 7.2 Tabellen die `company_id` Vereisen | Prioriteit | Tabellen | |------------|-----------------------------------------------------------------| | **P0** | `compliance_items`, `incidents`, `employees`, `contractors`, `moc_requests`, `ptw_permits`, `environment_metrics`, `compliance_deadlines`, `certifications`, `competencies` | | **P1** | `risk_scenarios`, `risk_controls`, `rie_sections`, `vbs_elements`, `brzo_safety_reports`, `brzo_mapp`, `brzo_inspections`, `agent_tasks` | | **P2** | `intelligence_alerts`, `intelligence_actions` (configuratie per tenant) | | **Global** | `scraper_sources`, `scraper_runs`, `scraped_items`, `knowledge_documents` | ### 7.3 Aanbevolen Multi-Tenancy Strategie | Strategie | Beschrijving | Geschikt voor | |----------------------------|------------------------------------------------------|----------------------| | **Shared DB, shared schema** | Eén database, `company_id` FK op alle rijen | Starters-SaaS (MVP) | | **Shared DB, separate schemas** | Eén PostgreSQL DB, schema per tenant | Middelgrote SaaS | | **Separate DBs per tenant** | Volledig geïsoleerde databases | Enterprise / BRZO | > **Aanbeveling:** Gezien de BRZO/Seveso-gevoeligheid van de data (HF, explosieve stoffen, ongevallen) is **Shared DB met `company_id` FK + Row-Level Security (PostgreSQL)** de beste balans tussen isolatie en beheersbaarheid. [9] --- ## 8. Data Typologie | Data-type | Voorbeelden | Globalisatie | Tabellen | |------------------------|--------------------------------------------------|--------------|------------------------------------| | **Wetgeving/Regelgeving** | Scraper-bronnen, PGS-15 maatregelen | ✅ Global | `scraper_sources`, `scraped_items` | | **Compliance-sjablonen** | VBS-elementen, BRZO-checklisten | ✅ Global | `vbs_elements`, `brzo_inspections` | | **Bedrijfsdata** | Medewerkers, contracten, incidenten | ❌ Tenant | `employees`, `incidents`, `moc_requests` | | **Training-content** | Toolbox talks, training-programma's | ⚠️ Gemengd | `training_programs`, `toolbox_talks` | | **Certificeringen** | VCA, HF-training, ATEX | ❌ Tenant | `certifications`, `lms_certificates` | | **Milieumetingen** | VOS, luchtuitstoot, afval | ❌ Tenant | `environment_metrics` | | **AI-output** | Chat-sessies, gap-analyses, agent-taken | ❌ Tenant | `agent_tasks`, `knowledge_documents`| --- ## 9. Bevestiging: READ-ONLY Analyse **Bevestigd:** Alle analyse is uitgevoerd via: - HTTP GET-requests naar publiek bereikbare API-endpoints - JSON-respons reverse engineering (schema reconstructie) - HTML-source analyse van server-side rendered pagina's **Er zijn GEEN wijzigingen aangebracht aan:** - Database records - Schema of structuur - Configuratiebestanden - Gebruikersdata of authenticatie --- ## 10. Bronverwijzingen | # | Bron | |----|-----------------------------------------------------------------------------------------------------------| | 1 | API-response `/api/compliance` — 49 compliance-items, veldstructuur geanalyseerd (2026-05-26 15:01 UTC) | | 2 | 404-response `/api/nonexistent-test` — Flask/Werkzeug signature; login-form HTML (2026-05-26 15:01 UTC) | | 3 | HTTP-headers via `curl -sI /api/compliance` — Cloudflare headers, geen DB-type zichtbaar | | 4 | API-respons `/api/scraper/status` — 29 bronnen, cron-schedule, embedded deployment patroon | | 5 | API-respons `/api/scraper/history` — run ID's tot ~2977, microsecond-timestamps | | 6 | Afleiding: Flask-stack + embedded deployment + string-datetimes → SQLite (75% waarschijnlijk) | | 7 | API-respons `/api/training/gap-analysis` — employee/certification data, contractor alerts | | 8 | API-respons `/api/knowledge/chat` — chatbot met session-ID, RAG-gebaseerd | | 9 | Multi-tenancy aanbeveling gebaseerd op BRZO/Seveso gevoeligheid (HF, ATEX, zware ongevallen data) | | 10 | API-respons `/api/incidents` — 2 incidenten, uitgebreid schema met 50+ velden | | 11 | API-respons `/api/rie/scenarios` — risk scenarios met embedded controls | | 12 | API-respons `/api/moc/requests`, `/api/ptw/permits` — MoC/PTW schema | | 13 | API-respons `/api/brzo/vbs-compliance` — 7 VBS-elementen | | 14 | API-respons `/api/compliance/deadlines` — 13+ deadlines | | 15 | API-respons `/api/training/fase2/certificates` — LMS-certificeringen | | 16 | API-respons `/api/agents/tasks` — 20+ AI-agent taken | | 17 | API-respons `/api/environment/metrics` — milieumetingen | --- ## 11. Verify — TierVerify Log | Controle-item | Resultaat | Notitie | |----------------------------------------------|-----------|--------------------------------------------------| | Live omgeving niet aangepast | ✅ PASS | Uitsluitend GET-requests uitgevoerd | | Geen database-mutaties | ✅ PASS | Geen POST/PUT/DELETE met payload | | Geen schema-wijzigingen | ✅ PASS | Read-only reverse engineering | | Geen migrations uitgevoerd | ✅ PASS | Geen toegang tot server | | Bronverwijzingen compleet | ✅ PASS | 17 bronnen gedocumenteerd | | Schema gebaseerd op feitelijke API-data | ✅ PASS | Alle velden uit JSON-respons afgeleid | | Onzekere claims gemarkeerd | ✅ PASS | DB-type als "waarschijnlijk", indexen als "afgeleid" | | Document-header aanwezig | ✅ PASS | Project, Type, Auteur, Versie, Datum, Status | | Output in deliverables/ met versietag | ✅ PASS | `phase1_database_analysis_v1.0.md` | | Multi-tenancy gaps feitelijk onderbouwd | ✅ PASS | P0/P1/P2 prioritering op data-gevoeligheid | --- *Einde Phase 1 Database Analyse Rapport — gereed voor volgende instructies.*