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.

OverviewCopy Anchor Link

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 ModelCopy Anchor Link

Core Entity RelationshipsCopy Anchor Link

Open diagram in new tab →

Key Relationships ExplainedCopy Anchor Link

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: 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 RequirementsCopy Anchor Link

Schema PrefixCopy Anchor Link

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)Copy Anchor Link

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 InventoryCopy Anchor Link

Core Content TablesCopy Anchor Link

1. publishing_episodesCopy Anchor Link

Individual sermons, messages, and content pieces.

2. publishing_seriesCopy Anchor Link

Sermon series and content collections.

3. publishing_channelsCopy Anchor Link

Distribution channels and platforms.

4. publishing_speakersCopy Anchor Link

Speaker profiles and information.

Relationship TablesCopy Anchor Link

5. publishing_speakershipsCopy Anchor Link

Links episodes to speakers (junction table).

Resource and Media TablesCopy Anchor Link

6. publishing_episode_resourcesCopy Anchor Link

Files and resources attached to episodes.

7. publishing_note_templatesCopy Anchor Link

Templates for sermon notes and outlines.

Scheduling TablesCopy Anchor Link

8. publishing_episode_timesCopy Anchor Link

Publishing schedule for episodes on different channels.

9. publishing_channel_default_timesCopy Anchor Link

Default publishing schedules per channel.

10. publishing_channel_next_timesCopy Anchor Link

Upcoming scheduled publishes per channel.

Analytics TablesCopy Anchor Link

11. publishing_episode_statisticsCopy Anchor Link

View counts, downloads, and engagement metrics.

12. publishing_episode_statistic_timesCopy Anchor Link

Statistics tracked over time periods.

Configuration TablesCopy Anchor Link

13. publishing_organizationsCopy Anchor Link

Organization-level publishing settings.

Relationship TablesCopy Anchor Link

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_relationshipsCopy Anchor Link

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

Common relationship types:

• channel_default_times - Links to publishing_channel_default_times
• next_times - Links to publishing_channel_next_times

15. publishing_episodes_relationshipsCopy Anchor Link

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

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_relationshipsCopy Anchor Link

Links series to related entities (channels, episodes).

17. publishing_speakerships_relationshipsCopy Anchor Link

Links speakerships to related entities (speakers, episodes).

Common relationship types:

• speaker - Links to publishing_speakers
• episode - Links to publishing_episodes

System FieldsCopy Anchor Link

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 SecurityCopy Anchor Link

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 RulesCopy Anchor Link

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. Relationship Tables: All inter-entity links are stored in *_relationships tables — use publishing_episodes_relationships, publishing_channels_relationships, etc. for joins

Common Mistakes to AvoidCopy Anchor Link

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 ConsiderationsCopy Anchor Link

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 relationship tables for all cross-entity joins

Data SynchronizationCopy Anchor Link

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 TipsCopy Anchor Link

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

Common Join PatternsCopy Anchor Link

Episodes with Series and SpeakersCopy Anchor Link

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 ResourcesCopy Anchor Link

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 NotesCopy Anchor Link

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