For a while now, I've been working on a paper trading app for NEPSE. Virtual money, real prices, and the freedom to make awful trades without it costing a single rupee.
Like most side projects, it kept getting sidelined whenever life got busy. And every time I came back, I had to relearn my own code: what was finished, what was half-broken, and worse, why I'd made some decision in the first place. Past me left very few notes. So this series is partly a favour to future me, a written record of what I built and why, that I can read and argue with. If you want to argue with it too, or suggest something better, I'd love that.
The MVP is small: live quotes, buy and sell orders that actually execute, and a page showing what you hold. It's slowly coming together. Maybe one day it grows into something closer to a real TMS, but that's far off.
On the tech side, the backend is NestJS with Prisma on top of PostgreSQL, Redis and BullMQ for the background work, and SSE (Server-Sent Events) for the live prices. The frontend is Next.js with React, Tailwind and shadcn/ui, TanStack Query and Zustand for state, and TradingView's lightweight-charts for the price charts. I'll get into the reasons as the series goes on, and I'm sure some of them will turn out to be wrong.
Since I'm doing this to learn, I'm writing most of the backend by hand. My code isn't better or more optimal than what an AI would produce (it's actually far from it). It's just that the best way I know to learn something is to build it badly myself and then fix it. I do use AI for the frontend, for planning, and for getting unstuck.
I'm also new to writing. I used AI to proofread this post and tidy my rough drafts, and I plan to lean on it less with each post until it's mostly just me. This first post is about the database schema, and later ones will cover the other parts.
DB Schema
erDiagram USER ||--o{ PORTFOLIO : "owns" PORTFOLIO ||--o{ HOLDINGS : "maintains (snapshot)" PORTFOLIO ||--o{ ORDER : "places" INSTRUMENT ||--o{ HOLDINGS : "held_as" INSTRUMENT ||--o{ ORDER : "targeted_by" INSTRUMENT ||--o{ TRANSACTION : "traded_in" ORDER ||--o{ TRANSACTION : "executes_into (fills)" HOLDINGS }o--|| TRANSACTION : "last_updated_by (audit)" USER { string id PK "UUID" string email UK "Unique email" string displayName "User full name" datetime createdAt "Timestamp" } PORTFOLIO { string id PK "UUID" string userId FK "References User.id" string name "Strategy / portfolio name" decimal startingBalance "Initial virtual capital (e.g. Rs 100,000)" decimal cashBalance "Available cash to trade" decimal lockedBalance "Cash locked in pending BUY orders" datetime createdAt "Creation time" datetime updatedAt "Last modification time" } INSTRUMENT { string id PK "UUID" string symbol UK "Ticker symbol (e.g. NABIL, HDL)" string organizationName "Company name" enum type "STOCK, DEBENTURE, MUTUAL_FUND, INDEX" } HOLDINGS { string portfolioId PK, FK "Composite PK" string instrumentId PK, FK "Composite PK" string symbol "Cached symbol for fast lookup" decimal quantity "Active shares currently held" decimal lockedQuantity "Shares locked in pending SELL orders" decimal avgCost "Weighted average cost (WACC)" decimal realized_pnl "Accumulated realized profit/loss" decimal currentValue "Latest evaluated mark-to-market value" string lastTransactionId FK "Audit pointer to latest mutating fill" } ORDER { string id PK "UUID" string portfolioId FK "References Portfolio.id" string instrumentId FK "References Instrument.id" string symbol "Cached symbol" enum orderType "MARKET, LIMIT, STOP" enum orderSide "BUY, SELL" enum orderStatus "PENDING, FILLED, PARTIAL_FILL, CANCELLED" int quantity "Total requested shares" int filledQuantity "Cumulative shares filled so far" decimal limitPrice "Target price for LIMIT orders" decimal stopPrice "Trigger price for STOP orders" decimal avgCost "Executed volume-weighted price" datetime createdAt "Submission timestamp" } TRANSACTION { string id PK "UUID" string orderId FK "References Order.id" string instrumentId FK "References Instrument.id" int quantity "Shares executed in this specific fill" decimal price "Actual execution price per share" decimal fees "Brokerage & SEBON commissions" decimal tax "Applicable capital gains / DP tax" datetime tradedAt "Execution timestamp" }Portfolios
My first instinct was simple: a user has orders, and a user has holdings. So the Order and Holdings tables would each point straight at a user with a foreign key.
Then I thought about testing different trading/investing strategies. If everything hangs off the user, all of it blends into one pile of money and one history, and I can't tell which strategy is working. So the Portfolio was born. A user can have multiple portfolios, and each portfolio has its own cashBalance. Orders and holdings point to a portfolio rather than a user. Now every strategy I want to test gets its own portfolio, and I can study them separately.
Orders and transactions
The Order table holds an order as it's placed. Transactions are created as the order gets filled. But why the separation? Let's look at an example.
Say you want 1,000 shares of NABIL at Rs 520, and it's currently trading at Rs 530. You place a limit order for 1,000 shares at Rs 520, and the price starts to drop. A seller shows up with 300 at Rs 518, so you get those. Some minutes later someone dumps 400 at Rs 520, and you get those too. Then the price runs off without you, you lose patience, and you cancel the remaining 300.
So the order was only partially filled and then cancelled, but real purchases happened along the way. That means one limit order for NABIL at Rs 520, with status CANCELLED, and two transactions attached to it. My first thought was to add a status like FILLED_BEFORE_CANCELLED for this case. But the order already carries filledQuantity, and 700 out of 1,000 says exactly what happened, so a new status would only repeat it.
An order is the intent ("up to 1,000 at Rs 520"), and a transaction is a fill: one thing that happened, never edited afterwards. An order can produce zero fills, or many.
Holdings
The Holdings table stores what I currently hold, one row per portfolio and instrument. I could compute this from Transactions every time, but that gets slower as the data grows. So I created a table for it that gets updated on every buy and sell.
On a buy, I increase the quantity and recompute the average cost:
On a sell, I reduce the quantity and book the realized profit or loss against that average. When quantity hits zero, the position is closed. Loading a portfolio is one indexed lookup.
What I traded away is the single source of truth. There are now two places that can disagree: the ledger and the snapshot. My current plan is to only ever update holdings in the same database transaction that writes the fill, so they can't get out of step. I think that works, but I'm not fully convinced, and I'll come back to it.
Spending the same money twice
Say you have Rs 50,000 and place a limit buy for Rs 40,000 worth of something. It sits there waiting for a seller. Before it fills, you place another order for Rs 30,000. If I only look at cashBalance, both go through, and you've committed Rs 70,000 you don't have.
I did check balances at fill time, but that's too late: the order has already been accepted by then. So I added a lockedBalance to the portfolio and a lockedQuantity to holdings. When buying, available balance is cashBalance - lockedBalance, and when selling, available shares are quantity - lockedQuantity. Placing an order moves money into locked, a fill settles it, and a cancel releases it.
Things I'm not sure about
I don't know how I'd notice if holdings and the ledger ever drifted apart. A periodic job that replays every fill and compares is one idea, but I haven't thought it through yet.
And bonus shares and rights issues. NEPSE has them constantly, and they mess with average cost in ways I haven't worked out. I've been avoiding them on purpose.
One small cleanup while I'm at it: currentValue on holdings doesn't make sense and nothing uses it, so it's going.
(P.S I've also put the app itself online. It's not finished and parts of it will feel empty. I wanted to wait until it was "done", but there's no real definition of done, and I know myself: if I wait, I'll probably drift away from it. So I'm releasing what exists and adding to it in the open. Link: App. Here, sign up has no verification. Also once you signup, you're taken to /dashboard. There is no way to navigate to chart page for now. For that, directly head over to /stock/)
