# 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/ │ ├── theme-init.js # Phase 16: synchronous dark-class set from localStorage (no FOUC) │ ├── app.js # site-wide; data-href click handler + hamburger menu + theme toggle │ ├── 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,present}.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}/present` | 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`, hotfix `7c298d3`): multi-select owner filter (checkbox dropdown) and column-visibility toggle, both pure client-side with localStorage persistence per sprint. Hotfix `7c298d3` repairs a regression `c35a934` left in `sprint-planner.js`: `ownerChoices()` still scraped the pre-Phase-10 `[data-owner-filter] option` selector (replaced by `[data-owner-filter-opt]` checkboxes in this phase), so every client-built task row (admin clicks "+ Add task") had an empty owner dropdown until a page reload re-rendered it server-side. Also affects the Phase 15 `/sprints/{id}/present` view since it reuses the same toolbar markup + JS. - [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`, hotfix `23ab365`): 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). Hotfix `23ab365` stamps `data-col` on JS-built task cells in `buildTaskRow`, which was a pre-existing gap exposed by both the Columns dropdown (Phase 10) and this phase's focus auto-hide — new-task cells are now recognised by both systems. - [x] **Phase 14 — Hamburger menu groups admin utilities + Sign out** (`101cc57`): `views/layout.php` moves Workers / Users / Audit log / Sign out into a dropdown behind a `