BlockbotX Database Schema Reference

Overview

• Database: PostgreSQL 16
• ORM: Drizzle ORM 0.45 with pg (node-postgres) driver
• Connection Pooling: PgBouncer in transaction mode
• Financial Precision: All monetary and percentage fields use Decimal(65,30) to prevent floating-point rounding errors
• Migration Strategy: Schema files in drizzle/schema/*.ts, migrations generated via pnpm db:generate and applied via pnpm db:push or pnpm db:migrate
• ID Strategy: CUID strings ($defaultFn(() => createId())) on all models except SystemSettings (singleton "singleton" ID)
• Timestamps: createdAt with .$defaultFn(() => new Date()), updatedAt with .$onUpdate(() => new Date()) where applicable
• Cascade Deletes: All child records cascade on parent deletion (except SecurityLog which uses SetNull for optional userId, and Trade which uses SetNull for optional botId)
• Table Naming: All tables use snake_case names as the first argument to pgTable() (e.g., pgTable('users', { ... }))
• Custom Types: drizzle/custom-types.ts defines a custom decimalNumber type using customType that converts between PostgreSQL Decimal(65,30) and decimal.js Decimal objects

---

Data Models by Domain

Users and Authentication

User

The core user model. All other domain models relate back to User.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
email	String	.unique()	Login email
password	String		Hashed password
firstName	String?
lastName	String?
avatar	String?		Avatar URL
bio	String?		User biography
phone	String?		Phone number
location	String?
website	String?
company	String?
position	String?		Job title
emailVerified	Boolean	.default(false)	Whether email is verified
emailVerifiedAt	DateTime?		Verification timestamp
emailVerificationToken	String?		Pending verification token
emailVerificationExpiry	DateTime?		Token expiry
resetPasswordToken	String?		Password reset token
resetPasswordExpiry	DateTime?		Reset token expiry
twoFactorEnabled	Boolean	.default(false)	2FA toggle
twoFactorSecret	String?		TOTP secret
twoFactorBackupCodes	String[]		Encrypted backup codes array
failedLoginAttempts	Int	.default(0)	Brute-force counter
lockedUntil	DateTime?		Account lockout expiry
status	String	.default('active')	active, suspended, banned, disabled
bannedAt	DateTime?		When banned
bannedReason	String?		Ban reason
bannedBy	String?		Admin who banned
suspendedUntil	DateTime?		Suspension expiry
suspendedReason	String?		Suspension reason
forcePasswordReset	Boolean	.default(false)	Force reset on next login
role	String	.default('user')	user, admin, superadmin
timezone	String?		User timezone
language	String	.default('en')	UI language
theme	String	.default('light')	light, dark
currency	String	.default('USD')	Display currency (USD, EUR, GBP, etc.)
referralCode	String?	.unique()	Unique referral code
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Indexes: email, status Table: users

Session

Refresh token sessions for JWT authentication. Expiry is 7 days.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String	FK -> User	Owning user
refreshToken	String	.unique()	JWT refresh token
expiresAt	DateTime		Session expiry (7 days)
ipAddress	String?		Client IP at login
userAgent	String?		Browser/device user agent
createdAt	DateTime	.defaultNow()

Indexes: userId, refreshToken, expiresAt Cascade: Deletes when User is deleted Table: sessions

TwoFactorAuth

TOTP-based two-factor authentication configuration. One-to-one with User.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String	.unique(), FK -> User	One per user
secret	String		TOTP secret key
backupCodes	String[]		Encrypted backup codes
enabled	Boolean	.default(false)	Whether 2FA is active
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Table: two_factor_auth

LoginHistory

Audit trail of all login attempts (successful and failed).

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String	FK -> User
ipAddress	String		Client IP
userAgent	String?		Browser user agent
success	Boolean		Whether login succeeded
failureReason	String?		Reason for failure
createdAt	DateTime	.defaultNow()

Indexes: userId, createdAt Table: login_history

KnownDevice

Trusted device fingerprints for detecting new-device logins.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String	FK -> User
deviceFingerprint	String		SHA-256 hash of userAgent
deviceName	String		Parsed browser + OS description
ipAddress	String		IP at time of registration
lastUsedAt	DateTime	.defaultNow()	Last login from this device
trusted	Boolean	.default(true)	Trust status
createdAt	DateTime	.defaultNow()

Unique: unique([userId, deviceFingerprint]) Indexes: userId Table: known_devices

PrivacySettings

GDPR consent flags and profile visibility preferences. One-to-one with User.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String	.unique(), FK -> User	One per user
profileVisibility	String	.default('private')	public, private, friends
showEmail	Boolean	.default(false)
showPhone	Boolean	.default(false)
allowMessagesFrom	String	.default('contacts')	everyone, contacts, nobody
dataUsageConsent	Boolean	.default(false)	GDPR data usage consent
marketingEmailsConsent	Boolean	.default(false)	Marketing opt-in
thirdPartyDataSharing	Boolean	.default(false)	Third-party sharing consent
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Table: privacy_settings

DataExport

GDPR data export requests and their processing status.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String	FK -> User	Requesting user
exportType	String		trades, bots, settings, profile, all
status	String		pending, processing, completed, failed
fileUrl	String?		Download URL when completed
expiresAt	DateTime?		Link expiry
createdAt	DateTime	.defaultNow()

Indexes: userId, status Table: data_exports

---

Exchanges and Trading

Exchange

Metadata about supported exchange platforms.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
name	String	.unique()	Exchange name
apiUrl	String		REST API base URL
websocketUrl	String?		WebSocket endpoint
supportedPairs	Json		Array of trading pairs
fees	Json		Fee structure
active	Boolean	.default(true)	Whether exchange is enabled
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Relations: tradingPairs, trades, orders Table: exchanges

ExchangeConnection

Encrypted API key storage for user exchange connections. Supports Binance and OKX.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String	FK -> User	Owning user
exchange	String		binance, okx, etc.
encryptedApiKey	String		AES-256-GCM encrypted API key
encryptedSecretKey	String		AES-256-GCM encrypted secret
encryptedPassphrase	String?		OKX passphrase (null for Binance)
isActive	Boolean	.default(true)	Connection enabled
isTestnet	Boolean	.default(false)	true = demo/testnet, false = production
permissions	String[]	.default(['read'])	read, trade (never withdraw)
label	String?		User-friendly label
lastUsed	DateTime?		Last API call timestamp
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Unique: unique([userId, exchange]) -- one connection per exchange per user Indexes: userId, exchange Table: exchange_connections

TradingPair

Exchange-specific trading pair configuration with precision rules.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
exchangeId	String	FK -> Exchange
symbol	String		e.g., BTCUSDT
base	String		Base asset, e.g., BTC
quote	String		Quote asset, e.g., USDT
minOrderSize	Decimal		Minimum order quantity
maxOrderSize	Decimal?		Maximum order quantity
pricePrecision	Int		Decimal places for price
quantityPrecision	Int		Decimal places for quantity
active	Boolean	.default(true)
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Unique: unique([exchangeId, symbol]) Indexes: symbol Table: trading_pairs

Trade

Executed trades -- both paper and live. Central to P&L tracking.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String	FK -> User
botId	String?	FK -> Bot (SetNull)	Bot that executed (null for manual)
exchangeId	String?	FK -> Exchange
pairId	String?	FK -> TradingPair
positionId	String?	FK -> Position
orderId	String?	FK -> Order
symbol	String		Trading pair symbol
side	String		BUY, SELL
type	String	.default('MARKET')	MARKET, LIMIT
price	Decimal		Intended price
quantity	Decimal		Trade quantity
executedPrice	Decimal		Actual execution price
fee	Decimal	.default(0)	Trading fee
feeCurrency	String	.default('USDT')	Fee denomination
total	Decimal	.default(0)	Total trade value
profitLoss	Decimal?		Realized P&L
balance	Decimal	.default(0)	Account balance after trade
status	String	.default('FILLED')	Trade status
isPaperTrade	Boolean	.default(true)	Paper vs. live
executedAt	DateTime	.defaultNow()	Execution timestamp

Indexes: userId, botId, exchangeId, executedAt, symbol, profitLoss Composite Indexes: [botId, executedAt DESC], [userId, executedAt DESC], [userId, symbol, executedAt DESC], [userId, status, executedAt DESC] Table: trades

Order

Order lifecycle management from placement through fill or cancellation.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String	FK -> User
botId	String?	FK -> Bot (SetNull)
exchangeId	String	FK -> Exchange
pairId	String	FK -> TradingPair
type	String		market, limit, stop_loss, take_profit
side	String		buy, sell
price	Decimal?		Limit price (null for market)
quantity	Decimal		Order quantity
filled	Decimal	.default(0)	Filled quantity
status	String		pending, open, filled, cancelled, failed
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())
filledAt	DateTime?		Fill timestamp

Indexes: userId, botId, status, createdAt Table: orders

Position

Open and closed trading positions with full P&L tracking.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
botId	String	FK -> Bot
symbol	String		Trading pair
side	String		BUY
quantity	Decimal		Position size
entryPrice	Decimal		Entry price
currentPrice	Decimal		Latest price
stopLoss	Decimal		Stop loss level
takeProfit	Decimal		Take profit level
unrealizedPnL	Decimal	.default(0)	Current unrealized P&L
unrealizedPnLPercent	Decimal	.default(0)	Unrealized P&L as percentage
status	String	.default('open')	open, closed
exitPrice	Decimal?		Price at close
profitLoss	Decimal?		Realized P&L
profitLossPercent	Decimal?		Realized P&L percentage
closeReason	String?		STOP_LOSS, TAKE_PROFIT, SIGNAL, BOT_STOPPED
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())
closedAt	DateTime?		Close timestamp

Indexes: botId, status, symbol Composite Indexes: [botId, status] Table: positions

PortfolioHistory

Periodic snapshots of portfolio value for charting.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String	FK -> User
totalValue	Decimal		Portfolio value in USD
assets	Json		Array of assets with amounts and USD values
timestamp	DateTime	.defaultNow()	Snapshot time

Indexes: userId, timestamp Table: portfolio_history

---

Trading Bots

Bot

Core bot configuration and lifecycle management.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String	FK -> User	Owner
name	String		Bot name
description	String?
type	String	.default('ai')	ai, signal, dca, arbitrage, pump_screener
status	String	.default('inactive')	active, inactive, paused, error
config	Json		Bot-type-specific configuration
riskSettings	Json		Risk management parameters
paperTrading	Boolean	.default(true)	Paper trading mode
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())
startedAt	DateTime?		Last start time
stoppedAt	DateTime?		Last stop time
lastExecutionAt	DateTime?		Most recent execution

Indexes: userId, type, status Composite Indexes: [userId, status], [status, lastExecutionAt], [userId, type, status] Relations: executions, trades, orders, positions, performanceSnapshots, backtests, pumpDetections Table: bots

Execution

Individual bot execution records -- each decision cycle.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
botId	String	FK -> Bot
executionId	String	.unique()	Idempotency key
signal	String		BUY, SELL, HOLD
confidence	Decimal		Signal confidence (0-100)
reason	String		Human-readable reason
price	Decimal		Price at execution time
indicators	Json		Technical indicator values
status	String		success, error
error	String?		Error message if failed
timestamp	DateTime	.defaultNow()

Indexes: botId, timestamp, signal Table: executions

PerformanceSnapshot

Periodic bot performance metrics for tracking and display.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
botId	String	FK -> Bot
timestamp	DateTime	.defaultNow()	Snapshot time
metrics	Json		Detailed metrics blob
riskScore	Decimal		Computed risk score
rating	String		excellent, good, fair, poor
totalTrades	Int		Total trade count
winRate	Decimal		Win percentage
totalReturn	Decimal		Absolute return
totalReturnPercent	Decimal		Return as percentage
maxDrawdownPercent	Decimal		Maximum drawdown percentage
sharpeRatio	Decimal		Risk-adjusted return metric
profitFactor	Decimal		Gross profit / gross loss

Indexes: botId, timestamp Composite Indexes: [botId, timestamp DESC] Table: performance_snapshots

Backtest

Backtesting results against historical data.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
botId	String	FK -> Bot
userId	String	FK -> User
config	Json		BacktestConfig parameters
result	Json		BacktestReport results
executionTime	Int		Duration in milliseconds
createdAt	DateTime	.defaultNow()

Indexes: botId, userId, createdAt Table: backtests

HistoricalDataset

OHLCV candle cache for backtesting, avoiding repeated API fetches.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
symbol	String		Trading pair symbol
timeframe	String		1m, 5m, 15m, 1h, 4h, 1d
startDate	DateTime		Dataset start
endDate	DateTime		Dataset end
candleCount	Int		Number of candles
candles	Json		Array of Candle objects (OHLCV)
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Unique: unique([symbol, timeframe, startDate, endDate]) Indexes: symbol, timeframe, updatedAt Table: historical_datasets

BotExecution

Execution tracking specifically for the arbitrage-bot engine.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
botId	String		Bot ID (no FK relation)
status	String		running, completed, failed
logs	Json?		Execution log entries
profitLoss	Decimal?		Realized P&L from this execution
tradesCount	Int	.default(0)	Number of trades executed
startedAt	DateTime	.defaultNow()
endedAt	DateTime?		Completion time

Indexes: botId, startedAt Table: bot_executions

PumpDetection

Pump screener bot alerts for unusual price/volume activity.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
botId	String	FK -> Bot
userId	String	FK -> User
symbol	String		Detected symbol
priceChange	Decimal		Percentage price change
volumeChange	Decimal		Percentage volume change
currentPrice	Decimal		Price at detection
marketCap	Decimal?		Market capitalization
volume24h	Decimal?		24-hour trading volume
socialMentions	Int	.default(0)	Social media mention count
confidence	String		high, medium, low
detectedAt	DateTime	.defaultNow()

Indexes: userId, botId, detectedAt, symbol Table: pump_detections

Analytics

General analytics snapshots across multiple domains.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String	FK -> User
type	String		portfolio, bot_performance, trading, defi, market_analysis
period	String		24h, 7d, 30d, 90d, 1y, all
data	Json		Analytics payload
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Indexes: userId, type, period, createdAt Composite Indexes: [userId, type, period] Table: analytics

---

Signal Providers

SignalProvider

External signal sources (webhooks, APIs, Telegram bots).

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
name	String		Provider name
type	String		webhook, api, telegram
url	String?		Webhook/API endpoint
apiKey	String?		Encrypted API key
config	Json		Provider-specific configuration
isActive	Boolean	.default(true)
reliability	Decimal	.default(0)	0-100 based on historical accuracy
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Indexes: name, type, isActive Relations: signals Table: signal_providers

Signal

Individual trading signals from providers.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
providerId	String	FK -> SignalProvider
symbol	String		Trading pair
action	String		BUY, SELL, HOLD
price	Decimal?		Target price
stopLoss	Decimal?		Suggested stop loss
takeProfit	Decimal?		Suggested take profit
confidence	Decimal?		0-100 confidence score
reason	String?		Signal rationale
metadata	Json?		Additional signal data
processed	Boolean	.default(false)	Whether signal has been acted on
createdAt	DateTime	.defaultNow()

Indexes: providerId, symbol, action, processed, createdAt Composite Indexes: [processed, createdAt] Table: signals

---

DeFi and Wallets

Wallet

User blockchain wallets for DeFi interactions.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String	FK -> User
address	String		Blockchain address
chain	String		ethereum, bsc, polygon
name	String?		User-friendly name
isActive	Boolean	.default(true)
encryptedKey	String?		For managed wallets only
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Unique: unique([userId, address, chain]) Indexes: userId, address, chain Relations: stakingPositions, farmPositions, liquidityPositions Table: wallets

StakingPosition

Token staking positions across DeFi protocols.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
walletId	String	FK -> Wallet
protocol	String		aave, compound, custom
chain	String		ethereum, bsc, polygon
tokenAddress	String		Staked token contract address
tokenSymbol	String		Staked token symbol
amount	Decimal		Staked amount
valueUSD	Decimal		Current USD value
apy	Decimal		Annual percentage yield
rewards	Decimal	.default(0)	Accumulated rewards
stakedAt	DateTime		When staking began
lastUpdated	DateTime	.defaultNow()	Last price/reward update
status	String	.default('active')	active, unstaking, completed
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Indexes: walletId, protocol, chain, status Composite Indexes: [walletId, status], [chain, status] Table: staking_positions

FarmPosition

Yield farming positions with LP token and reward tracking.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
walletId	String	FK -> Wallet
protocol	String		uniswap, pancakeswap, sushiswap
chain	String		Blockchain network
farmAddress	String		Farm contract address
lpTokenAddress	String		LP token contract address
lpTokenAmount	Decimal		LP token quantity
token0Symbol	String		First token symbol
token1Symbol	String		Second token symbol
token0Amount	Decimal		First token amount
token1Amount	Decimal		Second token amount
valueUSD	Decimal		Current USD value
apy	Decimal		Annual percentage yield
rewardsEarned	Decimal	.default(0)	Accumulated rewards
rewardTokenSymbol	String?		Reward token symbol
depositedAt	DateTime		Deposit timestamp
lastHarvest	DateTime?		Last reward harvest
lastUpdated	DateTime	.defaultNow()	Last update
status	String	.default('active')	active, withdrawn
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Indexes: walletId, protocol, chain, status Composite Indexes: [walletId, status], [chain, status] Table: farm_positions

LiquidityPosition

Liquidity pool positions with impermanent loss tracking.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
walletId	String	FK -> Wallet
protocol	String		uniswap-v2, uniswap-v3, pancakeswap
chain	String		Blockchain network
poolAddress	String		Pool contract address
lpTokenAddress	String		LP token contract address
lpTokenAmount	Decimal		LP token quantity
token0Symbol	String		First token symbol
token1Symbol	String		Second token symbol
token0Amount	Decimal		Current first token amount
token1Amount	Decimal		Current second token amount
initialToken0Amount	Decimal		Initial first token amount
initialToken1Amount	Decimal		Initial second token amount
initialValueUSD	Decimal		Value at deposit
currentValueUSD	Decimal		Current USD value
impermanentLoss	Decimal	.default(0)	IL amount
feesEarned	Decimal	.default(0)	Trading fees earned
addedAt	DateTime		Liquidity add timestamp
lastUpdated	DateTime	.defaultNow()
status	String	.default('active')	active, removed
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Indexes: walletId, protocol, chain, status Composite Indexes: [walletId, status], [chain, status] Table: liquidity_positions

DeFiProtocol

Registry of DeFi protocols with TVL and volume metrics.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
name	String	.unique()	Protocol name
chain	String		Primary blockchain
category	String		dex, lending, staking, yield
tvl	Decimal	.default(0)	Total value locked
volume24h	Decimal	.default(0)	24-hour trading volume
website	String?		Protocol website
logo	String?		Logo URL
description	String?		Protocol description
isActive	Boolean	.default(true)
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Indexes: chain, category, isActive Table: defi_protocols

LiquidityPool

Metadata about available liquidity pools.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
protocol	String		Protocol name
chain	String		Blockchain network
pair	String		e.g., ETH/USDT
token0	String		First token
token1	String		Second token
tvl	Decimal		Total value locked
apy	Decimal		Annual percentage yield
volume24h	Decimal		24-hour volume
active	Boolean	.default(true)
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Unique: unique([protocol, chain, pair]) Indexes: protocol, chain Table: liquidity_pools

---

Marketplace

Strategy

Trading strategies and bot configurations sold in the marketplace.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
creatorId	String	FK -> User	Strategy author
name	String		Strategy name
description	String		Description
price	Decimal		Price in USD
type	String		bot_config, trading_strategy
category	String?		trend_following, arbitrage, etc.
config	Json		Strategy configuration
performance	Json?		Performance metrics
rating	Decimal	.default(0)	Average rating
downloads	Int	.default(0)	Download/purchase count
active	Boolean	.default(true)	Listed status
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Indexes: creatorId, category, rating Relations: reviews, purchases Table: strategies

StrategyReview

User reviews and ratings for marketplace strategies.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
strategyId	String	FK -> Strategy
userId	String		Reviewer (no FK relation)
rating	Int		1-5 star rating
comment	String?		Review text
createdAt	DateTime	.defaultNow()

Unique: unique([strategyId, userId]) -- one review per user per strategy Indexes: strategyId Table: strategy_reviews

StrategyPurchase

Purchase records for marketplace strategies.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String		Buyer (no FK relation)
strategyId	String	FK -> Strategy
price	Decimal		Price paid
status	String	.default('completed')	completed, refunded
createdAt	DateTime	.defaultNow()

Unique: unique([userId, strategyId]) -- one purchase per user per strategy Indexes: userId, strategyId Table: strategy_purchases

---

Subscriptions and Payments

Subscription

User subscription plans with Stripe integration.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String	FK -> User
plan	String		free, basic, pro, enterprise
status	String		active, cancelled, expired
billingCycle	String		monthly, yearly
amount	Decimal		Subscription price
stripeCustomerId	String?	.unique()	Stripe customer ID
stripeSubscriptionId	String?	.unique()	Stripe subscription ID
startedAt	DateTime	.defaultNow()
expiresAt	DateTime		Subscription expiry
cancelledAt	DateTime?		Cancellation timestamp
grantedBy	String?		Admin who granted (for complimentary)
grantReason	String?		Reason for complimentary grant
isComplimentary	Boolean	.default(false)	Admin-granted subscription

Indexes: userId, status Relations: history Table: subscriptions

SubscriptionHistory

Audit trail of subscription plan changes.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
subscriptionId	String	FK -> Subscription
plan	String		Plan at time of change
status	String		Status at time of change
billingCycle	String		Billing cycle at time of change
amount	Decimal		Amount at time of change
changeReason	String?		Reason for change
createdAt	DateTime	.defaultNow()

Indexes: subscriptionId Table: subscription_history

Payment

Payment transactions for subscriptions and strategy purchases.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String	FK -> User
amount	Decimal		Payment amount
currency	String	.default('USD')	Payment currency
type	String		subscription, strategy_purchase, etc.
status	String		pending, completed, failed, refunded
stripePaymentId	String?	.unique()	Stripe payment intent ID
metadata	Json?		Additional payment data
createdAt	DateTime	.defaultNow()
completedAt	DateTime?		Completion timestamp

Indexes: userId, status Table: payments

Referral

Referral tracking between users with reward management.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
referrerId	String	FK -> User ("ReferrerReferrals")	User who referred
referredId	String	.unique(), FK -> User ("ReferredReferrals")	User who was referred
code	String	.unique()	Referral code used
rewardAmount	Decimal	.default(0)	Reward value
status	String	.default('pending')	pending, active, rewarded
createdAt	DateTime	.defaultNow()
rewardedAt	DateTime?		When reward was paid

Indexes: referrerId, code Table: referrals

---

Bot Templates

BotTemplate

Pre-built or user-created bot configuration templates.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String?	FK -> User	Null for system templates
name	String		Template name
description	String		Template description
category	String		trading, defi, arbitrage
type	String?		dca, grid, ai, signal, etc.
complexity	String		beginner, intermediate, advanced
visibility	String	.default('private')	public, private
config	Json		Template configuration
defaultSettings	Json?		Default settings to apply
tags	String[]		Searchable tags
usageCount	Int	.default(0)	Deployment count
featured	Boolean	.default(false)	Homepage featured flag
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Indexes: category, complexity, featured, userId, visibility Relations: deployments Table: bot_templates

TemplateDeployment

Records of templates deployed as actual bots by users.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
templateId	String	FK -> BotTemplate
userId	String		Deploying user (no FK relation)
botId	String?		Deployed bot ID (no FK relation)
customization	Json		User customizations applied
deployedAt	DateTime	.defaultNow()

Indexes: templateId, userId Table: template_deployments

---

Notifications

Notification

Multi-channel notification delivery with scheduling and tracking.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String	FK -> User
type	NotificationType	enum	See Key Enums section
channel	NotificationChannel	enum	Delivery channel
priority	NotificationPriority	.default('NORMAL')	LOW, NORMAL, HIGH, CRITICAL
title	String		Notification title
message	String	.text()	Notification body (unlimited length)
metadata	Json?		Additional data
read	Boolean	.default(false)	Read status
delivered	Boolean	.default(false)	Delivery status
deliveredAt	DateTime?		Delivery timestamp
failureReason	String?		Delivery failure reason
scheduledFor	DateTime?		Future delivery time
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Indexes: userId, read, delivered, createdAt, type, channel, priority Table: notifications

NotificationPreference

Per-user, per-channel notification preferences with quiet hours. One-to-one with User.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String	.unique(), FK -> User	One per user
emailEnabled	Boolean	.default(true)	Master email toggle
emailBotAlerts	Boolean	.default(true)	Bot alert emails
emailTrades	Boolean	.default(true)	Trade notification emails
emailPayments	Boolean	.default(true)	Payment emails
emailSupport	Boolean	.default(true)	Support ticket emails
emailMarketing	Boolean	.default(false)	Marketing emails (opt-in)
inAppEnabled	Boolean	.default(true)	Master in-app toggle
inAppBotAlerts	Boolean	.default(true)	In-app bot alerts
inAppTrades	Boolean	.default(true)	In-app trade notifications
inAppPayments	Boolean	.default(true)	In-app payment notifications
inAppSupport	Boolean	.default(true)	In-app support notifications
telegramEnabled	Boolean	.default(false)	Telegram toggle
telegramChatId	String?		Telegram chat ID
telegramBotAlerts	Boolean	.default(true)	Telegram bot alerts
telegramTrades	Boolean	.default(true)	Telegram trade notifications
telegramCriticalOnly	Boolean	.default(false)	Only CRITICAL priority
digestMode	Boolean	.default(false)	Batch notifications
digestFrequency	String	.default('realtime')	realtime, hourly, daily
quietHoursEnabled	Boolean	.default(false)	Quiet hours toggle
quietHoursStart	Int?		Start hour (0-23)
quietHoursEnd	Int?		End hour (0-23)
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Table: notification_preferences

---

Admin and System

AuditLog

Audit trail of user actions across the platform.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String	FK -> User	Acting user
action	String		login, api_key_created, trade_executed, etc.
details	Json?		Action-specific data
ipAddress	String?		Client IP
createdAt	DateTime	.defaultNow()

Indexes: userId, action, createdAt Table: audit_logs

AdminNote

CRM-style notes that admins attach to user accounts.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String	FK -> User ("UserAdminNotes")	Subject user
authorId	String	FK -> User ("AuthorAdminNotes")	Admin who wrote the note
content	String	.text()	Note content (unlimited length)
category	String	.default('general')	general, billing, security, compliance
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Indexes: userId Table: admin_notes

SecurityLog

Security event tracking -- authentication failures, rate limits, suspicious activity.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
type	String		auth_failure, rate_limit, csrf_failure, suspicious_activity
userId	String?	FK -> User (SetNull)	Associated user (optional)
ipAddress	String		Client IP
userAgent	String		Browser user agent
metadata	Json?		Event-specific data
createdAt	DateTime	.defaultNow()

Indexes: type, ipAddress, userId, createdAt Cascade: Uses SetNull on User deletion (preserves security logs) Table: security_logs

BlockedIP

IP blocklist for automated and manual blocking.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
ipAddress	String	.unique()	Blocked IP address
reason	String		Block reason
blockedAt	DateTime	.defaultNow()	When blocked
expiresAt	DateTime?		Block expiry (null = permanent)
createdBy	String?		Admin user ID or system
metadata	Json?		Additional context

Indexes: ipAddress, expiresAt Table: blocked_ips

SystemSettings

Global system configuration. Singleton model (fixed ID "singleton").

Field	Type	Constraints	Description
id	String	primaryKey, .default('singleton')	Always "singleton"
maintenanceMode	Boolean	.default(false)	Maintenance mode toggle
maintenanceMessage	String?		User-facing maintenance message
registrationsOpen	Boolean	.default(true)	Allow new registrations
updatedAt	DateTime	.$onUpdate(() => new Date())
updatedBy	String?		Admin who last updated

Table: system_settings

---

Help and Support

HelpArticle

Knowledge base articles for the help center.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
title	String		Article title
slug	String	.unique()	URL slug
content	String		Article body
categoryId	String	FK -> HelpCategory
tags	String[]		Searchable tags
views	Int	.default(0)	View count
published	Boolean	.default(false)	Published status
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Indexes: categoryId, published, slug Table: help_articles

HelpCategory

Categories for organizing help articles.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
name	String	.unique()	Category name
slug	String	.unique()	URL slug
description	String?		Category description
icon	String?		Icon identifier
order	Int	.default(0)	Display order
createdAt	DateTime	.defaultNow()

Relations: articles Table: help_categories

FAQ

Frequently asked questions, independent of articles.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
question	String		FAQ question
answer	String		FAQ answer
categoryId	String?		Optional category (no FK relation)
order	Int	.default(0)	Display order
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())

Table: faqs

SupportTicket

User support tickets with status and priority tracking.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
userId	String	FK -> User
subject	String		Ticket subject
category	String		technical, billing, general
status	String	.default('open')	open, in_progress, closed
priority	String	.default('normal')	low, normal, high, urgent
assignedTo	String?		Staff member ID
createdAt	DateTime	.defaultNow()
updatedAt	DateTime	.$onUpdate(() => new Date())
closedAt	DateTime?		Close timestamp

Indexes: userId, status, priority, category Relations: messages Table: support_tickets

TicketMessage

Individual messages within a support ticket thread.

Field	Type	Constraints	Description
id	String	primaryKey, $defaultFn(() => createId())	Primary key
ticketId	String	FK -> SupportTicket
userId	String	FK -> User	Message author
content	String		Message body
isStaffReply	Boolean	.default(false)	Staff vs. user message
createdAt	DateTime	.defaultNow()

Indexes: ticketId, createdAt Table: ticket_messages

---

Key Enums

NotificationType (28 values)

Category	Values
Bot Alerts	BOT_CREATED, BOT_STARTED, BOT_STOPPED, BOT_ERROR, TRADE_EXECUTED, STOP_LOSS_HIT, TAKE_PROFIT_HIT, DAILY_REPORT, WEEKLY_REPORT
Payment & Subscription	PAYMENT_SUCCESS, PAYMENT_FAILED, SUBSCRIPTION_CREATED, SUBSCRIPTION_UPGRADED, SUBSCRIPTION_CANCELLED, SUBSCRIPTION_RENEWED
Support	TICKET_CREATED, TICKET_MESSAGE, TICKET_STATUS_CHANGED, TICKET_CLOSED
Marketplace	STRATEGY_PURCHASED, STRATEGY_REVIEW
Referral	REFERRAL_JOINED, REFERRAL_ACTIVATED, REWARD_EARNED
Security	LOGIN_NEW_DEVICE, PASSWORD_CHANGED, TWO_FA_ENABLED, ACCOUNT_LOCKED

NotificationChannel (6 values)

EMAIL, IN_APP, TELEGRAM, DISCORD, PUSH, SMS

NotificationPriority (4 values)

LOW, NORMAL, HIGH, CRITICAL

String-Based Enums (used as String fields with convention)

Field	Model(s)	Allowed Values
role	User	user, admin, superadmin
status (user)	User	active, suspended, banned, disabled
type (bot)	Bot	ai, signal, dca, arbitrage, pump_screener
status (bot)	Bot	active, inactive, paused, error
side (trade)	Trade	BUY, SELL
type (trade)	Trade	MARKET, LIMIT
type (order)	Order	market, limit, stop_loss, take_profit
side (order)	Order	buy, sell
status (order)	Order	pending, open, filled, cancelled, failed
status (position)	Position	open, closed
closeReason	Position	STOP_LOSS, TAKE_PROFIT, SIGNAL, BOT_STOPPED
signal	Execution	BUY, SELL, HOLD
action	Signal	BUY, SELL, HOLD
type (signal provider)	SignalProvider	webhook, api, telegram
rating	PerformanceSnapshot	excellent, good, fair, poor
plan	Subscription	free, basic, pro, enterprise
status (subscription)	Subscription	active, cancelled, expired
billingCycle	Subscription	monthly, yearly
status (ticket)	SupportTicket	open, in_progress, closed
priority	SupportTicket	low, normal, high, urgent
category (ticket)	SupportTicket	technical, billing, general
type (analytics)	Analytics	portfolio, bot_performance, trading, defi, market_analysis
period	Analytics	24h, 7d, 30d, 90d, 1y, all
status (referral)	Referral	pending, active, rewarded
status (defi)	StakingPosition	active, unstaking, completed
status (farm)	FarmPosition	active, withdrawn
status (liquidity)	LiquidityPosition	active, removed
confidence	PumpDetection	high, medium, low
exportType	DataExport	trades, bots, settings, profile, all
category (protocol)	DeFiProtocol	dex, lending, staking, yield
type (strategy)	Strategy	bot_config, trading_strategy
complexity	BotTemplate	beginner, intermediate, advanced
visibility	BotTemplate	public, private
profileVisibility	PrivacySettings	public, private, friends

---

Financial Precision

Why Decimal(65,30) Instead of Float

Float (IEEE 754 double-precision) introduces rounding errors in financial calculations. For example:

0.1 + 0.2 = 0.30000000000000004 (Float)
0.1 + 0.2 = 0.3                 (Decimal)

In a trading platform, these errors accumulate across thousands of trades, leading to:

• Incorrect P&L calculations
• Wrong balance displays
• Flawed trading decisions based on inaccurate data
• Audit discrepancies

Fields Using Decimal

Model	Decimal Fields
Position (10 fields)	quantity, entryPrice, currentPrice, stopLoss, takeProfit, unrealizedPnL, unrealizedPnLPercent, exitPrice, profitLoss, profitLossPercent
PerformanceSnapshot (7 fields)	riskScore, winRate, totalReturn, totalReturnPercent, maxDrawdownPercent, sharpeRatio, profitFactor
Trade (7 fields)	price, quantity, executedPrice, fee, total, profitLoss, balance
Signal (4 fields)	price, stopLoss, takeProfit, confidence
Execution (2 fields)	confidence, price
SignalProvider (1 field)	reliability
StakingPosition (4 fields)	amount, valueUSD, apy, rewards
FarmPosition (6 fields)	lpTokenAmount, token0Amount, token1Amount, valueUSD, apy, rewardsEarned
LiquidityPosition (9 fields)	lpTokenAmount, token0Amount, token1Amount, initialToken0Amount, initialToken1Amount, initialValueUSD, currentValueUSD, impermanentLoss, feesEarned
DeFiProtocol (2 fields)	tvl, volume24h
LiquidityPool (3 fields)	tvl, apy, volume24h
PortfolioHistory (1 field)	totalValue
Order (3 fields)	price, quantity, filled
TradingPair (2 fields)	minOrderSize, maxOrderSize
Strategy (2 fields)	price, rating
StrategyPurchase (1 field)	price
Subscription (1 field)	amount
SubscriptionHistory (1 field)	amount
Payment (1 field)	amount
Referral (1 field)	rewardAmount
PumpDetection (5 fields)	priceChange, volumeChange, currentPrice, marketCap, volume24h
BotExecution (1 field)	profitLoss

Using Decimal in Application Code

Decimal columns return Decimal objects (from decimal.js), not JavaScript number values. The custom type in drizzle/custom-types.ts handles automatic conversion between PostgreSQL Decimal(65,30) and decimal.js Decimal objects. Key patterns:

import Decimal from 'decimal.js';

// Display: convert to number
const price = position.entryPrice.toNumber();

// Arithmetic: use Decimal methods
const pnl = position.currentPrice.sub(position.entryPrice).mul(position.quantity);

// Comparison
if (position.unrealizedPnL.greaterThan(0)) { /* profit */ }

