Lesson 190: Red-Team Finding Bind Reconciliation Nightly and Orphan Ticket Sweep (2026)

Direct answer: Lesson 187 created binds and manifests. Lesson 190 runs a nightly reconciliation proving no promoted blocker/major finding lacks a ticket and no bind row points at a missing or demoted finding—exporting BIND_RECONCILE_RECEIPT.json and fail-closing publish on red_team_bind_drift_open until resolved.

Why this matters now (mid-2026 standing intake question)

After Lesson 187 shipped, partner intake replays added a fixed checklist item:

“Show nightly proof that AI red-team findings and deficiency tickets stay in 1:1 promoted bind parity for the active publish_tuple_hash.”

One-time manifest export is not enough—binds drift when:

• Humans demote a finding but leave the ticket open.
• Tickets close while the bind row remains.
• A second promoted finding reuses an old deficiency_ticket_id without a new bind row.
• Lesson 183 CSV exports reference source_red_team_finding_id that no longer exists.

Nightly sweep + receipt closes the gap.

Lesson objectives

You will implement:

• red_team_bind_reconciliation_run — append-only audit table
• Three orphan detectors (unbound promoted, stale bind, ticket without finding)
• BIND_RECONCILE_RECEIPT.json nightly artifact
• Publish-gate red_team_bind_drift_open (extends Lesson 187 gates)
• CI job that fails on non-zero orphan counts

Prerequisites

• Lesson 187 — red_team_finding_deficiency_bind, bind manifest
• Lesson 180 — red_team_finding, promotion statuses
• Lesson 183 — optional CSV source_red_team_finding_id column
• Lesson 171 — active publish_tuple_hash

red_team_bind_reconciliation_run

CREATE TABLE red_team_bind_reconciliation_run (
  run_id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  publish_tuple_hash        TEXT NOT NULL,
  run_started_at_utc        TIMESTAMPTZ NOT NULL DEFAULT now(),
  run_finished_at_utc       TIMESTAMPTZ,
  unbound_promoted_count    INT NOT NULL DEFAULT 0,
  stale_bind_count          INT NOT NULL DEFAULT 0,
  orphan_ticket_bind_count  INT NOT NULL DEFAULT 0,
  status                    TEXT NOT NULL DEFAULT 'running'
    CHECK (status IN ('running','pass','fail')),
  receipt_sha256            TEXT,
  UNIQUE (publish_tuple_hash, run_started_at_utc::date)
);

Pass rule: all three counts = 0. Any non-zero → status = 'fail' and publish gate engages.

Orphan detector queries

A — Unbound promoted findings

SELECT f.finding_id
FROM red_team_finding f
JOIN governance_packet_red_team_run r USING (red_team_run_id)
LEFT JOIN red_team_finding_deficiency_bind b USING (finding_id)
WHERE r.publish_tuple_hash = :tuple
  AND f.promotion_status = 'promoted'
  AND f.severity IN ('blocker', 'major')
  AND b.bind_id IS NULL;

Same predicate as Lesson 187 gate—this job proves it stays zero every night.

B — Stale binds (finding demoted or deleted)

SELECT b.bind_id
FROM red_team_finding_deficiency_bind b
LEFT JOIN red_team_finding f USING (finding_id)
WHERE b.publish_tuple_hash = :tuple
  AND (f.finding_id IS NULL OR f.promotion_status <> 'promoted');

C — Orphan ticket bind (ticket closed, bind remains)

SELECT b.bind_id
FROM red_team_finding_deficiency_bind b
JOIN mock_audit_deficiency_ticket t USING (deficiency_ticket_id)
WHERE b.publish_tuple_hash = :tuple
  AND t.status IN ('resolved', 'withdrawn', 'carved_out');

Optional policy: auto-archive bind when ticket closes—only if signer ack documents the rule.

BIND_RECONCILE_RECEIPT.json

{
  "schema": "red_team_bind_reconcile_receipt_v1",
  "publish_tuple_hash": "c4e8…",
  "run_id": "9a01…",
  "run_finished_at_utc": "2026-11-04T02:15:00Z",
  "counts": {
    "unbound_promoted": 0,
    "stale_bind": 0,
    "orphan_ticket_bind": 0
  },
  "status": "pass",
  "receipt_sha256": "…",
  "sample_bind_manifest_sha256": "…"
}

Store under release-evidence/05-operations/red-team-reconcile/.

Publish gate extension

-- block_reason = 'red_team_bind_drift_open'
WHEN EXISTS (
  SELECT 1 FROM red_team_bind_reconciliation_run r
  WHERE r.publish_tuple_hash = :active_tuple
    AND r.status = 'fail'
    AND r.run_started_at_utc > now() - interval '36 hours'
);

Pairs with Lesson 187 red_team_unbound_promoted_finding—reconciliation catches drift after bind day.

Clears when a subsequent run passes and receipt SHA is pinned in BUILD_RECEIPT.json.

Nightly job sketch

def nightly_bind_reconcile(tuple_hash: str) -> None:
    run_id = insert_run(tuple_hash)
    a = count_unbound_promoted(tuple_hash)
    b = count_stale_binds(tuple_hash)
    c = count_orphan_ticket_binds(tuple_hash)
    status = "pass" if (a, b, c) == (0, 0, 0) else "fail"
    receipt = build_receipt(run_id, a, b, c, status)
    finish_run(run_id, a, b, c, status, sha256(receipt))
    if status == "fail":
        page_oncall("red_team_bind_drift_open")

Schedule 02:00 UTC after trend-board refresh (Lesson 173) and before business hours.

Common mistakes

• Running weekly “when we remember” — fails standing intake question.
• Fixing orphans by deleting tickets instead of rebinding — audit trail breaks.
• Ignoring stale binds after demotion — gate flips next night anyway.
• Receipt without sample_bind_manifest_sha256 — partner cannot correlate manifest version.
• Reconcile against wrong publish_tuple_hash — false pass.

Troubleshooting

Verification checklist

• [ ] Deliberately leave one unbound promoted → nightly fail
• [ ] Fix bind → next night pass clears gate
• [ ] Receipt SHA-256 in CI matches file on disk
• [ ] Lesson 183 export IDs all resolve in detector A
• [ ] Manifest hash in receipt matches Lesson 187 export

Mini exercise (20 minutes)

1. Promote two findings; bind only one.
2. Run reconcile job manually.
3. Capture BIND_RECONCILE_RECEIPT.json with unbound_promoted: 1.
4. Insert missing bind.
5. Re-run; confirm pass and gate clearance.

Continuity

• Lesson 187 — bind creation and manifest.
• Lesson 180 — promotion lane.
• Lesson 189 — year-end packet includes bind manifest member.
• Next: Lesson 191 — panel attendance quorum crosswalk.
• Help: OpenXR governance rollup mismatch — SLA snapshot discipline adjacent to bind drift.

FAQ

Do minor findings need nightly binds?
No—only promoted blocker/major per Lesson 187 gate. Log minors in annex without bind.

Can reconciliation auto-create tickets?
Discouraged. Auto-stub only with human ack same day; otherwise intake sees “machine opened ticket” without tabletop context.

What if we skip a night during holiday?
36-hour gate window allows one missed cron; document skipped_run_reason in ops log for partner ask.

Does this replace manifest export?
No. Manifest is point-in-time; receipt is continuous parity proof.

Mid-2026 partners trust AI assistance when nightly receipts show bind parity held. Run the sweep, pin the SHA-256, and treat orphan counts like production incidents—not backlog trivia.