Poor man's bitemporal data system in SQLite and Clojure | Hacker News Reader

Poor man's bitemporal data system in SQLite and Clojure

evalapply.org

172 pointsadityaathalye54 comments

I've been absolutely waist deep in a bitemporal system on top of PostgreSQL using tstzrange fields. We manage an enormous portfolio of hundreds of thousands of domain names. Every time our core db is modified, before/after states are emitted to a change table. We've been doing this since 2022. Those changes get lightly transformed via trigger into a time travel record, with the valid from/to range and a gist index to make asking questions about the state of the world at a particular point in time easy. For perspective our change table has 90M rows.

All of it works quite well and is decently performant. We can ask questions like, how many domains did we own on March 13th, 2024? Or look at the entire lifecycle of a domains ownership (owned, released, re-acquired, transfered, etc).

The big challenge and core issue we discovered though is that our data sucks. QAing this new capability has been a moving target. Tons of mistakes over time that were partially undone or manually undone without proper audit trail. Ghost records. Rapid changes by our bulk editor tool a->b->a->b that need to get squashed into just a->b. The schema of our database has evolved over time, too, which has made this tough to view a consistent representation of things even if the fields storing that data were renamed. When the system was first introduced, we had ~5 columns to track. Now we have over 30.

Suffice to say if I were to do things over again, I would implement a much better change tracking system that bakes in tools to clean/erase/undo/soft-delete/hard-delete mistakes so that future me (now) wouldn't have so many edge cases to deal with in this time traveling system. I'd also like to just make the change tracking capable of time travel itself, versus building that as a bolt-on side table that tracks and works from the change table. Transitioning to an EAV (entity-attr-value) approach is on my spike list, too. Makes it easier to just reduce (key,val) tuples down into an up to date representation versus looking at diffs of before/after.

Really interesting stuff. I learned a lot about this from Clojure/Datomic and think its quite neat that so many Clojurists are interested in and tackling this problem. As the author notes in this post, XTDB is another one.

For my little system (blog author here) I've decided that all tables will be append-only logs of facts. In the post, I tried keeping traditional schema alongside a "main" facts table. Of course, the problem of audit logs comes up for any change made to traditional "current-database-view" tables. And then who audit logs the audit log?

I feel like "two systems" is the problem.

Writes should either be traditional schema -> standard log of all changes, OR, should be "everything is a log", and the system keeps the "current" view updated, which is just a special case of the "as of" query, where "as of" is always "now".

How badly my schema will behave (in my SQLite-based architecture) is to be discovered. I will hopefully be in a position to run a reasonable benchmark next week.

A follow-up blog post is likely :sweat-smile:

> 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.

So much this!

Straight-up copy-paste from my development notes...

* what pivots in current db vs temporal db?

What is the difference between these pictures?

#+BEGIN_SRC text

  ("Current" DB)

   CRUD records
         ^
         |
         v
  [ current view ] -- update --> [ facts log ]

  ---------------------------------------------------------

  [ current view ] <-- update -- [ facts log ]
         |                             ^
         v                             |
    READ records             WRITE records (as facts)

  ("Temporal" DB)

#+END_SRC

- Hint: It is /not/ the schema. It is /what/ "pivots".

- In both cases the current view can be understood to be a pivot table of the facts log.

- BUT in the current DB, we must "pivot" the /process/, i.e. take a CRUD op and transform it into an audit log. This /must/ be done synchronously in in real-time. Whereas in the Temporal DB's case, we must "pivot" the stored data, which we can do at any point in query time, as of any point in time of the log.

- The complexity of Current DBs derives from /live process management/ challenges. Whereas the complexity of Temporal DBs derives from /retroactive process management/ challenges.

/now/ is /never/ cheap. It is the most expensive non-fungible thing. Once it's gone, it's gone. Fail to transact an audit trail for the CRUD operation? Too bad. Better luck next time. Whereas disk space is cheap, and practically infinite which affords Temporal DBs greater opportunity to find a better trade-off between essential complexity and DB capabilities. At least as long as disk space remains plentiful and cheap.