// Creating new Decimal values for writes
const newPrice = new Decimal("123.456789");

---

Key Constraints and Indexes

Unique Constraints

Model	Constraint	Purpose
User	email	One account per email
User	referralCode	Unique referral codes
ExchangeConnection	unique([userId, exchange])	One connection per exchange per user
KnownDevice	unique([userId, deviceFingerprint])	One entry per device per user
TradingPair	unique([exchangeId, symbol])	No duplicate pairs per exchange
Wallet	unique([userId, address, chain])	No duplicate wallets
HistoricalDataset	unique([symbol, timeframe, startDate, endDate])	No duplicate datasets
LiquidityPool	unique([protocol, chain, pair])	No duplicate pools
StrategyReview	unique([strategyId, userId])	One review per user per strategy
StrategyPurchase	unique([userId, strategyId])	One purchase per user per strategy
Referral	referredId (unique)	Each user can only be referred once
Referral	code (unique)	Unique referral codes
Session	refreshToken (unique)	Unique refresh tokens
Execution	executionId (unique)	Idempotency on executions
Subscription	stripeCustomerId (unique)	One Stripe customer per subscription
Subscription	stripeSubscriptionId (unique)	One Stripe subscription per record
Payment	stripePaymentId (unique)	One Stripe payment per record
BlockedIP	ipAddress (unique)	One block entry per IP

