Table of Contents

System Overview

MTS AI Business Intelligence is an embedded Shopify application that provides real-time analytics dashboards, AI-powered insights, and comprehensive business intelligence directly within the Shopify admin panel. The app synchronizes store data via webhooks and the GraphQL Admin API, stores it locally, and surfaces actionable metrics through an intuitive React-based UI.

Core Capabilities

• Real-time dashboard with KPIs: revenue, orders, AOV, customer metrics
• Sales analytics with time-series charts (daily, weekly, monthly aggregations)
• Product performance ranking by revenue and units sold
• Customer segmentation (new, returning, VIP) with lifetime value calculations
• Inventory health monitoring with stock status classification
• AI-powered natural language insights via OpenAI GPT-4
• Automated webhook-driven data synchronization
• Per-store configurable settings with encrypted API key storage

Technology Stack

The application leverages a modern, full-stack JavaScript/TypeScript stack optimized for the Shopify embedded app ecosystem.

Supporting Libraries

Prerequisites

Before setting up the development environment, ensure the following requirements are met:

Configuration Guide

The application requires configuration across multiple files for environment variables, Shopify app manifest, TypeScript compilation, and build tooling.

Environment Variables (.env)

Create a .env file in the project root by copying from .env.example:

# ─── Shopify App Credentials ───
SHOPIFY_API_KEY=your_shopify_api_key_here
SHOPIFY_API_SECRET=your_shopify_api_secret_here
SCOPES=read_orders,read_products,read_customers,read_inventory,read_analytics,read_content

# ─── App URLs ───
SHOPIFY_APP_URL=https://your-app-domain.com
HOST=0.0.0.0
PORT=5173

# ─── Database ───
DATABASE_URL="file:./dev.db"

# ─── AI Integration ───
OPENAI_API_KEY=sk-your-openai-api-key-here

shopify.app.toml

The Shopify app manifest defines the app's metadata, access scopes, authentication redirects, webhook subscriptions, and app proxy configuration.

# shopify.app.toml

client_id = "your_client_id"
name = "MTS AI Business Intelligence"
handle = "mts-ai-business-intelligence"
application_url = "https://your-app-domain.com"
embedded = true

# ─── Access Scopes ───
[access_scopes]
scopes = "read_orders,read_products,read_customers,read_inventory,read_analytics,read_content"

# ─── Auth Redirects ───
[auth]
redirect_urls = [
  "https://your-app-domain.com/auth/callback",
  "https://your-app-domain.com/auth/shopify/callback"
]

# ─── Webhook Subscriptions ───
[[webhook_subscriptions]]
topic = "orders/create"
uri = "https://your-app-domain.com/webhooks"

[[webhook_subscriptions]]
topic = "orders/update"
uri = "https://your-app-domain.com/webhooks"

[[webhook_subscriptions]]
topic = "orders/fulfilled"
uri = "https://your-app-domain.com/webhooks"

[[webhook_subscriptions]]
topic = "products/create"
uri = "https://your-app-domain.com/webhooks"

[[webhook_subscriptions]]
topic = "products/update"
uri = "https://your-app-domain.com/webhooks"

[[webhook_subscriptions]]
topic = "products/delete"
uri = "https://your-app-domain.com/webhooks"

[[webhook_subscriptions]]
topic = "customers/create"
uri = "https://your-app-domain.com/webhooks"

[[webhook_subscriptions]]
topic = "customers/update"
uri = "https://your-app-domain.com/webhooks"

# ─── App Proxy ───
[app_proxy]
subpath = "apps/mts-ai-bi"
replace = true
prefix = "apps"

TypeScript & Vite Configuration

tsconfig.json

{
  "include": [
    "env.d.ts",
    "**/*.ts",
    "**/*.tsx",
    "app/routes/**/*.ts"
  ],
  "compilerOptions": {
    "lib": ["DOM", "DOM.Iterable", "ES2022"],
    "types": ["@shopify/cli"],
    "isolatedModules": true,
    "esModuleInterop": true,
    "jsx": "react-jsx",
    "module": "ESNext",
    "moduleResolution": "Bundler",
    "resolveJsonModule": true,
    "target": "ES2022",
    "strict": true,
    "allowJs": true,
    "skipLibCheck": true,
    "forceConsistentCasingInFileNames": true,
    "paths": {
      "~/*": ["./app/*"]
    },
    "noEmit": true
  }
}

