Skip to main content

Migration Roadmap & Target Schema

INTERNAL CONFIDENTIAL — AZ1.AI Inc. — Internal Use Only This document contains proprietary and confidential information about database schema design, migration strategy, and gap analysis for the CODITECT Financial Suite GL Engine. Unauthorized distribution, reproduction, or disclosure is strictly prohibited.

CFS-DB-005: Migration Roadmap & Target Schema

Document Code: CFS-DB-005 Owner: Hal Casteel, CEO/CTO, AZ1.AI Inc. Classification: INTERNAL CONFIDENTIAL Last Updated: 2026-02-20 Database Target: PostgreSQL 16+ Engine: GL Engine (General Ledger)

Table of Contents​

  1. Current State Assessment
  2. Gap Analysis Summary
  3. Four-Phase Migration Plan
  4. Target Schema — Post-Phase 1
  5. Backward Compatibility Guarantees
  6. Risk Assessment & Mitigation
  7. Validation & Rollback Procedures
  8. Related Documents

1. Current State Assessment​

1.1 Overview​

The GL Engine prototype implements approximately 70% of the schema required for production multi-national operation across 30+ jurisdictions. The current implementation is sufficient for single-entity, single-currency Brazilian accounting (SPED/ECD/ECF compliance) but lacks the structural foundations for multi-entity hierarchies, three-slot currency representation, and cross-jurisdictional regulatory metadata required for commercial deployment to international customers.

1.2 Applied Migrations​

MigrationFileDescriptionStatus
001001_core_schema.sqlFoundation: all tables, enums, indexes, partitioned audit logApplied
002002_account_name_translations.sqlTri-lingual account names (PT source, EN, ES translations)Applied
003003_account_currency.sqlCurrency column on gl_accounts, defaults to BRLApplied

1.3 Current Schema Inventory​

Tables (8):

TableRows (Seed)Purpose
gl_accounts~150Chart of Accounts with SPED mapping
gl_fiscal_periods~12Period management, open/closed/locked states
gl_journal_entries~50Journal entry headers with reversal linkage
gl_journal_lines~200Journal detail lines, 2-slot currency
gl_account_balances~150Materialized balance cache by period
gl_exchange_rates~30FX rates (SPOT, AVERAGE, CLOSING)
gl_audit_log~500Immutable audit trail, range-partitioned by date
gl_sped_exports~5SPED ECD/ECF/EFD export tracking

Enum Types (10):

gl_account_type, gl_normal_balance, gl_account_status, gl_period_state, gl_entry_status, gl_module_source, gl_rate_type, gl_rate_source, gl_audit_action, gl_sped_natura

Extensions Enabled: uuid-ossp, pgcrypto

1.4 Gap Count by Category​

CategoryGaps IdentifiedBlocking Production
Critical Structural3Yes
Operational7Partial
Data Model Enhancements10No (performance/scale)
Total20—

Gaps were identified by cross-referencing the current schema against the following standards and platforms:

  • IAS 21 — The Effects of Changes in Foreign Exchange Rates
  • ASC 830 — Foreign Currency Matters (US GAAP)
  • CPC 02 R2 — Brazilian equivalent of IAS 21 (multi-currency)
  • GoBD — German Principles for Proper Bookkeeping (document date requirements)
  • FEC — Fichier des Ecritures Comptables (French regulatory export)
  • SAP S/4HANA — Universal Journal (ACDOCA) architecture
  • Oracle Cloud Financials — Subledger Accounting model
  • Microsoft Dynamics 365 Finance — Ledger and accounting model
  • NetSuite OneWorld — Multi-subsidiary/multi-currency model
  • Workday Financial Management — Worktags and ledger account model

2. Gap Analysis Summary​

2.1 Critical Structural Gaps (P0/P1)​

These gaps must be resolved before the GL Engine can be offered to any customer operating outside a single-entity, single-currency Brazilian context.

#GapImpactPriority
1No entity conceptCannot model multi-entity organizations (parent companies with subsidiaries). The current tenant_id alone is insufficient for consolidation, intercompany transactions, and entity-specific Charts of Accounts. Directly blocks: inter-entity elimination, entity-specific fiscal years, and segment reportingP0 — Blocks consolidation
2Only 2 currency slots on journal linesCurrent schema carries original_currency + debit/credit in functional currency only. IAS 21, ASC 830, and CPC 02 R2 all require three slots: transaction currency, functional currency, and reporting currency. Absence of reporting currency prevents USD-denominated financial statements for non-USD functional currency entitiesP0 — Blocks multi-currency reporting
3No document_date distinct from entry_dateMany jurisdictions require both an economic date (when the transaction occurred) and a posting date (when it was recorded in the system). EU VAT reporting, Brazilian SPED fiscal, and GoBD all require document_date to be separately captured and immutable after postingP1 — Blocks EU and advanced LATAM compliance

2.2 Operational Gaps (P1/P2)​

These gaps are not immediately blocking but create manual workarounds that cannot scale to commercial operations.

#GapCurrent StateRequired State
4No revaluation trackingAd-hoc via application service layer; no audit trailgl_revaluation_runs table with full audit trail, reversal linkage, and approval state
5No consolidation schemaNot implemented; planned in architecture docsgl_consolidation_runs, gl_elimination_rules, elimination journal entries
6No CoA template systemSingle hardcoded Brazilian seed datagl_coa_templates per industry vertical and per jurisdiction; parameterized instantiation
7No RBAC tablesSingle gl_app database role; no row-level user permissionsgl_user_roles, gl_permissions with entity-level access control integrated with RLS
8No closing entries tablePeriod close is implicit via gl_period_state status changegl_period_close_checklist for month-end automation with item-level completion tracking
9No dimension master tablesdimension_1 through dimension_5 are free-text columns; no validation or hierarchygl_cost_centers, gl_projects with hierarchical structure, validity dates, and budget linkage
10No bank reconciliationNot implementedgl_bank_statements, gl_bank_matches for automated and manual reconciliation workflows

