How to to store production data to a reporting DB in a Rails app ?

Question

We want to keep track of production data of a Rails app, so that we can analyse them in a few months.

To be more precise, we have a few data Models with a "status" and dates column that can change frequently. The idea is to write somewhere a new line in a DB or log file every time the status or any other attribute changes. These line consists in a copy of all the attributes for this object.

I see different solutions:

• using observer with after_save, and replicate the saved data to a reporting Mysql Db
• using a logger system.
• do it a different level, on the DB level, detecting changes in a table and copying the affected rows to the reporting DB.

It's easier to extract data then if they are stored in a DB. Log files are faster and don't slow down the process that much. I can't figure out what is the best way or if there is any other solution/implementation.

Answer 1

have you looked at acts_as_versioned? I'm sure there is a more modern library written for this purpose, but it might accomplish what you need: versioning the data on update so you can keep track of it later. It may give you a template to go by, anyway.

Answer 2

There is an option as a gem: http://github.com/andersondias/acts_as_auditable

Now I'm hesitating between saving in a audit table or saving in file with CVS format. My feeling is that the second option is better, because writting in file is faster and we don't depend on a connexion to a database server.

Any single BI tool is able to use a CVS file as a data source. The problem is I have to create a different file each time I change the structure of my table. But is it a problem ?

Answer 3

I finally used an observer, and a Logger with FasterCSV to generate audit log.

I wasn't able to copy/paste all the code properly, so here is a link to a blog post, for those who are interested:

log rails production data to perform some audit (bi)

This can be easily adapted for any Rails Model. :)