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.

Overview

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

Visual Data Model

The diagram below shows the core entities and their relationships in the Giving module. Use it as a visual reference while exploring the detailed table definitions below.

Core Entity Relationships

Open diagram in new tab →

Key Relationships Explained

Donation Flow:

A DONATION is created with amount and payment details
DESIGNATION(s) split the donation across one or more FUNDs
Donations are grouped into BATCHes for processing
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 Requirements

Schema Prefix

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)

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 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
giving_in_kind_donations - Non-cash (in-kind) donation records

Reference Tables

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

Relationship Tables

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 Definitions

giving_donations

Individual donation transactions from donors.

Column	Type	Description
`id`	UUID	Internal unique identifier
`donation_id`	VARCHAR(64)	Planning Center donation ID
`amount_cents`	INTEGER	Donation amount in cents
`amount_currency`	VARCHAR(10)	Currency code (USD, CAD, etc.)
`completed_at`	TIMESTAMP	When transaction completed
`created_at`	TIMESTAMP	When donation was created
`fee_cents`	INTEGER	Processing fee in cents
`fee_covered`	BOOLEAN	Whether donor covered the fee
`fee_currency`	VARCHAR(10)	Fee currency code
`payment_brand`	VARCHAR(255)	Card brand (Visa, Mastercard, etc.)
`payment_check_dated_at`	DATE	Date on check
`payment_check_number`	INTEGER	Check number
`payment_last4`	VARCHAR(4)	Last 4 digits of card
`payment_method`	VARCHAR(255)	Payment type (cash, check, card, ach)
`payment_method_sub`	VARCHAR(255)	Payment subtype details
`payment_status`	VARCHAR(255)	Transaction status
`received_at`	TIMESTAMP	When donation was received
`refundable`	BOOLEAN	Whether donation can be refunded
`refunded`	BOOLEAN	Whether donation has been refunded
`updated_at`	TIMESTAMP	Last update time
`memo`	TEXT	Donation memo/note
`payment_channel`	VARCHAR(50)	Channel used for payment
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status: ‘active’, ‘transferring’, ‘stale’
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

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_people

Donor profiles and information.

Column	Type	Description
`id`	UUID	Internal unique identifier
`person_id`	VARCHAR(64)	Planning Center person ID
`donor_number`	INTEGER	Unique donor identifier
`first_name`	VARCHAR(255)	Donor’s first name
`last_name`	VARCHAR(255)	Donor’s last name
`permissions`	VARCHAR(255)	Giving permissions
`first_donated_at`	TIMESTAMP	Date of first donation
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_funds

Fund definitions for designated giving.

Column	Type	Description
`id`	UUID	Internal unique identifier
`fund_id`	VARCHAR(64)	Planning Center fund ID
`color`	VARCHAR(64)	Display color for fund
`color_identifier`	INTEGER	Numeric color identifier
`created_at`	TIMESTAMP	When fund was created
`is_default`	BOOLEAN	Whether this is the default/general fund
`deletable`	BOOLEAN	Whether fund can be deleted
`description`	TEXT	Fund description
`ledger_code`	VARCHAR(64)	Accounting ledger code
`name`	VARCHAR(255)	Fund name
`slug`	TEXT	URL-friendly identifier for the fund
`updated_at`	TIMESTAMP	Last update time
`visibility`	VARCHAR(64)	Who can see this fund (everywhere, admin_only, etc.)
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_designations

How donations are allocated to specific funds.

Column	Type	Description
`id`	UUID	Internal unique identifier
`designation_id`	VARCHAR(64)	Planning Center designation ID
`amount_cents`	INTEGER	Amount designated in cents
`amount_currency`	VARCHAR(10)	Currency code
`fee_cents`	INTEGER	Processing fee in cents
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_batches

Groups of donations processed together.

Column	Type	Description
`id`	UUID	Internal unique identifier
`batch_id`	VARCHAR(64)	Planning Center batch ID
`committed_at`	TIMESTAMP	When batch was committed
`created_at`	TIMESTAMP	When batch was created
`description`	TEXT	Batch description
`donations_count`	INTEGER	Number of donations in this batch
`status`	VARCHAR(50)	Batch status (in_progress, committed)
`total_cents`	INTEGER	Total amount in cents
`total_currency`	VARCHAR(50)	Currency code
`updated_at`	TIMESTAMP	Last update time
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_pledges

Pledge commitments for campaigns.

