Poor man's bitemporal data system in SQLite and Clojure

Why is there no JS client for datomic (that is not abandoned)?

In a bitemporal database, every regular looking join over the current state of the world is secretly an AsOf join (across two dimensions of time), without constantly having to think about it when writing queries or extending the schema.

Wait no longer! I just updated the page with the unlisted video link: https://youtu.be/6Q_pAI20QPA

We are hoping to record another (more polished) session with Professor Snodgrass soon :)

I'm playing with https://github.com/hettie-d/pg_bitemporal right now and it seems great so far.

I do something like 4000 inserts a second, but maybe only a few queries a minute, so I use an "invalidated_by" column which (eventually) points to the newer record, and I update it on query instead of insert (when the multiple nulls are discovered and relevant)

Here is the core of it:

    CREATE TABLE time_travel_daily (
        domain              TEXT        NOT NULL,
        valid_range         tstzrange   NOT NULL,
        valid_from          timestamptz GENERATED ALWAYS AS (lower(valid_range)) STORED,
        valid_to            timestamptz GENERATED ALWAYS AS (upper(valid_range)) STORED,

        tld                     TEXT,
        owned                   BOOLEAN,
        acquired_at             timestamptz,
        released_at             timestamptz,
        registrar               TEXT,
        updated_at              timestamptz,
        accounting_uuid         TEXT,
        offer_received_date     timestamptz,
        payment_received_date   timestamptz,
        sold_at                 timestamptz,
        sold_channel            TEXT,
        last_renewed_at         timestamptz,
        expires_at              timestamptz,
        transfer_started_at     timestamptz,
        transfer_completed_at   timestamptz,
        transfer_eligible_at    timestamptz,

        snapshot_json           JSONB NOT NULL,
        inserted_at             timestamptz DEFAULT NOW() NOT NULL,
        source_data_change_id   INT,

        PRIMARY KEY (domain, valid_range)
    );

    CREATE INDEX ttd_domain_idx ON time_travel_daily(domain);
    CREATE INDEX ttd_gist_valid_range_idx ON time_travel_daily USING gist(valid_range);
    CREATE INDEX ttd_owned_valid_range_idx ON time_travel_daily USING gist(valid_range) WHERE owned = TRUE;
    CREATE INDEX ttd_registrar_idx ON time_travel_daily(registrar) WHERE registrar IS NOT NULL;
    CREATE INDEX ttd_source_data_change_id_idx ON time_travel_daily(source_data_change_id) WHERE source_data_change_id IS NOT NULL;

    UPDATE time_travel_daily
    SET valid_range = tstzrange(lower(valid_range), target_date::timestamptz, '[)')
    WHERE domain IN (
        SELECT DISTINCT dc.domain
        FROM data_changes dc
        WHERE dc.invalidated IS NULL
            AND dc.after IS NOT NULL 
            AND dc.modified_at::date = target_date
    )
    AND upper(valid_range) IS NULL  -- Only close open ranges
    AND lower(valid_range) < target_date::timestamptz;  -- Don't close ranges that started today

As a side project, I'm writing a CLI to tie environment variables to project directories ( https://github.com/bbkane/enventory ) and share env vars between projects with a "reference" system. Similar to direnv but in a central SQLite DB.

See https://github.com/bbkane/enventory/tree/master/dbdoc for how the schema looks, but it's a few tables with foreign keys to each other to support the "reference" system.

Unfortunately those foreign keys mean I don't have a way to "undo" changes easily. So instead I added a confirmation prompt, but I'd still like that undo button.

Hopefully in subsequent blog posts you can follow up on your "Model an example domain of sufficient complexity" TODO and make this a bit less abstract (and hopefully more understandable) to me.

100%. This is a regret that I have in our lifecycle tracking. We effectively are updating a giant spreadsheet (table) and tracking side effects of those updates. I would much rather work in a log/event based system kinda flipping that on its head, where we track events like domain_renewed or domain_transferred.

As it stands we track that the renewed_at attribute changed, and have to infer (along with other fields) things like "were you a renewal event?" which has changed meaning over time.

Alas change tracking wasn't even part of the spec of this original feature... so I am glad I had the foresight to institute change tracking from 2022, as imperfect as it is currently.