BigQuery Query Infrastructure 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: Ship aggregate-backed canned reports in the admin portal — first entry: event counts by type over a chosen window — built on a generic registry pattern reusable for future reports.

Architecture: Daily MERGE job populates a small analytics.event_counts_daily rollup table. A query registry exposes named, parameterized queries against that table via a new POST /analytics/admin/bq-query endpoint. Admin Console tab renders a saved-query picker + result table. Raw SQL is deliberately deferred; the endpoint rejects { sql } payloads this iteration.

Tech Stack: Node 22, Express 5, @google-cloud/bigquery SDK, Zod for validation, Vitest for tests, React 19 + Vite for admin, pino for structured logging.

Spec: docs/superpowers/specs/2026-04-30-bq-query-infrastructure-design.md

Pre-flight verification (do once, no commit)

Before Task 1, run these read-only checks from the main checkout (not the worktree). Capture outputs in scratchpad — they inform decisions in later tasks:

bash

# 1. Check whether analytics.events is partitioned (informs follow-up issue)
gcloud auth login   # if not already
bq show --format=prettyjson lantern-app-dev:analytics.events | grep -A3 "timePartitioning\|rangePartitioning" || echo "NOT PARTITIONED"

# 2. Check current analytics-api SA roles on the dataset
SA="$(gcloud run services describe analytics-api --region us-central1 --project lantern-app-dev --format='value(spec.template.spec.serviceAccountName)')"
echo "SA: $SA"
gcloud projects get-iam-policy lantern-app-dev --flatten='bindings[].members' --filter="bindings.members:$SA" --format='table(bindings.role)'

Findings to record (paste into umbrella issue when filed):

analytics.events partitioning: <yes/no, on what column>
analytics-api SA: <value>
SA's BQ-related roles: <list>

If bigquery.dataEditor (or broader) is not present on the dataset, Task 4 (the MERGE) will fail at runtime. This is fine — the deploy step (post-merge) is when we'd grant it. Note in the umbrella issue.

If the events table is not partitioned, file a separate issue (Add timestamp partitioning to analytics.events) as a follow-up. Don't block on it; the daily MERGE only reads one day at a time so the cost is bounded either way.

Task 0: Worktree + branch setup + umbrella issue

Files:

Create: .claude/worktrees/bq-query-infra/ (worktree dir)
Create: .claude/worktrees/bq-query-infra/.env.local (symlink)
[ ] Step 1: File umbrella issue

bash

gh issue create \
  --title "Admin: BigQuery query infrastructure (aggregate-backed canned reports)" \
  --body "$(cat <<'EOF'
Build aggregate-backed canned-report infrastructure in the admin portal. First report: event counts by type over a chosen window. Raw SQL editor deferred to a follow-up.

## Spec
docs/superpowers/specs/2026-04-30-bq-query-infrastructure-design.md

## Plan
docs/superpowers/plans/2026-04-30-bq-query-infrastructure.md

## Scope this iteration
- Aggregate table `analytics.event_counts_daily` + daily MERGE job
- Query registry pattern with `event_counts` as first entry
- `POST /analytics/admin/bq-query` endpoint (canned only; rejects `{ sql }`)
- Admin Console tab with saved-query picker + result table
- Backfill admin endpoint
- Per-query structured logging (audit + future cost-association substrate)

## Deferred (follow-up issue)
- Raw-SQL admin editor + `maximumBytesBilled` cost gates + global byte ceiling + SQL-shape checks

## Pre-flight findings
<paste from scratchpad>

EOF
)" \
  --label enhancement,analytics

Capture the issue number; substitute it for <N> in the next step.

[ ] Step 2: File deferred-iteration follow-up issue

bash

gh issue create \
  --title "Admin BQ Console: raw-SQL editor with cost gates" \
  --body "Follow-up to umbrella issue #<N>. Adds raw-SQL editor to the Console tab with maximumBytesBilled per-job caps, global byte ceiling, SQL-shape startswith check, dryRun cost estimation, row-limit truncation, and a strictly bigquery.dataViewer service account for raw-SQL execution. See spec docs/superpowers/specs/2026-04-30-bq-query-infrastructure-design.md (deferred section)." \
  --label enhancement,analytics

Capture the issue number for cross-linking later.

[ ] Step 3: Create worktree on new feature branch

bash

cd /home/mechelle/repos/lantern_app
git fetch origin
git worktree add -b feat/<N>-bq-query-infra .claude/worktrees/bq-query-infra origin/dev
ln -s /home/mechelle/repos/lantern_app/.env.local .claude/worktrees/bq-query-infra/.env.local

Verify:

bash

git worktree list
ls -la .claude/worktrees/bq-query-infra/.env.local   # should be a symlink

[ ] Step 4: Install deps in worktree

bash

cd .claude/worktrees/bq-query-infra
npm ci

Expected: completes without errors, ~1-2 min with hot npm cache. All subsequent tasks run from this directory unless stated otherwise.

[ ] Step 5: Verify validate baseline passes

bash

npm run validate

Expected: PASS (we have not yet introduced any code changes). If it fails before we touch anything, stop and resolve before proceeding.

[ ] Step 6: Commit nothing, push nothing yet

This task introduces no code. Move to Task 1.

Task 1: Aggregate BQ table schema + provisioning script

Files:

Create: tooling/schemas/bigquery-event-counts-daily.json
Create: tooling/scripts/bq-create-event-counts-daily.sh

The script provisions the table in dev. Prod table provisioning is a manual post-merge step documented in the script's header.

[ ] Step 1: Write the schema file

tooling/schemas/bigquery-event-counts-daily.json:

json

[
  {
    "name": "day",
    "type": "DATE",
    "mode": "REQUIRED",
    "description": "Calendar day (UTC) the events occurred on"
  },
  {
    "name": "event_type",
    "type": "STRING",
    "mode": "REQUIRED",
    "description": "Event name from the analytics taxonomy"
  },
  {
    "name": "event_tier",
    "type": "STRING",
    "mode": "REQUIRED",
    "description": "Event tier: auto or registered"
  },
  {
    "name": "environment",
    "type": "STRING",
    "mode": "REQUIRED",
    "description": "production or development"
  },
  {
    "name": "count",
    "type": "INT64",
    "mode": "REQUIRED",
    "description": "Number of events matching (day, event_type, event_tier, environment)"
  },
  {
    "name": "aggregated_at",
    "type": "TIMESTAMP",
    "mode": "REQUIRED",
    "description": "When this row was last (re)computed by the daily aggregation MERGE"
  }
]

[ ] Step 2: Write the provisioning script

tooling/scripts/bq-create-event-counts-daily.sh:

bash

#!/usr/bin/env bash
# Provisions the analytics.event_counts_daily aggregate table.
#
# Usage: ./tooling/scripts/bq-create-event-counts-daily.sh <project-id>
# Example: ./tooling/scripts/bq-create-event-counts-daily.sh lantern-app-dev
#
# This is a one-time setup script. Run for each environment (dev, prod)
# before the analytics-api scheduled job runs for the first time.
# Idempotent — re-running on an existing table is a no-op via --force.

set -euo pipefail

PROJECT_ID="${1:?Usage: $0 <project-id>}"
SCHEMA_FILE="$(dirname "$0")/../schemas/bigquery-event-counts-daily.json"

if [ ! -f "$SCHEMA_FILE" ]; then
  echo "Error: schema file not found at $SCHEMA_FILE" >&2
  exit 1
fi

bq --project_id="$PROJECT_ID" mk --force \
  --table \
  --time_partitioning_field=day \
  --time_partitioning_type=DAY \
  --clustering_fields=event_type \
  --description="Daily rollup of event counts by type, tier, and environment. Maintained by analytics-api scheduled job." \
  "${PROJECT_ID}:analytics.event_counts_daily" \
  "$SCHEMA_FILE"

echo "✓ Table ${PROJECT_ID}:analytics.event_counts_daily ready"

Make executable:

bash

chmod +x tooling/scripts/bq-create-event-counts-daily.sh

[ ] Step 3: Verify the script passes shellcheck (linter)

If shellcheck is part of npm run validate:

bash

npm run lint

Otherwise just inspect manually that it parses and is correct.

[ ] Step 4: Commit and push

bash

git add tooling/schemas/bigquery-event-counts-daily.json tooling/scripts/bq-create-event-counts-daily.sh
git commit -m "feat(analytics): add event_counts_daily aggregate table schema + provisioning script

Refs #<N>"
git push -u origin feat/<N>-bq-query-infra

Expected: branch created on remote, no CI fires (no PR open yet — per repo workflow rules).

Task 2: Forge read-side runner (`packages/forge/query.js`)

Files:

Create: packages/forge/query.js
Create: packages/forge/test/query.test.js
Modify: packages/forge/index.js — re-export runQuery
[ ] Step 1: Write the failing test

packages/forge/test/query.test.js:

import { describe, it, expect, vi, beforeEach } from 'vitest'

const mockGetQueryResults = vi.fn()
const mockCreateQueryJob = vi.fn()

vi.mock('@google-cloud/bigquery', () => ({
  BigQuery: vi.fn().mockImplementation(() => ({
    createQueryJob: mockCreateQueryJob,
  })),
}))

import { runQuery } from '../query.js'

