sprint_planer_web/HANDOFF.md

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, slated for vendoring in Phase 11) + jQuery 3.x + jQuery UI 1.13 (CDN). No build step yet.
• 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
├── phpunit.xml
├── ACCEPTANCE.md              # §10 manual checklist walkthrough
├── public/
│   ├── index.php              # front controller + router wiring + security headers
│   ├── .htaccess              # belt-and-suspenders rewrite
│   └── assets/
│       └── js/
│           ├── sprint-planner.js    # /sprints/{id} Arbeitstage + task list
│           └── sprint-settings.js   # /sprints/{id}/settings
├── src/
│   ├── Auth/            LocalAdmin, OidcClient, SessionGuard
│   ├── Controllers/     AuthController, WorkerController, SprintController,
│   │                    TaskController, AuditController
│   ├── 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, AuditRepository
│   └── 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,
│                        audit/index.php
├── tests/               TestCase.php + Services/ + Repositories/
└── data/                SQLite + sessions directory (volume-mounted, gitignored)

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

FK cascades (important for audit-integrity work in Phase 8):

• sprint_weeks.sprint_id → sprints(id) ON DELETE CASCADE
• sprint_workers.sprint_id → sprints(id) ON DELETE CASCADE
• sprint_workers.worker_id → workers(id) ON DELETE RESTRICT
• sprint_worker_days.sprint_worker_id → sprint_workers(id) ON DELETE CASCADE
• sprint_worker_days.sprint_week_id → sprint_weeks(id) ON DELETE CASCADE
• tasks.sprint_id → sprints(id) ON DELETE CASCADE
• tasks.owner_worker_id → workers(id) ON DELETE SET NULL
• task_assignments.task_id → tasks(id) ON DELETE CASCADE
• task_assignments.sprint_worker_id → sprint_workers(id) ON DELETE CASCADE

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 | | GET | /audit | 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

Shipped

• Phase 1 — Skeleton (58a6b30)
• Phase 2 — Auth (be193d2, hotfix 83493d0): Entra OIDC with PKCE, session + CSRF, first-user-is-admin bootstrap, local-admin fallback.
• Apache routing fix (82ddc98): FallbackResource /index.php.
• Phase 3 — Workers + sprints + audit (f189e7d).
• Phase 4 — Sprint settings (38ba151): meta edit, weeks resize, worker membership add/remove/reorder, per-row RTB.
• Phase 5 — Arbeitstage grid (515d7d0): editable matrix, capacity calc, per-cell persistence with audit.
• Phase 6 — Task list (ad78283): CRUD, assignments grid, sort/filter/search, drag-reorder.
• SRI hotfix (927b708): guarded sortable() calls.
• Phase 7 — Audit viewer + polish (21d0c4a): /audit admin page with filters + pagination + collapsible diffs, security headers + strict-ish CSP, CSRF audit (18/18 mutations), PHPUnit harness with 59 tests covering CapacityCalculator, day-value validation, AuditLogger, and OIDC bootstrap. ACCEPTANCE.md captures the spec §10 manual walkthrough.

Upcoming

Planned in §14. Short version:

• Phase 8 — Cascade audit integrity
• Phase 9 — Users management
• Phase 10 — Task list polish (column visibility + multi-select owner filter)
• Phase 11 — CSP hardening (vendor Tailwind + drop inline onclick)

10. Residual known gaps / deferred items

Items moved into Phases 8–11 are NOT listed here — see §14.

• 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). Not scheduled; the reload is fast and the alternative (live-reordering columns in JS) is complex for little win.
• OIDC library raises PHP 8.4 deprecations. jumbojett/openid-connect-php 1.0.2 uses implicitly-nullable params. The container runs 8.3 where these are E_DEPRECATED but still emit — harmless, and silenced by ini_set('display_errors','0') in production. Upstream library needs a release.
• Manual acceptance walkthrough (ACCEPTANCE.md) hasn't been executed end-to-end by a human yet — it's a documentary follow-up once Phase 8 lands.

11. Running locally

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:

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:

for f in $(git ls-files '*.php'); do php -l "$f" | tail -1 | sed "s|^|$f: |"; done

Run the test suite:

vendor/bin/phpunit
# → OK (59 tests, 90 assertions)

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, the git log, and ACCEPTANCE.md. Phases 1–7 are shipped (see §9). Start Phase 8 — Cascade audit integrity. Follow §14 for the full plan of Phases 8–11. Do one phase at a time, commit after each, and update HANDOFF.md §9 to move each phase from "Upcoming" to "Shipped" with its commit SHA.

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)

