# _CONTRACT/silver_rem_gates.yaml — SILVER_REM single machine-readable gate spec
# Contract: 8020rei.silver.real_estate_master v1.0 (DRAFT) — see knowledge/notes/etl_audit/SILVER_REM_CONTRACT.md §7.
# This file is the SINGLE definition of every executable gate in contract §§2–6 [CON-02].
# Producer (DE) and consumer (modeling) both execute THIS spec — producer inside the
# silver build pre-publish on Athena (target state, ramps per §8.1), consumer before
# every feature build / list build on DuckDB (rerun_silver_qa.sh), then 0-diff every
# gate's measured value where both run. Changes follow contract §9.2 sign-off.
#
# STATUS: v0.1.0 DRAFT (2026-06-09, consumer-authored) — adoption criterion §9.3-2 input.
# EVERY threshold in this file is `threshold_status: provisional` by construction: the
# contract is v1.0 and nothing was producer-pinned before it — all values derive from
# the measured 2026-04 baseline (finding 104, battery run across 8 counties, 4.81M rows)
# as baseline-max + headroom. DE confirms or replaces at kickoff (§9.3-2). Gates tagged
# `reality: true` tolerate a DOCUMENTED FA-source reality (contract D-1) — their
# thresholds are tolerances, not aspirations; untagged gates are invariants (target 0).
#
# GATE ID NAMESPACE: battery ids (A1…H1, G1/G2) kept verbatim for continuity with
# finding 104 + the live dashboard (/internal/silver-ae-qa-2f9a1c7e4b81/). Cross-contract
# references qualify: silver-rem:G1 (YearBuilt mutation) ≠ gold-permits G1 (grain).

meta:
  contract_id: 8020rei.silver.real_estate_master
  contract_version: "1.0"
  gates_yaml_version: "0.1.0"
  status: DRAFT
  date: "2026-06-09"
  authors: [ignacio-consumer]
  pending_sign_off: [santiago-producer]
  baseline: "2026-04 snapshot · 8 counties (12101 12053 12103 12086 12095 12031 12115 04013) · 4,814,454 rows · battery results data/sandbox/silver_qa/qa_silver_results.json"

  # Field semantics — identical to gold-permits gates.yaml v0.1.0:
  # id / section / dimension [DIM-01] / scope / mode (§8.1) / owner / measured_as /
  # alert_threshold / fail_threshold (direction ≤ unless stated) / threshold_status /
  # today / implements (battery:<id> = qa_silver_assessor.py check | <ticket-id> |
  # finding-NN | <etl_audit doc filename> | to_build) / check (canonical Athena/Trino
  # SQL) or procedure.
  # rule_version: bumped on ANY logic change to the gate's check [RUL-02] — results are
  #               only comparable across runs at the same rule_version.
  # Extra field here: `reality: true` = documented FA-source reality (contract D-1) —
  # the threshold is a TOLERANCE around the vendor baseline, not a defect target.
  # scope value `per_county_bucket` = evaluated per (county, UseType bucket); the
  # binding bucket is named in measured_as.
  # NULL-rate rule: a NULL measured value from an empty denominator (NULLIF(...,0)) is
  # recorded as measured=NULL and evaluated as PASS (nothing to assess), never as 0.

  bindings:
    "{table}": "Silver Real_Estate_Master_V1 at ({fips}, {period}) — producer: Athena 8020rei-silver-data-lake; consumer: data/sandbox/silver_rem/<period>/<fips>/ parquet (DuckDB)"
    "{fips}": "iterated over the contracted county set (5-digit string; Athena stores INTEGER — runner casts)"
    "{period}": "snapshot month YYYY-MM (latest published)"
    "{prev_period}": "the previous battery-run period (2025-10 in the baseline run) for group-G/VOL gates"
    "{prev_table}": "Silver Real_Estate_Master_V1 at ({fips}, {prev_period}) — same resolution as {table}"
    "{cohort}": "silver_cohort latest partition (COH-01)"
    "{bronze_assessor}": "Bronze Assessor_Annual at the matching vintage, via S3 (BRZ-01; consumer read verified 2026-06-09)"
    "{delivery_counties}": "the CallZeke delivery county set (today 12053, 12101, 12103)"
    "{zip_lo}": "lower bound of the county state's USPS ZIP band (FL 32000, AZ 85000, …) from the battery's state-band map (qa_silver_assessor.py); externalize with GEO-05's state_zip_ranges.csv when published"
    "{zip_hi}": "upper bound of the same band (FL 34999, AZ 86599, …)"
    "{distress_cols}": "the 23 distress trajectory column names (standard §7.1); list pinned in this file's repo dir when DIS-01 first runs"
    "{street_type_tokens}": "2-char street-type fragment denylist for UNIT-01 (ST, RD, DR, AV, …) — SILVER-07"
    "{sfh_bucket}": "comma-separated quoted LOWERCASE UseType literals for the battery's sfh bucket (today: 'sfh') — predicates compare lower(trim(UseType))"
    "{land_bucket}": "same, land bucket (today: 'land')"
    # List placeholders expand to bare comma-separated SQL literals inside IN (...).
    "{norm}": "address canonicalization used by the battery: regexp_replace(upper(x), '[^A-Z0-9]', '') — inline, both engines, see shims"

  dialect_shims:
    # One semantic definition; runners apply ONLY these mechanical rewrites for DuckDB.
    - "regexp_like(x, p)  ->  regexp_matches(x, p)"
    - "regexp_replace(x, p, r)  ->  regexp_replace(x, p, r, 'g')   (DuckDB replaces first match by default; Trino replaces all)"
    - "date_add('day', n, d)  ->  d + INTERVAL (n) DAY"
    # TRY_CAST, least/greatest, year(), upper/trim: identical in Trino and DuckDB.

