数据库 Schema 设计

1. ER 关系图

2. 订单表 (orders)

CREATE TABLE orders (
    -- 主键 & 标识
    order_id          BIGINT PRIMARY KEY,           -- Snowflake ID
    client_order_id   VARCHAR(64) NOT NULL,
    user_id           BIGINT NOT NULL,
    account_id        BIGINT NOT NULL,

    -- 交易对
    symbol            VARCHAR(20) NOT NULL,          -- e.g. 'BTCUSDT'
    base_asset        VARCHAR(10) NOT NULL,          -- e.g. 'BTC'
    quote_asset       VARCHAR(10) NOT NULL,          -- e.g. 'USDT'

    -- 订单参数
    side              VARCHAR(4) NOT NULL,           -- 'BUY' / 'SELL'
    order_type        VARCHAR(16) NOT NULL,          -- 'LIMIT','MARKET','STOP_LIMIT','STOP_MARKET'
    time_in_force     VARCHAR(4) DEFAULT 'GTC',      -- 'GTC','IOC','FOK','GTT'
    price             DECIMAL(32,16) DEFAULT 0,      -- 限价价格
    stop_price        DECIMAL(32,16) DEFAULT 0,      -- 止损触发价
    quantity          DECIMAL(32,16) NOT NULL,        -- 委托数量
    quote_order_qty   DECIMAL(32,16) DEFAULT 0,      -- 按金额下单

    -- 标志位
    post_only         BOOLEAN DEFAULT FALSE,
    reduce_only       BOOLEAN DEFAULT FALSE,
    is_liquidation    BOOLEAN DEFAULT FALSE,

    -- 成交状态
    status            VARCHAR(20) NOT NULL DEFAULT 'PENDING_NEW',
    executed_qty      DECIMAL(32,16) DEFAULT 0,      -- 累计成交量
    cumulative_quote  DECIMAL(32,16) DEFAULT 0,      -- 累计成交额
    avg_price         DECIMAL(32,16) DEFAULT 0,      -- 平均成交价
    remaining_qty     DECIMAL(32,16) DEFAULT 0,      -- 剩余量

    -- 手续费
    total_fee         DECIMAL(32,16) DEFAULT 0,
    fee_asset         VARCHAR(10),

    -- 杠杆
    leverage          SMALLINT DEFAULT 1,
    margin_type       VARCHAR(10) DEFAULT 'CROSS',   -- 'CROSS' / 'ISOLATED'
    frozen_margin     DECIMAL(32,16) DEFAULT 0,

    -- 时间
    created_at        TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    expire_at         TIMESTAMP WITH TIME ZONE,

    -- 版本控制
    version           INT DEFAULT 1,

    -- 唯一约束
    CONSTRAINT uq_client_order UNIQUE (user_id, client_order_id)
);

-- 状态检查
ALTER TABLE orders ADD CONSTRAINT chk_status
    CHECK (status IN (
        'PENDING_NEW','NEW','PARTIALLY_FILLED','FILLED',
        'PENDING_CANCEL','CANCELED','REJECTED','EXPIRED'
));
ALTER TABLE orders ADD CONSTRAINT chk_side
    CHECK (side IN ('BUY','SELL'));

3. 成交表 (trades)

