Planning Center Giving Data Model

This document provides complete documentation of the Planning Center Giving data model in Parable, including all tables, fields, and relationships.

OverviewCopy Anchor Link

The Giving module contains 19 entity tables and 9 relationship tables supporting donation processing, fund management, pledges, recurring donations, and financial reporting.

Visual Data ModelCopy Anchor Link

Core Entity RelationshipsCopy Anchor Link

Open diagram in new tab →

Key Relationships ExplainedCopy Anchor Link

Donation Flow:

1. A DONATION is created with amount and payment details
2. DESIGNATION(s) split the donation across one or more FUNDs
3. Donations are grouped into BATCHes for processing
4. Batches can be organized into BATCH_GROUPs

Generic Relationship Pattern:

• Donor information stored via giving_donations_relationships (relationship_type: 'Person')
• Campus association via giving_donations_relationships (relationship_type: 'Campus')
• Designation links via giving_donations_relationships (relationship_type: 'Designation')
• Fund links via giving_designations_relationships (relationship_type: 'Fund')
• Payment details via giving_donations_relationships (relationship_type: 'PaymentSource')

Pledge System:

• PLEDGE_CAMPAIGN defines fundraising campaigns
• PLEDGEs are commitments made during campaigns
• Person relationship tracked via giving_pledges_relationships

Recurring Donations:

• RECURRING_DONATION defines the schedule and total amount
• RECURRING_DONATION_DESIGNATIONs split recurring amounts across funds
• Actual donations created by Planning Center on schedule

Refund Handling:

• REFUND represents the refund transaction
• DESIGNATION_REFUNDs track which fund allocations were refunded
• Maintains audit trail of original donation and refund

Query RequirementsCopy Anchor Link

Schema PrefixCopy Anchor Link

IMPORTANT: All tables in the Planning Center Giving module are in the planning_center schema. You MUST prefix all table names with planning_center. in your queries.

✅ CORRECT: SELECT * FROM planning_center.giving_donations ❌ INCORRECT: SELECT * FROM giving_donations

Row Level Security (RLS)Copy Anchor Link

This database uses Row Level Security (RLS) to automatically filter data based on:

• tenant_organization_id: You only see data for your current organization
• system_status: You only see 'active' records by default

DO NOT add these filters to your WHERE clause - they are applied automatically:

• ❌ WHERE tenant_organization_id = 1 (unnecessary)
• ❌ WHERE system_status = 'active' (unnecessary)

The RLS policies ensure you only access data you're authorized to see, making these filters redundant and potentially causing performance issues.

Core Tables OverviewCopy Anchor Link

Primary Entity TablesCopy Anchor Link

• giving_donations - Individual donation transactions
• giving_people - Donor profiles
• giving_funds - Fund definitions for designated giving
• giving_designations - Donation allocations to funds
• giving_batches - Groups of donations processed together
• giving_pledges - Pledge commitments
• giving_pledge_campaigns - Campaign definitions
• giving_recurring_donations - Automated recurring giving

Financial Processing TablesCopy Anchor Link

• giving_batch_groups - Groups of donation batches
• giving_payment_methods - Payment method details
• giving_payment_sources - Sources of payments
• giving_recurring_donation_designations - Fund allocations for recurring
• giving_refunds - Refund transactions
• giving_designation_refunds - Refunds for specific designations
• giving_in_kind_donations - Non-cash (in-kind) donation records

Reference TablesCopy Anchor Link

• giving_campuses - Physical locations/campuses
• giving_organizations - Organization settings
• giving_labels - Categorization tags
• giving_notes - Text notes

Relationship TablesCopy Anchor Link

• giving_donations_relationships - Links donations to related entities
• giving_designations_relationships - Links designations to donations
• giving_pledges_relationships - Links pledges to people
• giving_people_relationships - Links people to other entities
• giving_batches_relationships - Links batches to related entities
• giving_refunds_relationships - Links refunds to donations
• giving_in_kind_donations_relationships - Links in-kind donations to related entities
• giving_recurring_donation_relationships - Links recurring donations to people and funds
• giving_recurring_donation_designation_relationships - Links recurring donation designations to funds (relationship_type = 'Fund')

Table DefinitionsCopy Anchor Link

giving_donationsCopy Anchor Link

Individual donation transactions from donors.

Note: Related entities (person, batch, campus, fund, recurring donation, refund) are linked via giving_donations_relationships using relationship_type values such as 'Person', 'Batch', 'Campus', 'Designation', 'RecurringDonation', 'Refund', etc.

giving_peopleCopy Anchor Link

Donor profiles and information.

giving_fundsCopy Anchor Link

Fund definitions for designated giving.

