dow-prod-tracker/HOWTO.md

# Dow Jones Studio Tracker — How-to Guide

Everything you need to get, run, use, admin, integrate, and extend the tracker.
Pairs with [DEPLOY.md](./DEPLOY.md) (prod deploy) and [API.md](./API.md) (REST/webhook
reference).

---

## Table of contents

1. [Quick mental model](#quick-mental-model)
2. [Run it locally](#run-it-locally)
3. [Run it in production](#run-it-in-production)
4. [The first-login ritual](#the-first-login-ritual)
5. [Add users](#add-users)
6. [Configure client teams + pods](#configure-client-teams--pods)
7. [Get real data in (XLSX)](#get-real-data-in-xlsx)
8. [Wire the OMG webhook](#wire-the-omg-webhook)
9. [Day-to-day: producer workflow](#day-to-day-producer-workflow)
10. [Day-to-day: external client viewer](#day-to-day-external-client-viewer)
11. [Resource planning](#resource-planning)
12. [Who can do what (RBAC)](#who-can-do-what-rbac)
13. [Common problems + fixes](#common-problems--fixes)
14. [Change the model](#change-the-model)

---

## Quick mental model

**One tracker, one tenant.** Every row is a `Project` — equivalent to one row in
Dow's existing Studio Tracker XLSX. Projects belong to a **ClientTeam** (Brand,
Events, B2B, Content, Briefing Team, Performance) which drives *who can see
what*. Under each project are **deliverables**, and each deliverable runs through
the Dow 11-stage pipeline:

```
Pipeline → New → Copywriter → Client Review (Copy) → In Progress Creative
  → Internal Review → Client Feedback → Final Approval → Completed
  ± On Hold / Canceled (terminal parking states)
```

Orthogonal to visibility, everyone belongs to a **Pod** (Sergio's / Deborah's /
Shared) — pods are for *capacity planning*, not for project access.

**The `omgJobNumber` is the canonical key.** Both the XLSX importer and the OMG
webhook upsert on it, so if OMG publishes a job update while someone's editing
the same row in the UI, the next ingest correctly merges onto that row.

---

## Run it locally

Prereqs: Docker Desktop, Node 20+, SSH key on Bitbucket.

```bash
# 1. Clone
cd ~/Desktop/CODING-2024
git clone git@bitbucket.org:zlalani/loreal-prod-tracker.git DOW-PROD-TRACKER/loreal-prod-tracker
cd DOW-PROD-TRACKER/loreal-prod-tracker
npm install

# 2. Env
cp .env.example .env
# Fill in AUTH_SECRET at minimum:
sed -i '' "s|^AUTH_SECRET=.*|AUTH_SECRET=\"$(openssl rand -base64 32)\"|" .env
# Set NEXT_PUBLIC_AUTH_ENTRA_ENABLED=false (default) so local auth works.

# 3. Start the DB (compose handles port clashes via the ${DB_HOST_PORT:-5492} default)
docker compose -p loreal-prod-tracker up -d db

# If 5492 is busy on your Mac (e.g. another project's postgres), create a local
# override — gitignored:
cat > docker-compose.override.yml <<EOF
services:
  db:
    ports: !override ["5493:5432"]
EOF
# And update DATABASE_URL in .env to use :5493 instead of :5492.

# 4. Migrate + seed
npx prisma migrate deploy
npm run db:seed     # prints admin email + temp password — SAVE THEM

# 5. Dev server
npm run dev
# → http://localhost:3000/loreal-prod-tracker
#   (or :3001 if Docker Desktop occupies :3000 on your Mac)
```

Sign in with the seed admin → forced password change → Dashboard.

---

## Run it in production

See [DEPLOY.md](./DEPLOY.md). One-liner summary:

```bash
cd /opt/loreal-prod-tracker
git pull
./deploy.sh
docker compose -p loreal-prod-tracker exec app npm run db:seed   # first deploy only
```

`deploy.sh` auto-picks free host ports (3002 / 5492 preferred), renders the
Apache snippet, reloads the vhost, configures ufw. Idempotent.

---

## The first-login ritual

When the seed runs, it prints:

```
Email:    admin@dowjones.com
Password: <16-char random>
```

Sign in with those. The app forces a password change on first login:

1. Go to `/login` → enter email + temp password
2. Land on `/change-password?first=1` → enter temp password + new password × 2
3. On success → redirected to the Dashboard

The `mustChangePassword` flag is DB-tracked per user. Invited users go through
the same flow via `/reset-password/<token>`.

---

## Add users

The "add user" flow is an **invitation** that creates both the User row AND a
password-reset token in one go. No separate "accept" step — the invitee just
sets their password via the reset link.

### Through the UI

1. Sign in as admin
2. **Settings → Team**
3. Type email, pick a role (Admin / Producer / Artist / **Client (read-only)**),
   click **Send Invite**
4. A green banner appears with the accept URL. Click **Copy**, paste into
   Teams / email / Slack to hand it to the user
5. User visits the URL → sets password → signs in

### Programmatically

```bash
curl -X POST \
  -H "Content-Type: application/json" \
  -H "X-API-Key: $API_KEY" \
  -d '{"email":"newuser@oliver.agency","role":"PRODUCER"}' \
  https://your-host/loreal-prod-tracker/api/org/invitations
# → response includes { "acceptUrl": ".../reset-password/<token>" }
```

### Notes

- **Role choice matters for what they see:**
  - `ADMIN` — everything across all client teams.
  - `PRODUCER` — the studio side. Can create/update projects and bookings.
  - `ARTIST` — can update stage statuses / submit revisions but not create
    projects.
  - `CLIENT_VIEWER` — *external* user. Read-only. Only sees the client teams
    they're explicitly assigned to.
- **Invitation tokens expire in 7 days.** Just re-invite to re-issue.
- **Placeholder seed users exist but can't log in** until an admin invites them
  (they have no passwordHash). Go to **Settings → Team** and invite any of them
  — their role/pod/department are already set.

---

## Configure client teams + pods

### Client teams

Six teams are seeded: Brand, Events, B2B, Content, Briefing Team, Performance.

1. **Settings → Client Teams**
2. Click a team to see its members
3. Add users via the dropdown → they'll now see projects on this team
4. Admins see all teams by default regardless of membership

A user who belongs to zero client teams **sees zero projects** (fail-closed).

### Pods

1. **Settings → Pods**
2. Three placeholder pods seeded: Sergio's / Deborah's / Shared
3. Create new pods or rename existing ones
4. Each user has one `homePod`. The Resources page groups by `department` for
   now — swap to pod-grouping when the roster stabilizes

---

## Get real data in (XLSX)

The fastest way to populate the tracker from Dow's existing spreadsheet.

### UI path

1. **Projects → Import XLSX**
2. Pick the file (expected: a Dow Studio Tracker workbook with a `Job Tracker `
   sheet — trailing space tolerated, example/instructions row 2 auto-skipped)
3. Preview opens with normalized rows + per-row errors
4. Review errors. Hit **Commit** when you're happy
5. 18-ish projects land, ~250 deliverables are auto-created, pipeline stages
   attach

### API path

```bash
# dry-run
curl -X POST \
  -H "X-API-Key: $API_KEY" \
  -F "file=@Dow_Studio_Tracker_2026_04_20.xlsx" \
  "https://your-host/loreal-prod-tracker/api/projects/bulk-import?commit=false"

# commit
curl -X POST \
  -H "X-API-Key: $API_KEY" \
  -F "file=@Dow_Studio_Tracker_2026_04_20.xlsx" \
  "https://your-host/loreal-prod-tracker/api/projects/bulk-import?commit=true"
```

### Idempotency

Re-upload tomorrow's snapshot of the same tracker:

- Projects with an **existing `omgJobNumber`** → fields merge into the existing
  row (blanks don't clobber producer-edited data)
- Projects with a **new `omgJobNumber`** → freshly created + pipeline stages
  spawned
- Dropped rows in the XLSX are NOT deleted (would be destructive). To kill a
  project, do it in the UI or via `DELETE /api/projects/:id`.

See [API.md § XLSX upload](./API.md#xlsx-upload--the-job-tracker--sheet) for
the full column map and ingest edge cases.

---

## Wire the OMG webhook

The long-term ingest channel. Set it up once, OMG pushes updates in real time.

### 1. Pick a secret

```bash
openssl rand -hex 32
```

### 2. Set it on the server

```bash
# /opt/loreal-prod-tracker/.env
OMG_WEBHOOK_SECRET="<the hex string>"
OMG_WEBHOOK_ALLOW_INSECURE="false"   # ensure this is false in prod
```

Restart the app container to pick up the env:

```bash
docker compose -p loreal-prod-tracker up -d --force-recreate app
```

### 3. Give the secret + endpoint to Shashank / OMG

- URL: `https://optical-dev.oliver.solutions/loreal-prod-tracker/api/webhooks/omg`
- Method: `POST`
- Content-Type: `application/json`
- Signature header: `X-OMG-Signature: sha256=<hex HMAC-SHA256 of the raw body
  using the shared secret>`
- Payload: see [API.md § OMG webhook](./API.md#omg-webhook--near-real-time-ingest)

### 4. Test with a stub payload

```bash
SECRET="$(grep OMG_WEBHOOK_SECRET /opt/loreal-prod-tracker/.env | cut -d= -f2- | tr -d '"')"
BODY='{"event":"job.updated","timestamp":"2026-04-21T12:00:00Z","job":{"number":"TEST-001","name":"Stub job","team":"Brand","status":"In production"}}'
SIG=$(printf "%s" "$BODY" | openssl dgst -sha256 -hmac "$SECRET" | awk '{print $2}')

curl -i -X POST \
  -H "Content-Type: application/json" \
  -H "X-OMG-Signature: sha256=$SIG" \
  -d "$BODY" \
  https://optical-dev.oliver.solutions/loreal-prod-tracker/api/webhooks/omg
```

Expected: `200 { "ok": true, "projectId": "..." }`. A TEST-001 project should
now be visible on the Brand team.

### 5. Replay / idempotency

Same `(number, timestamp)` replayed = noop. OMG can safely replay queues.

### 6. Verification

Both paths write through **the same upsert function** (`upsertProjectFromDow`
in `src/lib/services/dow-excel-service.ts`). If XLSX works but the webhook
doesn't, it's the signature or the HTTP layer — not the data.

---

## Day-to-day: producer workflow

The happy path.

1. **Dashboard** — glance at the KPIs, overdue deliverables, recent activity
2. **Projects** — the Excel-shaped grid. Filter by team, search by OMG #, sort
   by deadline. Click a project to open it
3. **Project detail** — see deliverables + their pipeline stages. Advance
   stages as work progresses (click the stage status badge → pick the new
   status). Transitions are gated: you can't jump to *In Progress Creative*
   before *New* is accepted
4. **Upload XLSX / sync from OMG** — both are additive. Producer-edited data
   survives re-ingest
5. **Client Feedback → CHANGES_REQUESTED** — automation rule kicks in,
   re-opens *In Progress Creative* on the same deliverable and increments
   its revision round. Notifies the assignee + PM
6. **Resources page** (admins + producers) — assign N hours of a job to a
   person on a day. Capacity bars go amber at 85%, red over 100%

---

## Day-to-day: external client viewer

For `@dowjones.com` users you want to give read-only visibility.

1. **Admin invites them** with role `CLIENT_VIEWER` (Settings → Team →
   Client (read-only))
2. **Admin adds them to exactly the client teams they should see** (Settings
   → Client Teams → Add member). Usually one team; could be multiple
3. User sets password via the reset link
4. On sign-in they see:
   - Only projects on teams they're a member of
   - Dashboard KPIs scoped to those same projects
   - No edit buttons (the UI checks `session.user.role === "CLIENT_VIEWER"`
     and hides mutations)
   - Can still comment (that's intentional — client feedback is normal for
     review flows and doesn't mutate state)
5. Any attempt to POST/PATCH/DELETE anything returns 403.

When you're ready for Entra guest-invite SSO instead of local accounts, set
`NEXT_PUBLIC_AUTH_ENTRA_ENABLED=true` and fill in the Azure env vars. No
code change.

---

## Resource planning

The **Resources** page (in the sidebar) is the daily-hours capacity view.

- Users grouped by department (swap to pod grouping when the real roster
  lands)
- Each cell = one person × one weekday. Shows job chips + capacity bar
- Click **Assign** on any cell → job-number autocomplete (pulls from your
  project `omgJobNumber` list) + hour picker → commit
- Job chips are color-coded by a hash of the job number — same number always
  gets the same color across the grid
- Over-cap days get a red outline + a "8h/6h" red badge

ADMIN + PRODUCER can write; ARTIST / CLIENT_VIEWER see read-only.

---

## Who can do what (RBAC)

Defaults per role (Settings → Permissions to view / customize per org):

| Role | Key permissions |
|---|---|
| `ADMIN` | Everything. Managing users, teams, pods, pipelines, automations. |
| `PRODUCER` | Projects CRUD, deliverables, stages, revisions, comments, bookings. Can't manage users/teams/pods. |
| `ARTIST` | Read projects/deliverables/stages. Update stage status + submit revisions. Can comment. |
| `CLIENT_VIEWER` | Read-only on visible projects. Comments only (no state mutation). |

Per-team visibility is layered on top — an Artist on Team X only sees Team X's
projects, an Admin sees everything.

---

## Common problems + fixes

### "No organizations found" on `/api/health`

You haven't run the seed. `docker compose -p loreal-prod-tracker exec app npm run db:seed`.

### Seed says `tsx: not found`

The runner image is missing the dev deps. Fixed in commit `df7ddbf` (installs
tsx globally). Rebuild with `--no-cache`:

```bash
docker compose -p loreal-prod-tracker down
docker compose -p loreal-prod-tracker build --no-cache app
docker compose -p loreal-prod-tracker up -d
```

### Browser returns 404 at `/loreal-prod-tracker/...`

Apache Include didn't land. Check:

```bash
grep loreal-prod-tracker /etc/apache2/sites-enabled/optical-dev.oliver.solutions.conf
```

If absent, re-run deploy.sh (it auto-detects whether `sites-enabled` is a
symlink to `sites-available` or a separate file).

### Port 5492 is busy on the server

`deploy.sh` auto-picks the next free port. If you want to force a specific
one, `DB_HOST_PORT=5499 ./deploy.sh`.

### Password reset email isn't sent

SMTP isn't wired yet. `POST /api/local-auth/forgot-password` returns the
reset URL in the response when `NODE_ENV !== production` (dev path). In
prod, the URL is only server-logged. For now: admin reads the URL from the
create-invite response and hands it to the user out-of-band.

### "Missing permission: X" on an action

Check the role. Settings → Team → see everyone's role. Only ADMIN gets full
powers by default.

### Apache 502 after deploy

App container didn't start. `docker compose -p loreal-prod-tracker logs app --tail 50`.

### XLSX upload rejects most rows

Usually means the Job Tracker sheet's headers drifted or Excel wrapped some
cells as hyperlinks. The importer handles the common cases; paste the
`?commit=false` preview errors and we'll iterate.

### OMG webhook returns 401

Shared secret mismatch. Double-check both sides have the same
`OMG_WEBHOOK_SECRET` and that the signing header is
`X-OMG-Signature: sha256=<hex>` (lowercase `sha256=`, no extra whitespace).

---

## Change the model

When the shape of "a job" changes, edits go here:

| Field of interest | Where to touch |
|---|---|
| Add a Project field | `prisma/schema.prisma` → new migration → `src/lib/validators/project.ts` → `src/components/projects/project-form-dialog.tsx` → optionally the Projects-page table column |
| Add/change an enum value | `schema.prisma` enum → new migration (Postgres: ALTER TYPE ADD VALUE) → update map/usage |
| Rename a ClientTeam | UI only — slugs are immutable (ingest depends on them) |
| Add a new pipeline stage | `prisma/seed-dow.ts` → `DOW_STAGES` + `DOW_DEPENDENCIES` → re-run seed |
| New XLSX column to ingest | `src/lib/validators/dow-import.ts` (schema) + `src/lib/services/dow-excel-service.ts` (`HEADER_MATCHERS` + `upsertProjectFromDow`) |
| New OMG webhook event | `src/app/api/webhooks/omg/route.ts` — the switch statement on `event` |
| New automation action | `src/lib/automation/action-executor.ts` — add a case + register it in `validateActions` |

Migrations: the repo is on Prisma 7 with a clean baseline plus one delta.
For a new schema change, generate a fresh migration against a running local
DB:

```bash
npx prisma migrate dev --name <short_description>
```

If a local DB isn't running, hand-write the SQL into
`prisma/migrations/<timestamp>_<name>/migration.sql` (idempotent, forward-only).