Report on Volunteer Hours - Virtuous API Docs

A reporting integration’s central question: how many hours did our volunteers serve, and where? The data exists in VOMO — every Participation captures hours, signed_up_at, checked_in_at, checked_out_at, and the linkage to a Project Date. But there’s no GET /participations endpoint to pull it directly. Hours reporting requires walking either users (via /users/{id} → embedded participations) or Project Dates (via /projects/date/{id} → embedded participants). This recipe walks through both approaches: when each is right, how to aggregate efficiently, how to build incremental reporting that doesn’t re-scan everything every time, and the performance patterns for large datasets.

What you’ll build

A reporting pipeline that:

Collects all volunteer participation data despite no direct participations endpoint
Aggregates hours by user, project, organization, time period, and other dimensions
Builds incremental reports that update rather than fully recompute
Stores aggregated results for fast querying by downstream BI tools or dashboards
Surfaces hours-related insights (trends, top volunteers, project performance)

When this recipe fits

Scenario	This recipe fits
Annual volunteer hours summary report	✓
BI dashboard showing hours trends	✓
Top-volunteer recognition rankings	✓
Per-project performance metrics	✓
Compliance reporting (volunteer-hour grants, audited financials)	✓ With reconciliation rigor
Real-time “hours so far today” display	✗ Real-time is hard without webhooks; consider hourly minimum

The data flow

Five components:

Component	Purpose
Participation collector	Pulls participation records from VOMO via the available endpoints
Raw participation store	Local database of every participation record, keyed by `(project_date_id, user_id)`
Hours aggregator	Computes summary statistics from the raw records
Aggregated reports	Pre-computed tables for fast querying (by user, project, period, etc.)
BI / dashboards	Customer-facing or partner-facing displays

The raw store is the integration’s canonical record of participations — once collected, aggregation is cheap and re-aggregation for different reports is straightforward.

Two collection strategies

The fundamental question: how do you collect participation records when there’s no direct participations endpoint?

Strategy A: walk Project Dates

For each upcoming or recent Project Date, fetch its participants:

JavaScript

async function collectFromProjectDates(customerId, sinceDate, untilDate) {
  // 1. Find Projects with Dates in the window
  const params = new URLSearchParams({
    dates_after: sinceDate.toISOString(),
    dates_before: untilDate.toISOString(),
  });
  const projects = await paginate(
    `https://api.vomo.org/v1/projects?${params}`,
    customerId
  );

  const records = [];

  // 2. For each Project, walk its Dates in the window
  for (const projectSummary of projects) {
    const project = await getProjectDetail(projectSummary.id, customerId);
    const datesInWindow = (project.all_dates ?? []).filter((d) => {
      const dStart = new Date(d.starts_at);
      return dStart >= sinceDate && dStart <= untilDate;
    });

    for (const date of datesInWindow) {
      const dateDetail = await getProjectDate(date.id, customerId);
      for (const participant of dateDetail.data?.participants ?? []) {
        records.push({
          userId: participant.id,
          projectId: project.id,
          projectName: project.name,
          projectDateId: date.id,
          startsAt: new Date(date.starts_at),
          endsAt: new Date(date.ends_at),
          checkedInAt: participant.checked_in_at ? new Date(participant.checked_in_at) : null,
          checkedOutAt: participant.checked_out_at ? new Date(participant.checked_out_at) : null,
          signedUpAt: participant.signed_up_at ? new Date(participant.signed_up_at) : null,
          hours: parseFloat(participant.hours) || 0, // audit #40: hours is string
          verified: participant.verified === 1 || participant.verified === true,
          role: participant.role,
          guests: participant.guests ?? 0,
        });
      }
    }
  }

  return records;
}

Cost: One Projects-list query + one Project-detail-fetch per Project + one Project-Date-detail-fetch per Date. For an account with 50 active Projects each with 4 weekly Dates over a month, that’s roughly 1 + 50 + 200 = 251 requests per scan.

Strategy B: walk Users

For each User, fetch their detail (which embeds participations):

JavaScript

