Yesterday, I laid out some invariants to maintain to ensure that there is always an effective versioned record for any given date. Maintaining these invariants means we can look up all record attributes — versioned as well as stable — for any date post the start date (e.g., the epoch).
When we look up a record, we usually want both the versioned attributes and the stable attributes. We should get all attributes back without having to worry about which attributes are variable and which ones are stable.
Let’s dive into how to look up a record’s versioned and stable attributes in one shot.
Representing a Loan as a Versioned Record
Last time, we were using a versioned table to store variable aspects of a pen (selling price, ink type) and a base table to store the stable attributes of a pen (identifier, name).
In this post, I’ll switch it up and use a loan as my example. The variable aspects of the loan (like rate) would live on the versioned table. The stable attributes of the loan (like name and principal) would live on the base table.
Combining Attributes at the Database Level
Pretty much any time we look up a loan for an effective date, we want both the variable attributes effective on that date and the stable attributes.
We want to look up these attributes efficiently, without having to pay a large performance cost. We also don’t want to have to think through how effective dates work each time we query the database; we’ll want to encapsulate this work.
With these goals in mind, we’ll create a function that takes an effective date and returns a combination of the versioned attributes effective for that date and the base attributes.
The return type of this function is the combined type of all attributes. The loan base table has five attributes:
- startAt
- paymentsPerYear
- paymentAmount
- principal
- name
The loan versioned table has four attributes:
- extraPayment
- rate
- headerId
- effectiveDateTimeRange
We can create a type combining the versioned and stable attributes:
CREATE TYPE "Loan_Type" AS ("id" uuid, "startAt" timestamp with time zone, "paymentsPerYear" integer, "paymentAmount" numeric, "principal" numeric, "name" character varying, "extraPayment" numeric, "rate" numeric, "effectiveDateTimeRange" tstzrange);
To actually fetch the variable and stable attributes, we join the Loan Version table with the Loan table. The headerId on the LoanVersion table corresponds to the “Base Identifier” in the above diagram; it specifies which base loan record the versioned record corresponds with. We join in the Loan table with LoanVersion table on the headerId column to find the header (base) attributes that correspond to the versioned attributes.
Our lens function will take an effective timestamp as a parameter. We use the effective timestamp to find the loan version that is effective for that effective time. A loan version is effective for a date-time if that date time falls within the effective date time range of that versioned record.
For example, say there were three version records of a loan:
If the parameter was 1/1/2015, we would return the versioned record that included 1/1/2015 in its effective date-time range (1/1/2000 – 1/1/2020). In this case, that’s the second versioned record with an interest rate of 3.5%.
As the effectiveDateTimeRange on the version table is a range, we can use the SQL range operator (@>) to find the versioned record that contains the effective date time.
This sql “Loan_Lens” function looks like this:
CREATE FUNCTION "Loan_Lens"(timestamptz) RETURNS setof "Loan_Type" AS $$
SELECT
base."id" as "id",
base."startAt" as "startAt",
base."paymentsPerYear" as "paymentsPerYear",
base."paymentAmount" as "paymentAmount",
base."principal" as "principal",
base."name" as "name",
version."extraPayment" as "extraPayment",
version."rate" as "rate",
version."effectiveDateTimeRange" as "effectiveDateTimeRange"
FROM
"Loan" base
JOIN "LoanVersion" version ON version."headerId" = base.id
AND (
version."effectiveDateTimeRange" @ > $1 :: timestamptz
) $$ LANGUAGE SQL;
Using the Loan Lens Function
We can now use the Loan Lens wherever we want to get all attributes for a loan.
My web app with loan versioning has an abstract records repository class that can be implemented for a specific record. This class has a table method that finds the database table for a specific record repository. For regular, non-versioned records, this table method finds the database table using the table name that the record class was instantiated with (this.recordType.tableName):
table() {
return this.db.table(this.recordType.tableName);
}
For versioned records, however, the record repository does not just use the table name to find the corresponding database table. Instead, it uses the results from the corresponding SQL lens function as the underlying database table. Additionally, it looks up the current effective date time and then provides the current effective date time to the lens function. The current effective date-time is stored on the context of the request, so will always be available.
table() {
const effectiveDateTimeRange = this.getCurrentEffectiveDateTime();
return this.db
.table(
this.db.raw(
`:lensName:(:effectiveDateTimeRange::timestamptz) as :tableName:`,
{
lensName: this.recordType.lensViewName,
effectiveDateTimeRange,
tableName: this.recordType.tableName,
}
)
)
.clone();
}
When we look up a record via the records layer, the look-up function doesn’t have to worry about if the underlying record is versioned record. Instead, records with a versioned portion can just implement the repository class with the lens-backed table function. It’s then up to the table function to use the SQL lens to combine all the versioned attributes with the stable (base) attributes.
This post is part of a series on versioned records:
- Storing Variable Attributes in a Version Table
- Looking up a Record with Variable Attributes
- Preventing Overlapping Ranges with Database-Level Exclusion Constraints