CFS-DB-001 Database Architecture Overview

INTERNAL CONFIDENTIAL — AZ1.AI Inc. — Authorized Personnel Only This document contains proprietary and confidential information about the CODITECT Financial Suite database architecture. Unauthorized distribution, reproduction, or disclosure is strictly prohibited.

---

CFS-DB-001: Database Architecture Overview

Classification: INTERNAL CONFIDENTIAL Owner: Hal Casteel, CEO/CTO, AZ1.AI Inc. Document Code: CFS-DB-001 Last Updated: 2026-02-20 Component: CODITECT Financial Suite — General Ledger Engine Database: PostgreSQL 16+

---

Table of Contents

1. Executive Summary
2. Design Principles
3. Entity-Relationship Diagram
4. Table Summary
5. Enum Types Reference
6. Index Architecture
7. Trigger & Business Logic Inventory
8. Migration History
9. Multi-Currency Architecture
10. Audit & Compliance Architecture
11. Naming Conventions
12. Technology Stack
13. Capacity & Performance Characteristics
14. Related Documents

---

1. Executive Summary

The CODITECT Financial Suite General Ledger Engine is built on PostgreSQL 16+ with a shared-schema, shared-database multi-tenancy architecture enforced entirely through Row-Level Security (RLS). The database layer is designed to be the authoritative source of accounting truth: every business rule — double-entry balance, period state transitions, audit immutability, and tenant isolation — is enforced at the database level and cannot be bypassed by any application-layer bug, API misconfiguration, or network anomaly.

Current Implementation Snapshot

Dimension	Current State
Core GL tables	8
PostgreSQL enum types	10
Applied migrations	3 (001–003)
Database extensions	uuid-ossp, pgcrypto
Triggers	12 (across all tables)
Stored functions	4 (GL verification and balance management)
Audit log partitioning	Range-partitioned by half-year
Multi-tenancy model	Shared-schema + RLS
Currency slots	2 (original + functional); target is 3 per IAS 21/ASC 830
Compliance targets	SPED ECD, SOX, IAS 21, ASC 830, GoBD

Architecture Decisions

PostgreSQL 16+ with RLS. The shared-schema, shared-database model was selected over database-per-tenant and schema-per-tenant alternatives after evaluating operational complexity, migration cost, and scaling characteristics at the anticipated tenant volume of hundreds to thousands of accounting firm clients. RLS enforcement occurs inside the PostgreSQL query executor — it cannot be bypassed by application code.

Database-level business rule enforcement. Double-entry balance verification, period state machine enforcement, audit immutability, and inactive account protections are implemented as PostgreSQL triggers. This means the database is self-defending: a direct SQL connection bypassing the application layer still cannot produce an invalid accounting state.

Partitioned audit log. The gl_audit_log table is range-partitioned by created_at in half-year intervals. This provides sub-second audit query performance over multi-year datasets while enabling data lifecycle management (archiving or dropping old partitions) without full table locks or data movement.

Materialized balance cache. Account balances are maintained in gl_account_balances via incremental upsert triggered on every journal line INSERT. A full-recompute repair function (gl_recompute_balances) allows correction of any balance discrepancy without data loss.

Multi-currency two-slot model (current). Journal lines carry original_amount/original_currency (transaction currency) and debit_amount/credit_amount (functional currency). The three-slot model required by IAS 21 and ASC 830 — adding a reporting currency — is planned for Phase 1 of the migration roadmap (CFS-DB-005).

---

2. Design Principles

These six principles govern every schema design decision in the GL Engine database layer. They are non-negotiable and must be preserved through all future migrations.

2.1 Tenant Isolation

Every table carries tenant_id UUID NOT NULL as the first non-primary-key column. This column is not defaulted — the application must supply it explicitly for every INSERT. PostgreSQL RLS policies enforce that every SELECT, INSERT, UPDATE, and DELETE operates only on rows matching the current session's tenant context, set via SET LOCAL app.current_tenant = '{uuid}'.

Tenant isolation is hardware-enforced inside the PostgreSQL query executor. It cannot be disabled by application code, bypassed by an ORM, or omitted by a developer who forgot to add a WHERE clause.

2.2 Double-Entry Enforcement

