Auditing Entities - Can subscribers/listeners have metadata passed to them, such as the id of the user making the change?

[kswierszczyk]

Issue type:

[x] question
[ ] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[x] mysql / mariadb
[ ] oracle
[ ] postgres
[ ] cockroachdb
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[x] latest
[ ] @next
[ ] 0.x.x (or put your version here)

I need to create an audit trail on entities and am curious whether using subscribers/listeners is the way to go..

My question is this - is there a way to attach metadata to an entity, say prior to calling .save(entity), which is then available to me in an entity listener's hook, such as beforeUpdate()? Is this possible without having to create a new column (modified_by) on the entity itself, and if so, how?

Example: Let's say that I have an AuthorizedUser context -- the user calling my api -- which contains a userId. Understandably, I'd like to log this in the audit record so that I can capture the user that made the change to the entity. Is there anyway to do something (pseudo-code) such as entity.metadata.add('userId', userId) prior to .save(entity) - and have that available in the listener/subscriber?

[jmls]

did you ever get a solution to this ? I am confused about the whole "current user" thing, as from what I can see the current user is the user specified on connection.

From an auditing point of view this doesn't make sense, as you need to know the actual user that is performing the create/update/delete (and maybe even read ..) of a certain table / record etc

I am genuinely curious to know how other people have solved this - most auditing takes place with triggers defined in the db schema - outside of typeorm - and therefore the only way of knowing "who" is performing the action is to use the "current user"

Whilst you can "cheat" by adding a "modified_by" column for create/update that doesn't help when it comes to deletes ...

[jmls]