afa9e4f Ignore PHPUnit cache directory
21d0c4a Phase 7: audit viewer + security headers + PHPUnit
09b67f3 Add HANDOFF.md for resuming work in a fresh session
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.

---

14. Upcoming phases (plan)

Four phases close out every remaining item from the original §10 "Known gaps". Ordered by value and independent enough to ship one at a time — do not batch them into one commit.

Phase 8 — Cascade audit integrity

Why. task_assignments deletes correctly snapshot-audit their children before the FK cascade (see TaskController::delete). Three other cascade paths don't:

Parent delete	Cascaded rows	Where gap lives
sprint_workers row removed	sprint_worker_days	SprintController::removeWorker
sprint_workers row removed	task_assignments	SprintController::removeWorker
sprint_weeks row removed (shrink)	sprint_worker_days	SprintController::replaceWeeks / SprintWeekRepository::syncCount

Spec §5 is explicit: every DELETE on a domain table produces exactly one audit row. Cascades violate that.

Deliverables.

• SprintWorkerDayRepository::allForSprintWorker(int $swId): list<SprintWorkerDay>
• SprintWorkerDayRepository::allForSprintWeek(int $weekId): list<SprintWorkerDay>
• TaskAssignmentRepository::allForSprintWorker(int $swId): list<TaskAssignment>
• SprintController::removeWorker — inside the tx, fetch both child sets for the target sprint_worker, emit DELETE audit rows for each, THEN call $sprintWorkers->remove($swId) so the FK cascade runs cleanly.
• SprintController::replaceWeeks — for each week in $weeks->syncCount(...)->removed, fetch sprint_worker_days for that week and emit DELETE audit rows BEFORE the inner DELETE (move the fetch into the repo or do it in the controller before calling syncCount). Simpler shape: change syncCount to return the orphaned sprint_worker_days alongside removed weeks.
• PHPUnit tests covering each of the three paths: set up a tiny sprint with one worker + one week + one day cell + one task + one assignment, trigger the delete, assert one audit row per cascaded child.

Commit message stub.

Phase 8: audit rows for FK-cascaded deletes

• Three cascade paths now emit DELETE audit rows before the parent delete runs, matching the TaskController::delete pattern from Phase 6.
• Repos gain by-parent lookup helpers.
• Regression tests.

Estimated size. ~150 LOC + 3 test classes.

---

Phase 9 — Users management

Why. The first login promotes a single user to admin. If that admin demotes themselves or wants to grant admin to someone else, there is no UI — they have to hand-edit SQLite or re-log in via local admin.

Design.

• New page GET /users (admin-only): table of every user with columns email, display name, is_admin (checkbox), last_login_at.
• New endpoint POST /users/{id} (admin-only form, CSRF) that toggles is_admin. Form-based not JSON, to match /workers/{id}.
• Guardrails (both enforced server-side with 422 on violation):
  • You cannot demote yourself.
  • You cannot demote the last remaining admin.
• Every toggle writes an UPDATE audit row on user.

Deliverables.

• UserRepository:
  • all(): list<User> — sorted by email.
  • countAdmins(): int.
  • setAdmin(int $id, bool $isAdmin): array{before: User, after: User}.
• UserController (new):
  • index(Request): Response — renders views/users/index.php.
  • updateAdmin(Request, array $params): Response — form POST handler.
• views/users/index.php — table + inline toggle forms + flash banner.
• Route wiring + admin nav link ("Users" next to Workers / New sprint / Audit log).
• PHPUnit:
  • Cannot demote self (return 422, no DB change, no audit).
  • Cannot demote last admin (same).
  • Admin→non-admin and back: UPDATE audit written with proper before/after.

Commit message stub.

Phase 9: users management page (promote / demote admin)

Estimated size. ~200 LOC + tests + 1 view.

---

Phase 10 — Task list polish

Why. Spec §6.4 calls out a column-visibility toggle and a multi-select owner filter; Phase 6 shipped the task list with a single-select filter and no visibility toggle.

Deliverables — purely client-side.

1. Multi-select owner filter in the task toolbar.

   • Replace the <select data-owner-filter> with a "trigger button → checkbox dropdown" pattern. Options: (No owner) + every worker.
   • State is a Set<string> of selected values. Empty set == show all.
   • Click outside closes the dropdown.
   • Filter logic: row visible iff the set is empty OR the row's owner id is in the set.
   • Persist in localStorage keyed by sprint-{id}-owner-filter.

