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.