Sprint Planner — Specification

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 SPEC 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 + JS-vendor copy + php:8.3-apache for runtime. The runtime stage installs pdo_sqlite, plus zip and gd (Phase 20 — required by PhpSpreadsheet); dom, xml, xmlreader, xmlwriter, simplexml, mbstring, fileinfo ship with the base image.
Language: PHP 8.3, strict types, PSR-12.
Database: SQLite via PDO, file at /var/www/data/app.sqlite (mounted volume).
Front end (Phase 19):
- Templates: Twig 3 (*.twig under views/, {% extends %} inheritance, auto-escape ON, compiled cache in data/twig-cache/).
- Styles: Tailwind CSS 3 compiled at image-build time (assets/css/input.css → public/assets/css/app.css). No CDN.
- Behaviour: vanilla JS (delegated addEventListener, fetch) for the live grid pipelines (Arbeitstage cells, RTB, task days, task status, filters, sort) plus SortableJS for drag-reorder. Alpine.js (CSP build) drives small declarative components (hamburger menu, theme toggle). htmx wires the simple form-post pages (auth, settings, workers, users, sprint create, audit filter) for AJAX swaps without controller changes.
- Strict CSP: script-src 'self' / style-src 'self' only — no unsafe-eval, no unsafe-inline, no third-party hosts. All JS deps vendored under public/assets/js/vendor/.
Auth: Microsoft Entra ID via OpenID Connect (Authorization Code + PKCE), plus an optional env-configured "local admin" fallback for dev / on-prem. OIDC can be hard-disabled with OIDC_ENABLED=false, leaving the local admin as the sole sign-in path; this is the supported dev / testing configuration.
Composer deps: twig/twig, jumbojett/openid-connect-php, vlucas/phpdotenv, phpoffice/phpspreadsheet (Phase 20 — XLSX import wizard), phpunit/phpunit (dev).
npm deps (build-time only): tailwindcss, alpinejs, @alpinejs/csp, htmx.org, sortablejs.

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
├── SPEC.md                     # this file
├── doc/
│   ├── admin-manual.md         # operator-facing setup + run guide
│   └── Tool_Sprint Planning.xlsx  # Phase 20 — sample workbook (parser fixture)
├── 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 delegation + Alpine appMenu + Alpine themeToggle + htmx CSRF wiring
│           ├── sprint-planner.js   # /sprints/{id} + /sprints/{id}/present — vanilla JS + SortableJS
│           ├── sprint-settings.js  # /sprints/{id}/settings — vanilla JS + SortableJS
│           └── vendor/             # GENERATED at image-build time (gitignored)
│               ├── alpine-csp.min.js   # @alpinejs/csp — Alpine without `unsafe-eval`
│               ├── htmx.min.js         # htmx.org
│               └── sortable.min.js     # SortableJS
├── bin/
│   └── audit.sh                # R01-N16 — wraps `composer audit --locked`
│                               # inside the runtime image; honours
│                               # SPRINT_PLANER_IMAGE for non-default tags
├── src/
│   ├── Auth/            BootstrapAdmin, LocalAdmin, OidcClaims (R01-N18),
│   │                    OidcClient, SessionGuard
│   ├── Controllers/     AuthController, WorkerController, SprintController,
│   │                    TaskController, AuditController, UserController,
│   │                    SettingsController, ImportController (Phase 20)
│   ├── Db/              Connection, Migrator
│   ├── Domain/          User, Worker, Sprint, SprintWeek, SprintWorker,
│   │                    SprintWorkerDay, Task, TaskAssignment
│   │   └── Import/      (Phase 20) ParsedSheet, ParsedWeek, ParsedWorker,
│   │                    ParsedTask, ParsedAssignment, ImportResult
│   ├── Http/            Request, Response, Router, View (+ e() helper),
│   │                    TrustedProxies (R01-N05/N07), FatalErrorHandler (R01-N13)
│   ├── Repositories/    UserRepository, WorkerRepository, SprintRepository,
│   │                    SprintWeekRepository, SprintWorkerRepository,
│   │                    SprintWorkerDayRepository, TaskRepository,
│   │                    TaskAssignmentRepository, AuditRepository,
│   │                    AppSettingsRepository, AuthThrottleRepository
│   └── Services/        AuditLogger, CapacityCalculator
│       └── Import/      (Phase 20) XlsxColorClassifier, XlsxSprintImporter,
│                        SprintImporter
├── migrations/          001_init.sql (full schema per spec §3)
│                        002_sprint_week_active_days.sql (Phase 12 — mask column)
│                        003_task_status_and_app_settings.sql (Phase 18 — task-cell status + KV)
│                        004_task_metadata_and_links.sql (Phase 22 — task description/url + linked_task_id)
│                        005_auth_throttle.sql (R01-N06 — local-admin login throttle)
├── views/               (Twig 3) layout.twig, layout-bare.twig, home.twig,
│                        auth/local.twig, workers/index.twig,
│                        users/index.twig, audit/index.twig,
│                        settings/index.twig,
│                        sprints/{new,show,settings,present}.twig,
│                        sprints/_task_list.twig (shared partial),
│                        sprints/import_upload.twig (Phase 20),
│                        sprints/import_preview.twig (Phase 20)
├── tests/               TestCase + Services/ + Repositories/ + Controllers/ +
│                        Cascade/ + Domain/ + Db/ + Http/ (Phase 19 TwigViewTest)
└── data/                SQLite + sessions directory + twig-cache/
                         (volume-mounted, gitignored)

4. Schema (migrations/001..005)

Tables (already applied): users, workers, sprints, sprint_weeks, sprint_workers, sprint_worker_days, tasks, task_assignments, audit_log, app_settings (Phase 18 — KV store for global flags), auth_throttle (R01-N06 — local-admin login throttle), plus the schema_version tracking table.

Phase 22 (migration 004) adds three columns to tasks: description TEXT NOT NULL DEFAULT '', url TEXT NOT NULL DEFAULT '', and linked_task_id INTEGER REFERENCES tasks(id) ON DELETE SET NULL — set on a copy and pointed at the source. Plus index idx_tasks_linked.

R01-N06 (migration 005) adds auth_throttle(ip_address, email, attempts, first_failure_at, last_failure_at, locked_until) with PK (ip_address, email) plus index idx_auth_throttle_locked. AuthThrottleRepository owns the policy: 5 failures in a 15-minute window → 5-min lock, 10 → 30-min, 20+ → 1-hour. A successful sign-in deletes the row.

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.
task_assignments.status ∈ {zugewiesen, gestartet, abgeschlossen, abgebrochen} (Phase 18). DB CHECK constraint enforces this.
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 | — | | POST | /csp-report | — (browser-fired, R01-N19 — no CSRF; 16 KiB body cap; audits as CSP_VIOLATION/csp_violation; always 204) | | 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/import | admin | | POST | /sprints/import | admin (multipart, _csrf) | | GET | /sprints/import/{token} | admin | | POST | /sprints/import/{token} | admin (form _csrf) | | GET | /sprints/{id} | signed-in | | GET | /sprints/{id}/present | signed-in | | GET | /sprints/{id}/settings | admin | | POST | /sprints/{id}/delete | admin (form _csrf + confirm_name must match sprint name verbatim) | | GET | /audit | admin | | GET | /settings | admin | | POST | /settings | admin (form CSRF via _csrf) |

JSON (admin-only, CSRF via X-CSRF-Token header; envelope per spec §7): | Method | Path | What | |--------|----------------------------------------------|---------------| | PATCH | /sprints/{id} | name/dates/reserve — when start_date or end_date changes, week rows are auto-resynced (count = ⌊(end−start)/7⌋+1, capped at 26; existing rows realign + audit) | | POST | /sprints/{id}/weeks | resize week set (legacy; UI no longer calls it — kept for back-compat) | | 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 (days only) | | PATCH | /tasks/{id}/assignments/status | batch cell status — any signed-in user; gated by app_settings.task_status_enabled (403 when off) | | POST | /tasks/{id}/move | move task to another sprint (drops assignments, audited) — Phase 22 | | POST | /tasks/{id}/copy | clone task into another sprint with linked_task_id = source.id — Phase 22 |

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.
R01-N23 adds two domain-table actions on users: TOMBSTONE (when an admin marks another user as (former user), also forcing is_admin = 0) and RESTORE (when the marker is cleared). Same one-row-per-change rule, normal before / after snapshot.
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, IMPORT_PREVIEW_ABANDONED, CSP_VIOLATION) → always one row. R01-N06 adds a second LOGIN_FAILED reason on the local-admin path: local_admin_throttled_until_<iso> is written when the (ip, email) bucket is currently locked, separate from the existing local_admin_credential_mismatch row written when the password itself was wrong. R01-N19 adds CSP_VIOLATION / entity_type=csp_violation rows: the browser POSTs the JSON report body to /csp-report, the controller unwraps the standard {"csp-report": {...}} envelope, and the inner object lands in after_json. entity_id is null; user_id / user_email are null (browser reports rarely carry session context); IP / UA come from the request. Body cap: 16 KiB.

8. Env (.env.example)

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

# Hard switch to disable OIDC even when ENTRA_* are populated. Accepted
# disabling values: false / 0 / no / off (case-insensitive). Blank/unset
# = enabled. Used by dev / testing / on-prem deployments to route all
# sign-ins through LOCAL_ADMIN_* below.
OIDC_ENABLED=true

# Reverse-proxy trust (R01-N05 / R01-N07). Comma-separated CIDRs of the
# proxies in front of the app. When `REMOTE_ADDR` matches one of these the
# app walks `X-Forwarded-For` for the originating client IP (audit log,
# login throttle bucket) and honours `X-Forwarded-Proto: https` for
# Secure-cookie / HSTS / HTTP→HTTPS-redirect decisions. Blank ⇒ no trust.
TRUSTED_PROXIES=

# Optional explicit OIDC bootstrap (R01-N03). When the `users` table has no
# admin and the signing user matches one of these (case-insensitive,
# timing-safe), they are promoted on sign-in. Either or both may be set;
# leave both blank to disable OIDC auto-promotion entirely.
BOOTSTRAP_ADMIN_OID=
BOOTSTRAP_ADMIN_EMAIL=

