Spaces:
Running
Running
File size: 4,308 Bytes
685998b | 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 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 | -- Migration: Create study tools tables
-- Description: Independent session and material storage for study tools
-- Date: 2026-01-03
-- Study tool sessions table
CREATE TABLE IF NOT EXISTS study_tool_sessions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
feature VARCHAR(50) NOT NULL, -- flashcard, mcq, conceptmap, highyield, explain
title TEXT NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Study tool materials table
CREATE TABLE IF NOT EXISTS study_tool_materials (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
session_id UUID NOT NULL REFERENCES study_tool_sessions(id) ON DELETE CASCADE,
feature VARCHAR(50) NOT NULL,
topic TEXT NOT NULL,
content TEXT NOT NULL,
tokens_used INTEGER DEFAULT 0,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_study_tool_sessions_user_id ON study_tool_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_study_tool_sessions_feature ON study_tool_sessions(feature);
CREATE INDEX IF NOT EXISTS idx_study_tool_sessions_created_at ON study_tool_sessions(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_study_tool_materials_session_id ON study_tool_materials(session_id);
CREATE INDEX IF NOT EXISTS idx_study_tool_materials_created_at ON study_tool_materials(created_at DESC);
-- Add RLS (Row Level Security) policies
ALTER TABLE study_tool_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE study_tool_materials ENABLE ROW LEVEL SECURITY;
-- Users can only see their own sessions
CREATE POLICY study_tool_sessions_select_policy ON study_tool_sessions
FOR SELECT
USING (auth.uid() = user_id);
-- Users can only insert their own sessions
CREATE POLICY study_tool_sessions_insert_policy ON study_tool_sessions
FOR INSERT
WITH CHECK (auth.uid() = user_id);
-- Users can only update their own sessions
CREATE POLICY study_tool_sessions_update_policy ON study_tool_sessions
FOR UPDATE
USING (auth.uid() = user_id);
-- Users can only delete their own sessions
CREATE POLICY study_tool_sessions_delete_policy ON study_tool_sessions
FOR DELETE
USING (auth.uid() = user_id);
-- Users can only see materials from their own sessions
CREATE POLICY study_tool_materials_select_policy ON study_tool_materials
FOR SELECT
USING (
EXISTS (
SELECT 1 FROM study_tool_sessions
WHERE study_tool_sessions.id = study_tool_materials.session_id
AND study_tool_sessions.user_id = auth.uid()
)
);
-- Users can only insert materials to their own sessions
CREATE POLICY study_tool_materials_insert_policy ON study_tool_materials
FOR INSERT
WITH CHECK (
EXISTS (
SELECT 1 FROM study_tool_sessions
WHERE study_tool_sessions.id = study_tool_materials.session_id
AND study_tool_sessions.user_id = auth.uid()
)
);
-- Users can only delete materials from their own sessions
CREATE POLICY study_tool_materials_delete_policy ON study_tool_materials
FOR DELETE
USING (
EXISTS (
SELECT 1 FROM study_tool_sessions
WHERE study_tool_sessions.id = study_tool_materials.session_id
AND study_tool_sessions.user_id = auth.uid()
)
);
-- Create function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_study_tool_session_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Create trigger to automatically update updated_at
CREATE TRIGGER update_study_tool_session_updated_at_trigger
BEFORE UPDATE ON study_tool_sessions
FOR EACH ROW
EXECUTE FUNCTION update_study_tool_session_updated_at();
-- Add comments for documentation
COMMENT ON TABLE study_tool_sessions IS 'Independent sessions for study tools (flashcards, MCQs, etc.)';
COMMENT ON TABLE study_tool_materials IS 'Generated study materials linked to sessions';
COMMENT ON COLUMN study_tool_sessions.feature IS 'Type of study tool: flashcard, mcq, conceptmap, highyield, explain';
COMMENT ON COLUMN study_tool_materials.content IS 'Generated content from AI model';
|