Trading Database — Access Runbook

Database: trading (PostgreSQL 15)
Host container: trading-postgres
Last updated: 2026-05-05

Connection Details

The port is bound to 0.0.0.0:5432 on the DGX host — reachable from any system on the same LAN or Tailscale mesh.

Credentials

Two read-only accounts have been provisioned. Both are members of the trading_reader role which grants SELECT on all current and future tables in the public schema.

Change these passwords immediately after first connection:

ALTER USER [db-user] PASSWORD 'your-strong-password';
ALTER USER [db-user-2] PASSWORD 'your-strong-password';

Permissions: SELECT only. No INSERT, UPDATE, DELETE, CREATE, or DROP.

Connecting

psql (CLI)

psql -h [DB-HOST-LAN-IP] -p 5432 -U [db-user] -d trading
# or via Tailscale
psql -h [DB-HOST-TAILSCALE-IP] -p 5432 -U [db-user] -d trading

Python — psycopg2

import psycopg2

conn = psycopg2.connect(
    host="[DB-HOST-LAN-IP]",
    port=5432,
    dbname="trading",
    user="[db-user]",
    password="[YOUR-DB-PASSWORD]",
)
cur = conn.cursor()
cur.execute("SELECT ticker, date, close FROM market_data_daily ORDER BY date DESC LIMIT 5")
rows = cur.fetchall()
conn.close()

Python — SQLAlchemy

from sqlalchemy import create_engine

engine = create_engine(
    "postgresql+psycopg2://[db-user]:[YOUR-DB-PASSWORD]@[DB-HOST-LAN-IP]:5432/trading"
)
with engine.connect() as conn:
    result = conn.execute("SELECT * FROM tqqq_signals ORDER BY signal_date DESC LIMIT 1")
    print(result.fetchall())

Python — pandas

import pandas as pd
import psycopg2

conn = psycopg2.connect(
    host="[DB-HOST-LAN-IP]", port=5432, dbname="trading",
    user="[db-user]", password="[YOUR-DB-PASSWORD]"
)
df = pd.read_sql(
    "SELECT * FROM market_data_daily WHERE ticker = 'NVDA' ORDER BY date DESC LIMIT 30",
    conn
)
conn.close()

Node.js — pg

const { Pool } = require('pg');

const pool = new Pool({
  host: '[DB-HOST-LAN-IP]',
  port: 5432,
  database: 'trading',
  user: '[db-user-2]',
  password: '[YOUR-DB-PASSWORD]',
});

const res = await pool.query(
  "SELECT * FROM tqqq_signals ORDER BY signal_date DESC LIMIT 1"
);
console.log(res.rows);
await pool.end();

Environment variable pattern (recommended)

Store credentials in .env and load them at runtime rather than hardcoding:

DB_HOST=[DB-HOST-LAN-IP]
DB_PORT=5432
DB_NAME=trading
DB_USER=miraiclaw
DB_PASSWORD=[YOUR-DB-PASSWORD]

import os, psycopg2

conn = psycopg2.connect(
    host=os.environ["DB_HOST"],
    port=int(os.environ.get("DB_PORT", 5432)),
    dbname=os.environ["DB_NAME"],
    user=os.environ["DB_USER"],
    password=os.environ["DB_PASSWORD"],
)

Data Freshness

Ingestion runs on the DGX via cron (weekdays, all times DGX local / US Central):

tqqq_signals and tqqq_daily_summary are populated on demand (not on a fixed cron).

Table Reference

Market Data

market_data_daily

OHLCV daily bars for the full NDX-100 universe + TQQQ, SQQQ, SPY, QQQ, IWM, DIA, plus ETFs (GLD, SLV, TLT, IEF, SHY, HYG, LQD, USO, UNG, GLD, VXX, UUP, FXE, FXY, FXA, FXB).

Primary key: (ticker, date). Upsert-safe — re-running ingestion is idempotent.

-- Latest close for all tickers
SELECT ticker, date, close
FROM market_data_daily
WHERE date = (SELECT MAX(date) FROM market_data_daily)
ORDER BY ticker;

