---
name: bfc-run-prod-user-locally
description: |
  Run BFC (cost basis computation) locally using production user data dumped from BigQuery.
  Use when: (1) profiling BFC memory/performance for a specific user, (2) debugging a production
  BFC failure offline, (3) testing in-memory lot processing changes against real data.
  Covers the full pipeline: BQ dump, local import, BFC enqueue, monitoring, and cleanup.
author: Di Wen
version: 1.0.0
date: 2026-04-13
---

# Run BFC Locally with Production User Data

## Usage

```
# Full pipeline: dump a user from BQ, import locally, and run BFC end-to-end
use /bfc-run-prod-user-locally to dump a sample user with around 10k txns and run bfc locally

# After a full BFC run has completed, cached accounting transactions exist in GCS.
# Run only compute_cost_basis directly (skips ~18 min of derived data/transfer detection):
use /bfc-run-prod-user-locally to run compute_cost_basis directly

# Same as above but with memray memory profiling enabled:
use /bfc-run-prod-user-locally to run compute_cost_basis with memray enabled

# For whale users (~1M txns): borrow production's GCS cache instead of running full BFC.
# No full BFC run needed — just dump user data, copy cache, and run compute_cost_basis:
use /bfc-run-prod-user-locally to find a whale user with ~1M txns who had a recent successful
full-recomp BFC run, copy their accounting transaction cache from production GCS, and run
compute_cost_basis locally with memray profiling
```

## Problem

Running BFC against realistic production data locally for profiling, debugging, or performance testing.
Local seed data is insufficient — it has ~230 transactions vs production users with 10k-100k+.

## Prerequisites

Standard local dev setup (Postgres, Redis, mise, uv, pitchfork) plus:

1. **Google Cloud / BigQuery access**:
   - `gcloud` and `bq` CLI installed (`brew install google-cloud-sdk`)
   - Authenticated: `gcloud auth application-default login`
   - IAM: read access to `data-tools-prod-a9s3.postgres.*` dataset
2. **`temporal` CLI** installed — for listing/terminating workflows (`brew install temporal`)

Scripts live at `~/.ai-config/claude/skills/bfc-run-prod-user-locally/scripts/`.

## Step 1: Dump Data from BigQuery

```bash
cd ~/.ai-config/claude/skills/bfc-run-prod-user-locally/scripts
./dump.sh <USER_ID>
```

This exports 7 tables to `scripts/data/<USER_ID>/`:
- `users.json` — single user row
- `accounts.json` — user's exchange/wallet accounts
- `wallets.json` — individual wallets within accounts
- `custom_wallets.json` — user-defined wallet groupings
- `transactions.json` — all user transactions
- `flows.json` — individual flows (sends/receives) within transactions
- `nfts.json` — NFT reference data referenced by user's flows (via UAID join)

Default max rows: 200,000. Override with second arg: `./dump.sh <USER_ID> 500000`

### Verifying the dump

```bash
for f in scripts/data/<USER_ID>/*.json; do
  echo "$(basename $f): $(python3 -c "import json; print(len(json.load(open('$f'))))")"
done
```

## Step 2: Import into Local Postgres

Run from the **project repo root** (so `uv run` picks up the virtualenv with `psycopg2`):

```bash
SCRIPTS=~/.ai-config/claude/skills/bfc-run-prod-user-locally/scripts

# Dry run first
uv run python $SCRIPTS/import.py <USER_ID> --dry-run

# Import for real
uv run python $SCRIPTS/import.py <USER_ID>
```

The import script handles:
- **Type conversion**: BQ returns all values as strings — converts bools, ints, floats, JSONB
- **PII anonymization**: email → `anon_{id}@test.local`, auth0_sub → NULL, partner_id → NULL
- **NFT `on_chain_id` computation**: `{blockchain_network}:{mint_address}[:{token_id}]` (exists in local DB but not in BQ)
- **NFT ID conflict resolution**: Local seed data may have the same NFT under a different auto-increment ID. The script detects conflicts by `(blockchain_network, mint_address, token_id)` and UPDATEs the local ID to match production so flow UAIDs resolve correctly.
- **Insert order**: Respects FK dependencies — nfts → users → accounts → wallets → custom_wallets → transactions → flows

## Step 3: Start BFC Services

```bash
pitchfork start temporal-server --force
sleep 5
pitchfork start bfc-scheduler bfc-local-worker --force
```

