CockroachDB MCP Server with FastAPI LLM-ready CLI

Overview

This project provides a lightweight Model Context Protocol (MCP) server built with FastAPI and CockroachDB. It stores arbitrary JSON documents in a JSONB-backed table, exposes REST endpoints for ingestion and retrieval, and includes a small command-line utility to prepare content for LLM consumption. The server is intended as a simple, production-ready starting point for applications that need to persist structured context for use with language models or other AI services.

Using CockroachDB’s PostgreSQL-compatible JSONB column gives flexible schema-less storage with ACID guarantees and distributed scaling. The included schema bootstrapping, optional debug endpoints, and an LLM-aware CLI make it easier to integrate document ingestion, querying, and prompt construction into existing pipelines.

Features

• FastAPI-based REST API for document ingestion and retrieval
• CockroachDB (PostgreSQL-compatible) storage using JSONB columns
• Schema bootstrapping SQL for quick setup
• LLM-ready CLI to batch-ingest files or export context payloads
• Optional /debug endpoints for health and schema inspection
• Simple JSON document model with metadata and timestamps
• Docker-friendly runtime and environment configuration

Installation / Configuration

Clone the repository, create a virtual environment, install dependencies, set environment variables, bootstrap the schema, and run the server.

1. Clone and install

git clone https://github.com/viragtripathi/cockroachdb-mcp-server.git
cd cockroachdb-mcp-server
python -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt

2. Configure environment variables

Create a .env file or export variables directly. Example env values:

DATABASE_URL="postgresql://user:password@hostname:26257/mcp_db?sslmode=require"
APP_HOST="0.0.0.0"
APP_PORT="8080"
DEBUG="false"

Common environment variables

3. Bootstrap the schema (SQL)

You can run the provided SQL file with psql-compatible tools:

-- bootstrap-schema.sql
CREATE TABLE IF NOT EXISTS mcp_documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  content JSONB NOT NULL,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_mcp_documents_created_at ON mcp_documents (created_at);

Run bootstrap:

psql "$DATABASE_URL" -f bootstrap-schema.sql

4. Start the server

Using uvicorn:

uvicorn app.main:app --host ${APP_HOST:-0.0.0.0} --port ${APP_PORT:-8080} --reload

Or run in Docker (example):

docker run -e DATABASE_URL="$DATABASE_URL" -p 8080:8080 ghcr.io/your/repo:latest

Available Tools / Resources

• FastAPI endpoints (typical routes)

  • POST /ingest — accept JSON document(s) or file uploads to store as JSONB
  • GET /documents/{id} — fetch a stored document by UUID
  • GET /search?key=value — simple metadata-based filtering
  • GET /export/{id} — export a document as a ready-to-use LLM context payload
  • Optional /debug/health and /debug/schema — visibility into server status and DB schema (toggle via DEBUG)

• CLI utility (scripts/cli.py)

  • Ingest a directory of JSON/markdown files
  • Export selected documents into JSONL or combined prompt context
  • Example invocations:

# Ingest files from a directory
python scripts/cli.py ingest --dir ./docs --batch-size 50 --metadata '{"source":"docs"}'

# Export documents to JSONL for model consumption
python scripts/cli.py export --query 'metadata->>source = ''docs''' --out context.jsonl

• SQL bootstrap script: bootstrap-schema.sql
• Example Postman/HTTPie/cURL snippets included in repo for quick testing

Use Cases

1. LLM context management for retrieval-augmented generation

   • Ingest knowledge base pages or product manuals as JSONB documents.
   • Export a matched set of documents as a context array for passing to a language model API (e.g., combined into a prompt or uploaded to an embeddings/vector pipeline).

2. Structured file storage for applications

   • Store and query metadata-rich payloads (e.g., events, enriched logs, or parsed documents) without a rigid schema.
   • Use metadata queries to select relevant context slices for downstream processing.

3. Lightweight content hub for microservices

   • Multiple services can push JSON documents to a shared CockroachDB-backed MCP server.
   • Use the CLI and REST endpoints to create reproducible export workflows for model inference, testing, or debugging.

Examples

Ingest a document with curl:

curl -X POST "$HOST:$PORT/ingest" \
  -H "Content-Type: application/json" \
  -d '{"content": {"text":"Example doc"}, "metadata": {"source": "example"}}'

Retrieve a document by id:

curl "$HOST:$PORT/documents/0a1b2c3d-4e5f-6a7b-8c9d-0e1f2a3b4c5d"

Export documents for an LLM:

python scripts/cli.py export --query 'metadata->>source = ''example''' --out examples.jsonl

Notes and Best Practices