Logging conversation history for chatbot post-mortems

A reliable way to log conversation history chatbot postmortem analysis starts with capturing every request, response, and tool call in a structured format. Without that forensic trail, you’re guessing why a session went wrong instead of reading what actually happened.

Step 1: Define a schema for conversation events

Treat each chat turn as an immutable event. A flat JSON object works for most stores and survives schema changes better than a normalized SQL model early on. Avoid the temptation to store the entire conversation as one growing blob—you lose the ability to query a single turn, measure latency per call, or filter out tool noise.

{
  "session_id": "sess_8f2c1a",
  "turn_id": 12,
  "ts": "2024-05-21T14:02:33.123Z",
  "role": "assistant",
  "model": "gpt-4o-mini",
  "content": "Here is your invoice summary...",
  "token_usage": { "prompt": 1203, "completion": 88, "cache_read": 900 },
  "latency_ms": 612,
  "error": null,
  "tool_calls": []
}

Key fields: session_id groups the thread, turn_id orders events, token_usage feeds cost post-mortems, and error captures failures. If you skip token_usage, you lose the ability to attribute spend during a log conversation history chatbot postmortem review. Add a trace_id if you already run OpenTelemetry; correlation across services pays off when the chatbot calls backend APIs.

Version the schema. Put a schema_version integer in every event. When you later add cached_tokens or finish_reason, old rows stay valid and new code knows what to expect.

Step 2: Instrument your chatbot runtime to emit logs

Wrap the LLM call so logging is mandatory, not optional. Below is a Python decorator around an OpenAI-compatible client. It captures the response and writes the event. The wrapper must not block the user-facing path; use a queue or async sink in production.

import time, json, logging, asyncio
from openai import AsyncOpenAI

client = AsyncOpenAI(base_url="https://api.openai.com/v1", api_key="sk-...")

async def log_turn(session_id, turn_id, model, messages):
    start = time.time()
    try:
        resp = await client.chat.completions.create(
            model=model,
            messages=messages,
            temperature=0.2
        )
        content = resp.choices[0].message.content
        usage = resp.usage
        event = {
            "session_id": session_id,
            "turn_id": turn_id,
            "ts": time.strftime("%Y-%m-%dT%H:%M:%SZ", time.gmtime()),
            "role": "assistant",
            "model": model,
            "content": content,
            "token_usage": {
                "prompt": usage.prompt_tokens,
                "completion": usage.completion_tokens,
                "cache_read": getattr(usage, "prompt_tokens_details", {}).get("cached_tokens", 0)
            },
            "latency_ms": int((time.time() - start) * 1000),
            "error": None,
            "tool_calls": []
        }
    except Exception as e:
        event = { "session_id": session_id, "turn_id": turn_id, "error": str(e), "role": "assistant", "model": model }
    logging.info(json.dumps(event))
    return event

If you route through n4n.ai, its per-token usage metering and forwarded provider cache-control hints arrive in response headers; capture those alongside the event so your log conversation history chatbot postmortem includes cache hits without extra instrumentation. The gateway’s automatic fallback when a provider is degraded also means you should log the model actually served, not just the requested one.

Do not log raw API keys or system prompts containing secrets. Redact with a middleware that masks known patterns before the event hits the store.

Step 3: Persist logs to a queryable store

Stdout is fine for dev, but post-mortems need queries. SQLite is enough for a single-node service; swap to Postgres or OpenSearch when you exceed one box. Create an index on session_id and turn_id upfront.

import sqlite3

con = sqlite3.connect("chatlogs.db")
con.execute("""CREATE TABLE IF NOT EXISTS turns (
    session_id TEXT, turn_id INT, ts TEXT, role TEXT, model TEXT,
    content TEXT, prompt INT, completion INT, cache_read INT,
    latency_ms INT, error TEXT)""")
con.execute("CREATE INDEX IF NOT EXISTS idx_sess ON turns(session_id)")

def save(event):
    con.execute("INSERT INTO turns VALUES (?,?,?,?,?,?,?,?,?,?,?)",
        (event.get("session_id"), event.get("turn_id"), event.get("ts"),
         event.get("role"), event.get("model"), event.get("content"),
         event.get("token_usage", {}).get("prompt", 0),
         event.get("token_usage", {}).get("completion", 0),
         event.get("token_usage", {}).get("cache_read", 0),
         event.get("latency_ms", 0), event.get("error")))
    con.commit()

