{"id":"historic-sql-patterns","name":"historic_sql_patterns","summary":"境界パターンシャードから繰り返されるクロステーブルヒストリックSQL分析的意図を特定し、決定論的ウィキ投影のための型付きパターン証拠を出力します。","body":"# Historic SQL Patterns\n\nUse 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.\n\n## Required Workflow\n\n1. Read the WorkUnit notes first.\n2. Find the single pattern input file listed under the WorkUnit `rawFiles` section.\n3. Call `read_raw_file` for that exact raw file path.\n4. Identify recurring analytical intents that span at least two tables and have repeated usage signal.\n5. Emit one `pattern` evidence object per durable cross-table intent by calling `emit_historic_sql_evidence`.\n6. Stop after all pattern evidence has been emitted.\n\nEvery join column mentioned in pattern descriptions must be verified via\nentity_details for both sides of the join.\n\n## Identifier Verification Protocol\n\nBefore writing a wiki page or SL source on any topic:\n\n1. `discover_data({query: \"<topic>\"})` - see what wikis, SL sources, and raw\n   tables already exist. Prefer updating existing pages over creating new ones.\n\nBefore emitting any `schema.table` or `schema.table.column` into a wiki body,\nSL source, `tables:` frontmatter, `sl_refs`, or `emit_unmapped_fallback`:\n\n2. `entity_details({connectionId, targets: [{display: \"<identifier>\"}]})` -\n   confirm the identifier resolves; inspect native types, FK/PK, and\n   sampleValues.\n3. For literal values from the source, such as status codes or plan tiers,\n   check whether they appear in `entity_details` sampleValues for the relevant\n   column. If sampleValues is short or the sample may have missed real values,\n   run a `sql_execution` probe with the same warehouse connection id:\n   `sql_execution({connectionId, sql: \"SELECT DISTINCT <col> FROM <ref> LIMIT 50\"})`.\n4. If the candidate identifier still does not resolve, do one of:\n   - Use `sql_execution({connectionId, sql: \"SELECT 1 FROM <ref> LIMIT 0\"})`.\n     If it errors, the identifier is fictional.\n   - Wrap the identifier in `[unverified - from <rawPath>]` in the wiki body,\n     citing the exact raw path that mentioned it.\n   - When recording `emit_unmapped_fallback` with `no_physical_table`, include\n     the failing probe error in `clarification`.\n5. Never copy `<schema>.<table>` placeholder strings from these instructions\n   into output.\n\n## Evidence Shape\n\nEach call to `emit_historic_sql_evidence` must use this shape:\n\n```json\n{\n  \"kind\": \"pattern\",\n  \"pattern\": {\n    \"slug\": \"order-lifecycle-analysis\",\n    \"title\": \"Order Lifecycle Analysis\",\n    \"narrative\": \"Analysts compare order statuses with customer segments to understand lifecycle movement.\",\n    \"definitionSql\": \"select o.status, count(*) from public.orders o join public.customers c on c.id = o.customer_id group by o.status\",\n    \"tablesInvolved\": [\"public.orders\", \"public.customers\"],\n    \"slRefs\": [\"orders\", \"customers\"],\n    \"constituentTemplateIds\": [\"pg:1\", \"pg:2\"]\n  }\n}\n```\n\nThe `pattern` object must match `patternOutputSchema`; multiple calls together must form `patternsArraySchema`.\n\n## Pattern Selection Rules\n\n- Prefer patterns that involve two or more tables.\n- Prefer templates with `executionsBucket` at least `10-100` and `distinctUsersBucket` above solo usage.\n- Merge templates into one pattern only when the business intent is the same.\n- Use a stable kebab-case slug based on intent, not a template id.\n- Set `definitionSql` to the clearest representative SQL from a constituent template.\n- Set `slRefs` to source names when the source name is obvious from table names; omit uncertain refs rather than guessing.\n- Treat each pattern shard independently; do not read peer shard files from `peerFileIndex`.\n\n## Boundaries\n\n- Do not call wiki_write.\n- Do not call sl_write_source.\n- Do not call sl_edit_source.\n- Do not call context_candidate_write.\n- Do not create single-table pattern pages.\n- Do not copy credentials, tokens, user emails, or unredacted literals into evidence.","author":"@Kaelio","ownerProfile":null,"authorContacts":null,"sourceUrl":"https://github.com/Kaelio/ktx/tree/main/packages/cli/src/skills/historic_sql_patterns","license":"Apache-2.0","category":"writing","lang":"en","tokens":923,"stars":0,"calls30d":1,"claimed":false,"visibility":"public","origin":"crawler","version":"0.1.0","createdAt":"2026-08-22","updatedAt":"2026-08-22","files":[],"requires":{"mcp":[],"tools":[]},"safety":{"flags":[],"scannedAt":"2026-08-22","hasScripts":false,"networkEndpoints":[]}}