alumni_lookup

Phase 29 — Giving Import

Status: ✅ Complete — drafted and built September 23, 2026. Effort Class: Medium (two additive migrations, one importer service, one form; the hard part is the coverage rule, which is decided below) Surfaces: Alumni Lookup only — Settings::EngagementActivitiesController, Csv::, app/models/. No held-surface writes. Prerequisites: None. Phase 26 is a peer — it asks who wins when two records describe one BUID; this phase only ever writes engagement_activities and one additive alumni column. Held surfaces touched: None.

1. The Question This Phase Answers

A comprehensive fiscal-year gift report arrives keyed by Salesforce contact ID — not BUID, not BQID. Turning it into engagement activity currently takes a case-sensitive VLOOKUP, a pivot, a hand-built level column, a manual delete of the fiscal year, and an upload. Can that be one file and one Import button — without ever letting a partial report overwrite a complete one?

The second clause is the whole phase. The first is a morning’s work.

2. The Manual Process Being Replaced

# Today After
1 Pull the comprehensive FY gift report (Contact ID, name, gift date, recognition credit amount, VSE category, degree year) unchanged — same report, uploaded as-is
2 Case-sensitive VLOOKUP against a contact export to recover BUIDs alumni.salesforce_contact_id, populated by the contact import already in use
3 Pivot to FY total + most recent gift date per person Csv::GivingImporter, grouped by (buid, fiscal_year)
4 Assign a giving level from the total GivingLevel.for_amount
5 Hand-build a BUID / activity_code / description / engagement_date table derived; description reads from EngagementType#name so the label cannot drift from the seed
6 Manually delete the FY’s giving activity, then import delete + insert in one transaction, scoped to fiscal years the report provably covers

Step 6 is where the current process can silently lose information, and §5 is the answer.

3. Decisions

Decision A — The join key is a new alumni column, and it is 15 characters

The app stores no Salesforce ID today. alumni.contact_id is the BQID (C-000000000); alumni.primary_contact_id is the assigned staff user, unrelated.

The contact export carries both Contact: Contact ID (15-char) and Contact: Full Contact Id (18-char). The gift report carries only the 15-char. The 18-char form is the 15-char plus a case-recovery checksum, which is of no use to us because Postgres compares strings case-sensitively by default — a plain = is the case-sensitive VLOOKUP, for free. So: store 15, truncate whatever arrives, map both header spellings.

Decision B — salesforce_contact_id must never be case-folded

Real, observed collision: 003Uq00000QBvQK and 003Uq00000QbvQK are two different contacts. This is why step 2 needs a case-sensitive lookup at all, and it is a live hazard in the code:

# app/services/csv/affinaquest_contact_importer.rb:787
when :buid, :contact_id
  value.to_s.strip.upcase

That is precisely where a reasonable person adds a third ID, and doing so would collapse both contacts to 003UQ00000QBVQK — baking the spreadsheet bug into the database permanently and irreversibly. salesforce_contact_id gets its own when branch that only strips, carrying a comment that says why, and a test pins it: two fixtures differing only in letter case stay two rows through a full import.

Decision C — Match on BUID only; report the misses

No VSE-category filter. VSE Student and Alumni Non-Degree may matter later, and filtering on a category is a guess about intent; failing to resolve a BUID is a fact. Rows whose contact ID resolves to no BUID are skipped and land in the issues CSV (§7), same posture as the existing engagement importer.

Decision D — The new column gets a NON-unique index, unlike contact_id

alumni.contact_id carries a unique index. This one deliberately does not.

Two alumni rows sharing one Salesforce ID means either duplicate alumni records — a known, recurring condition that staff resolve by hand — or a CRM-side mis-mapping. Under a unique index, the contact import is what fails, mid-batch, on a routine job, over a problem that is not urgent. AffinaquestContactImporter already has the right posture for this: log an AffinaquestImportConflict and keep going. This follows it.

The collision is then caught where a human is already looking: the giving preview flags any contact ID resolving to more than one BUID as a conflict, skips it, and names both BUIDs. Promote the index to unique later if data health shows a long clean run.

The column is also treated like contact_id on write: fill when blank, flag rather than overwrite on mismatch.

Decision E — No dollar amounts are persisted, anywhere

Levels only. Concretely:

Decision F — Totals of $0.00 or less produce no record

Bands start at $0.01. A net-zero or negative FY total (refunds, adjustments) yields no activity — and because the fiscal year is replaced wholesale, a prior record correctly disappears. Negative line items do count toward the total; they are corrections, not noise.

