Building B2B AI Agents with Supabase and PostgreSQL: Architecture and Trade-offs
A practical reference design for tenant-aware retrieval, vector search, durable jobs, and file storage—with the limits and checks teams should plan for.
Key Takeaways
- Postgres with pgvector can simplify relational filtering and retrieval for many B2B products, but a separate vector service may be right when scale, isolation, latency, or operating needs justify it.
- Row-Level Security is one layer of tenant authorization. Its protection depends on correct policies and grants, trusted identity claims, and ensuring privileged server connections do not bypass it.
- For hybrid retrieval, run lexical and vector searches as separate ranked lists and combine the results with a method such as Reciprocal Rank Fusion; do not assume raw similarity scores share a useful scale.
- A durable queue such as pg_boss can persist jobs and retry failures. Design handlers and external side effects to be idempotent because retries can repeat work.
- Object storage can hold original files while Postgres holds searchable text, metadata, and embeddings—but extracted text and vectors can remain sensitive data and need their own access and retention rules.
For many B2B products, PostgreSQL can hold tenant-owned application data, searchable text, vector embeddings, and background-job state without introducing several independently operated databases. Supabase adds managed Postgres services, authentication, and row-level security (RLS); pgvector adds vector similarity search, and a Postgres-backed queue such as pg_boss can persist asynchronous work.
That is a useful starting architecture, not a universal production standard or a security guarantee. It fits best when the team wants relational filtering close to retrieval, the data volume and query load fit the database, and the team can operate the database safely. A dedicated vector service, separate queue, or different object store may be the better choice when a measured requirement calls for one.
This guide uses a B2B support assistant as a running example: it retrieves account documents, drafts a response, and can create a support action after an authorized user confirms it. The same design questions apply to other agents, but the permissions, tests, and failure handling must match the actual workflow.
What does a Postgres-first stack simplify?
Keeping related state in one database can reduce synchronization work. A document record, its tenant ownership, and a retrieval job can share one transactional boundary when the application and queue use compatible connections and transactions. Postgres also lets a query combine vector search with relational filters such as tenant, document status, or access group.
The trade-off is shared capacity and operations. Vector indexes, queue workers, and normal application queries compete for database memory, connections, storage, and maintenance windows. A single database can simplify the architecture while also increasing the impact of a database outage or an overloaded workload. Compare the total operational cost and failure boundaries, not just the number of services.
| Choice | Often fits when | Check before choosing |
|---|---|---|
| Postgres + pgvector | Retrieval needs joins and access filters over product data already in Postgres. | Query plans, filtered recall, index size, connection limits, and concurrent load. |
| Separate vector service | Retrieval needs independent scaling, specialized features, or a separate failure boundary. | Data synchronization, authorization propagation, operational cost, and deletion consistency. |
| Postgres-backed job queue | Job volume and execution patterns fit the database, and atomic state changes are useful. | Worker connection pressure, retry behavior, job retention, and the effect on application queries. |
| Separate queue or workflow service | Workflows need independent capacity, richer orchestration, or a distinct operational boundary. | Additional infrastructure, duplicated state, and how the product recovers across service failures. |
How should tenant access be enforced?
Treat tenant authorization as a layered control. RLS can restrict which rows a database role may read or change, but only if the table grants, policies, identity claims, and connection roles are correct. Supabase's documentation recommends setting grants as well as policies and testing both allowed and denied access. Its secret/service role bypasses RLS, so privileged credentials must remain server-side and privileged code needs its own explicit authorization checks. See the Supabase RLS guide and its notes on securing data and service keys.
Avoid trusting a tenant ID supplied only by the browser or by model output. Derive access from the authenticated user and a trusted membership record, or from an appropriately managed claim. If you use custom JWT claims, understand who can modify them and when they become available in a token. The Supabase custom-claims guide describes that setup.
The following is an illustrative read policy only. It assumes a membership table whose own grants and policies are also reviewed. Add separate, deliberate policies for inserts, updates, and deletes; an update policy generally needs both a condition for existing rows and a check for the resulting row.
ALTER TABLE public.memories ENABLE ROW LEVEL SECURITY;
CREATE POLICY "active members can read tenant memories"
ON public.memories
FOR SELECT TO authenticated
USING (
EXISTS (
SELECT 1
FROM public.tenant_memberships AS membership
WHERE membership.tenant_id = memories.tenant_id
AND membership.user_id = (SELECT auth.uid())
AND membership.status = 'active'
)
);RLS does not make an agent safe by itself. Test the actual database roles and application paths: a user in tenant A must not retrieve tenant B's chunks, create a row for tenant B, or change a row's tenant. Include missing or expired membership, background workers, administrative operations, and any code path that uses a privileged key. Add these cases to repeatable database or integration tests before allowing the agent to access customer data.
How do you combine vector and keyword retrieval?
Vector search helps find semantically related passages; lexical search helps with exact names, identifiers, and phrases. A common Postgres approach is to run each search separately, retrieve a candidate list from each, then fuse the ranks. Supabase's hybrid-search guide shows this pattern with full-text search and pgvector. The pgvector project also describes Reciprocal Rank Fusion (RRF) as one way to combine the results.
RRF combines positions rather than assuming the vector distance and text relevance score have directly comparable scales. A simplified fusion score for one result is the sum of 1 / (k + rank) across the result lists where it appears. Tune the retrieval and fusion against a small, reviewed set of real product questions, relevant passages, and known access boundaries. A high aggregate relevance score must not hide a tenant permission failure.
For indexing, a GIN index can support PostgreSQL full-text search and an HNSW index can support approximate vector search. The distance operator in the query must match the operator class used by the vector index. HNSW trades memory and index-build work for approximate-neighbor performance; it does not guarantee a fixed latency or perfect recall. The pgvector documentation covers hybrid retrieval, HNSW, and filtering behavior.
-- Example index definitions. Choose the operator class
-- that matches the distance operator used in retrieval.
CREATE INDEX memories_embedding_hnsw
ON public.memories USING hnsw (embedding vector_cosine_ops);
CREATE INDEX memories_search_vector_gin
ON public.memories USING gin (search_vector);How should long-running agent work survive failures?
Put long tasks—such as document ingestion, multi-step analysis, or an approved write—in a durable queue instead of relying on a web request or an in-memory timer to remain alive. pg_boss stores jobs in PostgreSQL and supports retries, scheduling, and job-state management. Its job API documentation describes retries and expired work; this is persistence and recovery machinery, not a promise that every handler resumes at the exact point it crashed.
A worker may run a step, lose its lease before recording completion, and then see that step retried. Make handlers idempotent where possible. Use stable idempotency keys with external APIs, store step results and checkpoints in application state, and define dead-letter and operator recovery paths. Keep large documents and sensitive conversation history out of queue payloads; pass a record ID and retrieve authorized data when the worker runs.
For an approval step, store the requested action, actor, decision, and resulting state in the application's database. The UI should authorize the approver and record the decision before the workflow continues. Make the transition safe to retry so a duplicate approval event cannot repeat a consequential action.
A queue that shares the application database reduces the number of services, but it also shares connection and compute limits. Watch queue wait time, worker concurrency, retries, database saturation, and the effect on customer-facing query latency. Move the queue to another system when those measured constraints outweigh the benefit of keeping it in Postgres.
Where should files, extracted text, and embeddings live?
Object storage is usually a better place than relational rows for large original files such as PDFs, audio, and video. A database can hold the file's tenant owner, object key, lifecycle state, retention date, and links to the extracted text and embedding rows. Cloudflare R2 currently lists no egress charge for data transferred directly from R2; storage, operations, compute, and application costs still need to be included in the estimate. Check the current R2 pricing page before making a cost comparison.
Do not treat extracted text or embeddings as harmless metadata. They can reveal source content or sensitive attributes. Apply the same tenant authorization, deletion, retention, encryption, and regional-processing requirements to the original, extracted, cached, and vectorized copies. Use short-lived access paths and retrieve source files only after an authorized workflow needs them.
What should you validate before launch?
- Authorization: test read, create, update, and delete paths for users in different tenants, plus privileged services and missing memberships.
- Retrieval quality: build a small labeled set of representative questions and relevant passages; include exact terms, no-answer cases, stale documents, and adversarial cross-tenant queries.
- Failure recovery: interrupt workers, repeat a job, time out an external API call, and verify that retries do not duplicate writes or external actions.
- Capacity: measure realistic filtered search and queue load with production-like data, then set a budget for latency, cost, storage, and connection use.
- Operations: define alerts, retention, backups, restoration steps, model and prompt change review, and a human fallback for uncertain or consequential tasks.
This architecture is useful when it matches the product's access model and measured workload. Start with the smallest end-to-end workflow, test authorization and recovery before expanding actions, and keep the option to split services when a specific operational limit appears. For related guidance, see our articles on preparing data for AI, evaluating AI agents, and securing agents against prompt injection. If you are choosing an architecture for an existing product, use the AI Cost Estimator as a directional starting point or send us your project details for a scoped discussion.
Frequently Asked Questions
Should I use Supabase and pgvector for a B2B AI agent?
It is a reasonable starting point when the product already uses Postgres, retrieval needs relational filters, and the workload fits the database's measured capacity. Compare it with a separate vector service when you need independent scaling, specialized retrieval features, regional or isolation controls, or latency that your Postgres design cannot meet. Test with representative tenant filters and real data before deciding.
Does PostgreSQL Row-Level Security guarantee tenant isolation?
No single RLS policy guarantees tenant isolation by itself. RLS can enforce row access inside Postgres when policies, table grants, trusted user identity, and database roles are configured correctly. Privileged connections can bypass RLS, and mistakes in policies or application authorization can still expose data. Test allowed and denied operations for every role and tenant, including the service paths used by workers.
How should I combine vector and keyword search in Postgres?
Run semantic retrieval with pgvector and lexical retrieval with PostgreSQL full-text search as separate ranked queries. Merge their candidate lists with Reciprocal Rank Fusion or another method you evaluate on your own relevance set. Apply tenant authorization to both paths and verify the final results against representative access-control tests.
Will pg_boss resume an AI agent exactly where it stopped after a crash?
A durable queue can persist job state and retry work after a failure, but that does not automatically resume an application workflow at the exact instruction where it stopped. Persist workflow checkpoints and external state explicitly, and make each step safe to retry with idempotency keys or deduplication where possible. Define what happens when a job expires or reaches its retry limit.
Should I store AI documents in R2 and embeddings in Postgres?
That split can work well: use object storage for large original files and Postgres for ownership, access metadata, extracted text, chunks, and embeddings needed for retrieval. Treat text and embeddings as potentially sensitive too. Apply access checks, retention and deletion rules to every copy, and choose storage locations based on data residency and operational requirements.
How do I know whether pgvector is fast enough for production?
Measure it with representative data, query filters, concurrency, and hardware. Compare exact and approximate search for recall and latency, inspect query plans, and include the cost of tenant filtering. HNSW is approximate and has memory and build-time trade-offs; it does not promise a fixed query time. Tune indexes only after you have a baseline and a quality target.
Free Tools
Tell us what you're building — book a free scoping call.
Pick a time that works and walk us through your project — 30 minutes, straight to the point. You leave with a concrete plan, timeline, and cost. No sales pitch — if we're not the right fit, we'll say so.
Keep Reading
Get new playbooks by email
Occasional, no-fluff field notes on building production AI — new guides and tools, straight to your inbox. Unsubscribe anytime.