beforeEach(() => {
  mockGetQueryResults.mockReset()
  mockCreateQueryJob.mockReset()
  mockCreateQueryJob.mockResolvedValue([
    { id: 'job-abc-123', getQueryResults: mockGetQueryResults },
  ])
  mockGetQueryResults.mockResolvedValue([
    [{ event_type: 'lantern_lit', count: 42 }],
    null,
    {
      schema: {
        fields: [
          { name: 'event_type', type: 'STRING' },
          { name: 'count', type: 'INTEGER' },
        ],
      },
      totalBytesBilled: '10485760',
      totalBytesProcessed: '12480',
      cacheHit: false,
      startTime: '1714502400000',
      endTime: '1714502400184',
    },
  ])
})

describe('runQuery', () => {
  it('returns rows, schema, and jobMeta', async () => {
    const result = await runQuery({
      sql: 'SELECT event_type, count FROM `proj.analytics.event_counts_daily` WHERE day >= @since',
      params: { since: '2026-04-23' },
    })

    expect(result.rows).toEqual([{ event_type: 'lantern_lit', count: 42 }])
    expect(result.schema).toEqual([
      { name: 'event_type', type: 'STRING' },
      { name: 'count', type: 'INTEGER' },
    ])
    expect(result.jobMeta).toMatchObject({
      jobId: 'job-abc-123',
      bytesProcessed: 12480,
      totalBytesBilled: 10485760,
      cacheHit: false,
      durationMs: 184,
    })
    expect(typeof result.jobMeta.costUsd).toBe('number')
    expect(result.generatedAt).toBeInstanceOf(Date)
  })

  it('passes named parameters to BQ', async () => {
    await runQuery({
      sql: 'SELECT 1 WHERE x = @x',
      params: { x: 'hello' },
    })

    expect(mockCreateQueryJob).toHaveBeenCalledWith(
      expect.objectContaining({
        query: expect.stringContaining('@x'),
        params: { x: 'hello' },
      })
    )
  })

  it('computes costUsd from totalBytesBilled at $5/TB', async () => {
    const result = await runQuery({ sql: 'SELECT 1', params: {} })
    // totalBytesBilled = 10485760 → 10485760 / 1e12 * 5 = 5.24288e-5
    expect(result.jobMeta.costUsd).toBeCloseTo(5.24288e-5, 10)
  })

  it('propagates BQ errors with structured shape', async () => {
    mockCreateQueryJob.mockRejectedValueOnce(
      Object.assign(new Error('Permission denied'), { code: 403 })
    )

    await expect(runQuery({ sql: 'SELECT 1', params: {} })).rejects.toMatchObject({
      message: 'Permission denied',
      code: 403,
    })
  })
})

[ ] Step 2: Run test to verify it fails

bash

cd packages/forge && npm run test:run -- test/query.test.js

Expected: FAIL — Cannot find module '../query.js' or similar.

[ ] Step 3: Implement packages/forge/query.js

/**
 * BigQuery Read Module
 *
 * Executes parameterized BigQuery queries and returns shaped results.
 * Read-side companion to bigquery.js (which handles streaming inserts).
 *
 * No cost gates this iteration — aggregate-backed canned reports make them
 * unnecessary. Cost-gating infrastructure lands with the raw-SQL iteration.
 *
 * Emits a structured pino-style log payload via the optional `logger` arg
 * for audit trail + future cost-association infrastructure.
 */

import { BigQuery } from '@google-cloud/bigquery'

const BQ_ON_DEMAND_USD_PER_TB = 5

let _defaultClient = null
function getDefaultClient() {
  if (!_defaultClient) _defaultClient = new BigQuery()
  return _defaultClient
}

/**
 * Run a parameterized BigQuery query.
 *
 * @param {Object} args
 * @param {string} args.sql - Parameterized SQL with @param placeholders
 * @param {Object} args.params - Named parameter values
 * @param {Object} [args._client] - Injected BigQuery client (for tests)
 * @returns {Promise<{rows, schema, jobMeta, generatedAt}>}
 */
export async function runQuery({ sql, params = {}, _client } = {}) {
  const client = _client || getDefaultClient()

  const [job] = await client.createQueryJob({
    query: sql,
    params,
    parameterMode: 'NAMED',
    useLegacySql: false,
  })

  const [rawRows, , metadata] = await job.getQueryResults()

  const schema = metadata?.schema?.fields?.map((f) => ({ name: f.name, type: f.type })) ?? []
  const totalBytesBilled = Number(metadata?.totalBytesBilled ?? 0)
  const bytesProcessed = Number(metadata?.totalBytesProcessed ?? 0)
  const startTime = Number(metadata?.startTime ?? 0)
  const endTime = Number(metadata?.endTime ?? 0)
  const durationMs = startTime && endTime ? endTime - startTime : 0
  const costUsd = (totalBytesBilled / 1e12) * BQ_ON_DEMAND_USD_PER_TB

  return {
    rows: rawRows,
    schema,
    jobMeta: {
      jobId: job.id,
      bytesProcessed,
      totalBytesBilled,
      costUsd,
      cacheHit: !!metadata?.cacheHit,
      durationMs,
    },
    generatedAt: new Date(),
  }
}

[ ] Step 4: Add export to packages/forge/index.js

Append to the existing exports (do not modify the existing forge object):

// Read-side runner — used by analytics-api admin endpoints
export { runQuery } from './query.js'

[ ] Step 5: Run test to verify it passes

bash

cd packages/forge && npm run test:run -- test/query.test.js

Expected: PASS, all 4 tests.

[ ] Step 6: Run full Forge test suite

bash

cd packages/forge && npm run test:run

Expected: PASS, no regressions in existing tests (write path, validation, etc.).

[ ] Step 7: Commit + push

bash

git add packages/forge/query.js packages/forge/test/query.test.js packages/forge/index.js
git commit -m "feat(forge): add runQuery read-side BQ runner

Parameterized query execution with structured job metadata
(bytes processed, billed, cost, cache hit, duration). No cost
gates — those land with the raw-SQL iteration.

Refs #<N>"
git push

Task 3: Aggregation service module

Encapsulates the MERGE SQL builder so the scheduled and backfill routes share one implementation.

Files:

Create: services/api/analytics/src/services/eventCountsAggregation.service.js
Create: services/api/analytics/test/services/eventCountsAggregation.service.test.js
[ ] Step 1: Write the failing test

services/api/analytics/test/services/eventCountsAggregation.service.test.js:

import { describe, it, expect, vi, beforeEach } from 'vitest'

const mockCreateQueryJob = vi.fn()
const mockGetQueryResults = vi.fn()

vi.mock('@google-cloud/bigquery', () => ({
  BigQuery: vi.fn().mockImplementation(() => ({
    createQueryJob: mockCreateQueryJob,
  })),
}))

import { runEventCountsAggregation, buildMergeSql } from '../../src/services/eventCountsAggregation.service.js'

beforeEach(() => {
  mockCreateQueryJob.mockReset()
  mockGetQueryResults.mockReset()
  mockCreateQueryJob.mockResolvedValue([
    { id: 'job-merge-1', getQueryResults: mockGetQueryResults, getMetadata: vi.fn().mockResolvedValue([{ statistics: { query: { numDmlAffectedRows: '12' } } }]) },
  ])
  mockGetQueryResults.mockResolvedValue([[], null, { schema: { fields: [] }, totalBytesBilled: '0' }])
})

describe('buildMergeSql', () => {
  it('builds MERGE for a single day (default lookback=1)', () => {
    const sql = buildMergeSql({ projectId: 'lantern-app-dev' })
    expect(sql).toContain('MERGE `lantern-app-dev.analytics.event_counts_daily`')
    expect(sql).toContain('FROM `lantern-app-dev.analytics.events`')
    expect(sql).toContain('DATE(timestamp) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)')
    expect(sql).toContain('WHEN MATCHED THEN UPDATE')
    expect(sql).toContain('WHEN NOT MATCHED THEN INSERT')
  })

  it('builds MERGE with a date range when lookbackDays > 1', () => {
    const sql = buildMergeSql({ projectId: 'lantern-app-dev', lookbackDays: 14 })
    expect(sql).toContain('DATE(timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)')
  })

  it('rejects lookbackDays < 1 or > 365', () => {
    expect(() => buildMergeSql({ projectId: 'p', lookbackDays: 0 })).toThrow(/lookbackDays/)
    expect(() => buildMergeSql({ projectId: 'p', lookbackDays: 366 })).toThrow(/lookbackDays/)
  })
})

describe('runEventCountsAggregation', () => {
  it('runs the MERGE and returns rowsAffected', async () => {
    const result = await runEventCountsAggregation({ projectId: 'lantern-app-dev' })
    expect(result.rowsAffected).toBe(12)
    expect(mockCreateQueryJob).toHaveBeenCalledWith(
      expect.objectContaining({
        query: expect.stringContaining('MERGE'),
        useLegacySql: false,
      })
    )
  })
})

[ ] Step 2: Run test to verify it fails

bash

cd services/api/analytics && npm run test:run -- test/services/eventCountsAggregation.service.test.js

Expected: FAIL — module not found.

[ ] Step 3: Implement the service module

services/api/analytics/src/services/eventCountsAggregation.service.js:

/**
 * Event Counts Aggregation Service
 *
 * Maintains the analytics.event_counts_daily rollup table by running
 * an idempotent MERGE against analytics.events.
 *
 * Used by:
 *   - POST /analytics/scheduled/aggregate-event-counts-daily   (Cloud Scheduler, lookbackDays=1)
 *   - POST /analytics/admin/aggregations/event-counts/backfill (admin manual, lookbackDays up to 365)
 */

import { BigQuery } from '@google-cloud/bigquery'

let _client = null
function getClient() {
  if (!_client) _client = new BigQuery()
  return _client
}

