feat(admin): activity panel on admin dashboard — system-wide weekly contribution counts #335

New Issue

marcel · 2026-04-26T14:14:50+02:00

marcel commented

2026-04-26 14:14:50 +02:00

Context

#324 ships the admin dashboard at /admin with three panels: Invites, OCR, and a placeholder where Activity was originally planned. The Activity panel was descoped from #324 to keep that issue focused on the core dashboard structure and the OCR + Invites data pipelines.

The Activity panel shows system-wide contribution health at a glance — how much transcription, annotation, upload, and conversation activity happened across all users in the last 7 days. This is the answer to the admin question: "Is the archive being actively worked on, or has it gone quiet?"

No new data pipelines are needed. The audit log already records all four event types. AuditLogQueryRepository.getPulseStats() already counts three of the four; the fourth (COMMENT_ADDED) is one line of SQL.

Non-goals

No per-user breakdown (that belongs on a contributor leaderboard, not the health dashboard).
No date range selector — last 7 days is fixed in v1.
No trend arrows / sparklines — counts only.
No click-through to individual events — counts are informational, not navigable.

Proposed panel

┌─────────────────────────────────────────┐
│  Aktivität  ·  letzte 7 Tage            │
│  ───────────────────────────────────    │
│   12         47         8          3    │
│  Uploads  Kommentare  Seiten  Annotatio.│
└─────────────────────────────────────────┘

Four stat cards in a 2×2 grid on mobile, 1×4 row on desktop. Numbers prominent (font-serif text-3xl font-bold text-ink), labels small (font-sans text-xs text-ink-3 uppercase tracking-widest). Reuse the OcrStatCards.svelte visual pattern — it already exists in admin/ocr/ for exactly this purpose.

Implementation plan

Backend

Step 1 — Add getSystemActivityCounts() to AuditLogQueryRepository.

Adapt the existing getPulseStats() native query (lines 114–129). Changes:

Add COMMENT_ADDED to the counted kinds
Remove the yourPages per-user column (no userId parameter)
Remove the annotated split; count ANNOTATION_CREATED as a flat total

@Query(value = """
    SELECT
        COUNT(*) FILTER (WHERE a.kind = 'FILE_UPLOADED')      AS uploaded,
        COUNT(*) FILTER (WHERE a.kind = 'COMMENT_ADDED')      AS comments,
        COUNT(DISTINCT a.payload->>'blockId')
            FILTER (WHERE a.kind = 'TEXT_SAVED')               AS transcribed,
        COUNT(*) FILTER (WHERE a.kind = 'ANNOTATION_CREATED') AS annotated
    FROM audit_log a
    WHERE a.happened_at >= :weekStart
      AND a.kind IN ('FILE_UPLOADED','COMMENT_ADDED','TEXT_SAVED','ANNOTATION_CREATED')
    """, nativeQuery = true)
SystemActivityRow getSystemActivityCounts(@Param("weekStart") OffsetDateTime weekStart);

New projection interface SystemActivityRow (same package as PulseStatsRow):

public interface SystemActivityRow {
    int getUploaded();
    int getComments();
    int getTranscribed();
    int getAnnotated();
}

Step 2 — Extend AdminDashboardDTO with an activity field.

// AdminDashboardDTO — add:
@Schema(requiredMode = Schema.RequiredMode.REQUIRED)
private ActivityDTO activity;

public record ActivityDTO(
    @Schema(requiredMode = Schema.RequiredMode.REQUIRED) int uploadsThisWeek,
    @Schema(requiredMode = Schema.RequiredMode.REQUIRED) int commentsThisWeek,
    @Schema(requiredMode = Schema.RequiredMode.REQUIRED) int textSavedThisWeek,
    @Schema(requiredMode = Schema.RequiredMode.REQUIRED) int annotationsThisWeek
) {}

Step 3 — Wire into AdminDashboardService.

OffsetDateTime weekStart = OffsetDateTime.now().minusDays(7);
SystemActivityRow row = auditLogQueryRepository.getSystemActivityCounts(weekStart);
ActivityDTO activity = new ActivityDTO(
    row.getUploaded(),
    row.getComments(),
    row.getTranscribed(),
    row.getAnnotated()
);

