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:
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.