Custom Dashboard Views

Overview

Brands using Ezra can ask for custom dashboards in natural language:

> "Build me a view showing only fitness affiliates with EPC over $5, sorted by revenue."

Ezra translates the request into a structured query DSL and saves it as a dashboard_views row. The view is rendered at a stable URL the brand's team can bookmark or share:

` https://trcker.io/v/[brand-slug]/[view-slug] `

Sprint 1 ships one display type: ranked-list — a sortable table of partners with aggregated metrics (clicks, conversions, revenue, EPC, CVR) over a date window. Other display types (time-series, breakdown-table) come later.

How brands use it

Brands talk to Ezra; they don't see the DSL or the API. A typical conversation:

> Brand: "Build me a dashboard of my top 20 finance affiliates by revenue in the last 90 days." > > Ezra: "Done. Bookmark this: trcker.io/v/yourbrand/top-finance-90d"

Ezra is responsible for: - Picking a slug (lowercase, hyphen-separated, derived from the request) - Constructing the query DSL from the brand's natural-language description - Re-posting with the same slug when the brand asks to iterate ("show me top 30 instead") — the endpoint is upsert-on-(brand, slug)

Endpoint

POST /api/public/views/create

Service-key authenticated. See Ezra Integration for the auth contract.

Request body: `json { "brandId": "uuid", "slug": "top-fitness-affiliates", "name": "Top fitness affiliates", "description": "Partners in fitness with high EPC", "queryDsl": { "source": "partners", "filters": [ { "field": "type", "op": "eq", "value": "affiliate" }, { "field": "epc_cents", "op": "gt", "value": 500 } ], "sort": { "field": "total_revenue_cents", "dir": "desc" }, "limit": 50, "dateRange": { "type": "rolling", "days": 30 } }, "displayType": "ranked-list", "createdByLabel": "Sarah Chen via Ezra" } `

Response (201 Created for fresh, 200 OK for upsert): `json { "data": { "viewId": "uuid", "slug": "top-fitness-affiliates", "hostedUrl": "https://trcker.io/v/bedrock-fitness/top-fitness-affiliates", "brandSlug": "bedrock-fitness", "alreadyExisted": false } } `

Idempotent on (brand, slug) — re-posting with the same slug updates the existing view's DSL/name/description, so Ezra can iterate ("show top 30 instead of top 20") without managing version numbers.

Query DSL reference

The DSL is stored as JSONB so it can evolve without migrations. v0 supports:

Source: "partners" only. (Future: "conversions", "clicks".)

Filterable fields (validated by Zod allow-list — no arbitrary SQL):

| Field | Type | Notes | |---|---|---| | name | string | Partner display name | | email | string | Partner email | | type | enum | affiliate / creator | | status | enum | active / paused / pending / banned | | created_at | datetime | When the partner was created | | total_clicks | int | Aggregate clicks in date range | | total_conversions | int | Aggregate conversions in date range | | total_revenue_cents | int | Sum of conversion revenue, cents | | epc_cents | int | Earnings per click, cents (derived) | | cvr | float | Conversion rate 0–1 (derived) |

Operators: eq, ne, gt, gte, lt, lte, in, like. like is ILIKE substring; in takes an array.

Date range: - { type: "rolling", days: 30 } — last N days, max 365 - { type: "fixed", from: "2026-04-01T00:00:00Z", to: "2026-05-01T00:00:00Z" }

Sort: any filterable field, asc or desc. Default: total_revenue_cents desc.

Limit: 1–500. Default: 50.

Hosted view rendering

/v/[brandSlug]/[viewSlug] is a public Next.js server-rendered page. It:

1. Resolves the brand by slug (404 if not found)
2. Resolves the view by (brandId, viewSlug) (404 if not found)
3. Runs the query (see src/app/v/[brandSlug]/[viewSlug]/run-query.ts)
4. Renders results as a table

The page is public by design — the URL is the access control (brand-only knowledge). For more sensitive views, the brand can share only with their team via Slack/email. v1.5 may add optional auth-gated views (requireAuth: true in the DSL).

Query execution notes

• Identity filters (name, email, type, status, created_at) push down to SQL WHERE clauses on the partners table
• Metric filters (clicks, conversions, revenue, EPC, CVR) are computed via aggregation joins to clicks + conversions, then filtered in JavaScript at the application layer
• Test rows (is_test = true) are always excluded
• Date range scopes both clicks and conversions in the aggregation
• For brands with > 10K partners, in-JS metric filtering becomes the bottleneck — v2 will move this to a CTE-based HAVING clause

Field allow-list (security)

Filter and sort fields are validated against partnersFilterFieldEnum in src/lib/schemas/dashboard-view.ts. The renderer maps each enum value to a specific Drizzle column or computed metric. There is no path from user-provided strings to arbitrary SQL columns. Adding a new filterable field requires updating both the Zod enum and the renderer's filterFieldToRowKey map.

Operational notes

• View URLs persist as long as the brand exists. If a brand is deleted, all their views are cascade-deleted (per the ON DELETE CASCADE foreign key).
• Audit: every view records createdByLabel — typically the brand-side user's name or ezra:auto.
• Upsert semantics mean Ezra can safely call POST /views/create on retry without creating duplicates.
• No edit-history is preserved at v0. Updating a view loses the previous DSL.

Roadmap

Out of scope for Sprint 1: - Time-series display (line/bar charts over time) - Breakdown-table display (group by offer, geo, channel, etc.) - Auth-gated views (require WorkOS session) - Conversions and clicks as sources (today: only partners) - More than 20 filters per view - Export to CSV from the hosted page - Edit-history / view versioning

All of these are planned but not built. Ask Ezra to keep the view simple at v0.