ThreatLens · ZEIT3118 Capstone

Database & Schema

ThreatLens stores everything it learns in a seven-table SQLite database, defined with SQLAlchemy 2.0 models and built with Alembic migrations. The schema is at v1.5 (Alembic head b246eb301c95).

The ingest pipeline is the only thing that adds data: one report becomes a source, its archived document, the indicators it contains, a sighting for each, and any quality flags. The dashboard reads the database and allows a few manual edits. The threatlens CLI and the extraction engine never touch it.

Entity overview

sources ──1:1── raw_documents           (the report, identified by its SHA-256)
   │
   │ 1:N
   ▼
sightings ── N:1 ──▶ indicators ── 1:N ──▶ quality_flags
(indicator × report,  (one row per unique
 with the excerpt)     ioc_type + normalised value)

allowlist        known-benign patterns (reference data)
scoring_config   dimension weights and bands (reference data)

An indicator seen in five reports is one indicators row with five sightings. That is the schema's answer to the client's duplication problem: repeated observation across sources is corroboration, recorded as sightings, not as duplicate rows. Duplication is prevented at two levels by UNIQUE constraints: raw_documents.sha256 (the same report ingested twice) and indicators (ioc_type, normalised) (the same value under two representations).

Tables

All timestamps are ISO-8601 UTC text (e.g. 2026-10-12T15:23:44Z). JSON columns are TEXT guarded by a json_valid() CHECK.

`sources`: one row per report

Column	Type	Notes
`id`	INTEGER PK
`name`	TEXT NOT NULL	The report's filename
`source_type`	TEXT NOT NULL	`report` for everything ingested so far
`url`	TEXT	The report's own URL, if the extractor found one
`ingested_at`	TEXT NOT NULL	Defaults to `datetime('now')`
`raw_metadata`	TEXT (JSON)	The extractor's document metadata (title, authors, publisher, publish date, URL, `file_sha256`, how each field was found) plus `candidate_count`
`reliability`	INTEGER 0–100	Share of the report's candidates not rejected by the quality checker. Drives the accuracy dimension.
`extraction_engine`	TEXT	`python`, `llm` or `llm+python` (CHECK); NULL for reports ingested before v1.5
`verification_model`	TEXT	The verifier model(s), e.g. `qwen3.5:9b` or `python-heuristics`, comma-joined if both

`raw_documents`: the archived report, 1:1 with sources

Column	Type	Notes
`source_id`	INTEGER FK, UNIQUE	`ON DELETE CASCADE`
`content_type`	TEXT NOT NULL	e.g. `application/pdf`
`raw_text` / `file_path`	TEXT	At least one must be set (CHECK). The ingest pipeline stores the path relative to the data folder when the file is inside it (`docs/database/CTI/….pdf` in a checkout, `reports/….pdf` when pip-installed), absolute otherwise; not the content. The PDF stays on disk.
`sha256`	TEXT NOT NULL, UNIQUE	64 characters (CHECK), lower-case. The document's identity: the SHA-256 of the file bytes, from the extractor's `metadata.file_sha256`.
`size_bytes`	INTEGER
`archived_at`	TEXT NOT NULL

Because only the path is stored, moving or deleting a PDF in docs/database/CTI/ breaks the dashboard's PDF view for that report, though its data stays intact.

`indicators`: one row per unique observable

Column	Type	Notes
`ioc_type`	TEXT NOT NULL	Database vocabulary (see below). No CHECK: enforced by `conformance.py` on the ingest path.
`value` / `normalised`	TEXT NOT NULL	As found, and the canonical form (refanged, lower-cased where appropriate). UNIQUE `(ioc_type, normalised)`.
`first_seen`	TEXT	The earliest publish date among the reports that sighted it
`last_seen`	TEXT	The latest publish date among those reports. Drives timeliness. Both dates come from the reports only, never the ingest time: if no sighting report has a publish date, both are NULL and timeliness scores 0 (unknown). Ingest order does not matter.
`confidence`	INTEGER 0–100	How certain it is that the value is malicious. Set when the indicator is first stored; later ingests do not change it.
`quality_score`	INTEGER 0–100	Fitness of the indicator as data; recomputed by every rescore
`context`	TEXT (JSON)	Structured context: the report's context entities plus the first sighting's excerpt (see below)
`score_components`	TEXT (JSON)	How the score was reached (see below)
`scored_at`	TEXT	When the score was last computed
`ctx_malware`	generated, virtual	`json_extract(context, '$.malware_family')`, so malware can be filtered without parsing JSON

