Jaz Pseudo SQL

Jaz Pseudo-SQL Skill

You are running ad-hoc data queries against the curated reporting schema in Jaz — a read-only SQL DSL exposed via the Jaz API at /api/v1/reports/sql-query/*. Use this skill when:

• The user asks a custom analytical question that doesn't match any download_export(exportType=...) canonical report.
• The user wants a specific CSV slice ("invoices over $5k issued this quarter that are still unpaid") that the structured search_* tools can't express cleanly.
• The user wants to JOIN, GROUP BY, or aggregate across multiple tables in one query.

NOT a general-purpose database surface. Curated schema only — no DML (DELETE/UPDATE/INSERT), no multi-statement input, no access to private columns. Validators reject anything that isn't a single SELECT against an allowed table. See references/error-catalog.md for the full error vocabulary.

Source of truth for the schema

Call get_pseudo_sql_schema first for the live curated catalog (~70 tables, 91 join edges, 47 functions). Pass table to scope it to one table and its joins — the whole catalog is ~18k tokens, one table is ~1k.

The syntax guide is a SEPARATE call, get_pseudo_sql_syntax, returning the canonical jaz-pseudo-sql.md body in agentSkillsDoc.content. It is static — identical for every organization — so fetch it once, cache on version, and skip it entirely if you already have this skill loaded. The two were one response until 2026-09-03; combined they cost ~35k tokens on a call documented as "before any query", of which 42% was the unchanging guide.

The version field is a stable 16-char hex hash; cache by it. If you've already called the tool this session and the version is unchanged on a re-call, the schema and skill body are identical to your cached copy — no need to re-read.

Don't write a pseudo-SQL query from memory. The catalog grows; column names change; the live schema is the only source you should trust.

When NOT to use this skill

Tool selection within pseudo-SQL

• get_pseudo_sql_schema — call FIRST. Returns the live curated catalog (tables/columns/joins/functions). Optional table scopes it to one table plus the joins touching it. Use the response's version (16-char hex) as a session-stable cache key. Org-agnostic.
• get_pseudo_sql_syntax — the canonical jaz-pseudo-sql.md body in agentSkillsDoc.content. Static across organizations; fetch once, cache on version, or skip it if this skill is already in context.
• preview_pseudo_sql — sync, ≤100 rows. Use for any agent-loop question where you need to look at the data quickly.
• export_pseudo_sql + get_pseudo_sql_export — async kickoff + polling. Use when you want explicit job control (manual retry, parallel jobs, polling at your own cadence) or when the result set is too big for preview's 100-row cap.
• run_pseudo_sql_and_download — one-shot composite: kickoff + poll + fetch CSV. Use for "give me the file" flows. Default returns the CSV buffer; pass downloadToFile=true to write to ~/Downloads/.

DSL rules (load-bearing)

1. SELECT only. DELETE/UPDATE/INSERT → 422 PSEUDOSQL_VALIDATION_ERROR "only SELECT queries are supported".
2. Single statement. SELECT 1; SELECT 2; → 422 PSEUDOSQL_PARSE_ERROR "only a single SELECT statement is allowed per query". A trailing semicolon on one statement is fine.
3. Must SELECT FROM at least one table. SELECT 1 (no FROM) → 422 PSEUDOSQL_VALIDATION_ERROR.
4. Max 16,384 characters. Over → 422 validation_error "query must be a maximum of 16,384 characters in length". Note: this is the request-shape validator (different error_type from the SQL-engine validators).
5. Curated tables only. Unknown table → 422 PSEUDOSQL_VALIDATION_ERROR "unknown table " (lowercased in the error message). Call get_pseudo_sql_schema for the live inventory.
6. Preview cap is 100 rows. truncated:true means "MORE rows matched than were returned in this preview" — NOT "you hit the cap". To interpret: compare rowCount against your LIMIT clause or the preview cap (100). If you need every row, switch to export_pseudo_sql.
7. Export downloadUrl is short-lived. S3 pre-signed, ~15min expiry (X-Amz-Expires=900). Fetch immediately. If a fetch returns 403, call get_pseudo_sql_export(jobId) again for a fresh URL.
8. Idempotency-Key dedups server-side. Same key + DIFFERENT query body returns the prior job's result (the server doesn't cross-check). run_pseudo_sql_and_download auto-keys from sha256(query).slice(0,16) so dedup is query-tied automatically. If you call export_pseudo_sql directly with a manual key, don't reuse it across different intents.

Reference docs

• Schema inventory — call get_pseudo_sql_schema (live; ~73 KB for the whole catalog, ~5 KB with a table filter). The syntax guide is get_pseudo_sql_syntax, separately.
• Query patterns — example SELECTs by user intent (top customers, unpaid invoices, FX-exposed bills, etc.).
• Error catalog — every observed error code + recovery action.

Quick example — preview an ad-hoc query

Agent intent: "show me the 10 largest unpaid invoices"

preview_pseudo_sql({
  query: `
    SELECT invoice_number, total, balance, contact_id, due_date
    FROM invoices
    WHERE balance > 0
    ORDER BY balance DESC
    LIMIT 10
  `
})
→ { data: { columns: [...], rows: [...], rowCount: 10, truncated: false } }