Epic: Things Listing Enhancements — Implementation Plan

[OAGr]

Things Listing Enhancements — Implementation Plan

TL;DR

The /things universal index needs source_table filtering, verdict status indicators per row, a search pagination bug fix, and record-lookup support for 3 missing tables. This plan uses a LEFT JOIN approach (proven in grants/personnel) to add verdict data to the listing query in a single PR (~130 lines across 5 files). Coverage dots are explicitly excluded — generic metadata coverage is not actionable for a heterogeneous index. Phase 1 is the entire implementation, achievable in one session.

Problem

Wiki maintainers using /things to audit data quality across domains can't filter by source table or see which records are verified vs unchecked. The search endpoint inflates pagination totals during trigram fallback (line 275 of things.ts), and clicking "Record Data" on things from facts, resources, or research_areas tables produces a 400 error because those tables aren't in the record-lookup whitelist.

Current State

Asset	Status	Gap
things.ts GET /	Works, 4 filters	No source_table filter; no verdict data in response
things.ts GET /stats	Returns byType, byEntityType	No bySourceTable breakdown
things.ts GET /search line 275	Math.max(ftsTotal, combined.length)	Inflates total during FTS+trigram fallback
record-lookup.ts whitelist	15 tables	Missing: facts (composite key), resources, research_areas
verification-join.ts	Shared helpers for verdict LEFT JOIN	Used by grants/personnel; not yet by things
things-table.tsx	FilterChips, SortHeader, PaginationControls	No verdict dots, no source_table filter

Proposed Approach

Modify the things listing query to LEFT JOIN source_check_verdicts, add source_table filtering, fix the search bug, and fill record-lookup gaps — all in one PR.

Verdict JOIN: Can't reuse verdictJoinCondition() directly because it takes a fixed recordType string, but things rows are heterogeneous. Instead, build a raw SQL CASE expression mapping things.source_table to verdict record_type:

LEFT JOIN source_check_verdicts scv ON
  scv.record_type = CASE things.source_table
    WHEN 'grants' THEN 'grant'
    WHEN 'funding_programs' THEN 'funding-program'
    WHEN 'benchmark_results' THEN 'benchmark-result'
    ... (12 entries)
  END
  AND scv.record_id = things.source_id
  AND scv.field_name IS NULL

Listing row with verdict dot:

