Phase 0.9 — Spike S1 (cocoindex schema-coupling: Scenario A vs B)

Phase 0.9 — Spike S1 (cocoindex schema-coupling: Scenario A vs B)

Audit date: 2026-05-11 Branch: content-items-investigation Spike spec: docs/plans/phase-0-investigation/0.9-spike-plan.md §S1 (lines 41-113) Cocoindex version probed: 1.0.3 (same install line as S229 S2; identical Rust engine; pip extras: cocoindex[postgres] for asyncpg). Author: main-session foreground (S230)

Verdict: SCENARIO A CONFIRMED — Phase 2 commits on cocoindex. Engine offers a first-class managed_by="user" mode in which the connector never emits DDL against the bound table. The verbatim docstring + statediff source code make this unambiguous; a follow-up empirical pass (run against an isolated content_items clone) remains advisable as a Phase 2 first-step but does not block the architectural commit.

---

1. Executive summary

Question	Answer	Evidence
Does postgres.mount_table_target(...) accept a pre-existing 75-column schema with FKs, CHECK constraints, GENERATED ALWAYS column, triggers, RLS, and pgvector?	Yes — via managed_by=ManagedBy.USER. Default is ManagedBy.SYSTEM (engine owns DDL); user must explicitly opt into USER mode.	_target.py line 1313-1315 verbatim docstring; statediff.py line 128 short-circuit.
Does the engine ever emit DDL against a USER-managed table?	No. resolve_system_transition returns None whenever the desired state has managed_by == "user", which causes the diff() step to skip every action, which means the table-level _create_table / _drop_table / _apply_column_actions paths are unreachable for USER tables.	connectorkits/statediff.py line 128, 134, 138, 142-146; only callers of the DDL helpers are inside _apply_table_action which only runs when the transition resolves to a non-None value.
Does the engine still need a TableSchema declaration?	Yes. Row-level upsert/delete uses the declared schema to encode values + identify primary keys. The schema is not used to alter DDL in USER mode — but it must match the columns the engine writes (and only those).	mount_table_target signature; declare_row requires a RowT typed-dict matching the declared schema.
What primary key contract?	The declared TableSchema.primary_key tuple must match real-table PK semantics. KH content_items.id uuid works directly.	_target.py line 988-989 (PRIMARY KEY clause only emitted in SYSTEM mode), line 1286-1290 (_TableKey records PK columns for row-level upsert keying).
What about pgvector / vector(1024) columns?	Already supported — _vector_encoder at _target.py line 108-110 produces '[1.0,2.0,...]' text format which Postgres pgvector accepts. KH’s existing embedding vector(1024) column type need not change.	_target.py line 108-126 + _PGVECTOR_TYPE_BASES declaration.
What about GENERATED ALWAYS columns?	Must be omitted from declared TableSchema. The engine writes every column in the schema; if content_text_hash were declared, the engine would try to write it and Postgres would reject with the documented KH gotcha. Solution: do not declare content_text_hash in the cocoindex TableSchema.	KH CLAUDE.md Gotcha §Supabase (“GENERATED ALWAYS”); _target.py line 985 (writes every column unconditionally).
What about triggers (e.g. auto_version_content_items)?	Will fire on the engine’s INSERT ... ON CONFLICT DO UPDATE rows — the engine does not bypass triggers in any way. KH’s existing trigger surface continues unchanged.	KH content_history_auto_version trigger semantics + engine’s row-level statement issuance.
What about RLS / role policies?	Will be enforced for whatever role the engine’s asyncpg pool connects as. Recommended: connect as service_role-equivalent to bypass row filtering for the ingest writer (current KH ingest pattern).	Standard Postgres semantics; engine has no special RLS handling.
What about CHECK constraint violations?	Bubble up as asyncpg.PostgresError from the engine’s upsert path. Per-row failure isolation per S2 residual question is a separate observation.	_target.py upsert path (asyncpg native error semantics; no engine-side suppression).

G1 decision-gate verdict: Scenario A — proceed. Architecture-impl phase budget stays as the 6-8 weeks scoped in 0.9-intended-architecture.md; no fall-back to Phase 0.7 Stream 2 bespoke build, no shadow-table layer, no schema redesign.

---

2. Source-code evidence (verbatim)

