alumni_lookup

Phase 29 Launch Guide

One-file giving import on Alumni Lookup: Salesforce contact ID join, FY-scoped replace, coverage-window gating. Read spec §5A before QAing the coverage gate. The rule was revised on the first real report; several checkboxes below assert the revised behavior and contradict the original spec. Spec: docs/planning/phases/phase-29/ Update this file as each migration and task is written, not at wrap.

---

1. Pre-Deployment Check — resolved

How many web dynos does production run? — Answered September 23, 2026: one.

web.1: up 2026/09/22 18:28:46 -0500

Plus one worker dyno running Sidekiq, which never touches this path. So the tempfile preview → commit handoff stays, consistent with the two engagement importers already in use. Re-check with heroku ps -a alumni-lookup if the formation is ever scaled up — the note below is why it matters.

The preview → commit handoff in Settings::EngagementActivitiesController stashes a Tempfile path in the session. With more than one web dyno, a commit landing on a different dyno than the preview finds no file and imports zero rows without saying so.

This is already true of the two existing engagement importers — it is not introduced here. But this phase is the first one where a silent zero-row import is paired with a delete, which is why the dyno count had to be checked before the design was settled rather than after.

Scaling web past one dyno breaks this importer (and the two that already use the pattern), so it is a prerequisite for that change, not an afterthought: the manifest would need to move into the database, following education_import_batches.manifest_data / employment_import_batches.manifest_data. Worth a BACKLOG entry rather than work in this phase — at one dyno it is not a defect, just a constraint that is now written down.

---

2. Migrations

#	Migration	Risk	Notes
29.1	add_column :alumni, :salesforce_contact_id, :string, limit: 15 + non-unique index	Low	Additive, nullable, no backfill in the migration. Non-unique is deliberate — spec Decision D. Reversible by dropping the column
29.2	add_column :engagement_batch_logs, :deleted_count, :integer, default: 0, null: false and :metadata, :jsonb, default: {}	Low	Both defaulted, so the API writer at api/v1/engagements_controller.rb is unaffected

Neither is destructive. Neither rewrites existing rows.

---

3. Post-Deployment Task — Stage 1 backfill

The column ships empty. Run a full Affinaquest contact import before the giving importer is usable — the report is ready, per planning.

Verify the column actually populated, and that case survived (spec Decision B):

echo 'puts Alumni.where.not(salesforce_contact_id: nil).count' | heroku run -a alumni-lookup rails console
echo 'puts Alumni.where("salesforce_contact_id ~ \x27[a-z]\x27").count' | heroku run -a alumni-lookup rails console

The second count must be greater than zero. Salesforce IDs are mixed-case (003Uq00000QBvEm), so a zero there means something up-cased the column and the join is now case-blind — the exact bug this phase exists to avoid. Stop and fix before importing any giving.

Then check for collisions the non-unique index intentionally permits:

Alumni.where.not(salesforce_contact_id: nil)
      .group(:salesforce_contact_id).having("COUNT(*) > 1").count
# => expected {} ; anything here should be merged, and will be flagged in the giving preview

---

4. Sensitive Features / QA Focus Areas

The destructive path is the whole risk surface. Test in this order on staging:

• Coverage gate, full FY — window 2025-06-01 .. 2026-05-31, FY26 rows only. Preview reports FY26 authoritative; commit replaces
• Coverage gate, straddling file — window 2025-06-01 .. 2026-06-04, rows in both FY26 and FY27. FY26 replaced, FY27 untouched and reported provisional
• Coverage gate, truncated — window 2025-11-01 .. 2026-05-31. FY26 reported provisional; zero deletions, zero inserts
• In-progress FY, report pulled today — window 2026-06-01 .. today, newest gift in the file several days older. Reported authoritative with no friction. This is the normal case and it is the one the original rule got wrong (spec §5A)
• Stale window, inside the ceiling — window_end 20 days ago, file contains the in-progress FY. Authoritative, confirm checkbox required, importable once ticked
• Stale window, past the ceiling — window_end 60 days ago. Provisional and refused — not a confirm. ⚠️ This checkbox previously asserted “warns, requires confirm, still importable”, which was true of the pre-revision rule and is now wrong
• Completed FY raises no staleness — import FY26 with window 2025-06-01 .. 2026-05-31 in September. Authoritative, no stale confirm (a finished year’s report is supposed to end months ago)
• The June trap — with the clock inside the first 45 days of a fiscal year, a report ending before the previous year’s May 31 must still be refused for that previous year
• Pre-fill — choose a file and confirm window_start fills from its earliest gift and window_end fills with today, not the file’s newest gift. Filling the newest gift here is the bug from §5A
• Mass-loss guard — upload a deliberately filtered 10-row report against a populated FY. Confirm the checkbox is required and the message names real counts
• Level boundaries — a $10.00 donor is level 1, a $10.01 donor is level 2
• Zero / negative total — no record created; an existing record for that FY correctly disappears on replace
• Unmatched contact IDs — appear in the issues CSV, with no dollar amounts in any column
• Multi-BUID contact ID — skipped, both BUIDs named in the preview
• Re-run idempotence — importing the same authoritative file twice leaves identical rows and a second engagement_batch_logs entry
• Deletion scope — an FY25 giving row survives an FY26 import; a row labelled FY26 but dated in FY25 is treated by its date
• Transaction — force a mid-commit failure and confirm the fiscal year is not left emptied
• Amount leakage — grep the persisted manifest and the engagement_batch_logs row for any dollar figure

Engagement scores shift after import — spot-check two or three alumni profiles and the Engagement Stats giving breakdown against the source report by hand, once.

---

5. Deployment Stages

Stage	Contents	Independently deployable?
1	salesforce_contact_id, contact-importer mapping, GivingLevel	Yes — additive and inert on its own
2	Csv::GivingImporter, routes, views, engagement_batch_logs columns	Yes, but useless before stage 1’s backfill — every row would report Unmatched contact ID

---

6. Rollback

Symptom	Action
Import produced wrong levels	Re-pull a June-1-anchored report and re-import; replace is idempotent by design
Levels look uniformly too low for the current FY	The window’s end was understated — almost always the file’s newest gift date entered instead of the pull date. Re-import with the correct window_end; replace makes this self-healing
Import wiped a fiscal year it should not have	engagement_batch_logs records deleted_count and the FY verdicts in metadata, but deleted activity rows are not recoverable from it — restore from the daily Heroku backup, or re-import a comprehensive report for that FY, which reconstructs every level correctly
Contact import now failing	Drop the salesforce_contact_id mapping; the column is nullable and nothing else reads it
Case-folded column	Drop the column, fix the normalize_value branch, re-run the contact import

The second row is the one to read twice. A comprehensive re-import is the real recovery path, which is another reason the coverage rule refuses to replace on a partial file.

---

7. Coverage bands, for reference while QAing

Window ends	Completed FY	In-progress FY
Any date ≥ the FY’s May 31	Authoritative	—
Any date < the FY’s May 31	Refused	—
≤ 14 days ago	—	Authoritative, silent
15–45 days ago	—	Authoritative, confirm required
> 45 days ago	—	Refused (provisional)

Csv::GivingImporter::STALE_WINDOW_DAYS = 14, COVERAGE_MAX_STALE_DAYS = 45. Both are class constants — read them rather than trusting this table if the two ever disagree.