dow-prod-tracker/README.md

Dow Jones Studio Tracker

A production tracking tool for the Dow Jones studio team at Oliver Agency — replacing Excel/Planner with a single web app: per-team-scoped project visibility, the 11-stage Dow pipeline (Pipeline → New → Copywriter → Client Review → In Progress Creative → Internal Review → Client Feedback → Final Approval → Completed + On Hold + Canceled), and ingest from both the OMG webhook and the Studio Tracker XLSX.

Forked from hp-prod-tracker, customized for Dow Jones.

Docs:

• HOWTO.md — run it locally/prod, add users, configure teams + pods, ingest XLSX, wire the OMG webhook, day-to-day usage, RBAC, common problems.
• API.md — full REST + webhook reference. omgJobNumber is the key.
• DEPLOY.md — production deploy to optical-dev.oliver.solutions.

Built for producers and creative team members who need real-time visibility into where every deliverable stands across every stage of the pipeline.

---

Tech Stack

Layer	Technology
Framework	Next.js 16 (App Router, Turbopack)
Language	TypeScript 5.9
Styling	Tailwind CSS 4 + shadcn/ui (Radix)
Database	PostgreSQL 17 + pgvector + Prisma 7
Auth	Auth.js v5 (Microsoft Entra ID SSO)
State	TanStack Query v5 + Zustand
Forms	React Hook Form + Zod v4
AI Chat	Claude API + Ollama (dual provider)
Embeddings	Ollama (nomic-embed-text) for semantic search
Charts	Recharts
Media	Sharp (images), FFmpeg + HLS.js (video)
PDF	@react-pdf/renderer
Deployment	Docker + Docker Compose

---

Features

Production Dashboard

Single-screen overview: active projects, deliverable counts, overdue items, pipeline completion rates. Status charts and overdue alerts surface problems immediately.

10-Stage CG Pipeline

Every deliverable flows through 10 stages with enforced dependency gates:

1. Brief Intake → 2. File Delivery → 3. Model Prep (critical gate) → 4. Early Images (optional) → 5. Catalog Images (critical gate) → 6. Hero Images / 7. Packaging Images / 8. Photocomps / 9. 360 Spin / 10. Dynamic Spin (parallel)

Stages cannot start until their prerequisites are approved. Model Prep and Catalog Images are critical gates — all downstream work is blocked until they're done.

Multiple Views

• Table View — Dense, sortable deliverable list (TanStack Table with filters, column visibility)
• Board View — Kanban drag-and-drop for visual status management
• Timeline View — Gantt chart for cross-project scheduling (month/week/day zoom)
• Calendar View — Date-based view of upcoming stage deadlines

Visual Review System

Upload images and video for stage review. Annotate with rectangles, arrows, pins, and text directly on frames. Feedback items track resolution status (Open → In Progress → Resolved → Verified). Review sessions allow batch approval of multiple items.

Team Workload

Capacity grid and utilization heatmap per artist per week. Filter by project, highlight overloaded team members, and balance work before bottlenecks form.

AI Assistant

A built-in chat assistant that can query and mutate production data using natural language. See AI Architecture below.

Semantic Search

Vector-powered search using Ollama embeddings (nomic-embed-text) and pgvector. Find projects and deliverables by meaning, not just exact keywords.

Notifications & Automation

In-app alerts for assignments, status changes, approaching deadlines, and revision feedback. Rule-based automation engine triggers actions on events (auto-notify, auto-assign, etc.).

Excel Import/Export

Bulk import historical data from Excel. Export project data for external reporting.

Skills & Artist Matching

Track artist skill proficiencies (Junior → Lead) per pipeline stage. The AI assistant can suggest optimal artist assignments based on skills, availability, and current workload.

---

AI Architecture

The chat assistant uses a dual-provider architecture with automatic failover:

User message → Ollama (primary, free) → response
                    ↓ (if unavailable)
               Claude API (fallback, paid) → response + browser notification

Providers

Provider	Role	Model	Cost
Ollama	Primary	gemma4:latest	Free (internal GPU server)
Claude	Fallback	claude-haiku-4-5	Paid API

When Ollama is unavailable, the system automatically falls back to Claude and notifies the user in the browser with a toast: "Ollama unavailable — using Claude (paid API)".

