Pakistan Ka Pehla Professional Trading Bot CourseModule 8

8.1 — Database Design — SQLite Trading Schema

30 min 2 code blocks Quiz (4Q)

Database Design — SQLite Trading Schema

Of course. Here is the full lesson content, written in the requested "mentor bhai" tone.

COURSE: Pakistan Ka Pehla Professional Trading Bot Course

MODULE 8: Database & Monitoring — Trade Logging Aur Performance Tracking

LESSON 8.1: Database Design — SQLite Trading Schema

Assalam-o-Alaikum, future bot masters!

Yaar, ab tak hum ne strategies, APIs, aur execution pe bohat baat karli. Lekin ek sawaal hai: aapka bot chaltay chaltay VPS crash honay ki wajah se band ho gaya. Ab kya? Aapki open positions ka kya bana? Kitna profit ya loss hua? Agar aapke paas in sawaalon ka jawab nahi hai, tou you are just gambling, not trading. Aaj hum aapke bot ka "dimaagh" aur "yaadasht" banayenge — a solid database.

SQLite Kyun? PostgreSQL/MySQL Kyun Nahi?

Suno, market main baray fancy databases hain — PostgreSQL, MySQL, MongoDB. Log kehte hain "enterprise-grade" hain. Bilkul hain, lekin hamare bot ke liye yeh aisay hai jaisay anda phentnay ke liye construction mixer istemal karna. Overkill hai, boss.

Hamare liye, SQLite is the king. Kyun?

  1. Zero Setup: Koi installation nahi, koi user/password ka jhanjhat nahi, koi server run nahi karna parta. import sqlite3 likha aur kaam shuru.
  2. File-Based: Poora database ek single file hai, jaisay bot.db. Iska matlab hai bot ko ek VPS se doosray pe move karna? Sirf file copy-paste karni hai. Backup lena? File ko zip karlo. That's it.
  3. Perfect for Bots: Hamara bot ek single process hai jo database se baat kar raha hai. Hamein hazaron concurrent users ko handle nahi karna. SQLite is super-fast for this kind of workload.

Simple si baat hai: start with SQLite. Jab aapka bot itna scale ho jaye ke woh Pakistan Stock Exchange ka 10% volume handle kar raha ho, tab PostgreSQL ke baray mein sochna. Abhi nahi.

Hamara Trading Schema - The Code Breakdown

Chalo, ab aatay hain asal kaam pe. Aapke bot ka data structure kaisa hoga? Hum 5 core tables banayenge. Har table ka ek maqsad hai.

Pehle zara is code ko dekho. Yeh hamari db.py file ka starting point hai. Isko samjho, kyunke yehi foundation hai.

python
# db.py

import sqlite3
import os
from datetime import datetime, timezone

# Bot ki directory mein hi 'bot.db' file banayega. Clean rehta hai.
DB_PATH = os.path.join(os.path.dirname(__file__), 'bot.db')

def get_conn():
    """Database connection ka helper function. Har dafa isko call karenge."""
    # timeout=10 ka matlab hai agar DB locked hai, to 10 second wait karo.
    conn = sqlite3.connect(DB_PATH, timeout=10)
    
    # Yeh line magic hai. Results ko dictionary jaisay access kar saktay ho.
    # e.g., row['pnl_eur'] instead of row[13]
    conn.row_factory = sqlite3.Row 
    
    # Yeh performance booster hai. Aagay detail mein bataunga.
    conn.execute('PRAGMA journal_mode=WAL')
    return conn

def init_db():
    """Pehli baar run honay pe yeh function tables create karega."""
    conn = get_conn()
    c = conn.cursor()
    
    # --- POSITIONS TABLE ---
    # Yahan hamari har open ya closed trade ki summary hogi.
    c.execute('''CREATE TABLE IF NOT EXISTS positions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        market_id TEXT NOT NULL,
        question TEXT NOT NULL,
        side TEXT NOT NULL,         -- 'YES' or 'NO'
        entry_price REAL NOT NULL,
        current_price REAL,
        shares REAL NOT NULL,
        cost_eur REAL NOT NULL,
        strategy TEXT NOT NULL,     -- e.g., 'theta_sniper'
        status TEXT DEFAULT 'open', -- 'open', 'closed'
        entry_time TEXT NOT NULL,
        exit_time TEXT,
        exit_price REAL,
        pnl_eur REAL,               -- Profit and Loss
        theme TEXT,                 -- e.g., 'Cricket', 'Politics'
        buy_reasoning TEXT          -- AI se jo reason mila
    )''')
    
    # --- AI LOG TABLE ---
    # Har AI call ka hisaab rakhega. Kitne paise lagay?
    c.execute('''CREATE TABLE IF NOT EXISTS ai_log (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        tier TEXT NOT NULL,         -- 'haiku', 'sonnet', 'gemini'
        module TEXT,                -- 'scanner', 'analysis'
        prompt_tokens INTEGER,
        completion_tokens INTEGER,
        cost_usd REAL,
        timestamp TEXT NOT NULL,
        success INTEGER DEFAULT 1   -- 1 for success, 0 for failure
    )''')
    
    # Changes save karna zaroori hai
    conn.commit()
    conn.close()
    print('[DB] Database initialized.')

