Static Report Generation

import { IngestPipeline } from "@514labs/moose-lib"; // Define the data model - matches store POS export formatexport interface SalesTransaction {  // Unique identifier  transactionId: string;  // e.g., "TXN-W001-20250115-0001"   // Timestamp - the star of time-series analytics  saleTimestamp: Date;   // Store & location  storeId: string;             // e.g., "STORE-W001"  region: string;              // West, East, Central, South   // Product info  productId: string;  productCategory: string;     // Electronics, Clothing, Food, Home, Sports   // Customer (optional - from loyalty card)  customerId: string;   // Transaction amounts  quantity: number;  unitPrice: number;  totalAmount: number;} // IMPORTANT: Always include the type parameter <SalesTransaction> - it's required// for the compiler plugin to generate the correct schemaexport const salesPipeline = new IngestPipeline<SalesTransaction>("sales", {  ingestApi: true,  stream: true,  table: {    // Time-first ordering for efficient range queries    // The first field becomes the primary key in ClickHouse    orderByFields: ["saleTimestamp", "transactionId"],  }});

export * from "./ingest/SalesTransaction";

✓ Detected model changes✓ Created table: sales✓ Created stream: sales✓ Registered API endpoint: /ingest/sales

import Papa from "papaparse";import fs from "fs";import path from "path"; // Note: saleTimestamp can be string or Date because papaparse dynamicTyping// converts ISO date strings to Date objectsinterface SalesTransaction {  transactionId: string;  saleTimestamp: string | Date;  storeId: string;  region: string;  productId: string;  productCategory: string;  customerId: string | null;  // null for customers without loyalty cards  quantity: number;  unitPrice: number;  totalAmount: number;} async function loadFile(filePath: string): Promise<void> {  const ext = path.extname(filePath).toLowerCase();  const content = fs.readFileSync(filePath, "utf-8");   let records: SalesTransaction[];   if (ext === ".csv") {    const { data } = Papa.parse<SalesTransaction>(content, {      header: true,      dynamicTyping: true,    });    // Filter empty rows and normalize fields    records = data      .filter((r) => r.transactionId)      .map((r) => ({        ...r,        // papaparse dynamicTyping converts Date objects - convert back to ISO string        saleTimestamp: r.saleTimestamp instanceof Date          ? r.saleTimestamp.toISOString()          : r.saleTimestamp,        // papaparse dynamicTyping converts empty CSV fields to null        // but the API expects empty strings for optional string fields        customerId: r.customerId ?? "",      }));  } else if (ext === ".json") {    const parsed = JSON.parse(content);    const rawRecords: SalesTransaction[] = Array.isArray(parsed) ? parsed : [parsed];    // Apply same normalization as CSV: filter empty rows and normalize fields    records = rawRecords      .filter((r) => r.transactionId)      .map((r) => ({        ...r,        // Normalize null customerId to empty string (API expects string, not null)        customerId: r.customerId ?? "",      }));  } else {    throw new Error(`Unsupported file format: ${ext}`);  }   console.log(`Parsed ${records.length} records from ${filePath}`);   // Send records to ingest API (collecting errors for partial failure handling)  const errors: Array<{ index: number; error: string }> = [];  for (let i = 0; i < records.length; i++) {    const response = await fetch("http://localhost:4000/ingest/sales", {      method: "POST",      headers: { "Content-Type": "application/json" },      body: JSON.stringify(records[i]),    });     if (!response.ok) {      errors.push({ index: i, error: response.statusText });    }  }   if (errors.length > 0) {    console.error(`Failed to ingest ${errors.length} records:`, errors);  }  console.log(`Successfully loaded ${records.length - errors.length} of ${records.length} records`);} // CLI executionconst filePath = process.argv[2];if (!filePath) {  console.error("Usage: pnpm exec ts-node scripts/load-file.ts <path-to-file>");  process.exit(1);} loadFile(filePath).catch(console.error);