# Optional local admin fallback (disables when blank).
# Password is verified with PHP's password_verify() against the bcrypt hash
# stored in LOCAL_ADMIN_PASSWORD_HASH; the plaintext password never lands on
# disk. Generate the hash via `password_hash($pw, PASSWORD_DEFAULT)` (see
# README's Quick setup). The resulting user row has
# entra_oid="local:<email>", is_admin=1.
LOCAL_ADMIN_EMAIL=
LOCAL_ADMIN_PASSWORD_HASH=
LOCAL_ADMIN_NAME=Local Admin

First-admin bootstrap (R01-N03 hardening):

OIDC path. Auto-promotion requires users.countAdmins() === 0 AND the signing user's oid/email to match BOOTSTRAP_ADMIN_OID / BOOTSTRAP_ADMIN_EMAIL. With both env vars blank, OIDC never promotes — the operator must seed the first admin via the local-admin fallback or by flipping users.is_admin = 1 directly. The promotion emits a BOOTSTRAP_ADMIN audit row tagged via=oidc.
Local-admin path. LOCAL_ADMIN_EMAIL + LOCAL_ADMIN_PASSWORD_HASH is itself an explicit env-bootstrap, so the local user is always promoted on sign-in (forceAdmin=true). When the DB was empty before this sign-in, a BOOTSTRAP_ADMIN audit row tagged via=local is also recorded.

The pre-R01-N03 behaviour (first user to sign in via any path becomes admin) is gone — see src/Auth/BootstrapAdmin.php.

OIDC kill-switch (OIDC_ENABLED=false):

