AutoCRUD Preview

Announcements News
27

I’d like to introduce another value-added AutoCrud feature that I’m pretty excited about and happy to hear any feedback on missing functionality from anyone else who’s interested in using this feature, the best name of which I can come up with is “Executable Crud Audit Events”.

Quick Event Sourcing Overview

A little background first, Event Sourcing is a fairly popular approach for designing systems where instead of modifying normalized tables directly, all mutations would be captured as an “Event” that would be written to an “Event Store” who’d send it to its consumers who would act on those events, to, for example write to a materialized view that’s optimized to serve its read only system queries. Some differences with normal RDBMS development is that the Event Store is the master authority of your Data which would let you rebuild your RDBMS from scratch by replaying all events through your system and have it re-create your materialized views.

But I’ve never considered using ES due to the additional complexity it adds to a system, e.g. requires more infrastructure and moving parts, more dev overhead & additional artifacts, hurts productivity as system features would need to be routed through your systems ES funnel, can no longer rely on ACID properties of having an “always correct RDBMS state” instead you’d need to design system features to account for Eventual Consistency.

But being able to rebuild your Systems DB by replaying all system events is pretty a nice property where you can more easily redesign your RDBMS to more easily remove legacy schema designs and allow you to optimize your RDBMS design for querying, it also makes it easier to move to a NoSQL DB as being able to publish denormalized data lessens the reliance on needing RDBMS’s relational features.

As data is the most important part of most systems I prefer to keep a lot of Audit history, e.g. when items were created, modified and deleted (and by whom). Typically this means I’ll use “Soft Delete” and “Append Only” approaches to be able to maintain a history of changes to important data.

Executable Crud Audit Events

This feature tries to obtain some of the nice features of ES but without the additional complexity by allowing you to capture all CRUD operations in an executable log whilst still retaining your RDBMS as your master authority. This feature doesn’t require any additional dev overhead as your AutoCrud Request DTOs are the recorded events.

To enable this feature you just need to register an ICrudEvents provider which will let you persist your events in any data store, but typically you’d use OrmLiteCrudEvents to persist it in the same RDBMS that the AutoCrud requests are already writing to, e.g:

container.AddSingleton<ICrudEvents>(c =>
    new OrmLiteCrudEvents(c.Resolve<IDbConnectionFactory>()) {
        // NamedConnections = { SystemDatabases.Reporting }
    });
container.Resolve<ICrudEvents>().InitSchema();

If you’re using Multitenancy features or multiple RDBMS’s in your AutoCrud DTOs you can add them to NamedConnections where it will create an CrudEvent table in each of the RDBMS’s used.

and that’s all that’s required, now every AutoCrud operation will persist the Request DTO and associative metadata in the Event entry below within a DB transaction:

public class CrudEvent : IMeta
{
    [AutoIncrement]
    public long Id { get; set; }    
    // AutoCrudOperation, e.g. Create, Update, Patch, Delete, Save
    public string EventType { get; set; }    
    public string Model { get; set; }         // DB Model Name    
    public string ModelId { get; set; }       // Primary Key of DB Model
    public DateTime EventDate { get; set; }   // UTC
    public long? RowsUpdated { get; set; }    // How many rows were affected
    public string RequestType { get; set; }   // Request DTO Type    
    public string RequestBody { get; set; }   // Serialized Request Body    
    public string UserAuthId { get; set; }    // UserAuthId if Authenticated    
    public string UserAuthName { get; set; }  // UserName or unique User Identity
    public string RemoteIp { get; set; }      // Remote IP of the Request
    public string Urn { get; set; }  // URN format: urn:{requesttype}:{ModelId}

    // Custom Reference Data with or with non-integer Primary Key
    public int? RefId { get; set; }
    public string RefIdStr { get; set; }
    public Dictionary<string, string> Meta { get; set; }
}

Full Executable Audit History

With what’s captured this will serve as an Audit History of state changes for any row by querying the Model & ModelId columns, e.g:

var dbEvents = (OrmLiteCrudEvents)container.Resolve<ICrudEvents>();
var rowAuditEvents = dbEvents.GetEvents(Db, nameof(Rockstar), id);

The contents of the Request DTO stored as JSON in RequestBody. You can quickly display the contents of any JSON in human-friendly HTML with the htmlDump script if you’re using #Script, @Html.HtmlDump(obj) if you’re using Razor or just the static ViewUtils.HtmlDump(obj) method to get a raw pretty-formatted HTML String.

Replay AutoCrud Requests

If all your database was created with AutoCrud Services you could delete its rows and re-create it by just re-playing all your AutoCrud DTOs in the order they were executed, which can be done with:

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

The CrudEventsExecutor uses your AppHost’s ServiceController to execute the message, e,g. same execution pipeline MQ Requests use, so it will execute your AppHost’s GlobalMessageRequestFilters/Async if you have any custom logic in Request Filters (e.g. Multi TenantId example above). It also executes authenticated AutoCrud requests as the original AutoCrud Request Authenticated User, which just like JWT Refresh Tokens
will require either using an AuthRepository or if you’re using a Custom Auth Provider you can implement an IUserSessionSource to load User Sessions from a custom data store.

When replaying the Audit Events it will use the original primary key, even if you’re using [AutoIncrement] Primary Keys, this will let you re-create the state of a single entry, e.g:

db.DeleteById<Rockstar>(id);
var rowAuditEvents = dbEvents.GetEvents(Db, nameof(Rockstar), id);
foreach (var crudEvent in rowAuditEvents)
{
    await eventsPlayer.ExecuteAsync(crudEvent);
}

If for instance you wanted it to execute through your latest logic with any enhancements or bug fixes, etc.

This feature is now available in the latest v5.8.1 on MyGet if you want to try it out early, feedback welcome!

3 Likes