Technical Documentation

Data Models

This document describes the entity schemas that underpin the Open Mandi exchange. Financial values are represented using decimal types in the data model to minimize floating-point precision issues.

Entity Relationship Overview

User 1──* Wallet
User 1──* Order
User 1──* Position
Order 1──* Trade (as maker or taker)
Wallet 1──* Transaction
Position 1──* Trade

User

Represents a registered account on the platform.

User {
  id:           UUID        (primary key)
  firebaseUid:  string      (unique, indexed — Firebase Auth identifier)
  email:        string      (unique, indexed)
  createdAt:    timestamp
  updatedAt:    timestamp

  // Authentication is handled by Firebase Auth; no passwords or password
  // hashes are stored in Postgres.
}

Wallet

Each user has two wallets: one for USDT and one for USDC. The balance is the total funds, while availableBalance excludes funds locked as margin or in pending orders.

Wallet {
  id:               UUID        (primary key)
  userId:           UUID        (foreign key → User)
  currency:         enum        ("USDT" | "USDC")
  balance:          decimal     (total balance)
  availableBalance: decimal     (balance - locked funds)
  updatedAt:        timestamp

  UNIQUE(userId, currency)
}

Invariant: availableBalance <= balance at all times.

Order

Represents a buy or sell order on any of the three order books (USDT/USDC, XAU, XAG).

Order {
  id:             UUID        (primary key)
  userId:         UUID        (foreign key → User)
  pair:           enum        ("USDT-USDC" | "XAU-USD" | "XAG-USD")
  side:           enum        ("buy" | "sell")
  type:           enum        ("limit" | "market")
  price:          decimal?    (null for market orders)
  quantity:       decimal     (total order quantity)
  filledQuantity: decimal     (quantity already matched)
  status:         enum        ("open" | "partial" | "filled" | "cancelled")
  createdAt:      timestamp
  updatedAt:      timestamp

  INDEX(pair, status, price)  -- for order book queries
  INDEX(userId, status)       -- for user's open orders
}

Trade

Represents a single execution (fill) between a maker order and a taker order. One order can generate multiple trades (partial fills).

Trade {
  id:             UUID        (primary key)
  pair:           enum        ("USDT-USDC" | "XAU-USD" | "XAG-USD")
  makerOrderId:   UUID        (foreign key → Order)
  takerOrderId:   UUID        (foreign key → Order)
  makerUserId:    UUID        (foreign key → User)
  takerUserId:    UUID        (foreign key → User)
  price:          decimal     (execution price)
  quantity:       decimal     (executed quantity)
  makerFee:       decimal     (fee charged to maker)
  takerFee:       decimal     (fee charged to taker)
  createdAt:      timestamp

  INDEX(pair, createdAt)      -- for recent trades query
}

Position

Represents an open futures position for a user.

Position {
  id:               UUID        (primary key)
  userId:           UUID        (foreign key → User)
  contract:         enum        ("XAU-PERP" | "XAG-PERP")
  side:             enum        ("long" | "short")
  entryPrice:       decimal     (average entry price)
  quantity:         decimal     (number of contracts)
  margin:           decimal     (collateral locked)
  collateralCurrency: enum      ("USDT" | "USDC")
  liquidationPrice: decimal     (calculated)
  realizedPnl:      decimal     (from partial closes)
  status:           enum        ("open" | "closed" | "liquidated")
  createdAt:        timestamp
  updatedAt:        timestamp

  INDEX(userId, status)
  INDEX(contract, status)     -- for open interest queries
}

Transaction (Ledger)

Immutable record of every balance-affecting operation. Serves as the audit trail and enables double-entry bookkeeping verification.

Transaction {
  id:             UUID        (primary key)
  userId:         UUID        (foreign key → User)
  walletId:       UUID        (foreign key → Wallet)
  type:           enum        ("deposit" | "withdrawal" | "trade_debit" |
                               "trade_credit" | "fee" | "margin_lock" |
                               "margin_release" | "liquidation" | "funding")
  currency:       enum        ("USDT" | "USDC")
  amount:         decimal     (signed: positive for credits, negative for debits)
  balanceAfter:   decimal     (wallet balance after this transaction)
  referenceId:    UUID?       (links to Order, Trade, or Position)
  referenceType:  string?     ("order" | "trade" | "position" | "deposit" | "withdrawal")
  createdAt:      timestamp

  INDEX(userId, createdAt)
  INDEX(walletId, createdAt)
  INDEX(type, createdAt)      -- for transparency aggregation
}

Design Principles

• Decimal arithmetic — all monetary values use fixed-precision decimal types (e.g., DECIMAL(18,8)) to prevent floating-point errors
• Immutable ledger — Transaction records are insert-only; corrections are made by adding compensating entries, never by modifying existing records
• Referential integrity — all foreign keys are enforced at the database level
• Soft deletes — users and orders use status fields rather than physical deletion
• Optimistic locking — wallet balance updates use version checks to prevent lost updates under concurrency