Central transaction table (=single transactions table)

[christian-eriksson]

Definition: a transaction is the accounting representation of an atomic business event, such as a sale, or transfer of money.

At the moment there are 4 tables playing a role in recording transactions: transactions, GL, AR and AP (with transaction lines recorded in acc_trans, requiring the lines associated with a record in ar/ap/gl being balanced).

The role of the transactions table is to prevent the same id value being shared between ar, ap and gl by adding the IDs being inserted into those tables to the transactions immediately, preventing the same value being inserted in any of the other tables. This behaviour is implemented using a trigger. Leading to this schema:

   transactions(id) --- links to exactly one of ---> ar(id)/ap(id)/gl(id)

  acc_trans(trans_id) -- links to transactions(id) [ and thus indirectly to ar/ap/gl(id) ]

  payment(id) -- links to --> payment_link(payment_id)
  payment_link(entry_id) -- links to --> acc_trans(entry_id) [ and thus links selected acc_trans entries to a payment ]

Attempt to put this in an ERD:

erDiagram

transactions ||--o| ar : is
transactions ||--o| ap : is
transactions ||--o| gl : is
transactions ||--|{ acc_trans : contains
gl |o--o| payment : "in case of overpayment"
payment ||--|{ payment_link : contains
payment_link }o--|| acc_trans : refers
transactions ||--o|voucher : refers
acc_trans }|--o| voucher : contains
batch ||--|{ voucher : contains

transactions {
    boolean approved
    date transdate 
}

acc_trans {
    boolean approved
    date transdate
}

payment_link {
   int type "regular / overp create / overp use"
}

Loading

The three tables (ar/ap/gl) record data that could be interpreted as a "transaction header": information such as a description, posting date, notes, transaction reference number, etc.

Using the transaction definition stated above, the GL table is indeed the header of a transaction: once the header data is inserted in gl and the lines in acc_trans, nothing changes to the database records anymore. However, the AR and AP table cannot be classified the same way: after the AR and AP records have been created and the acc_trans lines posted, the AR and AP data is modified in the sense that acc_trans lines are added to the AR/AP data when a payment is recorded. This also leads to the AR/AP data no longer being atomic or single-point-in-time.

An example could be:

ar table:

id	date	description
1	2023-01-01	Sale to Erik

acc_trans table:

trans_id	line#	date	account	credit amount	debit amount
1	1	2023-01-01	Income -- sale of services	250
1	2	2023-01-01	Accounts Receivable		250

Let's assume Erik pays the full amount on Jan 31st to the bank account, the data for the AR item in acc_trans looks like:

trans_id	line#	date	account	credit amount	debit amount
1	1	2023-01-01	Income -- sale of services	250
1	2	2023-01-01	Accounts Receivable		250
1	3	2023-01-31	Accounts Receivable	250
1	4	2023-01-31	Bank account		250

As you can see: the data in acc_trans is not atomic and immutable when considering all data in the "AR" item.

Problem statement 1

The above has several problems:

1. Information is lost which data is inserted as part of which business event (although for payments this is somewhat mitigated by having a table which links acc_trans lines from multiple ar/ap transactions to a single payment).
2. It's impossible to close a voided invoice against a voiding invoice directly -- consider the example below:

There are 2 invoices, with IDs 1 and 2, where 2 voids 1:

trans_id	line#	date	account	credit amount	debit amount
1	1	2023-01-01	Income -- sale of services	250
1	2	2023-01-01	Accounts Receivable		250
2	1	2023-01-01	Accounts Receivable	250
2	2	2023-01-01	Income -- sale of services		250

Ideally, these would be closed against each other automatically as part of the voiding procedure like this:

trans_id	line#	date	account	credit amount	debit amount
1	1	2023-01-01	Income -- sale of services	250
1	2	2023-01-01	Accounts Receivable		250
2	1	2023-01-01	Accounts Receivable	250
2	2	2023-01-01	Income -- sale of services		250
1	3	2023-01-01	Accounts Receivable	250
2	3	2023-01-01	Accounts Receivable		250

This, however, is impossible, because it leaves both transactions unbalanced. To work around this, a zero-amount payment is created by the end-user, resulting in the following lines:

trans_id	line#	date	account	credit amount	debit amount
1	1	2023-01-01	Income -- sale of services	250
1	2	2023-01-01	Accounts Receivable		250
2	1	2023-01-01	Accounts Receivable	250
2	2	2023-01-01	Income -- sale of services		250
1	3	2023-01-01	Accounts Receivable	250
1	4	2023-01-01	Bank account		250
2	3	2023-01-01	Accounts Receivable		250
2	4	2023-01-01	Bank account	250

While this works technically, it's functionally confusing because it creates a payment and two transactions on the bank account (which did not really happen). Users strict about their reconciliations are finding this conflicting with their desire to match the bank statement exactly in the reconciliation report.

Desired outcome

Instead of the above, I'd like the outcome to be close to this, where "trans_id"==3 is a transaction triggered by the posting of the voiding invoice:

trans_id	line#	date	account	credit amount	debit amount	ar item #
1	1	2023-01-01	Income -- sale of services	250		1
1	2	2023-01-01	Accounts Receivable		250	1
2	1	2023-01-01	Accounts Receivable	250		2
2	2	2023-01-01	Income -- sale of services		250	2
3	1	2023-01-01	Accounts Receivable	250		1
3	2	2023-01-01	Accounts Receivable		250	2

This result nicely tracks the various business events, each of which should be balanced.

Problem statement 2

Payments can be part of payment batches, which can exist in unapproved state, meaning the payment lines will have an unapproved status while the transaction itself has a status of approved. The transaction is therefore inconsistent with its lines, even though the state is functionally correct.

Returning back to the example above where Erik pays an AR item, but this time two partial payments are applied on the day the item is created, the acc_trans items look like this:

trans_id	line#	date	account	credit amount	debit amount
1	1	2023-01-01	Income -- sale of services	250
1	2	2023-01-01	Accounts Receivable		250
1	3	2023-01-31	Accounts Receivable	100
1	4	2023-01-31	Bank account		100
1	3	2023-01-31	Accounts Receivable	50
1	4	2023-01-31	Bank account		50

Here, all lines are all referencing the same ar record. Due to the way the schema is structured, none of them reference the GL table and they all reference the same transactions record by definition.

Conclusion

The current schema is - with respect to the acc_trans records linked to the ar and ap tables - not complying to the definition of atomicity of accounting transactions and even loosing information to the effect of being able to distinguish (multiple) payments from the original transaction. There definitely is a role for the AR and AP table information, but it's not as the header of a transaction as defined above.

Way forward (proposal)

A replacement schema proposal that satisfies the atomicity changes the meaning of the AR and AP tables.

erDiagram
   batch |o--|{ transactions : contains
   transactions ||--|{ acc_trans : lines
   open_item ||--o| ar : is
   open_item ||--o| ap : is
   open_item ||--o| overpayment : is
   open_item_link }|--|| open_item : contains
   open_item_link }o--|| acc_trans : contains
   open_item |o--|| acc_trans : opens
 
   open_item {
      string type "One of 'ar', 'ap' or 'overpayment'"
   }

   transactions {
    boolean approved
    date transdate
   }

Loading

Summary of differences:

• No need for approved and transdate fields on acc_trans (only on transactions level)
• No need for vouchers table, because acc_trans lines allocated to the batch may not be a subset of lines within a transaction (which used to be allowed in case of payments and receipts)
• No need for gl table, because it's assimilated with the transactions table
• No need for payment and payment_link tables (which have been generalized into the open_item and open_item_link tables)
• Payments/Receipts become regular transactions, used to close out an AR/AP open_item through open_item_link-s relating the payment lines to the AR/AP open items
• Overpayments recorded as open_item instead of specialized payment
• An amount being posted on an account creates an open_item; not the transaction, because that does not uniquely identify the account and the amount of the open item (with this schema, it's possible to mix regular payments and overpayments in a single bank receipt/payment)

<--
In this schema, there's no need for a GL table, because it's completely assimilated into the transactions table. The role of the payments table is to be reconsidered, because the payment can be registered as a single transaction (where the role of the payments table currently is to track all payment related acc_trans records across ar and ap), with allocation to the various AR and AP items through the ar_links and ap_links tables. This is especially true if transactions posted on an account that is a bank account, allow storing additional data that currently goes into the payment table. [Note to self: overpayments still do need some kind of tracking, because they can't be directly allocated to invoices...]

Along the same lines, there shouldn't be a need for vouchers anymore, because vouchers group acc_trans lines into batches across transactions, which is most important for payment/receipt batches in the existing schema, but this is no longer important in the new schema, because approval moves to the transaction level only.
-->

rest of the original text:

this leads to some weirdness in the backend like:

• Having to use a zero-payment cash transaction to close out a voiding invoice against a voided one, as the cash account side of the transaction keeps the two AR transactions balanced. Basically, by having the lines directly attached to an AR item, one cannot clear on AR item immediately against another without making it unbalanced.
• An excessive amount of UNIONs and complexity to achieve the cumulative set of transactions.
• two identical tables AR and AP with a lot of unions between them, instead of a single AA.
• In the current solution, the AR item doesn't limit its lines to a single accounting "event" -- it can have lines stretching multiple days

To solve this issue, change the roles of the AR/AP/GL tables. The GL table will contain a header for every transaction (even the ones for which AR and AP used to be the source). This also means that every group of lines inserted in the
acc_trans table will be linked to a GL record.

The AR and AP tables would become a single AA table and hold journal headers instead of transaction headers and reference the transactions in the GL table. So the AR/AP will have all lines associated with the transaction which relate to the creation and closing of the AR/AP position.

In essence the GL table would be the "single source of truth" when it comes to what transactions have taken place in the system. This would also align with the current design for payments, where a payment is linked to its lines through the payment_links table.

A single transaction table would allow for a reference to both the voided and voiding invoice on a single transaction that can close out the balance of both.

This would also simplify the transaction handling and make it easier to implement payments, overpayments and more.

[christian-eriksson]

I should note that there was some push back on the merging of AR/AP tables, mainly due to the fact that AR and AP might diverge in the future as they have different meaning and might be likely to not have the same structures in the future leading to one table where some rows require certain columns to be filled in while others require a different set of columns with a few columns in common. This in turn would mean that the application needs to know which columns to leave empty or fill in as one cannot put these restraints on the table. So maybe one should remove that part.

[christian-eriksson]

There was also a fair bit of discussion between @ehuelsmann and @freelock after I summarized this, and I realized it after posting, so it may be that my summary is completely outdated 🙈

[ehuelsmann]

@christian-eriksson hope you don't mind my edits to the original description! Creating a new discussion item felt like red tape...

[christian-eriksson]

Don't mind at all! You have a much clearer image of what the issue is here as well as potential ways forward, I was just hoping to get the discussion from the chat to github :)