Tool Calling (20 tools)

The assistant has access to 20 tools that map to the service layer:

Discovery — search_entities, list_projects, get_project, list_deliverables, list_users, get_blocked_stages, list_overdue, get_available_artists, get_workload, get_suggested_artists, list_revisions

Mutations — create_project, create_deliverable, bulk_create_deliverables, advance_stage, bulk_update_stages, assign_artist, remove_assignment, bulk_assign_artists, create_revision

Dynamic Tool Selection (Ollama)

Smaller models struggle with 20 tool definitions. Instead of sending all tools on every request, the system matches the user's message against keyword groups and sends only the relevant tools:

User asks about...	Tools sent
Status/progress/blocked	search + project/deliverable query tools
Workload/availability	search + workload/user tools
Assign someone	search + assignment tools
Create a project	search + creation tools
Advance a stage	search + advance_stage
Generic question	search + basic query tools

This reduces the context from ~20 tools to 2-6 per request.

Safety Guardrails

Mutation confirmation — All mutations (create, update, assign) pause and show a confirmation card in the UI before executing. The user sees exactly what will happen and must click Confirm or Cancel.

User: "assign Sarah to hero images on ManxR2"
Assistant: calls search_entities → resolves IDs → calls assign_artist
    ↓
UI shows: "Assign Sarah Chen to Hero Images (ManxR2 - Beetroot)"
    [Confirm] [Cancel]
    ↓
User clicks Confirm → mutation executes → cache invalidated

RBAC enforcement — Tool access is role-gated:

• ARTIST — Read-only tools only (no mutations via chat)
• PRODUCER — All tools except bulk operations
• ADMIN — All tools

Rate limiting — 20 requests per minute per user (in-memory).

No deletion — No tool can delete projects, deliverables, or revisions. The only "removal" is remove_assignment which unassigns an artist from a stage.

---

Deployment

Production (Docker Compose)

# Clone and configure
git clone <repo-url> loreal-prod-tracker
cd loreal-prod-tracker
cp .env.example .env
# Edit .env with your values (see Environment Variables below)

# Build and start
docker compose up -d --build

The app runs on port 3001 (mapped to container port 3000). PostgreSQL runs on port 5491 (mapped to container port 5432).

The Dockerfile is a multi-stage Node 22 Alpine build with FFmpeg for video processing. It runs prisma migrate deploy on startup and creates a non-root nextjs user.

Environment Variables

# ─── Database ────────────────────────────────────────────
DATABASE_URL="postgresql://postgres:postgres@db:5432/loreal_prod_tracker?schema=public"
DB_PASSWORD="your-password"

# ─── Auth (Microsoft Entra ID SSO) ──────────────────────
AUTH_SECRET="openssl rand -base64 32"
AUTH_MICROSOFT_ENTRA_ID_ID="your-client-id"
AUTH_MICROSOFT_ENTRA_ID_SECRET="your-client-secret"
AUTH_MICROSOFT_ENTRA_ID_TENANT_ID="your-tenant-id"
AUTH_TRUST_HOST="true"

# ─── AI Chat ────────────────────────────────────────────
# Ollama — internal GPU server (primary, free)
OLLAMA_HOST="http://10.24.42.219:11434"
OLLAMA_CHAT_HOST="http://10.24.42.219:11434"
OLLAMA_CHAT_MODEL="gemma4:latest"
OLLAMA_EMBED_MODEL="nomic-embed-text"

# Claude — paid fallback
ANTHROPIC_API_KEY="sk-ant-..."
ANTHROPIC_MODEL="claude-haiku-4-5-20251001"

# ─── Security ───────────────────────────────────────────
CRON_SECRET="openssl rand -hex 32"
API_KEY="your-api-key"

# ─── Dev Auth Bypass (local only) ───────────────────────
DEV_BYPASS_AUTH="true"
DEV_USER_ID="dev-user-001"

Apache Reverse Proxy

The app is served behind Apache at /loreal-prod-tracker. The Next.js config sets basePath: '/loreal-prod-tracker'. Key Apache configuration:

ProxyPass /loreal-prod-tracker http://localhost:3001/loreal-prod-tracker
ProxyPassReverse /loreal-prod-tracker http://localhost:3001/loreal-prod-tracker

