SheetBot

SheetBot is an open-source automation and CI tool built with Node.js, Express.js, SQLite, Web Components, and JSON Schema. It provides flexible task orchestration and a widget-based UI for visualization, enabling cross-platform builds, remote installations, and bespoke automation without heavy dependencies.

Agent scripts still run on Deno, Python, Bash, or Node.js, so tasks can be written in the language that fits the job.

Core concepts

• Tasks — arbitrary scripts that run in any programming language.
• Agents — worker processes that poll for tasks matching their type and capabilities.
• Sheets — SQLite-backed tables for storing and visualizing task data.
• Artefacts — files uploaded or generated by tasks.
• Transitions — automated rules for changing task states.

Tasks are matched to agents with JSON Schema. See Why JSON Schema for the reasoning.

Quick start

Requires Node.js 22.6 or newer. Deno is still required for Deno-based agent scripts.

git clone https://github.com/jorisvddonk/sheetbot
cd sheetbot
npm install
npm run adduser
npm start

Then open http://localhost:3000/.

To run an agent:

export SHEETBOT_AUTH_USER=your_username
export SHEETBOT_AUTH_PASS=your_password
export SHEETBOT_BASEURL=http://localhost:3000

# Deno agent
deno run --allow-net --allow-env --allow-read ${SHEETBOT_BASEURL}/scripts/agent.ts

# Node.js agent
curl -fsSL ${SHEETBOT_BASEURL}/scripts/agent.js | node

Browse the docs

Start with Getting started, then Your first task and Setting up other agents. From there, learn how to run agents with API keys, build sheet dashboards, handle artefacts, and extend SheetBot with library scripts and custom widgets. For the bigger picture, read Architecture. See the project README for the full story.

Project structure

• lib/ — core libraries and data providers
• scripts/ — agent template files
• library/ — example automation scripts (examples)
• sheets/ — generated sheet database files
• static/ — web assets, HTML, and widgets
• init/ — initialization scripts run on server startup
• artefacts/ — task artefacts
• docs/ — documentation source

Tutorials

Guided lessons that take you from a fresh checkout to a running agent. Follow them in order the first time; each builds on the previous one.

When something does not work, see Debug a failed task, Troubleshooting, or the reference.

Getting started

This tutorial installs SheetBot locally, creates a login, and starts the server. It takes a few minutes.

Prerequisites

• Node.js 22.6 or newer — check with node --version.
• npm — installed with Node.js.
• git — to clone the repository.
• Deno (optional) — only needed for Deno-based agent scripts.

1. Clone and install

git clone https://github.com/jorisvddonk/sheetbot
cd sheetbot
npm install

2. Create a user

SheetBot stores users in users.db. Create your first user with:

npm run adduser

Choose a username and password. When asked for permissions, enter * to grant full access for local development.

3. Start the server

npm start

SheetBot listens on http://localhost:3000/ by default. Open that URL and log in with the user you just created.

The first start also creates tasks.db, secret.txt, and the artefacts/ directory. These files are gitignored.

4. Run an agent

Tasks are executed by agents, not by the server itself. In a second terminal, export your credentials and start the Node.js agent:

export SHEETBOT_BASEURL=http://localhost:3000
export SHEETBOT_AUTH_USER=your_username
export SHEETBOT_AUTH_PASS=your_password

curl -fsSL "$SHEETBOT_BASEURL/scripts/agent.js" | node

The Node.js agent polls once and exits. Run it in a loop, or schedule it with your operating system, if you want it to keep picking up tasks. See Setting up other agents for Deno, Python, and Bash agents.

Next

Continue to Your first task to create work for the agent.

Your first task

In this tutorial you create a small JavaScript task, let a Node.js agent execute it, and confirm that it completed.

1. Create the task

1. Open http://localhost:3000/addtask.html and log in if prompted.
2. Under Script Source, select Manual.
3. Set Task Type to Other (specify) and enter node.
4. Enter a task name, for example Hello from SheetBot.
5. Paste this script:

console.log("Hello from SheetBot");

6. Leave Status set to Awaiting.
7. Click Add Task and note the task ID.

2. Start the agent

In a terminal with your credentials exported:

export SHEETBOT_BASEURL=http://localhost:3000
export SHEETBOT_AUTH_USER=your_username
export SHEETBOT_AUTH_PASS=your_password

curl -fsSL "$SHEETBOT_BASEURL/scripts/agent.js" | node

The agent requests a task of type node, accepts it, runs the script, and reports completion. The script’s console.log output appears in the agent terminal. Because the agent polls once and exits, start it after creating the task, or run it in a loop.

3. Check the result

Open the task in the web UI. Its status should change from AWAITING to RUNNING and then COMPLETED.

Next

• Learn how tasks report data and dependency injection works in Data management.
• See how to write reusable scripts in Library script annotations.
• Browse the how-to guides for configuration and extension tasks.

Setting up other agents

The SheetBot server does not execute task scripts itself. Agents poll the server, claim matching tasks, run the scripts, and report the result. The Getting started tutorial uses the Node.js agent; this tutorial sets up the Deno, Python, and Bash agents.

You can run agents on the same machine as the server or on any other machine that can reach it.

Before you start

You need:

• The SheetBot base URL, for example http://localhost:3000 or https://sheetbot.example.com.
• A user or API key with permission to perform tasks.
• The runtime for the agent you want to use.
• A working directory for the agent. The templates read optional .capabilities*.json files from the current directory.

Set the common environment variables first:

export SHEETBOT_BASEURL=http://localhost:3000
export SHEETBOT_AUTH_USER=your_username
export SHEETBOT_AUTH_PASS=your_password

For unattended agents, prefer an API key instead of a password:

export SHEETBOT_AUTH_APIKEY=your.api.key
unset SHEETBOT_AUTH_USER SHEETBOT_AUTH_PASS

See Using API keys for agents for a step-by-step setup, or Manage API keys for the full reference.

Agents poll once per invocation. Run the command again, put it in a loop, or schedule it with cron, systemd, launchd, or another scheduler.

Deno agent

The Deno agent runs TypeScript tasks with type: "deno".

Requirements: Deno 2 or newer.

export SHEETBOT_BASEURL=http://localhost:3000
export SHEETBOT_AUTH_USER=your_username
export SHEETBOT_AUTH_PASS=your_password

deno run -A "$SHEETBOT_BASEURL/scripts/agent.ts"

-A grants all permissions. The agent needs network access, environment access, and permission to import the task script from the SheetBot host. Since agents execute arbitrary task code, full permissions are the simplest choice.

To narrow permissions, allow the SheetBot host for imports as well as network:

deno run \
  --allow-net \
  --allow-env \
  --allow-read \
  --allow-import=localhost:3000 \
  "$SHEETBOT_BASEURL/scripts/agent.ts"

Replace localhost:3000 with the host and port of your SheetBot server.

Python agent

The Python agent runs tasks with type: "python".

Requirements: uv and Python 3.12 or newer. The template declares its own dependencies, so uv installs requests automatically.

mkdir -p ~/sheetbot-agent
cd ~/sheetbot-agent
curl -fsSL "$SHEETBOT_BASEURL/scripts/agent.py" -o agent.py
uv run --script agent.py

You can also use the template’s shebang:

chmod +x agent.py
./agent.py

The Python agent executes task scripts with uv when they contain a /// script dependency block, and with plain Python otherwise.

Bash agent

The Bash agent runs tasks with type: "bash".

Requirements: Bash, curl, and jq.

export SHEETBOT_BASEURL=http://localhost:3000
export SHEETBOT_AUTH_USER=your_username
export SHEETBOT_AUTH_PASS=your_password

curl -fsSL "$SHEETBOT_BASEURL/scripts/agent.sh" | bash

The agent checks for curl and jq before doing anything. On Windows, use WSL or Git Bash.

Bash task scripts normally omit a shebang. If a script starts with #!, the agent executes it directly and treats its standard output as the completion data, so that output must be valid JSON. Inline scripts (no shebang) are run with bash and report empty data unless they set a data variable.

Verify an agent

Create a task of the same type as the agent, then run the agent. In the add-task page, set Task Type to deno, python, or bash and use a matching script:

// Deno
console.log("hello from Deno");

# Python
print("hello from Python")

# Bash
echo "hello from Bash"

The task should move from AWAITING to RUNNING and then COMPLETED. The script output appears in the agent terminal.

For a fuller task-creation walkthrough, see Your first task. Remember to change the task type to match the agent.

Keep an agent running

A simple loop is enough for a dedicated machine:

while true; do
  curl -fsSL "$SHEETBOT_BASEURL/scripts/agent.sh" | bash
  sleep 5
done

The same pattern works with the other templates: run the agent command, sleep, and repeat. If you use cron instead, run the one-shot command on your desired schedule.

Do not start more agents than you need. Each agent polls independently, and any agent with a matching type can claim a task.

Limit which tasks an agent receives

By default, the templates report basic capabilities such as OS, architecture, and hostname. You can add static, dynamic, and override capabilities in the agent’s working directory:

• .capabilities.json — static JSON.
• .capabilities.dynamic.ts, .dynamic.py, or .dynamic.sh — runtime detection.
• .capabilities.override.json — final overrides.

Example .capabilities.json:

{
  "tools": {
    "git": true,
    "cmake": true
  },
  "labels": ["linux-builder"]
}

Tasks use a capabilitiesSchema to require matching capabilities. See Declare capabilities for the full merge order and examples.

Troubleshooting

• Unauthenticated — check SHEETBOT_AUTH_USER / SHEETBOT_AUTH_PASS, or the API key.
• Unauthorized — the user or API key lacks the permission to perform tasks.
• The agent exits without running anything — no task matched. Check the task type and its capabilitiesSchema.
• curl: command not found or jq: command not found — install the missing tool for the Bash agent.
• uv: command not found — install uv for the Python agent.
• Deno cannot import the task — use -A or add --allow-import for the SheetBot host.

Next

• Agent runtime API — implement your own agent in another language.
• Declare capabilities — target tasks at specific machines.
• Manage API keys — run agents without storing a password.

Using API keys for agents

In this tutorial you create a dedicated user and a scoped API key, then run an agent with the key instead of a password. This is the recommended setup for unattended agents running in cron, systemd, launchd, or CI.

Why use an API key?

Passwords are meant for interactive logins. An API key:

• Can be scoped to only the permissions an agent needs.
• Can be rotated without changing the user’s password.
• Can be unique per agent or per machine.
• Is shown only once, so it is easy to keep out of source control.

API keys still belong to a user. The effective permissions are the intersection of the user’s permissions and the key’s permissions, so a key can never grant more than its owner has.

1. Create an agent user

On the machine running SheetBot:

npm run adduser

Use a descriptive username and a strong password:

username: agent-runner
password: <strong password>
permissions: performTasks,putSheetData,viewTasks

performTasks lets the agent poll, accept, complete, and fail tasks. putSheetData is only needed if your tasks write to sheets. Add createArtefacts and viewArtefacts if tasks use the S3-compatible artefact API.

You do not need createTasks for an agent. Create tasks with an admin account or a separate automation user.

2. Create a scoped API key

Generate a key for that user:

npm run addapikey

Use the same permissions as the user, or a narrower subset:

username: agent-runner
key name (optional) [default]: builder-agent
permissions (separate with commas or use * for all) [*]: performTasks,putSheetData,viewTasks

The key is printed once:

550e8400-e29b-41d4-a716-446655440000.a1b2c3d4e5f6...

Copy it somewhere safe immediately. SheetBot stores only a hash and cannot show the key again. If you lose it, create a new key and revoke the old one.

Use * to inherit the user’s permissions:

permissions (separate with commas or use * for all) [*]: *

3. Store the key on the agent machine

Create an environment file that only your user can read:

mkdir -p ~/sheetbot-agent
cd ~/sheetbot-agent

umask 077
cat > agent.env <<'EOF'
SHEETBOT_BASEURL=http://localhost:3000
SHEETBOT_AUTH_APIKEY=your.api.key
EOF

Load it in the shell that runs the agent:

set -a
. ~/sheetbot-agent/agent.env
set +a

Do not commit agent.env to git. For systemd, use EnvironmentFile=; for launchd, put the variables in the plist’s EnvironmentVariables; for cron, source the file from a wrapper script.

If you previously exported SHEETBOT_AUTH_USER or SHEETBOT_AUTH_PASS, unset them so the agent does not fall back to password authentication:

unset SHEETBOT_AUTH_USER SHEETBOT_AUTH_PASS

4. Run an agent with the key

All agent templates read SHEETBOT_AUTH_APIKEY.

Node.js

curl -fsSL "$SHEETBOT_BASEURL/scripts/agent.js" | node

Deno

deno run -A "$SHEETBOT_BASEURL/scripts/agent.ts"

Python

curl -fsSL "$SHEETBOT_BASEURL/scripts/agent.py" -o agent.py
uv run --script agent.py

Bash

curl -fsSL "$SHEETBOT_BASEURL/scripts/agent.sh" | bash

See Setting up other agents for prerequisites and scheduler examples.

5. Verify the key

Exchange the key for a JWT manually:

curl -sS -X POST "$SHEETBOT_BASEURL/login" \
  -H "Content-Type: application/json" \
  -d "{\"apiKey\":\"$SHEETBOT_AUTH_APIKEY\"}"

A successful response contains a token:

{
  "token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..."
}

Then create a task with a matching type and run the agent. The task should move to RUNNING and then COMPLETED. If the key is scoped too narrowly, the agent logs Unauthorized or Forbidden.

6. Rotate or revoke a key

To rotate, create a new key for the same user, update agent.env, restart or rerun the agent, then revoke the old key.

List keys (the secret is not stored, so only metadata is shown):

sqlite3 users.db \
  "SELECT id, name, user_id, permissions, datetime(created_at / 1000, 'unixepoch') FROM api_keys;"

Revoke a key by deleting its id:

sqlite3 users.db "DELETE FROM api_keys WHERE id = '550e8400-e29b-41d4-a716-446655440000';"

The next login with that key fails. Stop SheetBot first if SQLite reports that the database is locked.

Troubleshooting

• Login failed with API key — the key is wrong, was revoked, or belongs to a deleted user.
• Unauthorized / Forbidden — the key or its user lacks the required permission.
• Agent still asks for a password — SHEETBOT_AUTH_USER / SHEETBOT_AUTH_PASS are still set. Unset them.
• A task completes but sheet writes fail — add putSheetData to both the user and the key.
• Artefact uploads fail — add performTasks, createArtefacts, and viewArtefacts as needed.

Next

• Manage API keys — full reference for key creation, scoping, and storage.
• Configure permissions — see every available permission.
• Agent runtime API — implement a custom agent that reads SHEETBOT_AUTH_APIKEY.

Working with sheets

In this tutorial you create a task that writes rows into a sheet, then view the sheet in the web UI.

Sheets are SQLite databases under sheets/. A sheet is created the first time data is written to it, so the task does both: creates build-status and fills it with build results.

1. Create the task

Open http://localhost:3000/addtask.html, select Manual as the script source, and set Task Type to Other (specify) with the value node.

Give the task a name such as Write build status, leave the status as Awaiting, and paste this script:

const baseUrl = process.env.SHEETBOT_BASEURL;
const authorization = process.env.SHEETBOT_AUTHORIZATION_HEADER;
const sheet = "build-status";

const rows = [
  {
    key: "linux-x64",
    name: "Linux x64",
    status: 2,
    progress: 100,
    updated_at: Date.now(),
    notes: "Build succeeded",
  },
  {
    key: "windows-x64",
    name: "Windows x64",
    status: 3,
    progress: 42,
    updated_at: Date.now(),
    notes: "Compile failed at step 3",
  },
];

(async () => {
  for (const row of rows) {
    const response = await fetch(`${baseUrl}/sheets/${sheet}/data`, {
      method: "POST",
      headers: {
        "Content-Type": "application/json",
        "Authorization": authorization,
      },
      body: JSON.stringify(row),
    });

    if (!response.ok) {
      const body = await response.text();
      throw new Error(`Failed to write ${row.key}: ${response.status} ${body}`);
    }
  }

  console.log(`Wrote ${rows.length} rows to ${sheet}`);
})().catch((error) => {
  console.error(error);
  process.exit(1);
});

The agent injects SHEETBOT_BASEURL and SHEETBOT_AUTHORIZATION_HEADER, so the script does not need to log in itself. The user or API key running the agent needs the putSheetData permission; * includes it.

2. Run the agent

Create the task, then start a Node.js agent:

export SHEETBOT_BASEURL=http://localhost:3000
export SHEETBOT_AUTH_USER=your_username
export SHEETBOT_AUTH_PASS=your_password

curl -fsSL "$SHEETBOT_BASEURL/scripts/agent.js" | node

The task should complete. Its console output appears in the agent terminal.

3. View the sheet

Open http://localhost:3000/sheet.html?sheet=build-status.

You should see two rows and the columns key, name, status, progress, updated_at, and notes. The sheet was created automatically by the first POST.

