Project: Accounting Library

Installing vend is simple, but it does have one unusual requirement: it requires ECL, a different implementation of Common Lisp (we installed SBCL at the very beginning, before even installing Emacs). So first, we need to install ECL.

If you're on Linux, you can try looking for a package for your distribution.

On MacOS, ECL is available on Homebrew.

Otherwise, you can follow the instructions below to compile ECL from the source. These come directly from the manual.

This is where cl-dbi will be necessary. It will allow us to execute a query manually.

dbi works by first preparing a query for a certain database connection.

(dbi:prepare database-connection query)

The query is a simple string. You can yield one from a SQL statement prepared with sxql.

(sxql:select :*
  (sxql:from :almighty_account)
  (sxql:where (:= :almighty_account.id 5)))
                                        ; => #<SXQL-STATEMENT: SELECT * FROM
                                        ; almighty_account WHERE
                                        ; (almighty_account.id = 5)>


(sxql:yield
 (sxql:select :*
   (sxql:from :almighty_account)
   (sxql:where (:= :almighty_account.id 5))))
                                        ; => "SELECT * FROM almighty_account
                                        ; WHERE (almighty_account.id = ?)", (5)

Notice that we receive two values from yield. This is to make it easy to use dbi to prepare the statement–sanitizing user input to prevent SQL inject attacks.

(with-chart-of-accounts
  (multiple-value-bind (query binds)
      (sxql:yield
       (sxql:select :*
         (sxql:from :almighty_account)
         (sxql:where (:= :almighty_account.id 5))))
    (dbi:prepare mito:*connection* query)))
                                        ; => #<DBD.SQLITE3:DBD-SQLITE3-QUERY
                                        ; {70078DE2C3}>

We pass the prepared query and binds to execute to get a result.

(with-chart-of-accounts
  (multiple-value-bind (query binds)
      (sxql:yield
       (sxql:select :*
         (sxql:from :almighty_account)
         (sxql:where (:= :almighty_account.id 5))))
    (dbi:execute (dbi:prepare mito:*connection* query)
                 binds)))
                                        ; => #<DBD.SQLITE3:DBD-SQLITE3-QUERY
                                        ; {70081DDED3}>

We have two options: we can take one row of the result at a time and do something with it, or we can receive the whole thing all at once and then do something with all of the data.

