Skip to main content

INTERNAL CONFIDENTIAL — AZ1.AI Inc. — Internal Use 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-004: Business Logic Layer — Triggers & Stored Functions

Document Code: CFS-DB-004 Classification: INTERNAL CONFIDENTIAL Owner: Hal Casteel, CEO/CTO, AZ1.AI Inc. Last Updated: 2026-02-20 Database: PostgreSQL 16+ Component: GL Engine — Business Logic Layer

Table of Contents​

  1. Overview
  2. Design Philosophy
  3. Trigger Reference
  4. Stored Function Reference
  5. Error Code Reference
  6. Trigger Execution Order
  7. Session Variable Reference
  8. Testing & Validation

1. Overview​

This document is the authoritative technical reference for all database-level business logic in the CODITECT Financial Suite GL Engine. The GL Engine enforces accounting integrity entirely within the PostgreSQL 16+ database layer, ensuring that no application bug, network failure, or API bypass can produce an invalid accounting state.

The business logic layer comprises:

  • 10 triggers enforcing constraint rules, state machine transitions, audit capture, and balance maintenance
  • 4 stored functions supporting reporting, period management, and data repair operations

All triggers are written in PL/pgSQL. All user-facing error messages follow the GL error code convention (GL_NNN) for consistent, machine-parseable error handling at the application layer.

DocumentCodeContent
Schema ReferenceCFS-DB-001Table definitions, column types, constraints
Indexing StrategyCFS-DB-002Index design and performance guidance
Multi-Tenant IsolationCFS-DB-003Row-Level Security policies
Business Logic LayerCFS-DB-004This document — triggers and stored functions
Migration HistoryCFS-DB-005Schema version history and migration scripts

2. Design Philosophy​

2.1 Database as Final Authority​

The GL Engine treats the database as the ultimate enforcement boundary for accounting rules. Application-layer validation is a convenience for user experience; database-layer triggers are the true guarantors of correctness. This approach ensures that:

  • Direct SQL access (administrative, batch import, API bypass) cannot produce invalid data
  • Accounting invariants hold regardless of which application version, service instance, or client library initiates the operation
  • Audit records are produced atomically with the data change, within the same transaction

2.2 Fail Loud, Fail Fast​

All triggers that detect a violation raise an exception immediately using RAISE EXCEPTION with a structured error code. The PostgreSQL exception bubbles up through the call stack, rolls back the transaction, and surfaces a structured error to the application layer. There is no silent data correction or partial acceptance.

2.3 Statement-Level vs. Row-Level Triggers​

Most triggers in the GL Engine fire at the row level (FOR EACH ROW), which is appropriate for per-record validation and state transitions. The double-entry enforcement trigger fires at the statement level (FOR EACH STATEMENT), which is a deliberate architectural decision: it permits efficient batch inserts of journal lines (debit and credit in the same INSERT statement or multi-row INSERT) while still enforcing the double-entry invariant after the entire statement completes.

2.4 Normal Balance Direction​

The GL Engine tracks a normal_balance attribute per account (DEBIT or CREDIT). This convention follows standard accounting:

  • DEBIT normal balance: Assets, Expenses — closing balance = cumulative debits - cumulative credits
  • CREDIT normal balance: Liabilities, Equity, Revenue — closing balance = cumulative credits - cumulative debits

All balance calculations in triggers and stored functions respect this convention.

3. Trigger Reference​

Summary Table​

#Trigger NameTableLevelTimingEvent
1trg_enforce_double_entrygl_journal_linesSTATEMENTAFTERINSERT
2trg_prevent_posted_modificationgl_journal_entriesROWBEFOREUPDATE
3trg_prevent_closed_period_entrygl_journal_entriesROWBEFOREINSERT
4trg_check_period_on_postgl_journal_entriesROWBEFOREUPDATE
5trg_update_balancesgl_journal_linesROWAFTERINSERT
6trg_audit_* (6 triggers)6 tablesROWAFTERINSERT OR UPDATE
7trg_prevent_audit_mutationgl_audit_logROWBEFOREUPDATE OR DELETE
8trg_period_state_machinegl_fiscal_periodsROWBEFOREUPDATE
9trg_prevent_inactive_account_postinggl_journal_linesROWBEFOREINSERT
10trg_updated_at_* (3 triggers)3 tablesROWBEFOREUPDATE

3.1 trg_enforce_double_entry​

Purpose: Enforce the fundamental double-entry accounting invariant: the sum of all debit amounts must equal the sum of all credit amounts for every journal entry.

Trigger Definition:

CREATE TRIGGER trg_enforce_double_entry
    AFTER INSERT ON gl_journal_lines
    REFERENCING NEW TABLE AS new_table
    FOR EACH STATEMENT
    EXECUTE FUNCTION fn_enforce_double_entry();

Function:

CREATE OR REPLACE FUNCTION fn_enforce_double_entry()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_journal_entry_id  UUID;
    v_debit_total       NUMERIC(20, 6);
    v_credit_total      NUMERIC(20, 6);
BEGIN
    -- Iterate over each distinct journal_entry_id present in the inserted batch.
    -- Using a transition table (new_table) allows a single pass over all inserted rows
    -- rather than per-row queries, preserving performance for bulk inserts.
    FOR v_journal_entry_id IN
        SELECT DISTINCT journal_entry_id FROM new_table
    LOOP
        SELECT
            COALESCE(SUM(debit_amount),  0),
            COALESCE(SUM(credit_amount), 0)
        INTO v_debit_total, v_credit_total
        FROM gl_journal_lines
        WHERE journal_entry_id = v_journal_entry_id;

        IF v_debit_total != v_credit_total THEN
            RAISE EXCEPTION
                'GL_001: Double-entry violation for journal entry %. Debits (%) != Credits (%)',
                v_journal_entry_id,
                v_debit_total,
                v_credit_total
            USING ERRCODE = 'P0001';
        END IF;
    END LOOP;

    RETURN NULL; -- Statement-level triggers must return NULL
END;
$$;

Design Notes:

The trigger fires after the INSERT statement completes, using a transition table (REFERENCING NEW TABLE AS new_table). This is the critical design choice: it allows multi-row INSERT statements that include both the debit and credit lines of a journal entry to pass through before validation occurs. A BEFORE trigger or a per-row AFTER trigger would fire after each individual line, making it impossible to batch-insert a balanced entry in a single statement.

