Dev.to

Create AI-Powered Store Assistant Bot for Natural Language Inventory queries using BigQuery, Firestore, GFCs, and Vertex AI

Introduction Modern retail stores need more than just basic inventory tracking. They need intelligent assistants that can answer questions about products, stock levels, and sales trends. In this tutorial, I'll walk you through building a conversational store assistant bot that leverages Google Cloud Tools such as Firestore, BigQuery, and Google's Vertex AI to create a natural language interface for your store's inventory data. What you'll learn: Setting up a BigQuery dataset for product history tracking [Optional] Migrating store data from Firestore to BigQuery (Skip if your data is already in BigQuery or another database) Creating REST API endpoints for product analytics Building an AI-powered store assistant using Vertex AI's Gemini model Prerequisites Before we begin, make sure you have: A Google Cloud Platform account Firebase project set up with Firestore (Required for firestore and Google cloud functions) Node.js and npm installed Basic understanding of Express.js Firebase CLI installed (npm install -g firebase-tools) Setting Up The Environment First, you'll need to set up the necessary permissions and service accounts: Create a service account in the GCP console with the following roles: BigQuery Admin Firebase/Firestore Admin Vertex AI User Download the service account key file and store it securely Required npm packages for our project: firebase-admin @google-cloud/bigquery express cors firebase-functions axios @google-cloud/vertexai Step 1: Creating a BigQuery Table for Product History Data Let's start by creating our BigQuery table to store product transaction history by running the following code: const serviceAccountPath = "./path-to-your-service-account.json"; const { BigQuery } = require('@google-cloud/bigquery'); // Initialize services const bigquery = new BigQuery({ keyFilename: serviceAccountPath, projectId: "your-project-id" }); async function createTable() { const datasetId = 'store_assistant'; const tableId = 'product_history'; // Define schema based on your store's product data structure const schema = [ { name: 'transaction_id', type: 'STRING', mode: 'REQUIRED' }, { name: 'product_id', type: 'STRING' }, { name: 'category_id', type: 'STRING' }, { name: 'sku', type: 'STRING' }, { name: 'quantity', type: 'NUMERIC' }, { name: 'unit_count', type: 'NUMERIC' }, { name: 'current_stock', type: 'NUMERIC' }, { name: 'unit_price', type: 'NUMERIC' }, { name: 'product_name', type: 'STRING' }, { name: 'product_code', type: 'STRING' }, { name: 'attributes', type: 'STRING' }, { name: 'status', type: 'STRING' }, { name: 'category', type: 'STRING' }, { name: 'tags', type: 'STRING' }, { name: 'notes', type: 'STRING' }, // Timestamp { name: 'transaction_date', type: 'TIMESTAMP' } ]; const options = { schema, location: 'US' (optional) }; try { // Create the dataset if it doesn't exist await bigquery.createDataset(datasetId, { location: 'US' }).catch(err => { if (err.code !== 409) throw err; // 409 means dataset already exists }); // Create the table const [table] = await bigquery.dataset(datasetId).createTable(tableId, options); console.log(`✅ Table ${table.id} created successfully.`); } catch (error) { console.error('Error creating table:', error); } } createTable().catch(console.error); Step 2: [Optional] Migrating Data from Firestore to BigQuery Now, let's migrate your existing store data from Firestore to BigQuery for advanced analytics: const admin = require('firebase-admin'); const { BigQuery } = require('@google-cloud/bigquery'); // Initialize Firebase admin.initializeApp({ credential: admin.credential.cert('./path-to-your-service-account.json') //You can also initialize it as default }); const db = admin.firestore(); // Initialize BigQuery const bigquery = new BigQuery({ keyFilename: './path-to-your-service-account.json' }); const datasetId = 'store_assistant'; const tableId = 'product_history'; // Define fields structure as per you requirements //========NOTE===== //This is not required if you data has already the required structure const nestedFields = [ "quantity", "unit_count", "current_stock", "unit_price", "product_name", "product_code", "attributes", "status", "category", "tags", "notes", "transaction_date" ]; const directFields = ["product_id", "category_id", "sku"]; // Helper to parse numbers safely function parseNumeric(value) { const num = parseFloat(value); return isNaN(num) ? null : num; } async function processDocs() { let lastDoc = null; const batchSize = 1000; // Adjust based on your document size let processedCount = 0; let buffer = []; console.log('

Apr 16, 2025 - 16:06
 0
Create AI-Powered Store Assistant Bot for Natural Language Inventory queries using BigQuery, Firestore, GFCs, and Vertex AI

Introduction

Modern retail stores need more than just basic inventory tracking. They need intelligent assistants that can answer questions about products, stock levels, and sales trends. In this tutorial, I'll walk you through building a conversational store assistant bot that leverages Google Cloud Tools such as Firestore, BigQuery, and Google's Vertex AI to create a natural language interface for your store's inventory data.

What you'll learn:

  • Setting up a BigQuery dataset for product history tracking
  • [Optional] Migrating store data from Firestore to BigQuery (Skip if your data is already in BigQuery or another database)
  • Creating REST API endpoints for product analytics
  • Building an AI-powered store assistant using Vertex AI's Gemini model

Prerequisites

Before we begin, make sure you have:

  • A Google Cloud Platform account
  • Firebase project set up with Firestore (Required for firestore and Google cloud functions)
  • Node.js and npm installed
  • Basic understanding of Express.js
  • Firebase CLI installed (npm install -g firebase-tools)

Setting Up The Environment

First, you'll need to set up the necessary permissions and service accounts:

  1. Create a service account in the GCP console with the following roles:

    • BigQuery Admin
    • Firebase/Firestore Admin
    • Vertex AI User

  2. Download the service account key file and store it securely

  3. Required npm packages for our project:

    • firebase-admin
    • @google-cloud/bigquery
    • express
    • cors
    • firebase-functions
    • axios
    • @google-cloud/vertexai

Step 1: Creating a BigQuery Table for Product History Data

Let's start by creating our BigQuery table to store product transaction history by running the following code:

const serviceAccountPath = "./path-to-your-service-account.json";
const { BigQuery } = require('@google-cloud/bigquery');

// Initialize services
const bigquery = new BigQuery({
  keyFilename: serviceAccountPath,
  projectId: "your-project-id"
});

async function createTable() {
  const datasetId = 'store_assistant';
  const tableId = 'product_history';

  // Define schema based on your store's product data structure
  const schema = [
    { name: 'transaction_id', type: 'STRING', mode: 'REQUIRED' },
    { name: 'product_id', type: 'STRING' },
    { name: 'category_id', type: 'STRING' },
    { name: 'sku', type: 'STRING' },
    { name: 'quantity', type: 'NUMERIC' },
    { name: 'unit_count', type: 'NUMERIC' },
    { name: 'current_stock', type: 'NUMERIC' },
    { name: 'unit_price', type: 'NUMERIC' },
    { name: 'product_name', type: 'STRING' },
    { name: 'product_code', type: 'STRING' },
    { name: 'attributes', type: 'STRING' },
    { name: 'status', type: 'STRING' },
    { name: 'category', type: 'STRING' },
    { name: 'tags', type: 'STRING' },
    { name: 'notes', type: 'STRING' },

    // Timestamp
    { name: 'transaction_date', type: 'TIMESTAMP' }
  ];

  const options = {
    schema,
    location: 'US' (optional)
  };

  try {
    // Create the dataset if it doesn't exist
    await bigquery.createDataset(datasetId, {
      location: 'US'
    }).catch(err => {
      if (err.code !== 409) throw err; // 409 means dataset already exists
    });

    // Create the table
    const [table] = await bigquery.dataset(datasetId).createTable(tableId, options);
    console.log(`✅ Table ${table.id} created successfully.`);
  } catch (error) {
    console.error('Error creating table:', error);
  }
}

createTable().catch(console.error);

Step 2: [Optional] Migrating Data from Firestore to BigQuery

Now, let's migrate your existing store data from Firestore to BigQuery for advanced analytics:

const admin = require('firebase-admin');
const { BigQuery } = require('@google-cloud/bigquery');

// Initialize Firebase
admin.initializeApp({
  credential: admin.credential.cert('./path-to-your-service-account.json') //You can also initialize it as default
});

const db = admin.firestore();

// Initialize BigQuery
const bigquery = new BigQuery({
  keyFilename: './path-to-your-service-account.json'
});

const datasetId = 'store_assistant';
const tableId = 'product_history';

// Define fields structure as per you requirements
//========NOTE=====
//This is not required if you data has already the required structure
const nestedFields = [
  "quantity", "unit_count", "current_stock", "unit_price",
  "product_name", "product_code", "attributes", "status",
  "category", "tags", "notes", "transaction_date"
];
const directFields = ["product_id", "category_id", "sku"];

// Helper to parse numbers safely
function parseNumeric(value) {
  const num = parseFloat(value);
  return isNaN(num) ? null : num;
}

async function processDocs() {
  let lastDoc = null;
  const batchSize = 1000; // Adjust based on your document size
  let processedCount = 0;
  let buffer = [];

  console.log('
Read More

Tags:

Previous Article

Blog Draft Ai Technologies Analysis And Monetization Strategies 20250416 201752

Next Article

Hello dear community, I am looking for someone who can create a bot or a script ...

Related Posts

Easily Perform AI-powered Code Reviews In Minutes (CodeRabbit + GitHub)

Easily Perform AI-powered Code Reviews In Minutes (Code...

Apr 3, 2025  0

6 Common React Anti-Patterns That Are Hurting Your Code Quality

6 Common React Anti-Patterns That Are Hurting Your Code...

Mar 20, 2025  0

Developing Hyper-Personalized AI Assistants

Developing Hyper-Personalized AI Assistants

Mar 14, 2025  0