PO-Level Cross-Thread Linking — Layer 2

Status: Draft — reviewed (CEO + Eng, dual-voice: a fresh Claude subagent and Codex per phase, each independently against the actual code) Author: Claude (AI pair), prompted by David Boone Date: 2026-07-22 Triggered by: docs/designs/email-threading.md §2 (Layer 1, PR #2519) — which named this “the actual root-cause fix” for PR #2514 (VMS silent order-loss incident, merged) and recommended scheduling it as the next doc, not an indefinite someday.

Review summary (2026-07-22)

The first draft of this doc proposed one big structural fix (email_po_references, a new join table wired into ingest) as the whole answer. Two independent review voices — a fresh Claude subagent and Codex, neither seeing the other’s output — converged on the same critique unprompted, and it survived direct verification against the code:

The draft didn’t touch its own stated blocking gate. §1’s “Gap 2” was that _unverified_po_override only fires when _agent_enabled(connection_id) or _agent_shadow_enabled(connection_id) is true (verified: activities/gemini.py:2413-2420 — the entire _edi_hold_override/_unverified_po_override chain is unreachable otherwise). The first draft then explicitly scoped out touching that gate (“stays exactly as-is”), while still implying the new table addressed all three named gaps. It didn’t — a write-only side table changes zero live triage decisions. Gap 2 is now called out as explicitly not solved by this doc (see §1), because closing it is a Flagsmith rollout decision (widening email_triage_agent_v1’s targeting), not a schema change, and conflating the two would blur who owns that call.
A much cheaper fix already exists for the sharpest pain (Gap 3). missed_order_reconciliation already computes PO tokens per candidate (_po_tokens_from_subject, reused in its own prompt-builder) and already persists every evaluated email — verified directly: _persist_candidates(evaluated, run_start) at missed_order_reconciliation.py:336 is called with the full evaluated list (both process and skip decisions), not the process-filtered candidates list used only for the Slack alert. The first draft’s own characterization of missed_order_candidates as “reconciliation-flagged” was imprecise — it undersold how close a cheap fix already sits. Adding a po_tokens column there and populating it from tokens the code already computes is a same-week change with a real, existing consumer (the /missed-orders review UI), versus a new table whose only planned consumer (§5’s anti-join) was left for an undecided later phase.
The jsonb-rejection reasoning was factually wrong (caught by the Claude subagent): the first draft rejected a jsonb array on email_events because it “loses the B-tree index the query needs” — but Postgres GIN indexes jsonb arrays for containment (jsonb_path_ops), so that specific argument doesn’t hold. Not a reason to switch to jsonb; a reason to fix the stated reasoning (see §3).
“Root-cause fix” oversells the scope (both voices, independently matches this repo’s own CK triage-loss audit): subject-line-only extraction is the right shape for VMS’s EDI gateway (PO lives in the subject), but Cleveland Kitchen’s real losses are “diffuse, not VMS-shaped” — plain-language reorders with no PO token in the subject at all. This doc closes the VMS-EDI-shaped gap, not CK’s.

A second, Eng-focused round reviewed the revision produced from the CEO round above — same dual-voice setup, both sides again independently verifying against the code. Both voices converged, again unprompted, on three more corrections, since folded into the sections below:

Tier 1’s “reuse the already-computed tokens” claim was false. _po_tokens_from_subject’s output is trapped inside _po_on_file_note (missed_order_reconciliation.py:88-112), which returns a formatted prompt string, not the token list — nothing surfaces it to the evaluated dict _persist_candidates is called with. Tier 1 needs a second, cheap regex call, not a free byproduct of the first (§3).
Tier 2’s ON CONFLICT ... DO NOTHING silently dropped the higher-confidence source when a subject_regex row inserted before the matching extracted_order row for the same email+PO — contradicting §4’s own “highest-confidence signal” claim about extracted_order. Fixed to DO UPDATE with explicit source precedence (§4).
§5’s anti-join implied an equi-join, but po_numbers_on_file is a bidirectional substring match, not an equality lookup — an equi-join on po_number_normalized would silently miss the exact cases (e.g. subject "604100" vs. stored "604100-1") the live override was built to catch. §4/§5 now say plainly that a semantically-correct anti-join is not a cheap indexed equi-join, just cheaper than an LLM call.

Ironically, the first of these three is close kin to the jsonb-reasoning error the CEO round caught in the original draft: right conclusion (ship the cheap column), wrong mechanism claimed for getting there. Worth naming since it’s the second time in one doc’s review history — a pattern worth double-checking for in whatever comes after this doc, not just this one.

This revision keeps the underlying idea — persist PO references instead of computing and discarding them — but splits it into two tiers instead of one monolithic table: a small, same-week fix that closes the concrete gap with a real consumer today (§3 Tier 1), and the fuller cross-thread join, still believed to be the right eventual answer for “all messages about PO X,” but explicitly framed as a larger, later investment gated on §6’s consumer question plus a direct volume check (§3 Tier 2). No code citation in the reviewed draft was found to be fabricated — both voices verified every function, line reference, and table name against the actual repo.

0. What “Layer 2” means, precisely

From email-threading.md §2:

Layer 2 — PO/order linking. A business-level concept: “these emails are about the same purchase order,” which may span multiple Layer-1 threads (VMS’s EDI gateway) or may be a subset of one long Layer-1 thread that also discusses other POs.

Layer 1 (shipped) groups messages by the mail provider’s own conversation identity — purely mechanical, no business logic. Layer 2 is different: it groups messages by a PO number, a business identifier that can appear across many unrelated mail threads (VMS’s threadless EDI-gateway emails, one per PO change, sharing no conversationId) or repeatedly within one long thread that drifts across several POs (Cleveland Kitchen’s real OPOR WK 28 thread, cited in email-threading.md §0, which touches multiple distinct PO numbers as the conversation evolves over five days).

This doc designs the data model and detection mechanism for that grouping. It does not touch UI (see §7).

1. Problem, verified against the code as it exists today

email-threading.md framed Layer 2 as “the actual #2514 root-cause fix,” but #2514 has already merged and fixed two specific bugs. Before designing anything new, it’s worth being precise about what’s fixed, what’s still missing, what this doc actually closes, and what it explicitly does not.

What #2514 fixed (both already live):

_agent_enabled/_agent_shadow_enabled (activities/gemini.py) now send a connection_id trait so the conn_vms_e4 Flagsmith segment override can actually fire — previously it never could, on any connection.
missed_order_reconciliation (activities/missed_order_reconciliation.py) no longer excludes processing_status='completed' rows, so live-triage skip decisions (which are always stamped completed) are no longer silently invisible to the nightly sweep.

What already existed before #2514, and still runs today, now unblocked:

_unverified_po_override (activities/gemini.py:2011) flips a skip decision to process when the email’s subject references a PO number (via _po_tokens_from_subject, gemini.py:1996 — a bounded regex over the first 512 chars of the subject) that doesn’t match anything in extracted_orders for the org (via po_numbers_on_file, packages/pdf-shared/pdf_shared/crud.py:304 — bidirectional substring match: subject "PO 604100" matches stored "604100-1"; stored "G273107-01" matches token "G273107"). The whole override chain (_edi_hold_override and _unverified_po_override together, gemini.py:2413-2420) only runs when _agent_enabled(connection_id) or _agent_shadow_enabled(connection_id) — false on any connection without the flag.
The nightly missed_order_reconciliation workflow re-triages the last 24h of skipped/filtered emails with a more permissive prompt that also injects a “PO not found in the order system” hint from the same po_numbers_on_file check, and persists every evaluated email (process and skip decisions alike) to missed_order_candidates (missed_order_reconciliation.py:336), Slack-alerting #data-quality-alerts only on process verdicts.

Two gaps remain that this doc actually addresses; one gap it explicitly does not:

PO-token extraction is computed and thrown away, never persisted — this doc’s actual target. _po_tokens_from_subject runs inside _unverified_po_override and inside the reconciliation prompt-builder, but its output isn’t written anywhere durable. There is no query today that answers “show every email that has ever referenced PO 12267” — even though the tokens were computed, at some point, for most of them.
The nightly sweep catches candidates but can’t say which PO they’re about — this doc’s other actual target. missed_order_candidates (packages/db/src/schema/missed-order-candidates.ts) has no PO-number column at all — just subject/sender/reasoning text. A human still has to re-read the subject to know which PO an alert is about, even though the reconciliation workflow already computed it moments earlier. Per #2514’s own PR description, this sweep is also a real, previously-observed single point of failure: “the reconciliation schedule itself hadn’t produced a run since 2026-06-30 despite its flag being on.”
The live override only fires under a narrow gate — NOT addressed by this doc. _unverified_po_override requires, simultaneously: decision is skip; category isn’t other; the email has an attachment; the subject isn’t a Re:/Fwd: reply; and — via thread_has_prior_email (pdf_shared/crud.py:368) — no prior email exists in the thread, scoped by the ERP connection_id (that function’s param resolves org through erp_connections — the same id-space split email-threading.md §3 flagged for alias.get("connection_id"), the root class of bug behind #2514 itself). All of this is additionally gated behind email_triage_agent_v1/_agent_traits() per connection. Widening this is a Flagsmith rollout decision — whether and how far to expand email_triage_agent_v1’s targeting — owned by whoever runs that rollout, not something a data-model doc should decide by fiat. Named here so it isn’t lost, not solved here.

None of this is a criticism of #2514 — that PR fixed exactly the two bugs it found, correctly scoped. Gaps 1 and 2 above are what’s left once the Flagsmith-trait fix is applied everywhere; gap 3 is a separate, orthogonal decision this doc deliberately doesn’t make.

2. Scope

In scope: persisting PO-token extraction that’s already computed today and currently discarded, in two tiers ordered by cost and payoff (§3): a small addition to the existing reconciliation candidate table (Tier 1, closes gap 2 above with a real consumer this week), and a fuller cross-thread reference table (Tier 2, closes gap 1 above, gated on Tier 1’s data justifying the investment).

Out of scope:

Widening _unverified_po_override’s gate or email_triage_agent_v1’s rollout (gap 3, §1). A Flagsmith targeting decision, not a schema change — explicitly not this doc’s call to make.
Removing or replacing _unverified_po_override / missed_order_reconciliation. Both stay exactly as-is. This doc makes their existing PO-token computation a persisted, reusable signal instead of a value computed once and discarded — it does not change their triage-flipping behavior.
UI. A Layer-2 UI surface (e.g. “also referenced in these N other messages,” likely folded into email-threading.md §4’s existing Thread context section rather than a new one) is deferred to its own pass once Tier 2 ships in shadow — same discipline email-threading.md used for its own Phase 2.
Reconciling the two different PO-normalization implementations that already exist — po_numbers_on_file’s bidirectional-substring match (uppercase, strip to [A-Z0-9\-]) versus normId() (apps/webapp/src/lib/revision/engine.ts:38 — uppercase, strip all non-alphanumerics including hyphens, used for order-revision matching). Flagged as an open question (§6), not fixed here.
Body-text or LLM-based PO extraction. Only subject-line regex (reusing _po_tokens_from_subject exactly as it exists), matching its current scope and the VMS-EDI shape it was built for. Explicitly does not cover Cleveland Kitchen’s diffuse, non-subject-PO losses — see the Review summary above. A body-text extraction pass is a real, separate follow-up for that gap, not attempted here.
Renaming thread_has_prior_email’s connection_id param for clarity, despite it being the same class of ambiguity email-threading.md §3 named for alias.get("connection_id"). Flagged (§6), not fixed here.

3. Data model — two tiers

Tier 1 (recommended first ship): `po_tokens` on `missed_order_candidates`

Drizzle schema addition (packages/db/src/schema/missed-order-candidates.ts), generated migration per this repo’s rule — no hand-written SQL:

poTokens: text('po_tokens').array(),  // normalized tokens from _po_tokens_from_subject

Correction from an earlier version of this section: it’s not a pure “pass an already-computed value through.” _po_tokens_from_subject’s output today is trapped inside _po_on_file_note (missed_order_reconciliation.py:88-112), which returns a formatted prompt string, not the token list — the tokens never reach the evaluated dict (missed_order_reconciliation.py:296-312) that _persist_candidates is called with. The minimal fix is to call _po_tokens_from_subject(row["subject"]) a second time in the same loop that builds evaluated, and add its result to that dict — cheap (it’s a bounded regex, not an LLM call), but a genuine second call, not a free byproduct of the existing one. (Refactoring _po_on_file_note to return {tokens, note} instead and have the caller build the prompt string is the cleaner alternative if avoiding the double regex call matters; either way is a small diff, just not a zero-cost one.)

This directly closes gap 2 (§1): the /missed-orders review surface and any Slack alert can show the actual PO number(s) instead of forcing a human to re-read the subject. Real consumer, ships this week, small diff — the ON CONFLICT (email_event_id) DO UPDATE upsert _persist_candidates already runs (missed_order_reconciliation.py:505) just needs po_tokens added to the column list and the SET clause.

Why not the full Tier 2 table for this: missed_order_candidates is already the right table for “reconciliation evaluated this email” — adding one column there is strictly additive and has zero new moving parts (no new FK, no new index strategy, no wiring into the three save activities). It doesn’t generalize to “all messages about PO X” (§5’s cross-thread query), but gap 2 doesn’t need that generalization — it needs the PO number visible where a human is already looking.

Tier 2 (later, gated on Tier 1 data): `email_po_references`

A join table, not a column on email_events:

email_po_references
  id                     uuid pk
  clerk_organization_id  text not null
  email_event_id         uuid not null references email_events(id)
  po_number_raw          text not null   -- token as extracted, uppercased
  po_number_normalized   text not null   -- canonical form used for matching (see §4)
  source                 text not null   -- 'subject_regex' | 'extracted_order'
  created_at             timestamptz not null default now()

  unique (email_event_id, po_number_normalized)
  index (clerk_organization_id, po_number_normalized)  -- "all emails about PO X"

Why a join table, not a nullable column, and not jsonb: an email can reference more than one PO — the reverse of email-threading.md’s own “OPOR WK 28” example (one thread spanning many POs over time) is one message referencing several POs at once (e.g. a single shortage notice covering two open orders). A plain column can’t hold N>1 cleanly. A jsonb array on email_events could be made queryable — Postgres GIN-indexes jsonb via jsonb_path_ops for exactly this containment case, so “loses the index” is not the real reason to avoid it. The real reason is the same one email-threading.md §3 gave for Layer 1 threads: a real table is a real join target for Tier 2’s own future consumers (the anti-join in §5, and eventually a UI join), without a second denormalized copy of the same data drifting out of sync.

Why this is Tier 2, not the first ship: its only named consumer (the anti-join in §5) has no committed home yet — no alert channel, no UI, no owner decided (§6). Building the full cross-thread index before something reads it is exactly the “write-only shadow table nobody queries” risk both review voices flagged. Ship Tier 1 first; revisit Tier 2 once §6’s consumer question is answered and a direct check against email_events (not Tier 1’s data — see §6’s correction on why that can’t show this) confirms real multi-PO-per-message volume.

Population rule, when built — at ingest, not gated behind email_triage_agent_v1: every email gets its subject scanned, regardless of triage decision, category, attachment presence, or which connection it came through. Same “populated at ingest, not at triage” principle email-threading.md §3 used for Layer 1 threading, for the same reason: a skip-decided email referencing an unentered PO is exactly the #2514 failure shape, and gating detection behind a flag was the root of that incident once already.

4. Extraction and matching strategy

Tier 1: per §3’s correction, this is a second _po_tokens_from_subject call in the same loop that builds evaluated (missed_order_reconciliation.py) — not a pass-through of the call already buried inside _po_on_file_note. Still no LLM cost, still a small diff; just an honest one about what it costs.

Tier 2, when built — where it runs: inside the same three save activities Layer 1 already touches — save_email_order, save_email_rfq, save_skipped_email in activities/database.py — right alongside the _maybe_thread_email call Layer 1 added. Ingest-time, per-message, no LLM call.

Tier 2, what it does per email:

Run _po_tokens_from_subject(email.subject) — reused as-is from gemini.py:1996, not reimplemented. Already tested against catastrophic-backtracking subject payloads (_PO_TOKEN_PATTERN’s comment documents a measured 6.7s pathological case that was fixed).
For process-classified emails, additionally record extracted_orders.po_number (once extraction has produced it) as a source='extracted_order' row — the highest-confidence signal, already normalized by whatever produced the extraction.
Normalize every token (uppercase, strip to [A-Z0-9\-]) before insert. Correction: po_numbers_on_file is not itself a reusable normalizer — it filters (rejects tokens that don’t already match _PO_TOKEN_CHARSET, pdf_shared/crud.py:329-335), it doesn’t have a standalone strip-and-uppercase function to import. This step needs a small new helper matching that same charset rule, not a reused function call — still a third normalization variant alongside the two named in §6, just one that has to be written rather than imported.
Insert into email_po_references, ON CONFLICT (email_event_id, po_number_normalized) DO UPDATE SET source = CASE WHEN EXCLUDED.source = 'extracted_order' THEN 'extracted_order' ELSE email_po_references.source END — idempotent against Temporal’s at-least-once retries (a plain DO NOTHING was the first draft of this rule, but that silently drops the higher-confidence source if a subject_regex row for the same email+PO happens to insert first and a later extracted_order row for the same pair no-ops against it instead of upgrading it — this DO UPDATE makes extracted_order win regardless of insert order, never downgrades an existing extracted_order row back to subject_regex).

Detection (“is this PO actually on file”): Tier 1 and Tier 2 solve different halves of this, and they are NOT the same query shape — important correction from an earlier version of this section, which implied a single reusable “normalized match.”

Tier 1 doesn’t need a join at all: it stores tokens next to a candidate a human is already looking at, no matching required.
Tier 2’s future anti-join (§5) does need matching, and po_numbers_on_file (pdf_shared/crud.py:304) is NOT an equality lookup on a normalized value — it’s a bidirectional substring match (ILIKE/position()) specifically so subject token "604100" matches stored "604100-1" and vice versa. A plain email_po_references.po_number_normalized = extracted_orders.po_number equi-join is a different, weaker semantic that would miss exactly the cases po_numbers_on_file was built to catch. Any real anti-join has to either reuse po_numbers_on_file’s actual substring logic as the join predicate (same cost class as what already exists — a scan, not a cheap indexed equi-join) or accept and document that it’s intentionally a stricter, faster first-pass filter with more false “on file” negatives than the live override. §5 is corrected to say this plainly instead of calling it simply “cheap.”

5. What Tier 2 structurally enables (once built)

An anti-join with no LLM cost, runnable far more often than nightly — but, per §4’s correction, not a trivially cheap indexed equi-join: to match _unverified_po_override’s actual “is this PO on file” semantics it has to run the same bidirectional-substring logic po_numbers_on_file does, not a plain = on po_number_normalized. Still cheaper than an LLM call per candidate (missed_order_reconciliation’s cost today), just not free — surfaces unentered POs without waiting for _unverified_po_override’s narrow gate or the next missed_order_reconciliation run. No committed destination for this yet (§6) — that’s exactly why Tier 2 waits.
“All messages about PO 12267” — the literal use case email-threading.md §4 named and deferred (“Layer 2… may span multiple Layer-1 threads… or may be a subset of one long Layer-1 thread”) — becomes SELECT * FROM email_po_references WHERE po_number_normalized = ..., correctly spanning both shapes named in that doc’s §0: VMS’s threadless-EDI case (many threads, one PO) and CK’s OPOR WK 28 case (one thread, many POs).

6. Open questions

Normalization divergence. po_numbers_on_file (bidirectional substring, hyphen-preserving) and normId() (strict alphanumeric-only strip, engine.ts:38) solve different problems today — “is this PO known at all” versus “is this the exact same PO for a revision match.” Should stay separate, or should one be reconsidered? Needs its own look; not blocking either tier, both of which reuse po_numbers_on_file’s rule as-is.
Tier 2’s consumer. Where does the anti-join’s output land, once built — reuse missed_order_reconciliation’s #data-quality-alerts Slack channel? A new work_items flag? Not decided; this is the concrete gate on starting Tier 2 at all, not a detail to fill in later.
Multi-PO-per-message frequency, and why Tier 1 can’t answer it. How common is one email referencing N>1 POs, versus the “one long thread touches many POs over time” shape email-threading.md already confirmed? Correction: Tier 1’s po_tokens data can NOT settle this. It only covers the narrow missed_order_reconciliation candidate population — a 24h lookback, pre-filtered by NOT EXISTS against already-persisted candidates (missed_order_reconciliation.py:262-265) — not the full email population Tier 2 would need to cover. §7’s “gate Tier 2 on Tier 1 data” is therefore a qualitative signal (does the narrow slice show any multi-PO messages at all) rather than a real volume estimate; a real answer needs a small one-off query against email_events subjects directly, not waiting on Tier 1 to accumulate data it structurally can’t provide.
thread_has_prior_email’s connection_id param resolves org via erp_connections, not email_pull_connections — the same id-space split email-threading.md §3 named as a real, precedented risk (PR #2514). Worth a rename for clarity in a follow-up; not renamed here.
The CK-POC-only poRouting.poNumber prior art (apps/webapp/src/pages/api/webhook/inbound-email.ts:242, stored in email_events.provider_metadata.poRouting) is a narrower, webhook-path-only precedent for storing a PO signal adjacent to email_events. Correction: the write is gated server-side by processPOInboundEmail() returning null for non-CK orgs, not by isCKPOCEnabled — that’s a separate, client-side nanostore atom controlling badge rendering in the webapp UI, not the write path. CK-only in practice either way; likely superseded by Tier 2 once it ships, not migrated or removed here.
Gap 3’s Flagsmith rollout (§1) — whether and how to widen email_triage_agent_v1’s targeting so _unverified_po_override reaches more than VMS. Explicitly out of scope for this doc (§2); named so it isn’t lost as a real, unclosed gap.

7. Rollout

Tier 1 (ship first). po_tokens column on missed_order_candidates
- populate from the reconciliation workflow’s existing token computation. No new table, no new wiring, real consumer (/missed-orders review UI, Slack alert) day one.
Phase A (Tier 2, shadow, backend only — gated, not a default next step). email_po_references table + extraction wired into the three save activities. No behavior change, no alerting, no UI. Only start once §6’s consumer question has an answer AND a real multi-PO-per-message volume check has run — per §6’s correction, that check is a small one-off query against email_events subjects directly, not something Tier 1’s data can show on its own.
Phase B (Tier 2, detection surfaced). Decide where the anti-join’s output goes (§6) once Phase A’s shadow data shows real volume.
Phase C (Tier 2, UI). “Referenced POs” — most likely folded into email-threading.md §4’s existing Thread context section as a cross-thread element, rather than a new top-level surface. Not designed here.