async function collectFromUsers(customerId, sinceDate, untilDate) {
  const allUsers = await paginate('https://api.vomo.org/v1/users', customerId);

  const records = [];

  for (const userSummary of allUsers) {
    const detail = await getUserDetail(userSummary.id, customerId);
    const participations = (detail.participations ?? []).filter((p) => {
      const signedUpAt = p.signed_up_at ? new Date(p.signed_up_at) : null;
      return signedUpAt && signedUpAt >= sinceDate && signedUpAt <= untilDate;
    });

    for (const p of participations) {
      records.push({
        userId: detail.id,
        userEmail: detail.email,
        userName: detail.full_name,
        projectId: parseInt(p.project_id, 10), // audit #41: typed string
        projectDateId: parseInt(p.project_date_id, 10),
        checkedInAt: p.checked_in_at ? new Date(p.checked_in_at) : null,
        checkedOutAt: p.checked_out_at ? new Date(p.checked_out_at) : null,
        signedUpAt: p.signed_up_at ? new Date(p.signed_up_at) : null,
        hours: parseFloat(p.hours) || 0,
        verified: p.verified ?? false,
        role: p.role,
        guests: p.guests ?? 0,
      });
    }
  }

  return records;
}

Cost: One Users-list query + one User-detail-fetch per User. For an account with 5,000 users, that’s 5,001 requests per scan — significantly more than Strategy A.

Which to use

Strategy	Best for
A: Walk Project Dates	Reporting focused on specific Projects or time windows; most common case
B: Walk Users	Reporting focused on per-user hours summaries; when you need full user context (email, profile fields)
A then B	Reconciliation — use A primarily, B periodically to catch participations missed by Project filter

For most production reporting, Strategy A is the default. It’s cheaper, the data captured is richer (includes Project name), and Project-Date-based collection aligns naturally with reporting dimensions.

Step 1: incremental collection

Re-scanning the entire dataset on every refresh is wasteful. Build incremental collection:

JavaScript

class IncrementalParticipationCollector {
  constructor({ customerId, token, store }) {
    this.customerId = customerId;
    this.token = token;
    this.store = store; // Raw participation store
    this.client = new ThrottledVomoClient({ token, requestsPerSecond: 3 });
  }

  async incrementalCollect() {
    const lastCollection = await this.store.getLastCollectionTime(this.customerId);
    const now = new Date();

    // For incremental, scan Project Dates that started after lastCollection
    // OR are still in the future (because participation can change after the Date)
    const params = new URLSearchParams({
      dates_after: lastCollection.toISOString(),
      // No dates_before — collect forward in time including future Dates
    });

    const projects = await paginate(
      `https://api.vomo.org/v1/projects?${params}`,
      this.customerId
    );

    let processedDates = 0;
    let totalParticipations = 0;

    for (const projectSummary of projects) {
      const project = await this._getCachedProjectDetail(projectSummary.id);

      const datesToScan = (project.all_dates ?? []).filter((d) => {
        const dStart = new Date(d.starts_at);
        return dStart >= lastCollection;
      });

      for (const date of datesToScan) {
        const dateDetail = await this.client.fetch(
          `https://api.vomo.org/v1/projects/date/${date.id}`
        );
        const participants = (await dateDetail.json()).data?.participants ?? [];

        for (const p of participants) {
          await this._upsertParticipationRecord(project, date, p);
          totalParticipations++;
        }
        processedDates++;
      }
    }

    await this.store.setLastCollectionTime(this.customerId, now);

    return { processedDates, totalParticipations };
  }

  async _upsertParticipationRecord(project, date, participant) {
    await this.store.upsertParticipation({
      customer_id: this.customerId,
      project_id: project.id,
      project_name: project.name,
      project_date_id: date.id,
      user_id: participant.id,
      user_email: participant.email,
      starts_at: new Date(date.starts_at),
      ends_at: new Date(date.ends_at),
      checked_in_at: participant.checked_in_at ? new Date(participant.checked_in_at) : null,
      checked_out_at: participant.checked_out_at ? new Date(participant.checked_out_at) : null,
      signed_up_at: participant.signed_up_at ? new Date(participant.signed_up_at) : null,
      hours: parseFloat(participant.hours) || 0,
      verified: participant.verified === 1 || participant.verified === true,
      role: participant.role,
      guests: participant.guests ?? 0,
      synced_at: new Date(),
    });
  }

