Telemetry Improvements Implementation Plan¶

For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (- [ ]) syntax for tracking.

Goal: Close the instrumentation gaps found in the 2026-07-21 telemetry investigation, persist weekly aggregate rollups past WAE's 3-month retention, and replace the bash report with a readable Python report.

Architecture: Three phases, one PR each, per docs/superpowers/specs/2026-07-21-telemetry-improvements-design.md: (A) frontend track() call sites + four new allowlisted count-only events; (B) a route-less cron Worker (phonolex-analytics-rollup) that aggregates both WAE datasets into a new phonolex-analytics D1 weekly; (C) analytics-report.py reading live WAE + D1 history.

Tech Stack: React/TS (frontend, vitest + RTL), Hono-less plain Worker (rollup), Workers Analytics Engine SQL API, D1, Python (report, pytest).

Global Constraints¶

No free text in analytics, ever. New props are forbidden; new events are count-only (props: {}). The allowlist in packages/web/workers/src/config/analyticsEvents.ts stays the single source of truth.
error_shown categories are a closed set: crash | api_error | export_failed | audio. Never pass an error message as a prop.
Tool id values in props: wordLists, contrastive, sentences, textAnalysis, lookup, audio, editor, therapyPack (match existing search_executed/results_exported usage, NOT the governedGeneration sidebar id).
Rollup rows are aggregate-only. No session/device value (blob3) may appear in any INSERT parameter. COUNT(DISTINCT blob3) collapses to a number before write.
WAE query rules: counts via SUM(_sample_interval); numeric double slots use -1 = absent, so filter doubleN >= 0; blob layout is blob1=name blob2=env blob3=session blob4=tool blob5=format blob6=category blob7=outcome, doubles double1=ts … double4=elapsed_ms … double6=duration_ms.
D1 limit: ≤100 bind params per statement (rollup inserts are one row per statement — always under).
Branches off develop; before every push run the CI-equivalent checks for the touched package (frontend: npm run type-check && npm run lint && npm test && npm run build; workers: npm run type-check && npm test; python: the pytest command in the task).
Jira: verify/create a PHON ticket for this work with JQL before opening PRs (highest key was PHON-199 on 2026-07-21 — verify, don't assume).

Phase A — Instrumentation (branch `feature/telemetry-instrumentation`, one PR to develop)¶

Setup:

cd /Users/jneumann/Repos/PhonoLex && git checkout develop && git pull && git checkout -b feature/telemetry-instrumentation

Task 1: Allowlist the four Materials Editor lifecycle events (worker)¶

Files: - Modify: packages/web/workers/src/config/analyticsEvents.ts (after the pack_seeded entry, ~line 71) - Test: packages/web/workers/src/__tests__/events.test.ts

Interfaces: - Produces: event names pack_created, pack_opened, pack_duplicated, pack_deleted accepted by POST /api/events (count-only). Task 2's frontend calls depend on these names exactly.

[ ] Step 1: Write the failing tests (append inside the existing allowlist describe block in events.test.ts, reusing its post/envelope helpers):

it('accepts the Materials Editor lifecycle count-only events', async () => {
  const res = await post(envelope([
    { name: 'pack_created' },
    { name: 'pack_opened' },
    { name: 'pack_duplicated' },
    { name: 'pack_deleted' },
  ]));
  expect(res.status).toBe(202);
  expect(await res.json()).toMatchObject({ accepted: 4, dropped: 0 });
});

it('still drops the reserved PHON-177 funnel names', async () => {
  const res = await post(envelope([{ name: 'pack_started' }, { name: 'pack_exported' }]));
  expect(await res.json()).toMatchObject({ accepted: 0, dropped: 2 });
});

[ ] Step 2: Run to verify failure

Run: cd packages/web/workers && npx vitest run src/__tests__/events.test.ts Expected: FAIL — lifecycle events dropped (accepted: 0, dropped: 4).

[ ] Step 3: Implement — in analyticsEvents.ts, directly after the pack_seeded entry:

  /** 2026-07-21 telemetry spec — Materials Editor lifecycle. All count-only.
   *  pack_created = BLANK creation (newPack); the guided path keeps pack_seeded.
   *  Names deliberately avoid the reserved PHON-177 funnel set. */
  pack_created: { props: {} },
  /** Saved materials reopened from My Materials. */
  pack_opened: { props: {} },
  pack_duplicated: { props: {} },
  pack_deleted: { props: {} },

[ ] Step 4: Run to verify pass

Run: npx vitest run src/__tests__/events.test.ts — Expected: PASS (both new tests; reserved-names test passes because those names were never added).

[ ] Step 5: Commit

git add packages/web/workers/src/config/analyticsEvents.ts packages/web/workers/src/__tests__/events.test.ts
git commit -m "feat(analytics): allowlist Materials Editor lifecycle events"

Task 2: Fire lifecycle events from the pack store¶

Files: - Modify: packages/web/frontend/src/store/packStore.ts (actions newPack, loadPack, duplicatePack, deletePack, startOver, renamePack) - Test: packages/web/frontend/src/store/packStore.test.ts

Interfaces: - Consumes: event names from Task 1; track(name: string, props?: Record<string, string | number>): void from src/lib/analytics.ts. - Produces: every pack lifecycle action self-reports — no call-site changes needed anywhere else (root fix; createSeededPack stays untracked because WelcomeView already fires pack_seeded).

[ ] Step 1: Write the failing tests. At the top of packStore.test.ts, add the mock (before other imports execute) and import:

vi.mock('../lib/analytics', () => ({ track: vi.fn() }));
import { track } from '../lib/analytics';

(add vi to the existing vitest import). In the existing beforeEach, add vi.mocked(track).mockClear();. Append a describe block:

describe('lifecycle analytics (2026-07-21 telemetry spec)', () => {
  it('newPack fires pack_created', () => {
    store.getState().newPack();
    expect(track).toHaveBeenCalledWith('pack_created');
  });

  it('loadPack fires pack_opened only when the pack exists', async () => {
    store.getState().newPack();
    const id = store.getState().activePack!.id;
    store.setState({ activePack: null });
    vi.mocked(track).mockClear();
    await store.getState().loadPack(id);
    expect(track).toHaveBeenCalledWith('pack_opened');
    vi.mocked(track).mockClear();
    await store.getState().loadPack('no-such-id');
    expect(track).not.toHaveBeenCalled();
  });

  it('duplicatePack / deletePack fire their events', async () => {
    store.getState().newPack();
    const id = store.getState().activePack!.id;
    await store.getState().duplicatePack(id);
    expect(track).toHaveBeenCalledWith('pack_duplicated');
    await store.getState().deletePack(id);
    expect(track).toHaveBeenCalledWith('pack_deleted');
  });

  it('startOver and renamePack count as pack_edited', async () => {
    store.getState().newPack();
    const id = store.getState().activePack!.id;
    store.getState().startOver();
    expect(track).toHaveBeenCalledWith('pack_edited');
    vi.mocked(track).mockClear();
    await store.getState().renamePack(id, 'Renamed');
    expect(track).toHaveBeenCalledWith('pack_edited');
  });
});

[ ] Step 2: Run to verify failure

Run: cd packages/web/frontend && npx vitest run src/store/packStore.test.ts — Expected: FAIL (track never called).

[ ] Step 3: Implement in packStore.ts: add import { track } from '../lib/analytics'; and the calls —

    newPack(target = null) {
      const pack = createEmptyPack(newId(), Date.now());
      pack.target = target;
      commit(pack);
      track('pack_created');
    },

    async loadPack(id) {
      const raw = await packDb.getPack(id);
      if (raw) {
        set({ activePack: migratePack(raw) });
        track('pack_opened');
      }
    },

In duplicatePack, after await get().refreshLibrary(); add track('pack_duplicated');. In deletePack, after await get().refreshLibrary(); add track('pack_deleted');. In startOver, after the commit(...) add track('pack_edited');. In renamePack, add track('pack_edited'); in BOTH branches (after commit(...) in the active-pack branch, after await get().refreshLibrary(); in the library branch).

[ ] Step 4: Run to verify pass — npx vitest run src/store/packStore.test.ts → PASS. Also run npx vitest run src/components/pack to confirm no existing pack test regressed (PackEditor tests mock analytics already).
[ ] Step 5: Commit

git add packages/web/frontend/src/store/packStore.ts packages/web/frontend/src/store/packStore.test.ts
git commit -m "feat(analytics): fire pack lifecycle events from the pack store"

Task 3: Contrast Sets `search_executed` + `api_error`¶

Files: - Modify: packages/web/frontend/src/components/tools/ContrastiveInterventionTool.tsx (handlers handleGenerateMinimal, handleGenerateMaximalPairs, handleGenerateWordLists, handleGenerateMultiple, ~lines 148–295) - Test: packages/web/frontend/src/components/tools/ContrastiveInterventionTool.test.tsx

Interfaces: - Consumes: track from ../../lib/analytics. - Produces: search_executed with tool: 'contrastive' from the three results-producing handlers (minimal, word-lists step of maximal, multiple); error_shown {tool:'contrastive', category:'api_error'} from all four catch blocks. This is Phase A's representative api_error test site.

[ ] Step 1: Write the failing tests. At the top of the test file add:

vi.mock('../../lib/analytics', () => ({ track: vi.fn() }));
import { track } from '../../lib/analytics';

Append a describe (reuse the file's w() word helper and minimal-search pattern):

describe('ContrastiveInterventionTool analytics (2026-07-21 telemetry spec)', () => {
  beforeEach(() => vi.mocked(track).mockClear());

  const runMinimalSearch = () => {
    const inputs = screen.getAllByPlaceholderText(/e\.g\./i);
    fireEvent.change(inputs[0], { target: { value: 'p' } });
    fireEvent.change(inputs[1], { target: { value: 'b' } });
    fireEvent.click(screen.getByRole('button', { name: /find contrasts/i }));
  };

  it('fires search_executed on a successful minimal-pair search', async () => {
    const w = (word: string) =>
      ({ word, has_phonology: true, ipa: word, phonemes: word.split(''), syllables: null,
         phoneme_count: 3, syllable_count: 1 }) as never;
    vi.spyOn(api, 'getMinimalPairs').mockResolvedValue([
      { word1: w('pat'), word2: w('bat'), position: 0, position_type: 'initial', phoneme1: 'p', phoneme2: 'b' },
    ]);
    render(<ContrastiveInterventionTool />);
    runMinimalSearch();
    await waitFor(() =>
      expect(track).toHaveBeenCalledWith('search_executed', expect.objectContaining({
        tool: 'contrastive', result_count: 1,
      })));
  });

  it('fires error_shown api_error when the search fails', async () => {
    vi.spyOn(api, 'getMinimalPairs').mockRejectedValue(new Error('boom'));
    render(<ContrastiveInterventionTool />);
    runMinimalSearch();
    await waitFor(() =>
      expect(track).toHaveBeenCalledWith('error_shown', { tool: 'contrastive', category: 'api_error' }));
  });
});

[ ] Step 2: Run to verify failure — npx vitest run src/components/tools/ContrastiveInterventionTool.test.tsx → FAIL (track not called).
[ ] Step 3: Implement. Add import { track } from '../../lib/analytics';. Then:

handleGenerateMinimal — add const t0 = Date.now(); as the first line; after setMinimalResults(data.slice(0, 50));:

      track('search_executed', {
        tool: 'contrastive',
        // Two target phonemes + optional position + optional picture filter. Counts only.
        constraint_count: 2 + (position !== 'any' ? 1 : 0) + (pictureOnly ? 1 : 0),
        result_count: Math.min(data.length, 50),
        elapsed_ms: Date.now() - t0,
      });

handleGenerateWordLists — add const t0 = Date.now(); first; in the else branch after setWordLists(data);:

        track('search_executed', {
          tool: 'contrastive',
          constraint_count: 2 + (position !== 'any' ? 1 : 0) + (pictureOnly ? 1 : 0),
          result_count: data.length,
          elapsed_ms: Date.now() - t0,
        });

handleGenerateMultiple — add const t0 = Date.now(); first; in the else branch after setMultipleSets(sets);:

        track('search_executed', {
          tool: 'contrastive',
          constraint_count: 1 + targetList.length + (position !== 'any' ? 1 : 0) + (pictureOnly ? 1 : 0),
          result_count: sets.length,
          elapsed_ms: Date.now() - t0,
        });

All four catch blocks (including handleGenerateMaximalPairs) — add as the first line inside catch (err) {:

      track('error_shown', { tool: 'contrastive', category: 'api_error' });

(handleGenerateMaximalPairs gets no search_executed — it is an intermediate step; the word-lists step is the search.)

[ ] Step 4: Run to verify pass — same command → PASS, and the pre-existing tests in the file still pass.
[ ] Step 5: Commit

git add packages/web/frontend/src/components/tools/ContrastiveInterventionTool.tsx packages/web/frontend/src/components/tools/ContrastiveInterventionTool.test.tsx
git commit -m "feat(analytics): instrument Contrast Sets searches and failures"

Task 4: Shared export path `results_exported` (ExportMenu + WordListTable copy)¶

Files: - Modify: packages/web/frontend/src/components/shared/ExportMenu.tsx (props + 3 handlers) - Modify: packages/web/frontend/src/components/shared/WordListTable.tsx (copyWords, ~line 311) - Test: Create packages/web/frontend/src/components/shared/ExportMenu.test.tsx

Interfaces: - Consumes: track from ../../lib/analytics. - Produces: ExportMenuProps gains analyticsTool?: string; when absent the tool is derived from dataType (words→'wordLists', sentences→'sentences', pairs/groups→'contrastive' — same mapping as sourceToolFor in SelectionToolbar.tsx). Events: results_exported {tool, format: 'csv'|'copy', item_count}. No call-site changes required (defaults are correct for every current caller: WordListTable=words, OutputFeed via SelectionToolbar=sentences, ContrastiveGroupsTable=pairs/groups).

[ ] Step 1: Write the failing tests — create ExportMenu.test.tsx:

import { describe, it, expect, vi, beforeEach } from 'vitest';
import { render, screen, fireEvent } from '@testing-library/react';

vi.mock('../../lib/analytics', () => ({ track: vi.fn() }));
vi.mock('../../hooks/usePropertyMetadata', () => ({
  usePropertyMetadata: () => ({ propertyMap: {} }),
  formatPropertyValue: (v: unknown) => String(v),
}));
import { track } from '../../lib/analytics';
import ExportMenu from './ExportMenu';

const words = [{ word: 'cat' }, { word: 'dog' }] as never;

describe('ExportMenu analytics (2026-07-21 telemetry spec)', () => {
  beforeEach(() => {
    vi.mocked(track).mockClear();
    Object.assign(navigator, { clipboard: { writeText: vi.fn().mockResolvedValue(undefined) } });
    globalThis.URL.createObjectURL = vi.fn(() => 'blob:x');
    globalThis.URL.revokeObjectURL = vi.fn();
  });

  it('fires results_exported format copy on plain-text copy, tool derived from dataType', () => {
    render(<ExportMenu data={words} dataType="words" />);
    fireEvent.click(screen.getByRole('button', { name: /export/i }));
    fireEvent.click(screen.getByRole('menuitem', { name: /plain text/i }));
    expect(track).toHaveBeenCalledWith('results_exported',
      { tool: 'wordLists', format: 'copy', item_count: 2 });
  });

  it('fires results_exported format csv on download', () => {
    render(<ExportMenu data={words} dataType="words" />);
    fireEvent.click(screen.getByRole('button', { name: /export/i }));
    fireEvent.click(screen.getByRole('menuitem', { name: /download csv/i }));
    expect(track).toHaveBeenCalledWith('results_exported',
      { tool: 'wordLists', format: 'csv', item_count: 2 });
  });

  it('analyticsTool prop overrides the dataType default', () => {
    render(<ExportMenu data={words} dataType="words" analyticsTool="lookup" />);
    fireEvent.click(screen.getByRole('button', { name: /export/i }));
    fireEvent.click(screen.getByRole('menuitem', { name: /plain text/i }));
    expect(track).toHaveBeenCalledWith('results_exported',
      expect.objectContaining({ tool: 'lookup' }));
  });
});

If the menu-item accessible names differ from /plain text/i / /download csv/i, adjust the regexes to the actual labels rendered by ExportMenu's <MenuItem>s — do not change the labels themselves.

[ ] Step 2: Run to verify failure — npx vitest run src/components/shared/ExportMenu.test.tsx → FAIL.
[ ] Step 3: Implement. In ExportMenu.tsx: add import { track } from '../../lib/analytics';; extend the props interface:

  /** Analytics tool id for results_exported. Defaults from dataType; pass
   *  explicitly if this menu is ever mounted outside its home tool. */
  analyticsTool?: string;

Above the component add:

/** Mirrors sourceToolFor in SelectionToolbar.tsx (kept separate: that one is
 *  typed to PackSourceTool for add-to-editor provenance). */
function toolForDataType(dataType: ExportMenuProps['dataType']): string {
  if (dataType === 'words') return 'wordLists';
  if (dataType === 'sentences') return 'sentences';
  return 'contrastive';
}

In the component destructure analyticsTool and compute const tool = analyticsTool ?? toolForDataType(dataType);. In handleCopyPlainText and handleCopyNumberedList, after the export call (before setCopied(true)):

    track('results_exported', { tool, format: 'copy', item_count: dataToExport.length });

In handleDownloadCSV, before handleClose():

    track('results_exported', { tool, format: 'csv', item_count: dataToExport.length });

In WordListTable.tsx, add import { track } from '../../lib/analytics'; and extend copyWords:

  const copyWords = () => {
    const text = sortedWords.map(w => w.word).join('\n');
    navigator.clipboard.writeText(text);
    track('results_exported', { tool: 'wordLists', format: 'copy', item_count: sortedWords.length });
  };

[ ] Step 4: Run to verify pass — npx vitest run src/components/shared/ → new tests PASS, existing SelectionToolbar/ContrastiveGroupsTable/WordImageThumb tests unaffected.
[ ] Step 5: Commit

git add packages/web/frontend/src/components/shared/ExportMenu.tsx packages/web/frontend/src/components/shared/ExportMenu.test.tsx packages/web/frontend/src/components/shared/WordListTable.tsx
git commit -m "feat(analytics): instrument the shared export/copy path"

Task 5: `error_shown` — crash, export, and editor surfaces¶

Files: - Modify: packages/web/frontend/src/components/ErrorBoundary.tsx (componentDidCatch) - Modify: packages/web/frontend/src/components/pack/export/ExportDialog.tsx (both catch blocks) - Modify: packages/web/frontend/src/components/pack/PackEditor.tsx (three add-field promise chains) - Modify: packages/web/frontend/src/components/WelcomeView.tsx (handleBuild catch) - Test: packages/web/frontend/src/components/ErrorBoundary.test.tsx, packages/web/frontend/src/components/pack/export/ExportDialog.test.tsx

Interfaces: - Consumes: track; the error_shown allowlist entry (tool: string, category: string). - Produces: error_shown categories crash (tool '' — the boundary mounts app-wide in main.tsx with no tool context), export_failed (tool therapyPack), api_error (tool editor).

[ ] Step 1: Write the failing tests. In ErrorBoundary.test.tsx add the analytics mock + import (same two lines as Task 2, path ../lib/analytics) and:

  it('reports the crash to analytics without free text', () => {
    render(
      <ErrorBoundary>
        <Boom />
      </ErrorBoundary>,
    );
    expect(track).toHaveBeenCalledWith('error_shown', { tool: '', category: 'crash' });
  });

In ExportDialog.test.tsx (it already mocks track — reuse its spy) add a failure-path test following the file's existing render/setup helpers:

  it('fires error_shown export_failed when the clipboard copy fails', async () => {
    Object.assign(navigator, {
      clipboard: { writeText: vi.fn().mockRejectedValue(new Error('nope')) },
    });
    // render the dialog open with a non-empty pack, per this file's existing setup
    fireEvent.click(screen.getByRole('button', { name: /copy/i }));
    await waitFor(() =>
      expect(track).toHaveBeenCalledWith('error_shown', { tool: 'therapyPack', category: 'export_failed' }));
  });

[ ] Step 2: Run to verify failure — npx vitest run src/components/ErrorBoundary.test.tsx src/components/pack/export/ExportDialog.test.tsx → the two new tests FAIL.
[ ] Step 3: Implement.

ErrorBoundary.tsx — add import { track } from '../lib/analytics';; in componentDidCatch, after the logError(...) call:

    // Category only — the message/stack goes to logError, never to analytics.
    track('error_shown', { tool: '', category: 'crash' });

ExportDialog.tsx — in handleDownload's catch (err) {, before setExportError(...):

      track('error_shown', { tool: 'therapyPack', category: 'export_failed' });

and the same line in handleCopyCsv's catch {.

PackEditor.tsx — add import { track } from '../../lib/analytics'; (if not present). The words chain:

        action={<ManualAddField onAdd={(word) =>
          resolveWordForPack(word)
            .then((pw) => edit(() => addItem(pw)))
            .catch(() => track('error_shown', { tool: 'editor', category: 'api_error' }))
        } />}

The contrasts chain — wrap the async body:

        action={<ContrastAddField onAdd={async (a, b) => {
          try {
            const [wa, wb] = await Promise.all([resolveWordForPack(a), resolveWordForPack(b)]);
            edit(() => addItem({
              kind: 'contrast', id: newId(), a: wa.word, b: wb.word,
              aIpa: wa.ipa, aImageFile: wa.imageFile, aImageProvider: wa.imageProvider,
              bIpa: wb.ipa, bImageFile: wb.imageFile, bImageProvider: wb.imageProvider,
              provenance: { sourceTool: 'manual', addedAt: Date.now() },
            }));
          } catch {
            track('error_shown', { tool: 'editor', category: 'api_error' });
          }
        }} />}

The sentences chain — append the same .catch(() => track('error_shown', { tool: 'editor', category: 'api_error' })) after its .then(...).

WelcomeView.tsx — in handleBuild's catch {, before setSeedError(...):

      track('error_shown', { tool: 'editor', category: 'api_error' });

[ ] Step 4: Run to verify pass — same vitest command → PASS; also npx vitest run src/components/pack src/components/WelcomeView.test.tsx.
[ ] Step 5: Commit

git add packages/web/frontend/src/components/ErrorBoundary.tsx packages/web/frontend/src/components/ErrorBoundary.test.tsx packages/web/frontend/src/components/pack/export/ExportDialog.tsx packages/web/frontend/src/components/pack/export/ExportDialog.test.tsx packages/web/frontend/src/components/pack/PackEditor.tsx packages/web/frontend/src/components/WelcomeView.tsx
git commit -m "feat(analytics): wire error_shown for crash, export, and editor failures"

Task 6: `error_shown` — remaining tool catches + audio, then Phase A PR¶

Files: - Modify: packages/web/frontend/src/components/Builder.tsx (catch, ~line 443) - Modify: packages/web/frontend/src/components/tools/TextAnalysisTool.tsx (catch, ~line 157) - Modify: packages/web/frontend/src/components/tools/GovernedGenerationTool/index.tsx (catch, ~line 68) - Modify: packages/web/frontend/src/components/tools/LookupTool.tsx (catches at ~lines 649 and 934 — the two that call setError; NOT the console-only phoneme-features catch at 623) - Modify: packages/web/frontend/src/components/tools/AudioAnalysisTool/CaptureControls.tsx (mic catch, ~line 100) - Modify: packages/web/frontend/src/components/tools/AudioAnalysisTool/AudioAnalysisTool.tsx (the two error-setting branches, ~lines 76–84) - Test: packages/web/frontend/src/components/tools/AudioAnalysisTool/CaptureControls.test.tsx

Interfaces: - Consumes: track; category set from Global Constraints. - Produces: api_error on every user-visible tool failure (wordLists, textAnalysis, sentences, lookup); audio on client-side audio failures.

[ ] Step 1: Write the failing test in CaptureControls.test.tsx — add the analytics mock + import (path ../../../lib/analytics) at the top, then inside the main describe (consent pre-granted by its beforeEach):

  it('fires error_shown audio when the microphone is blocked', async () => {
    Object.defineProperty(navigator, 'mediaDevices', {
      configurable: true,
      value: { getUserMedia: vi.fn().mockRejectedValue(new Error('denied')) },
    });
    render(<CaptureControls onClip={vi.fn()} />);
    fireEvent.click(screen.getByRole('button', { name: /record/i }));
    await waitFor(() =>
      expect(track).toHaveBeenCalledWith('error_shown', { tool: 'audio', category: 'audio' }));
  });

[ ] Step 2: Run to verify failure — npx vitest run src/components/tools/AudioAnalysisTool/CaptureControls.test.tsx → new test FAILS.
[ ] Step 3: Implement. Add the track import to each file (relative path per location), then one line per catch:
Builder.tsx catch: track('error_shown', { tool: 'wordLists', category: 'api_error' });
TextAnalysisTool.tsx catch: track('error_shown', { tool: 'textAnalysis', category: 'api_error' });
GovernedGenerationTool/index.tsx catch: track('error_shown', { tool: 'sentences', category: 'api_error' });
LookupTool.tsx both setError catches: track('error_shown', { tool: 'lookup', category: 'api_error' });
CaptureControls.tsx mic catch (before setMicError): track('error_shown', { tool: 'audio', category: 'audio' });
AudioAnalysisTool.tsx — in the warming branch and the final else branch (both set status: 'error'), before each patchProduction: track('error_shown', { tool: 'audio', category: 'audio' });
[ ] Step 4: Run the full Phase A gate

cd packages/web/frontend && npm run type-check && npm run lint && npm test && npm run build
cd ../workers && npm run type-check && npm test

Expected: all green (build needs VITE_API_URL only in CI — local npm run build uses the repo's build config; if it throws on a missing VITE_API_URL, run VITE_API_URL=https://api.phonolex.com npm run build).

[ ] Step 5: Commit, push, PR

git add -A && git commit -m "feat(analytics): wire error_shown across tool and audio failure paths"
git push -u origin feature/telemetry-instrumentation
gh pr create --base develop --title "Telemetry: instrumentation gaps (Contrast Sets, shared export, error_shown, pack lifecycle)" --body "Implements Phase A of docs/superpowers/specs/2026-07-21-telemetry-improvements-design.md

🤖 Generated with [Claude Code](https://claude.com/claude-code)"

Stop for user review of the PR before Phase B.

Phase B — Retention rollup (branch `feature/analytics-rollup`, one PR)¶

Setup: git checkout develop && git pull && git checkout -b feature/analytics-rollup

Task 7: Rollup pure logic (week windows, SQL builders, row shaping)¶

Files: - Create: packages/web/workers/src/rollup/rollup.ts - Create: packages/web/workers/src/rollup/schema.sql - Test: packages/web/workers/src/__tests__/rollup.test.ts (collected by the existing workers vitest run; these are pure functions — no bindings needed)

Interfaces: - Produces (Task 8 consumes exactly these): - interface WeekWindow { weekStart: string; start: string; end: string } - priorWeekWindow(now: Date): WeekWindow, completedWeeks(now: Date, maxWeeks?: number): WeekWindow[] (default 12, ascending) - eventCountsSql(dataset: string, env: string, w: WeekWindow): string, devicesSql(...), searchStatsSql(...) — same signatures - parseWae(json: unknown): Array<Record<string, string>> (unwraps .data, tolerates missing) - insertParams(w: WeekWindow, env: string, events, devices, search): { eventCounts: unknown[][]; devices: unknown[]; searchStats: unknown[][] } — bind-param arrays matching schema column order; numeric strings from WAE already Number()-coerced

[ ] Step 1: Write schema.sql (verbatim from the spec):

-- phonolex-analytics D1 — weekly aggregate rollups from Workers Analytics
-- Engine (2026-07-21 telemetry spec).
--
-- POLICY INVARIANT: rows are AGGREGATE-ONLY. No session/device identifier
-- (WAE blob3) may ever be written here — that is what keeps the privacy
-- policy's "~90 day expiry" statement true while counts persist.
CREATE TABLE IF NOT EXISTS weekly_event_counts (
  week_start TEXT NOT NULL,
  env        TEXT NOT NULL,
  event      TEXT NOT NULL,
  tool       TEXT NOT NULL DEFAULT '',
  format     TEXT NOT NULL DEFAULT '',
  category   TEXT NOT NULL DEFAULT '',
  outcome    TEXT NOT NULL DEFAULT '',
  n          INTEGER NOT NULL,
  PRIMARY KEY (week_start, env, event, tool, format, category, outcome)
);
CREATE TABLE IF NOT EXISTS weekly_devices (
  week_start TEXT NOT NULL,
  env        TEXT NOT NULL,
  devices    INTEGER NOT NULL,
  PRIMARY KEY (week_start, env)
);
CREATE TABLE IF NOT EXISTS weekly_search_stats (
  week_start        TEXT NOT NULL,
  env               TEXT NOT NULL,
  tool              TEXT NOT NULL,
  searches          INTEGER NOT NULL,
  median_elapsed_ms REAL,
  p90_elapsed_ms    REAL,
  PRIMARY KEY (week_start, env, tool)
);

[ ] Step 2: Write the failing tests — src/__tests__/rollup.test.ts:

import { describe, it, expect } from 'vitest';
import {
  priorWeekWindow, completedWeeks, eventCountsSql, devicesSql, searchStatsSql,
  parseWae, insertParams,
} from '../rollup/rollup';

describe('week windows', () => {
  it('priorWeekWindow returns the previous ISO Monday-to-Monday window', () => {
    // 2026-07-21 is a Tuesday → prior ISO week is Mon 07-13 .. Mon 07-20.
    const w = priorWeekWindow(new Date('2026-07-21T14:00:00Z'));
    expect(w).toEqual({ weekStart: '2026-07-13', start: '2026-07-13 00:00:00', end: '2026-07-20 00:00:00' });
  });
  it('handles a Monday "now" (prior week ends today)', () => {
    const w = priorWeekWindow(new Date('2026-07-20T00:30:00Z'));
    expect(w.weekStart).toBe('2026-07-13');
  });
  it('completedWeeks returns maxWeeks ascending windows ending at the prior week', () => {
    const ws = completedWeeks(new Date('2026-07-21T14:00:00Z'), 3);
    expect(ws.map((w) => w.weekStart)).toEqual(['2026-06-29', '2026-07-06', '2026-07-13']);
  });
});

describe('SQL builders (layout-locked to analyticsEvents.ts)', () => {
  const w = { weekStart: '2026-07-13', start: '2026-07-13 00:00:00', end: '2026-07-20 00:00:00' };
  it('eventCountsSql groups the enum blob dims and never selects the session blob', () => {
    const sql = eventCountsSql('phonolex_events', 'prod', w);
    expect(sql).toContain('SUM(_sample_interval)');
    expect(sql).toContain("blob2 = 'prod'");
    expect(sql).toContain("toDateTime('2026-07-13 00:00:00')");
    expect(sql).not.toContain('blob3');
  });
  it('devicesSql collapses sessions to a count', () => {
    expect(devicesSql('phonolex_events', 'prod', w)).toContain('COUNT(DISTINCT blob3)');
  });
  it('searchStatsSql filters absent doubles and uses weighted quantiles', () => {
    const sql = searchStatsSql('phonolex_events', 'prod', w);
    expect(sql).toContain('double4 >= 0');
    expect(sql).toContain('quantileWeighted(0.5)(double4, _sample_interval)');
    expect(sql).toContain("blob1 = 'search_executed'");
  });
});

describe('insertParams', () => {
  const w = { weekStart: '2026-07-13', start: '2026-07-13 00:00:00', end: '2026-07-20 00:00:00' };
  it('shapes bind params in schema column order with numbers coerced', () => {
    const out = insertParams(w, 'prod',
      [{ event: 'app_loaded', tool: '', format: '', category: '', outcome: '', n: '21' }],
      '7',
      [{ tool: 'wordLists', searches: '12', median_elapsed_ms: '340.5', p90_elapsed_ms: '900' }]);
    expect(out.eventCounts).toEqual([['2026-07-13', 'prod', 'app_loaded', '', '', '', '', 21]]);
    expect(out.devices).toEqual(['2026-07-13', 'prod', 7]);
    expect(out.searchStats).toEqual([['2026-07-13', 'prod', 'wordLists', 12, 340.5, 900]]);
  });
  it('never emits a session-shaped value (aggregate-only invariant)', () => {
    const out = insertParams(w, 'prod',
      [{ event: 'word_viewed', tool: '', format: '', category: '', outcome: '', n: '5' }], '3', []);
    const flat = [...out.eventCounts.flat(), ...out.devices, ...out.searchStats.flat()];
    const uuidish = /^[0-9a-f]{8}-[0-9a-f]{4}/i;
    expect(flat.some((v) => typeof v === 'string' && uuidish.test(v))).toBe(false);
  });
  it('parseWae unwraps the data array and tolerates empty results', () => {
    expect(parseWae({ data: [{ a: '1' }] })).toEqual([{ a: '1' }]);
    expect(parseWae({})).toEqual([]);
  });
});

[ ] Step 3: Run to verify failure — cd packages/web/workers && npx vitest run src/__tests__/rollup.test.ts → FAIL (module missing).
[ ] Step 4: Implement src/rollup/rollup.ts:

/**
 * Pure logic for the phonolex-analytics-rollup Worker (2026-07-21 telemetry
 * spec). Week-window math, WAE SQL builders, and D1 bind-param shaping.
 *
 * LAYOUT — locked to packages/web/workers/src/config/analyticsEvents.ts:
 *   blob1=name blob2=env blob3=session blob4=tool blob5=format
 *   blob6=category blob7=outcome; double4=elapsed_ms; absent doubles = -1.
 *
 * POLICY INVARIANT: everything emitted here is aggregate-only — blob3 is
 * only ever collapsed through COUNT(DISTINCT ...), never selected.
 */

export interface WeekWindow {
  /** ISO date of the Monday, e.g. '2026-07-13' */
  weekStart: string;
  /** Inclusive SQL datetime lower bound */
  start: string;
  /** Exclusive SQL datetime upper bound (next Monday) */
  end: string;
}

function windowFromMonday(monday: Date): WeekWindow {
  const end = new Date(monday);
  end.setUTCDate(end.getUTCDate() + 7);
  const iso = (d: Date) => d.toISOString().slice(0, 10);
  return { weekStart: iso(monday), start: `${iso(monday)} 00:00:00`, end: `${iso(end)} 00:00:00` };
}

export function priorWeekWindow(now: Date): WeekWindow {
  const d = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate()));
  const dow = (d.getUTCDay() + 6) % 7; // Monday = 0
  d.setUTCDate(d.getUTCDate() - dow - 7);
  return windowFromMonday(d);
}

/** The last `maxWeeks` complete ISO weeks, ascending (oldest first). */
export function completedWeeks(now: Date, maxWeeks = 12): WeekWindow[] {
  const newest = priorWeekWindow(now);
  const out: WeekWindow[] = [];
  const monday = new Date(`${newest.weekStart}T00:00:00Z`);
  for (let i = 0; i < maxWeeks; i++) {
    out.unshift(windowFromMonday(new Date(monday)));
    monday.setUTCDate(monday.getUTCDate() - 7);
  }
  return out;
}

const where = (env: string, w: WeekWindow): string =>
  `timestamp >= toDateTime('${w.start}') AND timestamp < toDateTime('${w.end}') AND blob2 = '${env}'`;

export function eventCountsSql(dataset: string, env: string, w: WeekWindow): string {
  return `SELECT blob1 AS event, blob4 AS tool, blob5 AS format, blob6 AS category,
       blob7 AS outcome, SUM(_sample_interval) AS n
  FROM ${dataset} WHERE ${where(env, w)}
  GROUP BY event, tool, format, category, outcome FORMAT JSON`;
}

export function devicesSql(dataset: string, env: string, w: WeekWindow): string {
  return `SELECT COUNT(DISTINCT blob3) AS devices FROM ${dataset} WHERE ${where(env, w)} FORMAT JSON`;
}

export function searchStatsSql(dataset: string, env: string, w: WeekWindow): string {
  return `SELECT blob4 AS tool, SUM(_sample_interval) AS searches,
       quantileWeighted(0.5)(double4, _sample_interval) AS median_elapsed_ms,
       quantileWeighted(0.9)(double4, _sample_interval) AS p90_elapsed_ms
  FROM ${dataset} WHERE ${where(env, w)}
    AND blob1 = 'search_executed' AND double4 >= 0
  GROUP BY tool FORMAT JSON`;
}

/** WAE SQL API returns { data: [...] } with all values as strings. */
export function parseWae(json: unknown): Array<Record<string, string>> {
  const data = (json as { data?: unknown })?.data;
  return Array.isArray(data) ? (data as Array<Record<string, string>>) : [];
}

export function insertParams(
  w: WeekWindow,
  env: string,
  events: Array<Record<string, string>>,
  devices: string | number,
  search: Array<Record<string, string>>,
): { eventCounts: unknown[][]; devices: unknown[]; searchStats: unknown[][] } {
  return {
    eventCounts: events.map((r) => [
      w.weekStart, env, r.event, r.tool ?? '', r.format ?? '', r.category ?? '', r.outcome ?? '', Number(r.n),
    ]),
    devices: [w.weekStart, env, Number(devices)],
    searchStats: search.map((r) => [
      w.weekStart, env, r.tool, Number(r.searches), Number(r.median_elapsed_ms), Number(r.p90_elapsed_ms),
    ]),
  };
}

[ ] Step 5: Run to verify pass — npx vitest run src/__tests__/rollup.test.ts → PASS.
[ ] Step 6: Commit

git add packages/web/workers/src/rollup/rollup.ts packages/web/workers/src/rollup/schema.sql packages/web/workers/src/__tests__/rollup.test.ts
git commit -m "feat(rollup): week-window math, WAE SQL builders, aggregate row shaping"

Task 8: Rollup Worker entry (scheduled + token-guarded backfill)¶

Files: - Create: packages/web/workers/src/rollup/index.ts - Create: packages/web/workers/wrangler.rollup.toml - Test: append to packages/web/workers/src/__tests__/rollup.test.ts

Interfaces: - Consumes: everything Task 7 produces. - Produces: runRollup(env: RollupEnv, windows: WeekWindow[], fetchImpl?: typeof fetch): Promise<{ weeks: number }> and the default { scheduled, fetch } export. interface RollupEnv { ANALYTICS_DB: D1Database; ANALYTICS_READ_TOKEN: string; CF_ACCOUNT_ID: string }. Task 9 deploys against wrangler.rollup.toml.

[ ] Step 1: Write the failing tests (append to rollup.test.ts):

import { runRollup, type RollupEnv } from '../rollup/index';

function fakeD1() {
  const batches: string[][] = [];
  const db = {
    prepare: (sql: string) => ({ bind: (..._args: unknown[]) => ({ __sql: sql }) }),
    batch: (stmts: Array<{ __sql: string }>) => {
      batches.push(stmts.map((s) => s.__sql));
      return Promise.resolve([]);
    },
  };
  return { db: db as unknown as D1Database, batches };
}

const waeResponse = (rows: Array<Record<string, string>>) =>
  new Response(JSON.stringify({ data: rows }), { status: 200 });

describe('runRollup', () => {
  const w = { weekStart: '2026-07-13', start: '2026-07-13 00:00:00', end: '2026-07-20 00:00:00' };

  it('queries both datasets and writes delete-then-insert batches per (week, env)', async () => {
    const { db, batches } = fakeD1();
    const queried: string[] = [];
    const fetchImpl = (async (_url: RequestInfo | URL, init?: RequestInit) => {
      const sql = String(init?.body ?? '');
      queried.push(sql);
      if (sql.includes('COUNT(DISTINCT blob3)')) return waeResponse([{ devices: '7' }]);
      if (sql.includes('search_executed')) return waeResponse([]);
      return waeResponse([{ event: 'app_loaded', tool: '', format: '', category: '', outcome: '', n: '21' }]);
    }) as typeof fetch;

    const env: RollupEnv = {
      ANALYTICS_DB: db, ANALYTICS_READ_TOKEN: 'tok', CF_ACCOUNT_ID: 'acct',
    };
    const out = await runRollup(env, [w], fetchImpl);

    expect(out).toEqual({ weeks: 1 });
    expect(queried.some((q) => q.includes('FROM phonolex_events '))).toBe(true);
    expect(queried.some((q) => q.includes('FROM phonolex_events_staging '))).toBe(true);
    // One batch per (week, dataset); deletes precede inserts inside each batch.
    expect(batches).toHaveLength(2);
    for (const b of batches) {
      const firstInsert = b.findIndex((s) => s.startsWith('INSERT'));
      const lastDelete = b.map((s, i) => (s.startsWith('DELETE') ? i : -1)).filter((i) => i >= 0).pop()!;
      expect(lastDelete).toBeLessThan(firstInsert);
    }
  });

  it('propagates WAE errors (scheduled run fails loudly, next cron retries)', async () => {
    const { db } = fakeD1();
    const fetchImpl = (async () => new Response('nope', { status: 500 })) as typeof fetch;
    const env: RollupEnv = { ANALYTICS_DB: db, ANALYTICS_READ_TOKEN: 'tok', CF_ACCOUNT_ID: 'acct' };
    await expect(runRollup(env, [w], fetchImpl)).rejects.toThrow(/WAE SQL/);
  });
});

[ ] Step 2: Run to verify failure — npx vitest run src/__tests__/rollup.test.ts → FAIL (../rollup/index missing).
[ ] Step 3: Implement src/rollup/index.ts:

/**
 * phonolex-analytics-rollup — route-less cron Worker (2026-07-21 telemetry
 * spec). Weekly (Mon 06:00 UTC) it aggregates the prior ISO week from both
 * WAE datasets into the phonolex-analytics D1. A token-guarded /backfill
 * fetch handler re-rolls every complete week still inside WAE's 3-month
 * retention. Deployed manually: npx wrangler deploy -c wrangler.rollup.toml
 */

import {
  priorWeekWindow, completedWeeks, eventCountsSql, devicesSql, searchStatsSql,
  parseWae, insertParams, type WeekWindow,
} from './rollup';

export interface RollupEnv {
  ANALYTICS_DB: D1Database;
  /** Account-scoped API token with Account Analytics: Read. Also guards /backfill. */
  ANALYTICS_READ_TOKEN: string;
  CF_ACCOUNT_ID: string;
}

const DATASETS: Array<{ dataset: string; env: 'prod' | 'staging' }> = [
  { dataset: 'phonolex_events', env: 'prod' },
  { dataset: 'phonolex_events_staging', env: 'staging' },
];

async function waeQuery(
  env: RollupEnv, sql: string, fetchImpl: typeof fetch,
): Promise<Array<Record<string, string>>> {
  const res = await fetchImpl(
    `https://api.cloudflare.com/client/v4/accounts/${env.CF_ACCOUNT_ID}/analytics_engine/sql`,
    { method: 'POST', headers: { Authorization: `Bearer ${env.ANALYTICS_READ_TOKEN}` }, body: sql },
  );
  if (!res.ok) throw new Error(`WAE SQL query failed: ${res.status} ${await res.text()}`);
  return parseWae(await res.json());
}

export async function runRollup(
  env: RollupEnv, windows: WeekWindow[], fetchImpl: typeof fetch = fetch,
): Promise<{ weeks: number }> {
  for (const w of windows) {
    for (const { dataset, env: envName } of DATASETS) {
      const [events, devices, search] = await Promise.all([
        waeQuery(env, eventCountsSql(dataset, envName, w), fetchImpl),
        waeQuery(env, devicesSql(dataset, envName, w), fetchImpl),
        waeQuery(env, searchStatsSql(dataset, envName, w), fetchImpl),
      ]);
      const p = insertParams(w, envName, events, devices[0]?.devices ?? 0, search);
      const db = env.ANALYTICS_DB;
      // Idempotent: delete the (week, env) slice, then insert. One row per
      // statement keeps every statement far under D1's 100-bind-param cap.
      await db.batch([
        db.prepare('DELETE FROM weekly_event_counts WHERE week_start = ? AND env = ?').bind(w.weekStart, envName),
        db.prepare('DELETE FROM weekly_devices WHERE week_start = ? AND env = ?').bind(w.weekStart, envName),
        db.prepare('DELETE FROM weekly_search_stats WHERE week_start = ? AND env = ?').bind(w.weekStart, envName),
        ...p.eventCounts.map((row) =>
          db.prepare('INSERT INTO weekly_event_counts (week_start, env, event, tool, format, category, outcome, n) VALUES (?, ?, ?, ?, ?, ?, ?, ?)').bind(...row)),
        db.prepare('INSERT INTO weekly_devices (week_start, env, devices) VALUES (?, ?, ?)').bind(...p.devices),
        ...p.searchStats.map((row) =>
          db.prepare('INSERT INTO weekly_search_stats (week_start, env, tool, searches, median_elapsed_ms, p90_elapsed_ms) VALUES (?, ?, ?, ?, ?, ?)').bind(...row)),
      ]);
    }
  }
  return { weeks: windows.length };
}

function tokenMatches(header: string | null, secret: string): boolean {
  if (!header?.startsWith('Bearer ')) return false;
  const a = new TextEncoder().encode(header.slice(7));
  const b = new TextEncoder().encode(secret);
  if (a.byteLength !== b.byteLength) return false;
  // Workers-runtime extension; not in the standard SubtleCrypto types.
  return (crypto.subtle as unknown as {
    timingSafeEqual(x: ArrayBufferView, y: ArrayBufferView): boolean;
  }).timingSafeEqual(a, b);
}

export default {
  async scheduled(controller: ScheduledController, env: RollupEnv): Promise<void> {
    await runRollup(env, [priorWeekWindow(new Date(controller.scheduledTime))]);
  },
  async fetch(req: Request, env: RollupEnv): Promise<Response> {
    const url = new URL(req.url);
    if (url.pathname !== '/backfill' || !tokenMatches(req.headers.get('authorization'), env.ANALYTICS_READ_TOKEN)) {
      return new Response('Not found', { status: 404 });
    }
    return Response.json(await runRollup(env, completedWeeks(new Date())));
  },
};

[ ] Step 4: Write wrangler.rollup.toml (workers package root):

# phonolex-analytics-rollup — weekly WAE → D1 aggregate rollup (2026-07-21
# telemetry spec). Deployed MANUALLY (npx wrangler deploy -c
# wrangler.rollup.toml); deliberately outside the app deploy workflows.
# Secret: ANALYTICS_READ_TOKEN (wrangler secret put ... -c wrangler.rollup.toml)
# — account-scoped API token with "Account Analytics: Read".
name = "phonolex-analytics-rollup"
main = "src/rollup/index.ts"
compatibility_date = "2025-12-01"

[observability]
enabled = true

[triggers]
crons = ["0 6 * * 1"]

[[d1_databases]]
binding = "ANALYTICS_DB"
database_name = "phonolex-analytics"
database_id = "REPLACE-AFTER-D1-CREATE"   # filled in Task 9 step 1

[vars]
# Not a secret — appears in every dashboard URL (same as the container image id).
CF_ACCOUNT_ID = "1d876879deb11707881d1bf1fc5cd467"

[ ] Step 5: Run to verify pass — npx vitest run src/__tests__/rollup.test.ts && npm run type-check → PASS (type-check covers src/rollup/ since it lives under src/).
[ ] Step 6: Commit

git add packages/web/workers/src/rollup/index.ts packages/web/workers/wrangler.rollup.toml packages/web/workers/src/__tests__/rollup.test.ts
git commit -m "feat(rollup): scheduled + backfill Worker for weekly analytics rollups"

Task 9: Provision, deploy, backfill, verify; Phase B PR¶

Files: - Modify: packages/web/workers/wrangler.rollup.toml (real database_id)

Interfaces: - Consumes: Task 8's Worker; the WAE-read API token (same permission the report script uses — mint a dedicated token in the dash if .env's CLOUDFLARE_API_ACCESS_KEY is broader than Analytics Read). - Produces: live phonolex-analytics D1 with backfilled history; the cron is armed. Task 10/11's history section reads these tables.

[ ] Step 1: Create the database and apply the schema

cd packages/web/workers
npx wrangler d1 create phonolex-analytics        # copy database_id into wrangler.rollup.toml
npx wrangler d1 execute phonolex-analytics --remote --file src/rollup/schema.sql

[ ] Step 2: Set the secret and deploy

npx wrangler secret put ANALYTICS_READ_TOKEN -c wrangler.rollup.toml   # paste the Analytics-Read token
npx wrangler deploy -c wrangler.rollup.toml

Expected: deploy output shows the cron trigger 0 6 * * 1 and the workers.dev route.

[ ] Step 3: Backfill and verify

curl -sS -H "Authorization: Bearer <the same token>" https://phonolex-analytics-rollup.<subdomain>.workers.dev/backfill
# Expected: {"weeks":12}
npx wrangler d1 execute phonolex-analytics --remote --json \
  --command "SELECT week_start, env, SUM(n) AS events FROM weekly_event_counts GROUP BY week_start, env ORDER BY week_start"

Expected: rows for each week since early July with plausible counts (prod ≈ the numbers in the investigation: ~27 app_loaded/30d), zero-event weeks simply absent. Cross-check one week against a direct WAE query before calling it verified. An unauthorized curl (no/wrong token) must return 404.

[ ] Step 4: Full workers gate, commit, push, PR

npm run type-check && npm test
git add -A && git commit -m "feat(rollup): provision phonolex-analytics D1 and deploy the rollup worker"
git push -u origin feature/analytics-rollup
gh pr create --base develop --title "Telemetry: WAE weekly aggregate rollup (phonolex-analytics D1 + cron worker)" --body "Implements Phase B of docs/superpowers/specs/2026-07-21-telemetry-improvements-design.md. Backfill run and verified against WAE.

🤖 Generated with [Claude Code](https://claude.com/claude-code)"

Stop for user review before Phase C.

Phase C — Reporting (branch `feature/analytics-report`, one PR)¶

Setup: git checkout develop && git pull && git checkout -b feature/analytics-report

Task 10: `analytics_report.py` rendering functions + golden tests¶

Files: - Create: packages/web/workers/scripts/analytics_report.py (underscores — importable by its test) - Test: Create: packages/web/workers/scripts/test_analytics_report.py

Interfaces: - Produces (Task 11 wires these into main()): - wae_query(account_id: str, token: str, sql: str) -> list[dict] (urllib, no new deps; raises ReportSourceError on non-200) - d1_query(sql: str) -> list[dict] (subprocess npx wrangler d1 execute phonolex-analytics --remote --json --command ..., cwd = workers package root; raises ReportSourceError on failure) - render_live_section(results: dict[str, list[dict]]) -> str — keys: events, tools, searches, errors, sessions_by_day, audio - render_history_section(tables: dict[str, list[dict]], env: str) -> str — keys: weekly_devices, weekly_event_counts; renders per-week devices + headline events with signed deltas vs the prior week - LAYOUT docstring block carried over verbatim from analytics-report.sh (blob/double positions)

[ ] Step 1: Write the failing tests — test_analytics_report.py:

"""Golden-output tests for analytics_report.py rendering (no network)."""
import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).parent))
from analytics_report import render_history_section, render_live_section


