"""Customer Card observations from the tenant mirror and independent receivable checks.""" from __future__ import annotations import datetime as dt import json from decimal import Decimal from core import periods class MirrorUnavailable(RuntimeError): """A required observation is missing from the serving mirror.""" def _cursor(cursor, rt): if cursor is not None: if rt is not None: rt.assert_datastore_matches() return cursor from harness import runtime return runtime.mirror_cursor(rt or runtime.get_runtime("royal-imports")) def _columns(cursor, table, required=()): try: columns = {str(row[1]) for row in cursor.execute(f"PRAGMA table_info('{table}')").fetchall()} except Exception as exc: raise MirrorUnavailable(f"The {table} observation is unavailable.") from exc missing = set(required) - columns if missing: raise MirrorUnavailable(f"The {table} observation lacks {', '.join(sorted(missing))}.") return columns def _rows(cursor, sql, params=()): result = cursor.execute(sql, list(params)) names = [col[0] for col in result.description] return [dict(zip(names, row)) for row in result.fetchall()] def _money(value): if value is None: raise MirrorUnavailable("A required amount has not been observed.") return float(Decimal(str(value)).quantize(Decimal("0.01"))) def _date(value): day = periods.business_day(value) return day.isoformat() if day else None def _positive_id(value): if isinstance(value, bool): raise ValueError("Customer identifiers must be positive integers.") parsed = int(value) if parsed <= 0 or str(parsed) != str(value): raise ValueError("Customer identifiers must be positive integers.") return parsed def _customer(cursor, customer_id, team_id=None): columns = _columns(cursor, "res_partner", ("id", "name", "team_id")) agent_column = "agent_ids" if "agent_ids" in columns else "agent_id" if agent_column not in columns: raise MirrorUnavailable("The customer's assigned agent has not been observed.") rows = _rows(cursor, f"SELECT id, name, team_id, {agent_column} AS assigned_agents " "FROM res_partner WHERE id = ?", (customer_id,)) if not rows: raise KeyError(customer_id) row = rows[0] if str(row["name"] or "").strip().upper() == "GIFTWARE DEALS": raise PermissionError("This customer is outside the wholesale customer scope.") if team_id is not None: from core import odoo teams = odoo.active_team_ids() if team_id not in teams: raise ValueError("Select one of the tenant's sales teams.") assigned_team = row["team_id"] if assigned_team in teams: admitted = assigned_team == team_id else: _columns(cursor, "sale_order", ("partner_id", "team_id", "state")) admitted = bool(cursor.execute( "SELECT 1 FROM sale_order WHERE partner_id = ? AND team_id = ? " "AND state IN ('sale', 'done') LIMIT 1", [customer_id, team_id]).fetchone()) if not admitted: raise PermissionError("This customer belongs to another business unit.") return row def _invoice_where(): return ("partner_id = ? AND state = 'posted' " "AND move_type IN ('out_invoice', 'out_refund') " "AND amount_residual_signed <> 0") def _invoice_total(cursor, customer_id): _columns(cursor, "account_move", ("partner_id", "state", "move_type", "amount_residual_signed")) unknown = cursor.execute( "SELECT COUNT(*) FROM account_move WHERE partner_id = ? AND state = 'posted' " "AND move_type IN ('out_invoice', 'out_refund') AND amount_residual_signed IS NULL", [customer_id]).fetchone()[0] if unknown: raise MirrorUnavailable("An invoice's open amount has not been observed.") result = cursor.execute( "SELECT COALESCE(SUM(amount_residual_signed), 0), COUNT(*) " "FROM account_move WHERE " + _invoice_where(), [customer_id]).fetchone() return _money(result[0]), int(result[1]) def _agent_ids(value): if value in (None, False, ""): return [] if isinstance(value, str): try: value = json.loads(value) except ValueError as exc: raise MirrorUnavailable("The assigned agent observation is invalid.") from exc if not isinstance(value, (list, tuple)): value = [value] return list(dict.fromkeys(int(item) for item in value if item)) def card(customer_id, cursor=None, *, rt=None, team_id=None, invoice_limit=100, invoice_offset=0, today=None, authorize=None, include_agents=True, user=None): """Return one customer's paged invoices, full balance, latest product prices and agent contacts. authorize(section, rows) must admit every source row or raise before any card leaves the API. """ customer_id = _positive_id(customer_id) if type(invoice_limit) is not int or invoice_limit < 1: raise ValueError("Invoice page size must be a positive integer.") if type(invoice_offset) is not int or invoice_offset < 0: raise ValueError("Invoice offset must be a nonnegative integer.") cursor = _cursor(cursor, rt) customer = _customer(cursor, customer_id, team_id) if authorize: authorize("customer", [customer]) total, count = _invoice_total(cursor, customer_id) _columns(cursor, "account_move", ("id", "name", "invoice_date", "invoice_date_due", "payment_state")) if authorize: identities = _rows(cursor, "SELECT id FROM account_move WHERE " + _invoice_where(), (customer_id,)) authorize("invoices", identities) invoices = _rows(cursor, "SELECT id, name AS number, invoice_date AS date, invoice_date_due AS dueDate, " "amount_residual_signed AS openAmount, payment_state AS paymentState, move_type AS kind " "FROM account_move WHERE " + _invoice_where() + " ORDER BY invoice_date_due NULLS LAST, invoice_date NULLS LAST, id LIMIT ? OFFSET ?", (customer_id, invoice_limit, invoice_offset)) for row in invoices: row["date"], row["dueDate"] = _date(row["date"]), _date(row["dueDate"]) row["openAmount"] = _money(row["openAmount"]) today = today or periods.today() if isinstance(today, str): today = dt.date.fromisoformat(today) date_from, date_to = periods.ltm(today) _columns(cursor, "sale_order", ("id", "partner_id", "team_id", "date_order", "state")) _columns(cursor, "sale_order_line", ("id", "order_id", "order_partner_id", "product_id", "product_name", "price_unit", "product_uom_name")) _columns(cursor, "product_product", ("id", "type")) from core import odoo lo, hi = periods.business_day_bounds(date_from, date_to) params = [customer_id, customer_id, lo, hi] teams = odoo.active_team_ids() team_where = " AND so.team_id = ?" if team_id is not None else ( " AND so.team_id IN (" + ','.join('?' for _ in teams) + ")" if teams else " AND FALSE") if team_id is not None: team_where = " AND so.team_id = ?" params.append(team_id) else: from core import odoo teams = odoo.active_team_ids() team_where = f" AND so.team_id IN ({', '.join('?' * len(teams))})" if teams else " AND FALSE" params.extend(teams) lines = _rows(cursor, "SELECT sol.id, sol.product_id AS productId, sol.product_name AS name, " "sol.price_unit AS price, sol.product_uom_name AS unit, so.date_order AS lastOrderDate, " "pp.type AS productType " "FROM sale_order_line sol JOIN sale_order so ON so.id = sol.order_id " "LEFT JOIN product_product pp ON pp.id = sol.product_id " "WHERE so.partner_id = ? AND sol.order_partner_id = ? AND so.state IN ('sale', 'done') " "AND sol.product_id IS NOT NULL AND CAST(so.date_order AS TIMESTAMP) >= ? " "AND CAST(so.date_order AS TIMESTAMP) < ?" + team_where + " ORDER BY so.date_order DESC, sol.id DESC", params) if any(line["productType"] in (None, "") for line in lines): raise MirrorUnavailable("A purchased product's type has not been observed.") lines = [line for line in lines if line["productType"] != "service"] if authorize: authorize("products", lines) products, seen = [], set() for line in lines: if line["productId"] in seen: continue seen.add(line["productId"]) if line["price"] is None or line["unit"] in (None, ""): raise MirrorUnavailable("The customer's latest price or unit has not been observed.") products.append({"productId": int(line["productId"]), "name": line["name"], "lastOrderDate": _date(line["lastOrderDate"]), "price": float(line["price"]), "unit": line["unit"], "saleLineId": int(line["id"])}) agents = [] ids = _agent_ids(customer["assigned_agents"]) if include_agents else [] if ids: _columns(cursor, "res_partner", ("phone", "email")) agents = _rows(cursor, "SELECT id, name, phone, email FROM res_partner " "WHERE id IN (SELECT unnest(?::BIGINT[])) ORDER BY id", (ids,)) if {row["id"] for row in agents} != set(ids): raise MirrorUnavailable("An assigned agent is missing from the mirror.") by_id = {row["id"]: row for row in agents} agents = [by_id[aid] for aid in ids] if authorize: authorize("agents", agents) for agent in agents: agent["phone"] = agent["phone"] or None agent["email"] = agent["email"] or None if rt is not None or user is not None: from core import personal_reads personal_reads.record_rows("customer_data", [{"customer": customer["name"]}], st=rt, user=user) return {"customer": {"id": customer_id, "name": customer["name"]}, "businessUnit": team_id, "state": "ready", "total": total, "invoices": {"rows": invoices, "total": total, "totalCount": count, "offset": invoice_offset, "limit": invoice_limit, "hasMore": invoice_offset + len(invoices) < count}, "products": products, "productWindow": {"from": date_from, "to": date_to}, "agent": agents[0] if agents else None, "agents": agents, "source": {"observation": "tenant_mirror", "proof": "res.partner.credit", "reconciliation": "not_checked"}} def validate(team_id=None, *, customer_id=None, cursor=None, rt=None, source_client=None): """Tie serving balances to independently read live res.partner.credit without using mirror credit.""" cursor = _cursor(cursor, rt) if customer_id is not None: ids = [_positive_id(customer_id)] else: _columns(cursor, "account_move", ("partner_id", "state", "move_type")) ids = [int(row[0]) for row in cursor.execute( "SELECT DISTINCT partner_id FROM account_move WHERE partner_id IS NOT NULL " "AND state = 'posted' AND move_type IN ('out_invoice', 'out_refund') ORDER BY partner_id" ).fetchall()] observed = {} for pid in ids: if team_id is not None: try: _customer(cursor, pid, team_id) except PermissionError: if customer_id is not None: raise continue observed[pid] = _invoice_total(cursor, pid)[0] if not observed: return [{"check": "Customer receivables observed", "a": 0, "b": 1, "gap": -1, "ok": False}] if source_client is None: from core import odoo source_client = odoo.get_odoo() oracle = source_client.search_read("res.partner", [("id", "in", list(observed)), ("active", "in", [True, False])], ["id", "credit"], limit=None) live = {int(row["id"]): _money(row["credit"]) for row in oracle} return [{"check": f"Customer {pid} open total = independent Odoo receivable", "a": amount, "b": live.get(pid), "gap": _money(Decimal(str(amount)) - Decimal(str(live[pid]))) if pid in live else None, "ok": pid in live and Decimal(str(amount)) == Decimal(str(live[pid]))} for pid, amount in observed.items()] def fixture_cursor(): """Build synthetic native DuckDB observations without opening a tenant store or Odoo.""" import duckdb cursor = duckdb.connect(":memory:") cursor.execute("CREATE TABLE res_partner (id BIGINT, name VARCHAR, team_id BIGINT, " "agent_ids VARCHAR, phone VARCHAR, email VARCHAR)") cursor.execute("INSERT INTO res_partner VALUES " "(10, 'Fixture Royal', 6, '[90]', NULL, NULL), " "(11, 'Fixture Fisch', 5, '[]', NULL, NULL), " "(12, 'GIFTWARE DEALS', 6, '[]', NULL, NULL), " "(90, 'Fixture Agent', NULL, '[]', '555-0100', 'agent@example.invalid')") cursor.execute("CREATE TABLE account_move (id BIGINT, partner_id BIGINT, name VARCHAR, " "invoice_date DATE, invoice_date_due DATE, amount_residual_signed DECIMAL(18,2), " "payment_state VARCHAR, move_type VARCHAR, state VARCHAR, team_id BIGINT)") cursor.execute("INSERT INTO account_move SELECT i, 10, 'INV-' || i, DATE '2026-10-01', " "DATE '2026-11-01', 1, 'not_paid', 'out_invoice', 'posted', 1 FROM range(1, 502) r(i)") cursor.execute("INSERT INTO account_move VALUES " "(600,10,'CR-1','2026-10-01','2026-11-01',-20,'partial','out_refund','posted',1), " "(601,11,'OTHER','2026-10-01','2026-11-01',900,'not_paid','out_invoice','posted',1), " "(602,10,'DRAFT','2026-10-01','2026-11-01',800,'not_paid','out_invoice','draft',1), " "(603,10,'PAID','2026-10-01','2026-11-01',0,'paid','out_invoice','posted',1), " "(604,10,'BILL','2026-10-01','2026-11-01',700,'not_paid','in_invoice','posted',1)") cursor.execute("CREATE TABLE sale_order (id BIGINT, partner_id BIGINT, team_id BIGINT, " "date_order TIMESTAMP, state VARCHAR)") cursor.execute("INSERT INTO sale_order VALUES " "(1,10,6,'2026-09-01','sale'), (2,10,6,'2026-10-01','done'), " "(3,11,5,'2026-10-02','sale'), (4,10,6,'2026-10-03','draft'), " "(5,10,6,'2025-01-01','sale'), (6,12,6,'2026-10-04','sale'), " "(7,10,5,'2026-10-05','sale'), (8,10,6,'2026-10-20','sale')") cursor.execute("CREATE TABLE product_product (id BIGINT, type VARCHAR)") cursor.execute("INSERT INTO product_product VALUES " "(20,'product'), (21,'product'), (22,'product'), (23,'product'), (24,'service')") cursor.execute("CREATE TABLE sale_order_line (id BIGINT, order_id BIGINT, order_partner_id BIGINT, " "product_id BIGINT, product_name VARCHAR, price_unit DOUBLE, product_uom_name VARCHAR)") cursor.execute("INSERT INTO sale_order_line VALUES " "(1,1,10,20,'Fixture Product',7.5,'Pack'), (2,2,10,20,'Fixture Product',8.75,'Roll'), " "(3,3,11,20,'Fixture Product',99,'Box'), (4,4,10,20,'Fixture Product',98,'Box'), " "(5,5,10,21,'Old Product',6,'Each'), (6,6,12,20,'Fixture Product',97,'Box'), " "(7,7,10,22,'Fisch Product',5,'Each'), (8,8,10,20,'Fixture Product',96,'Box'), " "(9,2,11,23,'Mismatched Customer',95,'Each'), " "(10,2,10,24,'Fixture Service',4,'Hour')") return cursor def selftest(): """Prove invoice isolation, uncapped paging, latest own prices and an independent oracle offline.""" cursor = fixture_cursor() try: observed = card(10, cursor, team_id=6, today="2026-10-13", invoice_limit=1) assert observed["total"] == 481, "total includes another customer's invoice" assert observed["invoices"]["totalCount"] == 502 assert len(observed["invoices"]["rows"]) == 1 and observed["invoices"]["hasMore"] tail = card(10, cursor, team_id=6, today="2026-10-13", invoice_offset=500) assert len(tail["invoices"]["rows"]) == 2 and tail["total"] == 481 assert any(row["openAmount"] == -20 for row in tail["invoices"]["rows"]) assert all(row["productId"] != 24 for row in observed["products"]), "service entered the customer catalog" assert observed["products"] == [{"productId": 20, "name": "Fixture Product", "lastOrderDate": "2026-10-01", "price": 8.75, "unit": "Roll", "saleLineId": 2}] cursor.execute("UPDATE product_product SET type = 'consu' WHERE id = 20") consumable = card(10, cursor, team_id=6, today="2026-10-13") assert consumable["products"] == observed["products"], "consumable left the customer catalog" assert consumable["total"] == 481 cursor.execute("UPDATE product_product SET type = 'product' WHERE id = 20") assert observed["agent"]["phone"] == "555-0100" assert observed["agent"]["email"] == "agent@example.invalid" for pid, team in ((10, 5), (12, 6)): try: card(pid, cursor, team_id=team, today="2026-10-13") except PermissionError: pass else: raise AssertionError("customer scope widened") assert card(11, cursor, team_id=5, today="2026-10-13")["total"] == 900 assert card(11, cursor, team_id=5, today="2026-10-13")["agent"] is None class Oracle: def search_read(self, model, domain, fields, limit=None): assert model == "res.partner" and fields == ["id", "credit"] and limit is None assert domain[0] == ("id", "in", [10]) return [{"id": 10, "credit": 481}] checks = validate(customer_id=10, cursor=cursor, source_client=Oracle()) assert len(checks) == 1 and checks[0]["ok"] cursor.execute("UPDATE account_move SET amount_residual_signed = 2 WHERE id = 1") assert validate(customer_id=10, cursor=cursor, source_client=Oracle())[0]["ok"] is False cursor.execute("UPDATE account_move SET amount_residual_signed = 1.01 WHERE id = 1") assert validate(customer_id=10, cursor=cursor, source_client=Oracle())[0]["ok"] is False cursor.execute("UPDATE account_move SET amount_residual_signed = NULL WHERE id = 1") try: card(10, cursor, team_id=6, today="2026-10-13") except MirrorUnavailable: pass else: raise AssertionError("an unknown invoice amount became a partial total") cursor.execute("UPDATE account_move SET amount_residual_signed = 1 WHERE id = 1") cursor.execute("INSERT INTO res_partner VALUES " "(91, 'Second Fixture Agent', NULL, '[]', '555-0101', 'second@example.invalid')") cursor.execute("UPDATE res_partner SET agent_ids = '[91,90]' WHERE id = 10") assert card(10, cursor, today="2026-10-13")["agent"]["id"] == 91 cursor.execute("UPDATE account_move SET amount_residual_signed = 0 WHERE partner_id = 11") empty = card(11, cursor, today="2026-10-13") assert empty["total"] == 0 and empty["invoices"]["totalCount"] == 0 for mutation in ("UPDATE product_product SET type = NULL WHERE id = 20", "DELETE FROM product_product WHERE id = 20", "ALTER TABLE product_product DROP COLUMN type", "DROP TABLE product_product"): cursor.execute(mutation) try: card(10, cursor, team_id=6, today="2026-10-13") except MirrorUnavailable: pass else: raise AssertionError("an unknown product type became a partial catalog") print("PASS customer-card offline fixture: isolation, paging, prices, product types, agents, oracle drift") finally: cursor.close() if __name__ == "__main__": import argparse parser = argparse.ArgumentParser(description=__doc__) mode = parser.add_mutually_exclusive_group(required=True) mode.add_argument("--selftest", action="store_true") mode.add_argument("--validate", action="store_true", help="Read live Odoo credit independently.") parser.add_argument("--mirror", help="Scratch native DuckDB observation, opened read-only.") parser.add_argument("--customer-id", type=int) parser.add_argument("--team-id", type=int, help="One of the tenant's sales team ids.") args = parser.parse_args() if args.selftest: selftest() else: if not args.mirror or args.customer_id is None: parser.error("--validate requires --mirror and --customer-id") import duckdb import sys with duckdb.connect(args.mirror, read_only=True) as observation: checks = validate(cursor=observation, customer_id=args.customer_id, team_id=args.team_id) for check in checks: print("ok" if check["ok"] else "FAIL", check["check"], "observation=", check["a"], "oracle=", check["b"], "gap=", check["gap"]) sys.exit(0 if checks and all(check["ok"] for check in checks) else 1)