ID-111 {111.3} — Reference-item read/browse/detail UI: TECH

ID-111 — Reference-item read/browse/detail UI (TECH)

Spec tier: PRODUCT + TECH + PLAN. This TECH slice was added after ratification of two decisions that diverge from PRODUCT.md’s recommended defaults: B-30 = a NEW reference_list RPC (not the direct reference_items SELECT the spec recommended) and B-28 = richer source-document provenance (load the source_documents row, not ingestion_source alone). Both raise the technical surface above PRODUCT.md’s “low-risk read UI” framing — B-30 introduces an authored-not-applied migration and a new shared read seam; B-28 introduces a second read (an FK join) on the detail page. This TECH covers those two seams and the call-path changes the browse page, search endpoint, and detail page need to consume them. Everything else in PRODUCT.md (the pages, the success-card wiring, the filter UX, the empty/loading/error states) is unchanged and is implemented from PRODUCT.md’s invariants directly.

Predecessors (READ IN FULL): PRODUCT.md {111.2} (numbered invariants B-1..B-31) and RESEARCH-seed-s355.md {111.1} (S355 inventory) in this directory. This TECH does not restate their behaviour; it maps the divergent decisions onto concrete code/SQL changes.

Context

The reference read substrate (reference_items table + reference_search + reference_get_verbatim RPCs) is shipped and has zero TypeScript callers today. PRODUCT.md §Problem documents the RPC signatures verbatim; this section adds only the facts the two new seams depend on, with line references into the squash baseline migration.

Code-intelligence orientation (verbatim, not paraphrased):

gitnexus_query({query: 'reference item search verbatim retrieval read path', repo: 'canonical'}) returned no execution flow over the reference RPCs. Top processes were proc_85_post (POST → IsOk, lib/edit-intent/write-back.ts) and proc_86_post (POST → ResolveAbsolutePath) — both unrelated to references. definitions listed registerContentTools (lib/mcp/tools/content.ts), runItemSearch / runChunkSearch (lib/mcp/tools/search.ts) — the content_items MCP search path, not references — plus the query-keys.ts browse/search/library key factories. No reference_* symbol appeared. This confirms PRODUCT.md’s orientation: the reference read RPCs participate in no indexed flow.
gitnexus_context({name: 'tryQuery', repo: 'canonical'}) → Function:lib/supabase/safe.ts:tryQuery (lines 159–180), called by ~30 routes/pages incl. app/item/[id]/page.tsx and the cron/governance read paths; outgoing call to SupabaseError (lib/supabase/safe.ts). This is the safe-read wrapper the detail page and the source_documents join will use.
gitnexus_context({name: 'generateEmbedding', repo: 'canonical'}) → ambiguous, 3 candidates; the feature-relevant one is Function:lib/ai/embed.ts:generateEmbedding (line 96 — the singleton helper /api/search uses). The other two are script-local (scripts/eval-search.ts:130, scripts/catalogue-standard-sq.ts:2066) and out of scope.
A grep sweep for reference_search|reference_get_verbatim|reference_list across app/ lib/ components/ hooks/ types/ returns the single comment at app/api/ingest/url/route.ts:142 and nothing else — reference_list does not exist anywhere (TS or SQL), confirming it is net-new. gitnexus orientation: no existing symbols match the reference read path — greenfield read surface over existing RPCs; the new reference_list RPC is additive.

Schema-routing fact (load-bearing for the migration — ID-115). lib/supabase/schema.ts documents that after the PostgREST schema-isolation cutover, public is UNEXPOSED and the dedicated api schema is the only Data API surface. Every supabase-js client threads DB_OPTION so .from('x') resolves to api.x and .rpc('y') resolves to api.y at runtime. The existing reference RPCs are therefore defined twice in the squash baseline — a public.reference_search(...) body (lines 4626–4684) and a thin api.reference_search(...) INVOKER wrapper (lines 1059–1067) that does SELECT * FROM public.reference_search(...); likewise for reference_get_verbatim (public 4519–4545, api 1037–1045). Consequence: the new reference_list RPC MUST be created in BOTH schemas — a public.reference_list(...) body plus an api.reference_list(...) wrapper — or the supabase-js .rpc('reference_list') call resolves to a non-existent api.reference_list and 404s at runtime.