/**
 * Build the MERGE SQL for event-count aggregation.
 *
 * @param {Object} args
 * @param {string} args.projectId - GCP project (e.g., 'lantern-app-dev')
 * @param {number} [args.lookbackDays=1] - Number of days back to (re)aggregate
 * @returns {string} BigQuery SQL
 */
export function buildMergeSql({ projectId, lookbackDays = 1 }) {
  if (!Number.isInteger(lookbackDays) || lookbackDays < 1 || lookbackDays > 365) {
    throw new Error(`Invalid lookbackDays: ${lookbackDays}. Must be 1–365.`)
  }

  const dateFilter =
    lookbackDays === 1
      ? 'DATE(timestamp) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)'
      : `DATE(timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL ${lookbackDays} DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)`

  return `
    MERGE \`${projectId}.analytics.event_counts_daily\` T
    USING (
      SELECT
        DATE(timestamp)         AS day,
        event_type,
        event_tier,
        environment,
        COUNT(*)                AS count,
        CURRENT_TIMESTAMP()     AS aggregated_at
      FROM \`${projectId}.analytics.events\`
      WHERE ${dateFilter}
      GROUP BY day, event_type, event_tier, environment
    ) S
    ON  T.day = S.day
    AND T.event_type = S.event_type
    AND T.event_tier = S.event_tier
    AND T.environment = S.environment
    WHEN MATCHED THEN UPDATE SET
      count = S.count,
      aggregated_at = S.aggregated_at
    WHEN NOT MATCHED THEN INSERT (day, event_type, event_tier, environment, count, aggregated_at)
      VALUES (S.day, S.event_type, S.event_tier, S.environment, S.count, S.aggregated_at)
  `
}

/**
 * Run the MERGE and return rowsAffected.
 *
 * @param {Object} args
 * @param {string} args.projectId
 * @param {number} [args.lookbackDays=1]
 * @returns {Promise<{rowsAffected: number, jobId: string, lookbackDays: number}>}
 */
export async function runEventCountsAggregation({ projectId, lookbackDays = 1 }) {
  const sql = buildMergeSql({ projectId, lookbackDays })
  const client = getClient()

  const [job] = await client.createQueryJob({ query: sql, useLegacySql: false })
  await job.getQueryResults()
  const [meta] = await job.getMetadata()

  const rowsAffected = Number(meta?.statistics?.query?.numDmlAffectedRows ?? 0)

  return { rowsAffected, jobId: job.id, lookbackDays }
}

[ ] Step 4: Run test to verify it passes

bash

cd services/api/analytics && npm run test:run -- test/services/eventCountsAggregation.service.test.js

Expected: PASS, all 5 tests.

[ ] Step 5: Commit + push

bash

git add services/api/analytics/src/services/eventCountsAggregation.service.js \
        services/api/analytics/test/services/eventCountsAggregation.service.test.js
git commit -m "feat(analytics-api): add event counts aggregation service

Idempotent MERGE that maintains analytics.event_counts_daily.
Used by both the scheduled daily route and the admin backfill route.

Refs #<N>"
git push

Task 4: Scheduled aggregation route

Files:

Modify: services/api/analytics/src/routes/scheduled.js — add new endpoint
Create: services/api/analytics/test/routes/aggregateEventCountsDaily.test.js
[ ] Step 1: Write the failing test

services/api/analytics/test/routes/aggregateEventCountsDaily.test.js:

import { describe, it, expect, vi, beforeEach } from 'vitest'
import express from 'express'
import request from 'supertest'

const mockRunAggregation = vi.fn()

vi.mock('../../src/services/eventCountsAggregation.service.js', () => ({
  runEventCountsAggregation: mockRunAggregation,
}))

import scheduledRoutes from '../../src/routes/scheduled.js'

function buildApp({ asAdmin = false, asScheduler = false } = {}) {
  const app = express()
  app.use(express.json())
  app.use((req, _res, next) => {
    req.log = { info: vi.fn(), warn: vi.fn(), error: vi.fn() }
    if (asAdmin) req.user = { uid: 'admin-1', role: 'admin' }
    if (asScheduler) req.headers['x-cloudscheduler-jobname'] = 'event-counts-daily'
    next()
  })
  app.use('/analytics/scheduled', scheduledRoutes)
  app.use((err, _req, res, _next) => res.status(500).json({ error: err.message }))
  return app
}

beforeEach(() => {
  mockRunAggregation.mockReset()
  mockRunAggregation.mockResolvedValue({ rowsAffected: 8, jobId: 'job-1', lookbackDays: 1 })
  process.env.GCP_PROJECT_ID = 'lantern-app-dev'
})

describe('POST /analytics/scheduled/aggregate-event-counts-daily', () => {
  it('rejects request without scheduler header or admin role', async () => {
    const app = buildApp()
    const res = await request(app).post('/analytics/scheduled/aggregate-event-counts-daily')
    expect(res.status).toBe(403)
  })

  it('runs aggregation when invoked by Cloud Scheduler', async () => {
    const app = buildApp({ asScheduler: true })
    const res = await request(app).post('/analytics/scheduled/aggregate-event-counts-daily')
    expect(res.status).toBe(200)
    expect(res.body).toMatchObject({ success: true, rowsAffected: 8, lookbackDays: 1 })
    expect(mockRunAggregation).toHaveBeenCalledWith({ projectId: 'lantern-app-dev', lookbackDays: 1 })
  })

  it('runs aggregation when invoked by admin', async () => {
    const app = buildApp({ asAdmin: true })
    const res = await request(app).post('/analytics/scheduled/aggregate-event-counts-daily')
    expect(res.status).toBe(200)
    expect(res.body.success).toBe(true)
  })

  it('returns 500 on aggregation failure', async () => {
    mockRunAggregation.mockRejectedValueOnce(new Error('BQ permission denied'))
    const app = buildApp({ asScheduler: true })
    const res = await request(app).post('/analytics/scheduled/aggregate-event-counts-daily')
    expect(res.status).toBe(500)
  })
})

If supertest is not yet a devDependency, add it:

bash

cd services/api/analytics && npm install --save-dev supertest

[ ] Step 2: Run test to verify it fails

bash

cd services/api/analytics && npm run test:run -- test/routes/aggregateEventCountsDaily.test.js

Expected: FAIL — endpoint not registered (404 instead of expected status).

[ ] Step 3: Add the new route to services/api/analytics/src/routes/scheduled.js

Append after the existing /daily-aggregation route, before export default router:

import { runEventCountsAggregation } from '../services/eventCountsAggregation.service.js';

/**
 * POST /analytics/scheduled/aggregate-event-counts-daily
 *
 * Maintains analytics.event_counts_daily by aggregating yesterday's events.
 * Idempotent (MERGE) — safe to re-run.
 *
 * Auth: Cloud Scheduler header OR admin user (same pattern as /daily-aggregation).
 */
router.post('/aggregate-event-counts-daily', async (req, res, next) => {
  try {
    const isScheduler = !!req.headers['x-cloudscheduler-jobname'];
    const isAdmin = req.user?.role === 'admin';

    if (!isScheduler && !isAdmin) {
      return res.status(403).json({
        error: 'FORBIDDEN',
        message: 'This endpoint is for Cloud Scheduler or admin users only',
      });
    }

    const projectId = process.env.GCP_PROJECT_ID || process.env.FIREBASE_PROJECT_ID;
    if (!projectId) {
      return res.status(500).json({ error: 'CONFIG', message: 'GCP_PROJECT_ID not set' });
    }

    req.log.info({ source: isScheduler ? 'scheduler' : 'admin' }, 'Starting event counts aggregation');

    const result = await runEventCountsAggregation({ projectId, lookbackDays: 1 });

    req.log.info({ event: 'event_counts_aggregation', ...result }, 'Event counts aggregation completed');
    res.json({ success: true, ...result });
  } catch (error) {
    next(error);
  }
});

[ ] Step 4: Run test to verify it passes

bash

cd services/api/analytics && npm run test:run -- test/routes/aggregateEventCountsDaily.test.js

Expected: PASS, all 4 tests.

[ ] Step 5: Run full analytics-api test suite

bash

cd services/api/analytics && npm run test:run

Expected: PASS, no regressions.

[ ] Step 6: Commit + push

bash

git add services/api/analytics/src/routes/scheduled.js \
        services/api/analytics/test/routes/aggregateEventCountsDaily.test.js \
        services/api/analytics/package.json services/api/analytics/package-lock.json
git commit -m "feat(analytics-api): add scheduled event-counts aggregation endpoint

POST /analytics/scheduled/aggregate-event-counts-daily — runs the
idempotent MERGE that maintains analytics.event_counts_daily.

Refs #<N>"
git push

Task 5: Backfill admin route

Files:

Modify: services/api/analytics/src/routes/admin.js — add backfill endpoint
Create: services/api/analytics/test/routes/eventCountsBackfill.test.js
[ ] Step 1: Write the failing test

services/api/analytics/test/routes/eventCountsBackfill.test.js:

import { describe, it, expect, vi, beforeEach } from 'vitest'
import express from 'express'
import request from 'supertest'

const mockRunAggregation = vi.fn()

vi.mock('../../src/services/eventCountsAggregation.service.js', () => ({
  runEventCountsAggregation: mockRunAggregation,
}))

