Deploying

Five processes and two databases — see Architecture. Everything below is a way of arranging those, plus two add-ons that stay off until you ask for them.

Services: what is core, what is an add-on

The core always runs. Each add-on is one decision; leave it off and nothing about it is pulled, started or shown.

What is on when you change nothing

Everything that costs something while nobody is using it is off. A default install is five deployments and one volume, and the published image is the same size whichever of these you turn on — the only one with an image of its own is the renderer.

Mailpit and the demo warehouse in the development compose file are for developing SQLDesk, not for running it, and appear in neither of the files below.

MCP & Catalog

One switch turns on the MCP endpoint, the catalog it answers from, the scheduled harvest, measures, Admin → Catalog and manage ai export/import. Off, /mcp answers 404 and the Catalog page is not there. SQLDesk calls no model itself either way: the model is the MCP client's.

# .env
SQLDESK_FEATURE_AI=true
SQLDESK_MCP_QUEUE=mcp           # optional, with the mcp profile: MCP's own worker
COMPOSE_PROFILES=mcp

helm upgrade --install sqldesk ./charts/sqldesk --set mcp.enabled=true

Then fill the catalog once, rather than waiting for the daily harvest: /app/manage.py ai harvest in the server container.

Streams

Kafka topics you can query — see the Streams guide. One switch turns on the consumers, the Streams permissions, the streaming halves of the Queries and Dashboards menus, and Settings → Streaming Data Sources.

helm upgrade --install sqldesk ./charts/sqldesk --set streams.enabled=true

Two settings have to agree, and the lower of them wins. streams.worker.workersCount is how many processes exist; SQLDESK_STREAM_MAX_CONCURRENT is how many topics may consume at once. A slot limit of five served by two processes is a limit of two wearing a five.

helm upgrade --install sqldesk ./charts/sqldesk \
  --set streams.enabled=true \
  --set streams.worker.workersCount=5 \
  --set extraEnv.SQLDESK_STREAM_MAX_CONCURRENT=5

A slot is a topic, not a viewer: five people watching orders share one consumer, one window and one slot. Turning streams on also creates the shared volume, because the window is a file the stream worker writes and the server reads.

Uploads

CSV and Parquet files people upload and query with DuckDB.

helm upgrade --install sqldesk ./charts/sqldesk --set uploads.enabled=true

The claim is ReadWriteOnce by default, which mounts on one node — so with it the chart keeps the workers on the server's node, or they wait forever on a Multi-Attach error. If you intend to spread pods across nodes, give it a ReadWriteMany class (EFS, Filestore, Azure Files, NFS) and say so:

--set uploads.accessMode=ReadWriteMany --set uploads.storageClass=efs-sc

Files expire on a clock people can see and stop — uploads.lifecycle — because an uploaded file is the only copy there is, unlike a query result the warehouse can be asked for again.

Rendering

Pictures of dashboards and queries in alert emails. The renderer is a browser, which is why it is its own image (ghcr.io/bot-netizen/sqldesk-screenshots) and not part of the SQLDesk one.

# .env
SQLDESK_FEATURE_ALERT_SCREENSHOTS=true
SQLDESK_SCREENSHOT_URL=http://screenshots:3000
SQLDESK_INTERNAL_BASE_URL=http://server:5000
COMPOSE_PROFILES=rendering      # mcp,rendering for both

helm upgrade --install sqldesk ./charts/sqldesk --set rendering.enabled=true

The chart sets the three variables itself. The renderer reaches SQLDesk on the internal network rather than your public address, so redirect to HTTPS at your proxy and leave SQLDESK_ENFORCE_HTTPS off — with it on, the renderer is redirected to an address it cannot reach and every picture fails. Cookies are still marked Secure: that follows from SQLDESK_HOST starting with https://, not from the redirect.

The renderer is a browser, so it renders only SQLDesk's own address, and only for a caller that shows a token. The chart generates the token; with Compose, set SQLDESK_SCREENSHOT_TOKEN in .env (the renderer checks it whenever it is set).

What must be set

Keep the two secrets somewhere they survive a redeployment. Losing the cookie secret logs everyone out; losing the secret key loses every stored credential. Set both: with SQLDESK_SECRET_KEY unset, the cookie secret encrypts credentials too, and rotating it for sessions then loses them.

Both files also turn on CSRF checks (SQLDESK_ENFORCE_CSRF), which upstream leaves off so as not to break old installs. The application has always sent the token; API-key calls, MCP included, are not affected.

Mail

Invitations, password resets and alert emails need a mail server. In .env:

SQLDESK_MAIL_SERVER=smtp.example.com
SQLDESK_MAIL_PORT=587
SQLDESK_MAIL_USE_TLS=true
SQLDESK_MAIL_USERNAME=…
SQLDESK_MAIL_PASSWORD=…
SQLDESK_MAIL_DEFAULT_SENDER=sqldesk@example.com

