# Sprint Planner — Handoff

Single source of truth to resume work in a fresh session. Keep this file in
sync with the code; if something here disagrees with the repo, trust the repo.

## 1. What this is

A web replacement for an Excel sprint-planning workbook used by a ~15-person
ops/dev team. One sheet per sprint in the original; per sheet:

- **Arbeitstage matrix** (top): max working days per week + per-worker
  availability per week.
- **Task list** (bottom): one row per task with priority, owner, total days,
  and a per-worker days-allocated cell for each sprint worker.

The web app reproduces that workflow with proper auth, database, and
per-cell audit trail.

## 2. Tech stack (non-negotiable)

- Runtime: Docker, single container, `php:8.3-apache` base.
- Language: PHP 8.3, strict types, PSR-12.
- Database: SQLite via PDO, file at `/var/www/data/app.sqlite` (mounted volume).
- Front end: server-rendered PHP templates + Tailwind (Play CDN) + jQuery 3.x
  + jQuery UI 1.13 (CDN). No build step.
- Auth: Microsoft Entra ID via OpenID Connect (Authorization Code + PKCE),
  plus an optional env-configured "local admin" fallback for dev / on-prem.
- Composer deps: `jumbojett/openid-connect-php`, `vlucas/phpdotenv`,
  `phpunit/phpunit` (dev).

## 3. Directory layout

```
├── Dockerfile
├── docker-compose.yml
├── .env.example
├── composer.json / composer.lock
├── public/
│   ├── index.php              # front controller + router wiring
│   ├── .htaccess              # belt-and-suspenders rewrite
│   └── assets/
│       └── js/
│           ├── sprint-planner.js    # main planning view (/sprints/{id})
│           └── sprint-settings.js   # settings page
├── src/
│   ├── Auth/            LocalAdmin, OidcClient, SessionGuard
│   ├── Controllers/     AuthController, WorkerController, SprintController, TaskController
│   ├── Db/              Connection, Migrator
│   ├── Domain/          User, Worker, Sprint, SprintWeek, SprintWorker, SprintWorkerDay, Task, TaskAssignment
│   ├── Http/            Request, Response, Router, View (+ e() helper)
│   ├── Repositories/    UserRepository, WorkerRepository, SprintRepository, SprintWeekRepository,
│   │                    SprintWorkerRepository, SprintWorkerDayRepository,
│   │                    TaskRepository, TaskAssignmentRepository
│   └── Services/        AuditLogger, CapacityCalculator
├── migrations/          001_init.sql  (full schema per spec §3)
├── views/               layout.php, home.php, auth/local.php,
│                        workers/index.php, sprints/{new,show,settings}.php
├── data/                SQLite + sessions directory (volume-mounted, gitignored)
└── tests/               (empty; PHPUnit setup pending in Phase 7)
```

## 4. Schema (migrations/001_init.sql)

Tables (already applied): `users`, `workers`, `sprints`, `sprint_weeks`,
`sprint_workers`, `sprint_worker_days`, `tasks`, `task_assignments`,
`audit_log`, plus the `schema_version` tracking table.

Indexes: `idx_audit_occurred_at`, `idx_audit_entity`, `idx_tasks_sprint`,
`idx_sw_sprint`.

Value constraints enforced in PHP (not SQL):
- All `days` fields: non-negative multiple of 0.5.
- `max_working_days`, `sprint_worker_days.days` ∈ {0, 0.5, …, 5}.
- `task_assignments.days` ≥ 0, no hard upper bound.
- `reserve_fraction`, `rtb` ∈ [0, 1].

## 5. Capacity math (spec §6.5)

Runs identically in `App\Services\CapacityCalculator` (PHP) and in
`sprint-planner.js` (JS). Any edit must touch both.

```
round_half(x)  = round(x * 2) / 2
ressourcen     = Σ sprint_worker_days.days per sprint worker
after_reserves = round_half(ressourcen * (1 − sprint.reserve_fraction))
committed_p1   = Σ task_assignments.days where task.priority = 1
available      = after_reserves − committed_p1
```