This is why if we are modeling a Temporal DB over a Current DB, it is preferable to write all tables as /fact/ tables and query their auto-generated 'current view' versions, for normal query needs. For audit / analysis needs, we can snapshot the facts tables and operate on those out-of-band (detached from the live app). Impedance mismatch occurs when trying to run /both/ current CRUD tables (writing to audit logs) for some parts of the schema, in parallel with a "main" facts table for all fact-records. In a given data system, it is better to do either one or the other, not both at the same time.

The problem with `everything is a log` is that is very undisciplined, and trigger from the actual main table have this very serious advantages:

* Your main table has the correct shape, and the derived log too! * MOST of the queries are to that and for now

Yes, if the log has no regular schema, and if the overall system has no regular design pattern.

This is the minimal "standard record" that one can use, for all facts pertaining to all domain entities in one's system:

  system_time, valid_time, Entity, Attribute, Value, assert/redact

Now the problem is:

- For small-middle-heavy usage (anything that (reasonably) fits on one machine) how to use existing database technology, so that one can store and query this stuff sufficiently fast. This is what I'm trying to do with SQLite... partly because the "V" of E/A/V benefits from SQLite's "Flexible Typing" system. In SQLite parlance, E, A are TEXT, and V is NUMERIC.

- For at-scale usage (anything that needs many machines), how to make a data system from scratch to store log-structured information efficiently, and how to query it efficiently. See what the Datomic, XTDB, and RedPlanetLabs/Rama people are doing. Essentially: separate storage and compute, event-source everything, and build a system from scratch to use it efficiently and fast at scale.

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.

Maybe I could convert every table into a "log" table (i.e., append only similar to your blog) and add a timestamp column to each table (or UUID). Then I also store all of these UUIDs in another table to keep track of the global order. To "undo" I look up the previous UUID, find entries with that UUID, and append to the log table the values that make the state the same as the previous UUID....

This isn't as general as your setup, but I think it would work ok with my foreign key issue.

I believe UUIDv7 does not (cannot) guarantee total order. I am using it as a method to manage multiple timelines---each "fact" lives on its own virtual timeline (identified by a "valid_id", which is a UUIDv7). My DB system does not automatically disambiguate conflicting facts (because it cannot --- it simply does not have the meta-information). The users of the system can manually audit and fix inconsistencies by making correcting (append-only) entries.

For your case, I feel like your best bet would be to create the global order in the first place... i.e. force all writes through a single (and single-threaded) writer process, and fetch reads off one or more independent reader processes (I'm doing this with WAL-mode SQLite).

But I could be totally wrong --- this is my first foray into the freaky world of temporal data systems :D

As this is a single-user CLI tool I don't need any concurrency. So I can probably use the timestamp directly instead of a UUID and not worry about multiple timelines.

On thinking more this morning I think I can keep my original tables and add append-only "log" tables with triggers to keep them up to date when the main tables change. That doesn't slow down my main tables performance (they only contain the current data) and also allows me to incrementally change my db (adding new tables + triggers is easier than changing existing ones).

I've made https://github.com/bbkane/enventory/issues/122 to track this idea, though I'm not sure when (or even if) I'll have time to really play with the idea (much less "productionalize" it- add support for all enventory events, tab completion on the CLI, tests, UI, etc...).

But I'm at least tracking it for when I get time and motivation! Thanks for writing the post and replying to my comment.

The XTDB people did a bunch of interviews with people doing stuff in the temporal data systems universe.

Several are out on YouTube. One with Richard Snodgrass is yet to be published... waiting for that eagerly!

ref. topics and links to videos: https://github.com/orgs/xtdb/discussions/4419

  "Decades in the making: Bitemporality in SQL, with Rob Squire"

  "As-Of joins and database architecture, with Arjun Narayan (co-founded Materialize)"

  "Building an identity service on XTDB, with Andrew Maddock (Foundry OS)"  

  "Solving FinTech pains with Suresh Nageswaran (Senior Director, Grid Dynamics)"

  "A meeting with the one and only Richard T. Snodgrass."

  "Building a payments integration system on XTDB, with Edward Hughes"

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

> clean/erase/undo/soft-delete/hard-delete mistakes[...] make the change tracking capable of time travel itself [...] Transitioning to an EAV

I just finished building out all of that + more (e.g., data lineage, multi-verse, local overrides, etc), also on PG. Reach out if you want to chat and get nerd sniped!

What I’ve found works well, going along with the author’s “everything is a log”, is append only tables in PG with date ranges on them.

So you have a pet table with an ID, birth date, type, name, whatever, and ‘valid_range’.

That last column is a date_range column. Combined with the ID it serves as a unique key for the table. Records are inserted with a date grange from now() to infinity.

To update a record, you call a stored procedure. It creates the new record with that same date range, and updates the old record to be valid up to (but not including) now(). The SP ensures the process is done correctly.

You can use the same date range in join tables for the same reason.

This makes it possible to see the full state of any record kept like this at any point in time, see when it was created, or last changed. An audit table records who changed it by holding the ID and timestamp of the change. There is no real deletion, you’d do soft deletion by setting a status.

I suspect this wouldn’t work well for very high volume tables without sharping or something. But for CRUD tables that don’t change a lot it’s fantastic.

The only thing that’s not smooth is future updates. If you need a new non-null column, it ends up added to all records. So you can either set a default and just deal with the fact that it’s now set on all old records, leave it as nullable and enforce non-null in code, or enforce it only on insert in a trigger or the SP I described.

I’ve found it much easier to use than some sort of ‘updates’ table storing JSON changes or EAV style updates or whatever.

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)

