sql/001_schema.sql
1 CREATE DATABASE IF NOT EXISTS moraine;
2
3 CREATE TABLE IF NOT EXISTS moraine.raw_events (
4 ingested_at DateTime64(3) DEFAULT now64(3),
5 source_name LowCardinality(String),
6 provider LowCardinality(String),
7 source_file String,
8 source_inode UInt64,
9 source_generation UInt32,
10 source_line_no UInt64,
11 source_offset UInt64,
12 record_ts String,
13 top_type LowCardinality(String),
14 session_id String,
15 raw_json String,
16 raw_json_hash UInt64,
17 event_uid String
18 )
19 ENGINE = MergeTree
20 PARTITION BY toYYYYMM(ingested_at)
21 ORDER BY (source_name, source_file, source_generation, source_offset, source_line_no, event_uid);
22
23 CREATE TABLE IF NOT EXISTS moraine.events (
24 ingested_at DateTime64(3) DEFAULT now64(3),
25 event_uid String,
26 session_id String,
27 session_date Date,
28 source_name LowCardinality(String),
29 provider LowCardinality(String),
30 source_file String,
31 source_inode UInt64,
32 source_generation UInt32,
33 source_line_no UInt64,
34 source_offset UInt64,
35 source_ref String,
36 record_ts String,
37 event_ts DateTime64(3),
38 event_kind LowCardinality(String),
39 actor_kind LowCardinality(String),
40 payload_type LowCardinality(String),
41 op_kind LowCardinality(String),
42 op_status LowCardinality(String),
43 request_id String,
44 trace_id String,
45 turn_index UInt32,
46 item_id String,
47 tool_call_id String,
48 parent_tool_call_id String,
49 origin_event_id String,
50 origin_tool_call_id String,
51 tool_name LowCardinality(String),
52 tool_phase LowCardinality(String),
53 tool_error UInt8,
54 agent_run_id String,
55 agent_label String,
56 coord_group_id String,
57 coord_group_label String,
58 is_substream UInt8,
59 model LowCardinality(String),
60 input_tokens UInt32,
61 output_tokens UInt32,
62 cache_read_tokens UInt32,
63 cache_write_tokens UInt32,
64 latency_ms UInt32,
65 retry_count UInt16,
66 service_tier LowCardinality(String),
67 content_types Array(String),
68 has_reasoning UInt8,
69 text_content String,
70 text_preview String,
71 payload_json String,
72 token_usage_json String,
73 event_version UInt64,
74 CONSTRAINT events_event_kind_domain CHECK event_kind IN (
75 'session_meta', 'turn_context', 'message', 'tool_call', 'tool_result', 'reasoning',
76 'event_msg', 'compacted_raw', 'progress', 'system', 'summary', 'queue_operation',
77 'file_history_snapshot', 'unknown'
78 ),
79 CONSTRAINT events_payload_type_domain CHECK payload_type IN (
80 'session_meta', 'turn_context', 'message', 'function_call', 'function_call_output',
81 'custom_tool_call', 'custom_tool_call_output', 'web_search_call', 'reasoning',
82 'response_item', 'event_msg', 'user_message', 'agent_message', 'agent_reasoning',
83 'token_count', 'task_started', 'task_complete', 'turn_aborted', 'item_completed',
84 'search_results_received', 'compacted', 'thinking', 'tool_use', 'tool_result', 'text',
85 'progress', 'system', 'summary', 'queue-operation', 'file-history-snapshot', 'unknown'
86 )
87 )
88 ENGINE = ReplacingMergeTree(event_version)
89 PARTITION BY toYYYYMM(ingested_at)
90 ORDER BY (session_id, event_ts, source_name, source_file, source_generation, source_offset, source_line_no, event_uid);
91
92 CREATE TABLE IF NOT EXISTS moraine.event_links (
93 ingested_at DateTime64(3) DEFAULT now64(3),
94 event_uid String,
95 linked_event_uid String,
96 linked_external_id String,
97 link_type LowCardinality(String),
98 session_id String,
99 provider LowCardinality(String),
100 source_name LowCardinality(String),
101 metadata_json String,
102 event_version UInt64,
103 CONSTRAINT event_links_link_type_domain CHECK link_type IN (
104 'parent_event', 'compacted_parent', 'parent_uuid', 'tool_use_id', 'source_tool_assistant',
105 'unknown'
106 )
107 )
108 ENGINE = ReplacingMergeTree(event_version)
109 PARTITION BY toYYYYMM(ingested_at)
110 ORDER BY (session_id, event_uid, link_type, linked_event_uid);
111
112 CREATE TABLE IF NOT EXISTS moraine.tool_io (
113 ingested_at DateTime64(3) DEFAULT now64(3),
114 event_uid String,
115 session_id String,
116 provider LowCardinality(String),
117 source_name LowCardinality(String),
118 tool_call_id String,
119 parent_tool_call_id String,
120 tool_name LowCardinality(String),
121 tool_phase LowCardinality(String),
122 tool_error UInt8,
123 input_json String,
124 output_json String,
125 output_text String,
126 input_bytes UInt32,
127 output_bytes UInt32,
128 input_preview String,
129 output_preview String,
130 io_hash UInt64,
131 source_ref String,
132 event_version UInt64
133 )
134 ENGINE = ReplacingMergeTree(event_version)
135 PARTITION BY toYYYYMM(ingested_at)
136 ORDER BY (session_id, tool_call_id, event_uid);
137
138 CREATE TABLE IF NOT EXISTS moraine.ingest_errors (
139 ingested_at DateTime64(3) DEFAULT now64(3),
140 source_name LowCardinality(String),
141 provider LowCardinality(String),
142 source_file String,
143 source_inode UInt64,
144 source_generation UInt32,
145 source_line_no UInt64,
146 source_offset UInt64,
147 error_kind LowCardinality(String),
148 error_text String,
149 raw_fragment String
150 )
151 ENGINE = MergeTree
152 PARTITION BY toYYYYMM(ingested_at)
153 ORDER BY (source_name, source_file, source_generation, source_offset, source_line_no);
154
155 CREATE TABLE IF NOT EXISTS moraine.ingest_checkpoints (
156 updated_at DateTime64(3) DEFAULT now64(3),
157 source_name LowCardinality(String),
158 source_file String,
159 source_inode UInt64,
160 source_generation UInt32,
161 last_offset UInt64,
162 last_line_no UInt64,
163 status LowCardinality(String)
164 )
165 ENGINE = ReplacingMergeTree(updated_at)
166 PARTITION BY toYYYYMM(updated_at)
167 ORDER BY (source_name, source_file, source_generation);