File size: 17,246 Bytes
868b75a
9ad4b87
 
 
 
 
 
7f72224
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
868b75a
 
 
7f72224
 
868b75a
 
 
d598032
 
 
85f794d
 
 
 
 
 
 
d598032
 
 
 
85f794d
d598032
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
85f794d
7f72224
 
d598032
 
 
 
 
 
 
 
9ad4b87
 
 
 
 
 
 
 
 
 
 
 
 
868b75a
 
 
 
 
 
 
 
 
 
d598032
868b75a
 
 
 
9ad4b87
 
85f794d
 
9ad4b87
 
 
 
 
 
 
 
 
81a8217
9ad4b87
 
 
 
 
81a8217
 
 
 
 
 
 
 
 
9ad4b87
 
81a8217
 
868b75a
81a8217
 
9ad4b87
 
 
 
 
 
 
868b75a
9ad4b87
 
 
 
 
 
 
 
 
 
 
 
 
 
b2aa446
 
 
 
 
 
9ad4b87
b2aa446
9ad4b87
 
 
 
 
 
 
 
 
cf09724
 
 
 
 
 
 
 
 
 
 
 
 
 
9ad4b87
868b75a
7f72224
9ad4b87
 
 
 
 
 
 
 
cf09724
 
 
 
 
 
 
 
9ad4b87
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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
"""Table actions with separate read and write operations."""
import json

from core import external_tools
from . import legacy_action


QUERY_PAGING_HELP = (
    'Send limit as a positive integer, for example {"limit": 100}. '
    'A limit of 501 or more requests pages of at most 500 rows. '
    'Send page.next_cursor as cursor with the same query until page.has_more is false. '
    'There is no total row cap.')
DISCOVERY_HELP = (
    "If your client exposes only the previous data tool, send the same action and arguments to that tool. "
    "Refresh or reconnect the client to discover table. Read loopable_guide with group table for its actions."
)
READ_REFUSAL_FIXES = {
    "assistant_snapshot_unavailable": (
        "This database's current data is unavailable. Describe it to read its fields. "
        "Try the same read again after its source sync completes."),
    "assistant_store_not_ready": (
        "This database is temporarily unavailable. Try the same read again shortly."),
    "assistant_cross_tenant_store": (
        "This connection cannot read that workspace. Reconnect to the intended workspace before reading."),
    "assistant_scope_not_applicable": (
        "This database's access restriction could not be applied. Ask an Administrator to repair it. "
        "Do not remove or widen the restriction."),
    "assistant_filter_not_answerable": (
        "This filter cannot be applied. Use only the fields and operators returned by describe_database. "
        "Keep the intended restriction when correcting the filter."),
    "query_group_type": (
        "Month grouping requires a date field. Use a date field returned by describe_database, "
        "or omit unit to group by the field's values."),
}


READ_ARGUMENT_HELP = (
    "Call table with action describe_database or query_database and an arguments object. "
    "arguments.database must be a string. Put query options inside arguments. "
    "fields must be an array of field keys. " + QUERY_PAGING_HELP + " "
    "Read loopable_guide with group table for the selected action's input schema. " + DISCOVERY_HELP
)


#: The one skill.md section every query refusal names.
QUERY_SYNTAX_SECTION = "Query syntax"
QUERY_FILTER_EXAMPLE = {"conj": "and", "nodes": [{"colId": "<field key>", "op": "eq", "value": "<value>"}]}
QUERY_GROUPING_HELP = (
    "Connector mirrors group up to four fields and support month units. Their groups arrive in key order. "
    "A stored Table groups one field without a month unit, using the Table grid's aggregate reader. "
    "Its group order is count descending, then key ascending. "
    "A query_groups_unavailable refusal names its cause. Call table with action query_database "
    "using error.retry_query, without groupBy or aggregates, then follow page.next_cursor."
)
QUERY_SYNTAX_FIX = (
    "Filters use {conj, nodes: [{colId, op, value}]}, for example "
    + json.dumps(QUERY_FILTER_EXAMPLE, separators=(", ", ": ")) + ". "
    "groupBy is a list of 1 to 4 field keys, or {field, unit: month}, with at least one aggregate and no sorts. "
    + QUERY_GROUPING_HELP + " "
    "See " + QUERY_SYNTAX_SECTION + " in skill.md."
)
#: Query refusal codes that carry QUERY_SYNTAX_FIX.
QUERY_SYNTAX_CODES = frozenset({
    "query_arguments_invalid", "query_aggregates_invalid", "query_aggregate_type",
    "query_groups_invalid", "query_groups_unavailable", "assistant_bad_filter",
})
#: S79-T11: a schema-valid filter the read path refuses names the field and operator rules.
BAD_FILTER_FIX = (
    "Use field keys and operators that describe_database returns. "
    "isAnyOf and isNoneOf take values, an array. between takes value and value2."
)
#: S79-T11: an unknown database key names the call that lists keys, then the current query shape.
UNKNOWN_DATABASE_FIX = (
    'No readable database has that key. Call table with {"action": "list_databases"} and use a database key it '
    'returns. Then call table with {"action": "query_database", "arguments": {"database": "<database key>", '
    '"limit": 100}}.'
)


