# Patient advocate with long-term memory — the Supabase setup sheet

Companion artifact to the project "Build a patient advocate with
long-term memory" at
https://lyceumagents.com/projects/patient-advocate-with-memory/

The agent is `config/agents/examples/patient_advocate.yaml` in the
melchizedek-agents repo (https://github.com/jhwadman/melchizedek-agents),
mirrored verbatim at
https://lyceumagents.com/downloads/patient-advocate-prompt.md.

The SQL in §2 is the schema from the framework's DOCUMENTATION.md §4.2,
reproduced verbatim. If it and the repo's copy ever differ, the repo's
copy is the source of truth.

---

## 1. Two values into .env, nowhere else

1. Create a project at https://supabase.com (free tier).
2. Project Settings → API → Project URL → `SUPABASE_URL`
3. Same page → the secret key (starts `sb_secret_`) → `SUPABASE_SERVICE_ROLE_KEY`
4. `GOOGLE_GENAI_API_KEY` beside them — the distiller and the embedding
   model (`gemini-embedding-001`, 768 dimensions) run on it whichever
   model the agent uses.

The secret key bypasses row-level security. Server environment only —
never a browser, never a phone app, never a chat.

## 2. The schema — paste into the SQL Editor and run

```sql
-- 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;
```

Couplings: `vector(768)` must match `EMBEDDING_DIMENSIONS` in
`lib/config.ts`. An older database gets the structured columns from
`db/memory_v2.sql` (idempotent).

## 3. Harden — before any real record

Run `db/hardening.sql` from the repo in the same SQL Editor. Tier 1
revokes anon/authenticated privileges on the tables and the RPC. The
server logs `✓ DB hardening verified` at boot and refuses a public
deployment without it (override: `ALLOW_UNHARDENED_DB=true`, dev only).

What tier 1 does NOT do: constrain the server. The secret key bypasses
RLS by design; the server's silo is the `user_key` filter. Tier 2 (the
commented template at the bottom of hardening.sql) binds a dedicated
Postgres role to RLS policies so buggy server code cannot cross silos.
Read it now; adopt it before a second real person.

## 4. The six commands, in order

```bash
# 1. a session, ended on purpose — exit triggers distillation
npm run syndicate:advocate
#    tell it: a medication + dose · a lab value + date · a named clinician's instruction
#    then: exit

# 2. look — Table Editor → adk_memory_facts: one row per kept fact
#    read fact_date, source, keys against what you said

# 3. recall — a new session; ask without naming the drug
npm run syndicate:advocate

# 4. correct — tell it the new dose, per whom, on what date; exit
#    → old row status=superseded, superseded_by set; new row active

# 5. hygiene
npm run memory -- list
npm run memory -- list --silo "<key>" --dupes
npm run memory -- delete --silo "<key>" <id> --yes

# 6. erasure — rehearse it on the test silo
npm run memory -- delete --silo "<key>" --all --yes
npm run db:purge      # ALL sessions; development only
```

## 5. The silo

`user_key = {appName}/{userId}`. At the terminal the id is
`MELCHIZEDEK_USER_ID` (default `local-user`). Set it to something opaque
before a second person uses the machine:

```bash
MELCHIZEDEK_USER_ID=test-8f3a2c npm run syndicate:advocate
```

Behind a website: your backend authenticates its user, then sends
`X-User-Id: <opaque account id>` on every A2A request. Never an id the
visitor chooses. Erasure endpoint: `DELETE /memory` with the same
headers → `{ "deleted": N }`. Full rules: `lib/memory/README.md` §4.

## 6. The record format (what the distiller writes)

```
[TAG | date: YYYY-MM-DD | source: <who asserted it> | status: active|historical | keys: k1, k2] record text
```

Tags: FACT, PREFERENCE, DECISION, ACTION, CONTEXT, INSIGHT, CORRECTION,
EPISODE. Dates absolute at write time. Units exactly as stated. A
CORRECTION carries a `supersedes` quote that retires the old row.
Unresolved contradictions are kept as two rows plus a CONTEXT row naming
the conflict.