> ## 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 Publishing Data Model - Complete Documentation
This document provides **complete** documentation of ALL tables in the Planning Center Publishing data model in Parable, including all entity tables and relationship tables with full field definitions.
## Overview
The Publishing module manages your church's media content distribution, containing:
* **14 entity tables** - Episodes, series, channels, speakers, and supporting data
* **1 relationship table** - Speakerships linking episodes to speakers
* **Comprehensive metrics** - View counts, downloads, and engagement tracking
## Visual Data Model
The diagram below shows the core entities and their relationships in the Publishing 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/publishing-data-model-01.svg)
### Key Relationships Explained
**Content Hierarchy:**
1. `CHANNEL` is the top-level podcast/media feed
2. `SERIES` organizes episodes by topic or theme
3. `EPISODE` is the individual sermon, message, or content piece
4. Hierarchy: Channel → Series → Episode
**Publishing Schedule:**
* `CHANNEL_DEFAULT_TIME` defines recurring publish schedule
* `CHANNEL_NEXT_TIME` overrides next publish date/time
* `EPISODE_TIME` tracks when specific episodes were/will be published
* Supports both recurring schedules and one-off publications
**Media Resources:**
* `EPISODE_RESOURCE` stores multiple formats per episode
* Kinds include: audio, video, PDF notes, etc.
* Each resource has URL and file size tracking
* Single episode can have audio, video, and supplementary files
**Speaker Management:**
* `SPEAKER` defines individuals who present content
* `SPEAKERSHIP` links episodes to speakers (many-to-many)
* Single episode can have multiple speakers
* Speakers can appear in multiple episodes/series
**Analytics:**
* `EPISODE_STATISTIC` tracks engagement metrics
* Metrics: total plays, total downloads, time periods
* Historical tracking with date ranges
* Enables trend analysis and content performance review
**Templates & Onboarding:**
* `NOTE_TEMPLATE` provides reusable sermon note formats
* `ONBOARDING` tracks publishing setup completion
* `ORGANIZATION` links to Planning Center organization settings
**Parent-Child Relationships:**
* Times and resources use `parent_id` pattern
* Episode times belong to episodes
* Episode resources belong to episodes
* Channel times belong to channels
## Query Requirements
### Schema Prefix
**IMPORTANT:** All tables in the Planning Center Publishing module are in the `planning_center` schema. You MUST prefix all table names with `planning_center.` in your queries.
✅ CORRECT: `SELECT * FROM planning_center.publishing_episodes`
❌ INCORRECT: `SELECT * FROM publishing_episodes`
### 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.
## Complete Table Inventory
### Core Content Tables
#### 1. `publishing_episodes`
Individual sermons, messages, and content pieces.
| Column | Type | Description |
| ----------------------------------------- | ------------ | -------------------------- |
| `id` | UUID | Unique internal identifier |
| `episode_id` | VARCHAR(64) | Planning Center episode ID |
| `title` | TEXT | Episode title |
| `description` | TEXT | Episode description |
| `series_id` | VARCHAR(64) | Associated series ID |
| `channel_id` | VARCHAR(64) | Primary channel ID |
| `published_live_at` | TIMESTAMP | When episode went live |
| `published_to_library_at` | TIMESTAMP | When added to library |
| `video_url` | TEXT | Primary video URL |
| `video_thumbnail_url` | TEXT | Video thumbnail image |
| `library_video_url` | TEXT | Library video URL |
| `library_video_thumbnail_url` | TEXT | Library thumbnail |
| `library_audio_url` | TEXT | Library audio URL |
| `library_streaming_service` | VARCHAR(255) | Streaming provider |
| `sermon_audio` | JSONB | Audio file metadata |
| `art` | JSONB | Artwork/images metadata |
| `church_center_url` | TEXT | Church Center app URL |
| `stream_type` | VARCHAR(255) | Live/recorded/hybrid |
| `streaming_service` | VARCHAR(255) | Streaming platform |
| `page_actions` | JSONB | Available page actions |
| `needs_library_audio_or_video_url` | BOOLEAN | Missing media flag |
| `needs_video_url` | BOOLEAN | Missing video flag |
| `services_plan_remote_identifier` | VARCHAR(64) | Services app plan ID |
| `services_service_type_remote_identifier` | VARCHAR(64) | Service type ID |
| `created_at` | TIMESTAMP | Creation timestamp |
| `updated_at` | TIMESTAMP | Last update timestamp |
| `tenant_organization_id` | INTEGER | Organization identifier |
| `system_status` | VARCHAR(50) | Data lifecycle status |
| `system_created_at` | TIMESTAMP | System creation time |
| `system_updated_at` | TIMESTAMP | System update time |
#### 2. `publishing_series`
Sermon series and content collections.
| Column | Type | Description |
| ------------------------ | ------------ | -------------------------- |
| `id` | UUID | Unique internal identifier |
| `series_id` | VARCHAR(64) | Planning Center series ID |
| `title` | VARCHAR(255) | Series title |
| `description` | TEXT | Series description |
| `channel_id` | VARCHAR(64) | Primary channel ID |
| `art` | JSONB | Series artwork metadata |
| `church_center_url` | VARCHAR(255) | Church Center URL |
| `started_at` | TIMESTAMP | Series start date |
| `ended_at` | TIMESTAMP | Series end date |
| `episodes_count` | INTEGER | Number of episodes |
| `published` | BOOLEAN | Publication status |
| `created_at` | TIMESTAMP | Creation timestamp |
| `updated_at` | TIMESTAMP | Last update timestamp |
| `tenant_organization_id` | INTEGER | Organization identifier |
| `system_status` | VARCHAR(50) | Data lifecycle status |
| `system_created_at` | TIMESTAMP | System creation time |
| `system_updated_at` | TIMESTAMP | System update time |
#### 3. `publishing_channels`
Distribution channels and platforms.
| Column | Type | Description |
| ------------------------ | ------------ | -------------------------------- |
| `id` | UUID | Unique internal identifier |
| `channel_id` | VARCHAR(64) | Planning Center channel ID |
| `name` | VARCHAR(255) | Channel name |
| `kind` | VARCHAR(100) | Channel type (podcast/video/etc) |
| `description` | TEXT | Channel description |
| `art` | JSONB | Channel artwork metadata |
| `streaming_service` | VARCHAR(255) | Streaming platform |
| `livestream_url` | TEXT | Live streaming URL |
| `embed_code` | TEXT | Embed HTML code |
| `default_publish_time` | TIME | Default publish time |
| `time_zone` | VARCHAR(100) | Channel timezone |
| `church_center_enabled` | BOOLEAN | Church Center visibility |
| `created_at` | TIMESTAMP | Creation timestamp |
| `updated_at` | TIMESTAMP | Last update timestamp |
| `tenant_organization_id` | INTEGER | Organization identifier |
| `system_status` | VARCHAR(50) | Data lifecycle status |
| `system_created_at` | TIMESTAMP | System creation time |
| `system_updated_at` | TIMESTAMP | System update time |
#### 4. `publishing_speakers`
Speaker profiles and information.
| Column | Type | Description |
| ------------------------ | ------------ | -------------------------- |
| `id` | UUID | Unique internal identifier |
| `speaker_id` | VARCHAR(64) | Planning Center speaker ID |
| `name` | VARCHAR(255) | Speaker full name |
| `description` | TEXT | Speaker bio/description |
| `photo_url` | TEXT | Speaker photo URL |
| `person_id` | VARCHAR(64) | Link to People app |
| `created_at` | TIMESTAMP | Creation timestamp |
| `updated_at` | TIMESTAMP | Last update timestamp |
| `tenant_organization_id` | INTEGER | Organization identifier |
| `system_status` | VARCHAR(50) | Data lifecycle status |
| `system_created_at` | TIMESTAMP | System creation time |
| `system_updated_at` | TIMESTAMP | System update time |
### Relationship Tables
#### 5. `publishing_speakerships`
Links episodes to speakers (junction table).
| Column | Type | Description |
| ------------------------ | ----------- | ------------------------------ |
| `id` | UUID | Unique internal identifier |
| `speakership_id` | VARCHAR(64) | Planning Center speakership ID |
| `episode_id` | VARCHAR(64) | Associated episode ID |
| `speaker_id` | VARCHAR(64) | Associated speaker ID |
| `tenant_organization_id` | INTEGER | Organization identifier |
| `system_status` | VARCHAR(50) | Data lifecycle status |
| `system_created_at` | TIMESTAMP | System creation time |
| `system_updated_at` | TIMESTAMP | System update time |
### Resource and Media Tables
#### 6. `publishing_episode_resources`
Files and resources attached to episodes.
| Column | Type | Description |
| ------------------------ | ------------ | -------------------------------- |
| `id` | UUID | Unique internal identifier |
| `episode_resource_id` | VARCHAR(64) | Planning Center resource ID |
| `episode_id` | VARCHAR(64) | Associated episode ID |
| `name` | VARCHAR(255) | Resource name |
| `kind` | VARCHAR(100) | Resource type (notes/slides/etc) |
| `url` | TEXT | Resource download URL |
| `file_size` | BIGINT | File size in bytes |
| `content_type` | VARCHAR(255) | MIME type |
| `created_at` | TIMESTAMP | Creation timestamp |
| `updated_at` | TIMESTAMP | Last update timestamp |
| `tenant_organization_id` | INTEGER | Organization identifier |
| `system_status` | VARCHAR(50) | Data lifecycle status |
| `system_created_at` | TIMESTAMP | System creation time |
| `system_updated_at` | TIMESTAMP | System update time |
#### 7. `publishing_note_templates`
Templates for sermon notes and outlines.
| Column | Type | Description |
| ------------------------ | ------------ | --------------------------- |
| `id` | UUID | Unique internal identifier |
| `note_template_id` | VARCHAR(64) | Planning Center template ID |
| `episode_id` | VARCHAR(64) | Associated episode ID |
| `name` | VARCHAR(255) | Template name |
| `content` | TEXT | Template content/markdown |
| `kind` | VARCHAR(100) | Template type |
| `created_at` | TIMESTAMP | Creation timestamp |
| `updated_at` | TIMESTAMP | Last update timestamp |
| `tenant_organization_id` | INTEGER | Organization identifier |
| `system_status` | VARCHAR(50) | Data lifecycle status |
| `system_created_at` | TIMESTAMP | System creation time |
| `system_updated_at` | TIMESTAMP | System update time |
### Scheduling Tables
#### 8. `publishing_episode_times`
Publishing schedule for episodes on different channels.
| Column | Type | Description |
| ------------------------ | ----------- | -------------------------- |
| `id` | UUID | Unique internal identifier |
| `episode_time_id` | VARCHAR(64) | Planning Center time ID |
| `episode_id` | VARCHAR(64) | Associated episode ID |
| `channel_id` | VARCHAR(64) | Target channel ID |
| `published_at` | TIMESTAMP | Scheduled publish time |
| `status` | VARCHAR(50) | Publishing status |
| `created_at` | TIMESTAMP | Creation timestamp |
| `updated_at` | TIMESTAMP | Last update timestamp |
| `tenant_organization_id` | INTEGER | Organization identifier |
| `system_status` | VARCHAR(50) | Data lifecycle status |
| `system_created_at` | TIMESTAMP | System creation time |
| `system_updated_at` | TIMESTAMP | System update time |
#### 9. `publishing_channel_default_times`
Default publishing schedules per channel.
| Column | Type | Description |
| ------------------------- | ----------- | ------------------------------- |
| `id` | UUID | Unique internal identifier |
| `channel_default_time_id` | VARCHAR(64) | Planning Center default time ID |
| `channel_id` | VARCHAR(64) | Associated channel ID |
| `day_of_week` | INTEGER | Day (0=Sunday, 6=Saturday) |
| `time_of_day` | TIME | Default publish time |
| `enabled` | BOOLEAN | Schedule active flag |
| `created_at` | TIMESTAMP | Creation timestamp |
| `updated_at` | TIMESTAMP | Last update timestamp |
| `tenant_organization_id` | INTEGER | Organization identifier |
| `system_status` | VARCHAR(50) | Data lifecycle status |
| `system_created_at` | TIMESTAMP | System creation time |
| `system_updated_at` | TIMESTAMP | System update time |
#### 10. `publishing_channel_next_times`
Upcoming scheduled publishes per channel.
| Column | Type | Description |
| ------------------------ | ----------- | ---------------------------- |
| `id` | UUID | Unique internal identifier |
| `channel_next_time_id` | VARCHAR(64) | Planning Center next time ID |
| `channel_id` | VARCHAR(64) | Associated channel ID |
| `next_publish_at` | TIMESTAMP | Next scheduled publish |
| `created_at` | TIMESTAMP | Creation timestamp |
| `updated_at` | TIMESTAMP | Last update timestamp |
| `tenant_organization_id` | INTEGER | Organization identifier |
| `system_status` | VARCHAR(50) | Data lifecycle status |
| `system_created_at` | TIMESTAMP | System creation time |
| `system_updated_at` | TIMESTAMP | System update time |
### Analytics Tables
#### 11. `publishing_episode_statistics`
View counts, downloads, and engagement metrics.
| Column | Type | Description |
| ------------------------ | ----------- | ---------------------------- |
| `id` | UUID | Unique internal identifier |
| `episode_statistic_id` | VARCHAR(64) | Planning Center statistic ID |
| `episode_id` | VARCHAR(64) | Associated episode ID |
| `channel_id` | VARCHAR(64) | Associated channel ID |
| `view_count` | INTEGER | Total view count |
| `download_count` | INTEGER | Total download count |
| `unique_viewers` | INTEGER | Unique viewer count |
| `average_watch_time` | INTEGER | Average watch time (seconds) |
| `total_watch_time` | BIGINT | Total watch time (seconds) |
| `created_at` | TIMESTAMP | Statistic timestamp |
| `updated_at` | TIMESTAMP | Last update timestamp |
| `tenant_organization_id` | INTEGER | Organization identifier |
| `system_status` | VARCHAR(50) | Data lifecycle status |
| `system_created_at` | TIMESTAMP | System creation time |
| `system_updated_at` | TIMESTAMP | System update time |
#### 12. `publishing_episode_statistic_times`
Statistics tracked over time periods.
| Column | Type | Description |
| --------------------------- | ----------- | ---------------------------- |
| `id` | UUID | Unique internal identifier |
| `episode_statistic_time_id` | VARCHAR(64) | Planning Center stat time ID |
| `episode_statistic_id` | VARCHAR(64) | Parent statistic ID |
| `period_start` | TIMESTAMP | Period start time |
| `period_end` | TIMESTAMP | Period end time |
| `view_count` | INTEGER | Views in period |
| `download_count` | INTEGER | Downloads in period |
| `unique_viewers` | INTEGER | Unique viewers in period |
| `created_at` | TIMESTAMP | Creation timestamp |
| `updated_at` | TIMESTAMP | Last update timestamp |
| `tenant_organization_id` | INTEGER | Organization identifier |
| `system_status` | VARCHAR(50) | Data lifecycle status |
| `system_created_at` | TIMESTAMP | System creation time |
| `system_updated_at` | TIMESTAMP | System update time |
### Configuration Tables
#### 13. `publishing_organizations`
Organization-level publishing settings.
| Column | Type | Description |
| ------------------------ | ------------ | -------------------------- |
| `id` | UUID | Unique internal identifier |
| `organization_id` | VARCHAR(64) | Planning Center org ID |
| `name` | VARCHAR(255) | Organization name |
| `settings` | JSONB | Publishing settings |
| `default_channel_id` | VARCHAR(64) | Default channel |
| `created_at` | TIMESTAMP | Creation timestamp |
| `updated_at` | TIMESTAMP | Last update timestamp |
| `tenant_organization_id` | INTEGER | Organization identifier |
| `system_status` | VARCHAR(50) | Data lifecycle status |
| `system_created_at` | TIMESTAMP | System creation time |
| `system_updated_at` | TIMESTAMP | System update time |
#### 14. `publishing_channel_channel_default_episode_resources`
Default resources automatically attached to episodes per channel.
| Column | Type | Description |
| ------------------------------------- | ------------ | ----------------------------------- |
| `id` | UUID | Unique internal identifier |
| `channel_default_episode_resource_id` | VARCHAR(64) | Planning Center default resource ID |
| `channel_id` | VARCHAR(64) | Associated channel ID |
| `name` | VARCHAR(255) | Resource name |
| `kind` | VARCHAR(100) | Resource type |
| `url` | TEXT | Resource URL |
| `auto_attach` | BOOLEAN | Auto-attach flag |
| `created_at` | TIMESTAMP | Creation timestamp |
| `updated_at` | TIMESTAMP | Last update timestamp |
| `tenant_organization_id` | INTEGER | Organization identifier |
| `system_status` | VARCHAR(50) | Data lifecycle status |
| `system_created_at` | TIMESTAMP | System creation time |
| `system_updated_at` | TIMESTAMP | System update time |
### Administrative Tables
#### 15. `publishing_onboardings`
Tracks onboarding process for new channels.
| Column | Type | Description |
| ------------------------ | ----------- | ----------------------------- |
| `id` | UUID | Unique internal identifier |
| `onboarding_id` | VARCHAR(64) | Planning Center onboarding ID |
| `channel_id` | VARCHAR(64) | Associated channel ID |
| `status` | VARCHAR(50) | Onboarding status |
| `steps_completed` | JSONB | Completed steps tracking |
| `created_at` | TIMESTAMP | Creation timestamp |
| `updated_at` | TIMESTAMP | Last update timestamp |
| `tenant_organization_id` | INTEGER | Organization identifier |
| `system_status` | VARCHAR(50) | Data lifecycle status |
| `system_created_at` | TIMESTAMP | System creation time |
| `system_updated_at` | TIMESTAMP | System update time |
## System Fields
All tables include these system fields for data management:
* `tenant_organization_id` - Multi-tenant organization identifier
* `system_status` - Data lifecycle status (`active`, `transferring`, `stale`)
* `system_created_at` - When the record was created in Parable
* `system_updated_at` - When the record was last updated in Parable
## Row Level Security
All tables implement Row Level Security (RLS) to ensure tenant isolation:
```sql theme={null}
-- Example RLS policy
CREATE POLICY tenant_read_only ON planning_center.publishing_episodes
FOR SELECT
TO PUBLIC
USING (
system_status = 'active'
AND EXISTS (
SELECT 1 FROM public.tenant_dsn_credentials dsn
WHERE dsn.role_name = CURRENT_ROLE
AND dsn.tenant_organization_id = planning_center.publishing_episodes.tenant_organization_id
)
);
```
## 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**: Any monetization or purchase amount columns are stored in cents - divide by 100.0 for display
4. **Media Flags**: Use booleans like `needs_video_url` and `needs_library_audio_or_video_url` to identify missing assets instead of relying on `system_status`
5. **Direct ID Columns**: Tables such as `publishing_episodes`, `publishing_series`, and `publishing_speakerships` expose direct IDs for performance-sensitive joins
## Common Mistakes to Avoid
1. **Missing Schema Prefix**
* ❌ `FROM publishing_episodes`
* ✅ `FROM planning_center.publishing_episodes`
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 publishing_series s ON ...`
* ✅ `JOIN planning_center.publishing_series s ON ...`
4. **Skipping Currency Conversion**
* ❌ `SELECT purchase_price_cents as purchase_price`
* ✅ `SELECT purchase_price_cents / 100.0 as purchase_price`
## 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 by publication state or media availability when relevant
* Consider CTEs for aggregating view/download metrics
* Use direct ID columns when available instead of relationship tables
## Data Synchronization
Publishing data is synchronized through Temporal workflows:
1. **Master Workflow** - Orchestrates all child workflows
2. **Independent Entities** - Channels, Series, Speakers, Organizations
3. **Dependent Entities** - Episodes (depends on channels)
4. **Related Data** - Resources, times, statistics (depends on episodes)
5. **Junction Tables** - Speakerships linking episodes to speakers
## Usage Tips
1. **Trust RLS for active data** - Skip manual `system_status` or tenant filters
2. **Join through IDs** - Use `episode_id`, `series_id`, `speaker_id`
3. **Consider NULL values** - Many fields are optional
4. **Use JSONB operators** for nested data in `art`, `settings` fields
5. **Aggregate statistics** over time periods for trends
## Common Join Patterns
### Episodes with Series and Speakers
```sql theme={null}
SELECT
e.title as episode_title,
s.title as series_title,
sp.name as speaker_name
FROM planning_center.publishing_episodes e
LEFT JOIN planning_center.publishing_series s
ON e.series_id = s.series_id
LEFT JOIN planning_center.publishing_speakerships ship
ON e.episode_id = ship.episode_id
LEFT JOIN planning_center.publishing_speakers sp
ON ship.speaker_id = sp.speaker_id;
```
### Episodes with Statistics and Resources
```sql theme={null}
SELECT
e.title,
est.view_count,
est.download_count,
er.name as resource_name,
er.url as resource_url
FROM planning_center.publishing_episodes e
LEFT JOIN planning_center.publishing_episode_statistics est
ON e.episode_id = est.episode_id
LEFT JOIN planning_center.publishing_episode_resources er
ON e.episode_id = er.episode_id;
```
## Data Quality Notes
* Episode `published_live_at` indicates published content
* Series may have NULL `ended_at` for ongoing series
* Statistics are point-in-time snapshots, use latest for current counts
* Speakerships table enables many-to-many episode-speaker relationships
* Channel IDs in episodes indicate primary publishing channel
***
*This data model enables comprehensive media analytics and content management for your church's publishing ministry.*