Foundations — Architecture for the Autonomous AI Agent

-- The instruments table stores every version of every security
-- valid_from/valid_to define when this version was "current"

SELECT symbol, name, sector, exchange,
       valid_from, valid_to
FROM   instruments
WHERE  symbol = 'AAPL'
ORDER BY valid_from;

-- Result:
-- symbol | name      | sector               | valid_from | valid_to
-- AAPL   | Apple Inc | Consumer Electronics | 2000-01-01 | 2018-09-28
-- AAPL   | Apple Inc | Technology           | 2018-09-28 | NULL

-- What did we know about AAPL on 2017-06-15?
SELECT *
FROM   instruments
WHERE  symbol = 'AAPL'
  AND  valid_from <= '2017-06-15'
  AND  (valid_to > '2017-06-15' OR valid_to IS NULL);

-- This returns the "Consumer Electronics" version
-- because the reclassification happened in Sep 2018

-- Fundamentals table preserves every reported AND restated value
SELECT symbol, period, metric, value,
       reported_at,    -- when this value was first published
       superseded_at   -- when a restatement replaced it (NULL = current)
FROM   fundamentals
WHERE  symbol = 'ENRON'
  AND  period = '2001-Q3'
  AND  metric = 'eps'
ORDER BY reported_at;

-- Result:
-- value | reported_at | superseded_at
-- 0.34  | 2001-10-16  | 2001-11-08    -- original report
-- -0.84 | 2001-11-08  | NULL          -- restated (massive loss)

-- Reusable pattern: "what fundamentals were known at date X?"
SELECT symbol, period, metric, value
FROM   fundamentals
WHERE  symbol = 'ENRON'
  AND  reported_at <= '2001-10-20'::date   -- as of this date
  AND  (superseded_at > '2001-10-20'::date
        OR superseded_at IS NULL);

-- Returns EPS = 0.34 (the original, not-yet-restated value)
-- This is exactly what a trader on Oct 20 would have seen

CREATE TABLE exchanges (
    exchange_id  SMALLINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    mic          CHAR(4)  NOT NULL UNIQUE,  -- ISO 10383 Market Identifier Code
    name         TEXT     NOT NULL,
    country      CHAR(2)  NOT NULL,         -- ISO 3166-1 alpha-2
    timezone     TEXT     NOT NULL,
    open_time    TIME,
    close_time   TIME,
    currency     CHAR(3)  NOT NULL,         -- ISO 4217
    region       TEXT     NOT NULL           -- 'US', 'EU', 'APAC', 'LATAM', 'MEA'
);

CREATE TABLE instruments (
    instrument_id   BIGINT GENERATED ALWAYS AS IDENTITY,  -- surrogate key (version-specific)
    instrument_key  UUID        NOT NULL,                 -- business key (stable across versions)

    -- Identifiers (multi-system)
    symbol       TEXT     NOT NULL,   -- ticker: AAPL, 7203.T, AIR.PA
    isin         CHAR(12),           -- ISO 6166
    cusip        CHAR(9),            -- US/CA only
    sedol        CHAR(7),            -- UK/IE only
    figi         CHAR(12),           -- OpenFIGI

    -- Hedge-Fund Metadata
    name             TEXT     NOT NULL,
    instrument_type  VARCHAR(50) NOT NULL, -- 'EQUITY', 'FUTURE', 'FXFORWARD', 'IRS', 'OPTIONVOL', 'CRYPTO'
    sector           TEXT,                 -- GICS sector
    industry         TEXT,                 -- GICS industry
    exchange_id      SMALLINT REFERENCES exchanges(exchange_id),
    currency         CHAR(3)  NOT NULL,
    tick_size        NUMERIC(15, 6),
    is_tradable      BOOLEAN  DEFAULT TRUE,

    -- SCD Type 2 versioning (No overlap constraint!)
    valid_from   TIMESTAMPTZ NOT NULL DEFAULT now(),
    valid_to     TIMESTAMPTZ,         -- NULL = currently valid
    
    EXCLUDE USING gist (
        instrument_key WITH =, 
        tstzrange(valid_from, COALESCE(valid_to, 'infinity')) WITH &&
    ),

    PRIMARY KEY (instrument_id)
);

