# 1. Use PostgreSQL as the primary datastore

## Status

Accepted

## Context

The web application needs a primary datastore for its core domain data: user accounts, orders, inventory, and the audit trail that ties them together. The data is strongly relational — orders belong to users, inventory reservations reference orders, and the audit trail must stay consistent with the records it describes. Several reads join across three or four tables, and a handful of reports aggregate across the whole dataset.

Up to now the team has used SQLite for local development and has no shared datastore in production. The choice has to be made before the first production deploy, because migrating a live relational dataset between engines later is expensive and risky.

The team is small (four developers), runs on a single cloud VM to start with, and has prior experience with relational databases but no dedicated database administrator. Whatever we choose must be operable by the developers themselves, with managed hosting as an option when the workload grows.

## Decision

We will use PostgreSQL 16 as the primary datastore for all core domain data. The application will connect through a single connection pool, all writes will go through transactions where more than one table changes, and schema changes will be applied through versioned migrations kept in the repository. No other datastore will hold authoritative domain data.

## Consequences

### Positive

- Strong consistency guarantees: multi-table writes can be wrapped in a single transaction, so an order and its inventory reservation can never disagree.
- Mature ecosystem and tooling: backups, replication, monitoring, and managed hosting are all well understood, and hiring for PostgreSQL experience is straightforward.
- JSONB columns cover the few semi-structured fields (order metadata, audit payloads) without a second datastore.
- Rich querying: window functions, CTEs, and full-text search handle the reporting needs without exporting data elsewhere.
- A clear upgrade path: managed hosting (e.g. a cloud provider's managed PostgreSQL) is available when we outgrow a self-managed VM.

### Negative

- The team must learn to operate and tune it: connection pooling, vacuuming, and index maintenance are now our responsibility.
- Vertical scaling has a ceiling: if write volume outgrows a single primary, we will need read replicas or sharding, both of which add complexity.
- Schema migrations must be treated as deployments: a bad migration can lock tables and take the application down, so migrations need review and a rollback plan.
- Heavier than SQLite for local development: each developer now runs a local PostgreSQL instance (via container), which is slightly more setup than a file-based database.

## Alternatives Considered

### MySQL

The most obvious alternative, and the one the team knows second best. Rejected for three reasons: its default handling of transactions and foreign keys has historically been less strict, which matters for the audit-trail consistency requirement; its JSON support is weaker than PostgreSQL's JSONB for the metadata queries we need; and the team judged PostgreSQL's query planner and tooling stronger for the reporting workload. MySQL would have been a defensible choice, but it offered nothing PostgreSQL did not, and slightly less where it mattered.

### SQLite

Already in use for local development, and tempting because it is zero-operations: a file, no server, no tuning. Rejected for production because it allows only a single writer at a time, which caps write concurrency well below what the order flow needs at peak; it has no built-in replication for the backup and failover story; and running the same engine in development and production matters — SQLite's type system and locking behaviour differ enough from a server database that bugs found late would be expensive. It stays as a fallback for tests that need a throwaway database.

### MongoDB

Considered because the order metadata and audit payloads are semi-structured, and a document store would have made those fields trivial to store. Rejected because the core data is relational, not document-shaped: orders, users, inventory, and audit records are heavily cross-referenced, and expressing those joins in application code would have pushed consistency logic out of the database and into every service that touches it. Multi-document transactions exist, but they are not the database's strength, and the team has no operational experience with it. PostgreSQL's JSONB gives us the document flexibility we need for the few semi-structured fields without giving up the relational guarantees for the rest.
