A temporal EAV entity store in Postgres
Part 1 — Storage concept

One schema, a dozen domain types, full history on every field. These are field notes from designing and operating a generic entity store on Postgres + TypeORM: the patterns it combines, the exact update semantics as a behavior matrix, the SQL I use for fetching — and the caveats you only learn in production.

Goal

Accept one deliberately complex design in exchange for permanent velocity: ship new domain entities through a unified interface at incredible speed, with no DDL operations ever again. The schema below is defined once. Every future business object — a "product", a "category", a "promo campaign", whatever the domain invents next — is the same thing, an entity with typed, versioned attributes, added as data rather than as a migration, and it inherits history, relations and point-in-time reads the moment it exists.

The patterns, one line each

• EAV (Entity–Attribute–Value) — an object's fields are rows in an attribute table, not columns, so new fields need no DDL.
• Temporal versioning — nothing is updated in place; every value carries active_from/active_to and an update closes the old row and opens a new one.
• Half-open intervals — a value is visible at time t iff active_from <= t AND (active_to > t OR active_to IS NULL); intervals are [from, to).
• Family typing — an entity's "class" is a foreign key to a family table (product, category, campaign…), which scopes every query.
• Relations as data — parent/child links are versioned rows in a relation table, so hierarchy changes are history too, not overwrites.
• Batch CTE reads — views are assembled from a few raw CTE queries that fetch all attributes and relations for N entities at a timestamp, then pivoted in application code.

The six tables

-- 1. The type of an entity. Ids are assigned from application constants,
--    not a sequence: family 1 = 'entity', 2 = 'product', 3 = 'category', ...
CREATE TABLE sm_entity_family (
  id    integer PRIMARY KEY,
  name  varchar
);
CREATE INDEX idx_sm_entity_family_name ON sm_entity_family (name);

-- 2. The object itself. Nothing here but identity, lifetime and type.
CREATE TABLE sm_entity (
  uuid                    uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  active_from             timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  active_to               timestamptz,                    -- NULL = still active
  fk_sm_entity_family_id  integer REFERENCES sm_entity_family(id),
  CONSTRAINT chk_sm_entity_window CHECK (active_from <= active_to)
);
CREATE INDEX idx_sm_entity_window ON sm_entity (active_from, active_to);

-- 3. Field definitions, created lazily on first use.
--    type drives which val_* column carries the value.
CREATE TABLE sm_entity_attr_name (
  uuid  uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  name  varchar UNIQUE,   -- 'name', 'color', 'order', 'parentCategory', ...
  type  varchar           -- STR | INT | NUM | BOOL | DATE
                          -- | REL_UUID_CHILD_OF | REL_UUID_PARENT_OF | TAG_SET
);

-- 4. Field values: one row per (entity, field, version).
--    Exactly one val_* column is meaningful per row, chosen by attr_name.type.
CREATE TABLE sm_entity_attr (
  uuid                         uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  val_str                      varchar,
  val_int                      integer,
  val_num                      double precision,
  val_date                     timestamptz,
  active_from                  timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  active_to                    timestamptz,               -- NULL = current value
  fk_sm_entity_uuid            uuid NOT NULL REFERENCES sm_entity(uuid),
  fk_sm_entity_attr_name_uuid  uuid NOT NULL REFERENCES sm_entity_attr_name(uuid),
  CONSTRAINT chk_sm_entity_attr_window CHECK (active_from <= active_to)
);
CREATE INDEX idx_sm_entity_attr_val_str  ON sm_entity_attr (val_str);
CREATE INDEX idx_sm_entity_attr_val_int  ON sm_entity_attr (val_int);
CREATE INDEX idx_sm_entity_attr_val_num  ON sm_entity_attr (val_num);
CREATE INDEX idx_sm_entity_attr_val_date ON sm_entity_attr (val_date);
CREATE INDEX idx_sm_entity_attr_window   ON sm_entity_attr (active_from, active_to);
CREATE INDEX idx_sm_entity_attr_fk_dates ON sm_entity_attr
  (active_from, active_to, fk_sm_entity_attr_name_uuid);

