Database

ATHELOS uses PostgreSQL 16 with the pgvector extension. This page is a reference for the schema.

Conventions

Primary keys — UUIDs, generated at insert time
Timestamps — timestamptz, always UTC
Money — integer cents, never floats
Soft delete — some tables use a status field instead of hard delete
Foreign keys — enforced with ON DELETE CASCADE where appropriate
Indexes — every foreign key is indexed; some tables have composite indexes for query patterns

Extensions

Installed automatically during migration:

Extension	Purpose
`uuid-ossp`	UUID generation
`pgcrypto`	Encryption functions
`vector`	pgvector for embeddings
`pg_trgm`	Trigram matching for fuzzy search

Verify:

docker exec soc_postgres psql -U soc -d soc_solo -c "\\dx"

Table groups

The schema is organized into eight groups:

Auth and system — users, sessions, audit log, settings
Knowledge base — articles, tags, embeddings
Playbooks — structured metadata, executions
Engagements — clients, contacts, scope, findings, evidence, time
Reports — templates, report instances, finding selections
Business — contracts, rate cards, invoices, deliveries
Toolbox — tools, wordlists, payloads, labs, infrastructure
Learning — certifications, labs, notes

Auth and system

`users`

The owner account. Single-row in normal operation.

Column	Type	Notes
id	uuid	Primary key
email	text	Unique
name	text	Display name
password_hash	text	Argon2id hash
role	text	Default `owner`
totp_secret	text	Encrypted TOTP secret
totp_enabled	boolean	Whether TOTP is active
totp_enrolled_at	timestamptz	When TOTP was enabled
webauthn_enabled	boolean	Whether WebAuthn is active
created_at	timestamptz	—

`sessions`

Active login sessions.

Column	Type	Notes
id	text	Primary key
user_id	uuid	FK to users
expires_at	timestamptz	—

`audit_log`

Append-only record of every write operation.

Column	Type	Notes
id	uuid	Primary key
user_id	uuid	FK to users, nullable
action	text	e.g. `finding.create`
entity	text	e.g. `finding`
entity_id	text	The affected row
meta	jsonb	Additional context
ip	text	Source IP if available
created_at	timestamptz	—

`app_settings`

Key-value store for application-wide settings.

Column	Type	Notes
key	text	Primary key
value	jsonb	—
updated_at	timestamptz	—

Knowledge base

`kb_articles`

Every knowledge base article.

Column	Type	Notes
id	uuid	Primary key
slug	text	Unique, URL-friendly
title	text	—
summary	text	Short description
body	text	Markdown
category	text	e.g. `methodology`
pinned	boolean	Featured at top
created_at	timestamptz	—
updated_at	timestamptz	—

`kb_tags`

Tag definitions.

`kb_article_tags`

Many-to-many join.

`kb_article_embeddings`

Vector embeddings for semantic search.

Column	Type	Notes
article_id	uuid	PK, FK to kb_articles
model	text	Embedding model name
dimensions	integer	Vector size (768)
content_hash	text	SHA-256 for idempotency
embedding	vector(768)	pgvector type
updated_at	timestamptz	—

Playbooks

`kb_playbook_meta`

Structured metadata for playbook articles.

Column	Type	Notes
article_id	uuid	PK, FK to kb_articles
framework	text	Framework name
version	text	—
estimated_hours	integer	—
difficulty	text	—
phases	jsonb	Array of phase objects
tools	jsonb	Array of tool names
evidence_templates	jsonb	Evidence examples
attck_techniques	jsonb	ATT&CK IDs
references	jsonb	Reference links

`engagement_playbooks`

A playbook instance loaded onto an engagement.

`engagement_tasks`

Individual tasks within a loaded playbook.

`task_findings` / `task_evidence`

Attachments linking tasks to findings and evidence.

Engagements

`clients`

Client records with CRM fields.

Column	Type	Notes
id	uuid	Primary key
name	text	—
contact_name	text	—
contact_email	text	—
status	text	lead / qualified / proposal / negotiation / client / lost
address	text	—
tax_id	text	—
billing_email	text	—
currency	text	ISO 4217
payment_terms_days	integer	—
source	text	How you found them
tags	jsonb	Array of tags
expected_value_cents	integer	—
website	text	—
industry	text	—

