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.
| 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.
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 demomake 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/fixtureThe package currently resolves its SQL files from this checkout; install it in editable mode and run it with the repository present.
| 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.
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.
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-dataUse --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/.
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.