Bridge flows（跨链桥流水）

面向直接写 SQL 的内部团队。覆盖 Robinhood Chain（chain id 4663，Arbitrum Orbit） 上 L1<->L2 跨链桥的三类原始信号，派生出 4 张表，全部在 schema/clickhouse/derived/012_bridge_flows.sql。库名以 robinhood 为例。

0. 一张图看懂三个数据源怎么拼起来复制锚点链接

一笔"用户在 L1 存入 ERC20，在 L2 到账"的完整链路，实测会在三张表里各留一行， 且可以用真实哈希/字段精确串联（见第 3 节验证）：

L1 发起 (chain-indexer 目前不采 L1，仅从这里开始)
    │
    ▼
type=105 SubmitRetryableTx  ──┐   (bridge_deposits, tx_type=105)
  requestId = 0x...4e077     │   ticket 创建，tx_hash 记作 H1
    │ (自动或稍后手动 redeem)  │
    ▼                         │
type=104 ArbitrumRetryTx      │   (bridge_deposits, tx_type=104)
  ticketId = H1  ★与上面相等★  │   ticket 兑现，tx_hash 记作 H2
    │ (兑现时调用 L2 网关)      │
    ▼                         │
L2 网关 DepositFinalized 事件  │   (bridge_erc20_gateway_events)
  tx_hash = H2  ★与兑现交易相同★

反方向（L2 提现到 L1）：

用户/网关调用 ArbSys.sendTxToL1 (同一笔 L2 交易, tx_hash 记作 H3)
    │
    ├─▶ ArbSys L2ToL1Tx 事件        (bridge_withdrawals, tx_hash=H3, position=P)
    │
    └─▶ (若是网关代币提现) WithdrawalInitiated 事件
          (bridge_erc20_gateway_events, tx_hash=H3, l2_to_l1_id=P) ★与上面 position 相等★

这两条"★"标注的等式不是设计假设，是本次用生产真实数据实测出来的不变量，见第 3 节。

1. bridge_deposits：L1->L2 消息类交易（无 log，交易本身就是存款）复制锚点链接

来源：{db}.transactions，type IN (100, 104, 105)。三种类型字段几乎不重叠， 所以是一张宽表 + 大量 Nullable 列，tx_type 决定哪些列有值：

ticket_id 列把 105 的 requestId 和 104 的 ticketId 合并成一列——两者是同一个 标识符的两种称呼（创建 vs 兑现），coalesce 得到，同一行不会两个都有值。实测 发现一个很有用的不变量：104 行的 ticketId 精确等于对应 105 行自己的 tx_hash（3/3 抽样精确匹配，见第 3 节），所以查"这张 ticket 什么时候创建、什么 时候兑现"直接：

SELECT redeem.block_timestamp AS redeemed_at, submit.block_timestamp AS submitted_at
FROM robinhood.bridge_deposits FINAL AS redeem
JOIN robinhood.bridge_deposits FINAL AS submit
  ON redeem.ticket_id = submit.tx_hash
WHERE redeem.is_deleted = 0 AND redeem.tx_type = 104
  AND submit.is_deleted = 0 AND submit.tx_type = 105;

Fail-closed：ticket_id/refund_to/beneficiary/retry_to 都 CAST 成定长的 FixedString(32)/FixedString(20)，字节数不对会直接报错，不会静默截断或错位 ——符合项目 Cardinal Rule #1（拒绝而不是塞一个错误值）。6 个 wei 字段 （l1_base_fee/deposit_value/max_submission_fee/retry_value/max_refund/ submission_fee_refund）在 extra JSON 里是 hexutil.Big 编码（十六进制, 不补齐到偶数长度，如 "0x3de72b1"），转换前先补一个 '0' 半字节再 unhex，具体验证见第 3 节。

2. bridge_withdrawals：ArbSys L2ToL1Tx 事件（每一条 L2->L1 消息）复制锚点链接

来源：{db}.logs，address = 0x0000...0064（ArbSys 预编译）、 topic0 = keccak256("L2ToL1Tx(address,address,uint256,uint256,uint256,uint256,uint256,uint256,bytes)")。 不管是纯 ETH 提现、代币提现网关触发的，还是任意 L2->L1 调用，全部走这一个事件。

关键列：position（outbox 里的顺序号，实测严格按区块递减/递增连续）、 message_hash（不透明的 outbox 消息 id，当字节串存，不当数字算）、 callvalue（>0 才是直接 ETH 提现）、calldata（提现到 L1 后要执行的原始调用 数据，按事件自带的 ABI 长度字段截取，不再进一步解码目标合约调用）。

3. bridge_erc20_gateway_events：L2 代币网关的存款/提现事件复制锚点链接

来源同样是 {db}.logs，但这张表不是"如果存在就顺手做"的边角料——对于 "tokenized stocks"这个产品定位，这张表大概率比 bridge_deposits 本身更重要： 它是唯一直接给出"哪个 L1 代币、多少数量"的地方（bridge_deposits 只知道 用户往 L2 发了一条带 calldata 的消息，不解码 calldata 本身）。

