​

Planning Center Publishing Data Model - Complete Documentation

​

Overview

• 17 entity tables - Episodes, series, channels, speakers, and supporting data
• 1 primary relationship table - Speakerships linking episodes to speakers
• Comprehensive metrics - View counts, downloads, and engagement tracking

​

Query Requirements

​

Schema Prefix

​

Row Level Security (RLS)

• tenant_organization_id: You only see data for your current organization
• system_status: You only see ‘active’ records by default

• ❌ WHERE tenant_organization_id = 1 (unnecessary)
• ❌ WHERE system_status = 'active' (unnecessary)

​

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

​

14. publishing_channel_default_episode_resources

​

Administrative Tables

​

15. publishing_onboardings

​

16. publishing_jolt_tokens

​

17. publishing_page_restrictions

​

System Fields

• 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

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

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

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

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