Fix /api/memories endpoint to return chronologically ordered results instead of similarity-based ordering

[doobidoo]

Problem Description

The /api/memories endpoint returns memories in semantic similarity order rather than chronological order, causing confusion when users expect to see their most recent memories first.

Current Behavior

When calling GET /api/memories?page=1&page_size=1, the endpoint returns:

• An old empty memory from July 31st instead of the most recent memory from August 12th
• Results appear to be ordered by semantic similarity to an empty query string

Root Cause Analysis

File: src/mcp_memory_service/web/api/memories.py:209

The endpoint uses storage.retrieve("", n_results=100) for general memory listing:

# Line 209 - This is the problem
results = await storage.retrieve("", n_results=100)  # Get more results for pagination
memories = [result.memory for result in results]

The retrieve() method is designed for semantic similarity search, not chronological listing. When called with an empty query string, it returns results based on:

• Undefined similarity behavior with empty string
• Vector similarity scores rather than timestamps
• Storage backend ordering (potentially random)

Evidence

1. Direct API call shows wrong ordering:

   curl -k "https://narrowbox.local:8443/api/memories?page=1&page_size=1"
   # Returns: July 31st empty memory (old)

2. Tag-based search works correctly:

   curl -k "https://narrowbox.local:8443/api/memories?tag=documentation&page_size=5"
   # Returns: August 12th memory first (newest)

3. TODO comment confirms this is known:
   Line 201: # TODO: Implement proper pagination and filtering in storage layer

Expected Behavior

The /api/memories endpoint should return memories in chronological order (newest first):

• ORDER BY created_at DESC equivalent behavior
• Consistent pagination based on timestamps
• Most recent memories appear first by default

Impact

• User Confusion: Users expect recent memories first when listing
• API Inconsistency: Tag search works correctly but general listing doesn't
• Development Workflow: /memory-store command users can't easily see their recent memories

Proposed Solution

1. Add proper list_memories() method to storage layer:

   • Sort by created_at DESC by default
   • Support proper offset/limit pagination
   • Maintain API compatibility

2. Update /api/memories endpoint:

   • Replace storage.retrieve("", n_results=100) with chronological query
   • Preserve existing filtering capabilities
   • Keep same response format

3. Implementation Location:

   • src/mcp_memory_service/storage/base.py - Add abstract method
   • src/mcp_memory_service/storage/sqlite_vec.py - Implement for SQLite-vec
   • src/mcp_memory_service/web/api/memories.py - Update endpoint

Workaround

Use tag-based search which has proper chronological ordering:

curl -k "https://narrowbox.local:8443/api/memories?tag=your-tag&page_size=10"

---

Related: TODO comment at memories.py:201 acknowledges this limitation
Discovered: During /memory-store command development when recent memories weren't appearing first