-- ============================================================
-- Migration 020: Add orders.sub_tag for deterministic correlation
-- Date: 2026-02-22
-- Purpose:
--   1) Persist Alpaca omnibus sub_tag on lifecycle/order rows.
--   2) Improve traceability for profile + trade reconciliation.
-- ============================================================

ALTER TABLE orders
    ADD COLUMN IF NOT EXISTS sub_tag text;

CREATE INDEX IF NOT EXISTS idx_orders_profile_trade_sub_tag
    ON orders (profile_id, trade_id, sub_tag)
    WHERE sub_tag IS NOT NULL;

-- -----------------------------------------------------------------
-- Reversal (manual):
--   DROP INDEX IF EXISTS idx_orders_profile_trade_sub_tag;
--   ALTER TABLE orders DROP COLUMN IF EXISTS sub_tag;
-- -----------------------------------------------------------------
