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

## 1.Architecture design
```mermaid
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)
```ts
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)
```mermaid
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 的数据面。）

```mermaid
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
```