2. Column-visibility toggle in the task toolbar.

   • "Columns" button opens a checkbox list: Owner, Prio, Tot, and one entry per sprint worker column. Task column is always visible.
   • Hidden columns get a hidden class on BOTH the <th> and every corresponding <td>.
   • Persist in localStorage keyed by sprint-{id}-column-visibility.
   • Restoring a hidden state on page load runs BEFORE first sort / filter so layout doesn't flash.

3. Wire recomputeAllCapacity() to still work when some columns are hidden (it reads from DOM, so values stay correct — but verify).

4. Light "sort/filter don't touch hidden rows differently" check: sort sees hidden rows the same as visible rows (correct). Filter and visibility are independent dimensions.

Deliverables — no backend changes. No repo, no controller, no route, no audit. All in sprint-planner.js + views/sprints/show.php (replace the <select>, add a "Columns" button, add the dropdown containers).

Commit message stub.

Phase 10: multi-select owner filter + column visibility toggle

Estimated size. ~250 LOC of JS + small view changes. No tests required (behaviour is pure DOM manipulation); manual acceptance covers it.

---

Phase 11 — CSP hardening (vendor Tailwind + drop inline onclick)

Why. Current CSP:

script-src 'self' https://cdn.tailwindcss.com https://code.jquery.com 'unsafe-inline'
style-src 'self' https://code.jquery.com 'unsafe-inline'

• cdn.tailwindcss.com is a dev-only CDN that runs a JIT compiler in the browser — not acceptable for production.
• 'unsafe-inline' in script-src is there for the one inline onclick="location.href=…" in views/home.php.
• 'unsafe-inline' in style-src is there because Tailwind's runtime JIT injects <style> blocks.

Once Tailwind is pre-compiled and the inline onclick is gone, we can drop both 'unsafe-inline' directives.

Deliverables.

1. Vendor Tailwind via a multi-stage Docker build.

   • package.json with tailwindcss@^3 (v3 — simpler than v4).
   • tailwind.config.js scanning views/**/*.php, src/**/*.php, public/assets/js/**/*.js.
   • assets/css/input.css (NOT under public/) with @tailwind base; components; utilities;.

   • Dockerfile gains a build stage:

     FROM node:20-alpine AS css-builder
     WORKDIR /build
     COPY package.json package-lock.json* ./
     RUN npm ci
     COPY tailwind.config.js ./
     COPY assets/css/input.css ./assets/css/input.css
     COPY views/ ./views/
     COPY src/ ./src/
     COPY public/assets/js/ ./public/assets/js/
     RUN npx tailwindcss -i ./assets/css/input.css -o /build/app.css --minify
     

     Final stage COPY --from=css-builder /build/app.css /var/www/html/public/assets/css/app.css.

   • .gitignore gains /node_modules/ (already there).

   • views/layout.php: replace <script src="https://cdn.tailwindcss.com"> with <link rel="stylesheet" href="/assets/css/app.css">.

2. Move the inline onclick out of home.php.

   • Change each sprint-list row to data-href="/sprints/{id}" and drop the onclick attribute.
   • New tiny public/assets/js/app.js (loaded in layout.php) wires a delegated click handler on any [data-href] and navigates.

3. Tighten CSP in public/index.php.

   default-src 'self';
   script-src 'self' https://code.jquery.com;
   style-src  'self' https://code.jquery.com;
   img-src 'self' data:;
   font-src 'self' data: https://code.jquery.com;
   connect-src 'self';
   frame-ancestors 'none';
   base-uri 'self';
   form-action 'self' https://login.microsoftonline.com;
   

   No more 'unsafe-inline', no more cdn.tailwindcss.com.

4. Verification checklist (manual).

   • Pages render correctly after the rebuild (docker compose build --no-cache && docker compose up).
   • Browser DevTools → Console: zero CSP violations on every page (home, sprint, settings, workers, audit, local sign-in form).
   • curl -I http://localhost:8080/ shows the tightened CSP.
   • Sprint-row click on the home page still navigates.

Commit message stub.

Phase 11: vendor Tailwind + drop inline onclick + tighten CSP

• Multi-stage Docker build produces public/assets/css/app.css.
• home.php uses data-href instead of inline onclick; app.js handles nav.
• CSP drops 'unsafe-inline' and the Tailwind CDN.

Estimated size. Infrastructure changes (Dockerfile, package.json, tailwind.config.js, input.css, app.js, layout.php, index.php header builder). ~120 LOC net + the dep manifest. Manual verification dominates the cost.

Rollback plan. If Tailwind build breaks production, reverting this commit is a single git revert and the app returns to the Play-CDN state. No schema or data touched.