Write the save() call inside log_turn after building the event. Now you can run SELECT * FROM turns WHERE session_id = ? during a log conversation history chatbot postmortem. For high throughput, batch inserts or use a separate writer thread so the network round-trip to the DB never sits in the request path.

Step 4: Correlate multi-turn context and tool calls

Real chatbots call tools. Log the function request and the result as separate events in the same session. This preserves ordering and makes replay deterministic.

{
  "session_id": "sess_8f2c1a",
  "turn_id": 13,
  "ts": "2024-05-21T14:03:01.000Z",
  "role": "tool",
  "model": null,
  "content": "{\"order_id\": 9921, \"status\": \"shipped\"}",
  "token_usage": { "prompt": 0, "completion": 0, "cache_read": 0 },
  "latency_ms": 12,
  "error": null,
  "tool_calls": [{"name": "get_order", "args": {"id": 9921}}]
}

Store the tool_calls array even when empty. It makes replay code trivial: you filter by role and reconstruct the exact message list sent to the model. If a tool timed out, log the exception as an error event with role: "tool" so the post-mortem shows the gap between the assistant’s intent and the missing result.

Step 5: Build a post-mortem replay view

A post-mortem is a timeline. Write a function that pulls a session and prints it for quick inspection.

def replay(session_id):
    cur = con.execute("SELECT turn_id, role, model, content, error FROM turns WHERE session_id=? ORDER BY turn_id", (session_id,))
    for row in cur:
        tid, role, model, content, err = row
        if err:
            print(f"[{tid}] {role} ERROR: {err}")
        else:
            print(f"[{tid}] {role}/{model}: {content[:120]}")

Run replay("sess_8f2c1a") and you get a skimmable transcript. For a deeper log conversation history chatbot postmortem, dump the full JSON to a file and load it in a notebook. Add a flag to export the session as OpenAI-style messages so you can re-run the exact prompt locally and watch where the model diverges.

Step 6: Verify your logging pipeline

You need proof the pipeline works before trusting it. Write a small integration test that drives one session and asserts the rows exist.

def test_logging():
    sid = "test_session_1"
    asyncio.run(log_turn(sid, 1, "gpt-4o-mini", [{"role": "user", "content": "Ping"}]))
    count = con.execute("SELECT COUNT(*) FROM turns WHERE session_id=?", (sid,)).fetchone()[0]
    assert count == 1, "turn was not logged"

Execute it with pytest or a bare python -c. Success looks like: the test passes, the SQLite file grows, and replay() prints the assistant response. If you see missing token_usage or null session_id, fix the instrumentation before shipping.

python -m pytest test_logging.py -q
# PASSED

Beyond unit tests, run a synthetic load script that opens 50 sessions and confirms no events are dropped. Log sampling is acceptable for metrics, but never sample during a log conversation history chatbot postmortem capture—you need every turn.

Step 7: Add retention and access control

Logs contain conversation text—often PII. Set a retention policy at the storage layer. With SQLite, a cron job deleting rows older than 30 days is sufficient for small deployments.

sqlite3 chatlogs.db "DELETE FROM turns WHERE ts < datetime('now','-30 days');"

For larger systems, enforce column-level encryption and scope reads to on-call engineers. A log conversation history chatbot postmortem is only useful if the people debugging can access it without violating compliance. Audit the query log itself; if an engineer pulls a session, record who and when.

Step 8: Wire logs into incident response

When a user reports a broken session, ask for the session_id, not a screenshot. Query the store, run replay(), and read the error field. You will know within minutes whether the model hallucinated, a tool timed out, or the prompt exceeded the context window.

Derive lightweight metrics from the stored turns: median latency per model, error rate by session_id, and cache hit ratio from cache_read / prompt. Those numbers guide capacity planning and prompt refactoring.

The discipline of structured event logging turns random complaints into reproducible debugging. Build the pipeline once, and every future post-mortem becomes a query instead of a witch hunt.