Relevant code + SQL (with line references):

supabase/migrations/20260617130000_squash_baseline.sql — the squashed baseline (individual ID-75/ID-110 migration files no longer exist on disk; this is the only migration). Reference seams:
- public.reference_search body — lines 4626–4684 (the WHERE ri.embedding IS NOT NULL, LEFT(...,200) previews, internal embedding*0.6 + fulltext*0.4 blend). MUST NOT be altered (B-25).
- public.reference_get_verbatim body — lines 4519–4545. MUST NOT be altered (B-25).
- api.* wrappers — reference_search 1059–1067, reference_get_verbatim 1037–1045.
- public.reference_items table — lines 7219–7236 (cols: id, title, body, summary, source_url, published_at, primary_domain, primary_subtopic, layer, embedding vector(1024), source_document_id NOT NULL, ingestion_source CHECK IN ('rss_feed','url_import'), op_id, created_at, updated_at). api.reference_items view (security_invoker) 7246–7262.
- public.source_documents table — lines 7460–7485. B-28-relevant columns: original_filename, filename, mime_type, file_size, extraction_method (the producer, e.g. pullmd_* / docling), source_url, created_at (the landed-at timestamp — there is no separate fetched_at column; created_at is the closest provenance signal). api.source_documents view 7507–7530.
- FK: reference_items.source_document_id → source_documents.id ON DELETE RESTRICT — lines 9918–9919 (one source_documents row per reference, NOT NULL, guaranteed present).
- Index idx_reference_items_published_at (published_at DESC) — line 9212. The new reference_list default ordering mirrors this index.
- RLS reference_items_select FOR SELECT TO authenticated USING (true) — lines 10958–10961.
- Grant pattern to mirror for the new RPC: public.reference_get_verbatim REVOKE-from-PUBLIC + GRANT authenticated/service_role at lines 12834–12836; api.reference_get_verbatim at 11753–11755.
lib/supabase/schema.ts — DB_OPTION runtime-schema seam (see above).
app/api/search/route.ts (1–120) — the content_items search route: withRequestContext wrapper, getAuthenticatedClient() + if (!auth.success) return authFailureResponse(auth), generateEmbedding(query.trim()) (lib/ai/embed.ts:96), supabase.rpc('hybrid_search', { query_embedding: JSON.stringify(embedding), … }), export const maxDuration = 60. The {111.8} reference search endpoint mirrors this exactly, swapping the RPC name + params.
app/item/[id]/page.tsx (1–90) — the server-component detail pattern: await createClient() (@/lib/supabase/server), .from(...).select(...).single() with a PGRST116 read-after-write retry loop, notFound() on miss, and a parallel Promise.all of secondary reads each wrapped in tryQuery(...) (@/lib/supabase/safe). The reference detail page reuses this shape: primary read via reference_get_verbatim, secondary source_documents read via tryQuery (B-28).
lib/supabase/safe.ts:159 tryQuery<T> — the safe-read wrapper for the B-28 secondary read.
lib/ai/embed.ts:96 generateEmbedding — the embedding helper for {111.8}.

Proposed changes

Two seams change relative to PRODUCT.md’s recommended defaults; the rest of the feature is unchanged.

Seam 1 — NEW `reference_list` RPC (B-30) + its migration (authored, NOT applied)

Decision (B-31, picked explicitly): SERVER-SIDE filter pushdown. Because B-30 now mandates an RPC for the default list anyway, the four reference filters (primary_domain, primary_subtopic, ingestion_source, published_at date range — PRODUCT.md B-16) are pushed into reference_list as optional parameters rather than applied client-side over a fetched page. Rationale: a single round-trip, the filter predicates ride the same published_at DESC index scan, and pagination (p_limit/p_offset) stays correct under filtering (client-side filtering of a bounded page would make the “load more” count wrong — a B-19 hazard). The Checker accepts either approach per B-31; this spec commits to server-side and the browse page (111.9) is written to that contract.

New function signature (created in BOTH schemas per the ID-115 routing fact):