def test_live_section_renders_event_and_tool_tables():
    text = render_live_section({
        "events": [{"event": "app_loaded", "count": "27"}, {"event": "search_executed", "count": "12"}],
        "tools": [{"tool": "wordLists", "opens": "9"}],
        "searches": [{"tool": "wordLists", "searches": "12", "median_elapsed_ms": "340.5"}],
        "errors": [],
        "sessions_by_day": [{"day": "2026-07-20", "sessions": "4"}],
        "audio": [{"outcome": "ok", "requests": "2", "median_duration_ms": "1800"}],
    })
    assert "app_loaded" in text and "27" in text
    assert "wordLists" in text
    assert "no errors" in text.lower()


def test_history_section_computes_weekly_deltas():
    text = render_history_section({
        "weekly_devices": [
            {"week_start": "2026-07-06", "env": "prod", "devices": 5},
            {"week_start": "2026-07-13", "env": "prod", "devices": 8},
        ],
        "weekly_event_counts": [
            {"week_start": "2026-07-06", "env": "prod", "event": "app_loaded", "n": 10},
            {"week_start": "2026-07-13", "env": "prod", "event": "app_loaded", "n": 14},
        ],
    }, env="prod")
    assert "2026-07-13" in text
    assert "+3" in text          # devices delta
    assert "+4" in text          # app_loaded delta


