Investigation status mismatch between list and detail views

[bordumb]

Summary

The investigation list page shows a different status than the detail page for the same investigation. For example, an investigation may show "active" in the list but "completed" in the detail view.

Root Cause

The list and detail pages fetch status from different sources:

View	Source	Behavior
List (/investigations)	Database query	Reads from investigations table with fallback: COALESCE(outcome->>'status', status)
Detail (/investigations/:id)	Temporal API	Queries workflow directly for live status via temporal_client.get_status()

The database investigations table stores the initial status at creation time but is never updated when the Temporal workflow completes, fails, or is cancelled.

Reproduction

1. Start an investigation
2. Wait for it to complete (or view a completed one)
3. Compare status on list page vs detail page
4. List shows "active", detail shows "completed"

Proposed Solutions

Option A: Background sync job (Recommended)

Add a periodic background task that:

• Queries Temporal for all active workflows
• Updates database status for completed/failed/cancelled workflows
• Runs every 30-60 seconds

Pros: Non-blocking, simple
Cons: Up to 60s delay in list reflecting true status

Option B: Temporal activity on completion

Add a database update activity to the investigation workflow that runs on completion:

@activity.defn
async def update_investigation_status(investigation_id: str, status: str):
    await db.execute(
        "UPDATE investigations SET status = $1 WHERE id = $2",
        status, investigation_id
    )

Pros: Immediate consistency
Cons: Adds coupling between workflow and database

Option C: Query Temporal in list endpoint

Fetch status from Temporal for each investigation in the list.

Pros: Always accurate
Cons: N+1 queries, slow list loading, expensive

Recommendation

Option B is the cleanest solution - update the database when the workflow reaches a terminal state. This keeps the source of truth (Temporal) in sync with the read model (database) without polling overhead.

Related Files

• python-packages/dataing/src/dataing/entrypoints/api/routes/investigations.py (list_investigations, lines 175-218)
• python-packages/dataing/src/dataing/temporal/workflows/investigation.py