# DB Content and Introduction

###### tags: `Nogle QA`

## Balance_audit

### Scenarios
- Deposit
- Withdraw
- Convert
- Send to
- Received from

### Content
| Column | Comment | Details |
| -------- | -------- | -------- |
| client_amount | delta of amount in user bank |if deposit, equals to the input amount in UI|
| bank_charge | the fee of bank charges |dafault is 0.01% of the client_amount|
| received_amount | the amount bank recieved| N/A|
| fees | the fee of platform plus bank chrage(?)| dafault is 0.05% of the received_amount |
| amount | delta of amount in BTSE | N/A |
|transaction_type|N/A|1: deposit <br> 2: withdraw <br> 5: received from <br> 6: send to <br> 11: Withdraw Refund<br>12: convert
|status|N/A|1: open<br>2: locked<br>3: pending approval <br>6: admin cancel <br>10: complete <br>17: user not confirmed|

The relationship between Amount, Received_amount, Client_amount, bank_charge and fees in **deposit**:

$$
client\_amount - bank\_charge = received\_amount
$$
$$
received\_amount - fees = amount
$$

The relationship between Amount, Received_amount, Client_amount, bank_charge and fees in **withdraw**:

$$
amount - fees - bank\_charge = client\_amount
$$
$$
received\_amount = 0
$$

### SQL Code

SELECT * FROM public.balance_audit ba
WHERE username = 'jackchou'
ORDER BY update_date_time desc

## Wallet_history_new

### Scenarios

- Convert
- Send to
- Transfer
- Earn

### Content
- Every transaction will return 2 new rows, one for "from" and the other for "to".

### SQL Code

SELECT * FROM public.wallet_history_new whn 
WHERE from_username = 'jackchou'
ORDER BY "timestamp" desc

## User_coin_wallet
#### Each coin only shows once
### Scenarios

- Deposit
- Withdraw
- Convert
- Send to
- Received from
- Transfer

### Content
| Column | Comment | Details |
| ------ | ------- | ------- |
| coin_id| coin id |1: ETH<br>2: BTC<br>4: USD<br>79: USDT<br>119: BTSE|
| frozen | pendding withdraw | doesn't change after placing buy orders |
| openorder | buy / sell order | cross account doesn't change after ordering |

### Available Balance
$$
Available\ Balance = total - frozen - openorder
$$


### SQL Code

SELECT * FROM public.user_coin_wallet ucw 
WHERE username = 'jackchou'
ORDER BY  gmt_modified desc

/* check available balance */
SELECT ucw.coinname,wallet_name,total,frozen,openorder,total - frozen - openorder , gmt_modified FROM public.user_coin_wallet ucw 
WHERE username = 'jackchou'
ORDER BY  gmt_modified desc

## f_user

### Scenarios

- Every account infomation and setting

### Content

| Column | Comment | Details |
| -------- | -------- | -------- |
| fstatus | account status | 1: Normal<br>3: Blocked<br>5: Deleted<br>6: Suspend<br>7: Locked<br>8: Not Tradable|
|kyc_level | Identity Verification | 待補 |
|level | vip level| Same as dashboard vip level|
|type| user identity | 1: user<br>2: merchant <br> 11: finbee|
| referral_rate | BTSE Benefits | Same as BTSE Benefits referral rate|

### SQL Code

SELECT * FROM public.f_user fu 
WHERE username = 'jackchou'

## f_user_fees

### Scenarios

- Check how much will be taken in every transaction.

### Content

| Column | Comment | Details |
| ------ | ------- | ------- |
| market | type of coins | N/A|
| maker_fees | rate that will be taken | market: BTC -> same as BTSE Benefits UI|
| taker_fees | rate that will be taken |market: BTCPFC -> same as BTSE Benefits UI|

### SQL Code

SELECT * FROM public.f_user_fees fuf 
WHERE username = 'jackchou'