研究结论（生产环境只读查询，2026-09-25，链高 ~72.04M）：

• DepositFinalized(address indexed l1Token, address indexed from, address indexed to, uint256 amount) topic0 = 0xc7f2e9c55c40a50fbc217dfc70cd39a222940dfa62145aa0ca49eb9535d4fcb2 （cast sig-event 算出，不是凭记忆）——生产环境命中两个网关地址： 0x1D187C3E2DA52D72BC9C41E3ABA0FDFA6A7BF055（1460 条）、 0xFD9B17206278C16DDAACF6AC8F05DBF97EDCB31E（318 条）。
• WithdrawalInitiated(address l1Token, address indexed from, address indexed to, uint256 indexed l2ToL1Id, uint256 exitNum, uint256 amount) topic0 = 0x3073a74ecb728d10be779fe19a74a1428e20468f5b4d167bf9c73d9067847d73 ——同两个网关地址：10 条 + 64 条。

两个地址、两种事件都确认存在（不是"研究后发现没有就不做"的情况），故按任务要求 建了表。SQL 里没有把这两个地址写死进 WHERE（和 001/002 的风格一致）： 用事件"形状"（topic 齐全 + data 长度精确匹配）来筛选，未来任何新部署的、用 同一套标准 ABI 的自定义网关会被自动纳入。

小数位提醒（同 erc20_transfers.amount，见 docs/queries.md §1.4）：amount 是代币链上原始整数，本表不知道 decimals，展示前需要另外获取。

4. bridge_daily_net_flow：按日净流量（仅已收盘日）复制锚点链接

设计和 004_daily_chain_stats.sql 完全同源：SQL 文件里的注释已经把"为什么不能 用 insert-trigger MV（会在 backfill 重叠/reorg tombstone 上重复计数）"讲清楚， 这里不重复。用 REFRESH EVERY 1 DAY + ReplacingMergeTree(refreshed_at) + APPEND + 3 天回看窗口，每次刷新都是从 FINAL 全量重算最近 3 个已收盘日， 最近一次刷新的整行永远赢。

统计口径（明确写出来，避免有人拿这张表直接当"全部跨链桥流水"用）：

• ETH 净流量：bridge_deposits.tx_type = 100 的存款 vs. bridge_withdrawals.callvalue > 0 的提现。105 类型交易自带的 deposit_value 不计入 ETH 存款——它是这条消息 执行时要花的 gas/callvalue 资金，不是用户在做"存 ETH"这个操作本身，算进去 会和它最终变成的代币金额重复计一次经济价值。
• 代币净流量：直接来自 bridge_erc20_gateway_events，已经是"执行完成"的金额， 不是"请求"的金额。

asset 列：ETH 用全零的 FixedString(20)（0x0000...0000）作哨兵值，其余都是 真实的 L1 代币地址。

踩坑记录（ClickHouse FULL JOIN + 非 Nullable 列）：把 ETH/代币两条腿分别 GROUP BY 再 FULL JOIN 拼当天的存款和提现时，第一版忘记加 SETTINGS join_use_nulls = 1，本地实测复现了真实 bug——day/l1_token 来自 普通 GROUP BY（非 Nullable），outer join 未匹配的一侧会被填成类型的零值 （1970-01-01、全零地址），不是真正的 NULL，coalesce(d.x, w.x) 会把这个 零值误判成"有效值"直接返回，导致"某天只有代币 A 的存款、只有代币 B 的提现" 被错误合并成一行(1970-01-01, 全零地址)的假数据。加上 SETTINGS join_use_nulls = 1 后本地重跑，两条真实数据分别落在各自正确的 (day, asset) 行上，问题消失。SQL 文件里已经加了这个 setting 和对应注释。

5. 验证：生产只读查询 + 独立 Python 解码，样本精确匹配复制锚点链接

全部通过 ssh [email protected] + 生产 ClickHouse 的只读查询完成 （SETTINGS max_threads=2, max_execution_time=90，未写入生产任何东西）， 本地额外起了一个 wt36_bridge_scratch 临时库（Docker 里已有的共享 chain-indexer-ch 容器），把生产抓回来的真实字节原样灌回三张原始表，跑真正的 CREATE MATERIALIZED VIEW，验证完 DROP DATABASE 清理掉。

tx 类型/事件计数（生产全量，链高 72,044,301）：

精确匹配验证（Python int(x,16) / 手写 ABI 解码 vs. 本地真跑 ClickHouse 物化视图输出，逐值比对）：

