Permission-Gated Tool-Use: How Datasette Agent 0.3a0 Handles User Approval for Write Operations

The Problem with Agent-Driven Writes

Agents that can read data are useful. Agents that can write data are dangerous.

The core tension is straightforward: an LLM can decide to call a write tool at any point in a conversation, and once the tool executes, the damage is done. Wrong row updated. Accidental DELETE. INSERT with hallucinated values. A malicious prompt injection turning a benign "update my email" into "DROP TABLE users."

The standard mitigations — restrictive system prompts, input validation, output filtering — all leak. A determined prompt injection bypasses system instructions. Schema validation can't tell a legitimate UPDATE from a destructive one because both have valid SQL syntax.

The reliable solution is human approval at the point of execution, gated behind the same permission system that protects the non-agent interface. That's exactly what datasette-agent 0.3a0 implements with its execute_write_sql tool.

The Architecture in One Diagram

User query → LLM decides to write → Datasette permission check
                                           │
                                    ┌──────┴──────┐
                                    │ Has execute- │
                                    │ write-sql?   │
                                    └──────┬──────┘
                                           │
                              ❌ No → tool filtered from model's view
                                           │
                              ✅ Yes → SQL analysis → Permission audit
                                           │
                                      ask_user("Approve this write?")
                                           │
                              ❌ No → tool returns cancelled
                                           │
                              ✅ Yes → Execute via /-/execute-write
                                           │
                                    Result back to LLM

The critical design decision: the permission check happens before the tool is even shown to the model. If the user lacks execute-write-sql, the agent never sees the tool. No "model tried to call a forbidden tool" error handling needed.

The Foundation: Datasette's Permission System

Before the agent gets involved, Datasette 1.0a31 introduced the execute-write-sql permission. This is a first-class permission in Datasette's SQL-powered permissions system:

• execute-write-sql — required to execute any write SQL against a database
• Table-level gates — each operation also requires its specific permission: insert-row, update-row, delete-row, create-table, alter-table, drop-table
• Read gates for write queries — INSERT INTO ... SELECT ... requires view-table on the source table
• Deny-by-default — unsupported operations (VACUUM, writes to virtual tables, shadow tables) are rejected

Permissions are checked via /-/execute-write, the same endpoint the web UI uses. The agent calls it as the requesting actor, so permission rules are identical whether the SQL comes from the agent or from a human pasting SQL into the form.

The ask_user() Mechanism

Datasette-agent 0.2a0 introduced ask_user(), a mechanism that lets a tool pause mid-execution and ask the human user a question. The tool doesn't block — it suspends. The question renders as a form in the chat UI, persists to the internal database (surviving server restarts), and when answered, the tool function re-executes from the top.

Three question types:

# Yes/no — returns bool
await context.ask_user("Approve this database write?")

# Multiple choice — returns selected str
await context.ask_user("Which database?", options=["production", "staging"])

# Free text — returns str
await context.ask_user("Describe the change", free_text=True)

The key behavioral detail: call ask_user() before performing side effects. When the user answers, the tool re-executes from the top. Previously answered questions return stored answers immediately. If you called ask_user() after writing data, the write would happen again on replay.

The execute_write_sql Tool Walkthrough

Here's what happens step by step when the agent decides to write to a database.

Step 1: Tool Registration and Permission Filtering

execute_write_sql is registered as a built-in tool with required_permission="execute-write-sql". When the agent loads its tool list for a conversation, it calls datasette.allowed(action="execute-write-sql", actor=actor). If the actor lacks the permission, the tool is filtered out — the model never sees it in its tool list.

# Pseudocode — how permission-based tool filtering works
tools = get_all_registered_tools()
visible_tools = []
for tool in tools:
    if tool.required_permission:
        allowed = await datasette.allowed(
            action=tool.required_permission,
            actor=actor,
        )
        if not allowed:
            continue  # Skip this tool, model won't see it
    visible_tools.append(tool)

This is the first gate. It's clean because it requires zero error-handling logic in the tool itself.

Step 2: The LLM Calls execute_write_sql

The model receives the tool definition with its JSON Schema:

{
  "name": "execute_write_sql",
  "description": "Execute write SQL statements against a mutable database",
  "parameters": {
    "type": "object",
    "properties": {
      "database": {
        "type": "string",
        "description": "The name of the database to write to"
      },
      "sql": {
        "type": "string",
        "description": "One or more write SQL statements to execute, separated by semicolons"
      }
    },
    "required": ["database", "sql"]
  }
}

The model generates a tool call with specific arguments. This is the only path to writing data — there's no hidden write capability the model can discover.

Step 3: SQL Analysis

Before showing anything to the user, the tool analyzes the SQL to determine what operations it would perform and what permissions are needed:

1. Parse the SQL — identify whether it's INSERT, UPDATE, DELETE, CREATE TABLE, ALTER TABLE, or DROP TABLE
2. Check table-level permissions — verify the actor has insert-row / update-row / delete-row / etc. for the affected tables
3. Warn on destructive operations — flag DELETE without WHERE, DROP TABLE, and ALTER operations

Step 4: User Approval via ask_user()

The tool constructs an approval prompt showing the user exactly what will happen:

import html

# Build the approval detail
detail = f"""
<pre>Database: {html.escape(database)}
SQL: {html.escape(sql)}

Required permissions: execute-write-sql on {html.escape(database)}
Operations: INSERT into dogs, UPDATE dogs set name
Destructive: Yes — contains DELETE without WHERE clause
</pre>
"""

approved = await context.ask_user(
    "Execute this write SQL?",
    html=detail,     # Displayed in web chat UI
    text=f"Database: {database}\nSQL: {sql}\nApprove?",  # Displayed in CLI
)

The approval prompt is rendered as a form in the chat UI with the SQL shown inside a <pre> block. The user clicks Yes or No. If the user says No, the tool returns {"cancelled": True} and the model can explain why it couldn't proceed.

If the chat is running in the CLI (via datasette agent chat), the text= parameter provides a plain-text alternative. The CLI also supports --yes to auto-approve all prompts and --root to run with full permissions.

Step 5: Execution

On approval, the tool sends the SQL to Datasette's /-/execute-write endpoint as the requesting actor:

# Execute the write through Datasette's own endpoint
response = await datasette.client.post(
    f"/{database}/-/execute-write",
    json={"sql": sql},
    actor=actor,
)

The endpoint re-checks permissions, executes the SQL in a transaction, and returns the result. Multiple statements execute in order; if one fails, later statements are skipped and earlier successes are not rolled back — the tool returns the success/failure details for each statement.

Step 6: Result Back to the LLM

{
  "results": [
    {
      "statement": "INSERT INTO dogs (name, breed) VALUES ('Cleo', 'Mixed')",
      "success": true,
      "rows_affected": 1,
      "last_row_id": 42
    }
  ],
  "database": "pets",
  "sql": "INSERT INTO dogs (name, breed) VALUES ('Cleo', 'Mixed')"
}

The model sees the result and can describe what happened to the user.

Building Your Own Gated Tool

The same pattern works for any plugin that registers custom tools. Here's a complete example:

1. Define the Permission Action

from datasette import hookimpl
from datasette.permissions import Action

@hookimpl
def register_actions():
    return [
        Action(
            name="myplugin-email-users",
            description="Allow sending emails to users via the agent",
        ),
    ]

2. Register the Gated Tool

from datasette import hookimpl
from datasette_agent.tools import AgentTool
import json

async def send_email_handler(datasette, actor, context, user_id, subject, body):
    # Ask the user for approval before sending
    ok = await context.ask_user(
        f"Send email to user {user_id} with subject '{subject}'?",
        options=["approve", "cancel"],
    )
    if ok != "approve":
        return json.dumps({"cancelled": True})

    # Execute the send
    # ... email sending logic here ...

    return json.dumps({"sent": True, "user_id": user_id})

@hookimpl
def register_agent_tools(datasette):
    return [
        AgentTool(
            name="send_email",
            description="Send an email to a user. Requires approval before sending.",
            input_schema={
                "type": "object",
                "properties": {
                    "user_id": {
                        "type": "integer",
                        "description": "The user ID to send to",
                    },
                    "subject": {
                        "type": "string",
                        "description": "Email subject line",
                    },
                    "body": {
                        "type": "string",
                        "description": "Email body text",
                    },
                },
                "required": ["user_id", "subject", "body"],
            },
            fn=send_email_handler,
            required_permission="myplugin-email-users",
        ),
    ]

