How to implement Audit in a database

When designing and developing a large system, it is essential to consider and implement audit functionality. This means tracking when data is created, updated, deleted, or even accessed, and by which user.

Audit data is crucial for privacy compliance and regulatory reasons, but it also helps identify inconsistencies and inaccuracies. Additionally, it is invaluable for debugging and troubleshooting.

In this blog 3 possible implementations and their pros en cons are evaluated

The implementation of audit functionality is the same in OutSystems 11 and OutSystems Developer Cloud (ODC). This blog will use ODC examples.

Defining an Audit Strategy

 At the start of a project, it is important to define a proper approach for auditing data. Ideally, core entities that change frequently (such as Orders) should have audit functionality. On the other hand, master data entities (such as Countries or ProductTypes) may not require auditing if they rarely change.

Implementing audit functionality from the beginning of a project is also beneficial during development, as it helps in debugging and trouble shooting. Implementing it in a later stage can be done, but is less efficient. .

It is recommended to use a consistent audit approach across the system. Mixing multiple audit implementations can lead to confusion and complexity.

For very small applications that will only be used for a few months, audit functionality might not be necessary. However, in most cases, auditing is required.

1 Audit Within the Same Table

In this approach, audit-related attributes are added directly to the core entity. These attributes include:

• CreatedDate
• CreatedBy
• UpdatedDate
• UpdatedBy
• IsActive

Don’t use the userid for CreatedBy and UpdatedBy, but the Username. Otherwise users can not be deleted.

How It Works:

• When a record is created, CreatedDate and CreatedBy are set.
• When a record is updated, UpdatedDate and UpdatedBy are updated.
• When a record is deleted, UpdatedDate and UpdatedBy are updated, and IsActive is set to False. In this situation, a soft-delete is created. The record stays in the database, but must be excluded in every aggregate. Be careful with using soft deletes, because they have performance consequences. Unique keys are not reusable.

Pro’s

• No need for additional tables—everything is stored in the same entity. There will be less AO’s

Cons

• Only the last update is logged (no history of previous changes).
• It is not possible to track which specific attributes were modified.
• If soft deletes are allowed, all aggregates and queries must check IsActive = True. (In ODC, this is mostly handled automatically.)

2 Audit in a Separate Table, Logging All Attributes

This method involves creating a separate audit entity that mirrors the core entity. The audit entity is named with an _Audit suffix. For example, alongside the Order entity, there will be an Order_Audit entity. Foreign keys in the _Audit entity are removed and replace by plain long-integers.

Additional attributes in the audit entity:

• OriginalID (to reference the core record)
• Action (Create, Update, Delete)
• Username
• DateTime

How It Works:

• Each time a record is created, updated, or deleted, a new entry is added to the audit table.
• The audit entry contains all attribute values, along with the action type, username, and timestamp.
• This can be implemented within the entity’s CRUD actions, avoiding the need for manual logging in multiple places.

Handling Foreign Keys:

When an attribute in the core entity is a foreign key, a decision must be made. Storing the ID is an option, but if the referenced record is deleted, the ID becomes meaningless. In such cases, storing an alternate key (such as a OrderNumber instead of OrderId) is preferable.

Pro’s

• The core entity remains focused on business data, while audit logs are kept separately. Possible the audit can be made accessible only for the complianceteam.
• Every change is logged, making it possible to see exactly who changed what and when.
• Deleted records can be hard-deleted without affecting audit logs.
• Queries can be performed on the audit data.

Con’s

• Requires an additional table, the AO’s will rise.
• Audit tables can grow large, requiring periodic cleanup.
• Foreign keys need careful handling to ensure meaningful audit data

3 Audit in a Separate Table Using JSON

This approach is similar to option 2 but stores the entire record as a JSON structure instead of individual attributes.

The audit entity has the following fields:

• OriginalID
• Action
• Username
• DateTime
• Data (a serialized JSON representation of the record)
• A reference to a static entity which defines the original entity (if more entities are audited in 1 audit entity)

How it works:

• Each time a record is created, updated, or deleted, a new entry is added to the audit table.
• The entire record is serialized and stored as a JSON object.
• The implementation is done in the CRUD actions, similar to option 2.

Pro’s

• Keeps audit logs separate from the core entity.
• New attributes in the core entity do not require changes to the audit entity.
• Deleted records can be hard-deleted without affecting audit data.
• JSON comparison tools (like JsonCompare) can be used to detect differences between versions.
• Easier to create screens for displaying audit data compared to option 2.

Con’s

• Requires an additional table. To reduce the number of tables, multiple entities with infrequent changes can be audited in a single audit table with an entity identifier, so in that case there will be less rise in the number of AO’s
• Audit tables can grow large, requiring periodic cleanup.
• Querying specific changes is difficult since the data is stored in JSON format.

Implementation best practices

Audit functionality should be implemented within the CRUD actions of an entity.

Performance:

• For a better performance, when selecting form audit entity (option 2 and 3) it is the best practice to create an index on the OriginalID and the date.

Best Practices in OutSystems:

• In OutSystems 11, it was common to set entities as “Expose Read-Only” and use public CRUD server actions for Create, Update, and Delete.
• In ODC, updates can be done directly on the entity, but it is still recommended to use CRUD server actions. This allows for centralized audit logging and other enhancements, such as automatically setting default attribute values.
• Both O11 and ODC have the option to easily create the CRUD actions for an entity.

Handling Record Deletions:

In both JSON-based and attribute-based audit logging (options 2 and 3), the OriginalID of the record is stored. However, if the core entity allows hard deletes, the ID may become meaningless.

A better approach is to store the OriginalAlternateKey instead of the ID. This ensures that audit data remains meaningful even if the original record is deleted, such as a OrderNumber instead of OrderId.

Conclusion

Audit functionality is an essential part of database design, helping with compliance, debugging, and data integrity. There are multiple ways to implement auditing:

• Within the same table (simple but limited).
• In a separate table, logging all attributes (provides complete history and detailed insights).
• In a separate table, using JSON (more flexible but harder to query).

Choosing the right approach depends on system requirements, data sensitivity, and expected query needs. Regardless of the method chosen, auditing should be consistently applied across the system to ensure reliable and maintainable data tracking.

Auditing is key for data integrity and compliance. In OutSystems Developer Cloud (ODC), new options make auditing more scalable and flexible. Want to learn the best approach for your project?

Join my ODC Architecture Training and explore best practices, performance tips, and advanced strategies!