File size: 52,706 Bytes
bdac669 98c8696 bdac669 affe37f cfa8541 bdac669 9a6cb9a bdac669 98c8696 affe37f 64543d2 affe37f 98c8696 bdac669 cfa8541 bdac669 cfa8541 bdac669 cfa8541 bdac669 a93637f bdac669 a93637f bdac669 81a8217 bdac669 81a8217 bdac669 81a8217 85f794d 5b27402 bdac669 a93637f 98c8696 a93637f bdac669 a93637f bdac669 98c8696 bdac669 876a3b9 bdac669 d598032 bea2cff daae64a bdac669 0d60127 bdac669 0d60127 cfa8541 bdac669 9a6cb9a bdac669 98c8696 bdac669 98c8696 bdac669 cfa8541 bdac669 9a6cb9a bdac669 cfa8541 98c8696 cfa8541 7f72224 cfa8541 98c8696 cfa8541 bdac669 9a6cb9a 98c8696 bdac669 98c8696 bdac669 98c8696 bdac669 98c8696 85f794d 98c8696 bdac669 9a6cb9a cfa8541 85f794d cfa8541 4df2e4a 85f794d cfa8541 9a6cb9a bdac669 cfa8541 bdac669 9a6cb9a cfa8541 98c8696 cfa8541 9a6cb9a 98c8696 bdac669 9a6cb9a bdac669 9a6cb9a bdac669 9a6cb9a cfa8541 7f72224 cfa8541 7f72224 cfa8541 9a6cb9a cfa8541 98c8696 cfa8541 9a6cb9a cfa8541 9a6cb9a cfa8541 98c8696 cfa8541 98c8696 bdac669 cfa8541 affe37f cfa8541 81a8217 85f794d 98c8696 81a8217 98c8696 85f794d 98c8696 85f794d 98c8696 85f794d bdac669 81a8217 bdac669 9a6cb9a bdac669 85f794d bdac669 cfa8541 bdac669 cfa8541 bdac669 cfa8541 bdac669 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 | """External build tools over the app's Sheet, saved View and Application writers.
The API supplies its existing permission, read-projection and binding-resolution functions as
request-scoped callbacks. Core never imports API modules or chooses another tenant runtime.
Adding a tool means adding a registry entry; writes also require the router's literal allow-list.
"""
import copy
import json
import math
import re
from dataclasses import dataclass
from pathlib import Path
from typing import Callable, Optional
from core import perm_scope, sheets, sheet_structure, sheet_templates, write_execution
# Keep supported versions in release order, with the newest last.
MCP_SUPPORTED_PROTOCOL_VERSIONS = ("2025-06-18",)
def negotiate_mcp_protocol(requested=None) -> str:
"""Return the requested supported version or the newest supported version."""
if requested is not None and not isinstance(requested, str):
raise ValueError("The protocol version must be a string.")
if requested in MCP_SUPPORTED_PROTOCOL_VERSIONS:
return requested
return MCP_SUPPORTED_PROTOCOL_VERSIONS[-1]
CELL_FIGURE_RULE = (
"A figure a database can supply goes in a cell database or a calculation object, "
"and a typed value is for a title, a label or an assumption."
)
BUILD_TAGS = tuple(json.loads(
(Path(__file__).resolve().parents[1] / "build_tags.json").read_text(encoding="utf-8")))
_BUILD_TEXT = r"^(?=.*\S)[^\u0000-\u001f\u007f\u0085\u2013\u2014\u2028\u2029]+(?![\s\S])"
ABOUT_SCHEMA = {
"type": "object", "additionalProperties": False,
"properties": {
"title": {"type": "string", "minLength": 1, "maxLength": 240, "pattern": _BUILD_TEXT},
"description": {"type": "string", "minLength": 20, "maxLength": 240, "pattern": _BUILD_TEXT},
"tags": {"type": "array", "minItems": 1, "uniqueItems": True,
"items": {"type": "string", "enum": list(BUILD_TAGS)}},
}, "required": ["title", "description", "tags"],
}
@dataclass(frozen=True)
class ToolContext:
session: object
actor_name: str
require_reader: Callable
require_editor: Callable
project_book: Callable
resolve_binding: Callable
cache_binding: Callable
list_databases: Callable
describe_database: Callable
query_database: Callable
view_event: Callable
view_share_preview: Callable
app_upsert: Callable
app_delete: Callable
database_object: Optional[Callable] = None
sheet_object: Optional[Callable] = None
validated_batch: Optional[Callable] = None
confirm_change: Optional[Callable] = None
def _object(properties=None, required=(), **extra):
return {"type": "object", "properties": properties or {}, "required": list(required), **extra}
_STRING = {"type": "string"}
_ADDRESS = {"type": "string", "description": "An A1 cell address on the named tab."}
_FORMAT = _object({"kind": _STRING, "code": _STRING, "decimals": {"type": "integer"},
"currency": _STRING, "thousands": {"type": "boolean"}}, ("kind",))
# S67-T14 / contract C2: a border side is the legacy `true` (read as a thin black line) or
# `{style, color}` with one of Excel's seven line styles and a six digit hex colour; `false` or an
# absent side is no side. `sheets.py::_clean_border_side` refuses anything else by code, and this
# publishes the same shape so an AI client can draw a styled border at all.
_BORDER_SIDE = {"oneOf": [{"type": "boolean"}, _object({
"style": {"enum": list(sheets.CELL_BORDER_STYLES)},
"color": {"type": "string", "pattern": "^#[0-9A-Fa-f]{6}$"}}, ("style", "color"))]}
_STYLE = _object({
**{key: {"type": "boolean"} for key in ("bold", "italic", "underline", "strike", "wrap")},
**{key: _STRING for key in ("fill", "color", "font", "align", "valign")},
"size": {"type": ["number", "string"]}, "indent": {"type": "integer"},
"textRotation": {"type": "number"}, "format": _FORMAT,
"border": _object({side: _BORDER_SIDE for side in ("top", "right", "bottom", "left")}),
})
_VALIDATION = _object({"list": {"type": "array", "items": _STRING}}, ("list",))
# Mirror the grid vocabulary without importing the API. verify_api checks parity.
FILTER_OPS = frozenset({
"contains", "doesNotContain", "eq", "neq", "isEmpty", "isNotEmpty",
"gt", "gte", "lt", "lte", "between", "within",
"topN", "bottomN", "inTopPct", "inBottomPct", "aboveAvg", "belowAvg",
"inQuartile", "inDecile", "isAnyOf", "isNoneOf", "hasAnyOf", "hasAllOf", "hasNoneOf",
})
_FILTER_OP = {"type": "string", "enum": sorted(FILTER_OPS)}
_FILTER = _object({"conj": {"enum": ["and", "or"]}, "nodes": {"type": "array", "items": _object({
"colId": _STRING, "op": _FILTER_OP, "value": {},
"valueRef": _object({"ref": _ADDRESS}, ("ref",)),
"conj": _STRING, "children": {"type": "array", "items": {"type": "object"}},
})}})
def _query_filter_node(depth=1, *, allow_link=True):
"""Publish bounded query nodes without adding link conditions to saved View bindings."""
leaf = _object({
"id": _STRING, "colId": _STRING, "op": _FILTER_OP, "value": {}, "value2": {},
"values": {"type": "array", "items": _STRING, "minItems": 1, "maxItems": 100},
"rhs": _object({"kind": {"enum": ["field"]}, "colId": _STRING}, ("kind", "colId"),
additionalProperties=False),
}, ("colId", "op"), additionalProperties=False)
nodes = [leaf]
if depth < 3:
nodes.append(_object({"conj": {"enum": ["and", "or"]}, "children": {
"type": "array", "minItems": 1, "maxItems": 50,
"items": _query_filter_node(depth + 1, allow_link=allow_link),
}}, ("conj", "children"), additionalProperties=False))
if allow_link:
nodes.append(_object({
"link": _STRING,
"where": _query_filter_node(allow_link=False),
}, ("link", "where"), additionalProperties=False,
description="One link condition per call. The target database and join fields must be readable. "
"Use AND to intersect this pin with source filters."))
return {"oneOf": nodes}
_QUERY_FILTER_NODE = _query_filter_node()
_QUERY_FILTER = {"oneOf": [
_object({"conj": {"enum": ["and", "or"]}, "nodes": {
"type": "array", "minItems": 1, "maxItems": 50, "items": _QUERY_FILTER_NODE,
}}, ("conj", "nodes"), additionalProperties=False),
{"type": "array", "minItems": 1, "maxItems": 50, "items": _QUERY_FILTER_NODE},
_object({"link": _STRING, "where": _query_filter_node(allow_link=False)},
("link", "where"), additionalProperties=False),
]}
_CLIENT_QUERY_LEAF = {"anyOf": [
_QUERY_FILTER_NODE,
_object({"field": _STRING, "op": _FILTER_OP, "value": {}},
("field", "op"), additionalProperties=False),
_object({"field": _STRING, "operator": {"enum": ["="]}, "value": {}},
("field", "operator"), additionalProperties=False),
]}
_CLIENT_QUERY_NODES = {"type": "array", "minItems": 1, "maxItems": 50,
"items": _CLIENT_QUERY_LEAF}
_CLIENT_QUERY_FILTER = {"anyOf": [
_QUERY_FILTER, _CLIENT_QUERY_LEAF, _CLIENT_QUERY_NODES,
*[_object({conj: _CLIENT_QUERY_NODES}, (conj,), additionalProperties=False)
for conj in ("and", "or")],
_object(additionalProperties={"type": ["string", "number", "boolean", "null"]}),
]}
FILTER_NODE_REF = "#/$defs/filter_node"
FILTER_NODE_EXAMPLE = {"conj": "and", "nodes": [
{"colId": "status", "op": "eq", "value": "open"},
{"conj": "or", "children": [{"colId": "amount", "op": "gt", "value": 100},
{"colId": "region", "op": "eq", "value": "West"}]},
]}
def _is_filter_node(value):
"""Tell a published query filter node from any other schema."""
branches = value.get("oneOf") if isinstance(value, dict) and set(value) == {"oneOf"} else None
first = branches[0] if isinstance(branches, list) and branches and isinstance(branches[0], dict) else {}
properties = first.get("properties")
return isinstance(properties, dict) and "colId" in properties and "op" in properties
def guide_schema(schema):
"""Return a guide copy with each query filter node replaced by one reference, and whether any was."""
if _is_filter_node(schema):
return {"$ref": FILTER_NODE_REF}, True
if isinstance(schema, list):
pairs = [guide_schema(item) for item in schema]
return [item for item, _ in pairs], any(used for _, used in pairs)
if not isinstance(schema, dict):
return copy.deepcopy(schema), False
pairs = {key: guide_schema(child) for key, child in schema.items()}
return {key: item for key, (item, _) in pairs.items()}, any(used for _, used in pairs.values())
def filter_node_guide():
"""Return the one markdown section that defines the filter_node reference in a guide."""
node = {"oneOf": [guide_schema(branch)[0] for branch in _QUERY_FILTER_NODE["oneOf"]]}
return ("## filter_node\n\n"
"A filter_node is a condition {colId, op, ...}, a group {conj, children: 1 to 50 filter_node}, "
"or a link {link, where}.\n"
"Groups nest at most two deep inside one node. A link's where holds no link.\n"
"Example filter: `" + json.dumps(FILTER_NODE_EXAMPLE, ensure_ascii=True) + "`\n"
"```json\n" + json.dumps({"$defs": {"filter_node": node}}, ensure_ascii=True, indent=2) + "\n```")
_BINDING = _object({
"database": _STRING,
"aggregate": _object({"fn": _STRING, "field": _STRING, "metric_key": _STRING}),
"scope": _object({"filter": _FILTER, "view_id": _STRING, "view_name": _STRING}),
"period": _object({"unit": _STRING, "start": _STRING}),
"ending": {"type": "object"},
}, ("database", "aggregate", "scope"))
_RANGE = {"oneOf": [_STRING, _object({key: {"type": "integer", "minimum": 0}
for key in ("col0", "row0", "col1", "row1")},
("col0", "row0", "col1", "row1"))]}
# S67-T18: an in-cell sparkline, `{kind, range}` exactly as `sheets.py::_clean_sparkline` stores
# it; the range is a rectangle on the SAME tab (an A1 range such as "B2:M2", or the dict).
_SPARKLINE = _object({"kind": {"enum": list(sheets.SPARKLINE_KINDS)}, "range": _RANGE},
("kind", "range"))
_CELL = _object({"kind": {"enum": ["literal", "formula", "bound", "empty"]},
"value": {}, "text": _STRING, "formula": _STRING, "state": _STRING,
"style": _STYLE, "validation": _VALIDATION, "binding": _BINDING,
"sparkline": _SPARKLINE}, ("kind",))
_FIGURE_SOURCES = {
"type": "object",
"description": (
"Optional fields used together for each typed number, keyed by cell address. "
"List fields from both databases when the figure needs a join. "
"The preview names the join limit only when at least two visible databases are listed "
"and the workbook already reads one of them. This does not create a binding or a join."
),
"additionalProperties": {
"type": "array", "minItems": 1, "maxItems": 8,
"items": _object({"database": _STRING, "field": _STRING}, ("database", "field"),
additionalProperties=False),
},
}
# S67-T13: a colour scale's two ends and what each end means. `sheets.py::_clean_rules` refuses
# a type outside `SHEET_RULE_SCALE_TYPES`; the enum publishes the same list to the caller.
_SCALE_TYPE = {"enum": list(sheets.SHEET_RULE_SCALE_TYPES)}
_RULE = _object({"id": _STRING, "range": _RANGE, "kind": _STRING, "op": _STRING,
"value": {}, "value2": {}, "formula": _STRING, "style": _STYLE,
"scale": _object({"low": _STRING, "high": _STRING,
"minType": _SCALE_TYPE, "maxType": _SCALE_TYPE})},
("range", "op"))
_CHART = _object({"id": _STRING, "kind": _STRING, "range": _RANGE, "title": _STRING,
"stacking": _STRING, "source": {"type": "object"},
"series": {"type": "array", "items": {"type": "object"}},
"geometry": {"type": "object"}, "anchor": {"type": "object"},
"settings": {"type": "object"}}, ("kind", "range"))
_TARGET = {"workbook_id": _STRING,
"tab": {"type": "string", "description": "Tab id or tab name."},
"rev": {"type": "integer", "description": "Optional expected workbook revision."}}
_VIEW_CONFIG = _object({
"filters": {"oneOf": [_FILTER, {"type": "array", "items": {"type": "object"}}]},
"filterConj": {"enum": ["and", "or"]},
"sorts": {"type": "array", "items": _object({
"colId": _STRING, "dir": {"enum": ["asc", "desc"]},
"locked": {"type": "boolean"}}, ("colId", "dir"))},
"groupBy": {"type": ["string", "null"]},
"visible": {"type": "array", "items": _STRING,
"description": "Fields to show. Fields omitted from this list are hidden."},
"order": {"type": "array", "items": _STRING},
"display": _object({"mode": _STRING}, ("mode",)),
}, description="The View's filters, sorting, visible fields and display settings.")
_APPLICATION = {
"app_id": {"type": "string", "minLength": 1,
"description": "Omit to create. Supply an id to update mutable properties."},
"name": _STRING,
# Mirrors application_registry.APPLICATION_TYPES (core may not import the API layer).
"type": {"enum": ["chart", "route", "map", "code"]},
"compatibleDatabases": {"type": "array", "items": _STRING},
"sourceDatabase": {"type": "string", "description": "Source for a template Application only."},
# Mirrors aios-web/api/app_templates.TEMPLATES kinds; api_app_templates holds the parity.
"templateKind": {"enum": ["route", "catalog", "competitor_watch", "ranking_diagnostic",
"amazon_operator", "restock_planner", "reimbursements", "aged_stock",
"opportunity_finder", "returns_refunds", "repeat_buyers", "payouts", "account_health",
"amazon_dashboard", "amazon_profit", "alerts", "reports"]},
"fieldMap": {"type": "object", "additionalProperties": _STRING},
"navPlacement": _object({"section": {"const": "app"},
"order": {"type": "integer", "minimum": 0, "maximum": 999}},
("section", "order"), additionalProperties=False),
"semanticRoleBindings": {"type": "object"},
"config": _object({"schemaVersion": {"const": 1},
"display": _object({"mode": _STRING}, ("mode",))},
("schemaVersion", "display"), additionalProperties=False),
"share": _object({"mode": {"enum": ["private", "users", "tenant"]},
"users": {"type": "array", "items": _STRING}}, ("mode",)),
"starred": {"type": "boolean"},
}
def _schema(properties, required=(), *, tab=True):
return _object({**_TARGET, **properties},
("workbook_id", *(("tab",) if tab else ()), *required),
additionalProperties=False)
def _book(ctx, args, *, write=False):
book = sheets.get_workbook(ctx.session.runtime, args["workbook_id"])
(ctx.require_editor if write else ctx.require_reader)(book)
return book
def _saved(book, tab=None):
out = {"id": book["id"], "rev": book["rev"],
"tabs": [{"id": row["id"], "name": row["name"]} for row in book["sheets"]]}
if tab is not None:
out["tab"] = tab
return out
def _put(ctx, args, book, tab=None):
# Carry the revision read for this document patch even when the client omitted one.
# Never overwrite an intervening UI save with the older snapshot we just patched.
if "rev" in args:
book["rev"] = args["rev"]
return _saved(sheets.put_workbook(ctx.session.runtime, book["id"], book,
actor=ctx.session.uname, actor_name=ctx.actor_name), tab)
def _create(ctx, args):
return _saved(sheets.create_workbook(ctx.session.runtime, name=args["name"],
created_by=ctx.session.uname,
on_duplicate="refuse"))
def _add_tab(ctx, args):
book = _book(ctx, args, write=True)
#: ⛔ A DUPLICATE TAB NAME IS REFUSED, because every other tool addresses a tab by `tab`,
#: which `sheets.find_tab` resolves BY NAME to the FIRST match. Two tabs sharing a name make
#: every later write land on one of them and the other unreachable, with nothing reported.
#: Measured at SDLC 61's C7 build: a retried `sheet_add_tab` produced a second
#: `Summary (All)` and the workbook's cells split silently across the pair.
wanted = str(args["name"]).strip()
if any(str(sheet.get("name") or "").strip().casefold() == wanted.casefold()
for sheet in (book.get("sheets") or ())):
raise sheets.SheetRefusal(
"tab_name_taken",
"Tab " + wanted + " is already in this workbook. Choose another name.")
tab = {"id": write_execution.new_id()[:12], "name": args["name"], "cells": {}}
book["sheets"].append(tab)
return _put(ctx, args, book, tab["id"])
def _delete_tab(ctx, args):
book = _book(ctx, args, write=True)
tab = sheets.find_tab(book, args["tab"])
if len(book["sheets"]) <= 1:
raise sheets.SheetRefusal(
"tab_last", "A workbook keeps at least one sheet, so this one cannot be deleted.")
book["sheets"] = [row for row in book["sheets"] if row["id"] != tab["id"]]
return _put(ctx, args, book)
def _save_template(ctx, args):
book = ctx.project_book(_book(ctx, args))
record = sheet_templates.save_sheet_template(
ctx.session.runtime, book, args["key"], name=args["name"],
description=args["description"], created_by=ctx.session.uname, actor_name=ctx.actor_name)
return {"template": record}
def _read_cells(ctx, args):
book = ctx.project_book(_book(ctx, args))
tab = sheets.find_tab(book, args["tab"])
if "addresses" in args:
wanted = {addr.strip().upper() for addr in args["addresses"]}
tab = {**tab, "cells": {addr: cell for addr, cell in tab["cells"].items() if addr in wanted}}
return {"id": book["id"], "rev": book["rev"], "tab": tab}
def _patch(ctx, args, cells, *, resolved=None):
out = sheets.patch_cells(ctx.session.runtime, args["workbook_id"], args["tab"], cells,
expect_rev=args.get("rev"), actor=ctx.session.uname,
actor_name=ctx.actor_name)
if resolved is not None:
for addr in out["accepted"]:
cell = cells[addr]
ctx.cache_binding(args["workbook_id"], out["tab"], addr, cell["binding"], resolved)
# The UI computes after its patch too. Project before computing so a shared workbook
# never returns its owner's cached database numbers through an editor's write response.
book = ctx.project_book(_book(ctx, args))
tab = sheets.find_tab(book, args["tab"])
out["computed"] = {addr: cell for addr, cell in tab["cells"].items()
if cell.get("kind") in ("formula", "bound") or "spillParent" in cell}
return out
def _cell_figure_hints(ctx, args, book):
"""Suggest visible source fields without inferring where a typed number came from."""
numbers = [addr.strip().upper() for addr, cell in args["cells"].items()
if isinstance(cell, dict) and cell.get("kind") == "literal"
and type(cell.get("value")) in (int, float)]
if not numbers:
return []
target = sheets.find_tab(book, args["tab"])
databases = []
for tab in [target, *(tab for tab in book["sheets"] if tab["id"] != target["id"])]:
bindings = [cell.get("binding") for cell in tab["cells"].values()
if isinstance(cell, dict) and cell.get("kind") == "bound"]
for binding in [*bindings, tab.get("pivot")]:
database = binding.get("database") if isinstance(binding, dict) else None
if database and database not in databases:
databases.append(database)
if not databases:
return []
schemas = {}
def describe(database):
if database not in schemas:
try:
schemas[database] = ctx.describe_database(database)
except Exception:
# Optional advice omits unavailable metadata and never grants access or blocks a write.
schemas[database] = {}
return schemas[database]
numeric_types = {"int", "integer", "float", "number", "decimal", "currency", "percent", "rating"}
candidate = None
for database in databases:
schema = describe(database)
field = next((field for field in schema.get("fields", [])
if field.get("type") in numeric_types and field.get("label")), None)
if schema.get("label") and field:
candidate = (schema["label"], field["label"])
break
if candidate is None:
return []
sources = {addr.strip().upper(): refs for addr, refs in (args.get("figure_sources") or {}).items()}
hints = []
for addr in numbers:
declared = {}
for ref in sources.get(addr, []):
schema = describe(ref["database"])
field = next((field for field in schema.get("fields", [])
if field.get("key") == ref["field"] and field.get("label")), None)
if schema.get("label") and field:
declared[ref["database"]] = f'"{field["label"]}" in "{schema["label"]}"'
if len(declared) >= 2 and set(declared).intersection(databases):
hint = ("The declared figure uses " + " and ".join(declared.values()) + ". "
"A cell database cannot join two databases yet. "
"Use separate cell databases and a calculation object, or confirm a typed assumption.")
else:
database_label, field_label = candidate
hint = (f'This workbook reads "{database_label}". Use a cell database with "{field_label}" '
f'in "{database_label}" when it supplies this figure. You can still confirm a typed assumption.')
if hint not in hints:
hints.append(hint)
return hints
def _write_cells(ctx, args):
book = _book(ctx, args, write=True)
if "figure_sources" in args:
validate_arguments(args["figure_sources"], _FIGURE_SOURCES)
hints = _cell_figure_hints(ctx, args, book)
out = _patch(ctx, args, args["cells"])
if hints:
out["warnings"] = hints
return out
def _set_binding(ctx, args):
book = _book(ctx, args, write=True)
tab = sheets.find_tab(book, args["tab"])
resolved = ctx.resolve_binding(args["binding"], book, tab["id"])
addr = args["addr"].strip().upper()
before = tab["cells"].get(addr) or {}
cell = {key: copy.deepcopy(before[key]) for key in ("style", "validation", "link", "note")
if key in before}
cell.update(kind="bound", binding=copy.deepcopy(args["binding"]), state="fresh",
value=resolved["value"],
display="" if resolved["value"] is None else str(resolved["value"]))
out = _patch(ctx, args, {addr: cell}, resolved=resolved)
out["limits"] = resolved.get("limits") or []
return out
def _cell_property(ctx, args, property_name, values):
book = _book(ctx, args, write=True)
tab = sheets.find_tab(book, args["tab"])
cells = {}
for addr, value in values.items():
cell = copy.deepcopy(tab["cells"].get(addr.strip().upper()) or {"kind": "empty"})
cell[property_name] = value
cells[addr] = cell
return _patch(ctx, args, cells)
def _dimensions(ctx, args):
book = _book(ctx, args, write=True)
tab = sheets.find_tab(book, args["tab"])
tab.update(copy.deepcopy(args["dimensions"]))
return _put(ctx, args, book, tab["id"])
def _freeze(ctx, args):
book = _book(ctx, args, write=True)
tab = sheets.find_tab(book, args["tab"])
tab["freeze"] = args["freeze"]
return _put(ctx, args, book, tab["id"])
def _rule(ctx, args):
book = _book(ctx, args, write=True)
tab = sheets.find_tab(book, args["tab"])
tab["rules"] = [*(tab.get("rules") or []), args["rule"]]
return _put(ctx, args, book, tab["id"])
def _chart(ctx, args):
book = _book(ctx, args, write=True)
tab = sheets.find_tab(book, args["tab"])
chart = copy.deepcopy(args["chart"])
chart.setdefault("id", write_execution.new_id()[:12])
tab["charts"] = [row for row in tab.get("charts", []) if row["id"] != chart["id"]] + [chart]
return {**_put(ctx, args, book, tab["id"]), "chart_id": chart["id"]}
def _structure(ctx, args):
book = _book(ctx, args, write=True)
tab = sheets.find_tab(book, args["tab"])
shifted = sheet_structure.apply_structure(book, tab["id"], args["op"])
return _put(ctx, args, shifted, tab["id"])
def _tool(description, schema, handler, *, write=False, grants=("ai_apps", "sheets")):
return {"description": description, "inputSchema": schema, "write": write,
"grants": grants, "handler": handler}
def _callback(ctx, name, args):
callback = getattr(ctx, name, None)
if not callable(callback):
raise sheets.SheetRefusal(
"tool_capability_unsupported",
"This action is not available yet.")
return callback(args)
_DATABASE_ACTIONS = (
"list", "describe", "query", "record_create", "record_update", "record_delete",
"field_create", "field_update", "field_delete", "rename",
)
_VIEW_ACTIONS = ("upsert", "delete", "share_preview")
_SHEET_OBJECT_ACTIONS = {
"workbook_object": ("list", "get", "create", "named_ranges", "export"),
"worksheet_object": (
"list", "get", "add", "update", "delete", "geometry", "hidden_bands",
"freeze", "gridlines", "named_ranges",
),
"range_object": (
"read", "write_values", "write_formulas", "write_styles", "number_format",
"fill_down", "clear",
),
"calculation_object": ("calculate", "refresh"),
}
def _object_tool(name, actions):
target = {
"workbook_id": _STRING,
"worksheet": _STRING,
"range": _RANGE,
"revision": {"type": "integer"},
"arguments": {"type": "object"},
}
return _tool(
"Read or change a Sheet "
+ name.removesuffix("_object") + ".",
_object({"action": {"enum": list(actions)}, **target}, ("action",),
additionalProperties=False),
lambda ctx, args, object_name=name: _callback(
ctx, "sheet_object", {"object": object_name.removesuffix("_object"), **args}),
# The action classifier below distinguishes reads. Declaring the surface write-capable
# keeps the API's literal allow-list fail closed when a new action is added.
write=True,
)
TOOLS = {
"sheet_create_workbook": _tool("Create a private workbook with one initial tab.",
_object({"name": _STRING}, ("name",), additionalProperties=False), _create, write=True),
"sheet_add_tab": _tool("Add a blank tab to a workbook.",
_schema({"name": _STRING}, ("name",), tab=False), _add_tab, write=True),
"sheet_delete_tab": _tool(
"Preview deleting a tab by id or name; requires typed confirmation. The last tab cannot be deleted.",
_schema({}), _delete_tab, write=True),
"sheet_read_cells": _tool("Read a tab's styles, sizes and figures that you have permission to see.",
_schema({"addresses": {"type": "array", "items": _ADDRESS}}), _read_cells),
"sheet_write_cells": _tool("Write cell documents with values or formulas; null deletes a cell. " + CELL_FIGURE_RULE,
_schema({"cells": {"type": "object", "additionalProperties": {"anyOf": [_CELL, {"type": "null"}]}},
"figure_sources": _FIGURE_SOURCES},
("cells",)), _write_cells, write=True),
"sheet_set_binding": _tool("Set and resolve a Cell database binding, including filter valueRef cells.",
_schema({"addr": _ADDRESS, "binding": _BINDING}, ("addr", "binding")), _set_binding, write=True),
"sheet_set_styles": _tool("Replace styles by address, preserving cell content; null clears a style.",
_schema({"styles": {"type": "object", "additionalProperties": {"anyOf": [_STYLE, {"type": "null"}]}}},
("styles",)), lambda ctx, args: _cell_property(ctx, args, "style", args["styles"]), write=True),
"sheet_set_dimensions": _tool("Replace supplied geometry fields; widths use letters and row indices start at one.",
_schema({"dimensions": _object({
"cols": {"type": "integer"}, "rows": {"type": "integer"},
"colWidths": {"type": "object", "additionalProperties": {"type": "number"}},
"rowHeight": {"type": "number"},
"rowHeights": {"type": "object", "additionalProperties": {"type": "number"}},
"hiddenRows": {"type": "array", "items": {"type": "integer"}},
"hiddenCols": {"type": "array", "items": {"type": "integer"}},
}, additionalProperties=False)}, ("dimensions",)), _dimensions, write=True),
"sheet_set_validation": _tool("Replace dropdown lists by address, preserving content; null clears validation.",
_schema({"validations": {"type": "object", "additionalProperties": {
"anyOf": [_VALIDATION, {"type": "null"}]}}}, ("validations",)),
lambda ctx, args: _cell_property(ctx, args, "validation", args["validations"]), write=True),
"sheet_set_freeze": _tool("Set frozen row and column counts, using zero to unfreeze.",
_schema({"freeze": _object({"rows": {"type": "integer"}, "cols": {"type": "integer"}})},
("freeze",)), _freeze, write=True),
"sheet_add_rule": _tool("Add a conditional formatting rule to a Sheet.",
_schema({"rule": _RULE}, ("rule",)), _rule, write=True),
"sheet_set_chart": _tool("Add a chart or replace a chart with the same id using the app's chart document.",
_schema({"chart": _CHART}, ("chart",)), _chart, write=True),
"sheet_structure": _tool("Insert or delete rows or columns and reanchor references; positive delta inserts.",
_schema({"op": _object({"axis": {"enum": ["row", "col"]},
"at": {"type": "integer", "minimum": 1}, "delta": {"type": "integer"}},
("axis", "at", "delta"))}, ("op",)), _structure, write=True),
"sheet_save_template": _tool("Save a readable workbook as a tenant Sheet template with its Cell databases.",
_object({key: _STRING for key in ("workbook_id", "key", "name", "description")},
("workbook_id", "key", "name", "description"), additionalProperties=False),
_save_template, write=True),
"list_databases": _tool("List the databases you can access or see in Loopable.",
_object({"limit": {"type": "integer", "minimum": 1,
"description": "Optional client hint. The complete permitted database list is returned."}},
additionalProperties=False), lambda ctx, args: ctx.list_databases(), grants=("ai_apps",)),
"describe_database": _tool("Read the fields you have permission to use in a database.",
_object({"database": _STRING}, ("database",), additionalProperties=False),
lambda ctx, args: ctx.describe_database(args["database"]), grants=("ai_apps",)),
"query_database": _tool(
"Query currently permitted records with bounded filters, stable sorts, cursor pagination and aggregates.",
_object({
"database": _STRING,
"fields": {"type": "array", "items": _STRING, "maxItems": 200},
"filters": _CLIENT_QUERY_FILTER,
"sorts": {"type": "array", "maxItems": 8, "items": _object({
"field": _STRING, "direction": {"enum": ["asc", "desc"]},
}, ("field", "direction"), additionalProperties=False)},
"limit": {"type": "integer", "minimum": 1, "maximum": 500},
"cursor": _STRING,
"aggregates": {"type": "array", "maxItems": 16, "items": _object({
"fn": {"enum": ["count", "count_distinct", "sum", "avg", "min", "max"]},
"field": _STRING,
}, ("fn",), additionalProperties=False)},
# S68-T55 (COMBINE-AMEND): the MCP door asks for the same SKU x month groups the REST
# door answers (`routes_external._normalise_query_groups`): 1 to 4 field keys, or a
# field with unit month.
"groupBy": {"type": "array", "minItems": 1, "maxItems": 4, "items": {"oneOf": [
_STRING,
_object({"field": _STRING, "unit": {"enum": ["month"]}}, ("field",),
additionalProperties=False),
]}},
"group_by": {"type": "array", "minItems": 1, "maxItems": 4,
"items": _STRING, "description": "Client alias for groupBy. Send only one spelling."},
}, ("database",), additionalProperties=False),
lambda ctx, args: ctx.query_database(args["database"], {
key: value for key, value in args.items() if key != "database"
}), grants=("ai_apps",)),
"view_upsert": _tool(
"Create or update a saved View using the grid's filters, sorts, groupBy, visible fields and display config.",
_object({"database": _STRING, "view": _object({
"id": {"type": "string", "minLength": 1, "maxLength": 120},
"name": {"type": "string", "minLength": 1, "maxLength": 120},
"config": _VIEW_CONFIG, "note": _STRING}, ("id", "name", "config"),
additionalProperties=False)},
("database", "view"), additionalProperties=False),
lambda ctx, args: ctx.view_event("view_upsert", args), write=True, grants=("ai_apps",)),
"view_share_preview": _tool(
"Check who could see a personal draft View and its fields before sharing it.",
_object({
"database": _STRING,
"view_id": _STRING,
"entries": {"type": "array", "maxItems": 200, "items": _object({
"user": _STRING, "role": {"enum": ["view", "edit"]},
}, ("user", "role"), additionalProperties=False)},
}, ("database", "view_id", "entries"), additionalProperties=False),
lambda ctx, args: ctx.view_share_preview(args), grants=("ai_apps",)),
"view_delete": _tool("Delete a saved View when you have permission to do so.",
_object({"database": _STRING, "view_id": _STRING}, ("database", "view_id"),
additionalProperties=False),
lambda ctx, args: ctx.view_event("view_delete", args), write=True, grants=("ai_apps",)),
"app_upsert": _tool(
"Create an App with name, type, compatibleDatabases and config, or update it by app_id. Identity fields are immutable.",
_object(_APPLICATION, additionalProperties=False,
anyOf=[{"required": ["app_id"]}, {"required": ["name", "type", "config"]},
{"required": ["name", "templateKind", "sourceDatabase", "fieldMap", "navPlacement"]}]),
lambda ctx, args: ctx.app_upsert(args), write=True, grants=("ai_apps",)),
"app_delete": _tool("Delete an App. confirmName must match its current name exactly.",
_object({"app_id": _STRING, "confirmName": _STRING}, ("app_id", "confirmName"),
additionalProperties=False),
lambda ctx, args: ctx.app_delete(args), write=True, grants=("ai_apps",)),
"database_object": _tool(
"Read a database or preview changes to its records, fields, name or icon, then confirm them. Name and icon changes require an Administrator.",
_object({
"action": {"enum": list(_DATABASE_ACTIONS)},
"database": _STRING,
"name": _STRING,
"icon": {"oneOf": [{"type": "null"}, _object({
"shape": _STRING, "tone": _STRING,
}, ("shape", "tone"), additionalProperties=False)]},
"record_id": {"oneOf": [_STRING, {"type": "integer"}]},
"values": {"type": "object"},
"field": {"type": "object"},
"field_key": _STRING,
"query": {"type": "object"},
}, ("action",), additionalProperties=False),
lambda ctx, args: _callback(ctx, "database_object", args), write=True,
grants=("ai_apps",)),
"view_object": _tool(
"Read a personal View or preview changes to it, then confirm them.",
_object({
"action": {"enum": list(_VIEW_ACTIONS)}, "database": _STRING,
"view": {"type": "object"}, "view_id": _STRING,
"entries": {"type": "array", "items": {"type": "object"}, "maxItems": 200},
}, ("action", "database"), additionalProperties=False),
lambda ctx, args: (
ctx.view_event("view_upsert", {"database": args["database"], "view": args["view"]})
if args["action"] == "upsert" else
ctx.view_event("view_delete", {"database": args["database"],
"view_id": args["view_id"]})
if args["action"] == "delete" else
ctx.view_share_preview({"database": args["database"],
"view_id": args["view_id"],
"entries": args.get("entries") or []})
), write=True, grants=("ai_apps",)),
**{name: _object_tool(name, actions)
for name, actions in _SHEET_OBJECT_ACTIONS.items()},
"validated_batch": _tool(
"Validate a bounded object-operation batch; writes preview and confirm as one unit.",
_object({"operations": {"type": "array", "minItems": 1, "maxItems": 50,
"items": _object({"name": _STRING,
"arguments": {"type": "object"}},
("name", "arguments"),
additionalProperties=False)}},
("operations",), additionalProperties=False),
lambda ctx, args: _callback(ctx, "validated_batch", args), write=True,
grants=("ai_apps",)),
"confirm_change": _tool(
"Confirm a current preview and keep its operation_id. Repeating it returns the first result. "
"Read data.operation_status with that id if a response is lost. Deletions and field changes need the preview's exact phrase.",
_object({"confirmation_token": _STRING, "confirmation_phrase": _STRING},
("confirmation_token",), additionalProperties=False),
lambda ctx, args: _callback(ctx, "confirm_change", args), write=True, grants=("ai_apps",)),
}
_OBJECT_READ_ACTIONS = {
"database_object": frozenset({"list", "describe", "query"}),
"view_object": frozenset({"share_preview"}),
"workbook_object": frozenset({"list", "get", "export"}),
"worksheet_object": frozenset({"list", "get"}),
"range_object": frozenset({"read"}),
"calculation_object": frozenset(),
}
def build_kind(name):
"""Derive a flat tool's build kind without accepting a caller's kind."""
if name.startswith("view_"):
return "view"
if name.startswith("app_"):
return "app"
if name.startswith("sheet_") or name in _SHEET_OBJECT_ACTIONS:
return "workbook"
return {"database_object": "database", "validated_batch": "batch"}.get(name, name)
for _name, _entry in TOOLS.items():
if _entry["write"] and _name != "confirm_change":
_entry["buildKind"] = build_kind(_name)
_entry["inputSchema"]["properties"]["about"] = copy.deepcopy(ABOUT_SCHEMA)
def is_write(name, arguments) -> bool:
"""Classify dynamic object tools without trusting an unvalidated action as a read."""
tool = TOOLS.get(name) if isinstance(name, str) else None
if tool is None or name == "confirm_change":
return False
if name == "validated_batch":
operations = arguments.get("operations") if isinstance(arguments, dict) else None
if not isinstance(operations, list):
return True
return any(is_write(item.get("name"), item.get("arguments"))
for item in operations if isinstance(item, dict))
if name in _OBJECT_READ_ACTIONS:
action = arguments.get("action") if isinstance(arguments, dict) else None
return action not in _OBJECT_READ_ACTIONS[name]
return bool(tool.get("write"))
def validate_query_filter_operators(filters):
"""Refuse unknown source or target operators before permission or data access."""
stack, seen = [filters], set()
while stack:
node = stack.pop()
if not isinstance(node, (dict, list)) or id(node) in seen:
continue
seen.add(id(node))
if isinstance(node, list):
stack.extend(node)
continue
if "op" in node and (not isinstance(node["op"], str) or node["op"] not in FILTER_OPS):
raise sheets.SheetRefusal(
"tool_arguments_invalid", "Filter op must be one of: " + ", ".join(sorted(FILTER_OPS)) + ".")
stack.extend(node[key] for key in ("nodes", "children", "where") if key in node)
def validate_arguments(arguments, schema, *, named=False):
"""Validate grouped arguments against the selected action's JSON schema."""
if isinstance(arguments, dict) and "filters" in schema.get("properties", {}):
validate_query_filter_operators(arguments.get("filters"))
def equal(value, expected):
if isinstance(value, bool) != isinstance(expected, bool):
return False
if isinstance(value, dict) and isinstance(expected, dict):
return value.keys() == expected.keys() and all(equal(value[key], expected[key]) for key in value)
if isinstance(value, list) and isinstance(expected, list):
return len(value) == len(expected) and all(equal(a, b) for a, b in zip(value, expected))
return value == expected
def matches(value, rule, depth=0):
if depth > 40:
return False
if isinstance(rule, bool):
return rule
kinds = {
"object": isinstance(value, dict), "array": isinstance(value, list),
"string": isinstance(value, str), "boolean": type(value) is bool,
"integer": type(value) is int,
"number": type(value) is int or type(value) is float and math.isfinite(value),
"null": value is None,
}
kind = rule.get("type")
if kind is not None and not any(kinds.get(item, False) for item in (
kind if isinstance(kind, list) else [kind])):
return False
if "enum" in rule and not any(equal(value, choice) for choice in rule["enum"]):
return False
if "const" in rule and not equal(value, rule["const"]):
return False
for key in ("anyOf", "oneOf", "allOf"):
if key in rule:
count = sum(matches(value, child, depth + 1) for child in rule[key])
if (key == "anyOf" and not count or key == "oneOf" and count != 1
or key == "allOf" and count != len(rule[key])):
return False
if isinstance(value, dict):
if set(rule.get("required", ())) - value.keys():
return False
properties = rule.get("properties", {})
for key, child in value.items():
if not matches(child, properties.get(key, rule.get("additionalProperties", True)), depth + 1):
return False
if isinstance(value, list):
if not rule.get("minItems", 0) <= len(value) <= rule.get("maxItems", float("inf")):
return False
if any(not matches(child, rule.get("items", True), depth + 1) for child in value):
return False
if isinstance(value, str):
if not rule.get("minLength", 0) <= len(value) <= rule.get("maxLength", float("inf")):
return False
if "pattern" in rule and not re.search(rule["pattern"], value):
return False
if type(value) in (int, float):
if type(value) is float and not math.isfinite(value):
return False
if not rule.get("minimum", -float("inf")) <= value <= rule.get("maximum", float("inf")):
return False
return True
def problems(value, rule, path="arguments"):
if matches(value, rule):
return []
if not isinstance(rule, dict):
return [path]
for branch in ("anyOf", "oneOf"):
if branch in rule:
candidates = [problems(value, child, path) for child in rule[branch]]
return min(candidates, key=lambda found: (
-max((item.count(".") + item.count("[") for item in found), default=0), len(found)))
if isinstance(value, dict) and rule.get("type") == "object":
properties = rule.get("properties", {})
found = [path + "." + key for key in rule.get("required", ()) if key not in value]
for key, child in value.items():
found.extend(problems(child, properties.get(key, rule.get("additionalProperties", True)),
path + "." + str(key)))
return found or [path]
if isinstance(value, list) and rule.get("type") == "array":
found = [item for index, child in enumerate(value)
for item in problems(child, rule.get("items", True), f"{path}[{index}]")]
return found or [path]
return [path]
if not isinstance(arguments, dict) or not matches(arguments, schema):
detail = (" Invalid arguments: " + ", ".join(dict.fromkeys(problems(arguments, schema))) + "."
if named else "")
raise sheets.SheetRefusal("tool_arguments_invalid", "Tool arguments must match the published input schema." + detail)
encoded = json.dumps(arguments, ensure_ascii=False, allow_nan=False)
if any(prefix in encoded for prefix in ("lpb1.", "lpo1.", "lpr1.", "lpc1.")):
raise sheets.SheetRefusal("credential_in_arguments", "Tool arguments must not contain credential values.")
def table_read_arguments(name, arguments, schema=None):
"""Admit supported client spellings before the existing scoped Table read."""
schema = schema if schema is not None else TOOLS[name]["inputSchema"]
try:
try:
validate_arguments(arguments, schema, named=True)
except sheets.SheetRefusal as exc:
if name != "query_database" or exc.code != "tool_arguments_invalid":
raise
aggregate = schema["properties"]["aggregates"]
item = aggregate["items"]
# Preserve the semantic code only when aggregate member values cause the schema failure.
transport = {**schema, "properties": {**schema["properties"], "aggregates": {
**aggregate, "items": {**item, "properties": {key: {} for key in item["properties"]}}}}}
validate_arguments(arguments, transport, named=True)
accepted = ", ".join(item["properties"]["fn"]["enum"])
raise sheets.SheetRefusal("query_aggregates_invalid", exc.message + " Use aggregates like "
'[{"fn": "count"}, {"fn": "sum", "field": "<readable field key>"}]. '
"fn must be one of: " + accepted + ". When supplied, field must be a readable field key string.") from exc
args = copy.deepcopy(arguments)
if name != "query_database":
return args
if "group_by" in args:
if "groupBy" in args:
raise sheets.SheetRefusal("tool_arguments_invalid", "Send group_by or groupBy, not both.")
args["groupBy"] = args.pop("group_by")
filters = args.get("filters")
def leaf(node):
validate_arguments({"filters": node}, _object({"filters": _CLIENT_QUERY_LEAF}), named=True)
if "field" not in node:
return node
return {"colId": node["field"], "op": "eq" if "operator" in node else node["op"],
**({"value": node["value"]} if "value" in node else {})}
if isinstance(filters, list):
args["filters"] = [leaf(node) for node in filters]
elif isinstance(filters, dict):
if set(filters) in ({"and"}, {"or"}):
conj = next(iter(filters))
args["filters"] = {"conj": conj, "nodes": [leaf(node) for node in filters[conj]]}
elif set(filters) & {"colId", "field", "op", "operator"}:
args["filters"] = {"conj": "and", "nodes": [leaf(filters)]}
elif not set(filters) & {"conj", "nodes", "link", "where", "children", "and", "or"}:
if not filters or len(filters) > 50:
raise sheets.SheetRefusal("tool_arguments_invalid", "filters must contain 1 to 50 field equality values.")
args["filters"] = {"conj": "and", "nodes": [
{"colId": field, "op": "eq", "value": value} for field, value in filters.items()]}
validate_arguments({"filters": args["filters"]}, _object({"filters": _QUERY_FILTER}), named=True)
return args
except sheets.SheetRefusal as exc:
if exc.code != "tool_arguments_invalid":
raise
example = {"database": "<database>", "filters": {"conj": "and", "nodes": [
{"colId": "<field>", "op": "eq", "value": "<value>"}]}} if name == "query_database" else {}
shape = {"action": name, "arguments": example}
raise sheets.SheetRefusal(exc.code, exc.message + " Call table with " + json.dumps(shape) + ".") from exc
def dispatch(ctx, name, arguments):
"""Dispatch one declared tool. The API translates core and grant refusals to MCP content."""
tool = TOOLS.get(name) if isinstance(name, str) else None
if tool is None:
raise sheets.SheetRefusal("tool_not_found", "The requested tool is not available.")
if name == "query_database":
validate_query_filter_operators(arguments.get("filters") if isinstance(arguments, dict) else None)
for grant in tool["grants"]:
if grant == perm_scope.AI_APPS:
reason = perm_scope.ai_apps_refusal(ctx.session.user, ctx.session.runtime)
if reason:
raise sheets.SheetRefusal("ai_apps_off", reason)
else:
ctx.session.require(grant)
schema = tool["inputSchema"]
if name in {"query_database", "list_databases"}:
arguments = table_read_arguments(name, arguments, schema)
if not isinstance(arguments, dict):
raise sheets.SheetRefusal("tool_arguments_invalid", "Tool arguments must be an object.")
missing = set(schema["required"]) - arguments.keys()
unknown = arguments.keys() - schema["properties"].keys()
if missing or (unknown and schema.get("additionalProperties") is False):
raise sheets.SheetRefusal("tool_arguments_invalid", "Tool arguments must match the published input schema.")
# Validate only the transport shape here. Document semantics belong to the app writers.
for key, value in arguments.items():
property_schema = schema["properties"].get(key, {})
kind = property_schema.get("type")
types = {"object": dict, "array": list, "string": str, "integer": int}
if kind in types and (not isinstance(value, types[kind]) or (kind == "integer" and isinstance(value, bool))):
raise sheets.SheetRefusal("tool_arguments_invalid", f"Tool argument {key} must be an {kind}.")
if "enum" in property_schema and value not in property_schema["enum"]:
raise sheets.SheetRefusal(
"tool_arguments_invalid", f"Tool argument {key} is not a published operation.")
# A dimension patch must never become an arbitrary tab-document replacement.
if name == "sheet_set_dimensions":
allowed = schema["properties"]["dimensions"]["properties"]
if arguments["dimensions"].keys() - allowed.keys():
raise sheets.SheetRefusal("tool_arguments_invalid", "Dimensions may contain only the published geometry fields.")
encoded_arguments = json.dumps(arguments, ensure_ascii=False, allow_nan=False)
if any(prefix in encoded_arguments for prefix in ("lpb1.", "lpo1.", "lpr1.", "lpc1.")):
raise sheets.SheetRefusal("credential_in_arguments", "Tool arguments must not contain credential values.")
return tool["handler"](ctx, arguments)
|