query-database/docs/TECH-MySQL查询练习网站-技术架构.md

1.Architecture design

graph TD
  U["User Browser"] --> F["Vue Frontend Application"]
  F --> S["Backend API Service"]
  S --> M["MySQL 8 Database"]
  S --> P["SQLite (Metadata)"]

  subgraph "Frontend Layer"
    F
  end

  subgraph "Backend Layer"
    S
  end

  subgraph "Data Layer"
    M
  end

  subgraph "Service Layer (Provided by Supabase)"
    P
  end

2.Technology Description

• Frontend: Vue3 + Vite + TailwindCSS
• Backend: Go + Gin
• Auth: JWT（后端签发与校验）
• Metadata: SQLite（用户、题目、进度、用户数据库列表等）
• SQL Execution Engine: MySQL 8（用于模拟库与用户导入库的执行环境）

3.Route definitions

Route	Purpose
/login	登录/注册页
/	首页：题目分级列表、新手引导、继续练习
/practice/:id	练习页：SQL 编辑、运行、结果与校验
/databases	数据库管理：选择模拟库、导入自定义库、激活当前库

4.API definitions (If it includes backend services)

4.1 Core Types (TypeScript)

export type Level = 'beginner' | 'normal' | 'advanced'

export type ModuleKey = 'shop' | 'hr'

export type ActiveDatabase = {
  id: string
  name: string
  source: 'mock' | 'imported'
  schemaName: string
}

export type ExerciseSummary = {
  id: string
  title: string
  level: Level
  isSolved: boolean
}

export type ExecuteSqlRequest = {
  exerciseId: string
  sql: string
}

export type ExecuteSqlResponse = {
  ok: boolean
  durationMs: number
  columns?: string[]
  rows?: Array<Record<string, unknown>>
  errorMessage?: string
  verdict?: 'pass' | 'fail'
  hint?: string
}

export type ImportDatabaseRequest = {
  name: string
  initSqlFileUrl: string
}

export type UserDatabase = {
  id: string
  name: string
  source: 'mock' | 'imported'
  createdAt: string
}

4.2 Core API

• 获取题目列表

GET /api/exercises?level=beginner|normal|advanced&query=...

• 获取题目详情（含描述与校验配置）

GET /api/exercises/:id

• 执行 SQL + 返回结果 + 判题

POST /api/sql/execute

Request: ExecuteSqlRequest

• 保存/更新进度（草稿 SQL、是否通过）

POST /api/progress/upsert

• 获取/切换当前激活数据库

GET /api/user-databases
POST /api/user-databases/activate

• 导入自定义数据库（先上传文件到 Storage，再触发导入）

POST /api/user-databases/import

Request: ImportDatabaseRequest

• 新手引导状态（是否已完成/已跳过）

GET /api/onboarding
POST /api/onboarding/complete

5.Server architecture diagram (If it includes backend services)

graph TD
  A["Client / Frontend"] --> B["API Controller"]
  B --> C["Practice Service (Execute + Judge)"]
  B --> D["Exercise Service"]
  B --> E["Database Import Service"]
  C --> F["MySQL Repository"]
  D --> G["SQLite Repository (Metadata)"]
  E --> F

  subgraph "Server"
    B
    C
    D
    E
    F
    G
  end

6.Data model(if applicable)

6.1 Data model definition

（SQLite 用于题目/进度/用户库元数据；MySQL 用于实际运行 SQL 的数据面。）

erDiagram
  PROFILE {
    uuid id
    text email
    timestamptz created_at
  }
  EXERCISE {
    uuid id
    text title
    text level
    text prompt
    text answer_sql
  }
  USER_PROGRESS {
    uuid id
    uuid user_id
    uuid exercise_id
    text draft_sql
    bool is_solved
    timestamptz updated_at
  }
  USER_DATABASE {
    uuid id
    uuid user_id
    text name
    text source
    text mysql_schema_name
    bool is_active
    timestamptz created_at
  }

6.2 Data Definition Language

Profile Table (profiles)

CREATE TABLE profiles (
  id UUID PRIMARY KEY,
  email TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
GRANT SELECT ON profiles TO anon;
GRANT ALL PRIVILEGES ON profiles TO authenticated;

Exercise Table (exercises)

CREATE TABLE exercises (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  level TEXT NOT NULL CHECK (level IN ('beginner','normal','advanced')),
  prompt TEXT NOT NULL,
  answer_sql TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
GRANT SELECT ON exercises TO anon;
GRANT ALL PRIVILEGES ON exercises TO authenticated;

User Progress Table (user_progress)

CREATE TABLE user_progress (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  exercise_id UUID NOT NULL,
  draft_sql TEXT,
  is_solved BOOLEAN DEFAULT FALSE,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_user_progress_user_id ON user_progress(user_id);
CREATE INDEX idx_user_progress_exercise_id ON user_progress(exercise_id);
GRANT SELECT ON user_progress TO anon;
GRANT ALL PRIVILEGES ON user_progress TO authenticated;

User Database Table (user_databases)

CREATE TABLE user_databases (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  name TEXT NOT NULL,
  source TEXT NOT NULL CHECK (source IN ('mock','imported')),
  mysql_schema_name TEXT NOT NULL,
  is_active BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_user_databases_user_id ON user_databases(user_id);
GRANT SELECT ON user_databases TO anon