def test_history_section_survives_empty_tables():
    text = render_history_section({"weekly_devices": [], "weekly_event_counts": []}, env="prod")
    assert "no history" in text.lower()

[ ] Step 2: Run to verify failure

Run: uv run python -m pytest packages/web/workers/scripts/test_analytics_report.py -v — Expected: FAIL (module missing).

[ ] Step 3: Implement the rendering half of analytics_report.py:

#!/usr/bin/env python3
"""analytics_report.py — PhonoLex founder analytics report (2026-07-21 spec).

Live section: Workers Analytics Engine SQL API (last 7/28 days).
History section: phonolex-analytics D1 weekly rollups (deltas vs prior week).
Replaces analytics-report.sh.

Usage:
  CF_ACCOUNT_ID=<id> CF_API_TOKEN=<token> uv run python analytics_report.py [--env prod|staging]

LAYOUT — must match packages/web/workers/src/config/analyticsEvents.ts:
  blob1=name  blob2=env  blob3=session  blob4=tool  blob5=format  blob6=category
  blob7=outcome; double1=ts double2=constraint_count double3=result_count
  double4=elapsed_ms double5=item_count double6=duration_ms.
  Absent numeric props are -1 — filter doubleN >= 0.
  SUM(_sample_interval) recovers true counts under AE sampling.
"""
from __future__ import annotations

