sql/002_views.sql

     1 DROP VIEW IF EXISTS moraine.v_session_summary;
     2 DROP VIEW IF EXISTS moraine.v_turn_summary;
     3 DROP VIEW IF EXISTS moraine.v_conversation_trace;
     4 DROP VIEW IF EXISTS moraine.v_all_events;
     5  
     6 CREATE VIEW moraine.v_all_events AS
     7 SELECT
     8   ingested_at,
     9   event_uid,
    10   origin_event_id AS compacted_parent_uid,
    11   session_id,
    12   session_date,
    13   source_file,
    14   source_inode,
    15   source_generation,
    16   source_line_no,
    17   source_offset,
    18   source_ref,
    19   record_ts,
    20   event_kind AS event_class,
    21   payload_type,
    22   actor_kind AS actor_role,
    23   toString(turn_index) AS turn_id,
    24   item_id,
    25   tool_call_id AS call_id,
    26   tool_name AS name,
    27   if(tool_phase != '', tool_phase, op_status) AS phase,
    28   text_content,
    29   payload_json,
    30   token_usage_json,
    31   event_version
    32 FROM moraine.events;
    33  
    34 CREATE VIEW moraine.v_conversation_trace AS
    35 SELECT
    36   session_id,
    37   session_date,
    38   event_uid,
    39   compacted_parent_uid,
    40   source_file,
    41   source_generation,
    42   source_line_no,
    43   source_offset,
    44   source_ref,
    45   ifNull(parseDateTime64BestEffortOrNull(record_ts), ingested_at) AS event_time,
    46   row_number() OVER (
    47     PARTITION BY session_id
    48     ORDER BY ifNull(parseDateTime64BestEffortOrNull(record_ts), ingested_at), source_file, source_generation, source_offset, source_line_no, event_uid
    49   ) AS event_order,
    50   if(
    51     toUInt32OrZero(turn_id) > 0,
    52     toUInt32OrZero(turn_id),
    53     greatest(
    54       toUInt32(1),
    55       toUInt32(
    56         sum(if(actor_role = 'user' AND event_class = 'message', 1, 0)) OVER (
    57           PARTITION BY session_id
    58           ORDER BY ifNull(parseDateTime64BestEffortOrNull(record_ts), ingested_at), source_file, source_generation, source_offset, source_line_no, event_uid
    59           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    60         )
    61       )
    62     )
    63   ) AS turn_seq,
    64   turn_id,
    65   actor_role,
    66   event_class,
    67   payload_type,
    68   call_id,
    69   name,
    70   phase,
    71   item_id,
    72   text_content,
    73   payload_json,
    74   token_usage_json
    75 FROM moraine.v_all_events;
    76  
    77 CREATE VIEW moraine.v_turn_summary AS
    78 SELECT
    79   session_id,
    80   turn_seq,
    81   anyIf(turn_id, turn_id != '') AS turn_id,
    82   min(event_time) AS started_at,
    83   max(event_time) AS ended_at,
    84   count() AS total_events,
    85   countIf(actor_role = 'user' AND event_class = 'message') AS user_messages,
    86   countIf(actor_role = 'assistant' AND event_class = 'message') AS assistant_messages,
    87   countIf(event_class = 'tool_call') AS tool_calls,
    88   countIf(event_class = 'tool_result') AS tool_results,
    89   countIf(event_class = 'reasoning') AS reasoning_items
    90 FROM moraine.v_conversation_trace
    91 GROUP BY session_id, turn_seq;
    92  
    93 CREATE VIEW moraine.v_session_summary AS
    94 SELECT
    95   session_id,
    96   min(event_time) AS first_event_time,
    97   max(event_time) AS last_event_time,
    98   max(turn_seq) AS total_turns,
    99   count() AS total_events,
   100   countIf(event_class = 'tool_call') AS tool_calls,
   101   countIf(event_class = 'tool_result') AS tool_results,
   102   countIf(actor_role = 'user' AND event_class = 'message') AS user_messages,
   103   countIf(actor_role = 'assistant' AND event_class = 'message') AS assistant_messages
   104 FROM moraine.v_conversation_trace
   105 GROUP BY session_id;