# Moose / Olap / Supported Types Documentation – TypeScript

## Included Files
1. moose/olap/supported-types/supported-types.mdx

## Supported Column Types

Source: moose/olap/supported-types/supported-types.mdx

Complete guide to defining columns for ClickHouse tables in Moose

# Supported Column Types

Moose supports a comprehensive set of ClickHouse column types across both TypeScript and Python libraries. This guide covers all supported types, their syntax, and best practices for defining table schemas.

## Basic Types

### String Types

```typescript
interface User {
  string: string;                             // String
  lowCardinality: string & LowCardinality;    // LowCardinality(String)
  uuid: string & tags.Format<"uuid">;         // UUID (with typia tags)
}
```
| ClickHouse Type | TypeScript | Description |
|------|------------|--------|
| `String` | `string` | Variable-length string |
| `LowCardinality(String)` | `string & LowCardinality` | Optimized for repeated values |
| `UUID` | `string & tags.Format<"uuid">` | UUID format strings |

### Numeric Types

### Integer Types

```typescript

interface Metrics {
  user_id: Int32;           // Int32
  count: UInt64;            // UInt64
  small_value: Int8;        // Int8
}

// Alternative: You can still use the verbose syntax if preferred
interface MetricsVerbose {
  user_id: number & ClickHouseInt<"int32">;
  count: number & ClickHouseInt<"uint64">;
  small_value: number & ClickHouseInt<"int8">;
}
```

| ClickHouse Type | TypeScript (New Helper) | TypeScript (Verbose) | Description |
|------|------------|------------|--------|
| `Int8` | `Int8` | `number & ClickHouseInt<"int8">` | -128 to 127 |
| `Int16` | `Int16` | `number & ClickHouseInt<"int16">` | -32,768 to 32,767 |
| `Int32` | `Int32` | `number & ClickHouseInt<"int32">` | -2,147,483,648 to 2,147,483,647 |
| `Int64` | `Int64` | `number & ClickHouseInt<"int64">` | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
| `UInt8` | `UInt8` | `number & ClickHouseInt<"uint8">` | 0 to 255 |
| `UInt16` | `UInt16` | `number & ClickHouseInt<"uint16">` | 0 to 65,535 |
| `UInt32` | `UInt32` | `number & ClickHouseInt<"uint32">` | 0 to 4,294,967,295 |
| `UInt64` | `UInt64` | `number & ClickHouseInt<"uint64">` | 0 to 18,446,744,073,709,551,615 |

### Floating Point Types

```typescript

interface SensorData {
  temperature: Float32;                     // Float32
  humidity: Float64;                        // Float64
  pressure: number;                         // Default Float64
}

// Alternative: You can still use the verbose syntax if preferred
interface SensorDataVerbose {
  temperature: number & tags.Type<"float">; // Float32
  humidity: number;                         // Float64
}
```
| ClickHouse Type | TypeScript (New Helper) | TypeScript (Verbose) | Description |
|-----------------|------------|------------|---------------------|
| `Float32`       | `Float32` | `number & tags.Type<"float">` | 32-bit floating point |
| `Float64`       | `Float64` or `number` | `number` | 64-bit floating point (default) |

### Decimal Types

```typescript

interface FinancialData {
  amount: Decimal<10, 2>;                     // Decimal(10,2)
  rate: Decimal<5, 4>;                        // Decimal(5,4)
  fee: Decimal<8, 3>;                         // Decimal(8,3)
}

// Alternative: You can still use the verbose syntax if preferred
interface FinancialDataVerbose {
  amount: string & ClickHouseDecimal<10, 2>;  // Decimal(10,2)
  rate: string & ClickHouseDecimal<5, 4>;     // Decimal(5,4)
}
```

| ClickHouse Type | TypeScript (New Helper) | TypeScript (Verbose) | Description |
|------|------------|------------|---------------------|
| `Decimal(P,S)` | `Decimal<P,S>` | `string & ClickHouseDecimal<P,S>` | Fixed-point decimal with P total digits, S decimal places |

### Boolean Type

```typescript
interface User {
  is_active: boolean;
  verified: boolean;
}
```
| ClickHouse Type | TypeScript | Description |
|------|------------|--------|
| `Boolean` | `boolean` | `boolean` |

### Date and Time Types

```typescript

interface Event {
  created_at: DateTime;                               // DateTime
  updated_at: DateTime64<3>;                         // DateTime(3)
  logged_at: DateTime64<6>;                          // DateTime(6) - microsecond precision
  birth_date: Date;                                   // Date
}

// Alternative: You can still use the verbose syntax if preferred
interface EventVerbose {
  created_at: Date;                                   // DateTime
  updated_at: Date & ClickHousePrecision<3>;         // DateTime(3)
  birth_date: Date;                                   // Date
}
```

| ClickHouse Type | TypeScript (New Helper) | TypeScript (Verbose) | Description |
|-----------------|------------|------------|-------------|
| `Date`          | `Date`     | `Date`     | Date only   |
| `Date16`        | `Date`     | `Date`     | Compact date format |
| `DateTime`      | `DateTime` | `Date`     | Date and time |
| `DateTime(P)`   | `DateTime64` | `Date & ClickHousePrecision` | DateTime with precision (P=0-9) |