# Script run hotay hi DB check/create ho jayegi.
if __name__ == '__main__':
    init_db()

Chalo isko cheer phaar karte hain.

  • get_conn(): Yeh ek helper function hai. Bar bar connection string likhnay se behtar hai ek function bana lo. Isme do important cheezein hain:
    • conn.row_factory = sqlite3.Row: By default, SQLite se data (value1, value2) ki tarah tuple mein aata hai. Yeh line usko dictionary-like object bana deti hai, jis se code parhna asaan ho jata hai: row['price'] is much better than row[4].
    • PRAGMA journal_mode=WAL: Ispe aagay detail mein baat hogi. Abhi ke liye, yeh samajh lo ke yeh aapke bot ko crash honay se bachata hai jab ek hissa DB parh raha ho aur doosra likh raha ho.
  • init_db(): Yeh function schema banata hai. CREATE TABLE IF NOT EXISTS ka matlab hai ke agar table pehle se mojood hai tou error nahi dega, chup chaap aagay barh jayega.

Table 1: positions

Yeh aapke bot ki "balance sheet" hai. Har row ek open ya closed position hai.

  • market_id, question: Konsi market? e.g., "Will Babar Azam score 50+ in the next PSL match?"
  • side: 'YES' lia ya 'NO' lia?
  • entry_price, shares, cost_eur: Kitne shares, kis price pe, total kitne paison ke liye.
  • strategy: Kis strategy ne yeh trade li? strategies/theta_sniper.py se aayi ya kisi aur se? Is se pata chalega konsi strategy profit de rahi hai.
  • status: Position abhi open hai ya closed?
  • pnl_eur: The baap of all columns. Profit/Loss kitna hua? scanner.py isko current_price ke hisaab se update karta rahega, aur execution.py isko finalise karega jab position close hogi.
  • buy_reasoning: Jab ai/gemini.py ya ai/haiku.py market analyze karega, woh jo reason dega, usko yahan save karenge. Baad mein review karne ke liye awesome hai.

Table 2: ai_log

AI models free nahi hain. Har call ke paise lagte hain, bhale woh cents mein hon. Yeh table unka hisaab rakhegi.

  • tier: Konsa model use hua? haiku sasta hai, gemini mehnga. Pata hona chahiye kahan se cost aya.

Proper database schema enables efficient querying, analysis, and performance optimization.

📺 Recommended Videos & Resources

🎯 Mini-Challenge

5-Minute Practical Task: Design a SQLite schema with 4 tables: (1) markets (id, question, volume), (2) positions (id, market_id, entry_price, size), (3) trades (id, position_id, action, price, timestamp), (4) analysis_log (id, market_id, tier, output). Create the tables and verify with .schema command.

🖼️ Visual Reference

code
📊 Trading Bot Database Schema
┌──────────────────┐
│  MARKETS         │
├──────────────────┤
│ id (PK)          │
│ question         │
│ volume24hr       │
│ created_at       │
└────────┬─────────┘
         │ (1-to-many)
         ▼
┌──────────────────┐
│  POSITIONS       │
├──────────────────┤
│ id (PK)          │
│ market_id (FK)   │
│ entry_price      │
│ size             │
│ status: OPEN/CLOSED
│ entry_at         │
└────────┬─────────┘
         │ (1-to-many)
         ▼
┌──────────────────┐
│  TRADES          │
├──────────────────┤
│ id (PK)          │
│ position_id (FK) │
│ action: BUY/SELL │
│ price            │
│ timestamp        │
└──────────────────┘

┌──────────────────┐
│  ANALYSIS_LOG    │
├──────────────────┤
│ id (PK)          │
│ market_id (FK)   │
│ tier: G/H/S/O    │
│ confidence       │
│ timestamp        │
└──────────────────┘

Lesson Summary

2 runnable code examples4-question knowledge check below

Quiz: Database Design — SQLite Trading Schema

4 questions to test your understanding. Score 60% or higher to pass.

Previous
7.4 — Position Manager — Automatic Monitoring System
Next
8.2 — CRUD Operations — Positions Aur Trades Manage Karo