Retention Policy Field Name Fix

Retention Policy Field Name Fix

Version: 0.237.005

Problem Statement

The retention policy was not deleting any conversations despite being configured with valid retention periods. After the v0.237.004 fix that required conversations to have a valid timestamp field, the policy found zero conversations to delete because it was querying for a field (last_activity_at) that doesn't exist on any conversation document.

Symptoms

• Retention policy execution shows "0 conversations deleted" even with old conversations present
• Manual execution completes successfully but no conversations are affected
• Conversations older than the retention period remain in the database

Debug Output Example

[DEBUG] [INFO]: Querying aged conversations: workspace_type=personal, retention_days=1
[DEBUG] [INFO]: Found 0 aged conversations for personal workspace

Root Cause Analysis

The Field Mismatch

The retention policy SQL query was looking for last_activity_at:

SELECT c.id, c.title, c.last_activity_at, c.user_id
FROM c
WHERE c.user_id = @partition_value
AND IS_DEFINED(c.last_activity_at) 
AND NOT IS_NULL(c.last_activity_at)
AND c.last_activity_at < @cutoff_date

However, all conversation schemas use last_updated, not last_activity_at:

Example Conversation Documents

Schema 1 (Legacy - messages embedded):

{
    "id": "2ff663f2-f260-4a21-a388-dc8f60caa353",
    "user_id": "441f7b4e-2f43-4a83-abf1-40697309b24d",
    "messages": [...],
    "last_updated": "2025-03-04T21:09:23.945024",
    "title": "how do i know if i'm being sca..."
}

Schema 2 (Middle - messages in separate container):

{
    "id": "4d45051a-693d-4893-8960-9c4c2dc6b8be",
    "user_id": "07e61033-ea1a-4472-a1e7-6b9ac874984a",
    "last_updated": "2025-08-01T18:58:10.137683",
    "title": "what did paul win"
}

Schema 3 (Current - messages with threading):

{
    "id": "bba2f03e-aa9a-4cee-a8fb-273e0c89c834",
    "user_id": "07e61033-ea1a-4472-a1e7-6b9ac874984a",
    "last_updated": "2026-01-27T21:00:43.910594",
    "title": "tell me about https://microsof...",
    "context": [...],
    "tags": [...],
    "strict": false,
    "is_pinned": false,
    "is_hidden": false
}

Why last_activity_at Never Existed

The field last_activity_at was likely a planned feature that was never implemented. All conversation creation and update code paths use last_updated:

# From route_backend_conversations.py
conversation_item = {
    'id': conversation_id,
    'user_id': user_id,
    'last_updated': datetime.utcnow().isoformat(),  # ← Only 'last_updated' is set
    'title': 'New Conversation',
    ...
}

Solution Implementation

File Modified: functions_retention_policy.py

Changed all references from last_activity_at to last_updated:

1. Updated SQL Query

# Before (incorrect field)
query = f"""
    SELECT c.id, c.title, c.last_activity_at, c.{partition_field}
    FROM c
    WHERE c.{partition_field} = @partition_value
    AND IS_DEFINED(c.last_activity_at) 
    AND NOT IS_NULL(c.last_activity_at)
    AND c.last_activity_at < @cutoff_date
"""

# After (correct field)
query = f"""
    SELECT c.id, c.title, c.last_updated, c.{partition_field}
    FROM c
    WHERE c.{partition_field} = @partition_value
    AND IS_DEFINED(c.last_updated) 
    AND NOT IS_NULL(c.last_updated)
    AND c.last_updated < @cutoff_date
"""

2. Updated Docstring

# Before
"""Delete conversations that exceed the retention period based on last_activity_at."""

# After
"""Delete conversations that exceed the retention period based on last_updated."""

3. Updated Result Dictionaries

# Before
deleted_details.append({
    'id': conversation_id,
    'title': conversation_title,
    'last_activity_at': conv.get('last_activity_at')
})

# After
deleted_details.append({
    'id': conversation_id,
    'title': conversation_title,
    'last_updated': conv.get('last_updated')
})

Files Modified

Testing & Validation

After the fix, retention policy execution should correctly identify and delete old conversations:

[DEBUG] [INFO]: Querying aged conversations: workspace_type=personal, retention_days=1
[DEBUG] [INFO]: Found 3 aged conversations for personal workspace
[DEBUG] [INFO]: Deleted conversation abc123 (Test Conversation) due to retention policy

Test Scenarios

Schema Compatibility

This fix ensures compatibility with all three conversation schemas that exist in production:

Version History

Related Documentation

• v0.237.004 Null Last Activity Fix
• v0.236.012 NotFound Error Fix
• Retention Policy Feature Documentation