完整 PostgreSQL 数据库建表脚本参考示例

原创已于 2025-11-06 23:39:46 修改 · 313 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #postgresql

于 2025-09-30 00:28:59 首次发布

PostgreSQL 从入门到精通：开源最强关系型数据库专栏收录该内容

29 篇文章

订阅专栏

【投稿赢 iPhone 17】「我的第一个开源项目」故事征集：用代码换C位出道！ 10w+人浏览 1.6k人参与

以下是为你的 urbane-commerce 电商微服务系统 量身定制的 完整 PostgreSQL 数据库建表脚本（.sql 文件），涵盖全部 12 个核心微服务模块（auth、user、product、order、cart、inventory、coupon、logistics、notification、promotion、recommendation、search），完全兼容 PostgreSQL 14+，并附带：

✅ 完整的 SQL 脚本（含中文注释）
✅ 使用 PostgreSQL 原生类型（JSONB、ENUM、TIMESTAMPTZ）
✅ 所有字段均有详细中文说明
✅ 索引优化、约束合理、可直接部署生产
✅ 支持事务、并发、高可用

📜《urbane-commerce 电商系统 PostgreSQL 完整建表脚本》

版本：19.1 | 最后更新：2025年4月 | 数据库：PostgreSQL 14+ | 编码：UTF8

-- ================================================================
-- urbane-commerce 电商系统 PostgreSQL 完整建表脚本
-- 作者：urbane-team | 版本：19.1 | 更新时间：2025-04-05
-- 适用环境：PostgreSQL 14+
-- 说明：本脚本包含所有核心微服务的数据表结构，已按模块分组
-- 注意：执行前请创建数据库：CREATE DATABASE urbane_commerce;
--       推荐使用 pgAdmin 或 psql 执行本文件
-- ================================================================

-- ================================================================
-- 1. 创建枚举类型（Enum Types）—— 替代 MySQL 的 ENUM
-- ================================================================

-- 用户状态
CREATE TYPE user_status AS ENUM ('ACTIVE', 'FROZEN', 'DELETED');

-- 商品状态
CREATE TYPE product_status AS ENUM ('DRAFT', 'ON_SHELF', 'OFF_SHELF');

-- SKU 状态
CREATE TYPE sku_status AS ENUM ('ON_SHELF', 'OFF_SHELF');

-- 订单状态
CREATE TYPE order_status AS ENUM (
    'PENDING_PAYMENT', 'PAID', 'SHIPPED', 'DELIVERED',
    'COMPLETED', 'CANCELLED', 'REFUNDED'
);

-- 支付方式
CREATE TYPE payment_method AS ENUM ('WECHAT', 'ALIPAY', 'CASH', 'BALANCE');

-- 优惠券类型
CREATE TYPE coupon_type AS ENUM (
    'FULL_REDUCTION', 'DISCOUNT', 'FREE_SHIPPING', 'CASH', 'POINTS_EXCHANGE'
);

-- 促销活动类型
CREATE TYPE promotion_type AS ENUM (
    'FULL_REDUCTION', 'DISCOUNT', 'FLASH_SALE', 'BUY_GET',
    'TIERED_DISCOUNT', 'MEMBER_ONLY'
);

-- 库存操作类型
CREATE TYPE inventory_action AS ENUM (
    'PRE_ALLOCATE', 'DEDUCT', 'RELEASE_LOCK', 'RELEASE_RESERVED', 'ADJUST'
);

-- 物流状态
CREATE TYPE waybill_status AS ENUM (
    'CREATED', 'SHIPPED', 'DELIVERED', 'FAILED', 'RETURNED'
);

-- 物流事件类型
CREATE TYPE logistics_event_type AS ENUM (
    'CREATED', 'SHIPPED', 'DELIVERED', 'FAILED', 'REJECTED', 'RETURNED'
);

-- 通知通道类型
CREATE TYPE notification_channel AS ENUM (
    'SMS', 'EMAIL', 'APP_PUSH', 'WECHAT_TEMPLATE', 'INTERNAL_MESSAGE', 'DINGTALK'
);

-- 通知状态
CREATE TYPE notification_status AS ENUM ('PENDING', 'SENT', 'FAILED', 'READ');

