# kardex

**Google Sheets for AI agents, as an MCP server.**

Agents are good at reasoning and bad at bookkeeping. Give one a CSV and it will lose rows, invent columns,
misspell a name and then fail to find it, and overwrite what another agent just wrote. kardex is a small
SQLite-backed store where every sheet has a typed, described schema, every row has a stable id and a version,
every write is logged with its author, and lookups are fuzzy with an explicit match status. It ships as a
[Model Context Protocol](https://modelcontextprotocol.io) server, so any MCP client can use it: Claude Code,
Claude Desktop, Cursor, or your own agent.

```
you:    Track vendor invoices. Add INV-001 Acme 1200 due 2026-09-30 unpaid, INV-002 Globex 450 paid, ...
        then tell me what's outstanding and who we owe most.

agent:  kardex_create_sheet  → "Vendor Invoices" (invoice_number, vendor, amount, due_date, status: unpaid|paid|...)
        kardex_append_rows   → 5 rows
        kardex_run_sql       → SELECT vendor, SUM(amount) ... WHERE status = 'unpaid'
        "Outstanding: $4,500 across 4 invoices. Largest creditor: Initech ($2,200)."
```

## Why not just a database or a spreadsheet?

- **Schema is data.** Column descriptions, allowed options and defaults are stored with the sheet and returned
  by `kardex_describe_sheet`, so the next agent knows how to fill a column without being told.
- **Names are messy.** `kardex_resolve_values` and `kardex_find_rows` match across case, accents, spacing,
  punctuation and common misspellings, and return a status: `exact`, `unique_fuzzy`, `ambiguous` or `none`.
  A fuzzy hit is never silently treated as exact.
- **Rows are not entities.** A key column, per-key counts and `latest_by` make event-log style sheets
  (one row per status change) answerable without double counting.
- **Writes are safe.** Optimistic versions on single-row updates; bulk updates and deletes default to a dry
  run and then require the expected match count; empty filters are refused; the SQL tool is read-only.
- **Everything is auditable.** `kardex_history` shows who changed what, with before and after snapshots.

## Quick start

Requirements: Python 3.10+ and [uv](https://docs.astral.sh/uv/) (or pip).

```bash
git clone https://github.com/gauravhans8/kardex.git && cd kardex
uv venv && uv pip install -e ".[mcp]"        # or: python -m venv .venv && .venv/bin/pip install -e ".[mcp]"
.venv/bin/kardex-mcp --help
```

The server speaks MCP over stdio. It takes one option, `--db PATH` (or the `KARDEX_DB` environment
variable), and creates the SQLite file on first start.

### Claude Code

Put a `.mcp.json` in any project folder, using absolute paths:

```json
{
  "mcpServers": {
    "kardex": {
      "command": "/absolute/path/to/kardex/.venv/bin/kardex-mcp",
      "args": ["--db", "/absolute/path/to/your/sheets.db"]
    }
  }
}
```

Open Claude Code in that folder, approve the project server when prompted, and ask: *"What sheets do you
have?"* or *"Create a sheet to track candidates."* Several folders can share one database, or each can have
its own.

### Claude Desktop, Cursor and other clients

Same shape. Add the block above under `mcpServers` in the client's MCP configuration file (for Claude Desktop,
`claude_desktop_config.json`).

### From Python, without MCP

```python
from kardex import SheetStore

store = SheetStore("sheets.db")
store.create_sheet("Hiring Pipeline", [
    {"name": "candidate", "type": "text", "required": True, "description": "Full name"},
    {"name": "email", "type": "text", "description": "Identifies the person"},
    {"name": "stage", "type": "select", "options": ["applied", "screen", "offer", "hired"], "default": "applied"},
], key_column="email")
store.append_rows("Hiring Pipeline", [{"candidate": "Jane Doe", "email": "jane@x.com"}], actor="me")
store.query("Hiring Pipeline", where={"stage": "applied"})
store.resolve_values("Hiring Pipeline", "candidate", "jane do")     # -> status: unique_fuzzy
```

The core package has no dependencies beyond the standard library.

## Tools

All tools are prefixed `kardex_`. Every result is one envelope: `ok`, `resolved` (the sheet and columns
actually used), `data`, `warnings` (facts about the data that may change the meaning of an answer),
`suggestions` (did-you-mean candidates), and `total` / `returned` / `truncated` / `next_offset` on lists.
Failures the agent can fix come back as `isError` with `error.code` in `not_found`, `validation`, `conflict`,
`refused`, `read_only`.

| Discover | Read | Write |
|---|---|---|
| `kardex_list_sheets` (with hygiene flags) | `kardex_query_rows` (filter, sort, page, `latest_by`) | `kardex_create_sheet` |
| `kardex_resolve_sheet` | `kardex_count_rows` (rows or distinct keys) | `kardex_add_column` |
| `kardex_describe_sheet` (schema + column stats) | `kardex_get_row` (with history) | `kardex_update_column` (also renames) |
| `kardex_column_values` | `kardex_run_sql` (SELECT only, `norm()` for text) | `kardex_annotate` (descriptions, aliases, status, key column) |
| `kardex_resolve_values` (match status) | `kardex_history` | `kardex_append_rows` (warns on duplicate keys) |
| `kardex_find_rows` (search all text columns, grouped by entity) | | `kardex_update_row` (`expected_version`) |
| `kardex_find_duplicates` (exact, normalized, fuzzy) | | `kardex_update_rows` / `kardex_delete_rows` (`dry_run`, `expected_count`) |

Column types: `text`, `number`, `integer`, `boolean`, `date`, `datetime`, `select`, `json`. Values are coerced
leniently on write (`"3"` becomes 3, `"yes"` becomes true) and rejected with a specific message when they cannot be.

Filters are ANDed conditions in any of these shapes:

```
{"stage": "offer"}                       equality          {"stage": ["offer", "hired"]}   IN
{"email": null}                          IS NULL           [["score", "gte", 4], ...]      triples
"candidate~~gourav hans"                 fuzzy             "name~=renee dubois"            normalized equality
```

Operators: `eq ne gt gte lt lte in not_in contains starts_with is_null is_not_null normalized_eq fuzzy`.
For OR, joins and aggregates use `kardex_run_sql`; tables are named by sheet slug.

## How it is built

- **Storage.** One SQLite file in WAL mode. Meta tables `_sheets`, `_columns`, `_changes` hold schema and
  history as data; each sheet's rows live in their own table with system columns `_id`, `_version`,
  `_created_at`, `_created_by`, `_updated_at`, `_updated_by`. A deterministic `norm(text)` SQL function
  provides normalized comparison. Schema version is tracked and migrated on open.
- **Matching.** `kardex/normalize.py`: NFKC, strip zero-width and control characters, casefold, strip
  diacritics, collapse punctuation and whitespace. Scoring tiers: exact, normalized, token set, prefix,
  Jaro-Winkler fuzzy, phonetic. Status rules: one exact hit is `exact`; one fuzzy hit clearly ahead of the
  next is `unique_fuzzy`; two within 0.10 are `ambiguous`; nothing above threshold is `none`.
- **MCP layer.** `kardex_mcp/server.py` on the official Python `mcp` SDK (2.x, protocol 2026-07-28 and
  legacy clients). Tool descriptions and server instructions are in `kardex_mcp/descriptions.py` and are
  tuned against the eval below. Every tool has read-only, destructive and idempotent annotations and a strict
  input schema. Warnings state facts; guidance on what to do with them lives in the descriptions.
- **Design documents.** [docs/mcp-plan.md](docs/mcp-plan.md) explains which agent mistakes each tool exists
  to prevent. [MCP_SERVER_GUIDELINES.md](MCP_SERVER_GUIDELINES.md) is the checklist the server was built against.

## Development

```bash
uv pip install -e ".[dev]"
.venv/bin/python -m pytest                       # 48 tests: store, normalization, MCP layer, trap suite
.venv/bin/python examples/hiring_demo.py         # a scripted multi-agent session against the Python API
.venv/bin/python evals/run_traps.py              # model-in-the-loop eval (spends tokens, see below)
```

### The trap suite

Agents fail in predictable ways: wrong sheet, wrong spelling, counting rows as people, treating one fuzzy hit
as certain, bulk-editing with a filter that matched too much. [tests/fixtures/traps.py](tests/fixtures/traps.py)
seeds a database that contains every one of those traps. [tests/test_traps.py](tests/test_traps.py) drives the
tool path deterministically. [evals/run_traps.py](evals/run_traps.py) runs the real server under a headless
Claude Code session (`claude -p`) per question, grades the tool calls and the answer, and writes transcripts to
`evals/out/`. Use `--suite happy` for the create, load, query and schema-evolution flows, `--only <case>` for one
case. A full run is ten cases and about two dollars of tokens; at the time of writing all ten pass.

### Layout

```
kardex/        core library (store, types, filters, normalize, profile, search)
kardex_mcp/    MCP server (server, descriptions)
tests/              pytest suite and the trap fixture
evals/              model-in-the-loop runner
examples/           demo script
docs/               design plan
```

## Status and roadmap

Works for single-host, multi-agent use today. Not yet done:

- Postgres backend behind the same `SheetStore` interface for multi-tenant deployments.
- Hardened HTTP transport (auth, origin checks). The `--transport streamable-http` flag exists but is
  unauthenticated; do not expose it beyond localhost.
- Idempotency keys on `kardex_append_rows` so client retries cannot double insert.
- FTS5 acceleration for sheets beyond tens of thousands of rows.
- CSV import and export over MCP (available in the Python API).

## License

No license file yet. Add one before publishing.