admin/mini_tp
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
mini_tp/database.sql
T
nepiedg 36c506f4bf feat(note): add audio upload and sharing functionality 
- Introduced `note_audio` table for storing audio attachments related to notes.
- Implemented audio upload endpoint in `Note` controller to handle audio file uploads.
- Added sharing functionality with `note_share` table to manage share tokens and view counts.
- Updated API routes to include endpoints for audio uploads and share creation.
- Enhanced documentation to reflect new audio and sharing features.
2026-04-17 10:33:33 +00:00

141 lines
7.9 KiB
SQL
Raw Permalink Blame History

-- 用户表
CREATE TABLE IF NOT EXISTS `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`username` varchar(50) NOT NULL DEFAULT '' COMMENT '用户名',
`password` varchar(255) NOT NULL DEFAULT '' COMMENT '密码',
`nickname` varchar(50) DEFAULT '' COMMENT '昵称',
`email` varchar(100) DEFAULT '' COMMENT '邮箱',
`phone` varchar(20) DEFAULT '' COMMENT '手机号',
`avatar` varchar(255) DEFAULT '' COMMENT '头像',
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态 0:禁用 1:启用',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '更新时间',
`delete_time` int(11) DEFAULT NULL COMMENT '删除时间',
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
KEY `email` (`email`),
KEY `phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
-- 插入示例数据
INSERT INTO `user` (`username`, `password`, `nickname`, `email`, `status`) VALUES
('demo', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '演示用户', 'demo@example.com', 1);
-- 密码为: password
-- ============================================
-- note 独立模块：笔记小程序表结构
-- ============================================
-- 小程序用户表
CREATE TABLE IF NOT EXISTS `note_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`member_id` int(11) unsigned DEFAULT NULL COMMENT '关联 tp 现有 member.userid，可为空',
`openid` varchar(64) NOT NULL DEFAULT '' COMMENT '微信小程序 openid',
`unionid` varchar(64) NOT NULL DEFAULT '' COMMENT '微信 unionid',
`session_key` varchar(128) NOT NULL DEFAULT '' COMMENT '微信 session_key',
`nickname` varchar(100) NOT NULL DEFAULT '' COMMENT '用户昵称',
`avatar_url` varchar(255) NOT NULL DEFAULT '' COMMENT '头像地址',
`mobile` varchar(20) NOT NULL DEFAULT '' COMMENT '手机号',
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态：0禁用 1正常',
`last_login_ip` varchar(64) NOT NULL DEFAULT '' COMMENT '最后登录IP',
`last_login_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '最后登录时间',
`created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
`updated_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
`deleted_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '删除时间，0表示未删除',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_openid` (`openid`),
KEY `idx_member_id` (`member_id`),
KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='note模块-小程序用户表';
-- 笔记主表
CREATE TABLE IF NOT EXISTS `note_item` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '笔记ID',
`note_user_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'note_user.id',
`title` varchar(255) NOT NULL DEFAULT '' COMMENT '标题',
`content` longtext COMMENT '用户整理后的正文',
`transcript_text` longtext COMMENT '录音转写累计文本',
`source_type` varchar(20) NOT NULL DEFAULT 'text' COMMENT '来源类型：text/audio/mix',
`status` varchar(20) NOT NULL DEFAULT 'draft' COMMENT '状态：draft/completed/archived',
`audio_duration_ms` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '录音时长（毫秒）',
`summary_status` varchar(20) NOT NULL DEFAULT 'none' COMMENT 'AI总结状态：none/pending/success/failed',
`last_transcript_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '最后一次转写更新时间',
`created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
`updated_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
`deleted_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '删除时间，0表示未删除',
PRIMARY KEY (`id`),
KEY `idx_note_user_id` (`note_user_id`),
KEY `idx_status` (`status`),
KEY `idx_summary_status` (`summary_status`),
KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='note模块-笔记主表';
-- 笔记实时转写表
CREATE TABLE IF NOT EXISTS `note_transcript` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`note_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'note_item.id',
`segment_no` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '分片序号',
`segment_text` text COMMENT '本次分片转写文本',
`full_text` longtext COMMENT '当前累计完整转写文本',
`is_final` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否最终片段：0否 1是',
`audio_duration_ms` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '当前累计录音时长',
`created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_note_id` (`note_id`),
KEY `idx_note_segment` (`note_id`, `segment_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='note模块-实时转写记录表';
-- AI 总结结果表
CREATE TABLE IF NOT EXISTS `note_ai_summary` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`note_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'note_item.id',
`summary_type` varchar(20) NOT NULL DEFAULT 'brief' COMMENT '总结类型：brief/outline/todo',
`summary_text` longtext COMMENT 'AI总结正文',
`todo_list` longtext COMMENT '待办列表，建议JSON字符串',
`keywords` longtext COMMENT '关键词列表，建议JSON字符串',
`status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT '状态：pending/success/failed',
`error_message` varchar(500) NOT NULL DEFAULT '' COMMENT '失败原因',
`created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
`updated_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `idx_note_id` (`note_id`),
KEY `idx_status` (`status`),
KEY `idx_summary_type` (`summary_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='note模块-AI总结结果表';
-- 笔记录音附件表
CREATE TABLE IF NOT EXISTS `note_audio` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`note_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'note_item.id',
`disk` varchar(30) NOT NULL DEFAULT 'public' COMMENT '存储磁盘',
`file_path` varchar(255) NOT NULL DEFAULT '' COMMENT '磁盘相对路径',
`file_url` varchar(500) NOT NULL DEFAULT '' COMMENT '公开访问地址',
`file_size` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '文件大小',
`mime_type` varchar(100) NOT NULL DEFAULT '' COMMENT '文件类型',
`duration_ms` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '录音时长',
`created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
`updated_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `idx_note_id` (`note_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='note模块-录音附件表';
-- 笔记分享表
CREATE TABLE IF NOT EXISTS `note_share` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`note_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'note_item.id',
`note_user_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'note_user.id',
`share_token` varchar(64) NOT NULL DEFAULT '' COMMENT '分享 token',
`title` varchar(255) NOT NULL DEFAULT '' COMMENT '分享标题',
`view_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '查看次数',
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态：0失效 1有效',
`expired_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '过期时间，0不过期',
`last_view_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '最后查看时间',
`created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
`updated_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_share_token` (`share_token`),
KEY `idx_note_id` (`note_id`),
KEY `idx_note_user_id` (`note_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='note模块-分享记录表';
Reference in New Issue View Git Blame Copy Permalink