# SQL-tool YAML — authoring & validation

> Custom SQL tools are validated against the platform's `sql-tools-config` schema (snapshot at [`../assets/sql-tools-config.schema.json`](../assets/sql-tools-config.schema.json)) — the same draft-07 schema the in-app builder uses — by the **server**, when you `ixora agents create`. **Treat the validator as the source of truth:** write a draft, attach it, fix what `create` reports. This page is the shape and the non-obvious gotchas it catches.

A custom-tool agent's tools are named, parameterized, **read-only** statements the agent calls by name. The agent always also has the fixed IBM i CLI toolkit (`validate_and_run_sql`, `list_schemas`, …), so custom tools are for the *domain* queries. Your **named** tools run via `run_tool` (no confirmation pause); only the raw `validate_and_run_sql` / `run_cl` gate.

## Two ways to attach a tool

**Inline in the manifest** — `ibmiTools:` is a **list**, each item a single-key mapping keyed by the tool name:

```yaml
# in <agent-id>.agent.yaml
ibmiTools:
  - active_jobs:
      source: default                # 'default' → the auto-injected sources block
      description: "What it returns and when to use it (the model reads this to pick it)"
      statement: |
        SELECT JOB_NAME, SUBSYSTEM, CPU_TIME
        FROM   TABLE(QSYS2.ACTIVE_JOB_INFO(SUBSYSTEM_LIST_FILTER => :subsystem))
        FETCH FIRST :limit ROWS ONLY
      parameters:                     # a LIST of objects
        - { name: subsystem, type: string,  required: true }
        - { name: limit,     type: integer, default: 50, min: 1, max: 500 }
      security:
        readOnly: true
```

**From a separate file** — a top-level `tools:` **mapping** keyed by tool name, passed with the repeatable `--ibmi-tools <file>`:

```yaml
# active-jobs.tool.yaml
tools:
  active_jobs:
    source: default
    description: Active jobs on the system
    statement: SELECT JOB_NAME FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) X FETCH FIRST :limit ROWS ONLY
    parameters:
      - { name: limit, type: integer, default: 10, min: 1, max: 100 }
    security: { readOnly: true }
```
```bash
ixora agents create -f <agent-id>.agent.yaml --ibmi-tools ./active-jobs.tool.yaml
#   -> Success: Created agent '<agent-id>' (... 1 IBM i tool(s) written)
```

You don't write the top-level `sources:` block — the platform injects a default one (`${DB2i_HOST}` etc., expanded at run time).

## Gotchas the validator catches

- **`tools:` (file form) is a MAPPING keyed by tool name** — `tools:` then `  my_tool:` then its keys; **not** a list of `- name: my_tool` items. (Inline, `ibmiTools:` **is** a list, each item the single-key `tool_name:` mapping.)
- **The SQL field is `statement`** — not `sql`.
- **`source: default` is a REQUIRED per-tool key**, distinct from the top-level `sources:` block you don't write. Every tool also needs a **`description`**.
- **`parameters` is a LIST of objects**, not a mapping; each needs at least `name` + `type`.
- **Numeric bounds are `min`/`max`** — not `minimum`/`maximum`.
- **String/array bounds are `minLength`/`maxLength`** (camelCase — yes, inconsistent with `min`/`max`).
- **`type` is lowercase, one of `string|boolean|integer|float|array`.** `number` is invalid → use `integer`/`float`. `type: array` needs `itemType`.
- **`default` must match the declared type** (`50`, not `"50"`).
- **SQL placeholders are `:param_name`** and must match a declared parameter.

## Validate before you create

Neither the schema check nor `ibmi validate` **executes** the SQL, so prove each tool in this order:

```bash
uv run "$AB" ibmi --instance <id> -- validate "<the tool's exact statement>"   # syntax + objects, no execution
ixora agents create -f <agent-id>.agent.yaml --dry-run                       # resolved spec, no server write
ixora agents create -f <agent-id>.agent.yaml [--ibmi-tools <file>]           # server schema-checks every tool
```

`create` reports a precise path on a bad tool (e.g. `tools.active_jobs: 'source' is a required property`). Parameter-marker / type errors (e.g. Db2 `SQL0418` from a bare `:param` in a select-list or expression) surface **only at run time** — the verify run in [SKILL.md](../SKILL.md#verify) is the real proof, not these checks.

## See also

- [`manifest.md`](manifest.md) — wiring these tools into an agent
- [`introspection.md`](introspection.md) — discover the real schema first, through the container `ibmi`