https://github.com/denys-olleik/accounting Skip to content Navigation Menu Toggle navigation Sign in * Product + Actions Automate any workflow + Packages Host and manage packages + Security Find and fix vulnerabilities + Codespaces Instant dev environments + GitHub Copilot Write better code with AI + Code review Manage code changes + Issues Plan and track work + Discussions Collaborate outside of code Explore + All features + Documentation + GitHub Skills + Blog * Solutions By size + Enterprise + Teams + Startups By industry + Healthcare + Financial services + Manufacturing By use case + CI/CD & Automation + DevOps + DevSecOps * Resources Topics + AI + DevOps + Security + Software Development + View all Explore + Learning Pathways + White papers, Ebooks, Webinars + Customer Stories + Partners * Open Source + GitHub Sponsors Fund open source developers + The ReadME Project GitHub community articles Repositories + Topics + Trending + Collections * Enterprise + Enterprise platform AI-powered developer platform Available add-ons + Advanced Security Enterprise-grade security features + GitHub Copilot Enterprise-grade AI features + Premium Support Enterprise-grade 24/7 support * Pricing Search or jump to... Search code, repositories, users, issues, pull requests... Search [ ] Clear Search syntax tips Provide feedback We read every piece of feedback, and take your input very seriously. [ ] [ ] Include my email address so I can be contacted Cancel Submit feedback Saved searches Use saved searches to filter your results more quickly Name [ ] Query [ ] To see all available qualifiers, see our documentation. Cancel Create saved search Sign in Sign up Reseting focus You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session. Dismiss alert {{ message }} denys-olleik / accounting Public * Notifications You must be signed in to change notification settings * Fork 5 * Star 150 Implementation of a forward-only double-entry accounting method with a relational database. License MIT license 150 stars 5 forks Branches Tags Activity Star Notifications You must be signed in to change notification settings * Code * Issues 0 * Pull requests 0 * Actions * Projects 0 * Security * Insights Additional navigation options * Code * Issues * Pull requests * Actions * Projects * Security * Insights denys-olleik/accounting This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository. master BranchesTags Go to file Code Folders and files Last Last Name Name commit commit message date Latest commit History 180 Commits Accounting.Business Accounting.Business Accounting.Common Accounting.Common Accounting.Database Accounting.Database Accounting.Service Accounting.Service Accounting Accounting .gitattributes .gitattributes .gitignore .gitignore Accounting.sln Accounting.sln Double-entry_example_from_1926.png Double-entry_example_from_1926.png LICENSE.md LICENSE.md chrome_1YoniUtAt3.gif chrome_1YoniUtAt3.gif chrome_CodNSOn0C2.png chrome_CodNSOn0C2.png chrome_YHpC5nO5Ir.png chrome_YHpC5nO5Ir.png chrome_gp6bHABAqw.png chrome_gp6bHABAqw.png david-vs-goliath.png david-vs-goliath.png explorer_rDwVWXqL5O.png explorer_rDwVWXqL5O.png readme.md readme.md yt-thumb.png yt-thumb.png View all files Repository files navigation * README * MIT license Watch the video Watch the video on YouTube It doesn't have to be this confusing... Account Type Debit Credit Assets Increase Decrease Liabilities Decrease Increase Equity/Capital Decrease Increase Revenue/Income Decrease Increase Expenses Increase Decrease Screenshots Watch the video Watch the video Watch the video Watch the video Introduction GAAP and IFRS compliant implementation of a forward-only double-entry accounting method with a relational database. Suitable for U.S. and international organizations. The only system capable of managing everything from a single laundromat to a cartel, multiple aircraft carriers, and everything in between, uncontaminated by Silicon Valley . Due to its wide scope, the system is designed to be incomplete, allowing organizations to finalize the implementation. * Using: C# * ASP.NET MVC * Vue.js * PostgreSQL This solution contains example integrations of feature-specific junction tables which group the journal entries into a transaction for specific user action or feature. A feature should be integrated with the journal if it affects one of the following account types: revenue, expense, liabilities, assets, or equity. Since every feature that impacts the journal requires a minimum of two entries, a TransactionGuid column is used to group these entries together. To implement a new feature affecting the journal, create a table to group the transaction. Prefix the table with GeneralLedger..., then append the names of the tables referenced by the foreign keys that appear immediately after the GeneralLedgerId foreign key and before the Reversed... column (excluding the "Id" suffix from each). For example, GeneralLedgerInvoiceInvoiceLine is named as such because the foreign keys after the GeneralLedgerId but before Reversed... columns are InvoiceId and InvoiceLineId. Include a Reversed... column to reference the entries being reversed, and a TransactionGuid for grouping entries. This structure supports a reversal-and-amendment strategy in a forward-only journal, where entries cannot be modified once recorded. For example, if the revenue recognition for invoice line-items needs to be adjusted after initial creation, the original entries are reversed, and new ones are added. CREATE TABLE "GeneralLedgerInvoiceInvoiceLine" ( "GeneralLedgerInvoiceInvoiceLineID" SERIAL PRIMARY KEY NOT NULL, "GeneralLedgerId" INT NOT NULL, "InvoiceId" INT NOT NULL, "InvoiceLineId" INT NOT NULL, "ReversedGeneralLedgerInvoiceInvoiceLineId" INT NULL, "TransactionGuid" UUID NOT NULL, "Created" TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'), "CreatedById" INT NOT NULL, "OrganizationId" INT NOT NULL, FOREIGN KEY ("GeneralLedgerId") REFERENCES "GeneralLedger"("GeneralLedgerID"), FOREIGN KEY ("InvoiceId") REFERENCES "Invoice"("InvoiceID"), FOREIGN KEY ("InvoiceLineId") REFERENCES "InvoiceLine"("InvoiceLineID"), FOREIGN KEY ("ReversedGeneralLedgerInvoiceInvoiceLineId") REFERENCES "GeneralLedgerInvoiceInvoiceLine"("GeneralLedgerInvoiceInvoiceLineID"), FOREIGN KEY ("CreatedById") REFERENCES "User"("UserID"), FOREIGN KEY ("OrganizationId") REFERENCES "Organization"("OrganizationID") ); This naming strategy makes it easy for developers to understand the relationship between features and the journal. The GeneralLedgerInvoiceInvoiceLine is used to record entries during creation of the invoice, the modification of the line-items, and the removal of the line-items from the invoice. However, if you require these concepts to be separated even further, you could adopt a different naming strategy, for example, GeneralLedgerInvoiceInvoiceLineCreated, GeneralLedgerInvoiceInvoiceLineUpdated, GeneralLedgerInvoiceInvoiceLineRemoved. For example, it may be useful to know how much revenue is lost due to line-items being removed from the invoice. Journal, accounts, and double-entry CREATE TABLE "Account" ( "AccountID" SERIAL PRIMARY KEY NOT NULL, "Name" VARCHAR(200) NOT NULL, "Type" VARCHAR(50) NOT NULL CHECK ("Type" IN ('assets', 'liabilities', 'equity', 'revenue', 'expense')), -- ... "Created" TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'), "ParentAccountId" INT NULL, "CreatedById" INT NOT NULL, "OrganizationId" INT NOT NULL, FOREIGN KEY ("ParentAccountId") REFERENCES "Account"("AccountID"), FOREIGN KEY ("CreatedById") REFERENCES "User"("UserID"), FOREIGN KEY ("OrganizationId") REFERENCES "Organization"("OrganizationID"), UNIQUE ("Name", "OrganizationId") ); The ParentAccountId allows for hierarchical relationships which allow for better reporting and analysis. However, ensuring that the ParentAccountId is of the same Type as the AccountID is enforced at application level. CREATE TABLE "GeneralLedger" ( "GeneralLedgerID" SERIAL PRIMARY KEY NOT NULL, "AccountId" INT NOT NULL, "Credit" DECIMAL(20, 4) NULL, "Debit" DECIMAL(20, 4) NULL, "CurrencyCode" VARCHAR(3) NULL, "ExchangeRate" DECIMAL(12, 5) NULL, "Memo" TEXT NULL, "Created" TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'), "CreatedById" INT NOT NULL, "OrganizationId" INT NOT NULL, FOREIGN KEY ("AccountId") REFERENCES "Account"("AccountID"), FOREIGN KEY ("CreatedById") REFERENCES "User"("UserID"), FOREIGN KEY ("OrganizationId") REFERENCES "Organization"("OrganizationID") ); Journal entries are made in transactions of at least two entries where the sum of credits equals debits. Double entry is misleading because some people assume the entries of a transactions are made in pairs, or have an even number of entries. A typical chart-of-accounts might look like... AccountID Name Type ParentAccountId 1 accounts-receivable assets NULL 2 revenue revenue NULL 3 revenue-service revenue 2 4 revenue-product revenue 2 5 cash assets NULL 6 chase-9988 assets NULL 7 chase-9988-debit-card-2323 assets 6 8 expense expense NULL 9 expense-diesel expense 8 10 expense-meals expense 8 11 expense-maintenance expense 8 12 discover-5555 liabilities NULL 13 inventory assets NULL 14 sales-tax-payable liabilities NULL Keep an eye on number 14. Consider a transaction where an invoice is created with one line-item which requires sales tax to be collected... GeneralLedgerID AccountId Credit Debit 1 1 (ar) 0.00 1100.00 2 2 (revenue) 1000.00 0.00 3 14 (liabilities) 100.00 0.00 The above table shows a valid transaction made up of three entries where debits equal credits. Examine the TransactionGuid column in the table below from the result of SELECT * FROM "GeneralLedgerInvoiceInvoiceLine"; to identify transaction boundary. ID GeneralLedgerId InvoiceId InvoiceLineId ReversedId TransactionGuid 1 2 1 1 NULL e0b84e1c-e62d-60f2-0a96-61f895c14fcf 2 1 1 1 NULL e0b84e1c-e62d-60f2-0a96-61f895c14fcf 3 4 1 2 NULL e0b84e1c-e62d-60f2-0a96-61f895c14fcf 4 3 1 2 NULL e0b84e1c-e62d-60f2-0a96-61f895c14fcf 5 5 1 1 1 91977936-5d6a-401b-7ea6-9092eee92918 6 6 1 1 NULL 91977936-5d6a-401b-7ea6-9092eee92918 7 7 1 1 2 91977936-5d6a-401b-7ea6-9092eee92918 8 8 1 1 NULL 91977936-5d6a-401b-7ea6-9092eee92918 9 9 1 2 3 91977936-5d6a-401b-7ea6-9092eee92918 10 10 1 2 NULL 91977936-5d6a-401b-7ea6-9092eee92918 11 11 1 2 4 91977936-5d6a-401b-7ea6-9092eee92918 12 12 1 2 NULL 91977936-5d6a-401b-7ea6-9092eee92918 13 13 1 1 6 227fa74a-3166-01b6-61db-0f6a6af4a52e 14 14 1 1 8 227fa74a-3166-01b6-61db-0f6a6af4a52e Note the reversing entries and the three separate transactions. 1. Rows 1-4 invoice with two line-items is created. 2. Rows 5-12 both line-items updated. 3. Rows 13-14 one line-item is removed. To prevent confusion regarding how to determine if a line-item was removed from an invoice, and without adding additional flags or columns to indicate line-item removal, the line-items whose transactions end with a reversal are considered removed from the invoice. Payments * Payments are received against invoice line-items. * Payments can be partial. * The relationship between payment and invoice is many-to-many. The GeneralLedgerInvoiceInvoiceLinePayment links the journal entries with the payment. CREATE TABLE "GeneralLedgerInvoiceInvoiceLinePayment" ( "GeneralLedgerInvoiceInvoiceLinePaymentID" SERIAL PRIMARY KEY NOT NULL, "GeneralLedgerId" INT NOT NULL, "InvoiceInvoiceLinePaymentId" INT NOT NULL, "ReversedGeneralLedgerInvoiceInvoiceLinePaymentId" INT NULL, "TransactionGuid" UUID NOT NULL, "Created" TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'), "CreatedById" INT NOT NULL, "OrganizationId" INT NOT NULL, FOREIGN KEY ("GeneralLedgerId") REFERENCES "GeneralLedger" ("GeneralLedgerID"), FOREIGN KEY ("InvoiceInvoiceLinePaymentId") REFERENCES "InvoiceInvoiceLinePayment"("InvoiceInvoiceLinePaymentID"), FOREIGN KEY ("ReversedGeneralLedgerInvoiceInvoiceLinePaymentId") REFERENCES "GeneralLedgerInvoiceInvoiceLinePayment" ("GeneralLedgerInvoiceInvoiceLinePaymentID"), FOREIGN KEY ("CreatedById") REFERENCES "User"("UserID"), FOREIGN KEY ("OrganizationId") REFERENCES "Organization" ("OrganizationID") ); An incoming check is usually entered into the system before it is deposited. Ideally, the check should be entered into a check-in-transit or similar asset account and later synchronized when it appears on the statement. Reconciliations The pattern repeats in the GeneralLedgerReconciliationTransaction table. A bank or credit card statement contains rows of transactions. Credit card statements will have expense transactions while bank statements will have both expense and revenue transactions. Each transaction in the statement should be uniquely identifiable, but often the exported CSV does not contain a column to uniquely identify the row. CREATE TABLE "GeneralLedgerReconciliationTransaction" ( "GeneralLedgerReconciliationTransactionID" SERIAL PRIMARY KEY NOT NULL, "GeneralLedgerId" INT NOT NULL, "ReconciliationTransactionId" INT NOT NULL, "ReversedGeneralLedgerReconciliationTransactionId" INT NULL, "TransactionGuid" UUID NOT NULL, "Created" TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'), "CreatedById" INT NOT NULL, "OrganizationId" INT NOT NULL, FOREIGN KEY ("GeneralLedgerId") REFERENCES "GeneralLedger" ("GeneralLedgerID"), FOREIGN KEY ("ReconciliationTransactionId") REFERENCES "ReconciliationTransaction"("ReconciliationTransactionID"), FOREIGN KEY ("ReversedGeneralLedgerReconciliationTransactionId") REFERENCES "GeneralLedgerReconciliationTransaction" ("GeneralLedgerReconciliationTransactionID"), FOREIGN KEY ("CreatedById") REFERENCES "User"("UserID"), FOREIGN KEY ("OrganizationId") REFERENCES "Organization" ("OrganizationID") ); Integration with banks to pull the transaction data programatically is possible, but CSV import is used as universal fallback option. Installation 0. Have dotnet installed. 1. Have psql installed. 1. Have CREATE EXTENSION IF NOT EXISTS postgis; installed. 2. Have CREATE EXTENSION IF NOT EXISTS pgcrypto; installed. 2. Update connection strings in appsettings.json. 3. Set database-reset.json to true and run. This does two things... 1. Uses the main database context to create the application database. 2. Runs create-db... script to create tables, columns, relationships, indexes, etc. I will later separate sample data to be optional. 4. Use test@example.com pw test to login and choose "Farm To Market LLC", it already has sample chart of accounts. 5. Create invoice. 6. Receive payment. Backup and import/export strategy The system will have three levels of backups. 1. Ledger - System will attempt to perform a backup upon every action/transaction that affects the ledger. Backups will be in human and machine readable format such as csv, json, or xml (you'll get to pick whichever you hate the least). 2. Database - Performed as often as resources allow, ideally every hour. 3. Instance - Entire virtual machine instance is backed up daily. --------------------------------------------------------------------- Ambitions of Grandeur: Rebuilding an Institution Why doesn't anyone want to be an accountant? Because it's not accounting anymore. test I don't remember QwackBooks ever looking like that. Intuit didn't invent accounting; they've stolen it. Accounting existed before computers, databases, and software. Transactions were recorded in physical journals using pen and paper, which forced accountants to have a better understanding of company finances due to the process of manual entry, with little room for error. Now, the process has been automated through software, and the journal entries are hidden away, leaving only the final report to spot errors and fraud. None of you can call yourselves accountants; you're just QwackBooks users. The truck drivers of the office. Your boss thinks of you as a whiny, tail-dragging #####. That's why he needs just one of you who knows the entire system. Maybe you'll get lucky with an assistant, but most likely they'll cause more problems, because it'll be the boss's daughter and she doesn't give a ####. Companies that adopt my system are far less likely to get audited than those using QwackBooks or some half-baked ERP system thrown together by a committee in India and managed by another in San Francisco. These systems often go through months of customization and setup before they can be used, and still end in failed implementations. [david-vs-g] About Implementation of a forward-only double-entry accounting method with a relational database. Topics erp accounting double-entry-accounting Resources Readme License MIT license Activity Stars 150 stars Watchers 1 watching Forks 5 forks Report repository Releases No releases published Packages 0 No packages published Languages * C# 47.8% * JavaScript 27.1% * HTML 19.1% * CSS 6.0% Footer (c) 2024 GitHub, Inc. Footer navigation * Terms * Privacy * Security * Status * Docs * Contact * Manage cookies * Do not share my personal information You can't perform that action at this time.