Runbook: Stranded order email recovery

The stranded-order sweep (detect_stranded_failed_orders in apps/temporal-worker/activities/missed_order_reconciliation.py, flag stranded_order_detection_v1) pages when triage classified an email as an order but no order was saved. Two cases, both named in the alert:

• extraction_failed: email_events.processing_status = 'failed'. Extract, match or save raised.
• stuck_processing: the row has sat in processing for over 2 hours. The run was cancelled or hit its 90-minute ceiling.

Each alert line carries the email_events.id. The alert fires once per email and lists at most 10; an outage can strand more.

1. Look up the rows

List every stranded email from the last day that still has no order (read-only, production database):

select m.email_event_id, m.recon_decision, m.run_at, e.clerk_organization_id, e.subject
from missed_order_candidates m
join email_events e on e.id = m.email_event_id
where m.recon_decision in ('extraction_failed', 'stuck_processing')
  and m.run_at > now() - interval '1 day'
  and e.extracted_order_id is null
order by m.run_at desc;

Then pull what recovery needs for those ids:

select id, clerk_organization_id, provider, message_id, email_pull_connection_id,
       processing_status, processing_error, extracted_order_id, updated_at,
       "to"::text, subject
from email_events
where id in ('<email_event_id>', '<email_event_id>');

Skip a row that now has extracted_order_id set. It recovered on its own.

2. Check whether a CSR already entered it

A human may have keyed the order into the ERP from the mailbox while ours failed. Search the ERP for the PO number in the subject or attachment. If it’s there, stop; resuming would put finished work back in the CSR’s queue.

3. Resume the email

Resume re-runs EmailProcessingWorkflow from the stored message_id. It re-fetches the message and attachments from the mailbox and re-extracts. The save layer dedups on (provider, message_id, clerk_organization_id), so it cannot create a duplicate order.

It only works for pull-path mail (email_pull_connection_id set, provider gmail or microsoft). Don’t resume a processing row younger than 90 minutes; it may still be running.

If email_resume_recovery_v1 is enabled for the environment, a signed-in member of the email’s org can call POST /api/emails/<email_event_id>/resume. The route applies all the checks above. It evaluates the flag without an org identity, so an org segment override does not turn it on; only the environment default does.

Otherwise, start the same workflow the route starts, on the production Temporal VM:

gcloud compute ssh erp-prod-temporal --project=ordermatic-prod --zone=us-east1-b --tunnel-through-iap

sudo docker exec temporal-server temporal workflow start \
  --address localhost:7233 \
  --task-queue email-processing \
  --type EmailProcessingWorkflow \
  --workflow-id "email-resume-<email_pull_connection_id>-<message_id>" \
  --id-reuse-policy AllowDuplicateFailedOnly \
  --input '{"provider": "<provider>", "message_id": "<message_id>", "email_address": "<first address in to>", "connection_id": "<email_pull_connection_id>"}'

The workflow id matches resumeEmailProcessingWorkflow in apps/webapp/src/lib/temporal.ts, so the route and a manual start dedup against each other. If you pass --execution-timeout, give it a unit (600s); a bare number rejects the start.

4. Verify

Re-run the query from step 1. A recovered row shows processing_status = 'completed' and an extracted_order_id, and the order appears in the org’s inbox. If it fails again, read the new processing_error before retrying. The same error twice is a bug, not a transient failure.