Frontend

Step 4 — Create ActivityPanel.svelte in frontend/src/lib/components/admin/.

Props: activity: AdminDashboardDTO['activity']

Renders four stat cards using the OcrStatCards.svelte visual pattern. Labels come from Paraglide keys (see i18n section).

Step 5 — Add ActivityPanel to +page.svelte.

Replace the placeholder / empty section with <ActivityPanel activity={data.dashboard.activity} />. No layout changes — the card grid already exists from #324.

Step 6 — Regenerate API types (npm run generate:api).

i18n

4–5 new Paraglide keys:

Key	de	en	es
`admin_dashboard_activity_title`	Aktivität	Activity	Actividad
`admin_dashboard_activity_period`	letzte 7 Tage	last 7 days	últimos 7 días
`admin_dashboard_activity_uploads`	Uploads	Uploads	Subidas
`admin_dashboard_activity_comments`	Kommentare	Comments	Comentarios
`admin_dashboard_activity_transcribed`	Seiten transkribiert	Pages transcribed	Páginas transcritas
`admin_dashboard_activity_annotated`	Annotationen	Annotations	Anotaciones

Tests

Repository integration test (AuditLogQueryRepositoryTest, Testcontainers): seed known rows (e.g. 2 FILE_UPLOADED, 3 COMMENT_ADDED, 5 TEXT_SAVED, 1 ANNOTATION_CREATED) in the last 7 days + 1 row older than 7 days — assert each count matches exactly and the old row is excluded.
Service unit test (AdminDashboardServiceTest): mock getSystemActivityCounts() → assert ActivityDTO fields are mapped correctly.
Controller test (AdminDashboardControllerTest): GET /api/admin/dashboard — assert activity.uploadsThisWeek is present in the response body (add to the existing shape assertion).
Frontend component test (ActivityPanel.spec.ts, Vitest Browser): render with { uploadsThisWeek: 12, commentsThisWeek: 47, textSavedThisWeek: 8, annotationsThisWeek: 3 } — assert all four numbers are visible; render with all zeros — assert zero-state renders without errors.

Acceptance criteria

GET /api/admin/dashboard response includes activity.uploadsThisWeek, commentsThisWeek, textSavedThisWeek, annotationsThisWeek
Counts reflect the last 7 days only (events older than 7 days are excluded)
ActivityPanel renders correctly for non-zero counts and for all-zero counts
i18n complete for de/en/es
All four panel labels are visible and keyboard-navigable on mobile (320px)

Critical files

backend/src/main/java/org/raddatz/familienarchiv/audit/SystemActivityRow.java           (new projection)
backend/src/main/java/org/raddatz/familienarchiv/audit/AuditLogQueryRepository.java      (new method)
backend/src/main/java/org/raddatz/familienarchiv/controller/AdminDashboardController.java (no change — DTO drives it)
backend/src/main/java/org/raddatz/familienarchiv/dto/AdminDashboardDTO.java              (add activity field)
backend/src/main/java/org/raddatz/familienarchiv/service/AdminDashboardService.java      (wire activity)
backend/src/test/…/audit/AuditLogQueryRepositoryTest.java                                (new integration test)
frontend/src/lib/components/admin/ActivityPanel.svelte                                   (new)
frontend/src/lib/components/admin/ActivityPanel.spec.ts                                  (new)
frontend/src/routes/admin/+page.svelte                                                   (add ActivityPanel)
frontend/src/lib/generated/api.ts                                                        (regen)
frontend/messages/{de,en,es}.json

#324 (admin dashboard) — this issue adds the activity panel descoped from that milestone. Depends on #324 being merged first.