giving_designationsCopy Anchor Link

How donations are allocated to specific funds.

giving_batchesCopy Anchor Link

Groups of donations processed together.

giving_pledgesCopy Anchor Link

Pledge commitments for campaigns.

Note: Pledges connect to pledge campaigns and people via giving_pledges_relationships using relationship_type values 'PledgeCampaign' and 'Person'. There is no direct pledge_campaign_id or received_cents column.

giving_pledge_campaignsCopy Anchor Link

Campaign definitions for pledge drives.

giving_recurring_donationsCopy Anchor Link

Automated recurring giving setups.

giving_batch_groupsCopy Anchor Link

Groups of donation batches for organizational purposes.

giving_payment_methodsCopy Anchor Link

Payment method details for recurring donations.

giving_payment_sourcesCopy Anchor Link

Sources of payments (broader category than payment methods).

giving_refundsCopy Anchor Link

Refund transactions for donations.

giving_campusesCopy Anchor Link

Physical locations/campuses for the organization.

giving_organizationsCopy Anchor Link

Organization-level settings and information for giving.

giving_labelsCopy Anchor Link

Categorization tags for giving-related entities.

giving_notesCopy Anchor Link

Text notes attached to giving-related entities.

giving_recurring_donation_designationsCopy Anchor Link

Fund allocations for recurring donations.

giving_designation_refundsCopy Anchor Link

Refunds for specific designations within donations.

giving_in_kind_donationsCopy Anchor Link

Non-cash (in-kind) donation records such as property, goods, or services.

Note: Related entities (person, fund, campus) are linked via giving_in_kind_donations_relationships using the appropriate relationship_type values.

Relationship TablesCopy Anchor Link

giving_donations_relationshipsCopy Anchor Link

Links donations to related entities beyond the direct ID columns.

giving_designations_relationshipsCopy Anchor Link

Links designations to donations and other entities.

giving_pledges_relationshipsCopy Anchor Link

Links pledges to people and campaigns.

giving_people_relationshipsCopy Anchor Link

Links people to other entities beyond the direct ID columns.

giving_batches_relationshipsCopy Anchor Link

Links batches to related entities.

giving_refunds_relationshipsCopy Anchor Link

Links refunds to related entities.

giving_in_kind_donations_relationshipsCopy Anchor Link

Links in-kind donations to related entities.

System FieldsCopy Anchor Link

All tables include these system fields for data management:

• tenant_organization_id - Multi-tenant organization identifier
• system_status - Data lifecycle status:
  • transferring - Being imported from Planning Center
  • active - Current active data
  • stale - Marked for removal
• system_created_at - When record was created in Parable
• system_updated_at - When record was last updated in Parable

Common Query PatternsCopy Anchor Link

Getting Donations with Donor InformationCopy Anchor Link

-- CORRECT: Schema prefix included, no manual RLS filters
-- giving_donations has no direct person_id column; join via giving_donations_relationships
SELECT 
    d.donation_id,
    d.amount_cents / 100.0 as amount,
    d.amount_currency,
    p.first_name,
    p.last_name,
    p.donor_number
FROM planning_center.giving_donations d
LEFT JOIN planning_center.giving_donations_relationships dr
    ON d.donation_id = dr.donation_id
    AND dr.relationship_type = 'Person'
LEFT JOIN planning_center.giving_people p 
    ON dr.relationship_id = p.person_id
WHERE d.received_at >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY d.received_at DESC;

Donation Designations by FundCopy Anchor Link

-- giving_designations has no direct fund_id or donation_id columns;
-- use the relationship tables to join funds → designations → donations
SELECT 
    f.name as fund_name,
    COUNT(DISTINCT des.designation_id) as designation_count,
    SUM(des.amount_cents) / 100.0 as total_amount
FROM planning_center.giving_funds f
-- Join funds to designations via designation_relationships (type='Fund')
JOIN planning_center.giving_designations_relationships desr
    ON desr.relationship_id = f.fund_id
    AND desr.relationship_type = 'Fund'
JOIN planning_center.giving_designations des
    ON des.designation_id = desr.designation_id
-- Join designations to donations via donation_relationships (type='Designation')
JOIN planning_center.giving_donations_relationships dr_des
    ON dr_des.relationship_id = des.designation_id
    AND dr_des.relationship_type = 'Designation'
JOIN planning_center.giving_donations d
    ON d.donation_id = dr_des.donation_id
WHERE d.received_at >= DATE_TRUNC('month', CURRENT_DATE)
    AND d.refunded = false
GROUP BY f.fund_id, f.name
ORDER BY total_amount DESC;

Batch Summary ReportCopy Anchor Link

