June 2016

Volume 31 Number 6

[Cutting Edge]

Building an Historical CRUD, Part 2

By Dino Esposito | June 2016

Conceptually speaking, an historical Create, Read, Update, Delete (CRUD) is the classic CRUD extended with an additional parameter: a date. An historical CRUD lets you add, update and delete records from a database and lets you query the state of the database at a given point in time. An historical CRUD gives your applications a built-in infrastructure for business intelligence analysis and advanced reporting features.

In last month’s column (msdn.com/magazine/mt703431), I introduced the theoretical foundation of historical CRUD systems. In this article, I’ll give a practical demonstration.

Presenting the Sample Scenario

For the purposes of this article, I’ll consider a simple booking system. For example, take the system a company uses internally to let employees book meeting rooms. In the end, such software is a plain CRUD where a new record is created to reserve a slot. The same record is updated if the meeting is moved to a different time or deleted if the meeting is canceled.

If you code such a booking system as a regular CRUD, you know the latest state of the system, but lose any information about updated and deleted meetings. Is this really a problem? It depends. It’s probably not a problem if you simply look at the affects of meetings on the actual business. However, if you’re looking for ways to improve the overall performance of employees, then an historical CRUD that tracks updates and deletions of records might reveal that too many times meetings are moved or canceled and that could be the sign of less-than-optimal internal processes or bad attitude.

Figure 1 presents a realistic UI for a room-booking system. The underlying database is a SQL Server database with a couple of linked tables: Rooms and Bookings.

Figure 1 The Front-End UI for a Booking System

The sample application is designed as an ASP.NET MVC application. When the user clicks to place the request, a controller method kicks in and processes the posted information. The following code snippet gives a clear idea of the code handling the request on the server side:

[HttpPost]
public ActionResult Add(RoomRequest room)
{
  service.AddBooking(room); 
  return RedirectToAction("index", "home");
}

The method belongs to a BookingController class and delegates to an injected worker service class the burden of organizing the actual work. An interesting aspect of the method’s implementation is that it redirects to the front page in Figure 1 after creating the booking. There’s no explicit view being created as the result of the add-booking operation. This is a side effect of choosing a Command Query Responsibility Segregation (CQRS) architecture. The add-booking command is posted to the back end; it alters the state of the system and that’s it. Had the sample application used AJAX to post, there would’ve been no need to refresh anything and the command would’ve been a standalone operation with no visible link to the UI.

The core difference between a classic CRUD and an historical CRUD is that the latter keeps track of all operations that alter the state of the system since the beginning. To plan an historical CRUD, you should think of business operations as commands you give to the system and of a mechanism to track those commands down. Each command alters the state of the system and an historical CRUD keeps track of each state the system reaches. Any reached state is logged as an event. An event is the mere and immutable description of something that has happened. Once you have the list of events, you can create multiple projections of data on top of it, the most popular of which is just the current status of involved business entities.

In an application, events originate directly from the execution of user commands or indirectly from other commands or external input. In this sample scenario, you expect the user to click a button to post a booking request.

Processing the Command

Here’s a possible implementation of the AddBooking method of the application’s controller:

public void AddBooking(RoomRequest request)
{
  var command = new RequestBookingCommand(request);
  var saga = new BookingSaga();
  var response = saga.AddBooking(command);
  // Do something based on the outcome of the command
}

The RoomRequest class is a plain data-transfer object populated by the ASP.NET MVC binding layer out of posted data. The RequestBookingCommand class, instead, stores the input parameters required to execute the command. In such a simple scenario, the two classes nearly coincide. How would you process the command? Figure 2 presents the key three steps of processing a command.

Figure 2 The Chain of Core Steps to Process a Command

The handler is a component that receives the command and processes it. A handler can be invoked through a direct in-memory call from within the worker service code or you can have a bus in the middle, as shown here:

public void AddBooking(RoomRequest request)
{
  var command = new RequestBookingCommand(request);
  // Place the command on the bus for
  // registered components to pick it up
  BookingApplication.Bus.Send(command);
}

A bus might bring a couple of benefits. One is that you can easily handle scenarios in which multiple handlers might be interested in the same command. Another benefit is that a bus might be configured to be a reliable messaging tool that ensures the delivery of the message over time and overcoming possible connectivity issues. In addition, a bus can just be the component that offers the ability to log the command.

The handler might be a simple one-off component that starts and ends in the same request or it can be a long-running workflow that takes hours or days to complete and may be suspended waiting for human approval at some point. Handlers that are not simple one-off task executors are often called sagas.

In general, you use a bus or a queue if you have specific requirements in terms of scalability and reliability. If you’re just looking for building an historical CRUD in lieu of a classic CRUD, you probably don’t need to use a bus. Whether you use a bus or not, at some point the command might reach its one-off or long-running handler. The handler is supposed to carry out whatever tasks are expected. Most tasks consist of core operations on a database.

Logging the Command