The trg_enforce_double_entry trigger fires on AFTER INSERT OR UPDATE on gl_journal_entries. It verifies that the sum of debit amounts equals the sum of credit amounts across all lines for a given journal entry at the moment of status change to POSTED. Entries that fail the balance check are rejected with error code GL001.

This is enforced at the statement level (not row level), ensuring that partial inserts during a multi-line journal entry do not trigger false positives.

2.3 Audit Immutability

The gl_audit_log table is append-only. The trg_prevent_audit_mutation trigger fires on BEFORE UPDATE OR DELETE on gl_audit_log and raises an exception unconditionally. No application role has UPDATE or DELETE privileges on this table. The audit record of every financial event is permanent and unalterable from within the system.

2.4 Period State Machine

Fiscal periods follow a strict four-state progression: FUTURE → OPEN → CLOSED → LOCKED. State transitions are enforced by the trg_period_state_machine trigger, which rejects any attempt to move a period to a non-adjacent state (e.g., FUTURE directly to LOCKED) or to reverse a terminal state (e.g., LOCKED back to CLOSED). Journal entries cannot be posted into a CLOSED or LOCKED period.

2.5 Metadata Extensibility

Every GL table carries a metadata JSONB column. This column provides a structured escape hatch for jurisdiction-specific, customer-specific, or future-state data requirements that do not yet warrant a dedicated column. Using JSONB for extension data prevents schema proliferation while preserving queryability via PostgreSQL's native JSONB operators and GIN indexing.

2.6 Balance Materialization

Account balances are not computed on the fly. The trg_update_balances trigger fires on every journal line INSERT and performs an incremental upsert into gl_account_balances. This means trial balance and balance sheet queries execute in milliseconds regardless of journal line volume, because they read from the pre-aggregated balance table rather than summing raw journal lines.

The gl_recompute_balances stored function provides a full recompute capability for recovery scenarios, rebuilding the balance cache from the canonical journal line source of truth.

---

3. Entity-Relationship Diagram

The following diagram represents the eight core GL tables and their primary relationships.

gl_accounts
┌────────────────────────────────────────────────────────────────────┐
│ id (PK)                                                            │
│ tenant_id (NOT NULL) ◄─────────────────────────────────────────┐  │
│ parent_id (FK → gl_accounts.id, self-referential hierarchy)     │  │
│ account_code, account_type (enum), normal_balance (enum)        │  │
│ account_status (enum), sped_account_code, currency              │  │
│ name_pt, name_en, name_es, metadata JSONB                       │  │
└────────────────────────────────────────────────────────────────────┘
         │ 1                                    │ 1
         │ has many                             │ has many
         ▼ N                                    ▼ N
gl_journal_lines                     gl_account_balances
┌────────────────────┐               ┌────────────────────────────────┐
│ id (PK)            │               │ id (PK)                        │
│ tenant_id          │               │ tenant_id                      │
│ journal_entry_id ──┼─┐             │ account_id (FK → gl_accounts)  │
│ account_id ────────┼─┘             │ period_id (FK → gl_fiscal_...) │
│ line_number        │               │ debit_total, credit_total      │
│ debit_amount       │               │ net_balance                    │
│ credit_amount      │               │ last_updated_at                │
│ original_amount    │               └────────────────────────────────┘
│ original_currency  │
│ description        │                         ▲ N
│ dimension_1..5     │                         │ period reference
│ metadata JSONB     │                         │
└────────────────────┘               gl_fiscal_periods
         ▲ N                         ┌────────────────────────────────┐
         │ contains many             │ id (PK)                        │
         │                           │ tenant_id                      │
gl_journal_entries                   │ fiscal_year, period_number     │
┌────────────────────────────────┐   │ start_date, end_date           │
│ id (PK)                        │   │ period_state (enum)            │
│ tenant_id                      │   │ closed_at, locked_at           │
│ period_id (FK → gl_fiscal_...) │   │ metadata JSONB                 │
│ entry_date, entry_number       │   └────────────────────────────────┘
│ reference, description         │
│ entry_status (enum)            │
│ module_source (enum)           │
│ reversal_of_id (FK → self)     │   gl_exchange_rates
│ reversed_by_id (FK → self)     │   ┌────────────────────────────────┐
│ posted_at, posted_by           │   │ id (PK)                        │
│ metadata JSONB                 │   │ tenant_id                      │
└────────────────────────────────┘   │ from_currency, to_currency     │
                                     │ rate_type (enum)               │
                                     │ rate_source (enum)             │
                                     │ rate, effective_date           │
                                     │ metadata JSONB                 │
                                     └────────────────────────────────┘

