# DB content corresponds with UI
###### tags: `Nogle QA`
## Personal Information

### Account


| Item | Table | Column Name |
| ---- | ----- | ----------- |
| Email | f_user | femail |
| Username | f_user | username|
| Telegram Username | f_user | telegramusername|
| Reffered by |f_user<br>f_user_referral_code| referral_code <br> referral_code|
| Type | f_user | type <br> 1: individual<br>2: merchant <br> 3: finbee|
| Account Status | f_user | account status<br>1: Normal<br>3: Blocked<br>5: Deleted<br>6: Suspend<br>7: Locked<br>8: Not Tradable |
| Verification Status | f_user<br>f_user_kyc | is_kyc <br> kyc_v2_level|
| Trading Currency | f_user | tradecurr |
| My Fees | f_user_fees | maker_fees<br>taker_fees

### BTSE Benefits


| Item | Table | Column Name |
| ---- | ----- | ----------- |
| Available Balance|user_coin_wallet<br>coinname = 'BTSE'|total|
| Locked Balance |Not found|N/A|
| List of Benefits | f_user_fees| market = 'DEFFUTURES'<br>market = 'DEFSPOT'|
| Referral | f_user | referral_rate|
### Security


| Item | Table | Column Name |
| ---- | ----- | ----------- |
| Basic Authentication | f_user | floginpassword |
| Two-Factor Authentication | f_user | fgoogleauthenticator<br>fgoogleurl|
| Device Management | user_device | device_info<br>ip|
| Account Activity | f_log_user_action | fos<br>fbrowser<br>ip|

### Verification


| Item | Table | Column Name |
| ---- | ----- | ----------- |
| Photo ID | kyc_personal | id_confirmation<br>onfido/admin verified: 3<br>else: 1|
| Selfie | kyc_personal | id_selfie_confirmation<br>onfido/admin verified: 3<br>else: 1|
| Proof of Address | kyc_personal | address_confirmation<br>admin verified: 3<br>else: 1|
| Country of Residence | kyc_personal| residence_country |
| Occupation | Not found | N/A|
| Source of Funds | Not found | N/A|

### My Payment


| Item | Table | Column Name |
| ---- | ----- | ----------- |
| Bank Account | f_user_bankinfo | paymentway<br>fname<br>phone_number<br>|
| Credit Card | credit_card | alias<br>card_number <br> card_type<br>status|

### My API Key


| Item | Table | Column Name |
| ---- | ----- | ----------- |
| API Key Name | apikey_user | api_key_name<br>api_key<br>createtime<br>status==1|

## Wallets

### My Wallet


| Item | Table | Column Name |
| ---- | ----- | ----------- |
| My Wallet |user_coin_wallet|wallet_name = "SPOT@"|
| Asset | user_coin_wallet | coninname<br>|
| Total Balance | user_coin_wallet | total|
| Available Balance | user_coin_wallet | $$ total - frozen - openorder $$|

- frozen:
- pendding withdraw
- not effected by openorder

### Future Wallet
#### Wallets

| Item | Table | Column Name |
| ---- | ----- | ----------- |
| Future Wallet | user_coin_wallet | wallet_name =<br> "CROSS@" or "ISOLATED@" |
| Wallets | user_coin_wallet | wallet_name|
| Total Vaalue | user_coin_wallet | total |
| Assets in use | Compute in engine | futures websocket: assetsInUse in wallet |

$$ Assets\ in\ use = Unrealized\ P\& L + Position\ Margin + Active\ Orders\ Margin$$

#### Assets

| Item | Table | Column Name |
| ---- | ----- | ----------- |
| Future Wallet | user_coin_wallet | wallet_name =<br> "CROSS@" or "ISOLATED@" |
| Asset | user_coin_wallet | coinname |
| Total Balance | user_coin_wallet | $$ \Sigma total\ wallet $$ |
| Assets in use | Compute in engine |none

### Earn