// Mock metrics + cache services that admin.js imports — we don't exercise them here.
vi.mock('../../src/services/metrics.service.js', () => ({
  getPlatformOverview: vi.fn(), getEngagementMetrics: vi.fn(), getVenueRankings: vi.fn(),
}))
vi.mock('../../src/services/aggregation.service.js', () => ({
  runDailyAggregation: vi.fn(), getAggregatedDates: vi.fn(),
}))
vi.mock('../../src/services/cache.service.js', () => ({
  cacheClear: vi.fn(), cacheStats: vi.fn(),
}))

import adminRoutes from '../../src/routes/admin.js'

function buildApp() {
  const app = express()
  app.use(express.json())
  app.use((req, _res, next) => {
    req.log = { info: vi.fn(), warn: vi.fn(), error: vi.fn() }
    req.user = { uid: 'admin-1', role: 'admin' }
    next()
  })
  app.use('/analytics/admin', adminRoutes)
  app.use((err, _req, res, _next) => res.status(500).json({ error: err.message }))
  return app
}

beforeEach(() => {
  mockRunAggregation.mockReset()
  mockRunAggregation.mockResolvedValue({ rowsAffected: 200, jobId: 'job-bf', lookbackDays: 14 })
  process.env.GCP_PROJECT_ID = 'lantern-app-dev'
})

describe('POST /analytics/admin/aggregations/event-counts/backfill', () => {
  it('runs backfill for a valid days value', async () => {
    const res = await request(buildApp())
      .post('/analytics/admin/aggregations/event-counts/backfill')
      .send({ days: 14 })
    expect(res.status).toBe(200)
    expect(res.body).toMatchObject({ success: true, rowsAffected: 200, lookbackDays: 14 })
    expect(mockRunAggregation).toHaveBeenCalledWith({ projectId: 'lantern-app-dev', lookbackDays: 14 })
  })

  it('rejects days < 1', async () => {
    const res = await request(buildApp())
      .post('/analytics/admin/aggregations/event-counts/backfill')
      .send({ days: 0 })
    expect(res.status).toBe(400)
  })

  it('rejects days > 365', async () => {
    const res = await request(buildApp())
      .post('/analytics/admin/aggregations/event-counts/backfill')
      .send({ days: 400 })
    expect(res.status).toBe(400)
  })

  it('rejects non-integer days', async () => {
    const res = await request(buildApp())
      .post('/analytics/admin/aggregations/event-counts/backfill')
      .send({ days: 'forever' })
    expect(res.status).toBe(400)
  })
})

[ ] Step 2: Run test to verify it fails

bash

cd services/api/analytics && npm run test:run -- test/routes/eventCountsBackfill.test.js

Expected: FAIL — route not registered.

[ ] Step 3: Implement the backfill route

Add to services/api/analytics/src/routes/admin.js. At the top with other imports:

import { runEventCountsAggregation } from '../services/eventCountsAggregation.service.js';

And before export default router:

const backfillSchema = z.object({
  days: z.number().int().min(1).max(365),
});

/**
 * POST /analytics/admin/aggregations/event-counts/backfill
 *
 * Manually backfill the event_counts_daily aggregate for the last N days.
 * Used after first deploy to populate historical data, or to re-aggregate
 * after a schema change. Idempotent.
 */
router.post('/aggregations/event-counts/backfill', async (req, res, next) => {
  try {
    const parsed = backfillSchema.safeParse(req.body);
    if (!parsed.success) {
      return res.status(400).json({
        error: 'VALIDATION',
        message: 'Invalid body',
        issues: parsed.error.issues,
      });
    }

    const projectId = process.env.GCP_PROJECT_ID || process.env.FIREBASE_PROJECT_ID;
    if (!projectId) {
      return res.status(500).json({ error: 'CONFIG', message: 'GCP_PROJECT_ID not set' });
    }

    req.log.info({ days: parsed.data.days }, 'Starting event counts backfill');

    const result = await runEventCountsAggregation({
      projectId,
      lookbackDays: parsed.data.days,
    });

    req.log.info({ event: 'event_counts_backfill', ...result }, 'Backfill completed');
    res.json({ success: true, ...result });
  } catch (error) {
    next(error);
  }
});

[ ] Step 4: Run test to verify it passes

bash

cd services/api/analytics && npm run test:run -- test/routes/eventCountsBackfill.test.js

Expected: PASS, all 4 tests.

[ ] Step 5: Commit + push

bash

git add services/api/analytics/src/routes/admin.js \
        services/api/analytics/test/routes/eventCountsBackfill.test.js
git commit -m "feat(analytics-api): add admin backfill endpoint for event_counts_daily

POST /analytics/admin/aggregations/event-counts/backfill — runs
the MERGE for the last N days (1-365). Used post-deploy to populate
historical data.

Refs #<N>"
git push

Task 6: Query registry module

Files:

Create: services/api/analytics/src/services/queryRegistry.js
Create: services/api/analytics/test/services/queryRegistry.test.js
[ ] Step 1: Write the failing test

services/api/analytics/test/services/queryRegistry.test.js:

import { describe, it, expect } from 'vitest'
import {
  QUERY_REGISTRY,
  buildQuery,
  validateParams,
  resolveWindowToDate,
} from '../../src/services/queryRegistry.js'

describe('QUERY_REGISTRY', () => {
  it('contains the event_counts entry', () => {
    expect(QUERY_REGISTRY.event_counts).toBeDefined()
    expect(QUERY_REGISTRY.event_counts.audience).toBe('admin')
    expect(QUERY_REGISTRY.event_counts.source).toBe('aggregate')
  })
})

describe('resolveWindowToDate', () => {
  it.each([
    ['24h', 1],
    ['7d', 7],
    ['30d', 30],
    ['all', 365],
  ])('resolves %s to today minus %i days', (window, days) => {
    const today = new Date()
    today.setUTCHours(0, 0, 0, 0)
    const expected = new Date(today)
    expected.setUTCDate(today.getUTCDate() - days)
    const got = resolveWindowToDate(window)
    expect(got).toBe(expected.toISOString().slice(0, 10))
  })

  it('throws on unknown window', () => {
    expect(() => resolveWindowToDate('forever')).toThrow(/window/)
  })
})

describe('validateParams', () => {
  it('returns defaults when params are missing', () => {
    const result = validateParams({}, QUERY_REGISTRY.event_counts.paramSchema)
    expect(result.value).toEqual({ window: '7d' })
    expect(result.error).toBeUndefined()
  })

  it('accepts a valid window', () => {
    const result = validateParams({ window: '30d' }, QUERY_REGISTRY.event_counts.paramSchema)
    expect(result.value.window).toBe('30d')
  })

  it('rejects an invalid enum value', () => {
    const result = validateParams({ window: 'forever' }, QUERY_REGISTRY.event_counts.paramSchema)
    expect(result.error).toBeDefined()
  })
})

describe('buildQuery', () => {
  it('builds the event_counts SQL with @since param', () => {
    process.env.GCP_PROJECT_ID = 'lantern-app-dev'
    const built = buildQuery('event_counts', { window: '7d' })
    expect(built.sql).toContain('FROM `lantern-app-dev.analytics.event_counts_daily`')
    expect(built.sql).toContain('WHERE day >= @since')
    expect(built.sql).toContain('GROUP BY event_type')
    expect(built.sql).toContain('ORDER BY count DESC')
    expect(built.params.since).toMatch(/^\d{4}-\d{2}-\d{2}$/)
  })

  it('throws on unknown queryName', () => {
    expect(() => buildQuery('does_not_exist', {})).toThrow(/queryName/)
  })
})

[ ] Step 2: Run test to verify it fails

bash

cd services/api/analytics && npm run test:run -- test/services/queryRegistry.test.js

Expected: FAIL — module not found.

[ ] Step 3: Implement the registry

services/api/analytics/src/services/queryRegistry.js:

/**
 * Query Registry
 *
 * Server-controlled catalog of named, parameterized BigQuery queries.
 * Adding a new canned report = adding a new entry here. The endpoint,
 * runner, auth, and result-table code do not change.
 *
 * Each entry:
 *   - description: human-readable summary
 *   - source: 'aggregate' | 'raw' (informational this iteration)
 *   - audience: 'admin' | 'merchant' (endpoint-level enforcement)
 *   - paramSchema: { name: { enum?: [...], default?: ... } }
 *   - sql(params): { sql, params } — parameterized BQ SQL + named params
 */

const WINDOW_DAYS = { '24h': 1, '7d': 7, '30d': 30, all: 365 };

function getProjectId() {
  return process.env.GCP_PROJECT_ID || process.env.FIREBASE_PROJECT_ID || 'lantern-app-dev';
}

/**
 * Resolve a window enum to an ISO date (YYYY-MM-DD) representing the start
 * of the window (today - N days, UTC).
 */
export function resolveWindowToDate(window) {
  const days = WINDOW_DAYS[window];
  if (days === undefined) {
    throw new Error(`Unknown window: ${window}`);
  }
  const d = new Date();
  d.setUTCHours(0, 0, 0, 0);
  d.setUTCDate(d.getUTCDate() - days);
  return d.toISOString().slice(0, 10);
}

/**
 * Validate params against a registry entry's paramSchema and apply defaults.
 *
 * @returns {{ value: Object } | { error: { code, message, field? } }}
 */
export function validateParams(params, paramSchema) {
  const value = {};
  for (const [name, spec] of Object.entries(paramSchema)) {
    const provided = params?.[name];
    if (provided === undefined) {
      if (spec.default !== undefined) {
        value[name] = spec.default;
      } else {
        return { error: { code: 'MISSING_PARAM', message: `Missing required param: ${name}`, field: name } };
      }
    } else if (spec.enum && !spec.enum.includes(provided)) {
      return {
        error: {
          code: 'INVALID_PARAM',
          message: `Invalid value for ${name}: ${provided}. Must be one of: ${spec.enum.join(', ')}`,
          field: name,
        },
      };
    } else {
      value[name] = provided;
    }
  }
  return { value };
}

