Version control of resources in Ecto/Postgres and storing events on a resource

Previously, my Postgres DB was happily storing project offers to clients which were occasionally edited by an approval chain before being sent out to the offer recipients. Occasionally an offer would need to be re-edited and resent. Now my client has asked for the specific edits to be visible to the approval chain (before sending the offer) and the offer receiver.

I figure this is basic version control, which we’ve implemented in another area of the app, but we’re wondering if there is some ecto/postgres functionality or an external library or something we should be using to simplify/help what is a commonly done task.

As a short term improvement, we are going to build an offer_events table which stores events such as :offer_created, :offer_viewed, :offer_edited, :offer_updated, :offer_sent as well as the timestamp and the user id that performed that event. If event was :offer_edited then we will store the :edit_reason in which the user can say why they edited which we can show to the approvers and recipient.

Is making an ‘…_events’ table like this common practice? Is it a bad idea to store a new row each time someone views the offer? Any suggestions or criticisms with this solution?

This even led into the question - should we be storing every route that is hit and by who as a generic plug? It seems useful to know roughly who has gone where (in case there is a breach/error) but also seems like a lot of meta-data. How do you handle this?

Thanks for your help and sorry this turned into so many questions! Any thoughts or suggestions would be really appreciated.

1 Like

You can always have a janitor go over that table and condense identical calls to more simple ones to significantly reduce data, or normalize it entirely when it is first put in. :slight_smile:

Overall though, if you want a kind of versioned offer, well I’d make the offer table be versioned with a column that states the version and another stating if it is the most recent ‘submitted’ version rather than one still being made or checked. This is actually VERY similar to how the old database at work works, there is a version field that if it is null then it is the current ‘submitted’ field, if it is 'D' (an oracle database, but very stringly-typed) then it is deleted (but still there for recording purposes), if it is 'W' then it is in a being-worked-on state, etc…

Thanks for the reply Overmind!

1 Like