T16 · データベースとチェーンのデータ

create table balances (
  address text primary key,
  token   text,
  amount  numeric
);

-- 一行はチェーンの一ログ。追記のみで、update しない。
create table transfer_events (
  chain_id      integer       not null,
  block_number  bigint        not null,
  log_index     integer       not null,
  block_hash    bytea         not null,      -- 再編成検出に使用
  block_time    timestamptz   not null,      -- now() ではなくブロックヘッダー由来
  tx_hash       bytea         not null,
  tx_index      integer       not null,
  contract      bytea         not null,
  from_addr     bytea         not null,
  to_addr       bytea         not null,
  amount        numeric(78,0) not null,      -- 256 ビット整数を格納可能
  primary key (chain_id, block_number, log_index)
);

create index on transfer_events (chain_id, contract, from_addr, block_number);
create index on transfer_events (chain_id, contract, to_addr,   block_number);

-- チェーンごとに一行で索引位置を記録。
create table indexer_checkpoints (
  chain_id          integer     primary key,
  last_block_number bigint      not null,
  last_block_hash   bytea       not null,
  updated_at        timestamptz not null default now()
);

-- 最近のブロックのハッシュ連鎖を残し、再編成をさかのぼる。
create table blocks (
  chain_id     integer     not null,
  block_number bigint      not null,
  block_hash   bytea       not null,
  parent_hash  bytea       not null,       -- 連続性の確認に使用
  block_time   timestamptz not null,
  primary key (chain_id, block_number)
);

insert into transfer_events
  (chain_id, block_number, log_index, block_hash, block_time,
   tx_hash, tx_index, contract, from_addr, to_addr, amount)
values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
on conflict (chain_id, block_number, log_index) do nothing;

create table token_balances (
  chain_id         integer       not null,
  contract         bytea         not null,
  holder           bytea         not null,
  balance          numeric(78,0) not null,
  updated_at_block bigint        not null,
  primary key (chain_id, contract, holder)
);

with inserted as (
  insert into transfer_events
    (chain_id, block_number, log_index, block_hash, block_time,
     tx_hash, tx_index, contract, from_addr, to_addr, amount)
  values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
  on conflict (chain_id, block_number, log_index) do nothing
  returning chain_id, block_number, contract, from_addr, to_addr, amount
),
deltas as (
  select chain_id, contract, to_addr   as holder,  amount as delta, block_number from inserted
  union all
  select chain_id, contract, from_addr as holder, -amount as delta, block_number from inserted
)
insert into token_balances as b (chain_id, contract, holder, balance, updated_at_block)
select chain_id, contract, holder, delta, block_number from deltas
on conflict (chain_id, contract, holder) do update
set balance          = b.balance + excluded.balance,
    updated_at_block = greatest(b.updated_at_block, excluded.updated_at_block);

begin;
delete from transfer_events where chain_id = $1 and block_number > $2;
delete from blocks           where chain_id = $1 and block_number > $2;
update indexer_checkpoints
  set last_block_number = $2,
      last_block_hash   = (select block_hash from blocks where chain_id = $1 and block_number = $2)
  where chain_id = $1;
commit;