Portfolio Connections & Sync

Portfolio Connections & Sync Reliability

This section documents the broker-connection sync internals— how holdings are pulled automatically, throttled, retried, and guarded against data loss. It complements the Portfolio overview (which covers the routes, CSV import, and the per-broker adapter details) by focusing on the auto-refresh reliability model shipped in the Portfolio Sync Reliability work (Slice 0). For the MOSL Rise PIN/OTP login protocol, client-side encryption scheme, and session-cookie correlation details, see MOSL Rise sync in the Portfolio doc; this page only documents what that flow adds for auto-sync.

Connection model & roles

A broker account is one row in the shared brokers.broker_connections table (see Brokers), with Fernet-encrypted credentials, shared by the trading bot, dashboard, and portfolio runtime. The role column distinguishes how a connection is used:

Auto-sync only considers connections whose role includes sync: config_repo.get_sync_connections() returns the SYNC and TRADING+SYNC rows. A deployment with only TRADING connections therefore correctly runs no portfolio sync — but the auto-sync loop itself still executes its poll every cycle (see unconditional execution below), so it is never silently skipped by an upstream guard.

Auto-refresh loop (token-refresher container)

The deployed token-refresher service is also the portfolio maintenance loop. It polls roughly every 5 minutes; each iteration refreshes due broker tokens and then runs due portfolio syncs for Dhan, Zerodha, and IBKR. PortfolioAutoSyncService.run_due() (arb_bot/portfolio_app/auto_sync.py) iterates get_sync_connections(), filters to SUPPORTED_AUTO_SYNC_BROKERS = {"DHAN", "ZERODHA", "IBKR"}, asks _is_due for each, and calls ConnectionSyncService.sync_connection(id, auto=True) for those that are due.

MO_RISE stays manual. Its holdings sync still works through the existing OTP flow, but it is deliberately excluded from the hourly/automatic maintenance loop so the service never tries to reuse an expiring session in the background.

Dhan holdings self-heal on auth failure. If a Dhan holdings fetch comes back with an auth-style failure, the adapter now refreshes that connection's token on demand and retries the holdings call once before giving up. That keeps stale but recoverable TRADING+SYNC connections from showing an empty portfolio when the background refresher runs late.

Per-broker cadence & throttle

Each connection’s brokers.broker_sync_states row carries the throttle state. The cadence differs by broker:

Separate manual vs auto clocks (H2). The throttle reads last_auto_sync_at, a dedicated column written only when record_success/record_attempt are called with auto=True. A manual dashboard sync (POST /connections/{id}/sync) updates updated_at but leaves last_auto_sync_at untouched, so clicking “Sync” at 10:00 no longer silently delays the next auto-sync to 10:30 even when data is stale. For deployments still on the older schema, _is_due falls back to updated_at.

Failure backoff & the STALLED terminal state

On failure, BrokerSyncStateRepository.record_failure increments failure_count and sets next_retry_at using an escalating schedule _BACKOFF_MINUTES = [1, 5, 15, 60] — the index is failure_count - 1, capped at the last value, so 60 minutes is reached at the 4th consecutive failure.

Backoff is honored by all brokers (H3). The DHAN/ZERODHA intraday check first refuses to be due while now < next_retry_at, so a persistently-failing auth connection backs off rather than hammering every 30 min. IBKR dropped its flat delay_minutes=60 (H4) and now uses the same escalating schedule, so a misconfigured Flex query no longer retries 16×/day forever.

STALLED terminal state (H4). After PORTFOLIO_SYNC_MAX_FAILURES_STALLED (10) consecutive failures, _is_due returns False permanently for that connection — auto-sync stops retrying it. A manual sync that succeeds resets failure_count via record_success, clearing STALLED. This is the hard stop that prevents an unrecoverable connection from burning retries indefinitely.

Per-connection advisory lock (H1)

Auto-sync (token-refresher container) and a manual dashboard sync (dashboard container) can target the same connection at the same time. To stop the clear/upsert sequence from interleaving and wiping holdings, sync_connection takes a session-level Postgres advisory lock keyed by _LOCK_BASE + connection_id (_LOCK_BASE = 0x504F5254, the “PORT” namespace) before doing any work:

• It uses pg_try_advisory_lock — not the transaction-level pg_try_advisory_xact_lock, which releases at COMMIT and so could not span the whole sync. The lock session is held open across the entire sync and released (with pg_advisory_unlock) in a finally.
• If the lock is already held, the second caller returns immediately with status="skipped" and message “sync already in progress”.
• Fail-open: if advisory locks are unavailable (e.g. the SQLite test DB), the call logs a warning and proceeds. This is not a safety-critical section; the atomic persist below is the real guard.

Atomic holdings persist — no wipe on failure (C3)

The broker-holdings upsert is wrapped so a mid-write failure can never leave a portfolio empty. HoldingRepository.replace_portfolio_atomic(name, df) stages the new rows and swaps them with the clear in a single transaction; if the upsert raises, the clear is rolled back. The persist block sits in its own try/except inside _sync_connection_inner — on exception it logs, calls record_failure(..., "PERSIST_ERROR"), and returns a failed SyncResult with the existing holdings intact. Previously clear_portfolio() deleted the old rows before the upsert, and a raised exception escaped before record_failure ran — silent data loss with the UI showing green.

MO_RISE: manual OTP sync only

MO_RISE holdings sync still uses the existing PIN/OTP workflow, session-cookie correlation, and encrypted credential storage. The critical difference is that it is no longer part of the hourly auto-sync cadence. Operators run it manually when they are ready to enter OTP, and the dashboard “Verify OTP” / “Request OTP” actions resume that exact session. See MOSL Rise sync for the full PIN/OTP protocol, session-cookie correlation, and encryption scheme.

The OTP resend (request_otp in mo_rise_client.py) is now implemented: it submits the identify step’s otpToken (hybrid-encrypted, using the same session cookies + anti-forgery header as the originating login) to the resend endpoint, re-triggering the OTP send. The user then calls verify_otp with the new code on the same cookies. (The resend URL is pattern-derived from the sibling Home/ endpoints rather than read off a real resend capture — see the source TODO; confirm against the live MOSL server before go-live.)

Manual sync endpoints

All under /api/portfolio (composed in portfolio_app/api/router.py), behind the same JWT session as the rest of the API:

See the Portfolio routes table for the full CRUD surface and the automatic price refresh note for how /market-data/refresh is also driven by the dashboard.

Config knobs

The two auto-sync knobs live in class Config (arb_bot/config.py); see the Config Reference:

The backoff schedule [1, 5, 15, 60] min is the BrokerSyncStateRepository._BACKOFF_MINUTES constant (portfolio_app/db/repository.py), not a Config value.