Priority-2 assignments do NOT consume capacity (they're "nice to have").
A negative `available` turns the cell red but is not blocked.

## 6. Routes

Pages (HTML):
| Method | Path                        | Auth           |
|--------|-----------------------------|----------------|
| GET    | `/`                         | any (anon → sign-in CTA) |
| GET    | `/healthz`                  | —              |
| GET    | `/auth/login`               | —              |
| GET    | `/auth/callback`            | —              |
| GET    | `/auth/local`               | — (404 if disabled) |
| POST   | `/auth/local`               | — (404 if disabled) |
| POST   | `/auth/logout`              | signed-in      |
| GET    | `/workers`                  | admin          |
| POST   | `/workers`                  | admin          |
| POST   | `/workers/{id}`             | admin          |
| GET    | `/sprints/new`              | admin          |
| POST   | `/sprints`                  | admin          |
| GET    | `/sprints/{id}`             | signed-in      |
| GET    | `/sprints/{id}/settings`    | admin          |

JSON (admin-only, CSRF via `X-CSRF-Token` header; envelope per spec §7):
| Method | Path                                         | What          |
|--------|----------------------------------------------|---------------|
| PATCH  | `/sprints/{id}`                              | name/dates/reserve |
| POST   | `/sprints/{id}/weeks`                        | resize week set |
| POST   | `/sprints/{id}/workers`                      | add sprint worker |
| DELETE | `/sprints/{id}/workers/{sw_id}`              | remove sprint worker |
| POST   | `/sprints/{id}/workers/reorder`              | reorder sprint workers |
| PATCH  | `/sprints/{id}/workers/{sw_id}`              | set rtb |
| PATCH  | `/sprints/{id}/week-cells`                   | batch day cells |
| PATCH  | `/sprints/{id}/week/{week_id}`               | set max_working_days |
| POST   | `/sprints/{id}/tasks`                        | create task |
| POST   | `/sprints/{id}/tasks/reorder`                | reorder tasks |
| PATCH  | `/tasks/{id}`                                | title/owner/priority |
| DELETE | `/tasks/{id}`                                | delete task (audits cascaded assignments) |
| PATCH  | `/tasks/{id}/assignments`                    | batch assignment cells |

Response envelope:
- Success: `{"ok": true, "data": …}`
- Failure: `{"ok": false, "error": {"code", "message", "details?"}}`
- Validation errors: HTTP 422.

## 7. Audit logging rules (cross-cutting)

`App\Services\AuditLogger::record(action, entityType, entityId, before, after, userId, userEmail, ip, ua)`
is called inside the same transaction as the DB change. Controllers prefer
`recordForRequest(..., Request, ?User)` to drop the repeated plumbing.

- Every CREATE / UPDATE / DELETE on a domain table → exactly one row.
- Bulk operations (batch cell save) → one row per changed cell.
- A no-op UPDATE (canonical-JSON-equal before/after) → no row.
- FK-cascading deletes must be audited by the controller BEFORE calling the
  cascading delete. See `TaskController::delete()` as the reference.
- Non-mutation events (LOGIN, LOGOUT, LOGIN_FAILED, BOOTSTRAP_ADMIN) → always
  one row.

## 8. Env (.env.example)

```
ENTRA_TENANT_ID=
ENTRA_CLIENT_ID=
ENTRA_CLIENT_SECRET=
APP_BASE_URL=http://localhost:8080
SESSION_SECRET=
DB_PATH=/var/www/data/app.sqlite
SESSION_PATH=/var/www/data/sessions
APP_ENV=production

# Optional local admin fallback (disables when blank).
# Password is compared verbatim (not hashed) — .env must be file-permissions
# protected. The resulting user is entra_oid="local:<email>", is_admin=1.
LOCAL_ADMIN_EMAIL=
LOCAL_ADMIN_PASSWORD=
LOCAL_ADMIN_NAME=Local Admin
```

First-login bootstrap: when the `users` table is empty at the moment of
successful login (either OIDC or local), that user is promoted to `is_admin=1`
with a `BOOTSTRAP_ADMIN` audit row.

## 9. Build phases — status

- [x] **Phase 1 — Skeleton** (`58a6b30`): Dockerfile, docker-compose, composer,
  front controller, router, migrator, hello home, `/healthz`.
- [x] **Phase 2 — Auth** (`be193d2`, hotfix `83493d0`): Entra OIDC with PKCE,
  session + CSRF, first-user-is-admin bootstrap, login/logout/LOGIN_FAILED/
  BOOTSTRAP_ADMIN audit, local-admin fallback, scalar-safe Request.
- [x] **Apache routing fix** (`82ddc98`): replaced sed-based docroot hack with
  explicit site config using `FallbackResource /index.php`.
- [x] **Phase 3 — Workers + sprints + audit** (`f189e7d`): workers CRUD,
  sprints create, generic AuditLogger wired into repositories, home shows
  sprint list.
- [x] **Phase 4 — Sprint settings** (`38ba151`): meta edit, weeks resize,
  worker membership add/remove/reorder (jQuery UI sortable), per-row RTB.
- [x] **Phase 5 — Arbeitstage grid** (`515d7d0`): editable matrix,
  CapacityCalculator (server + client), per-cell persistence with audit.
- [x] **Phase 6 — Task list** (`ad78283`): task CRUD, assignments grid,
  client-side sort/filter/search, drag-reorder, capacity updates with
  committed prio-1.
- [x] **SRI fix** (`927b708`): dropped hand-typed SRI hashes, guarded
  `.sortable()` calls so missing jQuery UI fails soft.
- [ ] **Phase 7 — Audit viewer + polish** (pending). Required deliverables:
  - [ ] `/audit` admin page: reverse-chronological, paginated (50/page),
        filters (user / action / entity_type / date range / entity_id search),
        pretty-printed collapsible before/after JSON.
  - [ ] Security headers: `X-Content-Type-Options: nosniff`,
        `X-Frame-Options: DENY`, `Referrer-Policy: strict-origin-when-cross-origin`,
        a strict-ish CSP allowing `cdn.tailwindcss.com` and `code.jquery.com`.
  - [ ] Double-check CSRF on every mutation (mostly in place — verify).
  - [ ] PHPUnit tests from spec §10:
        - `CapacityCalculator`: half-step rounding, prio-2 exclusion, empty-sprint edges.
        - Day-value validation: rejects 0.3, 1.7, negative.
        - `AuditLogger`: no-op update → 0 rows; real update → 1 row; DELETE
          carries `before_json` + null `after_json`.
        - OIDC bootstrap: first user becomes admin; subsequent users do not.
  - [ ] Walk through the manual acceptance checklist in spec §10.

## 10. Known gaps / deferred items

- **Phase 4 removeWorker cascade is un-audited.** When a sprint_worker is
  removed, the FK on `sprint_worker_days` cascade-deletes rows that are
  NOT reflected in `audit_log`. TaskController::delete does the correct
  snapshot-then-delete; SprintController::removeWorker should be brought
  to parity. Fix sketch: in a tx, pre-read all `sprint_worker_days` rows
  for that sprint_worker, write DELETE audit for each, then call
  `SprintWorkerRepository::remove()`.
- **removeWorker also cascades `task_assignments`** (via FK on sprint_worker)
  — same audit gap.
- **PHPUnit test harness not set up** (no `phpunit.xml` yet, tests/ is
  empty). Phase 7 work.
- **No users management page**. Admin bootstrap on first login is the only
  way to gain admin rights. If you demote yourself, there's no UI to fix
  it — you'd need to hand-edit the DB (or re-log-in via local admin with
  `forceAdmin=true`).
