Every database course tells you to give each kind of thing its own table. Customers here, orders there, products over here. I did the opposite, on purpose, and four months later I still would.
Here is the schema, the query that justifies it, and the bill.
The problem: you do not know what a memory is when it arrives
I am building an assistant whose whole job is remembering. Someone says a sentence, it keeps what matters and can find it again later.
Take a perfectly ordinary sentence:
"Saw Dr Guy on Thursday, need another blood test in three months."
What is it?
- A reminder, because there is a deadline.
- A contact, because there is a doctor's name.
- A fact, because the appointment happened.
- A task, because someone has to book the next one.
All four. And which one matters depends on the next sentence, not this one.
With one table per kind, you must decide at write time. Decide early and you are wrong often: half of what people tell a memory fits no box cleanly, and the other half fits several.
There is a worse consequence. Splitting into tables splits your search. Looking for "doctor" becomes four queries against four tables, then you merge and rank the results yourself. A memory never searches inside a category. It searches everything.
The schema
One table. One row per memory. A type column tells them apart.
create table xneuronal.neurons (
id uuid primary key default gen_random_uuid(),
user_id uuid references auth.users(id) on delete cascade,
device_id text,
type text not null check (type in (
'reminder_short', 'reminder_long', 'task',
'memo', 'fact', 'contact'
)),
content text not null,
metadata jsonb not null default '{}',
embedding extensions.vector(1536),
due_at timestamptz,
status text not null default 'active'
check (status in ('active', 'done', 'archived')),
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
constraint neurons_owner_present
check (user_id is not null or device_id is not null)
);
Deliberately poor. content is the memory in the speaker's own words, never rewritten. metadata is a bag whose shape depends on type: checklist items with their checked state, a phone number, an address, the sources of a web search.
Note neurons_owner_present. A row belongs to an account or to a device, never to neither. That single constraint is what makes anonymous use real rather than a demo mode: memories created before signing up are stored in the same table, with the same columns and the same search. Signing up does not import anything, it swaps a device id for a user id on rows that already exist.
The column that pays for everything
embedding is why the whole bet works.
It holds 1536 floats produced by an embedding model. Individually meaningless; together they place the text in a space where distance means difference in meaning. "The doctor" lands near "Dr Guy" without sharing a single character.
Now the important part: the embedding is computed for every row, whatever its type. So semantic search crosses categories for free:
select id, type, content, 1 - (embedding <=> $1) as score
from xneuronal.neurons
where status = 'active'
and user_id = $2
order by embedding <=> $1
limit 10;
One query. One index. It returns a reminder, a contact and a recipe, ranked by relevance, and nothing in the code had to decide where to look.
With four tables you need four vector indexes, four queries, and a hand-written merge over scores that are not comparable across tables. That is not just more code. It ranks worse.
The indexes tell you the real access pattern
Four indexes, and three of them are partial:
create index neurons_user_active_idx
on xneuronal.neurons (user_id, status, type)
where user_id is not null and status = 'active';
create index neurons_device_active_idx
on xneuronal.neurons (device_id, status, type)
where device_id is not null and status = 'active';
create index neurons_due_at_idx
on xneuronal.neurons (due_at)
where due_at is not null and status = 'active';
create index neurons_embedding_idx
on xneuronal.neurons
using ivfflat (embedding extensions.vector_cosine_ops)
with (lists = 100);
A partial index is a confession about usage: it says most queries only ever touch that slice. Done and archived rows still exist and are still findable, just slower, and that is the correct trade.
The fourth one is different in kind. ivfflat clusters vectors into 100 lists, finds the nearest lists, and only compares inside them. It is an index that agrees to be slightly wrong in exchange for being roughly a hundred times faster. For exact lookups that would be unacceptable. For similarity, where "right answer" is already a matter of degree, the loss is not perceptible.
One caveat worth knowing: ivfflat needs rows to exist before it can build meaningful clusters. Creating it on an empty table and never reindexing gives you a bad index that silently degrades recall.
What it costs
Be honest about the bill.
The database stops guarding meaning. A contact and a reminder have identical columns, so Postgres cannot require a phone number on contacts or a due_at on reminders. Those rules now live in application code, which means they hold exactly as long as your code is correct. That is a real loss, and the usual reason to keep one table per kind.
metadata is where the debt accrues. Anything fits, so anything goes in. Six months later the same fact exists in three shapes depending on which code path wrote it. Ask me how I know.
The only discipline left is that check on type. It is not much. It is also the thing that stops the whole table dissolving into a soup.
The honest framing: I traded per-kind rigour for whole-set search. For a memory, search is the product. For an invoicing system the answer would flip, and it would be just as correct.
A thirty-eight minute detour: get out of public
Thirty-eight minutes after creating the table I moved it, unchanged, into a schema named after the product.
If you run Postgres behind PostgREST, anything in public is exposed automatically. Row-level security still filters who reads what, and you absolutely still need it, but the table's existence is discoverable at a predictable URL. In the default configuration, creating a table publishes its name.
That is not a vulnerability, it is a design choice that helps most of the time. The problem is the direction of the default: doing nothing exposes you, and you have to act to be private.
create schema xneuronal;
alter table public.neurons set schema xneuronal;
Then expose xneuronal explicitly and leave public empty. A table someone forgets in a corner is now invisible from outside. On the client side you pay for it by naming the schema on every call:
supabase.schema('xneuronal').from('neurons')
And to be clear, because the confusion is common: a private schema is not a substitute for row-level security. It is not a lock, it is not writing the address on the door. You want both.
Four months on
The table grew. It gained columns I did not foresee, neighbours, rules. Nothing that was added has challenged the noon decision.
type accepts more than six values now. metadata became a genuine subject with its own rules and accidents. What has not moved: one row per memory, one table for all of it, and a search that crosses categories because it never had to care about them.
If you are designing something similar: the question is not "one table or many". It is whether your product's core operation is per-kind or across-kinds. Answer that first, and the schema follows.
I write about how this assistant gets built, mistakes included, at xneuronal.com.
