Writing Queries

Writing Queries

Adding a tool means adding two files. No Python required — the registry discovers them at startup, validates them, and builds a typed MCP tool with the parameters you declared.

1. The SQL

Drop a SELECT into src/teslamate_mcp/queries/your_query.sql:

SELECT c.name AS car_name,
    count(*) AS drives,
    round(sum(d.distance)::numeric, 1) AS total_km
FROM drives d
    JOIN cars c ON d.car_id = c.id
WHERE d.start_date >= now() - make_interval(days => %(days)s::int)
    AND (%(car_name)s::text IS NULL OR c.name ILIKE '%%' || %(car_name)s || '%%')
GROUP BY c.name
ORDER BY total_km DESC
LIMIT %(limit)s::int;

2. The sidecar

Add your_query.toml beside it:

name = "get_your_data"
description = "What this returns, its units, its grouping, and the filters it accepts."

[[params]]                 # optional — declare typed tool arguments
name = "car_name"
type = "string"            # string | integer | number | boolean
description = "Case-insensitive substring match on the car's name. Omit for all cars."

[[params]]
name = "days"
type = "integer"
description = "How many days back to look."
default = 30
minimum = 1
maximum = 3650

[[params]]
name = "limit"
type = "integer"
description = "Maximum number of rows returned."
default = 10
minimum = 1
maximum = 100

[[output]]                 # optional — one table per column, gives the tool a typed outputSchema
name = "car_name"
type = "string"

[[output]]
name = "total_km"
type = "number"
description = "Total distance driven in the window."

3. Restart

The registry picks it up automatically. Confirm with:

teslamate-mcp list-tools

Rules the registry enforces at startup

These fail loudly at boot rather than on the first call:

• Every declared param must appear in the SQL, and vice versa. A typo in either file is caught immediately.
• Never string-interpolate. Reference params as %(name)s placeholders only; psycopg binds them.
• Cast the first occurrence — %(car_name)s::text, %(limit)s::int — so binding NULL works and PostgreSQL can resolve the type.
• Escape literal percent signs as %% in any query that takes parameters.
• Defaults must match their declared type, and must sit inside any minimum/maximum you set.

The reserved tz placeholder

%(tz)s is injected automatically from REPORT_TIMEZONE. Use it for date bucketing so reports follow local midnight:

SELECT date_trunc('day', d.start_date AT TIME ZONE 'UTC' AT TIME ZONE %(tz)s) AS day

Do not declare tz in [[params]] — it is reserved, along with ctx, and the registry supplies it.

Optional filters, not required ones

Prefer the (%(param)s::type IS NULL OR …) idiom over a mandatory WHERE. It keeps zero-argument calls meaningful: the model can ask for the whole picture first and narrow afterwards, which is how these tools actually get used.

Testing

tests/test_tools_e2e.py runs every bundled query against a seeded TeslaMate-shaped PostgreSQL, so a new query is covered the moment you add it. Run the suite with uv run pytest — see Development.