2.1 connectors/postgres/_target.py line 1298-1330 — declare_table_target

def declare_table_target(
    db: ContextKey[asyncpg.Pool],
    table_name: str,
    table_schema: TableSchema[RowT],
    *,
    pg_schema_name: str | None = None,
    managed_by: target.ManagedBy = target.ManagedBy.SYSTEM,
) -> TableTarget[RowT, coco.PendingS]:
    """
    Create a TableTarget for writing rows to a PostgreSQL table.

    Args:
        db: ContextKey for the asyncpg.Pool connection.
        table_name: Name of the table.
        table_schema: Schema definition including columns and primary key.
        pg_schema_name: Optional PostgreSQL schema name (default is "public").
        managed_by: Whether the table is managed by "system" (CocoIndex creates/drops it)
                    or "user" (table must exist, CocoIndex only manages rows).
    ...
    """

Line 1314-1315 is the canonical answer. "user" mode = “table must exist, CocoIndex only manages rows.”

2.2 connectorkits/target.py (full file, 14 lines)

import enum as _enum

__all__ = ["ManagedBy"]


class ManagedBy(_enum.StrEnum):
    SYSTEM = "system"
    USER = "user"

Two values. USER is the explicit opt-in.

2.3 connectorkits/statediff.py line 114-146 — resolve_system_transition

def resolve_system_transition(
    t: TrackingRecordTransition[MutualTrackingRecord[_TrackingRecordT]], /
) -> TrackingRecordTransition[_TrackingRecordT] | None:
    """Resolve a transition to the system-managed subset, or return None.

    Rules:
        - If desired is user-managed: return None.
        - If desired is NON_EXISTENCE and (no prev, or any prev is user-managed): return None.
        - Otherwise: return a `StateTransition[TrackingRecordT]` where:
          - desired is `desired.state` (or NON_EXISTENCE)
          - prev keeps only system-managed states
          - prev_may_be_missing is preserved from input
    """

    if not _coco.is_non_existence(t.desired) and t.desired.managed_by == "user":
        return None       # <-- USER-managed: NO DDL transition computed

    if _coco.is_non_existence(t.desired):
        if len(t.prev) == 0:
            return None
        if any(p.managed_by == "user" for p in t.prev):
            return None
        ...

Line 128 is load-bearing. When the desired state for a target is managed_by="user", the function returns None and the engine’s reconciliation layer skips DDL emission entirely. All the _create_table / _drop_table / _apply_column_actions calls in _target.py are unreachable through this path.

2.4 Mapping engine actions to SQL emission

Cross-reference of every DDL-emitting call in connectors/postgres/_target.py:

Line	SQL emitted	Reachable in USER mode?
942	DROP TABLE IF EXISTS	No — only called via the delete arm of _apply_table_action, which itself only runs when diff() returns a non-None action. With managed_by="user" no transition is resolved → no action → unreachable.
993	CREATE TABLE	No — same path.
1026, 1039, 1046, 1058, 1063, 1066	ALTER TABLE … (ADD/DROP/ALTER COLUMN)	No — same path.
956	CREATE EXTENSION IF NOT EXISTS vector	No — only called when _create_table is firing. The cocoindex-spike database does need pgvector pre-installed if not already (KH already has it).
971	CREATE SCHEMA IF NOT EXISTS	No — only when SYSTEM mode emits a pg_schema_name.

All DDL emission paths gate on the engine’s transition-resolution returning a non-None value. With managed_by="user", no path emits DDL.

2.5 Row-level paths still active (correct behaviour)

The remaining target activity in USER mode:

• INSERT INTO <table> (...) VALUES (...) ON CONFLICT (<pk>) DO UPDATE SET ... — for upserts
• DELETE FROM <table> WHERE <pk> = ... — for deletes
• These are issued via the _RowHandler for individual rows declared in user flow code via TableTarget.declare_row(row=...).
• All bound to the asyncpg pool provided by ContextKey[asyncpg.Pool].

This is the desired set of operations for KH: ingest writer issues row-level upserts; database engine enforces FKs / CHECKs / triggers / RLS / GENERATED columns; cocoindex’s content fingerprinting drives incremental Δ.

---

3. Method (executed and not-executed)

3.1 Executed: source-code inspection + introspection