Wait ~15-20s, then verify:
```bash
pitchfork logs bfc-local-worker --raw -n 20 | grep "Starting the temporal bfc worker"
```

Clear any stale workflows:
```bash
temporal workflow list --query 'ExecutionStatus="Running"'
temporal workflow terminate --workflow-id <id> --reason "Stale"
```

## Step 4: Enqueue BFC

```bash
mise exec -- flask enqueue-cost-basis-from-gist --user-ids=<USER_ID> --diff-context-source=SYNC
```

## Step 5: Monitor

```bash
# Follow worker logs (filter noise)
pitchfork logs bfc-local-worker --raw -f | grep -iE "INFO|ERROR" | grep -v "API not enabled\|heartbeat\|dev_secrets\|google_crc32c\|OTEL\|metrics to localhost"
```

Key signals to watch for:
- Activity progression: `transaction_derived_data_builder` → `transfer_detection` → `cost_basis` → `compute_portfolio`
- Success: `instrument_bfc_workflow_result_activity: BFC run completed for user: ... "status": "SUCCESS"`
- Completion: `Workflow completed for user_id: ...`

Check for errors:
```bash
pitchfork logs bfc-local-worker --raw -n 200 | grep -iE "error|exception|traceback" | grep -v "API not enabled\|heartbeat\|dev_secrets\|google_crc32c\|OTEL\|metrics to localhost"
```

## Cleanup

```bash
SCRIPTS=~/.ai-config/claude/skills/bfc-run-prod-user-locally/scripts

# Remove all imported AND BFC-generated data
uv run python $SCRIPTS/import.py <USER_ID> --cleanup

# Dry run cleanup
uv run python $SCRIPTS/import.py <USER_ID> --cleanup --dry-run
```

Cleanup deletes in order: BFC output tables (lots, tax summaries, callouts, etc.) → input tables (flows → transactions → custom_wallets → wallets → accounts → users → nfts by ID).

## Recommended: Tune Local Postgres

Default Homebrew Postgres settings (`work_mem=4MB`, `shared_buffers=128MB`) cause bad
query plans for large datasets. **Strongly recommended** before running BFC locally.

Edit `/opt/homebrew/var/postgresql@17/postgresql.conf` (adjust path for your PG version):

```
shared_buffers = 4GB            # was 128MB
work_mem = 256MB                # was 4MB — most impactful for BFC
maintenance_work_mem = 512MB    # was 64MB
effective_cache_size = 48GB     # was 4GB (adjust to ~75% of your RAM)
```

Then restart: `brew services restart postgresql@17`

The `work_mem` change is the most important — at 4MB, Postgres can't hash-join and falls
back to slow nested-loop plans for the LATERAL JOIN in `transaction_derived_data_builder`.

## Known Issues

### Slow LATERAL JOIN in transaction_derived_data_builder
`transaction_derived_data_builder` uses a LATERAL JOIN that can be slow locally if Postgres
chooses the wrong index. Tuning `work_mem` (see above) fixes the bad query plan. This
activity dominates wall time for full BFC runs.

### NFT Reference Data
NFTs are global reference data (no `user_id` column). The dump script pulls only NFTs referenced by the user's flows via UAID join. Local seed data may have ~1,359 NFTs vs millions in production. The import handles ID conflicts automatically.

### BQ Column Differences
Some local Postgres columns don't exist in BQ (e.g., `on_chain_id` on nfts). The import script computes these during import.

## Finding Users to Test

Use BigQuery to find users by transaction count:
```bash
bq query --use_legacy_sql=false --max_rows=10 '
SELECT u.id, u.email, tc.transaction_count
FROM `data-tools-prod-a9s3.postgres.users` u
JOIN (
  SELECT user_id, COUNT(*) as transaction_count
  FROM `data-tools-prod-a9s3.postgres.transactions`
  GROUP BY user_id
) tc ON u.id = tc.user_id
WHERE u.country_id = 1  -- US users
  AND tc.transaction_count BETWEEN 1000 AND 5000
ORDER BY tc.transaction_count DESC
LIMIT 10
'
```

## Run Cost Basis Only (Skip Everything Else)

Runs `CostBasisCalculationService.compute_cost_basis()` directly using cached
accounting transactions from GCS. No Temporal workers needed. Two ways to get the cache:

### Option A: After a local full BFC run

**Requires Steps 1-5 above.** The full run caches accounting transactions in GCS
(`coin-tracker-experiments-accounting-transactions-cache`). The
`BatchedFinancialCalculatorWorkflow` does not clean up this cache, so subsequent
runs can reuse it.