You can also read it as JSON:

curl -H "Authorization: Bearer $TOKEN" \
  "$SHEETBOT_BASEURL/sheets/build-status"

4. Upsert instead of duplicating

The key column is the primary key. Posting another row with the same key updates it instead of inserting a duplicate:

{
  key: "linux-x64",
  name: "Linux x64",
  status: 2,
  progress: 100,
  updated_at: Date.now(),
  notes: "Rebuilt successfully",
}

This makes sheets useful for status dashboards: a task can write the current state on every run.

Next

The columns currently use the default text widget. Continue to Customizing your sheet to choose a widget for each column, then publish the sheet for read-only sharing. For file outputs, see Working with artefacts.

Customizing your sheet

In this tutorial you choose a widget for each column of the build-status sheet created in Working with sheets.

Sheet widgets are configured per column in the columnstructure table inside the sheet’s SQLite database. The web UI reads columnstructure_view, a read-only view that joins columnstructure with the actual columns of data_view.

1. Open the sheet database

The sheet database is a normal SQLite file:

sqlite3 sheets/build-status.db

Turn on readable output and allow the CLI to read the view. columnstructure_view uses pragma_table_info, which the sqlite3 CLI blocks by default:

.headers on
.mode column
PRAGMA trusted_schema = ON;
SELECT name, widgettype, columnorder, datatype FROM columnstructure_view;

A new sheet has one wildcard row named % with the text widget. That fallback matches every column, which is why the sheet currently renders everything as text.

2. Replace the wildcard with explicit columns

Delete the wildcard and insert one row per column. columnorder controls display order; lower numbers appear first.

BEGIN;

DELETE FROM columnstructure;

INSERT INTO columnstructure
  (name, widgettype, minwidth, maxwidth, minheight, maxheight, columnorder)
VALUES
  ('key',        'text',               120, 220,  40,  80, 0),
  ('name',       'text',               160, 320,  40,  80, 1),
  ('status',     'taskstatus',         120, 200,  40,  80, 2),
  ('progress',   'progress',           160, 260,  40,  80, 3),
  ('updated_at', 'relative-timestamp', 160, 260,  40,  80, 4),
  ('notes',      'code',               240, 520, 100, 240, 5);

COMMIT;

Verify the result:

SELECT name, widgettype, columnorder, datatype FROM columnstructure_view;

Refresh http://localhost:3000/sheet.html?sheet=build-status. The sheet now renders:

3. Change a widget later

Update the matching row in columnstructure:

UPDATE columnstructure
SET widgettype = 'text-centered'
WHERE name = 'name';

Refresh the sheet to see the change. To hide a column from the UI, delete its row; the data stays in the data table.

How the views fit together

• data is the writable table. Tasks and the API write here.
• data_view is what the UI reads. By default it is SELECT * FROM data; you can replace it with a filtered or computed view.
• columnstructure is the writable widget configuration.
• columnstructure_view is the read-only join the UI reads. It combines columnstructure with pragma_table_info('data_view'), so it only includes columns that exist in data_view.

If a task later adds a new column and it does not appear in the sheet, add a matching columnstructure row for it. If you customize data_view to add computed columns, add columnstructure rows for those columns too.

Next

• Browse all widget types in Widgets.
• Build your own with Building a custom widget.
• Share the result with Publishing a read-only sheet.
• Read the full storage layout in Sheet database.

Publishing a read-only sheet

Sheets can be shared read-only without giving viewers a SheetBot login. This is useful for build dashboards, status pages, and reports.

In this tutorial you make an existing sheet public, open it anonymously, list it, and revoke access.

1. Check the current grant

Use a user with the manageSheetPermissions permission. Get a token and inspect the sheet:

export SHEETBOT_BASEURL=http://localhost:3000
export TOKEN=$(curl -sS -X POST "$SHEETBOT_BASEURL/login" \
  -H "Content-Type: application/json" \
  -d '{"username":"your_username","password":"your_password"}' \
  | jq -r .token)

curl -H "Authorization: Bearer $TOKEN" \
  "$SHEETBOT_BASEURL/sheets/build-status/public"

The response is:

{
  "sheet": "build-status",
  "public": false
}

2. Make the sheet public

curl -X PUT \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"public":true}' \
  "$SHEETBOT_BASEURL/sheets/build-status/public"

You can also use the Admin page in the web UI, which lists sheets and their public grants.

3. Open it anonymously

Fetch the sheet without an Authorization header:

curl "$SHEETBOT_BASEURL/sheets/build-status"

Anonymous clients also see public sheets in the sheet list:

curl "$SHEETBOT_BASEURL/sheets"

Share the viewer URL:

http://localhost:3000/sheet.html?sheet=build-status

Viewers can browse, sort, and copy data, but they cannot write to the sheet or see private sheets.

4. Hide columns before publishing

Public means every row and column returned by the sheet’s data_view is readable. If the sheet contains internal columns, change data_view to expose only the columns you want:

DROP VIEW data_view;

CREATE VIEW data_view AS
SELECT key, name, status, progress, updated_at
FROM data;

Then make sure columnstructure only contains those columns. The sheet API and UI read data_view, so hidden columns are not returned.

Stop SheetBot or expect SQLite locking while changing the view. Test with an anonymous request before sharing the link.

5. Revoke access

curl -X PUT \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"public":false}' \
  "$SHEETBOT_BASEURL/sheets/build-status/public"

Anonymous requests now return 401/403 for that sheet, and it disappears from the anonymous sheet list.

Notes

• Public sheets are read-only. Writes still require login and putSheetData.
• The whole sheet is public; there is no per-row or per-cell access control.
• Public artefacts are separate. See Working with artefacts for the public bucket.
• Use Customizing your sheet to choose widgets for the public columns.

Next

• Sheet database — data, data_view, and columnstructure.
• HTTP API reference — sheet and permission endpoints.
• Configure permissions — manageSheetPermissions and putSheetData.

Working with artefacts

Artefacts are files produced by tasks: build outputs, screenshots, reports, logs, or generated HTML. They are stored per task and can also be served through an S3-compatible bucket API.

In this tutorial you upload an artefact from a task, download it, and put a file in the public bucket.

1. Upload an artefact from a task

Create a task with type node and paste this script:

(async () => {
  const authorization = process.env.SHEETBOT_AUTHORIZATION_HEADER;
  const content = "hello from a SheetBot artefact\n";

  const form = new FormData();
  form.set("file", new Blob([content], { type: "text/plain" }), "hello.txt");

  const upload = await fetch(process.env.SHEETBOT_TASK_ARTEFACTURL, {
    method: "POST",
    headers: { "Authorization": authorization },
    body: form,
  });

  if (!upload.ok) {
    throw new Error(`Upload failed: ${upload.status} ${await upload.text()}`);
  }

  const uploaded = await upload.json();

  const update = await fetch(process.env.SHEETBOT_TASK_DATAURL, {
    method: "POST",
    headers: {
      "Authorization": authorization,
      "Content-Type": "application/json",
    },
    body: JSON.stringify({
      data: {
        artefact: uploaded.directURL,
      },
    }),
  });

  if (!update.ok) {
    throw new Error(`Data update failed: ${update.status} ${await update.text()}`);
  }

  console.log("uploaded", uploaded.directURL);
})().catch((error) => {
  console.error(error);
  process.exit(1);
});

Run a Node.js agent after creating the task:

export SHEETBOT_BASEURL=http://localhost:3000
export SHEETBOT_AUTH_USER=your_username
export SHEETBOT_AUTH_PASS=your_password

curl -fsSL "$SHEETBOT_BASEURL/scripts/agent.js" | node

The task needs the performTasks permission. The upload response contains:

{
  "url": "http://localhost:3000/tasks/<task-id>/artefacts/hello.txt",
  "directURL": "http://localhost:3000/artefacts/tasks/<task-id>/hello.txt"
}

Files are stored in artefacts/tasks/<task-id>/, and the filename is added to the task’s artefacts array.

2. Download the artefact

Open the task in the web UI, or use the task download endpoint:

export TOKEN=$(curl -sS -X POST "$SHEETBOT_BASEURL/login" \
  -H "Content-Type: application/json" \
  -d "{\"username\":\"$SHEETBOT_AUTH_USER\",\"password\":\"$SHEETBOT_AUTH_PASS\"}" \
  | jq -r .token)

curl -L -H "Authorization: Bearer $TOKEN" \
  "$SHEETBOT_BASEURL/tasks/<task-id>/artefacts/hello.txt"

The endpoint redirects to the direct file URL and sets an authentication cookie. The direct URL is also usable from a browser after login.

Deleting a task artefact requires deleteTasks:

curl -X DELETE -H "Authorization: Bearer $TOKEN" \
  "$SHEETBOT_BASEURL/tasks/<task-id>/artefacts/hello.txt"

3. Use the S3-compatible bucket API

Buckets are independent of tasks and are useful for shared reports, dashboards, or files consumed by other tools.

Upload a file with JWT authentication:

printf 'public report\n' > report.txt

curl -X PUT \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: text/plain" \
  --data-binary @report.txt \
  "$SHEETBOT_BASEURL/artefacts/reports/report.txt"

List a bucket:

curl -H "Authorization: Bearer $TOKEN" \
  "$SHEETBOT_BASEURL/artefacts/reports"

Download a file:

curl -H "Authorization: Bearer $TOKEN" \
  "$SHEETBOT_BASEURL/artefacts/reports/report.txt"

The bucket API also accepts temporary AWS-compatible credentials from POST /artefacts-credentials, which lets S3 clients sign requests. JWT authentication is simpler for scripts.

4. Publish a file in the public bucket

The public bucket is served without authentication:

printf 'anyone can read this\n' > public.txt

curl -X PUT \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: text/plain" \
  --data-binary @public.txt \
  "$SHEETBOT_BASEURL/artefacts/public/public.txt"

curl "$SHEETBOT_BASEURL/artefacts/public/public.txt"

Only put files in public that are safe for anonymous access. Uploading still requires createArtefacts; reading public does not require a login.

Permissions

See Configure permissions for the full list.

Next

• Artefacts reference — endpoints, storage, and AWS-compatible credentials.
• HTTP API reference — all artefact endpoints.
• Widgets — render artefact links with the download or attachments widget.

Writing a library script

Library scripts are reusable task templates. They appear on the Library page and in addtask.ts, and can prefill the task name, data, capability requirements, comments, and transitions.

In this tutorial you add a Python library script, inspect how its annotations map to the add-task form, and run it with an agent.

1. Create the script

Create library/hello_library.py:

# <name>Hello from the library</name>
#
# <addTaskComments>
# A minimal library script. The annotations prefill the add-task form.
# </addTaskComments>
#
# <data>
# {
#   "message": "hello from the library"
# }
# </data>
#
# <capabilitiesSchema>
# {
#   "type": "object",
#   "properties": {
#     "os": { "type": "string" }
#   },
#   "required": ["os"]
# }
# </capabilitiesSchema>

import json
import os
import urllib.request

request = urllib.request.Request(
    os.environ["SHEETBOT_TASK_BASEURL"],
    headers={"Authorization": os.environ["SHEETBOT_AUTHORIZATION_HEADER"]},
)

with urllib.request.urlopen(request) as response:
    task = json.load(response)

print(task["data"].get("message", "hello"))

SheetBot discovers .ts, .js, .py, and .sh files in library/. No registration step is needed.

2. Inspect it in the Library

Open http://localhost:3000/library or refresh the page. The script appears as Hello from the library.

The annotations are parsed by the server and returned from GET /library:

• <name> — suggested task name.
• <data> — suggested JSON task data.
• <capabilitiesSchema> — JSON Schema that an agent’s capabilities must match.
• <addTaskComments> — explanatory text shown while adding the task.
• <transitions> — optional JSON array of transitions to prefill.

For JSON blocks, lines whose first non-whitespace character is # are ignored, so the JSON can live inside comments in any language.

You can inspect the parsed metadata directly:

curl -H "Authorization: Bearer $TOKEN" \
  "$SHEETBOT_BASEURL/library" | jq '.[] | select(.filename == "hello_library.py")'

3. Create a task from it

Click Add Task next to the script. The add-task form is prefilled with:

• The suggested name.
• The suggested data, {"message": "hello from the library"}.
• The capabilities schema.
• The comments.

The Python extension selects the python task type. If you use a .js file, the UI treats it as a Deno task; create a Node.js task manually with type node instead.

Submit the task, then run a Python agent:

export SHEETBOT_BASEURL=http://localhost:3000
export SHEETBOT_AUTH_USER=your_username
export SHEETBOT_AUTH_PASS=your_password

mkdir -p ~/sheetbot-agent
cd ~/sheetbot-agent
curl -fsSL "$SHEETBOT_BASEURL/scripts/agent.py" -o agent.py
uv run --script agent.py

The script reads its own task data from SHEETBOT_TASK_BASEURL and prints the message. The task moves to COMPLETED.

4. Add transitions (optional)

A library script can suggest transitions. This example re-arms a completed task after five minutes:

# <transitions>
# [
#   {
#     "statuses": ["COMPLETED"],
#     "condition": {},
#     "timing": { "every": "5m", "immediate": false },
#     "transitionTo": "AWAITING"
#   }
# ]
# </transitions>

Add this block to the script and refresh the Library page. New tasks created from the script include the transition. See Task transitions for the full transition model.

5. Use additional library paths

Keep project-specific scripts outside the repository by setting SHEETBOT_LIBRARY_SEARCH_PATH to a colon-separated list:

export SHEETBOT_LIBRARY_SEARCH_PATH="/opt/sheetbot-scripts:$HOME/my-sheetbot-scripts"

The default ./library/ directory is always searched first. See Configure library paths for details.

Next

• Widgets — render task data in sheets.
• Library script annotations — the annotation reference.
• Setting up other agents — run the script with Deno, Node.js, or Bash instead.

Building a custom widget

Widgets render sheet cells. SheetBot ships with text, code, image, progress, metric, task, and other widgets, and automatically loads any widget-*.js file from its static directories.

In this tutorial you build a small hello widget, register it, and use it for a sheet column.

1. Create the widget

Create static/widget-hello.js:

import { html, css, LitElement } from "https://cdn.jsdelivr.net/gh/lit/dist@3/all/lit-all.min.js";

export class HelloWidget extends LitElement {
  static properties = {
    data: { type: String },
    rowkey: { type: String },
    column: { type: String },
  };

  static styles = css`
    span {
      display: inline-block;
      padding: 4px 8px;
      border-radius: 4px;
      background: #e8f4ff;
      color: #0b4f8a;
      font-weight: 600;
    }
  `;

  constructor() {
    super();
    this.data = "";
    this.rowkey = "";
    this.column = "";
  }

  render() {
    return html`<span>Hello, ${this.data || "world"}!</span>`;
  }

  getCopyText() {
    return `Hello, ${this.data || "world"}!`;
  }

  getCopyHTML() {
    return `<span>${this.getCopyText()}</span>`;
  }
}

customElements.define("widget-hello", HelloWidget);

Rules:

• The filename must start with widget- and end with .js.
• The custom element name must be widget-<name>, here widget-hello.
• data receives the cell value. rowkey and column are also set.
• getCopyText() is used for copy operations. getCopyHTML() and getContextMenuDefinition() are optional.
• If you implement delete(), the grid can call it for destructive context-menu actions.

2. Make SheetBot discover it

SheetBot exposes all discovered widgets at /widgets.json and imports them when a sheet page loads:

curl "$SHEETBOT_BASEURL/widgets.json"

The list is cached for five minutes by default. For development, disable the cache and restart SheetBot:

export SHEETBOT_WIDGET_CACHE_TTL=0
npm start

Alternatively, keep the default and wait for the cache to expire. A hard refresh in the browser also helps after changing a widget module.

3. Use the widget for a column

Use the build-status sheet from Customizing your sheet, or any sheet with a text column. Set the column’s widgettype to hello:

sqlite3 sheets/build-status.db \
  "UPDATE columnstructure SET widgettype = 'hello' WHERE name = 'name';"

Refresh http://localhost:3000/sheet.html?sheet=build-status. The name column now renders as Hello, Linux x64!, and the value is still copied as plain text.

If the column does not exist in columnstructure, add it first:

INSERT INTO columnstructure
  (name, widgettype, columnorder)
VALUES
  ('name', 'hello', 1);

4. Debug a widget

• The widget does not appear — check that the file is in static/ (or a directory in SHEETBOT_STATIC_SEARCH_PATHS), starts with widget-, and ends with .js.
• /widgets.json does not list it — the cache may still hold the old list. Set SHEETBOT_WIDGET_CACHE_TTL=0, restart, or wait.
• The cell is empty — open the browser console. A failed dynamic import or a mismatched customElements.define name shows up there.
• The column is missing — columnstructure has no row for it, or the column does not exist in data_view.
• Changes do not show — hard refresh. ES modules are cached by the browser.

Next

