00_Eins 数据库 Schema (v1)

本文档整合《Eins 数据库设计说明书 V1.1》，在第一次建库时一次性执行 / 生成所有表结构。默认采用 PostgreSQL 17，字段类型及约束均以 Postgres 语法为准。

1. 枚举类型

为便于约束取值范围，先创建枚举类型（PostgreSQL CREATE TYPE）。

sql

-- 用户角色
CREATE TYPE user_role AS ENUM ('student', 'teacher', 'school_admin', 'platform_admin');

-- 任务类型（示例，后续可扩充）
CREATE TYPE task_type AS ENUM ('material', 'quiz', 'assignment', 'interactive');

-- AI 会话媒介类型
CREATE TYPE media_type AS ENUM ('text', 'image', 'video', 'audio');

2. 表结构

字段若未特别说明均 NOT NULL，主键名统一为 id，时间戳默认 timezone now()，采用 bigint 作为整型主键。

2.1 `schools`

sql

CREATE TABLE schools (
    id          bigserial PRIMARY KEY,
    name        varchar(128)  UNIQUE,
    region      varchar(128),
    created_at  timestamptz   DEFAULT now()
);

2.2 `users`

sql

CREATE TABLE users (
    id              bigserial PRIMARY KEY,
    username        varchar(128) UNIQUE,
    password_hash   varchar(128),
    role            user_role    DEFAULT 'student',
    school_id       bigint       REFERENCES schools(id) ON DELETE SET NULL,
    parent_user_id  bigint       REFERENCES users(id)   ON DELETE SET NULL,
    real_name       varchar(128),
    avatar_url      varchar(512),
    created_at      timestamptz  DEFAULT now()
);
CREATE INDEX idx_users_school_id ON users(school_id);

2.3 `classes`

sql

CREATE TABLE classes (
    id          bigserial PRIMARY KEY,
    school_id   bigint REFERENCES schools(id) ON DELETE CASCADE,
    name        varchar(128),
    edu_year    int,   -- 学年，如 2025
    created_at  timestamptz DEFAULT now()
);
CREATE INDEX idx_classes_school_id ON classes(school_id);

2.4 `class_members`

sql

CREATE TABLE class_members (
    id         bigserial PRIMARY KEY,
    class_id   bigint REFERENCES classes(id) ON DELETE CASCADE,
    user_id    bigint REFERENCES users(id)   ON DELETE CASCADE,
    joined_at  timestamptz DEFAULT now(),
    UNIQUE(class_id, user_id)
);

2.5 `class_teachers`

sql

CREATE TABLE class_teachers (
    id         bigserial PRIMARY KEY,
    class_id   bigint REFERENCES classes(id) ON DELETE CASCADE,
    teacher_id bigint REFERENCES users(id)   ON DELETE CASCADE,
    role       varchar(32) DEFAULT 'instructor',
    UNIQUE(class_id, teacher_id)
);

2.6 `courses`

sql

CREATE TABLE courses (
    id            bigserial PRIMARY KEY,
    title         varchar(256),
    description   text,
    teacher_id    bigint REFERENCES users(id) ON DELETE SET NULL,
    syllabus_json jsonb,
    is_public     boolean DEFAULT false,
    created_at    timestamptz DEFAULT now()
);
CREATE INDEX idx_courses_teacher_id ON courses(teacher_id);

2.7 `lessons`

sql

CREATE TABLE lessons (
    id          bigserial PRIMARY KEY,
    course_id   bigint REFERENCES courses(id) ON DELETE CASCADE,
    title       varchar(256),
    content_url varchar(1024),
    sort_order  int DEFAULT 0
);
CREATE INDEX idx_lessons_course_id ON lessons(course_id);

2.8 `tasks`

sql

CREATE TABLE tasks (
    id          bigserial PRIMARY KEY,
    lesson_id   bigint REFERENCES lessons(id) ON DELETE CASCADE,
    title       varchar(256),
    ai_enabled  boolean DEFAULT false,
    task_type   task_type,
    config      jsonb
);
CREATE INDEX idx_tasks_lesson_id ON tasks(lesson_id);

2.9 `course_enrollments`

sql

CREATE TABLE course_enrollments (
    id          bigserial PRIMARY KEY,
    course_id   bigint REFERENCES courses(id) ON DELETE CASCADE,
    user_id     bigint REFERENCES users(id)   ON DELETE CASCADE,
    progress    numeric(5,2) DEFAULT 0,  -- 0-100
    enrolled_at timestamptz DEFAULT now(),
    UNIQUE(course_id, user_id)
);

2.10 `assignments`

sql

CREATE TABLE assignments (
    id          bigserial PRIMARY KEY,
    course_id   bigint REFERENCES courses(id) ON DELETE CASCADE,
    task_id     bigint REFERENCES tasks(id)   ON DELETE SET NULL,
    class_id    bigint REFERENCES classes(id) ON DELETE CASCADE,
    deadline    timestamptz,
    created_at  timestamptz DEFAULT now()
);

2.11 `assignment_submissions`

sql

CREATE TABLE assignment_submissions (
    id             bigserial PRIMARY KEY,
    assignment_id  bigint REFERENCES assignments(id) ON DELETE CASCADE,
    user_id        bigint REFERENCES users(id)        ON DELETE CASCADE,
    answer_text    text,
    score          numeric(5,2),
    submitted_at   timestamptz DEFAULT now(),
    UNIQUE(assignment_id, user_id)
);

