StockSense AI: Building a Retail Analytics Assistant That Shows Its Work

At closing time, a shopkeeper looks at a spreadsheet with hundreds of rows. Sales are recorded. Stock is somewhere between “probably fine” and “we should have ordered this yesterday.” A dashboard can show a total, but it may not explain why a product ranked first, whether a profit number is trustworthy, or what information is missing.

That scenario led me to a question: Could a small retailer get useful answers from sales and stock data without handing the arithmetic to a chatbot?

I built StockSense AI as an open-source retail analytics MVP to explore that question. It combines a conventional analytics backend with an optional local language model. The calculations produce the evidence; the model helps explain it. If the model is unavailable, the core dashboard still works.

• Repository: sleepyaishik69/Stock-Sense
• Setup and product notes: README
• Database schema snapshot: backend/schema.sql
• Alembic migrations: backend/migrations
• Backend analytics: analytics.py
• AI context builder: ai_context.py
• API and AI routes: main.py
• Dashboard: frontend/app/page.tsx
• Synthetic import file for trying the workflow: test_sales_data.csv
• Container setup: docker-compose.yml

The question that pushed the AI work forward

These are earlier versions of the Ask StockSense screen. In the first, I asked which product sold the most; the answer said it could not tell, even though the evidence panel showed sales totals. In the second, I asked how to sell more and got broad marketing advice without product-level evidence. Seeing those responses made the gap clear: showing metrics beside an answer is not the same as using those metrics to answer the question.

Earlier iteration: the answer did not identify a best-selling product, despite sales evidence being visible.

Earlier iteration: generic advice did not connect the recommendation to products or measured results.

The design rule: calculate first, explain second

A natural-language answer can sound polished and still be wrong. StockSense keeps business arithmetic in deterministic Python code. The backend calculates totals, comparisons, product rankings, and inventory signals from the selected workspace, store, and date range. Certain questions, including “Which product sold the most units?” and “What were net sales?”, are answered directly from those facts.

For broader questions, StockSense builds a structured, bounded analysis context from the selected period and an equal-length previous period. It includes sales and profit trends, product and category rankings, changes in units, inventory signals, basket patterns, and known data gaps. If Ollama and the configured Gemma model are available, the model uses that context to write an explanation. The model does not get a SQL tool and cannot change inventory or place an order.

The interface labels whether an answer came from core analytics, Gemma, or the core analytics fallback. It also shows the date range and supporting metric, product, and inventory evidence. When the selected period has no relevant data, the app says so instead of inventing a bestseller.

That separation matters: a model can help interpret a result, but it should not quietly become the source of the result.

What is in the app today?

1. A workspace starts empty

A new registration creates a workspace and its first store, but no seeded products or sales. Products, stores, sales, and imports carry workspace scope, and the API applies the authenticated workspace to its queries. Users can register or sign in with an email and password; the API uses a signed HttpOnly session cookie.

This keeps a demo dataset from masquerading as a user’s real business data. The included CSV is an explicit, synthetic test fixture that a developer can import when they want to explore the product.

2. Import existing sales instead of retyping them

The import workflow accepts CSV and Excel files. The preview detects common column aliases, displays a sample, and suggests how source columns map to StockSense fields. Users confirm the import before records are written.

Date, quantity, and unit price are required. Transaction ID, SKU, product name, category, discount, tax, unit cost, and a return indicator are optional. The backend validates dates and numbers, rejects non-positive quantities and duplicate rows, and can import valid rows while recording rejected rows and reasons in an import history. When a SKU is new, the import can create its product record.

That makes data quality visible. A partially successful import is not silently treated as a perfect one: users can inspect rejected rows and should resolve them before trusting the totals.

3. Metrics have explicit definitions

The analytics code uses Python Decimal values and rounds money to cents with half-up rounding. The headline definitions are:

• Net sales = gross sales − discounts − refunds. Tax is stored, but excluded from net sales.
• Gross profit = net sales − cost of goods sold. It is returned only when every relevant sale line has a unit cost.
• Gross margin = gross profit ÷ net sales. It is unavailable when costs are incomplete or net sales are zero.
• Transactions count distinct transaction IDs, scoped to a store.
• Average transaction value = net sales ÷ transaction count.
• Inventory value uses the current product cost and current on-hand count; it is a present-day estimate, not a historical valuation.

These rules help prevent a dashboard from presenting “0” where the honest answer is “unknown.” Missing cost data is particularly important: a product’s current catalog cost cannot retroactively prove what a sale line cost at the time it was sold.

4. Sales and product analysis

The overview includes a selected-period snapshot and an equal-length prior-period comparison. It tracks sales and profit trends at a weekly or monthly grain, ranks products by units, sales, and gross profit, compares product units with the previous period, and groups sales by category.

The dashboard is split into practical views, including Overview, Sales Analytics, Products, Inventory, Finance, Marketing, Ask StockSense, Reports, Imports, and Settings. That lets an owner move from a metric to the records and operational detail behind it.

Reports can be exported as CSV. The goal is not to bury a small business in charts; it is to let someone answer routine questions, see assumptions, and get back to operating the store.