# SSE (chat) needs a longer timeout — Ollama can take 60-180s
ProxyPass /loreal-prod-tracker/api/chat http://localhost:3001/loreal-prod-tracker/api/chat timeout=300

---

Local Development

See SETUP.md for full cross-platform setup instructions (macOS + Windows).

Quick start:

nvm use 22
npm install
cp .env.example .env           # configure DATABASE_URL + DEV_BYPASS_AUTH=true
npx prisma migrate dev
npx prisma db seed
npm run dev                     # http://localhost:3000

Available Scripts

Script	Description
npm run dev	Start dev server (Turbopack)
npm run build	Production build
npm run lint	ESLint check
npm run format	Prettier format all files
npm run db:migrate	Apply/create Prisma migrations
npm run db:seed	Seed pipeline templates + dev user
npm run db:seed-tracker	Seed sample tracker data
npm run db:seed-team	Seed team members
npm run db:studio	Open Prisma Studio GUI
npm run db:backfill-embeddings	Generate vector embeddings for existing data
npm run db:clean-slate	Reset database to clean state

---

Data Model

30+ Prisma models. Key entities:

Organization
  ├── Project (projectCode, status, priority, quarter, dueDate)
  │     └── Deliverable (name, status, priority, cmfSku, assetCount)
  │           └── DeliverableStage (10 per deliverable, auto-created)
  │                 ├── StageAssignment (LEAD / SUPPORT)
  │                 ├── Revision (submission → review → approval)
  │                 ├── Comment (threaded)
  │                 └── Annotation (visual markup on images/video)
  ├── User (role: ADMIN / PRODUCER / ARTIST)
  │     └── UserSkill (proficiency per pipeline stage)
  ├── PipelineTemplate (customizable stage sequences)
  ├── AutomationRule (trigger → action)
  └── NotificationRule (event subscriptions)

Key Enums

• ProjectStatus: ACTIVE, ON_HOLD, COMPLETED, ARCHIVED
• StageStatus: BLOCKED → NOT_STARTED → IN_PROGRESS → IN_REVIEW → CHANGES_REQUESTED → APPROVED → DELIVERED / SKIPPED
• Priority: LOW, MEDIUM, HIGH, URGENT
• UserRole: ADMIN, PRODUCER, ARTIST

---

RBAC

Role-based access control enforced at API and UI level:

Capability	ARTIST	PRODUCER	ADMIN
View projects & deliverables	Yes	Yes	Yes
Update stage status	Own stages	All	All
Assign artists	No	Yes	Yes
Create projects/deliverables	No	Yes	Yes
Bulk operations	No	No	Yes
Manage team & settings	No	No	Yes
AI chat mutations	No	Yes (no bulk)	Yes

---

Project Structure

src/
├── app/                    # Next.js App Router
│   ├── (app)/              # Auth-protected routes
│   │   ├── dashboard/      # Production dashboard
│   │   ├── projects/       # Project management
│   │   ├── my-work/        # Artist task view
│   │   ├── calendar/       # Deadline calendar
│   │   ├── timeline/       # Gantt chart
│   │   ├── workload/       # Team capacity
│   │   ├── reviews/        # Visual review sessions
│   │   ├── reports/        # Weekly reports + PDF
│   │   ├── settings/       # Team, pipelines, automation, permissions
│   │   └── notifications/  # Notification center
│   └── api/                # REST API endpoints
├── components/             # React components (23 directories)
├── hooks/                  # 31+ custom React hooks
├── lib/
│   ├── services/           # 32+ service modules (business logic)
│   ├── chat/               # AI chat (provider, tools, executor)
│   ├── automation/         # Automation engine
│   ├── pipeline/           # Pipeline state machine
│   ├── rbac/               # Role-based access control
│   └── validators/         # Zod schemas
├── stores/                 # Zustand state stores
└── types/                  # TypeScript definitions

---

Documentation

Document	Description
SETUP.md	Local development setup (macOS + Windows)
PRODUCER_GUIDE.md	End-user guide for production managers
EXECUTIVE_OVERVIEW.md	High-level product summary
ROADMAP.md	Feature pipeline & planned work
SECURITY-REVIEW.md	Security audit findings
docs/solutions/	Documented bug fixes & patterns
docs/plans/	Active feature plans