diff --git a/scripts/health/dashboard_v5/medication_schema.py b/scripts/health/dashboard_v5/medication_schema.py new file mode 100644 index 0000000..7c04ce0 --- /dev/null +++ b/scripts/health/dashboard_v5/medication_schema.py @@ -0,0 +1,501 @@ +"""Additive Sprint 7C-F medication-history schema contract. + +Legacy rows are deliberately left untouched. Structured constraints activate only +for new rows carrying a ``business_revision``. +""" +from __future__ import annotations + +import hashlib +import re +import sqlite3 +import secrets + +MIGRATION_NAME = "sprint7c_f_additive_medication_history" +SCHEMA_VERSION = 7 + +PRESCRIPTION_COLUMNS: tuple[tuple[str, str], ...] = ( + ("prescription_status", "TEXT"), + ("prescription_status_source", "TEXT"), + ("prescription_status_provenance", "TEXT"), + ("business_revision", "TEXT"), +) + +EVENT_COLUMNS: tuple[tuple[str, str], ...] = ( + ("medication_id", "INTEGER REFERENCES medikamente(id)"), + ("planned_event_id", "INTEGER REFERENCES medication_administrations(id)"), + ("planned_dose_value", "TEXT"), + ("planned_dose_unit", "TEXT"), + ("actual_dose_value", "TEXT"), + ("actual_dose_unit", "TEXT"), + ("route_original", "TEXT"), + ("route_normalized", "TEXT"), + ("injection_region", "TEXT"), + ("injection_side", "TEXT"), + ("injection_detail", "TEXT"), + ("lot_number", "TEXT"), + ("corrects_event_id", "INTEGER REFERENCES medication_administrations(id)"), + ("corrected_target_status", "TEXT"), + ("correction_reason", "TEXT"), + ("business_revision", "TEXT"), +) + +ROUTES = ( + "oral", + "subcutaneous", + "intravenous", + "intramuscular", + "topical", + "inhaled", + "other", + "unknown", +) +EVENT_STATUSES = ("planned", "administered", "missed", "corrected") +CORRECTED_TARGET_STATUSES = ("planned", "administered", "missed") +PRESCRIPTION_STATUSES = ("active", "ended", "paused", "unknown") +INJECTION_SIDES = ("left", "right", "unspecified") +MANAGED_INDEX_NAMES = { + "ux_medication_correction_origin", + "ix_medication_event_medication_time", + "ix_medication_event_plan", + "ux_medication_plan_effective_consumption", +} +MANAGED_INDEX_SHA256 = { + "ix_medication_event_medication_time": "62c44bb055ea3eb29a2197b43fb4b3fe1369ef7e31d9bc513f7358ce83ade4c9", + "ix_medication_event_plan": "23834baaf66eea814f4c5666a12679f055ec869ae45be3ff139f5ae2313b6424", + "ux_medication_correction_origin": "b4fcd23f3cac085427e40169044f7b4f1c47fbf73526d9fe8e6b57c79993c7cc", +} +MANAGED_TRIGGER_SHA256 = { + "trg_medication_event_immutable_delete": "ca6ec62519fbde1218cad8e425acf6a07b40205480c9a0abf16d4db92d7c1caa", + "trg_medication_event_immutable_update": "3604f96e3eac08cf1675fa75b105c7fcf126e8530b6b57ee81c11e2ec89269fb", + "trg_medication_event_validate_insert": "822efdfca9ca473cf6a70fae585d1e199717de64b207bc62c2ee97cbe6f24b27", + "trg_medication_plan_correction_consumption_insert": "7e8fbf85f3e419af13df8a94f4c56f940071527d9602aa4a23bf298ba185d4e0", + "trg_medication_plan_effective_consumption_insert": "a1284f1ca648da51ecfd6ecca31f75b5b882c598a156bf6fd11d52e29f3fb0f7", + "trg_medication_prescription_validate_insert": "42c4d290ea34f3bd4542527d39d817cf6d5c0663577d7ede8d5667a6bdd18ef9", + "trg_medication_prescription_validate_update": "34eef990a43aebceae627eb3ba08cf8eeeea6ed87c02112e5d7e489598e9f9fc", +} + + +def _normalized_sql(value: object) -> str: + return re.sub(r"\s+", " ", str(value or "")).strip().casefold() + + +def _columns(connection: sqlite3.Connection, table: str) -> set[str]: + return {str(row[1]) for row in connection.execute(f'PRAGMA table_info("{table}")')} + + +def _require_base(connection: sqlite3.Connection) -> None: + tables = { + str(row[0]) + for row in connection.execute("SELECT name FROM sqlite_master WHERE type='table'") + } + missing = {"medikamente", "medication_administrations"} - tables + if missing: + raise RuntimeError("base medication schema missing") + + +def _add_columns( + connection: sqlite3.Connection, table: str, expected: tuple[tuple[str, str], ...] +) -> list[str]: + actual = _columns(connection, table) + changes: list[str] = [] + for name, kind in expected: + if name in actual: + continue + connection.execute(f'ALTER TABLE "{table}" ADD COLUMN "{name}" {kind}') + changes.append(f"{table}.{name}") + return changes + + +def _has_legacy_event_uniqueness(connection: sqlite3.Connection) -> bool: + for row in connection.execute("PRAGMA index_list('medication_administrations')"): + if not int(row[2]): + continue + columns = [ + str(column[2]) + for column in connection.execute(f'PRAGMA index_info("{row[1]}")') + ] + if columns == ["datum", "medication_name", "event_type"]: + return True + return False + + +def _rebuild_event_table_without_legacy_uniqueness( + connection: sqlite3.Connection, +) -> bool: + """Relax the obsolete tuple uniqueness in the disposable candidate DB.""" + if not _has_legacy_event_uniqueness(connection): + return False + existing = _columns(connection, "medication_administrations") + preserved_objects: dict[str, str] = {} + for object_type, object_name, object_sql in connection.execute( + """SELECT type,name,sql FROM sqlite_master + WHERE tbl_name='medication_administrations' + AND type IN ('index','trigger') AND sql IS NOT NULL""" + ): + object_name = str(object_name) + if object_name.startswith("trg_medication_") or object_name in MANAGED_INDEX_NAMES: + continue + if object_type == "index": + columns = [ + str(column[2]) + for column in connection.execute(f'PRAGMA index_info("{object_name}")') + ] + if columns == ["datum", "medication_name", "event_type"]: + continue + preserved_objects[object_name] = str(object_sql) + connection.execute("PRAGMA defer_foreign_keys=ON") + connection.execute("DROP TABLE IF EXISTS medication_administrations_7cf_new") + connection.execute( + """CREATE TABLE medication_administrations_7cf_new ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + datum TEXT NOT NULL, + medication_name TEXT NOT NULL, + dose TEXT, + route TEXT, + event_type TEXT, + scheduled_next_date TEXT, + notes TEXT, + source TEXT DEFAULT 'manual', + created_at TEXT DEFAULT CURRENT_TIMESTAMP, + occurred_at TEXT, + medication_id INTEGER REFERENCES medikamente(id), + planned_event_id INTEGER REFERENCES medication_administrations_7cf_new(id), + planned_dose_value TEXT, + planned_dose_unit TEXT, + actual_dose_value TEXT, + actual_dose_unit TEXT, + route_original TEXT, + route_normalized TEXT, + injection_region TEXT, + injection_side TEXT, + injection_detail TEXT, + lot_number TEXT, + corrects_event_id INTEGER REFERENCES medication_administrations_7cf_new(id), + corrected_target_status TEXT, + correction_reason TEXT, + business_revision TEXT + )""" + ) + target_columns = [ + "id", "datum", "medication_name", "dose", "route", "event_type", + "scheduled_next_date", "notes", "source", "created_at", "occurred_at", + *(name for name, _ in EVENT_COLUMNS), + ] + copied = [name for name in target_columns if name in existing] + quoted = ",".join(f'"{name}"' for name in copied) + connection.execute( + f"INSERT INTO medication_administrations_7cf_new({quoted}) " + f"SELECT {quoted} FROM medication_administrations ORDER BY id" + ) + connection.execute("DROP TABLE medication_administrations") + connection.execute( + "ALTER TABLE medication_administrations_7cf_new RENAME TO medication_administrations" + ) + for object_sql in preserved_objects.values(): + connection.execute(object_sql) + for object_name, expected_sql in preserved_objects.items(): + row = connection.execute( + "SELECT sql FROM sqlite_master WHERE name=? AND type IN ('index','trigger')", + (object_name,), + ).fetchone() + if row is None or _normalized_sql(row[0]) != _normalized_sql(expected_sql): + raise RuntimeError(f"legacy schema object not preserved: {object_name}") + return True + + +def _execute_sql_script(connection: sqlite3.Connection, script: str) -> None: + statement = "" + for line in script.splitlines(keepends=True): + statement += line + if sqlite3.complete_statement(statement): + if statement.strip(): + connection.execute(statement) + statement = "" + if statement.strip(): + raise RuntimeError("incomplete medication schema statement") + + +def _apply_schema_uncommitted(connection: sqlite3.Connection) -> list[str]: + """Apply the schema within a caller-owned transaction or savepoint.""" + _require_base(connection) + changes: list[str] = [] + if _rebuild_event_table_without_legacy_uniqueness(connection): + changes.append("medication_administrations.relax_legacy_tuple_uniqueness") + changes.extend(_add_columns(connection, "medikamente", PRESCRIPTION_COLUMNS)) + changes.extend(_add_columns(connection, "medication_administrations", EVENT_COLUMNS)) + _execute_sql_script( + connection, + f""" + CREATE TABLE IF NOT EXISTS medication_schema_meta ( + migration_name TEXT PRIMARY KEY, + schema_version INTEGER NOT NULL, + installed_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP + ); + INSERT OR IGNORE INTO medication_schema_meta(migration_name,schema_version) + VALUES('{MIGRATION_NAME}',{SCHEMA_VERSION}); + CREATE TABLE IF NOT EXISTS medication_public_identity_key ( + singleton INTEGER PRIMARY KEY CHECK(singleton=1), + key BLOB NOT NULL CHECK(length(key)=32) + ); + + DROP INDEX IF EXISTS ux_medication_correction_origin; + DROP INDEX IF EXISTS ix_medication_event_medication_time; + DROP INDEX IF EXISTS ix_medication_event_plan; + DROP INDEX IF EXISTS ux_medication_plan_effective_consumption; + CREATE UNIQUE INDEX ux_medication_correction_origin + ON medication_administrations(corrects_event_id) + WHERE corrects_event_id IS NOT NULL; + CREATE INDEX ix_medication_event_medication_time + ON medication_administrations(medication_id,occurred_at,datum,id); + CREATE INDEX ix_medication_event_plan + ON medication_administrations(planned_event_id); + + DROP TRIGGER IF EXISTS trg_medication_prescription_validate_insert; + DROP TRIGGER IF EXISTS trg_medication_prescription_validate_update; + CREATE TRIGGER trg_medication_prescription_validate_insert + BEFORE INSERT ON medikamente + WHEN NEW.business_revision IS NOT NULL + BEGIN + SELECT CASE WHEN NEW.prescription_status NOT IN ('active','ended','paused','unknown') + OR (NEW.prescription_status<>'unknown' AND ( + trim(COALESCE(NEW.prescription_status_source,''))='' + OR trim(COALESCE(NEW.prescription_status_provenance,''))='' + )) + THEN RAISE(ABORT,'invalid prescription status') END; + SELECT CASE WHEN length(NEW.business_revision)<>64 OR NEW.business_revision GLOB '*[^0-9a-f]*' + THEN RAISE(ABORT,'invalid prescription revision') END; + END; + + CREATE TRIGGER trg_medication_prescription_validate_update + BEFORE UPDATE OF prescription_status,prescription_status_source,prescription_status_provenance,business_revision ON medikamente + WHEN NEW.business_revision IS NOT NULL + BEGIN + SELECT CASE WHEN NEW.prescription_status NOT IN ('active','ended','paused','unknown') + OR (NEW.prescription_status<>'unknown' AND ( + trim(COALESCE(NEW.prescription_status_source,''))='' + OR trim(COALESCE(NEW.prescription_status_provenance,''))='' + )) + THEN RAISE(ABORT,'invalid prescription status') END; + SELECT CASE WHEN length(NEW.business_revision)<>64 OR NEW.business_revision GLOB '*[^0-9a-f]*' + THEN RAISE(ABORT,'invalid prescription revision') END; + END; + + DROP TRIGGER IF EXISTS trg_medication_event_validate_insert; + DROP TRIGGER IF EXISTS trg_medication_plan_effective_consumption_insert; + DROP TRIGGER IF EXISTS trg_medication_plan_correction_consumption_insert; + DROP TRIGGER IF EXISTS trg_medication_event_immutable_update; + DROP TRIGGER IF EXISTS trg_medication_event_immutable_delete; + CREATE TRIGGER trg_medication_event_validate_insert + BEFORE INSERT ON medication_administrations + WHEN NEW.business_revision IS NOT NULL + BEGIN + SELECT CASE WHEN NEW.medication_id IS NULL OR + NOT EXISTS(SELECT 1 FROM medikamente WHERE id=NEW.medication_id) + THEN RAISE(ABORT,'invalid medication relation') END; + SELECT CASE WHEN lower(trim(COALESCE(NEW.event_type,''))) NOT IN ('planned','administered','missed','corrected') + THEN RAISE(ABORT,'invalid medication event status') END; + SELECT CASE WHEN length(NEW.business_revision)<>64 OR NEW.business_revision GLOB '*[^0-9a-f]*' + THEN RAISE(ABORT,'invalid medication event revision') END; + SELECT CASE WHEN NEW.route_normalized IS NOT NULL AND NEW.route_normalized NOT IN + ('oral','subcutaneous','intravenous','intramuscular','topical','inhaled','other','unknown') + THEN RAISE(ABORT,'invalid normalized route') END; + SELECT CASE WHEN NEW.injection_side IS NOT NULL AND NEW.injection_side NOT IN ('left','right','unspecified') + THEN RAISE(ABORT,'invalid injection side') END; + SELECT CASE WHEN (NEW.injection_region IS NOT NULL OR NEW.injection_side IS NOT NULL OR NEW.injection_detail IS NOT NULL) + AND COALESCE(NEW.route_normalized,'') NOT IN ('subcutaneous','intravenous','intramuscular','other') + THEN RAISE(ABORT,'injection site requires documented injection route') END; + SELECT CASE WHEN NEW.planned_event_id IS NOT NULL AND NOT EXISTS( + SELECT 1 FROM medication_administrations p + WHERE p.id=NEW.planned_event_id + AND lower(trim(COALESCE(p.event_type,'')))='planned' + AND (p.medication_id=NEW.medication_id OR (p.medication_id IS NULL AND p.medication_name=NEW.medication_name)) + ) THEN RAISE(ABORT,'invalid planned event relation') END; + SELECT CASE WHEN lower(trim(COALESCE(NEW.event_type,'')))='corrected' AND ( + NEW.corrects_event_id IS NULL OR + NEW.corrected_target_status NOT IN ('planned','administered','missed') OR + trim(COALESCE(NEW.correction_reason,''))='' OR + NOT EXISTS(SELECT 1 FROM medication_administrations o + WHERE o.id=NEW.corrects_event_id + AND (o.medication_id=NEW.medication_id OR (o.medication_id IS NULL AND o.medication_name=NEW.medication_name))) + ) THEN RAISE(ABORT,'invalid correction relation') END; + SELECT CASE WHEN lower(trim(COALESCE(NEW.event_type,'')))<>'corrected' AND + (NEW.corrects_event_id IS NOT NULL OR NEW.corrected_target_status IS NOT NULL OR NEW.correction_reason IS NOT NULL) + THEN RAISE(ABORT,'correction fields on non-correction') END; + END; + + CREATE TRIGGER trg_medication_plan_effective_consumption_insert + BEFORE INSERT ON medication_administrations + WHEN NEW.business_revision IS NOT NULL + AND NEW.planned_event_id IS NOT NULL + AND lower(trim(COALESCE(NEW.event_type,''))) IN ('administered','missed') + BEGIN + SELECT CASE WHEN EXISTS( + WITH RECURSIVE effective_chain(root_id,id,event_type,corrected_target_status) AS ( + SELECT id,id,event_type,corrected_target_status + FROM medication_administrations + WHERE (id=NEW.planned_event_id AND lower(trim(COALESCE(event_type,''))) IN ('planned','scheduled','geplant')) + OR (planned_event_id=NEW.planned_event_id + AND business_revision IS NOT NULL + AND lower(trim(COALESCE(event_type,''))) IN ('administered','missed')) + UNION ALL + SELECT prior.root_id,c.id,c.event_type,c.corrected_target_status + FROM medication_administrations c + JOIN effective_chain prior ON c.corrects_event_id=prior.id + WHERE c.business_revision IS NOT NULL + ) + SELECT 1 FROM effective_chain latest + WHERE NOT EXISTS( + SELECT 1 FROM medication_administrations child + WHERE child.corrects_event_id=latest.id AND child.business_revision IS NOT NULL + ) + AND lower(trim(COALESCE( + CASE WHEN lower(trim(COALESCE(latest.event_type,'')))='corrected' + THEN latest.corrected_target_status ELSE latest.event_type END,'' + ))) IN ('administered','missed') + ) THEN RAISE(ABORT,'planned medication event already effectively consumed') END; + END; + + CREATE TRIGGER trg_medication_plan_correction_consumption_insert + BEFORE INSERT ON medication_administrations + WHEN NEW.business_revision IS NOT NULL + AND lower(trim(COALESCE(NEW.event_type,'')))='corrected' + AND lower(trim(COALESCE(NEW.corrected_target_status,''))) IN ('administered','missed') + BEGIN + SELECT CASE WHEN EXISTS( + WITH RECURSIVE target_ancestry(id,corrects_event_id,event_type,planned_event_id) AS ( + SELECT id,corrects_event_id,event_type,planned_event_id + FROM medication_administrations WHERE id=NEW.corrects_event_id + UNION ALL + SELECT parent.id,parent.corrects_event_id,parent.event_type,parent.planned_event_id + FROM medication_administrations parent + JOIN target_ancestry child ON parent.id=child.corrects_event_id + ), + target_plan(target_root_id,plan_id) AS ( + SELECT id, + CASE WHEN lower(trim(COALESCE(event_type,''))) IN ('planned','scheduled','geplant') + THEN id ELSE planned_event_id END + FROM target_ancestry WHERE corrects_event_id IS NULL + ), + effective_chain(root_id,id,event_type,corrected_target_status) AS ( + SELECT candidate.id,candidate.id,candidate.event_type,candidate.corrected_target_status + FROM medication_administrations candidate,target_plan + WHERE target_plan.plan_id IS NOT NULL + AND candidate.id<>target_plan.target_root_id + AND ( + (candidate.id=target_plan.plan_id + AND lower(trim(COALESCE(candidate.event_type,''))) IN ('planned','scheduled','geplant')) + OR + (candidate.planned_event_id=target_plan.plan_id + AND candidate.business_revision IS NOT NULL + AND lower(trim(COALESCE(candidate.event_type,''))) IN ('administered','missed')) + ) + UNION ALL + SELECT prior.root_id,c.id,c.event_type,c.corrected_target_status + FROM medication_administrations c + JOIN effective_chain prior ON c.corrects_event_id=prior.id + WHERE c.business_revision IS NOT NULL + ) + SELECT 1 FROM effective_chain latest + WHERE NOT EXISTS( + SELECT 1 FROM medication_administrations child + WHERE child.corrects_event_id=latest.id AND child.business_revision IS NOT NULL + ) + AND lower(trim(COALESCE( + CASE WHEN lower(trim(COALESCE(latest.event_type,'')))='corrected' + THEN latest.corrected_target_status ELSE latest.event_type END,'' + ))) IN ('administered','missed') + ) THEN RAISE(ABORT,'planned medication event already effectively consumed') END; + END; + + CREATE TRIGGER trg_medication_event_immutable_update + BEFORE UPDATE ON medication_administrations + WHEN OLD.business_revision IS NOT NULL + OR NEW.business_revision IS NOT OLD.business_revision + OR EXISTS(SELECT 1 FROM medication_administrations c WHERE c.corrects_event_id=OLD.id) + BEGIN + SELECT RAISE(ABORT,'structured medication events are immutable'); + END; + + CREATE TRIGGER trg_medication_event_immutable_delete + BEFORE DELETE ON medication_administrations + WHEN OLD.business_revision IS NOT NULL + OR EXISTS(SELECT 1 FROM medication_administrations c WHERE c.corrects_event_id=OLD.id) + BEGIN + SELECT RAISE(ABORT,'structured medication events are immutable'); + END; + """ + ) + if connection.execute( + "SELECT 1 FROM medication_public_identity_key WHERE singleton=1" + ).fetchone() is None: + connection.execute( + "INSERT INTO medication_public_identity_key(singleton,key) VALUES(1,?)", + (secrets.token_bytes(32),), + ) + changes.append("medication_public_identity_key") + return changes + + +def apply_schema(connection: sqlite3.Connection) -> list[str]: + """Apply additive DDL atomically without committing the caller's transaction.""" + connection.execute("SAVEPOINT medication_schema_apply") + try: + changes = _apply_schema_uncommitted(connection) + except BaseException: + connection.execute("ROLLBACK TO medication_schema_apply") + connection.execute("RELEASE medication_schema_apply") + raise + connection.execute("RELEASE medication_schema_apply") + return changes + + +def assert_schema(connection: sqlite3.Connection) -> None: + _require_base(connection) + missing: dict[str, list[str]] = {} + for table, expected in ( + ("medikamente", PRESCRIPTION_COLUMNS), + ("medication_administrations", EVENT_COLUMNS), + ): + actual = _columns(connection, table) + absent = [name for name, _ in expected if name not in actual] + if absent: + missing[table] = absent + marker = connection.execute( + "SELECT schema_version FROM medication_schema_meta WHERE migration_name=?", + (MIGRATION_NAME,), + ).fetchone() + if missing or marker is None or int(marker[0]) != SCHEMA_VERSION: + raise RuntimeError("medication-history schema missing") + key = connection.execute( + "SELECT key FROM medication_public_identity_key WHERE singleton=1" + ).fetchone() + if key is None or not isinstance(key[0], bytes) or len(key[0]) != 32: + raise RuntimeError("medication public identity key missing") + indexes = { + str(row[0]): str(row[1] or "") + for row in connection.execute( + "SELECT name,sql FROM sqlite_master WHERE type='index' AND name IN (?,?,?,?)", + tuple(sorted(MANAGED_INDEX_NAMES)), + ) + } + if "ux_medication_plan_effective_consumption" in indexes: + raise RuntimeError("obsolete static plan-consumption index remains") + for name, expected_digest in MANAGED_INDEX_SHA256.items(): + if name not in indexes: + raise RuntimeError(f"medication-history index contract missing: {name}") + actual_digest = hashlib.sha256(_normalized_sql(indexes[name]).encode("utf-8")).hexdigest() + if actual_digest != expected_digest: + raise RuntimeError(f"medication-history index contract stale: {name}") + triggers = { + str(row[0]): str(row[1] or "") + for row in connection.execute( + "SELECT name,sql FROM sqlite_master WHERE type='trigger' AND name LIKE 'trg_medication_%'" + ) + } + required = set(MANAGED_TRIGGER_SHA256) + if not required <= set(triggers): + raise RuntimeError("medication-history trigger contract missing") + for name, expected_digest in MANAGED_TRIGGER_SHA256.items(): + actual_digest = hashlib.sha256(_normalized_sql(triggers[name]).encode("utf-8")).hexdigest() + if actual_digest != expected_digest: + raise RuntimeError(f"medication-history trigger contract stale: {name}") 62:+ "subkutan", "administered" if index <= 4 else "documented_status", f"2026-0{index+1}-01", 118:+ datum,medication_name,event_type,medication_id,corrects_event_id,corrected_target_status,correction_reason,business_revision) 119:+ VALUES('2026-06-01','Legacy 1','corrected',?,999,'administered','reason',?)""", (medication_id, revision)) 123:+ datum,medication_name,event_type,medication_id,corrects_event_id,corrected_target_status,correction_reason,business_revision) 124:+ VALUES('2026-06-02','Legacy 1','corrected',?,?,'administered','',?)""", (medication_id, origin, revision)) 126:+ datum,medication_name,event_type,medication_id,corrects_event_id,corrected_target_status,correction_reason,business_revision) 127:+ VALUES('2026-06-02','Legacy 1','corrected',?,?,'administered','documented correction',?)""", (medication_id, origin, revision)) 128:+ correction = connection.execute("SELECT id FROM medication_administrations WHERE event_type='corrected'").fetchone()[0] 130:+ datum,medication_name,event_type,medication_id,corrects_event_id,corrected_target_status,correction_reason,business_revision) 131:+ VALUES('2026-06-03','Legacy 1','corrected',?,?,'missed','self-correction cycle two',?)""", (medication_id, correction, "b" * 64)) 134:+ datum,medication_name,event_type,medication_id,corrects_event_id,corrected_target_status,correction_reason,business_revision) 135:+ VALUES('2026-06-04','Legacy 1','corrected',?,?,'planned','parallel correction',?)""", (medication_id, origin, "c" * 64)) 161:+def _action_data(prescription: dict, *, status: str = "administered", preview: str | None = None) -> dict: 179:+ "corrected_target_status": "", 211:+ second = worker.apply_capture_action(payload) 212:+ assert first == second 227:+def test_new_reader_separates_statuses_and_old_reader_remains_compatible(tmp_path: Path) -> None: 237:+ VALUES('2026-06-14','SYNTHETIC_ADMINISTERED_MEDICATION','missed',?,'','','oral','oral','fixture','2026-06-14T10:00',?)""", (prescription["id"], revision)) 239:+ old_summary, old_events = medication_data(connection, "2026-06-15") 241:+ assert old_summary["last_administered"] is not None 242:+ assert old_events 246:+ assert history["administered"] and history["planned"] and history["missed"] and history["unknown"] 279:+ VALUES('2026-06-15',?,'administered',?,'10','mg','oral','fixture',?,?)""", 286:+def test_preview_binds_complete_payload_and_plan_can_only_be_consumed_once(tmp_path: Path) -> None: 313:+ with pytest.raises(RuntimeError, match="already consumed"): 382:+ data = _action_data(prescription, status="corrected") 385:+ "corrected_target_status": target_status, 394:+ administered = correction("administered") 395:+ administered.update({"actual_dose_value": "10", "actual_dose_unit": "mg"}) 396:+ validate_capture_payload(_structured_payload(administered, key="3" * 32)) 397:+ administered["actual_dose_value"] = "" 398:+ with pytest.raises(ValueError, match="administered correction"): 399:+ validate_capture_payload(_structured_payload(administered, key="4" * 32)) 409:+ missed = correction("missed") 410:+ validate_capture_payload(_structured_payload(missed, key="7" * 32)) 411:+ missed["planned_dose_value"] = "10" 412:+ missed["planned_dose_unit"] = "mg" 414:+ validate_capture_payload(_structured_payload(missed, key="8" * 32)) 417:+def test_report_marks_correction_chain_and_next_plan_excludes_consumed(tmp_path: Path) -> None: 428:+ VALUES('2026-06-20',?,'administered',?,'10','mg','oral','oral','fixture','2026-06-20T10:00',?)""", 431:+ datum,medication_name,event_type,medication_id,corrects_event_id,corrected_target_status, 433:+ VALUES('2026-06-21',?,'corrected',?,?, 'missed','synthetic','', 'unknown','fixture','2026-06-21T10:00',?)""", 436:+ datum,medication_name,event_type,medication_id,corrects_event_id,corrected_target_status, 438:+ VALUES('2026-06-22',?,'corrected',?,?, 'administered','synthetic','10','mg','oral','oral','fixture','2026-06-22T10:00',?)""", 450:+ VALUES('2026-09-01',?,'missed',?,?,'','unknown','fixture','2026-09-01T10:00',?)""", 457:+ administered = next(item for item in report["medications"]["administered"] if item["date"] == "2026-06-20") 458:+ corrections = [item for item in report["medications"]["corrected"] if item["date"] in {"2026-06-21", "2026-06-22"}] 459:+ assert administered["superseded_by_correction"] is True 518:+ VALUES('2026-08-20',?,'administered',?,'1','mg','oral','fixture','2026-08-20T10:00',?)""", 521:+ datum,medication_name,event_type,medication_id,corrects_event_id,corrected_target_status, 523:+ VALUES('2026-08-21',?,'corrected',?,?, 'missed','first','unknown','fixture','2026-08-21T10:00',?)""", 528:+ datum,medication_name,event_type,medication_id,corrects_event_id,corrected_target_status, 530:+ VALUES('2026-08-22',?,'corrected',?,?, 'planned','parallel','unknown','fixture','2026-08-22T10:00',?)""", 534:+ OR name IN ('ix_medication_event_medication_time','ix_medication_event_plan','ux_medication_plan_effective_consumption') 542:+ OR name IN ('ix_medication_event_medication_time','ix_medication_event_plan','ux_medication_plan_effective_consumption') 567:+ corrected_plan = plan("2026-09-02", "6" * 64) 569:+ datum,medication_name,event_type,medication_id,corrects_event_id,corrected_target_status, 571:+ VALUES('2026-08-22',?,'corrected',?,?, 'missed','synthetic','unknown','fixture','2026-08-22T09:00',?)""", 572:+ (name, medication_id, corrected_plan, "7" * 64)) 575:+ consumed = int(connection.execute("""INSERT INTO medication_administrations( 578:+ VALUES('2026-08-22',?,'missed',?,?,'unknown','fixture','2026-08-22T10:00',?)""", 581:+ datum,medication_name,event_type,medication_id,corrects_event_id,corrected_target_status, 583:+ VALUES('2026-08-22',?,'corrected',?,?, 'planned','synthetic','10','mg','oral','fixture','2026-08-22T11:00',?)""", 584:+ (name, medication_id, consumed, "a" * 64)).lastrowid) 586:+ with pytest.raises(sqlite3.IntegrityError, match="already effectively consumed"): 590:+ VALUES('2026-08-22',?,'administered',?,?,'10','mg','oral','fixture','2026-08-22T09:30',?)""", 591:+ (name, medication_id, corrected_plan, "d" * 64)) 614:+ with pytest.raises(sqlite3.IntegrityError, match="already effectively consumed"): 616:+ datum,medication_name,event_type,medication_id,corrects_event_id,corrected_target_status, 618:+ VALUES('2026-08-22',?,'corrected',?,?, 'administered','reactivate old chain','10','mg','oral','fixture','2026-08-22T11:30',?)""", 620:+ with pytest.raises(sqlite3.IntegrityError, match="already effectively consumed"): 624:+ VALUES('2026-08-22',?,'administered',?,?,'10','mg','oral','fixture','2026-08-22T12:00',?)""", __HERMES_CWD_8d46a20096ed__/home/agent/.hermes/repos/HealthManager__HERMES_CWD_8d46a20096ed__