Building a Generative AI-powered Inventory Assista...

furkan5

Introduction

In modern enterprises, inventory management is no longer just about tracking numbers — it's about accessing the right information at the right time and enabling fast, data-driven decisions. Today, Generative AI offers a powerful opportunity to make these processes smarter, faster, and more intuitive.

In this project, I transformed a stock management application built with SAP CAP (Node.js) on SAP BTP into an intelligent decision assistant by integrating Generative AI, Retrieval-Augmented Generation (RAG) , and a vector database .

The application interprets natural language queries, finds relevant products using embedding-based similarity search , retrieves real records from the database, and — thanks to the grounding approach — generates responses strictly based on actual data. This prevents hallucinations and ensures a trusted enterprise AI experience.

A key learning resource for this development was the SAP Learning Journey
“Solving Business Problems using SAP's Generative AI Hub”
which helped me explore and apply Generative AI concepts in real SAP BTP scenarios.

In this blog, I will walk through the architecture, implementation details, and the technical components behind this solution.

STEP 1:

We set up the Basic CAP file structure with "cds init" in the terminal

Creates package.json and config files
Prepares db, srv, app folders

And then .envwe define our Gemini API key in the file in the project root directory.

Then we create the "serice.cds and service.js" files under the "SRV" folder.

and we define "service.cds" as follows:

namespace ai;

using data from '../db/products';


type ChatTurn {
  role   : String;  
  content: String;
}

type ChatResponse {
  content : String;
}


service ProductService @(path:'/products') {
  entity Products as projection on data.Products;

}

service AIService @(path:'/ai') {
  : { method: 'POST', path: 'chat' }
  action chat(message: String, history: many ChatTurn) returns ChatResponse;

 
}

ChatTurn: Represents each line of conversation (role: user/assistant, content: message)
ChatResponse: Response format returned from chat (an object containing only content)

These structures are used for AI chat API .

And right below, we define a "product" service that exposes product data to the outside.

This section

/productsvia endpoint
data.Productsshows his painting to the outside world

Just below this, we define the AI service .
This service is the structure that responds to user requests sent in natural language.

We created an action inside the service chatcalled . In the CAP world, action is used to execute special business logic outside of classic CRUD operations (CREATE/READ/UPDATE/DELETE).

This chataction:

message: text written by the user,
history: previous conversation history

takes and

ChatResponse: that is, the answer generated by artificial intelligence

returns.

In short, this action is the bridge between the UI side and the LLM. When
the user POST /ai/chatsubmits the request, the CAP service.jsruns the relevant code within it; it reads the product data, applies vector lookup and grounding, and then generates a response from the LLM.

After completing the service definition, we need to create the data model that will store the product data the application will use. This structure dbis defined in the CAP layer. Here, I defined an entity for the products and uploaded the initial data to the system via a CSV file.

In the data layer of the application, I defined three basic entities for inventory management and artificial intelligence operations:

namespace data;

entity Products {
  ID        : UUID @key;
  sku       : String(60) ;
  name      : String(200);
  price     : Decimal(15,2);
  currency  : String(3) default 'TRY';
  stock     : Integer default 0;
  category  : String(80);
  brand     : String(80);
  color     : String(40);
  barcode   : String(32);
  weight    : Decimal(10,3); 
  unit      : String(10) default 'adet';
  description: String(1000);
  createdAt : Timestamp default current_timestamp;
  updatedAt : Timestamp;
}

entity Embeddings {
  ID         : UUID @key;
  product    : Association to Products;
  chunkIndex : Integer;          
  text       : String(5000);    
  embedding  : LargeString;       
  model      : String(100);      
  dim        : Integer;       
  createdAt  : Timestamp default current_timestamp;
}

entity ProductVectors {
  sku       : String(60) @key;
  embedding : LargeString;
}

`Products`— Product Main Table

This table stores basic product information in the system.
Fields contain actual commercial parameters such as product name, price, and stock quantity.

`Embeddings`— Text Embedding Archive

This table is designed to store embeddings of product descriptions.
Embedding a product can be done by splitting it into multiple pieces of text, rather than as a single line.

`ProductVectors`— Fast Vector Lookup Table

This table holds a simplified version of the product SKU and embeddings.

For the project, I created a product dataset for testing purposes.
This CSV file contains basic product information used in the application's inventory query, embedding generation, and vector search processes.

We're using the CAP command to create the product and vector tables in the project deploy.
The following command takes the CDS data model and applies it to SQLite as a live database:

cds deploy --to sqlite:sqlite.db

Embedding Process and Data Preparation

The tables above form the data infrastructure of the application.
In the next step, the product text must be embedded and transferred to the vector index table.

This process takes place in two stages:

1 Converting product descriptions to embeddings 2 Loading
embeddings into the table for quick searchProductVectors

For this purpose, I added two helper scripts to the project:

/tools
 ├─ embed.js          
 └─ init-vectors.js

These scripts are run once before the application runs and prepare the RAG infrastructure.

What is Embedding?

Embedding is the process of converting text into a vector of numbers. In other words, it's a way of mathematically representing
text, words, or product descriptions .

try {
  require("dotenv").config();
} catch (e) {}
const cds = require("@sap/cds");
const { GoogleGenerativeAI } = require("@google/generative-ai");

