[showing first 100 of 336 lines]
# Reports Navigation Hub — Implementation Plan

**Spec:** docs/specs/2026-05-31-reports-navigation-hub.md  ·  **Slug:** reports-navigation-hub  ·  **Wave:** 13
**Depends on:** admin-reports-analytics, api-usage-quota-ui, expense-reports-ui, foundation-auth-rbac, profitability-reports, reports-analytics, revenue-forecasting, tenant-audit-log, time-reports

## Goal
Deliver the `/reports` landing page: a navigation hub that groups all tenant report views (Financial, Operations, CRM & Pipeline, System, Tax & Compliance) into clickable KPI tiles, a shared URL-driven date-range context, and per-user saved report shortcuts. The hub loads every tile headline from a single aggregate summary endpoint (no N+1) and tier-gates the Tax & Compliance section to Business+. This spec owns only the hub surface, the `report_shortcuts` table, the `GET /api/reports/summary` aggregation, and the shortcuts CRUD; the individual report destinations are owned by their upstream specs and merely linked.

## Architecture
The hub is a React route in `zync-app` (`apps/zync-app`). It reads `?from`/`?to` query params (default = this month) and passes them to `GET /api/reports/summary`, which runs one aggregate round-trip over upstream source tables and returns a flat KPI object powering all tiles. Tiles navigate to upstream routes via a static navigation map. The Tax & Compliance group is rendered only when `meetsMinimumTier(tenant.tier, TenantTier.BUSINESS)` is true; the matching backend routes (owned upstream) also guard with `requireTier('business')`.

Upstream tables/exports consumed (read-only, defined by dependencies — do NOT recreate):
- `invoices` (status enum `DRAFT|SENT|APPROVED|TAX_ISSUED|PAID|PARTIALLY_PAID|REJECTED|VOID|WRITTEN_OFF|BAD_DEBT`, columns `total`, `subtotal`, `vat_amount`, `issue_date`, `due_date`, `status`, `source`) — from `invoices-core` / `reports-analytics`.
- `invoice_payments` (columns `paid_at`, `amount`, `source`, `invoice_id`) — from `reports-analytics`.
- `time_entries` (columns `duration_min`, `billable`, period date) — from `time-management`, surfaced by `time-reports`.
- `expenses` (columns `invoice_total`, `status`, `expense_date`, `vat_deductible`, billed/unbilled state) — from `expenses-module`, surfaced by `expense-reports-ui`.
- `leads` (columns `estimated_value`, stage/status) — from `marketing-leads-pipeline`.
- `proposals` (columns `status`, value/`content`) — from `marketing-catalogs-campaigns`.
- `tenant_audit_log` (columns `tenant_id`, `created_at`) — from `tenant-audit-log`.
- API usage call counts — Cloudflare Analytics Engine dataset `api_usage` (from `api-usage-quota-ui`); queried via AE binding `ANALYTICS_ENGINE`.
- New table this spec owns: `report_shortcuts` (FKs to `tenants`, `users`).

Auth/exports consumed: `authMiddleware`, `requirePermission`, `requireTier`, `meetsMinimumTier`, `TenantTier`, `tenantQuery`, `buildPaginated`, `Session`, `useSubscription`/`useTierGate`, `Card`, `StatCard`, `Button`, `Select`, `EmptyState`, `Skeleton`, `useDirection`, `cn`, `translations`, `Db`/`createDb`.

## Tech Stack
- **App:** `apps/zync-app` (Vite + React, React Router, TanStack Query) — hub page, tiles, date-range control, saved-shortcuts panel.
- **API:** `apps/zync-api` (Hono on Cloudflare Workers) — `/api/reports/summary` and `/api/reports/shortcuts` routes.
- **DB:** Neon Postgres via Cloudflare Hyperdrive, Drizzle ORM (`@zync/db`). New table `report_shortcuts`.
- **Bindings:** `ANALYTICS_ENGINE` (AE) for `api_usage` call count; Hyperdrive Postgres binding for relational aggregates.
- **Packages:** `@zync/types` (shared response types), `@zync/ui` (Card/StatCard/Select/etc.), `@zync/auth` (middleware + tier helpers).
- **Validation:** Zod (`require-zod-validation-in-routes`). All route DB access via `tenantQuery` (`no-raw-drizzle-from-routes`).
- **i18n/RTL:** Hebrew labels via `translations`, direction via `useDirection`; Tax tile Hebrew names (מקדמות, מבנה אחיד) preserved.

