Spaces:
Sleeping
Sleeping
File size: 3,082 Bytes
2451d08 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 |
-- ==========================================
-- Speaking and Writing Tasks Tables
-- Run this SQL in the Supabase dashboard SQL editor
-- ==========================================
-- Create speaking_tasks table
CREATE TABLE speaking_tasks (
id BIGINT NOT NULL,
task_type TEXT NOT NULL,
task_description TEXT,
rephrased_task TEXT,
vocabulary JSONB,
response TEXT,
response_alternative TEXT,
brainstorm JSONB,
brainstorm_alternative JSONB,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
PRIMARY KEY (id, task_type) -- COMPOSITE PRIMARY KEY - ALLOWS SAME ID WITH DIFFERENT TASK_TYPES
);
-- Create index for task_type filtering (useful for your app)
CREATE INDEX IF NOT EXISTS speaking_tasks_task_type_idx
ON speaking_tasks (task_type);
-- Enable Row Level Security
ALTER TABLE speaking_tasks ENABLE ROW LEVEL SECURITY;
-- RLS policies for speaking_tasks
CREATE POLICY "Allow read access to authenticated users" ON speaking_tasks
FOR SELECT USING (auth.role() = 'authenticated');
CREATE POLICY "Allow insert to service role" ON speaking_tasks
FOR INSERT WITH CHECK (auth.role() = 'service_role');
CREATE POLICY "Allow update/delete to service role" ON speaking_tasks
FOR ALL USING (auth.role() = 'service_role');
-- ==========================================
-- Create writing_tasks table
CREATE TABLE writing_tasks (
id BIGINT NOT NULL,
task_type TEXT NOT NULL,
task_description TEXT,
rephrased_task TEXT,
vocabulary JSONB,
response TEXT,
response_alternative TEXT,
brainstorm JSONB,
brainstorm_alternative JSONB,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
PRIMARY KEY (id, task_type) -- COMPOSITE PRIMARY KEY - ALLOWS SAME ID WITH DIFFERENT TASK_TYPES
);
-- Create index for task_type filtering (useful for your app)
CREATE INDEX IF NOT EXISTS writing_tasks_task_type_idx
ON writing_tasks (task_type);
-- Enable Row Level Security
ALTER TABLE writing_tasks ENABLE ROW LEVEL SECURITY;
-- RLS policies for writing_tasks
CREATE POLICY "Allow read access to authenticated users" ON writing_tasks
FOR SELECT USING (auth.role() = 'authenticated');
CREATE POLICY "Allow insert to service role" ON writing_tasks
FOR INSERT WITH CHECK (auth.role() = 'service_role');
CREATE POLICY "Allow update/delete to service role" ON writing_tasks
FOR ALL USING (auth.role() = 'service_role');
-- ==========================================
-- Verification Queries (run these after creation)
-- ==========================================
-- Check if tables exist
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name IN ('speaking_tasks', 'writing_tasks');
-- Check indexes
SELECT indexname, tablename
FROM pg_indexes
WHERE tablename IN ('speaking_tasks', 'writing_tasks')
AND schemaname = 'public';
-- Check RLS policies
SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual
FROM pg_policies
WHERE tablename IN ('speaking_tasks', 'writing_tasks');
|