Insighttrack

---
name: insighttrack
description: >-
  Project skill for InsightTrack, a self-hosted web analytics platform
  (PostgreSQL writes + DuckDB reads, React/Vite dashboard, Express API,
  MCP/AI Analyst). Use whenever working in this repository — adding features,
  fixing bugs, reviewing code, writing tests, or answering questions about the
  architecture. Encodes the repo layout map (traffic / InsightTrack apps /
  appsv2), critical invariants, coding patterns, and workflows.
---

# InsightTrack Project Skill

InsightTrack is a self-hosted, privacy-friendly web analytics platform (a
Plausible/GA alternative). One Express backend ingests tracking events and
serves analytics; a React SPA renders the dashboard; an AI Analyst (LLM +
MCP tool layer) answers questions about the data in natural language.

## 1. The three synced copies (READ THIS FIRST)

The same product lives in **three places**, and they must be kept
**byte-identical** (modulo package names/paths) after every feature:

| Copy | Frontend | Backend | Extras |
|------|----------|---------|--------|
They live in **two separate git repos**, not one:

| Copy | Repo / path | Frontend | Backend | Extras |
|------|-------------|----------|---------|--------|
| 1 | `Personal/traffic/` — `github.com/NishikantaRay/traffic` | `analytics-dashboard/` | `analytics-db/` | legacy `analytics-server/` kept for reference |
| 2 | `Personal/InsightTrack/apps/` — `github.com/NishikantaRay/InsightTrack` | `dashboard-web/` | `analytics-api/` | — |
| 3 | `Personal/InsightTrack/appsv2/` — same repo as 2 | `dashboard-web/` | `analytics-api/` | `data-lake/` Parquet cold storage, `routes/sync.js`, `passmark-tests/` |

> Older notes call copies 2 and 3 `traffic2/`. **That directory does not
> exist.** The path is `Personal/InsightTrack/`, and it is a *different* GitHub
> repo from `traffic/` — so syncing means two commits and two pushes, not one.

Path translation when porting a change:

```
traffic/analytics-dashboard/…  ⇄  InsightTrack/{apps,appsv2}/dashboard-web/…
traffic/analytics-db/…         ⇄  InsightTrack/{apps,appsv2}/analytics-api/…
traffic/mcp-server/…           ⇄  InsightTrack/{apps,appsv2}/mcp-server/…
traffic/mcp-toolkit-core/…     ⇄  InsightTrack/{apps,appsv2}/mcp-toolkit-core/…
traffic/docs/…                 ⇄  InsightTrack/docs/…
```

**`package.json` is not a straight copy.** The three copies carry different
`version` values (traffic 1.0.0, both InsightTrack copies 1.0.1). Patch the
`scripts` / `dependencies` keys and leave `version` alone, or you silently
downgrade a copy.

After finishing a change in one copy, port it to the other two (`diff -rq`
between the `src/` trees to verify). `traffic/analytics-server/` is the
**legacy** backend (archived as `archive/analytics-api-legacy` in
InsightTrack) — do not add features there.

## 2. Golden rules (invariants — never break these)

1. **No SQL string interpolation, ever.** Parameterized queries only:
   `$1, $2…` for PostgreSQL, `?` for DuckDB.
2. **Writes → PostgreSQL only.** Tracking events, auth, sites, goals, team,
   settings. Never INSERT/UPDATE/DELETE directly in DuckDB (the sync engine
   owns DuckDB contents).
3. **Analytics reads → DuckDB only** (via `src/queries/queries.js`). Dashboard
   data never comes from PG.
4. **Every API route requires `authMiddleware`** except `/api/track/*`,
   `/api/auth/login`, `/api/auth/register`, `/api/health`, `/api/openapi.json`,
   and public share/invite endpoints. Site-scoped routes additionally chain
   `validateSiteId` + `authorizeSiteAccess` (checks `site_members` role).
5. **ES modules only** (`"type": "module"` everywhere). No `require()`.
6. **All UI supports dark mode** via Tailwind `dark:` variants and the custom
   theme tokens (`bg-card dark:bg-card-dark`, `text-text-primary
   dark:text-text-primary-dark`, …).
7. **Data fetching in React goes through `useAnalytics(endpoint)`** (or
   `useRealtime`), not raw `useEffect` + axios.
8. **Never leak internals in errors.** Use `safeMsg`/`sendError` from
   `src/utils/safeError.js`; response envelope is
   `{ success: true, data }` / `{ success: false, error }`.
9. **Update `docs/` after completing any feature** (and create a doc for
   significant new features).
10. **Port every change to all three copies** (section 1).
11. **The public site ships via `npm run build:seo`, not `npm run build`.**
    Plain `build` emits one empty-shell page and silently de-indexes the whole
    site. See section 6.
12. **SerpApi is reached ONLY through `services/serpapi/client.js`** — the one
    module holding `SERPAPI_KEY` or doing outbound HTTP. Everything above it
    (`normalize` → `searchVisibilityService` → `correlationService`) is pure or
    DB-only, which is what keeps the correlation testable without spending
    credits. With no key set the client serves `fixtures/serp/*.json` and stamps
    `source: 'fixture'`; never present fixture data as live.
13. **`correlationService.js` stays pure** — no DB, no network:
    `(trafficWeeks, keywordFindings) → Finding`. Attribution is a deterministic
    rule cascade, not an LLM, and rule 7 (`unexplained_by_serp`) must keep
    existing: a correlation tool that always finds a SERP cause is guessing.
14. **Public-facing claims must match measured evidence.** `README.md`,
    `public/llms.txt`, `public/index.md`, `index.html` JSON-LD, and
    `pages/Landing.jsx` all make privacy and performance claims. The audits in
    `docs/PERFORMANCE_BENCHMARK_AUDIT.md` and `docs/REPOSITORY_AUDIT.md` grade
    them — "10–100× faster" and "GDPR-compliant, no cookie banner" are both
    **unsupported** and must stay hedged. Update every surface together.

## 3. Architecture in one page

```
tracker.js on customer site
        │  POST /api/track/event  (public CORS, rate-limited)
        ▼
Express backend (port 3001)  src/index.js
        │  trackingService → INSERT INTO PostgreSQL (events, sessions)
        ▼
PostgreSQL 16 ──(sync engine, every 60 s + on startup)──▶ DuckDB
   writes         src/sync/sync.js: incremental high-water    reads
                  mark in _sync_meta, duckBulkInsert 1000-row
                  chunks, daily_stats rollups, optional S3/R2
                  Parquet cold storage + union views
        ▲                                            │
        │ auth/sites/goals/team (services/*)         │ queries/queries.js
        │                                            ▼
React dashboard (port 4173) ── axios services/api.js ── /api/analytics/:siteId/*
   Zustand stores · useAnalytics hook (60 s refresh) · Recharts · Tailwind

AI Analyst: /api/assistant (SSE streaming) + /api/mcp (connect tokens)
   └─ shared tool registry (src/mcp/tools/registry.js) — same tools power the
      in-dashboard panel and the external MCP server (mcp-server/, stdio proxy
      that calls the HTTP API; it never touches the DB directly)
```

Key facts:
- **Hot/cold data**: DuckDB tables `events_hot`/`sessions_hot` hold recent
  days; older data lives in Parquet partitions under `data-lake/`. Views
  `events` and `sessions` UNION both — query code always uses the views.
- **daily_stats rollup**: one row per site per day, recomputed after each
  sync; KPI queries use it for ranges > 1 day, raw events for today.
- **Caching**: `analyticsCache.getOrFetch(key, ttl, fn)` — coalesced
  (thundering-herd-proof) in-memory cache; TTLs in `CACHE_TTL`.
- **Auth**: JWT (Bearer), bcryptjs; multi-user sites via `site_members` with
  roles `owner | admin | viewer` (`req.userRole` after `authorizeSiteAccess`).
- **Secrets at rest** (BYO AI keys): `src/utils/secretBox.js` (AES-256-GCM,
  key derived from `ENCRYPTION_KEY` or `JWT_SECRET`).
- **CORS is two-tier**: `publicCors` for `/api/track`, `privateCors`
  (env `CORS_ORIGINS` allowlist) for everything else.
- **Date ranges**: strings like `'24h' | '7d' | '30d' | '90d'` or
  `'custom:YYYY-MM-DD:YYYY-MM-DD'`, parsed by `getDateRange()` in queries.js.

Deep dive: read [references/architecture.md](references/architecture.md).

## 4. Where things live

Backend (`analytics-db/` or `apps*/analytics-api/`):

