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 --------------------------- .. mermaid:: erDiagram Institution ||--|{ Scan : has Scan ||--|{ ScanResult : contains Scan ||--o{ Dispute : has Scan ||--o{ ReportAccessLog : "access tracked in" Host ||--|{ ScanResult : "found on" Vulnerability ||--|{ ScanResult : "is instance of" Product ||--o{ ScanResult : "related to" Source ||--o{ ScanResult : "found by" Location ||--o{ ScanResult : "found at" User ||--o{ UserLoginLog : "logs in" User |o--o| ContactPerson : "linked to" User ||--o{ AuditLog : "initiates" ContactPerson ||--o{ ContactPersonIP : "assigned" ContactPerson ||--o{ ContactPersonCIDR : "assigned" ContactPerson ||--o{ FacilityMapping : "mapped to" ContactPerson ||--o{ ContactPersonChange : "history" Institution { int id PK string name } 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 } Product { int id PK string name } Source { int id PK string source_name string source_oid } Location { int id PK string name } ScanResult { int id PK int scan_id FK int host_id FK int vulnerability_id FK int product_id FK int source_id FK int location_id FK float severity } User { int id PK string username string hashed_password boolean is_admin string full_name string email } 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 } 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 ^^^^^^^^^^^^ Groups scans by organizational unit. .. code-block:: sql CREATE TABLE institutions ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL UNIQUE ); scans ^^^^^ Represents a single imported scan report file. .. code-block:: sql CREATE TABLE scans ( id SERIAL PRIMARY KEY, report_id VARCHAR(255) NOT NULL UNIQUE, 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 ); hosts ^^^^^ Unique hosts identified by IP address. .. code-block:: sql 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). .. code-block:: sql 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 ); products ^^^^^^^^ Software products identified in scans. .. code-block:: sql CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(512) NOT NULL UNIQUE ); sources ^^^^^^^ Scan sources (e.g. scanner modules). .. code-block:: sql CREATE TABLE sources ( id SERIAL PRIMARY KEY, source_name VARCHAR(255) NOT NULL, source_oid VARCHAR(255) NOT NULL ); locations ^^^^^^^^^ Locations of findings (e.g. ports). .. code-block:: sql CREATE TABLE locations ( id SERIAL PRIMARY KEY, name VARCHAR(512) NOT NULL UNIQUE ); scan_results ^^^^^^^^^^^^ The central fact table linking findings to hosts and scans. .. code-block:: sql 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, product_id INTEGER REFERENCES products(id), source_id INTEGER REFERENCES sources(id), location_id INTEGER REFERENCES locations(id), severity NUMERIC(3, 1) ); User Management Tables ---------------------- users ^^^^^ Application users with login credentials. .. code-block:: sql 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 ); contact_persons ^^^^^^^^^^^^^^^ Individuals responsible for network assets. .. code-block:: sql 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. .. code-block:: sql 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. .. code-block:: sql 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. .. code-block:: sql 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. .. code-block:: sql 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. .. code-block:: sql 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() ); audit_logs ^^^^^^^^^^ System-wide audit trail for critical actions. .. code-block:: sql 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. .. code-block:: sql 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. .. code-block:: sql 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. .. code-block:: sql 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. .. code-block:: sql CREATE TABLE filename_patterns ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, pattern VARCHAR(255) NOT NULL, description TEXT, priority INTEGER DEFAULT 0, active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT NOW(), updated_at TIMESTAMP DEFAULT NOW() );