🏛️ ADR-002: PostgreSQL for Relational Data Storage

Context

SplitDee manages financial ledgers, debt balances, and user transaction records. The storage solution must ensure database transaction reliability, strict relational consistency, and complex querying features.

Alternatives Considered

1. MongoDB: A document store. Offers flexible schemas but lacks standard ACID compliance guarantees and complex relational joining operations, which are crucial for zero-sum debt ledgers.
2. SQLite: Lightweight and file-based. Excellent for local testing but lacks concurrent writing scalability, high-availability setups, and advanced indexing for production.
3. PostgreSQL (Selected): A robust open-source object-relational database.

Decision

We selected PostgreSQL as our primary relational database.

Consequences

• ACID Integrity: Guaranteed database transactions ensure calculations like expense splits, partial settlements, and payments are atomic and durable.
• Relational Operations: Facilitates compound queries across user relationships, memberships, and bill splits.
• Rich Indexing: Advanced indexing support (composite indices on foreign key relationships) enables sub-second query latency as datasets grow.
• JSONB Support: Offers PostgreSQL-native JSONB columns to store dynamic metadata without cluttering relational schemas.