Add PostgreSQL support to Docker Compose setup

[nanotaboada]

Description

This issue tracks the enhancement of our current Docker Compose stack by replacing SQLite with PostgreSQL as the primary database engine. While SQLite offered a self-contained and easy setup for early-stage development, it lacks support for production-like concurrency, robustness, and advanced SQL features. Introducing PostgreSQL will make our architecture more realistic, extensible, and better aligned with deployment-grade environments.

Proposed Solution

We will introduce PostgreSQL as the default database in the Compose stack, while still allowing fallback to SQLite for simpler or legacy development scenarios. PostgreSQL offers stronger concurrency handling, persistent storage, and better schema control, which is useful even in PoC environments.

Key benefits:

• Realistic infrastructure: More closely resembles real-world production stacks.
• ORM compatibility: Minimal code changes expected, due to ORM abstractions.
• Environment-based toggling: Allows developers to switch between PostgreSQL and SQLite with a simple config flag.
• Composable architecture: Adds a PostgreSQL service to Docker Compose with persistent volumes.

Suggested Approach

Docker Compose

• Add a new postgres service using the official postgres:17.5-bookworm image.
• Configure it with POSTGRES_DB, POSTGRES_USER, and POSTGRES_PASSWORD via .env.
• Create a persistent volume mount to store data at /var/lib/postgresql/data.

.env and .env.example

• Add POSTGRES_PASSWORD to a .env file (git-ignored).
• Commit a .env.example with placeholder values to guide other devs.

Application Setup

• Update appsettings.json to contain both SQLite and PostgreSQL (Npgsql) connection strings.
• Introduce a boolean flag UsePostgres in appsettings.json.
• In Program.cs, inject the correct connection string based on that flag:

var usePostgres = builder.Configuration.GetValue<bool>("UsePostgres");
builder.Services.AddDbContext<AppDbContext>(options =>
{
    if (usePostgres)
        options.UseNpgsql(builder.Configuration.GetConnectionString("Npgsql"));
    else
        options.UseSqlite(builder.Configuration.GetConnectionString("SQLite"));
});

Seed Strategy

• Reimplement seed logic in PostgreSQL (preferred).
• Alternatively, write a migration script from SQLite if necessary.

Acceptance Criteria

• Docker Compose includes a properly configured postgres service.
• .env.example contains placeholder POSTGRES_PASSWORD.
• App uses PostgreSQL by default and falls back to SQLite when configured.
• PostgreSQL data persists via Docker volumes.
• Developer instructions in README.md are updated accordingly.
• The app starts and connects to PostgreSQL with no runtime errors.
• Legacy SQLite functionality is preserved and toggleable via config.