ID-115 {115.1} S1 SPIKE FINDINGS — embedding/upsert/count gate (GREEN)
ID-115 {115.1} S1 spike — embedding / upsert / count feasibility (GATING)
Section titled “ID-115 {115.1} S1 spike — embedding / upsert / count feasibility (GATING)”Date: 2026-06-16 · Verdict: GREEN — proceed to S2 with the naive 1:1 security_invoker view plan.
The two blockers (R1 embedding, R2 onConflict upsert) are empirically eliminated.
Method
Section titled “Method”Built a throwaway api view set over all 70 public base tables on the local stack
(security_invoker = true, explicit ordered column lists, every FK column projected verbatim — the
S2 generation approach, validated: 70/70 views security_invoker=true). Exposed api to local
PostgREST (config.toml [api] schemas = ["public","api"], stack restart). Probed every distinct
embed relationship and onConflict target in the codebase via PostgREST (service key,
Accept-Profile/Content-Profile: api). PGRST200 (relationship) fires at plan-time regardless of row
count, so empty tables still prove resolution.
R1 — Embedding: ALL RESOLVE (no fallback needed)
Section titled “R1 — Embedding: ALL RESOLVE (no fallback needed)”Every embed style and every distinct relationship found in the codebase returned HTTP 200 (no
PGRST200) over the api views:
| Class | Probe | Result |
|---|---|---|
simple !inner (auth gate, 39 sites) | workspaces → application_types!inner(key) | 200, embedded |
FK !inner (5 sites) | content_history → content_items!inner(title,primary_domain) | 200, embedded |
| 3-hop chain (dashboard/reorient) | form_response_history → form_responses!inner(form_questions!inner(workspaces!inner(name))) | 200, resolved |
| self-join | content_items → content_items!superseded_by (and plain) | 200, resolved |
| count-with-embed (R8, metrics route) | feed_flags → feed_articles!inner(workspace_id) count=exact | 200, resolved |
| plain (no-bang) embed | coverage_targets → taxonomy_domains(name) | 200, resolved |
| other distinct FK embeds | feed_articles→feed_sources!inner, guide_sections→guides!inner, q_a_extractions→q_a_pairs!promoted_to_pair_id | 200 (guides returned embedded data) |
Mechanism: PostgREST follows pg_depend from the view column to the base column to the base FK,
so security_invoker views that project FK columns verbatim preserve relationship inference —
including 3-hop chains and self-joins. No computed-relationship api functions and no retained
DEFINER RPCs are required for any embed.
→ S2: build naive 1:1 security_invoker views projecting all columns (FK verbatim).
→ S4: the embedding-fallback work is eliminated; S4 reduces to the .schema('public')
escape-hatch inventory only.
R2 — onConflict upserts: TRANSPARENT (mechanism proven)
Section titled “R2 — onConflict upserts: TRANSPARENT (mechanism proven)”form_questions upsert through api.form_questions (onConflict workspace_id,question_text):
| Op | Result |
|---|---|
insert (resolution=ignore-duplicates) | 201 created |
| conflict branch, ignore-duplicates | 201 [] (DO NOTHING, no 42P10) |
| conflict branch, merge-duplicates | 200, row UPDATED (1,1 → 2,2) |
PostgREST forwards ON CONFLICT through the auto-updatable view to the base-table unique index.
api isolation is upsert-transparent — proven by the negative: read_marks upsert returns the
identical 42P10 through the api view and the base public table.
onConflict target coverage (all 10 distinct targets): id(PK), user_id(user_roles,
user_notification_prefs), workspace_id,question_text, payload_key, domain_id,metric_name,
canonical_name,entity_type,content_item_id, stored_tag,proposed_canonical,
citing_form_response_id,cited_content_item_id(citations — partial unique index
WHERE cited_kind='content_item'; mechanism identical, re-verify with seeded data in S12) — all
backed by a base-table unique index → work through the view.
→ S2/S4: no api upsert RPC fallback needed for api isolation.
Out-of-scope finding (PRE-EXISTING, not caused by api isolation) → backlog
Section titled “Out-of-scope finding (PRE-EXISTING, not caused by api isolation) → backlog”Two onConflict sites reference targets with no backing unique constraint anywhere (confirmed
absent on both local and staging via pg_constraint; 42P10 on direct base-table upsert):
read_marksonConflictuser_id,content_item_id(app/api/read-marks/route.ts:108,142) — only PK.form_responsesonConflictquestion_id(app/api/procurement/[id]/responses/draft/route.ts:228,draft-stream,lib/queue/handlers/procurement-draft-all.ts) — only PK.
These 42P10 wherever they run (latent bug: re-mark / re-draft idempotency path). Identical before and after api isolation (PostgREST forwards ON CONFLICT to the base relation either way). Triage question: is the upsert path reached / is the error swallowed? Add a unique constraint or change the conflict strategy. Tracked as a backlog candidate; NOT a blocker for ID-115.
Spike artifacts / cleanup
Section titled “Spike artifacts / cleanup”- Throwaway generator:
/tmp/claude/spike-api-views.sql(NOT committed); auto-cleaned by S2’s firstsupabase db reset --local(the views are not in any migration). config.tomlleft atschemas = ["public","api"]for S2/S12 testing convenience; S5 flips it to the final["api"](dropspublicfor the PGRST106 boundary).