yidaima/RuoYi-Vue/sql/front_workbench.sql

-- =================================================
-- EasyCode front workbench tables
-- Import after sql/db.sql, or use the copy appended to sql/db.sql
-- =================================================

drop table if exists front_project_file;
drop table if exists front_ai_usage_ledger;
drop table if exists front_ai_generation_task;
drop table if exists front_ai_quota_bucket;
drop table if exists front_project_generation;
drop table if exists front_project_column;
drop table if exists front_project_table;
drop table if exists front_project_module;
drop table if exists front_project;
drop table if exists front_user;

create table front_user (
  user_id              bigint(20)      not null auto_increment comment '前台用户ID',
  username             varchar(50)     not null comment '用户名',
  password             varchar(100)    not null comment '密码',
  nickname             varchar(50)     default '' comment '昵称',
  email                varchar(100)    default '' comment '邮箱',
  phone                varchar(20)     default '' comment '手机号',
  status               char(1)         default '0' comment '状态（0正常 1停用）',
  del_flag             char(1)         default '0' comment '删除标志（0存在 2删除）',
  last_login_time      datetime        default null comment '最后登录时间',
  create_time          datetime        default null comment '创建时间',
  update_time          datetime        default null comment '更新时间',
  remark               varchar(500)    default null comment '备注',
  primary key (user_id),
  unique key uk_front_user_username (username),
  key idx_front_user_email (email),
  key idx_front_user_phone (phone)
) engine=innodb auto_increment=100 default charset=utf8mb4 comment='前台用户表';

create table front_project (
  project_id           bigint(20)      not null auto_increment comment '前台项目ID',
  user_id              bigint(20)      not null comment '前台用户ID',
  project_name         varchar(100)    not null comment '项目名称',
  project_file_name    varchar(100)    default '' comment '项目文件名',
  package_name         varchar(100)    default 'com.easycode.generated' comment '包名',
  version              varchar(20)     default '1.0.0' comment '版本号',
  project_desc         varchar(1000)   default '' comment '项目描述',
  requirement_keyword  varchar(1000)   default '' comment '需求关键词',
  industry_template    varchar(50)     default '' comment '行业模板',
  front_framework      varchar(50)     default 'Vue3 + Element Plus' comment '前台框架',
  back_framework       varchar(50)     default 'Spring Boot + MyBatis' comment '后端框架',
  frontend_enabled     char(1)         default '1' comment '是否生成前台前端（1是 0否）',
  generate_status      char(1)         default '0' comment '数据库生成状态（0未生成 1生成中 2已生成 3失败）',
  preview_status       char(1)         default '0' comment '预览状态（0未预览 1已预览 2失败）',
  app_blueprint        longtext        comment '应用蓝图草稿JSON',
  er_diagram           longtext        comment 'ER图草稿JSON',
  business_blueprint   longtext        comment '业务蓝图草稿JSON',
  status               char(1)         default '0' comment '状态（0正常 1停用）',
  create_time          datetime        default null comment '创建时间',
  update_time          datetime        default null comment '更新时间',
  remark               varchar(500)    default null comment '备注',
  primary key (project_id),
  key idx_front_project_user_id (user_id),
  constraint fk_front_project_user foreign key (user_id) references front_user(user_id)
) engine=innodb auto_increment=100 default charset=utf8mb4 comment='前台项目草稿表';

create table front_project_module (
  id                   bigint(20)      not null auto_increment comment 'id',
  project_id           bigint(20)      not null comment '前台项目ID',
  module_id            bigint(20)      not null comment '功能模块ID',
  status               char(1)         default '0' comment '状态（0正常 1停用）',
  create_time          datetime        default null comment '创建时间',
  update_time          datetime        default null comment '更新时间',
  remark               varchar(500)    default null comment '备注',
  primary key (id),
  unique key uk_front_project_module (project_id, module_id),
  key idx_front_project_module_project_id (project_id),
  key idx_front_project_module_module_id (module_id),
  constraint fk_front_project_module_project foreign key (project_id) references front_project(project_id),
  constraint fk_front_project_module_module foreign key (module_id) references sys_module(module_id)
) engine=innodb auto_increment=100 default charset=utf8mb4 comment='前台项目功能模块关系';

