Architecture

Three SQLDesk processes — the server, the workers and the scheduler — and two databases, Postgres and Redis. None of them is unusual, and knowing which one is doing what explains almost every question about why something is slow, stale, or missing.

All of it, including the parts that are off

The three processes above are what always runs. Everything else is an add-on, drawn dashed here and absent from a default install — see what is on when you change nothing. The shape does not change when you switch one on: it is another consumer of the same Redis, and it writes where the others write.

The server

Flask behind gunicorn. It serves the single-page application, answers the REST API, and checks permissions. It reads and writes its own Postgres and it enqueues jobs — but it does not run your queries. A request that would take four minutes against Trino would hold a web worker for four minutes, so it does not happen here.

Postgres — the metadata database

SQLDesk's own. Not your warehouse, and not a copy of it. It holds:

• users, groups and permissions;
• data source definitions, with credentials encrypted at rest;
• queries, dashboards, widgets, alerts, and a record of changes to them;
• query results — the rows that came back, as JSON, until they are cleaned up;
• the catalog, if you have harvested one.

Query results are usually the largest thing in it by a wide margin. A maintenance job removes results nothing points at any more; how long they are kept is configurable.

Redis — queues, locks and cache

Three distinct jobs, which is worth separating because people assume it is only a cache:

Job queues

Every query execution, scheduled refresh, alert check and email is a job on an RQ queue. The server puts them on; workers take them off. MCP's queries can have a queue of their own (SQLDESK_MCP_QUEUE) and workers that take only that.

Locks

If five people open a dashboard at once, one job runs and the others wait on its result rather than each hammering the warehouse with the same SQL.

Schema cache

The table and column list shown by the editor's autocomplete, with a time to live. It is a cache in the real sense: losing it costs a refresh, not data.

Redis holds nothing you cannot afford to lose. If it is wiped, in-flight jobs are lost and schedules resume; no query, dashboard or result goes with it. That is deliberate — it means Redis can be a plain container with no persistence configured.

Workers

Separate processes running RQ. They take jobs off the queues, open connections to your data sources, run the SQL, and write results back to Postgres. Everything slow happens here. Scale them when queries queue up; scale the server when pages are slow to load. Those are different problems and the fix is rarely the same one.

The scheduler

One process, and only one. It watches the clock and puts due work on the queue — scheduled query refreshes, live dashboards, schema and catalog refreshes, cleanup. Alerts are not on the clock: one is checked when its query's new result is stored. The scheduler runs nothing itself. Two schedulers would double-schedule everything, so this is the one component you do not scale horizontally.

What happens when you open a dashboard

1. The browser asks the server for the dashboard and its widgets.
2. The server reads them from Postgres, along with the stored result for each query, and returns them. Nothing has touched your warehouse.
3. The page draws. The age shown under each widget is the age of that stored result.
4. If you press Refresh, the server enqueues a job and the page waits for it. A worker runs the SQL, writes the new result, and the widget redraws.

Two widgets on the same query with the same parameters share a single fetch. A dashboard with five views of one query asks once.

DuckDB — the second database

SQLDesk runs a second database engine, inside itself, for two jobs that Postgres is the wrong shape for. It is worth understanding because it is the piece people are most surprised to find.

Uploaded files

A CSV or Parquet file you upload becomes a view over the file on disk. Nothing is imported — the file is the table, and DuckDB reads it where it lies.

Stream windows

The last few minutes of a Kafka topic, one file per topic. See below.

Why not Postgres? Because both of these are columnar, analytical workloads over data you can afford to lose, and Postgres is the one database here you cannot. Pushing fifty thousand events a second through it would make the database holding your users and dashboards also the one under the most write pressure, and a scan of a few million rows would compete with the page you are reading.

Why a file, and not memory? Because the processes that write and the processes that read are different ones. A stream's consumer runs on a worker; your query runs in the web process; the rollup runs on another worker. A file is the only cheap thing all three can see. DuckDB gives a file to one writer at a time, so the consumer takes the lock for the moment it writes and releases it between flushes; readers open read-only and wait out a flush rather than failing on one.

