LMS Database Skill

Supabase 資料庫查詢模式、RLS 政策、Migration 記錄 Last Updated: 2026-01-02

相關 Skill: kcis-school-config - 學校專屬設定

Database Connection Strings

# Staging Database (kqvpcoolgyhjqleekmee)
# Used by: lms-staging.zeabur.app
psql "postgresql://postgres.kqvpcoolgyhjqleekmee:geonook8588@aws-1-ap-southeast-2.pooler.supabase.com:6543/postgres"

# Production Database (piwbooidofbaqklhijup)
# Used by: lms.kcislk.ntpc.edu.tw (future)
psql "postgresql://postgres.piwbooidofbaqklhijup:geonook8588@aws-1-ap-southeast-1.pooler.supabase.com:6543/postgres"

---

Supabase Nested Join 查詢模式（重要！）

核心規則

Supabase 的 table!inner 語法是透過 外鍵（FK） 連接，不是透過查詢中選取的欄位。

資料庫關聯鏈

scores → exam_id (FK) → exams → course_id (FK) → courses → class_id (FK) → classes

注意：exams 表沒有 class_id 欄位（Migration 035 已移除），只有 course_id。

正確模式

const { data } = await supabase
  .from('scores')
  .select(`
    student_id,
    assessment_code,
    score,
    exam:exams!inner(
      course_id,                    // ← 取得 FK 欄位
      course:courses!inner(
        id,
        class_id,                   // ← 從 course 取得 class_id
        course_type
      )
    )
  `)
  .in('student_id', studentIds)   // ← 限制查詢範圍
  .not('score', 'is', null);

// 過濾時使用 exam.course.class_id
const filtered = data.filter(s => {
  const examData = s.exam as { course_id: string; course: { class_id: string; ... } };
  return classIdSet.has(examData.course.class_id);  // 正確
});

錯誤模式

exam:exams!inner(
  class_id,                       // ← 這個欄位已不存在！會返回 400 Error
  course:courses!inner(...)
)

為什麼這很重要

• exams 表只有 course_id 欄位（NOT NULL）
• class_id 已在 Migration 035 移除
• 如果需要 class_id，必須從 exam.course.class_id 取得
• 直接查詢 exams.class_id 會返回 400 Bad Request

效能最佳實踐

// 永遠加上限制條件避免全表掃描
.in('student_id', studentIds)
.in('exam_id', examIds)
.eq('class_id', classId)

---

RLS (Row Level Security) 核心規則

四層安全架構（v1.66.0）

1. Authentication (Supabase Auth) - 必須登入
2. RLS (Database Layer) - 粗粒度：authenticated_read, admin_full_access
3. Application Layer (lib/api/permissions.ts) - 細粒度：角色過濾
4. Frontend (AuthGuard) - 頁面存取控制

設計原則：

• RLS 只負責「是否登入」和「是否 Admin」
• 細粒度權限（Head 年級過濾、Teacher 課程過濾）在 Application Layer 處理
• 避免 RLS 跨表查詢造成遞迴

簡化後的 RLS Policies

每張表最多 4 個 policies：

1. service_role_bypass - Service Role 繞過
2. admin_full_access - Admin 完整存取
3. authenticated_read - 已登入者可讀
4. teacher_manage_own - 教師管理自己課程（僅 courses, exams, scores）

角色權限矩陣

角色	RLS 層	Application 層
admin	全部存取	不過濾
office_member	可讀全部	不過濾（唯讀）
head	可讀全部	過濾 grade_band + track
teacher	可讀全部 + 寫自己課程	過濾 teacher_id

Service Role Bypass

所有表都有 service_role_bypass 政策：

• Service Role Key 可繞過所有 RLS
• 用於 CSV 匯入、Migration 等管理操作

---

關鍵 Migration 記錄

Migration 014: Track 欄位型別修正

• users.track: track_type → course_type
• students.track: track_type → course_type (nullable)
• 重建 3 個 Analytics 視圖

Migration 015: RLS 效能優化

• 優化 49 個 policies
• auth.uid() → (SELECT auth.uid())
• Database Linter 警告從 44+ 降至 0

Migration 028: Users 表 RLS 遞迴修復

• 刪除 24 個有遞迴問題的 policies
• 建立簡單的 authenticated_read_users 政策

Migration 029: Course Tasks Kanban

• 建立 course_tasks 表
• 支援任務看板功能

Migration 030: Four-Term 學期系統

• 新增 exams.term (1-4) 和 exams.semester (1-2) 欄位
• 自動計算 trigger

Migration 031: 2026-2027 學年

• 複製 84 班級 + 252 課程

Migration 032: Gradebook Expectations

• 建立 gradebook_expectations 表
• Head Teacher 成績進度預期設定

