Creating A DB Schema for Double-Entry Accounting using Next.js and Prisma

As payments become increasingly embedded into companies, it's becoming essential for engineers to begin understanding banking and accounting principles. We have observed their effective implementation at some of the largest fintechs and marketplaces. However, accounting can seem like a complicated topic for those new to the field.

Throughout this blog post, I aim to provide a clear and concise database schema for recording and organizing ledger entries. Unfortunately, engineers often overlook accounting terminology, disregarding terms such as "debit" and "credit" and opting for positive and negative numbers instead. This can lead to confusion. To address this, I propose designing a system easily implemented in a database that aligns with standard accounting practices.

Check out the full codebase here

But does this really matter?

Accounting matters in software development because it provides a framework for tracking and reporting financial transactions. Software developers must understand accounting principles to accurately design, build and maintain systems that handle financial data. This includes procedures for managing accounts payable and receivable, recording and tracking payments, generating financial statements, and more. With a solid understanding of accounting, software developers can avoid creating systems that generate incorrect or inconsistent financial data, leading to problems such as inaccurate tax reporting or misinformed business decisions.

The Basics

Understanding Accounts and Transactions

Accounts

An account is a separate pool of value, similar to a bank checking account that holds money on your behalf. This can range from a user's balance on Venmo to the annual defense spending of the United States. Accounts are associated with the balances you want to track. In accounting, accounts have specific types, which will be discussed later.

Transactions

Transactions are individual events that alter account balances. They are made up of entries, with each transaction having at least two entries corresponding to separate accounts.

Let's look at an example within a B2B ecosystem:

TimeDescriptionA16Z's AccountYour Startup's Account
1431510505A16Z sends a wire to your new startup-$5,000,000.00+$5,000,000.00

Congratulations! You just raised $5M for your startup. But how should this transaction be tracked and accounted for properly? It's clear that there are two accounts involved with this transaction; A16Z's and your startup's banking account. This transaction's entries specify which accounts were impacted. If an account is established for each user's balance, a single transaction can create an entry for each account.

These are the basics of a ledger, a record of financial events. Unfortunately, developers frequently modify balances directly instead of computing them from a log of transactions, which is suboptimal and typically implies data loss.

While directly altering balances is more convenient and easier to implement, it's more precise to store unchangeable transactions and calculate balances from them. In addition, directly changing balances creates an error-prone system, making it challenging to identify and resolve inaccuracies.

Observe how each transaction has multiple entries. Each entry belongs to a transaction and an account. By examining entries together, one can easily discern where the funds came from and how they were used. Double-entry ensures that as transactions are recorded, sources and uses of money are clearly displayed, and balances can be reconstructed for any date.

The central concept of double-entry accounting - one transaction with a minimum of two entries, one for the source and another for the use of funds - is crucial.

General Ledger Summary

The general ledger is a record used to classify and summarize business transactions. The ledger records transactions using debits and credits, which must always balance. Debits and credits are equal but opposite entries. Your financial records and statements will be accurate if they are in balance. There are five main account categories in a general ledger:

Each category can have various sub-accounts, for example, assets may include checking or savings accounts.

To post to the general ledger, you must follow double-entry bookkeeping. As shown above, this means recording two entries for every transaction using debits and credits.

The general ledger provides the necessary information to create financial statements, such as the business balance sheet, cash flow statement, and income statement. These financial statements give you a clear overview of your business's finances.

AccountIncreased ByDecreased By
AssetsDebitCredit
ExpensesDebitCredit
LiabilitiesCreditDebit
EquityCreditDebit
RevenueCreditDebit

Debits & Credits

Some guides advise abandoning debits and credits, but it is essential to understand these concepts when creating rules for handling transactions. The challenge of grasping debits and credits stems from their use as verbs (to debit or credit an account) and as entries (debiting or crediting an account).

In accounting, debits and credits refer to the entries of a transaction. For example, the cash account is debited in a sample transaction, and the equity account is credited for $1M. Accounting systems typically log positive numbers, except for a few special situations. The effect on account balances depends on whether the entry is on the debit or credit side.

Debits and credits are a shorthand for the expected effects on accounts based on their type. For example, a credit entry will always increase the balance of a normal credit account and decrease the balance of a normal debit account.

Phew. That was a lot. Let's quickly summarize: