Auditing

A benefit to AutoQuery's structured declarative approach to its CRUD APIs is that it's better able to enable high-level generic functionality that can benefit all CRUD APIs. AutoQuery CRUD's Executable Audit Log is an example of this which makes use of AutoQuery CRUD Attributes to capture every CRUD operation responsible for any modifications to its underlying RDBMS tables.

We'll explore an overview of this feature by applying it to our simple Bookings table from the AutoQuery CRUD Bookings Demo included in all jamstacks.net project templates there by adding the ability to track all CRUD API operations and with it all modifications made to our Booking RDBMS table.

Enabling Crud Events​

First thing we need to do is register the ICrudEvents dependency in our App's IOC, in this case uses OrmLiteCrudEvents to store all Audit Information in the CrudEvent table of our configured database:

public class ConfigureAutoQuery : IHostingStartup
{
    public void Configure(IWebHostBuilder builder) => builder
        .ConfigureServices(services => {
            // Enable Audit History
            services.AddSingleton<ICrudEvents>(c =>
                new OrmLiteCrudEvents(c.Resolve<IDbConnectionFactory>()));
        })
        .ConfigureAppHost(appHost => {
            appHost.Plugins.Add(new AutoQueryFeature {
                MaxLimit = 1000,
                //IncludeTotal = true,
            });
            
            // Create CrudEvent if it doesn't exist
            appHost.Resolve<ICrudEvents>().InitSchema();
        });
}

As ICrudEvents stores all events in a separate table, we also need to use InitSchema above to create the CrudEvent table if it doesn't already exist.

Enabling Audit History Tracking on Data Models and APIs​

With ICrudEvents registered, we can now choose which Data Models we want to enable Audit Tracking on by having them inherit from the built-in AuditBase class:

public class Booking : AuditBase
{
    [AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }
    public RoomType RoomType { get; set; }
    public int RoomNumber { get; set; }
    public DateTime BookingStartDate { get; set; }
    public DateTime? BookingEndDate { get; set; }
    public decimal Cost { get; set; }
    public string Notes { get; set; }
    public bool? Cancelled { get; set; }
}

This will extend our RDBMS tables with additional Audit Info to capture who and when bookings were Created, Last Modified and Deleted (when using Soft Deletes):

public abstract class AuditBase
{
    public DateTime CreatedDate { get; set; }
    public string CreatedBy { get; set; }
    public DateTime ModifiedDate { get; set; }
    public string ModifiedBy { get; set; }
    public DateTime? DeletedDate { get; set; }
    public string DeletedBy { get; set; }
}

Even without needing to inspect the audit history table, capturing this info on its own provides valuable insight into the provenance of each booking.

Configuring CRUD APIs​

But to be able to maintain a complete executable audit log we need to capture every CRUD API and modification done on our Booking table which we can do by annotating our Booking CRUD APIs with AutoApply attribute which annotates our APIs with the behavior we want to apply to them.

For the Audit feature, this behavior is implemented in the pre-registered AuditAutoCrudMetadataFilter which dynamically adds attributes to annotated APIs to achieve it's desired behavior. By primarily using the [AutoPopulate] attribute to populate the Audit Info and [AutoFilter] attribute to ensure our Query APIs don't return any Soft Deleted records.

Essentially [AutoApply] enables us to define a single attribute as a substitute for defining the multiple compound attributes to populate the Audit Info and Filter queries.

The appropriate [AutoApply] behavior needs to be added on all the CRUD APIs for the data models we want Audit History tracking enabled on, e.g:

[AutoApply(Behavior.AuditQuery)]
public class QueryBookings : QueryDb<Booking>
{
    //...
}

[AutoApply(Behavior.AuditCreate)]
public class CreateBooking
    : ICreateDb<Booking>, IReturn<IdResponse>
{
    //...
}

[AutoApply(Behavior.AuditModify)]
public class UpdateBooking
    : IPatchDb<Booking>, IReturn<IdResponse>
{
    //...
}

[AutoApply(Behavior.AuditSoftDelete)]
public class DeleteBooking : IDeleteDb<Booking>, IReturnVoid
{
    //...
}

TIP

Use Behavior.AuditDelete instead if you prefer your delete operations resulted in permanent hard deletes

Audit Info in Locode​

The AutoQueryFeature.AccessRole determines the accessibility of the CRUD Event APIs that Locode uses to display the Audit History logs for each entity, which by default is restricted to Admin users who will be able to view the Audit History of each record at the bottom of its Edit Form.

With our Bookings CRUD APIs now configured with Audit behavior we can see an example of what this looks like in Locode after the Employee User account records a Booking from John Smith for a Single room:

With the left section displaying audit information about the CRUD operation and the User making it including their UserName, Id and IP. The right section contains the info sent in the Request DTO, in this case the CreateBooking API.

If John Smith later contacts the manager to upgrade his booking to a Suite, the Audit information will be updated with the UpdateBooking Audit entry which as it is a IPatchDb<Table> operation, only contains information that's changed:

This is typically why the behavior of IPatchDb<Table> is preferable over IUpdateDb<Table> APIs when Audit Tracking is enabled as otherwise each Update operation would instead contain the entire entry on each update.

Full Executable Audit History​

As each Audit Entry contains the CRUD Request DTO, they can be used to recreate the state of the database by replaying each event & executing them against a blank database:

var eventsPlayer = new CrudEventsExecutor(appHost);
foreach (var crudEvent in dbEvents.GetEvents(db))
{
    await eventsPlayer.ExecuteAsync(crudEvent);
}

This can provide similar benefits to Event Sourcing without its additional development & maintenance burden where they can be used to reconstruct the state of the database at a specific point in time or used to populate external data stores like search indexes or analytical reporting databases to enable greater insight than what an RDBMS snapshot can provide.

You could replay all events to reconstruct the entire state of the database, or choose just the tables you want to rebuild where you can fetch all Audit Events for just the Booking table with:

var bookingAuditEvents = dbEvents.GetEvents(Db, nameof(Booking));

Alternatively you can fetch all the audit events for a single row which is what Locode uses to display its Audit Events:

var rowAuditEvents = dbEvents.GetEvents(Db, nameof(Booking), id);

Complete Bookings CRUD Implementation​

The Bookings CRUD Demo is a good representative example of the effort it takes to implement a traditional CRUD API with AutoQuery:

Which reduces the development to effort of creating Full Stack Apps down to declaring your Data Models and APIs with simple POCOs to define the precise schema of the underlying RDBMS tables and API contract, that can then benefit from:

And access to ServiceStack's rich ecosystem of features, most of which are centered around your typed API contracts making them easy to enhance & apply to your existing Services.

All without needing to write a single line of implementation logic thanks to the default implementation in AutoQuery Services & Auto UIs in Locode, API Explorer & Swagger UI. At the same time when needed the default behavior can be overridden at multiple levels, from custom AutoQuery implementations on the server to custom UIs on the client.

For completeness, the entire source code used to implement the Bookings CRUD implementation is below:

public class Booking : AuditBase
{
    [AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }
    public RoomType RoomType { get; set; }
    public int RoomNumber { get; set; }
    public DateTime BookingStartDate { get; set; }
    public DateTime? BookingEndDate { get; set; }
    public decimal Cost { get; set; }
    public string Notes { get; set; }
    public bool? Cancelled { get; set; }
}

public enum RoomType
{
    Single,
    Double,
    Queen,
    Twin,
    Suite,
}

[AutoApply(Behavior.AuditQuery)]
public class QueryBookings : QueryDb<Booking>
{
    public int[] Ids { get; set; }
}

[ValidateHasRole("Employee")]
[AutoApply(Behavior.AuditCreate)]
public class CreateBooking
    : ICreateDb<Booking>, IReturn<IdResponse>
{
    public string Name { get; set; }
    public RoomType RoomType { get; set; }
    [ValidateGreaterThan(0)]
    public int RoomNumber { get; set; }
    public DateTime BookingStartDate { get; set; }
    public DateTime? BookingEndDate { get; set; }
    [ValidateGreaterThan(0)]
    public decimal Cost { get; set; }
    public string Notes { get; set; }
}

[ValidateHasRole("Employee")]
[AutoApply(Behavior.AuditModify)]
public class UpdateBooking
    : IPatchDb<Booking>, IReturn<IdResponse>
{
    public int Id { get; set; }
    public string Name { get; set; }
    public RoomType? RoomType { get; set; }
    [ValidateGreaterThan(0)]
    public int? RoomNumber { get; set; }
    public DateTime? BookingStartDate { get; set; }
    public DateTime? BookingEndDate { get; set; }
    [ValidateGreaterThan(0)]
    public decimal? Cost { get; set; }
    public bool? Cancelled { get; set; }
    public string Notes { get; set; }
}

[ValidateHasRole("Manager")]
[AutoApply(Behavior.AuditSoftDelete)]
public class DeleteBooking : IDeleteDb<Booking>, IReturnVoid
{
    public int Id { get; set; }
}
Edit this page on GitHub