Spaces:
Running
Running
File size: 8,114 Bytes
49287af | 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 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 | -- Migration: Create document upload and RAG tables
-- Date: 2026-01-13
-- This script safely creates tables and columns, checking for existence first
-- Enable UUID extension if not already enabled
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- Create documents table with minimal schema
CREATE TABLE IF NOT EXISTS documents (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID NOT NULL,
filename TEXT NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Add all columns one by one, checking if they exist
DO $$
BEGIN
-- file_type
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'documents' AND column_name = 'file_type') THEN
ALTER TABLE documents ADD COLUMN file_type TEXT;
END IF;
-- file_size
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'documents' AND column_name = 'file_size') THEN
ALTER TABLE documents ADD COLUMN file_size INTEGER;
END IF;
-- storage_path
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'documents' AND column_name = 'storage_path') THEN
ALTER TABLE documents ADD COLUMN storage_path TEXT;
END IF;
-- processing_status
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'documents' AND column_name = 'processing_status') THEN
ALTER TABLE documents ADD COLUMN processing_status TEXT DEFAULT 'pending';
END IF;
-- error_message
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'documents' AND column_name = 'error_message') THEN
ALTER TABLE documents ADD COLUMN error_message TEXT;
END IF;
-- expires_at
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'documents' AND column_name = 'expires_at') THEN
ALTER TABLE documents ADD COLUMN expires_at TIMESTAMP WITH TIME ZONE;
END IF;
-- processed_at
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'documents' AND column_name = 'processed_at') THEN
ALTER TABLE documents ADD COLUMN processed_at TIMESTAMP WITH TIME ZONE;
END IF;
-- updated_at
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'documents' AND column_name = 'updated_at') THEN
ALTER TABLE documents ADD COLUMN updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW();
END IF;
-- feature
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'documents' AND column_name = 'feature') THEN
ALTER TABLE documents ADD COLUMN feature TEXT;
END IF;
-- file_hash
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'documents' AND column_name = 'file_hash') THEN
ALTER TABLE documents ADD COLUMN file_hash TEXT;
END IF;
END $$;
-- Add constraints only if they don't exist
DO $$
BEGIN
-- Add processing_status check constraint
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'documents_processing_status_check') THEN
ALTER TABLE documents ADD CONSTRAINT documents_processing_status_check
CHECK (processing_status IN ('pending', 'processing', 'completed', 'failed'));
END IF;
-- Add feature check constraint
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'documents_feature_check') THEN
ALTER TABLE documents ADD CONSTRAINT documents_feature_check
CHECK (feature IN ('chat', 'mcq', 'flashcard', 'explain', 'highyield'));
END IF;
END $$;
-- Create document_chunks table for RAG
CREATE TABLE IF NOT EXISTS document_chunks (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
document_id UUID NOT NULL,
chunk_index INTEGER NOT NULL,
content TEXT NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Create indexes for performance (only if they don't exist)
CREATE INDEX IF NOT EXISTS idx_documents_user_id ON documents(user_id);
CREATE INDEX IF NOT EXISTS idx_documents_status ON documents(processing_status);
CREATE INDEX IF NOT EXISTS idx_document_chunks_document_id ON document_chunks(document_id);
-- Create indexes that depend on columns existing
DO $$
BEGIN
-- Index on feature column
IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'documents' AND column_name = 'feature') THEN
CREATE INDEX IF NOT EXISTS idx_documents_feature ON documents(feature);
END IF;
-- Index on expires_at column
IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'documents' AND column_name = 'expires_at') THEN
CREATE INDEX IF NOT EXISTS idx_documents_expires_at ON documents(expires_at);
END IF;
-- Index on file_hash column
IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'documents' AND column_name = 'file_hash') THEN
CREATE INDEX IF NOT EXISTS idx_documents_file_hash ON documents(file_hash);
END IF;
-- Text search index on content
IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'idx_document_chunks_content') THEN
CREATE INDEX idx_document_chunks_content ON document_chunks USING gin(to_tsvector('english', content));
END IF;
END $$;
-- Enable RLS
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE document_chunks ENABLE ROW LEVEL SECURITY;
-- Drop existing policies if they exist (to avoid conflicts)
DROP POLICY IF EXISTS "Users can view own documents" ON documents;
DROP POLICY IF EXISTS "Users can insert own documents" ON documents;
DROP POLICY IF EXISTS "Users can update own documents" ON documents;
DROP POLICY IF EXISTS "Users can delete own documents" ON documents;
DROP POLICY IF EXISTS "Users can view chunks of own documents" ON document_chunks;
DROP POLICY IF EXISTS "System can insert chunks" ON document_chunks;
DROP POLICY IF EXISTS "System can delete chunks" ON document_chunks;
DROP POLICY IF EXISTS "Admins can view all documents" ON documents;
DROP POLICY IF EXISTS "Admins can view all chunks" ON document_chunks;
-- RLS Policies for documents
CREATE POLICY "Users can view own documents"
ON documents FOR SELECT
USING (auth.uid() = user_id);
CREATE POLICY "Users can insert own documents"
ON documents FOR INSERT
WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update own documents"
ON documents FOR UPDATE
USING (auth.uid() = user_id);
CREATE POLICY "Users can delete own documents"
ON documents FOR DELETE
USING (auth.uid() = user_id);
-- RLS Policies for document_chunks
CREATE POLICY "Users can view chunks of own documents"
ON document_chunks FOR SELECT
USING (
EXISTS (
SELECT 1 FROM documents
WHERE documents.id = document_chunks.document_id
AND documents.user_id = auth.uid()
)
);
CREATE POLICY "System can insert chunks"
ON document_chunks FOR INSERT
WITH CHECK (true);
CREATE POLICY "System can delete chunks"
ON document_chunks FOR DELETE
USING (true);
-- Admin policies (only if users table exists)
DO $$
BEGIN
IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'users') THEN
EXECUTE '
CREATE POLICY "Admins can view all documents"
ON documents FOR SELECT
USING (
EXISTS (
SELECT 1 FROM users
WHERE users.id = auth.uid()
AND users.role IN (''super_admin'', ''admin'', ''ops'')
)
);
CREATE POLICY "Admins can view all chunks"
ON document_chunks FOR SELECT
USING (
EXISTS (
SELECT 1 FROM users
WHERE users.id = auth.uid()
AND users.role IN (''super_admin'', ''admin'', ''ops'')
)
);
';
END IF;
END $$;
-- Add comments
COMMENT ON TABLE documents IS 'Stores uploaded document metadata';
COMMENT ON TABLE document_chunks IS 'Stores text chunks from documents for RAG';
|