1
public.reference_list(
2
  p_limit            integer DEFAULT 48,
3
  p_offset           integer DEFAULT 0,
4
  p_primary_domain   text    DEFAULT NULL,
5
  p_primary_subtopic text    DEFAULT NULL,
6
  p_ingestion_source text    DEFAULT NULL,   -- 'rss_feed' | 'url_import' | NULL (no filter)
7
  p_published_from   timestamptz DEFAULT NULL,
8
  p_published_to     timestamptz DEFAULT NULL
9
) RETURNS TABLE(
10
  reference_id       uuid,
11
  title              text,
12
  summary_preview    text,    -- LEFT(COALESCE(summary,''),200) — mirrors reference_search preview shape
13
  body_preview       text,    -- LEFT(body,200)
14
  source_url         text,
15
  published_at       timestamptz,
16
  primary_domain     text,
17
  primary_subtopic   text,
18
  layer              text,
19
  ingestion_source   text,
20
  source_document_id uuid
21
)

The return shape is a strict subset of reference_search’s columns (it omits embedding_score / fulltext_score, which are search-only ranking artefacts) so the browse page can render the default list and search results through the same ReferenceListItem card — the list mode simply has no score fields. This keeps {111.9}‘s card single-shape.

Body (key clauses):

LANGUAGE plpgsql STABLE SECURITY DEFINER + SET search_path = public, extensions (CLAUDE.md function-search_path rule; mirrors the existing reference RPCs).
Selects from public.reference_items ri with the previews computed as reference_search does (LEFT(COALESCE(ri.summary,''),200), LEFT(ri.body,200)) for preview parity.
No WHERE embedding IS NOT NULL — the default list is a plain corpus listing and MUST include embedding-null rows (PRODUCT.md B-12; the B-30 caveat that such rows are listable-but-unsearchable is preserved because search still goes through reference_search).
Filter predicates applied as (p_x IS NULL OR ri.col = p_x) for the three equality filters and (p_published_from IS NULL OR ri.published_at >= p_published_from) / (p_published_to IS NULL OR ri.published_at <= p_published_to) for the range — each filter inert when its param is NULL, composing with AND (PRODUCT.md B-16 compose-AND).
Ordering: ORDER BY ri.published_at DESC NULLS LAST, ri.id then LIMIT p_limit OFFSET p_offset. NULLS LAST realises PRODUCT.md B-12 (null published_at sorts after valued rows, not dropped); the , ri.id tiebreak makes pagination deterministic across pages (rows with equal/NULL published_at keep a stable order so OFFSET paging never skips or repeats a row). Mirrors idx_reference_items_published_at (squash baseline line 9212) for the leading key.
api.reference_list(...) wrapper: SELECT * FROM public.reference_list(p_limit => p_limit, …), LANGUAGE sql (or plpgsql) INVOKER — byte-for-byte the pattern of api.reference_search (squash baseline 1059–1067).

Grants (mirror the existing reference RPCs exactly):

1
REVOKE ALL  ON FUNCTION public.reference_list(integer,integer,text,text,text,timestamptz,timestamptz) FROM PUBLIC;
2
GRANT ALL   ON FUNCTION public.reference_list(...) TO authenticated;
3
GRANT ALL   ON FUNCTION public.reference_list(...) TO service_role;
4
REVOKE ALL  ON FUNCTION api.reference_list(...)    FROM PUBLIC;
5
GRANT ALL   ON FUNCTION api.reference_list(...)    TO authenticated;
6
GRANT ALL   ON FUNCTION api.reference_list(...)    TO service_role;

(REVOKE … FROM PUBLIC covers the anon-EXECUTE concern in the write-tech-spec checklist; reference_list is never granted to anon.)

Migration file (AUTHORED ONLY — NOT applied; see Risks). Create via supabase migration new id111_reference_list_rpc so it lands at supabase/migrations/<new-timestamp>_id111_reference_list_rpc.sql (the squash baseline timestamp is 20260617130000; the new file sorts after it). The migration contains only the two CREATE OR REPLACE FUNCTION statements + the six grant statements above — purely additive; it touches no existing object and therefore cannot alter reference_ingest / reference_search (B-25 satisfied structurally — the migration is CREATE OR REPLACE of a new name only). Filename guard: no client/counterparty name (the id111_… slug is clean).

