Designing the data model for a trading journal

Start with the trade, but don't overload it

The instinct is to make one giant trades table with fifty columns. Resist it. A trade has a small core of facts:

CREATE TABLE trades (
    id BIGSERIAL PRIMARY KEY,
    symbol TEXT NOT NULL,
    side TEXT NOT NULL CHECK (side IN ('long', 'short')),
    entry_price NUMERIC NOT NULL,
    exit_price NUMERIC,
    quantity NUMERIC NOT NULL,
    entry_at TIMESTAMPTZ NOT NULL,
    exit_at TIMESTAMPTZ,
    stop_loss NUMERIC,
    take_profit NUMERIC,
    commission NUMERIC DEFAULT 0,
    strategy_id BIGINT REFERENCES strategies(id)
);

Everything else (emotions, notes, screenshots, rule checks) hangs off this in separate tables. That separation is what lets you slice the data later without schema migrations every time you want a new report.

R multiples belong in the model, not the UI

The single most useful number in trading isn't dollar P&L. It's the R multiple: profit or loss expressed in units of the risk you planned to take. A +2R win and a +2R win are comparable across a $500 account and a $50,000 account; two dollar figures aren't.

Planned risk is defined the moment you enter, by your stop:

planned_risk_per_unit = |entry_price - stop_loss|
planned_R = (exit_price - entry_price) / planned_risk_per_unit (for longs)

Store the stop at entry time. If you only record the realized exit, you lose the ability to detect stop loss violations, which is the whole point. When realized risk exceeds planned risk, someone moved a stop. That's a rule break you want the data to surface, not something you have to remember.

I compute R multiples as a view rather than a stored column, so a corrected entry price recalculates everything downstream:

CREATE VIEW trade_metrics AS
SELECT
    t.id,
    (t.exit_price - t.entry_price) * t.quantity - t.commission AS net_pnl,
    ABS(t.entry_price - t.stop_loss) AS planned_risk_unit,
    CASE
        WHEN t.side = 'long'
            THEN (t.exit_price - t.entry_price) / NULLIF(ABS(t.entry_price - t.stop_loss), 0)
        ELSE (t.entry_price - t.exit_price) / NULLIF(ABS(t.entry_price - t.stop_loss), 0)
    END AS realized_R
FROM trades t
WHERE t.exit_price IS NOT NULL;

Emotions and rules: many to many, always

Here's the modeling decision that unlocks the reports people actually want. Emotions and rule checks are not columns on the trade. A single trade can carry several emotions (FOMO and impatience), and a strategy has several rules, each independently followed or broken. That's two many-to-many relationships:

CREATE TABLE emotions (
    id BIGSERIAL PRIMARY KEY,
    name TEXT UNIQUE
);  -- fomo, tilt, revenge...

CREATE TABLE trade_emotions (
    trade_id BIGINT REFERENCES trades(id),
    emotion_id BIGINT REFERENCES emotions(id),
    PRIMARY KEY (trade_id, emotion_id)
);

CREATE TABLE rule_checks (
    trade_id BIGINT REFERENCES trades(id),
    rule_id BIGINT REFERENCES rules(id),
    followed BOOLEAN NOT NULL,
    PRIMARY KEY (trade_id, rule_id)
);

Once this exists, "what does revenge trading cost me?" is a single join:

SELECT
    e.name,
    COUNT(*) AS trades,
    SUM(m.net_pnl) AS total_pnl
FROM trade_emotions te
JOIN emotions e ON e.id = te.emotion_id
JOIN trade_metrics m ON m.id = te.trade_id
GROUP BY e.name
ORDER BY total_pnl ASC;

And rule compliance per strategy, win rate when followed vs. broken, is a GROUP BY followed. No pivot tables, no manual tagging math. The model does it.

Exit efficiency needs excursion data

To answer "am I cutting winners early?" you need more than entry and exit. You need the best and worst prices the trade reached while open: MFE (maximum favorable excursion) and MAE (maximum adverse excursion). Capture those from your price feed at close:

ALTER TABLE trades ADD COLUMN mfe_price NUMERIC;
ALTER TABLE trades ADD COLUMN mae_price NUMERIC;

Exit efficiency is then actual profit captured as a fraction of the maximum that was available:

exit_efficiency = realized_move / (mfe_price - entry_price)

Consistently low efficiency on winners is the quantitative fingerprint of exiting too early, a habit that's invisible in raw P&L but obvious once the excursion data is in the model.

The payoff

None of these reports required special report writing logic. They're all straightforward joins and aggregates, because the relationships (one trade to many emotions, one strategy to many rule checks, one trade to its excursions) were modeled explicitly instead of flattened into columns. Get the schema right and the analytics are almost an afterthought. Even if you build your own, start from the relationships, not the columns.