2.3 Data Model Enhancement Gaps (P2/P3)​

These gaps represent architectural debt that will affect scalability, compliance, and integration quality as the platform grows.

#GapDescription
11Missing account group/class levelMost jurisdictions (and all five ERP platforms surveyed) require an account group level between account_type and individual accounts. SAP uses account groups; Oracle uses natural account segment hierarchies; NetSuite uses account classes
12Rigid fiscal year assumptionfiscal_year INTEGER on gl_fiscal_periods assumes a calendar year aligned to a single entity. Entities with non-calendar fiscal years (e.g., April–March UK fiscal year, July–June Australian fiscal year) cannot be modeled correctly
13No tax jurisdiction metadata on accountsgl_accounts lacks tax codes, VAT rate references, and withholding tax rules. This metadata is required for automated VAT computation, EC Sales Lists (EU), and SPED fiscal line classification
14No intercompany partner tracking on linesgl_journal_lines has no partner_entity_id column. Intercompany transaction matching and automated elimination require knowing the counterpart entity for every intercompany line
15No document attachment referencesNo foreign key or reference linking journal entries or lines to source documents (invoices, bank statements, receipts). Required for e-invoice mandates and document management integration
16Hot table partitioning not implementedgl_journal_lines and gl_account_balances will be the hottest tables in a multi-tenant deployment. Hash partitioning by tenant_id is required at scale; range partitioning by fiscal period is defined in architecture (CFS-004 §6.3) but not yet applied
17No regulatory metadata on periodsgl_fiscal_periods lacks submission_deadline, regulatory_reference, and submitted_at columns. These are required to track filing obligations (SPED, FEC, GoBD GdPdU exports) per period
18No exchange rate history versioningRate corrections currently overwrite existing records due to the UNIQUE constraint on (tenant_id, from_currency, to_currency, rate_type, effective_date). Rate history should be versioned: corrections create new records with supersession linkage
19No approval workflow tablesMulti-level approval for journal entries above configurable monetary thresholds. Required for SOX compliance, four-eyes principle enforcement, and enterprise customer procurement requirements
20No segment reporting hierarchydimension_1 through dimension_5 free-text values provide no hierarchy for segment roll-up. IFRS 8 Operating Segments and ASC 280 require structured, hierarchical segment reporting

3. Four-Phase Migration Plan​

Phasing Rationale​

The migration plan is sequenced to deliver the maximum compliance and commercial unlock at each phase boundary. Phase 1 resolves all P0 structural gaps and is a prerequisite for every subsequent phase. Phases 2 through 4 are sequenced to minimize inter-migration dependencies and to align with the development roadmap defined in CFS-009.

Phase Overview​

PhaseScopeMigrationsEstimated EffortPriority
1Entity Hierarchy & Currency Enhancement004–0072–3 daysP0
2Consolidation & Access Control008–0122 weeksP1
3Templates & Regulatory Metadata013–0161 weekP2
4Performance & Advanced Features017–0201 weekP2/P3

Phase 1: Entity Hierarchy & Currency Enhancement​

Migrations: 004–007 Estimated Effort: 2–3 engineering days Priority: P0 — Required before any multi-entity or multi-currency customer onboarding Compliance Unlocked: IAS 21, ASC 830, CPC 02 R2, GoBD (partial), FEC (partial)

Migration 004: gl_entities Table​

Creates the entity concept that underpins all multi-entity, multi-currency, and consolidation functionality. Adds entity_id foreign key references to gl_accounts, gl_journal_entries, and gl_fiscal_periods.

-- ============================================================================
-- GL Engine — Migration 004
-- Add entity hierarchy (multi-entity foundation)
-- Target: PostgreSQL 16+
-- Compliance: IAS 21, ASC 830, CPC 02 R2
-- ============================================================================

-- ============================================================================
-- New enum: entity type classification
-- ============================================================================

CREATE TYPE gl_entity_type AS ENUM (
  'HOLDING',
  'SUBSIDIARY',
  'BRANCH',
  'DIVISION',
  'JOINT_VENTURE'
);

-- ============================================================================
-- TABLE: gl_entities — Legal Entity / Organizational Unit Registry
-- ============================================================================

CREATE TABLE gl_entities (
  id                    UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id             UUID NOT NULL,
  parent_entity_id      UUID REFERENCES gl_entities(id) ON DELETE RESTRICT,

  entity_code           TEXT NOT NULL,
  entity_name           TEXT NOT NULL,
  entity_type           gl_entity_type NOT NULL,

  -- Geography & currency
  country_code          CHAR(2) NOT NULL,
  functional_currency   CHAR(3) NOT NULL,
  reporting_currency    CHAR(3),

  -- Fiscal year configuration
  -- fiscal_year_start_month: 1 = January, 4 = April (UK), 7 = July (AU)
  fiscal_year_start_month INTEGER NOT NULL DEFAULT 1
    CONSTRAINT ck_entity_fy_month CHECK (
      fiscal_year_start_month >= 1 AND fiscal_year_start_month <= 12
    ),

  -- Regulatory identifiers
  tax_id                TEXT,
  vat_registration_id   TEXT,

  -- Operational state
  status                TEXT NOT NULL DEFAULT 'ACTIVE'
    CONSTRAINT ck_entity_status CHECK (status IN ('ACTIVE', 'INACTIVE', 'LIQUIDATED')),

  -- Extensible metadata (ERP codes, integration IDs, etc.)
  metadata              JSONB NOT NULL DEFAULT '{}',

  created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_by            UUID NOT NULL,
  updated_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_by            UUID NOT NULL,

  CONSTRAINT uq_entity_code_tenant UNIQUE (tenant_id, entity_code),
  CONSTRAINT ck_entity_name_length CHECK (char_length(entity_name) <= 200),
  CONSTRAINT ck_entity_code_length CHECK (char_length(entity_code) <= 20),
  CONSTRAINT ck_entity_country_code CHECK (char_length(country_code) = 2),
  CONSTRAINT ck_functional_currency CHECK (char_length(functional_currency) = 3),
  CONSTRAINT ck_reporting_currency CHECK (
    reporting_currency IS NULL OR char_length(reporting_currency) = 3
  ),
  CONSTRAINT ck_no_self_parent CHECK (id != parent_entity_id)
);

