Smart Query Manager
===================

The Smart Query Manager merges data from three sources: local database, buffered cache, and read buffer, providing a consistent view even during replication lag.

.. contents:: Table of Contents
   :local:
   :depth: 2

The Problem
-----------

**Scenario**: Client buffers observation at T0, queries at T1 (before replication).

**Without smart queries**:

* Query local database: Empty (not replicated yet)
* Client sees inconsistent state

**With smart queries**:

* Query database + buffer + read buffer
* Merge results
* Client sees consistent state

Core Implementation
-------------------

Key code from ``smart_query_manager.py``:

.. literalinclude:: ../../../ccat_ops_db_api/transaction_buffering/smart_query_manager.py
   :language: python
   :lines: 19-65

Query Flow
----------

.. mermaid::

   sequenceDiagram
       participant Client
       participant Smart as Smart Query
       participant DB as Local Database
       participant Redis as Redis Cache
       participant ReadBuf as Read Buffer
       
       Client->>Smart: search_records("ExecutedObsUnit", ...)
       
       par Parallel Queries
           Smart->>DB: SELECT FROM executed_obs_unit
           Smart->>Redis: GET buffered:ExecutedObsUnit:*
           Smart->>ReadBuf: GET read_buffer:ExecutedObsUnit:*
       end
       
       DB-->>Smart: [record1, record2]
       Redis-->>Smart: [record3 (buffered)]
       ReadBuf-->>Smart: [updates to record3]
       
       Smart->>Smart: Merge (buffer > DB)
       Smart->>Smart: Apply read buffer updates
       Smart->>Smart: Deduplicate by ID
       
       Smart-->>Client: [record1, record2, record3 (merged)]

Merge Priority
--------------

When same record exists in multiple sources:

1. **Read buffer** (highest priority - latest updates)
2. **Buffered cache** (higher - pending writes)
3. **Database** (lowest - may be stale)

Example:

.. code-block:: python

   # Database has:
   {"id": "uuid-1", "status": "running", "end_time": null}
   
   # Read buffer has:
   {"status": "completed", "end_time": "2025-01-01T01:00:00Z"}
   
   # Merged result:
   {"id": "uuid-1", "status": "completed", "end_time": "2025-01-01T01:00:00Z"}

Using in Code
-------------

.. code-block:: python

   from ccat_ops_db_api.transaction_buffering import get_smart_query_manager
   
   smart_query = get_smart_query_manager()
   
   # Search with conditions
   observations = await smart_query.search_records(
       "ExecutedObsUnit",
       {"obs_unit_id": 123, "status": "running"},
       limit=100
   )
   
   # Get related records
   packages = await smart_query.get_related_records(
       parent_id=obs_id,
       child_table="RawDataPackage",
       relationship_field="executed_obs_unit_id"
   )

Next Steps
----------

* :doc:`read-buffer-manager` - Mutable updates to buffered records
* :doc:`../../tutorials/complex-endpoints/smart-queries-with-buffering` - Tutorial