CCXT Structure Schema Layer

This document defines the first database-facing contract layer for the Elixir CCXT target. The goal is to derive database schemas from CCXT unified structures without coupling exchange runtime code to a specific database.

Layering

The schema path has three layers:

1. Raw exchange payloads
   • Store original websocket messages, REST responses, and WS API responses.
   • Use JSONB or the target database equivalent.
   • Raw retention is mandatory because unified structures intentionally omit exchange-specific fields.
2. CCXT unified structures
   • Store normalized ticker, trade, order_book, ohlcv, balance, order, position, and liquidation structures.
   • The contract lives in priv/ccxt_structures/binance_pro_structures.json.
   • Numeric values are database decimals even when current runtime output is an Elixir number or numeric string.
3. Application state
   • Strategy state, risk state, reconciliation markers, and workflow status belong outside the CCXT structure layer.

Manifest

The first manifest is:

priv/ccxt_structures/binance_pro_structures.json

It records:

• sourceMethods: Elixir methods that emit the structure.
• canonicalTable: suggested current-state table.
• historyTable: suggested event/history table when the structure is mutable.
• fields: CCXT unified keys, runtime accepted types, nullable policy, and recommended database type.
• primaryKey: suggested logical identity, not a generated migration.

The manifest is intentionally database-agnostic. Ecto schema and migration templates are generated from it, while runtime metadata is exposed through Ccxt.StructureSchema.

State And Event Split

Use separate state and event tables for mutable websocket domains:

State tables should be upserted by logical identity. Event tables should be append-oriented and should keep enough exchange metadata to rebuild state.

Numeric Policy

The runtime currently emits a mix of Elixir numbers and numeric strings, depending on the parser path and how close the field is to CCXT TypeScript semantics. The database contract is stricter:

price, amount, cost, volume, fee, pnl, contract values -> decimal
timestamp -> bigint
datetime -> utc_datetime_usec
raw payload / info / nested fees / nested trades -> jsonb

Do not store prices, amounts, or balances as floats in the database. Convert at the boundary into Decimal or the database driver's decimal type.

Raw Payload Retention

Each normalized row should be traceable to raw data.

• If the parser output has info, store it in the structure table.
• Always keep a raw event/response table for websocket messages and WS API responses.
• Balance structures may not expose a single info field for all paths, so raw balance responses must be retained in the raw layer.

Ccxt.RawPayload provides the runtime contract for that raw layer:

{:ok, raw_attrs} =
  Ccxt.RawPayload.normalize_ws_event(payload,
    exchange_id: "binance",
    env: "prod",
    market_type: "spot",
    stream: "btcusdt@miniTicker"
  )

{:ok, %{raw: raw, raw_plan: raw_plan, attrs: attrs, plans: plans}} =
  Ccxt.RawPayload.normalize_structure_with_raw(:ticker, ticker, payload,
    exchange_id: "binance",
    env: "prod",
    market_type: "spot"
  )

attrs.raw_ref_id == raw.id

Suggested raw tables:

Raw attrs include:

id
exchange_id
env
market_type
account_type
transport
channel
stream
request_id
message_hash
url
payload
received_at

message_hash is a deterministic SHA-256 hash over the canonicalized Elixir payload term. It is intended for dedupe/debugging and does not replace the raw payload itself.

Suggested Tables

The manifest currently maps to these canonical table names:

• ccxt_tickers
• ccxt_ticker_events
• ccxt_order_books
• ccxt_order_book_events
• ccxt_trades
• ccxt_ohlcvs
• ccxt_balances
• ccxt_balance_events
• ccxt_orders
• ccxt_order_events
• ccxt_positions
• ccxt_position_events
• ccxt_liquidations

The first migration generator should add common columns to every table:

id
exchange_id
market_type
account_type
symbol
inserted_at
updated_at
raw_ref_id

Only fields that make sense for the specific structure should be populated.

Verification

test/ccxt_pro_binance_test.exs loads the manifest and validates it against current parser output for:

• ticker
• trade
• order book
• ohlcv
• balance
• order
• position
• liquidation

This keeps the schema layer tied to the generated/runtime output without adding database concerns to Ccxt.Pro.Binance.

Generated Templates

The manifest can already emit database templates:

npm run generateElixirStructureSchemas
npm run assertElixirStructureSchemas

Generated files:

priv/ccxt_structures/generated/binance_pro_ecto_schemas.exs
priv/ccxt_structures/generated/binance_pro_migration.exs

These are templates, not compiled runtime modules in this package. Copy them into the consuming Ecto application, review table prefixes/index names, and wire them to the application repo module before running migrations.

Runtime Metadata

Ccxt.StructureSchema exposes the same manifest contract at runtime:

Ccxt.StructureSchema.structures()
Ccxt.StructureSchema.table_names()
Ccxt.StructureSchema.schema_targets(:ticker)
Ccxt.StructureSchema.field_entries(:order)
Ccxt.StructureSchema.required_fields(:ohlcv)
Ccxt.StructureSchema.primary_key(:order)
Ccxt.StructureSchema.source_methods(:order_book)

This is the stable lookup layer for a consuming app that wants to derive schemas, changesets, upsert keys, or persistence routing from CCXT unified structures without parsing generated .exs templates.

Attr Normalization

Ccxt.StructureNormalizer converts CCXT unified output into attrs shaped for the generated Ecto templates:

{:ok, attrs} =
  Ccxt.StructureNormalizer.normalize(:ticker, ticker,
    exchange_id: "binance",
    market_type: "spot"
  )

The normalizer:

• converts CCXT camelCase fields to snake_case
• maps CCXT business id to ccxt_id to avoid Ecto primary-key collisions
• emits decimal fields as strings for downstream Ecto :decimal casting
• wraps JSONB list values as %{"data" => list} so generated :map fields can accept them
• expands balance maps into one attrs row per currency

Persistence Contract

Ccxt.StructurePersistence derives insert/upsert plans from the same manifest:

{:ok, [state_plan, event_plan]} = Ccxt.StructurePersistence.plans(:ticker)

state_plan.table
# "ccxt_tickers"

state_plan.operation
# :upsert

Ccxt.StructurePersistence.ecto_options(state_plan)
# [
#   conflict_target: [:exchange_id, :market_type, :symbol],
#   on_conflict: {:replace, state_plan.update_fields}
# ]

The contract is intentionally Repo-agnostic:

• canonical state tables use :upsert when the manifest defines a stable logical identity
• history/event tables use append-only :insert
• event-only structures such as trades and liquidations use append-only :insert
• source_method_plans/1 maps a high-level Pro method such as :stream_order_book to the persistence plans for its emitted structure

Runnable persistence dry-run example:

cd elixir
mix run examples/pro_structure_persistence.exs

The example streams public ticker updates, prints a raw insert plan first, then normalizes the ticker with raw_ref_id and prints the state/history persistence plans a consuming application would pass to its database layer.