transactionId,saleTimestamp,storeId,region,customerId,productId,productCategory,quantity,unitPrice,totalAmountTXN-W001-20250115-0001,2025-01-15T09:15:00Z,STORE-W001,West,CUST-1001,PROD-ELE-001,Electronics,2,299.99,599.98TXN-W001-20250115-0002,2025-01-15T09:32:00Z,STORE-W001,West,CUST-1002,PROD-CLO-015,Clothing,1,49.99,49.99TXN-W001-20250115-0003,2025-01-15T10:05:00Z,STORE-W001,West,,PROD-FOO-042,Food,3,12.99,38.97TXN-W001-20250115-0004,2025-01-15T10:45:00Z,STORE-W001,West,CUST-1003,PROD-HOM-008,Home,1,89.99,89.99TXN-W001-20250115-0005,2025-01-15T11:20:00Z,STORE-W001,West,CUST-1004,PROD-SPO-022,Sports,2,34.99,69.98TXN-W001-20250115-0006,2025-01-15T12:00:00Z,STORE-W001,West,,PROD-ELE-055,Electronics,1,149.99,149.99TXN-W001-20250115-0007,2025-01-15T13:30:00Z,STORE-W001,West,CUST-1005,PROD-CLO-033,Clothing,3,29.99,89.97TXN-W001-20250115-0008,2025-01-15T14:15:00Z,STORE-W001,West,CUST-1001,PROD-FOO-018,Food,5,8.99,44.95TXN-W001-20250115-0009,2025-01-15T15:45:00Z,STORE-W001,West,CUST-1006,PROD-HOM-041,Home,1,199.99,199.99TXN-W001-20250115-0010,2025-01-15T16:30:00Z,STORE-W001,West,,PROD-ELE-012,Electronics,1,599.99,599.99

Parsed 10 records from sample-data/west-store-001.csvSuccessfully loaded 10 records

import { MaterializedView, sql } from "@514labs/moose-lib";import { salesPipeline } from "../ingest/SalesTransaction"; // Define the aggregated data modelexport interface DailySalesSummary {  saleDate: Date;  region: string;  productCategory: string;  transactionCount: number;  totalQuantity: number;  totalRevenue: number;} // Get table reference from pipeline (with null check for TypeScript)const salesTable = salesPipeline.table;if (!salesTable) {  throw new Error("Sales table not configured in pipeline");} // Create materialized view with auto-created target tableexport const dailySalesMV = new MaterializedView<DailySalesSummary>({  tableName: "DailySalesSummary",  materializedViewName: "DailySalesSummary_MV",  orderByFields: ["saleDate", "region", "productCategory"],  selectTables: [salesTable],  selectStatement: sql`    SELECT      toStartOfDay(saleTimestamp) AS saleDate,      region,      productCategory,      count() AS transactionCount,      sum(quantity) AS totalQuantity,      sum(totalAmount) AS totalRevenue    FROM ${salesTable}    GROUP BY saleDate, region, productCategory  `,});

export * from "./ingest/SalesTransaction";export * from "./views/DailySalesSummary";

import { Api } from "@514labs/moose-lib";import { dailySalesMV } from "../views/DailySalesSummary"; // Define query parametersexport interface SalesReportParams {  startDate: string;   // Format: YYYY-MM-DD  endDate: string;     // Format: YYYY-MM-DD  region?: string;     // Optional filter} // Define response interfaceexport interface SalesReportResponse {  saleDate: string;  region: string;  productCategory: string;  transactionCount: number;  totalQuantity: number;  totalRevenue: number;} // Create the API endpointexport const salesReportApi = new Api<SalesReportParams, SalesReportResponse[]>(  "sales-report",  async ({ startDate, endDate, region }, { client, sql }) => {    // Access the target table from the materialized view    const targetTable = dailySalesMV.targetTable;     // Build query with optional region filter    // Note: We use separate queries for clarity. Production code could    // consolidate these using conditional query building.    let query;    if (region) {      query = sql`        SELECT          saleDate,          region,          productCategory,          sum(transactionCount) AS transactionCount,          sum(totalQuantity) AS totalQuantity,          sum(totalRevenue) AS totalRevenue        FROM ${targetTable}        WHERE saleDate BETWEEN ${startDate} AND ${endDate}          AND region = ${region}        GROUP BY saleDate, region, productCategory        ORDER BY saleDate DESC, totalRevenue DESC      `;    } else {      query = sql`        SELECT          saleDate,          region,          productCategory,          sum(transactionCount) AS transactionCount,          sum(totalQuantity) AS totalQuantity,          sum(totalRevenue) AS totalRevenue        FROM ${targetTable}        WHERE saleDate BETWEEN ${startDate} AND ${endDate}        GROUP BY saleDate, region, productCategory        ORDER BY saleDate DESC, totalRevenue DESC      `;    }     const result = await client.query.execute<SalesReportResponse>(query);    return result.json();  });

