Build a patient advocate with long-term memory

If you provide a personal detail in one session and open a new conversation, that detail disappears: no bytes carry across independent HTTP requests. Two distinct data structures dictate this behavior: the the weights, fixed during training and shared across all inferences, and the transient context window, assembled fresh from input tokens on each call and discarded upon completion. Long-term memory is an external storage architecture you construct around the model. Every historical detail an agent surfaces is text that your application retrieved from a persistent store and placed back into the active context window.

You will build that persistent storage architecture here. The Patient Advocate from its post supplies the agent. The underlying store is a Postgres database provisioned on Supabase with the pgvector extension, comprising two tables, one search function, and a security hardening script. You will create the database, execute the schema, wire the agent to the endpoint, and trace a single clinical fact through its entire lifecycle: articulated in an initial session, distilled into a structured record upon session termination, embedded as a vector, recalled across two subsequent sessions, superseded by an updated clinical value, and permanently erased. Each transition writes an observable row directly into the database.

Because this agent processes medical data, a core architectural requirement applies: memory must remain strictly siloed per patient and fully deletable by that patient. You will implement both the isolation boundary and the deletion mechanism directly in this project.

the store is two tables and one function

A patient says “metoprolol 25 mg twice daily” on 1 September. A month later, a new session with an empty window is asked “has anything changed with my heart medicine?” For the system to answer accurately, what has to have been written down, and in what form, for the right row to come back, dated, sourced, and marked superseded once the dose went to 50 mg?

The store resolves that requirement through two layers of storage and an active recall path. A transcript table records every turn as it arrives and judges nothing. At session exit, the distiller reads the transcript and produces a distilled line with its tag, date, source, status, and keys, followed by a 768-number embedding of the whole line. When a later turn poses a question, a database function searches those embeddings by cosine similarity inside Postgres, filtered strictly by user_key to isolate the patient’s silo. That search returns a shortlist of ten rows, which the engine re-ranks by keys and dates before the surviving facts land in the window.

The implementation maps to three concrete database objects: adk_sessions, adk_memory_facts, and match_memory_facts(). The raw transcript lives in adk_sessions, which preserves full session state and events as JSON across process restarts without evaluating content. Long-term memory lives in adk_memory_facts, storing each distilled fact alongside its 768-dimensional embedding, metadata tags, dates, source attributions, status flags, and a pointer that directs a retired row toward its replacement. Retrieval executes through match_memory_facts(), which calculates cosine distance inside Postgres while enforcing the user_key boundary. A third table, adk_agent_registry, stores agent configurations for server dispatch and operates outside the memory subsystem; the base schema provisions it automatically.

One statement about this page’s figure and numbers, made once. The figure draws recall as one arrow where the function returns ten rows and the engine re-ranks them by keys and dates before the top rows enter the window. The 768 dimensions, the three tables, the function, and its filter are exact; the fact line in step four is the record format with a shaped example value.

step one: create the project and take two values

Create a new project at supabase.com; the free tier accommodates every requirement of this build. Extract two configuration parameters from the dashboard and write them directly into your local .env file. Navigate to Project Settings, select API, and copy the Project URL into SUPABASE_URL. On that same configuration screen, copy the service role secret key, identifiable by the sb_secret_ prefix, into SUPABASE_SERVICE_ROLE_KEY. Add your Gemini API key alongside them: the distillation pipeline and embedding calculations use this key regardless of which model executes the conversational agent.

The service role secret key bypasses all table-level row security policies. This administrative access allows your server process to read and write records across all user silos. Because of this elevated privilege, store this secret exclusively within the server runtime environment. Never expose it to browser code, mobile clients, or conversational outputs.

step two: run the schema in the SQL editor

Open the SQL Editor in the Supabase dashboard, paste the complete script printed below, and execute it. This script contains the exact database definition, displayed here so you can verify each table, column constraint, and index before writing records to disk.

