Skip to content

DATABASE.md

Latest commit

 

History

History
83 lines (69 loc) · 5.33 KB

DATABASE.md

File metadata and controls

83 lines (69 loc) · 5.33 KB
Raw

Database Schema Reference

This document maps out the core tables in the hosted Supabase (Postgres) database to help answer "where does this number come from?" (migrated from a local SQLite file — column types below are the pre-migration SQLite types; the live schema uses the equivalent Postgres types, e.g. REAL→DOUBLE PRECISION, DATETIME→TIMESTAMPTZ, INTEGER PK AUTOINCREMENT→INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY.)

holdings_snapshot

Stores the most recent state of your actual brokerage holdings.

  • Columns: id (INTEGER, PK), stock_code (TEXT, UNIQUE), quantity (INTEGER), average_price (REAL), current_price (REAL), timestamp (DATETIME)
  • Written by: core/data_refresh.py (fetches from ICICI Breeze Demat & Portfolio APIs, taking the max quantity to avoid double-counting).
  • Read by: bot/formatters.py (Portfolio digest), Dashboard (Portfolio page), algo/action_classifier.py (determines if a stock is currently held for contextual guidance).

watchlist

User-defined symbols to track alongside actual holdings.

  • Columns: id (INTEGER, PK), stock_code (TEXT, UNIQUE)
  • Written by: Telegram bot (/watchlist add command), Dashboard (Watchlist page).
  • Read by: core/data_refresh.py (to backfill OHLCV), Dashboard (Watchlist page).

ohlcv_cache

Locally cached daily historical price data to avoid rate limits and speed up computation.

  • Columns: id (INTEGER, PK), stock_code (TEXT), date (TEXT), open (REAL), high (REAL), low (REAL), close (REAL), volume (INTEGER)
  • Constraints: UNIQUE(stock_code, date)
  • Written by: core/data_refresh.py (incremental daily backfill), scripts/backfill_history.py (deep historical backfills).
  • Read by: All algo/ modules (Screener, Stage Analysis, RS, Trend Template, etc.), Dashboard (Charts).

signals

Tactical short-term setup signals generated by the screener.

  • Columns: id (INTEGER, PK), stock_code (TEXT), rsi14 (REAL), macd_line (REAL), macd_signal (REAL), sma50 (REAL), sma200 (REAL), pct_from_52w_high (REAL), volume_ratio_20d (REAL), composite_score (REAL), timestamp (DATETIME)
  • Written by: algo/screener.py (run during data_refresh.py pipeline).
  • Read by: bot/formatters.py (Signals digest), Dashboard (Signals & Watchlist page).

job_heartbeats

Tracks the health and success of background operations.

  • Columns: id (INTEGER, PK), job_name (TEXT), timestamp (DATETIME)
  • Written by: core/data_refresh.py (upon successful pipeline completion).
  • Read by: Docker healthcheck (ensures bot container isn't stuck), Dashboard (Session Status page).

session_tokens

Stores the active ICICI Breeze API session token.

  • Columns: id (INTEGER, PK), token (TEXT), timestamp (DATETIME)
  • Written by: Telegram bot (/refresh_session command handler).
  • Read by: core/session_store.py (provides token to BreezeClient).

refresh_requests

A queue for manually triggering the data refresh pipeline asynchronously.

  • Columns: id (INTEGER, PK), requested_at (DATETIME), processed_at (DATETIME)
  • Written by: Dashboard (Refresh Data buttons).
  • Read by: core/scheduler.py (polls every 60s to trigger data_refresh.py).

stage_history

Caches the structural Stage Analysis results to power historical charts.

  • Columns: id (INTEGER, PK), stock_code (TEXT), date (TEXT), stage (INTEGER), sma_150 (REAL), slope (REAL)
  • Constraints: UNIQUE(stock_code, date)
  • Written by: core/data_refresh.py (calls action_classifier.py).
  • Read by: Dashboard (Charts page, for overlaying stage backgrounds).

stock_actions

The primary output of the Action Classification Engine, providing human-readable directives.

  • Columns: id (INTEGER, PK), stock_code (TEXT, UNIQUE), action (TEXT), rationale (TEXT), timestamp (DATETIME)
  • Written by: core/data_refresh.py (cleared and repopulated daily).
  • Read by: bot/formatters.py (Action Plan alerts), Dashboard (Action Plan page).

backfill_requests

A queue for asynchronously backfilling stock price history.

  • Columns: id (INTEGER, PK), stock_code (TEXT), requested_at (DATETIME), processed_at (DATETIME), status (TEXT), error (TEXT)
  • Written by: Dashboard (Watchlist & Signals page).
  • Read by: core/scheduler.py background poller.

data_health

Logs data fetch errors for both Breeze and yfinance provider pipelines.

  • Columns: id (INTEGER, PK), stock_code (TEXT), source (TEXT), status (TEXT), message (TEXT), timestamp (DATETIME)
  • Written by: core/data_refresh.py pipelines.
  • Read by: Dashboard (Session Status page), Telegram /status command.

portfolio_value_history

Tracks total invested value and current market value of holdings over time.

  • Columns: id (INTEGER, PK), timestamp (DATETIME), total_invested (REAL), total_current_value (REAL), total_pnl (REAL)
  • Written by: core/data_refresh.py Breeze sync pipeline.
  • Read by: Dashboard (Portfolio Analyser page).

stock_metadata

Categorizes holdings and watchlist symbols by sector/industry.

  • Columns: stock_code (TEXT, PK), sector (TEXT), industry (TEXT), updated_at (DATETIME)
  • Written by: Future metadata scraper (scaffolded).
  • Read by: Dashboard (Portfolio Analyser page).