Load windowed Home data (24h + high-priority) with backend search and tag filtering #79

Summary

Home currently loads ALL tags, tasks, and notes on every mount, which will not scale as user data grows. Change the default Home load to a windowed payload (high-priority incomplete tasks + anything touched in the last 24h), keep loading all tags, and move tag filtering and text search to the backend. No pagination for now.

This design was fully grilled and settled with the product owner. All decisions below are final; do not re-open them without new information.

Decisions (settled via grilling session)

Default window (Q1, Q2, Q9, Q11)

• "Touched" = the existing last_update column (last_update >= now() - interval '24 hours'). There is no created_at column and none will be added.
• Write-path audit confirmed every task/note mutation already bumps last_update (tag link/unlink only happens inside patchTask/patchNote), so no write-path changes are needed.
• High-priority tasks are always loaded, but ONLY when highPriority = true AND completed = false. Completed HP tasks appear only via the window, search, or tag queries.
• Archived notes follow the same 24h window (archiving bumps last_update, so a just-archived note still appears in the archived section). Do NOT always-load all archived notes, and do NOT remove the archived section from Home.
• No mid-session eviction, polling, or TTL timers. Data refreshes on reload, navigation back to Home, mutation, or filter/search change. (Session expiry kicks inactive users well before 24h anyway.)

Query mode (Q3, Q4, Q5, Q6, Q7)

• One new aggregated endpoint: GET /rest/home/items
  • No params: returns the default window payload described above.
  • ?q=<text>: backend search replicating today's client-side semantics exactly: notes matched on title, description, url, AND tags; tasks matched on description, tags, AND urls; completed tasks and archived notes INCLUDED in results.
  • ?tag=<tag>: full-database (unbounded, accepted) query for items with that tag, including the untagged pseudo-tag (items with zero tags).
  • ?type=all|tasks|notes: maps to the dropdown's everything/onlyTasks/onlyNotes modes.
  • Params compose with AND semantics (?q=foo&tag=work = both).
  • Response shape: { "tasks": TaskResponse[], "notes": NoteResponse[] } using the existing DTOs unchanged.
• Client search UX: debounce ~300ms, min 1 char; clearing the search box re-fetches the default window.
• Sorting stays client-side exactly as today.

Supporting changes (Q8, Q10, Q12, Q13)

• Rewrite GET /rest/home/tasks/tags to query the tag tables directly (union of distinct tags + untagged pseudo-tag injection). It currently scans ALL tasks and notes server-side in Java, which would defeat the purpose. Must stay response-compatible: it is also consumed by TaskAdd and NoteAdd views.
• New Flyway migration adding indexes on task.last_update and note.last_update.
• After every mutation from Home (create/edit/complete/archive/restore/delete), refetch the CURRENT view (default window, or re-run the active search/tag query). Server is the source of truth; no optimistic local patching for list membership.
• localStorage filter persistence (FILTER_TEXT, FILTER_OPTION) stays as-is; on reload with a persisted filter, fire the backend query on mount instead of the default window load.
• Full-list GET /rest/tasks and GET /rest/notes stay untouched (only Home consumes them today; account deletion uses the service layer directly).

Technical notes (for the implementing agent)

Backend facts (verified against the code)

• Entities are plain Java records + NamedParameterJdbcTemplate, NOT JPA. Hand-written SQL lives in server/src/main/java/br/com/tasknoteapp/server/repository/TaskRepository.java and NoteRepository.java.
• Task record (entity/Task.java): id, userId, description, completed, lastUpdate (LocalDateTime), dueDate, dueDateNotify, dueDateNotifySent, highPriority (Boolean). Priority is a boolean, not an enum.
• Note record (entity/Note.java): id, userId, description, title, lastUpdate (LocalDateTime), shared, shareToken, archived.
• last_update is set manually via LocalDateTime.now() in: TaskService create (:144) and patch (:219); NoteService create (:140), patch (:211), share (:338), unshare (:381), archive (:490), restore (:533).
• TaskResponse.lastUpdate and NoteResponse.lastUpdate are pre-formatted time-ago strings (via TimeAgoUtil), NOT raw timestamps. The window filtering must happen in SQL against the last_update column, not against DTO fields.
• Unused search methods already exist and can be adapted, but their semantics do NOT match the required behavior and must be fixed: TaskRepository.findAllBySearchTerm (:200-222) forces completed = false and misses nothing else; NoteRepository.findAllBySearchTerm (:198-215) misses url and tags. Neither is wired to any controller.
• HomeService.getTopTasksTag (:60-73) currently builds the tag list by fetching all tasks+notes and extracting tags in Java, and injects the untagged pseudo-tag there. Replace with direct SQL over tags, task_tags, note_tags tables.
• Tag mutations live in TagRepository (updateTagForTask :90, updateTagForNote :111, deleteTagFromTask :133, deleteTagFromNote :155) and are only invoked from task/note create/patch flows; no standalone tag endpoints exist.
• Flyway convention: V<yyyyMMddHHmm>__description.sql in server/src/main/resources/db/migration/; latest is V202609171142__drop_tag_from_notes_table.sql. Never edit old migrations.
• Integration tests (*IntTest.java, Failsafe) use Testcontainers Postgres (jdbc:tc:postgresql:15.8-bookworm) with real Flyway migrations; Docker required. Run bash tools/check-backend.sh for the full gate (tests need -P tests profile, the script handles it).

Frontend facts (verified against the code)

• client/src/views/Home/index.tsx: useEffect at :491-497 calls loadTags(), loadAllTasks(), loadAllNotes(). Tag dropdown options built at :556-620 (everything/onlyTasks/onlyNotes/radio_<tag>/radio_untagged). Client-side filtering in applyFilter at :250-339; archived partition via partitionNotes at :136; archived section rendered at :880.
• client/src/context/FilterProvider.tsx: filterText + selectedOption persisted to localStorage as FILTER_TEXT/FILTER_OPTION. Not keyed per user (pre-existing quirk, out of scope).
• API layer: client/src/api-service/api.ts (api.getJSON(url) etc., Bearer token from localStorage); base URLs in client/src/api-service/apiConfig.ts (homeUrl, tasksUrl, notesUrl). No query-param helper exists; interpolate query strings into the URL.
• Frontend gate: bash tools/check-frontend.sh (npm ci, lint:fix, build, test:no-watch).

Implementation checklist

1. Flyway migration: indexes on task.last_update and note.last_update.
2. Backend: new GET /rest/home/items in HomeController + HomeService, with SQL for (a) default window, (b) search across the required fields including tag joins, (c) tag filter including untagged, (d) type param; params compose with AND.
3. Backend: rewrite getTopTasksTag SQL to hit tag tables directly; keep untagged injection; keep response shape for TaskAdd/NoteAdd compatibility.
4. Frontend: replace loadAllTasks/loadAllNotes on Home with the windowed fetch; wire search (debounced) and tag/type dropdown to /rest/home/items with params; clearing search re-fetches the window.
5. Frontend: refetch current view after every mutation originating from Home.
6. Frontend: on mount, if persisted filter exists, fire the corresponding backend query instead of the default window.
7. Tests: backend integration tests for the new endpoint (window boundary at 24h, HP-completed exclusion, untagged, archived inclusion, composed params); frontend tests for debounce/clear behavior and mutation refetch.

Explicitly out of scope

• Pagination.
• Per-user localStorage filter keying.
• Removing or changing the full-list /rest/tasks and /rest/notes endpoints.
• Adding created_at or touched_at columns.
• Any TTL/eviction of aged-out items during an open session.