B-25 confirmation: the migration issues no DROP/ALTER/CREATE OR REPLACE against reference_ingest, reference_search, or reference_get_verbatim — their signatures and bodies are untouched. reference_list is purely additive.

Seam 2 — Detail-page `source_documents` join for richer provenance (B-28)

The detail page (111.6) gains a second read after the primary reference_get_verbatim succeeds. reference_get_verbatim returns source_document_id (NOT NULL, FK ON DELETE RESTRICT — the source_documents row is guaranteed to exist), so the page does:

1
const sd = await tryQuery(
2
  supabase.from('source_documents')
3
    .select('original_filename, filename, mime_type, file_size, extraction_method, source_url, created_at')
4
    .eq('id', reference.source_document_id)
5
    .maybeSingle(),
6
  'reference.detail.source_document',
7
);

Read path: tryQuery from @/lib/supabase/safe (the same wrapper app/item/[id]/page.tsx uses for its secondary reads). Runs in parallel with nothing else here (the primary read must resolve first to know source_document_id), so it is a sequential second await — acceptable, both are single indexed lookups.
Surfaced metadata (B-28): original filename (prefer original_filename, fall back to filename), the extraction_method rendered in plain language (e.g. “Extracted via pullmd” / “Extracted via Docling” — never the raw enum), and the document’s created_at as the “fetched” / landed timestamp formatted DD/MM/YYYY (PRODUCT.md B-27 date rule). source_url is already on the reference row; do not duplicate it.
Degradation (silent-failure discipline): if the source_documents read fails (transport/RPC error), the page logs via tryQuery’s logBestEffortWarn path and renders the detail page WITHOUT the enriched source-doc block, falling back to the ingestion_source plain-language line (PRODUCT.md B-2 — “Imported from URL” / “From an RSS feed”). The page does NOT 404 and does NOT blank: a failed provenance enrichment must not take down a readable reference (PRODUCT.md B-7 spirit — never silently render empty). The primary reference_get_verbatim failure paths (not-found → notFound(), transport error → error+retry) are unchanged from PRODUCT.md B-5/B-7.

Downstream call-path changes (consume the new seam)

Browse list path (111.9): the default-list hook calls supabase.rpc('reference_list', { p_limit, p_offset, p_primary_domain, p_primary_subtopic, p_ingestion_source, p_published_from, p_published_to }) via TanStack useInfiniteQuery (cursor = offset), replacing PRODUCT.md B-30 option (a)‘s direct from('reference_items').select(...). Filters are passed as RPC params (server-side pushdown, B-31), not applied client-side. The search path still POSTs the {111.8} endpoint.
Search endpoint (111.8): unchanged in contract — still embeds + calls reference_search. It does NOT call reference_list (search needs the embedding + score columns). The browse page picks list-vs-search mode and calls the corresponding path.
Types (111.5): ReferenceListItem is now the 11-field reference_list return shape PLUS the two optional score fields embedding_score / fulltext_score (present only on search results). Model the scores as optional (embedding_score?: number) so one type serves both the list RPC and the search RPC. ReferenceDetail is unchanged (14-field reference_get_verbatim shape). A separate small ReferenceSourceDocument type (7 fields) models the B-28 join result; derive field types from Tables<'source_documents'> per CLAUDE.md conventions.

Testing and validation

Behaviour-first (reference/test-philosophy.md); shared Supabase mock for unit tests; bun run test (never bun test). Each row maps a PRODUCT.md invariant to a concrete check.

