Designing the data model for a trading journal

RizeTrade — Sat, 11 Jul 2026 12:24:41 +0000

Every trader I know starts journaling in a spreadsheet, and every one of them abandons it within a month.

Not because journaling is useless, but because the spreadsheet can't answer the questions that actually matter: "which of my rules costs me the most money?" or "am I cutting winners too early?"
A flat sheet of rows can't tell you.

Tools like RizeTrade solve this with a proper data model, and in this post I'll walk through how to design one yourself.

The problem is almost always the data model.
If you design the schema well up front, the hard reports fall out of it for free.

Here's how I'd structure it.

*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.

DEV Community: RizeTrade

Designing the data model for a trading journal