Just released a new package on hex for tracking changes to your Ecto models.
Its functional, but in the early stages. Could use some additional features like restore, version limits, etc. Let me know what you think.
7 Likes
Huh, was actually thinking of making something like this myself. A question though, I notice that it seems to require a User schema of some form to mark whodoneit, however in my system the User is in an LDAP system (not exposed via Ecto since Ecto2 has no LDAP interface, as well as it would not work cross-repo anyway), and the unique ID from the LDAP database is a bigint integer in Ecto. How would I give it the bigint unique ID that does not have a corresponding ecto schema?
2 Likes
It does not need a user model if you don’t want to track who made the change. Please add an issue if want to see support for an LDAP based user model.
To suport your use case, I think we can add an option to the installer for this. Something that would set the user_id to a simple field with a provided type. For the user name, we could add support for a {module, fun} tuple and call the fun if its a tuple.
What do you think?
Steve
2 Likes
That would be nice! All I’d need to store for ‘who’ made the change is a PostgreSQL bigint integer or larger, and that is included in the assign on every request or nil if not logged in, though it would be nice to, say, save the IP of the person making the change too perhaps? How about a user defined User, either a table reference or a base type like :bigint, as well as a metadata section (:map perhaps?) so we could store other things like IP address?
I have about half of it implemented now with the syntax whatwasit.install —whodoneit-id=bigint syntax. Then you set name_field: {Mod, Fun} to fun/1 that will be called with the whodoneit-id to fetch the name.
Storing a map would be an interesting idea too. As I work though this, I’m thinking generating the Version model from a template in the projects namespace. This would all full customization of the fields.
Just as a comparison, my original idea for a module like this was less generic, I was planning an interface to use PostgreSQL’s natural ability to mark rows as ‘deleted/invalid’ and they no longer appear after in SQL queries (unless you use a specific one to get those specifically deleted ones), thus you can version that way. Could store something like a user on the table itself for example. I am unsure which method is better actually…
2 Likes
I had no idea PostgreSQL could do that…do you have an example handy?
There are a few ways. PostgreSQL used to have actual Time Travel functionality built in a long time ago but was removed for ANSI compliance over time and is now emulatable via: https://www.postgresql.org/docs/9.5/static/contrib-spi.html#AEN174541
Honestly that is overkill though, and database specific (which much of my code is anyway), and I am using such a row history as it is on one of my tables. On one of my tables I need to keep all history, and yet it is dynamic. I could have made a logging table but that is extra work so all I did was add a column of (in addition to the other primary keys):
field :invalidated_at, :datetime, primary_key: true
On initial write I set it far in the future (specific date of 294275 AD), on every query I add the 294275 AD date to the query via a helper on the schema. On write I update the :invalidated_at of the old row to now() and write out the new row with the new data with the 294275 AD future date. On delete I just set the :invalidated_at to now(). This is not a generic style that would be useful in most situations, not a good style for storing, say, who did it, although with minor modifications to some of the ordering it would be.
That table grew over a few versions to do that, I was thinking of redesigning it (would not need to be a primary key with an idea I have) with a set of helpers that I could pull out into a library to manage it for me, something as simple as just dropping another timestamps-like call into the migration and schema with a set of options it could take along. It would have been postgresql specific due to using its built in time handling functions though.
1 Like
I just published 0.2.0 on hex. This version has a mix whatwasit.install —whodoneit-map option that will save your user struct in the database as a map. This should work with any struct and not just an Ecto schema.
Steve
1 Like
Awesome, much more generically useful! 
Is it support:
- multiple databases setup
- old value, new value and diff (String)
- create action (I see delete and update)
I want to retrieve a user all activity history (create, delete, update - on update what was changed + diff if changed value is a string), so admin can control users work.
@Eiji Multiple repos might work. The prepare_version function used the repo field in the changeset. So if your handling that already then the version records should get put in the correct repo. The versions helper function takes a :repo option, so that should work.
None of the other options are currently supported. I might try and tackle create this weekend. Your welcome to add feature request issues to the package to track these items.
I need to think about old new diff. We could add a diff map to the version object. The other option would be to provide a helper function to generate this from the database records on request.
If I can find the time this weekend, I’ll try to add an option to save create records. I can see the value of this from an auditing perspective.
I have more questions:
- Can you add support for IP?
For example somebody used "Social engineering" and got access to one account and admin want to know what changes in database was made by from this IP. - Is it possible to get list of changes made by IP (see point 1) or whodoneit id (ex-content editor or ex-admin).
Generic - multiple databases setup. For example umbrella apps: fetch all initialized repos and get versions all repos where ip is … - Is it possible or can you add support for revert X version or all changes made by IP or whodoneit id (see point 2).
- Can you add log file support (if log file path was specified in configuration) for all tracked changes?
Example:
On <date (when)> <what (for example article)> with id <id (for example article id)> was <action (create/destroy/update)> by <whodoneit.name (for example John)> (id <whodoneit.id (for example John’s id)>.
Why? For example:
a) fail2ban or other third-party program - for example: rule to ban ip (matched with whodoneit.id - same ip and same author/user) from which created too more entries (spam bot using REST API).
b) Somebody may not have accesss to database - he only want to get all IP addresses for John (grep by regex where for example whodoneit id is 5). Here I mean server admin/user, not project web admin or project developer.
For this you can add one more field like: log_formatter_module (if not specified use default formatter).
defmodule MyProject.LogFormatter def format(what_was_it) do # returns: {:ok, result} tuple where result is log string end end
What do you think about it?
Edit:
Oh I forgot about map option.
Can I keep relation for user model and store map (with request data) at same time?
1 Like
