Overview

Planning Center API → Parable Sync Engine → PostgreSQL Database → Your SQL Queries

-- Main entity table
planning_center.{app}_{entity}
  - {entity}_id              -- Planning Center's ID
  - tenant_organization_id   -- Your church's ID
  - system_status           -- Data lifecycle (active/transferring/stale)
  - created_at              -- When created in Planning Center
  - updated_at              -- When last updated in Planning Center
  - [entity fields]         -- The actual data fields

-- Relationship table (for connections between entities)
planning_center.{app}_{entity}_relationships
  - {entity}_id             -- Parent entity
  - relationship_type       -- Type of related entity
  - relationship_id         -- ID of related entity

-- Find all donations with donor information
SELECT
    d.donation_id,
    d.amount_cents / 100.0 as amount,
    p.first_name,
    p.last_name
FROM planning_center.giving_donations d
JOIN planning_center.giving_donation_relationships dr
    ON d.donation_id = dr.donation_id
    AND dr.relationship_type = 'Person'
JOIN planning_center.giving_people p
    ON dr.relationship_id = p.person_id
WHERE d.received_at >= '2024-01-01';

-- Get all people
SELECT
    person_id,
    first_name,
    last_name,
    membership
FROM planning_center.people_people;

-- Get people with their household
SELECT
    p.first_name,
    p.last_name,
    h.name as household_name
FROM planning_center.people_people p
JOIN planning_center.people_person_relationships pr
    ON p.person_id = pr.person_id
    AND pr.relationship_type = 'Household'
JOIN planning_center.people_households h
    ON pr.relationship_id = h.household_id;

-- Find giving totals by group membership
SELECT
    g.name as group_name,
    COUNT(DISTINCT gm.person_id) as member_count,
    SUM(d.amount_cents) / 100.0 as total_giving
FROM planning_center.groups_groups g
JOIN planning_center.groups_memberships gm
    ON g.group_id = gm.group_id
JOIN planning_center.giving_donation_relationships dr
    ON gm.person_id = dr.relationship_id
    AND dr.relationship_type = 'Person'
JOIN planning_center.giving_donations d
    ON dr.donation_id = d.donation_id
WHERE d.received_at >= '2024-01-01'
GROUP BY g.name;

-- Get weekly giving totals with donor counts
SELECT
    DATE_TRUNC('week', d.received_at) as week_start,
    COUNT(DISTINCT dr.relationship_id) as unique_donors,  -- Count unique people
    SUM(d.amount_cents) / 100.0 as total_amount          -- Convert cents to dollars
FROM planning_center.giving_donations d
JOIN planning_center.giving_donation_relationships dr
    ON d.donation_id = dr.donation_id
    AND dr.relationship_type = 'Person'                  -- Only person relationships
WHERE d.received_at >= '2024-01-01'
GROUP BY DATE_TRUNC('week', d.received_at)
ORDER BY week_start DESC;