Article analytics dashboard for writers #114

Activity

1. kingRayhan

   added

   enhancementNew feature or requestNew feature or request

   on Apr 5, 2026
2. kingRayhan commented on Apr 5, 2026

   

   kingRayhan

   on Apr 5, 2026

   MemberAuthor

   More actions

   Implementation Plan: ClickHouse for Article Analytics

   Why ClickHouse?

   PostgreSQL (current DB) is not suited for analytics — counting millions of page view events with time-series aggregation is slow and pollutes the main DB. ClickHouse is purpose-built for this:

   • Columnar storage — aggregations over millions of rows are instant
   • Append-only inserts — perfect for event streams (page views)
   • Time-series queries — built-in date functions, fast GROUP BY toDate(timestamp)
   • Free tier available — ClickHouse Cloud has a generous free tier, or self-host via Docker
   • No impact on the main PostgreSQL DB

   ---

   Architecture

   Browser
     └── POST /api/analytics/pageview   (lightweight API route)
           └── ClickHouse: INSERT INTO article_views
                 └── (batch insert via @clickhouse/client)
   
   Dashboard page
     └── Server Action: getArticleAnalytics(articleId)
           └── ClickHouse: SELECT toDate(viewed_at), count() GROUP BY 1
   

   ---

   ClickHouse Schema

   CREATE TABLE article_views (
     article_id   UUID,
     viewer_id    Nullable(UUID),   -- NULL for anonymous
     session_id   String,           -- fingerprint / session token hash
     referrer     Nullable(String),
     country_code Nullable(String), -- from IP geo (optional)
     viewed_at    DateTime DEFAULT now()
   ) ENGINE = MergeTree()
   PARTITION BY toYYYYMM(viewed_at)
   ORDER BY (article_id, viewed_at);

   Use MergeTree — fast inserts, efficient range scans by article_id + viewed_at.

   ---

   New Files

   src/backend/persistence/
     clickhouse.client.ts          # ClickHouse client singleton
   
   src/backend/services/
     analytics.actions.ts          # Server actions for querying analytics
   
   src/app/api/analytics/
     pageview/route.ts             # POST endpoint — receives view events
   
   src/components/
     ArticleAnalyticsChart.tsx     # Recharts time-series chart (client component)
   
   src/app/(dashboard-editor)/dashboard/
     analytics/[articleId]/page.tsx
   

   ---

   Environment Variables to Add

   CLICKHOUSE_HOST=
   CLICKHOUSE_DATABASE=
   CLICKHOUSE_USERNAME=
   CLICKHOUSE_PASSWORD=
   

   ---

   Tracking Logic

   Client-side — fire-and-forget fetch on article page load:

   // src/app/[username]/[articleHandle]/page.tsx (or a client component)
   useEffect(() => {
     fetch('/api/analytics/pageview', {
       method: 'POST',
       body: JSON.stringify({ article_id, session_id: getSessionId() }),
     });
   }, []);

   API route — inserts into ClickHouse, returns 200 immediately (no await needed by client):

   // POST /api/analytics/pageview
   export async function POST(req: Request) {
     const { article_id, session_id } = await req.json();
     await clickhouseClient.insert({
       table: 'article_views',
       values: [{ article_id, session_id, viewed_at: new Date() }],
       format: 'JSONEachRow',
     });
     return new Response(null, { status: 200 });
   }

   Deduplication — count unique session_id per article per day to avoid reload inflation:

   SELECT toDate(viewed_at) as date, uniq(session_id) as views
   FROM article_views
   WHERE article_id = {articleId}
     AND viewed_at >= now() - INTERVAL 30 DAY
   GROUP BY date
   ORDER BY date

   ---

   Analytics Server Action

   // src/backend/services/analytics.actions.ts
   export async function getArticleAnalytics(articleId: string) {
     const userId = await authID();
     // verify ownership before returning data
   
     const rows = await clickhouseClient.query({
       query: `
         SELECT toDate(viewed_at) as date, uniq(session_id) as views
         FROM article_views
         WHERE article_id = {articleId: UUID}
           AND viewed_at >= now() - INTERVAL 30 DAY
         GROUP BY date ORDER BY date
       `,
       query_params: { articleId },
       format: 'JSONEachRow',
     });
   
     return rows.json();
   }

   ---

   Dashboard UI

   • Time-series line chart using Recharts (already likely in deps via shadcn)
   • Summary cards: total views (all time), views this month, reactions, bookmarks
   • Reactions + bookmarks still queried from PostgreSQL via existing repositories
   • Date range picker (7d / 30d / 90d / all time)

   ---

   Package

   bun add @clickhouse/client

   ---

   Rollout Order

   1. Set up ClickHouse Cloud instance + create table
   2. Add clickhouse.client.ts
   3. Build POST /api/analytics/pageview route
   4. Add tracking call to article detail page
   5. Build analytics.actions.ts query
   6. Build dashboard UI page + chart component
   7. Add env vars to production
3. kingRayhan

   added

   Feature

   on Apr 5, 2026
4. shoaibsharif

   assigned

   Copilot

   and

   shoaibsharif

   on Apr 5, 2026
5. Copilot

   linked a pull request that will close this issue[WIP] Add article analytics dashboard for writers #124on Apr 5, 2026
6. kingRayhan commented on Apr 5, 2026

   

   kingRayhan

   on Apr 5, 2026

   MemberAuthor

   More actions

   #125