# 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)

```sql
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](https://cockroach-memory-agent.vercel.app) → 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

```bash
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

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

### Connect a real CockroachDB cluster

```bash
export CRDB_DATABASE_URL="postgresql://user:pass@cluster.cockroachlabs.cloud: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