export const QUERY_REGISTRY = {
  event_counts: {
    description: 'Event types and counts over a chosen time window',
    source: 'aggregate',
    audience: 'admin',
    paramSchema: {
      window: { enum: ['24h', '7d', '30d', 'all'], default: '7d' },
    },
    sql: ({ window }) => ({
      sql: `
        SELECT event_type, SUM(count) AS count
        FROM \`${getProjectId()}.analytics.event_counts_daily\`
        WHERE day >= @since
        GROUP BY event_type
        ORDER BY count DESC
        LIMIT 5000
      `,
      params: { since: resolveWindowToDate(window) },
    }),
  },
};

/**
 * Build a query from the registry given a queryName and validated params.
 *
 * @returns {{ sql: string, params: Object }}
 */
export function buildQuery(queryName, validatedParams) {
  const entry = QUERY_REGISTRY[queryName];
  if (!entry) {
    throw new Error(`Unknown queryName: ${queryName}`);
  }
  return entry.sql(validatedParams);
}

[ ] Step 4: Run test to verify it passes

bash

cd services/api/analytics && npm run test:run -- test/services/queryRegistry.test.js

Expected: PASS, all tests (8).

[ ] Step 5: Commit + push

bash

git add services/api/analytics/src/services/queryRegistry.js \
        services/api/analytics/test/services/queryRegistry.test.js
git commit -m "feat(analytics-api): add query registry with event_counts entry

Server-controlled catalog of named, parameterized BQ queries.
Adding a new canned report = adding an entry. First entry reads
from the analytics.event_counts_daily aggregate.

Refs #<N>"
git push

Task 7: `POST /admin/bq-query` endpoint

Files:

Modify: services/api/analytics/src/routes/admin.js — add new endpoint
Create: services/api/analytics/test/routes/bqQuery.test.js

The endpoint composes the registry, runner, an in-memory 60s cache, and audience enforcement. Cache is a simple Map keyed on (queryName, JSON.stringify(params)); per Cloud Run instance.

[ ] Step 1: Write the failing test

services/api/analytics/test/routes/bqQuery.test.js:

import { describe, it, expect, vi, beforeEach } from 'vitest'
import express from 'express'
import request from 'supertest'

const mockRunQuery = vi.fn()

vi.mock('@lantern/forge', () => ({
  forge: { track: vi.fn() },
  runQuery: mockRunQuery,
}))

vi.mock('../../src/services/metrics.service.js', () => ({
  getPlatformOverview: vi.fn(), getEngagementMetrics: vi.fn(), getVenueRankings: vi.fn(),
}))
vi.mock('../../src/services/aggregation.service.js', () => ({
  runDailyAggregation: vi.fn(), getAggregatedDates: vi.fn(),
}))
vi.mock('../../src/services/cache.service.js', () => ({
  cacheClear: vi.fn(), cacheStats: vi.fn(),
}))
vi.mock('../../src/services/eventCountsAggregation.service.js', () => ({
  runEventCountsAggregation: vi.fn(),
}))

import adminRoutes from '../../src/routes/admin.js'

function buildApp({ uid = 'admin-1' } = {}) {
  const app = express()
  app.use(express.json())
  app.use((req, _res, next) => {
    req.log = { info: vi.fn(), warn: vi.fn(), error: vi.fn() }
    req.user = { uid, role: 'admin' }
    next()
  })
  app.use('/analytics/admin', adminRoutes)
  app.use((err, _req, res, _next) => res.status(500).json({ error: err.message }))
  return app
}

beforeEach(() => {
  mockRunQuery.mockReset()
  mockRunQuery.mockResolvedValue({
    rows: [{ event_type: 'lantern_lit', count: 42 }],
    schema: [
      { name: 'event_type', type: 'STRING' },
      { name: 'count', type: 'INTEGER' },
    ],
    jobMeta: { jobId: 'j1', bytesProcessed: 1000, totalBytesBilled: 10485760, costUsd: 5.24e-5, cacheHit: false, durationMs: 150 },
    generatedAt: new Date('2026-04-30T18:00:00.000Z'),
  })
  process.env.GCP_PROJECT_ID = 'lantern-app-dev'
})

describe('POST /analytics/admin/bq-query', () => {
  it('runs a known canned query and returns rows + schema + jobMeta', async () => {
    const res = await request(buildApp())
      .post('/analytics/admin/bq-query')
      .send({ queryName: 'event_counts', params: { window: '7d' } })

    expect(res.status).toBe(200)
    expect(res.body.queryName).toBe('event_counts')
    expect(res.body.params).toEqual({ window: '7d' })
    expect(res.body.rows).toEqual([{ event_type: 'lantern_lit', count: 42 }])
    expect(res.body.schema).toHaveLength(2)
    expect(res.body.jobMeta.totalBytesBilled).toBe(10485760)
    expect(res.body.generatedAt).toBe('2026-04-30T18:00:00.000Z')
  })

  it('applies default params when omitted', async () => {
    const res = await request(buildApp())
      .post('/analytics/admin/bq-query')
      .send({ queryName: 'event_counts', params: {} })
    expect(res.status).toBe(200)
    expect(res.body.params).toEqual({ window: '7d' })
  })

  it('rejects unknown queryName with 400', async () => {
    const res = await request(buildApp())
      .post('/analytics/admin/bq-query')
      .send({ queryName: 'does_not_exist', params: {} })
    expect(res.status).toBe(400)
    expect(res.body.error.code).toBe('UNKNOWN_QUERY')
  })

  it('rejects invalid param value with 400', async () => {
    const res = await request(buildApp())
      .post('/analytics/admin/bq-query')
      .send({ queryName: 'event_counts', params: { window: 'forever' } })
    expect(res.status).toBe(400)
    expect(res.body.error.code).toBe('INVALID_PARAM')
  })

  it('rejects raw {sql} payloads with 400', async () => {
    const res = await request(buildApp())
      .post('/analytics/admin/bq-query')
      .send({ sql: 'SELECT 1' })
    expect(res.status).toBe(400)
    expect(res.body.error.code).toBe('RAW_SQL_NOT_SUPPORTED')
  })

  it('caches results for 60s — second identical request hits cache', async () => {
    const app = buildApp()
    await request(app).post('/analytics/admin/bq-query').send({ queryName: 'event_counts', params: { window: '7d' } })
    await request(app).post('/analytics/admin/bq-query').send({ queryName: 'event_counts', params: { window: '7d' } })
    expect(mockRunQuery).toHaveBeenCalledTimes(1)
  })

  it('does NOT cache across different params', async () => {
    const app = buildApp()
    await request(app).post('/analytics/admin/bq-query').send({ queryName: 'event_counts', params: { window: '7d' } })
    await request(app).post('/analytics/admin/bq-query').send({ queryName: 'event_counts', params: { window: '24h' } })
    expect(mockRunQuery).toHaveBeenCalledTimes(2)
  })

  it('returns 502 on BQ failure', async () => {
    mockRunQuery.mockRejectedValueOnce(Object.assign(new Error('BQ exploded'), { code: 500 }))
    const res = await request(buildApp())
      .post('/analytics/admin/bq-query')
      .send({ queryName: 'event_counts', params: { window: '7d' } })
    expect(res.status).toBe(502)
    expect(res.body.error.code).toBe('BQ_QUERY_FAILED')
  })
})

[ ] Step 2: Run test to verify it fails

bash

cd services/api/analytics && npm run test:run -- test/routes/bqQuery.test.js

Expected: FAIL — endpoint not registered.

[ ] Step 3: Implement the endpoint in routes/admin.js

Add imports at the top:

import { runQuery } from '@lantern/forge';
import { QUERY_REGISTRY, validateParams, buildQuery } from '../services/queryRegistry.js';

Add the cache and endpoint before export default router:

// In-memory result cache. Per Cloud Run instance; keyed on (queryName, params JSON).
// 60s TTL — short enough to feel live, long enough to cushion accidental hammering.
const BQ_QUERY_CACHE = new Map();
const BQ_QUERY_CACHE_TTL_MS = 60_000;

function getCachedResponse(key) {
  const entry = BQ_QUERY_CACHE.get(key);
  if (!entry) return null;
  if (Date.now() - entry.cachedAt > BQ_QUERY_CACHE_TTL_MS) {
    BQ_QUERY_CACHE.delete(key);
    return null;
  }
  return entry.response;
}

function setCachedResponse(key, response) {
  BQ_QUERY_CACHE.set(key, { cachedAt: Date.now(), response });
}

const ROUTE_AUDIENCE = 'admin';

/**
 * POST /analytics/admin/bq-query
 *
 * Runs a server-controlled canned query from the registry. This iteration
 * supports queryName + params only; raw {sql} payloads are rejected.
 */
