ai-exchange/docs/AI_ICT_PostgreSQL_Migration_SQL_Drafts_v1.md

AI ICT PostgreSQL Migration SQL Drafts v1

1. 文档目标

本文档提供 AI ICT 项目第一波 PostgreSQL migration 的 SQL 草案，用于直接指导：

• 基础表初版建表
• 主键 / 外键 / 唯一约束落地
• 核心查询索引落地
• candles 月分区初始化
• 后续正式 migration 文件编写

本文档聚焦第一实现波次，不覆盖全部事件表，只覆盖：

1. venues
2. instruments
3. candles
4. market_sessions
5. swings

关联文档：

1. AI_ICT_PostgreSQL_Schema_v1.md
2. AI_ICT_PostgreSQL_Migration_Split_Plan_v1.md
3. AI_ICT_开发路线图_v1.md
4. AI_ICT_执行检查清单_v1.md

---

2. 设计边界与约定

第一波 migration 草案遵循以下原则：

1. 优先让基础数据层可落地
2. 使用 text + check constraint，不先引入数据库 enum
3. 高增长表优先做好唯一键与索引
4. 只有 candles 先做分区，其他表先普通表
5. SQL 草案以 PostgreSQL 15+ 常见写法为基准

统一约定：

• 主键使用 bigserial
• 时间字段统一使用 timestamptz
• 默认 schema 先按 public 处理
• 金额/价格统一使用 numeric(20,10) 或更高精度
• JSON 扩展字段使用 jsonb not null default '{}'::jsonb

---

3. 建议 migration 文件顺序

001_base_conventions.sql
002_create_venues.sql
003_create_instruments.sql
004_create_candles.sql
005_create_candles_partitions.sql
006_create_market_sessions.sql
007_create_swings.sql

说明：

• candles 主表和分区单独拆文件，便于验证与回滚
• 若团队偏向更细粒度，也可把索引拆到各自 *_indexes.sql
• 第一波先不把 seed 数据硬编码进 DDL migration，可单独写 seed_base_data.sql

---

4. 基础公共 SQL 草案

4.1 001_base_conventions.sql

建议先放公共 trigger function：

create or replace function set_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

用途：

• 给存在 updated_at 的基础表复用
• 减少每张表单独手写更新时间逻辑的重复

说明：

• candles 第一版没有 updated_at，通常不需要该 trigger
• 若团队想保持 migration 最小化，也可先不加 trigger，改由应用层更新

---

5. venues SQL 草案

5.1 建表 SQL

create table if not exists venues (
  id bigserial primary key,
  code text not null,
  name text not null,
  timezone text not null,
  status text not null default 'active',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint venues_code_key unique (code),
  constraint venues_status_check check (status in ('active', 'inactive'))
);

5.2 索引与 trigger

create index if not exists idx_venues_status
  on venues (status);

create trigger trg_venues_set_updated_at
before update on venues
for each row
execute function set_updated_at();

5.3 说明

• code 用于业务稳定引用，如 OKX
• timezone 保留交易所默认时区配置
• status 先保留最小集合：active | inactive

---

6. instruments SQL 草案

6.1 建表 SQL

create table if not exists instruments (
  id bigserial primary key,
  venue_id bigint not null references venues(id),
  symbol text not null,
  asset_class text not null,
  base_asset text,
  quote_asset text,
  price_scale integer not null,
  qty_scale integer not null,
  tick_size numeric(20,10) not null,
  lot_size numeric(20,10),
  active boolean not null default true,
  meta jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint instruments_venue_symbol_key unique (venue_id, symbol),
  constraint instruments_price_scale_check check (price_scale >= 0),
  constraint instruments_qty_scale_check check (qty_scale >= 0),
  constraint instruments_tick_size_check check (tick_size > 0),
  constraint instruments_lot_size_check check (lot_size is null or lot_size > 0)
);

6.2 索引与 trigger

create index if not exists idx_instruments_venue_id
  on instruments (venue_id);

create index if not exists idx_instruments_active
  on instruments (active);

create index if not exists idx_instruments_asset_class
  on instruments (asset_class);

create trigger trg_instruments_set_updated_at
before update on instruments
for each row
execute function set_updated_at();

6.3 说明

• 用 unique (venue_id, symbol) 保证交易所内品种唯一
• meta 用于附加合约规则、别名、外部映射等
• asset_class 暂时不强加 check，给现货、永续、CFD、贵金属留空间

---

7. candles SQL 草案

7.1 主表建表 SQL

第一版建议使用按 ts_open 的 range partition：

