; // From Foo.primaryKey
utcTimestamp: DateTime; // From Foo.timestamp
hasText: boolean; // From Foo.optionalText?
textLength: number; // From Foo.optionalText.length
newField?: string; // Add this new optional field
}
```
3. Save the file and watch your terminal
**Switch to Terminal 1** (where `moose dev` is running). You should see Moose automatically update your infrastructure:
```txt
⠋ Processing Infrastructure changes from file watcher
~ Table Bar:
Column changes:
+ newField: String
```
You should see the column change logged. Your API, database schema, and streaming topic all updated automatically!
**Try it yourself:** Add another field with a different data type and watch the infrastructure update in real-time.
## Recap
You've built a complete analytical backend with:
## Need Help?
**Docker not running:**
```bash filename="Terminal" copy
# macOS
open -a Docker
# Linux
sudo systemctl start docker
# Verify Docker is running
docker ps
```
**Docker out of space:**
```bash filename="Terminal" copy
docker system prune -a
```
**Node.js version too old:**
```bash filename="Terminal" copy
# Check version
node -v
# Install Node 20+ with nvm
curl -o- https://raw.githubusercontent.com/nvm-sh/nvm/v0.39.0/install.sh | bash
nvm install 20
nvm use 20
```
**Port 4000 already in use:**
```bash filename="Terminal" copy
# Find what's using port 4000
lsof -i :4000
# Kill the process (replace PID)
kill -9
# Or use a different port
moose dev --port 4001
```
**Permission denied:**
```bash filename="Terminal" copy
# Fix Docker permissions (Linux)
sudo usermod -aG docker $USER
newgrp docker
# Fix file permissions
chmod +x ~/.moose/bin/moose
```
**Port 4000 already in use:**
```bash filename="Terminal" copy
# Find what's using port 4000
lsof -i :4000
# Kill the process (replace PID)
kill -9
# Or use a different port
moose dev --port 4001
```
**Permission denied:**
```bash filename="Terminal" copy
# Fix Docker permissions (Linux)
sudo usermod -aG docker $USER
newgrp docker
# Fix file permissions
chmod +x ~/.moose/bin/moose
```
**Still stuck?** Join our [Slack community](https://join.slack.com/t/moose-community/shared_invite/zt-2fjh5n3wz-cnOmM9Xe9DYAgQrNu8xKxg) or [open an issue](https://github.com/514-labs/moose/issues).
---
## Minimum Requirements
Source: moose/help/minimum-requirements.mdx
Minimum Requirements for Moose
## Development Setup
The development setup has higher requirements because Moose runs locally along with all its dependencies (Redpanda, ClickHouse, Temporal, Redis).
- **CPU:** 12 cores
- **Memory:** 18GB
- **Disk:** >500GB SSD
- **OS:**
- Windows with Linux subsystem (Ubuntu preferred)
- Linux (Debian 10+, Ubuntu 18.10+, Fedora 29+, CentOS/RHEL 8+)
- Mac OS 13+
- **Runtime:** Python 3.12+ or Node.js 20+, Docker 24.0.0+, and Docker Compose 2.23.1+
## Production Setup
The production setup has lower requirements, as external components (Redpanda, ClickHouse, Redis, and Temporal) are assumed to be deployed separately.
- **CPU:** 1vCPU
- **Memory:** 6GB
- **Disk:** >30GB SSD
- **OS:**
- Windows with Linux subsystem (Ubuntu preferred)
- Linux (Debian 10+, Ubuntu 18.10+, Fedora 29+, CentOS/RHEL 8+)
- Mac OS 13+
- **Runtime:** Python 3.12+ or Node.js 20+
---
## Troubleshooting
Source: moose/help/troubleshooting.mdx
Troubleshooting for Moose
# Troubleshooting
Common issues and their solutions when working with Moose.
## Development Environment
### Issue: `moose dev` fails to start
**Possible causes and solutions:**
1. **Port conflicts**
- Check if ports 4000-4002 are already in use
- Solution: Kill the conflicting processes or configure different ports
```bash
# Find processes using ports
lsof -i :4000-4002
# Kill process by PID
kill
2. **Missing dependencies**
- Solution: Ensure all dependencies are installed
```bash
npm install
```
3. **Docker not running**
- Solution: Start Docker Desktop or Docker daemon
```bash
# Check Docker status
docker info
# Start Docker on Linux
sudo systemctl start docker
```
## Data Ingestion
### Issue: Data not appearing in tables
1. **Validation errors**
- Check logs for validation failures
- Solution: Ensure data matches schema
```bash filename="Terminal" copy
moose logs
```
2. **Stream processing errors**
- Solution: Check transform functions for errors
```bash filename="Terminal" copy
moose logs --filter functions
```
3. **Database connectivity**
- Solution: Verify database credentials in `.moose/config.toml`
```toml filename=".moose/config.toml" copy
[clickhouse_config]
db_name = "local"
user = "panda"
password = "pandapass"
use_ssl = false
host = "localhost"
host_port = 18123
native_port = 9000
```
## Stream Processing
### Issue: High processing latency
1. **Insufficient parallelism**
- Solution: Increase stream parallelism
```typescript
const stream = new Stream("high_volume", {
parallelism: 8 // Increase from default
});
```
### Issue: Data transformations not working
1. **Transform function errors**
- Solution: Debug transformation logic
```typescript
// Add logging to transform
stream.addTransform(outputStream, (record) => {
console.log('Processing record:', record.id);
try {
// Your transformation logic
return transformedRecord;
} catch (error) {
console.error('Transform error:', error);
return undefined; // Skip record on error
}
});
```
## Database Issues
### Issue: Slow queries
1. **Missing or improper indexes**
- Solution: Check orderByFields configuration
```typescript
const table = new OlapTable("slow_table", {
orderByFields: ["frequently_queried_field", "timestamp"]
});
```
2. **Large result sets**
- Solution: Add limits and pagination
```typescript
// In query API
const results = await client.query.execute(sql`
SELECT * FROM large_table
WHERE category = 'example'
LIMIT 100
`);
```
## Deployment Issues
### Issue: Deployment fails
1. **Configuration errors**
- Solution: Check deployment configuration
```bash
# Validate configuration
moose validate --config
```
2. **Resource limitations**
- Solution: Increase resource allocation
```yaml
# In kubernetes manifest
resources:
requests:
memory: "1Gi"
cpu: "500m"
limits:
memory: "2Gi"
cpu: "1000m"
```
3. **Permission issues**
- Solution: Verify service account permissions
```bash
# Check permissions
moose auth check
```
### Issue: Migration stuck with "Migration already in progress"
**Cause:** A previous migration was interrupted without releasing its lock.
**Solution:**
1. **Wait 5 minutes** - locks expire automatically
2. **Or manually clear the lock:**
```sql
DELETE FROM _MOOSE_STATE WHERE key = 'migration_lock';
```
3. **Verify it worked:**
```sql
SELECT * FROM _MOOSE_STATE WHERE key = 'migration_lock';
-- Should return no rows
```
The `_MOOSE_STATE` table uses ClickHouse's KeeperMap engine for distributed locking, ensuring only one migration runs at a time across multiple deployments.
## Getting Help
If you can't resolve an issue:
1. Ask for help on the [Moose community Slack channel](https://join.slack.com/t/moose-community/shared_invite/zt-2fjh5n3wz-cnOmM9Xe9DYAgQrNu8xKxg)
2. Search existing [GitHub issues](https://github.com/514-labs/moose/issues)
3. Open a new issue with:
- Moose version (`moose --version`)
- Error messages and logs
- Steps to reproduce
- Expected vs. actual behavior
---
## moose/in-your-stack
Source: moose/in-your-stack.mdx
# Moose In Your Dev Stack
Moose handles the analytical layer of your application stack. The [Area Code](https://github.com/514-labs/area-code) repository contains two working implementations that show how to integrate Moose with existing applications.
## User Facing Analytics (UFA)
UFA shows how to add a dedicated analytics microservice to an existing application without impacting your primary database.
View the open source repository to see the full implementation and clone it on your own machine.
### Data Flow
1. Application writes to Supabase (transactional backend)
2. Supabase Realtime streams changes to Analytical Backend and Retrieval Backend
3. Moose ingest pipeline syncs change events from Redpanda into ClickHouse
4. Frontend queries analytics APIs for dashboards
### Architecture Components
The UFA template demonstrates a microservices architecture with specialized components for different data access patterns:
The user interface for dashboards and application interactions
Technologies: [Vite](https://vite.dev), [React](https://react.dev), [TanStack Query](https://tanstack.com/query), [TanStack Router](https://tanstack.com/router), [Tailwind CSS](https://tailwindcss.com)
Handles CRUD operations and maintains application state
Technologies: [Supabase](https://supabase.com), [Fastify](https://fastify.dev), [Drizzle ORM](https://orm.drizzle.team/)
Fast text search and complex queries across large datasets
Technologies: [Elasticsearch](https://www.elastic.co/) + [Fastify](https://fastify.dev)
High-performance analytical queries and aggregations
Technologies: [ClickHouse](https://clickhouse.com/) + [Moose OLAP](/moose/olap), [Redpanda](https://redpanda.com/) + [Moose Streaming](/moose/streaming), [Moose APIs](/moose/apis)
Keep data synchronized between transactional, retrieval, and analytics systems
Technologies: [Supabase Realtime](https://supabase.com/docs/guides/realtime), [Temporal](https://temporal.io/) + [Moose Workflows](/moose/workflows)
## Operational Data Warehouse (ODW)
ODW shows how to build a centralized data platform that ingests from multiple sources for business intelligence and reporting.
View the open source repository to see the full implementation and clone it on your own machine.
### Data Flow
1. Sources send data to Moose ingestion endpoints
2. Streaming functions validate and transform data
3. Data lands in ClickHouse tables
4. BI tools query via generated APIs or direct SQL
### Architecture Components
Handles incoming data from push-based sources (webhooks, application logs) with validation and transformation
Technologies: [Moose APIs](/moose/apis), [Redpanda](https://redpanda.com/) + [Moose Streaming](/moose/streaming)
Connects to your existing databases, object storage, or third-party APIs
Technologies: [Temporal](https://temporal.io/) + [Moose Workflows](/moose/workflows)
Centralized analytical database for raw and transformed data
Technologies: [ClickHouse](https://clickhouse.com/) + [Moose OLAP](/moose/olap)
Query interface for business intelligence and reporting
Technologies: [Streamlit](https://streamlit.io/) dashboards, [Moose APIs](/moose/apis), [ClickHouse Connect](https://clickhouse.com/docs/en/interfaces/http/connect)
---
## Overview
Source: moose/index.mdx
Modular toolkit for building real-time analytical backends
# MooseStack
Type-safe, code-first tooling for building real-time analytical backends--OLAP Databases, Data Streaming, ETL Workflows, Query APIs, and more.
## Get Started
```bash filename="Install Moose" copy
bash -i <(curl -fsSL https://fiveonefour.com/install.sh) moose
```
## Everything as Code
Declare all infrastructure (e.g. ClickHouse tables, Redpanda streams, APIs, etc.) and pipelines in pure TypeScript or Python. Your code auto-wires everything together, so no integration boilerplate needed.
```ts filename="Complete Analytical Backend in 1 TS file" copy
interface DataModel {
primaryKey: Key;
name: string;
}
// Create a ClickHouse table
);
// Create an ingest API endpoint
);
// Create analytics API endpoint
interface QueryParams {
limit?: number;
}
: QueryParams, {client, sql}) => {
const result = await client.query.execute(sql`SELECT * FROM ${clickhouseTable} LIMIT ${limit}`);
return await result.json();
}
);
```
```python filename="Complete Analytical Backend in 1 Python file" copy
from moose_lib import Key, OlapTable, Stream, StreamConfig, IngestApi, IngestApiConfig, Api
from pydantic import BaseModel
class DataModel(BaseModel):
primary_key: Key[str]
name: str
# Create a ClickHouse table
clickhouse_table = OlapTable[DataModel]("TableName")
# Create a Redpanda streaming topic
redpanda_topic = Stream[DataModel]("TopicName", StreamConfig(
destination=clickhouse_table,
))
# Create an ingest API endpoint
ingest_api = IngestApi[DataModel]("post-api-route", IngestConfig(
destination=redpanda_topic,
))
# Create a analytics API endpoint
class QueryParams(BaseModel):
limit: int = 10
def handler(client, params: QueryParams):
return client.query.execute("SELECT * FROM {table: Identifier} LIMIT {limit: Int32}", {
"table": clickhouse_table.name,
"limit": params.limit,
})
analytics_api = Api[RequestParams, DataModel]("get-api-route", query_function=handler)
```
## Core Concepts
```ts
interface Event {
id: Key;
name: string;
createdAt: Date;
}
interface AggregatedEvent {
count: number;
name: string;
}
```
```bash
# Start local dev server
moose dev
⡏ Starting local infrastructure
Successfully started containers
Validated clickhousedb-1 docker container
Validated redpanda-1 docker container
Successfully validated red panda cluster
Validated temporal docker container
Successfully ran local infrastructure
```
## Modules
```ts
const table = new OlapTable("events");
const mv = new MaterializedView({
selectStatement: sql`
SELECT count(*) as count, name
FROM ${table}
GROUP BY name
`,
selectTables: [table],
tableName: "events",
materializedViewName: "aggregated_events"
});
```
```ts
const stream = new Stream("events", {
destination: table,
});
stream.addConsumer((event) => {
console.log(event);
});
```
```ts
const etl = new Workflow("my_etl", {
startingTask: startEtl,
schedule: "@every 1h",
retries: 3,
});
```
```ts
const postEvent = new IngestApi("post-event", {
destination: stream,
});
const getEvents = new Api("get-events", {
async handler({limit = 10}, {client, sql}) {
// query database and return results
return await client.query.execute(sql`
SELECT * FROM events LIMIT ${limit}
`);
}
});
```
Each module is independent and can be used on its own. You can start with one capability and incrementally adopt more over time.
## Tooling
```bash
# Build for production
moose build
```
```bash
# Create a plan
moose plan
# Example plan output:
~ Table events with column changes: [
Added(
Column {
name: "status",
data_type: String,
required: true,
unique: false,
primary_key: false,
default: None
})]
and order by changes: OrderByChange {
before: [], after: []
}
```
## Technology Partners
- [ClickHouse](https://clickhouse.com/) (Online Analytical Processing (OLAP) Database)
- [Redpanda](https://redpanda.com/) (Streaming)
- [Temporal](https://temporal.io/) (Workflow Orchestration)
- [Redis](https://redis.io/) (Internal State Management)
Want this managed in production for you? Check out Boreal Cloud (from the makers of the Moose Stack).
---
## LLM-Optimized Documentation
Source: moose/llm-docs.mdx
Language-scoped documentation feeds for AI assistants
# LLM-Optimized Documentation
Moose now publishes lightweight documentation bundles so AI assistants can reason about your project without scraping the entire site. Each docs page includes **LLM View** links for TypeScript and Python, and the CLI exposes HTTP endpoints that deliver pre-compiled reference text.
## Quick links
- TypeScript bundle: `/llm-ts.txt`
- Python bundle: `/llm-py.txt`
- Scoped bundle: append `?path=relative/docs/section` to either endpoint to fetch a specific subsection
You can open these URLs in a browser, pipe them into tooling, or share them with agents such as Claude, Cursor, and Windsurf.
```bash filename="Terminal"
# Fetch the TypeScript bundle for the OLAP docs from the hosted site
curl "https://docs.fiveonefour.com/llm-ts.txt?path=moose/olap/model-table"
```
For project-specific knowledge, combine these static bundles with live context from the [MCP server](/moose/mcp-dev-server).
---
## Development Mode
Source: moose/local-dev.mdx
Local development environment with hot reload and automatic infrastructure management
# Setting Up Your Development Environment
Development mode (`moose dev`) provides a full-featured local environment optimized for rapid iteration and debugging. It automatically manages Docker containers, provides hot reload capabilities, and includes enhanced debugging features.
## Getting Started
```bash
# Start development environment
moose dev
# View your running infrastructure
moose ls
```
## Container Management
Development mode automatically manages Docker containers for your infrastructure:
- **ClickHouse** (when `olap` feature is enabled)
- **Redpanda** (when `streaming_engine` feature is enabled)
- **Temporal** (when `workflows` feature is enabled)
- **Analytics APIs Server** (when `apis` feature is enabled)
- **Redis** (always enabled)
- **MCP Server** (always enabled) - Enables AI-assisted development. [Learn more](/moose/mcp-dev-server)
### Container Configuration
Control which containers start with feature flags:
```toml copy
# moose.config.toml
[features]
olap = true # Enables ClickHouse
streaming_engine = true # Enables Redpanda
workflows = false # Controls Temporal startup
apis = true # Enables Analytics APIs server
```
### Extending Docker Infrastructure
You can extend Moose's Docker Compose configuration with custom services by creating a `docker-compose.dev.override.yaml` file in your project root. This allows you to add additional infrastructure (databases, monitoring tools, etc.) that runs alongside your Moose development environment.
**Do not use docker-compose.dev.override.yaml to modify Moose-managed services** (ClickHouse, Redpanda, Redis, Temporal). The Docker Compose merge behavior makes it difficult to override existing configuration correctly, often leading to conflicts.
Instead, use `moose.config.toml` to configure Moose infrastructure. See [Configuration](/moose/configuration) for all available options including database connections, ports, volumes, and service-specific settings.
Use the override file **only for adding new services** that complement your Moose environment (e.g., PostgreSQL for application data, monitoring tools).
**How it works:**
When you run `moose dev`, Moose automatically detects and merges your override file with the generated Docker Compose configuration. The files are merged using Docker Compose's [standard merge behavior](https://docs.docker.com/compose/how-tos/multiple-compose-files/merge/).
**Example: Adding PostgreSQL for Application Data**
Create a `docker-compose.dev.override.yaml` file in your project root:
```yaml copy filename="docker-compose.dev.override.yaml"
services:
postgres:
image: postgres:16
environment:
POSTGRES_USER: myapp
POSTGRES_PASSWORD: mypassword
POSTGRES_DB: myapp_db
ports:
- "5432:5432"
volumes:
- postgres-data:/var/lib/postgresql/data
volumes:
postgres-data:
```
Now when you run `moose dev`, PostgreSQL will start alongside your other infrastructure. You'll see a message confirming the override file is being used:
```
[moose] Using docker-compose.dev.override.yaml for custom infrastructure
```
**Recommended Use Cases:**
- **Add databases**: PostgreSQL, MySQL, MongoDB for application data
- **Add monitoring**: Grafana, Prometheus for metrics visualization
- **Add custom services**: Additional message queues, caching layers, or development tools
**Not Recommended:**
- Modifying Moose-managed services (ClickHouse, Redpanda, Redis, Temporal)
- Overriding ports, volumes, or environment variables for Moose infrastructure
- Attempting to change database credentials or connection settings
For any Moose infrastructure configuration, use `moose.config.toml` instead. See [Configuration](/moose/configuration).
**Example: Adding Grafana for Monitoring**
```yaml copy filename="docker-compose.dev.override.yaml"
services:
grafana:
image: grafana/grafana:latest
ports:
- "3001:3000"
environment:
- GF_SECURITY_ADMIN_PASSWORD=admin
- GF_USERS_ALLOW_SIGN_UP=false
volumes:
- grafana-data:/var/lib/grafana
volumes:
grafana-data:
```
When merging files, Docker Compose follows these rules:
- **Services**: Merged by name with values from the override file taking precedence
- **Environment variables**: Appended (both files' values are used)
- **Volumes**: Appended
- **Ports**: Appended (use `!override` tag to replace instead of merge)
See [Docker's merge documentation](https://docs.docker.com/reference/compose-file/merge/) for complete details.
The override file is only used in development mode (`moose dev`). For production deployments, configure your infrastructure separately using your deployment platform's tools.
## Hot Reloading Development
The development runtime includes a file watcher that provides near-instantaneous feedback when you save code changes.
### Watched Files
The file watcher recursively monitors your entire `app/` directory structure and only rebuilds the components that actually changed.
Only the root file in your `app/` directory is run when changes are detected. In order for your tables/streams/apis/workflows to be detected, you must export them from your root file (`app/index.ts`). If you change a file in your app directory and it is a dependency of your root file, then those changes WILL be detected.
## Quick Example
**❌ Doesn't work - No export from root:**
```ts file="app/tables/users.ts"
const usersTable = new OlapTable("Users");
// Moose can't see this - not exported from root
```
**✅ Works - Export from root file:**
```ts file="app/tables/users.ts" {3}
export interface UserSchema {
id: string;
name: string;
email: string;
age: number; // Adding this triggers migration
}
```
*Moose detects this because `UserSchema` is imported in the root file via the dependency chain.*
Learn more about how Moose handles migrations.
## Script Execution Hooks
You can configure your dev server to run your own shell commands automatically during development. Use these hooks to keep generated artifacts in sync (e.g., refreshing external models, regenerating OpenAPI SDKs).
### Available hooks
- `on_first_start_script`: runs once when the dev server first starts in this process
- `on_reload_complete_script`: runs after each dev server reload when code/infra changes have been fully applied
Configure these in `moose.config.toml` under the `http_server_config` section:
```toml copy
# moose.config.toml
[http_server_config]
# One-time on first start
on_first_start_script = "echo 'dev started'"
# After every code/infra reload completes
on_reload_complete_script = "echo 'reload complete'"
```
Notes:
- Scripts run from your project root using your `$SHELL` (falls back to `/bin/sh`).
- Use `&&` to chain multiple commands or point to a custom script.
- Prefer passing credentials via environment variables or your secret manager.
### Use case: keep external models in sync (DB Pull)
Refresh `EXTERNALLY_MANAGED` table models from a remote ClickHouse on dev start so your local code matches the live schema.
```bash filename="Terminal" copy
export REMOTE_CLICKHOUSE_URL="https://username:password@host:8443/?database=default"
```
```toml copy
# moose.config.toml
[http_server_config]
on_first_start_script = "moose db pull --connection-string $REMOTE_CLICKHOUSE_URL"
```
See the full guide: [/moose/olap/db-pull](/moose/olap/db-pull)
### Use case: regenerate OpenAPI SDKs on reload
Automatically regenerate client SDKs after Moose finishes applying code/infra changes so `.moose/openapi.yaml` is fresh.
```toml copy
# moose.config.toml
[http_server_config]
on_first_start_script = "command -v openapi-generator-cli >/dev/null 2>&1 || npm i -g @openapitools/openapi-generator-cli"
on_reload_complete_script = "openapi-generator-cli generate -i .moose/openapi.yaml -g typescript-fetch -o ./generated/ts"
```
More examples: [/moose/apis/openapi-sdk](/moose/apis/openapi-sdk)
## Local Infrastructure
### Port Allocation
Development mode uses the following default ports:
- **4000**: Main API server
- **5001**: Management API (health checks, metrics, admin, OpenAPI docs)
### Service URLs
Access your development services at:
```bash
# Main application
http://localhost:4000
# Management interface
http://localhost:5001/metrics
# OpenAPI documentation
http://localhost:5001/openapi.yaml
```
### Container Networking
All containers run in an isolated Docker network with automatic service discovery:
- Containers communicate using service names
- Port mapping only for external access
- Automatic DNS resolution between services
### MCP Server for AI-Assisted Development
Development mode includes a built-in Model Context Protocol (MCP) server that lets AI assistants interact with your local infrastructure through natural language.
**What you can do:**
- Query your ClickHouse database with natural language
- Inspect streaming topics and messages
- Search and filter development logs
- Explore your infrastructure map
**Quick setup:**
The MCP server runs automatically at `http://localhost:4000/mcp`. For Claude Code, just run:
```bash copy
claude mcp add --transport http moose-dev http://localhost:4000/mcp
```
For other AI clients (Windsurf, VS Code, Cursor, Claude Desktop), see the [full setup guide](/moose/mcp-dev-server).
**Example prompts:**
- *"What errors are in the logs?"*
- *"What tables exist in my project?"*
- *"Show me the schema of all tables"*
- *"Sample 5 messages from the Foo stream"*
See the complete guide for all available tools, detailed configuration for each AI client, and example workflows.
## Troubleshooting
### Common Issues
**Container Startup Failures**
```bash
# Check Docker is running
docker info
# View container logs
moose logs
```
**Port Conflicts**
```bash
# Check what's using your ports
lsof -i :4000
lsof -i :5001
# Use custom ports
export MOOSE_HTTP_PORT=4040
export MOOSE_MANAGEMENT_PORT=5010
moose dev
```
---
## MCP Server
Source: moose/mcp-dev-server.mdx
Built-in Model Context Protocol server for AI-assisted development
# MCP Server for AI-Assisted Development
The Moose development server includes a built-in Model Context Protocol (MCP) server that enables AI agents and IDEs to interact directly with your local development infrastructure. This allows you to use natural language to query data, inspect logs, explore infrastructure, and debug your Moose project.
## What is MCP?
[Model Context Protocol (MCP)](https://modelcontextprotocol.io) is an open protocol that standardizes how AI assistants communicate with development tools and services. Moose's MCP server exposes your local development environment—including ClickHouse, Redpanda, logs, and infrastructure state—through a set of tools that AI agents can use.
## Quick Start
The MCP server runs automatically when you start development mode:
```bash
moose dev
```
The MCP server is available at: `http://localhost:4000/mcp`
The MCP server is enabled by default. To disable it, use `moose dev --mcp=false`.
## Configure Your AI Client
Connect your AI assistant to the Moose MCP server. Most clients now support native HTTP transport for easier setup.
**Setup**: Use the Claude Code CLI (easiest method)
```bash copy
claude mcp add --transport http moose-dev http://localhost:4000/mcp
```
That's it! Claude Code will automatically connect to your Moose dev server.
**Scope**: This command adds the MCP server to Claude Code's project configuration, making it available to your project when using Claude Code. Other AI clients (Cursor, Windsurf, etc.) require separate configuration - see the tabs below.
Make sure `moose dev` is running before adding the server. The CLI will verify the connection.
**Alternative**: Manual configuration at `~/.claude/config.json`
```json filename="config.json" copy
{
"mcpServers": {
"moose-dev": {
"transport": "http",
"url": "http://localhost:4000/mcp"
}
}
}
```
**Location**: `~/.codeium/windsurf/mcp_config.json`
Windsurf supports native Streamable HTTP transport:
```json filename="mcp_config.json" copy
{
"mcpServers": {
"moose-dev": {
"serverUrl": "http://localhost:4000/mcp"
}
}
}
```
**Prerequisites**:
- VS Code 1.102+ (built-in MCP support)
- Or install the [Cline extension](https://marketplace.visualstudio.com/items?itemName=saoudrizwan.claude-dev)
**Option 1: Native HTTP Support (VS Code 1.102+)**
Add to `.vscode/settings.json` or User Settings:
```json filename=".vscode/settings.json" copy
{
"mcp.servers": {
"moose-dev": {
"transport": "http",
"url": "http://localhost:4000/mcp"
}
}
}
```
**Option 2: Cline Extension**
Configure in Cline's MCP settings:
```json copy
{
"moose-dev": {
"transport": "sse",
"url": "http://localhost:4000/mcp"
}
}
```
**Location**: `.cursor/mcp.json` (project-level) or `~/.cursor/settings/mcp.json` (global)
Cursor currently uses stdio transport. Use `mcp-remote` to bridge to HTTP servers:
```json filename=".cursor/mcp.json" copy
{
"mcpServers": {
"moose-dev": {
"command": "npx",
"args": [
"-y",
"mcp-remote",
"http://localhost:4000/mcp"
]
}
}
}
```
**Location**: `~/Library/Application Support/Claude/claude_desktop_config.json`
Access via: Claude > Settings > Developer > Edit Config
```json filename="claude_desktop_config.json" copy
{
"servers": {
"moose-dev": {
"command": "npx",
"args": [
"-y",
"mcp-remote",
"http://localhost:4000/mcp"
]
}
}
}
```
The `-y` flag automatically installs `mcp-remote` if not already installed.
Make sure `moose dev` is running before using the MCP tools. The AI client will connect to `http://localhost:4000/mcp`.
## Available Tools
The Moose MCP server provides five tools for interacting with your local development environment:
### `get_logs`
Retrieve and filter Moose development server logs for debugging and monitoring.
**What you can ask for:**
- Filter by log level (ERROR, WARN, INFO, DEBUG, TRACE)
- Limit the number of log lines returned
- Search for specific text patterns in logs
**Example prompts:**
*"Show me the last 10 ERROR logs"*
```
Showing 10 most recent log entries from /Users/user/.moose/2025-10-10-cli.log
Filters applied:
- Level: ERROR
[2025-10-10T17:44:42Z ERROR] Foo -> Bar (worker 1): Unsupported SASL mechanism: undefined
[2025-10-10T17:44:43Z ERROR] FooDeadLetterQueue (consumer) (worker 1): Unsupported SASL mechanism
[2025-10-10T17:51:48Z ERROR] server error on API server (port 4000): connection closed
...
```
*"What WARN level logs do I have?"*
```
Showing 6 most recent log entries
Filters applied:
- Level: WARN
[2025-10-10T16:45:04Z WARN] HTTP client not configured - missing API_KEY
[2025-10-10T16:50:05Z WARN] HTTP client not configured - missing API_KEY
...
```
**Tip**: Combine filters for better results. For example: "Show me ERROR logs with 'ClickHouse' in them" combines level filtering with search.
**Use cases:**
- Debugging application errors
- Monitoring infrastructure health
- Tracking data processing issues
- Finding specific events or patterns
---
### `get_infra_map`
Retrieve and explore the infrastructure map showing all components in your Moose project.
**What you can ask for:**
- List specific component types (tables, topics, API endpoints, workflows, etc.)
- Get a complete overview of all infrastructure
- Search for components by name
- See detailed configuration or just a summary
**Example prompts:**
*"What tables exist in my project?"*
```
# Moose Infrastructure Map (Summary)
## Tables (28)
- MergeTreeTest
- ReplacingMergeTreeVersion
- Bar
- BasicTypes
- UserEvents_1_0
- UserEvents_2_0
- FooDeadLetter
- BarAggregated
- FooWorkflow
...
```
*"Give me an overview of my Moose infrastructure"*
```
# Moose Infrastructure Map (Summary)
## Topics (11)
- Bar, BasicTypes, Foo, FooDeadLetterQueue, SimpleArrays...
## API Endpoints (11)
- INGRESS_Foo (INGRESS -> topic: Foo)
- INGRESS_BasicTypes (INGRESS -> topic: BasicTypes)
- EGRESS_bar (EGRESS (4 params))
...
## Tables (28)
- MixedComplexTypes, Bar, UserEvents_1_0...
## Topic-to-Table Sync Processes (10)
- Bar_Bar, BasicTypes_BasicTypes...
## Function Processes (3)
- Foo__Bar_Foo_Bar, Foo_Foo...
```
*"Find all components with 'User' in the name"*
```
## Tables (2)
- UserEvents_1_0
- UserEvents_2_0
```
**Tip**: Search is case-sensitive by default. Use capital letters to match your component names, or ask the AI to search case-insensitively.
**Use cases:**
- Understanding project structure
- Discovering available components
- Debugging infrastructure issues
- Documenting your data pipeline
---
### `query_olap`
Execute read-only SQL queries against your local ClickHouse database.
**What you can ask for:**
- Query table data with filters, sorting, and aggregations
- Inspect table schemas and column information
- Count rows and calculate statistics
- List all tables in your database
- Results in table or JSON format
**Example prompts:**
*"What columns are in the UserEvents_1_0 table?"*
```
Query executed successfully. Rows returned: 4
| name | type | default_type | default_expression | comment | ...
|-----------|-------------------|--------------|-------------------|---------|
| userId | String | | | |
| eventType | String | | | |
| timestamp | Float64 | | | |
| metadata | Nullable(String) | | | |
```
*"List all tables and their engines"*
```
Query executed successfully. Rows returned: 29
| name | engine |
|-----------------------------|------------------------------|
| Bar | MergeTree |
| BasicTypes | MergeTree |
| UserEvents_1_0 | MergeTree |
| UserEvents_2_0 | ReplacingMergeTree |
| ReplicatedMergeTreeTest | ReplicatedMergeTree |
| BarAggregated_MV | MaterializedView |
...
```
*"Count the number of rows in Bar"*
```
Query executed successfully. Rows returned: 1
| total_rows |
|------------|
| 0 |
```
**Tip**: Ask the AI to discover table names first using "What tables exist in my project?" before querying them. Table names are case-sensitive in ClickHouse.
**Use cases:**
- Exploring data during development
- Validating data transformations
- Checking table schemas
- Debugging SQL queries
- Analyzing data patterns
**Safety:**
Only read-only operations are permitted (SELECT, SHOW, DESCRIBE, EXPLAIN). Write operations (INSERT, UPDATE, DELETE) and DDL statements (CREATE, ALTER, DROP) are blocked.
---
### `get_stream_sample`
Sample recent messages from Kafka/Redpanda streaming topics.
**What you can ask for:**
- View recent messages from any stream/topic
- Specify how many messages to sample
- Get results in JSON or pretty-printed format
- Inspect message structure and content
**Example prompts:**
*"Sample 5 messages from the Bar topic"*
```json
{
"stream_name": "Bar",
"message_count": 5,
"partition_count": 1,
"messages": [
{
"primaryKey": "e90c93be-d28b-47d6-b783-5725655c044f",
"utcTimestamp": "+057480-11-24T20:39:59.000Z",
"hasText": true,
"textLength": 107
},
{
"primaryKey": "b974f830-f28a-4a95-b61c-f65bfc607795",
"utcTimestamp": "+057370-11-04T17:11:51.000Z",
"hasText": true,
"textLength": 166
},
...
]
}
```
*"What data is flowing through the BasicTypes stream?"* (pretty format)
```markdown
# Stream Sample: BasicTypes
Retrieved 3 message(s) from 1 partition(s)
## Message 1
{
"id": "bt-001",
"timestamp": "2024-10-09T12:00:00Z",
"stringField": "hello world",
"numberField": 42,
"booleanField": true
}
## Message 2
{
"id": "bt-002",
"timestamp": "2024-10-09T12:05:00Z",
"stringField": "test",
"numberField": 100,
"booleanField": false
}
...
```
**Tip**: Use "List all streaming topics" first to discover available streams in your project.
**Use cases:**
- Debugging data flow issues
- Validating streaming transformations
- Inspecting message formats
- Troubleshooting pipeline failures
---
### `get_source`
Look up the file path where a Moose component (table, stream, API, workflow, etc.) is defined. The tool searches the infrastructure map and returns the relative path plus snippet metadata.
**What you can ask for:**
- "Where is the `UserActivity` table defined?"
- "Open the TypeScript file for the `orderEventPipeline` stream."
- "Show me the FastAPI app behind the `analytics_api` web route."
**Example prompt and response:**
```
You: "Find the source for the OrderItem stream"
AI:
Component: stream/OrderItem (TypeScript)
File: src/ingest/order-items.ts
Snippet:
);
```
**Use cases:**
- Jump from CLI output or infra map entries to the actual code
- Let AI assistants open the correct file before editing
- Generate documentation references that include file paths
The tool only reads paths within your workspace and never executes code. Pair it with `get_infra_map` for quick “find-then-open” workflows.
---
## Example Workflows
### Debugging a Data Pipeline
```
You: "My users table is empty, can you help me debug?"
AI: Let me check the infrastructure and data flow.
[Uses get_infra_map to see the pipeline]
[Uses get_logs to check for errors]
[Uses query_olap to verify table is empty]
[Uses get_stream_sample to check if data is in the source stream]
AI: I found the issue - there are errors in the logs showing the streaming
function is failing. The stream has data, but it's not reaching the table.
```
### Exploring a New Project
```
You: "Can you give me an overview of this Moose project?"
AI: [Uses get_infra_map with format=summary]
AI: This project has 3 tables (users, events, aggregated_metrics),
2 streaming topics, 4 API endpoints, and 1 workflow. The data flow
appears to be: raw events → transformation → aggregated metrics.
```
### Validating Data Transformations
```
You: "Did my streaming function correctly transform the user data?"
AI: [Uses get_stream_sample on source stream]
[Uses query_olap on destination table]
AI: Yes, the transformation is working. I compared the source stream
messages with the destination table records, and the enrichment
fields are being added correctly.
```
## Advanced Configuration
### Custom Port
If you're running Moose on a non-default port, update the MCP configuration:
```bash
export MOOSE_HTTP_PORT=8080
moose dev
```
Then update your MCP client configuration to use port 8080 instead of 4000.
### Disabling the MCP Server
To run development mode without the MCP server:
```bash
moose dev --mcp=false
```
### Production Considerations
The MCP server is designed for local development only. It provides direct access to your infrastructure and should **never** be exposed in production environments.
The MCP server:
- Runs only in development mode (`moose dev`)
- Does not run in production mode (`moose prod`)
- Provides read-only access to sensitive infrastructure
- Should not be exposed over networks or proxied externally
## LLM-Optimized Documentation Feeds
Before handing control to an AI assistant, prime it with a compact doc bundle so it understands Moose primitives and terminology. We publish TypeScript and Python versions at `/llm-ts.txt` and `/llm-py.txt`, with optional `?path=` filters for specific sections.
See [LLM-optimized docs](/moose/llm-docs) for instructions on embedding these feeds into Claude, Cursor, Windsurf, or MCP clients alongside the live tools described above.
## Troubleshooting
### MCP Tools Not Appearing
1. Verify `moose dev` is running: `curl http://localhost:4000/mcp`
2. Check your AI client's MCP configuration is correct
3. Restart your AI client after updating configuration
4. Check the Moose logs for MCP-related errors: `moose logs --filter mcp`
### Connection Errors
If your AI client can't connect to the MCP server:
```bash
# Check if the dev server is running
curl http://localhost:4000/health
# Check MCP endpoint specifically
curl -X POST http://localhost:4000/mcp \
-H "Content-Type: application/json" \
-d '{"jsonrpc":"2.0","id":1,"method":"initialize"}'
```
### Empty Results
If tools return no data:
- Verify your dev server has been running long enough to generate data
- Check that infrastructure has been created: `moose ls`
- Try ingesting test data: `moose peek `
## Related Documentation
- [Local Development](/moose/local-dev) - Development mode overview
- [Moose CLI Reference](/moose/moose-cli) - CLI commands and flags
- [Model Context Protocol](https://modelcontextprotocol.io) - MCP specification
---
## Observability
Source: moose/metrics.mdx
Unified observability for Moose across development and production—metrics console, health checks, Prometheus, OpenTelemetry, logging, and error tracking
# Observability
This page consolidates Moose observability for both local development and production environments.
## Local Development
### Metrics Console
Moose provides a console to view live metrics from your Moose application. To launch the console, run:
```bash filename="Terminal" copy
moose metrics
```
Use the arrow keys to move up and down rows in the endpoint table and press Enter to view more details about that endpoint.
#### Endpoint Metrics
Aggregated metrics for all endpoints:
| Metric | Description |
| :-------------------- | :---------------------------------------------------------------------------------- |
| `AVERAGE LATENCY` | Average time in milliseconds it takes for a request to be processed by the endpoint |
| `TOTAL # OF REQUESTS` | Total number of requests made to the endpoint |
| `REQUESTS PER SECOND` | Average number of requests made per second to the endpoint |
| `DATA IN` | Average number of bytes of data sent to all `/ingest` endpoints per second |
| `DATA OUT` | Average number of bytes of data sent to all `/api` endpoints per second |
Individual endpoint metrics:
| Metric | Description |
| :---------------------------- | :---------------------------------------------------------------------------------- |
| `LATENCY` | Average time in milliseconds it takes for a request to be processed by the endpoint |
| `# OF REQUESTS RECEIVED` | Total number of requests made to the endpoint |
| `# OF MESSAGES SENT TO KAFKA` | Total number of messages sent to the Kafka topic |
#### Stream → Table Sync Metrics
| Metric | Description |
| :---------- | :-------------------------------------------------------------------------------------------------- |
| `MSG READ` | Total number of messages sent from `/ingest` API endpoint to the Kafka topic |
| `LAG` | The number of messages that have been sent to the consumer but not yet received |
| `MSG/SEC` | Average number of messages sent from `/ingest` API endpoint to the Kafka topic per second |
| `BYTES/SEC` | Average number of bytes of data received by the ClickHouse consumer from the Kafka topic per second |
#### Streaming Transformation Metrics
For each streaming transformation:
| Metric | Description |
| :------------ | :---------------------------------------------------------------------------- |
| `MSG IN` | Total number of messages passed into the streaming function |
| `MSG IN/SEC` | Average number of messages passed into the streaming function per second |
| `MSG OUT` | Total number of messages returned by the streaming function |
| `MSG OUT/SEC` | Average number of messages returned by the streaming function per second |
| `BYTES/SEC` | Average number of bytes of data returned by the streaming function per second |
---
## Production
### Health Monitoring
Moose applications expose a health check endpoint at `/health` that returns a 200 OK response when the application is operational. This endpoint is used by container orchestration systems like Kubernetes to determine the health of your application.
In production environments, we recommend configuring three types of probes:
1. Startup Probe: Gives Moose time to initialize before receiving traffic
2. Readiness Probe: Determines when the application is ready to receive traffic
3. Liveness Probe: Detects when the application is in a deadlocked state and needs to be restarted
Learn more about how to configure health checks in your Kubernetes deployment.
### Prometheus Metrics
Moose applications expose metrics in Prometheus format at the `/metrics` endpoint. These metrics include:
- HTTP request latency histograms for each endpoint
- Request counts and error rates
- System metrics for the Moose process
Example metrics output:
```
# HELP latency Latency of HTTP requests.
# TYPE latency histogram
latency_sum{method="POST",path="ingest/UserActivity"} 0.025
latency_count{method="POST",path="ingest/UserActivity"} 2
latency_bucket{le="0.001",method="POST",path="ingest/UserActivity"} 0
latency_bucket{le="0.01",method="POST",path="ingest/UserActivity"} 0
latency_bucket{le="0.02",method="POST",path="ingest/UserActivity"} 1
latency_bucket{le="0.05",method="POST",path="ingest/UserActivity"} 1
latency_bucket{le="0.1",method="POST",path="ingest/UserActivity"} 1
latency_bucket{le="0.25",method="POST",path="ingest/UserActivity"} 1
latency_bucket{le="0.5",method="POST",path="ingest/UserActivity"} 1
latency_bucket{le="1.0",method="POST",path="ingest/UserActivity"} 1
latency_bucket{le="5.0",method="POST",path="ingest/UserActivity"} 1
latency_bucket{le="10.0",method="POST",path="ingest/UserActivity"} 1
latency_bucket{le="30.0",method="POST",path="ingest/UserActivity"} 1
latency_bucket{le="60.0",method="POST",path="ingest/UserActivity"} 1
latency_bucket{le="120.0",method="POST",path="ingest/UserActivity"} 1
latency_bucket{le="240.0",method="POST",path="ingest/UserActivity"} 1
latency_bucket{le="+Inf",method="POST",path="ingest/UserActivity"} 1
```
You can scrape these metrics using a Prometheus server or any compatible monitoring system.
### OpenTelemetry Integration
In production deployments, Moose can export telemetry data using OpenTelemetry. Enable via environment variables:
```
MOOSE_TELEMETRY__ENABLED=true
MOOSE_TELEMETRY__EXPORT_METRICS=true
```
When running in Kubernetes with an OpenTelemetry operator, you can configure automatic sidecar injection by adding annotations to your deployment:
```yaml
metadata:
annotations:
"sidecar.opentelemetry.io/inject": "true"
```
### Logging
Configure structured logging via environment variables:
```
MOOSE_LOGGER__LEVEL=Info
MOOSE_LOGGER__STDOUT=true
MOOSE_LOGGER__FORMAT=Json
```
The JSON format is ideal for log aggregation systems (ELK Stack, Graylog, Loki, or cloud logging solutions).
### Production Monitoring Stack
Recommended components:
1. Metrics Collection: Prometheus or cloud-native monitoring services
2. Log Aggregation: ELK Stack, Loki, or cloud logging solutions
3. Distributed Tracing: Jaeger or other OpenTelemetry-compatible backends
4. Alerting: Alertmanager or cloud provider alerting
### Error Tracking
Integrate with systems like Sentry via environment variables:
```
SENTRY_DSN=https://your-sentry-dsn
RUST_BACKTRACE=1
```
Want this managed in production for you? Check out Boreal Cloud (from the makers of the Moose Stack).
## Feedback
Join our Slack community to share feedback and get help with Moose.
---
## Migrations & Planning
Source: moose/migrate.mdx
How Moose handles infrastructure migrations and planning
# Moose Migrate
Moose's migration system works like version control for your infrastructure. It automatically detects changes in your code and applies them to your data infrastructure with confidence.
Moose tracks changes across:
- OLAP Tables and Materialized Views
- Streaming Topics
- API Endpoints
- Workflows
## How It Works
Moose collects all objects defined in your main file (index.ts) and automatically generates infrastructure operations to match your code:
```ts file="app/index.ts"
interface UserSchema {
id: string;
name: string;
email: string;
}
copy
// Before
copy
// After (add age field)
interface UserSchema {
id: Key;
name: string;
email: string;
age: number; // New field
}
```
**What happens:**
- Moose detects the new `age` field
- Generates migration: "Add age column to Users table"
- Applies migration
- Existing rows get NULL/default values
## Production Workflow
Moose supports two deployment patterns: **Moose Server** and **Serverless**.
### Moose Server Deployments
For deployments with a running Moose server, preview changes before applying:
```bash filename="Terminal" copy
moose plan --url https://your-production-instance --token
```
Remote planning requires authentication:
1. Generate a token: `moose generate hash-token`
2. Configure your server:
```toml filename="moose.config.toml" copy
[authentication]
admin_api_key = "your-hashed-token"
```
3. Use the token with `--token` flag
**Deployment Flow:**
1. **Develop locally** with `moose dev`
2. **Test changes** in local environment
3. **Plan against production**: `moose plan --url --token `
4. **Review changes** carefully
5. **Deploy** - Moose applies migrations automatically on startup
### Serverless Deployments
For serverless deployments (no Moose server), use the ClickHouse connection directly:
```bash filename="Terminal" copy
# Step 1: Generate migration files
moose generate migration --clickhouse-url --save
# Step 2: Preview changes in PR
moose plan --clickhouse-url clickhouse://user:pass@host:port/database
# Step 3: Execute migration after merge
moose migrate --clickhouse-url
```
**Deployment Flow:**
1. **Develop locally** with `moose dev`
2. **Generate migration plan**: `moose generate migration --clickhouse-url --save`
3. **Create PR** with `plan.yaml`, `remote_state.json`, `local_infra_map.json`
4. **PR validation**: Run `moose plan --clickhouse-url ` in CI to preview changes
5. **Review** migration files and plan output
6. **Merge PR**
7. **Execute migration**: Run `moose migrate --clickhouse-url ` in CI/CD
Requires `state_config.storage = "clickhouse"` in `moose.config.toml`:
```toml filename="moose.config.toml" copy
[state_config]
storage = "clickhouse"
[features]
olap = true
data_models_v2 = true
```
Your ClickHouse instance needs the KeeperMap engine for state storage and migration locking.
✅ **ClickHouse Cloud**: Works out of the box
✅ **`moose dev` or `moose prod`**: Already configured
⚠️ **Self-hosted ClickHouse**: See [ClickHouse KeeperMap documentation](https://clickhouse.com/docs/en/engines/table-engines/special/keeper-map) for setup requirements
### State Storage Options
Moose migrations require storing infrastructure state and coordinating locks. You can choose between two backends:
**ClickHouse State Storage (Default)**
Uses the `_MOOSE_STATE` KeeperMap table. Best for:
- ClickHouse Cloud (works out of the box)
- Self-hosted with ClickHouse Keeper already configured
**Redis State Storage**
Uses Redis for state and locking. Best for:
- Existing Redis infrastructure
- Multi-tenant deployments (isolated by `key_prefix`)
- When ClickHouse Keeper isn't available
**Configuration:**
```toml filename="moose.config.toml" copy
[state_config]
storage = "redis" # or "clickhouse" (default)
```
**Usage with Redis:**
```bash filename="Terminal" copy
# With environment variable (recommended)
export MOOSE_REDIS_CONFIG__URL="redis://host:port"
moose migrate --clickhouse-url clickhouse://...
# Or with CLI flag
moose migrate \
--clickhouse-url clickhouse://... \
--redis-url redis://host:port
```
The ClickHouse URL is always required, even when using Redis for state storage.
### Understanding Plan Output
Moose shows exactly what will change:
```bash
+ Table: Analytics Version None - id: String, number: Int64, status: String - - deduplicate: false
+ Table: Users Version None - id: String, name: String, email: String - - deduplicate: false
```
## Migration Types
| Change Type | Infrastructure Impact | Data Impact |
|-------------|----------------------|-------------|
| **Add new object** | New table/stream/API created | No impact |
| **Remove object** | Table/stream/API dropped | All data lost |
| **Add field** | New column created | Existing rows get NULL/default |
| **Remove field** | Column dropped | Data permanently lost |
| **Change type** | Column altered | Data converted if compatible |
## Viewing Infrastructure State
### Via CLI
```bash
# Check current infrastructure objects
moose ls
# View migration logs
moose logs
```
### Via Direct Connection
Connect to your local infrastructure using details from `moose.config.toml`:
```toml file="moose.config.toml"
[features]
olap = true # ClickHouse for analytics
streaming_engine = true # Redpanda for streaming
workflows = false # Temporal for workflows
[clickhouse_config]
host = "localhost"
host_port = 18123
native_port = 9000
db_name = "local"
user = "panda"
password = "pandapass"
[redpanda_config]
broker = "localhost:19092"
message_timeout_ms = 1000
retention_ms = 30000
replication_factor = 1
```
## Best Practices
### Development
- Use `moose dev` for all local development
- Monitor plan outputs for warnings
- Test schema changes with sample data
### Production
- Always use remote planning before deployments
- Review changes carefully in production plans
- Maintain proper authentication
- Test migrations in staging first
### Managing TTL Outside Moose
If you're managing ClickHouse TTL settings through other tools or want to avoid migration failures from TTL drift, you can configure Moose to ignore TTL changes:
```toml filename="moose.config.toml" copy
[migration_config]
ignore_operations = ["ModifyTableTtl", "ModifyColumnTtl"]
```
This tells Moose to:
- Skip generating TTL change operations in migration plans
- Ignore TTL differences during drift detection
You'll still get migrations for all other schema changes (adding tables, modifying columns, etc.), but TTL changes won't block your deployments.
## Troubleshooting
### Authentication Errors
- Verify your authentication token
- Generate a new token: `moose generate hash-token`
- Check server configuration in `moose.config.toml`
### Migration Issues
- Check `moose logs` for detailed error messages
- Verify object definitions in your main file
- Ensure all required fields are properly typed
- **Stuck migration lock**: If you see "Migration already in progress" but no migration is running, wait 5 minutes for automatic expiry or manually clear it:
```sql
DELETE FROM _MOOSE_STATE WHERE key = 'migration_lock';
```
---
## LifeCycle Management
Source: moose/migrate/lifecycle.mdx
Control how Moose manages database and streaming resources when your code changes
# LifeCycle Management
## Overview
The `LifeCycle` enum controls how Moose manages the lifecycle of database/streaming resources when your code changes.
This feature gives you fine-grained control over whether Moose automatically updates your database schema or
leaves it under external/manual control.
## LifeCycle Modes
### `FULLY_MANAGED` (Default)
This is the default behavior where Moose has complete control over your database resources. When you change your data models, Moose will automatically:
- Add new columns or tables
- Remove columns or tables that no longer exist in your code
- Modify existing column types and constraints
This mode can perform destructive operations. Data may be lost if you remove fields from your data models or if you perform operations that require a destroy and recreate to be effective, like changing the `orderByFields` field .
```ts filename="FullyManagedExample.ts" copy
interface UserData {
id: string;
name: string;
email: string;
}
// Default behavior - fully managed
const userTable = new OlapTable("users");
// Explicit fully managed configuration
const explicitTable = new OlapTable("users", {
orderByFields: ["id"],
lifeCycle: LifeCycle.FULLY_MANAGED
});
```
### `DELETION_PROTECTED`
This mode allows Moose to automatically add new database structures but prevents it from removing existing ones.
Perfect for production environments where you want to evolve your schema safely without risking data loss.
**What Moose will do:**
- Add new columns, tables
- Modify column types (if compatible)
- Update non-destructive configurations
**What Moose won't do:**
- Drop columns or tables
- Perform destructive schema changes
```ts filename="DeletionProtectedExample.ts" copy
interface ProductEvent {
id: string;
productId: string;
timestamp: Date;
action: string;
}
const productAnalytics = new IngestPipeline("product_analytics", {
table: {
orderByFields: ["timestamp", "productId"],
engine: ClickHouseEngines.ReplacingMergeTree,
},
stream: {
parallelism: 4,
},
ingestApi: true,
// automatically applied to the table and stream
lifeCycle: LifeCycle.DELETION_PROTECTED
});
```
### `EXTERNALLY_MANAGED`
This mode tells Moose to completely hands-off your resources.
You become responsible for creating and managing the database schema. This is useful when:
- You have existing database tables managed by another team
- You're integrating with another system (e.g. PeerDB)
- You have strict database change management processes
With externally managed resources, you must ensure your database schema matches your data models exactly, or you may encounter runtime errors.
```ts filename="ExternallyManagedExample.ts" copy
interface ExternalUserData {
userId: Key;
fullName: string;
emailAddress: string;
createdAt: Date;
}
// Connect to existing database table
const legacyUserTable = new OlapTable("legacy_users", {
lifeCycle: LifeCycle.EXTERNALLY_MANAGED
});
// Connect to existing Kafka topic
const legacyStream = new Stream("legacy_user_stream", {
lifeCycle: LifeCycle.EXTERNALLY_MANAGED,
destination: legacyUserTable
});
```
---
## Migration Examples & Advanced Development
Source: moose/migrate/migration-types.mdx
Detailed migration examples and advanced development topics
# Migration Types
This guide provides detailed examples of different migration types. For the complete workflow overview, see [Migrations & Planning](/moose/migrate/planning).
## Adding New Infrastructure Components
Keep in mind that only the modules that you have enabled in your `moose.config.toml` will be included in your migrations.
```toml file="moose.config.toml"
[features]
olap = true
streaming_engine = true
workflows = true
```
### New OLAP Table or Materialized View
```ts file="app/index.ts"
interface AnalyticsSchema {
id: string;
event_type: string;
timestamp: Date;
user_id: string;
value: number;
}
/apply-migrations`} ctaLabel="Learn More">
Check out the OLAP migrations guide to learn more about the different migration modes.
### New Stream
```ts file="app/index.ts"
interface UserSchema {
id: string;
name: string;
email: string;
age: number;
created_at: Date;
is_active: boolean;
}
```
**Migration Result:** Adds `age`, `created_at`, and `is_active` columns to existing table
### Removing Fields
```ts file="app/index.ts"
// Before
interface UserSchema {
id: string;
name: string;
email: string;
age: number;
deprecated_field: string; // Will be removed
}
// After
interface UserSchema {
id: string;
name: string;
email: string;
age: number;
}
```
**Migration Result:** Drops `deprecated_field` column (data permanently lost)
### Type Changes
```ts file="app/index.ts"
// Before
interface UserSchema {
id: string;
name: string;
email: string;
score: number; // Will change to string
}
// After
interface UserSchema {
id: string;
name: string;
email: string;
score: string; // Changed from number
}
```
**Migration Result:** Alters `score` column type (data converted if compatible)
## Removing Infrastructure
```ts file="app/index.ts"
// Before
usersTable = new OlapTable("Users");
analyticsTable = new OlapTable("Analytics");
deprecatedTable = new OlapTable("Deprecated");
// After (remove deprecated table)
usersTable = new OlapTable("Users");
analyticsTable = new OlapTable("Analytics");
```
**Migration Result:** Drops `Deprecated` table (all data lost)
## Working with Local Infrastructure
There are two main ways to inspect your local infrastructure to see how your migrations are applied:
### Using the CLI
Run `moose ls` to see the current state of your infrastructure:
```bash
# Verify object definitions
moose ls
```
### Connecting to your local infrastructure
You can also connect directly to your local infrastructure to see the state of your infrastructure.
All credentials for your local infrastructure are located in your project config file (`moose.config.toml`).
#### Connecting to ClickHouse
```bash
# Using clickhouse-client
clickhouse-client --host localhost --port 18123 --user panda --password pandapass --database local
# Using connection string
clickhouse-client "clickhouse://panda:pandapass@localhost:18123/local"
```
#### Connecting to Redpanda
```bash
# Using kafka-console-consumer
kafka-console-consumer --bootstrap-server localhost:19092 --topic UserEvents --from-beginning
# Using kafka-console-producer
kafka-console-producer --bootstrap-server localhost:19092 --topic UserEvents
```
#### Viewing Temporal Workflows
Navigate to `http://localhost:8080` to view the Temporal UI and see registered workflows.
## Gotchas:
Your dev server must be running to connect to your local infrastructure.
```bash
moose dev
```
Only the modules that you have enabled in your `moose.config.toml` will be included in your migrations:
```toml file="moose.config.toml"
[features]
olap = true # Required for OLAP Tables and Materialized Views
streaming_engine = true # Required for Streams
workflows = true # Required for Workflows and Tasks
```
---
## Moose CLI Reference
Source: moose/moose-cli.mdx
Moose CLI Reference
# Moose CLI Reference
## Installation
```bash filename="Terminal" copy
bash -i <(curl -fsSL https://fiveonefour.com/install.sh) moose
```
## Core Commands
### Init
Initializes a new Moose project.
```bash
moose init --template [--location ] [--no-fail-already-exists]
```
- ``: Name of your app or service
- ``: Template to use for the project
- `--location`: Optional location for your app or service
- `--no-fail-already-exists`: Skip the directory existence check
#### List Templates
Lists available templates for project initialization.
```bash
moose template list
```
### Build
Builds your Moose project.
```bash
moose build [--docker] [--amd64] [--arm64]
```
- `--docker`: Build for Docker
- `--amd64`: Build for AMD64 architecture
- `--arm64`: Build for ARM64 architecture
### Dev
Starts a local development environment with hot reload and automatic infrastructure management.
```bash
moose dev [--mcp] [--docker]
```
- `--mcp`: Enable or disable the MCP (Model Context Protocol) server (default: true). The MCP server provides AI-assisted development tools at `http://localhost:4000/mcp`. See [MCP Server documentation](/moose/mcp-server) for details.
- `--docker`: Use Docker for infrastructure (default behavior in dev mode)
The development server includes:
- Hot reload for code changes
- Automatic Docker container management (ClickHouse, Redpanda, Temporal, Redis)
- Built-in MCP server for AI assistant integration
- Health monitoring and metrics endpoints
### Prod
Starts Moose in production mode for cloud deployments.
```bash
moose prod
```
### Check
Checks the project for non-runtime errors.
```bash
moose check [--write-infra-map]
```
### Clean
Clears temporary data and stops development infrastructure.
```bash
moose clean
```
### Seed (ClickHouse)
Seed your local ClickHouse from a remote ClickHouse instance.
```bash
moose seed clickhouse [--connection-string ] [--table ] [--limit | --all]
```
- `--connection-string`: Remote ClickHouse connection string. If omitted, the CLI uses `MOOSE_SEED_CLICKHOUSE_URL`.
- `--table`: Seed only the specified table (default: all Moose tables).
- `--limit`: Copy up to N rows (mutually exclusive with `--all`). Large limits are automatically batched.
- `--all`: Copy entire table(s) in batches (mutually exclusive with `--limit`).
**Connection String Format:**
The connection string must use ClickHouse native protocol:
```bash
# ClickHouse native protocol (secure connection)
clickhouse://username:password@host:9440/database
```
**Important:**
- The data transfer uses ClickHouse's native TCP protocol via `remoteSecure()` function. The remote ClickHouse server must have the native TCP port accessible (typically port 9440 for secure connections).
- **Smart table matching**: The command automatically validates tables between local and remote databases. Tables that don't exist on the remote are gracefully skipped with warnings.
- Use `--table ` to seed a specific table that exists in both local and remote databases.
**User Experience:**
- Progress indicator shows seeding status with spinner
- Tables that don't exist on remote are automatically skipped with clear warnings
- Final summary shows successful and skipped tables
- Clean, streamlined output focused on results
Notes:
- Seeding is batched automatically for large datasets; Ctrl+C finishes the current batch gracefully.
- Use env var fallback:
```bash
export MOOSE_SEED_CLICKHOUSE_URL='clickhouse://username:password@host:9440/database'
```
### Truncate
Truncate tables or delete the last N rows from local ClickHouse tables.
```bash
moose truncate [TABLE[,TABLE...]] [--all] [--rows ]
```
- `TABLE[,TABLE...]`: One or more table names (comma-separated). Omit to use `--all`.
- `--all`: Apply to all non-view tables in the current database (mutually exclusive with listing tables).
- `--rows `: Delete the last N rows per table; omit to remove all rows (TRUNCATE).
Notes:
- For `--rows`, the command uses the table ORDER BY when available; otherwise it falls back to a timestamp heuristic.
## Monitoring Commands
### Logs
View Moose logs.
```bash
moose logs [--tail] [--filter ]
```
- `--tail`: Follow logs in real-time
- `--filter`: Filter logs by specific string
### Ps
View Moose processes.
```bash
moose ps
```
### Ls
View Moose primitives & infrastructure.
```bash
moose ls [--limit ] [--version ] [--streaming] [--type ] [--name ] [--json]
```
- `--limit`: Limit output (default: 10)
- `--version`: View specific version
- `--streaming`: View streaming topics
- `--type`: Filter by infrastructure type (tables, streams, ingestion, sql_resource, consumption)
- `--name`: Filter by name
- `--json`: Output in JSON format
### Metrics
View live metrics from your Moose application.
```bash
moose metrics
```
### Peek
View data from a table or stream.
```bash
moose peek [--limit ] [--file ] [-t|--table] [-s|--stream]
```
- ``: Name of the table or stream to peek
- `--limit`: Number of rows to view (default: 5)
- `--file`: Output to a file
- `-t, --table`: View data from a table (default if neither flag specified)
- `-s, --stream`: View data from a stream/topic
## Generation Commands
### Generate Hash Token
Generate authentication tokens for API access.
```bash
moose generate hash-token
```
Generates both a plain-text Bearer token and its corresponding hashed version for authentication.
### Generate Migration Plan (OLAP)
Create an ordered ClickHouse DDL plan by comparing a remote instance with your local code.
```bash
moose generate migration --url https:// --token --save
```
- Writes `./migrations/plan.yaml` and snapshots `remote_state.json` and `local_infra_map.json`
- Omit `--save` to output to stdout without writing files
- Requires these feature flags in `moose.config.toml`:
```toml filename="moose.config.toml" copy
[features]
olap = true
ddl_plan = true
```
### DB Pull (External Tables)
Refresh `EXTERNALLY_MANAGED` table definitions from a remote ClickHouse instance.
```bash
moose db pull --connection-string [--file-path ]
```
- `--connection-string`: ClickHouse URL; native `clickhouse://` is auto-converted to HTTP(S). Include `?database=` or the CLI will query the current database.
- `--file-path`: Optional override for the generated external models file (defaults to `app/externalModels.ts` or `app/external_models.py`).
Notes:
- Only tables marked `EXTERNALLY_MANAGED` in your code are refreshed.
- The command writes a single external models file and overwrites the file on each run.
- See the full guide: [/moose/olap/db-pull](/moose/olap/db-pull)
### Kafka
#### Pull external topics and schemas
Discover topics from a Kafka/Redpanda cluster and optionally fetch JSON Schemas from Schema Registry to emit typed external models.
```bash
moose kafka pull [--path ] [--include ] [--exclude ] [--schema-registry ]
```
- ``: Kafka bootstrap servers, e.g. `localhost:19092`
- `--path`: Output directory for generated files. Defaults to `app/external-topics` (TS) or `app/external_topics` (Python).
- `--include`: Include pattern (glob). Default: `*`
- `--exclude`: Exclude pattern (glob). Default: `{__consumer_offsets,_schemas}`
- `--schema-registry`: Base URL for Schema Registry, e.g. `http://localhost:8081`
Notes:
- JSON Schema is supported initially; Avro/Protobuf planned.
- Generated files will be overwritten on subsequent runs.
## Workflow Management
### Workflow
```bash
moose workflow [options]
```
Available workflow commands:
- `init [--tasks ] [--task ...]`: Initialize a new workflow
- `run [--input ]`: Run a workflow
- `resume --from `: Resume a workflow from a specific task
- `list [--json]`: List registered workflows
- `history [--status ] [--limit ] [--json]`: Show workflow history
- `terminate `: Terminate a workflow
- `pause `: Pause a workflow
- `unpause `: Unpause a workflow
- `status [--id ] [--verbose] [--json]`: Get workflow status
## Planning and Deployment
### Plan
Display infrastructure changes for the next production deployment.
**For Moose Server deployments:**
```bash
moose plan [--url ] [--token ]
```
- `--url`: Remote Moose instance URL (default: http://localhost:4000)
- `--token`: API token for authentication
**For Serverless deployments:**
```bash
moose plan --clickhouse-url
```
- `--clickhouse-url`: ClickHouse connection URL (e.g., `clickhouse://user:pass@host:port/database`)
### Refresh
Integrate matching tables from a remote instance into the local project.
```bash
moose refresh [--url ] [--token ]
```
- `--url`: Remote Moose instance URL (default: http://localhost:4000)
- `--token`: API token for authentication
This reference reflects the current state of the Moose CLI based on the source code in the framework-cli directory. The commands are organized by their primary functions and include all available options and flags.
---
## Moose OLAP
Source: moose/olap.mdx
Create and manage ClickHouse tables, materialized views, and data migrations
# Moose OLAP
## Overview
The Moose OLAP module provides standalone ClickHouse database management with type-safe schemas. You can use this capability independently to create tables, materialized views, and manage your table schemas without requiring other MooseStack components.
### Basic Example
```ts filename="FirstTable.ts" copy
interface MyFirstTable {
id: Key;
name: string;
age: number;
}
// Create a table named "first_table"
myTable = new OlapTable("first_table");
```
## Getting started
If you’re new to Moose OLAP, choose one of these paths:
### Import your schema from an existing ClickHouse database
```bash filename="Terminal" copy
moose init your-project-name --from-remote
```
Review the full guide to learn more about how to bootstrap a new Moose OLAP project from an existing ClickHouse DB.
### Start from scratch
Create a blank project, then model your first table:
```bash filename="Terminal" copy
moose init your-project-name --language
cd your-project-name
```
Review the guide to learn more about how to model your first table.
Working with ClickPipes/PeerDB? Read [External Tables](/moose/olap/external-tables) and keep them in sync with [DB Pull](/moose/olap/db-pull).
## Enabling Moose OLAP
In your `moose.config.toml` file, enable the OLAP Database capability:
```toml
[features]
olap = true
```
## Core Capabilities
- Model tables and views with TypeScript or Python
- Automatic type mapping and support for advanced ClickHouse column types (e.g `JSON`, `LowCardinality`, `Nullable`, etc)
- Create tables and views with one line of code
- In-database transformations/materialized views
- Migrations and schema evolution
- Query with templated SQL strings and type-safe table and column references
- Bulk insertion with failure handling and runtime validation
### Managing your database
### Accessing your data
These capabilities are available from other MooseStack modules or even from your own client applications:
### Connecting to your ClickHouse instance
You can connect to your ClickHouse instance with your favorite database client. Your credentials are located in your `moose.config.toml` file:
```toml filename="moose.config.toml" copy
[clickhouse_config]
db_name = "local"
user = "panda"
password = "pandapass"
use_ssl = false
host = "localhost"
host_port = 18123
native_port = 9000
```
These are the default credentials for your local ClickHouse instance running in dev mode.
### Combining with other modules:
Although designed to work independently, Moose OLAP can be combined with other modules to add additional capabilities surrounding your database:
- Combine with Streaming and APIs to setup streaming ingestion into ClickHouse tables
- Combine with Workflows to build ETL pipelines and data transformations
- Combine with APIs to expose your ClickHouse queries to client applications via REST API
---
## Applying Migrations
Source: moose/olap/apply-migrations.mdx
How to apply migrations to your database
# Applying Migrations
This page covers OLAP migrations. For migrations across the MooseStack, see the Migrate docs.
## Overview
Migrations are designed for two complementary goals:
- Move fast locally by inferring changes from your code and applying them immediately to your local database.
- Be deliberate in production by executing a reviewed, versioned plan that matches your intent and protects data.
How to think about it:
- Development mode: You edit code, MooseStack infers the SQL and immediately applies it to local ClickHouse. Great for rapid iteration; not guaranteed to infer intent (e.g., renames).
- Production (planned) mode: You generate a plan from the target environment vs your code, review and commit the plan, and MooseStack executes it deterministically during deploy with drift checks.
What you need to do:
- In dev: just code. MooseStack handles local diffs automatically.
- In prod (OLAP):
- Generate and save a plan:
```bash
moose generate migration --url https:// --token --save
```
- Review and edit the plan (`plan.yaml`) as needed
- Commit the plan to source control
- Deploy to production. MooseStack validates snapshots (current DB vs `remote_state.json`, desired code vs `local_infra_map.json`) and executes `plan.yaml` in order. If drift is detected, the deploy aborts; regenerate the plan and retry.
## Development Workflow
### Starting the Runtime
Use `moose dev` to start the MooseStack runtime with automatic migration detection:
```bash
moose dev
⡏ Starting local infrastructure
Successfully started containers
Validated clickhousedb-1 docker container
Validated redpanda-1 docker container
Successfully validated red panda cluster
Validated temporal docker container
Successfully ran local infrastructure
```
### Hot-Reloaded Migrations
MooseStack continuously monitors your code changes and applies migrations automatically. All changes are applied to your **local database only**.
```ts filename="app/tables/events.ts" copy
interface Event {
id: Key;
name: string;
createdAt: Date;
status: string; // New field - will trigger migration
}
/planned-migrations`} ctaLabel="Planned Migrations (OLAP)" compact>
Use planned migrations to generate, review, and apply OLAP DDL plans deterministically.
### Generating Migration Plans
When using planned migrations for OLAP, you need to generate a migration plan from the remote environment. This is done by running the following command:
```bash
moose generate migration --url https:// --token --save
```
This generates a few files in the `migrations` directory:
- `plan.yaml`: The migration plan containing an ordered list of operations to apply to the remote database to bring it into alignment with your local code.
- `remote_state.json`: A snapshot of the remote database state at the time the plan was generated
- `local_infra_map.json`: A snapshot of the local database state at the time the plan was generated
The remote and local state are used to validate that the plan is still valid at the time of deployment. If there have been schema changes made to your live remote database since the plan was generated, the deployment will abort and you will need to regenerate the plan. This is to prevent you from dropping data unintentionally.
### Reviewing and Editing the Plan
You can review and edit the plan as needed. The plan is a YAML file that contains an ordered list of operations to apply to the remote database to bring it into alignment with your local code.
```yaml filename="migrations/plan.yaml" copy
```
### Applying the Plan
The plan is applied during deployment. MooseStack will validate that the remote database state matches the snapshot of the database state at the time the plan was generated, and applies `plan.yaml` in order; it aborts if snapshots don’t match current state.
## Migration Types
### Adding New Tables or Materialized Views
```typescript filename="index.ts" {4-7} copy
// Export the new table and materialized view to apply changes
export {
newTable, // New table
newMaterializedView // New materialized view
}
```
The dev mode will automatically detect the new table or materialized view and apply the changes to your local database. You will see a log like this in the terminal:
```bash filename="Terminal" copy
$ moose dev
⠋ Processing Infrastructure changes from file watcher
+ Table: new_table Version None - id: String, a_column: String, some_other_column: Float64 - - deduplicate: false
+ Table: target_table Version None - id: String, a_column: String, some_other_column: Float64 - id - deduplicate: false
+ SQL Resource: mv_to_target
```
The generated plan for this operation will look like this:
```yaml filename="migrations/plan.yaml" copy
- CreateTable:
table:
name: new_table
columns:
- name: id
data_type: String
required: true
unique: false
primary_key: true
default: null
annotations: []
- name: a_column
data_type: String
required: true
unique: false
primary_key: false
default: null
annotations: []
- name: some_other_column
data_type: Float64
required: true
unique: false
primary_key: false
default: null
annotations: []
order_by:
- id
deduplicate: false
engine: MergeTree
version: null
metadata:
description: null
life_cycle: FULLY_MANAGED
- CreateTable:
table:
name: target_table
columns:
- name: id
data_type: String
required: true
unique: false
primary_key: true
default: null
annotations: []
- name: a_column
data_type: String
required: true
unique: false
primary_key: false
default: null
annotations: []
- name: some_other_column
data_type: Float64
required: true
unique: false
primary_key: false
default: null
annotations: []
order_by:
- id
deduplicate: false
engine: MergeTree
version: null
metadata:
description: null
life_cycle: FULLY_MANAGED
- RawSQL:
sql: "CREATE MATERIALIZED VIEW mv_to_target TO target_table AS SELECT * FROM source_table"
description: Running setup SQL for resource mv_to_target
```
### Column Additions
Adding new fields to your data models:
```ts filename="Before" copy
interface AddedColumn {
id: Key;
another_column: string;
some_column: string;
}
```
```ts filename="After" copy
interface AddedColumn {
id: Key;
another_column: string;
some_column: string;
new_column: number; // New field - migration applied
}
```
In dev mode, you will see a log like this:
```bash filename="Terminal" copy
$ moose dev
⢹ Processing Infrastructure changes from file watcher
~ Table events:
Column changes:
+ new_column: Int64
```
The generated plan for this operation will look like this:
```yaml filename="migrations/plan.yaml" copy
- AddTableColumn:
table: "events"
column:
name: "new_column"
data_type: "Int64"
```
### Column Removals
Removing fields from your data models:
```ts filename="Before" copy
interface RemovedColumn {
id: Key;
another_column: string;
some_column: string;
old_column: number;
}
const selectStatement = sql`
SELECT
toStartOfDay(${sourceTable.columns.a_date}) as day,
uniq(${sourceTable.columns.id}) as count,
SUM(${sourceTable.columns.a_number}) as sum
FROM ${sourceTable}
GROUP BY day
`;
const mv = new MaterializedView({
selectStatement,
selectTables: [sourceTable],
targetTable: {
name: "target_table",
engine: ClickHouseEngines.MergeTree,
orderByFields: ["day"],
},
materializedViewName: "mv_table_to_target,
});
```
```ts filename="After.ts" copy
interface TargetSchema {
day: Date;
count: number;
sum: number;
avg: number; // New column - migration applied
}
const selectStatement = sql`
SELECT
toStartOfDay(${sourceTable.columns.a_date}) as day,
uniq(${sourceTable.columns.id}) as count,
sum(${sourceTable.columns.a_number}) as sum,
avg(${sourceTable.columns.a_number}) as avg
FROM ${sourceTable}
GROUP BY day
`;
---
## Syncing External Tables
Source: moose/olap/db-pull.mdx
Refresh your external table models from an existing ClickHouse database
# Syncing External Tables
## What this is
Use `moose db pull` to refresh the definitions of tables you marked as `EXTERNALLY_MANAGED` from a live ClickHouse instance. It reads your code to find external tables, fetches their remote schemas, regenerates one external models file, and creates a small git commit if anything changed. If new external tables were added remotely (e.g., new CDC streams), they are added to the external models file as part of the same run.
## When to use it
- **External tables changed remotely**: a DBA, CDC, or ETL pipeline updated schema.
- **Keep types in sync**: update generated models without touching fully-managed tables.
- **Safe by design**: does not modify the database or your managed models.
This is a read-only sync for your code models. For concepts and modeling guidance, see [External Tables](/moose/olap/external-tables). To bootstrap a project from an existing DB, see [Initialize from ClickHouse](/moose/getting-started/from-clickhouse).
## Requirements
- Tables are defined with `lifeCycle: EXTERNALLY_MANAGED`
- A ClickHouse connection string (native or HTTP/S)
## Connection strings
`db pull` accepts both native and HTTP(S) URLs. Native strings are automatically converted to HTTP(S) with the appropriate ports.
Examples:
```bash filename="Terminal" copy
# Native (auto-converted to HTTPS + 8443)
moose db pull --connection-string "clickhouse://explorer@play.clickhouse.com:9440/default"
# HTTPS (explicit database via query param)
moose db pull --connection-string "https://play.clickhouse.com/?user=explorer&database=default"
# Local HTTP
moose db pull --connection-string "http://localhost:8123/?user=default&database=default"
```
## What gets written
`app/externalModels.ts`
`db pull` treats this file as the single source of truth for `EXTERNALLY_MANAGED` tables. It introspects the remote schema, updates existing external tables, and adds any newly detected external tables here. It does not modify models elsewhere in your codebase.
Keep all external tables in this file and import it once from your root (`app/index.ts`).
Important:
- The file is overwritten on every run (or at the path passed via `--file-path`).
- If you customize the path, ensure your root file imports it so Moose loads your external models.
## How it works
When you run `db pull` the CLI does the following:
- Loads your project’s infrastructure map and identifies tables marked as `EXTERNALLY_MANAGED`.
- Connects to the remote ClickHouse specified by `--connection-string` and introspects the live schemas for those tables.
- Regenerates a single external models file that mirrors the remote schema.
- Adds any newly detected external tables from the remote database to the generated file so your code stays in sync as sources evolve.
- Does not change any fully managed tables, your `app/index.ts`, or the database itself.
- Creates a small git commit if the generated file changed, so you can review and share the update.
### Example output
```ts filename="app/externalModels.ts"
// AUTO-GENERATED FILE. DO NOT EDIT.
// This file will be replaced when you run `moose db pull`.
// ...typed table definitions matching remote EXTERNALLY_MANAGED tables...
```
## Command
```bash filename="Terminal" copy
moose db pull --connection-string [--file-path ]
```
- **--connection-string**: Required. ClickHouse URL (native or HTTP/S)
- **--file-path**: Optional. Override the default output file. The file at this path will be regenerated (overwritten) on each run.
## Typical Use Cases
### Remote schema changed; update local types
Your DBA, CDC pipeline (e.g., ClickPipes), or ETL job updated a table’s schema. To keep your code accurate and type-safe, refresh your external models so queries, APIs, and materialized views reference the correct columns and types.
```bash filename="Terminal" copy
moose db pull --connection-string
```
This updates only `EXTERNALLY_MANAGED` models and leaves managed code untouched.
### Automatically run on dev startup (keep local fresh)
In active development, schemas can drift faster than you commit updates. Running `db pull` on dev startup helps ensure your local code matches the live schema you depend on.
```bash filename="Terminal" copy
export REMOTE_CLICKHOUSE_URL="clickhouse://:@:/"
```
Add to `moose.config.toml`:
```toml filename="moose.config.toml" copy
[http_server_config]
on_first_start_script = "moose db pull --connection-string $REMOTE_CLICKHOUSE_URL"
```
This runs once when the dev server first starts. To run after code reloads, use `on_reload_complete_script`. If you run this frequently, prefer HTTP(S) URLs and cache credentials via env/secrets to avoid friction.
### New project from an existing DB
If you’re starting with an existing ClickHouse database, bootstrap code with `init --from-remote`, then use `db pull` over time to keep external models fresh:
```bash filename="Terminal" copy
moose init my-project --from-remote $REMOTE_CLICKHOUSE_URL --language
```
Review the full getting started guide to learn more about how to bootstrap a new Moose OLAP project from an existing ClickHouse DB.
### A new CDC/external table appeared; add it to code
Your CDC pipeline created a new table (or exposed a new stream). Pull to add the new table to your external models file automatically.
```bash filename="Terminal" copy
moose db pull --connection-string
```
The regenerated external models file will now include the newly discovered external table.
## Troubleshooting
- **No changes written**: Ensure tables are actually marked as `EXTERNALLY_MANAGED` and names match remote.
- **Unsupported types**: The CLI will list tables with unsupported types; they’re skipped in the generated file.
- **Auth/TLS errors**: Verify scheme/ports (8123 or 8443) and credentials; try HTTPS if native URL fails.
- **Git commit issues**: The command attempts a lightweight commit; commit manually if your working tree is dirty.
## Related
- **External Tables**: concepts and configuration
- **Initialize from ClickHouse**: bootstrap projects from an existing DB
- **Supported Types**: mapping and constraints
---
## External Tables
Source: moose/olap/external-tables.mdx
Connect to externally managed database tables and CDC services
# External Tables
## Overview
External tables allow you to connect Moose to database tables that are managed outside of your application. This is essential when working with:
- **CDC (Change Data Capture) services** like ClickPipes, Debezium, or AWS DMS
- **Legacy database tables** managed by other teams
- **Third-party data sources** with controlled schema evolution
## When to Use External Tables
## Configuration
Set `lifeCycle: LifeCycle.EXTERNALLY_MANAGED` to tell Moose not to modify the table schema:
```ts filename="ExternalTableExample.ts" copy
interface CdcUserData {
id: string;
name: string;
email: string;
updated_at: Date;
}
// Connect to CDC-managed table
const cdcUserTable = new OlapTable("cdc_users", {
lifeCycle: LifeCycle.EXTERNALLY_MANAGED
});
```
## Getting Models for External Tables
### New project: initialize from your existing ClickHouse
If you don’t yet have a Moose project, use init-from-remote to bootstrap models from your existing ClickHouse:
```bash filename="Terminal" copy
moose init my-project --from-remote --language
```
What happens:
- Moose introspects your database and generates table models in your project.
- If Moose detects ClickPipes (or other CDC-managed) tables, it marks those as `EXTERNALLY_MANAGED` and writes them into a dedicated external models file:
- TypeScript: `app/externalModels.ts`
- Python: `app/external_models.py`
- This is a best-effort detection to separate CDC-managed tables from those you may want Moose to manage in code.
How detection works (ClickPipes/PeerDB example):
- Moose looks for PeerDB-specific fields that indicate CDC ownership and versions, such as `_peerdb_synced_at`, `_peerdb_is_deleted`, `_peerdb_version`, and related metadata columns.
- When these are present, the table will be marked `EXTERNALLY_MANAGED` and emitted into the external models file automatically.
```ts filename="externalModels.ts" copy
export interface foo {
id: string & typia.tags.Format<"uuid">;
name: string;
description: string | undefined;
status: string;
priority: number & ClickHouseInt<"int32">;
is_active: boolean;
metadata: string | undefined;
tags: string[];
score: (string & ClickHouseDecimal<10, 2>) | undefined;
large_text: string | undefined;
created_at: string & typia.tags.Format<"date-time"> & ClickHousePrecision<6>;
updated_at: string & typia.tags.Format<"date-time"> & ClickHousePrecision<6>;
_peerdb_synced_at: string & typia.tags.Format<"date-time"> & ClickHousePrecision<9> & ClickHouseDefault<"now64()">;
_peerdb_is_deleted: number & ClickHouseInt<"int8">;
_peerdb_version: number & ClickHouseInt<"int64">;
}
);
```
### Existing project: mark additional external tables
If there are other tables in your DB that are not CDC-managed but you want Moose to treat as external (not managed by code):
1) Mark them as external in code
```ts copy
// In a file you control (not the external file yet)
const table = new OlapTable("my_table", {
lifeCycle: LifeCycle.EXTERNALLY_MANAGED
});
```
2) Move them into the external models file
- Move the model definitions to your external file (`app/externalModels.ts` or `app/external_models.py`).
- Ensure your root file still loads only the external models via a single import:
- Add `import "./externalModels";` in your `app/index.ts` file.
This keeps truly external tables out of your managed code path, while still making them available locally (and in tooling) without generating production DDL.
## Important Considerations
`EXTERNALLY_MANAGED` tables reflect schemas owned by your CDC/DBA/ETL processes. Do not change their field shapes in code.
If you accidentally edited an external model, revert to the source of truth by running **DB Pull**: [/moose/olap/db-pull](/moose/olap/db-pull).
Locally, externally managed tables are created/kept in sync in your development ClickHouse so you can develop against them and **seed data**. See **Seed (ClickHouse)** in the CLI: [/moose/moose-cli#seed-clickhouse](/moose/moose-cli#seed-clickhouse).
Moose will **not** apply schema changes to `EXTERNALLY_MANAGED` tables in production. If you edit these table models in code, those edits will not produce DDL operations in the migration plan (they will not appear in `plan.yaml`).
For more on how migration plans are generated and what shows up in `plan.yaml`, see [/moose/olap/planned-migrations](/moose/olap/planned-migrations).
## Staying in sync with remote schema
For `EXTERNALLY_MANAGED` tables, keep your code in sync with the live database by running DB Pull. You can do it manually or automate it in dev.
```bash filename="Terminal" copy
moose db pull --connection-string
```
Use DB Pull to regenerate your external models file from the remote schema. To run it automatically during development, see the script hooks in [the local development guide](/moose/local-dev#script-execution-hooks).
---
## Secondary Indexes
Source: moose/olap/indexes.mdx
Specifying indexes with Moose OLAP
## Indexes for ClickHouse tables
Moose lets you declare secondary/data-skipping indexes directly in your table definitions.
Moose generates the ClickHouse `INDEX` clauses on create and
plans `ALTER TABLE ADD/DROP INDEX` operations when you change them later.
### When to use indexes
- Use indexes to optimize selective predicates on large tables, especially string and high-cardinality columns.
- Common types: `minmax`, `Set(max_rows)`, `ngrambf_v1(...)`, `bloom_filter`.
### TypeScript
```ts
interface Events {
id: string;
user: string;
message: string;
}
);
```
### Python
```python
from moose_lib.dmv2.olap_table import OlapTable, OlapConfig, MergeTreeEngine
from pydantic import BaseModel
class Events(BaseModel):
id: str
user: str
message: str
events_table = OlapTable[Events](
"Events",
OlapConfig(
engine=MergeTreeEngine(),
order_by_fields=["id"],
indexes=[
OlapConfig.TableIndex(name="idx_user", expression="user", type="minmax", granularity=1),
OlapConfig.TableIndex(name="idx_message_ngrams", expression="message", type="ngrambf_v1", arguments=["3","256","1","123"], granularity=1),
],
),
)
```
### How Moose applies changes
- On create, Moose emits `INDEX ...` entries inside `CREATE TABLE`.
- On change, Moose plans `ALTER TABLE DROP INDEX ` then `ADD INDEX ...` if the definition changed; pure adds/drops are applied as single operations.
---
## Inserting Data
Source: moose/olap/insert-data.mdx
Insert data into OLAP tables using various methods
# Inserting Data
Inserting data into your database is a common task. MooseStack provides a few different ways to insert data into your database.
If a table column is modeled as optional in your app type but has a ClickHouse default, Moose treats incoming records as optional at the API/stream boundary, but the ClickHouse table stores the column as required with a DEFAULT clause. If you omit the field in the payload, ClickHouse fills it with the default at insert time.
`field?: number & ClickHouseDefault<"18">` or `WithDefault`
## From a Stream (Streaming Ingest)
When you need to stream data into your ClickHouse tables, you can set the `Stream.destination` as a reference to the `OlapTable` you want to insert into. This will automatically provision a synchronization process that batches and inserts data into the table.
```ts filename="StreamInsert.ts" copy
interface Event {
id: Key;
userId: string;
timestamp: Date;
eventType: string;
}
const eventsTable = new OlapTable("Events");
const stream = new Stream("Events", {
destination: eventsTable // automatically syncs the stream to the table in ClickHouse-optimized batches
});
```
[ClickHouse inserts need to be batched for optimal performance](https://clickhouse.com/blog/asynchronous-data-inserts-in-clickhouse#data-needs-to-be-batched-for-optimal-performance). Moose automatically batches your data into ClickHouse-optimized batches of up to 100,000 records, with automatic flushing every second. It also handles at-least-once delivery and retries on connection errors to ensure your data is never lost.
## From a Workflow (Batch Insert)
If you have data source better suited for batch patterns, use a workflow and the direct `insert()` method to land data into your tables:
```ts filename="WorkflowInsert.ts" copy
interface Event {
id: Key;
userId: string;
timestamp: Date;
eventType: string;
}
const eventsTable = new OlapTable("user_events");
const etlTask = new Task({
name: "ETL",
run: async () => {
const result = await eventsTable.insert([
{ id: "evt_1", userId: "user_123", timestamp: new Date(), eventType: "click" },
{ id: "evt_2", userId: "user_456", timestamp: new Date(), eventType: "view" }
// ... more records of type Event
]);
}
})
)
```
## From a Client App
### Via REST API
In your Moose code, you can leverage the built in [MooseAPI module](/moose/apis) to place a `POST` REST API endpoint in front of your streams and tables to allow you to insert data from external applications.
```ts filename="IngestApi.ts" copy
const ingestApi = new IngestApi("user_events", {
destination: events_stream
});
```
Alternatively, use `IngestPipeline` instead of standalone `IngestApi`, `Stream` `OlapTable` components:
```ts filename="IngestPipeline.ts" copy
const eventsPipeline = new IngestPipeline("user_events", {
ingestApi: true,
stream: true,
table: {
orderByFields: ["id", "timestamp"],
engine: ClickHouseEngines.ReplacingMergeTree,
}
})
```
With these APIs you can leverage the built-in OpenAPI client integration to generate API clients in your own language to connect to your pipelines from external applications.
### Coming Soon: MooseClient
We're working on a new client library that you can use to interact with your Moose pipelines from external applications.
Join the community slack to stay updated and let us know if you're interested in helping us build it.
## Direct Data Insertion
The `OlapTable` provides an `insert()` method that allows you to directly insert data into ClickHouse tables with validation and error handling.
### Inserting Arrays of Records
```ts filename="DirectInsert.ts" copy
interface UserEvent {
id: Key;
userId: string;
timestamp: Date;
eventType: string;
}
const eventsTable = new OlapTable("user_events");
// Insert single record or array of records
const result = await eventsTable.insert([
{ id: "evt_1", userId: "user_123", timestamp: new Date(), eventType: "click" },
{ id: "evt_2", userId: "user_456", timestamp: new Date(), eventType: "view" }
]);
console.log(`Successfully inserted: ${result.successful} records`);
console.log(`Failed: ${result.failed} records`);
```
ClickHouse strongly recommends batching inserts. You should avoid inserting single records in to tables, and consider using Moose Streams and Ingest Pipelines if your data source sends events as individual records.
### Handling Large Batch Inserts
For large datasets, use Node.js streams for memory-efficient processing:
```ts filename="StreamInsert.ts" copy
const dataStream = new Readable({
objectMode: true,
read() {
// Stream implementation
}
});
const result = await eventsTable.insert(dataStream, {
strategy: 'fail-fast' // Note: 'isolate' not supported with streams
});
```
### Validation Methods
Before inserting data, you can validate it using the following methods:
```ts filename="ValidationMethods.ts" copy
// Type guard with compile-time type narrowing
if (eventsTable.isValidRecord(unknownData)) {
// TypeScript now knows unknownData is UserEvent
console.log(unknownData.userId); // Type-safe access
}
// Detailed validation with error reporting
const validationResult = eventsTable.validateRecord(unknownData);
if (validationResult.success) {
console.log("Valid data:", validationResult.data);
} else {
console.log("Validation errors:", validationResult.errors);
}
// Assert validation (throws on failure)
try {
const validData = eventsTable.assertValidRecord(unknownData);
// Use validData with full type safety
} catch (error) {
console.log("Validation failed:", error.message);
}
```
### Error Handling Strategies
Choose from three error handling strategies based on your reliability requirements:
#### Fail-Fast Strategy (Default)
```ts filename="FailFast.ts" copy
// Stops immediately on any error
const result = await eventsTable.insert(data, {
strategy: 'fail-fast'
});
```
#### Discard Strategy
```ts filename="Discard.ts" copy
// Discards invalid records, continues with valid ones
const result = await eventsTable.insert(data, {
strategy: 'discard',
allowErrors: 10, // Allow up to 10 failed records
allowErrorsRatio: 0.05 // Allow up to 5% failure rate
});
```
#### Isolate Strategy
```ts filename="Isolate.ts" copy
// Retries individual records to isolate failures
const result = await eventsTable.insert(data, {
strategy: 'isolate',
allowErrorsRatio: 0.1
});
// Access detailed failure information
if (result.failedRecords) {
result.failedRecords.forEach(failed => {
console.log(`Record ${failed.index} failed: ${failed.error}`);
});
}
```
### Performance Optimization
The insert API includes several performance optimizations:
- **Memoized connections**: ClickHouse clients are reused across insert calls
- **Batch processing**: Optimized batch sizes for large datasets
- **Async inserts**: Automatic async insert mode for datasets > 1000 records
- **Connection management**: Use `close_client()` when completely done
```ts filename="Performance.ts" copy
// For high-throughput scenarios
const result = await eventsTable.insert(largeDataset, {
validate: false, // Skip validation for performance
strategy: 'discard'
});
// Clean up when completely done (optional)
await eventsTable.closeClient();
```
## Best Practices
---
## Creating Materialized Views
Source: moose/olap/model-materialized-view.mdx
Create and configure materialized views for data transformations
# Modeling Materialized Views
## Overview
Materialized views are write-time transformations in ClickHouse. A static `SELECT` populates a destination table from one or more sources. You query the destination like any other table. The `MaterializedView` class wraps [ClickHouse `MATERIALIZED VIEW`](https://clickhouse.com/docs/en/sql-reference/statements/create/view/#create-materialized-view) and keeps the `SELECT` explicit. When you edit the destination schema in code and update the `SELECT` accordingly, Moose applies the corresponding DDL, orders dependent updates, and backfills as needed, so the pipeline stays consistent as you iterate.
In local dev, Moose Migrate generates and applies DDL to your local database.
Today, destination schemas are declared in code and kept in sync manually with your `SELECT`. Moose Migrate coordinates DDL and dependencies when you make those changes. A future enhancement will infer the destination schema from the `SELECT` and update it automatically.
This dependency awareness is critical for [cascading materialized views](https://clickhouse.com/docs/en/sql-reference/statements/create/view/#create-materialized-view-with-dependencies). Moose Migrate [orders DDL across views and tables](https://www.fiveonefour.com/blog/Moose-SQL-Getting-DDL-Dependencies-in-Order) to avoid failed migrations and partial states.
### Basic Usage
```ts filename="BasicUsage.ts" copy
// Define the schema of the transformed rows-- this is static and it must match the results of your SELECT. It also represents the schema of your entire destination table.
interface TargetSchema {
id: string;
average_rating: number;
num_reviews: number;
}
);
```
The ClickHouse `MATERIALIZED VIEW` object acts like a trigger: on new inserts into the source table(s), it runs the SELECT and writes the transformed rows to the destination.
### Quick Reference
```typescript filename="ViewOptions.ts"
interface MaterializedViewConfig {
// Static SELECT that computes the destination rows
selectStatement: string | Sql;
// Tables/views the query reads from
selectTables: (OlapTable | View)[];
// Name of the ClickHouse MATERIALIZED VIEW object
materializedViewName: string;
// Destination table where materialized rows are stored
targetTable?:
| OlapTable
| {
name: string;
engine?: ClickHouseEngines;
orderByFields?: (keyof T & string)[];
};
/** @deprecated prefer targetTable */
tableName?: string;
/** @deprecated prefer targetTable */
engine?: ClickHouseEngines;
/** @deprecated prefer targetTable */
orderByFields?: (keyof T & string)[];
}
```
## Modeling the Target Table
The destination table is where the transformed rows are written by the materialized view. You can model it in two ways:
### Option 1 — Define target table inside the MaterializedView (most cases)
- Simple, co-located lifecycle: the destination table is created/updated/dropped with the MV.
- Best for: projection/denormalization, filtered serving tables, enrichment joins, and most rollups.
```ts filename="InlineTarget.ts" copy
interface Dest { id: string; value: number }
new MaterializedView({
selectStatement: sql`SELECT id, toInt32(value) AS value FROM ${sourceTable}`,
selectTables: [sourceTable],
targetTable: { name: "serving_table", orderByFields: ["id"] }, // MergeTree by default
materializedViewName: "mv_to_serving_table",
});
```
### Option 2 — Decoupled: reference a standalone `OlapTable`
Certain use cases may benefit from a separate lifecycle for the target table that is managed independently from the MV.
```ts filename="DecoupledTarget.ts" copy
interface Dest { id: string; value: number }
const targetTable = new OlapTable("target_table", {
engine: ClickHouseEngines.MergeTree,
orderByFields: ["id"],
});
new MaterializedView({
selectStatement: sql`SELECT id, toInt32(value) AS value FROM ${sourceTable}`,
selectTables: [sourceTable],
targetTable: targetTable,
materializedViewName: "mv_to_target_table",
});
```
### Basic Transformation, Cleaning, Filtering, Denormalization
Create a narrower, query-optimized table from a wide source. Apply light transforms (cast, rename, parse) at write time.
```ts filename="Denormalization.ts" copy
interface Dest { id: string; value: number; created_at: string }
new MaterializedView({
selectStatement: sql`
SELECT id, toInt32(value) AS value, created_at
FROM ${sourceTable}
WHERE active = 1
`,
selectTables: [sourceTable],
targetTable: { name: "proj_table" },
materializedViewName: "mv_to_proj_table",
});
```
### Aggregations
### Simple Additive Rollups
When you want to maintain running sums (counts, totals) that are additive per key, use the `SummingMergeTree` engine:
```ts filename="Summing.ts" copy
interface DailyCounts {
day: string;
user_id: string;
events: number;
}
const stmt = sql`
SELECT
toDate(${events.columns.timestamp}) AS day,
${events.columns.user_id} AS user_id,
count(*) AS events
FROM ${events}
GROUP BY day, user_id
`;
new MaterializedView({
selectStatement: stmt,
selectTables: [events],
targetTable: {
name: "daily_counts",
engine: ClickHouseEngines.SummingMergeTree,
orderByFields: ["day", "user_id"],
},
materializedViewName: "mv_to_daily_counts",
});
```
#### Complex Aggregations
When you want to compute complex aggregation metrics that are not just simple additive operations (sum, count, avg, etc), but instead uses more complex anlaytical functions: (topK,percentile, etc), create a target table with the `AggregatingMergeTree` engine.
```ts filename="AggTransform.ts" copy
interface MetricsById {
id: string;
avg_rating: number & Aggregated<"avg", [number]>;
daily_uniques: number & ClickHouseInt<"uint64"> & Aggregated<"uniqExact", [string]>;
}
// All Aggregate Functions in this query have a [functionName][State]() suffix.
const stmt = sql`
SELECT
${events.columns.id} AS id,
avgState(${events.columns.rating}) AS avg_rating,
uniqExactState(${events.columns.user_id}) AS daily_uniques
FROM ${events}
GROUP BY ${events.columns.id}
`;
new MaterializedView({
selectStatement: stmt,
selectTables: [events],
targetTable: {
name: "metrics_by_id",
engine: ClickHouseEngines.AggregatingMergeTree,
orderByFields: ["id"],
},
materializedViewName: "mv_metrics_by_id",
});
```
Jump to the [Advanced: AggregatingMergeTree transformations](#advanced-aggregatingmergetree-transformations) section for more details.
### Fan-in Patterns
When you have multiple sources that you want to merge into a single destination table, its best to create an OlapTable and reference it in each MV that needs to write to it:
```ts filename="FanIn.ts" copy
interface DailyCounts { day: string; user_id: string; events: number }
// Create the destination table explicitly
const daily = new OlapTable("daily_counts", {
engine: ClickHouseEngines.SummingMergeTree,
orderByFields: ["day", "user_id"],
});
// MV 1 - write to the daily_counts table
const webStmt = sql`SELECT toDate(ts) AS day, user_id, 1 AS events FROM ${webEvents}`;
const mv1 = new MaterializedView({
selectStatement: webStmt,
selectTables: [webEvents],
targetTable: daily,
materializedViewName: "mv_web_to_daily_counts",
});
// MV 2 - write to the daily_counts table
const mobileStmt = sql`SELECT toDate(ts) AS day, user_id, 1 AS events FROM ${mobileEvents}`;
const mv2 = new MaterializedView({
selectStatement: mobileStmt,
selectTables: [mobileEvents],
targetTable: daily,
materializedViewName: "mv_mobile_to_daily_counts",
});
```
### Blue/green schema migrations
Create a new table for a breaking schema change and use an MV to copy data from the old table; when complete, switch reads to the new table and drop just the MV and old table.
For more information on how to use materialized views to perform blue/green schema migrations, see the [Schema Versioning](./schema-versioning) guide.
## Defining the transformation
The `selectStatement` is a static SQL query that Moose runs to transform data from your source table(s) into rows for the destination table.
Transformations are defined as ClickHouse SQL queries. We strongly recommend using the ClickHouse SQL reference and functions overview to help you develop your transformations.
- Use the Moose `sql` template to interpolate tables and columns safely. This gives type-checked column references and prevents runtime parameters.
- Reference tables and columns via objects in your project (e.g., `${sourceTable}`, `${sourceTable.columns.id}`) rather than string literals.
```ts filename="Transformation.ts" copy
interface Dest { id: string; name: string; day: string }
const transformation = sql`
SELECT
${users.columns.id} AS id,
${users.columns.name} AS name,
toDate(${events.columns.ts}) AS day
FROM ${events}
JOIN ${users} ON ${events.columns.user_id} = ${users.columns.id}
WHERE ${events.columns.active} = 1
`;
new MaterializedView({
selectStatement: transformation,
selectTables: [events, users],
targetTable: { name: "user_activity_by_day" },
materializedViewName: "mv_user_activity_by_day",
});
```
The columns returned by your `SELECT` must exactly match the destination table schema.
- Use column aliases (`AS target_column_name`) to align names.
- All destination columns must be present in the `SELECT`, or the materialized view won't be created. Adjust your transformation or table schema so they match.
Go to the [Advanced: Writing SELECT statements to Aggregated tables](#writing-select-statements-to-aggregated-tables) section for more details.
## Backfill Destination Tables
When the MaterializedView is created, Moose backfills the destination once by running your `SELECT` (so you start with a fully populated table).
Materialized views that source from S3Queue tables are **not backfilled** automatically. S3Queue tables only process new files added to S3 after the table is created - there is no historical data to backfill from. The MV will start populating as new files arrive in S3.
You can see the SQL that Moose will run to backfill the destination table when you generate the [Migration Plan](./migration-plan).
During dev mode, as soon as you save the MaterializedView, Moose will run the backfill and you can see the results in the destination table by querying it in your local ClickHouse instance.
## Query Destination Tables
You can query the destination table like any other table.
For inline or decoupled target tables, you can reference target table columns and tables directly in your queries:
```ts filename="Query.ts" copy
// Inline-defined destination table from earlier examples
const q = sql`
SELECT
${mv.targetTable.columns.id},
${mv.targetTable.columns.value}
FROM ${mv.targetTable}
ORDER BY ${mv.targetTable.columns.id}
LIMIT 10`;
```
If you define your target table outside of the MaterializedView, you can also just reference the table by its variable name in your queries:
```ts filename="QueryDecoupled.ts" copy
const targetTable = new OlapTable<{ id: string; average_rating: number }>("target_table")
// Assuming `targetTable` is the OlapTable you created explicitly
const q = sql`
SELECT ${targetTable.columns.id}, ${targetTable.columns.average_rating}
FROM ${targetTable}
WHERE ${targetTable.columns.id} = 'abc'
`;
```
Go to the [Querying Aggregated tables](#querying-aggregated-tables) section for more details on how to query Aggregated tables.
## Advanced: Aggregations + Materialized Views
This section dives deeper into advanced patterns and tradeoffs when building aggregated materialized views.
### Target Tables with `AggregatingMergeTree`
When using an `AggregatingMergeTree` target table, you must use the `AggregateFunction` type to model the result of the aggregation functions:
```ts filename="AggTransform.ts" copy
interface MetricsById {
id: string;
/**
* Result of avgState(events.rating)
* - avgState(number) returns number, so model the type as number
* - Aggregated arg type is [number] because the column (events.rating) is a number
* - Aggregated function name is "avg"
*/
avg_rating: number & Aggregated<"avg", [number]>;
/**
* Result of uniqExactState(events.user_id)
* - uniqExact returns an integer; use number & ClickHouseInt<"uint64"> for precision
* - Aggregated arg type is [string] because the column (events.user_id) is a string
* - Aggregated function name is "uniqExact"
*/
daily_uniques: number & ClickHouseInt<"uint64"> & Aggregated<"uniqExact", [string]>;
}
// All Aggregate Functions in this query have a [functionName][State]() suffix.
const stmt = sql`
SELECT
${events.columns.id} AS id,
avgState(${events.columns.rating}) AS avg_rating,
uniqExactState(${events.columns.user_id}) AS daily_uniques
FROM ${events}
GROUP BY ${events.columns.id}
`;
new MaterializedView({
selectStatement: stmt,
selectTables: [events],
targetTable: {
name: "metrics_by_id",
engine: ClickHouseEngines.AggregatingMergeTree,
orderByFields: ["id"],
},
materializedViewName: "mv_metrics_by_id",
});
```
- Using `avg()`/`uniqExact()` in the SELECT instead of `avgState()`/`uniqExactState()`
- Forgetting to annotate the schema with `Aggregated<...>` so the target table can be created correctly
- Mismatch between `GROUP BY` keys in your `SELECT` and the `orderByFields` of your target table
### Modeling columns with `AggregateFunction`
- Pattern: `U & Aggregated<"agg_func_name", [Types]>`
- `U` is the read-time type (e.g., `number`, `string`)
- `agg_func_name` is the aggregation name (e.g., `avg`, `uniqExact`)
- `Types` are the argument types. These are the types of the columns that are being aggregated.
```ts filename="FunctionToTypeMapping.ts" copy
number & Aggregated<"avg", [number]> // avgState(col: number)
number & ClickHouseInt<"uint64"> & Aggregated<"uniqExact", [string]> // uniqExactState(col: string)
number & ClickHouseInt<"uint64"> & Aggregated<"count", []> // countState(col: any)
string & Aggregated<"argMax", [string, Date]> // argMaxState(col: string, value: Date)
string & Aggregated<"argMin", [string, Date]> // argMinState(col: string, value: Date)
number & Aggregated<"corr", [number, number]> // corrState(col1: number, col2: number)
```
### Writing SELECT statements to Aggregated tables
When you write to an `AggregatingMergeTree` table, you must add a `State` suffix to the aggregation functions in your `SELECT` statement.
```ts filename="AggTransform.ts" copy
interface MetricsById {
id: string;
avg_rating: number & Aggregated<"avg", [number]>;
total_reviews: number & Aggregated<"sum", [number]>;
}
const aggStmt = sql`
SELECT
${reviews.columns.id} AS id,
avgState(${reviews.columns.rating}) AS avg_rating,
countState(${reviews.columns.id}) AS total_reviews
FROM ${reviews}
GROUP BY ${reviews.columns.id}
`;
const mv = new MaterializedView({
selectStatement: aggStmt,
selectTables: [reviews],
targetTable: {
name: "metrics_by_id",
engine: ClickHouseEngines.AggregatingMergeTree,
orderByFields: ["id"],
},
materializedViewName: "mv_metrics_by_id",
});
```
Why states? Finalized values (e.g., `avg()`) are not incrementally mergeable. Storing states lets ClickHouse maintain results efficiently as new data arrives. Docs: https://clickhouse.com/docs/en/sql-reference/aggregate-functions/index and https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators#-state
### Querying Aggregated Tables
When you query a table with an `AggregatingMergeTree` engine, you must use aggregate functions with the `Merge` suffix (e.g., `avgMerge`) `or rely on Moose’s `Aggregated` typing plus `sql` to auto-finalize at query time.
```ts filename="QueryAgg.ts" copy
// Auto-finalized via Aggregated + sql
const cols = mv.targetTable.columns; // mv from earlier Agg example
const autoFinalized = sql`
SELECT ${cols.avg_rating}, ${cols.total_reviews}
FROM ${mv.targetTable}
WHERE ${cols.id} = '123'
`;
// Manual finalization (explicit ...Merge)
const manual = sql`
SELECT
avgMerge(avg_rating) AS avg_rating,
countMerge(total_reviews) AS total_reviews
FROM metrics_by_id
WHERE id = '123'
`;
```
## Choosing the right engine
- Use `MergeTree` for copies/filters/enrichment without aggregation semantics.
- Use `SummingMergeTree` when all measures are additive, and you want compact, eventually-consistent sums.
- Use `AggregatingMergeTree` for non-additive metrics and advanced functions; store states and finalize on read.
- Use `ReplacingMergeTree` for dedup/upserts or as an idempotent staging layer before rollups.
---
## Modeling Tables
Source: moose/olap/model-table.mdx
Model your database schema in code using native TypeScript/Python typing
# Modeling Tables
## Overview
Tables in Moose let you define your database schema entirely in code using native TypeScript/Python typing.
You can integrate tables into your pipelines as destinations for new data or as sources for analytics queries in your downstream transformations, APIs, and more.
```ts filename="FirstTable.ts" copy
interface MyFirstTable {
id: Key;
name: string;
age: number;
}
// Create a table named "first_table"
);
// For deduplication, use the ReplacingMergeTree factory
);
// Now you can:
// - Write to this table from streams
// - Query it directly
// - Use it as a source for materialized views
```
### Creating Tables in Ingestion Pipelines
For end-to-end data flows, create tables as part of an ingestion pipeline:
```ts filename="PipelineTable.ts"
// Define your schema
interface UserEvent {
id: Key;
userId: string;
timestamp: Date;
eventType: string;
}
// Create a complete ingestion pipeline with a table
const eventsPipeline = new IngestPipeline("user_events", {
ingestApi: true, // Creates a REST API endpoint at POST localhost:4000/ingest/user_events
stream: true, // Creates Kafka/Redpanda topic
table: { // Creates and configures the table named "user_events"
orderByFields: ["id", "timestamp"]
}
});
// Access the table component when needed
const eventsTable = eventsPipeline.table;
```
## Data Modeling
### Special ClickHouse Types (LowCardinality, Nullable, etc)
```ts filename="ClickHouseTypes.ts" copy
export interface ClickHouseOptimizedExample {
id: Key;
stringField: string;
numberField: number;
decimalField: Decimal<10, 2>; // Precise decimal storage
// Alternative: decimalField: string & ClickHouseDecimal<10, 2>; // Verbose syntax still works
lowCardinalityField: string & LowCardinality; // Faster queries for enum-like data
nestedObject: {
innerString: string;
innerNumber: number;
};
namedTupleField: {
name: string;
value: number;
} & ClickHouseNamedTuple; // Optimized nested storage
numberArray: number[];
mapField: Record;
literalField: "optionA" | "optionB";
optionalField?: string; // Nullable field
dateField: Date;
}
```
### Default values
Use defaults instead of nullable columns to keep queries fast and schemas simple. You can specify defaults at the column level so Moose generates ClickHouse defaults in your table DDL.
```ts filename="Defaults.ts" copy
interface Event {
id: Key;
// Static defaults (ClickHouse expression as a string literal)
status: string & ClickHouseDefault<"'pending'">; // DEFAULT 'pending'
retries: number & ClickHouseDefault<"0">; // DEFAULT 0
// Server-side timestamps
createdAt: Date & ClickHouseDefault<"now()">; // DEFAULT now()
// Decimal with default
amount: Decimal<10, 2> & ClickHouseDefault<"0">;
// Alternative: amount: (string & ClickHouseDecimal<10, 2> & ClickHouseDefault<"0">); // Verbose syntax
}
);
```
The value passed into the `ClickHouseDefault<"">` tag can either be a string literal or a stringified ClickHouse SQL expression. If you run into typing issues specifically on `Date` fields with `ClickHouseDefault`, use `WithDefault` as a fallback workaround.
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`) → 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.
### Database Selection
By default, tables are created in the database specified in your `moose.config.toml` ClickHouse configuration. You can override this on a per-table basis using the `database` field:
```ts filename="DatabaseOverride.ts" copy
interface UserData {
id: Key;
name: string;
email: string;
}
// Table in default database (from moose.config.toml)
const defaultTable = new OlapTable("users");
// Table in specific database (e.g., "analytics")
const analyticsTable = new OlapTable("users", {
database: "analytics",
orderByFields: ["id"]
});
```
To use custom databases, configure them in your `moose.config.toml`:
```toml
[clickhouse_config]
db_name = "local"
additional_databases = ["analytics", "staging"]
```
The databases in `additional_databases` will be created automatically when you start your Moose application.
### Primary Keys and Sorting
You must configure table indexing using one of these approaches:
1. Define at least one `Key` in your table schema
2. Specify `orderByFields` in the table config
3. Use both (all `Key` fields must come first in the `orderByFields` array)
```ts filename="PrimaryKeyConfig.ts" copy
// Approach 1: Using primary key only
interface Record1 {
id: Key; // Primary key field
field1: string;
field2: number;
}
const table1 = new OlapTable("table1"); // id is the primary key
```
### Order By Fields Only
```ts filename="OrderByFieldsOnly.ts" copy
// Approach 2: Using orderByFields only
interface SchemaWithoutPrimaryKey {
field1: string;
field2: number;
field3: Date;
}
const tableWithOrderByFieldsOnly = new OlapTable("table2", {
orderByFields: ["field1", "field2"] // Specify ordering without primary key
});
```
### Order By Expression
Use a ClickHouse SQL expression to control ordering directly. This is useful for advanced patterns (functions, transformations) or when you want to disable sorting entirely.
```ts filename="OrderByExpression.ts" copy
// Use a ClickHouse expression for ORDER BY
interface Events {
userId: string;
createdAt: Date;
eventType: string;
}
const tableWithOrderByExpression = new OlapTable("events", {
// Equivalent to orderByFields: ["userId", "createdAt", "eventType"]
orderByExpression: "(userId, createdAt, eventType)",
});
// Advanced: functions inside expression
const tableWithMonthBucketing = new OlapTable("events_by_month", {
orderByExpression: "(userId, toYYYYMM(createdAt))",
});
// No sorting (ClickHouse tuple() means empty ORDER BY)
const unsortedTable = new OlapTable("events_unsorted", {
orderByExpression: "tuple()",
});
```
### Using Both Primary Key and Order By Fields
```ts filename="ComboKeyAndOrderByFields.ts" copy
// Approach 3: Using both (primary key must be first)
interface SchemaWithKey {
id: Key; // Primary key field
field1: string;
field2: number;
}
const tableWithKeyAndOrderByFields = new OlapTable("table3", {
orderByFields: ["id", "field1"] // Primary key must be first
});
```
### Using Multiple Primary Keys
```ts filename="MultiKeyTable.ts" copy
interface MultiKeyRecord {
key1: Key;
key2: Key;
field1: string;
}
const multiKeyTable = new OlapTable("multi_key_table", {
orderByFields: ["key1", "key2", "field1"] // Multiple keys must come first
});
```
### Table engines
By default, Moose will create tables with the `MergeTree` engine. You can use different engines by setting the `engine` in the table configuration.
```ts filename="TableEngine.ts" copy
// Default MergeTree engine
const table = new OlapTable("table", {
orderByFields: ["id"]
});
// Use engine configuration for other engines
const dedupTable = new OlapTable("table", {
engine: ClickHouseEngines.ReplacingMergeTree,
orderByFields: ["id"],
ver: "version", // Optional: keeps row with highest version
isDeleted: "deleted" // Optional: soft delete when deleted=1
});
```
#### Deduplication (`ReplacingMergeTree`)
Use the `ReplacingMergeTree` engine to keep only the latest record for your designated sort key:
```ts filename="DeduplicatedTable.ts" copy
// Basic deduplication
const table = new OlapTable("table", {
engine: ClickHouseEngines.ReplacingMergeTree,
orderByFields: ["id"]
});
// With version column (keeps record with highest version)
const versionedTable = new OlapTable("table", {
engine: ClickHouseEngines.ReplacingMergeTree,
orderByFields: ["id"],
ver: "updated_at" // Column that determines which version to keep
});
// With soft deletes (requires ver parameter)
const softDeleteTable = new OlapTable("table", {
engine: ClickHouseEngines.ReplacingMergeTree,
orderByFields: ["id"],
ver: "updated_at",
isDeleted: "deleted" // UInt8 column: 1 marks row for deletion
});
```
ClickHouse's ReplacingMergeTree engine runs deduplication in the background AFTER data is inserted into the table. This means that duplicate records may not be removed immediately.
**Version Column (`ver`)**: When specified, ClickHouse keeps the row with the maximum version value for each unique sort key.
**Soft Deletes (`is_deleted`)**: When specified along with `ver`, rows where this column equals 1 are deleted during merges. This column must be UInt8 type.
For more details, see the [ClickHouse documentation](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/replacingmergetree).
#### Streaming from S3 (`S3Queue`)
Use the `S3Queue` engine to automatically ingest data from S3 buckets as files are added:
S3Queue tables only process **new files** added to S3 after table creation. When used as a source for materialized views, **no backfill occurs** - the MV will only start populating as new files arrive. See the [Materialized Views documentation](./model-materialized-view#backfill-destination-tables) for more details.
```ts filename="S3StreamingTable.ts" copy
// Use direct configuration (S3Queue does not support orderByFields)
);
```
S3Queue is a streaming engine and does not support `orderByFields` or ORDER BY clauses. Configure only engine-specific parameters like `s3Path`, `format`, and `settings`.
**Security Risk**: Hardcoding credentials in your code embeds them in Docker images and deployment artifacts, creating serious security vulnerabilities.
**Solution**: Use `mooseRuntimeEnv` for runtime credential resolution:
```ts filename="SecureS3Streaming.ts" copy
// ✅ RECOMMENDED: Runtime environment variable resolution
);
```
**Then set environment variables:**
```bash filename="Terminal" copy
export AWS_ACCESS_KEY_ID="AKIA..."
export AWS_SECRET_ACCESS_KEY="your-secret-key"
moose prod up
```
**Benefits:**
- Credentials never embedded in Docker images
- Supports credential rotation (changing passwords triggers table recreation)
- Different credentials per environment (dev/staging/prod)
- Clear error messages if environment variables are missing
S3Queue requires ClickHouse 24.7+ and proper ZooKeeper/ClickHouse Keeper configuration for coordination between replicas. Files are processed exactly once across all replicas.
#### Direct S3 Access (`S3`)
Use the `S3` engine for direct read/write access to S3 storage without streaming semantics:
```ts filename="S3Table.ts" copy
// S3 table with credentials (recommended with mooseRuntimeEnv)
);
// Public S3 bucket (no authentication)
);
```
- **S3**: Direct read/write access to S3 files. Use for batch processing or querying static data.
- **S3Queue**: Streaming engine that automatically processes new files as they arrive. Use for continuous data ingestion.
Both engines support the same credential management and format options.
#### In-Memory Buffer (`Buffer`)
The `Buffer` engine provides an in-memory buffer that flushes data to a destination table based on time, row count, or size thresholds:
```ts filename="BufferTable.ts" copy
// First create the destination table
);
// Then create buffer that writes to it
);
```
- Data in buffer is **lost if server crashes** before flush
- Not suitable for critical data that must be durable
- Best for high-throughput scenarios where minor data loss is acceptable
- Buffer and destination table must have identical schemas
- Cannot use `orderByFields`, `partitionBy`, or `sampleByExpression` on buffer tables
For more details, see the [ClickHouse Buffer documentation](https://clickhouse.com/docs/en/engines/table-engines/special/buffer).
#### Distributed Tables (`Distributed`)
The `Distributed` engine creates a distributed table across a ClickHouse cluster for horizontal scaling:
```ts filename="DistributedTable.ts" copy
// Distributed table across cluster
);
```
- Requires a configured ClickHouse cluster with remote_servers configuration
- The local table must exist on all cluster nodes
- Distributed tables are virtual - data is stored in local tables
- Cannot use `orderByFields`, `partitionBy`, or `sampleByExpression` on distributed tables
- The `cluster` name must match a cluster defined in your ClickHouse configuration
For more details, see the [ClickHouse Distributed documentation](https://clickhouse.com/docs/en/engines/table-engines/special/distributed).
#### Replicated Engines
Replicated engines provide high availability and data replication across multiple ClickHouse nodes. Moose supports all standard replicated MergeTree variants:
- `ReplicatedMergeTree` - Replicated version of MergeTree
- `ReplicatedReplacingMergeTree` - Replicated with deduplication
- `ReplicatedAggregatingMergeTree` - Replicated with aggregation
- `ReplicatedSummingMergeTree` - Replicated with summation
```ts filename="ReplicatedEngines.ts" copy
// Basic replicated table with explicit paths
const replicatedTable = new OlapTable("records", {
engine: ClickHouseEngines.ReplicatedMergeTree,
keeperPath: "/clickhouse/tables/{database}/{shard}/records",
replicaName: "{replica}",
orderByFields: ["id"]
});
// Replicated with deduplication
const replicatedDedup = new OlapTable("dedup_records", {
engine: ClickHouseEngines.ReplicatedReplacingMergeTree,
keeperPath: "/clickhouse/tables/{database}/{shard}/dedup_records",
replicaName: "{replica}",
ver: "updated_at",
isDeleted: "deleted",
orderByFields: ["id"]
});
// For ClickHouse Cloud or Boreal (no parameters needed)
const cloudReplicated = new OlapTable("cloud_records", {
engine: ClickHouseEngines.ReplicatedMergeTree,
orderByFields: ["id"]
});
```
The `keeper_path` and `replica_name` parameters are **optional** for replicated engines:
- **Omit both parameters** (recommended): Moose uses smart defaults that work in both ClickHouse Cloud and self-managed environments. The default path pattern `/clickhouse/tables/{uuid}/{shard}` with replica `{replica}` works automatically with Atomic databases (default in modern ClickHouse).
- **Provide custom paths**: You can still specify both parameters explicitly if you need custom replication paths for your self-managed cluster.
**Note**: Both parameters must be provided together, or both omitted. The `{uuid}`, `{shard}`, and `{replica}` macros are automatically substituted by ClickHouse at runtime.
For more details, see the [ClickHouse documentation on data replication](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/replication).
## Externally Managed Tables
If you have a table that is managed by an external system (e.g Change Data Capture like ClickPipes), you can still use Moose to query it. You can set the config in the table config to set the lifecycle to `EXTERNALLY_MANAGED`.
```ts filename="ExternallyManagedTable.ts" copy
// Table managed by external system
const externalTable = new OlapTable("external_users", {
orderByFields: ["id", "timestamp"],
lifeCycle: LifeCycle.EXTERNALLY_MANAGED // Moose won't create or modify this table
});
```
Learn more about the different lifecycle options and how to use them in the [LifeCycle Management](/stack/olap/lifecycle) documentation.
## Invalid Configurations
```ts filename="InvalidConfig.ts" copy
// Error: No primary key or orderByFields
interface BadRecord1 {
field1: string;
field2: number;
}
const badTable1 = new OlapTable("bad_table1");
// Error: Primary key not first in orderByFields
interface BadRecord2 {
id: Key;
field1: string;
}
const badTable2 = new OlapTable("bad_table2", {
orderByFields: ["field1", "id"] // Wrong order - primary key must be first
});
// Error: Nullable field in orderByFields
interface BadRecord3 {
id: Key;
field1: string;
field2?: number;
}
const badTable3 = new OlapTable("bad_table3", {
orderByFields: ["id", "field2"] // Can't have nullable field in orderByFields
});
```
## Development Workflow
### Local Development with Hot Reloading
One of the powerful features of Moose is its integration with the local development server:
1. Start your local development server with `moose dev`
2. When you define or modify an `OlapTable` in your code and save the file:
- The changes are automatically detected
- The TypeScript compiler plugin processes your schema definitions
- The infrastructure is updated in real-time to match your code changes
- Your tables are immediately available for testing
For example, if you add a new field to your schema:
```ts filename="HotReloading.ts" copy
// Before
interface BasicSchema {
id: Key;
name: string;
}
// After adding a field
interface BasicSchema {
id: Key;
name: string;
createdAt: Date; // New field
}
```
The Moose framework will:
1. Detect the change when you save the file
2. Update the table schema in the local ClickHouse instance
3. Make the new field immediately available for use
### Verifying Your Tables
You can verify your tables were created correctly using:
```bash filename="Terminal" copy
# List all tables in your local environment
moose ls
```
#### Connecting to your local ClickHouse instance
You can connect to your local ClickHouse instance with your favorite database client. Your credentials are located in your `moose.config.toml` file:
```toml filename="moose.config.toml" copy
[clickhouse_config]
db_name = "local"
user = "panda"
password = "pandapass"
use_ssl = false
host = "localhost"
host_port = 18123
native_port = 9000
```
---
## Modeling Views
Source: moose/olap/model-view.mdx
Define standard ClickHouse Views for read-time projections
# Modeling Views
## Overview
Views are read-time projections in ClickHouse. A static `SELECT` defines the view over one or more base tables or other views. Moose wraps [ClickHouse `VIEW`](https://clickhouse.com/docs/en/sql-reference/statements/create/view) with a simple `View` class in TypeScript. You provide the view name, the `SELECT`, and the list of source tables/views so Moose can order DDL correctly during migrations.
Use `View` when you want a virtual read-time projection and don’t need write-time transformation or a separate storage table. For write-time pipelines and backfills, use a Materialized View instead.
## Basic Usage
```ts filename="BasicUsage.ts" copy
`.
---
## Planned Migrations (OLAP)
Source: moose/olap/planned-migrations.mdx
Generate, review, and safely execute ClickHouse DDL plans
# Planned Migrations
Migration planning is a new way to have more fine-grained control over HOW database schema changes are applied to your database when you deploy your code into production.
## Why planned migrations?
Most database migrations are designed under the assumption that your code is the sole owner of the database schema. In OLAP databases, we have to be more careful and assume that schema changes can happen at any time:
- The database schema is shared with other services (e.g. Change Data Capture services like ClickPipes)
- Other users (e.g. analysts) of the database may have credentials that let them change the schema
This is why the plan is generated from the remote environment, and validated against the live state of the database at the time of deployment. If it detects a drift, it will abort the deployment and require you to regenerate the plan, to make sure you are not dropping data unintentionally.
Planned migrations apply only to OLAP (ClickHouse) schema changes. Streaming, APIs, and processes are unaffected by this flow.
## What this does
- Generates an ordered set of ClickHouse operations and writes them to `./migrations/plan.yaml`
- Saves two validation snapshots for drift detection:
- `./migrations/remote_state.json` (state when plan was created)
- `./migrations/local_infra_map.json` (desired state from your local code)
- When enabled, validates state and executes the exact reviewed operations
## Prerequisites
```toml file="moose.config.toml"
[features]
olap = true
ddl_plan = true
```
## Generating a Plan
Once done editing your code in your feature branch, you can generate a plan that diffs your local code against your live remote database:
**For Moose server deployments:**
```bash filename="Terminal" copy
moose generate migration --url https:// --token --save
```
**For serverless deployments:**
```bash filename="Terminal" copy
moose generate migration --clickhouse-url clickhouse://user:pass@host:port/db --save
```
Outputs:
```text
./migrations/plan.yaml
./migrations/remote_state.json
./migrations/local_infra_map.json
```
What each file contains:
- `remote_state.json`: The state of the remote database when the plan was generated.
- `local_infra_map.json`: The state of the local code when the plan was generated.
- `plan.yaml`: The plan to apply to the remote database based on the diff between the two states.
You will commit the entire `migrations/` directory to version control, and Moose will automatically apply the plan when you deploy the code to production.
## Review and edit the plan
Moose makes some assumptions about your schema changes, such as renaming a column instead of dropping and adding. You can modify the plan to override these assumptions.
Open `plan.yaml` in your PR. Operations are ordered (teardown first, then setup) to avoid dependency issues. Review like regular code. You can also edit the plan to override the default assumptions Moose makes.
```yaml filename="migrations/plan.yaml" copy
# Drop a deprecated column
- DropTableColumn:
table: "events"
column_name: "deprecated_field"
# Rename a column to match code
- RenameTableColumn:
table: "events"
before_column_name: "createdAt"
after_column_name: "created_at"
# Add a new nullable column after created_at
- AddTableColumn:
table: "events"
column:
name: "status"
data_type: "String"
required: false
unique: false
primary_key: false
default: null
annotations: []
comment: null
after_column: "created_at"
# Change a column type to Nullable(Float64)
- ModifyTableColumn:
table: "events"
before_column:
name: "value"
data_type: "Float64"
required: false
unique: false
primary_key: false
default: null
annotations: []
comment: null
after_column:
name: "value"
data_type:
Nullable:
nullable: "Float64"
required: false
unique: false
primary_key: false
default: null
annotations: []
comment: null
# Create a simple view via raw SQL
- RawSql:
sql:
- "CREATE VIEW IF NOT EXISTS `events_by_user` AS SELECT user_id, count() AS c FROM events GROUP BY user_id"
description: "Creating view events_by_user"
```
You can edit the plan to override the default assumptions Moose makes.
### When to edit the plan
There are two main reasons to edit the plan:
1. To "override" the default assumptions Moose makes when it cannot infer the intent of your schema changes, such as renaming a column instead of dropping and adding.
2. To add new operations that are not covered by the default assumptions, such as adding a backfill operation to a new column.
#### Rename a column instead of drop/add
When you rename a column, Moose will default to dropping and adding the column. However, you can override this by using the `RenameTableColumn` operation:
```ts
interface SourceSchema {
created_at: Date;
color: string;
}
interface TargetSchema {
createdAt: Date;
color: string;
}
sourceTable = new OlapTable("source_table");
targetTable = new OlapTable("target_table");
```
```yaml filename="migrations/plan.yaml" copy
- DropTableColumn:
table: source_table
column_name: created_at
- AddTableColumn:
table: source_table
column:
name: createdAt
data_type: DateTime
required: true
unique: false
primary_key: false
default: null
annotations: []
after_column: color
```
In the plan, you can override this by using the `RenameTableColumn` operation:
```yaml filename="migrations/plan.yaml" copy
created_at: 2025-08-20T05:35:31.668353Z
- RenameTableColumn:
table: source_table
before_column_name: created_at
after_column_name: createdAt
```
#### Add a backfill operation to a new column
When you add a new column, Moose will default to backfilling the column based on the value in the `default` field.
If your field is a `DateTime`, you can edit the plan to set the default value to the current timestamp:
```yaml filename="migrations/plan.yaml" copy
- AddTableColumn:
table: "source_table"
column:
name: "created_at"
data_type: "DateTime"
required: false
unique: false
default: NOW ## Specify the default value to the current timestamp
```
You can also override the the default behavior by using the `RawSql` operation to define your own custom backfill logic:
```yaml filename="migrations/plan.yaml" copy
- AddTableColumn:
table: "source_table"
column:
name: "created_at"
data_type: "DateTime"
required: false
unique: false
default: null
- RawSql:
sql:
- "UPDATE events SET created_at = toDateTime(created_at_ms / 1000) WHERE created_at IS NULL"
description: "Backfill created_at from created_at_ms"
```
## Deployment Flows
### Moose Server Deployments
For Moose server deployments (with `moose prod` running), migrations are applied automatically on startup. Generate plans using:
```bash filename="Terminal" copy
moose generate migration --url https:// --token --save
```
When you deploy, Moose validates the plan and executes it automatically.
### Serverless Deployments
For serverless deployments (no Moose server), you manage migrations manually using the ClickHouse connection directly:
```toml file="moose.config.toml"
[state_config]
storage = "clickhouse"
[features]
olap = true
data_model_v2 = true
```
**Workflow:**
1. **Generate the plan** from your ClickHouse database:
```bash filename="Terminal" copy
moose generate migration --clickhouse-url --save
```
2. **Review** the generated `./migrations/` files in your PR
3. **Execute the plan** against your ClickHouse with CI/CD or manually:
```bash filename="Terminal" copy
moose migrate --clickhouse-url
```
Before applying the plan, Moose will first validate that the snapshot of your database that was taken when you generated the plan is still the same as the current database state. If it is not, Moose will abort the deployment. If it is, Moose will execute the plan in `plan.yaml` against your production database.
Execution rules:
- If current tables in your live production database differ from `remote_state.json`, Moose aborts (remote drift since planning).
- If desired tables in your local code differ from `local_infra_map.json`, Moose aborts (code changed since planning).
- If both match, `plan.yaml` operations are executed in order against ClickHouse.
## Troubleshooting
- Failure to connect to remote database? Make sure you have [your admin API key setup correctly](./apis/auth#admin-endpoints)
- Plan rejected due to drift: Re-generate a plan against the current remote, review, and retry.
- No execution in moose server deployments: Ensure `ddl_plan = true` and `./migrations/plan.yaml` exists.
- OLAP disabled: Ensure `[features].olap = true`.
---
## Querying Data
Source: moose/olap/read-data.mdx
Query OLAP tables using SQL with type safety
# Querying Data
Moose provides type-safe SQL querying for your `OlapTable` and `MaterializedView` instances. Use cases include:
- Building APIs to expose your data to client/frontend applications
- Building transformation pipelines inside your database with materialized views
## Querying with MooseClient
Use `MooseClient` to query data from existing tables and materialized views.
### Basic Querying
```ts filename="BasicQuerying.ts"
const client = new MooseClient();
// Query existing table
const query = sql`
SELECT id, name, email
FROM ${UserTable}
WHERE status = 'active'
LIMIT 10
`;
const result = await client.query.execute(query);
const data = await result.json();
```
### Querying Materialized Views
```ts filename="QueryMaterializedView.ts"
const client = new MooseClient();
// Query existing materialized view
const query = sql`
SELECT user_id, total_orders, average_order_value
FROM user_stats_view
WHERE total_orders > 10
ORDER BY average_order_value DESC
`;
const result = await client.query.execute(query);
```
## Select With Column and Table References
```ts filename="TypedReferences.ts"
// Reference table columns with type safety
const cols = UserTable.columns;
const query = sql`
SELECT
${cols.id},
${cols.name},
${cols.email}
FROM ${UserTable}
WHERE ${cols.status} = 'active'
`;
// Multiple table references
const joinQuery = sql`
SELECT
${UserTable.columns.id},
${UserTable.columns.name},
${OrderTable.columns.order_value}
FROM ${UserTable}
JOIN ${OrderTable} ON ${UserTable.columns.id} = ${OrderTable.columns.user_id}
`;
```
When you query a materialized view, you reference the `MaterializedView.targetTable` to get the columns of the target table.
```ts filename="TypedReferences.ts"
const query = sql`
SELECT
${ExampleMaterializedView.targetTable.columns.id},
${ExampleMaterializedView.targetTable.columns.name},
${ExampleMaterializedView.targetTable.columns.email}
FROM ${ExampleMaterializedView.targetTable}
`;
```
In ClickHouse, when you query a Materialized View that has columns of type `AggregateFunction` in the result set, ordinarily you would need to run:
```sql
SELECT sumMerge(amount) FROM {ExampleMaterializedView}
```
When querying this with Moose, you can just reference the column name in the `sql` template literal. The interpolation will be replaced with the correct ClickHouse function:
```ts filename="TypedReferences.ts"
const query = sql`
SELECT ${ExampleMaterializedView.targetTable.columns.amount}
FROM ${ExampleMaterializedView.targetTable}
`;
// This will be replaced with:
// SELECT sumMerge(amount) FROM {ExampleMaterializedView}
```
## Filtering with WHERE Clauses
```ts filename="WhereClauses.ts"
// Multiple WHERE conditions
const filterQuery = sql`
SELECT ${UserTable.columns.id}, ${UserTable.columns.name}
FROM ${UserTable}
WHERE ${UserTable.columns.status} = 'active'
AND ${UserTable.columns.created_at} > '2024-01-01'
AND ${UserTable.columns.email} ILIKE ${'%' + searchTerm + '%'}
`;
// Using IN clauses
const inQuery = sql`
SELECT * FROM ${UserTable}
WHERE ${UserTable.columns.id} IN (${userIds})
`;
// Using BETWEEN
const rangeQuery = sql`
SELECT * FROM ${UserTable}
WHERE ${UserTable.columns.age} BETWEEN ${minAge} AND ${maxAge}
`;
```
## Dynamic Query Building
Use the `sql` template literal to build safe queries:
```ts filename="SqlTemplateLiterals.ts"
// Safe interpolation with sql template literal
const status = 'active';
const limit = 10;
const query = sql`
SELECT id, name, email
FROM ${UserTable}
WHERE ${UserTable.columns.status} = ${status}
LIMIT ${limit}
`;
// Conditional WHERE clauses
interface FilterParams {
minAge?: number;
status?: "active" | "inactive";
searchText?: string;
}
const buildConditionalQuery = (filters: FilterParams) => {
let conditions = [];
if (filters.minAge !== undefined) {
conditions.push(sql`age >= ${filters.minAge}`);
}
if (filters.status) {
conditions.push(sql`status = ${filters.status}`);
}
if (filters.searchText) {
conditions.push(sql`(name ILIKE ${'%' + filters.searchText + '%'} OR email ILIKE ${'%' + filters.searchText + '%'})`);
}
let query = sql`SELECT * FROM ${UserTable}`;
if (conditions.length > 0) {
query = sql`${query} WHERE ${conditions.join(' AND ')}`;
}
return sql`${query} ORDER BY created_at DESC`;
};
```
## Building APIs
To build REST APIs that expose your data, see the [Bring Your Own API Framework documentation](/moose/app-api-frameworks) for comprehensive examples and patterns using Express, Koa, Fastify, or FastAPI.
## Common Pitfalls
- **Column name typos**: Use `UserTable.columns.columnName` for autocomplete
- **Type mismatches**: Ensure your schema types match ClickHouse types
- **Missing imports**: Import your table definitions before using them
- **Template literal syntax**: Use backticks `sql` not regular strings
- **Forgetting await**: Always await `client.query.execute()`
## Performance Optimization
If your query is slower than expected, there are a few things you can check:
- If using filters, try to filter on a column that is defined in the `orderByFields` of the table
- For common queries, consider [creating a materialized view](/stack/olap/create-materialized-view) to pre-compute the result set
## Further Reading
---
## moose/olap/schema-change
Source: moose/olap/schema-change.mdx
# Handling Failed Migrations
One of the main benefits of the Moose local development environment is that you can detect breaking schema changes before they happen in production. This can be specifically useful for identifying incompatible data type changes when you change a column's data type and the generated migration cannot cast the existing data to the new type.
This page describes how to recover from a failed migration in dev and gives a playbook for safely achieving the desired type change.
## What happened
You changed a column’s data type on a table that already has data. The dev migration tried to run an in-place ALTER and ClickHouse created a mutation that failed (incompatible cast, nullability, defaults, etc.).
Symptoms:
- Failed migration in dev
- A stuck mutation on the table
- Reverting your code type alone doesn’t help until the mutation is cleared
## Quick recovery (dev)
Follow these steps to get unblocked quickly.
### View the terminal logs to see the failing mutation
In your terminal, you should see a message like this:
```txt
⢹ Processing Infrastructure changes from file watcher
~ Table events:
Column changes:
~ value: String -> Float64
Applying: ALTER TABLE events MODIFY COLUMN value Float64
ClickHouse mutation created: mutation_id='00000001-0000-4000-8000-000000000123'
Error: Code: 368. Conversion failed: cannot parse 'abc' as Float64 (column: value)
Status: mutation failed; table may be partially transformed
```
Copy the mutation ID from the terminal logs and run the following command to kill the mutation.
### Kill the mutation
- If you have the `mutation_id`:
```sql
KILL MUTATION WHERE mutation_id = '';
```
- If you didn’t capture the ID, find it and kill by table:
```sql
SELECT mutation_id, command, is_done, latest_fail_reason
FROM system.mutations
WHERE database = currentDatabase() AND table = ''
ORDER BY create_time DESC;
KILL MUTATION WHERE database = currentDatabase() AND table = '';
```
ClickHouse ALTERs are implemented as asynchronous mutations, not transactional. If a mutation fails mid-way, some parts may have been rewritten while others were not, leaving the table partially transformed. The failed mutation also remains queued until you kill it. Clear the mutation first, then proceed.
Soon, Moose will automatically generate a local DDL plan that kills the mutation and "rolls back" the transformation to the data that was changed before the failure occurred.
### Revert your code to match the current DB schema
- Change the column type in code back to the previous (working) type
- Save your changes; let `moose dev` resync. You should be able to query the table again
If the table only has disposable dev data, you can also `TRUNCATE TABLE .` or drop/recreate the table and let `moose dev` rebuild it. Only do this in dev.
## Safely achieving the desired type change
Instead of editing the column type in place, you can add a new column with the target type and backfill the data. This is the recommended approach.
### Add a new column + backfill
```ts filename="app/tables/events.ts" copy
enum StatusEnum {
"active" = "active",
"inactive" = "inactive",
}
interface Event {
id: Key;
name: string;
createdAt: Date;
status: string;
status_v2: StatusEnum; // New field - will trigger migration
}
```
Then, generate a plan to add the new column and backfill the data.
```bash
moose generate migration --url --save --token
```
Open the generated `/migrations/plan.yaml` file. You'll see the `AddTableColumn` operation to add the new column. Right after it, you can add a `RawSql` operation to backfill the data. Here you can write an `ALTER TABLE` statement to update the new column with the data from the old column:
```yaml filename="migrations/plan.yaml"
- AddTableColumn:
table: "events"
column:
name: "status_v2"
data_type:
Nullable:
nullable: "StatusEnum"
default: null
- RawSql:
sql:
- "ALTER TABLE events UPDATE status_v2 = toStatusEnumOrNull(status) WHERE status_v2 IS NULL"
description: "Backfill status_v2 from status"
```
Then, when writing to the table, double write to both columns.
This allows for all surrounding processes and applications that rely on the old column to continue working, and you can later deprecate the old column and rename the new column when you are ready.
### Later, deprecate the old column and rename the new column
Once the column backfill is complete and you are ready to deprecate the old column, you can rename the new column to the old column name and apply this in a new, subsequent PR.
In your code, you can rename the column and deprecate the old column:
```ts filename="app/tables/events.ts" copy
interface Event {
id: Key;
name: string;
createdAt: Date;
status_old: string; // rename status to status_old
status: StatusEnum; // rename status_v2 to status
}
```
Initially you'll see two `DeleteTableColumn` operations, followed by two `AddTableColumn` operations.
*IMPORTANT*: DELETE ALL FOUR GENERATED `DeleteTableColumn` AND `AddTableColumn` OPERATIONS WITH THE FOLLOWING:
```yaml filename="migrations/plan.yaml"
- RenameTableColumn:
table: "events"
before_column_name: "status"
after_column_name: "status_old"
- RenameTableColumn:
table: "events"
before_column_name: "status_v2"
after_column_name: "status"
```
Once the old column is no longer needed, you can drop it in a third PR.
```yaml filename="migrations/plan.yaml"
- DropTableColumn:
table: "events"
column_name: "status_old"
```
## Common breaking cases
- String -> Int/Float: can fail on non-numeric rows; prefer `toInt64OrNull(...)`/`toFloat64OrNull(...)` + backfill
- Nullable(T) -> T (NOT NULL): fails if any NULLs exist and no default is provided; backfill then drop nullability
- Narrowing types (e.g., Int64 -> Int32): fails if values overflow; validate and transform first
Read about migration planning and how to use it to safely manage schema changes in production.
---
## moose/olap/schema-optimization
Source: moose/olap/schema-optimization.mdx
# Schema Optimization
Choosing the right data types and column ordering for your tables is crucial for ClickHouse performance and storage efficiency. Poor schema design can lead to 10-100x slower queries and 2-5x larger storage requirements.
## Data Types
Keep the following best practices in mind when defining your column types:
### Avoid Nullable Columns
Nullable columns in ClickHouse have significant performance overhead.
Instead of using `?` or `| undefined`, add the `& ClickHouseDefault<"...">` to your column type
.
```ts filename="AvoidNullable.ts"
// ❌ Bad: Using nullable columns
interface UserEvent {
id: string;
userId: string;
eventType: string;
description?: string; // Nullable
createdAt: Date;
}
// ✅ Good: Use default values instead
interface UserEvent {
id: string;
userId: string;
eventType: string;
description: string & ClickHouseDefault<"''"> // DEFAULT ''
createdAt: Date;
}
const userEventsTable = new OlapTable("user_events", {
orderByFields: ["id", "createdAt"]
});
```
### Use `LowCardinality` where possible
`LowCardinality` is ClickHouse's most efficient string type for columns with limited unique values.
```ts filename="LowCardinality.ts"
interface UserEvent {
id: string;
userId: string;
eventType: string & LowCardinality; // ✅ Good for limited values
status: "active" | "inactive" | "pending"; // ✅ Literals become LowCardinality automatically
country: string & LowCardinality; // ✅ Good for country codes
userAgent: string; // ❌ Keep as String for high cardinality
createdAt: Date;
}
const userEventsTable = new OlapTable("user_events", {
orderByFields: ["id", "createdAt"]
});
```
### Pick the right Integer types
Choose the smallest integer type that fits your data range to save storage and improve performance.
```ts filename="IntegerTypes.ts"
interface UserEvent {
id: string;
userId: string;
age: UInt8; // ✅ 0-255 (1 byte)
score: Int16; // ✅ -32,768 to 32,767 (2 bytes)
viewCount: UInt32; // ✅ 0 to ~4 billion (4 bytes)
timestamp: UInt64; // ✅ Unix timestamp (8 bytes)
eventType: string;
createdAt: Date;
}
// Integer type ranges:
// UInt8: 0 to 255
// UInt16: 0 to 65,535
// UInt32: 0 to 4,294,967,295
// UInt64: 0 to 18,446,744,073,709,551,615
// Int8: -128 to 127
// Int16: -32,768 to 32,767
// Int32: -2,147,483,648 to 2,147,483,647
// Int64: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
```
### Use the right precision for `DateTime`
Choose appropriate DateTime precision based on your use case to balance storage and precision.
```ts filename="DateTimePrecision.ts"
interface UserEvent {
id: string;
userId: string;
eventType: string;
createdAt: DateTime; // ✅ Second precision (default)
updatedAt: DateTime64<3>; // ✅ Millisecond precision
processedAt: DateTime64<6>; // ✅ Microsecond precision
loggedAt: DateTime64<9>; // ✅ Nanosecond precision
}
const userEventsTable = new OlapTable("user_events", {
orderByFields: ["id", "createdAt"]
});
```
### Use Decimal over Float
Use `Decimal` for financial and precise calculations to avoid floating-point precision issues.
```ts filename="DecimalOverFloat.ts"
interface Order {
id: string;
userId: string;
amount: Decimal<10, 2>; // ✅ 10 total digits, 2 decimal places
tax: Decimal<8, 2>; // ✅ 8 total digits, 2 decimal places
discount: Decimal<5, 2>; // ✅ 5 total digits, 2 decimal places
total: Decimal<12, 2>; // ✅ 12 total digits, 2 decimal places
createdAt: Date;
}
// ❌ Bad: Using float for financial data
interface BadOrder {
id: string;
amount: number; // Float - can cause precision issues
tax: number; // Float - can cause precision issues
}
const ordersTable = new OlapTable("orders", {
orderByFields: ["id", "createdAt"]
});
```
### Use `NamedTuple` over `Nested`
`NamedTuple` is more efficient than `Nested` for structured data in ClickHouse.
```ts filename="NamedTupleOverNested.ts"
interface UserEvent {
id: string;
userId: string;
eventType: string;
location: {
latitude: number;
longitude: number;
city: string;
country: string;
} & ClickHouseNamedTuple;
metadata: {
version: string;
source: string;
priority: number;
} & ClickHouseNamedTuple;
createdAt: Date;
}
// ❌ Bad: Using Nested (less efficient)
interface BadUserEvent {
id: string;
location: {
latitude: number;
longitude: number;
};
}
const userEventsTable = new OlapTable("user_events", {
orderByFields: ["id", "createdAt"]
});
```
## Ordering
### Choose columns that you will use in WHERE and GROUP BY clauses
Optimize your `orderByFields` (or `orderByExpression`) for your most common query patterns.
```ts filename="OrderByOptimization.ts"
interface UserEvent {
id: string;
userId: string;
eventType: string;
status: string;
createdAt: Date;
country: string;
}
// ✅ Good: Optimized for common query patterns
const userEventsTable = new OlapTable("user_events", {
orderByFields: ["userId", "createdAt", "eventType"] // Most common filters first
});
// Common queries this optimizes for:
// - WHERE userId = ? AND createdAt > ?
// - WHERE userId = ? AND eventType = ?
// - GROUP BY userId, eventType
```
### `ORDER BY` should prioritize LowCardinality columns first
Place `LowCardinality` columns first in your `orderByFields` (or reflect this priority in your `orderByExpression`) for better compression and query performance.
```ts filename="LowCardinalityOrdering.ts"
interface UserEvent {
id: string;
userId: string;
eventType: LowCardinality; // ✅ Low cardinality
status: LowCardinality; // ✅ Low cardinality
country: LowCardinality; // ✅ Low cardinality
createdAt: Date; // High cardinality
sessionId: string; // High cardinality
}
// ✅ Good: LowCardinality columns first
const userEventsTable = new OlapTable("user_events", {
orderByFields: ["eventType", "status", "country", "createdAt", "sessionId"]
});
// ❌ Bad: High cardinality columns first
const badUserEventsTable = new OlapTable("user_events", {
orderByFields: ["createdAt", "sessionId", "eventType", "status"] // Less efficient
});
```
---
## Schema Versioning with Materialized Views
Source: moose/olap/schema-versioning.mdx
Use table versions and materialized views to migrate breaking schema changes safely
# Table Versioning & Blue/Green Migrations
## Overview
Changing a table's storage layout (engine or sorting key) in ClickHouse requires a full table rewrite. Doing it in-place can block or slow concurrent reads and writes due to heavy merges and metadata changes, creating real risk for production workloads. Blue/Green avoids this by creating a new versioned table and migrating data live via a materialized view, so traffic continues uninterrupted.
**When to use it**:
- Change the **table engine** (e.g., MergeTree → ReplacingMergeTree)
- Update **ORDER BY fields** (sorting keys) to better match query patterns
- Reshape **primary keys** or perform type changes that require a rewrite
**How Moose does it**:
1. Define a new table with the same logical name and a bumped `version`, setting the new `orderByFields` and/or `engine` ([Table modeling](/moose/olap/model-table)).
2. Create a [Materialized view](/moose/olap/model-materialized-view) that selects from the old table and writes to the new one; Moose backfills once and keeps the view live for new inserts.
3. Later on, cut over readers/writers to the new export and clean up old resources ([Applying migrations](/moose/olap/apply-migrations)).
Setting `config.version` on an `OlapTable` changes only the underlying table name (suffixes dots with underscores). Your code still refers to the logical table you exported.
## High-level workflow
## Example: change sorting key (ORDER BY)
Assume the original `events` table orders by `id` only. We want to update the
sorting key to optimize reads by ordering on `id, createdAt`.
### Original table (version 0.0)
```ts filename="app/tables/events.ts" copy
interface EventV0 {
id: string;
name: string;
createdAt: Date;
}
);
```
### New table (bump to version 0.1)
Create a new table with the same logical name, but set `version: "0.1"` and update the ordering to `id, createdAt`. Moose will create `events_0_1` in ClickHouse.
```ts filename="app/tables/events_v01.ts" copy
interface EventV1 {
id: Key;
name: string;
createdAt: Date;
}
);
```
### Create the materialized view to migrate data
Create a materialized view that:
- SELECTs from the old table (`events_v0`)
- copies fields 1:1 to the new table
- writes into the versioned target table (`events_v1`)
Pass the versioned `OlapTable` instance as `targetTable`. If you only pass a `tableName`, Moose will create an unversioned target.
```ts filename="app/views/migrate_events_to_v01.ts" copy
);
```
What happens when you export this view:
- Moose creates the versioned table if needed
- Moose creates the MATERIALIZED VIEW and immediately runs a one-time backfill (`INSERT INTO ... SELECT ...`)
- ClickHouse keeps the view active: any new inserts into `events` automatically flow into `events_0_1`
## Cutover and cleanup
- Update readers to query the new table export (`eventsV1`).
- Update writers/streams to produce to the new table if applicable.
- After verifying parity and retention windows, drop the old table and the migration view.
## Notes and tips
- Use semantic versions like `0.1`, `1.0`, `1.1`. Moose will render `events_1_1` as the physical name.
- Keep the migration view simple and deterministic. If you need complex transforms, prefer explicit SQL in the `selectStatement`.
- Very large backfills can take time. Consider deploying during low-traffic windows.
---
## Supported Column Types
Source: moose/olap/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` | `string & ClickHouseDecimal` | 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>
polygon: ClickHousePolygon; // Polygon → Array>
multiPolygon: ClickHouseMultiPolygon; // MultiPolygon → Array>>
}
```
| 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[]; // 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; // Map(String, String)
metrics: Record; // 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; // 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;
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`) → 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
---
## moose/olap/ttl
Source: moose/olap/ttl.mdx
## TTL (Time-to-Live) for ClickHouse Tables
Moose lets you declare ClickHouse TTL directly in your data model:
- Table-level TTL via the `ttl` option on `OlapTable` config
- Column-level TTL via `ClickHouseTTL` on individual fields
### When to use TTL
- Automatically expire old rows to control storage cost
- Mask or drop sensitive columns earlier than the full row expiry
### TypeScript
```ts
interface Event {
id: Key;
timestamp: DateTime;
email: string & ClickHouseTTL<"timestamp + INTERVAL 30 DAY">; // column TTL
}
);
```
### Python
```python
from typing import Annotated
from moose_lib import OlapTable, OlapConfig, Key, ClickHouseTTL
from pydantic import BaseModel
from datetime import datetime
class Event(BaseModel):
id: Key[str]
timestamp: datetime
email: Annotated[str, ClickHouseTTL("timestamp + INTERVAL 30 DAY")]
events = OlapTable[Event](
"Events",
OlapConfig(
order_by_fields=["id", "timestamp"],
ttl="timestamp + INTERVAL 90 DAY DELETE",
),
)
```
### Notes
- Expressions must be valid ClickHouse TTL expressions, but do not include the leading `TTL` keyword.
- Column TTLs are independent from the table TTL and can be used together.
- Moose will apply TTL changes via migrations using `ALTER TABLE ... MODIFY TTL` and `MODIFY COLUMN ... TTL`.
### Related
- See `Modeling Tables` for defining your schema
- See `Applying Migrations` to roll out TTL changes
---
## Python Moose Lib Reference
Source: moose/reference/py-moose-lib.mdx
Python Moose Lib Reference
# API Reference
This is a comprehensive reference for the Python `moose_lib`, detailing all exported components, types, and utilities.
## Core Types
### `Key[T]`
A type annotation for marking fields as primary keys in data models. Used with Pydantic.
```python
from moose_lib import Key
from pydantic import BaseModel
class MyModel(BaseModel):
id: Key[str] # Marks 'id' as a primary key of type string
```
### `BaseModel`
Pydantic base model used for data modeling in Moose.
```python
from pydantic import BaseModel
class MyDataModel(BaseModel):
id: str
name: str
count: int
```
### `MooseClient`
Client for interacting with ClickHouse and Temporal.
```python
class MooseClient:
query: QueryClient # For database queries
workflow: Optional[WorkflowClient] # For workflow operations
```
### `ApiResult`
Class representing the result of a analytics API call.
```python
@dataclass
class ApiResult:
status: int # HTTP status code
body: Any # Response body
```
## Configuration Types
### `OlapConfig`
Configuration for OLAP tables.
```python
from typing import Union, Optional
from moose_lib.blocks import EngineConfig
class OlapConfig(BaseModel):
database: Optional[str] = None # Optional database name (defaults to moose.config.toml clickhouse_config.db_name)
order_by_fields: list[str] = [] # Fields to order by
engine: Optional[EngineConfig] = None # Table engine configuration
```
### `EngineConfig` Classes
Base class and implementations for table engine configurations.
```python
# Base class
class EngineConfig:
pass
# Available engine implementations
class MergeTreeEngine(EngineConfig):
pass
class ReplacingMergeTreeEngine(EngineConfig):
ver: Optional[str] = None # Version column for keeping latest
is_deleted: Optional[str] = None # Soft delete marker (requires ver)
class AggregatingMergeTreeEngine(EngineConfig):
pass
class SummingMergeTreeEngine(EngineConfig):
columns: Optional[List[str]] = None # Columns to sum
# Replicated engines
class ReplicatedMergeTreeEngine(EngineConfig):
keeper_path: Optional[str] = None # ZooKeeper/Keeper path (optional for Cloud)
replica_name: Optional[str] = None # Replica name (optional for Cloud)
class ReplicatedReplacingMergeTreeEngine(EngineConfig):
keeper_path: Optional[str] = None # ZooKeeper/Keeper path (optional for Cloud)
replica_name: Optional[str] = None # Replica name (optional for Cloud)
ver: Optional[str] = None # Version column for keeping latest
is_deleted: Optional[str] = None # Soft delete marker (requires ver)
class ReplicatedAggregatingMergeTreeEngine(EngineConfig):
keeper_path: Optional[str] = None # ZooKeeper/Keeper path (optional for Cloud)
replica_name: Optional[str] = None # Replica name (optional for Cloud)
class ReplicatedSummingMergeTreeEngine(EngineConfig):
keeper_path: Optional[str] = None # ZooKeeper/Keeper path (optional for Cloud)
replica_name: Optional[str] = None # Replica name (optional for Cloud)
columns: Optional[List[str]] = None # Columns to sum
```
### `StreamConfig`
Configuration for data streams.
```python
class StreamConfig(BaseModel):
parallelism: int = 1
retention_period: int = 60 * 60 * 24 * 7 # 7 days
destination: Optional[OlapTable[Any]] = None
```
### `IngestConfig`
Configuration for data ingestion.
```python
class IngestConfig(BaseModel):
destination: Optional[OlapTable[Any]] = None
```
### `IngestPipelineConfig`
Configuration for creating a complete data pipeline.
```python
class IngestPipelineConfig(BaseModel):
table: bool | OlapConfig = True
stream: bool | StreamConfig = True
ingest_api: bool | IngestConfig = True
```
## Infrastructure Components
### `OlapTable[T]`
Creates a ClickHouse table with the schema of type T.
```python
from moose_lib import OlapTable, OlapConfig
from moose_lib.blocks import ReplacingMergeTreeEngine
# Basic usage
my_table = OlapTable[UserProfile]("user_profiles")
# With configuration (fields)
my_table = OlapTable[UserProfile]("user_profiles", OlapConfig(
order_by_fields=["id", "timestamp"],
engine=ReplacingMergeTreeEngine(),
))
# With configuration (expression)
my_table_expr = OlapTable[UserProfile]("user_profiles_expr", OlapConfig(
order_by_expression="(id, timestamp)",
engine=ReplacingMergeTreeEngine(),
))
# With custom database override
analytics_table = OlapTable[UserProfile]("user_profiles", OlapConfig(
database="analytics", # Override default database
order_by_fields=["id", "timestamp"]
))
# Disable sorting entirely
my_table_unsorted = OlapTable[UserProfile]("user_profiles_unsorted", OlapConfig(
order_by_expression="tuple()",
))
```
### `Stream[T]`
Creates a Redpanda topic with the schema of type T.
```python
# Basic usage
my_stream = Stream[UserEvent]("user_events")
# With configuration
my_stream = Stream[UserEvent]("user_events", StreamConfig(
parallelism=3,
retention_period=86400 # 1 day in seconds
))
# Adding transformations
def transform_user_event(event: UserEvent) -> ProfileUpdate:
return ProfileUpdate(user_id=event.user_id, update_type="event")
my_stream.add_transform(profile_stream, transform_user_event)
```
### `IngestApi[T]`
Creates an HTTP endpoint for ingesting data of type T.
```python
# Basic usage with destination stream
my_ingest_api = IngestApi[UserEvent]("user_events", IngestConfigWithDestination(
destination=my_user_event_stream
))
```
### `Api[T, U]`
Creates an HTTP endpoint for querying data with request type T and response type U.
```python
# Basic usage
def get_user_profiles(params: UserQuery) -> list[UserProfile]:
# Query implementation
return [UserProfile(...), UserProfile(...)]
my_api = Api[UserQuery, list[UserProfile]](
"get_user_profiles",
get_user_profiles
)
```
### `IngestPipeline[T]`
Combines ingest API, stream, and table creation in a single component.
```python
from moose_lib import IngestPipeline, IngestPipelineConfig, StreamConfig, OlapConfig
from moose_lib.blocks import ReplacingMergeTreeEngine
# Basic usage
pipeline = IngestPipeline[UserEvent]("user_pipeline", IngestPipelineConfig(
ingest_api=True,
stream=True,
table=True
))
# With advanced configuration
pipeline = IngestPipeline[UserEvent]("user_pipeline", IngestPipelineConfig(
ingest_api=True,
stream=StreamConfig(parallelism=3),
table=OlapConfig(
order_by_fields=["id", "timestamp"],
engine=ReplacingMergeTreeEngine(),
)
))
```
### `MaterializedView[T]`
Creates a materialized view in ClickHouse.
```python
# Basic usage
view = MaterializedView[UserStatistics](MaterializedViewOptions(
select_statement="SELECT user_id, COUNT(*) as event_count FROM user_events GROUP BY user_id",
table_name="user_events",
materialized_view_name="user_statistics",
order_by_fields=["user_id"]
))
```
## ClickHouse Utilities
### Engine Configuration Classes
Type-safe configuration classes for table engines:
```python
from moose_lib.blocks import (
MergeTreeEngine,
ReplacingMergeTreeEngine,
AggregatingMergeTreeEngine,
SummingMergeTreeEngine,
ReplicatedMergeTreeEngine,
ReplicatedReplacingMergeTreeEngine,
ReplicatedAggregatingMergeTreeEngine,
ReplicatedSummingMergeTreeEngine,
S3QueueEngine
)
# ReplacingMergeTree with version control and soft deletes
dedup_engine = ReplacingMergeTreeEngine(
ver="updated_at", # Optional: version column for keeping latest
is_deleted="deleted" # Optional: soft delete marker (requires ver)
)
# ReplicatedMergeTree with explicit keeper paths (self-managed ClickHouse)
replicated_engine = ReplicatedMergeTreeEngine(
keeper_path="/clickhouse/tables/{database}/{shard}/my_table",
replica_name="{replica}"
)
# ReplicatedReplacingMergeTree with deduplication
replicated_dedup_engine = ReplicatedReplacingMergeTreeEngine(
keeper_path="/clickhouse/tables/{database}/{shard}/my_dedup_table",
replica_name="{replica}",
ver="updated_at",
is_deleted="deleted"
)
# For ClickHouse Cloud or Boreal - omit keeper parameters
cloud_replicated = ReplicatedMergeTreeEngine() # No parameters needed
# S3Queue configuration for streaming from S3
s3_engine = S3QueueEngine(
s3_path="s3://bucket/data/*.json",
format="JSONEachRow",
aws_access_key_id="AKIA...", # Optional
aws_secret_access_key="secret...", # Optional
compression="gzip", # Optional
headers={"X-Custom": "value"} # Optional
)
# Use with OlapTable
s3_table = OlapTable[MyData]("s3_events", OlapConfig(
engine=s3_engine,
settings={
"mode": "unordered",
"keeper_path": "/clickhouse/s3queue/events",
"loading_retries": "3"
}
))
# S3 engine for direct S3 access (not streaming)
s3_direct_engine = S3Engine(
path="s3://bucket/data/file.json",
format="JSONEachRow",
aws_access_key_id="AKIA...", # Optional
aws_secret_access_key="secret...", # Optional
compression="gzip" # Optional
)
s3_direct_table = OlapTable[MyData]("s3_data", OlapConfig(
engine=s3_direct_engine
))
# Buffer engine for high-throughput buffered writes
buffer_engine = BufferEngine(
target_database="local",
target_table="destination_table",
num_layers=16,
min_time=10,
max_time=100,
min_rows=10000,
max_rows=1000000,
min_bytes=10485760,
max_bytes=104857600
)
buffer_table = OlapTable[MyData]("buffer", OlapConfig(
engine=buffer_engine
))
# Distributed engine for cluster-wide distributed tables
distributed_engine = DistributedEngine(
cluster="my_cluster",
target_database="default",
target_table="local_table",
sharding_key="cityHash64(id)" # Optional
)
distributed_table = OlapTable[MyData]("distributed", OlapConfig(
engine=distributed_engine
))
```
## Task Management
### `Task[T, U]`
A class that represents a single task within a workflow system, with typed input and output.
```python
from moose_lib import Task, TaskConfig, TaskContext
from pydantic import BaseModel
# Define input and output models
class InputData(BaseModel):
user_id: str
class OutputData(BaseModel):
result: str
status: bool
# Task with input and output
def process_user(ctx: TaskContext[InputData]) -> OutputData:
# Process the user data
return OutputData(result=f"Processed {ctx.input.user_id}", status=True)
user_task = Task[InputData, OutputData](
name="process_user",
config=TaskConfig(
run=process_user,
retries=3,
timeout="30s"
)
)
# Task with no input, but with output
def fetch_data(ctx: TaskContext[None]) -> OutputData:
return OutputData(result="Fetched data", status=True)
fetch_task = Task[None, OutputData](
name="fetch_data",
config=TaskConfig(run=fetch_data)
)
# Task with input but no output
def log_event(ctx: TaskContext[InputData]) -> None:
print(f"Event logged for: {ctx.input.user_id}")
log_task = Task[InputData, None](
name="log_event",
config=TaskConfig(run=log_event)
)
# Task with neither input nor output
def cleanup(ctx: TaskContext[None]) -> None:
print("Cleanup complete")
cleanup_task = Task[None, None](
name="cleanup",
config=TaskConfig(run=cleanup)
)
```
### `TaskConfig[T, U]`
Configuration for a Task.
```python
@dataclasses.dataclass
class TaskConfig(Generic[T, U]):
# The handler function that executes the task logic
# Can be any of: () -> None, () -> U, (T) -> None, or (T) -> U depending on input/output types
run: TaskRunFunc[T, U]
# Optional list of tasks to run after this task completes
on_complete: Optional[list[Task[U, Any]]] = None
# Optional function that is called when the task is cancelled
on_cancel: Optional[Callable[[TaskContext[T_none]], Union[None, Awaitable[None]]]] = None
# Optional timeout string (e.g. "5m", "1h", "never")
timeout: Optional[str] = None
# Optional number of retry attempts
retries: Optional[int] = None
```
### `Workflow`
Represents a workflow composed of one or more tasks.
```python
from moose_lib import Workflow, WorkflowConfig
# Create a workflow that starts with the fetch_task
data_workflow = Workflow(
name="data_processing",
config=WorkflowConfig(
starting_task=fetch_task,
schedule="@every 1h", # Run every hour
timeout="10m", # Timeout after 10 minutes
retries=2 # Retry up to 2 times if it fails
)
)
```
### `WorkflowConfig`
Configuration for a workflow.
```python
@dataclasses.dataclass
class WorkflowConfig:
# The first task to execute in the workflow
starting_task: Task[Any, Any]
# Optional number of retry attempts for the entire workflow
retries: Optional[int] = None
# Optional timeout string for the entire workflow
timeout: Optional[str] = None
# Optional cron-like schedule string for recurring execution
schedule: Optional[str] = None
```
---
## TypeScript Moose Lib Reference
Source: moose/reference/ts-moose-lib.mdx
TypeScript Moose Lib Reference
# API Reference
This is a comprehensive reference for `@514labs/moose-lib` , detailing all exported components, types, and utilities.
## Core Types
### `Key`
A type for marking fields as primary keys in data models.
```ts
// Example
interface MyModel {
id: Key; // Marks 'id' as a primary key of type string
}
```
### `JWT`
A type for working with JSON Web Tokens.
```ts
// Example
type UserJWT = JWT<{ userId: string, role: string }>;
```
### `ApiUtil`
Interface providing utilities for analytics APIs.
```ts
interface ApiUtil {
client: MooseClient; // Client for interacting with the database
sql: typeof sql; // SQL template tag function
jwt: JWTPayload | undefined; // Current JWT if available
}
```
## Infrastructure Components
### `OlapTable`
Creates a ClickHouse table with the schema of type T.
```ts
// Basic usage with MergeTree (default)
);
// With sorting configuration (expression)
);
// Disable sorting entirely
);
// For deduplication, explicitly set the ReplacingMergeTree engine
);
```
### `BaseOlapConfig`
Base configuration interface for `OlapTable` with common table configuration options.
```ts
interface BaseOlapConfig {
// Optional database name (defaults to moose.config.toml clickhouse_config.db_name)
database?: string;
// Optional array of field names to order by
orderByFields?: (keyof T & string)[];
// Optional SQL expression for ORDER BY clause (alternative to orderByFields)
orderByExpression?: string;
// Optional table engine (defaults to MergeTree)
engine?: ClickHouseEngines;
// Optional settings for table configuration
settings?: { [key: string]: string };
// Optional lifecycle mode (defaults to MOOSE_MANAGED)
lifeCycle?: LifeCycle;
// Additional engine-specific fields (ver, isDeleted, keeperPath, etc.)
// depend on the engine type
}
```
Example with database override:
```ts
// Table in custom database
);
// Default database (from moose.config.toml)
);
```
### `Stream`
Creates a Redpanda topic with the schema of type T.
```ts
// Basic usage
);
// Adding transformations
myConfiguredStream.addTransform(
destinationStream,
(record) => transformFunction(record)
);
```
### `IngestApi`
Creates an HTTP endpoint for ingesting data of type T.
```ts
// Basic usage with destination stream
);
```
### `Api`
Creates an HTTP endpoint for querying data with request type T and response type R.
```ts
// Basic usage
) => {
const result = await client.query.execute(
sql`SELECT * FROM user_profiles WHERE age > ${params.minAge} LIMIT 10`
);
return result;
}
);
```
### `IngestPipeline`
Combines ingest API, stream, and table creation in a single component.
```ts
// Basic usage
);
// With advanced configuration
);
```
### `MaterializedView`
Creates a materialized view in ClickHouse.
```ts
// Basic usage
);
```
## SQL Utilities
### `sql` Template Tag
Template tag for creating type-safe SQL queries with parameters.
```ts
// Basic usage
const query = sql`SELECT * FROM users WHERE id = ${userId}`;
// With multiple parameters
const query = sql`
SELECT * FROM users
WHERE age > ${minAge}
AND country = ${country}
LIMIT ${limit}
`;
```
### `MooseClient`
Client for interacting with ClickHouse and Temporal.
```ts
class MooseClient {
query: QueryClient; // For database queries
workflow: WorkflowClient; // For workflow operations
}
```
## ClickHouse Utilities
### Table Engine Configurations
#### `ClickHouseEngines` Enum
Available table engines:
```ts
enum ClickHouseEngines {
MergeTree = "MergeTree",
ReplacingMergeTree = "ReplacingMergeTree",
AggregatingMergeTree = "AggregatingMergeTree",
SummingMergeTree = "SummingMergeTree",
ReplicatedMergeTree = "ReplicatedMergeTree",
ReplicatedReplacingMergeTree = "ReplicatedReplacingMergeTree",
ReplicatedAggregatingMergeTree = "ReplicatedAggregatingMergeTree",
ReplicatedSummingMergeTree = "ReplicatedSummingMergeTree",
S3Queue = "S3Queue"
}
```
#### `ReplacingMergeTreeConfig`
Configuration for ReplacingMergeTree tables:
```ts
type ReplacingMergeTreeConfig = {
engine: ClickHouseEngines.ReplacingMergeTree;
orderByFields?: (keyof T & string)[];
ver?: keyof T & string; // Optional: version column for keeping latest
isDeleted?: keyof T & string; // Optional: soft delete marker (requires ver)
settings?: { [key: string]: string };
}
```
#### Replicated Engine Configurations
Configuration for replicated table engines:
```ts
// ReplicatedMergeTree
type ReplicatedMergeTreeConfig = {
engine: ClickHouseEngines.ReplicatedMergeTree;
keeperPath?: string; // Optional: ZooKeeper/Keeper path (omit for Cloud)
replicaName?: string; // Optional: replica name (omit for Cloud)
orderByFields?: (keyof T & string)[];
settings?: { [key: string]: string };
}
// ReplicatedReplacingMergeTree
type ReplicatedReplacingMergeTreeConfig = {
engine: ClickHouseEngines.ReplicatedReplacingMergeTree;
keeperPath?: string; // Optional: ZooKeeper/Keeper path (omit for Cloud)
replicaName?: string; // Optional: replica name (omit for Cloud)
ver?: keyof T & string; // Optional: version column
isDeleted?: keyof T & string; // Optional: soft delete marker
orderByFields?: (keyof T & string)[];
settings?: { [key: string]: string };
}
// ReplicatedAggregatingMergeTree
type ReplicatedAggregatingMergeTreeConfig = {
engine: ClickHouseEngines.ReplicatedAggregatingMergeTree;
keeperPath?: string; // Optional: ZooKeeper/Keeper path (omit for Cloud)
replicaName?: string; // Optional: replica name (omit for Cloud)
orderByFields?: (keyof T & string)[];
settings?: { [key: string]: string };
}
// ReplicatedSummingMergeTree
type ReplicatedSummingMergeTreeConfig = {
engine: ClickHouseEngines.ReplicatedSummingMergeTree;
keeperPath?: string; // Optional: ZooKeeper/Keeper path (omit for Cloud)
replicaName?: string; // Optional: replica name (omit for Cloud)
columns?: string[]; // Optional: columns to sum
orderByFields?: (keyof T & string)[];
settings?: { [key: string]: string };
}
```
**Note**: The `keeperPath` and `replicaName` parameters are optional. When omitted, Moose uses smart defaults that work in both ClickHouse Cloud and self-managed environments (default path: `/clickhouse/tables/{uuid}/{shard}` with replica `{replica}`). You can still provide both parameters explicitly if you need custom replication paths.
### `S3QueueTableSettings`
Type-safe interface for S3Queue-specific table settings (ClickHouse 24.7+).
```ts
interface S3QueueTableSettings {
mode?: "ordered" | "unordered"; // Processing mode
after_processing?: "keep" | "delete"; // File handling after processing
keeper_path?: string; // ZooKeeper path for coordination
loading_retries?: string; // Number of retry attempts
processing_threads_num?: string; // Parallel processing threads
// ... and many more settings
}
```
### S3Queue Configuration
Configure S3Queue tables for streaming data from S3 buckets (ORDER BY is not supported):
```ts
);
```
### S3 Configuration
Configure S3 tables for direct read/write access to S3 storage:
```ts
);
// Public bucket (no authentication) - omit credentials for NOSIGN
);
```
### Buffer Configuration
Configure Buffer tables for high-throughput buffered writes (ORDER BY is not supported):
```ts
// First create destination table
);
// Then create buffer table
);
```
### Distributed Configuration
Configure Distributed tables for cluster-wide distributed queries (ORDER BY is not supported):
```ts
);
```
## Task Management
### `Task`
A class that represents a single task within a workflow system.
```ts
// No input, no output
);
// With input and output
);
```
### `TaskContext`
A context object that includes input & state passed between the task's run/cancel functions.
```ts
export type TaskContext = T extends null ? { state: any; input?: null } : { state: any; input: T };
```
### `TaskConfig`
Configuration options for tasks.
```ts
interface TaskConfig {
// The main function that executes the task logic
run: (context: TaskContext) => Promise;
// Optional array of tasks to execute after this task completes
onComplete?: (Task | Task)[];
// Optional function that is called when the task is cancelled.
onCancel?: (context: TaskContext) => Promise;
// Optional timeout duration (e.g., "30s", "5m", "never")
timeout?: string;
// Optional number of retry attempts
retries?: number;
}
```
### `Workflow`
A class that represents a complete workflow composed of interconnected tasks.
```ts
const myWorkflow = new Workflow("getData", {
startingTask: callAPI,
schedule: "@every 5s", // Run every 5 seconds
timeout: "1h",
retries: 3
});
```
### `WorkflowConfig`
Configuration options for defining a workflow.
```ts
interface WorkflowConfig {
// The initial task that begins the workflow execution
startingTask: Task | Task | Task | Task;
// Optional number of retry attempts
retries?: number;
// Optional timeout duration (e.g., "10m", "1h", "never")
timeout?: string;
// Optional cron-style schedule string
schedule?: string;
}
```
---
**Important:** The following components must be exported from your `app/index.ts` file for Moose to detect them:
- `OlapTable` instances
- `Stream` instances
- `IngestApi` instances
- `Api` instances
- `IngestPipeline` instances
- `MaterializedView` instances
- `Task` instances
- `Workflow` instances
**Configuration objects and utilities** (like `DeadLetterQueue`, `Key`, `sql`) do not need to be exported as they are used as dependencies of the main components.
---
## Moose Streaming
Source: moose/streaming.mdx
Build real-time data pipelines with Redpanda/Kafka streams, transformations, and event processing
# Moose Streaming
## Overview
The Streaming module provides standalone real-time data processing with Kafka/Redpanda topics. You can use this capability independently to build event-driven architectures, data transformations, and real-time pipelines without requiring other MooseStack components.
## Basic Usage
```ts filename="Stream.ts" copy
interface ExampleEvent {
id: string;
userId: string;
timestamp: Date;
eventType: string;
}
// Create a standalone stream for events
);
// Add consumers for real-time processing
exampleStream.addConsumer((event) => {
console.log("Processing event:", event);
// Custom processing logic here
});
```
### Enabling Streaming
To enable streaming, you need to ensure that the `streaming_engine` feature flag is set to `true` in your `moose.config.toml` file:
```toml
[features]
streaming_engine = true
```
## Core Capabilities
## Integration with Other Capabilities
The Streaming capability can be used independently, or in conjunction with other MooseStack modules:
---
## moose/streaming/connect-cdc
Source: moose/streaming/connect-cdc.mdx
# Connect to CDC Services
Coming Soon!
---
## Streaming Consumer Functions
Source: moose/streaming/consumer-functions.mdx
Read and process data from streams with consumers and processors
# Streaming Consumer Functions
## Overview
Consuming data from streams allows you to read and process data from Kafka/Redpanda topics. This is essential for building real-time applications, analytics, and event-driven architectures.
## Basic Usage
Consumers are just functions that are called when new data is available in a stream. You add them to a stream like this:
```typescript filename="StreamConsumer.ts"
interface UserEvent {
id: string;
userId: string;
timestamp: Date;
eventType: string;
}
const userEventsStream = new Stream("user-events");
// Add a consumer to process events
userEventsStream.addConsumer((event: UserEvent) => {
console.log(`Processing event: ${event.id}`);
console.log(`User: ${event.userId}, Type: ${event.eventType}`);
// Your processing logic here
// e.g., update analytics, send notifications, etc.
});
// Add multiple consumers for different purposes
userEventsStream.addConsumer((event: UserEvent) => {
// Analytics processing
if (event.eventType === 'purchase') {
updatePurchaseAnalytics(event);
}
});
userEventsStream.addConsumer((event: UserEvent) => {
// Notification processing
if (event.eventType === 'signup') {
sendWelcomeEmail(event.userId);
}
});
```
## Processing Patterns
### Stateful Processing with MooseCache
Maintain state across event processing using MooseCache for distributed state management:
```typescript filename="StatefulProcessing.ts"
// State container for accumulating data
interface AccumulatorState {
id: string;
counter: number;
sum: number;
lastModified: Date;
attributes: Record;
}
// Input message structure
interface InputMessage {
id: string;
groupId: string;
numericValue: number;
messageType: string;
timestamp: Date;
payload: Record;
}
const messageStream = new Stream("input-stream");
messageStream.addConsumer(async (message: InputMessage) => {
// Get distributed cache instance
const cache = await MooseCache.get();
const cacheKey = `state:${message.groupId}`;
// Load existing state or create new one
let state: AccumulatorState | null = await cache.get(cacheKey);
if (!state) {
// Initialize new state
state = {
id: message.groupId,
counter: 0,
sum: 0,
lastModified: new Date(),
attributes: {}
};
}
// Apply message to state
state.counter += 1;
state.sum += message.numericValue;
state.lastModified = message.timestamp;
state.attributes = { ...state.attributes, ...message.payload };
// Determine cache lifetime based on message type
const ttlSeconds = message.messageType === 'complete' ? 60 : 3600;
if (message.messageType === 'complete' || shouldFinalize(state)) {
// Finalize and remove state
await finalizeState(state);
await cache.delete(cacheKey);
} else {
// Persist updated state
await cache.set(cacheKey, state, ttlSeconds);
}
});
// Condition for automatic state finalization
function shouldFinalize(state: AccumulatorState): boolean {
const threshold = 100;
const timeLimit = 30 * 60 * 1000; // 30 minutes
const elapsed = new Date().getTime() - state.lastModified.getTime();
return state.counter >= threshold || elapsed > timeLimit;
}
async function finalizeState(state: AccumulatorState): Promise {
console.log(`Finalizing state ${state.id}: counter=${state.counter}, sum=${state.sum}`);
}
```
## Propagating Events to External Systems
You can use consumer functions to trigger actions across external systems - send notifications, sync databases, update caches, or integrate with any other service when events occur:
### HTTP API Calls
Send processed data to external APIs:
```typescript filename="HttpIntegration.ts"
interface WebhookPayload {
id: string;
data: Record;
timestamp: Date;
}
const webhookStream = new Stream("webhook-events");
webhookStream.addConsumer(async (payload: WebhookPayload) => {
try {
// Send to external webhook
const response = await fetch('https://external-api.com/webhook', {
method: 'POST',
headers: {
'Content-Type': 'application/json',
'Authorization': 'Bearer ' + process.env.API_TOKEN
},
body: JSON.stringify({
eventId: payload.id,
eventData: payload.data,
processedAt: new Date().toISOString()
})
});
if (!response.ok) {
throw new Error(`HTTP ${response.status}: ${response.statusText}`);
}
console.log(`Successfully sent event ${payload.id} to external API`);
} catch (error) {
console.error(`Failed to send event ${payload.id}:`, error);
// Could implement retry logic or dead letter queue here
}
});
```
#### Database Operations
Write processed data to external databases:
```typescript filename="DatabaseIntegration.ts"
interface DatabaseRecord {
id: string;
category: string;
value: number;
metadata: Record;
timestamp: Date;
}
const dbStream = new Stream("database-events");
// Initialize database connection
const dbConfig = {
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME
};
dbStream.addConsumer(async (record: DatabaseRecord) => {
const connection = await createConnection(dbConfig);
try {
// Insert record into external database
await connection.execute(
'INSERT INTO processed_events (id, category, value, metadata, created_at) VALUES (?, ?, ?, ?, ?)',
[
record.id,
record.category,
record.value,
JSON.stringify(record.metadata),
record.timestamp
]
);
console.log(`Inserted record ${record.id} into database`);
} catch (error) {
console.error(`Database insert failed for record ${record.id}:`, error);
} finally {
await connection.end();
}
});
```
#### File System Operations
Write processed data to files or cloud storage:
```typescript filename="FileSystemIntegration.ts"
interface FileOutput {
id: string;
filename: string;
content: string;
directory: string;
format: 'json' | 'csv' | 'txt';
}
const fileStream = new Stream("file-events");
fileStream.addConsumer(async (output: FileOutput) => {
try {
// Ensure directory exists
await mkdir(output.directory, { recursive: true });
// Format content based on type
let formattedContent: string;
switch (output.format) {
case 'json':
formattedContent = JSON.stringify(JSON.parse(output.content), null, 2);
break;
case 'csv':
formattedContent = output.content; // Assume already CSV formatted
break;
default:
formattedContent = output.content;
}
// Write file with timestamp
const timestamp = new Date().toISOString().replace(/[:.]/g, '-');
const filename = `${output.filename}_${timestamp}.${output.format}`;
const filepath = join(output.directory, filename);
await writeFile(filepath, formattedContent, 'utf8');
console.log(`Written file: ${filepath}`);
} catch (error) {
console.error(`Failed to write file for output ${output.id}:`, error);
}
});
```
#### Email and Notifications
Send alerts and notifications based on processed events:
```typescript filename="NotificationIntegration.ts"
interface NotificationEvent {
id: string;
type: 'email' | 'slack' | 'webhook';
recipient: string;
subject: string;
message: string;
priority: 'low' | 'medium' | 'high';
metadata: Record;
}
const notificationStream = new Stream("notifications");
// Configure email transporter
const emailTransporter = nodemailer.createTransporter({
host: process.env.SMTP_HOST,
port: parseInt(process.env.SMTP_PORT || '587'),
secure: false,
auth: {
user: process.env.SMTP_USER,
pass: process.env.SMTP_PASS
}
});
notificationStream.addConsumer(async (notification: NotificationEvent) => {
try {
switch (notification.type) {
case 'email':
await emailTransporter.sendMail({
from: process.env.SMTP_FROM,
to: notification.recipient,
subject: notification.subject,
text: notification.message,
html: `
${notification.subject}
${notification.message}
Priority: ${notification.priority}
`
});
break;
case 'slack':
await fetch(`https://hooks.slack.com/services/${process.env.SLACK_WEBHOOK}`, {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({
text: notification.message,
channel: notification.recipient,
username: 'Moose Alert',
icon_emoji: notification.priority === 'high' ? ':warning:' : ':information_source:'
})
});
break;
case 'webhook':
await fetch(notification.recipient, {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({
id: notification.id,
subject: notification.subject,
message: notification.message,
priority: notification.priority,
metadata: notification.metadata
})
});
break;
}
console.log(`Sent ${notification.type} notification ${notification.id}`);
} catch (error) {
console.error(`Failed to send notification ${notification.id}:`, error);
}
});
```
---
## Create Streams
Source: moose/streaming/create-stream.mdx
Define and create Kafka/Redpanda topics with type-safe schemas
# Creating Streams
## Overview
Streams serve as the transport layer between your data sources and database tables. Built on Kafka/Redpanda topics, they provide a way to implement real-time pipelines for ingesting and processing incoming data.
## Creating Streams
You can create streams in two ways:
- High-level: Using the `IngestPipeline` class (recommended)
- Low-level: Manually configuring the `Stream` component
### Streams for Ingestion
The `IngestPipeline` class provides a convenient way to set up streams with ingestion APIs and tables. This is the recommended way to create streams for ingestion:
```ts filename="IngestionStream.ts" copy {10}
interface RawData {
id: Key;
value: number;
}
);
```
While the `IngestPipeline` provides a convenient way to set up streams with ingestion APIs and tables, you can also configure these components individually for more granular control:
```ts filename="StreamObject.ts" copy {8-12}
interface RawData {
id: string;
value: number;
}
// Create a table for the raw data
);
// Create an ingestion API for the raw data
);
```
### Streams for Transformations
If the raw data needs to be transformed before landing in the database, you can define a transform destination stream and a transform function to process the data:
#### Single Stream Transformation
```ts filename="TransformDestinationStream.ts" copy
interface RawData {
id: Key;
value: number;
}
interface TransformedData {
id: Key;
transformedValue: number;
transformedAt: Date;
}
// Configure components for raw data ingestion & buffering
const rawData = new IngestPipeline("raw_data", {
ingestApi: true,
stream: true, // Buffers data between the ingestion API and the database table
table: false // Don't create a table for the raw data
});
// Create a table for the transformed data
const transformedData = new IngestPipeline("transformed_data", {
ingestApi: false, // Don't create an ingestion API for the transformed data
stream: true, // Create destination stream for the transformed data
table: true // Create a table for the transformed data
});
rawData.stream.addTransform(transformedData.stream, (record) => ({
id: record.id,
transformedValue: record.value * 2,
transformedAt: new Date()
}));
```
```ts filename="TransformDestinationStream.ts" copy
interface RawData {
id: Key;
value: number;
}
interface TransformedData {
id: Key;
transformedValue: number;
transformedAt: Date;
}
// Configure components for raw data ingestion & buffering
);
// Configure components for transformed data stream & storage
);
// Add a transform to the raw data stream to transform the data
rawDataStream.addTransform(transformedStream, (record) => ({
id: record.id,
transformedValue: record.value * 2,
transformedAt: new Date()
}));
```
#### Chaining Transformations
For more complex transformations, you can chain multiple transformations together. This is a use case where using a standalone Stream for intermediate stages of your pipeline may be useful:
```ts filename="ChainedTransformations.ts" copy
// Define the schema for raw input data
interface RawData {
id: Key;
value: number;
}
// Define the schema for intermediate transformed data
interface IntermediateData {
id: Key;
transformedValue: number;
transformedAt: Date;
}
// Define the schema for final transformed data
interface FinalData {
id: Key;
transformedValue: number;
anotherTransformedValue: number;
transformedAt: Date;
}
// Create the first pipeline for raw data ingestion
// Only create an API and a stream (no table) since we're ingesting the raw data
const rawData = new IngestPipeline("raw_data", {
ingestApi: true, // Enable HTTP ingestion endpoint
stream: true, // Create a stream to buffer data
table: false // Don't store raw data in a table
});
// Create an intermediate stream to hold data between transformations (no api or table needed)
));
// Create the final pipeline that will store the fully transformed data
const finalData = new IngestPipeline("final_stream", {
ingestApi: false, // No direct ingestion to this pipeline
stream: true, // Create a stream for processing
table: true // Store final results in a table
});
// Second transformation: further transform the intermediate data
intermediateStream.addTransform(finalData.stream, (record) => ({
id: record.id,
transformedValue: record.transformedValue * 2, // Double the intermediate value
anotherTransformedValue: record.transformedValue * 3, // Triple the intermediate value
transformedAt: new Date() // Update timestamp
}));
```
## Stream Configurations
### Parallelism and Retention
```typescript filename="StreamConfig.ts"
);
```
### LifeCycle Management
Control how Moose manages your stream resources when your code changes. See the [LifeCycle Management guide](./lifecycle) for detailed information.
```typescript filename="LifeCycleStreamConfig.ts"
// Production stream with external management
);
// Development stream with full management
);
```
See the [API Reference](/moose/reference/ts-moose-lib#stream) for complete configuration options.
---
## Dead Letter Queues
Source: moose/streaming/dead-letter-queues.mdx
Handle failed stream processing with dead letter queues
# Dead Letter Queues
## Overview
Dead Letter Queues (DLQs) provide a robust error handling mechanism for stream processing in Moose. When streaming functions fail during transformation or consumption, failed messages are automatically routed to a configured dead letter queue for later analysis and recovery.
## Dead Letter Record Structure
When a message fails processing, Moose creates a dead letter record with the following structure:
```ts
interface DeadLetterModel {
originalRecord: Record; // The original message that failed
errorMessage: string; // Error description
errorType: string; // Error class/type name
failedAt: Date; // Timestamp when failure occurred
source: "api" | "transform" | "table"; // Where the failure happened
}
interface DeadLetter extends DeadLetterModel {
asTyped: () => T; // Type-safe access to original record
}
```
## Creating Dead Letter Queues
### Basic Setup
```ts filename="dead-letter-setup.ts" copy
// Define your data model
interface UserEvent {
userId: string;
action: string;
timestamp: number;
}
// Create a dead letter queue for UserEvent failures
const userEventDLQ = new DeadLetterQueue("UserEventDLQ");
```
### Configuring Transformations with Dead Letter Queues
Add a dead letter queue to your Transformation Function configuration, and any errors thrown in the transformation will trigger the event to be routed to the dead letter queue.
```ts filename="transform-with-dlq.ts" copy
// Create dead letter queue
const eventDLQ = new DeadLetterQueue("EventDLQ");
// Add transform with errors to trigger DLQ, and DLQ configuration
rawEvents.stream!.addTransform(
processedEvents.stream!,
(event: RawEvent): ProcessedEvent => {
// This transform might fail for invalid data
if (!event.userId || event.userId.length === 0) {
throw new Error("Invalid userId: cannot be empty");
}
if (event.timestamp < 0) {
throw new Error("Invalid timestamp: cannot be negative");
}
return {
userId: event.userId,
action: event.action,
processedAt: new Date(),
isValid: true
};
},
{
deadLetterQueue: eventDLQ // Configure DLQ for this transform
}
);
```
### Configuring Consumers with Dead Letter Queues
Add a dead letter queue to your Consumer Function configuration, and any errors thrown in the function will trigger the event to be routed to the dead letter queue.
```ts filename="consumer-with-dlq.ts" copy
// Add consumer with errors to trigger DLQ, and DLQ configuration
rawEvents.stream!.addConsumer(
(event: RawEvent): void => {
// This consumer might fail for certain events
if (event.action === "forbidden_action") {
throw new Error("Forbidden action detected");
}
// Process the event (e.g., send to external API)
console.log(`Processing event for user ${event.userId}`);
},
{
deadLetterQueue: eventDLQ // Configure DLQ for this consumer
}
);
```
### Configuring Ingest APIs with Dead Letter Queues
Add a dead letter queue to your Ingest API configuration, and any runtime data validation failures at the API will trigger the event to be routed to the dead letter queue.
```typescript filename="ValidationExample.ts" copy
interface ExampleModel {
id: string;
userId: string;
timestamp: Date;
properties?: {
device?: string;
version?: number;
}
}
);
```
## Processing Dead Letter Messages
### Monitoring Dead Letter Queues
```ts filename="dlq-monitoring.ts" copy
// Add a consumer to monitor dead letter messages
eventDLQ.addConsumer((deadLetter) => {
console.log("Dead letter received:");
console.log(`Error: ${deadLetter.errorMessage}`);
console.log(`Error Type: ${deadLetter.errorType}`);
console.log(`Failed At: ${deadLetter.failedAt}`);
console.log(`Source: ${deadLetter.source}`);
// Access the original typed data
const originalEvent: RawEvent = deadLetter.asTyped();
console.log(`Original User ID: ${originalEvent.userId}`);
});
```
### Recovery and Retry Logic
```ts filename="dlq-recovery.ts" copy
// Create a recovery stream for fixed messages
const recoveredEvents = new Stream("recovered_events", {
destination: processedEvents.table // Send recovered data to main table
});
// Add recovery logic to the DLQ
eventDLQ.addTransform(
recoveredEvents,
(deadLetter): ProcessedEvent | null => {
try {
const originalEvent = deadLetter.asTyped();
// Apply fixes based on error type
if (deadLetter.errorMessage.includes("Invalid userId")) {
// Skip events with invalid user IDs
return null;
}
if (deadLetter.errorMessage.includes("Invalid timestamp")) {
// Fix negative timestamps
const fixedEvent = {
...originalEvent,
timestamp: Math.abs(originalEvent.timestamp)
};
return {
userId: fixedEvent.userId,
action: fixedEvent.action,
processedAt: new Date(),
isValid: true
};
}
return null; // Skip other errors
} catch (error) {
console.error("Recovery failed:", error);
return null;
}
}
);
```
## Best Practices
## Common Patterns
### Circuit Breaker Pattern
```ts filename="circuit-breaker.ts" copy
let failureCount = 0;
const maxFailures = 5;
const resetTimeout = 60000; // 1 minute
rawEvents.stream!.addTransform(
processedEvents.stream!,
(event: RawEvent): ProcessedEvent => {
if (failureCount >= maxFailures) {
throw new Error("Circuit breaker open - too many failures");
}
try {
// Your processing logic here
const result = processEvent(event);
failureCount = 0; // Reset on success
return result;
} catch (error) {
failureCount++;
if (failureCount >= maxFailures) {
setTimeout(() => { failureCount = 0; }, resetTimeout);
}
throw error;
}
},
{ deadLetterQueue: eventDLQ }
);
```
### Retry with Exponential Backoff
```ts filename="retry-backoff.ts" copy
// Create a retry DLQ with delay processing
const retryDLQ = new DeadLetterQueue("RetryDLQ");
retryDLQ.addTransform(
processedEvents.stream!,
(deadLetter): ProcessedEvent | null => {
const retryCount = deadLetter.originalRecord.retryCount || 0;
const maxRetries = 3;
if (retryCount >= maxRetries) {
console.log("Max retries exceeded, giving up");
return null;
}
// Calculate delay (exponential backoff)
const delay = Math.pow(2, retryCount) * 1000; // 1s, 2s, 4s
setTimeout(() => {
try {
const originalEvent = deadLetter.asTyped();
// Add retry count to track attempts
const eventWithRetry = {
...originalEvent,
retryCount: retryCount + 1
};
// Retry the original processing logic
processEvent(eventWithRetry);
} catch (error) {
// Will go back to DLQ with incremented retry count
throw error;
}
}, delay);
return null; // Don't emit immediately, wait for retry
}
);
```
Dead letter queues add overhead to stream processing. Use them judiciously and monitor their impact on throughput. Consider implementing sampling for high-volume streams where occasional message loss is acceptable.
Dead letter queue events can be integrated with monitoring systems like Prometheus, DataDog, or CloudWatch for alerting and dashboards. Consider tracking metrics like DLQ message rate, error types, and recovery success rates.
## Using Dead Letter Queues in Ingestion Pipelines
Dead Letter Queues (DLQs) can be directly integrated with your ingestion pipelines to capture records that fail validation or processing at the API entry point. This ensures that no data is lost, even if it cannot be immediately processed.
```typescript filename="IngestPipelineWithDLQ.ts" copy
interface ExampleSchema {
id: string;
name: string;
value: number;
timestamp: Date;
}
const pipeline = new IngestPipeline("example", {
ingestApi: true,
stream: true,
table: true,
deadLetterQueue: true, // Route failed ingestions to DLQ
});
```
See the [Ingestion API documentation](/moose/apis/ingest-api#validation) for more details and best practices on configuring DLQs for ingestion.
---
## Publish Data
Source: moose/streaming/from-your-code.mdx
Write data to streams from applications, APIs, or external sources
# Publishing Data to Streams
## Overview
Publishing data to streams allows you to write data from various sources into your Kafka/Redpanda topics. This is the first step in building real-time data pipelines.
## Publishing Methods
### Using REST APIs
The most common way to publish data is through Moose's built-in ingestion APIs. These are configured to automatically sit in front of your streams and publish data to them whenever a request is made to the endpoint:
```typescript filename="PublishViaAPI.ts"
// When you create an IngestPipeline with ingestApi: true, Moose automatically creates an API endpoint
const rawData = new IngestPipeline("raw_data", {
ingestApi: true, // Creates POST /ingest/raw_data endpoint
stream: true,
table: true
});
// You can then publish data via HTTP POST requests
const response = await fetch('/ingest/raw_data', {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({
id: '123',
value: 42
})
});
```
See the [OpenAPI documentation](/stack/open-api) to learn more about how to generate type-safe client SDKs in your language of choice for all of your Moose APIs.
### Direct Stream Publishing
You can publish directly to a stream from your Moose code using the stream's `send` method.
This is useful when emitting events from workflows or other backend logic.
`send` accepts a single record or an array of records.
If your `Stream` is configured with `schemaConfig.kind = "JSON"`,
Moose produces using the Confluent envelope automatically (0x00 + schema id + JSON).
No code changes are needed beyond setting `schemaConfig`. See the [Schema Registry guide](/moose/streaming/schema-registry).
```ts filename="DirectPublish.ts" copy
interface UserEvent {
id: Key;
userId: string;
timestamp: Date;
eventType: string;
}
// Create a stream (optionally configure destination to sync to a table)
const events = new Stream("user-events");
// Publish a single record
await events.send({
id: "evt_1",
userId: "user_123",
timestamp: new Date(),
eventType: "click",
});
// Publish multiple records
await events.send([
{ id: "evt_2", userId: "user_456", timestamp: new Date(), eventType: "view" },
{ id: "evt_3", userId: "user_789", timestamp: new Date(), eventType: "signup" },
]);
```
Moose builds the Kafka topic name from your stream name,
optional namespace, and optional version (dots become underscores).
For example, a stream named `events` with version `1.2.0` becomes `events_1_2_0`
(or `my_ns.events_1_2_0` when the namespace is `"my_ns"`).
### Using the Kafka/Redpanda Client from External Applications
You can also publish to streams from external applications using Kafka/Redpanda clients:
See the [Kafka.js documentation](https://kafka.js.org/docs/getting-started) for more information on how to use the Kafka.js client to publish to streams.
```typescript filename="ExternalPublish.ts"
const { Kafka } = KafkaJS;
const kafka = new Kafka({
kafkaJS: {
clientId: 'my-app',
brokers: ['localhost:19092']
}
});
const producer = kafka.producer();
await producer.connect();
// Publish to the stream topic
await producer.send({
topic: 'user-events', // Stream name becomes the topic name
messages: [
{
key: 'event-123',
value: JSON.stringify({
id: 'event-123',
userId: 'user-456',
timestamp: new Date().toISOString(),
eventType: 'page_view'
})
}
]
});
```
#### Locating Redpanda Connection Details
When running your Moose backend within your local dev environment, you can find the connection details for your Redpanda cluster in the `moose.config.toml` file in the root of your project:
```toml filename="moose.config.toml" copy
[redpanda_config]
broker = "localhost:19092"
message_timeout_ms = 1000
retention_ms = 30000
replication_factor = 1
```
---
## Schema Registry
Source: moose/streaming/schema-registry.mdx
Use Confluent Schema Registry with Moose streams (JSON Schema first)
# Schema Registry Integration
The first supported encoding is JSON Schema. Avro and Protobuf are planned.
## Overview
Moose can publish and consume Kafka/Redpanda messages using Confluent Schema Registry. The first supported encoding is JSON Schema; Avro and Protobuf are planned.
## Configure Schema Registry URL
Set the Schema Registry URL in `moose.config.toml` under `redpanda_config` (aliased as `kafka_config`). You can also override with environment variables.
```toml filename="moose.config.toml" copy
[redpanda_config]
broker = "localhost:19092"
schema_registry_url = "http://localhost:8081"
```
Environment overrides (either key works):
```bash filename="Terminal" copy
export MOOSE_REDPANDA_CONFIG__SCHEMA_REGISTRY_URL=http://localhost:8081
# or
export MOOSE_KAFKA_CONFIG__SCHEMA_REGISTRY_URL=http://localhost:8081
```
## Referencing Schemas
You can attach a Schema Registry reference to any `Stream` via `schemaConfig`. Use one of:
- Subject latest: `{ subjectLatest: string }`
- Subject and version: `{ subject: string, version: number }`
- Schema id: `{ id: number }`
```ts filename="sr-stream.ts" copy {9,16-23}
interface Event {
id: string;
value: number;
}
const schemaConfig: KafkaSchemaConfig = {
kind: "JSON",
reference: { subjectLatest: "event-value" },
};
);
// Producing uses Schema Registry envelope automatically
await events.send({ id: "e1", value: 42 });
```
## Consuming SR JSON in Runners
Moose streaming runners automatically detect the Confluent JSON envelope
when consuming and strip the header before parsing the JSON.
Your transformation code continues to work unchanged.
## Ingestion APIs and SR
When an Ingest API routes to a topic that has a `schemaConfig` of kind JSON,
Moose resolves the schema id and publishes requests using the Schema Registry envelope.
You can also set the reference to a fixed `id` to skip lookups.
## Discover existing topics and schemas
Use the CLI to pull external topics and optionally fetch JSON Schemas from Schema Registry to emit typed models.
```bash filename="Terminal" copy
moose kafka pull \
--schema-registry http://localhost:8081 \
--path app/external-topics \
--include "*" \
--exclude "{__consumer_offsets,_schemas}"
```
This writes external topic declarations under the provided path based on language (default path is inferred).
## Current limitations
- JSON Schema only (Avro/Protobuf planned)
- Ingest API schema declared in code may not match the actual schema in registry.
---
## Sync to Table
Source: moose/streaming/sync-to-table.mdx
Automatically sync stream data to OLAP tables with intelligent batching
# Sync to Table
## Overview
Moose automatically handles batch writes between streams and OLAP tables through a **destination configuration**. When you specify a `destination` OLAP table for a stream, Moose provisions a background synchronization process that batches and writes data from the stream to the table.
### Basic Usage
```ts filename="SyncToTable.ts" copy {13}
interface Event {
id: Key;
userId: string;
timestamp: Date;
eventType: string;
}
const eventsTable = new OlapTable("events");
const eventsStream = new Stream("events", {
destination: eventsTable // This configures automatic batching
});
```
## Setting Up Automatic Sync
### Using IngestPipeline (Easiest)
The simplest way to set up automatic syncing is with an `IngestPipeline`, which creates all components and wires them together:
```ts filename="AutoSync.ts" copy
interface Event {
id: Key;
userId: string;
timestamp: Date;
eventType: string;
}
// Creates stream, table, API, and automatic sync
const eventsPipeline = new IngestPipeline("events", {
ingestApi: true, // Creates HTTP endpoint at POST /ingest/events
stream: true, // Creates buffering stream
table: true // Creates destination table + auto-sync process
});
```
### Standalone Components
For more granular control, you can configure components individually:
```ts filename="ManualSync.ts" copy
interface Event {
id: Key;
userId: string;
timestamp: Date;
eventType: string;
}
// Create table first
const eventsTable = new OlapTable("events");
// Create stream with destination table (enables auto-sync)
const eventsStream = new Stream("events", {
destination: eventsTable // This configures automatic batching
});
// Create API that writes to the stream
const eventsApi = new IngestApi("events", {
destination: eventsStream
});
```
## How Automatic Syncing Works
When you configure a stream with a `destination` table, Moose automatically handles the synchronization by managing a Rust process process in the background.
Moose creates a **Rust background process** that:
1. **Consumes** messages from the stream (Kafka/Redpanda topic)
2. **Batches** records up to 100,000 or flushes every second (whichever comes first)
3. **Executes** optimized ClickHouse `INSERT` statements
4. **Commits** stream offsets after successful writes
5. **Retries** failed batches with exponential backoff
Default batching parameters:
| Parameter | Value | Description |
|-----------|-------|-------------|
| `MAX_BATCH_SIZE` | 100,000 records | Maximum records per batch insert |
| `FLUSH_INTERVAL` | 1 second | Automatic flush regardless of batch size |
Currently, you cannot configure the batching parameters, but we're interested in adding this feature. If you need this capability, let us know on slack!
[ClickHouse inserts need to be batched for optimal performance](https://clickhouse.com/blog/asynchronous-data-inserts-in-clickhouse#data-needs-to-be-batched-for-optimal-performance). Moose automatically handles this optimization internally, ensuring your data is efficiently written to ClickHouse without any configuration required.
## Data Flow Example
Here's how data flows through the automatic sync process:
```ts filename="DataFlow.ts" copy
// 1. Data sent to ingestion API
fetch('http://localhost:4000/ingest/events', {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify({
id: 'evt_123',
userId: 'user_456',
timestamp: '2024-01-15T10:30:00Z',
eventType: 'click'
})
})
// 2. API validates and writes to stream
// 3. Background sync process batches stream data
// 4. Batch automatically written to ClickHouse table when:
// - Batch reaches 100,000 records, OR
// - 1 second has elapsed since last flush
// 5. Data available for queries in events table
sql`SELECT * FROM events WHERE userId = 'user_456';`
```
## Monitoring and Observability
The sync process provides built-in observability within the Moose runtime:
- **Batch Insert Logs**: Records successful batch insertions with sizes and offsets
- **Error Handling**: Logs transient failures with retry information
- **Metrics**: Tracks throughput, batch sizes, and error rates
- **Offset Tracking**: Maintains Kafka consumer group offsets for reliability
---
## Transformation Functions
Source: moose/streaming/transform-functions.mdx
Process and transform data in-flight between streams
# Transformation Functions
## Overview
Transformations allow you to process and reshape data as it flows between streams. You can filter, enrich, reshape, and combine data in-flight before it reaches its destination.
## Implementing Transformations
### Reshape and Enrich Data
Transform data shape or enrich records:
```typescript filename="DataTransform.ts"
interface RawEvent {
id: Key;
timestamp: string;
data: {
user_id: string;
platform: string;
app_version: string;
ip_address: string;
}
}
interface EnrichedEvent {
eventId: Key;
timestamp: Date;
userId: Key;
properties: {
platform: string;
version: string;
country: string;
};
metadata: {
originalTimestamp: string;
processedAt: Date;
}
}
const rawStream = new Stream("raw_events");
const enrichedStream = new Stream("enriched_events");
// Reshape and enrich data
rawStream.addTransform(enrichedStream, async (record: RawEvent) => ({
eventId: record.id,
timestamp: new Date(record.timestamp),
userId: record.data.user_id,
properties: {
platform: record.data.platform || 'unknown',
version: record.data.app_version,
country: await lookupCountry(record.data.ip_address)
},
metadata: {
originalTimestamp: record.timestamp,
processedAt: new Date()
}
}));
```
### Filtering
Remove or filter records based on conditions:
```typescript filename="FilterStream.ts"
interface MetricRecord {
id: string;
name: string;
value: number;
timestamp: Date;
}
const inputStream = new Stream("input_metrics");
const validMetrics = new Stream("valid_metrics");
// Multiple filtering conditions
inputStream.addTransform(validMetrics, (record) => {
// Filter out records with invalid values
if (isNaN(record.value) || record.value < 0) {
return undefined;
}
// Filter out old records
if (record.timestamp < getStartOfDay()) {
return undefined;
}
// Filter out specific metrics
if (record.name.startsWith('debug_')) {
return undefined;
}
return record;
});
```
### Fan Out (1:N)
Send data to multiple downstream processors:
```ts filename="FanOut.ts" copy
interface Order {
orderId: string;
userId: string;
amount: number;
items: string[];
}
interface HighPriorityOrder extends Order {
priority: 'high';
}
interface ArchivedOrder extends Order {
archivedAt: Date;
}
// Define destination streams
const analyticsStream = new Stream("order_analytics");
const notificationStream = new Stream("order_notifications");
const archiveStream = new Stream("order_archive");
// Source stream
const orderStream = new Stream("orders");
// Send all orders to analytics
orderStream.addTransform(analyticsStream, (order) => order);
// Send large orders to notifications
orderStream.addTransform(notificationStream, (order) => {
if (order.amount > 1000) {
return {
...order,
priority: 'high'
};
}
return undefined; // Skip small orders
});
// Archive all orders
orderStream.addTransform(archiveStream, (order) => ({
...order,
archivedAt: new Date()
}));
```
### Fan In (N:1)
Combine data from multiple sources:
```typescript filename="FanIn.ts"
interface UserEvent {
userId: Key;
eventType: string;
timestamp: Date;
source: string;
}
// Source streams
const webEvents = new Stream("web_events");
const mobileEvents = new Stream("mobile_events");
const apiEvents = new Stream("api_events");
// Create a stream and table for the combined events
const eventsTable = new OlapTable("all_events");
const allEvents = new Stream("all_events", {
destination: eventsTable
});
// Fan in from web
webEvents.addTransform(allEvents, (event) => ({
...event,
source: 'web',
timestamp: new Date()
}));
// Fan in from mobile
mobileEvents.addTransform(allEvents, (event) => ({
...event,
source: 'mobile',
timestamp: new Date()
}));
// Fan in from API
apiEvents.addTransform(allEvents, (event) => ({
...event,
source: 'api',
timestamp: new Date()
}));
```
### Unnesting
Flatten nested records:
```typescript filename="Unnest.ts"
interface NestedRecord {
id: Key;
nested: {
value: number;
}[];
}
interface FlattenedRecord {
id: Key;
value: number;
}
const nestedStream = new Stream("nested_records");
const flattenedStream = new Stream("flattened_records");
nestedStream.addTransform(flattenedStream, (record) => record.nested.map((n) => ({
id: record.id,
value: n.value
})));
```
You cannot have multiple transforms between the same source and destination stream. If you need multiple transformation routes, you must either:
- Use conditional logic inside a single streaming function to handle different cases, or
- Implement a fan-out/fan-in pattern, where you route records to different intermediate streams and then merge them back into the destination stream.
## Error Handling with Dead Letter Queues
When stream processing fails, you can configure dead letter queues to capture failed messages for later analysis and recovery. This prevents single message failures from stopping your entire pipeline.
```typescript filename="DeadLetterQueue.ts" copy
interface UserEvent {
userId: string;
action: string;
timestamp: number;
}
interface ProcessedEvent {
userId: string;
action: string;
processedAt: Date;
isValid: boolean;
}
// Create pipelines
const rawEvents = new IngestPipeline("raw_events", {
ingestApi: true,
stream: true,
table: false
});
const processedEvents = new IngestPipeline("processed_events", {
ingestApi: false,
stream: true,
table: true
});
// Create dead letter queue for failed transformations
const eventDLQ = new DeadLetterQueue("EventDLQ");
// Add transform with error handling
rawEvents.stream!.addTransform(
processedEvents.stream!,
(event: UserEvent): ProcessedEvent => {
// This might fail for invalid data
if (!event.userId || event.userId.length === 0) {
throw new Error("Invalid userId: cannot be empty");
}
return {
userId: event.userId,
action: event.action,
processedAt: new Date(),
isValid: true
};
},
{
deadLetterQueue: eventDLQ // Failed messages go here
}
);
// Monitor dead letter messages
eventDLQ.addConsumer((deadLetter) => {
console.log(`Error: ${deadLetter.errorMessage}`);
console.log(`Failed at: ${deadLetter.failedAt}`);
// Access original typed data
const originalEvent: UserEvent = deadLetter.asTyped();
console.log(`Original User ID: ${originalEvent.userId}`);
});
```
For comprehensive dead letter queue patterns, recovery strategies, and best practices, see the [Dead Letter Queues guide](./dead-letter-queues).
---
## Templates & Apps
Source: moose/templates-examples.mdx
Browse templates and demo apps for MooseStack
# Templates & Apps
Moose provides two ways to get started: **templates** and **demo apps**. Templates are simple skeleton applications that you can initialize with `moose init`, while demo apps are more advanced examples available on GitHub that showcase real-world use cases and integrations.
**Initialize a template:**
```bash filename="Terminal" copy
moose init PROJECT_NAME TEMPLATE_NAME
```
**List available templates:**
```bash filename="Terminal" copy
moose template list
```
## Popular Apps
---
## Browse Apps
### Nextjs + Express + MCP demo app: Aircraft data [#plane-transponder-demo]
Complete demo application featuring real-time aircraft transponder data with MCP chat integration.
**Repository:** [https://github.com/514-labs/planes](https://github.com/514-labs/planes)
Key Features:
Next.js
Express
MCP
Moose OLAP
ClickHouse
---
### Postgres to ClickHouse CDC with Debezium [#postgres-clickhouse-cdc]
Easy-to-run demo of a CDC pipeline using Debezium, PostgreSQL, Redpanda, and ClickHouse.
**Repository:** [https://github.com/514-labs/debezium-cdc](https://github.com/514-labs/debezium-cdc)
**Blog Post:** [Code-First CDC to ClickHouse with Debezium, Redpanda, and MooseStack](https://www.fiveonefour.com/blog/cdc-postgres-to-clickhouse-debezium-drizzle)
Key Features:
CDC
Debezium
PostgreSQL
Redpanda
ClickHouse
Drizzle ORM
---
### User-facing analytics reference app (Postgres + Clickhouse + React) [#foobar-ufa]
A complete reference architecture showing how to add a dedicated analytics microservice to an existing application without impacting your primary database. Features Postgres + ClickHouse + React frontend with chat analytics.
**Repository:** [https://github.com/514-labs/area-code/tree/main/ufa](https://github.com/514-labs/area-code/tree/main/ufa)
Key Features:
PostgreSQL
ClickHouse
React
TanStack Query
Supabase
Moose OLAP
Moose Streaming
Moose APIs
Elasticsearch
Temporal
---
### User-facing analytics reference app (Clickhouse Cloud + React) [#foobar-ufa-lite]
A simplified version of the UFA architecture using ClickHouse Cloud + React frontend with chat analytics. This version demonstrates a cloud-native approach without local infrastructure dependencies.
**Repository:** [https://github.com/514-labs/area-code/tree/main/ufa-lite](https://github.com/514-labs/area-code/tree/main/ufa-lite)
Key Features:
ClickHouse Cloud
React
Moose OLAP
Moose APIs
---
## Browse Templates
### TypeScript (Default) [#typescript-default]
Default TypeScript project, seeded with foobar example components.
```bash filename="Terminal" copy
moose init PROJECT_NAME typescript
```
**Repository:** [https://github.com/514-labs/moosestack/tree/main/templates/typescript](https://github.com/514-labs/moosestack/tree/main/templates/typescript)
Key Features:
Moose APIs
Moose OLAP
Moose Streaming
Moose Workflows
---
### Python (Default) [#python-default]
Default Python project, seeded with foobar example components.
```bash filename="Terminal" copy
moose init PROJECT_NAME python
```
**Repository:** [https://github.com/514-labs/moosestack/tree/main/templates/python](https://github.com/514-labs/moosestack/tree/main/templates/python)
Key Features:
Moose APIs
Moose OLAP
Moose Streaming
Moose Workflows
---
### TypeScript (Empty) [#typescript-empty]
Empty TypeScript project with minimal structure.
```bash filename="Terminal" copy
moose init PROJECT_NAME typescript-empty
```
**Repository:** [https://github.com/514-labs/moosestack/tree/main/templates/typescript-empty](https://github.com/514-labs/moosestack/tree/main/templates/typescript-empty)
Key Features:
TypeScript
Moose OLAP
---
### Python (Empty) [#python-empty]
Empty Python project with minimal structure.
```bash filename="Terminal" copy
moose init PROJECT_NAME python-empty
```
**Repository:** [https://github.com/514-labs/moosestack/tree/main/templates/python-empty](https://github.com/514-labs/moosestack/tree/main/templates/python-empty)
Key Features:
Python
Moose OLAP
---
### Next.js (Empty) [#nextjs-empty]
TypeScript project with a Next.js frontend (empty).
```bash filename="Terminal" copy
moose init PROJECT_NAME next-app-empty
```
**Repository:** [https://github.com/514-labs/moosestack/tree/main/templates/next-app-empty](https://github.com/514-labs/moosestack/tree/main/templates/next-app-empty)
Key Features:
Next.js
TypeScript
Moose APIs
Moose OLAP
Moose Streaming
Moose Workflows
---
### Express.js [#express]
TypeScript project using Express for serving analytical APIs.
```bash filename="Terminal" copy
moose init PROJECT_NAME typescript-express
```
**Repository:** [https://github.com/514-labs/moosestack/tree/main/templates/typescript-express](https://github.com/514-labs/moosestack/tree/main/templates/typescript-express)
Key Features:
Express.js
TypeScript
Moose OLAP
Moose Streaming
Moose Workflows
---
### TypeScript MCP [#typescript-mcp]
TypeScript project with an MCP (Model Context Protocol) implementation using Express. The included example tool enables LLMs to query ClickHouse.
```bash filename="Terminal" copy
moose init PROJECT_NAME typescript-mcp
```
**Repository:** [https://github.com/514-labs/moosestack/tree/main/templates/typescript-mcp](https://github.com/514-labs/moosestack/tree/main/templates/typescript-mcp)
Key Features:
TypeScript
MCP
Express
Moose OLAP
Moose Streaming
Moose Workflows
---
### FastAPI [#fastapi]
Python project using FastAPI for serving analytical APIs.
```bash filename="Terminal" copy
moose init PROJECT_NAME python-fastapi
```
**Repository:** [https://github.com/514-labs/moosestack/tree/main/templates/python-fastapi](https://github.com/514-labs/moosestack/tree/main/templates/python-fastapi)
Key Features:
FastAPI
Python
Moose OLAP
Moose Streaming
Moose Workflows
---
### FastAPI (Client-Only) [#fastapi-client-only]
FastAPI client-only project using MooseStack libraries without requiring the Moose runtime.
```bash filename="Terminal" copy
moose init PROJECT_NAME python-fastapi-client-only
```
**Repository:** [https://github.com/514-labs/moosestack/tree/main/templates/python-fastapi-client-only](https://github.com/514-labs/moosestack/tree/main/templates/python-fastapi-client-only)
Key Features:
FastAPI
Python
Moose OLAP
Moose Streaming
Moose Workflows
---
### ADS-B (Aircraft Tracking) [#adsb]
Real-time aircraft transponder data tracking.
```bash filename="Terminal" copy
moose init PROJECT_NAME ads-b
```
**Repository:** [https://github.com/514-labs/moosestack/tree/main/templates/ads-b](https://github.com/514-labs/moosestack/tree/main/templates/ads-b)
Key Features:
Moose APIs
Moose OLAP
Moose Streaming
Moose Workflows
---
### ADS-B with Frontend [#adsb-frontend]
Real-time aircraft transponder data with a React frontend.
```bash filename="Terminal" copy
moose init PROJECT_NAME ads-b-frontend
```
**Repository:** [https://github.com/514-labs/moosestack/tree/main/templates/ads-b-frontend](https://github.com/514-labs/moosestack/tree/main/templates/ads-b-frontend)
Key Features:
Next.js
React
Moose APIs
Moose OLAP
Moose Workflows
---
### Live Heart Rate Leaderboard [#heartrate]
Live heart rate leaderboard inspired by F45 with Streamlit frontend.
```bash filename="Terminal" copy
moose init PROJECT_NAME live-heartrate-leaderboard
```
**Repository:** [https://github.com/514-labs/moosestack/tree/main/templates/live-heartrate-leaderboard](https://github.com/514-labs/moosestack/tree/main/templates/live-heartrate-leaderboard)
Key Features:
Streamlit
Python
Moose APIs
Moose OLAP
Moose Streaming
Moose Workflows
---
## Moose Workflows
Source: moose/workflows.mdx
Build ETL pipelines, scheduled jobs, and long-running tasks with orchestration
# Moose Workflows
## Overview
The Workflows module provides standalone task orchestration and automation. You can use this capability independently to build ETL pipelines, run scheduled jobs, trigger background tasks, and manage long-running tasks without requiring other MooseStack components like databases or streams.
### Basic Usage
```typescript filename="DataFlow.ts" copy
export interface Foo {
name: string;
}
export interface Bar {
name: string;
greeting: string;
counter: number;
}
);
);
);
```
### Enabling Workflows
To enable workflows, you need to add the `workflows` feature to your `moose.config.toml` file:
```toml filename="moose.config.toml" copy
[features]
workflows = true
```
## Core Capabilities
## Integration with Other Capabilities
While the Workflows capability works independently, it is designed to be used in conjunction with other MooseStack capabilities:
---
## moose/workflows/cancel-workflow
Source: moose/workflows/cancel-workflow.mdx
# Cancel a Running Workflow
To stop a workflow before it has finished running, use the `workflow cancel` command.
```bash filename="Terminal" copy
moose workflow cancel
```
### Implementing Cancelation Callbacks
For workflows that are running and have clean up operations to perform, you can implement a termination callback.
This is especially useful for any long running tasks that have open connections or subscriptions to other services that need to be closed.
You may also use the `state` within the run/cancel context to supplement your business logic.
```typescript filename="workflows/workflows.ts" copy
const task1 = new Task({
name: "task1",
run: async (ctx) => {
connection.open();
},
onCancel: async (ctx) => {
// Clean up any resources
connection.close();
},
});
const myworkflow = new Workflow({
name: "myworkflow",
startingTask: task1,
retries: 3,
});
```
---
## Define Workflows
Source: moose/workflows/define-workflow.mdx
Create workflow definitions with task sequences and data flow
# Define Workflows
## Overview
Workflows automate task sequences with built-in reliability and monitoring. Tasks execute in order, passing data between steps.
Built on Temporal for reliability, retries, and monitoring via GUI dashboard.
## Writing Workflow Tasks
Tasks are objects with a `run` function. Return values automatically pass to the next task.
```typescript filename="app/index.ts" copy
export interface Foo {
name: string;
}
);
);
```
Export `Task` and `Workflow` objects. Specify `startingTask` in the `Workflow` config.
## Data Flow Between Tasks
Tasks communicate through their return values. Each task can return an object that is automatically passed as input to the next task in the workflow.
- Only values inside the object are passed to the next task.
- The object must be JSON-serializable.
```typescript filename="app/index.ts" copy
export interface Foo {
name: string;
}
export interface Bar {
name: string;
greeting: string;
counter: number;
}
);
);
);
```
## Debugging Workflows
While the Temporal dashboard is a helpful tool for debugging, you can also leverage the Moose CLI to monitor and debug workflows. This is useful if you want to monitor a workflow without having to leave your terminal.
Use the `moose workflow status` command to monitor a workflow:
```bash filename="Terminal" copy
moose workflow status example
```
This will print high level information about the workflow run:
```txt filename="Terminal"
Workflow Workflow Status: example
Run ID: 446eab6e-663d-4913-93fe-f79d6109391f
Status: WORKFLOW_EXECUTION_STATUS_COMPLETED ✅
Execution Time: 66s
```
If you want more detailed information about the workflow's status, including task level logs and inputs/outputs, you can use the `--verbose` flag:
```bash filename="Terminal" copy
moose workflow status example --verbose
```
```txt filename="Terminal"
Workflow Workflow Status: example
Run ID: 446eab6e-663d-4913-93fe-f79d6109391f
Status: WORKFLOW_EXECUTION_STATUS_COMPLETED ✅
Execution Time: 66s
Request: GetWorkflowExecutionHistoryRequest { namespace: "default", execution: Some(WorkflowExecution { workflow_id: "example", run_id: "446eab6e-663d-4913-93fe-f79d6109391f" }), maximum_page_size: 0, next_page_token: [], wait_new_event: false, history_event_filter_type: Unspecified, skip_archival: false }
Found 17 events
Event History:
• [2025-02-21T14:16:56.234808764+00:00] EVENT_TYPE_WORKFLOW_EXECUTION_STARTED
• [2025-02-21T14:16:56.235132389+00:00] EVENT_TYPE_WORKFLOW_TASK_SCHEDULED
• [2025-02-21T14:16:56.259341847+00:00] EVENT_TYPE_WORKFLOW_TASK_STARTED
• [2025-02-21T14:16:56.329856180+00:00] EVENT_TYPE_WORKFLOW_TASK_COMPLETED
• [2025-02-21T14:16:56.329951889+00:00] EVENT_TYPE_ACTIVITY_TASK_SCHEDULED
Activity: example/task1
• [2025-02-21T14:16:56.333761680+00:00] EVENT_TYPE_ACTIVITY_TASK_STARTED
• [2025-02-21T14:16:56.497156055+00:00] EVENT_TYPE_ACTIVITY_TASK_COMPLETED
Result:
{
"counter": 1,
"greeting": "hello, no name!",
"name": "no name",
}
```
With this more detailed output, you can see the exact sequence of events and the inputs and outputs of each task. This is useful for debugging and understanding the workflow's behavior.
The result of each task is included in the output, allowing you to inspect the data that was passed between task for debugging purposes.
If your workflow fails due to some runtime error, you can use the event history timeline to identify the task that failed.
---
## moose/workflows/retries-and-timeouts
Source: moose/workflows/retries-and-timeouts.mdx
# Error Detection and Handling
Moose provides multiple layers of error protection, both at the workflow and task level:
### Workflow-Level Retries and Timeouts
Moose automatically catches any runtime errors during workflow execution. Errors are logged for debugging, and the orchestrator will retry failed tasks according to the `retries` option.
In your `Workflow`, you can configure the following options to control workflow behavior, including timeouts and retries:
```typescript filename="app/index.ts" {5,6} copy
);
```
### Task-Level Errors and Retries
For more granular control over task-level errors and retries, you can configure your individual tasks to have their own retry behavior.
For workflows & tasks that may not have a predefined timeout, you may set `never` as the timeout.
```typescript filename="app/index.ts" {5-6} copy
);
);
```
### Example: Workflow and Task Retry Interplay
When configuring retries, it's important to understand how workflow-level and task-level retries interact. Consider the following scenario:
- **Workflow Retry Policy**: 2 attempts
- **Task Retry Policy**: 3 attempts
```typescript filename="app/index.ts" {5,10} copy
);
);
```
If the execution of the workflow encounters an error, the retry sequence would proceed as follows:
1. **Workflow Attempt 1**
- **Task Attempt 1**: Task fails
- **Task Attempt 2**: Task fails
- **Task Attempt 3**: Task fails
- Workflow attempt fails after exhausting task retries
2. **Workflow Attempt 2**
- **Task Attempt 1**: Task fails
- **Task Attempt 2**: Task fails
- **Task Attempt 3**: Task fails
- Workflow attempt fails after exhausting task retries
In this example, the workflow will make a total of 2 attempts, and each task within those attempts will retry up to 3 times before the workflow itself retries.
---
## Schedule Workflows
Source: moose/workflows/schedule-workflow.mdx
Set up recurring and scheduled workflow execution
# Schedule Workflows
## Overview
Moose workflows can be configured to run automatically on a schedule using cron expressions or interval-based scheduling. This enables you to automate recurring tasks, data processing jobs, and maintenance operations.
## Scheduling Workflows
Workflows can be configured to run on a schedule using the `schedule` field in `Workflow`. This field is optional and blank by default.
### Cron Expressions
```typescript filename="app/scheduled-workflow.ts" copy
);
```
#### Cron Expression Format
```text
|------------------------------- Minute (0-59)
| |------------------------- Hour (0-23)
| | |------------------- Day of the month (1-31)
| | | |------------- Month (1-12; or JAN to DEC)
| | | | |------- Day of the week (0-6; or SUN to SAT; or 7 for Sunday)
| | | | |
| | | | |
* * * * *
```
#### Common Cron Examples
| Cron Expression | Description |
|-----------------|-------------|
| 0 12 * * * | Runs at 12:00 PM every day |
| 0 0 * * 0 | Runs at 12:00 AM every Sunday |
| 0 8 * * 1-5 | Runs at 8:00 AM on weekdays (Monday to Friday) |
| * * * * * | Runs every minute |
| 0 */6 * * * | Runs every 6 hours |
| 0 9 1 * * | Runs at 9:00 AM on the first day of every month |
| 0 0 1 1 * | Runs at midnight on January 1st every year |
Use an online cron expression visualizer like [crontab.guru](https://crontab.guru/) to help you understand how the cron expression will schedule your workflow.
### Interval Schedules
Interval schedules can be specified as a string `"@every "`. The interval follows standard duration format:
```typescript filename="app/interval-workflow.ts" copy
);
```
#### Interval Examples
| Interval | Description |
|----------|-------------|
| `@every 30s` | Every 30 seconds |
| `@every 5m` | Every 5 minutes |
| `@every 1h` | Every hour |
| `@every 12h` | Every 12 hours |
| `@every 24h` | Every 24 hours |
| `@every 7d` | Every 7 days |
## Practical Scheduling Examples
### Daily Data Processing
```typescript filename="app/daily-etl.ts" copy
);
```
### Weekly Reports
```typescript filename="app/weekly-reports.ts" copy
);
```
### High-Frequency Monitoring
```typescript filename="app/monitoring.ts" copy
);
```
## Monitoring Scheduled Workflows
### Development Environment
If your dev server is running, you should see logs in the terminal when your scheduled workflow is executed:
```bash filename="Terminal" copy
moose dev
```
```txt filename="Terminal"
[2024-01-15 12:00:00] Scheduled workflow 'daily-data-processing' started
[2024-01-15 12:00:01] Task 'extract' completed successfully
[2024-01-15 12:00:15] Task 'transform' completed successfully
[2024-01-15 12:00:30] Task 'load' completed successfully
[2024-01-15 12:00:30] Workflow 'daily-data-processing' completed successfully
```
### Checking Workflow Status
You can check the status of scheduled workflows using the CLI:
```bash filename="Terminal" copy
# List all workflows defined in your project
moose workflow list
# Alternative command to list all workflows
moose ls --type workflows
# View workflow execution history
moose workflow history
# Check specific workflow status
moose workflow status daily-data-processing
# Get detailed execution history
moose workflow status daily-data-processing --verbose
```
### Temporal Dashboard
Access the Temporal dashboard to view scheduled workflow executions:
```bash filename="Terminal" copy
# Open Temporal dashboard (typically at http://localhost:8080)
open http://localhost:8080
```
The dashboard shows:
- Scheduled workflow definitions
- Execution history and timing
- Success/failure rates
- Retry attempts and errors
## Best Practices for Scheduled Workflows
### Timeout and Retry Configuration
Configure appropriate timeouts and retries for scheduled workflows:
```typescript filename="app/robust-scheduled-workflow.ts" copy
);
);
```
## Troubleshooting Scheduled Workflows
### Common Issues
- **Timezone considerations**: Cron schedules use UTC by default
- **Resource conflicts**: Ensure scheduled workflows don't compete for resources
- **Long-running tasks**: Set appropriate timeouts for lengthy operations
- **Error handling**: Implement proper error handling and logging
---
## Trigger Workflows
Source: moose/workflows/trigger-workflow.mdx
Start workflows from events, APIs, or external triggers
# Trigger Workflows
## Overview
Moose workflows can be triggered programmatically from various sources including APIs, events, external systems, or manual execution. This enables you to build reactive data processing pipelines and on-demand task execution.
## Manual Workflow Execution
The simplest way to trigger a workflow is using the Moose CLI:
```bash filename="Terminal" copy
# Run a workflow manually
moose workflow run example
# Run with input parameters
moose workflow run example --input '{"name": "John", "email": "john@example.com"}'
```
### Passing Input to Workflows
When triggering workflows, you can pass input data that will be passed to the starting task:
```bash filename="Terminal" copy
moose workflow run data-processing --input '{
"sourceUrl": "https://api.example.com/data",
"apiKey": "your-api-key",
"batchSize": 100
}'
```
The input is parsed as JSON and passed to the workflow's starting task.
## API-Triggered Workflows
Trigger workflows directly via an HTTP POST endpoint exposed by the webserver.
- Endpoint: `/workflows/{workflowName}/trigger`
### Request
- Body: optional JSON payload passed to the workflow's starting task.
Example:
```bash filename="Terminal" copy
curl -X POST 'http://localhost:4000/workflows/data-processing/trigger' \
-H 'Content-Type: application/json' \
-d '{
"inputValue": "process-user-data",
"priority": "high"
}'
```
### Authentication
- Local development: no auth required.
- Production: protect the endpoint using an API key. Follow these steps:
1. Generate a token and hashed key (see the Token Generation section in the API Auth docs):
```bash filename="Terminal" copy
moose generate hash-token
# Outputs:
# - ENV API Key (hashed) → for environment/config
# - Bearer Token (plain) → for Authorization header
```
2. Configure the server with the hashed key:
```bash copy
MOOSE_CONSUMPTION_API_KEY=""
```
3. Call the endpoint using the plain Bearer token from step 1:
```bash filename="Terminal" copy
curl -X POST 'https://your-host/workflows/data-processing/trigger' \
-H 'Authorization: Bearer ' \
-H 'Content-Type: application/json' \
-d '{"inputValue":"process-user-data"}'
```
For details, see the API Auth page under “Token Generation” and “API Endpoints”.
### Response
```json filename="Response"
{
"workflowId": "data-processing-",
"runId": "",
}
```
In local development, the response also includes a `dashboardUrl` to Temporal UI:
```json filename="Response (dev)"
{
"workflowId": "data-processing-",
"runId": "",
"dashboardUrl": "http://localhost:8080/namespaces//workflows/data-processing-//history"
}
```
## Terminate a Running Workflow
After triggering a workflow, you can terminate it via an HTTP endpoint.
- Endpoint: `POST /workflows/{workflowId}/terminate`
### Request
- Local development (no auth):
```bash filename="Terminal" copy
curl -X POST 'http://localhost:4000/workflows/data-processing-/terminate'
```
- Production (Bearer token required):
```bash filename="Terminal" copy
curl -X POST 'https://your-host/workflows/data-processing-/terminate' \
-H 'Authorization: Bearer ''
```
---
## October 24, 2025
Source: release-notes/2025-10-24.mdx
Release notes for October 24, 2025
# October 24, 2025
* **New:** `moose migrate --clickhouse-url` enables serverless ClickHouse schema deploys
* **Improved:** JSON columns now accept dynamic payloads with fine-grained controls
## Serverless ClickHouse migrations with `moose migrate`
Run schema diffs and applies straight against a ClickHouse endpoint—perfect for OLAP-only or CI/CD environments that don’t boot the full Moose runtime.
```bash
# Detect changes and persist the migration plan
moose generate migration \
--clickhouse-url "https://user:pass@ch.serverless.dev/main" \
--save
# Apply the plan directly to ClickHouse
moose migrate --clickhouse-url "https://user:pass@ch.serverless.dev/main"
```
🐙 PR: ([#2872](https://github.com/514labs/moose/pull/2872)) | 📘 Docs: [Serverless ClickHouse migrations guide](/moose/migrate#serverless-deployments)
## Adaptive JSON columns with `__mooseJsonOptions`
Model semi-structured payloads while locking in typed paths for the fields you care about.
```typescript
export interface UserActivity {
id: Key;
metadata: {
userId: string;
sessionId: string;
__mooseJsonOptions: {
maxDynamicPaths: 256;
typedPaths: [
["userId", "String"],
["sessionId", "String"]
];
skipRegexps: ["^debug\\."];
};
};
}
```
🐙 PR:([#2887](https://github.com/514labs/moose/pull/2887)) | 📘 Docs: [Configurable JSON columns reference](/moose/data-modeling#configurable-json-columns)
## Moose
- **`moose migrate --clickhouse-url`** – Generate and apply migrations directly against hosted ClickHouse, ideal for OLAP-only or CI/CD workflows that run without the full Moose runtime. [Docs: Serverless ClickHouse migrations](/moose/migrate#serverless-deployments) | PRs [#2872](https://github.com/514labs/moose/pull/2872).
- **LLM-friendly docs & endpoints** – Framework pages expose TS/Py “LLM view” links and the CLI now serves `/llm-ts.txt` + `/llm-py.txt` for assistants that need scoped context. [Docs: LLM docs](/moose/llm-docs) | PRs [#2892](https://github.com/514labs/moose/pull/2892).
- **Flexible JSON columns** – `__mooseJsonOptions` lets models cap dynamic paths, pin typed paths, or skip keys/regexes so ingestion can accept evolving payloads without breaking typed reads. [Docs: Configurable JSON columns](/moose/data-modeling#configurable-json-columns) | PRs [#2887](https://github.com/514labs/moose/pull/2887).
- **Configurable `source_dir`** – `moose.config.toml` can point at `src/` (or any folder) instead of the default `app/`, simplifying adoption inside existing repos. [Docs: Custom source directory](/moose/configuration#custom-source-directory) | PRs [#2886](https://github.com/514labs/moose/pull/2886).
- **Array transforms can fan out events** – Transform functions that return arrays automatically emit one Kafka message per element, covering explode/normalize patterns without extra producers. [Docs: Array transforms](/moose/streaming/transform-functions#array-transforms) | PRs [#2882](https://github.com/514labs/moose/pull/2882).
- **ClickHouse modeling controls** – Table DSL now covers TTL per table/column, `sampleByExpression`, and fully configurable secondary indexes (type, args, granularity) so you can encode retention + performance plans directly in code. [Docs: TTL](/moose/olap/ttl) • [Docs: Sample BY](/moose/olap/schema-optimization#sample-by-expressions) • [Docs: Secondary indexes](/moose/olap/indexes) | PRs [#2845](https://github.com/514labs/moose/pull/2845), [#2867](https://github.com/514labs/moose/pull/2867), [#2869](https://github.com/514labs/moose/pull/2869).
- **`get_source` MCP tool** – AI assistants can resolve a Moose component (tables, APIs, streams) back to its source file for faster code navigation. [Docs: MCP get_source tool](/moose/mcp-dev-server#get_source) | PRs [#2848](https://github.com/514labs/moose/pull/2848).
- **Google Analytics v4 connector** – Service-account authenticated connector streams GA4 reports and realtime metrics into Moose pipelines so marketing data lands without bespoke ETL. [Docs: Connector reference](https://registry.moosestack.com/connectors/google-analytics) | PRs [registry#121](https://github.com/514-labs/registry/pull/121).
- **Connector registry APIs** – Public REST endpoints expose connector metadata, docs, schemas, and versions for catalogs or automation. [Docs: Registry API docs](https://registry.moosestack.com/docs/api) | PRs [registry#120](https://github.com/514-labs/registry/pull/120).
- **Onboarding & docs polish** – Quickstart, auth, materialized view, and config guides now call out install checkpoints, nullable column behavior, and when to prefer `moose.config.toml` over Docker overrides. [Docs: Quickstart](/moose/getting-started/quickstart) | PRs [#2903](https://github.com/514labs/moose/pull/2903), [#2894](https://github.com/514labs/moose/pull/2894), [#2893](https://github.com/514labs/moose/pull/2893), [#2890](https://github.com/514labs/moose/pull/2890).
- **Integer validation parity** – The ingest API enforces every ClickHouse integer range (Int8–UInt256) with clear errors, preventing silent overflows. [Docs: Ingest API](/moose/apis/ingest-api) | PRs [#2861](https://github.com/514labs/moose/pull/2861).
- **MCP watcher stability** – The MCP server now waits for file-system changes to settle before responding so IDE bots always read consistent artifacts. [Docs: MCP server](/moose/mcp-dev-server) | PRs [#2884](https://github.com/514labs/moose/pull/2884).
- **Release + schema compiler hardening** – Version detection ignores CI-only tags, and ClickHouse parsing handles ORDER BY around `PARTITION BY`, `TTL`, `SAMPLE BY`, and secondary indexes even when optional arguments are omitted. PRs [#2902](https://github.com/514labs/moose/pull/2902), [#2898](https://github.com/514labs/moose/pull/2898), [#2897](https://github.com/514labs/moose/pull/2897), [#2889](https://github.com/514labs/moose/pull/2889).
- **Proxy request fidelity** – Consumption APIs now preserve headers/body metadata end-to-end, keeping auth tokens and content negotiation intact. PRs [#2881](https://github.com/514labs/moose/pull/2881).
## Boreal
- **Navigation slug fix** – Visiting a 404 no longer strips the org ID from subsequent links, so multi-tenant operators stay on the right workspace. ([commercial#1014](https://github.com/514-labs/commercial/pull/1014))
---
## November 1, 2025
Source: release-notes/2025-11-01.mdx
Release notes for November 1, 2025
# November 1, 2025
- **New:** Support for ClickHouse table engines:
- [Buffer table engine](/moose/olap/model-table#in-memory-buffer-buffer): smooth ingest spikes before data lands in MergeTree ([ClickHouse docs](https://clickhouse.com/docs/en/engines/table-engines/special/buffer))
- [S3 table engine](/moose/olap/model-table#direct-s3-access-s3): keep data in S3 while ClickHouse reads and writes it on demand ([ClickHouse docs](https://clickhouse.com/docs/en/engines/table-engines/integrations/s3))
- Beta (self-hosted only): [Distributed table engine](/moose/olap/model-table#distributed-tables-distributed): serve cluster-wide queries through Moose-managed tables ([ClickHouse docs](https://clickhouse.com/docs/en/engines/table-engines/special/distributed))
- **Improved:** Serverless migrations gain Redis-backed state storage plus per-table databases.
- **Feature that will make a small number of people very happy:** Moose now has a flake.nix to let users install the cli via nix run github:514-labs/moosestack (also provides a dev shell to make contributing easier!)
## Buffer tables for burst protection
You can now model ClickHouse Buffer engines in MooseOLAP TypeScript and Python projects.
```ts filename="bufferTable.ts" copy
interface PaymentEvent {
eventId: Key;
amount: number;
capturedAt: Date;
}
);
```
PR: [#2908](https://github.com/514-labs/moosestack/pull/2908) | Docs: [Buffer engine](/moose/olap/model-table#in-memory-buffer-buffer) • [ClickHouse Buffer engine docs](https://clickhouse.com/docs/en/engines/table-engines/special/buffer)
## S3 tables for object storage
You can now model ClickHouse S3 engines in MooseOLAP TypeScript and Python projects. The CLI serializes engine settings, resolves runtime credentials, and enforces the S3 rule set (`PARTITION BY` allowed, `ORDER BY` rejected) so you can read or write datasets that live entirely in S3.
```ts filename="s3Archive.ts" copy
interface ArchivedOrder {
orderId: string;
status: string;
processedAt: Date;
}
);
```
PR: [#2908](https://github.com/514-labs/moosestack/pull/2908) | Docs: [S3 engine](/moose/olap/model-table#direct-s3-access-s3) • [ClickHouse S3 docs](https://clickhouse.com/docs/en/engines/table-engines/integrations/s3)
## Distributed tables for cluster fan-out
Beta (self-hosted only): Not supported on Boreal or ClickHouse Cloud.
You can now model ClickHouse Distributed engines in MooseOLAP TypeScript and Python projects. Plans capture the cluster, target database/table, and optional sharding key, while validation checks that the referenced local tables exist on every node before executing migrations.
```ts filename="distributedEvents.ts" copy
interface UserEvent {
userId: Key;
eventType: string;
occurredAt: Date;
}
);
```
PR: [#2908](https://github.com/514-labs/moosestack/pull/2908) | Docs: [Distributed engine](/moose/olap/model-table#distributed-tables-distributed) • [ClickHouse Distributed docs](https://clickhouse.com/docs/en/engines/table-engines/special/distributed)
## Redis state storage for serverless migrations
`moose generate migration` and `moose migrate` accept a Redis URL (flag or `MOOSE_REDIS_CONFIG__URL`) whenever `state_config.storage = "redis"`. The CLI resolves ClickHouse + Redis endpoints, acquires migration locks in Redis, and reuses the same builder across serverless tooling.
```bash filename="Terminal" copy
export MOOSE_CLICKHOUSE_CONFIG__URL="https://user:pass@ch.serverless.dev/main"
export MOOSE_REDIS_CONFIG__URL="redis://redis.example.com:6379"
moose migrate --clickhouse-url "$MOOSE_CLICKHOUSE_CONFIG__URL" --redis-url "$MOOSE_REDIS_CONFIG__URL"
```
PR: [#2907](https://github.com/514-labs/moosestack/pull/2907) | Docs: [Redis state storage](/moose/migrate#state-storage-options)
## Multi-database tables
Tables now carry a `database` field through the CLI, codegen, and infrastructure map. Moose will create any `additional_databases`, validate plans that attempt to move an existing table, and surface fully qualified names in `moose ls`.
```ts filename="auditLogs.ts" copy
interface AuditLog {
id: Key;
recordedAt: Date;
message: string;
}
);
```
PR: [#2876](https://github.com/514-labs/moosestack/pull/2876) | Docs: [Multi-database setup](/moose/olap/model-table#multi-database-setup)
## Moose
- **Buffer tables** – Burst-friendly Buffer engines ship with typed config, CLI validation, and template coverage. Docs: [Buffer engine](/moose/olap/model-table#in-memory-buffer-buffer) • [ClickHouse Buffer docs](https://clickhouse.com/docs/en/engines/table-engines/special/buffer) | PR [#2908](https://github.com/514-labs/moosestack/pull/2908)
- **S3 tables** – Direct object storage workflows stay in code with S3 engine support and credential handling. Docs: [S3 engine](/moose/olap/model-table#direct-s3-access-s3) • [ClickHouse S3 docs](https://clickhouse.com/docs/en/engines/table-engines/integrations/s3) | PR [#2908](https://github.com/514-labs/moosestack/pull/2908)
- **Distributed tables** – Cluster fan-out models emit the correct ClickHouse DDL and guard against missing local tables. Docs: [Distributed engine](/moose/olap/model-table#distributed-tables-distributed) • [ClickHouse Distributed docs](https://clickhouse.com/docs/en/engines/table-engines/special/distributed) | PR [#2908](https://github.com/514-labs/moosestack/pull/2908)
- **Serverless migrations stay coordinated** – Redis-backed locks and state storage plug into the existing `moose migrate` flow with env-var overrides for CI/CD. Docs: [Redis state storage](/moose/migrate#state-storage-options) | PR [#2907](https://github.com/514-labs/moosestack/pull/2907)
- **Per-table databases** – The migration planner now respects `database` overrides, auto-creates configured databases, and blocks accidental moves between them. Docs: [Multi-database setup](/moose/olap/model-table#multi-database-setup) | PR [#2876](https://github.com/514-labs/moosestack/pull/2876)
- **Runtime S3Queue credentials** – Environment variable markers resolve at deploy time for S3Queue sources, keeping AWS keys out of source. Docs: [Streaming from S3](/moose/olap/model-table#streaming-from-s3-s3queue) | PR [#2875](https://github.com/514-labs/moosestack/pull/2875)
## Boreal
- Blog redesign.
- Fixed the redirect loop after deleting an organization so users land back on the create-org screen instead of bouncing between routes.
## Nix development environment
`flake.nix` now bootstraps a full MooseStack build environment (`nix develop` drops you into a development shell that will have everything you need to build all the components of moosestack). If you use Nix, let us know!
Give it a go if you have nix installed on your machine with:
```bash
nix run github:514-labs/moosestack # -- to pass additional arguments to the cli
```
PR: [#2920](https://github.com/514-labs/moosestack/pull/2920)
---
## Release Notes
Source: release-notes/index.mdx
Moose / Sloan / Boreal Release Notes
# Release Notes
Welcome to the Moose, Sloan, and Boreal release notes. Here you'll find information about new features, improvements, and bug fixes for all our products.
## Latest Updates
* [November 1, 2025](/release-notes/2025-11-01)
* [October 24, 2025](/release-notes/2025-10-24)
## Installation
To get the latest versions of Moose and Sloan:
```bash
bash -i <(curl -fsSL https://fiveonefour.com/install.sh) moose,sloan
```
## Products
Our release notes cover updates across three main products:
**Moose** - Build analytical backends with code-first infrastructure, including OLAP tables, streaming pipelines, and APIs.
**Sloan** - AI-powered MCP tools for automated data engineering and project setup.
**Boreal** - Our hosting platform for deploying and managing Moose applications.
Select a date from the sidebar to view detailed release notes for that period.
---
## release-notes/upcoming
Source: release-notes/upcoming.mdx
### Highlights
* **Moose and Sloan users** can now embed metadata into their Moose primitives, for use by users, their agents, and their metadata tools.
* **Sloan users** can read and write from **Databricks** (more coming here soon).
### Sloan MCP
#### [Experimental] Wrangler Agent—Databricks tools.
We've had a bunch of our users (especially in the enterprise) request deeper integration with Databricks. We've created MCP tooling to allow you to read from Databricks, and create new derivative Databricks managed tables.
Turn it on with `sloan config tools`, and adding `experimental-databricks-tools`. [Docs](https://docs.fiveonefour.com/sloan/tool-reference). To connect with your Databricks instance, you'll need to modify the relevant `MCP.json` file to add:
```JSON
"DATABRICKS_HOST": "[-].databricks.com",
"DATABRICKS_PATH": "/sql/1.0/warehouses/[-]",
"DATABRICKS_TOKEN": "[-]",
```
**This allows you to:**
* Read from Databricks tables
* Create queries and run them against Databricks tables
* Create new Databricks managed tables
**Future of the feature:**
* **Workflows V2**: We're working on bringing schemas into our workflow creation tools, our Databricks integration will be able to leverage these in interacting with Databricks.
* **DatabricksTable**: We're working on a new primitive that will allow you to create a Databricks table from a Moose primitive.
# Moose + Sloan
#### Descriptions in your Moose primitives
**Store context about why you are building what you are building, for you and your agents.**
Moose primitives can now include descriptions.
```ts
const acPipeline = new IngestPipeline(
"AircraftTrackingProcessed",
{
table: true,
stream: true,
ingest: false,
},
{ description: "Pipeline for ingesting raw aircraft data" } // new description field!
);
```
**Where is this used?** Sloan tools that create Moose primitives will now write the intention of the Moose primitive into the description field to give tools/agents that work with that primitive in the future more context. Practically, every description is now served to the tools as context too when the infra map is loaded up as context.
**Future of the feature:** Two main extensions of this feature are planned:
* Embedding the descriptions into the underlying infrastructure (e.g. as table level comments in ClickHouse)
* Extending the metadata:
* To be on a field level as well as a primitive level
* To include any arbitrary key value pair, not just a description (use this for managing labels like PII!)
---
## Data Collection Policy
Source: sloan/data-collection-policy.mdx
Data Collection Policy for Sloan
# Sloan Data Collection Policy
Sloan is in research preview. Accordingly, we collect usage data to improve the product. By default, we collect the most granular data on "ready to use" templates (`comprehensive` data collection), and we collect less information from the other templates (`standard` data collection).
You can change the data you share with us by changing the following:
```ts filename="sloan.config.toml"
enable_telemetry = "standard"
```
The available options are:
- `standard` - collects tool usage and success or error status.
- `comprehensive` - collects tool usage, success or error status, and parameters used by the tools.
- `off` - disables telemetry.
Example standard data collection:
```javascript filename="standard data collection" copy
{
distinctId: distinctIdForEvent,
event: 'sloan_mcp_tool_usage',
properties: {
tool_name: toolName,
success: !error,
telemetry_level: 'standard',
source: 'sloan_mcp_execution',
timestamp: new Date().toISOString()
}
}
```
Example comprehensive data collection:
```javascript filename="comprehensive data collection" copy
{distinctId: distinctIdForEvent,
event: 'sloan_mcp_tool_usage',
properties: {
tool_name: toolName,
success: !error,
telemetry_level: 'comprehensive',
source: 'sloan_mcp_execution',
timestamp: new Date().toISOString(),
parameters: args, // Full tool parameters
error_message: error?.message, // Detailed error information
error_stack: error?.stack, // Error stack trace
machine_id: machineId, // Machine identifier
os: process.platform, // Operating system
arch: process.arch, // Architecture
node_version: process.version // Node.js version
}
}
```
Our privacy policy is available [here](https://www.fiveonefour.com/legal/privacy.pdf).
---
## Sloan Demos
Source: sloan/demos.mdx
Templates and instructions for achieving common tasks with Sloan
# Sloan Demos
This page will provide a series of templates and instructions for achieving common tasks with Sloan.
All demo flows will assume that you have already installed the Sloan CLI and Moose CLI.
```bash filename="Terminal" copy
bash -i <(curl -fsSL https://fiveonefour.com/install.sh) sloan,moose
```
---
## Context Demo -- Aircraft Metrics Definition
Source: sloan/demos/context.mdx
Learn how to use Sloan's context management to build knowledge-driven data applications
# Aircraft Metrics Definition
## Airspeed Metrics
**Ground Speed vs True Airspeed**: Ground speed (`gs`) represents the aircraft's speed relative to the ground, while true airspeed (`tas`) accounts for air density and temperature conditions. True airspeed calculation requires outside air temperature (`oat`) and pressure altitude data not currently available in our model.
**Indicated Airspeed (IAS)**: The airspeed reading from the aircraft's pitot-static system (`ias`), which differs from true airspeed based on altitude and atmospheric conditions. This metric requires direct airspeed sensor data not present in our current ADS-B feed.
## Climb/Descent Performance Metrics
**Vertical Speed**: Calculated using `baro_rate` (barometric rate) and `geom_rate` (geometric rate) to determine climb or descent performance. Positive values indicate climb, negative values indicate descent.
**Climb Efficiency**: Ratio of altitude gained to ground distance covered, calculated using altitude change (`alt_baro` or `alt_geom`) and position changes (`lat`, `lon`).
## Flight Phase Detection Metrics
**Takeoff Phase**: Identified by rapid altitude gain (`alt_baro` increasing) combined with increasing ground speed (`gs`) and high climb rate (`baro_rate` > 500 ft/min).
**Cruise Phase**: Characterized by stable altitude (minimal `baro_rate`), consistent ground speed (`gs`), and straight track (`track` changes < 5°).
**Approach Phase**: Detected by decreasing altitude (`baro_rate` < -300 ft/min), decreasing ground speed, and altitude below typical cruise levels.
**Landing Phase**: Final approach with very low altitude (`alt_baro` < 1000 ft), decreasing speed, and stable track toward runway.
## Signal Quality Metrics
**Signal Strength**: Direct measurement using `rssi` (Received Signal Strength Indicator) to assess reception quality.
**Data Freshness**: Calculated using `seen` (seconds since last message) and `seen_pos` (seconds since last position update) to determine data reliability.
**Message Frequency**: Messages per minute calculated from `messages` count and time window to assess tracking consistency.
## Position Accuracy Metrics
**Navigation Accuracy**: Composite score using `nic` (Navigation Integrity Category), `nac_p` (Navigation Accuracy Category - Position), and `nac_v` (Navigation Accuracy Category - Velocity) to determine positional reliability.
**Surveillance Accuracy**: Assessment using `sil` (Surveillance Integrity Level) and `sda` (System Design Assurance) to evaluate overall tracking quality.
## Flight Efficiency Metrics
**Great Circle Deviation**: Comparison of actual flight path (derived from sequential `lat`, `lon` coordinates) against the shortest great circle distance between origin and destination.
**Altitude Optimization**: Analysis of altitude profile against optimal flight levels for given aircraft type and distance.
**Speed Consistency**: Variance in ground speed (`gs`) throughout different flight phases to assess flight smoothness.
**Fuel Efficiency**: Calculated using fuel flow rate (`fuel_flow`) and ground speed to determine nautical miles per gallon. Requires engine performance data not available in our current dataset.
## Environmental & Weather Metrics
**Wind Speed & Direction**: Calculated by comparing true airspeed (`tas`) with ground speed (`gs`) and track changes. Requires true airspeed data and wind vector information (`wind_speed`, `wind_direction`) not present in our model.
**Turbulence Detection**: Identified through rapid changes in altitude (`alt_baro`) and track (`track`) combined with accelerometer data (`vertical_g_force`, `lateral_g_force`) not available in ADS-B transmissions.
**Weather Avoidance**: Analysis of flight path deviations around weather systems using onboard weather radar data (`weather_radar_returns`) and precipitation intensity (`precip_intensity`) not included in our current data model.
## Traffic Density & Separation Metrics
**Aircraft Density**: Count of aircraft within defined geographical boundaries using `lat`, `lon` coordinates and configurable radius.
**Separation Metrics**: Minimum distances between aircraft calculated using position data and altitude differences.
**Airspace Utilization**: Percentage of available airspace occupied by tracked aircraft at different altitude bands.
## Operational Metrics
**Emergency Detection**: Identification of emergency situations using `emergency` codes, `squawk` codes (7500, 7600, 7700), and `alert` flags.
**Autopilot Usage**: Analysis of autopilot engagement using navigation modes (`nav_modes`) and flight path consistency.
**Communication Quality**: Assessment based on transponder performance, message consistency, and data completeness across all available fields.
---
## Egress Demo
Source: sloan/demos/egress.mdx
Learn how to create data egress APIs using Sloan with a step-by-step TypeScript example
# Data Egress
## Create analytics APIs to serve your data
This demo will walk you through using Sloan tools to prompt your way to creating analytics APIs that serve the aircraft telemetry data you've ingested.
[Skip to prompt](#prompt-the-llm-to-create-a-geolocation-api) if you started with the ads-b template.
- **Sloan and Moose CLIs**: [Install them here](/sloan/getting-started/cursor)
- **OS**: macOS or Linux (WSL supported for Windows)
- **Docker Desktop/Engine**: [24.0.0+](https://docs.docker.com/get-started/get-docker/)
- **Node**: [version 20+](https://nodejs.org/en/download) (LTS recommended)
- **Anthropic API Key**: [Get one here](https://docs.anthropic.com/en/docs/initial-setup)
- **Client**: [Cursor](https://www.cursor.com/)
- **Completed**: [Ingest Demo](/sloan/demos/ingest) (or have data already in your system)
### Start with the ads-b template
```bash filename="Terminal" copy
sloan init egress-demo ads-b
```
```bash filename="Terminal" copy
cd egress-demo
npm install
```
### Run the Moose Dev Server
```bash filename="Terminal" copy
moose dev
```
### Open the project in Cursor
### Initialize the Sloan MCP
Navigate to `Cursor > Settings > Cursor Settings > Tools and Integrations` then toggle on the `Sloan MCP` tool.
### For best results, set the LLM to `claude-4-sonnet`
Gemini 2.5 and o3 are also reasonably good, but claude-4-sonnet has the most consistent results.
### Prompt the LLM to create a geolocation API
> Can you create an API that returns every aircraft within X miles of Y,Z coordinates.
### Action
The LLM should now use sloan tools to:
1. create the analytics API endpoint that calculates distance and filters aircraft
2. test the API to ensure it's working correctly with the existing aircraft data
You'll know it is succeeding if:
1. the LLM successfully tests the API
2. you can curl the API generated
3. you can see the generated openapi spec in `path/to/your/project/.moose/openapi.yaml`
### Continue this demo with:
* [Creating Materialized Views](/sloan/demos/mvs)
---
## Ingest Demo
Source: sloan/demos/ingest.mdx
Learn how to ingest data using Sloan with a step-by-step TypeScript example
# Ingest
## Ingest data periodically from an API
This demo will walk you through using Sloan tools to prompt your way to ingesting a whole bunch of aircraft telemetry data from adsb.lol.
[Skip to prompt](#prompt-the-llm-to-create-an-ingest) if you started with the ads-b template.
- **Sloan and Moose CLIs**: [Install them here](/sloan/getting-started/cursor)
- **OS**: macOS or Linux (WSL supported for Windows)
- **Docker Desktop/Engine**: [24.0.0+](https://docs.docker.com/get-started/get-docker/)
- **Node**: [version 20+](https://nodejs.org/en/download) (LTS recommended)
- **Anthropic API Key**: [Get one here](https://docs.anthropic.com/en/docs/initial-setup)
- **Client**: [Cursor](https://www.cursor.com/)
### Create and initialize a new typescript project
```bash filename="Terminal" copy
sloan init ingest-demo typescript-empty
```
```bash filename="Terminal" copy
cd ingest-demo
npm install
```
### Open the project in Cursor
### Run the Moose Dev Servers
```bash filename="Terminal" copy
moose dev
```
### Initialize the Sloan MCP
Navigate to `Cursor > Settings > Cursor Settings > Tools and Integrations` then toggle on the `Sloan MCP` tool.
### For best results, set the LLM to `claude-4-sonnet`
Gemini 2.5 and o3 are also reasonably good, but claude-4-sonnet has the most consistent results.
### Prompt the LLM to create an ingest
> I want to ingest data from the following aircraft transponder data api every 5 seconds for the purpose of creating a set of visualizations.
>
> API: @https://api.adsb.lol/v2/mil
>
> Docs: @https://api.adsb.lol/docs#/v2/v2_mil_v2_mil_get (note, I think the schema here is inaccurate, check out the data before you trust the docs).
>
> Can you execute on this?
The LLM might do a planning step, in which case, you can ask it to execute on the plan.
> Go for it!
### Action
The LLM should now use sloan tools to:
1. get sample data from the API using a temporary script
2. use that sample to create a schema that's used to create an ingest pipeline (ingest API, Redpanda stream, ClickHouse table)
3. create a Moose managed temporal workflow to periodically ingest the data
You'll know it is succeeding if you see dozens of events per second hit your Moose dev console.
### Continue this demo with:
* [Creating Egress APIs](/sloan/demos/egress)
* [Creating Materialized Views](/sloan/demos/mvs)
---
## Materialized Views Demo
Source: sloan/demos/mvs.mdx
Learn how to create materialized views using Sloan with a step-by-step TypeScript example
# Materialized Views
## Create materialized views to pre-aggregate your data, based on your egress API
This demo will walk you through using Sloan tools to prompt your way to creating materialized views that pre-aggregate the aircraft telemetry data you've ingested.
[Skip to prompt](#prompt-the-llm-to-create-a-materialized-view) if you started with the ads-b template.
- **Sloan and Moose CLIs**: [Install them here](/sloan/getting-started/cursor)
- **OS**: macOS or Linux (WSL supported for Windows)
- **Docker Desktop/Engine**: [24.0.0+](https://docs.docker.com/get-started/get-docker/)
- **Node**: [version 20+](https://nodejs.org/en/download) (LTS recommended)
- **Anthropic API Key**: [Get one here](https://docs.anthropic.com/en/docs/initial-setup)
- **Client**: [Cursor](https://www.cursor.com/)
- **Completed**: [Ingest Demo](/sloan/demos/ingest) (or have data already in your system)
### Start with the ads-b template
```bash filename="Terminal" copy
sloan init mvs-demo ads-b
```
```bash filename="Terminal" copy
cd mvs-demo
npm install
```
### Run the Moose Dev Server
```bash filename="Terminal" copy
moose dev
```
### Open the project in Cursor
### Initialize the Sloan MCP
Navigate to `Cursor > Settings > Cursor Settings > Tools and Integrations` then toggle on the `Sloan MCP` tool.
### For best results, set the LLM to `claude-4-sonnet`
Gemini 2.5 and o3 are also reasonably good, but claude-4-sonnet has the most consistent results.
### Create an egress API
You can skip this step if you've already completed the [Egress Demo](/sloan/demos/egress).
> Can you create an API that returns every aircraft within X miles of Y,Z coordinates.
If you prefer to implement the egress API manually, you can create the following analytics API:
```typescript filename="app/index.ts"
...
export * from './apis/getAircraftWithinRadius';
```
```typescript filename="app/apis/getAircraftWithinRadius.ts"
/**
* Parameters for the getAircraftWithinRadius API
*/
interface AircraftRadiusParams {
/** Latitude of the center point */
center_lat: number;
/** Longitude of the center point */
center_lon: number;
/** Radius in miles from the center point */
radius_miles: number;
/** Maximum number of results to return (optional) */
limit?: number & tags.Type<"int64"> & tags.Minimum<1> & tags.Maximum<1000>;
}
/**
* API to retrieve aircraft within a specified radius from given coordinates
* Uses ClickHouse's geoDistance function to calculate great circle distance
*/
) => {
// Execute the query with proper parameter handling
const result = await client.query.execute(sql`
SELECT
hex,
flight,
aircraft_type,
lat,
lon,
alt_baro,
gs,
track,
timestamp,
round(geoDistance(lon, lat, ${params.center_lon}, ${params.center_lat}) * 0.000621371, 2) as distance_miles
FROM AircraftTrackingProcessed
WHERE geoDistance(lon, lat, ${params.center_lon}, ${params.center_lat}) * 0.000621371 <= ${params.radius_miles}
ORDER BY distance_miles ASC
LIMIT ${params.limit || 100}
`);
return result;
},
{
metadata: {
description: "Returns all aircraft within a specified distance (in miles) from given coordinates"
}
}
);
```
### Prompt the LLM to create a materialized view
> Given the egress API, can you create a materialized view that improves the performance of the query?
### Action
The LLM should now use sloan tools to:
1. analyze the Moose project and the egress API
2. create a Materialized View that pre-aggregates the data
You'll know it is succeeding if:
1. the LLM successfully creates the materialized view
2. the LLM sees that the materialized view was created in the ClickHouse database
### Optional further prompts
> Can you create a new egress API that leverages the materialized view to improve the performance of the query?
> Can you test both APIs to see what the performance difference is?
> I want to see the difference in speed, number of rows read, amount of data read, compute, and other vectors you think are pertinent.
---
## Getting Started
Source: sloan/getting-started.mdx
Getting started guide for Sloan
CTACard,
CTACards,
ZoomImg,
ChipButton,
Columns,
Column,
FeatureCard,
FeatureGrid,
BulletPointsCard,
QnABullets,
CheckmarkBullets,
Icons,
} from "@/components";
## Getting Started
These guides will walk you through setting up Sloan MCP with your client of choice, using the CLI and using the MCP.JSON configuration file.
---
## Getting Started
Source: sloan/getting-started/claude.mdx
Getting started guide for Sloan
CTACard,
CTACards,
ZoomImg,
ChipButton,
Columns,
Column,
FeatureCard,
FeatureGrid,
BulletPointsCard,
QnABullets,
CheckmarkBullets,
Icons,
} from "@/components";
## Claude Desktop
### Install Claude Desktop
[Install the Claude Desktop application here](https://claude.ai/download). Note, the Pro version appears to work much more stably with MCPs.
### Install Moose and Sloan CLI
```bash filename="Terminal" copy
bash -i <(curl -fsSL https://fiveonefour.com/install.sh) sloan,moose
```
### Configure Sloan MCP
Create a new project with Claude Desktop MCP preconfigured:
```bash filename="Terminal" copy
sloan init --mcp claude-desktop
```
For other options, see [Sloan CLI docs](/sloan/getting-started/sloan-cli).
```json filename="~/Library/Application Support/Claude/claude_desktop_config.json" copy
{
"mcpServers": {
"sloan": {
"args": [
"@514labs/sloan-mcp@latest",
"--moose-read-tools",
"path/to/your/moose/project"
],
"command": "npx",
"env": {
"ANTHROPIC_API_KEY": "",
"MOOSE_PATH": "path/to/your/moose/installation",
"NODE_PATH": "path/to/your/node/installation",
"PYTHON_PATH": "path/to/your/python/installation"
}
}
}
}
```
### Adding other toolsets
For more information on available toolsets, see [Sloan MCP toolsets](/sloan/reference/tool-reference). For Claude Desktop, we recommend the following toolsets:
* [Moose Read Tools](../sloan/reference/tool-reference#moose-read-tools): gives you chat access to your Moose project and the data within it (enabled by default)
* [Remote ClickHouse Tools](../sloan/reference/tool-reference#remote-clickhouse) (read only): gives you chat access to your remote ClickHouse data
### Using the MCP
1. Open the Claude Desktop application (note, you often have to reload the application after adding a new MCP)
2. If you are using Moose tools, you will need to run your moose dev server
### Warnings / Peculiarities
* You shouldn't use "write"/generative tools with Claude Desktop.
* Every time you add an MCP or change its configuration, you will need to reload the application.
* If you want to change the Moose Project that the Sloan MCP is referring to, manually edit the MCP.JSON file or run `sloan config focus` and select a new project.
### Common issues / troubleshooting
* The MCP is running, but you aren't able to get your data? Look at the tool call response, it will tell you if your Moose dev server is running. If it is not, run `moose dev` in your Moose project directory.
* The MCP is not running. Check your configuration and then restart the application.
## Reference
---
## Getting Started
Source: sloan/getting-started/cursor.mdx
Getting started guide for Sloan
CTACard,
CTACards,
ZoomImg,
ChipButton,
Columns,
Column,
FeatureCard,
FeatureGrid,
BulletPointsCard,
QnABullets,
CheckmarkBullets,
Icons,
} from "@/components";
## Cursor
### Install
[Install Cursor here](https://www.cursor.com/).
### Install Moose and Sloan CLI
```bash filename="Terminal" copy
bash -i <(curl -fsSL https://fiveonefour.com/install.sh) sloan,moose
```
### Configure Sloan MCP
Create a new project with Cursor MCP preconfigured:
```bash filename="Terminal" copy
sloan init --mcp cursor-project
```
If you want to use this as a global Cursor MCP, use `cursor-global` instead of `cursor-project`.
For other options, see [Sloan CLI docs](/sloan/getting-started/sloan-cli).
```json filename="/path/to/your/project/.cursor/mcp.json" copy
{
"mcpServers": {
"sloan": {
"args": [
"@514labs/sloan-mcp@latest",
"--moose-read-tools",
"path/to/your/moose/project"
],
"command": "npx",
"env": {
"ANTHROPIC_API_KEY": "",
"MOOSE_PATH": "path/to/your/moose/installation",
"NODE_PATH": "path/to/your/node/installation",
"PYTHON_PATH": "path/to/your/python/installation"
}
}
}
}
```
### Adding other toolsets
For more information on available toolsets, see [Sloan MCP toolsets](/sloan/reference/tool-reference). All toolsets are available for Cursor.
### Using the MCP
1. Open Cursor
2. You will see a popup saying that an MCP is detected, and can be enabled. Our experience is that this is not always reliable, and the MCP is more stably launched if you go to `cursor > settings > cursor settings > tools and integrations` and enable the MCP there.
3. If you are using Moose tools, you will need to run your moose dev server with `moose dev`.
### Warnings / Peculiarities
* Every time you add an MCP or change its configuration, you will need to reload the MCP. You can do this by going to `cursor > settings > cursor settings > tools and integrations` and toggling the MCP off and on. If this doesn't work, you can also restart the Cursor application.
* If you have configured the MCP globally, and want to change the Moose Project that the Sloan MCP is referring to, manually edit the MCP.JSON file or run `sloan config focus` and select a new project.
### Common issues / troubleshooting
* The MCP is running, but you aren't able to get your data? Look at the tool call response, it will tell you if your Moose dev server is running. If it is not, run `moose dev` in your Moose project directory.
* The MCP is not running. Check your configuration and then restart the application.
## Reference
---
## Getting Started
Source: sloan/getting-started/other-clients.mdx
Getting started guide for Sloan
CTACard,
CTACards,
ZoomImg,
ChipButton,
Columns,
Column,
FeatureCard,
FeatureGrid,
BulletPointsCard,
QnABullets,
CheckmarkBullets,
Icons,
} from "@/components";
### Other clients
We are working on adding MCP support for other clients. If you are interested in using other clients, please [contact us](mailto:sloan@fiveonefour.com).
To try set up your client with Sloan MCP, use the MCP.JSON file within your client of choice.
### Configure Sloan MCP
**Configuration Object Naming**: Different MCP clients use different naming conventions for the server configuration object:
- **Cursor, Windsurf**: Uses `"mcpServers"`
- **Claude Desktop, VS Code**: Use `"servers"` instead
Make sure to check your specific client's documentation for the correct naming convention.
```json filename="MCP configuration" copy
{
"mcpServers": {
"sloan": {
"args": [
"@514labs/sloan-mcp@latest",
"--moose-read-tools",
"path/to/your/moose/project"
],
"command": "npx",
"env": {
"ANTHROPIC_API_KEY": "",
"MOOSE_PATH": "path/to/your/moose/installation",
"NODE_PATH": "path/to/your/node/installation",
"PYTHON_PATH": "path/to/your/python/installation"
}
}
}
}
```
```json filename="MCP configuration" copy
{
"servers": {
"sloan": {
"args": [
"@514labs/sloan-mcp@latest",
"--moose-read-tools",
"path/to/your/moose/project"
],
"command": "npx",
"env": {
"ANTHROPIC_API_KEY": "",
"MOOSE_PATH": "path/to/your/moose/installation",
"NODE_PATH": "path/to/your/node/installation",
"PYTHON_PATH": "path/to/your/python/installation"
}
}
}
}
```
### Adding other toolsets
For more information on available toolsets, see [Sloan MCP toolsets](/sloan/reference/tool-reference). If the client you are using is a chat client, we recommend the following toolsets:
* [Moose Read Tools](../sloan/reference/tool-reference#moose-read-tools): gives you chat access to your Moose project and the data within it (enabled by default)
* [Remote ClickHouse Tools](../sloan/reference/tool-reference#remote-clickhouse) (read only): gives you chat access to your remote ClickHouse data
If it is an IDE type client, all toolsets are available.
### Using the MCP
1. Open the Claude Desktop application (note, you often have to reload the application after adding a new MCP)
2. If you are using Moose tools, you will need to run your moose dev server
### Common issues / troubleshooting
* The MCP is running, but you aren't able to get your data? Look at the tool call response, it will tell you if your Moose dev server is running. If it is not, run `moose dev` in your Moose project directory.
* The MCP is not running. Check your configuration and then restart the application.
## Reference
---
## Getting Started
Source: sloan/getting-started/vs-code.mdx
Getting started guide for Sloan
CTACard,
CTACards,
ZoomImg,
ChipButton,
Columns,
Column,
FeatureCard,
FeatureGrid,
BulletPointsCard,
QnABullets,
CheckmarkBullets,
Icons,
} from "@/components";
## VS Code
- **VS Code**: [Install VS Code here](https://code.visualstudio.com/).
- **GitHub Copilot in VS Code**: [See VS Code docs](https://code.visualstudio.com/docs/copilot/setup)
### Install VS Code and enable MCPs
[Install VS Code here](https://code.visualstudio.com/).
[Enable MCPs](vscode://settings/chat.mcp.enabled) by toggling on the `chat.mcp.enabled` setting.
### Install Moose and Sloan CLI
```bash filename="Terminal" copy
bash -i <(curl -fsSL https://fiveonefour.com/install.sh) sloan,moose
```
### Configure Sloan MCP
Create a new project with Claude Desktop MCP preconfigured:
```bash filename="Terminal" copy
sloan init --mcp vscode-project
```
If you want to use this as a global VS Code MCP, use `vscode-global` instead of `vscode-project`.
For other options, see [Sloan CLI docs](/sloan/getting-started/sloan-cli).
```json filename="/path/to/your/project/.cursor/settings.json" copy
{
"mcp": {
"input": [],
"servers": {
"sloan": {
"args": [
"@514labs/sloan-mcp@latest",
"--moose-read-tools",
"path/to/your/moose/project"
],
"command": "npx",
"env": {
"ANTHROPIC_API_KEY": "",
"MOOSE_PATH": "path/to/your/moose/installation",
"NODE_PATH": "path/to/your/node/installation",
"PYTHON_PATH": "path/to/your/python/installation"
}
}
}
}
}
```
### Adding other toolsets
For more information on available toolsets, see [Sloan MCP toolsets](/sloan/reference/tool-reference). All toolsets are available for Cursor.
### Using the MCP
[ ] TODO: Add instructions for running the MCP server in VS Code
### Warnings / Peculiarities
**Recommended Configuration Method**: While VS Code has a feature that allows you to use MCPs from other clients (like Claude Desktop), we strongly recommend using either the **Sloan CLI** or the **settings.json file** method shown above instead. These methods provide better reliability and configuration control specifically for VS Code environments.
### Common issues / troubleshooting
* The MCP is running, but you aren't able to get your data? Look at the tool call response, it will tell you if your Moose dev server is running. If it is not, run `moose dev` in your Moose project directory.
* The MCP is not running. Check your configuration and then restart the application.
## Reference
---
## Getting Started
Source: sloan/getting-started/windsurf.mdx
Getting started guide for Sloan
CTACard,
CTACards,
ZoomImg,
ChipButton,
Columns,
Column,
FeatureCard,
FeatureGrid,
BulletPointsCard,
QnABullets,
CheckmarkBullets,
Icons,
} from "@/components";
## Windsurf
### Install Windsurf
[Install Windsurf here](https://windsurf.com/).
### Install Moose and Sloan CLI
```bash filename="Terminal" copy
bash -i <(curl -fsSL https://fiveonefour.com/install.sh) sloan,moose
```
### Configure Sloan MCP
Create a new project with Windsurf MCP preconfigured:
```bash filename="Terminal" copy
sloan init --mcp windsurf-global
```
For other options, see [Sloan CLI docs](/sloan/getting-started/sloan-cli).
```json filename="~/.codeium/windsurf/mcp_config.json" copy
{
"mcpServers": {
"sloan": {
"args": [
"@514labs/sloan-mcp@latest",
"--moose-read-tools",
"path/to/your/moose/project"
],
"command": "npx",
"env": {
"ANTHROPIC_API_KEY": "",
"MOOSE_PATH": "path/to/your/moose/installation",
"NODE_PATH": "path/to/your/node/installation",
"PYTHON_PATH": "path/to/your/python/installation"
}
}
}
}
```
### Adding other toolsets
For more information on available toolsets, see [Sloan MCP toolsets](/sloan/reference/tool-reference). All toolsets are available for Windsurf.
### Using the MCP
1. Open Windsurf
2. Run the MCP by going to `windsurf > settings > windsurf settings > cascade > Model Context Protocol (MCP) Servers` and enable the MCP there.
3. If you are using Moose tools, you will need to run your moose dev server with `moose dev`.
### Warnings / Peculiarities
* Every time you add an MCP or change its configuration, you will need to reload the MCP. You can do this by going to `windsurf > settings > windsurf settings > cascade > Model Context Protocol (MCP) Servers` and toggling the MCP off and on or refreshing the server. If this doesn't work, you can also restart the Windsurf application.
* If you have configured the MCP globally, and want to change the Moose Project that the Sloan MCP is referring to, manually edit the MCP.JSON file or run `sloan config focus` and select a new project.
### Common issues / troubleshooting
* The MCP is running, but you aren't able to get your data? Look at the tool call response, it will tell you if your Moose dev server is running. If it is not, run `moose dev` in your Moose project directory.
* The MCP is not running. Check your configuration and then restart the application.
## Reference
---
## Quickstart
Source: sloan/guides.mdx
Quickstart guide for Sloan
CTACard,
CTACards,
ZoomImg,
ChipButton,
Columns,
Column,
FeatureCard,
FeatureGrid,
BulletPointsCard,
QnABullets,
CheckmarkBullets,
Icons,
} from "@/components";
# Sloan Quickstart Guides
Sloan exposes tools to your LLM client for reading, exploring, and building on ClickHouse data—locally or in production. Get started with one of our quickstart guides below:
---
## sloan/guides/clickhouse-chat
Source: sloan/guides/clickhouse-chat.mdx
## Quickstart: AI Chat with ClickHouse
*Use your LLM client to explore your ClickHouse with Sloan MCP tools.*
This will walk you through using Sloan CLI to connect Sloan MCP tools to your ClickHouse database, allowing you to chat with your data in your client of choice. We'll use the ClickHouse Playground as our example database, but you can use any ClickHouse database.
- **OS**: macOS or Linux (WSL supported for Windows)
- **Node**: [version 20+](https://nodejs.org/en/download) (LTS recommended)
- **Client**: [Cursor](https://www.cursor.com/) or [Claude Desktop](https://claude.ai/download) or [Windsurf](https://windsurf.ai/download). For this particular use-case, we recommend Claude Desktop.
### Install Sloan CLI
```bash filename="Terminal" copy
bash -i <(curl -fsSL https://fiveonefour.com/install.sh) sloan
```
### Configure your Sloan MCP
```bash filename="Terminal" copy
sloan connect clickhouse --connection-string "https://explorer:@play.clickhouse.com:443/?database=default" --mcp cursor-project
```
You need a ClickHouse connection URL. Format looks like this:
```
http://username:password@host:port/?database=database_name
```
Want to test without your own ClickHouse? Use the [ClickHouse Playground](https://clickhouse.com/docs/getting-started/playground) with the connection string above. It has sample datasets (read-only) you can experiment with.
```txt copy
https://explorer:@play.clickhouse.com:443/?database=default
```
1. Log into your [ClickHouse Cloud console](https://clickhouse.cloud/)
2. Go to your service details page
3. Find "Connect" or "Connection Details" section
4. Copy the HTTPS endpoint and your username/password
- Check your ClickHouse config file (usually `/etc/clickhouse-server/config.xml`)
- Look for `` (default: 8123) and `` (default: 8443)
- Check users config in `/etc/clickhouse-server/users.xml` or users.d/ directory
- Default user is often `default` with no password
- Check your docker-compose.yml or docker run command for environment variables
- Look for `CLICKHOUSE_USER`, `CLICKHOUSE_PASSWORD`, `CLICKHOUSE_DB`
- Default is usually `http://default:@localhost:8123/?database=default`
- **Can't connect?** Try `curl http://your-host:8123/ping` to test connectivity
- **Authentication failed?** Verify username/password with `clickhouse-client --user=username --password=password`
- **Database not found?** Run `SHOW DATABASES` to see available databases
- **Permission denied?** Check user permissions with `SHOW GRANTS FOR username`
**Still stuck?** Check the [ClickHouse documentation](https://clickhouse.com/docs/en/getting-started/install) for your specific deployment method.
### Chat
Open your Claude Desktop client. We recommend starting the chat with a context setting question like "tell me about the data I have available to me in ClickHouse".
You can check that the MCP is correctly configured by looking at `claude > settings > developer > sloan`. It should say "running".
You can can also look at `search and tools` beneath the chat window, you should see `sloan` in the list of MCPs—if you click into it, you should see the tools that are enabled.
### What's next?
Try [creating a Moose Project from your ClickHouse database](https://docs.fiveonefour.com/sloan/quickstart/clickhouse-proj). That way, you can use Sloan MCP tools to create new primitives, like ingestion paths, data models, egress APIs, and more!
Or try [deploying your project to Boreal](https://www.fiveonefour.com/boreal), our hosting platform for Moose projects.
## Other Quickstart Guides
---
## sloan/guides/clickhouse-proj
Source: sloan/guides/clickhouse-proj.mdx
## Quickstart: AI analytics engineering from your ClickHouse
*Generate a local OLAP project from your ClickHouse deployment; Sloan MCP pre-configured for analytics engineering.*
This will walk you through creating a new local Moose project reflecting the structure of your ClickHouse database. It will allow you to add data to your local dev environment from your remote ClickHouse database, and use Sloan MCP tools to enrich your project with metadata, or create new Moose primitives that you can use in your project (e.g.egress APIs). We'll use the ClickHouse Playground as our example database, but you can use any ClickHouse database.
- **OS**: macOS or Linux (WSL supported for Windows)
- **Docker Desktop/Engine**: [24.0.0+](https://docs.docker.com/get-started/get-docker/)
- **Node**: [version 20+](https://nodejs.org/en/download) (LTS recommended)
- **Anthropic API Key**: [Get one here](https://docs.anthropic.com/en/docs/initial-setup)
- **Client**: [Cursor](https://www.cursor.com/) or [Claude Desktop](https://claude.ai/download) or [Windsurf](https://windsurf.ai/download). For this particular use-case, we recommend Claude Desktop.
### Install Moose and Sloan CLIs
```bash filename="Terminal" copy
bash -i <(curl -fsSL https://fiveonefour.com/install.sh) moose,sloan
```
We'll be using generative MCP tools here, so make sure you add your Anthropic API key in install. If you installed without adding it, you can add it later with `sloan config keys anthropic `. If you need to create one, see: https://docs.anthropic.com/en/docs/initial-setup.
### Create a new Moose project from your ClickHouse database
```bash filename="Terminal" copy
sloan init my_project_name --from-remote 'https://explorer:@play.clickhouse.com:443/?database=default' --language python --mcp cursor-project
```
Want to test without your own ClickHouse? Use the [ClickHouse Playground](https://clickhouse.com/docs/getting-started/playground) with the connection string above. It has sample datasets (read-only) you can experiment with.
```txt copy
https://explorer:@play.clickhouse.com:443/?database=default
```
1. Log into your [ClickHouse Cloud console](https://clickhouse.cloud/)
2. Go to your service details page
3. Find "Connect" or "Connection Details" section
4. Copy the HTTPS endpoint and your username/password
- Check your ClickHouse config file (usually `/etc/clickhouse-server/config.xml`)
- Look for `` (default: 8123) and `` (default: 8443)
- Check users config in `/etc/clickhouse-server/users.xml` or users.d/ directory
- Default user is often `default` with no password
- Check your docker-compose.yml or docker run command for environment variables
- Look for `CLICKHOUSE_USER`, `CLICKHOUSE_PASSWORD`, `CLICKHOUSE_DB`
- Default is usually `http://default:@localhost:8123/?database=default`
- **Can't connect?** Try `curl http://your-host:8123/ping` to test connectivity
- **Authentication failed?** Verify username/password with `clickhouse-client --user=username --password=password`
- **Database not found?** Run `SHOW DATABASES` to see available databases
- **Permission denied?** Check user permissions with `SHOW GRANTS FOR username`
**Still stuck?** Check the [ClickHouse documentation](https://clickhouse.com/docs/en/getting-started/install) for your specific deployment method.
This will create a new Moose project from your ClickHouse database. [See Moose docs](https://docs.fiveonefour.com/moose) for more information about the project structure, and how it spins up your local development environment (including a local ClickHouse database).
The new project is called "my_project_name" and is created in the current directory. the string after `--from-remote` is the connection string to your ClickHouse database, structured as `clickhouse://:@:/` (note, the ClickHouse Playground has no password).
### Install dependencies and run the dev server
Before you can run Moose's local dev server, Docker Desktop must be running.
Navigate into the project directory:
```bash filename="Terminal" copy
cd my_project_name
```
Install the dependencies:
```bash filename="Terminal" copy
npm i
```
Run the dev server:
```bash filename="Terminal" copy
moose dev
```
### Get sample data
```bash filename="Terminal" copy
moose seed clickhouse --connection-string clickhouse://explorer:@play.clickhouse.com:9440/default --limit 100
```
This will seed your local ClickHouse database with 100 rows of sample data from your remote ClickHouse database—here, the ClickHouse Playground. You can change the number of rows with the `--limit` flag.
This will improve the context provided to Sloan's MCP tools, and make it easier to validate analytic engineering tasks.
### Set up your Client
The `sloan init` command above configured Cursor to use Sloan MCP tools. You can check this by opening Cursor and looking at `cursor > settings > cursor settings > MCP` menu. You should see `sloan` in the list of MCPs, alongside a list of tools.
You may need to enable the MCP. Once you do so, you should see a green 🟢 status indicator next to it.
If you would like to use a different client, you can use the following command from within the project directory:
```bash filename="Terminal" copy
sloan setup --mcp
```
### Enrich project with metadata [coming soon]
Since we have a Moose project with sample data and some metadata, we can use this to create more metadata!
If we ask our client "Can you add a description to each Moose primitive in this project?", the LLM will use the `write_metadata` tool to add a description to each Moose primitive.
```TypeScript filename="my_project_name/index.ts"
const acPipeline = new IngestPipeline(
"AircraftTrackingProcessed",
{
table: true,
stream: true,
ingestApi: false,
metadata: {
description: "Pipeline for ingesting raw aircraft data" } // new description field!
}
);
```
### Chat with your data
You can also now just chat with your client about your data! Try asking "Look at my MTA data in ClickHouse, tell me about the trains that ran in the last 24 hours."
The client will use `read_moose_project`, `read_clickhouse_tables` and maybe `read_production_clickhouse` to answer your question.
### Create new Egress APIs with Sloan MCP tools
If you find a thread that you find interesting enough to want to productionize, try asking the client "can you create an egress API to furnish that data?"
The client will use `create_egress_api` and `test_egress_api` to create an egress API primitives in Moose, that will automatically deploy in your local dev environment when you save.
### What's next?
Try adding new [ingestion scripts, data models, or materialized views to your project using Sloan's experimental tools](https://docs.fiveonefour.com/sloan/reference/tool-reference#experimental-moose-tools)!
## Other Quickstart Guides
---
## sloan/guides/from-template
Source: sloan/guides/from-template.mdx
## Quickstart: AI powered OLAP templates
*Bootstrap a complete OLAP pipeline with a Moose template; with Sloan's AI tools already set up for you.*
This will get you started with a Moose data engineering project ingesting Aircraft Transponder data that you can use to learn about Sloan's Analytics Engineering MCP toolset.
- **OS**: macOS or Linux (WSL supported for Windows)
- **Docker Desktop/Engine**: [24.0.0+](https://docs.docker.com/get-started/get-docker/)
- **Node**: [version 20+](https://nodejs.org/en/download) (LTS recommended)
- **Anthropic API Key**: [Get one here](https://docs.anthropic.com/en/docs/initial-setup)
- **Client**: [Cursor](https://www.cursor.com/) or [Claude Desktop](https://claude.ai/download) or [Windsurf](https://windsurf.ai/download). For this particular use-case, we recommend Claude Desktop.
### Install Sloan and Moose CLIs
```bash filename="Terminal" copy
bash -i <(curl -fsSL https://fiveonefour.com/install.sh) sloan,moose
```
We'll be using generative MCP tools here, so make sure you add your Anthropic API key in install. If you installed without adding it, you can add it later with `sloan config keys anthropic `. If you need to create one, see: https://docs.anthropic.com/en/docs/initial-setup.
### Create a new Moose project from the ADS-B template
```bash filename="Terminal" copy
sloan init ads-b
```
This will create a new Moose project using the ADS-B template to gather ADS-B (aircraft transponder) data that you can use to explore Sloan's MCP offerings. By default, it will create the project configured for use with Cursor (by creating `~/.cursor/mcp.config`), but if you would like to use Claude Desktop, append `--mcp claude-desktop`.
If you want to create an empty project, and build your own Data Models and ingestion, try `sloan init typescript-empty` or `sloan init python-empty`
### Install dependencies and run the dev server
Navigate into the created project directory:
```bash filename="Terminal" copy
cd
```
Install the dependencies:
```bash filename="Terminal" copy
npm i
```
Run the dev server:
```bash filename="Terminal" copy
moose dev
```
### Set up your client: open Cursor and Enable the MCPs
Then open your code editor (e.g. by `cursor .`).
Cursor should prompt you to enable the MCP. If it doesn't, go to `cursor > settings > cursor settings > MCP` and enable the MCP called "sloan". Note, the tools will not all work until the dev server is run locally! Note, you might need to refresh the MCP until its status indicator shows 🟢.
### Start Ingesting Data
Run the command to start ingesting data with the configured ingest scripts: `moose workflow run military_aircraft_tracking`
You should start to see hundreds of live datapoints ingesting instantly!
### Enrich project with metadata [coming soon]
Since we have a Moose project with sample data and some metadata, we can use this to create more metadata!
If we ask our client "Can you add a description to each Moose primitive in this project?", the LLM will use the `write_metadata` tool to add a description to each Moose primitive.
```TypeScript filename="my_project_name/index.ts"
const acPipeline = new IngestPipeline(
"AircraftTrackingProcessed",
{
table: true,
stream: true,
ingestApi: false,
metadata: {
description: "Pipeline for ingesting raw aircraft data" } // new description field!
}
);
```
### Chat with your data
You can also now just chat with your client about your data! Try asking "What aircraft are listed in the data I have available."
The client will use `read_moose_project`, `read_clickhouse_tables` and maybe `read_production_clickhouse` to answer your question.
### Create new Egress APIs with Sloan MCP tools
If you find a thread that you find interesting enough to want to productionize, try asking the client "can you create an egress API to furnish that data?"
The client will use `create_egress_api` and `test_egress_api` to create an egress API primitives in Moose, that will automatically deploy in your local dev environment when you save.
## Other Quickstart Guides
---
## Sloan - Automated Data Engineering
Source: sloan/index.mdx
AI-powered tools and agents exposed through an easy to configure MCP server for data engineering and infrastructure
# Welcome to Sloan
Install [Sloan](/sloan/reference/cli-reference) and [Moose](/moose/reference/cli-reference) CLIs:
```
bash -i <(curl -fsSL https://fiveonefour.com/install.sh) sloan,moose
```
Quick Start →
Start from ClickHouse
See Examples
Tools Reference
## What is Sloan?
Sloan is a set of tools to make your chat, copilot or BI fluent in data engineering. Use the CLI to set up MCPs with the tools you need in the clients you use. Create new data engineering projects with a moose-managed ClickHouse based infrastructure or use these agents with your existing data infrastructure.
## Quickstart Guides
## Core features
Sloan offers a comprehensive suite of MCP tools and agents designed to streamline data engineering workflows, enabling faster and more efficient deployment and management.
3 steps in CLI to chat with ClickHouse
5 minutes to build a full stack OLAP project
Template based new projects for building your own infrastructure
}
variant="sloan"
/>
Data engineering in your IDE
Data analytics and analytics engineering in your chat client
BI in your BI tool of choice
}
variant="sloan"
/>
Opinionated OLAP deployments with Moose: Optimized ClickHouse development and deployment
Direct integration with your architecture: DuckDB, Snowflake, Databricks
Integration with your enterprise: metadata, CICD, logging and more
}
variant="sloan"
/>
Full-stack context: code, logs, data, docs
Self-improving feedback loops
Embedded metadata for continuity
}
variant="sloan"
/>
Each agent: context gathering → implementation → testing → doc workflows
Governance defaults, easily configurable: SDLC, data quality, reporting, privacy and security default practices
Learns your policies with minimal context, enforces them automatically
}
variant="sloan"
/>
## Why Sloan exists
### The DIY Approach
### The Sloan Approach
## What jobs can you do with Sloan?
Ad hoc analytics
Give your LLM client a way to chat with your data, in ClickHouse, Databricks, Snowflake, or wherever it lives
Analytics Engineering
Agents that can build new data products, materialized views and egress methods
Data Engineering
Have agents build and test end to end data pipelines
Data Wrangling
Agents that interact with your data systems like DuckDB, Databricks, Snowflake and more to create scripts, clean data, and prepare data for use
Data Migration
Automated creation of data pipelines to migrate data from legacy systems to a modern data backend
Data quality, governance and reporting
Agents that can help you enforce data quality, governance and reporting during development and in run time
## Getting Involved / Giving Feedback / Community
---
## CLI Reference
Source: sloan/reference/cli-reference.mdx
CLI Reference for Sloan
# CLI Reference
The Sloan CLI is a tool we created to facilitate the easy setup of Sloan MCPs. It is not required, and we hope that LLM clients will eventually support all these features natively, so that we can remove the CLI.
It allows you to:
- set up your Sloan MCP with a variety of clients
- manage multiple projects
- manage multiple toolsets
- update multiple MCP configurations at once
## Install CLI
```bash filename="Terminal" copy
bash -i <(curl -fsSL https://fiveonefour.com/install.sh) sloan
```
## Commands
### init
Creates a data engineering project with Moose, with Sloan MCP preconfigured.
```bash filename="Terminal" copy
sloan init <--mcp > <--location >
```
",
required: true,
description: "Name of your application (this will be the ).",
examples: ["e.g. my-app"]
},
{
name: "--mcp