Firebase and Sqlite3 migration to Mongo & Postgres.

satyarth12 commented 2 months ago

@amolkittur Can you provide the current db schema in firebase as well as sqlite?

amolkittur commented 2 months ago

As of now, we are storing all the trades of the user in the SQLite database. The structure of the database is as follows:

Database Structure

We have two main databases: user_equity.db and user_derivatives.db, one for equity and one for derivatives.
Each strategy has its own table within these databases.
The schema/column names are consistent across all strategies:

Completed Trades Table

Column Name	Description
trade_id	Unique identifier
trading_symbol	Symbol of the traded asset
signal	Signal generated for the trade
entry_time	Time of entry
exit_time	Time of exit
entry_price	Entry price
exit_price	Exit price
hedge_entry_price	Price for hedging entry
hedge_exit_price	Price for hedging exit
trade_points	Trade points
qty	Quantity traded
pnl	Profit/Loss
tax	Tax applied
net_pnl	Net profit/loss

Holdings Table

If the trade has not exited it is considered as a holding

Column Name	Description
trade_id	Unique identifier
trading_symbol	Symbol of the held asset
signal	Signal generated for holding
entry_time	Time of entry
entry_price	Entry price
hedge_entry_price	Price for hedging entry
qty	Quantity held
margin_utilised	Margin utilized
tax	Tax applied
setup	Setup cost

Signals Table

Column Name	Description
trade_id	Unique identifier
trading_symbol	Symbol of the traded asset
signal	Generated signal
entry_time	Time of entry
exit_time	Time of exit
entry_price	Entry price
exit_price	Exit price
hedge_entry_price	Price for hedging entry
hedge_exit_price	Price for hedging exit
trade_points	Trade points
Comments	Additional comments

Extra Information Database

We have another database (signal_info.db) to store extra information about the strategies, including:

The columns remain the same as signals db plus the extra infromation
Parameters used to generate signals (e.g., cross_type, trade_view, ib_level, moving_avg, etc.)
Any additional information needed for the strategies

This structure allows us to maintain separate databases for equity and derivatives strategies while keeping consistent schemas across them. The extra information database provides flexibility to store strategy-specific details.

amolkittur commented 2 months ago

Now coming to firebase we have admin, market_info, strategies and trademan_clients collections

here are the sample collections

admin:{
  "NextTradeManId": "Tr16",
  "password": "a",
  "primary_accounts": {
    "AliceBlue": {
      "ApiKey": "asdadasd",
      "ApiSecret": "asdasd",
      "BrokerName": "asd",
      "BrokerPassword": "K@asdasd",
      "BrokerUsername": "asdasdasd",
      "SessionId": ".asd.-----",
      "TotpAccess": "adasdsdasd"
    },
    "Zerodha": {
      "ApiKey": "asdasd",
      "ApiSecret": "asdasdasd",
      "BrokerName": "asdasd",
      "BrokerPassword": "asdasdasd",
      "BrokerUsername": "rty",
      "SessionId": "ghj",
      "TotpAccess": "fg"
    }
  },
  "username": "a"
}

market_info:{
  "EquityQtyAmplifier": 1,
  "OBQtyAmplifier": 2,
  "OSQtyAmplifier": 1,
  "TradeView": "Bullish"
}