-- 1. Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- 2. Create the adk_sessions table
CREATE TABLE adk_sessions (
  id TEXT PRIMARY KEY,
  app_name TEXT NOT NULL,
  user_id TEXT NOT NULL,
  state JSONB DEFAULT '{}'::jsonb,
  events JSONB DEFAULT '[]'::jsonb,
  last_update_time BIGINT,
  expire_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 3. Create the adk_memory_facts table (structured memory records:
--    every fact carries its date, source, active/superseded status, and
--    entity index keys alongside the embedding — see lib/memory/README.md)
CREATE TABLE adk_memory_facts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_key TEXT NOT NULL,
  fact TEXT NOT NULL,
  embedding vector(768),
  tag TEXT,
  fact_date DATE,
  source TEXT,
  status TEXT NOT NULL DEFAULT 'active',
  keys TEXT[] NOT NULL DEFAULT '{}',
  superseded_by UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 4. Indexes: vector similarity, entity keys, dates
CREATE INDEX ON adk_memory_facts USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);
CREATE INDEX adk_memory_facts_keys_idx ON adk_memory_facts USING gin (keys);
CREATE INDEX adk_memory_facts_date_idx ON adk_memory_facts (user_key, fact_date);

-- 5. Create an RPC for cosine similarity search
--    NOTE: filter_user_key is REQUIRED (no NULL default). A NULL/omitted key
--    would return every user's facts; forcing the argument keeps memory scoped
--    to a single user_key.
--    If you previously created the older signature, drop it first to avoid an
--    ambiguous overload:
DROP FUNCTION IF EXISTS match_memory_facts(vector, int, text);
CREATE OR REPLACE FUNCTION match_memory_facts (
  query_embedding vector(768),
  filter_user_key text,
  match_count int DEFAULT 10
) RETURNS TABLE (
  id UUID,
  user_key TEXT,
  fact TEXT,
  tag TEXT,
  fact_date DATE,
  source TEXT,
  status TEXT,
  keys TEXT[],
  created_at TIMESTAMPTZ,
  similarity float
)
LANGUAGE plpgsql
AS $$
BEGIN
  IF filter_user_key IS NULL THEN
    RAISE EXCEPTION 'filter_user_key is required';
  END IF;
  RETURN QUERY
  SELECT
    adk_memory_facts.id,
    adk_memory_facts.user_key,
    adk_memory_facts.fact,
    adk_memory_facts.tag,
    adk_memory_facts.fact_date,
    adk_memory_facts.source,
    adk_memory_facts.status,
    adk_memory_facts.keys,
    adk_memory_facts.created_at,
    1 - (adk_memory_facts.embedding <=> query_embedding) AS similarity
  FROM adk_memory_facts
  WHERE adk_memory_facts.user_key = filter_user_key
  ORDER BY adk_memory_facts.embedding <=> query_embedding
  LIMIT match_count;
END;
$$;

