choijaewook
ffada928f2
Implements Task 2: creates db/schema.sql with five tables (signals, trades, positions, portfolio, settings) and indexes; data/db.py with a Database class covering all CRUD operations; tests/test_db.py with 6 passing pytest tests. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
52 lines
1.4 KiB
SQL
52 lines
1.4 KiB
SQL
CREATE TABLE IF NOT EXISTS signals (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
coin TEXT NOT NULL,
|
|
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
technical_score REAL DEFAULT 50,
|
|
news_score REAL DEFAULT 50,
|
|
social_score REAL DEFAULT 50,
|
|
ai_score REAL DEFAULT 50,
|
|
composite_score REAL DEFAULT 50,
|
|
signal TEXT DEFAULT 'HOLD'
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS trades (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
coin TEXT NOT NULL,
|
|
side TEXT NOT NULL,
|
|
price REAL NOT NULL,
|
|
quantity REAL NOT NULL,
|
|
amount_usd REAL NOT NULL,
|
|
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
reason TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS positions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
coin TEXT NOT NULL,
|
|
entry_price REAL NOT NULL,
|
|
quantity REAL NOT NULL,
|
|
invested_usd REAL NOT NULL,
|
|
status TEXT DEFAULT 'OPEN',
|
|
opened_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
closed_at DATETIME
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS portfolio (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
total_value REAL NOT NULL,
|
|
cash REAL NOT NULL,
|
|
pnl REAL NOT NULL,
|
|
pnl_pct REAL NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_signals_coin_ts ON signals(coin, timestamp);
|
|
CREATE INDEX IF NOT EXISTS idx_positions_status ON positions(status);
|
|
CREATE INDEX IF NOT EXISTS idx_trades_coin ON trades(coin);
|