Cupola (SQL Server)#

Purpose: IPv4 "org / person" system of record for lookups and selective writes. Status changes are recommended to operations unless CUPOLA_AUTOMATIC_UPDATES=true. Per the active-only automation policy CUPOLA is the gate for every automated write — email / title updates on Hodor / Salesforce only fire when an active CUPOLA row exists, and the autoresponder never auto-adds new CUPOLA rows or auto-reactivates inactive ones (those cases route to Sai Teja / IP4 via the Human Review digest).

Code: src/auto_responder/services/databases/cupola.py

Configuration#

Setting	Notes
`CUPOLA_CONNECTION_STRING` or `cupola_connection_string`	Full MSSQL connection string.
`cupolaConfig` (JSON)	Alternative: server, database, user, password — see `get_cupola_connection_string()` in `config.py`.
`CUPOLA_AUTOMATIC_UPDATES`	Default `false`. When `true`, `ActionEngine` applies INACTIVE status flips through stored procedure `IP4SP_AutoBounce_MarkPersonInactive` (after Multipub safety gates); the same rows are still included in Venu notifications for audit/verification.

Contact lookup (every processed email, except dry-run)#

Used by ContactLookup:

Call	Purpose
`lookup_by_email(email)`	Resolve `link_org_person` rows; may return multiple records per mailbox

Batch mode: same read path per email (Cupola is sequential in batch; Hodor may batch separately).

Action engine touches (`ActionEngine`)#

Reads#

Call	Flow
`check_active_subscription(contact_id)`	INACTIVE path — after collecting inactive recommendation, warns if Cupola still shows active subscription
`check_organization_exists(name)`	INACTIVE + `new_org_details` — output list only
`is_ai_appropriate(...)`	INACTIVE + `new_org_details` — output list “add vs ignore” hint
`check_contact_exists(email)`	REPLACEMENT — decide add vs update for replacement person

Writes and "soft" writes#

Per the Active-only policy all automated CUPOLA touches are guarded. The table below reflects the current behaviour in ActionEngine.

Behavior	Flow	Notes
Status UPDATE optional for INACTIVE	`_handle_inactive`	If `CUPOLA_AUTOMATIC_UPDATES=true`, calls stored procedure `IP4SP_AutoBounce_MarkPersonInactive`; always appends to `_cupola_status_changes` and emails Venu for audit/review.
`add_contact(...)` (gated, off by default)	REPLACEMENT (new replacement email)	Only called when `CUPOLA_AUTO_ADD_REPLACEMENTS=true` and `check_contact_exists` finds none; see add_contact behavior below.
NO auto-add on ACTIVE	`_handle_active`	When no CUPOLA row exists the autoresponder does not call `add_contact`. Instead it writes an `output_document_inactive_no_cupola_match` row and a Human Review row (`HUMAN_REVIEW_REASON_ACTIVE_NEW_CONTACT`). Sai Teja / IP4 verifies and adds the entry manually.
NO auto-reactivate	`_handle_active` when the matched CUPOLA row is inactive	The autoresponder does not call `update_contact_status(..., True)`. A Human Review row with reason `HUMAN_REVIEW_REASON_REACTIVATION_CANDIDATE` is emitted; IP4 creates a new CUPOLA entry (the old inactive row is never reactivated).
Email/title update via stored procedure — CUPOLA-gated	`_handle_title_update` (EMAIL_UPDATE / TITLE_UPDATE)	Uses `IP4SP_AutoBounce_OrgPersonUpdates`; updates on mirrored systems fire only when the preferred CUPOLA row exists and `is_active=True`. Inactive CUPOLA rows route to Human Review (`HUMAN_REVIEW_REASON_UPDATE_ON_INACTIVE`); missing rows route to `HUMAN_REVIEW_REASON_ACTIVE_NEW_CONTACT`.
OUT_OF_OFFICE never touches CUPOLA	`_handle_out_of_office`	OOO determinations add a `HUMAN_REVIEW_REASON_OUT_OF_OFFICE` row for the planned dedicated OOO process and perform no CUPOLA / Hodor / Multipub / Salesforce writes.

`add_contact` behavior#

add_contact can still be called by the REPLACEMENT flow (_handle_replacement) where the LLM has a verified replacement person, but only when CUPOLA_AUTO_ADD_REPLACEMENTS=true (default is off). It is not called by _handle_active — active auto-replies with no CUPOLA row are routed to Human Review (see table above).

add_contact resolves the target organization_id first (from organization_id, or by organization name with create-if-missing, or default org 1 when neither is supplied). It then looks up the email in Cupola (lookup_by_email / link_org_person).

Situation	What happens
Email already linked at the target org	Returns the existing `org_person_id`; no insert.
Email linked only at other org(s)	Reuses the existing `person_id`, inserts one new `link_org_person` row for the target org (same mailbox, additional org affiliation). Logged at INFO.
Email not in Cupola	Inserts a new `person` row and a new `link_org_person` row (unchanged from prior behavior).

This prevents duplicate person rows when the same email should gain a second org affiliation, and makes same-org adds idempotent.

Audit side channel (local file, not Cupola DB)#

CupolaAuditLogger duplicates some Cupola-related actions into a local report file under the run directory (cupola_audit_log).

Wiring in main.py: The audit logger is passed into ActionEngine in live, dry-run, and read-only branches. After each run, cupola_audit_log.csv and cupola_audit_log.json are always written (header-only CSV when there are no entries), and both files are emailed to Venu (NOTIFICATION_EMAIL_VENU) as attachments when that address is configured.

Impact report: utils/impact_report.py reads the in-memory CupolaAuditLogger.entries list and derives the three headline numbers for the run: total emails processed, records deactivated (status_change with requested_status=False and auto_applied=True), and records added (contact_addition with a non-empty contact_id). The resulting impact_report.txt / .json is written next to the audit log; metrics are inlined in the N08 run-audit email body (the .txt file is not attached).

IP4 CUPOLA-undetermined handoff#

Rows captured by output_document_inactive_no_cupola_match.csv (inactive-no-match and ACTIVE-outcome-no-CUPOLA / reactivation candidates) are batched into Notifier.notify_sai_action_items (catalog N05/N06). To: NOTIFICATION_EMAIL_SAI; global Max + Vish Cc.

Read-only / dry-run#

Read-only mode: Cupola is wrapped → add_contact, update_contact_email, update_contact_title, update_contact_status, update_lead_status (if ever routed) are intercepted by ReadOnlyDatabaseWrapper (logged; success faked per wrapper). Lookup reads still hit the live DB.
Dry-run: Typically MockCupolaDatabase — no live SQL.

Output artifacts#

OutputDocumentCollector stores Cupola org/person IDs on inactive, alternate-contact, new-org, and undeliverable lists for CSV/JSON deliverables.

README.html — mode matrix
hodor.html, multipub.html, salesforce.html — parallel systems in the same flows