Overhaul persistence

[josecelano]

We should review the database layer.

Context

• User has a request for new driver implementation: PostgreSQL
• It seems adding Postgres is tricky with the crate we are using: r2d2
• We don't have migrations. We are using sqlx in the Index.

Problem

• I'm not sure if the package we are using is still the best fit for this project.
• I think we should include migrations if we start making schema changes more often, and it seems we will. For example, I'm planning to add more tables to persist metrics. See Overhaul stats: Add extendable-labeled metrics to the tracker-core package #1437 (comment)

Proposal

In the middle term (before adding more tables), we should:

• Research to confirm if the current crate is the best one. Change it if it's not.
• Add migrations.
• Split the database trait into many different traits per context. Otherwise, it will end up having too many methods.
  I think I will implement the metrics persistence with the current drivers because I want to finish the[+] stats overhaul,[/+][-] stats overhaul,[/-] but we should consider implementing this epic before continuing to add more tables (complexity) to the current persistence layer, especially if we decide to change it.

cc @da2ce7

The work is split into sequential sub-issues so each step can be independently reviewed and merged. See the implementation plan in the comments below.

[josecelano]

Alternatives

• https://www.sea-ql.org/

[josecelano]

An article about Rust, PostgreSQL, and sqlx by @skerkour

https://kerkour.com/rust-postgres-everything

cc @da2ce7

[josecelano]

Implementation Plan

The implementation has been broken down into 9 sequential sub-issues. Each one is independently reviewable and mergeable into develop before PostgreSQL support is complete.

The full specs have been merged into develop via PR #1702. Links below point to each spec file.

---

Phase 1 — Make the change easy

#	Sub-issue	Short description	Spec	Status
1	1525-01 Persistence test coverage	Add a Bash compatibility-matrix runner that tests the tracker against all supported SQLite, MySQL, and PostgreSQL versions	1525-01-persistence-test-coverage.md
2	1525-02 qBittorrent E2E runner	Add a Rust binary that runs a full seeder→tracker→leecher download using real qBittorrent clients in containers	1525-02-qbittorrent-e2e.md
3	1525-03 Persistence benchmarking	Add a benchmark runner (--before-image/--after-image CLI) that measures announce throughput so every subsequent step can be compared against a concrete baseline	1525-03-persistence-benchmarking.md
4	1525-04 Split persistence traits	Split the monolithic Database trait into four narrow context traits (SchemaMigrator, TorrentMetricsStore, WhitelistStore, AuthKeyStore) plus a blanket Database aggregate supertrait	1713-1525-04-split-persistence-traits.md
4b	1525-04b Migrate consumers to narrow traits	Replace every Arc<Box<dyn Database>> consumer with the specific narrow trait it actually needs; introduce DatabaseStores struct to replace Arc<Box<dyn Database>> in TrackerCoreContainer	1525-04b-migrate-consumers-to-narrow-traits.md
5	1525-05 Migrate SQLite and MySQL to sqlx	Replace r2d2/rusqlite/mysql with async sqlx connection pools for both existing drivers, without introducing PostgreSQL yet	1525-05-migrate-sqlite-and-mysql-to-sqlx.md
6	1525-06 Introduce schema migrations	Replace raw DDL with sqlx::migrate!() and add a legacy-bootstrap path that history-aligns pre-v4 databases	1525-06-introduce-schema-migrations.md
7	1525-07 Align Rust and DB types	Widen NumberOfDownloads from u32 to u64 and the corresponding columns from INTEGER to BIGINT via a versioned migration	1525-07-align-rust-and-db-types.md

Phase 2 — Make the easy change

#	Sub-issue	Short description	Spec	Status
8	1525-08 Add PostgreSQL driver	Add PostgreSQL as a first-class backend using async sqlx, with four history-aligned migrations, testcontainers-based driver tests, container config, and user-facing documentation	1525-08-add-postgresql-driver.md

---

EPIC planning doc: 1525-overhaul-persistence.md