`client_contacts`

Multiple contacts per client.

`client_interactions`

Chronological log of touchpoints.

`engagements`

The core engagement record.

Column	Type	Notes
id	uuid	Primary key
client_id	uuid	FK to clients
name	text	—
type	text	pentest / forensics / IR / etc.
status	text	scoping / recon / exploitation / reporting / closed
start_date	date	—
end_date	date	—

`scope_items`

In-scope and out-of-scope targets.

`findings`

Every finding.

Column	Type	Notes
id	uuid	Primary key
engagement_id	uuid	FK to engagements
title	text	—
severity	text	critical / high / medium / low / info
status	text	open / confirmed / false_positive / retested
cvss_score	numeric	0.0 to 10.0
cvss_vector	text	—
description	text	—
impact	text	—
remediation	text	—
references	text	—
affected_assets	jsonb	Array of scope item IDs

`finding_tags`

ATT&CK and custom tags per finding.

`evidence`

Evidence files attached to findings.

Column	Type	Notes
id	uuid	Primary key
finding_id	uuid	FK to findings
filename	text	—
size	integer	Bytes
mimetype	text	—
sha256	text	File hash
storage_path	text	Object storage key
uploaded_at	timestamptz	—

`time_entries`

Billable and non-billable time.

Reports

`reports`

Report instances.

Column	Type	Notes
id	uuid	Primary key
engagement_id	uuid	FK to engagements
title	text	—
version	integer	v1, v2, ...
status	text	draft / in_review / delivered
template_slug	text	—
template_kind	text	—
sections	jsonb	Section content
letterhead	jsonb	Cover page data
executive_summary	text	—
overview	text	—
methodology	text	—
delivered_at	timestamptz	—

`report_findings`

Which findings appear in a report and in what order.

`report_templates`

The seven templates that ship with ATHELOS.

Business

`contracts`

Signed contracts.

`contract_templates`

Reusable templates (MSA, SoW, NDA).

`rate_cards`

Billing rates.

`engagement_estimates`

Scoping calculator output per engagement.

`invoices`

Column	Type	Notes
id	uuid	Primary key
client_id	uuid	FK to clients
engagement_id	uuid	FK to engagements, nullable
invoice_number	text	Unique
issue_date	date	—
due_date	date	—
status	text	draft / sent / paid / overdue / cancelled
subtotal_cents	integer	—
tax_cents	integer	—
total_cents	integer	—
paid_at	timestamptz	—

`invoice_items`

Line items with integer cents.

`client_deliveries`

Signed delivery links for reports.

Column	Type	Notes
id	uuid	Primary key
report_id	uuid	FK to reports
client_id	uuid	FK to clients
token_hash	text	SHA-256 of raw token, unique
token_prefix	text	First 8 chars for UI
kind	text	report / finding-summary / retest-request
expires_at	timestamptz	—
revoked_at	timestamptz	—
view_count	integer	—
last_viewed_at	timestamptz	—
last_viewed_ip	text	—

Toolbox

`tools`

Tool catalog.

`wordlists`

Path and metadata only — files aren't uploaded.

`payloads`

Payload library.

`labs`

Lab environments.

`infrastructure`

Servers, VPS, domains. credentials_ref points to a password manager, never the secret itself.

Learning

`certifications`

Cert paths and status.

`learning_labs`

Lab progress.

`learning_lab_notes`

Inline notes on labs.

Querying the database

Open a shell:

docker exec -it soc_postgres psql -U soc -d soc_solo

Common queries:

-- List all tables
\\dt

-- Describe a table
\\d findings

-- Count findings by severity
SELECT severity, COUNT(*) FROM findings GROUP BY severity;

-- Recent audit entries
SELECT action, entity, created_at FROM audit_log
ORDER BY created_at DESC LIMIT 20;

Migrations

Migrations live in packages/db/drizzle/. Each migration is a numbered SQL file.

Run pending migrations:

cd packages/db
pnpm drizzle-kit migrate

Generate a new migration after schema changes:

cd packages/db
pnpm drizzle-kit generate

Where to go next

CLI reference — migration commands
Environment variables — DB connection
Scheduled backups — backup policy

Database

On this page