export * from "./ingest/SalesTransaction";export * from "./views/DailySalesSummary";export * from "./apis/salesReport";

[  {    "saleDate": "2025-01-15",    "region": "West",    "productCategory": "Electronics",    "transactionCount": 3,    "totalQuantity": 4,    "totalRevenue": 1349.96  },  {    "saleDate": "2025-01-15",    "region": "West",    "productCategory": "Clothing",    "transactionCount": 2,    "totalQuantity": 4,    "totalRevenue": 139.96  }]

import { TDocumentDefinitions } from "pdfmake/interfaces"; // Use require for pdfmake as it doesn't have proper ESM exportsconst pdfmake = require("pdfmake"); // Use standard PDF fonts (no external files needed)// Note: In pdfmake 0.3, these are built-in standard fontsconst fonts = {  Roboto: {    normal: "Helvetica",    bold: "Helvetica-Bold",    italics: "Helvetica-Oblique",    bolditalics: "Helvetica-BoldOblique",  },}; // Register fonts with pdfmake (required for pdfmake 0.3)pdfmake.addFonts(fonts); // Report data interface (matches API response)interface SalesReportRow {  saleDate: string;  region: string;  productCategory: string;  transactionCount: number;  totalQuantity: number;  totalRevenue: number;} async function fetchReportData(  startDate: string,  endDate: string): Promise<SalesReportRow[]> {  const url = `http://localhost:4000/consumption/sales-report?startDate=${startDate}&endDate=${endDate}`;  const response = await fetch(url);   if (!response.ok) {    throw new Error(`API request failed: ${response.statusText}`);  }   return response.json();} function aggregateByRegion(  data: SalesReportRow[]): Map<string, { revenue: number; transactions: number }> {  const regionMap = new Map<string, { revenue: number; transactions: number }>();   for (const row of data) {    const existing = regionMap.get(row.region) || { revenue: 0, transactions: 0 };    regionMap.set(row.region, {      revenue: existing.revenue + row.totalRevenue,      transactions: existing.transactions + row.transactionCount,    });  }   return regionMap;} function aggregateByCategory(  data: SalesReportRow[]): Map<string, { revenue: number; transactions: number }> {  const categoryMap = new Map<string, { revenue: number; transactions: number }>();   for (const row of data) {    const existing = categoryMap.get(row.productCategory) || {      revenue: 0,      transactions: 0,    };    categoryMap.set(row.productCategory, {      revenue: existing.revenue + row.totalRevenue,      transactions: existing.transactions + row.transactionCount,    });  }   return categoryMap;} function formatCurrency(amount: number): string {  return `$${amount.toLocaleString("en-US", { minimumFractionDigits: 2, maximumFractionDigits: 2 })}`;} async function generatePdf(  data: SalesReportRow[],  reportDate: string,  outputPath: string): Promise<void> {   // Calculate totals  const totalRevenue = data.reduce((sum, row) => sum + row.totalRevenue, 0);  const totalTransactions = data.reduce(    (sum, row) => sum + row.transactionCount,    0  );   // Aggregate by region and category  const regionData = aggregateByRegion(data);  const categoryData = aggregateByCategory(data);   // Sort regions by revenue descending  const sortedRegions = Array.from(regionData.entries()).sort(    (a, b) => b[1].revenue - a[1].revenue  );   // Sort categories by revenue descending  const sortedCategories = Array.from(categoryData.entries()).sort(    (a, b) => b[1].revenue - a[1].revenue  );   const docDefinition: TDocumentDefinitions = {    content: [      // Header      { text: "APEX RETAIL", style: "companyName" },      { text: "Daily Sales Summary", style: "reportTitle" },      { text: reportDate, style: "reportDate" },      {        canvas: [          { type: "line", x1: 0, y1: 5, x2: 515, y2: 5, lineWidth: 1 },        ],      },       // Executive Summary      {        text: "EXECUTIVE SUMMARY",        style: "sectionHeader",        margin: [0, 15, 0, 10],      },      {        columns: [          {            text: [              { text: "Total Revenue: ", bold: true },              formatCurrency(totalRevenue),            ],          },          {            text: [              { text: "Transactions: ", bold: true },              totalTransactions.toLocaleString(),            ],          },        ],        margin: [0, 0, 0, 15],      },       // Revenue by Region      {        text: "REVENUE BY REGION",        style: "sectionHeader",        margin: [0, 10, 0, 10],      },      {        table: {          headerRows: 1,          widths: ["*", "auto", "auto", "auto"],          body: [            [              { text: "Region", style: "tableHeader" },              { text: "Revenue", style: "tableHeader" },              { text: "Transactions", style: "tableHeader" },              { text: "Share", style: "tableHeader" },            ],            ...sortedRegions.map(([region, stats]) => [              region,              { text: formatCurrency(stats.revenue), alignment: "right" as const },              { text: stats.transactions.toLocaleString(), alignment: "right" as const },              {                text: `${totalRevenue > 0 ? ((stats.revenue / totalRevenue) * 100).toFixed(1) : "0.0"}%`,                alignment: "right" as const,              },            ]),          ],        },        layout: "lightHorizontalLines",      },       // Revenue by Category      {        text: "REVENUE BY CATEGORY",        style: "sectionHeader",        margin: [0, 20, 0, 10],      },      {        table: {          headerRows: 1,          widths: ["*", "auto", "auto", "auto"],          body: [            [              { text: "Category", style: "tableHeader" },              { text: "Revenue", style: "tableHeader" },              { text: "Transactions", style: "tableHeader" },              { text: "Share", style: "tableHeader" },            ],            ...sortedCategories.map(([category, stats]) => [              category,              { text: formatCurrency(stats.revenue), alignment: "right" as const },              { text: stats.transactions.toLocaleString(), alignment: "right" as const },              {                text: `${totalRevenue > 0 ? ((stats.revenue / totalRevenue) * 100).toFixed(1) : "0.0"}%`,                alignment: "right" as const,              },            ]),          ],        },        layout: "lightHorizontalLines",      },    ],     footer: {      text: `Generated: ${new Date().toISOString()} | Apex Retail Confidential`,      style: "footer",      margin: [40, 0],    },     styles: {      companyName: { fontSize: 24, bold: true, alignment: "center" },      reportTitle: {        fontSize: 16,        alignment: "center",        margin: [0, 5, 0, 5],      },      reportDate: { fontSize: 12, alignment: "center", color: "gray" },      sectionHeader: { fontSize: 12, bold: true },      tableHeader: { bold: true, fillColor: "#eeeeee" },      footer: { fontSize: 8, color: "gray", alignment: "center" },    },    defaultStyle: { font: "Roboto", fontSize: 10 },    pageMargins: [40, 40, 40, 60],  };   // Create PDF using pdfmake 0.3 API  const pdf = pdfmake.createPdf(docDefinition);   // Use the promise-based write() method (pdfmake 0.3+)  await pdf.write(outputPath);  console.log(`PDF report saved to ${outputPath}`);} // Main executionasync function main() {  const startDate = process.argv[2] || "2025-01-01";  const endDate = process.argv[3] || "2025-12-31";  const outputPath = process.argv[4] || "apex-retail-report.pdf";   console.log(`Fetching report data from ${startDate} to ${endDate}...`);   try {    const data = await fetchReportData(startDate, endDate);     if (data.length === 0) {      console.log("No data found for the specified date range.");      return;    }     console.log(`Generating PDF with ${data.length} data rows...`);    await generatePdf(data, `${startDate} to ${endDate}`, outputPath);  } catch (error) {    console.error("Error generating report:", error);    process.exit(1);  }} main();

