Generate predefined queries from your product code and import them into Cadau

In user language: for Chuansoft (your existing business system) backend and business admins. How to generate predefined-query JSON from Chuansoft’s own list queries, feature metadata, or data-access layer, then import it into a Cadau data connection so the embedded assistant asks numbers against a fixed metric. Cadau does not write SQL on the fly; SQL follows the definition Chuansoft generated.

For the user-side steps, see Data connections and predefined queries; for the embed architecture, see Embedding Cadau in Chuansoft.

Date: 2026-09-01 Status: Implemented Related help: Data connections and predefined queries Related mechanisms: Accuracy of asking numbers over a data connection

---

The takeaway (read this first)

In one sentence: the query metric is written in Chuansoft code; Cadau only runs queries that have been imported. Chuansoft ships a query pack; Cadau imports the matching groups to go live or to customize for a customer.

---

1. Why generate from Chuansoft code

When the assistant asks for business data in conversation, Cadau does not allow assembling arbitrary SQL on the fly. Everyday number-asking uses predefined queries on the data connection: each one matches a kind of business question (look up a person by name, look up people in a department who are still employed, this month’s attendance, and so on).

The source of truth for those questions is in Chuansoft:

• WHERE conditions already used on list and detail pages
• Feature metadata (business names, table names, field names)
• Existing query services / mappers

Rewriting them by hand in Cadau drifts from Chuansoft code. The right approach is:

1. Chuansoft generates JSON by module (core group, a feature-module group, a customer overlay group)
2. Import it in Cadau Data integration → Data connections → Predefined queries
3. Tap Save; then run Smart check, and Generate skill so the assistant picks the right query

Who can see which rows and columns is still enforced by policies on the data connection (host_actor). Do not expect to encode grants in SQL comments. Policy notes: [sdk/host-embed/宿主增强-AgentRun与数据权限.md](/docs/sdk-host-agent-run).

---

2. Import file format (Chuansoft generators should emit this)

Cadau accepts three kinds of JSON; Chuansoft picks what it needs. Export by group is recommended, matching “core / module / customer overlay”.

2.1 One group of queries (recommended for everyday delivery)

Suggested file name: {product}-{module}-query-defs.json.

{
  "kind": "cadau.query_def_group",
  "version": 1,
  "group": {
    "id": "hr_core",
    "name": "HR core",
    "description": "Active staff, departments, master data; maps to Chuansoft’s HR core module",
    "queries": [
      {
        "id": "staff_by_name",
        "name": "Look up active staff by name",
        "description": "Use when the user gives a person’s name and wants basic info or “is this person still here”. If several rows match, the user must confirm.",
        "sql": "SELECT id, empNo, empName, deptId FROM eaemp WHERE empName = ? AND state = 0",
        "params": [
          { "name": "empName", "type": "string", "required": true }
        ],
        "max_rows": 20
      }
    ]
  }
}

In Cadau: open the target data connection → Predefined queries → select a group or tap Import all → pick this file → Save.

• Import into the current group: queries merge into the group you are editing
• As a new group: a group is appended at the end of the list; use move up/down to change lookup order

2.2 All groups (recommended for a version release)

Ship core + each module + optional customer overlay in one file. Suggested file name: {product}-query-defs.json.

{
  "kind": "cadau.query_def_catalog",
  "version": 1,
  "groups": [
    {
      "id": "base",
      "name": "Core",
      "description": "Master data and org queries shared across customers",
      "queries": []
    },
    {
      "id": "attendance",
      "name": "Attendance module",
      "description": "Add after attendance is enabled; do not import this group if it is not enabled",
      "queries": []
    },
    {
      "id": "customer_acme",
      "name": "Customer overlay · Acme",
      "description": "This customer’s metric only; if it must win over core, move this group to the top of the list after import",
      "queries": []
    }
  ]
}

When Cadau imports all:

• Merge: same group id or same group name writes queries into the existing group; new groups are appended
• Replace all: the file replaces every current group (groups not in the file are cleared; confirmation required)

Group order in the list = agent lookup order: search the groups at the front first; only then use later groups. When a customer overlay must cover the standard metric, put the overlay group first.

2.3 Compatibility: a query array only

With no group wrapper, import still works (into the current group, or appended as one group on Import all):

[
  {
    "id": "staff_by_name",
    "name": "Look up active staff by name",
    "sql": "SELECT id, empName FROM eaemp WHERE empName = ?",
    "params": [{ "name": "empName", "type": "string", "required": true }]
  }
]

New generators should prefer 2.1 / 2.2, so you can ship by module.

---

3. How to write one query (field conventions)

Each queries[] element:

Do not emit check marks such as review_verified; Cadau’s Smart check writes those.

3.1 SQL and parameters (the easiest place for a Chuansoft generator to go wrong)

Suggested parameter type: string / int / date. required: true means a value is required before execution.

If the same parameter appears several times in the SQL, you need that many ? and that many params items (same name repeated is fine, or split into keyword used three times — the ? count is what counts). For example, a fuzzy match with three ? needs three parameter slots.

PostgreSQL: Cadau turns ? into $1,$2,…; SQL Server: @p1,@p2,…; Chuansoft still generates ?.

