Accounting database thoughts

accounting thoughts.markdown

Given an accounting database where:

• All changes to the database must be performed by a job posting process
• The database at the end of each posting will be marked with a GUID
• Each job posting will be performed through a job queue (e.g., Minion)
• Each job queue entry will be marked with the starting ("before") and ending ("after") GUID, and optionally, a log of all database modifications (e.g., SQL commands) performed

The following should be true:

• There will exist an audit trail of postings and modifications to the database in the job queue log
• A snapshot of the database at any time will contain its GUID, which can be matched to the postings immediately preceding and following that snapshot
• Given a database snapshot and one or more postings occuring immediately after the snapshot, replaying the jobs into a copy of the database should result in a database (as in an "after" snapshot) that will always be identical, other than last-modified timestamps. If not, either an error (hardware or software) occurred and the comparison may be used in diagnosis, or a possibly malicious change occured and the database server logs can be examined to see which transactions (and which processes or users) might be responsible for the mismatch.

A database comparison tool should also be useful in writing unit tests for accounting package components.

In 1980–81 I worked for Clyde Digital Systems of Bedford, Mass. (formerly Intelligent Systems; 1976 advert in Boston Globe). I was a sophomore and junior at Bedford High School at the time.

They had an accounting system, mentioned in 1980-10-20 Computerworld and 1981 Datamation. It was written mostly in BASIC-PLUS/2 (the same language Larry Wall used before developing Perl) on a PDP-11/40 running RSTS/E.

As I recall, the accounting system worked like this:

• Client programs (data-entry) could read from the database, for example to locate customer records or existing invoices
• All changes (new invoices, new clients, new general ledger categories, any change to the database at all) had to be made by creating a journal posting file and submitting it to a job posting program
• The job posting program would create a log of its actions and return that as a file to the client program
• The client program could display the log, parse it, print it, archive it, or simply give the user a status report

My job as software maintenance programmer was to take a misbehaving program, as identified by a starting database snapshot, a description of what the user entered and expected, a job posting from the data-entry program, a post log, and a resulting database snapshot, and identify why the log or resulting database differed from the expected; to document the reason; to modify the program that created a bad job posting or to modify the posting process; and to close the ticket with a full set of posting files, logs, and resultant database snapshots. I had an individual PPN (project-programmer number, i.e., user account) which would run a separate copy of the database engine and client programs which I could modify and test before rolling data and programs back to a known version.

In effect we had manual unit testing methodology, version control, and an integration process in 1980.

As far as I can tell, no existing consumer-grade accounting package works this way, nor has any useful level of audit-trail features, nor any way of replaying postings against database snapshots to verify database accuracy. A system with a process equivalent to what is outlined above, should result in a faithful and reproducible set of books.