-- 5. Versioned edges between entities. The edge's "field name" lives in
--    attr_name too (type REL_UUID_CHILD_OF / REL_UUID_PARENT_OF).
--    parent is nullable: root nodes are edges with a NULL parent.
CREATE TABLE sm_entity_rel (
  uuid                         uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  fk_sm_entity_parent_uuid     uuid REFERENCES sm_entity(uuid),
  fk_sm_entity_child_uuid      uuid NOT NULL REFERENCES sm_entity(uuid),
  fk_sm_entity_attr_name_uuid  uuid NOT NULL REFERENCES sm_entity_attr_name(uuid),
  active_from                  timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  active_to                    timestamptz
);
CREATE INDEX idx_sm_entity_rel_parent ON sm_entity_rel
  (fk_sm_entity_parent_uuid, active_from, active_to);
CREATE INDEX idx_sm_entity_rel_child  ON sm_entity_rel
  (fk_sm_entity_child_uuid, active_from, active_to);

-- 6. Tenancy binding: which customer an entity belongs to.
--    A versioned row like everything else, so ownership has history too
--    (and an entity could in principle move between customers).
CREATE TABLE sm_customer_entity (
  uuid               uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  customer_id        varchar NOT NULL,
  fk_sm_entity_uuid  uuid NOT NULL REFERENCES sm_entity(uuid),
  active_from        timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  active_to          timestamptz
);
CREATE INDEX idx_sm_customer_entity_lookup ON sm_customer_entity
  (customer_id, active_from, active_to);

How a domain type binds to this schema — the metadata-driven repository layer on top — deserves its own write-up: Part 2 — The abstract repository. Here the focus stays on the storage itself: what the SQL does, and how values behave over time. The part that deserves the most documentation is what exactly happens on update.

Behavior matrix: field updates

An update is expressed as a pair: the old field state (the row to close) and the new field state (the row to open). Both sides are optional, and both may carry explicit timestamps. That gives a surprisingly rich decision table. Rows marked with a left border are the ones that bite.

Scalar fields (str / int / num / bool)

Relation fields (child-of / parent-of)

Entity lifecycle

The fetching SQL

Reading an EAV entity through ORM joins multiplies rows and round-trips, so the hot read path is raw SQL: one CTE for attribute values, one per relation direction, executed for a whole batch of uuids at once and pivoted in application code with a groupBy. Everything below is parameterized: $1 = uuid array, $2/$3 = the point-in-time timestamp, $4 = family id.

-- a row is visible at timestamp t iff:
active_from <= t::timestamptz
AND (active_to > t::timestamptz OR active_to IS NULL)

-- intervals are half-open [from, to):
--   a value closed at exactly t is NOT visible at t
--   a value opened at exactly t IS visible at t
-- this is what makes a close+open handover at one instant unambiguous

WITH targets AS (
  SELECT e.uuid, e.active_from, e.active_to,
    CASE
      WHEN e.active_to IS NOT NULL AND e.active_to < $3::timestamptz THEN 0
      ELSE 1
    END AS is_inactive
  FROM sm_entity e
  WHERE e.uuid = ANY($1::uuid[])
),
entity_data AS (
  SELECT * FROM (
    SELECT
      e.uuid                AS e_uuid,
      e.active_from         AS e_active_from,
      e.active_to           AS e_active_to,

      ef.uuid               AS ef_attr_uuid,
      ef.name               AS ef_attr_name,
      ef.type               AS ef_attr_type,

      ev.uuid               AS ev_uuid,
      ev.val_str            AS ev_val_str,
      ev.val_int            AS ev_val_int,
      ev.val_num            AS ev_val_num,
      ev.val_date           AS ev_val_date,
      ev.active_from        AS ev_active_from,
      ev.active_to          AS ev_active_to,

      efam.name             AS family_name,
      efam.id               AS family_id
    FROM targets t
      INNER JOIN sm_entity e            ON e.uuid = t.uuid
      INNER JOIN sm_entity_attr ev      ON e.uuid = ev.fk_sm_entity_uuid
      INNER JOIN sm_entity_attr_name ef ON ef.uuid = ev.fk_sm_entity_attr_name_uuid
      INNER JOIN sm_entity_family efam  ON e.fk_sm_entity_family_id = efam.id
    WHERE e.active_from <= $2::timestamptz
      AND (e.active_to > $3::timestamptz OR e.active_to IS NULL)
      AND ev.active_from <= $2::timestamptz
      AND (ev.active_to > $3::timestamptz OR ev.active_to IS NULL)
      AND e.fk_sm_entity_family_id = $4
  ) AS subquery
  ORDER BY e_uuid, ev_active_from DESC
)
SELECT * FROM entity_data;

