Technical Implementation Draft: CiviCRM to MediaWiki Donor Sync

Open Questions

The following questions need to be resolved during implementation planning:

1. Data Hashing - Can we hash the email addresses and other PII prior to transfer to reduce risks associated with accidental data leaks? This would require MediaWiki to hash its stored emails using the same algorithm for matching. shared secret etc.
2. Sync Cadence - What is the optimal frequency for the export job (hourly/daily/weekly)? Resolved: Daily delta export of contacts modified in the last few days, with a full export at fiscal year start (same approach as Acoustic).
3. Data Expiration - Should donor status expire after a certain period of inactivity? Resolved: Status aging in CiviCRM handles this naturally (donors progress through lapsed/deep lapsed/ultra lapsed categories over time).
4. Error Handling - How should failed syncs or partial processing be handled and reported to avoid downstream failures?

Thoughts

• Send the diff (work out changes over the period and only export those)
• Daily exports for recurring
• Less active categories could be monthly

Overview

This document outlines the technical implementation for syncing donor information from CiviCRM to MediaWiki user accounts using email address as the linking identifier (Option 3.3 from the Cross-Platform Donor Identification decision brief).

The goal is to enable cross-platform donor identification by periodically synchronizing donor status from CiviCRM (the source of truth for donor data) to MediaWiki user preferences, allowing personalized donor experiences across Wikipedia platforms.

Architecture

System Components

┌─────────────────────┐     ┌─────────────────────┐     ┌─────────────────────┐
│      CiviCRM        │     │    SFTP Server      │     │     MediaWiki       │
│   (Donor Records)   │────▶│   (CSV Transfer)    │────▶│   (Preferences)     │
│                     │     │                     │     │                     │
│  - Email addresses  │     │  - Donor CSV file   │     │  - User accounts    │
│  - Contact IDs      │     │  - Secure storage   │     │  - wikimedia-donor  │
│  - Donor status     │     │                     │     │    preference       │
└─────────────────────┘     └─────────────────────┘     └─────────────────────┘

Data Flow

1. CiviCRM Export Job generates a CSV file containing donor information
2. CSV uploaded to a private SFTP location accessible by MediaWiki deployment cluster
3. MediaWiki Job detects and processes the CSV file
4. User preferences updated for matching email addresses where consent exists
5. CSV file deleted immediately after processing

Data Storage

MediaWiki User Preference

A single user preference (wikimedia-donor) stores an integer value representing the donor status:

Design Rationale:

• Values align with existing CiviCRM wmf_donor.donor_status_id values and the Donor Audience Framework, avoiding the need for custom classification logic
• Values -1, 0, and 1 are set by MediaWiki and remain unchanged by CiviCRM sync
• Values >= 2 are populated by CiviCRM based on the proven donor categorization already used in the Silverpop/Acoustic export
• Values > 0 indicate user has consented to donor linking
• "Not major donor" exclusion applies to all CiviCRM-populated statuses (values >= 2); major donors are excluded from the export

Storage Location Options

Recommended: User Preferences Table

• Existing APIs available in PHP and JavaScript
• GlobalPreferences extension enables cross-project synchronization
• Not publicly accessible (unlike user groups)
• Amir Sarabadani (Staff Database Architect) has confirmed this is acceptable from infrastructure perspective

Alternative: User Info Table

• Read-only API access already exists
• Would require new API for write access
• Preferable if manipulation prevention becomes a requirement

CiviCRM Export Component

Export Job Specification

The CiviCRM export job will be implemented by Fundraising Tech, inspired by the existing Silverpop/Acoustic export script.

CSV File Format:

csv
contact_id,email_address,donor_status_id,consent_status
12345,user@example.com,30,1
67890,donor@wikipedia.org,2,1

Required Fields:

• contact_id - CiviCRM Contact ID (allows MediaWiki to reference contacts without relying on email, which may change)
• email_address - Donor's email (possibly hashed - see Open Questions), used for matching to MediaWiki accounts
• donor_status_id - Integer matching the wmf_donor.donor_status_id value schema
• consent_status - Whether user has opted in to MediaWiki linking

Data Source and De-duplication

Data should be sourced from silverpop_export_view_full, which already handles the merging of contacts that share the same email address. This avoids reimplementing de-duplication logic.

Export Cadence

Following the same approach as the Acoustic export: daily delta export of contacts whose donor_status_id has been modified in the last few days. A lookback window of a few days provides a safety margin against missed updates while keeping file sizes small.

A full export of all contacts should be scheduled at the start of each fiscal year to refresh all statuses, as donor categories shift with the new FY (same as Acoustic).

File Transfer

Method: SFTP upload to private server accessible by MediaWiki deployment cluster

Security Considerations:

• Email addresses may be encrypted/hashed (implementer's discretion - see Open Questions)
• File should be deleted immediately after processing
• Access restricted to authorized systems only

Infrastructure:

• Can use mwscript-k8s on deploy host (confirmed by Chris Danis)
• Upload target location to be determined during implementation

MediaWiki Import Component

Import Job Specification

A MediaWiki maintenance job will process the exported CSV file.

Possible Job Workflow:

1. Check for presence of CSV file at configured location
2. If file exists:
   a. Parse CSV records
   b. For each record:
      i.   Find MediaWiki user(s) with matching verified email
      ii.  Check user's current preference value
      iii. Skip if preference is -1 (explicit opt-out)
      iv.  Skip if preference is 0 (no consent given)
      v.   Update preference with donor_status_id if current value > 0 (consent exists)
      vi.  Store contact_id association for the user
   c. Delete CSV file immediately after processing
3. Log processing statistics

Update Conditions:

• User has a MediaWiki account with verified email address
• User's current wikimedia-donor preference value is > 0 (indicating consent)
• User has not opted out in CiviCRM (consent_status = 1)

Skipped Records:

• Users with wikimedia-donor = -1 (MediaWiki opt-out)
• Users with wikimedia-donor = 0 (no consent)
• Users with unverified email addresses
• Email addresses with no matching MediaWiki account

Global Preferences Support

Consider using GlobalPreferences extension to sync donor status across all Wikimedia projects. This ensures consistent donor experience regardless of which wiki the user visits.

Consent Management

Obtaining Consent

Consent can be obtained through multiple channels:

1. Post-Donation Dialog - Cookie detection triggers opt-in prompt on Wikipedia
2. Donation Checkout - Checkbox during donation flow
3. Donor Portal - Option to link MediaWiki account
4. URL Code - For text message and postal donors (e.g., ?campaign=wikimedia-donor-onboard)
5. Apps - Native consent flow when donation detected

Consent Storage

In MediaWiki:

• Preference value > 0 indicates consent
• Preference value = -1 indicates explicit opt-out
• Preference value = 0 indicates no consent given

In CiviCRM:

• Separate consent field for "share with MediaWiki"
• Exported in CSV for cross-reference

Revoking Consent

Users can revoke consent via:

• MediaWiki preferences page
• Special page: Special:DonorOptOut
• Donor Portal

When consent is revoked:

• MediaWiki preference set to -1
• CiviCRM notified (via next export or separate mechanism)
• Future syncs skip this user

Implementation Phases

Phase 1: Consent and Basic Identification

MediaWiki (Readers Team):

• Implement consent dialog triggered by donation cookie detection
• Create wikimedia-donor user preference
• Build Special:DonorOptOut page
• Handle URL-based consent activation (?campaign=...)

CiviCRM (Fundraising Tech):

• Add consent checkbox to donation checkout
• Store consent status in CiviCRM
• Implement basic CSV export (contact_id + email + donor_status_id + consent_status)

Outcome: Users can consent to donor identification; basic "is donor" status available

Phase 2: Full Sync Implementation

CiviCRM (Fundraising Tech):

• Extend export with delta logic (modified in last X days)
• Source data from silverpop_export_view_full
• Set up SFTP transfer
• Schedule full export at fiscal year start

MediaWiki (Readers Team / Platform):

• Implement CSV import job
• Add email matching logic and contact_id storage
• Integrate with GlobalPreferences

Outcome: Donor status syncs from CiviCRM to MediaWiki

Security and Privacy Considerations

Privacy Review Requirements

• L3SC (Legal, Security, and Compliance) review required before implementation
• Initial assessment from Privacy Engineer (Samuel Guebo) and Legal (Chuck Roslof) indicates low risk for storing donor flag
• Implementation must undergo formal privacy review

Key Privacy Protections

1. Consent Required - No data synced without explicit user consent
2. Minimal Data - Only donor status stored, not amounts or dates
3. No Public Exposure - User preferences are private by default
4. Verified Email Only - Prevents impersonation via unverified email
5. Opt-Out Available - Users can revoke consent at any time

Risk Mitigations

• Donor status values represent categories, not specific amounts
• Cannot reverse-engineer donation details from status value
• Email-based linking already exists (users already have email in account)
• Adding donor flag does not significantly increase disclosure risk

Dependencies

• 1. Infrastructure
• SFTP server accessible by both CiviCRM and MediaWiki clusters
• MediaWiki job queue infrastructure
• GlobalPreferences extension (if cross-wiki sync required)

• 1. Teams
• Fundraising Tech - CiviCRM export implementation
• Readers Team - MediaWiki consent UI and preference management
• Platform/SRE - Infrastructure setup and job scheduling
• Legal/Privacy - Review and approval of data handling

References

• Fundraising Tech Stack Diagram
• CiviCRM Documentation
• [Silverpop Export Script](internal reference for existing export logic)
• [Donor Audience Framework](internal fundraising reference)