intrusion-detection/REPORTS.md

Report Inventory

This inventory records the current report surface before moving report reads or writes into SQLite. Generated paths are rooted at REPORT_DIR (normally reports/). The SQLite tables listed below already exist in schema v1 unless noted otherwise.

Artifacts

Area	Producer and formats	Current role and consumers	Classification
copyparty/	Analyzer.write_report() creates timestamped .txt; write_data() creates per-IP CSV/JSON, denied-path CSV, flagged/banned IP TXT, requests.csv, authed_users.json, and mikrotik-block.rsc.	Reports API serves top-level text reports. requests.csv is imported by app.db.backfill; per-run data supports inspection and legacy workflows. Retention prunes generated files.	Request rows and per-scan IP stats are canonical in requests and ip_stats; rendered summaries and exports are derived.
npm/	Same analyzer outputs, plus user-agent and host summary CSV files.	Same consumers as copyparty. requests.csv is a legacy backfill input; retention prunes generated files.	Request rows and per-scan IP stats are canonical in requests and ip_stats; summaries and exports are derived.
app/data/	Consolidated scan writes blocklist_ipv4.txt and blocklist_ipv6.txt.	Blocklist UI/API reads these files; scan history reconstruction and firewall sync use them as current/compatibility inputs. Retention explicitly protects them.	Operational compatibility exports. Canonical blocked targets and events belong in blocklist_entries and blocklist_events; keep files until every consumer uses the DB and git-history contract is replaced.
app/ timestamped JSON	write_blocklist_report() emits scan/firewall change reports when the router is reachable.	Human-readable audit trail containing run delta, cumulative targets, router changes, dry-run state, and errors; retained under report retention.	Derived audit artifact. Preserve structured fields and source scan association in the DB before considering file retirement.
mikrotik/	MikrotikFirewall.save_state_report() writes timestamped JSON snapshots and report data; latest state is also written to data/mikrotik/*.json.	Firewall UI/API displays current state and snapshot history. app.db.backfill currently imports only the latest data/mikrotik snapshot; runtime snapshot persistence uses firewall_snapshots, firewall_address_list_entries, and firewall_rule_entries.	Firewall rows and snapshots are canonical in the DB once persisted. Keep JSON exports during migration and retain any git-diff inputs required by deployment/audit.

Consumers

• GET /api/reports/<source> lists top-level files in the selected report directory; GET /api/reports/<source>/<path> reads a file below it.
• The reports page consumes those endpoints. Scan status and dashboard helpers select the newest source and MikroTik report files for display.
• /api/blocklist reads the consolidated IPv4/IPv6 text files. Scan history reconstruction and firewall sync also consume those files alongside the SQLite ledger and git history.
• RouterOS .rsc exports and firewall synchronization consume generated targets; these are operational outputs, not disposable presentation files.
• app.db.backfill imports per-source requests.csv, blocklist history, and the latest common MikroTik JSON snapshot. It does not currently import all timestamped reports or all historical MikroTik snapshots.
• Report retention removes old generated artifacts after consolidated scans, subject to configured age/file-count limits; the consolidated blocklist files are protected.

Restructuring Implications

• Keep requests, ip_stats, blocklist tables, and firewall snapshot tables as the queryable source of truth for facts they already represent. Avoid copying those facts into a generic report blob.
• A future report-artifact record should store metadata and structured fields that have no canonical home yet. Rendered text/CSV/JSON should be generated from those records or remain an explicit export.
• Preserve source paths, generated timestamps, scan IDs, and hashes during import so DB records can be traced back to original files.
• Do not retire blocklist exports, RouterOS scripts, or source logs until their consumers and audit requirements have explicit replacements.

Database and API Design

Canonical Data

Continue treating normalized domain tables as canonical for facts they already represent:

• requests for parsed request events.
• ip_stats for per-IP/per-scan aggregates.
• scans and scans_sources for run status, parameters, and source counts.
• blocklist_entries, blocklist_reasons, and blocklist_events for block decisions and history.
• firewall_snapshots, firewall_address_list_entries, and firewall_rule_entries for router state.

Add a report_artifacts table only for report-specific summaries that cannot be reconstructed from those canonical rows. Proposed columns:

Column	Purpose
id	Integer primary key.
source	copyparty, npm, app, or mikrotik. Keep as text because log_sources currently covers only request sources.
scan_id	Nullable link to the generating scan; legacy imports may not have a known scan.
report_type	Stable type such as analyzer_summary, blocklist_change, or firewall_snapshot_summary.
generated_at	Original generation timestamp when known; otherwise import time plus an explicit unknown/original-time flag in metadata.
schema_version	Version of the structured payload shape.
payload_json	Structured report-specific summary, not a duplicate of requests or raw logs.
original_path	Relative source path for provenance and legacy lookup.
content_sha256	Hash of imported source content for integrity and idempotent import.
created_at	Database import/creation timestamp.

Use a unique index on (original_path, content_sha256) for idempotent file imports. Keep payload schemas type-specific and versioned. Do not copy requests.csv, ips.json, firewall rows, or blocklists wholesale into payload_json; query their canonical tables and generate exports from those tables. If a rendered text report is retained as a database artifact, store its report-specific structured inputs and render text on demand rather than using opaque text as the only source of truth.

API Transition Contract

• Add GET /api/reports with optional source, type, since, until, limit, and opaque cursor filters. Return a stable list of artifact metadata: id, source, scan_id, type, generated_at, and available export formats. Enforce a bounded default and maximum page size.
• Add GET /api/report-artifacts/<artifact_id> for the structured payload and provenance metadata. Use a distinct path because /api/reports/<source> already owns the one-segment route shape.
• Add GET /api/report-artifacts/<artifact_id>/export for supported report types and formats (initially JSON and, where useful, CSV/TXT). Generate exports from canonical tables and versioned artifact payloads; do not accept filesystem paths from a database-backed export request.
• Preserve the current source/path routes as a read-only compatibility fallback while files remain. The reports page should consume DB metadata first, then offer legacy files when no imported artifact exists. Return stable JSON errors for unknown IDs, unsupported formats, and unavailable DB state.
• Keep ordering deterministic (generated_at DESC, then id DESC), validate filter values, and use cursor pagination for large histories. Do not expose absolute server paths in list responses.

Sensitive and Large Content

Do not include raw request lines or full request histories in report payloads; those belong in the dedicated log-storage design and require separate access and retention controls. Keep high-volume CSV exports generated on demand. Treat IP addresses, paths/query strings, user names, user agents, and firewall state as sensitive operational data: the existing web server must remain localhost-only, payloads should include only fields needed by the report, and new endpoints must not expose raw log data by default. Backups and exported files inherit the same access and retention requirements as the database.

Migration Implementation Status

• Schema v2 adds report_artifacts with source, scan link, report type, generation time, payload version, original relative path, and SHA-256 hash.
• python -m app.db.backfill --dry-run --reports-only inventories supported report files by source and format. The importer is idempotent, preserves malformed JSON as text with a parse error, reports unreadable/oversized files, and never modifies source files. It skips exports owned by canonical tables (for example requests.csv and current blocklists).
• New analyzer text summaries and consolidated blocklist change reports are written to SQLite and their existing files. Firewall snapshots continue to be persisted in the normalized firewall tables.
• The Reports page lists database artifacts first, supports cursor pagination and JSON/CSV/TXT downloads, and retains the existing file-backed routes as a fallback for unmigrated files.
• Validation covers idempotency, malformed/oversized inputs, source/format counts, backup/restore, dual-write payloads, and API fallback. A workspace dry-run found 76 files: 60 eligible, 16 canonical exports skipped, and none unmappable at the time of the check.
• No report files have been retired. Keep this gate closed until every listed consumer has moved or has an explicit compatibility requirement, and a production backup/restore and parity review has been approved.