• Widgets reference — built-in widget types and the widget contract.
• Customizing your sheet — per-column widget configuration.
• Static search paths — load widgets from outside static/.

How-to guides

Task-oriented guides for configuring and extending SheetBot. Each page assumes you already have a running instance; start with the getting started tutorial if you do not.

Access and operations

• Configure permissions — assign roles and control what users can do.
• Manage API keys — create and use API keys for agents and integrations.
• Debug a failed task — find out why a task failed or never started.
• Deploy SheetBot — run as a service, enable TLS, back up, restore, and upgrade.
• Troubleshoot — known issues and workarounds.

Configuration

• Configure task transitions — auto-delete, retry, re-arm, and time out tasks.
• Write init scripts — run code on every server startup.
• Configure library paths — load task scripts from additional directories.
• Configure script paths — serve agent scripts from additional directories.
• Configure static paths — serve widgets and assets from additional directories.

Extension

• Write custom middleware — add Express middleware around routes.
• Handle events — react to task and agent events.
• Declare capabilities — report agent capabilities for task matching.

Permissions System

SheetBot uses a role-based access control (RBAC) system to manage user permissions. Permissions control what actions a user can perform on various resources within the system.

How Permissions Work

• Permissions are assigned to users during user creation (via adduser.ts).
• Permissions are stored as a comma-separated string in the user database.
• When a user logs in, their permissions are included in the JWT token as an array.
• API endpoints use middleware to check if the user has the required permission(s).
• A user with the * permission has access to all features.

Available Permissions

The following permissions are defined in the system:

Task Management

• viewTasks: Allows viewing tasks and their details.
• createTasks: Allows creating new tasks.
• updateTasks: Allows updating task properties (e.g., status, configuration).
• deleteTasks: Allows deleting tasks.
• performTasks: Allows accepting, completing, failing, or updating task data, and uploading/downloading task artefacts.

Sheets

• putSheetData: Allows inserting, updating, and deleting data in sheets.

Artefacts

• viewArtefacts: Allows listing and viewing artefacts.
• createArtefacts: Allows uploading and creating artefacts.
• deleteArtefacts: Allows deleting artefacts.

Assigning Permissions

Permissions are assigned when creating a new user using the adduser.ts script:

npm run adduser
# or: node --experimental-sqlite --experimental-strip-types adduser.ts

You will be prompted to enter:

• Username
• Password
• Permissions (comma-separated list, or * for all permissions)

Example:

permissions (separate with commas or use * for all): createTasks,viewTasks,performTasks

Permission Checking

The system uses middleware functions:

• requiresLogin: Verifies the user is authenticated via JWT token.
• requiresPermission(permission): Checks if the user has the specified permission or the wildcard "*".

If a user lacks the required permission, the API returns a 403 Forbidden response with the message “Forbidden - insufficient permissions”.

Database Storage

User permissions are stored in the users.db SQLite database in the permissions column as a TEXT field containing a comma-separated list of permissions.

API Keys

SheetBot supports API key authentication as an alternative to username/password authentication. API keys are long-lived credentials that can be scoped with specific permissions.

Overview

API keys provide a secure way to authenticate agents and scripts without storing passwords. They can be restricted to specific permissions, enabling the creation of “downscoped” keys for read-only access or other limited operations.

For a step-by-step setup, see Using API keys for agents.

Creating API Keys

Use the addapikey.ts script to generate API keys for existing users:

npm run addapikey
# or: node --experimental-sqlite --experimental-strip-types addapikey.ts

The script will prompt for:

• Username: The user who will own this API key
• Key name: A descriptive name for the key (optional, defaults to “default”)
• Permissions: Comma-separated list of permissions or * for all permissions

Permissions

When creating an API key, you can specify permissions to restrict what the key can do:

• * - Inherit all permissions from the user (default)
• Specific permissions - Comma-separated list like viewTasks,performTasks
• Empty/subset - Any subset of the user’s permissions

Important: API keys can never have more permissions than the user who owns them. If you request permissions the user doesn’t have, only the intersection of requested and user permissions will be granted.

Example

$ npm run addapikey
username: alice
key name (optional) [default]: readonly-apikey
permissions (separate with commas or use * for all) [*]: viewTasks

Generating key...

API Key generated successfully:
----------------------------------------
550e8400-e29b-41d4-a716-446655440000.a1b2c3d4e5f6g7h8i9j0k1l2m3n4o5p6q7r8s9t0u1v2w3x4y5z6
----------------------------------------
Keep this key safe! You won't be able to see it again.

Using API Keys

Environment Variable

Set the SHEETBOT_AUTH_APIKEY environment variable instead of SHEETBOT_AUTH_USER and SHEETBOT_AUTH_PASS:

export SHEETBOT_AUTH_APIKEY=your.api.key
export SHEETBOT_BASEURL=http://localhost:3000
deno run --allow-net --allow-env --allow-read ${SHEETBOT_BASEURL}/scripts/agent.ts

Login Endpoint

API keys can be exchanged for JWT tokens via the /login endpoint:

curl -X POST http://localhost:3000/login \
  -H "Content-Type: application/json" \
  -d '{"apiKey": "your.api.key"}'

This returns a JWT token with the effective permissions (intersection of user permissions and key permissions):

{
  "token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..."
}

Permission Scoping

API keys support “downscoping” - restricting permissions below what the user has:

Example Scenarios

1. Full access key (inherits all user permissions):

   Permissions: *
   

2. Read-only key (limited to viewing):

   Permissions: viewTasks,viewArtefacts
   

3. Agent-only key (for task execution):

   Permissions: performTasks,createArtefacts,viewArtefacts
   

Effective Permissions Calculation

When an API key is used to authenticate, the effective permissions are calculated as:

• If user has *: effective = key permissions
• If key has *: effective = user permissions
• Otherwise: effective = intersection(user permissions, key permissions)

This ensures API keys can never escalate privileges beyond what the user has.

Supported Agents

All agent templates support API key authentication:

Deno/TypeScript Agent

export SHEETBOT_AUTH_APIKEY=your.api.key
deno run --reload -A "$SHEETBOT_BASEURL/scripts/agent.ts"

Python Agent

export SHEETBOT_AUTH_APIKEY=your.api.key
curl -fsSL "$SHEETBOT_BASEURL/scripts/agent.py" -o agent.py
uv run --script agent.py

Bash Agent

export SHEETBOT_AUTH_APIKEY=your.api.key
curl -fsSL "$SHEETBOT_BASEURL/scripts/agent.sh" | bash

Distributed Runtime

The distributed runtime also supports API keys via environment variables:

export SHEETBOT_DISPATCH_AUTH_APIKEY=your.api.key
# Or falls back to:
export SHEETBOT_AUTH_APIKEY=your.api.key

Security Best Practices

1. Store securely: Treat API keys like passwords. Never commit them to version control.
2. Use environment variables: Load keys from environment variables, not hardcoded in scripts.
3. Scope appropriately: Create keys with minimal required permissions.
4. Rotate regularly: Generate new keys periodically and revoke old ones.
5. One key per use case: Create separate keys for different agents or purposes.

Database Storage

API keys are stored in the api_keys table in users.db:

CREATE TABLE api_keys (
    id TEXT PRIMARY KEY,           -- UUID prefix of the key
    user_id TEXT NOT NULL,          -- Associated user
    key_hash TEXT NOT NULL,         -- Bcrypt hash of the secret
    name TEXT,                      -- Descriptive name
    created_at INTEGER,             -- Creation timestamp
    permissions TEXT,               -- Comma-separated permissions or *
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

Only the hash of the secret portion is stored. The full key is shown only once when created.

API Key Format

API keys have the format: {id}.{secret}

• id: UUID for fast database lookup
• secret: Long random string for authentication

Example: 550e8400-e29b-41d4-a716-446655440000.a1b2c3d4e5f6g7h8i9j0k1l2m3n4o5p6q7r8s9t0u1v2w3x4y5z6

Troubleshooting

Login fails with API key

• Verify the key is correct (copy-paste carefully)
• Check that the user still exists
• Ensure the key hasn’t been revoked (deleted from database)

Permission denied errors

• Check the effective permissions of the API key
• Verify the user has the required permissions
• Remember: API keys can’t exceed user permissions

Key not working after user permission change

API keys inherit permissions dynamically at login time. If a user’s permissions are reduced, their API keys will also have reduced effective permissions on next use.

Configure task transitions

Transitions change a task’s status automatically. They can delete finished tasks, retry failures, re-arm recurring tasks, or enforce timeouts.

Transitions are stored on the task as an array. Each transition has:

{
  "statuses": ["COMPLETED"],
  "condition": {},
  "timing": { "immediate": true },
  "transitionTo": "DELETED",
  "dataMutations": {}
}

Immediate transitions use first-match order. Scheduled transitions re-check the condition when they fire and are re-scheduled while the task remains in a matching status.

Add transitions to a task

Use the Transitions field in the add-task form, a library script’s <transitions> annotation, or the API:

curl -X POST \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "one-shot build",
    "script": "console.log(\"done\")",
    "type": "node",
    "transitions": [
      {
        "statuses": ["COMPLETED"],
        "condition": {},
        "timing": { "immediate": true },
        "transitionTo": "DELETED"
      }
    ]
  }' \
  "$SHEETBOT_BASEURL/tasks"

The caller needs createTasks.

Auto-delete a successful task

[
  {
    "statuses": ["COMPLETED"],
    "condition": {},
    "timing": { "immediate": true },
    "transitionTo": "DELETED"
  }
]

DELETED removes the task and removes it from other tasks’ dependsOn lists. Use this for ephemeral CI jobs that should not accumulate.

Retry a failed task

[
  {
    "statuses": ["FAILED"],
    "condition": {},
    "timing": { "every": "1m", "immediate": false },
    "transitionTo": "AWAITING"
  }
]

The task returns to the queue after one minute. Agents can pick it up again.

To retry only once, create the task with "data": {"retryCount": 0} and use a condition that only matches the initial value. dataMutations then marks the task as retried:

[
  {
    "statuses": ["FAILED"],
    "condition": {
      "type": "object",
      "properties": {
        "data": {
          "type": "object",
          "properties": {
            "retryCount": { "type": "number", "maximum": 0 }
          },
          "required": ["retryCount"]
        }
      }
    },
    "timing": { "every": "1m" },
    "transitionTo": "AWAITING",
    "dataMutations": { "retryCount": 1 }
  }
]

The first failure matches and sets retryCount to 1. If the retried task fails again, the condition no longer matches and the task stays FAILED. dataMutations merges values; it does not increment numbers. Use task data or a script if you need a true counter.

Re-arm a recurring task

[
  {
    "statuses": ["COMPLETED"],
    "condition": {},
    "timing": { "every": "5m", "immediate": false },
    "transitionTo": "AWAITING"
  }
]

This is the pattern used by the library’s counter example. The task becomes AWAITING again five minutes after completion, and any matching agent can run it.

Time out a waiting task

[
  {
    "statuses": ["AWAITING"],
    "condition": {},
    "timing": { "every": "1h", "immediate": false },
    "transitionTo": "FAILED"
  }
]

After an hour in AWAITING, the task becomes FAILED. While it remains AWAITING, the schedule is refreshed; once the status changes, the stale schedule is removed on the next check.

Conditions

condition is a JSON Schema applied to the full task object, including status, data, artefacts, dependsOn, and transitions. Examples:

Require a successful build result:

{
  "type": "object",
  "properties": {
    "data": {
      "type": "object",
      "properties": {
        "exitCode": { "const": 0 }
      },
      "required": ["exitCode"]
    }
  }
}

Require an artefact:

{
  "type": "object",
  "properties": {
    "artefacts": {
      "type": "array",
      "contains": { "const": "report.html" }
    }
  }
}

An empty object {} always matches. An invalid condition fails validation and the transition does not run; check the server log and /transitiontracker when debugging.

Inspect transitions

curl -H "Authorization: Bearer $TOKEN" \
  "$SHEETBOT_BASEURL/transitiontracker?minutes=60" | jq

Scheduled transitions are stored in transitions_schedule in tasks.db. A background worker checks them. Deleting a task also removes its schedules.

Next

• Protocol — task state diagram and failure flow.
• Writing a library script — suggest transitions from a script.
• Debug a failed task — diagnose transitions that did not fire.

Initialization System

SheetBot includes an initialization system that runs TypeScript scripts on every startup. This system allows you to perform setup tasks, initialize databases, generate secrets, or load configurations before the main server starts.

How It Works

• Place .ts files in the ./init/ directory
• Additional directories can be specified via SHEETBOT_INIT_SEARCH_PATHS environment variable (colon-separated)
• Files are executed in lexicographic order based on their filenames (e.g., 00_init.ts, 01_setup.ts)
• Each script can export a default function (sync or async) which will be called during startup
• If the default export is not a function, the script is skipped
• Scripts run before the main server components are initialized

Additional Init Directories

Set the SHEETBOT_INIT_SEARCH_PATHS environment variable to search additional directories:

export SHEETBOT_INIT_SEARCH_PATHS="/path/to/custom/init:/another/path/init"

Directories are processed in order:

1. ./init/ (always searched first)
2. Additional directories from SHEETBOT_INIT_SEARCH_PATHS in the order specified

Within each directory, scripts are executed in alphabetical order.

Creating Init Scripts

Init scripts are simple TypeScript files that export a default function. The function can be synchronous or asynchronous.

Basic Structure

export default async function() {
    // Your initialization code here
    console.log("Running initialization...");
    // async operations are supported
}

Example: Database Initialization

import { initDatabaseTables } from "../lib/db.ts";

export default function() {
    console.log("Initializing database tables...");
    initDatabaseTables();
}

Example: Secret Generation

import { existsSync } from "https://deno.land/std@0.220.1/fs/mod.ts";

export default function() {
    const secretPath = "./secret.txt";

    if (existsSync(secretPath)) {
        console.log("Secret file already exists, skipping generation.");
        return;
    }

    console.log("Generating secret key...");

    // Generate a random ASCII secret (64 characters, printable ASCII)
    const charset = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
    const secretArray = new Uint8Array(64);
    crypto.getRandomValues(secretArray);
    let secret = '';
    for (let i = 0; i < 64; i++) {
        secret += charset.charAt(secretArray[i] % charset.length);
    }

    Deno.writeTextFileSync(secretPath, secret);
    console.log("Secret key generated and saved to secret.txt");
}

Example: Migration Script

export default async function() {
    console.log("Running database migration...");

    // Migration logic here
    // For example, updating schema, transforming data, etc.

    console.log("Migration completed.");
}

Best Practices

• Use descriptive filenames with numeric prefixes to control execution order
• Keep scripts focused on a single responsibility
• Handle errors appropriately - failing init scripts will stop server startup
• Scripts can access the filesystem and perform I/O operations
• Avoid long-running operations that delay server startup

Execution Order

Scripts are sorted and executed in lexicographic order:

• 00_init.ts runs first
• 01_setup.ts runs second
• 10_migrate.ts runs after the above

Error Handling

If an init script throws an error, the server startup will fail. Ensure your scripts are robust and handle potential errors gracefully.

Permissions

Init scripts run with the same permissions as the main server process. They can read and write files, access the network, and perform other operations allowed by the Node.js process running main.ts. The Deno global is available via the compatibility shim for backwards compatibility.

Library Search Paths

SheetBot supports additional library search paths via the SHEETBOT_LIBRARY_SEARCH_PATH environment variable.

Usage

Set the environment variable to a colon-separated list of directories:

export SHEETBOT_LIBRARY_SEARCH_PATH="/path/to/custom/scripts:/another/path/scripts"

Behavior

• The default ./library/ directory is always searched first
• Additional paths from SHEETBOT_LIBRARY_SEARCH_PATH are searched in order
• Paths that don’t exist or aren’t accessible are silently skipped
• Scripts from all paths appear in the library UI and addtask.ts script picker
• Script content is served at /library/<filename> for all search paths, so scripts can be selected and added as tasks from the library page

Example

# Add custom library paths
export SHEETBOT_LIBRARY_SEARCH_PATH="/home/user/my-scripts:/opt/shared-scripts"

# Start SheetBot - it will now find scripts in all three locations:
# - ./library/
# - /home/user/my-scripts/
# - /opt/shared-scripts/
npm start

This allows you to:

• Keep custom scripts separate from the SheetBot repository
• Share script libraries across multiple SheetBot instances
• Organize scripts by project or team

Scripts Search Paths

SheetBot supports serving additional agent-facing scripts from external directories via the SHEETBOT_SCRIPTS_SEARCH_PATHS environment variable. Files in these directories are served at /scripts, alongside the built-in files from ./scripts/.

Usage

Set the environment variable to a colon-separated list of directories:

export SHEETBOT_SCRIPTS_SEARCH_PATHS="/path/to/my-scripts:/another/path/scripts"

Behavior