Column	Type	Description
`id`	UUID	Internal unique identifier
`pledge_id`	VARCHAR(64)	Planning Center pledge ID
`amount_cents`	INTEGER	Pledged amount in cents
`amount_currency`	VARCHAR(10)	Currency code
`created_at`	TIMESTAMP	When pledge was made
`donated_total_cents`	INTEGER	Total amount donated toward this pledge
`joint_giver_amount_cents`	INTEGER	Amount pledged by joint giver
`joint_giver_donated_total_cents`	INTEGER	Total amount donated by joint giver
`updated_at`	TIMESTAMP	Last update time
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

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_campaigns

Campaign definitions for pledge drives.

Column	Type	Description
`id`	UUID	Internal unique identifier
`pledge_campaign_id`	VARCHAR(64)	Planning Center campaign ID
`created_at`	TIMESTAMP	When campaign was created
`description`	TEXT	Campaign description
`ends_at`	TIMESTAMP	Campaign end date
`goal_cents`	INTEGER	Campaign goal in cents
`goal_currency`	VARCHAR(255)	Goal currency
`name`	TEXT	Campaign name
`received_total_from_pledges_cents`	INTEGER	Total received from pledged donations (use this, not `received_total_cents`)
`received_total_outside_of_pledges_cents`	INTEGER	Total received from non-pledged donations
`show_goal_in_church_center`	BOOLEAN	Public visibility of goal
`starts_at`	TIMESTAMP	Campaign start date
`updated_at`	TIMESTAMP	Last update
`fund_id`	VARCHAR(64)	Associated fund
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_recurring_donations

Automated recurring giving setups.

Column	Type	Description
`id`	UUID	Internal unique identifier
`recurring_donation_id`	VARCHAR(64)	Planning Center recurring ID
`created_at`	TIMESTAMP	When setup was created
`updated_at`	TIMESTAMP	Last update
`release_hold_at`	TIMESTAMP	When hold releases
`amount_cents`	INTEGER	Recurring amount in cents
`amount_currency`	VARCHAR(50)	Currency code
`status`	VARCHAR(50)	Status (active, indefinite_hold, temporary_hold)
`last_donation_received_at`	TIMESTAMP	Most recent successful donation
`next_occurrence`	TIMESTAMP	Next scheduled donation
`schedule`	JSONB	Schedule configuration
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_batch_groups

Groups of donation batches for organizational purposes.

Column	Type	Description
`id`	UUID	Internal unique identifier
`batch_group_id`	VARCHAR(64)	Planning Center batch group ID
`committed`	BOOLEAN	Whether batch group is committed
`created_at`	TIMESTAMP	When created
`description`	TEXT	Human-readable description
`status`	VARCHAR(50)	Batch group status
`total_cents`	INTEGER	Total amount in cents
`total_currency`	VARCHAR(50)	Currency code
`updated_at`	TIMESTAMP	Last update
`owner_id`	VARCHAR(64)	Person who owns this batch group
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_payment_methods

Payment method details for recurring donations.

Column	Type	Description
`id`	UUID	Internal unique identifier
`payment_method_id`	VARCHAR(64)	Planning Center payment method ID
`recurring_donation_id`	VARCHAR(64)	Associated recurring donation ID
`brand`	VARCHAR(255)	Card brand
`created_at`	TIMESTAMP	When created
`expiration`	VARCHAR(10)	Expiration date
`last4`	VARCHAR(4)	Last 4 digits
`method_subtype`	VARCHAR(255)	Payment method subtype
`method_type`	VARCHAR(255)	Payment method type
`updated_at`	TIMESTAMP	Last update
`verified`	BOOLEAN	Whether verified
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_payment_sources

Sources of payments (broader category than payment methods).

Column	Type	Description
`id`	UUID	Internal unique identifier
`payment_source_id`	VARCHAR(64)	Planning Center payment source ID
`created_at`	TIMESTAMP	When created
`name`	VARCHAR(255)	Display name
`payment_source_type`	VARCHAR(255)	Type of payment source
`status`	VARCHAR(50)	Payment source status
`updated_at`	TIMESTAMP	Last update
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_refunds

Refund transactions for donations.

Column	Type	Description
`id`	UUID	Internal unique identifier
`refund_id`	VARCHAR(64)	Planning Center refund ID
`amount_cents`	INTEGER	Refund amount in cents
`amount_currency`	VARCHAR(10)	Currency code
`created_at`	TIMESTAMP	When created
`fee_cents`	INTEGER	Refund processing fee in cents
`fee_currency`	VARCHAR(10)	Fee currency code
`refunded_at`	TIMESTAMP	When refund was processed
`updated_at`	TIMESTAMP	Last update
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_campuses

