Download platform/harness/semantic.py from fsanyoto/loopable: direct link, hf CLI and curl.
- Browser
- Download file 155 kB
-
https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/harness/semantic.py
- Command line
-
hf download hf://spaces/fsanyoto/loopable/platform/harness/semantic.py
-
curl -L -o semantic.py https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/harness/semantic.py
155 kB
"""harness/semantic.py β the semantic seam (OM-0a, 2026-07-11).
The ONE governed registry of topics + metrics (platform/model/*.yml) and the resolver that
turns a metric key into a number through the SAME proven data layer the validated modules use
(core/odoo domain builders + aggregate helpers) β so scope parity is by construction, and every
metric carries its definition, ai_context, and an independent validate contract.
Consumers (the point of the seam): the app's pages, the metric dictionary (OM-0c), the saved-
dashboard viewer (OM-3; the Explore page was retired 2026-07-23), the AI Analyst's tools (OM-4:
list_topics / describe_topic / run_semantic_query), and the MCP server (OM-6). Plan:
AutoWiki/research/omni-adoption.md (Part IV + the productization addendum).
Design constraints honored (from /odoo-api):
- read_group dot-path GROUPBY fails β scalar aggregates here use sum_field/distinct_count;
grouped queries (OM-3+) must group by direct m2o fields only.
- Scope is never hand-rolled: topics BIND to a named domain builder (sale_line_domain, ...);
the YAML `scope:` block is the declarative statement of what that builder bakes in.
- READ-ONLY on Odoo, always.
"""
import ast
import copy
from contextlib import contextmanager, nullcontext
from contextvars import ContextVar
import datetime as dt
import functools
import operator
import re
from pathlib import Path
import yaml
import core.odoo as O
from core import live_limiter
MODEL_DIR = Path(__file__).resolve().parents[1] / "model"
_STANDARD_METRICS = frozenset({'booked', 'shipped', 'invoiced', 'collected', 'margin',
'invoiced_sales', 'sales'})
_ROUTE_STOP_WORDS = frozenset(
"the a an of for in on at to from by with and or what which who when how much many "
"was were is are do did show me my our us last this that total sum count list give tell".split()
)
def _route_tokens(text):
words = re.findall(r"[^\W_]+", str(text or "").casefold())
return tuple(word[:-1] if len(word) > 2 and word.endswith("s") else word
for word in words if len(word) >= 2 and word not in _ROUTE_STOP_WORDS)
def route_question(question, candidates):
"""Rank semantic vocabulary using strings alone, without loading the model or any data.
ββ A SUBJECT WORD OUTWEIGHS A FIELD WORD, AND THE REASON WAS MEASURED, NOT ASSUMED.
Without it, "How many orders did we confirm in January?" routed to the CUSTOMERS registry:
`odoo_customers` approves a field labelled "Last order", "last" is a stop word, so that term
normalises to the single token "order" and ties exactly with `odoo_orders`' own subject. The
tie then fell to the alphabetical database key, and `customer_data` sorts before
`ut_odoo_orders`. A question about orders reached the customer book because of a sort order.
A candidate's `subject` is what the database IS (its label, its topic's label); a term drawn
entirely from it scores one more than the same term drawn from a field. `subject` is OPTIONAL:
a candidate that declares none scores exactly as it did before, which is what keeps every
hand-built routing fixture meaning what it says.
"""
words = _route_tokens(question)
scored = []
for candidate in candidates:
subject = set()
for term in candidate.get("subject") or ():
subject.update(_route_tokens(term))
terms = {}
for term in candidate.get("terms") or ():
tokens = _route_tokens(term)
if tokens and any(words[start:start + len(tokens)] == tokens
for start in range(len(words) - len(tokens) + 1)):
# Normalised duplicates count once, including singular/plural spellings.
label = str(term).strip().casefold()
terms[tokens] = min(terms.get(tokens, label), label)
matched = sorted(terms, key=lambda term: (-len(" ".join(term)), term))
score = sum((2 if len(term) > 1 else 1) + (1 if subject.issuperset(term) else 0)
for term in matched)
if score:
scored.append({"database": candidate["database"],
"label": candidate.get("label") or candidate["database"],
"score": score, "matched": [terms[term] for term in matched],
"longest": len(" ".join(matched[0]))})
scored.sort(key=lambda row: (-row["score"], -row["longest"], row["database"]))
best = scored[0] if scored else {}
return {"database": best.get("database", ""), "score": best.get("score", 0),
"matched": best.get("matched", []),
"ranked": [{key: row[key] for key in ("database", "label", "score")}
for row in scored]}
# Topic scope binds to a NAMED, reviewed domain builder β never a YAML-assembled domain (one
# source of truth for scope until OM-2 makes topics executable against the store).
def _order_domain(*a, **kw):
# lazy: order scope currently lives in modules/sales.py; OM-2 lifts it into core/the store
import modules.sales as S
return S.order_domain(*a, **kw)
def _customer_move_domain(move_type):
"""A posted customer document domain, matching `modules/returns.py`'s `_mdom` exactly.
β COMPANY-LEVEL, and it REFUSES a team rather than ignoring one. Credit notes all carry
team_id = 1, so a BU filter on the document is meaningless β silently dropping the argument
would hand a business-unit user the company's number under their own name, which is the
widening sin in a different currency.
"""
def build(date_from=None, date_to=None, team_id=None, **kw):
if team_id:
raise ModelError(
f"customer {move_type} documents are COMPANY-LEVEL. Credit notes and invoices "
f"carry no trustworthy business-unit tag, so a team-scoped total cannot be "
f"produced. Refusing rather than returning the company number.")
dom = [("move_type", "=", move_type), ("state", "=", "posted")]
if date_from:
dom.append(("invoice_date", ">=", date_from))
if date_to:
dom.append(("invoice_date", "<=", date_to))
return dom
return build
_BUILDERS = {
"sale_line_domain": O.sale_line_domain,
"sale_order_domain": _order_domain,
"credit_note_domain": _customer_move_domain("out_refund"),
"customer_invoice_domain": _customer_move_domain("out_invoice"),
}
class ModelError(Exception):
"""A model-file problem (unknown key, bad reference, unparseable expr)."""
class NotSyncedError(ModelError):
"""A metric reads mirror fields whose declared Odoo backfill has not finished."""
code = "not_synced"
def __init__(self, metric, sources):
self.metric = str(metric.get("key") or "")
self.sources = list(sources)
super().__init__(
f"{metric.get('label') or self.metric} is not available yet. The Odoo fields it reads "
f"have not finished syncing into this data cache ({', '.join(self.sources)}). "
"Run Sync now on the Odoo connector, or ask again after the next scheduled sync.")
class UnknownColumnError(ModelError):
"""A filter/group request named a column the topic does not declare."""
def __init__(self, message, column, columns=None):
super().__init__(message)
self.column = str(column)
self.columns = sorted({str(item) for item in (columns or [column])})
# ---------------------------------------------------------------- loading
_TENANT_RUNTIME = ContextVar('semantic_tenant_runtime', default=None)
@contextmanager
def tenant_context(runtime):
"""Bind semantic parameters to the admitted runtime and its source identity."""
runtime.assert_datastore_matches()
source = runtime.odoo_source()
if source is None:
raise ModelError('The tenant has no available Odoo source')
key = getattr(runtime, 'key', None)
if getattr(source, '_slug', key) != key:
raise ModelError('The Odoo source belongs to another tenant')
if _TENANT_RUNTIME.get() is runtime:
yield
return
token = _TENANT_RUNTIME.set(runtime)
try:
with source._bound():
yield
finally:
_TENANT_RUNTIME.reset(token)
def _runtime_bound(fn):
@functools.wraps(fn)
def call(*args, **kwargs):
runtime = kwargs.get('runtime')
subject = args[0] if args else kwargs.get('topic', kwargs.get('key'))
if subject in {'amazon_orders', 'amazon_order_count'}:
return fn(*args, **kwargs)
with tenant_context(runtime) if runtime is not None else nullcontext():
return fn(*args, **kwargs)
return call
def _live_oracle_bound(fn):
"""Admit independent semantic reads before tenant binding, preserving explicit offline clients."""
@functools.wraps(fn)
def call(*args, **kwargs):
subject = args[0] if args else kwargs.get('key')
if subject == 'amazon_order_count':
return fn(*args, **kwargs)
runtime = kwargs.get('runtime')
runtime = runtime if runtime is not None else _TENANT_RUNTIME.get()
if runtime is not None and kwargs.get('client') is not None:
raise ModelError('An explicit client cannot override the tenant source binding')
if runtime is None:
# Legacy CLI and offline fixtures have no runtime. Tenant tools pass or bind theirs.
return fn(*args, **kwargs)
# Legacy offline runtimes lack the store protocol that every tenant runtime supplies.
store = all(callable(getattr(runtime, name, None)) for name in ('get', 'revision', 'update'))
admission = (live_limiter.scoped_admit(runtime, caller='semantic.' + fn.__name__)
if store else nullcontext())
with admission:
return fn(*args, **kwargs)
return call
def _tenant():
"""Read the current runtime's configuration or its matching CLI tenant profile."""
runtime = _TENANT_RUNTIME.get()
connection = O._ctx()
slug = (connection or {}).get('slug')
f = MODEL_DIR / 'tenant.yml'
profile = yaml.safe_load(f.read_text(encoding='utf-8')) if f.exists() else {}
profile_slug = str((profile or {}).get('key', '')).replace('_', '-')
if runtime is not None:
key = getattr(runtime, 'key', None)
config = getattr(getattr(runtime, 'tenant', None), 'config', {}) or {}
if 'params' in config:
params = config['params']
if not isinstance(params, dict):
raise ModelError('Tenant parameters must be a mapping')
return {'key': key, 'params': copy.deepcopy(params)}
params = copy.deepcopy(profile.get('params') or {}) if profile_slug == key else {}
business_units = config.get('business_units') or {}
if business_units:
params.update({
'wholesale_team_ids': list(business_units.values()),
'team_names': {value: key for key, value in business_units.items()}})
params.update({key: copy.deepcopy(config[key]) for key in
('wholesale_team_ids', 'team_names', 'excluded_partner_name_like') if key in config})
return {'key': key, 'params': params}
if slug is None or profile_slug == slug:
return profile or {'params': {}}
return {'key': slug, 'params': {}}
def _store_binding(topic):
"""Resolve configured dimension labels without mutating the shared model."""
store = copy.deepcopy(topic['store'])
for dimension in (store.get('dims') or {}).values():
parameter = dimension.get('name_param')
if not parameter:
continue
names = (_tenant().get('params') or {}).get(parameter) or {}
cases = []
for key, label in names.items():
if (isinstance(key, bool) or not re.fullmatch(r'[0-9]+', str(key))
or not isinstance(label, str)):
raise ModelError('Team labels require numeric ids and text names')
cases.append(f'WHEN {int(key)} THEN {_sql_value(label)}')
fallback = f"CAST({dimension['col']} AS VARCHAR)"
dimension['name_col'] = (f"CASE {dimension['col']} {' '.join(cases)} ELSE {fallback} END"
if cases else fallback)
return store
def _sql_value(v):
"""Render a tenant param into SQL safely (params are versioned config, not user input β
but escape anyway): ints as-is, lists comma-joined, strings single-quoted + escaped."""
if isinstance(v, bool):
raise ModelError("boolean tenant params not supported in scope_sql")
if isinstance(v, (int, float)):
return str(v)
if isinstance(v, (list, tuple)):
return ", ".join(_sql_value(x) for x in v)
return "'" + str(v).replace("'", "''") + "'"
def excluded_partner_ids(pattern, cur=None):
"""Resolve the exclusion afresh on the bound tenant mirror."""
con = cur if cur is not None else _store_con()
try:
return sorted(int(row[0]) for row in con.execute(
"SELECT id FROM res_partner WHERE name LIKE ?", [str(pattern)]).fetchall())
finally:
if cur is None:
con.close()
def render_scope(sql, cur=None):
"""Substitute {param} placeholders in a store scope/filter SQL fragment from tenant.yml."""
params = _tenant().get("params") or {}
out = sql
for k, v in params.items():
out = out.replace("{" + k + "}", _sql_value(v))
if "{excluded_partner_ids}" in out:
if params.get("excluded_partner_name_like") is None:
raise ModelError("scope_sql references unknown tenant params: ['excluded_partner_name_like']")
out = out.replace("{excluded_partner_ids}", _sql_value(
excluded_partner_ids(params["excluded_partner_name_like"], cur)))
if "{" in out and "}" in out:
import re as _re
missing = _re.findall(r"\{([a-z_]+)\}", out)
if missing:
raise ModelError(f"scope_sql references unknown tenant params: {missing}")
return out
def _load_model_yaml(text):
"""Parse model documents with the safe C loader when available."""
loader = getattr(yaml, "CSafeLoader", yaml.SafeLoader)
return yaml.load(text, Loader=loader)
@functools.lru_cache(maxsize=1)
def _model():
topics, metrics = {}, {}
for f in sorted((MODEL_DIR / "topics").glob("*.yml")):
d = _load_model_yaml(f.read_text(encoding="utf-8"))
if not d or "key" not in d:
raise ModelError(f"topic file {f.name} lacks a 'key'")
if d.get("domain_builder") and d["domain_builder"] not in _BUILDERS:
raise ModelError(f"topic {d['key']}: unknown domain_builder {d.get('domain_builder')!r}")
# a topic may be STORE-ONLY (no live-path builder yet, e.g. gl_lines) β metric() raises
# a clear error if a live resolution is attempted on it
topics[d["key"]] = d
metric_docs = [(f.name, _load_model_yaml(f.read_text(encoding="utf-8")))
for f in sorted((MODEL_DIR / "metrics").glob("*.yml"))]
# Metric libraries may own new fact topics without changing legacy topic definitions.
for name, doc in metric_docs:
for topic in doc.get('topics') or ():
if not isinstance(topic, dict) or not topic.get('key'):
raise ModelError(f"metrics file {name}: a topic lacks a key")
if topic['key'] in topics:
raise ModelError(f"duplicate topic key {topic['key']!r}")
if topic.get('domain_builder'):
raise ModelError('Inline fact topics use metric live domains')
topics[topic['key']] = topic
metric_docs += [(f"topic {topic['key']}", {"topic": topic['key'],
"metrics": topic['metrics']})
for topic in topics.values() if topic.get('metrics')]
for name, d in metric_docs:
topic = d.get("topic")
if topic not in topics:
raise ModelError(f"metrics file {name}: unknown topic {topic!r}")
for m in d.get("metrics", []):
if "key" not in m:
raise ModelError(f"metrics file {name}: a metric lacks a 'key'")
if m["key"] in metrics:
raise ModelError(f"duplicate metric key {m['key']!r}")
m.setdefault("topic", topic) # a metric may name its own topic (rare)
if m["topic"] not in topics:
raise ModelError(f"metric {m['key']!r}: unknown topic {m['topic']!r}")
metrics[m["key"]] = m
for row in list(metrics.values()):
for alias in row.get('aliases') or ():
prior = metrics.get(alias)
if prior and any(prior.get(k) != row.get(k) for k in ('topic', 'agg', 'field')):
raise ModelError(f"metric alias {alias!r} changes an existing binding")
metrics[alias] = {**copy.deepcopy(row), 'key': alias, 'alias_of': row['key']}
if _STANDARD_METRICS - metrics.keys():
raise ModelError('The standard metric catalog is incomplete')
for topic in topics.values():
_validate_topic_semantic(topic, metrics)
return {"topics": topics, "metrics": metrics}
def reload_model():
_model.cache_clear()
# β The entity-measure offer is derived from the same files and would otherwise survive a
# reload β a stale offer is a column list that disagrees with the model it claims to come from.
_ENTITY_OFFER_CACHE.clear()
return _model()
def topics():
result = copy.deepcopy(_model()['topics'])
for topic in result.values():
if 'store' in topic:
topic['store'] = _store_binding(topic)
return result
def metrics():
return dict(_model()["metrics"])
def _validate_topic_semantic(topic, metric_rows):
"""Validate a topic dict without re-entering the model loader."""
key = topic["key"]
raw = topic.get("semantic")
def fail(message):
raise ModelError(f"topic {key!r}: {message}")
if not isinstance(raw, dict):
fail("semantic must be a curated block")
for metric_key in _STANDARD_METRICS:
row = metric_rows.get(metric_key) or {}
if row.get('topic') != key:
continue
for name in ('states', 'channel_excludes'):
values = row.get(name)
if not isinstance(values, list) or not values or any(
not isinstance(value, str) or not value.strip() for value in values):
fail(f"standard metric {metric_key!r} needs non-empty {name}")
for name in ('date_basis', 'cost_basis', 'store_filter_sql'):
if not isinstance(row.get(name), str) or not row[name].strip():
fail(f"standard metric {metric_key!r} needs non-empty {name}")
if not row.get('live_domain') or not row.get('required_sources'):
fail(f"standard metric {metric_key!r} needs live_domain and required_sources")
if metric_key == 'shipped' and (not row.get('value_sql') or
not {'sale_order_line', 'stock_move'}.issubset(row.get('required_backfills') or ())):
fail('standard shipped sales needs its monetary formula and linked field backfills')
if row.get('empty') not in {'zero', 'blank'}:
fail(f"standard metric {metric_key!r} needs an empty-window policy")
if (row.get('validate') or {}).get('method') not in _VALIDATORS:
fail(f"standard metric {metric_key!r} needs an independent validator")
if ("measure" in raw) == ("measure_not_yet" in raw):
fail("declare exactly one of measure or measure_not_yet")
block = {"approved": [], "sensitive": [], "purposes": {}, "synonyms": {},
"choices": {}, "date_field": "", "date_col_note": "", "scope": "",
"measure": None, "measure_not_yet": []}
block.update(copy.deepcopy(raw))
fields = {field["key"]: field for field in topic.get("fields") or ()}
for name in ("approved", "sensitive", "purposes", "synonyms", "choices"):
expected = list if name in ("approved", "sensitive") else dict
if not isinstance(block[name], expected):
fail(f"semantic.{name} must be a {expected.__name__}")
for field in block[name]:
if not isinstance(field, str) or field not in fields:
fail(f"semantic.{name} names unknown field {field!r}")
for field, purpose in block["purposes"].items():
if not isinstance(purpose, str) or not purpose.strip():
fail(f"semantic.purposes field {field!r} needs a purpose")
for name in ("synonyms", "choices"):
for field, values in block[name].items():
if not isinstance(values, list) or any(not isinstance(v, str) for v in values):
fail(f"semantic.{name} field {field!r} must contain strings")
for name in ("date_field", "date_col_note", "scope"):
if not isinstance(block[name], str):
fail(f"semantic.{name} must be a string")
date_field = block["date_field"]
if date_field and (date_field not in fields or
fields[date_field].get("type") not in ("date", "datetime")):
fail(f"semantic.date_field {date_field!r} must name a date or datetime field")
if not topic.get("grid") and not fields:
if raw.get("approved") != [] or raw.get("date_field") != "":
fail("a fact topic must declare approved: [] and date_field: an empty string")
if block["date_col_note"] != (topic.get("store") or {}).get("date_col"):
fail("semantic.date_col_note must document store.date_col")
if not block["scope"].strip():
fail("semantic.scope must explain the topic scope")
# Every core topic has to say WHICH column a dated question lands on. A registry such as
# odoo_customers or odoo_products has no business date of its own, so it documents the date
# column of the fact topic its measure is bound to instead of inventing a field it does not
# have; `verify_w45_semantic_apps.py` is what holds that note to the real source column.
if not (date_field or block["date_col_note"].strip()):
fail("semantic needs a date_field or a date_col_note naming the dated column")
if "measure" in raw:
measure = block["measure"]
if not isinstance(measure, dict):
fail("semantic.measure must name a validated metric")
measure.setdefault("source", key)
for name in ("key", "source", "validate"):
if not isinstance(measure.get(name), str) or not measure[name].strip():
fail(f"semantic.measure needs {name}")
metric = metric_rows.get(measure["key"]) or {}
if metric.get("topic") != measure["source"]:
fail(f"semantic.measure {measure['key']!r} does not belong to {measure['source']!r}")
if (measure["validate"] not in _VALIDATORS or
(metric.get("validate") or {}).get("method") != measure["validate"]):
fail(f"semantic.measure {measure['key']!r} needs a matching independent validator")
if measure["source"] != key:
bindings = topic.get("measures") or []
bindings = [bindings] if isinstance(bindings, dict) else bindings
if not any(binding.get("source") == measure["source"] and
measure["key"] in (binding.get("keys") or ()) for binding in bindings):
fail(f"semantic.measure {measure['key']!r} has no source binding")
else:
pending = block["measure_not_yet"]
if not isinstance(pending, list) or not pending:
fail("semantic.measure_not_yet must report the missing measure")
for row in pending:
if not isinstance(row, dict) or any(
not isinstance(row.get(name), str) or not row[name].strip()
for name in ("key", "cause", "fix")):
fail("semantic.measure_not_yet needs non-empty key, cause and fix strings")
return block
def topic_semantic(topic_key):
"""The validated platform curation for one topic, with all optional keys defaulted."""
loaded = _model()
topic = loaded["topics"].get(str(topic_key))
if not topic:
raise ModelError(f"unknown topic {topic_key!r}")
return _validate_topic_semantic(topic, loaded["metrics"])
def curated_topic_report():
"""Report declared reconciliation coverage without reading any Odoo records."""
metric_rows = metrics()
out = []
for key, topic in sorted(topics().items()):
block = topic_semantic(key)
measure = block["measure"]
validated = bool(measure and measure["validate"] in _VALIDATORS and
(metric_rows[measure["key"]].get("validate") or {}).get("method")
== measure["validate"])
out.append({"topic": key, "grid": topic.get("grid") or "",
"approved": len(block["approved"]), "date_field": block["date_field"],
"scope": block["scope"], "measure": measure,
"measure_not_yet": block["measure_not_yet"], "validated": validated})
return out
def semantic_fields(topic_key):
"""Structured platform baseline, before tenant governance overrides."""
topic = _model()["topics"].get(str(topic_key))
if not topic:
raise ModelError(f"unknown topic {topic_key!r}")
curated = topic_semantic(topic_key)
out = {}
for field in topic.get("fields") or ():
if not isinstance(field, dict) or not field.get("key"):
continue
key = field["key"]
defaults = {"state": "approved" if key in curated["approved"] else "draft",
"sensitivity": "sensitive" if key in curated["sensitive"] else "internal",
"purpose": curated["purposes"].get(key, field.get("means") or ""),
"synonyms": curated["synonyms"].get(key, [])}
field_meta = field.get("semantic") if isinstance(field.get("semantic"), dict) else {}
meta = {**defaults, **field_meta} # Per-field curation wins.
state = str(meta.get("state") or "draft").lower()
sensitivity = str(meta.get("sensitivity") or "internal").lower()
if state not in ("draft", "approved", "disabled"):
raise ModelError(f"topic {topic_key!r} field {field['key']!r}: bad semantic state")
if sensitivity not in ("public", "internal", "sensitive", "restricted"):
raise ModelError(f"topic {topic_key!r} field {field['key']!r}: bad sensitivity")
out[field["key"]] = {
"field": field["key"], "label": field.get("label") or field["key"],
"purpose": meta.get("purpose") or "",
"dataType": field.get("type") or "text",
"role": meta.get("role") or "attribute",
"synonyms": list(meta.get("synonyms") or ()),
"sensitivity": sensitivity, "state": state,
"personal": field.get('personal') is True,
}
return out
def metric_catalog(topic_key=None):
"""Existing validated Metrics only; no customer measure is inferred from a numeric field."""
return {key: dict(row) for key, row in _model()["metrics"].items()
if topic_key is None or row.get("topic") == topic_key}
def describe(key):
"""Full metadata for a metric (the dictionary page / AI describe_topic feed)."""
m = _model()["metrics"].get(key)
if not m:
raise ModelError(f"unknown metric {key!r}")
return {**m, "topic_def": _model()["topics"][m["topic"]]}
# ---------------------------------------------------------------- resolving
def _domain(topic_def, date_from, date_to, team_id):
b = topic_def.get("domain_builder")
if not b:
raise ModelError(f"topic {topic_def['key']!r} is store-only: use store_query(). "
f"No live-path domain builder is bound yet.")
return _BUILDERS[b](date_from, date_to, team_id=team_id)
_OPS = {ast.Add: operator.add, ast.Sub: operator.sub,
ast.Mult: operator.mul, ast.Div: operator.truediv}
def _eval_expr(expr, resolve):
"""Safely evaluate a derived-metric expression: metric keys, numbers, + - * / and parens."""
def ev(node):
if isinstance(node, ast.Expression):
return ev(node.body)
if isinstance(node, ast.BinOp) and type(node.op) in _OPS:
return _OPS[type(node.op)](ev(node.left), ev(node.right))
if isinstance(node, ast.UnaryOp) and isinstance(node.op, ast.USub):
return -ev(node.operand)
if isinstance(node, ast.Num): # py<3.8 compat name; Constant below
return node.n
if isinstance(node, ast.Constant) and isinstance(node.value, (int, float)):
return node.value
if isinstance(node, ast.Name):
return resolve(node.id)
raise ModelError(f"disallowed token in derived expr: {ast.dump(node)}")
try:
return ev(ast.parse(expr, mode="eval"))
except SyntaxError as e:
raise ModelError(f"unparseable derived expr {expr!r}: {e}")
@_runtime_bound
def metric(key, date_from=None, date_to=None, team_id=None, _seen=None, *, runtime=None, user=None,
customer_id=None):
"""Resolve a registered metric to a number for a window (+optional BU). The only public
compute path β every surface that shows this metric goes through here."""
_seen = _seen or set()
if key in _seen:
raise ModelError(f"circular metric reference at {key!r}")
_seen = _seen | {key}
m = _model()["metrics"].get(key)
if not m:
raise ModelError(f"unknown metric {key!r}")
uses_standard = key in _STANDARD_METRICS or (m.get('agg') in {'ratio', 'derived'} and
any(base in _STANDARD_METRICS for base in _expand_measures([key])[0]))
if uses_standard:
date_from, date_to = _metric_window(date_from, date_to)
filters = _metric_customer_filter(m, customer_id)
if customer_id is not None and user is not None:
table = _model()['topics'][m['topic']]['store']['table']
database = {'sale_order_line': 'ut_odoo_order_lines', 'stock_move': 'ut_odoo_stock_moves',
'account_move': 'ut_odoo_invoices', 'account_payment': 'ut_odoo_payments'}.get(table)
if not database or runtime is None:
raise ModelError('This customer metric has no admitted runtime scope.')
_metric_read_access(runtime, user, (database,))
if runtime is not None:
runtime.assert_datastore_matches()
result = store_query(m['topic'], [key], date_from=date_from, date_to=date_to,
team_id=team_id, runtime=runtime, user=user, filters=filters)
return _scalar_observation(result, key)
if key == 'amazon_order_count':
return store_query('amazon_orders', [key], date_from=date_from, date_to=date_to,
team_id=team_id, runtime=runtime, user=user)['rows'][0][key]
agg = m.get("agg")
if agg == "ratio":
num = metric(m["numerator"], date_from, date_to, team_id, _seen)
den = metric(m["denominator"], date_from, date_to, team_id, _seen)
return (num / den) if den else 0.0
if agg == "derived":
return _eval_expr(m["expr"], lambda k: metric(k, date_from, date_to, team_id, _seen))
t = _model()["topics"][m["topic"]]
dom = _domain(t, date_from, date_to, team_id)
# Wave 21 R2 β the LIVE twin of `store_filter_sql`. A filtered metric must filter BOTH
# paths, or store_parity would compare a narrowed store number against an unfiltered live
# one and the "parity failure" would read as a data problem. YAML shape:
# live_domain: [[field, op, value], ...] β appended verbatim to the topic domain.
dom += _metric_live_domain(m)
if agg == "sum":
# β `negate` applies HERE too, not only in `_measure_sql`. It existed for GL income on a
# STORE-ONLY topic, so the live path never met it β the first metric that is both negated
# and parity-checked (`returns`) would otherwise compare a negative live number against a
# positive store one, and the "parity failure" would look like a data problem.
got = O.sum_field(t["entity"], dom, m["field"])
return -got if m.get("negate") else got
if agg == "count_distinct":
return O.distinct_count(t["entity"], dom, m["field"])
if agg == "count":
return O.get_odoo().search_count(t["entity"], dom)
if agg in {"min", "max"}:
client = O.get_odoo()
dom += [(m['field'], '!=', False)]
if not client.search_count(t['entity'], dom):
return None
rows = client.read_group(t['entity'], dom, [f"{m['field']}:{agg}"], [], lazy=False)
value = rows[0].get(m['field']) if rows else None
if m.get('date_age'):
return (dt.date.today() - dt.date.fromisoformat(str(value)[:10])).days if value else None
return str(value)[:10] if value and m.get('format') == 'date' else value
raise ModelError(f"metric {key!r}: unknown agg {agg!r}")
# ---------------------------------------------------------------- validate contracts
def _metric_window(date_from, date_to):
"""Accept an explicit pair or a two-date window on the scalar metric door."""
if date_to is None and isinstance(date_from, (list, tuple)) and len(date_from) == 2:
return date_from[0], date_from[1]
if date_to is None and isinstance(date_from, dict):
return (date_from.get('date_from') or date_from.get('from'),
date_from.get('date_to') or date_from.get('to'))
return date_from, date_to
def _metric_customer_filter(row, customer_id):
"""Bind a positive customer identity to the metric's own dimension."""
if customer_id is None:
return None
if type(customer_id) is not int or customer_id <= 0:
raise ModelError('Use a positive customer id without expanding its scope.')
dims = _model()['topics'][row['topic']]['store'].get('dims') or {}
key = 'order_partner' if 'order_partner' in dims else 'partner'
if key not in dims:
raise ModelError('This metric has no proven customer attribution.')
return {key: [customer_id]}
def _metric_read_access(runtime, user, databases):
"""Refuse permission walls that the independent metric oracle cannot reproduce."""
from core import perm_scope
for database in databases:
access = perm_scope.min_access([user], database, st=runtime)
if (not access['readable'] or access['hidden'] or not perm_scope.may_metrics(user, database)
or perm_scope.wall_declared(user, database, st=runtime)
or (perm_scope.assistant_entry(user, database) or {}).get('filter')
or any(value is not None for value in perm_scope.derive_pool_scope(user, database))):
raise ModelError('This metric scope cannot be independently reconciled within your access.')
@_runtime_bound
def sales_warnings(key, date_from, date_to, *, runtime, user, customer_id=None, team_id=None):
"""Reconcile warnings within the same admitted customer and order-date scope."""
from harness import datastore
from modules import sales_warnings as warnings
if key not in {'booked', 'margin'}:
raise ModelError('Warnings require verified costs and fulfilment links on this metric basis. '
'Invoice lines have no observed historical purchase_price or margin fields.')
runtime.assert_datastore_matches()
if user is None or runtime.odoo_source() is None:
raise ModelError('Sales warnings require an admitted user and tenant source.')
_metric_read_access(runtime, user, ('ut_odoo_order_lines', 'ut_odoo_pickings', 'ut_odoo_purchase_lines'))
row = _model()['metrics'][key]
_metric_customer_filter(row, customer_id)
date_from, date_to = _metric_window(date_from, date_to)
try:
start, end = dt.date.fromisoformat(date_from), dt.date.fromisoformat(date_to)
if start > end:
raise ValueError
except (TypeError, ValueError):
raise ModelError('Sales warnings require an explicit valid date window.') from None
sources = {'sale_order_line', 'sale_order', 'res_partner', 'stock_picking',
'purchase_order_line', 'purchase_order'}
for source in sources:
if _source_ready(source) is not True:
raise ModelError('A required sales warning mirror source is unavailable or incomplete.')
spec = datastore.ENTITIES['purchase_order_line']
if ('sale_line_id' not in spec['fields']
or 'sale_line_id' not in (spec.get('backfill_fields') or ())):
raise ModelError('Purchase warnings require an observed and backfilled sale_line_id relation.')
store = _model()['topics'][row['topic']]['store']
predicate = render_scope(store['scope_sql']) + ' AND ' + render_scope(row['store_filter_sql'])
from core import periods
lo, hi = periods.business_day_bounds(date_from, date_to)
predicate += ' AND CAST(o.date_order AS TIMESTAMP) >= ? AND CAST(o.date_order AS TIMESTAMP) < ?'
parameters = [lo.strftime('%Y-%m-%d %H:%M:%S'), hi.strftime('%Y-%m-%d %H:%M:%S')]
domain = _metric_live_domain(row) + [
('order_id.date_order', '>=', parameters[0]),
('order_id.date_order', '<', parameters[1])]
if customer_id is not None:
predicate += ' AND l.order_partner_id = ?'
parameters.append(customer_id)
domain.append(('order_partner_id', '=', customer_id))
if team_id is not None:
teams = (_tenant().get('params') or {}).get('wholesale_team_ids') or []
if type(team_id) is not int or team_id not in teams:
raise ModelError('The requested business unit is outside the configured scope.')
predicate += ' AND o.team_id = ?'
parameters.append(team_id)
domain.append(('order_id.team_id', '=', team_id))
scope = {'sql': predicate, 'parameters': parameters, 'domain': domain, 'customer_id': customer_id}
with runtime.odoo_source()._bound():
con = _store_con()
try:
con.execute('BEGIN TRANSACTION')
for source in sources:
spec = datastore.ENTITIES[source]
if spec.get('backfill_fields') and not datastore._columns_backfilled(con, source, spec):
raise ModelError('Sales warning fields need their declared source backfill.')
return warnings.validate(con, O.get_odoo(), scope)
except ModelError:
raise
except ValueError as exc:
raise ModelError(str(exc)) from None
except Exception:
raise ModelError('Required sales warning tables or fields have no complete observation.') from None
finally:
con.close()
@_runtime_bound
def customer_invoice_periods(customer, previous_window, window, *, runtime, user, customer_id=None):
"""Read complete invoice documents and lines with one immutable customer pin."""
from core import perm_scope
from harness import datastore
if (not isinstance(customer, str) or not customer.strip() or user is None
or (customer_id is not None and (type(customer_id) is not int or customer_id <= 0))):
raise ModelError('Use an exact customer name and an optional positive customer id.')
customer = customer.strip()
runtime.assert_datastore_matches()
if runtime.odoo_source() is None:
raise ModelError('The customer invoice source is unavailable.')
for database in ('ut_odoo_invoices', 'ut_odoo_gl_lines'):
access = perm_scope.min_access([user], database, st=runtime)
if (not access['readable'] or access['hidden'] or not perm_scope.may_metrics(user, database)
or perm_scope.wall_declared(user, database, st=runtime)
or (perm_scope.assistant_entry(user, database) or {}).get('filter')
or any(value is not None for value in perm_scope.derive_pool_scope(user, database))):
raise ModelError('This customer invoice scope cannot be independently reconciled.')
for source in ('account_move', 'account_move_line'):
if _source_ready(source) is not True:
raise ModelError('The customer invoice mirror is unavailable or incomplete.')
windows = []
for period in (previous_window, window):
if not isinstance(period, dict):
raise ModelError('Use two explicit invoice date windows.')
try:
start, end = (dt.date.fromisoformat(period[key]) for key in ('date_from', 'date_to'))
if start > end:
raise ValueError
except (KeyError, TypeError, ValueError):
raise ModelError('Use two valid invoice date windows.') from None
windows.append(copy.deepcopy(period))
if windows[0]['date_to'] >= windows[1]['date_from']:
raise ModelError('The previous invoice window must end before the current window starts.')
con = _store_con()
try:
con.execute('BEGIN TRANSACTION')
for source in ('account_move', 'account_move_line'):
if not datastore._columns_backfilled(con, source, datastore.ENTITIES[source]):
raise ModelError('The customer invoice mirror needs its declared field backfill.')
identity = 'm.partner_name = ? AND m.partner_id IS NOT NULL'
parameters = [customer]
if customer_id is not None:
identity += ' AND m.partner_id = ?'
parameters.append(customer_id)
base = "m.state = 'posted' AND m.move_type = 'out_invoice' AND " + identity
history_ids = [row[0] for row in con.execute(
'SELECT DISTINCT m.partner_id FROM account_move m WHERE ' + base, parameters).fetchall()]
if len(history_ids) > 1:
raise ModelError('This customer name is ambiguous. Supply its permitted customer id.')
pin_id = history_ids[0] if history_ids else customer_id
if pin_id is not None and customer_id is None:
identity += ' AND m.partner_id = ?'
parameters.append(pin_id)
base = "m.state = 'posted' AND m.move_type = 'out_invoice' AND " + identity
periods = []
for period in windows:
predicate = base + ' AND CAST(m.invoice_date AS DATE) BETWEEN ? AND ?'
args = parameters + [period['date_from'], period['date_to']]
def rows(sql, values):
cursor = con.execute(sql, values)
names = [item[0] for item in cursor.description]
return [dict(zip(names, row)) for row in cursor.fetchall()]
documents = rows('SELECT m.id, m.amount_untaxed, m.amount_untaxed_signed '
'FROM account_move m WHERE ' + predicate + ' ORDER BY m.id', args)
line_pin = ' AND l.partner_id = ?' if pin_id is not None else ' AND l.partner_id = m.partner_id'
lines = rows('SELECT l.id, l.move_id, l.product_id, l.product_uom_id, l.display_type, '
'l.price_subtotal, l.quantity FROM account_move_line l '
'JOIN account_move m ON m.id = l.move_id WHERE ' + predicate + line_pin +
' ORDER BY l.id', args + ([pin_id] if pin_id is not None else []))
periods.append({'window': period, 'documents': documents, 'lines': lines})
except ModelError:
raise
except Exception:
raise ModelError('The customer invoice mirror has no complete observation.') from None
finally:
con.close()
return {'pin': {'id': pin_id, 'name': customer}, 'history_ids': history_ids, 'periods': periods}
def explain_customer(customer, previous_window, window, *, runtime, user, customer_id=None):
"""Resolve one validated customer explanation on the invoiced basis."""
from modules import customer_decline
return customer_decline.explain_customer(customer, previous_window, window, runtime=runtime,
user=user, customer_id=customer_id)
def _metric_live_domain(row):
"""Resolve typed tenant parameters in a metric's live domain."""
params = _tenant().get('params') or {}
def value(item):
if isinstance(item, str) and re.fullmatch(r'\{[a-z_]+\}', item):
name = item[1:-1]
if name not in params:
raise ModelError(f'Unknown metric tenant parameter {name!r}')
return copy.deepcopy(params[name])
return item
return [term if isinstance(term, str) else
(term[0], term[1], value(term[2])) for term in row.get('live_domain') or ()]
def _scalar_observation(result, key):
"""Refuse incomplete serving results before interpreting an empty aggregate."""
if result.get('refusals') or result.get('truncated') or result.get('state') == 'unproven':
raise ModelError('The observation read refused this metric')
rows = result.get('rows') or []
if len(rows) != 1 or key not in rows[0]:
raise ModelError('The observation read did not return a complete scalar aggregate')
return rows[0][key] if rows[0][key] is not None else 0.0
def _relation_id(value):
return value[0] if isinstance(value, (list, tuple)) else value
def _shipped_oracle(client, domain):
"""Value independently aggregated move quantities using source sale-line prices and unit factors."""
groups = client.read_group('stock.move', domain,
['quantity_done:sum', 'sale_line_id', 'product_uom'],
['sale_line_id', 'product_uom'], lazy=False)
line_ids = sorted({_relation_id(row.get('sale_line_id')) for row in groups
if row.get('sale_line_id')})
lines = {row['id']: row for row in client.search_read('sale.order.line',
[('id', 'in', line_ids)], ['price_subtotal', 'product_uom_qty', 'product_uom'])} if line_ids else {}
unit_ids = {_relation_id(row.get('product_uom')) for row in groups}
unit_ids.update(_relation_id(row.get('product_uom')) for row in lines.values())
unit_ids.discard(False)
unit_ids.discard(None)
units = {row['id']: row for row in client.search_read('uom.uom',
[('id', 'in', sorted(unit_ids)), ('active', 'in', [True, False])],
['factor', 'category_id'])} if unit_ids else {}
total = 0.0
for row in groups:
line = lines.get(_relation_id(row.get('sale_line_id'))) or {}
move_unit = units.get(_relation_id(row.get('product_uom'))) or {}
line_unit = units.get(_relation_id(line.get('product_uom'))) or {}
quantity, subtotal = row.get('quantity_done'), line.get('price_subtotal')
ordered = line.get('product_uom_qty')
if (quantity is None or subtotal is None or ordered is None or not line
or not move_unit.get('factor') or move_unit['factor'] <= 0
or not line_unit.get('factor') or line_unit['factor'] <= 0
or not move_unit.get('category_id') or not line_unit.get('category_id')
or _relation_id(move_unit['category_id']) != _relation_id(line_unit['category_id'])
or (ordered == 0 and quantity != 0)):
raise ModelError('Shipped sales require complete prices and compatible units')
if quantity:
total += quantity / move_unit['factor'] * line_unit['factor'] * subtotal / ordered
return total
@_live_oracle_bound
@_runtime_bound
def standard_metric_oracle(key, date_from=None, date_to=None, team_id=None, *, client=None,
runtime=None, user=None, customer_id=None):
"""Read a standard metric directly from Odoo without the serving resolver."""
key = 'invoiced_sales' if key in {'invoiced', 'sales'} else key
if key not in _STANDARD_METRICS:
raise ModelError(f'Unknown standard metric {key!r}')
row = _model()['metrics'][key]
_metric_customer_filter(row, customer_id)
params = _tenant().get('params') or {}
date_from, date_to = _metric_window(date_from, date_to)
teams = params.get('wholesale_team_ids') or []
excluded = params.get('excluded_partner_name_like')
if key in {'booked', 'margin', 'shipped'}:
if not teams or not excluded:
raise ModelError('Standard wholesale metrics need configured teams and a channel exclusion')
if team_id is not None and int(team_id) not in teams:
raise ModelError('The requested business unit is outside the configured wholesale scope')
prefix = 'sale_line_id.order_id.' if key == 'shipped' else 'order_id.'
domain = _metric_live_domain(row)
if team_id is not None:
domain.append((prefix + 'team_id', '=', int(team_id)))
if key == 'shipped':
entity, field, date_field = 'stock.move', 'quantity_done', 'picking_id.date_done'
else:
entity = 'sale.order.line'
field = 'margin' if key == 'margin' else 'price_subtotal'
date_field = 'order_id.date_order'
else:
if team_id is not None:
raise ModelError('This metric has no proven business-unit attribution')
if key == 'invoiced_sales':
entity, field, date_field = 'account.move', 'amount_untaxed_signed', 'invoice_date'
else:
entity, field, date_field = 'account.payment', 'amount_company_currency_signed', 'date'
domain = _metric_live_domain(row)
if customer_id is not None:
customer_field = ('sale_line_id.order_partner_id' if key == 'shipped' else
'order_partner_id' if key in {'booked', 'margin'} else 'partner_id')
domain.append((customer_field, '=', customer_id))
datetime_basis = date_field in {'order_id.date_order', 'picking_id.date_done'}
if datetime_basis:
# Odoo stores datetimes in UTC, so the tenant business day is cut in UTC.
from core import periods
lo, hi = periods.business_day_bounds(date_from, date_to)
if lo:
domain.append((date_field, '>=', lo.strftime('%Y-%m-%d %H:%M:%S')))
if hi:
domain.append((date_field, '<', hi.strftime('%Y-%m-%d %H:%M:%S')))
else:
if date_from:
domain.append((date_field, '>=', str(date_from)))
if date_to:
domain.append((date_field, '<=', str(date_to)))
if runtime is not None:
if client is not None:
raise ModelError('An explicit client cannot override the tenant source binding')
source = runtime.odoo_source()
if source is None:
raise ModelError('The tenant has no available Odoo source')
binding = source._bound()
else:
binding = nullcontext()
with binding:
reader = client if client is not None else O.get_odoo()
if key == 'shipped':
return _shipped_oracle(reader, domain)
rows = reader.read_group(entity, domain, [field + ':sum'], [], lazy=False)
if rows and field not in rows[0]:
raise ModelError('The source did not return the requested aggregate')
return float((rows[0].get(field) if rows else 0) or 0)
def _v_standard_metric(key):
def reconcile(date_from, date_to, team_id, *, runtime=None, user=None, customer_id=None):
ours = metric(key, date_from, date_to, team_id, runtime=runtime, user=user, customer_id=customer_id)
return ours, standard_metric_oracle(key, date_from, date_to, team_id,
runtime=runtime, user=user, customer_id=customer_id)
return reconcile
def _v_order_level_revenue(date_from, date_to, team_id):
"""Independent check: line-level Ξ£ price_subtotal == order-level Ξ£ amount_untaxed."""
import modules.sales as S
line = metric("revenue", date_from, date_to, team_id)
order = O.sum_field("sale.order", S.order_domain(date_from, date_to, team_id), "amount_untaxed")
return line, order
def _v_order_count(date_from, date_to, team_id):
"""Exact reconciliation: order-level count == distinct line-parent orders + line-LESS orders.
(Confirmed orders with zero lines exist in the data β 2 found at first proof, 2026-07-11;
counting them explicitly makes the identity exact instead of hiding them in a tolerance,
and surfaces them as a data-health signal.)"""
import modules.sales as S
order_level = metric("orders", date_from, date_to, team_id)
dom = S.order_domain(date_from, date_to, team_id)
from_lines = O.distinct_count("sale.order.line",
O.sale_line_domain(date_from, date_to, team_id), "order_id")
empty = O.get_odoo().search_count("sale.order", dom + [("order_line", "=", False)])
return order_level, from_lines + empty
def _v_gl_opex(date_from, date_to, team_id):
"""Store-path opex == the live Odoo aggregate with the Expenses-module domain (posted,
expense-type accounts), to the cent. team_id N/A (company-level)."""
res = store_query("gl_lines", ["opex"], date_from=date_from, date_to=date_to)
ours = res["rows"][0]["opex"] if res["rows"] else 0
dom = [("move_id.state", "=", "posted"),
("account_id.account_type", "in", ["expense", "expense_depreciation"])]
if date_from:
dom.append(("date", ">=", date_from))
if date_to:
dom.append(("date", "<=", date_to))
live = O.sum_field("account.move.line", dom, "balance")
return ours, live
def _v_ar_outstanding(date_from, date_to, team_id):
"""Store-path AR outstanding == live Odoo aggregate (posted out_invoice/out_refund residuals).
No date window β outstanding is an as-of-now balance."""
# Invoice grain on purpose: the receivables topic is one row per document and the invoice
# grid drills by `move`. It leaves out unapplied payments, so it is not Overdue and not the
# customer field `ar_outstanding` (line grain). Overdue is `_v_ar_overdue`.
res = store_query("receivables", ["ar_outstanding"])
ours = res["rows"][0]["ar_outstanding"] if res["rows"] else 0
live = O.sum_field("account.move",
[("state", "=", "posted"), ("move_type", "in", ["out_invoice", "out_refund"])],
"amount_residual_signed")
return ours, live
def _v_ar_overdue(date_from, date_to, team_id):
"""Store-path Overdue == Odoo receivable lines past due (`ar.overdue_tie`), on one date.
No date window and no team: Overdue is an as-of-today company balance. The tie runs on the
store's own CURRENT_DATE, the date the metric's filter reads. The customer field leg is
`ar.overdue_checks`, which `ar.validate()` records.
"""
from modules import ar
con = _store_con()
try:
as_of = con.execute("SELECT CURRENT_DATE").fetchone()[0]
finally:
con.close()
# The store side first: a mirror without the line residual refuses before any Odoo read.
res = store_query(_model()["metrics"]["ar_overdue"]["topic"], ["ar_overdue"])
ours = res["rows"][0]["ar_overdue"] if res["rows"] else 0
tie = ar.overdue_tie(ar.aged_receivable(as_of))
return ours, tie["b"]
def customer_rollup_oracle(key, client=None, *, partner_ids=None, grouped=False, today=None):
"""Read each customer rollup independently from Odoo."""
client = client or O.get_odoo()
orders = key in {'customer_order_count', 'customer_last_order'}
model = 'sale.order' if orders else 'account.move'
domain = ([('state', 'in', ['sale', 'done']), ('partner_id', '!=', False)] if orders else
[('state', '=', 'posted'), ('move_type', 'in', ['out_invoice', 'out_refund'])])
if key == 'customer_days_since_invoice':
domain = [('state', '=', 'posted'), ('move_type', '=', 'out_invoice'),
('partner_id', '!=', False), ('invoice_date', '!=', False)]
if partner_ids is not None:
domain += [('partner_id', 'in', list(partner_ids))]
if not partner_ids:
return {} if grouped else None
if key in {'customer_open_documents', 'customer_oldest_due'}:
domain += [('payment_state', 'in', ['not_paid', 'partial'])]
if key == 'customer_open_documents':
domain += [('partner_id', '!=', False)]
if key in {'customer_order_count', 'customer_open_documents'}:
return client.search_count(model, domain)
field, agg = {
'ar_outstanding': ('amount_residual_signed', 'sum'),
'customer_last_order': ('date_order', 'max'),
'customer_days_since_invoice': ('invoice_date', 'max'),
'customer_oldest_due': ('invoice_date_due', 'min'),
'customer_invoiced_all_time': ('amount_untaxed_signed', 'sum'),
}[key]
if key == 'customer_days_since_invoice':
as_of = dt.date.fromisoformat(str(today)[:10]) if today is not None else dt.date.today()
if grouped:
rows = client.read_group(model, domain, ['partner_id', f'{field}:{agg}'],
['partner_id'], lazy=False)
return {O.m2o_id(row['partner_id']):
(as_of - dt.date.fromisoformat(str(row[field])[:10])).days
for row in rows if row.get('partner_id') and row.get(field)}
if not client.search_count(model, domain):
return None
rows = client.read_group(model, domain, [f'{field}:{agg}'], [], lazy=False)
value = rows[0].get(field) if rows else None
return (as_of - dt.date.fromisoformat(str(value)[:10])).days if value else None
rows = client.read_group(model, domain, [f'{field}:{agg}'], [], lazy=False)
return (rows[0].get(field) if rows else None) or (0 if agg == 'sum' else '')
def _v_customer_rollup(key):
def reconcile(date_from, date_to, team_id):
topic = _model()['metrics'][key]['topic']
result = store_query(topic, [key])
ours = result['rows'][0].get(key) if result['rows'] else None
return ours, customer_rollup_oracle(key)
return reconcile
def _v_returns_sign(date_from, date_to, team_id):
"""Independent check on the NEGATION: `returns` must equal the ABSOLUTE untaxed total of the
same posted credit notes, read straight from Odoo without going through the metric.
A lost negation makes returns negative; a doubled one makes it negative again. Both read as
"no returns" rather than as an error, and neither is visible to any self-consistency check β
the store and the live path would agree with each other perfectly while both being wrong.
"""
ours = metric("returns", date_from, date_to, team_id)
raw = O.sum_field("account.move",
_BUILDERS["credit_note_domain"](date_from, date_to, team_id),
"amount_untaxed_signed")
return ours, abs(raw)
def _v_line_vs_move_grain(date_from, date_to, team_id):
"""Independent check on the invoice-LINE topic: the store's netted product-line total must
equal the same figure read straight from LIVE Odoo.
Two things this catches that self-consistency cannot. (1) A LOST CREDIT-NOTE NEGATION β
`price_subtotal` is stored POSITIVE on a refund line, so a dropped subtraction inflates
revenue while every internal check still agrees. (2) A WRONG `display_type` FILTER β the
invoice tables carry 'cogs' and 'payment_term' rows alongside 'product' ones, and including
them changes the total silently rather than erroring.
Company-level (team_id None) uses the strongest possible reference: the MOVE-grain
`amount_untaxed_signed`, a different table entirely. Per-BU there is no move-grain analogue
(account.move carries no business unit β every invoice sits on team 1), so the reference is
the live line-level aggregate reached through the sale-order dot path: still an independent
engine and query path from the DuckDB store, which is what the contract requires.
"""
ours = (store_query("invoice_lines", ["invoiced_line_sales"],
date_from=date_from, date_to=date_to, team_id=team_id)
.get("rows") or [{}])[0].get("invoiced_line_sales") or 0.0
if team_id is None:
raw = O.sum_field("account.move",
[("state", "=", "posted"),
("move_type", "in", ["out_invoice", "out_refund"]),
("invoice_date", ">=", date_from), ("invoice_date", "<=", date_to)],
"amount_untaxed_signed")
return ours, raw
dom = [("parent_state", "=", "posted"), ("display_type", "=", "product"),
("date", ">=", date_from), ("date", "<=", date_to),
("sale_line_ids.order_id.team_id", "=", team_id)]
gross = O.sum_field("account.move.line", dom + [("move_type", "=", "out_invoice")],
"price_subtotal")
refunds = O.sum_field("account.move.line", dom + [("move_type", "=", "out_refund")],
"price_subtotal")
return ours, (gross or 0) - (refunds or 0)
def _v_amazon_sales_order_count(date_from, date_to, team_id, *, runtime=None, user=None):
from harness import amazon_semantic
return amazon_semantic.validate(date_from, date_to, team_id, runtime=runtime, user=user)
_VALIDATORS = {
"amazon_sales_order_count": _v_amazon_sales_order_count,
"returns_sign": _v_returns_sign,
"line_vs_move_grain": _v_line_vs_move_grain,
"order_level_revenue": _v_order_level_revenue,
"order_count": _v_order_count,
"gl_opex": _v_gl_opex,
"ar_outstanding": _v_ar_outstanding,
"ar_overdue": _v_ar_overdue,
}
_VALIDATORS.update({key: _v_customer_rollup(key) for key in (
'customer_order_count', 'customer_last_order', 'customer_open_documents',
'customer_oldest_due', 'customer_invoiced_all_time', 'customer_days_since_invoice')})
from modules.finance_spine import FINANCE_VALIDATORS
_VALIDATORS.update(FINANCE_VALIDATORS)
from modules.amazon_profit import PROFIT_VALIDATORS
_VALIDATORS.update(PROFIT_VALIDATORS)
_VALIDATORS.update({'standard_' + key: _v_standard_metric(key)
for key in ('booked', 'shipped', 'collected', 'margin')})
_VALIDATORS['invoiced_sales'] = _v_standard_metric('invoiced_sales')
@_live_oracle_bound
def validate_metric(key, date_from=None, date_to=None, team_id=None, tol=0.01, *, runtime=None, user=None,
customer_id=None):
"""Run a metric's declared independent cross-check. Returns the standard check dict
(the validate.py convention: check/ok/gap)."""
m = _model()["metrics"].get(key)
if not m:
raise ModelError(f"unknown metric {key!r}")
date_from, date_to = _metric_window(date_from, date_to)
contract = m.get("validate")
if not contract:
if key in _STANDARD_METRICS:
return {"check": f"semantic.{key}: no independent contract declared", "ok": False,
"gap": None, "state": "unproven"}
return {"check": f"semantic.{key}: no independent contract declared", "ok": True, "gap": 0.0,
"note": "declare one in model/metrics when an independent aggregate exists"}
fn = _VALIDATORS.get(contract["method"])
if not fn:
raise ModelError(f"metric {key!r}: unknown validate method {contract['method']!r}")
if key == 'amazon_order_count' or key in _STANDARD_METRICS:
try:
kwargs = {'runtime': runtime, 'user': user}
if customer_id is not None:
if key not in _STANDARD_METRICS:
raise ModelError('This metric has no proven customer attribution.')
kwargs['customer_id'] = customer_id
ours, independent = fn(date_from, date_to, team_id, **kwargs)
except live_limiter.LiveLimitRefused:
raise
except Exception as exc:
return {"check": f"semantic.{key}: source proof unavailable", "ok": False,
"gap": None, "state": "unproven",
"reason": str(exc) if isinstance(exc, ModelError) else type(exc).__name__}
if key == 'amazon_order_count':
tol = 0 # Order counts reconcile exactly; a money tolerance cannot forgive missing orders.
else:
try:
ours, independent = fn(date_from, date_to, team_id)
except NotSyncedError as exc:
return {"check": f"semantic.{key}: mirror fields not synced", "ok": False, "gap": None,
"state": exc.code, "reason": str(exc), "sources": exc.sources}
if m.get('date_age'):
gap = 0 if ours == independent else 1
elif m.get('format') == 'date':
gap = 0 if str(ours or '')[:10] == str(independent or '')[:10] else 1
else:
gap = abs((ours or 0) - (independent or 0))
return {"check": f"semantic.{key} == {contract['method']} ({date_from}..{date_to}, team={team_id})",
"ok": gap <= tol, "gap": round(gap, 4), "ours": ours, "independent": independent}
def validate(pre=None):
"""Module-convention validate(): run every declared contract over YTD, all-BU + per-BU."""
import core.periods as P
yf, yt = P.ytd()
out = []
for key, m in _model()["metrics"].items():
if not m.get("validate"):
continue
out.append(validate_metric(key, yf, yt, None))
topic = _model()["topics"][m["topic"]]
if (topic.get("store") or {}).get("team_col"): # company-level topics have no BU runs
for tid in (_tenant().get('params') or {}).get('wholesale_team_ids', []):
out.append(validate_metric(key, yf, yt, tid))
return out
# ---------------------------------------------------------------- store path (OM-2)
# The compiler behind run_semantic_query: registered metrics + whitelisted dims β DuckDB SQL over
# the tenant store (harness/datastore.py). EVERY identifier comes from the versioned model files
# (trusted); every VALUE is parameterized β the small model only ever picks keys, so there is no
# injection surface. Live XML-RPC stays the validate() path (the store never validates itself).
def _store_con(runtime=None):
runtime = runtime if runtime is not None else _TENANT_RUNTIME.get()
if runtime is not None:
from harness.runtime import mirror_cursor
handle = runtime.mirror_handle()
if handle is None:
raise ModelError('The runtime has no tenant-owned mirror handle')
cur = mirror_cursor(runtime)
if getattr(cur, '_mirror_handle', None) is not handle:
cur.close()
raise ModelError('The runtime has no tenant-owned mirror cursor')
return cur
import harness.datastore as DS
if not DS.ready(): # a mid-backfill store returns PARTIAL totals silently
raise ModelError(_store_unready_cause(DS))
return DS.ro_cursor() # cursor on the shared instance β no sync-writer contention
def _pending_backfills(sources, cur=None, runtime=None):
"""The listed mirror entities whose declared column backfill has not finished."""
if not sources:
return []
from harness import datastore
con = cur if cur is not None else (_store_con(runtime) if runtime is not None else _store_con())
try:
# A column ALTERed in after the last pass stays absent or NULL until its marker reads done.
return [s for s in sources
if not datastore._columns_backfilled(con, s, datastore.ENTITIES[s])]
finally:
if cur is None:
con.close()
def _store_unready_cause(DS):
"""D-513: the condition `ready()` actually hit. The old sentence always blamed a warm-up after
a restart, which is rarely the cause (another process holding the file is the usual one) and
stopped three sessions from looking further. Client-facing copy: no em or en dash."""
live = "The dashboards work now from live data"
if not DS.DB_PATH.exists():
return ("the data cache file does not exist on this server yet, so it has never synced. "
f"{live}; ask me again once the first sync has run")
try:
cur = DS.ro_cursor()
try:
phases = dict(cur.execute("SELECT entity, phase FROM _sync_state").fetchall())
finally:
cur.close()
except Exception as exc: # noqa: BLE001 (the TYPE is named, never its message)
if "another process" in str(exc) or "lock" in str(exc).lower():
return ("the data cache is held by another process right now, so it cannot be read. "
f"{live}; ask me again in a moment")
return (f"the data cache exists but could not be read ({type(exc).__name__}). "
f"{live}; ask me again in a moment")
required = [k for k, v in DS.ENTITIES.items() if not v.get("optional")]
pending = [k for k in required if phases.get(k) != "live"]
if not pending:
return ("the data cache was busy a moment ago and is readable again. "
f"{live}; ask me again now")
return (f"the data cache has not finished syncing: {len(required) - len(pending)} of "
f"{len(required)} required sources are ready. {live}; ask me again once it finishes")
def _measure_sql(m, alias, today=None):
"""SQL for a base measure. Supports FILTERED measures (`store_filter_sql` β the Omni
filtered-measure concept: sum(CASE WHEN β¦ )) and `negate: true` (e.g. GL income, stored
credit-negative, reported positive)."""
agg, f = m.get("agg"), m.get("field")
flt = render_scope(m["store_filter_sql"]) if m.get("store_filter_sql") else None
if agg == "sum":
value = m.get('value_sql') or f"{alias}.{f}"
core = f"sum(CASE WHEN {flt} THEN {value} ELSE 0 END)" if flt else f"sum({value})"
elif agg == "count_distinct":
core = (f"count(DISTINCT CASE WHEN {flt} THEN {alias}.{f} END)" if flt
else f"count(DISTINCT {alias}.{f})")
elif agg == "count":
core = f"count(CASE WHEN {flt} THEN 1 END)" if flt else "count(*)"
elif agg in {"min", "max"}:
value = (f"CAST({alias}.{f} AS DATE)"
if m.get('format') == 'date' or m.get('date_age') else f"{alias}.{f}")
core = f"{agg}(CASE WHEN {flt} THEN {value} END)" if flt else f"{agg}({value})"
else:
raise ModelError(f"metric {m['key']!r}: agg {agg!r} has no store expression")
if m.get('date_age'):
if agg not in {'min', 'max'}:
raise ModelError(f"metric {m['key']!r}: date_age needs a date min or max")
as_of = (f"CAST({_sql_value(dt.date.fromisoformat(str(today)[:10]).isoformat())} AS DATE)"
if today is not None else 'CURRENT_DATE')
core = f"({as_of} - {core})"
return f"-({core})" if m.get("negate") else core
def _expand_measures(keys):
"""Expand ratio/derived metrics into the base measures SQL must aggregate, keeping the
requested keys for post-compute. Returns (base_keys, requested_keys)."""
mts = _model()["metrics"]
base, seen = [], set()
def need(k):
if k in seen:
return
seen.add(k)
m = mts.get(k)
if not m:
raise ModelError(f"unknown metric {k!r}")
if m["agg"] == "ratio":
need(m["numerator"]); need(m["denominator"])
elif m["agg"] == "derived":
import ast as _ast
for node in _ast.walk(_ast.parse(m["expr"], mode="eval")):
if isinstance(node, _ast.Name):
need(node.id)
else:
if k not in base:
base.append(k)
for k in keys:
need(k)
return base, list(keys)
def _post_compute(row, key):
m = _model()["metrics"][key]
if m["agg"] == "ratio":
num, den = _post_compute(row, m["numerator"]), _post_compute(row, m["denominator"])
return (num / den) if den else 0.0
if m["agg"] == "derived":
return _eval_expr(m["expr"], lambda k: _post_compute(row, k))
return row.get(key) or 0.0
# A metric's declared `format` -> the grid field type the compiler and the cells both read.
# One mapping, so a new metric cannot arrive as an untyped column.
_FORMAT_TYPE = {"usd": "currency", "int": "int", "pct": "pct", "date": "date"}
def _clean_tree(tree, cols, tolerate=()):
"""Run a filter tree through the SAME validator the client's view state goes through.
`filter_sql`'s documented input contract is "feed me CLEANED trees" β it is faithful to the
TS engine rather than fail-closed on garbage, because rejecting garbage is the validator's
job. The UI path always cleans (view state is sanitised on the way into the store), but
store_query/store_rows are callable directly, and an uncleaned tree is where the two
diverge: `{"value": null}` is ACTIVE in TS (and throws on a text field) and INACTIVE here.
Normalising at the entry point makes that unreachable rather than merely unlikely, and
brings the depth / width / node-count caps to the server side too. `aios_grid` is a leaf
module with zero imports of its own, so this cannot cycle.
"""
from aios_grid import (clean_filter_tree, COHORT_FIELD as _COHORT_FIELD,
VIEW_FIELD as _VIEW_FIELD, FILTER_OPS as _FILTER_OPS,
COHORT_OPS as _COHORT_OPS, COHORT_OP_ALIASES as _COHORT_ALIASES,
VIEW_OPS as _VIEW_OPS,
MAX_FILTER_DEPTH, MAX_FILTER_NODES, MAX_FILTER_SIBLINGS)
# REFUSE an over-large tree rather than TRUNCATE it. clean_filter_tree caps siblings per
# level, total nodes and depth by DROPPING the excess β correct on the client, where the
# cap is a DoS guard on a per-row render loop and a dropped condition is the lesser evil.
# On the server it is not: dropping AND-conditions WIDENS the result, and store_rows then
# reports a row_count and scope-wide totals that look authoritative for a query the caller
# never asked for. That is precisely the silent [:N] that [[no-unverifiable-aggregates]]
# forbids. A caller that exceeds the caps gets an error, not a quietly different answer.
total = 0
def _walk(nodes, depth):
nonlocal total
nodes = list(nodes or [])
if len(nodes) > MAX_FILTER_SIBLINGS:
raise ModelError(f"filter tree has {len(nodes)} conditions at one level; the limit "
f"is {MAX_FILTER_SIBLINGS}. Refusing rather than dropping the "
f"excess, which would silently widen the result.")
for node in nodes:
if not isinstance(node, dict):
continue
total += 1
if isinstance(node.get('children'), list):
if depth >= MAX_FILTER_DEPTH:
raise ModelError(f"filter tree nests deeper than {MAX_FILTER_DEPTH} levels; "
f"refusing rather than dropping the deepest group.")
_walk(node['children'], depth + 1)
_walk(tree, 1)
if total > MAX_FILTER_NODES:
raise ModelError(f"filter tree has {total} nodes; the limit is {MAX_FILTER_NODES}. "
f"Refusing rather than truncating, which would silently widen it.")
# CG-8. A MEASURE condition ("Sales in the last 90 days > 5,000") is answered by
# harness/measure_filter.py, which resolves it to a SET OF CUSTOMER IDS. It has no meaning
# on this path, and both ways it could arrive here are silent:
# - on a topic without that column, `clean_filter_tree` DROPS it as unknown β widening the
# query while store_rows reports an authoritative row_count for something nobody asked
# for (the same class as the sibling/depth caps above);
# - on sales_lines at row grain, `revenue` IS a real column, so it would compile as a
# per-LINE predicate with the window silently discarded β a different question answered
# confidently, which is worse than dropping it.
# The discriminator is the `window`, not the column name: no column condition has one.
windowed = []
cohorts = []
def _find(nodes):
for node in nodes or []:
if not isinstance(node, dict):
continue
if isinstance(node.get('children'), list):
_find(node['children'])
elif node.get('window') is not None:
windowed.append(str(node.get('colId')))
elif node.get('colId') == _COHORT_FIELD:
cohorts.append(str(node.get('value')))
_find(tree)
# Owner item 5, and the same refusal for the same reason. A cohort leaf names a
# hand-curated set of CUSTOMERS held per user in the tenant store β this path knows nothing
# about it, and `clean_filter_tree` below would drop it as an unknown key, widening the
# query while store_rows still reported an authoritative row_count. If a caller ever needs
# it here, the fix is to pass the membership in, not to let it fall through.
if cohorts:
raise ModelError(
f"filter tree carries cohort-membership condition(s) {sorted(set(cohorts))}. "
f"Cohort membership is host state, not a column of this topic. Refusing rather than "
f"dropping it, which would silently widen the result under an authoritative count.")
if windowed:
raise ModelError(
f"filter tree carries measure condition(s) over a date window {sorted(set(windowed))}"
f". Those are resolved to a set of ids by harness.measure_filter, not compiled into "
f"this query. Refusing rather than dropping or mis-compiling them, either of which "
f"would silently answer a different question under an authoritative count.")
# The UI cleaner drops unknown columns/operators for stale saved views. On a server query
# that would silently widen the result, so authoring faults are refused before normalization.
# `tolerate` covers deliberately narrower row projections.
tolerated = {str(column) for column in (tolerate or ())}
known = set(cols) | tolerated | {_COHORT_FIELD, _VIEW_FIELD}
unknown = []
bad_ops = []
def _inspect(nodes):
for node in nodes or []:
if not isinstance(node, dict):
continue
if isinstance(node.get('children'), list):
_inspect(node['children'])
continue
column = node.get('colId')
if column in (None, ''):
continue
column = str(column)
if column not in known:
unknown.append(column)
continue
if tolerated and column in tolerated and column not in cols:
continue
op = node.get('op')
if op in (None, ''):
continue
legal = (_COHORT_OPS | set(_COHORT_ALIASES) if column == _COHORT_FIELD
else _VIEW_OPS if column == _VIEW_FIELD
else _FILTER_OPS)
if str(op) not in legal:
bad_ops.append((column, str(op), sorted(legal)))
_inspect(tree)
if unknown:
bad = sorted(set(unknown))
extra = f" (and {bad[1:]})" if len(bad) > 1 else ""
raise UnknownColumnError(
f"filter rule names column {bad[0]!r}{extra}, which this topic does not have. "
f"Refusing rather than dropping the rule, which would silently widen the result.",
column=bad[0], columns=bad)
if bad_ops:
column, op, legal = bad_ops[0]
raise ModelError(
f"filter rule on column {column!r} carries operator {op!r}, which is not one this "
f"filter understands (use: {legal}). Refusing rather than dropping the rule, which "
f"would silently widen the result.")
return clean_filter_tree(tree, set(cols))
def _measure_row_sql(m, alias):
"""A sum-metric's per-ROW value: the same expression `_measure_sql` aggregates, without the
aggregate wrapper. At line grain `revenue` IS `l.price_subtotal` for that line β deriving it
from the metric rather than hand-writing the column is what keeps ONE definition of the
number (the drift the semantic layer exists to prevent). Only `sum` has a row value; a
count/count_distinct metric is a property of a SET, not of a row."""
if m.get("agg") != "sum":
return None
f = m.get("field")
flt = render_scope(m["store_filter_sql"]) if m.get("store_filter_sql") else None
core = f"CASE WHEN {flt} THEN {alias}.{f} ELSE 0 END" if flt else f"{alias}.{f}"
return f"-({core})" if m.get("negate") else core
def _business_date_sql(column):
from core import periods
if column.rsplit('.', 1)[-1] not in ('date_order', 'date_done'):
return f'CAST({column} AS TIMESTAMP)'
zone = periods._business_zone().key if hasattr(periods._business_zone(), 'key') else 'UTC'
return f"timezone({_sql_value(zone)}, timezone('UTC', CAST({column} AS TIMESTAMP)))"
def _business_window(column, date_from, date_to, clauses, params):
from core import periods
if column.rsplit('.', 1)[-1] in ('date_order', 'date_done'):
lo, hi = periods.business_day_bounds(date_from, date_to)
for op, bound in (('>=', lo), ('<', hi)):
if bound:
clauses.append(f'CAST({column} AS TIMESTAMP) {op} ?')
params.append(bound.strftime('%Y-%m-%d %H:%M:%S'))
else:
if date_from:
clauses.append(f'CAST({column} AS TIMESTAMP) >= ?')
params.append(f'{date_from} 00:00:00')
if date_to:
clauses.append(f'CAST({column} AS TIMESTAMP) <= ?')
params.append(f'{date_to} 23:59:59')
def store_columns(topic, include_measures=True, grain="aggregate"):
"""The `{colId: {sql, type, aggregate}}` spec `harness.filter_sql` compiles against (CG-1).
`grain="row"` is the LINE-grain projection (CG-2): sum-metrics resolve to their own raw
field with `aggregate=False`, so a line table filters and sorts on real row values in WHERE
rather than needing HAVING. count-style metrics are dropped β they have no row value.
This is the seam between the grid's field contract and the semantic model: the grid speaks
field KEYS, the compiler needs SQL expressions and a field TYPE, and only the model may say
what a key resolves to. Nothing about a topic leaks into filter_sql itself.
<dim> -> the dim's NAME column (what the grid displays and what a user means when
they type "contains floral"), filtered PRE-aggregation in WHERE. Correct
even in a grouped query: every row of a group shares its group's name.
<dim>_id -> the raw id, for exact machine-keyed filtering.
date -> the topic's date column, so a grid can filter/sort it like any other field.
<metric> -> the aggregate expression, marked `aggregate` so the caller routes it to
HAVING rather than WHERE (see store_query β that path is not built yet).
"""
t = _model()["topics"].get(topic)
if not t or "store" not in t:
raise ModelError(f"topic {topic!r} has no store binding")
s = _store_binding(t)
cols = {}
for key, d in (s.get("dims") or {}).items():
cols[key] = {"sql": d.get("name_col") or d["col"], "type": "text", "aggregate": False}
# Only expose a separate id column when the dim actually HAS one. `city` is its own
# name (col == name_col), so a `city_id` would be a text column typed int β filtering
# it numerically would quietly compare 0 against 0 for every row.
if d.get("name_col") and d["name_col"] != d["col"]:
cols[f"{key}_id"] = {"sql": d["col"], "type": "int", "aggregate": False}
if s.get("date_col"):
cols["date"] = {"sql": _business_date_sql(s["date_col"]), "type": "date", "aggregate": False}
if include_measures:
for k, m in _model()["metrics"].items():
if m.get("topic") != topic or m.get("agg") in ("ratio", "derived"):
continue # ratio/derived are post-computed, not SQL
# The field TYPE comes from the metric's declared format, never a guess: `units` is
# format:int, and typing it currency would render 791,960 units as dollars.
ftype = _FORMAT_TYPE.get(m.get("format"), "currency")
if grain == "row":
row_sql = _measure_row_sql(m, s["alias"])
if row_sql:
cols[k] = {"sql": row_sql, "type": ftype, "aggregate": False}
else:
cols[k] = {"sql": _measure_sql(m, s["alias"]),
"type": ftype, "aggregate": True}
return cols
#: The ceiling for a GROUPED `store_query`. One row per group, so this bounds DIMENSION
#: CARDINALITY, not payload β a tenant would need 200,000 distinct customers (or products, or
#: cities) to reach it. Deliberately NOT unbounded: a runaway group-by should fail, not swap.
MAX_GROUPS = 200_000
#: ββ W40-T10 / owner item 26 (ruling R7, contract C6) β THE CHANNEL VOCABULARY, and it is
#: CLOSED. C6 fixes it as `all | fisch | royal | amazon`: `all` is the existing `all_channels`
#: variant (no suffix, the base key), and the three named channels each get their own binding.
#: The display name here is the ONE place a channel is spelled for a human β the key suffix and
#: the "(Fisch)" in a measure label are both derived from it, so there is no second list to drift.
#:
#: β `amazon` IS DEFINED BY THE EXCLUDED PARTNER, NOT BY `team_id = 7`, and that distinction is
#: measured rather than stylistic. `core/odoo.sale_line_domain` fences wholesale on BOTH
#: `team_id IN (5,6)` AND `partner NOT IN excluded_partner_ids()`, and the two are not the same
#: set: GIFTWARE DEALS carries 24 orders on team 7 and 2 on team 5. Defining the channel by team
#: would be a SECOND idea of what Amazon is, disagreeing with the fence the rest of the platform
#: already draws. Taking the partner instead makes the three channels mutually exclusive by
#: construction: `fisch` and `royal` are their team MINUS the excluded partner, `amazon` IS that
#: partner, so no line can be counted twice.
#:
#: β THE THREE DO NOT SUM TO `all`, AND THAT IS A FACT ABOUT THE DATA, NOT A DEFECT. crm.team 1
#: ("Sales") is neither brand nor Amazon and carried $31,988.00 over the trailing year. The
#: channels therefore sum TOWARD the all-channels figure; a gate asserting equality would be
#: asserting something untrue.
_CHANNELS = {
"fisch": {"label": "Fisch"},
"royal": {"label": "Royal"},
"amazon": {"label": "Amazon"},
}
#: The prefix that turns a channel name into a binding's `scope_variant`. Declared once so the
#: parse and the render cannot disagree about where the name starts.
_CHANNEL_VARIANT_PREFIX = "channel_"
def channel_of_variant(scope_variant):
"""The channel name inside a `channel_*` scope variant, or None for every other variant.
The single parser. `_scope_for_binding` uses it to build SQL and `_one_binding_offer` uses it
to build the key suffix and the label, so a variant can never mean one channel to the filter
and another to the column heading.
"""
v = str(scope_variant or "")
if not v.startswith(_CHANNEL_VARIANT_PREFIX):
return None
ch = v[len(_CHANNEL_VARIANT_PREFIX):]
return ch if ch in _CHANNELS else None
def _channel_sql(channel):
"""The WHERE predicate selecting ONE channel out of the confirmed sale-line population.
β THE IDS AND THE PARTNER PATTERN COME FROM `tenant.yml`, never from a literal here. The
tenant already declares `team_names: {5: Fisch, 6: Royal}` and
`excluded_partner_name_like`, and `sales_lines.scope_sql` is built from the same two params β
so the channel filter and the wholesale fence stay one definition. A hard-coded `team_id = 5`
would be a second copy of a number the tenant file owns.
β The `NOT IN (SELECT β¦)` shape is COPIED from `sales_lines.scope_sql` deliberately, down to
the subquery, so a channel's exclusion is byte-for-byte the exclusion the wholesale scope
applies rather than a lookalike that could diverge on NULL handling.
"""
params = _tenant().get("params") or {}
excl = params.get("excluded_partner_name_like")
if not excl:
raise ModelError(
f"channel {channel!r} needs the tenant param `excluded_partner_name_like` to say "
f"which partner IS the Amazon channel, and this tenant declares none")
excluded = f"(SELECT unnest([{_sql_value(excluded_partner_ids(excl))}]::BIGINT[]))"
in_excluded = f"l.order_partner_id IN {excluded}"
if channel == "amazon":
return in_excluded
names = {str(v).strip().lower(): k for k, v in (params.get("team_names") or {}).items()}
tid = names.get(channel)
if tid is None:
raise ModelError(
f"channel {channel!r} is not a team this tenant names. `tenant.yml::team_names` "
f"carries {sorted(names)}, so the channel cannot be resolved to a team id")
return f"o.team_id = {int(tid)} AND l.order_partner_id NOT IN {excluded}"
def _scope_for_binding(store, scope_variant=None):
"""The effective store scope for one ENTITY measure binding.
Most callers use a FACT topic's declared `scope_sql` unchanged. An entity can instead opt
into one of this deliberately closed set of variants in its *binding* (not in the source
topic): W39-T11's Odoo-product movement columns include Amazon, while the `sales_lines`
topic remains the wholesale truth for customer grain, Analyst, saved views, and rollups.
`all_channels` is intentionally defined as confirmed sales only. It drops BOTH wholesale
fences (team and GIFTWARE partner) but does not broaden the fact to quotes/cancellations.
A BU argument must likewise be ignored under this variant: filtering team 5 or 6 after
opening the scope would silently throw Amazon (team 7) back out.
ββ W40-T10 adds `channel_fisch` / `channel_royal` / `channel_amazon` beside it (R7/C6). They
narrow the SAME confirmed-sales population to one channel, so `all_channels` stays the union
and the named three stay disjoint. The BU-suppression argument above generalises with them
and is stated in `store_query`: a variant IS a statement about the channel dimension, so a
caller's `team_id` must never be a second, conflicting statement of it.
"""
if scope_variant is None:
return render_scope(store["scope_sql"].strip())
if scope_variant == "all_channels":
if store.get("table") != "sale_order_line":
raise ModelError("scope_variant 'all_channels' is only defined for sale_order_line")
return "o.state IN ('sale','done')"
ch = channel_of_variant(scope_variant)
if ch:
if store.get("table") != "sale_order_line":
raise ModelError(
f"scope_variant {scope_variant!r} is only defined for sale_order_line")
return f"o.state IN ('sale','done') AND {_channel_sql(ch)}"
raise ModelError(f"unknown entity-measure scope_variant {scope_variant!r}")
def _check_shipped_links(date_from, date_to, *, runtime=None, cur=None):
"""Refuse missing linked observation records instead of dropping their delivered value."""
dates, params = [], []
_business_window('p.date_done', date_from, date_to, dates, params)
window = '(' + (' AND '.join(dates) or 'TRUE') + ' OR p.date_done IS NULL)'
con = cur if cur is not None else (_store_con(runtime) if runtime is not None else _store_con())
try:
missing = con.execute(
"SELECT count(*) FROM stock_move sm LEFT JOIN stock_picking p ON p.id = sm.picking_id "
"LEFT JOIN sale_order_line l ON l.id = sm.sale_line_id "
"LEFT JOIN sale_order o ON o.id = l.order_id "
"WHERE sm.state = 'done' AND sm.sale_line_id IS NOT NULL AND "
"(p.id IS NULL OR (p.state = 'done' AND p.picking_type_code = 'outgoing' AND "
f"{window} AND (l.id IS NULL OR o.id IS NULL OR p.date_done IS NULL)))", params).fetchone()[0]
finally:
if cur is None:
con.close()
if missing:
raise ModelError('Shipped sales require complete linked observation records')
@_runtime_bound
def store_query(topic, measures, group_by=None, grain=None, date_from=None, date_to=None,
team_id=None, filters=None, sort=None, limit=1000, exclude_services=False,
filter_tree=None, filter_conj="and", today=None, scope_variant=None, related=None,
*, runtime=None, user=None, cur=None, group_keys=None):
"""The semantic query over the tenant store β the engine behind the Analyst's
run_semantic_query tool and the saved-view re-runner. All keys whitelisted against the model."""
t = _model()["topics"].get(topic)
if not t or "store" not in t:
raise ModelError(f"topic {topic!r} has no store binding")
if topic == 'amazon_orders':
if (list(measures or []) != ['amazon_order_count'] or team_id is not None or filters
or filter_tree or scope_variant is not None or related is not None or exclude_services):
raise ModelError('Amazon Sales summary cannot validate a different measure or restricted scope')
from harness import amazon_semantic
return amazon_semantic.query(date_from, date_to, runtime=runtime, user=user,
group_by=group_by, grain=grain, sort=sort, limit=limit)
runtime = runtime if runtime is not None else _TENANT_RUNTIME.get()
s = _store_binding(t)
pin_predicate, pin_params = None, []
if related is not None:
from core import relations
try:
pin = relations.measure_pin(related)
if pin.refusal:
raise relations.PinRefusal(pin.refusal['code'], pin.refusal['message'], pin.path)
pin_predicate, pin_params = pin.predicate(s)
except relations.PinRefusal as exc:
return {"topic": topic, "rows": [], "row_count": 0, "truncated": False,
"refusals": [exc.marker]}
alias = s["alias"]
dims = s.get("dims") or {}
# β A GROUPED QUERY IS BOUNDED BY GROUPS, NOT BY ROWS β and the 5,000 row ceiling applied to
# both, which made it a SILENT TRUNCATION of the answer rather than of a payload.
#
# β MEASURED 2026-08-09 on the Royal mirror, grouping 256,810 order lines by customer:
# limit=100 -> 100 groups, total 1,644,181.65
# limit=1000 -> 1000 groups, total 9,042,614.10
# limit=5000 -> 1748 groups, total 14,567,929.72
# The TOTAL MOVES WITH THE CAP. A row window is honest because counts and totals are computed
# over the full scope beside it (`store_rows`' whole design); a GROUP window has no such
# companion β the groups ARE the answer, so dropping one is dropping data with nothing to
# notice. Royal has 1,943 customers so it passes today and would have passed every test,
# then gone quietly wrong for the first tenant with more ([[no-unverifiable-aggregates]]).
#
# β The ceiling is not removed, because unbounded is its own failure. It is raised to a bound
# no realistic dimension reaches, and β the load-bearing half β truncation is now a FACT the
# caller can read rather than something it must infer from `len(rows)`.
grouped = bool(group_by)
limit = max(1, min(int(limit or 1000), MAX_GROUPS if grouped else 5000))
base_keys, requested = _expand_measures(list(measures or []))
if not base_keys:
raise ModelError("at least one measure required")
if runtime is not None and cur is not None:
runtime.assert_datastore_matches(cur=cur)
if runtime is not None and any(key in _STANDARD_METRICS for key in base_keys):
runtime.assert_datastore_matches()
# Cross-topic base metrics (e.g. aov = revenue Γ· orders, where orders lives on sales_orders):
# in a SCALAR query they resolve via a nested scalar query on their HOME topic (correct grain β
# never count(*) on the wrong table); in a GROUPED query they are refused (split the request).
foreign = [k for k in base_keys if _model()["metrics"][k]["topic"] != topic]
if foreign and (group_by or grain):
raise ModelError(f"metrics {foreign} belong to another topic. Cross-topic measures are "
f"scalar-only; run them against their own topic when grouping")
base_keys = [k for k in base_keys if k not in foreign]
for metric_key in base_keys:
if metric_key not in _STANDARD_METRICS:
continue
if team_id is not None and not s.get('team_col'):
raise ModelError('This metric has no proven business-unit attribution')
if team_id is not None and scope_variant is None:
teams = (_tenant().get('params') or {}).get('wholesale_team_ids') or []
try:
known_team = int(team_id) in teams
except (TypeError, ValueError):
known_team = False
if not known_team:
raise ModelError('The requested business unit is outside the configured wholesale scope')
for source in _model()['metrics'][metric_key].get('required_sources') or ():
ready = (_source_ready(source, runtime=runtime, cur=cur)
if runtime is not None or cur is not None else _source_ready(source))
if ready is not True:
raise ModelError(f'Standard metric source {source!r} is not ready in the observation mirror')
for source in _model()['metrics'][metric_key].get('required_backfills') or ():
from harness import datastore
con = cur if cur is not None else (_store_con(runtime) if runtime is not None else _store_con())
try:
complete = datastore._columns_backfilled(con, source, datastore.ENTITIES[source])
finally:
if cur is None:
con.close()
if not complete:
raise ModelError(f'Standard metric source {source!r} needs its declared field backfill')
if metric_key == 'shipped':
if runtime is not None or cur is not None:
_check_shipped_links(date_from, date_to, runtime=runtime, cur=cur)
else:
_check_shipped_links(date_from, date_to)
for metric_key in base_keys:
if metric_key in _STANDARD_METRICS:
continue
sources = _model()['metrics'][metric_key].get('required_backfills')
pending = (_pending_backfills(sources, cur=cur, runtime=runtime)
if runtime is not None else _pending_backfills(sources, cur=cur))
if pending:
raise NotSyncedError(_model()['metrics'][metric_key], pending)
foreign_vals = {}
for k in foreign:
sub = store_query(_model()["metrics"][k]["topic"], [k], date_from=date_from,
date_to=date_to, team_id=team_id, related=related,
runtime=runtime, user=user, cur=cur)
if sub.get("refusals"):
return sub
foreign_vals[k] = sub["rows"][0][k] if sub["rows"] else 0
if not base_keys: # purely foreign scalar request
return {"topic": topic, "rows": [foreign_vals], "row_count": 1, "sql": "(nested)",
"measures": requested, "group_by": [], "grain": None}
select, group_cols, params = [], [], []
gb = [g for g in (group_by or []) if g]
for g in gb:
if g not in dims:
raise UnknownColumnError(
f"group_by {g!r} not a dim of {topic!r} (allowed: {list(dims)})", column=g)
d = dims[g]
select.append(f"{d['col']} AS {g}_id" if d.get("name_col") else f"{d['col']} AS {g}")
group_cols.append(d["col"])
if d.get("name_col"):
select.append(f"max({d['name_col']}) AS {g}")
if grain:
if grain not in ("month", "week", "day"):
raise ModelError("grain must be month|week|day")
day_expr = _business_date_sql(s['date_col'])
select.insert(0, f"date_trunc('{grain}', {day_expr}) AS period")
group_cols.insert(0, f"date_trunc('{grain}', {day_expr})")
for k in base_keys:
select.append(f"{_measure_sql(_model()['metrics'][k], alias, today=today)} AS {k}")
where = [_scope_for_binding(s, scope_variant)]
if pin_predicate is not None:
where.append(f'({pin_predicate})')
params.extend(pin_params)
# TYPE-CORRECT date bounds (the 2025-07-01 lesson): columns hold ISO strings in TWO shapes β
# datetimes ('YYYY-MM-DD HH:MM:SS', sale date_order) and bare dates ('YYYY-MM-DD', GL date).
# Lexical string comparison EXCLUDES the window's first day for bare dates ('2025-07-01' <
# '2025-07-01 00:00:00'), which silently dropped a whole day of GL ($49,467). Cast both sides.
if s['date_col'].rsplit('.', 1)[-1] in ('date_order', 'date_done'):
# A stored UTC datetime is cut at the tenant business day. A bare date keeps its day.
from core import periods
lo, hi = periods.business_day_bounds(date_from, date_to)
if lo:
where.append(f"CAST({s['date_col']} AS TIMESTAMP) >= ?")
params.append(lo.strftime('%Y-%m-%d %H:%M:%S'))
if hi:
where.append(f"CAST({s['date_col']} AS TIMESTAMP) < ?")
params.append(hi.strftime('%Y-%m-%d %H:%M:%S'))
else:
if date_from:
where.append(f"CAST({s['date_col']} AS TIMESTAMP) >= ?")
params.append(f"{date_from} 00:00:00")
if date_to:
where.append(f"CAST({s['date_col']} AS TIMESTAMP) <= ?")
params.append(f"{date_to} 23:59:59")
# β A BINDING SCOPE VARIANT OWNS THE CHANNEL DIMENSION, so a caller's BU fence is suppressed
# under EVERY variant, not only under `all_channels`. The variant set IS the channel
# vocabulary (W40-T10/C6): `all_channels` is the union and `channel_*` names one member, so
# intersecting either with a `team_id` restates the same dimension a second time and the two
# statements can disagree. Concretely, `channel_amazon` AND `team_id = 5` returns an empty
# cell that reads as "this SKU sold nothing on Amazon" β a FALSE number, not a narrower one.
if team_id and (scope_variant is None or related is not None):
if not s.get("team_col"):
raise ModelError(f"topic {topic!r} is company-level: it has no business-unit filter")
where.append(f"{s['team_col']} = ?"); params.append(int(team_id))
if exclude_services and s.get("service_filter"):
where.append(render_scope(s["service_filter"]))
for dim_key, vals in (filters or {}).items():
if dim_key not in dims:
raise UnknownColumnError(
f"filter dim {dim_key!r} not allowed (use: {list(dims)})", column=dim_key)
# id dims filter by int; TEXT dims (e.g. city) filter by the value itself β either way
# every VALUE stays parameterized (the whitelist covers identifiers only)
def _coerce(v):
try:
return int(v)
except (TypeError, ValueError):
return str(v)
ids = [_coerce(v) for v in (vals if isinstance(vals, (list, tuple)) else [vals])]
where.append(f"{dims[dim_key]['col']} IN ({','.join('?' for _ in ids)})")
params.extend(ids)
if group_keys is not None:
if len(gb) != 1:
raise ModelError('Selected entity keys require exactly one grouping dimension')
values = list(group_keys)
if values:
where.append(f"{dims[gb[0]]['col']} IN ({','.join('?' for _ in values)})")
params.extend(values)
else:
where.append('FALSE')
# The grid's filter TREE (CG-1). `filters` above stays the Analyst's flat dim=IN shape;
# this is the nested, 11-operator contract the table UI emits. Compiled by
# harness/filter_sql, which is held in lock-step with the TS engine and the validator by
# aios-web/verify_filter_engine.py. Appended AFTER the dim filters so params stay in
# positional order with `where`.
if filter_tree:
from harness import filter_sql as _fs
cols = store_columns(topic)
pred = _fs.compile_filter_tree(_clean_tree(filter_tree, cols), filter_conj, cols,
today=today)
if pred is not None:
if pred.uses_aggregate:
bad = sorted(c for c in pred.columns_used if cols[c].get("aggregate"))
raise ModelError(
f"filter_tree references measure(s) {bad}. A measure filter has to land in "
f"HAVING, and that path is deliberately NOT built: CG-1 ships the WHERE path "
f"because its consumer (CG-2, the line-grain sales table) does not aggregate. "
f"Filter on dims, or aggregate first and filter the result.")
where.append(pred.sql)
params.extend(pred.params)
sql = (f"SELECT {', '.join(select)} FROM {s['table']} {alias} {s.get('join','')} "
f"WHERE {' AND '.join(where)}")
if group_cols:
sql += f" GROUP BY {', '.join(group_cols)}"
if sort:
key = sort.lstrip("-")
if key not in base_keys + gb + (["period"] if grain else []):
raise ModelError(f"sort {sort!r} must reference a selected measure/dim")
sql += f" ORDER BY {key} {'DESC' if sort.startswith('-') else 'ASC'}"
elif grain:
sql += " ORDER BY period"
# β `limit + 1` β ONE extra row, so "did this truncate" is a FACT and not the guess
# `len(rows) == limit` makes (which is wrong exactly when the count lands on the cap).
sql += f" LIMIT {limit + 1}"
con = cur if cur is not None else (_store_con(runtime) if runtime is not None else _store_con())
try:
result = con.execute(sql, params)
cols = [d[0] for d in result.description]
rows = [dict(zip(cols, r)) for r in result.fetchall()]
finally:
if cur is None:
con.close()
for row in rows: # ratio/derived post-compute per group
row.update(foreign_vals) # scalar cross-topic components
for k in requested:
if _model()["metrics"][k]["agg"] in ("ratio", "derived"):
row[k] = _post_compute(row, k)
elif _model()["metrics"][k].get("format") == "date":
row[k] = str(row[k])[:10] if row.get(k) is not None else None
if "period" in row and row["period"] is not None:
row["period"] = str(row["period"])[:10]
truncated = len(rows) > limit
if truncated:
rows = rows[:limit]
return {"topic": topic, "rows": rows, "row_count": len(rows), "sql": sql,
"measures": requested, "group_by": gb, "grain": grain,
# β A CALLER THAT AGGREGATES THESE ROWS MUST CHECK THIS. For a grouped query the
# groups ARE the answer, so a truncated result is a WRONG NUMBER, not a short list.
"truncated": truncated}
def store_rows(topic, date_from=None, date_to=None, team_id=None, filter_tree=None,
filter_conj="and", search=None, sorts=None, limit=200, offset=0,
aggregates=None, exclude_services=False, id_sql=None, member_ids=None,
today=None):
"""ROW-grain fetch β the line-grain counterpart to store_query's aggregate path (CG-2).
store_query answers "what is the number"; this answers "which rows". At line grain the
payload is the binding constraint (254,837 sale_order_lines against 1,550 customers β the
whole book would be ~96 MB at the customer table's measured 377 B/row), so rows come back
WINDOWED.
A window is only honest if the counts and totals are not windowed with it
([[no-unverifiable-aggregates]]). So this returns THREE independent numbers, each from its
own query over the FULL scope, never from `len(rows)`:
row_count rows matching the filter across the whole scope -> the N of "N of M"
total_count rows in the scope with no filter at all -> the M
aggregates the topic's OWN metric definitions, summed over the whole FILTERED scope
That is the difference between a windowed fetch and a silent `[:N]` cap: the user is told
how many rows exist and shown totals for all of them, while receiving only the page they
can see.
Filters/sorts/search compile through harness.filter_sql, so a line table means EXACTLY what
the client engine means at customer grain β that equivalence is what verify_filter_engine.py
exists to hold.
Dates are returned already truncated to the 10-char ISO shape the filter compares against,
so what is displayed and what is filtered are the same string. Numerics come back RAW; the
payload layer rounds them (aios_grid._round), and the compiler mirrors that rounding.
"""
t = _model()["topics"].get(topic)
if not t or "store" not in t:
raise ModelError(f"topic {topic!r} has no store binding")
if topic == 'amazon_orders':
raise ModelError('Amazon semantic rows require a scoped database reader; use the Data row door')
from harness import filter_sql as _fs
s = _store_binding(t)
alias = s["alias"]
cols = store_columns(topic, grain="row")
id_sql = id_sql or f"{alias}.id"
limit = max(1, min(int(limit or 200), 5000))
offset = max(0, int(offset or 0))
# --- the scope every one of the three queries shares -------------------------------
scope, scope_params = [render_scope(s["scope_sql"].strip())], []
_business_window(s['date_col'], date_from, date_to, scope, scope_params)
if team_id:
if not s.get("team_col"):
raise ModelError(f"topic {topic!r} is company-level: it has no business-unit filter")
scope.append(f"{s['team_col']} = ?")
scope_params.append(int(team_id))
if exclude_services and s.get("service_filter"):
scope.append(render_scope(s["service_filter"]))
# --- the user's narrowing, on top of the scope --------------------------------------
narrow, narrow_params = [], []
pred = _fs.compile_filter_tree(_clean_tree(filter_tree or [], cols,
tolerate=store_columns(topic)),
filter_conj, cols,
today=today,
member_ids=member_ids or None, id_sql=id_sql)
if pred is not None:
if pred.uses_aggregate: # cannot happen at grain="row"; guard anyway
raise ModelError(f"row-grain filter referenced an aggregate: {sorted(pred.columns_used)}")
narrow.append(pred.sql)
narrow_params.extend(pred.params)
spred = _fs.compile_search(search, cols)
if spred is not None:
narrow.append(spred.sql)
narrow_params.extend(spred.params)
frm = f"{s['table']} {alias} {s.get('join', '')}"
scope_where = " AND ".join(scope)
all_where = " AND ".join(scope + narrow)
con = _store_con()
try:
# 1. the WINDOW of rows
select = [f"{id_sql} AS _rid"] + [
(f"SUBSTR(CAST({c['sql']} AS VARCHAR), 1, 10) AS {k}" if c["type"] == "date"
else f"{c['sql']} AS {k}")
for k, c in cols.items()]
order = _fs.compile_order_by(sorts, cols, tiebreak_sql=id_sql) or f"{id_sql} ASC"
sql = (f"SELECT {', '.join(select)} FROM {frm} WHERE {all_where} "
f"ORDER BY {order} LIMIT {limit} OFFSET {offset}")
cur = con.execute(sql, scope_params + narrow_params)
names = [d[0] for d in cur.description]
rows = [dict(zip(names, r)) for r in cur.fetchall()]
# 2. the two counts β over the WHOLE scope, never len(rows)
count_sql = f"SELECT count(*) FROM {frm} WHERE {all_where}"
row_count = con.execute(count_sql, scope_params + narrow_params).fetchone()[0]
total_count = con.execute(f"SELECT count(*) FROM {frm} WHERE {scope_where}",
scope_params).fetchone()[0]
# 3. the aggregates β the topic's OWN metric definitions over the whole FILTERED scope
mts = _model()["metrics"]
keys = [k for k in (aggregates if aggregates is not None
else [k for k, m in mts.items()
if m.get("topic") == topic and m.get("agg") == "sum"])]
aggs = {}
if keys:
for k in keys:
if k not in mts or mts[k].get("topic") != topic:
raise ModelError(f"aggregate {k!r} is not a metric of topic {topic!r}")
agg_sql = ("SELECT " + ", ".join(f"{_measure_sql(mts[k], alias)} AS {k}" for k in keys)
+ f" FROM {frm} WHERE {all_where}")
cur = con.execute(agg_sql, scope_params + narrow_params)
aggs = dict(zip([d[0] for d in cur.description], cur.fetchone()))
finally:
con.close()
return {
"topic": topic,
"rows": rows,
"columns": {k: {"type": c["type"]} for k, c in cols.items()},
"window": {"offset": offset, "limit": limit, "returned": len(rows)},
"row_count": row_count, # N β matches the filter, across the WHOLE scope
"total_count": total_count, # M β the unfiltered scope
"aggregates": aggs, # over the whole FILTERED scope, not the window
"sql": sql,
"count_sql": count_sql,
}
def store_field_values(topic, dim, search=None, limit=25):
"""Resolve real filterable values for a dim (the Analyst's get_field_values tool β fixes
'NYC Florist' vs the actual record name BEFORE querying)."""
t = _model()["topics"].get(topic)
if not t or "store" not in t:
raise ModelError(f"topic {topic!r} has no store binding")
if topic == 'amazon_orders':
raise ModelError('Amazon field values require a scoped database reader; use the Data field door')
s = _store_binding(t)
d = (s.get("dims") or {}).get(dim)
if not d:
raise UnknownColumnError(f"unknown dim {dim!r} for topic {topic!r}", column=dim)
if not d.get("name_col"):
raise ModelError(f"dim {dim!r} has no name column (filter by id)")
sql = (f"SELECT DISTINCT {d['col']} AS id, {d['name_col']} AS name "
f"FROM {s['table']} {s['alias']} {s.get('join','')} "
f"WHERE {render_scope(s['scope_sql'].strip())}")
params = []
if search:
sql += f" AND {d['name_col']} ILIKE ?"
params.append(f"%{search}%")
sql += f" ORDER BY name LIMIT {max(1, min(int(limit), 100))}"
con = _store_con()
try:
return [{"id": r[0], "name": r[1]} for r in con.execute(sql, params).fetchall()]
finally:
con.close()
# --- ENTITY LOOKBACK MEASURES (W37-T10 / owner item 4, ruling R1) -------------------------
#
# ββ THE FEATURE, STATED ONCE: a database that is a REGISTRY (`odoo_products`, `odoo_agents` β
# a catalogue, no date column of its own) gets columns that measure a FACT topic over a window
# and key the answer back to its own rows. "Revenue, last 90 days" as a column on the SKU grid.
#
# β WHY THIS IS NOT `core.measure_resolve`. That family is CUSTOMER-GRAIN by construction β it
# resolves against `allowed_pids` that ARE partner ids, through `harness.measure_filter`, whose
# `_entity_sql` names the customer entity. Neither file is in this wave's fences, so generalising
# them was not available; this is the entity-neutral path beside it, and the duplication is
# DELIBERATE and BOOKED (see the wave-37 lane-B mailbox, ownership-gap learning). The day one
# lane owns both, `measure_resolve.column_values` should become a thin caller of this.
#
# β AN OFFER IS A PROMISE. `_rollup_source_offer`'s docstring makes this argument for rollups and
# it is the same argument here: a measure this returns but cannot answer mints a column that sits
# BLANK forever looking configured β no error, nothing to notice. So `entity_measures()` refuses
# a key it cannot prove, rather than passing the model's list through.
class _EntityMeasureError(ModelError):
"""A binding problem, not a data problem β raised at OFFER time so it never reaches a cell."""
def topic_for_grid(grid_key):
"""The ENTITY topic that describes the database `grid_key`, or None.
β THE BINDING IS ALREADY DECLARED AT BOTH ENDS and this reads it rather than adding a third
place to state it: `odoo_products.yml` carries `grid: product_data`, `odoo_agents.yml` carries
`grid: ut_odoo_agents` (W33-T46's own convention β *"the same store key the nav opens"*). A
hand-written `{table_key: topic}` map in a route would be a second definition of one fact and
would drift the day a topic is renamed.
"""
key = str(grid_key or "").strip()
if not key:
return None
for tkey, t in _model()["topics"].items():
if str(t.get("grid") or "") == key:
return tkey
return None
def entity_measure_bindings(topic):
"""`[{source, dim, keys, scope_variant, not_yet}, β¦]` β every FACT topic this entity draws
columns from.
ββ A LIST, NOT ONE BINDING, since W37-T13. An entity's columns legitimately come from more
than one fact topic: the product grid takes revenue/units/margin from `sales_lines` and stock
in/out from `stock_moves`, and those are different tables joined by different dims. Collapsing
them into one source would have forced stock movement into the sales topic, where it has no
row β which is how a metric ends up defined twice.
β BOTH SHAPES PARSE. A topic may declare `measures:` as a single mapping (the wave's original
form, still used by `odoo_agents`) or as a LIST of them. Accepting only the list would have
been a silent break of every binding written before this change.
Structural read only: this says what the model CLAIMS. `entity_measures()` is what proves it.
"""
t = _model()["topics"].get(topic)
if not t:
raise ModelError(f"unknown topic {topic!r}")
raw = t.get("measures")
if isinstance(raw, dict):
raw = [raw]
if not isinstance(raw, list):
return []
out = []
for b in raw:
if not isinstance(b, dict) or not b.get("source") or not b.get("dim"):
continue
variant = b.get("scope_variant")
out.append({"source": str(b["source"]), "dim": str(b["dim"]),
"scope_variant": str(variant) if variant is not None else None,
"keys": list(b.get("keys") or []), "not_yet": list(b.get("not_yet") or [])})
return out
def entity_measure_binding(topic):
"""The FIRST binding, or None β kept for callers that predate the multi-source shape."""
bs = entity_measure_bindings(topic)
return bs[0] if bs else None
def entity_measures(topic, strict=False):
"""The measure CATALOGUE an entity topic may offer -> `[{key,label,type,format,empty,β¦}]`.
The shape is the workspace `measures` contract (`{key, label, type}` β `customer-grid/types.ts`
`Measure`), with the extra keys a resolver needs carried alongside; the client reads the three
it knows and ignores the rest.
β FOUR REFUSALS, each one a column that would otherwise render blank forever:
1. the metric does not exist, or does not live on the SOURCE topic β a foreign base measure
is refused by `store_query` the moment a `group_by` is present, so offering it mints a
column that can only ever error (this is exactly what excludes `aov` and `orders`);
2. the dim is not a dim of the source topic β nothing to group by;
3. the metric declares no `empty:` family β C1: a metric that cannot say what a row with no
activity renders as does not ship, because the default IS a claim about truth;
4. the metric's format has no grid type β `MEASURE_FIELD_TYPES` is {currency,int,pct}, so a
date- or text-valued measure cannot render as a column whatever we promise.
`strict=True` raises on the first refusal (the gate's mode). Otherwise a refused key is simply
absent from the offer and its reason is available through `entity_measure_refusals`.
"""
return _entity_offer(topic, strict=strict)[0]
def entity_measure_refusals(topic):
"""`[{key, reason}]` β every declared key `entity_measures` would NOT offer, and why.
β The reporting half of standing rule 1 applied to a catalogue: a key that drops out has a
stated cause rather than simply being missing from a list nobody diffs.
"""
return _entity_offer(topic)[1]
#: The offer is a pure function of the MODEL FILES, and `_model()` is already `lru_cache`d β so
#: this caches the derivation, not the read. It matters because `routes_grid::events` rebuilds the
#: product assembly on EVERY write event (hide a field, save a view, patch a cell), and W30-T30
#: records what putting real work on that path costs. Cleared by `reload_model()` like every other
#: model-derived cache, so a yml edit is not stale until a restart.
_ENTITY_OFFER_CACHE = {}
def _entity_offer(topic, strict=False):
if _TENANT_RUNTIME.get() is not None or O._ctx() is not None:
return _entity_offer_uncached(topic, strict=strict)
if not strict and topic in _ENTITY_OFFER_CACHE:
return _ENTITY_OFFER_CACHE[topic]
out = _entity_offer_uncached(topic, strict=strict)
# ββ AN INDETERMINATE ANSWER IS NEVER CACHED, and this is the more dangerous half of the
# guard above. The cache lives for the PROCESS, so a cold start that could not reach the store
# would freeze its "I don't know" into the offer every later request reads β long after the
# mirror came up. Refusing is safe; refusing FOREVER because of one early request is not.
# β The symmetric error is just as bad in the other direction: cache an over-broad offer taken
# during warmup and the column is promised for the process lifetime.
if not strict and not any(r.get("indeterminate") for r in out[1]):
_ENTITY_OFFER_CACHE[topic] = out
return out
def _entity_offer_uncached(topic, strict=False):
out, refused = [], []
seen = set()
# β ONE READINESS PROBE PER TABLE, not per BINDING (W40-T10). `_source_ready` opens a store
# connection and costs ~440 ms cold; the channel split took `odoo_products` from two bindings
# to five, and FOUR of those five ask about the same `sale_order_line`. Measured on this tree:
# 5 probes = 2,194 ms cold, 2 probes = 878 ms β so without this the split would have put
# 1.3 s onto the first `routes_grid::events` rebuild after every restart, which is exactly the
# path W30-T30 records the cost of. Scoped to ONE build deliberately: the process-lifetime
# cache above still refuses to memoise an indeterminate answer, and this must not weaken that.
ready = {}
for b in entity_measure_bindings(topic):
o, r = _one_binding_offer(topic, b, strict=strict, ready=ready)
# β FIRST BINDING WINS A DUPLICATE KEY, and the collision is REPORTED rather than
# silently resolved: two fact topics offering the same metric key would otherwise give a
# column whose source depends on dict order.
for m in o:
if m["key"] in seen:
refused.append({"key": m["key"],
"reason": f"already offered by another source topic; "
f"{b['source']}'s copy is not used"})
continue
seen.add(m["key"])
out.append(m)
refused.extend(r)
return out, refused
def _source_ready(name, *, runtime=None, cur=None):
"""Is this mirror table present AND finished backfilling? `None` when it cannot be asked.
β AN OFFER IS A PROMISE, AND A MODEL BINDING IS NOT EVIDENCE THE DATA IS THERE. A metric can
resolve perfectly in the model and still have no rows to group, because the mirror is synced
per ENTITY and a Space hydrates from a snapshot taken before the entity existed β which is
exactly the window W37-T13 opens by adding `stock_move`. Offering a column then would mint a
Metric field that renders BLANK forever with no error anywhere, this repo's most repeated
defect shape.
"""
# β TWO CONDITIONS, NOT ONE. A table can EXIST and be half-backfilled β `_ensure_table` runs
# before the first batch β and a grouped query over a partial table returns totals that are
# wrong in the one direction nobody checks: too small, per row, with no error. `entity_live`
# is the per-entity phase, which `ready()` deliberately no longer covers for an optional
# entity (see its comment: covering it would make adding one an outage).
try:
import harness.datastore as _ds
runtime = runtime if runtime is not None else _TENANT_RUNTIME.get()
handle = getattr(cur, '_mirror_handle', None)
if handle is None and runtime is not None:
handle = runtime.mirror_handle()
if handle is None:
return None
if handle is not None:
if not handle.ready():
return None
if not handle.entity_live(name):
return False
elif cur is not None:
row = cur.execute("SELECT phase FROM _sync_state WHERE entity = ?", [name]).fetchone()
if not row or row[0] != 'live' or not _ds._required_columns_ready(
cur, name, _ds.ENTITIES.get(name) or {}):
return False
# ββ "THERE IS NO STORE" IS NOT "THIS ENTITY IS NOT SYNCED", AND CONFLATING THEM CACHES
# AN EMPTY OFFER FOR THE PROCESS. `entity_live` answers False for both, so without this
# line a cold container with no mirror yet refuses EVERY binding, that refusal is
# definite rather than indeterminate, and it is memoised β so the grid serves no measures
# at all long after the mirror arrives. Measured: with the store path pointed at a file
# that does not exist, the offer came back `[]` AND was cached.
elif not _ds.DB_PATH.exists() or not _ds.ready():
return None # cannot ask -> INDETERMINATE, and never cached
elif not _ds.entity_live(name):
return False # asked, and this entity really is not live yet
con = cur if cur is not None else (_store_con(runtime) if runtime is not None else _store_con())
except Exception: # noqa: BLE001
return None # cannot ask -> INDETERMINATE
try:
con.execute(f"SELECT 1 FROM {name} LIMIT 0")
return True
except Exception: # noqa: BLE001
return False
finally:
try:
if cur is None:
con.close()
except Exception: # noqa: BLE001
pass
def _one_binding_offer(topic, b, strict=False, ready=None):
src = _model()["topics"].get(b["source"])
if not src or "store" not in src:
raise _EntityMeasureError(
f"{topic}: measure source {b['source']!r} has no store binding")
dims = (src["store"].get("dims") or {})
if b["dim"] not in dims:
raise _EntityMeasureError(
f"{topic}: measure dim {b['dim']!r} is not a dim of {b['source']!r} "
f"(allowed: {sorted(dims)})")
# Validate the declared variant at OFFER time. An unanswerable binding must be refused
# before the UI promises a metric column that only errors when its first cell is fetched.
_scope_for_binding(src["store"], b.get("scope_variant"))
# ββ W40-T10 / R7 β THE CHANNEL RIDES THE OFFERED KEY, and this is the whole mechanism.
#
# β IT CANNOT RIDE THE STORED METRIC SPEC, which is what C6's wording suggests and what an
# implementer will try first: `aios_grid.clean_measure_field` REBUILDS the stored dict from
# known members and returns `{"key", "window"}`, so a `channel` member is dropped on save
# with nothing to notice. The key survives that rebuild (up to 80 chars) β so the channel
# goes IN the key, and "Revenue (Fisch)" and "Revenue (Amazon)" become two distinct offers
# over one metric. No client change at all: the picker already offers what the catalogue does.
#
# β AND IT MUST NOT BECOME A REAL METRIC. Synthesising `revenue_fisch` into
# `_model()["metrics"]` would make every one of these resolve natively β and would inject 18
# channel keys into `sem.metrics()`, which is enumerated by the Analyst tool schema
# (`harness/tools.py`), the CUSTOMER grid's measure vocabulary (`harness/measure_filter.py`),
# the metric dictionary and `routes_tables`. On `sales_lines`' own wholesale scope
# `revenue_amazon` is 0 by construction, so those surfaces would be offered a metric that can
# only ever answer zero. The suffixed key therefore lives ONLY between `entity_measures` and
# `entity_measure_values`, and `base_key` below is what carries it home.
_chan = channel_of_variant(b.get("scope_variant"))
_suffix = f"_{_chan}" if _chan else ""
_chan_label = _CHANNELS[_chan]["label"] if _chan else ""
# β THE TABLE CHECK, before any key is offered from this binding.
_tbl = (src["store"] or {}).get("table")
if not _tbl:
_rdy = True
elif ready is not None and _tbl in ready:
_rdy = ready[_tbl] # already probed in THIS build β see `ready` above
else:
_rdy = _source_ready(_tbl)
if ready is not None:
ready[_tbl] = _rdy
if _rdy is not True:
# β `None` REFUSES TOO, and that is the opposite of the obvious reading. `None` means "the
# store could not be asked" β a warming or absent mirror β and a store that cannot answer
# THIS question cannot answer the grouped query either, so offering the column would mint
# exactly the permanently-blank field the check exists to prevent. Fail closed.
why = ((f"the mirror has not finished syncing `{_tbl}`, so every key from {b['source']!r} "
f"would render blank, or worse, as a too-small number from a half-filled table; "
f"run `datastore.sync_entity('{_tbl}')` until its phase is `live`")
if _rdy is False else
(f"the tenant store could not be read, so whether `{_tbl}` can answer is UNKNOWN; "
f"refusing rather than promising a column that may render blank"))
if strict:
raise _EntityMeasureError(f"{topic}: {why}")
return [], [{"key": k + _suffix, "reason": why, "indeterminate": _rdy is None}
for k in b["keys"]]
mts = _model()["metrics"]
out, refused = [], []
def refuse(key, reason):
# Reported under the key the OFFER would have carried, so a refusal names the column the
# user did not get rather than a base metric they never asked for.
key = key + _suffix
if strict:
raise _EntityMeasureError(f"{topic}: measure {key!r} refused: {reason}")
refused.append({"key": key, "reason": reason})
for key in b["keys"]:
m = mts.get(key)
if not m:
refuse(key, "no such metric in the model")
continue
# β THE CROSS-TOPIC TEST IS ON THE EXPANDED BASE, NOT THE METRIC'S OWN `topic`. `aov`
# declares no topic of its own and would pass a naive check; its DENOMINATOR `orders`
# lives on `sales_orders`, and that is what `store_query` refuses under a group_by.
try:
base, _ = _expand_measures([key])
except ModelError as e:
refuse(key, str(e))
continue
foreign = sorted({k for k in base if mts[k].get("topic", b["source"]) != b["source"]})
if foreign:
refuse(key, f"base measure(s) {foreign} live on another topic. Cross-topic measures "
f"are scalar-only and are refused under a group_by")
continue
empty = m.get("empty")
if empty not in ("zero", "blank"):
refuse(key, "declares no `empty:` family (C1: zero for additive, blank for a ratio)")
continue
ftype = _FORMAT_TYPE.get(m.get("format"))
if ftype not in ("currency", "int", "pct"):
refuse(key, f"format {m.get('format')!r} has no grid measure type "
f"(aios_grid.MEASURE_FIELD_TYPES is currency|int|pct)")
continue
out.append({
# The OFFERED key: the metric key, namespaced by channel. `_entity_offer_uncached`
# refuses a duplicate key across bindings ("first binding wins"), so four bindings
# over the same six metrics REQUIRE this to be distinct β the namespacing is what
# makes the split exist at all, not a cosmetic prefix on a label.
"key": key + _suffix,
# β THE WAY HOME. `store_query` resolves `measures` against `_model()["metrics"]`,
# where no suffixed key exists β so `entity_measure_values` translates back to this
# before querying and renames on the way out. Read it as `.get("base_key") or key`:
# gate fixtures that build a synthetic offer by hand do not carry this member.
"base_key": key,
# β THE LABEL IS THE METRIC'S OWN PLUS THE CHANNEL, in that order β "Revenue (Fisch)".
# ASCII parentheses and no dash of any kind: a measure label reaches a screen twice
# over (the column heading, and `routes_admin::_module_fields`' "Metric - <label>"
# row in the permission editor), so CLAUDE.md rule 2 binds here.
"label": (f"{m.get('label') or key} ({_chan_label})" if _chan
else (m.get("label") or key)),
# `all` for the unsuffixed binding, so the whole offer answers "which channel?" and a
# caller never has to infer it from the absence of a suffix (C6's vocabulary).
"channel": _chan or "all",
"type": ftype,
"format": m.get("format"),
"empty": empty,
# The denominator whose zero blanks a ratio. None for an additive metric.
# β NEVER SUFFIXED. This names a column of the GROUPED ROW, and those rows come back
# keyed by BASE metric β suffix it and `row.get(guard)` is None for every group, so
# every ratio (margin_pct, asp) silently drops out of all three channels at once.
"guard": m.get("denominator") if m.get("agg") == "ratio" else None,
# ββ TWO NORMALIZERS OF ONE WORD, and this is the seam between them
# ([[one-question-two-normalizers]]). A `pct` METRIC is a FRACTION internally
# (`margin_pct` = 0.5836); a `pct` GRID COLUMN is PERCENTAGE POINTS β the client
# renders it `num(v).toFixed(1) + "%"` (`customer-grid/cells.ts`), and the pool's own
# `yoy_pct` has always been on that scale (`core/periods.yoy_pct` multiplies by 100).
# Ship the fraction and a 58.4% margin prints as "0.6%": plausible, wrong, and nothing
# errors. `measure_filter.resolve_values` states the same rule for the customer path
# ("`input_scale` applied OUTWARD⦠the CELL shows points too") and this is that rule,
# derived from the metric's own `format` rather than a second hand-written list.
# β APPLIED ONCE, in `entity_measure_values`, so cells AND conditions are both in
# display units and the filter needs no inward scaling of the typed value.
"scale": 100.0 if m.get("format") == "pct" else 1.0,
# Cents for money (D-153: whole-dollar money cells are a shipped defect), one decimal
# for a percent β the convention `resolve_values` documents, minus its money rounding.
"round": 1 if m.get("format") == "pct" else (2 if ftype == "currency" else None),
"description": m.get("description") or "",
"topic": b["source"],
"dim": b["dim"],
# The resolver carries this alongside source/dim so a second binding to the same
# fact topic cannot accidentally borrow the source topic's default scope.
"scope_variant": b.get("scope_variant"),
})
return out, refused
def entity_measure_values(topic, keys, date_from=None, date_to=None, team_id=None,
exclude_services=False, offer=None, related=None, *, group_keys=None,
cur=None):
"""`{group key: {measure key: value}}` for an entity topic's lookback columns.
ONE grouped query for all `keys` together β they share a topic, a scope and a window, so
asking them separately would be N scans of the same rows (measured: the six ship-first
product metrics cost 0.22 s together against the mirror).
β THE GROUP KEY IS THE SOURCE DIM'S VALUE, NOT A pid. This layer does not know how a grid
hashes its identity (`product_data.sku_pid` is a CRC32; an agent row's id is the partner id),
so the CALLER maps. Keeping the mapping at the caller is what lets one engine serve both.
ββ `exclude_services` DEFAULTS TO **FALSE** HERE, AND THAT IS THE OPPOSITE OF A RANKING
QUERY β measured, after the agent reconciliation went red on it. The service filter exists so
Delivery Charges do not top a "best SKUs" list; on an ENTITY LOOKBACK COLUMN the row IS the
entity, and dropping part of its activity makes the cell a different number from the one every
other surface shows for the same subject. Concretely: with services excluded, per-agent revenue
came in **2.87% under** an independent Odoo order-header aggregate and FOUR of nine agents
missed their own figure by 4-11%; with them included it ties. A service product on the product
grid has the same problem in reverse β its own row would read $0 while it genuinely sold.
β So a caller that wants a RANKING must pass `exclude_services=True` deliberately. The default
is the one that makes a per-row cell true.
β THE EMPTY-WINDOW RULE IS APPLIED HERE, not left to a renderer, because it is the difference
between a true and a false cell (contract C1):
* a group that is ABSENT gets nothing back β the caller fills `zero`-family keys with 0 and
leaves `blank`-family keys out, which is what `_entity_zero_fill` does;
* a group that is PRESENT but whose ratio denominator is 0 has its ratio DROPPED here.
`_post_compute` answers 0.0 for `num/0` and on a grid that prints "0.0%" β a margin
nobody measured, under a row the user has no reason to doubt.
"""
if group_keys is not None:
group_keys = tuple(group_keys)
if not group_keys:
return {}
offer = offer if offer is not None else entity_measures(topic)
by_key = {m["key"]: m for m in offer}
want = [k for k in keys if k in by_key]
if not want:
return {}
# β ONE QUERY PER SOURCE TOPIC *and binding scope* (W37-T13/W39-T11). Keys from `sales_lines` and keys from `stock_moves`
# are different tables joined by different dims, so they cannot ride one scan β but every key
# WITHIN a source still does, which is the whole reason the six product metrics cost one query.
groups = {}
for k in want:
groups.setdefault((by_key[k]["topic"], by_key[k]["dim"],
by_key[k].get("scope_variant")), []).append(k)
if len(groups) > 1:
from core import relations
merged = relations.MeasureAnswer() if related is not None else {}
for (_src, _dim, _variant), ks in groups.items():
answer = entity_measure_values(
topic, ks, date_from=date_from, date_to=date_to, team_id=team_id,
exclude_services=exclude_services, offer=offer, related=related,
group_keys=group_keys, cur=cur)
if related is not None:
merged.refusals.extend(getattr(answer, 'refusals', ()))
for gk, cell in answer.items():
merged.setdefault(gk, {}).update(cell)
return merged
(src, dim, scope_variant), want = next(iter(groups.items()))
# ββ THE SUFFIXED KEY STOPS HERE (W40-T10). `store_query` resolves its `measures` against
# `_model()["metrics"]` via `_expand_measures`, and no `revenue_fisch` exists there β asking
# for one raises "unknown metric". So the offered key is translated back to its base on the
# way IN and the value is re-keyed on the way OUT. Two channels of one measure never collide,
# because they are separate `groups` entries (the scope variant is part of the group key) and
# each is renamed from the same base column into its own offered key.
# β `.get("base_key") or k` β NOT a subscript. `verify_scopes` monkeypatches `entity_measures`
# with a hand-built offer and `verify_grid_events` captures one into a fixture; neither
# carries the member, and a hard subscript would CRASH those gates rather than red them.
base_of = {k: (by_key[k].get("base_key") or k) for k in want}
res = store_query(src, [base_of[k] for k in want], group_by=[dim], date_from=date_from,
date_to=date_to, team_id=team_id, exclude_services=exclude_services,
limit=MAX_GROUPS, scope_variant=scope_variant, related=related,
**({'group_keys': group_keys} if group_keys is not None else {}),
**({'cur': cur} if cur is not None else {}))
if res.get("refusals"):
from core import relations
return relations.MeasureAnswer(refusals=[dict(marker, measure=k)
for marker in res['refusals'] for k in want])
# β A TRUNCATED GROUP SET IS A WRONG ANSWER PER ROW, not a short list β the same refusal
# `rollup_sql.group_values` makes, for the same reason. Never write cells from one.
if res.get("truncated"):
raise _EntityMeasureError(
f"{topic}: {src} grouped by {dim} exceeded {MAX_GROUPS} groups, so refusing to write "
f"cells from a truncated result")
# `store_query` emits the bare dim name when the dim has no `name_col`, `<dim>_id` when it
# does. Both shapes are live in the model, so read whichever arrived.
d = (_model()["topics"][src]["store"]["dims"] or {})[dim]
kcol = f"{dim}_id" if d.get("name_col") or d.get('name_param') else dim
out = {}
for row in res["rows"]:
gk = row.get(kcol)
if gk is None:
continue # an unattributed group keys nothing; never key on None
cell = {}
for k in want:
m = by_key[k]
# `guard` is a BASE metric key and the row is keyed by base metrics, so this needs no
# translation β see the `guard` comment in `_one_binding_offer` for why suffixing it
# would blank every ratio on every channel.
if m["guard"] is not None and not row.get(m["guard"]):
continue # ratio over a zero denominator -> blank, never 0.0
v = row.get(base_of[k]) # base column in, offered key out
if v is None:
continue
# β THE SCALE BOUNDARY, crossed exactly once β see `scale` in `_entity_offer`.
v = v * m["scale"] if m["scale"] != 1.0 else v
if m["round"] is not None and isinstance(v, float):
v = round(v, m["round"])
cell[k] = v
out[gk] = cell
return out
def entity_zero_fill(cell, keys, offer):
"""Complete one row's cells under C1's empty-window rule -> the dict to hand `derived`.
Additive keys land as a real `0` (it sold nothing, and that is a measurement); ratio keys stay
ABSENT so the grid paints an empty cell. Called for EVERY row, including the ones with no
group at all β which is 72% of the product catalogue in a 90-day window, and the reason this
rule is a contract rather than a default.
"""
by_key = {m["key"]: m for m in offer}
out = dict(cell or {})
for k in keys:
m = by_key.get(k)
if not m or k in out:
continue
if m["empty"] == "zero":
out[k] = 0
return out
def entity_measure_leaves(nodes, admitted):
"""Every MEASURE leaf in a filter tree whose `colId` is in `admitted` β the entity twin of
`measure_filter.collect`, which is bound to the CUSTOMER vocabulary by its own `ADMITTED`."""
found = []
for n in nodes or []:
if not isinstance(n, dict):
continue
if isinstance(n.get("children"), list):
found.extend(entity_measure_leaves(n["children"], admitted))
elif n.get("colId") in admitted:
found.append(n)
return found
def entity_measure_sets(topic, rules, today, team_id=None, keys_by_id=None, offer=None,
exclude_services=False, *, cur=None):
"""`{rule id: {group key, β¦}}` β a measure CONDITION answered as a set, per entity row.
ββ WHY THIS EXISTS AT ALL, because it is not obvious from T10's ticket: serving a non-empty
`measures` list does not only offer the Metric COLUMN, it offers the measure CONDITION too β
the client feeds the same array to its filter builder. A condition whose id never comes back
in `measureSets` is rendered PENDING ("Calculatingβ¦") and matches NOTHING, forever. So the
offer and this resolver are one feature; shipping the first alone converts a working filter
panel into a permanent spinner.
β THE POPULATION IS THE WHOLE POOL FOR AN ADDITIVE MEASURE, zero-filled β which is the point.
71.5% of the product catalogue has no group in a 90-day window, so `Revenue < 100` is a
question ABOUT those rows. Dropping them (the shape a naive GROUP BY gives you) would return
exactly the opposite set from the one the user asked for.
β A `blank`-family measure (a ratio) is NOT zero-filled and a row without one is simply not
comparable β "GM % below 40%" must not match a SKU that sold nothing, because it has no
margin percentage at all.
β A rule that is INCOMPLETE or unanswerable is left OUT of the answer rather than resolved to
something β `measure_filter.rule_complete`'s reasoning, unchanged: `to_num(None)` is 0, so a
half-typed `Revenue > β¦` would silently become `Revenue > 0` under a confident count.
"""
from harness import measure_filter as _mf # pure helpers only: percentile / _CMP / STATS
from harness import windows as _wn
offer = offer if offer is not None else entity_measures(topic)
by_key = {m["key"]: m for m in offer}
if not by_key:
return {}
cache = {}
def values_for(mkey, window):
"""`{group key: value}` for one (measure, window), memoised within this call."""
w = _wn.normalize(window)
# The same resolver serves both Metric CELLS and Metric FILTERS. Keep the binding
# scope in this cache identity so a future duplicate key cannot make conditions answer
# against a differently scoped value than the cells render.
sig = (mkey, by_key[mkey].get("scope_variant"),
None if w is None else tuple(sorted(w.items())))
if sig not in cache:
rng = _wn.resolve(window, today)
if rng is None:
cache[sig] = None # never widen to all time β see resolve()
else:
vals = entity_measure_values(topic, [mkey], date_from=rng[0], date_to=rng[1],
team_id=team_id, exclude_services=exclude_services,
offer=offer, group_keys=keys_by_id,
**({'cur': cur} if cur is not None else {}))
cache[sig] = {gk: c[mkey] for gk, c in vals.items() if mkey in c}
return cache[sig]
out = {}
for rule in rules or []:
rid = str(rule.get("id") or "")
if not rid or not _mf.rule_complete(rule):
continue
mkey = rule.get("colId")
m = by_key.get(mkey)
op = rule.get("op")
if not m or op not in _mf._CMP:
continue
left = values_for(mkey, rule.get("window"))
if left is None:
continue
pool = list(left if keys_by_id is None else keys_by_id)
zero = m["empty"] == "zero"
rhs = rule.get("rhs")
try:
if isinstance(rhs, dict) and rhs.get("kind") == "measure":
rk = rhs.get("colId")
if rk not in by_key:
continue
right = values_for(rk, rhs.get("window"))
if right is None:
continue
rzero = by_key[rk]["empty"] == "zero"
hit = set()
for gk in pool:
a, b = left.get(gk), right.get(gk)
if a is None:
if not zero:
continue # a ratio nobody has is not comparable
a = 0
if b is None:
if not rzero:
continue
b = 0
if _mf._CMP[op](a, b):
hit.add(gk)
elif isinstance(rhs, dict) and rhs.get("kind") == "stat":
stat = rhs.get("stat")
if stat not in _mf.STATS or op not in _mf.PAIR_OPS:
continue
# β THE POPULATION IS THE ROWS THAT HAVE THIS MEASURE AT ALL β the owner's ruling
# of 2026-07-27, carried over verbatim in effect: with 71.5% of this catalogue at
# zero, including them would drag the 25th percentile to exactly 0.0 and make
# "below the bottom quartile" match nobody.
population = {gk: v for gk, v in left.items()
if (keys_by_id is None or gk in set(pool)) and v}
cut = _mf.percentile(list(population.values()), stat)
if cut is None:
hit = set()
else:
hit = {gk for gk, v in population.items() if _mf._CMP[op](v, cut)}
else:
target = float(str(rule.get("value")).strip())
hit = set()
for gk in pool:
v = left.get(gk)
if v is None:
if not zero:
continue
v = 0
if _mf._CMP[op](v, target):
hit.add(gk)
except (TypeError, ValueError):
continue # a non-numeric value is not a question
out[rid] = hit
return out
def store_parity(date_from=None, date_to=None, *, runtime=None, user=None):
"""THE OM-2 gate: the store path must equal the live path to the cent, per metric per scope."""
import core.periods as P
if not date_from:
date_from, date_to = P.ytd()
out = []
for key in ("revenue", "margin", "units", "orders", "customers", "returns", "invoiced",
"revenue_invoiced", "orders_invoiced", "booked", "shipped", "collected"):
m = _model()["metrics"][key]
# β A COMPANY-LEVEL topic is checked at company scope ONLY. `store_query` RAISES when a
# team is asked of a topic with no team_col, and the live builder refuses too β so
# looping the BUs here would fail the gate for a topic that is correct. Parity for a
# company-level number means company-level parity; pretending otherwise would either
# crash or, worse, compare two numbers that silently ignored the team.
_store = (_model()["topics"].get(m["topic"]) or {}).get("store") or {}
teams = (_tenant().get('params') or {}).get('wholesale_team_ids', [])
scopes = (None, *teams) if _store.get("team_col") else (None,)
for tid in scopes:
if key in _STANDARD_METRICS:
result = validate_metric(key, date_from, date_to, tid, runtime=runtime, user=user)
out.append({**result, 'check': f'store.{key} == live.{key} (team={tid})',
'store': result.get('ours'), 'live': result.get('independent')})
continue
live = metric(key, date_from, date_to, tid)
res = store_query(m["topic"], [key], date_from=date_from, date_to=date_to, team_id=tid)
ours = res["rows"][0][key] if res["rows"] else 0
gap = abs((ours or 0) - (live or 0))
out.append({"check": f"store.{key} == live.{key} (team={tid})",
"ok": gap <= 0.01, "gap": round(gap, 4), "store": ours, "live": live})
# The agent dim (2026-07-17): store agent-filtered revenue must equal live revenue over the
# agent's FIRST-agent book β a fully independent path (live partner read + line-domain sum)
# against the store's res_partner.agent_id attribution. Largest book = the sharpest check.
recs = O.search_read("res.partner", [("active", "in", [True, False]),
("agent_ids", "!=", False)], ["agent_ids"], limit=100000)
books = {}
for r in recs:
a = (r.get("agent_ids") or [None])[0]
if a:
books.setdefault(a, []).append(r["id"])
if books:
top = max(books, key=lambda k: len(books[k]))
live = O.sum_field("sale.order.line",
O.sale_line_domain(date_from, date_to, partner_ids=books[top]),
"price_subtotal")
res = store_query("sales_lines", ["revenue"], date_from=date_from, date_to=date_to,
filters={"agent": [top]})
ours = res["rows"][0]["revenue"] if res["rows"] else 0
gap = abs((ours or 0) - (live or 0))
out.append({"check": f"store.revenue[agent={top}] == live book sum (first-agent, "
f"{len(books[top])} customers)",
"ok": gap <= 0.01, "gap": round(gap, 4), "store": ours, "live": live})
return out
"""harness/semantic.py β the semantic seam (OM-0a, 2026-07-11).
The ONE governed registry of topics + metrics (platform/model/*.yml) and the resolver that
turns a metric key into a number through the SAME proven data layer the validated modules use
(core/odoo domain builders + aggregate helpers) β so scope parity is by construction, and every
metric carries its definition, ai_context, and an independent validate contract.
Consumers (the point of the seam): the app's pages, the metric dictionary (OM-0c), the saved-
dashboard viewer (OM-3; the Explore page was retired 2026-07-23), the AI Analyst's tools (OM-4:
list_topics / describe_topic / run_semantic_query), and the MCP server (OM-6). Plan:
AutoWiki/research/omni-adoption.md (Part IV + the productization addendum).
Design constraints honored (from /odoo-api):
- read_group dot-path GROUPBY fails β scalar aggregates here use sum_field/distinct_count;
grouped queries (OM-3+) must group by direct m2o fields only.
- Scope is never hand-rolled: topics BIND to a named domain builder (sale_line_domain, ...);
the YAML `scope:` block is the declarative statement of what that builder bakes in.
- READ-ONLY on Odoo, always.
"""
import ast
import copy
from contextlib import contextmanager, nullcontext
from contextvars import ContextVar
import datetime as dt
import functools
import operator
import re
from pathlib import Path
import yaml
import core.odoo as O
from core import live_limiter
MODEL_DIR = Path(__file__).resolve().parents[1] / "model"
_STANDARD_METRICS = frozenset({'booked', 'shipped', 'invoiced', 'collected', 'margin',
'invoiced_sales', 'sales'})
_ROUTE_STOP_WORDS = frozenset(
"the a an of for in on at to from by with and or what which who when how much many "
"was were is are do did show me my our us last this that total sum count list give tell".split()
)
def _route_tokens(text):
words = re.findall(r"[^\W_]+", str(text or "").casefold())
return tuple(word[:-1] if len(word) > 2 and word.endswith("s") else word
for word in words if len(word) >= 2 and word not in _ROUTE_STOP_WORDS)
def route_question(question, candidates):
"""Rank semantic vocabulary using strings alone, without loading the model or any data.
ββ A SUBJECT WORD OUTWEIGHS A FIELD WORD, AND THE REASON WAS MEASURED, NOT ASSUMED.
Without it, "How many orders did we confirm in January?" routed to the CUSTOMERS registry:
`odoo_customers` approves a field labelled "Last order", "last" is a stop word, so that term
normalises to the single token "order" and ties exactly with `odoo_orders`' own subject. The
tie then fell to the alphabetical database key, and `customer_data` sorts before
`ut_odoo_orders`. A question about orders reached the customer book because of a sort order.
A candidate's `subject` is what the database IS (its label, its topic's label); a term drawn
entirely from it scores one more than the same term drawn from a field. `subject` is OPTIONAL:
a candidate that declares none scores exactly as it did before, which is what keeps every
hand-built routing fixture meaning what it says.
"""
words = _route_tokens(question)
scored = []
for candidate in candidates:
subject = set()
for term in candidate.get("subject") or ():
subject.update(_route_tokens(term))
terms = {}
for term in candidate.get("terms") or ():
tokens = _route_tokens(term)
if tokens and any(words[start:start + len(tokens)] == tokens
for start in range(len(words) - len(tokens) + 1)):
# Normalised duplicates count once, including singular/plural spellings.
label = str(term).strip().casefold()
terms[tokens] = min(terms.get(tokens, label), label)
matched = sorted(terms, key=lambda term: (-len(" ".join(term)), term))
score = sum((2 if len(term) > 1 else 1) + (1 if subject.issuperset(term) else 0)
for term in matched)
if score:
scored.append({"database": candidate["database"],
"label": candidate.get("label") or candidate["database"],
"score": score, "matched": [terms[term] for term in matched],
"longest": len(" ".join(matched[0]))})
scored.sort(key=lambda row: (-row["score"], -row["longest"], row["database"]))
best = scored[0] if scored else {}
return {"database": best.get("database", ""), "score": best.get("score", 0),
"matched": best.get("matched", []),
"ranked": [{key: row[key] for key in ("database", "label", "score")}
for row in scored]}
# Topic scope binds to a NAMED, reviewed domain builder β never a YAML-assembled domain (one
# source of truth for scope until OM-2 makes topics executable against the store).
def _order_domain(*a, **kw):
# lazy: order scope currently lives in modules/sales.py; OM-2 lifts it into core/the store
import modules.sales as S
return S.order_domain(*a, **kw)