Account Balance Documentation¶

Overview¶

The Account Balance module (account.balance) maintains aggregated balance summaries for all accounts in the chart of accounts. This is a cache/summary table that stores pre-calculated debit, credit, and currency amount totals for each account and tracking category combination, improving performance for financial reports and account inquiries.

Model Information¶

Model Name: account.balance Display Name: Account Balance Key Fields: None (Summary table)

Features¶

❌ Audit logging disabled (cache table)
❌ Multi-company support (inherits from account)
❌ Full-text content search
✅ Aggregated summary data from account.move.line
✅ Supports tracking category analysis (Track-1)

Understanding the Account Balance Model¶

What is Account Balance?¶

The account.balance model is a cache/summary table that stores pre-calculated balance totals for each account. Instead of summing up journal entry lines every time you need an account balance, the system maintains this table for fast lookups.

Why Use a Balance Cache?¶

Performance Optimization: - Fast balance lookups without scanning thousands of journal entries - Instant financial report generation - Quick account inquiry responses

Structure:

# Each record represents one unique combination of:
account_id + track_id = balance summary

When Balances are Updated¶

The balance table is typically updated: - After posting journal entries - During period-end closing procedures - When running the update_balances() method manually - Via scheduled batch jobs

Key Fields Reference¶

Core Balance Fields¶

Field	Type	Required	Description
`account_id`	Many2One	✅	Link to account.account - the GL account
`track_id`	Many2One	❌	Link to account.track.categ - tracking category (Track-1)
`debit`	Decimal	✅	Total debit amount for this account/track combination
`credit`	Decimal	✅	Total credit amount for this account/track combination
`amount_cur`	Decimal	❌	Total amount in foreign currency (if applicable)

Understanding the Fields¶

account_id: - References the Chart of Accounts entry - Required - every balance must belong to an account - Cascading delete - if account deleted, balances are removed

track_id: - Optional tracking dimension (department, project, cost center, etc.) - Allows balance analysis by tracking category - NULL means no tracking category applied

debit & credit: - Running totals of all debit/credit from journal entry lines - Always required fields (default to 0) - Used to calculate net balance: debit - credit

amount_cur: - For multi-currency accounts - Stores the foreign currency amount - Positive for debit, negative for credit - NULL for single-currency accounts

API Methods¶

1. Update Balances¶

Method: update_balances(context={})

Recalculates all account balances by aggregating data from account.move.line table. This method completely rebuilds the balance table.

Parameters: - context (dict): Optional context dictionary (currently unused)

Behavior: 1. Queries all journal entry lines grouped by account and tracking category 2. Sums debit, credit, and currency amounts for each group 3. Deletes all existing balance records 4. Inserts new calculated balance records

Returns: None

Example:

# Recalculate all account balances
get_model("account.balance").update_balances()

SQL Operations Performed:

-- 1. Aggregate from journal entry lines
SELECT
    account_id,
    track_id,
    SUM(debit) AS total_debit,
    SUM(credit) AS total_credit,
    SUM(amount_cur * SIGN(debit-credit)) AS total_amount_cur
FROM account_move_line
GROUP BY account_id, track_id

-- 2. Clear existing balances
DELETE FROM account_balance

-- 3. Insert new balances
INSERT INTO account_balance
    (account_id, track_id, debit, credit, amount_cur)
VALUES (...)

When to Use: - After bulk journal entry imports - During month-end/year-end closing - When balance discrepancies are detected - After database maintenance or corrections

Performance Note: - This is an expensive operation on large databases - Should be run during off-peak hours for production systems - Consider running via scheduled job instead of user-triggered action

Search Functions¶

Search by Account¶

# Get balance for specific account
balances = get_model("account.balance").search_browse([
    ["account_id", "=", account_id]
])
for bal in balances:
    print(f"Account: {bal.account_id.name}")
    print(f"Debit: {bal.debit}, Credit: {bal.credit}")
    print(f"Balance: {bal.debit - bal.credit}")

Search by Account and Tracking¶

# Get balance for account with specific tracking category
balances = get_model("account.balance").search_browse([
    ["account_id", "=", account_id],
    ["track_id", "=", track_id]
])

Search Accounts with Balances¶

# Find all accounts with non-zero balances
balances = get_model("account.balance").search_browse([
    "|",
    ["debit", "!=", 0],
    ["credit", "!=", 0]
])

Get All Balances for Account Type¶

