# J2 Client Stage Dashboard — Layout & Design Doc

**Author:** Cord | **Date:** 2026-05-25 | **Status:** v1 shipped, Sheet seeded, Apps Script ready

## Why this shape

Loom's spec §5 (`Team Inbox/Loom/client_stage_taxonomy_spec.md`) called for: exceptions on top, full roster below, mobile-readable, "Last update" column distinguishing human/agent/system actors. This is the data-architecture realization of that spec.

## Sheet structure (single tab "Clients")

| Row | Contents |
|-----|----------|
| 1 | Title (left) + Last refreshed timestamp (right) |
| 2 | (blank — frozen with row 1) |
| 3 | 🚨 EXCEPTIONS section header (red background) |
| 4 | Column headers (if exceptions exist) OR "— none — all clear" if empty |
| 5..N | Exception rows (variable, often empty) |
| N+1 | (blank divider) |
| N+2 | 📋 ALL ACTIVE CLIENTS (30) section header (blue background) |
| N+3 | Column headers |
| N+4 onward | All 30 active-client rows |

Rows 1-2 are frozen so the title + freshness timestamp stay visible during scroll. The Exceptions section being empty on a healthy day = no visual noise.

## Column choices (10 columns)

| # | Header | Source | Why included | Mobile priority |
|---|--------|--------|--------------|-----------------|
| A | Client | `name` | Identity | Critical — column A is widened to 220px |
| B | Stage | `stage` | Primary state | Critical — gets conditional formatting |
| C | Days | `days_in_stage` | "How long" — surfaces stagnation visually | High |
| D | Flags | `flags[]` joined | Parallel state signals | High — conditional formatting on text |
| E | Tier | `service_tier` | recurring_only vs. recurring_advisory vs. fractional_cfo | Medium |
| F | Cadence | `close_cadence` | monthly/quarterly/annual close rhythm | Medium |
| G | Sales tax | `sales_tax_schedule` | Filing frequency | Medium |
| H | Owner | `stage_owner` or '(unassigned)' | Who's accountable | High |
| I | Last update | `last_occurred_at` truncated | Recency signal | Medium |
| J | Actor | `last_actor_type` → emoji | 👤/🤖/⚙️ trust signal | Critical for Phase 2 |

## Columns deliberately NOT shown

| Field | Why omitted from default view |
|-------|------------------------------|
| `id` | Internal DB key; client + slug already identify uniquely |
| `slug` | Useful for API calls but noise for visual scan; can be unhidden by user if they need to construct API URLs |
| `version` | Optimistic-lock counter; meaningless to a reader |
| `gdrive_folder` | Atlas-internal mapping; not state |
| `notes` | Likely empty in v1; revisit if Jimmie starts using it |
| `stage_collaborators` | Empty in v1; revisit once Phase 2 multi-owner is real |
| `returning_to_stage` | Only meaningful during paused → resume cycle; click into stage_history if needed |
| `eligible_reviewers` | Permission detail; not relevant to the read view |
| `doc_count` | Available in the data; Cord left it out to keep mobile column count manageable. Adding back is one-line if Jimmie wants it. |

## Sort order

1. Stage priority (custom — see `STAGE_ORDER` in the Apps Script):
   - paused_internal (J2-side hold — urgent, surfaces to Reid)
   - eom_close (close in progress)
   - eom_review (awaiting senior sign-off)
   - cleanup (active engagement)
   - onboarding (new client)
   - paused_client (client-side hold)
   - weekly (steady state — bulk of the list)
   - offboarding (winding down)
2. Within stage: days_in_stage descending (most-stagnant at top)

This means the visual flow down the page is: urgent → active → steady → ending.

## Exception logic (must match Loom spec §5)

A client appears in the Exceptions section if ANY of:
- `flags` contains `stuck`
- `flags` contains `chronic_late`
- `stage` is `paused_internal` (any duration — J2-side hold is always exception-worthy)
- `stage` is `paused_client` AND `days_in_stage > 30`