-- direction is baked in at build time:
--   child-of : join on fk_sm_entity_child_uuid,  sub-entity = parent
--   parent-of: join on fk_sm_entity_parent_uuid, sub-entity = child
WITH targets AS (
  SELECT e.uuid, e.active_from, e.active_to, e.fk_sm_entity_family_id
  FROM sm_entity e
  WHERE e.uuid = ANY($1::uuid[])
),
entity_rel_data AS (
  SELECT * FROM (
    SELECT
      t.uuid                       AS e_uuid,
      t.active_from                AS e_active_from,
      t.active_to                  AS e_active_to,

      rel.uuid                     AS rel_uuid,
      rel.active_from              AS rel_active_from,
      rel.active_to                AS rel_active_to,
      rel.fk_sm_entity_parent_uuid AS rel_parent_uuid,
      rel.fk_sm_entity_child_uuid  AS rel_child_uuid,

      sub_se.uuid                  AS sub_uuid,
      sub_se.active_from           AS sub_active_from,
      sub_se.active_to             AS sub_active_to,

      ef.uuid                      AS ef_attr_uuid,
      ef.name                      AS ef_attr_name,
      ef.type                      AS ef_attr_type,

      efam.name                    AS family_name,
      efam.id                      AS family_id
    FROM targets t
      INNER JOIN sm_entity_rel rel      ON t.uuid = rel.fk_sm_entity_child_uuid   -- or parent_uuid
      INNER JOIN sm_entity sub_se       ON sub_se.uuid = rel.fk_sm_entity_parent_uuid -- or child_uuid
      INNER JOIN sm_entity_attr_name ef ON ef.uuid = rel.fk_sm_entity_attr_name_uuid
      INNER JOIN sm_entity_family efam  ON t.fk_sm_entity_family_id = efam.id
    WHERE t.active_from <= $2::timestamptz
      AND (t.active_to > $3::timestamptz OR t.active_to IS NULL)
      AND rel.active_from <= $2::timestamptz
      AND (rel.active_to > $3::timestamptz OR rel.active_to IS NULL)
      AND t.fk_sm_entity_family_id = $4
      AND ef.type = 'REL_UUID_CHILD_OF'   -- or REL_UUID_PARENT_OF
  ) AS subquery
  ORDER BY e_uuid, rel_active_from DESC
)
SELECT * FROM entity_rel_data;

-- 1. collect every timestamp at which anything changed for the entity:
SELECT DISTINCT active_from AS tsaf, active_to AS tsat
FROM sm_entity_attr
WHERE fk_sm_entity_uuid = $1;

SELECT DISTINCT active_from AS tsaf, active_to AS tsat
FROM sm_entity_rel
WHERE fk_sm_entity_parent_uuid = $1
   OR fk_sm_entity_child_uuid  = $1;

-- 2. union + sort the timestamps; for each instant T and each field:
--    before = the row with active_to   = T   (what was closed at T)
--    after  = the row with active_from = T   (what was opened at T)
-- a (before, after) pair at one instant IS the diff — no diffing logic needed,
-- the temporal model stores changes natively.

-- what the entity looks like at one moment
-- $1 = entity uuid, $2 = timestamp (now() while debugging)
-- BOTH windows matter: the entity's AND each attribute's —
-- an attribute row is meaningless if its entity is inactive at $2
SELECT an.name,
       an.type,
       a.val_str, a.val_int, a.val_num, a.val_date,
       a.active_from, a.active_to
FROM sm_entity e
JOIN sm_entity_attr a       ON a.fk_sm_entity_uuid = e.uuid
JOIN sm_entity_attr_name an ON an.uuid = a.fk_sm_entity_attr_name_uuid
WHERE e.uuid = $1
  AND e.active_from <= $2::timestamptz
  AND (e.active_to  >  $2::timestamptz OR e.active_to IS NULL)
  AND a.active_from <= $2::timestamptz
  AND (a.active_to  >  $2::timestamptz OR a.active_to IS NULL)
