Storage Flow

The Complete Storage Pipeline

When a feed is refreshed, here’s exactly what happens from HTTP request to database record:

1. Trigger (manual / scheduled)
         ↓
2. Fetch XML from feed URL
         ↓
3. Parse XML → ParsedFeed object
         ↓
4. Upsert feed metadata (title, siteUrl, favicon)
         ↓
5. Load existing GUIDs for this feed
         ↓
6. Filter out duplicate articles (by GUID)
         ↓
7. Sanitize HTML content for each new article
         ↓
8. Batch insert new articles into SQLite
         ↓
9. Update feed.lastFetchedAt timestamp
         ↓
10. Invalidate TanStack Query cache on frontend

Step 4: Upsert Feed Metadata

After parsing, the feed’s own metadata (title, link, favicon) is updated if it has changed:

await db
  .insert(feeds)
  .values({
    url:      feedUrl,
    title:    parsed.title,
    siteUrl:  parsed.url,
  })
  .onConflictDoUpdate({
    target: feeds.url,
    set: {
      title:   parsed.title,
      siteUrl: parsed.url,
    },
  });

Step 8: Batch Insert

Articles are inserted in a single batch transaction for performance:

await db.transaction(async (tx) => {
  for (const article of newArticles) {
    await tx.insert(articles).values({
      feedId:      feedId,
      guid:        article.guid,
      title:       article.title,
      url:         article.url,
      content:     sanitizeHtml(article.content ?? ''),
      summary:     article.summary,
      author:      article.author,
      publishedAt: article.publishedAt,
    });
  }
});

Using a transaction ensures that either all new articles are inserted, or none are, preventing partial updates if the process crashes.

Step 10: Cache Invalidation

When new articles are stored, the frontend needs to know. SparkFeed uses Server-Sent Events (SSE) to notify the client:

// Server: emit event when feed refresh completes
sendEvent(event, {
  type: 'feed:refreshed',
  data: { feedId, newArticleCount },
});

// Client: listen for events via TanStack Query
useEffect(() => {
  const source = new EventSource('/api/events');
  source.addEventListener('feed:refreshed', ({ data }) => {
    const { feedId } = JSON.parse(data);
    queryClient.invalidateQueries({ queryKey: ['articles', feedId] });
  });
  return () => source.close();
}, []);

This triggers a background refetch of the article list, updating the UI without a full page reload.

Transaction Safety

SQLite handles concurrent writes safely via WAL mode (Write-Ahead Logging). Even if a refresh runs while you’re reading, your reads are always consistent. They see either the pre-refresh or post-refresh state, never a partial update.

-- Applied on DB initialization
PRAGMA journal_mode = WAL;