-- 90-day history for NVDA
SELECT date, open, high, low, close, volume
FROM market_data_daily
WHERE ticker = 'NVDA'
ORDER BY date DESC
LIMIT 90;

market_data

NVDA 1-minute intraday bars (legacy table, NVDA only).

No primary key constraint — rows may duplicate on re-run. Filter by timestamp range for recent bars.

-- Last 390 minutes (one trading day) of NVDA 1-min bars
SELECT timestamp, open, high, low, close, volume
FROM market_data
WHERE ticker = 'NVDA'
  AND timestamp >= NOW() - INTERVAL '1 day'
ORDER BY timestamp;

options_metrics_daily

Daily implied volatility (IV) and historical volatility (HV) metrics per ticker, sourced from Interactive Brokers.

Primary key: (ticker, date).

-- IV/HV for TQQQ over the last 30 days
SELECT date, iv_close, hv_close, iv_close - hv_close AS iv_premium
FROM options_metrics_daily
WHERE ticker = 'TQQQ'
ORDER BY date DESC
LIMIT 30;

News

news_headlines

Article metadata from IB news feeds. One row per (ticker, article_id) combination — the same article may appear under multiple tickers.

-- Latest 20 headlines for NVDA
SELECT ticker, provider, headline, published_at
FROM news_headlines
WHERE ticker = 'NVDA'
ORDER BY published_at DESC
LIMIT 20;

-- Join headline to body
SELECT h.ticker, h.headline, h.published_at, a.body
FROM news_headlines h
JOIN news_articles a ON a.article_id = h.article_id
WHERE h.ticker = 'AAPL'
ORDER BY h.published_at DESC
LIMIT 5;

news_articles

Full article body text. Not all headlines have bodies — some providers are headline-only or paywalled (IB returns error 10172 for those).

-- How many headlines have bodies vs. not
SELECT
  COUNT(*) FILTER (WHERE a.article_id IS NOT NULL) AS with_body,
  COUNT(*) FILTER (WHERE a.article_id IS NULL)     AS headline_only
FROM news_headlines h
LEFT JOIN news_articles a ON a.article_id = h.article_id;

LDTM Model (LSTM Multi-Horizon Predictions)

ldtm_daily_snapshots

One row per (run_date, ticker) written each evening after LDTM inference. Actuals are filled back by snapshot_fillback.py once the future date's close is available.

Unique constraint: (run_date, ticker).

-- Today's predictions for all tickers
SELECT ticker, next_day_close_pred, next_monday_close_pred, one_month_close_pred, run_date_close
FROM ldtm_daily_snapshots
WHERE run_date = CURRENT_DATE
ORDER BY ticker;

-- Most recent prediction per ticker
SELECT DISTINCT ON (ticker)
  ticker, run_date, next_day_close_pred, next_day_direction_pred
FROM ldtm_daily_snapshots
ORDER BY ticker, run_date DESC;

ldtm_accuracy_30d (VIEW)

Rolling 30-day accuracy summary per ticker, computed live from ldtm_daily_snapshots.

-- Top 10 tickers by direction accuracy
SELECT ticker, direction_accuracy_pct, avg_abs_pct_error, evaluated_days
FROM ldtm_accuracy_30d
ORDER BY direction_accuracy_pct DESC
LIMIT 10;

ldtm_run_log

One row per model run (train or infer) — written by model/ldtm/ containers.

-- Last 10 inference runs and their status
SELECT run_at, ticker, mode, status, duration_sec, next_day_close
FROM ldtm_run_log
WHERE mode = 'infer'
ORDER BY run_at DESC
LIMIT 10;

ldtm_backtest_results

Walk-forward backtest results — one row per (ticker, train_cutoff, test_date, horizon).

-- Direction accuracy by ticker and horizon
SELECT ticker, horizon,
  COUNT(*) AS n,
  ROUND(100.0 * SUM(CASE WHEN direction_correct THEN 1 ELSE 0 END) / COUNT(*), 1) AS dir_acc_pct,
  ROUND(AVG(ABS(pct_error))::numeric, 2) AS avg_abs_err
FROM ldtm_backtest_results
GROUP BY ticker, horizon
ORDER BY dir_acc_pct DESC;

