So my team and I are working on a new project, and we need to keep an always-on audit trail. This is one of our first major projects using Rails for an internal business app.

This project uses Ruby on Rails as its framework with MySQL used with the ActiveRecord ORM datamapper for our development environment; our production environment will be ActiveRecord with Oracle, with transactions imported/synchronized to SAP via EAI conduit to BAPI/RFC invocation on an hourly interval.

We need to be able to audit models and its associated object changes. We require the ability to audit updates/deletions, recording a whodunit log for each revision, e.g, invoices, invoice lines; if I delete an invoice line in version 3, and add two invoice lines in version 4, and add one more invoice line in version 5, I'd like to be able to pull version 4 exactly as it was).

In short, an auditor must be able to look at a transaction and pull any previous revisions exactly how it looked from our software.

What is the best way to do this? My thinking is in the example of Invoices and InvoiceLines to create a new object called RevisedInvoices and RevisedInvoiceLines and have the database be triggered to copy the model when an update or deletion is called in SQL. On the other hand, I can control the logic better by programming the copy in a before_ hook in the app. Should I do this as a database trigger or should I let the MVC app logic handle the copying?

I know this isn't the most efficient way of building an audit trail since I am making entire copies rather than storing the delta changes for each revision. But since space is cheap, I'd like someone to give me their experience with building an audit trail, especially one with associations. If I change an invoice A with invoice lines B, C, and D, and I add another invoice line E, how can I audit/version associated objects? What do you guys think about cloning the model to a second a table such as a RevisedObject to do the trick?

Please advise.

I think a database trigger is the best way to go: the database system, which has likely been more thoroughly reviewed for correctness, would be the system guaranteeing that an audit record is created for each update, rather than your custom application. Besides, the database layer should be the layer responsible for ensuring data integrity. And it's easier to verify correctness of a small trigger than your entire application.

One approach is to duplicate the model then add two fields: one is a new unique identifier, and the other simply indicates "before" or "after". Inserting a row would result in an "after" copy in the audit table; deleting a row would result in a "before" copy in the audit table; and changing a row would result in a pair of "before" and "after" rows.

It might get a bit more complex if an object is not represented by a single row in a single table, though.

I have only briefly tinkered with Ruby on Rails, so I don't know how much of this advice might be applicable.

There are several options one can do, depending on how the company wants things.

Generally, for each table, there is an associated history table.
Example:
Table Customer:
CustomerID (int, PK),
CustomerName (varchar(50)).

Table CustomerHistory:
HistoryID (autonumber, pk),
CustomerID (int), -- the old data that was in Customer.
CustomerName(varchar(50), -- the old data that was in Customer.
Status (varchar(1)), -- A=add, C or U = change, D= delete.
DateModified(datetime, default = now), -- right now.
ChangeUser(varchar(50). -- whodunnit

Use a trigger on the Customer table.
Insert a row into Customer table? Then insert the data into the History table with a "A" for the status (a = add, c= change, d= delete). Your Customer table has the current data, while the history table has all the changes made. Capturing ChangeUser lets you capture "who" made the change. Date lets you capture "when."

A different option I'm using in one DB is a single audit table that captures the table, columns and data changed. They don't want dozens of history tables. A blog post I worked from is:
http://www.simple-talk.com/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-the-audit-trail/

I don't know how it works with MySQL, but in SQL Server (which the above example is for), the inserted and deleted tables are really views that the trigger can see. Inserted is the new data, Deleted is what the data used to be.

We are going to be using Oracle for production; just waiting for a new server to be setup and a license provisioned. We will probably wait to start the application conversion process until Oracle is setup.