create table if not exists candles (
  id bigserial,
  instrument_id bigint not null references instruments(id),
  timeframe text not null,
  ts_open timestamptz not null,
  ts_close timestamptz not null,
  open numeric(20,10) not null,
  high numeric(20,10) not null,
  low numeric(20,10) not null,
  close numeric(20,10) not null,
  volume numeric(28,10),
  source text not null default 'api',
  created_at timestamptz not null default now(),
  constraint candles_pk primary key (id, ts_open),
  constraint candles_timeframe_check check (timeframe in ('1m', '5m', '15m', '1h', '4h', '1d')),
  constraint candles_price_order_check check (high >= low),
  constraint candles_open_range_check check (open <= high and open >= low),
  constraint candles_close_range_check check (close <= high and close >= low),
  constraint candles_time_order_check check (ts_close > ts_open),
  constraint candles_unique_bar unique (instrument_id, timeframe, ts_open)
) partition by range (ts_open);

7.2 为什么主键这样定义

由于 PostgreSQL 分区表上的唯一约束/主键需要包含分区键，第一版草案建议：

• 主键使用 (id, ts_open)
• 真正业务幂等约束使用 unique (instrument_id, timeframe, ts_open)

这样可以同时满足：

• 保留自增主键
• 兼容分区表限制
• 保证单根 K 线不会重复写入

7.3 主表索引 SQL

create index if not exists idx_candles_instrument_tf_ts_open_desc
  on candles (instrument_id, timeframe, ts_open desc);

create index if not exists idx_candles_instrument_tf_ts_close_desc
  on candles (instrument_id, timeframe, ts_close desc);

create index if not exists idx_candles_instrument_tf_source_ts_open_desc
  on candles (instrument_id, timeframe, source, ts_open desc);

7.4 分区初始化 SQL 草案

例如先建立 2026-04 与 2026-05 两个分区：

create table if not exists candles_2026_04
partition of candles
for values from ('2026-04-01 00:00:00+00') to ('2026-05-01 00:00:00+00');

create table if not exists candles_2026_05
partition of candles
for values from ('2026-05-01 00:00:00+00') to ('2026-06-01 00:00:00+00');

7.5 分区级索引补充

若实际运行中发现分区继承索引不足以覆盖主要查询，可显式补：

create index if not exists idx_candles_2026_04_instrument_tf_ts_open_desc
  on candles_2026_04 (instrument_id, timeframe, ts_open desc);

create index if not exists idx_candles_2026_05_instrument_tf_ts_open_desc
  on candles_2026_05 (instrument_id, timeframe, ts_open desc);

7.6 后续自动建分区建议

后续可以增加一个月度分区初始化脚本，例如：

• 每月月底预建下两个月分区
• 避免行情入库时因缺少分区而失败

但这不属于第一波 migration DDL 主体，可放到运维脚本或 cron 任务中。

---

8. market_sessions SQL 草案

8.1 建表 SQL

create table if not exists market_sessions (
  id bigserial primary key,
  instrument_id bigint not null references instruments(id),
  session_date date not null,
  timezone text not null,
  session_code text not null,
  start_ts timestamptz not null,
  end_ts timestamptz not null,
  high numeric(20,10),
  low numeric(20,10),
  midpoint numeric(20,10),
  status text not null default 'closed',
  meta jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint market_sessions_unique_key unique (instrument_id, session_date, session_code, timezone),
  constraint market_sessions_status_check check (status in ('open', 'closed', 'incomplete', 'invalid')),
  constraint market_sessions_code_check check (session_code in ('ASIA', 'LONDON', 'NY_AM', 'NY_LUNCH', 'NY_PM')),
  constraint market_sessions_time_order_check check (end_ts > start_ts),
  constraint market_sessions_price_order_check check (
    high is null or low is null or high >= low
  )
);

8.2 索引与 trigger

create index if not exists idx_market_sessions_instrument_date
  on market_sessions (instrument_id, session_date desc);

create index if not exists idx_market_sessions_instrument_code_date
  on market_sessions (instrument_id, session_code, session_date desc);

create index if not exists idx_market_sessions_status
  on market_sessions (status);

create trigger trg_market_sessions_set_updated_at
before update on market_sessions
for each row
execute function set_updated_at();

8.3 说明

• session_date 是业务会话所属日，不等同于 start_ts::date
• midpoint 可存 session 中点，减少重复计算
• status 预留不完整或异常会话标记

---

9. swings SQL 草案

9.1 建表 SQL

create table if not exists swings (
  id bigserial primary key,
  instrument_id bigint not null references instruments(id),
  timeframe text not null,
  kind text not null,
  strength text not null,
  price numeric(20,10) not null,
  ts timestamptz not null,
  left_bars integer not null,
  right_bars integer not null,
  confirmed boolean not null default true,
  meta jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  constraint swings_kind_check check (kind in ('swing_high', 'swing_low')),
  constraint swings_strength_check check (strength in ('ST', 'IT', 'LT')),
  constraint swings_left_bars_check check (left_bars > 0),
  constraint swings_right_bars_check check (right_bars > 0)
);

9.2 索引 SQL

create index if not exists idx_swings_instrument_tf_ts_desc
  on swings (instrument_id, timeframe, ts desc);

