Skip to content

About

Reproducible PostgreSQL analytics for synthetic security program data: historical backlog, remediation episodes, and rollout progress.

Resources

Stars

0 stars

Watchers

0 watching

Forks

Repository files navigation

Security Program Analytics

A reproducible PostgreSQL lab for answering security program questions from event histories. The database tracks fictional assets, findings, teams, rollout programs, and state changes. SQL reconstructs what was true at a chosen cutoff and computes backlog, exceptions, remediation episodes, movement, and rollout completion. Python loads synthetic data, binds report parameters, exports results, and runs tests.

All data is synthetic. Names, findings, deadlines, and outcomes are invented for this portfolio exercise. They do not describe an employer or production security program.

What the reports answer

Report Question SQL
R1 How many actionable findings and accepted risks are assigned by team and severity? Backlog
R2 Which actionable findings are overdue, unowned, or missing deadlines? Exceptions
R3 How did the historical backlog change at weekly cutoffs? Weekly backlog
R4 How long did completed remediation episodes take, including reopened findings? Remediation time
R5 Which transitions created, reopened, or closed backlog? Backlog flows
R6 What share of eligible rollout tasks is done, and what work remains? Rollout progress

The definitions and denominator rules are in the metric contract. SQL performs state reconstruction, joins, grouping, window calculations, and percentiles. Python does not recalculate those business metrics.

Quickstart

Requirements: Python 3.12, Docker with Compose, and a shell with make. Compose starts PostgreSQL 17 on 127.0.0.1:55432 with disposable credentials for fictional data. No paid service is required.

Verified in GitHub Actions with PostgreSQL 17.11 and Python 3.12. Compose and CI pin the same multi-architecture postgres:17 manifest: sha256:3b0cda93be7c8dba2ed484d19393d93845dc3dafb88973e248ba8e6d99d784e1. Exact Python dependencies are in requirements.lock.txt.

git clone https://github.com/ezesecops/security-program-analytics.git
cd security-program-analytics
cp .env.example .env
make setup
source .venv/bin/activate
make db-up
python -m security_analytics.cli doctor
make init
make fixture
make test
make demo

make setup installs exact third-party versions from requirements.lock.txt, then the local package in editable mode. Python reads DATABASE_URL and TEST_DATABASE_URL from the shell or this project's .env file; shell variables take precedence. Copying .env.example works because the CLI explicitly loads .env. The test database is separate from the demo database. The integration suite fails if PostgreSQL is unavailable.

make init is safe to rerun: it creates missing tables/functions and never deletes data. make fixture rejects an existing dataset. To replace only the configured, loopback demo dataset, run python -m security_analytics.cli reset-demo --confirm security_analytics, then seed or load again. It never resets the test database or prunes Docker resources.

The same commands can be run without Make:

python -m security_analytics.cli init
python -m security_analytics.cli init-test-db
python -m security_analytics.cli seed --dataset fixture
python -m security_analytics.cli validate-data
python -m pytest -q
python -m security_analytics.cli report --report all \
  --cutoff 2026-01-16T00:00:00Z \
  --window-start 2026-01-01T00:00:00Z \
  --weekly-start 2026-01-05T00:00:00Z \
  --weekly-end 2026-01-12T00:00:00Z \
  --out artifacts/examples/fixture

The package currently resolves its SQL files from this checkout; install it in editable mode and run it with the repository present.

Model and time semantics

Table Grain Main relationship
teams One fictional team Referenced by historical owner fields
assets One fictional asset Parent of findings and rollout tasks
findings One tracked issue on an asset asset_id → assets
finding_events One complete finding-state snapshot finding_id → findings
rollouts One security rollout program Parent of rollout tasks
rollout_tasks One asset's task in a rollout rollout_id → rollouts, asset_id → assets
rollout_task_events One complete task-state snapshot task_id → rollout_tasks

An event includes the full state after a change. To report at cutoff T, SQL first discards events at or after T, then chooses each entity's latest eligible event by (occurred_at, event_id). A finding detected exactly at T is absent. A resolved finding can later reopen and start a new actionable episode. Remediation counts those completed episodes; it does not count false positives, not-applicable outcomes, or accepted risk as technical fixes. More detail is in the schema notes.

Verified fixture results

The hand-reviewed fixture contract is an independent correctness oracle. PostgreSQL 17 reproduced its results: four actionable findings, one accepted risk, and two exception findings at the January 16 cutoff. The tables below summarize the actual CSV/JSON exports, preserved from the successful CI run.

Jan 16 team/severity Actionable Accepted risk
Alpha / critical 1 0
Alpha / high 1 0
Alpha / medium 0 1
Beta / high 1 0
Beta / low 1 0

R1 also includes zero-filled rows for every severity of all three teams and Unowned.

Movement, Jan 1–16 Count
New findings 9
Reopened episodes 1
Remediated episodes 3
False-positive closures 1
Not-applicable closures 1
Risk acceptances 1
Ending actionable backlog 4
Rollout at Jan 16 Eligible Done Completion
Container Baseline 2 1 50%
Evidence Automation 2 1 50%
Empty Pilot 0 0 No eligible tasks

The report manifest records dates, table counts, SQL hashes, data hash, and source commit. The decision memo turns these fictional results into specific next actions.

Larger synthetic datasets

python -m security_analytics.cli generate \
  --profile demo --seed 42 \
  --start 2025-10-01T00:00:00Z --end 2026-02-01T00:00:00Z \
  --out data/generated/demo-seed-42
python -m security_analytics.cli reset-demo --confirm security_analytics
python -m security_analytics.cli load --input data/generated/demo-seed-42
python -m security_analytics.cli validate-data

Use --profile portfolio for 12 teams, 2,000 assets, and 25,000 findings. Each generated directory has seven CSV files and a manifest with exact row counts, file SHA-256 hashes, seed, range, and assumptions. Loading rejects an occupied database, changed files, malformed rows, and invalid history; it commits the seven tables together. data/generated/ stays out of Git because it is reproducible. The small fixture CSVs are in data/fixture/january-2026/.

Verification and performance

The verified CI run passed 38 tests with zero failures, errors, or skips on PostgreSQL 17. Tests cover cutoffs, ties, reopening, missing data, closure categories, rollout denominators, both generated profiles, exports, and fractional timestamp precision. The JUnit result and verification record identify the tested commit.

In the original CI benchmark, one selective latest-event diagnostic query went from 55.5995 ms to 0.058 ms median execution time after adding a 3 MiB event-history index. Each condition used three warmups and ten measured runs; result hashes matched. This measures one query over synthetic data with warm caches on a shared runner, not overall application or production performance. Plans, timings, and limitations document the experiment. Implementation status records release verification and remaining practice checkpoints.

This lab reconstructs effective event time using the data currently loaded. It does not preserve when a late event was received, team-name history, asset retirement, risk-acceptance approval/expiry, or per-team backlog conservation across transfers. Completed-episode percentiles do not predict when unresolved work will close. These limits and the illustrative deadline policy are documented in the metric contract.

About

Reproducible PostgreSQL analytics for synthetic security program data: historical backlog, remediation episodes, and rollout progress.

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages