CUPOLA Update Statements — AutoResponder Pipeline#

Purpose#

Sai Teja (IP4) asked (2026-04-21):

"In case of CUPOLA, if Race can share me what update statements are going to be performed in a simple text file, I just double check with our team and get back to Race with what SPs (if any can be used) be called to update the changes like Make inactive, update Title/Email etc. so that Stored Procedures will take care of other fields to update automatically like Updated on, Updated by etc."

This document is the "simple text file" of every write statement the AutoResponder pipeline can issue against CUPOLA in production. It is grouped by the operation buckets Teja named in his comment. Each statement is shown with its parameter placeholders and the call-site(s) that produce it, so the DBA team can map each one to a stored procedure that would also maintain the auxiliary columns (updated_on, updated_by, audit rows, etc.).

For the currently active update paths (§1-§4), the stored procedures are already wired in code. Keep §5 as the optional/deferred mapping area if IP4 wants stored procedures for replacement auto-add rollback scenarios. The code-side swap area remains local inside src/auto_responder/services/databases/cupola.py.

Operation buckets (Teja's taxonomy)#

Important business rules (already enforced in code):

• "Active-with-org" gate for INACTIVE. A row is only flipped to inactive when the preferred CUPOLA record for that email is currently status = 1 (active). If the preferred record is already inactive we skip the UPDATE and emit a no-op audit row instead of re-writing the status. See action_engine.py::_handle_inactive and IMPLEMENTATION_PLAN §7.2.

• Active-only gate for EMAIL / TITLE updates. The _handle_title_update handler now requires the preferred CUPOLA row to exist and be active before any email or title UPDATE runs (on CUPOLA or on mirrored systems). When the gate fails the entire row is routed to Human Review (HUMAN_REVIEW_REASON_UPDATE_ON_INACTIVE for inactive CUPOLA, HUMAN_REVIEW_REASON_ACTIVE_NEW_CONTACT when the person is not in CUPOLA) and none of the §2/§3 UPDATEs execute.

• No auto-reactivation of CUPOLA. _handle_active never calls update_contact_status(..., True). An inactive CUPOLA row on an ACTIVE determination is routed to Human Review as a "reactivation candidate" (HUMAN_REVIEW_REASON_REACTIVATION_CANDIDATE); Sai Teja / IP4 creates a new entry manually rather than flipping the old one back to active.

• No auto-add on ACTIVE. When an ACTIVE determination has no CUPOLA row, the autoresponder no longer calls add_contact(...). The row lands in output_document_inactive_no_cupola_match.csv + Human Review (HUMAN_REVIEW_REASON_ACTIVE_NEW_CONTACT) for IP4 to resolve.

• Replacement auto-add is OFF by default. By default the pipeline no longer calls add_contact(...) for replacement contacts. They flow to IP4 via output_document_alternate_contacts.csv. The code path in §5 below still exists but is gated behind the env flag CUPOLA_AUTO_ADD_REPLACEMENTS=true. Leave that flag off unless Teja explicitly opts in.

• OUT_OF_OFFICE never writes to CUPOLA. The active-only policy promoted Determination.OUT_OF_OFFICE to a first-class outcome. It is handled by _handle_out_of_office, which writes a Human Review row (HUMAN_REVIEW_REASON_OUT_OF_OFFICE) and performs zero CUPOLA / Hodor / Multipub / Salesforce writes — see §7.

• CUPOLA_AUTOMATIC_UPDATES toggle. When CUPOLA_AUTOMATIC_UPDATES=false (recommendation-only mode), the INACTIVE status change in §1/§4 is collected for audit/notification but not applied. Email/title updates in §2/§3 are still governed by their own handler and CUPOLA gate. When automatic status updates are enabled, the pipeline uses IP4SP_AutoBounce_MarkPersonInactive for the inactive flip.

• Trigger compatibility. All OUTPUT clauses go INTO @table_var because link_org_person, organization, and person all carry enabled triggers and SQL Server raises error 334 on a bare OUTPUT clause.

Per-run artifacts#

The pipeline no longer writes a per-run cupola_planned_updates.sql file. CUPOLA output is limited to the audit files (cupola_audit_log.csv / cupola_audit_log.json) and, when auto-applied status changes need a revert path, cupola_audit_log_rollback_plan.csv. Use this static document as the SP-mapping reference.

1. Make a person Inactive#

Source file: src/auto_responder/services/databases/cupola.py::execute_mark_person_inactive_sp

Trigger: action_engine._handle_inactive when the Cupola record for the sender is is_active == True and the determination is one of inactive, retired, deceased, left_company, unreachable.

EXEC IP4SP_AutoBounce_MarkPersonInactive
    @OrgPersonID = :org_person_id,
    @Result      = :result_text;

Parameters bound from Python.

Currently maintained automatically by this UPDATE. status.

Left to the SP (wish list from Teja's team). status_change_date, reason_comments, updated_on, updated_by. The pipeline already carries these values on the in-memory payload (reason, determination, email_source, previous_status); once the SP is known we bind them as extra parameters.

Stored procedure now used: IP4SP_AutoBounce_MarkPersonInactive

2. Update Title / Position (w.r.t. organization)#

Source file: src/auto_responder/services/databases/cupola.py::execute_org_person_updates_sp

Trigger: action_engine._handle_title_update when the determination is title_update (role change at the same organisation). Only fires when the active-only CUPOLA gate passes (preferred CUPOLA row exists and status = 1); otherwise the row is routed to Human Review and this UPDATE does not run.

EXEC IP4SP_AutoBounce_OrgPersonUpdates
    @OrgPersonID = :org_person_id,
    @Position    = :new_title,
    @Email       = :email,
    @Result      = :result_text;

Stored procedure now used: IP4SP_AutoBounce_OrgPersonUpdates

3. Update Email (w.r.t. organization)#

Source file: src/auto_responder/services/databases/cupola.py::execute_org_person_updates_sp

Trigger: action_engine._handle_title_update (shared handler for email_update + title_update) when the determination is email_update (same person, new address). Only fires when the active-only CUPOLA gate passes — preferred CUPOLA row exists and status = 1. When the gate fails, the change routes to Human Review (HUMAN_REVIEW_REASON_UPDATE_ON_INACTIVE or HUMAN_REVIEW_REASON_ACTIVE_NEW_CONTACT) and this UPDATE does not run.

EXEC IP4SP_AutoBounce_OrgPersonUpdates
    @OrgPersonID = :org_person_id,
    @Position    = :position,
    @Email       = :new_email,
    @Result      = :result_text;

Left to the SP (wish list). email_change_date, previous_email audit row, updated_on, updated_by.

Stored procedure now used: IP4SP_AutoBounce_OrgPersonUpdates

4. Replacement — mark old person inactive#

This is the only CUPOLA write the pipeline issues for a replacement determination under the 2026-04-20 process (see IMPLEMENTATION_PLAN §7.1). The replacement person is sent to IP4 as a CSV row for manual research.

Source file: src/auto_responder/services/databases/cupola.py::execute_mark_person_inactive_sp (same SP call as §1 — invoked against the original sender's row).

EXEC IP4SP_AutoBounce_MarkPersonInactive
    @OrgPersonID = :old_org_person_id,
    @Result      = :result_text;

The matching replacement row (new person, new email, same org) is not written by the pipeline — it's in output_document_alternate_contacts.csv with a cupola_action column set to add or update based on check_contact_exists(new_email).

Stored procedure now used: IP4SP_AutoBounce_MarkPersonInactive

5. Add a new person + link (DEFERRED / OPTIONAL)#

Source file: src/auto_responder/services/databases/cupola.py::add_contact

This path is disabled by default and only runs when CUPOLA_AUTO_ADD_REPLACEMENTS=true. It is kept in the spec for rollback / emergency use if the team explicitly turns replacement auto-add back on.

There are three sub-paths inside add_contact:

5a. Organization lookup / create#

-- Lookup first
SELECT TOP 1 organization_id
  FROM organization
 WHERE name = :org_name;

-- If not found, create
SET NOCOUNT ON;
DECLARE @out TABLE (new_id BIGINT);
INSERT INTO organization (name)
OUTPUT INSERTED.organization_id INTO @out
VALUES (:org_name);
SELECT new_id FROM @out;

5b. Cross-org link (email already exists at a different org)#

SET NOCOUNT ON;
DECLARE @out TABLE (new_id BIGINT);
INSERT INTO link_org_person
       (person_id, organization_id, email, position, status)
OUTPUT INSERTED.org_person_id INTO @out
VALUES (:person_id, :target_org_id, :new_email, :title, 1);
SELECT new_id FROM @out;

5c. New person (email not present anywhere in CUPOLA)#

-- person row
SET NOCOUNT ON;
DECLARE @out TABLE (new_id BIGINT);
INSERT INTO person (first_name, last_name, status)
OUTPUT INSERTED.person_id INTO @out
VALUES (:first_name, :last_name, 1);
SELECT new_id FROM @out;

-- link row (same INSERT as 5b, using the person_id returned above)

Recommended SPs (TO BE FILLED IN BY TEJA'S TEAM):

• Org create: ____________________________________________
• Person create: ____________________________________________
• Link create: ____________________________________________

6. Lookups (reference only — no writes)#

Included so Teja can verify that the SELECT clauses hitting link_org_person, person, and organization agree with his team's expectations.

• lookup_by_email(email) — case/space-insensitive email match with INNER JOIN person (p.status = 1) and INNER JOIN organization. Returns every row for the email so the action engine can pick the right (person, org) pair when the mailbox appears under multiple organizations.

• lookup_by_email_domain(domain) — fallback when direct email lookup misses. Returns up to 50 rows whose link_org_person.email domain equals the supplied value (lower-cased, trimmed). Used for "New Org" detection (IMPLEMENTATION_PLAN §1.2).

• check_active_subscription(org_person_id) — boolean; reads ISNULL(status, 1) = 1.

• check_contact_exists(email) — thin wrapper over lookup_by_email used to pick add vs. update for alternate-contact rows.

• check_organization_exists(org_name) — exact-name TOP 1 lookup.

No writes occur in this bucket.

7. What does not hit CUPOLA#

• ACTIVE determinations. No auto-add and no auto-reactivate. When the person is missing from CUPOLA, or the matched row is inactive, the pipeline records a Human Review row and performs zero CUPOLA writes. Sai Teja / IP4 owns the manual add of a new entry.

• OUT_OF_OFFICE determinations. _handle_out_of_office routes the row to Human Review with HUMAN_REVIEW_REASON_OUT_OF_OFFICE and performs zero CUPOLA / Hodor / Multipub / Salesforce writes. OOO is tracked as its own workflow — no status flip, no title change, no suppression.

• EMAIL_UPDATE / TITLE_UPDATE on inactive / missing CUPOLA. Gated by _handle_title_update; no CUPOLA write occurs — Human Review only.

• Suppression / unsubscribe writes — these go to Multipub and/or SFMC, never to CUPOLA. CUPOLA is the contact ledger only.

• Salesforce updates — see docs/SALESFORCE_UPDATE_OPERATIONS.html. Multipub-sourced Contacts in Salesforce are explicitly skipped by the action engine (marketing team handles those via SFMC).

• Hodor prospect updates — see docs/IMPLEMENTATION_PLAN.html §1 and services/databases/hodor.py. In live mode, INACTIVE handling both:

  • queues a Prospect Import row (Action=status, RequestedStatus=INACTIVE), and

  • calls mark_inactive_by_email_for_autorenewal(email), which runs:

    sql

    UPDATE dbo.[pros]
    SET IsNoLongerWithFirm = 1,
        LastUpdatedDate    = getdate(),
        UpdatedReason      = 'Updated based on AutoRenewal Program'
    WHERE email = ?;

    This preserves the AutoRenewal program semantics while keeping the Prospect Import deliverable for Hodor ops.

8. Review checklist for Teja's team#

1. Confirm §1/§2/§3/§4 stored procedure names and parameter signatures match DBA expectations.

2. Flag any column that the SP would populate automatically (e.g. updated_on, updated_by, status_change_date) so we know not to pass them as explicit parameters.

3. If the SP requires inputs that are not already bound in the Python call site (e.g. a session user ID), note them so we can source them from config / the audit context.

4. Reply on Assembla #374 or directly to rsaunders with the filled doc.

Once returned, the swap is a small edit: replace the in-place UPDATEs with cursor.execute("EXEC :sp_name ?, ?, ?, ...") calls and drop any columns the SP now handles for us.