| Item | Table | Column Name |
| ---- | ----- | ----------- |
| Currency | user_invest_wallet | investment_currency |
| Total Subscrubed | Compute in Engine | $$ \Sigma investment\_amount $$and transfer to USD |
| Total Amount | user_invest_wallet | $$ \Sigma investment\_amount $$ |
| Total Interest Earned | user_invest_wallet | $$ \Sigma total\_interested\_accrued $$|
| Type | user_invest_wallet | product_type<br>Flexible: 11<br>Fixed: 12<br>Stake: 13<br>Lend: 16|
| Auto-renewal | user_invest_wallet | auto_renew|
| Annualized Interest Rate | user_invest_wallet<br>interest_rate_history | current_interest_rate|
| Principal | user_invest_wallet | current_amount|
- Total Interest Earned:
Get sum of each currency and convert to USD

    select interest_payout_currency ,SUM(total_interested_accrued)
    from public.user_invest_wallet uiw 
    where username = 'jackchou'
    group by interest_payout_currency 

- open-term: means an agreement with no fixed date for the end of the date.

## History
==Any history is relevant to **trading** should be searched in txn database.==
**Note: <br>"trade" records recent 30 days<br>"trade_ar" records all of transactions**
### Spot Trade (txn)

| Item | Table | Column Name |
| -------------- | ----- |:------------------------------------------- |
| Type | trade_ar | order_type:L,M,O<br>order_mode:B,S |
| Fill Price | trade_ar | price |
| Order Size | trade_ar | original_amount |
| Fill Size | trade_ar | amount |
| Fees | trade_ar | fee_amount |
| Taker<br>Maker | trade_ar | order_user_initiated = true<br>maker = true |
| Total Buy&Sell | trade_ar | SUM(amount)
| Net Amount | trade_ar | Buy: amount-fee_amount<br> Sell: price*amount-fee_amount|
- Total Buy & Sell

    select t.order_mode ,SUM(amount) as amount
    from public.trade_ar t 
    where username ='jackchou' and crypto = 'BTC'
    group by order_mode

### Spot Wallet (exg and txn)

| Item | Table | Column Name |
| ---- | ----- | ----------- |
| Deposit | Balance_audit | transaction_type:1 |
| Withdraw|Balance_audit|transaction_type:2|
|Receive|Balance_audit<br>Wallet_history_new|transaction_type:5<br>to_transfer_type:500001
|Send|Balance_audit<br>Wallet_history_new|transaction_type:6<br>from_transfer_type:500002
| Referral|Balance_audit<br>Wallet_history_new|transaction_type:10<br>from_transfer_type:500113
| Convert |Balance_audit<br>Wallet_history_new|transaction_type:12<br>from_transfer_type:121|
| Transfer Out|Wallet_history_new|from_transfer_type:105
| Transfer In|Wallet_history_new|from_transfer_type:105
| Invest |Wallet_history_new|from_transfer_type:500102
| Redeem |Wallet_history_new|from_transfer_type:500106
| Paid |N/A|N/A|
| Get Paid|N/A|N/A|

| |Balance_audit | Wallet_history_new |
| ---- | ------ |--|
|Deposit |O|X|
|Withdraw |O|X
|Receive|O|O|
|Send|O|O|
|Referral|O|X|
|Convert|O|O|
|Transfer|X|O|
|Earn|X|O|

### Future (txn)


| Item | Table | Column Name |
| ---- | ----- | ----------- |
| Market Name | trade_future | market_name |
| Total Buy | trade_future |$$ \Sigma amount $$order_mode = 66|
| Total Sell| trade_future |$$ \Sigma amount $$order_mode = 83 |
| Position Remaining | trade_future | position_remaining|
| Fees | trade_future | $$ \Sigma fee\_amount $$|
| Type | trade_future | order_type<br>limit:76<br>market:77<br>index:80|
| Order Price | trade_future | max_price |
| Fill Price | trade_future | price |

### Earn

| Item | Table | Column Name |
| ---- | ----- | ----------- |
| Product | invest_prodoct<br>where<br> id = product_id in user_invest_wallet | name |
| Type | user_invest_wallet | product_type<br>Flexible: 11<br>Fixed: 12<br>Stake: 13<br>Lend: 16|
| Transaction Type | invest_history|result_type<br>Deposit: 500102<br>Interest Payout: 500105<br>Redemption: 500106<br>Early Redemption: 500107|
| Amount | user_invest_wallet | if Interest Payout:<br>total_redeeemed_amount<br>else:<br>investment_amount|
| Interest Rate | user_invest_wallet | current_interest_rate|
| Total Amount | user_invest_wallet |current_amount |

#### Early Redeem:

Stake -> 500114 -> after 24 hrs -> 500115
Fixed,Lending -> 500107

If result_type == 500105(Interest Payout):
    current_amount = total_interest_unpaid + totala_redeemed_amount