Database Design

The Acctz database uses four domain schemas inside a single PostgreSQL 18 database. This page provides the complete table catalog, entity-relationship diagram, trigger logic, index strategy, and Flyway migration map.

---

Entity-Relationship Diagram

↑ Back to top

---

Schema: iam -- Identity & Access Management

↑ Back to top

Created by V1__create_iam_schema.sql.

iam.users

The bridge between Firebase Auth and the relational world. Every Firebase user who signs in gets a corresponding row here.

Column	Type	Notes
id	UUID (PK)	UUIDv7, app-generated
firebase_uid	VARCHAR(128) UNIQUE	Maps to Firebase Auth UID
email	VARCHAR(255) UNIQUE NOT NULL	From Firebase JWT
display_name	VARCHAR(255)	From Firebase profile
avatar_url	TEXT
status	VARCHAR(20)	active, suspended, deleted
last_login_at	TIMESTAMPTZ	Updated on each authentication
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ

iam.organizations

The top-level tenant unit -- equivalent to a "company" in QuickBooks or a Xero "organisation."

Column	Type	Notes
id	UUID (PK)	UUIDv7
name	VARCHAR(255) NOT NULL
slug	VARCHAR(100) UNIQUE NOT NULL	URL-safe identifier
plan	VARCHAR(50)	free, starter, pro, enterprise
status	VARCHAR(20)	active, suspended, cancelled
billing_email	VARCHAR(255)
max_entities	INT	Default 1
max_users	INT	Default 5
created_by	UUID (FK → users)
created_at, updated_at	TIMESTAMPTZ

iam.roles

System-defined and custom roles. Five system roles ship in seed data (V5): Owner, Admin, Accountant, Bookkeeper, Viewer.

Column	Type	Notes
id	UUID (PK)
code	VARCHAR(50) UNIQUE NOT NULL	owner, admin, accountant, bookkeeper, viewer
name	VARCHAR(100) NOT NULL
description	TEXT
is_system	BOOLEAN	System roles cannot be deleted

iam.role_permissions

Permission matrix stored as (role, resource, action) tuples.

Column	Type	Notes
id	UUID (PK)
role_id	UUID (FK → roles)	CASCADE on delete
resource	VARCHAR(50)	organization, entity, accounts, journal_entries, fiscal_periods, parties, banking, reports
action	VARCHAR(20)	create, read, update, delete, approve, close, export
		UNIQUE on (role_id, resource, action)

iam.org_memberships

Links users to organizations with a specific role.

Column	Type	Notes
id	UUID (PK)
org_id	UUID (FK → organizations)
user_id	UUID (FK → users)
role_id	UUID (FK → roles)
status	VARCHAR(20)	active, invited, suspended
invited_email	VARCHAR(255)	For pending invitations
invited_at, joined_at	TIMESTAMPTZ
created_at	TIMESTAMPTZ
		UNIQUE on (org_id, user_id)

iam.api_tokens

Hashed API tokens for machine-to-machine integrations (import tools, partner apps).

Column	Type	Notes
id	UUID (PK)
org_id	UUID (FK → organizations)
user_id	UUID (FK → users)	Token creator
name	VARCHAR(255)	Human label
token_hash	VARCHAR(255)	bcrypt/argon2 hash, never plaintext
scopes	TEXT[]	PostgreSQL array of allowed scopes
expires_at	TIMESTAMPTZ	Optional expiration
last_used_at	TIMESTAMPTZ
is_active	BOOLEAN
created_at	TIMESTAMPTZ

---

Schema: ledger -- Core Accounting

↑ Back to top

Created by V2__create_ledger_schema.sql. This is the heart of the system.

ledger.account_types

Reference table for the five fundamental account classifications.

Code	Name	Normal Balance
ASSET	Asset	debit
LIABILITY	Liability	credit
EQUITY	Equity	credit
REVENUE	Revenue	credit
EXPENSE	Expense	debit

ledger.account_templates