ORDER BY an.name;
-- no rows for an entity you know exists? check the entity window first:
-- if e.active_to < $2 the entity is inactive and you must read at
-- active_to − ε to see its last state.

-- more than one row active for the same field at the same instant —
-- this is the state later writes reject with "Object schema is invalid"
SELECT an.name, count(*)
FROM sm_entity_attr a
JOIN sm_entity_attr_name an ON an.uuid = a.fk_sm_entity_attr_name_uuid
WHERE a.fk_sm_entity_uuid = $1
  AND a.active_from <= $2::timestamptz
  AND (a.active_to > $2::timestamptz OR a.active_to IS NULL)
GROUP BY an.name
HAVING count(*) > 1;

-- windows deliberately unfiltered: this is the history view,
-- for reading a field's whole life in one look
SELECT an.name, a.val_str, a.val_int, a.active_from, a.active_to
FROM sm_entity_attr a
JOIN sm_entity_attr_name an ON an.uuid = a.fk_sm_entity_attr_name_uuid
WHERE a.fk_sm_entity_uuid = $1
ORDER BY an.name, a.active_from;

Caveats

Everything below was learned the concrete way. If you build something similar, budget for these up front.

• Boundary semantics are exclusive-on-close. A value closed at exactly T is invisible at T; the value opened at T is visible. Off-by-one bugs in reports come from forgetting [from, to). The payoff is on the query side: "year 2025" is simply active_from >= '2025-01-01' AND active_from < '2026-01-01' — adjacent ranges meet exactly, with no 2025-12-31 23:59:59.999 end-of-day arithmetic and no lost last millisecond.
• Explicit timestamps can create gaps and overlaps. When callers supply both a close time and an open time, nothing forces them to meet — so pick a conflict-resolution strategy up front and enforce it in one place. The one that served best: treat active_to as write-once — close a row only while its active_to is still NULL, and reject attempts to move a close time that is already set. That single rule prevents silent history rewrites and most overlaps. A gap stays representable; an overlap must not be — it poisons the timeline and later writes fail validation.
• Type mismatches fail silently at the storage layer. A wrong-typed value is coerced to NULL by the type guards instead of raising — by the time a value reaches the generic value table, there is no schema left to object. So the schema has to be rebuilt virtually: every field definition carries virtual schema validators (type + business checks attached to the field's metadata, plus DTO validation at the API edge), and every write must pass through them. A value that bypasses the virtual schema is not rejected — it is silently lost as NULL.
• Relations to inactive targets must be handled, or at least surfaced. The target lookup is time-filtered, so linking to a just-closed entity quietly stores an edge to nothing. The same trap fires on deactivation: close a mid-level category in a category tree and the child edges pointing at it are untouched — at now() the subcategories still exist, their parent edge resolves to an inactive entity, and the whole subtree silently drops out of hierarchy views. Cascade the close, re-parent the children, or block deactivation while active children exist — but decide, don't default.
• You cannot detach a relation by writing null. That path is a deliberate no-op; detaching means explicitly closing the edge. Design your API accordingly.
• Closed entities need timestamp clamping. "Show me a deleted item" is a read at active_to − ε, not at now(). The ε exists to capture the entity's literal last moment: at active_to itself everything is already invisible (exclusive close), so you step back just before the close, where the entity row and its last field versions are all still inside their windows. Centralize that clamp, or every consumer reinvents it.
• Lazy-created attribute names race under concurrency. Field definitions are created on first use with a short cache; two concurrent first-writes can duplicate a name. Seed attr names in a migration, or add a unique index and retry.
• Know your ORM's undefined-in-where behavior. TypeORM's findOneBy({ uuid: undefined }) drops the predicate and returns the first row of the table (typeorm#9316). In a generic store that "first row" is some other family's entity. Guard every dynamic lookup.
• You lose the database as an enforcer of business constraints. "Product name is unique within its category", "this field is required", "price must be positive" — none of it can be expressed as a constraint, because values live in one generic table sliced by family, field and time. A plain UNIQUE index on val_str would collide across unrelated fields, families and historical versions. Every invariant becomes an application-level check inside the write transaction — and those checks are racy under concurrency unless you serialize writers with locks. This is the single biggest tax of EAV: the schema can no longer say "no".
• Row growth is multiplicative. Rows ≈ entities × fields × versions. Index the value columns and (active_from, active_to), and consider a partial index on active_to IS NULL — the "current state" predicate in almost every query.
• N+1 is the default failure mode. Any per-entity read inside a loop defeats the design. The batch CTEs exist precisely so list endpoints make ~3 queries total; treat single-entity fetches in loops as a code smell.
• Analysts will hate you a little. Ad-hoc SQL against EAV means pivots every time. Keep a set of views (or the debugging queries above) ready to share.

The schema flexibility is free at write time and paid for at read time — with interest, unless reads are batched and the update semantics are written down.

Verdict after running it

Was EAV + Temporal the right call? Under these constraints — yes. Thirteen domain types now ride on the same six tables; a new one costs about a day including tests and ships with point-in-time history that auditors and support keep using. That is the amortization argument actually happening, not hypothetically. The price is real too: pivot-heavy reads, weaker type safety at the storage layer, and a behavior matrix (above) that must be documented because nobody can infer it from a table definition. If your domain is stable and small, use plain tables and an audit log. If it grows faster than your team, this trade is worth making — deliberately, with the caveats list taped to the wall.

Before you build: the decision checklist

None of these have a universally right answer — but all of them have a wrong one: deciding by accident. Settle each explicitly, write it down, and enforce it in one place.

• Same-value write strategy. Per field (or field type): diff-and-skip, or re-version on every write? Default-everything-to-versioning inflates history; default-everything-to-skip loses "re-confirmed at T" signals. (my choice — re-version by default; sync jobs diff before writing)
• Timestamp conflict resolution. Is active_to write-once? What happens when a caller supplies a close time that is already set — reject, ignore, or move? Are gaps allowed; are overlaps ever? (my choice — active_to is write-once: close only while NULL, reject moves; gaps allowed, overlaps never)
• Boundary convention. Commit to [from, to) everywhere — storage, API contracts, and the reports your analysts write — and document it once, visibly. (my choice — [from, to), no exceptions)
• Relation detach semantics. Writing null is a no-op, so removal needs an explicit close operation. Decide what your API exposes and what a "root" node looks like. (my choice — explicit close-edge operation; a root is an edge with a NULL parent)
• Hierarchy deactivation policy. When a mid-tree entity closes: cascade to the subtree, re-parent the children, or block while active children exist? (my choice — no automatic cascade; each family's service validates its own deactivation rules)
• Inactive-entity reads. Where does the timestamp clamp (active_to − ε) live, and what is ε? One central place, or every consumer reinvents it. (my choice — one clamp in the shared read path; ε steps back to the entity's literal last moment before close, so its final field versions are still inside their windows — 2s in practice)
• Virtual schema enforcement. Where do the validators live, which write paths are exempt (none, ideally), and does a failed check reject the write or coerce the value? (my choice — validators attached to field metadata, every write passes through them, failure rejects the whole transaction)
• Business invariants under concurrency. Uniqueness and cross-field rules are application checks now — pick the serialization mechanism (advisory locks, serializable transactions, single-writer queues) that keeps them from racing. (my choice — in-transaction pre-write checks; the residual race window is accepted at current write volume)
• Field-definition provisioning. Seed attribute names in a migration, or allow lazy creation — and if lazy, add the unique index and a retry path for the duplicate-name race. (my choice — lazy creation behind a unique index on name, with a short lookup cache)
• Read discipline. Batch CTEs for every list endpoint; treat a per-entity query in a loop as a review blocker. Add the partial index on active_to IS NULL before growth forces it. (my choice — batch CTEs everywhere; the partial index is on the roadmap, added before scale demands it)
• History retention. Versions accumulate forever by default. Decide now whether old versions are ever archived or compacted, and who is allowed to ask for "as of three years ago". (my choice — keep everything; history is the product feature, revisit only if growth says otherwise)