Skip to main content

Module: database

A plug-and-play database inspector — a psql-like console pane where the user (or the agent) writes SQL and runs it against any connected database. It ships with a pluggable driver layer so you can connect to several providers, and exposes the dashboard's own local vector store as the built-in app connection.

Status: implemented — frontend in packages/core/src/modules/database/, backend in backend/modules/database/.

What it does​

  • Connections — a named-connection store. The built-in app connection (the node's own SQLite database, .data/app.db — library sources, browser history, code symbols, tasks, and every rec_* table the records module creates) is always present and read-only; you add your own connections for external databases. Connections persist in .data/connections.json.

    Records tables being ordinary tables here is the whole reason that module stores them physically rather than as JSON blobs: a CRM built in the Records panes is queryable from this console — SELECT stage, count(*) FROM rec_deals GROUP BY stage — with no records-specific support in the driver layer.

    A third built-in, atlas, is conditional: it appears only on a node that administers the shared MongoDB cluster — see The atlas built-in.

    app is the app database; vectors is the vector store

    Two built-ins, because the node keeps its data in two different shapes. app is the SQLite file .data/app.db. The vector store is LanceDB, a directory under .data/lancedb — no SQLite file for a SQL driver to open — so it is exposed as the separate vectors connection using the JSON dialect. The two used to be the same file (the vector store was SQLite before the LanceDB migration), which is why vectorstore.get_db_path() and app_db.get_app_db_path() are deliberately separate functions.

  • Query console — write a query, run it with Run or Ctrl/Cmd+Enter, and get a results grid with column headers, a status line (N rows · M ms, truncation flag), and inline error output. Recent queries are kept in a history dropdown. A Read-only toggle restricts a run to a single SELECT/WITH/EXPLAIN statement (SQL) or to the read ops (JSON).

  • Dialect-aware editor — the editor (QueryEditor.tsx) is one CodeMirror that reconfigures itself per connection through compartments, so switching connections never remounts it (which would drop undo history mid-edit):

    • SQL connections get @codemirror/lang-sql: keyword completion in the connection's dialect (postgres, mysql, sqlite; duckdb uses the PostgreSQL dialect as its nearest match, oracle falls back to StandardSQL) plus table and column completion fed live from the same /database/connections/{id}/schema introspection the sidebar shows — schema-qualified names (public.users) included, with the dominant schema as the unqualified default. The mapping lives in the pure helper sqlSchema.ts:schemaToSqlConfig.
    • Vector and MongoDB connections get JSON highlighting (via lang-javascript — JSON is a subset of JS, so this needs no extra language package) and completion for the op names, the body keys, and the store's real collection names. The op/key vocabulary is passed into one shared completion source, so the vector and Mongo dialects can't leak into each other.
  • Schema sidebar — an introspected tree of tables → columns (with types and PK markers), or collections → fields for vector stores and MongoDB. Clicking one inserts the dialect-appropriate starter: SELECT * FROM <table> LIMIT 100, {"op": "list", "collection": "<name>", "limit": 20}, or {"op": "find", "collection": "<name>", "filter": {}, "limit": 20}.

  • No synthetic data — the pane only ever shows real rows returned from a real connection.

Providers & drivers​

Each provider is a thin synchronous driver (backend/modules/database/drivers/) behind a uniform test / run_query / introspect contract. Routes run drivers via asyncio.to_thread so the (blocking) client libraries never stall the event loop. Client libraries are imported lazily, so a missing optional dependency yields a clear error rather than breaking boot.

Every driver declares a dialect — which query surface it speaks:

  • sql — the query text is SQL, handed to a real SQL engine.
  • json — the query text is a JSON operation body against a vector store. These stores have no SQL dialect (a collection, a metadata filter and a query vector is genuinely all they take), so the console sends their native shape rather than faking SQL over them. See Querying vector stores.
  • mongo — the query text is a JSON body carrying a MongoDB operation: a filter, a pipeline, a projection. See Querying MongoDB.

Every dialect returns the same columns/rows result, so the grid, the status line and the agent's reader are dialect-agnostic — only the input surface differs.

Why mongo isn't just json

Both send a JSON body, so folding Mongo into the vector dialect is tempting. It would mean mapping find onto search and a metadata filter onto an MQL filter — exactly the silent reinterpretation the vector drivers refuse to do (they raise instead when a store can't express something). MQL is a real query language with its own vocabulary; offering an agent "vector" and "query" keys at a Mongo collection, or "pipeline" at a vector store, teaches it the wrong shape. Same editor, different vocabulary.

ProviderDriverDialectDependencyNotes
sqlitesqlite_driversqlstdlibBacks the built-in app connection (.data/app.db) and any .sqlite/.db file.
postgrespostgres_driversqlpsycopg[binary]Connect to any Postgres; pgvector vector columns and bytea blobs are summarized in the grid.
duckdbduckdb_driversqlduckdbLocal analytical/columnar querying (file or in-memory).
mysqlmysql_driversqlpymysqlMySQL / MariaDB.
oracleoracle_driversqloracledb (extra oracle)Oracle Database incl. 23ai. Thin mode — no Instant Client. 23ai AI Vector Search is plain SQL (VECTOR, VECTOR_DISTANCE()), so it needs no special casing.
lancedblancedb_driverjsonlancedb (core dep)Backs the built-in vectors connection — the node's own vector store.
chromachroma_driverjsonchromadb (extra chroma)Embedded (path) or HTTP server (host).
qdrantqdrant_driverjsonqdrant-client (extra qdrant)Server (url/host) or embedded (path).
weaviateweaviate_driverjsonweaviate-client (extra weaviate)Local instance or Weaviate Cloud.
mongodbmongo_drivermongopymongo (core dep)Any Mongo deployment, and the shared Atlas cluster via the admin-gated built-in atlas connection.

Install the vector-store clients with uv sync --extra chroma (or qdrant, weaviate, or vectordb for all three); Oracle with uv sync --extra oracle. Each is lazy-imported, so the provider is always listed and only fails — with an install hint — when you actually run a query against it.

Result cells are coerced to JSON-safe values: binary blobs and long numeric arrays (embeddings, pgvector, Oracle 23ai VECTOR columns, which arrive as array.array) are summarized (e.g. <1536 bytes>, [384 numbers]), and dates/decimals are stringified.

Querying vector stores​

A json-dialect query is one JSON object. The shape is uniform across all four stores (drivers/vector_base.py), so one editor and one set of snippets work everywhere:

{"op": "search", "collection": "library", "query": "how does docking work", "limit": 5}
{"op": "search", "collection": "library", "vector": [0.1, 0.2], "where": {"kind": "note"}}
{"op": "list", "collection": "library", "limit": 50, "offset": 100}
{"op": "get", "collection": "library", "ids": ["abc", "def"]}
{"op": "count", "collection": "library"}
{"op": "collections"}
FieldMeaning
opRequired. Read: search, get, list, count, peek, collections, describe. Write: upsert, delete, create_collection, drop_collection.
collectionTarget collection (required by every op except collections).
queryText to embed and search with.
vectorExplicit query vector, instead of query.
whereMetadata equality filter, e.g. {"kind": "note"}.
idsDocument ids for get / delete.
limit / offsetPaging. limit is capped by the database.rowLimit setting.
selectRestrict/order the result columns.
metricDistance-metric override where the store takes one per query (LanceDB).

Read-only mode applies here too: it allows the read ops only. A body it can't parse is treated as not read-only — an op we can't identify is one we can't vouch for.

Results are flattened into the normal grid: id, score, text, then each metadata key as its own column (a metadata key colliding with a reserved name is prefixed meta.), with the raw vector last. Hoisting metadata to columns is the point — a single JSON blob cell would defeat filtering and eyeballing.

score is only shown when the metric allows it

Chroma and LanceDB both default to squared L2 distance, where 1 - distance yields negative, meaningless "scores" that still sort plausibly — the worst kind of wrong. A similarity is therefore derived only for cosine spaces (read from the collection's own configuration); otherwise the grid shows the raw distance and no score. Ranking is correct either way — ascending distance is descending similarity.

The vectors connection defaults to cosine to match vectorstore.py, which searches the node's own tables with .metric("cosine"). Override per query with "metric".

Metadata filters may be applied client-side

LanceDB stores metadata as a JSON string, so its keys are not physical columns and Lance cannot push a filter on them down — a where on a column you can plainly see in the grid would fail with No field named …. The driver therefore splits where: physical columns are pushed down, metadata keys are filtered in Python over a bounded over-fetched window, and the status line says so when that happens. It is not a cheap deep paginator.

Querying MongoDB​

A mongo-dialect query is one JSON object (drivers/mongo_driver.py):

{"op": "collections"}
{"op": "find", "collection": "presence", "filter": {"person_id": "abc"}, "limit": 20}
{"op": "find", "collection": "presence", "projection": {"addresses": 1}, "sort": {"updated_at": -1}}
{"op": "aggregate", "collection": "presence", "pipeline": [{"$group": {"_id": "$display_name", "n": {"$sum": 1}}}]}
{"op": "count", "collection": "presence", "filter": {}}
{"op": "distinct", "collection": "presence", "field": "person_id"}
{"op": "describe", "collection": "presence"}
{"op": "indexes", "collection": "presence"}
{"op": "databases"}
{"op": "update", "collection": "presence", "filter": {"_id": {"$oid": "…"}}, "update": {"$set": {"x": 1}}}
{"op": "delete", "collection": "presence", "filter": {"person_id": "abc"}, "many": true}
FieldMeaning
opRequired. Read: find, find_one, aggregate, count, distinct, collections, databases, describe, indexes, stats. Write: insert, update, delete, create_collection, drop_collection, command.
collectionTarget collection (required by every op except collections, databases, stats, command).
dbDatabase override — an admin inspecting more than one database on the cluster. Defaults to the connection's.
filterMQL filter document.
projectionFields to include/exclude, e.g. {"addresses": 1}.
sort{"field": -1}. Written as an object because that is what a human types; JSON object order survives parsing, so a multi-key sort keeps its order.
pipelineAggregation stages (array).
documentsDocuments for insert.
updateUpdate document for update, e.g. {"$set": {…}}.
fieldThe key to collect for distinct.
manytrue to update/delete every match instead of one.
limit / skipPaging. limit is capped by the database.rowLimit setting.

Documents flatten into the grid with _id first and every other field in document order, the union of keys across a ragged result set. describe is honest sampling rather than introspection — a document store has no declared schema, so it reports each field's BSON types and how many of the sampled documents carried it. $jsonSchema validators are deliberately not consulted: they describe what is allowed, not what is stored.

aggregate is not automatically a read

A pipeline ending in $out or $merge rewrites a collection. Classifying aggregate as read-only on the strength of the op name would let read-only mode — and the agent's read-only database.query tool — replace a collection on the shared cluster. base.mongo_query_is_read_only therefore inspects the pipeline, and searches for those stages at any depth: they are only legal as the final top-level stage, but a gate that trusts input to be well-formed is a gate you can walk past with input that isn't.

command is classified as a write for the same reason a multi-statement SQL string is: its contents can't be vouched for.

ObjectIds: bare hex out, {"$oid": …} back in

Query bodies are parsed as Extended JSON (bson.json_util), so {"$oid": "…"}, {"$date": "…"} and the rest of the BSON types work. Results go the other way through jsonable, which renders an ObjectId as bare hex because that is what is readable in a grid — so filtering on an id you can see in the results means typing it back as {"$oid": "65f…"}. The alternative, coercing any 24-hex string in a filter to an ObjectId, would silently change the meaning of a filter on a string field.

Clients are cached per URI. A MongoClient is a connection pool meant to outlive one query, and it is thread-safe, so the asyncio.to_thread workers share one rather than paying SRV resolution plus a TLS handshake to Atlas on every run.

The atlas built-in (admins only)​

The shared MongoDB Atlas cluster (backend/atlas.py — the social layer's presence directory) shows up as a third built-in connection, atlas, but only on a node whose operator administers it:

ATLAS_ADMINEffect
unset (default)No atlas connection. The cluster is unreachable from the console.
1 / true / roatlas appears, read-only — writes are refused in the driver.
rwatlas appears and accepts writes.

Env-only, and default-off, for two separate reasons:

  • Credentials present ≠ administrator. Every node on the social fabric sets ATLAS_DB_USER so it can publish its own presence record. Gating on "are credentials configured" would hand every user's console — and their agent — a free hand over shared infrastructure. Signed directory records stop a stranger forging presence; nothing would stop them reading the whole collection or dropping it.
  • Never a setting. GET /api/settings returns the whole settings bag to the browser, so a setting here would be a switch any page could read and the frontend could be tricked into flipping. Same rule as the credentials themselves.

ATLAS_ADMIN_URI optionally points the console at a higher-privileged cluster user than the app itself runs as (listing databases needs more than the app's own single-collection access); otherwise the app's URI is reused.

The ro / rw split is not tidiness. database.execute is an agent tool, so it is the difference between an agent that can inspect the cluster and one that can drop a collection every other node depends on. It is enforced in mongo_driver._guard_writes rather than in the route, because run_query(read_only=False) is exactly what that tool sends — a route-only check would leave the driver as an unguarded back door.

This is a gate against ambient credential use, not a boundary against the human at the keyboard: anyone who knows the cluster password can add an ordinary mongodb connection by hand. What it prevents is a node silently escalating a credential it holds for one narrow purpose into a cluster-wide console.

The node's two local stores​

The dashboard keeps its own data in two places, and they are not the same thing:

StorePathShapeConsole connection
App database.data/app.dbSQLite file — library_sources, browser_history, browser_bookmarks, code_symbols, llamacpp_traces, async_tasksbuilt-in app (sqlite, sql dialect)
Vector store.data/lancedbLanceDB directory, one table per collectionbuilt-in vectors (lancedb, json dialect)

Both are built-in, synthesized rather than stored, and neither can be edited or deleted.

The app database is ordinary SQL (SELECT id, library, status FROM library_sources), because it is a real SQLite file.

The vector store is reached through the vectors connection using the JSON dialect above ({"op": "collections"} to start). It is not SQL-attachable: LanceDB is a directory of its own format, so there is no file for a SQL driver to open — which is exactly why it needed a json-dialect driver to become queryable at all. Writes still belong to vectorstore.py (upsert_document / search_documents), which owns the embedder and each collection's fixed vector width; the console's LanceDB driver deliberately refuses upsert.

Sibling tables (<collection>__clip, the CLIP vectors) are hidden from the app's global sweeps so they don't show up as fake collections — see library — but the console does list them: an inspector's job is to show what is actually on disk, and a CLIP table you can't see is a CLIP table you can't debug.

These were one file once

Before the LanceDB migration the vector store was SQLite, and a single get_db_path() returned it. Code that still assumes that — passing the LanceDB path to sqlite3.connect() — fails with the opaque unable to open database file. That is why vectorstore.get_db_path() (the LanceDB dir) and app_db.get_app_db_path() (the SQLite file) are separate, deliberately un-mergeable functions.

Embeddings are generated by a tiered pipeline (active agent provider → dedicated embedding model probe → deterministic local hash fallback so it works offline) in embeddings.py. The vector store also backs agent-commons matchmaking — see network.

Bulk writes: use the batch APIs

get_embeddings(texts) and upsert_documents(collection, rows) are the paths any index build should take. Their single-document siblings are fine for one-off writes but pathological in a loop, because both costs are per-call, not per-row:

  • merge_insert is a whole-table operation — ~1.5s on a 5k-row table whether you hand it 1 row or 256.
  • get_embedding resolves the model (GET /api/tags) and builds a fresh HTTP client on every call.

Looping the single-document versions over ~40k symbols took hours, with the embedder itself accounting for well under a minute of it. get_embeddings resolves the model once (cached, 60s TTL), reuses one client, and sends a batched /api/embed — falling back to bounded-concurrency single requests when the provider has no batch endpoint. See symdex.

Contributions to the Layout Shell​

  • Widget: database.console — the connection bar + schema sidebar + query console.
  • Command: database.open (Database: Open Console) — opens the console in the workspace center.
  • Settings: database.defaultConnection (default app), database.rowLimit (default 1000), database.embedProvider (default agent) and database.embedModel (default blank), read live via useSetting() — see settings.

Choosing the embedding provider​

database.embedProvider decides where text is embedded. agent — the default, and the only behaviour before the setting existed — embeds with the chat agent's provider. Any provider id embeds there instead, so the agent can chat on a hosted model while embeddings run somewhere else, or the reverse.

Hosted providers are called with their key from Settings → API keys (NIM borrows the nvidia connector's). Without a key, or on a provider with no embeddings endpoint (Anthropic, Gemini), nothing is sent and the hash fallback answers — which index builds refuse to persist.

The pin (database.embedModel, below) is sent to whichever provider this names. A pin left over from a local server, such as LM Studio's text-embedding-nomic-embed-text-v1.5, makes a hosted provider refuse every request: Model … does not exist, logged as a warning, followed by the hash fallback. Change the pin when you change the provider, or blank it to use the provider's default.

Discovery (below) is skipped for hosted providers, because neither listing can be trusted to find an embedder. NIM's /v1/models still lists embedders that answer 410 Gone, and OpenRouter keeps its embedders out of the chat catalog altogether. Each has a default instead: nvidia/nemotron-3.5-embed-1b on NIM, nvidia/nemotron-3.5-embed-1b:free on OpenRouter (2048-dim, $0 on both), and text-embedding-3-small on OpenAI.

NVIDIA's embedders are asymmetric: a search query and the passage that answers it are embedded differently, and the request is refused without input_type. So get_embedding and get_embeddings take input_type="query" | "passage". Passage is the default because indexing is the common case, and every search path passes query. It is sent only to providers that accept it everywhere (NIM, OpenRouter); OpenAI rejects an unknown argument.

Pinning the embedding model​

database.embedModel names the model used to embed text for every vector collection on the node. Left blank, the model is discovered: _select_embedding_model walks a keyword priority — all-minilm, nomic-embed, bge-, embed — over whatever the configured provider happens to be serving, and takes the first hit.

Discovery is convenient on a fresh node and a hazard on an established one, because the answer depends on the provider's catalog rather than on anything the node decided. Moving this node's agent from Ollama to LM Studio dropped all-minilm from the list, so discovery fell through one slot to nomic-embed and the embedding width went 384 → 768 with no configuration change to point at. A collection's vector width is fixed when it is created, so that is not a degradation but a hard stop: default began raising DimensionMismatch on every ingest, and symdex — which records its own embed_model/dim and compares them at query time — started answering every search with reindex_needed and zero results. Loud in one case, silent in the other.

Pin the model once an index exists that you would not enjoy rebuilding. Two properties matter:

  • The pin is used verbatim, never validated against the catalog. A pin naming a model the provider is not serving fails as a request error rather than quietly reverting to discovery — falling back is precisely what would reintroduce the silent width change.
  • The pin is part of the resolution cache key, not a value behind its 60-second TTL, so it takes effect on the next call. Otherwise a rebuild kicked off immediately after changing this setting would embed with the previous model and write a whole collection into the wrong space.

Changing the pin re-embeds nothing. Rebuild the affected indexes: POST /api/symdex/reindex handles symdex (a changed embed_model forces a full rebuild of every kind by itself), while a library collection must be dropped and its sources re-ingested, since nothing else drops the table.

Backend REST API​

Mounted under /api/database.

MethodPathDescription
GET/connectionsList connections (built-in + saved, secrets redacted) and available providers.
POST/connectionsCreate a connection.
PUT/connections/{id}Update a connection (built-in is read-only; omitted secrets are preserved).
DELETE/connections/{id}Delete a saved connection.
POST/connections/testProbe an unsaved connection (form still holds the password).
POST/connections/{id}/testProbe a stored connection (server-side credentials).
GET/connections/{id}/schemaIntrospected tables/columns.
POST/queryRun a query against a connection. The sql field carries SQL for sql-dialect connections and a JSON operation body for json-dialect ones (the field keeps its name for wire compatibility). read_only rejects non-SELECT / write ops respectively. Returns columns, rows, rowcount, elapsed_ms, truncated.
GET/statusApp vector store stats + active embedding provider/model.
POST/pullStream an Ollama all-minilm embedding-model download (NDJSON).
GET/collectionsApp vector store collection names.
GET/documentsList app vector store documents (paginated, filterable).
POST/documentsEmbed and upsert a document into the app vector store.
DELETE/documents/{id}Delete a document by id.
POST/searchSemantic similarity search in an app vector store collection.

Agent Tools​

The widget registers these tools with the agent orchestrator:

Tool NameDescriptionParametersSide Effect
database.listConnectionsList connections the agent can query, each with its dialect.noneNo
database.describeSchema and dialect for a connection, so the agent writes the right kind of query.connection_id (str, opt)No
database.queryRun a read-only query: a SELECT for SQL connections, a JSON read op for vector connections.sql (str), connection_id (str, opt)No
database.executeRun a writing statement (INSERT/UPDATE/DELETE/DDL); permission-gated.sql (str), connection_id (str, opt)Yes
database.semanticSearchVector search a collection in the app vector store.collection (str), text (str), limit (int, opt)No

Agent Context​

The console exposes the active connection and its schema via useAgentContext() so the agent can target the right database:

{
activeConnection: "app",
provider: "sqlite",
dialect: "sql",
queryFormat: "SQL.",
tables: [{ name: "documents", columns: ["id", "collection", "text", "embedding"] }]
}

On a vector or MongoDB connection the same hook reports the dialect and that dialect's op vocabulary instead, and names the list collections:

{
activeConnection: "vectors",
provider: "lancedb",
dialect: "json",
queryFormat: 'Vector store: send a JSON body like {"op":"search",…}. Ops: search, list, …',
collections: [{ name: "symdex", columns: ["id", "text", "metadata", "vector"] }]
}

…and on the atlas connection it reports dialect: "mongo" with the MQL op list and a note that bodies are Extended JSON, so an ObjectId is {"$oid": "…"}.

dialect matters as much as the table list: without it the agent writes SQL at a vector store and gets a parse error it can't diagnose.

Security​

SQL execution is unsandboxed (consistent with the v1 trusted plugin posture). The agent's database.query tool is read-only; writes go through database.execute, which is side-effect gated. Connection credentials are stored as plaintext in the gitignored .data/connections.json; encryption is a tracked follow-up.

Two credential rules the driver layer enforces:

  • uri is a secret field. A connection string embeds the password, so uri is redacted to a boolean alongside password and dsn (connections._SECRET_FIELDS, mirrored by SECRET_FIELDS in widgets.tsx). A redactor that only knew about a password field would hand a mongodb+srv://user:pw@host straight to the browser.
  • The atlas built-in carries no URI at all. Its listed config holds only the cluster host, the database name and the read-only flag; resolve_config adds the URI from the environment at query time, so the cluster password never enters the connection store or a response body. That function re-checks ATLAS_ADMIN and raises PermissionError (→ 403) if access was revoked since the connection list was built.

Browser vs Desktop​

Identical in both layouts — the frontend talks to the local FastAPI server, which owns the driver connections and the local stores.