Database Schema

Relevant source files

The following files were used as context for generating this wiki page:

• cli/types/types.go
• api/db/postgres_test.go
• api/db/huskystats.go
• client/analysis/output.go
• api/util/api/api.go
• api/securitytest/brakeman.go
• client/integration/sonarqube/testdata/analysis/vulnerable_go_project.json

The huskyCI database is a core component responsible for persisting all data related to security analyses. It centralizes security test definitions, repository information, and detailed analysis results, including vulnerabilities. This allows for historical tracking, metrics generation, and centralized reporting of an organization's security posture over time. The system uses a document-oriented or relational database (the abstraction supports both) to store this information, with the Analysis entity being the central document for each security scan.

Entity Relationship Diagram

The following diagram illustrates the main entities within the huskyCI database and their relationships. The analysis table is the central entity, linking repositories, security tests, and their results.

Core Entities

The database schema is defined by several key Go structs, which are mapped to database tables or collections.

Analysis

The Analysis entity is the central record for each security scan performed by huskyCI. It aggregates all information about a specific run, including the repository scanned, the containers used, and the results.

Sources: cli/types/types.go:28-40

Field	Type	Description
ID	bson.ObjectId	The unique identifier for the analysis document (used in MongoDB).
RID	string	A unique, human-readable identifier for the analysis run.
URL	string	The URL of the repository that was analyzed.
Branch	string	The specific branch of the repository that was analyzed.
Status	string	The current status of the analysis (e.g., started, finished, error).
Result	string	The final result of the analysis (e.g., passed, failed).
Containers	[]Container	A slice of Container structs, detailing each security test run. Stored as JSONB.
ErrorFound	string	Any errors that occurred during the analysis.
StartedAt	time.Time	Timestamp when the analysis began.
FinishedAt	time.Time	Timestamp when the analysis completed.
Codes	[]Code	Information about the languages and files detected in the repository. Stored as JSONB.
HuskyCIResults	HuskyCIResults	A nested structure containing all vulnerabilities found, categorized by tool. Stored as JSONB.

The following class diagram shows the composition of the Analysis struct.

SecurityTest

This entity defines a security tool that can be executed by huskyCI. These are typically pre-populated at startup.

Sources: api/util/api/api.go:173-201, cli/types/types.go:81-90

Field	Type	Description
ID	bson.ObjectId	The unique identifier for the security test document.
Name	string	The unique name of the security tool (e.g., gosec, bandit).
Image	string	The Docker image used to run the tool.
ImageTag	string	The tag of the Docker image.
Cmd	string	The command executed inside the container to run the scan.
Language	string	The programming language this tool targets.
Default	bool	Whether this test is run by default.
TimeOutInSeconds	int	The timeout for the container execution.

Repository

Stores basic information about a repository that has been scanned.

Sources: api/db/postgres_test.go:100-104

Field	Type	Description
URL	string	The Git URL of the repository.
CreatedAt	time.Time	Timestamp when the repository was first added.
Branch	string[]	(Implicitly handled) A list of branches scanned for this repository.

User and DBToken

These entities are used for user authentication and API access control, but are not directly tied to the analysis results.

Sources: api/db/postgres_test.go:112-122

Entity	Field	Type	Description
User	Username	string	The user's login name.
	Password	string	The user's hashed password.
	Salt	string	The salt used for hashing the password.
DBToken	HuskyToken	string	The access token string.
	URL	string	The repository URL this token is associated with.
	IsValid	bool	Whether the token is currently valid.

Vulnerability Data Structure

All findings from various security tools are normalized into a common HuskyCIVulnerability structure. This allows for consistent processing and reporting.

HuskyCIResults and HuskyCIVulnerability

The HuskyCIResults struct is a container for the outputs of all security tests, categorized by language. Each tool's output is a HuskyCISecurityTestOutput struct, which contains slices of vulnerabilities.

The core vulnerability structure is HuskyCIVulnerability.

Sources: cli/types/types.go:93-120, client/analysis/output.go:100-250

Field	Type	Description	Example Tool
Language	string	The programming language of the vulnerability.	Bandit, Gosec
SecurityTool	string	The tool that found the vulnerability.	Brakeman
Severity	string	The severity level (e.g., LOW, MEDIUM, HIGH, NOSEC).	All
Confidence	string	The confidence level of the finding.	Bandit, Brakeman
File	string	The file path where the vulnerability was found.	Gosec, Bandit
Line	string	The line number of the vulnerability.	Gosec, Bandit
Code	string	The snippet of vulnerable code.	Gosec, Brakeman
Details	string	A detailed description of the vulnerability.	All
Type	string	The type or category of the vulnerability.	Brakeman
Title	string	A concise title for the vulnerability.	Gosec, Safety
VulnerableBelow	string	For dependency scanning, the version range that is vulnerable.	Safety, NpmAudit
Version	string	The detected version of the vulnerable dependency.	NpmAudit

This structure is populated by parsing the JSON output of each security tool container. For example, the analyzeBrakeman function processes the raw JSON from the Brakeman tool and maps its fields to HuskyCIVulnerability.

Sources: api/securitytest/brakeman.go:57-88

Database Operations

The application interacts with the database through a DB interface, with a concrete implementation for Postgres. The tests in postgres_test.go reveal the primary operations.

Analysis Lifecycle

The typical lifecycle of an Analysis record involves an initial insertion followed by one or more updates.

1. Insertion: A new analysis record is created with a started status using InsertDBAnalysis.
2. Updates: As containers finish, the Analysis record is updated with container results and vulnerability information using UpdateOneDBAnalysis.
3. Finalization: Once all containers are finished, the Analysis status is set to finished and the final result is recorded.

This sequence diagram shows the flow for creating and updating an analysis.

Statistics and Aggregation

The api/db/huskystats.go file defines aggregation pipelines to generate metrics from the stored analysis data. These queries allow for historical and real-time insights into security trends.

Key metrics include:

• language: Count of analyses per programming language.
• container: Count of analyses per security tool.
• analysis: Count of analyses by result (passed, failed, etc.).
• repository: Total number of scanned repositories and branches.
• author: Total number of unique commit authors.
• severity: Total count of vulnerabilities grouped by severity (low, medium, high).
• historyanalysis: A time-series of analysis results, aggregated by hour.

The following flowchart illustrates the aggregation pipeline for the severity metric.