field_value_ranks

field_values ranked per (identity, field)

What you'll learn

You're writing SQL that reads field_value_ranks and need its columns, what each holds, and the DDL as the spec states it.

  • every column and its type
  • what the object holds and who writes it
  • the DDL as the spec states it

A view. field_values ranked per (identity, field): confidence DESC, newest first.

Columns

id, identity_id, field, value, source, confidence, run_id, created_at, rank.

Definition

From spec/ledger.sql, which is the machine-checkable form of SPEC.md §3:

-- The current-value projection (ADR-003) is two views, not one: "highest
-- confidence within the freshness window" (SPEC §3) cannot be answered by an
-- unparameterized view, because the window is a per-caller argument (SPEC
-- §7's per-step `cache:`), not a fact about the row. field_value_ranks is the
-- one definition of the RANKING rule (confidence DESC, ties broken by newest
-- created_at); current_fields is that ranking with no window applied
-- (rank = 1) — the plain `gtme query` answer. The runner's windowed
-- projection (internal/ledger/project.go) reads field_value_ranks directly
-- and takes the first in-window row per field, falling through a stale
-- top-ranked row to the next-best one — the same ranking, just windowed
-- where the window is actually known.
CREATE VIEW field_value_ranks AS
SELECT id, identity_id, field, value, source, confidence, run_id, created_at,
       ROW_NUMBER() OVER (
         PARTITION BY identity_id, field
         ORDER BY confidence DESC, created_at DESC
       ) AS rank
FROM field_values;

Used in

  • Interrogate: The view that ranks every row of a field, which the history query reads