router.post('/bq-query', async (req, res, next) => {
  try {
    const { queryName, params, sql: rawSql } = req.body ?? {};

    if (rawSql !== undefined) {
      return res.status(400).json({
        error: {
          code: 'RAW_SQL_NOT_SUPPORTED',
          message: 'Raw SQL is not supported in this iteration. Use queryName + params.',
        },
      });
    }

    const entry = QUERY_REGISTRY[queryName];
    if (!entry) {
      return res.status(400).json({
        error: { code: 'UNKNOWN_QUERY', message: `Unknown queryName: ${queryName}` },
      });
    }

    if (entry.audience !== ROUTE_AUDIENCE) {
      return res.status(403).json({
        error: { code: 'AUDIENCE_MISMATCH', message: `Query not available to ${ROUTE_AUDIENCE} audience` },
      });
    }

    const validation = validateParams(params, entry.paramSchema);
    if (validation.error) {
      return res.status(400).json({ error: validation.error });
    }

    const cacheKey = `${queryName}:${JSON.stringify(validation.value)}`;
    const cached = getCachedResponse(cacheKey);
    if (cached) {
      req.log.info({ queryName, cached: true }, 'bq-query cache hit');
      return res.json(cached);
    }

    const built = buildQuery(queryName, validation.value);

    let result;
    try {
      result = await runQuery({ sql: built.sql, params: built.params });
    } catch (bqError) {
      req.log.error(
        { event: 'bq_query_failed', queryName, params: validation.value, error: bqError.message, code: bqError.code },
        'BQ query failed'
      );
      return res.status(502).json({
        error: { code: 'BQ_QUERY_FAILED', message: bqError.message, jobId: bqError.jobId },
      });
    }

    const response = {
      queryName,
      params: validation.value,
      generatedAt: result.generatedAt.toISOString(),
      rows: result.rows,
      schema: result.schema,
      jobMeta: result.jobMeta,
    };

    setCachedResponse(cacheKey, response);

    req.log.info(
      {
        event: 'bq_query_executed',
        queryName,
        params: validation.value,
        userId: req.user?.uid,
        jobId: result.jobMeta.jobId,
        bytesProcessed: result.jobMeta.bytesProcessed,
        totalBytesBilled: result.jobMeta.totalBytesBilled,
        costUsd: result.jobMeta.costUsd,
        durationMs: result.jobMeta.durationMs,
        cacheHit: result.jobMeta.cacheHit,
      },
      'bq-query executed'
    );

    res.json(response);
  } catch (error) {
    next(error);
  }
});

[ ] Step 4: Run test to verify it passes

bash

cd services/api/analytics && npm run test:run -- test/routes/bqQuery.test.js

Expected: PASS, all 8 tests.

[ ] Step 5: Run full analytics-api test suite

bash

cd services/api/analytics && npm run test:run

Expected: PASS, no regressions.

[ ] Step 6: Commit + push

bash

git add services/api/analytics/src/routes/admin.js \
        services/api/analytics/test/routes/bqQuery.test.js
git commit -m "feat(analytics-api): add POST /admin/bq-query endpoint

Generic canned-query endpoint composing the registry + Forge runner.
60s in-memory cache, audience enforcement, structured per-query log
line for audit + future cost-association infrastructure.

Refs #<N>"
git push

Task 8: Admin client API extension

Files:

Modify: apps/admin/src/shared/lib/analyticsApi.js — add runBqQuery helper
[ ] Step 1: Read the existing file to match its style

bash

cat apps/admin/src/shared/lib/analyticsApi.js

Note the export style and the apiRequest helper signature.

[ ] Step 2: Add the new helper

Append to apps/admin/src/shared/lib/analyticsApi.js (matching existing JSDoc + arrow-function style — adapt if the file's style differs):

/**
 * POST /analytics/admin/bq-query
 *
 * Runs a server-controlled canned BQ query. The set of available queryNames
 * is defined by the server-side query registry.
 *
 * @param {Object} args
 * @param {string} args.queryName
 * @param {Object} args.params
 * @returns {Promise<{ queryName, params, generatedAt, rows, schema, jobMeta }>}
 */
export async function runBqQuery({ queryName, params }) {
  return apiRequest('/analytics/admin/bq-query', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ queryName, params }),
  })
}

If apiRequest doesn't accept method/body in this codebase, use fetch directly via the same auth-token pattern the file already uses (read it first to confirm).

[ ] Step 3: Run admin tests to verify no regressions

bash

cd apps/admin && npm run test:run 2>/dev/null || npm test -- --run

Expected: PASS.

[ ] Step 4: Commit + push

bash

git add apps/admin/src/shared/lib/analyticsApi.js
git commit -m "feat(admin): add runBqQuery client helper

Refs #<N>"
git push

Task 9: `QueryResultTable` component

A generic component that takes (rows, schema) and renders a sortable table. Reused by the canned-query view this iteration and by the raw-SQL view in the follow-up.

Files:

Create: apps/admin/src/admin/analytics/QueryResultTable.jsx
Create: apps/admin/src/admin/analytics/__tests__/QueryResultTable.test.jsx
Create: apps/admin/src/admin/analytics/QueryResultTable.stories.jsx
[ ] Step 1: Write the failing test

apps/admin/src/admin/analytics/__tests__/QueryResultTable.test.jsx:

jsx

import React from 'react'
import { describe, it, expect } from 'vitest'
import { render, screen, fireEvent } from '@testing-library/react'
import QueryResultTable from '../QueryResultTable'

const SCHEMA = [
  { name: 'event_type', type: 'STRING' },
  { name: 'count', type: 'INTEGER' },
]

const ROWS = [
  { event_type: 'lantern_lit', count: 100 },
  { event_type: 'wave_sent', count: 250 },
  { event_type: 'chat_opened', count: 50 },
]

describe('QueryResultTable', () => {
  it('renders schema columns as headers', () => {
    render(<QueryResultTable rows={ROWS} schema={SCHEMA} />)
    expect(screen.getByRole('columnheader', { name: /event_type/i })).toBeInTheDocument()
    expect(screen.getByRole('columnheader', { name: /count/i })).toBeInTheDocument()
  })

  it('renders rows', () => {
    render(<QueryResultTable rows={ROWS} schema={SCHEMA} />)
    expect(screen.getByText('lantern_lit')).toBeInTheDocument()
    expect(screen.getByText('250')).toBeInTheDocument()
  })

  it('right-aligns numeric columns', () => {
    render(<QueryResultTable rows={ROWS} schema={SCHEMA} />)
    const countCell = screen.getByText('250').closest('td')
    expect(countCell).toHaveClass('numeric')
  })

  it('sorts ascending then descending when a header is clicked', () => {
    render(<QueryResultTable rows={ROWS} schema={SCHEMA} />)
    const countHeader = screen.getByRole('columnheader', { name: /count/i })

    fireEvent.click(countHeader) // asc
    let cells = screen.getAllByRole('cell').filter((c) => c.classList.contains('numeric'))
    expect(cells.map((c) => c.textContent)).toEqual(['50', '100', '250'])

    fireEvent.click(countHeader) // desc
    cells = screen.getAllByRole('cell').filter((c) => c.classList.contains('numeric'))
    expect(cells.map((c) => c.textContent)).toEqual(['250', '100', '50'])
  })

  it('renders an empty state when rows is empty', () => {
    render(<QueryResultTable rows={[]} schema={SCHEMA} />)
    expect(screen.getByText(/no results/i)).toBeInTheDocument()
  })
})

[ ] Step 2: Run test to verify it fails

bash

cd apps/admin && npm test -- --run src/admin/analytics/__tests__/QueryResultTable.test.jsx

Expected: FAIL — component not found.

[ ] Step 3: Implement the component

apps/admin/src/admin/analytics/QueryResultTable.jsx:

jsx

import React, { useMemo, useState } from 'react'

const NUMERIC_TYPES = new Set(['INTEGER', 'INT64', 'FLOAT', 'FLOAT64', 'NUMERIC', 'BIGNUMERIC'])

function isNumericType(type) {
  return NUMERIC_TYPES.has(String(type).toUpperCase())
}

function formatCell(value, type) {
  if (value === null || value === undefined) return ''
  if (isNumericType(type) && typeof value === 'number') return value.toLocaleString()
  return String(value)
}

export default function QueryResultTable({ rows, schema }) {
  const [sortBy, setSortBy] = useState(null)
  const [sortDir, setSortDir] = useState('asc')

  const sortedRows = useMemo(() => {
    if (!sortBy) return rows
    const colType = schema.find((c) => c.name === sortBy)?.type
    const numeric = isNumericType(colType)
    const copy = [...rows]
    copy.sort((a, b) => {
      const av = a[sortBy]
      const bv = b[sortBy]
      if (numeric) return sortDir === 'asc' ? av - bv : bv - av
      const cmp = String(av).localeCompare(String(bv))
      return sortDir === 'asc' ? cmp : -cmp
    })
    return copy
  }, [rows, schema, sortBy, sortDir])

  function handleSort(name) {
    if (sortBy === name) {
      setSortDir(sortDir === 'asc' ? 'desc' : 'asc')
    } else {
      setSortBy(name)
      setSortDir('asc')
    }
  }

  if (!rows || rows.length === 0) {
    return <div className="query-result-empty">No results.</div>
  }

  return (
    <table className="query-result-table">
      <thead>
        <tr>
          {schema.map((col) => (
            <th
              key={col.name}
              onClick={() => handleSort(col.name)}
              className={isNumericType(col.type) ? 'numeric' : ''}
              style={{ cursor: 'pointer' }}
            >
              <span className="th-content">
                {col.name}
                {sortBy === col.name && (sortDir === 'asc' ? ' ▲' : ' ▼')}
              </span>
            </th>
          ))}
        </tr>
      </thead>
      <tbody>
        {sortedRows.map((row, i) => (
          <tr key={i}>
            {schema.map((col) => (
              <td key={col.name} className={isNumericType(col.type) ? 'numeric' : ''}>
                {formatCell(row[col.name], col.type)}
              </td>
            ))}
          </tr>
        ))}
      </tbody>
    </table>
  )
}

