Firebase Firestore: Enterprise Multi-Tenant Accounting Verdict
This was the original analysis that drove the decision to use PostgreSQL as the system of record. The Firestore limitations listed in "Where Firestore Falls Short" are still accurate — Firestore is not and will never be the system of record for Acctz. However, the conclusion has evolved: Firestore is intentionally kept as the real-time read layer (synced from PostgreSQL via pg_notify triggers), not eliminated. The codebase cross-reference section reflects the early prototype state and is no longer current. Spring Boot references are outdated — the backend is now Node.js/Fastify.
An honest evaluation of Firestore as a backend for a multi-tenant, double-entry accounting platform — and how the existing Acctz architecture already solves the problems it surfaces.
Where Firestore Falls Short
1. No native multi-tenancy isolation
Firestore in Native mode has no built-in multi-tenancy support. The workaround (collection hierarchies like tenants/{id}/journal-entries) doesn't let you track per-tenant resource usage, making "noisy neighbor" problems invisible. You can't throttle, bill, or audit at the tenant level without building that layer yourself.
2. Relational accounting data in a document store is a mismatch
Double-entry accounting is fundamentally relational: journal entries reference accounts, accounts belong to entities, entities belong to users with roles. Firestore forces you to denormalize this, creating sync/consistency headaches. There are no foreign keys, no referential integrity constraints, and no joins.
3. Aggregation and reporting are severely limited
Firestore offers only count(), sum(), and average() as server-side aggregations — no GROUP BY, no windowing, no cross-collection queries. Everything else must be done client-side after fetching entire collections. For P&L, Trial Balance, and Balance Sheet generation, that means pulling potentially thousands of journal entries to the browser.
4. Transaction constraints
Firestore transactions must read before write, can retry multiple times on contention, and fail when the client is offline. For an accounting ledger where append-only correctness is non-negotiable, this is a significant risk surface.
5. Unpredictable cost at scale
Pay-per-operation pricing punishes growth. One cited case: 2.8 million reads cost ~$2,400/month. An accounting app doing real-time subscriptions across accounts, journal entries, and reporting will generate high read volumes as data grows.
6. Vendor lock-in
Proprietary APIs with no migration path. Moving off Firestore later requires rewriting the entire data layer.
Cross-Reference With the Acctz Codebase
The original acctz-ai-ui Firebase usage exhibited the exact limitations the research warns about.
Multi-tenancy is not implemented
Account queries are hardcoded to entityId === 'template'. The loadChartOfAccounts(entity?) function accepts an Entity parameter but ignores it in the query. Journal entries have no entity or user scoping — loadAllJournalEntriesFromFirestore fetches the entire journal-entries collection. Any signed-in user can see all journals (modulo security rules maintained separately).
All reporting is client-side
The JournalEntryList class computes running balances, filters by account code, and sorts by date — all in-memory after fetching every journal entry from Firestore. aggregateVendorsFromEntries does the same for vendor summaries. This works for a prototype but won't scale past a few hundred entries per entity.
No double-entry enforcement at the data layer
Firestore has no constraints or triggers. The balanced-entry invariant (debits === credits) can only be enforced in application code, with no database-level safety net if a bug, race condition, or direct Firestore console edit breaks it.
The Acctz Backend Solves These Problems
| Requirement | Firestore (prototype) | PostgreSQL (current) |
|---|---|---|
| Multi-tenancy | Not implemented | RLS with session variables, 16 tenant-scoped tables |
| Double-entry enforcement | App code only | Database constraints + posting trigger |
| Append-only audit trail | No enforcement | Immutable ledger entries by design |
| Period locking | Not implemented | Period close and lock |
| Reporting (P&L, Balance Sheet) | Client-side over full collection | Server-side with SQL aggregation |
| Referential integrity | None | Foreign keys, constraints |
| Cost model | Per-read, unpredictable | Fixed hosting, predictable |
| Complex queries | Barely supported | Full SQL, joins, GROUP BY, window functions |
Further Reading
- MVP Architecture -- how PostgreSQL, Node.js, and Firestore fit together as a system
- Database Design -- full schema, ERD, triggers, and indexes
- Book of Record -- accounting principles and why the ledger must be authoritative