3. Configure Permissions

In your datasette.yml, grant the permission to specific actors:

permissions:
  - action: myplugin-email-users
    actor:
      id: admin
  - action: myplugin-email-users
    actor:
      id: support-team-member

Permission Gating vs. Approval Gating

Datasette Agent supports two independent gating mechanisms. They work best together:

The first gate prevents the model from even attempting forbidden operations. The second gate forces the human to confirm before anything happens. Together they handle both the "model has too much power" and "model was tricked into using its power" scenarios.

The Tool Handler Signature

A well-structured tool handler accepts three kinds of parameters:

async def my_tool_handler(
    datasette,    # Datasette instance — for DB access, permissions, HTTP
    actor,        # Current authenticated actor dict
    context,      # Tool context — ask_user(), actor, conversation_id, tool_name
    # ... your tool-specific parameters from input_schema ...
) -> str:         # Always returns a JSON string

The context object exposes:

The datasette-agent-edit Pattern

The same gating architecture powers datasette-agent-edit 0.1a0, a companion plugin that provides file-editing tools (view / str_replace / insert / batch edit) for Datasette Agent.

What makes it interesting architecturally is the three-layer isolation:

1. Operations layer — pure string surgery. view_lines, str_replace, insert, apply_edits. No I/O, no Datasette, no await. This is pure, testable string manipulation.
2. EditStore layer — the storage backend. The defining method is edit(ref, transform): read current content → run pure transform atomically → persist. Implementations exist for SQLite (SqliteVersionedStore), local disk (DiskStore), and the same interface fits S3 (If-Match) and GitHub (sha) backends.
3. EditToolset layer — turns any EditStore into AgentTool instances. An id_codec maps internal refs to model-visible identifiers. A render hook optionally injects HTML previews.

The key insight: the transform is synchronous and pure. It sits between the backend's awaits, never inside them. The SQLite backend runs transforms on its write thread (which can't await), and S3/GitHub backends must not re-run network calls on retry. If a transform decision needs async work, resolve it first and close over the result:

resolved = await registry.lookup(name)
await store.edit(ref, lambda c: rewrite(c, resolved))

CLI Mode and Approval Flow

The datasette agent chat CLI handles the approval flow differently from the web UI:

# Interactive CLI — prompts for every ask_user()
datasette agent chat mydata.db

# CLI with auto-approve — skips yes/no prompts
datasette agent chat mydata.db --yes

# CLI as root actor — bypasses permission checks
datasette agent chat mydata.db --root

# Both — full power, no approval
datasette agent chat mydata.db --unsafe

The CLI renders ask_user() prompts as terminal questions. The text= parameter is used when available; if only html= was provided, the HTML is printed directly. This makes the same tools work identically in both web and terminal contexts — a design decision worth noting for any tool you build.

Key Design Patterns

Looking at the full architecture, four patterns stand out as reusable across any agent tool system:

1. Permission-first tool visibility. Filter tools before the model sees them. This is simpler and safer than filtering at execution time. The required_permission pattern on AgentTool is a clean implementation.

2. Approval before side effects. ask_user() re-executes the tool from the top on answer. If your tool calls ask_user() after performing a side effect, that side effect happens twice. The rule: ask before you act.

3. Reuse existing permission infrastructure. The execute_write_sql tool calls the same /-/execute-write endpoint that the web UI uses. No new permission logic. No risk of the agent bypassing a restriction the human user can't bypass.

4. Pure transforms for storage-agnostic editing. The datasette-agent-edit pattern of separating pure string operations from storage backends makes tools testable and backend-independent. Your transform logic doesn't need to know whether it's running against SQLite, S3, or GitHub.

What's Next

• datasette-agent — the plugin itself. Install it, configure a model, and try the CLI.
• datasette-agent-edit — file-editing tools following the same gating pattern.
• datasette-agent-charts — chart-rendering tools that use the _html return pattern for rich inline output.
• datasette-agent-micropython — MicroPython in a WASM sandbox as an agent tool, demonstrating the sandbox pattern for untrusted code execution.

For a deeper look at the permission system itself, read the Datasette 1.0a31 changelog and the SQL write queries blog post.