vite.config.ts

import { vitestConfigGenerator } from "@shopify/hydrogen/vite";
import { hydrogen } from "@shopify/hydrogen/vite";
import { oxygen } from "@shopify/mini-oxygen/vite";
import { vitePlugin } from "@shopify/remix-oxygen/vite";
import { defineConfig } from "vite";

export default defineConfig({
  plugins: [
    hydrogen,
    vitePlugin({
      presets: [],
      serverConditions: ["worker"],
    }),
    oxygen(),
  ],
  test: vitestConfigGenerator({
    pools: ["forks"],
    poolOptions: { forks: { singleFork: true } },
  }),
});

react-router.config.ts

import { defineConfig } from "@shopify/remix-oxygen/react-router";

export default defineConfig({
  future: {
    v3_fetcherPersist: true,
    v3_relativeSplatPath: true,
    v3_throwAbortReason: true,
    v3_lazyRouteDiscovery: true,
    v3_singleFetch: true,
    v3_optimizeDeps: true,
  },
  ssr: true,
});

Database Schema

The application uses Prisma 6.x as the ORM with SQLite as the embedded database. The schema defines 11 models covering session storage, user management, data synchronization, analytics caching, and AI conversation history.

// prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

// ─── Session Storage ───
model Session {
  id           String   @id
  shop         String
  state        String
  isOnline     Boolean  @default(false)
  scope        String?
  accessToken  String
  expires      DateTime?
  userId       String?
  user         User?    @relation(fields: [userId], references: [id])
  accounts     Account[]
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt

  @@index([shop])
  @@map("Session")
}

// ─── User ───
model User {
  id           String   @id
  email        String   @unique
  name         String?
  role         String   @default("merchant")
  sessions     Session[]
  accounts     Account[]
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt
  @@map("User")
}

// ─── Account ───
model Account {
  id           String   @id
  userId       String
  user         User     @relation(fields: [userId], references: [id])
  sessionId    String?
  session      Session? @relation(fields: [sessionId], references: [id])
  type         String
  createdAt    DateTime @default(now())
  @@map("Account")
}

// ─── Synced Order ───
model SyncedOrder {
  id              String              @id
  shopId          String
  shopifyId       String              @unique
  orderNumber     Int
  name            String
  email           String?
  totalPrice      Float
  subtotalPrice   Float
  totalTax        Float              @default(0)
  totalDiscounts  Float              @default(0)
  currency        String             @default("USD")
  financialStatus String?
  fulfillmentStatus String?
  customerName    String?
  customerEmail   String?
  shippingAddress String?
  createdAt       DateTime           @default(now())
  updatedAt       DateTime           @updatedAt
  syncedAt        DateTime           @default(now())
  lineItems       SyncedOrderItem[]

  @@index([shopId])
  @@index([createdAt])
  @@map("SyncedOrder")
}

// ─── Synced Order Item ───
model SyncedOrderItem {
  id              String   @id
  orderId         String
  order           SyncedOrder @relation(fields: [orderId], references: [id])
  shopifyId       String?
  title           String
  quantity        Int
  price           Float
  totalDiscount   Float    @default(0)
  sku             String?
  vendor          String?
  productTitle    String?
  variantTitle    String?
  productId       String?
  variantId       String?
  createdAt       DateTime @default(now())
  @@index([orderId])
  @@index([productId])
  @@map("SyncedOrderItem")
}

// ─── Synced Product ───
model SyncedProduct {
  id              String   @id
  shopId          String
  shopifyId       String   @unique
  title           String
  description     String?
  vendor          String?
  productType     String?
  status          String   @default("ACTIVE")
  handle          String?
  tags            String?
  totalInventory  Int      @default(0)
  totalVariants   Int      @default(0)
  imageSrc        String?
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt
  syncedAt        DateTime @default(now())
  variants        SyncedProductVariant[]

  @@index([shopId])
  @@map("SyncedProduct")
}

