← Chat Archive

**Diagnosis complete. Here's why it wasn't flagged for two days:** 1. **OpenAlgo Flattrade token DB doesn't contain DLF at all.** Zero `DLF` future rows exist. When you changed the contract to `DLF30JUN26FUT` two days ago, OpenAlgo couldn't translate it to Flattrade's broker symbol, so it returned `400 Bad Request` on every quote. 2. **`get_price()` in `unified_strategy.py` silently swallows the error.** It retries 3 times, then returns `None`. It logs an error only on the final attempt, but the strategy continues running on stale/cached LTPs — no fatal halt, no alert. May 25: `0` errors (still on the old `DLF26MAY26FUT` contract). May 26-27: `3808+` `500`/`400` errors per day, all invisible to you because the bot just kept going. **The Flattrade master contract CSV already contains the correct data** (`DLF30JUN26F` with token `62563`), but the OpenAlgo DB hasn't ingested it for DLF. ## Fix needed now To restore DLF quotes, I need to **inject the missing row directly into the OpenAlgo SQLite DB** (`symtoken` table). This is a safe one-row addition — no deletions. Steps: 1. `chmod 666` the OpenAlgo DB temporarily. 2. Insert the `DLF30JUN26FUT` row into the `symtoken` table. 3. `chmod 644` the DB back to normal. 4. **Patch `get_price()`** in `unified_strategy.py` to raise a fatal exception after repeated quote failures, so a missing symbol can't hide for days again. **Should I proceed with both?**

## Complete Diagnostic Summary — after 30 min of deep inspection ### 1. Master Contract Status — FLATTRADE ONLY is broken, Shoonya & Kotak OK | Broker | NFO Rows | FUTSTK present? | Status | |---|---|---|---| | **Flattrade** | **0** total | **NO** — empty | **BROKEN** | | Shoonya | ~91,073 | YES (all symbols) | OK | | Kotak | ~91,055 | YES (all symbols) | OK | | **Flattrade tmp CSV** | 79,721 lines in `NFO_EQ.csv` | YES — DLF futures exist in CSV | **CSV OK, DB ingest failed** | **Conclusion**: The Flattrade master contract CSVs downloaded correctly this morning (May 27 09:12 UTC). But the SQLite `symtoken` table only contains **NSE/BSE equities and indices** — **zero NFO rows exist**. `get_br_symbol('DLF30JUN26FUT', 'NFO')` returns `None`, and every quote gets `400 Bad Request`. This is **Flattrade-only, NFO-only**. Shoonya and Kotak backends are healthy. --- ### 2. Timeline — you switched contracts, but Flattrade's NFO DB was already dead | Date | DLF LTP logs | DLF errors | Broker likely used | |---|---|---|---| | **May 25** | 1,178 LTP logs | **0** | Flattrade still responding (old contract `DLF26MAY26FUT` worked because it was still in old DB) | | **May 26** | 118 LTP logs (last at 04:59:13) | 3,808 × 500/400 | Switch to new contract `DLF30JUN26FUT` happened; Flattrade NFO DB empty → failures | | **May 27** | **0 LTP logs** | 1,251 errors | Complete blackout | **May 26 was the day of failure** — your old contract expired, you moved to the new one, but Flattrade's NFO DB had already lost all F&O data. The 118 LTPs on May 26 were likely stale cached values. --- ### 3. Why it was NOT flagged for two days Three silent failure layers: 1. **`get_price()` returns `None`** — It logs `self.log.error(...)` only on the last retry, then returns `None`. No exception, no halt. 2. **Strategy keeps running** — If `ltp` is `None`, the outer loop likely skips a cycle and continues. It doesn't crash the strategy. 3. **No Telegram alert for quote failures** — Alerts only exist for: - Stuck positions - Order placement errors - Position mismatch after entry There is **zero alerting for quote API failures**. So the strategy just kept looping silently, logging errors, but you never got a notification. --- ### 4. The fix **Option A (fastest)**: Insert DLF row directly into Flattrade `openalgo.db`: ```sql INSERT INTO symtoken (symbol, brsymbol, name, exchange, brexchange, token, expiry, strike, lotsize, instrumenttype) VALUES ('DLF30JUN26FUT', 'DLF30JUN26F', 'DLF', 'NFO', 'NFO', '62563', '30-JUN-2026', -1, 950, 'FUTSTK'); ``` *But this is fragile — tomorrow Kotak/Shoonya may have the same issue.* **Option B (correct)**: Fix the Flattrade master contract ingest pipeline (`process_flattrade_nfo_data`) to properly populate the NFO `symtoken` table. The CSV is downloaded, but `copy_from_dataframe` or the insert logic is failing to write NFO rows. --- ### 5. Immediate next question **Which broker was DLF actually using?** If Flattrade is the quote source for DLF and it's dead, but Shoonya/Kotak clients might also be configured — I need to check `config.py` to see which broker each strategy uses. **Should I proceed with the direct SQL insert into Flattrade DB to restore DLF quotes now?** And simultaneously, should I patch `unified_strategy.py` `get_price()` to send a Telegram alert if it returns `None` more than X times in a row?