one option (in postgres at least) would be to use the set session / set role capabilities to the user that you want to store in the audit log, (which requires that the role be already defined in pg - I'm good with that) - but I can't find any way of adding that to the sql sent to the db

[mgcox2]

I think I found a solution that appears to work in my project. I have the authenticated user set on my request object and passed into my service that saves some data. I created a property on my entity, metadataUserContext: User, with the @column() decorator omitted so the value isn't saved to the database. When I perform a save on the entity I can set the metadataUserContext property that it now has and it gets passed into my subscriber methods. Seems to work great for my use case after some cursory testing. Hope it helps someone.

[kswierszczyk]

I think I found a solution that appears to work in my project. I have the authenticated user set on my request object and passed into my service that saves some data. I created a property on my entity, metadataUserContext: User, with the @column() decorator omitted so the value isn't saved to the database. When I perform a save on the entity I can set the metadataUserContext property that it now has and it gets passed into my subscriber methods. Seems to work great for my use case after some cursory testing. Hope it helps someone.

Hey, this was the exact solution I came up with as well! I provided a psuedo-sample code example at the end of this post. Apologies for not updating this w/ a solution sooner -- appreciate you doing so.

My solution was exactly as mgcox described: I added additional properties to my Entity, omitting Column decorators, for any properties that I wanted to have passed along to listeners/subscribers which were not included in the Entity db schema/table. So then it's just a matter of setting the property values whenever you mutate the Entity, and upon calling Save/Update, the assigned audit value will be passed into your listeners! This worked out great.

Also worth noting- if you're auditing using event listeners like this in a Serverless environment (such as AWS lambdas) you MUST remember to set the lambda handler's lifecycle configuration to stay alive until the javascript event loop is fully complete. This must be done on any API/handler that, in it's call chain, fires an event which a subscriber is listening to, such as user.Save(). In AWS lambda this can be done by setting context.callbackWaitsForEmptyEventLoop = true; in the handler.

Otherwise (the default value being false) your lambda will update the user but before the user audit subscriber has a chance to act on the event, the lambda will spin down, and only when that lambda/handler is called a next time will the previous event loop complete, which means your audits are always an API call behind and obviously very buggy.

SAMPLE PSUEDO-CODE -- sorry been on a brief departure from web dev

So let's say whenever you save/update a User, you also want to audit what the client's current browser/user agent is.

-- TYPEORM ENTITY
-- Entities/User.ts

@Entity()
public class User {
  @PrimaryGeneratedColumn("uuid")
  id : string;

  @Column(...)
  username : string;

  @Column(...)
  hashPassword : string;
  
  // the property we want to add to our audits, notice no decorator/attribute
  audit_clientUserAgent : string;
}

-- API: POST/PUT /user 
-- api/handlers/updateUserHandler.ts

exports.handler = async (event, context) => {
    
   // because this is serverless, we must set lifecycle if we want an event
   // subscriber to be able to get invoked in this lambda context.
    context.callbackWaitsForEmptyEventLoop = true;

    UpdateUserRequest updatedUser = JSON.parse(event.body);

    if (updatedUser?.IsValid()) {
      const user = User.Create({ ...updatedUser });
      user.audit_clientUserAgent = request.context.useragent;

      await this.userRepository.Save(user); // or user.Save()
      
      return { statusCode: 200, message: 'Updated user!' }
    }
}

-- AUDIT SUBSCRIBER/LISTENER:
-- Subscribers/UserAuditSubscriber.ts 

@EventSubscriber()
export class UserAuditSubscriber 
  implements EntitySubscriberInterface<User> {

    listenTo() => User;

    beforeUpdate(event: UpdateEvent<User>) {
        
       // the event object here contains the audit_clientUserAgent property
       // from which you assigned earlier in your API handler.
       // I don't recall exact prop names, but assuming it's event.entity:

      const { entity } = event;

      this.userAuditRepository.Save({
          userId: entity.id,
          clientBrowser: entity.audit_clientUserAgent
      });
}

[mgcox2]

@rahabash Great minds think alike. Thanks for updating with more details. I think we can consider this issue closed as far as I'm concerned.

[mustofa-id]

SAMPLE PSUEDO-CODE -- sorry been on a brief departure from web dev

So let's say whenever you save/update a User, you also want to audit what the client's current browser/user agent is.

-- TYPEORM ENTITY
-- Entities/User.ts

@Entity()
public class User {
  @PrimaryGeneratedColumn("uuid")
  id : string;

  @Column(...)
  username : string;

  @Column(...)
  hashPassword : string;
  
  // the property we want to add to our audits, notice no decorator/attribute
  audit_clientUserAgent : string;
}

-- API: POST/PUT /user 
-- api/handlers/updateUserHandler.ts

exports.handler = async (event, context) => {
    
   // because this is serverless, we must set lifecycle if we want an event
   // subscriber to be able to get invoked in this lambda context.
    context.callbackWaitsForEmptyEventLoop = true;

    UpdateUserRequest updatedUser = JSON.parse(event.body);

    if (updatedUser?.IsValid()) {
      const user = User.Create({ ...updatedUser });
      user.audit_clientUserAgent = request.context.useragent;

      await this.userRepository.Save(user); // or user.Save()
      
      return { statusCode: 200, message: 'Updated user!' }
    }
}

-- AUDIT SUBSCRIBER/LISTENER:
-- Subscribers/UserAuditSubscriber.ts 

@EventSubscriber()
export class UserAuditSubscriber 
  implements EntitySubscriberInterface<User> {

    listenTo() => User;

    beforeUpdate(event: UpdateEvent<User>) {
        
       // the event object here contains the audit_clientUserAgent property
       // from which you assigned earlier in your API handler.
       // I don't recall exact prop names, but assuming it's event.entity:

      const { entity } = event;

      this.userAuditRepository.Save({
          userId: entity.id,
          clientBrowser: entity.audit_clientUserAgent
      });
}

@rahabash this method can't be used if we don't have object of entity, for example when we just need to add relation using query builder like:

manager.createQueryBuilder(User, 'u')
      .relation('roles')
      .of(userId)
      .add(roleId)

[polosatyi]

@rahabash @mustofa-id
Also seems like this method can't be used if use operations like this:

this.repository.update({
    columnToUpdate: value,
});