Physical locations/campuses for the organization.

Column	Type	Description
`id`	UUID	Internal unique identifier
`campus_id`	VARCHAR(64)	Planning Center campus ID
`name`	TEXT	Campus name
`street_line_1`	VARCHAR(255)	Street address line 1
`street_line_2`	VARCHAR(255)	Street address line 2
`city`	VARCHAR(255)	City
`state`	VARCHAR(255)	State
`zip`	VARCHAR(255)	ZIP code
`location`	VARCHAR(255)	Location description
`street`	VARCHAR(255)	Street name
`line_1`	VARCHAR(255)	Address line 1
`line_2`	VARCHAR(255)	Address line 2
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_organizations

Organization-level settings and information for giving.

Column	Type	Description
`id`	UUID	Internal unique identifier
`organization_id`	VARCHAR(64)	Planning Center organization ID
`name`	VARCHAR(255)	Organization name
`time_zone`	VARCHAR(50)	Organization time zone
`text2give_enabled`	BOOLEAN	Whether Text-to-Give is enabled for the organization
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_labels

Categorization tags for giving-related entities.

Column	Type	Description
`id`	UUID	Internal unique identifier
`label_id`	VARCHAR(64)	Planning Center label ID
`slug`	TEXT	URL-friendly identifier
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_notes

Text notes attached to giving-related entities.

Column	Type	Description
`id`	UUID	Internal unique identifier
`note_id`	VARCHAR(64)	Planning Center note ID
`body`	TEXT	Note content
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_recurring_donation_designations

Fund allocations for recurring donations.

Column	Type	Description
`id`	UUID	Internal unique identifier
`recurring_donation_designation_id`	VARCHAR(64)	Planning Center recurring donation designation ID
`amount_cents`	INTEGER	Amount designated in cents
`amount_currency`	VARCHAR(10)	Currency code
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_designation_refunds

Refunds for specific designations within donations.

Column	Type	Description
`id`	UUID	Internal unique identifier
`designation_refund_id`	VARCHAR(64)	Planning Center designation refund ID
`donation_id`	VARCHAR(64)	Associated donation ID
`amount_cents`	INTEGER	Refund amount in cents
`amount_currency`	VARCHAR(10)	Currency code
`designation_id`	VARCHAR(64)	Associated designation ID
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_in_kind_donations

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

Column	Type	Description
`id`	UUID	Internal unique identifier
`in_kind_donation_id`	VARCHAR(64)	Planning Center in-kind donation ID
`acknowledgment_last_sent_at`	TIMESTAMP	When the last acknowledgment was sent to the donor
`created_at`	TIMESTAMP	When the in-kind donation was created
`description`	TEXT	Description of the donated item or service
`exchange_details`	TEXT	Details about any exchange arrangement
`fair_market_value_cents`	INTEGER	Fair market value of the donation in cents
`fair_market_value_currency`	VARCHAR(10)	Currency code for fair market value
`internal_notes`	TEXT	Internal staff notes about the donation
`received_on`	DATE	Date the in-kind donation was received
`updated_at`	TIMESTAMP	Last update time
`valuation_details`	TEXT	Details about how the value was determined
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

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

Relationship Tables

giving_donations_relationships

Links donations to related entities beyond the direct ID columns.

Column	Type	Description
`id`	UUID	Internal unique identifier
`donation_id`	VARCHAR(64)	Parent donation ID
`relationship_type`	VARCHAR(50)	Type of relationship
`relationship_id`	VARCHAR(64)	ID of related entity
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_designations_relationships

Links designations to donations and other entities.

Column	Type	Description
`id`	UUID	Internal unique identifier
`designation_id`	VARCHAR(64)	Parent designation ID
`relationship_type`	VARCHAR(50)	Type of relationship
`relationship_id`	VARCHAR(64)	ID of related entity
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_pledges_relationships

Links pledges to people and campaigns.

Column	Type	Description
`id`	UUID	Internal unique identifier
`pledge_id`	VARCHAR(64)	Parent pledge ID
`relationship_type`	VARCHAR(50)	Type of relationship
`relationship_id`	VARCHAR(64)	ID of related entity
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_people_relationships

Links people to other entities beyond the direct ID columns.

