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
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.
CREATE TABLE institutions (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL UNIQUE
);
scans
Represents a single imported scan report file.
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.
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
);
products
Software products identified in scans.
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(512) NOT NULL UNIQUE
);
sources
Scan sources (e.g. scanner modules).
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).
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.
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.
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.
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()
);
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,
priority INTEGER DEFAULT 0,
active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);