The function queries gl_journal_lines directly (not just new_table) to capture the full running totals for each affected journal entry. This is necessary when journal lines may be inserted across multiple statements within the same transaction — though in normal operation, all lines for a journal entry should be inserted in a single statement.

Fires on: gl_journal_lines — INSERT (statement-level, AFTER)

3.2 trg_prevent_posted_modification​

Purpose: Enforce immutability rules on journal entries in terminal states. A POSTED entry is the ledger of record; a REVERSED entry is the permanent accounting correction record.

State Immutability Rules:

Entry StatusPermitted Operations
DRAFTFull UPDATE allowed
POSTEDOnly status column may change, exclusively to REVERSED
REVERSEDNo UPDATE permitted

Trigger Definition:

CREATE TRIGGER trg_prevent_posted_modification
    BEFORE UPDATE ON gl_journal_entries
    FOR EACH ROW
    EXECUTE FUNCTION fn_prevent_posted_modification();

Function:

CREATE OR REPLACE FUNCTION fn_prevent_posted_modification()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- REVERSED entries are fully immutable — no field may change
    IF OLD.status = 'REVERSED' THEN
        RAISE EXCEPTION
            'GL_031: Cannot modify a REVERSED journal entry (id: %)',
            OLD.id
        USING ERRCODE = 'P0001';
    END IF;

    -- POSTED entries may only transition their status to REVERSED.
    -- All other field modifications are blocked.
    IF OLD.status = 'POSTED' THEN
        IF NEW.status != 'REVERSED' THEN
            RAISE EXCEPTION
                'GL_030: Cannot modify a POSTED journal entry (id: %). '
                'Only status transition to REVERSED is permitted.',
                OLD.id
            USING ERRCODE = 'P0001';
        END IF;

        -- Block any field change other than status
        IF (
            NEW.tenant_id         IS DISTINCT FROM OLD.tenant_id         OR
            NEW.period_id         IS DISTINCT FROM OLD.period_id         OR
            NEW.reference_number  IS DISTINCT FROM OLD.reference_number  OR
            NEW.entry_date        IS DISTINCT FROM OLD.entry_date        OR
            NEW.description       IS DISTINCT FROM OLD.description       OR
            NEW.currency_code     IS DISTINCT FROM OLD.currency_code     OR
            NEW.exchange_rate     IS DISTINCT FROM OLD.exchange_rate     OR
            NEW.source_module     IS DISTINCT FROM OLD.source_module     OR
            NEW.source_reference  IS DISTINCT FROM OLD.source_reference
        ) THEN
            RAISE EXCEPTION
                'GL_030: Cannot modify fields of a POSTED journal entry (id: %). '
                'Only status transition to REVERSED is permitted.',
                OLD.id
            USING ERRCODE = 'P0001';
        END IF;
    END IF;

    RETURN NEW;
END;
$$;

Design Notes:

The guard on REVERSED entries fires first (lines 1-7 of the logic), preventing any attempt to re-post or re-draft a reversed entry. The POSTED guard then follows, permitting only the status transition to REVERSED while blocking all data field modifications. The field-level check uses IS DISTINCT FROM (rather than !=) to correctly handle NULL comparisons.

Fires on: gl_journal_entries — UPDATE (row-level, BEFORE)

3.3 trg_prevent_closed_period_entry​

Purpose: Prevent direct insertion of a journal entry in POSTED status when the target fiscal period is not OPEN.

Trigger Definition:

CREATE TRIGGER trg_prevent_closed_period_entry
    BEFORE INSERT ON gl_journal_entries
    FOR EACH ROW
    WHEN (NEW.status = 'POSTED')
    EXECUTE FUNCTION fn_prevent_closed_period_entry();

Function:

CREATE OR REPLACE FUNCTION fn_prevent_closed_period_entry()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_period_state  TEXT;
BEGIN
    SELECT state
    INTO v_period_state
    FROM gl_fiscal_periods
    WHERE id = NEW.period_id
      AND tenant_id = NEW.tenant_id;

    IF NOT FOUND OR v_period_state != 'OPEN' THEN
        RAISE EXCEPTION
            'GL_010: Cannot post to fiscal period %. Period not found or not in OPEN state (current state: %)',
            NEW.period_id,
            COALESCE(v_period_state, 'NOT FOUND')
        USING ERRCODE = 'P0001';
    END IF;

    RETURN NEW;
END;
$$;

Design Notes:

The WHEN (NEW.status = 'POSTED') condition on the trigger definition means this trigger body executes only when a POSTED journal entry is inserted directly. Entries inserted in DRAFT status pass through without a period check. This is the correct behavior: draft entries are work-in-progress and may legitimately exist against a period that is not yet open (e.g., pre-posting staging). The period check occurs at the moment of commitment (DRAFT→POSTED transition), which is handled by the companion trigger trg_check_period_on_post.

Fires on: gl_journal_entries — INSERT (row-level, BEFORE, conditional on status = 'POSTED')

3.4 trg_check_period_on_post​

Purpose: Enforce the period-OPEN requirement at the moment a journal entry transitions from DRAFT to POSTED status.

Trigger Definition:

CREATE TRIGGER trg_check_period_on_post
    BEFORE UPDATE ON gl_journal_entries
    FOR EACH ROW
    WHEN (NEW.status = 'POSTED' AND OLD.status = 'DRAFT')
    EXECUTE FUNCTION fn_check_period_on_post();

Function:

CREATE OR REPLACE FUNCTION fn_check_period_on_post()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_period_state  TEXT;
BEGIN
    SELECT state
    INTO v_period_state
    FROM gl_fiscal_periods
    WHERE id = NEW.period_id
      AND tenant_id = NEW.tenant_id;

    IF NOT FOUND OR v_period_state != 'OPEN' THEN
        RAISE EXCEPTION
            'GL_010: Cannot post journal entry % to fiscal period %. '
            'Period not found or not in OPEN state (current state: %)',
            NEW.id,
            NEW.period_id,
            COALESCE(v_period_state, 'NOT FOUND')
        USING ERRCODE = 'P0001';
    END IF;

    RETURN NEW;
END;
$$;

Design Notes:

This trigger and trg_prevent_closed_period_entry together cover both paths to a POSTED entry: direct INSERT as POSTED, and the normal DRAFT→POSTED UPDATE transition. Both use the same GL_010 error code so that the application layer can handle them uniformly. The WHEN clause restricts execution to exactly the DRAFT→POSTED transition, avoiding redundant period checks on other UPDATE operations (e.g., POSTED→REVERSED, metadata corrections on DRAFT entries).

