Architecture: Move runtime state and replay onto a SQLite-backed state store

[techmore]

Title: Architecture: Move runtime state and replay onto a SQLite-backed state store

Type:
refactor

Severity:
high

Area:
runtime state / realtime delivery / packaged app resilience

Description:
The current runtime relies on in-memory process state plus Socket.IO replay buffers for topology hydration, job replay, report availability, and reconnect behavior. That is workable for a single long-lived process, but it is fragile for packaged desktop usage, reconnects, second tabs, and future offline/history features.

Recent regressions have repeatedly clustered around the same boundary:

• startup-discovered state exists but is not consistently rehydrated into new clients
• reconnect/new-tab behavior depends on connect-time Socket.IO handler correctness
• packaged app issues are hard to diagnose because runtime truth is spread across globals, registries, and replay buffers

A SQLite-backed state store would give the app a durable source of truth for:

• latest network topology and public IP snapshot
• current customer assignment
• last scan target
• active and recent jobs
• report artifacts and availability
• replayable event/log history

Socket.IO can still remain as the live transport, but it should stop being the primary state holder.

Evidence:

• app.py and runtime service layers still coordinate globals, client registries, and replay buffers
• nmapui/handlers/connections.py is a critical reconnect bottleneck for client hydration
• nmapui/jobs.py and client-state abstractions currently keep live state in memory only
• recent packaged regressions around target/topology hydration have shown how fragile this path is

Proposed Fix:
Introduce a SQLite-backed runtime state layer and migrate reconnect/hydration to read from SQLite first.

Suggested phases:

1. Add SQLite tables for runtime snapshot, jobs, reports, and event log
2. Persist startup network discovery and current assignment immediately at startup
3. Persist job lifecycle transitions and report artifacts as they happen
4. Change connect/new-tab hydration to read durable state from SQLite instead of relying on in-memory replay alone
5. Narrow Socket.IO to live progress and invalidation events instead of full source-of-truth state
6. Add browser and packaged-app regression tests around restart/reconnect behavior

Implementation Notes:

• This should not start as a full ORM rewrite; a small typed persistence layer is sufficient
• Keep filesystem scan/report assets on disk; store indexes, metadata, and runtime snapshots in SQLite
• Consider an append-only event log table for replay/debugging and a current-state table for fast hydration
• If transport simplification is desired later, this architecture would make SSE or polling viable for most read paths

Related Issues:

• Support multiple tabs with real-time sync and graceful reopen during scans #112
• Add dedicated Logs tab with searchable runtime and scan history #111
• History tab: keep top-level history UI and add scan-to-scan compare view #110
• Optimization: Persist diff summaries instead of reparsing XML on every /api/scans request #116
• Security: Narrow local UI auth bypass to verified loopback connections #121

[techmore]

Started implementation on codex/sqlite-runtime-store.

Delivered in this first increment:

• added nmapui/runtime_db.py
• added SQLite schema for:
  • runtime_snapshots
  • jobs
  • report_artifacts
  • runtime_logs
• added RUNTIME_DB_FILE at data/runtime.sqlite3
• initialized the runtime store in the real app startup path
• added direct schema/round-trip tests in tests/test_runtime_db.py

This is the durable base for the migration. Next increments will wire live runtime state, report artifacts, and log events into the store so reconnect/new-tab hydration can move off in-memory replay as the source of truth.

[techmore]

Second increment landed on codex/sqlite-runtime-store.

Delivered:

• wired build_client_state_helpers(...) to persist durable runtime snapshots to SQLite
• currently writes:
  • current_customer
  • network_key
  • last_scan_target
• app startup now passes the initialized runtime store into the client-state binding layer
• added regression coverage so future state updates must continue writing snapshots

This means topology/customer/target state is no longer memory-only when those setters run. Next step is to hydrate startup/default runtime state from SQLite on boot and begin persisting report artifacts/log events into the same store.

[techmore]

Third increment landed on codex/sqlite-runtime-store.

Delivered:

• create_runtime_services(...) now hydrates default runtime state from SQLite before app startup continues
• restored defaults currently include:
  • current_customer
  • network_key
  • last_scan_target
• ClientStateRegistry is seeded from the hydrated defaults, so future tabs inherit persisted state even before fresh live events arrive
• added direct tests for both hydrated and fallback startup behavior

