Function get_latest_selected_papers

skills/storage-helper/scripts/db_ops.py:1467–1546

Get the latest selected-paper batch for a user. Returns: Dict with push_id / selection_time / papers, or None when no selection exists.

(user_id: str)

def get_latest_selected_papers(user_id: str) -> Optional[Dict]:
    """
    Get the latest selected-paper batch for a user.

    Returns:
        Dict with push_id / selection_time / papers, or None when no selection exists.
    """
    conn = get_connection()
    cursor = conn.cursor()

    clear_after_log_id = _get_latest_selection_clear_log_id(cursor, user_id)
    latest_batch_sql = """
        SELECT push_id, MAX(timestamp) AS timestamp
        FROM behavior_logs
        WHERE user_id = ?
          AND action = 'selected'
    """
    latest_batch_params: List[Any] = [user_id]
    if clear_after_log_id is not None:
        latest_batch_sql += " AND id > ?"
        latest_batch_params.append(clear_after_log_id)
    latest_batch_sql += """
        GROUP BY push_id
        ORDER BY timestamp DESC, push_id DESC
        LIMIT 1
    """
    cursor.execute(latest_batch_sql, latest_batch_params)

    row = cursor.fetchone()
    if not row:
        conn.close()
        return None

    push_id = row["push_id"]
    selection_time = row["timestamp"]

    cursor.execute(
        """
        SELECT p.*, bl.id AS behavior_log_id, bl.metadata AS bl_metadata,
               push_bl.metadata AS push_metadata
        FROM papers p
        JOIN behavior_logs bl ON p.id = bl.paper_id
        LEFT JOIN behavior_logs push_bl
          ON push_bl.user_id = bl.user_id
         AND push_bl.push_id = bl.push_id
         AND push_bl.paper_id = bl.paper_id
         AND push_bl.action = 'pushed'
        WHERE bl.user_id = ?
          AND bl.push_id = ?
          AND bl.action = 'selected'
        ORDER BY bl.id ASC
        """,
        (user_id, push_id),
    )

    papers = [
        _build_paper_dict(
            row,