Comment by whalesalad

> waiting for that eagerly!

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 :)

This is a really good description of more or less exactly how our current approach works! This is a daily granularity variant we are testing atm, in order to eliminate flip-flops that occur during the length of a business day. The v1 impl was down to the second, this one is daily.

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;

And then here is a piece of our update trigger which "closes" previous entities and opens an new one:

    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

I'm not entirely sure what the valid_range is doing. Besides updating it, do you use this index for anything else? I agree the performance doesn't seem like it would be great.

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)

Interesting. It sounds like you are only doing partial temporality?

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

> OR, should be "everything is a log",

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.

Looking forward to the second blog!

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.