* Resolve PostgreSQL GROUP BY error in analytics

Resolves a GroupingError that occurred in PostgreSQL when querying
observability data (tools, prompts, resources). The error was caused by
SQLAlchemy generating different column expressions for the SELECT list
and the GROUP BY clause when extracting data from JSON fields.

This fix refactors the queries to create a single expression for the
JSON field extraction and reuses it in both the SELECT and GROUP BY
clauses, ensuring compatibility with PostgreSQL's strict grouping requirements.

Additionally, this commit introduces:
  - Unit tests parametrized for both PostgreSQL and SQLite dialects to
  verify the fix at the query construction level.
  - An end-to-end test that calls the affected API endpoints to ensure they
  function correctly with a database backend.

Signed-off-by: Gabriel Costa <gabrielcg@proton.me>

* 2182 - Improve observability charts lifecycle

Introduces a global Chart.js registry to manage chart instances
across the admin UI, preventing "canvas already in use" errors.
Enhances observability panels (metrics, tools, prompts) by:

 - Centralizing chart destruction and registration.
 - Implementing visibility checks to defer rendering of charts on hidden canvases.
 - Adding safeguards to prevent redundant and concurrent loading of partial views.
 - Ensuring proper cleanup of charts and auto-refresh intervals when navigating
  between tabs or unloading the page.

These changes improve the stability and performance of the observability
dashboard, providing a smoother user experience.

Signed-off-by: Gabriel Costa <gabrielcg@proton.me>

* Linting and testing fix

Signed-off-by: Gabriel Costa <gabrielcg@proton.me>

* fix(tests): use proper user context in observability SQL tests

The RBAC decorator expects a dict with 'email' and 'db' keys, not a
MagicMock. Updated the tests to use create_mock_user_context() which
provides the correct structure for permission checks.

Signed-off-by: Mihai Criveti <crivetimihai@gmail.com>

* fix(observability): address chart lifecycle and cleanup issues

- Remove redundant chart destruction on observability tab entry
  (charts are already destroyed when leaving the tab)
- Replace ineffective 'destroy' DOM event listeners with beforeunload
  handlers (Alpine.js doesn't emit 'destroy' DOM events)
- Move e2e test before __main__ block so it runs with pytest --main
- Update observability_resources.html to use global chart registry
  for consistent chart lifecycle management
- Add resources- prefix to chart cleanup when leaving observability tab

These fixes address blank chart issues when returning to the
observability tab and ensure proper cleanup of intervals/handlers.

Signed-off-by: Mihai Criveti <crivetimihai@gmail.com>

* fix(observability): stop auto-refresh and reset state on tab leave

Addresses two issues identified in review:

1. Blank charts on return: When leaving observability tab, charts are
   destroyed but `metricsLoaded` etc. flags remained true, preventing
   partials from reloading. Now dispatch `observability:leave` event
   that resets loaded flags so partials reload on return.

2. Orphaned intervals: Auto-refresh timers continued running after
   leaving the tab. Now each partial (metrics, tools, prompts, resources)
   listens for `observability:leave` and calls cleanup() to stop intervals.

The cleanup lifecycle is now:
- Tab leave: dispatches observability:leave event
- Partials: listen for event, call cleanup() to stop intervals
- Parent: resets loaded flags so partials reload on next visit
- Event handlers are properly removed in cleanup() to avoid leaks

Signed-off-by: Mihai Criveti <crivetimihai@gmail.com>

---------

Signed-off-by: Gabriel Costa <gabrielcg@proton.me>
Signed-off-by: Mihai Criveti <crivetimihai@gmail.com>
Co-authored-by: Mihai Criveti <crivetimihai@gmail.com>