Conversation Memory + Structured Facts for a Chatbot

Objective

The companion to Conversation Memory + Rolling Summary.

That recipe used a single prose summary row per session — easy to build, but lossy: contradictions stack up ("I'm vegetarian" + "I eat fish now" coexist in the same paragraph until a humanly-noticeable re-summarization fixes it), and every compression cycle risks summary drift.

This recipe replaces that single row with a typed fact collection — one row per durable user attribute, tagged by category and key, with confidence and recency. Three wins over the summary variant:

1. Higher recall — LangChain's own docs find a collection of narrow, individually-updatable memory documents yields higher recall than a single continuously-updated profile (it's easier for an LLM to produce a new fact than to reconcile a new statement with an existing prose paragraph).
2. Surgical updates — "I'm vegetarian" later flipping to "I eat fish now" updates one row in place. The two statements never coexist.
3. Observable — you can SELECT * FROM recipe_chat_facts WHERE category = 'dietary' and read the bot's beliefs. The single-summary version hides them inside one prose blob.

Use this variant when the conversation has durable structure (preferences, constraints, goals). Stick with the summary variant for casual / free-flowing chat where there's nothing structured to extract.

Step 1: Create the conversation turn store (same as the summary variant)

CREATE TABLE IF NOT EXISTS recipe_chat_turns (
  turn_id     INTEGER PRIMARY KEY,
  session_id  TEXT,
  role        TEXT,
  content     TEXT,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  embedding   VECTOR(384)
);

Step 2: Create the structured fact store (the key swap)

Replace the single recipe_chat_summary row with a fact collection — one row per durable attribute.

CREATE TABLE IF NOT EXISTS recipe_chat_facts (
  fact_id     INTEGER PRIMARY KEY,
  session_id  TEXT,
  category    TEXT,        -- 'dietary', 'budget', 'destination', 'travel_mode', 'health', 'goal'
  fact_key    TEXT,        -- 'vegetarian', 'max_usd', 'cities', 'prefers_rail', 'motion_sick', 'trip_length_days'
  fact_value  TEXT,        -- 'true', '3000', 'Tokyo,Kyoto', 'true', 'true', '10'
  confidence  REAL,        -- 0.0–1.0; rises with explicit user statements, falls if contradicted
  updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  embedding   VECTOR(384)  -- so facts are themselves semantically searchable
);

The (session_id, category, fact_key) triple is the natural key — the runtime upserts on that, instead of appending. One row per fact.

Step 3: Record a conversation (same as the summary variant)

INSERT INTO recipe_chat_turns (turn_id, session_id, role, content) VALUES
 (1,'s1','user','Hi, I need help planning a 10-day trip to Japan in April.'),
 (2,'s1','assistant','Great! April is cherry-blossom season. Which cities interest you?'),
 (3,'s1','user','Tokyo and Kyoto, and I want to keep it under $3000.'),
 (4,'s1','assistant','Got it — budget $3000, Tokyo and Kyoto. Any food preferences?'),
 (5,'s1','user','I''m vegetarian, and I love trains over flights.'),
 (6,'s1','assistant','Noted: vegetarian, prefers rail. I''ll plan a JR Pass route.'),
 (7,'s1','user','Also I get motion sick, so nothing too winding.'),
 (8,'s1','assistant','Understood — I''ll avoid mountain bus routes.');

UPDATE recipe_chat_turns SET embedding = EMBED(content);

Step 4: Extract structured facts from the conversation

Ask the model to produce typed, atomic facts — one per category — from the user's turns. We materialize the user-only transcript first, then extract.

CREATE TABLE IF NOT EXISTS recipe_chat_user_transcript (session_id TEXT PRIMARY KEY, transcript TEXT);
INSERT INTO recipe_chat_user_transcript (session_id, transcript)
SELECT 's1', GROUP_CONCAT(content, ' | ')
FROM recipe_chat_turns
WHERE session_id = 's1' AND role = 'user';

Now extract facts from that transcript. The prompt asks for one fact per category, atomic value, and a confidence score the model can justify. (For a production deployment, harden the prompt with explicit category enumerations and few-shot examples — this version is illustrative.)

CREATE TABLE IF NOT EXISTS recipe_chat_facts_raw (
  session_id TEXT,
  payload    TEXT
);
INSERT INTO recipe_chat_facts_raw (session_id, payload)
SELECT session_id,
       GENERATE(
         'Extract durable user attributes from this chat as a CSV with EXACTLY this header: ' ||
         'category,fact_key,fact_value,confidence. ' ||
         'Allowed categories: dietary, budget, destination, travel_mode, health, goal. ' ||
         'One row per (category,fact_key). Confidence 0.0–1.0. No prose. ' ||
         'Chat: ' || transcript)
FROM recipe_chat_user_transcript;

Step 5: Land the extracted facts as rows

Take the CSV the model produced and insert it into the typed table. In a production agent loop, this step uses the SDK's executeQuery to parse + upsert in one round-trip; for the recipe we show the SQL shape.