An uploaded file nobody has queried for a few days is unloaded — its view is dropped so no query pays to infer its schema — and the next query that names it brings it back. The file itself is never touched by that.

Streams — Kafka, and what a window means

A Kafka topic is not a table and SQLDesk does not pretend otherwise. What it keeps is a window: the last few minutes of a topic, held in DuckDB, queryable with ordinary SQL.

It is explicitly not a pipeline. SQLDesk does not land your topic anywhere, does not replay history, and does not promise to have seen every message. If you want the last six months, land the topic in your warehouse and point SQLDesk at that. What this is for is the question you have during an incident, about the last ten minutes.

The cluster is the data source

An administrator registers brokers and credentials once. Topics are its tables.

A consumer runs only while somebody is watching

Nobody watching means nothing consumed and nothing stored. A stream resumes from the end of the topic, never from where it left off — replaying a gap would stamp old events with an arrival time of now, and draw a chart that lies.

The window sizes itself

From the row budget and the rate actually observed. A slow topic gets an hour; a fast one gets four minutes. Above a ceiling the stream is sampled, and every chart drawn from it says so, because a count from a sampled stream is an estimate and silence about that is the worst outcome.

Rollups outlive the window

Each minute is aggregated into buckets and kept after the raw events have aged out, so "what happened this morning" has an answer when the window has long moved on.

MCP — where the AI pieces sit

The MCP server is part of the web server: JSON-RPC over HTTP, needing nothing heavy. An AI client connects to it the way a browser connects to the API, and runs as the person whose credentials it holds — the same data sources, the same permissions, the same audit.

Three pieces sit behind it, and they are deliberately separate:

The catalog

What the tools know about your warehouse: tables, columns, which ones are actually used together, agreed measures, and queries a person has confirmed as the right answer to a question. Built by a job on the workers — harvesting — which reads each data source's schema and the SQL that recently ran against it. It never overwrites a description somebody wrote.

Its own queue and its own workers

A model exploring a warehouse writes a great many queries. They go on the mcp queue, served by workers that take nothing else, so a model cannot slow down a dashboard somebody is watching.

A cost ceiling, from the engine's own estimate

Optional. Where the engine will say what a query costs before running it — a plan cost from the Postgres family, dry-run bytes from BigQuery — run_query can refuse above a limit and tell the model the figure. No estimate is never a refusal.

No AI component sits between your browser and your data. Nothing is sent to a model by SQLDesk; a model asks SQLDesk, and SQLDesk answers from the data sources that model's user may already read.

The words, and what they mean

Data source

A connection to somewhere data lives, with credentials encrypted at rest. The unit permissions are granted on. A warehouse, a file upload area, or a Kafka cluster.

Query

Saved SQL against one data source. Has an owner, a history of changes, optional parameters and an optional schedule.

Query result

The rows a query returned, stored in Postgres with the hash of the SQL that produced them. What a dashboard shows is a stored result, which is why dashboards are fast and why they have an age.

Visualization

A chart over a query's result. A query can have several.

Widget

A visualization placed on a dashboard.

Live dashboard

One the server refreshes on an interval, once for everyone watching, instead of every open tab re-running the queries on its own timer.

Window

What SQLDesk keeps of a Kafka topic: the last few minutes, in DuckDB. Not a copy of the topic.

Sampling

Keeping a fixed share of a topic's events when it is faster than the ceiling allows. Charts drawn from a sampled stream say so.

Rollup

A per-minute aggregate of a stream, kept after the raw events have gone.

Catalog

What the MCP tools know about your warehouse.

Harvest

The job that builds the catalog from schemas and recently-run SQL.

Unloaded

An uploaded file whose view has been dropped because nothing has queried it lately. The file is untouched and the next query that names it brings it back.

Where everything is kept