-- ============================================================================ -- Fix Approval Pipeline: Eliminate JSONB, Use Pure Relational Data -- ============================================================================ -- Problem: process_approval_transaction calls non-existent JSONB functions -- (resolve_temp_references, perform_create, perform_update) -- Solution: Direct INSERT/UPDATE from relational submission tables -- ============================================================================ -- Drop all overloaded versions of the function DROP FUNCTION IF EXISTS process_approval_transaction(UUID, UUID[], UUID, UUID); DROP FUNCTION IF EXISTS process_approval_transaction(UUID, UUID[], UUID, UUID, TEXT); DROP FUNCTION IF EXISTS process_approval_transaction(UUID, UUID[], UUID, UUID, TEXT, TEXT); DROP FUNCTION IF EXISTS process_approval_transaction(UUID, UUID[], UUID, UUID, TEXT, TEXT, TEXT); CREATE OR REPLACE FUNCTION process_approval_transaction( p_submission_id UUID, p_item_ids UUID[], p_moderator_id UUID, p_submitter_id UUID, p_request_id TEXT DEFAULT NULL, p_trace_id TEXT DEFAULT NULL, p_parent_span_id TEXT DEFAULT NULL ) RETURNS JSONB LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$ DECLARE v_start_time TIMESTAMPTZ; v_result JSONB; v_item RECORD; v_entity_id UUID; v_approval_results JSONB[] := ARRAY[]::JSONB[]; v_final_status TEXT; v_all_approved BOOLEAN := TRUE; v_some_approved BOOLEAN := FALSE; v_items_processed INTEGER := 0; v_span_id TEXT; -- Variables for temporary reference resolution v_resolved_park_id UUID; v_resolved_manufacturer_id UUID; v_resolved_ride_model_id UUID; v_resolved_operator_id UUID; v_resolved_property_owner_id UUID; BEGIN v_start_time := clock_timestamp(); v_span_id := gen_random_uuid()::text; -- Log span start with trace context IF p_trace_id IS NOT NULL THEN RAISE NOTICE 'SPAN: {"spanId": "%", "traceId": "%", "parentSpanId": "%", "name": "process_approval_transaction_rpc", "kind": "INTERNAL", "startTime": %, "attributes": {"submission.id": "%", "item_count": %}}', v_span_id, p_trace_id, p_parent_span_id, EXTRACT(EPOCH FROM v_start_time) * 1000, p_submission_id, array_length(p_item_ids, 1); END IF; RAISE NOTICE '[%] Starting atomic approval transaction for submission %', COALESCE(p_request_id, 'NO_REQUEST_ID'), p_submission_id; -- ======================================================================== -- STEP 1: Set session variables (transaction-scoped with is_local=true) -- ======================================================================== PERFORM set_config('app.current_user_id', p_submitter_id::text, true); PERFORM set_config('app.submission_id', p_submission_id::text, true); PERFORM set_config('app.moderator_id', p_moderator_id::text, true); -- ======================================================================== -- STEP 2: Validate submission ownership and lock status -- ======================================================================== IF NOT EXISTS ( SELECT 1 FROM content_submissions WHERE id = p_submission_id AND (assigned_to = p_moderator_id OR assigned_to IS NULL) AND status IN ('pending', 'partially_approved') ) THEN RAISE EXCEPTION 'Submission not found, locked by another moderator, or already processed' USING ERRCODE = '42501'; END IF; -- ======================================================================== -- STEP 3: Process each item sequentially within this transaction -- ======================================================================== FOR v_item IN SELECT si.*, ps.name as park_name, ps.slug as park_slug, ps.description as park_description, ps.park_type, ps.status as park_status, ps.location_id, ps.operator_id, ps.property_owner_id, ps.opening_date as park_opening_date, ps.closing_date as park_closing_date, ps.opening_date_precision as park_opening_date_precision, ps.closing_date_precision as park_closing_date_precision, ps.website_url as park_website_url, ps.phone as park_phone, ps.email as park_email, ps.banner_image_url as park_banner_image_url, ps.banner_image_id as park_banner_image_id, ps.card_image_url as park_card_image_url, ps.card_image_id as park_card_image_id, rs.name as ride_name, rs.slug as ride_slug, rs.park_id as ride_park_id, rs.category as ride_category, rs.status as ride_status, rs.manufacturer_id, rs.ride_model_id, rs.opening_date as ride_opening_date, rs.closing_date as ride_closing_date, rs.opening_date_precision as ride_opening_date_precision, rs.closing_date_precision as ride_closing_date_precision, rs.description as ride_description, rs.banner_image_url as ride_banner_image_url, rs.banner_image_id as ride_banner_image_id, rs.card_image_url as ride_card_image_url, rs.card_image_id as ride_card_image_id, cs.name as company_name, cs.slug as company_slug, cs.description as company_description, cs.company_type, cs.website_url as company_website_url, cs.founded_year, cs.founded_date, cs.founded_date_precision, cs.headquarters_location, cs.logo_url, cs.person_type, cs.banner_image_url as company_banner_image_url, cs.banner_image_id as company_banner_image_id, cs.card_image_url as company_card_image_url, cs.card_image_id as company_card_image_id, rms.name as ride_model_name, rms.slug as ride_model_slug, rms.manufacturer_id as ride_model_manufacturer_id, rms.category as ride_model_category, rms.description as ride_model_description, rms.banner_image_url as ride_model_banner_image_url, rms.banner_image_id as ride_model_banner_image_id, rms.card_image_url as ride_model_card_image_url, rms.card_image_id as ride_model_card_image_id, phs.entity_id as photo_entity_id, phs.entity_type as photo_entity_type, phs.title as photo_title FROM submission_items si LEFT JOIN park_submissions ps ON si.park_submission_id = ps.id LEFT JOIN ride_submissions rs ON si.ride_submission_id = rs.id LEFT JOIN company_submissions cs ON si.company_submission_id = cs.id LEFT JOIN ride_model_submissions rms ON si.ride_model_submission_id = rms.id LEFT JOIN photo_submissions phs ON si.photo_submission_id = phs.id WHERE si.id = ANY(p_item_ids) ORDER BY si.order_index, si.created_at LOOP BEGIN v_items_processed := v_items_processed + 1; v_entity_id := NULL; -- Reset temporary reference variables v_resolved_park_id := NULL; v_resolved_manufacturer_id := NULL; v_resolved_ride_model_id := NULL; v_resolved_operator_id := NULL; v_resolved_property_owner_id := NULL; -- Log item processing span event IF p_trace_id IS NOT NULL THEN RAISE NOTICE 'SPAN_EVENT: {"traceId": "%", "parentSpanId": "%", "name": "process_item", "timestamp": %, "attributes": {"item.id": "%", "item.type": "%", "item.action": "%"}}', p_trace_id, v_span_id, EXTRACT(EPOCH FROM clock_timestamp()) * 1000, v_item.id, v_item.item_type, v_item.action_type; END IF; -- ==================================================================== -- RELATIONAL LOGIC: Direct INSERT/UPDATE without JSONB -- ==================================================================== IF v_item.action_type = 'create' THEN -- ---------------------------------------------------------------- -- CREATE ACTIONS: Direct INSERT into entity tables -- ---------------------------------------------------------------- IF v_item.item_type = 'park' THEN -- Resolve temporary operator/property_owner references IF v_item.operator_id IS NULL THEN SELECT approved_entity_id INTO v_resolved_operator_id FROM submission_items WHERE submission_id = p_submission_id AND item_type IN ('operator', 'company') AND approved_entity_id IS NOT NULL LIMIT 1; END IF; IF v_item.property_owner_id IS NULL THEN SELECT approved_entity_id INTO v_resolved_property_owner_id FROM submission_items WHERE submission_id = p_submission_id AND item_type IN ('property_owner', 'company') AND approved_entity_id IS NOT NULL LIMIT 1; END IF; INSERT INTO parks ( name, slug, description, park_type, status, location_id, operator_id, property_owner_id, opening_date, closing_date, opening_date_precision, closing_date_precision, website_url, phone, email, banner_image_url, banner_image_id, card_image_url, card_image_id ) VALUES ( v_item.park_name, v_item.park_slug, v_item.park_description, v_item.park_type, v_item.park_status, v_item.location_id, COALESCE(v_item.operator_id, v_resolved_operator_id), COALESCE(v_item.property_owner_id, v_resolved_property_owner_id), v_item.park_opening_date, v_item.park_closing_date, v_item.park_opening_date_precision, v_item.park_closing_date_precision, v_item.park_website_url, v_item.park_phone, v_item.park_email, v_item.park_banner_image_url, v_item.park_banner_image_id, v_item.park_card_image_url, v_item.park_card_image_id ) RETURNING id INTO v_entity_id; ELSIF v_item.item_type = 'ride' THEN -- Resolve temporary park/manufacturer/ride_model references IF v_item.ride_park_id IS NULL THEN SELECT approved_entity_id INTO v_resolved_park_id FROM submission_items WHERE submission_id = p_submission_id AND item_type = 'park' AND approved_entity_id IS NOT NULL LIMIT 1; END IF; IF v_item.manufacturer_id IS NULL THEN SELECT approved_entity_id INTO v_resolved_manufacturer_id FROM submission_items WHERE submission_id = p_submission_id AND item_type IN ('manufacturer', 'company') AND approved_entity_id IS NOT NULL LIMIT 1; END IF; IF v_item.ride_model_id IS NULL THEN SELECT approved_entity_id INTO v_resolved_ride_model_id FROM submission_items WHERE submission_id = p_submission_id AND item_type = 'ride_model' AND approved_entity_id IS NOT NULL LIMIT 1; END IF; INSERT INTO rides ( name, slug, park_id, category, status, manufacturer_id, ride_model_id, opening_date, closing_date, opening_date_precision, closing_date_precision, description, banner_image_url, banner_image_id, card_image_url, card_image_id ) VALUES ( v_item.ride_name, v_item.ride_slug, COALESCE(v_item.ride_park_id, v_resolved_park_id), v_item.ride_category, v_item.ride_status, COALESCE(v_item.manufacturer_id, v_resolved_manufacturer_id), COALESCE(v_item.ride_model_id, v_resolved_ride_model_id), v_item.ride_opening_date, v_item.ride_closing_date, v_item.ride_opening_date_precision, v_item.ride_closing_date_precision, v_item.ride_description, v_item.ride_banner_image_url, v_item.ride_banner_image_id, v_item.ride_card_image_url, v_item.ride_card_image_id ) RETURNING id INTO v_entity_id; -- Copy related technical specifications IF v_entity_id IS NOT NULL AND v_item.ride_submission_id IS NOT NULL THEN INSERT INTO ride_technical_specifications ( ride_id, specification_key, specification_value, unit, display_order ) SELECT v_entity_id, specification_key, specification_value, unit, display_order FROM ride_technical_specifications WHERE ride_id = v_item.ride_submission_id; -- Copy coaster stats INSERT INTO ride_coaster_stats ( ride_id, stat_key, stat_value, unit, display_order ) SELECT v_entity_id, stat_key, stat_value, unit, display_order FROM ride_coaster_stats WHERE ride_id = v_item.ride_submission_id; END IF; ELSIF v_item.item_type IN ('company', 'manufacturer', 'operator', 'property_owner', 'designer') THEN INSERT INTO companies ( name, slug, description, company_type, person_type, website_url, founded_year, founded_date, founded_date_precision, headquarters_location, logo_url, banner_image_url, banner_image_id, card_image_url, card_image_id ) VALUES ( v_item.company_name, v_item.company_slug, v_item.company_description, v_item.company_type, v_item.person_type, v_item.company_website_url, v_item.founded_year, v_item.founded_date, v_item.founded_date_precision, v_item.headquarters_location, v_item.logo_url, v_item.company_banner_image_url, v_item.company_banner_image_id, v_item.company_card_image_url, v_item.company_card_image_id ) RETURNING id INTO v_entity_id; ELSIF v_item.item_type = 'ride_model' THEN -- Resolve temporary manufacturer reference IF v_item.ride_model_manufacturer_id IS NULL THEN SELECT approved_entity_id INTO v_resolved_manufacturer_id FROM submission_items WHERE submission_id = p_submission_id AND item_type IN ('manufacturer', 'company') AND approved_entity_id IS NOT NULL LIMIT 1; END IF; INSERT INTO ride_models ( name, slug, manufacturer_id, category, description, banner_image_url, banner_image_id, card_image_url, card_image_id ) VALUES ( v_item.ride_model_name, v_item.ride_model_slug, COALESCE(v_item.ride_model_manufacturer_id, v_resolved_manufacturer_id), v_item.ride_model_category, v_item.ride_model_description, v_item.ride_model_banner_image_url, v_item.ride_model_banner_image_id, v_item.ride_model_card_image_url, v_item.ride_model_card_image_id ) RETURNING id INTO v_entity_id; ELSIF v_item.item_type = 'photo' THEN -- Handle photo uploads INSERT INTO entity_photos ( entity_id, entity_type, title, photo_submission_id ) VALUES ( v_item.photo_entity_id, v_item.photo_entity_type, v_item.photo_title, v_item.photo_submission_id ) RETURNING id INTO v_entity_id; ELSE RAISE EXCEPTION 'Unknown item type for create: %', v_item.item_type; END IF; ELSIF v_item.action_type = 'update' THEN -- ---------------------------------------------------------------- -- UPDATE ACTIONS: Direct UPDATE to entity tables -- ---------------------------------------------------------------- IF v_item.entity_id IS NULL THEN RAISE EXCEPTION 'Update action requires entity_id'; END IF; IF v_item.item_type = 'park' THEN UPDATE parks SET name = v_item.park_name, slug = v_item.park_slug, description = v_item.park_description, park_type = v_item.park_type, status = v_item.park_status, location_id = v_item.location_id, operator_id = v_item.operator_id, property_owner_id = v_item.property_owner_id, opening_date = v_item.park_opening_date, closing_date = v_item.park_closing_date, opening_date_precision = v_item.park_opening_date_precision, closing_date_precision = v_item.park_closing_date_precision, website_url = v_item.park_website_url, phone = v_item.park_phone, email = v_item.park_email, banner_image_url = v_item.park_banner_image_url, banner_image_id = v_item.park_banner_image_id, card_image_url = v_item.park_card_image_url, card_image_id = v_item.park_card_image_id, updated_at = now() WHERE id = v_item.entity_id; ELSIF v_item.item_type = 'ride' THEN UPDATE rides SET name = v_item.ride_name, slug = v_item.ride_slug, park_id = v_item.ride_park_id, category = v_item.ride_category, status = v_item.ride_status, manufacturer_id = v_item.manufacturer_id, ride_model_id = v_item.ride_model_id, opening_date = v_item.ride_opening_date, closing_date = v_item.ride_closing_date, opening_date_precision = v_item.ride_opening_date_precision, closing_date_precision = v_item.ride_closing_date_precision, description = v_item.ride_description, banner_image_url = v_item.ride_banner_image_url, banner_image_id = v_item.ride_banner_image_id, card_image_url = v_item.ride_card_image_url, card_image_id = v_item.ride_card_image_id, updated_at = now() WHERE id = v_item.entity_id; -- Update related technical specifications IF v_item.ride_submission_id IS NOT NULL THEN -- Delete old specs and copy new ones DELETE FROM ride_technical_specifications WHERE ride_id = v_item.entity_id; INSERT INTO ride_technical_specifications ( ride_id, specification_key, specification_value, unit, display_order ) SELECT v_item.entity_id, specification_key, specification_value, unit, display_order FROM ride_technical_specifications WHERE ride_id = v_item.ride_submission_id; -- Update coaster stats DELETE FROM ride_coaster_stats WHERE ride_id = v_item.entity_id; INSERT INTO ride_coaster_stats ( ride_id, stat_key, stat_value, unit, display_order ) SELECT v_item.entity_id, stat_key, stat_value, unit, display_order FROM ride_coaster_stats WHERE ride_id = v_item.ride_submission_id; END IF; ELSIF v_item.item_type IN ('company', 'manufacturer', 'operator', 'property_owner', 'designer') THEN UPDATE companies SET name = v_item.company_name, slug = v_item.company_slug, description = v_item.company_description, company_type = v_item.company_type, person_type = v_item.person_type, website_url = v_item.company_website_url, founded_year = v_item.founded_year, founded_date = v_item.founded_date, founded_date_precision = v_item.founded_date_precision, headquarters_location = v_item.headquarters_location, logo_url = v_item.logo_url, banner_image_url = v_item.company_banner_image_url, banner_image_id = v_item.company_banner_image_id, card_image_url = v_item.company_card_image_url, card_image_id = v_item.company_card_image_id, updated_at = now() WHERE id = v_item.entity_id; ELSIF v_item.item_type = 'ride_model' THEN UPDATE ride_models SET name = v_item.ride_model_name, slug = v_item.ride_model_slug, manufacturer_id = v_item.ride_model_manufacturer_id, category = v_item.ride_model_category, description = v_item.ride_model_description, banner_image_url = v_item.ride_model_banner_image_url, banner_image_id = v_item.ride_model_banner_image_id, card_image_url = v_item.ride_model_card_image_url, card_image_id = v_item.ride_model_card_image_id, updated_at = now() WHERE id = v_item.entity_id; ELSE RAISE EXCEPTION 'Unknown item type for update: %', v_item.item_type; END IF; v_entity_id := v_item.entity_id; ELSE RAISE EXCEPTION 'Unknown action type: %', v_item.action_type; END IF; -- Update submission_item with approved entity UPDATE submission_items SET approved_entity_id = v_entity_id, approved_at = now(), status = 'approved' WHERE id = v_item.id; -- Track approval results v_approval_results := array_append(v_approval_results, jsonb_build_object( 'item_id', v_item.id, 'status', 'approved', 'entity_id', v_entity_id )); v_some_approved := TRUE; EXCEPTION WHEN OTHERS THEN -- Log the error RAISE WARNING 'Failed to process item %: % - %', v_item.id, SQLERRM, SQLSTATE; -- Track failure v_approval_results := array_append(v_approval_results, jsonb_build_object( 'item_id', v_item.id, 'status', 'failed', 'error', SQLERRM )); v_all_approved := FALSE; -- Re-raise to rollback transaction RAISE; END; END LOOP; -- ======================================================================== -- STEP 4: Update submission status -- ======================================================================== IF v_all_approved THEN v_final_status := 'approved'; ELSIF v_some_approved THEN v_final_status := 'partially_approved'; ELSE v_final_status := 'rejected'; END IF; UPDATE content_submissions SET status = v_final_status, resolved_at = CASE WHEN v_all_approved THEN now() ELSE NULL END, reviewer_id = p_moderator_id, reviewed_at = now() WHERE id = p_submission_id; -- Log span end IF p_trace_id IS NOT NULL THEN RAISE NOTICE 'SPAN: {"spanId": "%", "traceId": "%", "name": "process_approval_transaction_rpc", "kind": "INTERNAL", "endTime": %, "attributes": {"items_processed": %, "final_status": "%"}}', v_span_id, p_trace_id, EXTRACT(EPOCH FROM clock_timestamp()) * 1000, v_items_processed, v_final_status; END IF; -- Return result RETURN jsonb_build_object( 'success', v_all_approved, 'status', v_final_status, 'items_processed', v_items_processed, 'results', v_approval_results, 'duration_ms', EXTRACT(EPOCH FROM (clock_timestamp() - v_start_time)) * 1000 ); END; $$; GRANT EXECUTE ON FUNCTION process_approval_transaction TO authenticated; COMMENT ON FUNCTION process_approval_transaction IS 'Processes selective approval of submission items using pure relational data (NO JSONB). Handles temporary reference resolution and copies related data from submission tables to entity tables.';