CODEWITHSUNDEEP
aws-lambdaamazon-dynamodbaudit-logging
Shafi
Shafi

Reputation: 1970

What is the best approach to store audit-log / change-history in dynamodb?

The purpose is to build a Audit Log service to store change history for an application. The following information will be stored:

 - srcId
 - srcTable
 - changedAt
 - changes
 - user

For example, for the username change of id = 10 from profile table will be saved like

srcId = 10,
srcTable = 'profile',
changedAt = timestamp,
changes: { name: { old: old-value, new: new-value } },
user: the-user-id-who-performed-the-change-action

Trying to build the system based on aws lambda, dynamodb.

Initially thought to use srcId as sort-key, srcTable as partition key. But as there can be multiple entries for same srcId/srcTable pair, where the changedAt will be different. Any suggestion on how the index or primary key should be set for better performance gain.

P.S. expected queries

(percentage values are roughly perceived about the expected behaviour)

Upvotes: 0

Views: 3157

Answers (1)

Noel Llevares
Noel Llevares

Reputation: 16037

It would be best that your Table PK have a high cardinality. Based on your examples, you could achieve that by creating a computed column on the srcTable:srcId combination (I prefer using : instead of #) and then use your timestamp as your sort key.

This achieves the primary query pattern which is to find the change list for a given srcTable and srcId. Using the timestamp will allow you to query the table based on date ranges which in my experience is a common use case for audit logs.

Table (Get change history for <srcTable>:<srcId>):

  • PK - srcTable:srcId
  • SK - changedAt

GSI1 (Get change history for <srcTable>):

  • PK - srcTable
  • SK - changedAt

GSI2 (Get changes performed by <user>):

  • PK - user
  • SK - changedAt

Your items may look similar to this:

PK (srcTable:srcId) SK (<changedAt>) srcTable srcId userId changes
profile:10 2021-11-25T06:02:27.163Z profile 10 1 {}
user:1 2021-11-25T06:03:09.811Z user 1 1 {}
profile:12 2021-11-25T06:04:17.178Z profile 12 4 {}

You might have looked at this already but for the sake of future readers, I would highly suggest watching or following Rick Houlihan's videos on Advanced DynamoDB modeling. Another great resource is Alex DeBrie's DynamoDB Book.

Upvotes: 1

Related Questions