mirror of
https://github.com/pacnpal/thrilltrack-explorer.git
synced 2025-12-20 04:51:11 -05:00
345 lines
13 KiB
SQL
345 lines
13 KiB
SQL
-- ============================================================================
|
|
-- COMPLETE JSONB ELIMINATION MIGRATION (FIXED)
|
|
-- Converts all 16 JSONB violations to proper relational tables
|
|
-- ============================================================================
|
|
|
|
-- ============================================================================
|
|
-- GROUP 1: SUBMISSION SYSTEM (CRITICAL)
|
|
-- ============================================================================
|
|
|
|
-- 1.1: content_submissions.content → submission_metadata
|
|
CREATE TABLE IF NOT EXISTS submission_metadata (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
submission_id UUID NOT NULL REFERENCES content_submissions(id) ON DELETE CASCADE,
|
|
metadata_key TEXT NOT NULL,
|
|
metadata_value TEXT NOT NULL,
|
|
value_type TEXT CHECK (value_type IN ('string', 'number', 'boolean', 'date', 'url', 'json')),
|
|
display_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(submission_id, metadata_key)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_submission_metadata_submission ON submission_metadata(submission_id);
|
|
CREATE INDEX IF NOT EXISTS idx_submission_metadata_key ON submission_metadata(metadata_key);
|
|
|
|
ALTER TABLE submission_metadata ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Moderators view all submission metadata"
|
|
ON submission_metadata FOR SELECT
|
|
TO authenticated
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
CREATE POLICY "Users view own submission metadata"
|
|
ON submission_metadata FOR SELECT
|
|
TO authenticated
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM content_submissions cs
|
|
WHERE cs.id = submission_metadata.submission_id
|
|
AND cs.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "System inserts submission metadata"
|
|
ON submission_metadata FOR INSERT
|
|
TO authenticated
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1 FROM content_submissions cs
|
|
WHERE cs.id = submission_metadata.submission_id
|
|
AND (cs.user_id = auth.uid() OR is_moderator(auth.uid()))
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "System updates submission metadata"
|
|
ON submission_metadata FOR UPDATE
|
|
TO authenticated
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
-- ============================================================================
|
|
-- GROUP 2: REVIEW SYSTEM (HIGH PRIORITY)
|
|
-- ============================================================================
|
|
|
|
-- 2.1: reviews.photos → review_photos
|
|
CREATE TABLE IF NOT EXISTS review_photos (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
review_id UUID NOT NULL REFERENCES reviews(id) ON DELETE CASCADE,
|
|
cloudflare_image_id TEXT NOT NULL,
|
|
cloudflare_image_url TEXT NOT NULL,
|
|
caption TEXT,
|
|
order_index INTEGER DEFAULT 0,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_review_photos_review ON review_photos(review_id);
|
|
|
|
ALTER TABLE review_photos ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Public view review photos"
|
|
ON review_photos FOR SELECT
|
|
USING (true);
|
|
|
|
CREATE POLICY "Users manage own review photos"
|
|
ON review_photos FOR ALL
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM reviews r
|
|
WHERE r.id = review_photos.review_id
|
|
AND r.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Moderators manage all review photos"
|
|
ON review_photos FOR ALL
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
-- ============================================================================
|
|
-- GROUP 3: AUDIT & HISTORY TABLES (MEDIUM PRIORITY)
|
|
-- ============================================================================
|
|
|
|
-- 3.1: admin_audit_log.details → admin_audit_details
|
|
CREATE TABLE IF NOT EXISTS admin_audit_details (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
audit_log_id UUID NOT NULL REFERENCES admin_audit_log(id) ON DELETE CASCADE,
|
|
detail_key TEXT NOT NULL,
|
|
detail_value TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(audit_log_id, detail_key)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_admin_audit_details_log ON admin_audit_details(audit_log_id);
|
|
|
|
ALTER TABLE admin_audit_details ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Admins view audit details"
|
|
ON admin_audit_details FOR SELECT
|
|
USING (is_moderator(auth.uid()) AND has_aal2());
|
|
|
|
CREATE POLICY "System inserts audit details"
|
|
ON admin_audit_details FOR INSERT
|
|
WITH CHECK (is_moderator(auth.uid()));
|
|
|
|
-- 3.2: moderation_audit_log.metadata → moderation_audit_metadata
|
|
CREATE TABLE IF NOT EXISTS moderation_audit_metadata (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
audit_log_id UUID NOT NULL REFERENCES moderation_audit_log(id) ON DELETE CASCADE,
|
|
metadata_key TEXT NOT NULL,
|
|
metadata_value TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(audit_log_id, metadata_key)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_moderation_audit_metadata_log ON moderation_audit_metadata(audit_log_id);
|
|
|
|
ALTER TABLE moderation_audit_metadata ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Moderators view moderation audit metadata"
|
|
ON moderation_audit_metadata FOR SELECT
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
CREATE POLICY "System inserts moderation audit metadata"
|
|
ON moderation_audit_metadata FOR INSERT
|
|
WITH CHECK (is_moderator(auth.uid()));
|
|
|
|
-- 3.3: profile_audit_log.changes → profile_change_fields
|
|
CREATE TABLE IF NOT EXISTS profile_change_fields (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
audit_log_id UUID NOT NULL REFERENCES profile_audit_log(id) ON DELETE CASCADE,
|
|
field_name TEXT NOT NULL,
|
|
old_value TEXT,
|
|
new_value TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_profile_change_fields_log ON profile_change_fields(audit_log_id);
|
|
|
|
ALTER TABLE profile_change_fields ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Users view own profile change fields"
|
|
ON profile_change_fields FOR SELECT
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM profile_audit_log pal
|
|
WHERE pal.id = profile_change_fields.audit_log_id
|
|
AND pal.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Moderators view all profile change fields"
|
|
ON profile_change_fields FOR SELECT
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
-- 3.4: item_edit_history.changes → item_change_fields
|
|
CREATE TABLE IF NOT EXISTS item_change_fields (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
edit_history_id UUID NOT NULL REFERENCES item_edit_history(id) ON DELETE CASCADE,
|
|
field_name TEXT NOT NULL,
|
|
old_value TEXT,
|
|
new_value TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_item_change_fields_history ON item_change_fields(edit_history_id);
|
|
|
|
ALTER TABLE item_change_fields ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Moderators view item change fields"
|
|
ON item_change_fields FOR SELECT
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
CREATE POLICY "System inserts item change fields"
|
|
ON item_change_fields FOR INSERT
|
|
WITH CHECK (is_moderator(auth.uid()));
|
|
|
|
-- ============================================================================
|
|
-- GROUP 4: HISTORICAL DATA (MEDIUM PRIORITY)
|
|
-- ============================================================================
|
|
|
|
-- 4.1: historical_parks.final_state_data → Add columns directly
|
|
ALTER TABLE historical_parks
|
|
ADD COLUMN IF NOT EXISTS park_type TEXT,
|
|
ADD COLUMN IF NOT EXISTS status TEXT,
|
|
ADD COLUMN IF NOT EXISTS description TEXT,
|
|
ADD COLUMN IF NOT EXISTS location_id UUID REFERENCES locations(id),
|
|
ADD COLUMN IF NOT EXISTS operator_id UUID REFERENCES companies(id),
|
|
ADD COLUMN IF NOT EXISTS property_owner_id UUID REFERENCES companies(id),
|
|
ADD COLUMN IF NOT EXISTS opening_date DATE,
|
|
ADD COLUMN IF NOT EXISTS opening_date_precision TEXT,
|
|
ADD COLUMN IF NOT EXISTS closing_date DATE,
|
|
ADD COLUMN IF NOT EXISTS closing_date_precision TEXT,
|
|
ADD COLUMN IF NOT EXISTS website_url TEXT,
|
|
ADD COLUMN IF NOT EXISTS phone TEXT,
|
|
ADD COLUMN IF NOT EXISTS email TEXT,
|
|
ADD COLUMN IF NOT EXISTS banner_image_url TEXT,
|
|
ADD COLUMN IF NOT EXISTS banner_image_id TEXT,
|
|
ADD COLUMN IF NOT EXISTS card_image_url TEXT,
|
|
ADD COLUMN IF NOT EXISTS card_image_id TEXT;
|
|
|
|
-- 4.2: historical_rides.final_state_data → Add columns directly
|
|
ALTER TABLE historical_rides
|
|
ADD COLUMN IF NOT EXISTS category TEXT,
|
|
ADD COLUMN IF NOT EXISTS status TEXT,
|
|
ADD COLUMN IF NOT EXISTS description TEXT,
|
|
ADD COLUMN IF NOT EXISTS manufacturer_id UUID REFERENCES companies(id),
|
|
ADD COLUMN IF NOT EXISTS designer_id UUID REFERENCES companies(id),
|
|
ADD COLUMN IF NOT EXISTS ride_model_id UUID REFERENCES ride_models(id),
|
|
ADD COLUMN IF NOT EXISTS opening_date DATE,
|
|
ADD COLUMN IF NOT EXISTS opening_date_precision TEXT,
|
|
ADD COLUMN IF NOT EXISTS closing_date DATE,
|
|
ADD COLUMN IF NOT EXISTS closing_date_precision TEXT,
|
|
ADD COLUMN IF NOT EXISTS max_speed_kmh NUMERIC,
|
|
ADD COLUMN IF NOT EXISTS max_height_meters NUMERIC,
|
|
ADD COLUMN IF NOT EXISTS length_meters NUMERIC,
|
|
ADD COLUMN IF NOT EXISTS drop_height_meters NUMERIC,
|
|
ADD COLUMN IF NOT EXISTS inversions INTEGER,
|
|
ADD COLUMN IF NOT EXISTS max_g_force NUMERIC,
|
|
ADD COLUMN IF NOT EXISTS coaster_type TEXT,
|
|
ADD COLUMN IF NOT EXISTS seating_type TEXT,
|
|
ADD COLUMN IF NOT EXISTS intensity_level TEXT,
|
|
ADD COLUMN IF NOT EXISTS banner_image_url TEXT,
|
|
ADD COLUMN IF NOT EXISTS banner_image_id TEXT,
|
|
ADD COLUMN IF NOT EXISTS card_image_url TEXT,
|
|
ADD COLUMN IF NOT EXISTS card_image_id TEXT;
|
|
|
|
-- ============================================================================
|
|
-- GROUP 5: NOTIFICATION SYSTEM (LOW PRIORITY)
|
|
-- ============================================================================
|
|
|
|
-- 5.1: notification_logs.payload → notification_event_data
|
|
CREATE TABLE IF NOT EXISTS notification_event_data (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
notification_log_id UUID NOT NULL REFERENCES notification_logs(id) ON DELETE CASCADE,
|
|
event_key TEXT NOT NULL,
|
|
event_value TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(notification_log_id, event_key)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_notification_event_data_log ON notification_event_data(notification_log_id);
|
|
|
|
ALTER TABLE notification_event_data ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Admins view notification event data"
|
|
ON notification_event_data FOR SELECT
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
-- ============================================================================
|
|
-- GROUP 6: ERROR TRACKING (LOW PRIORITY)
|
|
-- ============================================================================
|
|
|
|
-- 6.1: request_metadata.breadcrumbs → request_breadcrumbs
|
|
CREATE TABLE IF NOT EXISTS request_breadcrumbs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
request_id UUID NOT NULL REFERENCES request_metadata(request_id) ON DELETE CASCADE,
|
|
timestamp TIMESTAMPTZ NOT NULL,
|
|
category TEXT NOT NULL,
|
|
message TEXT NOT NULL,
|
|
level TEXT CHECK (level IN ('debug', 'info', 'warn', 'error')),
|
|
sequence_order INTEGER NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_request_breadcrumbs_request ON request_breadcrumbs(request_id);
|
|
CREATE INDEX IF NOT EXISTS idx_request_breadcrumbs_timestamp ON request_breadcrumbs(timestamp);
|
|
|
|
ALTER TABLE request_breadcrumbs ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Admins view request breadcrumbs"
|
|
ON request_breadcrumbs FOR SELECT
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
-- 6.2: request_metadata.environment_context → Add specific columns
|
|
ALTER TABLE request_metadata
|
|
ADD COLUMN IF NOT EXISTS request_path TEXT,
|
|
ADD COLUMN IF NOT EXISTS request_method TEXT,
|
|
ADD COLUMN IF NOT EXISTS response_status INTEGER,
|
|
ADD COLUMN IF NOT EXISTS response_time_ms INTEGER,
|
|
ADD COLUMN IF NOT EXISTS session_id TEXT,
|
|
ADD COLUMN IF NOT EXISTS ip_address_hash TEXT;
|
|
|
|
-- ============================================================================
|
|
-- GROUP 7: CONFLICT RESOLUTION (LOW PRIORITY)
|
|
-- ============================================================================
|
|
|
|
-- 7.1: conflict_resolutions.conflict_details → conflict_detail_fields
|
|
CREATE TABLE IF NOT EXISTS conflict_detail_fields (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
conflict_resolution_id UUID NOT NULL REFERENCES conflict_resolutions(id) ON DELETE CASCADE,
|
|
field_name TEXT NOT NULL,
|
|
conflicting_value_1 TEXT,
|
|
conflicting_value_2 TEXT,
|
|
resolved_value TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_conflict_detail_fields_resolution ON conflict_detail_fields(conflict_resolution_id);
|
|
|
|
ALTER TABLE conflict_detail_fields ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Moderators view conflict detail fields"
|
|
ON conflict_detail_fields FOR SELECT
|
|
USING (is_moderator(auth.uid()));
|
|
|
|
-- ============================================================================
|
|
-- GROUP 8: EMAIL THREADS (LOW PRIORITY)
|
|
-- ============================================================================
|
|
|
|
-- 8.1: contact_email_threads.metadata → Add specific columns
|
|
ALTER TABLE contact_email_threads
|
|
ADD COLUMN IF NOT EXISTS email_provider TEXT,
|
|
ADD COLUMN IF NOT EXISTS smtp_message_id TEXT,
|
|
ADD COLUMN IF NOT EXISTS spam_score NUMERIC,
|
|
ADD COLUMN IF NOT EXISTS attachment_count INTEGER DEFAULT 0,
|
|
ADD COLUMN IF NOT EXISTS is_auto_reply BOOLEAN DEFAULT false;
|
|
|
|
-- ============================================================================
|
|
-- TRIGGERS FOR UPDATED_AT TIMESTAMPS
|
|
-- ============================================================================
|
|
|
|
CREATE TRIGGER update_submission_metadata_updated_at BEFORE UPDATE ON submission_metadata
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_review_photos_updated_at BEFORE UPDATE ON review_photos
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); |