Fetching report data from 2025-01-15 to 2025-01-15...Generating PDF with 5 data rows...PDF report saved to january-15-report.pdf

import { WebApp, getMooseUtils } from "@514labs/moose-lib";import { IncomingMessage, ServerResponse } from "http";import { parse as parseUrl } from "url";import { TDocumentDefinitions } from "pdfmake/interfaces";import { dailySalesMV } from "../views/DailySalesSummary"; // Use require for pdfmake as it doesn't have proper ESM exportsconst pdfmake = require("pdfmake"); // Use standard PDF fonts (no external files needed)const fonts = {  Roboto: {    normal: "Helvetica",    bold: "Helvetica-Bold",    italics: "Helvetica-Oblique",    bolditalics: "Helvetica-BoldOblique",  },};pdfmake.addFonts(fonts); // Report data interface (matches materialized view output)interface SalesReportRow {  saleDate: string;  region: string;  productCategory: string;  transactionCount: number;  totalQuantity: number;  totalRevenue: number;} function formatCurrency(amount: number): string {  return `$${amount.toLocaleString("en-US", { minimumFractionDigits: 2, maximumFractionDigits: 2 })}`;} // Generate PDF buffer using pdfmake 0.3 getBuffer() methodasync function generatePdfBuffer(  data: SalesReportRow[],  reportDateRange: string): Promise<Buffer> {  // Calculate totals  const totalRevenue = data.reduce((sum, row) => sum + row.totalRevenue, 0);  const totalTransactions = data.reduce(    (sum, row) => sum + row.transactionCount,    0  );   // Aggregate by region  const regionMap = new Map<string, { revenue: number; transactions: number }>();  for (const row of data) {    const existing = regionMap.get(row.region) || { revenue: 0, transactions: 0 };    regionMap.set(row.region, {      revenue: existing.revenue + row.totalRevenue,      transactions: existing.transactions + row.transactionCount,    });  }  const sortedRegions = Array.from(regionMap.entries()).sort(    (a, b) => b[1].revenue - a[1].revenue  );   // Aggregate by category  const categoryMap = new Map<string, { revenue: number; transactions: number }>();  for (const row of data) {    const existing = categoryMap.get(row.productCategory) || {      revenue: 0,      transactions: 0,    };    categoryMap.set(row.productCategory, {      revenue: existing.revenue + row.totalRevenue,      transactions: existing.transactions + row.transactionCount,    });  }  const sortedCategories = Array.from(categoryMap.entries()).sort(    (a, b) => b[1].revenue - a[1].revenue  );   const docDefinition: TDocumentDefinitions = {    content: [      { text: "APEX RETAIL", style: "companyName" },      { text: "Daily Sales Summary", style: "reportTitle" },      { text: reportDateRange, style: "reportDate" },      {        canvas: [          { type: "line", x1: 0, y1: 5, x2: 515, y2: 5, lineWidth: 1 },        ],      },      {        text: "EXECUTIVE SUMMARY",        style: "sectionHeader",        margin: [0, 15, 0, 10],      },      {        columns: [          {            text: [              { text: "Total Revenue: ", bold: true },              formatCurrency(totalRevenue),            ],          },          {            text: [              { text: "Transactions: ", bold: true },              totalTransactions.toLocaleString(),            ],          },        ],        margin: [0, 0, 0, 15],      },      {        text: "REVENUE BY REGION",        style: "sectionHeader",        margin: [0, 10, 0, 10],      },      {        table: {          headerRows: 1,          widths: ["*", "auto", "auto", "auto"],          body: [            [              { text: "Region", style: "tableHeader" },              { text: "Revenue", style: "tableHeader" },              { text: "Transactions", style: "tableHeader" },              { text: "Share", style: "tableHeader" },            ],            ...sortedRegions.map(([region, stats]) => [              region,              { text: formatCurrency(stats.revenue), alignment: "right" as const },              { text: stats.transactions.toLocaleString(), alignment: "right" as const },              {                text: `${totalRevenue > 0 ? ((stats.revenue / totalRevenue) * 100).toFixed(1) : "0.0"}%`,                alignment: "right" as const,              },            ]),          ],        },        layout: "lightHorizontalLines",      },      {        text: "REVENUE BY CATEGORY",        style: "sectionHeader",        margin: [0, 20, 0, 10],      },      {        table: {          headerRows: 1,          widths: ["*", "auto", "auto", "auto"],          body: [            [              { text: "Category", style: "tableHeader" },              { text: "Revenue", style: "tableHeader" },              { text: "Transactions", style: "tableHeader" },              { text: "Share", style: "tableHeader" },            ],            ...sortedCategories.map(([category, stats]) => [              category,              { text: formatCurrency(stats.revenue), alignment: "right" as const },              { text: stats.transactions.toLocaleString(), alignment: "right" as const },              {                text: `${totalRevenue > 0 ? ((stats.revenue / totalRevenue) * 100).toFixed(1) : "0.0"}%`,                alignment: "right" as const,              },            ]),          ],        },        layout: "lightHorizontalLines",      },    ],    footer: {      text: `Generated: ${new Date().toISOString()} | Apex Retail Confidential`,      style: "footer",      margin: [40, 0],    },    styles: {      companyName: { fontSize: 24, bold: true, alignment: "center" },      reportTitle: {        fontSize: 16,        alignment: "center",        margin: [0, 5, 0, 5],      },      reportDate: { fontSize: 12, alignment: "center", color: "gray" },      sectionHeader: { fontSize: 12, bold: true },      tableHeader: { bold: true, fillColor: "#eeeeee" },      footer: { fontSize: 8, color: "gray", alignment: "center" },    },    defaultStyle: { font: "Roboto", fontSize: 10 },    pageMargins: [40, 40, 40, 60],  };   const pdf = pdfmake.createPdf(docDefinition);  // pdfmake 0.3 provides promise-based getBuffer()  return pdf.getBuffer();} const handler = async (req: IncomingMessage, res: ServerResponse) => {  const url = parseUrl(req.url || "", true);  const startDate = url.query.startDate as string;  const endDate = url.query.endDate as string;  const region = url.query.region as string | undefined;   // Validate required parameters  if (!startDate || !endDate) {    res.writeHead(400, { "Content-Type": "application/json" });    res.end(JSON.stringify({ error: "startDate and endDate query parameters are required" }));    return;  }   // Validate date format (YYYY-MM-DD)  const dateRegex = /^\d{4}-\d{2}-\d{2}$/;  if (!dateRegex.test(startDate) || !dateRegex.test(endDate)) {    res.writeHead(400, { "Content-Type": "application/json" });    res.end(JSON.stringify({ error: "Dates must be in YYYY-MM-DD format" }));    return;  }   // Validate date order  if (startDate > endDate) {    res.writeHead(400, { "Content-Type": "application/json" });    res.end(JSON.stringify({ error: "startDate must be before or equal to endDate" }));    return;  }   try {    // Query ClickHouse directly using Moose utilities    const { client, sql } = await getMooseUtils();    const targetTable = dailySalesMV.targetTable;     // Build query with optional region filter    let query;    if (region) {      query = sql`        SELECT          saleDate,          region,          productCategory,          sum(transactionCount) AS transactionCount,          sum(totalQuantity) AS totalQuantity,          sum(totalRevenue) AS totalRevenue        FROM ${targetTable}        WHERE saleDate BETWEEN ${startDate} AND ${endDate}          AND region = ${region}        GROUP BY saleDate, region, productCategory        ORDER BY saleDate DESC, totalRevenue DESC      `;    } else {      query = sql`        SELECT          saleDate,          region,          productCategory,          sum(transactionCount) AS transactionCount,          sum(totalQuantity) AS totalQuantity,          sum(totalRevenue) AS totalRevenue        FROM ${targetTable}        WHERE saleDate BETWEEN ${startDate} AND ${endDate}        GROUP BY saleDate, region, productCategory        ORDER BY saleDate DESC, totalRevenue DESC      `;    }     const result = await client.query.execute<SalesReportRow>(query);    const data = await result.json() as SalesReportRow[];     if (data.length === 0) {      res.writeHead(404, { "Content-Type": "application/json" });      res.end(JSON.stringify({ error: "No data found for the specified date range" }));      return;    }     // Generate PDF buffer    const pdfBuffer = await generatePdfBuffer(data, `${startDate} to ${endDate}`);     // Return binary PDF response with appropriate headers    res.writeHead(200, {      "Content-Type": "application/pdf",      "Content-Disposition": `attachment; filename="sales-report-${startDate}-to-${endDate}.pdf"`,      "Content-Length": pdfBuffer.length,    });    res.end(pdfBuffer);  } catch (error) {    console.error("PDF generation error:", error);    res.writeHead(500, { "Content-Type": "application/json" });    res.end(JSON.stringify({ error: "Failed to generate PDF" }));  }}; // Create WebApp with custom mount path// Note: Cannot use reserved paths like /consumption or /ingestexport const salesReportPdfApi = new WebApp("salesReportPdf", handler, {  mountPath: "/reports",  metadata: {    description: "Generate sales report as PDF",  },});

