BackDB Queries
The backdb database provides access to backtest results stored in a relational database.
This database contains comprehensive information about each backtest, including metadata,
configuration parameters, and performance metrics.
Database Schema
The main table available for querying is backtest_results, which contains information about each backtest run.
backtest_results Schema
| Field Name | Description | Data Type | Optional |
|---|---|---|---|
| id | Unique identifier for the backtest | VARCHAR(40) | No |
| user_id | Identifier of the user who created the backtest | VARCHAR(40) | No |
| created_at | Timestamp when backtest was created | DATETIME | No |
| started_at | Timestamp when backtest execution started | DATETIME | Yes |
| finished_at | Timestamp when backtest execution completed | DATETIME | Yes |
| delete_mode | Deletion type if backtest was deleted | VARCHAR(32) | Yes |
| deleted_at | Timestamp when backtest was deleted | DATETIME | Yes |
| shared | Whether the backtest is shared with other users | BOOLEAN | Yes |
| state | Current status of the backtest | VARCHAR(128) | Yes |
| name | User-defined name of the backtest | VARCHAR(250) | Yes |
| strategy_name | Name of the strategy used | VARCHAR(128) | Yes |
| strategy_definition | Complete configuration of the strategy definition | JSON | No |
| failure_reason | Description of failure if backtest failed | TEXT | Yes |
| start_period | Start date of the backtest period | DATETIME | Yes |
| end_period | End date of the backtest period | DATETIME | Yes |
| strat_trade_cnt | Total number of trades executed | NUMERIC | Yes |
| strat_win_cnt | Number of winning trades | NUMERIC | Yes |
| strat_loss_cnt | Number of losing trades | NUMERIC | Yes |
| strat_adjustment_cnt | Number of trade adjustments | NUMERIC | Yes |
| strat_pt_hit_cnt | Number of profit target hits | NUMERIC | Yes |
| strat_sl_hit_cnt | Number of stop loss hits | NUMERIC | Yes |
| strat_max_dit_reached_cnt | Number of trades that reached maximum days in trade | NUMERIC | Yes |
| strat_settlement_cnt | Number of trades settled at expiration | NUMERIC | Yes |
| strat_avg_days_in_trade | Average number of days positions were held | DECIMAL(15,4) | Yes |
| strat_alpha | Alpha (excess return) of strategy | DECIMAL(15,4) | Yes |
| strat_beta | Beta (market correlation) of strategy | DECIMAL(15,4) | Yes |
| strat_cagr_pct | Compound Annual Growth Rate percentage | DECIMAL(15,4) | Yes |
| strat_cum_ret | Cumulative return over entire period | DECIMAL(15,4) | Yes |
| strat_max_dd | Maximum drawdown percentage | DECIMAL(15,4) | Yes |
| strat_avg_dd | Average drawdown percentage | DECIMAL(15,4) | Yes |
| strat_avg_dd_days | Average drawdown duration in days | DECIMAL(15,4) | Yes |
| strat_avg_up_month | Average return in positive months | DECIMAL(15,4) | Yes |
| strat_avg_down_month | Average return in negative months | DECIMAL(15,4) | Yes |
| strat_longest_dd_days | Longest drawdown duration in days | NUMERIC | Yes |
| strat_inform_ratio | Information ratio | DECIMAL(15,4) | Yes |
| strat_sharpe | Sharpe ratio (risk-adjusted return) | DECIMAL(15,4) | Yes |
| strat_prob_sharpe | Probabilistic Sharpe ratio | DECIMAL(15,4) | Yes |
| strat_smart_sharpe | Smart Sharpe ratio | DECIMAL(15,4) | Yes |
| strat_sortino | Sortino ratio (downside risk-adjusted return) | DECIMAL(15,4) | Yes |
| strat_smart_sortino | Smart Sortino ratio | DECIMAL(15,4) | Yes |
| strat_treynor | Treynor ratio | DECIMAL(15,4) | Yes |
| strat_calmar | Calmar ratio (return to max drawdown) | DECIMAL(15,4) | Yes |
| strat_omega | Omega ratio | DECIMAL(15,4) | Yes |
| strat_r_squared | R-squared (correlation with benchmark) | DECIMAL(15,4) | Yes |
| strat_profit_factor | Ratio of gross profits to gross losses | DECIMAL(15,4) | Yes |
| strat_kelly_criterion | Kelly criterion (optimal position sizing) | DECIMAL(15,4) | Yes |
| strat_ann_volatility | Annualized volatility | DECIMAL(15,4) | Yes |
| bm_cagr_pct | Benchmark Compound Annual Growth Rate percentage | DECIMAL(15,4) | Yes |
| bm_cum_ret | Benchmark cumulative return | DECIMAL(15,4) | Yes |
| bm_max_dd | Benchmark maximum drawdown percentage | DECIMAL(15,4) | Yes |
| bm_avg_dd | Benchmark average drawdown percentage | DECIMAL(15,4) | Yes |
| bm_avg_dd_days | Benchmark average drawdown duration in days | DECIMAL(15,4) | Yes |
| bm_avg_up_month | Benchmark average return in positive months | DECIMAL(15,4) | Yes |
| bm_avg_down_month | Benchmark average return in negative months | DECIMAL(15,4) | Yes |
| bm_longest_dd_days | Benchmark longest drawdown duration in days | NUMERIC | Yes |
| bm_inform_ratio | Benchmark information ratio | DECIMAL(15,4) | Yes |
| bm_sharpe | Benchmark Sharpe ratio | DECIMAL(15,4) | Yes |
| bm_prob_sharpe | Benchmark probabilistic Sharpe ratio | DECIMAL(15,4) | Yes |
| bm_smart_sharpe | Benchmark smart Sharpe ratio | DECIMAL(15,4) | Yes |
| bm_sortino | Benchmark Sortino ratio | DECIMAL(15,4) | Yes |
| bm_smart_sortino | Benchmark smart Sortino ratio | DECIMAL(15,4) | Yes |
| bm_treynor | Benchmark Treynor ratio | DECIMAL(15,4) | Yes |
| bm_calmar | Benchmark Calmar ratio | DECIMAL(15,4) | Yes |
| bm_omega | Benchmark Omega ratio | DECIMAL(15,4) | Yes |
| bm_r_squared | Benchmark R-squared | DECIMAL(15,4) | Yes |
| bm_profit_factor | Benchmark profit factor | DECIMAL(15,4) | Yes |
| bm_kelly_criterion | Benchmark Kelly criterion | DECIMAL(15,4) | Yes |
| bm_ann_volatility | Benchmark annualized volatility | DECIMAL(15,4) | Yes |
For AI-Assisted Queries please post the following schema as context to the AI Agents:
CREATE TABLE IF NOT EXISTS backtest_results (
id VARCHAR(40) NOT NULL,
user_id VARCHAR(40) NOT NULL,
created_at DATETIME NOT NULL,
started_at DATETIME,
finished_at DATETIME,
delete_mode VARCHAR(32),
deleted_at DATETIME,
shared BOOLEAN DEFAULT FALSE,
state VARCHAR(128),
name VARCHAR(250),
strategy_name VARCHAR(128),
strategy_definition JSON NOT NULL,
failure_reason TEXT,
start_period DATETIME,
end_period DATETIME,
strat_trade_cnt NUMERIC,
strat_win_cnt NUMERIC,
strat_loss_cnt NUMERIC,
strat_adjustment_cnt NUMERIC,
strat_pt_hit_cnt NUMERIC,
strat_sl_hit_cnt NUMERIC,
strat_max_dit_reached_cnt NUMERIC,
strat_settlement_cnt NUMERIC,
strat_avg_days_in_trade DECIMAL(15,4),
strat_alpha DECIMAL(15, 4),
strat_beta DECIMAL(15, 4),
strat_cagr_pct DECIMAL(15, 4),
strat_cum_ret DECIMAL(15, 4),
strat_max_dd DECIMAL(15, 4),
strat_avg_dd DECIMAL(15, 4),
strat_avg_dd_days DECIMAL(15, 4),
strat_avg_up_month DECIMAL(15, 4),
strat_avg_down_month DECIMAL(15, 4),
strat_longest_dd_days NUMERIC,
strat_inform_ratio DECIMAL(15, 4),
strat_sharpe DECIMAL(15, 4),
strat_prob_sharpe DECIMAL(15, 4),
strat_smart_sharpe DECIMAL(15, 4),
strat_sortino DECIMAL(15, 4),
strat_smart_sortino DECIMAL(15, 4),
strat_treynor DECIMAL(15, 4),
strat_calmar DECIMAL(15, 4),
strat_omega DECIMAL(15, 4),
strat_r_squared DECIMAL(15, 4),
strat_profit_factor DECIMAL(15, 4),
strat_kelly_criterion DECIMAL(15, 4),
strat_ann_volatility DECIMAL(15, 4),
bm_cagr_pct DECIMAL(15, 4),
bm_cum_ret DECIMAL(15, 4),
bm_max_dd DECIMAL(15, 4),
bm_avg_dd DECIMAL(15, 4),
bm_avg_dd_days DECIMAL(15, 4),
bm_avg_up_month DECIMAL(15, 4),
bm_avg_down_month DECIMAL(15, 4),
bm_longest_dd_days NUMERIC,
bm_inform_ratio DECIMAL(15, 4),
bm_sharpe DECIMAL(15, 4),
bm_prob_sharpe DECIMAL(15, 4),
bm_smart_sharpe DECIMAL(15, 4),
bm_sortino DECIMAL(15, 4),
bm_smart_sortino DECIMAL(15, 4),
bm_treynor DECIMAL(15, 4),
bm_calmar DECIMAL(15, 4),
bm_omega DECIMAL(15, 4),
bm_r_squared DECIMAL(15, 4),
bm_profit_factor DECIMAL(15, 4),
bm_kelly_criterion DECIMAL(15, 4),
bm_ann_volatility DECIMAL(15, 4)
);
note
The performance metrics are calculated using our fork of QuantStats
Example Queries
Basic Query - Strategy Performance Metrics
This query retrieves basic strategy performance metrics ordered by Sharpe ratio:
select
created_at,
name,
strategy_name,
strat_sharpe,
strat_max_dd,
strat_cagr_pct
from backtest_results
order by strat_sharpe desc;
Advanced Query - Strategy Definition Analysis
The strategy_definition column contains the complete MesoSim Strategy Definition as JSON.
You can query directly into this JSON structure to analyze how specific parameters affect performance:
with cte as (
select
id,
CAST(JSON_EXTRACT(strategy_definition, '$.Structure.Expirations[0].DTE') AS float) AS exp1,
CAST(JSON_EXTRACT(strategy_definition, '$.Exit.MaxDaysInTrade') AS float) AS MaxDaysInTrade,
strat_sharpe,
strat_cagr_pct,
strat_max_dd
from backtest_results
where 1 = 1
AND strategy_name = "MyStrategy"
),
aggregates as (
select
MaxDaysInTrade,
count(1) as run_cnt,
avg(strat_sharpe) as avg_strat_sharpe,
min(strat_sharpe) as min_strat_sharpe,
max(strat_sharpe) as max_strat_sharpe,
avg(strat_cagr_pct) as avg_strat_cagr_pct,
avg(strat_max_dd) as avg_strat_max_dd,
min(strat_max_dd) as min_strat_max_dd,
max(strat_max_dd) as max_strat_max_dd
from cte
group by MaxDaysInTrade
),
final as (
select
*,
ntile(10) over (order by avg_strat_sharpe) as sharpe_bin
from aggregates
)
select *
from final
order by avg_strat_sharpe;
This advanced query:
- Extracts parameters from the MesoSim Strategy Definition
- Allows filtering by template name and DTE (Days To Expiration) range
- Aggregates results by MaxDaysInTrade parameter
- Calculates averages, minimums, and maximums for key performance metrics
- Adds a percentile ranking for sharpe ratio
Query Tips
- Use JSON_EXTRACT with CAST to query nested parameters in the MesoSim Strategy Definition (
strategy_definition) field - You can add Metabase Dashboard Filter parameters (enclosed in double square brackets) for interactive queries
- Aggregate results to identify trends across similar configurations
- Use window functions like NTILE for percentile rankings
- Include both performance metrics and strategy parameters to identify optimal configurations