I removed an arbitrary-SQL RPC from an agent memory adapter

The first version worked. That was the problem.

I was adding Supabase-hosted pgvector support to an agent memory adapter. The
fastest path was tempting: send SQL to a database function, let the function
execute it, and keep the TypeScript side small.

It would have made the demo easy. It also would have turned one convenience
function into a much larger security boundary than the feature needed.

So I removed it.

The replacement uses ordinary PostgREST operations for writes and deletes, and
one purpose-specific RPC for similarity search. This article explains why that
boundary is smaller, what the implementation looks like, and what happened
when I tested it against a real disposable Supabase project.

Why a generic SQL RPC is the wrong abstraction

Vector memory only needs three operations:

1. store documents and embeddings;
2. find the nearest matching documents;
3. delete documents by ID.

An RPC that accepts arbitrary SQL can do all three, but it can also do almost
anything else allowed by its database role. If it is paired with security
definer, a mistake in application code can cross the caller's normal
permissions.

That is a bad trade: a small adapter gets an open-ended execution primitive.

The narrower design is less clever:

• use supabase.from(table).upsert(...) to store vectors;
• use delete().in('id', ids) to delete them;
• expose one RPC whose only job is similarity search;
• give that function fixed parameters and a fixed return shape;
• keep the service-role credential on the server.

Less clever is useful here. The database interface says exactly what the
application is allowed to ask for.

The bounded search function

The complete setup is in the AgentsKit documentation, but this is the important
shape of the function:

create or replace function public.match_agentskit_vectors(
  query_embedding extensions.vector(1536),
  match_count integer default 10,
  match_threshold double precision default 0,
  filter jsonb default '{}'::jsonb
)
returns table (
  id text,
  content text,
  metadata jsonb,
  similarity double precision
)
language sql
stable
security invoker
set search_path = ''
as $$
  select
    vectors.id,
    vectors.content,
    vectors.metadata,
    1 - (vectors.embedding operator(extensions.<=>) query_embedding) as similarity
  from public.agentskit_vectors as vectors
  where vectors.metadata @> filter
    and 1 - (vectors.embedding operator(extensions.<=>) query_embedding) > match_threshold
  order by vectors.embedding operator(extensions.<=>) query_embedding
  limit least(greatest(match_count, 1), 100);
$$;

There are a few deliberate constraints here:

• security invoker keeps the function under the caller's permissions;
• search_path is fixed instead of inherited from the session;
• the caller supplies an embedding, count, threshold and JSON filter—not SQL;
• the result count is clamped between 1 and 100;
• the function returns only the columns the adapter understands.

I also revoke execution from public and anon, then grant it only to the
server-side role used by the integration.

This is not a universal authorization model. Every application still needs its
own RLS policies and tenancy rules. It is simply a better starting boundary
than “send me a SQL string.”

The TypeScript side stays small

The adapter is configured with the Supabase URL and a server-only credential:

import { supabaseVectorStore } from '@agentskit/memory'

const memory = supabaseVectorStore({
  url: process.env.SUPABASE_URL!,
  serviceRoleKey: process.env.SUPABASE_SERVICE_ROLE_KEY!,
})

It implements the same VectorMemory surface as the other backends:

await memory.store(documents)
const matches = await memory.search(queryEmbedding, {
  topK: 5,
  threshold: 0.75,
  filter: { tenantId: 'acme' },
})
await memory.delete(documentIds)

This is the other boundary I care about. Application code depends on the
capability—store, search, delete—not on Supabase-specific calls scattered
through the agent.

Supabase remains a first-class backend. It just does not become a permanent
architectural decision. A team can use local pgvector, Supabase, or another
vector store behind the same contract as its deployment needs change.

That is what “no lock-in” should mean in practice: not pretending providers are
identical, but keeping their differences at an adapter boundary you can inspect
and replace.

I tested the real integration, not only a mock

Mocks proved that the adapter called the expected methods. They did not prove
that the SQL signature, PostgREST payloads, pgvector operators and permissions
worked together.

For that, I created a disposable Supabase free project in São Paulo and ran the
frozen implementation from commit
e47f30cf5a938dcf865e9342a41fcf9d7d378fd1.

The validation used three synthetic vectors and a three-dimensional version of
the schema to keep the fixture understandable. The production example uses
1536 dimensions and should be changed to match the selected embedding model.

The result:

• direct upsert stored all three records;
• similarity search passed on the first attempt;
• the expected records came back in order with scores 1 and 0.993883748801337;
• topK, the similarity threshold and a JSON tenant filter were honored;
• a record belonging to the excluded tenant did not appear;
• direct deletion removed the test records;
• a final filtered search returned zero records.

After the test, I removed the local credential and deleted the disposable
project.

The numbers are not a benchmark. They are integration evidence: the narrow
interface worked end to end under real Supabase behavior.

What this does—and does not—solve

The pattern removes an unnecessary arbitrary-SQL boundary and keeps vector
memory replaceable. It does not make all database access safe automatically.

A production deployment still needs:

• server-only credential handling;
• RLS and grants designed for its actual tenants;
• an embedding dimension that matches its model;
• indexes and performance testing for its data volume;
• a more specific bounded RPC if it needs compound filters or comparison operators.

The important part is that those decisions remain visible. The adapter does
not hide a general-purpose database escape hatch behind a convenient method.

I would like the Supabase community to challenge this design

I opened a proposal in the Supabase GitHub Discussions asking whether this
belongs as an AI integration, a pgvector framework example, or somewhere else
in the documentation:

https://github.com/orgs/supabase/discussions/48752

If you use Supabase for agent memory or RAG, review that proposal and tell me
what is missing—especially around RLS, tenancy or the RPC boundary. A concrete
counterexample is more useful than a star, and I will use the feedback to
improve the open-source adapter and its documentation.

Source:
https://github.com/AgentsKit-io/agentskit/blob/e47f30cf5a938dcf865e9342a41fcf9d7d378fd1/packages/memory/src/vector/supabase.ts

Setup and security guide:
https://github.com/AgentsKit-io/agentskit/blob/e47f30cf5a938dcf865e9342a41fcf9d7d378fd1/apps/docs-next/content/docs/data/memory/supabase-vector.mdx

Disclosure: I created and maintain AgentsKit. I wrote this article from the
public implementation and a live disposable-project validation; the example
links point to the exact commit tested.