Column	Type	Description
`id`	UUID	Internal unique identifier
`person_id`	VARCHAR(64)	Parent person ID
`relationship_type`	VARCHAR(50)	Type of relationship
`relationship_id`	VARCHAR(64)	ID of related entity
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_batches_relationships

Links batches to related entities.

Column	Type	Description
`id`	UUID	Internal unique identifier
`batch_id`	VARCHAR(64)	Parent batch ID
`relationship_type`	VARCHAR(50)	Type of relationship
`relationship_id`	VARCHAR(64)	ID of related entity
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_refunds_relationships

Links refunds to related entities.

Column	Type	Description
`id`	UUID	Internal unique identifier
`refund_id`	VARCHAR(64)	Parent refund ID
`relationship_type`	VARCHAR(50)	Type of relationship
`relationship_id`	VARCHAR(64)	ID of related entity
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

giving_in_kind_donations_relationships

Links in-kind donations to related entities.

Column	Type	Description
`id`	UUID	Internal unique identifier
`in_kind_donation_id`	VARCHAR(64)	Parent in-kind donation ID
`relationship_type`	VARCHAR(50)	Type of relationship
`relationship_id`	VARCHAR(64)	ID of related entity
`tenant_organization_id`	INTEGER	Organization identifier
`system_status`	VARCHAR(50)	Data status
`system_created_at`	TIMESTAMP	When record was created in Parable
`system_updated_at`	TIMESTAMP	When record was last updated in Parable

System Fields

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 Patterns

Getting Donations with Donor Information

-- 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 Fund

-- 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 Report

-- 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 Status

-- 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 Rules

Schema Qualification: Always use planning_center. prefix for all table references
Row Level Security: RLS automatically handles multi-tenancy and status filtering - do not add manual filters
Monetary Values: All amounts are stored in cents - divide by 100.0 for display
Refunded Donations: Filter refunded = true when needed for financial reports
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 Avoid

Missing Schema Prefix
- ❌ FROM giving_donations
- ✅ FROM planning_center.giving_donations
Adding Redundant RLS Filters
- ❌ WHERE tenant_organization_id = 1 AND system_status = 'active'
- ✅ Trust RLS to handle this automatically
Joining Without Schema
- ❌ JOIN giving_people p ON ...
- ✅ JOIN planning_center.giving_people p ON ...
Forgetting Currency Conversion
- ❌ SELECT amount_cents as amount (displays cents)
- ✅ SELECT amount_cents / 100.0 as amount (displays dollars)

Performance Considerations

Indexes: All tables have optimized indexes on:
- Primary keys and entity IDs
- Join columns and foreign keys
- Date columns for time-based queries
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 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

​Overview

​Visual Data Model

​Core Entity Relationships

​Key Relationships Explained

​Query Requirements

​Schema Prefix

​Row Level Security (RLS)

​Core Tables Overview

​Primary Entity Tables

​Financial Processing Tables

​Reference Tables

​Relationship Tables

​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

​giving_in_kind_donations

​Relationship Tables

​giving_donations_relationships

​giving_designations_relationships

​giving_pledges_relationships

​giving_people_relationships

​giving_batches_relationships

​giving_refunds_relationships

​giving_in_kind_donations_relationships

​System Fields

​Common Query Patterns

​Getting Donations with Donor Information

​Donation Designations by Fund

​Batch Summary Report

​Recurring Donation Status

​Data Integrity Rules

​Common Mistakes to Avoid

​Performance Considerations

​Data Types and Conventions

​Monetary Values

​Dates and Times

​Payment Methods

​Boolean Values

​Next Steps

Overview

Visual Data Model

Core Entity Relationships

Key Relationships Explained

Query Requirements

Schema Prefix

Row Level Security (RLS)

Core Tables Overview

Primary Entity Tables

Financial Processing Tables

Reference Tables

Relationship Tables

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

giving_in_kind_donations

Relationship Tables

giving_donations_relationships

giving_designations_relationships

giving_pledges_relationships

giving_people_relationships

giving_batches_relationships

giving_refunds_relationships

giving_in_kind_donations_relationships

System Fields

Common Query Patterns

Getting Donations with Donor Information

Donation Designations by Fund

Batch Summary Report

Recurring Donation Status

Data Integrity Rules

Common Mistakes to Avoid

Performance Considerations

Data Types and Conventions

Monetary Values

Dates and Times

Payment Methods

Boolean Values

Next Steps