laoyang/TripLog
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
TripLog/sql/schema.sql
T

268 lines
16 KiB
SQL
Raw Permalink Normal View History

安装向导：首次部署浏览器引导建库建表（批 26）
2026-09-28 20:55:16 +08:00
-- ============================================================================
-- TripLog 全量建表脚本（安装向导使用）
--
-- 来源：2026-09-28 从生产库 SHOW CREATE TABLE 导出后整理：
-- * 去掉导出文件里的 AUTO_INCREMENT=N 当前值；
-- * 全部 CREATE TABLE 加 IF NOT EXISTS（重复执行不破坏已有库）；
-- * 按「外键依赖」排好建表顺序：families → users → 字典表 → 业务表。
--
-- 由 install.php（Web 安装向导）在安装时执行；tools/migrate.php 只负责
-- 之后的**增量**迁移（见 src/Migrate.php），两者幂等、互不冲突。
-- ============================================================================
-- ---- 家庭空间（一切数据的根：users / 字典表都挂在 family 下）----
CREATE TABLE IF NOT EXISTS `families` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '家庭名称',
`default_home_location` json DEFAULT NULL COMMENT '默认家地址 {name, address, lat, lng}',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='家庭空间';
-- ---- 用户表 ----
CREATE TABLE IF NOT EXISTS `users` (
`id` bigint NOT NULL AUTO_INCREMENT,
`family_id` bigint NOT NULL COMMENT '所属家庭',
`name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '姓名',
`phone_encrypted` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'AES加密手机号',
`phone_hash` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '手机号SHA-256哈希，用于登录查找',
`id_card_encrypted` varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'AES加密身份证号',
`password_hash` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '密码哈希，仅admin/user有',
`role` enum('admin','user','guest') COLLATE utf8mb4_unicode_ci DEFAULT 'guest' COMMENT '角色',
`avatar` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '头像URL',
`status` enum('active','inactive') COLLATE utf8mb4_unicode_ci DEFAULT 'active' COMMENT '状态',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_family_role` (`family_id`,`role`),
KEY `idx_phone_hash` (`phone_hash`),
CONSTRAINT `fk_users_family` FOREIGN KEY (`family_id`) REFERENCES `families` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';
-- ---- 节点类型库 ----
CREATE TABLE IF NOT EXISTS `node_types` (
`id` bigint NOT NULL AUTO_INCREMENT,
`family_id` bigint NOT NULL,
`name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '游玩/用餐/赶路/休息/突发',
`icon` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '图标/表情',
`is_system` tinyint(1) DEFAULT '0' COMMENT '内置类型不可删除',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_family_name` (`family_id`,`name`),
CONSTRAINT `fk_node_types_family` FOREIGN KEY (`family_id`) REFERENCES `families` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='节点类型库';
-- ---- 角色标签库 ----
CREATE TABLE IF NOT EXISTS `role_tags` (
`id` bigint NOT NULL AUTO_INCREMENT,
`family_id` bigint NOT NULL,
`name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '角色名：司机/摄影担当',
`icon` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '图标/表情',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_family_name` (`family_id`,`name`),
CONSTRAINT `fk_role_tags_family` FOREIGN KEY (`family_id`) REFERENCES `families` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色标签库';
-- ---- 车辆库 ----
CREATE TABLE IF NOT EXISTS `vehicles` (
`id` bigint NOT NULL AUTO_INCREMENT,
`family_id` bigint NOT NULL,
`plate_number` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '车牌号',
`model_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '车型名称',
`vehicle_type` enum('own','rental') COLLATE utf8mb4_unicode_ci DEFAULT 'own' COMMENT '自有/租车',
`rental_company` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '租车公司',
`rental_order_no` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '租车订单号',
`is_default` tinyint(1) DEFAULT '0' COMMENT '是否为默认自有车',
`notes` text COLLATE utf8mb4_unicode_ci COMMENT '备注',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_family_type` (`family_id`,`vehicle_type`),
CONSTRAINT `fk_vehicles_family` FOREIGN KEY (`family_id`) REFERENCES `families` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='车辆库';
-- ---- 出行方式 ----
CREATE TABLE IF NOT EXISTS `travel_modes` (
`id` bigint NOT NULL AUTO_INCREMENT,
`family_id` bigint NOT NULL,
`mode_type` enum('drive_own','drive_rental','train','plane','bus','subway','taxi','bike','walk','ship','other') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '交通类型',
`vehicle_id` bigint DEFAULT NULL COMMENT '仅drive_own/drive_rental时关联vehicles',
`notes` text COLLATE utf8mb4_unicode_ci COMMENT '备注（延误等）',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_family_type` (`family_id`,`mode_type`),
KEY `fk_travel_modes_vehicle` (`vehicle_id`),
CONSTRAINT `fk_travel_modes_family` FOREIGN KEY (`family_id`) REFERENCES `families` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_travel_modes_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='出行方式';
-- ---- 活动容器 ----
CREATE TABLE IF NOT EXISTS `activities` (
`id` bigint NOT NULL AUTO_INCREMENT,
`family_id` bigint NOT NULL,
`title` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '活动主题',
`description` text COLLATE utf8mb4_unicode_ci COMMENT '活动简介/前言',
`cover_image` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '封面图URL',
`created_by` bigint NOT NULL COMMENT '创建者user_id',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_family_created` (`family_id`,`created_at`),
KEY `fk_activities_creator` (`created_by`),
CONSTRAINT `fk_activities_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
CONSTRAINT `fk_activities_family` FOREIGN KEY (`family_id`) REFERENCES `families` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='活动容器';
-- ---- 活动级人员名单 ----
CREATE TABLE IF NOT EXISTS `activity_members` (
`id` bigint NOT NULL AUTO_INCREMENT,
`activity_id` bigint NOT NULL,
`user_id` bigint NOT NULL,
`permission` enum('owner','editor','viewer') COLLATE utf8mb4_unicode_ci DEFAULT 'viewer' COMMENT '权限',
`joined_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '加入时间',
`left_at` datetime DEFAULT NULL COMMENT '离开时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_activity_user` (`activity_id`,`user_id`),
KEY `idx_activity` (`activity_id`),
KEY `fk_activity_members_user` (`user_id`),
CONSTRAINT `fk_activity_members_activity` FOREIGN KEY (`activity_id`) REFERENCES `activities` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_activity_members_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='活动级人员名单';
-- ---- 行程节点（计划/执行双轨）----
CREATE TABLE IF NOT EXISTS `trip_nodes` (
`id` bigint NOT NULL AUTO_INCREMENT,
`activity_id` bigint NOT NULL,
`node_type_id` bigint NOT NULL COMMENT '关联node_types',
`node_category` enum('plan','reality') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'plan=计划, reality=执行',
`original_plan_node_id` bigint DEFAULT NULL COMMENT '执行节点克隆自哪个计划节点（空=临时新增）',
`start_time` datetime NOT NULL COMMENT '开始时间',
`end_time` datetime NOT NULL COMMENT '结束时间',
`duration_minutes` int DEFAULT NULL COMMENT '持续分钟数',
`start_location` json DEFAULT NULL COMMENT '位置信息',
`end_location` json DEFAULT NULL COMMENT '位置信息',
`title` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '节点标题',
`description` text COLLATE utf8mb4_unicode_ci COMMENT '详细文字描述',
`vehicle_id` bigint DEFAULT NULL COMMENT '关联vehicles（仅自驾）',
`travel_mode_id` bigint DEFAULT NULL COMMENT '关联travel_modes（赶路节点必填）',
`status` enum('planned','ongoing','completed','cancelled') COLLATE utf8mb4_unicode_ci DEFAULT 'planned',
`cancel_reason` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '取消原因',
`sort_order` int DEFAULT '0' COMMENT '手动排序',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`line_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '线路名称（如京沪高铁、地铁10号线）',
`carrier` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '承运公司（如中国铁路、滴滴）',
`trip_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '车次/航班号/线路号',
`seat_info` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '座位号/车厢',
`gate` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '登机口/站台',
PRIMARY KEY (`id`),
KEY `idx_activity_category` (`activity_id`,`node_category`,`start_time`),
KEY `idx_original` (`original_plan_node_id`),
KEY `idx_travel_mode` (`travel_mode_id`),
KEY `fk_trip_nodes_type` (`node_type_id`),
KEY `fk_trip_nodes_vehicle` (`vehicle_id`),
CONSTRAINT `fk_trip_nodes_activity` FOREIGN KEY (`activity_id`) REFERENCES `activities` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_trip_nodes_original` FOREIGN KEY (`original_plan_node_id`) REFERENCES `trip_nodes` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_trip_nodes_travel` FOREIGN KEY (`travel_mode_id`) REFERENCES `travel_modes` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_trip_nodes_type` FOREIGN KEY (`node_type_id`) REFERENCES `node_types` (`id`),
CONSTRAINT `fk_trip_nodes_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='行程节点（计划/执行双轨）';
-- ---- 节点级参与人员 ----
CREATE TABLE IF NOT EXISTS `node_members` (
`id` bigint NOT NULL AUTO_INCREMENT,
`trip_node_id` bigint NOT NULL,
`user_id` bigint NOT NULL,
`role_tag_ids` json DEFAULT NULL COMMENT '[12, 34] 角色ID数组',
`emotion` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '情绪表情（仅执行节点）',
`check_in_time` datetime DEFAULT NULL COMMENT '签到时间',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_node_user` (`trip_node_id`,`user_id`),
KEY `idx_node` (`trip_node_id`),
KEY `fk_node_members_user` (`user_id`),
CONSTRAINT `fk_node_members_node` FOREIGN KEY (`trip_node_id`) REFERENCES `trip_nodes` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_node_members_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='节点级参与人员';
-- ---- 花销记录 ----
CREATE TABLE IF NOT EXISTS `expenses` (
`id` bigint NOT NULL AUTO_INCREMENT,
`trip_node_id` bigint NOT NULL,
`payer_user_id` bigint NOT NULL COMMENT '付款人',
`currency` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT 'CNY' COMMENT '币种',
`total_amount` decimal(12,2) NOT NULL COMMENT '总金额',
`category` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '餐饮/门票/交通/购物/其他',
`description` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '消费描述',
`is_budget` tinyint(1) DEFAULT '0' COMMENT 'TRUE=预算, FALSE=实际',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_node_budget` (`trip_node_id`,`is_budget`),
KEY `fk_expenses_payer` (`payer_user_id`),
CONSTRAINT `fk_expenses_node` FOREIGN KEY (`trip_node_id`) REFERENCES `trip_nodes` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_expenses_payer` FOREIGN KEY (`payer_user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='花销记录';
-- ---- 花销分摊明细 ----
CREATE TABLE IF NOT EXISTS `expense_splits` (
`id` bigint NOT NULL AUTO_INCREMENT,
`expense_id` bigint NOT NULL,
`user_id` bigint NOT NULL COMMENT '分摊对象（为谁花的）',
`share_amount` decimal(12,2) NOT NULL COMMENT '承担金额',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_expense` (`expense_id`),
KEY `fk_expense_splits_user` (`user_id`),
CONSTRAINT `fk_expense_splits_expense` FOREIGN KEY (`expense_id`) REFERENCES `expenses` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_expense_splits_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='花销分摊明细';
-- ---- 评论表 ----
CREATE TABLE IF NOT EXISTS `comments` (
`id` bigint NOT NULL AUTO_INCREMENT,
`author_id` bigint NOT NULL,
`target_type` enum('activity','trip_node') COLLATE utf8mb4_unicode_ci NOT NULL,
`target_id` bigint NOT NULL,
`parent_id` bigint DEFAULT NULL COMMENT '父评论ID（楼中楼）',
`content` text COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_target` (`target_type`,`target_id`,`created_at`),
KEY `fk_comments_author` (`author_id`),
KEY `fk_comments_parent` (`parent_id`),
CONSTRAINT `fk_comments_author` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`),
CONSTRAINT `fk_comments_parent` FOREIGN KEY (`parent_id`) REFERENCES `comments` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='评论表';
-- ---- 统一附件表 ----
CREATE TABLE IF NOT EXISTS `attachments` (
`id` bigint NOT NULL AUTO_INCREMENT,
`target_type` enum('activity','trip_node','comment') COLLATE utf8mb4_unicode_ci NOT NULL,
`target_id` bigint NOT NULL COMMENT '对应表的ID',
`uploader_id` bigint NOT NULL,
`file_url` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
`file_name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`sort_order` int DEFAULT '0',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`storage` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'local' COMMENT 'local|oss',
`object_key` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'OSS 对象键，或本地相对路径',
`mime_type` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`size_bytes` bigint DEFAULT NULL,
`width` int DEFAULT NULL COMMENT '图片宽（非图片为空）',
`height` int DEFAULT NULL COMMENT '图片高（非图片为空）',
`is_image` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_target` (`target_type`,`target_id`),
KEY `fk_attachments_uploader` (`uploader_id`),
KEY `idx_storage` (`storage`),
CONSTRAINT `fk_attachments_uploader` FOREIGN KEY (`uploader_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='统一附件表';
-- ---- 站点级键值设置 ----
CREATE TABLE IF NOT EXISTS `app_settings` (
`setting_key` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
`setting_value` text COLLATE utf8mb4_unicode_ci,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='站点级键值设置';
Reference in New Issue Copy Permalink