[ ] Step 4: Run test to verify it passes

bash

cd apps/admin && npm test -- --run src/admin/analytics/__tests__/QueryResultTable.test.jsx

Expected: PASS, all 5 tests.

[ ] Step 5: Add a Storybook story

apps/admin/src/admin/analytics/QueryResultTable.stories.jsx:

jsx

import React from 'react'
import QueryResultTable from './QueryResultTable'

export default { title: 'Analytics/QueryResultTable', component: QueryResultTable }

const schema = [
  { name: 'event_type', type: 'STRING' },
  { name: 'count', type: 'INTEGER' },
]

const rows = [
  { event_type: 'lantern_lit', count: 1247 },
  { event_type: 'wave_sent', count: 312 },
  { event_type: 'chat_opened', count: 89 },
]

export const Default = () => <QueryResultTable rows={rows} schema={schema} />
export const Empty = () => <QueryResultTable rows={[]} schema={schema} />

[ ] Step 6: Commit + push

bash

git add apps/admin/src/admin/analytics/QueryResultTable.jsx \
        apps/admin/src/admin/analytics/__tests__/QueryResultTable.test.jsx \
        apps/admin/src/admin/analytics/QueryResultTable.stories.jsx
git commit -m "feat(admin): add QueryResultTable shared component

Sortable, schema-driven table for BQ canned + raw query results.

Refs #<N>"
git push

Task 10: Saved-queries manifest + Console tab wiring

Files:

Create: apps/admin/src/admin/analytics/savedQueriesManifest.js
Modify: apps/admin/src/admin/analytics/BigQueryWorkspace.jsx — wire Console tab
Create: apps/admin/src/admin/analytics/__tests__/BigQueryWorkspace.test.jsx
[ ] Step 1: Create the client-side manifest

apps/admin/src/admin/analytics/savedQueriesManifest.js:

/**
 * Client-side manifest mirroring server query registry metadata.
 *
 * Adding a new canned report = adding an entry here AND a matching
 * server-side registry entry in services/api/analytics/src/services/queryRegistry.js.
 *
 * Each entry:
 *   - queryName: matches the server registry key
 *   - title: human-readable name shown in the Console tab card
 *   - description: short summary shown beneath the title
 *   - paramSchema: { name: { enum?: [...], default?: ..., label?: ... } }
 */
export const SAVED_QUERIES = [
  {
    queryName: 'event_counts',
    title: 'Event counts by type',
    description: 'Sum of events by type over the chosen window',
    paramSchema: {
      window: {
        label: 'Window',
        enum: ['24h', '7d', '30d', 'all'],
        default: '7d',
      },
    },
  },
]

[ ] Step 2: Write the failing test

apps/admin/src/admin/analytics/__tests__/BigQueryWorkspace.test.jsx:

jsx

import React from 'react'
import { describe, it, expect, vi, beforeEach } from 'vitest'
import { render, screen, fireEvent, waitFor } from '@testing-library/react'
import { MemoryRouter } from 'react-router-dom'
import BigQueryWorkspace from '../BigQueryWorkspace'

vi.mock('../../../shared/lib/analyticsApi', () => ({
  runBqQuery: vi.fn(),
}))

import { runBqQuery } from '../../../shared/lib/analyticsApi'

beforeEach(() => {
  runBqQuery.mockReset()
  runBqQuery.mockResolvedValue({
    queryName: 'event_counts',
    params: { window: '7d' },
    generatedAt: '2026-04-30T18:00:00.000Z',
    rows: [{ event_type: 'lantern_lit', count: 42 }],
    schema: [
      { name: 'event_type', type: 'STRING' },
      { name: 'count', type: 'INTEGER' },
    ],
    jobMeta: { jobId: 'j1', bytesProcessed: 1000, totalBytesBilled: 10485760, costUsd: 5.24e-5, cacheHit: false, durationMs: 150 },
  })
})

function renderConsole() {
  return render(
    <MemoryRouter initialEntries={['/analytics/bigquery/console']}>
      <BigQueryWorkspace />
    </MemoryRouter>
  )
}

describe('BigQueryWorkspace — Console tab', () => {
  it('lists saved queries', () => {
    renderConsole()
    expect(screen.getByText(/Event counts by type/i)).toBeInTheDocument()
  })

  it('runs the canned query and shows results', async () => {
    renderConsole()
    fireEvent.click(screen.getByRole('button', { name: /run/i }))
    await waitFor(() => expect(runBqQuery).toHaveBeenCalledWith({
      queryName: 'event_counts',
      params: { window: '7d' },
    }))
    expect(await screen.findByText('lantern_lit')).toBeInTheDocument()
    expect(screen.getByText('42')).toBeInTheDocument()
  })

  it('shows the raw SQL placeholder block', () => {
    renderConsole()
    expect(screen.getByText(/raw sql.*coming/i)).toBeInTheDocument()
  })

  it('surfaces query failures', async () => {
    runBqQuery.mockRejectedValueOnce(new Error('Server error'))
    renderConsole()
    fireEvent.click(screen.getByRole('button', { name: /run/i }))
    expect(await screen.findByText(/server error/i)).toBeInTheDocument()
  })
})

[ ] Step 3: Run test to verify it fails

bash

cd apps/admin && npm test -- --run src/admin/analytics/__tests__/BigQueryWorkspace.test.jsx

Expected: FAIL — Console tab still renders the placeholder.

[ ] Step 4: Wire the Console tab in BigQueryWorkspace.jsx

Replace the contents of apps/admin/src/admin/analytics/BigQueryWorkspace.jsx:

jsx

import React, { useState } from 'react'
import { useLocation } from 'react-router-dom'
import PageHeader from '../../shared/components/PageHeader'
import StyledSelect from '../../shared/components/StyledSelect'
import BigQueryTabs from './BigQueryTabs'
import QueryResultTable from './QueryResultTable'
import { SAVED_QUERIES } from './savedQueriesManifest'
import { runBqQuery } from '../../shared/lib/analyticsApi'

const SECTION_META = {
  export: {
    title: 'BigQuery Export Status',
    subtitle: 'Track export health, recent sync windows, and backfill coverage for analytics data.',
  },
  console: {
    title: 'BigQuery Query Console',
    subtitle: 'Run ad-hoc analytics queries and inspect event-level data in a controlled workspace.',
  },
  reports: {
    title: 'BigQuery Scheduled Reports',
    subtitle: 'Manage recurring analytics reports and monitor report delivery outcomes.',
  },
  retention: {
    title: 'BigQuery Data Retention',
    subtitle: 'Review partition retention windows, expiration policies, and storage lifecycle settings.',
  },
}

function getActiveSection(pathname) {
  if (pathname.startsWith('/analytics/bigquery/console')) return 'console'
  if (pathname.startsWith('/analytics/bigquery/reports')) return 'reports'
  if (pathname.startsWith('/analytics/bigquery/retention')) return 'retention'
  return 'export'
}

function SavedQueryCard({ entry, onRun, isRunning }) {
  const [params, setParams] = useState(() => {
    const seed = {}
    for (const [name, spec] of Object.entries(entry.paramSchema)) {
      seed[name] = spec.default
    }
    return seed
  })

  return (
    <div className="feature-card" style={{ marginBottom: 'var(--space-3)' }}>
      <h3>{entry.title}</h3>
      <p style={{ color: 'var(--text-muted)' }}>{entry.description}</p>
      <div style={{ display: 'flex', gap: 'var(--space-2)', alignItems: 'flex-end', flexWrap: 'wrap' }}>
        {Object.entries(entry.paramSchema).map(([name, spec]) => (
          <div key={name} style={{ minWidth: 160 }}>
            <label style={{ display: 'block', marginBottom: 4, fontSize: 12, color: 'var(--text-muted)' }}>
              {spec.label || name}
            </label>
            <StyledSelect
              value={{ value: params[name], label: params[name] }}
              onChange={(opt) => setParams({ ...params, [name]: opt.value })}
              options={spec.enum.map((v) => ({ value: v, label: v }))}
            />
          </div>
        ))}
        <button className="btn-secondary" disabled={isRunning} onClick={() => onRun(entry.queryName, params)}>
          {isRunning ? 'Running…' : 'Run'}
        </button>
      </div>
    </div>
  )
}

function ConsolePanel() {
  const [result, setResult] = useState(null)
  const [error, setError] = useState(null)
  const [isRunning, setIsRunning] = useState(false)

  async function handleRun(queryName, params) {
    setIsRunning(true)
    setError(null)
    try {
      const res = await runBqQuery({ queryName, params })
      setResult(res)
    } catch (err) {
      setError(err.message || 'Query failed')
      setResult(null)
    } finally {
      setIsRunning(false)
    }
  }

  return (
    <div>
      <h4>Saved Queries</h4>
      {SAVED_QUERIES.map((entry) => (
        <SavedQueryCard key={entry.queryName} entry={entry} onRun={handleRun} isRunning={isRunning} />
      ))}

      <div className="feature-card coming-soon" style={{ marginBottom: 'var(--space-3)' }}>
        <h3>Raw SQL editor — coming next iteration</h3>
        <p style={{ color: 'var(--text-muted)' }}>
          Custom SQL with cost gates is being built next. For now, use Saved Queries above.
        </p>
      </div>

      {error && (
        <div className="feature-card" role="alert" style={{ borderColor: 'var(--danger-600, #b00)' }}>
          <h4>Query failed</h4>
          <p>{error}</p>
        </div>
      )}

      {result && (
        <div className="feature-card">
          <h4>Results</h4>
          <QueryResultTable rows={result.rows} schema={result.schema} />
          <div style={{ marginTop: 'var(--space-2)', fontSize: 12, color: 'var(--text-muted)' }}>
            Scanned: {(result.jobMeta.bytesProcessed / 1024).toFixed(1)} KB ·
            Billed: {(result.jobMeta.totalBytesBilled / 1_048_576).toFixed(1)} MB ·
            Cost: ${result.jobMeta.costUsd.toFixed(6)} ·
            Cache: {result.jobMeta.cacheHit ? 'hit' : 'miss'} ·
            {result.jobMeta.durationMs}ms
          </div>
        </div>
      )}
    </div>
  )
}

