feat: optional PostgreSQL with SQLite default, verified switching and performance evidence #2144

Proposal

Offer PostgreSQL while keeping SQLite as CoreScope’s default. Operators must be able to select a backend at installation or explicitly switch during an update. Routine updates preserve the existing choice and data. This supersedes the original full-conversion scope and relates to #1442 and #2146; maintainer acceptance is not assumed.

Required behavior

• SQLite remains a complete default installation without a PostgreSQL service or PostgreSQL credentials. PostgreSQL is an explicit option.
• Preserve API/WebSocket behavior, telemetry read/write separation, separate account storage and restricted approved-channel access on both backends.
• Persist backend selection. Refuse ambiguous configuration and never silently fall back to an empty database.
• Support explicit offline switching in both directions: stop writers, retain recovery copies, preserve IDs and authentication state, verify counts/relationships/digests, and only then cut over. Document the ingestion gap, failure recovery and rollback boundary.
• Keep configuration, installation/update tooling, diagnostics and native backup/restore paths working for both choices.
• Test fresh/default installs, existing SQLite updates, PostgreSQL updates, switches, interrupted migrations, permissions, account/session behavior, browser journeys and restores.

Performance evidence

Improve common bottlenecks with separately reviewable changes, coordinated with #2146/#2147. Measure each backend using the same logical working data, durability and resource limits; reduced data coverage is not a memory optimization. Publish CPU/heap, wait/latency and retention evidence, including regressions.

The existing #2145 report describes the PostgreSQL-only candidate and remains historical evidence. The optional-backend implementation needs its own validation and measurements.