"""The prepared-row contract shared by both importer jobs: the content hash and the row checks. DevDocs/FoodDatabase/PLAN.md Part B §2.2, §6.1. Standard library only, because both jobs import it: `prepare` (transform.py, which has no psycopg) and `load` (load.py, which has no openpyxl or orjson). It is part of the published method (ODbL 4.6), beside transform.py. A prepared row is one JSON object per line of .items.jsonl.gz: the item columns below, with every number as its stored 3-decimal string ("12.300") or null; countries as a sorted list of upper-case ISO 3166 alpha-2 codes ([] when unknown); portions as a list of {"sort_order": int, "label": str, "amount": "236.000"}; content_hash, the SHA-256 hex of canonical_payload(row). check_row() checks types, enums, formats, lengths and the hash. It does NOT check value ranges: the CHECKs that load.py copies onto its staging tables do that, so a row the transform failed to clean stops the run during staging, before anything permanent is written (Part B §2.4). """ import hashlib import json import re # Bump with every change to the hash input or to a conversion rule that changes stored values. # A new version gives every product a new row on the next run, so expect the retire guard. HASH_VERSION = "v1" SOURCE_KEYS = ("OPEN_FOOD_FACTS", "COFID", "USDA_FNDDS", "USDA_SR_LEGACY") BRANDED_SOURCES = ("OPEN_FOOD_FACTS",) KINDS = ("BASE", "BRANDED") BASES = ("PER_100G", "PER_100ML") MACROS = ("calories", "protein", "carbohydrates", "fats") MICROS = ("fibre_g", "sugar_g", "sat_fat_g", "iron_mg", "calcium_mg", "vit_c_mg", "vit_d_mcg", "vit_b12_mcg", "magnesium_mg", "omega3_mg", "potassium_mg", "sodium_mg", "alcohol_units") # fitforge_nutrition.food_database_items columns the importer writes, in hash order. id, source_id, # retired_at, created_at and search_vector are the database's. ITEM_COLUMNS = (("source_key", "source_code", "kind", "name", "brand", "countries", "basis") + MACROS + MICROS + ("serving_size", "serving_label")) ROW_KEYS = frozenset(ITEM_COLUMNS + ("content_hash", "portions")) PORTION_KEYS = frozenset(("sort_order", "label", "amount")) # Column widths (nutrition_2026_11_09_food_database.yaml). MAX_CODE = 64 MAX_NAME = 255 MAX_LABEL = 100 # NUMERIC(10,3): at most 7 integer digits and exactly 3 decimals. The sign is allowed here so the # copied CHECKs, not this format, are what refuse a negative value. NUMBER = re.compile(r"-?(0|[1-9][0-9]{0,6})\.[0-9]{3}") COUNTRY = re.compile(r"[A-Z]{2}") SHA256_HEX = re.compile(r"[0-9a-f]{64}") CONTROL = re.compile(r"[\x00-\x1f\x7f-\x9f]") # transform.clean_text removes these # Fields every source entry in manifest.json carries; prepare writes them and load reads them. SOURCE_MANIFEST_FIELDS = ("source_key", "file", "items_sha256", "rows_read", "rows_kept", "rows_skipped", "version_name", "file_date", "download_url", "sha256", "licence_name", "licence_url", "credit_text", "carbohydrate_basis") class RowError(ValueError): """A prepared row that breaks the contract.""" def canonical_payload(row): """The exact bytes content_hash is taken over (Part B §6.1): UTF-8 compact JSON.""" portions = sorted(row["portions"], key=lambda p: p["sort_order"]) values = [HASH_VERSION] + [row[column] for column in ITEM_COLUMNS] values.append([[p["sort_order"], p["label"], p["amount"]] for p in portions]) return json.dumps(values, ensure_ascii=False, separators=(",", ":")).encode("utf-8") def canonical_hash(row): return hashlib.sha256(canonical_payload(row)).hexdigest() def _text(row, key, limit, required): value = row[key] if value is None: if required: raise RowError(f"{key} is missing") return if not isinstance(value, str): raise RowError(f"{key} is not text") if not value or value != value.strip(): raise RowError(f"{key} is blank or has untrimmed ends") if len(value) > limit: raise RowError(f"{key} is {len(value)} characters, over {limit}") if CONTROL.search(value): raise RowError(f"{key} holds a control character") def _number(row, key, required): value = row[key] if value is None: if required: raise RowError(f"{key} is missing") return if not isinstance(value, str) or not NUMBER.fullmatch(value): raise RowError(f"{key} is {value!r}, not a NUMERIC(10,3) string") def check_row(row): """Raises RowError when the row breaks the contract. Ranges are left to the staging CHECKs.""" if not isinstance(row, dict): raise RowError("the line is not a JSON object") keys = set(row) if keys != ROW_KEYS: raise RowError(f"unexpected keys {sorted(keys - ROW_KEYS)}, missing keys {sorted(ROW_KEYS - keys)}") if row["source_key"] not in SOURCE_KEYS: raise RowError(f"source_key {row['source_key']!r} is not one of {SOURCE_KEYS}") expected_kind = "BRANDED" if row["source_key"] in BRANDED_SOURCES else "BASE" if row["kind"] != expected_kind: raise RowError(f"kind {row['kind']!r} for {row['source_key']}, expected {expected_kind}") if row["basis"] not in BASES: raise RowError(f"basis {row['basis']!r} is not one of {BASES}") _text(row, "source_code", MAX_CODE, True) _text(row, "name", MAX_NAME, True) _text(row, "brand", MAX_NAME, False) _text(row, "serving_label", MAX_LABEL, False) countries = row["countries"] if not isinstance(countries, list) or not all(isinstance(c, str) and COUNTRY.fullmatch(c) for c in countries): raise RowError(f"countries {countries!r} is not a list of upper-case two-letter codes") if countries != sorted(set(countries)): raise RowError(f"countries {countries!r} is not sorted and de-duplicated") for column in MACROS: _number(row, column, True) for column in MICROS: _number(row, column, False) _number(row, "serving_size", False) if (row["serving_size"] is None) != (row["serving_label"] is None): raise RowError("serving_size and serving_label must be set together or not at all") portions = row["portions"] if not isinstance(portions, list): raise RowError("portions is not a list") orders = [] for portion in portions: if not isinstance(portion, dict) or set(portion) != PORTION_KEYS: raise RowError(f"portion {portion!r} must have exactly {sorted(PORTION_KEYS)}") order = portion["sort_order"] if isinstance(order, bool) or not isinstance(order, int) or order < 0: raise RowError(f"portion sort_order {order!r} is not a whole number >= 0") orders.append(order) _text(portion, "label", MAX_LABEL, True) _number(portion, "amount", True) if len(set(orders)) != len(orders): raise RowError(f"portion sort_order values repeat: {orders}") content_hash = row["content_hash"] if not isinstance(content_hash, str) or not SHA256_HEX.fullmatch(content_hash): raise RowError("content_hash is not 64 lower-case hex characters") if content_hash != canonical_hash(row): raise RowError("content_hash does not match the row (a different transform or hash version?)")