gates:

  # ────────────────────────────── SCHEMA ──────────────────────────────

  - id: SCH-01
    rule_version: 1
    title: contracted columns present with the contracted (VARCHAR-regime) types
    section: "§3.1–3.7"
    dimension: validity
    scope: snapshot
    mode: enforce
    owner: producer
    measured_as: missing/renamed columns vs the contract §3 families (identity, address,
      type/physicals, valuation, dates, owner, 23 distress columns + date companions)
    alert_threshold: 0
    fail_threshold: 0
    threshold_status: provisional
    today: "battery ran green on the 2026-04 schema — manifest = the columns it reads"
    implements: to_build
    procedure: DESCRIBE/information_schema diff vs the column manifest derived from
      qa_silver_assessor.py reads + contract §3; consumer data gate fails loud on
      missing/renamed columns.

  # ────────────────────────────── GROUP A — KEY / IDENTITY (§2) ──────────────────────────────

  - id: A1
    rule_version: 1
    title: one row per PropertyId per (fips, period)
    section: "§2 GR1/GR5"
    dimension: uniqueness
    scope: per_county
    mode: enforce
    owner: producer
    measured_as: duplicate PropertyId rows within the (fips, period) partition — a COUNT
      (battery-equivalent = (fan-out ratio − 1) × distinct ids; the battery re-key emits
      this same count so cross-run values 0-diff in like units)
    alert_threshold: 0
    fail_threshold: 0
    threshold_status: provisional
    today: "0 duplicate rows (fan-out ratio 1.0000) in all 8 counties (battery 2026-04)"
    implements: battery:A1
    check: |
      SELECT COUNT(*) - COUNT(DISTINCT PropertyId)
      FROM {table}

  - id: A2
    rule_version: 1
    title: one situs address → many PropertyIds
    section: "§2 / §3.2"
    dimension: uniqueness
    scope: per_county
    mode: shadow
    owner: producer
    reality: true
    measured_as: share of distinct NORMALIZED situs addresses mapping to >1 PropertyId —
      the battery's address-share metric the thresholds were calibrated on (NOT a
      row-share; row-share reads 2–3× higher and would breach day one). The
      ''-normalized empty group is included via coalesce, matching the battery.
    alert_threshold: 0.045
    fail_threshold: 0.05
    threshold_status: provisional — baseline max Hernando 4.12% + headroom
    today: "0.41–4.12% of addresses/county (Hernando 4.12% = 4,466 of 108,344; Maricopa 1.32% = 21,045) — genuine assessor address duplicates"
    implements: battery:A2
    check: |
      -- verified to reproduce qa_silver_results.json exactly (Pasco 1577/281095, Pinellas 4125/424412)
      WITH p AS (
        SELECT regexp_replace(upper(coalesce(SitusFullStreetAddress, '')), '[^A-Z0-9]', '') AS a,
               COUNT(DISTINCT PropertyId) AS n
        FROM {table}
        WHERE PropertyId IS NOT NULL
        GROUP BY 1
      )
      SELECT CAST(SUM(CASE WHEN n > 1 THEN 1 ELSE 0 END) AS DOUBLE) / COUNT(*) FROM p

  - id: A3
    rule_version: 1
    title: one PropertyId → many situs addresses
    section: "§2 GR2"
    dimension: consistency
    scope: per_county
    mode: enforce
    owner: producer
    measured_as: PropertyIds carrying >1 distinct NORMALIZED situs address within the
      partition (normalized form matches the battery — punctuation/case variants do not fire)
    alert_threshold: 0
    fail_threshold: 0
    threshold_status: provisional
    today: "0 in all 8 counties"
    implements: battery:A3
    check: |
      SELECT COUNT(*) FROM (
        SELECT PropertyId FROM {table}
        WHERE SitusFullStreetAddress IS NOT NULL
        GROUP BY PropertyId
        HAVING COUNT(DISTINCT regexp_replace(upper(coalesce(SitusFullStreetAddress, '')), '[^A-Z0-9]', '')) > 1
      ) multi

  - id: A4
    rule_version: 1
    title: one APN → many PropertyIds (collision key, QA only)
    section: "§2 GR4 / SILVER-03"
    dimension: uniqueness
    scope: per_county
    mode: shadow
    owner: producer
    reality: true
    measured_as: share of distinct non-empty APNs mapping to >1 PropertyId — the battery's
      APN-share metric the thresholds were calibrated on (NOT a row-share; row-share Duval
      reads 1.11% and breaches). Multi-parcel APNs + un-deduped id generations — SILVER-03.
    alert_threshold: 0.0055
    fail_threshold: 0.01
    threshold_status: provisional — baseline max Duval 0.49% (alert set just above it)
    today: "0.01–0.49% of APNs/county (Duval 1,981 of ~404K)"
    implements: [battery:A4, SILVER-03]
    check: |
      WITH p AS (
        SELECT NULLIF(trim(APN), '') AS apn, COUNT(DISTINCT PropertyId) AS n
        FROM {table}
        WHERE NULLIF(trim(APN), '') IS NOT NULL AND PropertyId IS NOT NULL
        GROUP BY 1
      )
      SELECT CAST(SUM(CASE WHEN n > 1 THEN 1 ELSE 0 END) AS DOUBLE) / COUNT(*) FROM p

  # ────────────────────────────── GROUP B — COMPLETENESS (§3.2/3.3/3.6) ──────────────────────────────

  - id: B1
    rule_version: 1
    title: situs address empty
    section: "§3.2 / SILVER-06 / SRC-04"
    dimension: completeness
    scope: per_county
    mode: shadow
    owner: producer
    reality: true
    measured_as: NULL/empty SitusFullStreetAddress share
    alert_threshold: 0.07
    fail_threshold: 0.18
    threshold_status: provisional — wide on purpose; covers Pasco 16.4% PENDING the
      open-item-3 root cause (ETL parse drop vs FA source gap). Splits and tightens
      per county after the verdict.
    today: "Pasco 16.40% (55,462) · Maricopa 6.92% · Miami-Dade 5.18% · rest <1%"
    implements: [battery:B1, SILVER-06, SRC-04]
    check: |
      SELECT CAST(SUM(CASE WHEN SitusFullStreetAddress IS NULL
                            OR trim(SitusFullStreetAddress) = '' THEN 1 ELSE 0 END) AS DOUBLE)
             / COUNT(*)
      FROM {table}

  - id: B2
    rule_version: 1
    title: mailing address empty
    section: "§3.2"
    dimension: completeness
    scope: per_county
    mode: shadow
    owner: producer
    reality: true
    measured_as: NULL/empty MailingFullStreetAddress share (OO-at-permit comparand)
    alert_threshold: 0.01
    fail_threshold: 0.015
    threshold_status: provisional — baseline max 0.91%
    today: "0.02–0.91%/county"
    implements: battery:B2
    check: |
      SELECT CAST(SUM(CASE WHEN MailingFullStreetAddress IS NULL
                            OR trim(MailingFullStreetAddress) = '' THEN 1 ELSE 0 END) AS DOUBLE)
             / COUNT(*)
      FROM {table}

  - id: B3
    rule_version: 1
    title: situs ZIP empty
    section: "§3.2"
    dimension: completeness
    scope: per_county
    mode: shadow
    owner: producer
    reality: true
    measured_as: NULL/empty SitusZIP5 share
    alert_threshold: 0.03
    fail_threshold: 0.10
    threshold_status: provisional — baseline max Pasco 8.6% (co-moves with B1)
    today: "Pasco 8.6% · Maricopa 2.71% · Miami-Dade 1.32% · rest <1%"
    implements: battery:B3
    check: |
      SELECT CAST(SUM(CASE WHEN SitusZIP5 IS NULL OR trim(SitusZIP5) = '' THEN 1 ELSE 0 END) AS DOUBLE)
             / COUNT(*)
      FROM {table}

  - id: B4
    rule_version: 1
    title: owner name empty
    section: "§3.6"
    dimension: completeness
    scope: per_county
    mode: shadow
    owner: producer
    reality: true
    measured_as: NULL/empty OwnerNAME1FULL share
    alert_threshold: 0.0005
    fail_threshold: 0.001
    threshold_status: provisional — baseline max 0.04%
    today: "0.00–0.04%/county"
    implements: battery:B4
    check: |
      SELECT CAST(SUM(CASE WHEN OwnerNAME1FULL IS NULL OR trim(OwnerNAME1FULL) = '' THEN 1 ELSE 0 END) AS DOUBLE)
             / COUNT(*)
      FROM {table}

  - id: B5
    rule_version: 1
    title: YearBuilt empty (type-aware — binding bucket = SFH)
    section: "§3.3"
    dimension: completeness
    scope: per_county_bucket
    mode: shadow
    owner: producer
    reality: true
    measured_as: NULL/empty YearBuilt share in the SFH bucket (model-critical; thresholds
      bind HERE). The ALL-types share is reported as an informational companion —
      empties concentrate in Land/Others by source reality.
    alert_threshold: 0.0095
    fail_threshold: 0.012
    threshold_status: provisional — SFH baseline max Orange 0.93% (alert just above it;
      ALL-types runs 4.6–23.9%)
    today: "SFH ≤0.93% (Orange 2,956; Pinellas 5 rows) · ALL 4.6–23.9% (Hernando 23.86%)"
    implements: battery:B5
    check: |
      SELECT CAST(SUM(CASE WHEN YearBuilt IS NULL OR trim(YearBuilt) = '' THEN 1 ELSE 0 END) AS DOUBLE)
             / COUNT(*)
      FROM {table}
      WHERE lower(trim(UseType)) IN ({sfh_bucket})
    check_companion: |
      -- informational ALL-types rate (NOT threshold-bound)
      SELECT CAST(SUM(CASE WHEN YearBuilt IS NULL OR trim(YearBuilt) = '' THEN 1 ELSE 0 END) AS DOUBLE)
             / COUNT(*)
      FROM {table}

  - id: B6
    rule_version: 1
    title: all three values empty simultaneously
    section: "§3.4"
    dimension: completeness
    scope: per_county
    mode: enforce
    owner: producer
    measured_as: rows where MarketTotalValue, AssdTotalValue AND CurrentAVMValue are all empty/NULL
    alert_threshold: 0
    fail_threshold: 0
    threshold_status: provisional
    today: "0.00% in all 8 counties"
    implements: battery:B6
    check: |
      SELECT COUNT(*) FROM {table}
      WHERE (MarketTotalValue IS NULL OR trim(MarketTotalValue) = '')
        AND (AssdTotalValue   IS NULL OR trim(AssdTotalValue)   = '')
        AND (CurrentAVMValue  IS NULL OR trim(CurrentAVMValue)  = '')

  # ────────────────────────────── GROUP C — VALUE SANITY (§3.4) ──────────────────────────────

  - id: C1
    rule_version: 1
    title: market value ≤ 0
    section: "§3.4 D-3"
    dimension: validity
    scope: per_county
    mode: shadow
    owner: producer
    reality: true
    measured_as: share of rows with TRY_CAST(MarketTotalValue) ≤ 0 (sentinel/exempt parcels)
    alert_threshold: 0.037
    fail_threshold: 0.04
    threshold_status: provisional — baseline max Pasco 3.51% (alert above it — D-1 says
      tolerances must not alert on the known baseline)
    today: "0.12–3.51%/county"
    implements: battery:C1
    check: |
      SELECT CAST(SUM(CASE WHEN TRY_CAST(MarketTotalValue AS DOUBLE) <= 0 THEN 1 ELSE 0 END) AS DOUBLE)
             / COUNT(*)
      FROM {table}

  - id: C2
    rule_version: 1
    title: assessed value ≤ 0
    section: "§3.4"
    dimension: validity
    scope: per_county
    mode: shadow
    owner: producer
    reality: true
    measured_as: share of rows with TRY_CAST(AssdTotalValue) ≤ 0
    alert_threshold: 0.037
    fail_threshold: 0.04
    threshold_status: provisional — baseline max Pasco 3.60% (alert above it)
    today: "0.43–3.6%/county"
    implements: battery:C2
    check: |
      SELECT CAST(SUM(CASE WHEN TRY_CAST(AssdTotalValue AS DOUBLE) <= 0 THEN 1 ELSE 0 END) AS DOUBLE)
             / COUNT(*)
      FROM {table}

  - id: C3
    rule_version: 1
    title: AVM ≤ 0 ("could not value" sentinel)
    section: "§3.4"
    dimension: validity
    scope: per_county
    mode: shadow
    owner: producer
    reality: true
    measured_as: share of rows with TRY_CAST(CurrentAVMValue) ≤ 0 — 0 is the vendor's
      could-not-value sentinel, never a price; consumers read it as NULL
    alert_threshold: 0.17
    fail_threshold: 0.18
    threshold_status: provisional — baseline max Pasco 16.50% (alert above it)
    today: "7.9–16.5%/county (Pasco 55,793)"
    implements: battery:C3
    check: |
      SELECT CAST(SUM(CASE WHEN TRY_CAST(CurrentAVMValue AS DOUBLE) <= 0 THEN 1 ELSE 0 END) AS DOUBLE)
             / COUNT(*)
      FROM {table}

  - id: C4
    rule_version: 1
    title: market value below type floor (type-aware — binding bucket = SFH)
    section: "§3.4"
    dimension: validity
    scope: per_county_bucket
    mode: shadow
    owner: producer
    reality: true
    measured_as: SFH-bucket share with 0 < market value < $10K floor (thresholds bind here);
      ALL-types companion informational (Land floor $1K; non-SFH below-floor values are
      sentinels/nominal assessments by source reality)
    alert_threshold: 0.003
    fail_threshold: 0.005
    threshold_status: provisional — SFH baseline max Maricopa 0.16%; ALL runs up to 7.45%
    today: "SFH ≤0.16% · ALL 0.15–7.45% (Pasco 25,209)"
    implements: battery:C4
    check: |
      SELECT CAST(SUM(CASE WHEN TRY_CAST(MarketTotalValue AS DOUBLE) > 0
                            AND TRY_CAST(MarketTotalValue AS DOUBLE) < 10000 THEN 1 ELSE 0 END) AS DOUBLE)
             / COUNT(*)
      FROM {table}
      WHERE lower(trim(UseType)) IN ({sfh_bucket})
    check_companion: |
      -- informational ALL-types rate with the type-aware floor (NOT threshold-bound)
      SELECT CAST(SUM(CASE WHEN TRY_CAST(MarketTotalValue AS DOUBLE) > 0
                            AND TRY_CAST(MarketTotalValue AS DOUBLE)
                                < (CASE WHEN lower(trim(UseType)) IN ({land_bucket}, 'others')
                                        THEN 1000 ELSE 10000 END)
                           THEN 1 ELSE 0 END) AS DOUBLE) / COUNT(*)
      FROM {table}

  - id: C5
    rule_version: 1
    title: AVM outside its [Low, High] band
    section: "§3.4"
    dimension: consistency
    scope: per_county
    mode: enforce
    owner: producer
    measured_as: rows where all three of AVM/Low/High cast AND AVM ∉ [Low, High]
    alert_threshold: 0
    fail_threshold: 0
    threshold_status: provisional
    today: "0 in all 8 counties — the AVM triple is internally consistent"
    implements: battery:C5
    check: |
      SELECT COUNT(*) FROM {table}
      WHERE TRY_CAST(CurrentAVMValue AS DOUBLE) IS NOT NULL
        AND TRY_CAST(LowValue  AS DOUBLE) IS NOT NULL
        AND TRY_CAST(HighValue AS DOUBLE) IS NOT NULL
        AND (TRY_CAST(CurrentAVMValue AS DOUBLE) < TRY_CAST(LowValue AS DOUBLE)
          OR TRY_CAST(CurrentAVMValue AS DOUBLE) > TRY_CAST(HighValue AS DOUBLE))

  - id: C6
    rule_version: 1
    title: sale price negative
    section: "§3.4"
    dimension: validity
    scope: per_county
    mode: enforce
    owner: producer
    measured_as: rows with TRY_CAST(CurrentSalesPrice) < 0
    alert_threshold: 0
    fail_threshold: 0
    threshold_status: provisional
    today: "0 in all 8 counties"
    implements: battery:C6
    check: |
      SELECT COUNT(*) FROM {table}
      WHERE TRY_CAST(CurrentSalesPrice AS DOUBLE) < 0

  # ────────────────────────────── GROUP D — DATE SANITY (§3.5) ──────────────────────────────

  - id: D1
    rule_version: 1
    title: sale recording date in the future
    section: "§3.5"
    dimension: validity
    scope: per_county
    mode: enforce
    owner: producer
    measured_as: rows with parsed CurrentSaleRecordingDate > run date
    alert_threshold: 0
    fail_threshold: 0
    threshold_status: provisional
    today: "0 in all 8 counties"
    implements: battery:D1
    check: |
      -- YYYYMMDD VARCHAR → DATE portably, zero shims (TRY_CAST('20250131' AS DATE) is
      -- NULL in BOTH engines — the naive form is a verified no-op)
      SELECT COUNT(*) FROM {table}
      WHERE TRY_CAST(substr(trim(CurrentSaleRecordingDate), 1, 4) || '-'
                  || substr(trim(CurrentSaleRecordingDate), 5, 2) || '-'
                  || substr(trim(CurrentSaleRecordingDate), 7, 2) AS DATE) > current_date

  - id: D2
    rule_version: 1
    title: contract date after recording date
    section: "§3.5"
    dimension: consistency
    scope: per_county
    mode: shadow
    owner: producer
    reality: true
    measured_as: rows (count) with both dates parsed AND contract > recording
    alert_threshold: 0
    fail_threshold: 10   # rows per county
    threshold_status: provisional — small genuine-source residual; tolerance pinned at kickoff
    mode_note: ground truth (qa_silver_results.json D_contract_gt_recording) is NONZERO in
      ALL 8 counties — D2 is one of the 18 battery fails, NOT a clean pass. Enforce-at-0
      would refuse every build on day one. Shadow with a ≤10-rows/county tolerance until
      kickoff pins it.
    today: "n=1–7 per county, nonzero in ALL 8 (19 pooled — Pasco 1, Hernando 2, Pinellas 7, Miami-Dade 3, Orange 2, Duval 2, Sarasota 1, Maricopa 1)"
    implements: battery:D2
    check: |
      -- same portable YYYYMMDD parse as D1
      SELECT COUNT(*) FROM {table}
      WHERE TRY_CAST(substr(trim(CurrentSaleContractDate), 1, 4) || '-'
                  || substr(trim(CurrentSaleContractDate), 5, 2) || '-'
                  || substr(trim(CurrentSaleContractDate), 7, 2) AS DATE)
          > TRY_CAST(substr(trim(CurrentSaleRecordingDate), 1, 4) || '-'
                  || substr(trim(CurrentSaleRecordingDate), 5, 2) || '-'
                  || substr(trim(CurrentSaleRecordingDate), 7, 2) AS DATE)

  - id: D3
    rule_version: 1
    title: YearBuilt in the future
    section: "§3.3"
    dimension: validity
    scope: per_county
    mode: enforce
    owner: producer
    measured_as: rows with TRY_CAST(YearBuilt) > year(run date)
    alert_threshold: 0
    fail_threshold: 0
    threshold_status: provisional
    today: "0 in all 8 counties"
    implements: battery:D3
    check: |
      SELECT COUNT(*) FROM {table}
      WHERE TRY_CAST(YearBuilt AS INTEGER) > year(current_date)

  - id: D4
    rule_version: 1
    title: YearBuilt before 1800
    section: "§3.3"
    dimension: validity
    scope: per_county
    mode: enforce
    owner: producer
    measured_as: rows with TRY_CAST(YearBuilt) < 1800 (non-null)
    alert_threshold: 0
    fail_threshold: 0
    threshold_status: provisional
    today: "0 in all 8 counties"
    implements: battery:D4
    check: |
      SELECT COUNT(*) FROM {table}
      WHERE TRY_CAST(YearBuilt AS INTEGER) < 1800

  # ────────────────────────────── GROUP E — GEO CONSISTENCY (§3.2) ──────────────────────────────

  - id: E1
    rule_version: 1
    title: ZIP outside the county state's band
    section: "§3.2"
    dimension: referential_integrity
    scope: per_county
    mode: shadow
    owner: producer
    reality: true
    measured_as: share of parseable ZIPs outside [{zip_lo}, {zip_hi}] for the county's state
      (parseable-ZIP denominator — declared basis; the battery divides by all rows, so the
      battery re-key adopts THIS basis for 0-diff)
    alert_threshold: 0.00001
    fail_threshold: 0.00005
    threshold_status: provisional — baseline n=1 rows imply rates 5.7e-7 (Maricopa) to
      3.2e-6 (Sarasota); alert 1e-5 lets single-row strays pass un-paged (≈3+ rows in
      Sarasota pages), fail 5e-5 ≈ 15–88 rows/county
    today: "0 in 6 counties; Sarasota n=1 (3.2e-6), Maricopa n=1 (5.7e-7)"
    implements: battery:E1
    check: |
      SELECT CAST(SUM(CASE WHEN TRY_CAST(SitusZIP5 AS INTEGER) NOT BETWEEN {zip_lo} AND {zip_hi}
                           THEN 1 ELSE 0 END) AS DOUBLE)
             / NULLIF(SUM(CASE WHEN TRY_CAST(SitusZIP5 AS INTEGER) IS NOT NULL THEN 1 ELSE 0 END), 0)
      FROM {table}

  - id: E2
    rule_version: 1
    title: SitusState mismatches the fips-implied state
    section: "§3.2"
    dimension: consistency
    scope: per_county
    mode: enforce
    owner: producer
    measured_as: rows where SitusState ≠ the state of the county fips
    alert_threshold: 0
    fail_threshold: 0
    threshold_status: provisional
    today: "0 in all 8 counties"
    implements: battery:E2
    procedure: compare SitusState vs the fips→state map (battery's state map; same source
      as {zip_lo}/{zip_hi} binding). Count mismatches.

  # ────────────────────────────── GROUP F — OWNER / OCCUPANCY (§3.6) ──────────────────────────────

  - id: F1
    rule_version: 1
    title: owner-occupied flag with mailing ≠ situs
    section: "§3.6 D-2"
    dimension: consistency
    scope: per_county
    mode: shadow
    owner: producer
    reality: true
    measured_as: share of rows with OwnerOccupied='Y' AND normalized mailing ≠ normalized situs
    alert_threshold: 0.027
    fail_threshold: 0.03
    threshold_status: provisional — baseline max Miami-Dade 2.64%
    today: "0.01–2.64%/county"
    implements: battery:F1
    check: |
      -- battery predicate replicated EXACTLY (coalesce-to-'' on both sides + non-empty
      -- mailing guard; situs may be NULL and still count): Pasco 4,834 reproduced
      SELECT CAST(SUM(CASE WHEN OwnerOccupied = 'Y'
                            AND regexp_replace(upper(coalesce(MailingFullStreetAddress, '')), '[^A-Z0-9]', '')
                              <> regexp_replace(upper(coalesce(SitusFullStreetAddress, '')),  '[^A-Z0-9]', '')
                            AND trim(coalesce(MailingFullStreetAddress, '')) <> ''
                           THEN 1 ELSE 0 END) AS DOUBLE)
             / COUNT(*)
      FROM {table}

  - id: F2
    rule_version: 1
    title: Absentee ↔ OwnerOccupied derivation holds
    section: "§3.6 D-2 / SILVER-01"
    dimension: consistency
    scope: per_county
    mode: enforce
    owner: producer
    measured_as: rows where (Absentee='1') ≠ (OwnerOccupied IS NULL OR OwnerOccupied <> 'Y') —
      the SILVER-01 deterministic-inverse relation this contract DECLARES (D-2); a break
      here means the derivation changed, which is a §9.1 breaking change, not an improvement
    alert_threshold: 0
    fail_threshold: 0
    threshold_status: provisional
    today: "0 in all 8 counties (the inverse is exact — that is the SILVER-01 finding)"
    implements: [battery:F2, SILVER-01]
    check: |
      -- COALESCE makes the left operand NULL-safe (the battery's bare `Absentee = '1'`
      -- silently skips Absentee-NULL rows — a blind spot on an enforce gate). This widens
      -- the violation space vs the battery's historical 0: re-verify 0 on the first
      -- contract-keyed run; apply the same fix to the battery on the open-item-4 re-key.
      SELECT COUNT(*) FROM {table}
      WHERE (COALESCE(Absentee, '') = '1') <> (OwnerOccupied IS NULL OR OwnerOccupied <> 'Y')

  - id: F3
    rule_version: 1
    title: OwnerType unknown/empty
    section: "§3.6"
    dimension: completeness
    scope: per_county
    mode: shadow
    owner: producer
    reality: true
    measured_as: share with OwnerType NULL/empty/'Unknown'
    alert_threshold: 0.06
    fail_threshold: 0.22
    threshold_status: provisional — wide on purpose; Orange 20.95% is the documented
      outlier (escalation candidate); tightens per county post-escalation
    today: "0.65–20.95%/county (Orange 104,805)"
    implements: battery:F3
    check: |
      SELECT CAST(SUM(CASE WHEN OwnerType IS NULL OR trim(OwnerType) = ''
                            OR OwnerType = 'Unknown' THEN 1 ELSE 0 END) AS DOUBLE)
             / COUNT(*)
      FROM {table}

  # ────────────────────────────── GROUP H — TYPE CONSISTENCY (§3.2/3.3) ──────────────────────────────

  - id: H1
    rule_version: 1
    title: unit number on SFH or Land
    section: "§3.2 / SILVER-05/07"
    dimension: consistency
    scope: per_county_bucket
    mode: shadow
    owner: producer
    reality: true
    measured_as: SFH+Land rows carrying a non-empty SitusUnitNbr, as a share of ALL rows in
      the county — the battery's whole-population denominator the 1.47% baseline and these
      thresholds were computed on (an SFH+Land-only denominator reads Orange 2.15% and
      breaches). Mis-typed condo/multi-unit, bad parse, or SILVER-07 fragment pollution.
    alert_threshold: 0.0125
    fail_threshold: 0.015
    threshold_status: provisional — baseline max Orange 1.47% (whole-county basis)
    today: "0.06–1.47%/county (Orange 7,374 of 500,347)"
    implements: [battery:H1, SILVER-05, SILVER-07]
    check: |
      SELECT CAST(SUM(CASE WHEN SitusUnitNbr IS NOT NULL AND trim(SitusUnitNbr) <> ''
                            AND lower(trim(UseType)) IN ({sfh_bucket}, {land_bucket})
                           THEN 1 ELSE 0 END) AS DOUBLE)
             / COUNT(*)
      FROM {table}

  # ────────────────────────────── GROUP G — PERIOD-OVER-PERIOD (§4) ──────────────────────────────

  - id: G1
    rule_version: 1
    title: YearBuilt mutated across periods (HARD DEFECT — D-4)
    section: "§4 / D-4"
    dimension: consistency
    scope: per_county
    mode: shadow
    owner: producer
    measured_as: persisting PropertyIds (both periods, both YearBuilt non-null) whose
      YearBuilt changed. Target 0 — immutable field (client directive). NOT a reality
      tolerance — this is the contract's one open hard defect.
    alert_threshold: 0
    fail_threshold: 0
    threshold_status: provisional
    mode_note: fails today in every county (10,688 pooled, e.g. 1951→2025) — enforce on
      day one would block all training. Shadow with MANDATORY escalation per alert;
      promote to enforce after the producer fix + 2 green snapshots (§8.1). Open item 6
      correlates mutations vs EffectiveYearBuilt before the fix design.
    today: "0.06–0.67%/county; pooled 10,688 (Maricopa 4,343, Orange 3,319); SFH-only subset 18–376/county"
    implements: battery:G1
    check: |
      SELECT COUNT(*) FROM {table} cur
      JOIN {prev_table} prev USING (PropertyId)
      WHERE TRY_CAST(cur.YearBuilt  AS INTEGER) IS NOT NULL
        AND TRY_CAST(prev.YearBuilt AS INTEGER) IS NOT NULL
        AND TRY_CAST(cur.YearBuilt  AS INTEGER) <> TRY_CAST(prev.YearBuilt AS INTEGER)

  - id: G2
    rule_version: 1
    title: market value crosses the $100 sentinel
    section: "§4 D-3"
    dimension: consistency
    scope: per_county
    mode: shadow
    owner: producer
    reality: true
    measured_as: persisting PropertyIds where (old < $100) XOR (new < $100) — per-period
      sentinel writes around roll-year boundaries
    alert_threshold: 0.011
    fail_threshold: 0.015
    threshold_status: provisional — baseline max Sarasota 1.01% (roll-year advance)
    today: "0.00–1.01%/county (Sarasota 3,080)"
    implements: battery:G2
    check: |
      SELECT CAST(COUNT(*) AS DOUBLE)
             / (SELECT COUNT(*) FROM {table} cur2 JOIN {prev_table} prev2 USING (PropertyId))
      FROM {table} cur
      JOIN {prev_table} prev USING (PropertyId)
      WHERE TRY_CAST(cur.MarketTotalValue  AS DOUBLE) IS NOT NULL
        AND TRY_CAST(prev.MarketTotalValue AS DOUBLE) IS NOT NULL
        AND ((TRY_CAST(cur.MarketTotalValue  AS DOUBLE) < 100)
              <> (TRY_CAST(prev.MarketTotalValue AS DOUBLE) < 100))

  - id: VOL-01
    rule_version: 1
    title: county volume stability
    section: "§4"
    dimension: completeness   # guard: volume
    scope: per_county
    mode: shadow
    owner: producer
    measured_as: row-count drop share vs the previous period; ALERT at >10% drop; FAIL is
      the BOOLEAN collapse condition (prev_count > 0 AND cur_count = 0), not a numeric
      threshold — a numeric 1.0 under direction ≤ would never fire
    alert_threshold: 0.10
    fail_threshold: null   # fail = the boolean collapse condition above
    threshold_status: provisional
    today: "period-over-period persistence 99.8% / county deltas ±1% (3 contados)"
    implements: to_build   # ancestor: contados_silver_assessor.py C1–C3
    procedure: COUNT(*) per (fips, period) vs (fips, prev_period); drop share =
      1 − cur/prev; collapse = prev > 0 AND cur = 0.

  # ────────────────────────────── COVERAGE / FRESHNESS / COHORT (§5) ──────────────────────────────

  - id: FRS-01
    rule_version: 1
    title: snapshot freshness — latest completed month present
    section: "§5"
    dimension: timeliness
    scope: snapshot
    mode: shadow
    owner: producer
    measured_as: lag in months between the run date's last completed month and the newest
      published period partition; nominal lag ≤1 month (standard §7.1)
    alert_threshold: 1
    fail_threshold: 2
    threshold_status: provisional — no SLA exists today; consumer-proposed, pin at kickoff
    today: "unmeasured — today NO timeliness check exists anywhere (gap analysis); a stale pull passes the whole battery"
    implements: to_build
    procedure: max(period) over published partitions per county vs run month.

  - id: COH-01
    rule_version: 1
    title: silver_cohort aligned with silver_rem (SILVER-02)
    section: "§5"
    dimension: consistency   # guard: coverage
    scope: snapshot
    mode: enforce
    owner: producer
    measured_as: violations of (set(cohort.fips) ⊇ {delivery_counties}) AND
      (cohort latest period == rem latest period). Consumer side — coverage builds
      REFUSE on breach (fail-closed; finding 96 pattern).
    alert_threshold: 0
    fail_threshold: 0
    threshold_status: provisional
    mode_note: FAILS TODAY on both legs (cohort missing 12053/12101/12103; 2026-02 vs
      2026-04 — SILVER-02, BLOCK ticket). Enforce is intentional — it blocks COVERAGE
      builds (the artifact SILVER-02 corrupts), not feature/training builds; that is
      the correct fail-closed consequence until the cohort fix lands (§9.3-5).
    today: "both legs failing (SILVER-02)"
    implements: SILVER-02
    procedure: set-compare cohort fips vs {delivery_counties}; compare max(period) of
      both tables per county.

  # ────────────────────────────── DISTRESS BLOCK (§3.7) ──────────────────────────────

  - id: DIS-01
    rule_version: 1
    title: distress value domain
    section: "§3.7"
    dimension: validity
    scope: per_county
    mode: shadow
    owner: producer
    measured_as: across the 23 distress columns — share of values outside the contracted
      domain (castable-to-double OR '' OR 'Unknown' OR NULL). A new token = encoding
      drift = §9.1 breaking-change candidate.
    alert_threshold: 0
    fail_threshold: 0
    threshold_status: provisional — domain unmeasured exhaustively; first run baselines it
    today: "known values: '1', '1.0', '0.8', '', 'Unknown', NULL (standard §7.1 + PFC postmortem)"
    implements: to_build
    procedure: per column c in {distress_cols} — COUNT(*) WHERE c IS NOT NULL AND c NOT IN
      ('', 'Unknown') AND TRY_CAST(c AS DOUBLE) IS NULL; sum across columns.

  - id: DIS-02
    rule_version: 1
    title: distress companion-date sanity
    section: "§3.5/§3.7"
    dimension: validity
    scope: per_county
    mode: dry_run   # unmeasured + fail null = textbook dry_run (gold §9.3-5: nothing
                    # implicit); promote to shadow when the first baseline pins thresholds
    owner: producer
    measured_as: distress date companions that parse to > run date (future) or carry
      2099/2100 sentinels; resolved-state dates additionally ≤ 1980-01-01 flagged
      (consumer reads those as NULL — anti-incident provision)
    alert_threshold: 0
    fail_threshold: null   # baseline first; pin at kickoff
    threshold_status: provisional — unmeasured; first run baselines
    today: "unmeasured"
    implements: to_build
    procedure: per date companion column — future / sentinel / pre-1980 counts.

  # ────────────────────────────── UNIT & BRONZE PARITY ──────────────────────────────

  - id: UNIT-01
    rule_version: 1
    title: SitusUnitNbr fragment pollution (SILVER-07)
    section: "§3.2 / SILVER-07"
    dimension: validity
    scope: per_county
    mode: dry_run    # share unmeasured precisely; baseline first, then threshold
    owner: producer
    measured_as: share of non-empty SitusUnitNbr values matching the 2-char street-type
      fragment denylist ({street_type_tokens}) — parser artifacts, not units
    alert_threshold: null
    fail_threshold: null
    threshold_status: provisional — unmeasured
    today: "pollution confirmed qualitatively (SILVER-07); share unmeasured"
    implements: SILVER-07
    check: |
      SELECT CAST(SUM(CASE WHEN upper(trim(SitusUnitNbr)) IN ({street_type_tokens})
                           THEN 1 ELSE 0 END) AS DOUBLE)
             / NULLIF(SUM(CASE WHEN SitusUnitNbr IS NOT NULL AND trim(SitusUnitNbr) <> ''
                               THEN 1 ELSE 0 END), 0)
      FROM {table}

  - id: BRZ-01
    rule_version: 1
    title: Bronze parity sample (vendor-gap vs ETL-drop decidability)
    section: "§5 / RUL-04"
    dimension: completeness
    scope: sample
    mode: dry_run    # infrastructure: sampling harness over Bronze S3 (read verified 2026-06-09)
    owner: consumer
    measured_as: on a stratified sample (≥200 rows/county), share where Silver
      situs/unit/mailing differ from Bronze Assessor_Annual at the matching vintage —
      separates "Silver dropped it" from "FA never sent it" (USPS-audit pattern —
      0/30 divergent at two vintages)
    alert_threshold: null
    fail_threshold: null
    threshold_status: provisional — expected ≈0 per the USPS precedent; baseline first
    today: "USPS bounce audit: Silver == Bronze on all 30 sampled rows, two vintages"
    implements: usps_bounce_unit_audit_2026-06-09.md
    procedure: sample PropertyIds; fetch same vintage from {bronze_assessor} via S3;
      field-level diff on situs/unit/mailing; report per-stratum.

  # ────────────────────────────── DRIFT (standard §11) ──────────────────────────────

  - id: DRIFT-01
    rule_version: 1
    title: per-gate measured-value drift vs the last 5 keyed runs
    section: "§8.4 / standard §11.2"
    dimension: consistency   # guard: drift
    scope: per_county
    mode: dry_run    # depends on open item 4 (results keyed by run date, not overwritten);
                     # promote to shadow once ≥5 keyed runs exist and per-metric
                     # rate-of-change thresholds are pinned
    owner: consumer
    measured_as: for each watched metric (per-gate measured values — esp. B1 situs-null,
      C3 AVM-sentinel share, F3 OwnerType-unknown, G1 mutation count, pass mix), the
      relative rate-of-change vs the trailing 5 keyed battery runs; "valid but moved"
      warns, never blocks (standard §11.1 — the Brooklyn-drift class a single-period
      gate cannot see)
    alert_threshold: null
    fail_threshold: null
    threshold_status: provisional — per-metric thresholds pinned after 5 keyed runs exist
    today: "unmeasurable — results JSON is overwritten per run today (open item 4)"
    implements: to_build
    procedure: persist each run's per-gate measured values keyed by (run date, period);
      compute relative deltas vs the trailing-5 window; alert on per-metric threshold
      breach. Warn-only by design (standard §11 — drift notifies + flags).