import argparse
import json
import os
import subprocess
import sys
import urllib.request
from collections import defaultdict
from pathlib import Path

WORKERS_DIR = Path(__file__).parent.parent


class ReportSourceError(RuntimeError):
    """A data source failed; the report degrades to a partial with a warning."""


def _table(rows: list[dict], cols: list[str]) -> str:
    if not rows:
        return "  (none)\n"
    widths = {c: max(len(c), *(len(str(r.get(c, ""))) for r in rows)) for c in cols}
    head = "  " + "  ".join(c.ljust(widths[c]) for c in cols)
    lines = ["  " + "  ".join(str(r.get(c, "")).ljust(widths[c]) for c in cols) for r in rows]
    return "\n".join([head, *lines]) + "\n"


def render_live_section(results: dict[str, list[dict]]) -> str:
    out = ["== Live (Workers Analytics Engine) ==\n"]
    out.append("Events by name (7d):\n" + _table(results["events"], ["event", "count"]))
    out.append("Tool opens (7d):\n" + _table(results["tools"], ["tool", "opens"]))
    out.append("Searches by tool (7d):\n" + _table(results["searches"], ["tool", "searches", "median_elapsed_ms"]))
    if results["errors"]:
        out.append("Errors (7d):\n" + _table(results["errors"], ["tool", "category", "errors"]))
    else:
        out.append("Errors (7d): no errors reported.\n")
    out.append("Sessions per day (7d):\n" + _table(results["sessions_by_day"], ["day", "sessions"]))
    out.append("Audio requests by outcome (7d):\n" + _table(results["audio"], ["outcome", "requests", "median_duration_ms"]))
    return "\n".join(out)