2.12 `ai_sessions`

sql

CREATE TABLE ai_sessions (
    id         bigserial PRIMARY KEY,
    user_id    bigint REFERENCES users(id) ON DELETE CASCADE,
    task_id    bigint REFERENCES tasks(id) ON DELETE SET NULL,
    prompt     text,
    response   text,
    media_type media_type,
    created_at timestamptz DEFAULT now()
);
CREATE INDEX idx_ai_sessions_user_id ON ai_sessions(user_id);

2.13 `tokens`

sql

CREATE TABLE tokens (
    id         bigserial PRIMARY KEY,
    user_id    bigint UNIQUE REFERENCES users(id) ON DELETE CASCADE,
    balance    numeric(10,2) DEFAULT 0,
    expires_at timestamptz,
    updated_at timestamptz DEFAULT now()
);

2.14 `token_logs`

sql

CREATE TABLE token_logs (
    id          bigserial PRIMARY KEY,
    user_id     bigint REFERENCES users(id) ON DELETE CASCADE,
    amount      numeric(10,2),  -- 正数充值 / 负数扣费
    reason      varchar(256),
    created_at  timestamptz DEFAULT now()
);
CREATE INDEX idx_token_logs_user_id ON token_logs(user_id);

3. 外键速查

子表	父表	外键字段
users	schools	school_id
users	users	parent_user_id
classes	schools	school_id
class_members	classes	class_id
class_members	users	user_id
class_teachers	classes	class_id
class_teachers	users	teacher_id
courses	users	teacher_id
lessons	courses	course_id
tasks	lessons	lesson_id
course_enrollments	courses	course_id
course_enrollments	users	user_id
assignments	courses	course_id
assignments	tasks	task_id
assignments	classes	class_id
assignment_submissions	assignments	assignment_id
assignment_submissions	users	user_id
ai_sessions	users	user_id
ai_sessions	tasks	task_id
tokens	users	user_id
token_logs	users	user_id

4. 建表顺序示例 (SQL)

sql

-- 1. 枚举类型
--   user_role, task_type, media_type

-- 2. 学校 & 用户等基础表
--   schools → users → classes → class_members

-- 3. 课程相关
--   courses → lessons → tasks → course_enrollments

-- 4. 作业相关
--   assignments → assignment_submissions

-- 5. AI & Token
--   ai_sessions → tokens → token_logs

5. 迁移工具

推荐使用 Alembic 生成对应迁移脚本：

bash

alembic revision --autogenerate -m "init all tables"
alembic upgrade head

TIP: 先在 models/ 中创建与上述表结构一致的 SQLAlchemy ORM 模型，再运行 --autogenerate。

6. 为什么选择 ORM + Alembic

优势	说明
单一数据模型源	业务代码与数据库结构共用同一份 SQLAlchemy Model，避免双维护。
自动迁移	表结构变动后，`alembic revision --autogenerate` 可自动生成增量迁移脚本，保障生产数据安全升级。
可测试、可回滚	每个迁移脚本具备 `upgrade` / `downgrade`，CI/CD 中可做回滚验证。
跨环境一致性	Dev / Staging / Prod 通过同一迁移历史保持结构一致，避免 "Works on my machine"。
pythonic 交互	ORM 层提供类型提示、关系导航，开发体验友好。

8. 默认种子数据（`seed initial data` 迁移）

表	记录	说明
schools	(id=1, name="测试学校", region="上海")	系统默认学校
classes	(id=1, school_id=1, name="五年五班", edu_year=2025)	默认班级
users	admin / admin / platform_admin	平台管理员，real_name=九章之光
users	school / school / school_admin	学校管理员，school_id=1，parent=admin
users	teacher / teacher / teacher	教师账号，school_id=1，parent=school
users	student / student / student	学生账号，school_id=1，parent=teacher
class_teachers	class_id=1, teacher_id=teacher	关联教师与班级
class_members	class_id=1, user_id=student	关联学生与班级

以上数据由 Alembic 迁移 db513ced18e7_seed_initial_data.py 在 upgrade() 阶段自动插入；downgrade() 会全部删除。

00_Eins 数据库 Schema (v1) ​

1. 枚举类型 ​

2. 表结构 ​

2.1 schools ​

2.2 users ​

2.3 classes ​

2.4 class_members ​

2.5 class_teachers ​

2.6 courses ​

2.7 lessons ​

2.8 tasks ​

2.9 course_enrollments ​

2.10 assignments ​

2.11 assignment_submissions ​

2.12 ai_sessions ​

2.13 tokens ​

2.14 token_logs ​

3. 外键速查 ​

4. 建表顺序示例 (SQL) ​

5. 迁移工具 ​

6. 为什么选择 ORM + Alembic ​

8. 默认种子数据（seed initial data 迁移） ​

00_Eins 数据库 Schema (v1)

1. 枚举类型

2. 表结构

2.1 `schools`

2.2 `users`

2.3 `classes`

2.4 `class_members`

2.5 `class_teachers`

2.6 `courses`

2.7 `lessons`

2.8 `tasks`

2.9 `course_enrollments`

2.10 `assignments`

2.11 `assignment_submissions`

2.12 `ai_sessions`

2.13 `tokens`

2.14 `token_logs`

3. 外键速查

4. 建表顺序示例 (SQL)

5. 迁移工具

6. 为什么选择 ORM + Alembic

8. 默认种子数据（`seed initial data` 迁移）