def bad_filter_message(message):
    """Rewrite the read path's filter refusal as one plain sentence plus the field and operator rules."""
    text = " ".join(str(message or "").split()).rstrip(".")
    for prefix, subject in (("assistant filters ", "Filters "), ("assistant filter ", "The filter ")):
        if text.startswith(prefix):
            text = subject + text[len(prefix):]
    text = (text[:1].upper() + text[1:] + ".") if text else "The filter cannot be read."
    return text if BAD_FILTER_FIX in text else text + " " + BAD_FILTER_FIX


def with_query_syntax(message):
    """Append the filter shape, the groupBy rule and the skill.md pointer once."""
    text = str(message or "").rstrip()
    return text if QUERY_SYNTAX_FIX in text else (text + " " + QUERY_SYNTAX_FIX).strip()


#: The measured full product pull and its limit, cause and fix (rule 1). Never a cap.
FULL_PULL_NOTE = (
    "Product data: a full pull reads about 6,000 rows in 12 pages, and each page after the first takes about 2 seconds. "
    "The first page after a server restart can take about 3 minutes, because the product list is built from Odoo "
    "on first use. If that call times out, repeat it. The next call reads the built list and nothing is capped."
)


def query_syntax_section():
    """Return the skill.md section that every query refusal names."""
    example = {"action": "query_database", "arguments": {
        "database": "<visible database key>", "fields": ["<field key>"],
        "filters": QUERY_FILTER_EXAMPLE, "limit": 500}}
    lines = [
        "## " + QUERY_SYNTAX_SECTION, "",
        "Every query refusal names this section. Call table with action query_database:", "",
        "```json", json.dumps(example, indent=2), "```", "",
        "- filters: {conj, nodes: [{colId, op, value}]}. conj is and or or. A node is a condition "
        "{colId, op, value}, or a group {conj, children: [...]}. isAnyOf and isNoneOf take values, an array. "
        "between takes value and value2. A link condition is {link, where}. Filters allow 3 levels, "
        "100 nodes and 50 conditions per group.",
        "- A flat object such as {\"status\": \"open\"}, or a condition with field or operator keys, is refused "
        "with tool_arguments_invalid. Use colId and op.",
        "- op: " + ", ".join(sorted(external_tools.FILTER_OPS)) + ". describe_database names the operators "
        "each field accepts.",
        "- sorts: [{field, direction}], direction asc or desc, at most 8.",
        "- aggregates: [{fn, field}], fn count, count_distinct, sum, avg, min or max, at most 16. "
        "count may omit field. sum and avg need a numeric field (query_aggregate_type otherwise).",
        "- groupBy: 1 to 4 field keys, or {field, unit: month} for a date field. A grouped query needs at least "
        "one aggregate and no sorts (query_groups_invalid otherwise). " + QUERY_GROUPING_HELP,
        "- Paging: default 100. " + QUERY_PAGING_HELP
        + " Only fields, filters, sorts, limit, cursor, aggregates and groupBy are "
        "accepted (query_arguments_invalid otherwise).",
        "- Full pulls: no row cap applies to a connected source. Read every page at limit 500.",
    ]
    if FULL_PULL_NOTE:
        lines.append("- " + FULL_PULL_NOTE)
    return lines + [""]


