Database Schema

This document provides a comprehensive reference of the database schema used in the Vulnerability Scanner. It is derived from the SQLAlchemy models which serve as the source of truth for the application.

Entity Relationship Diagram

        erDiagram
    Institution ||--|{ Scan : has
    Institution ||--o{ InstitutionIP : "mapped to"
    Institution ||--o{ InstitutionCIDR : "mapped to"
    Scan ||--|{ ScanResult : contains
    Scan ||--o{ Dispute : has
    Scan ||--o{ ReportAccessLog : "access tracked in"
    Host ||--|{ ScanResult : "found on"
    Vulnerability ||--|{ ScanResult : "is instance of"
    User |o--o| ContactPerson : "linked to"
    User ||--o{ UserLoginLog : "logs in"
    User ||--o{ AuditLog : "initiates"
    User ||--o{ ContactPersonChange : "records change"
    User ||--o{ ReportAccessLog : "forwards report"
    ContactPerson ||--o{ ContactPersonIP : "assigned"
    ContactPerson ||--o{ ContactPersonCIDR : "assigned"
    ContactPerson ||--o{ FacilityMapping : "mapped to"
    ContactPerson ||--o{ ContactPersonChange : "history"

    Institution {
        int id PK
        string name
    }
    InstitutionIP {
        int id PK
        int institution_id FK
        string ip_address
    }
    InstitutionCIDR {
        int id PK
        int institution_id FK
        string cidr_range
    }
    Scan {
        int id PK
        string report_id
        int institution_id FK
        datetime scan_date
        string file_path
        string file_hash
        string target_ip
        datetime last_accessed_at
    }
    Host {
        int id PK
        string ip_address
        string hostname
        string os_info
    }
    Vulnerability {
        int id PK
        string nvt_id
        string name
        string cve_id
        float severity
        text description
        text solution
    }
    ScanResult {
        int id PK
        int scan_id FK
        int host_id FK
        int vulnerability_id FK
        string host_ip
        string host_hostname
        string vulnerability_name
        string vulnerability_nvt_id
        string vulnerability_cve_id
        float severity
        string institution_name
        string product_name
        string source_name
        string source_oid
        string location_name
    }
    JobStatus {
        int id PK
        string job_id
        string status
        string message
        string file_path
        datetime created_at
        datetime updated_at
    }
    User {
        int id PK
        string username
        string hashed_password
        boolean is_admin
        string full_name
        string email
        string auth_provider
        string ldap_dn
    }
    ContactPerson {
        int id PK
        string name
        string email
        int user_id FK
        datetime created_at
        datetime updated_at
    }
    ContactPersonIP {
        int id PK
        int contact_person_id FK
        string ip_address
    }
    ContactPersonCIDR {
        int id PK
        int contact_person_id FK
        string cidr_range
    }
    FacilityMapping {
        int id PK
        string facility_name
        int contact_person_id FK
        datetime created_at
        datetime updated_at
    }
    Dispute {
        int id PK
        int scan_id FK
        text reason
        string suggested_email
        string status
        string dispute_type
        datetime created_at
        datetime resolved_at
    }
    ReportAccessLog {
        int id PK
        int scan_id FK
        string ip_address
        text user_agent
        datetime accessed_at
        int forwarded_by FK
        string forwarded_to
    }
    AuditLog {
        int id PK
        datetime timestamp
        int user_id FK
        string user_email
        string ip_address
        string event_type
        string target_id
        string target_type
        json details
    }
    UserLoginLog {
        int id PK
        int user_id FK
        datetime login_time
        string ip_address
        string user_agent
        boolean success
    }
    ContactPersonChange {
        int id PK
        int contact_person_id FK
        string field_name
        text old_value
        text new_value
        int changed_by_user_id FK
        datetime changed_at
        string change_source
    }
    EmailTemplate {
        int id PK
        string name
        string subject
        text body
        datetime last_updated
        string usage_type
    }
    FilenamePattern {
        int id PK
        string name
        string pattern
        text description
        int priority
        boolean active
        datetime created_at
        datetime updated_at
    }
    

Core Data Tables

institutions

Stores the canonical institution names used by scan metadata and IP/CIDR mappings.

CREATE TABLE institutions (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE
);

institution_ips

Exact IP-to-institution mappings used during import and dashboard reporting.

CREATE TABLE institution_ips (
    id SERIAL PRIMARY KEY,
    institution_id INTEGER NOT NULL REFERENCES institutions(id) ON DELETE CASCADE,
    ip_address VARCHAR(45) NOT NULL UNIQUE
);

institution_cidrs

CIDR-to-institution mappings used when no exact IP mapping exists.

CREATE TABLE institution_cidrs (
    id SERIAL PRIMARY KEY,
    institution_id INTEGER NOT NULL REFERENCES institutions(id) ON DELETE CASCADE,
    cidr_range VARCHAR(45) NOT NULL UNIQUE
);

scans

Represents a single imported scan report file.

CREATE TABLE scans (
    id SERIAL PRIMARY KEY,
    report_id VARCHAR(255) NOT NULL,
    institution_id INTEGER NOT NULL REFERENCES institutions(id),
    scan_date TIMESTAMP NOT NULL,
    file_path VARCHAR(1024) NOT NULL,
    file_hash VARCHAR(64),
    target_ip VARCHAR(128),
    last_accessed_at TIMESTAMP
);

report_file_matches

Links a visual report (PDF/HTML) to the master XML scan. host_ip is set for per-host visuals and left NULL only for an explicit master visual link that uses all host IPs in the XML for notifications and access authorization.

CREATE TABLE report_file_matches (
    id SERIAL PRIMARY KEY,
    visual_scan_id INTEGER NOT NULL UNIQUE REFERENCES scans(id) ON DELETE CASCADE,
    xml_scan_id INTEGER REFERENCES scans(id) ON DELETE SET NULL,
    host_ip VARCHAR(128),
    match_status VARCHAR(32) NOT NULL,
    match_method VARCHAR(64),
    match_reason TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

scan_match_key_cache

Stores precomputed matching keys for XML and visual scans so manual visual-to-XML candidate lookups do not need to reparse large XML files while an admin opens a dispute dialog.

CREATE TABLE scan_match_key_cache (
    scan_id INTEGER PRIMARY KEY REFERENCES scans(id) ON DELETE CASCADE,
    scan_type VARCHAR(16) NOT NULL,
    file_path VARCHAR(1024) NOT NULL,
    file_hash VARCHAR(64),
    cache_version INTEGER NOT NULL DEFAULT 1,
    ids_json TEXT NOT NULL,
    ips_json TEXT NOT NULL,
    hostnames_json TEXT NOT NULL,
    dates_json TEXT NOT NULL,
    names_json TEXT NOT NULL,
    family_keys_json TEXT NOT NULL,
    base_keys_json TEXT NOT NULL,
    parsed_family VARCHAR(255),
    parsed_ip VARCHAR(128),
    parsed_date VARCHAR(32),
    pattern_id INTEGER,
    pattern_name VARCHAR(255),
    host_ip_count INTEGER NOT NULL DEFAULT 0,
    host_ip_sample_json TEXT NOT NULL,
    key_search_text TEXT NOT NULL,
    generated_at TIMESTAMP DEFAULT NOW()
);

hosts

Unique hosts identified by IP address.

CREATE TABLE hosts (
    id SERIAL PRIMARY KEY,
    ip_address VARCHAR(128) NOT NULL UNIQUE,
    hostname VARCHAR(255),
    os_info VARCHAR(255)
);

vulnerabilities

Unique vulnerability definitions (NVTs).

CREATE TABLE vulnerabilities (
    id SERIAL PRIMARY KEY,
    nvt_id VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(512) NOT NULL,
    cve_id VARCHAR(255),
    severity NUMERIC(3, 1) NOT NULL,
    description TEXT,
    solution TEXT
);

scan_results

The central fact table linking findings to scans while storing the snapshot fields that are used most often in reporting queries.

CREATE TABLE scan_results (
    id SERIAL PRIMARY KEY,
    scan_id INTEGER NOT NULL REFERENCES scans(id) ON DELETE CASCADE,
    host_id INTEGER NOT NULL REFERENCES hosts(id) ON DELETE CASCADE,
    vulnerability_id INTEGER NOT NULL REFERENCES vulnerabilities(id) ON DELETE CASCADE,
    host_ip VARCHAR(128) NOT NULL,
    host_hostname VARCHAR(255),
    vulnerability_name VARCHAR(512) NOT NULL,
    vulnerability_nvt_id VARCHAR(255) NOT NULL,
    vulnerability_cve_id VARCHAR(255),
    severity NUMERIC(3, 1),
    institution_name VARCHAR(255),
    product_name VARCHAR(512),
    source_name VARCHAR(255),
    source_oid VARCHAR(255),
    location_name VARCHAR(512)
);

job_statuses

Tracks asynchronous import and processing jobs.

CREATE TABLE job_statuses (
    id SERIAL PRIMARY KEY,
    job_id VARCHAR(36) NOT NULL UNIQUE,
    status VARCHAR(50) NOT NULL,
    message TEXT,
    file_path VARCHAR(512),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

User Management Tables

users

Application users with login credentials.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(128) UNIQUE,
    hashed_password VARCHAR(1024),
    is_admin BOOLEAN DEFAULT FALSE,
    full_name VARCHAR(255),
    email VARCHAR(255) UNIQUE,
    auth_provider VARCHAR(32) DEFAULT 'local',
    ldap_dn VARCHAR(512) UNIQUE
);

auth_provider identifies whether the account authenticates locally or via LDAP. LDAP users store their directory DN in ldap_dn and keep administrative privileges disabled unless an administrator promotes them manually.

contact_persons

Individuals responsible for network assets.

CREATE TABLE contact_persons (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

contact_person_ips

Specific IPs assigned to a contact person.

CREATE TABLE contact_person_ips (
    id SERIAL PRIMARY KEY,
    contact_person_id INTEGER NOT NULL REFERENCES contact_persons(id) ON DELETE CASCADE,
    ip_address VARCHAR(45) NOT NULL UNIQUE
);

contact_person_cidrs

Network ranges assigned to a contact person.

CREATE TABLE contact_person_cidrs (
    id SERIAL PRIMARY KEY,
    contact_person_id INTEGER NOT NULL REFERENCES contact_persons(id) ON DELETE CASCADE,
    cidr_range VARCHAR(45) NOT NULL UNIQUE
);

facility_mappings

Maps external facility names to contact persons.

CREATE TABLE facility_mappings (
    id SERIAL PRIMARY KEY,
    facility_name VARCHAR(255) NOT NULL UNIQUE,
    contact_person_id INTEGER NOT NULL REFERENCES contact_persons(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

Operations & Audit Tables

disputes

Tracks user disputes regarding validity or ownership.

CREATE TABLE disputes (
    id SERIAL PRIMARY KEY,
    scan_id INTEGER NOT NULL REFERENCES scans(id),
    reason TEXT NOT NULL,
    suggested_email VARCHAR(255),
    status VARCHAR(32) DEFAULT 'OPEN', -- OPEN, RESOLVED, IGNORED
    dispute_type VARCHAR(32), -- UNASSIGNED_IP, MISSING_XML, USER_DISPUTE
    created_at TIMESTAMP DEFAULT NOW(),
    resolved_at TIMESTAMP
);

report_access_logs

Tracks when reports are accessed via magic links.

CREATE TABLE report_access_logs (
    id SERIAL PRIMARY KEY,
    scan_id INTEGER NOT NULL REFERENCES scans(id) ON DELETE CASCADE,
    ip_address VARCHAR(45),
    user_agent TEXT,
    accessed_at TIMESTAMP DEFAULT NOW(),
    forwarded_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    forwarded_to VARCHAR(255)
);

audit_logs

System-wide audit trail for critical actions.

CREATE TABLE audit_logs (
    id SERIAL PRIMARY KEY,
    timestamp TIMESTAMP WITHOUT TIME ZONE DEFAULT (now() AT TIME ZONE 'utc'),
    -- Actor
    user_id INTEGER REFERENCES users(id),
    user_email VARCHAR(255),
    ip_address VARCHAR(45),
    -- Action
    event_type VARCHAR(50) NOT NULL,
    -- Target
    target_id VARCHAR(255),
    target_type VARCHAR(50),
    -- Details
    details JSON
);

user_login_logs

Tracks user authentication attempts.

CREATE TABLE user_login_logs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    login_time TIMESTAMP NOT NULL DEFAULT NOW(),
    ip_address VARCHAR(45),
    user_agent VARCHAR(500),
    success BOOLEAN DEFAULT TRUE
);

contact_person_changes

History of modifications to contact person details.

CREATE TABLE contact_person_changes (
    id SERIAL PRIMARY KEY,
    contact_person_id INTEGER NOT NULL REFERENCES contact_persons(id) ON DELETE CASCADE,
    field_name VARCHAR(100) NOT NULL,
    old_value TEXT,
    new_value TEXT,
    changed_by_user_id INTEGER REFERENCES users(id),
    changed_at TIMESTAMP DEFAULT NOW(),
    change_source VARCHAR(50) DEFAULT 'manual'
);

Configuration Tables

email_templates

Customizable templates for notifications.

CREATE TABLE email_templates (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    subject VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    last_updated TIMESTAMP DEFAULT NOW(),
    usage_type VARCHAR(50) UNIQUE
);

filename_patterns

Regex patterns for parsing metadata from filenames.

CREATE TABLE filename_patterns (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    pattern VARCHAR(255) NOT NULL,
    description TEXT,
    applies_to VARCHAR(16) DEFAULT 'all',
    priority INTEGER DEFAULT 0,
    active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);