balances_live

Description

Pre-computed end-of-day balance snapshots. Updated transactionally when movements are recorded via RecordMovementWithProjections. Avoids expensive SUM queries for frequently accessed balances.

Table Definition

CREATE TABLE balances_live (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    account_id INTEGER NOT NULL,
    balance_date TEXT NOT NULL,
    balance INTEGER NOT NULL,
    updated_at TEXT DEFAULT (datetime('now'))
)

Columns

Name	Type	Default	Nullable	Parents	Comment
account_id	INTEGER		false	accounts	Account this balance belongs to (references accounts.id)
balance	INTEGER		false		End-of-day balance in smallest currency unit
balance_date	TEXT		false		Date of the balance snapshot (start of day)
id	INTEGER		true		Auto-incrementing primary key
updated_at	TEXT	datetime('now')	true		When this balance was last recomputed

Constraints

Name	Type	Definition
id	PRIMARY KEY	PRIMARY KEY (id)

Indexes

Name	Definition
idx_balances_live_unique	CREATE UNIQUE INDEX idx_balances_live_unique ON balances_live(account_id, balance_date)

Relations

erDiagram

"balances_live" }o--|| "accounts" : "balances_live.account_id -> accounts.id"

"balances_live" {
  INTEGER account_id "Account this balance belongs to (references accounts.id)"
  INTEGER balance "End-of-day balance in smallest currency unit"
  TEXT balance_date "Date of the balance snapshot (start of day)"
  INTEGER id "Auto-incrementing primary key"
  TEXT updated_at "When this balance was last recomputed"
}
"accounts" {
  TEXT account_id "Specific account identifier within the product"
  TEXT account_type "One of: Asset, Liability, Equity, Income, Expense"
  TEXT address "Sub-address within the account (e.g. branch). 'Pending' marks pending accounts"
  REAL annual_interest_rate "Annual interest rate as a decimal (0.045 = 4.5%)"
  TEXT created_at "Timestamp when the account was created"
  TEXT currency "ISO 4217 currency code (e.g. GBP, USD)"
  INTEGER exponent "Decimal exponent for amount precision (-2 = pence, -5 = high precision)"
  TEXT full_path "Hierarchical account path, e.g. Asset:Bank:Current:Main"
  INTEGER id "Auto-incrementing primary key"
  INTEGER is_pending "True if this is a pending/suspense account"
  TEXT product "Product category within the account type"
}

Generated by tbls