feat(infra): Postgres backend (currently SQLite-only)

[Metbcy]

Problem

The backend uses aiosqlite directly for all persistence. SQLite is great for single-host deploys but caps out for:

• Multi-worker (file-based locking + WAL contention)
• Multi-instance HA
• Operational tooling (backup/restore, replication, observability)

The current code also has direct SQL strings sprinkled throughout backend/securescan/database.py and elsewhere. A Postgres adapter requires rewriting those.

Acceptance criteria

• New SECURESCAN_DATABASE_URL env var (postgres:// or sqlite://, defaults to sqlite for back-compat).
• aiosqlite usage abstracted behind a small adapter layer that supports both.
• Migrations work on both DBs (this likely forces feat(infra): tracked migrations / Alembic #11 — tracked migration system — first).
• All 887 tests pass against Postgres in CI.
• Documented in docs/src/deployment/.

Suggested approach

Use SQLAlchemy 2.0 async (asyncpg driver for postgres, aiosqlite for sqlite). Or: thin adapter that translates a few SQLite-specific SQL idioms to Postgres equivalents (idempotent ALTER TABLE pattern → DO blocks, etc.).

Difficulty

~3 days. Touches every persistence call site; needs careful test coverage.

[snakefood3232]

The current SQLite-based backend limits multi-worker and operational setups as outlined. I'd handle this by implementing an adapter layer using SQLAlchemy 2.0 with asyncpg for Postgres, ensuring backward compatibility with SQLite. This abstraction will encapsulate SQL operations and facilitate seamless migrations, as specified. I can have a PR ready in about 4-5 days, ensuring all 887 tests pass against Postgres in CI. I've built similar database abstraction layers for multi-database support in previous projects, which ensures a robust and flexible solution.

[Metbcy]

The current SQLite-based backend limits multi-worker and operational setups as outlined. I'd handle this by implementing an adapter layer using SQLAlchemy 2.0 with asyncpg for Postgres, ensuring backward compatibility with SQLite. This abstraction will encapsulate SQL operations and facilitate seamless migrations, as specified. I can have a PR ready in about 4-5 days, ensuring all 887 tests pass against Postgres in CI. I've built similar database abstraction layers for multi-database support in previous projects, which ensures a robust and flexible solution.

Looking forward to seeing the PR, I think this is a great idea regarding scale.