1. 6 个 wei 字段十六进制转 UInt256：0x3de72b1→64910001、 0x38d7ea4c68000→1000000000000000 等 6/6 精确匹配（含奇数长度十六进制和 接近满宽度两种边界情况）。
2. L2ToL1Tx 完整 ABI 解码（caller/arbBlockNum/ethBlockNum/timestamp/ callvalue/calldata 长度与内容）：3 个真实样本（含一笔 292 字节网关 calldata、一笔 0 字节纯 ETH 提现）全部与手写 Python ABI 解码逐字节一致； arbBlockNum 与该 log 自身的 block_number 3/3 相等（协议不变量）。
3. DepositFinalized/WithdrawalInitiated 解码：4 个真实样本与 Python 解码 逐字段一致。
4. 跨表交叉验证（本节开头那张图的两个"★"）：
   • tx_hash = AEBE3DF75C59FDAFD94E703CB196B10EDD6856CFF91723E10A76CACDE3222546 的 type=104 兑现交易，其 ticket_id 精确等于 tx_hash = EA092E8B218F7ACF7250CACB0FDB5296EBAEAFEC8866BA50FDD3234D8F2D564B 的 type=105 创建交易自己的哈希；另两组样本同样精确匹配（3/3）。
   • 同一笔 AEBE3DF7... 交易在生产 log 里对应一条 DepositFinalized（l1_token=0x232CE3BD40FCD6F80F3D55A522D03F25DF784EE2）， tx_hash 与上面完全相同——本地把这三行（105 创建、104 兑现、 DepositFinalized 日志）原样灌回 wt36_bridge_scratch 后， bridge_deposits（tx_type=104 行）与 bridge_erc20_gateway_events （deposit_finalized 行）的 tx_hash 在物化视图输出里精确相等。
   • tx_hash = 166C41A193683A13004B7899B07F4048FADBC780C3E9D14604E31AE2071DF4E6 的 WithdrawalInitiated（l2_to_l1_id=2734）与同一笔交易的 L2ToL1Tx（position=2734）精确相等；本地灌回后 bridge_withdrawals.position 与 bridge_erc20_gateway_events.l2_to_l1_id 在物化视图输出里精确相等。
5. bridge_daily_net_flow：本地插入一条"昨天"（相对 ClickHouse 服务器时钟） 的 5 ETH 存款 fixture，手动 SYSTEM REFRESH VIEW 后，目标表里出现精确一行 (day=昨天, asset=全零, deposits_amount=5000000000000000000)——刷新链路 （含上面提到的 join_use_nulls 修复）端到端跑通，不是只测了 SQL 语法。

以上样本 tx hash 均为链上真实交易（Robinhood Chain 主网，chain id 4663），可在 生产 ClickHouse 里用 hex(tx_hash) = '...' 直接复核。

6. 已知限制复制锚点链接

• bridge_deposits.retry_data / bridge_withdrawals.calldata：只按 ABI 位置 和长度截出原始字节，不解码成"调用了哪个函数、传了什么参数"（那是目标合约的 任意 ABI，超出本次范围）。
• bridge_daily_net_flow 的 ETH 口径不含 105 类型的 deposit_value（见第 4 节口径说明），如果之后要做"资金托管余额对账"这类更严格的场景，需要单独设计。
• Robinhood Chain 若之后部署除已发现的两个地址外的自定义网关（L2CustomGateway 一类），只要复用标准 DepositFinalized/WithdrawalInitiated ABI 就会被这两个 物化视图自动纳入，不需要改 SQL；如果换了完全不同的事件签名则需要新增。

安装与补算复制锚点链接

# 1. 建表 + 物化视图（依赖 sql/schema.sql 的 blocks/transactions/logs 已存在）
sed 's/{db}/robinhood/g' schema/clickhouse/derived/012_bridge_flows.sql \
  | clickhouse-client --multiquery

# 2. 补历史数据：{db}.transactions / {db}.logs 里"建物化视图之前"就已落库的部分
#    不会被 mv_bridge_deposits / mv_bridge_withdrawals / mv_bridge_gateway_* 捕获，
#    需要手动补一次全量插入（E 的取法、回填分块方式与 001/002 完全一致，见
#    schema/clickhouse/derived/README.md 的 "E 值怎么取" 一节，这里不重复）：
INSERT INTO robinhood.bridge_deposits
SELECT <把 012 文件里 mv_bridge_deposits 的 SELECT 原样搬过来>
FROM robinhood.transactions
WHERE type IN (100, 104, 105) AND block_number < {E};
-- bridge_withdrawals / bridge_erc20_gateway_events 同理，各自把对应 MV 的
-- SELECT 搬过来，加 block_number < {E} 的过滤。

# 3. bridge_daily_net_flow 不需要手动补历史：它是 REFRESH EVERY 的可刷新视图，
#    首次 REFRESH（等计划触发，或手动 SYSTEM REFRESH VIEW robinhood.mv_bridge_daily_net_flow）
#    会自己从 FINAL 全量算最近 3 天；更早的历史天数如果需要，把该文件里
#    `today() - 3` 临时改大跑一次一次性 SYSTEM REFRESH VIEW，再改回 3。

验证 SQL（任选区间，三张 1:1 表按 block_number 过滤后 FINAL 对比原始表满足 同样条件的计数，方法与 schema/clickhouse/derived/README.md"验证 SQL"一节一致， 不重复贴长 SQL）：bridge_deposits 对 transactions WHERE type IN (100,104,105)， bridge_withdrawals 对 logs WHERE address=... AND topic0=...（ArbSys 签名）， bridge_erc20_gateway_events 对 logs WHERE topic0 IN (...)（两个网关事件签名）。