insights-workbook-cli

# Insights workbooks over frappectl You author workbook content and push it to a live Insights site with `frappectl`. This file is the procedure. `reference/` is the contract. This skill needs nothing installed on the site. It supports Insights 4 (develop) sites only. Section 1 checks the version. ## Before you start The user owns the `frappectl` profile for the site. Check it: ```sh frappectl -s <profile> auth whoami ``` If the site has no profile, stop and tell the user to make one. Do not run `auth` commands. Do not set `FRAPPE_*` variables. Pass `-s <profile>` on every call. The examples write `$SITE` for it. To shorten the command, define a shell function: `fc() { frappectl -s <profile> "$@"; }`. Do not put the command in a variable. zsh does not split `$S` into words, so `S="frappectl -s x"; $S ...` fails. `frappectl` prints a deprecation warning from `authlib` on stderr. It is noise. Parse stdout only, and never merge stderr into it (`2>&1`), or the JSON does not parse. ## The CLI is the only interface Every action on the site goes through `frappectl`. Never read or write the site's files, database, logs or bench, even when the site runs on this machine. What a local path tells you does not hold on the site the workbook must run on. **Assume you do not have the Insights source, because most users do not.** Nothing in this skill needs it. Every fact about the site is a call: its tables, its columns, its stored data, its function library. To know whether something exists, ask the site. ### When a call fails and the error names no cause Insights returns some failures as an exception type and nothing more. An Insights user is a Website User, so the framework suppresses the traceback. Do not read the site's code. Take these three steps. 1. Retry the call once. Change only what could matter. 2. Bisect over the API. Shrink the payload until you have the smallest call that still fails. Say what that proves. 3. Stop and report to the user. Give the exact command and the exact response. An error you cannot act on is a site or environment fault, not a payload fault. Hand the user a clean reproduction. Before you report it, run the call again with `--debug`. It prints the request and the server's own messages to stderr, and Insights often names its fault only there. These exception types do name a cause. Fix that part of the payload. Do not bisect: | Exception | Fix | |---|---| | `UnknownColumn` | A column, table or query name. Compare it with the table's columns or the base query's `columns`. An expression that reads a column the table does not have, bare, as `q.col` or as `q['col']`, fails with this type too. | | `NotPermitted` | The user may not read a table, a query or a column the query reads. Pick another, or ask the user for access. | | `QueryRefused` | The operation's shape: a join type, operator, aggregation or granularity. Compare it with `reference/operations.md`. | | `TableNotStored` | A data store run that may not import read a table that is not stored. Store it, or run on the live connection. See "Sample the data" in section 5. | | `ExpressionSyntaxError` | An expression. Check each function with `get_function_description`. | | `QueryTimeout` | The query's cost. Filter earlier, join less, or read the data store. | A failed run names the operation it failed in, and keeps its exception type: `Operation 2 (mutate 'doubled'): UnknownColumn: NameError: name 'foo' is not defined. Expression: foo * 2`. The number counts the query's operations from 1. After the colon comes the cause, `<type>: <text>`. A failure in a query this one reads adds that query's title, and names the inner operation once: `Operation 1 (source): Operation 2 (mutate 'doubled') in query 'Open Tickets': ...`. Only a user who may edit the query reads the cause. Anyone else reads the operation and the exception type, such as `Operation 1 (source): TableNotStored`. `NotPermitted` names no operation. `reference/expressions.md` has the rest of an expression's error. An older site answers only the exception type, or a bare `^`. Then `--debug` shows the cause. ## 1. Read the site before you plan ```sh frappectl -s $SITE auth whoami frappectl -s $SITE method call insights.api.get_app_version frappectl -s $SITE method call insights.api.data_sources.get_all_data_sources frappectl -s $SITE method call insights.api.workbooks.get_workbooks ``` You act as the profile's user, and you see what that user sees. `get_workbooks` lists what the user created or was given, admins included. An admin finds anyone else's workbook with `sources=["created","shared","others"]`. For anyone else, a workbook it does not list is a missing share, not a missing workbook. Insights reports a missing grant as "not found". Ask the user to share it. ### Read the site's version too, not only its data Read `get_app_version` before anything else. It returns a string such as `"4.0.0-dev"` or `"3.14.1"`. **If the major version is below 4, stop.** Tell the user this skill supports only Insights develop sites. An older site accepts payloads it does not understand and reports no error: it ignores unknown chart config keys, `import_workbook` returns only a name, and `json_value` is missing. The workbook then looks built and is wrong. A develop site can still be older than this file, so an endpoint this file names may not be there. Before you depend on one, call it once with no arguments: ```sh frappectl -s $SITE method call insights.api.ai.lineage.describe_query ``` Only a missing method answers `Failed to get method`, such as `error: Failed to get method insights.api.ai.lineage.describe_query with No module named 'insights.api.ai.lineage'`. Any other answer, a missing-argument error too, means the method is there. Probe each endpoint once per session. Do not use `method search`. It returns no results even for a method that exists. A query document method, such as `profile_column`, has no path to probe. `profile_column` arrived with `describe_query` and `verify_workbook`, so a site that has one has all three. **Every procedure here works on a develop site with no newer endpoint.** A newer endpoint does the same job faster, never a different job. A missing endpoint costs round trips, never a result. If one is missing, take the plain path and continue. Do not stop. Do not tell the user their site is behind unless they ask. The same holds for a function `get_function_list` does not return: the plan changes, the ask does not. ### Translate the ask into the site's vocabulary The user asks in business language. "Support tickets raised by partner sites" names no table and no column. Most users cannot name one. Only an admin knows what data is where. You must translate the ask, and this step is the most likely to go wrong. Work the ladder in order. **The corpus comes before the schema.** A schema tells you a column exists. It never tells you the column is the right one. A query somebody wrote and uses holds a definition that survived contact with the business. **1. Name the data sources.** `get_all_data_sources`. Their names are the first map: which system holds tickets, which holds sites, which holds billing. An ask that spans two of them is normal. **2. Search the corpus for every business word in the ask.** This is the step that pays. "Partner", "active customer", "churned" and "enterprise" are decisions somebody encoded once, in a title, a `mutate`, a `case_when`, a filter value or a `sql` query. Section 3 has the searches. Run them for every noun and every qualifier the user used, before you look at a single table. A hit gives you the calculation *and* the tables it reads, so it settles steps 3 and 4 at once. Take the definition from a hit, not its framing or its population. A template's metrics are not the user's focus. A workbook built for one audience does not say who the user's population is. And when a table has a column that names the thing directly, such as one row per app, prefer it to the key a corpus query joins through. **On an ERPNext site, the app's own reports come first.** Gross Profit, Stock Balance and Accounts Receivable are Script Reports, and each is ERPNext's definition of its measure. The user already trusts it. Build to match it, and reconcile against it in check 4 of section 6: ```sh frappectl -s $SITE doc list "Report" --filters-json '{"ref_doctype":"Sales Invoice"}' --fields name,report_type --all frappectl -s $SITE report run "Gross Profit" \ --filters-json '{"company":"Acme","from_date":"2026-01-01","to_date":"2026-03-31","group_by":"Invoice"}' ``` A report's filters are the `fieldname`s its form asks for. Pass every filter the form fills in by default, too: the form sets Gross Profit's `group_by`, and the report fails without it. **3. Search table labels for what the corpus did not answer.** If you omit `data_source`, `get_data_source_tables` searches the whole instance. It matches the table's `label` and its real name: ```sh for word in ticket partner site; do frappectl -s $SITE method call insights.api.data_sources.get_data_source_tables \ -F search_term=$word -F limit=50 done ``` Frappe doctype names are business language, so one search per noun cuts hundreds of tables to a handful. **4. Read the columns of the few tables that survived.** ```sh frappectl -s $SITE method call insights.api.data_sources.get_data_source_table_columns \ -F data_source="Frappe Cloud" -F table_name="tabSite" ``` `get_schema -F data_source=<name>` returns every table with its columns in one call. It opens each table on the backend unless the site stores its column lists, so it is the most expensive call in the skill. Use it only when you must search columns rather than tables. Say that you did. A newer site searches columns directly, across every table the caller may read: ```sh frappectl -s $SITE method call insights.api.ai.search.search_columns -F term=partner ``` It answers from stored column lists. A table nobody has synced or opened is not in it, so an empty answer is not proof. Fall back to `get_schema` when the answer matters. **Pick tables the dashboard's reader can read.** A chart runs with its reader's permissions unless its `run_as_owner` is set. A reader who cannot read a table sees the card refused: on one site a Sales Manager could not read Item, and an Expense Approver could not read Employee. Ask who reads the dashboard. If it needs a table that reader cannot read, say so in the scope block. **5. Ask.** What the ladder does not resolve goes in the scope block as a question, with the candidates you found. Do not guess a definition somebody has already written down. ### Say what you leaned on Discovery makes choices and the user sees none of them. Name every one, in the scope block and again in the closing summary. One line each: - **Reused a definition.** The workbook, the query, and the expression you copied. If the user's ask differs from it, say how and ask. - **Derived a definition yourself.** Say that you found no existing one. Name the columns you built it from. Ask the user to confirm it. **This line matters most.** The people who use a reused definition checked it. Nobody checked the one you derived. - **Picked one candidate over others.** Name what you rejected, and why. A search that returned three tables and a search that returned one are different situations. Only you can see which happened. - **Made a load-bearing cut.** A date the data starts, a filter that drops rows, a source you chose because a table was not stored. Each one changes the number. When a JSON payload changed shape over time, split it by the version field it carries, not by a date. - **Leaned on a source that sees part of the population.** Name the part. Telemetry comes only from sites that opted in. If another source sees more, such as a web analytics tool for website visits, say so. - **Built ahead of the data.** When the user asks for a chart before its data exists, keep the chart and say it reads empty until the data arrives. Do not drop it. Silence here reads as certainty. Do not spend certainty you do not have. ### Tip when the ask names a chart but not a question Users who are not analysts often ask for a chart type ("a pie chart of orders by status") when what they want is an answer ("which orders are stuck"). Build what they asked for. Then, in the closing summary, add one line suggesting they describe what they want to learn next time, so you can choose the query and chart. Skip the tip when the ask already says what the chart is for. Give it once per conversation. ### A query spanning two data sources needs the data store One flag decides cross-source. With `use_live_connection: 0` every table resolves through the warehouse, whatever its data source, so tables from two sources join normally. With `use_live_connection: 1` each table opens on its own backend, and a join across two of them cannot run. So an ask that spans two systems is a data store query, and every table it needs must be stored. See "Choose the data store or the live connection" in section 5. ## 2. Read the contract Read `reference/rules.md` now. It is short, and every rule in it is load-bearing. Read the rest when the plan needs it: | File | Read it before you write | |---|---| | `reference/documents.md` | any document you create — the field shapes | | `reference/operations.md` | any query pipeline | | `reference/expressions.md` | any `mutate`, expression measure or expression filter | | `reference/charts.md` | any chart config | | `reference/dashboards.md` | any dashboard layout or filter link | | `reference/workbook-format.md` | only when importing a workbook JSON the user gave you | Plan only with the operation types, chart types and functions these files list. Never invent one. The site is the authority on the function library. "The site holds the real list" in `reference/expressions.md` gives the two calls that read it. Read a function's docstring before you use one these files do not cover, or describe in one line only. For worked examples, read what the site already has (section 3). A workbook an app ships is a good one: `doc list "Insights Workbook" --fields name,title,is_standard --all`. ## 3. Reuse what the user already has People share workbooks. A measure somebody already defined and uses every day is more likely correct than one you derive from column names. **Run these searches before you read the schema.** Open a `sql` query even when you would not author one. Definitions nobody could express in builder operations end up there, so those queries hold the densest tribal knowledge on the site. Read the SQL, take the calculation, and rebuild it as builder operations. **Search the corpus. Do not download it.** Titles and the JSON fields are text columns, so the site greps them for you. A `like` filter is the whole search. **Search titles first.** A person wrote them, in the same business language the user uses. These searches translate "partner" into a real definition: ```sh frappectl -s $SITE method call insights.api.workbooks.get_workbooks -F search_term=partner frappectl -s $SITE doc list "Insights Query v3" \ --filters-json '{"title":["like","%partner%"]}' --fields name,title,workbook --all frappectl -s $SITE doc list "Insights Chart v3" \ --filters-json '{"title":["like","%partner%"]}' --fields name,title,workbook,chart_type --all ``` `search_term` matches workbook titles only, so run the query and chart searches too. A workbook titled "Cloud Metrics" can hold the partner definition. These searches work on every site. A newer site has one endpoint that runs them all in a single call. Probe for it once (section 1). Use it when it is there: ```sh frappectl -s $SITE method call insights.api.ai.search.search_content -F term=partner -F limit=20 ``` A hit names `doctype`, `name`, `title`, `workbook`, `workbook_title`, `matched_field` and a `snippet` of the text around the match. The snippet is the reason to prefer it. You can tell a real definition from a coincidence without fetching the document. **A hit also includes `used_by_charts`, `used_by_dashboards` and `dashboard_views`.** Read them. Three queries can define "partner" three ways. The one behind a dashboard forty people open every week is the definition the organisation runs on. An orphan query somebody made once is not. Prefer the used one, and say in the scope block how much it is used. Without the endpoint you get the same documents, in more calls, with no snippet and no usage count. The searches do not change. Rank by hand instead, and say you did. **For a query you found, read its lineage.** A newer site answers both directions in one call: ```sh frappectl -s $SITE method call insights.api.ai.lineage.describe_query -F query=<name> ``` `upstream` holds every query it reads, through every hop, and the tables at the end, each with `stored`, `row_limit` and `sync_from`. `downstream` holds the queries built on it, the charts on those, the dashboards that show those charts, and the `filter_links` that name one of those queries. It is permission filtered, so a query you cannot read answers "not found". `downstream` comes from an index a background job rebuilds after each save, so a query saved a moment ago may be missing from it. `upstream` is read from the operations and is always current. Without the endpoint, `doc get` each query in the chain and run the `like` searches above for its name. **Then search the JSON.** It reaches column names, expression text, filter values and measure names. A definition lives there even when no title says so: ```sh frappectl -s $SITE doc list "Insights Query v3" \ --filters-json '{"operations":["like","%partner%"]}' --fields name,title,workbook --all frappectl -s $SITE doc list "Insights Chart v3" \ --filters-json '{"config":["like","%base_net_total%"]}' --fields name,title,workbook,chart_type --all ``` Search one word at a time. Search the words the *user* used before the words the schema uses. `like` matches a substring and nothing else. It has no synonyms and no stemming, so "partner" finds "is_partner" and "Partner Sites". "partner persona" finds neither. Each search returns a shortlist of names. Then `doc get` the two or three worth reading. Read their `operations` for the calculation: the expression, the aggregation, the filter group that defines "revenue" or "active customer" here. Do not list every query with its `operations`. On a site with six hundred workbooks that is useless. **Copy the calculation into your own query. Do not reference the other workbook's query.** A cross-workbook query reference builds and runs, but the workbook's source selector lists only queries in its own workbook, so the user cannot see or edit where the data came from. Say what you reused. See "Say what you leaned on" in section 1. ## 4. Converge on scope with the user Before you author anything, post a scope block. It states: - the target workbook, by name - the counting unit: what one counted thing is, such as a site, a team or a signup. A team with three sites counts three times when the unit is a site. Name the dashboard by its unit: "Insights Sites", not "Insights Users", when it counts sites - the tables to read from, and the grain of each query - every measure, with the exact columns it computes from - any definition you reused, and where it came from - the dimensions and the filters - what the user is actually looking at, so the dashboard leads with it When more than one column could serve a measure, name the candidates and ask which one. Never resolve that ambiguity alone. Never put an unasked choice into the closing summary. A sample writes nothing: an unsaved query saves no document. So sample while you scope. See "Sample the data" in section 5. **Stop here.** Post the scope block, end your turn, and wait for the user's reply. Agree the scope with the user, not with yourself. ## 5. Author ### Never create a workbook the user did not ask for The default is to add to a workbook that exists. "Add a chart", "fix this query" and "put a filter on the dashboard" all create documents inside the named workbook. None of them creates a workbook. Create a workbook only when the user asks for one in those words. When the target workbook is unclear, ask which one. Do not resolve it by making a new one. Ask before you create a workbook when: - the user names no workbook and more than one could fit - the work would fit an existing workbook you can see - you recover from a failed attempt. Patch or delete what you made. Never leave a second copy ### Write through a build script Every write goes through a Python script. Reading and exploring stay direct `frappectl` calls. The reason is verification. The checks in section 6 are list comparisons. An agent that compares lists by hand reports a verdict nobody can audit. In a script the result is an exit code. `examples/build_workbook.py` is that script. Copy it and edit its `build()`, or import it from your own script: ```python import sys; sys.path.insert(0, "<skill>/examples") import build_workbook as bw bw.configure("<profile>", "<workbook>") b = bw.Build() query = b.upsert("invoices", bw.QUERY, {"title": "Sales Invoices", "operations": [...], ...}) b.upsert("revenue", bw.CHART, {"title": "Revenue", "query": query, "chart_type": "Line", "config": {...}}) sys.exit(bw.verify()) ``` `Build.upsert(key, doctype, fields)` creates the document behind `key`, or updates it in place, and returns its real name. The keys are yours. Keep them stable, because a key is how a rerun finds its document. The build keeps a state file next to the script, `<script>.state.json`, with the name behind each key and what the build last wrote. So a rerun never makes a second copy, and a field the user edited on the site since keeps the user's value. The run prints each one it kept. Tell the user which.