# Manager Dashboard — Integration Spec (PRD input)

> Hand-off document for the **separate** dashboard project. It reads and lightly writes **one Supabase database**; it owns no state of its own. The n8n pipeline is the brain — the dashboard is a window plus a few writes the pipeline already honors.
> Version 2026-07-21. Source of truth for the schema: the pipeline repo's `supabase-schema.sql`.

---

## 1. What this product is

A web dashboard (intended: Next.js on Vercel) that lets a **marketing manager**, without ever opening n8n:

- **See** the daily opportunity board, content briefs, publication verdicts, trends, and pipeline health.
- **Re-score** any opportunity (their judgment overrides the machine score — and the pipeline respects it).
- **Change an opportunity's status** (e.g. drop something from the briefing queue).

Everything else — collecting signals, scoring, writing briefs, verifying outcomes, and all configuration (competitors, weights, schedules) — stays in the n8n pipeline. **The dashboard is deliberately not a config editor.**

---

## 2. Architecture in one picture

```
 n8n pipeline (owns logic)  ──writes──▶   SUPABASE (single source of truth)   ◀──reads/writes──  Dashboard (this project)
   collect · score · brief ·                tables below                          board view · re-score · status
   verify · all config                                                            (no state of its own)
```

- The dashboard talks **only** to Supabase (PostgREST/REST or the `@supabase/supabase-js` client) — **server-side**.
- It does **not** talk to n8n at all in the MVP. (Action buttons that call n8n come later — see §7.)

---

## 3. Connection

| Item | Value |
|---|---|
| Supabase project URL | `https://onhskhrdtryvzinouzgt.supabase.co` |
| REST base | `https://onhskhrdtryvzinouzgt.supabase.co/rest/v1` |
| Region | AWS us-east-2 (host Vercel functions in US East for low latency) |
| Auth key | **`sb_secret_…` Secret key** — obtain from the operator / Supabase dashboard → Settings → API Keys. **Server-side only.** |
| Publishable key | Do **not** use — RLS blocks it, every read returns `[]`. |

Every REST call needs both headers: `apikey: <secret>` and `Authorization: Bearer <secret>`.

---

## 4. Data contract (the tables)

Only `opportunities` is writable by the dashboard. Everything else is read-only.

### `opportunities` — the board (READ + limited WRITE)
| Column | Type | Notes |
|---|---|---|
| `opportunity_id` | text PK | format `OPP-YYYYMMDD-###-HASH`; the join key |
| `date` | **text** `YYYY-MM-DD` | app string, not a real date — don't `new Date()` it for sorting |
| `topic` | text | the headline shown on the board |
| `trigger_type` | text | **comma-joined list** — `.split(',')` for chips |
| `source` | text | e.g. `Google News` |
| `score` | int 0–100 | **machine score — READ ONLY, never write** |
| `why_it_matters` | text | |
| `recommended_formats` | text | comma-joined list |
| `primary_team` / `supporting_teams` | text | supporting is comma-joined |
| `competitor_presence` / `nice_presence` | text | `low`/`medium`/`high` |
| `status` | text | **WRITABLE** — vocab in §6 |
| `business_theme` | text | |
| `source_urls` | text | **newline-joined** list — `.split('\n')` (NOT comma) |
| `created_at` | timestamptz | sort by this, not `date` |
| `manager_score` | int 0–100, nullable | **WRITABLE** — the override. null = no override |
| `manager_note` | text, nullable | **WRITABLE** |
| `reviewed_by` | text, nullable | **WRITABLE** — who overrode |
| `reviewed_at` | timestamptz, nullable | **WRITABLE** — set on override |

**Effective score = `coalesce(manager_score, score)`.** Sort and display by effective score everywhere. When they differ, show both (e.g. `62 → 85 ✎`) — the disagreement is signal.

### `content_briefs` — generated briefs (READ ONLY)
`brief_id` (PK, `BRIEF-YYYYMMDD-HASH`), `opportunity_id`, `date`, `topic`, `score`, `headline_angle`, `hook`, `target_keywords`, `content_formats`, `linkedin_post`, `key_messages`, `cta`, `owning_team`, `priority`, `created_at`.

### `content_publications` — published work being tracked (READ ONLY)
`brief_id` (PK), `published_url`, `target_keywords`, `topic`, `published_at` (timestamptz), `review_at` (timestamptz), `status` (`published` / `reviewed-moved` / `reviewed-flat`).

### `outcome_checks` — verdicts (READ ONLY)
`id`, `brief_id`, `published_url`, `verdict` (`moved`/`flat`), **`evidence` = stringified JSON — `JSON.parse` before rendering** (SERP/share-of-voice details), `checked_at`.

### `trend_snapshots` — trend report output (READ ONLY)
`id`, `dimension` (`topic`/`entity`), `name`, `cur_mentions`, `prev_mentions`, `cur_engagement`, `prev_engagement`, `velocity` (float), `direction` (`new`/`trending`/`dying`/`steady`), `window_days`, `computed_at`.

### `digest_log` (READ ONLY) — for a "last runs" health strip
`id`, `date`, `digest_text`, `item_count`, `channel`, `created_at`.