Fires on: gl_journal_entries — UPDATE (row-level, BEFORE, conditional on status transition DRAFT→POSTED)

3.5 trg_update_balances​

Purpose: Maintain the gl_account_balances materialized cache incrementally as POSTED journal lines are inserted.

Trigger Definition:

CREATE TRIGGER trg_update_balances
    AFTER INSERT ON gl_journal_lines
    FOR EACH ROW
    EXECUTE FUNCTION fn_update_balances();

Function:

CREATE OR REPLACE FUNCTION fn_update_balances()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_entry_status   TEXT;
    v_normal_balance TEXT;
BEGIN
    -- Only update balances for lines belonging to POSTED journal entries.
    -- Lines for DRAFT entries are ignored; balances are updated when the
    -- entry is posted via a separate trigger on gl_journal_entries.
    SELECT status
    INTO v_entry_status
    FROM gl_journal_entries
    WHERE id = NEW.journal_entry_id;

    IF v_entry_status IS DISTINCT FROM 'POSTED' THEN
        RETURN NEW; -- No-op for non-POSTED entries
    END IF;

    -- Retrieve the account's normal balance direction
    SELECT normal_balance
    INTO v_normal_balance
    FROM gl_accounts
    WHERE id = NEW.account_id;

    -- UPSERT into the balance cache.
    -- Opening balance is never modified here; it is set by gl_carry_forward_balances.
    -- Closing balance is recomputed as a function of normal_balance direction.
    INSERT INTO gl_account_balances (
        tenant_id,
        account_id,
        period_id,
        opening_balance,
        period_debits,
        period_credits,
        closing_balance
    )
    VALUES (
        NEW.tenant_id,
        NEW.account_id,
        (SELECT period_id FROM gl_journal_entries WHERE id = NEW.journal_entry_id),
        0,
        NEW.debit_amount,
        NEW.credit_amount,
        CASE
            WHEN v_normal_balance = 'DEBIT'
                THEN NEW.debit_amount - NEW.credit_amount
            ELSE
                NEW.credit_amount - NEW.debit_amount
        END
    )
    ON CONFLICT (tenant_id, account_id, period_id) DO UPDATE SET
        period_debits   = gl_account_balances.period_debits  + EXCLUDED.period_debits,
        period_credits  = gl_account_balances.period_credits + EXCLUDED.period_credits,
        closing_balance = CASE
            WHEN v_normal_balance = 'DEBIT'
                THEN gl_account_balances.opening_balance
                   + gl_account_balances.period_debits  + EXCLUDED.period_debits
                   - gl_account_balances.period_credits - EXCLUDED.period_credits
            ELSE
                gl_account_balances.opening_balance
                   + gl_account_balances.period_credits + EXCLUDED.period_credits
                   - gl_account_balances.period_debits  - EXCLUDED.period_debits
        END,
        updated_at = NOW();

    RETURN NEW;
END;
$$;

Design Notes:

The gl_account_balances table is a materialized cache, not the source of truth. The source of truth is always gl_journal_lines. The trigger maintains the cache incrementally to support fast balance lookups and report generation without requiring full aggregation scans over the journal lines table.

The trigger deliberately ignores lines belonging to DRAFT entries. When an entry is posted (DRAFT→POSTED), a companion trigger on gl_journal_entries is responsible for triggering balance updates for all pre-existing lines. This two-phase approach means the balance cache reflects only committed, posted accounting data.

The closing balance formula is computed as: opening_balance + period_debits - period_credits for DEBIT-normal accounts, and opening_balance + period_credits - period_debits for CREDIT-normal accounts. This ensures that positive closing balance values represent the economically expected sign for each account type.

Fires on: gl_journal_lines — INSERT (row-level, AFTER)

3.6 trg_audit_* (Audit Family)​

Purpose: Capture a complete, immutable audit record for every data change across all primary GL tables. Audit records contain full JSONB before/after snapshots and are written atomically within the same transaction as the data change.

Trigger Inventory:

Trigger NameTableEvents
trg_audit_accountsgl_accountsINSERT, UPDATE
trg_audit_periodsgl_fiscal_periodsINSERT, UPDATE
trg_audit_entriesgl_journal_entriesINSERT, UPDATE
trg_audit_linesgl_journal_linesINSERT only
trg_audit_balancesgl_account_balancesINSERT, UPDATE
trg_audit_exchange_ratesgl_exchange_ratesINSERT, UPDATE

Trigger Definition Pattern (example for gl_journal_entries):

CREATE TRIGGER trg_audit_entries
    AFTER INSERT OR UPDATE ON gl_journal_entries
    FOR EACH ROW
    EXECUTE FUNCTION fn_audit_log();

Function:

CREATE OR REPLACE FUNCTION fn_audit_log()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_action    TEXT;
    v_old_data  JSONB;
    v_new_data  JSONB;
    v_user_id   UUID;
BEGIN
    -- Determine action type.
    -- STATUS_CHANGE is a semantic distinction for state-machine transitions
    -- on journal entries and fiscal periods; it is not a separate DML event.
    IF TG_OP = 'INSERT' THEN
        v_action   := 'INSERT';
        v_old_data := NULL;
        v_new_data := to_jsonb(NEW);

    ELSIF TG_OP = 'UPDATE' THEN
        v_old_data := to_jsonb(OLD);
        v_new_data := to_jsonb(NEW);

        -- Detect status/state column changes for semantic action labeling
        IF TG_TABLE_NAME = 'gl_journal_entries'
           AND OLD.status IS DISTINCT FROM NEW.status THEN
            v_action := 'STATUS_CHANGE';
        ELSIF TG_TABLE_NAME = 'gl_fiscal_periods'
           AND OLD.state IS DISTINCT FROM NEW.state THEN
            v_action := 'STATUS_CHANGE';
        ELSE
            v_action := 'UPDATE';
        END IF;
    END IF;

    -- Read the current user ID from the session variable.
    -- The application layer is responsible for setting this variable at
    -- the start of each database session / transaction.
    BEGIN
        v_user_id := current_setting('app.current_user_id')::UUID;
    EXCEPTION WHEN OTHERS THEN
        v_user_id := NULL; -- Allow system operations without a user context
    END;

    INSERT INTO gl_audit_log (
        tenant_id,
        table_name,
        record_id,
        action,
        old_data,
        new_data,
        user_id,
        created_at
    )
    VALUES (
        COALESCE(NEW.tenant_id, OLD.tenant_id),
        TG_TABLE_NAME,
        COALESCE(NEW.id, OLD.id),
        v_action,
        v_old_data,
        v_new_data,
        v_user_id,
        NOW()
    );

    RETURN COALESCE(NEW, OLD);
