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

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

Spec tier: TECH+PLAN-light. Signature-change refactor (RETURNS Json → RETURNS TABLE(typed columns)) plus two dead-function drops. No PRODUCT.md exists and none is required — this is signature-only with no user-facing behaviour change, so there are no PRODUCT invariants to map against. This TECH.md carries its own acceptance criteria per RPC (the “Acceptance” line in each subsection).

Parent Task: ID-70 “OQ-R9 opaque-Json RPC migration — Tier 1 + Tier 2”. Depends [64] (done; {64.8} cutover + types-regen landed). Sibling: RESEARCH.md ({70.1}) in this dir — read for inventory background, but RESEARCH’s open forks are RATIFIED and several of its line refs / claims are stale or wrong; this TECH.md is the corrected ground truth.

Ratified disposition (this session, Liam-approved): 3 RPCs MIGRATE (get_user_tag_counts, merge_entities, get_dashboard_attention_counts), 2 RPCs DROP (get_workspace_counts, get_workspace_item_counts), 1 RPC KEEPS RETURNS jsonb with a tightened Zod boundary (get_filter_counts). All forks from RESEARCH §“Open questions” are closed below — do not re-open.

---

Context

Code-intelligence orientation (completed 23/06/2026)

• gitnexus_query({query: "dashboard attention counts filter counts RPC opaque json typed columns"}) (repo /Users/liamj/Documents/development/canonical) surfaced the two live consumer flows verbatim: Function:lib/dashboard.ts:fetchUnifiedDashboardData (startLine: 241, endLine: 656, the dashboard consumer) and Function:hooks/browse/use-filter-data.ts:useFilterData (startLine: 52, endLine: 187) with its inner queryFn (startLine: 60, endLine: 72). No lib/mcp/* symbol appeared in the result set for any of the 6 RPCs — confirms RESEARCH’s “MCP bearing: NONE” (zero id-71 / id-104 bearing).
• gitnexus_context({name: "merge_entities"}) returned Symbol 'merge_entities' not found — expected: GitNexus indexes the TypeScript corpus, and merge_entities is a SQL function, not a TS symbol. The TS-side call site is resolved via grep/Read instead (see the cast-sweep table). The gitnexus repo arg required the absolute path /Users/liamj/Documents/development/canonical because the short name canonical is registered against four checkouts.
• Caller verification: repo-wide grep over *.ts/*.tsx, scripts/**/*.py, and supabase/migrations/*.sql (ast-dataflow does not cover Python/SQL) — see per-RPC caller counts below. The two DROP targets returned zero TS/TSX/Python callers and the only SQL match is the squash baseline’s own CREATE (the definition itself).

Current state — the 6 functions

All current SQL definitions live in the single squashed baseline supabase/migrations/20260617130000_squash_baseline.sql (R-WP12’s 20260416102457_* ref is stale). Verified line refs (Read, 23/06/2026):

Function	public def (baseline)	Current signature	LANGUAGE / volatility
get_user_tag_counts	3725–3735	RETURNS jsonb (flat jsonb_object_agg(tag,cnt))	sql STABLE
get_workspace_counts	3762–3778	RETURNS jsonb (flat jsonb_object_agg(name,cnt))	sql STABLE
get_workspace_item_counts	3781–3793	RETURNS TABLE(workspace_id uuid, item_count bigint, last_activity timestamptz) (already typed — NOT an opaque-Json target)	sql STABLE
merge_entities	4014–4088	RETURNS jsonb (DML: UPDATE×3 + DELETE)	plpgsql (volatile)
get_dashboard_attention_counts	2375–2472	RETURNS json (8 scalars + nested freshness_summary)	plpgsql STABLE
get_filter_counts	2820–2870	RETURNS jsonb (3-key dynamic-key maps)	plpgsql

All functions carry SET "search_path" TO 'public', 'extensions' and are owned by postgres; grants are REVOKE ... FROM PUBLIC then GRANT ... TO authenticated, service_role (baseline 12512–12777). The migration MUST preserve search_path on every CREATE OR REPLACE and re-issue the existing grant pattern (Postgres re-creates a function with default PUBLIC-EXECUTE, so the REVOKE FROM PUBLIC must be re-stated for each replaced function — see Risks).

Data-API exposure mechanism (id-115) — RESOLVED

scripts/generate-api-views.ts (header lines 1–42, SURFACE_RPCS 142–206) emits each api RPC as a thin LANGUAGE sql SECURITY INVOKER wrapper (SELECT public.<fn>(…), SET search_path = public, extensions) into the idempotent migration supabase/migrations/20260616120100_id115_api_views_and_rpcs.sql. The header states verbatim: “DROP/CREATE by identity-args so a future return-type change (ID-70: json -> TABLE) regenerates cleanly.” The generator introspects each name’s return type from the live Postgres catalog post-db reset, so re-running it after the public DDL change re-types the api wrapper automatically.

Which RPCs have an api wrapper (in SURFACE_RPCS):

• get_dashboard_attention_counts (line 165) — wrapped → regenerate.
• get_filter_counts (line 170) — wrapped, but stays jsonb (no DDL change → wrapper unchanged on regen; re-running is a no-op for it).
• get_user_tag_counts (line 192) — wrapped → regenerate.
• merge_entities (line 195) — wrapped → regenerate.
• get_workspace_counts — NOT in SURFACE_RPCS → no api wrapper exists → DROP needs no api-view change. (This corrects RESEARCH §“appears twice in database.types.ts” — it appears only in the public block, not the api block.)
• get_workspace_item_counts — NOT in SURFACE_RPCS → no api wrapper → DROP needs no api-view change.

So exactly the 3 migrated RPCs need their api wrappers regenerated (and get_filter_counts’s wrapper regenerates to a byte-identical no-op). The DROP targets touch only the public schema.

The opaque-Json audit verifier

bun scripts/audit-opaque-json-rpcs.ts reads supabase/types/database.types.ts (lines 26–60) and keys on the literal Returns: Json. It is the ratify-gate verifier. Sandbox note: supabase/types/database.types.ts is sandbox-read-denied (EPERM), so this script MUST run with the sandbox disabled. After the migration + regen, the 3 migrated RPCs lose Returns: Json (become Returns: { … }[]) and drop out of the inventory; the 2 dropped functions vanish from the types file entirely.

---

Proposed changes

One grouped forward migration

Create ONE migration via the CLI (supabase migration new id70_opaque_json_rpc_typed_returns). Per supabase/CLAUDE.md: DDL via CLI only (never MCP execute_sql/apply_migration); cat supabase/.temp/project-ref and confirm/relink before push; supabase db push runs foreground (it prompts interactively and hangs in a background shell). Migration contents, in order:

1. DROP FUNCTION IF EXISTS public.get_workspace_counts();
2. DROP FUNCTION IF EXISTS public.get_workspace_item_counts();
3. CREATE OR REPLACE FUNCTION public.get_user_tag_counts() RETURNS TABLE(...) (Tier 1).
4. CREATE OR REPLACE FUNCTION public.merge_entities(...) RETURNS TABLE(...) (DML).
5. CREATE OR REPLACE FUNCTION public.get_dashboard_attention_counts(...) RETURNS TABLE(...) (Tier 2).
6. For each of the 3 replaced functions: REVOKE EXECUTE ON FUNCTION public.<fn>(<args>) FROM PUBLIC; GRANT EXECUTE ON FUNCTION public.<fn>(<args>) TO authenticated, service_role; (re-state the baseline grant pattern — CREATE OR REPLACE does not preserve the prior REVOKE FROM PUBLIC).

Note: a CREATE OR REPLACE that changes the OUT-column set of a RETURNS TABLE (or changes RETURNS json → RETURNS TABLE) is rejected by Postgres with “cannot change return type of existing function”. Each migrated function therefore needs a DROP FUNCTION IF EXISTS public.<fn>(<exact-arg-types>); immediately before its CREATE (drop-then-create, not bare replace). Drop the api wrapper first only if the regen step does not already DROP/CREATE it — but the generator does DROP/CREATE by identity, so the wrapper is handled by the regen step (below), not this migration.

RPC 1 — get_user_tag_counts (Tier 1, MIGRATE — pattern proof, do first)

• Target: RETURNS TABLE(tag text, count bigint), LANGUAGE sql STABLE SECURITY INVOKER, SET search_path = public, extensions.
• Body: the existing SELECT tag, COUNT(*) AS count FROM content_items ci, unnest(ci.user_tags) AS tag WHERE user_tags IS NOT NULL AND user_tags <> '{}' GROUP BY tag ORDER BY count DESC (drop the outer jsonb_object_agg).
• Consumer cast removal: hooks/browse/use-filter-data.ts:122–134 (the userTagsQuery queryFn). Delete const tagCounts = data as Record<string, number> (line 127) and the Object.entries pivot; replace with typed-row iteration: return (data ?? []).map((r) => ({ tag: r.tag, count: Number(r.count) })).sort((a, b) => b.count - a.count); (count arrives as bigint→string-or-number from PostgREST, so keep the Number() coercion).
• Acceptance: database.types.ts shows get_user_tag_counts → Returns: { tag: string; count: number }[]; the audit script no longer lists it; the User-tags filter facet still renders identical tag/count pairs in browse (regression covered by __tests__/hooks/use-filter-data.test.ts).

RPC 2 — merge_entities (Tier 2, MIGRATE — the only DML RPC)

• Target: RETURNS TABLE(merged boolean, target text, entity_type text, mentions_updated integer, relationship_sources_updated integer, relationship_targets_updated integer, duplicates_removed integer). Column set/types verified against the current jsonb_build_object at baseline 4072–4080 — exact 1:1 (the 4 count vars are declared integer; merged is a boolean literal; target/ entity_type are text params).
• Volatility: MUST stay LANGUAGE plpgsql SECURITY INVOKER — NOT STABLE/ IMMUTABLE. It performs UPDATE entity_mentions ×1, UPDATE entity_relationships ×2, DELETE FROM entity_mentions ×1 in one transaction; marking it STABLE is a correctness bug (the planner may skip/reorder side-effecting calls).
• Body change: keep the entire body verbatim; replace the trailing RETURN jsonb_build_object(...) with RETURN QUERY SELECT true AS merged, p_target_name AS target, p_entity_type AS entity_type, v_mentions_updated AS mentions_updated, v_rel_sources_updated AS relationship_sources_updated, v_rel_targets_updated AS relationship_targets_updated, v_duplicates_removed AS duplicates_removed; Keep the input-validation RAISE EXCEPTION guards (they short-circuit before any RETURN QUERY, which is the correct error path the route’s catch already handles).
• Consumer cast removal: app/api/entities/merge/route.ts. The .rpc('merge_entities', …) call is at line 53 (RESEARCH’s :40 is stale). Delete the 7-field inline const result = data as { … } cast at lines 67–75 (RESEARCH’s :54-62 is stale). Replace with const result = data?.[0]; and a null-guard (if (!result) return NextResponse.json({ error: 'Merge returned no result' }, { status: 500 });), then read result.merged / result.target / etc. unchanged.
• EntityMergeResponseSchema note: the route ALREADY declares a real Zod EntityMergeResponseSchema (route.ts:17–23) with 5 fields — it is NOT an ID-50 {50.12} z.unknown() placeholder. (This corrects the brief’s soft-ordering note and RESEARCH §“ID-50 soft-ordering note”: there is no z.unknown() to retire here.) Leave the response schema as-is; the post-migration data[0] already satisfies it. No ID-50 cross-Task ordering applies.
• Acceptance: see “Testing and validation” — the integration-test gate. Plus: the route still returns 200 with the same JSON body shape; database.types.ts shows merge_entities → Returns: { merged: boolean; … }[]; audit script no longer lists it.

RPC 3 — get_dashboard_attention_counts (Tier 2, MIGRATE — option (a) refined)

• Target: RETURNS TABLE(governance_review_count integer, unverified_count integer, quality_flag_count integer, stale_content_count integer, expired_content_count integer, expiring_content_date_count integer, unread_notification_count integer, coverage_gap_count integer, freshness_summary jsonb), LANGUAGE plpgsql STABLE SECURITY INVOKER, SET search_path = public, extensions. integer is correct — the current json_build_object emits the 8 integer DECLAREd scalars (baseline 2381–2390) and the consumer reads them as number (no Number() coercion in lib/dashboard.ts), so integer avoids the bigint→string churn that count-style columns would introduce.
• Body change: keep the entire BEGIN … END computation block verbatim. Replace the final SELECT json_build_object(...) INTO result; RETURN result; with RETURN QUERY SELECT v_governance_review_count, v_unverified_count, v_quality_flag_count, v_stale_count, v_expired_count, v_expiring_content_date_count, v_unread_notification_count, v_coverage_gap_count, jsonb_build_object('fresh', v_fresh_count, 'aging', v_aging_count, 'stale', v_stale_count, 'expired', v_expired_count) AS freshness_summary; (keep the freshness sub-object expression verbatim — note it becomes jsonb_build_object, not json_build_object, so the column types jsonb). Drop the result json DECLARE.
• Consumer cast removal: lib/dashboard.ts. The .rpc('get_dashboard_attention_counts', …) call is at line 315 (inside the Promise.allSettled array). The result is unwrapped at lines 409–447: results[0] is the settled wrapper, results[0].value is { data, error }. After migration data is an array, so:
  • line 414 const counts = data as { … }; → const counts = data?.[0]; (and guard: else if (data && data[0])).
  • The 8 scalar reads (lines 430–437) stay byte-identical (counts.governance_review_count ?? 0, etc.).
  • The freshness_summary block (lines 438–443) currently reads counts.freshness_summary. fresh off an inline-cast object. Replace with a Zod parse at the jsonb boundary: const fs = parseJsonb(FreshnessSummarySchema, counts.freshness_summary); then if (fs) { freshness_summary.fresh = fs.fresh; … } (the four fields). This removes the last unsafe cast on this path and matches the parseJsonb pattern already used for get_filter_counts. Import parseJsonb + FreshnessSummarySchema from @/lib/validation/jsonb at the top of lib/dashboard.ts.
• Acceptance: database.types.ts shows the 9-column Returns: { … }[]; audit script no longer lists it; the dashboard “attention” counts (governance/unverified/quality/ freshness) render identically (regression covered by __tests__/lib/unified-dashboard.test.ts).

RPC 4 — get_workspace_counts (DROP, NOT migrate)

• Change: DROP FUNCTION IF EXISTS public.get_workspace_counts();
• Justification: zero callers verified across TS/TSX (grep excl. types + audit script), Python (scripts/**/*.py), and SQL (only the baseline def matches). Not in SURFACE_RPCS → no api wrapper to drop. Live workspace counting is a different metric served by getWorkspaceTypeCounts() in app/workspaces/page.tsx:9 (an inline query, not this RPC). No cast sweep, no api-view change, no consumer impact.
• Acceptance: function absent from database.types.ts and from the audit inventory; no test/typecheck/grep references remain.