// ─── Synced Product Variant ───
model SyncedProductVariant {
  id              String   @id
  productId       String
  product         SyncedProduct @relation(fields: [productId], references: [id])
  shopifyId       String   @unique
  title           String
  sku             String?
  price           Float
  compareAtPrice  Float?
  inventory       Int      @default(0)
  inventoryPolicy String?
  weight          Float?
  weightUnit      String?
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt
  syncedAt        DateTime @default(now())
  @@index([productId])
  @@map("SyncedProductVariant")
}

// ─── Synced Customer ───
model SyncedCustomer {
  id              String   @id
  shopId          String
  shopifyId       String   @unique
  email           String?
  firstName       String?
  lastName        String?
  phone           String?
  totalSpent      Float    @default(0)
  ordersCount     Int      @default(0)
  averageOrderValue Float  @default(0)
  tags            String?
  state           String?
  city            String?
  country         String?
  lastOrderAt     DateTime?
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt
  syncedAt        DateTime @default(now())
  @@index([shopId])
  @@map("SyncedCustomer")
}

// ─── Analytics Snapshot ───
model AnalyticsSnapshot {
  id              String   @id
  shopId          String
  type            String
  period          String
  data            String   // JSON serialized analytics
  expiresAt       DateTime
  createdAt       DateTime @default(now())
  @@index([shopId, type, period])
  @@map("AnalyticsSnapshot")
}

// ─── AI Insight ───
model AiInsight {
  id              String   @id
  shopId          String
  question        String
  response        String
  type            String   @default("chat")
  context         String?  // JSON context used for generation
  model           String?  // "gpt-4" or "rule-based"
  createdAt       DateTime @default(now())
  @@index([shopId])
  @@map("AiInsight")
}

// ─── App Settings ───
model AppSettings {
  id              String   @id
  shopId          String   @unique
  openaiApiKey    String?
  syncEnabled     Boolean  @default(true)
  syncInterval    Int      @default(3600)
  autoSync        Boolean  @default(true)
  aiEnabled       Boolean  @default(true)
  dashboardLayout String?
  customFields    String?  // JSON
  lastSyncAt      DateTime?
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt
  @@map("AppSettings")
}

Schema Relationships

API Routes

All routes are defined using React Router v7 file-based conventions. Loader functions handle data fetching on GET requests; action functions handle mutations on POST requests.

Route Loading Pattern

// Example: app/routes/app.sales.tsx — loader
export const loader = async ({ request }: LoaderFunctionArgs) => {
  const { admin, session } = await authenticate.admin(request);
  const shopId = session.shop;

  const [revenueKpi, ordersKpi, aovKpi, chartData] = await Promise.all([
    getRevenueKpi(shopId),
    getOrdersKpi(shopId),
    getAovKpi(shopId),
    getSalesChart(shopId, "day"),
  ]);

  return json({ revenueKpi, ordersKpi, aovKpi, chartData });
};

Webhook Processing Pattern

// app/routes/webhooks.tsx
export const action = async ({ request }: ActionFunctionArgs) => {
  const { topic, shop, session, admin } = await authenticate.webhook(request);

  if (!admin) throw new Response("Unauthorized", { status: 401 });

  const payload = await request.json();

  switch (topic) {
    case "orders/create":
    case "orders/update":
      await upsertOrder(shop, payload);
      break;
    case "products/create":
    case "products/update":
      await upsertProduct(shop, payload);
      break;
    case "customers/create":
    case "customers/update":
      await upsertCustomer(shop, payload);
      break;
  }

  return json({ received: true });
};

Data Flow Architecture

The application operates on three primary data flows: sync, display, and AI analysis. Each flow is designed for reliability, with local SQLite caching reducing API calls and webhook-driven updates maintaining data freshness.

Primary Sync Flow

Webhook-Driven Real-Time Flow

AI Analysis Flow

Sync Detail: GraphQL Fetching

