Database Schema Reference

Auto-generated — do not edit. Re-run scripts/generate-db-erd.ts to update.

Summary Stats

• Total entities: 26
• Database: PostgreSQL (TypeORM)
• Tenant isolation: Schema-per-tenant (search_path)
• Generated: 2026-04-05T13:06:07.159Z

Entity Catalog

Entity Class	Table Name	Module	Description
ApiAuditLog	api_audit_logs	Administration & Auth	—
IpBlacklist	ip_blacklist	Administration & Auth	—
IpWhitelist	ip_whitelist	Administration & Auth	—
Role	roles	Administration & Auth	—
SavedFilter	saved_filters	Administration & Auth	SavedFilter — User-scoped saved filter presets for Explore page Each user can sa
SecurityAuditLog	security_audit_logs	Administration & Auth	—
Settings	settings	Administration & Auth	—
TokenBlacklist	token_blacklist	Administration & Auth	—
User	users	Administration & Auth	—
AnomalyDay	anomaly_days	AI Labs	AnomalyDay tracks days with detected data anomalies (spikes) Used for: - KPI nor
Asset	assets	Exposure Management	Asset entity for storing vulnerability management asset inventory. Supports mult
ScanHistoryImport	scan_history_imports	Exposure Management	Tracks individual scan-run imports. Each row represents one Tenable scan history
ScanImportJob	scan_import_jobs	Exposure Management	Tracks bulk scan history import jobs. Each row represents one admin-initiated im
SyncJob	sync_jobs	Exposure Management	—
SyncLog	sync_logs	Exposure Management	Vulnerability entity for storing Tenable.io vulnerability data This allows viewi
Vulnerability	vulnerabilities	Exposure Management	Vulnerability entity for storing Tenable.io vulnerability data This allows viewi
VulnerabilityStateTransition	vulnerability_state_transitions	Exposure Management	VulnerabilityStateTransition tracks state changes over time Enhanced to support
VulnerabilityStats	vulnerability_stats	Exposure Management	VulnerabilityStats entity for pre-computed daily aggregations This allows fast d
VulnerabilityStatsByCategory	vulnerability_stats_by_category	Exposure Management	VulnerabilityStatsByCategory stores daily per-category aggregations This enables
ApiConfig	api_config	Operations & Integrations	—
ArchivedData	archived_data	Operations & Integrations	—
ChunkProgress	chunk_progress	Operations & Integrations	ChunkProgress entity for tracking individual chunk downloads Enables resume capa
CleanupLog	cleanup_logs	Operations & Integrations	—
DateExclusion	date_exclusions	Operations & Integrations	DateExclusion entity - stores excluded dates for dashboard calculations Enhanced
ExportJob	export_jobs	Operations & Integrations	ExportJob entity for tracking async export requests Enables background generatio
QuarantinedData	quarantined_data	Operations & Integrations	QuarantinedData entity for storing corrupted or invalid data Enables data integr

Exposure Management Tables

9 entities

Entity Relationships (Mermaid ERD)

Column Reference

assets (Asset)

Asset entity for storing vulnerability management asset inventory. Supports multi-platform ingestion (Tenable, Qualys, Rapid7, CrowdStrike, etc.) Core fields are normalized across tools; tool-specific

Column	Type	Nullable	Notes
uuid	varchar	No	PK
id	varchar	Yes
hostname	varchar	Yes
ipv4	simple-array	Yes
ipv6	simple-array	Yes
fqdn	simple-array	Yes
operatingSystem	varchar	Yes
hasAgent	boolean	No
lastSeen	timestamp	Yes
lastAuthenticatedScanDate	timestamp	Yes
lastLicensedScanDate	timestamp	Yes
sources	text	Yes
tags	text	Yes
isLicensed	boolean	No
assetType	varchar	Yes
acrScore	float	Yes
aesScore	float	Yes
systemType	varchar	Yes
agentName	varchar	Yes
netbiosName	varchar	Yes
macAddresses	text	Yes
networkId	varchar	Yes
installedSoftware	text	Yes
sshFingerprint	varchar	Yes
isPublic	boolean	Yes
terminatedAt	timestamp	Yes
lastScanTime	timestamp	Yes
firstObserved	timestamp	Yes
sourceTool	varchar	No
sourceToolAssetId	varchar	Yes
toolSpecificData	text	Yes
syncJobId	varchar	Yes
createdAt	varchar	No
updatedAt	varchar	No

