thrilltrack-explorer/supabase/migrations/20251020125418_1c81e2c8-a632-49cc-b965-c24aa9a9b9b7.sql

-- Phase 1: Timeline Event Submissions Infrastructure
-- Create timeline_event_submissions table (NO JSONB - fully relational)

CREATE TABLE timeline_event_submissions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  submission_id UUID NOT NULL REFERENCES content_submissions(id) ON DELETE CASCADE,
  
  -- Entity reference
  entity_id UUID NOT NULL,
  entity_type TEXT NOT NULL CHECK (entity_type IN ('park', 'ride', 'company', 'ride_model')),
  
  -- Event core data
  event_type TEXT NOT NULL CHECK (event_type IN (
    'opening', 'closing', 'name_change', 'relocation', 'renovation',
    'ownership_change', 'operator_change', 'status_change', 'milestone', 'other'
  )),
  event_date DATE NOT NULL,
  event_date_precision TEXT NOT NULL CHECK (event_date_precision IN ('day', 'month', 'year')),
  title TEXT NOT NULL CHECK (char_length(title) >= 1 AND char_length(title) <= 200),
  description TEXT CHECK (description IS NULL OR char_length(description) <= 2000),
  
  -- Relational fields for specific event types (NO JSONB!)
  from_value TEXT,
  to_value TEXT,
  from_entity_id UUID,
  to_entity_id UUID,
  from_location_id UUID REFERENCES locations(id),
  to_location_id UUID REFERENCES locations(id),
  
  -- Display settings
  display_order INTEGER DEFAULT 0,
  is_public BOOLEAN DEFAULT TRUE,
  
  -- Timestamps
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX idx_timeline_event_submissions_submission ON timeline_event_submissions(submission_id);
CREATE INDEX idx_timeline_event_submissions_entity ON timeline_event_submissions(entity_type, entity_id);
CREATE INDEX idx_timeline_event_submissions_date ON timeline_event_submissions(event_date);

-- Enable RLS
ALTER TABLE timeline_event_submissions ENABLE ROW LEVEL SECURITY;

-- RLS Policy: Users can insert their own timeline submissions
CREATE POLICY "Users can insert own timeline submissions"
  ON timeline_event_submissions FOR INSERT
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM content_submissions cs
      WHERE cs.id = timeline_event_submissions.submission_id 
        AND cs.user_id = auth.uid()
    )
  );

-- RLS Policy: Moderators can view all timeline submissions (with MFA)
CREATE POLICY "Moderators can view all timeline submissions"
  ON timeline_event_submissions FOR SELECT
  USING (is_moderator(auth.uid()) AND has_aal2());

-- RLS Policy: Users can view their own timeline submissions
CREATE POLICY "Users can view own timeline submissions"
  ON timeline_event_submissions FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM content_submissions cs
      WHERE cs.id = timeline_event_submissions.submission_id 
        AND cs.user_id = auth.uid()
    )
  );

-- RLS Policy: Moderators can update timeline submissions (for review)
CREATE POLICY "Moderators can update timeline submissions"
  ON timeline_event_submissions FOR UPDATE
  USING (is_moderator(auth.uid()) AND has_aal2())
  WITH CHECK (is_moderator(auth.uid()) AND has_aal2());

-- RLS Policy: Moderators can delete timeline submissions
CREATE POLICY "Moderators can delete timeline submissions"
  ON timeline_event_submissions FOR DELETE
  USING (is_moderator(auth.uid()) AND has_aal2());

-- Update trigger for timestamps
CREATE TRIGGER update_timeline_event_submissions_updated_at
  BEFORE UPDATE ON timeline_event_submissions
  FOR EACH ROW
  EXECUTE FUNCTION update_content_submissions_updated_at();

-- =====================================================
-- Fix entity_timeline_events RLS (DENY direct writes)
-- =====================================================

-- Drop existing problematic policies if they exist
DROP POLICY IF EXISTS "Users can submit timeline events" ON entity_timeline_events;
DROP POLICY IF EXISTS "Users can delete own pending timeline events" ON entity_timeline_events;
DROP POLICY IF EXISTS "Users can update own pending timeline events" ON entity_timeline_events;
DROP POLICY IF EXISTS "Users can view own pending timeline events" ON entity_timeline_events;

-- CRITICAL: Deny direct inserts from users (only service role via edge function)
CREATE POLICY "Deny direct inserts to timeline events"
  ON entity_timeline_events FOR INSERT
  WITH CHECK (FALSE);

-- CRITICAL: Deny direct updates from users
CREATE POLICY "Deny direct updates to timeline events"
  ON entity_timeline_events FOR UPDATE
  USING (FALSE);

-- Allow public to view approved events
DROP POLICY IF EXISTS "Public can view approved timeline events" ON entity_timeline_events;
CREATE POLICY "Public can view approved timeline events"
  ON entity_timeline_events FOR SELECT
  USING (is_public = TRUE AND approved_by IS NOT NULL);

-- Moderators can view all events
DROP POLICY IF EXISTS "Moderators can view all timeline events" ON entity_timeline_events;
CREATE POLICY "Moderators can view all timeline events"
  ON entity_timeline_events FOR SELECT
  USING (is_moderator(auth.uid()) AND has_aal2());

-- Only moderators can delete (for cleanup/corrections)
DROP POLICY IF EXISTS "Moderators can delete timeline events" ON entity_timeline_events;
CREATE POLICY "Moderators can delete timeline events"
  ON entity_timeline_events FOR DELETE
  USING (is_moderator(auth.uid()) AND has_aal2());

-- Comment for clarity
COMMENT ON TABLE timeline_event_submissions IS 'Timeline event submissions go through moderation queue before being approved into entity_timeline_events';
COMMENT ON POLICY "Deny direct inserts to timeline events" ON entity_timeline_events IS 'Only edge functions (service role) can insert approved timeline events';
COMMENT ON POLICY "Deny direct updates to timeline events" ON entity_timeline_events IS 'Timeline events are immutable after approval - delete and recreate if needed';