// Core sync function: fetchOrders via Shopify GraphQL Admin API
async function fetchOrders(admin: AdminClient, shopId: string) {
  const query = `{
    orders(first: 250, sortKey: CREATED_AT, reverse: true) {
      edges {
        node {
          id name email totalPrice subtotalPrice
          totalTax currency financialStatus fulfillmentStatus
          createdAt updatedAt
          lineItems(first: 50) {
            edges { node {
              title quantity price sku vendor
              product { title id }
              variant { title id sku }
            }}
          }
          shippingAddress { address1 city province country zip }
        }
      }
      pageInfo { hasNextPage endCursor }
    }
  }`;

  const response = await admin.graphql(query);
  const { orders } = await response.json();

  for (const edge of orders.edges) {
    await prisma.syncedOrder.upsert({
      where: { shopifyId: edge.node.id },
      update: { /* fields */ },
      create: { shopId, /* fields */ },
    });
  }
}

Analytics Engine

The analytics engine is a collection of Prisma-based query functions that aggregate synced data into business metrics. All functions accept a shopId parameter for multi-tenant data isolation.

KPI Functions

Chart & Report Functions

Revenue Forecasting Algorithm

The forecasting module uses historical revenue data to project future trends via a weighted moving average:

async function forecastRevenue(shopId: string) {
  // 1. Fetch last 30 days of daily revenue
  const dailyData = await getSalesChart(shopId, "day");

  // 2. Calculate 7-day weighted moving average
  const weights = [0.35, 0.25, 0.20, 0.10, 0.05, 0.03, 0.02];
  const recent = dailyData.slice(-7);
  const wma = recent.reduce((sum, d, i) =>
    sum + d.revenue * (weights[i] || 0.01), 0);

  // 3. Project next 30 days with trend adjustment
  const trend = calculateTrend(dailyData); // slope of linear regression

  return {
    daily: Array.from({ length: 30 }, (_, i) => ({
      day: i + 1,
      projected: wma + trend * (i + 1),
    })),
    totalProjected: wma * 30 + trend * (30 * 31) / 2,
    confidence: calculateConfidence(dailyData),
  };
}

AI Integration

The AI integration provides natural language business insights by combining store analytics context with OpenAI's GPT-4 model. When no API key is configured, the system falls back to rule-based analysis.

OpenAI GPT-4 API Integration

import OpenAI from "openai";

const openai = new OpenAI({
  apiKey: settings.openaiApiKey || process.env.OPENAI_API_KEY,
});

async function generateAiInsight(
  shopId: string,
  question: string
): Promise<AiInsight> {
  // 1. Build context from analytics
  const analytics = await getFullAnalytics(shopId);
  const context = buildContextString(analytics);

  // 2. Check for OpenAI API key
  const settings = await prisma.appSettings.findUnique({
    where: { shopId },
  });

  let response: string;
  let model: string;

  if (settings?.openaiApiKey || process.env.OPENAI_API_KEY) {
    // 3a. Use GPT-4
    const completion = await openai.chat.completions.create({
      model: "gpt-4",
      messages: [
        {
          role: "system",
          content: `You are a business intelligence analyst...`,
        },
        {
          role: "user",
          content: `Context: ${context}\n\nQuestion: ${question}`,
        },
      ],
      max_tokens: 1500,
      temperature: 0.7,
    });
    response = completion.choices[0].message.content || "No insight generated.";
    model = "gpt-4";
  } else {
    // 3b. Rule-based fallback
    response = generateRuleBasedInsight(analytics, question);
    model = "rule-based";
  }

  // 4. Store in database
  return prisma.aiInsight.create({
    data: { shopId, question, response, model, type: "chat" },
  });
}

Context Building

The buildContextString() function aggregates key metrics into a structured prompt context:

function buildContextString(analytics: FullAnalytics): string {
  return `
Store Analytics Summary:
- Total Revenue: $${analytics.revenue.total.toFixed(2)}
- Revenue Change: ${analytics.revenue.change}%
- Total Orders: ${analytics.orders.count}
- Average Order Value: $${analytics.aov.aov.toFixed(2)}
- Total Customers: ${analytics.customers.total}
- Repeat Purchase Rate: ${analytics.repeatRate.rate}%
- Top Products: ${analytics.topProducts.slice(0, 5).map(p =>
    `${p.title} ($${p.revenue.toFixed(2)})`).join(", ")}
- Revenue Trend (last 7 days): ${analytics.chart.slice(-7).map(d =>
    `$${d.revenue.toFixed(0)}`).join(" → ")}
  `;
}

