Design Decisions

Archive

This page is the decision log for the Acctz PostgreSQL schema. Each section captures what we chose, what we rejected, and why. It was written during the initial design phase and remains a useful reference when onboarding, reviewing, or evolving the schema — however, some implementation details (notably UUIDv7 generation and RLS session variables) reference the original Spring Boot layer and may not reflect the current Node.js implementation.

---

Decision 1: Schema-Per-Domain

↑ Back to top

Chose: Four PostgreSQL schemas (iam, ledger, banking, audit) inside one database.

Rejected:

• Single flat schema (all tables in public)
• Separate databases per domain
• Schema-per-tenant

Rationale:

The Architecture Plan specifies domain-driven modules (IAM, Ledger, Banking, Audit) that own their business rules and write models. Schema-per-domain maps this directly to PostgreSQL:

• Namespace clarity -- ledger.accounts vs banking.bank_accounts makes ownership unambiguous
• Migration independence -- Flyway can target specific schemas, and future teams can own individual migration streams
• Evolution path -- if a domain needs to be extracted into its own service, the schema boundary is already drawn. Split the schemas into separate databases and update connection strings; no table renames required
• Single transaction -- unlike separate databases, cross-schema queries and transactions work natively within one database instance
• Schema-per-tenant was rejected because it doesn't scale with tenant count. At 1,000+ organizations, managing 1,000 schemas with 20+ tables each creates untenable migration complexity. Row Level Security provides equivalent isolation without schema proliferation

---

Decision 2: UUIDv7 Primary Keys

↑ Back to top

Chose: UUIDv7 (RFC 9562) for all primary keys, generated at the application layer.

Rejected:

• BIGSERIAL / auto-increment
• UUIDv4 (random)
• CUID2 / ULID
• Database-generated UUIDs

Rationale:

Factor	BIGSERIAL	UUIDv4	CUID2	UUIDv7
B-tree friendly	Yes (sequential)	No (random scatter)	Yes (time-prefix)	Yes (time-prefix)
Collision-free	Per-table only	Effectively yes	Yes	Yes
Offline generation	No (needs DB)	Yes	Yes	Yes
Native PG type	BIGINT (8 bytes)	UUID (16 bytes)	VARCHAR (36 bytes)	UUID (16 bytes)
Exposes record count	Yes	No	No	No
Import/export safe	Fragile across systems	Yes	Yes	Yes

Why not BIGSERIAL? Sequential integers expose record counts to clients, break when data is moved between environments (staging → production), and require the database to be online for ID generation. For a system that imports data from QBO/OFX/IIF files and may operate offline-first on mobile, this is a dealbreaker.

Why not UUIDv4? Random UUIDs cause B-tree index page splits. As tables grow past millions of rows, insert performance degrades and index bloat increases. PostgreSQL's uuid type stores UUIDv4 and UUIDv7 identically (16 bytes), but UUIDv7's time-ordering keeps inserts appending to the end of the B-tree.

Why not CUID2? CUID2 is a string (22-26 characters). PostgreSQL stores it as VARCHAR, which takes ~36 bytes plus overhead. UUIDv7 is stored as a native 16-byte UUID type with dedicated comparison operators. The storage and index performance difference compounds over millions of journal lines.

Why application-layer generation? At the time this decision was made, PostgreSQL 17 did not include a native uuidv7() function. Rather than installing a third-party extension (pg_uuidv7), IDs were generated in the application layer. PostgreSQL 18 now ships uuidv7() natively — the app-layer generator remains valid alongside it, so migration to DEFAULT uuidv7() on column definitions is non-breaking whenever convenient.

// com.acctz.ledger.common.UUIDv7
public static UUID generate() {
    long timestamp = System.currentTimeMillis();
    long msb = (timestamp << 16) & 0xFFFFFFFFFFFF0000L;
    msb |= 0x0000000000007000L; // version 7
    msb |= (long) (RANDOM.nextInt() & 0x0FFF);
    long lsb = RANDOM.nextLong();
    lsb = (lsb & 0x3FFFFFFFFFFFFFFFL) | 0x8000000000000000L; // variant 10
    return new UUID(msb, lsb);
}

Hibernate integration via UUIDv7Generator makes this transparent to JPA entities:

@Id
@GeneratedValue(generator = "uuidv7")
@GenericGenerator(name = "uuidv7", type = UUIDv7Generator.class)
private UUID id;

Now on PG 18: DEFAULT uuidv7() can be added to column definitions at any time. The application-layer generator remains valid alongside native generation, so migration is non-breaking.