END;
$$;

Design Notes:

A single function (fn_audit_log) serves all six audit triggers. The function uses PostgreSQL's built-in trigger variables TG_TABLE_NAME and TG_OP to adapt its behavior per table and operation.

The STATUS_CHANGE action type is a semantic classification applied on top of the standard UPDATE event. It allows downstream audit analysis tools and compliance exports to quickly filter state machine transitions without parsing the old_data/new_data JSONB diff. This is particularly important for regulatory requirements (e.g., Brazilian SPED fiscal audit trail) that require explicit documentation of status transitions.

Journal lines (gl_journal_lines) are audited on INSERT only. Lines are immutable after creation; deletion is not permitted. Therefore UPDATE and DELETE audit triggers are not applied to this table.

The app.current_user_id session variable is set by the application layer at the start of each authenticated database session. The audit function uses an exception handler to tolerate missing session variables, allowing system-level operations (migrations, scheduled jobs) to proceed without a user context while still capturing the data change.

Fires on: 6 tables — INSERT and/or UPDATE (row-level, AFTER)

3.7 trg_prevent_audit_mutation​

Purpose: Make the audit log table completely immutable. Once a record is written to gl_audit_log, it cannot be modified or deleted through any SQL operation, including administrative DML.

Trigger Definition:

CREATE TRIGGER trg_prevent_audit_mutation
    BEFORE UPDATE OR DELETE ON gl_audit_log
    FOR EACH ROW
    EXECUTE FUNCTION fn_prevent_audit_mutation();

Function:

CREATE OR REPLACE FUNCTION fn_prevent_audit_mutation()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE EXCEPTION
        'GL_060: The audit log is immutable. Record (id: %) cannot be modified or deleted.',
        OLD.id
    USING ERRCODE = 'P0001';

    -- This line is never reached; it satisfies the compiler requirement
    -- for BEFORE triggers that must return a row or NULL.
    RETURN NULL;
END;
$$;

Design Notes:

This trigger, combined with a PostgreSQL table-level privilege that revokes DELETE on gl_audit_log from the application database role, provides defense in depth for audit immutability. The trigger catches mutations attempted by any database role, including the application role.

Legitimate audit log archival operations must be performed by a dedicated database role with elevated privileges that bypasses this trigger via ALTER TABLE gl_audit_log DISABLE TRIGGER ALL within a controlled, logged administrative session. Such operations are subject to the change management procedures defined in CFS-DB-005.

Fires on: gl_audit_log — UPDATE OR DELETE (row-level, BEFORE)

3.8 trg_period_state_machine​

Purpose: Enforce the valid state transition graph for fiscal periods, preventing invalid transitions and automatically recording transition timestamps.

Valid State Transitions:

FUTURE  -->  OPEN
OPEN    -->  CLOSED
CLOSED  -->  LOCKED

No reverse transitions are permitted. A LOCKED period is permanently closed.

Trigger Definition:

CREATE TRIGGER trg_period_state_machine
    BEFORE UPDATE ON gl_fiscal_periods
    FOR EACH ROW
    EXECUTE FUNCTION fn_period_state_machine();

Function:

CREATE OR REPLACE FUNCTION fn_period_state_machine()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- Only validate state transitions; non-state field updates pass through
    IF OLD.state IS NOT DISTINCT FROM NEW.state THEN
        RETURN NEW;
    END IF;

    -- Validate the transition against the permitted state graph
    IF NOT (
        (OLD.state = 'FUTURE' AND NEW.state = 'OPEN'  ) OR
        (OLD.state = 'OPEN'   AND NEW.state = 'CLOSED') OR
        (OLD.state = 'CLOSED' AND NEW.state = 'LOCKED')
    ) THEN
        RAISE EXCEPTION
            'GL_013: Invalid fiscal period state transition from % to % (period id: %)',
            OLD.state,
            NEW.state,
            OLD.id
        USING ERRCODE = 'P0001';
    END IF;

    -- Auto-record transition timestamps
    IF NEW.state = 'CLOSED' THEN
        NEW.closed_at := NOW();
    END IF;

    IF NEW.state = 'LOCKED' THEN
        NEW.locked_at := NOW();
    END IF;

    RETURN NEW;
END;
$$;

Design Notes:

The state machine is encoded directly in the trigger rather than in a lookup table. This is intentional: the valid transitions for a fiscal period are a fundamental accounting invariant that should not be configurable at runtime. Encoding them as a fixed enumeration in the trigger body makes the constraints self-documenting and prevents accidental modification.

The closed_at and locked_at timestamps are set by the trigger itself, not by the application layer. This ensures that these timestamps reflect the actual moment of transition as recorded by the database server clock, regardless of application-layer clock skew or client-supplied values.

Fires on: gl_fiscal_periods — UPDATE (row-level, BEFORE)

3.9 trg_prevent_inactive_account_posting​

Purpose: Reject journal lines that target accounts in non-ACTIVE states, preventing postings to accounts that have been deactivated or blocked.

Account Status Rules:

Account StatusCan Receive Journal Lines
ACTIVEYes
INACTIVENo — GL_020
BLOCKEDNo — GL_020

Trigger Definition:

CREATE TRIGGER trg_prevent_inactive_account_posting
    BEFORE INSERT ON gl_journal_lines
    FOR EACH ROW
    EXECUTE FUNCTION fn_prevent_inactive_account_posting();

Function:

CREATE OR REPLACE FUNCTION fn_prevent_inactive_account_posting()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_account_status  TEXT;
BEGIN
    SELECT status
    INTO v_account_status
    FROM gl_accounts
    WHERE id        = NEW.account_id
      AND tenant_id = NEW.tenant_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION
            'GL_022: Account not found (id: %, tenant: %)',
            NEW.account_id,
            NEW.tenant_id
        USING ERRCODE = 'P0001';
    END IF;

    IF v_account_status != 'ACTIVE' THEN
        RAISE EXCEPTION
            'GL_020: Cannot post journal line to account % (status: %). '
            'Only ACTIVE accounts may receive journal entries.',
            NEW.account_id,
            v_account_status
        USING ERRCODE = 'P0001';
    END IF;

    RETURN NEW;
END;
$$;

Design Notes:

Two distinct error codes are used: GL_022 for the case where the account does not exist at all (which may indicate a data integrity problem at a higher level), and GL_020 for the case where the account exists but is not in a postable state. This distinction allows the application layer to surface different user-facing messages: GL_022 should escalate to a system error, while GL_020 may be surfaced as a user-actionable validation message.

The tenant_id guard in the SELECT is essential: it prevents cross-tenant account lookups that could theoretically occur in an improperly constructed query.

Fires on: gl_journal_lines — INSERT (row-level, BEFORE)

3.10 trg_updated_at_* (Timestamp Family)​

Purpose: Automatically maintain the updated_at timestamp column on tables that support it, ensuring that modification times are always accurate regardless of the update source.

Trigger Inventory:

Trigger NameTable
trg_updated_at_accountsgl_accounts
trg_updated_at_periodsgl_fiscal_periods
trg_updated_at_entriesgl_journal_entries

Trigger Definition Pattern:

CREATE TRIGGER trg_updated_at_accounts
    BEFORE UPDATE ON gl_accounts
    FOR EACH ROW
    EXECUTE FUNCTION fn_set_updated_at();

Function:

CREATE OR REPLACE FUNCTION fn_set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$;

Design Notes:

A single function (fn_set_updated_at) serves all three timestamp triggers. The function is intentionally minimal. Using NOW() within the trigger ensures that updated_at reflects the transaction start time (consistent within a transaction) rather than the wall clock time of each individual row update, which is the standard PostgreSQL convention for audit timestamps.

Fires on: gl_accounts, gl_fiscal_periods, gl_journal_entries — UPDATE (row-level, BEFORE)

4. Stored Function Reference​

Stored functions in the GL Engine are query functions (STABLE or VOLATILE) that provide higher-level operations on GL data. They differ from trigger functions in that they are invoked explicitly by the application layer or administrative scripts, not automatically by database events.

4.1 gl_verify_balances​

Signature:

CREATE OR REPLACE FUNCTION gl_verify_balances(
    p_tenant_id  UUID,
    p_period_id  UUID
)
RETURNS TABLE (
    account_id       UUID,
    account_number   TEXT,
    stored_closing   NUMERIC(20, 6),
    computed_closing NUMERIC(20, 6),
    difference       NUMERIC(20, 6)
)
LANGUAGE plpgsql
STABLE

Full Implementation:

CREATE OR REPLACE FUNCTION gl_verify_balances(
    p_tenant_id  UUID,
    p_period_id  UUID
)
RETURNS TABLE (
    account_id       UUID,
    account_number   TEXT,
    stored_closing   NUMERIC(20, 6),
    computed_closing NUMERIC(20, 6),
    difference       NUMERIC(20, 6)
)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
    RETURN QUERY
    WITH computed AS (
        -- Recompute balances from source journal lines
        SELECT
            jl.account_id,
            ab_ref.opening_balance,
            SUM(jl.debit_amount)              AS period_debits,
            SUM(jl.credit_amount)             AS period_credits,
            a.normal_balance
        FROM gl_journal_lines jl
        JOIN gl_journal_entries je
            ON je.id        = jl.journal_entry_id
           AND je.tenant_id = p_tenant_id
           AND je.period_id = p_period_id
           AND je.status    = 'POSTED'
        JOIN gl_accounts a
            ON a.id        = jl.account_id
           AND a.tenant_id = p_tenant_id
        LEFT JOIN gl_account_balances ab_ref
            ON ab_ref.account_id = jl.account_id
           AND ab_ref.period_id  = p_period_id
           AND ab_ref.tenant_id  = p_tenant_id
        WHERE jl.tenant_id = p_tenant_id
        GROUP BY
            jl.account_id,
            ab_ref.opening_balance,
            a.normal_balance
    ),
    computed_closing AS (
        SELECT
            c.account_id,
            CASE
                WHEN c.normal_balance = 'DEBIT'
                    THEN COALESCE(c.opening_balance, 0)
                       + c.period_debits
                       - c.period_credits
                ELSE
                    COALESCE(c.opening_balance, 0)
                       + c.period_credits
                       - c.period_debits
            END AS computed_closing
        FROM computed c
    )
    SELECT
        a.id                                 AS account_id,
        a.account_number                     AS account_number,
        COALESCE(ab.closing_balance, 0)      AS stored_closing,
        COALESCE(cc.computed_closing, 0)     AS computed_closing,
        COALESCE(cc.computed_closing, 0)
            - COALESCE(ab.closing_balance, 0) AS difference
    FROM gl_accounts a
    LEFT JOIN gl_account_balances ab
        ON ab.account_id = a.id
       AND ab.period_id  = p_period_id
       AND ab.tenant_id  = p_tenant_id
    LEFT JOIN computed_closing cc
        ON cc.account_id = a.id
    WHERE a.tenant_id = p_tenant_id
      AND a.status    = 'ACTIVE'
      AND (
          -- Return only accounts with discrepancies
          COALESCE(cc.computed_closing, 0) != COALESCE(ab.closing_balance, 0)
          -- Or accounts with POSTED journal lines but no balance cache row
          OR (cc.account_id IS NOT NULL AND ab.account_id IS NULL)
      )
    ORDER BY a.account_number;
END;
$$;

Return Value: A set of rows, one per account with a discrepancy. An empty result set indicates that all balance cache values match the recomputed values — balances are verified.

Usage Contexts:

  • Pre-period-close validation: Run before executing OPEN → CLOSED state transition to confirm the balance cache is consistent before it is used as the basis for period-end reporting
  • Pre-SPED export validation: Run before generating Brazilian SPED fiscal files to confirm the data underlying the export is internally consistent
  • Post-recompute verification: Run after gl_recompute_balances to confirm the recomputation completed correctly

Performance Characteristics: This function performs a full scan of POSTED journal lines for the specified period. For periods with large transaction volumes, execution time may be significant. It is marked STABLE (read-only, may use query result caching within a transaction) and should not be called in OLTP hot paths.

4.2 gl_get_trial_balance​

Signature:

CREATE OR REPLACE FUNCTION gl_get_trial_balance(
    p_tenant_id  UUID,
    p_period_id  UUID
)
RETURNS TABLE (
    account_id      UUID,
    account_number  TEXT,
    account_name    TEXT,
    account_type    TEXT,
    normal_balance  TEXT,
    opening_balance NUMERIC(20, 6),
    period_debits   NUMERIC(20, 6),
    period_credits  NUMERIC(20, 6),
    closing_balance NUMERIC(20, 6)
)
LANGUAGE plpgsql
STABLE

Full Implementation:

CREATE OR REPLACE FUNCTION gl_get_trial_balance(
    p_tenant_id  UUID,
    p_period_id  UUID
)
RETURNS TABLE (
    account_id      UUID,
    account_number  TEXT,
    account_name    TEXT,
    account_type    TEXT,
    normal_balance  TEXT,
    opening_balance NUMERIC(20, 6),
    period_debits   NUMERIC(20, 6),
    period_credits  NUMERIC(20, 6),
    closing_balance NUMERIC(20, 6)
)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
    RETURN QUERY
    SELECT
        a.id                                         AS account_id,
        a.account_number                             AS account_number,
        a.name                                       AS account_name,
        a.account_type                               AS account_type,
        a.normal_balance                             AS normal_balance,
        COALESCE(ab.opening_balance, 0::NUMERIC)     AS opening_balance,
        COALESCE(ab.period_debits,   0::NUMERIC)     AS period_debits,
        COALESCE(ab.period_credits,  0::NUMERIC)     AS period_credits,
        COALESCE(ab.closing_balance, 0::NUMERIC)     AS closing_balance
    FROM gl_accounts a
    LEFT JOIN gl_account_balances ab
        ON ab.account_id = a.id
       AND ab.period_id  = p_period_id
       AND ab.tenant_id  = p_tenant_id
    WHERE a.tenant_id   = p_tenant_id
      AND a.status      = 'ACTIVE'
      AND a.is_header   = FALSE  -- Exclude header/subtotal accounts
    ORDER BY a.account_number;
END;
$$;

Return Value: One row per active, non-header account, ordered by account number. Accounts with no activity in the period return zero balances (LEFT JOIN behavior). This is intentional: a trial balance should enumerate all active accounts, not only those with transactions in the period.

Usage Contexts:

  • Standard trial balance report generation
  • Balance sheet and income statement foundation data
  • External audit package preparation
  • Regulatory filing pre-computation (SPED, FEC, GAAP/IFRS statements)

Note on Header Accounts: The is_header = FALSE filter excludes accounts that serve as structural grouping nodes in the chart of accounts hierarchy. Header accounts do not receive direct postings; their balances, if needed for reporting, are computed by the reporting layer by summing their child accounts.

4.3 gl_carry_forward_balances​

Signature:

CREATE OR REPLACE FUNCTION gl_carry_forward_balances(
    p_tenant_id     UUID,
    p_from_period_id UUID,
    p_to_period_id  UUID,
    p_user_id       UUID
)
RETURNS INTEGER
LANGUAGE plpgsql

Full Implementation:

CREATE OR REPLACE FUNCTION gl_carry_forward_balances(
    p_tenant_id      UUID,
    p_from_period_id UUID,
    p_to_period_id   UUID,
    p_user_id        UUID
)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_count  INTEGER := 0;
BEGIN
    -- Set session user context for audit trigger
    PERFORM set_config('app.current_user_id', p_user_id::TEXT, TRUE);

    INSERT INTO gl_account_balances (
        tenant_id,
        account_id,
        period_id,
        opening_balance,
        period_debits,
        period_credits,
        closing_balance
    )
    SELECT
        p_tenant_id,
        from_ab.account_id,
        p_to_period_id,
        from_ab.closing_balance,  -- Closing of source period becomes opening of target
        0,                         -- No transactions yet in target period
        0,
        from_ab.closing_balance   -- Opening = closing until transactions are posted
    FROM gl_account_balances from_ab
    WHERE from_ab.tenant_id = p_tenant_id
      AND from_ab.period_id = p_from_period_id
      AND from_ab.closing_balance != 0  -- Only carry non-zero balances
    ON CONFLICT (tenant_id, account_id, period_id) DO UPDATE SET
        opening_balance = EXCLUDED.opening_balance,
        -- Recalculate closing balance preserving any period transactions already present
        closing_balance = EXCLUDED.opening_balance
                        + gl_account_balances.period_debits
                        - gl_account_balances.period_credits,
        updated_at      = NOW();

    GET DIAGNOSTICS v_count = ROW_COUNT;

    RETURN v_count;
END;
$$;

Return Value: Integer count of accounts whose balances were carried forward.

Usage Contexts:

  • Period-end close process: carry closing balances from the period being closed to the next period's opening balances
  • Year-end rollover: balances carry forward into the first period of the new fiscal year
  • Must be called after the source period has been transitioned to CLOSED state

Important Behavioral Notes:

The ON CONFLICT clause handles the case where the target period already has balance rows (e.g., if carry-forward is re-run after a correction). In this case, it updates the opening_balance and recalculates the closing_balance by applying the existing period transactions to the new opening balance. This makes the function idempotent and safe to re-run.

Only non-zero closing balances are carried forward. Accounts with a net zero balance at period end are not represented in the target period's opening balances. This matches standard accounting convention for income statement accounts (which reset to zero each year) and keeps the balance table lean.

4.4 gl_recompute_balances​

Signature:

CREATE OR REPLACE FUNCTION gl_recompute_balances(
    p_tenant_id  UUID,
    p_period_id  UUID,
    p_user_id    UUID
)
RETURNS INTEGER
LANGUAGE plpgsql

Full Implementation:

CREATE OR REPLACE FUNCTION gl_recompute_balances(
    p_tenant_id  UUID,
    p_period_id  UUID,
    p_user_id    UUID
)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_count  INTEGER := 0;
BEGIN
    -- Set session user context for audit trigger
    PERFORM set_config('app.current_user_id', p_user_id::TEXT, TRUE);

    -- Remove existing balance cache rows for the period.
    -- This must happen before recomputation to avoid double-counting.
    -- Note: This DELETE will NOT produce audit log rows because gl_audit_log
    -- does not have a DELETE trigger on gl_account_balances (balances are
    -- a derived cache, not primary accounting data).
    DELETE FROM gl_account_balances
    WHERE tenant_id = p_tenant_id
      AND period_id = p_period_id;

    -- Recompute from all POSTED journal lines for the period
    INSERT INTO gl_account_balances (
        tenant_id,
        account_id,
        period_id,
        opening_balance,
        period_debits,
        period_credits,
        closing_balance
    )
    SELECT
        p_tenant_id,
        jl.account_id,
        p_period_id,
        0,                             -- Opening balance: NOT set by this function.
                                       -- Caller must invoke gl_carry_forward_balances
                                       -- to populate opening_balance separately.
        SUM(jl.debit_amount),
        SUM(jl.credit_amount),
        CASE
            WHEN a.normal_balance = 'DEBIT'
                THEN SUM(jl.debit_amount) - SUM(jl.credit_amount)
            ELSE
                SUM(jl.credit_amount) - SUM(jl.debit_amount)
        END
    FROM gl_journal_lines jl
    JOIN gl_journal_entries je
        ON je.id        = jl.journal_entry_id
       AND je.tenant_id = p_tenant_id
       AND je.period_id = p_period_id
       AND je.status    = 'POSTED'
    JOIN gl_accounts a
        ON a.id        = jl.account_id
       AND a.tenant_id = p_tenant_id
    WHERE jl.tenant_id = p_tenant_id
    GROUP BY jl.account_id, a.normal_balance;

    GET DIAGNOSTICS v_count = ROW_COUNT;

    RETURN v_count;