Decision G — The audit trail attaches to engagement_batch_logs

Not a new table. It is already the generic “a batch of engagement rows was written” ledger. One clarification worth recording, because it was mis-remembered during planning: that table is not written by the email-click importer. Its only writer is Api::V1::EngagementsController, where event_identifier is a caller-supplied idempotency key with a uniqueness validation.

Two additive columns are needed: deleted_count (the replace step has a number worth keeping) and metadata jsonb (per-level breakdown, FY coverage verdicts). Putting either in errors_detail would be a lie about that column’s name. Both default, so the API path is untouched. source: "giving_import", event_identifier synthesized as giving_import_<fy list>_<utc timestamp>.

4. Schema

Migration Change Risk Reversible
29.1 alumni.salesforce_contact_id — string, limit: 15, nullable, non-unique index (Decision D) Low Yes — drop column
29.2 engagement_batch_logs.deleted_count — integer, default: 0, null: false; engagement_batch_logs.metadata — jsonb, default: {} Low Yes — drop columns

Both additive. Neither touches existing rows. No new tables.

GivingLevel is a new shared value object at app/models/giving_level.rb (unnamespaced — a taxonomy about a shared domain object, per CLAUDE.md rule 7). Its consumers on day one are Csv::GivingImporter and the preview view; the header names them.

GivingLevel.for_amount(250_000)   # => 5
GivingLevel.code_for(250_000)     # => "giving_level_5"
GivingLevel.for_amount(10.00)     # => 1   (band is 0.01..10.00)
GivingLevel.for_amount(10.01)     # => 2
GivingLevel.for_amount(0)         # => nil (Decision F)

Bands, stated once so the boundary cases are testable in one place:

Level From To
1 $0.01 $10.00
2 $10.01 $100.00
3 $100.01 $1,000.00
4 $1,000.01 $10,000.00
5 $10,000.01 —

5. The Coverage Rule — Why the Report Cannot Be Trusted to Describe Itself

A report pulled from November 1, and a report pulled from June 1 in a year where nobody gave in June or July, produce identical earliest-gift dates. So any “does this file reach back to June?” flag derived from min(gift_date) is a guess — and it guesses wrong in the expensive direction: a genuinely quiet June looks like a truncated report, the warning fires on a perfectly good file, and within a month it is clicked through reflexively.

So the coverage window is an input, not an inference. The upload form asks for it, pre-filled so it is usually two keystrokes. Coverage then becomes a fact:

An FY is AUTHORITATIVE iff
  window_start <= FiscalYear.first_day(fy)         # reaches back to June 1
  AND window_end >= required_end_for(fy, today)    # reaches forward far enough

REVISED September 23, 2026, on first contact with a real report. This clause originally read window_end >= [FiscalYear.last_day(fy), Date.current].min — the in-progress fiscal year had to be covered through today. That is unsatisfiable, and it refused the first real gift report uploaded. See §5A.

required_end_for splits the two cases:

Fiscal year Must be covered to Why
Complete its last day (May 31) A report stopping May 20 does not describe FY26, and no amount of recency makes it so
In progress within COVERAGE_MAX_STALE_DAYS (45) of today It cannot be covered to May 31, and demanding today is unsatisfiable (§5A)

The in-progress branch still earns its keep: above 45 days the year is refused, which catches re-importing a comprehensive report pulled three months ago — that would lower levels for everyone who has given since.

Two edges the formula above hides and the implementation has to handle:

Only authoritative fiscal years are replaced

Per fiscal year present in the file, independently:

Delete and insert run inside one transaction per import, so a failure cannot leave a fiscal year emptied and unrefilled.

Why “merge instead of replace” is explicitly NOT built

The obvious next idea — for a provisional FY, add rather than replace, raising a level but never lowering it — cannot be made correct without stored amounts, which Decision E rules out. Someone at $600 who gives another $600 has a true level 4; a partial report sees $600, computes level 3, finds an existing level 3, and changes nothing. It understates silently, and nothing on the record marks which levels are trustworthy.

“Only a June-1-anchored report produces a real level” is worth keeping as a bright line, and re-pulling from June 1 costs nothing — it is already how the report is run. If a raise-only mode is ever wanted, it needs amounts, and that is a different phase.

5A. Why the original clause was wrong — the finding from the first real import

The first real report was uploaded September 23, 2026. It was pulled that morning, it covered FY27 from June 1, and the importer refused it:

FY27 — Provisional — skipped.  183 alumni.
Needs coverage: Jun 1, 2026 – Sep 23, 2026
Gifts in file:  Jun 1, 2026 – Sep 15, 2026

Two things had gone wrong, and the second is the one worth carrying forward.

1. The gate was unsatisfiable. window_end >= today means the in-progress fiscal year can only ever be imported by pulling the report and uploading it inside one calendar day. Nothing about an eight-day-old window makes a report partial; it makes it older, and every total in it is still correct as of its date.

2. The window’s end is not the file’s last gift — gift PROCESSING lags giving. The operator entered September 15 because that is what the pre-fill offered, and September 15 is the newest gift the report contains. But the report was pulled on the 23rd. Those two dates differ by processing lag on every report ever pulled, and the pre-fill was quietly converting that lag into missing coverage. The form was generating the wrong answer and the rule was then punishing it — the worst shape a validation can take, because the operator has no way to tell which half is wrong.

The spec also contradicted itself, and that is what should have caught this at design time. §6.2 already declared staleness a soft concern — “warning plus confirm, not a block”, justified with “a report pulled last Friday and imported on Monday is normal.” §5 made the same fact a hard gate. Two rules governing one fact, disagreeing, and the hard one silently swallowed the soft one: for the in-progress year §6.2 could never fire, because anything it would have warned about was already refused. The screenshot shows exactly that — the stale-window warning printed next to the refusal it was supposed to be softening.

The rule now has three bands, which is what it takes to separate “structurally partial” from “merely older”:

Window ends In-progress FY Friction
≤ 14 days ago Authoritative none
15–45 days ago Authoritative explicit confirm checkbox naming the age
> 45 days ago Provisional refused

And the pre-fill now takes its two ends from different sources, because they answer different questions: window_start from the file’s earliest gift (conservative — it can only err toward “provisional”, which skips a year rather than replacing it wrongly), window_end from today, on the stated assumption the report was pulled today.

The generalizable lesson: when a spec states one rule twice at different strictness, that is not redundancy to tidy up later — it is an unresolved decision, and the harder copy wins silently at runtime. Resolve it at design time, or the softer copy becomes documentation for behavior that cannot happen.

6. Guards

Three, in descending order of how much they save you.

6.1 Mass-loss confirm — the one that matters

The skew this phase was raised to prevent costs one person one level. The failure that costs the whole fiscal year is uploading an accidentally-filtered report — one gift society, one campaign — with a window that claims full coverage. Replace then wipes the FY, inserts the handful of rows, and looks like it worked.

For each authoritative FY, compare existing distinct BUIDs against incoming. A net loss over 20% requires a separate confirm checkbox spelling out the real numbers:

FY26: 1,240 existing → 180 incoming, 1,060 net loss. Confirm this report is comprehensive.

Legitimate year-over-year attrition never looks like that. A mis-filtered report always does.

6.2 Stale window — soft

window_end more than 14 days before today, and only when the file contains the fiscal year still in progress: a warning plus confirm, not a block. A report pulled last Friday and imported on Monday is normal; one pulled in June and imported in September is refused outright by §5’s 45-day ceiling, so nothing reaches this guard to block.

The scoping is load-bearing. A completed fiscal year’s report is supposed to end months ago — warning that an FY26 report stops at May 31 would fire on every prior-year import, and noise is how a guard gets clicked through.

What is deliberately not checked: the gap between window_end and the newest gift in the file. That gap is processing lag, it is normal, and treating it as missing coverage is precisely the bug §5A documents.

6.3 Window cross-check — soft

If the claimed window_start precedes the file’s earliest gift date by more than 30 days on a file with more than 100 rows, warn. This is the one field a human can get wrong, and getting it wrong invents authority the report does not have. Warn, never block — a small report legitimately has gaps.

7. Inventory

Service — app/services/csv/giving_importer.rb, following the Csv:: interface (.preview / .call). Owns: header mapping, per-(buid, fy) aggregation, level assignment, coverage verdicts, guard evaluation, issues CSV construction. The controller instantiates and renders; no logic in the controller (CLAUDE.md, Key Services).

Controller — three actions on the existing Settings::EngagementActivitiesController, which already carries the import → preview → commit shape twice: import_giving, import_giving_preview, import_giving_commit. Routes join the existing collection block. Admin-only, inherited from Settings::BaseController.

