💾 Storage & Data Engineering Spec

Hoox operates a multi-tier edge storage topology to balance high-speed read/write performance, relational transactional safety, and long-term analytical capacity. By separating high-frequency dynamic states from static parameters and verbose event logs, Hoox ensures that latency remains in the single-digit milliseconds while keeping storage costs at $0/month.

1. The Multi-Tier Storage Architecture

Hoox segregates data into four distinct edge storage tiers based on consistency, read/write latency, and size constraints:

graph TD
    Pipeline["📥 Incoming Request Pipeline"] -->|Route| DO["💎 Durable Objects<br/>Strong Consistency<br/>(Idempotency Locks)"]
    Pipeline -->|Route| KV["⚡ CONFIG_KV<br/>Eventual Consistency<br/>(sub-ms reads, 10s write)"]
    Pipeline -->|Route| D1["🗄️ D1 SQLite<br/>Relational ACID<br/>(5ms SQL queries)"]

    DO -->|Persist / Log| R2["📦 R2 Object Storage<br/>Zero-Egress Bucket<br/>(Heavy JSON Fills / PDFs)"]
    KV -->|Persist / Log| R2
    D1 -->|Persist / Log| R2

    style Pipeline fill:#1e293b,stroke:#3b82f6,stroke-width:2
    style DO fill:#1e293b,stroke:#f59e0b,stroke-width:2
    style KV fill:#1e293b,stroke:#10b981,stroke-width:2
    style D1 fill:#1e293b,stroke:#ef4444,stroke-width:2
    style R2 fill:#1e293b,stroke:#a855f7,stroke-width:2

🗄️ 2. D1 Relational Engine & Drizzle Schema Specs

D1 runs a serverless SQLite engine embedded in the Cloudflare V8 worker isolate thread. This eliminates TCP handshake overhead when executing SQL queries.

Drizzle ORM Schema Standards

Hoox developers use Drizzle ORM to define strict type safety for D1 SQLite tables. Below is the active specification for our trades and positions schemas:

import { sqliteTable, text, real, integer } from "drizzle-orm/sqlite-core";

// 1. Transactional Trades Ledger Schema
export const trades = sqliteTable("trades", {
  id: text("id").primaryKey(), // UUIDv4
  requestId: text("request_id").notNull(), // Distributed Trace ID
  exchange: text("exchange").notNull(), // "bybit" | "binance" | "mexc"
  symbol: text("symbol").notNull(), // Uppercase symbol: "BTCUSDT"
  action: text("action").notNull(), // "LONG" | "SHORT" | "CLOSE"
  side: text("side").notNull(), // "BUY" | "SELL"
  quantity: real("quantity").notNull(), // Executed contract quantity
  price: real("price").notNull(), // Execution fill price
  fee: real("fee").notNull(), // Exchange transaction fee paid in quote
  orderId: text("order_id").notNull(), // Exchange-provided order identifier
  status: text("status").notNull(), // "Filled" | "Failed"
  createdAt: integer("created_at", { mode: "timestamp" }).default(new Date()),
});

// 2. Real-Time Open Position Matrix Schema
export const positions = sqliteTable("positions", {
  symbol: text("symbol").primaryKey(),
  exchange: text("exchange").notNull(),
  side: text("side").notNull(), // "LONG" | "SHORT"
  size: real("size").notNull(), // Current contract size
  entryPrice: real("entry_price").notNull(), // Volume-weighted average entry price
  leverage: integer("leverage").default(1),
  updatedAt: integer("updated_at", { mode: "timestamp" }).default(new Date()),
});

📦 3. R2 Log Offloading: SQLite Conservation Strategy

SQLite engines are single-writer databases. If write concurrency is too high, transaction queues can lock the thread. To conserve D1 write limits and maintain high performance during high-frequency events:

1. D1 Relational Logs: Only high-value financial transactions (the structured fields in the trades table above) are written to D1.
2. R2 Verbose Blobs: Full, verbose JSON payload exchanges, network headers, and WebSocket stream records are serialized and saved to R2 Storage using date-based key paths: logs/bybit/BTCUSDT/2026-05-19/order-18049284739.json

This offloading strategy reduces D1 SQLite write volumes by up to 75%, keeping your database compact, fast, and fully within free-tier limits.

🔑 4. KV Eventual Consistency & Cache Performance

Cloudflare KV is a highly distributed key-value store optimized for high-read/low-write operations:

• Eventual Consistency: When you toggle a setting in KV (e.g. trade:kill_switch = true), the change is instantly cached at your local gateway node. However, it takes up to 10 seconds to propagate to Cloudflare’s other 330+ locations globally.
• Operational Rule: KV is perfect for global configurations, allowlists, and emergency kill switches. It is never used to track highly dynamic real-time states like position sizes, account drawdowns, or margin balances. High-frequency states must always be routed through D1 or Durable Objects.

Danger: Never attempt to run raw migration files directly on your production D1 database without first running a backup. Ensure your database status is fully tracked and synchronized using Drizzle migrations: hoox db migrate --remote.

🔗 Next Steps

• Internal Endpoints Mapping — Map out exposed ports, URLs, and service bindings.
• Wrangler Setup & Tooling — Configure Wrangler to bind local D1 and KV instances for dev testing.