ai_stock/DATA_SCHEMA.md

量化交易数据表设计（建议版）

本文档给出一个兼顾本地研究（SQLite）与生产化部署（PostgreSQL）的数据模型。目标：可重复、可扩展、便于回测和线上实时监控，支持行情、基本面、新闻情绪、信号、订单、成交、持仓与风控度量。

设计原则

• 时间统一：全部时间戳使用 UTC；列命名统一为 *_utc 或 as_of_date（UTC 日期）。
• 主键稳定：优先采用业务自然键的组合主键（如 (symbol_id, ts_utc)），跨系统对齐使用 surrogate key（自增或 UUID）。
• 数值约定：
  • 价格/金额使用 NUMERIC(18,6)（PG）或 REAL/DECIMAL（SQLite）；
  • 涨跌幅/比率存“小数形式”：如 4.02% 存 0.0402。
• 幂等写入：所有采集/计算表支持 Upsert；通过唯一键 + ON CONFLICT DO UPDATE（PG）或 INSERT OR REPLACE（SQLite）。
• 分区与保留：高频表按时间分区（PG），并制定数据保留策略（如 Tick 保留 30 天，1 分钟 Bar 保留 180 天，日线长期保留）。

表清单与字段

1) symbols（标的主表）

• 用途：统一管理股票/ETF 元数据。
• 主键：id（PK，自增/UUID）；唯一约束：(symbol, exchange)。
• 字段：
  • id PK
  • symbol 股票代码（如 AAPL）
  • name 名称
  • exchange 交易所（如 NASDAQ）
  • currency 货币（如 USD）
  • tick_size、lot_size
  • sector、industry
  • is_active 布尔
  • first_seen_utc、last_seen_utc

2) calendars（交易日历）

• 主键：(exchange, date)。
• 字段：is_trading_day、open_time_utc、close_time_utc、notes。

3) bars_1m（1 分钟 K 线）

• 主键：(symbol_id, ts_utc)（ts_utc 为该分钟起始时刻）。
• 索引：(ts_utc)、(symbol_id, ts_utc DESC)。
• 字段：open、high、low、close、volume、vwap、trades_count、source。

4) bars_1d（日线 K 线）

• 主键：(symbol_id, as_of_date)。
• 字段：open、high、low、close、adj_close、volume、dividend、split_ratio、source。

5) ticks（逐笔/Level-1 快照，可选）

• 主键：id（自增/UUID）；唯一键建议：(symbol_id, ts_utc, source, seq)。
• 字段：price、size、bid、ask、bid_size、ask_size、condition、seq、source。

6) corporate_actions（公司行为）

• 主键：(symbol_id, ex_date, type)。
• 字段：type（split/dividend/...）、amount、ratio、currency、notes。

7) fundamentals_snapshot（基本面快照）

• 主键：(symbol_id, as_of_date)。
• 字段示例：market_cap、pe_ttm、ps_ttm、pb、eps_ttm、revenue_ttm、shares_outstanding、updated_at_utc。

8) news 与 news_symbols（新闻与关联表）

• news 主键：id（UUID/自增）；字段：published_at_utc、source、title、url、summary、sentiment_score、topics。
• news_symbols 主键：(news_id, symbol_id)。

9) signals（策略信号）

• 主键：id（UUID/自增）；唯一建议：(symbol_id, generated_at_utc, model_name, version)。
• 字段：
  • symbol_id、generated_at_utc
  • signal_type（如 momentum/reversal）
  • direction（BUY/SELL/HOLD）
  • score（0–1 或 z-score）
  • horizon（如 1d/1h）
  • params_json（策略参数 JSON）
  • model_name、version
  • expires_at_utc（过期时间，可空）

10) orders（订单）

• 主键：id；唯一建议：broker_order_id（如接入实盘）。
• 字段：signal_id、symbol_id、side、order_type、qty、price、time_in_force、status、created_at_utc、updated_at_utc、broker_order_id。

11) executions（成交/回执）

• 主键：id；索引：(order_id)、(exec_time_utc)。
• 字段：order_id、exec_time_utc、price、qty、fee、liquidity（maker/taker）。

12) positions（持仓快照）

