> ## Documentation Index
> Fetch the complete documentation index at: https://docs.getparable.io/llms.txt
> Use this file to discover all available pages before exploring further.
# Data model
# 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 **18 entity tables** and **6 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 →](/diagrams/planning-center/giving-data-model-01.svg)
### Key Relationships Explained
**Donation Flow:**
1. A `DONATION` is created with amount and payment details
2. `DESIGNATION`(s) split the donation across one or more `FUND`s
3. Donations are grouped into `BATCH`es for processing
4. Batches can be organized into `BATCH_GROUP`s
**Generic Relationship Pattern:**
* 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 System:**
* `PLEDGE_CAMPAIGN` defines fundraising campaigns
* `PLEDGE`s are commitments made during campaigns
* Person relationship tracked via `giving_pledge_relationships`
**Recurring Donations:**
* `RECURRING_DONATION` defines the schedule and total amount
* `RECURRING_DONATION_DESIGNATION`s split recurring amounts across funds
* Actual donations created by Planning Center on schedule
**Refund Handling:**
* `REFUND` represents the refund transaction
* `DESIGNATION_REFUND`s 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
### 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
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 |
| `batch_id` | VARCHAR(64) | Associated batch (direct reference) |
| `campus_id` | VARCHAR(64) | Associated campus (direct reference) |
| `person_id` | VARCHAR(64) | Donor person ID (direct reference) |
| `payment_source_id` | VARCHAR(64) | Payment source (direct reference) |
| `note_id` | VARCHAR(64) | Associated note (direct reference) |
| `recurring_donation_id` | VARCHAR(64) | Recurring donation (direct reference) |
| `refund_id` | VARCHAR(64) | Associated refund (direct reference) |
| `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**: This table uses direct ID columns for performance optimization rather than relationship tables.
### 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 |
| `primary_campus_id` | VARCHAR(64) | Primary campus (direct reference) |
| `joint_giving_unit_id` | VARCHAR(64) | Joint giving unit ID (direct reference) |
| `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 |
| `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 |
| `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 |
| `fund_id` | VARCHAR(64) | Associated fund 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\_batches
Groups of donations processed together.
| Column | Type | Description |
| ------------------------ | ----------- | --------------------------------------- |
| `id` | UUID | Internal unique identifier |
| `batch_id` | VARCHAR(64) | Planning Center batch ID |
| `batch_group_id` | VARCHAR(64) | Parent batch group |
| `committed_at` | TIMESTAMP | When batch was committed |
| `created_at` | TIMESTAMP | When batch was created |
| `description` | TEXT | Batch description |
| `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 |
### 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 |
| `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, paused, inactive) |
| `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 |
| `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 |
| `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 |
## Relationship Tables
### giving\_donation\_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\_designation\_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\_pledge\_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\_person\_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\_batch\_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\_refund\_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 |
## 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
```sql theme={null}
-- 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
```sql theme={null}
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
```sql theme={null}
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
```sql theme={null}
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](/planning-center/giving/basic-queries) for simple examples
* Progress to [Advanced Queries](/planning-center/giving/advanced-queries) for complex analysis
* Use [Reporting Examples](/planning-center/giving/reporting-examples) for production reports
* Return to [Overview](/planning-center/giving/overview) for overview