`indicators.context`

{
  "threat_actor": ["FEI XIAO"],          // the report's surviving context entities,
  "malware": ["…"], "malware_family": ["…"],   // grouped by type
  "campaign": […], "tool": […], "vulnerability": […],
  "excerpt": "…text around the value…",  // from the extractor
  "extraction_method": "regex:filename",
  "attributes": {"was_defanged": false, "document_section_hint": "Technical Analysis"}
}

Completeness is scored on how many of threat_actor, campaign, vulnerability and tool are present. The context is written when the indicator is first stored and only filled in later if it was empty, so it describes the first report the value was seen in. Each report's own excerpt is kept on its sighting.

`indicators.score_components`

{
  "dimensions": {"timeliness": 1, "accuracy": 5, "completeness": 2, "relevance": 3},
  "weights":    {"timeliness": 0.25, "accuracy": 0.3, "completeness": 0.25, "relevance": 0.2},
  "grade": "Marginal",
  "agreement": {"agreement": "single_verifier", "winner": "llm",
                "winning_status": "confirmed_ioc", "llm_status": "…", "llm_confidence": 1.0,
                "python_status": null, "python_confidence": null, "confidence": 85, …},
  "stix": {"kind": "sco", "type": "file:name"},
  "sightings": 1,                 // added by rescore
  "source_reliability": 100.0     // added by rescore
}

Every score records the weights it was computed with, so a score can always be explained after the weights change. The dashboard's indicator page reads this to show the breakdown.

`sightings`: indicator × report

Column	Type	Notes
`indicator_id`, `source_id`	composite PK, FKs	Both `ON DELETE CASCADE`
`seen_at`	TEXT NOT NULL	The ingest time
`excerpt_start`, `excerpt_end`	INTEGER	Character offsets of the value in the report's extracted text
`excerpt`	TEXT	The text around the value in this report

`quality_flags`: findings against a stored indicator

Column	Type	Notes
`indicator_id`	INTEGER FK NOT NULL	`ON DELETE CASCADE`
`flag_type`	TEXT NOT NULL	e.g. `verifier_rejected`, `known_benign`, `self_reference` (full list on the Quality Checker page)
`dimension`	TEXT	The quality dimension the finding belongs to, the traceability link back to the research framework
`severity`	TEXT NOT NULL	`warning` (default; screened as not malicious) or `info` (kept with a caveat). `error` findings belong to rejected candidates, which are never stored.
`detail`	TEXT	Human-readable reason, including the verifier's reasoning where relevant
`raised_at`	TEXT NOT NULL
`status`	TEXT NOT NULL	`open` (default); the dashboard sets `resolved` or `dismissed`. No CHECK.

A flag type already recorded against an indicator is not raised again. Flags exist only for stored indicators; candidates the quality checker rejected outright are not in the database at all.

`allowlist` and `scoring_config`: reference data

Table	Columns	Seeded with
`allowlist`	`ioc_type`, `pattern`, `reason`	16 entries: public DNS resolvers, RFC 1918 and loopback ranges, and major vendor domains. Patterns are exact, CIDR or `*.suffix`.
`scoring_config`	`dimension`, `weight`, `parameters` (JSON bands), `effective_from`	One row per scored dimension: timeliness 0.25, accuracy 0.30, completeness 0.25, relevance 0.20, with the Table 7 band thresholds

The latest scoring_config row per dimension is the one in force. threatlens.conf [quality.weights] overrides its weights when that section exists, so check both before reading a score. The allowlist table is seeded but not yet read during ingest; screening uses the same list from seed.py directly.