OidcClient::isConfigured() honours the flag — when set to a falsey value (false / 0 / no / off, case-insensitive, leading/trailing whitespace trimmed), it returns false even if ENTRA_* are populated. The home page hides the "Sign in with Microsoft" button, /auth/login returns 503 with the standard config-error page, and the OIDC callback path stays inert.
The flag is intended for dev / testing / on-prem setups that want to authenticate through LOCAL_ADMIN_* only — typical pairing is APP_ENV=development + OIDC_ENABLED=false + LOCAL_ADMIN_EMAIL / LOCAL_ADMIN_PASSWORD_HASH.
Production guard (public/index.php): when APP_ENV=production AND OIDC is not configured AND local admin is not enabled, the bootstrap refuses to serve any request — 503 + Retry-After: 30 + error_log line. This stops a fully unreachable instance from shipping silently.

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. ACCEPTANCE.md captures the spec §10 manual walkthrough.
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.
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.
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.
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.
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 <button data-menu-trigger> with an inline-SVG hamburger (three <line>s, stroke-current, no external asset). The <div id="app-menu" data-menu role="menu" hidden> panel is absolutely positioned with min-w-[12rem], rounded border, bg-white shadow-lg, items px-3 py-2 text-sm hover:bg-slate-50 plus a focus ring. Admins see Workers / Users / Audit log / <hr> / Sign out; non-admins see just Sign out (no divider). Sprints, New sprint (admin only), and the user badge stay inline. Sign out remains a native <form method="post" action="/auth/logout"> with the _csrf hidden input — no JS-driven POST. public/assets/js/ app.js gains a ~30-line vanilla-JS IIFE (document. querySelector + addEventListener, no jQuery) that toggles hidden + aria-expanded on click, closes on outside-click / Escape (returning focus to the trigger) / any role= "menuitem" click. CSP stays strict. Zero PHPUnit changes — 88 / 208 holds. ACCEPTANCE.md gains a "Phase 14 — Hamburger menu" section with the four manual scenarios from the plan.
[x] Phase 15 — Big-screen (beamer) task viewer at /sprints/{id}/present (d1dda4f). New signed-in route renders a stripped-down view: no shared layout chrome, no Arbeitstage matrix, no capacity summary — just a thin top bar (sprint name + dates + Close) and the task-list toolbar
- table. SprintController::show() keeps its behaviour; the shared data fan-out is extracted into a private loadSprintPage(int $id): ?array helper that both show() and the new present() method call, returning null for a missing sprint so each caller renders its own 404. views/sprints/present.php emits its own <!doctype html> (rendered with layout=null) reusing /assets/css/app.css
- the jQuery / jQuery UI CDN tags from layout.php + /assets/js/sprint-planner.js defer. The root <main> carries beamer-root + data-sprint-root + data-sprint- id + data-csrf + data-reserve-fraction + data- beamer="1". sprint-planner.js detects the beamer flag, namespaces its three localStorage keys with a :beamer suffix (so presentation filters don't clobber the user's /sprints/{id} workflow), seeds ["owner","prio","tot"] into hiddenCols:beamer on first load (before the first applyColumnVisibility() so nothing flashes), and after the boot applyFilters() measures table.scrollWidth > container.clientWidth; if it overflows, adds .beamer-vertical-headers (rotates sw column headers 90°); if it still overflows, console.warns and falls through to horizontal scroll — never a hang. Strict CSP unchanged. CSS scoping block lives under @layer components in assets/css/input.css (.beamer-root table typography + padding, .handle + [data-delete-task] hidden, vertical- header rule). Entry point is a new "Present" anchor next to Settings in views/sprints/show.php, target="_blank" for all signed-in users. Tests unchanged at 88 / 208 — refactor is a pure extraction and the sanity test the plan allowed was skipped because the existing tests/ Controllers/ harness only runs pure statics; a full controller integration test would need PDO + session wiring out of scope for this phase. ACCEPTANCE.md gains a "Phase 15 — Big-screen viewer" section with the six manual scenarios from the plan.
[x] Phase 16 — Dark-mode toggle + light-mode contrast cleanup (94b2841). Two small palette issues addressed at once: (a) both body and table-header bands used bg-slate-50, so table headers blended into the page — body bumps one shade cooler to bg-slate-100 (the user's explicit ask), <thead> bands stay at bg-slate-50 and now read as a distinct lighter strip; (b) no dark palette existed at all, painful for the Phase 15 beamer view in dim conference rooms. Manual toggle only — no prefers-color-scheme auto-detect. tailwind.config.js gains darkMode: 'class'. New public/assets/js/theme-init.js (8 lines, synchronous <script src> in <head> before the stylesheet) reads localStorage['sp:theme'] inside a try/catch and sets <html class="dark"> if the value is 'dark' — no FOUC. views/layout.php and views/sprints/present.php both include the init script; the present route emits its own <!doctype html>, hence two tags. public/assets/js/app.js grows a third vanilla-JS IIFE (~17 lines) wiring [data-theme-toggle]: toggle the class on <html>, write sp:theme, stamp the [data-theme-label] text; writes wrapped in try/catch so private-window denials no-op. The toggle lives in the hamburger menu in views/layout.php as a new "Theme" row above the <hr> divider, visible to admins and non-admins alike (theme is a personal preference, not an admin action); the divider now always renders (previously only admins saw one) because the Theme row always renders. Every view file (views/layout, views/home, views/auth/local, views/workers/index, views/users/index, views/sprints/{new,settings,show, present}, views/audit/index) gets a systematic dark: sweep: body/card/header surfaces on the slate-900/800/700 ramp, borders on slate-700 (600 for inputs), primary text slate-100, secondary slate-400, inputs dark:bg-slate-800 dark:border-slate-600 dark:text-slate-100 dark:focus:ring-slate-500, links dark:text-blue-400 dark:hover:text-blue-300, success / error / amber flash chips on *-900 backgrounds with *-200 text and *-800 borders, admin badge dark:bg-amber-900 dark:text-amber-200, Phase 12 weekday dots active dark:bg-green-400 / off dark:bg-slate-600, capacity "available" red dark:text-red-400, audit action chips similarly remapped. assets/css/input.css needed no edits — it carries no colour classes (only the .focus-auto-hidden utility and the .beamer-root typography block, both colour-free). Strict CSP stays intact (theme-init.js is a standard <script src> under script-src 'self'). Sign-out form block untouched — still a native POST with the _csrf hidden input. Zero PHPUnit — same pattern as Phases 10, 13, 14, 15; the change is CSS class additions plus ~25 lines of vanilla JS without a unit surface the existing harness can reach. Tests at 88 / 208. ACCEPTANCE.md gains a "Phase 16 — Dark mode + light contrast" section with the six scenarios from the plan (light-band separation, toggle flip, reload persistence under Network throttling + no FOUC, present view inherits dark, admin-pages contrast sweep, private-window localStorage denial fallback).
[x] Phase 17 — Hide native number spinners + custom slider popover for number inputs (b457896, UX tweak c07af1c, hotfix 832b256, rewrite 15b2d24, blur-fix f189ef7, close-fix 8d79f96, rebuild ff807c2, direct-listeners e93df6b). Three classes of number input — day cells, RTB cells, task assignment cells — deal in half-day increments (or 0.05 for RTB). Browsers rendered each as <input type="number"> with tiny native up/down spinner arrows: visually noisy in a dense table, inconsistent across Chrome / Firefox / Safari, useless on touch. Hidden app-wide via a two-rule @layer base block in assets/css/input.css (-webkit-*-spin-button { appearance: none; } + -moz-appearance: textfield). Week-count and reserve-percent inputs lose arrows too — fine, keyboard typing is their usual path. For the three opt-in cell types, a new public/assets/js/number-stepper.js (~180 lines, single IIFE, vanilla JS, no jQuery dep) delegates click + focusin on document to input[data-stepper]; on match, lazily builds a single .stepper-popover DOM node (role="dialog", − / <output> / + / hidden <input type="range">) and anchors it next to the bound input. Reads step / min / max off the input (default step=1); when both min and max parse as finite, un-hides the range slider and wires it; otherwise [data-assign] gets just the +/− buttons. clampToStep(current, delta, step, min, max) pure helper — adds delta, clamps to [min,max] when finite, quantises via Math.round(next/step) * step with a ~1e-9 epsilon tolerance so 0.6 + 0.05 = 0.65 lands cleanly. Every mutation mirrors into input.value and dispatches a bubbling synthetic input event, so sprint-planner.js's existing recomputeRow / row-total handlers fire live. On popover close (outside pointerdown, Escape, Tab-away, or clicking a different stepper input) the helper dispatches change — the existing debounced save pipeline (PATCH /sprints/{id}/week-cells, /workers/{sw_id}, or /tasks/{id}/assignments) fires once, same audit semantics as typed edits. ArrowUp / ArrowDown while focused on the input step by step (restores the shortcut the CSS reset just disabled). Position: below the input with a 4px gap unless the input sits in the lower 25% of the viewport, then above; horizontal clamp to viewport with 4px margins. Outside-click uses pointerdown (not click) so a Safari scroll gesture starting inside the popover doesn't dismiss it. data-stepper stamped on the admin-branch [data-day] / [data-rtb] / [data-assign] inputs in views/sprints/show.php, the [data-rtb] input in views/sprints/settings.php, the [data-assign] input in views/sprints/present.php, and the JS-built assignment cells in sprint-planner.js::buildTaskRow — non-admin <span> branches stay clean. Popover styled in assets/css/input.css under @layer components with Tailwind @apply on the Phase 16 palette (bg-white / dark:bg-slate-800 + slate-100/200/600/700 hover/border/text siblings + accent-slate-600/400 on the range). Both views/layout.php and views/sprints/present.php load the new module via <script src="/assets/js/number-stepper.js" defer> after sprint-planner.js — strict CSP stays intact, no inline handlers, no new external hosts. Zero PHPUnit — pure CSS + vanilla JS over existing markup, same pattern as Phases 10 / 13 / 14 / 15 / 16. Tests at 88 / 208. ACCEPTANCE.md gains a "Phase 17 — Number stepper popover" section with the five scenarios from the plan (no native arrows on any number input, day-cell stepper at 0.5 step, RTB stepper at 0.05 step, task-assignment stepper on both show and present views with no range slider because no max, Escape + outside-click + dark-mode polish). UX tweak c07af1c: the popover is now hover-to-open (pointerover / pointerout with a 200 ms grace timer so transit between input and popover is safe) instead of click-to-open, and the optional range slider renders vertically (writing-mode: vertical-lr + direction: rtl, with slider-vertical / orient="vertical" fallbacks). Focus and pointerdown triggers remain for keyboard and touch users respectively. Hotfix 832b256: readBounds() was using the IDL boundInput.max property which coerces a missing max attribute to the empty string and then to 0 via Number("") — the result was that task-assignment cells (which set min="0" but have no max) were clamped to [0, 0] and the +/− buttons appeared to do nothing on any table but the Arbeitstage grid. Switched to getAttribute()
- explicit null/empty check so missing bounds report as NaN. Same commit centres the popover on the input's horizontal midpoint instead of aligning to its left edge — looks balanced over narrow table cells. Rewrite 15b2d24 lands the final interaction model: the popover is now slider only — no +/− buttons, no numeric output — opens on click (not hover) of any input[type="number"] app-wide (no data-stepper opt-in needed; the attribute stays harmless on the existing markup), anchors to the right of the input at its vertical midpoint, flips to the left when the right edge would clip the viewport, and closes as soon as the pointer leaves the popover after entering it at least once (a popoverEntered latch forgives click-to-open cursors that already sit inside the popover rectangle). Task-assignment inputs, which declare min="0" but no max, get an adaptive slider max of Math.max(current + 5, 10) so the slider is always useful. Dragging fires change on the bound input on every tick; sprint-planner.js's 400 ms debounce coalesces the flurry into one server write while its capacity recompute runs on every tick so Ressourcen / Available / ≤ reserves update smoothly during the drag. Escape, outside-pointerdown and Tab-out fallbacks remain for keyboard / touch paths. Blur-fix f189ef7 plugs a gap the rewrite left open: the popover now closes whenever focus leaves either the bound input or a focusable element inside the popover, unless focus is moving into another eligible number input (seamless rebind, no flicker). Catches the case where a click elsewhere blurred the input but never landed on a registered "outside" target. Replaces the Tab-specific keydown handler, which is now a subset of the generic focusout behaviour. Close-fix 8d79f96: the "mouse leaves" and outside-click paths were unreliable in real use — the dismissal required the cursor to physically enter the popover rectangle at least once (popoverEntered latch), so a user who clicked a cell and moved the cursor anywhere the popover wasn't would see the popup linger indefinitely. Replaced the latch with a document-level pointermove tracker: while the popover is open, if the cursor is over neither the bound input nor the popover for more than 150 ms, close. A 250 ms initial grace after open() covers the first few frames where the cursor may briefly be in the gap. Outside-click close is now registered in both bubble (pointerdown) and capture (click, {capture:true}) phases via a shared handler so a downstream stopPropagation can't strand the popup. The viewport-exit pointerleave on document is also a close trigger — the pointer heading for the browser chrome shouldn't leave the popup behind. Rebuild ff807c2: after seven iterations the file had accumulated contradictory event plumbing (hover-to-open co-existing with click-to-open, two close latches, two outside- click paths, stopPropagation on the popover's own pointerdown) and still failed in practice — the popup wouldn't close on drift or outside click, slider clicks sometimes stole focus without mutating the value, and the popup didn't follow the input on page scroll. Threw the file out and rebuilt it from scratch as a single ~250-line IIFE with exactly four concerns: open (click / focusin → configure slider + position), slider-to-input sync (one input listener mirrors elRange.value → boundInput.value and dispatches bubbling input+change events), close (pointer off both rects > 200 ms past a 300 ms open-grace window / viewport exit / capture-phase outside-pointerdown / Escape — all testing live getBoundingClientRect so repositioned popovers don't leave stale hit rects), and scroll anchoring (window.addEventListener('scroll', fn, {capture:true}) plus resize, rAF-throttled reposition; closes if the bound input collapses to 0×0). The CSS was untouched — the visual style is identical. Kept the ArrowUp/Down keyboard-nudge bonus that replaces the spinner-arrow shortcut. Direct-listeners e93df6b: even after the rebuild the close logic still didn't fire reliably — every close path was routed through document-level delegation (pointermove tracker, capture-phase pointerdown, pointerleave on document), and whatever was silently suppressing those events on the user's page suppressed all of them at once, leaving the popup stranded. Swapped the mouse-tracking close paths to element-local pointerenter / pointerleave listeners attached directly to the two elements that matter — the popover (once, in build()) and the bound input (per-open via bindInput(), detached via unbindInput() on close or rebind). pointerleave on either schedules close; pointerenter on either cancels it; the 300 ms open-grace keeps the timer rescheduling itself during the first 300 ms after click-to- open. Outside-click keeps the capture-phase document pointerdown, now with a 50 ms OPEN_IGNORE_MS guard so the opening click can't be misread as an outside click. Removed the document-level pointermove tracker, the document-level pointerleave viewport-exit handler, and the focusout handler entirely — superseded.
[x] Remove number-stepper slider popover (e551705): after seven iterations the click-to-open vertical-slider popover added in Phase 17 still didn't behave reliably, and the team prefers plain typed entry. Deleted public/assets/js/number-stepper.js, dropped the <script> tags from views/layout.php + views/sprints/present.php, removed the .stepper-popover CSS block from assets/css/input.css, and stripped data-stepper attributes from views/sprints/{show, settings,present}.php and the JS-built cell in sprint-planner.js::buildTaskRow. Kept the @layer base rule that hides native number-spinner arrows app-wide — typing is now the only edit path on every number input; ArrowUp/ArrowDown still steps via browser default. No schema / route / PHP changes; tests untouched at 88 / 208.
[x] Phase 18 — Per-cell task-status colours + filter + global toggle (9cb7669, hotfix 3e115f5). Each task- assignment cell on both /sprints/{id} and /sprints/{id}/present now carries a workflow status — zugewiesen (transparent, default), gestartet (yellow), abgeschlossen (green), abgebrochen (red) — picked from a chevron-only <select data-assign-status> next to the day input/span. The status colour class (assign-status-{state}) and data-assign-cell / data-status / data-sw-id attrs live directly on the <td> — the original commit's <span class="assign-cell"> wrapper turned out to be a layout trap inside table cells (display:inline-flex on a span around an input with a sibling select made the day inputs collapse on blur in production), so the hotfix flattened the structure: the day input is now a direct child of the <td>, the status select sits as a sibling, no wrapper. sprint-planner.js mirrors the chosen value into the wrapper class + data- status and queues a save through a new pendingStatus/flushStatus debounced pipeline that hits PATCH /tasks/{id}/assignments/status (400 ms, same semantics as the days pipeline). New "Status" multi-select filter sits between Owners and Focus in the toolbar; a row passes when at least one cell is in the picked set, with a special-case rule that the default zugewiesen only matches when days > 0 (so picking it doesn't include every task). State persists in localStorage (sp:{sprintId}:statusFilter, :beamer-namespaced for the present view) and is wiped by the existing Reset button. Schema: migrations/003_task_status_and_app_settings.sql adds task_assignments.status TEXT NOT NULL DEFAULT 'zugewiesen' with a CHECK constraint, and creates a new app_settings(key TEXT PK, value TEXT NOT NULL, updated_at TEXT NOT NULL) KV table seeded with ('task_status_enabled', '0') so the feature is opt-in. App\Repositories\AppSettingsRepository (get/getBool/set) reads it; SprintController::loadSprintPage() passes taskStatusEnabled + statusGrid into both views, which conditionally render the per-cell selectors and the toolbar Status filter. New admin-only /settings page (linked from the hamburger menu, App\Controllers\SettingsController) flips the toggle via a native form POST with _csrf; audit row entity_type='app_setting'. New PATCH /tasks/{id}/assignments/status is the first non- admin write surface in the app — gated by SessionGuard::requireAuthJson (auth + CSRF, no admin) plus app_settings.task_status_enabled (403 when off). Existing PATCH /tasks/{id}/assignments stays admin-only and days- only; TaskAssignmentRepository::upsert preserves status, ::upsertStatus preserves days and inserts a days=0 row when the cell didn't exist (so a state can be tracked before any work is assigned). Per-cell audit semantics unchanged — one row per changed cell. The four .assign-status-* class names are interpolated server-side, so tailwind.config.js gains a safelist keeping them in the build (Tailwind was silently dropping them otherwise — the :not() reference happened to keep zugewiesen in but the other three vanished). Strict CSP unchanged. Tests: 105 / 265 (was 88 / 208) — +TaskAssignmentTest (status enum + audit-snapshot shape), +AppSettingsRepositoryTest (seeded flag, get/set roundtrip, no-op equality, default fallback), +TaskAssignmentRepositoryTest (upsertStatus's four cases, days writes preserving status, InvalidArgumentException guard, statusGridForSprint).
[x] Cell popover replaces per-cell status select (10ea4b8): the Phase 18 chevron <select data-assign-status> next to each task-day input is gone. Clicking a day input (admin) or the new [data-assign-readonly] span (non-admin) now opens a single body-attached .cell-popover panel anchored 8 px right of the cell (flips left if it would overflow the viewport). Left column: a <input type="range" min="0" step="0.5"> whose max comes from a new admin-configurable setting assignment_slider_max (1..100, default 10); dragging mirrors into input.value and dispatches change, so the existing 400 ms debounced days-save pipeline + row total + capacity recompute fire unchanged. The slider is hidden on the non-admin path (no input to mirror). Right column: four status pills with coloured bullets (slate / yellow / green / red, matching the Status filter dropdown) — picking one is terminal: set data-status + .assign-status-* on the cell, queue the existing PATCH /tasks/{id}/assignments/status save, refresh filters, close. Other close triggers: outside pointerdown (capture phase, 50 ms grace after open so the opening click isn't misread), Escape, scroll / resize, and a 250 ms mouseleave grace. New AppSettingsRepository::getInt; SettingsController::KEYS shape changed from key=>label to key=>[type, label] so int + bool keys can coexist. Server clamps the int to [1, 100]; the per-cell day input itself stays unbounded (typing > max still works). The legacy .assign-status-select CSS block was deleted; the new .cell-popover* + .bullet-* block lives in the same @layer components section in assets/css/input.css. Strict CSP unchanged — no inline handlers, no new external hosts, the popover element is JS-built and appended once. Tests: 109 / ? (+1 for getInt round-trip + numeric-string guard); the prior 108 tests pass without modification. ACCEPTANCE.md gains a note pointing at the popover for the manual cell-edit flow.
[x] Filter dropdown close polish (9b72c41, 1864835): the Owners / Status / Columns dropdowns no longer get cropped when the task table holds a single row (overflow-hidden was on the section; replaced with rounded-t-lg on the toolbar div + rounded-b-lg on the table wrapper so the rounded corners survive). Each dropdown closes on mouseleave of its data-*-root element with a 250 ms grace timer that mouseenter cancels — the previous naive listener fired while the cursor was in transit across the mt-1 gap and shut the panel before the user could reach it. Owner / Status Clear now also closes the dropdown.
[x] Task table polish (f204611): three small UX bugs in the shared task list partial fixed in one commit. (a) Sortable headers (Task / Owner / Prio / Tot) gained whitespace-nowrap; previously the label and the ↕ sort indicator could wrap to two lines on narrow viewports. (b) Per-row assignment cells gained whitespace-nowrap so the day input and the Phase 18 status <select> always sit on the same line — no second-row flow when the cell shrinks. (c) .assign-status-select was effectively invisible — width: 1rem (16px), border: 0, font-size: 0, transparent background — bumped to 22×22 px with a real bordered button affordance, text-indent: -9999px to hide the selected text reliably (works where Safari's font-size: 0 cancels the chevron too), and a dark- mode variant. (d) .assign-status-* tint moved off the <td> onto the inner input[data-assign] / read-only span — cell padding no longer paints a stripe of status colour around the field; only the field itself reflects state. The class still lives on the <td> so the Status filter and JS reads off data-status are unchanged. buildTaskRow in sprint-planner.js mirrors the new whitespace-nowrap on JS-built assignment cells (admin "+ Add task" path). Pure CSS / Twig / vanilla JS — tests untouched at 108 / 281.
[x] Sprint view tabs + smart Close on present (2813019): /sprints/{id} is now split into two tabs — "Arbeitstage and capacity" (Arbeitstage matrix + capacity table + the snap/save help line) and "Capacity and tasks" (capacity table + task list
- the 100 px popover-anchor spacer). The capacity table was extracted into a {% macro capacity_table(sprintWorkers, capacity) %} in views/sprints/show.twig and rendered twice via {{ _self.capacity_table(...) }}. sprint-planner.js's recomputeRow and applyServerCapacity switched from qs (first match) to qsa for the [data-cap-ressourcen|after- reserves|available] lookups so both copies update in lockstep on every edit, server response, and boot. A new initTabs IIFE in the boot section persists the active tab in localStorage under sp:{sprintId}:tab (:beamer-namespaced for parity with the other keys, even though the present view has no tab nav). Tab buttons use data-tab-btn/data-tab-panel plus Tailwind 3.4 data-[active=true]: variants for the underline + colour; no JS-driven class toggling needed beyond flipping data-active and the hidden class on the panels. Default tab is "arbeitstage" — survives until first selection, persists thereafter. The "Present" view's Close button is now smart: a new initSmartClose IIFE intercepts the click — if window.history.length > 1 (the user navigated to the present view within an existing tab), history.back(); otherwise window.close() with a 100 ms fallback to navigate to /sprints/{id} if the browser blocks close() (typed-URL or bookmark-opened pages can't always self-close). The link's href is preserved as the navigation fallback. Pure Twig + vanilla JS over existing endpoints — no schema, route, or audit changes; CSP unchanged. Tests at 108 / 281 (no PHP touched, no new test surface — same pattern as Phases 10, 13, 14, 15, 16). ACCEPTANCE.md not yet updated for the tab walkthrough.
[x] Phase 19 — Twig 3 + Tailwind 3 + Alpine CSP + htmx + SortableJS, jQuery removed (75e96e2). Stack-shift of the entire UI layer with zero changes to controllers, repositories, schema, capacity math, or audit semantics — every behaviour preserved end-to-end. All 11 views/*.php rewritten as views/*.twig using {% extends "layout.twig" %} inheritance; new layout-bare.twig backs /sprints/{id}/present's own <!doctype html>; new _task_list.twig partial is shared by show.twig and present.twig. src/Http/View.php now wraps Twig\Environment while keeping the historical render($name, $data, $layout) signature so controllers don't change; auto-escape ON; compiled cache written to data/twig-cache/ (gitignored, www-data-owned via the Dockerfile so first-render compilation succeeds). The legacy App\Http\e() helper stays defined for backwards compatibility but is unused by Twig templates. ~1500 lines of jQuery / jQuery UI deleted from app.js, sprint-planner.js, sprint-settings.js; each rewritten as a pure-vanilla IIFE using fetch + delegated addEventListener against the existing JSON-envelope endpoints. SortableJS replaces jQuery UI sortable on the three drag-reorder lists. Alpine (CSP build, @alpinejs/csp — no unsafe-eval) drives the hamburger menu (appMenu factory) and theme toggle (themeToggle factory); everything else is vanilla JS. htmx loaded site-wide; CSRF token attached via htmx:configRequest; hx-boost="true" sprinkled on the simple form-post pages (/auth/local, /workers create+edit, /users/{id}, /settings, /sprints/new, /audit filter) so submissions AJAX-swap the body without a full reload. Sprint show/settings/present pages stay native — their page-specific IIFEs would not re-init after a body swap. CSP tightened: script-src 'self' and style-src 'self' only — https://code.jquery.com dropped from every directive. Dockerfile css-builder stage now also vendors node_modules/{@alpinejs/csp,htmx.org,sortablejs}/dist/*.min.js into /build/vendor/, which the runtime stage COPYs into public/assets/js/vendor/ (gitignored). New runtime dep twig/twig ^3.10; new dev deps alpinejs, @alpinejs/csp, htmx.org, sortablejs. Tests: 108 / 281 (was 105 / 265) — new tests/Http/TwigViewTest.php adds three smoke renders (home as signed-in admin, audit/index empty, sprints/show with task grid + status filter); the prior 105 tests pass without modification. tailwind.config.js content glob switched from views/**/*.php to views/**/*.twig. The number-spinner reset (@layer base block in assets/css/input.css) and Phase 18 .assign-status-* safelist + status-select styling all carry over unchanged.
[x] Phase 20 — XLSX import wizard (8876239). Two-step admin-only wizard at /sprints/import that ingests the team's historical Tool_Sprint Planning.xlsx workbook into the database one tab per sprint. Step 1 is a multipart upload form with strict validation (≤ 5 MB, .xlsx extension, PK\x03\x04/PK\x05\x06 ZIP magic-byte check). Step 2 (/sprints/import/{token} where token is a 32-char hex random session key, TTL 30 min) shows one preview panel per parsed sheet: sprint-name input pre-filled from the tab name, inferred start/end dates pre-filled from KW-row + closest-year heuristic (using setISODate() over ±1 year and picking the nearest match to today), reserve fraction read-only from the workbook, target picker (Create new sprint / Merge into empty existing sprint — non-empty sprints are filtered out of the dropdown server-side), diff summary listing workers-to-create + assignment-cell totals + per-status colour counts, and a per-sheet "skip" toggle. New service trio: App\Services\Import\XlsxColorClassifier (pure ARGB → status, hue/saturation thresholds with no PhpSpreadsheet dep — bins green H 80..170/S>0.15, yellow+orange H 20..80, red H 340..20/S>0.20, everything else → zugewiesen; near-white L>0.96 and S<0.10 banding both fall through to default), App\Services\Import\ XlsxSprintImporter (parses with the fixed coordinate map locked down from the sample file: C6 = "Arbeitstage" / E.. = max working days, C7 = "Datum", C8 = "KW", C9.. worker name + E.. per-week days + J RTB + K Σ-formula, J{r} = "Reserven" ends the worker block; M4 = "Tasks", Q4.. worker-name formulas (=C9, =C10, …) for the authoritative task-column → Arbeitstage worker mapping which skips Arbeitstage gaps, M9..P9 = To Do/Owner/Prio/Tot header, M10.. task title with N owner, O prio, Q.. per-worker assignments; the parser tolerates up to 2 consecutive empty rows in both blocks so the sample workbook's row-13 visual gap doesn't truncate Sprint 2), and App\Services\Import\SprintImporter (transactional commit that creates or merges-into-empty sprints, materialises sprint_weeks with active_days_mask = (1 << maxDays) − 1 and max_working_days = popcount(mask), auto-creates missing workers by case-folded exact name with audit each, drops task-owner names that don't resolve into the global Workers table into a missingOwners warning list and creates the task with owner_worker_id = NULL, writes per-week days through the existing SprintWorkerDayRepository::upsert and per-cell days
- status through TaskAssignmentRepository::{upsert,upsertStatus} so the same audit semantics already in production carry over; one IMPORTED_FROM_XLSX audit row per sprint anchors the run). Composer dep: phpoffice/phpspreadsheet ^3.4. Dockerfile runtime stage adds zip + gd (PhpSpreadsheet require) on top of the existing pdo_sqlite. New App\Domain\Import\ value objects (ParsedSheet/Week/Worker/Task/Assignment + ImportResult) round-trip through toArray()/fromArray() so the parsed result can be JSON-stashed in $_SESSION['sp_imports'][$token] between steps without persisting the uploaded file to disk. Hamburger menu gains an "Import" link next to "New sprint" for admins. Strict CSP unchanged — no JS in the wizard, native form posts throughout. Tests: 143/392 (was 108/281) — pure-table-driven XlsxColorClassifierTest (20 cases incl. all six fills observed in the sample workbook + edges like fully-transparent, pure white, pure red, lavender), XlsxSprintImporterTest (5 smoke tests against doc/Tool_Sprint Planning.xlsx: 3 sheets parsed, Sprint 1 shape 5w/15w/>20t/0.2 reserve, Sprint 2 colour-count matches openpyxl audit (27 yellow/orange → gestartet, 5 green → abgeschlossen, 0 red), Sprint 2 16-worker count tolerating the row-13 gap, toArray/fromArray round-trip — auto-skipped when host PHP lacks dom/zip/xmlreader/simplexml/gd so the suite stays portable to thin developer environments), SprintImporterCommitTest (5 cases on the in-memory SQLite harness: full new-sprint commit shape + audit row count, refuse to merge into a non-empty existing sprint, case-folded worker reuse with leading/trailing whitespace, maxDaysToMask table, fold normalisation), ImportControllerTest (3 cases on the static helpers via reflection: ZIP magic-byte detection, wrong extension rejection, UPLOAD_ERR_* mapping). The classifier test was originally written with the legacy @dataProvider doc-block annotation; PHPUnit 11 deprecated metadata in doc-comments so we use the #[DataProvider] attribute.
[x] Phase 21 — Auto-derived week count in sprint settings: the "Set to N / Apply" form is gone; PATCH /sprints/{id} now resyncs sprint_weeks whenever start_date or end_date changes. Target count = floor((end − start)/7) + 1, capped at 26 (422 above). Existing rows are realigned (SprintWeekRepository::realignDates rewrites start_date/iso_week per sort_order offset, preserving active_days_mask); appended rows default to MASK_ALL; trailing rows shrink with the same audit-cascaded-days flow as the legacy replaceWeeks. The response gains a weeks_synced flag so sprint-settings.js can reload the page after a date edit. The per-week weekday checkboxes (Mo Di Mi Do Fr) and POST /sprints/{id}/weeks are unchanged — the JSON endpoint is kept for back-compat but the UI no longer calls it. +5 tests, 148 total.
[x] Phase 22 — Per-task hamburger menu: move / copy / edit / reorder (c2dad80). Replaces the SortableJS drag handle on the task table with a per-row hamburger button (admin only) that gathers every task-level action: Edit details… (modal with a description textarea, ≤8000 chars plain text, plus a url input — empty or http(s)://, ≤2048 chars), Move to sprint… (submenu of every other sprint), Copy to sprint… (same submenu), Move (pick up) (click-to-pickup → row follows cursor Y with an amber drop indicator → click-anywhere-to-drop, Escape cancels), Delete (the standalone × column was folded in). Worker-row SortableJS stays put — only the task-table draggable was removed. Title cell gains: a small external-link anchor when url is set (target="_blank" rel="noopener", visible to all), a description marker that opens a read-only popover (so non-admins can read descriptions without the edit modal), and bidirectional linked- task chips ("← Sprint X" for the source row, "→ Sprint Y" for every row that copies back). Two new admin-only JSON endpoints: POST /tasks/{id}/move (audits each task_assignment DELETE before the cascade, then UPDATE on tasks.sprint_id — task lands at MAX+1 in the destination's sort_order) and POST /tasks/{id}/copy (creates a fresh task with title / owner / priority / description / url cloned and linked_task_id = source.id; assignments are dropped per the design call). PATCH /tasks/{id} accepts description and url alongside the prior whitelist; URL is validated server-side (^https?:// or empty). Schema: migrations/004_task_metadata_ and_links.sql adds tasks.description TEXT NOT NULL DEFAULT '', tasks.url TEXT NOT NULL DEFAULT '', tasks.linked_task_id INTEGER REFERENCES tasks(id) ON DELETE SET NULL, and idx_tasks_linked — existing rows stay valid without backfill. TaskRepository::linkedSummariesForTasks() resolves both directions in two queries off the per-sprint task list; SprintController::loadSprintPage passes sprintChoices (every sprint except this one) + linkedMap into the show + present views, and the JS reads sprintChoices from a JSON-encoded data-sprint-choices attribute on data-task-section. The details modal, hamburger menu, description popover, and pickup indicator are all single body-attached nodes built lazily by sprint-planner.js — no Alpine, no htmx swap, strict CSP stays intact (no inline handlers, no new external hosts). New @layer components block in assets/css/input.css defines .task-menu + .task-menu-{item,sub-item,...}, .task-modal- {overlay,panel,header,body,footer}, .task-pickup-{active, indicator}, and .task-desc-popover; the Phase 15 beamer rule grows a [data-task-menu-trigger] hide so the present view stays read-only. Tests: 148 / 406 — unchanged. The new fields default empty/NULL so existing TaskRepository / cascade / status tests keep passing without modification; the migrator picks up 004 on next request.
[x] Sprint settings: secured Delete sprint action (8e8b8fd). New "Danger zone" section at the bottom of /sprints/{id}/settings with a POST /sprints/{id}/delete form gated three ways: SessionGuard::requireAdmin + verifyCsrf, plus a confirm_name field that must match sprint.name verbatim (server-side authoritative check, with a sprint-settings.js UX guard that keeps the submit button disabled until the typed name matches and a final window.confirm() so a misclick on the now-enabled button can't fire the destructive POST). Cascade audit (spec §7): SprintController::delete snapshots every descendant before the FK cascade fires — task_assignments, sprint_worker_days, tasks, sprint_workers, sprint_weeks — and records one DELETE per child plus one for the parent inside a single transaction. Phase 22's tasks.linked_task_id ON DELETE SET NULL on cross-sprint copies is also captured: rows in other sprints whose linked_task_id pointed at one of this sprint's tasks get an UPDATE audit (linked_task_id X → null) so the chain stays reconstructable. On success the user lands on /?deleted=<sprint name> with a green "Sprint X was deleted." chip rendered by views/home.twig. New SprintRepository::delete(int $id): ?Sprint returns the pre-deletion snapshot for the parent audit row, mirroring TaskRepository::delete. tests/Cascade/CascadeAuditTest.php grows a fourth path covering the full sprint delete: sets up a 2-worker / 4-week / 1-task / 2-assignment fixture, audits each leaf, drops the sprint, then asserts the per-entity-type audit count matches the cascade size. Tests: 149 / 424 (was 148 / 406).
[x] Header cleanup: Import moved into the admin dropdown (fe78f45). The /sprints/import link no longer sits inline in the header next to New sprint; it now lives at the top of the admin section of the hamburger menu (above Workers, Users, Audit log, Settings). Pure views/layout.twig edit — the route, ImportController, CSRF, and admin gating are all unchanged. New sprint stays in the header as the one quick-action admins reach for from any page.
[x] R01-N15 — noreferrer added to external task URL link (d16bff4). The user-controlled task link in views/sprints/_task_list.twig previously rendered with rel="noopener" only — that blocks window.opener access but still leaks the originating /sprints/{id} URL via the Referer header to whatever URL an admin saved as t.url. Sprint IDs are sequential integers, so a hostile t.url could confirm the existence of internal sprints just by inspecting its access logs. Switched to rel="noopener noreferrer". The fix is deliberately narrow: the /sprints/{id}/present anchor in views/sprints/show.twig keeps rel="noopener" because it is same-origin and the Referer leak the finding describes does not apply (browsers send the same-origin Referer to the same origin anyway). Pure twig edit; no test count change. Fourth fix from doc/REVIEW_01.md.
[x] R01-N01 — Local-admin password is hash-only (no plaintext fallback) (857df15). src/Auth/LocalAdmin.php previously read the password verbatim from LOCAL_ADMIN_PASSWORD and compared it with hash_equals(); anyone with read access to .env had immediate admin. Now the env var is LOCAL_ADMIN_PASSWORD_HASH (a password_hash() output, typically $2y$... bcrypt) and verification is password_verify(). Operator's explicit choice was the clean break — there is no plaintext fallback. Existing deployments must regenerate the hash before the next sign-in; until they do, LocalAdmin::isEnabled() returns false and /auth/local 404s rather than silently re-using stale config. Email comparison stays hash_equals() (timing-safe) on the trimmed input. Hash recipe lives in README §Quick setup step 3 and admin-manual §3.5 — both ship the host-PHP one-liner and the docker run --rm php:8.3-cli php -r '...' form for hash-without-host-PHP, with the single-quotes-in-.env warning so the $ segments aren't eaten by the shell that runs docker compose up. New tests/Auth/LocalAdminTest.php (9 cases) locks the contract in, including a regression guard asserting that LOCAL_ADMIN_PASSWORD alone does not enable the fallback. Tests: 159 / 443 (was 150 / 430).
[x] R01-N04 — SESSION_SECRET removed from env template + docs (296883c). The env var was documented as the salt for the session cookie name / CSRF tokens but nothing in the code reads it (SessionGuard doesn't reference it; CSRF tokens are bin2hex(random_bytes(32)); the session id is PHP-generated). Operators who rotated the secret expecting sessions / tokens to invalidate got a false sense of security. Took the "remove it" branch from REVIEW_01's two options — wiring the value into a CSRF HMAC or session-id derivation is recorded as a follow-up if a deploy-time rotation knob is wanted later. Touched .env.example, README.md, this file's §8, and doc/admin-manual.md (dropped §3.3 entirely; renumbered the remaining Database / Environment / Local admin subsections from §3.4-§3.6 down to §3.3-§3.5). Existing deployments' .env files keep their (now-dead) SESSION_SECRET= line; harmless. Pure docs change — no code, no test count delta.
[x] R01-N02 / R01-N31 — Runtime panel on / is now admin-only (7fd849b). views/home.twig's "Runtime" <details> block was previously rendered for anonymous visitors as well as admins, leaking PHP_VERSION, APP_ENV, the SQLite file path, the schema version, and the OIDC / local-admin enablement flags — useful reconnaissance for anyone scanning the public homepage. The twig guard tightened from currentUser is null or currentUser.isAdmin to currentUser is not null and currentUser.isAdmin. Same edit removes the in-page /healthz hint (R01-N31); the route itself stays public for liveness probes. New TwigViewTest::testHomeForAnonymousUserHidesRuntimePanel locks the behaviour in. Tests: 150 / 430 (was 149 / 424). First fix from doc/REVIEW_01.md.
[x] R01-N11 — Whitelist column in AuditRepository::distinctColumn (4ae1817). The private helper interpolated its $col argument directly into SQL ("SELECT DISTINCT {$col} FROM audit_log ORDER BY {$col} ASC"). Both internal callers (distinctActions, distinctEntityTypes) pass literal strings, so it was non-exploitable today, but the contract was implicit — any future caller wiring user input through the helper would hand over a SQL injection vector. Added an explicit in_array($col, ['action', 'entity_type'], true) whitelist at the top of distinctColumn(); anything else throws \InvalidArgumentException before the SQL is prepared. The phpdoc was tightened to the literal-string union ('action'|'entity_type') so static analysers carry the contract too. New tests/Repositories/AuditRepositoryTest.php (4 cases) covers the happy paths for both supported columns and two reflection-based regression guards (rejects user_email, rejects an injection attempt). Tests: 163 / 417 (was 159 / 413). Fifth fix from doc/REVIEW_01.md.
[x] R01-N06 — Throttle local-admin login by (ip, email) (e295432). The /auth/local path had no rate limiting — attackers could brute-force the password as fast as the server could respond. With R01-N01 already enforcing a hash-only credential, throttling is the remaining defence on the local-admin path (the OIDC path has Entra's own). New migration 005_auth_throttle.sql adds auth_throttle(ip_address, email, attempts, first_failure_at, last_failure_at, locked_until) PK (ip_address, email) + idx_auth_throttle_locked. New AuthThrottleRepository owns three operations (lockoutFor, recordFailure, clear) plus a purgeExpired housekeeping helper (not yet wired). Policy lives in the pure-static computeLockout(attempts, now): 1-4 → no lock, 5-9 → +5 min, 10-19 → +30 min, 20+ → +1 hour. Counter naturally rolls over after a 15-minute idle window so an honest user who later mistypes isn't penalised forever; a successful sign-in deletes the row outright. Email is canonicalised (lowercased + trimmed) to defeat case-variation bypass; IPs key verbatim so two attacker IPs don't share a lock. AuthController::loginLocal checks the lock BEFORE LocalAdmin::verify() (so a slow password_verify() can't be turned into an oracle by a still- locked attacker). Throttle hits emit a LOGIN_FAILED audit row with reason local_admin_throttled_until_<iso>, separate from the local_admin_credential_mismatch row written when the password itself was wrong. Form template gains an amber "Too many failed attempts" notice when the controller redirects with ?throttled=1. New tests/Repositories/AuthThrottleRepositoryTest.php (13 cases) pins the threshold matrix, the 4:59 / 5:00 lockout boundary, idle-window reset, IP / email bucketing, the full computeLockout matrix, and purgeExpired's selectivity — time is injected so no test sleeps. Tests: 176 / 484 (was 163 / 417). Sixth fix from doc/REVIEW_01.md.
[x] R01-N03 — Explicit env-bootstrap for the first OIDC admin (f565c86). The OIDC sign-in path used to auto-promote the very first user to land on /auth/callback (users.count() === 0). On a public-facing first deploy that was a land-grab — any tenant member with a valid Entra account could win the race against the intended operator. Two new env vars, BOOTSTRAP_ADMIN_OID and BOOTSTRAP_ADMIN_EMAIL, now name the bootstrap principal up front; OIDC auto-promotion requires countAdmins() === 0 AND a match (case-insensitive, trimmed, hash_equals). With both env vars blank, OIDC NEVER promotes anyone — operators must seed the first admin via the local- admin fallback (itself an explicit env-bootstrap) or by flipping is_admin in app.sqlite. The local-admin path is unchanged: forceAdmin: true continues to keep the configured local user admin on every sign-in, and the BOOTSTRAP_ADMIN audit row still fires on the first local sign-in into an empty users table — its after payload now carries via=local/via=oidc so /audit distinguishes the two channels. New src/Auth/BootstrapAdmin.php owns isConfigured() + matches(oid, email); blank incoming fields never opportunistically match an absent env value. New tests/Auth/BootstrapAdminTest.php (8 cases) pins the matcher; UserRepositoryTest comments lost their stale "count === 0" reference but the repo's promoteToAdmin / forceAdmin contract is mechanically unchanged. README + admin-manual rewritten: admin-manual gains a new §3.5 "Nominating the first administrator (OIDC)" and the old §3.5 (Local admin) shifts to §3.6. Tests: 184 / 502 (was 176 / 484). Seventh fix from doc/REVIEW_01.md.
[x] R01-N05 + R01-N07 — Trusted-proxy aware HTTPS detection & client IP (a2e77ea). Behind a reverse proxy the app used to record the proxy's address as the audit IP and to silently drop the session cookie's Secure flag whenever APP_BASE_URL was http:// (and never to redirect mistyped HTTP traffic to HTTPS). New src/Http/TrustedProxies.php owns the policy: a comma-separated TRUSTED_PROXIES= env var lists the CIDRs of hops that may speak X-Forwarded-For / X-Forwarded-Proto on behalf of the user, and clientIp() walks the XFF chain rightmost-to-leftmost to return the first hop that is not in any configured CIDR. Bare addresses without /n are normalised to host masks. With the env var blank — the default — both forwarded headers are ignored, so a hostile direct client can't lie its way into a different audit IP or HTTPS posture. Request::ip() now goes through the helper (so the audit pipeline and the R01-N06 throttle bucket key both fix themselves), and a new Request::isHttps() exposes the same decision to the rest of the app. SessionGuard::start() marks the session cookie Secure when either APP_BASE_URL is HTTPS or the live request is effectively HTTPS, so a TLS- terminated proxy hop no longer downgrades the cookie. public/index.php adds a one-shot HTTP→HTTPS redirect (308) when APP_BASE_URL is HTTPS and the request is provably HTTP — either there is no TRUSTED_PROXIES configured at all, or a trusted proxy explicitly reported X-Forwarded-Proto: http. We deliberately do NOT redirect when the proxy stays silent, to avoid an infinite-loop with TLS-terminating proxies that forgot to forward the scheme; /healthz is exempt outright. Operator docs: new .env.example block, new admin-manual §3.5 "Reverse proxy and HTTPS" (old §3.5 → §3.6, old §3.6 → §3.7) with an Nginx snippet showing the required proxy_set_header lines. New tests/Http/TrustedProxiesTest.php (14 cases) covers direct-client / single-hop / multi-hop / IPv6 / typo / port- stripping / X-Forwarded-Proto trust gating; new tests/Http/RequestTest.php (4 cases) wires the env into Request::ip() / isHttps(). Tests: 202 / 533 (was 184 / 502). Eighth fix from doc/REVIEW_01.md.
[x] R01-N08 — Idle session timeout + CSRF rotation on login (bc745cd). A signed-in session previously stayed valid until the browser closed or the 8h gc_maxlifetime GC tick fired — a stolen session cookie paired with the same-session CSRF token was good for hours of attacker-driven mutations. SessionGuard now drives a 30-minute idle window (IDLE_TIMEOUT_SECONDS = 1800) inside start(): any request that lands more than 1800 s after the previous one drops user_id / login_at / last_active / csrf_token and session_regenerate_id(true) rotates the id — the next gate sees an anonymous session and redirects to /auth/login. Foreign session keys (the OIDC library's state/nonce/PKCE) are preserved so an in-flight bounce to Entra is not killed by a stale idle clock from a previous logged-in session. login() now stamps last_active = time() and unset()s csrf_token, so a token a pre-login attacker may have captured from the public homepage form cannot be replayed against the now-authenticated session (the next csrfToken() call mints a fresh bin2hex(random_bytes(32))). The boundary is >= so a session exactly 1800 s idle is expired. Two pure- static helpers carry the policy so it is testable without spinning up PHP's session machinery: isIdleExpired(int $lastActive, int $now): bool and expireIdleSession(array &$session, int $now): bool. New tests/Auth/SessionGuardTest.php (9 cases) pins the constant, the boundary semantics (0 / 1 s / 1799 s / 1800 s / 2 h), the anonymous-session no-op, the last_active-missing seed-on- first-hit branch, the auth-key drop on idle with foreign-key survival, the exact-boundary expiry, the just-fresh case, and the non-int user_id defence that mirrors currentUserId()'s contract. Tests: 211 / 562 (was 202 / 533). Ninth fix from doc/REVIEW_01.md.
[x] R01-N10 — Bind sprint_id with placeholder in MAX(sort_order) lookups (c1dbfc1). Three repo-level read paths previously interpolated an integer route parameter directly into SQL ('... WHERE sprint_id = ' . $sprintId). The route layer int-casts the value, so this was not exploitable today, but the contract was implicit — one careless future caller passing an unvalidated string would have made the repo accept it. Switched TaskRepository::create, TaskRepository::moveToSprint, and SprintWorkerRepository::add to prepared statements with ? placeholders. Mechanical refactor, behaviour identical, no new tests (existing tests/Cascade + tests/Controllers already exercise these paths). Tenth fix from doc/REVIEW_01.md.
[x] R01-N18 + R01-N16 + R01-N17 — OIDC email trust path, composer-audit helper, concurrent-tab note (a0b717e, ef9b9b8). N18 (a0b717e): the OIDC callback's email-resolution rule was $claims->email ?? $claims->preferred_username ?? ''. In some Entra tenant configurations preferred_username is not a verified email — it can be controlled by the end user — so an attacker could spoof their forensic identity in the audit log. The user identity itself is keyed by oid/sub (immutable) and was never at risk; only the displayed actor was. New App\Auth\OidcClaims::resolveEmail($claims, $oid) is the single decision point: drops the preferred_username fallback, honours claims.email only when email_verified !== false (Entra v2.0 work/school tokens don't emit the flag at all and ship a directory-controlled email — strict === true would have flipped every existing audit row to a fallback label), and falls back to the documented immutable label entra:<oid> when no trusted email is available. The oid-or-sub check moved up so a missing identifier rejects the callback BEFORE the resolver runs (the resolver assumes a non-empty oid for its fallback). New tests/Auth/OidcClaimsTest.php (10 cases) pins the matrix: verified-true / verified-absent / verified- false / preferred_username-rejected / blank-email / whitespace- trim / no-claims-at-all / non-bool-truthy email_verified / non-scalar email defence. N16 (ef9b9b8): PhpSpreadsheet has a long history of XML- related advisories; the ^3.4 caret range lets minor upgrades land on each docker compose build --no-cache, but operators may not rebuild promptly. New bin/audit.sh wraps composer audit --locked --no-interaction inside the runtime image so the audit reflects the exact dependency tree the live container ships (host PHP often lacks the right ext-* set). Honours SPRINT_PLANER_IMAGE for non-default tags; refuses cleanly when docker is missing or the image hasn't been built. Today's baseline: "No security vulnerability advisories found." admin- manual §5.5 grew a "Composer dependency cadence" block — recommended rhythm (rebuild after every git pull, monthly minimum), the auditor invocation, and a copy-pasteable weekly- cron snippet that pipes a non-zero exit to mail. No CI hook (this repo has no CI today and adding GitHub Actions would have introduced a new external dependency). N17 (doc-only, in ef9b9b8): accepted-by-design after reading vendor/jumbojett/openid-connect-php/src/ OpenIDConnectClient.php lines 1789-1866. The library uses fixed session keys (openid_connect_state, openid_connect_nonce, openid_connect_code_verifier); two parallel sign-in tabs clobber each other's state and the older tab gets bounced to /?auth_error=1 on the state-mismatch guard at line 322 — the correct OIDC behaviour. The "fix" (subclass + transient flow-id cookie, ~80 LoC + tests) trades real complexity for a one-line UX nit. Not worth it for a single-admin tool. admin-manual §5.6 documents the rule: complete one sign-in at a time. No code change for N17. Tests: 252 / 683 (was 242 / 673). Fourteenth, fifteenth, and doc-only fix from doc/REVIEW_01.md.
[x] R01-N13 + R01-N14 — Fatal-error safety net + XLSX session cap (d7dbfb5). Two findings, one commit (both touch public/index.php). N13: public/index.php calls ob_start() so a stray warning/notice can't send headers before Response::send() runs, but the flush is in the happy path — a fatal error mid-request could let PHP flush whatever was buffered to the response, without the security headers (CSP / HSTS / X-Frame / X-Content-Type-Options / Referrer-Policy) that the regular path applies. New App\Http\FatalErrorHandler registers BOTH set_exception_handler (catches uncaught throwables escaping Router::dispatch()) and register_shutdown_function (filtered on a fatal mask: E_ERROR | E_PARSE | E_CORE_ERROR | E_COMPILE_ERROR | E_USER_ERROR | E_RECOVERABLE_ERROR). On fire, every open output buffer is drained (so a half-rendered Twig template can't leak in front of the 500 page) and a minimal 500 — Server error HTML body is written with the same security headers a normal response carries. Production hides the throwable detail; non-production echoes class + message (HTML-escaped). To prevent the happy/error paths from drifting on CSP edits, public/index.php now sources its security headers from FatalErrorHandler::securityHeaders($isHttps) — single source of truth. The handler is registered TWICE: once with isHttps=false immediately after autoload (so a fatal during migrations or service wiring still produces a clean 500), and again with the resolved HTTPS flag right before dispatch(). New tests/Http/FatalErrorHandlerTest.php (7 cases) pins production-vs-dev detail, HSTS-on-HTTPS-only, headers-skipped-when-already-sent, drain-runs-once-and-first, and securityHeaders() parity. N14: The full parsed workbook (every cell value, status, weeks, workers, tasks for every sheet) was JSON-stashed in PHP's session file. Limit was 5 MB on the upload but parsed expansion is unbounded. New ImportController::MAX_SESSION_PAYLOAD_BYTES = 2 * 1024 * 1024 (2 MiB) bounds the JSON-encoded preview blob via the new pure-static encodedPayloadBytes(array): int helper; a payload past the cap is rejected with ?error=too_large_payload. Both pruning paths now record an IMPORT_PREVIEW_ABANDONED audit row (entity_type= import_token, entity_id=NULL, after_json carrying {file_name, sheet_count, payload_bytes, age_seconds, created_at} UTC ISO-8601). The pure-static abandonedAuditPayload(array, int): array builder is guarded against malformed entries (missing created_at → age 0; clock skew → age 0). ImportController constructor gained an AuditLogger dependency. views/sprints/import_upload.twig gained the new error message. Test count: 4 → 12 in tests/Controllers/ImportControllerTest.php. N09: accepted-by-design in the same review — SameSite=Lax stays. Strict would block the cross-site-initiated OIDC callback navigation from carrying the cookie, killing the library's state/PKCE check. Real CSRF defence is SessionGuard::verifyCsrf() (per-session token, hash_equals). Tests: 242 / 673 (was 227 / 590). Twelfth, thirteenth, and doc-only fix from doc/REVIEW_01.md.
[x] R01-N12 — Validate /audit date filters server-side (1b28469). The audit viewer concatenated T00:00:00Z / T23:59:59Z onto the raw from_date / to_date query inputs and bound the result against the ISO-8601 occurred_at column. Garbage in (e.g. 2024/01/01 instead of 2024-01-01) silently produced empty result sets — a UX bug that hides events from a casual auditor. AuditController::index now routes both inputs through a pure static validateDateFilters($from, $to) that strict-parses Y-m-d (DateTimeImmutable::createFromFormat + round-trip equality, same pattern as SprintController:: isIsoDate) and returns the validated value (empty when parse fails so the filter is dropped instead of poisoning the query) plus an errors map keyed by field name. The view echoes the user's raw input back into the date inputs so they can fix the typo, tints the offending input amber, and renders an inline "Use the format YYYY-MM-DD" notice + a banner. No session-flash plumbing — the form is GET-driven and the response IS the form, so errors travel with the rendered page. New tests/Controllers/AuditControllerTest.php (16 cases) pins the matrix: empty / valid / one-side-bad / both-bad, lenient rollovers caught by the round-trip check (2025-02-29, 2026-13-01, 2026-02-30, 2026-1-1), whitespace rejected, leap-year preserved verbatim, injection-shaped string rejected. Tests: 227 / 590 (was 211 / 562). Eleventh fix from doc/REVIEW_01.md.
[x] R01-N21 — Twig auto-escape pinned by tests Twig autoescape: 'html' is the only barrier between user-supplied strings (sprint name, task title, audit JSON, …) and stored XSS in the views that render them — a single careless |raw filter or {% autoescape false %} block opens the door. The fix is two tests in tests/Http/TwigAutoescapeTest.php that catch both directions: Behaviour pin: renders a known XSS payload through a synthetic Twig template using the same View env the controllers wire up, and asserts the output is HTML-escaped (no <script> survives; <script> is present). A second case pins attribute-context double-quote escaping so a future flip from 'html' to false fails loudly. Static guard: walks every .twig file under views/ and fails if any line carries |\s*(raw|safe) or {%\s*autoescape. Verified end-to-end by temporarily appending {{ "x"|raw }} to home.twig — the test reports the exact path:line of the offence with a remediation hint. No production code changed; the guards live entirely in the test suite. Tests: 305 / 814 (was 302 / 791). Eighteenth fix from doc/REVIEW_01.md.
[x] R01-N20 — Response::redirect rejects non-path locations All callers in the app pass a path-only string (/foo?error=…, /sprints/{id}, etc.) — but Response::redirect accepted any string at the type system level, so a future caller passing ?next= from query input would happily emit an off-origin Location: header. Response::redirect now refuses anything that is not a single / followed by something other than /, and also refuses CR/LF/NUL (header injection). Protocol-relative URLs (//evil.example.com/x) — the dangerous shape that looks like a path — are caught by the second test. The HTTP→HTTPS canonical redirect in public/index.php (the one place that genuinely needs to leave the origin) moves to the new Response::external($url, $status) helper, which restricts the scheme to http/https and requires a non-empty host (so a stray javascript: or data: payload cannot wind up in the header). Both helpers throw \InvalidArgumentException on misuse — loud in dev/test, but harmless under the existing FatalErrorHandler safety net in production. Pure-static isPathOnly() and isExternalUrl() back the contract for direct unit tests. New tests/Http/ResponseTest.php (15 cases, 36 assertions) pins the accept and reject matrices for both helpers and the pure helpers separately. Tests: 302 / 791 (was 266 / 721). Seventeenth fix from doc/REVIEW_01.md.
[x] R01-N19 — CSP report-uri /csp-report + audit endpoint The strict CSP in App\Http\FatalErrorHandler::CSP was silent — operators wouldn't notice a future view inadvertently introducing an inline handler or a new external host until users complained. The CSP now carries report-uri /csp-report, and the new public POST /csp-report route (App\Controllers\CspReportController) writes one audit row per browser-fired report. Anonymous: no session, no CSRF — this is a browser mechanism, not a user action; a forged report only costs one audit row, bounded by the 16 KiB body cap (MAX_BODY_BYTES). Bodies that fail to decode as a JSON object, or that exceed the cap, get a 204 with no row written so the endpoint reveals nothing. The legacy {"csp-report": {...}} envelope is unwrapped before the inner object lands in after_json. We are not adding report-to (CSP Level 3) until the legacy form proves insufficient — report-to requires a Reporting-Endpoints response header on every page and a second JSON parser for the application/reports+json shape, neither cheap. The audit listing surfaces the new csp_violation entity-type filter automatically (the /audit view's filter dropdown is wired through AuditRepository::distinctEntityTypes()). The FatalErrorHandlerTest CSP-line assertion grew a report-uri check to fence drift on edits to either the directive or the route. New tests/Controllers/CspReportControllerTest.php (10 cases). Tests: 266 / 721 (was 252 / 683). Sixteenth fix from doc/REVIEW_01.md.
[x] New sprint form: drop weeks input + task list row hover (3728106). The /sprints/new form no longer collects an n_weeks value — the week count is derived from start_date / end_date exactly as Phase 21's PATCH /sprints/{id} does (floor((end−start)/7) + 1, capped at 26; above that the form now redirects with ?error=dates_too_long). SprintController:: create drops n_weeks_invalid / n_weeks_range, gains dates_too_long, and calls the existing static weeksBetween() helper to seed materializeWeeks. The legacy POST /sprints/{id}/weeks JSON endpoint still accepts n_weeks for back-compat (UI doesn't call it). Task-table <tr> rows in views/sprints/_task_list.twig (used by both show and present) gain hover:bg-slate-50 dark:hover:bg-slate-700 so the row tints under the cursor the same way the sprints table on / does — also mirrored on JS-built rows in sprint-planner.js::buildTaskRow.

Upcoming

Nothing scheduled.

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) 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

cp .env.example .env
# Fill Entra vars, OR set LOCAL_ADMIN_EMAIL + LOCAL_ADMIN_PASSWORD_HASH
# (see README's Quick setup for the password_hash() one-liner)
docker compose up --build
# open http://localhost:8080

Rebuild when the Dockerfile / composer manifest / Tailwind sources change:

docker compose build --no-cache && docker compose up

For local CSS dev without Docker:

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 from the Docker entrypoint — bin/docker-entrypoint.sh → php bin/migrate.php — before Apache starts; the request path only checks and 503s if pending, never auto-migrates).

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 (252 tests, 683 assertions)

The Phase 20 parser tests need ext-dom, ext-zip, ext-xmlreader, ext-simplexml, and ext-gd (PhpSpreadsheet's hard requires); on hosts that don't have all of them the parser tests auto-skip via extension_loaded() in setUp(). Run inside the Docker image when the host PHP is thin:

docker compose build
docker run --rm -v "$(pwd):/app" -w /app sprint_planer_web-app:latest \
    sh -c "git config --global --add safe.directory /app \
        && composer install --no-interaction --no-progress \
        && vendor/bin/phpunit --colors=never"

12. How to resume in a fresh Claude session

Tell Claude:

Working on /Users/achiappa/Development/claude_code_private/sprint_planer_web. Read SPEC.md, the git log, and ACCEPTANCE.md. Phases 1–22 are shipped (see §9; Phase 17's slider popover was removed — typed entry is now the only edit path on number inputs; Phase 18 added per-cell task-status colours + filter + a new /settings page gated by a global flag that's off by default; Phase 19 swapped the stack to Twig 3 + Tailwind 3 + Alpine CSP + htmx + SortableJS and removed jQuery + jQuery UI completely; Phase 20 added a two-step XLSX import wizard at /sprints/import powered by PhpSpreadsheet, with a colour-coded cell → status mapping; Phase 21 derives the sprint week count from start/end dates; Phase 22 replaced the task drag handle with a per-row hamburger menu — Edit details / Move across sprints / Copy across sprints (with bidirectional linked- task chips) / click-pickup reorder / Delete; tasks gained description / url / linked_task_id columns). Nothing is currently scheduled. Outstanding items are in §10 (mostly a human-run acceptance walkthrough in the running container). If I ask you to plan or work a new phase, follow the maintenance rule in §14 — commit code, then commit a SPEC.md update separately that marks the new work 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 writing)

ef9b9b8 Fix R01-N16, doc R01-N17: composer-audit helper + admin-manual cadence note
a0b717e Fix R01-N18: trust OIDC email only when issuer hasn't marked it unverified
50f9bd2 Docs: mark R01-N09 / R01-N13 / R01-N14 fixed, refresh SPEC §3 / §9 / §11 / §13
d7dbfb5 Fix R01-N13 + R01-N14: fatal-error safety net + XLSX session cap
868fe6c Docs: mark R01-N12 fixed, refresh SPEC §9 / §13
1b28469 Fix R01-N12: validate audit date filters server-side
d6b163d Docs: mark R01-N10 fixed, refresh SPEC §9 / §13
c1dbfc1 Fix R01-N10: bind sprint_id with placeholder in MAX(sort_order) lookups
a8ed6af Docs: mark R01-N08 fixed, refresh SPEC §9 / §11 / §13
bc745cd Fix R01-N08: idle session timeout + CSRF rotation on login
a2e77ea Fix R01-N05 + R01-N07: trusted-proxy aware HTTPS + client IP
f565c86 Fix R01-N03: explicit env-bootstrap for the first OIDC admin
2b8f167 Docs: mark R01-N06 fixed, refresh SPEC §3 / §4 / §7 / §9 / §11 / §13
e295432 Fix R01-N06: throttle local-admin login by (ip, email)
851f8cf Docs: mark R01-N11 fixed, refresh SPEC §9 / §13
4ae1817 Fix R01-N11: whitelist column in AuditRepository::distinctColumn
270c0c1 Docs: mark R01-N15 fixed, refresh SPEC §9 / §13
d16bff4 Fix R01-N15: add noreferrer to external task URL link
48a351c Docs: mark R01-N01 fixed, refresh SPEC §9 / §11 / §13
857df15 Fix R01-N01: hash-only LOCAL_ADMIN_PASSWORD_HASH (no plaintext fallback)
f075e12 Docs: mark R01-N04 fixed, refresh SPEC §9 / §13
296883c Fix R01-N04: drop unused SESSION_SECRET from env template + docs
18389bb Docs: mark R01-N02 / R01-N31 fixed, refresh SPEC §9 / §11 / §13
7fd849b Fix R01-N02 / R01-N31: gate runtime panel on home page to admins
912ef9b doc/REVIEW_01.md: initial security + fishy-pattern audit
756650a SPEC.md: note new-sprint form weeks-drop + task list row hover
3728106 New sprint form: drop weeks input + task list row hover
fe78f45 Header: move Import link into the admin dropdown menu
8e8b8fd Sprint settings: secured Delete sprint action
be91620 SPEC.md: mark Phase 22 shipped (per-task hamburger menu)
c2dad80 Phase 22: per-task hamburger menu — move/copy/edit/reorder
e2f19d6 Phase 21: derive sprint week count from start/end dates
62bb8b2 SPEC.md: mark Phase 20 shipped (XLSX import wizard)
8876239 Phase 20: XLSX import wizard (phpspreadsheet + colour→status)
2813019 Sprint view: tabs (Arbeitstage / Tasks) + smart Close on present
10ea4b8 Cell popover: replace per-cell status select with slider + status pills
1864835 Fix: filter dropdown close — grace timer for transit gap + close on Clear
9b72c41 Fix: filter dropdowns no longer cropped + close on mouse-leave
53891b2 SPEC.md: note task-table polish hotfix in §9 + §13
f204611 Fix: task table header alignment + status dropdown visibility + per-input tint
55f9726 Merge branch 'phase-19-stack-shift'
64d2782 Track composer.lock — fixes stale-cache Docker builds after Phase 19
c9e5b26 Merge pull request 'Phase 19...' (#1) from phase-19-stack-shift into main
9dd1340 SPEC.md: mark Phase 19 shipped (stack-shift to Twig+Alpine+htmx+Sortable)
75e96e2 Phase 19: Twig 3 + Tailwind 3 + Alpine CSP + htmx + SortableJS, jQuery removed
b3e5ec8 SPEC.md: note Phase 18 cell-markup hotfix
3e115f5 Fix: Phase 18 cell markup — drop span wrapper, color goes on <td>
205876a SPEC.md: mark Phase 18 shipped (task-status colours + filter)
9cb7669 Phase 18: per-cell task-status colours + filter + global toggle
da726bd SPEC.md: note number-stepper popover removal
e551705 Remove number-stepper slider popover
c5eef6a Docs: rename HANDOFF.md to SPEC.md, add admin manual, refresh README
fd2f0df changed docker compose port
761c4dd HANDOFF.md: note stepper close via direct listeners on Phase 17
e93df6b Fix: stepper close via direct element listeners (not doc delegation)
ac6ae73 HANDOFF.md: note number-stepper rebuild on Phase 17
ff807c2 Rewrite: number-stepper popover from scratch
729617d HANDOFF.md: note stepper close-fix (pointer-drift + capture-click)
8d79f96 Fix: stepper popover closes on mouse-drift + outside-click (belt-and-braces)
9de4bf2 HANDOFF.md: note stepper blur-close fix on Phase 17
f189ef7 Fix: stepper popover now closes when the bound input loses focus
27eea76 HANDOFF.md: note slider-only stepper rewrite on Phase 17
15b2d24 Stepper popover: slider-only, click-to-open, close on leave-popup
515f9ec HANDOFF.md: note stepper positioning + bounds-parsing hotfix
832b256 Fix: stepper popover broken on task-assignment cells + not centred
14a41b1 HANDOFF.md: note stepper hover + vertical-slider UX tweak on Phase 17
c07af1c Stepper popover: hover-to-open + vertical range slider
735aa4f HANDOFF.md: mark Phase 17 shipped
b457896 Phase 17: hide native number spinners + custom 0.5-step stepper popover
712bcc5 HANDOFF.md: add Phase 17 plan (number-stepper popover + hide native spinners)
0d738b2 HANDOFF.md: mark Phase 16 shipped
94b2841 Phase 16: dark-mode toggle + light-mode contrast cleanup
0d7124a HANDOFF.md: add Phase 16 plan (dark-mode toggle + light-mode contrast)
d4738d7 HANDOFF.md: note buildTaskRow owner-dropdown hotfix on Phase 10
7c298d3 Fix: buildTaskRow owner dropdown was empty until a page refresh
c70e442 HANDOFF.md: mark Phase 15 shipped
d1dda4f Phase 15: big-screen (beamer) task viewer at /sprints/{id}/present
48c56b7 HANDOFF.md: add Phase 15 plan (big-screen task viewer)
d59120c HANDOFF.md: mark Phase 14 shipped
101cc57 Phase 14: hamburger menu groups admin utilities + Sign out
15695ab HANDOFF.md: add Phase 14 plan (hamburger menu)
bfb93fc gitignore: exclude .claude/ (Claude Code agent runtime scratch)
a30cb0b HANDOFF.md: note buildTaskRow data-col hotfix on Phase 13
23ab365 Fix: stamp data-col on JS-built task row cells
d0fdf53 HANDOFF.md: mark Phase 13 shipped
b027c5d Phase 13: Focus filter + Reset in the task list
e23cfac HANDOFF.md: add Phase 13 plan (Focus filter + reset)
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:

Commit the code first. A commit message that captures what changed, why, and how it was verified is the canonical record.
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.
Update §13. Append the new SHA to the git history block.
If new files / directories were added or moved, refresh §3.
If the test count changed, update §11's expected count.
Commit the SPEC.md update as its own commit. Keeping it separate means a git revert of the code revert leaves the doc honest by reverting alongside.
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.

SPEC.md 109 KB Түүх Анхны өгөгдөл