ID-70 {70.1} RESEARCH — OQ-R9 opaque-Json RPC migration (Tier 1 + Tier 2)

ID-70 {70.1} RESEARCH — opaque-Json RPC migration (Tier 1 + Tier 2)

Spec tier: TECH+PLAN-light. This is a signature-change refactor (RETURNS Json → RETURNS TABLE(typed columns)) with no user-facing behaviour change — RESEARCH is deliberately light. No PRODUCT.md is required; a TECH.md ({70.3}) should carry the per-RPC migration plan + cast-sweep and the integration-test gate for merge_entities.

Parent Task: ID-70 “OQ-R9 opaque-Json RPC migration — Tier 1 + Tier 2 (RETURNS Json → RETURNS TABLE)”. Depends [64] (done; {64.8} cutover + types-regen landed → schedulable).

Source investigation: specs/id-50-ast-dataflow-tool/investigations/R-WP12-opaque-json-rpcs.md (§5-RPC detail + recommendations). R-WP12’s line refs and migration-file refs are PRE-CUTOVER and STALE — this doc re-derives current ground truth.

Code-intel orientation

Audit tool: bun scripts/audit-opaque-json-rpcs.ts — re-runnable JSONL verifier that scans database.types.ts for Returns: Json and emits per-RPC line refs + ts_callers + convertibility verdict. Sandbox note: the script reads supabase/types/database.types.ts, which is sandbox-read-denied (EPERM) — it must be run with the sandbox disabled. All line refs below are from a sandbox-disabled run on 22/06/2026.
Caller map: repo-wide grep over *.ts/*.tsx for the 5 names (excluding the types file + audit script).
MCP-bearing check: grep -rn <5 names> lib/mcp mcp-apps → zero matches.

Refreshed ground truth (post-cutover, 22/06/2026)

All 5 RPCs STILL resolve to opaque `Json`

The {64.8} cutover + types-regen did not alter these return types. The audit verdict is convertible for all 5 (i.e. Returns: Json still present in database.types.ts). Migration is still needed and still correct.

Each RPC now appears TWICE in `database.types.ts` (id-115 data-API schema isolation)

R-WP12’s single line ref per RPC is obsolete. Post-cutover, each RPC is exposed in two schema blocks — public.Functions (~3834–4100) and the data-API schema block (~8652–9051). This is a consequence of scripts/generate-api-views.ts, which lists all 5 names in its api-view exposure set. Migration implication: regenerating types after the DDL change must refresh BOTH exposures, and any data-API view wrapper around these functions may need regeneration via generate-api-views.ts (TECH.md must confirm whether the api-schema exposure is a direct function grant or a view that re-types the result).

Line-ref drift table (R-WP12 stale → current)

RPC	R-WP12 (STALE)	Current `public` block	Current data-API block
`get_dashboard_attention_counts`	—	3834	8652
`get_filter_counts`	—	3871	8760
`get_user_tag_counts`	3625-era refs	4059	8992
`get_workspace_counts`	3625	(not in public block w/ caller)	8994
`merge_entities`	3678	4100	9051

R-WP12’s migration-file refs (20260416102457_pre_squash_reconciliation.sql) are also stale: all 5 RPCs are now defined in the single squashed baseline supabase/migrations/20260617130000_squash_baseline.sql. The migration for ID-70 is a new forward CREATE OR REPLACE FUNCTION migration (one per RPC, or grouped).

Per-RPC caller map (current)

Tier	RPC	TS callers (current)	Cast / parse site
1	`get_user_tag_counts`	`hooks/browse/use-filter-data.ts:125`	raw `data as Record<string, number>` (125)
1	`get_workspace_counts`	ZERO (audit + grep)	none
1	`merge_entities`	`app/api/entities/merge/route.ts:40`	inline 7-field `data as { … }` (route.ts:54-62)
2	`get_dashboard_attention_counts`	`lib/dashboard.ts:341`	inline nested `data as { … freshness_summary {…} }` (dashboard.ts:440-454)
2	`get_filter_counts`	`hooks/browse/use-filter-data.ts:62`, `hooks/browse/use-top-domains.ts:51`	Zod `parseJsonb(FilterCountsSchema, data)` (use-filter-data.ts:67, use-top-domains.ts:61)

Test-fixture references (no production cast): __tests__/hooks/use-filter-data.test.ts, __tests__/lib/unified-dashboard.test.ts, __tests__/api/entities-users.test.ts, __tests__/integration/cocoindex/admin-merge-coexistence.integration.test.ts.

MCP bearing: NONE

grep -rn over lib/mcp + mcp-apps for all 5 names → zero matches. Confirms zero id-71 (AI tooling) / id-104 (eval engine) bearing — none of the 5 RPCs back an MCP tool.

Per-RPC migration approach (`RETURNS Json` → `RETURNS TABLE(typed columns)`)

Tier 1 (do now — convertible, single/zero caller)

get_user_tag_counts — flat jsonb_object_agg(tag, cnt) → RETURNS TABLE(tag text, count bigint), LANGUAGE sql STABLE SECURITY INVOKER. Caller use-filter-data.ts:125 swaps data as Record<string, number> for iterating typed rows (data.map(r => ({ tag: r.tag, count: Number(r.count) }))). Simplest end-to-end; use as the pattern proof-of-concept.
get_workspace_counts — flat jsonb_object_agg(name, cnt) → RETURNS TABLE(workspace_name text, item_count bigint), LANGUAGE sql STABLE SECURITY INVOKER. Zero callers → see migrate/drop verdict below.
merge_entities — the only volatile/DML RPC (UPDATE + DELETE in one txn). → RETURNS TABLE(merged boolean, target text, entity_type text, mentions_updated integer, relationship_sources_updated integer, relationship_targets_updated integer, duplicates_removed integer). MUST keep LANGUAGE plpgsql SECURITY INVOKER — NOT STABLE (it mutates). After migration, data[0] gives the typed row; delete the 7-field inline cast at route.ts:54-62; map result.* → data[0].*. Called via service client.

Tier 2 (do next — multi-caller / nested object)

get_dashboard_attention_counts — returns a nested object (freshness_summary sub-object alongside 8 scalar counts). Two options for TECH.md to pick: (a) flat columns for the 8 scalars + keep freshness_summary as a single jsonb column; or (b) fully flatten (8 scalars + 4 freshness columns = 12 columns). Option (a) is lower-churn against the lib/dashboard.ts:440-454 consumer (which already reads counts.freshness_summary.fresh etc.). LANGUAGE plpgsql/sql STABLE SECURITY INVOKER (read-only). Caller lib/dashboard.ts:341/extraction at 435-472 swaps the inline cast for typed results[0].value.data[0].
get_filter_counts — hardest of the 5. Returns a 3-key object of dynamic-key maps ({ domain: {<domain>: n}, content_type: {…}, platform: {…} } — see FilterCountsSchema, lib/validation/jsonb.ts:113-119). Dynamic string keys do not map to fixed typed columns. Options for TECH.md/Liam: (a) leave RETURNS jsonb but tighten the Zod parseJsonb boundary (lowest risk — the Zod parse already provides type safety, so the “opaque” cast is already mitigated here); or (b) convert to long-form RETURNS TABLE(facet text, key text, count bigint) and re-pivot client-side in both callers (use-filter-data.ts:62, use-top-domains.ts:51). Recommendation: option (a) — get_filter_counts is the weakest migration candidate because the value is inherently a dynamic-key map, and the Zod boundary already removes the unsafe-cast risk. Surface to Lian as an open question (below).

Common per-RPC steps (all 5)

New forward migration CREATE OR REPLACE FUNCTION … RETURNS TABLE(…) with correct LANGUAGE/volatility/SECURITY INVOKER/SET search_path = public, extensions.
Confirm/refresh the data-API exposure (scripts/generate-api-views.ts) so the api-schema block re-types too.
Regenerate types (supabase gen types typescript per supabase/CLAUDE.md) → both database.types.ts blocks update.
Cast-removal sweep at the caller(s).
bun run test (never bun test) full regression; behaviour-first.

`get_workspace_counts` migrate-vs-drop verdict

Verdict: MIGRATE (do not drop) — low-confidence, flag for Liam ratification.

Confirmed zero TS callers post-cutover (audit ts_callers: []; repo-wide grep finds only the audit script’s own CREATE FUNCTION string literal at line 334, plus the data-API exposure list — no .rpc('get_workspace_counts') anywhere).
Rationale to migrate, not drop: (a) zero callers = zero sweep cost and zero breaking risk either way; (b) migrating alongside get_user_tag_counts (near-identical jsonb_object_agg → TABLE shape) is cheap and establishes the correct typed pattern for the inevitable future caller; (c) dropping is a separate decision that needs confirmation the function isn’t referenced by any non-TS surface (SQL view, dashboard, external job) — out of scope for a signature-refactor task.
Counter-argument (drop): if Liam confirms the function is genuinely dead and not planned for reuse, a DROP FUNCTION is less schema noise than a migrated-but-unused RPC. This is the one genuine fork → open question for Liam.

Regen-types + cast-removal sweep step

After all migrations land:

Regen: supabase gen types typescript (per supabase/CLAUDE.md) — refreshes both the public and data-API blocks of database.types.ts. Verify each migrated RPC now shows Returns: { … }[] (array of typed rows) not Returns: Json.
Cast-removal sweep (exact sites):
- app/api/entities/merge/route.ts:54-62 — delete the 7-field data as { … } inline cast; read data[0]. The route is also an ID-50 {50.12} z.unknown() placeholder — its ResponseSchema becomes derivable from the typed columns (soft-ordering note below).
- hooks/browse/use-filter-data.ts:125 — delete data as Record<string, number> (get_user_tag_counts); iterate typed rows.
- lib/dashboard.ts:440-454 — delete the nested inline data as { … } (get_dashboard_attention_counts); read data[0] (option-(a) shape keeps freshness_summary as a jsonb column).
- hooks/browse/use-filter-data.ts:62 + hooks/browse/use-top-domains.ts:51 (get_filter_counts) — only if Tier-2 option (b) is chosen; option (a) leaves these untouched (Zod boundary retained).
Re-run bun scripts/audit-opaque-json-rpcs.ts (sandbox-disabled) → the migrated RPCs should drop out of the opaque-Json inventory.

`merge_entities` DML caveat + integration-test recommendation

merge_entities is the only volatile RPC in scope: it performs UPDATE + DELETE across entity-mention / relationship-source / relationship-target / duplicate rows in a single transaction.

Migration MUST keep LANGUAGE plpgsql SECURITY INVOKER — NOT STABLE/IMMUTABLE. Marking a DML function STABLE is a correctness bug (the planner may skip/reorder).
Integration test recommended after migration (extends the existing __tests__/integration/cocoindex/admin-merge-coexistence.integration.test.ts and __tests__/api/entities-users.test.ts): confirm row counts (mentions_updated, relationship_sources_updated, relationship_targets_updated, duplicates_removed) propagate correctly through the typed data[0] result, and the UPDATE+DELETE still run atomically. This is a behaviour-change-with-tests Subtask → test-philosophy.md behaviour-first discipline applies.

ID-50 soft-ordering note (NOT a blocker)

merge_entities backs app/api/entities/merge/route.ts, which is one of the ID-50 {50.12} z.unknown() placeholder routes (currently hand-casts the opaque-Json result at route.ts:54-62). After ID-70 migrates merge_entities, that cast is deletable and the route’s ResponseSchema becomes derivable from typed columns. Soft ordering preference only — ID-70 and ID-50 are NOT mutually gating. If ID-70 lands first, ID-50 {50.12} inherits a cleaner route; if ID-50 lands first, it keeps the z.unknown() placeholder until ID-70 retires the cast. No cross-Task dependency to encode.

Open questions for Liam (ratification)

get_workspace_counts: migrate or drop? Default verdict is MIGRATE (cheap, zero risk, establishes pattern), but if it is genuinely dead with no reuse planned, DROP is less schema noise. Needs confirmation it isn’t referenced by any non-TS surface.
get_filter_counts: convert or leave-as-Zod-boundary? Its dynamic-key-map shape resists a clean RETURNS TABLE. Recommendation is to LEAVE RETURNS jsonb (the Zod parseJsonb boundary already removes the unsafe-cast risk) rather than force a long-form pivot. Confirm whether to include it in the migration scope at all, or scope ID-70 to the 4 cleanly-convertible RPCs + the Zod-boundary tightening for filter-counts.
Data-API exposure: confirm whether the api-schema exposure of these RPCs (id-115 / generate-api-views.ts) is a direct function grant (auto-retypes on regen) or a view wrapper that needs explicit regeneration. TECH.md ({70.3}) to resolve.