merchmix-predict-be
currentMerchmix Predict is a Python/FastAPI forecasting and merchandise-analysis backend. It connects to client-supplied PostgreSQL data sources, discovers and maps retail datasets, calculates rate of sale (ROS), stock and demand measures, produces stock-constrained forecasts and option-plan outputs, and stores validated calculation snapshots for fast API reads. It also provides inventory-risk analysis, purchase-order recommendations, exports, scheduled Azure jobs, and an internal Yukti chat interface over forecasting tools.
Business summary
This service helps retail planners understand what is selling, how much stock is available, how long stock will last, and what demand is likely to be in future weeks. It highlights risky inventory and can recommend actions such as buying, reallocating, expediting, or reducing purchase orders. Planners and internal tools access the results through APIs and exported files rather than waiting for large calculations to run on every screen.
Capabilities
13Connects the forecasting platform to client retail databases and helps operators inspect their available tables and columns.
Stores tenant-scoped source connection metadata, tests PostgreSQL connectivity, discovers base tables and views through information_schema, previews tables, and supports source deletion. Source credentials are handled through the secret-store abstraction.
Defines which source tables represent sales, inventory, receipts, calendars, products, and stores, including tenant filters and joins, then validates those definitions before calculation.
Uses Dataset, DatasetTable, FieldMapping, and JoinRule models and exposes dataset listing, detail, preview, and validation-related API behavior. The ROS catalog also resolves logical retail tables and semantic columns for each client at runtime.
Calculates the weekly selling rate for each product at each store and explains the components behind the result.
The ROS engine evaluates read-only SQL against synced client PostgreSQL sources for the latest completed retail week. It applies local sales history, hierarchical fallback rates, product/store age stages, season rules, eligibility windows, confidence and validation rules, then persists a queryable snapshot.
Projects future product demand and stock levels by week, accounting for available stock and incoming purchase orders.
Provides baseline forecast endpoints and monthly/trend views. The forecast engine produces stock-constrained demand, sales, lost demand, opening stock, purchase-order receipts, and closing stock over a future horizon. A separate forecasting platform trains and backtests ROS, seasonal-naive, moving-average, damped-trend ETS, and LightGBM direct models, with champion promotion gated by recorded human decisions.
Supports decisions about how many product options should be carried by manufacturer and client, based on forecast and inventory information.
The ROS service has a dedicated option_plan calculation and stored run type. APIs expose client-level option-plan results, summaries, manufacturer detail, recommendations, and exports.
Shows stock on hand, weeks of cover, and sell-through so planners can see whether inventory is moving at a healthy pace.
Provides analysis APIs for sell-through, stock on hand, and weeks of cover. Results are sourced from stored ROS, forecast, and sell-through snapshots, with style-level aggregation performed from the underlying SKU-store data rather than averaging ratios.
Lets planners explore inventory performance from client and style views down through merchandise hierarchy and individual allocation or store records.
Exposes client lists, ROS summaries, exports, SQL diagnostics, style stories, hierarchy levels, and style/store allocation drill-downs. Results are filtered, sorted, paginated, and aggregated over stored snapshots and cached source rollups.
Identifies products and stores at risk from excess stock, weak demand, supply timing, or other forecasted inventory problems, and explains the drivers of the risk.
Risk routes expose overall risk, summaries, metadata, style grouping, item/store detail, and allocation-related views. Migrations create risk_meta and risk_snapshots tables containing forecast snapshot references, simulation counts, coverage information, totals, risk types, and severity-related fields.
Recommends whether to buy, rebalance, expedite, reduce or cancel supply, or take no action for inventory situations identified by the forecast.
The Predict package evaluates candidate scenarios by rerunning the validated forecast against a pinned stock snapshot. It calculates demand impact, feasibility, arrival status, cost, confidence, priority, and tenant policy gates; supported action types include REBALANCE, CREATE_PO, EXPEDITE_PO, REDUCE_CANCEL_PO, and DO_NOTHING. The exposed PO recommendation route serves these results.
Provides planner-ready metrics, trends, monthly views, exports, and methodology information for sharing or downstream analysis.
HTTP endpoints expose forecast, monthly forecast, trend, baseline forecast, ROS, options, metrics, and analysis exports. Stored snapshots and metric results support repeatable reads without recalculating large datasets during page requests.
Tracks long-running calculations and allows operators or scheduled jobs to monitor their status and retrieve completed results.
Pipeline runs have queued, running, succeeded, and failed statuses. Calculations execute in FastAPI background work locally or through Azure Container Apps Jobs in job mode; snapshots are persisted in the application database and stale results can be served while a refresh runs.
Provides an internal chat experience that can answer questions about a client or style using Predict's calculation and recommendation tools.
The Yukti service persists conversations, supplies context from the current run, calls Azure OpenAI through an LLM client, exposes tool definitions from the Predict package, and applies guardrails so numerical responses must be supported by tool results. Conversation deletion is exposed by an HTTP route.
Restricts administrative data-source and forecasting operations to authenticated operators.
Uses admin users, Argon2 password hashing, JWT/session tokens, protected dependencies, and an authenticated /api/v1/auth/me endpoint. The repository also supports machine authentication through configured API-key settings, although the complete route coverage is not established by the endpoint scan.
Workflows
5An administrator registers and verifies a PostgreSQL source before using it for forecasting.
- 1.Authenticate as an administrator.
- 2.Create or update a tenant-scoped source connection through the source-management API.
- 3.Test the connection against PostgreSQL.
- 4.Discover available tables and columns.
- 5.Preview selected tables and define dataset mappings, joins, and tenant filters.
- 6.Validate the dataset definition before starting a calculation.
The system calculates rate of sale once and serves subsequent dashboard requests from a stored snapshot.
- 1.Resolve the client's synced source and semantic table/column catalog.
- 2.Determine the latest completed retail week and eligible history.
- 3.Run the read-only ROS calculation with client configuration and business rules.
- 4.Validate quality metrics and persist the ROS rows and facets as a snapshot.
- 5.Serve filtering, sorting, paging, rollups, exports, and drill-downs from the stored snapshot.
The system projects demand and stock while avoiding long calculations during user requests.
- 1.Start a forecast run for a client, locally in background work or through an Azure Container Apps Job.
- 2.Read validated ROS, sales history, stock, calendar, and inbound purchase-order data.
- 3.Project demand by future week and constrain sales by available stock and receipts.
- 4.Persist forecast rows, metrics, and run metadata.
- 5.Return the latest completed snapshot while a refresh is running, then expose forecast, monthly, trend, and export views.
Nightly jobs compare alternative forecasting models with the incumbent ROS-based forecast.
- 1.Build a point-in-time weekly style/store demand panel from source extracts.
- 2.Train or run the incumbent ROS formula and challenger models.
- 3.Backtest models at rolling historical origins.
- 4.Select a champion per segment only when it beats the incumbent out of sample.
- 5.Score nightly forecasts and keep them beside the ROS walk until an authorised promotion changes the segment mode.
The recommendation engine compares possible supply actions against the same forecast and stock snapshot.
- 1.Load the current validated forecast and pinned inventory state.
- 2.Generate candidate actions such as rebalance, create PO, expedite, or reduce/cancel PO.
- 3.Rerun the forecast for each candidate scenario.
- 4.Measure demand, stock, financial, timing, feasibility, and confidence effects.
- 5.Rank candidates and apply tenant policy gates before publishing recommendations.
Architecture
A FastAPI application separates administrative/source configuration, calculation orchestration, domain engines, snapshot persistence, and read-oriented API shaping. Large calculations run asynchronously and write results to PostgreSQL; API reads use stored snapshots and SQL aggregation. Forecast training and scheduled snapshots are deployed as Azure Container Apps Jobs, while the web API runs as an Azure Container App.
Interfaces
12| Kind | Identifier | Description |
|---|---|---|
| http | FastAPI REST API under /api/v1 | Administrative, source, dataset, run, forecast, analysis, metrics, ROS, option-plan, risk, methodology, tenant, and authentication endpoints. |
| http | GET /api/v1/ros/{client_id} | Returns stored rate-of-sale results for a client. |
| http | GET /api/v1/forecast/{client_id} | Returns stored client forecast results. |
| http | GET /api/v1/analysis/{client_id}/sell-through | Returns stored sell-through analysis. |
| http | GET /api/v1/analysis/{client_id}/soh | Returns stock-on-hand analysis. |
| http | GET /api/v1/analysis/{client_id}/woc | Returns weeks-of-cover analysis. |
| http | GET /api/v1/options/{client_id}/recommendations | Returns option-plan recommendations. |
| http | GET /{client_id}/risk and related risk routes | Returns inventory-risk summaries, metadata, style views, and item/store detail. |
| http | GET /{client_id}/po-recommendation | Returns purchase-order/action recommendations. |
| http | GET /{client_id}/yukti/{style}/conversation | Supports the internal style-focused Yukti conversation interface; conversation deletion is exposed via DELETE. |
| cli | merchmix-predict-worker | Runs queued forecasting work through app.worker:main. |
| other | Azure Container Apps Jobs | Scheduled and manually started jobs for risk/predict snapshots and forecast training. |
Screens
7| Route | Name | Purpose |
|---|---|---|
/api/v1/ros/{client_id} | Rate of Sale Dashboard | Review product-by-store selling rates, components, confidence, and exportable detail. |
/api/v1/forecast/{client_id} | Forecast Dashboard | Review projected demand and stock by client, with monthly and trend views. |
/api/v1/options/{client_id}/recommendations | Option Plan | Review recommended option quantities and manufacturer-level planning results. |
/{client_id}/risk | Inventory Risk | Review products and stores with forecasted inventory risk. |
/{client_id}/po-recommendation | Purchase Recommendations | Review ranked actions for buying, rebalancing, expediting, or reducing supply. |
/{client_id}/levels/{level} | Merchandise Hierarchy Drill-Down | Explore stock, demand, and forecast measures at a selected merchandise level. |
/{client_id}/yukti/{style}/conversation | Yukti Internal Assistant | Ask questions about a style using the service's forecasting and recommendation tools. |
Data
19| Entity | Ownership | Description |
|---|---|---|
| Tenant | owns | Retail client or organisational tenant used to scope sources and datasets. |
| AdminUser | owns | Administrator identity, active status, and password hash used for access control. |
| SourceConnection | owns | Tenant-scoped PostgreSQL source metadata and connection status; passwords are handled through the secret-store path. |
| Dataset | owns | Named dataset definition for a tenant, including its status and selected source tables. |
| DatasetTable | owns | Mapped source table for a retail entity such as sales, inventory, receipts, calendar, product, or store. |
| FieldMapping | owns | Mapping from source fields to the semantic fields required by calculations. |
| JoinRule | owns | Configured relationships between mapped source tables. |
| PipelineRun | owns | Queued, running, successful, or failed calculation execution and its status metadata. |
| RunSnapshot | owns | Persisted calculation output and facets for ROS, forecast, option plan, and sell-through runs. |
| ROS Row | owns | SKU-store rate-of-sale result with sales history, fallback components, eligibility, stage, confidence, and as-of information. |
| Forecast Row | owns | Future period demand, sales, stock, purchase-order receipt, lost-demand, opening-stock, and closing-stock result. |
| BaselineForecast | owns | Stored baseline forecast records exposed by the application API. |
| MetricConfig | owns | Configured calculation metric definitions. |
| MetricResult | owns | Persisted metric outputs and exportable calculation measures. |
| Risk Snapshot | owns | Client/style/store inventory-risk result, including risk type and supporting merchandise attributes. |
| Risk Metadata | owns | Risk run date, forecast snapshot reference, horizon, coverage, simulation counts, failures, invariant checks, and totals. |
| YuktiConversation | owns | Persisted internal assistant conversation messages keyed by client, style, and user. |
| Client Retail Source Data | reads | Sales, inventory/stock, receipts or purchase orders, calendar, product, store, and related synced tables used as calculation inputs. |
| Forecast Training Extracts | reads | Parquet/manifest or local analytical extracts used by the forecasting training and scoring pipeline. |
Dependencies
13| Name | Kind | Relationship | Criticality |
|---|---|---|---|
| Application PostgreSQL | database | reads | critical |
| Client synced PostgreSQL sources | database | reads | critical |
| Azure Container Apps Jobs | internal service | calls | required |
| Azure Key Vault | external service | uses | required |
| Azure OpenAI | external service | calls | optional |
| Azure Blob Storage / ADLS | external service | writes | optional |
| merchmix-markdowns-api | internal service | calls | optional |
| DuckDB | database | uses | supporting |
| Polars and Pandas | library | uses | critical |
| LightGBM | library | uses | optional |
| SQLAlchemy and Alembic | library | uses | critical |
| Redis | database | uses | supporting |
| NATS | queue | uses | supporting |
Technology
Limitations
7- —The supplied endpoint scan does not list POST or PUT routes, although the source models and README indicate write workflows for sources, datasets, and runs; exact write-route exposure should be verified from the complete app.main file or OpenAPI schema.
- —The evidence establishes risk and purchase-recommendation implementations, but does not establish that recommendations directly create or modify purchase orders in an external ordering system.
- —Markdown action types are registered in the recommendation domain, but the source states markdown response generation is not implemented in the current phase.
- —Forecast challenger training and champion promotion are implemented in the repository, but the evidence does not show which models are currently promoted for any production client.
- —Redis, Redis TLS, NATS, Databricks, and the markdown API appear in scanner signals or host lists, but their active runtime usage is not fully demonstrated by the supplied authoritative excerpts.
- —The repository contains both legacy /api/v1 routes and newer client-prefixed routes; their complete compatibility and intended frontend ownership are not established.
- —No direct evidence was provided for a GraphQL, gRPC, or public webhook interface.
Agent instructions
Treat this repository as the forecasting, inventory-analysis, risk, and action-recommendation backend for Merchmix. Prefer stored validated snapshots for reads and use the calculation/run APIs for refreshes rather than assuming a request should calculate synchronously.
- →For planner questions about selling rate, stock, weeks of cover, sell-through, forecast, option planning, or risk, route to the corresponding client-scoped API capability.
- →Use the ROS, forecast, and risk results' as-of dates and run metadata; do not combine values from different snapshots without checking their dates and snapshot references.
- →Expect long-running calculations to return queued/running or stale-but-available results. Do not retry aggressively or assume an empty result means the client has no data.
- →For source onboarding, preserve tenant scope and use discovery, preview, mapping, join, and validation steps before requesting a calculation.
- →Treat source credentials and all environment variable values as secrets; never request, expose, or infer them.
- →For purchase recommendations, explain action, quantity basis, arrival status, feasibility, confidence, and policy gating separately; an unknown arrival time does not necessarily mean the demand quantity is unsupported.
- →Do not claim that a recommendation has executed a PO, transfer, or markdown unless another system confirms execution; this repository primarily calculates and publishes recommendations.
- →Use Yukti only as an explanatory interface over tool-backed results. Numerical answers should be traceable to returned tool data and should include the relevant client/style and as-of context.