KWDB Schema Design

ALWAYS invoke this skill via the Skill tool before designing any KWDB schema.

This applies even when:

• Reference file contents appear in conversation context (from previous reads)
• Session was restored from a compacted conversation
• You believe you already know the KWDB syntax

Reading reference files directly ≠ Skill invocation. The Skill tool triggers the complete workflow (classify → gather requirements → design → DDL → validate) and ensures guardrails are followed. Skip this step = skip the workflow.

Tiered Reference Architecture

Tier 1 (Always Read) - Core rules, scope, and examples:

• references/key-rules.md - Decision tree and core rules
• references/disambiguation.md - Clarifying questions
• references/_scope.md - Skill boundaries (IN/OUT of scope)
• references/_examples.md - Complete dialogue examples
• references/_contributing.md - How to add new references
• references/_sections.md - Tier definitions and categories

Tier 2 (High-Frequency DDL) - Read when designing tables/indexes/constraints:

• references/table-ddl-ref.md - CREATE/ALTER/DROP TABLE
• references/index-ddl-ref.md - CREATE/DROP INDEX
• references/constraint-ref.md - CHECK, UNIQUE, FOREIGN KEY

Tier 3 (Medium-Frequency DDL) - Read when needed:

• references/view-ref.md - Views and Materialized Views
• references/sequence-ref.md - Sequences and auto-increment
• references/partitioning-ref.md - LIST, RANGE, HASH partitioning
• references/retention-ref.md - Time-series retention policies

Tier 4 (Low-Frequency DDL) - Only when explicitly requested:

• references/trigger-ref.md - Triggers
• references/procedure-ref.md - Stored procedures and functions
• references/database-ref.md - Database and schema operations
• references/privilege-ref.md - User, role, and permission management

When to Activate

Should trigger (explicit KWDB):

• "design a KWDB schema"
• "write KWDB DDL"
• "create a table/index/view in KWDB"
• "KWDB schema for ..."
• "KWDB CREATE TABLE"

Should trigger (implicit - schema keywords):

• "create table" / "design table" / "table structure"
• "add column" / "modify column" / "alter table"
• "create index" / "add index" / "drop index"
• "primary key" / "foreign key" / "constraints"
• "schema design" / "database schema"

Should trigger (time-series keywords):

• "sensor data" / "IoT" / "metrics" / "readings"
• "time-series" / "time series" / "timestamp data"
• "TAGS" / "PRIMARY TAGS" / "RETENTIONS"
• "device data" / "monitoring data" / "logs"

Should trigger (relational keywords):

• "users/orders/products" + "schema/table"
• "entity" + "table"
• "foreign key relationship"

Should trigger (financial/trading keywords):

• "高频交易" / "HFT" / "行情" / "tick data"
• "订单簿" / "order book" / "K线" / "kline"
• "VWAP" / "成交量" / "价差" / "深度"
• "stock" / "futures" / "期权" / "证券"

Should NOT trigger:

• "SELECT ..." / "INSERT ..." / "UPDATE ..." (DML queries)
• "how to query" / "optimize query" (query optimization)
• Deployment, troubleshooting, migration questions
• Backup/restore operations
• "explain analyze" / "slow query" (performance tuning)
• User/permission management (unless explicitly requested)

Supported DDL Operations

Workflow

Step 1: Classify Workload Type

Request → RELATIONAL / TIME-SERIES / MIXED

Ask if unclear. Never skip this step.

Step 2: Gather Requirements

Use disambiguation.md to ask:

• What data fields are needed
• Primary key strategy
• Query patterns (filter, group, join)
• For time-series: retention needs

Step 3: Design Schema

Apply rules from key-rules.md and relevant tier-2/3 references:

• Choose correct column types
• Design primary keys
• Add indexes only when needed
• For time-series: design tags and retention

Step 4: Generate DDL

Output minimal, executable DDL:

• Use correct KWDB syntax
• Include NOT NULL where appropriate
• Add comments for clarity

Step 5: Validate

Include validation step:

• SHOW CREATE TABLE to verify syntax
• SHOW COLUMNS to verify structure
• SHOW INDEX to verify indexes

Output Format

## Intent
[Brief description of what the schema does]

## Workload Type
[relational / time-series / mixed]

## Assumptions
[Any assumptions made - retention, primary key strategy, etc.]

## Design
[Table structure with column types and rationale]

## DDL
```sql
-- minimal executable DDL

Validation

[How to verify the DDL works]

## Guardrails

1. **Never output DDL before classifying workload type**
2. **Never assume retention without stating it** (time-series)
3. **Never create speculative indexes** (label as optional)
4. **Prefer minimal schema** over comprehensive
5. **State all assumptions** when information is incomplete
6. **Validate DDL** before finishing
7. **FK columns must be indexed**
8. **Use appropriate types** (DECIMAL for money, not FLOAT)

## Error Handling

- If requirements unclear: ask clarifying questions first
- If DDL might be wrong: suggest validation steps
- If feature not supported: explain KWDB limitation
- If ambiguous request: classify workload type first