Ledger

Author: Rodda John Status: Draft

Overview

There are two predominant ways to store information changing over time:

• A set of "single entry" journals
• A set of "double entry" journals that zero balance

When writing application code, it's incredibly easy to write "single entry" journal concepts.

Consider the case of an invoice:

• Create an invoice object that has some line items that sum to some balance
• Create a payment object to pay down the invoice

This is a single entry model, where you have a list of things that edit some original state.

Consider the same case implemented in a double entry model:

• Cycle ends
  • Debit: unbilled receivables
  • Credit: revenue
• Invoice issued
  • Debit: AR
  • Credit: revenue
• When a payment is made
  • Credit: AR
  • Debit: clearing account ...

All of these accounts have meaning:

• Balance in unbilling revenue is the amount of revenue that you have "earned" but has not been billed
• Balance in revenue is the amount of revenue you've made
• Balance in AR is the amount of moeny you're owed
• Balance in the clearing account is the money you've collected but has not been distributed to you by the payment processor

In a double entry model, not only can you never be wrong (because every entry must zero balance), there is no state that needs to be constructed from anything that is not represented in the ledger:

• e.g. invoice state
  • If no entries, it hasn't been issued yet
  • If there's an AR entry, it was issued
  • If there's a balance in AR, it's unpaid
  • If there's no balance in AR, it's paid

Overlap with MEP 6

MEP 6 describes how a Moab ERP will function. This MEP describes how an internal ledger may function. Obviously an ERP has an enormous amount of overlap with a ledger, given an ERP is a ledger.

The internal ledger described in this MEP is intended to be a source of truth for any internal state that is dependent on balances. e.g. if an invoice balance is displayed, it should be the result of a balance call. The structure of this internal ledger is entirely controlled by Moab, and is not rendered to a user. Thus, using a single table with copious dimension usage (which is suboptimal UX) is a suitable tradeoff given the efficiencies such an approach yields in querying and internal management.

This internal ledger, via rules, can be translated to a Moab ERP, which in tern can be synced to an external ERP (just as is described in MEP 6).

Internal ledger --> Moab ERP <--> External ERP

To give a brief overview of how these rules may work.

Let's say a customer wants revenue tracked in different GLs by location. Although the internal ledger only has a single Revenue account, this can be filtered on the location dimension to the Moab ERP GLs.

Narrowly construed, this proposal is an alternative way of tracking the source of truth for balance laden transactions. The purpose of MEP 6 is to describe a Moab ERP (that could eventually replace e.g. Quickbooks) and how it is to interact with existing accounting softwares.

Proposal

Moab should build a double entry ledger that forms the source of truth on the state of any object. As much state as possible should be contained within the ledger and derived from it.

If the business wishes to use accounting syncing, they are functionally mapping Moab's ledger concepts to their own, this mapping would be on the account level.

A Ledger Account

A ledger account is the building block of the system.

A ledger account has a type, which is one of:

• State
  • asset (what you own)
  • liability (what you owe)
