Rails - Apr 2005 - Transactions, audit trails, and logging (fairly long)

I''m building a system where every change made to the database has to 
auditable. Every time a change is made I store a timestamp, a user id, 
and description of the transaction in a db_transactions table. Every 
other table has a transaction_id field that references what transaction 
last created or updated it. How I handle updates now is have a 
DbTransactions model that contains has_many relationships to all other 
tables. I create a DbTransaction, add whatever records I am creating or 
updating and save the DbTransaction item. It works fine. As far as 
logging goes I am going to add <tablename>_log tables for all of my 
normal tables and store snapshots of each record every time it changes. 
The thought in the back of my mind is that I can somehow point 
ActiveRecord at my log tables, add some code to specify the time period, 
and reuse all my regular code to see my database exactly as it was at 
any point in history.

First of all, I''m still fairly new at the Ruby/Rails way of doing 
things, is this a good framework or am I thinking the wrong way?

Secondly, assuming it is, I''d like to simplify my code from:

t = DbTransaction.new( :user_id => $user.id, :description => "my 
description" )
t.table1 << a
t.table2 << b
t.table2 << c
t.save

to:

save( "my description", a, b, c )

with save being a member of ApplicationController. My problem is how can 
save know what type of record a, b, and c are so it knows what relations 
to add it to in the DbTransaction.

Along the same line, I''d like to place my log snapshots in a after_save
handler of the DbTransaction model. Any suggestions on the best way to 
get the values from the records and write them to the proper 
<tablename>_log tables (which presumably would not have models)?

Thanks for your time!

Jack Christensen

Jack,

I''ve done the exact same thing in the past and I would have to say that
I would suggest that this really shouldn''t be placed inside Rails
unless
absolutely necessary. The proper location for logging like this is in
after_xxx triggers at the database level. The only one of the "big"
databases that this would be a problem for would be MySQL -- Oracle, MS
SQL, Firebird, Postgresql, etc, etc all have no problems with this. Yes,
it''s not automatically transportable between db engines - but
there''s a
price to pay for everything (especially security issues). The one big
advantage of triggers is no matter how (ok 99%+ of how) the database is
accessed - you would log the changes.

I love Rails and especially the ActiveRecord stuff - but this really is
a problem that is best served with the tool that is closest to the
action - and that is the database....

John W Higgins
develop-U23jnKMpDSxBDgjK7y7TUQ@public.gmane.org

Jack Christensen wrote:
> I''m building a system where every change made to the database has
to
> auditable. Every time a change is made I store a timestamp, a user id,
> and description of the transaction in a db_transactions table. Every
> other table has a transaction_id field that references what transaction
> last created or updated it. How I handle updates now is have a
> DbTransactions model that contains has_many relationships to all other
> tables. I create a DbTransaction, add whatever records I am creating or
> updating and save the DbTransaction item. It works fine. As far as
> logging goes I am going to add <tablename>_log tables for all of my
> normal tables and store snapshots of each record every time it changes.
> The thought in the back of my mind is that I can somehow point
> ActiveRecord at my log tables, add some code to specify the time period,
> and reuse all my regular code to see my database exactly as it was at
> any point in history.
> 
> First of all, I''m still fairly new at the Ruby/Rails way of doing
> things, is this a good framework or am I thinking the wrong way?
> 
> Secondly, assuming it is, I''d like to simplify my code from:
> 
> t = DbTransaction.new( :user_id => $user.id, :description => "my
> description" )
> t.table1 << a
> t.table2 << b
> t.table2 << c
> t.save
> 
> to:
> 
> save( "my description", a, b, c )
> 
> with save being a member of ApplicationController. My problem is how can
> save know what type of record a, b, and c are so it knows what relations
> to add it to in the DbTransaction.
> 
> Along the same line, I''d like to place my log snapshots in a
after_save
> handler of the DbTransaction model. Any suggestions on the best way to
> get the values from the records and write them to the proper
> <tablename>_log tables (which presumably would not have models)?
> 
> Thanks for your time!
> 
> Jack Christensen
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

> I''m building a system where every change made to the database has
to
> auditable. Every time a change is made I store a timestamp, a user id,
> and description of the transaction in a db_transactions table. 
I''ve built these type of applications many times, and if at all
possible I''d suggest you implement the audit trail via triggers in the
database rather than in your (Rails) application code.  As far as I''m
aware, MySQL is probably the only common database that doesn''t support
triggers (and it may well support it in the most recent releases), so
hopefully you''ll have that option available to you.

I''m gonna make the assumption that the content of your audit tables
are pretty crucial to your business, which has been the case every
time I''ve had to use them; if the audit trail are ever checked and
found to be incorrect, then someone''s gonna cop some significant pain.

Several reasons for using triggers for this type of task:
- it''s pretty much exactly the reason why triggers were created in the
first place
- when it''s for auditing purposes, you really need to log anything
that may change the data in your database.  In particular, that
includes changes that don''t come from your Web front-end, but are
instead caused by e.g. a DBA backing out transactions via direct SQL
DELETE statements
- the problem is generic enough that you will probably encounter it
repeatably, so you may as well learn to do it via triggers (which are
generic to pretty much any ACID-compliant database) rather than build
a solution that works with Rails, then build another that works with
your next project which may be J2EE-based, then build another solution
later that works with C#.  You really can''t afford to screw up audit
trail code, so you''re generally better of learning how to do it once
then applying the same solution over and over
- if your Rails app fails at an inopportune moment, the bit of Ruby
code that generates the audit trail entry may not run and you might
get transactions that don''t make it to the audit log.  That may be a
long shot, but again audit trails are generally something that has to
work right 100% of the time, not 99.9%.  If the audit logging code is
done via triggers, then your database will take care of the audit log
even if the database fails at the worst possible moment
- if your database ever gets screwed up and you have to do a
roll-forward or rollback on the database to sort it out, your Rails
audit logging code isn''t going to help; you''ll end up with
audit log
tables that aren''t in sync with your data

Regards

Dave M.