Created by @claydunker-yalc
Reviewed and merged by the Open Brain maintainer team — thank you for building the future of AI memory!
A pattern for using LLM-extracted metadata to route unstructured text into the correct database tables automatically. One input message becomes writes to four different tables — thoughts, people, interactions, and action_items — based entirely on what the LLM finds in the text.
Note
I'm an elementary school teacher, not a developer. I built this entire system with Claude Code. If I can get it running, you can too. The instructions below are written for people like me.
Before you start, make sure you have:
- A Supabase project with the database tables created (SQL provided below)
- Either an OpenAI API key OR an OpenRouter API key (matches canonical OB1 setup from
docs/01-getting-started.md) for LLM calls and embeddings - Node.js 18+ or Deno installed on your machine
- The
@supabase/supabase-jspackage installed (npm install @supabase/supabase-js)
The routing pattern follows three stages:
Raw text
│
▼
┌──────────────────────────┐
│ LLM Metadata Extraction │ ← Extracts people, action items, topics, type, domain
└──────────┬───────────────┘
│
▼
┌──────────────────────────┐
│ Schema-Aware Router │ ← Reads metadata fields, decides which tables to write
└──────────┬───────────────┘
│
├──→ thoughts table (ALWAYS — the raw capture is never lost)
├──→ people table (IF people are mentioned — find, fuzzy-match, or create)
├──→ interactions table (FOR EACH resolved person — links person ↔ thought)
└──→ action_items table (ONLY IF speaker uses first-person intent)
Decision 1 — Thoughts (always written):
Every input always creates a thoughts row. This is your safety net — raw data is never lost regardless of what else happens.
Decision 2 — People (find, fuzzy-match, or create):
When the LLM extracts a people array, each person goes through a three-pass resolution:
- Exact match — checks name and aliases (case-insensitive). If found, backfills any missing metadata (role, relationship_type) on the existing record.
- Fuzzy match — uses first-name similarity. "Mike" matches "Mike Smith", "Rob" matches "Robert". Same last name alone does NOT match (so "Kristin Dunker" won't match "Rosie Dunker"). Fuzzy matches get flagged for human confirmation.
- First-name collision — catches "Sarah J." vs existing "Sarah Johnson". Also flagged for confirmation.
- No match — creates a new person record.
Decision 3 — Interactions (one per resolved person):
For every person that gets resolved (found or created) with a valid ID, an interactions record is written. This links the person to the original thought and carries the same embedding vector for semantic search.
Decision 4 — Action items (first-person intent only): The LLM is prompted to ONLY extract action items when the speaker commits to doing something themselves: "I need to", "I should", "remind me to". If someone ELSE wants something ("she asked me to", "he needs"), that's an observation — not an action item. This prevents your task list from filling up with other people's requests.
Important
The LLM prompt is the single source of truth for routing. If you change the extraction prompt, you change what gets routed where. Treat it like a schema definition.
📋 SQL: Create all five tables and grant permissions (click to expand)
-- Enable the vector extension for embeddings
create extension if not exists vector;
-- 1. Thoughts table — the raw capture
create table thoughts (
id uuid primary key default gen_random_uuid(),
content text not null,
embedding vector(1536),
domain text default 'personal',
status text default 'active',
source text default 'api',
metadata jsonb default '{}',
created_at timestamptz default now(),
updated_at timestamptz default now()
);
-- 2. People table — your contact graph
create table people (
id uuid primary key default gen_random_uuid(),
name text not null,
aliases text[] default '{}',
relationship_type text,
role text,
status text default 'active',
created_at timestamptz default now(),
updated_at timestamptz default now()
);
-- 3. Interactions table — links people to thoughts
create table interactions (
id uuid primary key default gen_random_uuid(),
person_id uuid references people(id),
note text,
source text default 'api',
embedding vector(1536),
created_at timestamptz default now()
);
-- 4. Action items table — first-person commitments only
create table action_items (
id uuid primary key default gen_random_uuid(),
title text not null,
domain text default 'personal',
source text default 'api',
status text default 'open',
linked_person_id uuid references people(id),
created_at timestamptz default now(),
updated_at timestamptz default now()
);
-- 5. Pending confirmations table — for fuzzy match resolution
create table pending_confirmations (
id uuid primary key default gen_random_uuid(),
type text not null,
payload jsonb not null,
slack_ts text,
status text default 'pending',
created_at timestamptz default now()
);
-- Grant permissions to service_role (required on newer Supabase projects)
grant select, insert, update, delete on table public.thoughts to service_role;
grant select, insert, update, delete on table public.people to service_role;
grant select, insert, update, delete on table public.interactions to service_role;
grant select, insert, update, delete on table public.action_items to service_role;
grant select, insert, update, delete on table public.pending_confirmations to service_role;Run this SQL in your Supabase SQL Editor (Dashboard → SQL Editor → New Query → paste → Run).
✅ Done when: You can see all five tables in the Supabase Table Editor.
Open index.ts and find the two placeholder functions:
1. Replace extractMetadata():
Swap out the throw with your LLM API call. The system prompt (EXTRACTION_SYSTEM_PROMPT) is already defined for you. Send it as the system message and the input text as the user message. Request JSON response format.
2. Replace getEmbedding():
Swap out the throw with your embedding API call. We used text-embedding-3-small from OpenAI (1536 dimensions). If you use a different model, update the vector(1536) in the SQL above to match your model's dimensions.
Tip
You can use OpenRouter as a proxy to access multiple LLM providers with one API key. That's what I use — it lets me swap models without changing code.
const OPENAI_KEY = "sk-...";
async function extractMetadata(text: string) {
const response = await fetch("https://api.openai.com/v1/chat/completions", {
method: "POST",
headers: { Authorization: `Bearer ${OPENAI_KEY}`, "Content-Type": "application/json" },
body: JSON.stringify({
model: "gpt-4o-mini",
response_format: { type: "json_object" },
messages: [
{ role: "system", content: EXTRACTION_SYSTEM_PROMPT },
{ role: "user", content: text },
],
}),
});
return JSON.parse((await response.json()).choices[0].message.content);
}
async function getEmbedding(text: string): Promise<number[]> {
const response = await fetch("https://api.openai.com/v1/embeddings", {
method: "POST",
headers: { Authorization: `Bearer ${OPENAI_KEY}`, "Content-Type": "application/json" },
body: JSON.stringify({ model: "text-embedding-3-small", input: text }),
});
return (await response.json()).data[0].embedding;
}You can also use environment variables for the key and URL rather than hardcoding them — whatever works for your runtime.
If you set up OpenRouter in docs/01-getting-started.md Step 4, you already have everything you need. Replace the extractMetadata() function in your index.ts with this:
const OPENROUTER_KEY = process.env.OPENROUTER_API_KEY; // or wherever you store secrets
async function extractMetadata(text: string) {
const response = await fetch("https://openrouter.ai/api/v1/chat/completions", {
method: "POST",
headers: { Authorization: `Bearer ${OPENROUTER_KEY}`, "Content-Type": "application/json" },
body: JSON.stringify({
model: "openai/gpt-4o-mini",
response_format: { type: "json_object" },
messages: [
{ role: "system", content: EXTRACTION_SYSTEM_PROMPT },
{ role: "user", content: text },
],
}),
});
return JSON.parse((await response.json()).choices[0].message.content);
}
async function getEmbedding(text: string): Promise<number[]> {
const response = await fetch("https://openrouter.ai/api/v1/embeddings", {
method: "POST",
headers: { Authorization: `Bearer ${OPENROUTER_KEY}`, "Content-Type": "application/json" },
body: JSON.stringify({ model: "openai/text-embedding-3-small", input: text }),
});
return (await response.json()).data[0].embedding;
}Key differences from OpenAI direct:
- Base URL:
https://openrouter.ai/api/v1instead ofhttps://api.openai.com/v1 - Model strings:
openai/gpt-4o-miniandopenai/text-embedding-3-small(prefixed with the provider) - Same everything else: Same
Authorization: Bearerheader pattern, same JSON shapes, sameresponse_format: { type: "json_object" }support
This is the exact same provider/config pair the core OB1 MCP server (supabase/functions/open-brain-mcp/index.ts) uses, so if you have OB1 running, these snippets reuse your existing setup.
✅ Done when: Both functions make real API calls and return data instead of throwing errors.
import { createClient } from "@supabase/supabase-js";
const supabase = createClient(
"https://YOUR_PROJECT.supabase.co",
"YOUR_SERVICE_ROLE_KEY"
);Caution
Use the service role key, not the anon key. The anon key has Row Level Security restrictions that will block server-side inserts. Never expose the service role key in client-side code.
✅ Done when: You can run supabase.from("thoughts").select("id").limit(1) without errors.
import { processThought } from "./index";
const result = await processThought(
supabase,
"I need to call Sarah tomorrow about the school fundraiser"
);
console.log(result);
// {
// thoughtId: "uuid-here",
// writes: [
// { table: "thoughts", success: true },
// { table: "people", success: true, details: "Created: Sarah" },
// { table: "interactions", success: true, details: "For: Sarah" },
// { table: "action_items", success: true, details: "call Sarah tomorrow about the school f..." }
// ],
// people: [
// { name: "Sarah", id: "uuid-here", action: "created" }
// ]
// }✅ Done when: You see rows appear in all four tables in the Supabase Table Editor after running the script.
Test these three inputs to confirm each routing path works:
| Input | Expected Tables Written |
|---|---|
"I need to call Sarah tomorrow" |
thoughts + people + interactions + action_items |
"My daughter Poppy has swimming tonight" |
thoughts + people + interactions (no action items — it's an observation) |
"Really interesting article about AI in education" |
thoughts only (no people, no action items) |
✅ Done when: Each test input writes to exactly the tables listed above — no more, no less.
After following all five steps, you'll have a working schema-aware router that:
- Captures every input to the
thoughtstable (nothing is ever lost) - Automatically builds a contact graph in the
peopletable as you mention names - Links every person mention to an
interactionsrecord with a semantic embedding - Only creates action items for things YOU commit to doing (not other people's requests)
- Flags ambiguous name matches for human review instead of guessing
Your Supabase dashboard should show data flowing into all four tables, with proper foreign key relationships between people, interactions, and action_items.
You haven't run the SQL from Step 1 yet, or you ran it in the wrong Supabase project. Double-check that you're looking at the correct project in your Supabase dashboard, then re-run the SQL in the SQL Editor.
Warning
If you have multiple Supabase projects, make sure your SUPABASE_URL matches the project where you created the tables. This is the #1 mistake I made — spent an hour debugging before I realized I was pointed at my dev project instead of production.
The extraction prompt expects clear, explicit name mentions. Pronouns like "he" or "she" won't resolve to a person. Try rephrasing: instead of "She wants me to call her", say "Sarah wants me to call her". The LLM is instructed to only extract what's explicitly there.
If you're consistently getting bad extractions, try upgrading your LLM model. gpt-4o-mini works well for this. Smaller or older models may struggle with the structured JSON output.
The extraction prompt has very specific rules about first-person intent. Check that you haven't modified the EXTRACTION_SYSTEM_PROMPT. The key line is:
"If someone ELSE wants something ('she wants', 'he asked', 'they need') that is NOT an action item"
If you've customized the prompt, make sure this rule survived your edits.
The namesAreSimilar() function intentionally has conservative matching — it only looks at first names. If "Mike" and "Michael Smith" aren't matching, it's because the first name "Mike" doesn't contain "Michael" (it goes the other direction). You may want to adjust the fuzzy logic for your specific use case, but be careful: too aggressive and you'll merge different people; too conservative and you'll create duplicates.
Tip
Check the pending_confirmations table in Supabase. If fuzzy matches are being flagged there but never resolved, that's your queue of ambiguous matches waiting for human review. Build a simple UI or bot command to process them.
If you switched from text-embedding-3-small (1536 dimensions) to a different model, you need to update the vector(1536) in the SQL schema to match. For example, text-embedding-3-large uses 3072 dimensions. Drop and recreate the tables with the correct dimension, or alter the columns:
📋 SQL: Change embedding dimensions (click to expand)
alter table thoughts alter column embedding type vector(YOUR_DIMENSION);
alter table interactions alter column embedding type vector(YOUR_DIMENSION);Built by Clay Dunker (@claydunker-yalc) — an elementary school teacher who builds with Claude Code. This pattern emerged from building a personal knowledge management system (Open Brain / OB1) that captures thoughts from Slack and routes them into a structured database.
If you want to learn more about the project, check out the main OB1 repository.