Schema Design

This page documents the PostgreSQL schema powering Acctz Ledger -- its multi-tenant architecture, primary key strategy, Firebase Authentication integration, data integrity guarantees, and how to set it up from scratch.

Architecture Overview

↑ Back to top

The database is organized into four domain schemas inside a single PostgreSQL 17 instance. Each schema owns a bounded context and maps directly to the Architecture Plan domains.

Schema	Purpose	Key Tables
`iam`	Identity and access management	`users`, `organizations`, `roles`, `role_permissions`, `org_memberships`, `api_tokens`
`ledger`	Core double-entry accounting	`entities`, `accounts`, `account_types`, `account_templates`, `journal_entries`, `journal_lines`, `parties`, `fiscal_periods`, `attachments`
`banking`	Bank feeds, imports, reconciliation	`bank_accounts`, `bank_transactions`, `import_jobs`, `reconciliations`, `reconciliation_items`
`audit`	Immutable compliance log	`audit_log`

Multi-Tenant Design

↑ Back to top

Multi-tenancy follows a shared-database, shared-schema model with Row Level Security (RLS) enforcing isolation at the database level.

Tenant Hierarchy

Organization (org)           -- billing unit, "the company"
  └── Entity                 -- "company file" (a single set of books)
       ├── Accounts           -- chart of accounts for this entity
       ├── Journal Entries    -- financial transactions
       ├── Parties            -- vendors, customers, employees
       ├── Fiscal Periods     -- accounting periods
       ├── Bank Accounts      -- linked bank feeds
       └── Bank Transactions  -- imported bank activity

An Organization is the top-level billing unit (equivalent to a QuickBooks "company"). Each Organization can have multiple Entities (sets of books). All tenant-scoped tables carry either org_id or entity_id as the isolation key.

Row Level Security

Every request from Spring Boot sets a session variable before touching the database:

SET LOCAL app.current_user_id = '<uuid>';

RLS policies on every tenant-scoped table use helper functions to restrict visibility:

-- Helper: which entities can the current user see?
CREATE FUNCTION ledger.accessible_entity_ids() RETURNS SETOF UUID AS $$
    SELECT e.id FROM ledger.entities e
    JOIN iam.org_memberships m ON m.org_id = e.org_id
    WHERE m.user_id = current_setting('app.current_user_id', true)::UUID
      AND m.status = 'active';
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;

-- Applied to every entity-scoped table:
ALTER TABLE ledger.accounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON ledger.accounts
    USING (entity_id IN (SELECT ledger.accessible_entity_ids()));

This means tenant isolation is enforced at the database engine level, not just the application layer. Even raw SQL queries through pgAdmin or a JDBC connection will be scoped to the authenticated user's organizations.

Tables with RLS policies: iam.organizations, iam.org_memberships, iam.api_tokens, ledger.entities, ledger.accounts, ledger.fiscal_periods, ledger.parties, ledger.journal_entries, ledger.journal_lines, ledger.attachments, banking.bank_accounts, banking.bank_transactions, banking.import_jobs, banking.reconciliations, banking.reconciliation_items, audit.audit_log.

Firebase Authentication Integration

↑ Back to top

Firebase Auth is the identity provider for the Acctz platform. The frontend (Acctz AI UI) handles sign-in via Firebase (email/password and Google), and sends the resulting JWT to the Spring Boot backend.

Authentication Flow

The `iam.users` Bridge Table

The iam.users table bridges Firebase's identity world with PostgreSQL's relational world:

CREATE TABLE iam.users (
    id              UUID PRIMARY KEY,          -- UUIDv7 (app-generated)
    firebase_uid    VARCHAR(128) UNIQUE,       -- Firebase Auth UID
    email           VARCHAR(255) UNIQUE NOT NULL,
    display_name    VARCHAR(255),
    avatar_url      TEXT,
    status          VARCHAR(20) NOT NULL DEFAULT 'active',
    last_login_at   TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

On first login, the backend creates an iam.users row by extracting the uid and email from the verified Firebase JWT. All subsequent database operations use the PostgreSQL users.id (UUIDv7), not the Firebase UID.

Firebase vs PostgreSQL -- Division of Responsibility

Concern	Firebase	PostgreSQL
Authentication (who are you?)	Firebase Auth	-
Identity storage	Firebase user record	`iam.users` (mirror)
Authorization (what can you do?)	-	`iam.roles`, `iam.role_permissions`, `iam.org_memberships`
Financial data	-	`ledger.`, `banking.`
Offline data cache	Firestore (client-side)	-
Audit trail	-	`audit.audit_log`

The current Acctz AI UI also reads some data from Firestore directly (chart of accounts, user profiles). As the Ledger backend matures, these reads will migrate to the PostgreSQL-backed REST API, with Firestore retained only for real-time UI features where sub-second push updates are valuable (presence, notifications).

UUIDv7 Primary Key Strategy

↑ Back to top

All primary keys (except audit.audit_log) use UUIDv7 (RFC 9562).

Why Not Auto-Increment, UUIDv4, or CUID2?

Strategy	Pros	Cons for This Project
`BIGSERIAL`	Fast writes, small on disk	Exposes record count, fragile across import/export, no offline generation
`UUIDv4`	No coordination needed	Random layout causes B-tree fragmentation and cache misses at scale
`CUID2`	Collision-resistant, sortable	String-typed (36 bytes vs 16 for UUID), no native PostgreSQL type
UUIDv7	Time-ordered (B-tree friendly), 128-bit UUID type, collision-free, offline-safe	Requires app-layer generation on PG 17

UUIDv7 embeds a Unix millisecond timestamp in the high 48 bits, so new IDs sort chronologically. This keeps B-tree index pages sequential and avoids the page-split fragmentation that UUIDv4 causes at high insert rates.

Application-Layer Generation

PostgreSQL 17 does not include a native uuidv7() function (coming in PG 18). IDs are generated in the Spring Boot application layer:

// 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 is provided by UUIDv7Generator:

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

Audit Log Exception

audit.audit_log uses BIGSERIAL instead of UUIDv7. The audit log is write-heavy, append-only, and never referenced as a foreign key. Sequential integers maximize write throughput and minimize storage overhead for this use case.

Data Integrity

↑ Back to top

Double-Entry Enforcement

Two triggers on ledger.journal_entries guarantee accounting correctness:

Balanced entry validation -- when a journal entry transitions from draft to posted, a trigger sums all lines and rejects the entry if SUM(debit) != SUM(credit) or if there are no lines:

IF total_debits != total_credits THEN
    RAISE EXCEPTION 'Journal entry % is unbalanced: debits=%, credits=%',
        NEW.id, total_debits, total_credits;
END IF;

Immutable posted entries -- once an entry reaches posted status, a trigger blocks any further updates. Corrections must be made by voiding the original and creating a new entry:

IF OLD.status = 'posted' AND NEW.status = 'posted' THEN
    RAISE EXCEPTION 'Cannot modify a posted journal entry (id: %)...', OLD.id;
END IF;

Audit Log Immutability

The audit.audit_log table is protected by:

A trigger that raises an exception on UPDATE
A trigger that raises an exception on DELETE
REVOKE UPDATE, DELETE ON audit.audit_log FROM PUBLIC

The postgres superuser can still bypass these protections for disaster recovery, but the application role cannot.

Journal Line Constraints

Each journal line enforces debit XOR credit -- a line must have exactly one non-zero amount:

CONSTRAINT chk_debit_xor_credit CHECK (
    (debit > 0 AND credit = 0) OR (credit > 0 AND debit = 0)
)

Monetary values use NUMERIC(19, 4) for precision without floating-point rounding.

Role-Based Access Control

↑ Back to top

Five system roles ship in V5 seed data. Permissions are stored as (role, resource, action) tuples in iam.role_permissions.

Role	Scope
Owner	Full access including billing and organization deletion
Admin	Full access except billing and organization deletion
Accountant	Full accounting access, period closing, no admin functions
Bookkeeper	Data entry and categorization, no approving or closing
Viewer	Read-only access to all financial data and reports

Actions are: create, read, update, delete, approve, close, export.

Resources are: organization, entity, accounts, journal_entries, fiscal_periods, parties, banking, reports.

Import / Export Compatibility

↑ Back to top

The schema is designed to support standard accounting interchange formats alongside UUIDv7 internal keys.

Format	Standard	Key Fields Used
QBO / QFX	OFX (Open Financial Exchange)	`banking.bank_transactions.fitid`, `transaction_date`, `amount`, `payee_name`
OFX	OFX 2.x	`fitid`, `posted_date`, `check_number`
IIF	Intuit Interchange Format	`ledger.accounts.code`, `ledger.journal_entries.reference_number`, `ledger.parties.name`
CSV	Generic	All human-readable fields
Xero API	Xero	Account codes, reference numbers, party names

The banking.import_jobs table tracks file imports with format-aware validation:

file_format VARCHAR(20) NOT NULL
    CHECK (file_format IN ('ofx', 'qbo', 'qfx', 'csv', 'iif'))

Deduplication of bank transactions uses the (bank_account_id, fitid) unique constraint, matching the Financial Institution Transaction ID standard from OFX.

Flyway Migrations

↑ Back to top

Database evolution is managed by Flyway. Migrations run automatically on Spring Boot startup.

Migration	Description
`V1__create_iam_schema.sql`	IAM tables: users, organizations, roles, permissions, memberships, API tokens
`V2__create_ledger_schema.sql`	Core accounting: entities, accounts, journal entries/lines, parties, fiscal periods, attachments; triggers for balance validation and immutability
`V3__create_banking_schema.sql`	Banking: bank accounts, transactions, import jobs, reconciliations
`V4__create_audit_schema.sql`	Audit log with append-only enforcement (triggers + REVOKE)
`V5__seed_reference_data.sql`	Account types (ASSET through EXPENSE), system roles with permissions, default chart of accounts template (50+ accounts)
`V6__enable_rls_policies.sql`	Row Level Security on all 16 tenant-scoped tables, with helper functions
`V7__create_indexes.sql`	35 performance indexes across all schemas

Migration files live in ledger/src/main/resources/db/migration/.

Spring Boot Configuration

spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    schemas: iam,ledger,banking,audit
    default-schema: ledger
    baseline-on-migrate: true
  jpa:
    hibernate:
      ddl-auto: validate   # Flyway owns DDL; Hibernate only validates
    properties:
      hibernate:
        default_schema: ledger

Performance Indexes

↑ Back to top

V7 creates 35 indexes optimized for the most common query patterns:

Category	Index Pattern	Rationale
User lookup	`users(firebase_uid)`, `users(email)`	JWT verification and login
Membership resolution	`org_memberships(user_id)`, `org_memberships(org_id)`	RLS helper function performance
Account queries	`accounts(entity_id, code)`, `accounts(entity_id, account_type)`	Chart of accounts display and filtering
Journal entry listing	`journal_entries(entity_id, entry_date)`, `journal_entries(entity_id, status)`	Register views, period reports
Journal line drill-down	`journal_lines(journal_entry_id)`, `journal_lines(account_id)`	Account register, entry detail
Bank transaction matching	`bank_transactions(bank_account_id, fitid)`	OFX import deduplication
Audit trail queries	`audit_log(entity_id, created_at)`, `audit_log(user_id, created_at)`	Compliance reporting

Partial indexes (using WHERE ... IS NOT NULL) are used where applicable to reduce index size for sparse columns.

Database Setup Guide

↑ Back to top

Prerequisites

Docker & Docker Compose
Java 17+ (for running the Spring Boot app that executes Flyway)

Step 1: Start PostgreSQL

# From the acctz/ project root
docker compose up -d postgres

This starts PostgreSQL 17.3 (Alpine) with:

Database: acctz
User: postgres / postgres
Port: 5432
Init script: docker/postgres/scripts/init.sql (creates the four schemas and uuid-ossp extension)

Step 2: Run the Application (Flyway Executes Automatically)

# Option A: Gradle (local development)
./gradlew :ledger:bootRun

# Option B: Docker
docker compose --profile app up -d

On startup, Flyway runs V1 through V7 in order, creating all tables, triggers, seed data, RLS policies, and indexes.

Step 3: Verify

# Check Flyway migration history
docker exec -it $(docker ps -q -f name=postgres) \
  psql -U postgres -d acctz -c "SELECT version, description, success FROM ledger.flyway_schema_history ORDER BY installed_rank;"

Expected output:

 version |        description        | success
---------+---------------------------+---------
     | create iam schema         | t
     | create ledger schema      | t
     | create banking schema     | t
     | create audit schema       | t
     | seed reference data       | t
     | enable rls policies       | t
     | create indexes            | t

Step 4: Access pgAdmin (Optional)

docker compose up -d pgadmin

Open http://localhost:5050 and add a server connection:

Host: postgres (Docker network name)
Port: 5432
Username: postgres
Password: postgres

Resetting the Database

If you need a clean slate (e.g., after schema changes during development):

docker compose down -v && docker compose up -d postgres

The -v flag destroys the postgres_data volume, causing Docker to re-run init.sql on next startup. Flyway will then re-apply all migrations when the app starts.

Entity-Relationship Summary

↑ Back to top

Future Considerations

↑ Back to top

PostgreSQL 18 will add native uuidv7(), allowing migration of ID generation from the application layer to database defaults
Firestore sunset path: as the Ledger API covers more read operations, the UI can migrate away from direct Firestore reads for financial data while keeping Firestore for real-time features
Schema splitting: the four-schema design supports future extraction into separate databases or microservices without changing application code
Read replicas: reporting queries can be routed to replicas since the schema is read-heavy for reports and write-heavy only for journal posting

Architecture Overview​

Multi-Tenant Design​

Tenant Hierarchy​

Row Level Security​

Firebase Authentication Integration​

Authentication Flow​

The iam.users Bridge Table​

Firebase vs PostgreSQL -- Division of Responsibility​

UUIDv7 Primary Key Strategy​

Why Not Auto-Increment, UUIDv4, or CUID2?​

Application-Layer Generation​

Audit Log Exception​

Data Integrity​

Double-Entry Enforcement​

Audit Log Immutability​

Journal Line Constraints​

Role-Based Access Control​

Import / Export Compatibility​

Flyway Migrations​

Spring Boot Configuration​

Performance Indexes​

Database Setup Guide​

Prerequisites​

Step 1: Start PostgreSQL​

Step 2: Run the Application (Flyway Executes Automatically)​

Step 3: Verify​

Step 4: Access pgAdmin (Optional)​

Resetting the Database​

Entity-Relationship Summary​

Future Considerations​

Architecture Overview

Multi-Tenant Design

Tenant Hierarchy

Row Level Security

Firebase Authentication Integration

Authentication Flow

The `iam.users` Bridge Table

Firebase vs PostgreSQL -- Division of Responsibility

UUIDv7 Primary Key Strategy

Why Not Auto-Increment, UUIDv4, or CUID2?

Application-Layer Generation

Audit Log Exception

Data Integrity

Double-Entry Enforcement

Audit Log Immutability

Journal Line Constraints

Role-Based Access Control

Import / Export Compatibility

Flyway Migrations

Spring Boot Configuration

Performance Indexes

Database Setup Guide

Prerequisites

Step 1: Start PostgreSQL

Step 2: Run the Application (Flyway Executes Automatically)

Step 3: Verify

Step 4: Access pgAdmin (Optional)

Resetting the Database

Entity-Relationship Summary

Future Considerations