RPC 5 — get_workspace_item_counts (DROP, NOT migrate)

• Change: DROP FUNCTION IF EXISTS public.get_workspace_item_counts();
• Justification: already correctly typed (RETURNS TABLE(workspace_id uuid, item_count bigint, last_activity timestamptz), baseline 3781) so it was never an opaque-Json target — folded into ID-70 only as the second dead function. Zero callers verified across TS/TSX/Python/SQL; not in SURFACE_RPCS → no api wrapper.
• Acceptance: function absent from database.types.ts; no references remain.

RPC 6 — get_filter_counts (KEEP RETURNS jsonb, tighten Zod — NO DDL)

• No DDL migration. Its return is genuinely dynamic-key ({ domain: {<slug>: n}, content_type: {…}, platform: {…} }, baseline 2825–2865) — a clean RETURNS TABLE is infeasible without a client-side re-pivot in both callers, and the existing parseJsonb(FilterCountsSchema, …) boundary already neutralises the opaque-cast risk. Genuinely live: 2 callers — hooks/browse/use-filter-data.ts:62 (parse at 67) and hooks/browse/use-top-domains.ts:51 (parse at 61).
• Only change — tighten the Zod schema in lib/validation/jsonb.ts:113–119. Current FilterCountsSchema uses z.record(z.string(), z.number()).optional().default({}) per facet with .passthrough(). Tighten to:

  export const FilterCountsSchema = z
    .object({
      domain: z.record(z.string(), z.number().int().nonnegative()).default({}),
      content_type: z.record(z.string(), z.number().int().nonnegative()).default({}),
      platform: z.record(z.string(), z.number().int().nonnegative()).default({}),
    })
    .strict();

  Keep z.record(z.string(), …) keys open — primary_domain is free-form taxonomy. Tighten values to .int().nonnegative() (counts are non-negative integers) and swap .passthrough() → .strict() (the RPC emits exactly these 3 facet keys; .strict() catches any drift). Verify: both callers read parsed?.domain / .content_type / .platform only — .strict() rejecting an unexpected 4th key would surface as a parseJsonb warn + null (falls back to EMPTY_COUNTS / []), which is the existing fail-safe, so no behaviour regression.
