Improvement: Analytics sync endpoint has no pagination, N+1 queries, and memory spike risk on 512MB plan

[ChitkulLakshya]

Improvement: Analytics sync endpoint fetches all data in one batch (memory spike on large datasets)

File: server/index.js:2051-2101

Problem

The POST /api/analytics/sync endpoint fetches ALL drafts and ALL submissions from Supabase in a single query, then iterates through them sequentially:

const { data: drafts, error: draftsError } = await supabase
  .from('form_drafts')
  .select('*')
  .eq('user_id', req.user.uid);

for (const draft of (drafts || [])) {
  await TemplateData.findOneAndUpdate(...);
}

const { data: submissions, error: subError } = await supabase
  .from('form_submissions')
  .select('*')
  .eq('user_id', req.user.uid);

for (const sub of (submissions || [])) {
  const exists = await TemplateSubmission.findOne({ submissionId: sub.submission_id });
  if (!exists) {
    await TemplateSubmission.create(...);
  }
}

Issues

1. Memory: Loads all data into memory at once. On a 512MB Render plan, a user with thousands of submissions could cause OOM.
2. N+1 queries: Each submission does a findOne check before create — that's 2 DB calls per submission.
3. No pagination: Supabase default page size is 1000 rows. If a user has >1000 drafts or submissions, only the first 1000 are synced.
4. No progress feedback: Long syncs show no progress to the user.

Fix

1. Paginate Supabase queries (.range(0, 999), .range(1000, 1999), etc.)
2. Use upsert instead of findOne + create to halve DB calls
3. For bulk submissions, use insertMany with ordered: false and rawResult: true
4. Consider streaming or batch processing for large datasets

Severity

Medium — Works for small datasets but will fail or timeout for users with 1000+ submissions. Critical for 512MB Render plan.

Phase

Introduced in Phase 1 (PR #26, merged).