-- 6. Create the adk_agent_registry table
CREATE TABLE adk_agent_registry (
  id TEXT PRIMARY KEY,
  yaml_content JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 7. Defense-in-depth: enable Row Level Security on all tables.
--    The server connects with the service_role key, which BYPASSES RLS, so the
--    app keeps working. But with RLS enabled + no permissive policy, a leaked
--    anon/public key cannot read or write these tables. Add explicit policies
--    only if you intend to expose them to non-service-role clients.
ALTER TABLE adk_sessions        ENABLE ROW LEVEL SECURITY;
ALTER TABLE adk_memory_facts    ENABLE ROW LEVEL SECURITY;
ALTER TABLE adk_agent_registry  ENABLE ROW LEVEL SECURITY;

The script executes across seven discrete blocks. Block 1 enables the pgvector extension, provisioning native vector data types and the <=> cosine distance operator inside Postgres. Block 2 builds adk_sessions to persist conversational state and event arrays as structured JSON; its primary key is text rather than a UUID because the engine composes it from the app name, the user id, and the session id, preventing subagents operating within the same session boundary from overwriting adjacent records. Block 3 establishes adk_memory_facts, converting structured extraction schemas into database columns: the textual assertion, a 768-dimensional embedding vector, a category tag, the referenced factual date, the originating source, an active status flag, keyword index arrays, and a foreign key pointer to any superseding record. Block 4 creates three distinct indexes to optimize retrieval across semantic similarity, keyword entity searches, and chronological user queries. Block 5 defines match_memory_facts for cosine similarity queries, making the filter_user_key argument strictly mandatory to prevent queries from returning records across tenant boundaries. Block 6 provisions the agent registry table. Finally, Block 7 activates Row Level Security on all three tables without granting permissive access rules, preventing clients holding the default anonymous public key from reading or modifying database state while permitting the server to operate normally via the service role key.

The dimension count of 768 defined in the table schema and search function must strictly align with EMBEDDING_DIMENSIONS in lib/config.ts. Modifying your embedding model requires dropping and reconstructing the vector column and its IVFFlat index. If you are updating a database deployed with an earlier schema version, execute db/memory_v2.sql to append the necessary structured columns without data loss.

Every column answers a question a later session will ask. A subsequent session starts with an empty context window and must verify when an assertion was made, who provided it, whether newer events superseded it, and how to filter it by topic. You must define these metadata fields in the relational schema before storing records: prompt instructions cannot retrieve structural attributes that the database never persisted.

step three: harden before any real record

Execute db/hardening.sql from the repository within the Supabase SQL Editor. While the initial schema activated Row Level Security, this hardening script explicitly revokes table access and function execution permissions from both the anon and authenticated database roles. This closes the auto-generated HTTP endpoints at the network permissions layer rather than relying solely on the absence of access policies. The server harness evaluates these permissions during initialization and halts deployment if public access remains open, unless explicitly bypassed via configuration.

This hardening step protects external network interfaces, but it does not limit the backend process itself, which connects through the administrative service role key and bypasses row security checks. Because medical records require absolute isolation, tenant separation currently depends on the application layer appending the user_key parameter to every query. The commented template at the conclusion of db/hardening.sql outlines the production architecture: a restricted database role governed by strict database-level policies, ensuring that even software defects in application routing cannot query records across patient boundaries. Review that pattern now and implement it prior to handling production user data.

step four: run a session and end it on purpose

Launch the advocate process from your terminal:

npm run syndicate:advocate

Input three distinct clinical statements representing typical patient communications: an active medication with its prescribed dosage, a specific laboratory metric alongside its recorded date, and a clinical directive attributed to a named physician. Formulate your input using natural conversational phrases, such as “yesterday” or “my cardiologist”. Once submitted, enter exit.

Terminating the session starts distillation. The harness serializes the conversation transcript, submits the text to the distiller alongside the extraction prompt, and formats each extracted assertion into a structured record:

[FACT | date: 2026-09-01 | source: patient | status: active | keys: metoprolol, dose] metoprolol tartrate 25 mg twice daily

The record header provides programmatic metadata: tag classifies the data category, date resolves relative expressions such as “yesterday” into an ISO calendar date, source identifies the reporting entity, status indicates whether the record is clinically active, and keys indexes the terms required for keyword lookups. The pipeline embeds the complete string so that the temporal and attribution tokens participate directly in vector similarity calculations, while concurrently parsing header parameters into discrete database columns.

Open the Supabase Table Editor and inspect adk_memory_facts. The table should contain exactly one row per clinical fact, with conversational pleasantries and filler tokens stripped out entirely. Compare the values stored in fact_date, source, and keys against your terminal input. If a dosage measurement omitted its units or a relative date failed to resolve into a valid calendar string, the distillation step drifted. Manually auditing these rows lets you inspect extraction fidelity directly.

step five: come back and watch recall, then correct it

Initialize a second session and query the medication obliquely, without mentioning the drug by name: “has anything changed with my heart medicine?”. Before the language model processes your prompt, the preload_memory hook computes a query embedding, executes the vector search, and injects the highest-ranked rows into the system context. Because these records populate the active prompt, the generated completion cites the specific dosage along with its recording date and source. This demonstrates the physical retrieval mechanism: the model recalls data because software placed retrieved text into the context window, leaving the weights untouched.

Next, submit an explicit factual correction. State that a named clinician increased the dosage to 50 mg on a specific date. Type exit and inspect the adk_memory_facts table. The original 25 mg row remains stored in the database, updated with its status marked as superseded and its superseded_by column populated with the UUID of the newly inserted row. The new 50 mg entry appears with an active status. The system does not overwrite or destroy historical rows, because clinical audits require an immutable record of historical states. Start a third session and query the historical dosage from the preceding month: the system retrieves the superseded entry and explicitly identifies it as an inactive, historical record rather than current therapy.

Finally, submit a query regarding clinical information you never provided. The system handles this through the silence constraint: when the database search returns an empty result set, the system instructions require the model to state plainly that no records exist. If the model outputs an assertive claim unsupported by a retrieved database row, the system has drifted into confabulation. In that event, evaluate the completion against the retrieval trace and tighten the grounding constraints in your prompt.

step six: keep the store clean, and keep it yours

The repository includes three administrative scripts for managing the memory database:

npm run memory -- list
npm run memory -- list --silo "<key>" --dupes
npm run memory -- delete --silo "<key>" <id> --yes

The first command outputs each user silo alongside its total row count. The second identifies potential semantic duplicates within a specified silo to facilitate administrative review. The third permanently removes a record by its short identifier, requiring an explicit --yes flag to confirm deletion and scoping the query strictly by silo. Maintaining clean storage is an engineering necessity: superseded or invalid rows still occupy candidate slots within the ten records returned by the vector search.

Each silo is keyed by the user_key value, generated from the application name and a unique user identifier. In local terminal runs, this identifier resolves from MELCHIZEDEK_USER_ID, which defaults to local-user. Unless explicitly configured, all terminal runs share this default namespace. Assign an arbitrary string to this environment variable and restart the advocate: the agent accesses an empty database partition, because the filter_user_key condition in match_memory_facts prevents cross-tenant record retrieval. In a production web deployment, your backend server authenticates the user session and injects this identifier via trusted headers, never accepting an unverified identity from client input.

The platform exposes permanent erasure through a server deletion endpoint scoped to the authenticated user’s silo. From the terminal interface, running npm run memory -- delete --silo "<key>" --all --yes drops all rows tied to that partition, while npm run db:purge resets all session tables during local development. Execute this erasure command on your test silo now to verify that your deletion routines function properly before handling real patient data.

the doctrine is the other half of the build

When a query retrieves a patient’s prescription history, the row for 25 mg returns into the context window right beside the row for 50 mg. Nothing in a retrieved row says how to speak it. Without explicit instructions governing generation, the model can state the old dose as current therapy, quote a number without its date, or supply a typical threshold from its weights when the table has none. The system prompt must define how the model handles every record it receives.

The agent configuration provides these instructions through a <memory_doctrine> block. This block enforces four operational rules: provenance, supersession, contradiction, and silence. Alongside them sits an essential clinical boundary that software filters cannot evaluate alone:

Numbers belong to their clinician. A hold-the-dose cutoff or a when-to-call vital appears only as a quote from this patient’s record, attributed to the clinician or document that set it, with its date. A “typical” threshold supplied from general knowledge is a prescription in disguise.

Each doctrine constraint maps directly to the relational schema: provenance is tracked by source and fact_date, supersession is governed by status, contradiction is surfaced when multiple active rows assert competing values, and silence corresponds to an empty query result.

equip yourself with the materials

1. The complete agent, verbatim: patient-advocate-prompt.md.
2. The setup sheet for this project, with the schema, the hardening step, and the six commands in order: patient-advocate-supabase-setup.md.
3. A domain-neutral doctrine to adapt: memory-doctrine-template.md.
4. The module: building memory systems for agents.

apply it to your own ecosystem

This storage architecture extends directly to any domain where facts accumulate over extended intervals and the current operational value takes precedence over historical drafts. An athletic coach tracks client progress metrics. A property management system records equipment servicing dates and technician identities. A business tracks customer operational requirements.

Adapt the advocate architecture to one of these operational domains:

1. Draft the persona and domain block for your system while preserving the core memory doctrine rules verbatim, as those operational constraints apply across all domains.
2. Define a memory_extraction_rules configuration within the syndicate schema containing two explicit guidelines: data points the pipeline must ignore due to rapid decay, and durable commitments the system must persist.
3. Assign records to an explicit user silo upon initial startup, and verify your silo erasure command before writing production data.
4. Execute three simulated sessions across separate simulated intervals, introduce an explicit factual correction during the second run, and query the underlying table following each session.

If the agent misstates a detail, run through the four diagnostic verification questions: was the fact written to disk, was it distilled into self-contained text that reads cold without the original transcript, did the similarity query return it in the candidate shortlist, and was it placed in the context window and still misstated? Inspecting the database tables directly will identify where the pipeline broke down.