Views — app/views/settings/engagement_activities/import_giving.html.erb (upload + window fields) and import_giving_preview.html.erb (coverage verdict per FY, guard confirms, per-person table with on-screen totals, issues download).

Issues CSV — salesforce_contact_id, last_name, first_name, degree_year, fiscal_year, reason. No amounts (Decision E). Reasons: Unmatched contact ID, Multiple BUIDs, Provisional fiscal year, Zero or negative total, Invalid gift date. Downloadable from both the preview and the post-commit result, following the gap_csv_employment_import_batch download pattern.

Writes — insert_all for the derived rows, not the current per-row create! loop. A comprehensive FY report is thousands of rows, and the existing loop also runs a dead EngagementType.find_by on every iteration.

Known constraint, checked rather than assumed: the preview → commit handoff in this controller 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 — silently. This is already true of both existing engagement importers, but this is the first importer to pair a silent zero-row run with a delete, so it was checked before the design was settled: production runs one web dyno (verified September 23, 2026), so the tempfile pattern stays. Scaling web past one dyno is now a prerequisite for moving the manifest into the database, the way the education and employment importers already do with manifest_data — see the launch guide §1.

8. Acceptance Tests

Written as the code is written (CLAUDE.md rule 9), not after.

GivingLevel — every band boundary in both directions: 10.00 → 1, 10.01 → 2, 100.00 → 2, 100.01 → 3, 1000.00 → 3, 1000.01 → 4, 10000.00 → 4, 10000.01 → 5; 0 → nil; -50 → nil.

Case sensitivity (Decision B) — two alumni whose salesforce_contact_id differs only in letter case survive a full contact import as two distinct rows with distinct IDs. This test must fail if anyone adds the column to the .upcase branch.

Coverage (§5) — a file spanning FY26 (full) and FY27 (June 1–4) with a window of 2025-06-01 .. 2026-06-04: FY26 is replaced, FY27 activity is untouched and reported provisional. Inverted: a window starting 2025-11-01 leaves FY26 untouched too.

Replace semantics — an alum with an existing FY26 giving_level_4 who appears in a new authoritative FY26 report at level 2 ends with exactly one FY26 giving row, at level 2. An alum with an existing FY26 row who is absent from an authoritative FY26 report ends with none. A provisional FY never deletes anything.

Deletion scope — a giving_level_3 row dated inside FY25 is untouched by an authoritative FY26 import, and a giving row whose description says FY26 but whose engagement_date falls in FY25 is treated by its date, not its label.

Aggregation — multiple gifts for one contact in one FY sum to one row, dated the latest gift; engagement_date is the max, not the min or the first-seen.

Guards — the 20% mass-loss threshold blocks commit without the confirm and permits it with; a stale window warns; a contact ID resolving to two BUIDs is skipped and named.

Description — reads EngagementType#name, so renaming the seed renames the output. Asserted against the literal "Level 5 Giving FY26".

No amounts persisted — the manifest written between preview and commit contains no numeric amount field, and no engagement_activities or engagement_batch_logs column receives one.

Sabotage pass (per /testing): removing the coverage gate, and flattening the level bands, must each break tests written for them.

9. Activity Event Tracking

None. This is a staff-only admin screen on Alumni Lookup; neither ChampionSignupEvent nor the held Cp::ActivityEvent applies. The engagement_batch_logs row is the audit record (Decision G).

10. Documentation Updates (required before wrap)

11. Deploy Order

Not split into sub-phase files — the work is one session and one spec. But it is two deploys, and the order is not optional:

  1. Stage 1 — salesforce_contact_id + the contact-importer mapping. Deploy, then run a full contact import to populate the column.
  2. Stage 2 — the giving importer. Meaningless before stage 1’s backfill: every row would report Unmatched contact ID.

Stage 2 is testable on staging against fixtures without the backfill. It is not testable against real data without it.

12. Out of Scope

Item Why Where it goes
Raise-only merge for provisional fiscal years Cannot be correct without stored amounts (§5) Needs a phase that revisits Decision E
Storing FY gift totals Decision E —
A batch-history UI for engagement_batch_logs Nothing displays that table today; the issues CSV is reachable from preview and commit without one BACKLOG
Promoting salesforce_contact_id to a unique index Decision D — revisit after a clean run BACKLOG
VSE-category filtering Decision C —

13. What Was Implemented

Built September 23, 2026, and revised the same day after the first real report was uploaded — see §5A, which is the most useful thing in this document. bin/test: 6,060 runs, 17,574 assertions, 0 failures, 0 errors.

