Cockroach Memory Agent

An AI agent with persistent, globally-distributed memory backed by CockroachDB's MCP Server and AWS S3 — built for the CockroachDB × AWS Hackathon.

Live demo: https://cockroach-memory-agent.vercel.app GitHub: https://github.com/0xConsole/cockroach-memory-agent

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:

• 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

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 a standardized protocol — with automatic AWS S3 backups for disaster recovery.

Key Features

1. Persistent Memory — memories survive process restarts, failures, and redeployments
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 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
7. Full-Text Search — search memories by content (SQLite FTS5 demo / CockroachDB ILIKE)
8. Works without a cluster — mock transport simulates the full MCP server so the demo runs anywhere

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.

How It Uses CockroachDB (≥2 tools requirement)

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.
2. LangChain CockroachDB Integration — the architecture is compatible with langchain-cockroachdb for LangChain-native memory backends, enabling drop-in use with LangChain agents.

The 15 CockroachDB MCP Server Tools

Read (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 (5): create_database, create_table, insert_rows, update_rows, delete_rows

How It Uses AWS (≥1 service requirement)

• 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.

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 fallback)
                    ┌─────────┴──────────┐
                    │  SQLite + FTS5     │
                    │  (no-cluster demo) │
                    └────────────────────┘

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',  -- observation|reflection|plan|message
    role         STRING NOT NULL,                     -- user|assistant|system
    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);

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)

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)

Run locally

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

Run the automated demo

curl -X POST http://localhost:8000/api/demo | python -m json.tool

Connect a real CockroachDB cluster

export CRDB_DATABASE_URL="postgresql://user:[email protected]:26257/defaultdb?sslmode=verify-full"
export AWS_S3_BUCKET="my-agent-backups"
export AWS_ACCESS_KEY_ID="..."
export AWS_SECRET_ACCESS_KEY="..."
uvicorn app.main:app --reload

API Endpoints

Method	Path	Description
GET	/	Interactive chat UI
GET	/health	Health check
POST	/api/chat	Send a message to the agent
GET	/api/recall/{session_id}	Recall all memories for a session
GET	/api/search/{session_id}?q=...	Full-text search over memories
DELETE	/api/forget/{session_id}	Delete all memories for a session
GET	/api/cluster	CockroachDB cluster info + topology
POST	/api/backup	Back up memories to AWS S3
GET	/api/audit	Full MCP audit trail
POST	/api/demo	Run the 7-phase automated demo
GET	/mcp/tools	List MCP tools (MCP tools/list)
POST	/mcp/call	Call an MCP tool (MCP tools/call)

What's Real vs Mocked

Component	Demo (no cluster)	Production (with cluster)
CockroachDB MCP Server	Mock transport (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 is not a fake — it 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 (process crashes, deploys, scaling)
• Scales globally (multi-region, low-latency access)
• Provides audit trails (compliance, debugging)
• Backs up automatically (disaster recovery)
• Integrates with frameworks (LangChain, MCP clients)

A platform team would deploy Cockroach Memory Agent as the memory backend for their agent fleet.

License

Apache 2.0

Links

• Live demo: https://cockroach-memory-agent.vercel.app
• GitHub: https://github.com/0xConsole/cockroach-memory-agent
• CockroachDB MCP Server: https://github.com/cockroachdb/cockroachdb-mcp-server
• MCP Protocol: https://modelcontextprotocol.io