68 lines
2.4 KiB
SQL
68 lines
2.4 KiB
SQL
-- ============================================================================
|
|
-- Schema: trading
|
|
-- Table: orders
|
|
-- Description: Órdenes de trading (pendientes, ejecutadas, canceladas)
|
|
-- Dependencies: auth.users, trading.bots, trading.symbols
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE trading.orders (
|
|
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),
|
|
|
|
-- Identificador externo (de exchange)
|
|
external_order_id VARCHAR(100),
|
|
|
|
-- Tipo y lado
|
|
order_type trading.order_type NOT NULL,
|
|
order_side trading.order_side NOT NULL,
|
|
status trading.order_status NOT NULL DEFAULT 'pending',
|
|
|
|
-- Precios
|
|
price DECIMAL(20,8), -- Precio límite (NULL para market orders)
|
|
stop_price DECIMAL(20,8), -- Precio stop
|
|
average_fill_price DECIMAL(20,8), -- Precio promedio de ejecución
|
|
|
|
-- Cantidades
|
|
quantity DECIMAL(20,8) NOT NULL,
|
|
filled_quantity DECIMAL(20,8) DEFAULT 0,
|
|
remaining_quantity DECIMAL(20,8) NOT NULL,
|
|
|
|
-- Costos
|
|
commission DECIMAL(20,8) DEFAULT 0,
|
|
commission_asset VARCHAR(10),
|
|
|
|
-- Time in force
|
|
time_in_force VARCHAR(20) DEFAULT 'GTC', -- GTC, IOC, FOK
|
|
|
|
-- Validez
|
|
expires_at TIMESTAMPTZ,
|
|
|
|
-- Metadata
|
|
metadata JSONB DEFAULT '{}',
|
|
error_message TEXT,
|
|
|
|
-- Timestamps
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
filled_at TIMESTAMPTZ,
|
|
cancelled_at TIMESTAMPTZ
|
|
);
|
|
|
|
-- Índices
|
|
CREATE INDEX idx_orders_user ON trading.orders(user_id);
|
|
CREATE INDEX idx_orders_bot ON trading.orders(bot_id);
|
|
CREATE INDEX idx_orders_symbol ON trading.orders(symbol_id);
|
|
CREATE INDEX idx_orders_status ON trading.orders(status);
|
|
CREATE INDEX idx_orders_created ON trading.orders(created_at DESC);
|
|
CREATE INDEX idx_orders_external ON trading.orders(external_order_id);
|
|
|
|
-- Comentarios
|
|
COMMENT ON TABLE trading.orders IS 'Órdenes de trading (todas las órdenes del sistema)';
|
|
COMMENT ON COLUMN trading.orders.external_order_id IS 'ID de la orden en el exchange externo';
|
|
COMMENT ON COLUMN trading.orders.time_in_force IS 'GTC (Good Till Cancel), IOC (Immediate or Cancel), FOK (Fill or Kill)';
|
|
COMMENT ON COLUMN trading.orders.average_fill_price IS 'Precio promedio de ejecución para órdenes parcialmente completadas';
|