mirror of
https://github.com/pacnpal/thrilltrack-explorer.git
synced 2025-12-20 04:31:13 -05:00
85 lines
3.2 KiB
SQL
85 lines
3.2 KiB
SQL
-- =====================================================
|
|
-- Temporary Reference Storage for Composite Submissions
|
|
-- =====================================================
|
|
-- Stores _temp_*_ref fields extracted from submission items
|
|
-- These are used during approval to resolve dependencies
|
|
|
|
CREATE TABLE IF NOT EXISTS public.submission_item_temp_refs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
submission_item_id UUID NOT NULL REFERENCES public.submission_items(id) ON DELETE CASCADE,
|
|
ref_type TEXT NOT NULL CHECK (ref_type IN (
|
|
'operator', 'property_owner', 'manufacturer', 'designer',
|
|
'park', 'ride', 'ride_model', 'location'
|
|
)),
|
|
ref_order_index INTEGER NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Indexes for performance
|
|
CREATE INDEX idx_submission_item_temp_refs_item_id
|
|
ON public.submission_item_temp_refs(submission_item_id);
|
|
|
|
CREATE INDEX idx_submission_item_temp_refs_type
|
|
ON public.submission_item_temp_refs(ref_type);
|
|
|
|
-- Composite index for fast lookups during approval
|
|
CREATE INDEX idx_submission_item_temp_refs_item_type
|
|
ON public.submission_item_temp_refs(submission_item_id, ref_type);
|
|
|
|
-- Enable RLS
|
|
ALTER TABLE public.submission_item_temp_refs ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- RLS Policy: Moderators can view all temp refs
|
|
CREATE POLICY "Moderators can view all temp refs"
|
|
ON public.submission_item_temp_refs
|
|
FOR SELECT
|
|
USING (
|
|
is_moderator(auth.uid()) AND
|
|
((NOT has_mfa_enabled(auth.uid())) OR has_aal2())
|
|
);
|
|
|
|
-- RLS Policy: Users can view their own temp refs via submission ownership
|
|
CREATE POLICY "Users can view their own temp refs"
|
|
ON public.submission_item_temp_refs
|
|
FOR SELECT
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM public.submission_items si
|
|
JOIN public.content_submissions cs ON cs.id = si.submission_id
|
|
WHERE si.id = submission_item_temp_refs.submission_item_id
|
|
AND cs.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
-- RLS Policy: System can insert temp refs during submission creation
|
|
CREATE POLICY "System can insert temp refs"
|
|
ON public.submission_item_temp_refs
|
|
FOR INSERT
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM public.submission_items si
|
|
JOIN public.content_submissions cs ON cs.id = si.submission_id
|
|
WHERE si.id = submission_item_temp_refs.submission_item_id
|
|
AND cs.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
-- RLS Policy: Moderators can delete temp refs (manual cleanup if needed)
|
|
CREATE POLICY "Moderators can delete temp refs"
|
|
ON public.submission_item_temp_refs
|
|
FOR DELETE
|
|
USING (
|
|
is_moderator(auth.uid()) AND
|
|
((NOT has_mfa_enabled(auth.uid())) OR has_aal2())
|
|
);
|
|
|
|
COMMENT ON TABLE public.submission_item_temp_refs IS
|
|
'Stores temporary reference fields (_temp_operator_ref, etc.) from submission items for composite submissions. Cleaned up automatically when items are approved.';
|
|
|
|
COMMENT ON COLUMN public.submission_item_temp_refs.ref_type IS
|
|
'The entity type being referenced (e.g., operator, park, manufacturer). Extracted from _temp_operator_ref -> operator.';
|
|
|
|
COMMENT ON COLUMN public.submission_item_temp_refs.ref_order_index IS
|
|
'The array index of the referenced item in the submission order. Matches the order_index of the target submission_item.'; |