Creating an audit trail

Hey there folks,
I’m currently looking for a way to perform an audit trail for updates to records on our database. We would like to know of any changes to a record after a HTTP request/smart action ( e.g. which fields on a record have been updated ). Does anyone know of the best way to approach this task using Forest and Sequelize together with a postgres db.
Cheers Mitch

Hey @Mitchell_Lloyd :wave: ,

Have you already considered using our default activity logs? What additional information would you need to answer your case (it’s be super helpful to help us improve this feature)?

Generally speaking, the best approach to create your own custom audit trail would be to add a new table and populate it every time an update or action is done in Forest Admin. Here are some useful links:

Hope this helps

Hey @louis ,
We need to know the fields that have changed on a record ( for example, which fields have been updated following an update to some record in the db ). We’d also need to report what the contents of the record where before the update.

Hey @Mitchell_Lloyd,

Ok, I see your very clear needs. I’ll make a woodshop example on my spare-time. I will try to have a comprehensive example to share with you this Friday. :slight_smile:

Just some informations, in your opinion how and where would you save those datas ? (In your postgre DB with jsonb format ? In an elastic search ? In a file ? Something else ?)

Kind regards,
Morgan

1 Like

Hey @morganperre
That would be great - thankyou very much.
I think the best thing would be for us to save those datas in the postgres DB.
Cheers and thankyou so much

1 Like

Hey @Mitchell_Lloyd,

As promised, I’m back with some thoughts in this topic.

Expectations
At first glance I was thinking about a global middleware on the /forest routes. It was a sweat dream not achievable in the current state of forest-express-sequelize. And definitively not the right solution.

Current workaround

For this use case you can do something like that. It only works when the “transaction” is completed.

services/audit-helper.js

const {
  RecordSerializer,
  RecordCreator,
  RecordGetter,
  RecordUpdater,
} = require('forest-express-sequelize');

class AuditHelper {

  constructor({
    model,
  }) {

    if (!model) {
      throw new Error('Your collection model is required !');
    }

    this.model = model;
  }

  logUpdate() {
    return async (request, response, next) => {
      const updater = new RecordUpdater(this.model);
      const recordGetter = new RecordGetter(this.model);

      const record = await recordGetter.get(request.params.recordId);
      const { id, ...modifiedFieldsValues } = await updater.deserialize(request.body);

      const keys = Object.keys(modifiedFieldsValues);
      const initialFieldsValues = {};

      keys.forEach(field => initialFieldsValues[field] = (record[field]));

      next();

      console.log(`id of the modified record from the collection "${this.model.name}":`, id);
      console.log(initialFieldsValues);
      console.log(modifiedFieldsValues);
      console.log(request.user);

      // Afer next(), implement your own logic to save data
    }
  }

  logCreate() {
    return async (request, response, next) => {
      const recordCreator = new RecordCreator(this.model);
      const recordToCreate = await recordCreator.deserialize(request.body);

      next();

      // record id can be found here response.data.id
      console.log(`New record created into the collection "${this.model.name}"`);
      console.log(recordToCreate);
      console.log(request.user);

      // Afer next(), implement your own logic to save data
    }
  }

  logDelete() {
    return async (request, response, next) => {
      const deleteRecordId =  request.params.recordId;

      next();

      console.log(`id of the delete record from the collection "${this.model.name}":`, deleteRecordId);
      console.log(request.user);

      // Afer next(), implement your own logic to save data
    }
  }
}

module.exports = AuditHelper;

How to integrates in a route ?
routes/comments.js

const express = require('express');
const { PermissionMiddlewareCreator } = require('forest-express-sequelize');
const { comments } = require('../models');

const router = express.Router();
const permissionMiddlewareCreator = new PermissionMiddlewareCreator('comments');

// Initialize the service AuditHelper
const AuditHelper = require('../services/audit-helper');
const auditHelper = new AuditHelper({ model: comments })

// Create a Comment with AuditHelper middleware
router.post('/comments', permissionMiddlewareCreator.create(), auditHelper.logCreate());

// Update a Comment with AuditHelper middleware
router.put('/comments/:recordId', permissionMiddlewareCreator.update(), auditHelper.logUpdate());

// Delete a Comment with AuditHelper middleware
router.delete('/comments/:recordId', permissionMiddlewareCreator.delete(), auditHelper.logDelete());


// Get a list of Comments
router.get('/comments', permissionMiddlewareCreator.list(), (request, response, next) => {
  // Learn what this route does here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/routes/default-routes#get-a-list-of-records
  next();
});

// Get a number of Comments
router.get('/comments/count', permissionMiddlewareCreator.list(), (request, response, next) => {
  // Learn what this route does here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/routes/default-routes#get-a-number-of-records
  next();
});

// Get a Comment
router.get('/comments/:recordId(?!count)', permissionMiddlewareCreator.details(), (request, response, next) => {
  // Learn what this route does here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/routes/default-routes#get-a-record
  next();
});

// Export a list of Comments
router.get('/comments.csv', permissionMiddlewareCreator.export(), (request, response, next) => {
  // Learn what this route does here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/routes/default-routes#export-a-list-of-records
  next();
});

// Delete a list of Comments (you need to create a middleware into the AuditHelper to handle this case)
router.delete('/comments', permissionMiddlewareCreator.delete(), (request, response, next) => {
  // Learn what this route does here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/routes/default-routes#delete-a-list-of-records
  next();
});

module.exports = router;

Note: I haven’t implemented logDeletes and logAction middlewares in this example. I will take a look at those one if you think that example is relevant.
I’m also lacking the route Extending/Overriding mechanisms

I not really convinced by this implementation but it does the work. I put some log to show you the informations you can save in your DB.

Another workaround
You can also use directly the Hooks in sequelize. Here a list of interesting hooks. The downside of this method: you cannot know which user made the changes it’s pure sequelize logic here.

  beforeCreate(instance, options)
  beforeDestroy(instance, options)
  beforeUpdate(instance, options)

  afterCreate(instance, options)
  afterDestroy(instance, options)
  afterUpdate(instance, options)

The Holy Grail
This first aventure in this subject raised some interrogations on our current implementation. I think we can improve a lot. Thus, I think we will try to work on some hooks on our side. This way we could do sort of the same as sequelize but with forest specific logic (who did the changes, initialFieldsValues, modifiedFieldsValues, etc).

I hope this will help you.

Kind regards,
Morgan

Hello @morganperre
Thankyou for the extra effort you went to here - it is much appreciated !
This helped a great deal - I’m going to compare this post with our implementation. Our current implementation uses Sequelize Hooks as you mentioned.

1 Like