208 lines
7.2 KiB
PL/PgSQL
208 lines
7.2 KiB
PL/PgSQL
-- ============================================
|
||
-- PastPaper Master — 初始数据库 Schema
|
||
-- Version: 001
|
||
-- Date: 2025-03-11
|
||
-- ============================================
|
||
|
||
-- 启用必要的扩展
|
||
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
||
|
||
-- ============================================
|
||
-- Table 1: papers — 上传的试卷
|
||
-- ============================================
|
||
CREATE TABLE papers (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
||
|
||
-- 元信息（用户上传时填写）
|
||
course_code TEXT NOT NULL, -- "COMP2011"
|
||
year INTEGER NOT NULL, -- 2024
|
||
term TEXT NOT NULL CHECK (term IN ('fall', 'spring', 'summer')),
|
||
exam_type TEXT NOT NULL CHECK (exam_type IN ('midterm', 'final', 'quiz')),
|
||
|
||
-- 文件 (Supabase Storage)
|
||
paper_file_url TEXT NOT NULL, -- 试卷 PDF
|
||
answer_file_url TEXT, -- 答案 PDF（可选）
|
||
|
||
-- 处理状态
|
||
status TEXT NOT NULL DEFAULT 'uploaded'
|
||
CHECK (status IN ('uploaded', 'processing', 'ready', 'error')),
|
||
error_message TEXT, -- 处理失败时的错误信息
|
||
|
||
-- 提取的原始文本（缓存）
|
||
paper_extracted_text TEXT,
|
||
answer_extracted_text TEXT,
|
||
|
||
-- 整卷概览（AI 生成）
|
||
total_score INTEGER,
|
||
question_count INTEGER,
|
||
topics_summary JSONB, -- {"Linked List": 40, "Recursion": 30}
|
||
difficulty_level TEXT CHECK (difficulty_level IN ('easy', 'medium', 'hard')),
|
||
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
||
);
|
||
|
||
-- ============================================
|
||
-- Table 2: paper_questions — 逐题数据
|
||
-- ============================================
|
||
CREATE TABLE paper_questions (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
paper_id UUID NOT NULL REFERENCES papers(id) ON DELETE CASCADE,
|
||
|
||
-- 题目标识
|
||
question_number TEXT NOT NULL, -- "1", "1a", "2b"
|
||
parent_question TEXT, -- 子题的父题号: "1a" → "1"
|
||
display_order INTEGER NOT NULL, -- 显示顺序
|
||
|
||
-- 题目内容
|
||
question_type TEXT NOT NULL
|
||
CHECK (question_type IN ('mc', 'fill_blank', 'long_question')),
|
||
question_text TEXT NOT NULL, -- 题目原文
|
||
score INTEGER, -- 分值
|
||
page_number INTEGER, -- PDF 页码（左右联动）
|
||
|
||
-- 选择题专用
|
||
options JSONB, -- [{"label":"A","text":"..."},...]
|
||
correct_option TEXT, -- "B"
|
||
|
||
-- 填空题专用
|
||
correct_answer TEXT, -- 正确答案
|
||
accept_variants TEXT[], -- 等价表达 ["O(nlogn)","O(n log n)"]
|
||
|
||
-- 答案 PDF 提取的原始答案（所有题型）
|
||
raw_answer_text TEXT,
|
||
|
||
-- 知识点标签
|
||
topics TEXT[], -- ["Linked List","Pointer"]
|
||
difficulty TEXT CHECK (difficulty IN ('easy', 'medium', 'hard')),
|
||
|
||
-- AI 三件套（HTML + KaTeX）
|
||
knowledge_reminder TEXT, -- 知识点 Reminder
|
||
ai_hint TEXT, -- AI Hint
|
||
solution TEXT, -- Solution（逐步 derivation）
|
||
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
||
);
|
||
|
||
-- ============================================
|
||
-- Table 3: user_attempts — 用户答题记录
|
||
-- Phase 4 实现，先建好表结构
|
||
-- ============================================
|
||
CREATE TABLE user_attempts (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
||
question_id UUID NOT NULL REFERENCES paper_questions(id) ON DELETE CASCADE,
|
||
|
||
-- 用户的作答
|
||
attempt_type TEXT NOT NULL
|
||
CHECK (attempt_type IN ('select', 'input', 'photo')),
|
||
user_answer TEXT, -- 选项 / 输入的答案
|
||
photo_url TEXT, -- 上传的照片
|
||
photo_ocr_text TEXT, -- OCR 识别结果
|
||
|
||
-- AI 判定
|
||
is_correct BOOLEAN,
|
||
feedback TEXT, -- HTML — 逐步错误分析
|
||
error_at_step INTEGER, -- 第几步开始错
|
||
|
||
-- 错题本
|
||
in_error_book BOOLEAN NOT NULL DEFAULT false,
|
||
mastered BOOLEAN NOT NULL DEFAULT false,
|
||
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
||
);
|
||
|
||
-- ============================================
|
||
-- 索引
|
||
-- ============================================
|
||
CREATE INDEX idx_papers_user ON papers(user_id);
|
||
CREATE INDEX idx_papers_course ON papers(course_code);
|
||
CREATE INDEX idx_papers_status ON papers(status);
|
||
|
||
CREATE INDEX idx_questions_paper ON paper_questions(paper_id);
|
||
CREATE INDEX idx_questions_type ON paper_questions(question_type);
|
||
CREATE INDEX idx_questions_topics ON paper_questions USING GIN(topics);
|
||
|
||
CREATE INDEX idx_attempts_user ON user_attempts(user_id);
|
||
CREATE INDEX idx_attempts_question ON user_attempts(question_id);
|
||
CREATE INDEX idx_attempts_errorbook ON user_attempts(user_id)
|
||
WHERE in_error_book = true;
|
||
|
||
-- ============================================
|
||
-- RLS 策略
|
||
-- ============================================
|
||
ALTER TABLE papers ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE paper_questions ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE user_attempts ENABLE ROW LEVEL SECURITY;
|
||
|
||
-- papers: 用户只能看自己上传的（以后加公共库时再调整）
|
||
CREATE POLICY "Users can view own papers"
|
||
ON papers FOR SELECT
|
||
USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can insert own papers"
|
||
ON papers FOR INSERT
|
||
WITH CHECK (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can update own papers"
|
||
ON papers FOR UPDATE
|
||
USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can delete own papers"
|
||
ON papers FOR DELETE
|
||
USING (auth.uid() = user_id);
|
||
|
||
-- paper_questions: 跟随 paper 的权限
|
||
CREATE POLICY "Users can view questions of own papers"
|
||
ON paper_questions FOR SELECT
|
||
USING (
|
||
EXISTS (
|
||
SELECT 1 FROM papers
|
||
WHERE papers.id = paper_questions.paper_id
|
||
AND papers.user_id = auth.uid()
|
||
)
|
||
);
|
||
|
||
-- service_role 用于后端写入 questions（处理管线用）
|
||
-- 前端不直接写 questions，通过 API 触发后端处理
|
||
|
||
-- user_attempts: 用户只能看/写自己的
|
||
CREATE POLICY "Users can view own attempts"
|
||
ON user_attempts FOR SELECT
|
||
USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can insert own attempts"
|
||
ON user_attempts FOR INSERT
|
||
WITH CHECK (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can update own attempts"
|
||
ON user_attempts FOR UPDATE
|
||
USING (auth.uid() = user_id);
|
||
|
||
-- ============================================
|
||
-- updated_at 自动更新触发器
|
||
-- ============================================
|
||
CREATE OR REPLACE FUNCTION update_updated_at()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
NEW.updated_at = now();
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
CREATE TRIGGER papers_updated_at
|
||
BEFORE UPDATE ON papers
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at();
|
||
|
||
CREATE TRIGGER questions_updated_at
|
||
BEFORE UPDATE ON paper_questions
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at();
|
||
|
||
-- ============================================
|
||
-- Storage bucket
|
||
-- ============================================
|
||
-- 在 Supabase Dashboard 中手动创建 bucket: "papers"
|
||
-- 或通过 API 创建（后端初始化时处理）
|