rckrdmrd
45e77e9a9c
DDL schemas for Trading Platform: - User management - Authentication - Payments - Education - ML predictions - Trading data Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
52 lines
2.3 KiB
SQL
52 lines
2.3 KiB
SQL
-- ============================================================================
|
|
-- TRADING SCHEMA - Tabla: paper_balances
|
|
-- ============================================================================
|
|
-- Balances virtuales para paper trading
|
|
-- Permite a usuarios practicar sin riesgo real
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS trading.paper_balances (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
-- Usuario
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
|
|
-- Asset y balance
|
|
asset VARCHAR(10) NOT NULL DEFAULT 'USDT',
|
|
total DECIMAL(20, 8) NOT NULL DEFAULT 10000.00,
|
|
available DECIMAL(20, 8) NOT NULL DEFAULT 10000.00,
|
|
locked DECIMAL(20, 8) NOT NULL DEFAULT 0,
|
|
|
|
-- Tracking
|
|
initial_balance DECIMAL(20, 8) NOT NULL DEFAULT 10000.00,
|
|
total_deposits DECIMAL(20, 8) NOT NULL DEFAULT 0,
|
|
total_withdrawals DECIMAL(20, 8) NOT NULL DEFAULT 0,
|
|
total_pnl DECIMAL(20, 8) NOT NULL DEFAULT 0,
|
|
|
|
-- Reset tracking
|
|
last_reset_at TIMESTAMPTZ,
|
|
reset_count INTEGER NOT NULL DEFAULT 0,
|
|
|
|
-- Timestamps
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
-- Constraints
|
|
CONSTRAINT uq_paper_balances_user_asset UNIQUE(user_id, asset),
|
|
CONSTRAINT chk_balance_consistency CHECK (total = available + locked),
|
|
CONSTRAINT chk_balance_non_negative CHECK (total >= 0 AND available >= 0 AND locked >= 0),
|
|
CONSTRAINT chk_initial_positive CHECK (initial_balance > 0)
|
|
);
|
|
|
|
-- Indices
|
|
CREATE INDEX idx_paper_balances_user ON trading.paper_balances(user_id);
|
|
CREATE INDEX idx_paper_balances_asset ON trading.paper_balances(asset);
|
|
|
|
-- Comentarios
|
|
COMMENT ON TABLE trading.paper_balances IS 'Balances virtuales para paper trading - cada usuario tiene balance por asset';
|
|
COMMENT ON COLUMN trading.paper_balances.total IS 'Balance total = available + locked';
|
|
COMMENT ON COLUMN trading.paper_balances.available IS 'Balance disponible para nuevas ordenes';
|
|
COMMENT ON COLUMN trading.paper_balances.locked IS 'Balance bloqueado en ordenes abiertas';
|
|
COMMENT ON COLUMN trading.paper_balances.initial_balance IS 'Balance inicial (default $10,000 USDT)';
|
|
COMMENT ON COLUMN trading.paper_balances.reset_count IS 'Numero de veces que el usuario ha reseteado su balance';
|