Industry-specific chart of accounts templates used to seed new entities. The default general template ships with 50+ accounts (1000-series Assets through 7000-series Other Expenses).

ledger.entities

A "set of books" -- the equivalent of a QuickBooks company file. Each entity belongs to one organization and contains its own chart of accounts, journal entries, parties, and fiscal periods.

Column	Type	Notes
id	UUID (PK)	UUIDv7
org_id	UUID	Tenant isolation key (FK to iam.organizations conceptually, no cross-schema FK)
name	VARCHAR(255) NOT NULL
legal_name	VARCHAR(255)
ein	VARCHAR(20)	Tax ID
address_*	VARCHAR	Line1, Line2, City, State, Postal, Country
base_currency	VARCHAR(3)	Default USD
fiscal_year_start_month	INT	1-12, default January
industry	VARCHAR(100)	Links to account_templates for initial seeding
status	VARCHAR(20)	active, inactive, archived
created_by	UUID
created_at, updated_at	TIMESTAMPTZ

ledger.accounts

The chart of accounts for each entity. Supports hierarchical parent/child relationships via parent_code.

Column	Type	Notes
id	UUID (PK)	UUIDv7
entity_id	UUID (FK → entities)
code	VARCHAR(20)	Human-readable (e.g. 1110, 4100)
name	VARCHAR(255)
account_type	VARCHAR(20) (FK → account_types)
parent_code	VARCHAR(20)	Hierarchical structure
normal_balance	VARCHAR(6)	debit or credit
is_active, is_system, is_bank_account	BOOLEAN
tax_code	VARCHAR(20)
is_1099	BOOLEAN	1099 reporting flag
currency_code	VARCHAR(3)	Default USD
sort_order	INT	Display ordering
		UNIQUE on (entity_id, code)

ledger.journal_entries

Journal entry headers. Draft entries can be edited; posted entries are immutable (enforced by trigger).

Column	Type	Notes
id	UUID (PK)	UUIDv7
entity_id	UUID (FK → entities)
entry_date	DATE	Transaction date
effective_date	DATE	Optional, for accruals
reference_type	VARCHAR(50)	invoice, payment, adjustment, etc.
reference_number	VARCHAR(100)	Human-readable (for IIF/QBO export)
memo	TEXT
status	VARCHAR(20)	draft → posted → voided
source	VARCHAR(50)	manual, import, recurring, system
source_ref	VARCHAR(255)	External reference ID
fiscal_period_id	UUID (FK → fiscal_periods)
party_id	UUID (FK → parties)
posted_at, posted_by		Posting metadata
voided_at, voided_by, void_reason		Void metadata
created_by, updated_by	UUID
created_at, updated_at	TIMESTAMPTZ

ledger.journal_lines

Individual debit/credit lines within a journal entry. Each line touches exactly one account.

Column	Type	Notes
id	UUID (PK)	UUIDv7
journal_entry_id	UUID (FK → journal_entries)	CASCADE on delete
account_id	UUID (FK → accounts)
description	TEXT	Line-level memo
debit	NUMERIC(19,4)	CHECK >= 0
credit	NUMERIC(19,4)	CHECK >= 0
party_type, party_id		Optional line-level party
department, location, project	VARCHAR	Dimensional tagging
foreign_amount, foreign_currency, exchange_rate		Multi-currency support
line_order	INT	Display ordering
		chk_debit_xor_credit: exactly one of debit/credit must be non-zero

ledger.fiscal_periods

Accounting periods with open/closed/locked lifecycle.

Column	Type	Notes
id	UUID (PK)
entity_id	UUID (FK → entities)
name	VARCHAR(50)	e.g. "Jan 2026"
period_type	VARCHAR(20)	month, quarter, year
start_date, end_date	DATE	CHECK end_date > start_date
status	VARCHAR(20)	open → closed → locked
closed_at, closed_by		Closing metadata
		UNIQUE on (entity_id, start_date, end_date)

ledger.parties

Vendors, customers, and employees.

