Poor man's bitemporal data system in SQLite and Clojure
7 days ago
- #temporal-databases
- #Clojure
- #SQLite
- The article discusses creating a poor man's bitemporal data system using SQLite and Clojure, inspired by accounting practices and temporal databases like Datomic, XTDB, and Rama.
- Key concepts include immutable, append-only data storage, entity-attribute-value modeling, and the importance of recording both transaction time and valid time for facts.
- The system is designed to be vertically integrated, minimizing complexity, and avoiding cloud service lock-in, making it suitable for indie SaaS applications.
- SQLite is chosen for its flexibility, in-process database capabilities, and archival-grade data storage, ensuring long-term data accessibility.
- The article emphasizes the philosophical and practical aspects of temporal data, including the challenges of representing reality and handling conflicting facts.
- Practical implementation details include using UUIDv7 for conflict management, creating views for current data, and leveraging Clojure for materialized views and querying.
- The system aims to be single-operator friendly, with low maintenance overhead and the ability to handle data residency and privacy laws effectively.