loopable / platform /modules /customer_card.py
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
b2aa446 verified
Raw History Blame Contribute Delete
21.5 kB
"""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)