Portfolio Syncing

PortfolioSyncing

Refresh positions and balances from SnapTrade into family_office.db, then validate the snapshot against safety thresholds before anyone reasons off it.

family_office.db is the system of record. The Google Sheets DataHub was retired 2026-07-31; there is no spreadsheet to push to and no gdrive MCP configured.

Step 0: Refresh (sync-first, mandatory)

Positions and balances come from the local DB, refreshed FIRST so it can never be stale. Follow the shared Sync-First + DB-Read pattern.

uv run python -m src.integrations.snaptrade.sync_db          # writes positions + balances
uv run python -m src.integrations.snaptrade.sync_db --show   # read back the snapshot

Completion criterion: the positions and balances tables carry this run's synced_at. Everything downstream reads the DB, not a CSV.

To refresh positions, transactions, and bank expenses together:

uv run python -m src.integrations.refresh_all

Account Routing

config/snaptrade-accounts.yaml declares each account's role and enabled flag. An account with no declared role refuses to sync rather than guessing. Cash-management accounts belong to SimpleFIN (TransactionSyncing), not here, so brokerage margin math stays clean.

Safety Gates

⚠️ Capture the "before" state first, or these gates cannot fire. sync_db is a current-state store: it deletes each account's prior position rows and overwrites its single balances row (which is keyed on account_id). No history survives the refresh, so read the existing snapshot before running Step 0 and hold it in the session to diff against. There is no position_history table to fall back on.

# BEFORE Step 0 — capture the prior generation
sqlite3 family_office.db \
  "SELECT symbol, quantity, average_purchase_price FROM positions ORDER BY symbol;"
sqlite3 family_office.db "SELECT * FROM balances;"

STOP conditions (require user confirmation):

1. Fewer tickers than the previous snapshot (possible sales)
2. Any quantity change > 10%
3. Any cost basis change > 20%
4. Margin balance jumped > $5,000 (unintentional draw)
5. SPAXX discrepancy > $100 against the balances row

FLAG conditions (alert but proceed): SPAXX off by $1-$100; pending activity off by more than $100.

When STOPPED: show a clear diff table, ask the user to confirm, proceed only after explicit approval.

Cash Position Logic

• Do NOT use the SPAXX position value; it shows only settled money market.
• Use "Settled cash" from the balances row for the SPAXX figure.
• If settled cash is 0, SPAXX is $0 (all funds invested or in margin).
• "Cash market value" is NOT cash; it is the value of positions held in the Cash account rather than the Margin account.

Price Freshness: SnapTrade marks lag one session

A successful sync does not mean current prices. SnapTrade publishes price on each position from the prior session's close, so account_equity and gross_market_value are stale by one trading day even when synced_at is the current timestamp. Verified 2026-08-04: two syncs, at 17:46 and 17:53 CT (well after the 15:00 CT close), both returned Aug 3 closes on all six spot-checked tickers. PLTR read 23% below its actual Aug 4 close.

What this means in practice:

• Structurally reliable: holdings, quantities, cost basis, settled cash, margin debt. Use the DB for all of these.
• Lagging: price, account_equity, gross_market_value, and every ratio derived from them (equity-to-debt, gross-to-debt, position weights).

On any day with a material move, mark to market before reasoning off the snapshot. On 2026-08-04 the as-synced equity understated the real figure by about 8%, and equity-to-debt read 2.206x when it was actually 2.392x. That is the difference between "still in breach" and "approaching the gate".

Check freshness by comparing a couple of DB prices to the live close:

uv run python -c "
import sqlite3, yfinance as yf
c = sqlite3.connect('family_office.db')
for s in ['PLTR','VOO']:
    p = c.execute('SELECT price FROM positions WHERE symbol=?', (s,)).fetchone()[0]
    live = yf.download(s, period='2d', progress=False, auto_adjust=False, multi_level_index=False)['Close'].iloc[-1]
    print(f'{s}: DB {p:.2f} vs live close {float(live):.2f}')
"

Layer Classification for New Tickers

Dividend funds → Layer 2, growth → Layer 1, hedges → Layer 3. If a new ticker does not clearly match a pattern, mark it UNKNOWN - Manual Review Required and ask the user rather than guessing.

CSV Fallback

CSV import is a fallback and re-verification path only, not the primary flow. The IngestPositions workflow archives Portfolio_Positions_*.csv and Balances_*.csv from ~/Downloads into imports/. Use it when a live source is down or the user explicitly wants an archive.

Classifier for Fidelity position exports: a header containing Ex-date is the dividend view; a header containing Average Cost Basis is the regular view. The dividend view and transaction history CSVs are still consumed by dividend-tracking and TransactionSyncing.

Pre-Flight Checklist

• SnapTrade account is enabled and routed in config/snaptrade-accounts.yaml
• SNAPTRADE_* keys are present in .env
• DATABASE_URL is set in .env

Reference Files

• User Profile: user-profile.yaml
• Account routing: config/snaptrade-accounts.yaml
• Sync-first pattern: .claude/skills/_shared/SyncFirstDbRead.md

Skill Type: Domain (workflow guidance) Enforcement: BLOCK (data integrity critical) Priority: Critical