# Get balances for all asset accounts
balances = get_model("account.balance").search_browse([
    ["account_id.type", "=", "asset"]
])

Computed Fields Functions¶

This model does not have computed fields. All values are stored directly in the database for performance.

Best Practices¶

1. Use Balances for Reporting¶

# Bad: Summing journal lines every time (slow)
lines = get_model("account.move.line").search_browse([
    ["account_id", "=", account_id]
])
total_debit = sum(line.debit for line in lines)
total_credit = sum(line.credit for line in lines)

# Good: Read from balance cache (fast)
balances = get_model("account.balance").search_browse([
    ["account_id", "=", account_id]
])
total_debit = sum(bal.debit for bal in balances)
total_credit = sum(bal.credit for bal in balances)

2. Ensure Balances are Current¶

# Before generating critical financial reports
# Ensure balances are up-to-date
get_model("account.balance").update_balances()

# Then run your report
generate_trial_balance_report()

3. Handle Multiple Tracking Categories¶

# When account has multiple tracking categories
# Calculate net balance across all tracks
balances = get_model("account.balance").search_browse([
    ["account_id", "=", account_id]
])

net_debit = sum(bal.debit for bal in balances)
net_credit = sum(bal.credit for bal in balances)
net_balance = net_debit - net_credit

Database Constraints¶

Foreign Key Constraints¶

-- Account reference with cascade delete
FOREIGN KEY (account_id)
    REFERENCES account_account(id)
    ON DELETE CASCADE

-- Tracking category reference
FOREIGN KEY (track_id)
    REFERENCES account_track_categ(id)

Implications: - Deleting an account automatically removes its balance records - Deleting a tracking category sets track_id to NULL or fails (depending on config)

Model	Relationship	Description
`account.account`	Many2One	The GL account this balance belongs to
`account.move.line`	Source Data	Journal entry lines that feed into balances
`account.track.categ`	Many2One	Tracking category for dimensional analysis
`account.move`	Indirect	Journal entries containing the lines

Common Use Cases¶

Use Case 1: Generate Trial Balance Report¶

# Get all account balances for trial balance
balances = get_model("account.balance").search_browse([])

# Group by account
account_totals = {}
for bal in balances:
    acc_id = bal.account_id.id
    if acc_id not in account_totals:
        account_totals[acc_id] = {
            "name": bal.account_id.name,
            "code": bal.account_id.code,
            "debit": 0,
            "credit": 0
        }
    account_totals[acc_id]["debit"] += bal.debit or 0
    account_totals[acc_id]["credit"] += bal.credit or 0

# Print trial balance
print("TRIAL BALANCE")
print("=" * 60)
for acc_id, totals in sorted(account_totals.items()):
    print(f"{totals['code']} {totals['name']:30} "
          f"Dr: {totals['debit']:12.2f} Cr: {totals['credit']:12.2f}")

Use Case 2: Check Account Balance by Tracking Category¶

# Analyze expense account by department (tracking category)
account_id = get_model("account.account").search([
    ["code", "=", "5100"]  # Operating Expenses
])[0]

balances = get_model("account.balance").search_browse([
    ["account_id", "=", account_id]
])

print(f"Expense Analysis for Account {account_id}")
print("=" * 60)
for bal in balances:
    track_name = bal.track_id.name if bal.track_id else "Unallocated"
    net_amount = bal.debit - bal.credit
    print(f"{track_name:30} {net_amount:12.2f}")

Use Case 3: Verify Balance Accuracy¶

# Compare cached balance with actual journal line totals
account_id = 123

# Get cached balance
cached = get_model("account.balance").search_browse([
    ["account_id", "=", account_id]
])
cached_debit = sum(b.debit for b in cached)
cached_credit = sum(b.credit for b in cached)

# Calculate actual from journal lines
lines = get_model("account.move.line").search_browse([
    ["account_id", "=", account_id]
])
actual_debit = sum(l.debit for l in lines)
actual_credit = sum(l.credit for l in lines)

# Compare
if cached_debit != actual_debit or cached_credit != actual_credit:
    print("⚠ Balance mismatch detected! Updating...")
    get_model("account.balance").update_balances()
    print("✓ Balances updated")
else:
    print("✓ Balances are accurate")

Use Case 4: Get Multi-Currency Account Balance¶

# For accounts with foreign currency transactions
account_id = 456  # Foreign currency account

balances = get_model("account.balance").search_browse([
    ["account_id", "=", account_id]
])