Notable Composite Indexes

These indexes are designed for the most common query patterns:

Model	Index	Query Pattern
Trade	[botId, executedAt DESC]	Bot trade history, newest first
Trade	[userId, executedAt DESC]	User trade history
Trade	[userId, symbol, executedAt DESC]	User trades for a specific symbol
Trade	[userId, status, executedAt DESC]	User trades filtered by status
Bot	[userId, status]	User's bots by status
Bot	[status, lastExecutionAt]	Active bots needing execution
Bot	[userId, type, status]	User's bots by type and status
Position	[botId, status]	Bot's open/closed positions
PerformanceSnapshot	[botId, timestamp DESC]	Latest performance for a bot
Analytics	[userId, type, period]	User analytics by type and period
Signal	[processed, createdAt]	Unprocessed signals queue
StakingPosition	[walletId, status], [chain, status]	Active staking by wallet/chain
FarmPosition	[walletId, status], [chain, status]	Active farming by wallet/chain
LiquidityPosition	[walletId, status], [chain, status]	Active liquidity by wallet/chain

---

Relationships Diagram

User (users)
 |
 |-- 1:many --> Session (sessions)
 |-- 1:1   --> TwoFactorAuth (two_factor_auth)
 |-- 1:many --> LoginHistory (login_history)
 |-- 1:many --> KnownDevice (known_devices)
 |-- 1:1   --> PrivacySettings (privacy_settings)
 |-- 1:many --> DataExport (data_exports)
 |
 |-- 1:many --> ExchangeConnection (exchange_connections)
 |-- 1:many --> PortfolioHistory (portfolio_history)
 |
 |-- 1:many --> Bot (bots)
 |                |-- 1:many --> Execution (executions)
 |                |-- 1:many --> Trade (trades)
 |                |-- 1:many --> Order (orders)
 |                |-- 1:many --> Position (positions)
 |                |               |-- 1:many --> Trade (trades)
 |                |-- 1:many --> PerformanceSnapshot (performance_snapshots)
 |                |-- 1:many --> Backtest (backtests)
 |                |-- 1:many --> PumpDetection (pump_detections)
 |
 |-- 1:many --> Trade (trades)     [direct user trades]
 |-- 1:many --> Order (orders)     [direct user orders]
 |
 |-- 1:many --> Wallet (wallets)
 |                |-- 1:many --> StakingPosition (staking_positions)
 |                |-- 1:many --> FarmPosition (farm_positions)
 |                |-- 1:many --> LiquidityPosition (liquidity_positions)
 |
 |-- 1:many --> Strategy (strategies)
 |                |-- 1:many --> StrategyReview (strategy_reviews)
 |                |-- 1:many --> StrategyPurchase (strategy_purchases)
 |
 |-- 1:many --> Subscription (subscriptions)
 |                |-- 1:many --> SubscriptionHistory (subscription_history)
 |-- 1:many --> Payment (payments)
 |
 |-- 1:many --> Referral (referrals)  [as referrer]
 |-- 1:1   --> Referral (referrals)  [as referred, unique]
 |
 |-- 1:many --> Notification (notifications)
 |-- 1:1   --> NotificationPreference (notification_preferences)
 |
 |-- 1:many --> AuditLog (audit_logs)
 |-- 1:many --> AdminNote (admin_notes)  [as subject]
 |-- 1:many --> AdminNote (admin_notes)  [as author]
 |-- 1:many --> SecurityLog (security_logs)
 |
 |-- 1:many --> SupportTicket (support_tickets)
 |                |-- 1:many --> TicketMessage (ticket_messages)
 |-- 1:many --> TicketMessage (ticket_messages)  [as message author]
 |
 |-- 1:many --> Backtest (backtests)
 |-- 1:many --> BotTemplate (bot_templates)
 |                |-- 1:many --> TemplateDeployment (template_deployments)
 |-- 1:many --> PumpDetection (pump_detections)
 |-- 1:many --> Analytics (analytics)


