[Feature Discussion] PostgreSQL database provider support

[ausip]

Summary

I'd like to open a discussion about adding PostgreSQL as a supported database backend, with the intention of developing this as a contribution for potential adoption into the main branch.

Motivation

Our deployment runs Gokapi as a Docker container on AWS Lightsail, connected to an existing AWS RDS PostgreSQL instance. We already operate and maintain this Postgres infrastructure for backups, logging, reporting and general infrastructure consolidation — adding a Postgres provider to Gokapi would allow us to bring it into that ecosystem rather than managing a separate SQLite or Redis dependency alongside it.

Why not SQLite or Redis?

• SQLite is unsuitable for our environment as it relies on local filesystem persistence, which conflicts with our container deployment model
• Redis would solve the SQLite issue but adds another infrastructure dependency we'd prefer to avoid — we already have Postgres serving these needs

Proposed Approach

The existing database abstraction layer (dbabstraction.Database interface, provider pattern) appears well-suited for this addition. Our proposed implementation would:

• Add a TypePostgres constant to DbAbstraction.go
• Create a new provider/postgres/Postgres.go implementing the Database interface, modelled closely on the existing SQLite provider
• Add a postgres:// URL scheme to the dispatcher in Database.go
• Extend Setup.go with the relevant connection fields
• Update Migration.go to support Postgres as a migration source/destination
• Add a PostgreSQL driver (pgx) to go.mod

Questions Before We Begin

Before investing development effort, we'd value your feedback on:

1. Is this a direction you'd be open to accepting upstream?
2. Do you have any architectural preferences (driver choice, connection pooling approach, SQL dialect considerations)?
3. Are there any planned changes to the database abstraction layer we should be aware of?

We're happy to include tests, documentation and any migration tooling you'd require for a pull request to be accepted.