Skip to main content

ADR: Postgres-First Write Order with Firestore as Downstream

Status​

Provisionally Accepted / In Development

Context​

Our stack uses both PostgreSQL (relational, calculation engine) and Firestore (real-time transport, offline cache). We need both because:

  • Firestore provides real-time listeners, offline-first support, and direct client access — eliminating complex UI re-query logic when data changes across sessions
  • Postgres provides the relational query power we need for accounting: window functions, aggregations, joins, and period-based calculations that Firestore cannot express

The question is: which store do we write to first?

The Two Approaches​

Option A: Firestore-First (write to Firestore, sync to Postgres)​

  1. Client writes to Firestore
  2. A trigger/function syncs the document to Postgres asynchronously

Option B: Postgres-First (write to Postgres, sync to Firestore)​

  1. Client writes to Postgres via REST API
  2. pg_notify fires on commit
  3. A listener picks up the notification and writes the document to Firestore

Decision​

We are migrating to Option B: Postgres-first.

Option A (Firestore-first) was our original implementation and that code remains in the codebase.

Option B is actively in development and is proving more stable — particularly around balance consistency and failure recovery.

Reasoning​

1. The sync window problem favours Postgres-first​

The register view fetches running balances from Postgres whenever the Firestore snapshot fires. The question is: when the snapshot fires, is Postgres ready?

  • Firestore-first: Firestore fires immediately. Postgres hasn't received the sync yet. The balance re-fetch returns a stale result that doesn't include the new entry. The UI briefly shows the new entry with a wrong balance.
  • Postgres-first: Postgres is committed before pg_notify fires. pg_notify triggers the Firestore write. By the time the Firestore snapshot reaches the client, Postgres already has the entry. The balance re-fetch is always consistent.

This is a fundamental sequencing guarantee. Firestore-first cannot provide it without polling or a secondary confirmation step.

2. Postgres is the source of truth for financial data​

Accounting data requires ACID guarantees. Journal entries must balance. Foreign key constraints must hold. Status transitions must be controlled. Postgres enforces all of this at the database level.

Firestore is a document store with eventual consistency. It has no notion of referential integrity, no constraints, and no transactions that span the document graph the way a relational DB does. Treating Firestore as the write-primary for financial data means your source of truth has weaker guarantees than your reporting layer — which is backwards.

3. Failure modes are easier to reason about​

  • Postgres-first failure: Postgres write succeeds, Firestore sync fails. Firestore is stale until the sync retries. The fix is a replay of pg_notify events or a one-time reconciliation job. Postgres is correct; Firestore catches up.
  • Firestore-first failure: Firestore write succeeds, Postgres sync fails. Postgres is missing data. Your reporting, balance calculations, and audit trail are now wrong. The fix requires detecting the gap and backfilling — harder to detect, harder to recover from.

In accounting, a wrong balance is worse than a delayed real-time update.

4. Business logic belongs at the API layer​

Firestore security rules are not a substitute for business logic. The REST API layer (Fastify/Node) is where we enforce:

  • Entry status rules (draft → posted → voided)
  • Fiscal period validation
  • Balanced journal line requirements
  • Permission checks via Firebase token + Postgres RBAC

Firestore-first would require duplicating these rules in Firestore security rules or Cloud Functions — two places to keep in sync instead of one.

Tradeoffs Accepted​

ConcernResolution
Offline writes can't go to PostgresOffline writes go to Firestore only. On reconnect, Postgres receives the sync and recalculates. Balances shown offline are explicitly provisional — flagged as such in the UI.
Added latency for Firestore updateAcceptable. The pg_notify → Firestore path is fast (sub-second in normal operation). Real-time feel is preserved.
Dual-write operational complexityManageable. The sync is one-directional (Postgres → Firestore), which is easier to monitor, replay, and debug than a two-way sync.

Consequences​

  • All client writes go through the REST API, not directly to Firestore (except offline drafts)
  • Firestore documents are always a downstream reflection of Postgres state
  • Balance calculations, reports, and aggregations are always served from Postgres
  • The TypeScript createAccountRegister function is retained as an offline fallback only — it is not the authoritative calculation path
  • Running balances use a SQL window function query and are never stored as derived state in either Firestore or a separate table
Last updated on