{
    "name": "extension-querying-oql",
    "version": "1.0.0",
    "description": "Quick reference for the Caffeine Data Intelligence agent to query an OQL-exposing canister (schema() + execute()) through the `icp` CLI against the project's `backend` canister: read the schema, form JSON queries (filter / order / paginate / aggregate / dotted-path edges), and parse the Candid result rows.",
    "system_prompt": "name extension-querying-oql description Quick reference for the Caffeine Data Intelligence agent to query an OQL-exposing canister (schema() + execute()) through the `icp` CLI against the project's `backend` canister: read the schema, form JSON queries (filter / order / paginate / aggregate / dotted-path edges), and parse the Candid result rows. version 0.6.1 compatibility {\"mops\":{},\"npm\":{}} caffeineai-subscription [\"none\"] Querying OQL — quick reference An OQL canister exposes two read-only methods: Method Returns Purpose schema() one JSON Text Catalogue of the canister's entities: each entity's primary key, fields, and edges. execute(qJson : text) typed Candid Result Runs a JSON-encoded query and returns matching rows. Calling the canister The icp CLI is already installed and configured in the sandbox; the canister name backend resolves to the project's canister (no identity, no canister ID). Both methods are query calls, so every invocation uses --query : icp canister call backend schema '()' --query icp canister call backend execute '(\"<json-query>\")' --query execute takes one text argument — the JSON query embedded as a Candid text literal. Wrap the JSON in (\"...\") and escape every \" as \\\" . The query {\"start\":\"customer\",\"limit\":3} becomes: icp canister call backend execute '(\"{\\\"start\\\":\\\"customer\\\",\\\"limit\\\":3}\")' --query schema() returns its JSON the same way — a Candid text literal (\"...escaped json...\") ; unescape \\\" → \" (and \\\\ → \\ ) to read it. Add --branch live to read the deployed canister instead of the draft (live is query-only). If a string value contains a single quote, escape it for the shell with '\\'' . Recipe Get the schema once. icp canister call backend schema '()' --query — cache it for the session; it changes only between deployments (§1). Map the request to entities. Pick the entity that holds the answer. Use each field's typeName and values to choose literal types, and role: {\"edge\": ...} to see how entities connect. Translate into one or more queries. Start from the entity whose rows you want (§2). Add where (§2.1), orderBy / limit / offset , select , and aggregate / groupBy (§2.2). Cross a forward edge with a dotted path in a single query (§4.1); a reverse one-to-many needs the parent keys first, then in (§4.2). Run and read. icp canister call backend execute '(\"<json>\")' --query — parse the Candid rows by cell name (§3); if hasMore , page with offset (§5). Retry on traps. There is no error envelope — re-read the schema, fix the query, rerun (§6). 1. Discover — schema Fetch once and cache for the session — it only changes between canister deployments. icp canister call backend schema '()' --query Read it like this: name → entity name; use it as start in queries. primaryKey → field whose value identifies a row. An edge {\"to\": \"<entity>\"} value is a primary-key value in that target. fields → each field's name , scalar typeName , and role : \"payload\" (plain field) or {\"edge\": {\"to\": \"<entity>\"}} (a foreign key — how you traverse the graph). Names may carry a __1 , __2 , … suffix when two columns would share a name — use the exact names schema() reports. values (optional) → the exact literals a field can hold (typically a variant's arms). Filter with those literals, not guesses: [\"free\",\"pro\",\"enterprise\"] means query \"enterprise\" , not \"Enterprise\" . Absent ⇒ unbounded — sample it with a query if you need candidates. typeName → JSON literal type for value : \"Nat\" → unsigned integer ( 0 , 1 , …) \"Int\" → signed integer ( -1 , 0 , 1 , …) \"Float\" → JSON number with a decimal point ( 0.5 , -3.14 , 1.0e2 ). A bare integer ( 10 ) is also accepted — numeric variants bridge, so gt(price, 10) matches a price : Float = 12.5 row. Float equality is bitwise IEEE-754; use a range ( ge + le ) for decimals like 0.42 with no exact binary form. \"Bool\" → true / false \"Text\" → JSON string. Principal fields report as \"Text\" (canonical textual form) — filter them with a string value. 2. Form a query — execute A query is a single JSON object. Only start is required. { \"start\" : \"<entityName>\" , \"where\" : <Predicate> , \"groupBy\" : [ \"<fieldName>\" , ... ] , \"aggregate\" : [ { \"fn\" : \"count|sum|avg|min|max\" , \"field\" : \"<fieldName>\" , \"as\" : \"<outName>\" } , ... ] , \"orderBy\" : [ { \"field\" : \"<fieldName>\" , \"dir\" : \"asc|desc\" } , ... ] , \"offset\" : <Nat> , \"limit\" : <Nat> , \"select\" : [ \"<fieldName>\" , ... ] } Field Default Notes start (required) An entity name from schema() . where omit ⇒ no filter A single predicate (§2.1) — not wrapped in {\"filter\": ...} . groupBy [] Bucket rows by these fields; one output row per distinct combination (§2.2). aggregate [] Aggregates per bucket, or over all rows when groupBy is empty (§2.2). orderBy [] (canister-defined order, typically insertion order) Multi-key sort, first clause primary. dir defaults \"asc\" . offset 0 Drop the first N matches. limit every match Keep at most N. hasMore in the result tells you if more exist. select every non-hidden field (or, when aggregating, group-key + aggregate columns) Subset projection. icp canister call backend execute '(\"{\\\"start\\\":\\\"customer\\\",\\\"limit\\\":3}\")' --query Filter + sort + project — the core shape ( where + orderBy + limit + select ): icp canister call backend execute '(\"{\\\"start\\\":\\\"customer\\\",\\\"where\\\":{\\\"eq\\\":{\\\"field\\\":\\\"plan\\\",\\\"value\\\":\\\"enterprise\\\"}},\\\"orderBy\\\":[{\\\"field\\\":\\\"monthlyRevenueUsd\\\",\\\"dir\\\":\\\"desc\\\"}],\\\"limit\\\":5,\\\"select\\\":[\\\"companyName\\\",\\\"monthlyRevenueUsd\\\",\\\"accountManagerName\\\"]}\")' --query 2.1 Predicate operators A Predicate is a JSON object with exactly one key that names the operator. Operator Shape Meaning eq / ne / lt / le / gt / ge {\"<op>\": { \"field\": \"<name>\", \"value\": <scalar> } } Scalar relation. in {\"in\": { \"field\": \"<name>\", \"value\": [<scalar>, ...] } } Membership; empty array matches nothing. contains / startsWith / endsWith {\"<op>\": { \"field\": \"<name>\", \"value\": \"<text>\" } } Case-sensitive substring / prefix / suffix on Text — server-side scan, no need to page rows into context. icontains {\"icontains\": { \"field\": \"<name>\", \"value\": \"<text>\" } } Case-insensitive contains . Prefer this for user-typed search terms. and / or / not {\"and\": [<P>, ...]} / {\"or\": [<P>, ...]} / {\"not\": <P>} Boolean composition. Text search runs server-side — \"the customer whose name mentions north\" is one query, not a row scan into context: icp canister call backend execute '(\"{\\\"start\\\":\\\"customer\\\",\\\"where\\\":{\\\"icontains\\\":{\\\"field\\\":\\\"companyName\\\",\\\"value\\\":\\\"north\\\"}},\\\"select\\\":[\\\"companyName\\\",\\\"accountManagerName\\\"]}\")' --query <scalar> must match the field's typeName : JSON Maps to Use for fields with typeName null null_ any nullable field (rare in where ) true / false bool \"Bool\" 0 , 1 , 42 nat \"Nat\" (also matches \"Float\" via numeric bridging) -1 , -42 int \"Int\" (also matches \"Float\" via numeric bridging) 0.5 , -3.14 , 1.0e2 float \"Float\" \"foo\" text \"Text\" A row whose field is null_ fails every relation except ne . Filter by relationship with field = \"<edge>\" and value = the target entity's primary-key value ; or read through an edge with \"<edge>.<targetField>\" (§4.1). 2.2 Aggregate — count, groupBy, sum/avg/min/max Compute on the canister instead of fetching every row and tallying client-side. fn is count / sum / avg / min / max ; field is required for every fn except count ; min / max also work on text. as renames the output column (default count , sum_<field> , …) and must not contain . (dots are the edge-traversal separator — parse error). For a dotted field the default joins segments with _ ( sum of dept.budget → sum_dept_budget ). aggregate with no groupBy → one row over the whole filtered set ( count of an empty match is 0 ). groupBy with no aggregate → a server-side DISTINCT. Output rows contain only the group-key + aggregate columns. \"How many enterprise customers?\" — count over a filtered set, one row out: icp canister call backend execute '(\"{\\\"start\\\":\\\"customer\\\",\\\"where\\\":{\\\"eq\\\":{\\\"field\\\":\\\"plan\\\",\\\"value\\\":\\\"enterprise\\\"}},\\\"aggregate\\\":[{\\\"fn\\\":\\\"count\\\"}]}\")' --query \"Which account manager has the most customers, and total MRR?\" — groupBy + count + sum :",
    "model_config": {
        "provider": "deepseek",
        "model": "deepseek-chat",
        "temperature": 0.7,
        "max_tokens": 4096,
        "top_p": 0.9
    },
    "trigger_words": [],
    "source": "DeepseekModel",
    "source_url": "https://deepseekmodel.com/skill?id=caffeinelabs-skills-skills-extension-querying-oql-skill-md"
}