-- Partial index: fast "current version" lookups
CREATE INDEX idx_instruments_current
    ON instruments (symbol)
    WHERE valid_to IS NULL;

CREATE TABLE accounts (
    id                 BIGINT PRIMARY KEY,
    name               VARCHAR NOT NULL UNIQUE,
    mifid_lei          VARCHAR NOT NULL,
    operator_id        VARCHAR,
    is_locates_enabled BOOLEAN NOT NULL DEFAULT FALSE,
    skip_start_of_day_position BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE strategies (
    id              BIGINT PRIMARY KEY,
    name            VARCHAR NOT NULL UNIQUE,
    account_id      BIGINT  NOT NULL REFERENCES accounts(id),
    trading_enabled BOOLEAN NOT NULL
);

CREATE TABLE ExecutionReport (
    id                  BIGINT PRIMARY KEY,
    InternalOrdID       VARCHAR(40),
    ExecID              VARCHAR(30),
    OrdStatus           VARCHAR(15),
    Side                VARCHAR(15),
    
    SecurityID          VARCHAR(40),   -- Maps to instruments.symbol or isin
    SecurityIDSource    VARCHAR(20),   -- e.g. 'EXCHANGE', 'BLOOMBERG'
    
    LeavesQty           FLOAT8,
    CumQty              FLOAT8,
    AvgPx               FLOAT8,
    Account             VARCHAR(12),
    Strategy            VARCHAR(23),
    
    TradeDate           VARCHAR(20),
    InternalAppTimestamp TIMESTAMP
);

CREATE TABLE bars_daily (
    instrument_key  UUID           NOT NULL,
    trade_date      DATE           NOT NULL,
    open            NUMERIC(14,4)  NOT NULL,
    high            NUMERIC(14,4)  NOT NULL,
    low             NUMERIC(14,4)  NOT NULL,
    close           NUMERIC(14,4)  NOT NULL,
    volume          BIGINT         NOT NULL,
    adj_close       NUMERIC(14,4),            -- split/dividend adjusted
    vwap            NUMERIC(14,4),            -- volume-weighted avg price
    trade_count     INTEGER,                  -- number of trades (if available)
    source          TEXT           NOT NULL DEFAULT 'yahoo',
    loaded_at       TIMESTAMPTZ    NOT NULL DEFAULT now(),

    PRIMARY KEY (instrument_key, trade_date)
) PARTITION BY RANGE (trade_date);

-- Partitions are created automatically by a Nomad periodic job
-- Example: one partition per month
CREATE TABLE bars_daily_2026_01 PARTITION OF bars_daily
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE bars_daily_2026_02 PARTITION OF bars_daily
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE fundamentals (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    instrument_key  UUID         NOT NULL,
    period          TEXT         NOT NULL,   -- '2025-Q3', '2025-FY'
    period_end      DATE         NOT NULL,   -- fiscal period end date
    metric          TEXT         NOT NULL,   -- 'eps', 'revenue', 'net_income', 'fcf', ...
    value           NUMERIC      NOT NULL,
    currency        CHAR(3)      NOT NULL,

    -- Point-in-time tracking
    reported_at     TIMESTAMPTZ  NOT NULL,   -- when this value became known
    superseded_at   TIMESTAMPTZ,             -- when a restatement replaced it
    source          TEXT         NOT NULL,
    loaded_at       TIMESTAMPTZ  NOT NULL DEFAULT now(),

    UNIQUE (instrument_key, period, metric, reported_at)
);

-- Point-in-time index
CREATE INDEX idx_fundamentals_pit
    ON fundamentals (instrument_key, metric, reported_at, superseded_at);

CREATE TABLE corporate_actions (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    instrument_key  UUID   NOT NULL,
    action_type     TEXT   NOT NULL,  -- 'split', 'dividend', 'merger', 'spinoff', 'delist', 'name_change'
    effective_date  DATE   NOT NULL,
    announced_date  DATE,             -- when the market learned about it
    ex_date         DATE,             -- ex-dividend/ex-split date
    record_date     DATE,

    -- Action-specific data (JSONB for flexibility)
    details  JSONB  NOT NULL DEFAULT '{}',
    -- split:    {"ratio": "4:1", "factor": 4.0}
    -- dividend: {"amount": 0.82, "currency": "USD", "type": "regular"}
    -- merger:   {"acquirer_key": "uuid", "terms": "0.7 shares per share"}
    -- delist:   {"reason": "bankruptcy", "last_trade_date": "2008-09-15"}

    source     TEXT         NOT NULL,
    loaded_at  TIMESTAMPTZ  NOT NULL DEFAULT now()
);

CREATE INDEX idx_corpactions_instrument
    ON corporate_actions (instrument_key, effective_date);

CREATE TABLE index_constituents (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    index_symbol    TEXT         NOT NULL,   -- 'SPX', 'NDX', 'STOXX600', 'TOPIX'
    instrument_key  UUID         NOT NULL,
    added_date      DATE         NOT NULL,
    removed_date    DATE,                    -- NULL = still a constituent
    weight          NUMERIC(8,6),            -- index weight at addition
    source          TEXT         NOT NULL,
    loaded_at       TIMESTAMPTZ  NOT NULL DEFAULT now(),

    UNIQUE (index_symbol, instrument_key, added_date)
);

-- "What was in the S&P 500 on 2008-09-12?"
-- (one trading day before Lehman Brothers collapsed)
SELECT i.symbol, i.name, ic.weight
FROM   index_constituents ic
JOIN   instruments i ON i.instrument_key = ic.instrument_key
  AND  i.valid_from <= '2008-09-12'
  AND  (i.valid_to > '2008-09-12' OR i.valid_to IS NULL)
WHERE  ic.index_symbol = 'SPX'
  AND  ic.added_date <= '2008-09-12'
  AND  (ic.removed_date > '2008-09-12' OR ic.removed_date IS NULL)
ORDER BY ic.weight DESC;

-- This query returns Lehman Brothers (LEH) as a constituent
-- because it wasn't removed until after bankruptcy

-- Template: retrieve the state of any temporal table at @as_of_date
-- Works for: instruments, fundamentals, index_constituents

SELECT *
FROM   <table>
WHERE  <entity_key> = @entity
  AND  <valid_from_col> <= @as_of_date
  AND  (<valid_to_col> > @as_of_date OR <valid_to_col> IS NULL);

-- For bars_daily, it's simpler (no SCD, just filter by date):
SELECT * FROM bars_daily
WHERE  instrument_key = @key AND trade_date <= @as_of_date
ORDER BY trade_date DESC LIMIT 1;

-- For fundamentals (point-in-time):
SELECT * FROM fundamentals
WHERE  instrument_key = @key
  AND  metric = @metric
  AND  reported_at <= @as_of_date
  AND  (superseded_at > @as_of_date OR superseded_at IS NULL);

-- The backbone of any star schema: a calendar dimension
-- Pre-computed columns eliminate date math in every query

CREATE TABLE mart.dim_date (
    date_key          DATE PRIMARY KEY,
    year              INT,
    quarter           INT,
    month             INT,
    week_of_year      INT,
    day_of_week       INT,       -- 1=Monday, 7=Sunday
    day_name          TEXT,      -- 'Monday', 'Tuesday', ...
    is_trading_day    BOOLEAN,   -- FALSE for weekends + market holidays
    is_month_end      BOOLEAN,
    is_quarter_end    BOOLEAN,
    is_opex           BOOLEAN,   -- 3rd Friday (options expiration)
    is_fomc           BOOLEAN,   -- Fed announcement days
    trading_days_ytd  INT        -- cumulative trading days in year
);

-- Populate: generate_series + join to market holidays table
-- Covers 2000-01-01 to 2030-12-31 = ~11,000 rows

-- Flattened view of current instrument state
-- Joins instruments + exchanges in one row per active security

CREATE TABLE mart.dim_instrument AS
SELECT
    i.instrument_key,
    i.symbol,
    i.name,
    i.asset_type,
    i.sector,
    i.industry,
    i.country,
    i.currency,
    i.is_active,
    i.listed_date,
    i.delisted_date,
    e.exchange_code,
    e.exchange_name,
    e.timezone  AS exchange_timezone,
    e.region    AS exchange_region
FROM instruments i
JOIN exchanges e ON e.exchange_id = i.exchange_id
WHERE i.valid_to IS NULL;  -- current SCD2 row only

-- ~10,000 rows (one per instrument)
-- Refreshed daily by dbt: dbt run --select mart.dim_instrument

CREATE TABLE mart.dim_sector AS
SELECT DISTINCT
    sector,
    industry,
    COUNT(*) OVER (PARTITION BY sector)   AS instruments_in_sector,
    COUNT(*) OVER (PARTITION BY industry) AS instruments_in_industry
FROM mart.dim_instrument
WHERE is_active = TRUE;

-- One row per instrument per trading day
-- Pre-computes daily returns, cumulative returns, drawdown
-- This is the table 90% of BI queries will hit

CREATE TABLE mart.fact_daily_returns (
    date_key         DATE           NOT NULL REFERENCES mart.dim_date(date_key),
    instrument_key   UUID           NOT NULL,
    open             NUMERIC(14,4),
    high             NUMERIC(14,4),
    low              NUMERIC(14,4),
    close            NUMERIC(14,4),
    volume           BIGINT,
    adj_close        NUMERIC(14,4),

    -- Pre-computed analytics
    daily_return     NUMERIC(10,6),   -- (close - prev_close) / prev_close
    log_return       NUMERIC(10,6),   -- ln(close / prev_close)
    cum_return_ytd   NUMERIC(10,6),   -- cumulative return since Jan 1
    drawdown         NUMERIC(10,6),   -- current drawdown from peak
    rolling_vol_20d  NUMERIC(10,6),   -- 20-day rolling annualized vol
    sma_50           NUMERIC(14,4),
    sma_200          NUMERIC(14,4),
    rsi_14           NUMERIC(6,2),

    PRIMARY KEY (date_key, instrument_key)
) PARTITION BY RANGE (date_key);

-- Grain: 1 row per instrument per trading day
-- Estimated: ~60M rows (same as bars_daily)
-- Partitioned monthly, same scheme as bars_daily

-- One row per position per day
-- Built from trades table (see Trading Journal section)
-- Enables P&L attribution, sector exposure, risk decomposition

CREATE TABLE mart.fact_portfolio_snapshot (
    date_key        DATE           NOT NULL REFERENCES mart.dim_date(date_key),
    instrument_key  UUID           NOT NULL,
    portfolio_id    UUID           NOT NULL,
    quantity        NUMERIC(16,6),
    avg_cost        NUMERIC(14,4),
    market_price    NUMERIC(14,4),   -- close from bars_daily
    market_value    NUMERIC(16,4),   -- quantity * market_price
    unrealized_pnl  NUMERIC(16,4),   -- market_value - (quantity * avg_cost)
    weight_pct      NUMERIC(8,4),    -- position weight in portfolio
    daily_pnl       NUMERIC(16,4),   -- change from previous day

    PRIMARY KEY (date_key, portfolio_id, instrument_key)
) PARTITION BY RANGE (date_key);

-- Built nightly by dbt from trades + bars_daily
-- Enables: "What was my sector exposure on March 15?"

SELECT
    d.year,
    d.month,
    di.sector,
    AVG(f.daily_return) * 21  AS monthly_return,
    AVG(f.rolling_vol_20d)    AS avg_volatility
FROM mart.fact_daily_returns f
JOIN mart.dim_date d       ON d.date_key = f.date_key
JOIN mart.dim_instrument di ON di.instrument_key = f.instrument_key
WHERE d.year = 2025
  AND di.is_active = TRUE
  AND di.asset_type = 'equity'
GROUP BY d.year, d.month, di.sector
ORDER BY d.year, d.month, monthly_return DESC;

SELECT
    di.exchange_region,
    di.symbol,
    di.name,
    MIN(f.drawdown)         AS max_drawdown,
    MAX(f.rolling_vol_20d)  AS peak_volatility
FROM mart.fact_daily_returns f
JOIN mart.dim_date d        ON d.date_key = f.date_key
JOIN mart.dim_instrument di ON di.instrument_key = f.instrument_key
WHERE d.year = 2025
GROUP BY di.exchange_region, di.symbol, di.name
HAVING MIN(f.drawdown) < -0.20  -- only stocks with >20% drawdown
ORDER BY max_drawdown ASC LIMIT 20;

SELECT
    di.sector,
    SUM(ps.market_value)    AS sector_value,
    SUM(ps.weight_pct)      AS sector_weight,
    SUM(ps.unrealized_pnl)  AS sector_pnl
FROM mart.fact_portfolio_snapshot ps
JOIN mart.dim_instrument di ON di.instrument_key = ps.instrument_key
WHERE ps.date_key = '2025-06-15'
  AND ps.portfolio_id = '...'
GROUP BY di.sector
ORDER BY sector_weight DESC;

-- A trader may have multiple portfolios (swing, long-term, options)

CREATE TABLE journal.portfolios (
    portfolio_id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name             TEXT NOT NULL,
    description      TEXT,
    strategy         TEXT,             -- 'momentum', 'value', 'mean-reversion'
    base_currency    TEXT DEFAULT 'USD',
    initial_capital  NUMERIC(16,4),
    created_at       TIMESTAMPTZ DEFAULT now()
);

-- Immutable log of every trade execution
-- One row per fill (a single order may have multiple fills)

CREATE TABLE journal.trades (
    trade_id        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    portfolio_id    UUID           NOT NULL REFERENCES journal.portfolios(portfolio_id),
    instrument_key  UUID           NOT NULL,   -- FK to instruments.instrument_key
    trade_date      DATE           NOT NULL,
    trade_time      TIMESTAMPTZ,
    side            TEXT           NOT NULL,    -- 'buy', 'sell', 'short', 'cover'
    quantity        NUMERIC(16,6)  NOT NULL,
    price           NUMERIC(14,4)  NOT NULL,
    commission      NUMERIC(10,4)  DEFAULT 0,
    slippage        NUMERIC(10,4)  DEFAULT 0,

    -- Trade context (captured at entry time)
    setup_type    TEXT,             -- 'breakout', 'pullback', 'reversal', 'earnings'
    entry_reason  TEXT,             -- free-text thesis
    stop_loss     NUMERIC(14,4),   -- planned stop
    target_1      NUMERIC(14,4),   -- first target
    target_2      NUMERIC(14,4),   -- second target
    risk_reward   NUMERIC(6,2),    -- planned R:R at entry

    -- Metadata
    broker      TEXT,
    order_type  TEXT,               -- 'market', 'limit', 'stop_limit'
    tags        TEXT[],             -- ['momentum', 'FOMC', 'earnings-play']
    created_at  TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_trades_portfolio_date ON journal.trades(portfolio_id, trade_date);
CREATE INDEX idx_trades_instrument     ON journal.trades(instrument_key, trade_date);

-- Separate from trades for a reason: notes are written after the fact
-- A trade may have multiple notes (entry review, exit review, weekly review)

CREATE TABLE journal.trade_notes (
    note_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    trade_id        UUID NOT NULL REFERENCES journal.trades(trade_id),
    note_type       TEXT NOT NULL,    -- 'entry', 'exit', 'review', 'lesson'
    content         TEXT NOT NULL,
    emotion         TEXT,             -- 'confident', 'fearful', 'greedy', 'neutral'
    rating          INT,              -- self-score 1-5 for trade execution quality
    screenshot_url  TEXT,             -- link to chart screenshot at entry/exit
    created_at      TIMESTAMPTZ DEFAULT now()
);

CREATE VIEW journal.v_current_positions AS
WITH signed_trades AS (
    SELECT
        portfolio_id,
        instrument_key,
        trade_date,
        CASE
            WHEN side IN ('buy', 'cover') THEN  quantity
            WHEN side IN ('sell', 'short') THEN -quantity
        END AS signed_qty,
        price,
        commission
    FROM journal.trades
),
positions AS (
    SELECT
        portfolio_id,
        instrument_key,
        SUM(signed_qty) AS net_quantity,
        -- Weighted average cost (buys only)
        SUM(CASE WHEN signed_qty > 0
            THEN signed_qty * price ELSE 0 END)
        / NULLIF(SUM(CASE WHEN signed_qty > 0
            THEN signed_qty ELSE 0 END), 0) AS avg_cost,
        SUM(commission)  AS total_commissions,
        MIN(trade_date)  AS first_trade,
        MAX(trade_date)  AS last_trade
    FROM signed_trades
    GROUP BY portfolio_id, instrument_key
)
SELECT
    p.*,
    i.symbol,
    i.name,
    i.sector
FROM positions p
JOIN instruments i ON i.instrument_key = p.instrument_key
  AND i.valid_to IS NULL
WHERE p.net_quantity <> 0;  -- exclude closed positions

-- "What was my P&L on 2025-06-15?"
-- Combines realized (closed trades) + unrealized (open positions x market price)

WITH trades_asof AS (
    SELECT
        portfolio_id,
        instrument_key,
        SUM(CASE WHEN side IN ('buy','cover')
            THEN quantity ELSE -quantity END) AS net_qty,
        SUM(CASE WHEN side IN ('sell','short')
            THEN quantity * price ELSE 0 END)
        - SUM(CASE WHEN side IN ('buy','cover')
            THEN quantity * price ELSE 0 END) AS realized_pnl,
        SUM(commission) AS total_fees
    FROM journal.trades
    WHERE trade_date <= '2025-06-15'
    GROUP BY portfolio_id, instrument_key
),
market_prices AS (
    SELECT DISTINCT ON (instrument_key)
        instrument_key, close AS market_price
    FROM bars_daily
    WHERE trade_date <= '2025-06-15'
    ORDER BY instrument_key, trade_date DESC
)
SELECT
    t.portfolio_id,
    i.symbol,
    t.net_qty,
    t.realized_pnl,
    t.net_qty * m.market_price AS market_value,
    t.net_qty * m.market_price - t.net_qty *
        ABS(t.realized_pnl / NULLIF(t.net_qty, 0)) AS unrealized_pnl,
    t.total_fees,
    t.realized_pnl - t.total_fees AS net_realized
FROM trades_asof t
JOIN instruments i ON i.instrument_key = t.instrument_key
  AND i.valid_to IS NULL
LEFT JOIN market_prices m ON m.instrument_key = t.instrument_key
ORDER BY ABS(t.net_qty * m.market_price) DESC;

-- "What's my win rate on breakout trades when RSI was below 40?"

WITH closed_trades AS (
    SELECT
        t.trade_id, t.instrument_key, t.trade_date,
        t.setup_type, t.side, t.price, t.quantity,
        LEAD(t.price) OVER (
            PARTITION BY t.instrument_key
            ORDER BY t.trade_date
        ) AS exit_price
    FROM journal.trades t
    WHERE t.side = 'buy'
),
with_context AS (
    SELECT
        ct.*,
        f.rsi_14 AS entry_rsi,
        (ct.exit_price - ct.price) / ct.price AS return_pct
    FROM closed_trades ct
    JOIN mart.fact_daily_returns f
      ON f.instrument_key = ct.instrument_key
     AND f.date_key = ct.trade_date
    WHERE ct.exit_price IS NOT NULL
)
SELECT
    setup_type,
    COUNT(*)                                              AS total_trades,
    ROUND(100.0 * COUNT(*) FILTER (
        WHERE return_pct > 0) / COUNT(*), 1)           AS win_rate_pct,
    ROUND(AVG(return_pct) * 100, 2)                       AS avg_return_pct,
    ROUND(AVG(entry_rsi), 1)                              AS avg_entry_rsi
FROM with_context
WHERE entry_rsi < 40
GROUP BY setup_type
ORDER BY win_rate_pct DESC;

-- "Do I make worse trades when I'm feeling greedy?"

SELECT
    tn.emotion,
    COUNT(*)                    AS trades,
    ROUND(AVG(tn.rating), 1)   AS avg_self_rating,
    ROUND(100.0 * COUNT(*) FILTER (WHERE
        EXISTS (SELECT 1 FROM journal.trades t2
                WHERE t2.instrument_key = t.instrument_key
                  AND t2.side = 'sell'
                  AND t2.price > t.price
                  AND t2.trade_date > t.trade_date)
    ) / COUNT(*), 1)           AS win_rate_pct
FROM journal.trade_notes tn
JOIN journal.trades t ON t.trade_id = tn.trade_id
WHERE tn.note_type = 'entry'
  AND tn.emotion IS NOT NULL
GROUP BY tn.emotion
ORDER BY trades DESC;

from boring_semantic_layer import SemanticModel, Dimension, Measure
import ibis

con = ibis.postgres.connect(
    host="localhost",
    database="secmaster",
    user="secmaster",
    schema="mart",
)

model = SemanticModel(connection=con)

# Define semantic tables with dimensions and measures
model.add_table(
    "daily_returns",
    table=con.table("fact_daily_returns"),
    dimensions=[
        Dimension("sector", description="GICS sector classification"),
        Dimension("region", description="Exchange region (US, EU, APAC)"),
        Dimension("symbol", description="Ticker symbol"),
    ],
    measures=[
        Measure("avg_return", expression="daily_return.mean()", description="Average daily return"),
        Measure("total_vol", expression="rolling_vol_20d.mean()", description="Average 20d rolling volatility"),
        Measure("max_drawdown", expression="drawdown.min()", description="Maximum drawdown"),
        Measure("count", expression="instrument_key.nunique()", description="Number of instruments"),
    ],
)

from boring_semantic_layer.mcp import MCPSemanticModel

mcp = MCPSemanticModel(
    model=model,
    name="secmaster",
    description="Securities Master -- multi-asset, multi-region financial database",
)

if __name__ == "__main__":
    mcp.run(transport="stdio")

{
    "mcpServers": {
        "secmaster": {
            "command": "python",
            "args": ["mcp_server.py"],
            "env": {
                "DATABASE_URL": "postgresql://secmaster:***@localhost:5432/secmaster"
            }
        }
    }
}

import openai
from mcp_client import MCPClient

# OpenRouter: one API key, all models
llm = openai.OpenAI(
    base_url="https://openrouter.ai/api/v1",
    api_key="sk-or-...",
)

# Connect to our MCP server
mcp = MCPClient(command="python mcp_server.py")
tools = mcp.list_tools()  # auto-discovered from semantic layer

# Multi-model routing
MODELS = {
    "analysis":  "anthropic/claude-sonnet-4",   # deep reasoning
    "quick":     "google/gemini-2.0-flash",     # fast lookups
    "sentiment": "x-ai/grok-3",                # real-time Twitter/X
}

def query(question: str, mode: str = "analysis") -> str:
    """Ask the securities master a question in natural language."""
    response = llm.chat.completions.create(
        model=MODELS[mode],
        messages=[
            {"role": "system", "content": "You are a financial analyst. "
             "Use the secmaster tools to answer questions with data."},
            {"role": "user", "content": question},
        ],
        tools=tools,
    )
    # Handle tool calls, execute via MCP, return formatted answer
    return handle_tool_loop(response, mcp, llm)

# Example usage:
# query("What's my tech sector exposure today?")
# query("Quick: AAPL's RSI?", mode="quick")
# query("What's the Twitter sentiment on NVDA?", mode="sentiment")

job "postgres" {
    type        = "service"
    datacenters = ["dc1"]

    group "db" {
        network {
            port "pg" { static = 5432 }
        }

        volume "pgdata" {
            type   = "host"
            source = "pgdata"
        }

        task "postgres" {
            driver = "docker"

            config {
                image = "postgres:16-alpine"
                ports = ["pg"]
            }

            volume_mount {
                volume      = "pgdata"
                destination = "/var/lib/postgresql/data"
            }

            env {
                POSTGRES_DB       = "secmaster"
                POSTGRES_USER     = "secmaster"
                POSTGRES_PASSWORD = "${NOMAD_META_pg_password}"
            }

            resources {
                cpu    = 2000  # 2 cores
                memory = 4096  # 4 GB -- shared_buffers should be 1GB
            }
        }
    }
}

package main

import (
    "database/sql"
    "fmt"
    "log"
    "os"
    "time"
    _ "github.com/lib/pq"
)

func ensurePartitions(db *sql.DB, table string, months int) error {
    now := time.Now()
    for i := 0; i < months; i++ {
        start := time.Date(now.Year(), now.Month()+time.Month(i), 1, 0, 0, 0, 0, time.UTC)
        end := start.AddDate(0, 1, 0)
        name := fmt.Sprintf("%s_%s", table, start.Format("2006_01"))

        query := fmt.Sprintf(`
            CREATE TABLE IF NOT EXISTS %s
            PARTITION OF %s
            FOR VALUES FROM ('%s') TO ('%s')
        `, name, table, start.Format("2006-01-02"), end.Format("2006-01-02"))

        if _, err := db.Exec(query); err != nil {
            return fmt.Errorf("partition %s: %w", name, err)
        }
        log.Printf("ensured partition %s", name)
    }
    return nil
}

func main() {
    db, _ := sql.Open("postgres", os.Getenv("DATABASE_URL"))
    defer db.Close()

    tables := []string{"bars_daily", "bars_intraday"}
    for _, t := range tables {
        if err := ensurePartitions(db, t, 3); err != nil {
            log.Fatal(err)
        }
    }
}

job "partition-creator" {
    type        = "batch"
    datacenters = ["dc1"]

    periodic {
        cron             = "0 0 1 * *"  # 1st of every month
        prohibit_overlap = true
    }

    group "partitions" {
        task "create" {
            driver = "exec"
            config {
                command = "/usr/local/bin/partition-creator"
            }
            env {
                DATABASE_URL = "postgres://secmaster:${NOMAD_META_pg_password}@localhost:5432/secmaster"
            }
        }
    }
}

version: 1
project_id: secmaster

plugins:
  extractors:
    - name: tap-yahoo-finance
      variant: custom
      pip_url: git+https://github.com/your-org/tap-yahoo-finance.git
      config:
        symbols:
          - AAPL
          - MSFT
          - GOOGL
          - AMZN
          - TSLA
        start_date: "2000-01-01"
        streams:
          - bars_daily
          - instrument_metadata
          - dividends
          - splits

  loaders:
    - name: target-postgres
      variant: transferwise
      config:
        host: localhost
        port: 5432
        user: secmaster
        dbname: secmaster
        default_target_schema: raw_yahoo

-- models/staging/stg_bars_daily.sql
-- Incremental model: only process new rows since last run

{{ config(
    materialized='incremental',
    unique_key=['instrument_key', 'trade_date'],
    partition_by={'field': 'trade_date', 'data_type': 'date', 'granularity': 'month'}
) }}

SELECT
    i.instrument_key,
    r.date::date             AS trade_date,
    r.open::numeric(14,4)    AS open,
    r.high::numeric(14,4)    AS high,
    r.low::numeric(14,4)     AS low,
    r.close::numeric(14,4)   AS close,
    r.volume::bigint         AS volume,
    r.adj_close::numeric(14,4) AS adj_close,
    'yahoo'                  AS source,
    now()                    AS loaded_at
FROM {{ source('raw_yahoo', 'bars_daily') }} r
JOIN {{ ref('stg_instruments') }} i
  ON i.symbol = r.symbol
 AND i.valid_to IS NULL
WHERE r.open > 0
  AND r.high >= r.low
  AND r.volume >= 0
{% if is_incremental() %}
  AND r._sdc_extracted_at > (
      SELECT MAX(loaded_at) FROM {{ this }}
  )
{% endif %}

version: 2

models:
  - name: stg_bars_daily
    description: "Cleaned daily OHLCV bars"
    columns:
      - name: instrument_key
        tests: [not_null]
      - name: trade_date
        tests: [not_null]
      - name: open
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "> 0"
      - name: high
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: ">= low"
      - name: close
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: ">= low AND close <= high"
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - instrument_key
            - trade_date