Migrate database

[Aleksandergreg]

User Story

As a Development Team Member,
I want to migrate the application's database from SQLite to PostgreSQL,
so that the application can scale effectively, handle increased user load and data volume, and maintain optimal performance as the user base grows.

---

Acceptance Criteria

• A PostgreSQL database instance is provisioned (considering scalability, performance, and cost - managed service or self-hosted).
• The application's database schema is successfully defined and applied to the PostgreSQL instance.
• Data from the existing SQLite database is migrated to the PostgreSQL database, ensuring data integrity.
• The application is successfully reconfigured to connect to and interact with the PostgreSQL database for all data-related operations.
• Core application features dependent on the database function correctly after the migration.
• Research into database migration tools is conducted and documented (e.g., in GitHub Discussions or project wiki), including the rationale for the chosen approach (tool or manual scripting).
• A documented plan exists for performing the production migration with minimized downtime.
• Discussions regarding ORM and migration tool choices are held publicly on GitHub Discussions.

---

Tasks

• Research and decide on the PostgreSQL hosting strategy (e.g., Managed Service like AWS RDS/Google Cloud SQL vs. Self-hosted on VM).
• Provision the selected PostgreSQL database instance.
• Initiate GitHub Discussions thread(s) for ORM and Migration tool choices.
• Research, evaluate, and select (or decide against) a schema migration tool (e.g., Alembic, Flyway, Prisma Migrate, TypeORM Migrations, manual SQL).
• Define/adapt the database schema for PostgreSQL (using the chosen migration tool or SQL scripts).
• Research, evaluate, and select (or decide against) a data migration strategy/tool.
• Develop and test scripts/process for migrating data from SQLite to PostgreSQL.
• Update application dependencies (e.g., database drivers, ORM) if necessary.
• Refactor application code (data access layer, configuration) to use the PostgreSQL connection and handle potential SQL dialect differences.
• Perform thorough testing of the application against the PostgreSQL database in a staging/development environment.
• Develop and document the production cutover plan, focusing on minimizing downtime.
• Execute the migration in the production environment.
• Monitor application performance and stability post-migration.
• Document the final database setup, migration process, and any necessary operational procedures (e.g., backup strategy if not using a managed service with automated backups).

---

INVEST Principle

I: Independent, N: Negotiable, V: Valuable, E: Estimable, S: Small, T: Testable

• Independent: Yes, this is a foundational infrastructure change, largely independent of specific feature development (though future features depend on it).
• Negotiable: Yes, the specific tools (ORM, migration), hosting solution, and migration strategy are negotiable and should be discussed by the team. The core requirement (move to PostgreSQL) is less negotiable per the prompt.
• Valuable: Yes, directly addresses critical scalability and performance limitations of SQLite, enabling future growth and reliability.
• Estimable: Yes, the scope is reasonably well-defined, allowing for effort estimation (though it might be a large estimate, potentially an Epic).
• Small: This is likely not small and might span more than one sprint. Consider breaking it down into smaller, manageable user stories (e.g., Provision DB, Migrate Schema, Migrate Data, Update App Config). However, it's presented here as a single coherent piece of work.
• Testable: Yes, the acceptance criteria define clear, verifiable outcomes (database connection, data integrity, application functionality).

---

Additional Context / Notes (Optional)

• The choice of PostgreSQL is mandated over MySQL for modern stacks, as per discussion points (e.g., PostgreSQL vs MySQL).
• Database hosting choice must balance performance, scalability, maintenance overhead, and cost. Managed services are allowed.
• Using a dedicated migration tool is recommended but not strictly required if a robust manual plan is documented. The research into tools is required.
• Minimizing downtime during the production switchover is a key consideration.
• All discussions around ORM and migration tool selection should leverage GitHub Discussions.

[Aleksandergreg]

We have set it up and tested it locally with Neon, now we just need to do it with our production database (scary hihi)