#!/usr/bin/env python3 """Recompute a Xorino Log hash chain without the app. This is the tool the checksum page of the exported logbook points at. Its whole purpose is that somebody who does not trust us — or simply does not own an Android phone — can repeat the check on their own machine: read the change-log rows in order, rebuild each row's canonical form, hash it together with its predecessor, and stop at the first row whose hash does not come out. Deliberately dependency-free (standard library only) and deliberately short. A verifier nobody can read is not a verification. python verify_chain.py --db xorino-log.db python verify_chain.py --json export.json python verify_chain.py --self-test Exit status, so it can be used in a script: 0 the chain is intact 1 the chain is broken 2 the file's chain format is one this tool cannot reproduce The third is deliberately not the second. "This is wrong" and "I cannot judge this" are different answers, and a verifier that returns the first for the second accuses the data of the tool's own age -- at the moment somebody is presenting it. WHAT IT PROVES, AND WHAT IT DOES NOT ------------------------------------ It proves that no single row was altered, inserted or removed after the fact: each row's hash covers its own content *and* its predecessor's hash, so one changed byte invalidates that row and every row after it. Two things it cannot show, both inherent to a chain without a key: * that the whole chain was not recomputed from scratch by whoever holds the database, and * that rows were not removed from the END. What is left still starts at 1, is gapless and hashes correctly. When the input declares its own head and length (an export does), a mismatch is reported — but that declaration sits in the same file and can be edited too. Both are what the device signature is for, and why the honest word is tamper-*evident* rather than tamper-proof. """ import argparse import hashlib import json import sqlite3 import sys # U+2205 EMPTY SET. Marks a NULL and is distinguishable from an empty string, # which encodes as "0:". NULL = "∅" SEP = "|" def enc_num(value): """Integers go in bare; None becomes the NULL marker.""" return NULL if value is None else str(int(value)) def enc_text(value): """Length-prefixed by UTF-8 *byte* count: `9:Meier AG`. The prefix is why a separator inside free text cannot imitate a field boundary — the reader is told how many bytes to take, so it never has to look for a delimiter. This matters most for the payload fields, which are themselves `|`-joined strings. """ if value is None: return NULL raw = value.encode("utf-8") return f"{len(raw)}:{value}" # Every chain-line shape that has ever existed, newest first. This is what lets one tool # check a file of any age -- and it is the whole reason a verifier can be handed out as # "the current version" without an archive of older ones beside it. # # Only the CHAIN LINE matters here. The record forms inside `payload_before` and # `payload_after` have changed too (2026-09-04), but the chain hashes those payloads as # *text*: their internal shape never reaches this computation. So chain formats 1 and 2 # are, for a verifier, the same line -- and that is a fact worth stating rather than # leaving somebody to rediscover. # # The pre-1 shape is real: it is what was written before `reason` was added on 2026-08-30, # and it had to be recomputed by hand once. It never shipped, and it is kept as the working # proof that this mechanism does what it claims -- the next change will add its entry the # same way, and the old one simply stays. CHAIN_LINE_FORMS = ( # (name, the fields of the `chg` line, in order) ("with-reason (chain format 1 and 2)", ("seq", "occurred_at", "event_type", "entity_type", "entity_id", "payload_before", "payload_after", "reason")), ("without-reason (before 2026-08-30, never shipped)", ("seq", "occurred_at", "event_type", "entity_type", "entity_id", "payload_before", "payload_after")), ) # Which line shape a declared chain format uses. A format not listed here is newer than # this tool -- we then try the shapes we know, because a record-only change leaves the line # untouched and the file is verifiable after all. # Formats 1-5 all use the current line: 2 changed record forms, 3 added new row kinds # (origin, restore), 4 put `distance_source` into the trip record, 5 put `reading_source` # into the odometer-reference record. None of them touches the `chg` line, which is the only # thing hashed here -- record forms travel as text inside the payload fields, so a file of # any of these formats verifies with the same code. FORMAT_TO_LINE_SHAPE = {1: 0, 2: 0, 3: 0, 4: 0, 5: 0} NUMERIC_FIELDS = {"seq", "occurred_at", "entity_id"} def canonical_change_log(row, fields=None): """The canonical form of one chain link. Field order is fixed and is part of the format: changing it changes every hash ever written. `prev_hash` is deliberately *not* a field here — it is bound by chain_hash() below, and carrying it twice would invite the two copies to drift apart. `fields` selects the line shape; the default is the current one. """ if fields is None: fields = CHAIN_LINE_FORMS[0][1] out = ["chg"] for name in fields: # epoch milliseconds and ids are bare decimals; everything else is # length-prefixed, enums by name and never by ordinal out.append(enc_num(row[name]) if name in NUMERIC_FIELDS else enc_text(row.get(name))) return SEP.join(out) def chain_hash(prev_hash, canonical): """SHA-256 over `prev_hash + "|" + canonical`, lower-case hex. Binding the predecessor's hash into the input is what makes the chain a chain: altering an earlier row invalidates every later one. """ data = ((prev_hash or "") + SEP + canonical).encode("utf-8") return hashlib.sha256(data).hexdigest() def verify_any_form(rows, declared_format=None): """Verifies against whichever known line shape fits. Returns (ok, message, head, note). The order is deliberate and is the difference between checking and guessing: 1. If the file declares a format we know, that shape is authoritative. We do not fall back on a mismatch -- a declared-but-failing chain is a broken chain, and trying other shapes until one passes would turn the declaration into decoration. 2. If it declares nothing, the shape is unknown by omission, so we try the known ones oldest-code-first and say which one fitted. 3. If it declares a format newer than us, we say so and still try -- a record-only change leaves the line untouched, and refusing outright would send somebody looking for a tool that does not exist. """ if declared_format in FORMAT_TO_LINE_SHAPE: name, fields = CHAIN_LINE_FORMS[FORMAT_TO_LINE_SHAPE[declared_format]] ok, message, head = verify(rows, fields) return ok, message, head, None attempts = [] for name, fields in CHAIN_LINE_FORMS: ok, message, head = verify(rows, fields) if ok: if declared_format is None: note = (f"the file declares no chain format; it matches the " f"{name} line shape") else: note = (f"the file declares chain format {declared_format}, which " f"this tool does not know. The chain still verifies against " f"the {name}\n line shape, so the links are sound " f"-- but the payloads may be built differently than this " f"tool would read them.") return ok, message, head, note attempts.append((name, message)) # Nothing fitted. Report against the current shape, because that is the most likely # intent, but say that other shapes were tried -- otherwise "broken" overstates what # was established. name, message = attempts[0] note = ("tried every known line shape (" + ", ".join(n for n, _ in attempts) + ") and none reproduced the hashes.") return False, message, "", note def verify(rows, fields=None): """Walks the chain. Returns (ok, message, head_hash). Reports the *first* break rather than a count: after one altered row every later link fails too, so a count would say "everything after row 12 is wrong" when the honest statement is "row 12 is wrong". """ expected_prev = "" expected_seq = 1 head = "" for row in rows: seq = int(row["seq"]) if seq != expected_seq: return (False, f"row {seq}: sequence jumps -- expected {expected_seq}. " f"A row was removed or inserted.", head) stored_prev = row["prev_hash"] or "" if stored_prev != expected_prev: return (False, f"row {seq}: prev_hash does not match the preceding row.", head) recomputed = chain_hash(stored_prev, canonical_change_log(row, fields)) if recomputed != row["hash"]: return (False, f"row {seq}: content does not match its hash -- the row was " f"altered.\n stored: {row['hash']}\n" f" recomputed: {recomputed}", head) expected_prev = row["hash"] head = row["hash"] expected_seq += 1 return (True, f"{len(rows)} link(s), intact.", head) # --------------------------------------------------------------------- input COLUMNS = ["seq", "occurred_at", "event_type", "entity_type", "entity_id", "payload_before", "payload_after", "reason", "prev_hash", "hash"] def rows_from_db(path): con = sqlite3.connect(f"file:{path}?mode=ro", uri=True) con.row_factory = sqlite3.Row try: cur = con.execute( "SELECT " + ", ".join(COLUMNS) + " FROM change_log ORDER BY seq") return [dict(r) for r in cur.fetchall()] finally: con.close() def rows_from_json(path): """Returns (rows, declared, chain_format) — the file's own claims about itself. The declared head and length are checked against the recomputed ones below. They are the file's own claim about itself and therefore weak evidence — somebody who removes rows can edit them too — but the check turns a silent truncation into one that needs a second, deliberate edit, and it catches the accidental case outright. """ with open(path, encoding="utf-8") as fh: data = json.load(fh) if isinstance(data, dict): rows = data["change_log"] declared = data.get("chain") # Absent means unknown, NOT form 1. An export from before the field existed could # be any earlier shape, and defaulting to a specific one would pin the verifier to # a guess and switch off the very fallback that lets it read older files. chain_format = data.get("chain_format_version") else: rows, declared, chain_format = data, None, None return sorted(rows, key=lambda r: int(r["seq"])), declared, chain_format # ----------------------------------------------------------------- signature def verify_signature(head, signature_b64, public_key_b64, algorithm): """Checks the device signature over the chain head, if one is present. Optional on purpose: the chain is complete and checkable without it. The signature adds "and this device wrote it", not "and it is intact" — and it is absent by design after a restore onto a new phone, because the key cannot leave the device it was made on. Needs `cryptography`; without it we say so rather than pretending. """ try: from cryptography.hazmat.primitives import hashes, serialization from cryptography.hazmat.primitives.asymmetric import ec, utils # noqa: F401 from cryptography.exceptions import InvalidSignature except ImportError: return None, ("signature present but not checked: install `cryptography` " "to verify it (the chain itself is already verified above)") import base64 key = serialization.load_der_public_key(base64.b64decode(public_key_b64)) try: key.verify(base64.b64decode(signature_b64), head.encode("utf-8"), ec.ECDSA(hashes.SHA256())) return True, f"signature over the head verifies ({algorithm})" except InvalidSignature: return False, "signature does NOT match the head" def signatures_from_db(path): con = sqlite3.connect(f"file:{path}?mode=ro", uri=True) con.row_factory = sqlite3.Row try: cur = con.execute("SELECT seq, head_hash, signature, public_key, algorithm " "FROM chain_signatures ORDER BY seq DESC LIMIT 1") row = cur.fetchone() return dict(row) if row else None except sqlite3.OperationalError: return None # table absent — an unsigned chain is a valid state finally: con.close() # ----------------------------------------------------------------- self-test def self_test(): """Checks this implementation against the frozen vectors. The expectations below are the ones pinned in `ChainSerializerTest` on the app side, and they were built by hand from the format rules and hashed with `sha256sum` — not copied out of either implementation. If this passes, three independent constructions agree: the app's Java, this Python, and a command line hasher. """ cases = [ (dict(seq=1, occurred_at=1755691500000, event_type="CREATE", entity_type="TRIP", entity_id=17, payload_before=None, payload_after="trip|17|42", reason=None), "chg|1|1755691500000|6:CREATE|4:TRIP|17|" + NULL + "|10:trip|17|42|" + NULL, "", "e4fed7dedaf33304587845a84fc95c64add699cbda9b149031283bb627486ce7"), (dict(seq=1, occurred_at=1755691500000, event_type="CREATE", entity_type="TRIP", entity_id=17, payload_before=None, payload_after="trip|17|42", reason=None), None, "abc123", "5b2725f9f3838d5cd8b970a60e830990dc80e64b196a62d9de6579a4824da8e1"), (dict(seq=2, occurred_at=1755691500000, event_type="VOID", entity_type="TRIP", entity_id=17, payload_before="trip|17|42", payload_after=None, reason="versehentlich erfasst"), "chg|2|1755691500000|4:VOID|4:TRIP|17|10:trip|17|42|" + NULL + "|21:versehentlich erfasst", "abc123", "4fd60fbce8a910003d10717cc169e0bf1b6ddaa553293dc1b3861e15f9732131"), ] failures = 0 for row, expected_canonical, prev, expected_hash in cases: got = canonical_change_log(row) if expected_canonical is not None and got != expected_canonical: print(f"FAIL canonical form for seq {row['seq']}") print(f" expected: {expected_canonical}") print(f" got: {got}") failures += 1 got_hash = chain_hash(prev, got) if got_hash != expected_hash: print(f"FAIL hash for seq {row['seq']} after prev={prev!r}") print(f" expected: {expected_hash}") print(f" got: {got_hash}") failures += 1 # An empty string and a NULL are different values and must encode differently. if enc_text("") == enc_text(None): print("FAIL empty string and NULL encode the same") failures += 1 # A separator inside a payload must not be able to imitate a field boundary. a = canonical_change_log(dict(seq=1, occurred_at=0, event_type="CREATE", entity_type="TRIP", entity_id=1, payload_before="x", payload_after="y|z", reason=None)) b = canonical_change_log(dict(seq=1, occurred_at=0, event_type="CREATE", entity_type="TRIP", entity_id=1, payload_before="x|y", payload_after="z", reason=None)) if a == b: print("FAIL two different field splits produced the same canonical form") failures += 1 print("self-test: " + ("all checks passed" if failures == 0 else f"{failures} failure(s)")) return failures == 0 # ---------------------------------------------------------------------- main def main(): # Output stays ASCII on purpose: this tool is meant to be run by somebody else, on a # machine we know nothing about, and a Windows console defaulting to cp1252 turns an # em dash into a replacement character. A verifier whose output looks corrupted invites # exactly the doubt it exists to remove. The one non-ASCII character that must survive # is the NULL marker inside the canonical form -- and that one is hashed, never printed. try: sys.stdout.reconfigure(errors="backslashreplace") except Exception: pass ap = argparse.ArgumentParser(description=__doc__.splitlines()[0]) src = ap.add_mutually_exclusive_group(required=True) src.add_argument("--db", help="a Xorino Log SQLite database") src.add_argument("--json", help="an exported logbook (JSON)") src.add_argument("--self-test", action="store_true", help="check this tool against the frozen vectors") args = ap.parse_args() if args.self_test: return 0 if self_test() else 1 declared = None chain_format = None if args.db: rows = rows_from_db(args.db) else: rows, declared, chain_format = rows_from_json(args.json) if not rows: print("no change-log rows found -- nothing to verify") return 0 ok, message, head, note = verify_any_form(rows, chain_format) print(("chain: " if ok else "CHAIN BROKEN -- ") + message) if note: print(("note: " if ok else " ") + note) if not ok: # Exit 2 stays reserved for "I cannot judge this". A format we know that fails is a # judgement, not an inability, so it is 1. return 2 if (chain_format is not None and chain_format not in FORMAT_TO_LINE_SHAPE) else 1 print(f"head: {head}") # The chain alone cannot notice rows removed from its END: what is left starts at 1, # is gapless and hashes correctly. Only something outside the chain can — the file's # own declaration (weak, see rows_from_json) or a signature (strong, below). if declared: problems = [] if "length" in declared and int(declared["length"]) != len(rows): problems.append(f"the file declares {declared['length']} link(s), " f"{len(rows)} are present -- rows were removed or added") if declared.get("head") and declared["head"] != head: problems.append(f"the file declares a different head:\n" f" declared: {declared['head']}\n" f" recomputed: {head}") if problems: for p in problems: print("MISMATCH -- " + p) return 1 print("declared head and length match what was recomputed.") if args.db: sig = signatures_from_db(args.db) if sig is None: print("signature: none -- the chain is intact and unsigned. That is the " "expected state after a restore onto a new device.") print(" Note what is therefore NOT ruled out: rows removed from" " the END of the chain.\n" " What remains still starts at 1, is gapless and hashes" " correctly.\n" " Only a signature over a later position can show that" " something was cut off.") elif sig["head_hash"] != head: print(f"signature: covers position {sig['seq']}, not the current head -- " f"rows were added after it was signed.") else: result, note = verify_signature(head, sig["signature"], sig["public_key"], sig["algorithm"]) print("signature: " + note) if result is False: return 1 return 0 if __name__ == "__main__": sys.exit(main())