Repository: FlexMeasures/flexmeasures
Pull request #1179 (2024-09-17 19:31:00)

Fix: ordering of audit logs 
## Description

This PR resolves issue [#1178](https://github.com/FlexMeasures/flexmeasures/issues/1178)  by improving the sorting of audit logs (users, assets) and jobs queue on the status page. Previously, sorting was done by the human-friendly naturalized_datetime, which caused incorrect sorting results. also tryinng to sort by the raw datetime column gave inconsistent results  because the column has inputs in this format  `Sun, 28 Apr 2024 08:55:58 GMT`. So transforming that to UTC Timestamp gave the best results for sorting. 

- Changes include:

  - Added a new utility function `to_utc_timestamp()` in utils/time_utils.py to convert input like this  `Sun, 28 Apr 2024 08:55:58 GMT` to UTC timestamp.
  - Registered a Jinja filter for to_utc_timestamp.
  - Updated the HTML templates to include the hidden column for UTC timestamps and used that for sorting.


## Look & Feel

- Asset audit_log:
  - Before:
![Screenshot from 2024-09-17 19-51-54](https://github.com/user-attachments/assets/1b4d8ba7-4e1b-43ac-b6fa-99d0eeb6f187)
  - After:  
![Screenshot from 2024-09-17 21-26-38](https://github.com/user-attachments/assets/95fb29c3-b03a-4a6f-98c1-08fe60b9a971)


- User Audit_log:
   - Before:
![Screenshot from 2024-09-17 21-16-57](https://github.com/user-attachments/assets/99759a96-2f01-4d3c-b213-0147e7741dd0)
  - After:  
![Screenshot from 2024-09-17 21-17-36](https://github.com/user-attachments/assets/0b200fc4-86a1-471c-b39b-0e19f292b039)

-  jobs queue on the status page:
   - Before:
![Screenshot from 2024-09-17 20-53-22](https://github.com/user-attachments/assets/5c71e18a-04ea-4243-8672-fc58163665e6)
   - After:  
![Screenshot from 2024-09-17 20-53-12](https://github.com/user-attachments/assets/9265a8e2-e20a-40b5-ac0c-a65503425aac)


## How to test

1. Navigate to the audit logs page for assets or users or  The Asset status page.
2. Confirm that the logs are sorted in descending order correctly.

## Related Items

closes issue #1178

---

- [x] I agree to contribute to the project under Apache 2 License. 
- [x] To the best of my knowledge, the proposed patch is not based on code under GPL or other license that is incompatible with FlexMeasures




Patch:
diff --git a/documentation/changelog.rst b/documentation/changelog.rst
index a45025df..2aebe4c8 100644
--- a/documentation/changelog.rst
+++ b/documentation/changelog.rst
@@ -43,6 +43,8 @@ Bugfixes
 * Fix missing value on spring :abbr:`DST (Daylight Saving Time)` transition for ``PandasReporter`` using daily sensor as input [see `PR #1122 <https://github.com/FlexMeasures/flexmeasures/pull/1122>`_]
 * Fix date range persistence on session across different pages [see `PR #1165 <https://github.com/FlexMeasures/flexmeasures/pull/1165>`_]
 * Fix issue with account creation failing when the --logo-url flag is omitted. [see related PRs `PR #1167 <https://github.com/FlexMeasures/flexmeasures/pull/1167>`_ and `PR #1145 <https://github.com/FlexMeasures/flexmeasures/pull/1145>`_]
+* Fix ordering of audit logs and job list on status page [see PR `PR #1167 <https://github.com/FlexMeasures/flexmeasures/pull/1179>_`]
+
 
 v0.22.0 | June 29, 2024
 ============================
diff --git a/flexmeasures/ui/__init__.py b/flexmeasures/ui/__init__.py
index 9ba44677..44fc8f07 100644
--- a/flexmeasures/ui/__init__.py
+++ b/flexmeasures/ui/__init__.py
@@ -23,6 +23,7 @@ from flexmeasures.utils.flexmeasures_inflection import (
 from flexmeasures.utils.time_utils import (
     localized_datetime_str,
     naturalized_datetime_str,
+    to_utc_timestamp,
 )
 from flexmeasures.utils.app_utils import (
     parse_config_entry_by_account_roles,
@@ -135,6 +136,7 @@ def add_jinja_filters(app):
     )  # Allow expression statements (e.g. for modifying lists)
     app.jinja_env.filters["localized_datetime"] = localized_datetime_str
     app.jinja_env.filters["naturalized_datetime"] = naturalized_datetime_str
+    app.jinja_env.filters["to_utc_timestamp"] = to_utc_timestamp
     app.jinja_env.filters["naturalized_timedelta"] = naturaldelta
     app.jinja_env.filters["capitalize"] = capitalize
     app.jinja_env.filters["pluralize"] = pluralize
diff --git a/flexmeasures/ui/templates/crud/asset_audit_log.html b/flexmeasures/ui/templates/crud/asset_audit_log.html
index 8a804f71..fe90c2b6 100644
--- a/flexmeasures/ui/templates/crud/asset_audit_log.html
+++ b/flexmeasures/ui/templates/crud/asset_audit_log.html
@@ -13,6 +13,7 @@
             <table id="asset_audit_log" class="table table-striped table-responsive paginate nav-on-click" title="View data">
                 <thead>
                     <tr>
+                        <th style="display:none;">Event Timestamp</th><!-- Hidden UTC Timestamp column for sorting, Keep at position zero -->
                         <th>Event Datetime</th>
                         <th>Event Name</th>
                         <th>Acting user</th>
@@ -21,6 +22,9 @@
                 <tbody>
                     {% for audit_log in audit_logs: %}
                     <tr>
+                        <td style="display:none;">
+                            {{ audit_log.event_datetime | to_utc_timestamp }} <!-- Hidden UTC Timestamp column for sorting, Keep at position zero -->
+                        </td>
                         <td title="{{  audit_log.event_datetime }}">
                             {{ audit_log.event_datetime | naturalized_datetime }}
                         </td>
@@ -40,7 +44,9 @@
 
 <script>
     $(document).ready(function() {
-        $('#asset_audit_log').DataTable({"order": [[ 0, "desc" ]]});
+        $('#asset_audit_log').DataTable({
+            "order": [[ 0, "desc" ]]  // Sort by the hidden UTC Timestamp column
+        });
     });
 </script>
 
diff --git a/flexmeasures/ui/templates/crud/user_audit_log.html b/flexmeasures/ui/templates/crud/user_audit_log.html
index 5e2927d6..362ecec3 100644
--- a/flexmeasures/ui/templates/crud/user_audit_log.html
+++ b/flexmeasures/ui/templates/crud/user_audit_log.html
@@ -14,6 +14,7 @@
                 <table id="user_audit_log" class="table table-striped table-responsive paginate nav-on-click" title="View data">
                     <thead>
                         <tr>
+                            <th style="display:none;">Event Timestamp</th> <!-- Hidden UTC Timestamp column for sorting, Keep at position zero -->
                             <th>Event Name</th>
                             <th>Event Datetime</th>
                             <th>Acting user</th>
@@ -22,6 +23,9 @@
                     <tbody>
                         {% for audit_log in audit_logs: %}
                         <tr>
+                            <td style="display:none;">
+                                {{ audit_log.event_datetime | to_utc_timestamp }} <!-- Hidden UTC Timestamp column for sorting, Keep at position zero -->
+                            </td>
                             <td>
                                 {{ audit_log.event }}
                             </td>
@@ -42,7 +46,7 @@
 
 <script>
     $(document).ready(function() {
-        $('#user_audit_log').DataTable({"order": [[ 1, "desc" ]]});
+        $('#user_audit_log').DataTable({"order": [[ 0, "desc" ]]}); // Sort by the hidden UTC Timestamp column
     });
 </script>
 
diff --git a/flexmeasures/ui/templates/views/status.html b/flexmeasures/ui/templates/views/status.html
index 09295886..dc13f9af 100644
--- a/flexmeasures/ui/templates/views/status.html
+++ b/flexmeasures/ui/templates/views/status.html
@@ -65,18 +65,21 @@
                 <table id="scheduling_forecasting_jobs" class="table table-striped table-responsive paginate nav-on-click">
                     <thead>
                         <tr>
+                            <th style="display:none;">Created at Timestamp</th> <!-- Hidden UTC Timestamp column for sorting, Keep at position 0 -->
                             <th>Created at</th>
                             <th>Queue</th>
                             <th>Entity</th>
                             <th class="text-right no-sort">Status</th>
                             <th class="text-right">Info</th>
                             <th class="d-none">URL</th>
-                            <th class="d-none">Created at for sort</th>
                         </tr>
                     </thead>
                     <tbody>
                         {% for job_data in jobs_data: %}
                         <tr title="View data">
+                            <td style="display:none;">
+                                {{ job_data.enqueued_at | to_utc_timestamp }} <!-- Hidden UTC Timestamp column for sorting, Keep at position 0 -->
+                            </td>
                             <td title="Enqueued at: {{ job_data.enqueued_at}}">
                                 {{ job_data.enqueued_at | naturalized_datetime }}
                             </td>
@@ -122,9 +125,6 @@
                             <td class="hidden d-none invisible" style="display:none;">
                                 /tasks/0/view/job/{{ job_data.job_id }}
                             </td>
-                            <td class="hidden d-none invisible" style="display:none;">
-                                {{ job_data.enqueued_at }}
-                            </td>
                         </tr>
                         {% endfor %}
                     </tbody>
@@ -137,10 +137,10 @@
     </div>
 </div>
 
-<!-- sort scheduling and forecasting jobs by 'Created at' column -->
+<!-- sort scheduling and forecasting jobs by hidden 'Created at Timestamp' column -->
 <script>
     $(document).ready(function() {
-        $('#scheduling_forecasting_jobs').DataTable({"order": [[ 6, "desc" ]]});
+        $('#scheduling_forecasting_jobs').DataTable({"order": [[ 0, "desc" ]]}); // Sort by the hidden UTC Timestamp column
     });
 </script>
 {% endblock %}
diff --git a/flexmeasures/utils/time_utils.py b/flexmeasures/utils/time_utils.py
index ad350cf1..8467ea76 100644
--- a/flexmeasures/utils/time_utils.py
+++ b/flexmeasures/utils/time_utils.py
@@ -411,3 +411,50 @@ def apply_offset_chain(
     if isinstance(dt, datetime):
         return _dt.to_pydatetime()
     return _dt
+
+
+def to_utc_timestamp(value):
+    """
+    Convert a datetime object or string to a UTC timestamp (seconds since epoch).
+
+    The dt parameter can be either naive or tz-aware. We assume UTC in the naive case.
+    If dt parameter is a string, it is expected to look like 'Sun, 28 Apr 2024 08:55:58 GMT'.
+    String format is supported for cases when we process JSON from internal API responses,
+    e.g., ui/crud/users auditlog router.
+
+
+    Returns:
+    - Float: seconds since Unix epoch (1970-01-01 00:00:00 UTC)
+    - None: if input is None
+
+
+    Hint: This can be set as a jinja filter to display UTC timestamps in the app, e.g.:
+    app.jinja_env.filters['to_utc_timestamp'] = to_utc_timestamp
+
+    Example usage:
+    >>> to_utc_timestamp(datetime(2024, 4, 28, 8, 55, 58))
+    1714294558.0
+    >>> to_utc_timestamp("Sun, 28 Apr 2024 08:55:58 GMT")
+    1714294558.0
+    >>> to_utc_timestamp(None)
+    None
+    """
+    if value is None:
+        return None
+    if isinstance(value, str):
+        # Parse string datetime in the format 'Tue, 13 Dec 2022 14:06:23 GMT'
+        try:
+            value = datetime.strptime(value, "%a, %d %b %Y %H:%M:%S %Z")
+        except ValueError:
+            # Return None or raise an error if the string is in an unexpected format
+            return None
+    if isinstance(value, datetime):
+        if value.tzinfo is None:
+            # If naive, assume UTC
+            value = pd.Timestamp(value).tz_localize("utc")
+        else:
+            # Convert to UTC if already timezone-aware
+            value = pd.Timestamp(value).tz_convert("utc")
+
+    # Return Unix timestamp (seconds since epoch)
+    return value.timestamp()


Test patch:
diff --git a/flexmeasures/utils/tests/test_time_utils.py b/flexmeasures/utils/tests/test_time_utils.py
index 15c9056f..b66ff540 100644
--- a/flexmeasures/utils/tests/test_time_utils.py
+++ b/flexmeasures/utils/tests/test_time_utils.py
@@ -13,6 +13,7 @@ from flexmeasures.utils.time_utils import (
     naturalized_datetime_str,
     get_most_recent_clocktime_window,
     apply_offset_chain,
+    to_utc_timestamp,
 )
 
 
@@ -206,3 +207,21 @@ def test_apply_offset_chain(
     output_date: pd.Timestamp | datetime,
 ):
     assert apply_offset_chain(input_date, offset_chain) == output_date
+
+
+@pytest.mark.parametrize(
+    "input_value, expected_output",
+    [
+        (datetime(2024, 4, 28, 8, 55, 58), 1714294558.0),
+        (pd.Timestamp("2024-04-28 08:55:58", tz="UTC").to_pydatetime(), 1714294558.0),
+        ("Sun, 28 Apr 2024 08:55:58 GMT", 1714294558.0),
+        ("Invalid date string", None),
+        (None, None),
+    ],
+)
+def test_to_utc_timestamp(input_value, expected_output):
+    result = to_utc_timestamp(input_value)
+    if expected_output is None:
+        assert result is None
+    else:
+        assert result == pytest.approx(expected_output)