NOT included as exceptions:
- `client_blocking` flag alone — that's a "they're slow" signal, not "we're stuck"
- `recurring_active` — descriptive flag (cleanup + weekly running parallel), not an exception
- `1099_prep` / `year_end` / `sales_tax_due` / `advisory_due` — seasonal/normal flags, not exceptions
- `tax_prep_active` — Rex / external workflow signal

This list aligns with Loom spec §5: "Filter: any client with `stuck`, `chronic_late`, `paused_client > 30 days`, any `paused_internal`, or OOO owner."

**Note:** the "OOO owner" condition from Loom's spec is not implemented yet because Phase 1 doesn't track OOO state. When Phase 2 adds an OOO field, extend `isException_()` in the Apps Script.

## Refresh model

- **Cadence:** every 15 minutes via Apps Script time-driven trigger
- **Manual refresh:** Atlas menu → Refresh now
- **What refresh does:** clears the entire "Clients" sheet, re-fetches `/api/clients` (which already filters to active=true), re-sorts, re-writes both sections
- **What survives a refresh:** conditional formatting rules (they're attached to the Sheet, not the data), Sheet structure, the frozen rows setting

## Color / formatting decisions

Backgrounds applied by Apps Script:
- Section headers — soft red (exceptions), soft blue (all active)
- Exception data rows — soft yellow background to highlight them visually
- Column header rows — light gray

Backgrounds applied by USER via conditional formatting (setup-guide.md walks through this once):
- Stage column — color-coded per stage (urgent → green spectrum)
- Flags column — red on stuck/chronic_late, amber on client_blocking
- Days column — heatmap green → red over 0-30 days

The two-layer split is intentional: the script handles content + structure, the user controls visual semantics (which can change without redeploying the script).

## What this dashboard CANNOT do (and why that's OK)

1. **No two-way write** — you can't edit a client's stage from the Sheet. Stage changes go through Atlas via the API (`PATCH /api/clients/:slug/stage`) or eventually Riv's `/stage` Slack command. The Sheet is a read surface.
2. **No history view** — only "last update" is surfaced. To see the full stage_history for a client, hit `GET /api/clients/<slug>/stage_history` directly or wait for Phase 2 drilldown features.
3. **No Projects tab** — Loom's spec explicitly defers projects to a separate spec (§7 of the taxonomy doc). The current Sheet has only the Clients tab.
4. **No alerts / notifications** — if Exceptions show up, Jimmie has to actually open the Sheet. Phase 2 could add Slack notifications via Riv when a new exception appears.

These are all good Phase 2 candidates. None blocks Phase 1's value.

## Data dictionary (for future maintainers — per Cord quality standard)

| Sheet column | Atlas API field | Type | Notes |
|--------------|-----------------|------|-------|
| Client | `name` | string | Full legal name |
| Stage | `stage` | enum (8 values) | See `lib/stage.ts` CLIENT_STAGES |
| Days | `days_in_stage` | int | Derived server-side from `stage_entered_at` |
| Flags | `flags[]` joined | string | Comma-separated list of CLIENT_FLAGS values |
| Tier | `service_tier` | enum (3) | recurring_only / recurring_advisory / fractional_cfo |
| Cadence | `close_cadence` | enum (3) | monthly / quarterly / annual |
| Sales tax | `sales_tax_schedule` | enum (4) | none / monthly / quarterly / annual |
| Owner | `stage_owner` | string nullable | Currently null on most clients (Phase 2 assignment) |
| Last update | `last_occurred_at` substr | string | First 16 chars of ISO timestamp, space-separated |
| Actor | `last_actor_type` → emoji | string | 👤 human, 🤖 agent, ⚙️ system, blank if never touched |

Source: `GET https://minas-tirith.taildacccb.ts.net/api/clients` (Tailscale Funnel, bearer auth)

## Hand-off

- **Jimmie:** follow `setup-guide.md` (3 steps, ~5 min total)
- **Riv:** if Phase 2 wants Slack notification on new exceptions, add a webhook handler in Atlas that fires when `stuck` flag is added; subscribe Slack bot.
- **Loom:** spec §5 acceptance criteria all met. Confirm before declaring Phase 1 done.
- **Future maintenance:** if Atlas changes the API shape, update `clientRow_()` in the Apps Script. The script intentionally fails loud (throw on non-200) rather than silently writing bad data.