for bal in balances:
    base_balance = bal.debit - bal.credit
    currency_balance = bal.amount_cur or 0

    print(f"Base Currency Balance: {base_balance:12.2f}")
    if currency_balance:
        print(f"Foreign Currency Balance: {currency_balance:12.2f}")

Performance Tips¶

1. Update Balances During Off-Peak Hours¶

Schedule update_balances() as a nightly batch job
Avoid running during business hours on production
Consider incremental updates for very large databases

2. Use Indexes for Common Queries¶

-- Recommended indexes
CREATE INDEX idx_account_balance_account
    ON account_balance(account_id);

CREATE INDEX idx_account_balance_track
    ON account_balance(track_id);

CREATE INDEX idx_account_balance_combined
    ON account_balance(account_id, track_id);

3. Cache Balances in Application Layer¶

# For frequently accessed balances, cache in memory
balance_cache = {}

def get_account_balance(account_id):
    if account_id not in balance_cache:
        balances = get_model("account.balance").search_browse([
            ["account_id", "=", account_id]
        ])
        total_debit = sum(b.debit for b in balances)
        total_credit = sum(b.credit for b in balances)
        balance_cache[account_id] = total_debit - total_credit
    return balance_cache[account_id]

Troubleshooting¶

"Balance doesn't match journal entries"¶

Cause: Balance cache is out of sync with journal entry lines Solution: Run update_balances() to recalculate from source data

get_model("account.balance").update_balances()

"Missing balance records for some accounts"¶

Cause: New accounts created or balances not initialized Solution: Update balances to include all accounts with transactions

get_model("account.balance").update_balances()

"Performance degradation when running update_balances"¶

Cause: Large volume of journal entry lines Solution: - Run during off-peak hours - Add database indexes on account_move_line table - Consider archiving old journal entries - Monitor database performance during update

"Duplicate balance records for same account/track combination"¶

Cause: Race condition or incomplete update process Solution:

# Clear duplicates and rebuild
db = get_connection()
db.execute("DELETE FROM account_balance")
get_model("account.balance").update_balances()

Testing Examples¶

Unit Test: Balance Calculation Accuracy¶

def test_balance_accuracy():
    # Create test account
    account_id = get_model("account.account").create({
        "code": "TEST001",
        "name": "Test Account",
        "type": "asset"
    })

    # Create journal entries
    move_id = get_model("account.move").create({
        "journal_id": journal_id,
        "date": "2025-01-15",
        "lines": [
            ("create", {
                "account_id": account_id,
                "debit": 1000,
                "credit": 0
            }),
            ("create", {
                "account_id": account_id,
                "debit": 0,
                "credit": 500
            })
        ]
    })

    # Update balances
    get_model("account.balance").update_balances()

    # Verify balance
    balances = get_model("account.balance").search_browse([
        ["account_id", "=", account_id]
    ])

    assert len(balances) == 1
    assert balances[0].debit == 1000
    assert balances[0].credit == 500

    # Cleanup
    get_model("account.account").delete([account_id])

Security Considerations¶

Permission Model¶

Balance records inherit security from parent account.account
Read access requires accounting user role
Update operations should be restricted to accounting administrators

Data Access¶

Balances are company-specific (via account.company_id)
Users can only see balances for accounts they have access to
Tracking categories may have additional access controls

Audit Considerations¶

This model does not have audit logging enabled (cache table)
Audit trail is maintained at account.move.line level
Any balance corrections should be done via journal entries, not direct balance updates

Integration Points¶

Internal Modules¶

account.account: Source accounts for balance calculation
account.move.line: Source data for all balances
account.track.categ: Dimensional analysis support
Financial Reports: Primary consumer of balance data
Dashboard Widgets: Real-time balance displays

Update Triggers¶

The balance table should be updated when: - Journal entries are posted (account.move.post()) - Journal entries are unposted or deleted - Period-end closing procedures run - Bulk import operations complete

Version History¶

Last Updated: 2025-12-16 Model Version: account_balance.py Framework: Netforce

Additional Resources¶

Account Model Documentation: account.account
Journal Entry Documentation: account.move
Journal Line Documentation: account.move.line
Tracking Category Documentation: account.track.categ

Support & Feedback¶

For issues or questions about account balances: 1. Verify balances are current by running update_balances() 2. Check journal entry lines for source data accuracy 3. Review database indexes for performance issues 4. Consult financial reports to see balance usage

This documentation is generated for developer onboarding and reference purposes.