Then send yourself one to check it arrives: docker compose -f compose.prod.yaml run --rm server manage send_test_mail you@example.com. Without mail, people are invited with a link an admin copies to them by hand.

OAuth for MCP clients

On wherever MCP is on, and it needs nothing configured: a client discovers the endpoints from the 401 it gets on its first call, opens a browser, and the person signs in the way they always do. See signing in.

It needs https. An authorization code and a bearer token crossing a network in the clear is what the flow exists to avoid, so the endpoints refuse plain http. Behind a TLS-terminating proxy this works as long as the proxy sets X-Forwarded-Proto, which every ingress does; the hop SQLDesk itself sees is http and PROXIES_COUNT is what says how many hops to trust.

Tokens expire in an hour and refresh for thirty days of use. They are stored as hashes, listed on each person’s profile under Connected apps, visible to administrators under Admin → MCP, and revoked when an account is disabled. API keys keep working, which is what a script or a headless setup should use.

Scheduled subscriptions, and sending to Slack

Both need the renderer add-on — there is nothing to send without a picture — and the Send dashboards permission, which an administrator grants to a group. Where no renderer is configured the feature is not offered at all, rather than offered and failing.

The renderer is a separate optional container (rendering.enabled=true in the chart, or the commented-out service in compose.prod.yaml). It reaches the application by the internal service name, not the public address, so it does not have to get through whatever is in front of you:

Nothing has to be made public to be pictured. The renderer is let in with a pass good for one dashboard, for five minutes, read-only, and spent as soon as the picture is taken.

Sending to a Slack channel needs one more thing: an app installed in the workspace, with its bot token pasted under Settings → Slack. SQLDesk draws the picture and uploads it, so Slack never has to reach this install — which is what makes it work behind a VPN. Scopes: chat:write, files:write, channels:read, groups:read.

Docker Compose

cp .env.example .env        # then fill in the secrets and POSTGRES_PASSWORD
docker compose -f compose.prod.yaml run --rm server create_db
docker compose -f compose.prod.yaml up -d

The tables first, then the services: a server started against an empty database answers every page with an error. Open the address and the first page makes your organization and your administrator account.

Pin the image in .env as SQLDESK_IMAGE=. Without it the compose file falls back to the release it was written for.

The port is published on the machine itself (127.0.0.1) until you put a proxy in front or set SQLDESK_BIND=0.0.0.0. Tell SQLDesk how many proxies stand in front of it with SQLDESK_PROXIES_COUNT: 0 when it is reached directly, 1 behind nginx or Caddy. The number decides which address the login throttle and the audit log see.

Upgrading

# .env: SQLDESK_IMAGE=ghcr.io/bot-netizen/sqldesk:0.7.0
docker compose -f compose.prod.yaml pull
docker compose -f compose.prod.yaml run --rm server manage db upgrade
docker compose -f compose.prod.yaml up -d

Migrations before the new server takes traffic. Release notes call out any migration that rewrites data rather than adding to it. The chart does this itself, as a hook that runs before anything else changes.

0.6 → 0.7

One migration, one default that changed, and one thing you have to ask for.

Docker Compose. Pin the image, migrate, then start:

# .env
SQLDESK_IMAGE=ghcr.io/bot-netizen/sqldesk:0.7.0

docker compose -f compose.prod.yaml pull
docker compose -f compose.prod.yaml run --rm server manage db upgrade
docker compose -f compose.prod.yaml up -d

Helm. The chart migrates itself, in a hook that runs before anything else changes:

helm upgrade sqldesk ./charts/sqldesk --reuse-values \
  --set image.tag=0.7.0

helm upgrade sqldesk ./charts/sqldesk --reuse-values \
  --set image.tag=0.7.0 \
  --set uploads.enabled=true

The migration adds kind to dashboards and fills it in with the rule 0.6 applied when reading: a dashboard with a streaming panel becomes a streaming dashboard, everything else an ordinary one. No dashboard changes which list it appears in. It is reversible; the downgrade drops the column.

Streams are off until you ask for them — streams.enabled=true in the chart, or SQLDESK_STREAMS_ENABLED=true with a stream worker on Compose. Left off, the Streams permissions are offered to nobody, the streaming halves of the Queries and Dashboards menus are not drawn, and the Settings tab for topics is not there. See Streams.

Nothing else changes an existing dashboard, query or data source. The navigation moved — streaming is a half of Queries and a half of Dashboards rather than a section of its own — and the four /streams/… URLs from the old menu redirect to where those pages live now, so links people have already sent each other still work.

Rolling back to 0.6 means putting the old image back and running manage db downgrade once. Dashboards lose the kind column and 0.6 works out the kind from the widgets again, as it did before.

