Step 2 · The database

Step 2 of 8The database

Objective

Create the three tables the agent writes to, and understand why the schema looks like this.

Why this step exists

The database isn’t storage bolted on at the end — it’s the message bus between the agent and the UI. The agent writes rows; the browser reads rows. That’s the entire mechanism behind the live “Agent is searching…” display.

Get this right and step 7 is almost free.

Run the schema

Open your Supabase project → SQL Editor → New query. Paste all of database/schema.sql and hit Run.

What you just created

runs — one row per question

create table if not exists runs (
  id          uuid primary key default gen_random_uuid(),
  thread_id   uuid not null default gen_random_uuid(),  -- the conversation
  query       text not null,
  status      text not null default 'queued',   -- queued | running | done | error
  result      text,                             -- the final answer
  error       text,                             -- why it failed, if it failed
  messages    jsonb,                            -- the conversation, for the model
  provider    text,                             -- who generated it: google, ...
  model       text,                             -- ...and which model
  created_at  timestamptz not null default now(),
  updated_at  timestamptz not null default now()
);

A run also belongs to a thread. One question is one run; a conversation is several runs sharing a thread_id, and messages is that conversation in the shape the model wants it back — which is the entire mechanism behind “the agent remembers what we were talking about”. provider and model record who generated each answer, so a run still tells the truth about itself after you’ve switched keys.

This is the job. The API creates it instantly and returns its id; the agent fills it in later from a background task. The client polls this row until status is done or error.

steps — the agent’s thought process

create table if not exists steps (
  id          bigserial primary key,
  run_id      uuid not null references runs(id) on delete cascade,
  seq         int  not null,                    -- 1, 2, 3... display order
  label       text not null,                    -- shown in the UI
  detail      text,                             -- extra info
  created_at  timestamptz not null default now()
);

create index if not exists steps_run_seq_idx on steps (run_id, seq);

This is what makes an agent feel alive. Every row becomes a line in the UI.

It’s also your production debugger: when a run goes wrong on a server you can’t attach a breakpoint to, you can still read exactly how far it got.

The index matters — the client asks “give me the steps for this run, in order” every 1.5 seconds.

memories — long-term memory, for later

create extension if not exists vector with schema extensions;

create table if not exists memories (
  id          bigserial primary key,
  content     text not null,
  embedding   vector(768),        -- ⚠️ see below
  created_at  timestamptz not null default now()
);

Nothing writes to this yet. It’s here so pgvector is ready when you add RAG — see Learn · Memory and RAG.

The dimension gotcha

768 must exactly match your embedding model’s output dimensions. Different models output different sizes. Mismatch it and you get an error at insert time — or worse, you build the whole table on the wrong number and have to migrate later.

Check your model’s output size first, then write that line.

Security: default-deny

alter table runs  enable row level security;
alter table steps enable row level security;

Supabase gives you two kinds of key. The publishable key (sb_publishable_…) is safe in a browser and restricted by Row Level Security policies. The secret key (sb_secret_…) bypasses RLS completely and is what our server uses.

By enabling RLS and writing no policies at all, anyone holding the public publishable key gets nothing. Our backend still works because the secret key ignores RLS.

If your project predates the rename you’ll see anon and service_role instead — the same two roles, the same model. They work until Supabase retires them at the end of 2026.

That’s the guardrail: the browser can’t reach your data even if someone reads your frontend source.

Expected result

Open the Table Editor. You should see runs, steps, and memories.

Then restart your server and check http://localhost:8000/health:

{"ok": true, "database": true, "provider": "google",
 "model": "gemini-flash-latest", "web_search": false}

database is now true — it was false before, because there was nothing to query.

What usually goes wrong

??? failure “extension \"vector\" is not available”

Rare on Supabase, which ships pgvector. If you’re on plain Postgres elsewhere, install the extension first — or just delete the memories block and the two lines referencing it. Nothing else depends on it.

database: false even though the tables exist

Check SUPABASE_URL has no trailing slash and looks like https://abcdefghij.supabase.co. Then confirm you copied the secret key (sb_secret_…), not the publishable one — the publishable key will be blocked by the RLS you just enabled, which is exactly what it’s supposed to do.

Everything worked yesterday, today nothing does

Free Supabase projects pause when they see too little database activity over a 7-day window. Supabase emails a warning first, then a confirmation once it’s paused. Open the dashboard and hit Resume project.

A few requests a day is enough to keep it awake, so ping it daily if the project matters — a paused database looks exactly like a broken app. You have 90 days to restore a paused project before the backup is gone.

What’s next

The database is ready. Now the agent needs something it can actually do.

← Step 1 · Set upStep 3 · Tools over MCP →