CREATE INDEX idx_gl_entities_tenant
  ON gl_entities(tenant_id);
CREATE INDEX idx_gl_entities_parent
  ON gl_entities(tenant_id, parent_entity_id);
CREATE INDEX idx_gl_entities_status
  ON gl_entities(tenant_id, status);
CREATE INDEX idx_gl_entities_country
  ON gl_entities(tenant_id, country_code);

-- ============================================================================
-- Add entity_id to gl_accounts
-- Nullable initially: existing accounts remain valid for single-entity tenants
-- ============================================================================

ALTER TABLE gl_accounts
  ADD COLUMN entity_id UUID REFERENCES gl_entities(id) ON DELETE RESTRICT;

-- Drop existing unique constraint, recreate scoped to entity
ALTER TABLE gl_accounts
  DROP CONSTRAINT uq_account_number_tenant;

ALTER TABLE gl_accounts
  ADD CONSTRAINT uq_account_number_tenant_entity
    UNIQUE (tenant_id, entity_id, account_number);

CREATE INDEX idx_gl_accounts_entity
  ON gl_accounts(tenant_id, entity_id);

-- ============================================================================
-- Add entity_id to gl_journal_entries
-- ============================================================================

ALTER TABLE gl_journal_entries
  ADD COLUMN entity_id UUID REFERENCES gl_entities(id) ON DELETE RESTRICT;

CREATE INDEX idx_gl_entries_entity
  ON gl_journal_entries(tenant_id, entity_id);

-- ============================================================================
-- Add entity_id to gl_fiscal_periods
-- Nullable: global periods (tenant-level) remain representable as entity_id IS NULL
-- ============================================================================

ALTER TABLE gl_fiscal_periods
  ADD COLUMN entity_id UUID REFERENCES gl_entities(id) ON DELETE RESTRICT;

-- Drop existing unique constraint, recreate scoped to entity (NULL treated as global)
ALTER TABLE gl_fiscal_periods
  DROP CONSTRAINT uq_period_tenant_year_num;

ALTER TABLE gl_fiscal_periods
  ADD CONSTRAINT uq_period_tenant_entity_year_num
    UNIQUE (tenant_id, entity_id, fiscal_year, period_number);

CREATE INDEX idx_gl_periods_entity
  ON gl_fiscal_periods(tenant_id, entity_id);

-- ============================================================================
-- Comments for documentation generation
-- ============================================================================

COMMENT ON TABLE gl_entities IS
  'Legal entity and organizational unit registry. Supports multi-level hierarchies via parent_entity_id. Each entity has an independent functional currency and may optionally define a reporting currency for group consolidation. IAS 21 / ASC 830 compliant.';

COMMENT ON COLUMN gl_entities.functional_currency IS
  'ISO 4217 three-letter code. The currency in which the entity maintains its primary books of account (IAS 21.8).';

COMMENT ON COLUMN gl_entities.reporting_currency IS
  'ISO 4217 three-letter code. The currency used for group consolidation financial statements. NULL for entities where functional = reporting.';

COMMENT ON COLUMN gl_entities.fiscal_year_start_month IS
  '1 = January start (calendar year). 4 = April start (UK Government, many UK companies). 7 = July start (Australia federal government). Affects period generation logic.';

Migration 005: Three-Slot Currency on Journal Lines​

Extends gl_journal_lines from a 2-slot currency model (original + functional) to the 3-slot model (transaction + functional + reporting) required by IAS 21, ASC 830, and all major ERP platforms. Column renames are performed to align terminology with the architectural specification in CFS-004 §6.2.

-- ============================================================================
-- GL Engine — Migration 005
-- 3-slot currency model on gl_journal_lines
-- Target: PostgreSQL 16+
-- Compliance: IAS 21 §23-25, ASC 830-10-45, CPC 02 R2
-- Ref: CFS-004 §6.2 — Core GL Schema
-- ============================================================================

-- Step 1: Rename existing columns to precise terminology
-- original_currency  -> transaction_currency  (the currency of the source document)
-- original_amount    -> transaction_amount     (amount in transaction currency)
-- exchange_rate      -> transaction_rate       (rate: transaction -> functional)
-- debit_amount       -> functional_debit       (functional currency debit)
-- credit_amount      -> functional_credit      (functional currency credit)

ALTER TABLE gl_journal_lines
  RENAME COLUMN original_currency TO transaction_currency;

ALTER TABLE gl_journal_lines
  RENAME COLUMN original_amount TO transaction_amount;

ALTER TABLE gl_journal_lines
  RENAME COLUMN exchange_rate TO transaction_rate;

ALTER TABLE gl_journal_lines
  RENAME COLUMN debit_amount TO functional_debit;

ALTER TABLE gl_journal_lines
  RENAME COLUMN credit_amount TO functional_credit;

-- Step 2: Add functional_currency identifier column
-- Previously implicit (the tenant's or entity's home currency)
-- Now explicit: required for IAS 21 disclosure and revaluation audit trail