  _projectCache = new Map();
  async _getCachedProjectDetail(projectId) {
    if (this._projectCache.has(projectId)) return this._projectCache.get(projectId);
    const detail = await fetchProjectDetail(projectId, this.client);
    this._projectCache.set(projectId, detail);
    return detail;
  }
}

Two key patterns:

Pattern	Why
Upsert by `(project_date_id, user_id)`	Participation records can be modified after creation (check-ins, hour adjustments). Upsert handles both new records and modifications.
Cache Project details within a single scan	Many Project Dates belong to the same Project. Caching prevents N redundant Project-detail fetches.

Raw store schema

CREATE TABLE participations (
  customer_id      VARCHAR NOT NULL,
  project_date_id  INTEGER NOT NULL,
  user_id          INTEGER NOT NULL,
  project_id       INTEGER NOT NULL,
  project_name     VARCHAR,
  user_email       VARCHAR,
  starts_at        TIMESTAMP,
  ends_at          TIMESTAMP,
  checked_in_at    TIMESTAMP,
  checked_out_at   TIMESTAMP,
  signed_up_at     TIMESTAMP,
  hours            DECIMAL(10, 2),
  verified         BOOLEAN,
  role             VARCHAR,
  guests           INTEGER,
  synced_at        TIMESTAMP,
  PRIMARY KEY (customer_id, project_date_id, user_id)
);

CREATE INDEX participations_by_user ON participations (customer_id, user_id);
CREATE INDEX participations_by_project ON participations (customer_id, project_id);
CREATE INDEX participations_by_time ON participations (customer_id, signed_up_at);

The composite primary key (project_date_id, user_id) is the natural unique identifier for a participation. Indexes by user, by project, and by time support the common aggregation queries.

Step 2: aggregate hours

Once raw participations are in the local store, aggregation is fast:

Hours per user

SELECT
  user_id,
  user_email,
  COUNT(*) AS participation_count,
  SUM(hours) AS total_hours,
  COUNT(DISTINCT project_id) AS unique_projects,
  MIN(signed_up_at) AS first_participated_at,
  MAX(signed_up_at) AS last_participated_at
FROM participations
WHERE customer_id = :customerId
  AND verified = true
GROUP BY user_id, user_email
ORDER BY total_hours DESC;

Hours per project

SELECT
  project_id,
  project_name,
  COUNT(*) AS total_participations,
  COUNT(DISTINCT user_id) AS unique_volunteers,
  SUM(hours) AS total_hours,
  AVG(hours) AS avg_hours_per_participation,
  MIN(starts_at) AS first_date,
  MAX(starts_at) AS most_recent_date
FROM participations
WHERE customer_id = :customerId
  AND verified = true
GROUP BY project_id, project_name
ORDER BY total_hours DESC;

Hours per time period

SELECT
  DATE_TRUNC('month', signed_up_at) AS month,
  COUNT(*) AS participations,
  COUNT(DISTINCT user_id) AS unique_volunteers,
  SUM(hours) AS total_hours
FROM participations
WHERE customer_id = :customerId
  AND verified = true
  AND signed_up_at >= :startDate
GROUP BY DATE_TRUNC('month', signed_up_at)
ORDER BY month DESC;

Each query maps to a specific reporting question. With proper indexes, these queries respond in milliseconds even for large datasets.

Step 3: pre-computed aggregations

For high-traffic dashboards, even fast SQL queries can be slow at scale. Materialize aggregations:

CREATE MATERIALIZED VIEW monthly_volunteer_summary AS
SELECT
  customer_id,
  DATE_TRUNC('month', signed_up_at) AS month,
  COUNT(*) AS participations,
  COUNT(DISTINCT user_id) AS unique_volunteers,
  SUM(hours) AS total_hours,
  COUNT(DISTINCT project_id) AS unique_projects_engaged
FROM participations
WHERE verified = true
GROUP BY customer_id, DATE_TRUNC('month', signed_up_at);

CREATE INDEX msv_idx ON monthly_volunteer_summary (customer_id, month);

Refresh on a schedule that matches reporting needs (hourly for dashboards, daily for reports):

JavaScript

async function refreshAggregations() {
  await db.query('REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_volunteer_summary');
  await db.query('REFRESH MATERIALIZED VIEW CONCURRENTLY per_user_hours_summary');
  await db.query('REFRESH MATERIALIZED VIEW CONCURRENTLY per_project_hours_summary');
}