Migration 033: KCFS Scoring System

• 新增 KCFS 評量類別代碼（COMM, COLLAB, SD, CT, BW, PORT, PRES）
• 新增 scores.is_absent 欄位
• 支援 0-5 分制的 KCFS 評分

Migration 034: MAP Tables

• 建立 map_assessments 表（MAP 測驗成績）
• 建立 map_goal_scores 表（目標領域分數）
• 支援 NWEA MAP Growth 數據分析

Migration 035: Sync Exams Schema

• 移除 exams.class_id 欄位
• 設定 exams.course_id 為 NOT NULL
• 重命名 is_published 為 is_active
• 同步 Staging 與 Production 資料庫結構

Migration 036: RLS Simplification

• 簡化 RLS policies 從 100+ 降至 ~30
• 建立 is_admin() helper function
• 設計原則：不跨表查詢，防止無限遞迴
• 細粒度權限移至 Application Layer

Migration 037: Complete RLS Policies

• 補齊 12 個遺漏表的 RLS policies
• 受影響表：map_assessments, map_goal_scores, attendance, behavior_tags, student_behaviors, communications, gradebook_expectations, academic_periods, kcfs_categories, timetable_entries, timetable_periods, course_tasks, admin_audit_logs

Migration 038: Rename MAP Term

• 重命名 MAP 相關欄位

Migration 039: Fall-to-Fall Growth Columns

• 新增 Fall-to-Fall 成長計算欄位

Migration 040: Academic Periods

• 建立 academic_periods 表
• 學期與學年設定

Migration 041: Student Class History

• 建立 student_class_history 表
• 儲存學生歷史班級資訊（academic_year, grade, english_class, homeroom）
• 用於 MAP 成長分析中的正確班級對應（避免學生升級後顯示新班級）

---

常用查詢模式

取得班級課程與教師

const { data } = await supabase
  .from('courses')
  .select(`
    id,
    course_type,
    teacher:users!teacher_id(
      id,
      full_name
    ),
    class:classes!inner(
      id,
      class_name,
      grade
    )
  `)
  .eq('class_id', classId);

取得學生成績（含課程篩選）

const { data } = await supabase
  .from('scores')
  .select(`
    student_id,
    assessment_code,
    score,
    exam:exams!inner(
      course:courses!inner(
        course_type
      )
    )
  `)
  .eq('exam.course.course_type', courseType)
  .in('student_id', studentIds);

取得 Head Teacher 管轄班級

// HT 的 grade 和 track（course_type）定義管轄範圍
const { data } = await supabase
  .from('classes')
  .select('*')
  .eq('grade', headTeacherGrade)
  .eq('academic_year', academicYear);

// 課程需額外過濾 course_type
const courses = allCourses.filter(c => c.course_type === headTeacherTrack);

---

資料表索引

效能關鍵索引

-- scores 表
CREATE INDEX idx_scores_student_id ON scores(student_id);
CREATE INDEX idx_scores_exam_id ON scores(exam_id);

-- exams 表
CREATE INDEX idx_exams_course_id ON exams(course_id);
CREATE INDEX idx_exams_term ON exams(term);
CREATE INDEX idx_exams_course_term ON exams(course_id, term);

-- courses 表
CREATE INDEX idx_courses_class_id ON courses(class_id);
CREATE INDEX idx_courses_teacher_id ON courses(teacher_id);

---

常見錯誤與解決

錯誤：406 Not Acceptable

原因：RLS 政策阻擋查詢 解決：檢查用戶角色權限，或使用 service role

錯誤：25P02 transaction aborted

原因：RLS 無限遞迴 解決：檢查政策是否查詢同一張表，使用 SECURITY DEFINER 函數

錯誤：Nested join 結果為空

原因：FK 欄位與過濾邏輯不匹配 解決：從正確的巢狀物件取得 class_id（見上方正確模式）

錯誤：查詢結果被截斷到 1000 筆

原因：Supabase PostgREST 的 max_rows 預設是 1000 解決：

1. Supabase Dashboard → API Settings → Max rows 設為 10000
2. 程式碼使用 .range() 分頁查詢：

// 分頁查詢繞過 max_rows 限制
const PAGE_SIZE = 1000;
let page = 0;
let allData: any[] = [];
let hasMore = true;

while (hasMore) {
  const { data } = await supabase
    .from('scores')
    .select('exam_id')
    .in('exam_id', examIds)
    .range(page * PAGE_SIZE, (page + 1) * PAGE_SIZE - 1);

  if (data && data.length > 0) {
    allData = allData.concat(data);
    page++;
    hasMore = data.length === PAGE_SIZE;
  } else {
    hasMore = false;
  }
}