ALTER TABLE gl_journal_lines
  ADD COLUMN functional_currency CHAR(3)
    CONSTRAINT ck_line_functional_currency CHECK (
      functional_currency IS NULL OR char_length(functional_currency) = 3
    );

-- Step 3: Add reporting currency slot (third slot)
-- reporting_currency: ISO 4217 code (group currency, e.g., USD for US-listed parent)
-- reporting_rate:     rate applied to convert functional -> reporting
-- reporting_debit:    amount in reporting currency (debit side)
-- reporting_credit:   amount in reporting currency (credit side)

ALTER TABLE gl_journal_lines
  ADD COLUMN reporting_currency  CHAR(3)
    CONSTRAINT ck_line_reporting_currency CHECK (
      reporting_currency IS NULL OR char_length(reporting_currency) = 3
    ),
  ADD COLUMN reporting_rate      NUMERIC(18,8)
    CONSTRAINT ck_line_reporting_rate CHECK (
      reporting_rate IS NULL OR reporting_rate > 0
    ),
  ADD COLUMN reporting_debit     NUMERIC(20,4) NOT NULL DEFAULT 0
    CONSTRAINT ck_line_reporting_debit_nn CHECK (reporting_debit >= 0),
  ADD COLUMN reporting_credit    NUMERIC(20,4) NOT NULL DEFAULT 0
    CONSTRAINT ck_line_reporting_credit_nn CHECK (reporting_credit >= 0);

-- Step 4: Update existing constraint name for consistency
-- Original constraint: ck_one_side_only references debit_amount/credit_amount (old names)
-- PostgreSQL check constraints reference column names at definition time —
-- the constraint remains valid after rename; no rebuild required.
-- However, we rename it for documentation clarity.

ALTER TABLE gl_journal_lines
  RENAME CONSTRAINT ck_one_side_only TO ck_line_one_side_only;

ALTER TABLE gl_journal_lines
  RENAME CONSTRAINT ck_positive_amounts TO ck_line_functional_amounts_positive;

ALTER TABLE gl_journal_lines
  RENAME CONSTRAINT ck_exchange_rate_positive TO ck_line_transaction_rate_positive;

-- Step 5: Add reporting currency balance constraint (mirrors functional constraint)
-- A reporting line must also credit or debit on one side only
ALTER TABLE gl_journal_lines
  ADD CONSTRAINT ck_line_reporting_one_side CHECK (
    reporting_debit = 0 OR reporting_credit = 0
  );

-- Step 6: Update index names to reflect renamed columns
DROP INDEX IF EXISTS idx_gl_lines_account;
CREATE INDEX idx_gl_lines_account
  ON gl_journal_lines(tenant_id, account_id);

-- Step 7: Add entity_id to journal lines (entity-level line attribution)
ALTER TABLE gl_journal_lines
  ADD COLUMN entity_id UUID REFERENCES gl_entities(id) ON DELETE RESTRICT;

CREATE INDEX idx_gl_lines_entity
  ON gl_journal_lines(tenant_id, entity_id);

-- Step 8: Comments
COMMENT ON COLUMN gl_journal_lines.transaction_currency IS
  'ISO 4217 code. The currency of the source document (invoice, bank statement). Slot 1 of 3 (IAS 21.20).';

COMMENT ON COLUMN gl_journal_lines.transaction_amount IS
  'Absolute amount in transaction currency. Sign is determined by functional_debit/functional_credit split.';

COMMENT ON COLUMN gl_journal_lines.transaction_rate IS
  'Exchange rate applied at transaction date: 1 unit of transaction_currency = transaction_rate units of functional_currency. IAS 21.21 spot rate or average rate for the period.';

COMMENT ON COLUMN gl_journal_lines.functional_currency IS
  'ISO 4217 code. The entity''s functional currency per IAS 21.8. Slot 2 of 3.';

COMMENT ON COLUMN gl_journal_lines.functional_debit IS
  'Debit amount in the entity''s functional currency.';

COMMENT ON COLUMN gl_journal_lines.functional_credit IS
  'Credit amount in the entity''s functional currency.';

COMMENT ON COLUMN gl_journal_lines.reporting_currency IS
  'ISO 4217 code. The group/consolidation presentation currency. NULL for entities where functional equals reporting. Slot 3 of 3 (IAS 21.38-39).';

COMMENT ON COLUMN gl_journal_lines.reporting_rate IS
  'Exchange rate applied to translate functional -> reporting currency. Typically the closing rate (IAS 21.39a) for balance sheet items or average rate for income statement items.';

Migration 006: Document Date and Document Number​

Adds document_date and document_number to gl_journal_entries. These fields are mandatory for GoBD compliance (Germany), required for EU VAT reporting, and expected by all major ERP integration adapters.

-- ============================================================================
-- GL Engine — Migration 006
-- Add document_date and document_number to gl_journal_entries
-- Target: PostgreSQL 16+
-- Compliance: GoBD §14 (Germany), EU VAT Directive Art. 226, Brazilian SPED I50
-- ============================================================================

ALTER TABLE gl_journal_entries
  ADD COLUMN document_date   DATE,
  ADD COLUMN document_number TEXT
    CONSTRAINT ck_entry_doc_number_length CHECK (
      document_number IS NULL OR char_length(document_number) <= 100
    );

-- Back-fill: set document_date = entry_date for all existing records.
-- This is safe because all existing records are from the prototype phase
-- where document_date and entry_date are the same economic event date.
UPDATE gl_journal_entries
  SET document_date = entry_date
  WHERE document_date IS NULL;

-- Index: document date range queries are common in VAT return preparation
-- and GoBD export generation
CREATE INDEX idx_gl_entries_document_date
  ON gl_journal_entries(tenant_id, document_date);