### Option B: Borrow production's GCS cache (for whale users)

For whale users (~1M txns), running the full BFC pipeline locally is impractical.
Instead, copy the accounting transaction cache directly from the production bucket.

**1. Find a whale user with a recent successful full-recomp BFC run:**
```bash
bq query --use_legacy_sql=false --project_id=data-tools-prod-a9s3 --max_rows=10 '
SELECT
  JSON_VALUE(tags, "$.user_id") AS user_id,
  CAST(JSON_VALUE(tags, "$.processed_transaction_count") AS INT64) AS processed_txn_count,
  CAST(JSON_VALUE(tags, "$.user_transaction_count") AS INT64) AS user_txn_count,
  JSON_VALUE(tags, "$.actual_recomp_date") AS actual_recomp_date,
  CAST(JSON_VALUE(tags, "$.e2e_workflow_duration_in_seconds") AS FLOAT64) AS duration_secs,
  timestamp
FROM `data-tools-prod-a9s3.application_events.coin_tracker_events`
WHERE event_name = "ct.accounting.bfc.runs"
  AND JSON_VALUE(tags, "$.status") = "SUCCESS"
  AND JSON_VALUE(tags, "$.actual_recomp_date") = "0001-01-01"
  AND CAST(JSON_VALUE(tags, "$.processed_transaction_count") AS INT64) BETWEEN 900000 AND 1100000
  AND timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
ORDER BY timestamp DESC
LIMIT 10
'
```

**2. Verify production cache exists:**
```bash
gsutil ls gs://coin-tracker-production-accounting-transactions-cache/<USER_ID>/
```

**3. Copy to the experiments bucket (used by local dev):**
```bash
# Sequential copy (reliable on macOS; parallel -m can stall)
for f in $(gsutil ls gs://coin-tracker-production-accounting-transactions-cache/<USER_ID>/); do
  gsutil cp "$f" "gs://coin-tracker-experiments-accounting-transactions-cache/<USER_ID>/$(basename $f)"
done
```

**4. Dump & import user data** (only user + wallets are strictly needed):
```bash
SCRIPTS=~/.ai-config/claude/skills/bfc-run-prod-user-locally/scripts
cd $SCRIPTS && ./dump.sh <USER_ID>
cd <project-root> && uv run python $SCRIPTS/import.py <USER_ID>
```

**5. Run compute_cost_basis** (same as Option A below).

### Running

```bash
SCRIPTS=~/.ai-config/claude/skills/bfc-run-prod-user-locally/scripts

# Basic run
mise exec -- python $SCRIPTS/run_cost_basis_only.py <USER_ID>

# With memray (profiles only compute_cost_basis, not GCS fetches)
mise exec -- python $SCRIPTS/run_cost_basis_only.py <USER_ID> --memray

# With ddtrace
mise exec -- ddtrace-run python $SCRIPTS/run_cost_basis_only.py <USER_ID>
```

The script fetches cached accounting transactions from GCS, builds transaction counts
(lightweight DB query), then calls `CostBasisCalculationService.compute_cost_basis()`
directly — no Temporal, no worker needed.

**Clean up lots before re-running** (otherwise lots accumulate):
```bash
psql -d coin_tracker_development -c "
  DELETE FROM incremental_created_lots WHERE user_id = <USER_ID>;
  DELETE FROM incremental_available_lots WHERE user_id = <USER_ID>;
  DELETE FROM incremental_disposed_lots WHERE user_id = <USER_ID>;
  DELETE FROM incremental_created_lots_v2 WHERE user_id = <USER_ID>;
  DELETE FROM incremental_available_lots_v2 WHERE user_id = <USER_ID>;
  DELETE FROM incremental_disposed_lots_v2 WHERE user_id = <USER_ID>;
"
```

## Tested Users

| User ID | Txn Count | Status | Notes |
|---------|-----------|--------|-------|
| 2427049 | ~59,328 | SUCCESS | Full BFC: ~20 min. Cost basis only: ~41s (batch 0: 50k txns/32.7s, batch 1: 9k txns/5.5s). 164,124 total lots. |
| 3491356 | ~1,004,063 | SUCCESS | Used Option B (production cache copy). 15 batches, 311 MB cache. Cost basis only: ~419s (~7 min) local vs ~1,242s (~21 min) on production. 2,362,007 total lots. No full BFC run needed. |