71 lines
3.0 KiB
SQL
71 lines
3.0 KiB
SQL
-- ============================================================================
|
|
-- Schema: trading
|
|
-- Table: positions
|
|
-- Description: Posiciones abiertas y cerradas de trading
|
|
-- Dependencies: auth.users, trading.bots, trading.symbols
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE trading.positions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
-- Referencias
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
bot_id UUID REFERENCES trading.bots(id) ON DELETE SET NULL,
|
|
symbol_id UUID NOT NULL REFERENCES trading.symbols(id),
|
|
|
|
-- Tipo
|
|
position_side trading.order_side NOT NULL, -- buy (long), sell (short)
|
|
status trading.position_status NOT NULL DEFAULT 'open',
|
|
|
|
-- Entrada
|
|
entry_price DECIMAL(20,8) NOT NULL,
|
|
entry_quantity DECIMAL(20,8) NOT NULL,
|
|
entry_value DECIMAL(20,8) NOT NULL,
|
|
entry_commission DECIMAL(20,8) DEFAULT 0,
|
|
|
|
-- Posiciones parciales (para cierres parciales y promedios)
|
|
current_quantity DECIMAL(20,8) NOT NULL, -- Cantidad actual (puede diferir de entry_quantity)
|
|
average_entry_price DECIMAL(20,8) NOT NULL, -- Precio promedio de entrada (para DCA)
|
|
|
|
-- Salida
|
|
exit_price DECIMAL(20,8),
|
|
exit_quantity DECIMAL(20,8),
|
|
exit_value DECIMAL(20,8),
|
|
exit_commission DECIMAL(20,8) DEFAULT 0,
|
|
|
|
-- PnL
|
|
realized_pnl DECIMAL(20,8) DEFAULT 0,
|
|
unrealized_pnl DECIMAL(20,8) DEFAULT 0,
|
|
pnl_percentage DECIMAL(10,4) DEFAULT 0,
|
|
|
|
-- Stop loss y take profit
|
|
stop_loss_price DECIMAL(20,8),
|
|
take_profit_price DECIMAL(20,8),
|
|
|
|
-- Metadata
|
|
metadata JSONB DEFAULT '{}',
|
|
close_reason VARCHAR(50), -- 'take_profit', 'stop_loss', 'manual', 'liquidation'
|
|
|
|
-- Timestamps
|
|
opened_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
closed_at TIMESTAMPTZ,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Índices
|
|
CREATE INDEX idx_positions_user ON trading.positions(user_id);
|
|
CREATE INDEX idx_positions_bot ON trading.positions(bot_id);
|
|
CREATE INDEX idx_positions_symbol ON trading.positions(symbol_id);
|
|
CREATE INDEX idx_positions_status ON trading.positions(status);
|
|
CREATE INDEX idx_positions_opened ON trading.positions(opened_at DESC);
|
|
CREATE INDEX idx_positions_user_status ON trading.positions(user_id, status);
|
|
|
|
-- Comentarios
|
|
COMMENT ON TABLE trading.positions IS 'Posiciones de trading abiertas y cerradas';
|
|
COMMENT ON COLUMN trading.positions.position_side IS 'buy = posición larga, sell = posición corta';
|
|
COMMENT ON COLUMN trading.positions.realized_pnl IS 'Ganancia/pérdida realizada (posición cerrada)';
|
|
COMMENT ON COLUMN trading.positions.unrealized_pnl IS 'Ganancia/pérdida no realizada (posición abierta)';
|
|
COMMENT ON COLUMN trading.positions.close_reason IS 'Razón del cierre: take_profit, stop_loss, manual, liquidation';
|
|
COMMENT ON COLUMN trading.positions.current_quantity IS 'Cantidad actual de la posición (puede diferir de entry_quantity tras cierres parciales)';
|
|
COMMENT ON COLUMN trading.positions.average_entry_price IS 'Precio promedio de entrada (calculado con DCA o adiciones)';
|