​

Planning Center Giving Data Model

​

Overview

​

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