### Network Types

```typescript

interface NetworkEvent {
  source_ip: string & tags.Format<"ipv4">;
  dest_ip: string & tags.Format<"ipv6">;
}
```
| ClickHouse Type | TypeScript | Description |
|------|------------|--------|
| `IPv4` | `string & tags.Format<"ipv4">` | IPv4 addresses |
| `IPv6` | `string & tags.Format<"ipv6">` | IPv6 addresses |

## Complex Types

### Geometry Types

Moose supports ClickHouse geometry types. Use the helpers in each language to get type-safe models and correct ClickHouse mappings.

```typescript
  ClickHousePoint,
  ClickHouseRing,
  ClickHouseLineString,
  ClickHouseMultiLineString,
  ClickHousePolygon,
  ClickHouseMultiPolygon,
} from "@514labs/moose-lib";

interface GeoTypes {
  point: ClickHousePoint;                 // Point → [number, number]
  ring: ClickHouseRing;                   // Ring → Array<[number, number]>
  lineString: ClickHouseLineString;       // LineString → Array<[number, number]>
  multiLineString: ClickHouseMultiLineString; // MultiLineString → Array<Array<[number, number]>>
  polygon: ClickHousePolygon;             // Polygon → Array<Array<[number, number]>>
  multiPolygon: ClickHouseMultiPolygon;   // MultiPolygon → Array<Array<Array<[number, number]>>>
}
```

| ClickHouse Type | TypeScript |
|------|------------|
| `Point` | `ClickHousePoint` |
| `Ring` | `ClickHouseRing` |
| `LineString` | `ClickHouseLineString` |
| `MultiLineString` | `ClickHouseMultiLineString` |
| `Polygon` | `ClickHousePolygon` |
| `MultiPolygon` | `ClickHouseMultiPolygon` |

Geometry coordinates are represented as numeric pairs `[x, y]` (TypeScript) or `tuple[float, float]` (Python).

### Array Types

Arrays are supported for all basic types and some complex types.

```typescript
interface User {
  tags: string[];           // Array(String)
  scores: number[];         // Array(Float64)
  metadata: Record<string, any>[];  // Array(Json)
  tuple: {
    name: string;
    age: number;
  } & ClickHouseNamedTuple[]; // Array(Tuple(String, Int32))
}
```

### Map Types

Maps store key-value pairs with specified key and value types.

```typescript
interface User {
  preferences: Record<string, string>;     // Map(String, String)
  metrics: Record<string, number>;         // Map(String, Float64)
}
```

### Nested Types

Nested types allow embedding complex objects within tables.

```typescript
interface Address {
  street: string;
  city: string;
  zip: string;
}

interface User {
  name: string;
  address: Address;  // Nested type
}
```

### Named Tuple Types

Named tuples provide structured data with named fields.

```typescript

interface Point {
  x: number;
  y: number;
}

interface Shape {
  center: Point & ClickHouseNamedTuple;  // Named tuple
  radius: number;
}
```

### Enum Types

Enums map to ClickHouse enums with string or integer values.

```typescript
enum UserRole {
  ADMIN = "admin",
  USER = "user",
  GUEST = "guest"
}

interface User {
  role: UserRole;  // Enum with string values
}
```

## Special Types

### JSON Type

The `Json` type stores arbitrary JSON data with optional schema configuration for performance and type safety.

#### Basic JSON (Unstructured)

For completely dynamic JSON data without any schema:

```typescript
interface Event {
  metadata: Record<string, any>;  // Basic JSON - accepts any structure
  config: any;                    // Basic JSON - fully dynamic
}
```

#### Rich JSON with Type Configuration

For better performance and validation, you can define typed fields within your JSON using `ClickHouseJson`. This creates a ClickHouse `JSON` column with explicit type hints for specific paths.

```typescript

// Define the structure for your JSON payload
interface PayloadStructure {
  name: string;
  count: number;
  timestamp?: Date;
}

interface Event {
  id: string;
  // JSON with typed paths - better performance, allows extra fields
  payload: PayloadStructure & ClickHouseJson;

  // JSON with performance tuning options
  metadata: PayloadStructure & ClickHouseJson<
    256,      // max_dynamic_paths: limit tracked paths (default: no limit)
    16,       // max_dynamic_types: limit type variations (default: no limit)
    ["skip.me"],           // skip_paths: exclude specific paths
    ["^tmp\\."]            // skip_regexps: exclude paths matching regex
  >;
}
```

#### Configuration Options

| Option | Type | Description |
|--------|------|-------------|
| `max_dynamic_paths` | `number` | Maximum number of unique JSON paths to track. Helps control memory usage for highly variable JSON structures. |
| `max_dynamic_types` | `number` | Maximum number of type variations allowed per path. Useful when paths may contain different types. |
| `skip_paths` | `string[]` | Array of exact JSON paths to ignore during ingestion (e.g., `["temp", "debug.info"]`). |
| `skip_regexps` | `string[]` | Array of regex patterns for paths to exclude (e.g., `["^tmp\\.", ".*_internal$"]`). |

#### Benefits of Typed JSON

1. **Better Performance**: ClickHouse can optimize storage and queries for known paths
2. **Type Safety**: Validates that specified paths match expected types
3. **Flexible Schema**: Allows additional fields beyond typed paths
4. **Memory Control**: Configure limits to prevent unbounded resource usage

- **Basic JSON** (`any`, `Dict[str, Any]`): Use when JSON structure is completely unknown or rarely queried
- **Rich JSON** (`ClickHouseJson`): Use when you have known fields that need indexing/querying, but want to allow additional dynamic fields

#### Example: Product Event Tracking

```typescript

interface ProductProperties {
  category: string;
  price: number;
  inStock: boolean;
}

interface ProductEvent {
  eventId: Key<string>;
  timestamp: DateTime;
  // Typed paths for common fields, but allows custom properties
  properties: ProductProperties & ClickHouseJson<
    128,                    // Track up to 128 unique paths
    8,                      // Allow up to 8 type variations per path
    ["_internal"],          // Ignore internal fields
    ["^debug_"]             // Ignore debug fields
  >;
}
```

With this schema, you can send events like:
```json
{
  "eventId": "evt_123",
  "timestamp": "2025-10-22T12:00:00Z",
  "properties": {
    "category": "electronics",     // Typed field ✓
    "price": 99.99,                // Typed field ✓
    "inStock": true,               // Typed field ✓
    "customTag": "holiday-sale",   // Extra field - accepted ✓
    "brandId": 42,                 // Extra field - accepted ✓
    "_internal": "ignored"         // Skipped by skip_paths ✓
  }
}
```

### Nullable Types

All types support nullable variants using optional types.

```typescript
interface User {
  name: string;           // Required
  email?: string;         // Nullable
  age?: number;           // Nullable
}
```

If a field is optional in your app model but you provide a ClickHouse default, Moose infers a non-nullable ClickHouse column with a DEFAULT clause.

  - Optional without default (e.g., `field?: number`) → ClickHouse Nullable type.
  - Optional with default (e.g., `field?: number & ClickHouseDefault<"18">` or `WithDefault<number, "18">`) → non-nullable column with default `18`.

This lets you keep optional fields at the application layer while avoiding Nullable columns in ClickHouse when a server-side default exists.

### SimpleAggregateFunction

`SimpleAggregateFunction` is designed for use with `AggregatingMergeTree` tables. It stores pre-aggregated values that are automatically merged when ClickHouse combines rows with the same primary key.

```typescript

interface DailyStats {
  date: DateTime;
  userId: string;
  totalViews: number & SimpleAggregated<"sum", number>;
  maxScore: number & SimpleAggregated<"max", number>;
  lastSeen: DateTime & SimpleAggregated<"anyLast", DateTime>;
}

const statsTable = new OlapTable("daily_stats", {
  engine: ClickHouseEngines.AggregatingMergeTree,
  orderByFields: ["date", "userId"],
});
```

See [ClickHouse docs](https://clickhouse.com/docs/en/sql-reference/data-types/simpleaggregatefunction) for the complete list of functions.

## Table Engines

Moose supports all common ClickHouse table engines:

| Engine | Python | Description |
|--------|------------|-------------|
| `MergeTree` | `ClickHouseEngines.MergeTree` | Default engine |
| `ReplacingMergeTree` | `ClickHouseEngines.ReplacingMergeTree` | Deduplication |
| `SummingMergeTree` | `ClickHouseEngines.SummingMergeTree` | Aggregates numeric columns |
| `AggregatingMergeTree` | `ClickHouseEngines.AggregatingMergeTree` | Advanced aggregation |
| `ReplicatedMergeTree` | `ClickHouseEngines.ReplicatedMergeTree` | Replicated version of MergeTree |
| `ReplicatedReplacingMergeTree` | `ClickHouseEngines.ReplicatedReplacingMergeTree` | Replicated with deduplication |
| `ReplicatedSummingMergeTree` | `ClickHouseEngines.ReplicatedSummingMergeTree` | Replicated with aggregation |
| `ReplicatedAggregatingMergeTree` | `ClickHouseEngines.ReplicatedAggregatingMergeTree` | Replicated with advanced aggregation |

```typescript

const userTable = new OlapTable("users", {
  engine: ClickHouseEngines.ReplacingMergeTree,
  orderByFields: ["id", "updated_at"]
});
```

## Best Practices

### Type Selection

- **Use specific integer types** when you know the value ranges to save storage
- **Prefer `Float64`** for most floating-point calculations unless storage is critical
- **Use `LowCardinality`** for string columns with repeated values
- **Choose appropriate DateTime precision** based on your accuracy needs

### Performance Considerations

- **Order columns by cardinality** (low to high) for better compression
- **Use `ReplacingMergeTree`** for tables with frequent updates
- **Specify `orderByFields` or `orderByExpression`** for optimal query performance
- **Consider `LowCardinality`** for string columns with < 10,000 unique values