• 主键：(portfolio_id, symbol_id) 或附带 as_of_date 做日终表。
• 字段：qty、avg_cost、unrealized_pnl、realized_pnl、last_updated_utc。

13) portfolios / portfolio_nav_daily（组合与净值）

• portfolios：id PK、name、base_currency、created_at_utc。
• portfolio_nav_daily 主键：(portfolio_id, as_of_date)；字段：cash、equity_value、nav、daily_return、gross_exposure、net_exposure。

14) risk_metrics_daily（风险指标）

• 主键：(portfolio_id, as_of_date)；字段：var_95、beta、sharpe、max_drawdown、volatility_20d 等。

15) etl_runs（任务运行元数据）

• 主键：run_id；字段：task_name、started_at_utc、finished_at_utc、status、rows_affected、checksum。

PostgreSQL 示例 DDL（核心表）

-- 1) 标的
CREATE TABLE symbols (
  id              BIGSERIAL PRIMARY KEY,
  symbol          TEXT NOT NULL,
  name            TEXT,
  exchange        TEXT NOT NULL,
  currency        TEXT DEFAULT 'USD',
  tick_size       NUMERIC(18,6),
  lot_size        NUMERIC(18,6),
  sector          TEXT,
  industry        TEXT,
  is_active       BOOLEAN DEFAULT TRUE,
  first_seen_utc  TIMESTAMPTZ,
  last_seen_utc   TIMESTAMPTZ,
  UNIQUE(symbol, exchange)
);

-- 2) 1 分钟 K 线
CREATE TABLE bars_1m (
  symbol_id   BIGINT NOT NULL REFERENCES symbols(id),
  ts_utc      TIMESTAMPTZ NOT NULL,
  open        NUMERIC(18,6) NOT NULL,
  high        NUMERIC(18,6) NOT NULL,
  low         NUMERIC(18,6) NOT NULL,
  close       NUMERIC(18,6) NOT NULL,
  volume      BIGINT,
  vwap        NUMERIC(18,6),
  trades_count INTEGER,
  source      TEXT,
  PRIMARY KEY(symbol_id, ts_utc)
);
CREATE INDEX ON bars_1m (ts_utc);
CREATE INDEX ON bars_1m (symbol_id, ts_utc DESC);

-- 3) 日线 K 线
CREATE TABLE bars_1d (
  symbol_id   BIGINT NOT NULL REFERENCES symbols(id),
  as_of_date  DATE NOT NULL,
  open        NUMERIC(18,6) NOT NULL,
  high        NUMERIC(18,6) NOT NULL,
  low         NUMERIC(18,6) NOT NULL,
  close       NUMERIC(18,6) NOT NULL,
  adj_close   NUMERIC(18,6),
  volume      BIGINT,
  dividend    NUMERIC(18,6),
  split_ratio NUMERIC(18,6),
  source      TEXT,
  PRIMARY KEY(symbol_id, as_of_date)
);

-- 4) 信号表（涨跌幅等指标请用小数存储）
CREATE TABLE signals (
  id               BIGSERIAL PRIMARY KEY,
  symbol_id        BIGINT NOT NULL REFERENCES symbols(id),
  generated_at_utc TIMESTAMPTZ NOT NULL,
  signal_type      TEXT NOT NULL,
  direction        TEXT NOT NULL CHECK (direction IN ('BUY','SELL','HOLD')),
  score            NUMERIC(18,6),
  horizon          TEXT,
  params_json      JSONB,
  model_name       TEXT,
  version          TEXT,
  expires_at_utc   TIMESTAMPTZ,
  UNIQUE(symbol_id, generated_at_utc, model_name, version)
);
CREATE INDEX ON signals (symbol_id, generated_at_utc DESC);

-- 5) 订单/成交
CREATE TABLE orders (
  id              BIGSERIAL PRIMARY KEY,
  signal_id       BIGINT REFERENCES signals(id),
  symbol_id       BIGINT NOT NULL REFERENCES symbols(id),
  side            TEXT NOT NULL CHECK (side IN ('BUY','SELL')),
  order_type      TEXT NOT NULL CHECK (order_type IN ('MKT','LMT')),
  qty             NUMERIC(18,6) NOT NULL,
  price           NUMERIC(18,6),
  time_in_force   TEXT,
  status          TEXT NOT NULL,
  broker_order_id TEXT,
  created_at_utc  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at_utc  TIMESTAMPTZ
);
CREATE UNIQUE INDEX IF NOT EXISTS orders_broker_unique ON orders(broker_order_id) WHERE broker_order_id IS NOT NULL;