strategies:
{
  "AmiPy": {
    "Description": "Signals are generated using SuperTrend. Really!",
    "EntryParams": {
      "EMAPeriod": 324,
      "EntryTime": "09:25:00",
      "HeikinAshiMAPeriod": 13,
      "SupertrendMultiplier": 12,
      "SupertrendPeriod": 7,
      "TSLStepSize": 0.5
    },
    "ExitParams": {
      "AvgSLPoints": 50,
      "LastBuyTime": "14:50:00",
      "SLType": "StrategySL",
      "SquareOffTime": "14:55:00"
    },
    "ExtraInformation": {
      "HedgeDistance": 500,
      "Interval": "minute",
      "MultiLeg": "True",
      "QtyCalc": "DuringEntry",
      "StrategyParams": [
        "EMAPeriod",
        "HeikinAshiMAPeriod",
        "SupertrendMultiplier",
        "SupertrendPeriod",
        "TSLStepSize"
      ]
    },
    "GeneralParams": {
      "ExpiryType": "Weekly",
      "MaxMarginPerLot": 70000,
      "NiftyToken": "256265",
      "OrderType": "Market",
      "ProductType": "MIS",
      "Segment": "NFO",
      "StrategyType": "OS",
      "TimeFrame": "1Min"
    },
    "Instruments": [
      "NIFTY"
    ],
    "MarketInfoParams": {
      "OSQtyAmplifier": 1,
      "TradeView": "Bullish"
    },
    "NextTradeId": "AP236",
    "StrategyName": "AmiPy",
    "StrategyPrefix": "AP"
  },
  "ShortTerm": {
    "Description": "Stocks to buy for ShortTerm and is triggered every day",
    "EntryParams": {
      "EntryTime": "09:20:00",
      "SLMultiplier": 9
    },
    "ExitParams": {
      "RiskPerTrade": 9,
      "SLType": "Percentage"
    },
    "ExtraInformation": {
      "MeanReversionBBWindow": 20,
      "MeanReversionRSILength": 14,
      "MeanReversionRSIUpperThreshold": 55,
      "MomentumBBWindow": 20,
      "MomentumRSILength": 14,
      "MomentumRSIUpperThreshold": 50,
      "QtyCalc": "DuringEntry",
      "StocksPerStrategy": 3
    },
    "GeneralParams": {
      "ExpiryType": "Weekly",
      "OrderType": "Limit",
      "ProductType": "CNC",
      "SlOrderType": "Stoploss",
      "SlTransactionType": "SELL",
      "StrategyType": "Equity",
      "TimeFrame": "SingleEntry",
      "TransactionType": "BUY"
    },
    "Instruments": [
      "NSE"
    ],
    "MarketInfoParams": {
      "OBQtyAmplifier": 1,
      "OSQtyAmplifier": 1,
      "TradeView": "Bullish"
    },
    "NextTradeId": "ST39",
    "StrategyName": "ShortTerm",
    "StrategyPrefix": "ST",
    "TodayOrders": {
      "ST36_entry": {
        "EntryTime": "2024-09-03 09:30:07",
        "Setup": "SHORT_MOMENTUM",
        "Signal": "Long",
        "Status": "Open",
        "StrategyInfo": {
          "Direction": "Bullish"
        },
        "Symbol": "BGRENERGY",
        "TradeId": "ST36_LG_MO_EN"
      },
      "ST37_entry": {
        "EntryTime": "2024-09-03 09:30:13",
        "Setup": "SHORT_MOMENTUM",
        "Signal": "Long",
        "Status": "Open",
        "StrategyInfo": {
          "Direction": "Bullish"
        },
        "Symbol": "ANMOL",
        "TradeId": "ST37_LG_MO_EN"
      },
      "ST38_entry": {
        "EntryTime": "2024-09-03 09:30:15",
        "Setup": "SHORT_MOMENTUM",
        "Signal": "Long",
        "Status": "Open",
        "StrategyInfo": {
          "Direction": "Bullish"
        },
        "Symbol": "JETAIRWAYS",
        "TradeId": "ST38_LG_MO_EN"
      }
    }
  }
}

