---
name: Historic SQL Patterns
slug: historic-sql-patterns
category: Data
description: Historic SQL Patterns identifies recurring cross-table analytical intents from historic SQL usage shards and emits typed pattern evidence for deterministic wiki projection. Use it when processing a patterns-input shard from the historic-sql adapter.
github: "https://github.com/Kaelio/ktx/tree/main/packages/cli/src/skills/historic_sql_patterns"
language: TypeScript
stars: 1547
forks: 99
install: "npx degit https://github.com/Kaelio/ktx/tree/main/packages/cli/src/skills/historic_sql_patterns ~/.claude/skills/historic_sql_patterns"
installs_to: ~/.claude/skills/historic_sql_patterns
source_path: packages/cli/src/skills/historic_sql_patterns/SKILL.md
collection_size: 17
category_size: 668
collection_url: "https://dirskills.com/collections/Kaelio/ktx"
added: 2026-08-19T07:26:30.882Z
last_synced: 2026-08-19T07:26:30.882Z
canonical_url: "https://dirskills.com/skills/historic-sql-patterns"
---

# Historic SQL Patterns

Historic SQL Patterns identifies recurring cross-table analytical intents from historic SQL usage shards and emits typed pattern evidence for deterministic wiki projection. Use it when processing a patterns-input shard from the historic-sql adapter.

**Install:**

```bash
npx degit https://github.com/Kaelio/ktx/tree/main/packages/cli/src/skills/historic_sql_patterns ~/.claude/skills/historic_sql_patterns
```

## README

# Historic SQL Patterns

Use this skill when the WorkUnit raw file is a `patterns-input/part-0001.json` style shard from the `historic-sql` adapter. Older staged bundles may still provide root `patterns-input.json`; when that is the WorkUnit raw file, read it the same way.

## Required Workflow

1. Read the WorkUnit notes first.
2. Find the single pattern input file listed under the WorkUnit `rawFiles` section.
3. Call `read_raw_file` for that exact raw file path.
4. Identify recurring analytical intents that span at least two tables and have repeated usage signal.
5. Emit one `pattern` evidence object per durable cross-table intent by calling `emit_historic_sql_evidence`.
6. Stop after all pattern evidence has been emitted.

Every join column mentioned in pattern descriptions must be verified via
entity_details for both sides of the join.

## Identifier Verification Protocol

Before writing a wiki page or SL source on any topic:

1. `discover_data({query: "<topic>"})` - see what wikis, SL sources, and raw
   tables already exist. Prefer updating existing pages over creating new ones.

Before emitting any `schema.table` or `schema.table.column` into a wiki body,
SL source, `tables:` frontmatter, `sl_refs`, or `emit_unmapped_fallback`:

2. `entity_details({connectionId, targets: [{display: "<identifier>"}]})` -
   confirm the identifier resolves; inspect native types, FK/PK, and
   sampleValues.
3. For literal values from the source, such as status codes or plan tiers,
   check whether they appear in `entity_details` sampleValues for the relevant
   column. If sampleValues is short or the sample may have missed real values,
   run a `sql_execution` probe with the same warehouse connection id:
   `sql_execution({connectionId, sql: "SELECT DISTINCT <col> FROM <ref> LIMIT 50"})`.
4. If the candidate identifier still does not resolve, do one of:
   - Use `sql_execution({connectionId, sql: "SELECT 1 FROM <ref> LIMIT 0"})`.
     If it errors, the identifier is fictional.
   - Wrap the identifier in `[unverified - from <rawPath>]` in the wiki body,
     citing the exact raw path that mentioned it.
   - When recording `emit_unmapped_fallback` with `no_physical_table`, include
     the failing probe error in `clarification`.
5. Never copy `<schema>.<table>` placeholder strings from these instructions
   into output.

## Evidence Shape

Each call to `emit_historic_sql_evidence` must use this shape:

```json
{
  "kind": "pattern",
  "pattern": {
    "slug": "order-lifecycle-analysis",
    "title": "Order Lifecycle Analysis",
    "narrative": "Analysts compare order statuses with customer segments to understand lifecycle movement.",
    "definitionSql": "select o.status, count(*) from public.orders o join public.customers c on c.id = o.customer_id group by o.status",
    "tablesInvolved": ["public.orders", "public.customers"],
    "slRefs": ["orders", "customers"],
    "constituentTemplateIds": ["pg:1", "pg:2"]
  }
}
```

The `pattern` object must match `patternOutputSchema`; multiple calls together must form `patternsArraySchema`.

## Pattern Selection Rules

- Prefer patterns that involve two or more tables.
- Prefer templates with `executionsBucket` at least `10-100` and `distinctUsersBucket` above solo usage.
- Merge templates into one pattern only when the business intent is the same.
- Use a stable kebab-case slug based on intent, not a template id.
- Set `definitionSql` to the clearest representative SQL from a constituent template.
- Set `slRefs` to source names when the source name is obvious from table names; omit uncertain refs rather than guessing.
- Treat each pattern shard independently; do not read peer shard files from `peerFileIndex`.

## Boundaries

- Do not call wiki_write.
- Do not call sl_write_source.
- Do not call sl_edit_source.
- Do not call context_candidate_write.
- Do not create single-table pattern pages.
- Do not copy credentials, tokens, user emails, or unredacted literals into evidence.