---
id: T01
parent: S02
milestone: M015
provides: []
requires: []
affects: []
key_files: ["backend/schemas.py", "backend/routers/creators.py"]
key_decisions: ["Used correlated scalar subquery (MAX(created_at)) to preserve existing query structure"]
patterns_established: []
drill_down_paths: []
observability_surfaces: []
duration: ""
verification_result: "Ran curl against live API on ub01:8096 — last_technique_at appears on all 6 creators, with correct datetime values for 5 creators with techniques and null for Caracal Project (0 techniques). Pagination and sort params verified working."
completed_at: 2026-04-03T04:16:43.842Z
blocker_discovered: false
---

# T01: Added last_technique_at correlated subquery to creators API endpoint, returning MAX(technique_pages.created_at) per creator

> Added last_technique_at correlated subquery to creators API endpoint, returning MAX(technique_pages.created_at) per creator

## What Happened
---
id: T01
parent: S02
milestone: M015
key_files:
  - backend/schemas.py
  - backend/routers/creators.py
key_decisions:
  - Used correlated scalar subquery (MAX(created_at)) to preserve existing query structure
duration: ""
verification_result: passed
completed_at: 2026-04-03T04:16:43.843Z
blocker_discovered: false
---

# T01: Added last_technique_at correlated subquery to creators API endpoint, returning MAX(technique_pages.created_at) per creator

**Added last_technique_at correlated subquery to creators API endpoint, returning MAX(technique_pages.created_at) per creator**

## What Happened

Added `last_technique_at: datetime | None = None` to CreatorBrowseItem schema and a correlated scalar subquery using func.max(TechniquePage.created_at) to the list_creators endpoint. The value flows through the row unpacking loop and is passed to the CreatorBrowseItem constructor. Deployed to ub01 and verified all three must-haves: field present on every item, non-null for creators with techniques, null for creators with zero techniques.

## Verification

Ran curl against live API on ub01:8096 — last_technique_at appears on all 6 creators, with correct datetime values for 5 creators with techniques and null for Caracal Project (0 techniques). Pagination and sort params verified working.

## Verification Evidence

| # | Command | Exit Code | Verdict | Duration |
|---|---------|-----------|---------|----------|
| 1 | `ssh ub01 'curl -s http://localhost:8096/api/v1/creators?limit=5' | python3 -m json.tool | grep last_technique_at` | 0 | ✅ pass | 2000ms |
| 2 | `ssh ub01 'curl -s http://localhost:8096/api/v1/creators?limit=6&sort=alpha' | python3 -c ... (non-null/null check)` | 0 | ✅ pass | 2000ms |
| 3 | `ssh ub01 'curl -s http://localhost:8096/api/v1/creators?limit=2&offset=2&sort=views' | python3 ... (pagination)` | 0 | ✅ pass | 2000ms |


## Deviations

First commit missed the row unpacking edit due to an edit tool text match issue. Fixed with a second commit.

## Known Issues

None.

## Files Created/Modified

- `backend/schemas.py`
- `backend/routers/creators.py`


## Deviations
First commit missed the row unpacking edit due to an edit tool text match issue. Fixed with a second commit.

## Known Issues
None.