GROUPING_EXAMPLE = {
    "action": "query_database", "arguments": {
        "database": "ut_odoo_order_lines", "fields": [],
        "groupBy": ["pricelist"],
        "aggregates": [{"fn": "count"}, {"fn": "sum", "field": "price_subtotal"}],
        "limit": 2,
    },
}


def query_guide(rows):
    """Explain query results and offer an example only with readable fields."""
    lines = [
        "## Calling Table", "", READ_ARGUMENT_HELP,
        "Use a database key returned by list_databases. Replace the example key before calling.",
        "```json", json.dumps({"action": "describe_database", "arguments": {
            "database": "<visible database key>",
        }}, indent=2), "```",
        "```json", json.dumps({"action": "query_database", "arguments": {
            "database": "<visible database key>", "limit": 1,
        }}, indent=2), "```",
        "## Filter operators", "",
        "Accepted operator names: " + ", ".join(sorted(external_tools.FILTER_OPS)) + ".",
        QUERY_SYNTAX_FIX,
        "describe_database returns operators and operands for each readable field. "
        "Use eq for equality, gte and lte for inclusive date bounds, and isAnyOf with values for membership. "
        "Date comparisons use value in YYYY-MM-DD format. Membership uses a values array. "
        "Only use operators declared for that field. Combine date bounds in an AND group's nodes.", "",
        "## Grouping and paging", "",
        "Use groupBy with 1 to 4 readable field keys. For calendar months, use an object with "
        "field set to a readable date field's key and unit set to month. "
        "Grouping needs at least one aggregate and no sorts. " + QUERY_GROUPING_HELP,
        "The top-level aggregates cover the whole permitted, filtered result, never one page. "
        "Each groups entry has keys and aggregates for that entire group. "
        "page.returned counts groups on the current page. records is empty for grouped queries. "
        "A null group key means the field has no value. It remains a separate group and counts toward the total.",
        "count without a field counts records. count with a field counts non-null values. "
        "Other functions are count_distinct, sum, avg, min and max. sum and avg need numeric fields.",
        "Use page.next_cursor as cursor with the same arguments to get the next page. "
        "Stop when page.has_more is false. Do not add the repeated top-level aggregates across pages. "
        "Connected database pages use one completed sync. If a sync completes between pages, "
        "query_cursor_stale means run the same query again without a cursor. Call query_database with error.retry_query "
        "for a fresh first page. It keeps the original query and removes only cursor. "
        "Discard earlier pages before collecting the new result. "
        "query_cursor_invalid means the cursor cannot be read. Start again without cursor. "
        "query_source_unavailable means the database is temporarily unavailable. Try the same query again shortly. "
        "If the result or permissions change, start again without a cursor.",
        "## Link filters", "",
        "Describe the source database and use one declared link field per call: "
        '{"link": "<link field key>", "where": {"colId": "<target field>", "op": "eq", "value": "<value>"}}. '
        "Pass it as filters, or put it in an AND filter's nodes beside source conditions. "
        "where is a condition or a children group on the target database. "
        "Both databases and their join fields must be readable by this account and credential. "
        "A link pin intersects the source filter. No matching target members return zero records. "
        "Each call follows one link. Make a second call for the next link. "
        "total_rows counts every matching permitted record. Follow page.next_cursor to collect the result.",
        "Limits: 500 rows or groups per page, default 100. At most 200 selected fields, "
        "16 aggregates and 8 sorts. Filters allow 3 levels, 100 nodes and 50 conditions per group. "
        "These filter limits bound query validation work. If a filter exceeds them, split it into separate queries. "
        "assistant_unknown_field suggests at most 5 close visible field keys. Use a key returned by describe_database. "
        "Use the Table module: call tool table with action describe_database "
        "and arguments containing database set to a visible database key. "
        "The unqualified describe_database call also remains available.", "",
    ]
    args = GROUPING_EXAMPLE["arguments"]
    visible = next((row for row in rows if row["database"] == args["database"]), {})
    keys = {field.get("key") for field in visible.get("fields", [])}
    if (visible.get("answerable") and visible.get("schema", {}).get("source_kind") == "mirror_user_table"
            and {"pricelist", "price_subtotal"} <= keys):
        lines += ["### Example: Odoo sales lines by pricelist", "",
                  "Call table with this JSON. It counts sales lines and sums their amounts using your current access.",
                  "```json", json.dumps(GROUPING_EXAMPLE, indent=2), "```",
                  "Collect every page. Add each group's count aggregate and compare it with the count in this ungrouped call:",
                  "```json", json.dumps({"action": "query_database", "arguments": {
                      "database": args["database"], "fields": [], "aggregates": args["aggregates"], "limit": 1,
                  }}, indent=2), "```",
                  "The grouped and ungrouped counts and sums must agree. A null pricelist is included."]
    else:
        lines += ["The Odoo grouping example needs access to Sales order lines and its Pricelist and Line $ fields. "
                  "This connection cannot currently use that example. Describe a listed database before choosing fields."]
    return "\n".join(lines)


