Skip to content

README.md

Latest commit

 

History

History
273 lines (203 loc) · 10.4 KB

README.md

File metadata and controls

273 lines (203 loc) · 10.4 KB
Raw

hdb

hdb is the crypto accounting database CLI in this monorepo.

Source location: src/apps/hdb

For the local web UI over hdb data, see ../hdb-portal/README.md.

Requirements

  • Node.js >=20
  • npm
  • local PostgreSQL

Setup

Install dependencies from the repo root:

npm install

Configure env in the default helper path:

mkdir -p "${XDG_CONFIG_HOME:-$HOME/.config}/helper"
cp .env.example "${XDG_CONFIG_HOME:-$HOME/.config}/helper/.env"

For hdb, set these required Postgres values in the env file:

HELPER_POSTGRES_DATABASE=hdb
HELPER_POSTGRES_USERNAME=hdb_user
HELPER_POSTGRES_PASSWORD=hdb_password

Optional override:

export HELPER_ENV_FILE=/absolute/path/to/.env

Local PostgreSQL setup details are in README.postgres.md.

Usage

Run from TypeScript source:

  • tsx src/apps/hdb/cli.ts --help
  • tsx src/apps/hdb/cli.ts health

Run built CLI:

  • npm run build
  • node dist/apps/hdb/cli.js --help

Install binary in your shell:

  • npm link
  • hdb --help

Commands

System

  • hdb health
  • hdb system rebuild-all
    • rebuilds all input-derived tables sequentially
    • excludes Coinbase orders
    • stages: Coinbase transactions -> Coinbase balances -> CoinTracker transactions/balances -> CoinTracker gains

Coinbase

  • hdb coinbase balances list <asset>
    • asset supports colon-separated values
    • --json prints structured troubleshooting output
    • --json-file <path> writes the same structured payload to disk
    • --current live check requires --remote
  • hdb coinbase balances snapshot
    • --json prints structured troubleshooting output
    • --json-file <path> writes the same structured payload to disk
    • --current live check requires --remote
  • hdb coinbase balances trace <asset>
    • --json prints structured troubleshooting output
    • --json-file <path> writes the same structured payload to disk
  • hdb coinbase balances rebuild
  • hdb coinbase lots analyze <asset>
  • hdb coinbase lots analyze-all
  • hdb coinbase lots compare <asset>
  • hdb coinbase lots compare-all
    • supports --csv and --f8949 exports
  • hdb coinbase orders show <orderId>
    • --json prints { row, meta }
    • --json-file <path> writes the same structured payload to disk
  • hdb coinbase orders inspect <orderId>
    • --json prints { row, meta }
    • --json-file <path> writes the same structured payload to disk
  • hdb coinbase orders fees [productId]
  • hdb coinbase orders import-one <orderId>
    • requires explicit live mode: --remote
  • hdb coinbase orders rebuild
    • source selection required: --cache or --remote
  • hdb coinbase orders sync
    • requires source selection: --cache or --remote

Coinbase Transactions

  • hdb coinbase transactions list [asset]
    • --json prints { rows, filters, meta }
    • --json-file <path> writes the same structured payload to disk
  • hdb coinbase transactions summary [asset]
    • --json prints { rows, totals, filters, meta }
    • --json-file <path> writes the same structured payload to disk
  • hdb coinbase transactions show [id]
    • --json prints { rows, filters, meta }
    • --json-file <path> writes the same structured payload to disk
  • hdb coinbase transactions add-manual <asset>
  • hdb coinbase transactions import-statement <filepath>
  • hdb coinbase transactions rebuild
    • input directory: --input-dir <dir> or ${HELPER_HDB_ROOT_DIR}/input/coinbase-transactions
  • hdb coinbase transactions analyze-nav
    • requires explicit live mode: --remote

Coinbase migration status and next slices are tracked in:

CoinTracker

  • hdb cointracker balances list [currency]
    • --json prints { rows, filters, meta }
    • --json-file <path> writes the same structured payload to disk
  • hdb cointracker balances rebuild
  • hdb cointracker gains list [assets]
    • export flags: --csv, --f8949, --headers, --pages
    • totals/format: --totals, --raw, --json
    • --json-file <path> writes the same structured payload to disk
  • hdb cointracker gains summary [assets]
    • export flags: --csv, --f8949, --headers, --pages
    • totals/format: --totals, --raw, --json
    • --json-file <path> writes the same structured payload to disk
  • hdb cointracker gains rebuild
    • input directory: --input-dir <dir> or ${HELPER_HDB_ROOT_DIR}/input/cointracker-capital-gains
  • hdb cointracker gains analyze-usdc
    • use --buckets or --interval <day|week|month|quarter|year>
  • hdb cointracker transactions list [asset]
    • --json prints { rows, filters, meta }
    • --json-file <path> writes the same structured payload to disk
  • hdb cointracker transactions summary [asset]
    • --json prints { rows, filters, meta }
    • --json-file <path> writes the same structured payload to disk
  • hdb cointracker transactions rebuild
    • input directory: --input-dir <dir> or ${HELPER_HDB_ROOT_DIR}/input/cointracker-transactions
    • also rebuilds cointracker_balances_ledger

