Part of the Sanchayam series.
Why Friday
Portfolio performance is tracked week by week, not day by day. Daily snapshots would generate a large amount of data for a view that is displayed weekly at best. Friday is chosen because Indian markets close at 3:30 PM IST on Friday. The snapshot cron runs at 11:00 AM UTC, which is 4:30 PM IST - 30 minutes after market close, giving price feeds time to settle.
// Friday portfolio snapshot: 11:00 AM UTC = 4:30 PM IST, 30 min after Indian market close
cron.schedule('0 11 * * 5', async () => {
await takeSnapshotsForAllUsers('cron')
await enqueueEligibleAssets()
})
After taking snapshots, enqueueEligibleAssets is called to mark the completed week as eligible for historical price backfill.
What a Snapshot Stores
Each snapshot is one row in portfolio_snapshots and one row per holding in portfolio_snapshot_entries:
CREATE TABLE portfolio_snapshots (
id UUID PRIMARY KEY,
user_id UUID NOT NULL REFERENCES users(id),
snapshot_date DATE NOT NULL,
source VARCHAR(20) NOT NULL DEFAULT 'cron', -- 'cron' | 'import'
portfolio_xirr NUMERIC(10,6),
UNIQUE(user_id, snapshot_date)
);
CREATE TABLE portfolio_snapshot_entries (
snapshot_id UUID NOT NULL REFERENCES portfolio_snapshots(id) ON DELETE CASCADE,
holding_id UUID NOT NULL REFERENCES holdings(id),
asset_id UUID NOT NULL REFERENCES assets(id),
asset_name VARCHAR NOT NULL,
asset_category VARCHAR NOT NULL,
currency VARCHAR(10) NOT NULL,
quantity NUMERIC(38,8) NOT NULL,
price_per_unit_minor NUMERIC(38,0) NOT NULL,
value_minor NUMERIC(38,0) NOT NULL,
xirr NUMERIC(10,6),
...
);
Values are stored in the asset’s native currency in minor units. FX conversion to the user’s base currency happens at query time using current rates, not at snapshot time. This means the portfolio chart always reflects current exchange rates applied to historical values - a deliberate choice that avoids storing FX rates for every historical snapshot date.
Per-holding XIRR is computed and stored at snapshot time. Computing it at query time across months of data would be prohibitively slow.
One-Per-Week Query
The database may accumulate multiple snapshots per week if the cron fires more than once (restart during a Friday, manual trigger). The portfolio query deduplicates to one snapshot per week using DISTINCT ON:
WITH one_per_week AS (
SELECT DISTINCT ON (DATE_TRUNC('week', s.snapshot_date))
s.id, s.snapshot_date, s.source, s.portfolio_xirr
FROM portfolio_snapshots s
WHERE s.user_id = ${targetId}
ORDER BY DATE_TRUNC('week', s.snapshot_date),
(SELECT COUNT(*) FROM portfolio_snapshot_entries WHERE snapshot_id = s.id) DESC,
(CASE WHEN s.source = 'cron' THEN 0 ELSE 1 END),
s.snapshot_date DESC
)
Within a week, preference order is: most entries first, then cron over import, then latest date. This ensures the most complete snapshot for each week is used.
Historical Backfill on Saturday
When a new asset is added to the system, there are no snapshots for the weeks before it was added. The backfill generates historical snapshots for all past Fridays where price data is available:
// Saturday 1 AM UTC: historical snapshot backfill
cron.schedule('0 1 * * 6', async () => {
await runHistoricalSnapshotBackfill()
// then rebuild all family snapshots
})
The backfill runs with 10 concurrent workers. It finds all (userId, friday) pairs where a snapshot does not yet exist and price data does, then calls takeHistoricalSnapshot for each pair. A 50% coverage rule applies: if fewer than half of a user’s active holdings have price data for a given week, the snapshot is skipped. A snapshot with too few holdings would give a misleadingly low total value.
Historical snapshots are marked source = 'import' to distinguish them from the live Friday snapshots.
Price History Purge
Storing every price point indefinitely would be wasteful. The nightly purge at 2 AM keeps the last 30 days in full and thins older data to one price per asset per week:
DELETE FROM asset_price_history
WHERE price_date < CURRENT_DATE - INTERVAL '30 days'
AND id NOT IN (
SELECT DISTINCT ON (asset_id, DATE_TRUNC('week', price_date))
id
FROM asset_price_history
WHERE price_date < CURRENT_DATE - INTERVAL '30 days'
ORDER BY asset_id, DATE_TRUNC('week', price_date), price_date DESC
)
The kept row per week is the latest price that week for each asset. This aligns with how historical snapshots use price data - they look up the price closest to the Friday date, so one row per week is sufficient for backfill purposes.
Family Snapshots
When a user belongs to a family group (family_id set on the user), their portfolio snapshot triggers a rebuild of the family aggregate snapshot for the same date:
if (user.family_id) {
buildFamilySnapshotForDate(user.family_id, snapshotDate).catch(err => {
logger.error({ err }, '[snapshots] family snapshot update failed')
})
}
The family snapshot aggregates all members’ holdings into family_portfolio_snapshots and family_portfolio_snapshot_entries. Each entry carries the originating user_id so the frontend can break down the family portfolio by member. Family XIRR is computed the same way as individual XIRR - all lots across all members flattened into one cash flow series with FX conversion.
On Saturday after the historical backfill completes, the family snapshots for all active families are also rebuilt to incorporate the newly backfilled individual snapshots.