```
src/index.js        Express app: CORS, helmet, rate limit, route mounts, sync loop
src/db/             postgres.js (pool + idempotent schema/migrations), duckdb.js (conn pool, duckAll/duckRun/duckBulkInsert)
src/routes/         One router per domain: analytics, sites, tracking, auth, goals, reporting, sqlEditor, team, mcp, assistant, searchVisibility
src/services/       PG-backed business logic (sitesService, teamService, trackingService, authService, …)
                    searchVisibility: serpapi/{client,normalize}.js, searchVisibilityService.js, correlationService.js
fixtures/serp/      Scrubbed sample SERPs — served when SERPAPI_KEY is unset (fixture mode)
src/queries/        queries.js — ALL DuckDB analytics SQL lives here
src/schema/         schema.js — DuckDB DDL (SCHEMA_SQL) + SYNCABLE_TABLES
src/sync/           sync.js — PG→DuckDB incremental sync + rollups
src/storage/        s3.js — S3/R2 Parquet cold storage (httpfs)
src/mcp/            AI toolkit: tools/registry.js, llm/provider.js, openapi/mapper.js, connect/signing.js
src/middleware/     auth.js (authMiddleware)
src/utils/          safeError.js, secretBox.js
tests/              Vitest + testHelper.js (real PG, site_test% cleanup)
```

Frontend (`analytics-dashboard/` or `apps*/dashboard-web/`):

```
src/App.jsx             Lazy routes + ProtectedRoute/GuestRoute/SiteGate
src/pages/              One PascalCase component per route (Dashboard, Audience, SqlEditor, …)
src/components/ui/      Shared primitives: ChartCard, MetricCard, DataTable, EmptyState, LoadingSkeleton, InfoTooltip
src/components/charts/  Recharts wrappers (TrafficChart, SourcesChart, …)
src/components/layout/  DashboardLayout, Sidebar, Navbar
src/components/assistant/  AI Analyst panel
src/hooks/              useAnalytics.js (data fetching), useSeo.js
src/store/              Zustand: useAuthStore, useSiteStore, useDateFilterStore, useThemeStore, useAssistantStore, …
src/services/           api.js (axios instance + endpoint maps), assistantStream.js (SSE), aiSettings.js
src/utils/              formatters.js (formatNumber/Duration/Percent/Date, CHART_COLORS, DATE_RANGES), exportUtils.js (CSV/JSON/PNG)
src/__tests__/          Vitest + Testing Library unit tests
e2e/                    Playwright specs (*.spec.ts)
```

Naming: camelCase functions/variables, PascalCase components,
`use<Thing>Store` for Zustand stores, `use<Thing>` for hooks, kebab-case
URLs/API paths, `<domain>Service.js`, localStorage keys prefixed `analytics-`.

## 5. Canonical patterns (copy these shapes)

New analytics endpoint (read path) — three edits:

```js
// 1. src/queries/queries.js — parameterized DuckDB query
export async function getMyMetric(siteId, dateRange = '30d') {
    const { start, end } = getDateRange(dateRange);
    return duckAll(
        `SELECT path, COUNT(*) AS views FROM events
         WHERE site_id = ? AND timestamp BETWEEN ? AND ?
         GROUP BY path ORDER BY views DESC`,
        [siteId, start, end],
    );
}

// 2. src/routes/analytics.js — route with cache + envelope
router.get('/:siteId/my-metric', validateSiteId, async (req, res) => {
    try {
        const { dateRange = '30d' } = req.query;
        const cacheKey = analyticsCache.key('my-metric', req.siteId, dateRange);
        const data = await cachedQuery(cacheKey, CACHE_TTL.GENERAL, () =>
            queries.getMyMetric(req.siteId, dateRange));
        res.json({ success: true, data });
    } catch (error) {
        console.error('Error fetching my-metric:', error);
        res.status(500).json({ success: false, error: safeError(error) });
    }
});

// 3. dashboard src/services/api.js — endpoint map entry
getMyMetric: (siteId, dateRange) =>
    api.get(`/analytics/${siteId}/my-metric`, { params: { dateRange } }),
```

Consuming it in a page:

```jsx
const { data, loading, error } = useAnalytics('getMyMetric');
return (
    <ChartCard title="My Metric" loading={loading} error={error} empty={!data?.length}>
        {/* Recharts chart using CHART_COLORS */}
    </ChartCard>
);
```

