In many projects, one needs to track changes in data in your database. With pgMemento, you can automatically generate audit information like that. It extends your PostgreSQL tables by an audit_id column, which in turn refers to the specific audit event in the pgMemento tables, which are by default stored in a separate database schema.
So far, so good.
A need for more information
To get to this information, you need to query those tables. But there is one catch: If you want to know the before and after values of the changed target, you need to query the target’s complete history – pgMemento only stores the before value.
And wouldn’t it be more convenient if the audit information could be queried along with the entity it belongs to?
That’s what we wanted. Primarily, we wanted to be able to fill a UI view with our data and sort it by creation date, modification date, and so on.
As there are no tools out there to do this in a GraphQL/PostGraphile stack (as far as we know), we decided to code one.
postgraphile-audit-plugin
The postgraphile-audit-plugin can be installed via npm. It enriches your database with the audit event type and a function to get the audit data you want from the pgMemento tables. It also extends PostGraphile to enable you to query audit information where you expect to find them: on the entity it belongs to.
Check out the GitHub project or the npm registry for further information.
There you will find the setup instructions, as well as an implementation to get the username („who changed the data“) from your systems own JWT-token instead of the one provided by the database connection. This is especially useful in systems with an authentication service instead of using a single centralized database.
It’s in the details!
Do you want to know more? Let me show you an example. Instead of this
our queries now look like this
resulting in this:
Check out the plugin and tell us what you think!
Schreibe einen Kommentar