Everything on, for trying it out

Each add-on is off so that an install pays for nothing it does not use. To see all of them at once — on a laptop, with nothing real attached — turn them all on together:

helm upgrade --install sqldesk ./charts/sqldesk \
  --set image.tag=0.7.0 \
  --set streams.enabled=true \
  --set mcp.enabled=true \
  --set uploads.enabled=true \
  --set rendering.enabled=true

That adds a stream worker, an MCP worker, the shared volume, and the headless browser that draws pictures for alert emails. On a four-core laptop it is comfortable; see How much machine you need for what each part asks for.

Streams need something to consume. There is a toy broker in the repository — one Redpanda node and two producers, a message a second each on orders and payments:

kubectl apply -f examples/kafka-demo.yaml

Then add a data source of type Kafka stream with the broker redpanda:9092, and turn the topics on under Settings → Streaming Data Sources. An administrator may use every feature the install offers without granting anything; for everybody else the permissions are handed out per group under Settings → Groups.

# .env
SQLDESK_FEATURE_AI=true
SQLDESK_MCP_QUEUE=mcp
SQLDESK_FEATURE_ALERT_SCREENSHOTS=true
SQLDESK_SCREENSHOT_URL=http://screenshots:3000
SQLDESK_INTERNAL_BASE_URL=http://server:5000
SQLDESK_SCREENSHOT_TOKEN=a-random-string

docker compose -f compose.prod.yaml --profile mcp --profile rendering up -d

Kubernetes

A Helm chart is in charts/sqldesk. It generates the two secrets on first install and keeps them on every upgrade after, so do not pass them on the command line: a second --set secrets.secretKey=$(openssl rand …) would replace the key and every stored credential with it.

minikube start
helm install sqldesk ./charts/sqldesk --timeout 15m \
  --set host=http://localhost:8080
kubectl port-forward svc/sqldesk 8080:5000

Port 8080 rather than 5000, which macOS keeps for AirPlay. minikube service works too, but gives a different random port each time, so host cannot be set to match.

Uploaded files (CSV and Parquet for DuckDB) live on a volume the server and the workers share. It is ReadWriteOnce by default, which every cluster can provide, and the chart keeps the workers on the server's node so they can mount it. With a ReadWriteMany storage class, set uploads.accessMode=ReadWriteMany and they schedule anywhere.

For a real cluster, put an Ingress in front and terminate TLS there — an API key travelling to /mcp over plain HTTP is a credential in the clear — and set service.type to ClusterIP; NodePort is for minikube.

The bundled Redis takes a generated password, and a NetworkPolicy keeps Postgres, Redis and the renderer reachable from this release's pods only. The policy needs a network plugin that enforces it (Calico, Cilium, the managed clouds' own); elsewhere it is accepted and does nothing, which is why the password is there too.

Already running on Compose? Moving from Docker Compose to minikube brings the existing instance across instead of starting an empty one.

Moving from Docker Compose to minikube

Everything comes across: people, queries, dashboards, alerts, uploaded files and the credentials saved on data sources. The Compose install's own data is never changed, so it stays your way back until you delete it. Run all of this on the machine running Compose, from the folder with its compose.prod.yaml and .env. It assumes SQLDesk 0.3 or later; upgrade anything older on Compose first. Move to the same release or a newer one, never an older one: migrations only run forward.

1. Back up, while Compose is still running

# The database. -T stops the terminal mangling a binary file.
docker compose -f compose.prod.yaml exec -T postgres pg_dump -U sqldesk -d sqldesk -Fc > sqldesk.dump

# Uploaded files. If it says there is no uploads directory, skip it.
docker compose -f compose.prod.yaml exec -T server tar -C /app -czf - uploads > uploads.tgz

# The two secrets, as the running server has them.
docker compose -f compose.prod.yaml exec -T server python -c \
  "from sqldesk import settings as s; print('cookieSecret:', s.SECRET_KEY); print('secretKey:', s.DATASOURCE_SECRET_KEY)"

# Stop it. No -v: its volumes are your way back.
docker compose -f compose.prod.yaml down

Keep all three private: the dump and the archive are the whole instance, and the secrets unlock every stored credential. Stopping Compose before minikube starts matters too, because two schedulers would run every scheduled query and send every alert twice. If secretKey prints empty, stop here: the chart treats an empty value as "generate one", and no saved credential would decrypt.

2. Start minikube and get the chart

minikube start --cpus 4 --memory 6g
git clone https://github.com/bot-netizen/sqldesk.git

Give Docker Desktop at least 8 GB of memory first; with less, pods get restarted under load. The clone is the latest release, because main only moves at a release. For another one, add --branch and its tag from Releases.

3. Write a values file