gl_audit_log (PARTITIONED BY RANGE on created_at)
┌──────────────────────────────────────────────────────────────────────────┐
│ id (PK)  │  tenant_id  │  table_name  │  record_id  │  action (enum)    │
│  old_data JSONB  │  new_data JSONB  │  changed_by  │  created_at        │
└──────────────────────────────────────────────────────────────────────────┘
  Partition: gl_audit_log_2025_h1 (2025-01-01 .. 2025-07-01)
  Partition: gl_audit_log_2025_h2 (2025-07-01 .. 2026-01-01)
  Partition: gl_audit_log_2026_h1 (2026-01-01 .. 2026-07-01)
  [future partitions created programmatically]

gl_sped_exports
┌────────────────────────────────────────────────────────────────────┐
│ id (PK)  │  tenant_id  │  export_type  │  fiscal_year, period     │
│  generated_at  │  generated_by  │  file_path  │  checksum         │
│  status  │  metadata JSONB                                         │
└────────────────────────────────────────────────────────────────────┘

3.1 Relationship Summary

Relationship	Type	Constraint
gl_accounts.parent_id → gl_accounts.id	Self-referential	Optional (root accounts have NULL parent)
gl_journal_entries.period_id → gl_fiscal_periods.id	Many-to-one	NOT NULL
gl_journal_entries.reversal_of_id → gl_journal_entries.id	Self-referential	Optional (reversal linkage)
gl_journal_entries.reversed_by_id → gl_journal_entries.id	Self-referential	Optional (back-reference)
gl_journal_lines.journal_entry_id → gl_journal_entries.id	Many-to-one	NOT NULL, CASCADE DELETE
gl_journal_lines.account_id → gl_accounts.id	Many-to-one	NOT NULL
gl_account_balances.account_id → gl_accounts.id	Many-to-one	NOT NULL
gl_account_balances.period_id → gl_fiscal_periods.id	Many-to-one	NOT NULL
gl_audit_log	Append-only, no FK	Trigger-populated from all tables
gl_sped_exports	Independent per tenant	References period by fiscal_year/period columns
gl_exchange_rates	Independent per tenant	Referenced by application layer during posting

---

4. Table Summary

Table	Row Count (Seed)	Purpose	Primary Indexes	RLS Enabled
gl_accounts	~150	Chart of Accounts with self-referential hierarchy, SPED mapping, tri-lingual names	(tenant_id, account_code) UNIQUE; (tenant_id, parent_id); (tenant_id, account_type, account_status)	Yes
gl_fiscal_periods	~12	Period management with state machine enforcement (FUTURE/OPEN/CLOSED/LOCKED)	(tenant_id, fiscal_year, period_number) UNIQUE; (tenant_id, period_state)	Yes
gl_journal_entries	~50	Journal entry headers with status workflow and reversal linkage	(tenant_id, entry_date, entry_number) UNIQUE; (tenant_id, period_id, entry_status)	Yes
gl_journal_lines	~200	Individual debit/credit lines, 2-slot currency, up to 5 dimensions	(tenant_id, journal_entry_id, line_number) UNIQUE; (tenant_id, account_id)	Yes
gl_account_balances	~150	Materialized balance cache, upserted by trigger on each journal line insert	(tenant_id, account_id, period_id) UNIQUE; (tenant_id, period_id)	Yes
gl_exchange_rates	~30	FX rates by type (SPOT/AVERAGE/CLOSING) and source, per effective date	(tenant_id, from_currency, to_currency, rate_type, effective_date) UNIQUE	Yes
gl_audit_log	~500	Immutable audit trail for all GL mutations; range-partitioned by created_at	(tenant_id, table_name, record_id) on each partition; (tenant_id, created_at)	Yes
gl_sped_exports	~5	SPED ECD/ECF/EFD export job tracking with checksum and file path	(tenant_id, export_type, fiscal_year, period) UNIQUE	Yes

---

5. Enum Types Reference

PostgreSQL native ENUM types are used for all columns with a bounded domain of values. Enums enforce at the database level that no invalid value can be stored regardless of application-layer validation.

5.1 gl_account_type