// Schedule
scheduler.cron('refresh_volunteer_aggregations', '0 * * * *', refreshAggregations); // Hourly

The materialized views become the fast path for dashboards. The raw participation table is still queryable for ad-hoc reports.

Step 4: report-specific patterns

Different reports need different shapes. A few common ones:

Top volunteers ranking

JavaScript

async function getTopVolunteers(customerId, period, limit = 50) {
  const result = await db.query(`
    SELECT
      user_id,
      user_email,
      SUM(hours) AS total_hours,
      COUNT(*) AS participation_count,
      COUNT(DISTINCT project_id) AS project_count
    FROM participations
    WHERE customer_id = $1
      AND verified = true
      AND signed_up_at >= $2
      AND signed_up_at < $3
    GROUP BY user_id, user_email
    ORDER BY total_hours DESC
    LIMIT $4
  `, [customerId, period.start, period.end, limit]);

  return result.rows;
}

Volunteer engagement trend

JavaScript

async function getEngagementTrend(customerId, monthsBack = 12) {
  const result = await db.query(`
    SELECT
      DATE_TRUNC('month', signed_up_at) AS month,
      COUNT(DISTINCT user_id) AS active_volunteers,
      SUM(hours) AS total_hours
    FROM participations
    WHERE customer_id = $1
      AND signed_up_at >= NOW() - INTERVAL '${monthsBack} months'
      AND verified = true
    GROUP BY DATE_TRUNC('month', signed_up_at)
    ORDER BY month
  `, [customerId]);

  return result.rows;
}

Retention cohort analysis

JavaScript

async function getCohortRetention(customerId, cohortMonth) {
  // First, find users whose first participation was in the cohort month
  const cohortStart = startOfMonth(cohortMonth);
  const cohortEnd = startOfMonth(addMonths(cohortMonth, 1));

  const result = await db.query(`
    WITH cohort AS (
      SELECT DISTINCT user_id
      FROM participations
      WHERE customer_id = $1
        AND signed_up_at >= $2
        AND signed_up_at < $3
        AND user_id NOT IN (
          SELECT DISTINCT user_id FROM participations
          WHERE customer_id = $1 AND signed_up_at < $2
        )
    )
    SELECT
      DATE_TRUNC('month', p.signed_up_at) AS month,
      COUNT(DISTINCT p.user_id) AS retained_users
    FROM cohort c
    JOIN participations p ON p.user_id = c.user_id
    WHERE p.customer_id = $1
    GROUP BY DATE_TRUNC('month', p.signed_up_at)
    ORDER BY month
  `, [customerId, cohortStart, cohortEnd]);

  return result.rows;
}

Retention cohorts answer “of users who first volunteered in May, how many came back in June, July, etc.” — useful for understanding volunteer engagement quality.

Hours-by-organization (within the org family)

JavaScript

async function getHoursByOrg(customerId, period) {
  // First, fetch the Project → Organization mapping from VOMO
  const projectOrgMap = await fetchProjectOrgMapping(customerId);

  const result = await db.query(`
    SELECT
      project_id,
      SUM(hours) AS total_hours,
      COUNT(*) AS participation_count
    FROM participations
    WHERE customer_id = $1
      AND verified = true
      AND signed_up_at >= $2
      AND signed_up_at < $3
    GROUP BY project_id
  `, [customerId, period.start, period.end]);

  // Group by org
  const byOrg = new Map();
  for (const row of result.rows) {
    const orgSlug = projectOrgMap.get(row.project_id);
    if (!byOrg.has(orgSlug)) byOrg.set(orgSlug, { hours: 0, participations: 0 });
    byOrg.get(orgSlug).hours += parseFloat(row.total_hours);
    byOrg.get(orgSlug).participations += parseInt(row.participation_count);
  }

  return [...byOrg.entries()].map(([slug, stats]) => ({ orgSlug: slug, ...stats }));
}

Useful for multi-org customers — “the Gotham Outreach branch contributed X hours; the Wayne Manor branch contributed Y.”

Step 5: handle unverified vs. verified hours

Participations have a verified flag — true when the organizer confirmed attendance, false for self-reported or pending. Reports typically distinguish:

Audience	What to count
Internal operations dashboards	All participations (verified + unverified) — gives complete picture
Customer-facing reports	Verified only — these are the “confirmed” hours
Compliance / grant reporting	Verified only, with explicit “as of X date” stamp
Trend analysis	Both, separately tracked

For most reports, verified-only is the canonical metric. Make the distinction explicit in dashboard labels: “Total verified hours: 2,450” rather than just “Total hours.”

Step 6: reconciliation and accuracy

Reports about hours need to be accurate — undercounting embarrasses the customer; overcounting embarrasses the integration.

Daily verification scan

JavaScript

async function dailyHoursVerification(customerId) {
  const yesterday = startOfDayUTC(daysAgo(1));
  const today = startOfDayUTC(daysAgo(0));

  // 1. Re-collect participations from yesterday's Project Dates
  const collector = new IncrementalParticipationCollector({
    customerId,
    token: await credentials.getVomoToken(customerId),
    store: participationStore,
  });

  // Force re-collection of yesterday's window (override checkpoint)
  await collector.collectWindow(yesterday, today);

  // 2. Compare to what's in our store
  const expectedFromVomo = await collector.lastWindowParticipations();
  const storedRecords = await participationStore.getInWindow(customerId, yesterday, today);

  const gaps = expectedFromVomo.filter((expected) =>
    !storedRecords.some((stored) =>
      stored.projectDateId === expected.projectDateId &&
      stored.userId === expected.userId
    )
  );

  if (gaps.length > 0) {
    await alertOps({
      severity: 'medium',
      customerId,
      type: 'hours_reconciliation_gap',
      gapCount: gaps.length,
    });

    // Re-process gaps
    for (const gap of gaps) {
      await collector.processOne(gap);
    }
  }

  return { gapCount: gaps.length };
}

Daily reconciliation against yesterday’s data catches most gaps within a 24-hour window.

Verified vs. final-version drift

Sometimes a participation’s hours field changes after initial capture — organizers may adjust hours post-event. The incremental upsert handles this automatically (new value overwrites old), but you may want to track when changes happen:

JavaScript

async function upsertWithChangeTracking(newRecord) {
  const existing = await participationStore.get(
    newRecord.customer_id,
    newRecord.project_date_id,
    newRecord.user_id
  );

  if (existing && existing.hours !== newRecord.hours) {
    // Hours changed since last sync — log it
    await db.insert('hours_changes', {
      customer_id: newRecord.customer_id,
      project_date_id: newRecord.project_date_id,
      user_id: newRecord.user_id,
      previous_hours: existing.hours,
      new_hours: newRecord.hours,
      changed_at: new Date(),
    });
  }

  return participationStore.upsert(newRecord);
}

For audited reports, the change log is essential — it provides explainability when totals differ between report runs.

Performance at scale

A few patterns that help reports scale:

Partition the raw store

For customers with millions of participations, partition by time:

CREATE TABLE participations (
  -- columns ...
) PARTITION BY RANGE (signed_up_at);

CREATE TABLE participations_2024 PARTITION OF participations
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE participations_2025 PARTITION OF participations
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

Queries with time filters scan only relevant partitions.

Tiered aggregation

For dashboards needing fast responses:

Tier	Storage	Refresh cadence
Raw participations	Local DB	Continuous (via collector)
Daily aggregates	Materialized view	Hourly
Weekly aggregates	Materialized view	Daily
Monthly summaries	Materialized view	Daily
Annual reports	Materialized view	Weekly

The dashboard hits the materialized view at the appropriate granularity; the raw data remains for ad-hoc queries.

Pre-computed user lookups

For per-user dashboards (e.g., “show me my volunteer history”), pre-compute per-user summaries:

CREATE MATERIALIZED VIEW user_lifetime_summary AS
SELECT
  customer_id,
  user_id,
  user_email,
  SUM(hours) AS lifetime_hours,
  COUNT(*) AS lifetime_participations,
  MIN(signed_up_at) AS first_participated,
  MAX(signed_up_at) AS most_recent_participation,
  COUNT(DISTINCT project_id) AS unique_projects
FROM participations
WHERE verified = true
GROUP BY customer_id, user_id, user_email;

