Skip to main content

MVP Architecture

Overview​

The architecture here is the result of genuine debate within the team across two fault lines:

PostgreSQL vs Firestore as the data store — one camp valued Firestore's simplicity, real-time subscriptions, and zero-ops scaling; the other valued PostgreSQL's ACID guarantees, relational integrity, rich querying, and auditability — all of which matter deeply for an accounting ledger.

REST vs event-driven — one camp preferred the familiarity and control of a REST API; the other wanted Firestore's effortless real-time data push to the UI with no polling or manual refresh logic.

Rather than choosing a side, the current design brings the best of both worlds together: Firestore delivers fast, effortless, real-time data to the UI — while PostgreSQL provides the strict ACID transactions, relational integrity, audit compliance, and schema control via Flyway that financial data demands. A Node.js sync service bridges the two, keeping Firestore current whenever PostgreSQL changes.

Data Flow​

PostgreSQL — Source of Truth​

All ledger data lives in Cloud SQL (PostgreSQL). Every write originates here.

Why PostgreSQL and not Firestore?​

PostgreSQLFirestore
TransactionsFull ACID transactions across multiple tablesLimited — no cross-document transactions without batching
Relational integrityForeign keys, constraints, cascadesNo referential integrity
QueryingFull SQL — joins, aggregations, window functionsLimited — no joins, no aggregations server-side
Audit & complianceRow-level history, triggers, CDC toolingDifficult to audit; no native CDC
Double-entry accountingEnforcing balanced debits and credits requires transactionsCannot guarantee atomicity across a journal entry's lines
Schema controlStrict schema via Flyway migrationsSchemaless — easy to drift

For an accounting ledger, correctness and consistency are non-negotiable. PostgreSQL gives us the transactional guarantees and query flexibility that Firestore cannot. Firestore is used purely as a read cache to deliver real-time UI updates — not as a data store.

Node.js Service​

The Node.js service (in the node repo) runs two responsibilities:

REST API​

Fastify REST endpoints receive all write requests from the UI. The API handles:

  • Authentication via Firebase bearer token verification
  • Business logic (e.g. validating journal entry balance)
  • CRUD against PostgreSQL

The UI never writes directly to PostgreSQL or Firestore for ledger data.

Firestore Sync Service​

A long-running listener watches for PostgreSQL change notifications and writes the updated records to Firestore, keeping the UI's read cache current.

How it works:

  1. A trigger on PostgreSQL fires pg_notify on every INSERT, UPDATE, or DELETE to the ledger and IAM tables.
  2. The Node sync service holds an open connection to PostgreSQL and listens for these notifications.
  3. On notification, it reads the changed row and writes it to the corresponding Firestore document.
  4. The UI's onSnapshot listener receives the Firestore update and re-renders automatically.

Near-term implementation: pg_notify with a pg.Client listener — simple, low-latency, no additional infrastructure required.

As we approach MVP, we should consider upgrading to a proper Change Data Capture (CDC) pipeline for reliability and replay capability:

  • Staying on Google Cloud SQL → Datastream feeds changes into Pub/Sub or BigQuery without requiring a persistent connection from Node.
  • Moving off Google Cloud → Debezium streaming into Kafka gives a durable, replayable event log that multiple consumers can subscribe to.

Firestore — Read Cache​

Firestore holds a near-real-time copy of the ledger data that the UI subscribes to.

  • Firestore Security Rules enforce tenant isolation — a user can only read documents belonging to their own organisation. Cross-tenant data access is prevented at the database level.
  • The UI uses onSnapshot listeners to receive live updates as the sync service writes changes.
  • Firestore is never written to by the UI directly for ledger data. All writes go through the Node REST API → PostgreSQL → sync trigger → Firestore.

UI​

The React UI (in the ui repo):

  • Reads ledger data from Firestore via onSnapshot subscriptions, giving users a live view of the ledger that updates whenever any user makes a change.
  • Writes all changes through the Node REST API. The UI never touches PostgreSQL directly.
  • Authenticates via Firebase Auth. The bearer token is forwarded to the Node REST API on every request and verified server-side.