R-WP12 — Opaque-Json RPC inventory (Wave 5 / kh-ast-S9)

Status: DRAFT-S9 (kh-ast-S9 Wave 1 — R-WP19) Source: R-WP12 spec triple §WP-C (type-safety-pipeline/TECH.md lines ~245-283) Companion: R-WP12-type-safety-pipeline.md §Gap 2

Summary

Of the 14 opaque-Json RPCs in supabase/types/database.types.ts, 5 are convertible to typed RETURNS TABLE(...) signatures with mechanical migrations, 6 require design work before conversion (nested arrays or dynamic-keyed objects that do not map cleanly to a flat TABLE), 2 have no TS callers (candidates for deletion), and 1 must remain as-is (Supabase Auth Hook — protocol constraint).

Total active TS call sites across all 14: 12 (spread across 8 source files).

Recommended migration sprint shape: A focused S10 sprint targeting the 5 convertible functions would eliminate roughly half the cast surface in one pass (~6–8h total). The 6 requires-design functions need per-function design decisions from Liam before any migration is scheduled. The 2 dead functions (get_bid_summary, get_verification_stats) should be deleted to reduce schema noise, costing ~0.5h each.

Method

Step 1 — Enumerate

All 14 functions identified via:

1
grep -c 'Returns: Json' supabase/types/database.types.ts  # → 14
2
grep -n  'Returns: Json' supabase/types/database.types.ts

The grep -c 'Returns: Json$' form (with $ anchor) returned only 4 due to trailing whitespace on some lines — use the unanchored form.

Step 2 — TS callers

