Rails: implement auditing of table insertions/updates

Question

I am fairly new to ROR and am working on a cargo tracking project. The idea is to log all changes to shipment location so that when users track their shipment with a tracking number they would see the current location and a history of locations passed.

I have a shipments table and a shipment_locations table.

Shipments

+--------------------------+--------------+------+-----+---------+----------------+
| Field                    | Type         | Null | Key | Default | Extra          |
+--------------------------+--------------+------+-----+---------+----------------+
| id                       | int(11)      | NO   | PRI | NULL    | auto_increment |
| start_address_id         | int(11)      | YES  |     | NULL    |                |
| end_address_id           | int(11)      | YES  |     | NULL    |                |
| transportation_agency_id | int(11)      | YES  | MUL | NULL    |                |
| customer_id              | int(11)      | NO   | MUL | NULL    |                |
| status_id                | int(11)      | NO   | MUL | NULL    |                |
| cargo_id                 | int(11)      | YES  | MUL | NULL    |                |
| tracking_number          | varchar(255) | NO   | MUL | NULL    |                |
| mode_of_transport_id     | int(11)      | YES  | MUL | NULL    |                |
| expected_start_date      | date         | YES  |     | NULL    |                |
| actual_start_date        | date         | YES  |     | NULL    |                |
| expected_end_date        | date         | YES  |     | NULL    |                |
| actual_end_date          | date         | YES  |     | NULL    |                |
| created_at               | datetime     | NO   |     | NULL    |                |
| updated_at               | datetime     | NO   |     | NULL    |                |
| admin_user_id            | int(11)      | YES  | MUL | NULL    |                |
+--------------------------+--------------+------+-----+---------+----------------+

shipment_locations: (Location updates are done on this table, under (current_location))

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int(11)      | NO   | PRI | NULL    | auto_increment |
| shipment_id      | int(11)      | NO   | MUL | NULL    |                |
| current_location | varchar(255) | YES  |     | NULL    |                |
| final_location   | text         | YES  |     | NULL    |                |
| created_at       | datetime     | NO   |     | NULL    |                |
| updated_at       | datetime     | NO   |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+

Shipment_history (proposed)

Fields:
- id
- shipment_id
- current_location
- created_at
- updated_at

I would like to store all the updates from shipment_locations table to this history table so that users can get:

Time (Updated_at):----------------------------> Location:(current_location)
1/12/2012 11:30                                  222 John st.
1/12/2012 13:00                                  555 Paul st.
1/13/2012 07:30                                  final_location

How can this be achieved from the controllers so that only one update action saves to history table?

UPDATE:

I was able to get a shipment tracking log using this trigger, thanks vladr

DELIMITER $$

DROP TRIGGER shipment_after_update$$

CREATE TRIGGER shipment_after_update AFTER UPDATE on shipments FOR EACH ROW BEGIN INSERT INTO cargo_transit_histories (shipment_id , current_location , final_location , updated_at ) VALUES (NEW.id, NEW.current_location, NEW.final_location, NOW()); END$$

DELIMITER ;

You may want to also consider the pros and cons of (1) using database triggers, (2) *versioning* your tables instead of having a separate audit table — vladr, Nov 24 '12 at 19:55
Thanks vladr i will do some research on using triggers looks like a cleaner way of doing this. But with triggers there should be a separate table right? — Andy7, Nov 24 '12 at 20:04
With triggers you do whatever you want inside the trigger. Also look at http://stackoverflow.com/questions/5346367/object-versioning-in-rails-like-papertrail-but-individual-tables and http://stackoverflow.com/questions/1697456/versioning-of-models-in-ruby-on-rails — vladr, Nov 24 '12 at 20:05
Appreciate your prompt replies, will report back after trying these options. — Andy7, Nov 24 '12 at 20:10

score 0 · Answer 1 · answered Apr 06 '13 at 15:07

I'm not sure if I got you right and if it is a good approach but i'll try. I suggest you to make shipment_history belong to shipment_locations.

So if shipment_history has shipment_locations_id you could use a after_update callback to set what you need.

ShipmentLocations.model:

      #your other code
    after_update :track_changes_in_history

    def track_changes_in_history 
           @shipment_history=Shipment_history.where(:shipment_locations_id=>self.id).first_or_create!
@shipment_history.current_location = self.current_location
#so on
     end

But here with your model attributes I don't see the way to show its state in time. For that you'd probably better set separate for example Map location:string model, which belongs to all of other model by has_many declaration, and than you can show it state in history, by ordering locations, which belongs to this shipment.

Rails: implement auditing of table insertions/updates

1 Answers1