create table front_project_table (
  table_id             bigint(20)      not null auto_increment comment '表设计ID',
  project_id           bigint(20)      not null comment '项目ID',
  user_id              bigint(20)      not null comment '前台用户ID',
  table_name           varchar(200)    not null comment '表名称',
  table_comment        varchar(500)    default '' comment '表描述',
  class_name           varchar(100)    default '' comment '实体类名称',
  module_name          varchar(30)     default '' comment '生成模块名',
  business_name        varchar(30)     default '' comment '生成业务名',
  function_name        varchar(50)     default '' comment '生成功能名',
  tpl_category         varchar(200)    default 'crud' comment '模板类型',
  tpl_web_type         varchar(30)     default 'element-plus' comment '前端模板类型',
  options              varchar(1000)   default '' comment '生成选项',
  create_table_sql     longtext        comment '建表SQL',
  sort                 int             default 0 comment '排序',
  status               char(1)         default '0' comment '状态（0正常 1停用）',
  create_time          datetime        default null comment '创建时间',
  update_time          datetime        default null comment '更新时间',
  primary key (table_id),
  unique key uk_front_project_table (project_id, table_name),
  key idx_front_project_table_project_id (project_id),
  constraint fk_front_project_table_project foreign key (project_id) references front_project(project_id)
) engine=innodb auto_increment=100 default charset=utf8mb4 comment='前台项目表设计';

create table front_project_column (
  column_id            bigint(20)      not null auto_increment comment '字段设计ID',
  table_id             bigint(20)      not null comment '表设计ID',
  project_id           bigint(20)      not null comment '项目ID',
  column_name          varchar(200)    not null comment '字段名称',
  column_comment       varchar(500)    default '' comment '字段描述',
  column_type          varchar(100)    not null comment '字段类型',
  java_type            varchar(500)    default 'String' comment 'Java类型',
  java_field           varchar(200)    default '' comment 'Java字段名',
  is_pk                char(1)         default '0' comment '是否主键（1是）',
  is_increment         char(1)         default '0' comment '是否自增（1是）',
  is_required          char(1)         default '0' comment '是否必填（1是）',
  is_insert            char(1)         default '1' comment '是否插入字段（1是）',
  is_edit              char(1)         default '1' comment '是否编辑字段（1是）',
  is_list              char(1)         default '1' comment '是否列表字段（1是）',
  is_query             char(1)         default '0' comment '是否查询字段（1是）',
  query_type           varchar(200)    default 'EQ' comment '查询方式',
  html_type            varchar(200)    default 'input' comment '显示类型',
  dict_type            varchar(200)    default '' comment '字典类型',
  default_value        varchar(200)    default null comment '默认值',
  sort                 int             default 0 comment '排序',
  create_time          datetime        default null comment '创建时间',
  update_time          datetime        default null comment '更新时间',
  primary key (column_id),
  key idx_front_project_column_table_id (table_id),
  key idx_front_project_column_project_id (project_id),
  constraint fk_front_project_column_table foreign key (table_id) references front_project_table(table_id)
) engine=innodb auto_increment=100 default charset=utf8mb4 comment='前台项目字段设计';

create table front_project_generation (
  generation_id        bigint(20)      not null auto_increment comment '生成记录ID',
  project_id           bigint(20)      not null comment '项目ID',
  user_id              bigint(20)      not null comment '前台用户ID',
  generate_type        varchar(50)     not null comment '生成类型',
  provider             varchar(50)     default 'deepseek' comment 'AI提供方',
  prompt_summary       varchar(500)    default '' comment 'Prompt摘要',
  request_payload      longtext        comment '请求内容',
  response_payload     longtext        comment '响应内容',
  success              char(1)         default '0' comment '是否成功（1成功 0失败）',
  error_message        varchar(1000)   default '' comment '错误信息',
  token_usage          int             default 0 comment 'Token用量',
  elapsed_ms           bigint(20)      default 0 comment '耗时毫秒',
  create_time          datetime        default null comment '创建时间',
  primary key (generation_id),
  key idx_front_project_generation_project_id (project_id)
) engine=innodb auto_increment=100 default charset=utf8mb4 comment='前台项目生成记录';

