Planning Center Publishing Data Model

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:

13 entity tables - Episodes, series, channels, speakers, and supporting data
4 relationship tables - Linking episodes, channels, series, and speakerships to related entities
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 →

Key Relationships Explained

Content Hierarchy:

CHANNEL is the top-level podcast/media feed
SERIES organizes episodes by topic or theme
EPISODE is the individual sermon, message, or content piece
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: library watch count, live watch count, time periods
Historical tracking with date ranges
Enables trend analysis and content performance review

Templates & Organization:

NOTE_TEMPLATE provides reusable sermon note formats
ORGANIZATION links to Planning Center organization settings

Generic Relationship Pattern:

Inter-entity links are stored in *_relationships tables
Each relationship table holds a parent entity ID, relationship_type, and relationship_id
Episode times, episode resources, and channel times belong to their parent via relationship tables

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
`published_live_at`	TIMESTAMP	When episode went live
`published_to_library_at`	TIMESTAMP	When added to library
`video_url`	TEXT	Primary video URL
`video_embed_code`	TEXT	Primary video embed code
`video_thumbnail_url`	TEXT	Video thumbnail image
`library_video_url`	TEXT	Library video URL
`library_video_embed_code`	TEXT	Library video embed code
`library_video_thumbnail_url`	TEXT	Library thumbnail
`library_audio_url`	TEXT	Library audio URL
`library_streaming_service`	VARCHAR(255)	Streaming provider for library
`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_notes_template`	BOOLEAN	Missing notes template 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
`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`	TEXT	Channel name
`description`	TEXT	Channel description
`art`	JSONB	Channel artwork metadata
`podcast_art`	JSONB	Podcast artwork metadata
`podcast_feed_url`	TEXT	Podcast RSS feed URL
`podcast_settings`	JSONB	Podcast settings
`church_center_url`	TEXT	Church Center URL
`url`	TEXT	Channel URL
`published`	BOOLEAN	Publication status
`position`	INTEGER	Display position
`enable_audio`	BOOLEAN	Audio enabled flag
`enable_on_demand_video`	BOOLEAN	On-demand video enabled
`enable_watch_live`	BOOLEAN	Live watch enabled
`general_chat_enabled`	BOOLEAN	Chat enabled flag
`group_chat_enabled`	BOOLEAN	Group chat enabled
`sermon_notes_enabled`	BOOLEAN	Sermon notes enabled
`can_enable_chat`	BOOLEAN	Can enable chat
`default_video_duration`	INTEGER	Default video duration
`default_video_embed_code`	TEXT	Default video embed code
`default_video_url`	TEXT	Default video URL
`activate_episode_minutes_before`	INTEGER	Episode activation lead time
`services_service_type_remote_identifier`	VARCHAR(64)	Services app reference
`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
`first_name`	VARCHAR(255)	Speaker first name
`last_name`	VARCHAR(255)	Speaker last name
`formatted_name`	VARCHAR(255)	Full formatted name
`name_prefix`	VARCHAR(50)	Name prefix (Dr., Rev., etc.)
`name_suffix`	VARCHAR(50)	Name suffix
`speaker_type`	VARCHAR(50)	Speaker type
`avatar_url`	VARCHAR(255)	Speaker avatar/photo URL
`episodes_count`	INTEGER	Number of episodes
`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 (links to episodes via `publishing_episodes_relationships` with `relationship_type='speakerships'`; links to speakers via `publishing_speakerships_relationships` with `relationship_type='speaker'`)
`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 (links to episodes via `publishing_episodes_relationships` with `relationship_type='episode_resources'`)
`title`	TEXT	Resource title
`kind`	VARCHAR(255)	Resource kind
`type`	VARCHAR(255)	Resource type
`icon`	TEXT	Resource icon
`url`	TEXT	Resource URL
`featured`	BOOLEAN	Featured resource flag
`position`	INTEGER	Display position
`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 (links to episodes via `publishing_episodes_relationships` with `relationship_type='note_template'`)
`auto_create_free_form_notes`	BOOLEAN	Whether to auto-create free-form notes
`enabled`	BOOLEAN	Whether template is enabled
`published_at`	TIMESTAMP	When template was published
`template`	TEXT	Template content
`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 (links to episodes via `publishing_episodes_relationships` with `relationship_type='episode_times'`)
`starts_at`	TIMESTAMP	Episode start time
`ends_at`	TIMESTAMP	Episode end time
`current_state`	VARCHAR(50)	Current publish state
`streaming_service`	VARCHAR(50)	Streaming service
`video_url`	TEXT	Episode video URL
`video_embed_code`	TEXT	Video embed code
`caveats`	JSONB	Publishing caveats
`timestamp_current`	DOUBLE PRECISION	Current playback timestamp in seconds
`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 (links to channels via `publishing_channels_relationships` with `relationship_type='channel_default_times'`)
`day_of_week`	INTEGER	Day (0=Sunday, 6=Saturday)
`hour`	INTEGER	Publish hour
`minute`	INTEGER	Publish minute
`frequency`	VARCHAR(50)	Recurrence frequency
`position`	INTEGER	Display position
`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 (links to channels via `publishing_channels_relationships` with `relationship_type='next_times'`)
`starts_at`	TIMESTAMP	Next scheduled publish time
`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_statistics_id`	VARCHAR(64)	Planning Center statistic ID — follows pattern `‘es-‘	episode_id` for joining
`title`	VARCHAR(255)	Episode title (snapshot)
`library_watch_count`	INTEGER	Library watch count
`live_watch_count`	INTEGER	Live watch count
`published_live_at`	TIMESTAMP	When episode went live
`published_to_library_at`	TIMESTAMP	When added to library
`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_statistics_id`	VARCHAR(64)	Parent episode statistics ID (matches `publishing_episode_statistics.episode_statistics_id`)
`time_id`	VARCHAR(64)	Planning Center time ID
`starts_at`	TIMESTAMP	Period start time
`watch_count`	INTEGER	Watches in period
`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
`subdomain`	VARCHAR(255)	Organization subdomain
`downloads_used`	INTEGER	Number of downloads used
`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

All relationship tables share this structure: a parent entity ID, relationship_type (VARCHAR(50)), and relationship_id (VARCHAR(64)) to identify the related record, plus standard system fields.

14. `publishing_channels_relationships`

Links channels to related entities (default times, next times, episodes).

Column	Type	Description
`id`	UUID	Unique internal identifier
`channel_id`	VARCHAR(64)	Parent channel 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 lifecycle status
`system_created_at`	TIMESTAMP	System creation time
`system_updated_at`	TIMESTAMP	System update time

Common relationship types:

channel_default_times - Links to publishing_channel_default_times
next_times - Links to publishing_channel_next_times

15. `publishing_episodes_relationships`

Links episodes to related entities (series, channels, resources, times, speakerships, note templates).

Column	Type	Description
`id`	UUID	Unique internal identifier
`episode_id`	VARCHAR(64)	Parent episode 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 lifecycle status
`system_created_at`	TIMESTAMP	System creation time
`system_updated_at`	TIMESTAMP	System update time

Common relationship types:

series - Links to publishing_series
channel - Links to publishing_channels
episode_resources - Links to publishing_episode_resources
episode_times - Links to publishing_episode_times
speakerships - Links to publishing_speakerships
note_template - Links to publishing_note_templates

16. `publishing_series_relationships`

Links series to related entities (channels, episodes).

Column	Type	Description
`id`	UUID	Unique internal identifier
`series_id`	VARCHAR(64)	Parent series 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 lifecycle status
`system_created_at`	TIMESTAMP	System creation time
`system_updated_at`	TIMESTAMP	System update time

17. `publishing_speakerships_relationships`

Links speakerships to related entities (speakers, episodes).

Column	Type	Description
`id`	UUID	Unique internal identifier
`speakership_id`	VARCHAR(64)	Parent speakership 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 lifecycle status
`system_created_at`	TIMESTAMP	System creation time
`system_updated_at`	TIMESTAMP	System update time

Common relationship types:

speaker - Links to publishing_speakers
episode - Links to publishing_episodes

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:

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

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: Any monetization or purchase amount columns are stored in cents - divide by 100.0 for display
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
Relationship Tables: All inter-entity links are stored in *_relationships tables — use publishing_episodes_relationships, publishing_channels_relationships, etc. for joins

Common Mistakes to Avoid

Missing Schema Prefix
- ❌ FROM publishing_episodes
- ✅ FROM planning_center.publishing_episodes
Adding Redundant RLS Filters
- ❌ WHERE tenant_organization_id = 1 AND system_status = 'active'
- ✅ Trust RLS to handle this automatically
Joining Without Schema
- ❌ JOIN publishing_series s ON ...
- ✅ JOIN planning_center.publishing_series s ON ...
Skipping Currency Conversion
- ❌ SELECT purchase_price_cents as purchase_price
- ✅ SELECT purchase_price_cents / 100.0 as purchase_price

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 by publication state or media availability when relevant
- Consider CTEs for aggregating view/download metrics
- Use relationship tables for all cross-entity joins

Data Synchronization

Publishing data is synchronized through Temporal workflows:

Master Workflow - Orchestrates all child workflows
Independent Entities - Channels, Series, Speakers, Organizations
Dependent Entities - Episodes (depends on channels)
Related Data - Resources, times, statistics (depends on episodes)
Junction Tables - Speakerships linking episodes to speakers

Usage Tips

Trust RLS for active data - Skip manual system_status or tenant filters
Join through relationship tables - Use publishing_episodes_relationships, publishing_channels_relationships, etc.
Consider NULL values - Many fields are optional
Use JSONB operators for nested data in art, sermon_audio, page_actions fields
Aggregate statistics over time periods for trends

Common Join Patterns

Episodes with Series and Speakers

SELECT 
    e.title as episode_title,
    s.title as series_title,
    sp.formatted_name as speaker_name
FROM planning_center.publishing_episodes e
LEFT JOIN planning_center.publishing_episodes_relationships er_series
    ON er_series.episode_id = e.episode_id AND er_series.relationship_type = 'series'
LEFT JOIN planning_center.publishing_series s ON s.series_id = er_series.relationship_id
LEFT JOIN planning_center.publishing_episodes_relationships er_ship
    ON er_ship.episode_id = e.episode_id AND er_ship.relationship_type = 'speakerships'
LEFT JOIN planning_center.publishing_speakerships_relationships spr
    ON spr.speakership_id = er_ship.relationship_id AND spr.relationship_type = 'speaker'
LEFT JOIN planning_center.publishing_speakers sp ON sp.speaker_id = spr.relationship_id;

Episodes with Statistics and Resources

SELECT 
    e.title,
    est.library_watch_count,
    est.live_watch_count,
    er.title as resource_title,
    er.url as resource_url
FROM planning_center.publishing_episodes e
LEFT JOIN planning_center.publishing_episode_statistics est 
    ON est.episode_statistics_id = 'es-' || e.episode_id
LEFT JOIN planning_center.publishing_episodes_relationships er_res
    ON er_res.episode_id = e.episode_id AND er_res.relationship_type = 'episode_resources'
LEFT JOIN planning_center.publishing_episode_resources er ON er.episode_resource_id = er_res.relationship_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 (episode_statistics_id = 'es-' || episode_id)
Speakerships link episodes to speakers via publishing_episodes_relationships and publishing_speakerships_relationships
Episodes link to channels, series, and resources via publishing_episodes_relationships

This data model enables comprehensive media analytics and content management for your church’s publishing ministry.

​Overview

​Visual Data Model

​Core Entity Relationships

​Key Relationships Explained

​Query Requirements

​Schema Prefix

​Row Level Security (RLS)

​Complete Table Inventory

​Core Content Tables

​1. publishing_episodes

​2. publishing_series

​3. publishing_channels

​4. publishing_speakers

​Relationship Tables

​5. publishing_speakerships

​Resource and Media Tables

​6. publishing_episode_resources

​7. publishing_note_templates

​Scheduling Tables

​8. publishing_episode_times

​9. publishing_channel_default_times

​10. publishing_channel_next_times

​Analytics Tables

​11. publishing_episode_statistics

​12. publishing_episode_statistic_times

​Configuration Tables

​13. publishing_organizations

​Relationship Tables

​14. publishing_channels_relationships

​15. publishing_episodes_relationships

​16. publishing_series_relationships

​17. publishing_speakerships_relationships

​System Fields

​Row Level Security

​Data Integrity Rules

​Common Mistakes to Avoid

​Performance Considerations

​Data Synchronization

​Usage Tips

​Common Join Patterns

​Episodes with Series and Speakers

​Episodes with Statistics and Resources

​Data Quality Notes

Overview

Visual Data Model

Core Entity Relationships

Key Relationships Explained

Query Requirements

Schema Prefix

Row Level Security (RLS)

Complete Table Inventory

Core Content Tables

1. `publishing_episodes`

2. `publishing_series`

3. `publishing_channels`

4. `publishing_speakers`

Relationship Tables

5. `publishing_speakerships`

Resource and Media Tables

6. `publishing_episode_resources`

7. `publishing_note_templates`

Scheduling Tables

8. `publishing_episode_times`

9. `publishing_channel_default_times`

10. `publishing_channel_next_times`

Analytics Tables

11. `publishing_episode_statistics`

12. `publishing_episode_statistic_times`

Configuration Tables

13. `publishing_organizations`

Relationship Tables

14. `publishing_channels_relationships`

15. `publishing_episodes_relationships`

16. `publishing_series_relationships`

17. `publishing_speakerships_relationships`

System Fields

Row Level Security

Data Integrity Rules

Common Mistakes to Avoid

Performance Considerations

Data Synchronization

Usage Tips

Common Join Patterns

Episodes with Series and Speakers

Episodes with Statistics and Resources

Data Quality Notes