DiveLogix360 – Datenbankschema v4¶
Entity-Relationship-Diagramm aller Tabellen und ihrer Beziehungen.
Stand: April 2026 | 18 Tabellen | 8 ENUMs
Änderungen gegenüber v3:
- tenants um Schulprofil-Felder erweitert
- Neue Tabellen: notification_rules, notification_log, invitation_tokens
- Rollenbezeichnung vereinheitlicht: tenant_admin (war: admin)
erDiagram
tenants {
UUID id PK
VARCHAR slug UK
VARCHAR name
country_code country
plan_type plan_type
BOOLEAN is_active
TIMESTAMPTZ trial_ends_at
VARCHAR address
VARCHAR postal_code
VARCHAR city
VARCHAR phone_fixed
VARCHAR phone_mobile
VARCHAR contact_email
VARCHAR contact_person
VARCHAR vat_number
TEXT logo_path
VARCHAR default_currency
TIMESTAMPTZ created_at
TIMESTAMPTZ updated_at
UUID created_by FK
UUID updated_by FK
TIMESTAMPTZ deleted_at
UUID deleted_by FK
JSONB vector_clock
}
users {
UUID id PK
UUID tenant_id FK
VARCHAR email UK
VARCHAR username
user_role role
BOOLEAN tfa_enabled
BOOLEAN tfa_required
BOOLEAN is_active
TIMESTAMPTZ last_login_at
TIMESTAMPTZ created_at
TIMESTAMPTZ updated_at
UUID created_by FK
UUID updated_by FK
TIMESTAMPTZ deleted_at
UUID deleted_by FK
JSONB vector_clock
}
customers {
UUID id PK
UUID tenant_id FK
VARCHAR first_name
VARCHAR last_name
VARCHAR email
VARCHAR phone
BOOLEAN gdpr_consent
DATE gdpr_date
TIMESTAMPTZ gdpr_withdrawn_at
TIMESTAMPTZ retain_until
VARCHAR retain_reason
BOOLEAN is_archived
BOOLEAN pseudonymized
TIMESTAMPTZ created_at
TIMESTAMPTZ updated_at
UUID created_by FK
UUID updated_by FK
TIMESTAMPTZ deleted_at
UUID deleted_by FK
JSONB vector_clock
}
equipment {
UUID id PK
UUID tenant_id FK
UUID customer_id FK
equipment_type equipment_type
VARCHAR serial_number
VARCHAR manufacturer
VARCHAR model
INT year_of_manufacture
equipment_status status
TEXT photo_path
JSONB custom_data
TIMESTAMPTZ retain_until
VARCHAR retain_reason
BOOLEAN is_archived
BOOLEAN pseudonymized
TIMESTAMPTZ created_at
TIMESTAMPTZ updated_at
UUID created_by FK
UUID updated_by FK
TIMESTAMPTZ deleted_at
UUID deleted_by FK
JSONB vector_clock
}
cylinder_details {
UUID id PK
UUID equipment_id FK
UUID tenant_id FK
cylinder_material material
VARCHAR thread
DECIMAL pressure_bar
DECIMAL size_liters
VARCHAR valve_shape
VARCHAR valve_marking
DATE tuv_last
DATE tuv_next
VARCHAR norm_reference
VARCHAR inspection_body
VARCHAR inspector_id
TIMESTAMPTZ created_at
TIMESTAMPTZ updated_at
}
regulator_details {
UUID id PK
UUID equipment_id FK
UUID tenant_id FK
INT stages
VARCHAR din_or_yoke
DATE last_service
DATE next_service
INT service_interval_months
TIMESTAMPTZ created_at
TIMESTAMPTZ updated_at
}
usecase_workflows {
UUID id PK
UUID tenant_id FK
usecase_type usecase_type
UUID equipment_id FK
UUID customer_id FK
UUID assigned_to FK
workflow_status status
VARCHAR inspection_body
VARCHAR norm_reference
DATE result_date
DECIMAL cost_amount
VARCHAR cost_currency
BOOLEAN is_locked
TIMESTAMPTZ locked_at
UUID locked_by FK
VARCHAR lock_reason
UUID correction_of FK
TEXT correction_note
TIMESTAMPTZ retain_until
BOOLEAN is_archived
BOOLEAN pseudonymized
TIMESTAMPTZ created_at
TIMESTAMPTZ updated_at
UUID created_by FK
UUID updated_by FK
TIMESTAMPTZ deleted_at
JSONB vector_clock
}
workflow_history {
UUID id PK
UUID workflow_id FK
UUID tenant_id
UUID changed_by FK
workflow_status old_status
workflow_status new_status
TEXT notes
TIMESTAMPTZ changed_at
}
notification_rules {
UUID id PK
UUID tenant_id FK
equipment_type equipment_type
INT days_before
VARCHAR channel
TEXT message_template
BOOLEAN is_active
TIMESTAMPTZ created_at
TIMESTAMPTZ updated_at
}
notification_log {
UUID id PK
UUID tenant_id FK
UUID rule_id FK
UUID equipment_id FK
UUID customer_id FK
TIMESTAMPTZ sent_at
VARCHAR channel
VARCHAR status
}
invitation_tokens {
UUID id PK
UUID tenant_id FK
UUID customer_id FK
VARCHAR token UK
UUID invited_by FK
VARCHAR invited_email
TIMESTAMPTZ expires_at
TIMESTAMPTZ used_at
TIMESTAMPTZ created_at
}
sync_queue {
UUID id PK
UUID tenant_id FK
VARCHAR client_id
VARCHAR entity_type
UUID entity_id
VARCHAR operation
JSONB payload
TIMESTAMPTZ client_ts
TIMESTAMPTZ server_ts
BOOLEAN resolved
BOOLEAN conflict
TIMESTAMPTZ created_at
}
audit_log {
UUID id PK
UUID tenant_id
VARCHAR table_name
UUID record_id
VARCHAR operation
UUID changed_by FK
TIMESTAMPTZ changed_at
JSONB old_values
JSONB new_values
VARCHAR ip_address
}
auth_log {
UUID id PK
UUID tenant_id FK
UUID user_id FK
VARCHAR event
VARCHAR ip_address
BOOLEAN success
TIMESTAMPTZ created_at
}
deletion_log {
UUID id PK
UUID tenant_id
VARCHAR entity_type
UUID entity_id
UUID deleted_by FK
VARCHAR deletion_reason
TIMESTAMPTZ confirmed_at
JSONB data_snapshot
}
access_log {
UUID id PK
UUID tenant_id
UUID user_id FK
VARCHAR entity_type
UUID entity_id
VARCHAR action
TIMESTAMPTZ created_at
}
system_log {
UUID id PK
UUID tenant_id FK
VARCHAR event_type
VARCHAR severity
TEXT message
TIMESTAMPTZ created_at
}
customer_activity_log {
UUID id PK
UUID tenant_id
UUID customer_id FK
VARCHAR event_type
TIMESTAMPTZ created_at
}
tenants ||--o{ users : "hat"
tenants ||--o{ customers : "hat"
tenants ||--o{ equipment : "hat"
tenants ||--o{ usecase_workflows : "hat"
tenants ||--o{ sync_queue : "hat"
tenants ||--o{ auth_log : "hat"
tenants ||--o{ system_log : "hat"
tenants ||--o{ notification_rules : "konfiguriert"
tenants ||--o{ notification_log : "hat"
tenants ||--o{ invitation_tokens : "versendet"
customers ||--o{ equipment : "besitzt"
customers ||--o{ usecase_workflows : "hat"
customers ||--o{ customer_activity_log : "hat"
customers ||--o{ notification_log : "erhält"
customers ||--o{ invitation_tokens : "erhält"
equipment ||--|| cylinder_details : "UC01"
equipment ||--|| regulator_details : "UC02"
equipment ||--o{ usecase_workflows : "hat"
equipment ||--o{ notification_log : "löst aus"
notification_rules ||--o{ notification_log : "erzeugt"
users ||--o{ usecase_workflows : "zugewiesen"
users ||--o{ workflow_history : "geändert von"
users ||--o{ auth_log : "hat"
users ||--o{ audit_log : "geändert von"
users ||--o{ access_log : "hat"
users ||--o{ invitation_tokens : "versendet"
usecase_workflows ||--o{ workflow_history : "protokolliert"
usecase_workflows ||--o{ usecase_workflows : "korrigiert"
Normen & Compliance¶
| Norm / Gesetz | Relevanz |
|---|---|
| EN 1802:2002 | Wiederkehrende Inspektion Aluminiumflaschen |
| EN 1968:2002 | Wiederkehrende Inspektion Stahlflaschen |
| DGUV | Deutsche Unfallverhütungsvorschriften Tauchbereich (DE) |
| SVTI | Schweizerischer Verein für technische Inspektionen (CH) |
| EU-DSGVO | Datenschutz DE / AT |
| nDSG / DSV / VDSZ | Datenschutz CH (in Kraft seit 01.09.2023) |