Database Operations

Apply the D1 schema, run tracking migrations, query the ledger, and export backups with hoox db.

This page

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. Prefer hoox db over raw Wrangler D1 commands.

🏗️ The Core Database Tables

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

TablePurposeRow Estimate (Active User)
tradesThe primary ledger. Execution prices, contract quantities, timestamps, fees, and exchange order IDs.~10,000/mo
positionsTracks open margin/futures exposure (average entry price, size, leverage, direction).~50–200
balancesPeriodic snapshots of account equity, margin balance, and available free collateral.~30/day
trade_signalsHistorical record of every raw incoming webhook alert before execution processing.~10,000/mo
system_logsCrucial debug and error messages offloaded from compute nodes.~5,000/mo

Canonical DDL is workers/trade-worker/schema.sql (CREATE TABLE IF NOT EXISTS — re-applying does not wipe rows). Column names below match that file:

sql
CREATE TABLE IF NOT EXISTS trade_signals (
  signal_id TEXT PRIMARY KEY,
  timestamp INTEGER NOT NULL,
  symbol TEXT NOT NULL,
  signal_type TEXT NOT NULL,
  source TEXT,
  raw_data TEXT,
  tenant_id TEXT NOT NULL DEFAULT '',
  processed_at INTEGER DEFAULT (unixepoch())
);

CREATE TABLE IF NOT EXISTS trades (
  id TEXT PRIMARY KEY,
  signal_id TEXT,
  timestamp INTEGER NOT NULL,
  exchange TEXT NOT NULL,
  symbol TEXT NOT NULL,
  action TEXT NOT NULL,             -- LONG / SHORT / CLOSE_LONG / CLOSE_SHORT
  quantity REAL,
  price REAL,
  leverage INTEGER,
  status TEXT NOT NULL,             -- EXECUTED / TEST_EXECUTED / FAILED / PENDING
  error_message TEXT,
  raw_response TEXT,
  tenant_id TEXT NOT NULL DEFAULT '',
  created_at INTEGER DEFAULT (unixepoch())
);

CREATE TABLE IF NOT EXISTS positions (
  id TEXT PRIMARY KEY,
  exchange TEXT NOT NULL,
  symbol TEXT NOT NULL,
  side TEXT NOT NULL,               -- LONG or SHORT
  entry_price REAL,
  mark_price REAL,
  liquidation_price REAL,
  leverage INTEGER,
  size REAL,
  unrealized_pnl REAL,
  status TEXT NOT NULL,             -- OPEN or CLOSED
  tenant_id TEXT NOT NULL DEFAULT '',
  updated_at INTEGER DEFAULT (unixepoch())
);

tenant_id is present for opt-in tenant isolation (off by default). Existing single-tenant rows stay at ''.

⚡ Applying Schemas & Tables

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

bash
hoox db apply

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

Warning

Running hoox db apply executes workers/trade-worker/schema.sql against the local D1. For production, append --remote.

🔄 Managing Database Migrations

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

bash
# Apply tracking migrations (local)
hoox db migrate

# Apply tracking migrations on production D1
hoox db migrate --remote

There is no migrate status or migrate rollback subcommand. Export first (hoox db export --remote) if you need a restore point.

Migration Best Practices

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

  2. Backup before migrating — hoox db export --remote.

  3. Prefer additive DDL — the shipped schema uses CREATE TABLE IF NOT EXISTS so re-apply is safe.

🔎 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:

bash
# 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
hoox db query "SELECT symbol, side, size, entry_price FROM positions WHERE status = 'OPEN'" --remote

# E. Recent fills
hoox db query "SELECT timestamp, exchange, symbol, action, price, quantity, status FROM trades ORDER BY timestamp DESC LIMIT 7" --remote

Useful Query Templates

sql
-- Average notional by exchange (live fills)
SELECT exchange, AVG(quantity * price) as avg_trade_size
FROM trades WHERE status = 'EXECUTED'
GROUP BY exchange;

-- Hourly trading distribution
SELECT strftime('%H', datetime(timestamp, 'unixepoch')) as hour, COUNT(*) as trade_count
FROM trades WHERE status = 'EXECUTED'
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:

bash
# 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:

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

Restoring from Backup

bash
# 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:

bash
# 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

Warning

Wiping your production D1 database is an irreversible operation. It will permanently delete all trade logs, position records, and asset histories. Always execute hoox db export --remote before running a reset!

Soft Reset Alternative

If you want to clear data but preserve schema structure:

bash
# Truncate data tables while keeping schema intact (requires --allow-destructive)
hoox db query "DELETE FROM trades" --remote --allow-destructive
hoox db query "DELETE FROM positions" --remote --allow-destructive

📋 D1 Free Tier Limits & Monitoring

ResourceFree Tier LimitMonitoring Command
Rows Read/Day5,000,000hoox db query "SELECT COUNT(*) FROM trades WHERE created_at >= date('now', '-1 day')"
Rows Written/Day100,000hoox db query "SELECT COUNT(*) FROM trades WHERE created_at >= date('now', '-1 day')"
Storage5 GBhoox db query "SELECT page_count * page_size FROM pragma_page_count(), pragma_page_size()"
Max ConnectionsConcurrentN/A — serverless, no connection pool

🔗 Next Steps