## Wave Plan
| Sub-wave | Tasks | Files touched | Parallelizable? |
|----------|-------|---------------|-----------------|
| 13.a | Task 1 (schema), Task 2 (Drizzle model + queries) | `packages/db/src/schema/report-shortcuts.ts`, `packages/db/src/queries/reports.ts` | Task 1 then Task 2 |
| 13.b | Task 3 (summary endpoint), Task 4 (shortcuts CRUD) | `apps/zync-api/src/routes/reports.ts` | Parallel after 13.a |
| 13.c | Task 5 (shared types), Task 6 (nav map + tier gate) | `packages/types/src/reports.ts`, `apps/zync-app/src/features/reports/nav-map.ts` | Parallel |
| 13.d | Task 7 (date-range), Task 8 (tile + section), Task 9 (shortcuts panel) | `apps/zync-app/src/features/reports/*` | Parallel after 13.c |
| 13.e | Task 10 (hub page + route) | `apps/zync-app/src/features/reports/ReportsHubPage.tsx`, router | After 13.d |
| 13.f | Task 11 (i18n), Task 12 (a11y + tests) | `packages/i18n`, `*.test.ts(x)` | After 13.e |

## Tasks

### Task 1: `report_shortcuts` table migration
**Blocks:** 2, 4  ·  **Blocked by:** —
**Files:**
- Create: `packages/db/migrations/<timestamp>_report_shortcuts.sql`
**Steps:**
- [ ] Add the `report_shortcuts` DDL exactly as below (canonical Postgres dialect).
- [ ] Add the supporting index for per-user listing.
- [ ] Register the migration in the Drizzle migration journal.
**Schema / Interfaces:**
```sql
CREATE TABLE report_shortcuts (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,
  report_type TEXT NOT NULL CHECK (report_type IN (
                'revenue','invoices','payments','time','expenses',
                'profitability','revenue_forecast','leads','proposals',
                'ar_aging','bad_debt','audit','api_usage',
                'vat','pnl','cashflow','advance_tax','withholding',
                'bituach_leumi','uniform_format')),
  params      JSONB NOT NULL,        -- { from, to, filters... }
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_report_shortcuts_user ON report_shortcuts(tenant_id, user_id, created_at DESC);
```
**Acceptance:**
- [ ] Migration applies on a clean Neon branch with no error.
- [ ] `tenant_id`/`user_id` are UUID FKs to `tenants(id)`/`users(id)`; `params` is JSONB; `created_at` is TIMESTAMPTZ NOT NULL DEFAULT now().
- [ ] `report_type` CHECK rejects values outside the enumerated set.

### Task 2: Drizzle model + shortcut/summary query helpers
**Blocks:** 3, 4  ·  **Blocked by:** 1
**Files:**
- Create: `packages/db/src/schema/report-shortcuts.ts`
- Create: `packages/db/src/queries/reports.ts`
- Modify: `packages/db/src/schema/index.ts` (export new table)
**Steps:**
- [ ] Define the `reportShortcuts` Drizzle pgTable mirroring Task 1 DDL.
- [ ] Implement `listReportShortcuts(db, tenantId, userId)` returning shortcuts ordered by `created_at DESC`.
- [ ] Implement `createReportShortcut(db, tenantId, userId, input)` inserting a row and returning it.
- [ ] Implement `deleteReportShortcut(db, tenantId, userId, id)` deleting only when `tenant_id` AND `user_id` match (ownership in WHERE — never trust id alone).
- [ ] Implement `getReportsSummary(db, env, tenantId, scope, from, to)` running ONE aggregate round-trip plus one AE query for `api_usage`. `scope` is `'own'` (MEMBER) or `'org'` (ADMIN/OWNER): when `'own'`, org financial totals are `null` and time/expenses filter to the caller's `user_id`.
**Schema / Interfaces:**
```ts
// packages/db/src/schema/report-shortcuts.ts
export const reportShortcuts = pgTable('report_shortcuts', {
  id: uuid('id').primaryKey().defaultRandom(),
  tenantId: uuid('tenant_id').notNull().references(() => tenants.id, { onDelete: 'cascade' }),
  userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  name: text('name').notNull(),
  reportType: text('report_type').notNull(),
  params: jsonb('params').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
[truncated]