ADR: GL Engine Architecture
Status: Accepted Date: 2026-02-19 Decision Makers: Hal Casteel (Lead Architect)
Context
Avivatec's FP&A platform requires a production-grade General Ledger engine that:
- Enforces double-entry integrity at the database level
- Complies with Brazilian SPED ECD export requirements (IN RFB 2.003/2021)
- Supports multi-tenancy via PostgreSQL Row-Level Security
- Provides multi-currency support with revaluation
- Includes AI-native hooks (NLQ, anomaly detection, account mapping)
Decision
Build a self-contained GL Engine as a TypeScript service layer over PostgreSQL 16+, using database triggers for business rule enforcement rather than application-level validation alone.
Key Architectural Choices
| Decision | Choice | Rationale |
|---|---|---|
| Language | TypeScript (Node.js 20+) | Type safety, team familiarity, Avivatec stack alignment |
| Database | PostgreSQL 16+ | RLS, triggers, JSONB, partitioning, mature ecosystem |
| Multi-tenancy | Row-Level Security (RLS) | Database-enforced isolation, no application-level filtering bugs |
| Double-entry | Statement-level trigger | All lines validated atomically in a single INSERT |
| Immutability | Audit log triggers + UPDATE/DELETE prevention | Regulatory compliance, tamper-proof trail |
| Period control | State machine trigger (FUTURE->OPEN->CLOSED->LOCKED) | Prevents posting to wrong periods at DB level |
| SPED export | ISO-8859-1, pipe-delimited, SHA-256 hash | Per Brazilian federal requirements |
| Event system | NATS JetStream (with in-memory fallback) | Decoupled event publishing for downstream consumers |
| Testing | Vitest with forked pool | Tenant isolation between parallel test suites |
Architecture Layers
GL-PROTOTYPE/
src/
types/ # Type definitions (gl.types.ts, errors.ts, sped.types.ts)
services/ # Business logic (account, journal, period, balance, currency, sped, audit, ai, report)
events/ # Event publisher (NATS / in-memory)
schema/
migrations/ # DDL (tables, indexes, constraints)
triggers/ # PL/pgSQL triggers
rls/ # Row-Level Security policies
functions/ # Stored functions (verify_balances, carry_forward, recompute, trial_balance)
seed/ # Sample Brazilian Chart of Accounts
tests/
integration/ # 8 test suites, 37 scenarios
Database Schema (8 Tables)
| Table | Purpose | RLS | Audit |
|---|---|---|---|
gl_accounts | Chart of Accounts | Yes | Yes |
gl_fiscal_periods | Period state machine | Yes | Yes |
gl_journal_entries | Journal headers | Yes | Yes |
gl_journal_lines | Journal line items | Yes | Yes |
gl_account_balances | Period balances (materialized) | Yes | Yes |
gl_exchange_rates | FX rate time-series | Yes | No |
gl_audit_log | Immutable audit trail (partitioned) | Yes | N/A |
gl_sped_exports | SPED export records | Yes | No |
Trigger Enforcement
| Trigger | Level | Purpose |
|---|---|---|
fn_enforce_double_entry | STATEMENT (AFTER INSERT on gl_journal_lines) | Validates debits = credits for the entire entry |
fn_prevent_posted_entry_modification | ROW (BEFORE UPDATE on gl_journal_entries) | Blocks changes to POSTED entries except status transitions |
fn_check_period_on_post | ROW (BEFORE UPDATE on gl_journal_entries) | Ensures period is OPEN when posting |
fn_period_state_machine | ROW (BEFORE UPDATE on gl_fiscal_periods) | Enforces FUTURE->OPEN->CLOSED->LOCKED transitions |
fn_audit_log | ROW (AFTER INSERT/UPDATE on gl_* tables) | Records all changes to immutable audit log |
fn_prevent_audit_modification | ROW (BEFORE UPDATE/DELETE on gl_audit_log) | Makes audit log tamper-proof |
Consequences
Positive
- Double-entry integrity guaranteed at database level regardless of application bugs
- Multi-tenancy isolation impossible to bypass from application code
- SPED compliance built into the export pipeline with pre-validation
- Audit trail is immutable and complete
- 37 integration tests cover all critical paths
Negative
- Statement-level triggers require batch INSERT (cannot insert lines one-at-a-time)
- RLS requires SET LOCAL within transactions for every query
- PostgreSQL-specific (not portable to other databases)
Risks
- Performance under high-volume: mitigated by partitioned audit log and indexed queries
- Currency revaluation complexity: simplified in prototype, needs production hardening
Test Coverage
| Suite | Tests | Coverage |
|---|---|---|
| Double-entry integrity | 7 | Balanced, unbalanced, 0-line, 10-line, rounding, concurrent |
| Period control | 8 | State transitions, posting to each state |
| Balance verification | 3 | 100-entry verify, corruption detect, close |
| Audit trail | 5 | Post audit, UPDATE/DELETE blocked, reversal chain, period close |
| Multi-tenancy | 3 | Account isolation, cross-tenant block, audit isolation |
| Idempotency | 2 | Duplicate ref reject, different refs allowed |
| SPED export | 5 | Balanced export, unmapped fail, pipe format, encoding, hash |
| Multi-currency | 4 | Rate time-series, revaluation, most-recent rate, missing rate |
| Total | 37 | All passing |