### `ops_errors` (READ ONLY, **server-only**) — pipeline failures
`id`, `workflow_name`, `error_message`, `failed_node`, `created_at`, … Show a count / recent list; never expose to the client directly.

### `signals` (READ ONLY, optional) — raw signal history incl. sub-40
Powers "everything we ever saw about X". `brand_mentioned` is a **0/1 int**, not boolean. Large table — paginate/filter.

---

## 5. Write contract (the only writes)

A PATCH on `opportunities`, keyed by `opportunity_id`. **Whitelist these columns server-side and reject anything else** (especially `score`):

```
PATCH /rest/v1/opportunities?opportunity_id=eq.<id>
Headers: apikey, Authorization: Bearer <secret>, Content-Type: application/json, Prefer: return=minimal
Body: { "manager_score": 55, "manager_note": "off-brand angle",
        "reviewed_by": "<user>", "reviewed_at": "<ISO now>" }
```

- `manager_score`: integer **0–100 or null**. **null clears the override** (machine score takes over) — provide a "reset to machine score" control. DB enforces the 0–100 range (expect a 400 on violation).
- `status`: writable to one of the vocab values in §6.
- **The pipeline already honors these** — `next_brief_opportunity()` orders by `coalesce(manager_score, score)`, and the brief agent skips `status = 'Intel-Only'`. So a re-score or status change genuinely changes what gets briefed next. No n8n action needed.

---

## 6. Controlled vocabularies

- **Opportunity `status`:** `New` · `Monitor` · `Weekly Queue` · `Immediate` · `Intel-Only` · `Refresh-Queued`
  (Setting `Intel-Only` removes it from the briefing queue. `Immediate` = act now.)
- **Publication `status`:** `published` · `reviewed-moved` · `reviewed-flat`
- **Verdict:** `moved` · `flat`
- **Trend `direction`:** `new` · `trending` · `dying` · `steady`
- **Priority (briefs):** `now` · `this-week` · `backlog`

---

## 7. Deferred: pipeline actions (after n8n goes online)

Not in the MVP — n8n currently runs locally and isn't reachable from Vercel. Once the pipeline is on a public/online n8n instance, add **server-side proxy routes** (never call n8n from the browser). All are `POST` and require header `X-Webhook-Token: <token>` (obtain from operator; keep in server env):

| Action | Endpoint | Body |
|---|---|---|
| Generate a brief for the top opportunity | `POST {N8N_BASE}/webhook/nice-brief-run` | — |
| Record a publication (closes the loop) | `POST {N8N_BASE}/webhook/nice-publish` | `{ "brief_id": "...", "published_url": "...", "review_days": 21 }` |
| Run collection now | `POST {N8N_BASE}/webhook/nice-spine-run` | — |
| Run trend report now | `POST {N8N_BASE}/webhook/nice-trend-run` | — |

Design the PRD so these slot in as buttons later; hide/disable them until `N8N_BASE` exists.

---

## 8. Security requirements (non-negotiable)

1. **Secret key server-side only.** No `NEXT_PUBLIC_` prefix, ever. All Supabase access via server routes/components. It bypasses RLS — treat as a DB password.
2. **The dashboard needs its own auth** (2–3 users). Anyone with the URL could otherwise re-score the board. Ladder: Vercel password protection → Supabase Auth magic-link → company SSO.
3. **Browser never calls n8n directly** (when actions land) — always via a server proxy that injects `X-Webhook-Token`.
4. **Whitelist writable columns** in the write route; reject `score` and everything not in §5.
5. **RLS stays deny-all** for anon/publishable; do not add public read policies. `ops_errors` never reaches the client.

---

## 9. Scope

**In (MVP, Supabase-only, buildable today):** board with effective-score sort + status chips; opportunity detail (why, source links, linked brief, verdicts); re-score (slider + note + reset); status change; briefs list; trends view; health strip.

**Deferred (needs online n8n):** the four action buttons in §7.

**Out (stays in n8n):** competitors/aliases, score weights & thresholds, subreddit list, schedules, prompt/editorial rules. The dashboard never edits config.

---

## 10. Env vars for the dashboard project

```
SUPABASE_URL=https://onhskhrdtryvzinouzgt.supabase.co
SUPABASE_SECRET_KEY=            # sb_secret_… from operator — SERVER ONLY
# later, for §7 actions:
N8N_BASE_URL=                   # public n8n URL, once online
N8N_WEBHOOK_TOKEN=              # from operator — SERVER ONLY
# dashboard auth (choose one path)
# NEXTAUTH_… / Supabase Auth keys / Vercel protection
```

---

## 11. Gotchas checklist (paste into the build ticket)

- [ ] Sort by `coalesce(manager_score, score)` — in server code (PostgREST can't order by coalesce)
- [ ] `source_urls` splits on **newline**, other lists on comma
- [ ] `outcome_checks.evidence` is stringified JSON — parse it
- [ ] `date` is text; sort by `created_at`
- [ ] `brand_mentioned` / `teams_configured` are 0/1 ints
- [ ] `manager_score: null` = clear override (not "set to 0")
- [ ] Never write `score`; whitelist writable columns
- [ ] Secret key + webhook token are server-only; publishable key returns nothing