AG Studio is provider-agnostic - it does not bundle a connection to any LLM. The adapter is the seam between Studio and your provider. You implement the AgAiAssistant interface, which translates between Studio's request format and your chosen LLM.
The example below ships a complete OpenAI adapter. Copy it as a starting point and adapt it to your provider.
import {
createApp,
defineComponent,
onBeforeMount,
ref,
shallowRef,
} from "vue";
import { AgStudio } from "ag-studio-vue3";
import { openaiAdapter } from "./shared/openaiAdapter.ts";
import {
AgAiAssistant,
AgDataEngine,
AgDataSourcesDefinition,
AgReportState,
AgStudioAiModule,
AgStudioApi,
AgStudioApiReadyEvent,
AgStudioMode,
AgStudioModuleRegistry,
AgStudioProperties,
} from "ag-studio";
import { getMainDemoData } from "./data.ts";
AgStudioModuleRegistry.registerModules([AgStudioAiModule]);
export const AI_API_URL = "https://ai-api.ag-grid.com/api/openai/v1";
export const AI_API_TOKEN = "";
const VueExample = defineComponent({
template: `
<div style="height: 100%">
<div style="display: flex; flex-direction: column; height: 100%">
<ag-studio
style="width: 100%; height: 100%;"
class="my-studio-container"
@api-ready="onApiReady"
:data="data"
:mode="mode"
:initialState="initialState"
:ai="ai"></ag-studio>
</div>
</div>
`,
components: {
"ag-studio": AgStudio,
},
setup(props) {
const studioApi = shallowRef<AgStudioApi | null>(null);
const data = ref<AgDataSourcesDefinition | AgDataEngine>(
getMainDemoData("https://www.ag-grid.com/studio/example-assets"),
);
const mode = ref<AgStudioMode>("edit");
const initialState = ref<AgReportState>({
pages: [{ id: "main", widgets: {}, widgetLayout: {} }],
selectedPageId: "main",
panels: {
filters: { collapsed: true },
edit: { collapsed: true },
data: { collapsed: true },
},
});
const ai = ref<AgAiAssistant>(
openaiAdapter({
endpoint: AI_API_URL,
key: AI_API_TOKEN,
}),
);
const onApiReady = (params: AgStudioApiReadyEvent) => {
studioApi.value = params.api;
};
return {
studioApi,
data,
mode,
initialState,
ai,
onApiReady,
};
},
});
const app = createApp(VueExample);
app.mount("#app");
importScripts('https://cdn.jsdelivr.net/npm/typescript@5.4.5/lib/typescript.min.js');
self.addEventListener('install', () => self.skipWaiting());
self.addEventListener('activate', (event) => event.waitUntil(self.clients.claim()));
async function transpile(request, ext) {
const response = await fetch(request);
if (!response.ok) return response;
const source = await response.text();
const result = ts.transpileModule(source, {
compilerOptions: {
module: ts.ModuleKind.ESNext,
target: ts.ScriptTarget.ESNext,
jsx: ext.endsWith('x') ? ts.JsxEmit.React : undefined,
experimentalDecorators: ext === 'ts',
emitDecoratorMetadata: ext === 'ts',
},
});
return new Response(result.outputText, {
headers: { 'Content-Type': 'application/javascript' },
});
}
self.addEventListener('fetch', (event) => {
const url = new URL(event.request.url);
const ext = url.pathname
.match(/\.([a-z0-9]+)$/i)
?.at(1)
?.toLowerCase();
if (['jsx', 'ts', 'tsx'].includes(ext)) {
event.respondWith(transpile(event.request, ext));
}
});
import type {
AgCalendar,
AgDataSourcesDefinition,
AgExpressionFieldDefinition,
AgFieldDefinition,
AgRelationDefinition,
} from 'ag-studio';
import type { DemoData, DictColumn, EncodedTable } from './demoDataGenerator.ts';
import { encodeDemoData, generateDemoData, getDictVal } from './demoDataGenerator.ts';
// Internal duck-typed shape - the engine reads `nullMasks` / `statsHints`
// at the internal boundary even though the public response is just `{ data }`.
interface ColumnStatsHint {
nullCount?: number;
indexToValue?: string[];
}
interface ColumnsResponse {
data: ArrayLike<unknown>[];
nullMasks: Array<Uint8Array | null>;
statsHints: Array<ColumnStatsHint | null>;
}
// =============================================================================
// Field Definitions
// =============================================================================
export const storesFields: AgFieldDefinition[] = [
{
id: 'store_id',
name: 'Store ID',
format: 'textFormat',
cardinality: 'low',
notBlank: true,
},
{ id: 'store_name', name: 'Store', format: 'textFormat', cardinality: 'low', notBlank: true },
{ id: 'region', name: 'Region', format: 'textFormat', cardinality: 'low', notBlank: true },
{ id: 'city', name: 'City', format: 'textFormat', cardinality: 'low', notBlank: true },
{
id: 'opened_date',
name: 'Opened Date',
format: 'dateFormat',
cardinality: 'medium',
notBlank: true,
},
{ id: 'store_type', name: 'Store Type', format: 'textFormat', cardinality: 'low', notBlank: true },
];
export const productsFields: AgFieldDefinition[] = [
{
id: 'product_id',
name: 'Product ID',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
hide: false,
},
{
id: 'product_name',
name: 'Product',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
},
{ id: 'category', name: 'Category', format: 'textFormat', cardinality: 'low', notBlank: true },
{
id: 'subcategory',
name: 'Subcategory',
format: 'textFormat',
cardinality: 'medium',
notBlank: true,
},
{ id: 'brand', name: 'Brand', format: 'textFormat', cardinality: 'medium', notBlank: true },
{ id: 'launch_date', name: 'Launch Date', format: 'dateFormat', cardinality: 'high', notBlank: true },
{
id: 'list_price',
name: 'List Price',
format: 'currencyFormat',
cardinality: 'high',
notBlank: true,
formatOptions: {
format: 'ÂŁ#,##0.00',
},
},
{
id: 'unit_cost',
name: 'Unit Cost',
format: 'currencyFormat',
cardinality: 'high',
notBlank: true,
formatOptions: {
format: 'ÂŁ#,##0.00',
},
},
{
id: 'is_discontinued',
name: 'Discontinued',
format: 'booleanFormat',
cardinality: 'low',
notBlank: true,
},
];
export const customersFields: AgFieldDefinition[] = [
{
id: 'customer_id',
name: 'Customer ID',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
hide: false,
},
{
id: 'customer_name',
name: 'Customer',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
},
{ id: 'signup_date', name: 'Signup Date', format: 'dateFormat', cardinality: 'high', notBlank: true },
{ id: 'region', name: 'Region', format: 'textFormat', cardinality: 'low', notBlank: true },
{ id: 'segment', name: 'Segment', format: 'textFormat', cardinality: 'low', notBlank: true },
{ id: 'is_active', name: 'Active', format: 'booleanFormat', cardinality: 'low', notBlank: true },
{
id: 'marketing_opt_in',
name: 'Marketing Opt-in',
format: 'booleanFormat',
cardinality: 'low',
notBlank: true,
},
];
export const ordersFields: AgFieldDefinition[] = [
{
id: 'order_id',
name: 'Order ID',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
hide: false,
},
{
id: 'customer_id',
name: 'Customer ID',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
},
{
id: 'store_id',
name: 'Store ID',
format: 'textFormat',
cardinality: 'low',
notBlank: true,
},
{
id: 'order_datetime',
name: 'Order Date/Time',
format: 'dateTimeFormat',
cardinality: 'high',
notBlank: true,
},
{ id: 'channel', name: 'Channel', format: 'textFormat', cardinality: 'low', notBlank: true },
{ id: 'status', name: 'Status', format: 'textFormat', cardinality: 'low', notBlank: true },
{
id: 'payment_method',
name: 'Payment Method',
format: 'textFormat',
cardinality: 'low',
notBlank: true,
},
{
id: 'currency',
name: 'Currency',
format: 'textFormat',
cardinality: 'low',
notBlank: true,
hide: false,
},
{
id: 'promo_code',
name: 'Promo Code',
format: 'textFormat',
cardinality: 'medium',
notBlank: false,
hide: false,
},
{ id: 'notes', name: 'Notes', format: 'textFormat', cardinality: 'high', notBlank: false, hide: false },
];
export const orderItemsFields: AgFieldDefinition[] = [
{
id: 'order_item_id',
name: 'Order Item ID',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
},
{
id: 'order_id',
name: 'Order ID',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
},
{
id: 'product_id',
name: 'Product ID',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
},
{
id: 'quantity',
name: 'Qty',
format: 'integerFormat',
cardinality: 'medium',
notBlank: true,
formatOptions: { format: '#,##0' },
},
{
id: 'unit_price',
name: 'Unit Price',
format: 'currencyFormat',
cardinality: 'high',
notBlank: true,
formatOptions: {
format: 'ÂŁ#,##0.00',
},
},
{
id: 'discount_pct',
name: 'Discount',
format: 'percentageFormat',
cardinality: 'medium',
notBlank: true,
formatOptions: { format: '#,##0.0%' },
},
{
id: 'tax_rate',
name: 'Tax Rate',
format: 'percentageFormat',
cardinality: 'low',
notBlank: true,
formatOptions: { format: '#,##0%' },
},
{ id: 'returned', name: 'Returned', format: 'booleanFormat', cardinality: 'low', notBlank: true },
{
id: 'return_reason',
name: 'Return Reason',
format: 'textFormat',
cardinality: 'low',
notBlank: false,
},
];
export const shipmentsFields: AgFieldDefinition[] = [
{
id: 'shipment_id',
name: 'Shipment ID',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
},
{
id: 'order_id',
name: 'Order ID',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
},
{
id: 'ship_datetime',
name: 'Shipped Date/Time',
format: 'dateTimeFormat',
cardinality: 'high',
notBlank: false,
},
{
id: 'delivery_datetime',
name: 'Delivered Date/Time',
format: 'dateTimeFormat',
cardinality: 'high',
notBlank: false,
},
{ id: 'carrier', name: 'Carrier', format: 'textFormat', cardinality: 'low', notBlank: true },
{ id: 'delayed', name: 'Delayed', format: 'booleanFormat', cardinality: 'low', notBlank: false },
];
const subcategoriesFields: AgFieldDefinition[] = [
{
id: 'subcategory',
name: 'Subcategory',
format: 'textFormat',
cardinality: 'low',
notBlank: true,
},
];
const returnCostsFields: AgFieldDefinition[] = [
{
id: 'period',
name: 'Period',
format: 'dateFormat',
cardinality: 'medium',
notBlank: true,
},
{
id: 'subcategory',
name: 'Subcategory',
format: 'textFormat',
cardinality: 'low',
notBlank: true,
},
{
id: 'return_reason',
name: 'Return Reason',
format: 'textFormat',
cardinality: 'low',
notBlank: true,
},
{
id: 'refunds',
name: 'Refunds',
format: 'currencyFormat',
cardinality: 'medium',
notBlank: true,
formatOptions: { format: 'ÂŁ#,##0.0,K' },
},
{
id: 'shipping',
name: 'Shipping',
format: 'currencyFormat',
cardinality: 'medium',
notBlank: true,
formatOptions: { format: 'ÂŁ#,##0.0,K' },
},
{
id: 'write_offs',
name: 'Write-offs',
format: 'currencyFormat',
cardinality: 'medium',
notBlank: true,
formatOptions: { format: 'ÂŁ#,##0.0,K' },
},
];
// =============================================================================
// JSON Loading & Per-Source Caches
// =============================================================================
async function loadJson(baseUrl: string, filename: string): Promise<any[]> {
const url = `${baseUrl}/${filename}`;
const response = await fetch(url);
if (!response.ok) {
console.error(`Failed to load ${filename}: ${response.status}`);
return [];
}
const data = await response.json();
return data;
}
// =============================================================================
// Cached Loaders
// =============================================================================
// Each loader caches its promise so the file is fetched and parsed at most once.
let storesCache: Promise<any[]> | null = null;
const getStores = (baseUrl: string) => (storesCache ??= loadJson(baseUrl, 'stores.json'));
let productsCache: Promise<any[]> | null = null;
const getProducts = (baseUrl: string) => (productsCache ??= loadJson(baseUrl, 'products.json'));
let customersCache: Promise<any[]> | null = null;
const getCustomers = (baseUrl: string) => (customersCache ??= loadJson(baseUrl, 'customers.json'));
let ordersCache: Promise<any[]> | null = null;
const getOrders = (baseUrl: string) => (ordersCache ??= loadJson(baseUrl, 'orders.json'));
let orderItemsCache: Promise<any[]> | null = null;
const getOrderItems = (baseUrl: string) => (orderItemsCache ??= loadJson(baseUrl, 'order_items.json'));
let shipmentsCache: Promise<any[]> | null = null;
const getShipments = (baseUrl: string) => (shipmentsCache ??= loadJson(baseUrl, 'shipments.json'));
// =============================================================================
// Expressions (Calculated Columns & Measures)
// =============================================================================
export const expressions: AgExpressionFieldDefinition[] = [
// -------------------------------------------------------------------------
// Pre-aggregation expressions (row-level calculations on order_items)
// -------------------------------------------------------------------------
// line_gross = quantity * unit_price
{
id: 'line_gross',
isMeasure: false,
name: 'Line Gross',
hide: false,
format: 'currencyFormat',
formatOptions: {
format: 'ÂŁ#,##0.00',
},
expression: {
operator: 'multiply',
inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }],
},
},
// line_discount_amount = line_gross * discount_pct
{
id: 'line_discount_amount',
isMeasure: false,
name: 'Discount Amount',
hide: false,
format: 'currencyFormat',
formatOptions: {
format: 'ÂŁ#,##0.00',
},
expression: {
operator: 'multiply',
inputs: [
{ operator: 'multiply', inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }] },
{ id: 'order_items.discount_pct' },
],
},
},
// line_net = line_gross - line_discount_amount
{
id: 'line_net',
isMeasure: false,
name: 'Line Net',
hide: false,
format: 'currencyFormat',
formatOptions: {
format: 'ÂŁ#,##0.00',
},
expression: {
operator: 'subtract',
inputs: [
{ operator: 'multiply', inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }] },
{
operator: 'multiply',
inputs: [
{
operator: 'multiply',
inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }],
},
{ id: 'order_items.discount_pct' },
],
},
],
},
},
// line_cogs = quantity * unit_cost (from products via join)
{
id: 'line_cogs',
isMeasure: false,
name: 'Line COGS',
hide: false,
format: 'currencyFormat',
formatOptions: {
format: 'ÂŁ#,##0.00',
},
expression: {
operator: 'multiply',
inputs: [{ id: 'order_items.quantity' }, { id: 'products.unit_cost' }],
},
},
// line_margin = line_net - line_cogs
{
id: 'line_margin',
isMeasure: false,
name: 'Line Margin',
hide: false,
format: 'currencyFormat',
formatOptions: {
format: 'ÂŁ#,##0.00',
},
expression: {
operator: 'subtract',
inputs: [
// line_net
{
operator: 'subtract',
inputs: [
{
operator: 'multiply',
inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }],
},
{
operator: 'multiply',
inputs: [
{
operator: 'multiply',
inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }],
},
{ id: 'order_items.discount_pct' },
],
},
],
},
// line_cogs
{ operator: 'multiply', inputs: [{ id: 'order_items.quantity' }, { id: 'products.unit_cost' }] },
],
},
},
// return_flag = returned IS TRUE (for filtering/counting)
{
id: 'return_flag',
isMeasure: false,
name: 'Return Flag',
hide: true,
expression: {
operator: 'isTrue',
inputs: [{ id: 'order_items.returned' }],
},
},
// returned_line_flag = IF(return_flag, 1, 0) - numeric flag for summing
{
id: 'returned_line_flag',
isMeasure: false,
name: 'Returned Line Flag',
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'return_flag' }, { type: 'number', value: 1 }, { type: 'number', value: 0 }],
},
},
// returned_line_net = IF(return_flag, line_net, 0) - line net only for returned items
{
id: 'returned_line_net',
isMeasure: false,
name: 'Returned Line Net',
hide: true,
format: 'currencyFormat',
formatOptions: {
format: 'ÂŁ#,##0.00',
},
expression: {
operator: 'if',
inputs: [{ id: 'return_flag' }, { id: 'line_net' }, { type: 'number', value: 0 }],
},
},
// returned_line_margin = IF(return_flag, line_margin, 0) - margin only for returned items
{
id: 'returned_line_margin',
isMeasure: false,
name: 'Returned Line Margin',
hide: true,
format: 'currencyFormat',
formatOptions: {
format: 'ÂŁ#,##0.00',
},
expression: {
operator: 'if',
inputs: [{ id: 'return_flag' }, { id: 'line_margin' }, { type: 'number', value: 0 }],
},
},
// returned_line_cogs = IF(return_flag, line_cogs, 0)
{
id: 'returned_line_cogs',
isMeasure: false,
name: 'Returned Line COGS',
hide: true,
format: 'currencyFormat',
formatOptions: { format: 'ÂŁ#,##0.00' },
expression: {
operator: 'if',
inputs: [{ id: 'return_flag' }, { id: 'line_cogs' }, { type: 'number', value: 0 }],
},
},
// return_refunds = returned_line_net * 0.45
{
id: 'return_refunds',
isMeasure: false,
name: 'Refunds',
hide: true,
format: 'currencyFormat',
formatOptions: { format: 'ÂŁ#,##0.0,K' },
expression: {
operator: 'multiply',
inputs: [{ id: 'returned_line_net' }, { type: 'number', value: 0.45 }],
},
},
// return_shipping = returned_line_margin * 0.65
{
id: 'return_shipping',
isMeasure: false,
name: 'Shipping',
hide: true,
format: 'currencyFormat',
formatOptions: { format: 'ÂŁ#,##0.0,K' },
expression: {
operator: 'multiply',
inputs: [{ id: 'returned_line_margin' }, { type: 'number', value: 0.65 }],
},
},
// return_write_offs = returned_line_cogs * 0.35
{
id: 'return_write_offs',
isMeasure: false,
name: 'Write-offs',
hide: true,
format: 'currencyFormat',
formatOptions: { format: 'ÂŁ#,##0.0,K' },
expression: {
operator: 'multiply',
inputs: [{ id: 'returned_line_cogs' }, { type: 'number', value: 0.35 }],
},
},
// -------------------------------------------------------------------------
// Post-aggregation expressions (measures for KPIs)
// For now, use pre-agg expressions with aggregation in widgets directly
// -------------------------------------------------------------------------
// Gross Margin % = (SUM(line_net) - SUM(line_cogs)) / SUM(line_net)
{
id: 'gross_margin_pct',
isMeasure: true,
name: 'Gross Margin %',
hide: false,
expression: {
operator: 'divide',
inputs: [
{
operator: 'subtract',
inputs: [
{ id: 'line_net', aggregation: 'sum' },
{ id: 'line_cogs', aggregation: 'sum' },
],
},
{ id: 'line_net', aggregation: 'sum' },
],
},
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
// order_status_group = IF(status = "Processing", "Open", "Closed")
{
id: 'order_status_group',
isMeasure: false,
name: 'Status Group',
expression: {
operator: 'if',
inputs: [
{ operator: 'equals', inputs: [{ id: 'orders.status' }, { type: 'string', value: 'Processing' }] },
{ type: 'string', value: 'Open' },
{ type: 'string', value: 'Closed' },
],
},
},
// is_closed = status IN ("Completed", "Returned", "Cancelled") via chained OR
{
id: 'is_closed',
isMeasure: false,
name: 'Is Closed',
expression: {
operator: 'or',
inputs: [
{ operator: 'equals', inputs: [{ id: 'orders.status' }, { type: 'string', value: 'Completed' }] },
{
operator: 'or',
inputs: [
{
operator: 'equals',
inputs: [{ id: 'orders.status' }, { type: 'string', value: 'Returned' }],
},
{
operator: 'equals',
inputs: [{ id: 'orders.status' }, { type: 'string', value: 'Cancelled' }],
},
],
},
],
},
},
// ship_to_delivery_days = DATEDIFF(day, ship_datetime, delivery_datetime)
{
id: 'ship_to_delivery_days',
isMeasure: false,
name: 'Ship to Delivery Days',
expression: {
operator: 'datediff',
inputs: [
{ type: 'string', value: 'day' },
{ id: 'shipments.ship_datetime' },
{ id: 'shipments.delivery_datetime' },
],
},
},
// order_to_ship_hours = DATEDIFF(hour, order_datetime, ship_datetime)
{
id: 'order_to_ship_hours',
isMeasure: false,
name: 'Order to Ship Hours',
expression: {
operator: 'datediff',
inputs: [
{ type: 'string', value: 'hour' },
{ id: 'orders.order_datetime' },
{ id: 'shipments.ship_datetime' },
],
},
},
// is_shipped = ship_datetime IS NOT NULL
{
id: 'is_shipped',
isMeasure: false,
name: 'Is Shipped',
hide: true,
expression: {
operator: 'isNotNull',
inputs: [{ id: 'shipments.ship_datetime' }],
},
},
// is_delivered = delivery_datetime IS NOT NULL
{
id: 'is_delivered',
isMeasure: false,
name: 'Is Delivered',
hide: true,
expression: {
operator: 'isNotNull',
inputs: [{ id: 'shipments.delivery_datetime' }],
},
},
// is_on_time = IF(is_delivered, delayed = FALSE, FALSE)
// Use IF+EQUALS to guarantee a boolean result.
{
id: 'is_on_time',
isMeasure: false,
name: 'Is On Time',
hide: true,
expression: {
operator: 'if',
inputs: [
{ id: 'is_delivered' },
{ operator: 'equals', inputs: [{ id: 'shipments.delayed' }, { type: 'boolean', value: false }] },
{ type: 'boolean', value: false },
],
},
},
// is_delayed = IF(is_delivered, delayed = TRUE, FALSE)
// Use IF+EQUALS to guarantee a boolean result.
{
id: 'is_delayed',
isMeasure: false,
name: 'Is Delayed',
hide: true,
expression: {
operator: 'if',
inputs: [
{ id: 'is_delivered' },
{ operator: 'equals', inputs: [{ id: 'shipments.delayed' }, { type: 'boolean', value: true }] },
{ type: 'boolean', value: false },
],
},
},
// delayed_shipments = IF(is_delayed, 1, 0) - numeric flag for stacking
{
id: 'delayed_shipments',
isMeasure: false,
name: 'Delayed Shipments',
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_delayed' }, { type: 'number', value: 1 }, { type: 'number', value: 0 }],
},
},
// on_time_shipments = IF(is_on_time, 1, 0) - numeric flag for stacking
{
id: 'on_time_shipments',
isMeasure: false,
name: 'On-time Shipments',
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_on_time' }, { type: 'number', value: 1 }, { type: 'number', value: 0 }],
},
},
// delivered_shipments = IF(is_delivered, 1, 0) - numeric flag for rate denominators
{
id: 'delivered_shipments',
isMeasure: false,
name: 'Delivered Shipments',
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_delivered' }, { type: 'number', value: 1 }, { type: 'number', value: 0 }],
},
},
// delay_rate = SUM(delayed_shipments) / SUM(delivered_shipments)
// Use delivered shipments as the denominator so pending (not delivered) rows don't dilute the rate.
{
id: 'delay_rate',
isMeasure: true,
name: 'Delay Rate',
hide: false,
expression: {
operator: 'divide',
inputs: [
{ id: 'delayed_shipments', aggregation: 'sum' },
{ id: 'delivered_shipments', aggregation: 'sum' },
],
},
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
// shipped_order_id = IF(is_shipped, order_id, NULL)
{
id: 'shipped_order_id',
isMeasure: false,
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_shipped' }, { id: 'shipments.order_id' }, { type: 'string', value: null }],
},
},
// on_time_order_id = IF(is_on_time, order_id, NULL)
{
id: 'on_time_order_id',
isMeasure: false,
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_on_time' }, { id: 'shipments.order_id' }, { type: 'string', value: null }],
},
},
// delivered_order_id = IF(is_delivered, order_id, NULL)
{
id: 'delivered_order_id',
isMeasure: false,
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_delivered' }, { id: 'shipments.order_id' }, { type: 'string', value: null }],
},
},
// delivery_status = IF(NOT is_shipped, "Not shipped", IF(delayed = true, "Delayed", "On time"))
{
id: 'delivery_status',
isMeasure: false,
name: 'Delivery Status',
expression: {
operator: 'if',
inputs: [
{ operator: 'not', inputs: [{ id: 'is_shipped' }] },
{ type: 'string', value: 'Not shipped' },
{
operator: 'if',
inputs: [
{ id: 'shipments.delayed' },
{ type: 'string', value: 'Delayed' },
{ type: 'string', value: 'On time' },
],
},
],
},
},
// returned_order_id = IF(return_flag, order_id, NULL)
// Used for counting orders that have at least one returned line.
{
id: 'returned_order_id',
isMeasure: false,
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'return_flag' }, { id: 'order_items.order_id' }, { type: 'string', value: null }],
},
},
// Aggregated calculations for KPIs (using pre-aggregation expressions as inputs)
{
id: 'net_sales',
isMeasure: true,
name: 'Net Sales',
expression: {
id: 'line_net',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: 'ÂŁ#,##0.0,,\\M',
},
},
{
id: 'gross_sales',
isMeasure: true,
name: 'Gross Sales',
expression: {
id: 'line_gross',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: 'ÂŁ#,##0.0,,\\M',
},
},
{
id: 'discount_amount',
isMeasure: true,
name: 'Discount Amount',
expression: {
id: 'line_discount_amount',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: 'ÂŁ#,##0.0,K',
},
},
{
id: 'COGS',
isMeasure: true,
name: 'COGS',
expression: {
id: 'line_cogs',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: 'ÂŁ#,##0.0,,\\M',
},
},
{
id: 'gross_margin',
isMeasure: true,
name: 'Gross Margin',
expression: {
id: 'line_margin',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: 'ÂŁ#,##0.0,,\\M',
},
},
{
id: 'gross_margin_percentage',
isMeasure: true,
name: 'Gross Margin %',
expression: {
operator: 'divide',
inputs: [{ id: 'gross_margin' }, { id: 'net_sales' }],
},
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
// --- Orders ---
{
id: 'order_count',
isMeasure: true,
name: 'Order Count',
expression: {
id: 'orders.order_id',
aggregation: 'countd',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0,K',
},
},
{
id: 'average_order_value',
isMeasure: true,
name: 'Average Order Value',
expression: {
operator: 'divide',
inputs: [{ id: 'net_sales' }, { id: 'order_count' }],
},
format: 'currencyFormat',
formatOptions: {
format: 'ÂŁ#,##0.00',
},
},
{
id: 'active_customers',
isMeasure: true,
name: 'Active Customers',
expression: {
id: 'orders.customer_id',
aggregation: 'countd',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0,K',
},
},
// --- Returns ---
{
id: 'returned_lines',
isMeasure: true,
name: 'Returned Lines',
expression: {
id: 'returned_line_flag',
aggregation: 'sum',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0',
},
},
{
id: 'returned_orders',
isMeasure: true,
name: 'Returned Orders',
expression: {
id: 'returned_order_id',
aggregation: 'countd',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0',
},
},
{
id: 'return_rate',
isMeasure: true,
name: 'Return Rate (Lines)',
expression: {
operator: 'divide',
inputs: [{ id: 'returned_lines' }, { id: 'order_items.order_item_id', aggregation: 'count' }],
},
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
{
id: 'return_value',
isMeasure: true,
name: 'Return Value',
expression: {
id: 'returned_line_net',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: 'ÂŁ#,##0.0,K',
},
},
// return_margin_impact = -SUM(returned_line_margin)
// Display as a negative number to represent margin lost due to returns.
{
id: 'return_margin_impact',
isMeasure: true,
name: 'Return Margin Impact',
expression: {
operator: 'subtract',
inputs: [
{ type: 'number', value: 0 },
{ id: 'returned_line_margin', aggregation: 'sum' },
],
},
format: 'currencyFormat',
formatOptions: {
format: 'ÂŁ#,##0.0,K',
},
},
// --- Delivery ---
{
id: 'shipped_orders',
isMeasure: true,
name: 'Shipped Orders',
expression: {
id: 'shipped_order_id',
aggregation: 'countd',
},
format: 'integerFormat',
},
{
id: 'delivered_orders',
isMeasure: true,
name: 'Delivered Orders',
expression: {
id: 'delivered_order_id',
aggregation: 'countd',
},
format: 'integerFormat',
},
{
id: 'delivered_not_returned_orders',
isMeasure: true,
name: 'Delivered (Not Returned)',
expression: {
operator: 'subtract',
inputs: [{ id: 'delivered_orders' }, { id: 'returned_orders' }],
},
format: 'integerFormat',
formatOptions: {
format: '#,##0,K',
},
},
{
id: 'on_time_deliveries',
isMeasure: true,
name: 'On-time Deliveries',
expression: {
id: 'on_time_order_id',
aggregation: 'countd',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0,K',
},
},
{
id: 'on_time_rate',
isMeasure: true,
name: 'On-time Rate',
expression: {
operator: 'divide',
inputs: [{ id: 'on_time_deliveries' }, { id: 'delivered_orders' }],
},
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
{
id: 'avg_ship_to_delivery_days',
isMeasure: true,
name: 'Avg Ship to Delivery Days',
expression: {
id: 'ship_to_delivery_days',
aggregation: 'avg',
},
format: 'decimalFormat',
formatOptions: { format: '#,##0.0' },
},
];
// =============================================================================
// Computed Table Data (shared cache across all four pre-aggregated tables)
// =============================================================================
interface ComputedTableData {
returnCosts: any[];
}
let computedDataPromise: Promise<ComputedTableData> | null = null;
function getComputedData(baseUrl: string): Promise<ComputedTableData> {
return (computedDataPromise ??= buildComputedData(baseUrl));
}
async function buildComputedData(baseUrl: string): Promise<ComputedTableData> {
const [orders, orderItems, products] = await Promise.all([
getOrders(baseUrl),
getOrderItems(baseUrl),
getProducts(baseUrl),
]);
const toMonthIsoStr = (raw: any): string | null => {
const d = raw instanceof Date ? raw : raw != null && raw !== '' ? new Date(raw) : null;
if (!d || Number.isNaN(d.getTime())) return null;
const year = d.getUTCFullYear();
const month1 = d.getUTCMonth() + 1;
return `${year}-${String(month1).padStart(2, '0')}`;
};
// Order -> Month ISO string ("YYYY-MM")
const orderMonthById = new Map<string, string>();
for (const o of orders) {
const month = toMonthIsoStr((o as any).order_datetime);
if (month != null) {
orderMonthById.set(String((o as any).order_id), month);
}
}
// Helper to compute line-net consistent with existing expressions
const computeLineNet = (oi: any): number => {
const qty = Number(oi.quantity ?? 0);
const unitPrice = Number(oi.unit_price ?? 0);
const discountPct = Number(oi.discount_pct ?? 0);
const gross = qty * unitPrice;
return gross - gross * discountPct;
};
// Product -> unit_cost index (for margin)
const unitCostByProductId = new Map<string, number>();
for (const p of products) {
unitCostByProductId.set(String((p as any).product_id), Number((p as any).unit_cost ?? 0));
}
const computeLineCogs = (oi: any): number => {
const qty = Number(oi.quantity ?? 0);
const unitCost = unitCostByProductId.get(String(oi.product_id)) ?? 0;
return qty * unitCost;
};
// --- Return costs at month Ă subcategory granularity ---
const productSubcategory = new Map<string, string>();
for (const p of products) {
productSubcategory.set(String((p as any).product_id), String((p as any).subcategory));
}
// Accumulate net/cogs per monthĂsubcategoryĂreturn_reason triple
const cellNet = new Map<string, number>();
const cellCogs = new Map<string, number>();
for (const oi of orderItems) {
if ((oi as any).returned !== true) continue;
const month = orderMonthById.get(String((oi as any).order_id));
if (month == null) continue;
const sub = productSubcategory.get(String((oi as any).product_id));
if (!sub) continue;
const reason = String((oi as any).return_reason ?? '');
if (!reason) continue;
const lineNet = computeLineNet(oi);
const lineCogs = computeLineCogs(oi);
const key = `${month}|${sub}|${reason}`;
cellNet.set(key, (cellNet.get(key) ?? 0) + lineNet);
cellCogs.set(key, (cellCogs.get(key) ?? 0) + lineCogs);
}
const allSubs = new Set<string>();
const allReasons = new Set<string>();
for (const key of cellNet.keys()) {
const parts = key.split('|');
allSubs.add(parts[1]);
allReasons.add(parts[2]);
}
// Build rows for real months
const realMonthStrs = Array.from(new Set(Array.from(cellNet.keys()).map((k) => k.split('|')[0]))).sort();
const realRows: ReturnCostsRow[] = [];
for (const monthStr of realMonthStrs) {
for (const sub of allSubs) {
for (const reason of allReasons) {
const key = `${monthStr}|${sub}|${reason}`;
const net = cellNet.get(key) ?? 0;
const cogs = cellCogs.get(key) ?? 0;
if (net === 0 && cogs === 0) continue;
const margin = net - cogs;
realRows.push({
period: `${monthStr}-01`,
subcategory: sub,
return_reason: reason,
refunds: Math.round(net * 0.45),
shipping: Math.round(margin * 0.65),
write_offs: Math.round(cogs * 0.35),
});
}
}
}
return { returnCosts: realRows };
}
// =============================================================================
// Relationships
// =============================================================================
/** Page tabs for the report-builder studio state; ids match the pages in mainDemoState. */
export const mainDemoReports: { id: string; name: string }[] = [
{ id: 'executive', name: 'Executive Overview' },
{ id: 'sales-margin', name: 'Sales & Margin' },
{ id: 'fulfilment', name: 'Fulfilment & Delivery' },
{ id: 'returns', name: 'Returns & Quality' },
];
export const mainDemoCalendar: AgCalendar = {
id: 'calendar',
label: 'Calendar',
range: {
from: { literal: '2021-01-01' },
to: { operator: 'currentDate', inputs: [] },
},
fragments: [
'year',
'quarter',
{ unit: 'month', format: 'MMM yyyy' },
'week',
'day',
'monthOfYear',
'dayOfWeek',
'hour',
'minute',
],
};
export const relationships: AgRelationDefinition[] = [
{
id: 'orders-customers',
source: { tableId: 'orders', fieldId: 'customer_id' },
target: { tableId: 'customers', fieldId: 'customer_id' },
type: 'many-to-one',
},
{
id: 'orders-stores',
source: { tableId: 'orders', fieldId: 'store_id' },
target: { tableId: 'stores', fieldId: 'store_id' },
type: 'many-to-one',
},
{
id: 'order_items-orders',
source: { tableId: 'order_items', fieldId: 'order_id' },
target: { tableId: 'orders', fieldId: 'order_id' },
type: 'many-to-one',
},
{
id: 'order_items-products',
source: { tableId: 'order_items', fieldId: 'product_id' },
target: { tableId: 'products', fieldId: 'product_id' },
type: 'many-to-one',
},
{
id: 'shipments-orders',
source: { tableId: 'shipments', fieldId: 'order_id' },
target: { tableId: 'orders', fieldId: 'order_id' },
type: 'many-to-one',
},
{
// subcategories is a real dimension (a fixed, shared vocabulary - see SUBCATS in
// demoDataGenerator.ts) - products and return_costs each relate to it many-to-one,
// rather than joining directly to each other on a raw duplicated string value as
// many-to-many. return_costs is pre-aggregated to (month, subcategory, return_reason);
// grouping order/order-item-level measures by a return_costs field still genuinely
// fans out through this snowflake (many-to-one, many-to-one, then one-to-many back out
// to return_costs), but that's now a plain one-to-many the existing fan-out detector
// (fanoutDetector.ts) already reports correctly, rather than an ambiguous many-to-many
// needing a schema-level acceptFanout override.
id: 'products-subcategories',
source: { tableId: 'products', fieldId: 'subcategory' },
target: { tableId: 'subcategories', fieldId: 'subcategory' },
type: 'many-to-one',
},
{
id: 'return_costs-subcategories',
source: { tableId: 'return_costs', fieldId: 'subcategory' },
target: { tableId: 'subcategories', fieldId: 'subcategory' },
type: 'many-to-one',
},
// Calendar bindings - fact date columns bound to the calendar
{
id: 'orders-calendar',
source: { tableId: 'orders', fieldId: 'order_datetime' },
target: { calendarId: 'calendar' },
truncate: 'day',
},
{
id: 'shipments-calendar-ship',
source: { tableId: 'shipments', fieldId: 'ship_datetime' },
target: { calendarId: 'calendar' },
truncate: 'day',
},
{
id: 'return_costs-calendar',
source: { tableId: 'return_costs', fieldId: 'period' },
target: { calendarId: 'calendar' },
},
];
// =============================================================================
// Data Source Definition
// =============================================================================
export function getMainDemoData(baseUrl: string): AgDataSourcesDefinition {
const url = `${baseUrl}/main-demo`;
return {
sources: [
{
id: 'stores',
name: 'Stores',
dataShape: 'row',
tables: [{ id: 'stores', name: 'Stores', fields: storesFields }],
getData: async () => ({ data: await getStores(url) }),
},
{
id: 'products',
name: 'Products',
dataShape: 'row',
tables: [{ id: 'products', name: 'Products', fields: productsFields }],
getData: async () => ({ data: await getProducts(url) }),
},
{
id: 'customers',
name: 'Customers',
dataShape: 'row',
tables: [{ id: 'customers', name: 'Customers', fields: customersFields }],
getData: async () => ({ data: await getCustomers(url) }),
},
{
id: 'orders',
name: 'Orders',
dataShape: 'row',
tables: [{ id: 'orders', name: 'Orders', fields: ordersFields }],
getData: async () => ({ data: await getOrders(url) }),
},
{
id: 'order_items',
name: 'Order Items',
dataShape: 'row',
tables: [{ id: 'order_items', name: 'Order Items', fields: orderItemsFields }],
getData: async () => ({ data: await getOrderItems(url) }),
},
{
id: 'shipments',
name: 'Shipments',
dataShape: 'row',
tables: [{ id: 'shipments', name: 'Shipments', fields: shipmentsFields }],
getData: async () => ({ data: await getShipments(url) }),
},
{
id: 'return_costs',
name: 'Return Costs',
dataShape: 'row',
tables: [{ id: 'return_costs', name: 'Return Costs', fields: returnCostsFields }],
getData: async () => {
const computed = await getComputedData(url);
return { data: computed.returnCosts };
},
},
{
id: 'subcategories',
name: 'Subcategories',
dataShape: 'row',
tables: [{ id: 'subcategories', name: 'Subcategories', fields: subcategoriesFields }],
getData: async () => ({
data: subcategoriesFromValues((await getProducts(url)).map((p) => p.subcategory)),
}),
},
],
relationships,
expressions,
calendars: [mainDemoCalendar],
};
}
// =============================================================================
// Generated Data Source - uses demoDataGenerator (sf controlled by URL/agStudioOpts)
// =============================================================================
interface ReturnCostsRow {
period: string;
subcategory: string;
return_reason: string;
refunds: number;
shipping: number;
write_offs: number;
}
interface SubcategoryRow {
subcategory: string;
}
/** The distinct subcategory values in `subcategoryValues`, one row each - the `subcategories` dimension table. */
function subcategoriesFromValues(subcategoryValues: Iterable<string>): SubcategoryRow[] {
return Array.from(new Set(subcategoryValues), (subcategory) => ({ subcategory }));
}
function buildPrecomputed(data: DemoData): { return_costs: ReturnCostsRow[] } {
const { orders, order_items, products } = data;
const nOrders = orders.order_id.indices.length;
const orderMonthByIdx: string[] = new Array(nOrders);
for (let i = 0; i < nOrders; ++i) {
orderMonthByIdx[i] = getDictVal(orders.order_month, i);
}
const unitCostById = new Map<string, number>();
const subcategoryById = new Map<string, string>();
for (let i = 0; i < products.product_id.length; ++i) {
unitCostById.set(products.product_id[i] as string, products.unit_cost[i] as number);
subcategoryById.set(products.product_id[i] as string, products.subcategory[i] as string);
}
// Accumulate net/cogs per monthĂsubcategoryĂreturn_reason triple
const cellNet = new Map<string, number>();
const cellCogs = new Map<string, number>();
for (let i = 0; i < order_items.returned.length; ++i) {
if (!order_items.returned[i]) continue;
const gross = order_items.quantity[i] * order_items.unit_price[i];
const net = gross - gross * order_items.discount_pct[i];
const productId = getDictVal(order_items.product_id, i);
const cogs = order_items.quantity[i] * (unitCostById.get(productId) ?? 0);
const reason = getDictVal(order_items.return_reason, i);
const orderIdx = order_items.order_id.indices[i];
const monthStr = orderMonthByIdx[orderIdx];
const sub = subcategoryById.get(productId);
if (!monthStr || !sub || !reason) continue;
const key = `${monthStr}|${sub}|${reason}`;
cellNet.set(key, (cellNet.get(key) ?? 0) + net);
cellCogs.set(key, (cellCogs.get(key) ?? 0) + cogs);
}
const allSubs = new Set<string>();
const allReasons = new Set<string>();
for (const key of cellNet.keys()) {
const parts = key.split('|');
allSubs.add(parts[1]);
allReasons.add(parts[2]);
}
// Build rows for real months
const realRows: ReturnCostsRow[] = [];
const realMonthStrs = Array.from(new Set(Array.from(cellNet.keys(), (k) => k.split('|')[0]))).sort();
for (const monthStr of realMonthStrs) {
for (const sub of allSubs) {
for (const reason of allReasons) {
const key = `${monthStr}|${sub}|${reason}`;
const net = cellNet.get(key) ?? 0;
const cogs = cellCogs.get(key) ?? 0;
if (net === 0 && cogs === 0) continue;
const margin = net - cogs;
realRows.push({
period: `${monthStr}-01`,
subcategory: sub,
return_reason: reason,
refunds: Math.round(net * 0.45),
shipping: Math.round(margin * 0.65),
write_offs: Math.round(cogs * 0.35),
});
}
}
}
return { return_costs: realRows };
}
function isDictColumn(col: unknown): col is DictColumn {
return col != null && typeof col === 'object' && 'indices' in col && 'indexToValue' in col;
}
function colsFromStruct(
struct: EncodedTable | Record<string, ArrayLike<unknown>>,
fieldIds: string[]
): ColumnsResponse {
const data: ArrayLike<unknown>[] = [];
const nullMasks: Array<Uint8Array | null> = [];
const statsHints: Array<ColumnStatsHint | null> = [];
for (const id of fieldIds) {
const col = (struct as Record<string, unknown>)[id] ?? [];
if (isDictColumn(col)) {
data.push(col.indices);
nullMasks.push(null);
statsHints.push({ indexToValue: col.indexToValue, nullCount: col.nullCount });
} else {
const arr = col as ArrayLike<unknown>;
data.push(arr);
if (arr instanceof Float64Array) {
let nullCount = 0;
for (let i = 0, len = arr.length; i < len; ++i) {
if (arr[i] !== arr[i]) ++nullCount;
}
if (nullCount > 0) {
const bitmap = new Uint8Array(Math.ceil(arr.length / 8));
bitmap.fill(0xff);
for (let i = 0, len = arr.length; i < len; ++i) {
if (arr[i] !== arr[i]) bitmap[i >> 3] &= ~(1 << (i & 7));
}
nullMasks.push(bitmap);
statsHints.push({ nullCount });
} else {
nullMasks.push(null);
statsHints.push({ nullCount: 0 });
}
} else {
nullMasks.push(null);
statsHints.push(null);
}
}
}
return { data, nullMasks, statsHints };
}
export function getMainDemoDataGenerated(
createRng?: (seed: string) => () => number,
seed?: string,
sf?: number
): AgDataSourcesDefinition {
const traceEnabled = ((globalThis as any).agStudioDebug ?? []).includes('traceMarkers');
const mark = traceEnabled ? (label: string) => performance.mark(`ag:${label}`) : (_label: string) => {};
mark('data-gen-start');
const data = generateDemoData(createRng, seed, sf);
mark('encode-start');
const encoded = encodeDemoData(data);
mark('precompute-start');
const precomputed = buildPrecomputed(data);
mark('data-ready');
return {
sources: [
{
id: 'stores',
name: 'Stores',
dataShape: 'column',
tables: [{ id: 'stores', name: 'Stores', fields: storesFields }],
getData: (_t, fieldIds) => Promise.resolve(colsFromStruct(encoded.stores, fieldIds)),
},
{
id: 'products',
name: 'Products',
dataShape: 'column',
tables: [{ id: 'products', name: 'Products', fields: productsFields }],
getData: (_t, fieldIds) => Promise.resolve(colsFromStruct(encoded.products, fieldIds)),
},
{
id: 'customers',
name: 'Customers',
dataShape: 'column',
tables: [{ id: 'customers', name: 'Customers', fields: customersFields }],
getData: (_t, fieldIds) => Promise.resolve(colsFromStruct(encoded.customers, fieldIds)),
},
{
id: 'orders',
name: 'Orders',
dataShape: 'column',
tables: [{ id: 'orders', name: 'Orders', fields: ordersFields }],
getData: (_t, fieldIds) => Promise.resolve(colsFromStruct(encoded.orders, fieldIds)),
},
{
id: 'order_items',
name: 'Order Items',
dataShape: 'column',
tables: [{ id: 'order_items', name: 'Order Items', fields: orderItemsFields }],
getData: (_t, fieldIds) => Promise.resolve(colsFromStruct(encoded.order_items, fieldIds)),
},
{
id: 'shipments',
name: 'Shipments',
dataShape: 'column',
tables: [{ id: 'shipments', name: 'Shipments', fields: shipmentsFields }],
getData: (_t, fieldIds) => Promise.resolve(colsFromStruct(encoded.shipments, fieldIds)),
},
{
id: 'return_costs',
name: 'Return Costs',
dataShape: 'row',
tables: [{ id: 'return_costs', name: 'Return Costs', fields: returnCostsFields }],
getData: () => Promise.resolve({ data: precomputed.return_costs }),
},
{
id: 'subcategories',
name: 'Subcategories',
dataShape: 'row',
tables: [{ id: 'subcategories', name: 'Subcategories', fields: subcategoriesFields }],
getData: () => Promise.resolve({ data: subcategoriesFromValues(data.products.subcategory) }),
},
],
relationships,
expressions,
calendars: [mainDemoCalendar],
};
}
/**
* TypeScript port of generate_office_b2b_demo.py.
*
* Generates the same 6-table B2B office-supplies dataset used by the main demo.
* Distributions are simplified (uniform ranges in place of lognormal/normal where
* exact shape is not required) - field names and row counts are identical.
*/
// âââ Config âââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ
const SEED = '20260122';
const END_DT = new Date('2026-01-21T23:59:59Z');
const END_MS = END_DT.getTime();
// Row counts at SF=1 follow TPC-H conventions (SF=1 â 1 GB uncompressed).
// N_STORES is a fixed dimension and does not scale.
const N_STORES = 50;
const N_PRODUCTS = 200_000; // TPC-H PART
const N_CUSTOMERS = 150_000; // TPC-H CUSTOMER
const N_ORDERS = 1_500_000; // TPC-H ORDERS
const N_SHIPMENTS = 1_125_000; // 75% of orders (same ratio as original)
// SF=0.004 â ~6k orders - lightweight browser demo matching the b1.0.0 JSON sizes.
// SF=0.04 â ~60k orders (default full demo).
// SF=1 â ~1.5M orders â 1 GB.
const DEFAULT_SCALE_FACTOR = 0.04;
function getScaleFactor(): number {
if (typeof window === 'undefined') {
return DEFAULT_SCALE_FACTOR;
}
// Prefer window.agStudioOpts.scaleFactor when present (set by studio-debug.js
// or by the page bootstrap from URL params); otherwise fall back to ?sf
// query param so this module also works standalone.
const optsSf = (window as any).agStudioOpts?.scaleFactor;
if (typeof optsSf === 'number' && Number.isFinite(optsSf) && optsSf > 0) {
return optsSf;
}
const param = new URLSearchParams(window.location.search).get('sf');
if (param == null) {
return DEFAULT_SCALE_FACTOR;
}
const sf = parseFloat(param);
return Number.isFinite(sf) && sf > 0 ? sf : DEFAULT_SCALE_FACTOR;
}
const SCALE_FACTOR = getScaleFactor();
// âââ RNG helpers ââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ
type RNG = () => number;
function randInt(rng: RNG, lo: number, hi: number): number {
return (Math.floor(rng() * (hi - lo)) + lo) | 0;
}
function randFloat(rng: RNG, lo: number, hi: number): number {
return rng() * (hi - lo) + lo;
}
function pick<T>(rng: RNG, arr: readonly T[]): T {
return arr[randInt(rng, 0, arr.length)];
}
// ââ Optimisation #1: cumulative-weight binary search ââ
// Replaces O(n) linear scan in pickWeighted with O(log n) binary search.
// Pre-computed cumulative weight arrays are built once at module level for all
// static weight arrays, avoiding repeated partial-sum accumulation on each call.
/** Build a normalised cumulative weight array from raw weights. */
function buildCumW(w: readonly number[]): Float64Array {
const length = w.length;
const out = new Float64Array(length);
let s = 0;
for (let i = 0; i < length; ++i) {
out[i] = s += w[i];
}
// Normalise so that entries represent proportions, then pin the last to 1
// to prevent floating-point drift from causing misses.
const total = out[length - 1];
if (total !== 1) {
for (let i = 0; i < length; ++i) {
out[i] /= total;
}
}
out[length - 1] = 1;
return out;
}
/** Returns the index of the selected item using binary search on cumulative weights. */
function pickCumWIdx(rng: RNG, cumW: Float64Array): number {
const r = rng();
let lo = 0;
let hi = cumW.length - 1;
while (lo < hi) {
const mid = (lo + hi) >> 1;
if (cumW[mid] < r) {
lo = mid + 1;
} else {
hi = mid;
}
}
return lo;
}
/** Pick from `arr` using a pre-built cumulative weight array (binary search). */
function pickCumW<T>(rng: RNG, arr: readonly T[], cumW: Float64Array): T {
return arr[pickCumWIdx(rng, cumW)];
}
/** Fisher-Yates shuffle - mutates in place and returns the array. */
function shuffle<T>(rng: RNG, arr: T[]): T[] {
for (let i = arr.length - 1; i > 0; --i) {
const j = randInt(rng, 0, i + 1);
const tmp = arr[i];
arr[i] = arr[j];
arr[j] = tmp;
}
return arr;
}
// ââ Optimisation #5: typed-array permutation ââ
// Uses Int32Array instead of a JS number[] to avoid boxing overhead and reduce
// GC pressure at SF=1 (1.5M entries).
/** Returns a shuffled Int32Array of [0 .. n-1]. */
function permutation(rng: RNG, n: number): Int32Array {
const arr = new Int32Array(n);
for (let i = 0; i < n; ++i) {
arr[i] = i;
}
for (let i = n - 1; i > 0; --i) {
const j = randInt(rng, 0, i + 1);
const tmp = arr[i];
arr[i] = arr[j];
arr[j] = tmp;
}
return arr;
}
/** Normal sample via Box-Muller (discards the spare for simplicity). */
function randNormal(rng: RNG, mu: number, sigma: number): number {
let u: number;
let v: number;
let s: number;
do {
u = rng() * 2 - 1;
v = rng() * 2 - 1;
s = u * u + v * v;
} while (s >= 1 || s === 0);
return mu + sigma * u * Math.sqrt((-2 * Math.log(s)) / s);
}
// âââ Date helpers âââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ
function p2(n: number): string {
return n < 10 ? `0${n}` : `${n}`;
}
/** Extract UTC month (1-12) from a millisecond timestamp - no Date object allocation. */
function msToMonth(ms: number): number {
const days = Math.floor(ms / 86_400_000);
const z = days + 719468;
const era = Math.floor((z >= 0 ? z : z - 146096) / 146097);
const doe = z - era * 146097;
const yoe = Math.floor((doe - Math.floor(doe / 1460) + Math.floor(doe / 36524) - Math.floor(doe / 146096)) / 365);
const doy = doe - (365 * yoe + Math.floor(yoe / 4) - Math.floor(yoe / 100));
const mp = Math.floor((5 * doy + 2) / 153);
return mp < 10 ? mp + 3 : mp - 9;
}
function daysMs(days: number): number {
return days * 86_400_000;
}
// âââ Static reference data ââââââââââââââââââââââââââââââââââââââââââââââââââââ
const REGIONS = ['UK', 'DACH', 'Nordics', 'US East', 'US West'] as const;
const REGION_W = [0.3, 0.2, 0.1, 0.2, 0.2] as const;
// Pre-built cumulative weight array for region selection
const REGION_CUMW = buildCumW(REGION_W);
const CITY_BY_REGION: Record<string, readonly string[]> = {
UK: ['London', 'Manchester', 'Birmingham', 'Leeds', 'Bristol', 'Edinburgh', 'Glasgow'],
DACH: ['Berlin', 'Munich', 'Hamburg', 'Frankfurt', 'Vienna', 'Zurich'],
Nordics: ['Stockholm', 'Copenhagen', 'Oslo', 'Helsinki'],
'US East': ['New York', 'Boston', 'Washington', 'Philadelphia', 'Atlanta', 'Miami'],
'US West': ['San Francisco', 'Los Angeles', 'Seattle', 'San Diego', 'Portland', 'San Jose'],
};
const SUBCATS = [
'Paper & Notebooks',
'Writing Instruments',
'Filing & Organisation',
'Desk Accessories',
'Labels & Mailing',
'Printing & Imaging',
'Binders & Presentation',
'Cleaning & Facilities (Office)',
] as const;
type Subcat = (typeof SUBCATS)[number];
const CATEGORY_BY_SUBCAT: Record<Subcat, string> = {
'Paper & Notebooks': 'Paper & Print',
'Printing & Imaging': 'Paper & Print',
'Writing Instruments': 'Writing & Drawing',
'Filing & Organisation': 'Filing & Binding',
'Binders & Presentation': 'Filing & Binding',
'Desk Accessories': 'Desk & Office',
'Labels & Mailing': 'Packaging & Mailing',
'Cleaning & Facilities (Office)': 'Facilities',
};
const SUBCAT_W = [0.22, 0.2, 0.15, 0.12, 0.1, 0.1, 0.07, 0.04] as const;
// Pre-built cumulative weight array for subcategory selection
const SUBCAT_CUMW = buildCumW(SUBCAT_W);
const BRANDS = [
'PaperLine',
'StapleForge',
'Inkwell & Co',
'DeskMate',
'FileCraft',
'LabelWorks',
'PrintSure',
'BindRight',
'CleanSlate Office',
'NoteNest',
'Clipster',
'OfficeBasics',
] as const;
const BRAND_W = BRANDS.map((_, i) => 1 / (i + 1));
const BRAND_W_SUM = BRAND_W.reduce((a, b) => a + b, 0);
const BRAND_W_NORM = BRAND_W.map((w) => w / BRAND_W_SUM);
// Pre-built cumulative weight array for brand selection
const BRAND_CUMW = buildCumW(BRAND_W_NORM);
const NAME_TEMPLATES: Record<Subcat, readonly string[]> = {
'Paper & Notebooks': [
'Copy Paper {v} 80gsm',
'Recycled Paper {v} 75gsm',
'Notebook {v} Ruled',
'Notepad {v} Plain',
'Sticky Notes {v}',
],
'Writing Instruments': [
'Ballpoint Pens {v}',
'Gel Pens {v}',
'Highlighters {v}',
'Permanent Markers {v}',
'Whiteboard Markers {v}',
],
'Filing & Organisation': [
'Document Wallets {v}',
'Lever Arch Files {v}',
'File Folders {v}',
'Desk Trays {v}',
'Ring Folders {v}',
],
'Desk Accessories': ['Stapler {v}', 'Staples {v}', 'Tape Dispenser {v}', 'Paper Clips {v}', 'Desk Organiser {v}'],
'Labels & Mailing': [
'Address Labels {v}',
'Shipping Labels {v}',
'Mailing Envelopes {v}',
'Padded Envelopes {v}',
'Packing Tape {v}',
],
'Printing & Imaging': [
'Printer Toner {v} Compatible',
'Ink Cartridge {v} Compatible',
'Photo Paper {v}',
'Thermal Receipt Rolls {v}',
'Printer Drum Unit {v} Compatible',
],
'Binders & Presentation': [
'Presentation Folders {v}',
'Binder Dividers {v}',
'Plastic Pockets {v}',
'Spiral Binding Coils {v}',
'Laminating Pouches {v}',
],
'Cleaning & Facilities (Office)': [
'Surface Wipes {v}',
'Hand Towels {v}',
'Bin Liners {v}',
'Hand Sanitiser {v}',
'Air Freshener {v}',
],
};
const VARIANTS: Record<Subcat, readonly string[]> = {
'Paper & Notebooks': ['A4', 'A5', 'Letter', 'Legal'],
'Writing Instruments': ['Blue', 'Black', 'Red', 'Assorted'],
'Filing & Organisation': ['A4', 'A5', 'Letter'],
'Desk Accessories': ['Small', 'Medium', 'Large'],
'Labels & Mailing': ['Small', 'Medium', 'Large'],
'Printing & Imaging': ['Black', 'Cyan', 'Magenta', 'Yellow'],
'Binders & Presentation': ['A4', 'A5', 'Letter'],
'Cleaning & Facilities (Office)': ['Pack-10', 'Pack-50', 'Pack-200'],
};
const SUFFIXES = ['Standard', 'Pro', 'Plus', 'Bulk', 'Eco', 'Premium'] as const;
const INDUSTRIES = [
'Technology',
'Healthcare',
'Education',
'Finance',
'Retail',
'Manufacturing',
'Hospitality',
'Public Sector',
] as const;
const INDUSTRY_W = [0.16, 0.14, 0.12, 0.12, 0.14, 0.12, 0.1, 0.1] as const;
// Pre-built cumulative weight array for industry selection
const INDUSTRY_CUMW = buildCumW(INDUSTRY_W);
const SEGMENTS = ['SMB', 'Mid-Market', 'Enterprise'] as const;
const SEG_W = [0.6, 0.3, 0.1] as const;
// Pre-built cumulative weight array for segment selection
const SEG_CUMW = buildCumW(SEG_W);
const FIRST_NAMES = [
'Alex',
'Sam',
'Jordan',
'Taylor',
'Morgan',
'Casey',
'Jamie',
'Riley',
'Charlie',
'Avery',
'Robin',
'Cameron',
'Drew',
'Quinn',
'Harper',
'Rowan',
'Elliot',
'Finley',
'Parker',
'Reese',
'Nina',
'Maya',
'Amir',
'Omar',
'Leah',
'Hannah',
'Oliver',
'Noah',
'Sophia',
'Isla',
] as const;
const LAST_NAMES = [
'Johnson',
'Smith',
'Brown',
'Wilson',
'Taylor',
'Davies',
'Evans',
'Thomas',
'Roberts',
'Walker',
'White',
'Hall',
'Allen',
'Young',
'King',
'Wright',
'Scott',
'Green',
'Baker',
'Adams',
'Miller',
'Jones',
'Hughes',
'Clarke',
'Ward',
'Turner',
'Cox',
'Price',
'Cooper',
'Reid',
] as const;
const DEPARTMENTS = [
'procurement',
'purchasing',
'finance',
'accounts',
'operations',
'facilities',
'it',
'admin',
'office',
] as const;
const TLD_BY_REGION: Record<string, readonly string[]> = {
UK: ['co.uk', 'uk'],
DACH: ['de', 'at', 'ch'],
Nordics: ['se', 'dk', 'no', 'fi'],
'US East': ['com', 'us'],
'US West': ['com', 'us'],
};
const TLD_W: Record<string, readonly number[]> = {
UK: [0.85, 0.15],
DACH: [0.7, 0.15, 0.15],
Nordics: [0.28, 0.26, 0.23, 0.23],
'US East': [0.92, 0.08],
'US West': [0.92, 0.08],
};
// Pre-built cumulative weight arrays for TLD selection per region
const TLD_CUMW: Record<string, Float64Array> = {};
for (const r of REGIONS) {
TLD_CUMW[r] = buildCumW(TLD_W[r]);
}
const EMAIL_PATTERNS = ['first.last', 'firstlast', 'f.last', 'dept', 'dept.firstlast'] as const;
const EMAIL_PATTERN_W = [0.3, 0.22, 0.18, 0.2, 0.1] as const;
// Pre-built cumulative weight array for email pattern selection
const EMAIL_PATTERN_CUMW = buildCumW(EMAIL_PATTERN_W);
// âââ Product helpers ââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ
function uomPackForName(name: string): [string, number] {
const n = name.toLowerCase();
if (n.includes('printer toner') || n.includes('printer drum unit') || n.includes('ink cartridge')) {
return ['each', 1];
}
if (n.includes('thermal receipt rolls')) {
return ['box', 20];
}
if (n.includes('photo paper')) {
return ['pack', 100];
}
if (n.includes('copy paper') || n.includes('recycled paper')) {
return ['case', 5];
}
if (n.includes('notebook')) {
return ['pack', 5];
}
if (n.includes('notepad')) {
return ['pack', 6];
}
if (n.includes('sticky notes')) {
return ['pack', 12];
}
if (n.includes('ballpoint pens') || n.includes('gel pens')) {
return ['box', 12];
}
if (n.includes('highlighters') || n.includes('permanent markers') || n.includes('whiteboard markers')) {
return ['box', 8];
}
if (
n.includes('lever arch files') ||
n.includes('ring folders') ||
n.includes('stapler') ||
n.includes('tape dispenser') ||
n.includes('desk organiser') ||
n.includes('desk trays')
) {
return ['each', 1];
}
if (n.includes('file folders') || n.includes('document wallets') || n.includes('presentation folders')) {
return ['pack', 25];
}
if (n.includes('plastic pockets')) {
return ['pack', 100];
}
if (n.includes('binder dividers')) {
return ['pack', 20];
}
if (n.includes('spiral binding coils')) {
return ['box', 50];
}
if (n.includes('laminating pouches')) {
return ['pack', 100];
}
if (n.includes('staples')) {
return ['box', 5000];
}
if (n.includes('paper clips')) {
return ['box', 1000];
}
if (n.includes('address labels') || n.includes('shipping labels')) {
return ['roll', 1];
}
if (n.includes('mailing envelopes') || n.includes('padded envelopes')) {
return ['box', 250];
}
if (n.includes('packing tape')) {
return ['pack', 6];
}
if (n.includes('surface wipes')) {
return ['case', 12];
}
if (n.includes('hand towels')) {
return ['case', 16];
}
if (n.includes('bin liners')) {
return ['case', 10];
}
if (n.includes('hand sanitiser') || n.includes('air freshener')) {
return ['case', 12];
}
return ['each', 1];
}
/**
* Returns [min, max] price range for uniform sampling.
* Python uses lognormal(median, sigma) clipped to [min, max]; we use uniform over the same range.
*/
function priceRange(name: string, uom: string): [number, number] {
const n = name.toLowerCase();
if (n.includes('printer toner')) {
return [30, 220];
}
if (n.includes('printer drum unit')) {
return [40, 280];
}
if (n.includes('ink cartridge')) {
return [12, 140];
}
if (n.includes('thermal receipt rolls')) {
return [12, 90];
}
if (n.includes('photo paper')) {
return [8, 70];
}
if (n.includes('copy paper') || n.includes('recycled paper')) {
return [20, 95];
}
if (n.includes('notebook')) {
return [5, 40];
}
if (n.includes('notepad')) {
return [4, 30];
}
if (n.includes('sticky notes')) {
return [3, 25];
}
if (n.includes('ballpoint pens') || n.includes('gel pens')) {
return [4, 35];
}
if (n.includes('highlighters')) {
return [4, 38];
}
if (n.includes('permanent markers') || n.includes('whiteboard markers')) {
return [5, 45];
}
if (n.includes('lever arch files')) {
return [2.5, 20];
}
if (n.includes('ring folders')) {
return [2, 18];
}
if (n.includes('document wallets') || n.includes('file folders') || n.includes('presentation folders')) {
return [6, 45];
}
if (n.includes('desk trays')) {
return [3.5, 28];
}
if (n.includes('stapler')) {
return [3, 30];
}
if (n.includes('tape dispenser')) {
return [2.5, 24];
}
if (n.includes('desk organiser')) {
return [5, 55];
}
if (n.includes('staples')) {
return [2, 18];
}
if (n.includes('paper clips')) {
return [1.5, 14];
}
if (n.includes('address labels')) {
return [3, 22];
}
if (n.includes('shipping labels')) {
return [4, 35];
}
if (n.includes('mailing envelopes')) {
return [7, 55];
}
if (n.includes('padded envelopes')) {
return [12, 85];
}
if (n.includes('packing tape')) {
return [5, 40];
}
if (n.includes('plastic pockets')) {
return [5, 40];
}
if (n.includes('binder dividers')) {
return [2.5, 22];
}
if (n.includes('spiral binding coils')) {
return [7, 60];
}
if (n.includes('laminating pouches')) {
return [8, 70];
}
if (n.includes('surface wipes')) {
return [10, 85];
}
if (n.includes('hand towels')) {
return [12, 95];
}
if (n.includes('bin liners')) {
return [9, 75];
}
if (n.includes('hand sanitiser')) {
return [10, 85];
}
if (n.includes('air freshener')) {
return [7, 70];
}
if (uom === 'each') {
return [2.5, 40];
}
if (uom === 'roll') {
return [3, 35];
}
if (uom === 'box') {
return [4, 60];
}
if (uom === 'pack') {
return [3, 60];
}
return [5, 95];
}
// ââ Optimisation #3: template-level UOM/pack and price-range lookup tables ââ
// Pre-compute uomPackForName and priceRange results for every (subcat, template)
// pair at module load time so genProducts can use direct array lookups instead of
// repeated toLowerCase()+includes() string scans per product.
const TEMPLATE_UOM_PACK: [string, number][][] = SUBCATS.map((sc) =>
NAME_TEMPLATES[sc].map((tmpl) => {
const name = tmpl.replace('{v}', VARIANTS[sc][0]) + ' Standard';
return uomPackForName(name);
})
);
const TEMPLATE_PRICE_RANGE: [number, number][][] = SUBCATS.map((sc, si) =>
NAME_TEMPLATES[sc].map((tmpl, ti) => {
const [uom] = TEMPLATE_UOM_PACK[si][ti];
const name = tmpl.replace('{v}', VARIANTS[sc][0]) + ' Standard';
return priceRange(name, uom);
})
);
// âââ Slug helpers âââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ
function slugCompany(name: string): string {
const s = name
.toLowerCase()
.replace(/[.,&]/g, ' ')
.replace(/\b(ltd|gmbh|ab|inc|llc|plc|sas|bv)\b/g, '')
.replace(/[^a-z0-9]+/g, '');
return s.slice(0, 30) || 'company';
}
// âââ 1) Stores ââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ
export interface Store {
store_id: string;
store_name: string;
region: string;
city: string;
opened_date: string;
store_type: string;
}
export interface StoreData {
store_id: string[];
store_name: string[];
region: string[];
city: string[];
opened_date: Float64Array; // ms since epoch
store_type: string[];
}
function genStores(rng: RNG): StoreData {
const store_id: string[] = [];
const store_name: string[] = [];
const region: string[] = [];
const city: string[] = [];
const opened_date_ms: number[] = [];
const store_type: string[] = [];
let idx = 1;
// 2 fulfilment hubs per region = 10
for (const r of REGIONS) {
for (let i = 0; i < 2; ++i) {
const c = pick(rng, CITY_BY_REGION[r]);
store_id.push(`S${idx}`);
store_name.push(`${c} Fulfilment Hub`);
region.push(r);
city.push(c);
opened_date_ms.push(END_MS - daysMs(randInt(rng, 365, 3650)));
store_type.push('Fulfilment');
++idx;
}
}
// remaining: sales offices + partner depots
const remaining = N_STORES - store_id.length;
const salesCount = Math.floor(remaining * 0.6);
const types: string[] = [
...Array(salesCount).fill('Sales Office'),
...Array(remaining - salesCount).fill('Partner Depot'),
];
shuffle(rng, types);
// genStores uses pickCumW with the pre-built REGION_CUMW
for (const t of types) {
const r = pickCumW(rng, REGIONS, REGION_CUMW);
const c = pick(rng, CITY_BY_REGION[r]);
store_id.push(`S${idx}`);
store_name.push(`${c} ${t}`);
region.push(r);
city.push(c);
opened_date_ms.push(END_MS - daysMs(randInt(rng, 365, 3650)));
store_type.push(t);
++idx;
}
return {
store_id,
store_name,
region,
city,
opened_date: new Float64Array(opened_date_ms),
store_type,
};
}
// âââ 2) Products ââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ
export interface Product {
product_id: string;
product_name: string;
category: string;
subcategory: string;
brand: string;
launch_date: string;
list_price: number;
unit_cost: number;
is_discontinued: boolean;
uom: string;
pack_size: number;
variant: string;
}
export interface ProductData {
product_id: string[];
product_name: string[];
category: string[];
subcategory: string[];
brand: string[];
launch_date: Float64Array; // ms
list_price: Float64Array;
unit_cost: Float64Array;
is_discontinued: boolean[];
uom: string[];
pack_size: Float64Array;
variant: string[];
}
// ââ Optimisation #3 + #4: template lookup tables + pre-allocated arrays ââ
// genProducts now indexes into TEMPLATE_UOM_PACK/TEMPLATE_PRICE_RANGE via
// subcategory and template indices, eliminating string scans. Arrays are
// pre-allocated with known size and written by index instead of push().
// Pre-compute suffix price multipliers to avoid per-product regex/includes
const SUFFIX_MULT: Record<string, number> = {
Standard: 1,
Pro: 1.06,
Plus: 1.03,
Bulk: 0.92,
Eco: 0.95,
Premium: 1.1,
};
function genProducts(rng: RNG, n: number): ProductData {
// Per-table arena: 4 Ă Float64(n) = 32n bytes
const arena = new DemoArena(32 * n);
const product_id: string[] = new Array(n);
const product_name: string[] = new Array(n);
const category: string[] = new Array(n);
const subcategory: string[] = new Array(n);
const brand: string[] = new Array(n);
const launch_date = arena.float64(n);
const list_price = arena.float64(n);
const unit_cost = arena.float64(n);
const is_discontinued: boolean[] = new Array(n);
const uom: string[] = new Array(n);
const pack_size = arena.float64(n);
const variant: string[] = new Array(n);
for (let i = 0; i < n; ++i) {
const sc = pickCumW(rng, SUBCATS, SUBCAT_CUMW);
const b = pickCumW(rng, BRANDS, BRAND_CUMW);
const scVariants = VARIANTS[sc];
const v = pick(rng, scVariants);
// Pick template by index so we can look up UOM/pack and price range directly
const scIdx = SUBCATS.indexOf(sc);
const templates = NAME_TEMPLATES[sc];
const tmplIdx = randInt(rng, 0, templates.length);
const tmpl = templates[tmplIdx];
const suffix = pick(rng, SUFFIXES);
const name = tmpl.replace('{v}', v) + ' ' + suffix;
// Direct lookup instead of string scanning
const [uomVal, packSizeVal] = TEMPLATE_UOM_PACK[scIdx][tmplIdx];
const [pMin, pMax] = TEMPLATE_PRICE_RANGE[scIdx][tmplIdx];
// launch date biased to recent (uÂČ Ă 36 months)
const u = rng();
const daysAgo = Math.floor(u * u * 36 * 30);
const launchMs = END_MS - daysMs(daysAgo);
let price = randFloat(rng, pMin, pMax);
// Apply suffix-based price multiplier (replaces toLowerCase+includes per suffix)
price *= SUFFIX_MULT[suffix];
const listPrice = Math.round(Math.min(Math.max(price, 0.99), 320) * 100) / 100;
const costMult = sc === 'Printing & Imaging' ? randFloat(rng, 0.65, 0.85) : randFloat(rng, 0.45, 0.72);
const unitCost = Math.round(Math.min(listPrice * costMult, listPrice - 0.01) * 100) / 100;
const isDiscontinued = rng() < 0.06;
product_id[i] = `P${i + 1}`;
product_name[i] = name;
category[i] = CATEGORY_BY_SUBCAT[sc];
subcategory[i] = sc;
brand[i] = b;
launch_date[i] = launchMs;
list_price[i] = listPrice;
unit_cost[i] = unitCost;
is_discontinued[i] = isDiscontinued;
uom[i] = uomVal;
pack_size[i] = packSizeVal;
variant[i] = v;
}
return {
product_id,
product_name,
category,
subcategory,
brand,
launch_date,
list_price,
unit_cost,
is_discontinued,
uom,
pack_size,
variant,
};
}
// âââ 3) Customers âââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ
export interface Customer {
customer_id: string;
customer_name: string;
email: string;
signup_date: string;
region: string;
segment: string;
is_active: boolean;
industry: string;
}
export interface CustomerData {
customer_id: string[];
customer_name: string[];
email: string[];
signup_date: Float64Array; // ms
region: string[];
segment: string[];
is_active: boolean[];
industry: string[];
}
function makeEmail(rng: RNG, company: string, region: string): string {
const dom = slugCompany(company);
const tlds = TLD_BY_REGION[region] ?? ['com'];
// Use pre-built cumulative weight array for TLD selection
const tldCumW = TLD_CUMW[region];
const tld = tldCumW != null ? pickCumW(rng, tlds, tldCumW) : tlds[0];
const fn = pick(rng, FIRST_NAMES).toLowerCase();
const ln = pick(rng, LAST_NAMES).toLowerCase();
const dept = pick(rng, DEPARTMENTS);
const pat = pickCumW(rng, EMAIL_PATTERNS, EMAIL_PATTERN_CUMW);
let local: string;
if (pat === 'first.last') {
local = `${fn}.${ln}`;
} else if (pat === 'firstlast') {
local = `${fn}${ln}`;
} else if (pat === 'f.last') {
local = `${fn[0]}.${ln}`;
} else if (pat === 'dept') {
local = dept;
} else {
local = `${dept}.${fn}${ln}`;
}
if (rng() < 0.12) {
local = `${local}${randInt(rng, 2, 99)}`;
}
return `${local}@${dom}.${tld}`;
}
const ADJECTIVES = [
'North',
'Green',
'Blue',
'Prime',
'Metro',
'Summit',
'Crescent',
'Vertex',
'Silver',
'Beacon',
'Apex',
'Harbour',
'Oak',
] as const;
const NOUNS = [
'Systems',
'Group',
'Holdings',
'Solutions',
'Partners',
'Industries',
'Labs',
'Networks',
'Care',
'Logistics',
'Foods',
'Energy',
'Services',
] as const;
const LEGAL_SUFFIXES = ['Ltd', 'GmbH', 'AB', 'Inc', 'LLC', 'PLC', 'SAS', 'BV'] as const;
// ââ Optimisation #7: pre-allocated arrays in genCustomers ââ
function genCustomers(rng: RNG, n: number): CustomerData {
// Per-table arena: 1 Ă Float64(n) = 8n bytes
const arena = new DemoArena(8 * n);
const customer_id: string[] = new Array(n);
const customer_name: string[] = new Array(n);
const email: string[] = new Array(n);
const signup_date = arena.float64(n);
const region: string[] = new Array(n);
const segment: string[] = new Array(n);
const is_active: boolean[] = new Array(n);
const industry: string[] = new Array(n);
for (let i = 0; i < n; ++i) {
const r = pickCumW(rng, REGIONS, REGION_CUMW);
const ind = pickCumW(rng, INDUSTRIES, INDUSTRY_CUMW);
const seg = pickCumW(rng, SEGMENTS, SEG_CUMW);
const u = rng();
const signupMs = END_MS - daysMs(Math.floor(u * u * 2100));
const name = `${pick(rng, ADJECTIVES)} ${pick(rng, NOUNS)} ${pick(rng, LEGAL_SUFFIXES)}`;
const em = makeEmail(rng, name, r);
const activeThresh = seg === 'Enterprise' ? 0.96 : seg === 'Mid-Market' ? 0.93 : 0.88;
const active = rng() < activeThresh;
customer_id[i] = `C${i + 1}`;
customer_name[i] = name;
email[i] = em;
signup_date[i] = signupMs;
region[i] = r;
segment[i] = seg;
is_active[i] = active;
industry[i] = ind;
}
return {
customer_id,
customer_name,
email,
signup_date,
region,
segment,
is_active,
industry,
};
}
// âââ 4) Orders ââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ
export interface Order {
order_id: string;
customer_id: string;
store_id: string;
order_datetime: string;
channel: string;
status: string;
currency: string;
price_list: string;
discount_reason: string;
payment_method: string;
payment_terms: string;
notes: string | null;
promo_code: null;
}
export interface OrderData {
order_id: DictColumn;
customer_id: DictColumn;
store_id: DictColumn;
order_datetime: Float64Array; // ms (replaces the old orderMsArr)
order_month: DictColumn; // pre-computed 'YYYY-MM'
order_year: DictColumn; // pre-computed 'YYYY' string
channel: DictColumn;
status: DictColumn;
currency: DictColumn;
price_list: DictColumn;
discount_reason: DictColumn;
payment_method: DictColumn;
payment_terms: DictColumn;
notes: DictColumn;
promo_code: DictColumn;
}
function genOrders(rng: RNG, customers: CustomerData, stores: StoreData, n: number): OrderData {
// Per-table arena: 1ĂFloat64(n) + 3ĂUint32(n) + 11ĂUint8(n) = 31n bytes
const arena = new DemoArena(31 * n);
// Status pool: proportions from original Python script (56200/60000, 2200/60000, 600/60000, 1000/60000).
// indices: 0=Completed, 1=Cancelled, 2=Returned, 3=Processing
const STATUS_VALS = ['Completed', 'Cancelled', 'Returned', 'Processing'] as const;
const nCancelled = Math.round(n * (2_200 / 60_000));
const nReturned = Math.round(n * (600 / 60_000));
const nProcessing = Math.round(n * (1_000 / 60_000));
const nCompleted = n - nCancelled - nReturned - nProcessing;
const statusPool: number[] = [
...Array(nCompleted).fill(0), // Completed
...Array(nCancelled).fill(1), // Cancelled
...Array(nReturned).fill(2), // Returned
...Array(nProcessing).fill(3), // Processing
];
shuffle(rng, statusPool);
// Customer sampling weighted by segment (SMB=1, Mid-Market=2, Enterprise=5)
const nCusts = customers.customer_id.length;
const segTotal = customers.segment.reduce((a, seg) => a + (seg === 'SMB' ? 1 : seg === 'Mid-Market' ? 2 : 5), 0);
const segCumW = new Float64Array(nCusts);
{
let s = 0;
for (let i = 0; i < nCusts; ++i) {
const seg = customers.segment[i];
s += (seg === 'SMB' ? 1 : seg === 'Mid-Market' ? 2 : 5) / segTotal;
segCumW[i] = s;
}
}
function pickCustomer(): number {
const r = rng();
let lo = 0;
let hi = segCumW.length - 1;
while (lo < hi) {
const mid = (lo + hi) >> 1;
if (segCumW[mid] < r) {
lo = mid + 1;
} else {
hi = mid;
}
}
return lo;
}
// ~60-month window (Jan 2021 â END_DT) with seasonality and year-growth weights
const months: { year: number; month: number }[] = [];
{
let y = END_DT.getUTCFullYear(),
m = END_DT.getUTCMonth() + 1;
const startYear = 2021;
const startMonth = 1;
while (y > startYear || (y === startYear && m >= startMonth)) {
months.unshift({ year: y, month: m });
if (--m === 0) {
m = 12;
--y;
}
}
}
const yearGrowth: Record<number, number> = { 2021: 0.55, 2022: 0.7, 2023: 0.85 };
const monthWeights = months.map(({ year: y, month: m }) => {
let w = yearGrowth[y] ?? 1;
if (m === 10 || m === 11 || m === 12) {
w *= 1.2;
}
if (m === 12) {
w *= 1.1;
}
if (m === 7 || m === 8) {
w *= 0.9;
}
if (m === 1) {
w *= 1.05;
}
return w;
});
const monthWTotal = monthWeights.reduce((a, b) => a + b, 0);
const length = monthWeights.length;
const monthCumW = new Float64Array(length);
{
let s = 0;
for (let i = 0; i < length; ++i) {
s += monthWeights[i] / monthWTotal;
monthCumW[i] = s;
}
}
// Precompute days-per-month (avoids new Date inside the loop)
const monthDays = months.map(({ year: y, month: m }) => new Date(y, m, 0).getDate());
// Hour weights: business hours bias
const hourCumW = new Float64Array(24);
{
const hourTotal = 6 * 0.3 + 10 * 2.2 + 3 * 0.6 + 5 * 0.3; // precomputed sum
let s = 0;
for (let h = 0; h < 24; ++h) {
const w = h >= 8 && h < 18 ? 2.2 : h >= 18 && h < 21 ? 0.6 : 0.3;
s += w / hourTotal;
hourCumW[h] = s;
}
}
// ââ Optimisation #6: binary search for pickHour and pickMonthIdx ââ
// pickCumWIdx replaces the integer range arrays - returns the index directly.
// Store lookup by region - built from StoreData column arrays using indices
const fulfilByRegion: Record<string, number[]> = {};
const allByRegion: Record<string, number[]> = {};
const partnerByRegion: Record<string, number[]> = {};
for (const r of REGIONS) {
fulfilByRegion[r] = [];
partnerByRegion[r] = [];
allByRegion[r] = [];
}
const length2 = stores.store_id.length;
for (let si = 0; si < length2; ++si) {
const r = stores.region[si];
const t = stores.store_type[si];
allByRegion[r].push(si);
if (t === 'Fulfilment') {
fulfilByRegion[r].push(si);
} else if (t === 'Partner Depot') {
partnerByRegion[r].push(si);
}
}
const CHANNELS = ['Portal', 'Sales-Assisted', 'Partner', 'In-person'] as const;
const CHANNEL_W_SMB = [0.7, 0.2, 0.08, 0.02] as const;
const CHANNEL_W_MID = [0.55, 0.25, 0.15, 0.05] as const;
const CHANNEL_W_ENT = [0.35, 0.35, 0.25, 0.05] as const;
// Pre-built cumulative weight arrays for channel selection by segment
const CHANNEL_CUMW_SMB = buildCumW(CHANNEL_W_SMB);
const CHANNEL_CUMW_MID = buildCumW(CHANNEL_W_MID);
const CHANNEL_CUMW_ENT = buildCumW(CHANNEL_W_ENT);
const PRICE_LISTS = ['Contract', 'Volume', 'List'] as const;
const PL_W_ENT = [0.6, 0.25, 0.15] as const;
const PL_W_MID = [0.45, 0.2, 0.35] as const;
const PL_W_SMB = [0.2, 0.15, 0.65] as const; // reversed for SMB: List,Contract,Volume â use fixed order
// Pre-built cumulative weight arrays for price list selection by segment
const PL_CUMW_ENT = buildCumW(PL_W_ENT);
const PL_CUMW_MID = buildCumW(PL_W_MID);
const PL_CUMW_SMB = buildCumW(PL_W_SMB);
const PAYMENT_METHODS = ['Invoice', 'Bank Transfer', 'Card'] as const;
const PAYMENT_METHOD_W = [0.78, 0.15, 0.07] as const;
const PAYMENT_METHOD_CUMW = buildCumW(PAYMENT_METHOD_W);
const PAYMENT_TERMS = ['Net 30', 'Net 45', 'Net 60'] as const;
const PAYMENT_TERMS_W = [0.55, 0.3, 0.15] as const;
const PAYMENT_TERMS_CUMW = buildCumW(PAYMENT_TERMS_W);
const NOTES_OPTIONS = ['PO required', 'Deliver to goods-in', 'Leave at reception', 'Urgent replacement'] as const;
const DISC_REASONS_CONTRACT_CUMW = buildCumW([0.8, 0.12, 0.08]);
// discount_reason vocab: Contract=0, Renewal=1, Bundle=2, Volume=3, None=4
const DISC_REASON_VALS = ['Contract', 'Renewal', 'Bundle', 'Volume', 'None'] as const;
const CURRENCY_VALS = ['GBP', 'EUR', 'USD'] as const;
// Pre-allocate typed index arrays - all from arena (single ArrayBuffer).
const order_datetime = arena.float64(n);
const order_id_indices = arena.uint32(n);
const order_id_vals: string[] = new Array(n);
const customer_id_indices = arena.uint32(n);
const store_id_indices = arena.uint32(n);
const order_month_indices = arena.uint8(n);
const channel_indices = arena.uint8(n);
const status_indices = arena.uint8(n);
const currency_indices = arena.uint8(n);
const price_list_indices = arena.uint8(n);
const disc_reason_indices = arena.uint8(n);
const payment_method_indices = arena.uint8(n);
const payment_terms_indices = arena.uint8(n);
const notes_indices = arena.uint8(n); // 0 = placeholder for null rows
let notesNullCount = 0;
for (let i = 0; i < n; ++i) {
const custIdx = pickCustomer();
const custRegion = customers.region[custIdx];
const custSegment = customers.segment[custIdx];
const currIdx = custRegion === 'UK' ? 0 : custRegion === 'DACH' || custRegion === 'Nordics' ? 1 : 2; // GBP=0, EUR=1, USD=2
const channelCumW =
custSegment === 'SMB'
? CHANNEL_CUMW_SMB
: custSegment === 'Mid-Market'
? CHANNEL_CUMW_MID
: CHANNEL_CUMW_ENT;
const chIdx = pickCumWIdx(rng, channelCumW);
// Order datetime - use pickCumWIdx for month and hour
const mIdx = pickCumWIdx(rng, monthCumW);
const { year: my, month: mm } = months[mIdx];
const day = randInt(rng, 1, monthDays[mIdx] + 1);
const hour = pickCumWIdx(rng, hourCumW);
let orderMs = Date.UTC(my, mm - 1, day, hour, randInt(rng, 0, 60), randInt(rng, 0, 60));
if (orderMs > END_MS) {
orderMs = END_MS - randInt(rng, 0, 86_400) * 1000;
}
// Store - write store index directly
let storeIdx: number;
const ch = CHANNELS[chIdx];
if (ch === 'Partner') {
const partners = partnerByRegion[custRegion];
storeIdx = partners.length > 0 && rng() < 0.7 ? pick(rng, partners) : pick(rng, fulfilByRegion[custRegion]);
} else {
storeIdx = rng() < 0.85 ? pick(rng, fulfilByRegion[custRegion]) : pick(rng, allByRegion[custRegion]);
}
// Price list - uses pre-built cumulative weight arrays
const plCumW =
custSegment === 'Enterprise' ? PL_CUMW_ENT : custSegment === 'Mid-Market' ? PL_CUMW_MID : PL_CUMW_SMB;
const plIdx = pickCumWIdx(rng, plCumW);
const pl = PRICE_LISTS[plIdx];
let discReasonIdx: number;
if (pl === 'Contract') {
// Contract=0, Renewal=1, Bundle=2
discReasonIdx = pickCumWIdx(rng, DISC_REASONS_CONTRACT_CUMW);
} else if (pl === 'Volume') {
discReasonIdx = rng() < 0.85 ? 3 : 2; // Volume=3, Bundle=2
} else {
discReasonIdx = 4; // None=4
}
const note = rng() < 0.04 ? pick(rng, NOTES_OPTIONS) : null;
order_id_vals[i] = `O${i + 1}`;
order_id_indices[i] = i;
customer_id_indices[i] = custIdx;
store_id_indices[i] = storeIdx;
order_datetime[i] = orderMs;
// ââ Optimisation #2: use already-computed mIdx - no string allocation per order ââ
order_month_indices[i] = mIdx;
channel_indices[i] = chIdx;
status_indices[i] = statusPool[i];
currency_indices[i] = currIdx;
price_list_indices[i] = plIdx;
disc_reason_indices[i] = discReasonIdx;
payment_method_indices[i] = pickCumWIdx(rng, PAYMENT_METHOD_CUMW);
payment_terms_indices[i] = pickCumWIdx(rng, PAYMENT_TERMS_CUMW);
if (note != null) {
notes_indices[i] = NOTES_OPTIONS.indexOf(note);
} else {
++notesNullCount;
}
}
const monthStrs = months.map(({ year: y, month: m }) => `${y}-${p2(m)}`);
const uniqueYears = [...new Set(months.map(({ year: y }) => y))].sort((a, b) => a - b);
const yearStrs = uniqueYears.map(String);
const monthToYearIdx = months.map(({ year: y }) => uniqueYears.indexOf(y));
const order_year_indices = arena.uint8(n);
for (let i = 0; i < n; ++i) {
order_year_indices[i] = monthToYearIdx[order_month_indices[i]];
}
return {
order_id: { indices: order_id_indices, indexToValue: order_id_vals, nullCount: 0 },
customer_id: { indices: customer_id_indices, indexToValue: customers.customer_id, nullCount: 0 },
store_id: { indices: store_id_indices, indexToValue: stores.store_id, nullCount: 0 },
order_datetime,
order_month: { indices: order_month_indices, indexToValue: monthStrs, nullCount: 0 },
order_year: { indices: order_year_indices, indexToValue: yearStrs, nullCount: 0 },
channel: { indices: channel_indices, indexToValue: [...CHANNELS], nullCount: 0 },
status: { indices: status_indices, indexToValue: [...STATUS_VALS], nullCount: 0 },
currency: { indices: currency_indices, indexToValue: [...CURRENCY_VALS], nullCount: 0 },
price_list: { indices: price_list_indices, indexToValue: [...PRICE_LISTS], nullCount: 0 },
discount_reason: { indices: disc_reason_indices, indexToValue: [...DISC_REASON_VALS], nullCount: 0 },
payment_method: { indices: payment_method_indices, indexToValue: [...PAYMENT_METHODS], nullCount: 0 },
payment_terms: { indices: payment_terms_indices, indexToValue: [...PAYMENT_TERMS], nullCount: 0 },
notes: { indices: notes_indices, indexToValue: [...NOTES_OPTIONS], nullCount: notesNullCount },
promo_code: { indices: arena.uint8(n), indexToValue: [], nullCount: n },
};
}
// âââ 5) Order Items âââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ
export interface OrderItem {
order_item_id: string;
order_id: string;
product_id: string;
quantity: number;
unit_price: number;
discount_pct: number;
tax_rate: number;
returned: boolean;
return_reason: string | null;
}
export interface OrderItemData {
order_item_id: DictColumn;
order_id: DictColumn;
product_id: DictColumn;
quantity: Float64Array;
unit_price: Float64Array;
discount_pct: Float64Array;
tax_rate: Float64Array;
returned: boolean[];
return_reason: DictColumn;
}
const CAP_BY_UOM: Record<string, number> = { case: 70, box: 90, pack: 110, roll: 80 };
const QTY_BASES: Record<string, readonly [number, number, number]> = {
'Paper & Notebooks': [6, 14, 28],
'Labels & Mailing': [4, 12, 24],
'Cleaning & Facilities (Office)': [4, 12, 22],
'Writing Instruments': [3, 10, 18],
'Filing & Organisation': [3, 9, 16],
'Binders & Presentation': [3, 9, 16],
'Printing & Imaging': [2, 5, 10],
'Desk Accessories': [1, 3, 6],
};
const QTY_BASES_DEFAULT: readonly [number, number, number] = [3, 8, 14];
/** Returns the upper bound for uniform quantity sampling in [1, max]. */
function qtyMax(subcat: string, segment: string, uom: string): number {
const base = QTY_BASES[subcat] ?? QTY_BASES_DEFAULT;
const med = segment === 'Enterprise' ? base[2] : segment === 'Mid-Market' ? base[1] : base[0];
const cap = uom === 'each' ? (segment === 'Enterprise' ? 12 : 6) : (CAP_BY_UOM[uom] ?? 25);
return Math.min(med * 2, cap);
}
const RETURN_REASONS = ['Damaged', 'Incorrect spec', 'Over-ordered', 'Warranty claim'] as const;
export function genOrderItems(
rng: RNG,
orders: OrderData,
customers: CustomerData,
products: ProductData
): OrderItemData {
const nOrders = orders.order_id.indices.length;
const nItems = computeItemCount(nOrders);
// Per-table arena: 4ĂFloat64(nItems) + 3ĂUint32(nItems) + 1ĂUint8(nItems) = 45ĂnItems bytes
const arena = new DemoArena(45 * nItems);
// Assign line counts using proportions from the SF=1 baseline (20k/45k/55k/60k).
const t2 = Math.round(nOrders * (20_000 / 60_000));
const t3 = Math.round(nOrders * (45_000 / 60_000));
const t4 = Math.round(nOrders * (55_000 / 60_000));
const counts = new Uint8Array(nOrders);
// Optimisation #5: typed-array permutation
const perm = permutation(rng, nOrders);
for (let i = 0; i < t2; ++i) {
counts[perm[i]] = 2;
}
for (let i = t2; i < t3; ++i) {
counts[perm[i]] = 3;
}
for (let i = t3; i < t4; ++i) {
counts[perm[i]] = 4;
}
for (let i = t4; i < nOrders; ++i) {
counts[perm[i]] = 5;
}
// Build cumulative start offsets
const orderStart = new Int32Array(nOrders);
for (let i = 1; i < nOrders; ++i) {
orderStart[i] = orderStart[i - 1] + counts[i - 1];
}
// Product lookup arrays
const prodIds = products.product_id;
const prodSubcats = products.subcategory;
const prodUoms = products.uom;
const prodPrices = products.list_price;
const nProds = prodIds.length;
// Index by subcat
const subToIdx: Record<string, number[]> = {};
for (const sc of SUBCATS) {
subToIdx[sc] = [];
}
for (let i = 0; i < nProds; ++i) {
subToIdx[prodSubcats[i]].push(i);
}
// Output arrays - all from arena. nItems is the exact count (pre-computed from
// line-count proportions), so no over-allocation or slicing needed.
const quantity = arena.float64(nItems);
const unit_price = arena.float64(nItems);
const discount_pct = arena.float64(nItems);
const tax_rate = arena.float64(nItems);
const order_item_id_indices = arena.uint32(nItems);
const order_item_id_vals: string[] = new Array(nItems);
const order_id_indices = arena.uint32(nItems);
const product_id_indices = arena.uint32(nItems);
const return_reason_indices = arena.uint8(nItems); // 0 = placeholder for null rows
const returned: boolean[] = new Array(nItems).fill(false);
let returnReasonNullCount = nItems; // start all-null, decrement as reasons are set
let ptr = 0;
// Product selection pass - uses pickCumW with pre-built SUBCAT_CUMW
// Scratch array - heap-allocated, collected after generation.
const productIdxArr = new Int32Array(nItems);
for (let oi = 0; oi < nOrders; ++oi) {
const c = counts[oi];
const dominant = pickCumW(rng, SUBCATS, SUBCAT_CUMW);
const pool = subToIdx[dominant];
productIdxArr[ptr] = pool[randInt(rng, 0, pool.length)];
for (let j = 1; j < c; ++j) {
productIdxArr[ptr + j] = rng() < 0.7 ? pool[randInt(rng, 0, pool.length)] : randInt(rng, 0, nProds);
}
ptr += c;
}
// Quantities, prices, discounts
ptr = 0;
for (let oi = 0; oi < nOrders; ++oi) {
const c = counts[oi];
// Look up customer segment via orders.customer_id index
const custIdx = orders.customer_id.indices[oi];
const segment = customers.segment[custIdx];
const pl = getDictVal(orders.price_list, oi);
const curr = getDictVal(orders.currency, oi);
const st = getDictVal(orders.status, oi);
const fx = curr === 'EUR' ? 1.12 : curr === 'USD' ? 1.25 : 1;
const plMult =
pl === 'Contract'
? randFloat(rng, 0.9, 0.98)
: pl === 'Volume'
? randFloat(rng, 0.88, 0.96)
: randFloat(rng, 0.98, 1.03);
for (let j = 0; j < c; ++j) {
const pidx = productIdxArr[ptr];
const subcat = prodSubcats[pidx];
const uom = prodUoms[pidx];
const base = prodPrices[pidx] * fx;
const qty = randInt(rng, 1, qtyMax(subcat, segment, uom) + 1);
const qtyBreak = 1 - Math.min(Math.max((qty - 10) / 200, 0), 0.18);
const noise = randFloat(rng, 0.985, 1.02);
const unitPriceVal = Math.round(base * plMult * qtyBreak * noise * 100) / 100;
let disc =
pl === 'Contract'
? randFloat(rng, 0.04, 0.14)
: pl === 'Volume'
? randFloat(rng, 0.06, 0.2)
: randFloat(rng, 0, 0.06);
disc += Math.min(Math.max((qty - 20) / 260, 0), 0.1) * randFloat(rng, 0.6, 1);
if (st === 'Cancelled') {
disc = Math.min(disc, randFloat(rng, 0, 0.04));
}
disc = Math.round(Math.min(disc, 0.55) * 10_000) / 10_000;
const taxRateVal = curr === 'USD' ? 0 : 0.2;
order_item_id_indices[ptr] = ptr;
order_item_id_vals[ptr] = `I${ptr + 1}`;
order_id_indices[ptr] = oi;
product_id_indices[ptr] = pidx;
quantity[ptr] = qty;
unit_price[ptr] = unitPriceVal;
discount_pct[ptr] = disc;
tax_rate[ptr] = taxRateVal;
++ptr;
}
}
// Adjust returnReasonNullCount to reflect actual allocated rows
returnReasonNullCount = nItems;
// Returns: only for 'Returned' orders
// Reusable scratch buffer (max 5 lines per order) avoids per-order allocation
const lineIdxsBuf = [0, 1, 2, 3, 4];
for (let oi = 0; oi < nOrders; ++oi) {
if (getDictVal(orders.status, oi) !== 'Returned') {
continue;
}
const c = counts[oi];
const start = orderStart[oi];
const k = rng() < 0.85 ? 1 : 2;
// Partial Fisher-Yates on first c slots of the reusable buffer
for (let j = 0; j < c; ++j) lineIdxsBuf[j] = j;
for (let j = c - 1; j > 0; --j) {
const r = randInt(rng, 0, j + 1);
const tmp = lineIdxsBuf[j];
lineIdxsBuf[j] = lineIdxsBuf[r];
lineIdxsBuf[r] = tmp;
}
for (let j = 0; j < k && j < c; ++j) {
const pos = start + lineIdxsBuf[j];
returned[pos] = true;
return_reason_indices[pos] = randInt(rng, 0, RETURN_REASONS.length);
--returnReasonNullCount;
}
// Ensure at least one line is returned
if (!returned[start]) {
returned[start] = true;
return_reason_indices[start] = randInt(rng, 0, RETURN_REASONS.length);
--returnReasonNullCount;
}
}
// Arena arrays are exact-sized - no slicing needed.
return {
order_item_id: {
indices: order_item_id_indices,
indexToValue: order_item_id_vals,
nullCount: 0,
},
order_id: {
indices: order_id_indices,
indexToValue: orders.order_id.indexToValue,
nullCount: 0,
},
product_id: {
indices: product_id_indices,
indexToValue: products.product_id,
nullCount: 0,
},
quantity,
unit_price,
discount_pct,
tax_rate,
returned,
return_reason: {
indices: return_reason_indices,
indexToValue: [...RETURN_REASONS],
nullCount: returnReasonNullCount,
},
};
}
// âââ 6) Shipments ââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ
export interface Shipment {
shipment_id: string;
order_id: string;
ship_datetime: string | null;
delivery_datetime: string | null;
carrier: string;
delayed: boolean;
}
export interface ShipmentData {
shipment_id: DictColumn;
order_id: DictColumn;
ship_datetime: Float64Array; // epoch ms, NaN for null (arena-backed)
delivery_datetime: Float64Array; // epoch ms, NaN for null (arena-backed)
carrier: DictColumn;
delayed: boolean[];
}
const CARRIERS = ['DHL', 'DPD', 'Royal Mail', 'UPS'] as const;
const CARRIER_W = [0.3, 0.3, 0.25, 0.15] as const;
// Pre-built cumulative weight array for carrier selection
const CARRIER_CUMW = buildCumW(CARRIER_W);
const MEAN_TRANSIT_DAYS: Record<string, number> = { GBP: 2.5, EUR: 3.0, USD: 3.5 };
const DELAY_THRESH: Record<string, number> = { GBP: 4.5, EUR: 4.5, USD: 5.5 };
// ââ Optimisation #7: pre-allocated arrays in genShipments ââ
function genShipments(rng: RNG, orders: OrderData, nShipments: number): ShipmentData {
// Per-table arena: 2ĂFloat64 + 2ĂUint32 + 1ĂUint8 = 25ĂnShipments bytes (upper bound)
const arena = new DemoArena(25 * nShipments);
const nOrders = orders.order_id.indices.length;
const idxReturned: number[] = [];
const idxProcessing: number[] = [];
const idxCompleted: number[] = [];
for (let i = 0; i < nOrders; ++i) {
const st = getDictVal(orders.status, i);
if (st === 'Returned') {
idxReturned.push(i);
} else if (st === 'Processing') {
idxProcessing.push(i);
} else if (st === 'Completed') {
idxCompleted.push(i);
}
}
const need = nShipments - idxReturned.length - idxProcessing.length;
shuffle(rng, idxCompleted);
const selCompleted = idxCompleted.slice(0, need);
const shipOrders = [...idxReturned, ...idxProcessing, ...selCompleted];
shuffle(rng, shipOrders);
const nShipOrders = shipOrders.length;
// Float64 arrays first for alignment, then Uint32, then Uint8.
const ship_datetime = arena.float64(nShipOrders);
const delivery_datetime = arena.float64(nShipOrders);
const shipment_id_indices = arena.uint32(nShipOrders);
const shipment_id_vals: string[] = new Array(nShipOrders);
const order_id_indices = arena.uint32(nShipOrders);
const carrier_indices = arena.uint8(nShipOrders);
const delayed: boolean[] = new Array(nShipOrders);
for (let i = 0; i < nShipOrders; ++i) {
const oi = shipOrders[i];
const carrIdx = pickCumWIdx(rng, CARRIER_CUMW);
const st = getDictVal(orders.status, oi);
const curr = getDictVal(orders.currency, oi);
shipment_id_indices[i] = i;
shipment_id_vals[i] = `SH${i + 1}`;
order_id_indices[i] = oi;
carrier_indices[i] = carrIdx;
// Some Processing orders not shipped yet
if (st === 'Processing' && rng() < 0.35) {
ship_datetime[i] = NaN;
delivery_datetime[i] = NaN;
delayed[i] = false;
continue;
}
const shipHours = st === 'Processing' ? randFloat(rng, 0, 36) : randFloat(rng, 2, 60);
const shipMs = orders.order_datetime[oi] + shipHours * 3_600_000;
const meanDays = MEAN_TRANSIT_DAYS[curr] ?? 3;
const transit = Math.min(Math.max(randNormal(rng, meanDays, 1.1), 0.6), 9);
const delivMs = shipMs + transit * 86_400_000;
const thresh = DELAY_THRESH[curr] ?? 5;
const month = msToMonth(shipMs);
const isDelayed = transit > thresh || rng() < (month === 11 || month === 12 ? 0.1 : 0.07);
ship_datetime[i] = shipMs;
delivery_datetime[i] = delivMs;
delayed[i] = isDelayed;
}
return {
shipment_id: { indices: shipment_id_indices, indexToValue: shipment_id_vals, nullCount: 0 },
order_id: { indices: order_id_indices, indexToValue: orders.order_id.indexToValue, nullCount: 0 },
ship_datetime,
delivery_datetime,
carrier: { indices: carrier_indices, indexToValue: [...CARRIERS], nullCount: 0 },
delayed,
};
}
// âââ Top-level entry ââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ
export interface DemoData {
stores: StoreData;
products: ProductData;
customers: CustomerData;
orders: OrderData;
order_items: OrderItemData;
shipments: ShipmentData;
}
/** Compute exact order-item count from line-count proportions (deterministic, no RNG). */
function computeItemCount(nOrders: number): number {
const t2 = Math.round(nOrders * (20_000 / 60_000));
const t3 = Math.round(nOrders * (45_000 / 60_000));
const t4 = Math.round(nOrders * (55_000 / 60_000));
return t2 * 2 + (t3 - t2) * 3 + (t4 - t3) * 4 + (nOrders - t4) * 5;
}
export function generateDemoData(
createRng: (seed: string) => () => number = () => () => window.agRandom(),
seed = SEED,
sf = SCALE_FACTOR
): DemoData {
const rng = createRng(seed);
const nProducts = Math.max(Math.round(N_PRODUCTS * sf), SUBCATS.length);
const nCustomers = Math.max(Math.round(N_CUSTOMERS * sf), 1);
const nOrders = Math.max(Math.round(N_ORDERS * sf), 1);
const nShipments = Math.round(N_SHIPMENTS * sf);
// Each generator creates its own per-table arena. This avoids the single
// upfront allocation spike (177MB at SF=0.5) that pushes V8 into large-object
// space and delays scavenges. Per-table arenas allow V8 to collect generation
// temporaries incrementally between tables.
tracingStart('gen:data');
tracingStart('gen:data:stores');
const stores = genStores(rng);
tracingEnd('gen:data:stores');
tracingMeasure('gen:data:stores', { rows: stores.store_id.length });
tracingStart('gen:data:products');
const products = genProducts(rng, nProducts);
tracingEnd('gen:data:products');
tracingMeasure('gen:data:products', { rows: nProducts });
tracingStart('gen:data:customers');
const customers = genCustomers(rng, nCustomers);
tracingEnd('gen:data:customers');
tracingMeasure('gen:data:customers', { rows: nCustomers });
tracingStart('gen:data:orders');
const orders = genOrders(rng, customers, stores, nOrders);
tracingEnd('gen:data:orders');
tracingMeasure('gen:data:orders', { rows: nOrders });
tracingStart('gen:data:order-items');
const order_items = genOrderItems(rng, orders, customers, products);
tracingEnd('gen:data:order-items');
tracingMeasure('gen:data:order-items', { rows: order_items.order_item_id.indices.length });
tracingStart('gen:data:shipments');
const shipments = genShipments(rng, orders, nShipments);
tracingEnd('gen:data:shipments');
tracingMeasure('gen:data:shipments', { rows: nShipments });
tracingEnd('gen:data');
tracingMeasure('gen:data');
return { stores, products, customers, orders, order_items, shipments };
}
// âââ Dictionary encoding ââââââââââââââââââââââââââââââââââââââââââââââââââââââ
/**
* A pre-built dictionary column: typed index array + vocabulary array.
* Passes directly to the engine's zero-copy ingestion path via `statsHints.indexToValue`.
*/
export interface DictColumn {
indices: Uint8Array | Uint16Array | Uint32Array;
indexToValue: string[];
nullCount: number;
}
/**
* Build a dictionary column from a string (or nullable string) array.
* Uses the smallest index type that fits the vocabulary size.
*/
export function buildDict(values: ReadonlyArray<string | null>): DictColumn {
const indexToValue: string[] = [];
const termMap = new Map<string, number>();
let nullCount = 0;
for (const v of values) {
if (v == null) {
++nullCount;
} else if (!termMap.has(v)) {
termMap.set(v, indexToValue.length);
indexToValue.push(v);
}
}
const size = indexToValue.length;
const IndexArray = size <= 256 ? Uint8Array : size <= 65_536 ? Uint16Array : Uint32Array;
const indices = new IndexArray(values.length);
for (let i = 0; i < values.length; ++i) {
const v = values[i];
if (v != null) indices[i] = termMap.get(v)!;
}
return { indices, indexToValue, nullCount };
}
/** Decode a single value from a dictionary column. */
export function getDictVal(col: DictColumn, i: number): string {
return col.indexToValue[col.indices[i]];
}
/** A table where string columns have been replaced with pre-built `DictColumn`s. */
export type EncodedTable = Record<string, DictColumn | ArrayLike<unknown>>;
/** All six demo tables with string columns pre-dictionary-encoded. */
export interface EncodedDemoData {
stores: EncodedTable;
products: EncodedTable;
customers: EncodedTable;
orders: EncodedTable;
order_items: EncodedTable;
shipments: EncodedTable;
}
function encodeStringCols(
struct: Record<string, ArrayLike<unknown>>,
stringFields: ReadonlyArray<string>
): EncodedTable {
const result: EncodedTable = { ...struct };
for (const f of stringFields) {
const col = struct[f];
if (col != null) {
result[f] = buildDict(col as ReadonlyArray<string | null>);
}
}
return result;
}
/**
* Pre-encode all string columns across the six demo tables into dictionary form.
* The result can be fed directly to `colsFromStruct` in `mainDemoData.ts`, which
* detects `DictColumn` values and supplies `statsHints.indexToValue` so the engine
* takes the zero-copy ingestion path instead of calling `buildStringColumn`.
*
* `orders`, `order_items`, and `shipments` already carry `DictColumn` fields built
* inline by the generators - only the three small dimension tables need encoding here.
*/
export function encodeDemoData(data: DemoData): EncodedDemoData {
return {
stores: encodeStringCols(data.stores as any, ['store_id', 'store_name', 'region', 'city', 'store_type']),
products: encodeStringCols(data.products as any, [
'product_id',
'product_name',
'category',
'subcategory',
'brand',
'uom',
'variant',
]),
customers: encodeStringCols(data.customers as any, [
'customer_id',
'customer_name',
'email',
'region',
'segment',
'industry',
]),
// Large tables already have DictColumn fields - pass through directly.
orders: data.orders as unknown as EncodedTable,
order_items: data.order_items as unknown as EncodedTable,
shipments: data.shipments as unknown as EncodedTable,
};
}
// âââ Arena allocator âââââââââââââââââââââââââââââââââââââââââââââââââââââââââ
//
// Generators allocate output typed arrays from a single pre-sized ArrayBuffer,
// keeping all generated data as views into one contiguous allocation. This
// reduces GC roots from ~45 separate ArrayBuffers to 1 - the arena itself.
//
// Scratch arrays (permutations, cumulative weight tables) remain heap-allocated
// and are collected normally after generation completes.
function alignUp(offset: number, alignment: number): number {
return (offset + alignment - 1) & ~(alignment - 1);
}
/**
* Bump allocator over a single ArrayBuffer. All typed arrays returned are
* views into the same backing store.
*/
export class DemoArena {
private readonly buffer: ArrayBuffer;
private offset = 0;
constructor(bytes: number) {
this.buffer = new ArrayBuffer(bytes);
}
float64(length: number): Float64Array {
this.offset = alignUp(this.offset, 8);
const view = new Float64Array(this.buffer, this.offset, length);
this.offset += length * 8;
return view;
}
uint32(length: number): Uint32Array {
this.offset = alignUp(this.offset, 4);
const view = new Uint32Array(this.buffer, this.offset, length);
this.offset += length * 4;
return view;
}
uint16(length: number): Uint16Array {
this.offset = alignUp(this.offset, 2);
const view = new Uint16Array(this.buffer, this.offset, length);
this.offset += length * 2;
return view;
}
uint8(length: number): Uint8Array {
const view = new Uint8Array(this.buffer, this.offset, length);
this.offset += length;
return view;
}
}
const traceEnabled = ((globalThis as any).agStudioDebug ?? []).includes('traceMarkers');
const tracingOptions = (label: string, details?: Record<string, unknown>) => {
const split = label.split(':');
const color = split[2] ? (split[3] ? 'tertiary' : 'secondary') : 'primary';
const tooltipDetails = Object.keys(details ?? {})
.map((k) => `${k}=${(details ?? {})[k]}`)
.join(':');
return {
...details,
devtools: {
dataType: 'track-entry',
color,
track: split[0],
tooltipText: `${label} (${tooltipDetails})`,
properties: details && Object.entries(details),
},
};
};
const noop = () => {};
export const tracingStart = traceEnabled
? (label: string, details?: Record<string, unknown>) => {
return performance.mark(`ag:${label}-start`, { ...details, detail: tracingOptions(label, details) });
}
: noop;
export const tracingEnd = traceEnabled
? (label: string, details?: Record<string, unknown>) => {
return performance.mark(`ag:${label}-end`, { ...details, detail: tracingOptions(label, details) });
}
: noop;
export const tracingMeasure = traceEnabled
? (label: string, details?: Record<string, unknown>) => {
const firstProp = details ? Object.values(details)[0] : null;
return performance.measure(`ag:${label}${firstProp ? `:${firstProp}` : ''}`, {
detail: tracingOptions(label, details),
start: `ag:${label}-start`,
end: `ag:${label}-end`,
});
}
: noop;
/**
* OpenAI Responses API adapter for AG Studio.
*
* This is example code - copy it into your project and adapt as needed.
* It maps between AG Studio's AI types and the OpenAI Responses API,
* handling encoding (AG â OpenAI), decoding (OpenAI â AG), and SSE streaming.
*/
import type {
AgAiAssistant,
AgAiConversationItem,
AgAiJsonFormat,
AgAiOutputContent,
AgAiOutputContentPart,
AgAiOutputItem,
AgAiOutputMessage,
AgAiReasoningItem,
AgAiRequest,
AgAiResponse,
AgAiResponseHandler,
AgAiStreamEvent,
AgAiStreamPartType,
AgAiStreamStatusEvent,
AgAiTextFormat,
AgToolSchema,
} from 'ag-studio';
// =============================================================================
// OpenAI Types (hand-written, minimal)
// =============================================================================
interface OpenAiAdapterOptions {
key?: string;
endpoint?: string;
model?: string;
organization?: string;
}
interface OpenAiConfig {
endpoint: string;
key?: string;
model: string;
organization?: string;
}
// =============================================================================
// JSON Schema â OpenAI strict-mode subset
// =============================================================================
//
// The Shape library emits JSON Schema 2020-12. OpenAI's Responses API in
// `strict: true` mode accepts only a narrow subset. This transform bridges the
// two so docs examples work against OpenAI without forcing Shape authors to
// know the quirks.
//
// What OpenAI accepts: object/array/string/number/integer/boolean/enum/anyOf,
// `$ref` + `$defs` (including recursive), `additionalProperties: false`, and
// the standard string/number/array constraint keywords. Every key in
// `properties` must appear in `required`; optional fields are encoded as a
// nullable type. Open-ended `additionalProperties: <schema>` (i.e. Shape's
// `s.record(...)`) is **not** representable.
type JsonSchema = Record<string, unknown>;
const BANNED_KEYWORDS = [
'allOf',
'not',
'oneOf',
'if',
'then',
'else',
'prefixItems',
'patternProperties',
'propertyNames',
'unevaluatedProperties',
'unevaluatedItems',
'dependentSchemas',
'dependentRequired',
'contains',
] as const;
function isSchema(value: unknown): value is JsonSchema {
return typeof value === 'object' && value !== null && !Array.isArray(value);
}
// Shape encodes "undefined" (used in `union(T, undefined)` to mark optionality) as the
// sentinel `{ not: {} }`. OpenAI doesn't allow `not`, so strip these from any `anyOf`
// branches; the surrounding object handler turns the remaining schema nullable for
// optional properties.
function isUndefinedSentinel(s: unknown): boolean {
if (!isSchema(s)) return false;
return Object.keys(s).length === 1 && isSchema(s.not) && Object.keys(s.not as JsonSchema).length === 0;
}
function stripUndefinedSentinel(schema: JsonSchema): JsonSchema {
if (!Array.isArray(schema.anyOf)) return schema;
const filtered = (schema.anyOf as unknown[]).filter((b) => !isUndefinedSentinel(b));
if (filtered.length === schema.anyOf.length) return schema;
if (filtered.length === 0) {
throw new Error('toOpenAiSchema: schema reduces to `undefined`-only - nothing to express');
}
const { anyOf: _, ...rest } = schema;
if (filtered.length === 1 && isSchema(filtered[0])) {
return { ...filtered[0], ...rest } as JsonSchema;
}
return { ...rest, anyOf: filtered as JsonSchema[] };
}
function inferTypeFromValue(v: unknown): string | undefined {
if (v === null) return 'null';
if (typeof v === 'string') return 'string';
if (typeof v === 'boolean') return 'boolean';
if (typeof v === 'number') return Number.isInteger(v) ? 'integer' : 'number';
return undefined;
}
function makeNullable(schema: JsonSchema): JsonSchema {
if (typeof schema.type === 'string') {
return schema.type === 'null' ? schema : { ...schema, type: [schema.type, 'null'] };
}
if (Array.isArray(schema.type)) {
return schema.type.includes('null') ? schema : { ...schema, type: [...schema.type, 'null'] };
}
if (Array.isArray(schema.anyOf)) {
const branches = schema.anyOf as JsonSchema[];
const hasNull = branches.some((b) => isSchema(b) && b.type === 'null');
return hasNull ? schema : { ...schema, anyOf: [...branches, { type: 'null' }] };
}
return { anyOf: [schema, { type: 'null' }] };
}
function transformSchema(schema: JsonSchema): JsonSchema {
schema = stripUndefinedSentinel(schema);
// OpenAI strict mode rejects any sibling keyword on `$ref` (description, examples, etc.).
// Shape authors apply per-callsite descriptions on the outside of the def - preserve `$ref`
// itself, drop everything else; the description lives inside the referenced `$def` via the
// first emission.
if ('$ref' in schema) {
const { $ref, $defs } = schema as JsonSchema & { $ref: unknown };
return $defs !== undefined ? { $ref, $defs } : { $ref };
}
for (const kw of BANNED_KEYWORDS) {
if (kw in schema) {
throw new Error(`toOpenAiSchema: '${kw}' is not supported by OpenAI strict mode`);
}
}
if ('const' in schema) {
const { const: literalValue, ...rest } = schema as JsonSchema & { const: unknown };
const inferred = inferTypeFromValue(literalValue);
const out: JsonSchema = { ...rest, enum: [literalValue] };
if (out.type == null && inferred != null) out.type = inferred;
return transformSchema(out);
}
const out: JsonSchema = { ...schema };
if (Array.isArray(out.anyOf)) {
out.anyOf = (out.anyOf as JsonSchema[]).map((branch) => (isSchema(branch) ? transformSchema(branch) : branch));
}
if (isSchema(out.$defs)) {
const transformedDefs: JsonSchema = {};
for (const [k, v] of Object.entries(out.$defs as JsonSchema)) {
transformedDefs[k] = isSchema(v) ? transformSchema(v) : v;
}
out.$defs = transformedDefs;
}
if (out.type === 'object' || isSchema(out.properties)) {
if ('additionalProperties' in out && out.additionalProperties !== false) {
throw new Error(
'toOpenAiSchema: open-ended `additionalProperties` (e.g. s.record(...)) cannot be expressed in OpenAI strict mode'
);
}
const properties = isSchema(out.properties) ? out.properties : {};
const required = new Set(Array.isArray(out.required) ? (out.required as string[]) : []);
const newProperties: JsonSchema = {};
for (const [key, propSchema] of Object.entries(properties)) {
const transformed = isSchema(propSchema) ? transformSchema(propSchema) : propSchema;
newProperties[key] = required.has(key)
? transformed
: isSchema(transformed)
? makeNullable(transformed)
: transformed;
}
out.properties = newProperties;
out.required = Object.keys(newProperties);
out.additionalProperties = false;
}
// Array items keep their real schema: only optional PROPERTIES need the required+nullable
// rewrite. Advertising nullable items invites the model to emit `[null]` for values the
// AG-side shapes reject.
if (isSchema(out.items)) {
out.items = transformSchema(out.items);
}
return out;
}
function toOpenAiSchema(schema: JsonSchema): JsonSchema {
if (Array.isArray(schema.anyOf) && schema.type !== 'object' && !isSchema(schema.properties)) {
throw new Error(
'toOpenAiSchema: root schema cannot be `anyOf` - wrap in an object (e.g. `s.object({ value: ... })`)'
);
}
return transformSchema(schema);
}
// =============================================================================
// Encoding: AG â OpenAI
// =============================================================================
function encodeConversationItems(items: AgAiConversationItem[]): unknown[] {
return items.map((item) => {
if (item.kind === 'input' && item.type === 'message') {
return {
type: 'message',
role: item.role,
status: item.status,
content: item.content.map((c) => {
switch (c.type) {
case 'text':
return { type: 'input_text', text: c.text };
case 'image':
return {
type: 'input_image',
detail: c.detail,
file_id: c.fileId ?? null,
image_url: c.imageUrl ?? null,
};
case 'file':
return {
type: 'input_file',
file_id: c.fileId ?? null,
file_data: c.fileData,
file_url: c.fileUrl,
filename: c.filename,
};
}
}),
};
}
if (item.type === 'function_call_output') {
return {
type: 'function_call_output',
call_id: item.callId,
output: item.output,
status: item.status,
};
}
if (item.kind === 'output' && item.type === 'message') {
return {
id: item.id,
type: 'message',
role: 'assistant',
status: item.status,
content: item.content.map((c) => {
if (c.type === 'text') {
return {
type: 'output_text',
text: c.text,
annotations: c.annotations.map((ann) => {
switch (ann.type) {
case 'file_path':
return { type: 'file_path', file_id: ann.fileId, index: ann.index };
case 'file_citation':
return {
type: 'file_citation',
file_id: ann.fileId,
index: ann.index,
filename: ann.filename,
};
case 'url_citation':
return {
type: 'url_citation',
url: ann.url,
start_index: ann.startIndex,
end_index: ann.endIndex,
title: ann.title,
};
case 'container_file_citation':
return {
type: 'container_file_citation',
container_id: ann.containerId,
file_id: ann.fileId,
start_index: ann.startIndex,
end_index: ann.endIndex,
filename: ann.filename,
};
}
}),
};
}
return { type: 'refusal', refusal: c.refusal };
}),
};
}
if (item.kind === 'output' && item.type === 'function_call') {
return {
id: item.id,
type: 'function_call',
call_id: item.callId,
name: item.name,
arguments: item.arguments,
status: item.status,
};
}
if (item.kind === 'output' && item.type === 'reasoning') {
return {
id: item.id,
type: 'reasoning',
summary: item.summary.map((s) => ({ type: 'summary_text', text: s.text })),
content: item.content?.map((c) => ({ type: 'reasoning_text', text: c.text })),
};
}
throw new Error(`Unknown conversation item type: ${(item as { type: string }).type}`);
});
}
// =============================================================================
// Decoding: OpenAI â AG
// =============================================================================
// `toOpenAiSchema` rewrites optional properties as required + nullable to satisfy
// OpenAI strict mode, so the model returns `null` for unset optionals. AG-side
// validation treats those fields as optional (not nullable), so strip `null`s
// from tool-call argument payloads on the way back. Only object PROPERTIES are
// stripped: a null array item is either a genuinely nullable value that must
// survive (e.g. a rank filter's `[10, null]` bounds) or invalid input that
// AG-side validation should report rather than have silently deleted.
function stripNulls(value: unknown): unknown {
if (Array.isArray(value)) return value.map((v) => stripNulls(v));
if (value !== null && typeof value === 'object') {
const out: Record<string, unknown> = {};
for (const [k, v] of Object.entries(value)) {
if (v === null) continue;
out[k] = stripNulls(v);
}
return out;
}
return value;
}
function stripNullsFromToolArgs(argsJson: string): string {
if (!argsJson) return argsJson;
let parsed: unknown;
try {
parsed = JSON.parse(argsJson);
} catch {
return argsJson;
}
return JSON.stringify(stripNulls(parsed));
}
function decodeAnnotations(annotations: any[]): any[] {
return (annotations ?? []).map((ann: any) => {
if (ann.type === 'file_path') {
return { type: 'file_path', fileId: ann.file_id, index: ann.index };
}
if (ann.type === 'file_citation') {
return { type: 'file_citation', fileId: ann.file_id, index: ann.index, filename: ann.filename };
}
if (ann.type === 'url_citation') {
return {
type: 'url_citation',
url: ann.url,
startIndex: ann.start_index,
endIndex: ann.end_index,
title: ann.title,
};
}
if (ann.type === 'container_file_citation') {
return {
type: 'container_file_citation',
containerId: ann.container_id,
fileId: ann.file_id,
startIndex: ann.start_index,
endIndex: ann.end_index,
filename: ann.filename,
};
}
return ann;
});
}
function decodeOutputContent(input: Record<string, any>): AgAiOutputContent {
if (input.type === 'output_text') {
return {
type: 'text',
text: input.text,
annotations: decodeAnnotations(input.annotations),
};
}
return input as AgAiOutputContent;
}
function decodeOutputItem(input: Record<string, any>): AgAiOutputItem {
switch (input.type) {
case 'message': {
const message: AgAiOutputMessage = {
id: input.id ?? '',
kind: 'output',
type: 'message',
role: 'assistant',
status: input.status ?? 'completed',
content: input.content.map(decodeOutputContent),
};
return message;
}
case 'function_call':
return {
id: input.id ?? '',
kind: 'output',
type: 'function_call',
callId: input.call_id,
name: input.name,
arguments: stripNullsFromToolArgs(input.arguments ?? ''),
status: input.status,
};
case 'reasoning': {
const reasoning: AgAiReasoningItem = {
id: input.id ?? '',
kind: 'output',
type: 'reasoning',
summary: input.summary.map((s: any) => ({ type: 'summary', text: s.text })),
content: input.content?.map((c: any) => ({ type: 'text', text: c.text })),
};
return reasoning;
}
default:
throw new Error(`Unknown output item type: ${input.type}`);
}
}
function decodeResponse(input: Record<string, any>): AgAiResponse {
return {
id: input.id,
createdAt: input.created_at,
incompleteDetails: input.incomplete_details ? { reason: input.incomplete_details.reason } : undefined,
output: input.output.map(decodeOutputItem),
status: input.status,
error: input.error ? { code: input.error.code, message: input.error.message } : undefined,
};
}
function decodeContentPart(input: Record<string, any>): AgAiOutputContentPart {
if (input.type === 'output_text') {
return {
type: 'text',
text: input.text,
annotations: decodeAnnotations(input.annotations),
};
}
if (input.type === 'reasoning_text') {
return { type: 'text', text: input.text };
}
if (input.type === 'summary_text') {
return { type: 'summary', text: input.text };
}
return input as AgAiOutputContentPart;
}
const STATUS_EVENT_MAP: Record<string, AgAiStreamStatusEvent['event']> = {
'response.created': 'created',
'response.in_progress': 'in_progress',
'response.completed': 'completed',
'response.failed': 'failed',
'response.incomplete': 'incomplete',
'response.queued': 'queued',
};
function decodeStreamEvent(input: Record<string, any>): AgAiStreamEvent {
const seq = input.sequence_number ?? 0;
switch (input.type) {
// Status events
case 'response.created':
case 'response.in_progress':
case 'response.completed':
case 'response.failed':
case 'response.incomplete':
case 'response.queued':
return {
type: 'status',
event: STATUS_EVENT_MAP[input.type],
response: decodeResponse(input.response),
sequence_number: seq,
};
// Error event
case 'error':
return {
type: 'error',
event: 'api',
code: input.code ?? null,
message: input.message,
param: input.param ?? null,
sequence_number: seq,
};
// Item events
case 'response.output_item.added':
return {
type: 'item',
event: 'added',
item: decodeOutputItem(input.item),
itemIndex: input.output_index,
sequence_number: seq,
};
case 'response.output_item.done':
return {
type: 'item',
event: 'done',
item: decodeOutputItem(input.item),
itemIndex: input.output_index,
sequence_number: seq,
};
// Content part events
case 'response.content_part.added':
return {
type: 'part',
event: 'added',
itemId: input.item_id,
itemIndex: input.output_index,
partIndex: input.content_index,
part: decodeContentPart(input.part),
sequence_number: seq,
};
case 'response.content_part.done':
return {
type: 'part',
event: 'done',
itemId: input.item_id,
itemIndex: input.output_index,
partIndex: input.content_index,
part: decodeContentPart(input.part),
sequence_number: seq,
};
// Text delta/done
case 'response.output_text.delta':
return {
type: 'delta',
event: 'update',
itemId: input.item_id,
itemIndex: input.output_index,
partIndex: input.content_index,
partType: 'text' as AgAiStreamPartType,
content: input.delta,
sequence_number: seq,
};
case 'response.output_text.done':
return {
type: 'delta',
event: 'done',
itemId: input.item_id,
itemIndex: input.output_index,
partIndex: input.content_index,
partType: 'text' as AgAiStreamPartType,
content: input.text,
sequence_number: seq,
};
// Refusal delta/done
case 'response.refusal.delta':
return {
type: 'delta',
event: 'update',
itemId: input.item_id,
itemIndex: input.output_index,
partIndex: input.content_index,
partType: 'refusal' as AgAiStreamPartType,
content: input.delta,
sequence_number: seq,
};
case 'response.refusal.done':
return {
type: 'delta',
event: 'done',
itemId: input.item_id,
itemIndex: input.output_index,
partIndex: input.content_index,
partType: 'refusal' as AgAiStreamPartType,
content: input.refusal,
sequence_number: seq,
};
// Function call arguments delta/done
case 'response.function_call_arguments.delta':
return {
type: 'delta',
event: 'update',
itemId: input.item_id,
itemIndex: input.output_index,
partIndex: 0,
partType: 'arguments' as AgAiStreamPartType,
content: input.delta,
sequence_number: seq,
};
case 'response.function_call_arguments.done':
return {
type: 'delta',
event: 'done',
itemId: input.item_id,
itemIndex: input.output_index,
partIndex: 0,
partType: 'arguments' as AgAiStreamPartType,
content: stripNullsFromToolArgs(input.arguments),
sequence_number: seq,
};
// Reasoning delta/done
case 'response.reasoning_text.delta':
return {
type: 'delta',
event: 'update',
itemId: input.item_id,
itemIndex: input.output_index,
partIndex: input.content_index,
partType: 'reasoning' as AgAiStreamPartType,
content: input.delta,
sequence_number: seq,
};
case 'response.reasoning_text.done':
return {
type: 'delta',
event: 'done',
itemId: input.item_id,
itemIndex: input.output_index,
partIndex: input.content_index,
partType: 'reasoning' as AgAiStreamPartType,
content: input.text,
sequence_number: seq,
};
// Reasoning summary delta/done
case 'response.reasoning_summary_text.delta':
return {
type: 'delta',
event: 'update',
itemId: input.item_id,
itemIndex: input.output_index,
partIndex: input.summary_index,
partType: 'reasoning_summary' as AgAiStreamPartType,
content: input.delta,
sequence_number: seq,
};
case 'response.reasoning_summary_text.done':
return {
type: 'delta',
event: 'done',
itemId: input.item_id,
itemIndex: input.output_index,
partIndex: input.summary_index,
partType: 'reasoning_summary' as AgAiStreamPartType,
content: input.text,
sequence_number: seq,
};
// Reasoning summary part events
case 'response.reasoning_summary_part.added':
return {
type: 'part',
event: 'added',
itemId: input.item_id,
itemIndex: input.output_index,
partIndex: input.summary_index,
part: { type: 'summary', text: input.part.text },
sequence_number: seq,
};
case 'response.reasoning_summary_part.done':
return {
type: 'part',
event: 'done',
itemId: input.item_id,
itemIndex: input.output_index,
partIndex: input.summary_index,
part: { type: 'summary', text: input.part.text },
sequence_number: seq,
};
default:
throw new Error(`Unknown stream event type: ${input.type}`);
}
}
// =============================================================================
// Stream Processor
// =============================================================================
async function* streamOpenAi(
config: OpenAiConfig,
requestBody: Record<string, unknown>
): AsyncIterableIterator<AgAiStreamEvent> {
try {
const response = await fetch(`${config.endpoint}/responses`, {
method: 'POST',
headers: {
'Content-Type': 'application/json',
...(config.key && { Authorization: `Bearer ${config.key}` }),
...(config.organization && { 'OpenAI-Organization': config.organization }),
},
body: JSON.stringify(requestBody),
});
if (!response.ok) {
const error = await response.json().catch(() => ({}));
yield {
type: 'error',
sequence_number: 0,
event: 'api',
code: error.error?.code || 'api_error',
message: error.error?.message || `HTTP ${response.status}: ${response.statusText}`,
param: error.error?.param || '',
};
return;
}
const reader = response.body!.getReader();
const decoder = new TextDecoder();
let buffer = '';
while (true) {
const { done, value } = await reader.read();
if (done) break;
buffer += decoder.decode(value, { stream: true });
const messages = buffer.split('\n\n');
buffer = messages.pop() || '';
for (const message of messages) {
const lines = message.split('\n').filter((line) => line.trim() !== '');
for (const line of lines) {
if (line.startsWith('data: ')) {
const data = line.slice(6);
if (data === '[DONE]') break;
try {
const parsed = JSON.parse(data);
if (parsed.type === 'keepalive') continue;
yield decodeStreamEvent(parsed);
} catch (error) {
yield {
type: 'error',
sequence_number: 0,
event: 'api',
code: 'stream_error',
message: error instanceof Error ? error.message : String(error),
param: '',
};
}
}
}
}
}
if (buffer.trim()) {
const lines = buffer.split('\n').filter((line) => line.trim() !== '');
for (const line of lines) {
if (line.startsWith('data: ')) {
const data = line.slice(6);
if (data !== '[DONE]') {
try {
const parsed = JSON.parse(data);
if (parsed.type === 'keepalive') continue;
yield decodeStreamEvent(parsed);
} catch {
// Tail buffer parse failures are expected
}
}
}
}
}
} catch (error) {
yield {
type: 'error',
sequence_number: 0,
event: 'api',
code: 'stream_error',
message: error instanceof Error ? error.message : String(error),
param: '',
};
}
}
// =============================================================================
// Request Builder
// =============================================================================
function prepareToolChoice(
toolChoice: AgAiRequest['toolChoice']
): 'auto' | 'none' | 'required' | { type: 'function'; name: string } | undefined {
if (!toolChoice) return undefined;
if (typeof toolChoice === 'string') return toolChoice;
return { type: 'function', name: toolChoice.name };
}
function prepareResponseFormat(format: AgAiTextFormat | AgAiJsonFormat): Record<string, unknown> {
if (format.type === 'text') return { type: 'text' };
return {
type: 'json_schema',
name: format.name,
description: format.description,
schema: toOpenAiSchema(format.schema as JsonSchema),
strict: true,
};
}
function runRequest(config: OpenAiConfig, request: AgAiRequest): AgAiResponseHandler {
const { tools = [], toolChoice, responseFormat, input, ...rest } = request;
const requestBody: Record<string, unknown> = {
...rest,
input: encodeConversationItems(input),
model: config.model,
stream: true,
tools: tools.map((tool: AgToolSchema) => ({
type: 'function' as const,
name: tool.name,
description: tool.description,
parameters: toOpenAiSchema(tool.parameters as unknown as JsonSchema),
strict: true,
})),
tool_choice: prepareToolChoice(toolChoice),
text: { format: prepareResponseFormat(responseFormat!) },
reasoning: { effort: 'medium' },
parallel_tool_calls: true,
};
const streamIterator = streamOpenAi(config, requestBody);
let finalResponse: AgAiResponse | null = null;
let resolveComplete: (response: AgAiResponse) => void;
let rejectComplete: (error: Error) => void;
const completePromise = new Promise<AgAiResponse>((resolve, reject) => {
resolveComplete = resolve;
rejectComplete = reject;
});
async function* wrappedIterator(): AsyncIterableIterator<AgAiStreamEvent> {
try {
for await (const event of streamIterator) {
if (event.type === 'status' && event.event === 'completed') {
finalResponse = event.response;
} else if (event.type === 'error') {
rejectComplete(new Error(`${event.code}: ${event.message}`));
}
yield event;
}
if (finalResponse) {
resolveComplete(finalResponse);
} else {
rejectComplete(new Error('Stream completed without final response'));
}
} catch (error) {
rejectComplete(error instanceof Error ? error : new Error(String(error)));
throw error;
}
}
const wrapped = wrappedIterator();
return {
stream: { [Symbol.asyncIterator]: () => wrapped },
complete: completePromise,
};
}
// =============================================================================
// Factory Function
// =============================================================================
export function openaiAdapter(options: OpenAiAdapterOptions): AgAiAssistant {
const config: OpenAiConfig = {
endpoint: options.endpoint ?? 'https://api.openai.com/v1',
key: options.key,
model: options.model ?? 'gpt-5.4-mini',
organization: options.organization,
};
return {
executeTurn: (request: AgAiRequest) => runRequest(config, request),
};
}
The AgAiAssistant Interface Copy Link
The adapter is a plain object. Its one required method is executeTurn.
Execute a single turn of conversation with the AI. A turn consists of sending input and receiving a streamed response.
|
The set of agents the active runtime runs â each agent is instructions + tools + a delegation graph. When omitted, the built-in runtime uses AG's default agents. Supply your own (compose with agStudioDefaultAgents to keep AG's) to fully control the set. This is orchestration policy interpreted by the active runtime: the built-in runtime runs these as its agents; a custom runtime may own its own agents and ignore this field.
|
Type of the agent the conversation starts from. Defaults to 'lead'. Interpreted by the active runtime alongside agents.
|
The agents and primaryAgent fields configure the agents the built-in runtime uses - see Custom Agents.
executeTurn Copy Link
executeTurn is called each time Studio needs an AI response. It receives an AgAiRequest and must return an AgAiResponseHandler synchronously. The handler exposes a live stream and a completion promise.
The Request Copy Link
Each call to executeTurn receives everything the model needs for one turn.
Conversation history to send to the AI, providing context.
|
System instructions for this specific turn, overriding defaults.
|
Tools available for the AI to use during this turn.
|
Strategy for how the AI should choose tools. |
Output format configuration controlling response structure.
|
The Response Copy Link
executeTurn returns an AgAiResponseHandler - a stream of incremental events and a complete promise that resolves with the final response.
Async iterable of stream events for real-time updates. Events are yielded as they arrive from the AI provider.
|
Promise that resolves when the response is fully complete. Contains the final, consolidated response data.
|
Stream Events Copy Link
The stream yields AgAiStreamEvent values. Each has a type and an event discriminator:
| Type | Event | Description |
|---|---|---|
status | created | The provider has created the response object. |
status | in_progress | The model is actively generating. |
status | completed | Generation finished. Includes the final AgAiResponse. |
status | failed | Generation failed. |
error | api, network, timeout, etc. | An error occurred. Includes code and message. |
item | added | A new output item (message, tool call, reasoning) started. |
item | done | An output item finished. |
part | added | A content part within an item started. |
part | done | A content part finished. |
delta | update | Incremental content to append. |
delta | done | Final content for a part. |
Tool Calls Copy Link
The adapter does not execute tools. It only:
- Passes the
AgToolSchema[]inrequest.toolsto the LLM. - Relays the tool-call output items from the LLM back through the stream.
The runtime intercepts those tool calls, executes them, and feeds the results back as function_call_output items on the next turn. Your adapter never needs to know what view_schema or configure_widget do.
Keeping Keys Off the Client Copy Link
executeTurn runs in the browser, so calling a provider directly exposes your API key. For production, point executeTurn at your own backend endpoint instead: forward the AgAiRequest, call the provider server-side with your secret key, and stream the response back. The adapter contract is unchanged - only the URL it calls differs.
Interface Reference Copy Link
Unique identifier for this response.
|
Timestamp when the response was created (milliseconds since epoch).
|
Error details if the response failed.
|
Details about why the response was incomplete. Present when the AI couldn't fully complete its response.
|
Output items produced by the AI (messages, tool calls, reasoning).
|
Current status of the response.
|
Tool name.
|
Human-readable description for the LLM.
|
JSON Schema describing the tool's parameters.
|
Next Steps Copy Link
- Module Setup - Register the module and show the panel.
- Default Agents - The agents and tools the runtime drives.