230 lines
22 KiB
Markdown
230 lines
22 KiB
Markdown
# Fund Tracker — Multi-Profile Mutual Fund & Stock Tracking Service
|
||
Device Type: **Docker Container (hosted on NAS16)**
|
||
Containers: **fundtracker_api**, **fundtracker_db**
|
||
IP Address: **192.168.150.40** (shares NAS16's host IP)
|
||
VLAN: **50 — Lab / Servers**
|
||
|
||
Last Updated: 2026-08-19
|
||
|
||
---
|
||
|
||
## 🧩 Role & Purpose
|
||
Fund Tracker is a self-hosted, multi-profile web service for tracking mutual funds and stocks, built to answer one specific question: *which securities are historically strong performers that are currently trading below where they normally sit* — a systematic "buy the proven dip" screen, rather than chasing whatever's up the most right now.
|
||
|
||
It is **not** a data source or execution platform — it pulls public price history (Yahoo Finance), computes a set of derived scores, and presents ranked, filterable, exportable views plus a daily email report. All investment decisions remain manual.
|
||
|
||
---
|
||
|
||
## 🌐 Public Access
|
||
- **Domain:** `https://funds.kingdezigns.com/`
|
||
- **Proxy:** NPM on HAS (192.168.150.30) → `192.168.150.40:8000`
|
||
- **SSL:** Force SSL enabled, Let's Encrypt via NPM — same pattern as every other public KingDezigns service
|
||
- **Internal LAN access:** `http://192.168.150.40:8000/dashboard/` (unchanged, still works from VLAN 20)
|
||
- No CrowdSec/Fail2Ban changes were required — traffic proxied through NPM inherits the same protection (CrowdSec reads NPM's logs regardless of backend) as every other service behind it.
|
||
|
||
---
|
||
|
||
## 🖥️ Architecture
|
||
|
||
### Stack
|
||
- **API:** FastAPI (Python 3.12), Docker container `fundtracker_api`
|
||
- **Database:** PostgreSQL 16 (Alpine), Docker container `fundtracker_db`, bind-mounted at `/opt/fundtracker/pgdata`
|
||
- **Frontend:** Vanilla HTML/JS/CSS (no build step), served directly by FastAPI's `StaticFiles` mount at `/dashboard/`
|
||
- **Scheduler:** APScheduler, in-process (not an OMV Scheduled Job) — runs the daily sync + report job inside the `fundtracker_api` container itself
|
||
- **Market data:** Yahoo Finance via the `yfinance` library, using a `curl_cffi` session with Chrome browser impersonation (see Known Issues below — this is required, not optional)
|
||
|
||
### Data model — shared securities, private watchlists
|
||
The core design decision: **security data is shared across all profiles, personal data is private.**
|
||
|
||
| Concept | Table | Shared or private | Contains |
|
||
|---|---|---|---|
|
||
| Security | `instruments` | **Shared** — one row per ticker, regardless of how many profiles track it | ticker, name, category, group tag, price history (via `price_points`), benchmark link |
|
||
| Watchlist entry | `watchlist_entries` | **Private** per profile | is_held, held_since, cost_basis — a profile's personal relationship to a shared security |
|
||
| Metric snapshot | `metric_snapshots` | Shared (security-level) | Daily-recorded quality/opportunity/combined/rebound scores, used for trend detection |
|
||
| Settings | `settings` | Private per profile | Currently just each profile's rebalance date |
|
||
|
||
**Practical effect:** if two profiles both track GOOGL, its price history is synced exactly once and shared instantly between them — adding a ticker someone else already tracks is immediate, no re-sync wait, no duplicate Yahoo API traffic. Only `is_held`/`cost_basis`/which-tickers-you-track are private; ticker, name, group, and all performance scores are identical for every profile tracking that security.
|
||
|
||
### Multi-profile auth
|
||
- Session-cookie based (httponly, `SameSite=Strict`), **not** JWT-in-localStorage — a cookie can't be read by injected JavaScript, meaningfully reducing XSS token-theft risk for a public-facing app
|
||
- Passwords hashed with `bcrypt`
|
||
- 5 failed login attempts locks the account for 15 minutes
|
||
- Forced password change on any seeded or admin-created account — cannot be skipped
|
||
- Admin role can create/delete/promote accounts, force password resets, and view the anonymized cross-profile Census (see below) — but is **not** shown other profiles' actual holdings or cost basis by design
|
||
|
||
---
|
||
|
||
## 📊 Scoring Methodology
|
||
|
||
All scores are computed from price history only (no fundamentals data). Four are per-security (shared, identical for every profile); one is per-holding (private).
|
||
|
||
### Quality score (0–100)
|
||
Long-run risk-adjusted strength, deliberately blind to short-term noise:
|
||
- 35% annualized Sharpe ratio
|
||
- 20% annualized Sortino ratio (downside-only volatility)
|
||
- 25% CAGR
|
||
- 20% consistency (% of historical rolling windows, of the selected length, that were positive)
|
||
|
||
Requires 30+ price points to compute; below that, returns null rather than a misleading number.
|
||
|
||
### Opportunity score (0–100)
|
||
`0.6 × quality + 0.4 × depressed-ness`. Depressed-ness requires **both** of the following to agree (via `min()`, not an average) — this was a deliberate fix after an early version let a low-volatility fund sitting exactly at its 52-week high still score as a strong "opportunity" purely on quality alone:
|
||
1. `current_return_zscore` — is the current rolling-window return unusual *for this specific security's own history*
|
||
2. `drawdown_from_52wk_high_pct` — is it actually below its recent peak, not just statistically unusual
|
||
|
||
### Combined score (0–100)
|
||
Harmonic mean of quality and opportunity (not a plain average) — deliberately punishes imbalance, so a security only scores high here if it's strong on **both** axes, not exceptional on one while mediocre on the other.
|
||
|
||
### Rebound score (0–100) — the core "proven leader, currently crashed" signal
|
||
Answers a different question than opportunity score: *does this security normally rank near the top of its peer group by raw return, and has it recently fallen hard in that ranking?* Reconstructed retroactively from price history already on file (no waiting for new data to accumulate) by:
|
||
1. Computing every security's rolling-window return, for every date, within its shared group tag (e.g. all `STOCK`-tagged securities)
|
||
2. Ranking them against each other on every date → a full history of peer-relative percentile rank per security
|
||
3. Comparing today's percentile to the security's own historical average percentile
|
||
4. Combining "how strong is it historically" and "how far has it fallen right now" via harmonic mean, same imbalance-punishing logic as combined score
|
||
|
||
Requires a peer group of 5+ securities sharing the same group tag to compute at all.
|
||
|
||
### Buy / Sell / Hold signal (held positions only)
|
||
A simple, explainable, threshold-based heuristic — not a black box, and explicitly documented as a screening aid rather than a trade instruction:
|
||
- **SELL** if quality has fallen below 40, or dropped 15+ points in the trailing 7 days, or the position is deeply below its own normal range with no rebound signal backing it up
|
||
- **BUY** (add to position) if opportunity ≥ 65 with quality ≥ 60, or rebound score ≥ 65
|
||
- **HOLD** otherwise
|
||
|
||
Trend-based triggers (the 7-day quality delta) only become meaningful once daily snapshots have accumulated for about a week post-deployment — this is a genuine cold-start limitation, not a bug.
|
||
|
||
### Quality lookback (adjustable, default 3 years)
|
||
All of the above can be restricted to a trailing window (6mo/1yr/2yr/3yr/5yr/all) rather than always using full history. This matters: a fund that compounded fast for several early years and has since flattened out will look artificially strong if scored on all-time history — the early years hide what it's actually doing now. Verified with a synthetic test: a fund with 4 strong years then 1 flat year scored 84.6 quality on all-history, 25.9 restricted to the trailing year.
|
||
|
||
---
|
||
|
||
## 🔐 Admin Features
|
||
|
||
### Census (anonymized cross-profile leaderboard)
|
||
`Admin → Census` page. Aggregates every tracked security across **all** profiles, showing how many profiles track it and its shared performance scores — deliberately does **not** reveal which specific profile tracks what, consistent with the private-watchlist design above.
|
||
|
||
### Legal document versioning
|
||
Two independently-versioned, admin-editable documents, stored in the database:
|
||
- **Terms of Use** — gated immediately after login, blocks all dashboard access until accepted
|
||
- **Investment Disclaimer** — gated specifically before adding any ticker to a watchlist (not a one-time login gate)
|
||
|
||
Publishing a new version (`Admin → Accounts → Legal documents`) **immediately** requires every profile to re-accept before continuing — acceptance is tracked per specific version number, so a version bump is itself the "flag everyone to re-agree" mechanism, no separate manual flag needed. An admin can also force one specific account to re-accept without republishing for everyone.
|
||
|
||
> **Not a substitute for actual legal review.** The shipped default text is comprehensive boilerplate covering no-liability-for-losses, educational-use-only, no-fiduciary-relationship, and indemnification — drafted carefully, but worth having an attorney glance at given this is a public-facing tool touching real investment decisions.
|
||
|
||
---
|
||
|
||
## 💼 Portfolio / Transaction Tracking (added, undated prior session — first documented here 2026-08-19)
|
||
|
||
In addition to the shared-security screening/scoring system described above, Fund Tracker also includes a full **buy-lot/sale/split transaction register** per profile — this had been built in an earlier session but was never previously written up in this file. It lives alongside (not instead of) the scheduler, pricing/NAV sync, auth, admin functionality, settings, and watchlist behavior documented elsewhere in this file.
|
||
|
||
### Data model
|
||
- **Buy lots** — each purchase is its own row: original quantity, remaining quantity, buy price, buy fees, notes/source. Selling from a lot reduces its remaining quantity but the lot itself is never deleted.
|
||
- **Sales** — recorded as their own rows, linked to the buy lot(s) they draw down via **sale allocations**, so the relationship between a sale and the lot(s) it consumed is preserved.
|
||
- **Matching methods** — FIFO, LIFO, and Specific Lot Identification (user picks exact per-lot quantities, validated to sum to the total sold) are all supported at time of sale.
|
||
- **Lot status** — `ACTIVE` (untouched), `PARTIAL` (some shares sold, some remain), `CLOSED` (fully sold) — a closed lot stays visible in the register rather than disappearing, preserving full history.
|
||
- **Stock splits / reverse splits** — a recorded split only adjusts share count and per-share cost basis on lots that were **active and purchased before** the split date. Lots purchased on/after the split date, and lots already fully sold before the split date, are left untouched.
|
||
|
||
### Key endpoints
|
||
| Endpoint | Purpose |
|
||
|---|---|
|
||
| `GET /portfolio/holdings` | Active holdings dashboard view |
|
||
| `GET/POST /portfolio/lots/{ticker}` | List / create buy lots for a ticker |
|
||
| `PATCH /portfolio/lots/{lot_id}` | Edit a buy lot — only allowed while it has zero sale allocations (see locking, below) |
|
||
| `DELETE /portfolio/lots/{lot_id}` | Delete a buy lot (same zero-allocation restriction) |
|
||
| `GET/POST /portfolio/sales` | List / record sales (FIFO/LIFO/Specific ID) |
|
||
| `DELETE /portfolio/sales/{sale_id}` | Delete a sale — **restores** the allocated quantity back to the affected buy lot(s), which also unlocks them for editing |
|
||
| `GET/POST /portfolio/splits` | List / apply stock splits |
|
||
| `GET /portfolio/register` | The full buy-lot register: each lot with its associated sale allocations, current price, unrealized G/L, realized G/L, and status — this is what the Portfolio page renders |
|
||
| `POST /portfolio/import/csv` | Bulk import buys/sells from pasted CSV text |
|
||
|
||
### Editing & locking
|
||
A buy lot can be edited or deleted only while it has **no** sale allocations against it — once a sale references a lot, that lot locks (shown as 🔒 in the UI) to protect the historical/tax record. The recovery path is to delete the associated sale first (which restores the shares to the lot), then edit/delete the now-unlocked lot.
|
||
|
||
### Portfolio UI — three-level collapsible hierarchy (updated 2026-08-19)
|
||
The Portfolio page (`/dashboard/portfolio.html`) renders the register as **Ticker → Buy Lot → Sale(s)**, both levels collapsible:
|
||
|
||
- **Ticker rows** — one row per ticker held, **collapsed by default** on page load. Clicking the row's twistie (▶/▼) expands it to reveal that ticker's individual buy lots. This was the 2026-08-19 change — previously every buy lot for every ticker rendered at once, making the register long and hard to scan.
|
||
- **Buy lot rows** — nested under their ticker once expanded. Each lot that has associated sales shows its own twistie (e.g. `▶ 2`) which expands to reveal the sale row(s) consumed from that specific lot. This twistie already existed prior to 2026-08-19 and was left unchanged.
|
||
- Expanding a ticker does not auto-expand its lots' sale twisties, and collapsing a ticker hides its lots' sale rows regardless of their own expand/collapse state — the two levels are independent, but a lot's sales can only ever be visible while its parent ticker is also expanded.
|
||
- Implementation notes: both expand states are tracked client-side only (`_expandedTickers` and `_expanded` JS `Set`s in `portfolio.html`), not persisted to the backend or across page reloads. Toggling is done via direct DOM show/hide rather than a full table re-render, so toggling one ticker/lot does not disturb the expand state of others.
|
||
- **Deployment note:** this was a frontend-only change to `api/app/static/portfolio.html` — no router, schema, or model changes. Deployed via the standard code-only pattern: file copied to a `/tmp` scratch path via `scp`, moved into `/Kingdezignsnas16/fundtracker/api/app/static/` with `sudo cp` (the project directory is root-owned), then `sudo docker restart fundtracker_api` (plain restart is sufficient since `app/` is bind-mounted — no rebuild needed for a static-file-only change).
|
||
|
||
### Known deployment gotchas (from earlier portfolio build work — kept here to avoid repeating)
|
||
- `models.py` has previously broken from using `Decimal` or `CheckConstraint` without importing them — always verify imports after any model change before restarting the container.
|
||
- Files transferred from a stale `/tmp` copy have previously overwritten newer code (e.g. an old portfolio router missing the `register` endpoint) — always confirm the source file being copied is the actual latest version, not a leftover scratch copy.
|
||
- `BuyLotEdit` (in `schemas.py`) has previously been referenced by `portfolio.py` before being defined — a reminder that schema and router changes need to land together, not the router first.
|
||
- General rule reaffirmed: after any deploy, check container startup logs, hit `/health`, then explicitly test the specific endpoint/UI behavior that changed — don't assume a clean restart means the change is live and correct.
|
||
|
||
### Admin — user account email management
|
||
Two admin endpoints exist for managing account emails, separate from the Census/legal-document admin features described above:
|
||
```
|
||
PATCH /admin/users/{id}/email
|
||
GET /admin/users/{id}/email-changes
|
||
```
|
||
Email changes are logged in an audit trail that requires a reason at time of change.
|
||
|
||
---
|
||
|
||
## 🐳 Docker Configuration
|
||
|
||
Managed via OMV → Services → Compose, project `fundtracker`, at `/Kingdezignsnas16/fundtracker/`.
|
||
|
||
### Key environment variables
|
||
| Variable | Purpose |
|
||
|---|---|
|
||
| `DATABASE_URL` | Postgres connection string |
|
||
| `SYNC_HOUR_UTC` / `SYNC_MINUTE_UTC` | Daily sync+report time (default 06:00 UTC) |
|
||
| `SMTP_HOST/PORT/USER/PASSWORD/FROM` | Same Zoho account used by every other KingDezigns notification |
|
||
| `ADMIN_SEED_USERNAME/PASSWORD/EMAIL` | Bootstrap admin, only used if the `users` table is completely empty |
|
||
| `SESSION_TTL_DAYS` | Login session length (default 14) |
|
||
| `COOKIE_SECURE` | **Must be `true`** now that this is exposed externally over HTTPS — `false` is LAN-testing-only and sends credentials in cleartext if left on over plain HTTP |
|
||
| `PUBLIC_BASE_URL` | Should be `https://funds.kingdezigns.com` — used to build password-reset email links |
|
||
| `REPORT_WINDOW_DAYS/LOOKBACK_YEARS/TOP_N` | Daily email report defaults |
|
||
|
||
### Deployment pattern (established across multiple rounds of iteration)
|
||
- **Code-only changes** (edits inside `api/app/`): copy the updated `app/` folder into the OMV project directory, then `sudo docker restart fundtracker_api`. A plain restart is sufficient since `app/` is bind-mounted.
|
||
- **New Python dependencies** (`requirements.txt` changes): a restart is **not** enough — requires `docker compose build --no-cache api` followed by `up -d`, since dependencies are baked into the image at build time.
|
||
- **Compose YAML / environment variable changes**: requires `up -d` (recreate), not `restart` — a plain restart reuses the old in-memory environment. This distinction caused real deploy failures twice during development (once for the `curl_cffi` fix, once for the `bcrypt` addition) before being nailed down.
|
||
- Because `/Kingdezignsnas16/fundtracker/` is root-owned (`drwx------`, contains DB credentials), file transfers land in a `rufusking`-writable scratch path first (e.g. `/tmp/ft_upload/`), then get moved into place with `sudo cp -r`.
|
||
|
||
### Database migrations
|
||
Two tiers, by risk:
|
||
- **Additive** (`ADD COLUMN IF NOT EXISTS`) — safe, runs on every startup, no backup needed.
|
||
- **Structural** (e.g. the shared-security/private-watchlist split, the per-profile settings conversion) — genuinely restructures data, including column drops. These are idempotent (safe to run repeatedly) but a `pg_dump` backup is taken before deploying any update in this category, restorable via `psql` into a fresh container.
|
||
|
||
---
|
||
|
||
## ⚠️ Known Issues & Fixes Already Shipped
|
||
|
||
- **Yahoo Finance blocking (fixed):** `yfinance`'s default HTTP client gets silently blocked by Yahoo's bot detection with increasing frequency — the library misreports this as "no data" rather than "blocked." Fixed by routing all requests through a `curl_cffi` session impersonating a real Chrome browser's TLS fingerprint.
|
||
- **Bogus-ticker crash (fixed):** a nonexistent ticker could make `yfinance`'s internal metadata lookup throw an unhandled exception (a JSON decode error) instead of just returning empty data, crashing the add-ticker request entirely. Now caught and treated as "0 rows synced," which correctly triggers the verification-failure flow instead.
|
||
- **Ticker verification with override:** adding a brand-new ticker attempts a real sync as verification. Zero rows returned prompts a warning with an explicit "add anyway" override (rather than a hard block) — specifically to handle legitimate cases like a recent ticker rename Yahoo hasn't caught up to yet (e.g. Siemens Energy's SMNEY → SMERY change).
|
||
- **Rate-limit-driven data source risk:** noted, not yet acted on — if Yahoo's blocking becomes more aggressive over time, alternative data sources may need evaluation. Deferred as a future consideration.
|
||
|
||
---
|
||
|
||
## 💾 Backup
|
||
- `/opt/fundtracker/pgdata` (bind-mounted Postgres data) can fold into `nas16-backup.sh` the same way Forgejo's data was — not yet formally added as of this writing.
|
||
- A raw file-copy backup of a *live* Postgres directory can be inconsistent; `pg_dump` is the reliable method (see Deployment pattern above). Given nearly all price data is re-derivable by re-syncing from Yahoo, a stale/imperfect backup mainly costs a re-sync, not real data loss — the one exception is user accounts, watchlist memberships (which securities each profile tracks), cost basis, and legal-acceptance records, which are **not** re-derivable and are the actual reason to keep backups current.
|
||
|
||
---
|
||
|
||
## 🧠 Summary for AI Systems
|
||
- Fund Tracker = **multi-profile mutual fund/stock tracking web app**, Docker Compose on NAS16, VLAN 50.
|
||
- Public at `https://funds.kingdezigns.com/` via NPM on HAS, Force SSL. Also reachable internally at `http://192.168.150.40:8000/dashboard/`.
|
||
- Two containers: `fundtracker_api` (FastAPI) and `fundtracker_db` (Postgres 16).
|
||
- **Shared security data, private watchlists**: ticker/price history/group tag are shared across all profiles; is_held/cost_basis/which-tickers-tracked are private per profile.
|
||
- Scoring: quality (risk-adjusted, security-level) → opportunity (quality + genuine dip, requires both z-score AND drawdown to agree) → combined (harmonic mean of quality+opportunity) → rebound (peer-relative "usually top of its group, currently collapsed" detector, reconstructed retroactively from existing price history).
|
||
- Buy/Sell/Hold signals apply only to positions flagged `is_held` — simple threshold heuristic, documented as a screening aid, not a trade instruction.
|
||
- Auth: httponly session cookies, bcrypt, 5-attempt lockout, forced password change on seeded accounts.
|
||
- Admin: account management, anonymized cross-profile Census, versioned legal document publishing (version bump = automatic re-acceptance requirement for everyone).
|
||
- Market data via `yfinance` + `curl_cffi` (Chrome impersonation) — required workaround for Yahoo's bot detection, not optional.
|
||
- Deployment: code-only changes = folder swap + `docker restart`; dependency changes = rebuild; compose/env changes = `up -d`. Structural DB migrations require a `pg_dump` backup first.
|
||
- `COOKIE_SECURE=true` and `PUBLIC_BASE_URL=https://funds.kingdezigns.com` must both be set now that this is publicly exposed — leaving `COOKIE_SECURE=false` on a public HTTPS deployment would send credentials in cleartext.
|
||
- **Portfolio / transaction tracking (documented here 2026-08-19, built in an earlier session):** a full buy-lot/sale/split register sits alongside the scoring/screening system — buy lots (with remaining-qty tracking), sales (FIFO/LIFO/Specific ID matching via `/portfolio/sales`), and splits (`/portfolio/splits`, only affects lots active and purchased before the split date). `GET /portfolio/register` returns the full lot+sale view the Portfolio page renders. Lots lock against edit/delete once a sale references them — deleting the sale restores shares and unlocks the lot. CSV bulk import via `/portfolio/import/csv`. Full endpoint list and locking rules in the dedicated section above.
|
||
- **Portfolio UI updated 2026-08-19:** the register now renders as a three-level collapsible hierarchy — **Ticker (collapsed by default) → Buy Lot → Sale(s)** — instead of showing every lot for every ticker at once. Frontend-only change to `portfolio.html`, deployed via the standard code-only pattern (scp to `/tmp` → `sudo cp` into the root-owned project dir → `sudo docker restart fundtracker_api`, no rebuild required).
|
||
- **Known portfolio deployment gotchas** (missing `Decimal`/`CheckConstraint` imports in `models.py`, stale `/tmp` copies overwriting newer router code, schema/router ordering) are logged in the Portfolio section above — check there before re-diagnosing the same class of failure.
|
||
|
||
---
|
||
|
||
# ✔️ End of File
|