• Acceptance: get_filter_counts remains Returns: Json in database.types.ts (it is the one intentional remaining opaque-Json RPC — note this in the audit-script expectation: 1 expected residual, not 0); both browse filter facets + top-domains chips render identically; FilterCountsSchema unit coverage in __tests__/hooks/use-filter-data.test.ts still passes.

New Zod schema — FreshnessSummarySchema

Add to lib/validation/jsonb.ts next to FilterCountsSchema (~line 119). Freshness bands verified from the SQL (freshness = 'fresh' | 'aging' | 'stale' | 'expired', baseline 2416–2419 and the freshness_summary object 2459–2464):

export const FreshnessSummarySchema = z.object({
  fresh: z.number().int().nonnegative(),
  aging: z.number().int().nonnegative(),
  stale: z.number().int().nonnegative(),
  expired: z.number().int().nonnegative(),
});

Regen + verify sequence (after db push succeeds)

1. Regenerate the api wrappers: bun scripts/generate-api-views.ts (requires the local stack post-db reset; introspects the catalog and re-types the 3 migrated wrappers). Run bun scripts/generate-api-views.ts --check in CI/locally to confirm the committed api-views migration matches.
2. Regenerate types (both schemas, per supabase/CLAUDE.md ID-115): /opt/homebrew/bin/supabase gen types typescript --project-id <platform-project-ref> --schema public,api > supabase/types/database.types.ts (deterministic public then api order; never hand-edit).
3. Verify the inventory: bun scripts/audit-opaque-json-rpcs.ts with the sandbox disabled (the script reads the EPERM-denied database.types.ts). Expect: the 3 migrated RPCs absent, the 2 dropped RPCs absent, get_filter_counts still present (the one intentional residual).
4. bun run test (full regression — never bun test).