- **Column-visibility toggle** on the task list (spec §6.4 mentions it)
  was deferred; not implemented. Horizontal scroll handles wide worker
  counts for now.
- **Multi-select owner filter** (spec §6.4) is a single-select in the UI.
- **Worker reorder on `/sprints/{id}` reloads the page** after drag so the
  task list's worker columns stay in sync. Acceptable, but noisy if the
  user has a lot of edits in flight (they're all saved by then).

## 11. Running locally

```bash
cp .env.example .env
# Fill Entra vars, OR set LOCAL_ADMIN_EMAIL + LOCAL_ADMIN_PASSWORD
docker compose up --build
# open http://localhost:8080
```

Rebuild when the Dockerfile / composer manifest changes:
```bash
docker compose build --no-cache && docker compose up
```

The SQLite file lives at `./data/app.sqlite` on the host; nuking it resets
the app to a blank slate (migrations run on the next request).

Syntax-check PHP without Docker:
```bash
for f in $(git ls-files '*.php'); do php -l "$f" | tail -1 | sed "s|^|$f: |"; done
```

Run the in-session smoke tests (used throughout to verify each phase):
these live as ad-hoc `php -r '…'` scripts in the commit history and the
handoff conversation. There's no formal test runner until Phase 7 lands
PHPUnit.

## 12. How to resume in a fresh Claude session

Tell Claude:

> Working on `/Users/achiappa/Development/claude_code_private/sprint_planer_web`.
> Read `HANDOFF.md`, `CLAUDE.md` if present, the git log, and
> `.planning/*` if present. Phases 1–6 are complete (including hotfixes).
> Start **Phase 7 — Audit viewer + polish**: /audit page with filters and
> pagination, security headers, strict-ish CSP allowing the two CDNs,
> PHPUnit test harness with the four test groups listed in spec §10, and
> walk through the manual acceptance checklist. Do it one deliverable at a
> time, commit between each, keep the existing audit-logging conventions.

Claude should verify what's described here against actual repo state
before acting — nothing here is load-bearing once it grows stale.

## 13. Git history (as of this handoff)

```
927b708 Fix: drop unreliable SRI hashes + guard sortable() calls
ad78283 Phase 6: task list, assignments, client-side sort/filter/search
515d7d0 Phase 5: Arbeitstage grid + capacity calculator + cell persistence
38ba151 Phase 4: sprint settings — meta, weeks, workers, reorder, RTB
f189e7d Phase 3: workers + sprints + generic audit wiring
82ddc98 Route all URLs to the front controller via FallbackResource
83493d0 Phase 2 hotfix: scalar-safe Request + local admin login
be193d2 Phase 2: Entra OIDC auth + session + audit log
58a6b30 Phase 1: skeleton
```

Each commit message captures the deliverables, rationale, and the smoke
tests that were run to validate it. Read those in preference to any
summary here.