-- Partial index: entries with a document_number (source document linkage queries)
CREATE INDEX idx_gl_entries_document_number
  ON gl_journal_entries(tenant_id, document_number)
  WHERE document_number IS NOT NULL;

-- Comments
COMMENT ON COLUMN gl_journal_entries.document_date IS
  'The economic date of the underlying transaction — the date on the source document (invoice date, bank value date, contract date). Distinct from entry_date (posting date). Required by GoBD §14 (Germany), EU VAT Directive Art. 226(7), and Brazilian SPED I050 DT_DOC field. Must not be modified after status = POSTED.';

COMMENT ON COLUMN gl_journal_entries.document_number IS
  'The reference number from the source document (invoice number, bank statement line ID, contract reference). Supports traceability from the GL back to originating documents. Required for GoBD Verfahrensdokumentation and SPED document linkage.';

COMMENT ON COLUMN gl_journal_entries.entry_date IS
  'The posting date — the date the entry was recorded in the ledger. Used for period assignment. Distinct from document_date (economic date of transaction).';

Migration 007: Entity Hierarchy Closure Table​

Adds gl_entity_closure, a closure table that materializes all ancestor-descendant relationships in the entity tree. This enables O(1) descendant queries (essential for consolidation scope determination) without recursive CTEs at query time.

-- ============================================================================
-- GL Engine — Migration 007
-- Entity hierarchy closure table for O(1) ancestor/descendant queries
-- Target: PostgreSQL 16+
-- Pattern: Closure Table (Joe Celko, "Trees and Hierarchies in SQL")
-- Use: Consolidation scope, entity access control, roll-up reporting
-- ============================================================================

CREATE TABLE gl_entity_closure (
  ancestor_id    UUID NOT NULL REFERENCES gl_entities(id) ON DELETE CASCADE,
  descendant_id  UUID NOT NULL REFERENCES gl_entities(id) ON DELETE CASCADE,
  depth          INTEGER NOT NULL
    CONSTRAINT ck_closure_depth_nn CHECK (depth >= 0),

  PRIMARY KEY (ancestor_id, descendant_id)
);

CREATE INDEX idx_gl_entity_closure_descendant
  ON gl_entity_closure(descendant_id, ancestor_id, depth);

CREATE INDEX idx_gl_entity_closure_depth
  ON gl_entity_closure(ancestor_id, depth);

-- ============================================================================
-- Function: populate_entity_closure()
-- Maintains the closure table when entities are inserted or reparented.
-- Called by trigger on gl_entities INSERT and UPDATE (parent_entity_id).
-- ============================================================================

CREATE OR REPLACE FUNCTION populate_entity_closure()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    -- Self-reference (depth = 0)
    INSERT INTO gl_entity_closure (ancestor_id, descendant_id, depth)
    VALUES (NEW.id, NEW.id, 0);

    -- All ancestors of the parent become ancestors of the new entity
    IF NEW.parent_entity_id IS NOT NULL THEN
      INSERT INTO gl_entity_closure (ancestor_id, descendant_id, depth)
      SELECT ancestor_id, NEW.id, depth + 1
      FROM gl_entity_closure
      WHERE descendant_id = NEW.parent_entity_id;
    END IF;

  ELSIF TG_OP = 'UPDATE' AND OLD.parent_entity_id IS DISTINCT FROM NEW.parent_entity_id THEN
    -- Reparenting: remove all ancestor links that go through the old parent
    DELETE FROM gl_entity_closure
    WHERE descendant_id IN (
      SELECT descendant_id FROM gl_entity_closure WHERE ancestor_id = NEW.id
    )
    AND ancestor_id NOT IN (
      SELECT descendant_id FROM gl_entity_closure WHERE ancestor_id = NEW.id
    );

    -- Re-insert paths through the new parent
    IF NEW.parent_entity_id IS NOT NULL THEN
      INSERT INTO gl_entity_closure (ancestor_id, descendant_id, depth)
      SELECT p.ancestor_id, c.descendant_id, p.depth + c.depth + 1
      FROM gl_entity_closure p
      CROSS JOIN gl_entity_closure c
      WHERE p.descendant_id = NEW.parent_entity_id
        AND c.ancestor_id = NEW.id
      ON CONFLICT (ancestor_id, descendant_id) DO UPDATE
        SET depth = EXCLUDED.depth;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_entity_closure_maintain
  AFTER INSERT OR UPDATE OF parent_entity_id
  ON gl_entities
  FOR EACH ROW
  EXECUTE FUNCTION populate_entity_closure();

-- ============================================================================
-- Helper view: direct entity descendants (depth = 1, immediate children only)
-- ============================================================================

CREATE VIEW gl_entity_children AS
SELECT
  ec.ancestor_id   AS parent_entity_id,
  e.id             AS child_entity_id,
  e.entity_code,
  e.entity_name,
  e.entity_type,
  e.functional_currency,
  e.country_code
FROM gl_entity_closure ec
JOIN gl_entities e ON e.id = ec.descendant_id
WHERE ec.depth = 1;

-- ============================================================================
-- Helper function: get all entities in the consolidation scope of a root entity
-- Usage: SELECT * FROM gl_entity_consolidation_scope('<root_entity_id>');
-- ============================================================================

CREATE OR REPLACE FUNCTION gl_entity_consolidation_scope(p_root_entity_id UUID)
RETURNS TABLE (
  entity_id       UUID,
  entity_code     TEXT,
  entity_name     TEXT,
  entity_type     gl_entity_type,
  depth           INTEGER,
  functional_currency CHAR(3),
  reporting_currency  CHAR(3),
  country_code    CHAR(2)
)
LANGUAGE sql
STABLE
AS $$
  SELECT
    e.id,
    e.entity_code,
    e.entity_name,
    e.entity_type,
    ec.depth,
    e.functional_currency,
    e.reporting_currency,
    e.country_code
  FROM gl_entity_closure ec
  JOIN gl_entities e ON e.id = ec.descendant_id
  WHERE ec.ancestor_id = p_root_entity_id
    AND e.status = 'ACTIVE'
  ORDER BY ec.depth, e.entity_code;