This is the first boot-time read path from SQLite. Next increment will persist report artifacts into report_artifacts so historical report availability is durable alongside the runtime state.

[techmore]

Fourth increment landed on codex/sqlite-runtime-store.

Delivered:

• report generation now persists durable artifact rows into SQLite report_artifacts
• applies to:
  • fresh report-generation runs
  • saved-PDF generation runs
• persisted fields now include:
  • scan path
  • customer id
  • target
  • HTML/PDF/XML artifact paths
  • normalized metadata payload
• added regression tests for both fresh-report and saved-PDF persistence paths

This gives the migration branch durable historical report records, not just runtime snapshots. Next increment will persist structured runtime/audit log events into SQLite runtime_logs.

[techmore]

Fifth increment landed on codex/sqlite-runtime-store.

Delivered:

• added /api/runtime/logs backed by SQLite runtime_logs
• Logs tab now hydrates from persisted log history on load before continuing with live in-browser logging
• added route and contract coverage for the persisted logs path

This does not yet persist every runtime event server-side, but it establishes the read/query path for durable logs. Next step is to start appending structured backend events into runtime_logs during startup, job lifecycle, and report completion/failure paths.

[techmore]

Sixth increment landed on codex/sqlite-runtime-store.

Delivered:

• backend now appends structured log rows into SQLite runtime_logs for key flows:
  • startup checks started/completed
  • startup network initialization result
  • traceroute success/failure
  • report generation success/failure
  • saved-PDF generation success/failure
• added direct tests for persisted startup and traceroute log writes

At this point the branch has:

• durable runtime snapshots
• boot-time hydration from SQLite
• durable report artifact records
• persisted log read path
• initial backend event persistence into runtime_logs

Next step is to move reconnect/new-tab hydration to prefer SQLite snapshots and persisted report availability over in-memory-only replay when possible.

[techmore]

Implemented the next SQLite migration increment on .

What changed:

• now prefers persisted SQLite runtime snapshots for , , and when a new tab connects and there is no active scan/report owner.
• Active owner replay still wins when a live scan or report is in progress, so the freshest in-memory state remains authoritative for live jobs.
• Wired into the connection handler through and .
• Added regression coverage in and .

Verification:

• ...........................................sssss........................ [ 34%]
  .s...................................................................... [ 68%]
  ................................................................... [100%]
  205 passed, 6 skipped in 2.15s ->

Commit:

[techmore]

Implemented the next SQLite migration increment on codex/sqlite-runtime-store.

What changed:

• nmapui/handlers/connections.py now prefers persisted SQLite runtime snapshots for current_customer, network_key, and last_scan_target when a new tab connects and there is no active scan/report owner.
• Active owner replay still wins when a live scan or report is in progress, so the freshest in-memory state remains authoritative for live jobs.
• Wired runtime_store into the connection handler through nmapui/app_composition.py and nmapui/app_handler_registration.py.
• Added regression coverage in tests/test_runtime_info_handlers.py and tests/test_runtime_contract.py.

Verification:

• ./.venv/bin/python -m pytest -q -> 205 passed, 6 skipped

Commit:

• de83a47 Hydrate new tabs from SQLite snapshots

[techmore]

Implemented the next SQLite migration increment on codex/sqlite-runtime-store.

What changed:

• ClientJobRegistry now accepts runtime_store and persists job start/update/complete/cancel state into the SQLite jobs table.
• Added RuntimeStateStore.list_jobs(...) for querying persisted jobs by status and recency.
• create_runtime_services(...) now passes runtime_store into the job registry when the registry supports it.
• nmapui/handlers/connections.py now emits a persisted job_status payload for fresh tabs when there is no active in-memory owner but SQLite still has a running/cancelling job recorded.
• Added regression coverage in tests/test_runtime_db.py, tests/test_jobs_modules.py, tests/test_runtime_services.py, tests/test_runtime_info_handlers.py, and tests/test_runtime_contract.py.

Verification:

• ./.venv/bin/python -m pytest -q -> 208 passed, 6 skipped

Commit:

• 6dfd874 Persist active jobs to SQLite

[techmore]

Implemented the next SQLite migration increment on codex/sqlite-runtime-store.

What changed:

• Added a persisted job_events table to the SQLite runtime store.
• make_broadcast_emit(...) now writes replay-worthy emitted events into SQLite for both scan and report jobs while still using the in-memory broadcaster for live fanout.
• nmapui/handlers/connections.py now replays persisted job events after emitting persisted job_status when there is no active in-memory owner.
• Threaded runtime_store through the scan/report runtime binding path so both scan and report workflows persist replay events automatically.
• Added regression coverage in tests/test_runtime_db.py, tests/test_runtime_info_handlers.py, and tests/test_runtime_contract.py.

Verification:

• ./.venv/bin/python -m pytest -q -> 208 passed, 6 skipped

Commit:

• 14698cc Persist replay events to SQLite

[techmore]

Implemented the next SQLite migration increment on codex/sqlite-runtime-store.

What changed:

• build_event_helpers(...) now persists structured backend runtime events into SQLite as they are emitted, including scan feedback, job status, report completion/errors, and update status/errors.
• The Logs tab in static/js/audit_log.js now treats /api/runtime/logs as the primary source for backend runtime activity and refreshes from that durable API on relevant socket events instead of building those entries mainly from browser-side socket listeners.
• Added regression coverage in tests/test_runtime_logs.py and tests/test_runtime_contract.py.

Verification:

• ./.venv/bin/python -m pytest -q -> 209 passed, 6 skipped

Commit:

• f6c2405 Persist structured runtime logs

[techmore]

Implemented the next SQLite migration increment on codex/sqlite-runtime-store.

What changed:

• /api/scans now prefers SQLite report_artifacts as the primary source for completed report rows.
• The route still falls back to metadata/index traversal for legacy scans, failures, or entries not yet present in the runtime store, so behavior stays compatible while reducing filesystem dependence.
• Wired runtime_store into scan-route deps through nmapui/app_composition.py and nmapui/app_handler_registration.py.
• Added regression coverage in tests/test_auth_routes.py and tests/test_runtime_contract.py.

Verification:

• ./.venv/bin/python -m pytest -q -> 211 passed, 6 skipped

Commit:

• c9d25a9 Prefer SQLite report artifacts in scan API

[techmore]

Implemented the next SQLite migration increment on codex/sqlite-runtime-store.

What changed:

• Added a dedicated SQLite-backed reports route: /api/runtime/reports.
• The Reports tab in static/js/reports_tab.js now loads from /api/runtime/reports instead of the mixed /api/scans compatibility endpoint.
• This separates completed report listing from the broader history/failure/legacy scan path and makes the Reports tab depend directly on report_artifacts in SQLite.
• Added regression coverage in tests/test_runtime_logs.py and tests/test_runtime_contract.py.

Verification:

• ./.venv/bin/python -m pytest -q -> 212 passed, 6 skipped

Commit:

• 079562d Add SQLite-backed reports API

[techmore]

Implemented the next SQLite migration increment on codex/sqlite-runtime-store.

What changed:

• Added a dedicated history route: /api/runtime/history.
• The History tab in static/js/reports_tab.js now loads from /api/runtime/history instead of the mixed /api/scans compatibility endpoint.
• The new route prefers SQLite-backed report rows from report_artifacts and falls back to metadata/index traversal for failures and legacy scan entries.
• Wired the extra history route dependencies through nmapui/app_composition.py and nmapui/app_handler_registration.py.
• Added regression coverage in tests/test_runtime_logs.py and tests/test_runtime_contract.py.

Verification:

• ./.venv/bin/python -m pytest -q -> 213 passed, 6 skipped

Commit:

• d7d27e4 Add SQLite-backed history API

[techmore]

Implemented the next SQLite migration increment on codex/sqlite-runtime-store.

What changed:

• persist_report_artifact(...) now stores a parsed asset_snapshot in SQLite report_artifacts and refreshes the payload from disk metadata first, so persisted artifacts carry current diff_summary and baseline data.
• Added RuntimeStateStore.get_report_artifact(...).
• /api/scans/compare now prefers persisted asset_snapshot data from SQLite report artifacts and only falls back to XML parsing when the artifact data is unavailable.
• Added regression coverage in tests/test_runtime_db.py, tests/test_reporting_modules.py, tests/test_auth_routes.py, and tests/test_runtime_contract.py.

Verification:

• ./.venv/bin/python -m pytest -q -> 215 passed, 6 skipped

Commit:

• 8faa603 Persist compare assets in SQLite