windmill Documentation

Technical Documentation: windmill-labs/windmill

ℹ️ Provenance: Multi-Tier Ingestion · CodeWiki Reference

Overview (README.md) Copy Section

Open-source developer platform for internal code: APIs, background jobs, workflows and UIs. Self-hostable alternative to Retool, Pipedream, Superblocks and a simplified Temporal with autogenerated UIs and custom UIs to trigger workflows and scripts as internal apps.

Scripts are turned into sharable UIs automatically, and can be composed together into flows or used into richer apps built with low-code. Supported languages: Python, TypeScript, Go, Bash, SQL, GraphQL, PowerShell, Rust, and more.

Windmill - Developer platform for APIs, background jobs, workflows and UIs

Windmill is fully open-sourced (AGPLv3) and Windmill Labs offers dedicated instances and commercial support and licenses.

https://github.com/user-attachments/assets/d80de1d9-64de-4d89-aacd-6df23fa81fc4

• Windmill - Developer platform for APIs, background jobs, workflows and UIs
  • Main Concepts
  • Show me some actual script code
  • Local Development
  • Stack
  • Fastest Self-Hostable Workflow Engine
  • Security
  • Performance
  • Architecture
  • How to self-host
    • Docker compose
    • Kubernetes (Helm charts)
    • Cloud providers
    • OAuth, SSO & SMTP
    • License
    • Integrations
  • Environment Variables
  • Run a local dev setup
    • Frontend only
    • Backend + Frontend
  • Contributing
  • Contributors
  • Copyright

Main Concepts Copy Chapter

1. Define a minimal and generic script in Python, TypeScript, Go or Bash that solves a specific task. The code can be defined in the provided Web IDE or synchronized with your own GitHub repo (e.g. through VS Code extension): provided Web IDE or synchronized with your own GitHub repo (e.g. through VS Code extension):

2. Your scripts parameters are automatically parsed and generate a frontend.

3. Make it flow! You can chain your scripts or scripts made by the community shared on WindmillHub.

4. Build complex UIs on top of your scripts and flows.

Scripts and flows can be triggered by schedules, webhooks, HTTP routes, Kafka, WebSockets, emails, and more.

Build your entire infra on top of Windmill!

Show me some actual script code Copy Chapter

//import any dependency  from npm
import * as wmill from "windmill-client";
import * as cowsay from "[email protected]";

// fill the type, or use the +Resource type to get a type-safe reference to a resource
type Postgresql = {
  host: string;
  port: number;
  user: string;
  dbname: string;
  sslmode: string;
  password: string;
};

export async function main(
  a: number,
  b: "my" | "enum",
  c: Postgresql,
  d = "inferred type string from default arg",
  e = { nested: "object" }
  //f: wmill.Base64
) {
  const email = process.env["WM_EMAIL"];
  // variables are permissioned and by path
  let variable = await wmill.getVariable("f/company-folder/my_secret");
  const lastTimeRun = await wmill.getState();
  // logs are printed and always inspectable
  console.log(cowsay.say({ text: "hello " + email + " " + lastTimeRun }));
  await wmill.setState(Date.now());

  // return is serialized as JSON
  return { foo: d, variable };
}

Local Development Copy Chapter

Windmill supports multiple ways to develop locally and sync with your instance:

https://github.com/user-attachments/assets/c541c326-e9ae-4602-a09a-1989aaded1e9

You can run scripts locally by passing the right environment variables for the wmill client library to fetch resources and variables from your instance. See local development docs.

Stack Copy Chapter

• Database: Postgres (compatible with Aurora, Cloud SQL, Neon, Azure PostgreSQL)
• Backend: Rust - stateless API servers and workers pulling jobs from a Postgres queue
• Frontend: Svelte 5
• Sandboxing: nsjail and PID namespace isolation
• Runtimes:
  • TypeScript/JavaScript: Bun (default); Deno only on the windmill-full images
  • Python: python3 with uv for dependency management
  • PHP: only on the windmill-full images
  • Go, Bash, PowerShell, Rust, C#, Java, Ansible

Fastest Self-Hostable Workflow Engine Copy Chapter

We have compared Windmill to other self-hostable workflow engines (Airflow,
Prefect & Temporal) and Windmill is the most performant solution for both
benchmarks: one flow composed of 40 lightweight tasks & one flow composed of 10
long-running tasks.

All methodology & results on our
Benchmarks
page.

Security Copy Chapter

• Sandboxing: nsjail for filesystem/resource isolation, and PID namespace isolation (enabled by default) to prevent jobs from accessing worker process memory
• Secrets: One encryption key per workspace for credentials stored in Windmill's K/V store. We recommend encrypting the Postgres database as well.

See Security documentation for details.

Performance Copy Chapter

Once a job started, there is no overhead compared to running the same script on
the node with its corresponding runner (Deno/Go/Python/Bash). The added latency
from a job being pulled from the queue, started, and then having its result sent
back to the database is ~50ms. A typical lightweight deno job will take around
100ms total.

Architecture Copy Chapter

How to self-host Copy Chapter

For detailed setup options, see Self-Host documentation.

Docker compose Copy Section

Deploy Windmill with 3 files (docker-compose.yml, Caddyfile, .env):

curl https://raw.githubusercontent.com/windmill-labs/windmill/main/docker-compose.yml -o docker-compose.yml
curl https://raw.githubusercontent.com/windmill-labs/windmill/main/Caddyfile -o Caddyfile
curl https://raw.githubusercontent.com/windmill-labs/windmill/main/.env -o .env

docker compose up -d

Go to http://localhost - default credentials: [email protected] / changeme

Using an external database: Set DATABASE_URL in .env to point to your managed Postgres (AWS RDS, GCP Cloud SQL, Azure, Neon, etc.) and set db replicas to 0.

Kubernetes (Helm charts) Copy Section

helm repo add windmill https://windmill-labs.github.io/windmill-helm-charts/
helm install windmill-chart windmill/windmill --namespace=windmill --create-namespace

See windmill-helm-charts for configuration options.

Cloud providers Copy Section

Windmill works on AWS (EKS/ECS), GCP, Azure, Ubicloud, Fly.io, Render.com, Hetzner, Digital Ocean, and others. Rule of thumb: 1 worker per 1vCPU and 1-2 GB RAM.

OAuth, SSO & SMTP Copy Section

Configure OAuth and SSO (Google Workspace, Microsoft/Azure, Okta) directly from the superadmin UI. See documentation.

License Copy Section

The Community Edition is free to use internally. For commercial redistribution or managed services, contact [email protected]. See LICENSE and Pricing for details.

The "Community Edition" of Windmill available in the docker images hosted under ghcr.io/windmill-labs/windmill and the github binary releases contains the files under the AGPLv3 and Apache 2 sources but also includes proprietary and non-public code and features which are not open source and under the following terms: Windmill Labs, Inc. grants a right to use all the features of the "Community Edition" for free without restrictions other than the limits and quotas set in the software and a right to distribute the community edition as is but not to sell, resell, serve Windmill as a managed service, modify or wrap under any form without an explicit agreement.

The binary compilable from source code in this repository without the "enterprise" feature flag is open-source under the LICENSE-AGPLv3 License terms and conditions.

To re-expose directly any Windmill parts to your users as a feature of your product, with the exception of iframed public Windmill "apps", or to build a feature on top of "Windmill Community Edition" that you sell commercially or embed in a distributable product or binary, you must get a commercial license. Contact us at [email protected] if you have any questions. To do the same from the binary compiled from the source code in this repository without the "enterprise" feature flag, you must comply with the AGPLv3 license terms and conditions or get a commercial license from Windmill Labs, Inc.

To use Windmill "Community Edition" as is internally in your organization, or to use its APIs as is, you do NOT need a commercial license.

Integrations Copy Section

In Windmill, integrations are referred to as resources and resource types. Each Resource has a Resource Type that defines the schema that the resource
needs to implement.

On self-hosted instances, you might want to import all the approved resource types from WindmillHub. A setup script will prompt you to have it being synced automatically everyday.

Environment Variables Copy Chapter

Run a local dev setup Copy Chapter

We recommend using Nix. See ./frontend/README_DEV.md for all options.

Frontend only Copy Section

Uses the backend of https://app.windmill.dev with local frontend (hot-reload):

cd frontend
npm install
npm run generate-backend-client  # or generate-backend-client-mac on Mac
npm run dev

Windmill available at http://localhost/

Backend + Frontend Copy Section

See the ./frontend/README_DEV.md file for all
running options.

1. Start a local Postgres database using for instance the start-dev-db.sh script which will make a database available at postgres://postgres:changeme@localhost:5432/windmill
   Then run the migrations using the following command:
   text Copy

   cargo install sqlx-cli
   env DATABASE_URL=<YOUR_DATABASE_URL> sqlx migrate run

   This will also avoid compile time issue with sqlx's query! macro.
2. (optional, linux only) Install nsjail and have it accessible in
   your PATH
3. Install bun and python3 (+ any languages you want to use), have the bins at /usr/bin/bun and
   /usr/local/bin/python3 or set the corresponding environment variables.
4. (optional) Install the lld linker
5. Go to frontend/:
   1. npm install, npm run generate-backend-client then REMOTE=http://localhost:8000 npm run dev
   2. You might need to set some extra heap space for the node runtime
      export NODE_OPTIONS="--max-old-space-size=4096"
   3. Create an empty frontend/build folder using mkdir frontend/build
6. Go to backend/:
   1. env DATABASE_URL=<YOUR_DATABASE_URL> RUST_LOG=info cargo run
   2. You can specify any feature flag you want to enable, for example cargo run --features python to enable the python executor.
7. Windmill should be available at http://localhost:3000

Contributing Copy Chapter

At this time, we are not seeking outside contribution. Bug reports and feature requests remain very
welcome, and small, trivially-verified PRs that fix a problem are still accepted. See
CONTRIBUTING.md for the full policy.

Contributors Copy Chapter

Copyright Copy Chapter

© 2023-2026 Windmill Labs, Inc.

Agent Worker E2e (`docs/agent-worker-e2e.md`) Copy Section

Running an agent worker locally, for e2e

An agent worker reaches the database only through the API, so whole code paths
(Connection::Http) are never taken by a normal cargo run. Exercising them
needs a real one. Every step below has a failure mode that looks like something
else; they are listed with the error each produces.

1. Build with the right features Copy Chapter

Four features, and the agent's own mode gate is the one that is easy to miss:

cd backend
cargo build --features quickjs,private,enterprise,license,agent_worker_server