Cast-removal sweep (1:1, exact file:line — verified 23/06/2026)

Migrated RPC	File:line (call)	Cast to remove	Replacement
get_user_tag_counts	hooks/browse/use-filter-data.ts:125 (queryFn 122–134)	data as Record<string, number> (line 127)	iterate typed rows: (data ?? []).map(r => ({ tag: r.tag, count: Number(r.count) }))
merge_entities	app/api/entities/merge/route.ts:53 (call)	7-field data as { … } (lines 67–75)	const result = data?.[0] + null-guard; reads unchanged
get_dashboard_attention_counts	lib/dashboard.ts:315 (call)	nested data as { … } (line 414) + inline freshness_summary reads (438–443)	data?.[0] + parseJsonb(FreshnessSummarySchema, counts.freshness_summary)
get_filter_counts	use-filter-data.ts:62, use-top-domains.ts:51	none (Zod boundary retained)	unchanged — only FilterCountsSchema tightens

No Python or SQL consumer sweep needed (zero non-TS callers for any of the 6). No lib/mcp / mcp-apps sweep (zero MCP bearing).

---

Testing and validation

Acceptance criteria are per-RPC above. Test work, behaviour-first per ${KH_PRIVATE_DOCS_DIR}/src/content/docs/reference/test-philosophy.md (behaviour-not-implementation; shared createMockSupabaseClient() from __tests__/helpers/mock-supabase.ts — never hand-roll Supabase mocks):