3.2 Id and naming suggestions

When the same id appears in several groups, the one in the group that is listed first wins. If a customer overlay should cover a standard query, write the same id in the overlay group and put that group first.

3.3 When narrowing by person, leave the id parameter in

Employee self-service “can only see myself” is enforced by the data connection row policy such as empId = {{host_actor.employee_id}}, not by hoping the model remembers. When generating queries:

• SQL that filters by person must have a matching parameter (e.g. empId)
• Do not hard-code identity as a SQL literal
• Column names follow the business database (do not mix empId / emp_id)

After import, the workspace admin configures or generates row and column policies; the generator need not emit policy JSON.

---

4. Suggested way to generate from Chuansoft code

You do not have to generate the whole database at once. Map how users will ask onto queries you already have.

4.1 What to pull from

4.2 Generation pipeline (suggested)

1. Enumerate modules → decide groups[] order (core first or customer overlay first, by product policy)
2. Each list/detail query → one QueryDef
3. Validate: id legal, SELECT only, no semicolon, ? count = params length, no @param
4. Emit cadau.query_def_group or cadau.query_def_catalog
5. Run every query on a test database with real parameters (row counts, empty results, several rows)
6. Hand the JSON to a Cadau workspace admin to import and save

Pseudocode (illustrative):

for each Chuansoft query spec Q:
  emit {
    id: slug(Q.module + "_" + Q.key),
    name: Q.uiTitle,                    // query name on the page
    description: Q.whenToUse,           // “which questions this is for” from product/interaction notes
    sql: rewriteNamedParamsToQuestionMark(Q.sql),
    params: Q.filters.map(f => { name, type, required }),
    max_rows: min(Q.pageSize or 200, 500)
  }

4.3 Group by module (same as the Cadau UI)

On the Cadau side: move up/down changes agent lookup order; each group can be imported and exported on its own, matching Chuansoft shipping patches by module.

---

5. Import in Cadau (admin)

1. Workspace → Data integration → open the data connection that already points at the Chuansoft database (run Test connection first)
2. Open Predefined queries
3. Pick by pack:

- Single-module file → select the target group and tap Import, or Import all and choose “as a new group” - All-groups file → Import all → merge or replace

1. When the customer should win, move up the overlay group to the front
2. Tap Save (without save, conversation still uses the old catalog)
3. Suggested: Smart check (current group) → apply, then save again
4. Suggested: Generate skill, writing each query’s purpose into a workspace skill so the assistant picks the right id

Import only changes the form; after save, members’ conversations can use the new queries. If target database table names do not match the JSON, import first then correct the SQL; do not expect table names to remap automatically.

---

6. How to confirm it works after import

If the numbers still do not match: first check whether name/description sound like the user’s words; then whether the group order is wrong (a weak match in an earlier group can block a later group).

---

7. Chuansoft generator checklist

Before you ship JSON, self-check:

• [ ] kind is cadau.query_def_group or cadau.query_def_catalog (or a compatible query array)
• [ ] Every id is legal and, across modules, does not collide unless you intend to overlay
• [ ] sql starts with SELECT, has no ;, has no write keywords, and parameters are only ?
• [ ] ? count = params length
• [ ] name and description state the applicable questions, not only a table name
• [ ] max_rows is reasonable (directory-style 100–200, detail-style 1–20)
• [ ] Group id/name are stable, so the next import can merge instead of replacing all every time
• [ ] You have trial-run on the same database as the Cadau data connection
• [ ] No database accounts, passwords, or connection strings

---

8. Minimal runnable example

Save the following as hr-core-query-defs.json, import into one group on the Cadau HR-database connection, then save. You can then joint-debug with “look up active staff by name” (change table names to Chuansoft’s real tables):

{
  "kind": "cadau.query_def_group",
  "version": 1,
  "group": {
    "id": "hr_core",
    "name": "HR core",
    "queries": [
      {
        "id": "staff_by_name",
        "name": "Look up active staff by name",
        "description": "The user gives a name; look up active staff number and department.",
        "sql": "SELECT id, empNo, empName, deptId FROM eaemp WHERE empName = ? AND state = 0",
        "params": [{ "name": "empName", "type": "string", "required": true }],
        "max_rows": 20
      },
      {
        "id": "staff_by_id",
        "name": "Look up master data by person id",
        "description": "When you already have an employee id, fetch master data; a row policy can force the person’s own id.",
        "sql": "SELECT id, empNo, empName, deptId, state FROM eaemp WHERE id = ?",
        "params": [{ "name": "empId", "type": "int", "required": true }],
        "max_rows": 1
      }
    ]
  }
}

A fuller employee-profile example is in the repo at examples/employee-profile-pipeline/data-connection/query-defs.example.json (import as the 2.3 array format, or wrap it yourself in group.queries).

---

Implementation map (for generator authors)

---

Related documents

• Data connections and predefined queries — how admins configure and query in Cadau
• Embedding Cadau in Chuansoft — embed, identity, data-connection policies
• Skill and app import/export and predefined queries — skill packs that carry query dependencies (complements this article’s “generate in Chuansoft then import”)
• Smart check for predefined queries — after import, check SQL against table columns