(with-chart-of-accounts
  (multiple-value-bind (query binds)
      (sxql:yield
       (sxql:select :*
         (sxql:from :almighty_account)
         (sxql:where (:= :almighty_account.id 5))))
    (dbi:fetch (dbi:execute (dbi:prepare mito:*connection* query)
                            binds))))
                                        ; => (:|id| 5 :|name| "General Expenses"
                                        ; :|code| "GEN000" :|category| "expense"
                                        ; :|currency| "USD" :|created_at|
                                        ; "2025-11-30 03:59:02.662347Z"
                                        ; :|updated_at| "2025-11-30
                                        ; 03:59:02.662347Z")

(with-chart-of-accounts
  (multiple-value-bind (query binds)
      (sxql:yield
       (sxql:select :*
         (sxql:from :almighty_account)
         (sxql:where (:= :almighty_account.id 5))))
    (dbi:fetch-all (dbi:execute (dbi:prepare mito:*connection* query)
                                binds))))
                                        ; => ((:|id| 5 :|name| "General
                                        ;  Expenses" :|code| "GEN000"
                                        ;  :|category| "expense" :|currency|
                                        ;  "USD" :|created_at| "2025-11-30
                                        ;  03:59:02.662347Z" :|updated_at|
                                        ;  "2025-11-30 03:59:02.662347Z"))
                                        ;  notice the extra parentheses

And that's the basics of using dbi. multiple-value-bind is the go-to macro for working with forms that return multiple values like sxql:yield.

Now that you know basically how we're going to use dbi to get our data, we can get started on the task at hand.

First, rename the account-balance function to account-balance-slow. We'll use it to test later.

+ (defun account-balance-slow (account &optional (as-of (universal-now))) +
....)

Don't forget to export it with sly-export-symbol-at-point (C-c x with the cursor on function name).

Now we need to get to the SQL query. What we want to do is get all of the legs for a certain account (as we do with account-legs) and then sum up the entry_amount columns of all those legs. In the end, we need an amount and a currency so we can properly make a money object later, so we're not going to (select :*), we're going to return a sum.

(with-coa
  (let ((account (ak:get-account "CSH000")))
    (multiple-value-bind (query binds)
        (s:yield
         (s:select ((:as (:sum :leg.entry_amount) :amount))
           (s:from (:as :almighty_account :account))
           (s:inner-join (:as :almighty_leg :leg) :on (:= :leg.account_id
 :account.id))
           (s:where (:= :account.id (m:object-id account)))))
      (d:fetch-all (d:execute (d:prepare m:*connection* query)
                              binds)))))
                                        ; => ((:|amount| 100000000))

That looks good, but let's just be sure with a smaller test.

(with-chart-of-accounts                 ; Using in-memory database
  ;; Use cash and revenue accounts.
  (let ((cash (ak:get-account "CSH000"))
        (revenue (ak:get-account "RMR000")))
    ;; Make transactions.
    (ak:transact! (ak:debit cash 5000)
                  (ak:credit revenue 5000))
    (ak:transact! (ak:debit cash 5000)
                  (ak:credit revenue 5000))
    (ak:transact! (ak:debit cash 5000)
                  (ak:credit revenue 5000))
    ;; Reverse the transactions.
    (ak:transact! (ak:credit cash 5000)
                  (ak:debit revenue 5000))
    (ak:transact! (ak:credit cash 5000)
                  (ak:debit revenue 5000))
    (ak:transact! (ak:credit cash 5000)
                  (ak:debit revenue 5000))
    (multiple-value-bind (query binds)
        (s:yield
         (s:select ((:as (:sum :leg.entry_amount) :amount))
           (s:from (:as :almighty_account :account))
           (s:inner-join (:as :almighty_leg :leg) :on (:= :leg.account_id
 :account.id))
           ;; Check cash balance.
           (s:where (:= :account.id (m:object-id cash)))))
      (d:fetch-all (d:execute (d:prepare m:*connection* query)
                              binds)))))
                                        ;  => ((:|amount| 0))

At this point, we just need to add a way to filter for the as-of transaction date, add the entry_currency to the return values, and turn this into a proper function.

(defun sql-account-balance (account as-of)
  (multiple-value-bind (query binds)
      (s:yield (s:select ((:as (:coalesce (:sum :leg.entry_amount) 0) :amount)
                          (:as :leg.entry_currency :currency))
                 (s:from (:as :almighty_account :account))
                 (s:inner-join (:as :almighty_leg :leg) :on (:= :leg.account_id
 :account.id))
                 (s:inner-join (:as :almighty_transaction :tran) :on (:=
 :leg.transaction_id :tran.id))
                 (s:where (:and (:= :account.id (m:object-id account))
                                (:<= :tran.date as-of)))
                 (s:group-by :leg.entry_currency)))
    (let ((result (d:fetch (d:execute (d:prepare m:*connection* query)
                                      binds))))
      (am:make-money (second result) (fourth result)))))

Then let's make a new account-balance function:

(defun account-balance (account &optional (as-of (universal-now)))
  "A function for getting the balance of an account at a certain point in time,
defaulting to now.

Usage Example:
    (account-balance \"CSH000\")
    (account-balance sales-account-object)
"
  (let ((account (if (stringp account)
                     (get-account account)
                     account))
        (as-of (etypecase as-of
                 (lt:timestamp (lt:timestamp-to-universal as-of))
                 (transaction (lt:timestamp-to-universal (transaction-date as-of)))
                 (string (lt:timestamp-to-universal (lt:parse-timestring as-of)))
                 (integer as-of))))
    (sql-account-balance account as-of)))

Now we can test it out against the slower version.

(time (with-coa (ak:account-balance-slow "CSH000")))

Results in:

Evaluation took:
  0.675 seconds of real time
  0.677828 seconds of total run time (0.638939 user, 0.038889 system)
  [ Real times consist of 0.060 seconds GC time, and 0.615 seconds non-GC time. ]
  [ Run times consist of 0.060 seconds GC time, and 0.618 seconds non-GC time. ]
  100.44% CPU
  7 forms interpreted
  391,349,920 bytes consed

Now for the optimized version:

(time (with-coa (ak:account-balance "CSH000")))

Results in:

Evaluation took:
  0.051 seconds of real time
  0.044906 seconds of total run time (0.033658 user, 0.011248 system)
  88.24% CPU
  7 forms interpreted
  262,080 bytes consed

Much better.

In the project root directory, make a new directory named example.

We need a system for this example, so let's set that up.

;; almighty-kaikei/almighty-kaikei-examples.asd
(defsystem "almighty-kaikei-examples"
  :author "Micah Killian"
  :version "0.0.1"
  :description "Examples of how to use the Almighty-Kaikei library."
  :depends-on (#:almighty-money #:almighty-kaikei #:local-time #:mito #:sxql
 #:dbi)
  :components ((:module "examples"
                :serial t
                :components ((:file "db")
                             (:file "views")))))

Before we work on our queries, it'll help to know the motivation for getting the data by looking at our views.

First, let's get our package set up in examples/views.lisp.

(defpackage #:examples/views
  (:use #:cl)
  (:local-nicknames (#:db #:examples/db)
                    (#:lt #:local-time)
                    (#:ak #:almighty-kaikei)
                    (#:am #:almighty-money)))
(in-package #:examples/views)

In here, we're going to create some simple reports. The first one is a general ledger. The most basic kind of accounting report is the general ledger. It simply contains all of the transactions made, detailing the two sides and accounts affected.

;; * ░ VIEWS ░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░

(defun format-general-ledger (from to)
  (let ((transaction-count (db:transaction-count-for-period from to))
        (legs (db:legs-for-period from to)))
    (format t "~&Showing ~a transactions.~%" transaction-count)
    (format t "~a~30t~a~60t~a~%" "Transaction #" "Debits" "Credits")
    (loop :for leg :in legs
          :for i = 0
          :if (and (ak:debit-p leg) (= 0 i))
            :do (format t "~a~30t~30a~%" (ak:leg-transaction-id leg) (list (am:format-money
 nil (ak:leg-entry leg)) (ak:account-name (ak:leg-account leg))))
          :else
            :do (format t "~a~60t~30a~%" (ak:leg-transaction-id leg) (list (am:format-money
 nil (ak:leg-entry leg)) (ak:account-name (ak:leg-account leg))))
          :finally (format t "~%"))))

This function will

Let's make examples/db.lisp and define the package:

(defpackage #:examples/db
  (:use #:cl)
  (:local-nicknames (#:lt #:local-time)
                    (#:m #:mito)
                    (#:s #:sxql)
                    (#:ak #:almighty-kaikei)
                    (#:am #:almighty-money)))

(in-package #:examples/db)

This package will be in charge of helping us work with the database: establishing a connection, select queries, and other "business logic" queries.

(defmacro with-universals (bindings &body body)
  `(let ,(loop :for (var val) :in bindings
               :collect (list var
                              `(lt:timestamp-to-universal
                                (if (stringp ,val)
                                    (lt:parse-timestring ,val)
                                    ,val))))
     ,@body))

(defmacro with-db-connection (&optional (db-name "sqlite.db") &body body)
  `(let ((mito:*connection* (dbi:connect-cached :sqlite3 :database-name ,db-name)))
     (unwind-protect (progn ,@body)
       (dbi:disconnect mito:*connection*))))

(defun db-path ()
  (let ((db-path (asdf:system-relative-pathname :almighty-kaikei-examples #p"
data/sqlite.db")))
    (ensure-directories-exist db-path)
    db-path))

(defun initialize-application-database ()
  (with-db-connection (db-path)
    (let (
          ;; Asset Accounts
          (cash-account                 (ak:make-account "Cash"                "
CSH000" "asset"))
          (inventory-account            (ak:make-account "Inventory"           "
INV000" "asset"))
          (accounts-receivable-account  (ak:make-account "Accounts Receivable" "
REC000" "asset"))
          ;; Expense Accounts
          (cost-of-goods-sold-account   (ak:make-account "Cost of Goods Sold"  "
CGS000" "expense"))
          (general-expenses-account     (ak:make-account "General Expenses"    "
GEN000" "expense"))
          ;; Income Account
          (sales-revenue-account        (ak:make-account "Sales Revenue"       "
SLS000" "income"))
          ;; Liability Account
          (tax-account                  (ak:make-account "Tax Payable"         "
TAX000" "liability"))
          ;; Equity Accounts
          (room-revenue-account         (ak:make-account "Room Revenue"        "
RMR000" "equity")))
      (m:ensure-table-exists 'ak:account)
      (m:ensure-table-exists 'ak:transaction)
      (m:ensure-table-exists 'ak:leg)

      ;; Asset
      (ak:get-or-save-account cash-account)
      (ak:get-or-save-account inventory-account)
      (ak:get-or-save-account accounts-receivable-account)
      ;; Expense
      (ak:get-or-save-account cost-of-goods-sold-account)
      (ak:get-or-save-account general-expenses-account)
      ;; Income
      (ak:get-or-save-account sales-revenue-account)
      ;; Liablity
      (ak:get-or-save-account tax-account)
      ;; Equity
      (ak:get-or-save-account room-revenue-account))))

To begin with, we have with-universals. This converts local-time:timestamp objects into Common Lisp universal times. According to the HyperSpec (https://cl-community-spec.github.io/pages/Universal-Time.html),

Universal time is an absolute time represented as a single non-negative integer—the number of seconds since midnight, January 1, 1900 GMT (ignoring leap seconds).

Earlier, we wrote the transaction deftable in order to automatically serialize timestamps to universal times as well as the reverse. For our queries, if we ever want to filter rows by timestamps, we might be tempted to use local-time:timestamp objects directly, but they don't work. We could try converting timestamps into a format that SQLite understands that still looks like a timestamp, but for our purposes, a simple integer will do.

with-db-connection is a simple macro for running some code with a database connection. mito:*connection* needs to be set to a dbi connection, which we set to sqlite3. unwind-protect is a macro that will always run the form of its second argument even if the first argument form has some error. It's useful for any necessary cleanup after a form does some work, perhaps saving some data to a file, but then fails in the middle. In our case, we use it to close the connection.

db-path ensures that we have a place to put our database file (if the data folder isn't already created, for example).

initialize-application-database is for ensuring the almighty-kaikei tables are available and our Chart of Accounts is created. When we first start our application, we need to run this function.

The most basic kind of accounting report is the General Ledger. It simply contains all of the transactions made, detailing the two sides and accounts affected.

(defun sales (from to)
  (with-universals ((from from)
                    (to to))
    (let* ((transaction-count (second (first (m:retrieve-by-sql
                                              (s:select ((:count :*))
                                                (s:from :almighty_transaction)
                                                (s:inner-join :almighty_leg :on (:=
 :almighty_leg.transaction_id :almighty_transaction.id))
                                                (s:where (:and
                                                          (:=
 :almighty_leg.account_id (m:object-id (ak:get-account "RMR000")))
                                                          (:>=
 :almighty_transaction.date from)
                                                          (:<=
 :almighty_transaction.date to))))))))
           (sales-account (ak:get-account "RMR000"))
           (legs (m:select-dao 'ak:leg
                   (m:joins 'ak:transaction)
                   (s:where (:and
                             (:= :almighty_leg.account-id (m:object-id
 sales-account))
                             (:>= :almighty_transaction.date from)
                             (:<= :almighty_transaction.date to)))
                   (s:order-by :almighty_transaction.id))))
      (format t "~&Showing ~a transactions.~%" transaction-count)

      (format t "~a~15t~a~45t~a~%" "Transaction #" "Debits" "Credits")
      (loop :for leg :in legs
            :for i = 0
            :if (and (ak:debit-p leg) (= 0 i))
              :do (format t "~a~15t~a~%" (ak:leg-transaction-id leg) (list (am:format-money
 nil (ak:leg-entry leg)) (ak:account-name (ak:leg-account leg))))
            :else
              :do (format t "~a~45t~a~%" (ak:leg-transaction-id leg) (list (am:format-money
 nil (ak:leg-entry leg)) (ak:account-name (ak:leg-account leg))))
            :finally (format t "~%")))))

general-ledger uses a with-universals macro for taking two local-time "timestrings" and converting them to universal times. When making queries like this, universal times are easier to use than trying to work with local-time timestamps–that's why we set up transaction dates to inflate/deflate (serialize/deserialize) between timestamps and universal times earlier.

Here's with-universals:

(defmacro with-universals (bindings &body body)
  `(let ,(loop :for (var val) :in bindings
               :collect (list var
                             `(lt:timestamp-to-universal
                               (if (stringp ,val)
                                   (lt:parse-timestring ,val)
                                   ,val))))
     ,@body))

It takes bindings, a list of lists (as in a let form) with a variable and the value to bind it to. It takes a string and converts it to a universal time. A simple but useful for reports like general-ledger that want to show data for a certain time range.

The transaction-count variable is bound to the result of a simple SQL query that gets the number of transactions for a time range.

legs is similar, with one small difference: it uses mito:joins–the equivalent of typing in inner-join :table_name :on .... Since we're using select-dao, we can use the joins macro to save a little bit of typing.

After that, we just go through the list of leg objects and format them. The TILDE t format directive here is useful for making tabular output. ~nt is padded to the nth space. ~15t means "If you aren't at column 15 yet, move there before continuing to format."

Output of general-ledger should look like this:

Showing 300 transactions.
Transaction #                 Debits                        Credits
1                             ($50.00 Cash)                 
1                                                           ($50.00 Room Revenue)         
2                             ($50.00 Cash)                 
2                                                           ($50.00 Room Revenue)         
3                             ($50.00 Cash)                 
3                                                           ($50.00 Room Revenue)         
4                             ($50.00 Cash)                 
4                                                           ($50.00 Room Revenue)

We're implementing these reports using format, but we could just as easily use HTML to format them for the web. For simplicity we're keeping the query definition and the "front end" together in one function.

A trial balance shows the net total balance of all accounts.

To get started, open a buffer in t/main.lisp and add this:

(defpackage #:almighty-kaikei/test/main
  (:use #:cl)
  (:local-nicknames (#:lu #:lisp-unit2)
                    (#:lt #:local-time)
                    (#:ak #:almighty-kaikei)
                    (#:am #:almighty-money)))
(in-package #:almighty-kaikei/test/main)

Now let's make a simple test to learn how to use lisp-unit2.

(lu:define-test testing-testing-123 ()
  (lu:assert-eql 123 123))

This test will pass if (eql 123 123) or fail otherwise. To run the test, we can use lu:run-tests.

(lu:run-tests)

It should return something like this:

#<LU:TEST-RESULTS-DB Tests:(1) Passed:(1) Failed:(0) Errors:(0) Warnings:(0) {700C0D01D3}>

You can group sets of tests using "tags". You can specify one or more tags.

(lu:define-test test-my-test (:tags '(math)))
(lu:define-test test-my-test2 (:tags '(accounting reporting)))

To run tests with a certain tag or tags:

(lu:run-tests :tags '(accounting))

In addition to tags, you can specify contexts. Contexts are lexical environments for tests. You can specify them on a per-test basis or for all tests in the call to lu:run-tests.

(lu:define-test test-my-context-text (:contexts (list #'with-my-context
 #'with-another-context)))
(lu:run-tests :run-contexts (list #'lu:with-failure-debugging-context))

lu:with-failure-debugging-context is a built-in context that will open the debugger if an assertion fails, giving you an opportunity to look at the data.

Contexts are functions that return a funcall to a body function.

(defun with-my-context (body-fn)
  (let ((...))
    (do-thing)
    (do-another-thing)
    (funcall body-fn)))

Every test that uses with-my-custom-context will include this lexical environment.

In order to run our tests, we need to be able to do several things for every test:

1. Connect and disconnect to a database.
2. Create and destroy our database tables.
3. Create a standard set of accounts (called a Chart of Accounts).

First, let's create the function and macro for connecting to the database:

(defmacro with-db-connection (&optional (db-name "sqlite.db") &body body)
  `(let ((mito:*connection* (dbi:connect-cached :sqlite3 :database-name ,db-name)))
     (unwind-protect (progn ,@body)
       (dbi:disconnect mito:*connection*))))

(defun test-db-path ()
  (let ((db-path (asdf:system-relative-pathname :almighty-kaikei #p"t/test.db")))
    (ensure-directories-exist db-path)
    db-path))

Next, we need to make a "chart of accounts" lexical database environment–"fixtures". To reiterate, there are five different categories of accounts, and a chart of accounts may have several of each category. In our context, we will make the SQL tables and then add the accounts to the database.

(defun with-chart-of-accounts-context (body-fn)
  (with-db-connection #p":memory:"
    (m:ensure-table-exists 'ak:account)
    (m:ensure-table-exists 'ak:transaction)
    (m:ensure-table-exists 'ak:leg)
    (let (
          ;; Asset Accounts
          (cash-account                 (ak:make-account "Cash"                "
CSH000" "asset"))
          (inventory-account            (ak:make-account "Inventory"           "
INV000" "asset"))
          (accounts-receivable-account  (ak:make-account "Accounts Receivable" "
REC000" "asset"))
          ;; Expense Accounts
          (cost-of-goods-sold-account   (ak:make-account "Cost of Goods Sold"  "
CGS000" "expense"))
          (general-expenses-account     (ak:make-account "General Expenses"    "
GEN000" "expense"))
          ;; Income Account
          (sales-revenue-account        (ak:make-account "Sales Revenue"       "
SLS000" "equity"))
          ;; Liability Account
          (liability-account            (ak:make-account "Liabilities"         "
LIA000" "liability"))
          ;; Equity Accounts
          (room-revenue-account         (ak:make-account "Room Revenue"        "
RMR000" "equity")))
      ;; Asset
      (ak:get-or-save-account cash-account)
      (ak:get-or-save-account inventory-account)
      (ak:get-or-save-account accounts-receivable-account)
      ;; Expense
      (ak:get-or-save-account cost-of-goods-sold-account)
      (ak:get-or-save-account general-expenses-account)
      ;; Income
      (ak:get-or-save-account sales-revenue-account)
      ;; Liablity
      (ak:get-or-save-account liability-account)
      ;; Equity
      (ak:get-or-save-account room-revenue-account)
      (funcall body-fn))))

This is the context that we'll use for our lisp-unit2 tests. It uses SQLite's in-memory database, making it temporary for the duration of each connection.

We also want a way to mess around outside of the tests, so let's make a macro that lets us do that:

(defmacro with-coa (&body body)
  `(with-db-connection (test-db-path)
     (let (
           ;; Asset Accounts
           (cash-account                 (ak:make-account "Cash"                "
CSH000" "asset"))
           (inventory-account            (ak:make-account "Inventory"           "
INV000" "asset"))
           (accounts-receivable-account  (ak:make-account "Accounts Receivable" "
REC000" "asset"))
           ;; Expense Accounts
           (cost-of-goods-sold-account   (ak:make-account "Cost of Goods Sold"  "
CGS000" "expense"))
           (general-expenses-account     (ak:make-account "General Expenses"    "
GEN000" "expense"))
           ;; Income Account
           (sales-revenue-account        (ak:make-account "Sales Revenue"       "
SLS000" "income"))
           ;; Liability Account
           (liability-account            (ak:make-account "Liabilities"         "
LIA000" "liability"))
           ;; Equity Accounts
           (room-revenue-account         (ak:make-account "Room Revenue"        "
RMR000" "equity")))
       (m:ensure-table-exists 'ak:account)
       (m:ensure-table-exists 'ak:transaction)
       (m:ensure-table-exists 'ak:leg)

       ;; Asset
       (ak:get-or-save-account cash-account)
       (ak:get-or-save-account inventory-account)
       (ak:get-or-save-account accounts-receivable-account)
       ;; Expense
       (ak:get-or-save-account cost-of-goods-sold-account)
       (ak:get-or-save-account general-expenses-account)
       ;; Income
       (ak:get-or-save-account sales-revenue-account)
       ;; Liablity
       (ak:get-or-save-account liability-account)
       ;; Equity
       (ak:get-or-save-account room-revenue-account)
       ,@body)))

Using with-coa will cause a .db file to be created that we can inspect and run queries on.

So now it's time to make some tests. We'll start with some warm-up tests just to see if we can do basic stuff.

;; * ░ Accounting ░░░░░░░░░░░░░░░░░░░░ For our accounting scenarios, we will
;; imagine the accounting for a hotel. The hotel sells stays, has a restaurant,
;; and rooms have snack fridges. They take cash and credit.

(lu:define-test tests-get-account (:tags '(accounting) :contexts (list
 #'with-chart-of-accounts-context))
  (lu:assert-true (ak:get-account "CSH000")))

(lu:define-test test-account-code (:tags '(accounting) :contexts (list
 #'with-chart-of-accounts-context))
  (lu:assert-equal "CSH000" (ak:account-code (ak:get-account "CSH000"))))

(lu:define-test test-make-account (:tags '(accounting) :contexts (list
 #'with-chart-of-accounts-context))
  (lu:assert-true (typep (ak:make-account "Expenses" "EXP" "expense")
 'ak:account)))

(lu:define-test test-get-or-create-account (:tags '(accounting) :contexts (list
 #'with-chart-of-accounts-context))
  (let ((new-account (ak:get-or-save-account (ak:make-account "More Assets" "
ASS001" "asset"))))
    (lu:assert-no-error 'error (ak:account-code (ak:get-account "ASS001")))))

These tests check to see if everything related to getting or making accounts is working. We can run these with (lu:run-tests). If everything passes, great. If you have a failing test, run (lu:run-tests :run-contexts (list #'lu:with-failure-debugging-context)).

Assuming you have everything settled with the above tests, let's start getting into the meat of things. Remember that the purpose of these tests is to confirm both that the system is working and that we understand how the accounting works.

;; * ░ Transactions ░░░░░░░░░░░░░░░░░░░░ * ░ Card Sale of Service
;; *░░░░░░░░░░░░░░░░░░░░ ;; This scenario involves tracking: ;; 1. A card
;; *payment going to our Receivable account. ;; 2. Card payment provider
;; *expense. ;; 3. The revenue after paying that expense.

(lu:define-test test-transact!-card-sale-of-service (:tags '(accounting
 transactions)
                                                     :contexts (list
 #'with-chart-of-accounts-context))

  (ak:transact! (ak:debit "REC000" 4500)
                (ak:debit "GEN000" 500)
                (ak:credit "RMR000" 5000))
  (lu:assert-equality #'am:money= (ak:account-balance (ak:get-account "REC000")) (am:usd
 4500))
  (lu:assert-equality #'am:money= (ak:account-balance (ak:get-account "GEN000")) (am:usd
 500))
  (lu:assert-equality #'am:money= (ak:account-balance (ak:get-account "RMR000")) (am:usd
 5000)))

(lu:define-test test-transact!-unbalanced-sides (:tags '(accounting transactions)
                                                 :contexts (list
 #'with-chart-of-accounts-context))
  (lu:assert-error 'ak:unbalanced-transaction (ak:transact! (ak:debit "CSH000"
 5000)
                                                            (ak:credit "RMR000"
 4000))))

(lu:define-test test-transact!-not-enough-legs (:tags '(accounting transactions)
                                                :contexts (list
 #'with-chart-of-accounts-context))
  (lu:assert-error 'simple-error (ak:transact! (ak:debit "CSH000" 5000))))

Recall that we're considering the example of a hotel's finances. In the card-cale-of-service scenario, we receive a credit card payment for a room. We credit our Room Revenue income account 5000 cents ($50)–increasing its value. The card payment service charges us 500 cents ($5) that is debited to our general expenses account–adding to its value. Finally we credit our Receivable account the remainder of the money, 4500 cents ($45), which is what we expect to actually have after paying our expenses.

Since the test begins with no transactions in the database, all three accounts should have account balances equal the amounts they've been increased by.

The second test just checks that our unbalanced-transaction condition works properly. The third checks to make sure we receive an error if there aren't at least two legs in the transaction.

We'll make a couple more tests just to confirm that our understanding of the principles of double-entry accounting are solid.

;; ** ░ Cash Sale of Goods ░░░░░░░░░░░░░░░░░░░░ This scenario involves tracking:
;; 1. The cash paid. 2. The revenue earned. 3. The cost of the good sold. 4. The
;; reduction in inventory of the good. 5. The total profit (revenue - cogs)

(lu:define-test test-cash-sale-of-goods (:tags '(accounting transactions)
                                         :contexts (list
 #'with-chart-of-accounts-context))
  (let ((cash-account "CSH000")
        (sales-account "SLS000")
        (cogs-account "CGS000")
        (inventory-account "INV000"))
    (ak:transact! (ak:debit cash-account 5000) (ak:credit sales-account 5000)
                  (ak:debit inventory-account 3000) (ak:credit cogs-account 3000))
    (lu:assert-equality #'am:money= (ak:account-balance cash-account) (am:usd
 5000))
    (lu:assert-equality #'am:money= (ak:account-balance sales-account) (am:usd
 5000))
    (lu:assert-equality #'am:money= (ak:account-balance cogs-account) (am:usd -3000)) ; Crediting
                                                                                      ; expense
                                                                                      ; account
                                                                                      ; decreases
                                                                                      ; its
                                                                                      ; value.
    (lu:assert-equality #'am:money= (ak:account-balance inventory-account) (am:usd
 3000))
    (lu:assert-equality #'am:money=
                        (am:money+ (ak:account-balance sales-account) (ak:account-balance
 cogs-account))
                        (am:usd 2000))))

;; ** ░ Credit Card Sale of Hotel Stay ░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░
;; This scenario involves tracking: 1. Accounts Receivable (Asset) for credit
;; card payment. 2. The room revenue (Equity) earned. 3. Credit card processing
;; fees (Expense)

(lu:define-test test-debit-card-sale-of-hotel-stay (:tags '(accounting
 transactions)
                                                    :contexts (list
 #'with-chart-of-accounts-context))
  (let ((receivable-asset-account "REC000")
        (general-expense-account "GEN000")
        (room-revenue-account "RMR000")
        (cash-asset-account "CSH000"))
    ;; Sale is made--debit Receivable Account for card payment, debit general
    ;; expenses for card fees. Credit Room Revenue for sale.
    (ak:transact! (ak:credit room-revenue-account (am:usd 5000))
                  (ak:debit general-expense-account (am:usd 500))
                  (ak:debit receivable-asset-account (am:usd 4500)))
    (lu:assert-equality #'am:money=
                        (ak:account-balance receivable-asset-account)
                        (am:usd 4500))
    (lu:assert-equality #'am:money=
                        (ak:account-balance general-expense-account)
                        (am:usd 500))
    (lu:assert-equality #'am:money=
                        (ak:account-balance room-revenue-account)
                        (am:usd 5000))
    ;; Bank fulfills payment; clear receivable and add to cash
    (ak:transact! (ak:debit cash-asset-account (am:usd 4500))
                  (ak:credit receivable-asset-account (am:usd 4500)))
    (lu:assert-equality #'am:money=
                        (ak:account-balance receivable-asset-account)
                        (am:usd 0))
    (lu:assert-equality #'am:money=
                        (ak:account-balance cash-asset-account)
                        (am:usd 4500))))

Call (lu:run-tests) again. Everything pass? Good. Let's test one more important feature: the as-of parameter of account-balance.

(lu:define-test test-account-legs-as-of
    (:tags '(query) :contexts (list #'with-chart-of-accounts-context))
  (let ((cash (ak:get-account "CSH000"))
        (sale (ak:get-account "SLS000"))
        (date (lt:now)))
    (let ((1st (ak:transact! (ak:debit cash (am:usd 500))
                             (ak:credit sale (am:usd 500))
                             date))
          (2nd (ak:transact! (ak:debit cash (am:usd 500))
                             (ak:credit sale (am:usd 500))
                             (lt:timestamp+ date 10 :sec))))
      (lu:assert-number-equal 1 (length (ak:account-legs cash (lt:timestamp+ (ak:transaction-date
 2nd) -1 :sec)))))))

Here we make two transactions, one ten seconds after the other. We set the as-of parameter to one second before the second transaction. We expect for the cash account that we should only have one leg–the one for the first transaction. Call (lu:run-tests) and check if this passes. It does? Good.