Write path (PG): route → `<domain>Service.js` → `query('… $1 …', [v])`.
Services return plain objects and throw `Error` with `.status`; routes catch
and respond. Never put SQL in route files.

More full examples (stores, tracking, team roles, MCP tools, tests):
[references/patterns.md](references/patterns.md).

## 6. Commands

Run from the package directory (`analytics-dashboard`/`analytics-db` in
traffic; `apps/dashboard-web`/`apps/analytics-api` in InsightTrack):

```bash
npm run dev            # frontend: Vite dev server | backend: node --watch
npm start              # backend: production start (runs sync loop)
npm test               # Vitest (both packages)
npx playwright test    # frontend e2e
npm run migrate && npm run seed && npm run init   # backend: PG schema, sample data, DuckDB init
npm run sync -- --full # full PG→DuckDB re-sync
docker-compose up --build    # full stack from repo root
```

### Building the public site (SEO) — use `build:seo`, never `build`

`npm run build` is a plain Vite build: it emits **one** `index.html` with an
empty `<div id="root">`. Crawlers that don't execute JS — Bing and every AI
crawler allowlisted in `robots.txt` (GPTBot, ClaudeBot, PerplexityBot) — then
see **zero content**, and every URL shares the home page's title and canonical.

```bash
npm run build:seo   # vite build → install chromium → OG cards → RSS → sitemap → prerender
```

The pipeline (`package.json` scripts, all in `dashboard-web`/`analytics-dashboard`):

| Step | Script | Output |
|------|--------|--------|
| 1 | `vite build` | SPA bundle |
| 2 | `prerender:install` | Chromium for Playwright (idempotent; ~0.3s when cached) |
| 3 | `og:images` → `scripts/og-images.mjs` | `dist/og/<slug>.png`, one card per post |
| 4 | `feed` → `scripts/feed.mjs` | `dist/feed.xml` (RSS 2.0) |
| 5 | `sitemap` → `scripts/sitemap.mjs` | `dist/sitemap.xml`, every URL with a `<lastmod>` |
| 6 | `prerender` → `scripts/prerender.mjs` | real HTML per route + `dist/404.html` |

Rules that keep it correct:

- **Routes are derived from `BLOG_POSTS` and `getTags()`**, never hardcoded.
  Adding a post automatically prerenders it, generates its OG card, and adds a
  feed item and a sitemap entry. A hardcoded list goes stale silently — the
  sitemap was hand-maintained once and drifted into 8 URLs with no `<lastmod>`.
- **An FAQ section becomes FAQPage schema automatically.** A `## Frequently
  asked questions` heading with `### ` questions under it is parsed by
  `extractFaq()` in `src/pages/Blog.jsx` and emitted as FAQPage JSON-LD. It is
  derived from the body, never duplicated in post data, because marked-up
  answers must match the visible text. Posts without the section emit no
  FAQPage node. **Google retired the FAQ rich result** (gone from Search by
  May 2026; FAQPage is no longer in the rich-results gallery), so this earns no
  Google snippet — it is kept for Bing and the AI answer engines. Write FAQ
  sections because the questions are worth answering, not for rich results.
- **`updated` drives freshness.** Setting `updated: 'YYYY-MM-DD'` on a post
  feeds `dateModified`, the sitemap `<lastmod>`, and a visible "Updated …" line
  in `PostMeta`. Set it whenever a post is materially rewritten — `lastmod` is
  only used by Google when it is verifiably accurate, and the visible line is
  what keeps the markup honest. Schema dates carry an explicit `+00:00` offset
  via `isoDate()`.
- **Every public URL ends in a trailing slash.** The host serves
  `dist/<path>/index.html` and 308-redirects the bare path to it, so a canonical
  without the slash names a URL that immediately redirects. `canonicalUrl()` in
  `src/hooks/useSeo.js` is the single place that builds public URLs — canonical,
  `og:url`, and the Article/Breadcrumb JSON-LD all go through it, and
  `scripts/sitemap.mjs` mirrors it. Image URLs (`/og/<slug>.png`) are files, not
  routes, and must **not** gain a slash.