Indicator type vocabulary

conformance.to_ioc_type() maps the extractor's types to the database's. Two are narrowed using attributes the extractor supplies, so that allowlist entries (stored as ipv4) can match:

Extractor type	Stored as
`ip`	`ipv4` / `ipv6` (from `ip_version`)
`hash`	`md5` / `sha1` / `sha256` / `sha512` (from `algorithm`)
`registry`	`registry_key`
`domain`, `url`, `hostname`, `port`, `certificate`, `user_agent`, `filename`, `filepath`, `mutex`, `process`, `email_address`, `email_domain`, `email_subject`, `email_header`, `attachment`, …	Unchanged
`malware`, `threat_actor`, `campaign`, `tool`, `vulnerability`, `ttp`	Never stored as rows; folded into `indicators.context`

In the ex4 run the stored types were, by count: domain, ipv4, sha256, url, filename, filepath, email_address, md5, sha1, and a few others.

How data gets in, and out

Ingest

The only path that adds data, used by the dashboard's Ingest page, threatlens-ingest and the evaluation runs (see the Quality Checker for the full flow):

quality_checker.persist_report() builds an indicators row for each stored candidate and validates it with conformance.validate_indicator_row(), which mirrors the CHECK constraints and names the bad column instead of failing at commit.
db/ingest.py ingest_scored_rows() looks up the report by sha256. A known report reuses its source and only widens extraction_engine / verification_model; a new one gets a sources and raw_documents row.
Each row is matched on (ioc_type, normalised). A new value is inserted; an existing one gets its score, components and scored_at updated. A sighting is added if this report has not sighted it before. Then refresh_seen_dates() sets each touched indicator's first_seen / last_seen to the earliest and latest publish dates of the reports that sighted it.
raise_flags() writes the findings, then the caller commits.
rescore_from_db() recomputes timeliness and accuracy for every indicator in the database and commits again.

The ingest is idempotent per report: re-ingesting the same file adds no rows, though it does refresh scores.

Other writes

Dashboard edits (PATCH /api/indicators/{id}) set fields directly, without conformance.py. Only the CHECK constraints apply, so ioc_type can be set to anything. A hand-edited quality_score is overwritten by the next rescore; an edited confidence stays.
Dashboard deletes: deleting an indicator removes its sightings and flags. Deleting a report removes its raw document and sightings; indicators seen only in that report stay, with no sightings. Their first/last seen dates are re-derived from the reports that remain.
Flag review: the dashboard changes quality_flags.status.
Rescore from the dashboard's Configuration page, same as step 5 above.
Seeding: python -m threatlens.db.seed deletes and repopulates scoring_config and allowlist in the database named by threatlens.conf [database] default. That discards any scoring_config history.

db/ingest.py also keeps an older, unscored path (ingest_extractions(), ingest_extraction_file()) that the pipeline no longer uses.

Reading it

The dashboard reads through the same models. For raw access, the dashboard's /db-browser/ (Datasette, read-only) shows every table, and any SQLite client can open the files directly; close it before running an ingest. To take data elsewhere, use the dashboard's Export page: indicators, reports, quality flags and sightings as CSV or JSON, and indicators as STIX 2.1.

Creating a database

threatlens init does this as part of setup. By hand:

python -m threatlens.db.migrate                   # creates/upgrades threatlens.conf's database in db/
python -m threatlens.db.migrate db/other.db       # ...or any other file (parent folders are created)
python -m threatlens.db.seed                      # scoring_config + allowlist

# or, for an evaluation run into any file (migrates, seeds if empty, ingests):
python .dev_tools/run_ex_pipeline.py --db-path db/exN_threatlens.db --batch 1/3

threatlens.db.migrate runs the Alembic migrations shipped inside the package (src/threatlens/migrations/) with no alembic.ini, so it works the same from a repo checkout and from a pip install. In code: threatlens.db.migrate.upgrade(path), which run_ex_pipeline.py uses. From a checkout, the plain alembic CLI also works from the repo root through the root alembic.ini (URL db/threatlens.db):

