-- Autonomous memory — Postgres schema (v2: layered tier scoping).
--
-- Two-tier canonical-document model:
--
--   memory/agent/*   → shared across every user of the agent (user_id = NULL)
--   memory/user/*    → private to a specific caller (user_id = <callerId>)
--
-- Uniqueness key is `(agent_id, user_id, path)` with NULLS NOT DISTINCT
-- so two NULL user_id rows on the same path collide (exactly-one
-- agent-tier row per path) while per-user rows on the same path coexist
-- (one row per user for user-tier paths).
--
-- The read path filters with `(user_id IS NULL OR user_id = $caller)`,
-- so callers see agent-tier rows + only their own user-tier rows —
-- never another user's private memory. Enforcement is in SQL, not just
-- the UI.
--
-- NULLS NOT DISTINCT requires PostgreSQL 15+. Host's production
-- pgvector image is PG16, so this is safe. The migration in
-- `migrate.ts` will drop the legacy `(agent_id, path)` constraint
-- before creating the new one if it exists on an older database.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE IF NOT EXISTS agent_memories (
  id           BIGSERIAL   PRIMARY KEY,
  agent_id     TEXT        NOT NULL,
  user_id      TEXT,                                -- NULL = agent-tier, shared
  path         TEXT        NOT NULL,
  content      TEXT        NOT NULL,
  embedding    VECTOR(1024),
  tsv          TSVECTOR    GENERATED ALWAYS AS (to_tsvector('english', content)) STORED,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_user_id TEXT,                                -- latest session to append
  CONSTRAINT agent_memories_agent_user_path_uq
    UNIQUE NULLS NOT DISTINCT (agent_id, user_id, path)
);

-- Primary lookup index for list/search/generate-prompt — matches the
-- exact filter shape of every read query.
CREATE INDEX IF NOT EXISTS agent_memories_scope_idx
  ON agent_memories (agent_id, user_id, path);

-- Vector ANN index — untouched by the scoping change.
CREATE INDEX IF NOT EXISTS agent_memories_vector_idx
  ON agent_memories USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

-- Full-text GIN index — untouched.
CREATE INDEX IF NOT EXISTS agent_memories_tsv_idx
  ON agent_memories USING GIN (tsv);
