Skip to content

Core Data Model: Hub–Link–Satellite on PostgreSQL¤

Context and Problem Statement¤

db4's core design constraint (see CLAUDE.md) is to optimize for many disparate data sources, not many users: a bank export, a smartwatch API, a lab PDF, a beancount ledger, and whatever shows up next all need to plug into the same core without redesigning it each time. Per-domain normalized SQL tables (the shape explored on experiment/add-accounting) work fine in isolation, but there's no shared way to cross-link rows of different types (e.g. a Transaction to a Person) without a bespoke join table per pair of types, and no built-in notion of history. What core data shape lets every new domain plug in as "add a table or two," while keeping arbitrary cross-linking, full history, and user-added tags/metadata as things core already provides rather than things every plugin re-invents?

Decision Drivers¤

  • Many disparate, unanticipated future data sources; only a handful of users — extensibility over scale.
  • Arbitrary entity ↔ entity cross-linking (transaction ↔ person ↔ task ↔ artifact) must not require a new join table for every new pair of types.
  • History is first-class: "time of fact" vs. "time recorded" (bitemporal), queryable "as of" any point.
  • Users can add their own tags and free-form metadata from the UI(s), without a schema migration.
  • Low operational burden appropriate for a single-Postgres-instance, few-user system.
  • Should not foreclose local/cloud AI (embeddings, semantic search) integrating "seamlessly" later.

Considered Options¤

  • Hub–Link–Satellite (Data Vault–inspired) on PostgreSQL
  • Per-domain normalized tables with ad hoc foreign keys (status quo)
  • Generic EAV / triple-store inside PostgreSQL
  • Dedicated bitemporal/Datalog engine as the primary store (XTDB / Datomic family)
  • Event Sourcing + CQRS with a separate event store

Decision Outcome¤

Chosen option: "Hub–Link–Satellite on PostgreSQL 18", because it solves cross-linking structurally (one real link table, backed by a shared entity-identity spine, instead of a join table per type pair), gives bitemporal history without a second storage engine (insert-only, valid-time-ranged satellites), and lets every new data source ship as a plugin owning its own hub + satellite tables — directly serving the "many sources, few users" constraint at an operations cost appropriate for a single Postgres instance.

