INTERNAL CONFIDENTIAL -- AZ1.AI Inc. -- Authorized Personnel Only This document contains proprietary and confidential information about the CODITECT Financial Suite database implementation. Unauthorized distribution, reproduction, or disclosure is strictly prohibited.
CFS-DB-002: Core Schema Reference
Classification: INTERNAL CONFIDENTIAL Owner: Hal Casteel, CEO/CTO, AZ1.AI Inc. Document Code: CFS-DB-002 Last Updated: 2026-02-20 Component: CODITECT Financial Suite -- General Ledger Engine Database: PostgreSQL 16+
Table of Contents
- Overview
- Database Prerequisites
- Enum Types
- 3.1 gl_account_type
- 3.2 gl_normal_balance
- 3.3 gl_account_status
- 3.4 gl_period_state
- 3.5 gl_entry_status
- 3.6 gl_module_source
- 3.7 gl_rate_type
- 3.8 gl_rate_source
- 3.9 gl_audit_action
- 3.10 gl_sped_natura
- Table Reference
- 4.1 gl_accounts -- Chart of Accounts
- 4.2 gl_fiscal_periods -- Period Management
- 4.3 gl_journal_entries -- Journal Entry Headers
- 4.4 gl_journal_lines -- Journal Entry Detail Lines
- 4.5 gl_account_balances -- Materialized Balance Cache
- 4.6 gl_exchange_rates -- Currency Exchange Rates
- 4.7 gl_audit_log -- Immutable Audit Trail
- 4.8 gl_sped_exports -- SPED Export Tracking
- Migration History
- Entity Relationship Summary
- Index Inventory
- Design Notes and Conventions
- Related Documents
1. Overview
This document is the authoritative schema reference for the CODITECT Financial Suite General Ledger Engine. It documents every table, column, constraint, index, and enum type in the GL Engine's PostgreSQL 16+ schema, including all changes introduced by the three migrations applied to date.
The GL Engine schema is designed around five architectural principles:
- Hard multi-tenancy isolation -- every table carries
tenant_id; Row-Level Security is enforced at the database layer (see CFS-DB-003). - Immutable audit trail -- the
gl_audit_logtable is append-only, range-partitioned, and protected by trigger-level mutation prevention. - Double-entry enforcement -- debit/credit balance is enforced by database trigger, not application logic alone (see CFS-DB-004).
- Brazilian fiscal compliance -- SPED mapping fields, SPED export tracking, and the
gl_sped_naturaenum are first-class schema concerns. - Multi-currency readiness -- every monetary line carries original currency, original amount, and exchange rate alongside functional-currency debit/credit columns.
Schema scope: 8 tables, 10 enum types, 3 migrations applied. The schema targets PostgreSQL 16 and relies on the uuid-ossp and pgcrypto extensions.
2. Database Prerequisites
The following PostgreSQL extensions must be enabled before applying migrations:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
| Extension | Purpose |
|---|---|
uuid-ossp | Provides uuid_generate_v4() used as the default primary key generator for all tables |
pgcrypto | Available for future encryption-at-rest patterns; required by the security layer |
Minimum PostgreSQL version: 16.0
Required privileges for migration execution: SUPERUSER or a role with CREATE, TRIGGER, and REFERENCES privileges on the target schema.
3. Enum Types
All enum types reside in the GL schema namespace and are created by migration 001_core_schema.sql. They are referenced as column types across multiple tables.
3.1 gl_account_type
Purpose: Classifies every account in the Chart of Accounts according to the five standard accounting categories. Controls normal balance derivation and financial statement placement.
CREATE TYPE gl_account_type AS ENUM (
'ASSET', 'LIABILITY', 'EQUITY', 'REVENUE', 'EXPENSE'
);
| Value | Description | Normal Balance | Statement |
|---|---|---|---|
ASSET | Economic resources owned or controlled | DEBIT | Balance Sheet |
LIABILITY | Obligations owed to external parties | CREDIT | Balance Sheet |
EQUITY | Residual interest of owners | CREDIT | Balance Sheet |
REVENUE | Income earned from operations | CREDIT | Income Statement |
EXPENSE | Costs incurred in generating revenue | DEBIT | Income Statement |
Used by: gl_accounts.account_type
3.2 gl_normal_balance
Purpose: Records the side of the accounting equation on which an account normally carries a positive balance. Used for balance sign normalization in reporting.
CREATE TYPE gl_normal_balance AS ENUM ('DEBIT', 'CREDIT');
| Value | Description |
|---|---|
DEBIT | Account increases on the debit side (left); assets, expenses |
CREDIT | Account increases on the credit side (right); liabilities, equity, revenue |
Used by: gl_accounts.normal_balance
3.3 gl_account_status
Purpose: Controls the operational state of a Chart of Accounts entry. Only ACTIVE accounts may receive journal line postings.
CREATE TYPE gl_account_status AS ENUM ('ACTIVE', 'INACTIVE', 'BLOCKED');
| Value | Description | Can Receive Postings |
|---|---|---|
ACTIVE | Account is in normal operational use | Yes |
INACTIVE | Account is retired but retained for historical reference | No |
BLOCKED | Account is administratively suspended; may be reactivated | No |
Used by: gl_accounts.status
3.4 gl_period_state
Purpose: Governs the lifecycle state of a fiscal period. Determines whether journal entries may be created, modified, or posted against the period.
CREATE TYPE gl_period_state AS ENUM ('FUTURE', 'OPEN', 'CLOSED', 'LOCKED');
| Value | Description | Accepts New Entries | Modification Allowed |
|---|---|---|---|
FUTURE | Period is defined but not yet active | No | Yes (by admin) |
OPEN | Period is active for posting | Yes | Yes |
CLOSED | Period is closed; balances are final | No | No |
LOCKED | Period is locked for regulatory/audit purposes; immutable | No | No |
State transition rules: FUTURE -> OPEN -> CLOSED -> LOCKED. Transitions are irreversible after CLOSED.
Used by: gl_fiscal_periods.state
3.5 gl_entry_status
Purpose: Tracks the lifecycle state of a journal entry header. Only POSTED entries contribute to account balances and appear in financial reports.
CREATE TYPE gl_entry_status AS ENUM ('DRAFT', 'POSTED', 'REVERSED');
| Value | Description | Affects Balances | Modifiable |
|---|---|---|---|
DRAFT | Entry is being prepared; not yet posted | No | Yes |
POSTED | Entry has been approved and posted; contributes to balances | Yes | No |
REVERSED | Entry has been reversed by a subsequent correcting entry | Yes (net zero) | No |
Used by: gl_journal_entries.status
3.6 gl_module_source
Purpose: Identifies the originating subsystem or workflow that created a journal entry. Enables source-segregated reporting and audit filtering.
CREATE TYPE gl_module_source AS ENUM (
'BUDGET', 'FORECAST', 'MANUAL', 'CONSOLIDATION', 'ADJUSTMENT', 'SYSTEM'
);
| Value | Description |
|---|---|
BUDGET | Entry originated from the budget module |
FORECAST | Entry originated from the forecast or rolling estimate module |
MANUAL | Entry was created manually by an authorized user |
CONSOLIDATION | Entry was generated by the intercompany consolidation process |
ADJUSTMENT | Entry is a period-end or audit adjustment |
SYSTEM | Entry was generated by automated system processes (e.g., currency revaluation) |
Used by: gl_journal_entries.module_source, gl_audit_log.module_source (TEXT column, not typed)
3.7 gl_rate_type
Purpose: Classifies the methodology used to determine an exchange rate. Different rate types are required for different accounting treatments.
CREATE TYPE gl_rate_type AS ENUM ('SPOT', 'AVERAGE', 'CLOSING');
| Value | Description | Typical Usage |
|---|---|---|
SPOT | Rate at a specific point in time | Transaction recording |
AVERAGE | Arithmetic average rate over a period | Income statement translation |
CLOSING | Rate at the close of a reporting period | Balance sheet translation |
Used by: gl_exchange_rates.rate_type
3.8 gl_rate_source
Purpose: Records the data provenance of an exchange rate, supporting audit requirements and data quality controls.
CREATE TYPE gl_rate_source AS ENUM ('MANUAL', 'BCB_API', 'BLOOMBERG', 'SYSTEM');
| Value | Description |
|---|---|
MANUAL | Rate entered manually by an authorized user |
BCB_API | Rate sourced from Banco Central do Brasil API (ptax) |
BLOOMBERG | Rate sourced from Bloomberg data feed |
SYSTEM | Rate calculated or propagated by system process |
Used by: gl_exchange_rates.source
3.9 gl_audit_action
Purpose: Classifies the type of change recorded in each audit log entry.
CREATE TYPE gl_audit_action AS ENUM ('INSERT', 'UPDATE', 'STATUS_CHANGE');
| Value | Description |
|---|---|
INSERT | A new record was created |
UPDATE | One or more fields on an existing record were modified |
STATUS_CHANGE | The status or state field of a record was changed (tracked separately for regulatory traceability) |
Note: DELETE is not a valid action; the GL schema does not permit hard deletes on financial records. Soft deletion is handled via status transitions.
Used by: gl_audit_log.action
3.10 gl_sped_natura
Purpose: Maps a Chart of Accounts entry to a Brazilian SPED (Sistema Publico de Escrituracao Digital) account nature classification. Required for ECD (Escrituracao Contabil Digital) export generation.
CREATE TYPE gl_sped_natura AS ENUM (
'01', '02', '03', '04', '05', '09'
);
| Value | Portuguese Description | English Description |
|---|---|---|
01 | Ativo | Asset |
02 | Passivo | Liability |
03 | Patrimonio Liquido | Shareholders' Equity |
04 | Resultado | Income/Result |
05 | Compensacao | Compensating/Contingency |
09 | Outras | Other |
Used by: gl_accounts.sped_natura
Regulatory reference: SPED ECD Leiaute, Registro I052, Campo "IND_CTA_EQTM_NAT_INF_COMPL"
4. Table Reference
4.1 gl_accounts -- Chart of Accounts
Purpose: The master Chart of Accounts registry. Stores every account available to a tenant for journaling, including hierarchical parent-child relationships, SPED compliance mapping, and tri-lingual name support.
Key characteristics:
- Self-referential hierarchy via
parent_idsupports unlimited depth account trees is_headerflag distinguishes summary (non-postable) accounts from leaf (postable) accounts- SPED mapping fields (
sped_natura,sped_cod_agl,sped_referencial_code) are maintained per-account for ECD export - Tri-lingual naming (
account_nameas Portuguese canonical,account_name_en,account_name_es) added by migration 002 - Per-account
currencycolumn added by migration 003
Column Specification:
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | UUID | NOT NULL | uuid_generate_v4() | Primary key |
tenant_id | UUID | NOT NULL | -- | Tenant isolation key; must match session context |
parent_id | UUID | NULL | -- | Self-referential FK to gl_accounts(id); NULL for root accounts |
account_number | TEXT | NOT NULL | -- | Unique account code within tenant (e.g., 1.1.01.001) |
account_name | TEXT | NOT NULL | -- | Canonical account name in Portuguese (primary language) |
account_name_en | TEXT | NULL | -- | English translation of account_name (migration 002) |
account_name_es | TEXT | NULL | -- | Spanish translation of account_name (migration 002) |
account_type | gl_account_type | NOT NULL | -- | Account classification: ASSET, LIABILITY, EQUITY, REVENUE, EXPENSE |
normal_balance | gl_normal_balance | NOT NULL | -- | Normal positive side: DEBIT or CREDIT |
status | gl_account_status | NOT NULL | 'ACTIVE' | Operational state: ACTIVE, INACTIVE, BLOCKED |
level | INTEGER | NOT NULL | 1 | Depth in account hierarchy; root = 1 |
is_header | BOOLEAN | NOT NULL | FALSE | TRUE = summary account (non-postable); FALSE = detail account (postable) |
currency | CHAR(3) | NOT NULL | 'BRL' | Functional currency for this account; ISO 4217 code (migration 003) |
sped_natura | gl_sped_natura | NULL | -- | SPED ECD account nature classification (01-05, 09) |
sped_cod_agl | TEXT | NULL | -- | SPED aggregation code (Codigo de Aglutinacao) |
sped_referencial_code | TEXT | NULL | -- | SPED referential plan code for ECD I052 register |
description | TEXT | NULL | -- | Free-text description or notes for this account |
metadata | JSONB | NULL | '{}' | Extensible key-value store for integration and UI-layer attributes |
created_at | TIMESTAMPTZ | NOT NULL | NOW() | Record creation timestamp |
created_by | UUID | NOT NULL | -- | User UUID who created the record |
updated_at | TIMESTAMPTZ | NOT NULL | NOW() | Last modification timestamp; maintained by trigger |
updated_by | UUID | NOT NULL | -- | User UUID who last modified the record |
Constraints:
| Constraint Name | Type | Definition | Description |
|---|---|---|---|
gl_accounts_pkey | PRIMARY KEY | (id) | Record identity |
gl_accounts_parent_id_fkey | FOREIGN KEY | parent_id REFERENCES gl_accounts(id) | Hierarchical self-reference; ON DELETE is not set -- parent deletion requires child reassignment first |
uq_account_number_tenant | UNIQUE | (tenant_id, account_number) | Account numbers are unique per tenant |
ck_account_level_positive | CHECK | level >= 1 | Level must be a positive integer; root accounts are level 1 |
Indexes:
| Index Name | Columns | Type | Description |
|---|---|---|---|
idx_gl_accounts_tenant | (tenant_id) | B-tree | Primary tenant filter; used by all tenant-scoped queries |
idx_gl_accounts_parent | (tenant_id, parent_id) | B-tree | Hierarchy traversal; supports parent-child tree queries |
idx_gl_accounts_type | (tenant_id, account_type) | B-tree | Account type filtering for trial balance and financial statements |
idx_gl_accounts_sped | (tenant_id, sped_natura) | B-tree | SPED export queries filtering by nature classification |
idx_gl_accounts_status | (tenant_id, status) | B-tree | Active account filtering; excludes INACTIVE/BLOCKED in UI and posting validation |
idx_gl_accounts_currency | (tenant_id, currency) | B-tree | Currency-specific account filtering (migration 003) |
4.2 gl_fiscal_periods -- Period Management
Purpose: Defines the calendar of fiscal periods for each tenant. Periods control which date ranges are open for posting and lock balances once closed. Supports up to 14 periods per fiscal year to accommodate Brazilian fiscal year conventions with adjustment periods.
Key characteristics:
- Period numbers 13 and 14 are reserved for adjustment periods (
is_adjustment = TRUE) - The
statelifecycle is strictly unidirectional:FUTURE->OPEN->CLOSED->LOCKED closed_at/closed_byandlocked_at/locked_byprovide regulatory-grade audit timestamps for period state transitions- Journal entries cannot be posted to
CLOSEDorLOCKEDperiods (enforced by triggertrg_prevent_closed_period_entry)
Column Specification:
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | UUID | NOT NULL | uuid_generate_v4() | Primary key |
tenant_id | UUID | NOT NULL | -- | Tenant isolation key |
fiscal_year | INTEGER | NOT NULL | -- | Four-digit fiscal year (e.g., 2025) |
period_number | INTEGER | NOT NULL | -- | Period sequence within fiscal year; 1-12 standard, 13-14 adjustment |
period_name | TEXT | NOT NULL | -- | Human-readable period label (e.g., "Janeiro 2025", "Ajuste Q4 2025") |
start_date | DATE | NOT NULL | -- | First calendar date of the period (inclusive) |
end_date | DATE | NOT NULL | -- | Last calendar date of the period (inclusive) |
state | gl_period_state | NOT NULL | 'FUTURE' | Lifecycle state: FUTURE, OPEN, CLOSED, LOCKED |
is_adjustment | BOOLEAN | NOT NULL | FALSE | TRUE for special adjustment periods (periods 13-14) |
closed_at | TIMESTAMPTZ | NULL | -- | Timestamp when period was moved to CLOSED state |
closed_by | UUID | NULL | -- | User UUID who performed the period close operation |
locked_at | TIMESTAMPTZ | NULL | -- | Timestamp when period was moved to LOCKED state |
locked_by | UUID | NULL | -- | User UUID who performed the period lock operation |
metadata | JSONB | NULL | '{}' | Extensible attributes |
created_at | TIMESTAMPTZ | NOT NULL | NOW() | Record creation timestamp |
created_by | UUID | NOT NULL | -- | User UUID who created the record |
updated_at | TIMESTAMPTZ | NOT NULL | NOW() | Last modification timestamp |
updated_by | UUID | NOT NULL | -- | User UUID who last modified the record |
Constraints:
| Constraint Name | Type | Definition | Description |
|---|---|---|---|
gl_fiscal_periods_pkey | PRIMARY KEY | (id) | Record identity |
uq_period_tenant_year_num | UNIQUE | (tenant_id, fiscal_year, period_number) | One period per number per year per tenant |
ck_period_dates | CHECK | end_date >= start_date | End date must not precede start date |
ck_period_number | CHECK | period_number >= 1 AND period_number <= 14 | Period number within valid range; 1-12 standard, 13-14 adjustment |
Indexes:
| Index Name | Columns | Type | Description |
|---|---|---|---|
idx_gl_periods_tenant | (tenant_id) | B-tree | Tenant-scoped period lookup |
idx_gl_periods_state | (tenant_id, state) | B-tree | Filters periods by lifecycle state; used when validating posting eligibility |
idx_gl_periods_dates | (tenant_id, start_date, end_date) | B-tree | Date-range queries to find the period containing a given entry date |
4.3 gl_journal_entries -- Journal Entry Headers
Purpose: The header record for each accounting transaction. A journal entry groups one or more detail lines (gl_journal_lines) that must collectively balance debits to credits. The header captures the transaction's identity, date, status, module origin, and reversal linkage.
Key characteristics:
reference_numberis a tenant-unique identifier (assigned by the creating module or user)total_amountis a denormalized cache of the sum of absolute debit values; maintained by trigger- Reversal chains are modeled via bidirectional FK pairs:
reverses_id(this entry reverses that one) andreversed_by_id(that entry reverses this one) POSTEDentries are immutable; modification is prevented by triggertrg_prevent_posted_modificationdescriptionis limited to 500 characters by constraint; this length is sufficient for all regulatory requirements including SPED
Column Specification:
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | UUID | NOT NULL | uuid_generate_v4() | Primary key |
tenant_id | UUID | NOT NULL | -- | Tenant isolation key |
reference_number | TEXT | NOT NULL | -- | Unique transaction reference within tenant (e.g., JE-2025-001234) |
entry_date | DATE | NOT NULL | -- | Accounting date of the transaction; must fall within an OPEN period |
description | TEXT | NOT NULL | -- | Narrative description; maximum 500 characters |
status | gl_entry_status | NOT NULL | 'DRAFT' | Lifecycle state: DRAFT, POSTED, REVERSED |
module_source | gl_module_source | NOT NULL | -- | Originating module: BUDGET, FORECAST, MANUAL, CONSOLIDATION, ADJUSTMENT, SYSTEM |
period_id | UUID | NOT NULL | -- | FK to gl_fiscal_periods(id); period must be OPEN at time of posting |
posted_at | TIMESTAMPTZ | NULL | -- | Timestamp when entry status changed to POSTED |
posted_by | UUID | NULL | -- | User UUID who posted the entry |
reversed_by_id | UUID | NULL | -- | FK to gl_journal_entries(id); the entry that reverses this one (populated on this entry when reversed) |
reverses_id | UUID | NULL | -- | FK to gl_journal_entries(id); the original entry this entry reverses (populated on the reversal entry) |
reversed_at | TIMESTAMPTZ | NULL | -- | Timestamp when the reversal was posted |
total_amount | NUMERIC(20,2) | NOT NULL | 0 | Denormalized sum of debit amounts across all lines; maintained by trigger |
metadata | JSONB | NULL | '{}' | Extensible attributes |
created_at | TIMESTAMPTZ | NOT NULL | NOW() | Record creation timestamp |
created_by | UUID | NOT NULL | -- | User UUID who created the record |
updated_at | TIMESTAMPTZ | NOT NULL | NOW() | Last modification timestamp |
updated_by | UUID | NOT NULL | -- | User UUID who last modified the record |
Constraints:
| Constraint Name | Type | Definition | Description |
|---|---|---|---|
gl_journal_entries_pkey | PRIMARY KEY | (id) | Record identity |
gl_je_period_id_fkey | FOREIGN KEY | period_id REFERENCES gl_fiscal_periods(id) | Entry must reference a valid period |
gl_je_reversed_by_id_fkey | FOREIGN KEY | reversed_by_id REFERENCES gl_journal_entries(id) | Self-referential; reversal chain back-pointer |
gl_je_reverses_id_fkey | FOREIGN KEY | reverses_id REFERENCES gl_journal_entries(id) | Self-referential; reversal chain forward-pointer |
uq_reference_tenant | UNIQUE | (tenant_id, reference_number) | Reference numbers are unique per tenant |
ck_description_length | CHECK | char_length(description) <= 500 | Description capped at 500 characters |
Indexes:
| Index Name | Columns | Type | Predicate | Description |
|---|---|---|---|---|
idx_gl_entries_tenant | (tenant_id) | B-tree | -- | Primary tenant filter |
idx_gl_entries_status | (tenant_id, status) | B-tree | -- | Status-based filtering for DRAFT/POSTED/REVERSED queries |
idx_gl_entries_period | (tenant_id, period_id) | B-tree | -- | Period-scoped entry retrieval for balance calculation and reporting |
idx_gl_entries_date | (tenant_id, entry_date) | B-tree | -- | Date-range queries for transaction history |
idx_gl_entries_source | (tenant_id, module_source) | B-tree | -- | Module-segregated reporting (e.g., all BUDGET entries) |
idx_gl_entries_reversal | (reverses_id) | B-tree | WHERE reverses_id IS NOT NULL | Partial index; efficient lookup of reversal entries without scanning non-reversal rows |
4.4 gl_journal_lines -- Journal Entry Detail Lines
Purpose: The individual debit or credit postings that compose a journal entry. Each line posts to exactly one account, carries one side of the double-entry (debit XOR credit), and includes multi-currency fields for the original transaction currency alongside the functional-currency amount.
Key characteristics:
- The
ck_one_side_onlyconstraint enforces that each line carries either a debit or a credit, never both and never neither - Multi-currency lines record
original_currency,original_amount, andexchange_rate; functional-currency amounts indebit_amount/credit_amountare derived viaoriginal_amount * exchange_rate - Five generic
dimension_Ncolumns (pluscost_center_idandproject_id) provide a flexible dimensional accounting structure without requiring schema changes as new dimensions are defined - Lines are immutable once the parent entry is
POSTED line_numberis sequenced within a journal entry and must be unique per entry
Column Specification:
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | UUID | NOT NULL | uuid_generate_v4() | Primary key |
tenant_id | UUID | NOT NULL | -- | Tenant isolation key; must match parent journal entry |
journal_entry_id | UUID | NOT NULL | -- | FK to gl_journal_entries(id) |
account_id | UUID | NOT NULL | -- | FK to gl_accounts(id); must be an ACTIVE, non-header account |
line_number | INTEGER | NOT NULL | -- | Sequential line number within the entry; 1-based |
debit_amount | NUMERIC(20,2) | NOT NULL | 0 | Functional-currency debit amount; 0 when line is a credit |
credit_amount | NUMERIC(20,2) | NOT NULL | 0 | Functional-currency credit amount; 0 when line is a debit |
original_currency | CHAR(3) | NOT NULL | 'BRL' | ISO 4217 currency code of the original transaction |
original_amount | NUMERIC(20,2) | NOT NULL | 0 | Amount in the original transaction currency |
exchange_rate | NUMERIC(18,8) | NOT NULL | 1.0 | Exchange rate applied: original_currency to functional currency |
cost_center_id | UUID | NULL | -- | Optional cost center dimension reference |
project_id | UUID | NULL | -- | Optional project dimension reference |
dimension_1 | TEXT | NULL | -- | Generic dimension slot 1 (e.g., product line) |
dimension_2 | TEXT | NULL | -- | Generic dimension slot 2 (e.g., region) |
dimension_3 | TEXT | NULL | -- | Generic dimension slot 3 (e.g., channel) |
dimension_4 | TEXT | NULL | -- | Generic dimension slot 4 (reserved for future use) |
dimension_5 | TEXT | NULL | -- | Generic dimension slot 5 (reserved for future use) |
description | TEXT | NULL | -- | Line-level narrative; supplements the entry-level description |
metadata | JSONB | NULL | '{}' | Extensible attributes |
created_at | TIMESTAMPTZ | NOT NULL | NOW() | Record creation timestamp |
created_by | UUID | NOT NULL | -- | User UUID who created the record |
Note: gl_journal_lines does not carry updated_at/updated_by columns because lines are immutable once the parent entry is posted. Pre-posting edits operate at the entry level; line corrections require entry reversal and re-entry after posting.
Constraints:
| Constraint Name | Type | Definition | Description |
|---|---|---|---|
gl_journal_lines_pkey | PRIMARY KEY | (id) | Record identity |
gl_jl_journal_entry_id_fkey | FOREIGN KEY | journal_entry_id REFERENCES gl_journal_entries(id) | Line must belong to a valid entry |
gl_jl_account_id_fkey | FOREIGN KEY | account_id REFERENCES gl_accounts(id) | Line must reference a valid account |
ck_one_side_only | CHECK | (debit_amount > 0 AND credit_amount = 0) OR (debit_amount = 0 AND credit_amount > 0) | Each line carries exactly one side; never both, never zero-zero |
ck_positive_amounts | CHECK | debit_amount >= 0 AND credit_amount >= 0 | Amounts cannot be negative |
ck_exchange_rate_positive | CHECK | exchange_rate > 0 | Exchange rate must be strictly positive |
uq_journal_line_number | UNIQUE | (journal_entry_id, line_number) | Line numbers are unique within an entry |
Indexes:
| Index Name | Columns | Type | Description |
|---|---|---|---|
idx_gl_lines_entry | (journal_entry_id) | B-tree | Retrieves all lines for a given entry; primary access pattern |
idx_gl_lines_account | (tenant_id, account_id) | B-tree | Account-centric queries for ledger and balance calculation |
idx_gl_lines_tenant | (tenant_id) | B-tree | Full tenant-scoped line scans |
4.5 gl_account_balances -- Materialized Balance Cache
Purpose: Stores pre-computed account balances per period per currency. This table is a performance cache -- balances are derived from gl_journal_lines and maintained by the trg_update_balances trigger. It eliminates the need to aggregate all journal lines for every balance inquiry.
Key characteristics:
- Balances are maintained incrementally by database trigger on every line insert/update affecting a posted entry
closing_balance=opening_balance+period_debits-period_credits(for DEBIT-normal accounts) or the inverse- The unique constraint
uq_balance_account_periodensures exactly one row per account-period-currency combination last_updated_atrecords the most recent trigger-driven update, distinct fromcreated_at- If a balance row does not exist when a trigger fires, it is inserted; otherwise it is updated (upsert semantics)
Column Specification:
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | UUID | NOT NULL | uuid_generate_v4() | Primary key |
tenant_id | UUID | NOT NULL | -- | Tenant isolation key |
account_id | UUID | NOT NULL | -- | FK to gl_accounts(id) |
period_id | UUID | NOT NULL | -- | FK to gl_fiscal_periods(id) |
currency | CHAR(3) | NOT NULL | 'BRL' | ISO 4217 currency code for this balance row |
opening_balance | NUMERIC(20,2) | NOT NULL | 0 | Balance carried forward from the prior period |
period_debits | NUMERIC(20,2) | NOT NULL | 0 | Total debit activity posted in this period |
period_credits | NUMERIC(20,2) | NOT NULL | 0 | Total credit activity posted in this period |
closing_balance | NUMERIC(20,2) | NOT NULL | 0 | Computed closing balance; maintained by trigger |
last_updated_at | TIMESTAMPTZ | NOT NULL | NOW() | Timestamp of last trigger-driven update |
metadata | JSONB | NULL | '{}' | Extensible attributes |
created_at | TIMESTAMPTZ | NOT NULL | NOW() | Record creation timestamp |
created_by | UUID | NOT NULL | -- | User UUID or system identifier that created the row |
Constraints:
| Constraint Name | Type | Definition | Description |
|---|---|---|---|
gl_account_balances_pkey | PRIMARY KEY | (id) | Record identity |
gl_ab_account_id_fkey | FOREIGN KEY | account_id REFERENCES gl_accounts(id) | Must reference a valid account |
gl_ab_period_id_fkey | FOREIGN KEY | period_id REFERENCES gl_fiscal_periods(id) | Must reference a valid period |
uq_balance_account_period | UNIQUE | (tenant_id, account_id, period_id, currency) | One balance row per account-period-currency combination |
Indexes:
| Index Name | Columns | Type | Description |
|---|---|---|---|
idx_gl_balances_tenant | (tenant_id) | B-tree | Tenant-scoped balance retrieval |
idx_gl_balances_account | (tenant_id, account_id) | B-tree | Account ledger history across periods |
idx_gl_balances_period | (tenant_id, period_id) | B-tree | Period-scoped trial balance generation |
4.6 gl_exchange_rates -- Currency Exchange Rates
Purpose: Maintains the exchange rate registry used to convert foreign currency transactions into the functional currency (BRL). Supports multiple rate types per currency pair per date to accommodate different accounting translation requirements.
Key characteristics:
- The unique constraint prevents duplicate rates for the same currency pair, rate type, and effective date
from_currency != to_currencyis enforced by constraint; self-rates are not stored- Rates are stored with 8 decimal places (
NUMERIC(18,8)) to preserve precision for currencies with large differentials - Rate lookup follows the effective date model: the rate with the most recent
effective_dateon or before the transaction date is used
Column Specification:
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | UUID | NOT NULL | uuid_generate_v4() | Primary key |
tenant_id | UUID | NOT NULL | -- | Tenant isolation key |
from_currency | CHAR(3) | NOT NULL | -- | Source ISO 4217 currency code (e.g., USD) |
to_currency | CHAR(3) | NOT NULL | -- | Target ISO 4217 currency code (e.g., BRL) |
rate_type | gl_rate_type | NOT NULL | -- | Rate methodology: SPOT, AVERAGE, CLOSING |
rate | NUMERIC(18,8) | NOT NULL | -- | Exchange rate: 1 unit of from_currency = rate units of to_currency |
effective_date | DATE | NOT NULL | -- | Date from which this rate is valid |
source | gl_rate_source | NOT NULL | 'MANUAL' | Data provenance: MANUAL, BCB_API, BLOOMBERG, SYSTEM |
metadata | JSONB | NULL | '{}' | Extensible attributes; stores API response metadata when source is BCB_API or BLOOMBERG |
created_at | TIMESTAMPTZ | NOT NULL | NOW() | Record creation timestamp |
created_by | UUID | NOT NULL | -- | User UUID or system identifier |
Constraints:
| Constraint Name | Type | Definition | Description |
|---|---|---|---|
gl_exchange_rates_pkey | PRIMARY KEY | (id) | Record identity |
uq_exchange_rate | UNIQUE | (tenant_id, from_currency, to_currency, rate_type, effective_date) | One rate per pair-type-date combination |
ck_rate_positive | CHECK | rate > 0 | Exchange rates must be strictly positive |
ck_different_currencies | CHECK | from_currency != to_currency | Prevents self-rate entries |
Indexes:
| Index Name | Columns | Type | Description |
|---|---|---|---|
idx_gl_rates_tenant | (tenant_id) | B-tree | Tenant-scoped rate retrieval |
idx_gl_rates_pair | (tenant_id, from_currency, to_currency, effective_date) | B-tree | Ordered pair lookup; supports "most recent rate on or before date" queries |
4.7 gl_audit_log -- Immutable Audit Trail
Purpose: Records every state-changing operation on GL entities as an immutable, tamper-evident log. This table is the primary instrument for regulatory compliance (CVM, SPED, SOX-equivalent) and forensic audit. It is range-partitioned by created_at for query performance and operational manageability.
Key characteristics:
- The composite primary key
(id, created_at)is required by PostgreSQL's partitioning rules; both columns must be present in the primary key when partitioning by a non-key column - No
UPDATEorDELETEis permitted on this table; enforced by triggertrg_prevent_audit_mutation - Partitioned into half-year segments from 2025 through 2027; new partitions must be created in advance before each half-year boundary
old_valuesandnew_valuesstore the full JSON snapshot of the record before and after the changesped_export_idis populated when an audit entry is referenced by a SPED export, enabling export reconciliation
Column Specification:
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | UUID | NOT NULL | uuid_generate_v4() | Record identifier; part of composite PK |
tenant_id | UUID | NOT NULL | -- | Tenant isolation key |
table_name | TEXT | NOT NULL | -- | Name of the source table that was modified (e.g., gl_journal_entries) |
record_id | UUID | NOT NULL | -- | Primary key of the affected record in the source table |
action | gl_audit_action | NOT NULL | -- | Type of change: INSERT, UPDATE, STATUS_CHANGE |
old_values | JSONB | NULL | -- | Full JSON snapshot of the record before the change; NULL for INSERT actions |
new_values | JSONB | NULL | -- | Full JSON snapshot of the record after the change; NULL for hypothetical future DELETE actions |
user_id | UUID | NULL | -- | UUID of the authenticated user who triggered the change |
session_id | UUID | NULL | -- | Application session identifier for session-level audit correlation |
ip_address | INET | NULL | -- | Client IP address at the time of the operation |
module_source | TEXT | NULL | -- | Originating module (TEXT here, not typed enum, to decouple audit log from module changes) |
sped_export_id | UUID | NULL | -- | FK to gl_sped_exports(id); populated during SPED export generation |
created_at | TIMESTAMPTZ | NOT NULL | NOW() | Event timestamp; partition key |
Primary Key: Composite (id, created_at) -- required for partitioned table compatibility in PostgreSQL.
Partitioning Strategy:
| Partition Name | Range Start | Range End | Covers |
|---|---|---|---|
gl_audit_log_2025_h1 | 2025-01-01 | 2025-07-01 | Jan-Jun 2025 |
gl_audit_log_2025_h2 | 2025-07-01 | 2026-01-01 | Jul-Dec 2025 |
gl_audit_log_2026_h1 | 2026-01-01 | 2026-07-01 | Jan-Jun 2026 |
gl_audit_log_2026_h2 | 2026-07-01 | 2027-01-01 | Jul-Dec 2026 |
gl_audit_log_2027_h1 | 2027-01-01 | 2027-07-01 | Jan-Jun 2027 |
gl_audit_log_2027_h2 | 2027-07-01 | 2028-01-01 | Jul-Dec 2027 |
Operational note: Partitions for 2028 and beyond must be created before January 1, 2028. A maintenance procedure or scheduled job should be established to pre-create partitions six months in advance of their activation date.
Indexes:
| Index Name | Columns | Type | Predicate | Description |
|---|---|---|---|---|
idx_gl_audit_tenant | (tenant_id, created_at) | B-tree | -- | Time-ordered audit history per tenant; primary access pattern for audit reports |
idx_gl_audit_record | (table_name, record_id, created_at) | B-tree | -- | Full history for a specific record; used by entity detail audit views |
idx_gl_audit_sped | (sped_export_id) | B-tree | WHERE sped_export_id IS NOT NULL | Partial index; efficient lookup of audit entries associated with a SPED export |
Note on index propagation: Indexes created on the parent partitioned table are automatically propagated to all existing and future partitions by PostgreSQL 16.
4.8 gl_sped_exports -- SPED Export Tracking
Purpose: Tracks the lifecycle and outcome of each SPED (Sistema Publico de Escrituracao Digital) export job. Records the export type, fiscal scope, processing status, file integrity hash, and any validation errors returned by pre-submission validation.
Key characteristics:
- Supports three export types: ECD (Escrituracao Contabil Digital), ECF (Escrituracao Contabil Fiscal), EFD (Escrituracao Fiscal Digital)
file_hashstores the SHA-256 hash of the generated SPED file for integrity verification and submission reconciliationvalidation_resultstores the structured output of pre-submission validation rules in JSONB formaterror_detailscaptures failure information whenstatusisFAILED- The
sped_export_idongl_audit_logrows links audit records to the export that triggered them
Column Specification:
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | UUID | NOT NULL | uuid_generate_v4() | Primary key |
tenant_id | UUID | NOT NULL | -- | Tenant isolation key |
export_type | TEXT | NOT NULL | -- | SPED module: ECD, ECF, or EFD |
fiscal_year | INTEGER | NOT NULL | -- | Four-digit fiscal year covered by this export |
period_start | DATE | NOT NULL | -- | First date of the reporting period |
period_end | DATE | NOT NULL | -- | Last date of the reporting period |
status | TEXT | NOT NULL | 'PENDING' | Lifecycle state: PENDING, GENERATING, COMPLETED, FAILED |
file_hash | TEXT | NULL | -- | SHA-256 hash of the generated file; NULL until COMPLETED |
file_size_bytes | BIGINT | NULL | -- | Size of the generated file in bytes; NULL until COMPLETED |
record_count | INTEGER | NULL | -- | Number of SPED records written to the file; NULL until COMPLETED |
validation_result | JSONB | NULL | -- | Structured pre-submission validation output; fields: passed (boolean), warnings (array), errors (array) |
error_details | JSONB | NULL | -- | Failure details when status = 'FAILED'; fields: error_code, error_message, stack_trace |
metadata | JSONB | NULL | '{}' | Extensible attributes |
created_at | TIMESTAMPTZ | NOT NULL | NOW() | Record creation timestamp |
created_by | UUID | NOT NULL | -- | User UUID who initiated the export |
completed_at | TIMESTAMPTZ | NULL | -- | Timestamp when export reached COMPLETED or FAILED state |
Constraints:
| Constraint Name | Type | Definition | Description |
|---|---|---|---|
gl_sped_exports_pkey | PRIMARY KEY | (id) | Record identity |
Note: export_type and status are intentionally TEXT columns rather than enum types. The SPED program evolves its module taxonomy over time; TEXT avoids the need for DDL changes when new export types or states are introduced, while validation_result JSONB accommodates evolving validation rule sets without schema changes.
Indexes:
| Index Name | Columns | Type | Description |
|---|---|---|---|
idx_gl_sped_tenant | (tenant_id) | B-tree | Tenant-scoped export history |
5. Migration History
Migrations are located at:
GL-PROTOTYPE/src/schema/migrations/
They are applied in sequential order. Each migration is idempotent where technically feasible (IF NOT EXISTS guards on index creation; IF NOT EXISTS on column additions). The runner script (src/schema/migrate.ts) tracks applied versions and will not re-apply a completed migration.
5.1 001_core_schema.sql
Applied: Initial deployment Classification: Foundation migration
Summary: Creates the entire GL Engine schema from scratch. This is the baseline migration that all subsequent migrations build upon.
Objects Created:
Extensions:
uuid-ossppgcrypto
Enum Types (10):
gl_account_typegl_normal_balancegl_account_statusgl_period_stategl_entry_statusgl_module_sourcegl_rate_typegl_rate_sourcegl_audit_actiongl_sped_natura
Tables (8):
gl_accountsgl_fiscal_periodsgl_journal_entriesgl_journal_linesgl_account_balancesgl_exchange_ratesgl_audit_log(partitioned parent)gl_sped_exports
Partitions (6):
gl_audit_log_2025_h1throughgl_audit_log_2027_h2
Indexes: All indexes documented in Section 4, excluding idx_gl_accounts_currency (added in migration 003).
Rollback note: Rolling back migration 001 drops all GL Engine objects. This is a destructive operation that must only be performed in development environments.
5.2 002_account_name_translations.sql
Applied: After 001_core_schema.sql Classification: Additive -- no breaking changes
Summary: Adds tri-lingual account name support to gl_accounts. The original account_name column is retained as the canonical Portuguese name. English and Spanish translations are stored in new nullable columns.
Objects Modified:
ALTER TABLE gl_accounts
ADD COLUMN IF NOT EXISTS account_name_en TEXT,
ADD COLUMN IF NOT EXISTS account_name_es TEXT;
COMMENT ON COLUMN gl_accounts.account_name_en IS 'English translation of account_name';
COMMENT ON COLUMN gl_accounts.account_name_es IS 'Spanish translation of account_name';
Impact:
- Both columns are nullable; existing rows receive
NULLvalues, which is acceptable since translations are populated asynchronously by the AI translation service - No index was added; translation-based filtering is not an expected query pattern
- No constraints were added; translation accuracy is enforced at the application layer
Rollback:
ALTER TABLE gl_accounts
DROP COLUMN IF EXISTS account_name_en,
DROP COLUMN IF EXISTS account_name_es;
5.3 003_account_currency.sql
Applied: After 002_account_name_translations.sql Classification: Additive -- no breaking changes
Summary: Adds a per-account functional currency column to gl_accounts. This corrects a design omission discovered during frontend integration: the UI expected a currency attribute on account records but the column was absent from the initial schema.
Root cause: The initial schema assumed currency would always be determined at the journal line level (gl_journal_lines.original_currency). The frontend integration revealed that account-level currency is needed for Chart of Accounts display, validation UI, and multi-currency account grouping.
Objects Modified:
ALTER TABLE gl_accounts
ADD COLUMN IF NOT EXISTS currency CHAR(3) NOT NULL DEFAULT 'BRL';
CREATE INDEX IF NOT EXISTS idx_gl_accounts_currency
ON gl_accounts(tenant_id, currency);
Impact:
NOT NULL DEFAULT 'BRL'ensures existing accounts receive a valid default without a data migration step- The default reflects the primary operating currency for Brazilian fiscal environments
- Accounts transacting in foreign currencies should have their
currencyfield updated after migration - The new index
idx_gl_accounts_currencysupports currency-segmented account filtering
Rollback:
DROP INDEX IF EXISTS idx_gl_accounts_currency;
ALTER TABLE gl_accounts DROP COLUMN IF EXISTS currency;
6. Entity Relationship Summary
The following diagram describes the primary relationships between GL Engine tables:
gl_accounts (self-referential via parent_id)
|
+--< gl_journal_lines.account_id
|
+--< gl_account_balances.account_id
gl_fiscal_periods
|
+--< gl_journal_entries.period_id
|
+--< gl_account_balances.period_id
gl_journal_entries (self-referential via reverses_id / reversed_by_id)
|
+--< gl_journal_lines.journal_entry_id
gl_sped_exports
|
+--< gl_audit_log.sped_export_id (partial; nullable)
Key relationship properties:
| Relationship | Cardinality | Cascade Behavior |
|---|---|---|
gl_accounts -> gl_accounts (parent) | Many-to-one (tree) | No cascade; parent deletion requires child reassignment |
gl_fiscal_periods -> gl_journal_entries | One-to-many | No cascade; period deletion requires entry removal first |
gl_journal_entries -> gl_journal_lines | One-to-many | No cascade; entry deletion requires line removal first |
gl_accounts -> gl_journal_lines | One-to-many | No cascade |
gl_accounts -> gl_account_balances | One-to-many (by period) | No cascade |
gl_fiscal_periods -> gl_account_balances | One-to-many (by account) | No cascade |
gl_journal_entries -> gl_journal_entries (reversal) | One-to-one (optional) | No cascade |
Note: No ON DELETE CASCADE rules are defined. The GL Engine enforces referential integrity conservatively; deletion of parent records requires explicit cleanup or soft-deletion via status transitions. Hard deletes on financial records are not a supported operation.
7. Index Inventory
Complete listing of all indexes in the GL Engine schema after all three migrations are applied.
| Index Name | Table | Columns | Type | Predicate | Added By |
|---|---|---|---|---|---|
idx_gl_accounts_tenant | gl_accounts | (tenant_id) | B-tree | -- | 001 |
idx_gl_accounts_parent | gl_accounts | (tenant_id, parent_id) | B-tree | -- | 001 |
idx_gl_accounts_type | gl_accounts | (tenant_id, account_type) | B-tree | -- | 001 |
idx_gl_accounts_sped | gl_accounts | (tenant_id, sped_natura) | B-tree | -- | 001 |
idx_gl_accounts_status | gl_accounts | (tenant_id, status) | B-tree | -- | 001 |
idx_gl_accounts_currency | gl_accounts | (tenant_id, currency) | B-tree | -- | 003 |
idx_gl_periods_tenant | gl_fiscal_periods | (tenant_id) | B-tree | -- | 001 |
idx_gl_periods_state | gl_fiscal_periods | (tenant_id, state) | B-tree | -- | 001 |
idx_gl_periods_dates | gl_fiscal_periods | (tenant_id, start_date, end_date) | B-tree | -- | 001 |
idx_gl_entries_tenant | gl_journal_entries | (tenant_id) | B-tree | -- | 001 |
idx_gl_entries_status | gl_journal_entries | (tenant_id, status) | B-tree | -- | 001 |
idx_gl_entries_period | gl_journal_entries | (tenant_id, period_id) | B-tree | -- | 001 |
idx_gl_entries_date | gl_journal_entries | (tenant_id, entry_date) | B-tree | -- | 001 |
idx_gl_entries_source | gl_journal_entries | (tenant_id, module_source) | B-tree | -- | 001 |
idx_gl_entries_reversal | gl_journal_entries | (reverses_id) | B-tree | WHERE reverses_id IS NOT NULL | 001 |
idx_gl_lines_entry | gl_journal_lines | (journal_entry_id) | B-tree | -- | 001 |
idx_gl_lines_account | gl_journal_lines | (tenant_id, account_id) | B-tree | -- | 001 |
idx_gl_lines_tenant | gl_journal_lines | (tenant_id) | B-tree | -- | 001 |
idx_gl_balances_tenant | gl_account_balances | (tenant_id) | B-tree | -- | 001 |
idx_gl_balances_account | gl_account_balances | (tenant_id, account_id) | B-tree | -- | 001 |
idx_gl_balances_period | gl_account_balances | (tenant_id, period_id) | B-tree | -- | 001 |
idx_gl_rates_tenant | gl_exchange_rates | (tenant_id) | B-tree | -- | 001 |
idx_gl_rates_pair | gl_exchange_rates | (tenant_id, from_currency, to_currency, effective_date) | B-tree | -- | 001 |
idx_gl_audit_tenant | gl_audit_log | (tenant_id, created_at) | B-tree | -- | 001 |
idx_gl_audit_record | gl_audit_log | (table_name, record_id, created_at) | B-tree | -- | 001 |
idx_gl_audit_sped | gl_audit_log | (sped_export_id) | B-tree | WHERE sped_export_id IS NOT NULL | 001 |
idx_gl_sped_tenant | gl_sped_exports | (tenant_id) | B-tree | -- | 001 |
Total indexes: 27 (26 from migration 001, 1 from migration 003)
8. Design Notes and Conventions
Naming Conventions
All schema objects follow a consistent prefix and naming convention:
| Object Type | Prefix | Example |
|---|---|---|
| Tables | gl_ | gl_journal_entries |
| Enum types | gl_ | gl_account_type |
| Primary keys | {table}_pkey | gl_accounts_pkey |
| Foreign keys | {table}_{column}_fkey | gl_jl_account_id_fkey |
| Unique constraints | uq_{descriptive_name} | uq_account_number_tenant |
| Check constraints | ck_{descriptive_name} | ck_one_side_only |
| Indexes | idx_{table}_{columns} | idx_gl_lines_account |
| Partitions | {parent}_{year}_{h1|h2} | gl_audit_log_2026_h1 |
Primary Key Strategy
All tables use UUID primary keys generated by uuid_generate_v4(). This choice:
- Eliminates sequential ID enumeration attacks
- Supports distributed ID generation without coordination
- Enables safe data migration and merge between tenant environments
- Is consistent with the application layer's UUID-first identity model
Monetary Precision
All monetary columns use NUMERIC(20,2):
- 20 total digits accommodates amounts up to 999,999,999,999,999,999.99 (quintillions) -- sufficient for any foreseeable financial context
- 2 decimal places is standard for BRL and most G20 currencies
- Exchange rates use
NUMERIC(18,8)to preserve 8 decimal places of precision for currency conversions
Tenant Isolation Pattern
Every table carries tenant_id UUID NOT NULL. This column is:
- Never given a default value (explicit assignment required)
- Included as the leading column in all non-trivial indexes to ensure index use under Row-Level Security policies
- The basis for all RLS policies (documented in CFS-DB-003)
- Verified at the application layer before any database operation
Audit Column Pattern
All mutable tables include the following audit columns:
| Column | Type | Description |
|---|---|---|
created_at | TIMESTAMPTZ NOT NULL DEFAULT NOW() | Set at insert time; never updated |
created_by | UUID NOT NULL | Authenticated user UUID at creation time |
updated_at | TIMESTAMPTZ NOT NULL DEFAULT NOW() | Updated by trigger on every modification |
updated_by | UUID NOT NULL | Set to authenticated user UUID on every modification |
The gl_journal_lines table intentionally omits updated_at/updated_by because lines are immutable after the parent entry is posted.
The gl_audit_log table omits all update audit columns because it is an append-only table.
JSONB Metadata Pattern
All tables include a metadata JSONB DEFAULT '{}' column. This column:
- Provides a structured escape hatch for integration-specific attributes without requiring schema changes
- Is indexed by applications using expression indexes where specific JSONB paths are queried frequently
- Must not be used to store information that belongs in a typed column; it is an extension point, not a substitute for schema design
SPED Compliance Design
Brazilian SPED compliance requirements influenced several schema decisions:
gl_sped_naturaenum encodes the six account nature classifications required by ECDsped_cod_aglandsped_referencial_codeongl_accountsmap to ECD register fields directlygl_sped_exportsprovides a full audit trail of every export job, including file hash verificationgl_audit_log.sped_export_idcreates a bidirectional link between audit entries and the exports that reference themgl_fiscal_periods.is_adjustmentsupports Brazilian fiscal year conventions that include adjustment periods beyond the standard 12 months
9. Related Documents
| Document Code | Title | Relationship |
|---|---|---|
| CFS-DB-001 | GL Engine Architecture Overview | Parent document; system-level context for this schema |
| CFS-DB-003 | Security and Multi-Tenancy Model | Describes RLS policies applied to the tables documented here |
| CFS-DB-004 | Business Logic Layer -- Triggers and Stored Functions | Documents triggers that enforce double-entry, balance maintenance, and audit trail on this schema |
| CFS-DB-005 | Migration Roadmap | Forward-looking schema evolution plan; next migrations pending |
Source of Truth for Schema: The migration files at GL-PROTOTYPE/src/schema/migrations/ are the authoritative definition. This document reflects their state as of 2026-02-20 (migrations 001 through 003 applied).
Copyright 2026 AZ1.AI Inc. All rights reserved. INTERNAL CONFIDENTIAL -- Authorized Personnel Only.
Document Code: CFS-DB-002 | Version: 1.0.0 | Last Updated: 2026-02-20 Owner: Hal Casteel, CEO/CTO, AZ1.AI Inc.