dow-prod-tracker/API.md

# Dow Jones Studio Tracker — API Reference

All endpoints live under the app's base path **`/loreal-prod-tracker`**. Examples
in this doc use `https://optical-dev.oliver.solutions/loreal-prod-tracker` as the
origin — substitute your own when self-hosting.

**The canonical key for jobs is `omgJobNumber`.** XLSX uploads and the OMG
webhook both upsert projects on this field. The REST API exposes it on every
project payload.

---

## Authentication

Three ways to call the API.

### 1. Session cookie (browser / user-flow)

After a user signs in via `POST /api/local-auth/login`, the response sets an
`authjs.session-token` cookie. Subsequent requests in the same browser use
that cookie automatically. Session gets per-team visibility scoping
(admins see everything; others see only their `ClientTeam`'s projects).

### 2. API key (machine-to-machine)

Send `X-API-Key: <value>` where `<value>` matches the server's `API_KEY` env
var. The caller is authenticated as the first ADMIN in the resolved org.
The `X-Org-Id: <cuid>` header can narrow to a specific org (defaults to the
first one found).

### 3. HMAC (webhooks only — see the OMG section below)

Scoped to `/api/webhooks/*`, not the rest of the API.

---

## Data model — what is a "job"?

In Dow terms: a **job** ≈ one row in the Studio Tracker XLSX ≈ one `Project`
in the system. Key fields:

| App field | XLSX column | Notes |
|---|---|---|
| `omgJobNumber` | Number | **Canonical key.** String. Upsert primary. |
| `name` | Project Name | E.g. `"2337959 — BRND-CMKT-WSJ-March Education Media Pack"` |
| `status` | Status | Enum: `PIPELINE`, `ACTIVE`, `ON_HOLD`, `COMPLETED`, `CANCELED`, `ARCHIVED` |
| `priority` | Risk | Enum: `LOW`, `MEDIUM`, `HIGH`, `URGENT` (from Low / Medium / High / Priority) |
| `clientTeamId` | Team | FK → ClientTeam. Drives visibility. |
| `businessUnit` | Project Category | Copywriting / Display / GIF / PDF / Print / Social / Static / Video |
| `requestor` | Owner | Project manager name |
| `startDate` | Brief Acceptance Date | ISO 8601 |
| `dueDate` | External Deadline | ISO 8601 |
| `description` | Status Details | Free-form |

Under each project are **deliverables** (1:N), under each deliverable are
**pipeline stages** (N per template), which carry **revisions**, **comments**,
**annotations**.

---

## Endpoints

### Health

```
GET /api/health              → 200, liveness only (DB + env vars)
GET /api/health?strict=1     → 200 only if seeded (org + pipeline templates present)
```

Return shape:

```json
{
  "status": "healthy",
  "mode": "liveness",
  "checks": {
    "database": { "status": "ok" },
    "pgvector": { "status": "ok", "detail": "v0.8.2" },
    "organization": { "status": "ok", "detail": "1 org(s)" },
    "pipeline_templates": { "status": "ok", "detail": "1 template(s)" },
    "dev_bypass": { "status": "ok" },
    "auth_secret": { "status": "ok" }
  },
  "timestamp": "2026-04-21T..."
}
```

Use the liveness endpoint for Docker/Kubernetes health checks; `strict=1` is
for post-deploy verification.

---

### Projects (CRUD)

#### List

```
GET /api/projects
```

Scoped by visibility. Returns JSON array of:

```jsonc
[
  {
    "id": "cmo7...",
    "omgJobNumber": "2337959",
    "projectCode": "BRND-CMKT-WSJ-03",
    "name": "2337959 — BRND-CMKT-WSJ-March Education Media Pack",
    "status": "ACTIVE",
    "priority": "HIGH",
    "startDate": "2026-03-10T00:00:00.000Z",
    "dueDate":   "2026-04-03T00:00:00.000Z",
    "businessUnit": "PDF",
    "requestor": "Celena",
    "clientTeam": { "id": "...", "name": "Brand", "slug": "brand" },
    "_count": { "deliverables": 42 },
    "updatedAt": "2026-04-20T..."
  }
]
```

#### Get one

```
GET /api/projects/:projectId
```

Returns the project with nested `deliverables[].stages[]` — enough data to
render the Kanban / deliverable detail page in one fetch.

#### Create

```
POST /api/projects
Content-Type: application/json

{
  "projectCode": "BRND-CMKT-WSJ-03",
  "name": "2337959 — BRND-CMKT-WSJ-March Education Media Pack",
  "omgJobNumber": "2337959",
  "clientTeamId": "cmo7...",
  "businessUnit": "PDF",
  "priority": "HIGH",
  "status": "ACTIVE",
  "startDate": "2026-03-10",
  "dueDate":   "2026-04-03",
  "requestor": "Celena",
  "description": "Timeline notes go here"
}
```

Responses: `201` + project body, `400` on validation errors, `403` if caller
lacks `PROJECT_CREATE`. Caller needs an existing client team — fetch via
`GET /api/client-teams`.

#### Update

```
PATCH /api/projects/:projectId
```

Same body shape, all fields optional. Visibility pre-checked.

#### Delete

```
DELETE /api/projects/:projectId
```

Cascades deliverables + stages.

---

### XLSX upload — the "Job Tracker " sheet

The bulk-import endpoint parses the Dow Studio Tracker workbook and upserts
one project per row (keyed on `omgJobNumber`).

#### Preview (dry-run, nothing written)

```bash
curl -X POST \
  -H "X-API-Key: $API_KEY" \
  -F "file=@Dow Jones_Studio Tracker_Example.xlsx" \
  "https://optical-dev.oliver.solutions/loreal-prod-tracker/api/projects/bulk-import?commit=false"
```

Response:

```json
{
  "preview": true,
  "totalRows": 27,
  "validRows": 26,
  "errors": [
    { "row": 21, "reason": "omgNumber: Invalid input" }
  ],
  "rows": [
    {
      "omgNumber": "2337959",
      "projectName": "2337959 - BRND-CMKT-WSJ-March Education Media Pack-26",
      "clientTeamSlug": "brand",
      "status": "ACTIVE",
      "priority": "HIGH",
      "externalDeadline": "2026-04-03T00:00:00.000Z"
    }
  ]
}
```

The first 25 normalized rows ship in the response so you can show a
preview UI. Errors are row-scoped and don't abort the batch.

#### Commit (actually write)

```bash
curl -X POST \
  -H "X-API-Key: $API_KEY" \
  -F "file=@Dow Jones_Studio Tracker_Example.xlsx" \
  "https://optical-dev.oliver.solutions/loreal-prod-tracker/api/projects/bulk-import?commit=true"
```

Response:

```json
{
  "preview": false,
  "totalRows": 27,
  "imported": 26,
  "created": 18,
  "updated": 8,
  "deliverablesCreated": 267,
  "errors": [
    { "row": 21, "reason": "omgNumber: Invalid input" }
  ]
}
```

- Idempotent on `omgJobNumber` — re-upload the same file tomorrow and rows
  with unchanged numbers update in place; rows with new numbers create.
- Rows that already exist: only fields that are set on the new row overwrite;
  blank cells don't clobber producer-edited data.
- On create, one `Deliverable` is spawned per unit of `# of outputs` and the
  default Dow pipeline stages are attached.

#### Expected XLSX structure

Sheet name: **`Job Tracker `** (trailing space is tolerated — parser
normalizes headers). Header row detected automatically within the first
5 rows. Row 2 (the example/instructions row) is skipped.

Column mapping is substring-matched on normalized headers:

| Header (any case / trailing space) | Maps to |
|---|---|
| `Owner` | `requestor` |
| `Risk` | `priority` (Priority→URGENT, High→HIGH, etc.) |
| `Creative Team Member…` | assignee (not persisted on Project; flagged in errors) |
| `Number` | `omgJobNumber` **(required)** |
| `Team` | `clientTeamId` (resolved by slug; auto-creates if missing) |
| `Status` | `status` (Brief in Review→PIPELINE, Amends/In production/Client Review→ACTIVE, On hold→ON_HOLD) |
| `Project Category` | `businessUnit` |
| `Project Name` | `name` **(required)** |
| `Brief Acceptance Date` | `startDate` |
| `External Deadline` | `dueDate` |
| `# of outputs` | count of deliverables created |
| `Status Details` | `description` |

---

### OMG webhook — near-real-time ingest

For the OMG platform to push job updates directly. Same upsert path as the
XLSX importer, so the two can never drift.

#### Endpoint

```
POST /api/webhooks/omg
Content-Type: application/json
X-OMG-Signature: sha256=<hex HMAC-SHA256 of the raw body>
```

HMAC is computed with the server's `OMG_WEBHOOK_SECRET` (a shared-secret
ops hands to the OMG team). Timing-safe compare.