A “show me my history” query becomes a single-row lookup instead of an aggregation.

Things to watch for

Hours field is a string

The hours field is returned as a string ("4.00") per audit #40. Always parse:

JavaScript

const hours = parseFloat(participation.hours) || 0;

The || 0 defends against null or invalid values.

Participation modifications happen post-event

Organizers commonly adjust hours, verify participations, and add notes after a shift ends. The incremental collector picks these up because Project Dates’ participants can change. But don’t assume yesterday’s data is final — the collector should keep re-scanning recent Project Dates (e.g., last 14 days) for changes.

Unverified participations are noisy

verified: false participations are often self-reported or pending — including them in reports can produce inflated numbers. Default to verified: true only for customer-facing reports.

Project deletion can orphan participations

If a Project is deleted in VOMO (admin action), its Project Dates and embedded participations disappear. Your local store still has them — they’ll appear in historical reports but not in current VOMO state. Decide whether to keep them (historical accuracy) or remove them (current accuracy). Typically: keep them with a vomo_deleted_at timestamp, so historical reports remain accurate but current state shows “this Project no longer exists in VOMO.”

Multiple participations per user per Date are unusual but possible

The primary key (project_date_id, user_id) enforces one row per user per Date. If VOMO ever returns multiple participation entries for the same (user, date) tuple, the upsert will collapse them into one. This is almost certainly the right behavior, but be aware of it for reports that involve participation counts.

What you’ve built

After this recipe:

✅ A collector that pulls participation records from VOMO via Project Dates
✅ A local raw store keyed by (project_date_id, user_id)
✅ Incremental collection that doesn’t re-scan everything every run
✅ Aggregated views for the common reporting questions
✅ Pre-computed materialized views for fast dashboards
✅ Daily reconciliation catching gaps
✅ Change tracking for hours modifications

This is a reporting integration’s foundation. Specific dashboards and reports build on top of these materialized aggregates.

Where to go next

Combine Volunteer Data with CRM+ Data

The cross-API recipe joining Volunteer hours with CRM+ donor data.

Build a Volunteer Self-Service Portal

The end-user-facing recipe using participation data.

The Volunteer Data Model

The data model context for participations.

Reconciliation Patterns

The reconciliation patterns this recipe uses.

​What you’ll build

​When this recipe fits

​The data flow

​Two collection strategies

​Strategy A: walk Project Dates

​Strategy B: walk Users

​Which to use

​Step 1: incremental collection

​Raw store schema

​Step 2: aggregate hours

​Hours per user

​Hours per project

​Hours per time period

​Step 3: pre-computed aggregations

​Step 4: report-specific patterns

​Top volunteers ranking

​Volunteer engagement trend

​Retention cohort analysis

​Hours-by-organization (within the org family)

​Step 5: handle unverified vs. verified hours

​Step 6: reconciliation and accuracy

​Daily verification scan

​Verified vs. final-version drift

​Performance at scale

​Partition the raw store

​Tiered aggregation

​Pre-computed user lookups

​Things to watch for

​Hours field is a string

​Participation modifications happen post-event

​Unverified participations are noisy

​Project deletion can orphan participations

​Multiple participations per user per Date are unusual but possible

​What you’ve built

​Where to go next

Combine Volunteer Data with CRM+ Data

Build a Volunteer Self-Service Portal

The Volunteer Data Model

Reconciliation Patterns

What you’ll build

When this recipe fits

The data flow

Two collection strategies

Strategy A: walk Project Dates

Strategy B: walk Users

Which to use

Step 1: incremental collection

Raw store schema

Step 2: aggregate hours

Hours per user

Hours per project

Hours per time period

Step 3: pre-computed aggregations

Step 4: report-specific patterns

Top volunteers ranking

Volunteer engagement trend

Retention cohort analysis

Hours-by-organization (within the org family)

Step 5: handle unverified vs. verified hours

Step 6: reconciliation and accuracy

Daily verification scan

Verified vs. final-version drift

Performance at scale

Partition the raw store

Tiered aggregation

Pre-computed user lookups

Things to watch for

Hours field is a string

Participation modifications happen post-event

Unverified participations are noisy

Project deletion can orphan participations

Multiple participations per user per Date are unusual but possible

What you’ve built

Where to go next