Rule-Based Fallback

When no OpenAI API key is available, the system generates insights using deterministic rules:

Conversation History & Daily Brief

• Every question/response pair is persisted in the AiInsight model with type "chat"
• Daily briefs are generated with type "brief" containing aggregated daily insights
• The insights page loads chat history for context continuity across sessions
• Each insight stores the model used ("gpt-4" or "rule-based"}) for transparency

Installation Steps

Follow these steps to set up the MTS AI Business Intelligence app in your local development environment.

1. Clone or Extract the Project
   Obtain the project source code and navigate to the project directory.

   # Clone from repository or extract archive
   cd I:\xampp8212\htdocs\mtsaivusinessintelligence
   dir # verify project files present

2. Install Dependencies
   Install all npm packages defined in package.json.

   npm install

3. Create Environment File
   Copy the example environment file and customize it.

   # Windows
   copy .env.example .env
   
   # macOS / Linux
   cp .env.example .env

4. Configure Environment Variables
   Edit .env with your actual credentials and configuration values.

   # Edit .env with your values
   SHOPIFY_API_KEY=abc123def456
   SHOPIFY_API_SECRET=shpss_abcdef123456
   SCOPES=read_orders,read_products,read_customers,read_inventory,read_analytics,read_content
   SHOPIFY_APP_URL=https://your-app.ngrok.io
   DATABASE_URL="file:./dev.db"
   OPENAI_API_KEY=sk-your-key-here
   HOST=0.0.0.0
   PORT=5173

5. Update shopify.app.toml
   Configure the Shopify app manifest with your app credentials and webhook URIs.

   # Edit shopify.app.toml
   client_id = "your_client_id"
   application_url = "https://your-app.ngrok.io"
   
   # Update webhook URIs to match your development URL
   [[webhook_subscriptions]]
   topic = "orders/create"
   uri = "https://your-app.ngrok.io/webhooks"
   # ... repeat for all webhook topics

6. Initialize Database
   Push the Prisma schema to SQLite and generate the Prisma client.

   npx prisma db push
   npx prisma generate

7. Start Development Server
   Launch the Vite dev server with SSR enabled.

   npm run dev

8. Install on Shopify Store
   Navigate to your Partner Dashboard → App → Install on development store. Authorize the app with the requested scopes. The app will appear in the Shopify admin sidebar.

# Option A: Using ngrok
ngrok http 5173

# Option B: Using Shopify CLI
npx shopify app dev

File Structure

Complete project directory layout showing all application files and their organizational structure.

Deployment

The application supports deployment to Shopify's managed infrastructure or custom hosting platforms. This section covers production build, environment configuration, and deployment options.

Production Build

# 1. Build the application for production
npm run build

# 2. The build output is in the build/ directory
# 3. Start the production server
npm run start

Production Environment Variables

# Production .env — ensure all values are set
SHOPIFY_API_KEY=production_api_key
SHOPIFY_API_SECRET=production_api_secret
SCOPES=read_orders,read_products,read_customers,read_inventory,read_analytics,read_content
SHOPIFY_APP_URL=https://your-production-domain.com
DATABASE_URL="file:./production.db"
OPENAI_API_KEY=sk-production-key
HOST=0.0.0.0
PORT=3000
NODE_ENV=production

Deployment Options

Post-Deployment Checklist

Pricing & Plans

MTS AI Business Intelligence offers a tiered pricing model with a free plan to get started and a Pro plan for advanced features. Below is the detailed plan comparison and feature breakdown.

Plan Overview

Free Plan

Pro Plan

Plan Comparison

MTS AI Business Intelligence — Technical Documentation v1.0.0

Built by MageTech Solutions · Shopify GraphQL Admin API 2024-10