XTDB bi-temporal player state store for football analytics.
Most football clubs store player data in Postgres and can only answer: "What does the data say now?" temporal-squad uses XTDB to answer a harder question: "What did our system know about this player's fitness on the day the selection decision was made?" — not what we know now with hindsight, but what was actually available to decision-makers at that moment.
| Capability | Postgres | XTDB |
|---|---|---|
| Current state queries | ✅ Native | ✅ Native |
| "What was true on date X?" (valid-time) | ✅ Automatic for every document | |
| "What did we know on date Y?" (transaction-time) | ❌ Requires custom infrastructure | ✅ Automatic for every document |
| Retroactive history reconstruction | ❌ Cannot reconstruct pre-audit history | ✅ Full history from first ingestion |
| Bi-temporal queries | ✅ Native — pass two time parameters |
The key insight: A Postgres audit table added after the fact cannot reconstruct history that predates it. XTDB records both time axes automatically from day one, for every document, with zero application code.
XTDB Bi-Temporal Model
══════════════════════
Transaction-Time (what the system knew)
▲
│
│ tx₃ ┌─────────────────────────────────────────┐
│ │ GPS correction loaded: Player A │
│ │ fatigue 0.4 → 0.6 (valid 2024-03-10) │
│ └─────────────────────────────────────────┘
│ tx₂ ┌─────────────────────────────────────────┐
│ │ Selection decision made using tx₁ data │
│ │ (no new data loaded) │
│ └─────────────────────────────────────────┘
│ tx₁ ┌─────────────────────────────────────────┐
│ │ Match data loaded: Players A, B, C │
│ │ fatigue 0.4, 0.8, 0.9 │
│ └─────────────────────────────────────────┘
│
└──────────────────────────────────────────────▶ Valid-Time
2024-03-10 2024-03-11 (what was true
(match day) (selection day) in the world)
Query at (tx₁, 2024-03-10): Player A fatigue = 0.4 ← decision basis
Query at (tx₃, 2024-03-10): Player A fatigue = 0.6 ← hindsight
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ StatsBomb │ │ derive.clj │ │ store.clj │
│ Match Events│────▶│ Player State│────▶│ XTDB Ingest │
│ (JSON/EDN) │ │ Derivation │ │ (valid-time)│
└──────────────┘ └──────────────┘ └──────┬───────┘
│
▼
┌──────────────┐
│ XTDB Node │
│ (RocksDB) │
│ │
│ tx-log │
│ doc-store │
│ index-store │
└──────┬───────┘
│
▼
┌──────────────┐
│ query.clj │
│ 4 Named │
│ Bi-Temporal │
│ Queries │
└──────────────┘
"What did we know about player availability on selection day, using only data loaded by then?"
(query/available-players-at node
#inst "2024-03-10" ; valid-time: match day
#inst "2024-03-11") ; tx-time: selection day
;; => #{[:player/1 "Amara Diallo" 0.4]
;; [:player/2 "Keza Ndayisaba" 0.8]}
;; Player C (fatigue 0.9, unavailable) correctly excludedPostgres equivalent: Requires a manually maintained audit/history table with two timestamp columns, populated by triggers. Complex to build, easy to break, impossible to query retroactively if the trigger was added after the fact.
"Show me every recorded version of this player's state across all matches."
(query/player-history node :player/1)
;; => [{:xtdb.api/tx-time #inst "2024-03-11"
;; :xtdb.api/valid-time #inst "2024-03-10"
;; :xtdb.api/doc {:xt/id :player/1
;; :player/name "Amara Diallo"
;; :player/fatigue-idx 0.4}}
;; {:xtdb.api/tx-time #inst "2024-03-14"
;; :xtdb.api/valid-time #inst "2024-03-10"
;; :xtdb.api/doc {:xt/id :player/1
;; :player/name "Amara Diallo"
;; :player/fatigue-idx 0.6}}]"What fatigue data was available on each matchday?"
(query/squad-fatigue-on node #inst "2024-03-10")
;; => #{["Amara Diallo" 0.4 3764760]
;; ["Keza Ndayisaba" 0.8 3764760]
;; ["Tendai Moyo" 0.9 3764760]}"Which players had their data corrected after we already made the selection call?"
(query/players-corrected-after-decision node
#inst "2024-03-11" ; selection made
#inst "2024-03-15") ; correction loaded
;; => [{:player-id :player/1
;; :name "Amara Diallo"
;; :fatigue-at-selection 0.4
;; :fatigue-after-correction 0.6}]This is the invisible risk in any system without bi-temporal tracking. The decision was made on stale data, but nobody knew until it was too late.
- Java 21+ (Temurin recommended)
- Leiningen
git clone https://github.com/dennisgathu8/temporal-squad.git
cd temporal-squad
lein testlein repl;; In the REPL:
(require '[temporal-squad.core :as core])
(require '[temporal-squad.store :as store])
(require '[temporal-squad.query :as query])
;; Start a node
(def node (core/start-node))
;; Ingest a player state
(store/put-player-state! node
{:xt/id :player/42
:player/name "Amara Diallo"
:player/fatigue-idx 0.4
:player/available? true
:player/match-id 3764760}
#inst "2024-03-10")
;; Query availability
(query/available-players-at node
#inst "2024-03-10"
(java.util.Date.))
;; Clean up
(core/stop-node node)lein uberjar$ lein test
lein test temporal-squad.derive-test
lein test temporal-squad.query-test
lein test temporal-squad.store-test
Ran 14 tests containing 28 assertions.
0 failures, 0 errors.
Building temporal-squad taught me that the gap between "what happened" and "what we knew when we decided" is where most analytical failures hide. Every football club I've seen runs on Postgres — which is great for answering today's questions but structurally blind to yesterday's decision context. XTDB's bi-temporal model eliminates this blind spot not by adding complexity, but by making the database do the work that application developers normally hack together with audit tables and triggers. The Datalog query syntax took adjustment, but the payoff is that a query like "show me what we knew on selection day" is a one-liner instead of a three-table CTE. This is the kind of infrastructure that separates data engineering from data entry — and it's the project I'd most want to show in an interview because the problem it solves is one most teams don't even know they have.
This project uses data structures compatible with StatsBomb Open Data, available under the StatsBomb Public Data License (CC BY-NC-SA 4.0).
All test fixtures use synthetic data only — no real StatsBomb data is committed to this repository.
StatsBomb data is provided free of charge for non-commercial use. If you use StatsBomb data in your own work, you must credit StatsBomb as the data source.
temporal-squad is Project 2 of a five-project Clojure sports data engineering portfolio:
- pitch-pipe — StatsBomb data pipeline (foundation)
- temporal-squad — Bi-temporal player state store (this project)
- Project 3 — Coming soon
- Project 4 — Coming soon
- Project 5 — Coming soon
Copyright © 2024 Dennis Gathu
Distributed under the MIT License.