export default function BigQueryWorkspace() {
  const location = useLocation()
  const section = getActiveSection(location.pathname)
  const meta = SECTION_META[section]

  return (
    <div className="user-management-container">
      <div className="user-management-main">
        <PageHeader title={meta.title} subtitle={meta.subtitle} />
        <BigQueryTabs />

        <div className="user-management-body">
          {section === 'console' ? (
            <ConsolePanel />
          ) : (
            <div className="feature-card coming-soon" style={{ maxWidth: 760 }}>
              <h3>{meta.title}</h3>
              <p>{meta.subtitle}</p>
              <p style={{ marginTop: 'var(--space-2)', color: 'var(--text-muted)' }}>
                BigQuery controls are scaffolded and can now be implemented section by section.
              </p>
            </div>
          )}
        </div>
      </div>
    </div>
  )
}

If StyledSelect is imported from a different path in this codebase, adjust accordingly. Verify with:

bash

grep -rn "import StyledSelect" apps/admin/src/ | head -3

[ ] Step 5: Run test to verify it passes

bash

cd apps/admin && npm test -- --run src/admin/analytics/__tests__/BigQueryWorkspace.test.jsx

Expected: PASS, all 4 tests.

[ ] Step 6: Run full admin test suite

bash

cd apps/admin && npm test -- --run

Expected: PASS, no regressions.

[ ] Step 7: Commit + push

bash

git add apps/admin/src/admin/analytics/savedQueriesManifest.js \
        apps/admin/src/admin/analytics/BigQueryWorkspace.jsx \
        apps/admin/src/admin/analytics/__tests__/BigQueryWorkspace.test.jsx
git commit -m "feat(admin): wire BigQuery Console tab to canned queries

Saved-query picker + result table + raw-SQL placeholder block.
First entry: 'Event counts by type' over a configurable window.

Refs #<N>"
git push

Task 11: OpenAPI updates

Files:

Modify: services/api/analytics/openapi.json — document the three new endpoints
[ ] Step 1: Read the current spec to match its style

bash

cat services/api/analytics/openapi.json | head -100

Note the version, base path, and conventions used by existing entries.

[ ] Step 2: Add three new path entries

In services/api/analytics/openapi.json, add under the paths object (alphabetic order):

json

"/analytics/admin/aggregations/event-counts/backfill": {
  "post": {
    "tags": ["admin", "analytics"],
    "summary": "Backfill event_counts_daily for the last N days",
    "security": [{ "bearerAuth": [] }],
    "requestBody": {
      "required": true,
      "content": {
        "application/json": {
          "schema": {
            "type": "object",
            "required": ["days"],
            "properties": {
              "days": { "type": "integer", "minimum": 1, "maximum": 365 }
            }
          }
        }
      }
    },
    "responses": {
      "200": { "description": "Backfill complete" },
      "400": { "description": "Validation error" },
      "403": { "description": "Forbidden" }
    }
  }
},
"/analytics/admin/bq-query": {
  "post": {
    "tags": ["admin", "analytics"],
    "summary": "Run a server-controlled canned BQ query",
    "security": [{ "bearerAuth": [] }],
    "requestBody": {
      "required": true,
      "content": {
        "application/json": {
          "schema": {
            "type": "object",
            "required": ["queryName"],
            "properties": {
              "queryName": { "type": "string", "example": "event_counts" },
              "params": { "type": "object", "example": { "window": "7d" } }
            }
          }
        }
      }
    },
    "responses": {
      "200": { "description": "Query executed" },
      "400": { "description": "Unknown queryName, invalid params, or raw {sql} payload" },
      "403": { "description": "Audience mismatch or unauthorized" },
      "502": { "description": "BigQuery query failure" }
    }
  }
},
"/analytics/scheduled/aggregate-event-counts-daily": {
  "post": {
    "tags": ["scheduled", "analytics"],
    "summary": "Maintain analytics.event_counts_daily — yesterday's events",
    "responses": {
      "200": { "description": "Aggregation complete" },
      "403": { "description": "Forbidden — Cloud Scheduler header or admin role required" },
      "500": { "description": "Aggregation failed" }
    }
  }
}

[ ] Step 3: Validate JSON parses

bash

cd services/api/analytics && node -e "JSON.parse(require('fs').readFileSync('openapi.json'))"

Expected: no output (valid JSON). If error, fix the placement.

[ ] Step 4: Commit + push

bash

git add services/api/analytics/openapi.json
git commit -m "docs(analytics-api): add OpenAPI entries for new BQ endpoints

Refs #<N>"
git push

Task 12: Final validation, push, and handoff

[ ] Step 1: Run full repo validation

bash

cd /home/mechelle/repos/lantern_app/.claude/worktrees/bq-query-infra
npm run validate

Expected: PASS — lint, format, all tests, audit. If any check fails, fix in place (TDD-style: failing test → fix → re-run) and commit each fix as a separate commit before proceeding.

[ ] Step 2: Confirm coverage threshold (75%) not regressed

bash

npm run test:coverage -w apps/web
# and (the target workspace where new code lives):
cd services/api/analytics && npm run test:run -- --coverage

If coverage on any new file is below 75%, add tests for the gap and commit.

[ ] Step 3: Push final state

bash

git push

Already pushed throughout — this is a no-op confirming branch is current.

[ ] Step 4: Print branch summary for the user

bash

echo "=== Branch ready for review ==="
git log --oneline origin/dev..HEAD
echo
echo "Worktree: $(pwd)"
echo "Branch: $(git branch --show-current)"
echo
echo "Pull into your local with:"
echo "  cd /home/mechelle/repos/lantern_app"
echo "  git fetch origin"
echo "  git checkout feat/<N>-bq-query-infra"

[ ] Step 5: Stop. Do not open a PR.

Per the user's standing instruction: branch reviewed via direct pull, PR opened only on explicit go-ahead. Wait for the user.

Post-merge human-driven steps (NOT for the agent)

These run after the user merges the eventual PR and the deploy lands:

Provision the dev BQ table:

bash

./tooling/scripts/bq-create-event-counts-daily.sh lantern-app-dev

Verify analytics-api SA has BQ dataEditor on analytics dataset:

bash

bq --project_id=lantern-app-dev show --format=prettyjson analytics | grep -A20 access

Grant if missing:

bash

gcloud projects add-iam-policy-binding lantern-app-dev \
  --member="serviceAccount:<analytics-api-sa>" \
  --role="roles/bigquery.dataEditor"

Run a manual backfill (e.g., last 14 days) via the admin endpoint:

bash

curl -X POST https://analytics-api-dev.../analytics/admin/aggregations/event-counts/backfill \
  -H "Authorization: Bearer <admin-id-token>" \
  -H "Content-Type: application/json" \
  -d '{"days": 14}'

Configure Cloud Scheduler to invoke the daily aggregation:

bash

gcloud scheduler jobs create http event-counts-daily \
  --schedule="0 1 * * *" \
  --uri="https://analytics-api-dev.../analytics/scheduled/aggregate-event-counts-daily" \
  --http-method=POST \
  --oidc-service-account-email=<scheduler-sa> \
  --location=us-central1 \
  --project=lantern-app-dev \
  --headers="X-CloudScheduler-JobName=event-counts-daily"

Repeat 1–4 for lantern-app-prod when ready.
Smoke test in the admin portal: open /analytics/bigquery/console, click "Run" on the Event counts card, verify data renders.

BigQuery Query Infrastructure Implementation Plan ​

Pre-flight verification (do once, no commit) ​

Task 0: Worktree + branch setup + umbrella issue ​

Task 1: Aggregate BQ table schema + provisioning script ​

Task 2: Forge read-side runner (packages/forge/query.js) ​

Task 3: Aggregation service module ​

Task 4: Scheduled aggregation route ​

Task 5: Backfill admin route ​

Task 6: Query registry module ​

Task 7: POST /admin/bq-query endpoint ​

Task 8: Admin client API extension ​

Task 9: QueryResultTable component ​

Task 10: Saved-queries manifest + Console tab wiring ​

Task 11: OpenAPI updates ​

Task 12: Final validation, push, and handoff ​

Post-merge human-driven steps (NOT for the agent) ​

BigQuery Query Infrastructure Implementation Plan

Pre-flight verification (do once, no commit)

Task 0: Worktree + branch setup + umbrella issue

Task 1: Aggregate BQ table schema + provisioning script

Task 2: Forge read-side runner (`packages/forge/query.js`)

Task 3: Aggregation service module

Task 4: Scheduled aggregation route

Task 5: Backfill admin route

Task 6: Query registry module

Task 7: `POST /admin/bq-query` endpoint

Task 8: Admin client API extension

Task 9: `QueryResultTable` component

Task 10: Saved-queries manifest + Console tab wiring

Task 11: OpenAPI updates

Task 12: Final validation, push, and handoff

Post-merge human-driven steps (NOT for the agent)