[Database Operations]

Hoox utilizes Cloudflare® D1—a fully serverless, highly optimized SQLite database engine distributed globally across Cloudflare's edge network. This document serves as your operational runbook for executing database schemas, running schema migrations, querying transaction ledgers, and performing secure database backup and recovery.

---

The Core Database Tables

The database schema defines five fundamental tables designed for trading operations, plus supporting indices for fast query performance:

Schema Details

-- Core trades ledger
CREATE TABLE trades (
  id TEXT PRIMARY KEY,
  request_id TEXT NOT NULL,
  exchange TEXT NOT NULL,           -- 'bybit', 'binance', 'mexc'
  symbol TEXT NOT NULL,
  action TEXT NOT NULL,             -- 'LONG', 'SHORT', 'CLOSE'
  side TEXT NOT NULL,               -- 'BUY' or 'SELL'
  quantity REAL NOT NULL,
  price REAL NOT NULL,
  leverage INTEGER DEFAULT 1,
  fee REAL NOT NULL DEFAULT 0,
  order_id TEXT NOT NULL,
  status TEXT NOT NULL,             -- 'Filled', 'Rejected', 'Failed', 'Partial'
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Active positions tracking
CREATE TABLE positions (
  id TEXT PRIMARY KEY,
  symbol TEXT NOT NULL UNIQUE,
  side TEXT NOT NULL,
  quantity REAL NOT NULL,
  entry_price REAL NOT NULL,
  mark_price REAL DEFAULT 0,
  leverage INTEGER DEFAULT 1,
  unrealized_pnl REAL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Account balance snapshots
CREATE TABLE balances (
  id TEXT PRIMARY KEY,
  total_balance REAL NOT NULL,
  available_balance REAL NOT NULL,
  unrealized_pnl REAL DEFAULT 0,
  snapshot_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Incoming signal audit trail
CREATE TABLE trade_signals (
  id TEXT PRIMARY KEY,
  raw_payload TEXT NOT NULL,
  parsed_symbol TEXT,
  parsed_action TEXT,
  received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- System diagnostic logs
CREATE TABLE system_logs (
  id TEXT PRIMARY KEY,
  level TEXT NOT NULL,              -- 'info', 'warn', 'error'
  source TEXT NOT NULL,             -- Worker name
  message TEXT NOT NULL,
  metadata TEXT,                    -- JSON blob of additional context
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

---

Applying Schemas & Tables

When launching a new workspace, you must initialize the required tables. The Hoox CLI handles this via declarative migration scripts:

hoox db apply

# 2. Deploy schema and seed tables directly to live production Cloudflare D1
hoox db apply --remote

Migration Files Location

Migrations are stored in workers/d1-worker/src/drizzle/ and are automatically versioned:

workers/d1-worker/src/drizzle/
├── 0001_initial_schema.sql
├── 0002_add_leverage_column.sql
├── 0003_balances_snapshot_table.sql
└── meta/
    └── snapshot.json

---

Managing Database Migrations

When new features are added that require changes to the database structure, you must run migrations:

# List all applied and pending database migrations in production
hoox db migrate status --remote

# Apply all pending schema migrations sequentially to production D1
hoox db migrate --remote

# Rollback the most recent migration (use with caution)
hoox db migrate rollback --remote

The migration engine tracks history inside a special d1_migrations table, ensuring that migrations are never executed twice or applied in the wrong sequence.

Migration Best Practices

1. Always write reversible migrations — include both UP and DOWN scripts.

2. Never modify existing migrations — create new ones for schema changes.

3. Test locally first — run hoox db apply without --remote to verify.

4. Backup before migrating — export your production database first.

---

Querying Data from the CLI

The Hoox CLI features a built-in SQL interface allowing you to run arbitrary queries directly against your local or remote database:

# A. List all active tables in your production database
hoox db list --remote

# B. Count the total number of executed trades
hoox db query "SELECT COUNT(*) FROM trades" --remote

# C. Inspect the 5 most recent trade fills with formatting
hoox db query "SELECT created_at, symbol, action, price, quantity FROM trades ORDER BY created_at DESC LIMIT 5" --remote

# D. Check currently open positions on Bybit
hoox db query "SELECT symbol, side, size, entry_price FROM positions WHERE size > 0" --remote

# E. Calculate daily P&L
hoox db query "SELECT DATE(created_at) as day, SUM(CASE WHEN side='BUY' THEN -price*quantity ELSE price*quantity END) as daily_pnl FROM trades WHERE status='Filled' GROUP BY day ORDER BY day DESC LIMIT 7" --remote

# F. Export trades as CSV
hoox db query "SELECT * FROM trades ORDER BY created_at DESC" --remote --format csv > trades_export.csv

Useful Query Templates

-- Win rate calculation
SELECT
  COUNT(CASE WHEN realized_pnl > 0 THEN 1 END) * 100.0 / COUNT(*) AS win_rate_pct
FROM trades WHERE status = 'Filled';

-- Average trade size by exchange
SELECT exchange, AVG(quantity * price) as avg_trade_size
FROM trades WHERE status = 'Filled'
GROUP BY exchange;

-- Hourly trading distribution (for optimal timing)
SELECT strftime('%H', created_at) as hour, COUNT(*) as trade_count
FROM trades WHERE status = 'Filled'
GROUP BY hour ORDER BY hour;

---

Backup & Export Workflows

To secure your historical P&L records, transaction ledger, and bot performance telemetry, execute regular exports:

# Export the entire D1 database as a clean SQL script
hoox db export --remote

# Export to a specific file path
hoox db export --remote --output backups/my-backup.sql

Export Output & Structure

The export command creates a timestamped SQL dump inside your workspace directory: backups/db-backup-2026-05-19-174000.sql

This file contains standard DDL and DML commands:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS trades (...);
INSERT INTO trades VALUES(...);
COMMIT;

Restoring from Backup

# 1. Create a fresh database first
hoox infra d1 create hoox-db-backup

# 2. Apply the backup SQL
bunx wrangler d1 execute hoox-db-backup --file=backups/db-backup-2026-05-19.sql

---

Database Reset (Destructive Operations)

If you are running simulated paper trading and wish to wipe all ledger history to start fresh, you can reset the tables:

# Wipe all tables in the local development database
hoox db reset --confirm

# Wipe all tables in the live production D1 database (USE WITH EXTREME CAUTION)
hoox db reset --remote --confirm

Soft Reset Alternative

If you want to clear data but preserve schema structure:

# Truncate all data tables while keeping schema intact
hoox db query "DELETE FROM trades; DELETE FROM positions; DELETE FROM balances; DELETE FROM system_logs;" --remote

---

D1 Free Tier Limits & Monitoring

---

Next Steps

• Local Development & Testing — Run your local V8 wrangler isolates with local D1 SQLite bindings.
• Infrastructure Management — Manage KV config namespaces, Queue parameters, and R2 storage buckets.
• Schema Reference — Detailed DDL documentation and storage engineering details.