**Dev bypass**: set `OMG_WEBHOOK_ALLOW_INSECURE=true` in `.env` to skip
signature verification. DO NOT use in production.

Route is exempt from session auth; middleware passes all `/api/webhooks/*`
through.

#### Payload shape (speculative — confirm with Shashank)

```jsonc
{
  "event": "job.created",             // or job.updated | job.assigned | job.status_changed | job.completed
  "timestamp": "2026-04-21T12:34:56Z",
  "job": {
    "number": "2337959",               // → omgJobNumber. REQUIRED.
    "name": "2337959 — BRND-CMKT-WSJ-March Education Media Pack",
    "client": "Dow Jones",
    "team": "Brand",                   // → clientTeamId, resolved by name
    "category": "PDF",                 // → businessUnit
    "status": "In production",         // → ProjectStatus, mapped
    "priority": "High",                // → Priority, mapped
    "assignees": [
      { "email": "foo@dowjones.com", "role": "Creative" }
    ],
    "dates": {
      "accepted": "2026-03-10T00:00:00Z",
      "externalDeadline": "2026-04-03T00:00:00Z"
    },
    "outputs": 42
  },
  "raw": {
    // any OMG-native fields we haven't mapped — land on Project.customFields
  }
}
```

#### Responses

- `200 { "ok": true, "projectId": "cmo7..." }` — upsert succeeded
- `401 { "error": "Invalid signature" }` — HMAC mismatch
- `400 { "error": "Invalid payload" }` — body didn't parse
- `500 { "error": "Internal server error" }` — something went wrong server-side