export * from "./ingest/SalesTransaction";export * from "./views/DailySalesSummary";export * from "./apis/salesReport";export * from "./apis/salesReportPdf";

Record 1: [txn_id=1, timestamp=..., store=W001, region=West, amount=599.98]Record 2: [txn_id=2, timestamp=..., store=W001, region=West, amount=49.99]Record 3: [txn_id=3, timestamp=..., store=W002, region=East, amount=89.99]

txn_ids:    [1, 2, 3, ...]timestamps: [2025-01-15T09:00, 2025-01-15T09:30, ...]stores:     [W001, W001, W002, ...]regions:    [West, West, East, ...]amounts:    [599.98, 49.99, 89.99, ...]

Raw Data → [Wait for schedule] → Batch Job → Aggregated Table

Raw Data → [INSERT triggers MV] → Aggregated Table (immediate)

Batch 1 (morning): 50 transactions → MV inserts [saleDate=2025-01-15, region=West, category=Electronics, count=50, revenue=5000]Batch 2 (evening): 30 transactions → MV inserts [saleDate=2025-01-15, region=West, category=Electronics, count=30, revenue=3000]

-- Wrong: May return multiple rows per day/regionSELECT * FROM DailySalesSummary WHERE saleDate = '2025-01-15' -- Correct: Consolidates all batches into final aggregatesSELECT saleDate, region, sum(transactionCount), sum(totalRevenue)FROM DailySalesSummaryGROUP BY saleDate, region