Exchange (exchanges)
 |-- 1:many --> TradingPair (trading_pairs)
 |-- 1:many --> Trade (trades)
 |-- 1:many --> Order (orders)


SignalProvider (signal_providers)
 |-- 1:many --> Signal (signals)


HelpCategory (help_categories)
 |-- 1:many --> HelpArticle (help_articles)


Standalone Models (no parent relations):
 - BotExecution (bot_executions)   [botId is plain String, no FK]
 - DeFiProtocol (defi_protocols)
 - LiquidityPool (liquidity_pools)
 - FAQ (faqs)
 - BlockedIP (blocked_ips)
 - SystemSettings (system_settings)  [singleton]

---

Migration Strategy

Schema is defined in TypeScript files under drizzle/schema/*.ts (e.g., users.ts, trading.ts, bots.ts, defi.ts). Each file exports pgTable() definitions. Migrations are stored in drizzle/migrations/.

Workflow:

1. Edit schema -- modify the relevant drizzle/schema/*.ts file
2. Generate migration -- pnpm db:generate (runs drizzle-kit generate)
3. Apply to database -- pnpm db:push (runs drizzle-kit push) for development, or pnpm db:migrate (runs drizzle-kit migrate) for production
4. Inspect visually -- pnpm db:studio (runs drizzle-kit studio)
5. Seed data -- pnpm db:seed (runs tsx drizzle/seed.ts)

No generate step needed -- unlike Prisma, Drizzle uses TypeScript schema files directly. There is no client generation step.