Classifies an account within the standard accounting equation.

Value	Description	Normal Balance
ASSET	Resources owned or controlled	DEBIT
LIABILITY	Obligations owed to creditors	CREDIT
EQUITY	Owner's residual interest	CREDIT
REVENUE	Income from operations	CREDIT
EXPENSE	Costs incurred in operations	DEBIT

5.2 gl_normal_balance

Defines which side of the ledger increases the account. Used in balance reporting to correctly sign net balances.

Value	Description
DEBIT	Account increases with debits (Assets, Expenses)
CREDIT	Account increases with credits (Liabilities, Equity, Revenue)

5.3 gl_account_status

Controls whether an account is available for posting.

Value	Description	Posting Allowed
ACTIVE	Normal operational state	Yes
INACTIVE	Soft-disabled; preserved for history	No (trigger enforced)
BLOCKED	Hard-blocked pending review or compliance action	No (trigger enforced)

5.4 gl_period_state

Implements the fiscal period state machine. Transitions are enforced by trg_period_state_machine and trg_prevent_closed_period_entry.

Value	Description	Journal Posting	State Transitions Allowed
FUTURE	Period not yet open	No	→ OPEN
OPEN	Current active period	Yes	→ CLOSED
CLOSED	Period closed; no new entries	No	→ LOCKED, → OPEN (reopen)
LOCKED	Permanently sealed for compliance	No	None (terminal state)

5.5 gl_entry_status

Tracks journal entry lifecycle from draft to reversal.

Value	Description	Lines Mutable
DRAFT	Work in progress; not yet validated	Yes
POSTED	Validated, balanced, and committed	No (trigger enforced)
REVERSED	Superseded by a reversal entry	No

5.6 gl_module_source

Identifies the originating module that created a journal entry. Used for subledger reconciliation and financial close automation.

Value	Description
BUDGET	Budget allocation entries
FORECAST	Rolling forecast entries
MANUAL	Human-entered journal entries
CONSOLIDATION	Inter-entity consolidation entries
ADJUSTMENT	Audit or correction adjustments
SYSTEM	System-generated entries (e.g., period carry-forward, revaluation)

5.7 gl_rate_type

Classifies the economic measurement context of an exchange rate.

Value	Description	Usage
SPOT	Rate at a specific point in time	Transaction-date translation
AVERAGE	Period-average rate	Income statement translation (IAS 21)
CLOSING	Rate at period-end	Balance sheet translation (IAS 21)

5.8 gl_rate_source

Records the provenance of an exchange rate for auditor traceability.

Value	Description
MANUAL	Entered by a user; requires approval workflow in production
BCB_API	Brazilian Central Bank (Banco Central do Brasil) automated feed
BLOOMBERG	Bloomberg Terminal / Bloomberg Data License automated feed
SYSTEM	System-computed (e.g., cross-rate derived from two base-currency rates)

5.9 gl_audit_action

Records the type of mutation event captured in the audit log.

Value	Description
INSERT	New record created
UPDATE	Existing record modified (field-level change captured in old_data/new_data)
STATUS_CHANGE	Status field changed (explicit capture for compliance workflows)

5.10 gl_sped_natura

Maps GL accounts to SPED (Brazilian tax authority) account nature codes for regulatory export compliance. These codes are defined by the Brazilian Receita Federal.

Value	Portuguese Label	Description
01	Ativo	Asset accounts
02	Passivo	Liability accounts
03	PL	Equity (Patrimonio Liquido) accounts
04	Resultado	Income and expense accounts (P&L)
05	Compensacao	Compensating accounts (off-balance sheet)
09	Outras	Other accounts not fitting standard classification

---

6. Index Architecture

Indexes are designed around the two most common access patterns: tenant-scoped queries with account or period filters, and time-range queries for audit and reporting.

6.1 Primary Index Strategy

Every composite index begins with tenant_id as the leading key. This ensures PostgreSQL uses the index for all tenant-scoped queries, which represent 100% of production queries. Cross-tenant queries are an administrative function executed only by the gl_admin role and are not performance-critical.

-- Pattern: all GL indexes lead with tenant_id
CREATE UNIQUE INDEX uq_gl_accounts_tenant_code
  ON gl_accounts (tenant_id, account_code);

