Poor man's bitemporal data system in SQLite and Clojure

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