- **`scripts/prerender.mjs` self-checks and exits 1** if any page has an empty
  root, a duplicate `title`/`canonical`/`og:title`/`og:url`, a canonical
  missing its trailing slash, or if `404.html` stops looking like a 404. A
  broken SEO build fails the deploy instead of shipping.
- **The SPA fallback must serve the pristine shell**, captured before any route
  overwrites `dist/index.html`. Serving the file from disk hands later routes a
  pre-rendered landing page to hydrate.
- **`dist/404.html` comes from the `/__not-found__` route**, which must stay
  *outside* the `ProtectedRoute` catch-all in `App.jsx`. Inside it, a signed-out
  visitor hitting an unknown URL is redirected to the landing page — a soft 404
  that returns 200 with duplicate content.
- **Cloudflare Pages build command must be `npm run build:seo`.** Root
  directory `analytics-dashboard`, output `dist`.

### Blog content (`src/data/blogPosts.js`)

One array of post objects; `slug`, `title`, `description`, `keyword`, `date`,
`readingMinutes`, `tags`, `body` are all required (`blogSeo.test.jsx` enforces
this). `body` is markdown rendered by the small renderer in `pages/Blog.jsx` —
it supports headings, tables, code fences, lists, `**bold**`, `` `code` ``,
`*italic*`, and `[text](/blog/slug)` links, and nothing else.

- **Every post must link out to, and be linked from, another post.** The test
  suite fails on orphans and on broken or self-referential links.
- **Never anchor a script on `grep '^];$'`** to find the array terminator —
  posts contain JS code samples with `];` inside code fences. Match the *last*
  occurrence.
- Editing post prose? Watch backtick escaping: inline code is stored as
  `\`` (backslash-backtick) inside the template literal.

Backend tests need PostgreSQL running (they use the real `analytics_db` and
clean up `site_test%` rows via `tests/testHelper.js`).

## 7. Verify against code, not prose (how to not get misled)

Docs and checklists in this repo have drifted before (e.g. references to an
`authenticateToken` middleware that is actually `authMiddleware`, or a
`getCached/setCached` cache API that is actually `analyticsCache.getOrFetch`).
When this skill, a doc, and the code disagree — **the code wins**. Before
building on any claimed name or pattern, ground yourself:

```bash
# The five files that define the backend's truth
src/index.js  src/middleware/auth.js  src/services/cache.js  src/db/duckdb.js  src/schema/schema.js

# The four files that define the frontend's truth
src/App.jsx  src/services/api.js  src/hooks/useAnalytics.js  tailwind.config.js

# Confirm a symbol actually exists before using it
grep -rn "authMiddleware\|getOrFetch" src/ --include='*.js' -l

# Confirm copy drift before and after any change
diff -rq traffic/analytics-db/src InsightTrack/apps/analytics-api/src
```

Copy an existing neighbor (the route above yours, the store next to yours)
rather than writing from memory — every layer has a canonical local example.
If you find drift between this skill and the code, fix the skill in the same
change.

## 8. Things to avoid

- Adding npm dependencies for anything achievable with the existing stack.
- Querying PG for dashboard analytics, or DuckDB for writes.
- Raw `useEffect` + axios data fetching in components.
- Hard-coded hex colors in JSX — use Tailwind theme tokens / `CHART_COLORS`.
- Echoing request URLs, stack traces, paths, or env names in API errors.
- Editing `dist/`, `node_modules/`, `duckdb/*.duckdb`, or `data-lake/` files.
- Touching the legacy `analytics-server/` (traffic) / `archive/` (InsightTrack).
- Skipping the three-copy sync or the `docs/` update after a feature.

## 9. Workflows & further reading

- Feature development, testing, debugging, and the three-copy sync procedure:
  [references/workflows.md](references/workflows.md)
- Deep architecture (request lifecycle, sync engine, hot/cold storage, AI
  Analyst/MCP): [references/architecture.md](references/architecture.md)
- Full code examples per layer + utilities catalog:
  [references/patterns.md](references/patterns.md)
- Code review, PR review, and security audit (threat model, checklists,
  report formats): [references/review-security.md](references/review-security.md)
- Product/feature docs: `docs/` — index at `docs/index.md` (e.g.
  `sql-editor.md`, `custom-dashboards.md`, `reporting-studio.md`,
  `ai-analyst.md`, `mcp-toolkit.md`, `pg-duckdb-sync.md`, `security.md`,
  `testing.md`)