81 lines
2.7 KiB
SQL
81 lines
2.7 KiB
SQL
-- Trahn Grid Trader Database Schema
|
|
-- PostgreSQL
|
|
|
|
-- Table 1: Price History
|
|
-- Stores ETH price data points from CoinGecko
|
|
CREATE TABLE IF NOT EXISTS price_history (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
timestamp TIMESTAMPTZ NOT NULL,
|
|
price DECIMAL(12, 2) NOT NULL,
|
|
trading_day DATE NOT NULL,
|
|
source VARCHAR(50) DEFAULT 'coingecko',
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_price_timestamp ON price_history(timestamp);
|
|
CREATE INDEX IF NOT EXISTS idx_price_trading_day ON price_history(trading_day);
|
|
|
|
-- Table 2: Trade History
|
|
-- Stores executed trades (buys and sells)
|
|
CREATE TABLE IF NOT EXISTS trade_history (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
timestamp TIMESTAMPTZ NOT NULL,
|
|
trading_day DATE NOT NULL,
|
|
side VARCHAR(10) NOT NULL CHECK (side IN ('buy', 'sell')),
|
|
price DECIMAL(12, 2) NOT NULL,
|
|
quantity DECIMAL(18, 8) NOT NULL,
|
|
usd_value DECIMAL(12, 2) NOT NULL,
|
|
grid_level INTEGER,
|
|
tx_hash VARCHAR(66),
|
|
is_paper_trade BOOLEAN DEFAULT FALSE,
|
|
slippage_percent DECIMAL(5, 3),
|
|
gas_cost_eth DECIMAL(18, 8),
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_trade_timestamp ON trade_history(timestamp);
|
|
CREATE INDEX IF NOT EXISTS idx_trade_trading_day ON trade_history(trading_day);
|
|
CREATE INDEX IF NOT EXISTS idx_trade_side ON trade_history(side);
|
|
|
|
-- Table 3: Support/Resistance History
|
|
-- Stores S/R levels fetched from Dune Analytics over time
|
|
CREATE TABLE IF NOT EXISTS support_resistance_history (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
timestamp TIMESTAMPTZ NOT NULL,
|
|
method VARCHAR(20) NOT NULL,
|
|
lookback_days INTEGER NOT NULL,
|
|
support DECIMAL(12, 2) NOT NULL,
|
|
resistance DECIMAL(12, 2) NOT NULL,
|
|
midpoint DECIMAL(12, 2) NOT NULL,
|
|
avg_price DECIMAL(12, 2),
|
|
grid_recalculated BOOLEAN DEFAULT FALSE,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_sr_timestamp ON support_resistance_history(timestamp);
|
|
|
|
-- Table 4: Grid State
|
|
-- Stores current grid configuration and state
|
|
-- Only one active row at a time (or keyed by bot instance)
|
|
CREATE TABLE IF NOT EXISTS grid_state (
|
|
id SERIAL PRIMARY KEY,
|
|
base_price DECIMAL(12, 2),
|
|
grid_levels_json JSONB,
|
|
trades_executed INTEGER DEFAULT 0,
|
|
total_profit DECIMAL(12, 2) DEFAULT 0,
|
|
last_sr_refresh TIMESTAMPTZ,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_grid_active ON grid_state(is_active);
|
|
|
|
-- View: Latest S/R
|
|
-- Convenience view for getting the most recent S/R data
|
|
CREATE OR REPLACE VIEW latest_support_resistance AS
|
|
SELECT * FROM support_resistance_history
|
|
ORDER BY timestamp DESC
|
|
LIMIT 1;
|
|
|