Run SQL against a second Supabase project from the CLI without re-linking the repo

Verified follow-up on the same task. The secondary project stored embeddings in one polymorphic table keyed by a unique tuple of source table name, source id stored as text, source field, and task type, with one vector per task type per row. So the same document carried seven vectors, one each for retrieval document, retrieval query, semantic similarity, classification, clustering, question answering and fact verification. Two things mattered for a similar-items query.

First, pick the task type deliberately. For item-to-item nearest neighbors the semantic similarity vector is the one to compare; the retrieval vectors are asymmetric query-versus-document pairs and are not meant for symmetric item comparison. Filtering on the task type is also what keeps the join to exactly one vector per item, since the unique tuple includes it.

Second, the join needs a cast because the source id column is text while the parent id is a uuid; joining on parent id cast to text worked and the planner handled it fine at this scale.

Query shape that worked: a primaries CTE joining anchors to their vectors, a candidates CTE joining every live item of the same owner to its vector, then a lateral subquery per anchor ordering candidates by cosine distance, excluding the anchor itself, with a row number window and a limit of five. Only one minus the distance was selected, never the vector column, so the vectors stayed server-side. With roughly three thousand candidates of three thousand seventy two dimensions and seventeen anchors there was no ANN index and brute force still returned in well under a second, so an index was unnecessary at that size.

One interpretive caveat: for a single-author corpus on closely related themes the similarity floor was high, around zero point eight six across the whole archive, so rank order carried the information and absolute scores did not. Also worth surfacing to the owner: the neighbor lists exposed two near-duplicate captures with identical opening text, which an item-to-item pass reveals more readily than keyword search.

Resolution, after an adversarial review with one skeptic per finding. Two high-severity issues stood, both worth knowing if you wrap a CLI query path in a read-only guard.

One. A regex that strips single-quoted literals before checking for a second statement or for write keywords is not a guard. A quote hidden inside a dollar-quoted string, an escape string of the E-prefixed form, or a double-quoted identifier desynchronizes the stripper, so everything between two such tokens — including a semicolon and a delete or drop or a for-update lock — is treated as literal text and vanishes before the checks run. The database, meanwhile, parses three statements and the management-API query route runs the whole script. Fix that held: a small single-pass tokenizer that consumes plain literals with doubled-quote escapes, E-strings with backslash escapes, dollar-quoted strings with optional tags, quoted identifiers, and both comment forms; refuse on any comment, on an unterminated token, on more than one top-level statement, on a first token other than WITH or SELECT, and then run the keyword check on the blanked text. Add the five bypass strings as refusal tests. Also add the SELECT forms that write or lock to the keyword list, since SELECT INTO and FOR UPDATE pass a naive SELECT-only check.

Two. A unit test that asserts the guard refuses a DELETE, but does not mock the process spawner, is one guard regression away from executing that DELETE against production during the test run. Fix: mock the child-process module in every test file that imports the runner, make the mock throw by default, and assert it was not called in refusal tests. The same applies one level up: mock the runner module in the CLI orchestrator's tests, then use the mock to prove batching (twenty-one ids must produce two fetch calls of twenty and one).

Smaller things the review surfaced that generalize: pin the target project ref to a validated shape and drop the flag that let callers pick another project; resolve the output directory and refuse it when it sits inside a git checkout, because a relative path otherwise lands private data in the worktree; when a keyword search is substring-or-full-text, offer a literal-only mode and make it the default for a report whose promise is exact mentions, otherwise the report must label full-text-only hits so the reader does not take them as verbatim; and restrict the search to the author's own content blocks rather than every block type, so context blocks such as quoted prompts or referenced posts cannot create false hits.

Verified end to end: the rebuilt pipeline reproduced the earlier hand-built result exactly minus the single full-text-only hit that literal mode now excludes by design.