END;
$$;

Return Value: Integer count of accounts whose balances were recomputed.

CRITICAL WARNING — Opening Balance Handling:

gl_recompute_balances sets opening_balance = 0 for all recomputed rows. It does not carry forward opening balances from the prior period. After calling this function, the caller must invoke gl_carry_forward_balances with the correct source period to populate opening balances. The correct recovery sequence is:

BEGIN;

-- Step 1: Recompute period transactions from journal lines
SELECT gl_recompute_balances(p_tenant_id, p_period_id, p_user_id);

-- Step 2: Restore opening balances from prior period
SELECT gl_carry_forward_balances(p_tenant_id, p_prior_period_id, p_period_id, p_user_id);

-- Step 3: Verify the result
SELECT COUNT(*) FROM gl_verify_balances(p_tenant_id, p_period_id);
-- Expected: 0 rows (no discrepancies)

COMMIT;

Usage Contexts:

This function is a repair utility, not a routine operation. It is intended for use when the gl_account_balances cache has become corrupted or inconsistent due to system failure, data migration issues, or manual database intervention. Under normal operation, the incremental trigger (trg_update_balances) maintains the cache correctly and this function should not be needed.

Situations requiring this function include:

  • Recovery from partial transaction failure that left balance rows inconsistent
  • Post-migration balance reconciliation after bulk data import
  • Balance corruption investigation and repair
  • Testing and development environment reset

The function should only be invoked by administrators with appropriate database privileges. All invocations should be logged in the system administration audit trail outside the database.

5. Error Code Reference​

All GL Engine trigger errors use PostgreSQL ERRCODE = 'P0001' (RAISE EXCEPTION) with a structured message prefix GL_NNN. Application layers should parse the error message for the GL code to provide user-facing messages and structured error responses.

CodeMessage TemplateTriggerSeverity
GL_001Double-entry violation for journal entry %. Debits (%) != Credits (%)trg_enforce_double_entryCRITICAL
GL_010Period not found or not in OPEN state (current state: %)trg_prevent_closed_period_entry, trg_check_period_on_postERROR
GL_013Invalid fiscal period state transition from % to %trg_period_state_machineERROR
GL_020Cannot post journal line to account % (status: %)trg_prevent_inactive_account_postingERROR
GL_022Account not found (id: %, tenant: %)trg_prevent_inactive_account_postingCRITICAL
GL_030Cannot modify a POSTED journal entry (id: %)trg_prevent_posted_modificationERROR
GL_031Cannot modify a REVERSED journal entry (id: %)trg_prevent_posted_modificationERROR
GL_060The audit log is immutable. Record (id: %) cannot be modified or deletedtrg_prevent_audit_mutationCRITICAL

Error Severity Classification​

SeverityMeaningApplication Response
CRITICALIndicates a system or data integrity problem that should not occur in normal operationLog to error monitoring, escalate to engineering team
ERRORIndicates a user or application-layer validation failure that is expected during normal operationReturn structured validation error to the user/API caller

Application Layer Error Handling Pattern​

# Python example — parsing GL error codes from PostgreSQL exceptions
import psycopg2
import re

GL_ERROR_PATTERN = re.compile(r'^(GL_\d{3}):', re.MULTILINE)

def parse_gl_error(exc: psycopg2.Error) -> tuple[str | None, str]:
    """Extract GL error code and message from a PostgreSQL exception."""
    message = str(exc.pgerror or exc.diag.message_primary or '')
    match = GL_ERROR_PATTERN.search(message)
    gl_code = match.group(1) if match else None
    return gl_code, message

# Usage
try:
    cursor.execute("UPDATE gl_journal_entries SET description = 'x' WHERE id = %s", [entry_id])
except psycopg2.Error as e:
    gl_code, message = parse_gl_error(e)
    if gl_code == 'GL_030':
        raise ValidationError("This journal entry has been posted and cannot be modified.")
    elif gl_code == 'GL_031':
        raise ValidationError("This journal entry has been reversed and cannot be modified.")
    else:
        raise  # Re-raise unexpected errors

6. Trigger Execution Order​

PostgreSQL fires multiple triggers on a single DML statement in alphabetical order within the same timing (BEFORE/AFTER) and level (ROW/STATEMENT). The following sections document the effective execution order for the four primary GL operations.

6.1 Insert Journal Entry (DRAFT Status)​

Operation: INSERT INTO gl_journal_entries (..., status='DRAFT', ...)

OrderTriggerFires?Notes
1trg_prevent_closed_period_entryNOWHEN condition status='POSTED' not met
2trg_updated_at_entriesNOBEFORE UPDATE only
3trg_audit_entriesYESAFTER INSERT — captures the new DRAFT entry

Result: Entry created in DRAFT state with an audit record.

6.2 Insert Journal Lines​

Operation: INSERT INTO gl_journal_lines (...) VALUES (...), (...), ...

For each row (BEFORE, ROW level):

OrderTriggerFires?Notes
1trg_prevent_inactive_account_postingYESValidates each line's account status

After all rows (AFTER, ROW level):

OrderTriggerFires?Notes
2trg_update_balancesYES (per row)Skips if parent entry is DRAFT
3trg_audit_linesYES (per row)Captures INSERT audit record

After the statement completes (AFTER, STATEMENT level):

OrderTriggerFires?Notes
4trg_enforce_double_entryYESValidates SUM(debits) = SUM(credits) for each affected journal entry

Result: Lines created with account validation, balance cache updated (if parent POSTED), audit records written, double-entry invariant verified.

6.3 Post Entry (DRAFT to POSTED Transition)​

Operation: UPDATE gl_journal_entries SET status='POSTED' WHERE id=... AND status='DRAFT'

BEFORE ROW level (in alphabetical order):

