Checking predefined queries

In user language: This article is for workspace admins and developers/operators. It explains what Cadau smart check and fix actually does when you configure predefined queries (query.run) on an HR (or similar) data connection, which real table metadata it uses, and how that relates to a failed tool call in conversation. User-facing “generate an employee portrait” is in Building an employee portrait from chat; Rilong connection parameter notes are in docs/data-connection-templates/caretop-employee-profile/.

Date: 2026-07-12 Related code: backend/internal/datasource/query_defs_review.go, query_def_schema.go, query_def_sql_fix.go, query_run_params.go, backend/internal/api/handlers/workspace_data_sources.go

Takeaway

Cadau smart check and fix for predefined queries is not mere JSON pretty-print or parameter-format validation. It is a per-query pipeline:

Key prerequisite: the admin check API must include data_source_id, so the platform can connect and pull column structure. Otherwise AI can only do format-level fixes and cannot reliably correct column names.

Conversation-side query.run runtime tolerance (promote sibling args into inner params, accept id / empId / emp_id aliases) complements check-and-fix. See §5.

1. Background: why a mechanism, not “just ask AI”

1.1 Typical failures (Rilong / employee portrait)

In a real conversation the agent fired many query.run calls against the rilong connection. Failures clustered in two kinds:

The second kind shows: with only a table-name list, AI cannot know column names. Local JSON/? normalize also cannot fix a wrong column name.

1.2 Design goals

1. Verifiable: prefer the database’s real column metadata, not model guesses.
2. Explainable: each query returns a note of what local and AI steps changed.
3. Degradable: if connect fails or no connection is selected, fall back to format fix and do not block the flow.

2. Entry and permissions

2.1 API

Request body (admin):

{
  "query_defs_json": "[{\"id\":\"eaemp_by_id\",\"name\":\"...\",\"sql\":\"SELECT ... WHERE empId = ?\", \"params\":[{\"name\":\"id\",\"required\":true}]}]",
  "data_source_id": "<workspace data-connection UUID>"
}

• data_source_id is strongly recommended: used to pull database name, table list, and connect to read columns.
• Must be a workspace admin; the platform must have data connections enabled and an LLM configured.

2.2 Response

{
  "query_defs_json": "[...]",
  "note": "[eaemp_by_id] schema-fixed column name: empId→emp_id\n[...]",
  "changed": true,
  "warnings": []
}

The admin writes query_defs_json back to the data-connection config and saves; only then does conversation query.run use the corrected SQL.

3. Pipeline architecture

flowchart TB
    subgraph Input
        A[query_defs_json]
        B[data_source_id]
    end
    subgraph Enrich
        C[database name + ListTables]
        D[QueryDefsReviewSchema connection]
    end
    subgraph PerQuery["Per QueryDef"]
        L1[Local normalize normalizeQueryDefLocal]
        L2[ListColumns for involved tables]
        L3[Local column fix fixQueryDefColumnsFromSchema]
        L4[AI review reviewQueryDefItem]
    end
    subgraph Output
        O[Corrected JSON + note + warnings]
    end
    A --> PerQuery
    B --> Enrich
    Enrich --> L2
    L1 --> L2 --> L3 --> L4 --> O

Core type: QueryDefsReviewInput (query_defs_review.go) adds, on top of QueryDefsJSON, TableNames, DatabaseName:

Schema *QueryDefsReviewSchema // Engine + Conn, used for ListColumns

Handler enrichQueryDefsInputFromDataSource fills those fields when data_source_id is present (workspace_data_sources.go).

4. The three stages

4.1 Stage 1: local normalize (normalizeQueryDefLocal)

No database connection. Deterministic rewrite of one query:

This stage handles placeholders and a few known conventions. It does not read information_schema.

4.2 Stage 2: schema fix (query_def_schema.go)

Condition: QueryDefsReviewInput.Schema != nil (request had a valid data_source_id and could connect).

Steps:

1. ExtractTablesFromSelectSQL: parse table names from SQL FROM / JOIN (adhoc_select.go).
2. ListColumns: for each table, query information_schema.columns (MySQL / PostgreSQL / SQL Server implementations).
3. fixQueryDefColumnsFromSchema: regex-match WHERE … col = ?; if col is not in the column list, try camelCase → snake_case (empId → emp_id) and replace once a canonical name is found in the list.

Example:

-- before
SELECT * FROM rt_emergency_contact WHERE empId = ?

-- column list has emp_id, not empId
-- after
SELECT * FROM rt_emergency_contact WHERE emp_id = ?

The note records: [rt_emergency_contact_by_empid] schema-fixed column name: empId→emp_id.

Column cache: column lists are cached by table name within one check job so the database is not hit repeatedly.

4.3 Stage 3: AI per-item review (reviewQueryDefItem)

The following is assembled into the user payload (buildQueryDefsReviewItemPayload):

Hard requirements in the system prompt (excerpt):

• If real column names of involved tables are provided, SELECT/WHERE fields must match that list; do not invent names.
• Common fixes: empId→emp_id, empName→emp_name (the column list wins).
• If table/column lists are empty, only do JSON/SQL normalize.

AI returns one query_def + note; if JSON is invalid, keep the stage-2 result and write warnings.

5. Relation to query.run runtime tolerance

Check-and-fix is about SQL/params as saved configuration. Conversation execution has a separate runtime layer (query_run_params.go, invoke.go):

So:

• Check and fix: make SQL column names and params definitions as correct as possible before go-live.
• Runtime tolerance: soften agent call-shape mistakes (nesting, aliases). It cannot fix a wrong column name or a missing table in SQL.

Keep both. Do not rely on runtime tolerance to paper over bad SQL.

6. How this looks in the employee-portrait case

On the Rilong connection, inner parameter names for the same kind of query_id are not unified (trust query.list or the post-check definition), for example:

Smart check does not unify parameter names across the whole catalog (that would break existing SQL), but it does:

1. Surface column-name fixes in note;
2. Give AI real column names so fields in SELECT lists and WHERE are less often wrong;
3. Pair with skill docs that tell the portrait flow to query.list first, then fetch along the pipeline.

Detailed mapping: docs/data-connection-templates/caretop-employee-profile/PARAMS-REFERENCE.md.

7. Boundaries and known limits

8. Recommended use (admin)

1. In Data integration → Data connections, edit the HR connection (e.g. rilong); paste or maintain query_defs_json.
2. Click Smart check and fix (the request must include the current connection ID).
3. Read the returned note: watch “schema-fixed column name” and the AI comments.
4. Save the connection config.
5. For a fixed empId (e.g. 892), query.run each important query_id once.
6. Then have users say “generate so-and-so’s employee portrait” in conversation.

You can tell the work agent in conversation:

Please run smart check and fix on the rilong data connection’s predefined queries, save, and list any query_id that still has SQL errors

9. Implementation index

Tests: query_def_schema_test.go, query_run_params_test.go, query_defs_review_test.go.

10. Evolution (not implemented)

These can iterate later. They are not in the check pipeline today:

• Static column checks and auto-replace for SELECT lists and JOIN ON;
• Auto trial-run each query after check (default params or EXPLAIN);
• Diff preview and per-query_id rollback before writing corrections back;
• Share the same schema context with “synthesize predefined queries from a document” (SynthesizeQueryDefsFromDocument).

Related reading

• Building an employee portrait from chat — user-side wording and pipeline flow
• docs/data-connection-templates/caretop-employee-profile/README.md — Rilong parameters and SQL fix list
• examples/employee-profile-pipeline/data-connection/query-defs.example.json — canonical pipeline query examples