App reference

ADR-0001: JSONB query convention for the table module

Metadata

  • Status: Accepted
  • Date: 2026-07-22
  • Deciders: Eagraí Clainne Team
  • Context: Shipped in refactor(database): add query seam (List/Count/FindBy/FindAllBy)
  • Related: ADR-0009 (service-layer authorization, formerly RFC 003) — consumes these lookups

Context

Every entity is stored as a single JSONB column (data) keyed by uid, and the internal/database table module is generic over the four protobuf message types (Table[T]). Until now that module exposed exactly one read primitive beyond Read(uid):

Scan(fn func(id string, data T) error) error   // read every row, deserialize every blob

Callers did all filtering, limiting and counting in Go after a full-table scan:

  • findUserByEmail scanned all users and compared with strings.EqualFold — the idx_user_email index was never used.
  • GetUserRewards scanned all rewards and discarded non-matching rows one at a time.
  • All four ListXxx handlers applied limit with a Go slice-length check — no SQL LIMIT.
  • emailExists keyed off err.Error() == "user not found" — a stringly-typed signal leaking across the seam.

This is a shallow interface: narrow, but the real work sits on the callers' side. To deepen it we needed the table module to answer the questions callers actually ask, in SQL. That raised a design question with no precedent in the codebase: how does a caller name a field to query when the field lives inside a JSONB document, and what are the matching semantics? This ADR records that convention so a second query pattern does not diverge from it.

Decision

The table module exposes a Querier[T] interface and answers queries in SQL:

List(limit int) ([]T, error)                                  // SELECT ... [LIMIT $1]
Count() (int, error)                                           // SELECT COUNT(*)
FindBy(field, value string) (id string, data T, found bool, err error)   // first match
FindAllBy(field, value string) ([]T, error)                   // all matches

The following rules are the convention:

  1. Fields are named by a dotted path. field is translated to a JSONB accessor by jsonbPath: each segment before the last descends with ->, the final segment extracts text with ->>. So "email" becomes data->>'email' and "user.uid" becomes data->'user'->>'uid'. This lets FindAllBy("user.uid", …) reach a nested reference.

  2. Path segments are validated, never user-supplied. Every segment must match ^[a-zA-Z0-9_]+$; an invalid field returns an error before any SQL is built. Fields are in-code constants — the validation exists to keep the string interpolation injection-proof, not to accept arbitrary input. Values are always passed as bind parameters ($1).

  3. Equality matching is case-insensitive: WHERE lower(<path>) = lower($1). This preserves the historical EqualFold behaviour for email (emails are not normalised on write) and is a harmless no-op for the lowercase-hex UUID fields.

  4. A miss is not an error. FindBy returns found = false (not a wrapped sql.ErrNoRows) when nothing matches, replacing the previous err.Error() string check.

  5. Every field queried this way must have a matching functional index. Because matching uses lower(<path>), a plain expression index does not qualify. The two shipped lookups are backed by:

    CREATE INDEX idx_user_email_lower ON "user" ((lower(data->>'email')));
CREATE INDEX idx_reward_user_uid  ON reward  ((lower(data->'user'->>'uid')));

Scan was deleted; all seven callers moved onto Querier.

Consequences

Positive

  • Leverage. WHERE/LIMIT/COUNT live in one module; every list and lookup becomes index-backed, and the stringly-typed "not found" is gone in favour of a bool.
  • The interface is the test surface. Uniqueness, pagination and nested-path lookups are asserted once against the table module (unit tests via sqlmock, plus the real-Postgres harness) instead of re-tested as Go-loop logic in each service.
  • Deletion test passes. Remove FindBy/FindAllBy/List and the SQL knowledge scatters back into each service — it concentrates here.

Negative / trade-offs

  • New queryable field ⇒ new lower() index. Forget it and the query silently degrades to a sequential scan. This coupling between the convention and schema.sql is the main tax and the reason this ADR exists.
  • The original non-lower idx_user_email is now redundant for these lookups (kept for any future case-sensitive use; candidate for removal).
  • The convention deliberately covers only equality. Ranges, ORDER BY, and offset/cursor pagination are out of scope; List caps rows but does not order them. A future need for those should extend — and be recorded against — this ADR rather than growing ad-hoc SQL back in the services.
  • field is trusted developer input, not a general query language. That is intentional: the seam stays narrow and injection-proof, at the cost of not being caller-programmable.

Amendment (2026-07-22): containment probes for array references

The equality convention above cannot reach a reference stored inside a JSON array — data->'users'->>'uid' is null when users is an array, so the reference-integrity work (integrity & intake review, candidate A) could not express "which events reference user X" with a dotted path. Rather than grow a second ad-hoc pattern, this amendment records the convention for that case:

  • Containment, not paths. TxTable.CountByContains(probe) counts rows where data @> $1::jsonb. The probe is a JSON document built in code (domain.UserBackRefs), never from user input; the value is a bind parameter.
  • Matching is exact (case-sensitive). Containment has no lower(); the probed values are lowercase-hex UUIDs, where case-insensitivity was always a no-op.
  • Indexing: the per-table GIN indexes on data (already in schema.sql) serve whole-document containment. No per-field index tax, but probes must anchor at the document root ({"users":[{"uid":…}]}, not a bare fragment) to stay on that index.
  • Scope: transactional existence/count checks only — currently the user back-reference guard on delete. Reading rows back out still goes through the equality convention above.

Amendment (2026-08-08): the convention is dialect-translated (ADR-0021)

Storage now runs on two engines behind a dialect seam — SQLite (default) and PostgreSQL (ADR-0021). The conventions above are the caller contract and are unchanged: dotted paths for equality (case-insensitive via lower()), root-anchored probe documents for containment (exact, case-sensitive). What changed is who renders them:

  • On PostgreSQL everything renders exactly as written above.
  • On SQLite the dotted-path SQL survives verbatim (->/->> match the Postgres operators from 3.38); containment probes are translated by the sqlite dialect into json_extract equality per object path and EXISTS (SELECT 1 FROM json_each(...)) per array element, preserving @> semantics. There is no GIN equivalent, so containment runs as a table scan — acceptable while probes only guard deletes and role checks on family-sized tables.

New probe shapes need no schema or dialect work as long as they are built from objects, arrays and scalars; null in a probe is rejected loudly.

Alternatives considered

  • Keep Scan, filter in Go. The status quo. Rejected: full-table scans on every lookup, unused indexes, and a stringly-typed not-found signal — the friction this change removes.
  • Case-sensitive equality with value normalisation. Lowercase emails on write and match exactly. Rejected for now: it needs a data migration for existing rows and a write-path invariant; lower() on both sides preserves current behaviour with only an index change.
  • A general predicate/expression API (e.g. a query builder or passing SQL fragments). Rejected as over-built for four entities with a handful of equality lookups; it would widen the seam and reopen the injection surface the dotted-path validation closes.