# merchmix-predict-be

Merchmix 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

| Capability | Category | Status | Access | Exposure |
|---|---|---|---|---|
| Retail Data Source Configuration | Other | `production` | `write` | user_facing, agent_facing |
| Dataset Mapping and Validation | Planning | `production` | `write` | user_facing, agent_facing |
| Rate of Sale Calculation | Forecasting | `production` | `execute` | user_facing, agent_facing |
| Demand Forecasting | Forecasting | `production` | `execute` | user_facing, agent_facing, internal |
| Option Planning | Planning | `production` | `execute` | user_facing, agent_facing |
| Inventory Performance Analysis | Inventory | `production` | `read` | user_facing, agent_facing |
| Rate of Sale Drill-Down | Reporting | `production` | `read` | user_facing, agent_facing |
| Inventory Risk Analysis | Inventory | `production` | `read` | user_facing, agent_facing |
| Purchase Order Recommendation | Orders | `beta` | `execute` | user_facing, agent_facing |
| Forecast and Inventory Reporting | Reporting | `production` | `read` | user_facing, agent_facing |
| Forecast Run Management | Other | `production` | `execute` | user_facing, agent_facing, internal |
| Internal Forecasting Assistant | Other | `beta` | `execute` | user_facing, agent_facing |
| Administrator Authentication | Authentication | `production` | `read` | user_facing, agent_facing |

**Retail Data Source Configuration** — Connects the forecasting platform to client retail databases and helps operators inspect their available tables and columns.

**Dataset Mapping and Validation** — Defines which source tables represent sales, inventory, receipts, calendars, products, and stores, including tenant filters and joins, then validates those definitions before calculation.

**Rate of Sale Calculation** — Calculates the weekly selling rate for each product at each store and explains the components behind the result.

**Demand Forecasting** — Projects future product demand and stock levels by week, accounting for available stock and incoming purchase orders.

**Option Planning** — Supports decisions about how many product options should be carried by manufacturer and client, based on forecast and inventory information.

**Inventory Performance Analysis** — Shows stock on hand, weeks of cover, and sell-through so planners can see whether inventory is moving at a healthy pace.

**Rate of Sale Drill-Down** — Lets planners explore inventory performance from client and style views down through merchandise hierarchy and individual allocation or store records.

**Inventory Risk Analysis** — 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.

**Purchase Order Recommendation** — Recommends whether to buy, rebalance, expedite, reduce or cancel supply, or take no action for inventory situations identified by the forecast.

**Forecast and Inventory Reporting** — Provides planner-ready metrics, trends, monthly views, exports, and methodology information for sharing or downstream analysis.

**Forecast Run Management** — Tracks long-running calculations and allows operators or scheduled jobs to monitor their status and retrieve completed results.

**Internal Forecasting Assistant** — Provides an internal chat experience that can answer questions about a client or style using Predict's calculation and recommendation tools.

**Administrator Authentication** — Restricts administrative data-source and forecasting operations to authenticated operators.

## Workflows

**Configure a client data source** — An 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.

**Calculate and serve ROS** — 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.

**Generate a baseline forecast** — 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.

**Train and evaluate forecast challengers** — 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.

**Evaluate inventory actions** — 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.

**Components:** FastAPI application and route layer in app.main and analysis_v2_api., SQLAlchemy models, Alembic migrations, and application database access in app.db, app.models, and migrations., Source discovery, preview, extraction, dataset mapping, and validation services., ROS calculation stack: catalog, calendar, rules, stages, current query, forecast, validation, engine, store, and service modules., Forecasting stack with point-in-time data panels, model contracts, backtesting, tournament selection, and nightly scoring., Predict action stack for scenario overlays, finance, feasibility, confidence, priority, policy gates, recommendations, persistence, and artifacts., Analysis and drill-down services for style-level and hierarchy-level reporting., Yukti chat service, Azure OpenAI client, tool adapter, conversation persistence, and guardrails., Worker and Azure job integration for queued and scheduled execution.

**Patterns:** Asynchronous calculate-once, read-many snapshot architecture., Read-only SQL execution against synced client sources., Tenant-scoped source and dataset configuration., Runtime semantic catalog resolution rather than fixed physical source table names., Point-in-time validation and rolling-origin backtesting for forecasting., SQL-side filtering, sorting, paging, and aggregation for large snapshots., Versioned calculation/model engines and explicit configuration hashes., Stale-while-refresh behavior: serve the latest completed snapshot while a new run executes., Policy-gated scenario recommendation with measured counterfactual benefits.

## Interfaces

| 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

| 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

| 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

| 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

Python 3.13, FastAPI, Uvicorn, Pydantic Settings, SQLAlchemy 2, Alembic, PostgreSQL, psycopg, Polars, Pandas, DuckDB, LightGBM, Azure Container Apps, Azure Container Apps Jobs, Azure Key Vault, Azure Blob Storage, Azure OpenAI, JWT with python-jose, Argon2 password hashing, Pytest, Ruff

## Limitations

- 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.

## Source

| Field | Value |
|---|---|
| Repository | `merchmix-predict-be` |
| Branch | `main` |
| Commit | `941292ec989f` |
| Generated by | `gpt-5.6-luna` |

*Generated by the Merchmix Skills Platform from the current codebase.*