def _delta(cur: int, prev: int | None) -> str:
    if prev is None:
        return ""
    d = cur - prev
    return f" ({'+' if d >= 0 else ''}{d})"


def render_history_section(tables: dict[str, list[dict]], env: str) -> str:
    devices = [r for r in tables.get("weekly_devices", []) if r.get("env") == env]
    counts = [r for r in tables.get("weekly_event_counts", []) if r.get("env") == env]
    if not devices and not counts:
        return "== History (weekly rollups) ==\n  No history rows yet.\n"

    by_week_event: dict[str, dict[str, int]] = defaultdict(dict)
    for r in counts:
        by_week_event[r["week_start"]][r["event"]] = by_week_event[r["week_start"]].get(r["event"], 0) + int(r["n"])
    dev_by_week = {r["week_start"]: int(r["devices"]) for r in devices}
    weeks = sorted(set(by_week_event) | set(dev_by_week))

    out = [f"== History (weekly rollups, env={env}) ==\n"]
    prev_dev: int | None = None
    prev_events: dict[str, int] = {}
    for wk in weeks:
        dev = dev_by_week.get(wk, 0)
        out.append(f"Week of {wk}: devices {dev}{_delta(dev, prev_dev)}")
        for event in ("app_loaded", "search_executed", "pack_seeded", "pack_created", "results_exported"):
            n = by_week_event.get(wk, {}).get(event, 0)
            if n or prev_events.get(event):
                out.append(f"    {event}: {n}{_delta(n, prev_events.get(event))}")
        prev_dev, prev_events = dev, by_week_event.get(wk, {})
    return "\n".join(out) + "\n"

