thrilltrack-explorer/docs/submission-pipeline/SCHEMA_REFERENCE.md

Submission Pipeline Schema Reference

Critical Document: This reference maps all entity types to their exact database schema fields across the entire submission pipeline to prevent schema mismatches.

Last Updated: 2025-11-08
Status: ✅ All schemas audited and verified

---

Table of Contents

1. Overview
2. Parks
3. Rides
4. Companies
5. Ride Models
6. Photos
7. Timeline Events
8. Critical Functions Reference
9. Common Pitfalls

---

Overview

Pipeline Flow

User Input → *_submissions table → submission_items → Moderation → 
process_approval_transaction → create/update_entity_from_submission → 
Main entity table → Version trigger → *_versions table

Entity Types

• park - Theme parks and amusement parks
• ride - Individual rides and attractions
• company - Used for: manufacturer, operator, designer, property_owner
• ride_model - Ride model templates
• photo - Entity photos
• timeline_event - Historical events

---

Parks

Main Table: parks

Required Fields:

• id (uuid, PK)
• name (text, NOT NULL)
• slug (text, NOT NULL, UNIQUE)
• park_type (text, NOT NULL) - Values: theme_park, amusement_park, water_park, etc.
• status (text, NOT NULL) - Values: operating, closed, under_construction, etc.

Optional Fields:

• description (text)
• location_id (uuid, FK → locations)
• operator_id (uuid, FK → companies)
• property_owner_id (uuid, FK → companies)
• opening_date (date)
• closing_date (date)
• opening_date_precision (text) - Values: year, month, day
• closing_date_precision (text)
• website_url (text)
• phone (text)
• email (text)
• banner_image_url (text)
• banner_image_id (text)
• card_image_url (text)
• card_image_id (text)

Metadata Fields:

• view_count_all (integer, default: 0)
• view_count_30d (integer, default: 0)
• view_count_7d (integer, default: 0)
• average_rating (numeric, default: 0.00)
• review_count (integer, default: 0)
• created_at (timestamptz)
• updated_at (timestamptz)
• is_test_data (boolean, default: false)

Submission Table: park_submissions

Schema Identical to Main Table (excluding auto-generated fields like id, timestamps)

Additional Field:

• submission_id (uuid, NOT NULL, FK → content_submissions)
• temp_location_data (jsonb) - For pending location creation

Version Table: park_versions

All Main Table Fields PLUS:

• version_id (uuid, PK)
• park_id (uuid, NOT NULL, FK → parks)
• version_number (integer, NOT NULL)
• change_type (version_change_type, NOT NULL) - Values: created, updated, restored
• change_reason (text)
• is_current (boolean, default: true)
• created_by (uuid, FK → auth.users)
• created_at (timestamptz)
• submission_id (uuid, FK → content_submissions)

---

Rides

Main Table: rides

Required Fields:

• id (uuid, PK)
• name (text, NOT NULL)
• slug (text, NOT NULL, UNIQUE)
• park_id (uuid, NOT NULL, FK → parks)
• category (text, NOT NULL) ⚠️ CRITICAL: This field is required
  • Values: roller_coaster, water_ride, dark_ride, flat_ride, transport, kids_ride
• status (text, NOT NULL)
  • Values: operating, closed, under_construction, sbno, etc.

⚠️ IMPORTANT: rides table does NOT have ride_type column!

• ride_type only exists in ride_models table
• Using ride_type in rides updates will cause "column does not exist" error

Optional Relationship Fields:

• manufacturer_id (uuid, FK → companies)
• designer_id (uuid, FK → companies)
• ride_model_id (uuid, FK → ride_models)

Optional Descriptive Fields:

• description (text)
• opening_date (date)
• closing_date (date)
• opening_date_precision (text)
• closing_date_precision (text)

Optional Technical Fields:

• height_requirement (integer) - Height requirement in cm
• age_requirement (integer)
• max_speed_kmh (numeric)
• duration_seconds (integer)
• capacity_per_hour (integer)
• max_g_force (numeric)
• inversions (integer) - Number of inversions
• length_meters (numeric)
• max_height_meters (numeric)
• drop_height_meters (numeric)

Category-Specific Fields:

Roller Coasters:

• ride_sub_type (text)
• coaster_type (text)
• seating_type (text)
• intensity_level (text)
• track_material (text)
• support_material (text)
• propulsion_method (text)

Water Rides:

• water_depth_cm (integer)
• splash_height_meters (numeric)
• wetness_level (text)
• flume_type (text)
• boat_capacity (integer)

Dark Rides:

• theme_name (text)
• story_description (text)
• show_duration_seconds (integer)
• animatronics_count (integer)
• projection_type (text)
• ride_system (text)
• scenes_count (integer)

Flat Rides:

• rotation_type (text)
• motion_pattern (text)
• platform_count (integer)
• swing_angle_degrees (numeric)
• rotation_speed_rpm (numeric)
• arm_length_meters (numeric)
• max_height_reached_meters (numeric)

Kids Rides:

• min_age (integer)
• max_age (integer)
• educational_theme (text)
• character_theme (text)

Transport:

• transport_type (text)
• route_length_meters (numeric)
• stations_count (integer)
• vehicle_capacity (integer)
• vehicles_count (integer)
• round_trip_duration_seconds (integer)

Image Fields:

• banner_image_url (text)
• banner_image_id (text)
• card_image_url (text)
• card_image_id (text)
• image_url (text) - Legacy field

Metadata Fields:

• view_count_all (integer, default: 0)
• view_count_30d (integer, default: 0)
• view_count_7d (integer, default: 0)
• average_rating (numeric, default: 0.00)
• review_count (integer, default: 0)
• created_at (timestamptz)
• updated_at (timestamptz)
• is_test_data (boolean, default: false)

Submission Table: ride_submissions

Schema Identical to Main Table (excluding auto-generated fields)

Additional Fields:

• submission_id (uuid, NOT NULL, FK → content_submissions)

Version Table: ride_versions

All Main Table Fields PLUS:

• version_id (uuid, PK)
• ride_id (uuid, NOT NULL, FK → rides)
• version_number (integer, NOT NULL)
• change_type (version_change_type, NOT NULL)
• change_reason (text)
• is_current (boolean, default: true)
• created_by (uuid, FK → auth.users)
• created_at (timestamptz)
• submission_id (uuid, FK → content_submissions)

⚠️ Field Name Differences (Version Table vs Main Table):

• height_requirement_cm in versions → height_requirement in rides
• gforce_max in versions → max_g_force in rides
• inversions_count in versions → inversions in rides
• height_meters in versions → max_height_meters in rides
• drop_meters in versions → drop_height_meters in rides

---

Companies

Used For: manufacturer, operator, designer, property_owner

Main Table: companies

Required Fields:

• id (uuid, PK)
• name (text, NOT NULL)
• slug (text, NOT NULL, UNIQUE)
• company_type (text, NOT NULL)
  • Values: manufacturer, operator, designer, property_owner

Optional Fields:

• description (text)
• person_type (text, default: 'company')
  • Values: company, individual
• founded_year (integer)
• founded_date (date)
• founded_date_precision (text)
• headquarters_location (text)
• website_url (text)
• logo_url (text)
• banner_image_url (text)
• banner_image_id (text)
• card_image_url (text)
• card_image_id (text)

Metadata Fields:

• view_count_all (integer, default: 0)
• view_count_30d (integer, default: 0)
• view_count_7d (integer, default: 0)
• average_rating (numeric, default: 0.00)
• review_count (integer, default: 0)
• created_at (timestamptz)
• updated_at (timestamptz)
• is_test_data (boolean, default: false)

Submission Table: company_submissions

Schema Identical to Main Table (excluding auto-generated fields)

Additional Field:

• submission_id (uuid, NOT NULL, FK → content_submissions)

Version Table: company_versions

All Main Table Fields PLUS:

• version_id (uuid, PK)
• company_id (uuid, NOT NULL, FK → companies)
• version_number (integer, NOT NULL)
• change_type (version_change_type, NOT NULL)
• change_reason (text)
• is_current (boolean, default: true)
• created_by (uuid, FK → auth.users)
• created_at (timestamptz)
• submission_id (uuid, FK → content_submissions)

---

Ride Models

Main Table: ride_models

Required Fields:

• id (uuid, PK)
• name (text, NOT NULL)
• slug (text, NOT NULL, UNIQUE)
• manufacturer_id (uuid, NOT NULL, FK → companies)
• category (text, NOT NULL) ⚠️ CRITICAL: This field is required
  • Values: roller_coaster, water_ride, dark_ride, flat_ride, transport, kids_ride

Optional Fields:

• ride_type (text) ⚠️ This field exists in ride_models but NOT in rides
  • More specific classification than category
  • Example: category = roller_coaster, ride_type = inverted_coaster
• description (text)
• banner_image_url (text)
• banner_image_id (text)
• card_image_url (text)
• card_image_id (text)

Metadata Fields:

• view_count_all (integer, default: 0)
• view_count_30d (integer, default: 0)
• view_count_7d (integer, default: 0)
• average_rating (numeric, default: 0.00)
• review_count (integer, default: 0)
• installations_count (integer, default: 0)
• created_at (timestamptz)
• updated_at (timestamptz)
• is_test_data (boolean, default: false)

Submission Table: ride_model_submissions

Schema Identical to Main Table (excluding auto-generated fields)

Additional Field:

• submission_id (uuid, NOT NULL, FK → content_submissions)

Version Table: ride_model_versions

All Main Table Fields PLUS:

• version_id (uuid, PK)
• ride_model_id (uuid, NOT NULL, FK → ride_models)
• version_number (integer, NOT NULL)
• change_type (version_change_type, NOT NULL)
• change_reason (text)
• is_current (boolean, default: true)
• created_by (uuid, FK → auth.users)
• created_at (timestamptz)
• submission_id (uuid, FK → content_submissions)

---

Photos

Main Table: photos

Required Fields:

• id (uuid, PK)
• cloudflare_id (text, NOT NULL)
• url (text, NOT NULL)
• entity_type (text, NOT NULL)
• entity_id (uuid, NOT NULL)
• uploader_id (uuid, NOT NULL, FK → auth.users)

Optional Fields:

• title (text)
• caption (text)
• taken_date (date)
• taken_date_precision (text)
• photographer_name (text)
• order_index (integer, default: 0)
• is_primary (boolean, default: false)
• status (text, default: 'active')

Metadata Fields:

• created_at (timestamptz)
• updated_at (timestamptz)
• is_test_data (boolean, default: false)

Submission Table: photo_submissions

Required Fields:

• id (uuid, PK)
• submission_id (uuid, NOT NULL, FK → content_submissions)
• entity_type (text, NOT NULL)
• entity_id (uuid, NOT NULL)
• cloudflare_id (text, NOT NULL)
• url (text, NOT NULL)

Optional Fields:

• title (text)
• caption (text)
• taken_date (date)
• taken_date_precision (text)
• photographer_name (text)
• order_index (integer)

Note: Photos do NOT have version tables - they are immutable after approval

---

Timeline Events

Main Table: entity_timeline_events

Required Fields:

• id (uuid, PK)
• entity_type (text, NOT NULL)
• entity_id (uuid, NOT NULL)
• event_type (text, NOT NULL)
  • Values: opening, closing, relocation, renovation, name_change, ownership_change, etc.
• title (text, NOT NULL)
• event_date (date, NOT NULL)

Optional Fields:

• description (text)
• event_date_precision (text, default: 'day')
• from_value (text)
• to_value (text)
• from_entity_id (uuid)
• to_entity_id (uuid)
• from_location_id (uuid)
• to_location_id (uuid)
• is_public (boolean, default: true)
• display_order (integer, default: 0)

Approval Fields:

• created_by (uuid, FK → auth.users)
• approved_by (uuid, FK → auth.users)
• submission_id (uuid, FK → content_submissions)

Metadata Fields:

• created_at (timestamptz)
• updated_at (timestamptz)

Submission Table: timeline_event_submissions

Schema Identical to Main Table (excluding auto-generated fields)

Additional Field:

• submission_id (uuid, NOT NULL, FK → content_submissions)

Note: Timeline events do NOT have version tables

---

Critical Functions Reference

1. create_entity_from_submission

Purpose: Creates new entities from approved submissions

Parameters:

• p_entity_type (text) - Entity type identifier
• p_data (jsonb) - Entity data from submission
• p_created_by (uuid) - User who created it
• p_submission_id (uuid) - Source submission

Critical Requirements:

• ✅ MUST extract category for rides and ride_models
• ✅ MUST NOT use ride_type for rides (doesn't exist)
• ✅ MUST use ride_type for ride_models (does exist)
• ✅ MUST handle all required NOT NULL fields

Returns: uuid - New entity ID

2. update_entity_from_submission

Purpose: Updates existing entities from approved edits

Parameters:

• p_entity_type (text) - Entity type identifier
• p_data (jsonb) - Updated entity data
• p_entity_id (uuid) - Existing entity ID
• p_changed_by (uuid) - User who changed it

Critical Requirements:

• ✅ MUST use COALESCE to preserve existing values
• ✅ MUST include category for rides and ride_models
• ✅ MUST NOT use ride_type for rides
• ✅ MUST use ride_type for ride_models
• ✅ MUST update updated_at timestamp

Returns: uuid - Updated entity ID

3. process_approval_transaction

Purpose: Atomic transaction for selective approval

Parameters:

• p_submission_id (uuid)
• p_item_ids (uuid[]) - Specific items to approve
• p_moderator_id (uuid)
• p_change_reason (text)

Critical Requirements:

• ✅ MUST validate all item dependencies first
• ✅ MUST extract correct fields from submission tables
• ✅ MUST set session variables for triggers
• ✅ MUST handle rollback on any error

Called By: Edge function process-selective-approval

4. create_submission_with_items

Purpose: Creates multi-item submissions atomically

Parameters:

• p_submission_id (uuid)
• p_entity_type (text)
• p_action_type (text) - create or edit
• p_items (jsonb) - Array of submission items
• p_user_id (uuid)

Critical Requirements:

• ✅ MUST resolve dependencies in order
• ✅ MUST validate all required fields per entity type
• ✅ MUST link items to submission correctly

---

Common Pitfalls

1. ❌ Using ride_type for rides

-- WRONG
UPDATE rides SET ride_type = 'inverted_coaster' WHERE id = $1;
-- ERROR: column "ride_type" does not exist

-- CORRECT
UPDATE rides SET category = 'roller_coaster' WHERE id = $1;

2. ❌ Missing category field

-- WRONG - Missing required category
INSERT INTO rides (name, slug, park_id, status) VALUES (...);
-- ERROR: null value violates not-null constraint

-- CORRECT
INSERT INTO rides (name, slug, park_id, category, status) VALUES (..., 'roller_coaster', ...);

3. ❌ Wrong column names in version tables

-- WRONG
SELECT height_requirement FROM ride_versions WHERE ride_id = $1;
-- Returns null

-- CORRECT
SELECT height_requirement_cm FROM ride_versions WHERE ride_id = $1;

4. ❌ Forgetting COALESCE in updates

-- WRONG - Overwrites fields with NULL
UPDATE rides SET 
  name = (p_data->>'name'),
  description = (p_data->>'description')
WHERE id = $1;

-- CORRECT - Preserves existing values if not provided
UPDATE rides SET 
  name = COALESCE(p_data->>'name', name),
  description = COALESCE(p_data->>'description', description)
WHERE id = $1;

5. ❌ Not handling submission_id in version triggers

-- WRONG - Version doesn't link back to submission
INSERT INTO ride_versions (ride_id, ...) VALUES (...);

-- CORRECT - Trigger must read session variable
v_submission_id := current_setting('app.submission_id', true)::uuid;
INSERT INTO ride_versions (ride_id, submission_id, ...) VALUES (..., v_submission_id, ...);

---

Validation Checklist

Before deploying any submission pipeline changes:

• All entity tables have matching submission tables
• All required NOT NULL fields are included in CREATE functions
• All required NOT NULL fields are included in UPDATE functions
• category is extracted for rides and ride_models
• ride_type is NOT used for rides
• ride_type IS used for ride_models
• COALESCE is used for all UPDATE statements
• Version table column name differences are handled
• Session variables are set for version triggers
• Foreign key relationships are validated
• Dependency resolution works correctly
• Error handling and rollback logic is present

---

Maintenance

When adding new entity types:

1. Create main table with all fields
2. Create matching submission table + submission_id FK
3. Create version table with all fields + version metadata
4. Add case to create_entity_from_submission
5. Add case to update_entity_from_submission
6. Add case to process_approval_transaction
7. Add case to create_submission_with_items
8. Create version trigger for main table
9. Update this documentation
10. Run full test suite

When modifying schemas:

1. Check if field exists in ALL three tables (main, submission, version)
2. Update ALL three tables in migration
3. Update ALL functions that reference the field
4. Update this documentation
5. Test create, update, and rollback flows

---

Related Documentation

• Submission Pipeline Overview
• Versioning System
• Moderation Workflow
• Migration Guide