Database

The Cloud Backend uses Azure SQL Server with sqlx for all data access. There is no ORM — queries are written as raw SQL with named struct scanning.

Migrations

Migrations are managed by Flyway and live in Backend/db/migration/. They run automatically on deploy.

Migration	Description
V1	Base schema — all core tables
V10	CVE feed catalog
V11	Vuln scanner — drop tenant API keys
V12	Vuln scanner — drop feed sync enabled
V13	Asset registry vendor
V14	Disable tenant RLS (RLS policy definition)
V15	User settings appearance
V16	Refresh tokens table
V17	Tenant features table
V18	Assets vendor → producer rename
V19	CVE scores cache
V20	ExploitDB catalog
V21	Edge snap updates
V22	Edge device control
V23	Edge ingest events
V24	Onboarding OT completed flag
V25	Knowledge article → problem links
V26	Zones/VLANs site_id
V27	Network hierarchy links
V28	Drop diagram-related change_id

Run migrations locally with Flyway CLI or via the CI/CD pipeline post-deploy step.

Row models

All database row types are defined in Backend/internal/db/models.go. Domain packages define their own richer types that embed or transform these models.

Core entity ERD

erDiagram
    tenants {
        uuid id PK
        string name
        string status
        timestamp created_at
    }
    users {
        uuid id PK
        uuid tenant_id FK
        string email
        string password_hash
        bool mfa_enabled
        timestamp created_at
    }
    groups {
        uuid id PK
        uuid tenant_id FK
        string name
    }
    user_groups {
        uuid user_id FK
        uuid group_id FK
    }
    assets {
        uuid id PK
        uuid tenant_id FK
        string name
        string type
        uuid site_id FK
        timestamp created_at
    }
    sites {
        uuid id PK
        uuid tenant_id FK
        string name
    }
    incidents {
        uuid id PK
        uuid tenant_id FK
        string title
        string status
        string severity
        timestamp sla_response_due
        timestamp sla_resolve_due
        timestamp created_at
    }
    problems {
        uuid id PK
        uuid tenant_id FK
        string title
        string status
        string root_cause
    }
    changes {
        uuid id PK
        uuid tenant_id FK
        string title
        string status
        string type
        string risk
    }
    vulnerabilities {
        uuid id PK
        uuid tenant_id FK
        string cve_id
        float cvss_score
        string status
        uuid asset_id FK
    }
    audit_logs {
        uuid id PK
        uuid tenant_id FK
        uuid user_id FK
        string action
        string resource_type
        string resource_id
        timestamp created_at
    }
    tenant_licenses {
        uuid tenant_id FK
        string plan
        timestamp expires_at
    }
    tenant_features {
        uuid tenant_id FK
        string feature
        bool enabled
    }

    tenants ||--o{ users : "has"
    tenants ||--o{ groups : "has"
    tenants ||--o{ assets : "owns"
    tenants ||--o{ incidents : "owns"
    tenants ||--o{ problems : "owns"
    tenants ||--o{ changes : "owns"
    tenants ||--o{ vulnerabilities : "owns"
    tenants ||--o{ audit_logs : "generates"
    tenants ||--|| tenant_licenses : "has"
    tenants ||--o{ tenant_features : "has"
    users }o--o{ groups : "belongs to (user_groups)"
    assets }o--|| sites : "located at"
    vulnerabilities }o--|| assets : "affects"

Connection configuration

Env var	Purpose
DATABASE_URL	Local/fallback: sqlserver://user:pass@host:port?database=...
SQL_SERVER	Azure SQL server hostname (takes precedence over DATABASE_URL)
SQL_DATABASE	Azure SQL database name
AZURE_CLIENT_ID	User-assigned Managed Identity client ID (omit for system-assigned)
IS_DEV=true	Forces DATABASE_URL (SQL auth) even when SQL_SERVER is set

In production, the backend connects using Azure Managed Identity — no password in environment variables.

Local development

For local dev, run SQL Server in Docker:

docker run -e ACCEPT_EULA=Y -e SA_PASSWORD=<password> \
  -p 1433:1433 mcr.microsoft.com/mssql/server:2022-latest

Then set DATABASE_URL=sqlserver://sa:<password>@localhost:1433?database=monozu&encrypt=disable in .env.

Flyway applies migrations on first run. On a fresh DB, run Flyway against the local instance:

cd Backend
flyway -url="jdbc:sqlserver://localhost:1433;databaseName=monozu;encrypt=false" \
       -user=sa -password=<password> migrate