• The default ./scripts/ directory is always served first at /scripts
• Additional paths from SHEETBOT_SCRIPTS_SEARCH_PATHS are served in order at the same /scripts prefix
• Paths that don’t exist are skipped with a warning
• Files in later paths can shadow files from earlier paths (including ./scripts/)

Example

export SHEETBOT_SCRIPTS_SEARCH_PATHS="/home/user/my-sheetbot-customizations/scripts"

npm start
# A file at /home/user/my-sheetbot-customizations/scripts/lib/myutil.ts
# is accessible at http://localhost:3000/scripts/lib/myutil.ts
# and can be imported by agents as:
#   import { foo } from "${SHEETBOT_BASEURL}/scripts/lib/myutil.ts";

Use Cases

• Custom agent utility modules: Share helper TypeScript modules that your library scripts import by URL, without putting them in the SheetBot repo
• Custom agent scripts: Serve scripts like verification tasks or one-off automation scripts from an external directory
• Overriding built-in scripts: Files in an additional path shadow ./scripts/ files with the same name

Notes

• Agent scripts import these files by their full URL (e.g. ${SHEETBOT_BASEURL}/scripts/lib/myutil.ts), so they work the same regardless of which directory they were served from
• The /scripts route for specific task scripts (/scripts/:id) is handled by a dedicated route and is not affected by this setting
• When SheetBot is updated, your external scripts files are unaffected
• Combine with SHEETBOT_LIBRARY_SEARCH_PATH to also add library scripts from the same external repo

Static Search Paths

SheetBot supports serving additional static files from external directories via the SHEETBOT_STATIC_SEARCH_PATHS environment variable. This is the primary way to add custom widgets and other static assets without modifying the SheetBot repository.

Usage

Set the environment variable to a colon-separated list of directories:

export SHEETBOT_STATIC_SEARCH_PATHS="/path/to/my-widgets:/another/path/static"

Behavior

• The default ./static/ directory is always served first
• Additional paths from SHEETBOT_STATIC_SEARCH_PATHS are served in order
• Paths that don’t exist are skipped with a warning
• Files in later paths can shadow files from earlier paths (including ./static/)
• All paths are served at the root URL, exactly like ./static/

Example

export SHEETBOT_STATIC_SEARCH_PATHS="/home/user/my-sheetbot-customizations/static"

npm start
# SheetBot now serves files from both ./static/ and the external path.
# A file at /home/user/my-sheetbot-customizations/static/widget-foo.js
# is accessible at http://localhost:3000/widget-foo.js

Use Cases

• Custom widgets: Place widget-*.js files in an external directory and reference them in sheet column structures without putting them in the SheetBot repo
• Custom HTML pages: Add extra HTML files served at the root
• Overriding built-in assets: Files in an additional path shadow ./static/ files with the same name (useful for patching a widget without forking)

Notes

• Widgets loaded this way are registered by the browser in the same custom element registry as built-in widgets, so they work identically
• When SheetBot is updated, your external static files are unaffected
• Combine with SHEETBOT_INIT_SEARCH_PATHS to also run initialization code (e.g. creating sheets) from the same external repo

Custom Middleware

SheetBot supports loading custom Express middleware from external directories via the SHEETBOT_MIDDLEWARE_SEARCH_PATHS environment variable.

Usage

Set the environment variable to a colon-separated list of directories:

export SHEETBOT_MIDDLEWARE_SEARCH_PATHS="/path/to/middleware:/another/path"

SheetBot will search these directories for .ts files and load any that export setupMiddleware and/or setupPostRouteMiddleware functions.

Middleware Types

Pre-Route Middleware

Runs before route handlers. Use for:

• Authentication/authorization
• Request logging
• CORS configuration
• Rate limiting
• Request validation
• Body parsing modifications
• Custom headers

Export a setupMiddleware(app) function:

export function setupMiddleware(app: any) {
    // Add middleware that runs before routes
    app.use((req: any, res: any, next: any) => {
        console.log("Custom pre-route middleware");
        next();
    });
}

Post-Route Middleware

Runs after route handlers. Use for:

• 404 handlers
• Error handlers
• Fallback responses

Export a setupPostRouteMiddleware(app) function:

export function setupPostRouteMiddleware(app: any) {
    // Add middleware that runs after routes
    app.use((req: any, res: any, next: any) => {
        res.status(404).json({ error: "Not found" });
    });
}

Both Types

A single file can export both functions:

export function setupMiddleware(app: any) {
    // Pre-route middleware
    app.use((req: any, res: any, next: any) => {
        req.customData = "something";
        next();
    });
}

export function setupPostRouteMiddleware(app: any) {
    // Post-route middleware
    app.use((req: any, res: any) => {
        res.status(404).json({ error: "Route not found" });
    });
}

Examples

CORS Middleware

export function setupMiddleware(app: any) {
    app.use((req: any, res: any, next: any) => {
        res.header("Access-Control-Allow-Origin", "*");
        res.header("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE, PATCH");
        res.header("Access-Control-Allow-Headers", "Content-Type, Authorization");
        
        if (req.method === "OPTIONS") {
            return res.sendStatus(200);
        }
        
        next();
    });
}

Rate Limiting

const rateLimits = new Map<string, { count: number; resetAt: number }>();

export function setupMiddleware(app: any) {
    app.use((req: any, res: any, next: any) => {
        const ip = req.ip || "unknown";
        const now = Date.now();
        const limit = rateLimits.get(ip);
        
        if (!limit || now > limit.resetAt) {
            rateLimits.set(ip, { count: 1, resetAt: now + 60000 }); // 1 minute window
            return next();
        }
        
        if (limit.count >= 100) {
            return res.status(429).json({ error: "Rate limit exceeded" });
        }
        
        limit.count++;
        next();
    });
}

Custom Authentication

export function setupMiddleware(app: any) {
    app.use((req: any, res: any, next: any) => {
        const apiKey = req.headers["x-api-key"];
        
        if (req.path.startsWith("/api/") && !apiKey) {
            return res.status(401).json({ error: "API key required" });
        }
        
        // Validate API key
        if (apiKey && !isValidApiKey(apiKey)) {
            return res.status(403).json({ error: "Invalid API key" });
        }
        
        next();
    });
}

function isValidApiKey(key: string): boolean {
    // Your validation logic
    return key === Deno.env.get("VALID_API_KEY");
}

Custom 404 Handler

export function setupPostRouteMiddleware(app: any) {
    app.use((req: any, res: any) => {
        res.status(404).json({
            error: "Not Found",
            path: req.path,
            method: req.method,
            timestamp: new Date().toISOString()
        });
    });
}

Error Handler

export function setupPostRouteMiddleware(app: any) {
    app.use((err: any, req: any, res: any, next: any) => {
        console.error("Error:", err);
        res.status(err.status || 500).json({
            error: err.message || "Internal Server Error",
            timestamp: new Date().toISOString()
        });
    });
}

Loading Order

1. SheetBot’s built-in middleware (logging, body parsing, static files)
2. Custom pre-route middleware from SHEETBOT_MIDDLEWARE_SEARCH_PATHS (alphabetically by filename)
3. SheetBot’s route handlers
4. Custom post-route middleware from SHEETBOT_MIDDLEWARE_SEARCH_PATHS (alphabetically by filename)

Within each directory, middleware files are loaded in alphabetical order.

Error Handling

• If a middleware directory doesn’t exist or isn’t accessible, it’s skipped with a warning
• If a middleware file fails to load or execute, an error is logged but other middleware continues loading
• The server will start even if all middleware fails to load

Access to SheetBot Components

Middleware has full access to the Express app instance. To access SheetBot-specific components (database, event emitters, etc.), you can:

1. Import them directly from SheetBot’s modules
2. Store references in app.locals from an init script
3. Use environment variables for configuration

Example using init script:

// In an init script
import { openDatabase } from "./lib/db.ts";

export default function() {
    // Store DB reference for middleware to use
    globalThis.sheetbotDb = openDatabase();
}

// In middleware
export function setupMiddleware(app: any) {
    const db = (globalThis as any).sheetbotDb;
    
    app.use((req: any, res: any, next: any) => {
        // Use db here
        next();
    });
}

Event Handlers

SheetBot supports loading custom event handlers from external directories via the SHEETBOT_EVENTHANDLER_SEARCH_PATHS environment variable.

Usage

Set the environment variable to a colon-separated list of directories:

export SHEETBOT_EVENTHANDLER_SEARCH_PATHS="/path/to/handlers:/another/path"

SheetBot will search these directories for .ts files and load any that export a setupEventHandlers function.

Event Handler Structure

Create a TypeScript file that exports a setupEventHandlers function:

// my-handler.ts
import { TaskEvent } from "./lib/task-events.ts";
import { AgentEvent } from "./lib/agent-events.ts";

export function setupEventHandlers(emitters: {
    taskEventEmitter: any;
    agentEventEmitter: any;
    taskTracker: any;
    agentTracker: any;
    transitionTracker: any;
}) {
    const { taskEventEmitter, agentEventEmitter } = emitters;

    // Listen for task events
    taskEventEmitter.on(TaskEvent.ADDED, (data: any) => {
        console.log(`Custom handler: Task ${data.taskId} added`);
    });

    taskEventEmitter.on(TaskEvent.STATUS_CHANGED, (data: any) => {
        console.log(`Custom handler: Task ${data.taskId} status changed`);
    });

    // Listen for agent events
    agentEventEmitter.on(AgentEvent.CONNECTED, (data: any) => {
        console.log(`Custom handler: Agent ${data.agentId} connected`);
    });
}

Available Emitters

The setupEventHandlers function receives an object with the following properties:

• taskEventEmitter - TaskEventEmitter instance for task events
• agentEventEmitter - AgentEventEmitter instance for agent events
• taskTracker - TaskTracker instance for task metrics
• agentTracker - AgentTracker instance for agent metrics
• transitionTracker - TransitionTracker instance for transition metrics

Example Use Cases

Notification System

export function setupEventHandlers({ taskEventEmitter }: any) {
    taskEventEmitter.on(TaskEvent.FAILED, async (data: any) => {
        await sendSlackNotification(`Task ${data.taskId} failed!`);
    });
}

Metrics Collection

export function setupEventHandlers({ taskEventEmitter }: any) {
    taskEventEmitter.on(TaskEvent.COMPLETED, (data: any) => {
        // Send metrics to monitoring system
        sendMetric("task.completed", 1, { taskId: data.taskId });
    });
}

Custom Logging

export function setupEventHandlers({ taskEventEmitter, agentEventEmitter }: any) {
    taskEventEmitter.on(TaskEvent.CHANGED, (data: any) => {
        logToFile(`Task ${data.taskId} changed: ${JSON.stringify(data.changes)}`);
    });

    agentEventEmitter.on(AgentEvent.CONNECTED, (data: any) => {
        logToFile(`Agent ${data.agentId} connected from ${data.ip}`);
    });
}

Loading Order

Event handlers are loaded:

1. After the database and event emitters are initialized
2. Before the Express app starts
3. In alphabetical order by filename within each directory
4. Directories are processed in the order specified in SHEETBOT_EVENTHANDLER_SEARCH_PATHS

Error Handling

• If a directory doesn’t exist or isn’t accessible, it’s skipped with a warning
• If a handler file fails to load or execute, an error is logged but other handlers continue loading
• The server will start even if all event handlers fail to load

Capabilities System

Overview

The SheetBot capabilities system allows agents to report their capabilities to the server through a flexible three-tier configuration. This enables intelligent task routing where tasks are matched to agents with the appropriate tools, resources, and characteristics. The system consists of static, dynamic, and override capabilities that are merged in a specific order.

Architecture

The capabilities system works through a three-tier configuration that allows for flexible capability definition:

1. Static Capabilities (.capabilities.json)

Manually defined capabilities that are explicitly set by the user. These include:

• Custom tool configurations
• Environment-specific settings
• User-defined packages or features
• Static system properties

2. Dynamic Capabilities (.capabilities.dynamic.{extension})

Runtime-detected capabilities that are automatically discovered by executing detection scripts. These include:

• Installed software versions (git, clang, cmake, node, etc.)
• Operating system details and versions
• System resources (memory, load averages)
• Hardware information
• Available tools and utilities

3. Override Capabilities (.capabilities.override.json)

Final overrides that can modify or add to any capabilities from the previous tiers. These are useful for:

• Forcing specific capability values
• Adding computed or derived capabilities
• Environment-specific overrides
• Testing and debugging

File Structure

.agent-directory/
├── .capabilities.json                 # Static capabilities
├── .capabilities.dynamic.ts/.py/.sh  # Dynamic detection script
└── .capabilities.override.json       # Override capabilities

Capability Loading and Merging

Capabilities are loaded and merged in the following order:

1. Base capabilities - OS and architecture from the runtime environment
2. Static capabilities - Loaded from .capabilities.json (if exists)
3. Dynamic capabilities - Generated by .capabilities.dynamic.{extension} script (if exists)
4. Override capabilities - Applied from .capabilities.override.json (if exists)

Each layer can add new capabilities or override existing ones. The dynamic capabilities receive the static capabilities as input, allowing them to build upon or modify existing definitions.

Static Capabilities Example

{
  "packages": ["typescript", "node"],
  "custom_tools": ["my-custom-linter"],
  "environment": "production",
  "max_concurrent_tasks": 4
}

Override Capabilities Example

{
  "hostname": "custom-agent-name",
  "memory": {
    "total": 16384,
    "unit": "MB"
  },
  "debug_mode": true
}

Dynamic Capabilities Detection

The dynamic capabilities library (scripts/lib/capabilities.ts) automatically detects:

Software Versions

• Git - Version detection via git --version
• Clang - Version detection via clang --version
• CMake - Version detection via cmake --version
• Node.js - Version detection via node --version
• UV - Version detection via uv --version
• SCons - Version detection via scons --version
• VirtualBox - Version detection via vboxmanage --version
• Deno - Version from Deno.version.deno

System Information

• OS Details - OS type, release version with major/minor/patch parsing
• Memory - Total, free, and available memory in MB
• Load Average - 1min, 5min, and 15min load averages
• Hostname - System hostname
• Build Info - Deno build target, arch, etc.

Linux-Specific Information

• Distribution Details - Name, version, codename from /etc/os-release
• Pretty Name - Human-readable OS description

Usage by Agent Runners

All agent runners follow the same capability loading pattern: static → dynamic → override.

TypeScript Agent Runner (scripts/agent.template.ts)

// Load static capabilities
let localCapabilities = {};
const capabilitiesJSONPath = path.resolve("./.capabilities.json");
if (fs.existsSync(capabilitiesJSONPath)) {
  const capabilitiesText = Deno.readTextFileSync(capabilitiesJSONPath);
  localCapabilities = Object.assign({}, localCapabilities, JSON.parse(capabilitiesText));
}

// Load dynamic capabilities
const capabilitiesPath = path.resolve("./.capabilities.dynamic.ts");
if (fs.existsSync(capabilitiesPath)) {
  const { getCapabilities } = await import(path.toFileUrl(capabilitiesPath).toString());
  const dynamicCapabilities = await getCapabilities(localCapabilities);
  localCapabilities = Object.assign({}, localCapabilities, dynamicCapabilities);
}

// Apply overrides
const capabilitiesOverrideJSONPath = path.resolve("./.capabilities.override.json");
if (fs.existsSync(capabilitiesOverrideJSONPath)) {
  const capabilitiesOverrideText = Deno.readTextFileSync(capabilitiesOverrideJSONPath);
  localCapabilities = Object.assign({}, localCapabilities, JSON.parse(capabilitiesOverrideText));
}

Python Agent Runner (scripts/agent.template.py)

# Load static capabilities
local_capabilities = {}
if os.path.exists("./.capabilities.json"):
    with open("./.capabilities.json") as f:
        local_capabilities.update(json.load(f))

# Load dynamic capabilities
if os.path.exists("./.capabilities.dynamic.py"):
    spec = importlib.util.spec_from_file_location(
        "capabilities", "./.capabilities.dynamic.py"
    )
    if spec and spec.loader:
        module = importlib.util.module_from_spec(spec)
        spec.loader.exec_module(module)
        dynamic_capabilities = module.getCapabilities(local_capabilities)
        local_capabilities.update(dynamic_capabilities)

# Apply overrides
if os.path.exists("./.capabilities.override.json"):
    with open("./.capabilities.override.json") as f:
        local_capabilities.update(json.load(f))

Node.js Agent Runner (scripts/agent.template.js)

const localCapabilities = {};
if (fs.existsSync("./.capabilities.json")) {
  Object.assign(localCapabilities, JSON.parse(fs.readFileSync("./.capabilities.json", "utf8")));
}
if (fs.existsSync("./.capabilities.dynamic.mjs")) {
  const mod = await import(pathToFileURL("./.capabilities.dynamic.mjs").href);
  if (mod.getCapabilities) Object.assign(localCapabilities, await mod.getCapabilities(localCapabilities));
}
if (fs.existsSync("./.capabilities.override.json")) {
  Object.assign(localCapabilities, JSON.parse(fs.readFileSync("./.capabilities.override.json", "utf8")));
}
const capabilities = { os: os.platform(), arch: os.arch(), node: process.version, ...localCapabilities };