## Context #324 ships the admin dashboard at `/admin` with three panels: Invites, OCR, and a placeholder where Activity was originally planned. The Activity panel was descoped from #324 to keep that issue focused on the core dashboard structure and the OCR + Invites data pipelines. The Activity panel shows system-wide contribution health at a glance — how much transcription, annotation, upload, and conversation activity happened across all users in the last 7 days. This is the answer to the admin question: "Is the archive being actively worked on, or has it gone quiet?" **No new data pipelines are needed.** The audit log already records all four event types. `AuditLogQueryRepository.getPulseStats()` already counts three of the four; the fourth (`COMMENT_ADDED`) is one line of SQL. ## Non-goals - No per-user breakdown (that belongs on a contributor leaderboard, not the health dashboard). - No date range selector — last 7 days is fixed in v1. - No trend arrows / sparklines — counts only. - No click-through to individual events — counts are informational, not navigable. ## Proposed panel ``` ┌─────────────────────────────────────────┐ │ Aktivität · letzte 7 Tage │ │ ─────────────────────────────────── │ │ 12 47 8 3 │ │ Uploads Kommentare Seiten Annotatio.│ └─────────────────────────────────────────┘ ``` Four stat cards in a 2×2 grid on mobile, 1×4 row on desktop. Numbers prominent (`font-serif text-3xl font-bold text-ink`), labels small (`font-sans text-xs text-ink-3 uppercase tracking-widest`). Reuse the `OcrStatCards.svelte` visual pattern — it already exists in `admin/ocr/` for exactly this purpose. ## Implementation plan ### Backend **Step 1 — Add `getSystemActivityCounts()` to `AuditLogQueryRepository`.** Adapt the existing `getPulseStats()` native query (lines 114–129). Changes: - Add `COMMENT_ADDED` to the counted kinds - Remove the `yourPages` per-user column (no `userId` parameter) - Remove the `annotated` split; count `ANNOTATION_CREATED` as a flat total ```java @Query(value = """ SELECT COUNT(*) FILTER (WHERE a.kind = 'FILE_UPLOADED') AS uploaded, COUNT(*) FILTER (WHERE a.kind = 'COMMENT_ADDED') AS comments, COUNT(DISTINCT a.payload->>'blockId') FILTER (WHERE a.kind = 'TEXT_SAVED') AS transcribed, COUNT(*) FILTER (WHERE a.kind = 'ANNOTATION_CREATED') AS annotated FROM audit_log a WHERE a.happened_at >= :weekStart AND a.kind IN ('FILE_UPLOADED','COMMENT_ADDED','TEXT_SAVED','ANNOTATION_CREATED') """, nativeQuery = true) SystemActivityRow getSystemActivityCounts(@Param("weekStart") OffsetDateTime weekStart); ``` New projection interface `SystemActivityRow` (same package as `PulseStatsRow`): ```java public interface SystemActivityRow { int getUploaded(); int getComments(); int getTranscribed(); int getAnnotated(); } ``` **Step 2 — Extend `AdminDashboardDTO` with an `activity` field.** ```java // AdminDashboardDTO — add: @Schema(requiredMode = Schema.RequiredMode.REQUIRED) private ActivityDTO activity; public record ActivityDTO( @Schema(requiredMode = Schema.RequiredMode.REQUIRED) int uploadsThisWeek, @Schema(requiredMode = Schema.RequiredMode.REQUIRED) int commentsThisWeek, @Schema(requiredMode = Schema.RequiredMode.REQUIRED) int textSavedThisWeek, @Schema(requiredMode = Schema.RequiredMode.REQUIRED) int annotationsThisWeek ) {} ``` **Step 3 — Wire into `AdminDashboardService`.** ```java OffsetDateTime weekStart = OffsetDateTime.now().minusDays(7); SystemActivityRow row = auditLogQueryRepository.getSystemActivityCounts(weekStart); ActivityDTO activity = new ActivityDTO( row.getUploaded(), row.getComments(), row.getTranscribed(), row.getAnnotated() ); ``` ### Frontend **Step 4 — Create `ActivityPanel.svelte`** in `frontend/src/lib/components/admin/`. Props: `activity: AdminDashboardDTO['activity']` Renders four stat cards using the `OcrStatCards.svelte` visual pattern. Labels come from Paraglide keys (see i18n section). **Step 5 — Add `ActivityPanel` to `+page.svelte`.** Replace the placeholder / empty section with `<ActivityPanel activity={data.dashboard.activity} />`. No layout changes — the card grid already exists from #324. **Step 6 — Regenerate API types** (`npm run generate:api`). ### i18n 4–5 new Paraglide keys: | Key | de | en | es | |---|---|---|---| | `admin_dashboard_activity_title` | Aktivität | Activity | Actividad | | `admin_dashboard_activity_period` | letzte 7 Tage | last 7 days | últimos 7 días | | `admin_dashboard_activity_uploads` | Uploads | Uploads | Subidas | | `admin_dashboard_activity_comments` | Kommentare | Comments | Comentarios | | `admin_dashboard_activity_transcribed` | Seiten transkribiert | Pages transcribed | Páginas transcritas | | `admin_dashboard_activity_annotated` | Annotationen | Annotations | Anotaciones | ## Tests - **Repository integration test** (`AuditLogQueryRepositoryTest`, Testcontainers): seed known rows (e.g. 2 `FILE_UPLOADED`, 3 `COMMENT_ADDED`, 5 `TEXT_SAVED`, 1 `ANNOTATION_CREATED`) in the last 7 days + 1 row older than 7 days — assert each count matches exactly and the old row is excluded. - **Service unit test** (`AdminDashboardServiceTest`): mock `getSystemActivityCounts()` → assert `ActivityDTO` fields are mapped correctly. - **Controller test** (`AdminDashboardControllerTest`): `GET /api/admin/dashboard` — assert `activity.uploadsThisWeek` is present in the response body (add to the existing shape assertion). - **Frontend component test** (`ActivityPanel.spec.ts`, Vitest Browser): render with `{ uploadsThisWeek: 12, commentsThisWeek: 47, textSavedThisWeek: 8, annotationsThisWeek: 3 }` — assert all four numbers are visible; render with all zeros — assert zero-state renders without errors. ## Acceptance criteria - [ ] `GET /api/admin/dashboard` response includes `activity.uploadsThisWeek`, `commentsThisWeek`, `textSavedThisWeek`, `annotationsThisWeek` - [ ] Counts reflect the last 7 days only (events older than 7 days are excluded) - [ ] `ActivityPanel` renders correctly for non-zero counts and for all-zero counts - [ ] i18n complete for de/en/es - [ ] All four panel labels are visible and keyboard-navigable on mobile (320px) ## Critical files ``` backend/src/main/java/org/raddatz/familienarchiv/audit/SystemActivityRow.java (new projection) backend/src/main/java/org/raddatz/familienarchiv/audit/AuditLogQueryRepository.java (new method) backend/src/main/java/org/raddatz/familienarchiv/controller/AdminDashboardController.java (no change — DTO drives it) backend/src/main/java/org/raddatz/familienarchiv/dto/AdminDashboardDTO.java (add activity field) backend/src/main/java/org/raddatz/familienarchiv/service/AdminDashboardService.java (wire activity) backend/src/test/…/audit/AuditLogQueryRepositoryTest.java (new integration test) frontend/src/lib/components/admin/ActivityPanel.svelte (new) frontend/src/lib/components/admin/ActivityPanel.spec.ts (new) frontend/src/routes/admin/+page.svelte (add ActivityPanel) frontend/src/lib/generated/api.ts (regen) frontend/messages/{de,en,es}.json ``` ## Related - #324 (admin dashboard) — this issue adds the activity panel descoped from that milestone. Depends on #324 being merged first.

marcel added the P3-later feature ui labels 2026-04-26 14:14:56 +02:00

marcel referenced this issue

2026-05-07 17:28:06 +02:00

fix(db): add indexes on documents.sender_id and document_comments.author_id #470

marcel referenced this issue

2026-05-07 17:48:01 +02:00

fix(db): add indexes on documents.sender_id and document_comments.author_id #470

marcel referenced this issue

2026-05-07 17:48:32 +02:00

fix(db): add indexes on documents.sender_id and document_comments.author_id #470

Sign in to join this conversation.