CREATE INDEX idx_gl_journal_entries_tenant_period_status
  ON gl_journal_entries (tenant_id, period_id, entry_status);

CREATE INDEX idx_gl_journal_lines_tenant_account
  ON gl_journal_lines (tenant_id, account_id);

CREATE UNIQUE INDEX uq_gl_account_balances_tenant_account_period
  ON gl_account_balances (tenant_id, account_id, period_id);

6.2 Audit Log Index Strategy

Partition-local indexes are created on each gl_audit_log partition automatically. Queries against the audit log specify tenant_id and a created_at date range, allowing PostgreSQL partition pruning to scan only the relevant partition.

-- Applied to each partition individually
CREATE INDEX idx_gl_audit_log_{period}_tenant_table
  ON gl_audit_log_{period} (tenant_id, table_name, record_id);

CREATE INDEX idx_gl_audit_log_{period}_tenant_created
  ON gl_audit_log_{period} (tenant_id, created_at);

6.3 Constraint Naming Convention

Prefix	Type	Example
uq_	UNIQUE constraint	uq_gl_accounts_tenant_code
ck_	CHECK constraint	ck_gl_journal_lines_nonzero
idx_	Non-unique index	idx_gl_journal_entries_tenant_period
fk_	Foreign key	fk_gl_journal_lines_account

---

7. Trigger & Business Logic Inventory

All business logic enforced at the database layer. No application-layer bypass is possible for any trigger-enforced rule.

Trigger Name	Table	Event	Purpose
trg_enforce_double_entry	gl_journal_entries	AFTER INSERT/UPDATE	Verifies SUM(debits) = SUM(credits) across all lines on status → POSTED
trg_prevent_posted_modification	gl_journal_lines	BEFORE UPDATE/DELETE	Blocks modification of lines belonging to POSTED entries
trg_prevent_closed_period_entry	gl_journal_entries	BEFORE INSERT/UPDATE	Rejects entries into CLOSED or LOCKED periods
trg_check_period_on_post	gl_journal_entries	BEFORE UPDATE	Verifies period is OPEN at the moment of posting
trg_update_balances	gl_journal_lines	AFTER INSERT	Incremental upsert into gl_account_balances
trg_audit_journal_entries	gl_journal_entries	AFTER INSERT/UPDATE	Writes audit record to gl_audit_log
trg_audit_journal_lines	gl_journal_lines	AFTER INSERT/UPDATE	Writes audit record to gl_audit_log
trg_audit_accounts	gl_accounts	AFTER UPDATE	Writes audit record to gl_audit_log
trg_prevent_audit_mutation	gl_audit_log	BEFORE UPDATE/DELETE	Unconditionally raises exception; audit log is append-only
trg_period_state_machine	gl_fiscal_periods	BEFORE UPDATE	Enforces valid state transitions (FUTURE→OPEN→CLOSED→LOCKED)
trg_prevent_inactive_account_posting	gl_journal_lines	BEFORE INSERT	Rejects lines referencing INACTIVE or BLOCKED accounts
trg_updated_at_*	All tables	BEFORE UPDATE	Sets updated_at = NOW() on every mutation

7.1 Stored Function Inventory

Function	Returns	Purpose
gl_verify_balances(tenant_id, period_id)	TABLE(account_id, discrepancy)	Identifies accounts where materialized balance diverges from SUM of journal lines
gl_get_trial_balance(tenant_id, period_id)	TABLE(account, debit, credit)	Returns trial balance view for a given tenant and period
gl_carry_forward_balances(tenant_id, from_period, to_period)	void	Copies balance sheet account balances to open a new period
gl_recompute_balances(tenant_id, period_id)	void	Full recompute of gl_account_balances from journal lines (repair function)

---

8. Migration History

8.1 Applied Migrations

Migration	File	Description	Status	Applied
001	001_core_schema.sql	Foundation: all 8 tables, 10 enum types, all indexes, all triggers, partitioned audit log, RLS policies	Applied	Prototype phase
002	002_account_name_translations.sql	Adds name_en and name_es columns to gl_accounts for tri-lingual Chart of Accounts (PT source, EN and ES translations)	Applied	Prototype phase
003	003_account_currency.sql	Adds currency VARCHAR(3) column to gl_accounts with default BRL; enables per-account currency designation for multi-currency environments	Applied	Prototype phase

8.2 Pending Migrations (Roadmap)

