Data model

PostgreSQL. UUID primary keys (uuid_generate_v4()) except two small lookup tables that use integers. GORM maps every table to a struct; API responses go through separate DTOs, never the raw struct.

Core entities

Tenant — an organization using the platform. slug (used for subdomain resolution), ingest_key (issued once, used by nothing sensitive today but reserved for future machine-to-machine ingestion), is_active, plan_tier.

User — login identity. Email, password hash, optional Google/Microsoft OAuth ids, verification and password-reset tokens, lockout counters. (tenant_id, email) is a composite unique index — the same email can exist once per tenant, not once globally.

Employee — the person behind a User, inside an org chart. Name, department, position, role (admin / safety_officer / manager / employee), a self-referencing reporting_manager_id. One Employee per User.

Department — a small integer-keyed lookup table (Administration, Operations, Maintenance, and so on).

Incident — something that happened: an injury, near-miss, property damage, environmental or security event. Type, severity, status (new → investigating → action_required → resolved → closed), location, a sequential reference number (INC00001), reporter and optional assignee, and JSONB fields for witnesses, environmental conditions, and equipment involved.

Investigation — a formal inquiry into one Incident (one-to-one: unique on (tenant_id, incident_id)). Lead investigator, root cause, contributing factors (JSONB), findings, recommendations, status.

InvestigationInterview / InvestigationEvidence — child records of an Investigation: scheduled interviews with a status and location; collected evidence with a type, description, and storage reference.

CorrectiveAction — a remediation tied to an Incident. Type (corrective/preventive), priority, status (pending → in_progress → completed → verified, or overdue), assignee, due date, and an extension-request workflow (previous due date, reason, requester, approval status).

ActionUpdate — a timestamped progress note against a CorrectiveAction.

Hazard — a reported unsafe condition or act, independent of any specific incident. Type, risk level, status, department, its own sequential reference number (HAZ00001).

SafetyObservation (the "VPC" model in code) — a lightweight field observation: what was seen, department, type (unsafe act/condition), action taken on the spot, its own sequential reference number. Attachments hang off it the same way they do off incidents and hazards.

Notification — an in-app + email notification fanned out to a single user, with a type, title, message, and a polymorphic reference (reference_id + reference_type) back to whatever triggered it.

NotificationSettings — per-user delivery preferences.

AuditLog — before/after JSONB snapshots of writes to sensitive tables, with the acting user and IP.

TenantSequence — (tenant_id, kind) → counter. Built to back per-tenant reference-number generation; currently only populated during tenant onboarding, not yet consulted by the number-generating hooks (see architecture.md, "what I'd change now").

Multi-tenancy shape

Every tenant-owned table (23 of them) carries a nullable-by-design tenant_id column, NOT NULL on all but a few that predate the rollout. Uniqueness that used to be global is now a composite index scoped by tenant — for example incident reference numbers are unique per tenant, not system-wide ((tenant_id, reference_number)), so two tenants can each have their own INC00001. See multi-tenancy.md for how tenant_id gets set and enforced.

Entity relationships

erDiagram
    TENANT ||--o{ USER : owns
    TENANT ||--o{ EMPLOYEE : owns
    TENANT ||--o{ INCIDENT : owns
    TENANT ||--o{ HAZARD : owns
    TENANT ||--o{ SAFETY_OBSERVATION : owns
    TENANT ||--|| TENANT_SEQUENCE : has

    USER ||--o| EMPLOYEE : "is"
    EMPLOYEE }o--o{ EMPLOYEE : "reports to"
    EMPLOYEE }o--|| DEPARTMENT : "belongs to"

    EMPLOYEE ||--o{ INCIDENT : reports
    EMPLOYEE ||--o{ INCIDENT : "is assigned"
    INCIDENT ||--o| INVESTIGATION : "investigated by"
    INVESTIGATION ||--o{ INVESTIGATION_INTERVIEW : includes
    INVESTIGATION ||--o{ INVESTIGATION_EVIDENCE : includes
    INCIDENT ||--o{ CORRECTIVE_ACTION : requires
    CORRECTIVE_ACTION ||--o{ ACTION_UPDATE : logs

    EMPLOYEE ||--o{ HAZARD : reports
    HAZARD }o--o| DEPARTMENT : "assigned to"

    EMPLOYEE ||--o{ SAFETY_OBSERVATION : files

    USER ||--o{ NOTIFICATION : receives
    USER ||--o| NOTIFICATION_SETTINGS : configures

    TENANT {
        uuid id PK
        string slug UK
        string ingest_key
        bool is_active
        string plan_tier
    }
    USER {
        uuid id PK
        uuid tenant_id FK
        string email
        string password_hash
        bool is_verified
    }
    EMPLOYEE {
        uuid id PK
        uuid tenant_id FK
        uuid user_id FK
        string employee_number
        string role
        uuid reporting_manager_id FK
    }
    INCIDENT {
        uuid id PK
        uuid tenant_id FK
        string reference_number
        string type
        string severity_level
        string status
        uuid reported_by FK
        uuid assigned_to FK
    }
    INVESTIGATION {
        uuid id PK
        uuid tenant_id FK
        uuid incident_id FK
        uuid lead_investigator_id FK
        string status
    }
    CORRECTIVE_ACTION {
        uuid id PK
        uuid tenant_id FK
        uuid incident_id FK
        uuid assigned_to FK
        string status
        timestamp due_date
    }
    HAZARD {
        uuid id PK
        uuid tenant_id FK
        string reference_number
        string risk_level
        string status
    }
    SAFETY_OBSERVATION {
        uuid id PK
        uuid tenant_id FK
        string reference_number
        string type
    }
    NOTIFICATION {
        uuid id PK
        uuid tenant_id FK
        uuid user_id FK
        string type
        uuid reference_id
        string reference_type
    }
    TENANT_SEQUENCE {
        uuid tenant_id PK, FK
        string kind PK
        bigint counter
    }