Files

File Role
db/migrate/20260923100000_add_salesforce_contact_id_to_alumni.rb 29.1
db/migrate/20260923100100_add_giving_import_columns_to_engagement_batch_logs.rb 29.2
app/models/giving_level.rb Shared value object — the bands, once
app/services/csv/giving_importer.rb Parse, aggregate, coverage verdicts, guards, replace, batch log
app/services/csv/affinaquest_contact_importer.rb +FILL_ONLY_FIELDS, the salesforce_contact_id header aliases and its own normalize_value branch
app/controllers/settings/engagement_activities_controller.rb import_giving, _preview, _commit, _issues_csv
app/views/settings/engagement_activities/import_giving{,_preview}.html.erb Upload + preview
app/javascript/controllers/giving_window_controller.js Window pre-fill (see deviation 2)
test/models/giving_level_test.rb 6 tests
test/services/csv/giving_importer_test.rb 37 tests
test/services/csv/salesforce_contact_id_case_test.rb 7 tests — Decision B’s tripwire
test/controllers/settings/engagement_activities_giving_import_test.rb 19 tests

Integration (anti-orphan): the giving import is linked from the engagement activities index header and from the standard import page’s options list; both links are asserted by tests.

Spec deviations

Three, all small, none changing a decision.

1. .preview / .commit, not .preview / .call. §7 named the Csv:: interface as .preview / .call. Every existing Csv:: importer that has a two-pass shape uses .preview / .commit (EmploymentImporter, EducationAreaOfStudyImporter); .call in this namespace means a one-shot full import (AffinaquestContactImporter.call). Following the codebase rather than the spec sentence, because the spec sentence would have made .call mean two different things inside one namespace.

2. The window pre-fill is client-side, and it does not use the file’s max gift date. §5 says the form is “pre-filled from the file’s min and max gift dates.” A server-side default would have been the one thing §5 forbids — an inferred window. So giving_window_controller.js fills the inputs in the browser, and they stay required and editable; the server never derives a window, and a preview with a blank one redirects with an alert.

The max half of that sentence turned out to be the bug in §5A: the file’s newest gift is systematically older than the day the report was pulled, because gift processing lags giving, so filling it into window_end understated coverage on every import. window_end is pre-filled with today instead, on the stated assumption the report was pulled today. window_start still comes from the file’s earliest gift, because that direction is conservative — it can only err toward “provisional”, which skips a year rather than replacing it wrongly.

3. A fourth route. §7 describes the issues CSV as downloadable “from both the preview and the post-commit result” without naming an action. import_giving_issues_csv serves it, the preview links to it, and the upload page offers it after a commit (the commit redirects there). The issues tempfile deliberately outlives the manifest for exactly that reason, and is cleaned up when the next preview replaces it.

Three things worth knowing that the spec did not anticipate

The fixture pair is load-bearing. alumni.yml’s john_doe and jane_smith carry 003Uq00000QBvQK and 003Uq00000QbvQK — the same letters, different case. That is Decision B’s test data, and a comment at the top of the file says not to “fix” the casing. It also means a global Alumni.where(salesforce_contact_id: ...).count in a test is measuring the fixtures as much as the code; scope such assertions to the BUIDs under test.

The engagement_activities fixtures put two giving rows in the fiscal year in progress (engagement_date: 1.month.ago), which means any test that replaces the current fiscal year with fewer than two alumni trips the mass-loss guard before reaching whichever guard it was written for. clear_in_progress_giving! in the controller test exists for that, and says so. Those same fixtures are described as “Level 1 Giving FY26” while being dated in FY27 — an accidental but perfect demonstration of why deletion is scoped by date and not by label.

EngagementType#level is not the giving band number. The seeds give giving_level_5 a level of 4 — that column is the engagement score weight, and levels 4 and 5 deliberately share it. The fixtures match the seeds. Nothing in this phase reads EngagementType#level; GivingLevel owns the band numbers and EngagementType#name owns the label.

Deploy

Two deploys, per §11, and the order is not optional. Migrations are manual — there is no release: phase in the Procfile:

heroku run rails db:migrate --app alumni-lookup-staging
heroku run rails db:migrate --app alumni-lookup

Stage 1 is the schema plus the contact-importer mapping, followed by a full contact import to populate salesforce_contact_id. Stage 2 is the importer, which reports Unmatched contact ID for every row until that backfill has run. Stage 2 is testable on staging against fixtures without the backfill; it is not testable against real data without it.