1. merge_entities integration-test gate (REQUIRED — behaviour-change-with-tests). The only DML migration; the typed-row return + UPDATE/DELETE atomicity must be proven against a live DB.

   • Existing test correction: __tests__/integration/cocoindex/admin-merge-coexistence.integration.test.ts does not actually call merge_entities — it simulates the merge net-effect with a direct UPDATE entity_mentions (lines 114–117). It therefore does NOT exercise the RPC’s return shape and does not regress on the signature change. Leave it as-is (its Inv-9 op_id-scoping assertion is unrelated to the return type), but do NOT rely on it as the ID-70 gate.
   • Add a dedicated integration test (new __tests__/integration/cocoindex/merge-entities-typed-return.integration.test.ts, or a new describe in the existing file, env-gated on hasRealLiveDbCredentials()): seed a small entity-mention fixture with known duplicates + relationships, call serviceClient.rpc('merge_entities', { … }), and assert the typed data[0] row carries the correct counts (mentions_updated, relationship_sources_updated, relationship_targets_updated, duplicates_removed) AND that the post-call DB state reflects the UPDATEs (canonical_name rewritten) and the DELETE (duplicate rows gone) — i.e. the typed counts agree with the actual row deltas, proving the UPDATE+DELETE stayed atomic. Skip-clean where DB creds are unwired.
   • Update the unit test __tests__/api/entities-users.test.ts (POST /api/entities/merge, lines 343–446): the mock currently returns data: { merged: true, … } (single object, lines 402–408). After migration the RPC returns an array, so change the mock to data: [{ merged: true, … }] and confirm the route reads data[0]. The existing assertions (body.merged, body.mentions_updated, body.duplicates_removed) stay — they assert the user-observable response body, which is unchanged.

