Weekly Expiry Reminder Report — Missing Contracts

Key investigative pivot: If the ES query returns the expected number of contracts but the recipient still reports missing → root cause is EmailConfig routing (cause 1). If ES query itself returns fewer contracts than expected → investigate re-sync lag (cause 2).

Investigation Workflow

Step 1 — Confirm ES query returns expected contracts

Run the backend Elasticsearch query for the workspace on the appropriate cluster. Check:

• Does ES return the number of contracts the customer expects?
• What are the last_re_sync_at values for contracts that are missing?

Known ES index name format: contracts-{timestamp} (Prod IN cluster confirmed: contracts-20250312110313772786).

Key fields to check in the ES source:

• expiring_at — must be populated for the contract to appear
• last_re_sync_at — if recently after the report run date, sync lag is the cause
• created_by_workspace — confirm workspace scope

If ES returns fewer contracts than expected and last_re_sync_at values are after the report date → Cause 2: sync lag (see mitigation below). If ES returns the full expected set → proceed to Step 2.

Step 2 — Audit EmailConfig routing for the missing contracts

Run this query against the affected workspace (in Metabase or via Django Admin):

SELECT
    c.id AS contract_id,
    c.contract_type_id,
    ct.name AS contract_type_name,
    ec.id AS email_config_id,
    ec.to_business_user,
    ec.to_emails,
    CASE
        WHEN ec.id IS NULL THEN 'NO CONFIG'
        WHEN ec.contract_type_id IS NULL THEN 'catch-all config'
        ELSE 'per-type config'
    END AS config_scope
FROM contracts_v3_contractv3 c
JOIN contracts_contracttype ct ON ct.id = c.contract_type_id
LEFT JOIN emails_emailconfig ec
    ON (ec.contract_type_id = c.contract_type_id OR ec.contract_type_id IS NULL)
    AND ec.workspace_id = {wsid}
    AND ec.email_type = 'CONTRACT_EXPIRATION_REPORT'
WHERE c.id IN ({contract_ids})
ORDER BY config_scope, c.id

Replace {contract_ids} with the comma-separated list of missing contract IDs from the customer's report.

Step 3 — Interpret the 3 contract routing buckets

For each contract in the result, apply this decision tree:

Confirmed example (Dvara Holdings, WSID 122251, March 2026):

• 18 contracts sent correctly (arvind IS BU, to_business_user=True)
• 34 not sent (BU = other users; no to_emails entry for arvind)
• 2 not sent (arvind IS BU but to_business_user=False on those 2 contract types; not in to_emails)

Step 4 — Confirm whether missing is WAI or misconfiguration

Ask support/CS: Does the customer expect the recipient to receive all workspace contracts regardless of who the Business User is?

• Yes → Missing EmailConfig. The workspace has not configured a workspace-wide catch-all to_emails recipient. This is a configuration gap, not a code bug. Fix: add the recipient's email to to_emails in the relevant EmailConfigs.
• No → The report is working as intended. Explain the routing logic to the customer.

Mitigation

Cause 1 — Missing EmailConfig routing

Two types of fix depending on the bucket:

For contracts where recipient is NOT the BU (34-contract pattern): Add the recipient email to to_emails field in emails_emailconfig for those contract types.

• Admin URL: https://api.{cluster}.spotdraft.com/admin/emails/emailconfig/?workspace_id={wsid}&email_type=CONTRACT_EXPIRATION_REPORT
• Metabase: Query the emails_emailconfig table filtered by workspace_id={wsid} and email_type='CONTRACT_EXPIRATION_REPORT'

For contracts where recipient IS the BU but to_business_user=False (2-contract pattern): Enable to_business_user=True for those contract types' EmailConfig rows, OR add the recipient to to_emails.

⚠️ Confirm intent with CS before modifying EmailConfig — adding a recipient to to_emails means they will receive expiry reminders for ALL contracts of that type going forward, regardless of BU assignment.

Cause 2 — Elasticsearch sync lag

No active fix needed — the re-sync is self-healing. Verify by checking last_re_sync_at against the report date:

• If last_re_sync_at > report_date for missing contracts → those contracts were not indexed at report time
• Next week's report will include them once re-sync completes

If the customer needs an immediate re-send, an engineer must manually trigger re-sync for the affected contracts or re-run the weekly report for those specific contracts (no self-service tooling exists for this as of March 2026 — escalate to engineering).

Observability gap: Weekly expiry reminder reports are generated in-memory with no backup/logging of which contract IDs were included. There is no way to audit a past report's exact content. Jira tracking: SPD-42457.

EmailConfig Schema

Table: emails_emailconfig

A contract is included in a recipient's report if any of these are true:

• Recipient is the BU and to_business_user=True
• Recipient's email is in to_emails for that contract type's config
• A catch-all config (contract_type_id IS NULL) exists with to_business_user=True and the recipient is the BU, or recipient is in its to_emails

Admin & Diagnostic URLs

Confirming vs Ruling Out

Known Issue Patterns

Pattern 1: Missing EmailConfig for workspace-level recipient (Dvara Holdings — Mar 2026)

Symptom: Recipient receives 18 of 54 expiring contracts. Others go to other BU addresses.

Root cause: No EmailConfig entry adding recipient to to_emails for the 34 contract types where they are not the BU.

Confirmed IDs: WSID 122251, IN cluster. Jira: SPD-42256. Incident: #incident-20260312-high-weekly-expiry-reminder-report-missing-contracts-expiring-

Resolution: This is WAI — explained to customer that EmailConfig must be set up to route to them. Mitigation: add recipient email to to_emails in relevant EmailConfig rows.

Pattern 2: ES sync lag after metadata cleanup (Dvara Holdings — Mar 2026, transient)

Symptom: 6 contracts missing from March 9 report. All 6 had last_re_sync_at values of March 13 — after the report ran.

Trigger: Bulk metadata cleanup (SPD-41927) deleted duplicate Expiry Date key pointer fields and unset is_global flag across 228 contract types. Affected contracts lost their expiring_at ES value temporarily.

Self-healed: Re-sync completed March 13. March 16 report returned all 54 contracts in ES.

Escalation Triggers

• ES returns expected contracts; EmailConfig looks correct; recipient still gets subset → check if email audit shows delivery to a forwarding address or alias that the customer doesn't control
• Customer wants all workspace contracts routed to one recipient permanently → requires adding email to to_emails on every EmailConfig for the workspace (or creating a catch-all config with contract_type_id=NULL)
• Large-scale re-sync lag (many workspaces affected after a platform migration) → escalate to engineering to trigger batch re-sync

Related Incidents & Jira

Connections to Other Skills

• email-delivery-triage — if EmailConfig is correct but emails aren't arriving → check Postmark suppression
• contract-lookup — for workspace and cluster identification
• recurring-reports-post-offboarding — if the issue is recurring scheduled reports continuing after churn/trial expiry (different report type: RecurringReportSchedule, not CONTRACT_EXPIRATION_REPORT)