Database with "audit trail" table
Adrian von Bidder
Instead of creating "changeby" / "changed" fields on all my tables, I'm
planning to write some model classes where changes would be recorded in a
separate "audit trail" table (the obvious benefit beyond not requiring the
additional fields is that I can preserve the history as far back as I want)
So, like a table audit ( timestamp, dbchange, info, ...) where "dbchange"
would be some kind of machine interpretable description and info would be a
textual description of the event ("created user blah", "removed product x
from order y", ...)
Where do I start to get this automated?
Session has the information about what needs to be done (insert, delete,
update), and the model classes know what should be recorded into the audit
records. So where do I hook into so that I automatically can add these
audit records at flush time? (this would need to be after flush, actually,
since autogenerated values need to be available. But as long as I get into
the same transaction I'm just fine.)
Perhaps this has even been done before?
(Obviously, this kind of audit trail would be lost upon rollback. For a
first variant this is fine with me; later I' guess an option to use a
separate session for the audit stuff would be nice, but that'd need to log
which audit records were part of a transaction that was later rolled
back...)
Ideas & comments?
cheers
-- vbi
--
Bill Dickey is learning me his experience.
-- Yogi Berra in his rookie season.
Mike Conley
Adrian von Bidder
> You might want to start here
>
> http://www.sqlalchemy.org/trac/wiki/UsageRecipes/LogVersions
Hmm. Sounds like it's the direction I'm interested in. Is this discussed
somewhere in a bit more verbose form? I'm quite new to sqla and also to a
lesser degree to python, so reading the code without an idea what it's
supposed to do (and almost more important, why it does it the way it does)
is quite hard...
Still, thanks
-- vbi
--
featured link: http://www.pool.ntp.org
David Bolen
> Ideas & comments?
For what it's worth, I'd think that the best sort of audit would be
something done in the database itself, since it would audit any
changes whether done through any interface.
It depends on the database involved, but for example, in PostgreSQL
you could establish audit rules on the relevant tables that copied old
row values into a mirror audit table whenever they changed. You can
put the audit tables off in a different schema (which also lets you
keep the same table names) to avoid them being visible by default to
normal users/applications in the database.
-- David
Adrian von Bidder
> Adrian von Bidder <avbi...@fortytwo.ch> writes:
> > Ideas & comments?
>
> For what it's worth, I'd think that the best sort of audit would be
> something done in the database itself, since it would audit any
> changes whether done through any interface.
Yes and no...
I see your point (and since I know pg better than sqla/python I'd probably
even be quicker doing it in SQL), but the application has more knowledge
about what's going on.
My "audit" table should not just version the db content, but I plan to
record additional stuff that comes from the application (like: who
authorized the change? etc.) which is not readily available at the db level.
(And then there's the fact that I'm writing this toy project to learn about
sqlalchemy, so going ahead and writing an SQL audit trail framework misses
this goal completely :-)
cheers
-- vbi
(Off to play around with SeesionExtension some more ...)