Schema refactor — introduce Tracker, rename purchases to entries #31

New issue

Closed

opened 2026-05-01 21:31:34 +02:00 by myrmidex · 0 comments

myrmidex commented

2026-05-01 21:31:34 +02:00

Owner

Refactor the database schema to introduce Tracker and remove financial-specific naming.

Target schema

trackers:     id, user_id, asset_id (nullable), label, unit, price_tracking_enabled
entries:      id, tracker_id, quantity, unit_price (nullable), total_cost (nullable), date
milestones:   id, tracker_id, target, description
asset_prices: id, asset_id, price, date   ← unchanged
assets:       id, symbol, full_name       ← unchanged

Migration steps

Create trackers table (user_id FK, asset_id nullable FK, label string, unit string, price_tracking_enabled bool default false)
Rename purchases → entries
Rename entries.shares → entries.quantity
Rename entries.price_per_share → entries.unit_price
Add entries.tracker_id FK, drop entries.asset_id (entries linked via tracker now)
Add milestones.tracker_id FK
Drop users.asset_id, users.price_tracking_enabled
Data migration: for each existing user, create one tracker with their current asset_id, price_tracking_enabled, label = 'Portfolio', unit = 'shares'; backfill tracker_id on all entries and milestones

Design decisions

One tracker per user for now (User hasOne Tracker) — multi-tracker is a future milestone
No position column needed yet
asset_prices unchanged — shared market price per asset symbol
entries.unit_price = what user paid (broker price), distinct from asset_prices.price (market price)

Refactor the database schema to introduce Tracker and remove financial-specific naming. ## Target schema ``` trackers: id, user_id, asset_id (nullable), label, unit, price_tracking_enabled entries: id, tracker_id, quantity, unit_price (nullable), total_cost (nullable), date milestones: id, tracker_id, target, description asset_prices: id, asset_id, price, date ← unchanged assets: id, symbol, full_name ← unchanged ``` ## Migration steps 1. Create `trackers` table (user_id FK, asset_id nullable FK, label string, unit string, price_tracking_enabled bool default false) 2. Rename `purchases` → `entries` 3. Rename `entries.shares` → `entries.quantity` 4. Rename `entries.price_per_share` → `entries.unit_price` 5. Add `entries.tracker_id` FK, drop `entries.asset_id` (entries linked via tracker now) 6. Add `milestones.tracker_id` FK 7. Drop `users.asset_id`, `users.price_tracking_enabled` 8. Data migration: for each existing user, create one tracker with their current `asset_id`, `price_tracking_enabled`, `label = 'Portfolio'`, `unit = 'shares'`; backfill `tracker_id` on all entries and milestones ## Design decisions - One tracker per user for now (User hasOne Tracker) — multi-tracker is a future milestone - No `position` column needed yet - `asset_prices` unchanged — shared market price per asset symbol - `entries.unit_price` = what user paid (broker price), distinct from `asset_prices.price` (market price)