​

Planning Center Giving Data Model

​

Overview

​

Visual Data Model

​

Core Entity Relationships

​

Key Relationships Explained

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

• Donor information stored via giving_donation_relationships (relationship_type: person)
• Campus association via giving_donation_relationships (relationship_type: campus)
• Payment details via giving_donation_relationships (relationship_type: payment_method, payment_source)

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

• 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 represents the refund transaction
• DESIGNATION_REFUNDs track which fund allocations were refunded
• Maintains audit trail of original donation and refund

​

Query Requirements

​

Schema Prefix

​

Row Level Security (RLS)

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

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

​

Core Tables Overview

​

Primary Entity Tables

• 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 Tables

• 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

​

Reference Tables

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

​

Relationship Tables

• giving_donation_relationships - Links donations to related entities
• giving_designation_relationships - Links designations to donations
• giving_pledge_relationships - Links pledges to people
• giving_person_relationships - Links people to other entities
• giving_batch_relationships - Links batches to related entities
• giving_refund_relationships - Links refunds to donations

​

Table Definitions

​

giving_donations

​

giving_people

​

giving_funds

​

giving_designations

​

giving_batches

​

giving_pledges

​

giving_pledge_campaigns

​

giving_recurring_donations

​

giving_batch_groups

​

giving_payment_methods

​

giving_payment_sources

​

giving_refunds

​

giving_campuses

​

giving_organizations

​

giving_labels

​

giving_notes

​

giving_recurring_donation_designations

​

giving_designation_refunds

​

Relationship Tables

​

giving_donation_relationships

​

giving_designation_relationships

​

giving_pledge_relationships

​

giving_person_relationships

​

giving_batch_relationships

​

giving_refund_relationships

​

System Fields

• 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 Patterns

​

Getting Donations with Donor Information

-- CORRECT: Schema prefix included, no manual RLS filters
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_people p 
    ON d.person_id = p.person_id
WHERE d.received_at >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY d.received_at DESC;

​

Donation Designations by Fund

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 planning_center.giving_designations des
    ON f.fund_id = des.fund_id
JOIN planning_center.giving_donations d
    ON des.donation_id = d.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 Report

SELECT 
    b.batch_id,
    b.description,
    b.committed_at,
    b.total_cents / 100.0 as batch_total,
    COUNT(d.donation_id) as donation_count,
    SUM(d.amount_cents) / 100.0 as calculated_total
FROM planning_center.giving_batches b
LEFT JOIN planning_center.giving_donations d
    ON b.batch_id = d.batch_id
WHERE b.status = 'committed'
GROUP BY b.batch_id, b.description, b.committed_at, b.total_cents
ORDER BY b.committed_at DESC;

​

Recurring Donation Status

SELECT 
    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
LEFT JOIN planning_center.giving_donations d
    ON rd.recurring_donation_id = d.recurring_donation_id
LEFT JOIN planning_center.giving_people p
    ON d.person_id = p.person_id
WHERE rd.status = 'active'
GROUP BY rd.recurring_donation_id, rd.amount_cents, rd.status, 
         rd.last_donation_received_at, rd.next_occurrence, 
         p.first_name, p.last_name
ORDER BY rd.next_occurrence;

​

Data Integrity Rules

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. Direct ID Columns: The giving_donations table uses direct ID columns for performance optimization

​

Common Mistakes to Avoid

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 Considerations

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
   • Use direct ID columns when available instead of relationship tables

​

Data Types and Conventions

​

Monetary Values

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

​

Dates and Times

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

​

Payment Methods

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

​

Boolean Values

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

​

Next Steps

• 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