The ledger

Books, accounts, transactions and entries, edit history, and several devices at once.

In one line: a transaction is a header the user sees, plus one or two entries that carry the money; an account’s balance is the sum of its entries, and nothing else.

users ──< book_members >── books ─┬─< accounts ─────────< entries >──── transactions >── categories
          (owner/editor/viewer)    ├─< categories                      (header: type, date, time, payee, note)
                                   ├─< contacts
                                   ├─< budgets
                                   └─< transactions

• transactions are what the user sees: type, date, time of day (in the book’s timezone, like the date; 00:00 for those saved before 0.2.0), category, payee, note.
• entries are the money: a signed amount in minor units, one row per account touched.

• Opening balances can’t have a category. A transfer can have only a transfer category (category_kind error otherwise), and only a transfer can have one; a category used by transactions can’t switch to or from the transfer kind. Transfer categories are labels: spending, budgets and AI summaries all select by transaction type, so they never count.
• An account’s balance is SUM(entries.amount) (account_balances view). There’s no balance column, so it can’t drift from history, and two devices adding at once can’t race.
• Liabilities are negative. A credit card with $450 owed has balance -45000. Buying on the card is an expense on the card; paying it off is a transfer from checking. A loan starts with a negative opening balance. A book’s net worth is the sum of its balances.
• Books can’t reference each other. Every row carries book_id, and the foreign keys include it, so the database rejects a transfer between books or a category from another book. Moving money from a personal book to a household book is an expense in one and income in the other.
• Deleting a book deletes everything in it, its audit-log rows included. An account with history can only be archived. A category with transactions can’t be deleted either: the API archives it instead and says so (as the old app did). A category with sub-categories can’t be deleted (has_subcategories); move or delete them first (default).
• Net worth toggle: accounts.in_net_worth (on by default) leaves an account out of book_net_worth, for money held for someone else. It still shows (greyed out) with its balance, and its spending and income still count.
• Details by kind (all optional): a note on any account; a credit card’s limit, annual fee, statement day and payment due day; a loan’s monthly installment, payment day and a one-off installment fee; a yearly interest rate on savings, credit cards, loans and money lent. The database refuses a detail on a kind it doesn’t belong to. The interest rate is for reference: interest is a transaction like any other (income on savings, an expense on a loan), never a change to the balance on its own.
• Monthly bills (credit cards and loans; worked out from the transactions when asked, never stored, GET .../accounts/{id}/bills):
  • A credit card’s bill is its statement balance, as a bank counts it: what the card owed at the end of its last statement date (that day’s transactions included; a month without that day uses its last day), earlier unpaid amounts included, minus all the money in since (payments and refunds). It’s due on the first payment due day after the statement date.
  • Next to it, a card shows this statement so far: the open period up to the next statement date, with only what’s new in it (spending up to that date, the installments and installment fees it will bill, less any payment beyond the last statement), and when it will be due. Once the last statement is paid off, or had nothing to pay, it closes and only this one shows.
  • Installments on a card: a purchase paid in installments stays one expense on the card for its full amount, so the balance and net worth say what’s really owed. Its plan (installment_plans: 2 to 60 months, the conversion date, the first statement, which by default is the one the conversion date falls in) makes each statement bill one installment, the purchase over the months in whole minor units with the last taking what’s left: the card’s bill leaves out the part no statement has billed yet. The conversion fee goes all on the first installment’s bill, or split into equal parts on each, as the user chooses; a part no Pay has recorded stays on the bill. End early bills everything left on the next statement. The part not billed yet counts toward the card’s limit unless the plan says otherwise. Only an expense on one credit card that has a statement day can have a plan; moving the purchase off its card, or making it something other than an expense, removes the plan.
  • A loan’s bill is its monthly installment, due on its next payment day; payments since the previous payment day count. The installment fee, if any, is added once, to the first payment. With an interest rate, the month’s interest (what was owed before this month’s payments, times the rate, over 12) is the part of the installment recorded as an expense on the loan, so the loan goes down by the principal only. One month at a time: a missed installment isn’t carried over.
  • Pay, on every bill and statement and always there, saves the payment as ordinary transactions: a transfer from the account the user picks (same currency; the one used last time is offered first) for the amount left or any other amount (on a card it pays the last statement first, then brings the open one down); for a loan, also the fee and the month’s interest as expenses on the loan; for a card, the installment fees due as expenses on the card. Their ids don’t change on a retry, and the fee’s and each month’s interest’s are fixed, so nothing is charged twice.
• Running balance on an account’s page: a window SUM(amount) over its entries ordered by date, time, then created_at, computed per request.
• The shape rules (a transfer has two entries, an expense is negative) are checked by the database when the transaction commits, so the app writes the header and entries together in any order.
• Why entries rather than from_account/to_account columns: every balance is the same SUM, whatever the type, and “money doesn’t appear from nowhere” is a check on one transaction’s entries.

Examples: coffee 45,000 ₫ from Cash is one entry of -45000 on Cash. Checking → Savings 1,000,000 ₫ is -1000000 on Checking and +1000000 on Savings. A credit card that starts owing $450 is an opening balance of -45000.

Transaction edits and history

• created_at, modified_at and version are on every transaction; the database sets the last two on every edit.
• An edit that changes only amounts or accounts is a version like any other. Those live in entries, which the version trigger on the header can’t see, so the API also sets modified_at then, and the trigger counts that as a change. A save that changes nothing adds no version. The entries are rewritten only when they change, so a header edit adds no entry rows to the audit log.
• transaction_history: one full snapshot per saved version (date, type, category, payee, note, entries) with who saved it. The edit history screen reads it: “created”, “edited” for each save, “deleted” at the end. It stays after a transaction is deleted, and goes only with its book. Members can read it; nobody can change it.
• audit_log is separate: a raw record of every change to books, members, people, accounts, categories, transactions, entries, budgets and exchange rates, for security investigations. Only owners can read it, nobody can change it, and it goes with its book.
• Editing rewrites the transaction. If you ever want accounting-style immutability instead, an edit would add a reversing transaction.

Several devices at once