Shell Agent Runner (scripts/agent.template.sh)

# Load static capabilities
local_capabilities="{}"
if [ -f "./.capabilities.json" ]; then
    local_capabilities=$(jq -s '.[0] * .[1]' <(echo "$local_capabilities") ./.capabilities.json)
fi

# Load dynamic capabilities
if [ -f "./.capabilities.dynamic.sh" ]; then
    source "./.capabilities.dynamic.sh"
    if command -v getCapabilities >/dev/null 2>&1; then
        dynamic_caps=$(getCapabilities "$local_capabilities")
        local_capabilities=$(jq -s '.[0] * .[1]' <(echo "$local_capabilities") <(echo "$dynamic_caps"))
    fi
fi

# Apply overrides
if [ -f "./.capabilities.override.json" ]; then
    local_capabilities=$(jq -s '.[0] * .[1]' <(echo "$local_capabilities") ./.capabilities.override.json)
fi

Static Capabilities Setup

Static capabilities are defined in a JSON file that contains manually configured capabilities:

Creating Static Capabilities

Create .capabilities.json in your agent directory:

{
  "packages": ["typescript", "node", "python"],
  "gpu": {
    "available": true,
    "type": "nvidia",
    "memory_gb": 8
  },
  "storage": {
    "type": "ssd",
    "size_gb": 500,
    "available_gb": 200
  },
  "network": {
    "bandwidth_mbps": 100,
    "latency_ms": 5
  },
  "custom_properties": {
    "team": "backend",
    "region": "us-west-2",
    "cost_center": "engineering"
  }
}

Override Capabilities

Create .capabilities.override.json to override any capabilities:

{
  "hostname": "production-agent-01",
  "memory": {
    "total": 32768,
    "unit": "MB"
  },
  "packages": ["typescript", "node", "python", "production-tools"],
  "environment": "production",
  "debug_mode": false
}

Setup Instructions

For TypeScript Agents

1. Download the capabilities library:

   curl -o .capabilities.dynamic.ts <sheetbot_baseurl>/scripts/lib/capabilities.ts
   

2. Or create a dynamic import file .capabilities.dynamic.ts:

   async function getCapabilities(staticCapabilities) {
       const lib = await import(Deno.env.get("SHEETBOT_BASEURL") + "/scripts/lib/capabilities.ts");
       return await lib.getCapabilities(staticCapabilities);
   }
   
   export { getCapabilities };
   

3. Test the capabilities:

   deno run --allow-all .capabilities.dynamic.ts
   

For Python Agents

Create .capabilities.dynamic.py:

import subprocess
import json
import platform
import psutil  # pip install psutil

def getCapabilities(staticCapabilities):
    capabilities = {}

    # Detect software versions
    try:
        result = subprocess.run(['git', '--version'], capture_output=True, text=True)
        if result.returncode == 0:
            version = result.stdout.split()[-1]
            capabilities['git'] = {'version': version}
    except:
        pass

    # Add system info
    capabilities.update({
        'os': platform.system().lower(),
        'arch': platform.machine(),
        'memory': {
            'total': psutil.virtual_memory().total // (1024*1024),
            'available': psutil.virtual_memory().available // (1024*1024),
            'unit': 'MB'
        }
    })

    return capabilities

For Shell Agents

Create .capabilities.dynamic.sh:

#!/bin/bash

getCapabilities() {
    local static_caps="$1"
    local caps="{}"

    # Detect git
    if command -v git >/dev/null 2>&1; then
        local git_version=$(git --version | awk '{print $3}')
        caps=$(jq -n --arg version "$git_version" '{"git": {"version": $version}}')
    fi

    # Detect node
    if command -v node >/dev/null 2>&1; then
        local node_version=$(node --version | sed 's/v//')
        local node_caps=$(jq -n --arg version "$node_version" '{"node": {"version": $version}}')
        caps=$(jq -s '.[0] * .[1]' <(echo "$caps") <(echo "$node_caps"))
    fi

    # Add system info
    local mem_info=$(free -m | awk 'NR==2{printf "{\"total\":%s,\"free\":%s,\"available\":%s,\"unit\":\"MB\"}", $2, $4, $7}')
    local mem_caps=$(jq -n --argjson mem "$mem_info" '{"memory": $mem}')

    caps=$(jq -s '.[0] * .[1]' <(echo "$caps") <(echo "$mem_caps"))

    echo "$caps"
}

Capabilities Data Structure

The final capabilities object sent to the server has this structure:

{
  "os": "linux",
  "arch": "x86_64",
  "hostname": "agent-host",
  "software": {
    "git": {
      "version": "2.34.1",
      "major_version": 2,
      "minor_version": 34,
      "patch_version": 1
    },
    "deno": {
      "version": "1.28.0",
      "major_version": 1,
      "minor_version": 28,
      "patch_version": 0
    }
  },
  "packages": ["git", "deno"],
  "build": {
    "target": "x86_64-unknown-linux-gnu",
    "arch": "x86_64",
    "os": "linux"
  },
  "os": {
    "os": "linux",
    "release": {
      "version": "5.15.0",
      "major_version": 5,
      "minor_version": 15,
      "patch_version": 0
    }
  },
  "memory": {
    "total": 8192,
    "free": 2048,
    "available": 4096,
    "unit": "MB"
  },
  "loadavg": {
    "1min": 0.5,
    "5min": 0.3,
    "15min": 0.2
  },
  "linux": {
    "release": {
      "version": "20.04",
      "major_version": 20,
      "minor_version": 4,
      "patch_version": 0
    },
    "pretty_name": "Ubuntu 20.04.4 LTS",
    "name": "Ubuntu",
    "versionCodename": "focal",
    "versionId": "20.04"
  }
}

Task Matching

The server uses reported capabilities to match tasks to appropriate agents. Tasks can specify required capabilities, and only agents with matching capabilities will be assigned those tasks.

Testing and Validation

Testing Dynamic Capabilities

For TypeScript dynamic capabilities:

# Test the capabilities detection
deno run --allow-all scripts/lib/capabilities.ts

For Python dynamic capabilities:

# Test by importing and running your dynamic script
python3 -c "
import sys
sys.path.append('.')
import importlib.util
spec = importlib.util.spec_from_file_location('capabilities', './.capabilities.dynamic.py')
module = importlib.util.module_from_spec(spec)
spec.loader.exec_module(module)
caps = module.getCapabilities({})
import json
print(json.dumps(caps, indent=2))
"

For Shell dynamic capabilities:

# Source and test the function
source ./.capabilities.dynamic.sh
getCapabilities "{}"

Validating Final Capabilities

To see what capabilities your agent will report:

# For TypeScript agents - temporarily modify agent.template.ts to print capabilities
# Look for the line: console.log("capabilities:", localCapabilities);

# For Python agents - temporarily add: print("capabilities:", local_capabilities)

# For Shell agents - temporarily add: echo "capabilities: $local_capabilities"

Error Handling

The capabilities loading process is designed to be fault-tolerant:

• Missing files are ignored
• Detection failures for individual tools don’t prevent other detections
• The agent continues with whatever capabilities were successfully detected
• Errors are logged but don’t stop agent execution

Debug a failed task

When a task fails or never starts, the cause is usually in one of four places: the task definition, the agent, the script, or a transition. This guide walks through the checks in order.

1. Inspect the task

Get a token and fetch the task:

export SHEETBOT_BASEURL=http://localhost:3000
export TOKEN=$(curl -sS -X POST "$SHEETBOT_BASEURL/login" \
  -H "Content-Type: application/json" \
  -d '{"username":"your_username","password":"your_password"}' \
  | jq -r .token)

curl -sS -H "Authorization: Bearer $TOKEN" \
  "$SHEETBOT_BASEURL/tasks/<task-id>" | jq

Check:

• status — 0 awaiting, 1 running, 2 completed, 3 failed, 4 paused, 5 deleted.
• type — must match an agent that is actually running.
• capabilitiesSchema — the agent’s capabilities must satisfy it.
• data — the script may have written partial results.
• artefacts — expected output files.
• dependsOn — dependencies must be completed before the task can run.
• transitions — a transition may have moved the task unexpectedly.

If the task is missing, it may have been deleted by a DELETED transition or by an ephemeral task setting.

2. Read the agent output

Agent templates print errors to standard error. Where you look depends on how the agent runs:

• Interactive terminal — the curl ... | bash, deno run, uv run, or node output is directly in front of you.
• systemd — journalctl -u sheetbot-agent -f.
• launchd — the StandardOutPath and StandardErrorPath files from the plist.
• cron — redirect stdout/stderr to a log file in the crontab entry.

Common agent errors:

• Unauthenticated — bad credentials or revoked API key.
• Unauthorized / Forbidden — the user or key lacks performTasks.
• No such file or directory — a script interpreter or command is missing.
• Script failed with exit code N — the task script returned a non-zero status.
• fetch failed / ECONNREFUSED — the agent cannot reach SHEETBOT_BASEURL.

3. Check agent matching

The agent polls with its own type and capabilities. A task is only returned if both match.

Check which agents have checked in recently:

curl -sS -H "Authorization: Bearer $TOKEN" \
  "$SHEETBOT_BASEURL/agenttracker?minutes=60" | jq

If no agent of the task’s type appears, start one:

• deno — Deno agent
• node, javascript, js, typescript, ts — Node.js agent
• python — Python agent
• bash — Bash agent

See Setting up other agents.

If an agent is present but gets nothing, compare its capabilities with the task’s capabilitiesSchema. A schema requiring packages containing cmake will not match an agent that does not report cmake.

You can inspect the effective capabilities from the agent’s working directory:

cat .capabilities.json
cat .capabilities.override.json

4. Watch task events live

Subscribe to the SSE stream:

curl -N -H "Authorization: Bearer $TOKEN" \
  "$SHEETBOT_BASEURL/events"

Events include task added, changed, status changed, completed, failed, and deleted, plus agent events. This is useful for seeing whether an agent accepted the task or whether a transition fired immediately.

5. Check execution and transition metrics

curl -sS -H "Authorization: Bearer $TOKEN" \
  "$SHEETBOT_BASEURL/tasktracker?minutes=60" | jq
curl -sS -H "Authorization: Bearer $TOKEN" \
  "$SHEETBOT_BASEURL/transitiontracker?minutes=60" | jq

The transition tracker records evaluation time and whether a transition matched. If a transition never fires, check its statuses, condition, and timing.

6. Requeue or reset a task

If an agent crashed while running a task, the task can remain RUNNING. A user with updateTasks can reset it:

curl -X PATCH \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"status":0}' \
  "$SHEETBOT_BASEURL/tasks/<task-id>"

0 is AWAITING. Use 4 for PAUSED if you want to stop it being picked up. Requeue only after fixing the underlying cause, or the same failure repeats.

7. Inspect script-specific failures

• Node.js — task scripts run as ESM because the repository uses "type": "module". Use import, not require. Node strip-only TypeScript does not support enums or network imports.
• Deno — the agent imports the task script. If Deno blocks the import, run the agent with -A or add --allow-import for the SheetBot host.
• Python — scripts with a /// script block run through uv; others run inline in the agent process.
• Bash — a script starting with #! has its stdout treated as completion data, which must be valid JSON. Inline scripts are run with bash.

8. Check file and permission problems

• Task artefacts are under artefacts/tasks/<task-id>/.
• Sheet writes need putSheetData.
• Bucket uploads need createArtefacts; reads need viewArtefacts.
• Init, library, script, static, middleware, and event-handler directories must exist and be readable by the server user.
• SQLite errors usually mean the database is locked by another process or the directory is not writable.

Quick checklist

Next

• Task transitions — transition syntax and examples.
• Agent workflow — how polling and reporting work.
• Task events — event names and payloads.

Deploy SheetBot

This guide covers running SheetBot as a long-lived service, putting it behind TLS, and backing up its data. The examples assume Linux with systemd; a launchd example for macOS follows.

1. Prepare the install

Create a dedicated user and directory:

sudo useradd --system --home /opt/sheetbot --shell /usr/sbin/nologin sheetbot
sudo mkdir -p /opt/sheetbot
sudo chown sheetbot:sheetbot /opt/sheetbot

Install as the service user:

sudo -u sheetbot git clone https://github.com/jorisvddonk/sheetbot /opt/sheetbot
cd /opt/sheetbot
sudo -u sheetbot npm ci --omit=dev

SheetBot needs Node.js 22.6 or newer. npm ci --omit=dev is enough at runtime; TypeScript is only used for development type checks.

The working directory holds persistent state:

• users.db, tasks.db, sheet_permissions.db
• sheets/*.db
• artefacts/
• secret.txt
• key.pem, cert.pem if using built-in HTTPS
• init/

Keep these writable by the service user and out of version control.

2. Configure environment

Create /etc/sheetbot/sheetbot.env:

NODE_ENV=production
SHEETBOT_PORT=3000

Restrict it:

sudo install -d -m 750 /etc/sheetbot
sudo chown root:sheetbot /etc/sheetbot
sudo chmod 640 /etc/sheetbot/sheetbot.env

Add any environment variables you need, such as search paths or SHEETBOT_WIDGET_CACHE_TTL.

3. Run with systemd

Create /etc/systemd/system/sheetbot.service:

[Unit]
Description=SheetBot
After=network.target

[Service]
Type=simple
User=sheetbot
Group=sheetbot
WorkingDirectory=/opt/sheetbot
EnvironmentFile=/etc/sheetbot/sheetbot.env
ExecStart=/usr/bin/node --experimental-sqlite --experimental-strip-types main.ts
Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target

Adjust the Node path (command -v node). Enable and start:

sudo systemctl daemon-reload
sudo systemctl enable --now sheetbot
sudo systemctl status sheetbot
journalctl -u sheetbot -f

Check it locally:

curl -fsS http://127.0.0.1:3000/ >/dev/null && echo ok

4. Run with launchd on macOS

Create ~/Library/LaunchAgents/local.sheetbot.plist:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
  <key>Label</key>
  <string>local.sheetbot</string>
  <key>ProgramArguments</key>
  <array>
    <string>/opt/homebrew/bin/node</string>
    <string>--experimental-sqlite</string>
    <string>--experimental-strip-types</string>
    <string>main.ts</string>
  </array>
  <key>WorkingDirectory</key>
  <string>/opt/sheetbot</string>
  <key>EnvironmentVariables</key>
  <dict>
    <key>NODE_ENV</key>
    <string>production</string>
    <key>SHEETBOT_PORT</key>
    <string>3000</string>
  </dict>
  <key>RunAtLoad</key>
  <true/>
  <key>KeepAlive</key>
  <true/>
  <key>StandardOutPath</key>
  <string>/tmp/sheetbot.log</string>
  <key>StandardErrorPath</key>
  <string>/tmp/sheetbot.err.log</string>
</dict>
</plist>

Load it:

launchctl bootstrap gui/$(id -u) ~/Library/LaunchAgents/local.sheetbot.plist
launchctl kickstart -k gui/$(id -u)/local.sheetbot

Adjust the Node path for Intel Homebrew (/usr/local/bin/node) or your Node installation.

5. Put TLS in front

SheetBot listens on all interfaces. For a public deployment, bind it behind a reverse proxy and a firewall rather than exposing port 3000 directly.

Example Nginx site:

server {
    listen 443 ssl http2;
    server_name sheetbot.example.com;

    ssl_certificate     /etc/letsencrypt/live/sheetbot.example.com/fullchain.pem;
    ssl_certificate_key /etc/letsencrypt/live/sheetbot.example.com/privkey.pem;

    location / {
        proxy_pass http://127.0.0.1:3000;
        proxy_http_version 1.1;
        proxy_set_header Host $host;
        proxy_set_header X-Forwarded-Proto $scheme;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    }

    location = /events {
        proxy_pass http://127.0.0.1:3000;
        proxy_http_version 1.1;
        proxy_set_header Host $host;
        proxy_set_header X-Forwarded-Proto $scheme;
        proxy_set_header Connection "";
        proxy_buffering off;
        proxy_read_timeout 1h;
    }
}

SheetBot trusts forwarded headers, so req.protocol and generated agent/artefact URLs use https when X-Forwarded-Proto is set.

Alternatively, use the built-in HTTPS server. Put key.pem and cert.pem in the working directory; SheetBot starts HTTPS on SHEETBOT_HTTPS_PORT (default 443):

openssl req -x509 -newkey rsa:2048 -nodes \
  -keyout key.pem -out cert.pem -days 365 \
  -subj "/CN=sheetbot.local"

A self-signed certificate is fine for testing but not for public clients. A reverse proxy with Let’s Encrypt is usually simpler. Binding port 443 as a non-root user requires extra privileges; use a high port behind a proxy or grant the capability explicitly.

6. Back up

Back up the SQLite files with .backup while the service is running, and copy artefacts:

BACKUP="/backup/sheetbot-$(date +%F-%H%M)"
mkdir -p "$BACKUP/sheets"

sqlite3 tasks.db ".backup '$BACKUP/tasks.db'"
sqlite3 users.db ".backup '$BACKUP/users.db'"

[ -f sheet_permissions.db ] && \
  sqlite3 sheet_permissions.db ".backup '$BACKUP/sheet_permissions.db'"

for db in sheets/*.db; do
  [ -e "$db" ] || continue
  sqlite3 "$db" ".backup '$BACKUP/sheets/$(basename "$db")'"
done

cp -a artefacts "$BACKUP/artefacts"

for file in secret.txt key.pem cert.pem; do
  [ -e "$file" ] && cp -a "$file" "$BACKUP/"
done

Back up /etc/sheetbot/sheetbot.env separately if it contains deployment settings. Do not put API keys or passwords in a world-readable backup.

7. Restore

Stop the service, replace the data, fix ownership, and start:

sudo systemctl stop sheetbot

cp -a "$BACKUP/tasks.db" "$BACKUP/users.db" /opt/sheetbot/
[ -f "$BACKUP/sheet_permissions.db" ] && \
  cp -a "$BACKUP/sheet_permissions.db" /opt/sheetbot/
cp -a "$BACKUP/sheets/." /opt/sheetbot/sheets/
cp -a "$BACKUP/artefacts/." /opt/sheetbot/artefacts/

sudo chown -R sheetbot:sheetbot /opt/sheetbot
sudo systemctl start sheetbot

For launchd, use launchctl bootout/bootstrap around the same file operations.

8. Upgrade

sudo systemctl stop sheetbot
sudo -u sheetbot git -C /opt/sheetbot pull
cd /opt/sheetbot
sudo -u sheetbot npm ci --omit=dev
sudo systemctl start sheetbot

Back up first. Schema migrations run automatically on startup.

Security notes

• Run as an unprivileged user.
• Do not expose users.db, tasks.db, sheets/, or artefacts/ through the reverse proxy.
• Use scoped API keys for agents; see Using API keys for agents.
• Public sheets and the public artefact bucket are readable by anyone. Never publish secrets.
• Agent tasks execute arbitrary code by design. Run agents on machines you are willing to trust.

Next

• Environment variables — every deployment setting.
• Manage API keys — credentials for remote agents.
• Debug a failed task — diagnose an unhealthy deployment.

Known issues

• If you submit an ephemeral task and then a task that depends on the ephemeral task, BUT the ephemeral task got completed before you submitted the second task, the second task will never complete, as it has a dependency on a task that isn’t in the database anymore. This is because dependsOn is no longer getting checked when inserting a task.
• UNIQUE constraint failed on transitions_schedule when scheduling periodic transitions: When a task status is updated multiple times (e.g., through repeated completion requests or middleware triggers), periodic transitions matching the current status are scheduled multiple times, causing a database constraint violation since (task_id, transition_index) must be unique. This results in 500 errors when completing tasks with recurring transitions.

Reference

Information-oriented pages that describe SheetBot’s contracts, APIs, data formats, and built-in components.

• HTTP API — endpoints, authentication, permissions, and examples.
• Protocol — task lifecycle, failure handling, transitions, and state changes.
• Agent runtime API — the contract for implementing agent runtimes.
• Environment variables — server, agent, and task-injected variables.
• Task events — event types and payloads.
• Data management — task data, dependency injection, and sheet data.
• Artefacts — artefact storage and the S3-compatible API.
• Widgets — built-in widget types and configuration.
• Library script annotations — metadata annotations for library scripts.
• Sheet database — tables, views, and sheet storage layout.

HTTP API

SheetBot exposes an HTTP API for tasks, agents, sheets, artefacts, scripts, and events. The full machine-readable specification is served at:

GET /openapi.yaml

It is also in the repository as openapi.yaml. This page is a practical map of the endpoints; use the OpenAPI file for exact schemas.

Base URL and authentication

Use the same URL you open in the browser, for example http://localhost:3000 or https://sheetbot.example.com.

Authenticate with a JWT:

TOKEN=$(curl -sS -X POST "$SHEETBOT_BASEURL/login" \
  -H "Content-Type: application/json" \
  -d '{"username":"your_username","password":"your_password"}' \
  | jq -r .token)

Or exchange an API key:

TOKEN=$(curl -sS -X POST "$SHEETBOT_BASEURL/login" \
  -H "Content-Type: application/json" \
  -d "{\"apiKey\":\"$SHEETBOT_AUTH_APIKEY\"}" \
  | jq -r .token)

Send Authorization: Bearer $TOKEN on authenticated requests. Most endpoints enforce a permission; see Configure permissions.

Common status codes

Authentication and metadata

Tasks

Create a task:

curl -X POST "$SHEETBOT_BASEURL/tasks" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "hello",
    "script": "console.log(\"hello\")",
    "type": "node",
    "data": {},
    "transitions": []
  }'

Poll as an agent:

curl -X POST "$SHEETBOT_BASEURL/tasks/get" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"type":"node","capabilities":{"os":"linux","arch":"x86_64"}}'

Status values: 0 awaiting, 1 running, 2 completed, 3 failed, 4 paused, 5 deleted.

Scripts and library

The agent template endpoints replace ${req.protocol} and ${req.get('host')} with the request origin, so a downloaded agent points back at the server that served it.

Sheets

Upsert a row:

curl -X POST "$SHEETBOT_BASEURL/sheets/build-status/data" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"key":"linux-x64","name":"Linux x64","status":2,"progress":100}'

The sheet is created on the first write. See Sheet database and Working with sheets.

Artefacts and buckets

Upload a bucket file with JWT:

curl -X PUT "$SHEETBOT_BASEURL/artefacts/reports/report.txt" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: text/plain" \
  --data-binary @report.txt

See Working with artefacts and Artefacts.

Events and metrics

Subscribe to events:

curl -N -H "Authorization: Bearer $TOKEN" "$SHEETBOT_BASEURL/events"

Errors

Errors are usually plain text or JSON with an error field. A 403 means the JWT is valid but lacks the required permission; a 401 means the JWT is missing, expired, or invalid. Agent templates translate these into Unauthenticated and Unauthorized.

Next

• OpenAPI specification — exact request and response schemas.
• Protocol — task lifecycle and state diagram.
• Agent runtime API — the agent-specific contract.

Protocol

The SheetBot protocol enables distributed task execution with dependency management and capability-based matching.

Sequence Diagram

sequenceDiagram
    participant User
    participant Server
    participant Agent

    User->>Server: POST /tasks (create task)
    Agent->>Server: POST /tasks/get (type, capabilities)
    Server->>Agent: Task script URL
    Agent->>Server: POST /tasks/:id/accept
    Note over Agent: Run script (with dep results injected)
    Agent->>Server: POST /tasks/:id/complete (with result data)
    Note over Server: Update task status
    Note over Server: Evaluate transitions for new status

Failure Handling

sequenceDiagram
    participant Agent
    participant Server

    Agent->>Server: POST /tasks/:id/failed
    Note over Server: Set status to FAILED
    Note over Server: Evaluate transitions for new status

Task State Diagram

stateDiagram-v2
    [*] --> PAUSED : Task created (scripts/tools)
    [*] --> AWAITING : Task created (API default)
    PAUSED --> AWAITING : Manual activation
    AWAITING --> RUNNING : Agent accepts task
    RUNNING --> COMPLETED : Task succeeds
    RUNNING --> FAILED : Task fails
    note right of PAUSED
        Tasks can start paused for
        dependency setup before execution
    end note
    note right of AWAITING
        Tasks ready for agent pickup
        (default API creation state)
    end note
    note right of RUNNING
        Task being executed by agent
    end note
    note right of COMPLETED
        Transitions allow automated
        status changes to any other
        status based on conditions
        and timing rules configured
        per task.
    end note

Key Concepts

• Tasks: Units of work with scripts, dependencies, and capability requirements
• Dependencies: Tasks can depend on others; execution waits for all deps to complete
• Capabilities: JSON Schema matching for agent selection
• Transitions: Timed status changes for tasks (e.g., auto-delete, periodic reset)
• Artefacts: File outputs stored per task
• Runner Types: SheetBot is agnostic to execution environments; it provides task management API while runners handle actual script execution

Transitions

Transitions enable automated status changes for tasks based on time and conditions. See Configure task transitions for worked examples:

• Configuration: Tasks include a transitions array with status change rules
• Evaluation: Transitions are checked when task status changes or at scheduled intervals
• Use Cases: Auto-deletion after completion, periodic task resets, timeouts for awaiting tasks
• Condition Matching: JSON Schema validation against the entire task object (including id, name, script, status, data, artefacts, dependsOn, transitions, type, capabilitiesSchema)
• Data Mutations: Optional dataMutations object to update task data when transitioning
• Timing: Immediate evaluation or periodic checks (e.g., every 1h, 30m, 1s)

Example transition for auto-deleting completed tasks after 1 hour:

{
  "statuses": ["COMPLETED"],
  "condition": {},
  "timing": {"every": "1h", "immediate": false},
  "transitionTo": "DELETED"
}

Example transition with condition and data mutation:

{
  "statuses": ["COMPLETED"],
  "condition": {
    "type": "object",
    "properties": {
      "data": {
        "type": "object",
        "properties": {
          "errorCount": {"type": "number", "maximum": 5}
        }
      }
    }
  },
  "timing": {"immediate": true},
  "transitionTo": "AWAITING",
  "dataMutations": {"retryCount": 1}
}

Artefacts

See Artefacts for details on file outputs, storage, and S3-compatible API.

Capabilities

Capabilities enable fine-grained agent selection using JSON Schema validation:

• Task Schema: Tasks specify capabilitiesSchema (JSON Schema) defining required agent capabilities
• Agent Capabilities: Agents send a capabilities JSON object when polling /tasks/get
• Matching: Server validates agent capabilities against task schema using AJV
• Static Capabilities: Fixed properties like OS, CPU architecture, available tools
• Dynamic Capabilities: Runtime-computed properties like current load, available memory, network status

Example task schema requiring Linux with Node.js:

{
  "type": "object",
  "properties": {
    "os": { "const": "linux" },
    "nodeVersion": { "type": "string" }
  },
  "required": ["os", "nodeVersion"]
}

Agents with matching capabilities (e.g., {"os": "linux", "nodeVersion": "18.0.0"}) can execute the task.

Task designers have full control over capabilitiesSchema; SheetBot enforces no predefined schemas, allowing complete customization for agent matching.

Setting Up Dynamic Capabilities

Agents can use a dynamic capabilities file to compute runtime capabilities like software versions, memory, and load averages. To set up:

1. Download the capabilities library:

   curl -o .capabilities.dynamic.ts <sheetbot_baseurl>/scripts/lib/capabilities.ts
   

2. Or create a dynamic import file .capabilities.dynamic.ts:

   async function getCapabilities(staticCapabilities) {
       const lib = await import(Deno.env.get("SHEETBOT_BASEURL") + "/scripts/lib/capabilities.ts");
       return await lib.getCapabilities(staticCapabilities);
   }
   
   export { getCapabilities };
   

3. Modify agent templates to use the dynamic capabilities file by importing and calling getCapabilities() with static capabilities.

The dynamic capabilities include detected software versions (git, clang, cmake, etc.), OS details, memory info, load averages, and hostname.

Agent Runtime API Contract

This document specifies the API contract for implementing agent runtimes that integrate with the Sheetbot distributed task execution system. It defines the endpoints, request/response formats, and behavioral expectations for new runtime implementations.

Core Concepts

Agent Templating System

Sheetbot provides a templating system to serve pre-built agent runtime implementations for different programming languages. These templates are stored as agent.template.<extension> files (e.g., agent.template.py for Python, agent.template.ts for TypeScript/Deno, agent.template.js for Node.js) and are served dynamically via the /scripts/agent.<extension> endpoint.

The templates contain boilerplate code that implements the full agent lifecycle:

• Authentication with the Sheetbot server
• Capability detection and declaration
• Task polling and execution (per invocation, agents typically poll ONCE, leaving polling frequency to the operating system)
• Result reporting

When serving a template, Sheetbot replaces placeholders in the template code:

• ${req.protocol} is replaced with the request protocol (e.g., “https”)
• ${req.get('host')} is replaced with the request host (e.g., “example.com”)

This allows agents to be downloaded and run immediately, automatically connecting back to the originating Sheetbot server without manual configuration.

Supported Extensions:

• .py: Python agent template (uses uv for dependency management and script execution)
• .ts: TypeScript/Deno agent template
• .js: JavaScript/TypeScript-capable Node.js agent template (scripts/agent.template.js)
• .sh: Bash agent template (scripts/agent.template.sh)

Agents can load additional capabilities from local files:

• .capabilities.json: Static JSON capabilities
• .capabilities.dynamic.<extension>: Dynamic capability detection script
• .capabilities.override.json: Override capabilities

Agent Lifecycle

Agents follow a polling-based execution model:

1. Authentication (optional): Obtain Bearer token for API access
2. Task Discovery: Poll for available tasks matching agent capabilities
3. Task Acquisition: Accept and prepare task for execution
4. Script Execution: Run task script in isolated environment
5. Result Submission: Report execution outcome to server

Capabilities Declaration

See Capabilities for more details on the capabilities system.

Agents declare their execution capabilities to receive appropriate tasks. Capabilities are JSON objects with arbitrary structure, but should include basic system information.

Environment Variables

Scripts receive execution context through environment variables set by the agent runtime.

Authentication API

POST /login

Authenticates an agent and returns a JWT token for subsequent API calls.

Request:

• Method: POST
• Content-Type: application/json
• Body:

{
  "username": "string",
  "password": "string"
}

Or using an API key:

{
  "apiKey": "string"
}

Response (Success - 200):

{
  "token": "jwt_token_string"
}

Response (Error - 401/403/500):

{
  "error": "error_message"
}

Usage: Include Authorization: Bearer <token> header in all subsequent API requests.

Capabilities Declaration

Agents declare their execution capabilities when requesting tasks. Capabilities are arbitrary JSON objects that help the server match tasks to suitable agents.

Recommended Capability Fields

While capabilities can contain any JSON-serializable data, implementations should include:

• os: Operating system identifier (e.g., “linux”, “darwin”, “win32”)
• arch: CPU architecture (e.g., “x86_64”, “aarch64”, “AMD64”)
• hostname: Machine hostname for identification

Capabilities Usage

Capabilities are submitted when polling for tasks and determine which tasks an agent receives.

Usage

Capabilities are sent to /tasks/get endpoint to request matching tasks:

{
  "type": "python", // or "deno", or something else...
  "capabilities": {
    "os": {
      "os": "linux"
    },
    "arch": "x86_64",
    "hostname": "myhost01"
  }
}

Task Execution API

POST /tasks/get

Polls for available tasks matching the agent’s capabilities.

Request:

• Method: POST
• Content-Type: application/json
• Authorization: Bearer <token> (if authenticated)
• Body:

{
  "type": "runtime_type",
  "capabilities": {
    // agent capabilities object
  }
}

Response (Task Available - 200):

{
  "id": "task-uuid",
  "script": "script_url",
  "type": "runtime_type"
}

Response (No Task Available - 200):

{}

GET /scripts/:id.*

Retrieves the executable script for a task. May include dependency injection placeholders replaced with data from completed prerequisite tasks.

Request:

• Method: GET
• Authorization: Bearer <token> (if authenticated)

Response (Success - 200):

• Content-Type: Appropriate for script language (e.g., application/typescript, text/x-python)
• Body: Script content with dependency injection applied

Dependency Injection: Scripts may contain placeholders like __DEP_RESULT_{task_id}__ that are replaced with JSON-stringified results from dependent tasks.

Artefacts API

Tasks can have associated file artefacts for data sharing and persistence.

POST /tasks/:id/artefacts

Uploads a file artefact for a task.

Request:

• Method: POST
• Content-Type: multipart/form-data
• Authorization: Bearer <token> (if authenticated)
• Body: Form data with file field containing the file

Response (Success - 200):

{
  "url": "download_url",
  "directURL": "direct_file_url"
}

GET /tasks/:id/artefacts/:filename

Downloads a specific artefact file.

Request:

• Method: GET
• Authorization: Bearer <token> (if authenticated)

Response (Success - 200):

• Content-Type: Appropriate MIME type for the file
• Body: File content

DELETE /tasks/:id/artefacts/:filename

Removes an artefact file from a task.

Request:

• Method: DELETE
• Authorization: Bearer <token> (if authenticated)

Response (Success - 204):

• No content

Sheet Data API

Agents can update sheet data during task execution to store results or share state.

POST /sheets/:id/data

Updates or inserts data into a sheet.

Request:

• Method: POST
• Content-Type: application/json
• Authorization: Bearer <token> (if authenticated)
• Body:

{
  "key": "unique_key",
  // additional arbitrary JSON properties
}

Response (Success - 200):

{
  "id": "sheet_id",
  "key": "unique_key"
}

Response (Error - 400/404/500):

{
  "error": "error_message"
}

Sheets provide persistent key-value storage using SQLite databases. Agents can use this to store execution results, metrics, or intermediate data that persists across tasks.

Script Execution Environment

Agents must provide the following environment variables to executing scripts, constructed exactly as shown:

Required Variables:

• SHEETBOT_BASEURL: Base URL of the Sheetbot server
• SHEETBOT_TASK_ID: Task identifier (from task response id field)
• SHEETBOT_AUTHORIZATION_HEADER: Complete authorization header string (Bearer <token>)
• SHEETBOT_TASK_BASEURL: {SHEETBOT_BASEURL}/tasks/{task_id}
• SHEETBOT_TASK_ACCEPTURL: {SHEETBOT_BASEURL}/tasks/{task_id}/accept
• SHEETBOT_TASK_COMPLETEURL: {SHEETBOT_BASEURL}/tasks/{task_id}/complete
• SHEETBOT_TASK_FAILEDURL: {SHEETBOT_BASEURL}/tasks/{task_id}/failed
• SHEETBOT_TASK_DATAURL: {SHEETBOT_BASEURL}/tasks/{task_id}/data
• SHEETBOT_TASK_ARTEFACTURL: {SHEETBOT_BASEURL}/tasks/{task_id}/artefacts

Construction Example:

// Assuming task = {id: "task-123"} and baseUrl = "https://example.com"
SHEETBOT_BASEURL = "https://example.com"
SHEETBOT_TASK_ID = "task-123"
SHEETBOT_AUTHORIZATION_HEADER = "Bearer eyJ..."
SHEETBOT_TASK_BASEURL = "https://example.com/tasks/task-123"
SHEETBOT_TASK_ACCEPTURL = "https://example.com/tasks/task-123/accept"
SHEETBOT_TASK_COMPLETEURL = "https://example.com/tasks/task-123/complete"
SHEETBOT_TASK_FAILEDURL = "https://example.com/tasks/task-123/failed"
SHEETBOT_TASK_DATAURL = "https://example.com/tasks/task-123/data"
SHEETBOT_TASK_ARTEFACTURL = "https://example.com/tasks/task-123/artefacts"

Scripts use these variables to interact with the server during execution.

POST /tasks/:id/accept

Signals that the agent has received the task and is beginning execution.

Request:

• Method: POST
• Content-Type: application/json
• Authorization: Bearer <token> (if authenticated)
• Body:

{}

Response (Success - 200):

{}

Script Execution Contract

Agents are responsible for executing scripts in their target runtime environment. The execution contract requires:

1. Environment Setup: Provide all required environment variables to the script
2. Execution: Run the script with appropriate isolation and error handling
3. Result Extraction: Collect execution results in the expected format
4. Status Reporting: Report either success with data or failure

Scripts may access server APIs during execution using the provided environment variables for authentication and task-specific operations.

POST /tasks/:id/complete

Reports successful task completion with result data.

Request:

• Method: POST
• Content-Type: application/json
• Authorization: Bearer <token> (if authenticated)
• Body:

{
  "data": {
    // execution result data (arbitrary structure)
  }
}

Response (Success - 200):

{}

POST /tasks/:id/failed

Reports task execution failure.

Request:

• Method: POST
• Content-Type: application/json
• Authorization: Bearer <token> (if authenticated)
• Body:

{}

Response (Success - 200):

{}

Example success responses:

{
  "data": {
    "result": "task completed",
    "timestamp": "2024-01-01T12:00:00Z",
    "metrics": {
      "duration_ms": 1500,
      "memory_used_mb": 45
    }
  }
}

{
  "data": {
    "default": "main result",
    "helper": "additional data"
  }
}

Failure Report

Request Details:

• Method: POST
• URL: SHEETBOT_TASK_FAILEDURL
• Headers: Content-Type: application/json and Authorization: Bearer <token> (if authenticated)
• Body: Empty JSON object

{}

Failures are reported when script execution throws an exception or returns a non-zero exit code.

Error Handling

HTTP Status Codes

API endpoints return standard HTTP status codes:

• 200: Success
• 204: Success with no content
• 400: Bad request (invalid data)
• 401: Authentication required
• 403: Authorization failed
• 404: Resource not found
• 500: Server error

Error Response Format

Error responses include a JSON body with error details:

{
  "error": "error_description"
}

Task Execution Errors

Runtime implementations must catch execution errors and report them via the failure endpoint. Scripts that throw exceptions or return non-zero exit codes should be reported as task failures.

Implementation Checklist

New runtime implementations should:

1. ✅ Implement authentication flow with /login endpoint
2. ✅ Declare capabilities for task matching
3. ✅ Poll for tasks using /tasks/get with appropriate capabilities
4. ✅ Accept tasks via /tasks/:id/accept
5. ✅ Fetch and execute scripts from /scripts/:id.* endpoints
6. ✅ Provide required environment variables to scripts
7. ✅ Report execution results via /tasks/:id/complete or /tasks/:id/failed
8. ✅ Handle HTTP errors and authentication requirements
9. ✅ Support artefact upload/download if needed by scripts
10. ✅ Implement proper error handling and cleanup

Security Considerations

• Credentials are removed from environment after login
• Scripts execute in isolated contexts
• HTTP-only communication with server
• No persistent storage of sensitive data

Deployment

Agents are typically deployed as:

• Standalone executables in containerized environments
• Scheduled jobs on compute resources
• On-demand processes triggered by external systems

The runtime is designed to be stateless and horizontally scalable.

Environment variables

SheetBot reads environment variables at startup. Restart the server after changing them.

Server

Search paths

Search paths are colon-separated lists. Relative paths are resolved from the server’s working directory. Missing directories are skipped with a warning.

Example:

export SHEETBOT_LIBRARY_SEARCH_PATH="/opt/sheetbot/library:$HOME/sheetbot/library"
export SHEETBOT_STATIC_SEARCH_PATHS="/opt/sheetbot/widgets"
export SHEETBOT_INIT_SEARCH_PATHS="/etc/sheetbot/init"

Agent and client

These are read by agent templates and task scripts. They are not server settings.

Example for an agent:

export SHEETBOT_BASEURL=http://localhost:3000
export SHEETBOT_AUTH_APIKEY=your.api.key

See Using API keys for agents.

Distributed runtime

The experimental distributed runtime falls back to the normal auth variables:

Precedence and notes

• Agent authentication prefers SHEETBOT_AUTH_APIKEY, then username/password. Unset user/password when using a key.
• Server CLI flags --port/-p and --https-port/-P override their environment variables.
• Init scripts run in lexicographic order per directory; ./init/ runs before additional directories.
• Widget discovery scans static directories once per cache window; restart or set SHEETBOT_WIDGET_CACHE_TTL=0 when developing widgets.
• Env files are a shell/systemd/launchd concern, not a SheetBot feature. Do not commit secrets.

Next

• Deploy SheetBot — service files and environment files.
• Configure library paths — SHEETBOT_LIBRARY_SEARCH_PATH.
• Static search paths — widgets and assets from other directories.

Task Events

SheetBot emits events whenever tasks are added, changed, deleted, or their status changes. These events are emitted at the database level to ensure they always trigger properly.

Event Types

The following events are available:

• task:added - Emitted when a new task is added to the database
• task:changed - Emitted when any task property changes (data, artefacts, etc.)
• task:status_changed - Emitted when a task’s status changes
• task:deleted - Emitted when a task is deleted
• task:created - Legacy event (kept for compatibility)
• task:completed - Legacy event (kept for compatibility)
• task:failed - Legacy event (kept for compatibility)

Usage

The TaskEventEmitter is available globally in main.ts and can be accessed to listen for events:

import { TaskEventEmitter, TaskEvent } from "./lib/task-events.ts";

// Get the global event emitter (passed through your code)
const taskEventEmitter = new TaskEventEmitter();

// Listen for task additions
taskEventEmitter.on(TaskEvent.ADDED, (data) => {
    console.log(`Task added: ${data.taskId}`, data.task);
});

// Listen for status changes
taskEventEmitter.on(TaskEvent.STATUS_CHANGED, (data) => {
    console.log(`Task ${data.taskId} status changed from ${data.oldStatus} to ${data.newStatus}`);
});

// Listen for any task changes
taskEventEmitter.on(TaskEvent.CHANGED, (data) => {
    console.log(`Task ${data.taskId} changed:`, data.changes);
});

// Listen for task deletions
taskEventEmitter.on(TaskEvent.DELETED, (data) => {
    console.log(`Task deleted: ${data.taskId}`);
});

Event Data Structures

TaskEventData

{
    taskId: string;
    timestamp: number;
    metadata?: Record<string, any>;
}

TaskChangedEventData

{
    taskId: string;
    timestamp: number;
    task: Task;
    changes?: Record<string, any>;
}

TaskStatusChangedEventData

{
    taskId: string;
    timestamp: number;
    task: Task;
    oldStatus: TaskStatus;
    newStatus: TaskStatus;
}

Implementation Details

Events are emitted at the lowest level (database operations) to ensure they always trigger:

• addTask() - Emits task:added
• updateTaskStatus() - Emits task:status_changed and task:changed
• updateTaskData() - Emits task:changed
• updateTaskAddArtefact() - Emits task:changed
• updateTaskRemoveArtefact() - Emits task:changed
• deleteTask() - Emits task:deleted

Transitions also trigger these events when they execute, ensuring all task modifications are tracked.

Example: Custom Notification System

// In an init script or custom module
taskEventEmitter.on(TaskEvent.STATUS_CHANGED, async (data) => {
    if (data.newStatus === TaskStatus.FAILED) {
        // Send notification when a task fails
        await sendNotification(`Task ${data.taskId} failed!`);
    }
});

taskEventEmitter.on(TaskEvent.COMPLETED, async (data) => {
    // Log completed tasks
    console.log(`Task ${data.taskId} completed successfully`);
});

Data Management

Task Data

Tasks store arbitrary JSON data that can be updated during execution:

• Update during execution: Agents can POST to /tasks/:id/data to add/update data
• Completion data: Agents include result data in /tasks/:id/complete request
• Dependency injection: Completed task data is injected into dependent scripts using __DEP_RESULT_<taskId>__ placeholders (createInjectDependenciesMiddleware in lib/middleware.ts)

Example: A compilation task might store execution time, success status, or warning counts in its data.

Dependency Injection

When serving task scripts, SheetBot injects dependency results:

• Placeholders: Use __DEP_RESULT_<taskId>__ in scripts
• Injection: Server replaces placeholders with JSON-stringified depTask.data.default values
• Usage: Scripts can reference dependency outputs directly

Example script:

const depResult = __DEP_RESULT_abc123__; // Becomes actual data
console.log(depResult);

Note: Dependency injection is an advanced feature primarily used by the distributed runtime. For most use cases, it’s recommended to fetch task data dynamically at runtime using the API (e.g., GET /tasks/{id}), or have tasks communicate via shared sheets for better flexibility and decoupling.

Sheet Data

Sheets provide persistent key-value storage using SQLite databases. See Sheet Database Table Structure for details on the underlying schema, or follow Working with sheets for a task-based example.

• Upsert data: POST to /sheets/:id/data with JSON containing key and other fields
• Delete row: DELETE /sheets/:id/data/:key
• Retrieve data: GET /sheets/:id returns schema and all rows
• Storage: Each sheet is a SQLite DB file in ./sheets/ directory

Sheets are useful for storing shared state, results, or configuration across tasks.

Sheet Updates During Tasks

Tasks can update sheet data during execution:

sequenceDiagram
    participant Agent
    participant Server
    participant SheetDB

    Agent->>Server: POST /sheets/:id/data (key and fields)
    Server->>SheetDB: Upsert data
    SheetDB->>Server: Confirm update
    Server->>Agent: 200 OK

Artefacts

Artefacts are file outputs associated with tasks, stored in the filesystem for persistence and sharing.

• Storage: Files are stored in ./artefacts/tasks/{taskId}/ directories
• Uploading: Agents can upload artefacts during execution via POST /tasks/{id}/artefacts with multipart/form-data
• Accessing: Artefacts are accessible via GET /tasks/{id}/artefacts/{filename} which redirects to the direct file URL
• Listing: Artefacts are listed in the task’s artefacts array
• Deletion: Individual artefacts can be deleted via DELETE /tasks/{id}/artefacts/{filename}
• Cloning: When tasks are cloned, artefacts are copied to the new task directory

S3-Compatible API

SheetBot provides an S3-compatible API for artefact management, enabling integration with S3 clients and tools:

• Multipart Uploads: POST /artefacts/{bucket}/* for initiating multipart uploads
• Direct Uploads: PUT /artefacts/{bucket}/* for uploading files or parts
• Listing: GET /artefacts/{bucket} or GET /artefacts/{bucket}/* to list artefacts
• Deletion: DELETE /artefacts/* to remove artefacts

This allows using standard S3-compatible tools and workflows for artefact handling.

Authentication for the S3-compatible API can be done via standard SheetBot login (with appropriate permissions) or AWS-compatible credentials extracted from requests (extractAWSCredentialsIfPresent in lib/middleware.ts). Ensure users have the “createArtefacts”, “viewArtefacts”, or “deleteArtefacts” permissions as needed.

To obtain temporary AWS-compatible credentials for S3 API access, use POST /artefacts-credentials after logging in. This returns fake AWS credentials that can be used with S3-compatible tools.

Public Artefacts Bucket

The public bucket is served without authentication:

• GET /artefacts/public lists the bucket.
• GET /artefacts/public/* reads a file.

Uploads still require login and the createArtefacts permission. Use the public bucket only for files that are safe for anonymous access:

curl -X PUT "$SHEETBOT_BASEURL/artefacts/public/report.txt" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: text/plain" \
  --data-binary @report.txt

See Working with artefacts for a full walkthrough.

Widgets

SheetBot provides a variety of customizable widgets for displaying data in sheet cells. Widgets determine how data is rendered and interacted with in the web interface.

You can preview all available widgets by visiting the ‘Help’ page in SheetBot’s web interface, which includes a comprehensive widget gallery with examples.

Setting Up Widgets

Widgets are configured in a sheet’s columnstructure table. See Customizing your sheet for a worked example. Each column specifies:

• widgettype: The widget name (e.g., “text”, “image”)
• minwidth/maxwidth: Width constraints
• minheight/maxheight: Height constraints
• columnorder: Display order
• datatype: Data type hint

For multi-widgets, widgettype can be an array like ["multi", "text", "code"].

UI Interaction

Widgets are displayed in an HTML table interface that supports advanced interaction:

• Navigation: Use arrow keys to navigate between cells
• Selection: Click and drag to select multiple cells, or hold Shift while clicking to select ranges
• Copying: Press Ctrl+C to copy selected cells to clipboard. Copies the underlying data as plain text (tab-separated) or HTML table format, not the visual representation
• Multi-widgets: Right-click on cells with multiple widgets to access a context menu for switching between widget views (e.g., switching a script column from hash image to text display)

Text Widgets

Basic text display and formatting widgets.

• text: Plain text display
• text-centered: Centered text display

Code & Data Widgets

Widgets for displaying code, JSON data, and structured information.

• code: Syntax-highlighted code blocks
• json-viewer: Interactive JSON viewer with collapsible structure

Media & File Widgets

Widgets for images, downloads, and file-related content.

• image: Image rendering with size constraints
• download: Downloadable file links
• hashimg: Images generated from hash values
• video: Video player for media URLs
• audio: Audio player for media URLs

Task & Status Widgets

Widgets for displaying task information and status indicators.

• taskstatus: Task status display (AWAITING, RUNNING, etc.)
• taskid: Task ID display
• taskname: Task name display
• tasktype: Task type display

Time & Progress Widgets

Widgets for time display, progress tracking, and duration formatting.

• timestamp: Unix timestamp to human-readable time
• relative-timestamp: Time relative to now (e.g., “2 hours ago”)
• progress: Progress bar (0-100)
• duration: Duration in seconds to formatted time
• metric: Numeric metric with unit and label

Rating & Quality Widgets

Widgets for ratings, test results, and quality indicators.

• rating: Star rating display (0-5)
• testresult: Test result indicator (PASS/FAIL)

Schema & Validation Widgets

Widgets for JSON Schema display, validation, and explanation.

• jsonschema: JSON Schema visualization
• jsonschema-validation: Schema validation results
• jsonschema-explanation: Schema documentation

Interactive & Utility Widgets

Interactive buttons, multi-widgets, utility components, and QR codes.

• sheetkey: Sheet key display
• schedule-task: Button to schedule tasks
• multi: Multiple widgets in one cell (specify array of widget types)
• transitions: Task transitions display
• qr-code: QR code generation from text
• attachments: Links to task attachments/artefacts

Creating Custom Widgets

To create a custom widget:

1. Add widget-<name>.js in static/ or a directory listed in SHEETBOT_STATIC_SEARCH_PATHS.
2. Import Lit (or use a plain custom element) and define a class, normally extending LitElement.
3. Read the cell value from the data property. rowkey and column are also set.
4. Implement getCopyText(); it is used by copy operations. getCopyHTML() and getContextMenuDefinition() are optional.
5. Register with customElements.define('widget-<name>', WidgetClass).
6. Set widgettype = '<name>' in columnstructure.

Widgets are discovered automatically by /widgets.json and imported by sheet.html; you do not need to edit sheet.html. The list is cached for SHEETBOT_WIDGET_CACHE_TTL minutes (default 5); set it to 0 while developing.

See Building a custom widget for a complete example, and existing widgets in static/ for more patterns.

Library Script Annotations

This document describes the XML-like annotations used in SheetBot library scripts to provide metadata for task creation and agent matching.

Overview

Library scripts in the library/ directory can optionally use comment-embedded XML-like annotations to define task metadata. These annotations are parsed by SheetBot’s library handler and the addtask.ts script to prefill information when creating tasks, saving users from having to manually enter common configuration details.

Annotation Types

<name>

Provides a suggested name for tasks created from this script.

Example:

/*
Suggested name for this task: <name>Godot compile</name>
*/

