Building A History of Data Changes

Use a second database, laravel can have multiple database connections. I do accounting that way, I have a backup table for older ledger entries. That json thing nah.

I do something similar to @jlrdw

In my audit/accounting tables, I keep it pretty atomic.

Each row has:

• The type of model name
• primary key to find that record.
• field that was updated
• old value
• new value

If you ever need to roll back, or produce an audit report on changes to a model, you have the data you need.

Whether or not you keep that audit table in the same database as your models is up to you.

This is easily accomplished using a database trigger. Laravel doesn't have it built in, but you can easily create a trigger using raw sql, and then have a model to work with the "history" table. Triggers work at the database level and don't involve any php at all. You basically tell it "when this table is updated, copy it's data to this other table". It's really fast since it's internal and happens automatically behind the scenes at the database level.

You can have triggers for INSERT, as well as UPDATE functions. You can have conditionals, etc, like "if x field changes on an update, then copy to history table"

https://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html

I use this for tracking changes in real estate data for properties, so anytime a field on a property gets updated, it creates an entry in the history table so I can track all changes to properties over time. You just need to create a regular model to retrieve the data from the table and have a one-to-many relationship to the history table (one property can have many histories). The "listings" table holds the current listing data, and the "listing_history" table contains all previously changed data.

@thc1967 I have a simple pet database I use just for test data, here is what a simple trigger looks like

DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `pbackdate`.`au_petname`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `pbackdate`.`au_petname` AFTER UPDATE ON `pbackdate`.`pets` 
    FOR EACH ROW BEGIN
INSERT INTO `nameback` SET `petnamebak` = OLD.petname;
END;
$$

DELIMITER ;

Just quick example, change a name, and old name is backed up. But remember, that backed up data grows fast with triggers, every edit. Have to figure out a purge technique.

I would prefer to manage the record as a whole instead of creating the equivalent of a row per field. If I have new and old together, I can show them side-by-side and do cool things like filter to only populated values or only changed values.

I don't think I'll be high enough volume that I need to worry about purging or storage. The system that I'm replacing has been in place for over 15 years, it tracks history as 1 record per 1 field change, and it has under 500,000 records in its history table.

So let's change the question...

If I have a JSON representation of an Eloquent model inside a field in a table, how would I reconstitute that into the right Eloquent model object in PHP / Laravel? I feel like I should be looking into how polymorphic relations work...

If your JSON is a straight mapping of the data model or contains a straight mapping. You could just instantiate a new model with the attributes.

$attributes = json_decode($json);

$model = new Model($attributes);

You could even write a helper to map over a collection of your stored objects and return a collection of real models.