mirror of
https://github.com/pacnpal/thrilltrack-explorer.git
synced 2025-12-20 04:51:11 -05:00
27 lines
1.2 KiB
SQL
27 lines
1.2 KiB
SQL
-- Create moderation queue in pgmq
|
|
SELECT pgmq.create('moderation_queue');
|
|
|
|
-- Add queue management columns to content_submissions
|
|
ALTER TABLE content_submissions
|
|
ADD COLUMN IF NOT EXISTS priority INTEGER NOT NULL DEFAULT 3,
|
|
ADD COLUMN IF NOT EXISTS assigned_to UUID REFERENCES auth.users(id),
|
|
ADD COLUMN IF NOT EXISTS assigned_at TIMESTAMP WITH TIME ZONE,
|
|
ADD COLUMN IF NOT EXISTS locked_until TIMESTAMP WITH TIME ZONE,
|
|
ADD COLUMN IF NOT EXISTS submitted_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
|
|
ADD COLUMN IF NOT EXISTS first_reviewed_at TIMESTAMP WITH TIME ZONE,
|
|
ADD COLUMN IF NOT EXISTS resolved_at TIMESTAMP WITH TIME ZONE,
|
|
ADD COLUMN IF NOT EXISTS review_count INTEGER DEFAULT 0;
|
|
|
|
-- Create indexes for queue operations
|
|
CREATE INDEX IF NOT EXISTS idx_submissions_queue_status
|
|
ON content_submissions(status, priority DESC, submitted_at ASC)
|
|
WHERE status IN ('pending', 'partially_approved');
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_submissions_assigned
|
|
ON content_submissions(assigned_to, locked_until)
|
|
WHERE assigned_to IS NOT NULL;
|
|
|
|
-- Simple index on locked_until for expired lock cleanup
|
|
CREATE INDEX IF NOT EXISTS idx_submissions_locked_until
|
|
ON content_submissions(locked_until)
|
|
WHERE locked_until IS NOT NULL; |