<capabilitiesSchema>

Defines the JSON Schema that agents must match to execute this task. This enables capability-based task routing.

Example with build requirements:

/*
Suggested capabilitiesSchema for this task: <capabilitiesSchema>
{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "type": "object",
  "properties": {
    "software": {
      "type": "object",
      "properties": {
        "clang": {
          "type": "object",
          "properties": {
            "major_version": { "type": "number", "minimum": 6 }
          },
          "required": ["major_version"]
        },
        "deno": {
          "type": "object",
          "properties": {
            "major_version": { "type": "number", "maximum": 1 }
          },
          "required": ["major_version"]
        }
      },
      "required": ["clang", "deno"]
    },
    "memory": {
      "type": "object",
      "properties": {
        "free": { "type": "number", "minimum": 500 }
      },
      "required": ["free"]
    },
    "loadavg": {
      "type": "object",
      "properties": {
        "5min": { "type": "number", "exclusiveMaximum": 0.7 }
      },
      "required": ["5min"]
    }
  },
  "required": ["memory", "loadavg", "software"]
}
</capabilitiesSchema>
*/

<data>

Provides suggested default data for tasks created from this script.

Example:

/*
Suggested data for this task: <data>
{
  "branch": "4.3-stable",
  "additional_build_flags": ""
}
</data>
*/

