PostgreSQL Strategy & Overview

Archive

This was the original rationale document written during the PostgreSQL migration decision. The architecture diagrams and Technology Versions table reference the Spring Boot implementation. The "Why PostgreSQL" requirements table and the Firebase/PostgreSQL responsibility split are the most durable content here; the rest is superseded by MVP Architecture and Database Design.

This page explains why PostgreSQL was chosen as the system of record for the Acctz platform, how it relates to the existing Firebase layer, and the high-level database architecture that supports a multi-tenant accounting product.

---

Why PostgreSQL

↑ Back to top

The Acctz platform targets the same problem space as QuickBooks, Xero, and Wave -- multi-user, multi-tenant financial data management. That problem has hard requirements that map directly to a relational database:

Requirement	Why It Matters	PostgreSQL Capability
Double-entry enforcement	Every journal entry must balance (debits = credits)	CHECK constraints, triggers, transactions
Referential integrity	Accounts belong to entities, entities belong to orgs	Foreign keys, CASCADE rules
Multi-tenant isolation	Users must never see another organization's data	Row Level Security (RLS), session variables
Complex reporting	P&L, Balance Sheet, Trial Balance, aging reports	SQL joins, GROUP BY, window functions, CTEs
Period locking	Closed periods must be immutable	Status constraints, trigger-enforced immutability
Audit trail	Every change must be traceable for compliance	Append-only tables, REVOKE, trigger guards
Import/export	QBO, OFX, IIF, CSV interchange formats	FITID deduplication, human-readable codes alongside UUIDs
Predictable cost	Hosting cost should not scale with read volume	Fixed infrastructure, no per-operation billing

The Firebase Firestore Verdict documents the specific shortcomings of Firestore for this use case. The short version: Firestore is structurally wrong for relational, multi-tenant, aggregation-heavy accounting data.

---

What Firebase Still Does

↑ Back to top

Firebase is not being abandoned -- its role is being narrowed to what it does well:

Concern	Stays in Firebase	Moves to PostgreSQL
Authentication (who are you?)	Yes	-
Identity tokens (JWT issuance)	Yes	-
User profile data	-	Yes (iam.users)
Authorization (roles, permissions)	-	Yes (iam.roles, iam.role_permissions)
Chart of accounts	-	Yes (ledger.accounts)
Journal entries	-	Yes (ledger.journal_entries)
Bank transactions	-	Yes (banking.bank_transactions)
Audit trail	-	Yes (audit.audit_log)
Real-time UI sync (future)	Possibly (notifications, presence)	-

See the Identity Bridge page for the full authentication flow.

---

Database Architecture at a Glance

↑ Back to top

A single PostgreSQL 18 instance hosts one database (acctz) with four domain schemas:

acctz (database)
├── iam          Identity & access management
├── ledger       Core double-entry accounting
├── banking      Bank feeds, imports, reconciliation
└── audit        Immutable compliance log

This schema-per-domain layout was specified in the Architecture Plan and is now implemented via seven Flyway migrations (V1 through V7). Each schema maps to a bounded context in the backend:

Schema	Domain	Owner Module	Tables
iam	Identity & Access	IAM service layer	6 tables
ledger	Core Accounting	Ledger service layer	9 tables + 2 reference tables
banking	Bank Feeds & Reconciliation	Banking service layer	5 tables
audit	Compliance	Cross-cutting	1 table

See the Database Design page for the full ERD and table catalog.

---

How It Fits Together

↑ Back to top

The overall system flow, from user sign-in through to database query:

Key points:

• Firebase owns authentication -- the client never sends credentials to Spring Boot
• PostgreSQL owns authorization -- roles, permissions, and data visibility are enforced by RLS at the database level
• The bridge is the iam.users table, which maps firebase_uid to a local UUIDv7 id
• Every query is tenant-scoped -- the SET LOCAL app.current_user_id session variable activates RLS policies on all 16 tenant-scoped tables

---

Technology Versions

↑ Back to top

Component	Version	Notes
PostgreSQL	17.3 (Alpine)	Docker image postgres:18.3-alpine
Flyway	Managed by Spring Boot starter	7 migrations (V1-V7)
Spring Boot	3.x	JPA/Hibernate with ddl-auto: validate
Java	17+	UUIDv7 generator requires java.security.SecureRandom
Firebase Auth	Latest	JWT verification via Firebase Admin SDK
Docker Compose	v2	Orchestrates PostgreSQL, pgAdmin, app

---

Further Reading

↑ Back to top

• Database Design -- full ERD, table catalog, triggers, and indexes
• Identity Bridge -- Firebase Auth ↔ PostgreSQL integration in depth
• Design Decisions -- rationale log for UUIDv7, RLS, schema layout, and more
• MVP Architecture -- how Node.js, PostgreSQL, and Firestore fit together
• Architecture Plan -- backend architecture principles and evolution path