[ ] Step 4: Run to verify pass — same pytest command → PASS.
[ ] Step 5: Commit

git add packages/web/workers/scripts/analytics_report.py packages/web/workers/scripts/test_analytics_report.py
git commit -m "feat(report): analytics_report.py rendering with golden tests"

Task 11: Report data sources + main(); retire the bash script; CI; Phase C PR¶

Files: - Modify: packages/web/workers/scripts/analytics_report.py (append sources + main) - Delete: packages/web/workers/scripts/analytics-report.sh - Modify: packages/web/workers/wrangler.toml (the ANALYTICS binding comment: scripts/analytics-report.sh → scripts/analytics_report.py) - Modify: .github/workflows/ci.yml (add the pytest line next to the seed-R2 step)

Interfaces: - Consumes: Task 10's functions; the rollup tables from Task 9. - Produces: the one founder report command: CF_ACCOUNT_ID=… CF_API_TOKEN=… uv run python packages/web/workers/scripts/analytics_report.py --env prod

[ ] Step 1: Append the data sources + main to analytics_report.py:

def wae_query(account_id: str, token: str, sql: str) -> list[dict]:
    req = urllib.request.Request(
        f"https://api.cloudflare.com/client/v4/accounts/{account_id}/analytics_engine/sql",
        data=sql.encode(), method="POST",
        headers={"Authorization": f"Bearer {token}", "Content-Type": "text/plain"},
    )
    try:
        with urllib.request.urlopen(req, timeout=30) as res:
            return json.loads(res.read()).get("data", [])
    except Exception as e:  # noqa: BLE001 — any failure degrades to a partial report
        raise ReportSourceError(f"WAE query failed: {e}") from e


