mirror of
https://github.com/pacnpal/thrilltrack-explorer.git
synced 2025-12-20 04:51:11 -05:00
25 lines
1.5 KiB
SQL
25 lines
1.5 KiB
SQL
-- ============================================================================
|
|
-- ELIMINATE JSONB FROM submission_items
|
|
-- ============================================================================
|
|
-- Problem: submission_items.item_data stores entire submission payloads as JSONB
|
|
-- Solution: Use existing relational tables (*_submissions) and link via foreign key
|
|
--
|
|
-- CRITICAL PROJECT RULE: NO JSON OR JSONB IN SQL COLUMNS FOR RELATIONAL DATA
|
|
-- ============================================================================
|
|
|
|
-- Step 1: Add foreign key column to link to relational submission tables
|
|
ALTER TABLE submission_items
|
|
ADD COLUMN item_data_id UUID;
|
|
|
|
-- Step 2: Create index for performance
|
|
CREATE INDEX idx_submission_items_item_data_id ON submission_items(item_data_id);
|
|
|
|
-- Step 3: Add comments explaining the architecture
|
|
COMMENT ON COLUMN submission_items.item_data_id IS
|
|
'Foreign key to *_submissions tables based on item_type. park -> park_submissions, ride -> ride_submissions, company types -> company_submissions, ride_model -> ride_model_submissions. Replaces the deprecated item_data JSONB column.';
|
|
|
|
COMMENT ON COLUMN submission_items.item_data IS
|
|
'DEPRECATED: Use item_data_id to reference relational *_submissions tables. This JSONB column violates the NO JSON IN DATABASE rule and will be dropped.';
|
|
|
|
COMMENT ON COLUMN submission_items.original_data IS
|
|
'DEPRECATED: Use item_data_id to reference relational *_submissions tables. This JSONB column violates the NO JSON IN DATABASE rule and will be dropped.'; |