Build short- and long-term agent memory with retention and deletion controls

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE agent_memory (
  memory_id uuid PRIMARY KEY,
  tenant_id text NOT NULL,
  subject_id text NOT NULL,
  fact_key text NOT NULL,
  memory_class text NOT NULL CHECK (memory_class IN ('working','preference','account_fact')),
  value_json jsonb,
  encrypted_payload_ref text,
  source_type text NOT NULL,
  source_ref text NOT NULL,
  confidence numeric(4,3) NOT NULL CHECK (confidence BETWEEN 0 AND 1),
  policy_revision text NOT NULL,
  valid_from timestamptz NOT NULL,
  valid_until timestamptz,
  expires_at timestamptz NOT NULL,
  state text NOT NULL CHECK (state IN ('proposed','active','superseded','expired','quarantined','deleted')),
  superseded_by uuid REFERENCES agent_memory(memory_id),
  deletion_event_id text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CHECK (
    state IN ('expired','deleted')
    OR (value_json IS NOT NULL)::integer + (encrypted_payload_ref IS NOT NULL)::integer = 1
  ),
  CHECK (valid_until IS NULL OR valid_until >= valid_from),
  UNIQUE (tenant_id, subject_id, fact_key, valid_from)
);
CREATE TABLE agent_memory_lifecycle (
  event_id text PRIMARY KEY,
  memory_id uuid NOT NULL REFERENCES agent_memory(memory_id),
  tenant_id text NOT NULL,
  subject_id text NOT NULL,
  event_type text NOT NULL CHECK (event_type IN ('proposed','confirmed','corrected','expired','quarantined','deletion_requested','deleted')),
  source_ref text NOT NULL,
  policy_revision text NOT NULL,
  reason_code text NOT NULL,
  value_digest text,
  occurred_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE agent_memory_representations (
  memory_id uuid NOT NULL REFERENCES agent_memory(memory_id),
  target text NOT NULL CHECK (target IN ('postgres','vector','cache','provider_conversation')),
  external_id text,
  state text NOT NULL CHECK (state IN ('active','deletion_pending','deleted','verified')),
  deleted_at timestamptz,
  verified_at timestamptz,
  PRIMARY KEY (memory_id, target)
);
CREATE TABLE memory_release_members (
  release_id text NOT NULL,
  memory_id uuid NOT NULL REFERENCES agent_memory(memory_id),
  tenant_id text NOT NULL,
  included_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (release_id, memory_id)
);
CREATE FUNCTION reject_memory_lifecycle_mutation() RETURNS trigger
LANGUAGE plpgsql AS $$ BEGIN RAISE EXCEPTION 'agent_memory_lifecycle is append-only'; END $$;
CREATE TRIGGER agent_memory_lifecycle_append_only
BEFORE UPDATE OR DELETE ON agent_memory_lifecycle
FOR EACH ROW EXECUTE FUNCTION reject_memory_lifecycle_mutation();
ALTER TABLE agent_memory ENABLE ROW LEVEL SECURITY;
ALTER TABLE agent_memory FORCE ROW LEVEL SECURITY;
ALTER TABLE agent_memory_lifecycle ENABLE ROW LEVEL SECURITY;
ALTER TABLE agent_memory_lifecycle FORCE ROW LEVEL SECURITY;
ALTER TABLE agent_memory_representations ENABLE ROW LEVEL SECURITY;
ALTER TABLE agent_memory_representations FORCE ROW LEVEL SECURITY;
ALTER TABLE memory_release_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE memory_release_members FORCE ROW LEVEL SECURITY;
CREATE POLICY memory_tenant ON agent_memory
  USING (tenant_id = current_setting('app.tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true));
CREATE POLICY memory_lifecycle_tenant ON agent_memory_lifecycle
  USING (tenant_id = current_setting('app.tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true));
CREATE POLICY memory_representation_tenant ON agent_memory_representations
  USING (EXISTS (
    SELECT 1 FROM agent_memory m
    WHERE m.memory_id = agent_memory_representations.memory_id
      AND m.tenant_id = current_setting('app.tenant_id', true)
  ))
  WITH CHECK (EXISTS (
    SELECT 1 FROM agent_memory m
    WHERE m.memory_id = agent_memory_representations.memory_id
      AND m.tenant_id = current_setting('app.tenant_id', true)
  ));
CREATE POLICY memory_release_tenant ON memory_release_members
  USING (tenant_id = current_setting('app.tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true));

curl -sS https://api.openai.com/v1/responses -H "Authorization: Bearer $OPENAI_API_KEY" -H "Content-Type: application/json" -d '{"model":"{{model}}","store":false,"input":[{"role":"developer","content":"Use only supplied task context."},{"role":"user","content":"Summarize the three open actions."}]}' | jq '{id,status,usage}'

{"type":"object","required":["confirmed_facts","open_questions","decisions","side_effects","source_ids","next_action"],"properties":{"confirmed_facts":{"type":"array","items":{"type":"object","required":["fact","source_id","confidence"]}},"open_questions":{"type":"array"},"decisions":{"type":"array"},"side_effects":{"type":"array"},"source_ids":{"type":"array"},"next_action":{"type":"string"}}}

psql {{databaseUrl}} -v ON_ERROR_STOP=1 -c "BEGIN; SET LOCAL app.tenant_id='{{tenantId}}'; INSERT INTO agent_memory(memory_id,tenant_id,subject_id,fact_key,memory_class,value_json,source_type,source_ref,confidence,policy_revision,valid_from,expires_at,state) VALUES ('{{memoryId}}','{{tenantId}}','{{subjectId}}','timezone','preference','{\"timezone\":\"Europe/Warsaw\"}','user-confirmation','ui-confirmation-771',1.000,'memory-policy-v3',now(),now()+interval '180 days','active'); INSERT INTO agent_memory_lifecycle(event_id,memory_id,tenant_id,subject_id,event_type,source_ref,policy_revision,reason_code,value_digest) VALUES ('event-771','{{memoryId}}','{{tenantId}}','{{subjectId}}','confirmed','ui-confirmation-771','memory-policy-v3','explicit_user_confirmation',encode(digest('{\"timezone\":\"Europe/Warsaw\"}','sha256'),'hex')); INSERT INTO agent_memory_representations(memory_id,target,external_id,state) VALUES ('{{memoryId}}','postgres','{{memoryId}}','active'); COMMIT;"