The valid_range with a gist index is quite fast.

    SELECT COUNT(DISTINCT domain)
    FROM time_travel
    WHERE (CURRENT_DATE - INTERVAL '90 days')::timestamptz <@ valid_range
    AND owned;

This is asking, "how many domains did we own 90 days ago"

Instead of finding records where the start is less than, end is greater than, you can just say find me rows that will cover this point in time. The GiST index on valid_range does the heavy lifting.

Lots of handy range-specific query tools available: https://www.postgresql.org/docs/17/functions-range.html

Yep this is it. Since you mostly view the newest stuff you could even have a partial index only over the records that are considered current.

And it’s PG’s range queries that make this shine, as you showed. If you had to simulate the range with two columns all the queries would be a pain.

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

A trigger to ‘close’ the old record is a great idea. My stored procedure is also doing some additional validation (validate at every layer = less bugs) so what I’ve got works well enough for me.

But that’s very smart. Never considered it.

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.

It's a pity that Clojure is kind of a hermetic space these days, because the concept of bitemporality really deserves much more attention. It's amazing how often you want to know "What was the P&L for March using the data available on Apr 4?" and how uncommon it is to find a database design that supports that kind of query.

Spanner does that with ease (I worked there, so I’m clearly biased).

https://cloud.google.com/spanner/docs/timestamp-bounds#exact...

That only covers the 'transaction time' axis though? And the page says retention is limited to 1 week. No doubt useful for some things, but probably not end-user reporting requirements.

"It's a pity that Clojure is kind of a hermetic space these days"

If you don't mind sharing, I'm curious why you feel this way.

We hermits enjoy its use in our lairs, worlds of pure thought.

It's not particularly rare in analytical databases/query engines, nearly all of which support AsOf joins these days, including

  * Clickhouse
  * DuckDB
  * BigQuery
  * Snowflake
  * QuestDB

AsOf join in those systems solves a rather narrow problem of performance and SQL expressiveness for data with overlapping user-defined timestamps. The bitemporal model solves much broader issues of versioning and consistent reporting whilst also reducing the need for many user-defined timestamp columns.

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.

This is the book I like for an overview of the problem. https://www.amazon.com/Temporal-Relational-Kaufmann-Manageme...

'Hermetic' is an interesting word-choice, considering Clojure has a habit/raison detre of attaching to other host languages/ecosystems i.e. Jank (C++), ClojureScript (JS), Basilisp (Python), Phel (PHP), Babashka (general scripting), and of course, Clojure itself on the JVM.

Sure. But the other way around?

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

datomic =/= clojure

Clojure doesn't have much manpower, being a niche language and all, so maybe that's where your impression comes from. It's hardly on purpose, and there's many examples of software that ships with another language as public API.

Rama is written in clojure but the main api is java

Klipse is written in cljs but is marketed as a "javascript library"

Even Datomic maintains a java api

Rama[1] for example launched with Java API first. Probably for some reason Datomic just didn't get traction with JS users for some nontechnical reason.