ldtm_backtest_summary (VIEW)

Aggregated from ldtm_backtest_results — one row per (ticker, horizon).

model_versions

LDTM model registry — tracks candidate → active → retired lifecycle.

-- Currently active models
SELECT ticker, version, val_loss, bt_direction_acc_nextday, promoted_at
FROM model_versions
WHERE status = 'active'
ORDER BY ticker;

TQQQ RF Classifier

tqqq_signals

Daily directional signal for TQQQ from the Random Forest classifier.

Unique constraint: (signal_date, horizon_days).

-- Latest signal
SELECT signal_date, direction, prob_buy, prob_short, prob_hold, horizon_days
FROM tqqq_signals
ORDER BY signal_date DESC, horizon_days
LIMIT 5;

-- Historical accuracy
SELECT
  direction,
  COUNT(*) AS n,
  ROUND(100.0 * SUM(CASE WHEN was_correct THEN 1 ELSE 0 END) / NULLIF(COUNT(*) FILTER (WHERE was_correct IS NOT NULL), 0), 1) AS accuracy_pct
FROM tqqq_signals
GROUP BY direction;

tqqq_backtest_runs

Walk-forward backtest results for the TQQQ RF strategy.

-- Latest backtest summary
SELECT run_date, return_pct, cagr, sharpe, max_drawdown, alpha, total_trades, win_rate
FROM tqqq_backtest_runs
ORDER BY run_date DESC
LIMIT 1;

tqqq_montecarlo_runs

Bootstrap Monte Carlo simulation results (1000 sims) based on a backtest's trade distribution.

tqqq_daily_summary

LLM-ready JSONB summary blob, generated by model-tqqq-summarize. One row per trading day.

-- Today's summary
SELECT summary_date, summary_json
FROM tqqq_daily_summary
WHERE summary_date = CURRENT_DATE;

TFT Model

tft_run_log

One row per TFT training or inference run.

Dashboard / Custom Jobs

custom_run_jobs

Jobs submitted through the Streamlit dashboard for on-demand LDTM runs.

Useful Cross-Table Queries

-- Latest LDTM prediction + 30d accuracy for every ticker
SELECT
  s.ticker,
  s.run_date,
  s.next_day_close_pred,
  s.next_day_direction_pred,
  s.run_date_close,
  a.direction_accuracy_pct,
  a.avg_abs_pct_error
FROM (
  SELECT DISTINCT ON (ticker) *
  FROM ldtm_daily_snapshots
  ORDER BY ticker, run_date DESC
) s
LEFT JOIN ldtm_accuracy_30d a ON a.ticker = s.ticker
ORDER BY a.direction_accuracy_pct DESC NULLS LAST;

-- TQQQ composite view: today's signal + latest backtest + latest MC
SELECT
  ts.signal_date,
  ts.direction,
  ts.prob_buy,
  ts.prob_short,
  tb.cagr,
  tb.sharpe,
  tb.alpha,
  tm.prob_profit,
  tm.p50_return_pct AS mc_median_return
FROM tqqq_signals ts
LEFT JOIN tqqq_backtest_runs tb ON tb.run_date = ts.signal_date
LEFT JOIN tqqq_montecarlo_runs tm ON tm.run_date = ts.signal_date
WHERE ts.signal_date = (SELECT MAX(signal_date) FROM tqqq_signals);

-- Recent news with bodies for a ticker
SELECT h.ticker, h.headline, h.published_at, a.body
FROM news_headlines h
JOIN news_articles a ON a.article_id = h.article_id
WHERE h.ticker = 'NVDA'
  AND h.published_at >= NOW() - INTERVAL '7 days'
ORDER BY h.published_at DESC;

Access Control Notes

• Both miraiclaw and miraistudio are read-only via the trading_reader role.
• trading_reader has SELECT on all present and future tables (ALTER DEFAULT PRIVILEGES is set).
• Neither user can modify data, create objects, or access system catalogs beyond standard pg_catalog reads.
• To revoke access: REVOKE trading_reader FROM miraiclaw;
• To add another system: CREATE USER newsystem WITH PASSWORD '...'; GRANT trading_reader TO newsystem;

Troubleshooting