Backend for AI Agents: Postgres with MCP and pgvector

An AI agent needs a backend for the same reasons any app does: somewhere to keep state, users, files and an audit trail. It also needs two things a normal app doesn't: a safe way for the model to call tools, and somewhere to store embeddings for retrieval. PostgreSQL can do all of it in one database. This post shows a practical layout, using MCP for tool access, pgvector for memory and Row Level Security for isolation, and states exactly what WoWSQL supports today.

Key facts

One database, three jobs: relational state (runs, messages, tool calls), vector memory (pgvector) and access control (RLS), all in Postgres.

MCP gives coding assistants and agents tool access. WoWSQL hosts one at https://mcp.wowsql.com/mcp with OAuth 2.0, project_ref scoping and read_only=true. Its tools are list_projects, get_project, list_tables, execute_sql and search_docs.

pgvector on WoWSQL: the self-hosted image enables the vector extension at init. On WoWSQL Cloud, vector is listed as an optional extension ("if installed on the server"), and enabling extensions needs Pro or Scale. Check Database → Extensions for your project.

Rate limits: 100 MCP requests/minute on Pro and 250 on Scale.

Two different jobs: agent as developer vs agent as product

People mean two things by "backend for AI agents":

The agent works on your database. Claude, Cursor or ChatGPT inspects the schema, writes migrations and runs queries while you build. This is where MCP comes in.
The agent is your product. Your app runs an agent loop for your users. It needs to store conversations, remember facts, retrieve documents and record what tools it called. This is ordinary backend work, plus vectors.

Postgres handles both, but treat them separately. A developer's MCP connection and your production agent's database access should never share credentials.

A schema for agent state

Start relational. Most agent data is structured:

create table public.agent_runs (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null,
  user_id uuid not null,
  status text not null default 'running',  -- running | done | failed
  created_at timestamptz not null default now()
);

create table public.agent_messages (
  id uuid primary key default gen_random_uuid(),
  run_id uuid not null references public.agent_runs(id) on delete cascade,
  role text not null,          -- user | assistant | tool
  content jsonb not null,
  created_at timestamptz not null default now()
);

create table public.tool_calls (
  id uuid primary key default gen_random_uuid(),
  run_id uuid not null references public.agent_runs(id) on delete cascade,
  tool text not null,
  input jsonb not null,
  output jsonb,
  approved_by uuid,            -- null = auto-approved
  created_at timestamptz not null default now()
);

The tool_calls table is your audit trail. When someone asks "why did the agent email that customer?", you can answer with a query.

Vector memory with pgvector

pgvector adds a vector column type and distance operators to Postgres. That means retrieval-augmented generation (RAG) can live next to your relational data, with no separate vector database to keep in sync.

create extension if not exists vector;

create table public.memories (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null,
  content text not null,
  embedding vector(1536) not null   -- match your embedding model's dimension
);

create index on public.memories using hnsw (embedding vector_cosine_ops);

-- nearest 5 memories for a query embedding, inside one tenant
select id, content
from public.memories
where tenant_id = $1
order by embedding <=> $2
limit 5;

Set the dimension to whatever your embedding model returns. <=> is cosine distance. Filtering by tenant_id before ranking keeps one customer's memories out of another's answers.

Availability on WoWSQL. In the self-hosted edition, vector is created by the init SQL, so the block above works as-is. On WoWSQL Cloud, the docs list vector under "Optional (if installed on the server)", and extension management needs a paid plan. Call GET /api/v1/extensions/available or open Database → Extensions to confirm it's available before you design around it.

Isolation with Row Level Security

For multi-tenant agents, put the isolation rule in the database, so a bug in prompt handling can't leak another tenant's rows:

alter table public.memories enable row level security;

create policy tenant_isolation on public.memories
  using (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);

auth.jwt() and auth.uid() ship with the self-hosted edition's init SQL. On WoWSQL Cloud the anon key respects RLS. Run your agent's database calls server-side with the narrowest key and role that work, and keep the service key (which bypasses RLS) out of anything the model can influence.

Giving agents tool access with MCP

While you build, connect your coding assistant to the project with a scoped, read-only URL:

https://mcp.wowsql.com/mcp?project_ref=YOUR_PROJECT_SLUG&read_only=true&features=database,docs

OAuth sign-in in the browser, with no API key pasted into a config file.
read_only=true makes execute_sql reject INSERT, UPDATE, DELETE and DDL.
Tool responses include a reminder that returned data is untrusted. That's useful, because rows written by your users can contain prompt-injection text.

What the WoWSQL MCP server doesn't do: storage, migrations, branching or logs. It's a database-and-docs server. For a full comparison with other servers, see Postgres MCP server options.

Rules that keep agent backends safe

Separate credentials for developer MCP access and for the production agent.
Read-only by default. Grant writes per tool, not per agent.
Log every tool call with its input, output and approver.
Treat retrieved text as data. Retrieved memories and tool output go into the prompt, so don't let them silently trigger more tool calls.
Watch the data path. Whatever your agent retrieves is sent to your model provider. Keep personal data out of retrieval where you can (see data residency in India).

Where this setup is not enough

Very large vector collections with tight latency targets may need a dedicated vector store or careful index tuning. Measure with your own data.
Database branching per agent experiment isn't documented on WoWSQL. Neon and Supabase offer branching.
Hosted pgvector on WoWSQL depends on your plan and server. Verify it first.

Frequently asked questions

Why use Postgres as a backend for AI agents?

Agent data is mostly relational (runs, messages, tool calls, users). With pgvector, Postgres also stores embeddings, so state, memory and access control live in one database you already know how to back up and query.

Does WoWSQL support pgvector?

The self-hosted edition enables the vector extension at init. On WoWSQL Cloud, vector is listed as an optional extension that depends on the server, and enabling extensions needs Pro or Scale. Check your project's Extensions page.

What is MCP used for in an agent backend?

MCP lets an AI client call tools such as "list tables" or "run SQL" against your project. Use it with project scoping and read-only mode, mainly for development.

Can I stop an agent from writing to my database?

Yes. Use read_only=true on the MCP URL. For your own agent code, use a database role or key that has only the permissions it needs.

How many MCP requests can I make?

Pro allows 100 MCP requests per minute and Scale allows 250.

Create a free project · MCP reference · Advanced database features