/**
* Window Function Tools for Native SQLite Adapter
*
* Provides window function tools for analytics and ranking operations.
*/
import { z } from "zod";
import type { ToolDefinition, RequestContext } from "../../../types/index.js";
import type { NativeSqliteAdapter } from "../NativeSqliteAdapter.js";
import { validateWhereClause } from "../../../utils/index.js";
// Schemas
const RowNumberSchema = z.object({
table: z.string().describe("Table name"),
orderBy: z.string().describe("Column(s) to order by"),
partitionBy: z.string().optional().describe("Column(s) to partition by"),
selectColumns: z
.array(z.string())
.optional()
.describe("Columns to include in result"),
whereClause: z.string().optional().describe("Optional WHERE clause"),
limit: z.number().optional().default(100).describe("Maximum rows to return"),
});
const RankSchema = z.object({
table: z.string().describe("Table name"),
orderBy: z.string().describe("Column(s) to order by (determines rank)"),
partitionBy: z.string().optional().describe("Column(s) to partition by"),
selectColumns: z
.array(z.string())
.optional()
.describe("Columns to include in result"),
rankType: z
.enum(["rank", "dense_rank", "percent_rank"])
.optional()
.default("rank")
.describe("Rank function type"),
whereClause: z.string().optional().describe("Optional WHERE clause"),
limit: z.number().optional().default(100).describe("Maximum rows to return"),
});
const LagLeadSchema = z.object({
table: z.string().describe("Table name"),
column: z.string().describe("Column to get lag/lead value from"),
orderBy: z.string().describe("Column(s) to order by"),
direction: z
.enum(["lag", "lead"])
.describe("LAG (previous) or LEAD (next) row"),
offset: z
.number()
.optional()
.default(1)
.describe("Number of rows to look back/ahead"),
defaultValue: z
.string()
.optional()
.describe("Default value if no row exists"),
partitionBy: z.string().optional().describe("Column(s) to partition by"),
selectColumns: z
.array(z.string())
.optional()
.describe("Columns to include in result"),
whereClause: z.string().optional().describe("Optional WHERE clause"),
limit: z.number().optional().default(100).describe("Maximum rows to return"),
});
const RunningTotalSchema = z.object({
table: z.string().describe("Table name"),
valueColumn: z.string().describe("Column to sum"),
orderBy: z.string().describe("Column(s) to order by"),
partitionBy: z
.string()
.optional()
.describe("Reset running total for each partition"),
selectColumns: z
.array(z.string())
.optional()
.describe("Columns to include in result"),
whereClause: z.string().optional().describe("Optional WHERE clause"),
limit: z.number().optional().default(100).describe("Maximum rows to return"),
});
const MovingAverageSchema = z.object({
table: z.string().describe("Table name"),
valueColumn: z.string().describe("Column to average"),
orderBy: z.string().describe("Column(s) to order by"),
windowSize: z.number().describe("Number of rows in the moving window"),
partitionBy: z.string().optional().describe("Column(s) to partition by"),
selectColumns: z
.array(z.string())
.optional()
.describe("Columns to include in result"),
whereClause: z.string().optional().describe("Optional WHERE clause"),
limit: z.number().optional().default(100).describe("Maximum rows to return"),
});
const NtileSchema = z.object({
table: z.string().describe("Table name"),
orderBy: z.string().describe("Column(s) to order by"),
buckets: z.number().describe("Number of buckets (e.g., 4 for quartiles)"),
partitionBy: z.string().optional().describe("Column(s) to partition by"),
selectColumns: z
.array(z.string())
.optional()
.describe("Columns to include in result"),
whereClause: z.string().optional().describe("Optional WHERE clause"),
limit: z.number().optional().default(100).describe("Maximum rows to return"),
});
/**
* Helper to format column selection
*/
function formatColumns(selectColumns: string[] | undefined): string {
if (selectColumns === undefined || selectColumns.length === 0) {
return "*";
}
return selectColumns.map((c) => `"${c}"`).join(", ");
}
/**
* Get all window function tools
*/
export function getWindowTools(adapter: NativeSqliteAdapter): ToolDefinition[] {
return [
createRowNumberTool(adapter),
createRankTool(adapter),
createLagLeadTool(adapter),
createRunningTotalTool(adapter),
createMovingAverageTool(adapter),
createNtileTool(adapter),
];
}
/**
* ROW_NUMBER window function
*/
function createRowNumberTool(adapter: NativeSqliteAdapter): ToolDefinition {
return {
name: "sqlite_window_row_number",
description:
"Assign sequential row numbers based on ordering. Useful for pagination and ranking.",
group: "stats",
inputSchema: RowNumberSchema,
requiredScopes: ["read"],
handler: async (params: unknown, _context: RequestContext) => {
const input = RowNumberSchema.parse(params);
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.table)) {
throw new Error("Invalid table name");
}
const columns = formatColumns(input.selectColumns);
const partition = input.partitionBy
? `PARTITION BY ${input.partitionBy}`
: "";
let sql = `
SELECT ${columns},
ROW_NUMBER() OVER (${partition} ORDER BY ${input.orderBy}) as row_num
FROM "${input.table}"
`;
if (input.whereClause) {
validateWhereClause(input.whereClause);
sql += ` WHERE ${input.whereClause}`;
}
sql += ` LIMIT ${input.limit}`;
const result = await adapter.executeReadQuery(sql);
return {
success: true,
rowCount: result.rows?.length ?? 0,
rows: result.rows,
};
},
};
}
/**
* RANK/DENSE_RANK/PERCENT_RANK window functions
*/
function createRankTool(adapter: NativeSqliteAdapter): ToolDefinition {
return {
name: "sqlite_window_rank",
description:
"Calculate rank of rows. RANK leaves gaps after ties, DENSE_RANK does not, PERCENT_RANK gives 0-1 range.",
group: "stats",
inputSchema: RankSchema,
requiredScopes: ["read"],
handler: async (params: unknown, _context: RequestContext) => {
const input = RankSchema.parse(params);
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.table)) {
throw new Error("Invalid table name");
}
const columns = formatColumns(input.selectColumns);
const partition = input.partitionBy
? `PARTITION BY ${input.partitionBy}`
: "";
const rankFunc = input.rankType.toUpperCase();
let sql = `
SELECT ${columns},
${rankFunc}() OVER (${partition} ORDER BY ${input.orderBy}) as rank_value
FROM "${input.table}"
`;
if (input.whereClause) {
validateWhereClause(input.whereClause);
sql += ` WHERE ${input.whereClause}`;
}
sql += ` LIMIT ${input.limit}`;
const result = await adapter.executeReadQuery(sql);
return {
success: true,
rankType: input.rankType,
rowCount: result.rows?.length ?? 0,
rows: result.rows,
};
},
};
}
/**
* LAG/LEAD window functions
*/
function createLagLeadTool(adapter: NativeSqliteAdapter): ToolDefinition {
return {
name: "sqlite_window_lag_lead",
description:
"Access previous (LAG) or next (LEAD) row values. Useful for comparing consecutive rows.",
group: "stats",
inputSchema: LagLeadSchema,
requiredScopes: ["read"],
handler: async (params: unknown, _context: RequestContext) => {
const input = LagLeadSchema.parse(params);
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.table)) {
throw new Error("Invalid table name");
}
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.column)) {
throw new Error("Invalid column name");
}
const columns = formatColumns(input.selectColumns);
const partition = input.partitionBy
? `PARTITION BY ${input.partitionBy}`
: "";
const func = input.direction.toUpperCase();
const defaultVal =
input.defaultValue !== undefined ? `, ${input.defaultValue}` : "";
let sql = `
SELECT ${columns},
${func}("${input.column}", ${input.offset}${defaultVal}) OVER (${partition} ORDER BY ${input.orderBy}) as ${input.direction}_value
FROM "${input.table}"
`;
if (input.whereClause) {
validateWhereClause(input.whereClause);
sql += ` WHERE ${input.whereClause}`;
}
sql += ` LIMIT ${input.limit}`;
const result = await adapter.executeReadQuery(sql);
return {
success: true,
direction: input.direction,
offset: input.offset,
rowCount: result.rows?.length ?? 0,
rows: result.rows,
};
},
};
}
/**
* Running total (cumulative SUM)
*/
function createRunningTotalTool(adapter: NativeSqliteAdapter): ToolDefinition {
return {
name: "sqlite_window_running_total",
description:
"Calculate running (cumulative) total. Useful for balance tracking, cumulative metrics.",
group: "stats",
inputSchema: RunningTotalSchema,
requiredScopes: ["read"],
handler: async (params: unknown, _context: RequestContext) => {
const input = RunningTotalSchema.parse(params);
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.table)) {
throw new Error("Invalid table name");
}
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.valueColumn)) {
throw new Error("Invalid column name");
}
const columns = formatColumns(input.selectColumns);
const partition = input.partitionBy
? `PARTITION BY ${input.partitionBy}`
: "";
let sql = `
SELECT ${columns},
SUM("${input.valueColumn}") OVER (${partition} ORDER BY ${input.orderBy} ROWS UNBOUNDED PRECEDING) as running_total
FROM "${input.table}"
`;
if (input.whereClause) {
validateWhereClause(input.whereClause);
sql += ` WHERE ${input.whereClause}`;
}
sql += ` LIMIT ${input.limit}`;
const result = await adapter.executeReadQuery(sql);
return {
success: true,
valueColumn: input.valueColumn,
rowCount: result.rows?.length ?? 0,
rows: result.rows,
};
},
};
}
/**
* Moving average
*/
function createMovingAverageTool(adapter: NativeSqliteAdapter): ToolDefinition {
return {
name: "sqlite_window_moving_avg",
description:
"Calculate moving (rolling) average. Useful for smoothing time series data.",
group: "stats",
inputSchema: MovingAverageSchema,
requiredScopes: ["read"],
handler: async (params: unknown, _context: RequestContext) => {
const input = MovingAverageSchema.parse(params);
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.table)) {
throw new Error("Invalid table name");
}
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.valueColumn)) {
throw new Error("Invalid column name");
}
const columns = formatColumns(input.selectColumns);
const partition = input.partitionBy
? `PARTITION BY ${input.partitionBy}`
: "";
const preceding = input.windowSize - 1;
let sql = `
SELECT ${columns},
AVG("${input.valueColumn}") OVER (${partition} ORDER BY ${input.orderBy} ROWS BETWEEN ${preceding} PRECEDING AND CURRENT ROW) as moving_avg
FROM "${input.table}"
`;
if (input.whereClause) {
validateWhereClause(input.whereClause);
sql += ` WHERE ${input.whereClause}`;
}
sql += ` LIMIT ${input.limit}`;
const result = await adapter.executeReadQuery(sql);
return {
success: true,
valueColumn: input.valueColumn,
windowSize: input.windowSize,
rowCount: result.rows?.length ?? 0,
rows: result.rows,
};
},
};
}
/**
* NTILE (divide into buckets/quantiles)
*/
function createNtileTool(adapter: NativeSqliteAdapter): ToolDefinition {
return {
name: "sqlite_window_ntile",
description:
"Divide rows into N buckets. E.g., 4 buckets = quartiles, 10 = deciles, 100 = percentiles.",
group: "stats",
inputSchema: NtileSchema,
requiredScopes: ["read"],
handler: async (params: unknown, _context: RequestContext) => {
const input = NtileSchema.parse(params);
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.table)) {
throw new Error("Invalid table name");
}
const columns = formatColumns(input.selectColumns);
const partition = input.partitionBy
? `PARTITION BY ${input.partitionBy}`
: "";
let sql = `
SELECT ${columns},
NTILE(${input.buckets}) OVER (${partition} ORDER BY ${input.orderBy}) as bucket
FROM "${input.table}"
`;
if (input.whereClause) {
validateWhereClause(input.whereClause);
sql += ` WHERE ${input.whereClause}`;
}
sql += ` LIMIT ${input.limit}`;
const result = await adapter.executeReadQuery(sql);
return {
success: true,
buckets: input.buckets,
rowCount: result.rows?.length ?? 0,
rows: result.rows,
};
},
};
}