Four additional phases are defined in CFS-DB-005. The following is a summary.

Phase	Migrations	Key Changes	Priority
Phase 1	004–007	Entity hierarchy (gl_entities), three-slot currency on journal lines, document_date column	P0 — Blocks commercial launch
Phase 2	008–012	Consolidation schema, RBAC tables, dimension master tables, period close checklist	P1
Phase 3	013–016	CoA templates, regulatory metadata on periods, tax jurisdiction metadata on accounts	P2
Phase 4	017–020	Hash partitioning for hot tables, approval workflow, intercompany partner tracking	P2/P3

See CFS-DB-005 for full migration specifications, DDL statements, and rollback procedures.

---

9. Multi-Currency Architecture

9.1 Current Two-Slot Model

Journal lines currently carry two currency representations.

-- Current currency columns on gl_journal_lines
original_amount    NUMERIC(20,6) NOT NULL,    -- Amount in transaction currency
original_currency  VARCHAR(3)    NOT NULL,    -- ISO 4217 currency code (e.g., USD, EUR)
debit_amount       NUMERIC(20,6),             -- Amount translated to functional currency
credit_amount      NUMERIC(20,6),             -- Amount translated to functional currency
-- Functional currency is set at the tenant level; currently defaulted to BRL

This model supports the common case: a transaction denominated in a foreign currency, translated to the entity's functional currency at the spot rate on the transaction date.

9.2 Target Three-Slot Model (Phase 1 Migration)

IAS 21 (The Effects of Changes in Foreign Exchange Rates), ASC 830 (Foreign Currency Matters), and CPC 02 R2 require three distinct currency measurements for each journal line.

Slot	Column Names	Rate Applied	Standard
Transaction	transaction_amount, transaction_currency	None (source)	All standards
Functional	functional_debit, functional_credit	Spot rate at transaction date	IAS 21, ASC 830
Reporting	reporting_debit, reporting_credit	Closing rate at period-end	IAS 21, ASC 830

The three-slot model enables USD-denominated financial statements for entities whose functional currency is BRL, EUR, or another non-USD currency. This is a P0 requirement for multi-national commercial deployment.

9.3 Exchange Rate Table

gl_exchange_rates stores rates by type, source, and effective date. The unique constraint (tenant_id, from_currency, to_currency, rate_type, effective_date) ensures one canonical rate per currency pair per type per date. Rate corrections currently overwrite this record; version history is planned for Phase 4 (Migration 018).

-- Exchange rate lookup (application layer)
SELECT rate
FROM gl_exchange_rates
WHERE tenant_id = current_setting('app.current_tenant')::uuid
  AND from_currency = 'USD'
  AND to_currency   = 'BRL'
  AND rate_type     = 'SPOT'
  AND effective_date = $entry_date
LIMIT 1;

---

10. Audit & Compliance Architecture

10.1 Partition Strategy

The gl_audit_log table is declared as a partitioned parent table. Partitions are created in six-month intervals keyed on created_at. New partitions are created programmatically before the start of each new half-year period.