5. Inventory as a sequence of movements

StockSense has an append-only stock movement ledger for opening counts, physical counts, deliveries, damage, and corrections. Each entry records the product, store, workspace, movement type, quantity change, resulting quantity, optional note, actor, and time. Product stock edits also create a movement record. Sales imports do not silently overwrite today’s stock.

This is a deliberate shift from treating stock as one mutable integer. The movement history gives the operator a trail to review when counts change. The ledger only records changes from the point a user starts entering them; it cannot reconstruct stock history that was never captured.

The overview’s Daily Action Center flags unknown stock, low-stock or stockout risk, suggested reorders, and missing product costs. Reorder estimates use selected-period average daily units, lead time, and the configured reorder threshold. They are review prompts: they do not model seasonality, confirmed incoming orders, supplier pack sizes, or demand uncertainty, and they never create a purchase order.

6. Budget and basket signals

The purchase budget planner allocates a stated budget across supported reorder suggestions using current recorded cost and stock. It only includes products with enough information to estimate the cost; it does not contact a supplier or place an order.

The marketing view can surface products that appear together in transaction baskets. It uses support, confidence, and lift, and only works when usable transaction IDs are present. These are descriptive co-purchase patterns. They do not prove that one product causes another to sell or measure a promotion effect. Promotion lift, price elasticity, and customer segmentation are not established by this data model.

7. Optional local Gemma through Ollama

The AI path is optional. StockSense can call a locally running Ollama service configured for gemma2:2b. The dashboard exposes model status; model-backed insight generation has a timeout and bounded concurrency. If Ollama is offline, deterministic analytics, reports, imports, and the rest of the dashboard remain available. Natural-language chat falls back to a labeled summary of calculated facts.

That is useful for local experimentation and keeps the model dependency separate from the data pipeline. It does not mean the complete application has been production-hardened: running a model locally still requires suitable hardware and an operator-managed service.

Stack and architecture

• Frontend: Next.js 16, React 19, TypeScript, Tailwind CSS, and Recharts.
• API: Python, FastAPI, SQLAlchemy, and Pydantic.
• Persistence: SQLite for local development; PostgreSQL in the Docker Compose path.
• Schema changes: Alembic migrations. The initial schema and stock-movement migration live in the repository.
• Inference: Optional Ollama HTTP calls to Gemma; no model is required for core metrics.

The high-level request path is:

CSV / Excel import
        ↓
Preview → map columns → validate → confirm
        ↓
Workspace-scoped relational records
        ↓
Python Decimal analytics + inventory signals
        ├── Dashboard and CSV reports
        └── Bounded facts context → optional Ollama/Gemma explanation

For SQLite, the API creates an empty schema at startup. PostgreSQL deployments apply the Alembic migration chain. The checked-in schema.sql is a generated SQLite snapshot; migrations are the source of truth for schema evolution.

Run it locally

Clone the repository:

git clone https://github.com/sleepyaishik69/Stock-Sense.git
cd Stock-Sense

Start the API in one terminal:

cd backend
python -m venv .venv
.venv\Scripts\Activate.ps1
pip install -r requirements.txt
alembic upgrade head
uvicorn app.main:app --reload --port 8000

Then start the Next.js app in another terminal:

cd frontend
npm install
npm run dev

Open http://localhost:3000, create a workspace, and import your own sales file or the synthetic test CSV. API documentation is available locally at http://localhost:8000/docs.

To try the optional local model, install Ollama and run:

ollama pull gemma2:2b
ollama serve

The API reads OLLAMA_BASE_URL, OLLAMA_MODEL, and OLLAMA_TIMEOUT_SECONDS from configuration. For a PostgreSQL-backed local stack, copy .env.example to .env, replace the development secret, then use docker compose up --build. The optional Ollama service is available through the ai Compose profile.

Limits I want to keep visible

StockSense is an MVP foundation, not a finished SaaS product. The repository’s README lists the current limitations and roadmap. Among the important gaps: PostgreSQL row-level security, workspace roles, password reset, CSRF defenses, rate limiting, supplier records, confirmed purchase orders and partial receiving, historical cost snapshots, seasonality-aware demand, promotion analysis, and browser end-to-end tests are not implemented yet.

The session cookie currently supports local HTTP development; a real deployment needs HTTPS and production cookie settings, secret management, careful database credentials, backups, monitoring, and security review. The Compose credentials are for development. Financial output is operational analysis, not accounting, tax, or professional financial advice.

I think making these limits part of the product story is a strength. A retailer should be able to tell which numbers are observed, which are estimated, and which questions the current data cannot answer.

What I learned building it

The most useful engineering decision was to make the data path explicit before adding natural language. It forced me to define net sales, returns, missing costs, transaction identity, stock history, and the boundary between a measured fact and a plausible explanation.

It also changed how I think about “AI analytics.” The impressive part is not just asking a model to summarize a table. It is preparing the right evidence, preserving metric semantics, showing provenance, and letting the system say “there isn’t enough data.” For inventory, it means recording movements instead of pretending a current count explains the past. For costs, it means making missingness visible instead of filling a gap with a guess.