CREATE TABLE trades (
    trade_id          BIGINT PRIMARY KEY,            -- 全局唯一成交 ID
    symbol            VARCHAR(20) NOT NULL,
    price             DECIMAL(32,16) NOT NULL,       -- 成交价格
    quantity          DECIMAL(32,16) NOT NULL,       -- 成交数量
    quote_qty         DECIMAL(32,16) NOT NULL,       -- 成交额

    -- 买卖双方
    buy_order_id      BIGINT NOT NULL REFERENCES orders(order_id),
    buy_user_id       BIGINT NOT NULL,
    sell_order_id     BIGINT NOT NULL REFERENCES orders(order_id),
    sell_user_id      BIGINT NOT NULL,

    -- 角色
    buyer_is_maker    BOOLEAN NOT NULL,

    -- 手续费
    buy_fee           DECIMAL(32,16) DEFAULT 0,
    buy_fee_asset     VARCHAR(10),
    sell_fee          DECIMAL(32,16) DEFAULT 0,
    sell_fee_asset    VARCHAR(10),

    -- 时间
    trade_time        TIMESTAMP WITH TIME ZONE NOT NULL,
    sequence_id       BIGINT NOT NULL,                -- 撮合序列号

    -- 索引
    created_at        TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

4. 账户与余额表

-- 账户表
CREATE TABLE accounts (
    id                BIGINT PRIMARY KEY,
    user_id           BIGINT NOT NULL,
    account_type      VARCHAR(10) NOT NULL DEFAULT 'SPOT',
                      -- 'SPOT' / 'MARGIN' / 'FUTURES'
    status            VARCHAR(10) DEFAULT 'ACTIVE',
    created_at        TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    CONSTRAINT uq_user_type UNIQUE (user_id, account_type)
);

-- 余额表（每个账户每种资产一行）
CREATE TABLE balances (
    id                BIGINT PRIMARY KEY,
    account_id        BIGINT NOT NULL REFERENCES accounts(id),
    asset             VARCHAR(10) NOT NULL,           -- 'BTC','USDT','ETH'
    available         DECIMAL(32,16) DEFAULT 0,       -- 可用余额
    frozen            DECIMAL(32,16) DEFAULT 0,       -- 冻结金额
    total             DECIMAL(32,16) GENERATED ALWAYS AS (available + frozen) STORED,
    version           INT DEFAULT 1,                  -- 乐观锁
    updated_at        TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    CONSTRAINT uq_account_asset UNIQUE (account_id, asset),
    CONSTRAINT chk_available CHECK (available >= 0),
    CONSTRAINT chk_frozen CHECK (frozen >= 0)
);

余额操作原子性

-- 冻结余额（下单时）
UPDATE balances
SET available = available - :amount,
    frozen    = frozen + :amount,
    version   = version + 1,
    updated_at = NOW()
WHERE account_id = :accountId
  AND asset = :asset
  AND available >= :amount
  AND version = :expectedVersion;
-- affected_rows == 0 → 余额不足或版本冲突

-- 成交结算（释放冻结 + 增加对手资产）
BEGIN;
  -- 买方: 释放 USDT 冻结，增加 BTC
  UPDATE balances SET frozen = frozen - :quoteAmt, version = version + 1
    WHERE account_id = :buyerAccountId AND asset = 'USDT';
  UPDATE balances SET available = available + :baseQty, version = version + 1
    WHERE account_id = :buyerAccountId AND asset = 'BTC';
  -- 卖方: 释放 BTC 冻结，增加 USDT
  UPDATE balances SET frozen = frozen - :baseQty, version = version + 1
    WHERE account_id = :sellerAccountId AND asset = 'BTC';
  UPDATE balances SET available = available + :quoteAmt, version = version + 1
    WHERE account_id = :sellerAccountId AND asset = 'USDT';
COMMIT;

余额操作模拟器

点击按钮模拟下单冻结、成交结算、撤单解冻的资金流转过程:

5. 冻结流水表

CREATE TABLE frozen_logs (
    id                BIGSERIAL PRIMARY KEY,
    account_id        BIGINT NOT NULL,
    asset             VARCHAR(10) NOT NULL,
    order_id          BIGINT NOT NULL,                -- 关联订单
    action            VARCHAR(10) NOT NULL,            -- 'FREEZE','UNFREEZE'
    amount            DECIMAL(32,16) NOT NULL,
    reason            VARCHAR(32),                     -- 'ORDER_NEW','TRADE_SETTLE','CANCEL'
    created_at        TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    CONSTRAINT uq_freeze UNIQUE (order_id, action, reason)
                      -- 幂等：同一订单同一操作不重复
);

冻结流水表记录每次资金冻结/解冻的完整审计轨迹。UNIQUE(order_id, action, reason) 保证同一订单的冻结操作幂等，防止重复冻结或解冻。

6. 手续费表

CREATE TABLE fee_records (
    id                BIGSERIAL PRIMARY KEY,
    trade_id          BIGINT NOT NULL REFERENCES trades(trade_id),
    user_id           BIGINT NOT NULL,
    order_id          BIGINT NOT NULL,
    symbol            VARCHAR(20) NOT NULL,

    -- 费率
    fee_role          VARCHAR(5) NOT NULL,            -- 'MAKER' / 'TAKER'
    base_rate         DECIMAL(10,8) NOT NULL,          -- 基础费率
    discount_rate     DECIMAL(10,8) DEFAULT 0,         -- 折扣后费率
    discount_source   VARCHAR(20),                     -- 'VIP','PLATFORM_TOKEN','COUPON'

    -- 费用
    fee_amount        DECIMAL(32,16) NOT NULL,
    fee_asset         VARCHAR(10) NOT NULL,
    original_amount   DECIMAL(32,16) NOT NULL,         -- 折扣前费用

    -- 时间
    created_at        TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- VIP 费率表
CREATE TABLE fee_tiers (
    id                SERIAL PRIMARY KEY,
    vip_level         INT NOT NULL,
    maker_rate        DECIMAL(10,8) NOT NULL,
    taker_rate        DECIMAL(10,8) NOT NULL,
    effective_from    TIMESTAMP WITH TIME ZONE NOT NULL,
    effective_to      TIMESTAMP WITH TIME ZONE,
    CONSTRAINT uq_vip_period UNIQUE (vip_level, effective_from)
);

7. 持仓表 (positions)

CREATE TABLE positions (
    id                BIGSERIAL PRIMARY KEY,
    user_id           BIGINT NOT NULL,
    account_id        BIGINT NOT NULL,
    symbol            VARCHAR(20) NOT NULL,

    -- 仓位信息
    side              VARCHAR(5) NOT NULL,             -- 'LONG' / 'SHORT'
    quantity          DECIMAL(32,16) DEFAULT 0,        -- 持仓数量
    entry_price       DECIMAL(32,16) DEFAULT 0,        -- 平均入场价
    mark_price        DECIMAL(32,16) DEFAULT 0,        -- 最新标记价格

    -- 保证金
    margin            DECIMAL(32,16) DEFAULT 0,        -- 当前保证金
    margin_type       VARCHAR(10) DEFAULT 'CROSS',
    leverage          SMALLINT DEFAULT 1,

    -- 盈亏
    unrealized_pnl    DECIMAL(32,16) DEFAULT 0,        -- 未实现盈亏
    realized_pnl      DECIMAL(32,16) DEFAULT 0,        -- 已实现盈亏
    liquidation_price DECIMAL(32,16) DEFAULT 0,        -- 强平价格

    -- 时间
    created_at        TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at        TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    version           INT DEFAULT 1,

    CONSTRAINT uq_user_symbol UNIQUE (user_id, symbol, side)
);

-- 持仓历史（每次变更记录）
CREATE TABLE position_history (
    id                BIGSERIAL PRIMARY KEY,
    position_id       BIGINT NOT NULL,
    trade_id          BIGINT,
    action            VARCHAR(10) NOT NULL,            -- 'OPEN','ADD','REDUCE','CLOSE','LIQUIDATE'
    quantity_change   DECIMAL(32,16) NOT NULL,
    price             DECIMAL(32,16) NOT NULL,
    realized_pnl      DECIMAL(32,16) DEFAULT 0,
    created_at        TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

仓位更新逻辑

-- 加仓: 更新平均入场价
new_entry_price = (old_qty * old_entry + add_qty * add_price) / (old_qty + add_qty)

-- 减仓/平仓: 计算已实现盈亏
realized_pnl = (exit_price - entry_price) * close_qty  -- LONG
realized_pnl = (entry_price - exit_price) * close_qty  -- SHORT

-- 未实现盈亏: 根据 mark_price 实时计算
unrealized_pnl = (mark_price - entry_price) * qty      -- LONG
unrealized_pnl = (entry_price - mark_price) * qty      -- SHORT

8. K 线存储 (OHLCV)

-- TimescaleDB hypertable
CREATE TABLE klines (
    symbol            VARCHAR(20) NOT NULL,
    interval          VARCHAR(4) NOT NULL,             -- '1m','5m','15m','1h','4h','1d','1w'
    open_time         TIMESTAMP WITH TIME ZONE NOT NULL,
    close_time        TIMESTAMP WITH TIME ZONE NOT NULL,

    -- OHLCV
    open              DECIMAL(32,16) NOT NULL,
    high              DECIMAL(32,16) NOT NULL,
    low               DECIMAL(32,16) NOT NULL,
    close             DECIMAL(32,16) NOT NULL,
    volume            DECIMAL(32,16) NOT NULL,         -- 基础资产成交量
    quote_volume      DECIMAL(32,16) NOT NULL,         -- 计价资产成交额
    trade_count       INT DEFAULT 0,                   -- 成交笔数

    PRIMARY KEY (symbol, interval, open_time)
);

-- 转为 TimescaleDB hypertable（按 open_time 分块）
SELECT create_hypertable('klines', 'open_time',
    chunk_time_interval => INTERVAL '7 days'
);

-- 压缩策略：7 天以上的数据自动压缩
ALTER TABLE klines SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'symbol,interval',
    timescaledb.compress_orderby = 'open_time DESC'
);
SELECT add_compression_policy('klines', INTERVAL '7 days');

-- 保留策略：只保留 2 年数据，更老的归档到冷存储
SELECT add_retention_policy('klines', INTERVAL '2 years');

K 线聚合流程

以 1m K 线为基础，每分钟窗口关闭时写入 TimescaleDB 并触发向上合成。5m 由 5 根 1m 合成，15m 由 3 根 5m 合成，以此类推。缺少成交的窗口插入空蜡烛（open=close=上一根 close, high=low=close, volume=0）。

9. 索引策略

-- === orders 表索引 ===
CREATE INDEX idx_orders_user_time ON orders (user_id, created_at DESC);
CREATE INDEX idx_orders_symbol_status ON orders (symbol, status);
CREATE INDEX idx_orders_status ON orders (status) WHERE status IN ('NEW','PARTIALLY_FILLED');
                      -- 部分索引：只索引活跃订单

-- === trades 表索引 ===
CREATE INDEX idx_trades_symbol_time ON trades (symbol, trade_time DESC);
CREATE INDEX idx_trades_buy_user ON trades (buy_user_id, trade_time DESC);
CREATE INDEX idx_trades_sell_user ON trades (sell_user_id, trade_time DESC);
CREATE INDEX idx_trades_order ON trades (buy_order_id);
CREATE INDEX idx_trades_order2 ON trades (sell_order_id);

-- === balances 表索引 ===
CREATE UNIQUE INDEX idx_balances_account_asset ON balances (account_id, asset);

-- === positions 表索引 ===
CREATE INDEX idx_positions_user ON positions (user_id);
CREATE INDEX idx_positions_liquidation ON positions (liquidation_price)
    WHERE quantity > 0;  -- 只索引有持仓的记录

-- === klines 表索引（TimescaleDB 自动创建 hypertable 索引）===
CREATE INDEX idx_klines_symbol_interval ON klines (symbol, interval, open_time DESC);

10. 分区与归档

-- 按月分区订单表
CREATE TABLE orders (
    -- ... 同上 ...
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2026_01 PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE orders_2026_02 PARTITION OF orders
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
-- ... 自动化脚本定期创建未来月份分区

-- 成交表同样按月分区
CREATE TABLE trades (
    -- ... 同上 ...
) PARTITION BY RANGE (trade_time);

数据生命周期模拟器

点击时间推进按钮，观察订单数据如何在热/温/冷三层存储间自动迁移:

11. Redis 数据结构

# === 订单簿快照 ===
# Sorted Set: 价格作为 score
ZADD  ob:BTCUSDT:asks  42000  "orderId1:qty1"
ZADD  ob:BTCUSDT:asks  42050  "orderId2:qty2"
ZADD  ob:BTCUSDT:bids  41950  "orderId3:qty3"

# === 最新成交价 ===
SET   ticker:BTCUSDT:last  "42000.5"
HSET  ticker:BTCUSDT  price "42000.5"  vol24h "1234.5"  high24h "43000"  low24h "41000"

# === 用户活跃订单缓存 ===
HSET  user:42:orders  "ORD-123"  "{json}"
# TTL: 订单完成后删除

# === 幂等去重 ===
SET   dedup:42:web-123  "ORD-123"  EX 86400
# clientOrderId 去重，TTL 24h

# === K 线实时缓存 ===
HSET  kline:BTCUSDT:1m:current  open "42000"  high "42100"  low "41950"  close "42050"  vol "123.5"

# === 撮合引擎快照 offset ===
SET   engine:BTCUSDT:offset  "1050"
SET   engine:BTCUSDT:snapshot_time  "1700000000000"

# === 限流计数器 ===
# 滑动窗口限流
ZADD  ratelimit:42:orders  {timestamp}  {requestId}
ZREMRANGEBYSCORE  ratelimit:42:orders  0  {timestamp - window}
ZCARD  ratelimit:42:orders  -- 当前窗口请求数

12. 数据迁移与版本管理