SYN-234 spike: EAV vs per-type generated columns for indexed fields

Date: 2026-06-13 Resolves: the open item in ADR-028 §Consequences (“EAV vs per-type generated columns for indexed fields … prototype EAV first, spike against real query patterns”). Outcome: EAV: a single entity_fields(type, id, field_name, value) side table, indexed on (field_name, value).

The question

A user-defined type (e.g. book) declares a whitelist of indexed fields in .synapse/types.toml:

[types.book]
store_dir = "books"
indexed = ["author", "year"]      # ≤ 16, ADR-028

Those fields must become filterable / group-by-able through the existing read surfaces: query (ADR-023), list, search: while:

1. preserving ADR-023’s invariant: a request string never reaches a SQL identifier position; field_name is bound as a value, never interpolated;
2. keeping the index disposable and rebuildable (syn reindex drops and recreates it from the Markdown source of truth: see AGENTS.md);
3. adding a new type or field staying purely additive (types.toml edit only, no schema migration, no ALTER TABLE): the expand-contract discipline ADR-028 commits to.

Two candidate storage shapes were on the table.

Option A: EAV side table

CREATE TABLE entity_fields (
    type       TEXT NOT NULL,
    id         TEXT NOT NULL,
    field_name TEXT NOT NULL,
    value      TEXT,
    PRIMARY KEY (type, id, field_name),
    FOREIGN KEY (type, id) REFERENCES entities(type, id) ON DELETE CASCADE
) STRICT;
CREATE INDEX idx_ef_field_value ON entity_fields (field_name, value);

One row per (entity, indexed field). Filter / group-by join entities and bind both field_name and value:

SELECT e.type, e.id, e.title
FROM entities e
JOIN entity_fields ef ON ef.type = e.type AND ef.id = e.id
WHERE ef.field_name = ?1 AND ef.value = ?2;   -- ?1, ?2 both bound

Option B: per-type generated / real columns

Either ALTER TABLE entities ADD COLUMN gc_book_author TEXT GENERATED ALWAYS AS (json_extract(frontmatter, '$.author')) per indexed field, or a per-type side table with one real column per field.

Empirical spike

Prototyped Option A against the real schema subset at 10 000 synthetic entities, each with two indexed fields (20 000 EAV rows), on SQLite 3.51 (the bundled floor is older but the planner behaviour is the same). Full script and output reproduced below.

Point filter on a custom indexed field: index-backed SEARCH, not a scan:

QUERY PLAN
|--SEARCH ef USING INDEX idx_ef_field_value (field_name=? AND value=?)
`--SEARCH e USING INDEX sqlite_autoindex_entities_1 (type=? AND id=?)

Group-by on a custom indexed field (count per year): index-backed, covering index on the entities PK:

QUERY PLAN
|--SEARCH ef USING INDEX idx_ef_field_value (field_name=?)
|--SEARCH e USING COVERING INDEX sqlite_autoindex_entities_1 (type=? AND id=?)
`--USE TEMP B-TREE FOR ORDER BY

Injection invariant holds by construction: binding a SQL payload as field_name matches nothing and leaves the schema intact:

SELECT COUNT(*) FROM entity_fields
WHERE field_name = 'author''; DROP TABLE entities; --' AND value = 'author_7';
-- hits = 0 ; SELECT COUNT(*) FROM entities still returns 10000

This is the same guarantee ADR-023 gives the hot columns, but stronger: with columns the field identity lives in the SQL text; with EAV it lives in a bound value, so the whitelist check (field_name ∈ registry.indexed[kind]) is a defence-in-depth in-memory check, not the only thing standing between a request and the SQL identifier position.

Decision: EAV

EAV wins on every axis that matters for a single-user, local, Markdown-as-truth KG with a disposable index. Generated columns only pull ahead at a scale and query complexity Synapse does not target, and they would break the property that keeps types.toml purely additive (adding a type must never mutate the index schema). The join cost EAV pays is, per the query plans above, an indexed SEARCH: invisible at 50k entities. If SYN-227’s perf budgets ever surface a real regression, a STORED generated column is a localised, backward-compatible optimisation we can add then without reopening this decision.

The closed core stays in entities. Only the per-type indexed whitelist projects into entity_fields. The built-in hot columns (status, owner, priority, due) keep their dedicated columns: EAV is strictly additive surface for the open skin, never a replacement for the frozen spine (ADR-028: freeze the spine, not the skin).

Implementation consequences (for the slices that follow)

• Migration 0006 adds entity_fields + its index. Additive; no change to existing rows.
• reindex / upsert_entity populate entity_fields for a kind’s registered indexed fields (resolved from Registry), bounding field_name to the whitelist before insert.
• query/list/search gain a registry-checked dynamic-field filter that resolves field_name against registry.indexed[kind] (a closed in-memory set) and binds it: the group_column allow-list in core::mcp::query extends to “or a registered indexed field for the filtered kind”, never to an arbitrary string.
• Built-in hot columns are untouched: no data migration, no behaviour change for the six built-ins.

Reproduction

CREATE TABLE entities (
    type TEXT NOT NULL, id TEXT NOT NULL, title TEXT NOT NULL,
    status TEXT, owner TEXT, priority TEXT, frontmatter TEXT NOT NULL,
    PRIMARY KEY (type, id)
) STRICT;
CREATE TABLE entity_fields (
    type TEXT NOT NULL, id TEXT NOT NULL, field_name TEXT NOT NULL, value TEXT,
    PRIMARY KEY (type, id, field_name),
    FOREIGN KEY (type, id) REFERENCES entities(type, id) ON DELETE CASCADE
) STRICT;
CREATE INDEX idx_ef_field_value ON entity_fields (field_name, value);

WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM n WHERE i < 10000)
INSERT INTO entities(type,id,title,frontmatter)
SELECT 'book','b'||i,'Book '||i,'{}' FROM n;

WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM n WHERE i < 10000)
INSERT INTO entity_fields(type,id,field_name,value)
SELECT 'book','b'||i,'author','author_'||(i % 50) FROM n
UNION ALL
SELECT 'book','b'||i,'year', CAST(2000 + (i % 26) AS TEXT) FROM n;
ANALYZE;

EXPLAIN QUERY PLAN
SELECT e.type, e.id, e.title FROM entities e
JOIN entity_fields ef ON ef.type=e.type AND ef.id=e.id
WHERE ef.field_name='author' AND ef.value='author_7';