# 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:", 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 - [x] **Phase 1 — Skeleton** (`58a6b30`) - [x] **Phase 2 — Auth** (`be193d2`, hotfix `83493d0`): Entra OIDC with PKCE, session + CSRF, first-user-is-admin bootstrap, local-admin fallback. - [x] **Apache routing fix** (`82ddc98`): FallbackResource /index.php. - [x] **Phase 3 — Workers + sprints + audit** (`f189e7d`). - [x] **Phase 4 — Sprint settings** (`38ba151`): meta edit, weeks resize, worker membership add/remove/reorder, per-row RTB. - [x] **Phase 5 — Arbeitstage grid** (`515d7d0`): editable matrix, capacity calc, per-cell persistence with audit. - [x] **Phase 6 — Task list** (`ad78283`): CRUD, assignments grid, sort/filter/search, drag-reorder. - [x] **SRI hotfix** (`927b708`): guarded sortable() calls. - [x] **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](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](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 ```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 test suite: ```bash 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` - `SprintWorkerDayRepository::allForSprintWeek(int $weekId): list` - `TaskAssignmentRepository::allForSprintWorker(int $swId): list` - `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` — 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 ``, 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 `