As Binance doesn't allow Master Account see MSA account Future PNL Analysis, so we decide to clone Binance Future PNL Analysis page with Phoenix Live View to show all Account Future PNL

## Why we use Phoenix Live View for Binance Future PNL analysis page

### Real-time data handling

- Phoenix Live View has built-in Websocket management so we can update data realtime with price or position update
- Efficient handling of continuous data streams from Binance
- Automatic connection management and recovery

### Server-side state management

- Keeps sensitive trading data secure on the server
- Ensures calculation accuracy for PNL computations
- Prevents client-side manipulation of important data

### Complex calculations

- Handles all PnL calculations server-side
- Better precision for financial calculations
- Centralized calculation logic

### Development efficiency

- Single technology stack (Elixir)
- No need for separate frontend framework
- Simplified state management

## How to optimize query with timescale

### Data source

Base on [Binance Docs](https://www.binance.com/en/support/faq/how-are-pnl-calculated-on-binance-futures-and-options-pnl-analysis-dbb171c4db1e4626863ec8bc545be46a) we have compound data from 2 timescale tables: `ts_user_trades` and `ts_future_incomes`

- ts_user_trades: to calculate realized pnl, commission, and trading volume
- ts_future_incomes: to calculate funding fee and net inflow

### Timescale table

- ts_user_trades and ts_future_incomes are large data tables so if we use normal table with indexing it will be slower by time that why we use timescale to hyper chunks to prevent this issue

- ts_user_trades is hyper by 1 day
- ts_future_incomes is hyper by 30 days

### Use timescale style query to get summary data in date range

Ecto query to calculate PnL data from `ts_user_trades` and `ts_future_incomes`

```elixir
from(t in TsUserTrades,
 where: t.account_id in ^account_ids,
 where: t.time >= ^start_time and t.time <= ^end_time,
 group_by: [
 t.account_id,
 fragment("time_bucket('1 day', ?)::date", t.time)
 ],
 select: %{
 account_id: t.account_id,
 date: fragment("time_bucket('1 day', ?)::date", t.time),
 commission: fragment("COALESCE(-1 * ABS(SUM(?)), 0)", t.commission),
 realized_pnl: coalesce(sum(t.realized_pnl), 0),
 trade_volume:
 fragment(
 "COALESCE(SUM(CASE WHEN ? IS NOT NULL THEN ? ELSE 0 END), 0)",
 t.quote_qty,
 t.quote_qty
 )
 }
)

from(i in TsFutureIncomes,
 where: i.account_id in ^account_ids,
 where: i.time >= ^start_time and i.time <= ^end_time,
 group_by: [
 i.account_id,
 fragment("time_bucket('1 day', ?)::date", i.time)
 ],
 select: %{
 account_id: i.account_id,
 date: fragment("time_bucket('1 day', ?)::date", i.time),
 net_inflow:
 sum(fragment("CASE WHEN ? = 'TRANSFER' THEN ? ELSE 0 END", i.income_type, i.income)),
 received_funding_fee:
 sum(
 fragment(
 "CASE WHEN ? = 'FUNDING_FEE' AND ? > 0 THEN ? ELSE 0 END",
 i.income_type,
 i.income,
 i.income
 )
 ),
 paid_funding_fee:
 sum(
 fragment(
 "CASE WHEN ? = 'FUNDING_FEE' AND ? < 0 THEN ? ELSE 0 END",
 i.income_type,
 i.income,
 i.income
 )
 ),
 insurance_clear:
 sum(
 fragment(
 "CASE WHEN ? = 'INSURANCE_CLEAR' THEN ? ELSE 0 END",
 i.income_type,
 i.income
 )
 )
 }
)
```

Because timescale table will be spitted into multiple chunks so if we use normal query it will have timeout issue if range too long. So we have to use `time_bucket` to let it join multiple chunks.

### Create cronjob to fill ts_account_pnl_analysis

- We implement account_pnl_analysis_cronjob to backfill from today until oldest date has ts_user_trades order ts_future_incomes to calculate daily pnl

- For current day, we will run interval 1 hour to update data

- With this cronjob it will help us no need to recalculate from 2 tables every request so it will be fast and don't make database pressure

## User interface implementation

![Overview](assets/overview.jpg) _Figure 1: Future PNL Analysis Overview Tab_

![Detail](assets/detail.png) _Figure 2: Future PNL Detail Tab_

![Symbol Analysis](assets/symbol-analysis.png) _Figure 3: Future PNL Analysis Symbol Tab_

![Symbol Analysis](assets/funding-and-transaction.png) _Figure 4: Future PNL Funding and Transaction Tab_

Implementing Binance Futures PNL Analysis page with Phoenix LiveView to optimize development efficiency. This approach reduces the need for separate frontend and backend resources while enabling faster real-time data updates through WebSocket connections and server-side rendering.

Implement Binance Futures PNL analysis page by Phoenix LiveView

Due to increasing trading volume, the `user_trades` and `incomes` tables have grown significantly, causing slower performance in our reporting queries. To address this, we propose migrating these tables to TimescaleDB, which will allow us to partition the data into time-based chunks. This partitioning strategy should optimize our report query performance.

## We create new timescale tables

Because current tables have large data so we can't migrate it in one SQL Query so that why we have create new table with new timescale structure and this is new table structures

### New ts_user_trades

```sql
CREATE TABLE "public"."ts_user_trades" (
 "id" uuid NOT NULL DEFAULT uuid_generate_v4(),
 "buyer" bool,
 "commission" numeric,
 "commission_asset" text,
 "trade_id" int8 NOT NULL,
 "maker" bool,
 "order_id" int8,
 "price" numeric,
 "qty" numeric,
 "quote_qty" numeric,
 "realized_pnl" numeric,
 "side" text,
 "position_side" text,
 "symbol" text,
 "time" timestamptz NOT NULL,
 "time_unix" int8,
 "account_id" uuid,
 "is_locked_position" bool DEFAULT false,
 "created_at" timestamptz NOT NULL DEFAULT now(),
 "updated_at" timestamptz NOT NULL DEFAULT now(),
 CONSTRAINT "ts_user_trades_account_id_fkey" FOREIGN KEY ("account_id") REFERENCES "public"."accounts"("id"),
 PRIMARY KEY ("id","time")
);

-- Indices
CREATE UNIQUE INDEX ts_user_trades_account_id_symbol_trade_id_time_index ON public.ts_user_trades USING btree (account_id, symbol, trade_id, "time");

CREATE INDEX ts_user_trades_account_id_time_index ON public.ts_user_trades USING btree (account_id, "time");

CREATE INDEX ts_user_trades_account_id_symbol_time_index ON public.ts_user_trades USING btree (account_id, symbol, "time");

SELECT create_hypertable('ts_user_trades', 'time',
 chunk_time_interval => INTERVAL '1 day',
 if_not_exists => TRUE
);
```

#### ts_user_trades indices explain

**1. Unique index**

```SQL
CREATE UNIQUE INDEX ts_user_trades_account_id_symbol_trade_id_time_index ON public.ts_user_trades USING btree (account_id, symbol, trade_id, "time");
```

Three columns `account_id`, `symbol`, `trade_id` can detect duplicate data but we need to add time to unique index for hypertable also

**2. Index `account_id`, `time`**

```sql
CREATE INDEX ts_user_trades_account_id_time_index ON public.ts_user_trades USING btree (account_id, "time");
```

This index for optimize query `WHERE account_id = {id} AND time BETWEEN {time1} and {time2}`

**3. Index `account_id`, `time`, `symbol`**

```sql
CREATE INDEX ts_user_trades_account_id_symbol_time_index ON public.ts_user_trades USING btree (account_id, symbol, "time");
```

This index for optimize query `WHERE account_id = {id} AND symbol in {symbols} AND time BETWEEN {time1} and {time2}`

**4. Hypertable**

```sql
SELECT create_hypertable('ts_user_trades', 'time',
 chunk_time_interval => INTERVAL '1 day',
 if_not_exists => TRUE
);
```

User trading activity is sporadic, but when trades occur, they tend to cluster into periods of high volume within the same minute.

We choose interval 1 day to balance chunk number and size per chunk to make sure each chunk less than 300MB for optimized query

### New ts_future_incomes

```sql
CREATE TABLE "public"."ts_future_incomes" (
 "id" uuid NOT NULL DEFAULT uuid_generate_v4(),
 "account_id" uuid NOT NULL,
 "symbol" text,
 "income_type" text NOT NULL,
 "income" numeric,
 "asset" text,
 "info" text,
 "time" timestamptz,
 "time_unix" int8 NOT NULL,
 "trade_id" text,
 "tran_id" text,
 "is_locked_position" bool DEFAULT false,
 "is_notify" bool DEFAULT false,
 "created_at" timestamptz NOT NULL DEFAULT now(),
 "updated_at" timestamptz NOT NULL DEFAULT now(),
 CONSTRAINT "ts_future_incomes_account_id_fkey" FOREIGN KEY ("account_id") REFERENCES "public"."accounts"("id"),
 PRIMARY KEY ("id","account_id","income_type","time_unix")
);

-- Indices
CREATE UNIQUE INDEX ts_future_incomes_account_id_income_type_tran_id_time_unix_inde ON public.ts_future_incomes USING btree (account_id, income_type, tran_id, time_unix);

-- This index for optimize query WHERE account_id = {id} AND income_type = {income_type} AND time BETWEEN {time1} and {time2}
CREATE INDEX ts_future_incomes_account_id_income_type_time_index ON public.ts_future_incomes USING btree (account_id, income_type, "time");

-- This index for optimize query WHERE account_id = {id} AND income_type = {income_type} AND time BETWEEN {time1} and {time2} and symbol = {symbol}
CREATE INDEX ts_future_incomes_account_id_symbol_income_type_time_index ON public.ts_future_incomes USING btree (account_id, symbol, income_type, "time");

SELECT create_hypertable(
 'ts_future_incomes',
 'time_unix',
 chunk_time_interval => 604800000, -- 7 days in milliseconds
 create_default_indexes => false
)
```

#### ts_user_trades indices explain

**1. Unique index**

```SQL
CREATE UNIQUE INDEX ts_future_incomes_account_id_income_type_tran_id_time_unix_inde ON public.ts_future_incomes USING btree (account_id, income_type, tran_id, time_unix);
```

Avoid duplicate data

**2. Index `account_id`, `income_type`, and `time`**

```sql
CREATE INDEX ts_future_incomes_account_id_income_type_time_index ON public.ts_future_incomes USING btree (account_id, income_type, "time");
```

This index for optimize query `WHERE account_id = {id} AND income_type = {income_type} AND time BETWEEN {time1} and {time2}`

**3. Index `account_id`, `symbol`, `income_type`, and `time`**

```sql
CREATE INDEX ts_future_incomes_account_id_symbol_income_type_time_index ON public.ts_future_incomes USING btree (account_id, symbol, income_type, "time");
```

This index for optimize query `WHERE account_id = {id} AND symbol in {symbols} AND income_type = {income_type} AND time BETWEEN {time1} and {time2} AND `

**4. Hypertable**

```sql
SELECT create_hypertable(
 'ts_future_incomes',
 'time_unix',
 chunk_time_interval => 604800000, -- 7 days in milliseconds
 create_default_indexes => false
)
```

We store two types of transactions in ts_future_incomes: FUNDING_FEE and TRANSFER.

For FUNDING_FEE transactions:

- Each symbol generates 4-8 fees per account per day
- With 320 positions per account across 200 accounts, this results in:
 - Daily records: 320 × 200 × 8 = 512,000
 - Weekly chunk size: 512,000 × 7 = 3,584,000

The resulting chunk size is within acceptable limits.

## Migration plan

### Dual write

To make sure new tables have new data same as old tables we insert both of tables to make sure we don't lost new data and can rollback to old table if we have problem

### Backfilling

#### Create migrator to import data from old table from new timescale table

**How migrator work**

1. Compare total record between old and new
2. If not equal we will get min time from new ts_user_trades to continue backfill
3. Get data from old table with query `WHERE time < {min_time} ORDER BY time DESC, trade_id DESC OFFSET {offset} LIMIT 1000`
4. Increase min_time if latest record oldest than min_time 1 hours to reset offset back to 0 (The offset is bigger, the query time is longer)
5. Backfill until no data in query

```mermaid
flowchart TD
 A[Start] --> B[Compare total records between old and new tables]
 B --> C{Records equal?}
 C -->|Yes| D[End]
 C -->|No| E[Get min timestamp from new ts_user_trades]
 E --> F[Query old table with: WHERE time < min_time ORDER BY time DESC, trade_id DESC OFFSET offset LIMIT 1000]
 F --> G[Process batch]
 G --> H{Latest record > min_time - 1hr?}
 H -->|Yes| I[Reset offset to 0 Update min_time to latest record time]
 H -->|No| J[Increase offset by 1000]
 I --> K{Query returned data?}
 J --> K
 K -->|Yes| F
 K -->|No| D
```

### Validate data

We need to replace new query to old query function by function to retest to make sure correct data and acceptable query time

### Change primary table to new table

After everything is work fine, we can replace primary table to new table then consider to remove old tables if needed to save data storage

How do we migrate normal table to timescale table to optimized data storage