pipeline-tabs-and-canvas-aggregates.md33.9 KBView on GitHub # Pipeline stage tabs + canvas aggregate summaries
Grounded in the **Aug 5 2026** working session — _"Reform <> Cedar - Collateral, Playbook &
Outbound working Session"_ (Jesse, Isabelle, Brock Parsons; 63 min; event
`0454e704-77d8-465f-933a-46ab7084b0fe` on conversation `f72133d2-…`).
Two deliverables:
1. **Stage tabs** — one Cedar pipeline tab per HubSpot pipeline stage, so Brock's Cedar
pipeline view is a 1:1 mirror of the HubSpot view he lives in today.
2. **Aggregate summaries** — per-tab roll-ups (count / total value / averages) on
conversation canvases, matching the totals HubSpot shows above each pipeline tab.
> **Source note.** Circleback gives Cedar the transcript + AI notes only — the screen share
> is not recoverable from the recording. The HubSpot layout below is therefore taken from
> **screenshots Jesse supplied on 2026-08-11**, cross-checked against Reform's live Cedar
> data. Every stage tab, column, count and dollar figure attributed to HubSpot in this doc
> comes from those screenshots.
**Status: Phases 0–2 shipped 2026-08-11** — Brock's eight stage tabs are live in production
(§2.3, §2.5). Phase 3 onwards — the aggregate summary row — is specified but not built.
---
## 1. What Brock actually asked for
Verbatim from the transcript:
| Timestamp | Quote | Implication |
|---|---|---|
| 39:11 | _"I just want to be able to see into every single deal with every customer name… discovery call phases, we have just next steps. What's the next step and how much is it? **And I have it for each stage.**"_ | One tab per stage — this is the "sanity check" artifact |
| 36:38 | _"I do want visibility into every single account, **how much that account is worth, how much pipeline we have at each stage**, and should be able to drill into it at any point, any day, what accounts are in that stage and how much is in the pipeline for that."_ | Per-stage aggregate: count + summed deal value |
| 35:38 | _"Daily, by stage, by customer, or by prospect. Last activity date, and nothing should be in the past… All revenue should be in the future or assigned certain revenue at a certain stage."_ | Columns: last activity, close date, deal value, next step; overdue is an error state |
| 50:32 (Jesse) | _"I'm gonna make your pipeline view just match your HubSpot exactly, so that there's literally no difference."_ | The commitment |
| 59:33 | _"I have dashboards that are built out, so like monthly forecasts… and then like activity report… demos booked by month."_ | Explicitly deferred by Brock — _"that's beside the point, I'm going to talk about that next time"_ |
And the constraint that makes this worth doing at all (45:34, 48:20):
> _"It's like I've got a lot of cleanup to do in here… I'm just looking at like tidiness…
> I want to be able to use Cedar end to end and not have to use two different platforms, but
> I just don't have the cleanliness or the confidence yet."_
**Scoping call I'm making:** dashboards/forecast charts (59:33) are out of scope — Brock
deferred them himself. The aggregate work here is the per-tab summary row only.
---
## 2. Current state
### 2.1 Brock's Cedar stage enum
Read live from his `Deals` AOP (`0d9f1ddd-0cc5-4e21-a699-e7a1df2ae22f`, org
`5262ee3b-…`). The option values are HubSpot's internal stage IDs, so this enum was
seeded from HubSpot at some point:
| # | Value | Label |
|---|---|---|
| 0 | `2733809346` | Discovery |
| 1 | `appointmentscheduled` | Demo booked |
| 2 | `qualifiedtobuy` | Evaluation |
| 3 | `presentationscheduled` | Proposal |
| 4 | `closedwon` | Closed Won - Customer |
| 5 | `1220706697` | Closed Won - Pre-Pilot |
| 6 | `1220706698` | Closed Won - Pilot |
| 7 | `closedlost` | Closed Lost |
| 8 | `1181502819` | Nurturing |
| 9 | `1181502820` | Unqualified |
**There is no `Prospecting` option** — even though Brock named it on the call
(56:22: _"Prospecting is one of our—"_) and 83 deals carry a `prospecting` status value.
### 2.2 The stage value space is split three ways
`SELECT status, COUNT(*) … GROUP BY status` over Reform's org (2,421 conversations):
| Status | Deals | With deal value | Σ deal value | Verdict |
|---|---:|---:|---:|---|
| `null` | **1,940** | 4 | $4,253 | **This is "the noise"** |
| `closedlost` | 109 | 100 | $1,972,600 | ✅ in enum |
| `prospecting` | 83 | 1 | $2,000 | ⚠️ legacy value, **not in enum** |
| `1181502819` (Nurturing) | 80 | 55 | $1,926,500 | ✅ |
| `2733809346` (Discovery) | 46 | 17 | $320,000 | ✅ |
| `1181502820` (Unqualified) | 33 | 23 | $111,788 | ✅ |
| `appointmentscheduled` (Demo booked) | 26 | 11 | $339,000 | ✅ |
| `closedwon` | 23 | 23 | $1,938,200 | ✅ |
| `qualifiedtobuy` (Evaluation) | 18 | 10 | $382,000 | ✅ |
| `presentationscheduled` (Proposal) | 11 | 10 | $1,216,000 | ✅ |
| `negotiation` | 9 | 2 | $102,000 | ⚠️ legacy, not in enum |
| `closed_lost` | 9 | 0 | — | ⚠️ legacy dupe of `closedlost` |
| `1220706698` (Closed Won - Pilot) | 8 | 6 | $418,000 | ✅ |
| `closed_won` | 6 | 2 | $10,800 | ⚠️ legacy dupe of `closedwon` |
| `qualification` | 6 | 0 | — | ⚠️ legacy |
| `1220706697` (Closed Won - Pre-Pilot) | 6 | 6 | $828,000 | ✅ |
| `demo` | 5 | 0 | — | ⚠️ legacy |
| `lead` | 2 | 0 | — | ⚠️ legacy |
| `new_lead` | 1 | 0 | — | ⚠️ legacy |
Three findings that shape the whole plan:
- **1,940 of 2,421 conversations (80%) have no stage at all.** This *is* the mess Brock was
pointing at — the standalone contacts, unowned accounts, and cold-inbound records he
scrolled past at 29:18–30:41. Stage tabs alone do not fix it; they only hide it, because
a per-stage tab filters `status = X` and null-status records fall out of every tab.
- **121 deals sit on 8 legacy snake-case values that no longer exist in the enum.** They
render as unknown, can't be filtered by the stage picker, and silently vanish from any
stage tab.
- **Deal value is sparsely populated** — e.g. Discovery has 46 deals but only 17 carry a
value. A bare `SUM(deal_value)` on a tab would badly understate the stage and read as a
bug. **Every aggregate must ship with its denominator** ("17 of 46 valued").
### 2.3 Brock's tabs — before and after
**Before (2026-08-11):** seven canvases, none of them per-stage.
| Canvas | Type | Notes |
|---|---|---|
| Full Pipeline (Brock & Omar) | `conversationCanvas` | `ownerUserIds: all_org`, excludes Pilot/Closed Lost/Unqualified |
| Hubspot pipeline (my deals only) | `kanbanConversation` | The closest thing to a stage view today — columns, not tabs |
| What should I do (Overdue Next Steps) | `conversationCanvas` | 4 active stages selected |
| Post-meeting followups | `conversationCanvas` | excludes `"prospecting"` ← the legacy value |
| Stalled Deals (>5 days) | `conversationCanvas` | excludes `"prospecting"` |
| New Report | `report` | empty |
| List | `conversationCanvas` | empty, no filters |
**After:** eight stage tabs prepended in HubSpot's own order (`home_view_order` 0–7), with
all seven pre-existing canvases preserved and shifted to 8–14. Seeded by
[`reform-brock-stage-tabs.ts`](../../server/src/db/migrations/scripts/account-setup/reform-brock-stage-tabs.ts)
— idempotent, deletes and re-inserts only canvases whose title is in its own `STAGE_TABS` list.
| # | Tab | Cedar `status` value | HubSpot stage |
|---|---|---|---|
| 0 | Prospecting | `prospecting` ⚠️ | Prospecting |
| 1 | Disco | `2733809346` | Discovery |
| 2 | Demo | `appointmentscheduled` | Demo booked |
| 3 | Evaluation | `qualifiedtobuy` | Evaluation |
| 4 | Proposal | `presentationscheduled` | Proposal |
| 5 | Pre Pilot | `1220706697` | Closed Won - Pre-Pilot |
| 6 | Pilot | `1220706698` | Closed Won - Pilot |
| 7 | Customer | `closedwon` | Closed Won - Customer |
Every tab carries `crmSynced: ['synced']`, `ownerUserIds: 'all_org'` across all three org
Deals AOPs (mirroring HubSpot's "Deal owner (9)"), the HubSpot column layout (deal name,
next-step due date, next step, amount, close date, last activity, owner) and a
next-step-due-date descending sort.
Two tabs from the HubSpot strip were **not** created:
- **Prospecting** exists as a tab but is **dead** — Brock's Cedar status enum has no
Prospecting option (§2.1), so it filters on the legacy `prospecting` string, which no
CRM-linked deal carries. Fixing it needs an enum change, which is out of scope for a
tabs-only change. See Phase 1.
- **Report** is not a deal stage. No conversation in the entire org carries it and it is
absent from HubSpot's stage set — it is a saved report/view in HubSpot. Left alone;
Brock's existing empty `New Report` canvas is the natural home if he wants it.
### 2.5 Reconciliation vs. the HubSpot screenshots
What each new tab actually renders today (org-wide, CRM-synced) against the screenshots:
| Tab | HubSpot | Cedar tab | Verdict |
|---|---|---|---|
| Prospecting | 6 deals, all unvalued | **0** | ❌ dead tab — no enum option |
| Disco | 18 | 16 | −2; every matched value exact ($30k/$20k) |
| Demo | 7 | 9 | ✅ all 7 present, values exact; +2 extras (M.E. Dey $75k, Neptune Pacific — HubSpot has it in Disco) |
| Evaluation | 5 · $282,000 | 6 · **$282,000** | ✅ all 5 exact, **total matches to the dollar**; 1 unvalued extra |
| Proposal | 7 · $956,000 | 7 · $1,001,000 | ⚠️ see below |
| Pre Pilot | 4 · $716,000 | 6 · $828,000 | ✅ all 4 exact ($716k); +2 extras (SPARX $62k, Customs Direct Renewal $50k) |
| Pilot | 2 · $142,000 | 5 · $218,000 | ✅ both exact; +3 extras (ITC Diligence $44k, Worldwide Express $32k, ICAT $0) |
| Customer | not captured | 20 · $1,678,000 | — |
**Proposal is the one genuinely broken tab.** Count coincidentally matches at 7; the
contents do not:
| HubSpot | Cedar |
|---|---|
| A. N. Deringer $146,000 | ✅ exact |
| EFL Container Lines $120,000 | ✅ exact |
| Gap Inc. $48,000 | ✅ exact |
| Ryan $33,000 | ✅ exact |
| Livingston International $120,000 | ⚠️ present at **$654,000** — stale, and duplicated across two conversations |
| UPS Canada $339,000 | ❌ sitting in Cedar under Demo at $30,000 |
| Purolator $150,000 | ❌ absent from Cedar entirely |
| — | ➕ `DTP` $0, `[DELETE] Test Deal` $0 — junk records |
**Read of the whole reconciliation:** where a deal exists in both systems the values agree
almost perfectly — Evaluation and Pre Pilot tie out to the dollar. The drift is entirely
**membership**: Cedar holds deals HubSpot has since moved or filtered out, misses two, and
carries junk. That is Brock's "noise" complaint measured precisely, and it is a sync-repair
problem, not a tabs problem.
### 2.4 How the surfaces are built today
- **Tabs** = canvases with a non-null `home_view_order`, rendered as a drag-sortable tab
strip in [PipelineView.tsx](../modules/pipeline/components/PipelineView.tsx). Creating a
tab = inserting a `canvases` row (`apps/server/src/db/canvas-schema.ts`) via
`canvas.createCanvas`.
- **A stage tab is expressible today** with no schema change:
`viewConfig.filterSortConfiguration.status.filter.selected = ['<stageValue>']`.
Precedent for seeding this headlessly:
[phase6-canvases.ts](../../server/src/db/migrations/scripts/account-setup/phase6-canvases.ts).
- **Aggregates do not exist on `conversationCanvas`.** The only precedent is
`KanbanConversationViewConfig.summaryField` / `summaryAggregation`, computed **client-side
over loaded rows** in
[KanbanConversationCanvas.tsx:355-386](../modules/crm/components/kanban-canvas/KanbanConversationCanvas.tsx#L355-L386).
That is wrong for this use case: `crm.listConversations` is offset-paginated
(`limit`/`cursor`), so the kanban's "SUM $1.2M" is the sum of *the page you've scrolled*,
not the stage. It already returns `total` (the full filtered count), but no value roll-up.
---
## 3. Proposed changes
### 3.1 Stage tabs (deliverable 1) — ✅ shipped
Delivered as described in §2.3. Remaining work is data, not UI:
- Add a **Prospecting** option to the Deals status field using the real HubSpot stage ID,
then restage the CRM-linked prospecting deals onto it. Until then tab 0 is dead.
- Backfill the 121 legacy-value deals (§2.2) onto canonical enum values.
- Repair the membership drift found in §2.5 — most visibly the duplicated Livingston
International, the stale UPS Canada amount, the missing Purolator, and the two junk
records (`DTP`, `[DELETE] Test Deal`).
- `Closed Lost` deliberately has **no tab** — Brock wants those out of the active pipeline
but still reachable (58:20: _"it shouldn't be gone and lost forever, it's just out of the
active pipeline"_).
### 3.2 Aggregate summaries (deliverable 2)
**Compute server-side over the full filtered set, not the loaded page.** This is the core
design decision and the reason it isn't a 20-line client patch.
#### The metric set (confirmed from the Proposal + Pre Pilot screenshots)
HubSpot renders a six-tile Metrics bar between the filter row and the table. The first
three tiles carry an "Average per deal" sub-line; the last three do not.
| Tile | Proposal tab | Pre Pilot tab | Definition |
|---|---|---|---|
| **Total deal amount** | $956K · avg $136.57K | $716K · avg $179K | `SUM(amount)` over the filtered set |
| **Weighted deal amount** | $621.4K · avg $88.77K | $644.4K · avg $161.1K | `SUM(amount × stageProbability)` |
| **Open deal amount** | $956K · avg $136.57K | $716K · avg $179K | `SUM(amount)` where stage is not closed-won/closed-lost |
| **Closed deal amount** | $0 | $0 | `SUM(amount)` where stage is closed-won |
| **New deal amount** | $0 | $0 | `SUM(amount)` for deals created inside the view's date window |
| **Average deal age** | 5.4 months | 9.3 months | `AVG(now − createdAt)`, rendered in months |
Both screenshots reconcile exactly, which pins down the two non-obvious definitions:
- **Weighted** — $621.4K / $956K = **0.65**, and $644.4K / $716K = **0.90**. So weighted is
amount × the HubSpot stage's win probability (Proposal 65%, Pre Pilot 90%). Because each
stage tab is a single stage, weighted collapses to `total × P(stage)` per tab — but the
probability still has to come from HubSpot, and Cedar does not store it today. **This is
the one hard dependency in the whole feature** (see Phase 3).
- **Open vs. closed** — Pre Pilot shows $716K open / $0 closed even though Cedar labels that
stage "Closed Won - Pre-Pilot". Reform's HubSpot treats Pre Pilot and Pilot as **open**
stages; only `Closed Won - Customer` and `Closed Lost` are terminal. Cedar's labels are
misleading here and the open/closed split must follow HubSpot's flags, not the label text.
- **Average per deal** divides by the **row count**, not by the number of valued rows
($956K / 7 = $136.57K). Note this differs from `populatedCount` below, which is a display
denominator, not the divisor.
**New endpoint** `crm.aggregateConversations`, sibling to `crm.listConversations` in
[crm.ts:572](../../server/src/trpc/routes/crm.ts#L572):
```ts
### 3.2 Aggregate summaries (deliverable 2)
**Compute server-side over the full filtered set, not the loaded page.** This is the core
design decision and the reason it isn't a 20-line client patch.
**New endpoint** `crm.aggregateConversations`, sibling to `crm.listConversations` in
[crm.ts:572](../../server/src/trpc/routes/crm.ts#L572):
```ts
// input: the SAME conversationFilterParamsSchema.shape + ownerUserIds — no limit/cursor
// so a canvas can pass its existing listVariables object verbatim.
input: {
...conversationFilterParamsSchema.shape,
ownerUserIds: z.union([z.literal('all_org'), z.array(z.string())]).nullish(),
metrics: z.array(z.discriminatedUnion('kind', [
z.object({ kind: z.literal('count') }),
// The six HubSpot tiles. Named metrics rather than generic field+agg pairs because
// weighted / open / closed / new each carry their own semantics (stage probability,
// terminal-stage flags, the created-at window) that a generic agg can't express.
z.object({ kind: z.literal('totalDealAmount') }),
z.object({ kind: z.literal('weightedDealAmount') }),
z.object({ kind: z.literal('openDealAmount') }),
z.object({ kind: z.literal('closedDealAmount') }),
z.object({ kind: z.literal('newDealAmount'), sinceDays: z.number().default(30) }),
z.object({ kind: z.literal('averageDealAge') }),
// Generic escape hatch — any numeric builtin or custom field.
z.object({
kind: z.literal('builtin'),
field: z.enum(['dealValue']),
agg: z.enum(['sum', 'avg', 'min', 'max']),
}),
z.object({
kind: z.literal('customField'),
fieldKey=[redacted],
agg: z.enum(['sum', 'avg', 'min', 'max']),
}),
])),
}
output: {
total: number, // rows matching the filter — also the avg-per-deal divisor
results: Array<{
key=[redacted], // stable id echoing the requested metric
value: number | null,
averagePerDeal: number | null, // value / total — the HubSpot sub-line
populatedCount: number, // ← non-null contributors; the honesty denominator
unit: 'currency' | 'months' | 'count',
}>,
}
```
`populatedCount` is not optional polish. With Discovery at 17-of-46 valued, a summary that
says `$320,000` without saying `17 of 46` is a number Brock will not trust — and trust is
the entire point of the deliverable. Note it is deliberately **not** the divisor for
`averagePerDeal`: HubSpot divides by row count, and matching HubSpot is the requirement.
**Stage probability is the one thing Cedar does not have.** `weightedDealAmount` needs each
stage's HubSpot win probability (Proposal 65%, Pre Pilot 90%, derived above). Options, in
order of preference:
1. Pull `probability` from the HubSpot pipelines API during CRM sync and persist it on the
status field's option definitions (`options[].probability`). Correct, self-maintaining,
and makes the metric work for every HubSpot customer — not just Reform.
2. A per-org config map of stage value → probability. Fast, but drifts silently the moment
the customer edits their pipeline.
Take option 1. Until it lands, `weightedDealAmount` returns `null` and the tile renders a
dash rather than a wrong number.
**viewConfig extension** (`ConversationViewConfig`, mirrored in
`apps/mail/modules/canvas/types/canvas-types.ts`):
```ts
export type CanvasSummaryMetric =
| { kind: 'count' }
| { kind: 'totalDealAmount' }
| { kind: 'weightedDealAmount' }
| { kind: 'openDealAmount' }
| { kind: 'closedDealAmount' }
| { kind: 'newDealAmount'; sinceDays?: number }
| { kind: 'averageDealAge' }
| { kind: 'builtin'; field: 'dealValue'; agg: 'sum' | 'avg' | 'min' | 'max' }
| { kind: 'customField'; fieldKey=[redacted]; agg: 'sum' | 'avg' | 'min' | 'max' };
export type CanvasSummarySpec = {
id: string;
metric: CanvasSummaryMetric;
/** Override the default tile caption (e.g. "TOTAL DEAL AMOUNT"). */
label?: string;
/** Show the "Average per deal" sub-line — HubSpot shows it on the first three tiles only. */
showAveragePerDeal?: boolean;
};
export type ConversationViewConfig = {
type: 'conversationCanvas';
// …existing fields…
summaries?: CanvasSummarySpec[];
/** Collapse the metrics bar without losing its configuration. */
summariesCollapsed?: boolean;
};
```
**UI**: a **metrics bar above the table** — between `CanvasFilterSearchSection` and
`ColumnConfigurationItem` in
[ConversationCanvas.tsx](../modules/crm/components/conversation-canvas/ConversationCanvas.tsx)
— matching HubSpot's placement rather than a footer. Horizontally scrollable tile row,
collapsible via a gear affordance (HubSpot has one in the bar's top-right), each tile
rendering caption / big value / optional "Average per deal" sub-line. Configured from that
same gear popover. `GroupedConversationCanvas` additionally gets per-group subtotals.
Brock's stage tabs are then seeded with exactly his six:
```ts
summaries: [
{ id: 'total', metric: { kind: 'totalDealAmount' }, showAveragePerDeal: true },
{ id: 'weighted', metric: { kind: 'weightedDealAmount' }, showAveragePerDeal: true },
{ id: 'open', metric: { kind: 'openDealAmount' }, showAveragePerDeal: true },
{ id: 'closed', metric: { kind: 'closedDealAmount' } },
{ id: 'new', metric: { kind: 'newDealAmount' } },
{ id: 'age', metric: { kind: 'averageDealAge' } },
]
```
**Unify kanban onto it**, replacing the page-scoped client math with the same endpoint (one
call per column) so both surfaces report the same number.
### 3.3 What this deliberately does not do
- No dashboards, charts, or forecast/activity reports (Brock deferred, 59:33).
- No new stage-transition automation.
- The 1,940 null-status records are addressed by **hiding** them behind
`crmSynced: synced` + stage filters, and flagged for cleanup — genuinely fixing the
ingestion rules that create them is separate work, and Brock said he'd do a cleanup pass
on his side in parallel (61:02).
---
## 4. Critical files
**Stage tabs**
| File | Role |
|---|---|
| `apps/server/src/db/migrations/scripts/account-setup/reform-brock-stage-tabs.ts` | **The seeding script** (shipped) |
| `apps/server/src/db/migrations/scripts/account-setup/phase6-canvases.ts` | Prior art it was modelled on |
| `apps/server/src/db/canvas-schema.ts` | `canvases` table, `ConversationViewConfig` |
| `apps/server/src/trpc/routes/canvas.ts` | `createCanvas`, `reorderMyHomeView` |
| `apps/mail/modules/pipeline/components/PipelineView.tsx` | Tab strip / ordering |
| `apps/mail/modules/home/components/NewCanvasScreen.tsx` | `buildCanvasViewConfig` |
**Aggregates**
| File | Role |
|---|---|
| `apps/server/src/trpc/routes/crm.ts` (~L572) | Add `aggregateConversations` beside `listConversations` |
| `apps/server/src/lib/conversation-filter-schema.ts` | Shared filter schema — reuse, do not fork |
| `apps/server/src/db/…/listConversationsSingleQuery` | Its WHERE-clause builder must be factored out and shared |
| `apps/server/src/db/canvas-schema.ts` | `summaries` on `ConversationViewConfig` |
| `apps/mail/modules/canvas/types/canvas-types.ts` | Frontend mirror of the type |
| `apps/mail/modules/crm/hooks/use-canvas-conversations.ts` | `listVariables` memo — extract so the aggregate query reuses it |
| `apps/mail/modules/crm/components/conversation-canvas/ConversationCanvas.tsx` | Metrics bar, above the column header |
| `apps/mail/modules/crm/components/conversation-canvas/GroupedConversationCanvas.tsx` | Per-group subtotals |
| `apps/mail/modules/crm/components/conversation-canvas/ColumnConfigurationItem.tsx` | Column alignment + config entry point |
| `apps/mail/modules/crm/components/kanban-canvas/KanbanConversationCanvas.tsx` | Replace client-side summary math |
---
## 5. Phased implementation
### Phase 0 — Establish the real stage set — ✅ done 2026-08-11
- [x] Stage set taken from Jesse's HubSpot screenshots: Prospecting, Disco, Demo, Evaluation,
Proposal, Pre Pilot, Pilot, Customer (+ a `Report` saved view that is not a stage).
- [x] Mapped each to its Cedar `status` option value (§2.3). Eight of nine map cleanly;
Prospecting has no option.
- [x] Confirmed `Report` carries zero deals org-wide and is absent from HubSpot's stage set.
### Phase 1 — Stage parity in the enum + data — ⏳ outstanding
- [ ] Add a **Prospecting** option to the Deals status field using the real HubSpot stage ID
(needs a HubSpot pipelines API read — the OAuth token is not readable from this
environment), then restage its CRM-linked deals. Tab 0 stays empty until this lands.
- [ ] Backfill the 121 legacy-value deals: `closed_lost`→`closedlost`, `closed_won`→`closedwon`,
`negotiation`/`qualification`/`demo`/`lead`/`new_lead` → mapped equivalents. Re-verify
against HubSpot per deal, don't guess.
- [ ] Repair the §2.5 membership drift: de-duplicate Livingston International, resync UPS
Canada's amount and stage, pull in Purolator, delete `DTP` and `[DELETE] Test Deal`.
- [ ] Confirm with Brock whether Nurturing survives his cleanup
(57:08: _"I'm sunsetting nurturing and switching everything to closed lost"_).
_Verify:_ re-run the §2.2 group-by and the §2.5 reconciliation. Every non-null status is a
value present in the enum, and each tab's membership matches its HubSpot tab.
### Phase 2 — Seed the stage tabs — ✅ done 2026-08-11
- [x] [`reform-brock-stage-tabs.ts`](../../server/src/db/migrations/scripts/account-setup/reform-brock-stage-tabs.ts)
— idempotent, deletes/re-inserts only its own `STAGE_TABS` titles.
- [x] HubSpot column layout, `crmSynced: ['synced']`, `ownerUserIds: 'all_org'` across all
three org Deals AOPs, `nextStepDate` descending (matching HubSpot's sort arrow).
- [x] Eight tabs at `home_view_order` 0–7; Brock's seven pre-existing canvases preserved at 8–14.
- [x] Run for Brock.
- [ ] Run for Omar and Ayman (same org, same stage set) — parameterise `USER_ID` first.
_Verify:_ ✅ done — §2.5 reconciles each tab against its HubSpot screenshot.
### Phase 3 — `crm.aggregateConversations` — ✅ done 2026-08-14
- [x] ~~Extract the WHERE-clause builder~~ — **already existed.** `buildConversationFilterConditions`
(`services/crm/conversations.ts:577`) was already shared by `listConversationsSingleQuery`
and `executeConversationQuery`. What did need extracting was the *scope* half:
`resolveConversationOwnerScope` is now pulled out of `listConversationsSingleQuery` and
called by both, so the summary and the rows resolve owner scope and org-wide AOP
expansion through one code path.
- [x] Endpoint implemented in `services/crm/conversation-aggregates.ts` + route
`crm.aggregateConversations`. Aggregates in SQL over the whole filtered set.
- [x] Unit tests (9) at `services/crm/__tests__/conversation-aggregates.test.ts`.
- [x] Headless driver: `pnpm cedar-cli crm aggregates` (see §5.1 below).
- [ ] **Deferred:** `customField` metrics. The six named tiles + `builtin:dealValue` are done;
the custom-field escape hatch in §3.2 is not built. Nothing in Brock's tile set needs it.
**Two corrections the implementation forced, both material:**
1. **Stage probability needed no new sync work.** §3.2 called this "the one hard dependency"
and proposed pulling it from the HubSpot pipelines API. Unnecessary — Cedar *already*
syncs `hs_deal_stage_probability`, `hs_is_closed` and `hs_is_closed_won` onto every deal's
properties. The service reads them off the newest snapshot per stage.
2. **Open/closed must come from `hs_is_closed`, not the label — and not from probability
either.** Confirmed against live data: Reform's "Closed Won - Pre-Pilot" and
"Closed Won - Pilot" are `hs_is_closed = false`. They are open pipeline. §3.2 guessed this
from the screenshots; the synced flags prove it.
**A third correction, found by running it:** the original spec said an empty result must be
`value: null` "never 0". That is right for an *amount over every row* (no row carries an
amount → null, because $0 would claim the stage is worthless) but wrong for a *predicated*
metric — "none of these 7 is closed-won" is a definite **$0**, and HubSpot prints $0. The
service now distinguishes the two via a `resolvable` flag; both are covered by tests.
_Verified:_ 9/9 tests green, plus the live headless runs in §5.1.
### Phase 4 — viewConfig + store plumbing
- [ ] Add `CanvasSummarySpec` / `summaries` to both schema and frontend types.
- [ ] Extract `listVariables` from `use-canvas-conversations.ts` into `useCanvasListVariables`
so both the list and the aggregate consume one memoized object.
- [ ] New `useCanvasAggregates(canvasId)` hook, keyed on the same variables, `staleTime` matched
to the list query so the footer can't disagree with the rows above it.
_Verify:_ `pnpm --filter @zero/mail run types`; existing `canvas-view-config-persistence.test.ts`
still passes; a `summaries` array round-trips through save/reload.
### Phase 5 — Metrics bar UI — ◑ partly done 2026-08-14
- [x] Tile row (`CanvasMetricsBar`) above `ColumnConfigurationItem`, horizontally scrollable,
collapsible via `summariesCollapsed`. Renders nothing unless the canvas configures
`summaries`, so no existing canvas changes.
- [x] Tile format: caption / compacted value (`$956K`) / optional "Average per deal".
`populatedCount` surfaces on hover when it is below the row count.
- [x] Brock's six tiles seeded onto all eight stage tabs — now emitted by the seeding script
itself, so a re-run restores tabs and tiles together.
- [ ] **Deferred:** per-group subtotals in `GroupedConversationCanvas`.
- [x] Tile picker — a collapsible **Cumulative** toggle inside the existing Fields popover, rather
than a separate gear. Fields already answers "what does each row show"; this is the
same question for the whole set, so it belongs beside it rather than in a second
surface. Ticking rebuilds the list in canonical (HubSpot) order regardless of click
sequence, and unticking everything hides the bar.
- [ ] **Deferred:** reordering tiles by hand — the canonical order is fixed for now.
**Incident, recorded because the script is still the recovery path:** an earlier run of the
seeder was piped through `head`, which closed its stdout and killed the process mid-loop —
after it had deleted all eight tabs and re-inserted only four. Brock lost Proposal, Pre
Pilot, Pilot and Customer until it was re-run. The rebuild is now wrapped in a single
transaction so a killed run can no longer leave the account half-built.
#### Original plan (superseded above)
- [ ] Tile row in `ConversationCanvas`, above `ColumnConfigurationItem`, horizontally
scrollable, collapsible via a gear affordance (`summariesCollapsed`).
- [ ] Tile format: caption (`TOTAL DEAL AMOUNT`) / big value (`$956K`) / optional
`Average per deal $136.57K`. Currency compacted to `K`/`M` like HubSpot; `averageDealAge`
renders as `5.4 months`. Surface `populatedCount` on hover when it is below `total`.
- [ ] Per-group subtotals in `GroupedConversationCanvas`.
- [ ] Config UI in the gear popover (add/remove/reorder tiles).
- [ ] Seed Brock's six tiles (§3.2) onto the eight stage tabs.
_Verify:_ run the app against Reform. The Pre Pilot tab must read `$716K · avg $179K` for
total, `$644.4K · avg $161.1K` for weighted, `$716K` open, `$0` closed — i.e. reproduce the
screenshot exactly — **once Phase 1's membership repair has removed the two extra deals**.
Before that repair it will read `$828K`, which is correct-for-Cedar and wrong-for-HubSpot.
### Phase 6 — Unify kanban
- [ ] Repoint `KanbanConversationCanvas` summary lines at `crm.aggregateConversations`
(one call per column, same filter + that column's group value).
- [ ] Delete the client-side reduce at L355-386.
- [ ] Migrate existing `summaryField`/`summaryAggregation` view configs onto `summaries`,
keeping the old fields readable for one release.
_Verify:_ the kanban column total and the corresponding stage tab's footer show the same
number for the same stage — today they would not.
---
## 5.1 Headless verification (Phase 3)
Every number below came from the real tRPC procedure over HTTP, authenticated as
`<email>` impersonating Brock via `X-Admin-View-User` — the same path the app
uses, no forked logic:
```
pnpm --filter @zero/server cedar-cli crm aggregates --for <email> \
--status <stageValue> --aop <the three org Deals AOPs> --owner all_org --crm-synced synced
```
| Tab | rows | total | weighted | open | closed |
|---|---:|---:|---:|---:|---:|
| Proposal (`presentationscheduled`, p=0.65) | 7 | $1,001,000 | **$650,650** | $1,001,000 | $0 |
| Pre Pilot (`1220706697`, p=0.90) | 6 | $828,000 | **$745,200** | $828,000 | $0 |
| Customer (`closedwon`, p=1.00) | 21 | $1,818,000 | **$1,818,000** | $0 | $1,818,000 |
Weighted reconciles exactly against stage probability in all three
(`1,001,000 × 0.65`, `828,000 × 0.9`, `1,818,000 × 1.0`). Pre Pilot vs Customer is the case
that matters: both are labelled "Closed Won - …" in Cedar, and only the synced flags put Pre
Pilot on the open side — which is what HubSpot's own bar does ($716K open / $0 closed).
Cedar's totals differ from the HubSpot screenshots ($1,001,000 vs $956K on Proposal) purely
because of the §2.5 membership drift — a Phase 1 data problem, not an aggregation one.
## 6. Verification (end to end)
1. Stage-by-stage row-count and value reconciliation, Cedar tab vs. HubSpot tab, for all
stages, for Brock — the acceptance test Jesse committed to on the call
("literally no difference").
2. Aggregates recomputed by direct SQL and compared to the rendered footer.
3. Regression: `pnpm --filter @zero/mail run types`, `pnpm --filter @zero/server run types`,
scoped vitest over the canvas + crm route tests.
4. Ship to Brock, Omar, and Anik; confirm live on the next Reform check-in
(**Aug 14 2026, 22:30 UTC**, already on the calendar).
---
## 7. Risks
| Risk | Impact | Mitigation |
|---|---|---|
| **Stage probability is unavailable** | `weightedDealAmount` — one of Brock's six tiles — cannot be computed at all | Persist HubSpot's per-stage `probability` during CRM sync (§3.2); render a dash, never a guessed number, until it lands |
| Cedar's stage enum is missing Prospecting | Tab 0 is dead; 6 HubSpot deals invisible | Phase 1: add the option with the real HubSpot stage ID and restage |
| Membership drift between Cedar and HubSpot (§2.5) | Every aggregate is right for Cedar and wrong for HubSpot — the tiles will be the most visible place this shows | Phase 1 repair is a prerequisite for demoing the metrics bar, not a follow-up |
| Brock is mid-cleanup (sunsetting Nurturing, reworking Closed Lost) | Tabs land against a pipeline that changes underneath them | Confirm the final stage set before Phase 2; make the seeding script re-runnable |
| 1,940 null-status records | Tabs look clean, "All deals" still looks like a dumpster | Filter aggressively on the tabs; report the null count to Brock as its own cleanup task |
| Sparse `dealValue` | Stage totals read low and destroy the trust this is meant to build | `populatedCount` on every metric, surfaced in the UI, never suppressed |
| Aggregating over the loaded page (the kanban's current bug) | Silently wrong numbers | Server-side aggregation over the full filtered set; Phase 6 removes the client-side path entirely |