Businesses, particularly those in the regulatory, FinTech, IoT or data analysis industries, often need to reference specific date / time when reviewing information. For example

• When investigating a potential loan fraud for a loan that was approved in March 2022, the bank would need to determine the credit rating of the individual as of January 31, 2022, as known by the bank on March 15th, 2022 when the approval decision was made.
• An IoT Smart Meter recorded a sensor reading at 10 am on December 20, 2022, but the reading was not actually recorded by the system until 12 pm due to a network delay. This resulted in a delay of two hours in reporting the breach.

The above situations can be answered by a bi-temporal systems of record that track data against two different time axes — when the event actually happened and when it was recorded in the system.

In this blog, I explore various temporal data patterns and how you can use MongoDB for handling temporal data. I have built a simple wrapper solution that would allow you to store and retrieve bi-temporal data without having to worry about the intricacies of bi-temporal access patterns.

Temporality

Databases, be in relational or document oriented, have long been used as systems of record. However based on the temporal data being recorded or lack thereof, we can classify systems into

1. Non-temporal
2. Uni-temporal
3. Bi-temporal

Let’s explore above by taking the example of an IoT system where a water level sensor is reporting data every minute.

Non-temporal

Uni-temporal

Bi-temporal

Bi-Temporal access patterns

While recording a single snapshot with data across two time axes is a start, things get interesting when you look at

• How to handle new data coming in and the time periods overlap with what you already have?
• how do you handle corrections to data?
• how to handle inactivations or deletions?

Let’s use the example of a bank and its customer who is reporting their address to the bank.

#1 New customer registered and address recorded

I have considered the following structure to turn any document / record into a bi-temporal data point. We will explore this further with an concrete example using MongoDB.

• key: primary identifier of the customer in this case
• data: this can be any type of data. In this case, we record address
• recordMeta: this is an envelope for data points like createdAt, createdBy, updatedAt, updatedBy, deletedAt, deletedBy (soft delete)
• effectiveMeta: this is an envelope for the effective period comprising of effectiveFrom and effectiveTo
• isActive: as the name suggests, it denotes if the bi-temporal record is active or not

#2 Customer changes their address and reports to the bank

With bi-temporal data, we will need to maintain the history. So instead of just updating the snapshot record, we take the following steps

• Update existing record’s “effectiveTo” date to new “effectiveFrom” minus 1 second
• Insert new record
• And most importantly, archive the old record.

As you can see in the example above, the address returned by the system really depends on when you query it. On October 2nd, when someone queries the system, the address would not be the new address but what the bank knew as the address. The customer has not yet reported the new address to the bank.

This really helps in situations where the bank has to investigate any decision the bank had taken on Oct 2nd based on the information it had at that point in time.

#3 Updates that overlap time periods

Let’s say the customer was temporarily in a different address from Sep 19th, 2020 to Oct 7th, 2020 before moving to a new address and wishes to report the same to the bank. To handle this update, the bank will have to take the following steps

• Update Record #1’s “effectiveTo” date to Sep 18th, 2020
• Insert new record with “effectiveFrom” as Sep 19th, 2020 & effectiveTo as Oct 7th, 2020
• Update Record #2’s “effectiveFrom” date to Oct 8th, 2020

And the most important step is to ensure the previous versions of the records are archived before making any updates.

#4 Customer notices a problem with the address and issues correction

Things get more complicated when you start updating historical records. What happens when the customer notices a problem on Dec 15th and issues an update to the address?

As you can see in the above, there are a number of scenarios that can arise while handling bi-temporal data. And when you want to time travel and consider investigating what did you know at a certain point in time, the query patterns become more complex.

With the above described scenarios and complexities in mind, how can we now make it simple for application developers to incorporate bi-temporal access patterns in the applications they are building?

Bi-temporal database — implementation in MongoDB

I have experimented using MongoDB for a simple implementation of bi-temporal database. It provides a bi-temporal wrapper for any type of data you have. The implementation provides a REST API interface. All you need to worry about is providing the “key” and “data” and the required effective meta-data.

For example when you need to create a new customer record for the bank example above, you would invoke the REST endpoint as shown below.

#1 New customer registered and address recorded

A new account registration results in a MongoDB document as follows. I have created the “recordMeta” and “effectiveMeta” with the UTC timestamp as well as offset so it is easy to decipher at a later point and convert the timestamp back to appropriate local time. The “epochMilli” helps index the data effectively as well as simplify access patterns.

#2 Customer changes their address and reports to the bank

Update in address results in three records

• Updated old address record
• Archived old address record
• New address record

Now to query the data, you can use the following REST endpoint. Based on the “asOf” timestamp provided, the API will return the appropriate results.

Being a generic bi-temporal wrapper, you can use this to store and access any type of data.

You can check out the code and take it for a spin here. I realize there are a number of potential scenarios that need to be handled. I have documented the scenarios handled as well as created test cases for those scenarios as well for reference.

I welcome those who can help further test and improve this core implementation.

Better organization of the core and persistence layer for future extensibility.

Extend this further into a library that developers can use and add bi-temporality by just annotating their models.

Conclusion

Bi-temporal data is a valuable Data Governance and Compliance tool for businesses that need to track and reference information based on specific dates and times. By using a bi-temporal system of record, it is possible to accurately track and retrieve data based on both the time the event occurred and the time it was recorded in the system.

This is a complex problem and has been solved in different ways by others. I am interested in hearing about your experiences implementing bi-temporality in your applications. Are there any insights or best practices you can share with me? I look forward to exploring different approaches and learn more about your experiences with this topic.

References

1. Book by Richard Snodgrass. One of the earliest writings — based primarily in the SQL world. Developing Time-Oriented Database Applications in SQL by Richard T . Snodgrass
2. Temporal Patterns by Martin Fowler and other blogs provide further insights.
3. Better with Bitemporal
4. BarbelHisto. Bitemporal data for Java applications. Not sure if this is still maintained — but it has a very good organization and separation of core and persistence layers.