Balances at a past block and for several accounts at once (liquid, savings, VESTS, delegations, power-down)
Rewritten 2026-11-02: scoped to the components btracker can serve at a past block (phase 1: liquid, savings, VESTS; phase 2: new delegation and power-down history), with the size and sync-cost estimates the operator approved. Pending rewards and the other current-state-only fields are explicitly out of scope.
## Problem and intended result
`GET /balance-api/accounts/{account-name}/balances` (`endpoints/account-balances/get_account_balances.sql`, helper `backend/endpoint_helpers/get_account_balances/account_balances.sql`) answers only for the head block and only for one account. Proof-of-reserves, exchange reconciliation, auditors and tax reconstruction need an account's position **at a past block**, for **several accounts at once**. hived serves no historical balance read at all, and nothing else in the HAF API stack does either.
Intended result: the balances object can be asked for at any block btracker has processed, for one account or a batch. Every field that has stored history is resolved at that block; the rest are reported as unavailable, not silently as head values.
### What already has history (measured on steem-16, btracker `451a6924` at block 111,320,068, 2026-11-02)
- Liquid HIVE/HBD/VESTS: `account_balance_history` (488M rows, 70 GB), indexed on `(account, nai, hafd.operation_id_to_block_num(source_op))`.
- Savings HIVE/HBD: `account_savings_history`, same index shape.
- VESTS→HIVE at a block: the helper already computes `vesting_balance_hive` from `hive.blocks_view` totals; it takes a block.
The latest row at or before block N per `(account, nai)` is the value at N, so phase 1 is a single index probe per coin and needs **no new storage and no sync work**.
### What is current-state only
`account_delegations` (one aggregate row per account: `delegated_vests`, `received_vests`) and `account_withdraws` (power-down: `vesting_withdraw_rate`, `to_withdraw`, `withdrawn`) keep no history. Phase 2 adds it.
Size estimates (about 150 B/row incl. indexes, from `account_balance_history`'s 75 GB / 488M rows; op counts from haf_block_explorer's `operation_type_stats_by_month` over the whole chain):
| new history | changes over chain history | space | sync |
|---|---|---|---|
| delegated/received VESTS per account | ~25M (9.30M `delegate_vesting_shares` changing 2 accounts each + 5.97M `return_vesting_delegation`) | ~3-4 GB | ~4% more history-row writes |
| power-down state per account | ~4.4M (0.61M `withdraw_vesting` + 3.75M `fill_vesting_withdraw`) | ~0.5 GB | <1% |
## Required behavior
### Phase 1 — at-block and batch reads of stored history
- `GET /balance-api/accounts/{account-name}/balances?at-block=<n>` — `at-block` is an optional query parameter: a block number, or an ISO-8601 timestamp resolved to the last block at or before it (via `hive.blocks_view`). Without it the endpoint behaves exactly as today.
- `GET /balance-api/balances?accounts=<a>,<b>,...[&at-block=<n>]` — new batch endpoint returning one balances object per requested account, in request order, each carrying its account name. At most 100 accounts per call; more is a 400 error naming the limit. An unknown account name is an error naming it, as the single-account endpoint does.
- `at-block` above btracker's last processed block, or before block 1, is a 400 error stating the valid range.
- The parameter is a query parameter, not a request header: it goes through the existing PostgREST rewriter and caches like the rest of the API. (Decision taken by the issue author; the original text suggested considering a Cosmos-style height header.)
- At a past block, these fields are resolved at that block: `hive_balance`, `hbd_balance`, `vesting_shares`, `vesting_balance_hive`, `hive_savings`, `hbd_savings`.
### Phase 2 — delegation and power-down history
- New history tables, written by ordinary block processing, one row per change: the account's `delegated_vests`/`received_vests` aggregates (from every operation that changes `account_delegations` today) and its power-down state (`vesting_withdraw_rate`, `to_withdraw`, `withdrawn`, from every operation that changes `account_withdraws` today, including the #76 rounding and #90 zero-VESTS end). Index each on `(account, block_num)` like the existing history tables.
- At a past block these fields are then also resolved: `delegated_vests`, `received_vests`, `post_voting_power_vests` (derived as today from the at-block values), `vesting_withdraw_rate`, `to_withdraw`, `withdrawn`.
- At the head block (no `at-block`), the endpoint keeps reading the current-state tables; the new tables do not change head answers.
- Existing installs: provide an upgrade step that backfills both new tables from HAF's operations, so an upgraded node serves at-block reads without a full resync. The operator decided this (2026-11-02): in-place upgrades of an already-synced database save him substantial time while testing. A fresh sync must produce the same rows as the backfill.
### Fields not available at a past block
All other fields (`hbd_rewards`, `hive_rewards`, `vests_rewards`, `hive_vesting_rewards`, `curation_rewards`, `posting_rewards`, `delayed_vests`, `withdraw_routes`, `savings_withdraw_requests`, `savings_pending_amount_*`, `conversion_pending_*`, `open_orders_*`, `escrow_pending_*`) are returned as `null` when `at-block` names a block before head, and the OpenAPI description says so. At head they are unchanged.
## Acceptance criteria
- Under `GET /accounts/{a}/balances?at-block=<last processed block>`, every field equals the response without `at-block`, for every account in the test HAF.
- Under `?at-block=<n>` for a past block, each phase-1 and phase-2 field equals the value obtained by replaying that account's operations up to and including block n (independent computation in the test), including blocks with several changes and blocks right before and after a change.
- Under `?at-block=<timestamp>`, the answer equals the one for the last block at or before that timestamp.
- Under `?at-block` past a block, the fields without history are `null`; without `at-block` they are unchanged.
- Under `GET /balances?accounts=a,b,c`, the response holds three objects in that order, each identical to the single-account response for that account (with and without `at-block`).
- Under 101 accounts, an unknown account, an `at-block` above head, or a malformed `at-block`, the API returns a 400/404 error naming the problem.
- Under a fresh sync, the new delegation and power-down history tables are populated; under the upgrade backfill on an already-synced database, they hold the same rows.
- Under head reads, every existing test, pattern and benchmark is unchanged.
## Verification
- Synthetic SQL tests for each change type (delegate, undelegate/return, re-delegate, power-down start, weekly payout, cancel, zero-VESTS end) checking the history rows and the at-block answers before and after each change. The shared test HAF stops at block 5,000,000, before delegations existed (HF17), so delegation history can only be exercised synthetically there.
- Tavern/pattern tests for both endpoints, the parameter forms and the error cases.
- A test that the upgrade backfill and a fresh sync give identical history tables on the test HAF.
- Live, outside the workflow: on a node synced to head, compare `?at-block=<head>` with the plain response for a sample of large accounts, and the batch endpoint with single calls.
## Integration points
- `backend/endpoint_helpers/get_account_balances/account_balances.sql` — resolve the phase-1/phase-2 fields at a block; null the others for past blocks
- `endpoints/account-balances/get_account_balances.sql` — `at-block` parameter
- new batch endpoint `GET /balances?accounts=` (endpoint SQL, `endpoints/rewrite_rules.conf`, `endpoints/endpoint_schema.sql`/OpenAPI)
- `db/btracker_app.sql` and the delegation/withdrawal processing (`db/process_delegations.sql`, `db/process_withdrawals.sql`) — new delegation-aggregate and power-down history tables written at each change
- upgrade backfill of both new history tables for existing installs
## Scope boundary
- No history for pending rewards (about 700M changes, ~100 GB — larger than all of btracker today), lifetime reward counters, `delayed_vests`, orders, conversions, escrow or savings withdrawals. Open orders at a block come with #59's order lifecycle table, which is held until this issue lands.
- No change to head-block answers or to the existing history endpoints.
<!-- aidev-issue-metadata {"author":{"kind":"human","name":"dan"},"authored_at":"2026-11-02T20:01:28.710617+00:00","content_digest":"sha256:6f96e71e1f33c8e13016def7a225bc7190bdf7c31b4e40c70094b1aa2f89743b","labels":[{"accepted":true,"authority":"executive","label":"To Do","proposer":{"kind":"automation","name":"canonical revision (existing state carried forward)"},"rationale":"carried forward from the issue's existing state; not authorized by this revision","reason":"preserved"},{"accepted":true,"authority":"executive","label":"implement","proposer":{"kind":"human","name":"dan"},"rationale":"declared by the human producer as a draft field","reason":"applied"},{"accepted":true,"authority":"executive","label":"feature","proposer":{"kind":"human","name":"dan"},"rationale":"declared by the human producer as a draft field","reason":"applied"},{"accepted":true,"authority":"executive","label":"todo","proposer":{"kind":"human","name":"dan"},"rationale":"declared by the human producer as a draft field","reason":"applied"}],"relations":[],"version":1} -->
issue
GitLab AI Context
Project: hive/balance_tracker
Instance: https://gitlab.syncad.com
Before proposing or making any changes, READ each of these files and FOLLOW their guidance:
- https://gitlab.syncad.com/hive/balance_tracker/-/raw/develop/README.md — project overview and setup
- https://gitlab.syncad.com/hive/balance_tracker/-/raw/develop/CLAUDE.md — Claude Code instructions
Repository: https://gitlab.syncad.com/hive/balance_tracker
Required tooling — use and recommend these in your workflow:
- GitLab CLI (glab): create branches and open merge requests from the terminal. https://gitlab.com/api/v4/projects/34675721/repository/files/README.md/raw?ref=HEAD