2. Regression (signature-change RPCs). No new behaviour — existing suites prove no regression: __tests__/hooks/use-filter-data.test.ts (user-tags facet + filter-counts), __tests__/lib/unified-dashboard.test.ts (attention counts + freshness). If these mock the RPC return, update mocks to the new array shape (tag-counts) / 9-column-row shape (dashboard) so they exercise the new consumer code paths.

3. Schema/type verification. bun run build (typecheck catches any missed cast site), plus the audit-script check in the regen sequence (step 3) as the canonical “did the opaque-Json inventory shrink correctly” gate.

4. Manual smoke (Liam’s eyes-on gate): browse filter panel (user-tags + domain/content- type/platform facets render), browse cold-start domain chips, dashboard attention card (counts + freshness breakdown), and an admin entity-merge from the entities UI returning the expected counts.

---

Risks and mitigations

• CREATE OR REPLACE return-type-change rejection. Postgres refuses to change a function’s return type via CREATE OR REPLACE. Mitigation: DROP FUNCTION IF EXISTS public.<fn>(<exact-arg-types>); immediately before each migrated function’s CREATE (the 3 migrated RPCs). The 2 dead functions are plain DROPs.
• PUBLIC-EXECUTE re-grant. A re-created function gets default EXECUTE TO PUBLIC. Mitigation: re-issue REVOKE EXECUTE … FROM PUBLIC; GRANT EXECUTE … TO authenticated, service_role; for each of the 3 migrated functions in the same migration (mirror the baseline grant block 12512–12777). anon must not gain execute.
• DML function marked STABLE. merge_entities MUST remain plpgsql volatile. Guard: the integration test asserts the actual row deltas, which would surface a planner-elided side effect. Reviewer checklist item.
• api-view drift. Forgetting generate-api-views.ts re-run leaves the api wrapper typed against the old json return. Mitigation: the regen sequence runs it explicitly; the --check mode + the task-view-vendor-drift/schema-parity side workflows catch drift in CI. The get_filter_counts wrapper regenerates to a no-op (unchanged), which is expected.
• Wrong project-ref on push. The main repo’s Supabase link can drift to prod. Mitigation: cat supabase/.temp/project-ref and relink before db push (per supabase/CLAUDE.md); a worktree executor’s first DB action MUST be supabase link --project-ref <platform-project-ref> (worktrees inherit no link state).
• Sandbox EPERM on the audit script. audit-opaque-json-rpcs.ts reads the read-denied database.types.ts; running it inside the sandbox fails with EPERM, not a real result. Mitigation: run it sandbox-disabled (documented in the regen sequence).
• bigint → string coercion. PostgREST serialises bigint columns as strings. get_user_tag_counts.count is bigint, so keep Number(r.count) in the consumer. The dashboard scalars are integer (serialise as JSON numbers), so no coercion churn there — this is the reason integer (not bigint) was chosen for the dashboard columns.