alembic upgrade head
alembic current
alembic revision --autogenerate -m "describe the change"   # new file in src/threatlens/migrations/versions/

Never use create_all(); the schema's history lives in the migrations. models.py's own __main__ still calls it, as a leftover, and should not be used.

Migrations

Revision	Version	Change
`7797ff2fc9e2`	v1.4	Initial schema: all seven tables
`b246eb301c95`	v1.5	`sources.extraction_engine` (with CHECK) and `sources.verification_model`. Existing reports are left NULL.

The migrations are part of the package (threatlens.migrations, with the revisions as modules in versions/), so a new revision file ships in the next build with no packaging change. (They were previously in a top-level migrations/ folder that the wheel did not include.)

Alembic is configured with render_as_batch=True, because SQLite's ALTER TABLE cannot drop or alter columns or add constraints, so Alembic rebuilds the table instead. Take care with that on parent tables. Foreign keys are switched on for every connection, including Alembic's, and rebuilding drops the old table, which would cascade-delete every child row. The v1.5 migration avoids this by using plain ALTER TABLE … ADD COLUMN instead of a batch rebuild; do the same for any change to sources or indicators.

Why SQLite, and what that costs

SQLite needs no installation, no admin rights on managed lab machines and no server process, and a database is one portable file, which makes client handover simple. PostgreSQL is the documented production target. Queries go through the SQLAlchemy ORM, but the schema itself has SQLite-specific parts a port would need to replace:

the ctx_malware computed column (json_extract);
the json_valid() CHECK constraints;
datetime('now') column defaults;
the connection PRAGMAs below.

SQLite behaviours the code works around:

Foreign keys are off by default. A connect listener in models.py runs PRAGMA foreign_keys = ON on every connection. Without it, nothing cascades and nothing fails, silently.
WAL mode (PRAGMA journal_mode = WAL) lets the dashboard keep reading while an ingest writes. There is still one writer at a time, so a client holding a write transaction open causes "database is locked".
Type affinity, not enforcement. SQLite will store a string in an INTEGER column, so the CHECK constraints and conformance.py carry the integrity burden.

Database files

Database files live in the database folder (db/ in a checkout, <data folder>/db/ when pip-installed; see where files live). In a checkout they are gitignored (*.db, -shm, -wal): the migrations are the recipe, not the data. Several can sit side by side: threatlens.db, the evaluation runs ex2–ex4_threatlens.db, and test copies. The dashboard opens threatlens.conf [database] default at startup and can switch between them at runtime.

Known issues

Databases built before 13 Oct 2026 have first_seen empty and last_seen set in ingest order (since fixed). python .dev_tools/backfill_seen_dates.py db/<file>.db repairs them and rescores as of the original scoring time. ex2, ex3, ex4 and threatlens.db have been backfilled.
Dashboard edits bypass validation, and deleting a report can leave indicators with no sightings (see Other writes).
The allowlist table is not read during ingest, so editing it has no effect yet.

Design lineage

STIX 2.1: indicator typing (observables vs. context), 0–100 confidence, and the Sighting concept. The schema records first_seen / last_seen rather than STIX's valid_from / valid_until.
MISP: the event→attribute hierarchy became sources→sightings; warninglists became allowlist.
OpenCTI: the reference point for what is deliberately left out: no actor or campaign graph, no relationships between indicators, no enrichment.
Computers & Security (2024), "Improving quality of indicators of compromise using STIX graphs": the quality framework and Table 7 bands behind quality_score and scoring_config.

Source

src/threatlens/db/models.py: the schema and the connection PRAGMAs
src/threatlens/db/ingest.py: document identity, source creation, the scored-row write path
src/threatlens/db/conformance.py: type vocabulary, value conversion and row validation
src/threatlens/db/seed.py: scoring_config and allowlist reference data
alembic.ini (repo root): Alembic CLI configuration for a checkout
src/threatlens/migrations/versions/: migration history; src/threatlens/migrations/env.py: batch mode; src/threatlens/db/migrate.py: runs them (python -m threatlens.db.migrate)