• Change
  • income (what you've earned)
  • expense (what you've spent)

A ledger account has a balance, which is derived from entries associated with it.

A Ledger Entry

A ledger entry is an update to a ledger account that is balanced.

A ledger entry has multiple lines, each line representing a change to a single account.

Each ledger line should have all fields indexed. The database will store each line and not have a complete "ledger entry" concept.

A ledger entry is considered balanced iff:

sum(changes_to_asset_accounts) - sum(changes_to_liability_accounts) =
sum(changes_to_income_accounts) - sum(changes_to_expense_accounts)

The date on which the entry is recorded on, and the date it is recorded for are written.

Dimensions

Each ledger entry has zero or more dimensions associated with it. A dimension is simply a reference to another object in Moab.

Dimensions:

• branch
• customer
• order_line_item
• asset
• vendor
• invoice
• invoice_line_item

A dimension should always be the most granular object possible, as higher order summaries can be aggregated and derived:

• order should not be a dimension, as order_line_item is much more specific
• klass should not be a dimension, as asset is much more specific

Accounts

Every business in Moab will have the same set of accounts, the dimensions could obviously change as the business uses the product, but the core accounts would not.

These are values.

Some accounts require dimensions to be selected when writing to them.

The accounts every business will have are:

• Asset
  • Rental inventory
    • Requires: asset
  • Sale inventory
    • Requires: asset
  • Parts inventory
    • Requires: asset
  • Cash
    • Liquid assets in a bank
  • Undeposited funds
    • Petty cash (e.g. in a cash drawer)
  • Unbilled receivables
    • Earned but not billed revenue
    • Requires: order_line_item
  • Accounts receivable
    • Money owed to you
    • Requires: order_line_item
  • Clearing account
    • Amount held by a third party that is owed to you (e.g. Stripe)
• Liability
  • Accrued purchase
    • Received but not billed goods
    • Requires: order_line_item
  • Accounts payable
    • Money you owe
    • Requires: invoice
  • Deferred revenue
    • Billed but not earned revenue
    • Requires: order_line_item
  • Deposit liability
    • Money collected as a deposit
  • Tax liability
    • Money collected as a tax payment
  • Warranty clearing
    • Money owed by a vendor
  • Customer balance
    • A customer stored balance account
    • Requires: customer
  • Accumulated depreciation (contra-asset)
    • Sum of all depreciation expenses
  • Refund owed
    • Refund owed to the customer
    • Requires: customer
• Income
  • Rental revenue
    • Revenue from rental
    • Requires: invoice, order_line_item
  • Sale revenue
    • Revenue from sales
    • Requires: invoice, order_line_item
  • Parts revenue
    • Revenue from parts sales
    • Requires: invoice, order_line_item
  • Service revenue
    • Revenue from services
    • Requires: invoice, order_line_item
  • Warranty revenue
    • Revenue where payer is a vendor
    • Requires: order_line_item
• Expense
  • Payment processing fees
    • Paid to a payment processor
  • Refund (contra-income)
    • Amount refunded to customers
  • Bad debt
    • Delinquent balances
  • Repair
    • Costs incurred to repair equipment
  • General expense
    • A misc expense account
  • COGS
    • Cost of goods sold
  • Depreciation
    • Each cycle's depreciation
    • Requires: asset

Asset: debit increases, credit decreases Liability: debit decreases, credit increases Income: credit increases, debit decreases Expense: credit decreases, debit increases

Exposed Services

The only services exposed are ones that sum balances and return all journals:

def get_balance(
    accounts: list[AccountEnum] | None = None,
    branch_ids: list[int] | None = None,
    customer_ids: list[int] | None = None,
    order_line_item_ids: list[int] | None = None,
    asset_ids: list[int] | None = None,
    vendor_ids: list[int] | None = None,
    start_date: date | None = None,
    end_date: date | None = None,
) -> Decimal:
    # accounts: list of accounts to filter by
    # if None, all accounts are included

    # branch_ids: list of branch_ids to filter by
    # if None, all branches are included

    # customer_ids: list of customer_ids to filter by
    # if None, all customers are included

    # order_line_item_ids: list of order_line_item_ids to filter by
    # if None, all order_line_items are included

    # asset_ids: list of asset_ids to filter by
    # if None, all assets are included

    # vendor_ids: list of vendor_ids to filter by
    # if None, all vendors are included

    # filtered within the range [start_date, end_date)
    # If no date then it's the entire range

    # returns the balance of the accounts that match the filters
    ...

def get_balances(
    accounts: list[AccountEnum] | None = None,
    **kwargs
) -> list[AccountEnum]:
    # A utility to allow the fetching of the balances of multiple
    # accounts with the same dimensions easily

    return [get_balance(accounts=[account], **kwargs) for account in accounts]

def get_entries(    
    accounts: list[AccountEnum] | None = None,
    branch_ids: list[int] | None = None,
    customer_ids: list[int] | None = None,
    order_line_item_ids: list[int] | None = None,
    asset_ids: list[int] | None = None,
    vendor_ids: list[int] | None = None,
    start_date: date | None = None,
    end_date: date | None = None,
) -> list[Entry]:
    # Parameters are the same as get_balance
    ...


def waterfall_value(
    value: Decimal,
    accounts: list[AccountEnum],
    **kwargs
) -> list[Decimal]:
    # Distributes the value across the accounts in order
    # until the value is exhausted

    balances = get_balances(accounts=accounts, **kwargs)

    to_return = [Decimal('0') for _ in accounts]

    for i, account in enumerate(accounts):
        to_return[i] = min(balances[i], value)
        value -= to_return[i]

    return to_return

This runs the accounting equation.

Actions & Reporting

Actions

Every action in Moab that affects the assets, liabilities, income, or expenses of the business should be recorded in the ledger.

Every action in Moab should be understood to create journals, and the journals it creates should be standardized.

Every action should require no inputs beyond the dimensions of the journals. If more inputs are needed, a different action is required.

Reporting

Reporting becomes trivial, as its simply a query on a list of journals that match a set of dimensions for some time frame.

e.g. Rate of return on an inventory item, query all journals marked as affecting that asset.

e.g. Customer balance: get the balance of the customer balance account filtered on the relevant customer id.

Examples

All examples will be in the following form

• [some action descriptor]
  • [debit | credit] - [account]
  • [debit | credit] - [account]
  • [0 or more lines]

An Invoice

Invoice Lifecycle

All invoice related ledgers are related to an invoice line item

Invoice Issuance

amount_of_cycle_billing = <some-input>

unbilled_revenue_amount = get_balance(
    [unbilled_revenue],
    invoice_line_item_id=invoice_line_item_id,
)

deferred_revenue_amount = amount_of_cycle_billing - unbilled_revenue_amount

Rental Items

• invoice issued

  • debit - accounts receivable (deferred_revenue_amount)
  • credit - deferred revenue (deferred_revenue_amount)

• invoice issued

  • debit - accounts receivable (unbilled_revenue_amount)
  • credit - unbilled receivables (unbilled_revenue_amount)

Sale Items

accumulated_depreciation = get_balance(
    accumulated_depreciation,
    asset_id=asset_id
)
rental_inventory_value = get_balance(
    rental_inventory_value,
    asset_id=asset_id
)

• inventory transfer
  • debit - accumulated depreciation (accumulated_depreciation)
  • credit - rental inventory (rental_inventory_value)
  • debit - sale inventory (rental_inventory_value + accumulated_depreciation)

sale_inventory_value = get_balance(
    sale_inventory_value,
    asset_id=asset_id
)

• invoice issued for sale

  • credit - sale inventory (sale_inventory_value)
  • debit - cost of goods sold (salve_inventory_value)

• invoice issued for sale

  • credit - revenue (input)
  • debit - accounts receivable (input)

Service & Repair

A business is paying for service and repair itself.

part_balance = get_balance(
    part_inventory_value,
    asset_id=asset_id,
)

• purchases a part
  • credit - parts inventory (part_balance)
  • debit - repair expense (part_balance)

A business is capitalizing the service

part_balance = get_balance(
    part_inventory_value,
    asset_id=asset_id,
)

• purchases a part for capitalization
  • credit - part inventory (part_balance)
  • debit - repair expense (input)
  • debit - sale / rental inventory (input + part_balance)

Warranties

part_balance = get_balance(
    part_inventory_value,
    asset_id=asset_id,
)

• work is done

  • credit - parts inventory (part_balance)
  • debit - cost of goods sold (part_balance)
  • credit - warranty revenue (input)
  • debit - warranty clearing (input)

• warranty pays

  • credit - warranty clearing (input)
  • debit - cash (input)

Liabilities (Security Deposit / Tax Liability)

• invoice issued
  • debit - accounts receivable (input)
  • credit - customer liability account (input)

Payment Handling

Customer Pays an Invoice

• non credit card payment

  • debit - cash (input)
  • credit - accounts receivable (input)

• credit card payment settles

  • debit - clearing account (input)
  • credit - accounts receivable (input)

• Stripe pays you

  • debit - cash (input)
  • debit - payment processing fees (input_1)
  • credit - accounts receivable (input)

Handling a Refund

• refund
  • debit - refund (input)
  • credit - cash (input)

Invoice Edge Cases

Delinquency

accounts_receivable_amount = get_balance(
    [accounts_receivable],
    invoice_line_item_id=invoice_line_item_id,
)

• delinquent
  • debit - accounts receivable (accounts_receivable_amount)
  • credit - bad debt (accounts_receivable_amount)

Credit Note Adjustment

credit_note_amount = <some-input>

deferred_revenue_amount, revenue_amount, accounts_receivable = get_balances(
    [deferred_revenue, revenue, accounts_receivable],
    invoice_line_item=invoice_line_item,
)

invoice_balance = unbilled_revenue + deferred_revenue + revenue

assert credit_note_amount <= invoice_balance

deferred_revenue_proportional_amount = (
    (deferred_revenue_amount / invoice_balance) * credit_note_amount
)

revenue_proportional_amount = (
    (revenue_amount / invoice_balance) * credit_note_amount
)

accounts_receivable_change = min(
    credit_note_amount,
    accounts_receivable,
)

overpayment = credit_note_amount - accounts_receivable_change

• credit note
  • credit - accounts receivable (accounts_receivable_change)
  • debit - deferred revenue (deferred_revenue_proportional_amount)
  • debit - revenue (revenue_proportional_amount)
  • if refunding the customer
    • credit - refund (overpayment)
  • if applying to the customer's balance
    • credit - customer balance (overpayment)

Voiding Invoice

This is writing a credit note for 100% of the invoice.

Revenue Recognition

amount_of_cycle_billing = <some-input>

deferred_revenue_amount = get_balance(
    [deferred_revenue],
    invoice_line_item_id=invoice_line_item_id,
)

assert deferred_revenue_amount <= amount_of_cycle_billing

unbilled_revenue_amount = amount_of_cycle_billing - deferred_revenue_amount

• at the end of a cycle

  • debit - deferred revenue (deferred_revenue_amount)
  • credit - revenue (deferred_revenue_amount)

• at the end of a cycle

  • debit - unbilled receivables (unbilled_revenue_amount)
  • credit - revenue (unbilled_revenue_amount)

Depreciation

• depreciate an asset
  • debit - depreciation expense (input)
  • credit - accumulated depreciation (input)

Technical Questions

• Can we guarantee at the Postgres level 0 balanced entries? Each line will have an entry reference number it's associated with, so could group by that and run the accounting equation and guarantee it's 0, but that requires a check constraint across rows. Would this have to be a trigger? Could that run in the same transaction?

• Are there any guarantee problems we have to worry about?

  • Rodda: I don't think so because postgres is pretty awesome

Decisions

Tree vs Dimensions

Many ERPs represent accounts as belonging to a tree hierarchy, this document reflects that hierarchy through dimensions.

Dimensions are significantly more flexible, as there isn't a certain dimension that's considered 'parent'.

e.g. if you were to consider branch the top level splitter, it would then be very difficult to run a report cross branch. This gets around that limitation.

With copious filtering, this should be easy to avoid