ktx-ai-data-context-layer

ktx AI Data Context Layer

Skill by ara.so — AI Agent Skills collection.

ktx is a self-improving context layer that teaches AI agents how to query your data warehouse accurately. It ingests metadata from databases, dbt, Looker, Metabase, and wikis to build a semantic layer with approved metric definitions, joinable columns, and business knowledge. Agents access this through MCP (Model Context Protocol) tools and CLI commands.

What ktx Does

• Learns from company knowledge — ingests wiki content, removes duplicates, flags contradictions
• Maps the data stack — samples tables, detects joinable columns, captures metadata and usage patterns
• Builds a semantic layer — combines raw tables and high-level metrics with automatic join resolution
• Serves agents at execution — exposes CLI and MCP tools with semantic search across wiki and semantic-layer entities

Works with PostgreSQL, Snowflake, BigQuery, ClickHouse, MySQL, SQL Server, and SQLite. Integrates with dbt, MetricFlow, LookML, Looker, Metabase, and Notion.

Installation

Global CLI Installation

npm install -g @kaelio/ktx

Project-Specific Installation

cd /path/to/your/project
npm install @kaelio/ktx

Verify Installation

ktx --version
ktx --help

Quick Setup

# Create or resume a ktx project in current directory
ktx setup

# Check project readiness
ktx status

The ktx setup command will:

1. Create or resume a local ktx project
2. Configure LLM and embedding providers
3. Set up database connections
4. Configure context sources (dbt, Looker, etc.)
5. Build initial context
6. Install agent integration (MCP)

Project Structure

my-project/
├── ktx.yaml                         # Project configuration
├── semantic-layer/<connection-id>/  # YAML semantic sources
├── wiki/global/                     # Shared business context
├── wiki/user/<user-id>/             # User-scoped notes
├── raw-sources/<connection-id>/     # Ingest artifacts and reports
└── .ktx/                            # Local state and secrets (git-ignored)

Important: Commit ktx.yaml, semantic-layer/, and wiki/. Keep .ktx/ local.

Configuration

ktx.yaml Structure

version: 1
name: my-analytics-project

llm:
  provider: anthropic
  model: claude-sonnet-4-6

embeddings:
  provider: openai
  model: text-embedding-3-small

databases:
  warehouse:
    type: postgres
    host: localhost
    port: 5432
    database: analytics
    # Credentials stored in .ktx/secrets.yaml

context-sources:
  dbt_main:
    type: dbt
    path: ./dbt-project
    target: prod
  
  looker_main:
    type: looker
    base_url: https://company.looker.com
    # API credentials in .ktx/secrets.yaml

agent-integration:
  type: mcp
  clients:
    - codex:project
    - claude-code

LLM Configuration

ktx supports multiple LLM providers:

# During setup, choose provider
ktx setup

# Supported providers:
# - anthropic (requires ANTHROPIC_API_KEY)
# - vertex-ai (requires Google Cloud credentials)
# - ai-gateway (custom endpoint)
# - claude-agent-sdk (local Claude Code session)

Set API keys via environment variables:

export ANTHROPIC_API_KEY=your-key-here
export OPENAI_API_KEY=your-key-here

Database Connection

Example PostgreSQL connection in ktx.yaml:

databases:
  warehouse:
    type: postgres
    host: localhost
    port: 5432
    database: analytics
    schema: public
    ssl: true

Credentials in .ktx/secrets.yaml (auto-generated, git-ignored):

databases:
  warehouse:
    user: readonly_user
    password: ${DB_PASSWORD}

Context Sources

dbt Integration

context-sources:
  dbt_main:
    type: dbt
    path: ./dbt-project
    target: prod
    manifest_path: ./dbt-project/target/manifest.json

Looker Integration

context-sources:
  looker_main:
    type: looker
    base_url: https://company.looker.com
    # client_id and client_secret in .ktx/secrets.yaml

Notion Integration

context-sources:
  company_wiki:
    type: notion
    # token in .ktx/secrets.yaml
    database_ids:
      - abc123def456

Key Commands

Setup and Status

# Create/resume project
ktx setup

# Check project status
ktx status

# Example output:
# ktx project: /home/user/analytics
# Project ready: yes
# LLM ready: yes (claude-sonnet-4-6)
# Embeddings ready: yes (text-embedding-3-small)
# Databases configured: yes (warehouse)
# Context sources configured: yes (dbt_main)
# ktx context built: yes
# Agent integration ready: yes (codex:project)

Building Context

# Ingest all configured sources
ktx ingest

# Ingest specific connection
ktx ingest --connection warehouse

# Ingest specific context source
ktx ingest --source dbt_main

# Force re-ingestion (skip cache)
ktx ingest --force

Searching Context

# Search semantic layer (metrics, dimensions, entities)
ktx sl "revenue"
ktx sl "monthly active users"