[1] https://redplanetlabs.com/

Shutout to XTDB, super friends folks and awesome tech to boot

https://github.com/xtdb/xtdb

I don’t understand footnote 16? It sounds like he’s saying he’s ceo of htmx and datastar?

Yes... This is a running joke in both communities. IYKYK :)

Oh ok, I’m out of the loop on it ;) thought it was some sort of ai hallucination.

All eleven thousand words were hand-typed. No AIs are used or abused in the making of any of my blog posts. (Because I write to think, because writing is nature's way of showing me how sloppy my thinking is. So it goes...)

"Because I write to think"

Hallelujah!!

this part (and this is how you know he's a true Clojurian)

If you don’t mind a tangent, this is close to my way of working out thoughts as well, including with code.

When I write code, it’s as much a cognitive tool as it is a tool to make things happen in the system. It develops thoughts as much as it develops system behavior.

Involving AI changes this quite a bit, but I feel like I’m making my way to a balance where it supports rather than replaces (or worse: disrupts) my cognitive processes.

Not at all, I'm all about tangents. The blog post itself is a tangent.

Programming is writing for me. So, yes I am the same... I need to type (or sometimes write it longhand), to make progress.

I gave LLMs a fair shake, but generative mode usage overwhelms my nervous system. I could use 'em, maybe, for pattern-recognition. But using an expressive language (Clojure) means I can eyeball my source code and/or grep through it to maintain a good enough view of my system. This also applies to most third-party code I use from the Clojure ecosystem. Libraries tend to be small (a few thousand lines of code), and I can skim-read through them quick enough.

I know there is a black art to it that one is supposed to learn, in order to get useful results, but so far, the incentive isn't strong enough for me.

So, hand typing / writing it is... might as well feel satisfied using my nice keyboard and little notebook, on my way to obsolescence. No?

Some of us keep htmx around as a momento. Just for old times sake.

Are you mixing it up with xhtml

> as a momento

FWIW I think you’re thinking of “memento”

     an object or item that serves to remind one of a person, past event, etc.; keepsake; souvenir.

Oddly it works as a neologism as well. "moment" conveying that it was short-lived or fleeting.

A memory or artifact of a short period of time.

> Oddly it works as a neologism as well

TIL, thank you :)

It's not a joke, there is only one Datastar CEO. Identity theft is not a joke Jim

We are Borg. #NotACult

See "DSL for Bitemporal Sixth Normal Form with UUIDv7" https://github.com/sergeyprokhorenko/6NF_DSL

Oh, this is neat... thank you for sharing! Naurally, the "I don't know what I don't know" problem plagues me, as a solo maker trying to feel his way about this (temporal) space.

Maybe it's time (hehe) someone started a 6NF Conf.

Tangential but

> Git simply refuses to transact the conflict into the live state of the codebase, until someone a) fixes the conflict manually and b) tells git that the conflict is resolved.

This is something that a lot of people seem to forget or maybe never knew, they think that the git cli's merge heuristics are somehow sound and go all YOLO with rewriting history with maybe-correctly merged code (rebase). Sometimes the same people who voice loud objections about LLM coding assistants...

tl;dr

  CREATE VIEW IF NOT EXISTS world_facts_as_of_now AS
  SELECT
    rowid, txn_time, valid_time,
    e, a, v, ns_user_ref, fact_meta
  FROM (
    SELECT *,
      ROW_NUMBER() OVER (
        PARTITION BY e, a
        ORDER BY valid_preferred DESC, txn_id DESC
      ) AS row_num
    FROM world_facts
  ) sub
  WHERE row_num = 1
    AND assert = 1
  ORDER BY rowid ASC;

...cool approach, but poor query optimizer!

It would be interesting to see what Turso's (SQLite fork) recent DBSP-based Incremental View Maintenance capability [0] would make of a view like this.

[0] https://github.com/tursodatabase/turso/tree/main/core/increm...

It is a poor man's database, after all :)

I really need to complete this thing and run some data through it... like, how poor is poor really? Can it be just enough for me to make a getaway with smol SaaS apps?

Perhaps because of the domains I work in I continue to find bitemporality completely uninteresting. Stop trying to make fetch-as-of happen.

Reply on news.ycombinator.com