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 (prod deploy) and API.md (REST/webhook reference).

---

Table of contents

1. Quick mental model
2. Run it locally
3. Run it in production
4. The first-login ritual
5. Add users
6. Configure client teams + pods
7. Get real data in (XLSX)
8. Wire the OMG webhook
9. Day-to-day: producer workflow
10. Day-to-day: external client viewer
11. Resource planning
12. Who can do what (RBAC)
13. Common problems + fixes
14. 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.

# 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. One-liner summary:

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

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

# 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 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

openssl rand -hex 32

2. Set it on the server

# /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:

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

4. Test with a stub payload

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:

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:

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:

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).