# Search wiki pages
ktx wiki "refund policy"
ktx wiki "data retention"

# Combined search (semantic layer + wiki)
ktx search "customer churn"

MCP Server

# Start MCP server for agent clients
ktx mcp start

# Start with specific project directory
ktx mcp start --project-dir /path/to/project

# Check MCP server status
ktx mcp status

# Stop MCP server
ktx mcp stop

Semantic Layer Management

# List semantic sources
ktx sl list

# Validate semantic layer definitions
ktx sl validate

# Show semantic source details
ktx sl show users
ktx sl show revenue_metrics

Project Management

# Initialize new project (alternative to setup)
ktx init

# Update project configuration
ktx config set llm.provider anthropic
ktx config set llm.model claude-sonnet-4-6

# Show current configuration
ktx config show

# Clean cached context
ktx clean --cache

# Clean everything (reset project)
ktx clean --all

Real Usage Examples

Example 1: Set Up ktx for PostgreSQL Warehouse

# Install globally
npm install -g @kaelio/ktx

# Navigate to project
cd ~/my-analytics-project

# Set LLM API key
export ANTHROPIC_API_KEY=sk-ant-...

# Run setup (interactive)
ktx setup
# Choose: anthropic, claude-sonnet-4-6
# Choose: openai, text-embedding-3-small
# Add database: postgres
# Host: localhost
# Port: 5432
# Database: analytics
# User: readonly_user
# Password: [enter securely]

# Verify setup
ktx status

Example 2: Ingest dbt and Build Context

# Configure dbt source in ktx.yaml
cat >> ktx.yaml <<EOF
context-sources:
  dbt_main:
    type: dbt
    path: ./dbt-project
    target: prod
EOF

# Run ingestion
ktx ingest

# Search for dbt models
ktx sl "dim_customers"
ktx sl "fct_orders"

Example 3: Query Semantic Layer from TypeScript

import { execSync } from 'child_process';

// Search semantic layer
function searchSemanticLayer(query: string): string {
  const result = execSync(`ktx sl "${query}"`, { encoding: 'utf-8' });
  return result;
}

// Example usage
const revenueMetrics = searchSemanticLayer('revenue');
console.log(revenueMetrics);

// Search wiki
function searchWiki(query: string): string {
  const result = execSync(`ktx wiki "${query}"`, { encoding: 'utf-8' });
  return result;
}

const refundPolicy = searchWiki('refund policy');
console.log(refundPolicy);

Example 4: Set Up MCP for Claude Code

# Navigate to project
cd ~/my-analytics-project

# Run setup with MCP integration
ktx setup
# During setup, select: "Install MCP integration? yes"
# Select agent: "Claude Code (codex)"

# Start MCP server
ktx mcp start --project-dir ~/my-analytics-project

# Keep terminal open, open Claude Code in another window
# Claude Code will auto-discover the MCP server

Example 5: Create Semantic Layer Definition

Create semantic-layer/warehouse/customers.yaml:

kind: SemanticSource
name: customers
type: entity

description: Customer dimension with lifetime metrics

columns:
  - name: customer_id
    type: dimension
    data_type: integer
    primary_key: true
    
  - name: email
    type: dimension
    data_type: string
    
  - name: created_at
    type: dimension
    data_type: timestamp
    
  - name: lifetime_value
    type: metric
    data_type: numeric
    sql: SUM(order_total)
    aggregation: sum
    
  - name: total_orders
    type: metric
    data_type: integer
    sql: COUNT(DISTINCT order_id)
    aggregation: count

sql: |
  SELECT 
    c.id as customer_id,
    c.email,
    c.created_at,
    COALESCE(SUM(o.total), 0) as lifetime_value,
    COUNT(o.id) as total_orders
  FROM customers c
  LEFT JOIN orders o ON c.id = o.customer_id
  GROUP BY c.id, c.email, c.created_at

joins:
  - to: orders
    type: one_to_many
    on: customers.customer_id = orders.customer_id

Validate and ingest:

ktx sl validate
ktx ingest --force
ktx sl show customers

Example 6: Add Wiki Context

Create wiki/global/data-definitions.md:

# Data Definitions

## Revenue Recognition

Revenue is recognized at the point of sale, not when payment is received.

**Key Metrics:**
- `gross_revenue`: Total sales before discounts
- `net_revenue`: Sales minus refunds and discounts
- `arr`: Annual Recurring Revenue (subscription only)

## Customer Classification

- **Active**: Made a purchase in last 90 days
- **Churned**: No purchase in 90+ days
- **New**: First purchase within last 30 days

## Refund Policy

Refunds are processed within 7 days and deducted from revenue in the same period.

Ingest wiki:

ktx ingest
ktx wiki "revenue recognition"

Common Patterns

Pattern 1: Daily Context Refresh

#!/bin/bash
# refresh-context.sh

cd /path/to/ktx/project
ktx ingest --force
ktx sl validate