trademan_clients:
{
  "Tr1": {
    "Accounts": {
      "CurrentBaseCapital": 150001,
      "CurrentWeekCapital": -380.65,
      "Equity": {
        "CapitalAllocation": 100,
        "Equity_AccountValue": 247074,
        "Equity_FreeCash": 182923.35,
        "Equity_Holdings": 64151
      },
      "Portfolio": {
        "Portfolio_AccountValue": 247455,
        "Portfolio_FreeCash": 179177,
        "Portfolio_Holdings": 68278
      }
    },
    "Active": true,
    "Broker": {
      "ApiKey": "asdas",
      "ApiSecret": "fdg",
      "BrokerName": "Firstock",
      "BrokerPassword": "K@fghh",
      "BrokerUsername": "dfgdf",
      "SessionId": "dg",
      "TotpAccess": "ghjghj"
    },
    "Profile": {
      "AadharCardNo": "ert",
      "AccountStartDate": "",
      "BankAccountNo": "ert",
      "BankName": "ert Bank",
      "DOB": "1989-ert-13",
      "Email": "ert@gmail.com",
      "GmailPassword": "",
      "Name": "NOertE",
      "PANCardNo": "gthfgh",
      "PhoneNumber": "+ytu",
      "RiskProfile": {
        "AreaOfInvestment": [
          "Equity"
        ],
        "Commission": "Percentage",
        "DrawdownTolerance": "20",
        "Duration": "12",
        "WithdrawalFrequency": "OnRequest"
      },
      "pwd": "a",
      "usr": "nootanahegde@gmail.com"
    },
    "Strategies": {
      "Equity": {
        "LongTerm": {
          "AllocationPercent": 25,
          "LONG_COMBO": {
            "AllocationPercent": 50
          },
          "LONG_RATIO": {
            "AllocationPercent": 50,
            "Qty": 84,
            "TradeState": {
              "orders": [
                {
                  "avg_prc": "501.05",
                  "exchange_token": 13057,
                  "order_id": "24072900014912",
                  "order_status": "PASS",
                  "qty": 37,
                  "setup": "LONG_RATIO",
                  "tax": 22.29,
                  "time_stamp": "2024-07-29 13:57",
                  "trade_id": "LT7_LG_MO_EN"
                },
                {
                  "avg_prc": "75.48",
                  "exchange_token": 11667,
                  "order_id": "24072900014914",
                  "order_status": "PASS",
                  "qty": 248,
                  "setup": "LONG_RATIO",
                  "tax": 22.48,
                  "time_stamp": "2024-07-29 13:57",
                  "trade_id": "LT8_LG_MO_EN"
                },
                {
                  "avg_prc": "223.20",
                  "exchange_token": 857,
                  "order_id": "24072900014916",
                  "order_status": "PASS",
                  "qty": 84,
                  "setup": "LONG_RATIO",
                  "tax": 22.51,
                  "time_stamp": "2024-07-29 13:57",
                  "trade_id": "LT9_LG_MO_EN"
                }
              ]
            }
          }
        },
        "MidTerm": {
          "AllocationPercent": 50,
          "MID_TFEMA": {
            "AllocationPercent": 50
          },
          "MID_TFMOMENTUM": {
            "AllocationPercent": 50
          }
        },
        "ShortTerm": {
          "AllocationPercent": 25,
          "SHORT_MOMENTUM": {
            "AllocationPercent": 33,
            "Qty": 102,
            "TradeState": {
              "orders": [
                {
                  "avg_prc": "129.40",
                  "exchange_token": 19783,
                  "order_id": "24082100002253",
                  "order_status": "FAIL",
                  "qty": 31,
                  "setup": "SHORT_MOMENTUM",
                  "tax": 5.17,
                  "time_stamp": "2024-08-21 09:30",
                  "trade_id": "ST22_LG_MO_EN"
                },
                {
                  "avg_prc": "106.70",
                  "exchange_token": 13688,
                  "order_id": "24082600001647",
                  "order_status": "FAIL",
                  "qty": 38,
                  "setup": "SHORT_MOMENTUM",
                  "tax": 5.22,
                  "time_stamp": "2024-08-26 09:30",
                  "trade_id": "ST31_LG_MO_EN"
                }
              ]
            }
          }
        }
      }
    },
    "Tr_No": "Tr1"
  }
}

I have just uploaded 2 strategies. If there are no orders placed today we wont get the dict TodayOrders if orders are placed/ signals generated then only we will get the TodayOrders.

I have uploaded the sample data of a single user here you can see in the strategies there is only equity if the user opts for derivatives there will be an extra dict containing the strategy details similar to equity i.e allocation percent, risk etc and also if there are trades which have taken place today then only they will appear in the strategy under the TradeState. This are cleared at the end of the day when the data is written in the db and only those enteries without the exit of the trade stay in the db

satyarth12 commented 2 months ago

@omkarh25 @amolkittur

Postgres migration:

Single database, with three tables: Completed Trades, Holdings, Signals
Each table will have 3 new columns: Strategy Type, UserId, Component
Strategy Type: A string repr of a particular strategy name. Example: Amipy, LongTerm, ...
UserId: String repr of a user's id. Example: Tr1, Tr2, ....
Component: String repr of what service did the user chose. Example: derivatives, equity,...

Mongo migration:

Will keep the firebase's schema as it is, to avoid any complication in the current system.

amolkittur commented 2 months ago

@satyarth12 Arrange a meeting for this issue. We will divide this main story into sub tasks and start working on it

amolkittur commented 2 months ago

@satyarth12 Remember to add the API expiry date as well as password expiry date in the broker section. Also we need a mechanism to unhash the password to view it at the admin level

Serendipity2-0 / TradeManV1