Recent activity filters on created_at instead of updated_at

[groksrc]

Bug Description

recent-activity can return misleadingly empty or stale results because the date filter is applied to search_index.created_at instead of search_index.updated_at.

This means notes/entities that were created before the requested timeframe but edited/updated inside the timeframe are excluded from recent activity, even though they are the most important results for a “what changed recently?” query.

Steps to Reproduce

1. Create an entity/note older than the requested recent-activity timeframe.
2. Update that existing entity/note within the timeframe.
3. Query recent activity, for example:

bm tool recent-activity --project main --timeframe 14d

or through the v2 memory/recent activity endpoint that uses the search repository date filter.

Expected Behavior

Recent activity should include entities/notes whose updated_at falls within the requested timeframe, even when their created_at is older.

Unranked recent-activity results should be ordered by recency, e.g. newest updated_at first.

Actual Behavior

The search repository filters after_date against search_index.created_at, so updated older notes are omitted.

In unranked recent-activity calls, ordering is also not recency-first; it sorts by a constant/null rank and id before updated recency is considered.

Suspected Root Cause

Both SQLite and Postgres search repositories use created_at for after_date filtering where recent-activity semantics require updated_at:

• src/basic_memory/repository/sqlite_search_repository.py
• src/basic_memory/repository/postgres_search_repository.py

The unranked ordering should prefer:

updated_at DESC, id ASC

rather than rank/id ahead of recency.

Suggested Fix

• Change after_date filters from created_at >= after_date to updated_at >= after_date in both SQLite and Postgres search repositories.
• For unranked/recent-activity search, order by updated_at DESC, id ASC.
• Add regression coverage that verifies:
  • an old-created but recently-updated entity is included,
  • a stale entity outside the timeframe is excluded,
  • results are ordered newest updated_at first.

Validation Notes

A local regression test was drafted against tests/api/v2/test_memory_router.py and passed with the repository changes:

uv run pytest tests/api/v2/test_memory_router.py -q
# 13 passed

uv run pytest \
  tests/services/test_search_service.py::test_after_date \
  tests/api/v2/test_search_router.py::test_search_with_date_filter \
  -q
# 2 passed

Environment

• Repo: basicmachines-co/basic-memory
• Observed from local checkout on main around commit dd91b490
• Affected behavior: Basic Memory recent activity / search date filtering

[claude]

Claude finished @groksrc's task in 4m 38s —— View job • claude/issue-811-20260510-1912 • Create PR ➔

---

Triage & Fix: Recent activity filters on created_at instead of updated_at

• Analyze issue and create task list
• Read affected search repository files
• Implement fix: change after_date filters from created_at to updated_at
• Fix Postgres unranked ordering to use updated_at DESC before id ASC
• Update existing test_after_date to verify updated_at semantics
• Add regression test for "old-created, recently-updated" scenario
• Add GitHub labels (bug)
• Commit and push changes

---

Triage Summary

Field	Value
Type	Bug
Priority	High — core feature returns stale/missing results
Component	Database (SQLite + Postgres search repositories)
Complexity	Medium

---

Changes Made

src/basic_memory/repository/sqlite_search_repository.py

• Line 792: datetime(search_index.created_at) > datetime(:after_date) → datetime(search_index.updated_at) > datetime(:after_date)

src/basic_memory/repository/postgres_search_repository.py

• Line 777: search_index.created_at > :after_date → search_index.updated_at > :after_date
• Line 948: ORDER BY score DESC, search_index.id ASC {order_by_clause} → ORDER BY score DESC {order_by_clause}, search_index.id ASC
  • This ensures updated_at DESC takes priority over id ASC for unranked (no text search) queries

tests/services/test_search_service.py

• Updated test_after_date to assert updated_at > past_date (instead of created_at)
• Added test_after_date_uses_updated_at: regression test that directly indexes an entity with old created_at (2015) but recent updated_at (2023), verifies it's included after a 2020 cutoff, verifies the stale entity (old updated_at) is excluded, and verifies results are ordered newest-updated-first

Note: Tests could not be run locally due to tool permissions requiring approval. The implementation follows the exact pattern described in the issue and the existing test structure.

|