$$;

COMMENT ON TABLE gl_entity_closure IS
  'Closure table materializing all ancestor-descendant relationships in the entity hierarchy. Enables O(1) consolidation scope queries and entity access control without recursive CTEs. Maintained automatically by trg_entity_closure_maintain.';

COMMENT ON FUNCTION gl_entity_consolidation_scope(UUID) IS
  'Returns all active entities within the consolidation scope of the given root entity, ordered by hierarchy depth. Used by the consolidation engine to determine which entities contribute to a group consolidation run.';

Phase 2: Consolidation & Access Control​

Migrations: 008–012 Estimated Effort: 2 weeks Priority: P1 — Required for multi-entity consolidated financial statements and enterprise RBAC

MigrationDescription
008gl_revaluation_runs — FX revaluation run tracking with audit trail and reversal linkage
009gl_consolidation_runs — Consolidation execution records with scope, method, and status
010gl_elimination_rules — Intercompany elimination rule engine (account-to-account mappings)
011gl_user_roles, gl_permissions — Application-level RBAC integrated with PostgreSQL RLS
012Entity-level RLS policy extension — Scope existing tenant-level RLS policies to also enforce entity-level access based on gl_user_roles.entity_ids

Key design decisions for Phase 2:

  • gl_revaluation_runs will link to the reversal journal entry it generates, enabling one-click unrealization if a revaluation needs to be voided.
  • gl_elimination_rules will support three elimination types: intercompany receivable/payable netting, intercompany revenue/expense elimination, and intercompany profit-in-inventory elimination.
  • RBAC will be implemented at the database layer via RLS policies that read from gl_user_roles, not solely at the application layer, providing defense in depth consistent with the principle defined in CFS-003.

Phase 3: Templates & Regulatory Metadata​

Migrations: 013–016 Estimated Effort: 1 week Priority: P2 — Required for multi-jurisdiction onboarding automation and regulatory filing workflows

MigrationDescription
013gl_coa_templates — Industry-specific and jurisdiction-specific Chart of Accounts templates with parameterized instantiation
014Tax jurisdiction metadata on gl_accounts — Tax code, VAT rate reference, withholding rule columns
015Regulatory metadata on gl_fiscal_periods — submission_deadline, regulatory_reference, submitted_at, filing_method
016Document attachment references on gl_journal_entries and gl_journal_lines — External document store linkage (path, MIME type, hash)

Key design decisions for Phase 3:

  • gl_coa_templates will use a seeded population approach: templates are defined once per jurisdiction/industry combination and instantiated per entity at onboarding. This replaces the current single-file Brazilian seed approach.
  • Tax metadata on accounts will be stored as JSONB to accommodate jurisdiction-specific structures (EU VAT codes differ structurally from Brazilian ICMS/PIS/COFINS rules and US sales tax nexus requirements).

Phase 4: Performance & Advanced Features​

Migrations: 017–020 Estimated Effort: 1 week Priority: P2/P3 — Scalability and advanced compliance features

MigrationDescription
017Hash partitioning on gl_journal_lines by tenant_id — Distribute I/O across shards for multi-tenant write throughput
018Range partitioning on gl_account_balances by fiscal_year — Align with CFS-004 §6.3 partitioning strategy
019gl_approval_workflows, gl_approval_steps — Multi-level approval for journal entries above configurable thresholds (SOX, four-eyes principle)
020gl_segment_hierarchies, gl_segment_nodes — Hierarchical dimension master for IFRS 8 / ASC 280 segment reporting

Note on Migration 017: Converting gl_journal_lines from an unpartitioned table to a hash-partitioned table requires a table replacement procedure (not a simple ALTER TABLE). This migration will be implemented as a blue-green table swap:

  1. Create gl_journal_lines_partitioned as the new hash-partitioned table.
  2. Copy all rows from gl_journal_lines using INSERT INTO ... SELECT.
  3. Rename tables atomically within a transaction: gl_journal_lines -> gl_journal_lines_legacy, gl_journal_lines_partitioned -> gl_journal_lines.
  4. Update all foreign key references.
  5. Retain gl_journal_lines_legacy for one release cycle before dropping.

4. Target Schema — Post-Phase 1​

After applying migrations 004 through 007, the complete GL Engine schema will consist of the following tables:

4.1 New Tables (Phase 1)​

TableMigrationPurpose
gl_entities004Legal entity and organizational unit registry
gl_entity_closure007Materialized ancestor-descendant hierarchy for O(1) consolidation queries

4.2 Modified Tables (Phase 1)​

TableChangesMigration
gl_accounts+entity_id (FK to gl_entities), unique constraint scoped to entity004
gl_fiscal_periods+entity_id (FK to gl_entities), unique constraint scoped to entity004
gl_journal_entries+entity_id, +document_date, +document_number004, 006
gl_journal_linesColumn renames (2-slot -> 3-slot), +functional_currency, +reporting_currency, +reporting_rate, +reporting_debit, +reporting_credit, +entity_id005

4.3 Unmodified Tables (Phase 1)​

TableNotes
gl_account_balancesUnmodified. Balance currency column already present. Entity-scoping deferred to Phase 2.
gl_exchange_ratesUnmodified. Rate history versioning deferred to Phase 3.
gl_audit_logUnmodified. Range-partitioned by date. Trigger updates will capture new columns automatically.
gl_sped_exportsUnmodified. SPED is Brazil-specific; entity scoping deferred to Phase 2.