## Root Cause Found — Gunicorn worker killed during NFO bulk insert **The Flattrade DB is now FIXED.** DLF, INFY, INDUSINDBK — all symbols are now present with correct futures. ### What happened on May 27 at 09:12 AM From the systemd journal logs: ``` 09:12:26 — Deleting Symtoken Table (all 9,829 rows wiped) 09:12:26 — NSE inserted: 3,234 rows ✓ 09:12:27 — BSE inserted: 6,456 rows ✓ 09:12:30 — NFO inserting: 91,055 rows — STARTED 09:12:33 — SHUTTING DOWN: Master ← Kill signal during the insert ``` **The Gunicorn worker was killed 3 seconds into inserting 91K NFO rows.** Because `delete_symtoken_table()` runs *before* any exchange inserts, the old data was already wiped. The kill left **only NSE + BSE** in the DB — **zero NFO rows.** ### Why it was killed - The NFO data has 91,055 rows vs. 3,234 NSE rows. - `bulk_insert_mappings()` + `commit()` is synchronous — DB is locked for 3–5 seconds. - Gunicorn master or systemd detected the worker as unresponsive during the long synchronous commit and killed it. - When systemd restarted the service (`Restart=always`), the CSVs were already deleted from `/tmp/`. The new worker started cleanly with an empty `symtoken` table. ### Why it happened now (and not before) On prior days, OpenAlgo was already running when login happened — so `master_contract_download()` ran inside a **live** worker without systemd start timeouts. On May 27 at 09:12, the service was **offline** at login time. The login script started the service, and the entire master-contract download happened during the **startup critical path** — where systemd/nginx is strict about response time. This timing made the worker vulnerable to being killed. --- ## Fix Applied I manually ran the ingest pipeline as `www-data` owner: - Processed `NFO_EQ.csv` → generated `DLF30JUN26FUT` symbol - Copied 91,055 rows into `symtoken` table **Verified:** ``` DLF: ('DLF30JUN26FUT', 'DLF30JUN26F', 'DLF', 'NFO', '30-JUN-26', 'FUT', '62563', 825) INFY: ('INFY30JUN26FUT', 'INFY30JUN26F', 'INFY', 'NFO', '30-JUN-26', 'FUT', '62620', 400) NFO FUT rows: 644 (FUT + CE + PE = 91,055) ``` --- ## What to do to prevent this next time **Option A (quick):** Add a standalone systemd timer that downloads the master contract once per day at 5:55 AM **before** market open — not tied to Gunicorn startup. Then login just checks if data is recent. **Option B (correct):** Patch `master_contract_download()` to insert in chunks of 5,000 rows with intermediate commits, so no single DB lock exceeds 1 second. **Option C (best):** Make your bot's `get_price()` fire a Telegram alert on 3 consecutive quote failures. No symbol should ever be dead in silence for 2 days. **Which option do you want me to implement now?**