c7651796c9
All checks were successful
CI/CD / go-lint (push) Has been skipped
CI/CD / python-lint (push) Has been skipped
CI/CD / nodejs-lint (push) Has been skipped
CI/CD / test-go-ai-compliance (push) Successful in 36s
CI/CD / test-python-backend-compliance (push) Successful in 36s
CI/CD / test-python-document-crawler (push) Successful in 22s
CI/CD / test-python-dsms-gateway (push) Successful in 18s
CI/CD / validate-canonical-controls (push) Successful in 12s
CI/CD / Deploy (push) Successful in 2s
Add dual-mode risk engine: legacy S×E×P (avoidance=0) and ISO mode S×F×P×A (avoidance>=1) with new thresholds (low/medium/high/very_high/not_acceptable). - 150+ hazard library entries across 28 categories incl. physical hazards (mechanical, electrical, thermal, pneumatic/hydraulic, noise/vibration, ergonomic, material/environmental) - 160-entry protective measures library with 3-step hierarchy validation (design → protective → information) - 25 lifecycle phases, 20 affected person roles, 50 evidence types - 10 verification methods (expanded from 7) - New API endpoints: lifecycle-phases, roles, evidence-types, protective-measures-library, validate-mitigation-hierarchy - DB migrations 018+019 for extended schema - Frontend: 4-slider risk assessment, hierarchy warnings, measures library modal - MkDocs wiki updated with ISO mode docs and legal notice (no norm text) All content uses original wording — norms referenced as methodology only. Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
59 lines
3.5 KiB
SQL
59 lines
3.5 KiB
SQL
-- Migration 018: ISO 12100 Machine Risk Model Extension for IACE
|
|
-- Adds lifecycle phases, extended hazard fields, and protective measures metadata.
|
|
|
|
-- ============================================================================
|
|
-- 1. Extend iace_hazards with ISO 12100 fields
|
|
-- ============================================================================
|
|
|
|
ALTER TABLE iace_hazards ADD COLUMN IF NOT EXISTS machine_module TEXT DEFAULT '';
|
|
ALTER TABLE iace_hazards ADD COLUMN IF NOT EXISTS function TEXT DEFAULT '';
|
|
ALTER TABLE iace_hazards ADD COLUMN IF NOT EXISTS lifecycle_phase TEXT DEFAULT '';
|
|
ALTER TABLE iace_hazards ADD COLUMN IF NOT EXISTS hazardous_zone TEXT DEFAULT '';
|
|
ALTER TABLE iace_hazards ADD COLUMN IF NOT EXISTS trigger_event TEXT DEFAULT '';
|
|
ALTER TABLE iace_hazards ADD COLUMN IF NOT EXISTS affected_person TEXT DEFAULT '';
|
|
ALTER TABLE iace_hazards ADD COLUMN IF NOT EXISTS possible_harm TEXT DEFAULT '';
|
|
ALTER TABLE iace_hazards ADD COLUMN IF NOT EXISTS sub_category TEXT DEFAULT '';
|
|
ALTER TABLE iace_hazards ADD COLUMN IF NOT EXISTS review_status TEXT DEFAULT 'draft';
|
|
|
|
-- ============================================================================
|
|
-- 2. Extend iace_hazard_library with ISO 12100 metadata
|
|
-- ============================================================================
|
|
|
|
ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS sub_category TEXT DEFAULT '';
|
|
ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS default_exposure INT DEFAULT 3;
|
|
ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS default_avoidance INT DEFAULT 3;
|
|
ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS typical_causes JSONB DEFAULT '[]';
|
|
ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS typical_harm TEXT DEFAULT '';
|
|
ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS relevant_lifecycle_phases JSONB DEFAULT '[]';
|
|
ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS recommended_measures_design JSONB DEFAULT '[]';
|
|
ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS recommended_measures_technical JSONB DEFAULT '[]';
|
|
ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS recommended_measures_information JSONB DEFAULT '[]';
|
|
ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS suggested_evidence JSONB DEFAULT '[]';
|
|
ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS related_keywords JSONB DEFAULT '[]';
|
|
|
|
-- ============================================================================
|
|
-- 3. Reference table: Lifecycle phases (DE/EN labels)
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS iace_lifecycle_phases (
|
|
id TEXT PRIMARY KEY,
|
|
label_de TEXT NOT NULL,
|
|
label_en TEXT NOT NULL,
|
|
sort_order INT NOT NULL DEFAULT 0
|
|
);
|
|
|
|
INSERT INTO iace_lifecycle_phases (id, label_de, label_en, sort_order) VALUES
|
|
('transport', 'Transport', 'Transport', 1),
|
|
('assembly', 'Montage', 'Assembly', 2),
|
|
('commissioning', 'Inbetriebnahme', 'Commissioning', 3),
|
|
('setup_teach', 'Einrichten / Teach', 'Setup / Teach', 4),
|
|
('normal_operation', 'Normalbetrieb', 'Normal Operation', 5),
|
|
('special_operation', 'Sonderbetrieb', 'Special Operation', 6),
|
|
('cleaning', 'Reinigung', 'Cleaning', 7),
|
|
('maintenance', 'Wartung', 'Maintenance', 8),
|
|
('fault_clearing', 'Stoerungsbeseitigung', 'Fault Clearing', 9),
|
|
('changeover', 'Umruestung', 'Changeover', 10),
|
|
('decommissioning', 'Ausserbetriebnahme', 'Decommissioning', 11),
|
|
('disposal', 'Demontage / Entsorgung', 'Dismantling / Disposal', 12)
|
|
ON CONFLICT (id) DO NOTHING;
|