PRODUCT.md invariant	Verification
B-12 (default list `published_at DESC`, NULLs last, none dropped)	`reference_list` RPC test: rows returned in `published_at DESC` order with NULL-published rows last and present, not filtered out. Browse-page unit: default mode renders that order.
B-16 / B-17 (filters compose AND, server-pushdown)	`reference_list` returns only rows matching each non-NULL filter param; combining domain + source + date range ANDs them; NULL params are inert. Browse-page: changing a filter re-queries the RPC with the param set and reflects it in the URL.
B-19 (pagination reaches all, count never lies)	`reference_list` with `p_limit`/`p_offset` paginates deterministically (the `published_at DESC NULLS LAST, id` tiebreak): no row skipped/repeated across pages under equal/NULL `published_at`.
B-25 (shared RPC seam untouched)	Migration review: the file issues NO `DROP`/`ALTER`/`CREATE OR REPLACE` against `reference_ingest`/`reference_search`/`reference_get_verbatim`; `reference_list` is a new name. Grant block mirrors the existing reference RPCs (REVOKE PUBLIC + authenticated/service_role, no anon).
B-28 (richer source-doc provenance)	Detail-page test: with a resolvable `source_document_id`, the page surfaces original filename + plain-language extraction method + landed date (DD/MM/YYYY); with a failing source_documents read, it degrades to the `ingestion_source` line and still renders the reference (no 404/blank).
B-2, B-5, B-7 (provenance line, 404, error+retry)	Detail-page tests unchanged from PRODUCT.md: unknown/invalid id → `notFound()`; primary RPC transport error → error+retry; provenance line present in all success cases.
B-13, B-14, B-23 (search endpoint)	{111.8} endpoint tests unchanged: embeds + calls `reference_search`, references-only, returns raw score columns. The new `reference_list` path is NOT exercised by the search endpoint.
RPC routing (ID-115)	Manual / integration check that `supabase.rpc('reference_list', …)` resolves at runtime (i.e. `api.reference_list` exists) — the dual-schema creation is what makes the `.rpc()` call non-404. Type-regen (`--schema public,api`) after apply surfaces `reference_list` in `database.types.ts`.

Empirical-verification scope (OQ-3 / Q-EX2). This spec cites NO external-library symbols. Every symbol referenced is internal KH (tryQuery, generateEmbedding, DB_OPTION, the reference RPCs), Postgres built-ins (LEFT, COALESCE, ts_rank, ORDER BY … NULLS LAST), or Next.js/supabase-js framework surface (notFound, .rpc(), .from().select()) — all out of the import-and-call verification scope (external-library symbols only, per shared-discipline §Empirical verification). No verification block is required. The one runtime contract that COULD drift is the ID-115 .rpc()→api routing — covered by the “RPC routing” validation row above (the dual-schema creation is the mitigation, not an unverified assumption).

Risks and mitigations

Migration authored-not-applied (CLAUDE.md / dispatch brief). The Platform DB zjqbrdctesqvouboziae is prod+staging; parallel db push from worktrees collide and an unverified push lands on prod silently (supabase/CLAUDE.md). Mitigation: the executor AUTHORS the id111_reference_list_rpc migration file only. Migration-apply + type-regen are Orchestrator-on-MAIN post-merge intents: (1) cat supabase/.temp/project-ref then relink to the staging ref from .env.local if drifted; (2) supabase db push foreground (it prompts — never background, it hangs); (3) regen types supabase gen types typescript --project-id <ref> --schema public,api > supabase/types/database.types.ts (ID-115 dual-schema, deterministic order). Until applied, supabase.rpc('reference_list') 404s — so the browse-page (111.9) executor’s local unit tests MUST mock the RPC (they do not need the live function), and the E2E (111.10) gate runs only after the Orchestrator applies the migration.
Single-schema-creation footgun (ID-115). If the executor creates only public.reference_list and forgets the api.reference_list wrapper, every .rpc('reference_list') call 404s at runtime while local SQL tests against public pass — a silent prod break. Mitigation: the {N.x} RPC subtask details mandates BOTH functions in one migration file and cites the reference_search dual-definition (squash baseline 1059 + 4626) as the template; the “RPC routing” validation row gates it.
reference_list preview/order drift from reference_search. If the previews or the column set diverge, the browse card can’t render list + search rows uniformly. Mitigation: the RPC reuses reference_search’s exact LEFT(...,200) preview expressions and returns a strict subset of its columns; ReferenceListItem carries the scores as optional.
B-28 join failure cascading to a dead detail page. A source_documents read error must not blank the reference. Mitigation: tryQuery + graceful degradation to the ingestion_source line (Seam 2); the primary read owns the 404/error paths, the enrichment read is best-effort.

Follow-ups

None required for this slice. (If a future need arises to paginate filtered SEARCH results the same way as the list, reference_search would need an offset param — explicitly out of scope here and a B-25-bounded change for ID-71 to coordinate, not this Task.)