create table front_ai_generation_task (
  task_id              bigint(20)      not null auto_increment comment 'AI task ID',
  generation_id        bigint(20)      default null comment 'Generation record ID',
  project_id           bigint(20)      not null comment 'Project ID',
  user_id              bigint(20)      not null comment 'Front user ID',
  generate_type        varchar(50)     not null comment 'Generation type',
  provider             varchar(50)     default 'deepseek' comment 'AI provider',
  model                varchar(100)    default 'deepseek-chat' comment 'AI model',
  status               varchar(30)     not null default 'QUEUED' comment 'Task status',
  request_hash         varchar(64)     not null comment 'Request idempotency hash',
  request_payload      longtext        comment 'Request JSON',
  result_payload       longtext        comment 'Result JSON',
  error_code           varchar(100)    default '' comment 'Error code',
  error_message        varchar(1000)   default '' comment 'Error message',
  attempts             int             default 0 comment 'Attempt count',
  max_attempts         int             default 3 comment 'Max attempts',
  next_retry_time      datetime        default null comment 'Next retry time',
  locked_by            varchar(100)    default '' comment 'Worker lock owner',
  locked_until         datetime        default null comment 'Worker lock expiry',
  progress             int             default 0 comment 'Progress percent',
  current_step         varchar(100)    default '' comment 'Current step',
  input_tokens         int             default 0 comment 'Input tokens',
  output_tokens        int             default 0 comment 'Output tokens',
  total_tokens         int             default 0 comment 'Total tokens',
  cost_cents           int             default 0 comment 'Estimated cost in cents',
  started_at           datetime        default null comment 'Started time',
  finished_at          datetime        default null comment 'Finished time',
  create_time          datetime        default null comment 'Create time',
  update_time          datetime        default null comment 'Update time',
  primary key (task_id),
  key idx_front_ai_task_project (user_id, project_id),
  key idx_front_ai_task_status (status, next_retry_time),
  key idx_front_ai_task_generation (generation_id),
  key idx_front_ai_task_hash (user_id, project_id, request_hash)
) engine=innodb auto_increment=100 default charset=utf8mb4 comment='Front AI generation task';

create table front_ai_quota_bucket (
  bucket_id            bigint(20)      not null auto_increment comment 'Quota bucket ID',
  user_id              bigint(20)      not null comment 'Front user ID',
  period_type          varchar(20)     not null comment 'DAY or MONTH',
  period_key           varchar(20)     not null comment 'Period key',
  task_limit           int             default 0 comment 'Task limit',
  task_used            int             default 0 comment 'Used tasks',
  token_limit          int             default 0 comment 'Token limit',
  token_used           int             default 0 comment 'Used tokens',
  cost_limit_cents     int             default 0 comment 'Cost limit in cents',
  cost_used_cents      int             default 0 comment 'Used cost in cents',
  running_limit        int             default 1 comment 'Running task limit',
  running_count        int             default 0 comment 'Running task count',
  create_time          datetime        default null comment 'Create time',
  update_time          datetime        default null comment 'Update time',
  primary key (bucket_id),
  unique key uk_front_ai_quota_bucket (user_id, period_type, period_key)
) engine=innodb auto_increment=100 default charset=utf8mb4 comment='Front AI quota bucket';

create table front_ai_usage_ledger (
  ledger_id            bigint(20)      not null auto_increment comment 'Usage ledger ID',
  task_id              bigint(20)      default null comment 'Task ID',
  generation_id        bigint(20)      default null comment 'Generation record ID',
  project_id           bigint(20)      not null comment 'Project ID',
  user_id              bigint(20)      not null comment 'Front user ID',
  generate_type        varchar(50)     not null comment 'Generation type',
  provider             varchar(50)     default 'deepseek' comment 'AI provider',
  model                varchar(100)    default 'deepseek-chat' comment 'AI model',
  input_tokens         int             default 0 comment 'Input tokens',
  output_tokens        int             default 0 comment 'Output tokens',
  total_tokens         int             default 0 comment 'Total tokens',
  cost_cents           int             default 0 comment 'Estimated cost in cents',
  success              char(1)         default '0' comment 'Success flag',
  error_message        varchar(1000)   default '' comment 'Error message',
  create_time          datetime        default null comment 'Create time',
  primary key (ledger_id),
  key idx_front_ai_usage_user (user_id, create_time),
  key idx_front_ai_usage_task (task_id)
) engine=innodb auto_increment=100 default charset=utf8mb4 comment='Front AI usage ledger';

create table front_project_file (
  file_id              bigint(20)      not null auto_increment comment '文件缓存ID',
  project_id           bigint(20)      not null comment '项目ID',
  template_type        varchar(50)     not null comment '模板类型',
  node_id              varchar(100)    default '' comment '结构节点ID',
  table_id             bigint(20)      default null comment '表设计ID',
  file_name            varchar(255)    not null comment '文件名',
  file_path            varchar(500)    default '' comment '文件路径',
  file_content         longtext        comment '文件内容',
  content_hash         varchar(64)     default '' comment '内容哈希',
  create_time          datetime        default null comment '创建时间',
  update_time          datetime        default null comment '更新时间',
  primary key (file_id),
  key idx_front_project_file_project_id (project_id),
  key idx_front_project_file_lookup (project_id, template_type, table_id)
) engine=innodb auto_increment=100 default charset=utf8mb4 comment='前台项目预览文件缓存';