integrity of entries

[Andrew Sullivan]

On Mon, Jan 30, 2012 at 05:35:12PM -0500, Mike or Penny Novack wrote:
> Zeev, your authorities are fools?

Probably, but it's not actually an unusual request in accounting
systems.  

> When I did this stuff for a living a lot of what I did was "starting
> back form a previous version, bring in everything (except these that
> are errors)". For one of the world's largest "financials".

That's normally how you detect it, yes.  That's why the back end
database has the various triggers and so on that I alluded to.

For instance, here's roughly how I'd do it in Postgres.

1.  Add triggers on every table in the Gnucash schema.  These triggers
track every INSERT, UPDATE, and DELETE and copy the altered rows to
another SQL schema, with a log table.  That log table tracks the row,
and also tracks the timestamp (and maybe the transaction id and some
other things).  The schema is write-only for these triggers: the
underlying functions would be security definers, so the gnucash user
would have write access to the target schema but not read access.

2.  Add logging to the Postgres back end to track every connection by
the Postgres superuser(s).

3.  Replicate the database to other locations, ideally in log-shipping
mode so that it would be possible to roll transactions forward to a
given point in time just in case someone managed to undermine the
above controls.

Now you have a system, using GnuCash, that can track the changes
people make and that could recover from forged transactions if there
were any.  

This would be pretty painful (and IMO completely unreliable, but that
might be my prejudices) in MySQL, but I think you could do it there
too.  You couldn't do it in SQLite, which doesn't have some of the
features needed.

All of that said, I think looking at the SQL schema at
http://wiki.gnucash.org/wiki/SQL that GnuCash's SQL back end is not
really a back end.  It's more like a SQL-shaped file store.  If the OP
wanted stricter controls of the sort one might be able to get by using
the back end storage to enforce such controls, then he might want to
look elsewhere.  When I was choosing GnuCash, I also looked at
xTuple's PostBooks.  I found it too complicated for what I wanted to
do, but I didn't know what I was doing and that was a couple years
ago.  Anyway, it's pretty tightly integrated with its Postgres back
end, so one would have an easier time using the database as the source
of controls.

Best,

A

-- 
Andrew Sullivan
ajs at anvilwalrusden.com