What’s the recommended/most popular approach for database record
versioning in Rails? Is there anything out-of-the-box that could
assist?
By versioning I mean the concept of tracking all changes that occur to
data in a table against who made them. The manner in which I’ve done
this in java has included approaches such as:
[1] Keep versioned records in the table
Use of the following columns:
ACTIVE_FROM_DATE
ACTIVE_TO_DATE
USER_ID
In this case there may be multiple records in the table for a given
item, however there will be only ever 1 active record, based on whether
the current time fits into the records active range.
This also provides a means to insert future records (e.g. value X will
take affect next week)
I’m assuming this would NOT be a good fit to existing rails
functionality
[2] One record only in main table, but changes to AUDIT table
In this case the main table can have:
MODIFIED_BY
MODIFIED_DATE
All changes are reflected in a separate audit table
Which approach do people use in Rails? or which approach is the least
amount of work to incorporate in Rails?
Questions this all may boil down to I guess may include:
how to “base class” the setting of MODIFIED_BY & MODIFIED_DATE
attributes for all models - i.e. there is then no way for a developer to
forget to set these
how to “base class” the population of audit table data for all table
changes? (similar to first bullet point I guess)
PS. Should have pointed out that in particular I’m interested mostly in
approaches for AUDIT (don’t really need full versioning). From (a) the
DBA point of view the ability to see who changed data when and (b) from
the user interface point of view the ability for a given object to allow
the user to click on a HISTORY button and see the history of changes and
by who.
Tks - looking forward to hearing how people are tackling this within
Rails.
Greg H. wrote:
Hi all,
What’s the recommended/most popular approach for database record
versioning in Rails? Is there anything out-of-the-box that could
assist?
By versioning I mean the concept of tracking all changes that occur to
data in a table against who made them. The manner in which I’ve done
this in java has included approaches such as:
[1] Keep versioned records in the table
Use of the following columns:
ACTIVE_FROM_DATE
ACTIVE_TO_DATE
USER_ID
In this case there may be multiple records in the table for a given
item, however there will be only ever 1 active record, based on whether
the current time fits into the records active range.
This also provides a means to insert future records (e.g. value X will
take affect next week)
I’m assuming this would NOT be a good fit to existing rails
functionality
[2] One record only in main table, but changes to AUDIT table
In this case the main table can have:
MODIFIED_BY
MODIFIED_DATE
All changes are reflected in a separate audit table
Which approach do people use in Rails? or which approach is the least
amount of work to incorporate in Rails?
Questions this all may boil down to I guess may include:
how to “base class” the setting of MODIFIED_BY & MODIFIED_DATE
attributes for all models - i.e. there is then no way for a developer to
forget to set these
how to “base class” the population of audit table data for all table
changes? (similar to first bullet point I guess)
We do auditing in postgresql using triggers and a separate audit table
for each table. Much better solution IMO then trying to do it in the
application.
Did you have any thoughts regarding how you might expose this data to
the UI for user requests such as “show me the history of changes” for
this item? Do you think a different table structure would be better for
this?
I don’t know if this helps, but in a current Rails application I am
working on, I intend to do by table auditing at the database level
itself.
It’s an environment where a rails application as well as several batch
processes will be modifying the database as well as occasional
developers who need to ‘fix’ an issue (it always happens!) so just
managing the auditing in the Rails piece isn’t enough for me. Plus,
it’s a system that manages real money, so a propper and complete audit
trail is super ultra important.
My solution is currently postgresql specific (and derived from a
solution currently used at me ‘real’ job)
Essentially, will have an ‘audit’ table that contains user, table,
column, etc information that will be populated via triggers on each
table. Any time an insert, update or delete is performed, the triggers
kick off a series of entries to the audit table. I intend to ‘hack’ on
postgresql_adapter.rb (wish me luck!) to ‘do some trickery’ whenever a
database connection is requested via activerecord . That trickery
essentially sets up some transient data using temp tables that
postgresql can use via the triggers to know what ‘user’ is actually
performing the database change and can audit appropriately. (outside of
rails, the system will log the database user that was used to connect as
well as the IP address)
So…there is some added work for me whenever tables are added or their
structures are modified (to regenerate the triggers), but that can be
scripted pretty easily.
The audit table also contains the ‘id’ of the row entry that was
modified…
so a simple query based on table/id can give you the ‘history’ for any
item.
Greg H. wrote:
Sounds good Jeff,
Did you have any thoughts regarding how you might expose this data to
the UI for user requests such as “show me the history of changes” for
this item? Do you think a different table structure would be better for
this?
How did you pass the userid to the trigger for auditing? Are you able
to
post what you did? (e.g. I intend to ‘hack’ on postgresql_adapter.rb).
I’m wondering whether for me (so having to get too deep into
mysqltriggers/stored procedures - if it has them, haven’t checked yet)
whether an
approach similar to “keeping track of who did what - Rails Receipes”
would
be easier. That is using a Cache:Sweeper to trigger a log statement
which
will have access to the session information. This could then pass the
userid to a stored procedure to do the audit. I guess then this is
non-DB
specific and I may as well let the rails layer then write the audit
information.
PS. I’m also keen on your concept of 1 audit table only, with columns
to
store which table/column was changed. I’m thinking of complementing
this
table with an action based table which captures audit from the UI point
of
view, e.g. “UserA performed a Contact Edit & modified X,Y,Z”. If I
include
the concept of a transaction number for this I could also put this into
the
detailed database audit table and tie to the two tables together. E.g.
For
a given audit action I could then look up all the database attributes
that
were changed in relation to this transactions. Comments anyone?
There is a full fledged product vAuditServer, that you can attach to
your application. It will silently listen to your application, and
comes with a full UI that shows the audit trace of the business data
objects.