4.4 Complete Post-Phase-1 Table Inventory​

gl_entities               (NEW — 004)
gl_entity_closure         (NEW — 007)
gl_accounts               (MODIFIED — 004)
gl_fiscal_periods         (MODIFIED — 004)
gl_journal_entries        (MODIFIED — 004, 006)
gl_journal_lines          (MODIFIED — 005)
gl_account_balances       (UNMODIFIED)
gl_exchange_rates         (UNMODIFIED)
gl_audit_log              (UNMODIFIED — range-partitioned)
gl_sped_exports           (UNMODIFIED)

4.5 Complete Post-Phase-1 Enum Inventory​

gl_account_type           (existing)
gl_normal_balance         (existing)
gl_account_status         (existing)
gl_period_state           (existing)
gl_entry_status           (existing)
gl_module_source          (existing)
gl_rate_type              (existing)
gl_rate_source            (existing)
gl_audit_action           (existing)
gl_sped_natura            (existing)
gl_entity_type            (NEW — 004)

4.6 Post-Phase-1 Key Relationship Diagram​

tenants (external)
  |
  +-- gl_entities (tenant_id)
        |
        +-- gl_entities (parent_entity_id, self-referential)
        |     |
        |     +-- gl_entity_closure (ancestor_id, descendant_id)
        |
        +-- gl_accounts (entity_id) --------+-- gl_account_balances
        |                                   |
        +-- gl_fiscal_periods (entity_id) --+-- gl_account_balances
        |
        +-- gl_journal_entries (entity_id)
              |
              +-- gl_journal_lines (journal_entry_id)
                    |
                    +-- gl_accounts (account_id)

gl_exchange_rates (tenant_id, standalone)
gl_audit_log (tenant_id, standalone — partitioned)
gl_sped_exports (tenant_id, standalone)

5. Backward Compatibility Guarantees​

5.1 Additive-Only Principle​

All Phase 1 migrations are strictly additive. No existing columns are dropped. No existing constraints are tightened in a manner that would reject previously valid data. The following specific guarantees apply:

Change TypeGuarantee
New columnsAll new columns are either nullable or carry DEFAULT values that are valid for existing rows
Column renamesApplied via ALTER TABLE ... RENAME COLUMN. All application code referencing old column names must be updated before deploying the application layer against Migration 005
New tablesAdditive only; no application code path is required to write to new tables until entity functionality is explicitly activated
Constraint changesUnique constraint on gl_accounts is reconstructed to include entity_id; existing rows where entity_id IS NULL satisfy the constraint because NULL != NULL in SQL unique constraint evaluation
New enum typegl_entity_type is additive; no existing column type is changed

5.2 Column Rename Impact on Application Layer​

Migration 005 introduces column renames on gl_journal_lines. These are the only changes in Phase 1 that require coordinated application code updates:

Old Column NameNew Column NameApplication Files Affected
original_currencytransaction_currencysrc/services/, src/types/, src/ai/
original_amounttransaction_amountsrc/services/, src/types/
exchange_ratetransaction_ratesrc/services/, src/types/
debit_amountfunctional_debitsrc/services/, src/types/, src/sped/
credit_amountfunctional_creditsrc/services/, src/types/, src/sped/

Deployment sequence for Migration 005:

  1. Apply migration on a non-production database copy.
  2. Update all application layer references to use new column names.
  3. Run full test suite against the updated schema.
  4. Deploy database migration to production (zero-downtime: renames are metadata-only in PostgreSQL).
  5. Deploy updated application layer.

PostgreSQL column renames are metadata operations that do not rewrite table data and complete near-instantaneously even on large tables.

5.3 Single-Entity Tenant Compatibility​

Tenants that remain single-entity (the current prototype use case) continue to operate without modification. The entity_id column on gl_accounts, gl_journal_entries, gl_fiscal_periods, and gl_journal_lines is nullable. Existing application code paths that do not set entity_id will continue to function and all existing data will remain valid.

6. Risk Assessment & Mitigation​

RiskLikelihoodImpactMitigation
Column rename breaks existing queriesMediumHighApplication code audit before migration; feature flag to run both old and new column names during transition via view
Closure table trigger causes performance regression on bulk entity insertsLowMediumTrigger only fires on gl_entities INSERT/UPDATE, not on journal lines. Benchmark with 1,000 entity inserts before production deploy
entity_id nullable constraint allows inconsistent dataMediumMediumApplication-layer validation enforces entity_id for all new records; existing NULL records are treated as global/unscoped
Unique constraint change on gl_accounts rejects existing dataLowHighTest on a copy of production data before applying; NULL != NULL in PostgreSQL unique index evaluation protects existing rows
Migration 007 trigger fails on circular entity referenceLowHighck_no_self_parent constraint on gl_entities prevents direct self-reference; trigger does not protect against multi-hop cycles — application layer must validate entity tree acyclicity before INSERT

7. Validation & Rollback Procedures​

7.1 Pre-Migration Validation Checks​

Execute the following checks immediately before applying each migration in production:

-- Check 1: Verify no duplicate (tenant_id, entity_id, account_number)
-- combinations exist that would violate the new constraint in Migration 004
-- (expected result: 0 rows)
SELECT tenant_id, account_number, COUNT(*) AS cnt
FROM gl_accounts
GROUP BY tenant_id, account_number
HAVING COUNT(*) > 1;

-- Check 2: Verify gl_journal_lines has no rows violating the implicit
-- one-side constraint (should already be guaranteed by existing check,
-- but confirm before rename in Migration 005)
SELECT id
FROM gl_journal_lines
WHERE debit_amount > 0 AND credit_amount > 0;