<addTaskComments>

Provides additional comments or instructions displayed when adding a task.

Example:

/*
AddTaskComments: <addTaskComments>
If you want to build a release template, use "additional_build_flags": "target=template_release"
</addTaskComments>
*/

<transitions>

Provides suggested task transitions for automatic state management. Transitions define how tasks should move between states based on conditions.

Example:

/*
Suggested transitions for this task: <transitions>
[
  {
    "statuses": ["COMPLETED"],
    "condition": {},
    "timing": { "immediate": true },
    "transitionTo": "DELETED"
  }
]
</transitions>
*/

How It Works

Parsing

Annotations are parsed using simple string extraction from script source code:

• Location: Can appear anywhere in comments
• Format: XML-like tags with <tag> and </tag> delimiters
• Content: JSON for <capabilitiesSchema> and <data>, plain text for others
• Comment Filtering: Shell script # prefixes are stripped before JSON parsing

Task Creation Flow

When using addtask.ts:

1. Script annotations are parsed (if present)
2. <name> becomes the default task name
3. <data> pre-fills the JSON data field
4. <addTaskComments> displays helpful guidance
5. <transitions> pre-fills suggested transitions
6. <capabilitiesSchema> validates agent compatibility

Library API

The /library endpoint returns parsed metadata:

{
  "filename": "godot-compile.ts",
  "name": "Godot compile",
  "capabilitiesSchema": { /* parsed JSON */ },
  "suggestedData": { /* parsed JSON */ },
  "comments": "If you want to build a release template...",
  "transitions": [ /* parsed JSON array */ ]
}

Best Practices

While all annotations are optional:

1. Include <name> for clear task identification
2. Define <capabilitiesSchema> to ensure tasks run on suitable agents
3. Provide <data> with sensible defaults
4. Use <addTaskComments> for special requirements
5. Specify <transitions> for automatic task lifecycle management
6. Validate JSON in <capabilitiesSchema>, <data>, and <transitions>
7. Keep comments concise for CLI display

Optionality

All annotations are optional. Scripts without them work perfectly - users just enter details manually. Annotations provide convenience by reducing repetitive data entry.

Sheet Database Table Structure

Sheets in SheetBot are stored as individual SQLite database files in the ./sheets/ directory. Each sheet contains several tables and views that manage data storage and presentation.

For a walkthrough, see Working with sheets and Customizing your sheet.

Core Tables

data Table

The main data storage table. Structure:

• key TEXT PRIMARY KEY - Unique identifier for each row

Additional columns are added dynamically as data is inserted. Column types are inferred automatically:

• Objects and booleans → TEXT (JSON-encoded)
• Numbers → INTEGER or REAL
• Strings → TEXT

Example schema after some inserts:

CREATE TABLE "data" (
    key TEXT PRIMARY KEY,
    name TEXT,
    age INTEGER,
    active TEXT,  -- JSON-encoded boolean
    metadata TEXT -- JSON-encoded object
);

columnstructure Table

Defines metadata for each column in the data table, controlling how data is displayed in the web interface.

Columns:

• name TEXT PRIMARY KEY - Column name (must match a column in the data table)
• widgettype TEXT - Widget type for rendering (see Widgets documentation)
• minwidth INTEGER - Minimum width in pixels
• maxwidth INTEGER - Maximum width in pixels
• minheight INTEGER - Minimum height in pixels
• maxheight INTEGER - Maximum height in pixels
• columnorder INTEGER UNIQUE - Display order (lower numbers appear first)

Example:

INSERT INTO "columnstructure" (name, widgettype, minwidth, maxwidth, minheight, maxheight, columnorder)
VALUES ('name', 'text', 100, 300, 50, 100, 1);

Views

data_view View

A customizable view of the data table. Initially defined as:

CREATE VIEW "data_view" AS SELECT * FROM "data";

Users can modify this view to transform data before display (e.g., add computed columns, filter rows).

columnstructure_view View

Combines column structure metadata with actual table schema information:

CREATE VIEW "columnstructure_view" AS
SELECT p.name, c.widgettype, c.minwidth, c.maxwidth, c.minheight, c.maxheight,
       (ROW_NUMBER() OVER(ORDER BY c.columnorder)) - 1 AS columnorder,
       p.type as 'datatype'
FROM "columnstructure" c
JOIN pragma_table_info('data_view') p ON p.name LIKE c.name
ORDER BY c.columnorder ASC;

This view provides:

• Column metadata from columnstructure
• Actual SQLite datatype from table schema
• Zero-based column ordering for the UI

Default Initialization

When a new sheet is created:

1. data table is created with only the key column
2. data_view is created as SELECT * FROM "data"
3. columnstructure table is created
4. A default entry for the key column is inserted with widgettype = 'text'
5. columnstructure_view is created

Dynamic Schema

Sheets support dynamic schema evolution:

• New columns are added automatically when data with unknown fields is inserted
• Column types are inferred from the data values
• The columnstructure table can be updated to customize display without changing the data schema

This allows flexible data storage while maintaining structured presentation through the columnstructure metadata.

Explanation

Understanding-oriented pages about how SheetBot works and why it is designed this way.

• Architecture — the server, storage, agents, and UI at a glance.
• Agent workflow — how agents poll, accept, execute, and report tasks.
• Distributed promise runtime — the experimental TypeScript runtime for distributed DAGs.
• Extensibility — the external and code-level extension points.

Architecture

SheetBot keeps the server small and pushes execution and visualization outward. The server owns task state and matching; agents execute opaque scripts; the browser renders sheets and widgets.

Components

• HTTP server — an Express app that exposes the task, sheet, artefact, library, script, and widget endpoints. It also serves the web UI.
• SQLite storage — tasks.db for tasks and transitions, users.db for users and permissions, and one database per sheet.
• Agents — external processes that poll for tasks matching their type and capabilities. Agents can be written in Deno, Node.js, Python, Bash, or a custom runtime.
• Web UI — Lit-based Web Components that render sheets, task tables, and widgets in the browser.
• Artefacts — files stored under artefacts/ and exposed through task endpoints and an S3-compatible API.
• Initialization and extensions — startup scripts, library scripts, middleware, event handlers, and additional static/search paths let you extend a deployment without forking the server.

Task flow

1. A user or script creates a task with a script, type, data, capabilities schema, and optional transitions.
2. An agent polls /tasks/get with its type and capabilities.
3. The server matches the task and returns its script URL.
4. The agent accepts the task, runs the script, and reports completion or failure.
5. The server updates the task, emits events, and evaluates transitions.

See Protocol for the exact sequence and state machine, and Agent workflow for the agent side.

Design choices

• Opaque scripts — SheetBot does not parse task scripts. Runners decide how to execute them.
• JSON Schema matching — capabilities are compared with a task’s schema, so routing is flexible and language-agnostic.
• SQLite first — tasks, users, and sheets work without an external database service.
• Browser-side rendering — sheets and widgets are rendered in the viewer, keeping server compute low.
• Configuration as code — init scripts, library scripts, middleware, and event handlers are loaded from directories or environment variables.

Where to go next

• Extensibility — choose an extension point.
• Agent runtime API — implement a custom runner.
• Sheet database — understand the storage layout.
• Deploy SheetBot — run it as a service and back it up.
• Debug a failed task — diagnose task and agent failures.

Agent Workflow

Agents poll for tasks based on their type and capabilities. SheetBot provides templates for common runners:

• Deno Runner: For TypeScript tasks (type: "deno")
  • Template: /scripts/agent.ts
  • Executes scripts in the Deno runtime
• Node.js Runner: For JavaScript and TypeScript tasks
  • Template: /scripts/agent.js
  • JavaScript task types: "node", "javascript", "js"
  • TypeScript task types: "typescript", "ts"
  • Requires Node.js 22.6 or newer. TypeScript tasks run with --experimental-sqlite --experimental-strip-types
• Python Runner: For Python tasks (type: "python")
  • Template: /scripts/agent.py
  • Executes scripts in a Python environment
• Bash Runner: For shell script tasks (type: "bash")
  • Template: /scripts/agent.sh
  • Executes scripts in Bash. Requires curl and jq to be installed.

Workflow:

1. Poll /tasks/get with agent type and capabilities
2. Receive task script URL if available
3. Accept task via /tasks/:id/accept
4. Execute script (dependencies injected as __DEP_RESULT_<taskId>__)
5. Report completion via /tasks/:id/complete with result data

Node.js tasks are executed as temporary files. Node’s strip-only TypeScript mode does not support network imports, so Node task scripts must be self-contained or use fetch for remote data.

See Setting up other agents for Deno, Python, and Bash agent commands, and Agent Runtime Implementation for details on implementing custom runners.

Runner implementations are flexible; SheetBot only requires adherence to the polling and reporting protocol.

Distributed Promise Runtime

Overview

SheetBot includes a distributed promise runtime that allows writing natural async JavaScript/TypeScript code for orchestrating distributed tasks. This enables patterns like:

const result = await Promise.all([
  compile("windows"),
  compile("linux")
]).then(files => zip(files)).then(upload);

The runtime automatically infers and executes a DAG of tasks across agents.

Core Components

RemoteTask Class

Extends Promise to represent distributed computations:

class RemoteTask<T> extends Promise<T> {
  id: string;
  deps: RemoteTask<any>[];
  schema?: JSONSchema;

  constructor(
    run: () => Promise<T>,
    deps: RemoteTask<any>[] = [],
    schema?: JSONSchema
  ) {
    // Registers with runtime, handles resolution
  }
}

Distributed Function Decorator

Marks functions as distributed:

function distributed<T extends any[], R>(
  fn: (...args: T) => Promise<R>,
  schema?: JSONSchema
) {
  return (...args: T): RemoteTask<R> => {
    const deps = args.filter(arg => arg instanceof RemoteTask);
    return new RemoteTask(() => fn(...args), deps, schema);
  };
}

Runtime Orchestrator

Manages task registration, DAG building, and execution:

class Runtime {
  static execute(rootTask: RemoteTask<any>) {
    const dag = buildDAG(rootTask);
    // Topological execution across agents
  }
}

Usage

// Define distributed functions
const compile = distributed(
  async (src: string) => runCompileJob(src),
  { properties: { os: { const: "linux" } } } // capabilities JSON Schema
);
const zip = distributed(async (...files) => zipFiles(files));
const upload = distributed(async (file) => uploadFile(file));

// Write natural async code
(async () => {
  const [libfoo, libbar] = await Promise.all([
    compile("libfoo.cpp"),
    compile("libbar.cpp")
  ]);
  const result = await upload(await zip(libfoo, libbar));

  console.log("Final result:", result);
})();

Integration

• Schema Matching: Leverages SheetBot’s capability matching
• Task Dispatch: Uses /tasks/get endpoint
• Result Storage: Central KV store for intermediate results
• Dependency Resolution: Runtime waits for deps before dispatching

Features

• Caching: Avoids re-execution of completed tasks
• Retries: Auto-retry failed tasks on different agents
• Monitoring: DAG visualization (planned)
• Type Safety: Full TypeScript support

This runtime makes distributed computing feel like local async code.

Programmability and Extensibility

SheetBot is designed for easy extension through code modification and external configuration. Key extension points:

External Extension Points (No Code Modification Required)

• Library Scripts (SHEETBOT_LIBRARY_SEARCH_PATH): Add custom task scripts from external directories (see Library Search Paths)
• Event Handlers (SHEETBOT_EVENTHANDLER_SEARCH_PATHS): React to task and agent events with custom handlers (see Event Handlers)
• Init Scripts (SHEETBOT_INIT_SEARCH_PATHS): Run custom initialization code on server startup (see Initialization System)
• Custom Middleware (SHEETBOT_MIDDLEWARE_SEARCH_PATHS): Add Express middleware for authentication, logging, CORS, etc. (see Custom Middleware)
• Additional Static Files (SHEETBOT_STATIC_SEARCH_PATHS): Serve extra static assets (e.g. custom widgets) from external directories alongside ./static (see Static Search Paths)
• Additional Scripts (SHEETBOT_SCRIPTS_SEARCH_PATHS): Serve extra agent-facing scripts at /scripts from external directories alongside ./scripts (see Scripts Search Paths)
• Widget Auto-loading (SHEETBOT_WIDGET_CACHE_TTL): The /widgets.json endpoint dynamically discovers all widget-*.js files across all static directories, and sheet.html imports them as modules. The result is cached for this many minutes (default: 5). Set to 0 to disable caching. See Building a custom widget.

Code Modification Extension Points

• Authentication: Add custom middleware to main.ts for auth schemes (e.g., OAuth, LDAP)
• New Endpoints: Extend the Express app with additional routes
• Data Providers: Implement custom storage backends by extending data provider classes
• Widgets: Add new widget types (see Widgets)
• Sheet Views: Customize data presentation with widgets and column structures (see Sheet Database Table Structure)
• Custom Runners: Implement new execution environments (see Agent Runtime Implementation)
• Capabilities: Customize agent matching logic (see Capabilities)
• Distributed Runtime: Enable distributed task execution (see Distributed Runtime)

The codebase is structured for straightforward modifications. For most use cases, the external extension points provide sufficient flexibility without requiring code changes.

Forking vs Extension Points

Use external extension points when:

• You want to stay on the main SheetBot release track
• You want to receive upstream updates and bug fixes easily
• Your customizations are self-contained (event handlers, middleware, init scripts)
• You prefer configuration over code modification

Fork SheetBot when:

• You need deep integration or architectural changes
• External extension points don’t provide sufficient flexibility
• You’re comfortable maintaining your own version

Implications of forking:

• You become responsible for merging upstream changes
• You maintain your own testing and deployment pipeline
• Bug fixes and features from upstream require manual integration
• You have full control over the codebase

For most users, the external extension points (SHEETBOT_LIBRARY_SEARCH_PATH, SHEETBOT_EVENTHANDLER_SEARCH_PATHS, SHEETBOT_INIT_SEARCH_PATHS, SHEETBOT_MIDDLEWARE_SEARCH_PATHS, SHEETBOT_STATIC_SEARCH_PATHS, SHEETBOT_SCRIPTS_SEARCH_PATHS) provide sufficient customization without the maintenance burden of a fork.

Important: SheetBot is provided AS IS without warranty. The maintainer makes no guarantees about backward compatibility, API stability, or continued support for any extension points or internal APIs. Extension points may change or be removed in future versions. Use at your own risk and be prepared to adapt your customizations as the project evolves.