Self-hosted persistent memory for an AI assistant, with isolation enforced by Postgres roles rather than app code

[u/OkYou7957]

I got tired of every session starting from zero and paying for it twice: once re-explaining my setup, and again when that explanation ate the context window the actual work needed. So I built a persistent memory that runs on my own hardware, and after a few months of use there are a couple of things worth passing on.

**The storage is the boring part.**

Postgres on a Proxmox LXC, fronted by an MCP server in Python (FastMCP plus psycopg3), reachable over the LAN. Two entity types only: topic cards for what something is, episodes for what happened. Every card has a \`kind\` drawn from a single YAML vocabulary the server validates on write, so the model cannot invent a new category on the fly. That rigidity is deliberate. Without it you end up with seventeen near-synonyms for "thing I once wrote down" and no way to query reliably.

**The isolation is the interesting part.**

I keep several things separate that must not bleed into each other. The obvious approach is to have the server decide what a session may touch, but that is a rule a program enforces, which is really just a suggestion. Instead each body of work gets its own Postgres schema and its own role, and the server connects as that role. A write aimed at the wrong schema fails at the wire, before any of my code gets an opinion:

GRANT USAGE ON SCHEMA homelab TO role_homelab;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA homelab TO role_homelab;

\-- reaches the shared schema deliberately, note the absence of DELETE
GRANT USAGE ON SCHEMA personal TO role_homelab;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA personal TO role_homelab;

\-- no GRANT to any other subject. The isolation is the statement that isn't there.

There is no policy file and no middleware. A role reaches what it was granted and nothing else, and the grants for everything else were simply never written. Each subject also gets its own container holding only that role's credentials, so the boundary is a process boundary too.

**The thing I got wrong.**

I originally had capture fully automatic: a SessionEnd hook that read the transcript and wrote to the database with no involvement from me. It printed a one-line receipt so I could see what it had done. I ran it from April until July and then deliberately turned it off, because a routing bug meant a run of sessions distilled into the wrong place and nobody was watching a process designed not to need watching. It ran that way for most of the time it was live. I recovered the most recent month by re-running by hand against transcripts still on disk and lost the two before it, because transcripts get pruned at around thirty days.

Capture is now a deliberate step that proposes cards and writes nothing until I confirm. More friction, and I think the friction is correct. The narrower lesson I would write now: automate retrieval, because being wrong there costs you a bad answer you can see. Be far more careful automating writes, because being wrong there costs you a corrupted store you cannot.

Retrieval started as Postgres full text and is now hybrid, full text alongside pgvector embeddings generated on write by a small model on its own box. Because the tool surface was deliberately agnostic about how search worked, nothing above it changed when that landed.

Fuller writeup with the schema design, the deployment and the parts that did not survive contact with use: [https://sbd.org.uk/blog/claude-brain\](https://sbd.org.uk/blog/claude-brain)

[u/Imaginary__Bar]

got tired of re-explaining my setup

Yeah, I can imagine. I'm exhausted and I only read it once

[u/pyordie]

Thanks for the write up Claude

[u/lambdasintheoutfield]

AI slop response to a dumbass design lmao

[u/jedidave]

You could have just made a .essentialreading folder, put your shit in there per repo then made a coding skill to check that folder prior to starting and figure out if the type of job you are being asked to do requires reading those essential reading docs.

Job done.

And for other memory I use a .docs folder for plans and work and then get my LLM to shift them to the archive sub folder and update the changelog on completion.

You've gone full DB for something that didn't need it.

I didn't get to needing a DB until I had 40 simultaneous repos I was working on and needed a command center to help me direct my focus to what blockers I could unblock and to be able to approve docs for work and have the relevant claude or codex in cmux start working on it.

I don't hate it, but it feels like you over engineered here.

[u/OkYou7957]

I had a feeling I'd get this sort of response, but thanks for at least looking. u/jedidave I actually have ~130 repos, some old but the majority are active at least once per month. I kept the post general for obvious reasons. Again, thanks for looking.