-- Check 3: Confirm migration history table reflects exactly 3 applied migrations
SELECT version, description, applied_at
FROM schema_migrations
ORDER BY version;
-- Expected: rows for 001, 002, 003 only

7.2 Post-Migration Validation Checks​

Execute after each migration is applied:

-- After Migration 004: verify gl_entities table exists and indexes are present
SELECT tablename, indexname
FROM pg_indexes
WHERE tablename = 'gl_entities'
ORDER BY indexname;

-- After Migration 005: verify column renames were applied correctly
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'gl_journal_lines'
  AND column_name IN (
    'transaction_currency', 'transaction_amount', 'transaction_rate',
    'functional_debit', 'functional_credit', 'functional_currency',
    'reporting_currency', 'reporting_rate', 'reporting_debit', 'reporting_credit'
  )
ORDER BY column_name;
-- Expected: 10 rows returned

-- After Migration 007: verify trigger exists and closure table is populated
-- (insert a test entity and verify closure entry)
SELECT trigger_name, event_manipulation
FROM information_schema.triggers
WHERE trigger_name = 'trg_entity_closure_maintain';

7.3 Rollback Procedures​

All Phase 1 migrations have corresponding rollback procedures. Rollback is intended for non-production environments only; production rollback requires explicit authorization from the engineering lead.

-- Rollback Migration 007 (closure table)
DROP TRIGGER IF EXISTS trg_entity_closure_maintain ON gl_entities;
DROP FUNCTION IF EXISTS populate_entity_closure();
DROP FUNCTION IF EXISTS gl_entity_consolidation_scope(UUID);
DROP VIEW IF EXISTS gl_entity_children;
DROP TABLE IF EXISTS gl_entity_closure;

-- Rollback Migration 006 (document_date, document_number)
ALTER TABLE gl_journal_entries
  DROP COLUMN IF EXISTS document_date,
  DROP COLUMN IF EXISTS document_number;
DROP INDEX IF EXISTS idx_gl_entries_document_date;
DROP INDEX IF EXISTS idx_gl_entries_document_number;

-- Rollback Migration 005 (column renames — reverse direction)
-- NOTE: Application code must be reverted BEFORE rolling back schema
ALTER TABLE gl_journal_lines
  DROP COLUMN IF EXISTS reporting_currency,
  DROP COLUMN IF EXISTS reporting_rate,
  DROP COLUMN IF EXISTS reporting_debit,
  DROP COLUMN IF EXISTS reporting_credit,
  DROP COLUMN IF EXISTS functional_currency,
  DROP COLUMN IF EXISTS entity_id;
ALTER TABLE gl_journal_lines RENAME COLUMN transaction_currency TO original_currency;
ALTER TABLE gl_journal_lines RENAME COLUMN transaction_amount   TO original_amount;
ALTER TABLE gl_journal_lines RENAME COLUMN transaction_rate     TO exchange_rate;
ALTER TABLE gl_journal_lines RENAME COLUMN functional_debit     TO debit_amount;
ALTER TABLE gl_journal_lines RENAME COLUMN functional_credit    TO credit_amount;

-- Rollback Migration 004 (entity columns and gl_entities table)
ALTER TABLE gl_journal_entries DROP COLUMN IF EXISTS entity_id;
ALTER TABLE gl_fiscal_periods
  DROP CONSTRAINT IF EXISTS uq_period_tenant_entity_year_num;
ALTER TABLE gl_fiscal_periods
  ADD CONSTRAINT uq_period_tenant_year_num
    UNIQUE (tenant_id, fiscal_year, period_number);
ALTER TABLE gl_fiscal_periods DROP COLUMN IF EXISTS entity_id;
ALTER TABLE gl_accounts
  DROP CONSTRAINT IF EXISTS uq_account_number_tenant_entity;
ALTER TABLE gl_accounts
  ADD CONSTRAINT uq_account_number_tenant UNIQUE (tenant_id, account_number);
ALTER TABLE gl_accounts DROP COLUMN IF EXISTS entity_id;
DROP TYPE IF EXISTS gl_entity_type;
DROP TABLE IF EXISTS gl_entities;
DocumentTitleLocation
CFS-DB-001Database Architecture Overviewdocs/database/CFS-DB-001-ARCHITECTURE.md
CFS-DB-002Core Schema Referencedocs/database/CFS-DB-002-CORE-SCHEMA.md
CFS-DB-003Security & Multi-Tenancy Modeldocs/database/CFS-DB-003-SECURITY.md
CFS-DB-004Business Logic Layerdocs/database/CFS-DB-004-BUSINESS-LOGIC.md
CFS-004Technical Architecture (§6.2 Core GL Schema, §6.3 Partitioning)docs/architecture/CFS-004-TECHNICAL-ARCHITECTURE.md
CFS-008Regulatory Compliance Matrixdocs/internal-confidential/CFS-008-REGULATORY-COMPLIANCE-MATRIX.md
CFS-009Development Roadmapdocs/internal-confidential/CFS-009-DEVELOPMENT-ROADMAP.md
RES-GL-006Universal GL Foundation Schema & Gap Analysisdocs/research/
001_core_schema.sqlApplied Migration 001GL-PROTOTYPE/src/schema/migrations/001_core_schema.sql
002_account_name_translations.sqlApplied Migration 002GL-PROTOTYPE/src/schema/migrations/002_account_name_translations.sql
003_account_currency.sqlApplied Migration 003GL-PROTOTYPE/src/schema/migrations/003_account_currency.sql

Copyright 2026 AZ1.AI Inc. All rights reserved. This document and its contents are the proprietary and confidential information of AZ1.AI Inc. and may not be reproduced, distributed, or disclosed in whole or in part without the prior written consent of AZ1.AI Inc.