Problem
The backend currently uses SQLite (file-based database at ./data/vaultix.db) which is unsuitable for production:
No concurrent write support
No replication or backup strategy
Limited scalability for high-traffic scenarios
TypeORM migrations work differently between SQLite and PostgreSQL (e.g., ALTER TABLE limitations)
This was previously opened as #204 but not fully implemented.
Current State
data-source.ts uses SQLite with DATABASE_PATH env var3 existing migrations written against SQLite dialect
No PostgreSQL-specific migration or connection config
No connection pooling configured
Acceptance Criteria
If DATABASE_URL is set, use PostgreSQL
If only DATABASE_PATH is set, fall back to SQLite (for local dev)
Configure connection pooling with pg driver (min: 2, max: 10 connections)
data-source.ts to support both drivers:
PostgreSQL: type: 'postgres', url from DATABASE_URL
SQLite: type: 'better-sqlite3', database from DATABASE_PATH
Add SSL configuration for production PostgreSQL
Fix any SQLite-specific syntax (e.g., AUTOINCREMENT vs SERIAL)
Ensure UUID type works correctly in PostgreSQL
Verify decimal precision handling for monetary amounts
docker-compose.yml with PostgreSQL 15Pre-configured with vaultix_db, user, password
GET /health/database
Returns connection status, database type, migration status
.env.example with PostgreSQL configuration options
Technical Notes
Use pg and pg-pool packages for PostgreSQL
Connection string format: postgresql://user:password@host:5432/vaultix_db
Set synchronize: false in production (use migrations only)
Consider using @nestjs/typeorm for better NestJS integration
Points: 150 (Medium)
Problem
The backend currently uses SQLite (file-based database at
./data/vaultix.db) which is unsuitable for production:This was previously opened as #204 but not fully implemented.
Current State
data-source.tsuses SQLite withDATABASE_PATHenv varAcceptance Criteria
DATABASE_URLis set, use PostgreSQLDATABASE_PATHis set, fall back to SQLite (for local dev)pgdriver (min: 2, max: 10 connections)data-source.tsto support both drivers:type: 'postgres', url fromDATABASE_URLtype: 'better-sqlite3', database fromDATABASE_PATHAUTOINCREMENTvsSERIAL)docker-compose.ymlwith PostgreSQL 15vaultix_db, user, passwordGET /health/database.env.examplewith PostgreSQL configuration optionsTechnical Notes
pgandpg-poolpackages for PostgreSQLpostgresql://user:password@host:5432/vaultix_dbsynchronize: falsein production (use migrations only)@nestjs/typeormfor better NestJS integrationPoints: 150 (Medium)