Why Trading Bots Need an Analytics Layer
Running a multi-agent auto-trading system on Solana and CEX pairs generates an enormous amount of data. Every second, DeFiKit's bots process order book snapshots, execute risk checks, monitor honeypot detectors, and submit trades across multiple chains. Without a structured analytics layer, all that data is noise.
An analytics dashboard transforms that noise into signals: which strategies are winning, which pairs are bleeding, where your latency bottlenecks are, and whether your LLM-based risk analysis actually improves returns. This post walks through how we built the analytics dashboard for DeFiKit's bot ecosystem, using a serverless, zero-cost stack on Cloudflare Workers and D1.
Architecture Overview
The dashboard is a single-page application that queries a dedicated D1 analytics database. Bots write event records asynchronously via a lightweight HTTP API, and the dashboard queries aggregate views for visualization.
```
Trading Bots → HTTP POST /events → Cloudflare Worker → D1 Analytics DB → Dashboard (Astro + Chart.js)
```
Each bot is instrumented to emit events at key lifecycle points: signal generation, risk evaluation, trade execution, and post-trade settlement. The worker validates and inserts these events into D1, which serves as both the operational store and the analytics source.
Event Schema Design
The core analytics table captures every meaningful action across all bots:
```sql
CREATE TABLE bot_events (
id TEXT PRIMARY KEY,
bot_id TEXT NOT NULL,
event_type TEXT NOT NULL, -- 'signal', 'risk_eval', 'trade_open', 'trade_close', 'error'
pair TEXT,
strategy TEXT,
timestamp TEXT NOT NULL,
metadata TEXT, -- JSON blob with contextual data
created_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX idx_bot_events_bot ON bot_events(bot_id, timestamp);
CREATE INDEX idx_bot_events_type ON bot_events(event_type, timestamp);
```
This schema lets us answer key questions:
- Which bots generate the most signals per hour?
- What is the win rate per strategy per pair?
- How long does each stage of the pipeline take (signal → risk → execution)?
- Which errors are most frequent, and do they correlate with specific pairs?
Aggregation Views
Raw events are useful for debugging, but dashboards need aggregates. We use D1 materialized views that refresh periodically:
```sql
CREATE VIEW bot_performance_daily AS
SELECT
bot_id,
date(timestamp) as day,
COUNT(CASE WHEN event_type = 'signal' THEN 1 END) as signals,
COUNT(CASE WHEN event_type = 'trade_open' THEN 1 END) as trades_opened,
COUNT(CASE WHEN event_type = 'trade_close' THEN 1 END) as trades_closed,
COUNT(CASE WHEN event_type = 'error' THEN 1 END) as errors,
json_extract(metadata, '$.pnl_usd') as total_pnl
FROM bot_events
GROUP BY bot_id, date(timestamp);
```
This view powers the main dashboard widgets: daily trade volume, win/loss ratio, error rate, and P&L trend. For the latency pipeline analysis, a separate view calculates the median time between 'signal' and 'trade_open' events per bot per hour.
Dashboard Frontend
The dashboard is built with Astro and Chart.js, deployed on Cloudflare Pages alongside the main DeFiKit site. Key widgets include:
Live Activity Feed
A real-time scrolling feed of the last 50 events across all bots, color-coded by event type. Green for trades, yellow for signals, red for errors. The feed auto-refreshes every 10 seconds via a lightweight endpoint:
```typescript
export const GET: APIRoute = async ({ url }) => {
const db = (env as any).DB;
const since = url.searchParams.get('since') || '30s';
const result = await db.prepare(`
SELECT * FROM bot_events
WHERE created_at > datetime('now', ?)
ORDER BY created_at DESC
LIMIT 50
`).bind(`-${since}`).all();
return Response.json(result.results);
};
```
Strategy Comparison Matrix
This widget lets you compare any two strategies side-by-side across 7 metrics: total trades, win rate, average PnL, max drawdown, Sharpe ratio, signal frequency, and average latency. The data comes from a weekly rollup table that runs every Sunday via cron trigger.
Error Analysis Panel
Errors are grouped by type and bot, with a drill-down showing the raw error messages and stack context. We classify errors into three categories automatically:
| Category | Examples | Alert Threshold |
|----------|----------|----------------|
| Connection | RPC timeout, WebSocket disconnect | >5 in 5 minutes |
| Execution | Slippage exceeded, insufficient balance | Any single occurrence |
| Validation | Honeypot check failed, liquidity too low | Logged, no alert |
Connection errors trigger a Telegram alert via the existing DeFiKit bot infrastructure. Execution errors require manual review.
LLM Integration: Natural Language Queries
The dashboard also includes an LLM-powered query interface. Instead of navigating through filter menus, users can type:
> "Show me bots that had more than 3 errors yesterday with SOL pairs"
A Cloudflare Worker takes the query, generates the SQL via an LLM call, executes it against D1, and formats the results. We used few-shot prompting with the D1 schema as context to get reliable SQL generation:
```typescript
const prompt = `
Given the following database schema:
${SCHEMA}
Translate this user question to a SQLite query:
"${userQuery}"
Return ONLY the SQL query, no explanation.
`;
```
This feature turned out to be the most popular in internal testing. Non-technical team members could suddenly answer their own questions about bot performance without waiting for a developer to write SQL.
Performance Considerations
D1 handles the write volume easily (DeFiKit bots generate ~10,000 events/day across all instances). The key optimization was batching writes: instead of one INSERT per event, each bot buffers events for 5 seconds or 50 events (whichever comes first) and inserts them in a batch:
```typescript
const batchSQL = events.map(e => `
INSERT INTO bot_events (id, bot_id, event_type, pair, strategy, timestamp, metadata)
VALUES ('${e.id}', '${e.bot_id}', '${e.type}', ...)`
).join(';\n');
```
Batch inserts reduced D1 write costs by 87% compared to individual inserts, and the 5-second delay is invisible to the real-time dashboard.
What the Data Told Us
After running the analytics dashboard for 30 days on DeFiKit's production bots, the data revealed:
- **Freqtrade Ichimoku strategy** on XRP/USDT had the best Sharpe ratio (1.8) but executed only 12 trades/month — too few to be reliable.
- **Solana memecoin sniping** had the highest win rate on paper (68%) but the widest latency variance (300ms to 4.2s), directly correlating with slippage losses.
- **45% of errors** were RPC connection timeouts during Solana congestion events — leading us to implement automatic RPC failover.
- Bots running the LLM risk analysis module had 23% fewer unprofitable trades but 8% fewer total trades (the LLM was too conservative).
These insights directly drove product changes. The analytics dashboard wasn't just a nice-to-have visualization — it became the feedback loop that improved every bot in the fleet.
Next Steps
The next version will add anomaly detection: the dashboard uses statistical models (moving average + standard deviation) to flag unusual patterns automatically. For example, if a bot's trade frequency drops below 2 standard deviations from its 7-day average, the dashboard highlights it and sends an alert. This catches RPC failures, strategy bugs, and market regime changes before they cause significant losses.