bga list: unfiltered output groups by status instead of sorting by created_at

[scoropeza]

Problem

bga list (no filters) shows tasks grouped by status, then sorted by timestamp within each group. Example:

FAILED      2026-04-22T04:09:02Z  [old]
FAILED      2026-04-22T04:05:07Z  [old]
...
COMPLETED   2026-04-29T00:20:07Z  [newest task]
...
CANCELLED   2026-04-28T23:49:53Z

The newest task is buried behind older FAILED tasks because of lexicographic ordering across the composite sort key: FAILED > COMPLETED > CANCELLED > ... > SUBMITTED.

Root cause

cdk/src/handlers/list-tasks.ts queries the UserStatusIndex GSI with:

• KeyCondition: user_id = :uid (partition-only; no sort-key condition)
• ScanIndexForward: false

The GSI sort key is status_created_at = {status}#{created_at}, so DynamoDB sorts lexicographically across the full composite. Without a status-prefix begins_with() condition, older rows with higher-lexicographic status values rank above newer rows with lower-lexicographic status values.

Status-filtered queries are separately broken

bga list --status COMPLETED returns "No tasks found." even when many COMPLETED tasks exist. The handler puts status in a FilterExpression (post-query filter) rather than a KeyCondition begins_with(status_created_at, "COMPLETED#"). With the default --limit 20, DDB scans the first 20 GSI rows then filters — if those 20 are not COMPLETED, the response is empty despite more matching rows existing further back in the index.

Options

1. Add a second GSI UserCreatedAtIndex (PK=user_id, SK=created_at). Best UX: bga list is newest-first regardless of status. Status filtering still uses UserStatusIndex via begins_with(status_created_at, "<STATUS>#"). Adds one GSI.
2. Rework list-tasks.ts to use begins_with key-condition when a status filter is present, fall back to multi-status fan-out-and-merge when not. No schema change, but the unfiltered list still needs work.
3. Base-table Scan with user filter. Don't do this at scale.

Recommendation

Option 1. The existing GSI is fine for "my RUNNING tasks" / "my FAILED tasks" queries; add a parallel GSI for the default newest-first use case.

Context

The data layer is now consistent. Prior to the fix(agent): maintain status_created_at on RUNNING/terminal transitions commit, the Python agent-side writers (write_running, write_terminal) did not update status_created_at on transitions, so the GSI sort key lied about the current status. That is fixed — the GSI now returns correct results when queried with a status prefix. The CLI/handler just doesn't yet use that query pattern.

Reproduction

On a deployed dev stack with at least one COMPLETED task newer than the oldest FAILED task:

bga list --limit 10       # newest COMPLETED task is NOT at the top
bga list --status COMPLETED --limit 5   # "No tasks found." despite matching rows existing