• agent_worker_server mounts /api/agent_workers/* on the SERVER. Without it,
  create_agent_token returns 404 with an empty body.
• enterprise + license compile the agent MODE into the binary. Without them
  the worker exits immediately with Agent mode is only available in the EE,
  even though the server side works and mints tokens happily.

Verify before spending time on the handshake — the panic string must be absent:

strings target/debug/windmill | grep -c "Agent mode is only available in the EE"   # want 0

Pin this feature set for the whole session. target/debug/windmill is one
path shared by every feature combination, and cargo swaps the cached artifact in
and out as the set changes — a cargo build --features quickjs (or any build
with a different set) in another pane silently replaces the binary the server and
agent are about to run, and the swap back "completes" in under a second, so it
does not look like a rebuild happened. The symptom is the agent 401ing again
after it had been working, or the EE panic reappearing. Re-run the strings
check above whenever anything unexpected regresses, and start the server and the
agent from the SAME build.

2. Run the server without a local worker Copy Chapter

MODE=server so nothing else drains the queue and the agent is provably the one
that ran the job:

DATABASE_URL=<this worktree's db> PORT=8420 MODE=server ./target/debug/windmill

3. Mint a token — with an expiry, and unquoted Copy Chapter

TOK=$(curl -s -X POST localhost:8420/api/auth/login -H 'Content-Type: application/json' \
  -d '{"email":"[email protected]","password":"changeme"}')

AT=$(curl -s -X POST localhost:8420/api/agent_workers/create_agent_token \
  -H "Authorization: Bearer $TOK" -H 'Content-Type: application/json' \
  -d '{"worker_group":"agentgrp","tags":["dbt"],"exp":1900000000}' | tr -d '"')

Two traps, both of which surface as a bare 401 on the agent and a decoded
reason only in the SERVER log:

• exp must be a real timestamp. "exp": null mints a token the validator
  rejects with Missing required claim: exp.
• The response is JSON, so it arrives quoted. Keeping the " gives
  Base64 error: Invalid byte 34, offset 0 — hence the tr -d '"'.

Pass the token exactly as minted. It looks like jwt_agent_<jwt> and the
client appends its own hostname-derived suffix to form jwt_agent_<suffix>_<jwt>,
which is what the server splits on. Adding a suffix yourself yields
Base64 error: Encoded text cannot have a 6-bit remainder.

4. Start the agent Copy Chapter

WORKER_TAGS must contain the tag the JOBS carry, not a tag you invent — a job
whose tag nothing serves sits in v2_job_queue forever and looks like a hang.
dbt scripts default to the dbt tag.

AGENT_TOKEN="$AT" BASE_INTERNAL_URL=http://localhost:8420 MODE=agent \
  WINDMILL_DIR=/home/$USER/wmagent \
  WORKER_GROUP=agentgrp WORKER_TAGS=dbt PORT=8499 ./target/debug/windmill

WINDMILL_DIR off /tmp matters on a dev box. Jobs fail with IoErr: Disk quota exceeded (os error 122) while writing the project's files, and df looks
healthy — free space and free inodes both. /tmp is a tmpfs and Linux supports
per-user quotas on it, so the limit is the user's, not the filesystem's; several
agent sessions' caches under /tmp are enough to reach it. Point the worker at a
real disk instead of trying to clean up under the quota.

Confirm it registered rather than trusting a quiet log:

SELECT worker FROM worker_ping
 WHERE worker_group = 'agentgrp' AND ping_at > now() - interval '2 min';
-- ag-agentgrp-<host>-<rand>

Reading failures Copy Chapter

The agent only ever prints Agent worker cannot connect to server. Please check AGENT_TOKEN and BASE_INTERNAL_URL. The actual reason is in the server log, from
windmill-api-agent-workers/src/ee.rs — grep it for JWT_AGENT auth error.

Confirming the agent is what ran the job Copy Chapter

worker on the completed job starts with ag-:

curl -s -H "Authorization: Bearer $TOK" \
  "localhost:8420/api/w/<ws>/jobs_u/completed/get/<job>" | jq -r .worker

What dbt does on an agent worker Copy Chapter

Runs, retries, and publishes its graph — including a per-run snapshot for a
dynamic descriptor, which it POSTs to /api/agent_workers/dbt_graph/{workspace}
rather than writing itself.

What it does not get is LIVE progress: the reporter tails a JSON event log and
needs a SQL connection, so per-model state is settled from run_results.json
when the run ends. Retry state lives only in the worker-local generation, since
there is no database row to arbitrate against — which is why state_dir is keyed
by principal.

Confirming a run really exercised that path:

SELECT job_id, count(*) FROM dbt_node WHERE script_path = '<path>' GROUP BY job_id;
-- a row keyed to the JOB id (not the zero UUID) means the agent published a snapshot

Ai Agent Evals (`docs/ai-agent-evals.md`) Copy Section

AI agent evals

A reusable AI agent (docs/reusable-ai-agents.md) can be run on its own, against a curated set
of cases — the inputs it is expected to keep handling.

Three words, and no fourth:

• a case is one input the agent should handle, held in a dataset;
• a run (stored as an experiment) is one execution of a whole dataset: a single flow job
  that answers every case, which is what the UI labels "Run N";
• each case is answered as one iteration of that run.

The surface is a dialog, two screens deep: this agent's runs across every dataset it has been
measured on (one row per run, a badge per scorer), and, on opening a run, one table — a row per
case, a column per scorer, the cell being that scorer's verdict — with a case's detail beside it.
Editing a dataset is a drawer over both. It opens from where an agent already is: the agent card at
the top of an AI agent step's inputs in the flow editor, and the ai_agent row on /resources.

Evals belong to a saved agent. A dataset and its runs hang off an ai_agent resource, so they
outlive the step being renamed, copied or deleted, and two runs are comparable because they name
the same thing. A step whose agent is written inline has nothing to hang them on: what stands in
its place is Save as reusable agent, which is the only setup evals ask for.

What runs Copy Chapter

A run is one flow: a loop over the dataset's cases, each iteration answering its case and then
scoring the answer. Pushed as a RawFlow, so the agent step is the same vehicle
ModuleTest.svelte uses to test an agent step and a case exercises the production branch of
ai_executor.rs rather than a parallel one.

One flow rather than one job per case, because a run outlives the tab that started it: a
dataset of two hundred cases against a slow provider takes long enough that nobody watches it, and
only a worker can notice that the last case finished. Scoring is therefore a step, not something
the client does afterwards, and the run is one thing to watch, cancel, or point a schedule at. Each
iteration is read back by node id (get_result_and_success_by_id_from_flow), so an answer is
fetched without walking the loop's status.

The loop is parallel with a bounded parallelism and skip_failures: a dataset is a burst of
calls to one provider, and one case failing is one cell of the run rather than the end of it.

The cases are the loop's static iterator, so they live in the flow's value, which is stored
once. Passing them as an argument would put a copy of the whole dataset in every iteration's
arguments.

Whichever state of the agent is chosen — what is deployed, the edits in progress, or a past version
— its configuration is fixed once, when the run is opened, and inlined into the step every case
runs. A linked step would resolve the resource when each case reaches it, so a deploy part-way
through a run would be executed by the cases after it while every row still named the version the
run started against. One run measures one configuration; the cost is that a run does not exercise
the linked branch the production step takes. The edits and a past version could not be run any
other way: a reference resolves to what is deployed, which is exactly what neither of them is.

A saved agent's and a past version's configuration are never taken from the request: both are read
from the workspace by the path they name, and a subject carrying one is refused. The edits in
progress are the one kind the request has to carry — they exist only in the editor — and the run
records what it was handed, inlined into its flow and hashed, so it is reproducible and
attributable to "this version plus these edits"; what the server cannot assert about them is that
they derive from that version.

Each iteration is an agent step, then a payload step, then one step per scorer. The payload
step exists because the flow cannot see what it needs to: the agent's own result carries the
answer and every message, but each tool call's arguments, result, status, duration and schema
belong to the job that ran it. The step reads them back through GET /ai_evals/run_payload, whose
one argument is the iteration's own job id, and hands the scorers exactly what a scorer receives
anywhere else. A scorer therefore measures the agent's latency and not its own: the payload
reports the agent step's duration, never the iteration's.

The edits are the transforms as authored, expressions included. One that reads results.<step>.x
or a flow_input the case does not supply resolves to nothing here, the same way it would in any
run of that step outside its flow.

A linked agent is not fully self-contained: a host flow can override its tools' inputs through
the step's tool_inputs. A run does not reproduce that wiring — the agent runs with its own
authored defaults — so an agent whose behaviour depends on one flow's overrides is measured here
without them.

A case carries no conversation: one question and the answer it should produce, so a run starts
from the agent's own memory configuration and nothing is replayed into it.

Where results live Copy Chapter

Results are jobs. A run's logs, trajectory, tool-call child jobs, permissions and retention are
already v2_job / v2_job_completed and the flow status's agent_actions, and none of that is
stored a second time.

What the table itself is made of is the exception: each cell's answer, its outcome and every
scorer's verdict are copied into the run's own rows the first time they can be read. Jobs have
their own retention, and a recorded run is meant to still read as the run it was long after the
jobs that produced it are gone.

The pane shows the answer and nothing else of a job; the trajectory is the run page's, and
job_id is the way there. For a recorded row the answer is read off the row rather than out of
the job, because job_id is the whole iteration — the agent and then the scorers that measured it
— and its result is the last scorer's verdict, not the answer.

What makes a job findable again is stamped on it at push:

• runnable_path is the agent's own path, so the existing script_path_start job filter
  answers "every run of this agent" with no new state.
• _eval in the flow's args records {subject: {kind, path, version}, dataset, experiment_id},
  and every iteration inherits it, so a job opened cold from the runs page explains itself. Which
  case an iteration ran is in its own iter.value, which is also how a cell finds its job again.
  Extra flow inputs are inert — the agent step reads only user_message/user_attachments.

Versioning Copy Chapter

subject.version is the agent's version number: how many times it has been saved. It is counted
per resource rather than read off resource_version.id, which is one identity sequence for the
whole table — an agent saved nine times reads v4 … v24 under it, and the gaps count writes in
workspaces the reader cannot see. The id stays how a version is addressed, by the history routes
and by restore; the number is what a version is called, and what runs are named and compared by.

The number is stored on the row rather than counted when read, because both ways of deleting
versions take the oldest: the monitor's trim past MAX_RESOURCE_VERSIONS, and clearing a history
down to its current value. Counting the survivors would renumber under either, so a run recorded
against v3 would later name a different version.

For an agent run the version names the configuration the run read when it opened, which is the
one every case executes. Pinning an older version is a subject kind of its own — an
agent_version run says which version to read, where an agent run reads whatever is deployed at
the moment it starts.

A version captures the resource, not its transitive closure. Two byte-identical versions can
behave differently because a $var:/$res: they reference changed underneath them, so a
recorded version is necessary for attribution but not sufficient.

Experiments Copy Chapter

Running a dataset produces an experiment: every case executed against one subject, with a row per
case. The experiment records the exact case set it ran, by value — a dataset keeps changing,
and a result set that cannot say which inputs produced it is not reproducible.

What a run is called Copy Section

An experiment is Run N: run_number is allocated per (dataset, agent path) when the run is
opened, once, and never reused. Stored rather than counted at read time, so a run keeps the name it
was given as history is pruned around it. There is no user-given label.

Numbering is per agent and not per subject kind, so runs of what is deployed and runs of the edits
on top of it share one sequence: "Run 7" means one thing, and which of the two ran it is what the
run says beside its number.

A run is permanent Copy Section

Every run is written once and then only ever read: there is no writable experiment, no partial
rerun, and no cell that can be edited after the fact. A run in which some cells came from one
version and some from another would not be worth comparing, and running the dataset is the only
way a run appears.

• One experiment holds one subject, and one agent keeps one history. The experiment list is
  filtered to the agent the pane was opened on, across both kinds, so a dataset shared by two
  agents never shows one agent's runs when the other is opened.
• A version is per cell (eval_experiment_case.subject_version), not per experiment. The
  subject is resolved once when the run is opened and every cell is stamped from it, so the column
  is uniform today; it is per cell so that a run which one day executes cell by cell can say so
  rather than averaging two versions silently.
• Edits are dated by their hash, not by a version, because editing moves nothing a version
  could record. Each cell carries subject_draft_hash, the hash of the configuration it ran
  (canonicalised: key order is not meaningful and serde_json preserves insertion order).
• An agent's unsaved edits are their own subject. Their runs are keyed under agent_draft, so
  a number produced by edits is never quietly read as the deployed agent's. The run dialog offers
  them only when it was opened from the editing card, preselected there; from anywhere else the
  agent is what is deployed.

The table asks what version the agent is on when it opens and whenever the tab regains focus — a
small subject_state read — rather than polling for it; the results endpoint reports the same
version but collects the run as it goes, so it is polled only while a run is in flight, one pass
at a time. An agent saved in another tab while this one stays focused is noticed on the next focus
or the next run.

A run of an older version is history and says so (Run 14 · v23 beside an agent on v24); nothing
flags it, since that would flag every past run the moment anything is deployed. A run whose edits
were later deployed is a run of that version, and the results endpoint recognises and restamps it
(see "What a run says it ran"). The hash each run carries is recorded, not shown.

In the flow editor, editing a linked agent forks the configuration into the step and clears the
link; the step is the only copy of the edits until Save changes, Cancel or Discard
(docs/reusable-ai-agents.md). Evals open from the agent card in both of its states: from the
editing card they run the edits as the step holds them when Run is pressed; from the linked card
they run the deployed agent, the same reading a linked step makes at run time. The agent's own
resource draft — the one the resource editor writes — is never read by evals. The card also names
the version a run is recorded against (v24, and v24 beside an unsaved-changes badge for edits
on top of it), read from the resource's newest history entry since the resource itself does not
carry its version.

Scoring Copy Chapter

Scoring is not a second act with a button of its own: Run produces an answer and then scores
it. Each iteration of the run's flow scores its own answer as a step, and the numbers are harvested
into rows when results are read.

A run's cells are therefore measured by the scorers as they stood when it ran, and never again.
A scorer edited or added afterwards has no cell in the runs that predate it: rescoring a run in
place would make a permanent run editable. The one thing read through the present is the pass line
— pass_if is applied when a score is read, so moving it re-reads every run with no model call.

The columns themselves are the dataset's current scorers, so the table stays comparable across the
runs it lists rather than growing a column per run. Removing a scorer therefore takes its column
off the runs already recorded as well: the rows it produced are not deleted, but nothing renders
them, and adding the scorer back mints a new column that fills from the next run on. The removal
asks first, and says that.

Two things are deliberately absent. Rescoring stored answers under edited scorers would need a run
of its own that reuses a parent run's answers and is attributed to the version that produced them,
so it never reads as the agent having answered again. A result cache keyed on (agent configuration,
case, scorer definition) would assert the agent is deterministic, which it is not, so it has to be
an explicit choice with its own answer to what a run means when half of it was computed last week.

A score is a number, and optionally a line through it Copy Section

Every scorer returns a number between 0 and 1 — both templates say so, and the mean and the
pass rate read it as a fraction; a scorer returning anything outside that range has its result
recorded as an error rather than counted, and a pass_if threshold is held to the same range. Pass
or fail is not a second kind of score: a column carries an optional pass_if, and a case scoring at
or above it counts as a pass. A boolean scorer is one that returns 0 or 1 with the line at 0.5.

A column with a threshold reports a pass rate beside its mean and marks each cell; a column
without one is a plain number and is not dressed up as a verdict.

The line is deliberately outside the score's definition hash: where it sits is an
interpretation of a score rather than part of producing it, so moving it re-reads every run already
recorded with nothing re-run. It is set when the column is added and changed later under Scorer
settings in the dataset drawer, which is also where the column is named.

The name is this dataset's own name for the scorer, seeded from the summary given when it was
added. It is a copy, not a link: the script or judge agent keeps whatever it is called, so renaming
a column here does not rename anything a second dataset shows. Reading it live from the runnable
would cost a fetch per column and leave a column blank for anyone who cannot read what it points
at.

What a scorer receives Copy Section

An agent is judged on its behaviour, so the final answer is the smaller half of the evidence. Every
scorer — a judge prompt or a script — is handed the same EvalRun, built from the job the run
already stored:

Tool results are truncated at 4 KiB with truncated: true, so a large one cannot swamp a judge's
context, and a check that reads a truncated result can say so rather than failing on the missing
tail. A tool whose schema could not be resolved carries null, and a scorer validating arguments
must treat that as unchecked rather than as a failure. There is no cost field: Windmill keeps no
provider price table — the script template takes a rate as an argument instead.

The kinds Copy Section

Two, and both are runnables:

Keeping every scorer a runnable is what makes columns comparable: each has a path, a version, and
code you can open. There is no third kind stored as configuration on the dataset — a judge's model
and grading prompt live on the agent resource, so editing a judge is editing that agent, and the
column is not something you edit at all. Editing a column is editing the runnable it points at, so
the dataset drawer opens it in place: a script in the script editor, a judge in the resource
editor.

Adding a scorer chooses the kind before the form opens: a judge is created next to the dataset
from the model you pick and a grading prompt that starts at the default; a script is created from
the template and opened in the editor. Both are named by a summary of what they score, which
becomes the column header and, prefixed with the dataset, the path.

A reason is worth returning: it is what the cell shows on hover, together with the per-assertion
checks, so a number that looks wrong can be read rather than re-derived from the trajectory.

A scorer may return a bare number, a boolean, or {score, reason, checks}; a judge's answer arrives
under output, sometimes as a string holding one of those, and often as a markdown code fence
around it, which is still read. comment is read as reason, so a scorer written for another
platform keeps its rationale. Anything with no number in it is left empty rather than guessed at,
and means skip the empty ones — a missing score counted as zero would read as a regression.

{score: null} is the one exception, and it means the scorer read the case and had nothing to
measure on it: a column asking whether sources were cited has no verdict on a case with nothing to
cite. The cell shows n/a and is left out of the column's mean and pass rate, which is not the
same as the scorer failing — that is an error, and the column reports it as one. Written out
rather than merely absent, since a scorer that returns nothing at all is a scorer that is broken.

What a run says it ran Copy Section

A run is v15 when it ran the deployed agent and v15 + edits when it ran that version with
undeployed changes on top.

An agent_draft run records the version it is an edit of, because "the draft" is not attributable
without saying which deployed state it is a draft of. It also stops being a draft by itself: the
agent is hashed as deployed, in the same shape a draft is hashed in, so a run whose configuration
was later saved is recognised as the version it became. Edit, run, deploy, and the run you made
reads as v16 rather than staying an edit of v15 forever.

That recognition is written, not derived. When the hashes match, the run's subject is rewritten
to agent at that version, once, keeping the hash it is founded on. Deriving it on every read
would make the answer expire: it would only ever mean "this ran what is deployed right now", so the
next deployment would send a run that already read v16 back to v15 + edits. The write goes to
the unrestricted pool alongside the scores harvested in the same read, and nothing in it comes from
the caller — the hash is the proof, and a run of a configuration that was never deployed simply
stays an edit.

It follows that the resolution needs someone to look: a run is stamped by the first results read
after its configuration is deployed. A run whose configuration was deployed and then replaced
without anyone opening the table keeps saying + edits, which is the honest answer when the only
evidence is a hash that matches nothing deployed.

Reusing a scorer Copy Section

The add form lists the scorers this workspace already uses, most recently edited dataset first,
read out of the datasets' own scorers rather than stored anywhere new. It is filtered twice,
both times by what the caller can read: the datasets are read through user_db, so a scorer only
appears if the dataset carrying it does; then the runnables themselves are checked the same way, so
a script or agent the caller cannot open is never suggested.

A scorer is a column Copy Section

A scorer is stored on the dataset as {id, name?, pass_if?, kind, path}, with the id assigned
once and never reused: on a write, an incoming id is kept only when it names a column the dataset
already holds, and anything else is minted, so a column that was removed cannot come back under
its old id and inherit the scores recorded against it. That id is what makes a column the same
column across experiments when the scorer is renamed or its definition edited, and a delta is only
ever computed between two scores carrying the same id. Two scorers pointing at the same script are
two columns.

A score is keyed (experiment_id, ordinal, scorer_id), not baked into the experiment, so a frozen
experiment can gain a score without becoming mutable in any way that matters: what is frozen is
which runs are in it.

Each score also records the definition that produced it — the kind, the path, and the script
hash or resource version that actually ran, so a path alone cannot hide an edit. When two scores
of one column carry different definitions the delta is still shown, marked: hiding the number
would force model calls just to see anything, and showing it unmarked would let a change of judge
read as a change of agent.

The surface Copy Section

Opening evals selects the dataset this agent was last worked in, remembered per agent in
localStorage and only restored while it still exists and is still readable; no run is opened for
you. The picker lists this agent's own datasets first and everyone else's below, sorted rather
than filtered, since running one dataset against a second agent is a comparison the picker exists
for.

A dataset is named the way a script is: a summary of what the cases are for, from which the
path follows, prefixed with the agent so it sorts with the agent's own. With no summary the
fallback is <agent>_dataset1, taking the next free number. One path segment rather than a folder
under the agent, because a Windmill path is <kind>/<owner>/<name> and the picker that edits it
cannot express a deeper one.

The dataset is edited in a drawer over the table: the summary and the path, the scorers, then
the cases in a grid. Every way of managing a scorer is in that drawer — adding, renaming,
moving its pass line, opening the runnable behind it, removing it; the column header over a run's
table reports and does not edit, since a run is permanent. Creating a dataset is the same drawer
with no cases yet, reached from the dataset named on a row of the runs list and from the run
dialog. Renaming moves the dataset, and its cases and its runs follow through the foreign keys.
The drawer edits a working copy and writes it in one request when Save is pressed — the
rename, the summary and the cases together — so a rename the server refuses leaves the cases as
they were, and a half-finished edit is never what the next run executes. A row's panel in the
results table is read-only and shows the case as the run executed it, not as the dataset holds
it now; deleting a case is in the drawer, and asks first.

A case is its message, what it expects, and nothing else; the message is what identifies it.
expected is what a scorer compares an answer against: plain text, or JSON when the answer has
structure.

The runs list is one row per run of this agent, newest first, whichever dataset it was of: the
run's number and what executed it (v24, v24 + edits, or a pinned v18), how many cases, one
badge per scorer, the dataset, and when. Each badge is the headline that column reports — a pass
rate where the column has a line, the mean where it does not — read through the thresholds as they
are now. A column that never scored a run reads —; a run still going spins. The badges are
named and resolved server-side: a list spanning datasets cannot hold every dataset's scorers to
look a column's name up, so the name and the kind ride along with the number, and the thresholds
are joined in per (run, column) — one grouped query over eval_score rather than a read of each
run's cells. A run whose scores are still in its flow is read out of it by the list itself, capped
per call and skipped for runs already collected, so the steady state is one query.

Run asks two questions: which state of the agent (v24 (latest deployed) as it is saved when
you press Run; a past version as it was then; v24 + edits (current) running the step's edits as
they are when you press Run, offered and preselected only from the editing card), and which
dataset, with an edit button on the row and a way to start a new one without leaving. A pinned
version reproduces the configuration, not the world around it: $var: and $res: references
inside it still resolve at run time. The run that was just started opens straight away.

The results table's rows are the dataset's cases, in dataset order, each carrying its result in
the selected experiment when it has one, so a dataset that has never been run is not an empty
table. A case the experiment ran but the dataset no longer holds keeps its row at the end: the run
happened, and deleting the case does not unmake it. Each column's mean sits under its header, with
its delta beside it when a baseline is selected.

Picking a baseline adds a per-scorer delta to every cell and to each column's mean, and counts the
cells that regressed. Every delta names its scorer; there is no single number for a dataset, since
averaging a judge with an exact match would invent one. Rows are joined by case id, so a case added
after the baseline ran has no delta rather than counting as a change, and a column the baseline
was never scored with reports that rather than a difference that does not exist.

Storage Copy Chapter

Datasets, cases and experiments are rows:

An experiment records its cases by value instead of pointing at eval_case, because a dataset
keeps changing and a result set that cannot say which inputs produced it is not reproducible. For
the same reason case_id is a plain column rather than a foreign key: deleting a case must not
rewrite the history of the runs that used it.

Deleting a dataset takes its cases, its experiments, their recorded case sets and every score with
it through the foreign keys. The jobs those experiments produced are left alone — they are jobs,
with their own retention.

A case is text: a message and an expected answer. Attachments are S3 references rather than inline
bytes, so nothing in a case is meant to be large, and three caps keep it that way — 256 KiB per
case, 16 MiB and 1 000 cases per dataset — all refused at the API rather than truncated. A run
scores every case by every scorer, so a dataset also holds at most 20 scorers, refused the same
way.

Permissions Copy Section

A dataset is permissioned like any other path-addressed object: row-level security on
eval_dataset decides who may see it (readers of its folder, u/<self>, a group, or an
extra_perms grant) and who may change it. Operators cannot write at all. Recording an experiment
counts as a write, since it persists into the dataset.

Cases are the contents of a dataset rather than objects in their own right. eval_case carries a
read policy derived from its dataset (see_parent_dataset) and write policies that check the
dataset is writable — eval_dataset_writable, one function holding the same disjunction the
dataset's own write policies use, so a read-only grant can list a dataset's cases but not edit
them. A dataset and its cases therefore move in one user_db transaction, governed by the same
policies, and a rename is checked against the destination path the same way. The experiment tables
are the exception: their rows are written both by a launch (which holds dataset write) and by the
harvest (which holds only read of the run it copies onto its rows), so they carry read policies
only and are written on the unrestricted pool after the API has checked the right access.

Why an experiment is recorded before it is launched Copy Section

Launching picks the run job's id up front, writes the experiment, its case set and a pending score
per cell in one transaction, and only then queues the flow. Queueing first and recording afterwards
leaves a window in which a flow is running that no experiment accounts for, that nothing will
collect and that a retry would silently duplicate. In this order, a launch that dies before the
push leaves an experiment naming a job that never started — a run that did not run — and a push
that fails deletes it, because one failed push is the whole run.

The dataset's foreign key guards a delete that races the assembly: the transaction fails, and at
that point nothing has been queued. It does not cover a delete that lands after this transaction
commits and before the flow is queued, which cascades the experiment away while the run still
starts.

How a cell finds its job, and its score Copy Section

The flow engine mints the iteration job ids, so a case is recorded before it has one. Three things
fill the gap, each copied out of the flow the first time it can be read:

• Which iteration ran which case. The case is what the loop iterates over, so it is in the
  iteration's own arguments by construction: args -> 'iter' -> 'value' ->> 'case_id' matches the
  cell, whatever order the iterations finish in.
• What the agent answered. The agent step's result and outcome, copied onto the cell as soon
  as that step is done — which is well before the iteration around it, since the scorers are still
  reading it.
• What the scorers returned. Each scorer step's result is read out of the iteration's flow
  status into the pending row that was written for it at launch.

All three are written once, when they first become readable, and every later read is of the rows.
A job that was retained away before anything read it leaves the cell saying so, rather than
looking like a case still being answered.

The flow itself cannot write them: it runs on workers that know nothing about these tables. So two
things call the collector. A run's flow ends with a step that calls POST /ai_evals/experiments/collect on itself, which is what records a run nobody watched finish.
Reading a run collects it too, which covers the run whose flow never reached that step: one
cancelled part-way, or started while nothing served the nativets tag.

That step is bookkeeping, so it is continue_on_error: a run whose every case answered and scored
does not become a failed job because the call did not land.

Ai Session Backups (`docs/ai-session-backups.md`) Copy Section

AI session backups

AI sessions live in the browser: the session list (windmill-sessions), chat transcripts and
image blobs (copilot-chat-history) and artifacts (copilot-artifacts), all per-user IndexedDB
stores. This is the design of their backup in the workspace's object storage, and the
constraints future work on either side must keep.

Backend: backend/windmill-api/src/ai_sessions.rs (/w/{w}/ai/sessions/{list,pull,push}).
Frontend: frontend/src/lib/components/sessions/sessionMirror*.ts.

Why it is lazy Copy Chapter

A session changes at the local write rate: a transcript write every 2 s while streaming, a
session-record write per new message on screen. An object in S3 is replaced whole and every PUT
is billed, so the backup deliberately does not follow that rate. Local writes only mark a session
dirty (sessionMirrorSignal.ts, import-free so the stores never depend on the backup). A flush
runs 15 s after the marks go quiet, at most 2 min after the first unflushed mark, when the tab is
hidden, and 10 s after load for marks a crash left behind. Marks are persisted in localStorage
(shared by the user's tabs) for that reason, one key per mark: a shared blob would let two tabs
marking different sessions at once rewrite each other's mark away. A dirty mark is a counter
bumped on every write; a push retires it by recording the counter it covered on the session's
sync row rather than deleting the mark, since two localStorage calls cannot compare-and-delete
and a bump landing between them would be lost; retired marks are not reclaimed (one small key per
session ever backed up), and the marks of unsent drafts and of workspaces that are off stay too,
each costing one lookup per flush. Only a session gone from the store has its mark deleted. Losing the last
seconds of a device that never comes back is accepted; a tab that closes normally keeps its marks.
A signal names the user whose store the write landed in (read off the store's scoped name), so
a write that completes after the logged-in user changed marks that user's session, for their
next load, rather than the current user's.

A flush plans and sends one session at a time, filling requests of about 8 MB as it goes, so a
first backfill of a large history never holds more than one request's worth of records and
images in memory.

What a push carries Copy Chapter

The pure planner (sessionMirrorPlan.ts) compares each piece against the marker of what was
last pushed, kept per session in the windmill-sessions-mirror store:

All under windmill_ai_sessions/{w_id}/g{generation}/{sha256(email)}/ in the workspace's
primary storage (the generation is what a key rotation moves, see below).
The listing reads only index/: one object per session whatever the session holds, so a
session with many chats cannot crowd newer ones out of a bounded scan, and its
last_modified is the session's updated_at. Written last, and only by an entry no unsent
part follows (a session split over several entries says partial on all but the last, and
names the push on each, or the part is refused), it
lists a session only once a whole push landed; the parts of a session after a failed one are
not written either, on the server within one push and on the client across pushes, so the
marker on the last part never lists a session missing a chat, and a new session whose last part
never lands is not listed at all. A push of the session whole (no sync row, or a stale one,
whole on every part) opens with the head on the first: that part replaces the backup (the
marker goes first, then everything under the session), so what an old storage still held of
the session and the push does not carry is gone. An incremental part rides on a listed
session, and the server refuses it with needs_whole, writing nothing, when none is listed
(a removal deletes the marker first), rather than write a marker over a session missing what
earlier parts or earlier pushes carried; one that changes more than one object unlists the
session before its writes and lists it again after them, so a pull between two of the writes,
or after one failed, finds it absent rather than a mix of old and new pieces (one object
changing is one write, and stays listed). A push split over several parts, whole or
incremental, names itself on each with a token the browser draws (push, opens on the
first): the opening part unlists the session, so a pull between two parts finds it absent
rather than a mix of old and new pieces, the last part lists it again, and a later part is
written only while that token is the one there, so two devices pushing the session at once
cannot list a mix of their pieces (the push that opened later wins; the other is refused with
needs_whole and goes again), and one abandoned leaves the session unlisted, so the next
push of it goes whole. A push and a removal of one session
are serialized on the server by a Postgres advisory lock keyed on the session's prefix, so
the two never interleave object by object.

The head signature leaves out name (a per-browser counter the sessions page routes by),
the unsent-draft fields, workspace_root_id (recomputed on import), and the two fields reading
a session bumps (lastSeenCount, lastActivityAt). Reading a session must never cost a push;
keep that property when adding fields to Session.

Unsent drafts (no workspace_id) and attached files (Blobs, directory handles) are not backed up.

Encryption and access Copy Chapter

Every object is encrypted with a key derived from the workspace key and the user
(build_crypt_with_key_suffix with the email hash), because workspace storage credentials are
shared far more widely than a user's transcripts: public_resource storages and legacy-mode
READ/WRITE hand any member the bucket. The key is per user rather than per workspace so that a
member who copies another user's ciphertext under their own prefix gets nothing from pull; an
object that does not decrypt for its reader is treated as absent. Rotating the workspace key
(set_encryption_key) does not re-key the backups the way it re-encrypts the workspace's
secrets. The objects live under a prefix named by a generation
(workspace_settings.ai_sessions_backup_generation) that the rotation bumps in the
transaction committing the new key; once committed, the routes read and write under the new
generation's prefix, the answers name it (backup_generation, below; storage_id names the
storage and does not change), so every browser marks its sync rows stale and pushes its
sessions whole again there, and every older generation, which nothing writes to any more, is deleted off the
request at leisure (windmill-api-workspaces/src/ai_session_backups.rs). Sessions no browser
holds any more are lost. A generation is never reused, so no deletion, however late, can
touch live objects; a rotation that fails before its commit bumps nothing and deletes
nothing; two rotations racing serialize on the key row; the same key set again bumps
nothing. A rotation is rare, and the alternative,
rewriting every object in place while pushes, restarts, storage switches and further
rotations race the rewrite, is where the complexity would be; with this, nothing but the
current key ever reads an object. The
server builds every key from ids it validated
([A-Za-z0-9_-]{1,64}) and the caller's own email; the client never names a key, and the
workspace storage permission rules are not consulted (the same stance as volumes). Only an
unscoped user token may reach the routes: a job token can carry an on_behalf_of identity and
every scoped token (guest, embed, app policy, MCP) was minted for something narrower.

The backup is keyed by the email like the browser's own stores are (userScopedDb scopes
IndexedDB by it): a user whose email changes starts from an empty history on both sides, and
the objects under the old hash stay in the bucket unread. Carrying them over would need a
server-side re-key (decrypt with the old suffix, encrypt with the new, move every object) in the
email-change flow, which this design leaves out.

An image is accepted only as a base64 data URL of at most 4 MB and stored verbatim, so it
serializes back into a pull answer at its stored size; anything JSON would escape could grow
several times and defeat the pull budget.

push carries owner, the email the browser prepared the batch for, and the server refuses a
mismatch with 409: an in-place account switch must not file one user's sessions under another's
prefix. The client captures its user at flush start and checks every store handle's name
against it for the same reason.

The feature is on wherever the workspace has primary storage, and off with
ai_config.sessions_storage_disabled (the copilot_disabled pattern: no migration, carried by
settings export and the CLI). A build without parquet has no routes (404), a workspace without
storage and nothing to stand in for it answers enabled: false; either turns the backup off
for ten minutes, after which the
page asks again on its own (a flush for whatever is pending, and a restore), and the AI
settings page tells the mirror at once when the switch is saved there (the off state is
forgotten, the rows that went stale are marked again, a restore runs).

The instance store standing in Copy Chapter

A workspace without storage of its own keeps its backups in the instance object store
(object_store_cache_config, loaded the way every other use of it is, so never with
DISABLE_S3_STORE; the plan is checked on every request and Pro never falls back, since a
store loaded before a switch to Pro stays loaded), under the same layout and the same
per-user key,
while the instance setting ai_sessions_instance_storage_fallback allows it (on unless set
to false; the instance settings page shows it under Object Storage). A build without
private has neither workspace storage nor the quota below, and never falls back. Every
answer says which kind of store it came from (fallback), and the instance store is named
(storage_id) by what locates its objects, the endpoint, region and bucket its settings
resolve to, in a namespace of its own: moving the instance store to another endpoint under
the same bucket name is a storage switch for the browsers, and a workspace bucket is never
taken for it. The location is kept with the loaded store, so a server whose reload is still
pending names the store it writes to. A route decides between the workspace's storage and
the instance store from the row it reads the generation from, so a push lands in the
instance store only under a generation read while the workspace had no storage. The store a
workspace's backups live in is resolved in one place
(ai_session_backups::workspace_store), for the routes and for the rotation's deletion of
older generations alike.

Configuring a storage for a workspace that had none (edit_large_file_storage_config) bumps
the backup generation in the transaction that sets it, so everything the workspace left in
any instance store sits under a generation the routes never read again: a later return to
the instance store, whichever it is by then, starts from a newer one. Every storage settings
change then deletes from the instance store, off the request, the workspace's generations
older than the one it committed, whether the setting is on or off (copies from when it was
on may be there). Nothing live is older, whatever happens next: a deletion that is slow, cut
short, or overtaken by the storage being dropped or pointed at the instance store's own
bucket touches only generations nothing reads. Dropping the storage bumps nothing and is a
switch like any other: the rows go stale, the sessions are pushed whole into the instance
store, and the old bucket keeps its copy. On the browser side a removal owed to an instance
store (the row names it apart, storageName in sessionMirrorPlan.ts) is retired by any
answer from the workspace's own storage, since that storage being there means the
generation moved past the copy; one owed to a workspace storage still waits for that
storage, whatever the instance store answered. Copies a deletion missed stay in the
operator's bucket unread, as a deleted workspace's copies do.

On CE the bytes in the instance store count toward the workspace's storage quota under a
storage name of their own (_ai_sessions_fallback_, listed by the periodic recount while
the workspace has no storage of its own, and left out when there are none), so a member
cannot fill the operator's bucket past what the workspace may use; on EE, where workspace
storage has no quota either, nothing bounds them but the per-push caps and the instance
setting.

Conflicts and deletion Copy Chapter

Last write wins across devices. The head carries no manifest; pull lists the session's prefix
instead, so a stale device that renames or archives a session rewrites only the head and cannot
hide chats a newer device wrote. Two devices continuing the same chat still collide.

Restore brings back only sessions the browser does not have (importSessions is write-if-absent,
and skips ids the user deleted in this page) and never overwrites or deletes a local one from
remote state. It covers the workspace and its forks together, and only once every one of them
that keeps backups has listed (a listing that failed leaves the family for the next page load
or workspace switch, or the copy that did list could be the stale one): a session listed by
two of them (moved between them, the old copy not yet removed, since that mark is the moving
browser's, which may never come back) is brought back from the copy that moved last (epoch,
the record's move count, which names the marker), the storage's own modification time
deciding between two of the same count, and not from the other, which would otherwise take
the id first and keep the later copy out for good. A workspace's records land together once
its pulls are done, and just before they do the whole family is listed again (members whose
backups were off included, since a move from another device can land in a workspace between
the first listings and the pulls; a family of one, with nowhere else for a copy to show up, is
not): a session a later copy of which showed up elsewhere is left, with the family, for the
next time. Only a user-initiated deleteSession, and the retention sweep below, remove the
backup; the next push from
another device that still has the session is refused with needs_whole (nothing of it is
written), its row goes stale without a backoff, and that device's next flush sends the session
whole; the workspace-lifecycle
removals (reconcileSessionsLifecycle, deleteSessionsForWorkspace) leave it, so a session
dropped by a wrong reconcile comes back on the next restore. Objects of deleted workspaces stay
in the bucket. A session moved to another workspace is pushed whole into the new one, and once
that push has landed the copy in the old one gets a removal mark of its own, naming the storages holding
that copy (the row that knew is the new workspace's by then), retried independently until
each of them has answered, even when the old workspace's backups are off at the time (they
may hold the copy still). Filing the removal only after the new copy is acknowledged keeps the
session backed up somewhere at every point.

A restore writes a session's artifacts and chats before its record, and records nothing for a
session whose pieces could not be written: recording it would let the next flush push the
half-empty local state over the backup.

Every answer names the storage it came from (storage_id, a hash of what locates the objects,
endpoint, region and bucket, not the credentials, which rotate; the instance store standing
in for a workspace without one is named apart, see above) and the backup generation a
key rotation bumps (backup_generation). A sync row records both, and a row naming another
storage or generation goes stale and its session is marked again: a workspace pointed at a
new bucket, or whose key was rotated, holds nothing, and the server looks nowhere else, so
the next flush carries the session whole. A switch leaves the old copy where it was, so the
row rewritten under the new storage records the old one (alsoIn, one entry per storage
the workspace was on), and a removal is done only once every storage holding a copy answered
it, whatever the generation (a rotation deleted the older generation's copy anyway): each
answer narrows the row to the storages still holding one, and the mark waits for them to
answer, so a switch back never brings a deleted session back. That includes the rows a flush has just written, when a later answer of the
same flush names another storage or the session was pushed in part on top of a row from the
old one; a session whose own parts were answered from different storages is not settled at
all. The listing a restore starts with runs the same check, so a storage switch is noticed at
the first push after it or on the next page load, whichever comes first.

Retention Copy Chapter

ai_config.sessions_retention_days (per workspace, in the AI settings; unset by default;
the sessions_storage_disabled pattern: no migration, carried by settings export and the
CLI; 1 to 3650) puts an age on sessions, counted from their last activity. Each side applies
it with its own clock against its own timestamps, so no clock is compared with another
machine's, and the two do not time the same event: the server counts the last push that
completed, a browser its last local activity, which includes reading new messages and is not
pushed. A backup swept while a browser still reads its copy comes back once that browser
writes to the session again (its incremental push is refused and goes whole):

• The server sweeps the object store (sweep_expired_ai_session_backups, from the monitor
  about every 40 minutes on each server, one pass at a time under a session-level advisory
  lock). For every workspace with a retention it takes the store its backups live in, its
  own storage or the instance store standing in, decided from the row it reads the
  generation from as the routes do, names the users under the generation prefix
  (list_with_delimiter) and lists each user's index/ once: one object
  per session, nothing of what the sessions hold. A session whose marker is older than the
  retention is removed under its lock (lock_session), once its markers, listed again
  there, are still all older: a push that renewed the session between the walk and the lock
  keeps it, and one split over parts either holds the lock or has the session unlisted with
  its token next to the markers (index/{sid}/push), which the sweep leaves alone while the
  token is younger than the retention: an older one is a push a browser abandoned, whose
  landed parts nothing lists, and it goes the same way. Before deleting anything the sweep
  writes a record next to
  the markers (index/{sid}/sweep, not an epoch, so neither list nor pull counts it),
  and remove_session deletes it last: a removal cut short, its markers already gone, is
  found by the next pass and finished, unless a push listed the session again first. At
  most 1000 sessions per workspace and pass; the rest wait for the next. list leaves an
  expired marker out of its answer meanwhile, so a browser never restores a session the
  sweep has not reached. The marker's modification time is the storage's clock and the
  cutoff the server's. The sweep reaches only the backups the routes would: a deleted
  workspace's stay in its storage, and so do those a workspace keeps in the instance store
  once ai_sessions_instance_storage_fallback is set to false.

• The browser sweeps its own stores when a tab resolves the logged-in user
  (sweepExpiredSessions, from the one onUserChange in sessionState.svelte.ts), before
  that tab reads a single session. A session whose last activity is older than the retention
  by the browser's clock is deleted locally, record, chats, images, attached files and
  artifacts. A restored session carries the backup's time as its last activity, the storage's
  clock, so it counts from the later of that and the moment it was restored here
  (restoredAt): a browser clock ahead of the storage's never deletes a session it just
  brought back. Archived sessions count like any other, and persisted unsent drafts by their
  pending workspace.

  The stores are shared by the user's tabs, and each keeps copies of the sessions in memory,
  so every tab holds a shared Web Lock from before it reads them until it stops using them,
  and the sweep deletes only while holding that lock exclusively, requested if available:
  granted exactly when no tab of the user has the sessions loaded, which is why the sweep
  runs where it does and nowhere else. Nothing holds a copy of what it deletes and nothing
  writes the stores meanwhile, so it deletes one record at a time and without re-reading. It
  also takes the tab lock the flush and the restore take, again only if available, so neither
  plans nor stages a session half deleted; like the restore, it does not run where Web Locks
  do not exist. With several tabs open nothing is swept, until one of them reloads alone.

  The hold is only as good as the tabs that take it, so a tab still running a build from before
  it has the sessions loaded and holds nothing. A tab loaded after that one, across a deploy,
  can sweep a session the older tab has in memory, and a write there afterwards brings the
  record back without its chats, which the next flush pushes. It needs a tab left open across a
  deploy, a session untouched for the whole retention, and the user going back to that session
  in the older tab; the next sweep deletes it again. The same window is open to the
  workspace-lifecycle delete in reconcileSessionsLifecycle, which no lock guards at all.

  What deletes is the retention the server gives as the sweep runs, asked for under both locks
  (POST /workspaces/session_workspace_retention, its own route rather than a field on the
  lifecycle status, whose answer a tab loaded before this version still reads). Never a
  remembered one: a retention raised or cleared since would otherwise delete a session that is
  now within it, and a persisted unsent draft has no backup to come back from. What the sweep
  keeps in localStorage decides only whether to ask again — it asks when it has asked nothing
  yet, when the answer it has is a day old, or when that answer marks a session expired — so
  an ordinary load costs no request at all. An answer that does not arrive within five seconds
  leaves the sessions for the next load rather than delete on what this browser guessed. That
  route answers for a workspace the caller can be authed into, unlike the status: a status is
  what to do with the caller's own sessions, a setting is the workspace's to tell, so a
  disabled membership is told nothing though its sessions still reconcile.

  Each session's record goes before its pieces, so nothing plans a push for it afterwards,
  and a localStorage key written before the record and removed once every piece is gone makes
  a later sweep finish a deletion that failed, unless a restore brought the session back
  since. The record is deleted without the tombstone a user delete leaves, which is what lets
  a restore bring it back. The session's dirty mark and sync row go with it (sessionSwept),
  unless the row still carries a removal or a restore's staging. Nothing is sent to the storage: the local
  copy's age says nothing about another device's, which may have pushed the session since,
  and the server applies the rule to the backup on its own. A session swept here that the
  storage still lists comes back on the next restore.

Limits Copy Chapter

Push bodies are packed to about 8 MB (UTF-8 bytes as sent), at most 100 entries, 200 removals and
4000 pieces each (the server's caps, with 32 MB on the body, and 100 chats, 500 images or 1000
deletes per entry, since every piece is an object-store call); an entry that
outgrows the target is split into chat-only parts (the artifacts and deletes on the last, the
head on the last too for an incremental push and on the first part, whatever it carries, for a
push of the session whole), and deletes
past the per-entry cap are carried over to the next push, which the session stays marked for. A chat above
16 MB or a session's artifacts above 8 MB are left out with a console warning; a chat that grew
past the cap after it was backed up has its copy deleted, so a restore never presents the old
transcript as the current one. A 413 fails only the sessions of that request. A
request the server refuses (any other 4xx but 404/403/409) stops the backup for the page but keeps
the marks and the sync state, so the next load tries again; a session the server reports it could
not store stays marked and is retried with backoff. A move files the old workspace's removal
mark before recording the new copy's row, so a mark that could not be written leaves the move
to be planned again. A workspace that answers enabled: false
marks its sync rows stale (the next push after storage returns carries every session whole,
since a new storage may be a new bucket) and leaves its dirty marks where they are (a move into
it must still remember the old copy); it keeps the removal marks of sessions that had been backed
up, so one deleted while backups are off does not come back once they are on, and drops the
removals of sessions never backed up from this browser, so a storage-less instance does not
collect one mark per deleted session forever. A user delete whose removal mark cannot be written
(localStorage full) is carried by the session's sync row instead (removed), which the flush
and the restore read like a mark; a session without a row yet (its first push may be in
flight) gets a row saying only that, and every row write keeps a removal filed meanwhile, so
the push's own row cannot erase it. Pull bodies are
capped at 64 KB. Pull answers up to 20 ids within a 32 MB
budget: a session's size is known from the listings before anything of it is read, one that
would not fit is deferred unless it is the first of the answer, in which case it comes in
pages: the answer carries what fits in key order (at least one object, so every page makes
progress) and names where the next picks up (next, a cursor the browser sends back as
resume with that session alone). Every page carries a fingerprint of the session's listing
(marker, keys, sizes, modification times, entity tags and versions, since a store reports
modification times coarsely and an object rewritten at the same size within that grain would
otherwise fingerprint the same) taken before anything of it is read, and the server
takes it again once the page is read: a page the backup moved under (a push landing object by
object) is read again, a few times, then answered as moved, and the browser starts the
session over on that or on two pages whose fingerprints differ. The browser writes each page's pieces as it arrives, over whatever
an earlier restore cut short had staged (the session is absent locally, so its pieces have no
local edits to keep, and the backup may have moved on), and the record, which is what makes
the session visible, only with the last page. A restore in progress keeps a staging row for
the session (the ids of every chat, image, artifact and version it wrote), which outlives it
if it is cut short; the next restore deletes the staged pieces the backup no longer has, by id
and never by clock, before the record lands (once the record is there no restore looks at the
session again, and a flush would push them back), and a prune that could not run leaves the
session, its pieces and its staging row for the restore after. A restore holds the user's tab
lock while it runs, so two tabs cannot each write the same absent session's pieces over the
other's, and runs only where Web Locks exist (a secure context: https, or localhost); on a plain
http origin the browser still backs up, and its sessions come back on a secure one. A
restore never writes an older record over a newer
one; between pages it holds nothing but the sync
row being assembled, whose chats also admit the images of a later page. Every page carries a
fingerprint of the session's listing (listing), taken before anything of the page is
listed or read, so an object landing after it is in the next page's; a session whose
fingerprint moved between two of its pages (a chat added by another device could sort before
the cursor and be missed) starts over, up to three times, then waits for the next restore. An object that grew
since the listing (a push replaced it) ends its page just before it and the answer names that
spot, so the next page sizes it anew rather than the session being imported without it. A pull sees every key of a session's listing but keeps the 5000 smallest
past its cursor (a page is defined by key order, and the store promises none), so a session
grown without bound by valid pushes cannot grow the answer's memory through its metadata
either; removing a prefix and a rotation's deletion stream their listings. list scans at most 50 000 index markers, keeps the newest 500 as it goes and answers with
them (truncated says when there were more); the restore takes 50 of them. Every read checks the object's size before buffering it: one larger than any push writes
(32 MB) is planted, whatever its listing said, and skipped, since whoever holds the bucket's
credentials can put anything at a predictable key; one larger than its listing said grew
since (a push replaced it) and ends its page, for the next pull to size anew. A dirty mark that cannot be written
(localStorage full) records its bump on the session's sync row instead (extraV, counted
with the mark's counter and kept by every row write, so a push in flight cannot retire it);
a session without a row yet (its first push in flight) keeps the bump in the page, and the
next row write takes it onto the row in the same transaction, so the row the push writes
cannot retire the mark with the bump unseen; every load's backfill marks again a session
without a row, with a stale one, or with one carrying bumps no push has covered.
A mark or removal for another user (a write that landed after a switch) reaches that user's
rows through a connection of its own, since the shared handle follows the current user. Nothing is read past the budget, whatever a session holds. A
restore takes the newest 50 sessions per workspace: every visible session gets a runtime, and
each runtime's history load reads the whole chat store. On CE the push checks the storage quota
and bumps usage by bytes written (an over-count on overwrites; the periodic recount settles it).

App Mode Ai Chat Review (`docs/app-mode-ai-chat-review.md`) Copy Section

App Mode AI Chat Review

This note only tracks the highest-value next steps for making app-mode AI chat
safer and more efficient.

Recommended Next Steps Copy Chapter

1. Add confirmation for dangerous app tools.

   Require explicit user confirmation before file writes, file deletes, backend
   runnable writes, backend runnable deletes, and datatable SQL execution. Show a
   useful diff or exact SQL before applying the action.

2. Enforce datatable SQL safety in code.

   Do not rely on prompt instructions for SQL safety. Classify statements before
   execution, block DDL unless table creation is allowed, and require
   confirmation for DDL, DML, and row-returning reads that would expose data back
   to the model.

3. Keep default context demand-driven.

   Prefer selected context and targeted reads before broad discovery. Keep file
   listings metadata-only, avoid sending full datatable schemas by default, and
   keep SDK/reference material out of the base prompt unless it is requested or
   needed for the task.

4. Improve app context lifecycle.

   Treat @ context as per-message by default, with an explicit pinning affordance
   for context that should persist. Lazy-load file and runnable contents, and add
   a visible approximate context-size indicator so users can spot prompt bloat.

5. Refresh datatable context after mutations.

   Refresh table metadata after data-panel changes and after AI-created tables so
   follow-up tool calls and user-visible context do not use stale schema data.

6. Persist table creation policy explicitly.

   Store whether AI table creation is enabled as an explicit app setting instead
   of inferring it from the presence of datatable configuration.

7. Add focused eval coverage for these behaviors.

   Cover confirmation requirements, datatable SQL policy enforcement, selected
   context minimization, and stale-schema refresh behavior with targeted app-mode
   evals or lower-level tests where practical.

App Policy Email Removal (`docs/app-policy-email-removal.md`) Copy Section

Removing `policy.on_behalf_of_email`

An app's identity is policy.on_behalf_of; the address beside it is a function of that
principal. on_behalf_of_email is no longer required — a policy carrying only a principal
executes, deriving the address — but it is still written on every save, and that is the only
thing holding it in place.

The gate Copy Chapter

get_on_behalf_of gained its derive-when-absent fallback in 1.810. Every replica before that
requires the key and errors outright without it, so it would 400 every anonymous, publisher and
guest app saved by a newer one. A rolling deploy runs both versions at once, which is why the
write stays until no replica older than 1.810 can be live — in practice, once
MIN_KEEP_ALIVE_VERSION (windmill-common/src/min_version.rs) has passed it.

There is no MIN_VERSION_* constant for this and it does not need one: those exist to gate
behavior at runtime or to trip the build when a constraint expires, and nothing here does either.
The key is written unconditionally, so no replica ever meets its absence until someone follows
the steps below.

Step 1 — stop writing the key Copy Chapter

• stored_on_behalf_of_email in windmill-api/src/apps.rs, and the create_app /
  update_app_internal call sites that store what it returns.
• The CLI and frontend workspace-deploy paths (cli/windmill-utils-internal/src/deploy.ts,
  frontend/src/lib/utils_workspace_deploy.ts). These send the address instead of a principal
  for a cross-workspace deploy, which is the one case where it is the only identity available —
  so this is "stop sending it once the target resolves a principal itself", not a deletion.

Policies written before this keep their key and keep being read from it; they agree with their
principal, so nothing has to strip them.

Step 2 — drop the field Copy Chapter

Remove on_behalf_of_email from Policy and the fallback in get_on_behalf_of, which then
always derives. Optionally strip the key from stored policies.

This can ship with step 1. It is written separately because step 1 alone is revertible without
touching stored data or the response schema, and because the gate above is what makes either
step safe — nothing about step 2 needs its own waiting period.

Why the address is not derived on read Copy Chapter

Read paths return the stored address verbatim rather than recomputing it. Deriving on read means
resolving a principal that, for a draft, is caller-controlled — which turns the read into an
oracle for addresses the caller cannot otherwise see, and leaves a principal that resolves to
nobody with no address at all. Both were live defects while the read paths did derive.

Auth Surface (`docs/auth-surface.md`) Copy Section

Auth surface: facts that are easy to get wrong

Symbols, not line numbers, are cited: they drift less.

• Credential precedence (windmill-api-auth/src/auth.rs extract_token): Authorization: Bearer
  → token cookie → ?token= query param. A URL with ?token= is a credential on every route, but
  an existing cookie silently wins over it.
• AUTH_CACHE caches a token's identity for 120 s. Deleting a token row does not purge it: the
  DB trigger (migrations/20260316000001_token_hash_pk_swap.up.sql) notifies only for
  label = 'session' rows, and delete_token never calls invalidate_token_from_cache.
• Sessions are token rows with label='session' plus the HttpOnly token cookie, minted only
  by create_session_token (windmill-api-users/src/users.rs). GET /api/users/refresh_token
  mints one for any non-job token but returns plain text, no redirect.
• tokens/impersonate (superadmin) returns a multi-use token and sets no cookie.
• max_token_expiration_days caps POST /users/tokens/create and tokens/impersonate, by
  shortening the stored expiration (cap_token_expiration), never by refusing: the CLI
  authorization page, wmill user create-token and the editor's language-server token all pick a
  lifetime without reading the setting, and CLIs already installed never will. The CLI signs in
  again on its own when its token expires, which is why the authorization page labels it
  cli-login:, reserved in is_user_token so its expiry does not email the user. A token
  owned by a service account is exempt: one in the workspace the token names, or in any workspace
  for a workspace-less token (for tokens/impersonate, the impersonated account). Any workspace
  admin can therefore create and impersonate a service account to hold an uncapped token, so the
  ceiling bounds personal tokens only. Only the stored expiration is capped: the auth lookup never
  reads the setting, so tokens that exist when it is turned on or lowered keep theirs, including
  none. Deliberately outside it: server-side mints (create_token_internal callers such as native
  trigger webhook tokens, which never expire for GitHub and Nextcloud), and tokens with their own
  fixed lifetime that outlive a short ceiling: sessions (MAX_SESSION_VALIDITY_SECONDS, 3 days, and
  re-mintable through GET /users/refresh_token) and MCP OAuth access tokens (7 days, with a
  rotating 30-day refresh token). Any logged-in user can read the setting through GET /settings/global/{key}, which the token form uses to offer only expirations within it. The
  settings API and config sync reject any value parse_max_token_expiration_days cannot read, since
  the token routes would read it as no ceiling; parseMaxTokenExpirationDays in the frontend must
  accept exactly the same values.
• A token's label decides whether its expiry raises alerts. When delete_expired_items removes
  an expired token row, the monitor emails the owner and raises a critical alert (if enabled);
  rows registered by register_token_expiry_notification also get an "expiring soon" warning first,
  except a token whose whole lifetime fits in TOKEN_EXPIRY_WARNING_DAYS (7), which gets no row
  since the warning would arrive minutes after it was created. Neither happens when is_user_token
  (windmill-common/src/auth.rs) reserves the label, so a token the system mints for itself,
  whether from the backend or from the frontend through tokens/create, needs a reserved label. An
  ephemeral- prefix needs no other change (keep it clear of is_server_minted_label if minted
  through tokens/create); a new prefix also goes into the SQL and Svelte mirrors that function's
  doc lists.
• Every superadmin route refuses a job token: require_super_admin
  (windmill-api-auth/src/lib.rs) errors on authed.job_id.is_some(). A script that needs
  users/create, tokens/impersonate, set_login_type, … must use a dedicated superadmin user
  token stored as a secret, never $WM_TOKEN. Token scopes cannot narrow superadmin routes.
• Write access to a folder steers its readers' AI chat. An ai_instruction resource's body is
  delivered to the global chat of everyone who can read the resource, with no opt-in, whenever the
  chat touches a path under its folder (copilot/chat/folderInstructions.ts); the chat then acts
  with that reader's permissions, outside the folder too. ai_skill bodies are likewise in play
  for every reader until turned off. The resource ACL is the only control, so granting write on a
  folder grants that influence.
• Restricted job tokens (job_token_scopes on scripts and flows): a job's effective scopes
  are stored on its job_perms row at push and minted into its token
  (create_token_for_owner); an empty set is minted as NO_API_ACCESS_SCOPE, since
  scopes: Some([]) reads as unscoped. Every push takes a scope_ceiling, and the job gets the
  ceiling ∩ the target's own setting: flow steps take their flow job's, agent tools their agent's,
  WAC children their parent's, retries the run they replace, and API pushes the calling job's
  (caller_scope_ceiling) — a scoped user or webhook token caps nothing. The row is swept once a
  job leaves the queue, so a restart of a completed flow takes the flow's current setting. Schedules,
  triggers and error/success handlers start from the target's own setting. A restricted token
  keeps only the runtime routes about its own job (is_own_job_runtime_route) and the reads of
  its own flow run (flow_run_read_route_job, checked against its lineage: the orchestrator
  evaluates a step's results.x with the token of the step that just finished). That lineage is
  trusted, so a restricted job token, even an admin's, can only place a job it starts in its own
  run (unclaimable_run_lineage); a route that
  authenticates a token itself instead of through the route layer must call
  check_job_token_scope. A restricted job never runs on a dedicated worker, which uses its own
  unscoped token for every job. $var:/$res: args are resolved through the API with the job's
  own token, so they need read scopes. A deploy that omits the setting keeps the deployed value
  and null clears it, so an unaware client cannot drop a restriction; setting one is refused
  until every worker supports it, since an older worker mints without it. The OIDC
  job_token_scopes claim is the presented token's scopes, not the runnable's setting, so a
  verifier sees what the job could reach and a token minted without the restriction shows as
  unrestricted. The setting belongs to
  the deployed version: an older script hash run by hash runs with that version's setting (a
  restricted caller still caps it). Write scopes on scripts, flows, schedules or triggers let a
  job escape its restriction.
• A remote deploy token is a credential for another instance, held per account and workspace
  (remote_deploy_token, encrypted under the workspace key, re-keyed by set_encryption_key).
  Its email references password(email) with ON DELETE/UPDATE CASCADE, so whatever deletes or
  renames an account takes the token along and a recycled address cannot inherit it; removal from
  a workspace deletes it explicitly (delete_workspace_user_internal, both leave_workspace). The
  row records the target it was granted for, and
  remote_deploy::proxy only sends it to a target still matching the workspace setting, so
  re-pointing the setting cannot redirect anyone's token to a URL of the admin's choosing.
  require_own_credentials refuses job, scoped and read-only tokens (on set_target too): the
  stored token carries none of their restrictions. The proxy turns the local session into a remote
  bearer credential, so every ambient-cookie vector becomes one on the remote: its URL carries the
  row's random proxy_key (a link riding the SameSite=Lax cookie cannot know it; a header would
  do, but the frontend's only per-call hook is the global OpenAPI.HEADERS, whose mere presence
  switches every download to in-memory blobs). The key is served no-store, withheld from the
  credentials require_own_credentials refuses, and masked in this instance's own request logs
  (RedactedUri, used by the request span and the log context); a reverse proxy in front still
  writes the full path to its access log. connect names the target the token was obtained for
  and is refused, before the token is sent anywhere, if the workspace now points elsewhere; the
  token only ever goes to that target. It takes no lock against what clears these rows (a removal
  from the workspace, a target change, a key rotation): their writers take the account, membership,
  key and settings rows in every order, so any lock held there could close a deadlock. A row can
  therefore land after one of them ran, however long its remote call took, and every read
  (load_connection) voids it instead, by identity rather than by comparing times: the connect
  records, before its remote call, the target setting's version (remote_deploy_target_changed_at)
  and the created_at of the owner's membership, and the row counts only while both are still
  the same (or the owner is a superadmin) and it decrypts. So a target set A → B → A, or a
  removal and re-add, voids it whatever the clocks say. A superadmin with no membership is bound
  through the credential making the request instead: the insert requires its token row to
  still exist, and delete_user, offboarding and SCIM removal delete an account's tokens, so an
  address deleted that way and re-created during the call does not inherit the connection (a JWT,
  having no row, cannot connect a workspace its owner is not a member of). leave_instance
  deletes only the password row, leaving the tokens and the memberships, which is not covered.
  Neither is a member's connect across an account re-creation: an address is not expected to pass
  to another person. A disconnect empties and stamps the row
  (inserting one if needed) rather than deleting it, connected_at is when a connect started,
  and the connect's upsert leaves a row stamped after that alone, so an older connect cannot undo
  a disconnect or a newer connect. Connecting by redirect: the remote's
  /user/remote_deploy_authorize page
  mints a token bound to the one remote workspace (remote-deploy:, a label
  reserved in is_user_token so its expiry emails nobody) only on an
  explicit Authorize, only for a callback whose path is /remote_deploy/callback, and refuses to
  render inside a frame; the token travels in the fragment, and the callback checks a single-use
  state the drawer stored, so no other page can plant a token as the user's. The proxy also refuses a path the URL parser would rewrite,
  serves every response under CSP: sandbox + nosniff, forwards only the method, query, body,
  content-type and accept — never this instance's cookie or token — and turns the target's 401
  into a 502, because the browser logs the user out of this instance on an unhandled 401.
• login_type (password table) is a free-form VARCHAR(50). Password login and password
  reset require login_type = 'password'; set_password also accepts pending_oauth and turns
  the account into a password one in the same statement (an account created ahead of its owner
  gets its first credential that way, or through the OAuth claim below).
• Login links (login_link table, POST /users/login_links superadmin-only,
  GET /auth/login_link/{token} unauthenticated): single-use, ≤15 min, a session cookie and a
  302 to a same-origin rd. require_login_type on the mint refuses (409) an account whose
  login_type has moved on — the way a caller re-entering an account it created stops being
  able to once the owner has a password or a provider.
• Pre-approved trial offer (cloud_trial_offer, cloud-only routes under
  /users/cloud_trial_offer): written by a superadmin at provisioning, consumed by
  {consumed: true} or by the portal's refusal; …/go is the one Windmill→portal hop that
  mints a portal login, over the same CUSTOMER_SERVICE_TOKEN trust the onboarding hook uses
  (users_ee.rs, the portal's admin token). It never expires on its own.
• OAuth login (oauth2_ee.rs login_externally, decision in existing_login_decision)
  matches an existing account by lowercased email only. Same provider → login; a
  pending_oauth account (see PENDING_OAUTH_LOGIN_TYPE) is claimed by the first login
  whose address the provider itself asserted and did not mark unverified — login_type becomes
  the client key and the hash is nulled; otherwise require_preexisting_user_for_oauth decides:
  on, every existing account is loggable-into by any provider; off, "exists but with a
  different login type". A new account gets login_type = <client key>.
• OAuth email trust: LoginUserInfo.email_verified is read leniently (bool or
  "true"/"false" strings) and is only consulted for the claim above; only GitHub is filtered to
  primary && verified; a missing email is fabricated from name as <name>@windmill.dev and
  reaches login_externally with email_asserted = false.
• GET /api/oauth/login/{client} is an unauthenticated 302 to the provider — a plain link
  from any page starts SSO.
• CLOUD_HOSTED is presence-tested (windmill-common/src/worker.rs): CLOUD_HOSTED=false
  still enables cloud mode. Of the routes above only the cloud trial offer and onboarding
  profile routes are cloud-gated; for the rest, cloud only adds quotas.
• CREATE_WORKSPACE_REQUIRE_SUPERADMIN defaults to true when unset; only the literal
  "true" enables it when set.

Datatable Viewer Roles (`docs/datatable-viewer-roles.md`) Copy Section

Data table connections and whose roles they hold

A data table under roles (Enterprise Edition) resolves a connection that names a role to that
role alone. A connection that names none is resolved from the data table's default role and the
roles of one identity:

The identity is the job's own, permissioned_as, like everything else a job reaches: the
publisher for a publisher-mode app, the owner of a schedule or trigger, whoever ran a script. It
is the job's viewer only for an inline runnable of a full-code app whose policy entry sets
viewer_data_access (DatatableConnectAs::Viewer). Postgres row-level security then answers per
viewer from a runnable that runs as the publisher, with nothing written in the code.

A connection left with no role at all is refused. Policies test membership, which is true for a
role's own login too, so one policy serves a named connection and an unnamed one:

CREATE POLICY emea ON orders FOR SELECT
  USING (region = 'emea' AND pg_has_role(current_user, 'sales_emea', 'member'));

A policy names its role as text, so renaming the role breaks it.

Code Copy Chapter

What holds it together Copy Chapter

• A default of admin is not a floor. admin owns the tables and bypasses row-level
  security, so nothing could be added to it, and it is the default until someone changes it.
  Such a data table resolves exactly as it did before identity roles existed. Nothing ever sets a
  default back to admin: removing or deleting the default role leaves none.
• One identity decides both the default and the added roles. The default is held when that
  identity may use it, and the roles added are those whose tenants name that same identity. A
  job never mixes its own identity's default with its viewer's roles.
• Connecting as the viewer is the publisher's choice, made per runnable and stored in the
  policy. It is honored on inline runnables only, whose code is deployed with the app: a path
  runnable is someone else's script, which would read with the viewer's roles without its author
  choosing to. The server stamps job_perms.viewer_data_access in the transaction that pushes
  the job. Nested jobs do not inherit it; an inline preview run with the job's token
  (run_inline/preview, what wmill.datatable() uses) does, since that code is the job's own.
• A job that connects as its viewer and has none is refused, never resolved as the job: that
  would undo what the runnable asked for. An anonymous app viewer is the usual case.
• No role at all is a refusal, never an empty answer. There is no login that would read
  nothing: what a role sees is up to each table's grants and policies.
• A set login owns what it creates. Postgres gives a new object to current_user, and
  default privileges go by the creating role, so a table created over a connection that holds
  several roles belongs to that set's login: no other role or identity reaches it, and it passes
  to custom_instance_user when the login is swept. Unnamed connections are for rows. To create
  an object, name a role or run SET ROLE <role> first, which a set login may do for any role it
  holds. Migrations are not affected: one that declares no role runs as admin.
• The viewer is job_perms.end_user_email, and nothing else. The server records it when the
  run starts: the session behind an app request, the caller of a direct run or preview, and for a
  job started with another job's token, that job's viewer. No argument reaches it.
• A cached result belongs to one identity, one viewer and one mode. cached_result_path
  hashes the job's permissioned_as, its recorded viewer and viewer_data_access into the key,
  since a cache hit returns before any connection is resolved. Turning the setting on changes no
  code, so without the mode a result read with the app's roles would be served to the viewer.
• Only a tenant list naming the identity counts. A workspace admin may use every role,
  admin included. The roles added are those whose tenants name the identity (user, group,
  folder, *), and never admin.
• One role is that role's login. Several are a login per distinct set. wm_set_<hash> is a
  member of exactly those roles, with INHERIT; the hash is over their catalog ids, so a rename
  moves nothing and a change in someone's groups resolves them to another login instead of
  editing one. Its privileges are its memberships, so SET ROLE only narrows and RESET ROLE
  comes back to it. The prefix is reserved in validate_role_name.
• A datatable_role_set row with a password names a login that can authenticate with it. The
  row is written after the login exists and removed, in its own commit, before the login is
  dropped or made NOLOGIN. A login without a row is taken back with a new password on next use,
  and so is one whose row has an empty password: that is what a sweep leaves when it could
  neither drop a login nor let it back in, so the next sweep still finds it.
• Set logins are bounded by use, not by the number of role combinations. One exists only for
  a set some identity actually connected with, so there are at most as many as (identity, data
  table) pairs. last_used_at is recorded at most once a day, and sweep_unused_role_set_logins,
  run from the monitor loop, drops those unused for 30 days; a dropped one is made again on
  demand. The sweep goes by this instance's rows, never by the cluster's wm_set_* roles: a
  cluster can serve several Windmill instances.
• Disabling or dropping a role retires every set login holding it. NOLOGIN on a role does
  not reach a login that is a member of it.
• Mixed fleets. A worker or server older than v1.831 connects as the default role alone, and
  refuses a data table with no default role, whose marker it reads as a role the data table
  lacks. One from v1.831 or v1.832 adds the recorded viewer's roles to every job that has one,
  flagged or not, and the job's own roles to none.

Trust Copy Chapter

A job that names no role reads with its own identity's roles, the same trust as the resources and
variables it reaches: every viewer of a publisher-mode app sees what the publisher's roles see.
An inline runnable that connects as its viewer runs its publisher's code with the viewer's reach
into the data table, the same trust as an app in viewer execution mode. Naming a role in the code
pins the connection to that one role either way.

Dbt Runtime (`docs/dbt-runtime.md`) Copy Section

Windmill as a dbt runtime

Implementation spec for running an existing dbt project on Windmill with no
changes to the project itself. Companion to pipelines-vs-dbt.md,
which covers the opposite direction (native pipeline features that replace dbt).
The two are complementary: this is the adoption ramp, that is the long game.

Benchmark to beat is Airflow + astronomer-cosmos,
the dominant way dbt is orchestrated today.

Scope Copy Chapter

• In: run an unmodified dbt project synced into Windmill, one Windmill job per
  invocation, live per-model observability, dbt models as first-class assets in
  the existing asset graph.
• Out: one Windmill job per dbt model, slim CI orchestration, dbt docs
  hosting, semantic layer, dbt platform integration.
• CE: the runtime, the manifest ingest, the asset graph and every piece of
  UI ship in CE, as do all adapters except two. Only the mssql and oracle
  adapters are EE, mirroring the native ScriptLang boundary (decision 21).

Decision log Copy Chapter

Decision 1: engine toggle, and why the shipped default is not Fusion yet Copy Chapter

engine: dbt-core-1x | dbt-core-2x | fusion in the descriptor. Omitted, it is
dbt-core-1x, which runs today's projects untouched.

No engine is baked into any image. Each is fetched or built on first use and
cached, for a different reason in each case.

2.x is the one that could be baked, and deliberately is not: it is a
pre-release (2.0.0-alpha.5) that nothing is defaulted onto, so baking it costs
a layer in every image and a version pinned in two places with nothing keeping
them in step. An operator who wants an engine pre-staged — an air-gapped
instance, or a fleet that should not fetch per worker — populates
DBT_BUNDLED_DIR (default /usr/local/dbt) with core2x-<version>/dbt-sa-cli
in a derived image; the worker prefers it over its own cache.

Two things to know before choosing 2.x: it is a pre-release, and it does not
emit the per-node events the run page animates (see "Live per-model progress"),
so a run on it reports its models only at the end.

The 1.x venv resolves dbt-core>=1.8,<2.0.0 together with the adapter rather
than pinning a core version, because several adapters cap below the newest core
(dbt-oracle and dbt-databricks below 1.12) and an independent pin makes those
projects unprovisionable. The lockfile records whichever version the resolver
actually chose. Both bounds and each engine version are env-overridable
(DBT_CORE_1X_FLOOR, DBT_CORE_1X_CEILING, DBT_CORE_2X_VERSION).

Fusion is the fastest option and the toggle exists so users can choose it. Two
things block making it the shipped default, both verifiable rather than matters
of taste:

1. Redistribution terms. The Fusion license grants only a "limited,
   non-exclusive, non-transferable, non-sublicensable" redistribution right, and
   4.1 forbids introducing "obstacles or delays that have the effect of hampering
   or interfering with (a) communication between Provider and End User, (b)
   User's ability to view, access, or use the Product and/or any Account
   Features." A sandboxed non-interactive job runner sits squarely in that
   clause's path, and "may not share, pool, or relay its own login credentials to
   any End User" reads directly onto putting one dbt platform token in a
   workspace secret. That needs counsel, not an engineering judgment.
   Fetch-at-runtime is the mitigation: the user's own instance pulls the
   binary from dbt Labs directly, so Windmill never redistributes and never
   interposes. Do not bake Fusion into any image.
2. Fusion is v2 semantics, and v2 drops all deprecated functionality. Every
   deprecation warning, including historic ones and those added in 1.10, must be
   resolved before a project runs on it. An arbitrary existing dbt 1.x project
   therefore may not run unchanged, which is this feature's entire premise. dbt
   ships an autofix tool and Fusion/Core interoperate side by side, so it is a
   migration users can do, but not one Windmill should silently require of them.

Consequence: ship with dbt-core-1x, which runs today's projects untouched, and
flip the instance default to fusion once counsel clears the runtime-fetch model
and a real project is verified end to end on it. Both dbt-core engines are
exercised by the e2e suite, so the flip is a config change, not a port.

Decision 21: mirror the native warehouse boundary, do not invent one Copy Chapter

Everything structural is CE: the executor, all three engines, the manifest
ingest, the dbt:// asset graph, live progress, the editor. The only gate is
on two adapters, and it is not a dbt-specific policy — it is the same boundary
the native script languages already draw. Since bigquery and snowflake
became CE, the only warehouse ScriptLangs still behind a license are mssql
and oracledb, so those two dbt adapters are EE and every other one (postgres,
mysql, duckdb, snowflake, bigquery, databricks, redshift, clickhouse,
salesforce) is CE. Gating any of the others would make reaching a warehouse
through dbt stricter than reaching it natively, which is backwards.

Those two are recognized (for the gate and for the pip package the 1.x
engine's venv needs), but no Windmill connection resource translates into them:
an oracledb resource is {user, password, database} with no
host/protocol/service, and dbt-sqlserver needs an ODBC driver the images do not
install. They reach their warehouse through a dbt_profile resource or the
project's own profiles.yml, which is also how duckdb, clickhouse and salesforce
work.

Recognition is what the gate keys on, and it survives the open adapter set: a
dbt_profile stating sqlserver, mssql or oracle resolves to the same
KnownAdapter a resource type would, so it is gated identically. An adapter
Windmill has never heard of is never enterprise — the boundary mirrors the two
native warehouse languages, and an adapter with no Windmill runtime behind it is
not one of them.

The gate almost never fires in practice: dbt-core-2x supports neither adapter,
so it can only apply to dbt-core-1x with one of those two.

The mechanism differs from the native languages. They gate at compile time,
so a CE binary simply lacks the executor. That is not available here: there is
one dbt executor and the adapter is only known once the profile resolves. So it
is a runtime check on the resolved adapter, at both deploy and run, and it must
say what is wrong — a silent degradation that surfaces later as a connection
error is worse than no gate at all.

One trap: ee_oss::LICENSE_KEY_VALID is initialized to true in the OSS
variant, so reading it alone passes on a CE build. The check is
cfg!(feature = "enterprise") && LICENSE_KEY_VALID, which rejects both a CE
build and an enterprise build whose key did not verify.

Decision 11: `dbt://`, keyed on the relation and not on the dbt node Copy Chapter

dbt://<warehouse>/<schema>/<name>, one AssetKind, where <warehouse> is the
workspace warehouse's NAME, so two scripts running against the same warehouse
agree on identity.

The SCHEME names the namespace dbt made, not an exclusive producer. dbt is what
put warehouse relations in the asset graph and is what derives them from a
project; no other language infers one, and calling the kind something generic
promised a parity with native Snowflake and BigQuery scripts that does not exist.
A script can nonetheless DECLARE that it writes one — // materialize manual dbt://<warehouse>/<schema>/<name>, in any language but dbt's own, whose writes
come from its manifest — and that declaration lands on the same node the dbt model
reading the relation does, because identity is the relation rather than the tool.
See "No cascade from dbt" below.

The PATH is the physical relation, and that is the load-bearing half. dbt-core
has no cross-project ref(): two projects meet when one materializes a mart and
the next declares it a source. Their dbt identities differ there —
model.a_pkg.orders against source.b_pkg.analytics.orders — while the relation
does not, so keying on unique_id would make every project an island and turn
the handoff into two unconnected nodes. unique_id also embeds the package name
from dbt_project.yml, which two unrelated projects may both call analytics,
collapsing two different tables onto one node. The relation cannot collide that
way. It is also what a DuckDB, Python, TS or Ansible script can name in a
// on dbt://… annotation to join the lineage — those four are the languages
with a body-asset parser; the native SQL ones cannot declare assets at all.

A dbt run does not trigger those readers. See "no cascade from dbt" below.

An ephemeral model (an inlined CTE, never written), an exposure, or a source that
is not separately modelled has no physical relation and therefore no place in
this namespace. If those ever prove worth rendering they need a key of their own
— unique_id suits them, precisely because nothing else can refer to them.

Two traps, both of which quietly defeat the point if handled wrong.

Identifier canonicalization. manifest.json gives relation_name
pre-quoted ("windmill"."Analytics"."Orders"), an annotation is written by hand,
and the warehouses disagree on case: Snowflake folds unquoted identifiers up,
Postgres folds them down, DuckDB compares case-insensitively. Two spellings of
one table produce two nodes, no edge, and nothing looks broken in isolation. So
one rule is applied in exactly one place — parse_asset_syntax, the single
point where an asset URI becomes a graph key: strip the quote characters
(", backtick, [/]) from the schema and name, then ASCII-lowercase them,
matching the case-insensitive identifier comparison the DuckDB paths already
use. The warehouse-name prefix is spelled as the workspace configures it and
stays case-sensitive.

Warehouse identity is the workspace warehouse's name, exactly as
ducklake://main.orders keys on the workspace lake's name — never the host,
account or database. A descriptor cannot name a resource at all (Decision 24), so
there is exactly one spelling per warehouse and the ambiguity a per-project
resource would create does not arise. The warehouse names the default database
too, so it stays out of the key; a model that overrides
its database (Snowflake database, BigQuery project) is genuinely elsewhere and
qualifies its schema segment as <database>.<schema>, so two same-named relations
in different databases cannot collapse onto one node. A project that brings its own
profiles.yml reports its target's database from that file, read with the same
keys the renderer writes, so it spells a relation exactly as a workspace-warehouse
project does and the two meet on one node. Only where the target leaves its
database implicit does every relation qualify, because assuming they share one
database is exactly what would collapse them.

Three call sites derive this key: the manifest ingest that creates the node, and
the live-progress and end-of-run paths that record status against it. They share
one function, because a site that derives it differently records progress against
a path no node has — the run still succeeds and the graph simply never moves. The same
warehouse is reachable under several hostnames, and credential material has no
business in an asset key. Accepted limitation, worth knowing before it is
filed as a bug: two workspace warehouses pointing at the same physical
warehouse do not unify, so assets under one will not share edges with assets
under the other. Point both projects at one warehouse to link them.

Decision 24: the warehouse is a workspace setting, named, and the only one Copy Chapter

A descriptor names a warehouse by NAME (profile.warehouse, main when it names
none) and cannot name a resource. Admins configure the warehouses under Settings
→ dbt, where each entry points at a resource, exactly as large_file_storage
points at the object-storage resource and a DuckLake names its catalog.

What a warehouse may point at. Either a Windmill connection resource whose
type render_profile translates (postgresql, redshift, mysql, snowflake,
snowflake_oauth, bigquery, gcp_service_account, databricks), or a
dbt_profile resource, whose VALUE IS one entry of that file's outputs
map — type included, nothing lifted out or renamed. A block is copied from a
working profiles.yml and pasted in, which is the whole point: a type that asked
the user to restructure their block first would be doing the translation this
exists to avoid. Its schema declares no properties, so the resource form renders
one JSON editor over the value (ResourceForm.svelte). The picker is
constrained to exactly these (WAREHOUSE_RESOURCE_TYPES); anything else has no
way to become a target at all, which is why an unconstrained picker was a trap:
it offered slack and github resources for a field that can only be a warehouse.

The two exist for different reasons. A Windmill resource is the ergonomic path
and is shared with everything else that connects to that warehouse, but it is
not a dbt target: each adapter arm translates the fields Windmill's resource
happens to carry into the keys dbt reads, so only what an arm covers can be
expressed, and an adapter with no arm cannot be reached from one at all.
dbt_profile inverts that — nothing is translated, so any adapter and any key it
documents works.

Which of the two a value is cannot be read off the value: both are objects with a
type, and Windmill's bigquery resource is a service-account JSON that says
type: service_account. So the warehouse carries its resource's TYPE
(DbtWarehouseConnection.resource_type), and that is also what finally makes
decision 9's "the resource type name is the authority" true at runtime rather
than aspirational — the translated path resolved its adapter by sniffing
connection fields until it had the name.

dbt_profile is open, deliberately. Its type is not checked against a list:
DbtAdapter carries an optional KnownAdapter beside the name, so the eleven
adapters Windmill has facts about (a field mapping, a pip package, the license
gate) keep them, and every other adapter dbt has — trino, athena, spark,
whatever ships next — is carried by name and rendered, licensed and identified
without Windmill knowing anything about it. A closed list would have made
"whatever dbt supports" mean "whatever this enum lists", and each new adapter a
Windmill release. The name is constrained to [a-z0-9_-] starting alphanumeric
because it is open: it reaches a pip requirement and a venv path on the host,
where a leading - is a flag and a / is a path segment.

Installing one is a separate question from using one. dbt-core-1x fetches
dbt-<name> from PyPI, dbt- is not a reserved prefix there, and that install
runs through run_tool — outside the nsjail ordinary Python dependency
installation uses, with uv executing a source distribution's PEP 517 backend. An
unbounded name would therefore let a script author publish dbt-<x> and run code
as the worker, on the one dependency path that is not sandboxed. So
ensure_adapter_installable gates that install on PUBLISHED_ADAPTERS plus
whatever an operator lists in DBT_EXTRA_ADAPTERS: the author chooses which
adapter to use, the admin decides which packages this instance trusts. Nothing
else is gated — a profile still renders for any adapter, and dbt-core-2x and
fusion carry their adapters in the binary, install nothing, and take any
type at all.

Two keys are not passed through: type (Windmill writes the adapter's own dbt
spelling) and root_certificate_pem, which is a PEM body rather than the path
dbt hands the driver — it is written beside profiles.yml and pointed at by
sslrootcert, as it is for a translated postgres resource. profile.schema and
threads from the descriptor override their block keys rather than joining them.

A snowflake_oauth warehouse behaves like a key-pair snowflake one,
although its credential is an access token that lasts ten minutes:

• The token goes under the authenticator each engine reads as an access
  token: oauth on dbt-core 1.x, jwt on the Rust engines. Those read oauth
  as the refresh-token flow and refuse a profile without client credentials,
  while dbt-snowflake has jwt only from 1.9 and the 1.x engine can resolve 1.8.
• Every dbt process that logs in re-resolves the warehouse first
  (PreparedProject::refresh_profile), and resolving an expired OAuth token
  refreshes it. So the build, the after_all tests, a node retry and the
  column-lineage pass each start with a live token, unless resolving fails: the
  process then keeps the profile it already has, token included.
• Run identity masks credentials (RenderedProfile::identity), so a token
  refreshed between a failure and its retry still matches.
• The OAuth connect flow asks for database, warehouse, role and schema
  (resource_fields in oauth_connect.json). No token response carries them,
  and dbt needs a database.

One gap stays: a login after the token its dbt process started with has expired
fails. That is a thread whose first connection opens late in a long process, or
a process's first login when the token it was handed had only seconds left.
Refreshing tokens ahead of expiry would narrow it, but a token's lifetime is not
stored, so no margin fits every provider. Closing it would mean handing dbt the
refresh token and the instance's client secret, which any model can read on
dbt-core 1.x.

Three things follow, and they are the reason for the rule rather than
consequences to work around.

A dbt project carries no connection. The same project runs locally against a
developer's own ~/.dbt/profiles.yml and on Windmill against the workspace
warehouse, with no Windmill-specific file in between and nothing to strip before
committing it to a repository. This is what makes Decision 23 possible at all: if
a project had to name its own resource, the descriptor could never be optional.

Asset identity has exactly one spelling. Keying on a name is only sound
because a name is all there is. Had both profile.resource and
profile.warehouse existed, one physical warehouse would be reachable under two
spellings and two projects on it would silently fail to share nodes — the exact
failure Decision 11 exists to prevent.

dbt is unpermissioned, and the blast radius is bounded by construction
instead. The warehouse resource is read with NO permission check on the runner,
exactly as s3:// reaches the workspace bucket without the caller being granted
the storage resource: configuring a warehouse is what makes it available, and
anyone who may run a dbt script may build with it and read its models. What is
reachable stays bounded because only an admin writes the setting and a descriptor
cannot name a resource, only one of the names an admin configured.

Per-relation rules were considered and rejected. s3:// can enforce a path glob
because Windmill mediates every object operation through its proxy; dbt has no
such chokepoint — Windmill renders profiles.yml and dbt opens its own
connection. A rule could only be a pre-run check against the manifest, and a
pre-hook, a macro or dbt run-operation issues arbitrary SQL on the same
connection, so it would stop the ordinary case while implying a guarantee it
cannot keep.

A project that brings its own profiles.yml still connects with it, and then
names a warehouse only to say where its assets belong. The name must still match
a configured warehouse — a typo is not identity, it strands the project's models
on a node nothing else reaches — but it grants nothing, since nothing here is
granted. It gets no identity by default, because defaulting to main would key a
self-hosted profile's tables onto a workspace warehouse it never connected to.

That label is worth having only because such a project spells its relations the
same way: Windmill reads the target's database out of the project's own file
(Decision 11), so a mart it builds and a workspace-warehouse project's source
on the same relation land on ONE node. Without that the label would name a
namespace and still share nothing, which is the failure it exists to prevent.

An agent worker cannot read the database, so it resolves the name through a
job-scoped API route. That route returns the resolved connection, which is why it
requires a job token: a running job already holds those credentials in its
rendered profiles.yml, and a browsable route would hand them to anyone. The
same worker posts its per-model outcomes to a second job-scoped route, since the
live reporter tails a log straight into the database and cannot run there. An
agent's run page therefore fills in when the run ends rather than during it.
Both routes are posted with the JOB's token: an agent's own credential
authenticates only against the agent surface.

Decision 23: the descriptor is optional, and lives inside the project Copy Chapter

<script>__dbt/wm_dbt.yaml. An unmodified dbt project — one cp -r away from a
developer's working copy, or a repository cloned as-is — is already a complete
Windmill script: it runs the whole project against the workspace's default
warehouse. The descriptor appears only when the project wants something
Windmill-specific: run arguments, a named warehouse, an engine pin, a test
policy.

It lives INSIDE the project rather than beside it so that an author writes
nothing outside the directory dbt itself reads. A dbt developer's working copy
and a Windmill bundle are then the same directory, which is the whole bargain of
Decision 5.

Absent means an empty descriptor, never a missing script. dbt_project.yml is
what identifies a project — the descriptor cannot, being optional — and three
rules keep "absent" from reading as a change: the export omits an empty
descriptor, the sync map gives BOTH sides the empty descriptor an absence means
(so neither reads as an addition), and a pull deletes rather than writes one.
Without all three a descriptor-less project either diffs forever or grows the
very file this decision exists to avoid.

Decision 12: the graph refreshes with the deploy Copy Chapter

The project's files are the script's, so a deploy already sees exactly what will
run: it parses the bundle and stores the graph. "Refresh" is just "redeploy". No
manual button, no webhook, no separate mechanism.

The one case that cannot be settled at deploy is a descriptor that is dynamic by
construction: a vars value spelled with a {{ placeholder }}, or an env
value spelled $var: (re-resolved every run). dbt vars can steer enabled,
aliases, schemas, databases and materializations, so for those the deploy cannot
know what will run and the graph is re-ingested from every run's own manifest,
under that run's job id. A run that cannot refresh those rows fails rather than
showing a stale graph. What the SCRIPT owns stays the deploy's — see "Which
run's graph becomes what the script owns" for why the two cannot diverge.

An agent worker reaches the database only through the API, so it POSTs the graph
it parsed to /api/agent_workers/dbt_graph/{workspace} instead of writing it —
which is why it needs no way to READ the stored relation root: it re-ingests
every run, so its own run page shows the profile it actually used. What it
publishes is that per-run snapshot alone — the path-keyed ownership rows are
written by the deploy and by database-connected workers. Dynamic descriptors and
Windmill-resolved profiles therefore both run there. What an agent does not get is LIVE progress —
that is a per-model event stream, and a round trip per node is the wrong trade —
so its per-model state is settled from run_results.json when the run ends, and
its retry state lives only in the worker-local generation. See
agent-worker-e2e.md.

The refresh happens before the build, from a dbt parse with this run's own
vars and env, so a run in flight is already showing the models it is building.

A dynamic descriptor's graph is a property of the RUN, not of the deployed
version, so it is stored per job — see "The graph belongs to a script version"
below. Two concurrent runs of one such script therefore keep their own, and each
run page shows the models that run built.

Re-ingesting is nearly free: the run parses the project (about a second) before
building it and ingests that manifest.

The parse is what makes a newly added model appear in the same run that builds
it, rather than one run late: the graph is written before the build, so the run
page shows the model while it is being built.

What a share-link viewer sees Copy Chapter

A share link is not anonymous access: the token is HMAC'd with the workspace key
and scoped to one job and its descendants. It is an extra grant for a logged-in
user who lacks access to that job — which is normally why someone was sent a
link.

Both halves of a dbt run page go through one gate: /jobs/run_progress/{id} and
/jobs/dbt_graph/{id}, each behind require_job_read_access, which validates
the token. The graph then has a second, independent filter — RLS on the script
row — and a viewer sent a link usually has no grant there. Deciding the graph's
SHAPE under that filter is wrong: it would answer for the caller's access to the
project rather than for the run they were given, and the Models panel would come
back blank beneath working progress rows.

So a pinned run resolves its version from the JOB ROW, not from script: the
live CTE takes the path and hash the handler read after authorizing the job.
Two things make that safe rather than a widening:

• It leaks nothing new. v2_job_completed.result already carries every
  node's unique_id and relation_name, and this viewer can read it — the
  model set and its relations are already visible to them.
• raw_code is gated separately, on an EXISTS against script in the
  authed transaction. The body of a model is the project's source code and stays
  behind access to the project, whatever the shape query resolved. column_schema
  and the column trace behind /jobs/dbt_column_lineage/{id} take the same gate,
  for the same reason: both are the shape of what the author wrote.

The path and hash coming from the job row rather than the query also means a
caller cannot pin one project's version while naming another's run.

What a dbt job returns, and which half of it is a contract Copy Chapter

The result is {engine, engine_version, command, totals, nodes, invocation_args},
and each node carries both status and outcome.

invocation_args is the arguments the run used, as SUBMITTED — a $var: stays a
reference, so no resolved value is published — and it is omitted when empty. It
exists because a dbt retry restores the failed run's arguments inside the
worker and never writes them back to the retry job, whose own args are just
{"command": {"label": "retry", "dbt_retry_job": ""}}: the row preview, which
is a dbt show of the same project, has nowhere else to get them. On a retry it is therefore ANOTHER
invocation's arguments, which is why a hidden run saves no state at all (see the
retry section).

status is dbt's own word, verbatim — success, error, partial success,
no-op. It is what the log says and what dbt's docs describe, so it belongs in
the result, but it is dbt's vocabulary and dbt may change it: 1.x and 2.x
already differ on casing, and no-op arrived in a minor release.

outcome is the same result in Windmill's terms — passed, failed, warned,
skipped, no_op, unknown — and it is the half a downstream script should
branch on. A dbt release that renames a status moves status and leaves
outcome where it is. Publishing only dbt's word would have made every such
release either a break for users or a lie in our mapping.

The graph belongs to a script version Copy Chapter

dbt_node / dbt_edge are keyed (workspace_id, script_path, script_hash, job_id, unique_id). Each deployed version keeps its own graph, and a job records
the version it ran (v2_job.runnable_id), so a run page asks for that one:
/assets/graph?dbt_script_hash=<hex> renders the project as it was — its models,
its SQL, its ref() lineage — instead of whatever is deployed today.

job_id is the second half, and it exists for dynamic descriptors only. A
{{ }} placeholder in vars can enable a different set of models per run, so
those runs re-ingest; keyed by version alone, each re-ingest overwrote the last
and reopening an older run showed the newer run's project, with any model only
the older run built simply gone. A run of a dynamic descriptor therefore writes
its own snapshot under its job id, and its page reads the graph through
GET /w/{w_id}/jobs/dbt_graph/{id}, passing the version hash.

A static descriptor writes nothing per run: its graph is the version's, under
the zero-UUID DEPLOYED_GRAPH sentinel, and every run of it reads that. The
sentinel is a value rather than NULL because job_id is in the primary key and
Postgres does not treat two NULLs as one key, so a re-ingest would accumulate row
sets instead of replacing one. The route falls back to it whenever the job has no
snapshot, which is why a run page can use it unconditionally rather than having
to know whether its descriptor was dynamic.

Pinning to a run is job-scoped, so it is a job route and not a parameter on
/assets/graph: it needs the whole job-read contract, which is
require_job_read_access. That helper lives in windmill-api, which depends on
windmill-api-assets, so the read moved to the check rather than the check to
the read. The route charges assets:read on top of the jobs:read its URL
implies, since the body it returns is asset data.

A snapshot is only written when it DIFFERS from the version's graph, compared by
a digest of the nodes, edges and relation root. Marking a descriptor dynamic is
conservative — a {{ }} in vars says the arguments reach dbt, not that they
change which models exist — so the usual dynamic run (a date var) resolves to
exactly the graph the deploy stored, and storing that per run would duplicate an
unchanging picture. Those runs write nothing and read the version's graph
through the fallback; only a run whose model set really differs pays.

Which run's graph becomes what the script owns Copy Section

Re-ingesting has several causes and they do not want the same thing, so the
reason is carried rather than a bool (GraphRefresh):

Ownership follows the version's graph exactly, which is what the first three
rows have in common: the workspace graph takes an asset's relations from the
asset rows and its models, SQL, tests and ref() lineage from that version's
dbt_node/dbt_edge, so publishing relations the version's graph does not name
leaves those assets with no model behind them — a placeholder that moves an
alias would empty the current graph of everything dbt contributes to it. A run
storing a snapshot of its own therefore publishes nothing, and an override's
schemas and aliases do not stand as the script's until the next deploy, which is
what a snapshot is for.

The consequence for a dynamic descriptor is that its ownership stays the
deploy's, and a profile that moves under one is settled by a redeploy rather than
by a run: every run of it already shows its own models and re-parses regardless,
so the drift it keeps re-detecting costs it nothing it was not already paying.

The last row is the one that has to publish. The drift check compares the
resolved root against relation_root_at_last_ingest, so a run that saw a move and
did not republish leaves the next run seeing the same move — forever, with the
asset rows still naming the old schema and every run paying a dbt parse for a
snapshot nobody reads. It rewrites the VERSION's graph rather than a per-run
snapshot for the same reason: once the root is republished no later run detects
the move, so a snapshot would leave those runs reading the pre-move rows.

A snapshot wins where they meet: a drifted run that also overrode its arguments,
or whose descriptor is dynamic, snapshots under its job id and publishes
nothing, and the drift is settled by an ordinary run of a static descriptor or
by a redeploy — a wasted parse per overriding run, where the alternative is one
caller's subset standing as the script's own, or replacing the version's graph
with a picture missing every model that run did not select.

Both halves have a retention story, and they differ because their readers do. A
run's snapshot expires on a clock — 30 days — because the run page that reads it
is transient. A VERSION's graph cannot: its reader is every finished run of that
version, and a run page is as old as its job. So version graphs are bounded by
deploy COUNT instead — the newest 50 per path keep theirs — which makes growth
versions x models rather than unbounded in time. Without it a CI deploying on
every commit adds a full model set per commit and nothing ever reclaims it. The
bound is generous on purpose: reaching it empties that version's run pages, so
it exists to stop unbounded growth rather than to be hit in normal use.

Both are pruned by every dbt run, so no background sweep has to know about the
tables. The prune is
deliberately not hung off the progress reporter, which exists only for engines
that emit node events: retention that stops working because an instance chose
Fusion is not retention. A version's own graph lives as long as the version.

The third provenance: a parse of the editor's buffer Copy Section

The dbt editor draws a graph of the project as it is in the editor, refreshed
on demand by a dbt_command: "parse" job over the buffer — the deploy's own
deps → parse → ingest path, with no build. That graph is neither of the two
above: the buffer differs from what is deployed, which is the point of
refreshing it, and a project being written may have no deployed version at all.

So it is keyed to its own PREVIEW JOB with no version — script_hash IS NULL — and readable only back through that job id
(GET /jobs/dbt_graph/{id}), never through the path. That is what keeps the
property GraphPublisher::Unversioned exists for: a parse publishes no
path-keyed asset usages and no relation root, so a principal who needs only
jobs:run still cannot restate what a deployed project's graph says.

Three consequences of the version being absent:

• script_hash is nullable, so the primary keys of dbt_node, dbt_edge
  and dbt_graph_snapshot became two partial unique indexes each — versioned
  rows keyed by their version, editor rows by their job alone. The composite
  foreign key to script is unchanged: MATCH SIMPLE is satisfied by a NULL,
  so a versioned row still cascades with its version and a version-less one is
  outside its reach. A partial arbiter also has to be named, so the marker's
  ON CONFLICT repeats WHERE script_hash IS NOT NULL.
• Being outside that cascade, they need clearing by hand. A route that
  deletes the script rows outright reclaims the versioned graph through
  ON DELETE CASCADE and deliberately locks nothing ahead of the script row;
  a version-less row references nothing, so it would survive its own script.
  The delete-by-path and bulk-delete routes therefore call
  clear_dbt_editor_graphs — AFTER the delete, beside the retry state, since
  every dbt writer takes the script row first and a sidecar taken ahead of it
  deadlocks one of the pair. Archiving clears neither: it leaves the script
  row, and both graphs still answer for finished runs.
• No digest suppression. A run's snapshot that matches the version's stores
  nothing and reads the version's back; an editor parse always stores, because
  the editor pins to its own job and a suppressed write leaves it nothing to pin
  to — and its provenance label would then claim a parse that is not on screen.
• Bounded per (path, PRINCIPAL), not by age: the newest
  DBT_EDITOR_GRAPHS_KEPT parses of one script by one identity keep their
  graph, dropped as each refresh lands, since the ones before it are dead the
  moment a newer parse arrives. The principal is load-bearing rather than
  incidental — a preview's PATH is chosen by a caller who needs only jobs:run,
  so a count bounded per path alone is a way to retire the graphs of whoever is
  actually editing that script. permissioned_as is the execution principal the
  queue derived, which is why dbt_run_state keys on it too. The instance-wide
  age sweep every dbt run performs still catches one refreshed once and left.

A parse of a job that DOES name a deployed version — the scriptable form, from
a flow or the CLI — writes an ordinary per-run snapshot of that version instead,
suppressed when it agrees with the deploy. Either way it publishes no ownership:
a parse answers for the arguments it was given, so it can no more stand as what
the script owns than an overriding run can.

Which graph is on screen is stated rather than left to be inferred — "parsed
from the editor at 14:32" against "as of last deploy" — from
dbt_graph_ingested_at on the graph response. The two are drawn identically, so
without the label the ambiguity the explicit refresh removes would just move
into the editor.

A parse renders profiles.yml before dbt runs, so it needs a resolvable
warehouse and a misconfigured project fails a refresh the way it would fail a
run. That is useful early feedback, and the empty state says so.

Per DEPLOY, not per run: ten thousand runs of one version share one graph. The
rows carry a composite foreign key to script (workspace_id, hash) with
ON DELETE CASCADE, so a version's graph dies with the version and nothing has
to sweep it.

The routes that hard-delete a script rely on exactly that for the VERSIONED
rows and clear none of them. Clearing them first would lock the sidecars ahead
of the script rows, the reverse of the order a publication takes — script
row FOR UPDATE, then the sidecars — and Postgres would abort one of the two
for deadlock. So anything the cascade cannot reach is cleared explicitly and
AFTER the delete, which keeps that order: dbt_run_state (keyed by path, no
script key), and the version-less editor graphs, whose NULL script_hash
satisfies the composite key without referencing anything (see "The third
provenance" above). dbt_run_progress (keyed by job, no key to either) is
reclaimed only by its age sweep.

Two consequences worth knowing:

• Concurrent deploys no longer race for the graph. Two versions write
  disjoint rows, so neither can lose. claim_graph_publication survives only for
  what is still keyed by PATH — the asset usage rows, of which there is one set
  per script — and an older deploy finishing late now records its own graph
  before declining to touch those.
• A pinned request is scoped differently. Unpinned, the endpoint scopes by
  the relations in view, using asset. Pinned, asset is the wrong scope: it
  describes the current deploy, so a model that version had and a later one
  dropped would be filtered out of its own run's graph. The pinned version's
  nodes are the scope instead.

No cascade *from* dbt, and no pipeline membership Copy Chapter

A finished dbt run does not trigger anything. Its models are recorded, drawn and
tracked; they do not fan out. The opposite direction does: a script that declares
// materialize manual dbt://<warehouse>/<schema>/<name> is an ordinary producer
of that relation, and its completion wakes # on dbt://<relation> subscribers
through the same fan-out every other asset kind uses.

A dbt script is also not a pipeline member (in_pipeline is forced false for
ScriptLang::Dbt at deploy). It materializes warehouse tables, so it looks like
one, but that membership carries an editor whose premise is that you author the
transforms in it — and a dbt project is authored in a local dbt run / dbt test loop, with Windmill as the runner and the viewer. Enrolling it put a dbt
project inside the pipeline editor and blurred which of the two a folder holds.
Its models are dbt:// assets in the shared graph regardless: that is what
puts a native script reading one of them on the same node, and it is independent
of pipeline membership.

dbt already orders its own DAG, so a cascade out of a run would only ever add one
thing: waking a Windmill script that reads a mart. That edge is real but narrow,
and dispatching it correctly is not cheap. A run's select can build any subset
of the project, so the deploy-time write set is not what ran; using it wakes
consumers of relations the run never touched, and narrowing it needs a per-job
record of what was built, which the per-relation state table cannot supply (it
keeps one row per relation, stamped with the last writer).

So dbt materializes and reports, and asset_dispatch returns early for
ScriptLang::Dbt. Wiring it up later means deciding what a selective run should
notify — that decision is the work, not the plumbing.

Declaring the write, and which subscriptions are refused Copy Section

// materialize manual dbt://<warehouse>/<schema>/<name> is how an ingestion
script says it writes a warehouse relation. manual is not a mode but the only
mode: nothing generates warehouse DDL, so the script issues its own write and
Windmill records the outcome — the same materialized_partition row a DuckLake
target lands, so the relation carries a last writer on the run page and the graph.
It is language-agnostic (the DuckLake write ENGINE is DuckDB's; this declaration
is anyone's but a dbt project's, whose writes are read from its manifest), and the
recording happens in the generic job path
(record_declared_warehouse_write) rather than in an executor, for the same
reason. Identity is unchanged — the physical relation — so the ingestion script
and the dbt model reading it are one node, and a source declared on the relation
puts the whole thing on one lineage. // data_test is refused beside it: those
checks are probes the DuckDB executor splices around a managed write, so on a
warehouse relation — which the script writes itself, from any language — nothing
would run them, and a declarer would deploy green with its assertions silently
skipped. Assert on the relation with a dbt test in the project that reads it.
The <warehouse> segment is resolved at
deploy for the same reason a descriptor's profile.warehouse is: a name no
warehouse answers to is not a namespace, it strands the write on a node nothing
else reaches.

Known boundary, shared with every other runtime pipeline annotation: the record
is written from the normal execution path, and recording and cascading are decided
separately, so the routes off it differ.

• A dedicated worker never enters that path — it bypasses the record exactly
  as it bypasses // partitioned resolution — while its job is still a top-level
  Script, so the fan-out (which reads the deploy-time asset rows) runs. It
  cascades and records nothing, leaving the relation with no last writer.
• A flow runner bypasses the path too, and is routed by flow_step_id, which
  is_eligible_kind rejects. Neither record nor cascade.
• A flow step running a deployed script enters the path as a Script job, so
  it records — and carries a flow_step_id, so it never cascades.
• A flow step with an inline body is a FlowScript job, which the recording
  guard excludes along with previews: neither.

Fixing the recording half is one change for every runtime pipeline annotation,
not this one.

A # on dbt://<relation> subscription is held to the same relation a producer
is — a whole <warehouse>/<schema>/<name> under a configured warehouse, checked
by the validator the // materialize target goes through, since two spellings of
that rule would refuse and accept the same string. Beyond that it is refused in
exactly one shape: when every script that writes that relation is a dbt one. Nothing
produces it yet is NOT that shape — a subscriber may be deployed before its
producer, as for every other asset kind, and refusing there would break
deploy-order-independent syncs. A dbt script may neither subscribe nor declare a
// materialize: its graph ingest republishes that path's trigger and asset rows
wholesale, so either annotation would deploy something the dependency job then
silently removes — while the declared write would still stamp the relation on
every run.

The producer set is read as it stands committed, minus the deploying script's own
rows — those describe the version being replaced, so a script dropping its
// materialize while adding a subscription would otherwise count itself as the
producer that wakes it, which it could not be anyway (the dispatcher skips
self-loops).

What that leaves is a subscription accepted while it was live and later orphaned.
A dbt project that claims the relation afterwards names those edges in its own log
rather than leaving them silently dormant — the same "an edge that can never fire
is worse than saying so" the refusal is for, at the other point where it is
knowable. Both points that publish ownership warn: the deploy, and a run whose
static descriptor found its profile moved. An agent run publishes none — it is
forced to per-run models, so it stores a job-pinned snapshot and leaves workspace
ownership with the deployed graph — so it cannot orphan a subscription either.

Two orphanings are reported nowhere, and both are accepted rather than overlooked.
A native producer that drops its // materialize and leaves dbt alone on the
relation: the deploy that causes it does not touch the subscriber. And the
interleaving where a dbt ingest commits between a subscriber's producer check and
its own commit — the check sees no producer and accepts, the ingest's warning
query sees no trigger and says nothing. Closing the second means a per-relation
lock shared by the deploy path and the ingest, and the ingest takes
script … FOR UPDATE before its own advisory lock, so a deploy holding relation
locks first inverts that order into a deadlock across two subsystems — a worse
failure than the cosmetic edge it would prevent. Both are bounded the same way:
the next deploy of that project warns, and the canvas is where they show
meanwhile.

A plain READ still renders the consumer beside the model, which is what makes
the lineage one graph — but it is written in the script's own code, not in a
comment: the body parsers resolve an asset URI from a string literal
(parse_asset_syntax), so "dbt://<resource>/<schema>/<name>" appearing in a
Python, TS/Bun/Deno, DuckDB or Ansible script is the read. Those four are the
languages with a body-asset parser; the native warehouse ones (snowflake,
bigquery, postgresql, mysql, mssql) declare no assets at all today, so a mart
they consume joins the graph only once that inference exists — while a relation
one of them WRITES joins it now, through the annotation.

Live per-model progress, and why only dbt-core 1.x has it Copy Chapter

DbtEngine::emits_node_events() is true for dbt-core-1x alone, so only 1.x
moves nodes on the run-page graph while it builds. The other two engines settle
every relation at the end instead.

That is a statement about where the engines put their events, not about
whether they produce them. Both Rust engines emit exactly the structured node
events the tailer parses:

$ dbt-sa-cli build --log-format json   # and likewise the fusion binary
{"info":{"name":"NodeStart"},"data":{"node_info":{
  "node_status":"started","unique_id":"model.probe.m3",
  "node_relation":{"relation_name":"windmill_dbt_runtime.probe_sch.m3", ...}}}}

Measured on 2.0.0-alpha.5 and fusion 2.0.0-preview.202, a three-model project:
15 node events each on the console, 0 in the file log. --log-format-file json
is accepted by both — json is a listed value — and ignored: the file is text
either way.

The events are therefore only on stdout, which is the human-readable job log.
Taking them would mean setting --log-format json and rendering the log
ourselves from each event's info.msg, so the run's log stays readable. That
buys live progress on two pre-release engines at the price of permanently owning
log presentation, to work around something upstream has already declared it
intends to support. Not worth it: when either engine honours
--log-format-file json, flipping emits_node_events() is the whole change,
and the existing tailer starts working untouched.

A finished run is unaffected on every engine — it is coloured from the run's own
result, not from these events (decision 11's note on run_progress).

Where the dbt project lives Copy Chapter

In Windmill. One dbt project is one Windmill script: the script's content is
the descriptor, and the project's files ride with it as its module bundle, a
path-keyed map the worker materialises into the job directory before invoking
dbt. There is one way to do this. Nothing is cloned, so there is no repository
resource, no ref, no commit and no clone cache.

A team whose repository must stay canonical keeps it: git-sync points at that
repository and pushes it into the workspace, so the repository still holds the
truth and Windmill receives the project. A team with no repository at all pushes
straight from a working copy.

On disk, the project is a canonical dbt project Copy Section

wmill sync pull writes the bundle verbatim, so the tree under the module folder
is exactly what dbt expects, with the extensions dbt expects:

f/analytics/
└── analytics__dbt/                 the module bundle: the project, unmodified
    ├── wm_dbt.yaml                 the descriptor (the script's content) — OPTIONAL
    ├── dbt_project.yml
    ├── packages.yml
    ├── models/staging/stg_orders.sql
    ├── models/marts/_marts__models.yml
    ├── macros/cents_to_dollars.sql
    ├── seeds/country_codes.csv
    └── snapshots/orders_snapshot.sql

Import is therefore a copy, never a transformation:

cp -r my-dbt-project/. f/analytics/analytics__dbt/
wmill sync push

The descriptor is optional, and nothing above it is authored: an unmodified dbt
project is already a complete Windmill script, running the whole project against
the workspace's default warehouse. wm_dbt.yaml appears only when the project
needs something Windmill-specific — run arguments, a named warehouse, an engine
pin — and it lives inside the project so that a dbt developer's working copy and
a Windmill bundle are the same directory.

Locally, dbt runs against the bundle with --project-dir analytics__dbt (or a
cd), which is what a monorepo holding several dbt projects already does, and
what dbt Cloud exposes as its "project subdirectory" setting.

Why a module bundle rather than one script per model. Models as scripts was
considered and rejected on three counts. A Windmill path admits no dots, and the
CLI rejects bare .sql as ambiguous (.pg.sql, .duckdb.sql, … are the
convention), so a model could only be typed by its location inside the project,
which breaks the rule that extension determines language. dbt resolves ref()
project-wide and cannot run a model alone, so each model job would reassemble
and reparse the whole project anyway. And schema.yml describes many models at
once, so splitting models into objects while their tests and docs stay in shared
YAML puts a model's contract in a different object. The bundle keeps the project
whole, and per-model execution is offered as an action on the graph node
(--select <model>+) rather than as a separate object.

What that costs, stated plainly: a model has no permissions or version history of
its own. The unit of both is the project.

Consequences Copy Section

The version is the script version. Deploying the script deploys the project
atomically; rollback is redeploying a previous version. The lockfile keeps the
resolved engine and adapter versions and the manifest digest.

Windmill holds the files, so the graph can show them. A model's compiled SQL
is readable from its node in the asset graph. A dbt project has its own editor —
the file tree, the descriptor, the run arguments and the model graph, which is
the artifact's actual shape — but it is not where a dbt project is developed:
that is a CLI loop against a local warehouse (dbt run --select, dbt test).
Windmill is the runner, the viewer and the place a project is corrected.

Two scripts against one project means two copies. Splitting a project across
scripts, so an upstream selection and a downstream one compose, assumed a shared
repository. With bundles they would duplicate the project and drift. Prefer one
script per project with per-run select, and treat two scripts as two projects
(decision 6).

Seeds are the only thing that can bloat a version. Measured on real dbt code,
.sql files run about 500 bytes median and 1.9 KB at p90, so even a 5000-model
project is a few MB before compression. A single committed CSV can exceed all of
it, so the CLI drops any file over 5 MB from the bundle and says which, rather
than counting models.

Only text is carried. A dbt project's authored files are text; a binary one
(an image under docs/, a stray .DS_Store, a parquet seed) is skipped with
the reason. Left in, it would be read as mojibake and, if it carried a NUL,
rejected by Postgres with an opaque unsupported Unicode escape sequence.
Binary is detected the way git does it, by a NUL in the first 8000 bytes,
because docs/ and dotfiles do not follow extensions. The push, the staleness
hash and the sync diff share one predicate: a file one drops and another keeps
is a change no push can resolve.

Secrets are not carried. .env, .env.* and .envrc are skipped with the
reason. The import above copies whatever the checkout holds, and what a
.gitignore was keeping out of the repo is exactly the file that must not
become a script version, readable by anyone who can read the script and handed
back on every pull. dbt does not read them either — env_var() takes the
process environment, which Windmill fills from the descriptor's env and the
script's environment variables.

dbt_project.yml is rendered before it is read. dbt allows env_var() in
that file, so a project may name its profile or its packages directory through
one. Windmill renders those two settings against the environment the run gives
dbt before acting on them: reading the template instead leaves a rendered
profiles.yml keyed under a name dbt never looks up, and a package cache
watching a directory dbt deps never fills.

The script artifact Copy Chapter

New ScriptLang::Dbt. Content is a YAML descriptor whose field names track dbt's
and Cosmos's vocabulary so the mental model ports without translation:

engine: dbt-core-1x               # or dbt-core-2x | fusion
profile:
  warehouse: main                 # a warehouse configured on the workspace, by
                                  # name; omitted takes `main`
  target: prod
  # schema: marts                 # target schema; REQUIRED for BigQuery, whose
                                  # resource is a service-account JSON with no
                                  # dataset in it
  # profiles_yml: profiles.yml    # alternative: keep your own file; it then
                                  # names a warehouse only to say where its
                                  # assets belong (see below)
select: ["tag:nightly+"]
exclude: []
test_behavior: build              # build | after_all | none
column_lineage: false             # opt in to the static-analysis pass that
                                  # produces column-level lineage (decision 14)
vars:                             # typed: numbers/bools/lists keep their type,
  run_date: "{{ run_date }}"      # and string leaves take job arguments
  strict: false
threads: 8
full_refresh: false
env:                              # for the project's own `{{ env_var() }}`
  DBT_PASSWORD: $var:u/rf/wh_password

env values spelled $var: are resolved to that Windmill variable, so a
project keeping its own profiles.yml never needs a credential written into the
descriptor — which is versioned script content. Both this map and the script's
own environment variables apply to the deploy-time parse as well as the run, so
an env_var() feeding a schema, alias or enabled produces the same relation
in the stored graph and in the build either way. Prefer the descriptor's env
when the value belongs to the project rather than to one deployment of it: it is
versioned with the descriptor, so a redeploy from git carries it.

select/exclude/selector are passed verbatim to dbt. Do not reimplement
the selector grammar; Cosmos's manifest path had to, and it is a recurring source
of divergence. One thing is decided before dbt sees them: a run that spells out
select or exclude drops the descriptor's selector, because dbt resolves
--selector instead of --select and passing both would silently build the
descriptor's nodes rather than the ones the run asked for. "Spells out" means
DIFFERS from the descriptor's own value, not merely "was submitted": the
generated run form posts a default back for every field left untouched, and a
selector descriptor's select default is [], so reading a submitted [] as
an override dropped --selector from every run started from the UI, a schedule
or a webhook and built the whole project. A run that wants the whole project
despite the selector asks for it with a selection that differs — ["*"].
select and vars are overridable per run via job args. The graph stays the
deployed descriptor's: asset rows are written at deploy, like every other
language's, so a run-arg override changes what gets built without changing what
the graph says the script owns. Split the project into several scripts
(decision 6) when the graph itself should differ.

A vars override does re-ingest — vars steer enabled, aliases, schemas and
materializations, so the deployed graph would name another run's relations — but
under the job id alone, never as what the script owns: publishing an override's
relations would leave them recorded for the next default run, which then builds
the descriptor's while the graph shows the override's. See "Which run's graph
becomes what the script owns" for the whole table, including the profile move
that is the one cause a run publishes.

vars interpolates from job args with interpolate_template (common.rs,
shared with the Ansible executor). The syntax is {{ arg_name }}.

select/exclude also scope what the script owns in the graph, resolved by
asking dbt (dbt ls --output json) rather than by interpreting the selector
string. Without that a narrowly-selected script registers as the producer of
every model in the project, and two scripts splitting one project would each
claim all of it. Running several scripts with different selections only composes
because of this.

Deploy path Copy Chapter

New ScriptLang::Dbt arm in worker_lockfiles.rs (near the ScriptLang::Ansible
arm at :2758), producing:

struct DbtDependencyLocks {
    manifest_digest: String,
    engine: String,
    engine_version: String,
    adapter_version: Option<String>,
    package_lock_digest: Option<String>,
    profile_relation_root: Option<String>,
}

Steps: write the script's modules into the job directory, dbt deps, dbt parse
for the manifest, then ingest.

Ingestion writes the rows the native parser writes, via
replace_static_asset_usage (windmill-common/src/assets.rs:254) into
asset (workspace_id, path, kind, usage_access_type, usage_path, usage_kind, columns).
The language dispatch point is parse_assets_for_lang
(windmill-api-scripts/src/asset_inference.rs:33).

The one architectural wrinkle. Every other language's asset parsing there is a
pure function of script content. dbt's needs the bundle on disk and a dbt
invocation, so it cannot run inline: it runs as a deploy-time job, persists the manifest, and
parse_assets_for_lang reads the persisted result. Prototype this first, it
is the assumption most likely to reshape the phasing.

Dependencies resolve at deploy, and are pinned for every run Copy Section

A project declaring packages.yml ranges or a mutable git revision asks dbt to
resolve them, and dbt re-resolves on every dbt deps. Windmill resolves once, at
deploy, and pins the result — the same contract every other language's lockfile
gets here.

The deploy records the digest of the package-lock.yml dbt produced into
DbtDependencyLocks. That digest keys the worker-local package cache and joins the
run identity that gates dbt retry. A run restores the tree under that key; a worker
that resolves anything else is refused rather than run, because accepting it would let
one resolution's run_results.json decide what a retry rebuilds.

Only a run has a resolution to be held to. The deploy establishes one and accepts
whatever dbt returns — including a package-lock.yml dbt rewrites itself, which it
does whenever the sha1_hash it stored for packages.yml no longer matches. Holding
the deploy to a committed lock would refuse the first deploy after a package is added,
with no way out, since redeploying resolves the same way again.

Consequences worth knowing before choosing whether to commit a lockfile:

• To pick up a newer version of a ranged dependency, deploy a CHANGE. Only a
  deploy re-resolves, and an unchanged push is skipped as a no-op — the lock and the
  schema are both derived, so they are compared as they would be stored rather than
  as they arrive. Editing packages.yml, or committing the package-lock.yml you
  want, is what moves a pinned resolution.
• A committed package-lock.yml lets a deploy hit the cache, since it is the
  digest the lookup is keyed on before dbt deps has run. A project without one, or
  whose committed lock is not what dbt resolves, pays a real dbt deps per deploy.
  This is dbt's own recommendation for the same reason.
• The refusal is per worker, not per script. dbt deps writes its lock into the
  job directory, so a project with a range and no committed lock reproduces its
  resolution only from a cache hit. Once upstream publishes a new version, that
  script keeps running on every worker already holding its tree and fails on the
  first cold one — same commit, same arguments, different outcome by worker. A
  deploy that changes something re-pins and clears it; committing the lock avoids it
  entirely.

Nothing evicts these worker-local caches — package trees, engine installs and retry
state alike — and cache_clear does not reach them either: it removes
$WINDMILL_DIR/cache/, while all three live under $WINDMILL_DIR/cache_nomount/,
as bun's cache does. An operator reclaims them by deleting that directory. Engine
installs dominate the space by two orders of magnitude (~270–290 MB each, bounded
by engine version) and package trees grow one tree per edit of a project that
declares packages. Sweeping either on an age or a size bound is follow-up work.

Run path Copy Chapter

One dbt build per job, the shape Cosmos arrived at with ExecutionMode.WATCHER
after per-model Airflow tasks proved roughly 6x slower (about 5.5 minutes for one
dbt run versus about 32 minutes for 184 per-model invocations on
google/fhir-dbt-analytics). dbt's own threading provides parallelism; Windmill
provides observability.

The run's arguments are one command block Copy Section

A run takes a single command argument, plus one argument per {{ placeholder }}
the descriptor interpolates. command is a oneOf whose variant IS the command,
so it carries exactly the overrides that command takes:

{"command": {"label": "build", "select": [], "exclude": [], "vars": {}, "full_refresh": false}}
{"command": {"label": "retry", "dbt_retry_job": "019fb410-8ea9-…"}}
{"command": {"label": "show",  "model": "stg_orders", "vars": {}, "limit": 100}}
{"command": {"label": "parse", "vars": {}}}

show and parse are accepted by the worker but are not run-form variants:
each is a thing to do to the project in front of you rather than a job to fill a
form in for, and the graph, the assets list and the dbt editor are where they
live. Both stay reachable from a flow, the CLI and the API, which is what makes
the editor's refresh scriptable and testable rather than a UI-only affordance.

The union is the point: dbt_retry_job is required where it means something and
absent everywhere else, show takes the ONE model it previews rather than the
select/exclude pair that narrows a build, full_refresh cannot reach a
command that ignores it, and
the run form renders a toggle over the variants rather than a list of fields that
quietly do nothing. The worker spreads the block over the run's arguments to read
them — label becomes dbt_command — which is why a {{ placeholder }} may not
take one of those names (RESERVED_ARG_NAMES). What a run SUBMITTED keeps the
block, since that is what dbt_run_state saves and invocation_args publishes.

1. Materialise the script's modules into the job directory, restore
   dbt_packages/ from cache.

2. Render profiles.yml from the resource, or use the project's own file with
   Windmill secrets injected as env vars for {{ env_var() }}.

3. dbt build --log-format json plus select/exclude/vars/threads.

4. Stream events: each NodeFinished updates per-model status live and emits
   RecordMaterializationRequest (windmill-common/src/materialization.rs:53),
   which already carries asset_kind, asset_path, partition, status,
   row_count, job_id, error, schema. run_results.json supplies all of it.

5. Structured job result (per-model status, timing, rows, failed tests), not just
   an exit code. Partial failure is dbt's normal case and must be legible without
   reading logs.

6. Node-level retry. retry_failed_nodes: {attempts, delay_seconds} in the
   descriptor rebuilds only what a failed build left failed or skipped, in the
   same job, before reporting failure. dbt confines a failure to its own
   subtree, so a transient warehouse error costs those nodes rather than the
   project. In-job is what keeps the state question out of it: the previous
   attempt's run_results.json is still in the job directory, so there is
   nothing to persist and no worker to land back on. This is the granularity
   astronomer-cosmos gets from one Airflow task per model, without the ~6x that
   per-model tasks measured (decision 4).

   A retry's run_results.json names only the nodes it redid, so it overlays
   the accumulated results rather than replacing them: the job's result must be
   every node the job touched, or the nodes that succeeded before the retry
   settle no materializations.

7. dbt retry resumes from the failure point using run_results.json, which is
   what makes one-job-per-invocation defensible. It is saved twice: to the
   worker's local cache, and to dbt_run_state in the database, so a retry
   works from any worker with a database connection. An agent worker reaches
   the database only through the API, which does not expose this, so it keeps
   only its own local copy; the automatic node retry is refused there for the
   same reason, since its wait could not observe a cancellation. Only run_results.json is stored there.
   dbt retry also needs manifest.json, roughly sixty times larger and
   growing with the project (732 KB against 12 KB on a six-node fixture), but
   the manifest is a pure function of the project files, vars and env — all of
   which the stored identity already pins — so a worker restoring from the
   database re-derives it with a dbt parse of about a second. It is a run
   argument (the retry command variant) rather than the automatic behavior of
   Windmill's generic retry, which has no per-language hook to change the invoked
   command.
   Each attempt gets a fresh job dir, so the previous run's target/ is cached
   per (workspace, script) on the worker and restored for a retry.

   Naming the run. The retry variant requires dbt_retry_job, the id of
   the run to resume, and the worker refuses one that is not the run it holds —
   naming both ids, since "that is not the one" is otherwise indistinguishable
   from "nothing is saved". Only the latest failure of a script is kept, so an
   unnamed retry would mean "whatever failed last" and would quietly resume a
   different run than the caller was looking at. The run page's Resume this run
   and Run again → dbt retry with same args both fill it in; on the run form,
   choosing retry prefills it with the run that caller's own retry would land
   on. It is not a selector: naming a run other than the saved one is refused,
   not resumed.

   Concurrency is the script's, not the retry's. A retry that starts while
   another run of the same script is in flight rebuilds nodes that run may also
   be rebuilding — appending an incremental model twice. That is what two
   concurrent builds of one project do as well: dbt takes no cross-process
   lock, so a project that must not run twice at once sets the script's
   concurrency limit, which covers its retries with it. A lock held across a
   dbt execution instead would outlive worker deaths and cancellations.

   Who may resume it. The state is keyed (workspace, script_path, permissioned_as) — one saved run per script per identity it executes as — so
   anyone entitled to run the script as that principal may resume its last
   failure, which is the same capability as re-running that job: running the
   script requires read access on it, and that access shows them the run and its
   arguments already. Naming the run neither widens nor narrows that; the id is
   checked against what the state holds, not against the caller.

   For an on_behalf_of script that means every caller shares one saved run,
   since they all execute as the owner — deliberately, since the state describes
   the script's last run under the owner's identity rather than any one caller's.

   What a retry actually adds, and where that crosses a line. Resuming grants
   no capability a caller lacks: they may already run the script as that principal,
   and a plain run builds everything the descriptor selects, of which a retry
   rebuilds a subset. The one thing it adds is information — the result carries
   invocation_args, the resumed run's arguments as SUBMITTED, because a retry
   job's own args are just the command and the run it names, and the row preview
   needs the real ones.

   For almost every shape the caller could already read that run, so nothing
   crosses: under f/, see_folder_extra_perms_user makes a job readable to
   everyone with read on the folder, which is also what grants execution; an
   ordinary script's runs are permissioned_as the caller, and another user's runs
   are keyed under their own principal and unreachable. It takes all three of a
   u/<owner> path, extra_perms sharing and on_behalf_of for "may run as this
   principal" to be broader than "may read this principal's jobs" — and there a
   grantee learns the literal argument values of another's run. References stay
   references, so no resolved secret is among them.

   That residual is accepted rather than gated. A gate needs the caller's identity,
   and a worker has only created_by — display_username(), which a token LABEL
   supplies. Resolving it as a username denies every labelled token (a CI token
   becomes label-<name>, which is no workspace member) and still trusts a name;
   authorizing where the caller is real means the submitting path, not the worker.
   The exposure did not justify either.

   That equivalence holds only while the run is READABLE, so the one run that
   breaks it saves nothing: a job pushed invisible_to_owner is hidden from the
   script's owners, and a retry publishes the arguments it restored, which would
   make that retry the one way to see them. A hidden run therefore keeps no
   retry state at all — it cannot be resumed by anyone, including whoever
   launched it, which is the cheaper half of the trade. Keying by the initiating
   caller would not have worked instead: created_by is display_username(),
   which a token LABEL supplies, so two callers can share one value and one can
   name a third person (GHSA-8x8x-88qc-qp4r, whose fix was to stop trusting that
   name for authorization). A retry does name the run it resumes, but that name is
   checked against the saved state rather than authorized as a job read — doing
   the latter, which is what would let a hidden run be resumed by its own author,
   needs the submitting path, where the caller is real.

8. Test failures honor dbt's severity: error fails the job, warn surfaces
   without failing. Overriding this would make the same project behave differently
   on Windmill than locally, breaking the core promise.

Durable state per environment, and what defers to it Copy Chapter

dbt --defer --state <dir> resolves a ref() the run does not build to the
relation the manifest in <dir> names, instead of to the schema this run writes
into. That is what lets one model be rebuilt into a scratch schema without
rebuilding everything above it, and it needs a manifest of the environment the
project actually lives in.

Nothing that already existed could supply one. dbt_run_state answers a
different question — it holds the LAST run whatever its outcome, keyed by the
principal, so dbt retry can resume its failures — and the worker-local
generations behind it are a cache: the next run of a project usually lands on a
worker holding neither artifact. So the state is its own table,
dbt_environment_state, one row per (workspace, script path, environment),
holding manifest.json and run_results.json from the last SUCCESSFUL run.
Success is half of the contract: a relation a later run defers to has to exist.

The environment is the warehouse, the target and where they resolve to Copy Section

The workspace warehouse's name, the target dbt actually runs, and the database
and schema that target resolves to — the pair relation_root reports to the
graph's drift check. Each component is length-prefixed rather than joined on a
separator — <warehouse>|<target>|<schema>|<database>, each written <len>:<value>,
so main/prod/analytics/dbt_wh_defer is stored as
4:main|4:prod|9:analytics|12:dbt_wh_defer. A target name and a schema are both
the user's own strings, so prod|analytics + scratch and prod +
analytics|scratch would otherwise be one key, and a profile moving between them
would read as the same environment rather than as one nothing has published. What
a message names is spelled out instead, never the encoded key.

The target is the EFFECTIVE one, not the descriptor's profile.target: a
descriptor naming none inherits the workspace warehouse's, or the default in the
project's own profiles.yml, so reading the descriptor's would file every
inherited target under one empty name — and a target.name macro decides where a
model is built.

The last two are in the key because deferring is resolving a relation NAME. A
warehouse repointed at another database, or a profile.schema moved by a
redeploy, keeps the first two while putting every relation somewhere else, and a
manifest is a list of relation names — there is no other way to notice. Keyed on
the first two alone, such a move would hand the next deferring run the names of
relations that are no longer there. Keyed on all four, it reads as an
environment nothing has published yet, which is what it is.

What the key deliberately does NOT carry is the resolved connection. That is the
profile_digest a retry is held to. It masks credentials, but it still moves
with changes that move no relation, like another Snowflake warehouse or role; a
warehouse pointing somewhere else entirely is decision 11's accepted limitation,
spelled the same way here as everywhere else.

Today one script has one environment, because a descriptor fixes both the
warehouse and the target and a run cannot override either. The key is what makes
the later item — fork and preview environments — an addition rather than a
migration, and what makes a profile move detectable now.

Which runs publish it Copy Section

A successful build that did not itself defer, and whose graph becomes what the
script owns (GraphRefresh::publishes_ownership) — the same condition as the
graph's and the same reason: an invocation that scoped its own model set — a
vars or select override, or a descriptor dynamic by construction — describes
where the CALLER put those relations, not where this project's models live.
Publishing it would point every later deferral at one caller's scratch schema.

A run that deferred never publishes, whatever narrowed it, and that is a
separate condition rather than a consequence of the first. A deferring run built
some of the relations its manifest names and resolved the rest out of the state
it read, so recording that manifest would claim relations nothing built — and a
model renamed since would be recorded under a name only a full build creates,
breaking every later deferral until one repairs it. publishes_ownership cannot
see this: it reads the caller's overrides, and a descriptor that already narrows
select needs none.

A retry publishes nothing. Its run_results.json names only the nodes it
redid, so the environment would come to claim a run of a handful of models. The
environment's state is therefore the last full successful build, exactly as dbt
Cloud's "last successful run" is, and a run recovered by a retry leaves it at
the previous one.

The AUTOMATIC in-job node retry is the same artifact under a different name: a
build it recovers is a successful build, but the run_results.json on disk is
the retry's. Such a run publishes the manifest without results, rather than
with a set describing some other slice of the build — the manifest is a function
of the project rather than of what ran, so deferral is unaffected. A result:
selector is the one thing left with nothing to read, and it is refused by name
against such a publication rather than passed to dbt (see "Selectors that read
the state" below).

Under test_behavior: after_all the stored run_results.json is the test
phase's, because that is what the second invocation leaves in the target
directory — the same artifact a local dbt run && dbt test leaves behind.

What that condition means for what the artifacts may carry, and why this
table is keyed by environment where dbt_run_state is keyed by principal. dbt
records the invocation's flags into run_results.json, and Windmill resolves
$var: / $res: references before dbt sees them — which is exactly why the
retry state is per-principal, so one caller's resolved select and vars are
not restorable by the next. Here they cannot be one caller's: a publishing run
added nothing of its own, and a descriptor that interpolates a {{ }}
placeholder into vars never publishes at all, so what is recorded is the
descriptor's own arguments — the script's content, which anyone entitled to run
it may already read. Widen the publish condition and that stops being true.

Where the blob goes Copy Section

run_results.json is small; manifest.json is not, and grows with the project
(535 KB on a two-model fixture). Each takes the same two homes: inline in the row
under DBT_STATE_INLINE_MAX_BYTES (8 MiB), and the INSTANCE's object storage
above it, with the row keeping the key. Inline is what makes the feature work on
an instance that has configured no storage at all; the ceiling is what stops one
project's manifest from becoming a multi-megabyte row rewritten by every run. A
project past the ceiling with no storage configured is told so, in the job log,
naming the setting and the variable — the run itself still succeeds, since
losing the state costs the next deferral rather than the build that just ran.

The instance store, not the workspace's, which is where every other internal
worker artifact already lives (bun bundles, python wheels, job logs, the global
cache). The workspace bucket is the one members read and write through
job_helpers/* and wmill.write_s3_file with a caller-supplied key, and only
volumes/ is reserved there — so a manifest under it is one any member could
replace, and the next deferring run would hand dbt an attacker-chosen
defer_relation for every unbuilt ref() while holding the script's warehouse
credentials. Its compiled SQL would be readable there too, for a project the
reader may have no access to. The consequence to know: a project past the ceiling
needs the instance store configured, which is an EE feature, so on CE the ceiling
is the limit and DBT_STATE_INLINE_MAX_BYTES is how it moves.

Each publication writes its OWN keys
(wmill_dbt_state/<workspace>/<digest of path and environment>/<job>.<nonce>/<artifact>)
and the row switches to them in one statement, so an upload never overwrites an
artifact the committed row still names: a run that fails between its two uploads,
or between them and its row, leaves the state pointing at the pair it already
had. The objects the commit displaced are dropped afterwards, never before, since
a reader that has already read the row is about to fetch them; a reader that
loses that race re-reads for as long as the row keeps MOVING, rather than
reporting a state that is there. A reader takes no lock, so successive
publications can each overtake one; an unmoved row whose objects are gone is the
error that means what it says, and a bound on the re-reads is the other, for a
project republishing faster than a run can read. What a publication uploaded and then could not commit is dropped on the
way out — except after a commit that REPORTED an error, where what was lost may
be only the acknowledgement: dropping then would leave a committed row naming
objects that are gone, so an orphan is the cheaper side to take.

The path and the environment are only a prefix of that key. The row is what says
where an artifact is, which is why state can travel with a renamed script and go
on naming objects under the old path's digest. The rest of the key is the job and
a per-EXECUTION nonce — zombie recovery re-runs a job under its own id, so keyed
on that alone a second attempt would overwrite the objects the first attempt's
committed row still names, then read those keys back as displaced and drop them.

Publishers of one environment serialize on pg_advisory_xact_lock, so only one
of them settles the row and the objects it displaces at a time — an advisory lock
rather than the row's, because the first publish of an environment has no row to
lock and is exactly when two runs of a newly deployed script are most likely to
race.

Retention Copy Section

None, deliberately, and this is where it differs from the graph tables next
door. Those are pruned by age by the dbt runs themselves because their reader is
a transient run page. This one holds a single row per script per environment,
replaced in place, so it does not grow with runs — and its reader is every later
run of that script, so a project that runs monthly must still find last month's
state. It goes with the script instead: a path no live dbt version occupies any
more clears it, alongside dbt_run_state (clear_dbt_script_state,
clear_dbt_script_state_if_path_retired).

The write carries a guard of its own, and it names the VERSION rather than the
path: the live dbt script there must be the one this job ran, or a later version
of it (hash = $n OR $n = ANY(parent_hashes)). "Some live dbt script is here" —
which is what the retry state settles for — is also satisfied by a script created
at a path this one was renamed away from, and this job's manifest would then
become that project's deferral state. A preview names no version and so publishes
nothing, which is right for a run of content that was never deployed.

The job's KIND is checked beside it, because a preview carries a caller-supplied
script_hash into runnable_id (run_preview_script): the version alone would
let anyone who may run a job publish arbitrary content as a deployed script's
state. A flow or app step naming a deployed dbt script by path is an ordinary
script job carrying that script's own hash, so it publishes like any other run;
only INLINE flow code is a FlowScript, and that has no deployed version to
publish for.

That guard HOLDS the script row (FOR SHARE) for the rest of the publication, so
a rename, archive or delete of the path either waits for it or is seen by it.
Read unlocked, it leaves a window where the lifecycle clear finds no row to take,
finishes, and the publication then commits state at a path a new script goes on
to occupy. The script row is taken before the sidecar, which is the order every
other dbt writer takes and what keeps the two off a deadlock.

An artifact too large for its row is left in the store when the row is cleared,
as a deleted script leaves its bundle: reaching it from the delete would mean an
object-store client in windmill-common and a delete that has to land after the
caller's transaction commits, for one object per environment of a script that is
gone.

Asking for it Copy Section

defer is a field on the build command block, defaulting to the descriptor's
own defer:. A per-run toggle rather than a descriptor-only setting, because the
run that publishes an environment's state and the run that defers to it are two
invocations of ONE script (decision 6: N scripts means N projects): a project
that could only defer by descriptor could never populate the state it reads.

A project whose profile selects its schema or database with a TEMPLATE — either
delimiter, since dbt renders {% … %} blocks as well as {{ … }} — is refused a
deferral outright, and publishes no state either: dbt renders those and Windmill
does not, so two renderings resolve to one relation_root, and a
deferral after the value changed would resolve every unbuilt ref() through the
previous location's manifest. Both sides, because a published template would sit
under a key a literal profile shares, and de-templating later would make that
stale manifest readable as the new location's. It covers a project-owned
profiles.yml, a dbt_profile resource — one block of the user's own file,
copied through unchanged — and a profile.schema written as given. Plainly
absent is different: that is the adapter's default, which does not move.

A run that asks to defer with nothing published is refused, naming the
environment and the runs that cannot publish one. The alternative — running
without deferral — fails deep inside dbt with a relation-not-found the caller has
no way to connect back to a missing state. An agent worker is refused the same
way and for a reason it can act on: it reaches the database only through the API,
which does not expose this table.

A show defers too, and every engine takes the flags on it. It compiles the
model it previews, so a model whose upstream this environment built and this run
did not is exactly the case a deferral exists for. So does the dbt ls that
resolves what a run's selection owns, without which a result: selector — which
reads run_results.json out of the state directory, and which select passes to
dbt verbatim — would fail before the build that would have honoured it.

The result carries deferred_to, the run whose state was used. Without it what
a deferring run built against is unrecoverable, since the next successful run of
that environment replaces the state.

Selectors that read the state, and why they are refused rather than passed Copy Section

--state also feeds dbt's own selector methods, so publishing the state is what
makes state:modified+, state:new and result:error+ resolve at all. Only a
deferring run is handed the directory, so a state: or result: method in
select or exclude without defer is refused before dbt starts.

Refused, rather than left to dbt, because the engines disagree about it and two
of the three disagree silently. Given a state selector and no --state,
dbt-core 1.x raises (Got a state selector method, but no comparison manifest,
exit 2), but dbt-sa-cli 2.x and fusion read a MISSING state as an EMPTY one and
exit 0: state:modified then selects nothing and the run reports success having
built nothing, while state:new selects everything, because against an empty
state every node is new. A scheduled run that quietly stops doing work, or
quietly rebuilds the project, is the failure this state exists to prevent.

From the DESCRIPTOR they are refused whether or not the run defers, and the
message says so. That selection is also what decides which nodes the script owns,
and the deploy resolves it before any run exists, with no state to compare
against. "Whatever changed last" is not an ownership answer. They describe one
run, so they belong in a run's own select.

source_status: is refused under any setting: it compares sources.json, which
dbt source freshness writes and no run publishes here, so there is nothing to
compare against even while deferring.

Two more refusals follow from the same argument, that a selector with nothing to
read must say so rather than resolve to a silent answer:

• A result: method while deferring to a state that carries no
  run_results.json. Publishing that is deliberate — a build recovered by
  automatic node retry stores the manifest alone, its results describing the
  retried nodes rather than the build ("Which runs publish it") — so defer
  being on is not enough to know the file is there. Answerable only once the
  state is loaded, so it is checked right after, naming the run that published.
• Any of them on a parse. A parse resolves a selection to store the graph and
  never defers, so defer would not hand it a state at any setting, and the
  remedy the other refusal offers would lead nowhere. It says that instead.

Matching nothing is then an ordinary outcome for these methods, and for no
others. state:modified+ selects the empty set exactly when nothing changed
since the published state, which is the answer a CI run wants, so a selection
naming a state: or result: method may resolve to no nodes. Such a run scoped
its own selection, so what it stores is a snapshot of its own and never what the
script owns, and nothing is un-wired by the empty set.

The exemption is by METHOD, not by who chose the selection. Exempting every
caller-chosen one would take a misspelled model name, which resolves to nothing
just as surely, and report it as a build that did its work. An ordinary selection
matching nothing stays refused, from a run as from the descriptor — from the
descriptor because that one also decides ownership.

Only what select and exclude spell directly. A method reached through a
selectors.yml definition is named nowhere the worker reads, and dbt's own
behaviour — including the silent one — is what stands there.

`--state` is also a retry's own argument, and that is a trap Copy Section

dbt retry reads the run it RESUMES from --state. Handed the deferral's
directory it resumes the successful run stored there, finds nothing failed, and
reports a green retry having rebuilt nothing — silently, on dbt-core 1.x, which
warns and exits 0.

dbt-core 1.x has --defer-state, the deferral-only half of the pair, so a retry
there passes that and leaves --state alone. The Rust engines do not have it,
and a run that deferred is refused a retry on them, before the build: the
alternative is rebuilding the failed nodes with every ref() resolving into the
schema this run writes into, which for the narrowed run a deferral exists to
serve means writing them somewhere they do not belong. The automatic in-job node
retry is dropped for the same reason and says so in the log.

The state directory is passed RELATIVE (wm_dbt_state, beside wm_target in the
job directory). dbt records the invocation's flags into run_results.json and a
later dbt retry restores them, so an absolute path would name the job directory
of the run being resumed, which is gone by then. Relative, it resolves against
the project root — whichever job directory the retry landed in.

Three engine facts found while wiring this up, all worth knowing before filing a
bug against the feature. dbt retry on dbt-core 2.x restores neither the
resumed invocation's --vars nor its deferral: it re-parses with the current
(empty) ones, so a retry of a run that overrode vars rebuilds into the
descriptor's schema rather than the run's. That is independent of deferral and
predates it; the refusal above stops the deferring case from being the way it is
discovered. dbt show on either Rust engine prints a bare JSON array where
dbt-core frames it as {"node": …, "show": […]}, which run_show is written
against — so a preview there fails to parse whether or not it defers, and the
deferral itself resolves correctly under it. And neither Rust engine reached
dbt's own service-backed State (--manage-state) on any run measured here, so no
flag is passed to disable it.

Because select reaches dbt verbatim, a deferring run also has a --state
directory for result: selectors, which is why run_results.json is stored
beside the manifest rather than the manifest alone.

Two decisions the implementation narrowed Copy Chapter

Decision 13 — the manifest is stored once per environment, not per version.
The sidecar holds every field the graph renders, so a copy of manifest.json
bought the graph nothing: it is reproducible by redeploying, or for a dynamic
descriptor by the next run. Deferral is the reader that changed that — it
resolves an unbuilt ref() through a manifest, and one on worker-local disk
answers for a machine's history rather than for the environment. So exactly one
manifest is kept per (script, environment), replaced by each successful run,
rather than one per version (see "Durable state per environment" above).

Decision 14 — column lineage comes from the parquet index, not the manifest.
manifest.json carries no column-to-column edges, in any engine, and its
columns are the ones an author declared in schema.yml. Both halves exist in a
different artifact: dbt compile --static-analysis strict --write-index writes
target/index/, and two of its tables are dbt.column_lineage.parquet
(from_node_unique_id, from_column_name, to_node_unique_id,
to_column_name, lineage_kind) and dbt.node_columns.parquet (every column of
every node, with its declared type, its inferred type and its description).

Three measured properties decide the shape of the ingest.

Strict analysis is a stricter dialect. select no_such_column from ref(...) is UnresolvedIdentifier (dbt0227) and exit 1 under strict, and
compiles fine under baseline (the default). So this is a separate dbt compile
with its own --target-path, never a flag on the build, and it is opt-in per
project: column_lineage: true in the descriptor. Off, nothing changes. On, a
project that cannot be analyzed keeps exactly the graph it had.

The pass is best-effort about everything that is ITS: a wrong engine, a rejected
analysis, a missing or unreadable artifact, an over-long output and outrunning its
own time budget all degrade to partial lineage or none, plus a line in the job
log saying which. It is not best-effort about the JOB: a cancellation or the job's
own deadline fail it, because swallowing those would let a run that blew its
timeout inside an optional annotation publish a graph and report success. That
split is why the two halves have separate error contracts — the compile owns the
job's semantics and may Err; nothing the artifact does or fails to do is a
reason to fail a job, so an absent, unreadable or partial index is a value. The
decode still runs under the job poller, which both heartbeats through it and
ends it if the job is cancelled or completed meanwhile: the job reaching in, not
the artifact reaching out. The
budget is half the job's remaining wall clock, spent on the compile alone, so the
build that follows cannot be starved by it.

A failed pass still writes the index, holding every edge of the models that
did analyze, so the artifact is read whatever the exit status and partial lineage
is a normal outcome. An unreachable source is milder still: RemoteError (dbt1014) downgrades that model to static_analysis: off and the compile
succeeds. (Strict analysis queries the warehouse catalog for source schemas; a
ref()ed model is inferred statically and needs no built table.)

The flag is not the capability. dbt-core 2.0.0-alpha.5 — the version
DBT_CORE_2X_VERSION pins — accepts --write-index and --write-lineage, and
its own views.sql declares views over both tables, but it writes neither
parquet; only Fusion does today. The ADAPTER decides too: an experimental one
(postgres under DBT_ALLOW_EXPERIMENTAL_ADAPTERS) turns static analysis off and
says so only in a warning on an otherwise successful compile. The gate is
therefore "the engine has the flag" (everything but 1.x, whose Python CLI has no
such option) plus "the file appeared", so a later release picking the feature up
needs no change here — and the job log carries the engine's own stderr whenever
no index appears, since without it "no column lineage" has no explanation.

lineage_kind is stored as TEXT, not an enum. Three values exist — copy
(passthrough), mod (transformed) and scan (the column was read to produce the
ROW rather than the value: a join key, a where predicate, a group by) — and
the engine's own reader maps those three and passes anything else through, so the
set is the engine's to extend. All three are stored, and copy/mod are kept
first when the bound bites: a scan edge reaches every output column of its
model, so it is most of what a project's index holds and would draw as a complete
bipartite graph. Keeping it in the table anyway is what lets a later "show
indirect" view ask for it without every project being redeployed.

Storage mirrors dbt_edge exactly: dbt_column_edge, keyed by (path, version,
job) with the same composite foreign key to script, so a version's column
lineage dies with the version and a run's snapshot with the sweep.

A table of its own, not dbt_edge.column_lineage JSONB. Hanging the links on
the ref() edge they sit beneath would inherit its clone, prune, clear and
cascade paths for free, and it does not work: a model reading {{ this }} gets
column lineage from itself to itself, and parent_map has no self-loop, because
a model does not ref() itself. Those pairs have no dbt_edge row to attach to.
The loss is not hypothetical — an incremental that selects from {{ this }}
(coalesce(p.dbl, s.dbl), p.up as prev_up) yields up → prev_up with kind
copy, a drawn edge meaning "this column carries the previous run's value".
Inventing self-loop dbt_edge rows to hold it is not an option either: that
table is ref() lineage. The typed column list lands in
dbt_node.column_schema, beside columns rather than merged into it —
columns stays what the author declared.

Two things are user-visible. column_schema — every column of a relation, typed
and in the order the model emits them — rides the asset graph the details pane
already fetches, and replaces a panel that could only list the columns an author
happened to document. The edges are served by an endpoint of their own,
assets/column_lineage, which the pane asks for the selection it is drawing.

Both are gated on being able to read the producing project, like the model's SQL:
a column-level view is the shape of what the author wrote, one level finer than
the ref() graph, which is ungated only because it draws relations the caller
already sees. A share-link viewer entitled to a dbt run therefore gets its
relations and ref() edges, and neither the SQL nor the columns.

One request per selection, and the gate re-decided per project. The endpoint
takes every relation the view has reached and answers their union, because one
selection reaches several — a script's output column can derive from columns of
several models. Holding partial answers between selections instead was tried and
is what a client cache is: it produced a wrong premise for a relation two projects
describe, then staleness on redeploy, then a lost retry.

The answer is the connected component around those relations, and that component
does not stop at the project that owns them: a relation one project produces is
another's source, so the walk resolves owners, reads their edges, walks, and
repeats for the relations that walk newly reached. Resolving once — for the
relations asked about — stops the trace at the first project boundary. The
security half is that a project reached this way is a project the caller may not
be entitled to, so the scope filter and the project's visibility are re-applied to
every project the expansion discovers, not decided once for the first owner set.

A PINNED answer is the exception and needs none of it, whether it names a job or
a deployed version: the pin says which stored graph is on screen, and another
project's live graph is not part of it, so it answers for that one project. The
dbt editor pins by version on every selection and the run page pins by job; the
pipeline page pins nothing, and is where the walk crosses projects.

The component is bounded, and says when it was cut. The renderer draws a box
per column, so a component past a few thousand edges is unreadable however it is
served — a synthetic 3000-model project whose models share a column returns 58k
direct edges and 7.3MB. The walk is breadth-first from the asked-for relations and
stops at 5000 edges, so what survives is the part nearest the selection rather
than an arbitrary slice, and truncated says so: a trace that stops short is
otherwise indistinguishable from one that ends.

truncated is set only where a project this caller may read is left unread, or
where the walk itself was cut. Neither the expansion's size budget nor a relation
whose owners were never asked about is evidence on its own: a big project's small
component is answered whole, and the relations still waiting to be asked about at
the end are almost always owned by the project already in hand. The order the
rounds run in is what makes that decidable — the owners query comes before the
budget check, so the stop happens with a named unread project rather than a
suspicion of one.

The FETCH is not bounded the way the answer is: a project's edges arrive whole,
because the walk is what decides which of them are in the component, and a
LIMIT would cut a set that need not contain the asked-for relation at all. The
seeds' own projects are therefore read whatever their size — reading them is the
answer. What bounds a single fetch is the ingest's own cap per version; the
budget bounds only the expansion on top of it.

The walk is in Rust rather than a recursive CTE. EXPLAIN ANALYZE on that same
project measured 1243ms against 59ms for the query alone: a CTE has no index to
walk, so the recursive term rescans the doubled edge set once per level (11.7M
rows), while the same walk over a map is microseconds.

The two halves of a trace — dbt's and the pipeline's — meet at shared node ids. A
DuckDB script's // column x <- dbt://wh/s/model.col mints the same
(dbt, path, column) node dbt's own lineage does, so the producer graph the asset
graph already carries and the dbt graph are MERGED rather than chosen between, and
a trace crosses that boundary in either direction. What the browser cannot close
in one request is a relation the server discovers whose columns are consumed by a
script that writes into a third project: the producer half of that hop is the
canvas's, not the server's, and the seeds were computed before the answer arrived.

The analysis pass takes the build's own --full-refresh. is_incremental()
branches on it, so an incremental model reading {{ this }} compiles its
self-join — and any ref() inside that branch — only when the flag is absent. A
pass that used the descriptor's default while the run overrode it would store
lineage for SQL that run never executed. For the same reason an invocation that
overrides the flag counts as per_run_models: its graph is its own, keyed to the
job, rather than standing as the version's.

That flag is not the whole of it, and the rest is a property rather than a bug to
fix. is_incremental() is also false when the target table does not exist, so an
incremental model has two shapes and one ingest holds one of them: a deploy
before the first build compiles the cold shape, and the same project deployed
again once its tables exist compiles the incremental one. A static descriptor
re-ingests on neither runs nor time, so what is stored stays whatever the compile
in front of it saw. dbt has no mode that emits both, and re-analyzing per run
would buy a second dbt compile on every build to keep a graph nobody asked to
refresh. The contract is therefore the honest one: a version's graph describes
the compile that produced it, and a run that re-ingests describes its own run.

Concept mapping Copy Chapter

Phases Copy Chapter

Phase 1: run it. ScriptLang::Dbt across the 41 ADD_NEW_LANG sites (mostly
one-liners in EditorBar.svelte, scripts.ts, script_helpers.ts,
LanguageIcon.svelte, script_common.ts). Engine provisioning for all three
options in Dockerfile and docker/DockerfileFull* (bundle 1x and 2x, fetch
Fusion at runtime). New backend/windmill-worker/src/dbt_executor.rs: descriptor
parse, bundle materialisation, profiles.yml render, dbt build, log
passthrough, structured result, retry.

Phase 2: graph. DbtDependencyLocks and the deploy arm. Migration via
cargo sqlx migrate add -r dbt_runtime. New dbt:// AssetKind with
its canonical_prefix. Manifest ingest. Deploy-time ingest plus the per-run
re-ingest for dynamic descriptors. Extend AssetGraphRunnableNode/AssetGraphAssetNode in
frontend/src/lib/components/assets/AssetGraph/types.ts with dbt provenance and
render through the existing RunnableNode.svelte / AssetNode.svelte /
DataTestNode.svelte.

Phase 3: live progress and ergonomics. JSON event stream to per-model status
on the canvas mid-run. record_materialization per model. Profile and select
pickers in the editor. Per-model failure triage in the run view.

Phase 4 (not in this PR). Slim CI: the fork and preview environments a
deferral would name instead of its own. The selectors themselves are here, since
state: and result: read the published state like any deferral does; what is
missing is a per-branch environment to compare a CI run against. Partition and
backfill integration so BackfillRangeDialog.svelte works on dbt models.
wmill dbt import <dag.py> reading DbtDag(...) kwargs.

E2E test requirements Copy Chapter

Against a real dbt project (jaffle_shop shape) and the local Postgres:

1. Happy path: deploy a dbt script, run it, assert models exist in the
   warehouse and the job succeeds with a structured per-model result.
2. Engine parity: the same project passes on dbt-core-1x and dbt-core-2x.
   Fusion covered by a manually-run test, not CI (runtime fetch).
3. Test severity: a failing error-severity test fails the job; a failing
   warn-severity test does not.
4. Retry: a run failing midway, retried, resumes via dbt retry and does not
   rebuild already-successful models.
5. Graph ingest: after deploy, model assets and ref() edges exist; a native
   script reading one of the marts gets an edge to it.
6. Shared node: a native script that READS a mart renders as a reader of the
   same node the dbt model writes — one node, not two islands. Declared with a
   plain read (# dbt://<mart>), never # on: a subscription to a relation dbt
   alone builds is refused at deploy, since a dbt run does not dispatch.
7. Declared write: a native // materialize manual dbt://<relation> script
   and a dbt project reading that relation as a source render as one node; a
   run of the script records its materialization and wakes a
   # on dbt://<relation> subscriber — a subscription only that producer makes
   wakeable, the dbt project reading the relation being no producer of it (see
   "no cascade from dbt").
8. Selection: descriptor select/exclude, and a run-arg override, each
   build only the expected subset.
9. Dynamic descriptors: a {{ }} placeholder in vars re-ingests the graph
   from the run's own manifest, so a model that placeholder enables appears in
   the same run that builds it.
10. Both credential paths: resource-rendered profiles.yml, and the project's
    own profiles.yml with env-var injection.
11. Caching: a second run reuses the cached dbt_packages/ with no network
    fetch.
12. Deferral: a full run publishes the environment's state; a second run
    that builds one downstream model into another schema resolves its unbuilt
    ref() to the relation the state names, where the same run without defer
    fails with relation-not-found.
13. State selectors: with a state published, state:modified+ selects
    nothing while the project is unchanged and exactly the changed model and its
    children after one is edited. Without defer it is refused rather than
    passed, and a result: selector against a state published by a
    node-retry-recovered build is refused too, that one carrying no
    run_results.json.

Keep only tests that pin behavior a future change could break. Per AGENTS.md,
delete development scaffolding before marking the PR ready.

Docker Security (`docs/docker-security.md`) Copy Section

Docker base-OS security patching

The runtime images are built on debian:trixie-slim (Debian stable). The base
runtime stages run apt-get update && apt-get upgrade -y && apt-get install …
so that base-OS packages pick up Debian security and point-release fixes at
build time, instead of staying frozen at whatever versions the base tag shipped.

Where the upgrade lives Copy Chapter

apt-get upgrade -y is applied in the first apt block of the three stages that
establish a runtime Debian layer:

• Dockerfile (the primary windmill / windmill-ee image)
• docker/DockerfileSlim (windmill-slim)
• docker/DockerfileSlimEe (windmill-ee-slim)

Every other runtime image inherits its base OS from one of these transitively,
so patching here is sufficient:

• DockerfileFull, DockerfileFullEe, DockerfileCuda build FROM the primary
  windmill / windmill-ee image.
• DockerfileExtra builds FROM windmill-ee-slim.

The nsjail builder stages are throwaway (only the compiled nsjail binary is
copied out), so they are intentionally not upgraded. The CLI, Caddy-L4,
CUDA-only, and RHEL/dnf images are out of scope for this apt-based patching.

Why `apt-get upgrade` and not `unattended-upgrades` / pinning Copy Chapter

Debian stable's archive only receives security updates and ABI-stable point
releases (e.g. openssl 3.0.x → 3.0.x+deb12u2, same soname). It does not ship
feature/major bumps, so a build-time apt-get upgrade cannot silently break a
pinned runtime dependency the way it might on a rolling distro. The default
debian.sources already includes the *-security suite, so a plain upgrade
picks up security fixes without extra machinery. unattended-upgrades adds a
package and config for no benefit in a build context (it does not run at build
time), and a pinned base digest would freeze the CVEs in place.

Note the runtime apt installs were already unpinned (only the throwaway nsjail
builder pins versions), so these images were never byte-for-byte reproducible in
this dimension; upgrade moves the same already-floating packages to their
patched versions rather than changing the reproducibility posture.

Caching and freshness Copy Chapter

apt-get upgrade sits in the same RUN as apt-get update && install. Docker
keys that layer on the command string plus the parent layer, not on package
contents, so an unchanged build is a cache hit and the upgrade does not re-run.
The layer is invalidated — and fresh patches are pulled — when the parent layer
changes, primarily when the mutable debian:trixie-slim base digest moves on a
Debian point release. That self-aligns: the cache refreshes when there is
something new to pick up.

Because of apt-cache staleness, a security fix that lands between base-digest
bumps will not be picked up by a cached build until the next bump. To close that
gap, rebuild and republish the latest / patch tags:

• on each Debian point release (base digest bump), and
• on a periodic cadence (e.g. per Windmill release), rebuilding the base stages
  with --no-cache if you need to force a fresh apt-get upgrade regardless of
  the base digest.

Scan the published images (e.g. Trivy / Defender) after rebuilds to confirm the
base-OS finding count stays low.

Verifying image signatures, SBOMs and provenance

Release images are signed and attested at publish time:

• cosign keyless signature on the pushed manifest digest (index and
  per-arch manifests), via GitHub OIDC — no long-lived signing key exists
  (.github/actions/sign-attest-image).
• SBOMs are generated at build time (sbom: true on the depot build
  step) and embedded in the image index as BuildKit attestation manifests —
  one SPDX document per platform. They are part of the signed index digest,
  so the cosign signature covers them. They are not sent to a transparency
  log: SPDX documents for these images run tens of MB, beyond what Rekor or
  GitHub attestations accept as payloads.
• SLSA build provenance recorded as a GitHub artifact attestation and
  pushed to the registry (actions/attest-build-provenance).

What is covered Copy Chapter

Only images published from a release tag (v*) are signed: windmill,
windmill-ee, windmill-ee-cuda, windmill-slim, windmill-ee-slim,
windmill-full, windmill-ee-full (.github/workflows/docker-image.yml),
windmill-cli (build_cli_image.yml) and windmill-extra
(publish_extra.yml). The :latest and :main tags are repointed on
every main push as well as on releases, so they resolve to a signed
digest only until the next main build lands — verify a version tag or a
digest, not :latest. Development images (:dev, branch builds,
windmill-test), the dispatch-only RHEL/rpi images and the caddy-l4
image are not signed.

How to verify Copy Chapter

Signatures are keyless: trust is anchored in the Fulcio certificate identity,
which for these images is the calling workflow file at a v* tag ref in
this repository. Verify a signature with cosign (v2.x):

cosign verify \
  --certificate-oidc-issuer https://token.actions.githubusercontent.com \
  --certificate-identity-regexp '^https://github.com/windmill-labs/windmill/\.github/workflows/(docker-image|publish_extra|build_cli_image)\.yml@refs/tags/v' \
  ghcr.io/windmill-labs/windmill:<version>

Extract the embedded SBOM (per platform; verify the signature first — it
covers the index these documents live in):

docker buildx imagetools inspect ghcr.io/windmill-labs/windmill:<version> \
  --format '{{ json .SBOM }}'

Verify SLSA provenance through GitHub's attestation API:

gh attestation verify oci://ghcr.io/windmill-labs/windmill:<version> \
  -R windmill-labs/windmill

Note for registry housekeeping: cosign stores signatures as extra
sha256-<digest>.sig tags in the same ghcr package, and the pushed
provenance attestations live there as referrer artifacts — any
tag-retention automation must not prune them.

Docker V2 Runtime (`docs/docker-v2-runtime.md`) Copy Section

Sandboxed container runtime (daemonless docker)

Windmill bash scripts can run a container image. There are two runtimes:

The three bash annotations are distinct and don't overload each other:

• # docker → legacy daemon docker (unchanged).
• # sandbox → run the bash script under nsjail.
• # sandbox → run that image's command under nsjail (this runtime).

Using it Copy Chapter

Put the image ref on a # sandbox annotation line; the rest of the script runs
inside that image:

# sandbox python:3.12-slim
name="$1"          # windmill args bind positionally, like any bash script
python3 -c "import sys; print('hello', sys.argv[1])" "$name"

• The body runs via the image's /bin/sh -c (so the image needs a shell).
• An empty body runs the image's ENTRYPOINT + CMD.
• Windmill args (declared x="$1", …) are appended to the command.
• The image's Env, WorkingDir are applied; the windmill reserved variables
  (WM_TOKEN, BASE_INTERNAL_URL, …) are injected so wmill/API calls work.

Result Copy Chapter

Same conventions as a plain bash script, in this order:

1. ./result.json (relative to the image's WorkingDir) if non-empty → returned as
   the JSON result; malformed JSON fails the job. It is a host file bind-mounted into
   the container, so it works for WorkingDir: "" (→ /) too, which otherwise lands in
   nsjail's ephemeral root and never reaches the job. Being a bind-mount point, it must
   be written in place (> ./result.json); a write-temp-then-rename fails.

   The bind is skipped, with a warning in the job logs, when the destination can't be
   proven to stay inside the container: a WorkingDir with .., one whose path in the
   extracted rootfs crosses a symlink, or one under /tmp, /proc, /dev or /sys
   (mounted after this bind, so a result there would be shadowed anyway). nsjail resolves
   a mount destination before pivot_root, so an unverified path would have it create
   a file on the host. For the same reason the bind is applied directly after the
   rootfs binds — a # volume mounted over the WorkingDir therefore hides it, and the
   result falls through to the stdout rule below.

2. Otherwise the last non-empty line of stdout, returned as a string. stderr is
   excluded on purpose: the two pipes are merged by a fair select, so a diagnostic
   on stderr could otherwise beat a block-buffered stdout result.

3. Otherwise a completion message.

How it works Copy Chapter

1. Pull/extract (crane, no
   daemon/store/root): crane export streams the image's flattened root
   filesystem to a tar (layers + whiteouts applied, like docker export) and
   crane config reads its OCI config. The tar + config are cached
   content-addressed by digest (crane digest) so unchanged digests reuse the
   cache; tar -x materializes the per-job {job_dir}/rootfs. crane is a single
   ~25 MB static binary — we never run the image with it (nsjail does), so a full
   container engine like podman isn't needed.
2. Run (the job's nsjail sandbox): nsjail binds each top-level entry of the
   rootfs in place (binding the whole rootfs at / trips nsjail's read-only
   remount of its base root in a rootless userns), mounts the standard
   pseudo-filesystems (/proc from the jail's pid namespace, a tmpfs /tmp,
   /dev nodes), maps uid/gid 0 inside → the worker user outside, and runs the
   command. The container is the jail.

# sandbox <image>  ─▶  crane export → digest-keyed rootfs cache → tar -x → {job_dir}/rootfs  ─▶  nsjail (chroot rootfs)
                       crane config (OCI config) ───────────────────────────────────────────▶  Env / Cmd / WorkingDir

Because the run is just the job's own nsjail with the image's filesystem as root,
the container inherits exactly the job's confinement:

• Filesystem: only the rootfs + the job's mounts are visible — no host /,
  no other job dirs, no dep cache. There is nothing to bind-mount escape to.
• /proc: the jail's own pid namespace — the worker and other jobs aren't
  visible.
• uid: a single-uid jail — an escape lands as the unprivileged worker user.
• network: the job's network (same as any bash job).

Image storage, freshness & limits Copy Chapter

• Where pulls live: a content-addressed cache of flattened rootfs tars (+ OCI
  config sidecars) keyed by image digest, under {ROOT_CACHE_DIR}/sandbox_rootfs
  (persistent, dedups pulls across jobs). The per-job extracted rootfs lives in
  {job_dir}/rootfs and is removed with the job.
• Freshness (SANDBOX_IMAGE_PULL_POLICY, default newer): the cache is keyed
  by digest, so a moving tag whose digest changed re-pulls automatically. newer
  (default) / always re-resolve the digest each job (one cheap crane digest
  manifest fetch); missing reuses a cached digest for the ref without hitting the
  registry; never only uses the cache (errors if absent). Pinning a digest
  (img@sha256:…) is immutable and never stale.
• Per-image size cap (SANDBOX_IMAGE_MAX_SIZE_MB, default 0 = off): images
  whose compressed download size (crane manifest) exceeds the cap are rejected
  before any layer is downloaded.
• Cache size cap (SANDBOX_IMAGE_CACHE_MAX_MB, default 0 = off): best-effort
  eviction — after a run, the oldest cached rootfs tars (by creation time) are
  removed until the cache is back under the cap.

Requirements Copy Chapter

• crane and tar on the worker
  for image pull/extract (a single static binary — no daemon, root, or privileged).
• nsjail on the worker — required. If nsjail is absent, a # sandbox
  job errors clearly (use a bare # docker + a daemon instead).

Limitations (by design — daemonless, run-to-completion) Copy Chapter

• No docker run -d + later exec/attach/logs -f, no docker build,
  compose, swarm, healthchecks.
• No arbitrary -v host bind mounts, --privileged, --cap-add, --device,
  host namespace sharing.
• Images that drop to a non-root uid or chown to arbitrary uids inside need a
  subuid range in the jail (single-uid only today — follow-up: newuidmap
  range mapping).

Follow-ups Copy Chapter

• Subuid-range nsjail variant for multi-uid images.
• Per-container isolated networking (slirp/pasta).
• Support under the non-nsjail unshare isolation mode.

Duckdb Execution (`docs/duckdb-execution.md`) Copy Section

How DuckDB jobs run

A DuckDB job runs in windmill_duckdb_runner, a binary built from
backend/windmill-duckdb-ffi-internal (src/bin) and shipped next to the windmill binary.
The worker spawns one runner per DuckDB connection a job opens: the job itself, and the
on_schema_change=sync pre-pass. It never loads the engine into its own process.

The runner gets the same job isolation as every other language. duckdb_runner::runner_mode
decides it per job:

There is no SQL-level policy on top. A script can read and write what the jail lets it and load
any extension DuckDB will sign, as a Python job can read the same files and load native code.
Under nsjail its disk use is capped from the worker (below). Network egress is open in every
mode, as it is for the other languages: the S3 proxy, metadata endpoints and internal hosts are
all reachable.

What crosses to the runner Copy Chapter

spawn_runner writes one JSON request to the runner's stdin, so nothing secret is put on argv or
into the environment:

• the blocks, already rewritten by the worker: resource ATTACHes, DuckLake and datatable
  attaches with their credentials inlined, workspace macros;
• the job args, the job token, base_internal_url and the workspace id. The runner derives the
  S3/GCS secrets for the workspace's S3 proxy from the token, which it reaches over the network;
• memory_limit (DUCKDB_MEMORY_LIMIT, or 80% of the worker's cgroup), the temp directory (the
  job dir), and the result-size cap (MAX_SQL_RESULT_SIZE).

The environment is cleared except PATH, HOME (outside nsjail), DUCKDB_MAX_TEMP_DIRECTORY_SIZE,
GOOGLE_APPLICATION_CREDENTIALS (BigQuery ATTACH, whose credentials file nsjail mounts) and the
proxy variables. In particular a script cannot use the worker's own AWS_* credential chain.

Under nsjail the job dir is mounted read-only at its host path, so every path the worker wrote
into the blocks (Postgres root certificates) resolves unchanged. The one writable place on the
host is {job dir}/duckdb/: the spill directory and the result file, which the worker opens
without following symlinks, since the script can write there.

The runner writes its answer to a file in that directory: a first line with the last statement's
column order, then either the collected results or ERROR <json-encoded message>.
finish_run / finish_prepare in duckdb_executor.rs turn that into the job result.

Failure, cancellation and memory Copy Chapter

• A crash fails the job, never the worker. A runner that exits without writing its result
  becomes The DuckDB engine process … The worker is unaffected. with the tail of its stderr.
  An engine std::terminate (for example a remote temp_directory that has to spill) and a
  panic in result rendering both land here.
• Cancellation and timeouts kill the engine. The executor drops the job future, and
  KillGroupOnDrop kills the runner's whole process group. Killing the spawned process alone is
  not enough: unshare --fork leaves its child running.
• The kernel OOM-kills the runner, not the worker. JOB_OOM_SCORE_ADJ is written in
  pre_exec, before nsjail or unshare is exec'd, so the runner inherits it. Written after spawn
  it would race their fork, and the jail's /proc is read-only. Within the job, DuckDB bounds
  itself through memory_limit and spills past it.
• Runner output is bounded. stdout/stderr are drained keeping only the last 4 KB: a script
  controls how much the engine prints (CALL enable_logging(storage='stdout')).

Extensions Copy Chapter

Outside nsjail the runner uses the worker user's ~/.duckdb/extensions and installs whatever a
script names.

Under nsjail it gets its own cache, ~/.duckdb/windmill_jail_extensions, mounted read-only.
Only the worker writes to it: before a jailed run it starts the runner unjailed in install
mode, with only INSTALL statements it built itself, for httpfs, azure, parquet, json
and every extension a block installs by name. That runs no script SQL and loads nothing, so no
extension code runs outside the jail. It happens once per extension per worker process, except
for FORCE INSTALL, which is honored every time and becomes a plain INSTALL inside the jail.
A job can therefore neither add to nor replace what the next job loads from that cache. An
extension used without being named and outside those four (icu behind SET TimeZone, say)
fails on the read-only mount: name it with INSTALL.

A script can still point extension_directory somewhere writable and install into it. That only
affects its own connection, and is no more than a Python job could do.

The engine Copy Chapter

The runner links duckdb-rs from crates.io, pinned with = so cargo update cannot move the
engine. The engine loads the prebuilt extensions from extensions.duckdb.org, compiled for the
exact release it reports, so an engine bump is a version change in Cargo.toml and nothing else.

Confinement belongs to the job isolation, not to engine settings. DuckDB spills through the same
file system a query reads with, so disabled_filesystems='LocalFileSystem' also stops every query
that outgrows memory_limit, and no combination of stock settings gives "spill, but no other local
file access". Fencing the engine at the SQL level again would need a patched engine to keep
spilling.

Bounding disk use Copy Chapter

The cap is DUCKDB_MAX_TEMP_DIRECTORY_SIZE, or 6GiB on a CLOUD_HOSTED worker that does not
set it. The cloud runs one worker per pod on nodes with no per-pod ephemeral-storage limit, and a
worker pod already holds several GiB of runtime caches, so the cap is sized against a mostly full
node; re-measure before raising it. Unset elsewhere, DuckDB's own default applies: 90% of the
temp volume's free space.

It is applied twice:

• DuckDB's max_temp_directory_size, set on every connection, so an ordinary query that
  outgrows it fails with the engine's failed to offload data block … max_temp_directory_size
  error and cleans up its temp files.
• A worker-side watchdog, under nsjail. A script can raise the engine setting, so on its own
  it is only a default. The worker sums the allocated size of {job dir}/duckdb/ once a second
  and kills the runner when it passes the cap, whatever wrote the bytes: spill, COPY … TO, or
  the result file. With a disk-backed jail /tmp (NSJAIL_TMP_BACKING=disk) that /tmp lives
  inside the same directory, so it counts too; a RAM-backed one is bounded by its tmpfs size.
  Enforcement lags by up to a second of writing. Outside nsjail the script can
  write anywhere the worker user can, as other languages can, so there the cap stays a default.

The value is DuckDB's spelling, not Kubernetes': KiB/MiB/GiB/TiB for 1024-based units,
KB/MB/GB/TB for 1000-based. An unparseable value fails every DuckDB job on the worker
rather than being ignored. DUCKDB_MEMORY_LIMIT is spelled the same way, and the two are set in
separate statements so a parse error names the right variable.

Ducklake Materialization (`docs/ducklake-materialization.md`) Copy Section

DuckLake-native materialization

Design sketch for "managed, versioned, incremental" assets built on the
DuckLake substrate. This is a companion to pipelines-vs-dbt.md
and extends its Path C (hybrid, partition-first) recommendation. For the
extract-load side that feeds these materializations, see §"Ingestion (EL)"
at the end of this doc (user-facing guide: windmilldocs
core_concepts/63_pipelines → "Ingestion (EL)"). The new
contribution here is leveraging DuckLake's snapshot/time-travel layer, which
the earlier doc's incremental deep-dive did not use. The annotation grammar is
reconciled with that doc — // partitioned + // unique_key + // append
stay canonical; nothing here forks a competing vocabulary.

The core reframe Copy Chapter

dbt had to build a materialization engine (compile SQL → CREATE TABLE AS /
incremental MERGE / SCD2 snapshots) because the warehouse gives it nothing
but raw SQL over a mutable table. DuckLake hands us, at the storage layer, the
four things that engine exists to provide:

Windmill already attaches DuckLake fully — see transform_attach_ducklake
(backend/windmill-worker/src/duckdb_executor.rs:661), which rewrites a user's
ATTACH 'ducklake://name' AS dl into the real
ATTACH 'ducklake:postgres:…' AS dl (DATA_PATH 's3://…', OVERRIDE_DATA_PATH TRUE, AUTOMATIC_MIGRATION TRUE) at duckdb_executor.rs:730. But today every
write is a destructive overwrite and all four capabilities above are thrown
away — snapshots are never surfaced, partitioning is purely an orchestration
concept disconnected from the physical layout.

So the materialization engine we need is a thin layer — a write-strategy
wrapper + snapshot capture — not a dbt rebuild. This is exactly the
"buy A's 80% without B's dialect-rewriting tax" tradeoff pipelines-vs-dbt.md
argued for; DuckLake is what makes the remaining 20% (versioning, reproducible
reads, materialization history) nearly free instead of a second project.

Annotation grammar (final) Copy Chapter

One self-documenting line; managed-by-default. Strategy options live on the
materialize line (they have no meaning without it), while // partitioned
stays separate because it is cross-cutting (cascade + scheduling + materialize).

// materialize ducklake://analytics/orders_daily              → managed, replace (default)
// materialize ducklake://analytics/orders_daily key=order_id → managed, merge (SCD type 1)
// materialize ducklake://analytics/orders_daily append       → managed, append
// materialize ducklake://analytics/dim_customer key=id history → managed, SCD type 2 history
// materialize manual ducklake://analytics/orders_daily       → track-only escape hatch
// materialize ducklake://analytics/raw_events on_schema_change=ignore
//                                  → managed, downstream contract warnings muted

• managed (default) — the script is setup + one trailing SELECT; Windmill
  generates the write DDL, captures the DuckLake snapshot, and records state.
  DuckDB-only; validated at deploy (a non-SELECT script is rejected with a clear
  error pointing to the wmll.ducklake helpers).
• manual — escape hatch: the script writes its own DDL; Windmill only
  records state (no snapshot capture, no idempotency guarantee). Rare; explicit.
• key=<col> → MERGE (upsert-by-key within the slice, SCD type 1 —
  overwrites history); append → INSERT-only; neither → DELETE-by-partition
  • INSERT (replace). append wins over key if both are given (deploy warning).
  • Source must have unique keys. The merge is delete-by-key + insert-all: it
    reconciles the incoming rows against the target (rows whose key is in the
    SELECT are replaced), but it does not deduplicate the source. Two
    incoming rows sharing a key would therefore both land under that key, silently
    breaking the "one row per key" contract. The managed write guards against this
    — the run fails with a clear error when the SELECT returns more than one
    row for a non-NULL key (NULL keys are exempt, matching the insert-only path).
    Deduplicate in the SELECT (e.g. QUALIFY row_number() OVER (PARTITION BY <key> ORDER BY <recency-col> DESC) = 1) or use append if duplicates are intended.
• key=<col> history [track=<c1,c2,…>] [deletes=close] → upgrades the keyed
  merge to managed SCD type 2 history (the leading keyword scd2 is an alias).
  The SELECT is the current snapshot (one row per key), and the runtime adds
  valid_from/valid_to/is_current; a change to any tracked column (track=,
  default all non-key) closes the prior version and opens a new one, keeping full
  history. Diff → close-old (UPDATE) → open-new (INSERT) in one transaction;
  the effective timestamp is the transaction clock (now()), so a run is
  self-consistent. v1 is non-partitioned only (// partitioned + history is
  rejected at deploy), and it requires key= (also rejected at deploy).
  Both misconfigurations fail fast at save with a clear message instead of on the
  first run. Unlike manual, it is managed, so // data_test and schema capture
  work.
  • Deletes. By default a key that disappears from the snapshot stays current
    (soft delete — dbt's hard_deletes=ignore). deletes=close opts into
    hard-delete-close: the vanished key's current version is closed (valid_to
    set, is_current=false) with no new version — dbt's hard_deletes=close. If
    that key later reappears in the snapshot it opens a fresh version, leaving a
    validity gap between the delete and the reactivation (correct SCD2).
  • Reserved names. valid_from/valid_to/is_current are reserved column
    names in this mode — a SELECT that already projects one fails at run time —
    and the <dim>_current suffix is reserved for the companion view (below), so
    don't separately materialize a table by that name in the same lake (the view
    is created IF NOT EXISTS inside the write transaction, so such a collision
    is skipped silently — the _current convenience is simply absent — rather
    than erroring).
  • Key should be non-null. A NULL natural key is ill-formed for a
    dimension; the codegen matches keys null-safely so a NULL-key row is
    materialized rather than silently dropped, but you should enforce it with
    // data_test not_null <key>.
  • track= takes no spaces. Like every =-option in the annotation grammar
    (which is whitespace-tokenized), the track= value must be a bare
    comma-separated list with no spaces: track=name,tier, not track=name, tier
    (a space ends the value and the rest is silently ignored).
  • Schema is frozen at first run (persist-and-mutate, like merge/append):
    the history table is CREATE TABLE IF NOT EXISTS, so adding/removing a
    projected column later fails the run — an append-only history can't retroactively
    reshape closed versions. Changing the SELECT's columns needs a manual rebuild
    (the replace strategy is the only one that re-derives schema each run).
  • Consumer convenience. Each run (re)creates a <dim>_current view (WHERE is_current) in the same catalog, so the common "latest version" read needs no
    filter and downstream scripts can // on ducklake://…/<dim>_current. The
    effective-dated payoff is a native DuckDB ASOF JOIN against the history
    table: … ASOF JOIN <dim> d ON fact.key = d.<key> AND fact.ts >= d.valid_from
    returns, for each fact, the dimension version that was current at fact.ts —
    something neither merge nor DuckLake time-travel can do in one query.
• // partitioned <kind> — unit of work + state + backfill (separate;
  cross-cutting). Polyglot / multi-statement writes use the wmll.ducklake
  helpers instead of // materialize.

There is no wrap keyword — materialize is "manage the write," so it was
redundant; the only reason for it was to carve out the weak track-only mode,
which is now the explicit manual opt-out.

DuckLake snapshots are orthogonal to all of the above — they apply to every
strategy automatically because every write is a DuckLake commit. The user never
annotates for versioning; they get it.

Executor codegen Copy Chapter

The seam Copy Section

run_duckdb already splits the script into statement blocks and rewrites
custom ATTACH blocks in a single pass before execution
(duckdb_executor.rs:114-160):

let query_block_list = parse_sql_blocks(&query, true);
// each block: remove_comments → if ducklake/datatable ATTACH, expand; else passthrough

All blocks run in order on one DuckDB connection. Materialize has two modes:

1. Managed (default). The user writes setup + one trailing SELECT. Windmill
   replaces that SELECT with generated statements, wrapped in an explicit
   DuckLake transaction it controls — never textual BEGIN/COMMIT injected
   around the user's other statements (fragile across their own ATTACHs and
   multi-statement SQL). For a partitioned replace the SELECT block expands to:

   sql Copy

   -- generated for: // partitioned daily ; target = dl.orders_daily ; partition = '2026-06-19'
   CREATE TABLE IF NOT EXISTS dl.orders_daily AS
     SELECT *, CAST(NULL AS VARCHAR) AS _wm_partition FROM (<user_select>) WHERE false;  -- first-run bootstrap
   ALTER TABLE dl.orders_daily SET PARTITIONED BY (_wm_partition);
   BEGIN TRANSACTION;
   DELETE FROM dl.orders_daily WHERE _wm_partition = '2026-06-19';
   INSERT INTO dl.orders_daily SELECT *, '2026-06-19' AS _wm_partition FROM (<user_select>);
   COMMIT;

   The strategy variants are all DELETE+INSERT-shaped — no MERGE INTO, which
   DuckLake can't reliably run on a fresh partition (it 404s writing the first
   rows):

   • whole-table replace (no // partitioned) → a single CREATE OR REPLACE TABLE … AS (handles schema changes, still snapshots).
   • key=<col> → `DELETE FROM … WHERE [ AND] IN (SELECT FROM ())` then `INSERT` (upsert within the slice).
   • append → the DELETE is dropped (insert-only).

2. manual. The user writes their own DDL inside their own BEGIN … COMMIT;
   Windmill injects nothing into the body and only records state (no snapshot
   capture, no idempotency guarantee).

The _wm_partition column is the physical link the orchestration layer lacks: on
first materialize Windmill runs ALTER TABLE … SET PARTITIONED BY (_wm_partition)
so DuckLake prunes on read and DELETE-by-partition rewrites only that partition's
Parquet files.

{partition} in the user SELECT is the identity string, not a timestamp.
The token substitutes to the partition's identity — the same string stored in
_wm_partition and used for dedup/backfill/state — as an escaped SQL literal
('2026-07-05T23', '2026-W27', '2026-07', '2026-07-05'). Only the daily
identity happens to be a valid DuckDB DATE/TIMESTAMP literal, so a naive
WHERE ts = TIMESTAMP {partition} works for daily but raises a Conversion Error for hourly/weekly/monthly. Rather than make every author hand-write a
strftime format that must match the resolver's, the executor injects a
convenience macro as the first setup statement of a time-partitioned
materialize:

sql Copy

CREATE OR REPLACE TEMP MACRO wm_partition(ts) AS strftime(ts, '<fmt>');

so the filter is one grain-agnostic line: WHERE wm_partition(<ts_col>) = {partition}. The <fmt> comes from one source —
PartitionKind::default_time_format in windmill-parser (respecting a custom
format= override) — which the EE resolver also reads to stamp the identity,
so the macro and {partition} can never drift (hourly %Y-%m-%dT%H, weekly
%G-W%V, monthly %Y-%m, daily %Y-%m-%d). By construction
strftime(ts, fmt) equals the identity for any row in the slice, so this both
parses and buckets the whole partition (not just the boundary instant a
timestamp cast would match). The macro is timezone-agnostic (it formats ts as
given, matching the raw-strftime idiom it replaces); when a non-UTC tz= is
set the author expresses ts in that zone. dynamic partitions get no
macro — their identity is a caller-supplied key, so the filter is
WHERE <key_col> = {partition} directly. The scaffold and the AI pipeline
prompt teach the wm_partition form.

Storage: DuckLake writes go to s3://_default_/ through the windmill S3 proxy
(/api/w/{ws}/s3_proxy, gated behind the parquet + private features). The
proxy must sign the SigV4 canonical URI with single percent-encoding — the
SigV4 default (Double) 401s Hive-partition keys like _wm_partition=2026-06-19
(the = double-encodes to %253D vs the client's %3D).

Run summary capture Copy Section

After the generated blocks, Windmill appends one read block — it is both the job's
result (a useful preview rendered as the materialized table) and the row it records:

SELECT 'ducklake://<name>/<table>' AS materialized,
       '<partition>' AS partition,            -- only when partitioned
       (SELECT count(*) FROM <target> [WHERE _wm_partition = '<partition>']) AS rows,
       (SELECT max(snapshot_id) FROM ducklake_snapshots('<target>')) AS snapshot_id;

The snapshot_id and rows are persisted as materialized_partition metadata.
One extra round-trip per materialization, no new infra.

Metadata schema Copy Chapter

Extends the materialized_partitions table proposed in pipelines-vs-dbt.md
§"First implementation slice" with the DuckLake snapshot id:

materialized_partition (
  workspace_id      TEXT,
  asset_kind        TEXT,        -- 'ducklake'
  asset_path        TEXT,        -- 'analytics/orders_daily'
  partition         TEXT,        -- '2026-06-19'  (NULL for unpartitioned)
  snapshot_id       BIGINT,      -- DuckLake snapshot produced by this materialize
  row_count         BIGINT,
  job_id            UUID,
  materialized_at   TIMESTAMPTZ,
  PRIMARY KEY (workspace_id, asset_kind, asset_path, partition)
)

This one table drives four things at once:

• Observability — "last materialized: snapshot 42, 1.2M rows, 09:14" per
  asset node (closes the Dagster-catalog gap from the v1-readiness review).
• Run-stale / gap detection — which partitions exist, which are missing.
• Backfill — the missing/failed set is the backfill worklist.
• Snapshot pinning — see below.

Reproducibility — the beyond-dbt part Copy Chapter

Because every materialization records the snapshot it produced, you can read any
table as of a past version — a capability dbt has no native answer for (dbt
models are always "whatever's in the warehouse now"). DuckLake gives us this for
the cost of recording one integer per run, and it covers the three things people
actually reach for: debugging ("what did this table look like at the failing
run"), rollback (re-materialize a consumer from snapshot N), and ad-hoc
experimentation on a historical state.

How it's surfaced (shipped): explicit, discoverable time-travel Copy Section

The version is exposed as a user-driven surface, not hidden plumbing. A
consumer pins a read by writing the DuckLake clause directly:

FROM dl.orders_daily AT (VERSION => 42)

The asset node's History tab is a master-detail view: the snapshot list (id

• time) on the left selects the version previewed in a read-only grid on the
  right, which surfaces — and copies — the catalog-qualified
  FROM lake. AT (VERSION => n) clause. Snapshot ids are captured automatically; the user opts
  into pinning when they want it, and the clause degrades to "latest" if removed,
  so the same script still runs standalone. Mechanically this rides on
  time-travel reads (make_select_query / make_count_query emit the AT
  clause when a version is threaded through the WM_INTERNAL_DB_* markers) plus
  a DUCKLAKE_SNAPSHOTS read for the history list — capabilities DuckLake already
  has, no new write path.

Deferred: automatic snapshot pinning across the cascade Copy Section

An earlier sketch had the cascade automatically thread each producer's
snapshot_id into the trigger blob and inject AT (VERSION => $WM_UPSTREAM_SNAPSHOT)
into consumer reads, so a whole run is pinned to upstream state at dispatch time
without anyone asking. This is deliberately not built, for three reasons:

• Not critical. The only thing it adds over the explicit surface above is
  automatic per-run consistency — protection against an upstream
  re-materializing in the window between dispatch and a consumer reading. That
  race only bites high-frequency event-driven cascades (rare today), and the
  read is always a whole, ACID snapshot regardless — never corruption, just
  "newer than the triggering version". Debugging and rollback are already
  covered by the explicit surface.
• Implicit magic. Auto-injecting an AT clause and stripping it on
  standalone runs is invisible behaviour to debug when it misfires; the explicit
  clause is inspectable.
• Multi-upstream ambiguity + EE coupling. A consumer reading two ducklake
  upstreams needs a per-ref snapshot map accumulated across the AND-join — and
  the join-slot logic is EE. A single $WM_UPSTREAM_SNAPSHOT would silently pin
  every read to one (the firing) producer's snapshot.

If a workload ever shows the consistency race in practice, pinning can be layered
on top — the capture and the snapshot surfacing built here are its foundation.

What is built is the forensic slice of that sketch: when the cascade
dispatches a consumer, it records the latest captured snapshot of each of the
consumer's direct upstream assets into the job's trigger arg
(upstream_snapshots, rendered read-only on the run detail page with a
copyable AT (VERSION => n) clause). "What did the failing run actually see"
stays answerable after the fact, with zero read-path changes — reads are
not pinned to the recorded versions.

This is distinct from SCD2 history (// materialize … key=… history), which is built:
DuckLake time-travel answers "what did the whole table look like at snapshot N?"
but not "give me each entity's version history as queryable rows"
(valid_from/valid_to/is_current) — the shape dbt's {% snapshot %} produces
and downstream dimensional queries join against. The scd2 strategy generates and
manages that history table (see the strategy bullet above).

Data tests (`// data_test`) — and the extensible-annotation pattern Copy Chapter

Data tests are the first dbt-parity gap closed on top of materialization, and
the first deliberately extensible annotation. The design goal was not just
"add five test types" but to establish the convention a sibling family
(column-lineage is the next one) follows, so the annotation vocabulary stops
being a closed hardcoded list (pipelines-vs-dbt.md gap #7).

Grammar Copy Section

// data_test unique <col>
// data_test not_null <col>
// data_test accepted_values <col> = a,b,c
// data_test relationships <col> -> datatable://other/asset.<col>
// data_test <script_path>            ← escape hatch (dbt's singular test)

// data_test lines accumulate (every well-formed line adds one check),
unlike the single-value annotations (// materialize, // partitioned, …)
which are first-write-wins. Malformed lines are dropped fail-safe — a typo
becomes an absent check (visible in the graph), never a mis-parsed one.

The keyword is data_test, not test — there is an unrelated, shipped
// test: CI-test annotation (windmill_common::schema::parse_ci_test_annotation,
tests a script's logic on deploy). data_test tests the data in the
materialized asset at run time. This mirrors dbt 1.8's own tests: →
data_tests: rename, made for exactly this disambiguation.

The pattern: annotation → verifier Copy Section

The reusable shape, in three layers, each a clean extension seam:

1. Parse (asset_parser.rs + parsePipelineAnnotations.ts, kept in
   lockstep by the parity corpus). A data_test line is dispatched on a
   keyword head to a typed variant (DataTest). A new built-in is one
   match arm + its sub-parser; the Custom arm is the open fallback. A sibling
   family reuses this head-keyword dispatch rather than adding a parallel list.
2. Compile (sql_materialize.rs::build_data_test_checks). Each test becomes
   a check: (name, probe), where the probe is a one-row query counting
   and sampling the test's violating rows in a single scan. Built-ins differ
   only in their violating-rows query; Custom supplies its own (the user's
   SELECT of violating rows). Referenced assets (relationships) emit an
   ATTACH resolved by the same transform pass as the user's own.
3. Execute (duckdb_executor.rs). The materialize summary query embeds
   every check's outcome in one data_tests list-of-struct column (probes are
   lifted into one-row CTEs and cross-joined, since DuckDB rejects subqueries
   inside struct literals), so all tests run in a single pass against the
   freshly-materialized slice — no abort-on-first. The worker reads the
   breakdown from the result and decides pass/fail: any violation fails the
   run (record Failed, propagate up the cascade) with an error listing
   every test (✓/✗ + counts); a clean run returns the per-test summary so
   the UI can render a checklist.

Violating-row samples Copy Section

Each check's probe also captures a bounded sample of its violating rows
(≤20 rows, ≤50KB serialized per test — over-cap samples are dropped, never
truncated, since truncated JSON fails parsing after paying the bytes). The
sample is optional by contract at every layer: enforcement reads only the
count; NULL / dropped / unparseable samples degrade to "no sample" and can
never flip pass/fail. Where they surface:

• Failed run: the worker returns a structured error payload
  (Error::ExecutionRawError), so the failed job's result carries
  error.data_tests: [{test, violating, sample?}] with samples on failed
  tests only. The error message stays counts-only, plus a pointer at the
  payload — so run descriptions and materialized_partition.error carry no
  row data. The full payload is the job result, and follows every failed
  job's result wherever that already goes: the worker's failed-job log line
  (add_completed_job_error) and, on EE, the args of configured
  global/workspace error handlers — the same pre-existing paths that carry
  e.g. a failed process's own result.json. A handler that formats only
  error.message (the common alert shape) stays counts-only; one that wants
  the offending rows can read error.data_tests. The duckdb executor's
  password-sanitize catch-all must special-case ExecutionRawError —
  flattening it to a string would silently strip the payload.
• UI: DataTestsResult.svelte renders a per-failed-test expandable
  AutoDataTable; DisplayResult.svelte prefers the structured payload and
  falls back to text-parsing the breakdown for results predating it.
• Enterprise (write-audit-publish): samples only exist on the
  commit-then-test path. Under the EE in-transaction guard a violation aborts
  pre-commit and the slice rolls back, so the violating rows cease to exist —
  WAP failures are counts-only by construction (the guard's in-SQL ✓/✗
  message), and the guard projects only the probe's count column
  (pipeline_advanced_ee.rs::data_test_guard_sql).

Sample mechanics worth knowing:

• The sample rides as a JSON string (to_json(...)::VARCHAR) through the
  summary row, deliberately: expanded rows would be visible to the executor's
  key-recursive extract_i64(result, "rows"/"snapshot_id") scans, which a
  user column of the same name could corrupt. It is parsed only inside
  extract_data_tests.
• No ORDER BY on the violating rows — which rows land in the sample is
  nondeterministic (labelled "sample" in the UI for that reason).
• unique samples {value, count} pairs at its count's grain (number of
  duplicated values), so count and sample can't contradict each other.
• On partitioned targets the synthetic _wm_partition column is EXCLUDEd
  from samples (same rule as schema capture).
• A custom body joining with SELECT * can yield duplicate column names —
  duplicate JSON keys keep the last value; harmless but visible.
• The probe shape is gated by in-memory tests against the bundled engine in
  windmill-duckdb-ffi-internal (json extension availability, zero-row NULL
  degrade, exotic types) — a sample-expr runtime error would fail the summary
  read after the write committed, so keep those green when touching the shape.

A new annotation family that produces post-materialize checks (or, for
column-lineage, post-materialize metadata reads) plugs into the same three
seams: add a parsed variant, emit its check/reader SQL into the summary, read
it back in the worker. Nothing about the closed set of today's keywords is
load-bearing.

Cascade ordering (`test_edges`) Copy Section

A relationships test — and, best-effort, a custom test whose body reads a
pipeline asset — needs the referenced asset to exist at test time, but it is
not a data-consumption // on/read of that asset, so it contributes no
lineage edge. Without an ordering constraint, a cold cascade can run the tested
script before the referenced dimension is materialized and hard-fail
(Catalog Error: Table … does not exist).

asset_graph (windmill-api-assets/src/lib.rs) closes this by resolving the
referenced asset's in-pipeline producer (from the write edges it already
builds) and emitting an ordering-only test_edges entry
producer → testing_script. It is:

• Only emitted when a producer exists in the graph. An external table (no
  in-pipeline producer) adds no edge — the missing dependency is real and the
  runtime error is the correct signal. Self-edges (a script relationship-testing
  its own output) are dropped.
• Rendered distinctly — amber dashed "test needs" link on the canvas, apart
  from lineage (blue/gray solid) and triggers (gray) — because the tested script
  does not ingest the asset's rows.
• Fed into the cascade topo-sort via buildLineageDag
  (AssetGraph/boundedCascade.ts), routed through the referenced asset node
  (asset → testing_script) so the existing producer → asset write edge
  extends into producer → asset → testing_script and the two-hop
  buildLineageDownstreamMap invariant holds. Bounded and full-pipeline runs
  (computeInducedSchedule) then order the producer first.

Scope caveat: this orders within a single client-driven cascade (dev run /
bounded run / full-pipeline run). It does not change the production reactive
asset-dispatch of two independently-scheduled roots — there, relationships
targets on a disjoint root should still be co-scheduled or bound with an
explicit // on <dim> if a hard ordering is required.

Scoping decisions (v1) Copy Section

• Partition scope. When // partitioned, built-in checks are scoped to the
  slice just written (WHERE _wm_partition = <value>), so a rerun/backfill of
  one partition is independent of other partitions' (possibly pre-existing)
  data. Whole-table assertions are a follow-up.
• SCD2 scope. On a key=… history target, built-in checks assert the
  current snapshot (WHERE is_current): the history table legitimately
  repeats the natural key across closed versions, so an unscoped
  unique(<key>) would fail the run on the second change of any key. Custom
  tests see the raw history and scope themselves.
• Commit-then-test (public) / write-audit-publish (enterprise). In the
  public build, like dbt, the write commits before tests run; a failed test
  fails the run (and records Failed, so downstream cascade stops) but does
  not roll back the committed snapshot — time-travel still lets you inspect
  exactly what failed. Enterprise upgrades this to write-audit-publish: the
  same checks also run inside the write transaction as a guard statement
  that raises on any violation, aborting the run before COMMIT — a failing
  slice is never published, readers keep the previous version, and no snapshot
  is created (even a first run of a new asset rolls back to "no table").
  The whole mechanism lives in the enterprise repo:
  sql_materialize::build_wrap_blocks produces a typed statement plan
  (MaterializePlan — statements plus structural kinds and the compiled
  checks), and pipeline_advanced::finalize_materialize_query assembles it —
  verbatim on the public build, restructured into the guarded transaction on
  EE. The public build thus carries only plan metadata, not the
  write-audit-publish transform itself.
• Custom = DuckDB SQL, server worker. The escape hatch fetches the deployed
  script's content (a single DuckDB SELECT/CTE returning the violating rows —
  it's embedded as a subquery, so a multi-statement body is rejected with a
  clear error) and inlines it as a check; {partition} is substituted and
  _wm_target is in scope. Agent (Http) workers — which have no script cache —
  get a clear error. Non-DuckDB custom tests (dispatched as sub-jobs, any
  language) are the natural follow-up and fit the same verifier seam.
• Managed only. // materialize manual + // data_test is rejected with a
  clear error (we can't know the manual script's target alias / partition col).

Schema contracts (save-time, gap #2b) Copy Chapter

The captured schema (#2a) is read back as a contract: at save/deploy time,
every consumer's asset references — body-read/written columns, // column
lineage sources, // data_test relationships refs — are diffed against the
latest materialized_asset_schema version of each referenced ducklake asset,
and mismatches surface as warnings (deploy never blocks; dbt's
on_schema_change=fail is deliberately absent in v1). The diff lives in
windmill_common::schema_contracts (endpoint:
POST /w/{ws}/scripts/check_schema_contracts, called by the UI right after a
save) and is mirrored 1:1 by the editor (schemaContracts.ts): live Monaco
warning squiggles from the WASM buffer parse, plus column-name completion for
annotation refs fed by the same captured schemas.

Comparison rules worth knowing: column names are case-insensitive (DuckDB
unquoted-identifier semantics); _wm_partition is whitelisted (it's excluded
from capture); {partition} tokens are stripped before lookup; a
<dim>_current reference falls back to the scd2 base table's capture; a
relationships join across two captured assets also flags a captured-type
difference (the runtime probe coerces, so it's "differs", not "will fail").
An asset with no capture (never materialized, manual mode, any
datatable://) produces no warnings.

The producer opts a deliberately unstable schema out with
// materialize … on_schema_change=ignore — consumers then get a single
informational "suppressed" note instead of per-column warnings. On manual
materialize the option is inert (nothing is captured) and deploy logs a
warning saying so.

Scoping decision: DuckLake vs DataTable Copy Chapter

Make DuckLake the materialization/versioning substrate; keep DataTable as the
live operational table with no versioning. DataTable is plain Postgres
(transform_attach_datatable, duckdb_executor.rs:742) — no native snapshots
or time-travel — so giving it the versioned/incremental story means building
MVCC-on-top ourselves (history tables, SCD2), precisely the complexity this
DuckLake approach exists to avoid. Clean split:

• ducklake:// → analytics, versioned, reproducible, backfillable.
• datatable:// → mutable app/operational state; partition idempotency via
  DELETE+INSERT still works, but no snapshot/time-travel layer.

Don't try to give both the full treatment for v1.

v1 slice (smallest viable) Copy Chapter

1. Partition runtime context — resolve (value, start, end) and surface as
   WM_PARTITION* bind/env (Path C step 1; partly built per the
   pipeline-partition-runtime work).
2. Physical partition wiring — _wm_partition column + SET PARTITIONED BY
   on first materialize for ducklake:// targets.
3. Strategy templates — DELETE+INSERT default (CREATE OR REPLACE for the
   whole table); delete-by-key + insert when key=<col>; INSERT-only when
   append. Managed // materialize wraps a single-SELECT DuckDB script behind
   these templates; // materialize manual opts out.
4. Snapshot + metadata capture — append ducklake_snapshots read, persist
   materialized_partition rows.
5. Surface it — last-materialized/snapshot/row-count on the asset node;
   missing-partition set feeds the backfill UI.
6. v1.x — time-travel UX over the captured snapshots: a per-asset History
   tab — a master-detail snapshot list + query-at-version preview that copies the
   full FROM lake. AT (VERSION => n) clause. Automatic cascade pinning
   ($WM_UPSTREAM_SNAPSHOT) is deferred — see §"Reproducibility" for why.

Steps 1–5 are a thin annotation+template layer plus one metadata table and one
extra read per run. They deliver managed/incremental/versioned assets,
idempotent partitioned materialization, the backfill substrate, and
materialization observability together — and stay recognizably Windmill-shaped.

Open decisions Copy Chapter

These ride on top of the six in pipelines-vs-dbt.md §"Decisions either path
forces"; DuckLake-specific:

1. Bootstrap of SET PARTITIONED BY. First-materialize detection — table
   absent vs. present-but-unpartitioned. Idempotent re-apply.
2. Snapshot retention / compaction. DuckLake snapshots accumulate; when do
   we expire old ones, and does pinning hold a snapshot alive past retention?
3. Pin scope. Pin only direct producers, or the full transitive upstream
   set per run? Storage and "stale pin" semantics differ.
4. Managed multi-statement. Resolved: managed // materialize accepts
   setup statements (ATTACH/SET/…) followed by exactly one trailing SELECT, and
   rejects anything else at deploy with a clear error pointing to
   // materialize manual. The classifier (sql_materialize.rs) is the single
   source of truth.

Ingestion (EL): the sanctioned entry-node shape Copy Chapter

Design constraints for how external data enters the lake — the user-facing
how-to (extract-engine choice, cursor recipes, schema-drift handling, worked
examples) lives in windmilldocs core_concepts/63_pipelines → "Ingestion
(EL)"; this section records only what future feature work must not break.

• A ducklake:// // materialize is DuckDB-only (deploy-rejected
  elsewhere, managed and manual alike — windmill-api-scripts/src/scripts.rs;
  a dbt:// warehouse-relation target is the one any language but dbt's own may
  declare, and it is track-only — see docs/dbt-runtime.md), and the SDK
  materialize helpers (upsert_partition / upsertPartition) build their SQL
  inside the SDK, so the asset parsers cannot see the write. A polyglot node
  that "writes the lake directly" therefore deploys with no output edge —
  breaking lineage, cascade scheduling, and the backfill UI's producer lookup.
• The sanctioned polyglot shape is two scripts: an entry node that lands
  the raw batch as an object in workspace storage (write_s3_file — a
  parser-visible write), and a DuckDB loader (-- on s3:///<key> +
  -- materialize) that the asset dispatcher re-runs per batch. The landing
  object is the seam; splitting E from L also keeps row work vectorized and
  gives the load the full engine treatment (strategies, snapshots, partition
  grid, // data_test).
• One string spelling: s3:///<key>. SDK string params are strictly
  s3://… URIs with a non-empty key; anything else raises (clients >
  1.746.0 — older clients silently upload such strings to an auto-generated
  windmill_uploads/… name, so keep URIs in code that must run on them).
  The same URI is what
  // on annotations and DuckDB SQL take, and all forms (URI, {s3} object,
  S3Object(s3=…)) canonicalize to path /<key>. The asset parsers record
  no asset for a bare-string SDK argument (the call can only error at
  run time) — keep parse_s3_object (py client), parseS3Object
  (ts client, s3Types.ts) and both s3_object_arg_path parsers in lockstep
  when touching any of them.
• DuckDB workers load no ICU extension: bare TIMESTAMPTZ - INTERVAL
  arithmetic binder-errors. Incremental-pull SQL casts both sides
  (updated_at::TIMESTAMP > now()::TIMESTAMP - INTERVAL 7 DAY).

Enterprise (`docs/enterprise.md`) Copy Section

Enterprise (EE) Development

File Conventions Copy Chapter

• Enterprise files use the *_ee.rs suffix
• Source lives in windmill-ee-private (sibling repo), symlinked into each crate's src/
• _ee.rs files are gitignored in the main windmill repo — tracked only in windmill-ee-private
• Use feature flags: #[cfg(feature = "enterprise")] for enterprise logic
• The private feature flag gates compilation of *_ee.rs files
• The license feature flag gates features that require a valid license key at runtime
• Isolate enterprise code in separate modules

Finding the EE Repo Copy Chapter

• Standard location: ~/windmill-ee-private
• Worktree location: ~/windmill-ee-private__worktrees//

Detecting EE Changes Copy Chapter

The *_ee.rs files in the windmill repo are symlinks — changes won't appear in git diff of the windmill repo. Check the EE repo directly: git -C <ee-path> status --short

EE PR Workflow (MUST DO whenever the change has an EE companion PR) Copy Chapter

If your work requires any change in windmill-ee-private (modifying *_ee.rs
files, adding new ones, adjusting EE-only modules, etc.) — i.e. you need to
open a companion PR on windmill-ee-private — then the windmill (OSS) PR
counts as an EE PR even when its own git diff only touches
backend/ee-repo-ref.txt. The symlinked *_ee.rs files won't appear in the
OSS diff, but the OSS build pulls them in via the EE ref, so reviewers need
to know.

1. Prefix the windmill PR title with [ee]: [ee] <type>: <description>
2. Create a matching branch in windmill-ee-private (same branch name)
3. Commit and push the _ee.rs changes in that branch
4. Create a companion PR on windmill-ee-private with a link to the windmill PR (no [ee] prefix on this one)
5. Update ee-repo-ref.txt: Run bash write_latest_ee_ref.sh from backend/
   • Verify it wrote the correct commit hash from your branch, not from main (the script may fall back to ~/windmill-ee-private on main)
   • If wrong, manually write the correct hash
6. Commit ee-repo-ref.txt in the windmill repo so CI picks up the correct EE ref

Validation Copy Chapter

# EE code (always include private to compile *_ee.rs files)
cargo check --features enterprise,private

# EE code that also requires license validation
cargo check --features enterprise,private,license

Troubleshooting Copy Chapter

E0583: file not found for module <x>_ee on cargo check --features enterprise,private:
the EE worktree is behind the OSS code it must satisfy. Fast-forward it to EE
origin/main — EE worktrees are shallow clones, so run git fetch --unshallow origin
first or the merge fails with "refusing to merge unrelated histories". After the
fast-forward, any *_ee.rs file that is new since the worktree was created still
needs its OSS symlink made by hand (worktree setup only links files that existed then).
The EE repo has no backend/ prefix — crates sit at its root. Use an absolute target
(a relative one would resolve against the link's directory, not your cwd):

# from the windmill repo root
ln -s ~/windmill-ee-private/<crate>/src/<x>_ee.rs backend/<crate>/src/<x>_ee.rs

External Instance Datatables (`docs/external-instance-datatables.md`) Copy Section

External instance data tables

A data table is backed by one of three things: a Postgres resource a workspace brings
(postgresql), a database on Windmill's own cluster (instance), or a database on a separate
cluster Windmill administers (external_instance, Enterprise Edition). The third is what this
document covers; Ducklake catalogs take the same three shapes.

Windmill administers the external cluster the way it administers its own: it creates and drops
databases there, owns custom_instance_user and custom_instance_replication_user, and creates
the data table roles of that cluster. It logs in as the admin in the external_instance_pg
instance setting, and keeps what it generates in the hidden external_instance_pg_state setting.

Code Copy Chapter

What holds it together Copy Chapter

• One lifecycle lock. lock_external_instance_pg_state serializes everything that changes
  which databases exist on the cluster or which entries name them: setup, create, drop, data table
  and Ducklake saves, external role DDL, and writes to the setting itself. Anything reading the
  configuration to reach the cluster reads it under that lock, so a database is never created on
  one cluster and registered while the setting names another.
• Windmill only touches what it made. Databases it creates carry a comment, and a drop
  requires it. The two managed roles and every data table role carry their own comment, and setup
  refuses a custom_instance_user without it rather than resetting the password of someone else's
  role.
• Creation needs a successful setup. set_up_for records the host:port the last successful
  setup converged. Creating a database on a cluster that setup has not succeeded on is refused.
• Nothing is dropped from under a user. managed_database_uses lists every data table naming
  a database, every fork pointing at those, every Ducklake catalog on it, and every fork Ducklake
  metadata schema still to be dropped. Fork cleanup exempts exactly the entry it is cleaning up.
• Fork copies belong to a workspace. wm_fork_* is a name, not an authorization: every
  database of a cluster answers to the same custom_instance_user. The registry records the
  workspace a copy was created for, and a member can only import into or fork onto a copy of their
  own workspace.
• Roles are per cluster. datatable_role.cluster splits the catalog, so the same role name can
  exist on both clusters. Role names are unique per cluster, as they are in Postgres.

Running one locally Copy Chapter

docker run -d --name wm-external-pg -e POSTGRES_PASSWORD=external -p 5497:5432 postgres:18 \
  -c wal_level=logical
psql "postgresql://postgres:[email protected]:5497/postgres" \
  -c "CREATE ROLE wm_admin LOGIN PASSWORD 'adminpw' CREATEDB CREATEROLE REPLICATION"

A non-superuser admin with CREATEDB and CREATEROLE is the realistic case: managed Postgres
gives nothing more. REPLICATION is only needed for Postgres triggers on external data tables.

Then, as superadmin ($T is a token):

api=http://localhost:8000/api
curl -s -X POST $api/settings/global/external_instance_pg -H "Authorization: Bearer $T" \
  -H 'Content-Type: application/json' \
  --data '{"value":{"host":"127.0.0.1","port":5497,"user":"wm_admin","password":"adminpw","sslmode":"disable"}}'
curl -s -X POST $api/settings/external_instance_pg/setup -H "Authorization: Bearer $T" \
  -H 'Content-Type: application/json' --data '{}'          # report per step
curl -s -X POST $api/settings/external_instance_pg/databases/dt_demo -H "Authorization: Bearer $T" \
  -H 'Content-Type: application/json' --data '{}'
curl -s -X POST $api/w/admins/workspaces/edit_datatable_config -H "Authorization: Bearer $T" \
  -H 'Content-Type: application/json' \
  --data '{"settings":{"datatables":{"demo":{"database":{"resource_type":"external_instance","resource_path":"dt_demo"}}}}}'

sslmode defaults to verify-full; disable is for a local container only. With verify-full
against a server with a private CA, put the CA in root_certificate_pem — pg_dump, psql and
DuckDB attaches all verify against the system trust store plus that certificate.

Jobs then reach it as any data table: ATTACH 'datatable://demo' AS d from DuckDB, or
datatable://demo as the database of a PostgreSQL script, with -- role <name> to connect as a
data table role of that cluster.

Worth knowing while testing:

• A worker needs the postgresql and duckdb tags for those jobs
  (update config set config = jsonb_set(config, '{worker_tags}', …) where name = 'worker__default').
• DuckDB jobs run windmill_duckdb_runner from next to the worker binary, so a binary built
  into its own CARGO_TARGET_DIR needs DUCKDB_RUNNER_PATH pointed at the runner.
• Setup holds the lifecycle lock for its whole run, so a settings save during it waits.

Feature Telemetry (`docs/feature-telemetry.md`) Copy Section

Feature usage telemetry

feature_usage is the product-telemetry accumulator: day-bucketed counters that roll into the
anonymous usage-stats payload. It answers "does anyone use this, and which variant do they pick"
without any identifying data leaving the instance.

It currently carries 62 registered actions across twenty-one features (ai_session, ai_chat,
ai_fix, ai_agent, ai_decision, ai_agent_eval, app_sandbox, datatable, db_manager, flow_editor,
flow_run, flow_step, home, run_form, debugger, trigger, command_script,
hub_script, usage_meter, sso_groups_claim, cloud_trial_offer). Nearly all of the
product is uninstrumented, so new user-facing work is the opportunity to change that.

When to instrument Copy Chapter

Raise it in the plan, with the concrete vocabulary written out, and let the user keep or drop
it in one line. Don't stop and ask as a standalone question.

Propose it when a new user-facing affordance leaves a real question open:

• a new panel, mode, tab, toggle, or entry point — is it discovered and used at all?
• competing UX paths, or a new default — which one wins?
• an opt-in or beta gate — what is the take rate?
• a multi-step flow — where do people stop?

Stay silent for bugfixes, refactors, internal plumbing, and anything whose useful signal would
need per-item identifiers (paths, names, prompts, code) — those cannot be logged at all, see
Privacy rules. If the answer wouldn't change a decision, instrumenting is
overkill; say nothing.

Designing the vocabulary Copy Chapter

Identifier-shaped means ASCII alphanumerics plus _ - : . / — no spaces. Anything else is
rejected.

Supplying entity_id is what unlocks the distribution stats: the payload reports entity_count,
total_value, median_value, p90_value, and inactive_3d_entity_count per
(feature, kind, key). Omit it for a plain "how many times did this happen" counter. Keep the key
vocabulary closed and small — enumerate the values in a TS union next to the call site, the way
flowEditorTelemetry.ts does, so the whole set is reviewable in one place.

The recipe Copy Chapter

Four steps. Skipping step 1 fails quietly.

1. Register the pair in FEATURE_USAGE_KINDS
(backend/windmill-common/src/feature_usage_ee.rs, tracked in windmill-ee-private). An
unregistered (feature, kind) is dropped by is_recordable_event with a bare continue — no
error, no log, still a 204 to the browser. Frontend-only instrumentation records nothing and
looks like it worked.

2. Log from the frontend:

import { logFeatureUsage } from '$lib/utils/featureUsage'

logFeatureUsage('flow_editor', 'panel_placement', { key: 'force_detach' })

Fire-and-forget. Events sum locally per (workspace, feature, kind, key, entityId) and flush
every 30s, on visibilitychange → hidden, and on pagehide; 50 events per request, and a failed
batch is dropped rather than retried.

3. Check the disclosure. InstanceSettings.svelte describes a non-minimal payload by
category, not counter by counter (two places — the copy appears twice); keep it short. A plain
counter needs no edit. Only a key that sends a new kind of value beyond a count — a name or an
identifier, like the AI model and hub names it already lists — must be added there.

4. Verify a row lands. The silent-drop path means "no error" proves nothing:

SELECT feature, kind, key, entity_id, day, value FROM feature_usage ORDER BY updated_at DESC LIMIT 10;

Collection sits behind the private feature, so a public build records nothing from either the
HTTP route or the Rust helper. Run the backend with --features enterprise,private or this query
stays empty however correct the instrumentation is.

Privacy rules Copy Chapter

Only aggregated counts ever leave the instance, and only when telemetry is enabled and minimal
mode is off. Never put a path, prompt, script body, workspace name, email, or any user identifier
into key or entity_id. Entity ids must be opaque random ids, never anything that maps back to
a user or a resource. If the signal you want can only be expressed with identifying data, it
cannot be collected — drop it.

Counters aggregate over the last 30 days; rows are pruned after 60.

Logging from the backend Copy Chapter

A feature with no UI is instrumented the same way, from Rust:

windmill_common::feature_usage::log_feature_usage("trigger", "fired", kind.as_str());

Same registry, same key rules, and the same silent drop when the pair is unregistered. feature
and kind are &'static str so a call site cannot pass a computed pair. The call increments an
in-memory counter and returns; the monitor loop flushes the accumulator, so it is cheap enough for
hot paths — but only cheap per call, not free: a key with unbounded cardinality would grow the map
until it hits the per-action cap and starts dropping new keys.

There is no entity_id and no explicit value on this path: it counts occurrences.

feature_usage_ee holds the registry and the writer; the public build gets the inert
feature_usage_oss, since a CE instance never sends a stats payload.

Fork Object Storage (`docs/fork-object-storage.md`) Copy Section

Fork-isolated workspace object storage

A fork clones its parent's large_file_storage settings, so without isolation it reads and
writes the parent's objects: a script iterating in a fork overwrites production's files. Each
storage the fork isolates (a fork_storage_namespace row, chosen at fork creation, default
isolated) instead maps logical keys:

• Writes (put, multipart, delete, copy destination) go to __wm_forks/<fork segment>/<key>,
  the same one-component segment DuckLake's fork namespaces use (fork_data_dir_segment).
• Reads try the fork's own key, then each ancestor's mapping, nearest first, then the root's
  raw key. They stop at the first ancestor whose storage is a different one
  (lfs_entry_storage_ref): its keys are not in this bucket. A shared fork maps like its parent.
• Listings show the workspace's own objects only, as logical keys.
• Deletes remove the fork's copy; an ancestor's object stays readable through it.

The mapping (windmill_common::fork_storage::ForkStorageKeys) is applied wherever Windmill
turns a workspace-storage key into a storage operation: the ForkStore object-store wrapper
(windmill_object_store::fork_store::workspace_object_store) for every server-side helper, and
key rewriting in the S3 proxy's raw S3 forwarding (URI key, list prefix/start-after/marker
and response keys, x-amz-copy-source). A named resource (s3_resource_path) is not workspace
storage and is never mapped. A workspace whose keys are mapped is refused its workspace
storage's own credentials (s3_resource_info, the duckdb/polars settings endpoints): a client
holding them would write past the mapping.

Not mapped, on purpose: AI sessions and volumes (their keys already carry the workspace id),
SMTP attachments (job-id keys), the storage usage count (it counts the whole bucket). Known
gaps: the worker's result-cache hash HEADs the logical key unmapped, and a read of an object
the fork does not own costs one HEAD per chain level before the GET.

Invariants Copy Chapter

• __wm_forks/ is reachable only through the workspace's own chain. A key under it maps to
  itself when it is in the workspace's own area (writes) or an area of the chain (reads), and is
  refused otherwise; listings hide it. This is also what keeps DuckLake working: its fork data
  paths arrive already prefixed, and its defer attaches read ancestors' raw keys through the
  read chain. Every workspace gets the rule, isolating or not, so pre-existing forks' lakes keep
  working.
• No . or .. key segments, in any workspace: the proxy forwards mapped keys in a URL,
  where they collapse and would climb out of the fork's prefix.
• The prefix derives from the workspace id, so an isolating fork's id cannot be changed
  (change_workspace_id refuses): under a new id it would see none of its objects. Nor can an
  isolated storage be repointed, since the purge targets the storage recorded at creation.
• A shared lake needs its storage shared: its files must land at the parent's keys, where the
  parent's catalog looks for them. Fork creation refuses the other combination.
• Children before parents: deleting a fork that has forks of its own is refused when anything
  in its chain isolates a storage, shared forks included. A child reads through, or writes into,
  the isolating prefix via that link, and without it the child's chain would map like the root's.
• Deletion and same-id creation are serialized on the fork: advisory lock, and a purge
  skips an id that exists again: the rows are then the new fork's. The proxy caches only the raw
  client, never a mapping, so fork creation and deletion invalidate the one mapping cache.
• Deletion always purges the fork's area of each isolated storage, post-commit, from the
  storage identity recorded at creation: nothing can reach it once the fork is gone. Rows have
  no FK and outlive the workspace when the purge fails; creating a fork with the same id retries
  them and refuses while they cannot be purged, since a same-id fork would read them.
• Isolation is a per-fork-creation choice (shared_storages on create-fork), never inherited.
  Forks created before the registry existed have no rows and keep sharing.

Requiring isolation Copy Chapter

The RequireIsolatedForks protection rule on a workspace makes its forks leave nothing shared
with it, for every user the ruleset does not exempt (admins always are): no shared_storages,
no shared_ducklakes, and every data table whose database it owns cloned rather than kept as a
pointer. A data table whose database another workspace owns is left alone: that is not its data.
A clone governed from another workspace still has its own database, so it is not exempt. It is
checked against the workspace being forked, in
create_workspace_fork and in the git-branch pre-check (so a refused fork leaves no branch
behind), and the data table part again under the parent's lock_fork_datatables, which data table
edits also take: a table added between the two would otherwise reach the fork as a pointer.

Fork Triggers (`docs/fork-triggers.md`) Copy Section

Triggers and schedules in workspace forks

A workspace fork is a developer-controlled copy of a parent workspace, used to
test changes before merging back via git sync. Triggers and schedules in a
fork need special handling for two reasons:

1. Listener take-over: most trigger kinds (Kafka, Postgres, MQTT, NATS,
   SQS, GCP, Azure) attach to a stateful upstream resource. Two listeners
   sharing the same identifier compete for events.
2. Merge round-trip: every change made in a fork can flow back to the
   parent through git sync. If the fork sets a trigger to disabled, that
   value would otherwise overwrite the parent's enabled state on merge.

Cloning model — always cloned, always disabled Copy Chapter

Fork creation always runs clone_triggers_and_schedules. Every row in each
*_trigger table and in schedule is copied from the parent into the fork
with two invariants:

• Always disabled. The clone forces mode='disabled'::TRIGGER_MODE on
  triggers and enabled=false on schedules, regardless of the parent's
  state. Disabled rows have no side effects — no listener attaches to
  the upstream, no cron fires — so this clone is safe by construction.
  The user re-enables manually in the fork.
• Listener identifiers copied verbatim. Stored values for group_id,
  replication_slot_name, subscription_name, client_id,
  consumer_name, etc. are copied 1:1. Until the runtime-suffix work
  ships (see below), enabling a cloned listener in the fork would compete
  with the parent — the conflict warning below catches that case.

native_trigger (Nextcloud, Google Drive, GitHub) is intentionally not
cloned. Those triggers manage external webhook state we don't want
duplicated.

Non-workspaced HTTP triggers are also skipped. A row with
workspaced_route=false (and where neither CLOUD_HOSTED nor the
HTTP_ROUTE_WORKSPACED_ROUTE instance setting is on) has a runtime URL
without any workspace prefix. A clone would collide with the parent's row at
the matchit router level, where duplicate inserts are silently dropped — one
trigger would invisibly hijack the other. There is no namespacing escape
hatch for these (the whole point of workspaced_route=false is to skip the
prefix), so the clone filter excludes them. The fork user can re-create one
manually if they need it. When CLOUD_HOSTED or HTTP_ROUTE_WORKSPACED_ROUTE
is on, every route is workspace-prefixed at runtime regardless of the column,
and the clone copies all rows.

Non-workspaced email triggers are skipped on the same grounds. A row
with workspaced_local_part=false exposes a bare local_part@domain
address shared instance-wide; a clone would share the address with the
parent and incoming mail would be delivered arbitrarily. The clone filter
copies email triggers only when workspaced_local_part IS TRUE (or
CLOUD_HOSTED, since cloud scopes email lookup by workspace_id natively).

Operational state is owned by the parent Copy Chapter

The rule that makes both the normal-git PR merge and the in-app merge behave:

A trigger's mode (and a schedule's enabled) belongs to the parent
workspace. Git-sync reads the parent's value into a fork's synced file and
never writes a fork's value back. No git-sync / merge / create / update
write sets a fork's operational state; the setmode / setenabled endpoint
is the intended explicit mutator.

(Runtime error handling can still auto-disable an errored trigger or schedule —
that's orthogonal to this rule, which governs git-sync/merge/create/update
writes.)

This is enforced in two halves, keyed off parent_workspace_id IS NOT NULL
(the column, not the wm-fork-* naming convention — it stays consistent with
the conflict-warning gates and survives any future ID rename):

Read half — parent-value substitution on export. When the source workspace
is a fork, the tarball export at /api/w/{workspace}/workspaces/tarball
rewrites each trigger's mode (and each schedule's enabled) to the
parent's value for the same path, looked up at export time. A fork-only path
(absent from the parent) keeps the fork's own value — there's no parent state
to defer to, so it lands with whatever the fork creator set.

The earlier design stripped these fields instead. That broke a normal-git PR
merge: the parent branch (and the merge base) carries the line, the fork branch
dropped it, so the 3-way merge either silently deleted mode/enabled from
the parent — corrupting the source of truth — or conflicted outright when the
parent had also edited it. Substituting the parent's value makes the fork's
file byte-identical to the parent on that field, so the merge has nothing to
resolve.

Write half — fork writes never set operational state. A write into a fork
(git-sync push, merge deploy, clone, or a plain UI create) must not set the
state, otherwise pulling the substituted parent value straight back into the
fork would re-enable it. So create_trigger/create_schedule force disabled
for a fork target, and update_trigger preserves the fork's existing mode
(workspace_is_fork in windmill-trigger/src/handler.rs; schedule enabled is
naturally preserved because EditSchedule has no enabled field). The same
handlers serve both merge paths, so the two can't diverge. The fork owner
re-enables locally via setmode/setenabled (which carry the conflict
warning below).

For a non-fork target the incoming value is applied as given — so a fork→parent
merge of an existing trigger writes the parent's own (substituted) value (a
no-op), and a fork-only trigger lands with the fork creator's chosen state.

Conflict warning on enable Copy Chapter

The set_*_trigger_mode endpoint fires the warning whenever a fork transitions
to a mode that attaches a listener — Enabled or Suspended. Suspended is
not "off": the listener still attaches and consumes events; only the auto-run
of queued jobs is paused. Two suspended forks would still split Kafka events
or share a Postgres slot with the parent. Disabled is the only mode that
fully detaches.

The check fires whenever the parent workspace has a row at the same trigger
path — regardless of the parent's current mode/enabled. If so, the
endpoint rejects the request with an error string of the shape:

fork-conflict:<kind>:<parent_workspace_id>

The frontend's withForkConflictRetry helper detects this prefix, asks the
user to confirm via a dialog (a ConfirmationModal mounted at the logged
layout root, driven by the forkConflictModal store), and re-issues the
call with force: true if the user agrees. The CLI sees the raw error.

The check fires whenever the parent has the row because the fork's row was
cloned from the parent — the upstream identifier (Kafka group, PG slot,
SQS queue URL, GCP/Azure subscription, …) is shared by construction. That
sharing is a risk independent of the parent's current state:

• Both enabled → the listeners compete (split events) or fire twice.
• Parent disabled → the fork can destructively claim shared state (PG WAL
  advance, Azure secret_hash reuse, MQTT client_id race) before the parent
  re-enables.

The check is opt-out per kind via TriggerCrud::FORK_CONFLICT_ON_ENABLE
(default true). It is skipped for kinds whose upstream identifier is
already workspace-scoped at runtime — fork and parent there can never share
a real upstream: