fraiseql_core/compiler/fact_table/
mod.rs

1//! Fact Table Introspection Module
2//!
3//! This module provides functionality to detect and introspect fact tables following
4//! FraiseQL's analytics architecture:
5//!
6//! # Fact Table Pattern
7//!
8//! - **Table naming**: `tf_*` prefix (table fact)
9//! - **Measures**: SQL columns with numeric types (INT, BIGINT, DECIMAL, FLOAT) - for fast
10//!   aggregation
11//! - **Dimensions**: JSONB `data` column - for flexible GROUP BY
12//! - **Denormalized filters**: Indexed SQL columns (`customer_id`, `occurred_at`) - for fast WHERE
13//!
14//! # No Joins Principle
15//!
16//! FraiseQL does NOT support joins. All dimensional data must be denormalized into the
17//! `data` JSONB column at ETL time (managed by DBA/data team, not FraiseQL).
18//!
19//! # Example Fact Table
20//!
21//! ```sql
22//! CREATE TABLE tf_sales (
23//!     id BIGSERIAL PRIMARY KEY,
24//!     -- Measures (SQL columns for fast aggregation)
25//!     revenue DECIMAL(10,2) NOT NULL,
26//!     quantity INT NOT NULL,
27//!     cost DECIMAL(10,2) NOT NULL,
28//!     -- Dimensions (JSONB for flexible grouping)
29//!     data JSONB NOT NULL,
30//!     -- Denormalized filters (indexed for fast WHERE)
31//!     customer_id UUID NOT NULL,
32//!     product_id UUID NOT NULL,
33//!     occurred_at TIMESTAMPTZ NOT NULL,
34//!     created_at TIMESTAMPTZ DEFAULT NOW()
35//! );
36//! ```
37
38use std::collections::HashMap;
39
40use serde::{Deserialize, Serialize};
41
42mod detector;
43// Re-export from fraiseql-db to avoid duplication
44pub use fraiseql_db::{introspector::DatabaseIntrospector, types::DatabaseType};
45
46pub use self::detector::FactTableDetector;
47
48#[cfg(test)]
49mod tests;
50
51/// Metadata about a fact table structure
52#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
53pub struct FactTableMetadata {
54    /// Table name (e.g., "`tf_sales`")
55    pub table_name:               String,
56    /// The type the fact table is read as (ruling AB 1).
57    ///
58    /// Each measure, denormalized filter and dimension path is a field of it, so an aggregate
59    /// or a window over the table is gated as a read of that type: a field's `requires_scope`
60    /// and `authorize`, and the type's `requires_role`. `None`: the table declares no field
61    /// gate, and none applies.
62    #[serde(default, skip_serializing_if = "Option::is_none")]
63    pub type_name:                Option<String>,
64    /// Measures (aggregatable numeric columns)
65    pub measures:                 Vec<MeasureColumn>,
66    /// Dimension column (JSONB)
67    pub dimensions:               DimensionColumn,
68    /// Denormalized filter columns
69    pub denormalized_filters:     Vec<FilterColumn>,
70    /// Calendar dimensions for optimized temporal aggregations
71    #[serde(default)]
72    pub calendar_dimensions:      Vec<CalendarDimension>,
73    /// Optional partial-period awareness configuration.
74    ///
75    /// When a coarse-grain fact table (e.g. monthly pre-aggregated) is queried with
76    /// a date filter that falls mid-period, the runtime generates a UNION ALL query
77    /// combining fine-grain source data for boundary periods with pre-aggregated data
78    /// for complete intermediate periods.
79    #[serde(default)]
80    pub partial_period:           Option<PartialPeriodConfig>,
81    /// Maps JSONB measure paths to flat SQL column names for pre-aggregated views.
82    ///
83    /// When a materialized view stores measures as native columns (e.g. `volume BIGINT`)
84    /// instead of inside a JSONB `data` column, this mapping tells the SQL generator to
85    /// use `SUM("volume")` instead of `SUM((data->'measures'->>'volume')::numeric)`.
86    #[serde(default)]
87    pub native_measures:          HashMap<String, String>,
88    /// Maps deep JSONB dimension paths to flat SQL column names.
89    ///
90    /// When a materialized view denormalizes dimension values into flat columns
91    /// (e.g. `category_id INT` instead of `data->'dimensions'->'category'->>'id'`),
92    /// this mapping tells the GROUP BY generator to use `GROUP BY "category_id"`
93    /// instead of JSONB extraction. Enables btree index usage.
94    #[serde(default)]
95    pub native_dimension_mapping: HashMap<String, String>,
96}
97
98/// A dimension key in the form `native_dimension_mapping` keys compare in (#1231): each
99/// `.` segment in `snake_case`, so `itemCategory` and `item_category` name one dimension.
100#[must_use]
101pub fn dimension_key(key: &str) -> String {
102    key.split('.').map(crate::utils::to_snake_case).collect::<Vec<_>>().join(".")
103}
104
105/// A measure column (aggregatable numeric type)
106#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
107pub struct MeasureColumn {
108    /// Column name (e.g., "revenue")
109    pub name:     String,
110    /// SQL data type
111    pub sql_type: SqlType,
112    /// Is nullable
113    pub nullable: bool,
114}
115
116/// SQL data types
117#[non_exhaustive]
118#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
119pub enum SqlType {
120    /// SMALLINT, INT, INTEGER
121    Int,
122    /// BIGINT
123    BigInt,
124    /// DECIMAL, NUMERIC
125    Decimal,
126    /// REAL, FLOAT, DOUBLE PRECISION
127    Float,
128    /// JSONB (PostgreSQL)
129    Jsonb,
130    /// JSON (MySQL, SQL Server)
131    Json,
132    /// TEXT, VARCHAR
133    Text,
134    /// UUID
135    Uuid,
136    /// TIMESTAMP, TIMESTAMPTZ
137    Timestamp,
138    /// DATE
139    Date,
140    /// BOOLEAN
141    Boolean,
142    /// LTREE (PostgreSQL `ltree` extension): a materialized tree path (#1498). A filter
143    /// column of this type takes the path operators, groups by tree level or depth, and
144    /// resolves node ids through its declared `hierarchy`.
145    Ltree,
146    /// Other types
147    Other(String),
148}
149
150/// Dimension column (JSONB)
151#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
152pub struct DimensionColumn {
153    /// Column name (default: "dimensions" for fact tables)
154    pub name:  String,
155    /// Detected dimension paths (optional, extracted from sample data)
156    pub paths: Vec<DimensionPath>,
157}
158
159/// A dimension path within the JSONB column
160#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
161pub struct DimensionPath {
162    /// Path name (e.g., "category")
163    pub name:      String,
164    /// JSON path (e.g., "dimensions->>'category'" for PostgreSQL)
165    pub json_path: String,
166    /// Data type hint
167    pub data_type: String,
168}
169
170/// Calendar dimension metadata (pre-computed temporal fields)
171///
172/// Calendar dimensions provide 10-20x performance improvements for temporal aggregations
173/// by using pre-computed JSONB columns (`date_info`, `month_info`, etc.) instead of runtime
174/// `DATE_TRUNC` operations.
175///
176/// # Multi-Column Pattern
177///
178/// - 7 JSONB columns: `date_info`, `week_info`, `month_info`, `quarter_info`, `semester_info`,
179///   `year_info`, `decade_info`
180/// - Each contains hierarchical temporal buckets (e.g., `date_info` has: date, week, month,
181///   quarter, year)
182/// - Pre-populated by user's ETL (FraiseQL reads, doesn't populate)
183///
184/// # Example
185///
186/// ```json
187/// {
188///   "date": "2024-03-15",
189///   "week": 11,
190///   "month": 3,
191///   "quarter": 1,
192///   "semester": 1,
193///   "year": 2024
194/// }
195/// ```
196#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
197pub struct CalendarDimension {
198    /// Source timestamp column (e.g., "`occurred_at`")
199    pub source_column: String,
200
201    /// Available calendar granularity columns
202    pub granularities: Vec<CalendarGranularity>,
203}
204
205/// Calendar granularity column with pre-computed fields
206#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
207pub struct CalendarGranularity {
208    /// Column name (e.g., "`date_info`", "`month_info`")
209    pub column_name: String,
210
211    /// Temporal buckets available in this column
212    pub buckets: Vec<CalendarBucket>,
213}
214
215/// Pre-computed temporal bucket in calendar JSONB
216#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
217pub struct CalendarBucket {
218    /// JSON path key (e.g., "date", "month", "quarter")
219    pub json_key: String,
220
221    /// Corresponding `TemporalBucket` enum
222    pub bucket_type: crate::compiler::aggregate_types::TemporalBucket,
223
224    /// Data type (e.g., "date", "integer")
225    pub data_type: String,
226}
227
228/// A denormalized filter column
229#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
230pub struct FilterColumn {
231    /// Column name (e.g., "`customer_id`")
232    pub name:      String,
233    /// SQL data type
234    pub sql_type:  SqlType,
235    /// Is indexed (for performance)
236    pub indexed:   bool,
237    /// The declared hierarchy (`[hierarchies.<name>]`) an `ltree` path column is a path
238    /// of, so `descendant_of_id` / `ancestor_of_id` can resolve a node id to its path
239    /// (#1498). `None`: the column takes the path operators only.
240    #[serde(default, skip_serializing_if = "Option::is_none")]
241    pub hierarchy: Option<String>,
242}
243
244/// Configuration for partial-period awareness (UNION ALL optimization).
245///
246/// When a coarse-grain fact table (e.g. monthly pre-aggregated) is queried with
247/// a date filter that falls mid-period, the runtime generates a UNION ALL query
248/// combining fine-grain source data for boundary periods with pre-aggregated data
249/// for complete intermediate periods.
250#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
251pub struct PartialPeriodConfig {
252    /// Fine-grain source view (e.g., "`v_events_day`").
253    pub fine_grain_view:   String,
254    /// Column holding the period date (e.g., "`date`").
255    pub time_grain_column: String,
256    /// Truncation granularity for period boundaries.
257    pub time_grain_trunc:  TemporalGrain,
258}
259
260/// Temporal granularity for period boundary calculations.
261///
262/// Unlike `TemporalBucket` which
263/// includes sub-day granularities (`Second`, `Minute`, `Hour`) for GROUP BY bucketing,
264/// `TemporalGrain` is restricted to date-level granularities that define meaningful
265/// period boundaries for partial-period UNION ALL queries.
266#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize)]
267#[serde(rename_all = "lowercase")]
268pub enum TemporalGrain {
269    /// Day-level periods.
270    Day,
271    /// ISO week (Monday-start) periods.
272    Week,
273    /// Calendar month periods.
274    Month,
275    /// Calendar quarter periods (Q1=Jan, Q2=Apr, Q3=Jul, Q4=Oct).
276    Quarter,
277    /// Calendar year periods.
278    Year,
279}
280
281impl TemporalGrain {
282    /// Returns the PostgreSQL `DATE_TRUNC` argument string.
283    ///
284    /// # Examples
285    ///
286    /// ```
287    /// use fraiseql_core::compiler::fact_table::TemporalGrain;
288    ///
289    /// assert_eq!(TemporalGrain::Month.postgres_trunc_arg(), "month");
290    /// assert_eq!(TemporalGrain::Quarter.postgres_trunc_arg(), "quarter");
291    /// ```
292    #[must_use]
293    pub const fn postgres_trunc_arg(self) -> &'static str {
294        match self {
295            Self::Day => "day",
296            Self::Week => "week",
297            Self::Month => "month",
298            Self::Quarter => "quarter",
299            Self::Year => "year",
300        }
301    }
302
303    /// Converts to the corresponding `TemporalBucket` for use with SQL generators.
304    #[must_use]
305    pub const fn to_temporal_bucket(self) -> super::aggregate_types::TemporalBucket {
306        match self {
307            Self::Day => super::aggregate_types::TemporalBucket::Day,
308            Self::Week => super::aggregate_types::TemporalBucket::Week,
309            Self::Month => super::aggregate_types::TemporalBucket::Month,
310            Self::Quarter => super::aggregate_types::TemporalBucket::Quarter,
311            Self::Year => super::aggregate_types::TemporalBucket::Year,
312        }
313    }
314}
315
316/// Aggregation strategy for fact tables
317///
318/// Determines how fact table data is updated and structured.
319///
320/// # Strategies
321///
322/// - **Incremental**: New records added (e.g., transaction logs)
323/// - **`AccumulatingSnapshot`**: Records updated with new events (e.g., order milestones)
324/// - **`PeriodicSnapshot`**: Complete snapshot at regular intervals (e.g., daily inventory)
325#[non_exhaustive]
326#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize, Default)]
327pub enum AggregationStrategy {
328    /// New records are appended (e.g., transaction logs, event streams)
329    #[serde(rename = "incremental")]
330    #[default]
331    Incremental,
332
333    /// Records are updated with new events (e.g., order status changes)
334    #[serde(rename = "accumulating_snapshot")]
335    AccumulatingSnapshot,
336
337    /// Complete snapshots at regular intervals (e.g., daily inventory levels)
338    #[serde(rename = "periodic_snapshot")]
339    PeriodicSnapshot,
340}
341
342/// Explicit fact table schema declaration
343///
344/// Allows users to explicitly declare fact table metadata instead of relying on
345/// auto-detection. Explicit declarations take precedence over auto-detected metadata.
346///
347/// # Example
348///
349/// ```json
350/// {
351///   "name": "tf_sales",
352///   "measures": ["amount", "quantity", "discount"],
353///   "dimensions": ["product_id", "region_id", "date_id"],
354///   "primary_key": "id",
355///   "metadata": {
356///     "aggregation_strategy": "incremental",
357///     "grain": ["date", "product", "region"]
358///   }
359/// }
360/// ```
361#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
362pub struct FactTableDeclaration {
363    /// Fact table name (e.g., "`tf_sales`")
364    pub name: String,
365
366    /// Measure column names (aggregatable numeric fields)
367    pub measures: Vec<String>,
368
369    /// Dimension column names or paths within JSONB
370    pub dimensions: Vec<String>,
371
372    /// Primary key column name
373    pub primary_key: String,
374
375    /// Optional metadata about the fact table
376    pub metadata: Option<FactTableDeclarationMetadata>,
377}
378
379/// Metadata for explicitly declared fact tables
380#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
381pub struct FactTableDeclarationMetadata {
382    /// Aggregation strategy (how data is updated)
383    #[serde(default)]
384    pub aggregation_strategy: AggregationStrategy,
385
386    /// Grain of the fact table (combination of dimensions that makes a unique record)
387    pub grain: Vec<String>,
388
389    /// Column containing snapshot date (for periodic snapshots)
390    pub snapshot_date_column: Option<String>,
391
392    /// Whether this is a slowly changing dimension
393    #[serde(default)]
394    pub is_slowly_changing_dimension: bool,
395}
396
397impl SqlType {
398    /// Parse SQL type from string (database-specific)
399    #[must_use]
400    pub fn from_str_postgres(type_name: &str) -> Self {
401        match type_name.to_lowercase().as_str() {
402            "smallint" | "int" | "integer" | "int2" | "int4" => Self::Int,
403            "bigint" | "int8" => Self::BigInt,
404            "decimal" | "numeric" => Self::Decimal,
405            "real" | "float" | "double precision" | "float4" | "float8" => Self::Float,
406            "jsonb" => Self::Jsonb,
407            "json" => Self::Json,
408            "text" | "varchar" | "character varying" | "char" | "character" => Self::Text,
409            "uuid" => Self::Uuid,
410            "timestamp"
411            | "timestamptz"
412            | "timestamp with time zone"
413            | "timestamp without time zone" => Self::Timestamp,
414            "date" => Self::Date,
415            "boolean" | "bool" => Self::Boolean,
416            "ltree" => Self::Ltree,
417            other => Self::Other(other.to_string()),
418        }
419    }
420
421    /// Parse SQL type from string (MySQL)
422    #[must_use]
423    pub fn from_str_mysql(type_name: &str) -> Self {
424        match type_name.to_lowercase().as_str() {
425            "tinyint" | "smallint" | "mediumint" | "int" | "integer" => Self::Int,
426            "bigint" => Self::BigInt,
427            "decimal" | "numeric" => Self::Decimal,
428            "float" | "double" | "real" => Self::Float,
429            "json" => Self::Json,
430            "text" | "varchar" | "char" | "tinytext" | "mediumtext" | "longtext" => Self::Text,
431            "timestamp" | "datetime" => Self::Timestamp,
432            "date" => Self::Date,
433            "boolean" | "bool" | "tinyint(1)" => Self::Boolean,
434            other => Self::Other(other.to_string()),
435        }
436    }
437
438    /// Parse SQL type from string (SQLite)
439    #[must_use]
440    pub fn from_str_sqlite(type_name: &str) -> Self {
441        match type_name.to_lowercase().as_str() {
442            "integer" | "int" => Self::BigInt, // SQLite INTEGER is 64-bit
443            "real" | "double" | "float" => Self::Float,
444            "numeric" | "decimal" => Self::Decimal,
445            "text" | "varchar" | "char" => Self::Text,
446            "blob" => Self::Other("BLOB".to_string()),
447            other => Self::Other(other.to_string()),
448        }
449    }
450
451    /// Parse SQL type from string (SQL Server)
452    #[must_use]
453    pub fn from_str_sqlserver(type_name: &str) -> Self {
454        match type_name.to_lowercase().as_str() {
455            "tinyint" | "smallint" | "int" => Self::Int,
456            "bigint" => Self::BigInt,
457            "decimal" | "numeric" | "money" | "smallmoney" => Self::Decimal,
458            "float" | "real" => Self::Float,
459            "nvarchar" | "varchar" | "char" | "nchar" | "text" | "ntext" => Self::Text,
460            "uniqueidentifier" => Self::Uuid,
461            "datetime" | "datetime2" | "smalldatetime" | "datetimeoffset" => Self::Timestamp,
462            "date" => Self::Date,
463            "bit" => Self::Boolean,
464            other => Self::Other(other.to_string()),
465        }
466    }
467}
fraiseql_core/compiler/fact_table/mod.rs

fraiseql_core/compiler/fact_table/
mod.rs
