""" sync.py — outbox protocol server side (docs/11 §7, ACC-04/07/08/10, REC-04, CAP-17). POST /api/sync/ apply_ops(device_id, ops) → {acked, rejected, server_time} GET /api/sync/pull pull(device_id, since) → changed rows for every device in the same account Protocol rules (revised after review 2026-09-22 #1–#9, #17, #18, #21) - op_id is the idempotency key (sync_ops, INSERT OR IGNORE). A replayed op is acked without re-applying. - Every op runs in its own SAVEPOINT: a bad op is *rejected* with a reason and never poisons the batch. Client MUST drop rejected ops from its queue (they will never succeed) and log telemetry. - payload is a FULL ROW SNAPSHOT of the entity as the client knows it. Last-write-wins per entity on client_ts (ACC-10). client_ts must be ISO-8601; it is normalised to UTC-naive before comparison; unparsable → rejected. hide also carries client_ts so a stale upsert cannot resurrect a hidden row. - Ownership: a row may be written by the device that created it or by any device bound to the same account. Rows with NULL device_id (legacy Web/LINE) are never writable through sync. A message / image / feedback whose case_id belongs to someone else is rejected. - hide (REC-04 / CAP-17) sets hidden=1, never deletes. Hiding an existing case cascades to its images and messages. Hiding a missing row is acked but does nothing. - Cursor: every App-visible row gets a monotonically increasing `seq` (single-writer SQLite ⇒ seq order == commit order). pull returns rows with seq > since, ordered by seq, ≤ PULL_LIMIT per entity, and a `cursor` (max seq returned). Ties/NULL timestamps can no longer drop rows. - device_id on rows always comes from the authenticated header, never from payload. """ from __future__ import annotations import json import logging from datetime import datetime, timezone from typing import Any from sqlalchemy import insert, select, update from sqlalchemy.exc import OperationalError from app import db from app.db import cases, devices, engine, feedback, images, messages, next_seq, now_iso, sync_ops log = logging.getLogger("sync") MAX_OPS = 100 PULL_LIMIT = 500 _ENTITIES: dict[str, tuple[Any, str]] = { # entity → (table, pk column name) "case": (cases, "case_id"), "image": (images, "img_id"), "message": (messages, "msg_id"), "feedback": (feedback, "fb_id"), # App: fb_id == msg_id (one vote per message) } _PROTECTED = {"device_id", "server_received_at", "ts", "session_id", "conv_id", "origin", "hidden", "legacy", "seq", "client_ts", "case_id_owner"} _JSON_FIELDS = {"questionnaire_answers", "rapid_test", "quality_flags"} _MAX_TEXT = 20_000 class OpError(ValueError): """Rejected op; str(self) is the reason sent to the client.""" def normalise_ts(value: Any) -> str: """ISO-8601 (any offset, 'Z', or naive=UTC) → UTC-naive isoformat string. Raises OpError.""" if value is None or value == "": raise OpError("client_ts required") if not isinstance(value, str): raise OpError("client_ts must be ISO-8601 string") try: dt = datetime.fromisoformat(value.replace("Z", "+00:00")) except ValueError as e: raise OpError("client_ts unparsable") from e if dt.tzinfo is not None: dt = dt.astimezone(timezone.utc).replace(tzinfo=None) return dt.isoformat() def _same_owner(conn, row_device_id: str | None, device_id: str) -> bool: if row_device_id is None: return False # legacy anonymous rows are never writable (#6) if row_device_id == device_id: return True a = conn.scalar(select(devices.c.account_id).where(devices.c.device_id == device_id)) b = conn.scalar(select(devices.c.account_id).where(devices.c.device_id == row_device_id)) return a is not None and a == b def _coerce(col, v): """Coerce payload value to the column's python type; None passes through. Raises OpError.""" if v is None: return None pytype = col.type.python_type try: if pytype is bool: return int(bool(v)) if pytype is int: if isinstance(v, bool): return int(v) if isinstance(v, float) and not v.is_integer(): raise OpError(f"{col.name} must be int") return int(v) if pytype is float: return float(v) if pytype is str: if isinstance(v, (dict, list)): raise OpError(f"{col.name} must be string") s = str(v) if len(s) > _MAX_TEXT: raise OpError(f"{col.name} too long") return s except OpError: raise except (TypeError, ValueError) as e: raise OpError(f"{col.name} bad type") from e return v def _clean_payload(table, payload: Any) -> dict: if payload is None: payload = {} if not isinstance(payload, dict): raise OpError("payload must be object") out: dict[str, Any] = {} for k, v in payload.items(): if k in _PROTECTED or k not in table.c: continue if k in _JSON_FIELDS and not isinstance(v, str) and v is not None: v = json.dumps(v, ensure_ascii=False) out[k] = _coerce(table.c[k], v) return out def _check_case_owner(conn, case_id: str | None, device_id: str) -> None: """Rows attached to a case must belong to the same owner as the case (if the case is known) (#9).""" if not case_id: return row = conn.execute(select(cases.c.device_id).where(cases.c.case_id == case_id)).first() if row is None: return # case not synced yet → allowed (arrives later) if not _same_owner(conn, row[0], device_id): # NULL device_id (legacy) also lands here (#B4) raise OpError("forbidden: case belongs to another account") def _validate_row(entity: str, row: dict) -> None: if entity == "message": if row.get("role") not in ("user", "assistant", "system", "image") or row.get("content") is None: raise OpError("message needs role and content") if row.get("route") not in (None, "API", "LOCAL"): raise OpError("route must be API|LOCAL") elif entity == "feedback": if row.get("rating") not in ("up", "down"): raise OpError("feedback needs value 1|-1") elif entity == "case": if row.get("status") not in (None, "open", "archived"): raise OpError("status must be open|archived") if row.get("verdict_class") not in (None, "bacteria", "virus"): raise OpError("verdict_class must be bacteria|virus") def _prepare_insert(entity: str, device_id: str, entity_id: str, vals: dict, client_ts: str) -> dict: """Fill legacy columns so Admin views keep working; validate required fields (#4, #21).""" if entity == "message": vals.update(session_id=device_id, conv_id=vals.get("case_id") or "", ts=client_ts, origin="app") vals.setdefault("route", "API") elif entity == "image": vals.update(session_id=device_id, conv_id=vals.get("case_id") or "", ts=client_ts) elif entity == "feedback": vals.setdefault("msg_id", entity_id) vals.update(session_id=device_id, conv_id=vals.get("case_id") or "", ts=client_ts) _validate_row(entity, vals) if entity == "case": vals.setdefault("status", "open") vals.setdefault("created_at", client_ts) vals.setdefault("last_activity_at", client_ts) return vals def _feedback_value(payload: dict) -> None: """App sends value ∈ {1,-1} (docs/11 §4); map onto legacy rating up/down in place.""" if "value" in payload and "rating" not in payload: v = payload.get("value") if v in (1, "1", True): payload["rating"] = "up" elif v in (-1, "-1"): payload["rating"] = "down" else: raise OpError("feedback value must be 1 or -1") def _apply_one(conn, device_id: str, op: dict) -> None: entity = op.get("entity"); kind = op.get("op") entity_id = op.get("entity_id") if entity not in _ENTITIES or kind not in ("upsert", "hide"): raise OpError("malformed") if not isinstance(entity_id, str) or not (1 <= len(entity_id) <= 128): raise OpError("malformed entity_id") table, pk = _ENTITIES[entity] client_ts = normalise_ts(op.get("client_ts")) existing = conn.execute(select(table).where(table.c[pk] == entity_id)).first() if existing is not None and not _same_owner(conn, existing._mapping.get("device_id"), device_id): raise OpError("forbidden") now = now_iso() if kind == "hide": if existing is None: return # nothing to hide, still consumed (#5) prev_ts = existing._mapping.get("client_ts") if prev_ts and client_ts < prev_ts: return # stale hide loses to a newer write hide_vals = dict(client_ts=client_ts, server_received_at=now, seq=next_seq(conn)) if "hidden" in table.c: hide_vals["hidden"] = 1 conn.execute(update(table).where(table.c[pk] == entity_id).values(**hide_vals)) if entity == "case": for child in (images, messages): for (cid,) in conn.execute(select(child.c[child.c.keys()[0]]).where(child.c.case_id == entity_id)): conn.execute(update(child).where(child.c[child.c.keys()[0]] == cid) .values(hidden=1, server_received_at=now, seq=next_seq(conn))) return payload = op.get("payload") if entity == "feedback" and isinstance(payload, dict): payload = dict(payload); _feedback_value(payload) vals = _clean_payload(table, payload) if entity != "case": _check_case_owner(conn, vals.get("case_id"), device_id) vals["client_ts"] = client_ts vals["server_received_at"] = now vals["seq"] = next_seq(conn) if existing is None: vals[pk] = entity_id vals["device_id"] = device_id if "hidden" in table.c: vals["hidden"] = 0 vals = _prepare_insert(entity, device_id, entity_id, vals, client_ts) conn.execute(insert(table).values(**vals)) else: prev_ts = existing._mapping.get("client_ts") if prev_ts and client_ts < prev_ts: return # LWW: older snapshot loses, op consumed vals.pop(pk, None) if entity == "image" and existing._mapping.get("hidden"): vals["hidden"] = 0 # a newer upsert re-adds a removed photo (docs/11 §7; review #8) cur = existing._mapping if all(cur.get(k) == v for k, v in vals.items() if k not in ("seq", "server_received_at", "client_ts")): # an identical re-send (typically the outbox copy of a user turn /api/chat/ already stored): keep the row's # seq — a bumped seq hid the stored answer from the msg_id replay and the LLM ran twice (review 68b26f2 M-2). # client_ts still advances so last-writer-wins stays right for a later, older op (re-review nit 3). conn.execute(update(table).where(table.c[pk] == entity_id).values(client_ts=client_ts)) return merged = {**dict(existing._mapping), **vals} _validate_row(entity, merged) # same rules as insert (#B3) conn.execute(update(table).where(table.c[pk] == entity_id).values(**vals)) def apply_ops(device_id: str, ops: list) -> dict: acked: list[str] = [] rejected: list[dict] = [] with engine().begin() as conn: for op in ops: if not isinstance(op, dict): rejected.append({"op_id": None, "reason": "malformed"}); continue op_id = op.get("op_id") if not isinstance(op_id, str) or not (1 <= len(op_id) <= 128): rejected.append({"op_id": op_id, "reason": "malformed op_id"}); continue if conn.scalar(select(sync_ops.c.op_id).where(sync_ops.c.op_id == op_id)): acked.append(op_id); continue # replay → idempotent ack sp = conn.begin_nested() try: _apply_one(conn, device_id, op) conn.execute(insert(sync_ops).values(op_id=op_id, device_id=device_id, entity=str(op.get("entity")), entity_id=str(op.get("entity_id")), op=str(op.get("op")), received_at=now_iso()).prefix_with("OR IGNORE")) sp.commit() acked.append(op_id) except OpError as e: sp.rollback() rejected.append({"op_id": op_id, "reason": str(e)}) except OperationalError: raise # DB busy/locked → whole request 503, retry (B1) except Exception as e: # noqa: BLE001 — unexpected: isolate, reject, keep the batch alive (#1) sp.rollback() log.warning("sync op %s failed: %s", op_id, e) rejected.append({"op_id": op_id, "reason": "invalid"}) return {"acked": acked, "rejected": rejected, "server_time": now_iso()} def _account_devices(conn, device_id: str) -> list[str]: acc = conn.scalar(select(devices.c.account_id).where(devices.c.device_id == device_id)) if acc is None: return [device_id] return [r[0] for r in conn.execute(select(devices.c.device_id).where(devices.c.account_id == acc))] def pull(device_id: str, since: int | None) -> dict: since = int(since or 0) out: dict[str, Any] = {"server_time": now_iso(), "since": since, "has_more": False} maxima_all: list[int] = [] # last seq per entity that returned rows maxima_truncated: list[int] = [] # …restricted to entities that hit the limit with db.read_connection() as conn: ids = _account_devices(conn, device_id) for entity, (table, pk) in _ENTITIES.items(): stmt = (select(table).where(table.c.device_id.in_(ids), table.c.seq > since) .order_by(table.c.seq).limit(PULL_LIMIT + 1)) rows = [dict(r._mapping) for r in conn.execute(stmt)] truncated = len(rows) > PULL_LIMIT rows = rows[:PULL_LIMIT] out[entity + "s"] = rows if rows: maxima_all.append(rows[-1]["seq"]) if truncated: maxima_truncated.append(rows[-1]["seq"]) # Safe cursor: with truncation, the smallest "complete up to here" seq among truncated entities; rows of # other entities above it are simply re-sent next round (client de-duplicates by pk). Never skips a row. out["has_more"] = bool(maxima_truncated) out["cursor"] = min(maxima_truncated) if maxima_truncated else (max(maxima_all) if maxima_all else since) for row in out.get("images", []): row.pop("lat", None); row.pop("lon", None) # CAP-14: GPS never leaves the server return out