Contents
Core REST Explorer
Run historical and aggregate endpoints
→
Streaming Explorer
Subscribe, snapshot, and stream live data
→

2026-06-21-hourly-csv-export-design

Hourly CSV Export + Usage Metering + Profile Redesign

Branch: csv (rebased clean onto origin/master, single commit CSV export (daily)).

Goal

Extend the existing daily CSV export with hourly range export, replace the per-export counter with a unified pairs/month budget, restrict all exports to a configurable last-month window, surface usage in a redesigned profile, and document the feature.

Background (existing daily export)

  • Client ZealousExplorer.getVisibleChartData() collects visible daily points → POST /api/premium/csv-export → generateCsv() → CSV download.
  • Firestore csvExportUsage/{uid} = { currentMonth, exportCount, lastExportAt }, via src/lib/firebase/csv-export-usage-service.ts.
  • Gated on Stripe csv-export feature (requireFeature server, useHasFeature client).
  • Profile CsvExportUsageSection shows usage. csv-export feature is in the canonical src/lib/api/auth/plan.ts.

Decisions

  1. Hourly export = server-side range export. Client has only one day of hourly data loaded at a time, so the server fetches the range. Daily stays client-provided (unchanged).
  2. Unified pairs/month budget, spend model (like AI tokens — no cross-export dedup). Daily + hourly share it. A pair = distinct source|symbol.
  3. Last-month window applies to both granularities: exported range must lie within [today − WINDOW_DAYS, today]. One rule bounds recency and span.
  4. Hourly gating = requires both csv-export AND existing hourly-data features. No new Stripe feature.
  5. Separate export buttons per toolbar: daily button in the main chart toolbar, hourly button in the HourlyChart header.
  6. Spend model confirmed (re-exporting a pair costs again).

Config (env, all optional → defaults)

VarDefaultScope
NEXT_PUBLIC_CSV_EXPORT_WINDOW_DAYS31client button-disable + server default
CSV_EXPORT_WINDOW_DAYSNEXT_PUBLIC_… ?? 31server override
CSV_EXPORT_MONTHLY_PAIR_LIMIT100server

Server is source of truth. Deprecated CSV_EXPORT_MAX_DAYS / CSV_EXPORT_MONTHLY_LIMIT read as fallbacks only.

Firestore (csvExportUsage/{uid})

{ currentMonth: 'YYYY-MM', pairsUsed: number, exportCount: number, lastExportAt }

pairsUsed is additive (missing → 0); no migration (Firestore convention).

Service layer — csv-export-usage-service.ts

  • getCsvWindowDays() → CSV_EXPORT_WINDOW_DAYS ?? NEXT_PUBLIC_CSV_EXPORT_WINDOW_DAYS ?? CSV_EXPORT_MAX_DAYS ?? 31.
  • getCsvMonthlyPairLimit(planType) → dev: MAX_SAFE_INTEGER; free: 0; else CSV_EXPORT_MONTHLY_PAIR_LIMIT ?? CSV_EXPORT_MONTHLY_LIMIT ?? 100.
  • checkCsvExportLimit(uid, planType, pairsRequested) → { allowed, pairsUsed, pairLimit, pairsRemaining, resetsAt } (allowed iff pairsUsed + pairsRequested ≤ limit).
  • recordCsvExport(uid, pairCount) → increment pairsUsed by pairCount, exportCount by 1 (monthly reset preserved).
  • getCsvExportUsageForProfile(uid, planType) → { pairsUsed, pairLimit, pairsRemaining, percentUsed, windowDays, resetsAt, currentMonth }.

Window helper (shared)

isWithinExportWindow(range, windowDays, now) and a countDistinctPairs(metrics) helper, used by both client (button state) and server (enforcement).

API — POST /api/premium/csv-export

Body gains granularity: 'daily' | 'hourly' and range: { start, end } (ISO). Flow:

  1. Auth → requireFeature('csv-export'). If hourly → also requireFeature('hourly-data').
  2. Validate range within window (getCsvWindowDays). Out → 400 RANGE_EXCEEDED.
  3. pairs = countDistinctPairs(metrics); checkCsvExportLimit(uid, plan, pairs). Over → 429 LIMIT_EXCEEDED with usage.
  4. daily → generateCsv(clientData) (current path). hourly → for each metric resolve hourly type+column (mirror useHourlyData), call new range queries, assemble ChartDataExport with ISO-timestamp points, generateCsv.
  5. recordCsvExport(uid, pairs) (skip for dev). Return CSV.

Range SQL — actions.ts

Add range variants beside each per-date hourly fn: getOhlcHourlyRange / getOrderBookHourlyRange / getQuotesHourlyRange / getTradesHourlyRange / getEntropyHourlyRange / getGiniHourlyRange / getErc20TransferVolumeHourlyRange — same SELECT but date BETWEEN ${start} AND ${end} ORDER BY hour_timestamp. Bounded concurrency across metrics.

Metric → hourly type mapping

Extract the mapping useHourlyData uses (metric.sourceType/metricType/side → HourlyDataType + column) into a shared module so client hook and server export agree.

UI

  • Daily button (toolbarButtons in ZealousExplorer): always visible.
    • no csv-export → disabled, tooltip "Upgrade to Pro to export data".
    • range outside window → disabled, tooltip "Adjust the date range to the last N days to export".
  • Hourly button: new onExportCsv/exportDisabled/exportTooltip props on HourlyChart, rendered in its <header>. Disabled (own tooltips) when missing csv-export/hourly-data or out of window. Exports the window for visible pairs.
  • Window days for client = NEXT_PUBLIC_CSV_EXPORT_WINDOW_DAYS ?? 31.

Profile redesign — (pages)/profile/page.tsx

Responsive 2-col grid (grid md:grid-cols-2 gap-4/6), single column on mobile. App tokens (bg-background, border-grey-100, text-white-100/64). Cards: Account/Plan, AI Token Usage, CSV Export Usage (now "X / N pairs", progress bar, reset date, window note), Preferences, Promo, Cookies, Sign out. Existing logic and useHasFeature/abort-controller fetch patterns preserved.

Docs + plans page

  • New frontend/docs/2. Product Overview/4. Data Export (CSV).md.
  • Add 'CSV Export' to dev-mode plan feature list in /api/stripe/plans. Production Stripe entitlement = devops follow-up (gate fails closed without it).

Tests

Extend existing vitest suites: window check, pair counting + budget spend, hourly granularity branch, range-query mapping, profile usage shape, button disabled states. Then /simplify, prettier, eslint.