Column	Type	Notes
id	UUID (PK)
entity_id	UUID (FK → entities)
type	VARCHAR(20)	vendor, customer, employee
name	VARCHAR(255) NOT NULL
tax_id	VARCHAR(50)	For 1099 reporting
is_1099	BOOLEAN
default_account_id	UUID (FK → accounts)	Auto-categorization
payment_terms	VARCHAR(50)	net_30, etc.
Address, contact fields		Standard address block

ledger.attachments

Documents (receipts, invoices) linked to journal entries.

Column	Type	Notes
id	UUID (PK)
entity_id	UUID (FK → entities)
journal_entry_id	UUID (FK → journal_entries)	SET NULL on delete
file_name, file_type, file_size		File metadata
storage_path	TEXT NOT NULL	S3 / local path
uploaded_at, uploaded_by

---

Schema: banking -- Bank Feeds & Reconciliation

↑ Back to top

Created by V3__create_banking_schema.sql.

banking.bank_accounts

Linked bank accounts. Stores masked account numbers only.

Column	Type	Notes
id	UUID (PK)
entity_id	UUID	Tenant isolation key
account_id	UUID	Links to ledger.accounts (the GL account)
institution_name	VARCHAR(255)
account_number_masked	VARCHAR(20)	e.g. ****1234
account_type	VARCHAR(50)	checking, savings, credit_card, loan, money_market
provider, provider_account_id		Bank feed provider fields (OFX/API integration)
status	VARCHAR(20)	active, inactive, error
last_synced_at	TIMESTAMPTZ

banking.bank_transactions

Imported bank activity. Deduplication uses the OFX fitid field.

Column	Type	Notes
id	UUID (PK)
bank_account_id	UUID (FK → bank_accounts)
entity_id	UUID
transaction_date, posted_date	DATE
amount	NUMERIC(19,4)	Positive for deposits, negative for withdrawals
description, payee_name, category
check_number	VARCHAR(50)	For check matching
fitid	VARCHAR(255)	Financial Institution Transaction ID (OFX standard)
status	VARCHAR(20)	pending → categorized → matched → excluded
matched_journal_id	UUID	Links to the journal entry this was matched to
		UNIQUE on (bank_account_id, fitid)

banking.import_jobs

Tracks file import operations with format-aware validation.

Column	Type	Notes
id	UUID (PK)
entity_id	UUID
bank_account_id	UUID (FK → bank_accounts)
file_name	VARCHAR(255)
file_format	VARCHAR(20)	ofx, qbo, qfx, csv, iif
status	VARCHAR(20)	pending → processing → completed / failed
total_records, imported_records, skipped_records	INT	Progress tracking
error_message	TEXT
created_by	UUID

banking.reconciliations and banking.reconciliation_items

Period-based reconciliation workflow. Each reconciliation captures a statement balance and tracks which journal entries have been cleared against it.

---

Schema: audit -- Compliance Log

↑ Back to top

Created by V4__create_audit_schema.sql.

audit.audit_log

Append-only. The only table in the system that uses BIGSERIAL instead of UUIDv7 (for maximum write throughput; see Design Decisions).

Column	Type	Notes
id	BIGSERIAL (PK)	Sequential, not UUIDv7
org_id	UUID
entity_id	UUID
user_id	UUID NOT NULL	Who performed the action
action	VARCHAR(50)	create, update, delete, login, export, etc.
resource_type	VARCHAR(50)	journal_entry, account, party, etc.
resource_id	UUID	Which record was affected
changes	JSONB	Before/after snapshot
ip_address	INET
user_agent	TEXT
created_at	TIMESTAMPTZ

Immutability enforcement:

• Trigger on UPDATE → raises exception
• Trigger on DELETE → raises exception
• REVOKE UPDATE, DELETE ON audit.audit_log FROM PUBLIC

Only the postgres superuser can bypass these protections for disaster recovery.

---

Triggers

↑ Back to top