Callers found by scanning the full corpus (app/, lib/, hooks/, components/) for .rpc('<function_name>' patterns using the audit script (scripts/audit-opaque-json-rpcs.ts). This is equivalent to bun run ast-dataflow string-literal-uses '<function_name>' --scope app/ lib/ and is re-runnable at any time.

Step 3 — PL/pgSQL function bodies

Function bodies read from the live database via pg_proc.prosrc queries (MCP execute_sql with read-only SELECT — no DDL executed). Migration files crosschecked for historical definitions.

Step 4 — Convertibility verdict

Four tiers per function body:

Verdict	Definition
`convertible`	Fixed scalar output fields; mechanical migration to `RETURNS TABLE(...)`
`requires-design`	Contains nested `json_agg` arrays or dynamic-keyed objects
`no-ts-callers`	Zero TS callers; candidate for deletion
`leave-as-is`	JSONB contract is protocol-mandated or genuinely polymorphic

Inventory

Function summary table

RPC	Location (`database.types.ts`)	TS callers	Return shape	Verdict
`get_author_analysis`	line 3227	1	Scalars + 4 nested arrays	`requires-design`
`get_bid_summary`	line 3255	0	Scalars + 4 nested arrays	`no-ts-callers`
`get_content_gaps`	line 3263	1	3 nested arrays (different element shapes)	`requires-design`
`get_dashboard_attention_counts`	line 3311	1	9 scalars + 1 nested object	`convertible`
`get_entity_list_aggregated`	line 3386	1	Pagination envelope `{entities:[...], total:N}`	`requires-design`
`get_filter_counts`	line 3419	2	3 flat jsonb maps (label→count)	`convertible`
`get_reading_patterns`	line 3533	1	Scalars + 3 nested arrays	`requires-design`
`get_review_breakdown_stats`	line 3534	1	Scalars + 4 dynamic-keyed objects	`requires-design`
`get_topic_deep_dive`	line 3586	1	Scalars + 5 nested arrays	`requires-design`
`get_user_tag_counts`	line 3623	1	Flat jsonb map (tag→count)	`convertible`
`get_verification_stats`	line 3624	0	Scalars + 1 nested array	`no-ts-callers`
`get_workspace_counts`	line 3625	0	Flat jsonb map (name→count)	`convertible`
`hook_restrict_signup_to_allowed_domain`	line 3636	0	Auth Hook — platform protocol	`leave-as-is`
`merge_entities`	line 3678	1	7 scalar fields	`convertible`

Per-function detail

`get_author_analysis`

Location: supabase/types/database.types.ts:3227 — migration supabase/migrations/20260419095345_restore_stub_functions_from_production.sql
Arguments: (p_author_name text)
TS callers:
- app/api/insights/route.ts:71 — const { data, error } = await supabase.rpc('get_author_analysis', { p_author_name: author }) — data passed to NextResponse.json({ author: data }) without casting

Current return shape:

1
json_build_object(
2
  'author_name', p_author_name,
3
  'total_items', (COUNT),
4
  'first_item', (MIN captured_date),
5
  'latest_item', (MAX captured_date),
6
  'avg_confidence', (AVG),
7
  'domain_breakdown', json_agg([{domain, count}]),
8
  'subtopic_breakdown', json_agg([{subtopic, count}]),
9
  'top_keywords', json_agg([{keyword, count}] LIMIT 10),
10
  'content_types', json_agg([{type, count}])
11
)

Convertibility verdict: requires-design
Reason: Four nested json_agg arrays prevent a single flat RETURNS TABLE. Top-level scalar fields (5 fields) are individually convertible.
Design path: Split into two RPCs — get_author_stats(p_author_name) returning the 5 scalars as RETURNS TABLE(...), and get_author_breakdowns(p_author_name) returning a structured array type or keeping breakdown queries client-side.
Risk notes: Single API route caller. Insights page at /insights?type=author would need route-level changes to call the split RPCs.

`get_bid_summary`

Location: supabase/types/database.types.ts:3255 — migration supabase/migrations/20260416102457_pre_squash_reconciliation.sql:1029
Arguments: (bid_workspace_id uuid)
TS callers: none

Current return shape:

1
json_build_object(
2
  'workspace_id', bid_workspace_id,
3
  'total_questions', (COUNT),
4
  'status_breakdown', json_agg([{status, count}]),
5
  'confidence_breakdown', json_agg([{posture, count}]),
6
  'responses_count', (COUNT),
7
  'review_status_breakdown', json_agg([{status, count}]),
8
  'sections', json_agg([{section, question_count, completed}])
9
)

Convertibility verdict: no-ts-callers
Recommendation: Candidate for deletion. Confirmed zero callers in app/, lib/, hooks/, scripts/, and Python pipeline. Verify against Supabase Edge Function logs and direct DB clients before deleting.
Risk notes: The function summarises bid workspace data — its purpose is served by get_bid_question_stats_batch (which has active callers) and per-question queries. If it was used historically and dropped from callers intentionally, deletion is safe.

`get_content_gaps`

Location: supabase/types/database.types.ts:3263 — migration supabase/migrations/20260419095345_restore_stub_functions_from_production.sql
Arguments: (none)
TS callers:
- app/api/insights/route.ts:84 — const { data, error } = await supabase.rpc('get_content_gaps') — data passed to NextResponse.json({ gaps: data }) without casting

Current return shape:

1
json_build_object(
2
  'sparse_subtopics', json_agg([{domain, subtopic, count, latest}]),
3
  'stale_subtopics',  json_agg([{domain, subtopic, count, latest, days_since}]),
4
  'domain_summary',   json_agg([{domain, total_items, subtopic_count, latest, avg_confidence}])
5
)

Convertibility verdict: requires-design
Reason: Three sections with different element shapes. No single RETURNS TABLE fits all three simultaneously.
Design path: Three separate structured RPCs — get_sparse_subtopics(), get_stale_subtopics(), get_domain_summary() — each returning a typed RETURNS TABLE(...). Enables independent caching and parallel fetching.
Risk notes: Single API route caller. Insights page at /insights?type=gaps would need route-level changes. Network round trips would increase from 1 to 3 unless combined in a single route handler.

`get_dashboard_attention_counts`

Location: supabase/types/database.types.ts:3311 — migration supabase/migrations/20260416102457_pre_squash_reconciliation.sql (extended in subsequent migrations)
Arguments: (p_user_id uuid, p_role text DEFAULT 'viewer')
TS callers:
- lib/dashboard.ts:287 — supabase.rpc('get_dashboard_attention_counts', { p_user_id: userId, p_role: effectiveRole }) — result is manually cast at line 374 to a hand-authored inline type

Current return shape:

1
json_build_object(
2
  'governance_review_count',      integer,
3
  'unverified_count',             integer,
4
  'quality_flag_count',           integer,
5
  'stale_content_count',          integer,
6
  'expired_content_count',        integer,
7
  'expiring_content_date_count',  integer,
8
  'unread_notification_count',    integer,
9
  'coverage_gap_count',           integer,
10
  'freshness_summary', {
11
    'fresh':   integer,
12
    'aging':   integer,
13
    'stale':   integer,
14
    'expired': integer
15
  }
16
)

Convertibility verdict: convertible

Migration sketch:

1
-- Flatten freshness_summary into 4 separate columns
2
CREATE OR REPLACE FUNCTION public.get_dashboard_attention_counts(
3
  p_user_id uuid,
4
  p_role    text DEFAULT 'viewer'
5
)
6
RETURNS TABLE (
7
  governance_review_count     integer,
8
  unverified_count            integer,
9
  quality_flag_count          integer,
10
  stale_content_count         integer,
11
  expired_content_count       integer,
12
  expiring_content_date_count integer,
13
  unread_notification_count   integer,
14
  coverage_gap_count          integer,
15
  freshness_fresh             integer,
16
  freshness_aging             integer,
17
  freshness_stale             integer,
18
  freshness_expired           integer
19
)
20
LANGUAGE plpgsql
21
SECURITY INVOKER
22
SET search_path = public, extensions
23
AS $$ ... $$;

After migration: run supabase gen types typescript --project-id rovrymhhffssilaftdwd > supabase/types/database.types.ts. Cast at lib/dashboard.ts:374 is removed; caller accesses data[0].freshness_fresh etc.

Cast removal sweep: 1 call site (lib/dashboard.ts:287). Inline type definition at lines 374-403 removed. Estimated ~10 lines net deletion.
Risk notes: Low. Single caller. Function body is stable (last extended in a numbered migration). The freshness sub-object flattening may require client-side re-composition of the freshness_summary object if other consumers exist — none found in this scan.

`get_entity_list_aggregated`

Location: supabase/types/database.types.ts:3386 — migration supabase/migrations/20260416102457_pre_squash_reconciliation.sql:1503
Arguments: (p_type text, p_search text, p_variants_only boolean, p_type_conflicts boolean, p_limit integer, p_offset integer)
TS callers:
- app/api/entities/route.ts:40 — const { data, error } = await supabase.rpc('get_entity_list_aggregated', { ... }) — data passed directly as NextResponse.json(data) (line 56); no casting

Current return shape:

1
json_build_object(
2
  'entities', json_agg([{
3
    canonical_name, entity_type, mention_count, variant_count,
4
    variant_names (array), relationship_count,
5
    has_type_conflict, types_seen (array)
6
  }] ORDER BY mention_count DESC),
7
  'total', (COUNT from total_count CTE)
8
)

Convertibility verdict: requires-design
Reason: The { entities: [...], total: N } pagination envelope is a deliberate design pattern — data and page count returned in one round trip. RETURNS TABLE alone cannot model this without splitting into two RPCs, which would require client-side changes to the GET /api/entities handler and its consumers. The types_seen and variant_names array columns would also require composite type definitions.
Design options:
1. Split RPCs: get_entity_list(...) returning RETURNS TABLE(...) for the rows, get_entity_count(...) returning integer. Doubles network round trips unless coalesced in the route handler.
2. Composite type: Define a entity_list_result composite type in Postgres and return RETURNS entity_list_result. More structured but still opaque to database.types.ts generation.
3. Accept JSONB columns: Keep the RETURNS TABLE(entity_data jsonb, total integer) pattern — partially typed (total is exact) but entities array remains jsonb. Minimal benefit.
Risk notes: The existing app/api/entities/route.ts passes data directly to the client — any conversion affects the client-side API shape.

`get_filter_counts`

Location: supabase/types/database.types.ts:3419 — migration supabase/migrations/20260501173008_widen_get_filter_counts_publication_status.sql
Arguments: (none)
TS callers:
- hooks/browse/use-filter-data.ts:62 — const { data, error } = await getSupabase().rpc('get_filter_counts') — result validated via parseJsonb(FilterCountsSchema, data) (Zod)
- hooks/browse/use-top-domains.ts:51 — const { data, error } = await getSupabase().rpc('get_filter_counts') — result validated via parseJsonb(FilterCountsSchema, data) (Zod)

Current return shape:

1
jsonb_build_object(
2
  'domain',       jsonb_object_agg(primary_domain, count),
3
  'content_type', jsonb_object_agg(content_type,   count),
4
  'platform',     jsonb_object_agg(platform,        count)
5
)

Convertibility verdict: convertible

Migration sketch:

1
CREATE OR REPLACE FUNCTION public.get_filter_counts()
2
RETURNS TABLE (
3
  domain       jsonb,   -- e.g. {"Technology": 42, "Finance": 17}
4
  content_type jsonb,
5
  platform     jsonb
6
)
7
LANGUAGE plpgsql
8
SECURITY INVOKER
9
SET search_path = public, extensions
10
AS $$ ... $$;

After migration: callers receive a single-row TABLE result; access via data[0].domain (was data.domain). Both callers already use parseJsonb(FilterCountsSchema, data) — the Zod call site would change from parseJsonb(schema, data) to parseJsonb(schema, data[0]).

Cast removal sweep: 2 call sites. Both already Zod-validated — cast removal is replacing data with data[0] in the parseJsonb call. Estimated ~2 lines changed.
Risk notes: Lower urgency than get_dashboard_attention_counts — Zod already provides runtime type safety at both call sites. The jsonb column types are retained (the maps remain JSONB objects); the benefit is typed access to the three top-level keys rather than opaque Json.

`get_reading_patterns`

Location: supabase/types/database.types.ts:3533 — migration supabase/migrations/20260419095633_restore_stub_analytics_functions.sql
Arguments: (p_days integer DEFAULT 30)
TS callers:
- app/api/insights/route.ts:95 — const { data, error } = await supabase.rpc('get_reading_patterns', { p_days: days }) — data passed to NextResponse.json({ reading: data }) without casting

Current return shape:

1
json_build_object(
2
  'period_days',      p_days,
3
  'total_items',      (COUNT),
4
  'items_read',       (COUNT),
5
  'reading_velocity', (ROUND(items/days)),
6
  'domain_reading',   json_agg([{domain, total, read, read_pct}]),
7
  'type_reading',     json_agg([{type, total, read}]),
8
  'reading_timeline', json_agg([{date, count}])
9
)

Convertibility verdict: requires-design
Reason: 4 scalar fields + 3 nested arrays. Same pattern as get_author_analysis. Scalars are convertible; arrays are not without composite types or split RPCs.
Design path: Same as get_author_analysis — split scalars from breakdowns, or accept composite array types. Network efficiency consideration applies since the insights page fetches this in a single round trip.
Risk notes: Single API route caller. Low urgency — route passes data through without client-side casting.

`get_review_breakdown_stats`

Location: supabase/types/database.types.ts:3534 — migration supabase/migrations/20260427230503_extend_review_breakdown_overdue.sql
Arguments: (none)
TS callers:
- app/api/review/stats/route.ts:45 — supabase.rpc('get_review_breakdown_stats') — result explicitly cast at line 76: statsResult.data as Omit<ReviewStatsResponse, 'unverified' | 'awaiting_publication'> & { total: number; verified: number }

Current return shape:

1
json_build_object(
2
  'total',    (COUNT),
3
  'verified', (COUNT),
4
  'flagged',  (COUNT),
5
  'draft',    (COUNT),
6
  'overdue',  (COUNT),
7
  'by_domain',        json_object_agg(domain_name, {total, verified}),
8
  'by_content_type',  json_object_agg(ct,          {total, verified}),
9
  'by_source_file',   json_object_agg(sf,           {total, verified}),
10
  'by_source_document', json_object_agg(doc_id,     {total, verified, name})
11
)

Convertibility verdict: requires-design
Reason: Five scalar fields are convertible. Four breakdown objects use json_object_agg with runtime-dynamic keys (domain names, content types, source file paths, document IDs) — these cannot be modelled as RETURNS TABLE columns. JSONB is the correct type for dynamic-keyed objects.
Design path (partial conversion): Highest-value option is a hybrid signature: scalar fields as typed RETURNS TABLE columns, breakdown objects as jsonb columns.
```
1
RETURNS TABLE (
2
  total     integer,
3
  verified  integer,
4
  flagged   integer,
5
  draft     integer,
6
  overdue   integer,
7
  by_domain        jsonb,
8
  by_content_type  jsonb,
9
  by_source_file   jsonb,
10
  by_source_document jsonb
11
)
```
This would allow the cast at app/api/review/stats/route.ts:76 to be reduced — total and verified would be typed directly, removing the & { total: number; verified: number } extension. The four JSONB breakdown columns remain opaque but their presence is now compiler-verified.
Risk notes: The ReviewStatsResponse type in types/review.ts would need updating after regenerating database.types.ts. Single caller. Medium complexity.

`get_topic_deep_dive`

Location: supabase/types/database.types.ts:3586 — migration supabase/migrations/20260419095633_restore_stub_analytics_functions.sql
Arguments: (p_keyword text)
TS callers:
- app/api/insights/route.ts:52 — const { data, error } = await supabase.rpc('get_topic_deep_dive', { p_keyword: keyword }) — data passed to NextResponse.json({ topic: data }) without casting

Current return shape:

1
json_build_object(
2
  'keyword',               p_keyword,
3
  'total_items',           (COUNT),
4
  'domain_distribution',   json_agg([{domain, count}]),
5
  'top_authors',           json_agg([{author, count}] LIMIT 10),
6
  'timeline',              json_agg([{month, count}] LIMIT 12),
7
  'co_occurring_keywords', json_agg([{keyword, count}] LIMIT 15),
8
  'recent_items',          json_agg([{...item fields}])
9
)

Convertibility verdict: requires-design
Reason: 2 scalar fields + 5 nested arrays with different element shapes. Same pattern as get_author_analysis and get_reading_patterns.
Design path: Same split-RPC approach as the other insights analytics functions.
Risk notes: Single API route caller. Route passes data through without casting — low type-safety risk currently.

`get_user_tag_counts`

Location: supabase/types/database.types.ts:3623 — migration supabase/migrations/20260419095633_restore_stub_analytics_functions.sql
Arguments: (none)
TS callers:
- hooks/browse/use-filter-data.ts:125 — const { data } = await getSupabase().rpc('get_user_tag_counts') — result cast as data as Record<string, number> at line 127 then decomposed via Object.entries(tagCounts)

Current return shape:

1
SELECT COALESCE(jsonb_object_agg(tag, cnt), '{}'::jsonb)
2
FROM (SELECT tag, COUNT(*) as cnt FROM content_items ci, unnest(ci.user_tags) AS tag
3
  WHERE user_tags IS NOT NULL AND user_tags != '{}' GROUP BY tag ORDER BY cnt DESC) sub;

Returns a flat JSONB object: { "procurement": 41, "AI": 28, ... }.

Convertibility verdict: convertible

Migration sketch:

1
CREATE OR REPLACE FUNCTION public.get_user_tag_counts()
2
RETURNS TABLE (tag text, cnt bigint)
3
LANGUAGE sql
4
STABLE
5
SECURITY INVOKER
6
SET search_path = public, extensions
7
AS $$
8
  SELECT tag, COUNT(*) AS cnt
9
  FROM content_items ci, unnest(ci.user_tags) AS tag
10
  WHERE user_tags IS NOT NULL AND user_tags != '{}'
11
  GROUP BY tag
12
  ORDER BY cnt DESC;
13
$$;

After migration: data is a typed row array. The cast at line 127 (data as Record<string, number>) is removed. The Object.entries(tagCounts) decomposition becomes data.map(row => ({ tag: row.tag, count: Number(row.cnt) })).

Cast removal sweep: 1 call site. ~3 lines changed. Zero risk.
Risk notes: Simplest conversion in the set. Single caller, no nested structures, existing logic is entirely straightforward. Recommended as the first conversion to validate the migration + gen types + cast removal workflow.

`get_verification_stats`

Location: supabase/types/database.types.ts:3624 — migration supabase/migrations/20260416102457_pre_squash_reconciliation.sql:2455
Arguments: (none)
TS callers: none

Current return shape:

1
json_build_object(
2
  'total',      (COUNT),
3
  'verified',   (COUNT),
4
  'unverified', (COUNT),
5
  'recent_7d',  (COUNT),
6
  'domains',    json_agg([{domain, count}])
7
)

Convertibility verdict: no-ts-callers
Recommendation: Candidate for deletion. Similar purpose to get_review_breakdown_stats, which has an active caller and richer output. Verify via Supabase Edge Function logs or direct DB access logs before deleting. Zero sweep cost.
Risk notes: If deleted and later found to be used by a DB-level trigger or another Postgres function, the impact is low — function can be re-created from the migration history.

`get_workspace_counts`

Location: supabase/types/database.types.ts:3625 — migration supabase/migrations/20260416102457_pre_squash_reconciliation.sql:2479
Arguments: (none)
TS callers: none

Current return shape:

1
SELECT COALESCE(jsonb_object_agg(name, cnt), '{}'::jsonb)
2
FROM (
3
  SELECT w.name, COUNT(*) as cnt
4
  FROM content_item_workspaces ciw
5
  JOIN workspaces w ON w.id = ciw.workspace_id
6
  WHERE w.is_archived = false
7
  GROUP BY w.name ORDER BY cnt DESC
8
) sub;

Returns a flat JSONB object: { "Bid Management": 312, "Finance Sector": 186, ... }.

Convertibility verdict: convertible

Migration sketch:

1
CREATE OR REPLACE FUNCTION public.get_workspace_counts()
2
RETURNS TABLE (workspace_name text, item_count bigint)
3
LANGUAGE sql
4
STABLE
5
SECURITY INVOKER
6
SET search_path = public, extensions
7
AS $$
8
  SELECT w.name AS workspace_name, COUNT(*) AS item_count
9
  FROM content_item_workspaces ciw
10
  JOIN workspaces w ON w.id = ciw.workspace_id
11
  WHERE w.is_archived = false
12
  GROUP BY w.name
13
  ORDER BY item_count DESC;
14
$$;

No active TS callers — zero sweep cost. Recommended to establish the correct pattern before any future caller is added.

Risk notes: Zero TS callers means zero breaking changes. Convert alongside get_user_tag_counts for efficiency.

`hook_restrict_signup_to_allowed_domain`

Location: supabase/types/database.types.ts:3636 — migration supabase/migrations/20260424202806_capture_signup_domain_hook.sql
Arguments: (event jsonb)
TS callers: none (by design)

Current return shape:

1
-- Success (domain = 'client.example'):
2
RETURN '{}'::jsonb;
3

4
-- Rejection:
5
RETURN jsonb_build_object(
6
  'error', jsonb_build_object(
7
    'message', 'Please sign up with your @client.example email address.',
8
    'http_code', 403
9
  )
10
);

Convertibility verdict: leave-as-is
Reason: This function is a Supabase Auth Hook, registered via the pg-functions://postgres/public/hook_restrict_signup_to_allowed_domain URI in the Supabase project settings (see docs/audits/kh-production-readiness-phase-1/research/09-vercel-supabase-env-mapping-spec.md:724). The JSONB input/output contract is defined by the Supabase Auth Hook protocol — the platform calls this function with a structured event jsonb and expects a jsonb response. Converting to RETURNS TABLE would break the hook registration. Zero TS callers is expected and correct.
Risk notes: Do not convert. Do not add TS callers. The function is platform-invoked infrastructure.

`merge_entities`

Location: supabase/types/database.types.ts:3678 — migration supabase/migrations/20260416102457_pre_squash_reconciliation.sql:2617
Arguments: (p_source_names text[], p_target_name text, p_entity_type text)
TS callers:
- app/api/entities/merge/route.ts:48 — const { data, error } = await serviceClient.rpc('merge_entities', { ... }) — result explicitly cast at line 62 to a hand-authored inline type

Current return shape:

1
jsonb_build_object(
2
  'merged',                      true,
3
  'target',                      p_target_name,
4
  'entity_type',                 p_entity_type,
5
  'mentions_updated',            v_mentions_updated,
6
  'relationship_sources_updated', v_rel_sources_updated,
7
  'relationship_targets_updated', v_rel_targets_updated,
8
  'duplicates_removed',          v_duplicates_removed
9
)

Convertibility verdict: convertible

Migration sketch:

1
CREATE OR REPLACE FUNCTION public.merge_entities(
2
  p_source_names text[],
3
  p_target_name  text,
4
  p_entity_type  text
5
)
6
RETURNS TABLE (
7
  merged                       boolean,
8
  target                       text,
9
  entity_type                  text,
10
  mentions_updated             integer,
11
  relationship_sources_updated integer,
12
  relationship_targets_updated integer,
13
  duplicates_removed           integer
14
)
15
LANGUAGE plpgsql
16
SECURITY INVOKER
17
SET search_path = public, extensions
18
AS $$ ... $$;

After migration: data[0] gives the typed result. The cast at app/api/entities/merge/route.ts:62 is removed entirely.

Cast removal sweep: 1 call site (~8 lines of inline type + cast removed). After supabase gen types typescript regeneration, the column types are compiler-enforced.
Risk notes: Function is VOLATILE (performs DML: UPDATE, DELETE in a single transaction). Mark as LANGUAGE plpgsql SECURITY INVOKER — not STABLE. Single caller via service client. Integration test recommended after migration (confirm row counts propagate correctly in the typed result).

Recommendations

Migration sprint scope (OQ-R9 input)

Tier	Functions	Effort estimate	Value
Tier 1 — Do now (convertible, single caller)	`get_user_tag_counts`, `get_workspace_counts`, `merge_entities`	~3h total (1h per function: migration + gen types + cast sweep + test)	Removes 3 casts, validates the migration pattern
Tier 2 — Do next (convertible, multi-caller or nested object)	`get_dashboard_attention_counts`, `get_filter_counts`	~4h total	Removes the dashboard inline type definition (~30 lines); Zod call sites simplified
Tier 3 — Design first (requires-design)	`get_review_breakdown_stats` (partial conversion recommended — scalars to typed columns)	~3h for partial + 1h design	Narrows the cast at `review/stats/route.ts:76`
Tier 3 — Design first (remaining requires-design)	`get_author_analysis`, `get_content_gaps`, `get_reading_patterns`, `get_topic_deep_dive`, `get_entity_list_aggregated`	~2h design per function + ~4h implementation per split	Major sprint; requires Liam input on split-RPC vs composite type approach
Tier 4 — Housekeeping	`get_bid_summary`, `get_verification_stats` (delete)	~0.5h each	Reduces schema noise

If you migrate only Tier 1+2 (the 5 convertible functions): ~7h total. Eliminates the as Record<string, unknown> cast in use-filter-data.ts, the inline type definition in lib/dashboard.ts, and the three simple casts. All 12 active TS caller files receive typed access to the columns. This is the recommended starting scope.

If you also tackle get_review_breakdown_stats partial conversion (Tier 3 first function): ~10h total. Adds typed scalar columns to the review stats RPC, which is the highest-traffic API endpoint in the review workflow.

If you tackle all requires-design functions: ~30h total (design + implementation + client changes). Multi-sprint effort; recommend spreading across S10–S12 with Liam review at each stage.

Recommended start: `get_user_tag_counts`

This is the simplest function to convert end-to-end:

Single caller, single cast removed
Body converts trivially from jsonb_object_agg to a SELECT ... GROUP BY table return
Low risk (no DML, no auth role dependency)
Validates the full pipeline: migration → supabase gen types → cast removal → test

Use it as a proof-of-concept to confirm the gen types regeneration workflow is stable before tackling the higher-value functions.

Decision gate (R-WP21 / S10 follow-up)

The key question for Liam at S10 planning:

Is the “requires-design” category worth a split-RPC approach, or should the insights analytics functions (get_author_analysis, get_content_gaps, get_reading_patterns, get_topic_deep_dive) accept typed scalars + opaque JSONB arrays?

The partial conversion option (scalars typed, arrays remain JSONB) is mechanically achievable without any client-side changes. It would narrow the type gap for the scalar fields (total counts, period parameters) while accepting continued JSONB opacity for the breakdown arrays. This may be the right pragmatic call given the insight functions are read-only, low-volume analytics endpoints with no current type-safety incidents.

Out of scope

Migration execution. All migration sketches above are recommendations only — none have been run.
Cast-removal sweep at call sites (that is R-WP18 territory, after R-WP17 ships the drift report).
Python pipeline opaque-Json equivalents. The Python pipeline (scripts/kb_pipeline/) makes Supabase RPC calls directly via the Python supabase client — those call sites are a separate concern and not inventoried here.
database.types.ts CI automation. Adding supabase gen types to CI on migration merges is a production-readiness track item (flagged in R-WP12 §Gap 2 OQ 6).

Open questions

OQ	Question	For
OQ-1	Are `get_bid_summary` and `get_verification_stats` safe to delete? Confirm via production Supabase function-call metrics or Edge Function logs.	Liam / S10
OQ-2	For the `requires-design` analytics functions (`get_author_analysis`, `get_content_gaps`, `get_reading_patterns`, `get_topic_deep_dive`): prefer split-RPC, partial conversion (scalars typed / arrays JSONB), or accept as-is?	Liam
OQ-3	For `get_entity_list_aggregated`: the `{ entities: [...], total: N }` pagination envelope is a deliberate design. Is a two-RPC split (rows + count) acceptable, or should the envelope be kept and the function left as `requires-design`?	Liam
OQ-4	Is `get_review_breakdown_stats` partial conversion (scalars typed, breakdown objects remain JSONB columns) the right call? Or is the dynamic-keyed breakdown structure worth resolving fully?	Liam
OQ-5	Tier 1+2 sprint (5 convertible functions, ~7h): schedule in S10 or defer to S11?	Liam

Appendix — audit script

The machine-readable backing for this brief is scripts/audit-opaque-json-rpcs.ts.

1
# Re-run to verify inventory (deterministic — reads database.types.ts + corpus)
2
bun scripts/audit-opaque-json-rpcs.ts
3

4
# Verdict summary
5
bun scripts/audit-opaque-json-rpcs.ts 2>&1 | grep -A5 'Audit summary'
6

7
# Per-function TS caller counts
8
bun scripts/audit-opaque-json-rpcs.ts 2>/dev/null | jq -r '[.function_name, (.ts_callers | length | tostring)] | join(": ")'

Authored kh-ast-S9 Wave 1 (R-WP19). Investigation only — no migrations executed.

Redaction note (04/08/2026, id-377 D7). Client-name tokens in this file were redacted per the ID-115 redaction map (specs/id-115-data-api-schema-isolation/CUTOVER-RUNBOOK.md step 3; full rule set in runbooks/_archive/id68-purge-redaction-map-draft.md). Six occurrences in the auth-hook entry: the function identifier (inventory row, §heading and the pg-functions:// URI) now reads hook_restrict_signup_to_allowed_domain — the client-neutral name that superseded it at canonical HEAD (ID-68 {68.21}); the migration filename follows map rule R5 (capture_signup_domain_hook.sql); and the two domain literals in the illustrative return-shape block — including the user-facing rejection message — follow rule R3 (client.example). The 20260424202806 version prefix, the database.types.ts:3636 line reference and the leave-as-is verdict are unchanged. No other content was changed by the redaction pass.

R-WP12 — Opaque-Json RPC inventory (Wave 5 / kh-ast-S9)

R-WP12 — Opaque-Json RPC inventory (Wave 5 / kh-ast-S9)

Summary

Method

Step 1 — Enumerate

Step 2 — TS callers

Step 3 — PL/pgSQL function bodies

Step 4 — Convertibility verdict

Inventory

Function summary table

Per-function detail

get_author_analysis

get_bid_summary

get_content_gaps

get_dashboard_attention_counts

get_entity_list_aggregated

get_filter_counts

get_reading_patterns

get_review_breakdown_stats

get_topic_deep_dive

get_user_tag_counts

get_verification_stats

get_workspace_counts

hook_restrict_signup_to_allowed_domain

merge_entities

Recommendations

Migration sprint scope (OQ-R9 input)

Recommended start: get_user_tag_counts

Decision gate (R-WP21 / S10 follow-up)

Out of scope

Open questions

Appendix — audit script

`get_author_analysis`

`get_bid_summary`

`get_content_gaps`

`get_dashboard_attention_counts`

`get_entity_list_aggregated`

`get_filter_counts`

`get_reading_patterns`

`get_review_breakdown_stats`

`get_topic_deep_dive`

`get_user_tag_counts`

`get_verification_stats`

`get_workspace_counts`

`hook_restrict_signup_to_allowed_domain`

`merge_entities`

Recommended start: `get_user_tag_counts`