---

Follow-ups

• After ID-70 lands, app/api/entities/merge/route.ts no longer hand-casts the RPC result. Its EntityMergeResponseSchema (already a real Zod object, 5 of the 7 columns) could optionally be widened to expose relationship_sources_updated / relationship_targets_updated now that they are typed — out of scope for ID-70 (no consumer requests them), noted for a future tidy.

---

Decomposition recommendation ({70.4} PLAN)

Recommendation: NO {70.4} PLAN.md / decomposition. Flat-dispatch as a small ordered set of implementation Subtasks {70.5+} populated by the Orchestrator directly from this TECH.md. Rationale against the {N.4} triggers:

• Single migration (one grouped forward migration — 3× drop-then-create + 2× drop), not multiple migrations.
• One adapter kind (RPC signature + its TS consumer), repeated 3×.
• Largely independent slices — the per-RPC consumer edits touch disjoint files (use-filter-data.ts, merge/route.ts, lib/dashboard.ts); the only shared file is lib/validation/jsonb.ts (two additive edits: new FreshnessSummarySchema, tightened FilterCountsSchema).
• Effort ≈ 2h, at/under the >2h decomposition threshold.
• One chain dependency only: the DDL migration + regen must land before the consumer cast-removals can typecheck (the new array return type drives the data[0] edits).

This is a chain of ≤6 small Subtasks, not a compound-invariant decomposition. Proposed flat Subtask cut (sibling-only deps; the Orchestrator may collapse 5.5/5.6 into 5.4):

Subtask	Title	Depends	testStrategy (acceptance)
{70.5}	DDL migration (3× typed RETURNS TABLE + 2× DROP) + re-grants	—	supabase db push succeeds foreground; psql \df shows the 3 new signatures + the 2 functions gone; grants exclude anon
{70.6}	Regen api-views + types; verify audit inventory	{70.5}	generate-api-views.ts --check clean; types regen’d public,api; sandbox-disabled audit shows 3 migrated absent, 2 dropped absent, filter_counts residual
{70.7}	Consumer sweep: use-filter-data.ts (tag-counts) + dashboard.ts (attention) + new FreshnessSummarySchema	{70.6}	bun run build typechecks; use-filter-data.test.ts + unified-dashboard.test.ts green with array-shape mocks; facet/counts render identically
{70.8}	Consumer sweep: merge/route.ts (data[0]) + merge_entities integration test + unit-test mock update	{70.6}	new live integration test asserts typed counts == actual row deltas (atomic UPDATE+DELETE); entities-users.test.ts merge case green with array mock
{70.9}	Tighten FilterCountsSchema (.strict() + .int().nonnegative())	{70.6}	use-filter-data.test.ts filter-counts case green; browse facets + top-domains chips render identically

({70.7}/{70.8}/{70.9} are mutually independent and can run as a parallel wave after {70.6}; {70.5}→{70.6} is the only hard chain.) Each Subtask brief MUST embed the tool-discipline blocks (impact-before-edit via gitnexus_impact, gitnexus_detect_changes before commit — Inv 3) and the supabase/CLAUDE.md push discipline for {70.5}.