# Team Activity Dashboard Redesign — Data Model and Page Architecture

## Purpose
Split the dashboard into two clear layers:

1. **Live metrics** that continue using the existing token-consumption computation path.
2. **DB-backed operational data** for events, project/activity state, and repo management sourced from Oracle tables and refreshed by n8n.

This avoids mixing fast-changing telemetry with slower workflow records and makes stale data obvious instead of hidden.

---

## Recommended page architecture

### 1) Live Metrics strip
Use this section only for values that are computed the same way today.

Include:
- token consumption total
- token delta since last refresh
- optional per-agent token breakdown if already available from the current computation
- refresh timestamp for the live metrics fetch

Do **not** migrate token math into Oracle just for the redesign if the existing method is working.

### 2) Operational summary
Back this from Oracle tables.

Include:
- project / workflow status
- active, blocked, waiting, completed counts
- recent activity count
- last workflow sync time
- stale-data warning if the DB snapshot is old

### 3) Activity feed
Back this from Oracle event tables, not from ad hoc UI computation.

Include:
- handoffs
- status changes
- repo refresh events
- repo active/inactive toggles
- sync failures / retries

### 4) Repo management section
Back this from Oracle `github_repos`.

Include per repo:
- repo full name
- active flag
- default branch
- webhook enabled flag
- `last_seen_at`
- `updated_at`
- refresh action
- active/inactive toggle

Add actions for:
- refresh this repo from GitHub
- refresh all repos from GitHub
- add newly discovered repos into Oracle
- disable a repo from workflow operation without deleting it

This section should be actionable and editable.

### 5) Repository detail drawer or modal
Optional but useful.

Include:
- latest GitHub sync result
- last error / note
- linked workflow counts
- current active work items
- manual refresh button

---

## Data ownership model

### A. Keep as live computations
These should come from the existing computation pipeline or API, not Oracle state tables:
- token totals
- token trends / deltas
- any already-working live usage aggregates
- real-time fetch timestamp from the live metrics source

Reason: these are telemetry-like values that can change frequently and already have a trusted computation path.

### B. Move to Oracle-backed records
These should be persisted and read from Oracle:
- project/activity events
- repo registry and activation state
- GitHub sync history
- workflow state for issues/PRs/tasks
- dashboard activity timeline items derived from sync events

### Timestamp ownership
- `last_seen_at`: updated by n8n when a repo is successfully observed from GitHub.
- `updated_at`: updated by n8n whenever the Oracle row changes.
- `last_synced_at`: updated by n8n when the last successful workflow reconciliation completes.

Reason: these are operational records, need auditability, and should survive page refreshes and n8n runs.

---

## Suggested Oracle tables

The redesign can reuse the existing workflow schema and add a small dashboard-oriented view layer.

### Existing / preferred core tables
- `github_repos`
  - repo registry and management
  - active/inactive control
  - `last_seen_at`
  - `updated_at`

- `github_work_items`
  - one row per issue/PR/work item
  - current state, waiting_on, latest commit/review/comment timestamps
  - `updated_at`
  - `last_synced_at`

- `github_work_item_events`
  - immutable event history
  - used for the activity feed

### Optional dashboard read model
If you want faster dashboard reads, add a denormalized snapshot table or materialized view such as:
- `dashboard_workflow_summary`
- `dashboard_repo_summary`
- `dashboard_activity_rollup`

These should be derived from the canonical tables, not become a second source of truth.

---

## Data flow recommendation

### n8n responsibilities
n8n should own the DB-backed sync flow:
1. Cron or webhook triggers run repo reconciliation.
2. n8n reads active repos from Oracle.
3. n8n refreshes GitHub metadata.
4. n8n upserts repo/work-item rows.
5. n8n inserts event rows for notable changes.
6. n8n updates `last_seen_at` when a repo is successfully observed.
7. n8n updates `updated_at` whenever a repo row is changed.

### Dashboard responsibilities
The page should only read:
- one live-metrics source for tokens
- Oracle-backed endpoints or snapshots for everything else

The UI should not recompute workflow state from raw GitHub payloads.

---

## Maintenance rules for timestamps

### `last_seen_at`
Maintain on the backend sync path only.

Update when:
- n8n successfully fetches the repo from GitHub
- a webhook or manual refresh confirms the repo is reachable / active

Use this as the “repo was last observed in the wild” timestamp.

### `updated_at`
Maintain whenever the row changes.

Update when:
- repo active/inactive is toggled
- default branch or webhook setting changes
- refresh metadata is written
- any state fields on work items change

Prefer a DB trigger or a single shared repository update function so the app and n8n do not drift.

### `last_synced_at`
If retained on `github_work_items`, use it for the last successful reconciliation time for that work item, separate from `updated_at`.

---

## Stale metrics cautions

- Do not present the dashboard as fully live if the DB-backed tables were last synced hours ago.
- Show separate freshness labels for:
  - live token metrics
  - Oracle-backed activity/repo data
- If `generated_at` or `last_synced_at` is old, show a stale banner or muted state.
- Treat token metrics and repository activity as different freshness domains; one can be current while the other is stale.
- Avoid using `updated_at` as a proxy for real GitHub freshness; it only means the row changed.

A good UI rule is:
- if live metrics are fresh but Oracle sync is stale, keep tokens visible and mark activity/repo data as delayed
- if live metrics are stale, clearly label the whole top strip as outdated even if Oracle data is current

---

## Implementation shape for the page

### Frontend
- `GET /api/dashboard/live-metrics` for existing token computation
- `GET /api/dashboard/summary` for Oracle-backed summary data
- `GET /api/dashboard/activity` for Oracle event feed
- `GET /api/dashboard/repos` for repo management state
- `POST /api/dashboard/repos/:id/refresh`
- `PATCH /api/dashboard/repos/:id`

### n8n
- Cron/webhook workflow to keep Oracle in sync
- action workflow for repo refresh and state toggles if you want orchestration outside the UI

### Best split
Keep the dashboard page thin:
- live metrics = read-only, computed elsewhere
- activity/repo management = Oracle-backed, auditable, and editable

That gives you a clean cut between telemetry and workflow state.