Skip to content
SF CTA Study Hub

Scenario 02: BookBridge Education Foundation

AI-Generated Content — Use for Reference Only

This content is AI-generated and has only been validated by AI review processes. It has NOT been reviewed or validated by certified Salesforce CTAs or human subject matter experts. Do not rely on this content as authoritative or completely accurate. Use it solely as a reference point for your own study and preparation. Always verify architectural recommendations against official Salesforce documentation.

Practice Information

Difficulty: Intermediate Domain weights: D1 System Arch: LIGHT | D2 Security: LIGHT | D3 Data: MEDIUM | D4 Solution: MEDIUM | D5 Integration: HEAVY | D6 Dev Lifecycle: HEAVY Designed for 90-120 minute prep window

Before You Start

Print this scenario. Read it twice using the Two-Pass Reading Method — once for understanding, once to extract implicit requirements. Then build your solution artifacts within the 120-minute window.

Project Overview

BookBridge Education Foundation is a nonprofit improving childhood literacy by collecting donated books and distributing them to under-resourced schools. Founded 9 years ago, it has grown from a single-city operation to a national presence across 15 US cities.

AttributeDetail
Paid employees120 (35 programs, 25 warehouse, 20 fundraising, 8 tech, 32 admin)
Regular volunteers~800 active
Partner schools2,500 across 15 cities
Annual book volume~600K donated, ~550K distributed
Donors~500,000 in database
Annual budget$8.5M (40% individual donors, 30% corporate, 20% grants, 10% events)

The “One Platform” initiative has an approved $1.2M budget over 18 months. Goals: reduce operational overhead, improve donor retention, and scale to 5,000 partner schools within 3 years.

The CTO: “We have six different systems that don’t talk to each other, a development team that just tripled in size with no processes, and a data mess that makes it impossible to answer basic questions about our impact.”

Current Systems and Pain Points

Donation Platform (GiveHub — Custom Ruby on Rails)

Custom web app built 7 years ago. Hosted on Heroku, running Rails 5.2 (end-of-life, no security patches). PostgreSQL database with ~500K donor records and ~3.2M transactions. Handles one-time and recurring donations via Stripe. Processes ~2,000 donations/day normally; spikes to 8,000-12,000/day during year-end campaign (Dec 15-31). Has an undocumented REST API. Original developer left 3 years ago; no Ruby on Rails expertise on current team. No automated tests.

Warehouse Management (ShelfTrack Cloud)

SaaS application with documented REST API (v3.1) supporting webhooks. Tracks books by condition grade, genre, reading level, and language across 4 regional warehouses. ~180K books in inventory at any time. Processes ~2,500 inbound and ~2,200 outbound books/day. Outbound shipments linked to school requests (50-200 books each). Does not track donor attribution — link to donor is lost once a book enters the warehouse.

School District Student Data

15 school districts provide aggregated school-level data (student counts, reading proficiency, demographics, Title I status) via SFTP. File formats and field names vary by district. Delivery cadence varies (monthly, quarterly, or annually). One staff member manually downloads from 15 SFTP servers and reformats into a master spreadsheet. No PII — all data aggregated at school level.

QuickBooks Online (Accounting)

Finance uses QBO for GL, AP, and reporting. Donation revenue manually entered from weekly CSV summaries. Monthly reconciliation across Stripe settlements, GiveHub, and QBO takes 2 full days. REST API with OAuth 2.0.

MailChimp (Donor Communications)

~350K email subscribers, ~2.5M emails/month. Donor segments manually exported as CSV from GiveHub before each campaign. Email engagement data (opens, clicks) trapped in MailChimp, not connected to donor records. REST API (v3.0) with webhooks.

Eventbrite (Volunteer Events)

~120 events/year across 15 cities. ~800 regular + ~2,000 one-time volunteers annually. Volunteer hours tracked in separate Google Sheets per city manager. No connection between volunteer and donor records. REST API with webhooks.

Business Process Requirements

Donor Management and Fundraising

  1. Single source of truth for all donor information, consolidating records from GiveHub, MailChimp, Eventbrite, and spreadsheets into unified constituent profiles.
  2. Donor records capture giving history, communication preferences, volunteer participation, employer match eligibility, relationships, and engagement scores.
  3. Online donations must flow without interruption during and after migration — no downtime, especially during the December year-end campaign.
  4. Recurring donation management (create, modify, cancel, failed payment retry) within the platform; donors self-manage through a portal.
  5. Targeted donor segmentation by giving history, engagement, events, geography, and communication engagement; launch campaigns without manual exports.
  6. Corporate sponsorship tracking: multi-year pledges, installment schedules, tiers, and deliverable tracking.
  7. Grant management: application deadlines, proposals, awards, fund restrictions, reporting deadlines, and program-level expenditure tracking.