---

Decision 3: BIGSERIAL for Audit Log

↑ Back to top

Chose: BIGSERIAL primary key for audit.audit_log.

Rejected: UUIDv7 (used everywhere else).

Rationale:

The audit log is different from every other table:

• Write-only -- records are never updated or deleted (enforced by triggers and REVOKE)
• Never a foreign key target -- no other table references audit_log.id
• Extremely high volume -- every create, update, delete, login, and export generates a row
• Never generated offline -- audit events originate from the backend, which always has a database connection

BIGSERIAL gives maximum write throughput with minimum storage (8 bytes vs 16 for UUID). Since the audit log is append-only and never joined to, the benefits of UUIDv7 (offline generation, cross-system identity) don't apply.

---

Decision 4: Row Level Security for Tenant Isolation

↑ Back to top

Chose: PostgreSQL Row Level Security (RLS) with session variables.

Rejected:

• Application-layer WHERE clause filtering
• Separate databases per tenant
• Schema-per-tenant
• View-based isolation

Rationale:

Application-layer filtering (adding WHERE entity_id IN (...) to every query) is fragile. One missed filter in one query exposes tenant data. RLS moves the boundary to the database engine, where it cannot be bypassed by application bugs:

ALTER TABLE ledger.accounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON ledger.accounts
    USING (entity_id IN (SELECT ledger.accessible_entity_ids()));

Session variable pattern: The Spring Boot security filter sets app.current_user_id via SET LOCAL before any business query executes. SET LOCAL scopes the variable to the current transaction, so there is no risk of leaking between requests in a connection pool.

Performance: The accessible_entity_ids() function is marked STABLE SECURITY DEFINER, which allows PostgreSQL to cache the result within a transaction. Combined with indexes on org_memberships(user_id) and entities(org_id), the RLS overhead is minimal.

Tradeoff acknowledged: RLS adds complexity to debugging. Direct psql sessions without setting app.current_user_id will see zero rows on protected tables. This is a feature (defense in depth), but developers need to know to run SET app.current_user_id = '<uuid>' when using pgAdmin.

---

Decision 5: Firebase Auth as Identity Provider

↑ Back to top

Chose: Keep Firebase Auth; bridge to PostgreSQL via iam.users.

Rejected:

• Self-hosted auth (Keycloak, Ory Hydra)
• Auth0 / Okta
• Build custom JWT issuance

Rationale:

Firebase Auth is already deployed in the Acctz UI (React PWA) and the Shadow2 UI (React Native) mobile app. It supports:

• Email/password, Google, Apple sign-in
• JWT issuance with RSA signatures
• Multi-platform SDKs (web, iOS, Android)
• Free tier that covers startup-scale usage

Self-hosted auth introduces operational burden (key rotation, token storage, password hashing) that Firebase already handles. Auth0/Okta would work but add per-user cost that Firebase doesn't have at this scale.

The bridge table (iam.users.firebase_uid) creates provider independence. If Firebase is replaced later, only the JWT verification filter and the firebase_uid column need to change. All other tables reference users.id (UUIDv7), which is provider-agnostic.

See the Identity Bridge page for the full request lifecycle.

---

Decision 6: No Cross-Schema Foreign Keys

↑ Back to top

Chose: ledger.entities.org_id references iam.organizations.id conceptually, but no FOREIGN KEY constraint exists across schemas.

Rejected: Cross-schema foreign keys.

Rationale:

The Architecture Plan specifies "no cross-domain foreign keys" to keep domains independently deployable. If the iam schema is ever extracted into a separate service with its own database, cross-schema FKs would break.

Integrity between schemas is enforced at the application layer:

• The Spring Boot service validates that org_id exists before creating an entity
• RLS policies join across schemas (which is safe for reads) to resolve access

Within a schema, foreign keys are used aggressively (e.g., journal_lines.journal_entry_id → journal_entries.id with ON DELETE CASCADE).

---

Decision 7: Monetary Precision

↑ Back to top

Chose: NUMERIC(19, 4) for all monetary values.

Rejected:

• FLOAT / DOUBLE PRECISION
• NUMERIC(15, 2)
• Integer cents (BIGINT storing pennies)

Rationale:

• FLOAT is disqualified for financial data. IEEE 754 floating-point arithmetic produces rounding errors (0.1 + 0.2 != 0.3). This is non-negotiable for an accounting system.
• NUMERIC(15, 2) would limit precision to cents. Multi-currency exchange rates and tax calculations regularly need 4+ decimal places (e.g., 1 USD = 0.8573 GBP). The exchange_rate column uses NUMERIC(15, 8) for even higher precision.
• Integer cents are space-efficient but require constant division/multiplication at the application layer, creating bug surface.
• NUMERIC(19, 4) is the standard used by QuickBooks, Xero, and SAP for general ledger amounts. 19 digits covers values up to 999,999,999,999,999.9999 -- more than sufficient for any single-entity accounting.

---

Decision 8: Append-Only Journal Entries

↑ Back to top

Chose: Posted journal entries are immutable. Corrections are new entries (voids + re-entries).

Rejected: Allowing edits to posted entries.

Rationale:

This is not a database design choice -- it is an accounting principle. The Book of Record documentation explains why:

• Posted entries are legal records. Modifying them after the fact violates GAAP, IFRS, and SOX compliance requirements.
• Auditors must see the complete transaction history, including errors and their corrections.
• The void-and-reenter pattern creates an explicit paper trail.

The database enforces this via trigger:

CREATE TRIGGER trg_prevent_posted_entry_update
    BEFORE UPDATE ON ledger.journal_entries
    FOR EACH ROW
    WHEN (OLD.status = 'posted')
    EXECUTE FUNCTION ledger.prevent_posted_entry_update();

Draft entries remain editable until posted. This matches the workflow in QuickBooks and Xero where transactions can be modified before they're "recorded."

---

Decision 9: Human-Readable Codes Alongside UUIDs

↑ Back to top

Chose: Business-meaningful identifiers (account codes, reference numbers, FITIDs) alongside UUIDv7 primary keys.

Rejected: UUID-only identification.

Rationale:

Accounting interchange formats (IIF, QBO, OFX, Xero API) use human-readable identifiers:

Format	Identifier	Acctz Column
IIF (QuickBooks Desktop)	Account name + number	ledger.accounts.code + name
QBO (QuickBooks Web Connect)	FITID	banking.bank_transactions.fitid
OFX	FITID, check number	fitid, check_number
Xero API	Account code	ledger.accounts.code
CSV export	Reference number	ledger.journal_entries.reference_number

UUIDv7s are opaque to users and external systems. The dual-identifier approach means:

• Internal operations (joins, RLS, audit trail) use UUIDv7 for performance and safety
• External operations (import, export, display) use human-readable codes for compatibility
• The UNIQUE(entity_id, code) constraint on accounts ensures codes are unique within an entity

---

Decision 10: Seed Data in Flyway

↑ Back to top

Chose: Reference data (account types, roles, permissions, chart of accounts template) seeded via V5__seed_reference_data.sql.

Rejected:

• Application-layer seeding (Spring CommandLineRunner)
• Liquibase changesets
• Manual insertion

Rationale:

Seed data is part of the schema contract. Without the five account types, the accounts.account_type foreign key has nothing to reference. Without roles, memberships cannot be created. Seeding in Flyway guarantees:

• Data is present before the application starts
• Data is versioned alongside the schema
• Data is identical in every environment (local, staging, production)
• ON CONFLICT DO NOTHING makes migrations re-runnable without duplicating data

The default chart of accounts template (50+ accounts for a general industry) lets new entities start with a sensible COA rather than an empty one.

---

Decision Summary

↑ Back to top

#	Decision	Choice	Key Reason
1	Schema layout	Schema-per-domain (4 schemas)	Maps to architecture domains; supports future extraction
2	Primary keys	UUIDv7 (app-generated)	Time-ordered, B-tree friendly, offline-safe, native UUID type
3	Audit log PK	BIGSERIAL	Write-heavy, never a FK target, no offline generation
4	Tenant isolation	Row Level Security	Database-enforced, cannot be bypassed by app bugs
5	Identity provider	Firebase Auth + bridge table	Already deployed, provider-agnostic via iam.users
6	Cross-schema FKs	None (app-layer validation)	Keeps domains independently deployable
7	Monetary type	NUMERIC(19,4)	Exact arithmetic, multi-currency precision, industry standard
8	Journal immutability	Append-only (trigger-enforced)	GAAP/IFRS compliance, audit trail integrity
9	Identifiers	UUIDv7 + human-readable codes	Internal performance + external interchange compatibility
10	Seed data	Flyway migration	Versioned, environment-consistent, required by FK constraints

---

Further Reading

↑ Back to top

• Identity Bridge -- Firebase Auth ↔ PostgreSQL integration flow
• Database Design -- full table catalog and ERD
• Architecture Plan -- backend architecture principles
• Book of Record -- accounting principles behind immutability