In a classic CRUD, writing information to a database would mean adding a record that lays out the values passed in. In an historical CRUD perspective, though, the newly added record represents the created event of a booking. The created event of a booking is an independent and immutable piece of information that includes a unique identifier for the event, a timestamp, a name and a list of arguments specific of the event. The arguments of a created event typically include all the columns you would fill in for a newly added Booking record in a classic Bookings table. The arguments of an updated event, instead, are limited to the fields that are actually updated. Subsequently, all updated events might not have the same content. Finally, the arguments of a deleted event are limited to the values that uniquely identify the booking.

Any operation of an historical CRUD is made up of two steps:

1. Log the event and its related data.
2. Ensure the current state of the system is immediately and quickly queryable.

In this way, the current state of the system is always available and up-to-date and all the operations that led to it are also available for any further analysis. Note that the “current state of the system” is just the only state you see in a classic CRUD system. To be effective in the context of a simple CRUD system, the step of logging the event and updating the state of the system should take place synchronously and within the same transaction, as shown in Figure 3. 

Figure 3 Logging an Event and Updating the System

using (var tx = new TransactionScope())
{
  // Create the "regular" booking in the Bookings table   
  var booking = _bookingRepository.AddBooking(
    command.RoomId, ...);
  if (booking == null)
  {
    tx.Dispose();   
    return CommandResponse.Fail;
  }
  // Track that a booking was created
  var eventToLog = command.ToEvent(booking.Id);
    eventRepository.Store(eventToLog);
  tx.Complete();
  return CommandResponse.Ok;
}

As things are, every time you add, edit or delete a booking record you maintain the overall list of bookings up-to-date while knowing the exact sequence of events that led to the current state. Figure 4 shows the two SQL Server tables involved in the sample scenario and their content after an insert and update.

Figure 4 Bookings and LoggedEvents Tables Side by Side

The Bookings table lists all distinct bookings found in the system and for each returns the current state. The LoggedEvents table lists all the events for the various bookings in the order they were recorded. For example, the booking 54 has been created on a given date and modified a few days later. In the example, the Cargo column in the picture stores the JSON serialized stream of the command being executed.

Using Logged Events in the UI

Let’s say that an authorized user wants to see the details of a pending booking. Probably the user will get to the booking from a calendar list or through a time-based query. In both cases, the fundamental facts of the booking—when, how long and who—are already known and the details view might even be of little use. It might really be helpful, though, if you could show the entire history of the booking, as shown in Figure 5.

Figure 5 Consuming Logged Events in the UI

By reading through the logged events, you can build a view model that includes a list of states for the same aggregate entity—booking #54. In the sample application, when the user clicks to view the details of the booking a modal popup appears and some JSON is downloaded in the background. The endpoint that returns the JSON is shown here:

public JsonResult BookingHistory(int id)
{
  var history = _service.History(id);
  var dto = history.ToJavaScriptSlotHistory();
  return Json(dto, JsonRequestBehavior.AllowGet);
}

The History method on the worker service does most of the work here. The core part of this work is querying all events that related to the specified booking ID:

var events = new EventRepository().All(aggregateId);
foreach (var e in events)
{
  var slot = new SlotInfo();
  switch (e.Action)
  {
    :
  }
  history.Changelist.Add(slot);
}

As you loop through the logged events, an appropriate object is appended to the data-transfer object to be returned. Some transformation performed in the ToJavaScriptSlotHistory makes it quick and easy to display the delta between two consecutive states in the form you see in Figure 5.

It’s remarkable, though, that while logging events even within a CRUD allows for such nice improvements in the UI, the largest value lies in the fact that you now know everything that ever happened within the system and can process that data to extract any custom projection of data you might need at some point. For example, you might create a statistics of the update and let analysts come to the conclusion that the entire process of requesting meeting rooms doesn’t work in the company because people too often book and then update or delete. You can also easily track down what the situation was of the bookings at a specific date by simply querying events logged until then and calculating the subsequent state of things. In a nutshell, an historical CRUD opens up a whole new world of possibilities for applications.

Wrapping Up

Historical CRUD is simply a smarter way of evolving plain CRUD applications. Yet, this discussion touched on buzzwords and patterns that have a lot more potential, such as CQRS, event sourcing, bus and queues, and message-based business logic. If you found this article helpful, I suggest you go back and read my July 2015 (msdn.com/magazine/mt238399) and August 2015 (msdn.com/magazine/mt185569) columns. In light of this example, you might find those articles even more inspiring!

Dino Esposito is the author of “Microsoft .NET: Architecting Applications for the Enterprise” (Microsoft Press, 2014) and “Modern Web Applications with ASP.NET” (Microsoft Press, 2016). A technical evangelist for the .NET and Android platforms at JetBrains, and frequent speaker at industry events worldwide, Esposito shares his vision of software at software2cents@wordpress.com and on Twitter: @despos.

Thanks to the following Microsoft technical expert for reviewing this article: Jon Arne Saeteras

Discuss this article in the MSDN Magazine forum