async function embed(text) {
  const KEY = process.env.GEMINI_API_KEY;
  const genAI = new GoogleGenerativeAI(KEY);
  const model = genAI.getGenerativeModel({ model: "text-embedding-004" });
  const res = await model.embedContent(text);
  return res?.embedding?.values || [];
}

(async () => {
  const db = await cds.connect.to("db");
  const products = await db.run(`SELECT sku, name FROM data_Products`);
  for (const p of products) {
    const text = `${p.sku} ${p.name}`;
    const vec = await embed(text);
    await db.run(
      `INSERT OR REPLACE INTO data_ProductVectors (sku, embedding) VALUES (?, ?)`,
      [p.sku, JSON.stringify(vec)]
    );
    console.log("✓ embedded:", p.sku);
  }
  console.log(" Bitti");
  process.exit(0);
})();
// Note: To run this file, use the following in the terminal:
//    node tools/embed.js

To run the embedding script, use the following command in the terminal:

node tools/embed.js

When this command is run, product descriptions are converted to embeddings via the Gemini API and saved in the Embeddings table. This step is only performed when data is loaded or the product list changes.

Passing Embeddings to Vector Lookup Table (init-vectors.js):

Once the embeddings are created, we need to load this data into the SQLite vector table for fast vector search.

At this stage, I run the init-vectors.js script.

This script: Reads the Embeddings table Converts vectors to Float32 format.

Writes to the ProductVectors table. This makes it possible to search for cosine similarity / L2 distance on SQLite.

node tools/init-vectors.js

The embeddings and vector table are ready.
At this point, let's add the plugin to the project to enable vector similarity calculation in SQLite sqlite-vec:

sqlite-extensions/
 └─ vec0.dll

This plugin adds the following functionality to SQLite:

vec_distance_cos() — Cosine similarity
vec_distance_l2() — L2 (Euclidean) distance
vec_version()— Version control

You can download it from the link below.

https://github.com/asg017/sqlite-vec/releases

Here's how I install the plugin in Node.js:

const dll = path.resolve(__dirname, "..", "sqlite-extensions", "vec0.dll");
if (fs.existsSync(dll)) db.loadExtension(dll);
In this way the CAP service can work as an embedded vector database .

Retrieval-Augmented Generation (RAG) Phase

To this point, we've prepared the application's data infrastructure, loaded the products into the system, converted them to embedding (numeric vector) format, and established a structure capable of vector searching on SQLite. Now, we're moving on to the real intelligence: the RAG (Retrieval-Augmented Generation) process.

The primary goal of the RAG approach is to prevent AI from relying solely on the model it was trained on and to enable it to produce more accurate, reliable, and contextual results by accessing real data. In this project, we implemented this as follows:

When a user types a stock-related query in natural language, the system first embeds the query and identifies the most relevant SKUs by performing a vector similarity search on SQLite-Vec. The actual records for these products are then retrieved from the SAP CAP database and provided as grounding for the Gemini model. This ensures that the AI generates its response based solely on existing product data, eliminating any hallucinations.

R — Retrieval (Finding information):

The query from the user ( ) queryis converted into an embedding and this embedding finds similar products using SQLite-Vec:

const qvec = await embed(query);
if (!qvec.length)
  return { error: "Embedding üretilemedi (Gemini).", products: [] };


const buf = toFloat32Buffer(qvec);
rows = db
  .prepare(`
    SELECT sku, vec_distance_cos(embedding, ?) AS dist
    FROM vec_product_vectors
    ORDER BY dist ASC
    LIMIT 5
  `)
  .all(buf);

In this phase, the system translates the user's natural language query into a numerical vector and returns the closest product placements based on vector similarity (cosine distance). In other words, this is where the "finding related information"
step occurs.

A — Augmentation (Adding contextual data)

Actual product records are pulled from the CAP database using the found SKUs :

const skus = rows.map((r) => r.sku);
const products = await cds.run`
  SELECT *
  FROM data.Products
  WHERE sku in ${skus}
`;

At this point:

Actual commercial data (product name, price, stock, category, etc.) are taken from the system.
The model is now prepared to produce answers with only this data.

In other words , the Retrieval result is strengthened with real information by being “Augmented” .

Generation (Answer generation)

The model generates an answer based solely on the above data with the grounding rule:

const prompt = `Kullanıcı: "${query}"

Ürün verileri (sadece bunlara dayan):
${lines}

Kurallar:
- Sadece yukarıdaki verilere dayan, uydurma bilgi verme.
- Kısa ve net Türkçe cevap ver.
- Eğer kullanıcı spesifik SKU/ürün sormamışsa, en yakın 1-3 uygun ürünü özetle.
`;

const answer = await callGemini(prompt);
return { content: answer };

Here:

The model is fed only with real data .
Grounding is provided with the “do not provide fabricated information” rule .
As a result, the model generates a reliable and context-based response.

RESULT:

With this project, we transformed a traditional inventory management application running on SAP CAP into an intelligent decision support system based on real data. Thanks to embedding, vector search, grounding, and Gemini integration, users can now search for products using natural language, and the model generates reliable and consistent answers based solely on existing corporate data. This approach provides a concrete example of how to improve processes with AI in businesses.

GİTHUB: https://github.com/furkanaksy838/Stock-asistan.git