+---------+------------------------------+----------------+---------+---+
| Type    | Title                        | Parent         | Updated | V |
+---------+------------------------------+----------------+---------+---+
| Entity  | Anthropic                    | —              | Apr 2   | ● |  green = verified
| Grant   | AI Safety Research Grant     | Open Phil      | Mar 28  | ○ |  white = not checked
| Pers.   | Jane Doe — CTO at DeepMind   | DeepMind       | Mar 25  |   |  (no dot = table doesn't support verdicts)
+---------+------------------------------+----------------+---------+---+

Key Decisions

• LEFT JOIN vs bulk endpoint: LEFT JOIN reuses proven grants/personnel pattern, requires no new endpoint, one query per page. Bulk endpoint rejected (new API surface, two round-trips, client-side stitching).
• No coverage dots: Generic metadata coverage ("has description? has URL?") is not actionable for a heterogeneous index. Top-level entities would always score lower than child records because they lack parentThingId. Type-specific scorers on domain directory pages (orgs, grants, people) already provide meaningful coverage. Adding a coarser contradictory signal to /things is noise.
• Skip verdict on search results: Search uses 3 fallback stages including raw SQL trigram. Adding the JOIN to all 3 is fragile. Search is for quick lookup; quality auditing uses the browse listing.
• Custom JOIN builder instead of verdictJoinCondition(): The helper takes a fixed string; things needs a CASE expression. Inline the raw SQL in things.ts — single-use, not worth a shared abstraction.
• Search total = ftsTotal: Trigram supplements are best-effort additions, not separately paginated. The FTS total is the correct pagination total.

Architecture

File	Action	Purpose
apps/wiki-server/src/routes/tablebase/things.ts	modify	source_table filter, bySourceTable stats, search total fix, verdict LEFT JOIN, extend formatThing
apps/wiki-server/src/routes/tablebase/record-lookup.ts	modify	Add facts (composite key parser), resources, research_areas to whitelist
apps/web/src/app/things/types.ts	modify	Add bySourceTable to stats; add verdict fields to ThingRow
apps/web/src/app/things/page.tsx	modify	Pass source_table filter to API URL
apps/web/src/app/things/things-table.tsx	modify	Source_table FilterChips/dropdown; verdict SourceCheckDot column
apps/wiki-server/src/__tests__/things.test.ts	modify	Tests for new filter, stats, search fix, verdict JOIN

Implementation — Single PR (1 session)

Goal: Source_table filtering works, verdict dots visible per row, search pagination correct, record-lookup handles all tables.

Backend (things.ts):

• Add source_table: z.string().max(100).optional() to ListQuery schema
• Add eq(things.sourceTable, source_table) condition in listing handler
• Add bySourceTable GROUP BY query to stats endpoint
• Fix search total: line 275 → use ftsTotal instead of Math.max(ftsTotal, combined.length)
• Build SOURCE_TABLE_TO_RECORD_TYPE constant (12 entries, inline in things.ts)
• Change listing query from db.select().from(things) to db.select({ things, ...verdictSelectFields }).from(things).leftJoin(sourceCheckVerdicts, sql\...`)` with CASE expression
• Create ThingsJoinedRow interface (mirroring grants.ts JoinedRow pattern)
• Split formatThing into formatThingBase(t) for detail/children endpoints and formatThingWithVerdict(row) for listing endpoint

Backend (record-lookup.ts):

• Add resources and research_areas to VALID_SOURCE_TABLES + TABLE_MAP (straightforward — both have text PKs)
• Add facts with composite key parsing: split sourceId on :, query WHERE entity_id = ? AND fact_id = ? (following research_area_organizations pattern at line 106)
• Add entities to whitelist (already imported, one-line addition)

Frontend (things-table.tsx):

• Add source_table filter — compact <select> dropdown (not FilterChips — 15+ source tables is too many chips). Driven by stats.bySourceTable.
• Add verdict column: SourceCheckDot for rows where SOURCE_TABLE_TO_RECORD_TYPE has a mapping; empty for others
• Wire source_table to useDirectoryUrl filter

Frontend (types.ts):

• Add bySourceTable: Record<string, number> to ThingsStatsResponse
• Add verdict?: string | null, verificationConfidence?: number | null to ThingRow

Tests:

• source_table filter returns filtered results
• bySourceTable appears in stats response
• Search total is not inflated (specifically: FTS returns 100 total, trigram adds 5 → total should be 100, not 105)
• Verdict LEFT JOIN returns verdict field for matching records, null for non-matching
• Unmapped source_table produces null verdict (not error)
• Record-lookup for facts with composite sourceId returns correct record
• Record-lookup for resources and research_areas works

Quality gates: pnpm build passes, all tests pass, Playwright render-audit passes
Exit criteria: /things?source_table=grants shows filtered results with verdict dots; search pagination totals are accurate; /things/[id] record data works for facts/resources/research_areas things

Scope Cuts

• Coverage dots: Excluded. Generic "has description?" coverage is not actionable and contradicts type-specific scorers on domain pages. Verdict dots provide the useful quality signal.
• Verdict on search results: Excluded. Search has 3 raw SQL fallback stages; adding JOIN is fragile for marginal benefit.
• New bulk verdict endpoint: Unnecessary — LEFT JOIN eliminates the need.
• source-table-map.ts shared file: Unnecessary — single-use constant inlined in things.ts.
• Data integrity fixes (missing sync handlers, backfill migrations): Separate workstream. Political data tables are empty (0 records). Only matters when data is actually ingested.

Quality & Verification

Area	Plan
Tests	7 new tests in things.test.ts (source_table filter, stats bySourceTable, search total, verdict JOIN with/without match, unmapped source_table, record-lookup composite key)
Integration smoke test	After deploy: curl /api/things?source_table=grants&limit=5 returns verdict fields; curl /api/things/stats includes bySourceTable
UI verification	Playwright render-audit against prod. Manual: /things shows verdict dots + source_table dropdown
Migration verification	N/A — no schema migrations
Deploy steps	Standard deploy. No new env vars, no migrations, no manual steps.
Documentation	N/A — no new conventions
Monitoring	Existing error boundary + health endpoint. No new monitoring needed.

Risks & Mitigations

Risk	Severity	Mitigation
source_table→record_type naming mismatch (e.g., "grants" vs "grant")	High	Inline constant with exhaustive test covering all 12 entries. Unmapped tables produce NULL verdict (graceful degradation, not error).
CASE expression in JOIN prevents index use on verdicts table	Low	50-row pages keep overhead small. The idx_scv_pk unique index covers (record_type, record_id) prefix for nested-loop join. Monitor query plan post-deploy.
formatThing() type signature breaks after LEFT JOIN changes row shape	High	Split into formatThingBase() (for detail/children) and formatThingWithVerdict() (for listing), following the grants.ts JoinedRow pattern.
facts composite sourceId (entityId:factId) can't be used as WHERE id = ?	High	Special-case facts in record-lookup with composite key parser, identical to research_area_organizations pattern.
Multiple field-level verdicts inflate listing rows	High	CASE join includes AND field_name IS NULL (matching verdictJoinCondition pattern). Test confirms no row duplication.

Open Questions

1. Source_table filter UI: Dropdown <select> vs FilterChips? Dropdown is more compact for 15+ source tables. FilterChips with counts are better for discoverability. Recommend dropdown with counts in option labels.

Rejected Approaches

• Materialized view: Adds schema complexity, 5-min data staleness, REFRESH orchestration. Overkill for a 50-row-per-page listing.
• Denormalized columns + trigger: Dual source of truth for verdict status. Trigger coupling. Higher maintenance.
• Bulk verdict endpoint: New API surface, two round-trips, client-side stitching. LEFT JOIN achieves the same with less code.
• Coverage dots: Generic metadata coverage is noise, not signal. parentThingId presence penalizes top-level entities unfairly. Type-specific scorers on domain pages are the right tool for coverage.
• 3-phase plan: ~130 lines of code following established patterns doesn't need 3 PRs. One PR, one review.

Red Team Log

Technical critic found 2 blocking issues:

1. verdictJoinCondition() can't handle dynamic CASE mapping → resolved by using inline raw SQL CASE expression instead of the helper
2. formatThing() type breaks with LEFT JOIN row shape → resolved by splitting into base/verdict variants following grants.ts JoinedRow pattern

Technical critic found 4 significant issues:
3. facts sourceId is composite key, not numeric PK → resolved by adding composite key parser (research_area_organizations pattern)
4. Search "correct total" unspecified → resolved: use ftsTotal (trigram supplements not paginated)
5. entities table also missing from record-lookup → added to the plan
6. Test/verification gaps (5 specific items) → added to test list

Scope critic recommendations adopted:

• Dropped coverage dots (generic coverage is misleading noise)
• Collapsed 3 phases to 1 PR (~130 lines)
• Dropped source-table-map.ts as separate file (inline constant)
• Dropped Phase 3 test backfill as separate phase (tests written with code)

Tasks

• Things listing: source_table filter, verdict dots, search bug fix, record-lookup gaps #3983 — Things listing: source_table filter, verdict dots, search bug fix, record-lookup gaps

[OAGr]

Branch: claude/deploy-tasks-verify-cmd

Cross-link: sibling of #3945 (Things UI Universal Record Inspector — detail page) and #4005 (TableBase Coverage Dashboard & Enrichment Queue). #3998 is the listing-side counterpart to #3945's detail-side work. Consider treating these as siblings of one Things UI epic. — Discussion review 2026-04-08.