#### Replay safety

Idempotent: same `(omgJobNumber, timestamp)` replayed is effectively a noop
(the upsert just writes the same fields back). OMG can replay a queue
without worrying about duplicates.

#### Example — signing a payload in bash

```bash
SECRET="$(grep OMG_WEBHOOK_SECRET .env | cut -d= -f2- | tr -d '"')"
BODY='{"event":"job.updated","timestamp":"2026-04-21T12:00:00Z","job":{"number":"2337959","name":"Test","team":"Brand"}}'
SIG=$(printf "%s" "$BODY" | openssl dgst -sha256 -hmac "$SECRET" | awk '{print $2}')
curl -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
```

---

### Client Teams

```
GET    /api/client-teams                       # list
POST   /api/client-teams                       # admin only
PATCH  /api/client-teams/:teamId               # admin only
DELETE /api/client-teams/:teamId               # admin only (blocks if team has projects)

POST   /api/client-teams/:teamId/members       # body: { userId, isPrimary? }
DELETE /api/client-teams/:teamId/members?userId=... # remove
```

Team slugs drive the XLSX/webhook resolution — keep them stable.

---

### Pods

```
GET    /api/pods
POST   /api/pods                # { name, slug?, leadUserId? }
PATCH  /api/pods/:podId
DELETE /api/pods/:podId
POST   /api/pods/:podId/members # body: { userId }  — sets homePodId
DELETE /api/pods/:podId/members?userId=...
```

Pods are orthogonal to client teams — they drive internal capacity
planning, not project visibility.

---

### Invitations (add users)

```
GET  /api/org/invitations
POST /api/org/invitations       # body: { email, role, isExternal? }
DELETE /api/org/invitations/:id
```