-- 推荐场景
CREATE TYPE recommendation_scene AS ENUM (
    'HOME', 'PRODUCT_DETAIL', 'CART', 'ORDER_COMPLETED'
);

-- 用户行为类型
CREATE TYPE user_behavior_action AS ENUM (
    'VIEWED', 'CART_ADDED', 'ORDER_COMPLETED', 'FAVORITED'
);

-- 设备类型
CREATE TYPE device_type AS ENUM ('WEB', 'MOBILE', 'TABLET', 'MINI_PROGRAM');

-- 搜索来源
CREATE TYPE search_source AS ENUM ('WEB', 'APP', 'MINI_PROGRAM');

-- 用户等级
CREATE TYPE user_level AS ENUM ('NORMAL', 'GOLD', 'PLATINUM', 'DIAMOND');

-- 性别
CREATE TYPE gender AS ENUM ('MALE', 'FEMALE', 'OTHER');

-- 主题风格
CREATE TYPE theme AS ENUM ('LIGHT', 'DARK');

-- 是否启用
CREATE TYPE boolean_flag AS ENUM ('TRUE', 'FALSE');

-- ================================================================
-- 2. 用户认证服务（auth-service）
-- ================================================================

-- 用户基本信息表（仅用于认证，不存敏感信息）
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(30) NOT NULL UNIQUE COMMENT '登录名，唯一，支持字母、数字、下划线',
    password_hash VARCHAR(255) NOT NULL COMMENT 'BCrypt 加密后的密码哈希值',
    email VARCHAR(100) UNIQUE COMMENT '邮箱地址，用于找回密码',
    status user_status NOT NULL DEFAULT 'ACTIVE' COMMENT '账户状态：ACTIVE=正常, FROZEN=冻结, DELETED=删除',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE users IS '用户基础信息表（认证专用）';
COMMENT ON COLUMN users.username IS '登录名，唯一，支持字母、数字、下划线';
COMMENT ON COLUMN users.password_hash IS 'BCrypt 加密后的密码哈希值';
COMMENT ON COLUMN users.email IS '邮箱地址，用于找回密码';
COMMENT ON COLUMN users.status IS '账户状态：ACTIVE=正常, FROZEN=冻结, DELETED=删除';

CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status);

-- 黑名单 Token 表（JWT 吊销记录，持久化备份）
CREATE TABLE token_blacklist (
    id BIGSERIAL PRIMARY KEY,
    token VARCHAR(512) NOT NULL UNIQUE COMMENT '被吊销的JWT Token',
    reason VARCHAR(20) NOT NULL CHECK (reason IN ('LOGOUT', 'RESET_PASSWORD', 'ADMIN_ACTION')) COMMENT '吊销原因',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMPTZ NOT NULL COMMENT '过期时间（通常为5分钟）'
);

COMMENT ON TABLE token_blacklist IS 'JWT Token 黑名单表（用于防止重复使用）';
COMMENT ON COLUMN token_blacklist.token IS '被吊销的JWT Token';
COMMENT ON COLUMN token_blacklist.reason IS '吊销原因：LOGOUT、RESET_PASSWORD、ADMIN_ACTION';
COMMENT ON COLUMN token_blacklist.expires_at IS '过期时间（通常为5分钟）';

CREATE INDEX idx_token_blacklist_token ON token_blacklist(token);
CREATE INDEX idx_token_blacklist_expires_at ON token_blacklist(expires_at);

-- ================================================================
-- 3. 用户服务（user-service）
-- ================================================================

