Implement Temporal versioning

[mlhaufe]

Original Approach

Postgres does not support Temporal tables.

Extensions are not allowed on Azure Postgres. Here is an alternative:

https://github.com/nearform/temporal_tables

The ORM doesn't support the range type though, so an additional library and custom type is required.

The library is pretty old though. I had to modernize it in TypeScript and an ESM module as:

Click for details:

export type TstzRangeBounds = '()' | '[]' | '[)' | '(]';

/**
 * Represents a range of time with a beginning and end.
 *
 * @example
 * const range = new TstzRange(new Date("2021-01-01T00:00:00Z"), new Date("2021-01-02T00:00:00Z"), "[)");
 */
export default class TstzRange {
    static parse(range: string, parseEndpoint: (str: string) => Date | null = (str) => new Date(str)): TstzRange {
        const match = range.match(/([\[\(])(.*?), (.*?)([\]\)])/);
        if (!match) throw new Error("Invalid range format");
        const [, startBound, begin, end, endBound] = match,
            beginDate = begin.trim() === "-infinity" ? null : parseEndpoint(begin.trim()),
            endDate = end.trim() === "infinity" ? null : parseEndpoint(end.trim());
        return new TstzRange(beginDate, endDate, `${startBound}${endBound}` as TstzRangeBounds);
    }

    /**
     * Returns the union of two ranges.
     */
    static union(a: TstzRange, b: TstzRange): TstzRange {
        const newBegin = !a.begin || (b.begin && a.compareBegin(b.begin) > 0) ? b.begin : a.begin,
            newEnd = !a.end || (b.end && a.compareEnd(b.end) < 0) ? b.end : a.end,
            newBoundStart = a.bounds[0] === '[' || b.bounds[0] === '[' ? '[' : '(',
            newBoundEnd = a.bounds[1] === ']' || b.bounds[1] === ']' ? ']' : ')',
            newBounds = `${newBoundStart}${newBoundEnd}` as TstzRangeBounds;
        return new TstzRange(newBegin, newEnd, newBounds);
    }

    constructor(
        /**
         * The beginning of the range.
         * If `null`, the range is unbounded on the left.
         */
        public begin: Date | null,
        /**
         * The end of the range.
         * If `null`, the range is unbounded on the right.
         */
        public end: Date | null,
        /**
         * The bounds of the range.
         *
         * - `()` for exclusive
         * - `[]` for inclusive
         * - `[)` for left-inclusive
         * - `(]` for right-inclusive
         */
        bounds?: TstzRangeBounds
    ) {
        if (begin && end && begin.getTime() > end.getTime())
            throw new Error("Begin date must be before end date");

        if (!bounds) {
            const bStart = begin ? "[" : "(",
                bEnd = end ? "]" : ")";
            this.bounds = `${bStart}${bEnd}`;
        } else {
            this.bounds = bounds;
        }
    }

    bounds: TstzRangeBounds;

    /**
     * Compares the beginning of the range with the given date.
     */
    compareBegin(begin: Date): number {
        if (!this.begin) return -1;
        return this.begin.getTime() - begin.getTime();
    }

    /**
     * Compares the end of the range with the given date.
     */
    compareEnd(end: Date): number {
        if (!this.end) return 1;
        return this.end.getTime() - end.getTime();
    }

    /**
     * Checks if the range contains the given date.
     */
    contains(value: Date): boolean {
        const [$0, $1] = this.bounds,
            { begin, end } = this,
            isAfterBegin = $0 === "[" ? value >= begin! : value > begin!,
            isBeforeEnd = $1 === "]" ? value <= end! : value < end!;
        return isAfterBegin && isBeforeEnd;
    }

    /**
     * Checks if the range intersects (overlaps) with another range.
     */
    intersects(other: TstzRange): boolean {
        // If either range is empty, they don't intersect
        if (this.isEmpty() || other.isEmpty()) return false;

        // Check if either range contains the other's begin or end, or if they are directly overlapping
        return (
            ((this.begin && other.contains(this.begin)) ?? false) ||
            ((this.end && other.contains(this.end)) ?? false) ||
            ((other.begin && this.contains(other.begin)) ?? false) ||
            ((other.end && this.contains(other.end)) ?? false)
        );
    }

