Skip to content

sql.md

Latest commit

 

History

History
244 lines (133 loc) · 6.49 KB

sql.md

File metadata and controls

244 lines (133 loc) · 6.49 KB
Raw

数据库表设计

docker exec -it nova.trip.pgsql psql -U postgres
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

用户表（users）

  • id (bigint): 主键

  • username (varchar): 用户名（唯一）

  • email (varchar): 用户邮箱

  • password_hash (text): 密码哈希（可为空）

  • created_at (timestamptz) / updated_at (timestamptz) / created_by (varchar) / updated_by (varchar): 元信息

索引：

  • idx_users_username: 保证 username 唯一

身份关联表（identities）

  • id (bigint): 主键

  • user_id (bigint): 外键关联 users(id)

  • user_role (user_type): 枚举（client/driver/admin/support），表示用户类型

  • identity_id (bigint): 外键关联对应角色表的主键（如driver对应drivers(id)）

  • created_at (timestamptz) / updated_at (timestamptz) / created_by (varchar) / updated_by (varchar): 元信息

索引：

  • idx_identities_user_id: 加速按用户ID查询

  • idx_identities_user_role: 加速按角色查询

  • idx_identities_identity_id: 加速按身份ID查询

  • uk_identities_user_role_identity: 唯一约束，确保一个用户在同一角色下只有一个身份记录

客户表（clients）

  • id (bigint): 主键

  • full_name (varchar): 客户姓名

  • phone (varchar): 联系电话

  • from_field (varchar): 客户来源国家/地区

  • notes (text): 备注或特殊要求

  • created_at (timestamptz) / updated_at (timestamptz) / created_by (varchar) / updated_by (varchar): 元信息

  • deleted (boolean): 逻辑删除标记，默认为 false

索引：

  • idx_clients_phone: 加速按手机号查询

司机表（drivers）

  • id (bigint): 主键

  • full_name (varchar): 司机姓名

  • phone (varchar): 电话

  • credentialed (boolean): 布尔值，司机是否有资质

  • work_mode (driver_work_mode): 工作方式枚举（主要是 full_time / crowdsource，全职 / 众包）

  • status (driver_status): 枚举（active / on_leave / resigned）

  • languages (text[]): 语言列表，例如 {"Chinese","English"}

  • current_city (varchar): 当前所在城市

  • notes (text): 备注

  • created_at (timestamptz) / updated_at (timestamptz) / created_by (varchar) / updated_by (varchar): 元信息

  • deleted (boolean): 逻辑删除标记，默认为 false

索引：

  • idx_drivers_languages_gin: 例如查找会英语的司机

  • idx_drivers_status: 按状态快速筛选可用司机

车辆表（vehicles）

  • id (bigint): 主键

  • license_plate (varchar): 车牌号（唯一索引）

  • model (varchar): 车型/型号（海狮、阿尔法）

  • is_licensed (boolean): 车辆是否为有牌照/正规（true）或黑车（false）

  • max_seats (integer) / min_seats (integer): 可载人数范围

  • vehicle_type (varchar): 车型类别（轿车、SUV、MPV 等）

  • base_price (numeric): 每辆车的基础报价/计价参考

  • status (varchar): 车辆当前状态（检修、可用等）

  • current_city (varchar): 当前所在地

  • notes (text): 备注

  • current_driver_id (bigint): 当前绑定司机（可为空）

  • created_at (timestamptz) / updated_at (timestamptz) / created_by (varchar) / updated_by (varchar): 元信息

  • deleted (boolean): 逻辑删除标记，默认为 false

索引：

  • idx_vehicles_license_plate: 唯一车牌索引

  • idx_vehicles_max_seats: 按座位数筛选车辆

订单表（orders）

  • id (bigint): 主键

  • start_time (timestamptz) / end_time (timestamptz): 整个订单周期或服务时间范围

  • currency (char): 货币代码（比如 CNY、JPY）

  • special_requirements (text[]): 文本数组，记录客户特殊需求

  • seats_required (integer): 要求的最大座位数

  • vehicle_service_category (vehicle_service_type): 枚举（licensed/unlicensed），表示白车/黑车等业务分类

  • preferred_vehicle_model (varchar): 客户偏好的车型

  • preferred_driver_language (varchar): 客户偏好的司机语言

  • status (order_status): 订单状态枚举（pending/confirmed/in_progress/completed/cancelled）

  • committing_client_name (varchar): 请求创建订单的客户姓名

  • order_source (varchar): 订单来源（合作旅行社名字或单源）

  • client_region (varchar): 客户所在地区

  • created_at (timestamptz) / updated_at (timestamptz) / created_by (varchar) / updated_by (varchar): 元信息

  • client_ids (bigint[]): 客户 ID 的数组

  • deleted (boolean): 逻辑删除标记，默认为 false

索引：

  • idx_orders_start_time / idx_orders_end_time: 按时间范围查询

  • idx_orders_status: 按状态快速筛选

  • idx_orders_client_ids_gin: 查找属于某客户的订单

  • idx_orders_special_requirements_gin: 模糊匹配索引

Itineraries

  • id (bigint): primary key
  • driver_id (bigint): FK drivers(id)
  • order_id (bigint): FK orders(id); itinerary amounts use the order currency
  • vehicle_id (bigint): FK vehicles(id)
  • start_time / end_time (timestamptz): schedule window
  • price (numeric): income for this itinerary (order currency)
  • task_type (varchar): e.g. pickup/charter/transfer/other
  • origin (text) / destination (text): locations
  • notes (text)
  • created_at / updated_at / created_by / updated_by
  • deleted (boolean): soft delete flag, default false

Indexes:

  • idx_itineraries_driver_id
  • idx_itineraries_order_id
  • idx_itineraries_start_time

行程开支表（trip_expenses）

  • id (bigint): 主键

  • amount (numeric): 开销金额

  • currency (char): 货币代码（比如 CNY、JPY）

  • expense_type (varchar): 开支类型（燃油、过路费、餐费、票据等）

  • driver_id (bigint): 操作或报销的司机（可为空）

  • order_id (bigint): 关联订单

  • itinerary_id (bigint): 关联行程

  • receipt_urls (text[]): 凭证链接数组

  • notes (text): 备注

  • created_at (timestamptz) / updated_at (timestamptz) / created_by (varchar) / updated_by (varchar): 元信息

  • deleted (boolean): 逻辑删除标记，默认为 false

索引：

  • idx_trip_expenses_order_id

  • idx_trip_expenses_itinerary_id

  • idx_trip_expenses_driver_id

汇率表（currency_rates）

  • id (bigint): 主键

  • currency1 (char): 货币代码1（比如 CNY、JPY）

  • currency2 (char): 货币代码2（比如 CNY、JPY）

  • rate (numeric): 货币1到货币2的汇率比值

  • year_and_month (char): 年月，格式为 YYYYMM（例如 202512）

  • created_at (timestamptz) / updated_at (timestamptz) / created_by (varchar) / updated_by (varchar): 元信息

索引：

  • idx_currency_rates_unique: 联合唯一索引，确保货币对和年月的唯一性，使用LEAST和GREATEST函数避免重复存储