def _report_limitation(ctx, args):
    """Send an AI's bounded question to the team without changing tenant data."""
    from routes_feedback import record_limitation, refusal_code
    # A report about a refusal carries that refusal's code. capability_unavailable is the last resort.
    sent = args.get("code") or None
    found = refusal_code(ctx.session, args["tool"], args["action"], sent)
    internal, public = found or ("", sent or "capability_unavailable")
    # An empty internal code stores the public one: a caller's own code is never recorded as internal.
    recorded = record_limitation(ctx.session, tool=args["tool"], action=args["action"],
                                 code=public, internal_code=internal, question=args["question"])
    return {"recorded": recorded}


def _operation_status(ctx, args):
    """Read the result of this connection's proposed change."""
    from routes_external import _operation_status as read_status
    return read_status(ctx.session, args)


def _delivery_photos(ctx, args):
    """List one Odoo delivery's photos when this credential and account may read that delivery."""
    import odoo_attachments
    from loopy_links import public_base
    from routes_external import _assert_database_scope

    def key_allowed(key):
        _assert_database_scope(ctx.session, key)

    photos = odoo_attachments.list_photos(ctx.session, args["picking_id"], key_allowed=key_allowed,
                                          base=public_base() or "")
    return {"picking_id": args["picking_id"], "photos": photos}


GROUP = {
    "name": "table", "title": "Table",
    "guide": "Table: list databases, read fields, group and page records, propose changes, list an Odoo delivery's photos with delivery_photos, or report a limitation. Read loopable_guide(group='table') for actions and a grouping example. " + DISCOVERY_HELP,
    "actions": {
        "operation_status": {
            "read": True, "destructive": False, "confirm": False,
            "schema": {"type": "object", "properties": {
                "operation_id": {"type": "string", "pattern": "^[a-f0-9]{64}$"},
            }, "required": ["operation_id"], "additionalProperties": False},
            "handler": _operation_status,
        },
        "delivery_photos": {
            "read": True, "destructive": False, "confirm": False,
            "schema": {"type": "object", "properties": {
                "picking_id": {"type": "integer", "minimum": 1, "maximum": 2147483647,
                               "description": "The stock.picking id: odoo_id in Odoo shipments."},
            }, "required": ["picking_id"], "additionalProperties": False},
            "handler": _delivery_photos,
        },
        "report_limitation": {
            "read": True, "destructive": False, "confirm": False,
            "schema": {"type": "object", "properties": {
                "tool": {"type": "string", "minLength": 1, "maxLength": 100},
                "action": {"type": "string", "minLength": 1, "maxLength": 100},
                "code": {"type": "string", "maxLength": 100},
                "question": {"type": "string", "minLength": 1, "maxLength": 500,
                             "description": "Your question in plain words. Omit records, cell values, filters and credentials."},
            }, "required": ["tool", "action", "question"], "additionalProperties": False},
            "handler": _report_limitation,
        },
        "list_databases": legacy_action("list_databases"),
        "describe_database": legacy_action("describe_database"),
        "query_database": legacy_action("query_database"),
        **{"database." + action: legacy_action(
            "database_object", action=action,
            read=action in external_tools._OBJECT_READ_ACTIONS["database_object"],
            destructive=action in {"record_delete", "field_delete"})
           for action in external_tools._DATABASE_ACTIONS},
    },
}