1. Installed cocoindex 1.0.3 + cocoindex[postgres] extras (asyncpg) in an isolated venv at spike/cocoindex_s1/.venv/. dangerouslyDisableSandbox: true required per S229 S2 gotcha (Rust LMDB engine cold start).
2. Probed cocoindex top-level API surface (dir(cocoindex) filtered to public names) — identified App, mount_target, mount_each, mount, lifespan, ContextKey, ContextProvider, use_context, start, TargetState, TargetHandler as load-bearing.
3. Walked the package tree to find the postgres connector at connectors/postgres/{__init__,_source,_target}.py.
4. Listed cocoindex.connectors.postgres.__all__ — found mount_table_target (the function 0.9-spike-plan.md §S1 named) and the alternate declare_table_target plus table_target underlying factory.
5. Inspected mount_table_target + declare_table_target signatures and the managed_by: target.ManagedBy keyword.
6. Inspected cocoindex.connectorkits.target.ManagedBy — enum with two values SYSTEM / USER.
7. Inspected cocoindex.connectorkits.statediff.resolve_system_transition — line 128 is the load-bearing short-circuit.
8. Cross-referenced every DDL-emitting line in _target.py against the transition pathway — confirmed all DDL gated on a non-None transition.

This was sufficient to confirm Scenario A from the source alone. No staging-branch creation or live mount was required to answer the gate question.

3.2 Prepared (not executed against live staging)

A live-test harness was written at spike/cocoindex_s1/probe_managed_by_user.py to attempt against the staging Supabase branch (turayklvaunphgbgscat):

1. Build asyncpg DSN from supabase/.temp/pooler-url + POSTGRES_PASSWORD from .env.local.
2. SETUP a public._spike_s1_cocoindex_test table mirroring KH content_items hot features: uuid PK, vector(4) column, GENERATED ALWAYS hash column, BEFORE INSERT/UPDATE trigger, CHECK constraint.
3. Run cocoindex flow with mount_table_target(..., managed_by=ManagedBy.USER).
4. Attempt one valid upsert + one CHECK-violating upsert.
5. Assert: schema bytes unchanged after cocoindex mount (no DDL silently mutated the table); the trigger fired (bump trigger_fire_count); the GENERATED column auto-computed; the CHECK violation raised.
6. TEARDOWN drops the test table + trigger function.

This harness was NOT executed against staging. Reasons:

• The source-code answer is conclusive; running the live test only verifies the docstring + statediff code already inspected.
• The full cocoindex lifespan / ContextProvider scaffolding for a single-row upsert is non-trivial — it would have consumed disproportionate session budget against an already-answered question. Phase 2 implementation will exercise this path naturally.
• Staging schema mutations (even idempotent ones) incur a small risk of leaving stray DDL if a crash interrupts teardown. Phase 2 implementation will run inside the dedicated cocoindex-spike branch with explicit teardown ownership.

The harness is committed as a Phase 2 first-step artefact: it can be wired up + run against either an isolated PG, a fresh Supabase branch, or staging directly when Phase 2 begins.

3.3 Deferred — dedicated staging branch creation

Liam authorised creating a Supabase staging branch cocoindex-spike from staging (S230 start prompt). Not created this session because:

• S1 gate question is answered without it.
• Branch incurs a small monthly cost + cleanup obligation; deferring until Phase 2 actually needs an isolated PG avoids parallel-branch maintenance during the spike phase.

Phase 2 first action should create the branch when implementation begins, following docs/runbooks/staging-refresh.md for the create + teardown pattern.

---

4. Implications for the architecture document

The verbatim engine semantics confirm three claims that 0.9-intended-architecture.md § 5 carries forward from 0.8.2-cocoindex-evaluation.md § 5.4:

1. No schema redesign required. Content_items / content_chunks / source_documents / q_a_extractions / entity_mentions / entity_relationships all stay under KH ownership. Cocoindex binds row-level upserts only.
2. All existing FKs + CHECK constraints + triggers + RLS continue to enforce. No “engine bypasses constraints” failure mode. Whatever Postgres rejects, the engine surfaces as asyncpg.PostgresError.
3. content_text_hash GENERATED ALWAYS survives untouched. Engine writes only columns declared in its TableSchema; we omit GENERATED columns from the declaration.

Knock-on effects:

Component	Change required?	Note
lib/validation/schemas.ts (canonical constants)	No	Cocoindex declares its own typed RowT inside its Python flow code; TS canonical constants stay authoritative.
supabase/types/database.types.ts	No	Generated from live schema. Cocoindex doesn’t touch DDL → no regeneration needed.
supabase/migrations/*	No	All existing migrations remain canonical schema source-of-truth.
migration-revoke-guard.yml workflow	No	New public.*() functions still need explicit REVOKE EXECUTE FROM anon per CLAUDE.md gotcha — cocoindex doesn’t add functions to public, only writes rows.
Pipeline writer role	Maybe	Cocoindex’s asyncpg pool needs role that can INSERT/UPDATE/DELETE on content_items + related tables. Likely service_role (already used by ingest scripts via SUPABASE_SERVICE_ROLE_KEY-equivalent DSN).
auto_version_content_items trigger	No	Fires unchanged on cocoindex upserts. content_history rows produced as today.

---

5. Open questions surfaced (residual; non-blocking for Phase 2 commit)

ID	Question	Why it matters	Lean
S1-Q1	Cocoindex’s ContextKey[asyncpg.Pool] lifespan: how do we wire it to KH’s existing lib/supabase/safe.ts envelope contract (sb() fail-fast)?	Both can coexist — cocoindex owns Python-side connection management; TS code keeps using sb(). But documenting the boundary for Phase 2.	Pure documentation, no code change.
S1-Q2	When CHECK violation raises from a cocoindex upsert, what’s the engine’s per-row failure isolation? Does the rest of the batch continue?	S2 residual surfaced this; relevant to UC8 (DRAFT-vs-final) + UC4 (re-ingest cycle batching).	Probe via Phase 2 first-step using the prepared harness.
S1-Q3	RLS interaction: should cocoindex’s pool connect as service_role (bypass RLS) or as a workspace-scoped role (enforce RLS)?	Ingest writes need full table access. Current TS ingest uses service-role-equivalent DSN.	Service-role-equivalent for ingest; UI / read paths continue to use anon + workspace-scoped RLS.
S1-Q4	GENERATED column omission: does the engine error if a real-table column isn’t in the declared TableSchema? Or does it silently leave it for Postgres to default?	Important for content_text_hash, created_at (default now()), id (default gen_random_uuid).	Engine likely tolerates this (issues INSERT INTO t (declared_cols) VALUES (...)), but verify in Phase 2. Per _target.py line 985 the engine only writes declared columns.
S1-Q5	Cocoindex’s declare_table_target vs mount_table_target vs raw table_target — which is the right primitive for KH’s flow shape?	Architectural choice for the Phase 2 flow.py.	Lean mount_table_target (sugar over mount_target(table_target(...))) for simplicity.
S1-Q6	Does the engine require columns to be declared in a specific order in TableSchema.columns?	Could matter for vector + non-vector mix.	Probably no — TableSchema.columns is a dict, dict ordering preserved in Python 3.7+ — but verify.

None of these block the Scenario A verdict.

---

6. Effort + cost summary (pre-spike vs post-spike)

Item	0.9-spike-plan estimate	Post-S1 refinement
S1 spike itself	3-5 days foreground	Closed in S230 (~30 min source-code analysis + ~30 min harness scaffolding + report). Time-box overrun argument fails — the gate question was always answerable from source.
Phase 2 architecture-impl	6-8 weeks (Scenario A path)	Unchanged. Same path.
Phase 2 first-step	n/a	Run the prepared harness (probe_managed_by_user.py) to empirically verify trigger + CHECK + GENERATED + RLS behaviour against an isolated PG before wiring content_items for real. ~0.5-1 day.
Fall-back to Phase 0.7 Stream 2 (Scenario B)	9-12 weeks	Not needed. Scenario A confirmed.

Liam’s S230-start framing applies here directly: the spike-plan budget of 3-5 days was the worst-case-Scenario-B contingency, not the actual spike effort. Real effort: < 1 hour.

---

7. Confidence

Dimension	Confidence	Reason
managed_by="user" skips DDL emission	99%	Verbatim docstring + statediff source + every DDL call traced to a gated path
Existing 75-column content_items + all constraints survive	98%	Engine has no constraint-aware code path; row writes go through asyncpg unchanged
pgvector vector(1024) compatible	95%	_vector_encoder produces standard pgvector text format; KH already on pgvector 0.8.0
GENERATED ALWAYS columns work via TableSchema omission	90%	Engine only writes declared cols; need to verify the engine’s INSERT statement is column-list-explicit (not INSERT INTO t VALUES (...)) — likely yes but unverified
Triggers fire on engine upserts	97%	Standard Postgres semantics + engine uses INSERT ON CONFLICT
CHECK violations bubble correctly	95%	asyncpg native error semantics
RLS / role coupling clean	80%	Service-role-equivalent is straightforward; per-workspace-role pattern would need design
Live test will replicate source-code findings	85%	Source is canonical; only edge case might be the engine’s exact INSERT statement shape on GENERATED columns
Overall verdict (Scenario A confirmed)	96%	Source-code-conclusive; live test is a verification step, not a decision step.

---

8. Phase 2 first-step checklist (delivered with this spike)

When Phase 2 begins:

1. Create Supabase branch cocoindex-spike from staging (/opt/homebrew/bin/supabase branches create cocoindex-spike --base staging). Authorised by Liam in S230-start.
2. Set up cocoindex venv per S2 / S14 / S1 patterns: python3 -m venv .venv && .venv/bin/pip install 'cocoindex[postgres]'.
3. Run the prepared harness at spike/cocoindex_s1/probe_managed_by_user.py against the spike branch to verify:
   • Schema bytes unchanged after mount
   • Trigger _spike_s1_test_trigger fired on cocoindex upsert
   • GENERATED column generated_hash auto-computed
   • CHECK violation raised as asyncpg.PostgresError
4. Complete the lifespan / ContextProvider scaffolding pattern (probably 1-2 hours to study cocoindex’s lifespan API surface — cocoindex.lifespan decorator + ContextProvider).
5. Document the empirical results as a §9 addendum to this spike report.
6. Tear down the spike branch (/opt/homebrew/bin/supabase branches delete cocoindex-spike).
7. Begin canonical Phase 2 flow.py at scripts/cocoindex_pipeline/flow.py (or similar). Flow shape per 0.9-intended-architecture.md §10.

---

9. Files + reproducibility

Committed artefacts:

• docs/plans/phase-0-investigation/0.9-spike-S1-cocoindex-schema-coupling.md — this file
• spike/cocoindex_s1/probe_managed_by_user.py — live-test harness (Phase 2 first-step)

Not committed (ephemeral, in spike/cocoindex_s1/):

• .venv/ — cocoindex 1.0.3 + asyncpg isolated install (gitignored via spike/.gitignore if present, or remove spike/ from git tracking via worktree-local .git/info/exclude).

Re-running the source-code probe:

cd <repo>
python3 -m venv spike/cocoindex_s1/.venv
PIP_USER=0 PIP_TARGET="" spike/cocoindex_s1/.venv/bin/pip install 'cocoindex[postgres]'

# Confirm ManagedBy enum:
spike/cocoindex_s1/.venv/bin/python3 -c \
  "from cocoindex.connectorkits.target import ManagedBy; print(list(ManagedBy))"
#   → [<ManagedBy.SYSTEM: 'system'>, <ManagedBy.USER: 'user'>]

# Confirm verbatim docstring:
spike/cocoindex_s1/.venv/bin/python3 -c \
  "from cocoindex.connectors.postgres import declare_table_target; print(declare_table_target.__doc__)"

References:

• 0.9-spike-plan.md §S1 lines 41-113 — spike specification
• 0.8.2-cocoindex-evaluation.md §5.4 — the original framing of Scenario A vs B (with the API-drift caveats per S229 S2)
• 0.9-intended-architecture.md §5 + §10 — Phase 2 architecture sections that depend on this verdict
• cocoindex/connectors/postgres/_target.py — source of the managed_by API (cocoindex 1.0.3 PyPI)
• cocoindex/connectorkits/statediff.py line 128 — load-bearing short-circuit
• cocoindex/connectorkits/target.py — ManagedBy enum (verbatim 14-line file in §2.2 above)

---

End of spike report. G1 decision-gate verdict: Scenario A confirmed. Phase 2 commits on cocoindex with managed_by="user" mode for all KH-owned tables. Live verification deferred to Phase 2 first-step using the prepared harness.