orderByFields: ["saleTimestamp", "transactionId"]  // GoodorderByFields: ["transactionId", "saleTimestamp"]  // Bad for time queries

Raw transactions → Hourly MV → Daily MV → Monthly MV

CSV/JSON Files → Ingest API → ClickHouse Table → Materialized View → API → PDF

SELECT  saleDate,  region,  sum(totalRevenue) AS revenue,  sum(transactionCount) AS transactionsFROM local.DailySalesSummaryGROUP BY saleDate, regionORDER BY saleDate DESC, revenue DESC;

SELECT  toHour(saleTimestamp) AS hour_of_day,  count() AS transactions,  sum(totalAmount) AS revenueFROM local.salesGROUP BY hour_of_dayORDER BY hour_of_day;

SELECT  productCategory,  sum(totalRevenue) AS revenue,  sum(transactionCount) AS transactionsFROM local.DailySalesSummaryWHERE saleDate BETWEEN '2025-01-01' AND '2025-01-31'GROUP BY productCategoryORDER BY revenue DESCLIMIT 5;

[project]name = "sales-reports"language = "typescript" [features]olap = truestreaming = true [clickhouse_config]db_name = "local"host = "localhost"host_port = 18123native_port = 9000user = "panda"password = "pandapass"use_ssl = false

SELECT transactionId, count() as cntFROM local.salesGROUP BY transactionIdHAVING cnt > 1

-- Wrong (may show partial aggregates)SELECT * FROM local.DailySalesSummary -- Correct (consolidates partial aggregates)SELECT  saleDate,  region,  sum(transactionCount) AS transactionCount,  sum(totalRevenue) AS totalRevenueFROM local.DailySalesSummaryGROUP BY saleDate, region