Book Distribution and School Partnerships

  1. Track partner school profiles (district affiliation, Title I status, demographics, distribution history, contacts) as the central relationship management point.
  2. Book request approval workflow considering inventory, equitable distribution, and alignment with school needs.
  3. Real-time inventory visibility by warehouse, condition grade, genre, reading level, and language without logging into ShelfTrack.
  4. Shipment dispatch notifications with tracking information to the requesting school and assigned Programs team member.
  5. Track complete book lifecycle from donation through warehouse to school distribution, enabling donor impact reporting.

Volunteer Engagement

  1. Link volunteer profiles to constituent records for a unified engagement view (e.g., a $500 donor who volunteers 40 hours/year appears as one record).
  2. Volunteer event management (registration, check-in, hour tracking) with certification letter generation for tax and employer-matching.
  3. City managers see volunteer participation trends for their city: active counts, average hours, retention, and upcoming events.

Data Model and Migration Requirements

  1. Migrate ~500K donor records from GiveHub with full giving history; deduplicate against MailChimp and Eventbrite imports.
  2. Migrate ~4M historical book distribution records linking donations to warehouse intake to school distribution for impact reporting.
  3. Support ongoing volume of ~600K book donation records/year and ~550K distribution records/year plus warehouse inventory transactions.
  4. Donor deduplication across 3 systems (different emails, name variations, missing fields) with fuzzy matching and clear survivorship rules.
  5. Normalize school district SFTP data into a consistent format regardless of source layout; enable year-over-year comparison of demographics and needs.

Accessibility and Visibility Requirements

  1. Development team sees full donor profiles and communication engagement but not HR data, accounting details, or warehouse operations.
  2. Warehouse staff sees inventory and shipments but not donor financials or school contacts beyond shipping addresses.
  3. City managers see volunteer and event data for their city only, plus regional school partnerships.
  4. School partners portal: submit requests, view status and shipments, download impact reports — no visibility into other schools, donors, or operations.
  5. Finance team sees donation transactions and grant financials but not donor communication history or volunteer details.
  6. Volunteers log in to view participation history, upcoming events, and download hour certification letters.

Reporting Requirements

  1. Executive Director dashboard: donations vs targets, books distributed vs targets, active partnerships, volunteer hours, donor retention — refreshed daily.
  2. Campaign performance: gifts received, average gift size, donor acquisition vs retention, and ROI by campaign with historical comparison.
  3. Impact reporting connecting the full chain: donation dollars to books collected to distributed to schools served to students reached — by city, district, and school.
  4. Automated monthly reconciliation comparing platform donation income against payment processor settlements and GL entries; flag discrepancies >$50.
  5. Board quarterly report: year-over-year trends in donors, revenue, books, schools, volunteer hours, and cost per book (currently takes 3 days from 5 sources).
  6. Grant funder impact reports showing fund usage aligned to deliverables, due quarterly or annually per funder.

Integration Requirements

  1. Online donations flow in near-real-time (~2,000/day, spiking to 12,000/day in December) with donor attribution, amount, campaign source, and payment method.
  2. ShelfTrack Cloud synchronizes inventory, inbound receipts, and outbound shipments; status changes reflected within 15 minutes.
  3. Ingest SFTP data from 15 districts with varying formats; validate quality, flag anomalies, and load clean records.
  4. Donation revenue summaries flow to QuickBooks at least weekly as journal entries with proper account coding and fund allocation.
  5. Donor segments and campaigns synchronize with email platform; engagement data (opens, clicks, bounces, unsubscribes) flows back to constituent records.
  6. Volunteer event data (registrations, check-ins, cancellations, hours) synchronizes with constituent matching to link volunteer and donor profiles.
  7. All integrations include error handling with failed transaction capture, team visibility, and retry/manual correction without data loss.
  8. Architecture must accommodate 2-3 new integrations per year without re-architecture.

Development Lifecycle Requirements

  1. Team grew from 3 to 8 in 6 months. No defined development process — all changes deployed directly to production. 4 production outages in 3 months, including one during a fundraising event (~$15K in lost donations).
  2. Structured environment strategy (dev, test, UAT) that maintains delivery speed.
  3. All changes tested in non-production before promotion, with QA analyst and business stakeholder approval.
  4. Source control supporting parallel feature work, change history, accountability, and rollback.
  5. Automated testing covering integration points and custom logic at minimum.
  6. Defined release management with calendar, documentation, stakeholder communication, and rollback plans.
  7. QA environment mirroring production data volumes and integration connections.
  8. Development process documented clearly enough for new team members to onboard within their first week.

Implicit Requirements

Pay attention to: the year-end donation spike and cutover timing; the variety of integration protocols and middleware implications; rapid team growth vs current process gaps; nonprofit budget constraints affecting architecture; the 3-year scaling target (5,000 schools) and data volume impact; varying SFTP formats and data transformation needs.