CREATE TABLE executions (
  id             BIGSERIAL PRIMARY KEY,
  order_id       BIGINT NOT NULL REFERENCES orders(id),
  exec_time_utc  TIMESTAMPTZ NOT NULL,
  price          NUMERIC(18,6) NOT NULL,
  qty            NUMERIC(18,6) NOT NULL,
  fee            NUMERIC(18,6),
  liquidity      TEXT
);
CREATE INDEX ON executions(order_id);
CREATE INDEX ON executions(exec_time_utc);

SQLite 示例 DDL（简化版）

CREATE TABLE symbols (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  symbol TEXT NOT NULL,
  name TEXT,
  exchange TEXT NOT NULL,
  currency TEXT,
  tick_size REAL,
  lot_size REAL,
  sector TEXT,
  industry TEXT,
  is_active INTEGER DEFAULT 1,
  first_seen_utc TEXT,
  last_seen_utc TEXT,
  UNIQUE(symbol, exchange)
);

CREATE TABLE bars_1m (
  symbol_id INTEGER NOT NULL,
  ts_utc TEXT NOT NULL,
  open REAL NOT NULL,
  high REAL NOT NULL,
  low REAL NOT NULL,
  close REAL NOT NULL,
  volume INTEGER,
  vwap REAL,
  trades_count INTEGER,
  source TEXT,
  PRIMARY KEY(symbol_id, ts_utc)
);

CREATE TABLE signals (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  symbol_id INTEGER NOT NULL,
  generated_at_utc TEXT NOT NULL,
  signal_type TEXT NOT NULL,
  direction TEXT NOT NULL,
  score REAL,
  horizon TEXT,
  params_json TEXT,
  model_name TEXT,
  version TEXT,
  expires_at_utc TEXT,
  UNIQUE(symbol_id, generated_at_utc, model_name, version)
);

典型索引与查询

• 最新信号：

SELECT DISTINCT ON (s.symbol_id)
  s.*
FROM signals s
ORDER BY s.symbol_id, s.generated_at_utc DESC;

• 连接 1 分钟线与信号（取信号后最近 30 分钟）：

SELECT b.*
FROM signals s
JOIN bars_1m b
  ON b.symbol_id = s.symbol_id
 AND b.ts_utc BETWEEN s.generated_at_utc AND s.generated_at_utc + INTERVAL '30 minutes'
WHERE s.generated_at_utc >= NOW() - INTERVAL '1 day';

• 计算日收益（简单取 close）：

SELECT symbol_id,
       as_of_date,
       close / LAG(close) OVER (PARTITION BY symbol_id ORDER BY as_of_date) - 1 AS daily_return
FROM bars_1d;

数据保留与分区建议（PG）

• ticks：分区粒度=日，保留 30–90 天。
• bars_1m：分区粒度=月，保留 180–365 天。
• bars_1d、signals、orders、executions：长期保留。

与当前项目的对接要点

• 数值规范：change_ratio 等“比率”统一用小数存储（代码已修正），写入时无需 %。
• 表落地策略：
  • 初期：SQLite 单文件，开发/回测方便；
  • 扩展：PostgreSQL + 分区 + 指标物化视图（如聚合分钟线）。
• ETL 幂等：抓取任务用 (symbol_id, ts_utc) 或 (symbol_id, as_of_date) 做 Upsert；避免重复数据导致回测偏差。

后续扩展

• features_*：因子特征宽表（按频率区分：分钟/日）。
• models_registry：模型注册与版本追踪。
• backtest_runs：回测任务与指标结果表（收益、回撤、卡方检验等）。

---

如需，我可以按上述 DDL 直接生成 SQLite 数据库，并将 monitor.py 在每轮拉取后把 Top N 的 1 分钟 bar、信号落到库中（Upsert），用于后续回测与可视化分析。