`POST` creates/upserts the placeholder User, issues a password-reset token,
and returns `acceptUrl` — the `/reset-password/<token>` link you hand to the
invitee.

---

### Resource bookings (capacity planner)

```
GET    /api/resources/bookings?weekStart=YYYY-MM-DD
POST   /api/resources/bookings       # ADMIN or PRODUCER only
DELETE /api/resources/bookings/:id

GET /api/resources/job-numbers       # autocomplete source for the UI
```

Body for POST:

```json
{
  "userId": "cmo7...",
  "date": "2026-04-21",
  "jobNumber": "2337959",
  "hours": 4,
  "note": "Client review prep"
}
```

---

### Dashboards / reads

```
GET /api/dashboard/stats
GET /api/my-work
GET /api/workload?numWeeks=8
GET /api/calendar?startDate=...&endDate=...
GET /api/reports/weekly?date=2026-04-21
POST /api/search/semantic          # { query }
```

All visibility-scoped — non-admins see only their client-team projects.

---

## Common flows

### Bootstrap a tenant from zero

```bash
# 1. Health
curl https://optical-dev.oliver.solutions/loreal-prod-tracker/api/health

# 2. Invite the first producer (needs X-API-Key — you're the admin who ran seed)
curl -X POST \
  -H "Content-Type: application/json" \
  -H "X-API-Key: $API_KEY" \
  -d '{"email":"producer@oliver.agency","role":"PRODUCER"}' \
  https://optical-dev.oliver.solutions/loreal-prod-tracker/api/org/invitations

# response: {"id":"...","email":"...","acceptUrl":".../reset-password/<token>"}
# hand that URL over for the producer to set a password.

# 3. Upload the current XLSX tracker
curl -X POST \
  -H "X-API-Key: $API_KEY" \
  -F "file=@Dow Jones_Studio Tracker_2026_04_20.xlsx" \
  "https://optical-dev.oliver.solutions/loreal-prod-tracker/api/projects/bulk-import?commit=true"
```

### OMG publishes a status change

OMG side (pseudocode):

```python
import hmac, hashlib, json, requests

secret = os.environ["DOW_TRACKER_SECRET"]
body = json.dumps({
  "event": "job.status_changed",
  "timestamp": datetime.utcnow().isoformat() + "Z",
  "job": {
    "number": "2337959",
    "name": "…",
    "team": "Brand",
    "status": "Client Review",
  }
}).encode("utf-8")

sig = hmac.new(secret.encode(), body, hashlib.sha256).hexdigest()
requests.post(
  "https://optical-dev.oliver.solutions/loreal-prod-tracker/api/webhooks/omg",
  data=body,
  headers={
    "Content-Type": "application/json",
    "X-OMG-Signature": f"sha256={sig}",
  },
)
```

Returns `200 { "ok": true, "projectId": "..." }` — project updated in place
if the `number` already exists, created otherwise.

### Update a single job from an external script

```bash
JOB="2337959"
PROJECT_ID=$(curl -s -H "X-API-Key: $API_KEY" \
  "https://optical-dev.oliver.solutions/loreal-prod-tracker/api/projects" \
  | jq -r ".[] | select(.omgJobNumber == \"$JOB\") | .id")

curl -X PATCH \
  -H "Content-Type: application/json" \
  -H "X-API-Key: $API_KEY" \
  -d '{"status":"COMPLETED","description":"Delivered 2026-04-21"}' \
  "https://optical-dev.oliver.solutions/loreal-prod-tracker/api/projects/$PROJECT_ID"
```

---

## Error conventions

All endpoints return JSON on error:

- `400 { "error": "<human message>" }` — validation / bad input
- `401 { "error": "Unauthorized" }` — no valid session / API key
- `403 { "error": "Forbidden" }` — authenticated but lacks the required permission
- `404 { "error": "Not found" }` — resource doesn't exist or isn't visible to this caller
  (visibility-scoped endpoints return 404 instead of 403 so ACLs aren't leaked)
- `500 { "error": "Internal server error" }` — server-side bug; check logs

Permission errors surface the missing permission:

```json
{ "error": "Missing permission: PROJECT_CREATE" }
```