go-luca

Description

Movement-based double-entry bookkeeping database schema

Tables

Name Columns Comment Type
accounts 11 Chart of accounts. Each account has a hierarchical path (Type:Product:AccountID:Address) and belongs to one of five fundamental types: Asset, Liability, Equity, Income, Expense. Amounts are stored as integers at the precision defined by exponent (e.g. -2 for pence).
 table
balances_live 5 Pre-computed end-of-day balance snapshots. Updated transactionally when movements are recorded via RecordMovementWithProjections. Avoids expensive SUM queries for frequently accessed balances.
 table
movements 12 Core transaction records. Each movement transfers an integer amount from one account to another. Movements with the same batch_id form a linked transaction (compound entry). Inspired by TigerBeetle's transfer model with code, ledger, and pending_id fields.
 table

Relations

erDiagram

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

"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"
}
"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"
}
"movements" {
  INTEGER amount "Transfer amount in smallest currency unit (integer at account exponent)"
  INTEGER batch_id "Groups related movements into a single atomic transaction"
  INTEGER code "Movement category: 0=normal, 1=interest accrual (TigerBeetle-inspired)"
  TEXT description "Human-readable description of the movement"
  INTEGER from_account_id "Source account (references accounts.id)"
  INTEGER id "Auto-incrementing primary key"
  TEXT knowledge_time "When the system recorded this movement (knowledge date)"
  INTEGER ledger "Partition identifier for multi-ledger setups (TigerBeetle-inspired)"
  INTEGER pending_id "Two-phase commit: references pending movement to post/void (0=N/A)"
  INTEGER to_account_id "Destination account (references accounts.id)"
  INTEGER user_data_64 "Arbitrary external reference for application use"
  TEXT value_time "When the movement economically occurred (value date)"
}

Generated by tbls