mirror of
https://github.com/Kaelio/ktx.git
synced 2026-06-10 08:05:14 +02:00
943 lines
38 KiB
Markdown
943 lines
38 KiB
Markdown
# Historic SQL Pattern WorkUnit Sharding Implementation Plan
|
|
|
|
> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.
|
|
|
|
**Goal:** Keep historic-SQL pattern WorkUnit inputs under the raw-file and prompt-size limits by writing deterministic bounded pattern shards while preserving `patterns-input.json` as the full audit artifact.
|
|
|
|
**Architecture:** The stager continues to write full `patterns-input.json` for audit and diff visibility, then writes bounded `patterns-input/part-0001.json` style shards that contain only cross-table pattern candidates. The chunker emits one `historic_sql_patterns` WorkUnit per changed shard and never asks the skill to read the full audit file. Pattern projection is unchanged because emitted evidence already carries a free-form `rawPath`.
|
|
|
|
**Tech Stack:** TypeScript, Node.js filesystem APIs, Zod, Vitest, pnpm workspace commands.
|
|
|
|
---
|
|
|
|
## Spec And Existing Plan Status
|
|
|
|
Spec: `docs/superpowers/specs/2026-05-11-historic-sql-redesign-design.md`
|
|
|
|
Plans derived from this spec:
|
|
|
|
- `docs/superpowers/plans/2026-05-11-historic-sql-foundations.md` - implemented. Current evidence includes `packages/context/src/ingest/adapters/historic-sql/skill-schemas.ts`, `SqlAnalysisPort.analyzeBatch()`, daemon `/sql/analyze-batch`, `SemanticLayerSource.usage`, and `mergeUsagePreservingExternal()`.
|
|
- `docs/superpowers/plans/2026-05-11-historic-sql-search-enrichment.md` - implemented. Current evidence includes usage-aware `buildSemanticLayerSourceSearchText()`, FTS snippets in `sqlite-sl-sources-index.ts`, and list surfaces exposing `frequencyTier` plus `snippet`.
|
|
- `docs/superpowers/plans/2026-05-11-historic-sql-unified-hot-path.md` - implemented. Current evidence includes `stageHistoricSqlAggregatedSnapshot()`, `chunkHistoricSqlUnifiedStagedDir()`, `PostgresPgssReader`, aggregate BigQuery/Snowflake readers, unified schemas, and package exports.
|
|
- `docs/superpowers/plans/2026-05-11-historic-sql-skills-projection-cutover.md` - implemented. Current evidence includes production adapter cutover, `historic_sql_table_digest`, `historic_sql_patterns`, `emit_historic_sql_evidence`, `HistoricSqlProjectionPostProcessor`, and removal of legacy skill names from runtime code.
|
|
- `docs/superpowers/plans/2026-05-11-historic-sql-cross-dialect-readiness.md` - implemented. Current evidence includes local adapter registration tests for Postgres, BigQuery, and Snowflake plus PG doctor coverage for informational `pg_stat_statements.max`.
|
|
- `docs/superpowers/plans/2026-05-11-historic-sql-docs-smoke-and-config-cleanup.md` - implemented. Current evidence includes canonical setup config tests, docs using `minExecutions`, and the Postgres historic smoke script asserting unified staged artifacts and unchanged-run idempotency.
|
|
- `docs/superpowers/plans/2026-05-11-historic-sql-projection-archive-hardening.md` - implemented. Current evidence includes `isArchivedPatternPage()`, archive exclusion from active slug matching, stale table tests, and legacy query-page cleanup coverage.
|
|
- `docs/superpowers/plans/2026-05-11-historic-sql-end-to-end-retrieval-acceptance.md` - implemented. Current evidence includes `local-ingest-acceptance.test.ts` proving production adapter output reaches SL search and wiki search.
|
|
- `docs/superpowers/plans/2026-05-11-historic-sql-redaction-hardening.md` - implemented. Current evidence includes `redaction.ts`, `redaction.test.ts`, and `stage-unified.test.ts` proving original SQL is analyzed while staged artifacts contain `[REDACTED]`.
|
|
|
|
No existing spec-derived plan is currently unimplemented in this worktree. This plan covers the next uncovered implementation gap from spec section 5.2: `historic_sql_patterns` may need "a small handful" of deterministic chunks when `patterns-input.json` exceeds the LLM context budget. Current code always emits one WorkUnit with raw file `patterns-input.json`; `read_raw_file` rejects files larger than 120,000 bytes and WorkUnit prompt construction rejects prompts larger than 240,000 characters.
|
|
|
|
## File Structure
|
|
|
|
- Create `packages/context/src/ingest/adapters/historic-sql/pattern-inputs.ts`
|
|
Owns deterministic pattern audit ordering, cross-table candidate filtering, byte-bounded shard creation, shard path constants, and shard path detection.
|
|
- Create `packages/context/src/ingest/adapters/historic-sql/pattern-inputs.test.ts`
|
|
Covers deterministic shard ordering, single-table exclusion from WorkUnit shards, byte limits, and oversize-template manifest warnings.
|
|
- Modify `packages/context/src/ingest/adapters/historic-sql/stage-unified.ts`
|
|
Writes full `patterns-input.json` plus bounded `patterns-input/part-0001.json` shard files, and appends shard warnings to `manifest.json`.
|
|
- Modify `packages/context/src/ingest/adapters/historic-sql/stage-unified.test.ts`
|
|
Adds a regression for audit file preservation and sharded WorkUnit input creation.
|
|
- Modify `packages/context/src/ingest/adapters/historic-sql/chunk-unified.ts`
|
|
Emits one patterns WorkUnit per changed shard path, treats root `patterns-input.json` as audit-only, and includes shard paths in the scope descriptor and eviction calculation.
|
|
- Modify `packages/context/src/ingest/adapters/historic-sql/chunk-unified.test.ts`
|
|
Updates root-file expectations and adds multi-shard diff behavior.
|
|
- Modify `packages/context/skills/historic_sql_patterns/SKILL.md`
|
|
Tells the skill to read the exact pattern shard in `rawFiles` and emit evidence with that shard as `rawPath`.
|
|
- Modify `packages/context/src/ingest/adapters/historic-sql/local-ingest-acceptance.test.ts`
|
|
Updates the fake agent to emit pattern evidence for `historic-sql-patterns-part-0001`.
|
|
- Modify `packages/context/src/ingest/ingest-runtime-assets.test.ts`
|
|
Keeps packaged skill assertions aligned with sharded pattern file guidance.
|
|
|
|
## Task 1: Add Pattern Input Sharding Helper
|
|
|
|
**Files:**
|
|
- Create: `packages/context/src/ingest/adapters/historic-sql/pattern-inputs.ts`
|
|
- Create: `packages/context/src/ingest/adapters/historic-sql/pattern-inputs.test.ts`
|
|
|
|
- [ ] **Step 1: Write the failing helper tests**
|
|
|
|
Create `packages/context/src/ingest/adapters/historic-sql/pattern-inputs.test.ts`:
|
|
|
|
```typescript
|
|
import { describe, expect, it } from 'vitest';
|
|
import {
|
|
HISTORIC_SQL_PATTERN_WORKUNIT_MAX_BYTES,
|
|
isHistoricSqlPatternInputShardPath,
|
|
serializedStagedPatternsInputByteLength,
|
|
splitHistoricSqlPatternInputs,
|
|
} from './pattern-inputs.js';
|
|
import type { StagedPatternsInput } from './types.js';
|
|
|
|
type PatternTemplate = StagedPatternsInput['templates'][number];
|
|
|
|
function template(id: string, tablesTouched: string[], canonicalSql = 'select 1'): PatternTemplate {
|
|
return {
|
|
id,
|
|
canonicalSql,
|
|
tablesTouched,
|
|
executionsBucket: '10-100',
|
|
distinctUsersBucket: '2-5',
|
|
dialect: 'postgres',
|
|
};
|
|
}
|
|
|
|
describe('historic-SQL pattern input sharding', () => {
|
|
it('keeps the audit input complete while sharding only cross-table pattern candidates', () => {
|
|
const largeSql = `select * from public.orders join public.customers on true where marker = '${'x'.repeat(260)}'`;
|
|
const input: StagedPatternsInput = {
|
|
templates: [
|
|
template('single-table-orders', ['public.orders']),
|
|
template('orders-customers-2', ['public.orders', 'public.customers'], largeSql),
|
|
template('orders-customers-1', ['public.customers', 'public.orders'], largeSql),
|
|
template('orders-customers-payments', ['public.orders', 'public.customers', 'public.payments'], largeSql),
|
|
],
|
|
};
|
|
|
|
const result = splitHistoricSqlPatternInputs(input, { maxBytes: 760 });
|
|
|
|
expect(result.auditInput.templates.map((entry) => entry.id)).toEqual([
|
|
'orders-customers-1',
|
|
'orders-customers-2',
|
|
'orders-customers-payments',
|
|
'single-table-orders',
|
|
]);
|
|
expect(result.shards.length).toBeGreaterThan(1);
|
|
expect(result.shards.map((shard) => shard.path)).toEqual(['patterns-input/part-0001.json', 'patterns-input/part-0002.json', 'patterns-input/part-0003.json']);
|
|
expect(result.shards.flatMap((shard) => shard.input.templates.map((entry) => entry.id))).toEqual([
|
|
'orders-customers-payments',
|
|
'orders-customers-1',
|
|
'orders-customers-2',
|
|
]);
|
|
expect(result.shards.every((shard) => shard.byteLength <= 760)).toBe(true);
|
|
expect(result.shards.flatMap((shard) => shard.input.templates).some((entry) => entry.id === 'single-table-orders')).toBe(false);
|
|
expect(result.warnings).toEqual([]);
|
|
});
|
|
|
|
it('omits a single oversized template from shards and reports a manifest warning', () => {
|
|
const input: StagedPatternsInput = {
|
|
templates: [
|
|
template(
|
|
'oversized-cross-table',
|
|
['public.orders', 'public.customers'],
|
|
`select * from public.orders join public.customers on true where payload = '${'x'.repeat(500)}'`,
|
|
),
|
|
],
|
|
};
|
|
|
|
const result = splitHistoricSqlPatternInputs(input, { maxBytes: 240 });
|
|
|
|
expect(result.auditInput.templates.map((entry) => entry.id)).toEqual(['oversized-cross-table']);
|
|
expect(result.shards).toEqual([]);
|
|
expect(result.warnings).toEqual(['patterns_input_template_too_large:oversized-cross-table']);
|
|
});
|
|
|
|
it('recognizes only generated pattern shard paths', () => {
|
|
expect(isHistoricSqlPatternInputShardPath('patterns-input/part-0001.json')).toBe(true);
|
|
expect(isHistoricSqlPatternInputShardPath('patterns-input/part-0012.json')).toBe(true);
|
|
expect(isHistoricSqlPatternInputShardPath('patterns-input.json')).toBe(false);
|
|
expect(isHistoricSqlPatternInputShardPath('patterns-input/part-1.json')).toBe(false);
|
|
expect(isHistoricSqlPatternInputShardPath('patterns-input/readme.md')).toBe(false);
|
|
});
|
|
|
|
it('uses a production byte budget below read_raw_file maximum size', () => {
|
|
expect(HISTORIC_SQL_PATTERN_WORKUNIT_MAX_BYTES).toBeLessThan(120_000);
|
|
expect(serializedStagedPatternsInputByteLength({ templates: [] })).toBeGreaterThan(0);
|
|
});
|
|
});
|
|
```
|
|
|
|
- [ ] **Step 2: Run helper tests to verify they fail**
|
|
|
|
Run:
|
|
|
|
```bash
|
|
pnpm --filter @ktx/context exec vitest run src/ingest/adapters/historic-sql/pattern-inputs.test.ts
|
|
```
|
|
|
|
Expected: FAIL because `./pattern-inputs.js` does not exist.
|
|
|
|
- [ ] **Step 3: Add the sharding helper**
|
|
|
|
Create `packages/context/src/ingest/adapters/historic-sql/pattern-inputs.ts`:
|
|
|
|
```typescript
|
|
import { Buffer } from 'node:buffer';
|
|
import type { StagedPatternsInput } from './types.js';
|
|
|
|
export const HISTORIC_SQL_PATTERN_WORKUNIT_DIR = 'patterns-input';
|
|
export const HISTORIC_SQL_PATTERN_WORKUNIT_MAX_BYTES = 110_000;
|
|
export const HISTORIC_SQL_PATTERN_WORKUNIT_PATH_RE = /^patterns-input\/part-\d{4}\.json$/;
|
|
|
|
type PatternTemplate = StagedPatternsInput['templates'][number];
|
|
|
|
export interface HistoricSqlPatternInputShard {
|
|
path: string;
|
|
input: StagedPatternsInput;
|
|
byteLength: number;
|
|
}
|
|
|
|
export interface HistoricSqlPatternInputSplitResult {
|
|
auditInput: StagedPatternsInput;
|
|
shards: HistoricSqlPatternInputShard[];
|
|
warnings: string[];
|
|
}
|
|
|
|
export interface HistoricSqlPatternInputSplitOptions {
|
|
maxBytes?: number;
|
|
}
|
|
|
|
export function isHistoricSqlPatternInputShardPath(path: string): boolean {
|
|
return HISTORIC_SQL_PATTERN_WORKUNIT_PATH_RE.test(path);
|
|
}
|
|
|
|
export function serializeStagedPatternsInput(input: StagedPatternsInput): string {
|
|
return `${JSON.stringify(input, null, 2)}\n`;
|
|
}
|
|
|
|
export function serializedStagedPatternsInputByteLength(input: StagedPatternsInput): number {
|
|
return Buffer.byteLength(serializeStagedPatternsInput(input), 'utf-8');
|
|
}
|
|
|
|
function sortedAuditTemplates(templates: readonly PatternTemplate[]): PatternTemplate[] {
|
|
return [...templates].sort((left, right) => left.id.localeCompare(right.id));
|
|
}
|
|
|
|
function sortedPatternCandidates(templates: readonly PatternTemplate[]): PatternTemplate[] {
|
|
return [...templates]
|
|
.filter((template) => template.tablesTouched.length >= 2)
|
|
.map((template) => ({ ...template, tablesTouched: [...template.tablesTouched].sort() }))
|
|
.sort((left, right) => {
|
|
const cardinality = right.tablesTouched.length - left.tablesTouched.length;
|
|
if (cardinality !== 0) return cardinality;
|
|
const tableSignature = left.tablesTouched.join('\0').localeCompare(right.tablesTouched.join('\0'));
|
|
if (tableSignature !== 0) return tableSignature;
|
|
return left.id.localeCompare(right.id);
|
|
});
|
|
}
|
|
|
|
function shardPath(index: number): string {
|
|
return `${HISTORIC_SQL_PATTERN_WORKUNIT_DIR}/part-${String(index).padStart(4, '0')}.json`;
|
|
}
|
|
|
|
export function splitHistoricSqlPatternInputs(
|
|
input: StagedPatternsInput,
|
|
options: HistoricSqlPatternInputSplitOptions = {},
|
|
): HistoricSqlPatternInputSplitResult {
|
|
const maxBytes = options.maxBytes ?? HISTORIC_SQL_PATTERN_WORKUNIT_MAX_BYTES;
|
|
const auditInput: StagedPatternsInput = { templates: sortedAuditTemplates(input.templates) };
|
|
const warnings: string[] = [];
|
|
const shards: HistoricSqlPatternInputShard[] = [];
|
|
let current: PatternTemplate[] = [];
|
|
|
|
const flush = () => {
|
|
if (current.length === 0) {
|
|
return;
|
|
}
|
|
const shardInput: StagedPatternsInput = { templates: current };
|
|
shards.push({
|
|
path: shardPath(shards.length + 1),
|
|
input: shardInput,
|
|
byteLength: serializedStagedPatternsInputByteLength(shardInput),
|
|
});
|
|
current = [];
|
|
};
|
|
|
|
for (const template of sortedPatternCandidates(input.templates)) {
|
|
const singleInput: StagedPatternsInput = { templates: [template] };
|
|
if (serializedStagedPatternsInputByteLength(singleInput) > maxBytes) {
|
|
warnings.push(`patterns_input_template_too_large:${template.id}`);
|
|
continue;
|
|
}
|
|
|
|
const nextInput: StagedPatternsInput = { templates: [...current, template] };
|
|
if (current.length > 0 && serializedStagedPatternsInputByteLength(nextInput) > maxBytes) {
|
|
flush();
|
|
}
|
|
current.push(template);
|
|
}
|
|
|
|
flush();
|
|
return { auditInput, shards, warnings };
|
|
}
|
|
```
|
|
|
|
- [ ] **Step 4: Run helper tests to verify they pass**
|
|
|
|
Run:
|
|
|
|
```bash
|
|
pnpm --filter @ktx/context exec vitest run src/ingest/adapters/historic-sql/pattern-inputs.test.ts
|
|
```
|
|
|
|
Expected: PASS.
|
|
|
|
- [ ] **Step 5: Commit the helper**
|
|
|
|
```bash
|
|
git add packages/context/src/ingest/adapters/historic-sql/pattern-inputs.ts packages/context/src/ingest/adapters/historic-sql/pattern-inputs.test.ts
|
|
git commit -m "feat: shard historic sql pattern inputs"
|
|
```
|
|
|
|
## Task 2: Write Pattern Shards During Staging
|
|
|
|
**Files:**
|
|
- Modify: `packages/context/src/ingest/adapters/historic-sql/stage-unified.ts`
|
|
- Modify: `packages/context/src/ingest/adapters/historic-sql/stage-unified.test.ts`
|
|
- Test: `packages/context/src/ingest/adapters/historic-sql/pattern-inputs.test.ts`
|
|
|
|
- [ ] **Step 1: Add the failing stager regression**
|
|
|
|
Append this test inside the existing `describe('stageHistoricSqlAggregatedSnapshot', ...)` block in `packages/context/src/ingest/adapters/historic-sql/stage-unified.test.ts`:
|
|
|
|
```typescript
|
|
it('preserves full patterns audit input and writes bounded cross-table pattern shards', async () => {
|
|
const stagedDir = await tempDir();
|
|
const largeSql = `select * from public.orders o join public.customers c on c.id = o.customer_id where payload = '${'x'.repeat(8000)}'`;
|
|
const reader: HistoricSqlReader = {
|
|
async probe() {
|
|
return { warnings: [], info: [] };
|
|
},
|
|
async *fetchAggregated() {
|
|
yield aggregate({
|
|
templateId: 'orders-customers-a',
|
|
canonicalSql: largeSql,
|
|
stats: {
|
|
executions: 25,
|
|
distinctUsers: 4,
|
|
firstSeen: '2026-05-01T00:00:00.000Z',
|
|
lastSeen: '2026-05-11T00:00:00.000Z',
|
|
p50RuntimeMs: 15,
|
|
p95RuntimeMs: 90,
|
|
errorRate: 0,
|
|
rowsProduced: 250,
|
|
},
|
|
});
|
|
yield aggregate({
|
|
templateId: 'orders-customers-b',
|
|
canonicalSql: largeSql.replace('payload', 'payload_b'),
|
|
stats: {
|
|
executions: 22,
|
|
distinctUsers: 3,
|
|
firstSeen: '2026-05-01T00:00:00.000Z',
|
|
lastSeen: '2026-05-11T00:00:00.000Z',
|
|
p50RuntimeMs: 20,
|
|
p95RuntimeMs: 95,
|
|
errorRate: 0,
|
|
rowsProduced: 220,
|
|
},
|
|
});
|
|
yield aggregate({
|
|
templateId: 'orders-single-table',
|
|
canonicalSql: 'select count(*) from public.orders',
|
|
stats: {
|
|
executions: 30,
|
|
distinctUsers: 2,
|
|
firstSeen: '2026-05-01T00:00:00.000Z',
|
|
lastSeen: '2026-05-11T00:00:00.000Z',
|
|
p50RuntimeMs: 10,
|
|
p95RuntimeMs: 20,
|
|
errorRate: 0,
|
|
rowsProduced: 30,
|
|
},
|
|
});
|
|
},
|
|
};
|
|
const sqlAnalysis: SqlAnalysisPort = {
|
|
analyzeForFingerprint: vi.fn(),
|
|
analyzeBatch: vi.fn(async () => new Map([
|
|
[
|
|
'orders-customers-a',
|
|
{
|
|
tablesTouched: ['public.orders', 'public.customers'],
|
|
columnsByClause: {
|
|
select: [],
|
|
where: ['payload'],
|
|
join: ['customer_id', 'id'],
|
|
groupBy: [],
|
|
},
|
|
},
|
|
],
|
|
[
|
|
'orders-customers-b',
|
|
{
|
|
tablesTouched: ['public.orders', 'public.customers'],
|
|
columnsByClause: {
|
|
select: [],
|
|
where: ['payload_b'],
|
|
join: ['customer_id', 'id'],
|
|
groupBy: [],
|
|
},
|
|
},
|
|
],
|
|
[
|
|
'orders-single-table',
|
|
{
|
|
tablesTouched: ['public.orders'],
|
|
columnsByClause: {
|
|
select: [],
|
|
where: [],
|
|
join: [],
|
|
groupBy: [],
|
|
},
|
|
},
|
|
],
|
|
])),
|
|
};
|
|
|
|
await stageHistoricSqlAggregatedSnapshot({
|
|
stagedDir,
|
|
connectionId: 'warehouse',
|
|
queryClient: {},
|
|
reader,
|
|
sqlAnalysis,
|
|
pullConfig: { dialect: 'postgres' },
|
|
now: new Date('2026-05-11T12:00:00.000Z'),
|
|
});
|
|
|
|
const audit = await readJson<Record<string, any>>(stagedDir, 'patterns-input.json');
|
|
expect(audit.templates.map((entry: any) => entry.id)).toEqual([
|
|
'orders-customers-a',
|
|
'orders-customers-b',
|
|
'orders-single-table',
|
|
]);
|
|
|
|
const firstShard = await readJson<Record<string, any>>(stagedDir, 'patterns-input/part-0001.json');
|
|
expect(firstShard.templates.map((entry: any) => entry.id)).toEqual(['orders-customers-a', 'orders-customers-b']);
|
|
expect(firstShard.templates.some((entry: any) => entry.id === 'orders-single-table')).toBe(false);
|
|
|
|
const manifest = await readJson<Record<string, any>>(stagedDir, 'manifest.json');
|
|
expect(manifest.warnings).toEqual([]);
|
|
});
|
|
```
|
|
|
|
- [ ] **Step 2: Run the stager regression to verify it fails**
|
|
|
|
Run:
|
|
|
|
```bash
|
|
pnpm --filter @ktx/context exec vitest run src/ingest/adapters/historic-sql/stage-unified.test.ts
|
|
```
|
|
|
|
Expected: FAIL because `patterns-input/part-0001.json` is not written.
|
|
|
|
- [ ] **Step 3: Import the sharding helper in the stager**
|
|
|
|
In `packages/context/src/ingest/adapters/historic-sql/stage-unified.ts`, add this import below the bucket import block:
|
|
|
|
```typescript
|
|
import { splitHistoricSqlPatternInputs } from './pattern-inputs.js';
|
|
```
|
|
|
|
- [ ] **Step 4: Write the audit input and shard files**
|
|
|
|
In `stageHistoricSqlAggregatedSnapshot()` in `packages/context/src/ingest/adapters/historic-sql/stage-unified.ts`, replace this block:
|
|
|
|
```typescript
|
|
await writeJson(input.stagedDir, 'patterns-input.json', toPatternsInput(parsedTemplates));
|
|
await writeJson(input.stagedDir, 'manifest.json', {
|
|
source: HISTORIC_SQL_SOURCE_KEY,
|
|
connectionId: input.connectionId,
|
|
dialect: config.dialect,
|
|
fetchedAt: now.toISOString(),
|
|
windowStart: windowStart.toISOString(),
|
|
windowEnd: now.toISOString(),
|
|
snapshotRowCount,
|
|
touchedTableCount: byTable.size,
|
|
parseFailures: warnings.filter((warning) => warning.startsWith('parse_failed:')).length,
|
|
warnings,
|
|
probeWarnings: probe.warnings,
|
|
staleArchiveAfterDays: config.staleArchiveAfterDays,
|
|
});
|
|
```
|
|
|
|
with this code:
|
|
|
|
```typescript
|
|
const patternsInput = toPatternsInput(parsedTemplates);
|
|
const patternInputSplit = splitHistoricSqlPatternInputs(patternsInput);
|
|
const allWarnings = [...warnings, ...patternInputSplit.warnings];
|
|
await writeJson(input.stagedDir, 'patterns-input.json', patternInputSplit.auditInput);
|
|
for (const shard of patternInputSplit.shards) {
|
|
await writeJson(input.stagedDir, shard.path, shard.input);
|
|
}
|
|
await writeJson(input.stagedDir, 'manifest.json', {
|
|
source: HISTORIC_SQL_SOURCE_KEY,
|
|
connectionId: input.connectionId,
|
|
dialect: config.dialect,
|
|
fetchedAt: now.toISOString(),
|
|
windowStart: windowStart.toISOString(),
|
|
windowEnd: now.toISOString(),
|
|
snapshotRowCount,
|
|
touchedTableCount: byTable.size,
|
|
parseFailures: allWarnings.filter((warning) => warning.startsWith('parse_failed:')).length,
|
|
warnings: allWarnings,
|
|
probeWarnings: probe.warnings,
|
|
staleArchiveAfterDays: config.staleArchiveAfterDays,
|
|
});
|
|
```
|
|
|
|
- [ ] **Step 5: Run helper and stager tests**
|
|
|
|
Run:
|
|
|
|
```bash
|
|
pnpm --filter @ktx/context exec vitest run src/ingest/adapters/historic-sql/pattern-inputs.test.ts src/ingest/adapters/historic-sql/stage-unified.test.ts
|
|
```
|
|
|
|
Expected: PASS.
|
|
|
|
- [ ] **Step 6: Commit stager shard writing**
|
|
|
|
```bash
|
|
git add packages/context/src/ingest/adapters/historic-sql/pattern-inputs.ts packages/context/src/ingest/adapters/historic-sql/pattern-inputs.test.ts packages/context/src/ingest/adapters/historic-sql/stage-unified.ts packages/context/src/ingest/adapters/historic-sql/stage-unified.test.ts
|
|
git commit -m "feat: write historic sql pattern shards"
|
|
```
|
|
|
|
## Task 3: Emit Pattern WorkUnits From Shards
|
|
|
|
**Files:**
|
|
- Modify: `packages/context/src/ingest/adapters/historic-sql/chunk-unified.ts`
|
|
- Modify: `packages/context/src/ingest/adapters/historic-sql/chunk-unified.test.ts`
|
|
- Test: `packages/context/src/ingest/adapters/historic-sql/pattern-inputs.test.ts`
|
|
|
|
- [ ] **Step 1: Update chunk tests for sharded pattern WorkUnits**
|
|
|
|
In `packages/context/src/ingest/adapters/historic-sql/chunk-unified.test.ts`, replace the `patterns-input.json` write inside `writeUnifiedStagedDir()` with these writes:
|
|
|
|
```typescript
|
|
await writeJson(root, 'patterns-input.json', {
|
|
templates: [
|
|
{
|
|
id: 'orders',
|
|
canonicalSql: 'select * from public.orders join public.customers on true',
|
|
tablesTouched: ['public.orders', 'public.customers'],
|
|
executionsBucket: '10-100',
|
|
distinctUsersBucket: '2-5',
|
|
dialect: 'postgres',
|
|
},
|
|
],
|
|
});
|
|
await writeJson(root, 'patterns-input/part-0001.json', {
|
|
templates: [
|
|
{
|
|
id: 'orders',
|
|
canonicalSql: 'select * from public.orders join public.customers on true',
|
|
tablesTouched: ['public.orders', 'public.customers'],
|
|
executionsBucket: '10-100',
|
|
distinctUsersBucket: '2-5',
|
|
dialect: 'postgres',
|
|
},
|
|
],
|
|
});
|
|
```
|
|
|
|
In the first test, replace the patterns WorkUnit expectation with:
|
|
|
|
```typescript
|
|
expect.objectContaining({
|
|
unitKey: 'historic-sql-patterns-part-0001',
|
|
displayLabel: 'Historic SQL cross-table patterns: part-0001',
|
|
rawFiles: ['patterns-input/part-0001.json'],
|
|
dependencyPaths: ['manifest.json'],
|
|
notes: expect.stringContaining('patterns-input/part-0001.json'),
|
|
}),
|
|
```
|
|
|
|
In the diff-set test, replace the second expectation with:
|
|
|
|
```typescript
|
|
await expect(
|
|
chunkHistoricSqlUnifiedStagedDir(stagedDir, {
|
|
added: [],
|
|
modified: ['patterns-input/part-0001.json'],
|
|
deleted: [],
|
|
unchanged: ['manifest.json', 'patterns-input.json', 'tables/public.orders.json'],
|
|
}),
|
|
).resolves.toMatchObject({
|
|
workUnits: [expect.objectContaining({ unitKey: 'historic-sql-patterns-part-0001' })],
|
|
});
|
|
|
|
await expect(
|
|
chunkHistoricSqlUnifiedStagedDir(stagedDir, {
|
|
added: [],
|
|
modified: ['patterns-input.json'],
|
|
deleted: [],
|
|
unchanged: ['manifest.json', 'patterns-input/part-0001.json', 'tables/public.orders.json'],
|
|
}),
|
|
).resolves.toMatchObject({
|
|
workUnits: [],
|
|
});
|
|
```
|
|
|
|
In the scope test, add these expectations:
|
|
|
|
```typescript
|
|
expect(scope.isPathInScope('patterns-input/part-0001.json')).toBe(true);
|
|
expect(scope.isPathInScope('patterns-input/part-1.json')).toBe(false);
|
|
```
|
|
|
|
Append this additional test inside the same `describe` block:
|
|
|
|
```typescript
|
|
it('emits one patterns WorkUnit per changed shard', async () => {
|
|
const stagedDir = await tempDir();
|
|
await writeUnifiedStagedDir(stagedDir);
|
|
await writeJson(stagedDir, 'patterns-input/part-0002.json', {
|
|
templates: [
|
|
{
|
|
id: 'line-items',
|
|
canonicalSql: 'select * from public.orders join public.line_items on true',
|
|
tablesTouched: ['public.orders', 'public.line_items'],
|
|
executionsBucket: '10-100',
|
|
distinctUsersBucket: '2-5',
|
|
dialect: 'postgres',
|
|
},
|
|
],
|
|
});
|
|
|
|
const result = await chunkHistoricSqlUnifiedStagedDir(stagedDir, {
|
|
added: ['patterns-input/part-0002.json'],
|
|
modified: ['patterns-input/part-0001.json'],
|
|
deleted: [],
|
|
unchanged: ['manifest.json', 'patterns-input.json', 'tables/public.orders.json'],
|
|
});
|
|
|
|
expect(result.workUnits.map((unit) => unit.unitKey)).toEqual([
|
|
'historic-sql-patterns-part-0001',
|
|
'historic-sql-patterns-part-0002',
|
|
]);
|
|
expect(result.workUnits.map((unit) => unit.rawFiles)).toEqual([
|
|
['patterns-input/part-0001.json'],
|
|
['patterns-input/part-0002.json'],
|
|
]);
|
|
});
|
|
```
|
|
|
|
- [ ] **Step 2: Run chunk tests to verify they fail**
|
|
|
|
Run:
|
|
|
|
```bash
|
|
pnpm --filter @ktx/context exec vitest run src/ingest/adapters/historic-sql/chunk-unified.test.ts
|
|
```
|
|
|
|
Expected: FAIL because `chunkHistoricSqlUnifiedStagedDir()` still emits `historic-sql-patterns` from root `patterns-input.json`.
|
|
|
|
- [ ] **Step 3: Import shard path helpers in the chunker**
|
|
|
|
In `packages/context/src/ingest/adapters/historic-sql/chunk-unified.ts`, add this import below the existing type imports:
|
|
|
|
```typescript
|
|
import { isHistoricSqlPatternInputShardPath } from './pattern-inputs.js';
|
|
```
|
|
|
|
- [ ] **Step 4: Emit WorkUnits from shard paths**
|
|
|
|
In `packages/context/src/ingest/adapters/historic-sql/chunk-unified.ts`, replace the root `patterns-input.json` WorkUnit block:
|
|
|
|
```typescript
|
|
if (files.includes('patterns-input.json') && touchedPath('patterns-input.json', touched)) {
|
|
stagedPatternsInputSchema.parse(await readJson(stagedDir, 'patterns-input.json'));
|
|
workUnits.push({
|
|
unitKey: 'historic-sql-patterns',
|
|
displayLabel: 'Historic SQL cross-table patterns',
|
|
rawFiles: ['patterns-input.json'],
|
|
dependencyPaths: ['manifest.json'],
|
|
peerFileIndex: files.filter((file) => file !== 'patterns-input.json' && file !== 'manifest.json').sort(),
|
|
notes:
|
|
'Use historic_sql_patterns. Read patterns-input.json and emit pattern objects with emit_historic_sql_evidence. Do not call wiki_write or sl_write_source.',
|
|
});
|
|
}
|
|
```
|
|
|
|
with this code:
|
|
|
|
```typescript
|
|
for (const path of files.filter(isHistoricSqlPatternInputShardPath)) {
|
|
if (!touchedPath(path, touched)) {
|
|
continue;
|
|
}
|
|
stagedPatternsInputSchema.parse(await readJson(stagedDir, path));
|
|
const shardLabel = path.replace(/^patterns-input\//, '').replace(/\.json$/, '');
|
|
workUnits.push({
|
|
unitKey: `historic-sql-patterns-${safeUnitKey(shardLabel)}`,
|
|
displayLabel: `Historic SQL cross-table patterns: ${shardLabel}`,
|
|
rawFiles: [path],
|
|
dependencyPaths: ['manifest.json'],
|
|
peerFileIndex: files.filter((file) => file !== path && file !== 'manifest.json').sort(),
|
|
notes:
|
|
`Use historic_sql_patterns. Read ${path} and emit pattern objects with emit_historic_sql_evidence using rawPath "${path}". Do not call wiki_write or sl_write_source.`,
|
|
});
|
|
}
|
|
```
|
|
|
|
- [ ] **Step 5: Update eviction and scope matching**
|
|
|
|
In `packages/context/src/ingest/adapters/historic-sql/chunk-unified.ts`, replace the deleted-path filter:
|
|
|
|
```typescript
|
|
const deleted = diffSet?.deleted.filter((path) => path === 'patterns-input.json' || /^tables\/.+\.json$/.test(path)).sort();
|
|
```
|
|
|
|
with:
|
|
|
|
```typescript
|
|
const deleted = diffSet?.deleted
|
|
.filter((path) => isHistoricSqlPatternInputShardPath(path) || /^tables\/.+\.json$/.test(path))
|
|
.sort();
|
|
```
|
|
|
|
In `describeHistoricSqlUnifiedScope()`, replace the scope predicate:
|
|
|
|
```typescript
|
|
isPathInScope: (rawPath) =>
|
|
rawPath === 'manifest.json' || rawPath === 'patterns-input.json' || /^tables\/.+\.json$/.test(rawPath),
|
|
```
|
|
|
|
with:
|
|
|
|
```typescript
|
|
isPathInScope: (rawPath) =>
|
|
rawPath === 'manifest.json' ||
|
|
rawPath === 'patterns-input.json' ||
|
|
isHistoricSqlPatternInputShardPath(rawPath) ||
|
|
/^tables\/.+\.json$/.test(rawPath),
|
|
```
|
|
|
|
- [ ] **Step 6: Run helper, stage, and chunk tests**
|
|
|
|
Run:
|
|
|
|
```bash
|
|
pnpm --filter @ktx/context exec vitest run src/ingest/adapters/historic-sql/pattern-inputs.test.ts src/ingest/adapters/historic-sql/stage-unified.test.ts src/ingest/adapters/historic-sql/chunk-unified.test.ts
|
|
```
|
|
|
|
Expected: PASS.
|
|
|
|
- [ ] **Step 7: Commit chunker shard WorkUnits**
|
|
|
|
```bash
|
|
git add packages/context/src/ingest/adapters/historic-sql/pattern-inputs.ts packages/context/src/ingest/adapters/historic-sql/pattern-inputs.test.ts packages/context/src/ingest/adapters/historic-sql/chunk-unified.ts packages/context/src/ingest/adapters/historic-sql/chunk-unified.test.ts packages/context/src/ingest/adapters/historic-sql/stage-unified.ts packages/context/src/ingest/adapters/historic-sql/stage-unified.test.ts
|
|
git commit -m "feat: emit historic sql pattern shard work units"
|
|
```
|
|
|
|
## Task 4: Update Skill Guidance And Acceptance Coverage
|
|
|
|
**Files:**
|
|
- Modify: `packages/context/skills/historic_sql_patterns/SKILL.md`
|
|
- Modify: `packages/context/src/ingest/adapters/historic-sql/local-ingest-acceptance.test.ts`
|
|
- Modify: `packages/context/src/ingest/ingest-runtime-assets.test.ts`
|
|
|
|
- [ ] **Step 1: Update the packaged historic SQL patterns skill**
|
|
|
|
Replace `packages/context/skills/historic_sql_patterns/SKILL.md` with:
|
|
|
|
````markdown
|
|
---
|
|
name: historic_sql_patterns
|
|
description: Identify recurring cross-table historic-SQL analytical intents from a bounded pattern shard and emit typed pattern evidence for deterministic wiki projection.
|
|
callers: [memory_agent]
|
|
---
|
|
|
|
# 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. Set each evidence object's `rawPath` to the exact raw file path read in step 3.
|
|
7. Stop after all pattern evidence has been emitted.
|
|
|
|
## Evidence Shape
|
|
|
|
Each call to `emit_historic_sql_evidence` must use this shape:
|
|
|
|
```json
|
|
{
|
|
"kind": "pattern",
|
|
"rawPath": "patterns-input/part-0001.json",
|
|
"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.
|
|
````
|
|
|
|
- [ ] **Step 2: Update runtime asset assertions**
|
|
|
|
In `packages/context/src/ingest/ingest-runtime-assets.test.ts`, replace this assertion:
|
|
|
|
```typescript
|
|
expect(body).toContain('patterns-input.json');
|
|
```
|
|
|
|
with:
|
|
|
|
```typescript
|
|
expect(body).toContain('patterns-input/part-0001.json');
|
|
```
|
|
|
|
- [ ] **Step 3: Update the local ingest acceptance fake agent**
|
|
|
|
In `packages/context/src/ingest/adapters/historic-sql/local-ingest-acceptance.test.ts`, replace this block:
|
|
|
|
```typescript
|
|
if (params.telemetryTags.unitKey === 'historic-sql-patterns') {
|
|
const result = await emitEvidence.execute(
|
|
{
|
|
kind: 'pattern',
|
|
rawPath: 'patterns-input.json',
|
|
pattern: {
|
|
```
|
|
|
|
with:
|
|
|
|
```typescript
|
|
if (params.telemetryTags.unitKey === 'historic-sql-patterns-part-0001') {
|
|
const result = await emitEvidence.execute(
|
|
{
|
|
kind: 'pattern',
|
|
rawPath: 'patterns-input/part-0001.json',
|
|
pattern: {
|
|
```
|
|
|
|
The rest of the pattern object stays unchanged.
|
|
|
|
- [ ] **Step 4: Run skill and acceptance tests**
|
|
|
|
Run:
|
|
|
|
```bash
|
|
pnpm --filter @ktx/context exec vitest run src/ingest/ingest-runtime-assets.test.ts src/ingest/adapters/historic-sql/local-ingest-acceptance.test.ts
|
|
```
|
|
|
|
Expected: PASS.
|
|
|
|
- [ ] **Step 5: Commit skill and acceptance updates**
|
|
|
|
```bash
|
|
git add packages/context/skills/historic_sql_patterns/SKILL.md packages/context/src/ingest/ingest-runtime-assets.test.ts packages/context/src/ingest/adapters/historic-sql/local-ingest-acceptance.test.ts
|
|
git commit -m "test: align historic sql pattern skill with shards"
|
|
```
|
|
|
|
## Task 5: Final Verification
|
|
|
|
**Files:**
|
|
- Verify: `packages/context/src/ingest/adapters/historic-sql/pattern-inputs.ts`
|
|
- Verify: `packages/context/src/ingest/adapters/historic-sql/stage-unified.ts`
|
|
- Verify: `packages/context/src/ingest/adapters/historic-sql/chunk-unified.ts`
|
|
- Verify: `packages/context/skills/historic_sql_patterns/SKILL.md`
|
|
|
|
- [ ] **Step 1: Run focused historic SQL tests**
|
|
|
|
Run:
|
|
|
|
```bash
|
|
pnpm --filter @ktx/context exec vitest run \
|
|
src/ingest/adapters/historic-sql/pattern-inputs.test.ts \
|
|
src/ingest/adapters/historic-sql/stage-unified.test.ts \
|
|
src/ingest/adapters/historic-sql/chunk-unified.test.ts \
|
|
src/ingest/adapters/historic-sql/local-ingest-acceptance.test.ts \
|
|
src/ingest/ingest-runtime-assets.test.ts
|
|
```
|
|
|
|
Expected: PASS.
|
|
|
|
- [ ] **Step 2: Run context package type-check**
|
|
|
|
Run:
|
|
|
|
```bash
|
|
pnpm --filter @ktx/context run type-check
|
|
```
|
|
|
|
Expected: PASS.
|
|
|
|
- [ ] **Step 3: Verify no legacy historic SQL code path returned**
|
|
|
|
Run:
|
|
|
|
```bash
|
|
rg -n "stagePgStatStatementsTemplates|expandCategoricalTemplates|classifySlot|pgss-baseline|historic_sql_ingest|historic_sql_curator|PostgresPgssQueryHistoryReader|historic_sql_template" packages/context packages/cli
|
|
```
|
|
|
|
Expected: no matches in runtime or test source. Matches inside `docs/superpowers/plans/` are acceptable when searching docs separately, but this command does not search docs.
|
|
|
|
- [ ] **Step 4: Run pre-commit on changed files if configured**
|
|
|
|
Run:
|
|
|
|
```bash
|
|
uv run pre-commit run --files \
|
|
packages/context/src/ingest/adapters/historic-sql/pattern-inputs.ts \
|
|
packages/context/src/ingest/adapters/historic-sql/pattern-inputs.test.ts \
|
|
packages/context/src/ingest/adapters/historic-sql/stage-unified.ts \
|
|
packages/context/src/ingest/adapters/historic-sql/stage-unified.test.ts \
|
|
packages/context/src/ingest/adapters/historic-sql/chunk-unified.ts \
|
|
packages/context/src/ingest/adapters/historic-sql/chunk-unified.test.ts \
|
|
packages/context/src/ingest/adapters/historic-sql/local-ingest-acceptance.test.ts \
|
|
packages/context/src/ingest/ingest-runtime-assets.test.ts \
|
|
packages/context/skills/historic_sql_patterns/SKILL.md
|
|
```
|
|
|
|
Expected: PASS. If the repository has no pre-commit config or the local `uv` version cannot satisfy the project pin, record the exact error and rely on the focused tests plus type-check above.
|
|
|
|
- [ ] **Step 5: Commit verification-only adjustments if any were needed**
|
|
|
|
If any test or type-check step required small follow-up edits, commit them:
|
|
|
|
```bash
|
|
git add packages/context/src/ingest/adapters/historic-sql/pattern-inputs.ts packages/context/src/ingest/adapters/historic-sql/pattern-inputs.test.ts packages/context/src/ingest/adapters/historic-sql/stage-unified.ts packages/context/src/ingest/adapters/historic-sql/stage-unified.test.ts packages/context/src/ingest/adapters/historic-sql/chunk-unified.ts packages/context/src/ingest/adapters/historic-sql/chunk-unified.test.ts packages/context/src/ingest/adapters/historic-sql/local-ingest-acceptance.test.ts packages/context/src/ingest/ingest-runtime-assets.test.ts packages/context/skills/historic_sql_patterns/SKILL.md
|
|
git commit -m "test: verify historic sql pattern shard work units"
|
|
```
|
|
|
|
If there were no follow-up edits, do not create an empty commit.
|
|
|
|
## Self-Review
|
|
|
|
**Spec coverage:** This plan covers spec section 5.2's allowance for multiple deterministic pattern WorkUnits when `patterns-input.json` exceeds a context budget. It preserves section 4.6's full `patterns-input.json` audit artifact, keeps section 4.7's changed-file DiffSet behavior, and does not alter deterministic projection from section 5.3.
|
|
|
|
**Placeholder scan:** The plan contains concrete files, commands, expected outcomes, code snippets, and commit commands. It has no deferred implementation markers.
|
|
|
|
**Type consistency:** `StagedPatternsInput`, `splitHistoricSqlPatternInputs()`, `isHistoricSqlPatternInputShardPath()`, `HISTORIC_SQL_PATTERN_WORKUNIT_MAX_BYTES`, and `serializedStagedPatternsInputByteLength()` are introduced in Task 1 and imported with the same names in later tasks. Pattern shard raw paths use `patterns-input/part-0001.json` consistently in the stager, chunker, skill, and acceptance test.
|
|
|
|
Plan complete and saved to `docs/superpowers/plans/2026-05-11-historic-sql-pattern-workunit-sharding.md`. Two execution options:
|
|
|
|
**1. Subagent-Driven (recommended)** - I dispatch a fresh subagent per task, review between tasks, fast iteration
|
|
|
|
**2. Inline Execution** - Execute tasks in this session using executing-plans, batch execution with checkpoints
|
|
|
|
Which approach?
|