Historic sql patterns

--- name:historicsqlpatterns description: Identify recurring cross-table historical SQL analytical intents from a bounded pattern shard and emit typed pattern evidence for deterministic wiki projection. callers: [memory_agent ] --- # Historical SQL Patterns Use thisskill when the WorkUnit raw file is apatterns-input/part-0001.json -style shard from thehistoric-sql adapter. Older staged bundles may still provide root-patterns-input.json ; when that is the WorkUnit raw file, read it in the same way. ## Required Workflow 1. Read the WorkUnit notes first. 2. Find the single pattern input file listed under the “rawFiles ” section of the WorkUnit. 3. Call `read_raw_file for that exact raw file path. 4. Identify recurring analytical intents that span at least two tables and show signs of repeated usage. 5. Generate one “pattern ” evidence object per durable cross-table intent by callingemithistoricsql_evidence . 6. Stop once all pattern evidence has been generated. 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 including anyschema.table orschema.table.column in a wiki body, SL source,tables: frontmatter,sl_refs , oremitunmappedfallback : 2.entity_details({connectionId, targets: [{display: "<identifier>"}]}) - confirm that 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 inentity_detailssampleValues for the relevant column. IfsampleValues is short or the sample may have omitted actual values, run asql_execution probe using 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 the following: - Usesql_execution({connectionId, sql: "SELECT 1 FROM <ref> LIMIT 0"}) . If an error occurs, the identifier is fictitious. - Enclose the identifier in[unverified - from <rawPath>] in the wiki body, citing the exact raw path where it was mentioned. - When recordingemitunmappedfallback withnophysicaltable , include the failing probe error inclarification . 5. Never copy<schema>.<table> placeholder strings from these instructions into the output. ## Evidence Shape Each call toemithistoricsql_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 callwikiwrite . - Do not callslwritesource . - Do not callsleditsource . - Do not callcontextcandidate_write . - Do not create single-table pattern pages. - Do not copy credentials, tokens, user emails, or unredacted literals int