Cockroach Memory Agent

An AI agent with persistent, globally-distributed memory backed by CockroachDB's MCP Server. Memories survive restarts, scale across regions, and back up to AWS S3.

Comment

The Problem

AI agents are amnesiac. Every conversation starts from scratch. Every process restart loses context. Every scale-out creates new instances that don't share memory. This makes agents unreliable for production use cases:

  • A DevOps agent that monitored incidents yesterday can't reference them today
  • A code review agent that learned your codebase patterns forgets on restart
  • A trading agent that tracked market conditions loses its history on redeploy
  • Multi-region deployments can't share a consistent memory store

Existing solutions (Redis, vector DBs) work for single-region, ephemeral memory. They don't survive process failures, don't scale globally, and don't provide audit trails.

The Solution

Cockroach Memory Agent gives agents a persistent, globally-distributed memory backed by CockroachDB's distributed SQL database. It uses CockroachDB's MCP Server for all database operations, ensuring every memory is stored, retrieved, and audited through the Model Context Protocol — with automatic AWS S3 backups for disaster recovery.

How It Uses CockroachDB (≥2 tools)

  1. CockroachDB MCP Server — all memory operations (create table, insert, select, delete, cluster info, cluster nodes) go through the MCP Server's 15 tools (10 read + 5 write). The MCP client wraps every operation as a tool call with full audit logging. Compatible with Claude Code, Cursor, and VS Code.

Read tools (10): list_databases, list_tables, get_table_schema, get_cluster, list_sql_users, list_cluster_nodes, show_running_queries, select_query, explain_query, show_statement

Write tools (5): create_database, create_table, insert_rows, update_rows, delete_rows

  1. LangChain CockroachDB Integration — the architecture is compatible with langchain-cockroachdb for LangChain-native memory backends, enabling drop-in use with LangChain agents.

How It Uses AWS (≥1 service)

  • AWS S3 — periodic gzip-compressed memory snapshots with backup/restore/list operations. Free tier compatible (5 GB storage, 2,000 PUT, 20,000 GET requests/month). Mock fallback for demo mode when no AWS credentials are set.

Key Features

  1. Persistent Memory — memories survive process restarts, failures, and redeployments (verified in the demo via "Simulate Restart")
  2. MCP Protocol — all DB operations go through CockroachDB's MCP Server (15 tools: 10 read + 5 write)
  3. AWS S3 Backup — periodic gzip-compressed memory snapshots with restore capability
  4. Audit Trail — every MCP tool call is logged with input, output, duration, and timestamp for compliance
  5. Session Isolation — multi-session support with session-scoped memory recall
  6. Multi-Region Topology — CockroachDB's global distribution for low-latency memory access (3 regions: us-east-1, us-west-2, eu-west-1)
  7. Full-Text Search — search memories by content (SQLite FTS5 demo / CockroachDB ILIKE in production)
  8. Typed Memories — memories are typed: observation, reflection, plan, message
  9. Works without a cluster — mock transport simulates the full MCP server so the demo runs anywhere

The Unique Angle

Unlike ephemeral agent memory (Redis, in-process dicts), Cockroach Memory Agent uses CockroachDB's MCP Server for persistent, globally-distributed, audited memory that survives restarts and scales across regions — with automatic AWS S3 backups for disaster recovery.

Architecture

┌─────────────┐     ┌─────────────────┐     ┌──────────────────────────┐
│   AI Agent  │────▶│  MCP Protocol   │────▶│ CockroachDB MCP Server   │
│ (chat/CLI)  │     │  (tools/call)   │     │   (15 tools: 10R + 5W)   │
└─────────────┘     └─────────────────┘     └────────────┬─────────────┘
                                                         │
                              ┌───────────────────────────┴────────────┐
                              │                                        │
                    ┌─────────▼──────────┐              ┌──────────────▼──────────┐
                    │  CockroachDB        │              │   AWS S3                │
                    │  (multi-region,     │              │   (gzip backup          │
                    │   ACID, survivable) │              │    snapshots)           │
                    └────────────────────┘              └─────────────────────────┘

Demo (the 3-minute judge flow)

  1. Open the live demo → see the chat UI.
  2. Type Hi, I'm Alice → agent responds "Nice to meet you, Alice!" (stored via MCP insert_rows).
  3. Click "🔄 Simulate Restart" → memory persists across the restart.
  4. Type What's my name? → agent responds "Your name is Alice — I remember from our conversation." (recalled via MCP select_query).
  5. Click the "MCP Tools" tab → see all 23 MCP tools (8 agent + 15 CockroachDB).
  6. Click the "Audit Trail" tab → see every MCP tool call logged with input/output/timestamp.
  7. Click the "AWS S3 Backup" tab → create a gzip-compressed memory snapshot.
  8. Click "▶ Run Auto-Demo" → runs the full 7-phase demo automatically.

The 7-Phase Automated Demo

  1. Store messages via MCP insert_rows
  2. Simulate process restart → recall (persistence verified)
  3. Ask "What's my name?" → recall works
  4. Search memories for "Python"
  5. AWS S3 backup (gzip compressed)
  6. CockroachDB cluster topology (3 regions)
  7. Full MCP audit trail (100% success rate)

Memory Schema (mirrors CockroachDB DDL)

CREATE TABLE memories (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id   STRING NOT NULL,
    kind         STRING NOT NULL DEFAULT 'message',
    role         STRING NOT NULL,
    content      STRING NOT NULL,
    metadata     JSONB NOT NULL DEFAULT '{}'::JSONB,
    importance   FLOAT NOT NULL DEFAULT 0.5,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_memories_session ON memories(session_id);
CREATE INDEX idx_memories_session_created ON memories(session_id, created_at);
CREATE INVERTED INDEX idx_memories_metadata ON memories(metadata);

Tech Stack

  • Python 3.11 + FastAPI
  • CockroachDB MCP Server (15 tools: 10 read + 5 write)
  • AWS S3 (boto3, gzip-compressed backups, free tier)
  • SQLite FTS5 for demo search (mirrors CockroachDB ILIKE)
  • Vercel serverless (free tier)
  • Static HTML/CSS/JS UI (no framework, fast load)

Setup (<5 commands)

git clone https://github.com/0xConsole/cockroach-memory-agent.git && cd cockroach-memory-agent
pip install -r requirements.txt
uvicorn app.main:app --reload
# Open http://localhost:8000

What's Real vs Mocked

Component Demo (no cluster) Production (with cluster)
CockroachDB MCP Server Mock transport (faithfully simulates all 15 tools) Real MCP server via stdio/HTTP
Memory storage SQLite (file-based, persists across restarts) CockroachDB (distributed ACID)
Full-text search SQLite FTS5 CockroachDB ILIKE
AWS S3 backup In-memory mock Real S3 with boto3
Agent response generation Rule-based (no LLM key needed) LLM (GPT-4, Claude) with memory context

The mock transport faithfully implements the full MCP tool surface so the agent exercises the real MCP integration path. Swapping to a real cluster is a one-line config change (CRDB_DATABASE_URL).

Real-World Usefulness

Production AI agents (DevOps automation, code review, trading, customer support) need persistent memory that survives restarts, scales globally, provides audit trails, backs up automatically, and integrates with frameworks like LangChain and MCP clients. A platform team would deploy Cockroach Memory Agent as the memory backend for their agent fleet.

Links

Built With

Share this project:

Updates

Submission history