# 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. > **Maintenance rule (read first, then keep doing it).** > After every commit that completes a phase or substantive change, update > §9 (move the entry from Upcoming → Shipped with the SHA) and §13 (git > history). If new files were added, refresh §3. Commit the HANDOFF > update separately. See §14. ## 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, two-stage build, `node:20-alpine` for CSS + `php:8.3-apache` for runtime. - 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 CSS (vendored, compiled by the Node stage) + jQuery 3.x + jQuery UI 1.13 (from code.jquery.com CDN). - 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). - npm deps: `tailwindcss` (build-time only). ## 3. Directory layout ``` ├── Dockerfile # multi-stage: css-builder + php:8.3-apache ├── docker-compose.yml ├── .dockerignore ├── .env.example ├── composer.json / composer.lock ├── package.json / package-lock.json ├── tailwind.config.js ├── phpunit.xml ├── ACCEPTANCE.md # spec §10 manual checklist walkthrough ├── HANDOFF.md # this file ├── assets/ │ └── css/input.css # Tailwind entry, compiled into public/assets/css/app.css ├── public/ │ ├── index.php # front controller + router wiring + security headers │ ├── .htaccess # belt-and-suspenders rewrite │ └── assets/ │ ├── css/app.css # GENERATED at image-build time (gitignored) │ └── js/ │ ├── app.js # site-wide; data-href click handler │ ├── 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, UserController │ ├── 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) │ 002_sprint_week_active_days.sql (Phase 12 — mask column) ├── views/ layout.php, home.php, auth/local.php, │ workers/index.php, users/index.php, │ sprints/{new,show,settings}.php, │ audit/index.php ├── tests/ TestCase + Services/ + Repositories/ + Controllers/ + Cascade/ │ + Domain/ + Db/ └── data/ SQLite + sessions directory (volume-mounted, gitignored) ``` ## 4. Schema (migrations/001_init.sql + 002_sprint_week_active_days.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. `sprint_weeks.active_days_mask INTEGER NOT NULL DEFAULT 31` (Phase 12) is a 5-bit mask — bit0=Mo, bit1=Di, bit2=Mi, bit3=Do, bit4=Fr — and is the source of truth for "is this a workday this week." `max_working_days` lives on as a cached `popcount(mask)` projection, so the two columns are always in sync. 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. - `sprint_weeks.max_working_days` ∈ {0, 1, 2, 3, 4, 5} — derived from the weekday mask, so half-days are gone at the week level (Phase 12). - `sprint_weeks.active_days_mask` ∈ 0..31 (bits Mo..Fr). - `sprint_worker_days.days` ∈ {0, 0.5, …, 5}. - `task_assignments.days` ≥ 0, no hard upper bound. - `reserve_fraction`, `rtb` ∈ [0, 1]. FK cascades (every cascade path now snapshot-audits before the parent delete runs — 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 | `/users` | admin | | POST | `/users/{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 (audits cascaded children) | | 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 active_days_mask or active_days (derives 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. References: - `TaskController::delete()` — task → task_assignments - `SprintController::removeWorker()` — sprint_worker → sprint_worker_days + task_assignments - `SprintController::replaceWeeks()` — sprint_week → sprint_worker_days (on shrink) - 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. [ACCEPTANCE.md](ACCEPTANCE.md) captures the spec §10 manual walkthrough. - [x] **Phase 8 — Cascade audit integrity** (`dd158f3`): three FK cascade paths (sprint_worker → sprint_worker_days, sprint_worker → task_assignments, sprint_week → sprint_worker_days on shrink) now snapshot-audit before the parent delete fires. +4 tests, +2 repo lookup methods. - [x] **Phase 9 — Users management** (`f7f5db5`): `GET /users` + `POST /users/{id}` with self-demote and last-admin guardrails. Pure static `UserController::demoteGuardrail` is testable with no PDO/session setup. +6 tests. - [x] **Phase 10 — Task list polish** (`c35a934`): multi-select owner filter (checkbox dropdown) and column-visibility toggle, both pure client-side with localStorage persistence per sprint. - [x] **Phase 11 — CSP hardening** (`ab9430b`): vendored Tailwind via a Node css-builder Docker stage; inline onclick replaced by `data-href` + `app.js`; CSP dropped `'unsafe-inline'` and the Tailwind CDN host. Strict CSP now in effect. - [x] **Phase 12 — Per-week weekday selection (Mo–Fr) drives Arbeitstage** (`a634582`, follow-up UI `1aca417`): `sprint_weeks.active_days_mask` is the new source of truth; `max_working_days` is a cached `popcount(mask)` projection. Sprint Settings exposes five checkboxes (Mo Di Mi Do Fr) per week. The sprint view's Arbeitstage row shows a row of five dots per week (green = active, gray = off) — no labels, tooltip carries the day names. `PATCH /sprints/{id}/week/{week_id}` now accepts `active_days_mask` or `active_days`; `max_working_days` in the body is rejected. Migration 002 backfills legacy rows (half-days round up, clamped to 0..5). +14 tests, 88 total (was 74). - [x] **Phase 13 — Focus filter + Reset in the task list** (`b027c5d`): new `[data-focus-select]` in the task-list toolbar picks one sprint worker; `applyFilters()` grows a fourth AND predicate hiding rows whose `[data-assign][data-sw-id="{focus}"]` is not > 0, and a new `applyFocusColumnVisibility()` tags every sw column that is all-zero across the remaining visible rows with `.focus-auto-hidden` (a one-line utility added to `assets/css/input.css` — does NOT touch `hiddenCols`, so clearing focus restores the user's manual Columns picks). `[data-reset-filters]` wipes search, prio, ownerFilterSet, focusWorker, and hiddenCols in one click and re-hides itself. All state lives in `localStorage` (`sp:{sprintId}:focusWorker` joins the existing namespace). Pure client-side; no schema, route, or audit changes. Tests unchanged at 88 (the phase is 100% JS over existing HTML, same pattern as Phase 10). ### Upcoming Nothing scheduled. Residual follow-ups are in §10; the main outstanding item is a human-run walkthrough of [ACCEPTANCE.md](ACCEPTANCE.md) (now covering Phases 1–13) in a running container. ## 10. Residual known gaps / deferred items - **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 that should happen in the running container. ## 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 / Tailwind sources change: ```bash docker compose build --no-cache && docker compose up ``` For local CSS dev without Docker: ```bash npm install npm run watch:css # rebuilds public/assets/css/app.css on change ``` 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 (88 tests, 208 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–13 are > shipped (see §9) and §9 Upcoming is empty — no scheduled work. > Outstanding items are in §10 (mostly a human-run acceptance > walkthrough in the running container). If you plan to take on a new > phase, append its plan to §9 Upcoming first; when it ships, follow > the maintenance rule in §14 — commit code, then commit a HANDOFF.md > update separately that moves the entry from Upcoming → Shipped with > its 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) ``` b027c5d Phase 13: Focus filter + Reset in the task list 815e232 HANDOFF.md: note 5-dot Arbeitstage indicator follow-up 1aca417 Sprint view Arbeitstage: 5-dot weekday indicator instead of a number 59eb1d7 HANDOFF.md: mark Phase 12 shipped a634582 Phase 12: per-week weekday selection (Mo–Fr) drives Arbeitstage a1a1266 HANDOFF.md: mark Phases 8–11 shipped + codify the maintenance rule ab9430b Phase 11: vendor Tailwind + drop inline onclick + tighten CSP c35a934 Phase 10: multi-select owner filter + column visibility toggle f7f5db5 Phase 9: users management page (promote / demote admin) dd158f3 Phase 8: audit rows for FK-cascaded deletes 8537fc2 Plan Phases 8–11 in HANDOFF.md 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. Maintenance contract The previous §14 was the plan for Phases 8–11. All four shipped, so the plan moved into §9. This section now codifies the rule that produced this file in the first place — **don't lose it on a context reset.** After every commit that completes a phase or substantive change: 1. **Commit the code first.** A commit message that captures *what* changed, *why*, and *how it was verified* is the canonical record. 2. **Update §9.** If the work matched a planned phase, move it from Upcoming → Shipped with the SHA. If it didn't match a planned phase (hotfix, infra fix, etc.), insert it inline with the SHA. 3. **Update §13.** Append the new SHA to the git history block. 4. **If new files / directories were added or moved, refresh §3.** 5. **If the test count changed, update §11's expected count.** 6. **Commit the HANDOFF.md update as its own commit.** Keeping it separate means a `git revert` of the code revert leaves the doc honest by reverting alongside. 7. If the change affects the resume prompt in §12 (e.g. a new "next phase" or a deferred-work item gets resolved), update that too. Why: a fresh Claude session starts by reading this file. Stale status here is the single biggest source of duplicated or wrong work. Keeping the file truthful costs ~2 minutes per phase; recovering from a stale file costs more. If you skip these steps, the next session will eventually notice and have to rebuild the picture from `git log` and the codebase. That's recoverable, but a regression from why this file exists.