-- giving_donations has no direct batch_id column;
-- join via giving_donations_relationships (relationship_type='Batch')
SELECT 
    b.batch_id,
    b.description,
    b.committed_at,
    b.total_cents / 100.0 as batch_total,
    b.donations_count,
    COUNT(d.donation_id) as calculated_donation_count,
    SUM(d.amount_cents) / 100.0 as calculated_total
FROM planning_center.giving_batches b
LEFT JOIN planning_center.giving_donations_relationships dr
    ON dr.relationship_id = b.batch_id
    AND dr.relationship_type = 'Batch'
LEFT JOIN planning_center.giving_donations d
    ON d.donation_id = dr.donation_id
WHERE b.status = 'committed'
GROUP BY b.batch_id, b.description, b.committed_at, b.total_cents, b.donations_count
ORDER BY b.committed_at DESC;

Recurring Donation StatusCopy Anchor Link

-- giving_donations has no direct recurring_donation_id or person_id columns;
-- find person via donation_relationships using RecurringDonation→Person chain
SELECT DISTINCT ON (rd.recurring_donation_id)
    rd.recurring_donation_id,
    rd.amount_cents / 100.0 as amount,
    rd.status,
    rd.last_donation_received_at,
    rd.next_occurrence,
    p.first_name,
    p.last_name
FROM planning_center.giving_recurring_donations rd
-- Find donations linked to this recurring donation
LEFT JOIN planning_center.giving_donations_relationships dr_rd
    ON dr_rd.relationship_id = rd.recurring_donation_id
    AND dr_rd.relationship_type = 'RecurringDonation'
-- Get the person from that same donation
LEFT JOIN planning_center.giving_donations_relationships dr_p
    ON dr_p.donation_id = dr_rd.donation_id
    AND dr_p.relationship_type = 'Person'
LEFT JOIN planning_center.giving_people p
    ON p.person_id = dr_p.relationship_id
WHERE rd.status = 'active'
ORDER BY rd.recurring_donation_id, rd.next_occurrence;

Data Integrity RulesCopy Anchor Link

1. Schema Qualification: Always use planning_center. prefix for all table references
2. Row Level Security: RLS automatically handles multi-tenancy and status filtering - do not add manual filters
3. Monetary Values: All amounts are stored in cents - divide by 100.0 for display
4. Refunded Donations: Filter refunded = true when needed for financial reports
5. Relationship Tables: All entity-to-entity links (donations→person, donations→batch, designations→fund, pledges→campaign, etc.) go through relationship tables — there are no direct FK columns on these tables

Common Mistakes to AvoidCopy Anchor Link

1. Missing Schema Prefix

   • ❌ FROM giving_donations
   • ✅ FROM planning_center.giving_donations

2. Adding Redundant RLS Filters

   • ❌ WHERE tenant_organization_id = 1 AND system_status = 'active'
   • ✅ Trust RLS to handle this automatically

3. Joining Without Schema

   • ❌ JOIN giving_people p ON ...
   • ✅ JOIN planning_center.giving_people p ON ...

4. Forgetting Currency Conversion

   • ❌ SELECT amount_cents as amount (displays cents)
   • ✅ SELECT amount_cents / 100.0 as amount (displays dollars)

Performance ConsiderationsCopy Anchor Link

1. Indexes: All tables have optimized indexes on:

   • Primary keys and entity IDs
   • Join columns and foreign keys
   • Date columns for time-based queries

2. Query Optimization:

   • Always use the planning_center. schema prefix
   • RLS handles tenant and status filtering automatically
   • Filter refunded donations when needed
   • Consider CTEs for complex aggregations
   • Join through the *_relationships tables — entity tables carry no foreign-key columns

Data Types and ConventionsCopy Anchor Link

Monetary ValuesCopy Anchor Link

• All amounts stored in cents (INTEGER)
• Divide by 100.0 for dollar amounts
• Currency codes follow ISO 4217 (USD, CAD, EUR, etc.)

Dates and TimesCopy Anchor Link

• TIMESTAMP fields represent UTC time
• DATE fields for date-only values (no time component)
• All times stored without timezone info (implicit UTC)

Payment MethodsCopy Anchor Link

• payment_method - Type (cash, check, card, ach)
• payment_method_sub - Subtype details
• payment_status - Transaction status

Boolean ValuesCopy Anchor Link

• TRUE/FALSE for PostgreSQL boolean type
• No NULL booleans - default to FALSE where appropriate

Next StepsCopy Anchor Link

• Start with Basic Queries for simple examples
• Progress to Advanced Queries for complex analysis
• Use Reporting Examples for production reports
• Return to Overview for overview