Architecture Sketch¤

  • core.entity(entity_id uuid PK, entity_type text, created_at timestamptz) — the shared identity spine. Every domain hub table's primary key is also a FK to this table, so anything, regardless of domain, can be referenced generically.
  • Domain hub tables, one per business concept, owned by the plugin that defines it — e.g. people.hub_person, accounting.hub_account, accounting.hub_transaction. A new domain registers a new hub; core is untouched.
  • core.link(link_id, left_entity_id, predicate, right_entity_id, valid_range tstzrange, load_time, source_plugin) — one generic table for arbitrary entity ↔ entity relations, with a GiST exclusion constraint on (left_entity_id, predicate, right_entity_id, valid_range) so overlapping-in-time duplicate links are rejected at the database level. This is the "cross-linking should be easy" answer.
  • Domain satellite tables hold descriptive/measured facts for a hub, keyed by (entity_id, valid_range), insert-only — never UPDATE, only append a new row and let the previous one's range close. This is where bitemporal history lives: valid_range is domain time, load_time is transaction time.
  • User-added tags and metadata reuse the same shapes, so they need zero schema changes:
  • core.hub_tag(entity_id PK, slug UNIQUE, label, created_by) — a tag is just another hub; any UI can create one.
  • Tagging anything = a core.link row with predicate = 'tagged_as'.
  • core.sat_metadata(entity_id, key, value jsonb, valid_range, load_time) — free-form key/value attached to any entity, from any UI, versioned like everything else.
  • core.hub_artifact(entity_id PK, uri, content_hash, mime_type, size_bytes, added_at) — uri is an fsspec/universal_pathlib-resolvable URI (local path, s3://…, etc.); artifacts attach to any entity via core.link, keeping unstructured content out of SQL while still linkable.
  • Extensions, all CREATE EXTENSION, no extra services: timescaledb (Apache-2 edition) as hypertables for genuinely high-frequency satellites; pgvector for embeddings, colocated with the facts they describe — the concrete hook for "AI should integrate seamlessly," since a local or cloud model just writes rows like any other ingest plugin; ltree (built-in contrib) for hierarchies like the accounting Account tree. Not "ladybugdb" — LadybugDB (Kuzu's community successor) is a standalone embedded graph engine, not a PostgreSQL extension, so it can't run inside Postgres. If core.link joins/recursive CTEs stop being enough for real multi-hop graph queries, Apache AGE is the actual Postgres extension for that (openCypher inside Postgres, Apache top-level project, supports PG 11–18) — worth adding later, not needed for the initial shape.
  • PostgreSQL 18 is current stable as of this writing; 19 is in beta, targeted for GA around September
  • Start on 18; re-evaluate 19 after it's been GA for a while rather than tracking the beta.
flowchart TB
    subgraph Core["core"]
        ENTITY[("core.entity")]
        LINKTBL[("core.link")]
        TAG[("core.hub_tag")]
        META[("core.sat_metadata")]
        ARTIFACT[("core.hub_artifact")]
    end

    subgraph PeoplePlugin["people plugin"]
        HPERSON[("hub_person")]
    end

    subgraph AccountingPlugin["accounting plugin"]
        HTXN[("hub_transaction")]
        HACCOUNT[("hub_account")]
        SBAL[("sat_account_balance")]
    end

    subgraph HealthPlugin["blood_test plugin"]
        HTEST[("hub_blood_test_result")]
        SVAL[("sat_test_value")]
    end

    HPERSON -->|entity_id FK| ENTITY
    HTXN -->|entity_id FK| ENTITY
    HACCOUNT -->|entity_id FK| ENTITY
    HTEST -->|entity_id FK| ENTITY
    TAG -->|entity_id FK| ENTITY
    ARTIFACT -->|entity_id FK| ENTITY

    HACCOUNT --> SBAL
    HTXN --> SBAL
    HTEST --> SVAL

    HPERSON -.->|"tagged_as (via link)"| TAG
    HTXN -.->|"involves (via link)"| HPERSON
    HTEST -.->|"belongs_to (via link)"| HPERSON
    ARTIFACT -.->|"attached_to (via link)"| HTEST

Note on event sourcing for measurements¤

Largely already included, if satellites are kept insert-only. Each satellite row is already an immutable fact with a valid_range and load_time; appending a new row for every new measurement or correction (instead of UPDATE-ing) already gives an append-only, replayable, "as of any point in time" log — the practical benefit event sourcing is usually adopted for. What real event-sourcing/CQRS additionally buys you is modeling process-level events that aren't just "a value changed" (e.g. "ingestion run started," "duplicate detected and merged," "user corrected a typo and here's why") — genuinely distinct from state facts. If a concrete need for that shows up, add a narrow core.event_log table alongside Hub/Link/ Satellite rather than adopting a second architecture (separate event store, message bus, projection engine). This is consistent with experiment/health-track-ideas's own earlier conclusion that a message bus is overkill at db4's write volume. Decision: don't adopt event-sourcing/CQRS as the primary architecture; revisit only if a workflow-level auditing need appears, not just a history need.

Consequences¤

  • Good, because every new data source ships as one plugin owning its own hub + satellite tables, with zero changes to core.
  • Good, because cross-linking is free: one core.link table with real FK integrity via the shared core.entity spine, instead of a join table per pair of types.
  • Good, because bitemporal history comes from the shape itself (insert-only satellites), not a second storage engine or event bus.
  • Good, because tags and free-form metadata are extensible from any UI with zero migrations.
  • Good, because a single Postgres instance stays the only piece of infrastructure to run; timescaledb, pgvector, and (later) apache_age are extensions, not new services.
  • Bad, because core.link sacrifices per-relationship-type typed columns (e.g. no NOT NULL role column specific to a person↔task link) — mitigate by allowing a plugin-owned side table once a relation type is attribute-rich enough to need one.
  • Bad, because generic operations (tag lookup, artifact discovery) always cost at least one join through core.entity.
  • Bad, because there's no purpose-built temporal query language (unlike XTDB/Datomic); "as of" queries are hand-written SQL range containment, which is more verbose.
  • Bad, because PostgreSQL's native WITHOUT OVERLAPS temporal constraint support is very new (18+); until it's battle-tested, bitemporal validity is enforced by hand-written GiST exclusion constraints instead.

Confirmation¤

Before committing further domains to this shape: implement core.entity + core.link, re-derive the experiment/add-accounting Account/Transaction shapes as a hub_account/hub_transaction + satellites, and run one "as of" query and one cross-domain link query (e.g. "which person does this transaction involve") end to end.

Pros and Cons of the Options¤

Hub–Link–Satellite on PostgreSQL¤

  • Good, because the generic core.link table gives arbitrary cross-linking with real FK integrity.
  • Good, because satellites give bitemporal history "for free," with no separate audit mechanism.
  • Neutral, because it requires discipline (insert-only satellites) that the shape itself doesn't enforce — needs a small core repository/helper layer to make that the easy path.

Per-domain normalized tables + ad hoc FKs (status quo)¤

  • Good, because it's the simplest shape to reason about for a single isolated domain.
  • Bad, because this is exactly the pain already hit on experiment/add-accounting: no shared identity, so linking a Transaction to a Person needs a bespoke join table per pair of types, and it doesn't compose as more domains are added.
  • Bad, because there's no built-in history model — bitemporal columns would be re-invented per table.

Generic EAV / triple-store inside PostgreSQL¤

  • Good, because it's maximally flexible — no schema change is ever needed.
  • Bad, because it loses typed columns and native FK integrity; most queries become self-joins over one giant table.
  • Bad, because the prior art that does this well (Wikibase, RDF stores) runs on purpose-built triple-store engines, not vanilla Postgres tables.

Dedicated bitemporal/Datalog engine as primary store (XTDB / Datomic family)¤

  • Good, because bitemporal semantics and generic facts are native, not hand-built.
  • Bad, because it adds a second engine/ecosystem (JVM/Clojure, often Kafka-backed) alongside Postgres — more operational burden than justified for a few-user system.
  • Neutral, because it's worth a real spike later if hand-rolled bitemporal-on-Postgres proves insufficient.

Event Sourcing + CQRS with a separate event store¤

  • Good, because it gives full replayable history and a clean audit trail of why something changed.
  • Bad, because it needs a second storage system (event store/message bus) plus projection/read-model machinery — already judged overkill for db4's write volume on experiment/health-track-ideas.
  • Neutral, because insert-only satellites already deliver the state-history benefit without adopting the full pattern (see "Note on event sourcing for measurements" above).

More Information¤

Revisit this ADR if: the write volume grows enough that insert-only satellites become a real storage/performance problem, a concrete workflow-auditing need (not just history) appears, or a spike of XTDB/the Datomic family turns out to remove more hand-built bitemporal code than it costs in operational complexity.