OrderTriggerFires?Notes
1trg_check_period_on_postYESWHEN condition NEW.status='POSTED' AND OLD.status='DRAFT' met
2trg_prevent_posted_modificationYESOLD.status is DRAFT — no violation, passes through
3trg_updated_at_entriesYESSets updated_at = NOW()

AFTER ROW level:

OrderTriggerFires?Notes
4trg_audit_entriesYESaction = 'STATUS_CHANGE', captures DRAFT→POSTED transition

Result: Entry status set to POSTED (if period is OPEN), updated_at refreshed, STATUS_CHANGE audit record written.

Note on Balance Update During Post: The trg_update_balances trigger fires on gl_journal_lines INSERT, not on gl_journal_entries UPDATE. When an entry transitions from DRAFT to POSTED, a supplementary process must be invoked by the application layer to trigger balance updates for lines that were inserted while the entry was DRAFT. This is typically handled by a post-status-change event handler that re-inserts or touch-updates the journal lines. The specific implementation pattern is defined in the application service layer documentation.

6.4 Close Period (OPEN to CLOSED Transition)​

Operation: UPDATE gl_fiscal_periods SET state='CLOSED' WHERE id=... AND state='OPEN'

BEFORE ROW level (in alphabetical order):

OrderTriggerFires?Notes
1trg_period_state_machineYESValidates OPEN→CLOSED transition, sets closed_at
2trg_updated_at_periodsYESSets updated_at = NOW()

AFTER ROW level:

OrderTriggerFires?Notes
3trg_audit_periodsYESaction = 'STATUS_CHANGE', captures OPEN→CLOSED transition

Result: Period state set to CLOSED, closed_at timestamp set, updated_at refreshed, STATUS_CHANGE audit record written.

7. Session Variable Reference​

The GL Engine business logic layer reads and writes the following PostgreSQL session configuration variables. These must be set by the application layer at the start of each authenticated database session.

VariableTypePurposeSet ByRead By
app.current_user_idUUID (as TEXT)Identifies the authenticated user for audit log captureApplication layerfn_audit_log
app.current_tenantUUID (as TEXT)Row-Level Security tenant isolation (see CFS-DB-003)Application layerRLS policies

Setting Session Variables​

-- Set at session start / transaction start
SELECT set_config('app.current_user_id', '550e8400-e29b-41d4-a716-446655440000', TRUE);
SELECT set_config('app.current_tenant',  '6ba7b810-9dad-11d1-80b4-00c04fd430c8', TRUE);

The third argument (TRUE) makes the setting local to the current transaction. This is the preferred approach in connection pool environments (PgBouncer, pgpool-II) where connections are reused across multiple application requests. Setting TRUE ensures that session variables do not leak between transactions.

Handling Missing Session Variables​

The audit function (fn_audit_log) uses an exception handler to tolerate missing app.current_user_id:

BEGIN
    v_user_id := current_setting('app.current_user_id')::UUID;
EXCEPTION WHEN OTHERS THEN
    v_user_id := NULL;
END;

A NULL user_id in an audit record indicates that the operation was performed without an application user context — for example, during database migrations, scheduled maintenance jobs, or administrative SQL sessions. These records are still captured; the NULL user_id distinguishes them from user-initiated operations.

8. Testing & Validation​

8.1 Trigger Test Matrix​

The following test scenarios must pass in the CI/CD test suite for the GL Engine:

Test CaseExpected OutcomeError Code
Insert balanced journal lines (debit=credit)Success—
Insert unbalanced journal lines (debit != credit)RollbackGL_001
Insert journal line for INACTIVE accountRollbackGL_020
Insert journal line for non-existent accountRollbackGL_022
Insert POSTED entry against OPEN periodSuccess—
Insert POSTED entry against CLOSED periodRollbackGL_010
Insert DRAFT entry against CLOSED periodSuccess—
Update DRAFT entry to POSTED against OPEN periodSuccess—
Update DRAFT entry to POSTED against CLOSED periodRollbackGL_010
Update POSTED entry metadata fieldRollbackGL_030
Update POSTED entry status to REVERSEDSuccess—
Update POSTED entry status to DRAFTRollbackGL_030
Update REVERSED entry any fieldRollbackGL_031
Transition period FUTURE→OPENSuccess—
Transition period OPEN→CLOSEDSuccess—
Transition period CLOSED→OPEN (reverse)RollbackGL_013
Transition period OPEN→LOCKED (skip step)RollbackGL_013
Update audit log recordRollbackGL_060
Delete audit log recordRollbackGL_060

8.2 Balance Verification Procedure​

After any significant data operation or migration, run the following verification sequence:

-- 1. Verify balances are consistent (expect 0 rows for a clean dataset)
SELECT COUNT(*) AS discrepancy_count
FROM gl_verify_balances(p_tenant_id := '<tenant-uuid>', p_period_id := '<period-uuid>');

-- 2. If discrepancies exist, inspect them
SELECT * FROM gl_verify_balances(
    p_tenant_id := '<tenant-uuid>',
    p_period_id := '<period-uuid>'
)
ORDER BY account_number;

-- 3. If repair is needed, execute the recovery sequence within a transaction
BEGIN;
SELECT gl_recompute_balances('<tenant-uuid>', '<period-uuid>', '<admin-user-uuid>');
SELECT gl_carry_forward_balances('<tenant-uuid>', '<prior-period-uuid>', '<period-uuid>', '<admin-user-uuid>');
SELECT COUNT(*) FROM gl_verify_balances('<tenant-uuid>', '<period-uuid>'); -- Must be 0
COMMIT;

8.3 Trigger Dependency Verification​

Verify all triggers are installed and active:

SELECT
    trigger_name,
    event_manipulation,
    event_object_table,
    action_timing,
    action_orientation,
    action_condition
FROM information_schema.triggers
WHERE trigger_schema = 'public'
  AND trigger_name LIKE 'trg_%'
ORDER BY event_object_table, action_timing, trigger_name;

Expected output: 20 rows (10 named triggers; the audit family and timestamp family expand to 6 and 3 individual trigger registrations respectively, plus trg_enforce_double_entry, trg_prevent_posted_modification, trg_prevent_closed_period_entry, trg_check_period_on_post, trg_update_balances, trg_prevent_audit_mutation, trg_period_state_machine, trg_prevent_inactive_account_posting).

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

This document is part of the CODITECT Financial Suite technical documentation series. Next document: CFS-DB-005 — Migration History & Schema Version Control