# Admin Reports & Analytics (`/admin/reports`, `/admin/analytics`) — Implementation Plan

**Spec:** docs/specs/2026-05-31-admin-reports-analytics.md  ·  **Slug:** admin-reports-analytics  ·  **Wave:** 4
**Depends on:** admin-dashboard, foundation-auth-rbac, zync-subscription

## Goal
Deliver platform-level business intelligence for Zync operators (SUPER_ADMIN only): a Reports surface (`/admin/reports`) with revenue/MRR, signups & churn, and a per-tenant table, plus an Analytics surface (`/admin/analytics`) with module adoption, AI token usage, and a platform-wide marketing funnel. All data is cross-tenant (no `tenantQuery` scoping) and sourced from existing Neon tables (`zync_subscriptions`, `tenants`, `tenant_audit_log`, `ai_usage_log`) and the Cloudflare Analytics Engine SQL HTTP API. This fills in the `GET /api/admin/reports` and `GET /api/admin/analytics` endpoints that `admin-dashboard` stubbed.

## Architecture
- **No new tables.** This spec is read-only aggregation over upstream data. It consumes, by exact name:
  - `zync_subscriptions` (cols: `tenant_id`, `tier`, `status`, `period`, `current_period_start`, `current_period_end`, `trial_ends_at`, `canceled_at`, `created_at`) — MRR/ARR, churn, trial conversion.
  - `tenants` (cols: `id`, `slug`, `name`, `tier`, `created_at`) — tenant identity, signup date, joins.
  - `tenant_audit_log` (cols: `tenant_id`, `event_type`, `entity_type`, `created_at`) — module adoption (active tenant = ≥1 write event for a module's event-type prefix in period).
  - `ai_usage_log` (cols: `tenant_id`, `input_tokens`, `output_tokens`, `total_tokens`, `cost_usd`, `created_at`) — AI usage tab.
  - `users` — last login per tenant in the Tenants tab. **Spec/schema gap:** the locked foundation `users` table (`id, email, password_hash, email_verified_at, status, created_at`) has NO last-login column, yet both this spec and admin-dashboard's Overview ("logged in last 30d") require one. This plan resolves it with an additive schema delta `ALTER TABLE users ADD COLUMN last_login_at TIMESTAMPTZ` (Task 2a) plus a write on successful login.
  - Cloudflare **Analytics Engine** via the SQL HTTP API (binding `ANALYTICS_ENGINE` is write-only; reads use `CF_ANALYTICS_READ_TOKEN`) — Funnel Health tab. Funnel event names (blob1): `catalog_view`, `lead_captured`, `proposal_accepted`, `invoice_paid`.
- **Auth:** the admin session (`session.type === 'admin'`, `session.totp_verified === true`) carries `role_id` → `admin_roles` (not a `role` string), and admin-dashboard established `requireAdminSession()` + `requireAdminPermission(perm)` middleware. Every route here is guarded by `requireAdminSession()` **and** `requireAdminPermission('admin.reports:view')` (a new permission key seeded to the `SUPER_ADMIN` admin role only, per the spec's "SUPER_ADMIN only" requirement). No tenant JWT, no `tenantQuery`. Cross-tenant raw queries run through `systemQuery` (un-scoped DB handle from `@zync/db`).
- **Data flow:** Admin SPA (`apps/zync-admin`) page → React Query hook → `GET /api/admin/reports?tab=…` or `GET /api/admin/analytics?tab=…` on the admin Hono app → aggregation service in `packages/admin-analytics` → Drizzle queries against Neon (and AE SQL fetch for funnel) → typed JSON. CSV export hits `GET /api/admin/reports/export`.
- **Pricing:** AI cost is read directly from `ai_usage_log.cost_usd` (already computed at call time by `calculateCost`). ILS display values are converted from USD using a fixed configured rate `ADMIN_REPORTS_USD_ILS_RATE` (env, default `3.7`); UI labels the figure "est."

## Tech Stack
- **API:** `apps/zync-admin` Hono routes (admin Worker), Drizzle ORM over Neon Postgres via Hyperdrive, `@zync/db` (`systemQuery`, `createDb`), `@zync/auth` (`requireAdminSession`).
- **Aggregation package:** new `packages/admin-analytics` (TypeScript, no UI) — exports the aggregation functions and shared response types.
- **AE reads:** `fetch` to `https://api.cloudflare.com/client/v4/accounts/{ACCOUNT_ID}/analytics_engine/sql` with `Authorization: Bearer ${CF_ANALYTICS_READ_TOKEN}`.
- **UI:** `apps/zync-admin` React SPA, `@zync/ui` (`Card`, `StatCard`, `DataTable`, `Tabs`, `Button`, `Select`, `EmptyState`, `Skeleton`, `ErrorState`), TanStack Query, a lightweight chart lib (`recharts`) for line/bar charts.
- **Bindings/env:** `HYPERDRIVE`/Neon connection, `ANALYTICS_ENGINE` (declared; not read here), `CF_ANALYTICS_READ_TOKEN`, `CF_ACCOUNT_ID`, `ADMIN_REPORTS_USD_ILS_RATE`.
- **Cross-cutting:** CSP preserved (admin app strict CSP, no inline scripts; charts render to SVG/canvas without `eval`); SUPER_ADMIN role compared with `timingSafeEqual` is not required (role string is not a secret) but the admin session token verification already uses `verifyAdminSession` with timing-safe token compare. A11y: every chart has an adjacent data table (the spec's tables ARE the accessible equivalent), `aria-label` on tabs, `role="table"` semantics from `DataTable`. i18n/RTL: admin UI strings via `@zync/types` translations; numbers/currency via locale formatter; tables `dir`-aware. prefers-reduced-motion: charts disable entrance animation when the media query matches.

## Wave Plan
| Sub-wave | Tasks | Files touched | Parallelizable? |
|----------|-------|---------------|-----------------|
| A | 1 | `packages/admin-analytics` types + scaffold | No (foundation for rest) |
| B | 2, 3, 4 | reports aggregations, analytics aggregations, AE funnel reader | Yes (independent services, all depend on Task 1) |
| C | 5, 6 | reports route, analytics route | Yes (depend on B) |
| D | 7 | CSV export route | No (depends on 5) |
| E | 8, 9 | React Query hooks, shared admin chart/table UI primitives | Yes (depend on C types) |
| F | 10, 11 | `/admin/reports` page, `/admin/analytics` page | Yes (depend on E) |
| G | 12 | wiring into admin nav + route guard | No (depends on F) |

## Tasks

### Task 1: `packages/admin-analytics` package scaffold & shared types
**Blocks:** 2, 3, 4, 5, 6, 7, 8  ·  **Blocked by:** —
**Files:**
- Create: `packages/admin-analytics/package.json`
- Create: `packages/admin-analytics/tsconfig.json`
- Create: `packages/admin-analytics/src/index.ts`
- Create: `packages/admin-analytics/src/types.ts`
**Steps:**
- [ ] Create the package `@zync/admin-analytics` with `"type": "module"`, deps on `@zync/db`, `@zync/types`, `drizzle-orm`, `zod`. Add to the Turborepo workspace (pnpm workspace already globs `packages/*`).
- [ ] Define the canonical query-param schema and all response types in `types.ts` (transcribed below). Export them from `index.ts`.
- [ ] Export a `ReportsTab = 'revenue' | 'signups' | 'tenants'` and `AnalyticsTab = 'modules' | 'ai' | 'funnel'` union, plus a `DateRange` type.
- [ ] Define `MODULE_EVENT_PREFIXES` — the map used by module-adoption inference from `tenant_audit_log.event_type` / `entity_type`.
**Schema / Interfaces:**
```ts
import { z } from 'zod'

export type ReportsTab = 'revenue' | 'signups' | 'tenants'
export type AnalyticsTab = 'modules' | 'ai' | 'funnel'

// from=YYYY-MM-DD, to=YYYY-MM-DD; default = current calendar month
export const dateRangeSchema = z.object({
  from: z.string().regex(/^\d{4}-\d{2}-\d{2}$/),
  to: z.string().regex(/^\d{4}-\d{2}-\d{2}$/),
})
export type DateRange = z.infer<typeof dateRangeSchema>

export const reportsQuerySchema = dateRangeSchema.partial().extend({
  tab: z.enum(['revenue', 'signups', 'tenants']).default('revenue'),
})
export const analyticsQuerySchema = dateRangeSchema.partial().extend({
  tab: z.enum(['modules', 'ai', 'funnel']).default('modules'),
})
export const reportsExportQuerySchema = reportsQuerySchema.extend({
  format: z.literal('csv').default('csv'),
})

// Monthly list-price ILS per tier (matches zync-subscription tiers).
// enterprise/white_label list prices used for estimate; custom deals tracked outside Zync.
export const TIER_MONTHLY_ILS: Record<string, number> = {
  freelancer: 0,
  business: 89,
  enterprise: 159,
  white_label: 250,
}

// --- Reports: Revenue tab ---
export interface PlanBreakdownRow {
  tier: string            // 'freelancer' | 'business' | 'enterprise' | 'white_label'
  tenants: number
  mrrIls: number
  pctOfMrr: number        // 0..100
}
export interface MrrPoint { month: string; mrrIls: number } // month = 'YYYY-MM'
export interface RevenueReport {
  mrrIls: number
  arrIls: number
  activeTenants: number
  mrrTrend: MrrPoint[]    // last 12 months ending at range.to
  breakdown: PlanBreakdownRow[]
}

// --- Reports: Signups & Churn tab ---
export interface SignupBar { month: string; byTier: Record<string, number> }
export interface ChurnByTierRow { tier: string; churned: number }
export interface SignupsReport {
  signupsByMonth: SignupBar[]          // last 12 months, stacked by tier
  newSignups: number                   // within range
  churned: number                      // within range
  net: number                          // newSignups - churned
  trialConversionPct: number           // last 90 days, 0..100
  avgTrialDurationDays: number
  churnByTier: ChurnByTierRow[]
}

// --- Reports: Tenants tab ---
export interface TenantReportRow {
  slug: string
  name: string
  status: string          // zync_subscriptions.status
  tier: string
  mrrIls: number
  signupDate: string      // ISO date
  lastLoginAt: string | null
}
export interface TenantsReport {
  rows: TenantReportRow[]
  total: number
  page: number
  pageSize: number
}

// --- Analytics: Module adoption tab ---
export interface ModuleAdoptionRow {
  moduleId: string        // one of the keys of MODULE_EVENT_PREFIXES (invoices, time, expenses, marketing, ai, contracts)
  moduleLabel: string
  activeTenants: number
  adoptionPct: number     // relative to active-tenant count, 0..100
  events: number
}
export interface ModuleAdoptionReport {
  activeTenantCount: number
  rows: ModuleAdoptionRow[]
}

// --- Analytics: AI usage tab ---
export interface AiConsumerRow {
  tenantSlug: string
  tenantName: string
  inputTokens: number
  outputTokens: number
  costIls: number
}
export interface AiUsageReport {
  totalInputTokens: number
  totalOutputTokens: number
  totalCostIls: number    // est., converted from sum(cost_usd) * usdIlsRate
  topConsumers: AiConsumerRow[]
}

// --- Analytics: Funnel tab ---
export interface FunnelStep {
  event: string           // 'catalog_view' | 'lead_captured' | 'proposal_accepted' | 'invoice_paid'
  count: number
  pctOfPrev: number       // conversion vs previous step, 0..100 (100 for first)
}
export interface FunnelReport { steps: FunnelStep[] }

// event_type / entity_type prefixes that signal each module's usage in tenant_audit_log
export const MODULE_EVENT_PREFIXES: Record<string, { label: string; prefixes: string[] }> = {
  invoices:  { label: 'Invoices',        prefixes: ['invoice'] },
  time:      { label: 'Time Tracking',   prefixes: ['time_entry', 'time'] },
  expenses:  { label: 'Expenses',        prefixes: ['expense'] },
  marketing: { label: 'Marketing / CRM', prefixes: ['lead', 'campaign', 'catalog', 'ticket', 'customer'] },
  ai:        { label: 'AI Assistant',    prefixes: ['ai'] },
  contracts: { label: 'Contracts',       prefixes: ['contract'] },
}
```
**Acceptance:**
- [ ] `pnpm --filter @zync/admin-analytics build` (or `tsc --noEmit`) passes.
- [ ] All response types and zod schemas are exported from `@zync/admin-analytics`.

### Task 2: Reports aggregation service (revenue, signups/churn, tenants)
**Blocks:** 5, 7  ·  **Blocked by:** 1
**Files:**
- Create: `packages/admin-analytics/src/reports.ts`
- Modify: `packages/admin-analytics/src/index.ts`
**Steps:**
- [ ] Implement `getRevenueReport(db, range)`. Per-subscription monthly revenue = `TIER_MONTHLY_ILS[tier]` for `period = 'monthly'` or `period IS NULL`, and `TIER_MONTHLY_ILS[tier]` (annual list price equals 12× the monthly list price in `TIER_MONTHLY_ILS`, so the per-month contribution is the same `TIER_MONTHLY_ILS[tier]`) for `period = 'annual'` — i.e. normalize annual plans to their monthly-equivalent value. MRR = `SUM(monthlyRevenue)` over rows in `zync_subscriptions` where `status = 'active'` (one row per tenant; table has `UNIQUE(tenant_id)`). Annualize for ARR (`mrrIls * 12`). `activeTenants` = count of those rows. Build `breakdown` grouped by `tier` with per-tier tenant count, summed MRR, and `pctOfMrr = mrrIls / totalMrr * 100` (0 when totalMrr is 0). Build `mrrTrend` as 12 monthly points ending at `range.to`: for each month, MRR of subscriptions whose `current_period_start <= monthEnd AND (canceled_at IS NULL OR canceled_at > monthEnd) AND status IN ('active','trialing')`.
- [ ] Implement `getSignupsReport(db, range)`. `newSignups` = count of `zync_subscriptions` (or `tenants`) created within `[from,to]`. `churned` = count of subscriptions whose `status` is `canceled` or `past_due` and whose `canceled_at` (or `created_at` fallback for `past_due`) falls within range. `net = newSignups - churned`. `signupsByMonth` = last 12 months, each bucketed by `tier` at signup. `trialConversionPct` over last 90 days = tenants that entered `trialing` and reached `active` within 30 days, divided by tenants that entered trial. `avgTrialDurationDays` = average of `(activated_at - trial_started)` for converted trials; approximate using `current_period_start - (trial_ends_at - INTERVAL '14 days')` when explicit timestamps are absent. `churnByTier` = churned count grouped by tier.
- [ ] Implement `getTenantsReport(db, range, page, pageSize)`. LEFT JOIN `tenants` to `zync_subscriptions` on `tenant_id`, and LEFT JOIN a per-tenant `MAX(users.last_login_at)` subquery (grouped by the user's tenant via `tenant_memberships`) for `lastLoginAt` (relies on the Task 2a `users.last_login_at` column; `null` for tenants whose members never logged in). Each row: slug, name, status, tier, `mrrIls = TIER_MONTHLY_ILS[tier] when status='active' else 0`, `signupDate = tenants.created_at`, `lastLoginAt`. Order by `tenants.created_at DESC`. Paginate with `clampLimit`/`buildPaginated` conventions from `@zync/db` if available, else `LIMIT/OFFSET`.
- [ ] All queries use the un-scoped `systemQuery` handle (cross-tenant). Never wrap in `tenantQuery`.
- [ ] Export `getRevenueReport`, `getSignupsReport`, `getTenantsReport`.
**Schema / Interfaces:**
```ts
import type { DB } from '@zync/db'
import type { DateRange, RevenueReport, SignupsReport, TenantsReport } from './types'

export function getRevenueReport(db: DB, range: DateRange): Promise<RevenueReport>
export function getSignupsReport(db: DB, range: DateRange): Promise<SignupsReport>
export function getTenantsReport(
  db: DB, range: DateRange, page: number, pageSize: number,
): Promise<TenantsReport>
```
**Acceptance:**
- [ ] Given a seeded set of `zync_subscriptions`, `getRevenueReport` returns `mrrIls` equal to the hand-summed plan prices of active tenants and `breakdown` percentages summing to ~100.
- [ ] `getSignupsReport` computes `net = newSignups - churned` and `trialConversionPct` within `[0,100]`.
- [ ] `getTenantsReport` paginates and returns `total` matching the un-paginated count.

### Task 2a: `users.last_login_at` schema delta + login write
**Blocks:** 2  ·  **Blocked by:** —
**Files:**
- Create: `packages/db/migrations/0001_add_users_last_login_at.sql` (use the repo's next sequential migration number/timestamp)
- Modify: `packages/db/src/schema/users.ts` (Drizzle column definition)
- Modify: the login handler that issues a session (foundation-auth-rbac `POST /api/auth/login` route) to stamp `last_login_at`.
**Steps:**
- [ ] Add the additive column (nullable, no default backfill required) to the `users` table.
- [ ] Add `lastLoginAt: timestamp('last_login_at', { withTimezone: true })` to the Drizzle `users` schema.
- [ ] On successful authenticated login, `UPDATE users SET last_login_at = now() WHERE id = :userId`. This is the data source for both this spec's Tenants tab and admin-dashboard's "active users (last 30d)" metric.
**Schema / Interfaces:**
```sql
ALTER TABLE users ADD COLUMN last_login_at TIMESTAMPTZ;
-- NULL = user has never logged in since this column was introduced.
CREATE INDEX idx_users_last_login ON users(last_login_at);
```
**Acceptance:**
- [ ] Migration applies cleanly on Neon; column is nullable.
- [ ] A successful login stamps `last_login_at`; the Tenants tab shows a non-null value for tenants with recent logins.

### Task 3: Analytics aggregation service (module adoption, AI usage)
**Blocks:** 6, 7  ·  **Blocked by:** 1
**Files:**
- Create: `packages/admin-analytics/src/analytics.ts`
- Modify: `packages/admin-analytics/src/index.ts`
**Steps:**
- [ ] Implement `getModuleAdoptionReport(db, range)`. `activeTenantCount` = distinct `tenant_id` in `zync_subscriptions` where `status IN ('active','trialing')`. For each module in `MODULE_EVENT_PREFIXES`, count distinct `tenant_id` and total events in `tenant_audit_log` where `created_at` within range AND (`event_type` starts with any of the module's prefixes OR `entity_type` equals one of those prefixes) — e.g. for `invoices`: `event_type LIKE 'invoice%' OR entity_type = 'invoice'`. `adoptionPct = activeTenants / activeTenantCount * 100` (0 when denominator is 0). Order rows by `activeTenants DESC`.
- [ ] Implement `getAiUsageReport(db, range, usdIlsRate)`. From `ai_usage_log` where `created_at` within range: `totalInputTokens = SUM(input_tokens)`, `totalOutputTokens = SUM(output_tokens)`, `totalCostIls = SUM(cost_usd) * usdIlsRate`. `topConsumers` = top 10 tenants by `SUM(cost_usd)`, joined to `tenants` for `slug`/`name`, each with summed input/output tokens and `costIls = sumCostUsd * usdIlsRate`.
- [ ] Use `systemQuery` (cross-tenant). Round ILS to whole shekels for display but keep token sums exact.
- [ ] Export `getModuleAdoptionReport`, `getAiUsageReport`.
**Schema / Interfaces:**
```ts
import type { DB } from '@zync/db'
import type { DateRange, ModuleAdoptionReport, AiUsageReport } from './types'

export function getModuleAdoptionReport(db: DB, range: DateRange): Promise<ModuleAdoptionReport>
export function getAiUsageReport(
  db: DB, range: DateRange, usdIlsRate: number,
): Promise<AiUsageReport>
```
**Acceptance:**
- [ ] `getModuleAdoptionReport` returns one row per module key with `adoptionPct ∈ [0,100]`.
- [ ] `getAiUsageReport` totals equal hand-summed `ai_usage_log` columns over the range, and `topConsumers.length <= 10`.

### Task 4: Analytics Engine funnel reader
**Blocks:** 6  ·  **Blocked by:** 1
**Files:**
- Create: `packages/admin-analytics/src/funnel.ts`
- Modify: `packages/admin-analytics/src/index.ts`
**Steps:**
- [ ] Implement `getFunnelReport(env, range)` that issues a SQL query to the CF Analytics Engine SQL HTTP API: `POST https://api.cloudflare.com/client/v4/accounts/${env.CF_ACCOUNT_ID}/analytics_engine/sql` with header `Authorization: Bearer ${env.CF_ANALYTICS_READ_TOKEN}` and a `text/plain` body of the SQL.
- [ ] SQL (built as a template literal): `` SELECT blob1 AS event, count() AS count FROM ${env.CF_ANALYTICS_DATASET ?? 'zync_events'} WHERE timestamp >= toDateTime('${range.from} 00:00:00') AND timestamp < toDateTime('${range.to} 00:00:00') + INTERVAL '1' DAY AND blob1 IN ('catalog_view','lead_captured','proposal_accepted','invoice_paid') GROUP BY blob1 ``. The dataset name is the one bound to `ANALYTICS_ENGINE` (env `CF_ANALYTICS_DATASET`, default `'zync_events'`). `blob1` is the event-name field written by funnel emitters. Sanitize `range.from`/`range.to` against the `YYYY-MM-DD` regex (already validated by `dateRangeSchema`) before interpolation to prevent SQL injection into the AE query.
- [ ] Order the four steps in funnel order (`catalog_view → lead_captured → proposal_accepted → invoice_paid`), filling missing events with count 0. Compute `pctOfPrev`: first step = 100; each subsequent = `count / prevCount * 100` (0 when prevCount is 0).
- [ ] On AE fetch failure or missing token, throw a typed `FunnelUnavailableError` so the route can return a 503 with a "funnel data unavailable" message rather than crashing the whole analytics response.
- [ ] Export `getFunnelReport` and `FunnelUnavailableError`.
**Schema / Interfaces:**
```ts
import type { FunnelReport } from './types'

export interface FunnelEnv {
  CF_ACCOUNT_ID: string
  CF_ANALYTICS_READ_TOKEN: string
  CF_ANALYTICS_DATASET?: string   // default 'zync_events'
}
export class FunnelUnavailableError extends Error {}
export function getFunnelReport(env: FunnelEnv, range: DateRange): Promise<FunnelReport>
```
**Acceptance:**
- [ ] Given a mocked AE HTTP response, `getFunnelReport` returns exactly 4 ordered steps with correct `pctOfPrev` chaining.
- [ ] Missing `CF_ANALYTICS_READ_TOKEN` produces `FunnelUnavailableError`, not an unhandled throw.

### Task 5: `GET /api/admin/reports` route
**Blocks:** 7, 8  ·  **Blocked by:** 2
**Files:**
- Create: `apps/zync-admin/src/routes/reports.ts`
- Modify: `apps/zync-admin/src/app.ts` (mount the route)
**Steps:**
- [ ] Add a Hono route `GET /api/admin/reports` guarded by `requireAdminSession()` then `requireAdminPermission('admin.reports:view')` middleware (this permission is seeded to the `SUPER_ADMIN` admin role only; non-SUPER_ADMIN admins get 403). Seed the `admin.reports:view` permission key in the admin permissions seed (same seed that admin-dashboard introduced for `admin.roles:manage`).
- [ ] Validate query with `reportsQuerySchema`. Default `from`/`to` to the first and last day of the current calendar month (server time) when absent.
- [ ] Resolve the un-scoped DB handle via `systemQuery(env)` / `createDb(env)`.
- [ ] Dispatch by `tab`: `revenue` → `getRevenueReport`; `signups` → `getSignupsReport`; `tenants` → `getTenantsReport(db, range, page, pageSize)` reading `page` (default 1) and `pageSize` (default 25, `clampLimit` to max 100) from query.
- [ ] Return `{ tab, range, data }` as JSON. Set `Cache-Control: private, no-store` (admin-only data) and the app's standard strict CSP headers.
- [ ] On error, return the platform standard `ApiError` JSON shape.
**Schema / Interfaces:**
```
GET /api/admin/reports?tab=revenue|signups|tenants&from=YYYY-MM-DD&to=YYYY-MM-DD&page&pageSize
auth: requireAdminSession() + requireAdminPermission('admin.reports:view') [SUPER_ADMIN-only]
200: { tab, range: { from, to }, data: RevenueReport | SignupsReport | TenantsReport }
403: not SUPER_ADMIN   400: invalid query
```
**Acceptance:**
- [ ] A non-admin session → 401; an admin session that is not SUPER_ADMIN → 403.
- [ ] `?tab=revenue` with no dates returns current-month revenue; `?tab=tenants&page=2` paginates.
- [ ] Route never imports or calls `tenantQuery`.

### Task 6: `GET /api/admin/analytics` route
**Blocks:** 8  ·  **Blocked by:** 3, 4
**Files:**
- Create: `apps/zync-admin/src/routes/analytics.ts`
- Modify: `apps/zync-admin/src/app.ts` (mount the route)
**Steps:**
- [ ] Add `GET /api/admin/analytics` guarded by `requireAdminSession()` + SUPER_ADMIN role check (403 otherwise).
- [ ] Validate query with `analyticsQuerySchema`; default date range to current calendar month (modules tab spec says "last 30 days" — when no range provided, default to `[today-30d, today]` for `modules` and `ai`, current month for parity is acceptable; document the chosen default in the response `range`).
- [ ] Dispatch by `tab`: `modules` → `getModuleAdoptionReport(db, range)`; `ai` → `getAiUsageReport(db, range, usdIlsRate)` where `usdIlsRate = Number(env.ADMIN_REPORTS_USD_ILS_RATE ?? 3.7)`; `funnel` → `getFunnelReport(env, range)`.
- [ ] For the `funnel` tab, catch `FunnelUnavailableError` and return 503 `{ error: 'funnel_unavailable' }` (UI shows an ErrorState, other tabs unaffected).
- [ ] Return `{ tab, range, data }`. `Cache-Control: private, no-store`, strict CSP headers.
**Schema / Interfaces:**
```
GET /api/admin/analytics?tab=modules|ai|funnel&from=YYYY-MM-DD&to=YYYY-MM-DD
auth: requireAdminSession() + requireAdminPermission('admin.reports:view') [SUPER_ADMIN-only]
200: { tab, range, data: ModuleAdoptionReport | AiUsageReport | FunnelReport }
503: { error: 'funnel_unavailable' }  (funnel tab only)
403/400 as above
```
**Acceptance:**
- [ ] `?tab=ai` returns ILS totals derived from `ai_usage_log.cost_usd * rate`.
- [ ] `?tab=funnel` with AE reachable returns 4 steps; with AE unreachable returns 503 without breaking other tabs.

### Task 7: `GET /api/admin/reports/export` CSV route
**Blocks:** —  ·  **Blocked by:** 2, 5
**Files:**
- Create: `apps/zync-admin/src/routes/reports-export.ts`
- Modify: `apps/zync-admin/src/app.ts` (mount the route)
**Steps:**
- [ ] Add `GET /api/admin/reports/export` guarded by `requireAdminSession()` + `requireAdminPermission('admin.reports:view')`.
- [ ] Validate with `reportsExportQuerySchema` (`tab`, `from`, `to`, `format=csv`).
- [ ] Reuse the Task 2 aggregation functions to fetch the active tab's data, then serialize to CSV:
  - `revenue` → header `tier,tenants,mrr_ils,pct_of_mrr` + breakdown rows, then a totals row.
  - `signups` → header `month,tier,signups` from `signupsByMonth` plus a summary block (`new_signups,churned,net,trial_conversion_pct,avg_trial_days`).
  - `tenants` → header `slug,name,status,tier,mrr_ils,signup_date,last_login_at`, all rows (no pagination for export; stream/iterate to avoid loading excessive rows at once if count is large).
- [ ] Escape CSV fields (quote fields containing comma/quote/newline; double internal quotes). Prevent CSV injection: prefix any field starting with `=`, `+`, `-`, `@`, tab, or CR with a single quote.
- [ ] Respond with `Content-Type: text/csv; charset=utf-8` and `Content-Disposition: attachment; filename="zync-reports-${tab}-${from}_${to}.csv"` (interpolating the validated tab and date strings).
**Schema / Interfaces:**
```
GET /api/admin/reports/export?tab=revenue|signups|tenants&from=YYYY-MM-DD&to=YYYY-MM-DD&format=csv
auth: requireAdminSession() + requireAdminPermission('admin.reports:view') [SUPER_ADMIN-only]
200: text/csv attachment
```
**Acceptance:**
- [ ] Export of each tab produces a well-formed CSV with a header row.
- [ ] A tenant name containing a comma or a leading `=` is correctly escaped/neutralized (no injection).

### Task 8: Admin React Query hooks
**Blocks:** 10, 11  ·  **Blocked by:** 5, 6
**Files:**
- Create: `apps/zync-admin/src/features/reports/useAdminReports.ts`
- Create: `apps/zync-admin/src/features/analytics/useAdminAnalytics.ts`
**Steps:**
- [ ] Implement `useAdminReports(tab, range, page?)` → TanStack Query fetching `/api/admin/reports`, typed against `RevenueReport | SignupsReport | TenantsReport` (discriminated by `tab`). `queryKey: ['admin','reports',tab,range,page]`.
- [ ] Implement `useAdminAnalytics(tab, range)` → fetch `/api/admin/analytics`, typed against the analytics response union. Treat 503 on funnel as a recoverable error surfaced to the component (do not throw to error boundary).
- [ ] Provide `exportReportsCsv(tab, range)` helper that triggers a browser download of `/api/admin/reports/export` (anchor with `download` attr, credentials included).
- [ ] Import all response types from `@zync/admin-analytics`.
**Acceptance:**
- [ ] Hooks compile against the `@zync/admin-analytics` types; switching `tab` refetches via queryKey change.

### Task 9: Shared admin chart + metric UI primitives
**Blocks:** 10, 11  ·  **Blocked by:** 1
**Files:**
- Create: `apps/zync-admin/src/components/DateRangePicker.tsx`
- Create: `apps/zync-admin/src/components/MetricRow.tsx`
- Create: `apps/zync-admin/src/components/ReportLineChart.tsx`
- Create: `apps/zync-admin/src/components/ReportBarChart.tsx`
**Steps:**
- [ ] `DateRangePicker` — month-default range control (default current month) built on `@zync/ui` `Select`/`Popover`; emits `{ from, to }`. Keyboard-navigable, labeled, `dir`-aware.
- [ ] `MetricRow` — horizontal set of `@zync/ui` `StatCard`s (e.g. MRR / ARR / Active tenants). Pure presentational.
- [ ] `ReportLineChart` / `ReportBarChart` — thin `recharts` wrappers (line for MRR-over-12-months, stacked bar for signups-by-tier). Each renders an `aria-hidden` chart plus a visually-hidden accessible summary; the real accessible data lives in the adjacent `DataTable`. Disable animation when `prefers-reduced-motion: reduce` matches (via a `useReducedMotion` check). Use design-system color tokens only (no hardcoded hex) — pull series colors from CSS custom properties / theme tokens.
- [ ] Charts must not use inline event handlers that violate CSP; render SVG via recharts (no `eval`).
**Acceptance:**
- [ ] Charts render with token-driven colors and honor reduced-motion.
- [ ] `DateRangePicker` defaults to the current calendar month and is operable by keyboard.

### Task 10: `/admin/reports` page (Revenue · Signups & Churn · Tenants tabs)
**Blocks:** 12  ·  **Blocked by:** 8, 9
**Files:**
- Create: `apps/zync-admin/src/pages/ReportsPage.tsx`
- Create: `apps/zync-admin/src/features/reports/RevenueTab.tsx`
- Create: `apps/zync-admin/src/features/reports/SignupsTab.tsx`
- Create: `apps/zync-admin/src/features/reports/TenantsTab.tsx`
**Steps:**
- [ ] `ReportsPage` renders `@zync/ui` `Tabs` (Revenue / Signups & Churn / Tenants), a `DateRangePicker` (default current month), and an "Export CSV" `Button` that calls `exportReportsCsv(activeTab, range)`.
- [ ] `RevenueTab`: `MetricRow` for MRR / ARR / Active tenants, a `ReportLineChart` of `mrrTrend`, and a `DataTable` of `breakdown` (Plan, Tenants, MRR, % of MRR). Use locale currency formatting (ILS) for money.
- [ ] `SignupsTab`: `ReportBarChart` of `signupsByMonth` (stacked by tier), a `MetricRow` (New signups / Churned / Net), trial-conversion % and avg trial duration text, and a small "Churn by plan" table.
- [ ] `TenantsTab`: `DataTable` (Name, Slug, Status badge, Plan, MRR, Signup date, Last login) with `DataTablePagination`; each row links to `/admin/tenants/:slug`. Use `Badge` for status (active→green, trialing→blue, past_due→amber, canceled→red).
- [ ] Wire loading (`Skeleton`), empty (`EmptyState`), and error (`ErrorState`) states for each tab. Format dates via the locale/Hebrew date formatter; tables `dir`-aware for RTL.
**Acceptance:**
- [ ] All three tabs render with live data; changing the date range refetches.
- [ ] Export CSV button downloads the active tab's CSV.
- [ ] Each tenant row navigates to `/admin/tenants/:slug`.

### Task 11: `/admin/analytics` page (Module Adoption · AI Usage · Funnel tabs)
**Blocks:** 12  ·  **Blocked by:** 8, 9
**Files:**
- Create: `apps/zync-admin/src/pages/AnalyticsPage.tsx`
- Create: `apps/zync-admin/src/features/analytics/ModuleAdoptionTab.tsx`
- Create: `apps/zync-admin/src/features/analytics/AiUsageTab.tsx`
- Create: `apps/zync-admin/src/features/analytics/FunnelTab.tsx`
**Steps:**
- [ ] `AnalyticsPage` renders `Tabs` (Module Adoption / AI Usage / Funnel) and a `DateRangePicker`.
- [ ] `ModuleAdoptionTab`: `DataTable` (Module, Active Tenants, Adoption %, Events) using `getModuleAdoptionReport` data; header notes the active-tenant denominator.
- [ ] `AiUsageTab`: `MetricRow` (Total input tokens / Total output tokens / Total cost est. ₪) and a "Top consumers" `DataTable` (Tenant, Input Tokens, Output Tokens, Cost). Label cost with an "(est.)" suffix.
- [ ] `FunnelTab`: render the 4-step funnel (`catalog_view → lead_captured → proposal_accepted → invoice_paid`) with counts and `pctOfPrev` percentages; on 503 `funnel_unavailable`, show an `ErrorState` ("Funnel data temporarily unavailable") without affecting the other tabs.
- [ ] Loading/empty/error states per tab; number formatting via locale formatter; RTL-aware.
**Acceptance:**
- [ ] Module adoption rows show adoption % vs active tenants.
- [ ] AI usage cost is labeled "est." and derived from `cost_usd * rate`.
- [ ] Funnel tab degrades gracefully when AE is unavailable.

### Task 12: Wire pages into admin nav + SUPER_ADMIN route guards
**Blocks:** —  ·  **Blocked by:** 10, 11
**Files:**
- Modify: `apps/zync-admin/src/router.tsx` (or the admin route table)
- Modify: `apps/zync-admin/src/components/AdminNav.tsx`
**Steps:**
- [ ] Register routes `/admin/reports` → `ReportsPage` and `/admin/analytics` → `AnalyticsPage` (the nav items already exist per admin-dashboard; ensure they point to these pages).
- [ ] Add a client-side guard so both routes render only when `adminSession.role === 'SUPER_ADMIN'`; otherwise show a 403/forbidden view (server already enforces 403 — this is UI affordance, hide the nav items for non-SUPER_ADMIN admins).
- [ ] Ensure the admin app's strict CSP and security headers cover the new pages (no inline scripts/styles introduced by recharts; nonce or hashed if needed).
**Acceptance:**
- [ ] SUPER_ADMIN sees Reports and Analytics nav items and can open both pages.
- [ ] A non-SUPER_ADMIN admin does not see the nav items and is blocked (403) if navigating directly.