scan_history_imports (ScanHistoryImport)

Tracks individual scan-run imports. Each row represents one Tenable scan history entry that was (or is being) imported. The UNIQUE(scan_id, history_id) constraint prevents re-importing the same scan r

Column	Type	Nullable	Notes
id	int	No	PK
importJobId	varchar	No
scanId	int	No
scanName	varchar	Yes
historyId	int	No
scanDate	timestamptz	No
findingsCount	int	No
status	varchar	No
error	text	Yes
processedAt	timestamptz	Yes
createdAt	timestamptz	No

scan_import_jobs (ScanImportJob)

Tracks bulk scan history import jobs. Each row represents one admin-initiated import job which may process multiple scan runs. Progress counters are updated in real-time during import. Status lifecycl

Column	Type	Nullable	Notes
id	varchar	No	PK
status	varchar	No
totalRuns	int	No
processedRuns	int	No
failedRuns	int	No
skippedRuns	int	No
totalFindings	int	No
selectedScanIds	jsonb	Yes
dateRangeStart	timestamptz	Yes
dateRangeEnd	timestamptz	Yes
dryRun	boolean	No
startedBy	varchar	Yes
startedAt	timestamptz	Yes
completedAt	timestamptz	Yes
error	text	Yes
createdAt	timestamptz	No

sync_jobs (SyncJob)

Column	Type	Nullable	Notes
id	uuid	No	PK
status	varchar	No
ingestionStatus	varchar	No
statsStatus	varchar	No
triggerType	varchar	No
scope	varchar	No
dateRangeStart	date	Yes
dateRangeEnd	date	Yes
progress	int	No
totalRecords	int	No
processedRecords	int	No
apiCallsCount	int	No
phase	varchar	No
recordsInserted	int	No
recordsUpdated	int	No
recordsSkipped	int	No
recordsFailed	int	No
statusMessage	text	Yes
errorMessage	text	Yes
triggeredBy	varchar	Yes
createdAt	varchar	No
startedAt	timestamp	Yes
completedAt	timestamp	Yes
updatedAt	varchar	No

sync_logs (SyncLog)