Trigger	Table	When	Purpose
trg_prevent_posted_entry_update	ledger.journal_entries	BEFORE UPDATE (when OLD.status = 'posted')	Blocks modification of posted entries. Corrections require voiding and re-entry.
trg_validate_balanced_entry	ledger.journal_entries	BEFORE UPDATE (draft → posted)	Verifies SUM(debit) = SUM(credit) across all lines. Rejects empty or unbalanced entries.
trg_prevent_audit_update	audit.audit_log	BEFORE UPDATE	Raises exception -- audit records are immutable
trg_prevent_audit_delete	audit.audit_log	BEFORE DELETE	Raises exception -- audit records are immutable

---

Indexes

↑ Back to top

V7__create_indexes.sql creates 35 indexes across all schemas. Key categories:

Category	Indexes	Rationale
User lookup	users(firebase_uid), users(email)	JWT verification on every request
RLS performance	org_memberships(user_id), org_memberships(org_id)	The accessible_entity_ids() helper runs on every RLS-guarded query
Account queries	accounts(entity_id, code), accounts(entity_id, account_type), accounts(entity_id, is_active)	Chart of accounts views with filtering
Journal listing	journal_entries(entity_id, entry_date), journal_entries(entity_id, status)	Register views sorted by date, filtered by status
Line drill-down	journal_lines(journal_entry_id), journal_lines(account_id)	Account register (all entries for an account)
Bank deduplication	bank_transactions(bank_account_id, fitid)	OFX FITID uniqueness check on import
Audit queries	audit_log(entity_id, created_at), audit_log(user_id, created_at)	"What happened to this entity?" and "What did this user do?"

Partial indexes (with WHERE ... IS NOT NULL) are used for sparse columns like source_ref, matched_journal_id, and journal_entry_id on attachments.

---

Flyway Migration Map

↑ Back to top

Migrations live in db/migrations/ and run via acctz create-db (or cd db && ./gradlew flywayMigrate directly).

File	Schema(s)	What It Creates
V1__create_iam_schema.sql	iam	6 tables: users, organizations, roles, role_permissions, org_memberships, api_tokens
V2__create_ledger_schema.sql	ledger	9 tables + 2 triggers: account_types, account_templates, entities, accounts, fiscal_periods, parties, journal_entries, journal_lines, attachments
V3__create_banking_schema.sql	banking	5 tables: bank_accounts, bank_transactions, import_jobs, reconciliations, reconciliation_items
V4__create_audit_schema.sql	audit	1 table + 2 triggers + REVOKE: audit_log
V5__seed_reference_data.sql	iam, ledger	Account types, 5 system roles with permissions, default chart of accounts template (50+ accounts)
V6__enable_rls_policies.sql	all	RLS on 16 tables, 2 helper functions (accessible_entity_ids, accessible_org_ids)
V7__create_indexes.sql	all	35 performance indexes

Bootstrap Sequence

Before Flyway runs, scripts/bootstrap.sql is mounted to Docker's docker-entrypoint-initdb.d/ and creates the four schemas, the uuid-ossp extension, and grants:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE SCHEMA IF NOT EXISTS iam AUTHORIZATION postgres;
CREATE SCHEMA IF NOT EXISTS ledger AUTHORIZATION postgres;
CREATE SCHEMA IF NOT EXISTS banking AUTHORIZATION postgres;
CREATE SCHEMA IF NOT EXISTS audit AUTHORIZATION postgres;

GRANT ALL ON SCHEMA iam TO postgres;
GRANT ALL ON SCHEMA ledger TO postgres;
GRANT ALL ON SCHEMA banking TO postgres;
GRANT ALL ON SCHEMA audit TO postgres;

This separation exists because Flyway needs the schemas to already exist before it can create tables within them.

---

Further Reading

↑ Back to top

• Design Decisions -- rationale for UUIDv7, RLS, schema layout, and more
• Architecture Plan -- backend architecture principles
• Book of Record -- accounting principles behind immutability