Function queryVizData

src/bookmarks-viz.ts:273–428

()

}

async function queryVizData(): Promise<VizData> {
  const db = await openDb(twitterBookmarksIndexPath());

  try {
    const total = db.exec('SELECT COUNT(*) FROM bookmarks')[0]?.values[0]?.[0] as number;
    const authors = db.exec('SELECT COUNT(DISTINCT author_handle) FROM bookmarks')[0]?.values[0]?.[0] as number;
    const timelineRows = db.exec(
      `SELECT author_handle, posted_at, synced_at
       FROM bookmarks
       WHERE posted_at IS NOT NULL OR synced_at IS NOT NULL OR author_handle IS NOT NULL`
    );
    const timelineData = aggregateTimelineData(
      (timelineRows[0]?.values ?? []).map((row) => ({
        authorHandle: (row[0] as string) ?? undefined,
        postedAt: (row[1] as string) ?? null,
        syncedAt: (row[2] as string) ?? null,
      }))
    );

    const topAuthorsRows = db.exec(
      `SELECT author_handle, COUNT(*) as c FROM bookmarks
       WHERE author_handle IS NOT NULL
       GROUP BY author_handle ORDER BY c DESC LIMIT 20`
    );

    // Domains from links_json
    const domainRows = db.exec(
      `SELECT links_json FROM bookmarks WHERE links_json IS NOT NULL AND links_json != '[]'`
    );
    const domainCounts = new Map<string, number>();
    for (const row of domainRows[0]?.values ?? []) {
      try {
        const links = JSON.parse(row[0] as string) as string[];
        for (const link of links) {
          const url = typeof link === 'string' ? link : (link as any).expanded_url ?? (link as any).url ?? '';
          try {
            const domain = new URL(url).hostname.replace(/^www\./, '');
            if (domain && domain !== 'x.com' && domain !== 't.co') {
              domainCounts.set(domain, (domainCounts.get(domain) ?? 0) + 1);
            }
          } catch {}
        }
      } catch {}
    }
    const topDomains = [...domainCounts.entries()]
      .sort((a, b) => b[1] - a[1])
      .slice(0, 12)
      .map(([domain, count]) => ({ domain, count }));

    const mediaStats = {
      withMedia: db.exec('SELECT COUNT(*) FROM bookmarks WHERE media_count > 0')[0]?.values[0]?.[0] as number,
      withLinks: db.exec('SELECT COUNT(*) FROM bookmarks WHERE link_count > 0')[0]?.values[0]?.[0] as number,
      total,
    };

    const langRows = db.exec(
      `SELECT language, COUNT(*) as c FROM bookmarks WHERE language IS NOT NULL
       GROUP BY language ORDER BY c DESC LIMIT 8`