Database Schema

File: 04-database-schema.md

Loading documentation...

# Database Schema

## Overview

CreatorContent.net uses MySQL/PostgreSQL with Laravel Eloquent ORM. All tables follow Laravel conventions with automatic timestamps (`created_at`, `updated_at`) unless otherwise specified.

## Core Tables

### Users
**Table:** `users`

Primary user authentication and profile table.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `username` | string | Unique, URL-safe identifier |
| `name` | string | Full display name |
| `email` | string | Unique email address |
| `phone` | string | Unique phone (E.164 format) |
| `password` | string | Bcrypt hashed password |
| `role` | enum | 'creator' or 'viewer' |
| `email_verified_at` | timestamp | Email verification timestamp |
| `phone_verified_at` | timestamp | Phone verification timestamp |
| `two_factor_enabled` | boolean | Two-factor auth enabled |
| `is_admin` | boolean | Admin privileges |
| `is_suspended` | boolean | Account suspension status |
| `stripe_id` | string | Stripe customer ID |
| `pm_type` | string | Payment method type |
| `pm_last_four` | string | Last 4 digits of payment method |

**Relationships:**
- `hasMany`: Podcasts, Episodes, Blogs, Posts, Events, Media, PublicProfiles, PublicPages, Subscriptions, TokenPurchases
- `hasOne`: PublicProfile (primary)

### Login Tokens
**Table:** `login_tokens`

One-time passwords for email/SMS authentication.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `token` | string | 8-digit OTP |
| `type` | enum | 'email', 'phone', 'phone_change', 'password_reset' |
| `expires_at` | timestamp | Expiration (10 minutes) |

## Subscription & Billing

### Plans
**Table:** `plans`

Subscription plan definitions.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `name` | string | Plan name |
| `slug` | string | Unique URL slug |
| `description` | text | Plan description |
| `stripe_price_id` | string | Stripe Price ID |
| `stripe_product_id` | string | Stripe Product ID |
| `amount` | decimal | Monthly price |
| `interval` | string | 'month' or 'year' |
| `is_active` | boolean | Available for purchase |
| `is_default` | boolean | Default plan for new users |
| `features` | json | Plan features configuration |

### Plan Features
**Table:** `plan_features`

Feature limits per plan.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `plan_id` | bigint | Foreign key to plans |
| `feature_type` | enum | Feature identifier |
| `limit` | integer | Usage limit (-1 = unlimited) |

### Subscriptions
**Table:** `subscriptions`

Active user subscriptions (Laravel Cashier).

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `plan_id` | bigint | Foreign key to plans |
| `stripe_id` | string | Stripe subscription ID |
| `stripe_status` | string | Active subscription status |
| `stripe_price` | string | Stripe Price ID |
| `quantity` | integer | Subscription quantity |
| `trial_ends_at` | timestamp | Trial end date |
| `ends_at` | timestamp | Cancellation date |

### Subscription Payments
**Table:** `subscription_payments`

Payment history for subscriptions.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `subscription_id` | bigint | Foreign key to subscriptions |
| `stripe_payment_intent_id` | string | Stripe Payment Intent ID |
| `amount` | decimal | Payment amount |
| `currency` | string | Currency code |
| `status` | string | Payment status |
| `paid_at` | timestamp | Payment completion date |

### Token Packages
**Table:** `token_packages`

One-time token purchase packages.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `name` | string | Package name |
| `description` | text | Package description |
| `tokens` | integer | Number of tokens |
| `price` | decimal | Package price |
| `stripe_price_id` | string | Stripe Price ID |
| `is_active` | boolean | Available for purchase |
| `feature_type` | enum | Token type |

### Token Purchases
**Table:** `token_purchases`

User token purchase records.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `package_id` | bigint | Foreign key to token_packages |
| `amount_purchased` | integer | Original purchase amount |
| `amount_remaining` | integer | Remaining tokens |
| `status` | enum | 'pending', 'active', 'expired' |
| `stripe_payment_intent_id` | string | Stripe Payment Intent ID |

### Token Usage Logs
**Table:** `token_usage_logs`

Detailed token usage tracking.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `token_purchase_id` | bigint | Foreign key to token_purchases |
| `feature_type` | enum | Type of feature used |
| `tokens_used` | integer | Tokens consumed |
| `status` | enum | 'pending', 'completed', 'failed' |
| `metadata` | json | Additional usage data |

### User Usage
**Table:** `user_usage`

Monthly allocation tracking per feature type.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `feature_type` | enum | Feature identifier |
| `monthly_allocation` | integer | Monthly limit |
| `current_usage` | integer | Current month usage |
| `reset_at` | timestamp | Next reset date |

## Public Profiles & Pages

### Public Profiles
**Table:** `public_profiles`

Creator public profile pages.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `slug` | string | Unique URL slug |
| `display_name` | string | Profile display name |
| `bio` | text | Profile biography |
| `avatar_media_id` | bigint | Foreign key to media |
| `banner_media_id` | bigint | Foreign key to media |
| `theme_color` | string | Primary theme color |
| `background_color` | string | Background color |
| `custom_domain` | string | Custom domain name |
| `is_public` | boolean | Public visibility |
| `is_active` | boolean | Active status |
| `is_verified` | boolean | Verified badge |
| `social_links` | json | Social media links |
| `seo_settings` | json | SEO metadata |
| `custom_css` | json | Custom CSS rules |
| `settings` | json | Profile settings |
| `base_template_id` | bigint | Foreign key to base_templates |
| `last_activity` | timestamp | Last activity timestamp |

**Relationships:**
- `belongsTo`: User, BaseTemplate, avatarMedia, bannerMedia
- `hasMany`: ProfileSections, ProfileLinks, ProfileDomains, PublicPages (via pivot)

### Profile Sections
**Table:** `profile_sections`

Dynamic content sections on public profiles.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `public_profile_id` | bigint | Foreign key to public_profiles |
| `section_type_id` | bigint | Foreign key to section_types |
| `section_template_id` | bigint | Foreign key to section_templates |
| `order` | integer | Display order |
| `data` | json | Section variable values |
| `individual_page` | boolean | Render as separate page |

### Profile Links
**Table:** `profile_links`

Featured links on public profiles.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `public_profile_id` | bigint | Foreign key to public_profiles |
| `title` | string | Link title |
| `url` | string | Link URL |
| `icon` | string | Font Awesome icon class |
| `order` | integer | Display order |
| `open_in_new_tab` | boolean | Open in new window |
| `linkable_type` | string | Polymorphic type |
| `linkable_id` | bigint | Polymorphic ID |

### Profile Domains
**Table:** `profile_domains` (aliased as `custom_domains`)

Custom domain configuration.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `public_profile_id` | bigint | Foreign key to public_profiles |
| `domain` | string | Custom domain name |
| `status` | enum | 'pending', 'verified', 'failed' |
| `dns_records` | json | Required DNS records |
| `verified_at` | timestamp | DNS verification timestamp |

### Public Pages
**Table:** `public_pages`

Standalone public pages.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `slug` | string | Unique slug (per user) |
| `title` | string | Page title |
| `description` | text | Page description |
| `is_public` | boolean | Public visibility |
| `base_template_id` | bigint | Foreign key to base_templates |

**Relationships:**
- `belongsTo`: User, BaseTemplate
- `hasMany`: PageSections
- `belongsToMany`: PublicProfiles (pivot table: `public_profile_public_page`)

### Page Sections
**Table:** `page_sections`

Content sections on public pages.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `public_page_id` | bigint | Foreign key to public_pages |
| `section_type_id` | bigint | Foreign key to section_types |
| `section_template_id` | bigint | Foreign key to section_templates |
| `order` | integer | Display order |
| `data` | json | Section variable values |
| `background_color` | string | Section background color |
| `background_image_media_id` | bigint | Foreign key to media |

## Section System

### Section Types
**Table:** `section_types`

Available section types (podcasts, blogs, events, etc.).

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `name` | string | Type name |
| `slug` | string | Unique slug |
| `description` | text | Type description |
| `is_active` | boolean | Available for use |

### Section Templates
**Table:** `section_templates`

Blade templates for rendering sections.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `section_type_id` | bigint | Foreign key to section_types |
| `name` | string | Template name |
| `description` | text | Template description |
| `storage_type` | enum | 'database' or 'file' |
| `content` | text | Template content (if database) |
| `file_path` | string | Template file path (if file) |
| `variables` | json | Variable definitions |
| `is_active` | boolean | Available for use |

### Section Variables
**Table:** `section_variables`

Variable definitions for section templates.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `section_type_id` | bigint | Foreign key to section_types |
| `name` | string | Variable name |
| `type` | enum | Variable type (text, number, select, etc.) |
| `label` | string | Display label |
| `default_value` | string | Default value |
| `options` | json | Options for select/list types |

### Section Variable Values
**Table:** `section_variable_values`

Actual values for section variables.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `sectionable_type` | string | Polymorphic type |
| `sectionable_id` | bigint | Polymorphic ID |
| `section_variable_id` | bigint | Foreign key to section_variables |
| `value` | text | Variable value |

### Base Templates
**Table:** `base_templates`

Layout templates for profiles and pages.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `name` | string | Template name |
| `slug` | string | Unique slug |
| `header_template` | text | Header template content |
| `footer_template` | text | Footer template content |
| `view_path` | string | Optional view file path |
| `is_active` | boolean | Available for use |
| `is_default` | boolean | Default template |

## Content Management

### Media
**Table:** `media`

User-uploaded media files.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `type` | enum | 'image', 'audio', 'video', 'document' |
| `path` | string | Storage path |
| `thumbnail_path` | string | Thumbnail storage path |
| `title` | string | Media title |
| `description` | text | Media description |
| `alt_text` | string | Alt text for images |
| `file_size` | integer | File size in bytes |
| `duration` | integer | Duration in seconds (audio/video) |
| `mime_type` | string | MIME type |
| `source_media_id` | bigint | Foreign key to media (for derivatives) |
| `is_active` | boolean | Active status |
| `status` | enum | 'processing', 'completed', 'failed' |

**Relationships:**
- `belongsTo`: User, sourceMedia
- `hasMany`: derivedMedia, clips
- `belongsToMany`: Tags

### Media Clips
**Table:** `media_clips`

Short clips extracted from media files.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `media_id` | bigint | Foreign key to media |
| `title` | string | Clip title |
| `start_time` | decimal | Start time in seconds |
| `end_time` | decimal | End time in seconds |
| `duration` | decimal | Clip duration |

### Media Lists
**Table:** `media_lists`

Playlists and media collections.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `name` | string | List name |
| `description` | text | List description |
| `type` | enum | 'playlist', 'album', 'collection' |
| `is_public` | boolean | Public visibility |

### Media List Items
**Table:** `media_list_items`

Media items in lists (pivot with order).

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `media_list_id` | bigint | Foreign key to media_lists |
| `media_id` | bigint | Foreign key to media |
| `order` | integer | Display order |

## Podcasts & Episodes

### Podcasts
**Table:** `podcasts`

Podcast series.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `title` | string | Podcast title |
| `description` | text | Podcast description |
| `slug` | string | Unique slug (soft-delete aware) |
| `image_media_id` | bigint | Foreign key to media |
| `external_image_url` | string | External image URL (RSS feeds) |
| `rss_feed_url` | string | Source RSS feed URL |
| `rss_type` | enum | 'native' or 'external' |
| `rss_raw_xml` | text | Cached RSS XML |
| `meta_title` | string | SEO meta title |
| `meta_description` | text | SEO meta description |
| `status` | enum | 'draft', 'published' |
| `deleted_at` | timestamp | Soft delete timestamp |

**Relationships:**
- `belongsTo`: User, imageMedia
- `hasMany`: Episodes, Seasons
- `belongsToMany`: Contributors, Categories, Tags

### Episodes
**Table:** `episodes`

Individual podcast episodes.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `podcast_id` | bigint | Foreign key to podcasts |
| `season_id` | bigint | Foreign key to seasons |
| `title` | string | Episode title |
| `description` | text | Episode description |
| `content` | text | Episode content |
| `slug` | string | Unique slug |
| `rss_guid` | string | RSS GUID |
| `episode_number` | integer | Episode number |
| `season_number` | integer | Season number |
| `duration_seconds` | integer | Duration in seconds |
| `duration_formatted` | string | Formatted duration |
| `image_media_id` | bigint | Foreign key to media |
| `external_image_url` | string | External image URL |
| `audio_media_id` | bigint | Foreign key to media |
| `external_audio_url` | string | External audio URL |
| `meta_title` | string | SEO meta title |
| `meta_description` | text | SEO meta description |
| `status` | enum | 'draft', 'published' |
| `featured` | boolean | Featured episode |
| `explicit` | boolean | Explicit content flag |
| `published_at` | timestamp | Publication date |
| `deleted_at` | timestamp | Soft delete timestamp |

**Relationships:**
- `belongsTo`: Podcast, Season, imageMedia, audioMedia
- `belongsToMany`: Contributors, Categories, Tags

### Seasons
**Table:** `seasons`

Podcast seasons.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `podcast_id` | bigint | Foreign key to podcasts |
| `name` | string | Season name |
| `season_number` | integer | Season number |
| `description` | text | Season description |
| `deleted_at` | timestamp | Soft delete timestamp |

### Transcriptions
**Table:** `transcriptions`

AI-generated episode transcriptions.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `episode_id` | bigint | Foreign key to episodes |
| `type` | enum | Transcription type |
| `status` | enum | 'pending', 'processing', 'completed', 'failed' |
| `text` | text | Transcription text |
| `process_id` | string | Background process ID |
| `deleted_at` | timestamp | Soft delete timestamp |

### Transcription Chunks
**Table:** `transcription_chunks`

Segmented transcription processing units.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `transcription_id` | bigint | Foreign key to transcriptions |
| `chunk_number` | integer | Chunk sequence number |
| `start_time` | decimal | Start time in seconds |
| `end_time` | decimal | End time in seconds |
| `text` | text | Chunk transcription |
| `status` | enum | 'pending', 'processing', 'completed', 'failed' |

## Contributors, Categories & Tags

### Contributors
**Table:** `contributors`

Podcast/episode contributors.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `name` | string | Contributor name |
| `bio` | text | Contributor biography |
| `deleted_at` | timestamp | Soft delete timestamp |

### Categories
**Table:** `categories`

Content categorization.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `name` | string | Category name |
| `slug` | string | Unique slug |
| `type` | enum | Category type |
| `deleted_at` | timestamp | Soft delete timestamp |

### Tags
**Table:** `tags`

Content tagging system.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `name` | string | Tag name |
| `slug` | string | Unique slug |
| `type` | enum | Tag type |
| `color` | string | Tag color |
| `deleted_at` | timestamp | Soft delete timestamp |

**Pivot Tables:**
- `podcast_tags`: podcasts ↔ tags
- `episode_tags`: episodes ↔ tags
- `media_tags`: media ↔ tags
- `podcast_categories`: podcasts ↔ categories
- `episode_categories`: episodes ↔ categories
- `podcast_contributors`: podcasts ↔ contributors (with `role`, `notes`)
- `episode_contributors`: episodes ↔ contributors (with `role`, `notes`)

## Blogs & Posts

### Blogs
**Table:** `blogs`

Blog collections.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `title` | string | Blog title |
| `slug` | string | Unique slug |
| `description` | text | Blog description |
| `is_public` | boolean | Public visibility |

### Posts
**Table:** `posts`

Blog posts.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `blog_id` | bigint | Foreign key to blogs |
| `title` | string | Post title |
| `slug` | string | Unique slug |
| `content` | text | Post content |
| `image_media_id` | bigint | Foreign key to media |
| `is_published` | boolean | Published status |
| `published_at` | timestamp | Publication date |

**Relationships:**
- `belongsTo`: User, Blog, imageMedia
- `hasMany`: Comments, PostLikes

### Comments
**Table:** `comments`

Polymorphic comments system.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `commentable_type` | string | Polymorphic type |
| `commentable_id` | bigint | Polymorphic ID |
| `parent_id` | bigint | Foreign key to comments (for replies) |
| `content` | text | Comment content |
| `status` | enum | 'pending', 'approved', 'rejected' |
| `is_featured` | boolean | Featured comment |
| `likes_count` | integer | Like count cache |

### Post Likes
**Table:** `post_likes`

User likes on posts.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `post_id` | bigint | Foreign key to posts |

### Comment Likes
**Table:** `comment_likes`

User likes on comments.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `comment_id` | bigint | Foreign key to comments |

## Events

### Events
**Table:** `events`

Event listings.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `public_profile_id` | bigint | Foreign key to public_profiles (nullable) |
| `event_list_id` | bigint | Foreign key to event_lists (nullable) |
| `title` | string | Event title |
| `description` | text | Event description |
| `start_date` | date | Event start date |
| `end_date` | date | Event end date |
| `location` | string | Event location |
| `image_media_id` | bigint | Foreign key to media |
| `is_public` | boolean | Public visibility |

### Event Lists
**Table:** `event_lists`

Event collections.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `title` | string | List title |
| `slug` | string | Unique slug |
| `description` | text | List description |
| `is_public` | boolean | Public visibility |

**Pivot Table:**
- `event_event_list`: events ↔ event_lists

## Surveys & Polls

### Surveys
**Table:** `surveys`

Survey forms.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `title` | string | Survey title |
| `slug` | string | Unique slug |
| `description` | text | Survey description |
| `is_public` | boolean | Public visibility |

### Survey Questions
**Table:** `survey_questions`

Questions in surveys.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `survey_id` | bigint | Foreign key to surveys |
| `question` | text | Question text |
| `type` | enum | Question type |
| `order` | integer | Display order |
| `required` | boolean | Required field |

### Survey Options
**Table:** `survey_options`

Options for multiple choice questions.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `survey_question_id` | bigint | Foreign key to survey_questions |
| `option_text` | string | Option text |
| `order` | integer | Display order |

### Survey Responses
**Table:** `survey_responses`

User survey submissions.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `survey_id` | bigint | Foreign key to surveys |
| `user_id` | bigint | Foreign key to users (nullable for anonymous) |

### Survey Answers
**Table:** `survey_answers`

Individual question answers.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `survey_response_id` | bigint | Foreign key to survey_responses |
| `survey_question_id` | bigint | Foreign key to survey_questions |
| `answer_text` | text | Answer value |

### Polls
**Table:** `polls`

Simple voting polls.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `question` | string | Poll question |
| `slug` | string | Unique slug |
| `is_public` | boolean | Public visibility |
| `allows_multiple_votes` | boolean | Multiple votes allowed |

### Poll Options
**Table:** `poll_options`

Poll answer options.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `poll_id` | bigint | Foreign key to polls |
| `option_text` | string | Option text |
| `order` | integer | Display order |
| `votes_count` | integer | Vote count cache |

### Poll Responses
**Table:** `poll_responses`

User votes on polls.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `poll_id` | bigint | Foreign key to polls |
| `poll_option_id` | bigint | Foreign key to poll_options |
| `user_id` | bigint | Foreign key to users (nullable for anonymous) |

## Studio System

### Studio Projects
**Table:** `studio_projects`

Audio editing projects.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `name` | string | Project name |
| `description` | text | Project description |
| `status` | enum | 'draft', 'rendering', 'completed', 'failed' |

### Studio Project Assets
**Table:** `studio_project_assets`

Media assets in projects.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `studio_project_id` | bigint | Foreign key to studio_projects |
| `media_id` | bigint | Foreign key to media |
| `name` | string | Asset name |

### Studio Project Clips
**Table:** `studio_project_clips`

Audio clips on timeline.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `studio_project_id` | bigint | Foreign key to studio_projects |
| `studio_project_asset_id` | bigint | Foreign key to studio_project_assets |
| `start_time` | decimal | Clip start time |
| `duration` | decimal | Clip duration |
| `volume` | decimal | Clip volume (0-1) |

### Studio Project Timeline
**Table:** `studio_project_timeline`

Timeline configuration.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `studio_project_id` | bigint | Foreign key to studio_projects |
| `clips` | json | Timeline clip configuration |

### Studio Project Outputs
**Table:** `studio_project_outputs`

Rendered project outputs.

## Other Tables

### Contact Submissions
**Table:** `contact_submissions`

Contact form submissions.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `public_profile_id` | bigint | Foreign key to public_profiles |
| `name` | string | Submitter name |
| `email` | string | Email address |
| `phone` | string | Phone number |
| `subject` | string | Message subject |
| `message` | text | Message content |

### Notification Subscriptions
**Table:** `notification_subscriptions`

User notification subscriptions.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `public_profile_id` | bigint | Foreign key to public_profiles |
| `email` | string | Subscriber email |
| `unsubscribed_at` | timestamp | Unsubscription timestamp |

### Notification Messages
**Table:** `notification_messages`

Sent notification messages.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `subject` | string | Message subject |
| `content` | text | Message content |
| `sent_at` | timestamp | Sent timestamp |

### Profile Analytics
**Table:** `profile_analytics`

Profile page view tracking.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `public_profile_id` | bigint | Foreign key to public_profiles |
| `public_page_id` | bigint | Foreign key to public_pages (nullable) |
| `view_date` | date | View date |
| `views` | integer | View count |

### User Activity Logs
**Table:** `user_activity_logs`

User action tracking.

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `action` | string | Action type |
| `model_type` | string | Affected model type |
| `model_id` | bigint | Affected model ID |
| `metadata` | json | Additional data |

### Page Notes
**Table:** `page_notes`

Developer notes for pages (dev tool).

| Column | Type | Description |
|--------|------|-------------|
| `id` | bigint | Primary key |
| `user_id` | bigint | Foreign key to users |
| `view_path` | string | Page view path |
| `type` | enum | 'info', 'bug', 'feature_request', 'improvement' |
| `content` | text | Note content |
| `parent_id` | bigint | Foreign key to page_notes (for replies) |
| `status` | enum | 'open', 'in_progress', 'resolved', 'closed' |

## Indexes & Constraints

### Common Indexes
- Primary keys on all tables
- Unique constraints on slugs, usernames, emails, phones
- Foreign key indexes on all foreign key columns
- Composite indexes for polymorphic relationships

### Soft Deletes
Tables with soft deletes (using `deleted_at`):
- `podcasts`
- `episodes`
- `seasons`
- `transcriptions`
- `contributors`
- `categories`
- `tags`
- `rss_feeds`

### Unique Constraints
- `users.username`, `users.email`, `users.phone`
- `public_profiles.slug`
- `public_pages.slug` (per user)
- `podcasts.slug` (soft-delete aware)
- `episodes.slug`

## Relationships Summary

### User Relationships
- Has Many: Podcasts, Episodes, Blogs, Posts, Events, Media, PublicProfiles, PublicPages, Subscriptions, TokenPurchases, ContactSubmissions
- Has One: Primary PublicProfile

### Public Profile Relationships
- Belongs To: User, BaseTemplate
- Has Many: ProfileSections, ProfileLinks, ProfileDomains
- Belongs To Many: PublicPages

### Polymorphic Relationships
- Comments: `commentable_type` + `commentable_id`
- Profile Links: `linkable_type` + `linkable_id`
- Section Variable Values: `sectionable_type` + `sectionable_id`

## Migration Files

All migrations are located in `database/migrations/` and follow Laravel naming conventions. Key migration groups:

- **Core**: Users, authentication, tokens
- **Billing**: Plans, subscriptions, tokens
- **Profiles**: Public profiles, sections, pages, domains
- **Content**: Media, podcasts, episodes, blogs, posts
- **Engagement**: Events, surveys, polls, comments
- **Studio**: Audio editing projects
- **Analytics**: Usage tracking, activity logs

Database Schema

Database Schema

📝 Page Notes

Add New Note