# sqldesk-values.yaml -- private, it holds the secrets
secrets:
  cookieSecret: "<cookieSecret from step 1>"
  secretKey: "<secretKey from step 1>"
host: http://localhost:8080
migrations:
  enabled: false   # until the backup is in; step 5 turns it on

Carry over what else you set in .env. mcp.enabled: true if you use MCP; anything the chart has no value for, such as mail, goes under extraEnv: as NAME: value.

4. Install, then restore into the empty database

helm install sqldesk ./sqldesk/charts/sqldesk -f sqldesk-values.yaml --timeout 15m

# Everything but the databases stops, so nothing touches Postgres during the restore.
kubectl scale --replicas=0 $(kubectl get deploy -l app.kubernetes.io/instance=sqldesk -o name | grep -v -e postgres -e redis)
kubectl rollout status deploy/sqldesk-postgres --timeout=10m

kubectl exec -i deploy/sqldesk-postgres -- pg_restore -U sqldesk -d sqldesk --no-owner < sqldesk.dump

Pods erroring between the install and the scale are expected, since there are no tables yet. Migrations are off for the install because on a first install the job creates the current schema, and a backup cannot be restored over it.

5. Migrate, and bring everything back

helm upgrade sqldesk ./sqldesk/charts/sqldesk -f sqldesk-values.yaml --set migrations.enabled=true --timeout 15m
kubectl logs -l app.kubernetes.io/component=migrate --tail=40

The job finds tables already there, so it skips creating them and migrates the restored database to the chart's release. The upgrade then puts every deployment back at its replica count. Set migrations.enabled: true in the values file as well, or the next upgrade turns migrations off again.

6. Uploads, and open it

kubectl rollout status deploy/sqldesk-server --timeout=10m
kubectl exec -i deploy/sqldesk-server -- tar -C /app/uploads --strip-components=1 -xzf - < uploads.tgz
kubectl port-forward svc/sqldesk 8080:5000

Sign in with an existing account. If MCP is on, kubectl exec deploy/sqldesk-server -- /app/manage.py ai harvest fills the catalog now rather than at the first scheduled harvest.

After the move

• A "create your organization" page means the database is empty: the restore did not land. Do not fill it in. Repeat step 4, read what pg_restore prints, then step 5.
• A data source that cannot connect probably points at localhost or a Compose service name, which inside minikube mean the pod itself. A database on the same machine is host.minikube.internal.
• InvalidToken when a data source connects means secretKey is not the key its credentials were encrypted with. Correct it in the values file and run step 5's upgrade again.
• Going back: minikube stop, then docker compose -f compose.prod.yaml up -d. Compose returns as it was at step 1; nothing done on minikube since is in it.
• Later upgrades: pull the new release into the clone and run helm upgrade sqldesk ./sqldesk/charts/sqldesk -f sqldesk-values.yaml --timeout 15m. Always pass the values file; --reuse-values on its own carries the old chart's defaults forward.
• minikube stop and reboots keep the data. minikube delete erases it.

How much machine you need

The figures below are resident memory measured on a running install at idle, not estimates. Add your own headroom: a worker's memory grows with the size of the result it is holding, and a 3 MB result is an ordinary one.

Three shapes

A team, up to about 25 people. The chart's defaults, with a managed Postgres instead of the bundled one. Roughly 2 vCPU and 3 GB for SQLDesk.

A department, about 100 concurrent. Two server replicas, two worker replicas of four processes each, and a Postgres you did not install with Helm. Roughly 6 vCPU and 12 GB.

500 concurrent. Assuming ~300 of them watching live dashboards and ~25 queries executing at any moment:

About 22 vCPU and 50 GB for SQLDesk, plus the database.

--set extraEnv.SQLALCHEMY_POOL_SIZE=2 --set extraEnv.SQLALCHEMY_MAX_OVERFLOW=3

Scaling

• Queries queueing — more workers.
• Pages slow to load — more web workers, and the connection pool above has to move with them.
• The scheduler is one process. Two would double-schedule everything.
• Give a slow data source its own queue so it cannot starve the others — see Administration for how, since there is no screen for it yet.
• A worker being stopped finishes the query it is running, for up to five minutes, before it exits.
• Uploads pin the workers to one node unless the volume is ReadWriteMany. If you are spreading pods across nodes, that is the setting to check first.

Backups

Back up Postgres, and the uploads volume if people upload files — uploaded files live on disk, not in the database. Keep the semantic/ directory in git if you export the catalog. Redis holds queues, locks and a schema cache — losing it costs in-flight jobs and nothing else, which is why it can be a plain container with no persistence.

Restoring or moving the database needs the same SQLDESK_SECRET_KEY it was made with: data source and alert destination credentials are encrypted with it. To change the key, re-encrypt first rather than after:

manage database reencrypt <old key> <new key>