def d1_query(sql: str) -> list[dict]:
    cmd = ["npx", "wrangler", "d1", "execute", "phonolex-analytics", "--remote", "--json", "--command", sql]
    try:
        res = subprocess.run(cmd, cwd=WORKERS_DIR, capture_output=True, text=True, timeout=120, check=True)
        payload = json.loads(res.stdout)
        return payload[0]["results"] if payload else []
    except Exception as e:  # noqa: BLE001
        raise ReportSourceError(f"D1 query failed: {e}") from e


def live_queries(dataset: str, env: str) -> dict[str, str]:
    w = f"timestamp > NOW() - INTERVAL '7' DAY AND blob2 = '{env}'"
    return {
        "events": f"SELECT blob1 AS event, SUM(_sample_interval) AS count FROM {dataset} WHERE {w} GROUP BY event ORDER BY count DESC FORMAT JSON",
        "tools": f"SELECT blob4 AS tool, SUM(_sample_interval) AS opens FROM {dataset} WHERE {w} AND blob1 = 'tool_opened' GROUP BY tool ORDER BY opens DESC FORMAT JSON",
        "searches": f"SELECT blob4 AS tool, SUM(_sample_interval) AS searches, quantileWeighted(0.5)(double4, _sample_interval) AS median_elapsed_ms FROM {dataset} WHERE {w} AND blob1 = 'search_executed' AND double4 >= 0 GROUP BY tool ORDER BY searches DESC FORMAT JSON",
        "errors": f"SELECT blob4 AS tool, blob6 AS category, SUM(_sample_interval) AS errors FROM {dataset} WHERE {w} AND blob1 = 'error_shown' GROUP BY tool, category ORDER BY errors DESC FORMAT JSON",
        "sessions_by_day": f"SELECT toDate(timestamp) AS day, COUNT(DISTINCT blob3) AS sessions FROM {dataset} WHERE {w} GROUP BY day ORDER BY day ASC FORMAT JSON",
        "audio": f"SELECT blob7 AS outcome, SUM(_sample_interval) AS requests, quantileWeighted(0.5)(double6, _sample_interval) AS median_duration_ms FROM {dataset} WHERE {w} AND blob1 = 'audio_request' AND double6 >= 0 GROUP BY outcome ORDER BY requests DESC FORMAT JSON",
    }


