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)
  - system_created_at        When Parable first synced the record
  - system_updated_at        When Parable last synced the record
  - 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 (plural table name, e.g. giving_donations_relationships.donation_id)
  - relationship_type        Type of related entity (e.g. 'Person', 'Fund')
  - 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_donations_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 >= DATE_TRUNC('year', CURRENT_DATE);

-- 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_people_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 mr_person.relationship_id) as member_count,
    SUM(d.amount_cents) / 100.0 as total_giving
FROM planning_center.groups_groups g
JOIN planning_center.groups_memberships_relationships mr_group
    ON mr_group.relationship_id = g.group_id AND mr_group.relationship_type = 'Group'
JOIN planning_center.groups_memberships gm
    ON gm.membership_id = mr_group.membership_id
JOIN planning_center.groups_memberships_relationships mr_person
    ON mr_person.membership_id = gm.membership_id AND mr_person.relationship_type = 'Person'
JOIN planning_center.giving_donations_relationships dr
    ON mr_person.relationship_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 >= DATE_TRUNC('year', CURRENT_DATE)
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_donations_relationships dr
    ON d.donation_id = dr.donation_id
    AND dr.relationship_type = 'Person'                  -- Only person relationships
WHERE d.received_at >= DATE_TRUNC('year', CURRENT_DATE)
GROUP BY DATE_TRUNC('week', d.received_at)
ORDER BY week_start DESC;