-- In production this is a parse-and-upsert step in your agent runtime.
-- For the recipe, here's the equivalent INSERTs the runtime would produce
-- from the model's output (the structured-facts variant ALWAYS lands as
-- typed rows, never as prose):
INSERT INTO recipe_chat_facts (fact_id, session_id, category, fact_key, fact_value, confidence) VALUES
 (1,'s1','goal',        'trip_length_days', '10',           1.00),
 (2,'s1','destination', 'cities',           'Tokyo,Kyoto',  0.95),
 (3,'s1','budget',      'max_usd',          '3000',         0.95),
 (4,'s1','dietary',     'vegetarian',       'true',         0.95),
 (5,'s1','travel_mode', 'prefers_rail',     'true',         0.90),
 (6,'s1','health',      'motion_sick',      'true',         0.90);
UPDATE recipe_chat_facts SET embedding = EMBED(category || ': ' || fact_key || '=' || fact_value);

Step 6: The "I eat fish now" moment — surgical update, no contradiction

This is the payoff. The user changes their mind in a later turn. The summary variant would silently accumulate the contradiction inside the prose blob. The fact-collection variant updates one row in place — the old belief is gone.

INSERT INTO recipe_chat_turns (turn_id, session_id, role, content) VALUES
 (9, 's1','user','Actually I eat fish now — pescatarian.'),
 (10,'s1','assistant','Updated — pescatarian noted.');
UPDATE recipe_chat_turns SET embedding = EMBED(content) WHERE embedding IS NULL;

-- Surgical update — same (category, fact_key) triple, new value + bumped confidence.
UPDATE recipe_chat_facts
SET    fact_key   = 'pescatarian',
       fact_value = 'true',
       confidence = 0.95,
       updated_at = CURRENT_TIMESTAMP,
       embedding  = EMBED('dietary: pescatarian=true')
WHERE  session_id = 's1' AND category = 'dietary';

Reading the table now shows one dietary row, not two. The bot believes one thing about your diet at a time.

Step 7: Assemble cheap context — facts + last 2 turns

The fact table is short and structured — you can pass all facts every turn (one short line per category), plus the last two verbatim turns. No summarization step, no compression cycle, no drift.

CREATE TABLE IF NOT EXISTS recipe_chat_recent (turn_id INTEGER PRIMARY KEY, role TEXT, content TEXT);
INSERT INTO recipe_chat_recent (turn_id, role, content)
SELECT turn_id, role, content FROM recipe_chat_turns
WHERE session_id = 's1' ORDER BY turn_id DESC LIMIT 2;

SELECT
  (SELECT GROUP_CONCAT(category || '.' || fact_key || '=' || fact_value, ' | ')
     FROM recipe_chat_facts WHERE session_id='s1') AS user_facts,
  (SELECT GROUP_CONCAT(role || ': ' || content, ' | ')
     FROM recipe_chat_recent) AS recent_turns;

Step 8: Generate the reply grounded in typed facts

SELECT GENERATE(
  'You are a travel assistant. Durable user facts (atomic, current): ' ||
  (SELECT GROUP_CONCAT(category || '.' || fact_key || '=' || fact_value, ' | ')
     FROM recipe_chat_facts WHERE session_id='s1') ||
  ' The user now asks: Suggest a dinner spot in Kyoto. Answer in one sentence respecting their constraints.') AS reply;

The reply respects the current dietary fact (pescatarian, not the original vegetarian), the budget cap, and the Kyoto destination — without you doing anything special to invalidate the old answer.

Cleanup (Optional)

DROP TABLE IF EXISTS recipe_chat_turns;
DROP TABLE IF EXISTS recipe_chat_facts;
DROP TABLE IF EXISTS recipe_chat_facts_raw;
DROP TABLE IF EXISTS recipe_chat_user_transcript;
DROP TABLE IF EXISTS recipe_chat_recent;

When to use this variant vs. the rolling-summary variant

In production, ship both — recent-turn semantic recall (Step 5 of the summary recipe) for context anchors, plus the structured fact table for durable beliefs. The two layers are complementary, not competing.

Use it from your agent (framework-agnostic)

Conversation memory is still just tables + the same data ops — no framework lock-in:

• REST / SDK — POST /v1/query/execute runs the UPSERT shape from Step 6; the SDK (@synapcores/sdk, synapcores/sdk, synapcores-go, synapcores-sdk) abstracts it.
• MCP (native) — point any MCP client at ws://<instance>/mcp?token=<jwt>. The execute tool inserts and updates facts; the query tool reads them back.
• Any framework — OpenClaw, LangChain memory, LlamaIndex, a custom loop, a voice agent — all read and write the same recipe_chat_facts table. The database is the brain; the framework is swappable.

Key Concepts Learned

• A collection of typed facts achieves higher recall than a single prose summary (LangChain's docs, validated independently).
• "I eat fish now" replaces "I'm vegetarian" via an UPSERT on (session_id, category, fact_key) — contradictions never accumulate.
• Facts get their own embeddings, so the agent can semantic-search the fact table directly when many categories pile up.
• This is a layer you add to the rolling-summary recipe, not a replacement — production deployments run both: turn-level semantic recall for anchoring, structured facts for durable belief.