-- Parent table declaration
CREATE TABLE gl_audit_log (
    id            UUID         NOT NULL DEFAULT uuid_generate_v4(),
    tenant_id     UUID         NOT NULL,
    table_name    VARCHAR(100) NOT NULL,
    record_id     UUID         NOT NULL,
    action        gl_audit_action NOT NULL,
    old_data      JSONB,
    new_data      JSONB,
    changed_by    UUID,
    created_at    TIMESTAMPTZ  NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Half-year partitions
CREATE TABLE gl_audit_log_2026_h1
    PARTITION OF gl_audit_log
    FOR VALUES FROM ('2026-01-01') TO ('2026-07-01');

CREATE TABLE gl_audit_log_2026_h2
    PARTITION OF gl_audit_log
    FOR VALUES FROM ('2026-07-01') TO ('2027-01-01');

10.2 Audit Record Structure

Every gl_audit_log record captures the complete before and after state of a mutated row in JSONB. This enables field-level change analysis without additional instrumentation.

Column	Type	Description
table_name	VARCHAR	The GL table that was mutated
record_id	UUID	Primary key of the mutated row
action	gl_audit_action	INSERT, UPDATE, or STATUS_CHANGE
old_data	JSONB	Complete row state before mutation (NULL for INSERT)
new_data	JSONB	Complete row state after mutation
changed_by	UUID	User UUID from application session context
created_at	TIMESTAMPTZ	Immutable event timestamp (UTC)

10.3 Compliance Target Mapping

Standard	Requirement	Implementation
SPED ECD	Immutable audit trail for Brazilian electronic accounting	gl_audit_log append-only; gl_sped_exports job tracking
SOX Section 404	Internal control evidence, audit trail retention	trg_prevent_audit_mutation; 7-year partition retention policy
IAS 21 / ASC 830	Three-slot currency on all journal lines	Planned Phase 1 migration (004_three_slot_currency.sql)
GoBD	Document date distinct from posting date	Planned Phase 1 migration (007_document_date.sql)
EU VAT	Tax jurisdiction metadata on transactions	Planned Phase 3 migration (013_tax_jurisdiction.sql)

10.4 SPED Export Workflow

The gl_sped_exports table tracks the generation of SPED ECD (electronic accounting records), ECF (corporate income tax), and EFD (digital tax bookkeeping) export files. Each export job record stores the file path, SHA-256 checksum, generation timestamp, and current status, enabling proof of regulatory submission for audit purposes.

---

11. Naming Conventions

Consistent naming across all GL schema objects ensures readability, tool compatibility, and predictable behavior for automated migration scripts.

11.1 Identifier Standards

Convention	Rule	Example
Table prefix	All GL tables prefixed gl_	gl_accounts, gl_journal_entries
Case	snake_case for all identifiers	account_code, tenant_id
Primary keys	UUID, generated by uuid_generate_v4()	id UUID PRIMARY KEY DEFAULT uuid_generate_v4()
Tenant column	Always tenant_id UUID NOT NULL, always first non-PK column	tenant_id UUID NOT NULL REFERENCES tenants(id)
Timestamps	created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW()	Set and maintained by trg_updated_at_* triggers
Audit columns	created_by UUID, updated_by UUID (user UUID from session)	Populated by application from JWT claims
Enums	Prefixed gl_, underscore-separated values in SCREAMING_SNAKE	gl_period_state, value FUTURE

11.2 Constraint Naming

Prefix	Type	Example
uq_	UNIQUE constraint or UNIQUE INDEX	uq_gl_accounts_tenant_code
ck_	CHECK constraint	ck_gl_journal_lines_debit_or_credit
fk_	FOREIGN KEY constraint	fk_gl_journal_lines_entry
idx_	Non-unique index	idx_gl_journal_entries_tenant_period_status
trg_	Trigger	trg_enforce_double_entry
fn_	Stored function	(used in application layer documentation)

11.3 Partition Naming

Audit log partitions follow the pattern gl_audit_log_{YYYY}_{h1|h2}:

gl_audit_log_2025_h1   -- 2025-01-01 to 2025-07-01
gl_audit_log_2025_h2   -- 2025-07-01 to 2026-01-01
gl_audit_log_2026_h1   -- 2026-01-01 to 2026-07-01

---

12. Technology Stack

12.1 Database Layer

Component	Specification	Notes
Database engine	PostgreSQL 16+	Minimum version enforced by trg_period_state_machine trigger syntax
Extension: uuid-ossp	uuid_generate_v4()	All primary key generation
Extension: pgcrypto	crypt(), gen_salt()	Sensitive field hashing where applicable
Multi-tenancy	Shared-schema + RLS	SET LOCAL app.current_tenant per transaction
Partitioning	Range partitioning on created_at	Applied to gl_audit_log; hot table hash partitioning planned Phase 4

12.2 Application Layer

Component	Specification	Notes
Runtime	Node.js 20 LTS	TypeScript strict mode
PostgreSQL driver	pg (node-postgres)	Connection pool with tenant context injection middleware
ORM / query builder	Knex.js	Raw SQL for trigger-critical paths; Knex for routine CRUD
Migrations	Knex migrations	Sequential numbered SQL files; never destructive
Connection pool	pg-pool	Min 5, Max 20 per pod; PgBouncer in front for 10,000+ user scale

12.3 Tenant Context Injection Pattern

The Node.js connection pool wraps every acquired connection with tenant context injection before the SQL is executed.

// Middleware pattern: inject tenant context on every connection
async function withTenantContext<T>(
  pool: Pool,
  tenantId: string,
  fn: (client: PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query(
      "SELECT set_config('app.current_tenant', $1, true)",
      [tenantId]
    );
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

The set_config(..., true) call with is_local = true mirrors SET LOCAL behavior: the configuration variable is cleared when the transaction ends, ensuring the connection pool cannot leak tenant context between requests.

12.4 Infrastructure

Component	Specification
Deployment	Google Kubernetes Engine (GKE); one PostgreSQL StatefulSet per environment
Database storage	GCP Cloud SQL (PostgreSQL 16) for managed operations; direct StatefulSet for maximum performance
Backup	pg_dump + Cloud Storage; point-in-time recovery via WAL archiving
Monitoring	pg_stat_statements, pg_stat_activity; exported to Cloud Monitoring
Connection proxy	Cloud SQL Auth Proxy; PgBouncer in transaction pooling mode for scale

---

13. Capacity & Performance Characteristics

13.1 Current Prototype Scale

Table	Seed Row Count	Target Production (Year 1)	Target Production (Year 3)
gl_accounts	~150	500 per tenant × 1,000 tenants = 500,000	500 per tenant × 10,000 tenants = 5M
gl_fiscal_periods	~12	12–24 per tenant	12–24 per tenant
gl_journal_entries	~50	10,000 per tenant/year	100,000 per tenant/year
gl_journal_lines	~200	50,000 per tenant/year	500,000 per tenant/year
gl_account_balances	~150	6,000 per tenant/year	60,000 per tenant/year
gl_audit_log	~500	200,000 per tenant/year	2,000,000 per tenant/year

13.2 Performance Targets

Query Type	Target P95 Response Time	Mechanism
Trial balance retrieval	< 50 ms	Reads gl_account_balances (materialized)
Journal entry list (paginated, 50 rows)	< 100 ms	Composite index on (tenant_id, period_id, entry_status)
Audit log query (30-day window)	< 200 ms	Partition pruning + partition-local index
Journal entry POST (with balance check)	< 300 ms	Synchronous trigger execution within transaction
Full balance recompute (repair)	< 5 s per tenant per period	gl_recompute_balances() batch function

13.3 Scalability Path

The current schema supports single-node PostgreSQL with connection pooling to approximately 500 concurrent tenants. The following architectural steps are defined for scale-out:

Scale Tier	Tenants	Mechanism
Prototype	1–50	Single Cloud SQL instance, direct connections
Pilot	50–500	Cloud SQL + PgBouncer transaction pooling
Commercial	500–5,000	Cloud SQL read replicas for reporting queries; CQRS separation
Enterprise	5,000+	Hash partitioning of hot tables by tenant_id; read replica per shard; eventual Citus for distributed PostgreSQL

---

14. Related Documents

This document is the entry point to the GL Engine database documentation series. The following documents provide detailed specifications for specific subsystems.

Document Code	Title	Description
CFS-DB-001	Database Architecture Overview	This document. System-level overview, ER diagram, enum reference, naming conventions
CFS-DB-002	Core Schema Reference	Complete DDL for all 8 tables, all 10 enum types, all constraints and indexes. Annotated SQL with design rationale
CFS-DB-003	Security & Multi-Tenancy Model	RLS policy specifications, role permissions, tenant context injection, audit trail immutability
CFS-DB-004	Business Logic Layer	Complete trigger and stored function specifications with SQL source, error codes, and test procedures
CFS-DB-005	Migration Roadmap & Target Schema	Gap analysis (20 identified gaps), four-phase migration plan, target schema DDL, rollback procedures
CFS-004	Technical Architecture	System-level C4 architecture, microservices topology, API gateway design, infrastructure specification

Document Reading Order for New Engineering Team Members

1. CFS-DB-001 (this document) — understand the overall structure, principles, and table inventory
2. CFS-DB-003 — understand how tenant isolation and security are enforced
3. CFS-DB-004 — understand all database-level business rules before writing application code
4. CFS-DB-002 — reference for DDL details during implementation
5. CFS-DB-005 — understand the migration roadmap before making any schema changes

---

Hal Casteel CEO/CTO, AZ1.AI Inc. Copyright 2026 AZ1.AI Inc. All rights reserved.

This document is subject to version control. Changes must be approved by the document owner and reflected in the document version history. The current version is 1.0.0, dated 2026-02-20.