cognexdb 0.4.2

Local-first human-readable file database with REST, SQL, CLI, and MCP interfaces.

Cognex

Cognex is a local-first database built for humans, apps, and AI. It turns folders of Markdown, JSON, and CSV files into a queryable backend with SQLite indexing, REST APIs, SQL reads, CLI commands, and MCP tools.

Demo day smoke test:

python -m cognex.demo

After installation, this runs a real end-to-end flow across SDK, files, SQL, AI tasks, imports, functions, sync/CRDT, REST auth, and row policies. See docs/DEMO_DAY.md.

The source of truth stays human-readable:

customers/john.md
customers/sarah.json
orders/order_001.json
products/catalog.csv

Apps can call Cognex like a backend. Developers can run SQL. AI assistants can query and write records through MCP. Humans can still open the files in an editor.

Local-first SDK

Cognex is meant to feel familiar to developers who already use cloud backends such as Supabase, but the default runtime is local files plus a SQLite index. Start with the SDK when you want database, search, sync, and AI-ready human-readable data without making cloud infrastructure the default.

from cognex import create_client

db = create_client("./data")

db.from_("customers").insert({
    "id": "john",
    "name": "John",
    "status": "lead",
}).execute()

leads = db.from_("customers").select("*").eq("status", "lead").execute()
rows = db.sql("SELECT * FROM customers WHERE status = ?", ["lead"]).data

This gives Supabase-style application code while keeping the source of truth in files like customers/john.json. Use table("customers") as an alias for from_("customers") when you prefer a Python keyword-safe name.

AI-native data tasks

Cognex now includes a provider-free AI-native command layer for natural-language database tasks. It creates an inspectable plan first, then safely executes supported operations against local human-readable files.

from cognex import create_client

db = create_client("./data")

# Search records by intent.
result = db.ai_run("Find every customer that bought iPad", collection="customers")

# Inspect before execution.
plan = db.ai_plan("update customers where status = lead set status = active")

# Import a CSV into JSON records.
imported = db.ai_run(
    "Create a CRM from this CSV",
    collection="crm",
    csv_text="id,name,status\nada,Ada,lead\n",
)

The same layer is exposed through REST (/v1/ai/plan, /v1/ai/run), CLI (cognex ai ...), SDK (ai_plan, ai_run), and MCP (ai_run_database_task). It is intentionally deterministic and local-first so AI assistants can automate database work without sending data to a model provider by default.

JavaScript and browser offline SDKs

Cognex includes a fetch-based JavaScript REST client and a browser IndexedDB helper:

import { createClient } from "./sdks/js/cognex.js";

const db = createClient("http://localhost:8080", { apiKey: "dev-secret" });
await db.from("customers").insert({ id: "john", data: { name: "John" } });
const rows = await db.sql("SELECT * FROM customers");

import { createIndexedDbStore } from "./sdks/browser/cognex-indexeddb.js";

const local = createIndexedDbStore();
await local.put("customers", "john", { name: "John" });
await local.sync(db);

Imports, row policies, and local functions

Import Supabase CSV/JSON exports or Firebase/Firestore JSON exports:

./bin/cognex --root ./data import supabase ./customers.csv
./bin/cognex --root ./data import firebase ./firestore-export.json

Add basic collection policies in .cognex/policies.json:

{
  "collections": {
    "customers": {"read": "owner", "write": "owner", "owner_field": "owner"}
  }
}

Add local functions in .cognex/functions/name.py. Functions read JSON from stdin and write JSON to stdout, then can be called with:

./bin/cognex --root ./data function name --data '{"hello":"world"}'

Or through REST at POST /v1/functions/{name}.

Supabase-style capability status

Cognex is aiming for a Supabase-familiar developer experience, but it should not claim every Supabase feature is complete yet. This is the current honest status:

Supabase feature	Cognex status today	How it works / gap
Postgres-style database	✅ Available locally	Human-readable Markdown/JSON/CSV files are indexed into SQLite for queryable app data.
Tables	✅ Available locally	Folders become collections, and files become records.
SQL reads	✅ Available locally	Read-only SELECT/WITH queries run against SQLite materialized collection tables.
Storage	✅ Available locally	Files and folders are the storage layer; JSON/Markdown writes stay human-readable.
SDK	✅ Available locally	Python SDK plus JS REST SDK and browser IndexedDB offline helper are included.
REST API	✅ Available locally / optionally hosted	The /v1/* server can run on localhost or on a host such as Railway with persistent storage.
CLI	✅ Available locally	bin/cognex exposes index, query, CRUD, sync, CRDT, server, and project-intelligence commands.
MCP / AI tooling	✅ Available locally	MCP tools expose project search/context, natural-language data tasks, database query/write, sync, and CRDT operations to AI assistants.
CRDT record merge	✅ Available for structured JSON fields	Operation-based LWW field updates can be exported, imported, materialized, and synced.
Filesystem/Git sync	✅ Available	Data files, manifests, and CRDT operation logs can move through local folders or Git-backed remotes.
Auth	✅ Basic local auth	API-key protection plus local email/password signup, login, logout, and bearer session tokens stored under .cognex/auth.json. OAuth and hosted auth are not included.
Realtime	✅ Basic local realtime	/v1/realtime streams Server-Sent Events for collection snapshots and file-backed record changes.
JavaScript SDK	✅ Basic	sdks/js/cognex.js provides a fetch-based browser/Node REST client.
Dashboard / Studio	✅ Basic local web UI	/dashboard provides a lightweight local web UI for collections, SQL, and JSON record writes.
Row-level security	✅ Basic	.cognex/policies.json supports public/authenticated/owner/admin collection policies.
Functions/plugins	✅ Basic local	.cognex/functions/*.py scripts can be called through REST/CLI as local plugins.

So the accurate V1 claim is: Cognex provides the local-first file database, SQLite SQL reads, REST, CLI, MCP, Python SDK, basic JavaScript/browser SDKs, basic local auth, basic local realtime, a basic local dashboard, basic row policies, local functions/plugins, CRDT operations, and filesystem/Git sync. It does not yet provide full Supabase parity for OAuth/hosted auth, passkeys, PostgreSQL wire compatibility, advanced realtime channels, hosted edge runtimes, or managed multi-region infrastructure.

For deployment decisions and the exact boundary between "usable V1" and "not Supabase-equivalent yet," see Cognex V1 production-readiness status.

Optional REST backend

Run the REST backend locally when another process, browser app, or phone app needs HTTP access to the same local data folder:

PYTHONPATH=src python -m cognex.server --root ./data --port 8080 --api-key dev-secret

Cloud deployment is optional rather than the default. If you later want a hosted endpoint, the included Procfile can run the same local-first backend on platforms such as Railway:

web: PYTHONPATH=src python -m cognex.server --root ${COGNEX_ROOT:-/data} --host 0.0.0.0 --port ${PORT:-8080}

Useful deployment variables:

Variable	Purpose
COGNEX_ROOT	Persistent mounted folder for user data, for example /data.
COGNEX_API_KEY	Bearer token required by the API.
COGNEX_CORS_ORIGIN	Frontend origin, or * while testing.
COGNEX_WATCH_INTERVAL	Background re-index interval in seconds.

Use persistent storage for COGNEX_ROOT so files and SQLite state survive deploys.

REST API

All endpoints accept Authorization: Bearer <COGNEX_API_KEY> when COGNEX_API_KEY or --api-key is set.

Method	Path	Purpose
GET	/health	Health check for Railway and uptime.
GET	/dashboard	Basic local web dashboard for collections, SQL, and JSON writes.
POST	/v1/index	Re-index Markdown, JSON, and CSV files.
POST	/v1/auth/signup	Create a local email/password user and session token.
POST	/v1/auth/login	Create a bearer session token for a local user.
POST	/v1/auth/logout	Revoke the current bearer session token.
GET	/v1/collections	List detected collections.
GET	/v1/transactions	Inspect recent local write transactions and revisions.
GET	/v1/sync/status?remote=/path	Show pending push/pull/conflict state for a sync remote.
GET	/v1/realtime?collection=name	Stream Server-Sent Events for local record snapshots and changes.
GET	/v1/collections/{collection}	List records in a collection.
GET	/v1/collections/{collection}/records/{id}	Read one record.
POST	/v1/collections/{collection}/records	Create a JSON or Markdown record.
PUT	/v1/collections/{collection}/records/{id}	Replace a JSON or Markdown record.
DELETE	/v1/collections/{collection}/records/{id}	Soft-delete a record by moving the file to a .deleted-* backup.
POST	/v1/query	Run read-only SQL against file collections.
POST	/v1/ai/plan	Create an inspectable natural-language data-task plan.
POST	/v1/ai/run	Execute a supported natural-language data task locally.
POST	/v1/import/supabase	Import a Supabase CSV/JSON export from a server-local path.
POST	/v1/import/firebase	Import a Firebase/Firestore JSON export from a server-local path.
POST	/v1/functions/{name}	Run a local Cognex function/plugin.
POST	/v1/search	Full-text search across records.
POST	/v1/sync/push	Push local files to a filesystem/Git sync remote.
POST	/v1/sync/pull	Pull remote files into the local data root.
POST	/v1/crdt/update	Apply CRDT field operations and materialize the merged record.
GET	/v1/crdt/export	Export CRDT operations for another device.
POST	/v1/crdt/import	Import CRDT operations and merge them into local files.

Create a customer:

curl -X POST http://localhost:8080/v1/collections/customers/records \
  -H 'Authorization: Bearer dev-secret' \
  -H 'Content-Type: application/json' \
  -d '{"id":"john","expected_revision":"","format":"json","data":{"name":"John","status":"lead","product":"iPad"}}'

Query it with SQL:

curl -X POST http://localhost:8080/v1/query \
  -H 'Authorization: Bearer dev-secret' \
  -H 'Content-Type: application/json' \
  -d '{"sql":"SELECT * FROM customers WHERE status = ?","params":["lead"]}'

Create a local user session instead of using a shared API key:

curl -X POST http://localhost:8080/v1/auth/signup \
  -H 'Content-Type: application/json' \
  -d '{"email":"[e-mail]","password":"correct horse battery staple"}'

Watch local collection changes with Server-Sent Events:

curl -N http://localhost:8080/v1/realtime?collection=customers \
  -H 'Authorization: Bearer dev-secret'

Open the local dashboard at http://localhost:8080/dashboard.

CLI

Run from a checkout without installation:

./bin/cognex --root ./data collections
./bin/cognex --root ./data upsert customers john --data '{"name":"John","status":"lead"}'
./bin/cognex --root ./data sql 'SELECT * FROM customers'
./bin/cognex --root ./data ai "Find every customer that bought iPad" --collection customers
./bin/cognex --root ./data transactions
./bin/cognex --root ./data sync-status ./remote-sync
./bin/cognex --root ./data sync-push ./remote-sync
./bin/cognex --root ./data sync-pull ./remote-sync
./bin/cognex --root ./data crdt-update customers john --fields '{"name":"John","status":"lead"}'
./bin/cognex --root ./data crdt-export ./ops.json
./bin/cognex --root ./data crdt-import ./ops.json
./bin/cognex --root ./data serve --port 8080 --api-key dev-secret

The previous project-intelligence commands still exist:

./bin/cognex --root /path/to/project index
./bin/cognex --root /path/to/project search "authentication stripe pricing page"
./bin/cognex --root /path/to/project context "build another SaaS landing page using the existing design system"
./bin/cognex --root /path/to/project graph

MCP tools

Point an MCP client at:

{
  "mcpServers": {
    "cognex": {
      "command": "cognex",
      "args": ["--root", "/absolute/path/to/data", "mcp"]
    }
  }
}

The MCP adapter exposes:

Tool	Purpose
index_project	Refresh project intelligence and file database indexes.
search_project	Search project files by natural language.
build_context	Build AI-ready code/project context.
project_graph	Return files, symbols, imports, and inferred relations.
query_database	Run read-only SQL over local file collections.
upsert_record	Create or update JSON/Markdown records as files.
delete_record	Soft-delete a file-backed record.
transaction_log	List recent local write transactions with before/after revisions.
sync_status	Show pending push/pull/conflict state for a sync remote.
sync_push	Push local files to a filesystem/Git sync remote.
sync_pull	Pull remote files into the local data root.
crdt_update	Apply CRDT field updates that merge across imported operation logs.
crdt_export	Export CRDT operations for sync/merge.
crdt_import	Import CRDT operations and materialize merged records.

How data maps to SQL

A folder becomes a collection. A file becomes a record.

customers/john.json     -> collection `customers`, id `john`
customers/sarah.md      -> collection `customers`, id `sarah`
products/catalog.csv    -> collection `catalog`, one record per row

Cognex materializes safe temporary SQLite tables for read-only SQL, so this works:

SELECT * FROM customers WHERE status = 'lead';

Writes go back to human-readable .json or .md files. CSV is indexed and queryable, but API writes currently target JSON/Markdown to avoid destructive spreadsheet rewrites. Every record response includes a revision hash. Send expected_revision in JSON bodies or If-Match on PUT/DELETE to prevent stale clients from overwriting newer edits.

Sync engine

Cognex now includes Option 2 sync for filesystem and Git-backed remotes. A sync remote is a directory with this structure:

remote/
  data/
    customers/john.json
    orders/order_001.json
  .cognex-sync/
    manifest.json
    crdt_ops.json

That remote directory can be:

• another local folder;
• a mounted network drive;
• a Dropbox/iCloud/Google Drive folder managed by that provider's desktop sync client;
• a Git repository when using --git.

REST examples:

curl -X POST http://localhost:8080/v1/sync/push \
  -H 'Authorization: Bearer dev-secret' \
  -H 'Content-Type: application/json' \
  -d '{"remote":"/path/to/remote-sync"}'

curl -X POST http://localhost:8080/v1/sync/pull \
  -H 'Authorization: Bearer dev-secret' \
  -H 'Content-Type: application/json' \
  -d '{"remote":"/path/to/remote-sync"}'

Git-backed sync:

./bin/cognex --root ./data sync-push ./git-remote --git --message "Sync customer data"
./bin/cognex --root ./data sync-pull ./git-remote --git

The sync engine uses file revision manifests and the last synced base revision to detect divergent file edits. It also carries CRDT operation logs in .cognex-sync/crdt_ops.json, so structured JSON record operations can move through the same folder/Git/Dropbox/iCloud/Google Drive sync path. If both local and remote changed the same non-CRDT file since the last sync, Cognex reports a conflict instead of overwriting data. On pull conflicts, it also saves the remote version as a .conflict-remote-* copy beside the local file for manual resolution. Use --force or force: true only when you intentionally want one side to win.

This is working push/pull replication, cloud-folder backup, Git sync, and CRDT-operation sync for structured JSON records. Rich text CRDT editing remains a future specialized layer.

CRDT engine

Cognex now includes Option 3 for JSON-style records: an operation-based CRDT engine. Each field update becomes an idempotent operation with:

• op_id
• actor
• per-actor seq
• collection
• record id
• field name
• JSON value or delete marker
• timestamp

Different fields merge automatically across devices. If two actors edit the same field concurrently, Cognex resolves deterministically with a last-writer-wins tuple (timestamp, actor, op_id). Imported operations are idempotent, so the same operation can be received more than once without duplicating state.

REST examples:

curl -X POST http://localhost:8080/v1/crdt/update \
  -H 'Authorization: Bearer dev-secret' \
  -H 'Content-Type: application/json' \
  -d '{"actor":"alice","collection":"customers","id":"john","fields":{"name":"John"}}'

curl -X GET http://localhost:8080/v1/crdt/export \
  -H 'Authorization: Bearer dev-secret'

curl -X POST http://localhost:8080/v1/crdt/import \
  -H 'Authorization: Bearer dev-secret' \
  -H 'Content-Type: application/json' \
  -d '{"ops":[...]}'

CLI examples:

./bin/cognex --root ./data crdt-update customers john --fields '{"name":"John"}' --actor alice
./bin/cognex --root ./data crdt-export ./ops.json
./bin/cognex --root ./other-device crdt-import ./ops.json

The CRDT layer is best for structured JSON record fields. Its operation log is synced by sync-push/sync-pull through folders, Git repositories, or provider-synced folders such as Dropbox, iCloud, and Google Drive. Markdown body text is still stored and synced as files; rich collaborative text editing would require a specialized text CRDT layer later.

Local-first safety model

Cognex is not trying to beat PostgreSQL at global multi-writer clustering. It is designed for local-first ownership first, then optional sync later. The backend now protects local writes with:

• an inter-process .cognex/write.lock so two local writers do not write the same store at the same time;
• atomic temp-file writes plus os.replace() and fsync so power loss is less likely to leave half-written JSON/Markdown;
• per-record revision hashes returned by the API;
• optimistic concurrency through expected_revision or If-Match;
• HTTP 409 Conflict when a stale frontend, user, or AI tries to overwrite a newer record;
• a SQLite tx_log table so local write history can be inspected.

This makes Cognex safer for single-user, personal, internal, and small-business local-first apps today. Filesystem/Git push-pull sync and structured JSON record CRDT merging are included, and sync carries CRDT operation logs between devices.

Security and production notes

• Set COGNEX_API_KEY in production.
• Set COGNEX_CORS_ORIGIN to your frontend URL.
• Mount persistent storage at COGNEX_ROOT on Railway.
• /v1/query only accepts read-only SELECT/WITH statements.
• Local writes are protected by an inter-process file lock, atomic temp-file replacement, SQLite WAL mode, optimistic record revisions, and a tx_log audit trail.
• Deletes are soft deletes: files are moved to timestamped .deleted-* backups.
• The REST server uses Python's standard library ThreadingHTTPServer, SQLite WAL mode, and no external runtime dependencies.

What is not included yet

Cognex now provides the production backend shape for your frontend, filesystem/Git sync, and structured JSON record CRDT operations. These are still future layers: user accounts/multi-tenancy, browser IndexedDB-native sync, provider API integrations for iCloud/Google Drive, rich-text CRDT editing, and a hosted dashboard.