def main() -> int:
    ap = argparse.ArgumentParser(description=__doc__)
    ap.add_argument("--env", choices=["prod", "staging"], default="prod")
    args = ap.parse_args()
    dataset = "phonolex_events" if args.env == "prod" else "phonolex_events_staging"
    account_id, token = os.environ.get("CF_ACCOUNT_ID"), os.environ.get("CF_API_TOKEN")

    print(f"PhonoLex analytics report — env={args.env}\n")
    warnings: list[str] = []

    if account_id and token:
        try:
            results = {k: wae_query(account_id, token, sql) for k, sql in live_queries(dataset, args.env).items()}
            print(render_live_section(results))
        except ReportSourceError as e:
            warnings.append(str(e))
    else:
        warnings.append("live section skipped: set CF_ACCOUNT_ID and CF_API_TOKEN")

    try:
        tables = {
            "weekly_devices": d1_query("SELECT week_start, env, devices FROM weekly_devices ORDER BY week_start"),
            "weekly_event_counts": d1_query("SELECT week_start, env, event, n FROM weekly_event_counts ORDER BY week_start"),
        }
        print(render_history_section(tables, args.env))
    except ReportSourceError as e:
        warnings.append(str(e))

    for msg in warnings:
        print(f"WARNING: {msg}", file=sys.stderr)
    return 0 if len(warnings) < 2 else 1


if __name__ == "__main__":
    sys.exit(main())

[ ] Step 2: Retire the bash script and update pointers

git rm packages/web/workers/scripts/analytics-report.sh

In packages/web/workers/wrangler.toml, change the ANALYTICS binding comment's scripts/analytics-report.sh to scripts/analytics_report.py. In .github/workflows/ci.yml, next to the existing seed-R2 pytest step add:

      - name: Analytics report rendering tests
        run: uv run python -m pytest packages/web/workers/scripts/test_analytics_report.py -v

[ ] Step 3: Verify against live data

set -a && . ./.env && set +a
CF_ACCOUNT_ID=1d876879deb11707881d1bf1fc5cd467 CF_API_TOKEN="$CLOUDFLARE_API_ACCESS_KEY" \
  uv run python packages/web/workers/scripts/analytics_report.py --env prod

Expected: live section matches a spot-check WAE query; history section shows the backfilled weeks with deltas; exit 0; no stack traces. Also run once with CF_API_TOKEN unset → live section skipped with a warning, history still prints, exit 0.

[ ] Step 4: Tests, commit, push, PR

uv run python -m pytest packages/web/workers/scripts/test_analytics_report.py packages/web/workers/scripts/test_seed_r2.py -v
git add -A && git commit -m "feat(report): analytics_report.py live+history report; retire analytics-report.sh"
git push -u origin feature/analytics-report
gh pr create --base develop --title "Telemetry: analytics_report.py (live WAE + weekly rollup history)" --body "Implements Phase C of docs/superpowers/specs/2026-07-21-telemetry-improvements-design.md

🤖 Generated with [Claude Code](https://claude.com/claude-code)"

Telemetry Improvements Implementation Plan¶

Global Constraints¶

Phase A — Instrumentation (branch feature/telemetry-instrumentation, one PR to develop)¶

Task 1: Allowlist the four Materials Editor lifecycle events (worker)¶

Task 2: Fire lifecycle events from the pack store¶

Task 3: Contrast Sets search_executed + api_error¶

Task 4: Shared export path results_exported (ExportMenu + WordListTable copy)¶

Task 5: error_shown — crash, export, and editor surfaces¶

Task 6: error_shown — remaining tool catches + audio, then Phase A PR¶

Phase B — Retention rollup (branch feature/analytics-rollup, one PR)¶