# ADR-006: SQLite to PostgreSQL

# ADR-006: SQLite for Development, PostgreSQL for Production

**Status:** SUPERSEDED by [ADR-014: PostgreSQL-Only Architecture](ADR-014-postgresql-only.md) (2026-03-03)
**Date:** 2026-02-21
**Deciders:** John (AI Director)
**Category:** Database

---

## Context

Drop requires a database strategy that supports rapid local development while being production-ready for a financial application. The key tension is between developer velocity (zero-config local setup) and production reliability (concurrent writes, ACID transactions, managed backups).

| Database | Local Setup | Concurrent Writes | Managed Services | Financial Compliance |
|----------|------------|-------------------|------------------|---------------------|
| **SQLite** | Zero config, file-based | Poor (single writer) | None | Not suitable for production |
| **PostgreSQL** | Docker required | Excellent (MVCC) | AWS RDS, Aurora | Industry standard for fintech |
| **MySQL** | Docker required | Good | AWS RDS, Aurora | Common but less feature-rich |

Drop's data model includes 19 tables with foreign keys, transactions requiring atomicity (balance deduction + transaction record), and compliance tables needing reliable concurrent access for audit logging. SQLite handles development workloads but cannot support concurrent writes from multiple App Runner instances.

## Decision

**Use SQLite (`better-sqlite3`) for development and PostgreSQL for production, with a dual-driver abstraction layer.**

Driver detection is automatic based on environment:
```
const USE_PG = !!process.env.DATABASE_URL;
```

When `DATABASE_URL` is set (production), PostgreSQL is used. Otherwise, SQLite with WAL mode is used.

```mermaid
graph LR
    subgraph dev["Development"]
        app_dev["Drop App"] --> dal["Database Access Layer<br/>(db.ts)"]
        dal --> sqlite["SQLite<br/>(better-sqlite3)<br/>./data/drop.db"]
    end

    subgraph prod["Production"]
        app_prod["Drop App (x N)"] --> dal2["Database Access Layer<br/>(db.ts)"]
        dal2 --> pg["PostgreSQL<br/>(AWS RDS)<br/>DATABASE_URL"]
    end

    dal -.->|"Same API:<br/>query(), getOne(),<br/>run(), transaction()"| dal2
```

See [ADR-010: Dual Database Driver](ADR-010-dual-database-driver.md) for the abstraction layer details.

## Consequences

### Positive
- Zero-config local development: `npm run dev` just works, no Docker needed for DB
- Production-grade concurrent access with PostgreSQL MVCC
- AWS RDS provides automated backups, point-in-time recovery (critical for financial data)
- Same application code runs against both databases via abstraction layer
- SQLite WAL mode provides good read performance during development

### Negative
- SQL compatibility layer adds complexity (see ADR-010)
- Subtle behavioral differences between SQLite and PostgreSQL (e.g., type coercion, datetime handling)
- Cannot test PostgreSQL-specific features locally without Docker
- Must test against both databases in CI

### Risks
- **SQL dialect drift:** A query that works in SQLite may fail in PostgreSQL. Mitigation: dual-driver abstraction normalizes SQL; CI tests against both.
- **Performance characteristics differ:** SQLite is faster for single-connection workloads. Mitigation: performance testing against PostgreSQL before production launch.

## References

- [ADR-010: Dual Database Driver](ADR-010-dual-database-driver.md) -- Abstraction layer implementation
- [Database Schema](../../backend/DATABASE-SCHEMA.md) -- Full schema documentation
- [Database Design](../database/database-design.md) -- Database architecture decisions
- [Migration Strategy](../database/migration-strategy.md) -- SQLite to PostgreSQL migration plan