Account Balances & Daily Balance Tracking

This guide explains how PinkApple tracks and maintains GL account balances, the daily balance mechanism, and how to query balance data.

Overview

PinkApple maintains account balances through a daily balance table (daily_coa_balance) that records the running balance of every COA account per business unit, per day. This provides:

• Point-in-time balance queries for any historical date
• Efficient period-end reporting without full journal re-aggregation
• Balance integrity through automated reconciliation

Balance Architecture

Daily COA Balance

Every time a GL journal line is posted, the system updates (or inserts) a row in daily_coa_balance:

• COA ID: The chart of account entry
• Business Unit ID: The business unit context
• Balance Date: The posting date
• Debit Balance: Cumulative debit total as of that date
• Credit Balance: Cumulative credit total as of that date
• Net Balance: Debit − Credit (or Credit − Debit depending on normal balance direction)

Sub-Ledger Balance

Similarly, daily_subledger_balance tracks balances at the sub-ledger level for detailed control account reconciliation.

How Balances Update

When gl_post_ultimate processes a journal entry:

1. Each journal line specifies a COA, amount, and debit/credit direction
2. The daily balance row for that COA + BU + date is updated:
   • If a row exists for that date → increment the debit or credit
   • If no row exists → insert a new row carrying forward the prior day's balance
3. The balance is immediately available for queries

TIP

Balances are cumulative — each day's row contains the total balance from inception, not just the day's movement.

Querying Balances

Trial Balance Report

The Trial Balance report aggregates daily balances across all COAs:

• Filter by date range, business unit, currency
• Shows opening balance, period movement, and closing balance
• Supports comparative periods

General Ledger Report

The General Ledger report shows transaction-level detail:

• Individual journal entries affecting each account
• Running balance after each entry
• Filterable by COA, date range, business unit, and more

Balance Sheet & Income Statement

These financial statements derive from daily balances:

• Balance Sheet: Point-in-time balances for asset, liability, equity accounts
• Income Statement: Period movement (revenue − expenses) between two dates

Balance Integrity

SL ↔ COA Reconciliation

At period close, the system verifies that sub-ledger balances sum to their control COA:

SUM(daily_subledger_balance for all SLs under COA X) = daily_coa_balance for COA X

A mismatch indicates posting errors or direct adjustments that bypassed the sub-ledger.

Opening Balance Carry-Forward

When a new fiscal year begins:

• Balance Sheet accounts carry their closing balance as the opening balance
• Income Statement accounts reset to zero (P&L is rolled into Retained Earnings via year-end close)

Balance Views

PinkApple provides several balance perspectives:

View	Description	Use Case
COA Balance	Balance per chart of account entry	Financial statements, TB
Sub-Ledger Balance	Balance per sub-ledger entry	Detailed account analysis
BU Balance	Aggregate balance per business unit	BU-level P&L
Consolidated	All BUs aggregated (post-elimination)	Group reporting

Foundation Account Categories

Balances roll up through the account hierarchy:

1. Sub-Ledger → lowest level, individual entities
2. COA → chart of account entry (primary posting level)
3. Account Header → grouping level
4. Major Account → major classification
5. Foundation Account → top-level category (ASSET, LIABILITY, EQUITY, REVENUE, EXPENSE)

The foundation_account_details.account_sub_cat further classifies accounts (e.g. CURRENT_ASSET, FIXED_ASSET, PROFIT_AND_LOSS) for reporting purposes.

Performance Considerations

• Daily balance rows are indexed by COA + BU + date for fast lookups
• Period reports query a bounded date range, not all-time
• The balance table is authoritative — there is no need to re-aggregate from journal lines
• For large organisations, consider archiving daily balance data for closed fiscal years