create index if not exists idx_swings_instrument_tf_kind_ts_desc
  on swings (instrument_id, timeframe, kind, ts desc);

create index if not exists idx_swings_instrument_tf_strength_ts_desc
  on swings (instrument_id, timeframe, strength, ts desc);

create index if not exists idx_swings_confirmed_ts_desc
  on swings (instrument_id, timeframe, confirmed, ts desc);

9.3 可选幂等约束建议

若后续想减少同一 swing 被重复写入，可考虑增加唯一约束：

unique (instrument_id, timeframe, kind, strength, ts, price)

但第一版建议先不急着加，原因：

• 不同算法版本可能在同一时间点重建结构
• 后续也可能需要靠 meta.recalc_batch_id 区分重算来源
• 先观察真实数据再决定是否收紧

---

10. 推荐 seed SQL 草案

10.1 venues seed

insert into venues (code, name, timezone)
values
  ('OKX', 'OKX', 'UTC'),
  ('LMAX', 'LMAX', 'Europe/London')
on conflict (code) do nothing;

10.2 instruments seed

insert into instruments (
  venue_id,
  symbol,
  asset_class,
  base_asset,
  quote_asset,
  price_scale,
  qty_scale,
  tick_size,
  lot_size
)
select v.id, x.symbol, x.asset_class, x.base_asset, x.quote_asset, x.price_scale, x.qty_scale, x.tick_size, x.lot_size
from venues v
join (
  values
    ('OKX', 'BTC-USDT-SWAP', 'perpetual', 'BTC', 'USDT', 1, 3, 0.1::numeric, 0.001::numeric),
    ('OKX', 'ETH-USDT-SWAP', 'perpetual', 'ETH', 'USDT', 2, 3, 0.01::numeric, 0.001::numeric),
    ('LMAX', 'XAUUSD', 'spot', 'XAU', 'USD', 2, 2, 0.01::numeric, 0.01::numeric)
) as x(venue_code, symbol, asset_class, base_asset, quote_asset, price_scale, qty_scale, tick_size, lot_size)
  on v.code = x.venue_code
on conflict (venue_id, symbol) do nothing;

---

11. 第一波最小验证 SQL 草案

11.1 唯一约束验证

-- candles 重复写入应失败
insert into candles (
  instrument_id, timeframe, ts_open, ts_close, open, high, low, close, volume
) values (
  1, '1m', '2026-04-01 00:00:00+00', '2026-04-01 00:01:00+00',
  100, 101, 99, 100.5, 10
);

insert into candles (
  instrument_id, timeframe, ts_open, ts_close, open, high, low, close, volume
) values (
  1, '1m', '2026-04-01 00:00:00+00', '2026-04-01 00:01:00+00',
  100, 101, 99, 100.5, 10
);

第二条应因 candles_unique_bar 失败。

11.2 约束验证

-- 非法 timeframe 应失败
insert into candles (
  instrument_id, timeframe, ts_open, ts_close, open, high, low, close
) values (
  1, '2m', '2026-04-01 00:00:00+00', '2026-04-01 00:02:00+00',
  100, 101, 99, 100
);

-- 非法 swing kind 应失败
insert into swings (
  instrument_id, timeframe, kind, strength, price, ts, left_bars, right_bars
) values (
  1, '1m', 'high', 'ST', 100, '2026-04-01 00:10:00+00', 2, 2
);

11.3 分区验证

select tableoid::regclass, instrument_id, timeframe, ts_open
from candles
where ts_open = '2026-04-01 00:00:00+00';

应看到记录落到 candles_2026_04。

---

12. 回滚草案建议

第一波回滚顺序建议：

swings
-> market_sessions
-> candles partitions
-> candles
-> instruments
-> venues
-> set_updated_at function

回滚示例：

drop table if exists swings;
drop table if exists market_sessions;
drop table if exists candles_2026_05;
drop table if exists candles_2026_04;
drop table if exists candles;
drop table if exists instruments;
drop table if exists venues;
drop function if exists set_updated_at();

说明：

• 实际生产环境不应直接照抄物理删除
• 先停写入任务，再确认无下游依赖，再执行回滚
• 若已有真实数据，应先备份再处理

---

13. 第一波完成标准

满足以下条件后，可认为 migration SQL draft 文档完成：

• 已给出基础表的建表 SQL 草案
• 已给出唯一约束和 check constraint 草案
• 已给出核心索引草案
• 已给出 candles 分区建表与初始化草案
• 已给出最小 seed 与验证 SQL 草案
• 已能直接作为正式 migration 编写参考

---

14. 推荐的下一步

完成本文件后，建议立即继续：

1. 把上述 SQL 草案拆成正式 migration 文件
2. 单独补一份 verify_base_tables.sql
3. 单独补一份 rollback_base_tables.sql
4. 再进入 dealing_ranges / liquidity_pools 的第二波 migration 草案