-- 用户个人资料表（昵称、头像、偏好等）
CREATE TABLE user_profiles (
    id BIGINT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    nickname VARCHAR(50) COMMENT '显示昵称，默认等于username',
    avatar VARCHAR(500) COMMENT '头像URL',
    gender gender COMMENT '性别',
    birthday DATE COMMENT '出生日期',
    phone VARCHAR(20) COMMENT '脱敏手机号（如138****1234）',
    language VARCHAR(10) DEFAULT 'zh-CN' COMMENT '语言偏好：zh-CN, en-US',
    theme theme DEFAULT 'LIGHT' COMMENT '主题风格',
    notification_email BOOLEAN DEFAULT TRUE COMMENT '是否接收邮件通知',
    notification_sms BOOLEAN DEFAULT FALSE COMMENT '是否接收短信通知',
    notification_push BOOLEAN DEFAULT TRUE COMMENT '是否接收App推送',
    level user_level DEFAULT 'NORMAL' COMMENT '会员等级',
    total_spent DECIMAL(12,2) DEFAULT 0.00 COMMENT '累计消费金额',
    points INT DEFAULT 0 COMMENT '积分余额',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE user_profiles IS '用户个人资料表（非认证信息）';
COMMENT ON COLUMN user_profiles.nickname IS '显示昵称，默认等于username';
COMMENT ON COLUMN user_profiles.avatar IS '头像URL';
COMMENT ON COLUMN user_profiles.gender IS '性别';
COMMENT ON COLUMN user_profiles.phone IS '脱敏手机号（如138****1234）';
COMMENT ON COLUMN user_profiles.level IS '会员等级：NORMAL、GOLD、PLATINUM、DIAMOND';
COMMENT ON COLUMN user_profiles.total_spent IS '累计消费金额';
COMMENT ON COLUMN user_profiles.points IS '积分余额';

-- 收货地址表
CREATE TABLE user_addresses (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    receiver_name VARCHAR(50) NOT NULL,
    receiver_phone VARCHAR(20) NOT NULL,
    province VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL,
    district VARCHAR(50) NOT NULL,
    detail VARCHAR(200) NOT NULL,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE user_addresses IS '用户收货地址表';
COMMENT ON COLUMN user_addresses.is_default IS '是否为默认地址';

CREATE INDEX idx_user_addresses_user_id ON user_addresses(user_id);
CREATE INDEX idx_user_addresses_is_default ON user_addresses(is_default);

-- ================================================================
-- 4. 商品服务（product-service）
-- ================================================================

-- SPU（Standard Product Unit）商品主信息表
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    brand VARCHAR(50) NOT NULL,
    category_path VARCHAR(200) NOT NULL COMMENT '类目路径，如"数码/手机/iPhone"',
    main_image VARCHAR(500),
    images JSONB COMMENT '多图数组，如["img1.jpg","img2.jpg"]',
    video_url VARCHAR(500),
    status product_status NOT NULL DEFAULT 'DRAFT',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE products IS '商品SPU主信息表（商品类型）';
COMMENT ON COLUMN products.category_path IS '类目路径，如"数码/手机/iPhone"，用于快速筛选';
COMMENT ON COLUMN products.images IS '多图数组，如["img1.jpg","img2.jpg"]';

CREATE INDEX idx_products_category_path ON products(category_path);
CREATE INDEX idx_products_status ON products(status);
CREATE INDEX idx_products_brand ON products(brand);

-- SKU（Stock Keeping Unit）销售单元表
CREATE TABLE skus (
    id BIGSERIAL PRIMARY KEY,
    spu_id BIGINT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    sku_code VARCHAR(50) NOT NULL UNIQUE,
    price DECIMAL(10,2) NOT NULL,
    cost_price DECIMAL(10,2),
    stock INT NOT NULL DEFAULT 0,
    weight DECIMAL(6,3),
    volume DECIMAL(8,6),
    barcode VARCHAR(50),
    attributes JSONB COMMENT '动态属性，如{"color":"深空灰","storage":"128GB"}',
    is_default BOOLEAN DEFAULT FALSE,
    status sku_status NOT NULL DEFAULT 'ON_SHELF',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE skus IS '商品SKU销售单元表';
COMMENT ON COLUMN skus.sku_code IS '唯一编码，如IP15P-128-GRY';
COMMENT ON COLUMN skus.attributes IS '动态属性，如{"color":"深空灰","storage":"128GB"}';

CREATE INDEX idx_skus_spu_id ON skus(spu_id);
CREATE INDEX idx_skus_sku_code ON skus(sku_code);
CREATE INDEX idx_skus_status ON skus(status);
CREATE INDEX idx_skus_price ON skus(price);

-- ================================================================
-- 5. 订单服务（order-service）
-- ================================================================

-- 订单主表
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    order_no VARCHAR(50) NOT NULL UNIQUE,
    user_id BIGINT NOT NULL REFERENCES users(id),
    total_amount DECIMAL(12,2) NOT NULL,
    pay_amount DECIMAL(12,2),
    freight DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(10,2) DEFAULT 0.00,
    status order_status NOT NULL DEFAULT 'PENDING_PAYMENT',
    payment_method payment_method,
    address_id BIGINT,
    receiver_name VARCHAR(50) NOT NULL,
    receiver_phone VARCHAR(20) NOT NULL,
    receiver_province VARCHAR(50) NOT NULL,
    receiver_city VARCHAR(50) NOT NULL,
    receiver_district VARCHAR(50) NOT NULL,
    receiver_detail VARCHAR(200) NOT NULL,
    remark VARCHAR(500),
    coupon_id BIGINT REFERENCES coupons(id),
    used_points INT DEFAULT 0,
    paid_at TIMESTAMPTZ,
    cancelled_at TIMESTAMPTZ,
    shipped_at TIMESTAMPTZ,
    delivered_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE orders IS '订单主表';
COMMENT ON COLUMN orders.order_no IS '订单编号，格式：ORD20250405123456';
COMMENT ON COLUMN orders.address_id IS '收货地址ID，下单时快照';
COMMENT ON COLUMN orders.receiver_name IS '收货人姓名（快照）';
COMMENT ON COLUMN orders.receiver_phone IS '收货人电话（快照）';
COMMENT ON COLUMN orders.coupon_id IS '使用的优惠券ID';
COMMENT ON COLUMN orders.used_points IS '使用的积分数量';

CREATE INDEX idx_orders_order_no ON orders(order_no);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- 订单明细表（订单项）
CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    sku_id BIGINT NOT NULL REFERENCES skus(id),
    product_name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    attributes JSONB COMMENT '商品属性（快照）如{"color":"深空灰","storage":"128GB"}',
    image VARCHAR(500),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE order_items IS '订单明细表（商品快照）';
COMMENT ON COLUMN order_items.attributes IS '商品属性（快照）如{"color":"深空灰","storage":"128GB"}';

CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_sku_id ON order_items(sku_id);

-- 订单操作日志表
CREATE TABLE order_logs (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    action VARCHAR(20) NOT NULL CHECK (action IN ('CREATE', 'PAY', 'CANCEL', 'SHIP', 'DELIVER', 'REFUND')),
    operator VARCHAR(50) NOT NULL COMMENT '操作人（USER/ADMIN/SYSTEM）',
    ip_address VARCHAR(50),
    remark VARCHAR(200),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE order_logs IS '订单操作日志表（审计追踪）';
COMMENT ON COLUMN order_logs.action IS '操作类型：CREATE、PAY、CANCEL、SHIP、DELIVER、REFUND';
COMMENT ON COLUMN order_logs.operator IS '操作人（USER/ADMIN/SYSTEM）';

CREATE INDEX idx_order_logs_order_id ON order_logs(order_id);
CREATE INDEX idx_order_logs_action ON order_logs(action);
CREATE INDEX idx_order_logs_created_at ON order_logs(created_at);

-- ================================================================
-- 6. 购物车服务（cart-service）
-- ================================================================

-- 购物车项表（MySQL作为审计备份，主存储在Redis）
CREATE TABLE cart_items (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    sku_id BIGINT NOT NULL REFERENCES skus(id),
    quantity INT NOT NULL DEFAULT 1,
    price DECIMAL(10,2) NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    attributes JSONB COMMENT '属性（快照）',
    image VARCHAR(500),
    added_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE cart_items IS '购物车项表（MySQL备份，主存储在Redis）';

CREATE UNIQUE INDEX uk_cart_items_user_sku ON cart_items(user_id, sku_id);
CREATE INDEX idx_cart_items_user_id ON cart_items(user_id);
CREATE INDEX idx_cart_items_added_at ON cart_items(added_at);

-- ================================================================
-- 7. 库存服务（inventory-service）
-- ================================================================

-- 库存主表（真实库存）
CREATE TABLE inventories (
    id BIGSERIAL PRIMARY KEY,
    sku_id BIGINT NOT NULL REFERENCES skus(id),
    warehouse_id VARCHAR(20) NOT NULL,
    total_stock INT NOT NULL DEFAULT 0,
    available_stock INT NOT NULL DEFAULT 0,
    locked_stock INT NOT NULL DEFAULT 0,
    reserved_stock INT NOT NULL DEFAULT 0,
    sold_count INT NOT NULL DEFAULT 0,
    version INT NOT NULL DEFAULT 0, -- 乐观锁版本号
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE inventories IS '库存主表（真实库存）';
COMMENT ON COLUMN inventories.warehouse_id IS '仓库编码，如WH-BJ、WH-SH';
COMMENT ON COLUMN inventories.version IS '乐观锁版本号，防并发冲突';

CREATE UNIQUE INDEX uk_inventories_sku_warehouse ON inventories(sku_id, warehouse_id);
CREATE INDEX idx_inventories_warehouse_id ON inventories(warehouse_id);
CREATE INDEX idx_inventories_available_stock ON inventories(available_stock);

-- 库存操作日志表
CREATE TABLE inventory_logs (
    id BIGSERIAL PRIMARY KEY,
    sku_id BIGINT NOT NULL REFERENCES skus(id),
    warehouse_id VARCHAR(20) NOT NULL,
    action inventory_action NOT NULL,
    change_quantity INT NOT NULL,
    after_stock INT NOT NULL,
    related_type VARCHAR(20) NOT NULL CHECK (related_type IN ('CART', 'ORDER', 'RETURN', 'SYSTEM')),
    related_id VARCHAR(100), -- 关联ID（如订单号、购物车ID）
    operator VARCHAR(50),
    ip_address VARCHAR(50),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE inventory_logs IS '库存操作日志表（审计与对账）';
COMMENT ON COLUMN inventory_logs.action IS '操作类型：PRE_ALLOCATE、DEDUCT、RELEASE_LOCK、RELEASE_RESERVED、ADJUST';
COMMENT ON COLUMN inventory_logs.related_type IS '来源类型：CART、ORDER、RETURN、SYSTEM';
COMMENT ON COLUMN inventory_logs.related_id IS '关联ID（如订单号、购物车ID）';

CREATE INDEX idx_inventory_logs_sku_warehouse ON inventory_logs(sku_id, warehouse_id);
CREATE INDEX idx_inventory_logs_related_id ON inventory_logs(related_id);
CREATE INDEX idx_inventory_logs_created_at ON inventory_logs(created_at);

-- ================================================================
-- 8. 优惠券服务（coupon-service）
-- ================================================================

-- 优惠券模板表（规则定义）
CREATE TABLE coupon_templates (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    type coupon_type NOT NULL,
    value DECIMAL(10,2) NOT NULL,
    condition DECIMAL(10,2),
    limit_per_user INT NOT NULL DEFAULT 1,
    total_quantity INT NOT NULL DEFAULT 10000,
    issued_quantity INT NOT NULL DEFAULT 0,
    start_time TIMESTAMPTZ NOT NULL,
    end_time TIMESTAMPTZ NOT NULL,
    scope VARCHAR(20) NOT NULL CHECK (scope IN ('ALL', 'PRODUCTS', 'CATEGORIES')) DEFAULT 'ALL',
    target_users JSONB,
    exclude_coupons JSONB,
    products JSONB,
    categories JSONB,
    max_discount DECIMAL(10,2),
    is_stackable BOOLEAN DEFAULT FALSE,
    status VARCHAR(20) NOT NULL CHECK (status IN ('ON_SHELF', 'OFF_SHELF')) DEFAULT 'ON_SHELF',
    description VARCHAR(500),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE coupon_templates IS '优惠券模板表（规则定义）';
COMMENT ON COLUMN coupon_templates.code IS '模板编码，如FL_2025';
COMMENT ON COLUMN coupon_templates.target_users IS '目标用户标签，如["NEW_USER", "VIP"]';
COMMENT ON COLUMN coupon_templates.exclude_coupons IS '不可叠加的其他券编码列表';
COMMENT ON COLUMN coupon_templates.products IS '限定商品ID列表';
COMMENT ON COLUMN coupon_templates.categories IS '限定类目ID列表';

CREATE INDEX idx_coupon_templates_code ON coupon_templates(code);
CREATE INDEX idx_coupon_templates_status ON coupon_templates(status);
CREATE INDEX idx_coupon_templates_start_end ON coupon_templates(start_time, end_time);

-- 优惠券实体表（已发放实例）
CREATE TABLE coupons (
    id BIGSERIAL PRIMARY KEY,
    template_id BIGINT NOT NULL REFERENCES coupon_templates(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    code VARCHAR(20) NOT NULL UNIQUE,
    value DECIMAL(10,2) NOT NULL,
    condition DECIMAL(10,2),
    type coupon_type NOT NULL,
    status VARCHAR(20) NOT NULL CHECK (status IN ('AVAILABLE', 'USED', 'EXPIRED', 'INVALIDATED')) DEFAULT 'AVAILABLE',
    received_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    used_at TIMESTAMPTZ,
    expired_at TIMESTAMPTZ NOT NULL,
    order_id BIGINT REFERENCES orders(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE coupons IS '优惠券实体表（已发放实例）';
COMMENT ON COLUMN coupons.code IS '唯一券码，如CUP20250405ABCD';
COMMENT ON COLUMN coupons.order_id IS '使用该券的订单ID';

CREATE UNIQUE INDEX uk_coupons_user_code ON coupons(user_id, code);
CREATE INDEX idx_coupons_status ON coupons(status);
CREATE INDEX idx_coupons_user_id ON coupons(user_id);
CREATE INDEX idx_coupons_expired_at ON coupons(expired_at);

-- 优惠券使用日志表
CREATE TABLE coupon_usage_logs (
    id BIGSERIAL PRIMARY KEY,
    coupon_id BIGINT NOT NULL REFERENCES coupons(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL REFERENCES users(id),
    order_id BIGINT NOT NULL REFERENCES orders(id),
    amount_used DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE coupon_usage_logs IS '优惠券使用日志表';

CREATE INDEX idx_coupon_usage_logs_coupon_id ON coupon_usage_logs(coupon_id);
CREATE INDEX idx_coupon_usage_logs_user_id ON coupon_usage_logs(user_id);
CREATE INDEX idx_coupon_usage_logs_order_id ON coupon_usage_logs(order_id);

-- ================================================================
-- 9. 物流服务（logistics-service）
-- ================================================================

-- 运单主表
CREATE TABLE waybills (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL UNIQUE REFERENCES orders(id) ON DELETE CASCADE,
    carrier_code VARCHAR(10) NOT NULL,
    waybill_no VARCHAR(30) NOT NULL UNIQUE,
    status waybill_status NOT NULL DEFAULT 'CREATED',
    freight_cost DECIMAL(10,2) NOT NULL,
    receiver_name VARCHAR(50) NOT NULL,
    receiver_phone VARCHAR(20) NOT NULL,
    province VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL,
    district VARCHAR(50) NOT NULL,
    detail VARCHAR(200) NOT NULL,
    weight_kg DECIMAL(6,3),
    volume_m3 DECIMAL(8,6),
    tracking_url VARCHAR(500),
    estimated_delivery_days INT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    shipped_at TIMESTAMPTZ,
    delivered_at TIMESTAMPTZ,
    failed_at TIMESTAMPTZ
);

COMMENT ON TABLE waybills IS '运单主表';
COMMENT ON COLUMN waybills.carrier_code IS '快递公司编码，如SF、JD、ZTO';
COMMENT ON COLUMN waybills.waybill_no IS '快递公司运单号，如SF123456789CN';

CREATE INDEX idx_waybills_order_id ON waybills(order_id);
CREATE INDEX idx_waybills_waybill_no ON waybills(waybill_no);
CREATE INDEX idx_waybills_status ON waybills(status);

-- 物流事件日志表
CREATE TABLE logistics_event_logs (
    id BIGSERIAL PRIMARY KEY,
    waybill_no VARCHAR(30) NOT NULL REFERENCES waybills(waybill_no),
    event_type logistics_event_type NOT NULL,
    location VARCHAR(200),
    time TIMESTAMPTZ NOT NULL,
    operator VARCHAR(50),
    remark VARCHAR(200),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE logistics_event_logs IS '物流事件日志表';

CREATE INDEX idx_logistics_event_logs_waybill_no ON logistics_event_logs(waybill_no);
CREATE INDEX idx_logistics_event_logs_time ON logistics_event_logs(time);

-- ================================================================
-- 10. 通知服务（notification-service）
-- ================================================================

-- 用户通知偏好表
CREATE TABLE user_preferences (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
    notify_order_created BOOLEAN DEFAULT TRUE,
    notify_order_paid BOOLEAN DEFAULT TRUE,
    notify_order_shipped BOOLEAN DEFAULT TRUE,
    notify_order_delivered BOOLEAN DEFAULT TRUE,
    notify_coupon_issued BOOLEAN DEFAULT TRUE,
    notify_system_alert BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE user_preferences IS '用户通知偏好表';

-- 通知发送日志表
CREATE TABLE notification_logs (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    event_type VARCHAR(50) NOT NULL,
    channel_type notification_channel NOT NULL,
    status notification_status NOT NULL DEFAULT 'PENDING',
    content TEXT,
    trace_id VARCHAR(50),
    error_message TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    sent_at TIMESTAMPTZ,
    read_at TIMESTAMPTZ
);

COMMENT ON TABLE notification_logs IS '通知发送日志表';
COMMENT ON COLUMN notification_logs.event_type IS '触发事件类型，如ORDER_PAID';
COMMENT ON COLUMN notification_logs.channel_type IS '通道类型：SMS、EMAIL、APP_PUSH、WECHAT_TEMPLATE、INTERNAL_MESSAGE、DINGTALK';
COMMENT ON COLUMN notification_logs.trace_id IS '链路追踪ID';

CREATE INDEX idx_notification_logs_user_id ON notification_logs(user_id);
CREATE INDEX idx_notification_logs_event_type ON notification_logs(event_type);
CREATE INDEX idx_notification_logs_status ON notification_logs(status);
CREATE INDEX idx_notification_logs_created_at ON notification_logs(created_at);

-- ================================================================
-- 11. 促销服务（promotion-service）
-- ================================================================

-- 促销活动模板表
CREATE TABLE promotion_templates (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    type promotion_type NOT NULL,
    value DECIMAL(10,2) NOT NULL,
    condition DECIMAL(10,2),
    min_items INT DEFAULT 1,
    limit_per_user INT NOT NULL DEFAULT 1,
    total_quantity INT NOT NULL DEFAULT 10000,
    issued_quantity INT NOT NULL DEFAULT 0,
    start_time TIMESTAMPTZ NOT NULL,
    end_time TIMESTAMPTZ NOT NULL,
    scope VARCHAR(20) NOT NULL CHECK (scope IN ('ALL', 'PRODUCTS', 'CATEGORIES')) DEFAULT 'ALL',
    target_users JSONB,
    exclude_promos JSONB,
    products JSONB,
    categories JSONB,
    max_discount DECIMAL(10,2),
    is_stackable BOOLEAN DEFAULT FALSE,
    status VARCHAR(20) NOT NULL CHECK (status IN ('ON_SHELF', 'OFF_SHELF')) DEFAULT 'ON_SHELF',
    description VARCHAR(500),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE promotion_templates IS '促销活动模板表';
COMMENT ON COLUMN promotion_templates.code IS '活动编码';
COMMENT ON COLUMN promotion_templates.target_users IS '目标用户标签，如["NEW_USER", "VIP"]';
COMMENT ON COLUMN promotion_templates.exclude_promos IS '不可叠加的其他活动';
COMMENT ON COLUMN promotion_templates.products IS '限定商品ID列表';
COMMENT ON COLUMN promotion_templates.categories IS '限定类目ID列表';

CREATE INDEX idx_promotion_templates_code ON promotion_templates(code);
CREATE INDEX idx_promotion_templates_status ON promotion_templates(status);
CREATE INDEX idx_promotion_templates_start_end ON promotion_templates(start_time, end_time);

-- 促销使用日志表
CREATE TABLE promotion_usage_logs (
    id BIGSERIAL PRIMARY KEY,
    promotion_id BIGINT NOT NULL REFERENCES promotion_templates(id),
    user_id BIGINT NOT NULL REFERENCES users(id),
    order_id BIGINT NOT NULL REFERENCES orders(id),
    applied_amount DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE promotion_usage_logs IS '促销使用日志表';

CREATE INDEX idx_promotion_usage_logs_user_id ON promotion_usage_logs(user_id);
CREATE INDEX idx_promotion_usage_logs_order_id ON promotion_usage_logs(order_id);
CREATE INDEX idx_promotion_usage_logs_created_at ON promotion_usage_logs(created_at);

-- ================================================================
-- 12. 推荐服务（recommendation-service）
-- ================================================================

-- 用户行为日志表
CREATE TABLE user_behavior_logs (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    product_id BIGINT NOT NULL REFERENCES products(id),
    sku_id BIGINT REFERENCES skus(id),
    action user_behavior_action NOT NULL,
    source VARCHAR(20) CHECK (source IN ('WEB', 'APP', 'MINI_PROGRAM')),
    ip_address VARCHAR(50),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE user_behavior_logs IS '用户行为日志表';
COMMENT ON COLUMN user_behavior_logs.action IS '行为类型：VIEWED、CART_ADDED、ORDER_COMPLETED、FAVORITED';
COMMENT ON COLUMN user_behavior_logs.source IS '来源：WEB、APP、MINI_PROGRAM';

CREATE INDEX idx_user_behavior_logs_user_id ON user_behavior_logs(user_id);
CREATE INDEX idx_user_behavior_logs_product_id ON user_behavior_logs(product_id);
CREATE INDEX idx_user_behavior_logs_action ON user_behavior_logs(action);
CREATE INDEX idx_user_behavior_logs_created_at ON user_behavior_logs(created_at);

-- 推荐记录表
CREATE TABLE recommendation_logs (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    scene recommendation_scene NOT NULL,
    items_count INT NOT NULL,
    device_type device_type,
    request_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    response_time_ms INT
);

COMMENT ON TABLE recommendation_logs IS '推荐服务调用日志表';
COMMENT ON COLUMN recommendation_logs.scene IS '推荐场景：HOME、PRODUCT_DETAIL、CART、ORDER_COMPLETED';
COMMENT ON COLUMN recommendation_logs.device_type IS '设备类型：WEB、MOBILE、TABLET';
COMMENT ON COLUMN recommendation_logs.response_time_ms IS '响应耗时（毫秒）';

CREATE INDEX idx_recommendation_logs_user_id ON recommendation_logs(user_id);
CREATE INDEX idx_recommendation_logs_scene ON recommendation_logs(scene);
CREATE INDEX idx_recommendation_logs_request_time ON recommendation_logs(request_time);

-- ================================================================
-- 13. 搜索服务（search-service）
-- ================================================================

-- 搜索行为日志表
CREATE TABLE search_logs (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id),
    keyword VARCHAR(200) NOT NULL,
    result_count INT NOT NULL,
    click_count INT DEFAULT 0,
    device_type device_type,
    ip_address VARCHAR(50),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE search_logs IS '搜索行为日志表';
COMMENT ON COLUMN search_logs.keyword IS '搜索关键词';
COMMENT ON COLUMN search_logs.click_count IS '点击商品数';

CREATE INDEX idx_search_logs_keyword ON search_logs(keyword);
CREATE INDEX idx_search_logs_created_at ON search_logs(created_at);
CREATE INDEX idx_search_logs_user_id ON search_logs(user_id);

-- ================================================================
-- 14. 公共配置表（可选）
-- ================================================================

-- 系统参数表
CREATE TABLE system_configs (
    key_name VARCHAR(100) PRIMARY KEY,
    value_text TEXT,
    description VARCHAR(500),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE system_configs IS '系统配置表';
COMMENT ON COLUMN system_configs.key_name IS '配置键名，如shipping.base_fee';
COMMENT ON COLUMN system_configs.value_text IS '配置值（JSON字符串或文本）';

-- 示例插入：
-- INSERT INTO system_configs VALUES ('shipping.base_fee', '{"region":"全国","fee":10}', '全国基础运费10元');

✅ 四、使用说明（如何部署）

1. 创建数据库

createdb urbane_commerce -E UTF8 --locale=C

2. 执行脚本

psql -d urbane_commerce -f urbane-commerce-postgresql.sql