Vulnerability entity for storing Tenable.io vulnerability data This allows viewing real IPs, hostnames, and downloading raw data UNIQUE CONSTRAINT: instanceKey (sha256 of assetUuid:pluginId:port:proto

Column	Type	Nullable	Notes
id	uuid	No	PK
syncJobId	varchar	No
endpoint	varchar	No
status	varchar	No
durationMs	int	Yes
recordCount	int	No
triggerType	varchar	No
triggeredBy	varchar	Yes
errorMessage	text	Yes
timestamp	varchar	No

vulnerabilities (Vulnerability)

Vulnerability entity for storing Tenable.io vulnerability data This allows viewing real IPs, hostnames, and downloading raw data UNIQUE CONSTRAINT: instanceKey (sha256 of assetUuid:pluginId:port:proto

Column	Type	Nullable	Notes
id	uuid	No	PK
instanceKey	varchar	Yes
findingId	varchar	Yes
assetUuid	varchar	No
hostname	varchar	Yes
ipv4	simple-array	Yes
ipv6	simple-array	Yes
operatingSystem	varchar	Yes
fqdn	simple-array	Yes
pluginId	int	No
pluginName	varchar	No
pluginFamily	varchar	Yes
description	text	Yes
solution	text	Yes
port	int	Yes
protocol	varchar	Yes
service	varchar	Yes
severity	varchar	No
severityId	int	No
cvssBaseScore	decimal	Yes
cvss3BaseScore	decimal	Yes
cve	simple-array	Yes
cveIds	text	Yes
exploitAvailable	boolean	No
hasPatch	boolean	No
state	varchar	No
firstFound	timestamp	Yes
lastFound	timestamp	Yes
lastFixed	timestamp	Yes
firstFixedObservedAt	timestamp	Yes
syncJobId	varchar	Yes
osCategory	varchar	Yes
dataChecksum	varchar	Yes
version	int	No
lastSeenAt	timestamp	Yes
createdAt	varchar	No
updatedAt	varchar	No

vulnerability_state_transitions (VulnerabilityStateTransition)

VulnerabilityStateTransition tracks state changes over time Enhanced to support instanceKey-based tracking and detailed event metadata Used for: - Historical trend computation (correct snapshots) - MT

Column	Type	Nullable	Notes
id	uuid	No	PK
instanceKey	varchar	Yes
assetUuid	varchar	No
pluginId	int	No
port	int	No
protocol	varchar	No
previousState	varchar	Yes
newState	varchar	No
oldLastFound	timestamp	Yes
newLastFound	timestamp	Yes
oldLastFixed	timestamp	Yes
newLastFixed	timestamp	Yes
syncJobId	varchar	Yes
reasonTag	varchar	Yes
source	varchar	No
hasPatch	boolean	Yes
severity	varchar	Yes
pluginFamily	varchar	Yes
transitionedAt	varchar	No

vulnerability_stats (VulnerabilityStats)

VulnerabilityStats entity for pre-computed daily aggregations This allows fast dashboard loading without processing 100k+ records in realtime

Column	Type	Nullable	Notes
date	date	No	PK
totalActive	int	No
totalNew	int	No
totalRemediated	int	No
totalResurfaced	int	No
patchableNew	int	No
unpatchableNew	int	No
patchableRemediated	int	No
unpatchableRemediated	int	No
patchableResurfaced	int	No
unpatchableResurfaced	int	No
totalRemediatedCumulative	int	No
criticalCount	int	No
highCount	int	No
mediumCount	int	No
lowCount	int	No
infoCount	int	No
patchableActive	int	No
unpatchableActive	int	No
patchableCritical	int	No
patchableHigh	int	No
unpatchableCritical	int	No
unpatchableHigh	int	No
patchableMedium	int	No
patchableLow	int	No
patchableInfo	int	No
unpatchableMedium	int	No
unpatchableLow	int	No
unpatchableInfo	int	No
reopenedCount	int	No
meanTimeToRemediate	float	No
osCounts	simple-json	Yes
dataSource	varchar	No
syncJobId	varchar	Yes
computedAt	varchar	No

vulnerability_stats_by_category (VulnerabilityStatsByCategory)

VulnerabilityStatsByCategory stores daily per-category aggregations This enables correct historical trend computation - trends are computed from these snapshots, NOT derived from current state Categor

Column	Type	Nullable	Notes
date	date	No	PK
osCategory	varchar	No	PK
totalActive	int	No
totalNew	int	No
totalRemediated	int	No
totalResurfaced	int	No
patchableNew	int	No
unpatchableNew	int	No
patchableRemediated	int	No
unpatchableRemediated	int	No
patchableResurfaced	int	No
unpatchableResurfaced	int	No
criticalCount	int	No
highCount	int	No
mediumCount	int	No
lowCount	int	No
infoCount	int	No
patchableActive	int	No
unpatchableActive	int	No
patchableCritical	int	No
patchableHigh	int	No
unpatchableCritical	int	No
unpatchableHigh	int	No
patchableMedium	int	No
patchableLow	int	No
patchableInfo	int	No
unpatchableMedium	int	No
unpatchableLow	int	No
unpatchableInfo	int	No
validRemediations	int	No
mttrHours	decimal	Yes
mttrHoursValidOnly	decimal	Yes
dataSource	varchar	No
syncJobId	varchar	Yes
computedAt	varchar	No

AI Labs Tables

1 entity

Entity Relationships (Mermaid ERD)

Column Reference

anomaly_days (AnomalyDay)

AnomalyDay tracks days with detected data anomalies (spikes) Used for: - KPI normalization (exclude anomaly days from counts) - Spike investigation and root cause analysis - Automated detection based

Column	Type	Nullable	Notes
date	date	No	PK
fixedCount	int	No
baselineAvg	decimal	Yes
baselineStddev	decimal	Yes
classification	varchar	No
status	varchar	No
suspectedCause	varchar	Yes
distinctPluginsAffected	int	Yes
distinctAssetsAffected	int	Yes
notes	text	Yes
createdAt	varchar	No

Administration & Auth Tables

9 entities

Entity Relationships (Mermaid ERD)

Column Reference

api_audit_logs (ApiAuditLog)

Column	Type	Nullable	Notes
id	int	No	PK
timestamp	varchar	No
endpoint	varchar	No
method	varchar	No
status	varchar	No
httpStatus	int	Yes
durationMs	int	No
recordsLoaded	int	No
chunksProcessed	int	No
totalChunks	int	No
syncType	varchar	No
syncId	varchar	Yes
errorMessage	text	Yes
requestParams	jsonb	Yes
responseMetadata	jsonb	Yes

ip_blacklist (IpBlacklist)

Column	Type	Nullable	Notes
id	uuid	No	PK
ipAddress	varchar	No
reason	varchar	No
failedAttempts	int	No
relatedUserEmails	simple-array	Yes
blockedAt	timestamp	No
expiresAt	timestamp	Yes
isActive	boolean	No
blockedByEmail	varchar	Yes
unblockedByEmail	varchar	Yes
unblockedAt	timestamp	Yes
createdAt	varchar	No

ip_whitelist (IpWhitelist)

Column	Type	Nullable	Notes
id	uuid	No	PK
ipAddress	varchar	No
reason	varchar	No
description	varchar	Yes
whitelistedAt	timestamp	No
expiresAt	timestamp	Yes
isActive	boolean	No
createdByEmail	varchar	No
removedByEmail	varchar	Yes
removedAt	timestamp	Yes
createdAt	varchar	No

roles (Role)

Column	Type	Nullable	Notes
id	uuid	No	PK
name	varchar	No
description	text	Yes
permissions	jsonb	No
isSystem	boolean	No
createdAt	varchar	No
updatedAt	varchar	No

Relations:

• users: OneToMany → User

saved_filters (SavedFilter)

SavedFilter — User-scoped saved filter presets for Explore page Each user can save named filter configurations for both the Vulnerabilities and Assets tabs. Filters can be Quick Filters, Advanced Quer

Column	Type	Nullable	Notes
id	uuid	No	PK
userId	uuid	No
name	varchar	No
page	varchar	No
filterData	jsonb	No
isDefault	boolean	No
createdAt	varchar	No
updatedAt	varchar	No

security_audit_logs (SecurityAuditLog)

Column	Type	Nullable	Notes
id	int	No	PK
timestamp	varchar	No
eventType	varchar	No
userId	varchar	Yes
userEmail	varchar	Yes
userRole	varchar	Yes
ipAddress	varchar	Yes
userAgent	varchar	Yes
endpoint	varchar	Yes
method	varchar	Yes
success	boolean	No
details	jsonb	Yes

settings (Settings)

Column	Type	Nullable	Notes
key	varchar	No	PK
value	jsonb	No
description	varchar	Yes
updatedAt	varchar	No

token_blacklist (TokenBlacklist)

Column	Type	Nullable	Notes
id	int	No	PK
token	varchar	No
userId	varchar	Yes
reason	varchar	No
expiresAt	timestamp	No
createdAt	varchar	No

users (User)

Column	Type	Nullable	Notes
id	uuid	No	PK
email	varchar	No
passwordHash	varchar	No
name	varchar	No
role	varchar	Yes
status	varchar	No
failedLoginAttempts	int	No
lockedUntil	timestamp	Yes
lastLoginAt	timestamp	Yes
lastLoginIp	varchar	Yes
twoFactorEnabled	boolean	No
twoFactorSecret	varchar	Yes
resetPasswordToken	varchar	Yes
resetPasswordExpires	timestamp	Yes
createdAt	varchar	No
updatedAt	varchar	No

Relations:

• roleDefinition: ManyToOne → Role

Operations & Integrations Tables

7 entities

Entity Relationships (Mermaid ERD)

Column Reference

api_config (ApiConfig)

Column	Type	Nullable	Notes
id	uuid	No	PK
key	varchar	No
encryptedValue	text	No
description	varchar	Yes
createdAt	varchar	No
updatedAt	varchar	No

archived_data (ArchivedData)

Column	Type	Nullable	Notes
id	int	No	PK
filename	varchar	No
startDate	date	No
endDate	date	No
status	varchar	No
sizeBytes	bigint	No
recordCount	int	No
storagePath	varchar	Yes
checksum	varchar	Yes
errorMessage	text	Yes
createdBy	varchar	Yes
createdAt	varchar	No
restoredAt	timestamp	Yes

chunk_progress (ChunkProgress)

ChunkProgress entity for tracking individual chunk downloads Enables resume capability and detailed progress monitoring

Column	Type	Nullable	Notes
id	uuid	No	PK
syncJobId	varchar	No
exportUuid	varchar	No
chunkId	int	No
chunkIndex	int	No
totalChunks	int	No
status	varchar	No
recordCount	int	No
retryCount	int	No
maxRetries	int	No
startedAt	timestamp	Yes
completedAt	timestamp	Yes
durationMs	int	Yes
errorMessage	text	Yes
lastError	text	Yes
createdAt	varchar	No
updatedAt	varchar	No

cleanup_logs (CleanupLog)

Column	Type	Nullable	Notes
id	uuid	No	PK
jobId	varchar	Yes
assetUuid	varchar	No
hostname	varchar	Yes
lastSeen	timestamp	No
reason	varchar	No
deletedVulnerabilitiesCount	int	No
deletedAt	varchar	No

date_exclusions (DateExclusion)

DateExclusion entity - stores excluded dates for dashboard calculations Enhanced with automated anomaly detection (Phase 3): - Supports both manual exclusions and auto-detected spikes - Tracks baselin

Column	Type	Nullable	Notes
date	date	No	PK
isExcluded	boolean	No
reason	varchar	Yes
excludedBy	varchar	Yes
isAutoDetected	boolean	No
baselineAvg	decimal	Yes
baselineStddev	decimal	Yes
fixedCount	int	Yes
suspectedCause	varchar	Yes
distinctPluginsAffected	int	Yes
distinctAssetsAffected	int	Yes
status	varchar	No
createdAt	timestamp	No
updatedAt	timestamp	No

export_jobs (ExportJob)

ExportJob entity for tracking async export requests Enables background generation of large exports without blocking the frontend

Column	Type	Nullable	Notes
id	uuid	No	PK
status	varchar	No
format	varchar	No
progress	int	No
totalRecords	int	Yes
processedRecords	int	Yes
filePath	varchar	Yes
downloadUrl	varchar	Yes
fileSize	bigint	Yes
expiresAt	timestamp	Yes
errorMessage	text	Yes
requestedBy	varchar	Yes
filters	simple-json	Yes
createdAt	varchar	No
updatedAt	varchar	No
completedAt	timestamp	Yes

quarantined_data (QuarantinedData)

QuarantinedData entity for storing corrupted or invalid data Enables data integrity protection and automated recovery attempts

Column	Type	Nullable	Notes
id	uuid	No	PK
syncJobId	varchar	No
chunkId	int	Yes
quarantineReason	varchar	No
errorDetails	text	No
rawData	jsonb	Yes
assetUuid	varchar	Yes
pluginId	int	Yes
retryCount	int	No
maxRetries	int	No
status	varchar	No
resolutionNotes	text	Yes
resolvedBy	varchar	Yes
quarantinedAt	varchar	No
lastRetryAt	timestamp	Yes
resolvedAt	timestamp	Yes
updatedAt	varchar	No

---

Generated on 2026-04-05T13:06:07.159Z