Run via cron:

# Run daily at 2am
0 2 * * * /path/to/refresh-context.sh

Pattern 2: Multi-Environment Setup

# ktx.yaml
databases:
  warehouse_prod:
    type: postgres
    host: prod.example.com
    database: analytics
    
  warehouse_staging:
    type: postgres
    host: staging.example.com
    database: analytics

# Ingest only production
ktx ingest --connection warehouse_prod

# Ingest only staging
ktx ingest --connection warehouse_staging

Pattern 3: CI/CD Validation

# .github/workflows/validate-ktx.yml
name: Validate ktx Semantic Layer

on: [push, pull_request]

jobs:
  validate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      
      - name: Install ktx
        run: npm install -g @kaelio/ktx
        
      - name: Validate semantic layer
        run: ktx sl validate

Pattern 4: Environment-Specific Secrets

# .env.production
ANTHROPIC_API_KEY=sk-ant-prod-...
DB_PASSWORD=prod_password

# .env.staging
ANTHROPIC_API_KEY=sk-ant-staging-...
DB_PASSWORD=staging_password

Load before running:

source .env.production
ktx ingest

Troubleshooting

Issue: ktx command not found

Solution: Ensure global installation or use npx:

npm install -g @kaelio/ktx
# or
npx @kaelio/ktx setup

Issue: MCP server not connecting

Solution: Check server is running and project directory is correct:

ktx mcp status

# If not running:
ktx mcp start --project-dir /absolute/path/to/project

# Verify in Claude Code settings:
# MCP servers should show ktx with status "Connected"

Issue: Database connection fails

Solution: Verify credentials and network access:

# Test connection manually
psql -h localhost -p 5432 -U readonly_user -d analytics

# Check ktx configuration
ktx config show

# Re-run setup to update credentials
ktx setup

Issue: Ingestion fails with "LLM error"

Solution: Verify API key and quota:

# Check API key is set
echo $ANTHROPIC_API_KEY

# Verify in ktx config
ktx config show

# Try with different model
ktx config set llm.model claude-sonnet-3-5-20240620
ktx ingest

Issue: Semantic layer validation errors

Solution: Check YAML syntax and required fields:

# Run validation with verbose output
ktx sl validate --verbose

# Common issues:
# - Missing 'kind' field
# - Invalid column types (must be: dimension, metric, or attribute)
# - Invalid join syntax
# - Missing SQL or columns definition

Issue: Wiki search returns no results

Solution: Verify wiki ingestion completed:

# Re-run ingestion
ktx ingest --force

# Check wiki files exist
ls -la wiki/global/

# Verify embeddings configured
ktx config show | grep embeddings

Issue: Slow ingestion performance

Solution: Use incremental ingestion and tune sampling:

# Skip force refresh (use cache)
ktx ingest

# Reduce table sampling (in ktx.yaml)
databases:
  warehouse:
    sampling:
      max_rows: 1000  # default is 10000

Issue: Agent can't find ktx MCP tools

Solution: Ensure MCP server is running and configured:

# Start MCP server in background
ktx mcp start --project-dir $(pwd) &

# In Claude Code, verify MCP connection:
# Settings > MCP Servers > ktx should show "Connected"

# If using Cursor/other, check their MCP configuration

Advanced Usage

Custom LLM Provider (AI Gateway)

llm:
  provider: ai-gateway
  endpoint: https://gateway.example.com/v1/chat/completions
  model: custom-model
  api_key_env: CUSTOM_API_KEY

Multiple Semantic Layer Sources

context-sources:
  dbt_core:
    type: dbt
    path: ./dbt-core
    
  dbt_marketing:
    type: dbt
    path: ./dbt-marketing
    
  metabase:
    type: metabase
    url: https://metabase.example.com

Programmatic Access (Node.js)

import { spawn } from 'child_process';

async function queryKtx(query: string): Promise<string> {
  return new Promise((resolve, reject) => {
    const proc = spawn('ktx', ['search', query]);
    let output = '';
    
    proc.stdout.on('data', (data) => {
      output += data.toString();
    });
    
    proc.on('close', (code) => {
      if (code === 0) {
        resolve(output);
      } else {
        reject(new Error(`ktx exited with code ${code}`));
      }
    });
  });
}

// Usage
const results = await queryKtx('customer metrics');
console.log(results);

Project Resolution

ktx resolves the project directory in this order:

1. --project-dir flag
2. KTX_PROJECT_DIR environment variable
3. Nearest ktx.yaml (walk up from current directory)
4. Current directory

# Explicit project directory
ktx status --project-dir /path/to/project

# Using environment variable
export KTX_PROJECT_DIR=/path/to/project
ktx status

# Auto-discover from current directory
cd /path/to/project
ktx status

Resources

• Documentation
• CLI Reference
• Agent Quickstart
• GitHub Repository
• Slack Community