Use hdb <command path> --help for option details.

Troubleshooting Workflow

Prefer these two local-only paths when validating hdb correctness:

  1. hdb ... --json
    • best for repeated debugging and agent-assisted inspection
    • stable machine-readable output with normalized filters and row counts
  2. psql with the local hdb_readonly role
    • best for ad hoc SQL when the existing command surface is not enough
    • use only for local read-only inspection

Examples:

hdb coinbase transactions list btc --from 2026-01-01 --to 2026-02-01 --json
hdb coinbase balances trace eth --to 2026-02-01T00:00:00Z --json
hdb cointracker gains summary btc:eth --totals --json
psql "postgresql://hdb_readonly:readonly_password@localhost:5432/hdb" -c "select count(*) from coinbase_transactions;"

Structured JSON (--json / --json-file) is read-only and intended for troubleshooting. It is not available on mutation commands. For CoinTracker gains, structured JSON cannot be combined with --csv or --f8949.

Remaining TODOs

This section tracks legacy old.js TODOs that are still relevant for the migrated hdb app.

1) Incremental transaction import + controlled overrides

Goal: support incremental ingestion of new CSVs by filename (without full rebuild), with explicit conflict handling.

Current state: Coinbase supports import-statement <filepath> and both Coinbase/CoinTracker support directory rebuilds. Conflict replacement is available for manual Coinbase inserts only.

Needed:

  • Add incremental file import command(s) for CoinTracker transactions similar to Coinbase statement import.
  • Add explicit conflict policy (error, skip, replace) for CSV imports/rebuilds.
  • For replace paths on Coinbase transactions, trigger or enforce dependent recomputation (at minimum balances, and potentially lots) to keep derived data consistent.

2) Tie Coinbase transactions to orders/fills

Goal: map trade transactions to order lifecycle and fill records for stronger auditability and reconciliation.

Current state: coinbase_orders is stored, but coinbase_transactions rows do not contain order_id, and there is no fill-level table.

Needed:

  • Add a coinbase_fills model (or equivalent) with fill-level attributes, including order_id.
  • Build a deterministic transaction-to-fill linking path where possible.
  • Add explicit unmatched/ambiguous handling plus optional manual overrides for hard cases.

3) Historical NAV reconstruction

Goal: compute point-in-time NAV over a historical range, not only current snapshot NAV.

Current state: coinbase transactions analyze-nav uses current live account balances and current product prices, not historical prices.

Needed:

  • Historical price source and ingestion pipeline.
  • Local storage for historical candles/prices keyed by product and interval.
  • Valuation logic that joins historical holdings with historical prices at consistent timestamps.
  • Policy for missing candle data (fail, nearest, interpolation).

Reference:

4) CoinTracker balances ledger linkage to Coinbase transactions

Goal: extend CoinTracker balance ledger to optionally reference matched Coinbase transaction IDs for cross-system reconciliation.

Current state: CoinTracker balances ledger is implemented and references cointracker_transaction_id, but it does not store Coinbase linkage.

Needed:

  • Add Coinbase linkage field(s) or a dedicated mapping table.
  • Build matching heuristics (timestamp window, asset, quantity, type).
  • Persist match confidence/status and allow unresolved rows.

5) Explain CoinTracker PnL vs Coinbase NAV deltas

Goal: provide a reproducible reconciliation report that explains differences between CoinTracker PnL and Coinbase NAV.

Current state: both views exist, but there is no dedicated decomposition/reporting workflow.

Needed:

  • Define exact reconciliation semantics (realized/unrealized, cash handling, fees, time windows, excluded assets).
  • Add command/report output that breaks deltas into categories (pricing basis, unmatched transfers, fees, synthetic rows, timing differences).
  • Add regression tests over fixed fixtures to prevent silent drift in reconciliation logic.

Completed Legacy TODO

  • USDC day-level grouping is available now via hdb cointracker gains analyze-usdc --interval day.

Development

  • npm run lint
  • npm run typecheck
  • npm run test
  • npm run validate:hdb:inputs
    • offline CSV parser validation for ${repoRoot}/data/input/*
    • optional input root override: npm run validate:hdb:inputs -- --input-root <dir>
  • npm run build
  • npm run release:check

Naming Topology

To keep files easy to scan as hdb grows, avoid generic repeated names.

  • Command registration files: register-<domain>-commands.ts
  • Command handlers: <domain>-handlers.ts
  • Command option schemas: <domain>-options.ts
  • Shared date/time helpers: date-range-utils.ts
  • DB entrypoint: db-client.ts
  • DB pool module: postgres-pool.ts
  • DB repositories: <domain>-repository.ts
  • DB SQL modules: <domain>-sql.ts
  • DB mapping modules: <domain>-mappers.ts
  • General rule: prefer explicit, domain-scoped filenames over register.ts, handlers.ts, or orders.ts.

Project workflow and standards are in CONTRIBUTING.md.