    /**
     * Checks if the range is bounded.
     */
    isBounded(): boolean { return this.begin !== null && this.end !== null; }

    /**
     * Checks if the range is empty.
     */
    isEmpty(): boolean { return this.begin === this.end; }

    /**
     * Checks if the range is finite.
     */
    isFinite(): boolean { return this.begin !== null && this.end !== null; }

    /**
     * Checks if the range is infinite.
     */
    isInfinite(): boolean { return this.begin === null || this.end === null; }

    /**
     * Checks if the range is unbounded.
     */
    isUnbounded(): boolean { return this.isInfinite(); }

    toJSON(): string {
        return JSON.stringify({
            begin: this.begin ? this.begin.toISOString() : null,
            end: this.end ? this.end.toISOString() : null,
            bounds: this.bounds,
        });
    }

    /**
     * Returns a string representation of the range.
     *
     * @example
     * const range = new TstzRange(new Date("2021-01-01T00:00:00Z"), new Date("2021-01-02T00:00:00Z"), "[)");
     * console.log(range.toString()); // [2021-01-01T00:00:00.000Z, 2021-01-02T00:00:00.000Z)
     */
    toString(): string {
        const beginStr = this.begin ? this.begin.toISOString() : "-infinity",
            endStr = this.end ? this.end.toISOString() : "infinity";
        return `${this.bounds[0]}${beginStr}, ${endStr}${this.bounds[1]}`;
    }

    valueOf(): string { return this.toString(); }
}

Sigh... This implementation needs alot of workarounds to function

1. The SQL Versioning function
   a. This has to be embedded in a migration file and unmanaged by the ORM as the ORM does not have a means of defining it
2. A trigger definition hack on every base entity. Ex:
   
3. ~50 History tables to go with the corresponding entities
   a. Maybe there is a way to dynamically define the resulting Single Table Inheritance schema from the class hierarchy
4. The Custom type declaration to map the TypeScript TstzRange class to the datbase value:

Click for details

import { Type, type Platform, type EntityProperty, type TransformContext } from '@mikro-orm/core';
import TstzRange from '../../../shared/TstzRange.js';

export default class TstzRangeType extends Type<TstzRange, string> {
    override convertToDatabaseValue(value: TstzRange, platform: Platform, context?: TransformContext): string {
        return value.toString();
    }

    override convertToJSValue(value: string, platform: Platform): TstzRange {
        return TstzRange.parse(value);
    }

    override getColumnType(prop: EntityProperty, platform: Platform): string {
        return 'tstzrange';
    }
}

Even with the custom type declaration, range comparisons won't work as desired with the database as postgres utilizes a custom operator: <@. I believe the ORM solution to this is to perform the comparisons in the application layer instead of the database via the TstzRangeType.prototype.compareValues(a, b) method.

New Approach

A simpler, more correct, approach may be the design of TommCatt:

1. The basic idea: https://stackoverflow.com/a/38735711/153209
2. In depth: https://www.dropbox.com/s/8hnkzet6fueblz7/TemporalDBDesign.pdf?dl=0

erDiagram

REQUIREMENT {
    uuid req_id PK
    foo static_attr1
    bar static_attr2
}
REQUIREMENT_VERSION {
    uuid req_id PK,FK
    date effective PK
    bit deleted
    foo volatile_attr1
    bar volatile_attr2
}
REQUIREMENT ||--o{ REQUIREMENT_VERSION : versions

Loading

• The domain classes will need to all become readonly.
• All CRUD actions become database INSERTs
  • Deletions are accomplished by inserting a new record with the deleted bit set
  • Interactors and Repositories will probably need to be re-introduced
• Each Domain class will need a corresponding *Version class
  • This will have to be a parallel class hierarchy to take advantage of STI from the ORM
• With the addition of extra fields effective, deleted, the Domain entities would no longer be agnostic of the data layer in violation of Clean Architecture. This implies that the decorators would need to be replaced with data model definitions again.
  • Implement Application layer #449
  • Implement data layer models #450