AI Sales Forecasting

Documented design

• A read-only connection to a data warehouse supplies the model's forecasts, actual sales and master data. The repository holds no model training or inference code.
• One forecast table keeps three volumes per row: the model volume, the adjusted volume and the final volume, plus the workflow columns (flag, submitter, rejection reason). The adjustment paths do not change the model volume; reverting an adjustment copies it back.
• A row is identified by the forecast month, the target month, the customer, the product and a lead time of one to six months.
• A daily job at 07:00 reads the trigger months flagged in a warehouse event table. For each month not yet marked as synced it loads the forecast in chunks of 5,000 rows and then marks the month. A month that fails stays pending and is retried the next day. The same run replaces the previous day's month of actual sales.
• A monthly job on the first day syncs employees and product colors. An admin route can reload the whole forecast table in one transaction, which also replaces the adjustments.

Documented design

• The grid endpoint builds one SQL statement with common table expressions: actual sales, the history forecast (lead time one), the future forecast (the snapshot of the target month) and a full outer join, so months that have only a forecast or only actuals still appear.
• The window starts in January of the target year (or three months back when the target is before March) and ends six months after the target month.
• A per-user response cache is keyed by role, identity, scope and normalized filters. The time to live defaults to 300 seconds and the cap to 100,000 cached rows; a larger result is not cached. Lock status is computed live and never cached.
• A change to the data clears the caches in every API process through a PostgreSQL LISTEN and NOTIFY channel. Name lookups use a stale-while-revalidate cache, and the forecast table has five indexes, one of them partial.

Documented design

• A forecast cell moves from no flag to SUBMITTED and then to APPROVED or REJECTED. The adjustment is stored on the forecast row itself; approval copies the adjusted volume into the final volume, and a rejection requires a reason and keeps the adjusted volume.
• Submit validates that rows are present, that each volume is a finite number that is not negative and that the lead time is an integer. The adjusted total of a cell is spread across the customer rows in proportion to their model volume, and the last row absorbs the rounding remainder.
• Submit runs in one transaction and retries up to three times on a deadlock or serialization error. A sales manager cannot resubmit rows that are already submitted or approved.
• Approve is one UPDATE on rows that are still SUBMITTED, so a repeated approval changes nothing. Approve-all does the same set-based update for every submitted row after the forecast month, with optional filters. Deleting a submission restores the model volume.
• After the response, audit entries and notifications are written on a best-effort basis, and the lock state of the manager is updated.
• Drafts are stored per user and month with one atomic upsert-and-delete statement, and the browser syncs them after one second of idle time, at most ten seconds after the first change.
• A transfer request moves forecast rows between sales offices. It is created in one transaction, and its approval, by a line manager or a full-access role, rewrites the office on the stored rows in one transaction.

Documented design

• Rows form a hierarchy. Each shows three series (model, adjusted and actual) and the adjusted one is editable. The grid is virtualized with TanStack Virtual and supports expandable rows and pinned columns.
• Editing a leaf stores a draft on that leaf only, and every parent is recomputed as the exact sum of its children.
• Editing a parent splits the rounded target over its children for that month, recursively. Weights are the children's model volumes, or their actual sales when the model volumes sum to zero; with no weight at all, the split is equal. Each share is rounded down, and the leftover units go one by one to the children with the largest fractional parts, ties by order. The shares always sum to the rounded target.
• A parent edit spreads only over the children that are visible after filtering. Cells at or before the forecast month, in a locked period, or already submitted are not editable, and negative values are rejected in the interface.

Documented design

• For each sales manager, product group and lead time from one to six months, the service compares the sum of the final volume with the sum of actual sales over the last six completed months.
• The error is a signed percentage, (forecast minus actual) divided by actual and multiplied by 100, and is skipped when actual sales are zero or missing. The average is the plain mean of the signed values; despite the name used in the code, it is not an absolute error.
• A streak counts the most recent months whose absolute error is above the bad threshold, skipping months without data, and a warning is raised when the streak reaches the configured number of months. Defaults are 15 for good, 25 for bad and 3 months when no configuration row exists.
• Results are stored in a history table and refreshed at startup and after each daily sync. A separate comparison view shows, per month and lead time, the model volume, the adjusted volume and actual sales with a percentage difference for both.

Documented design

• Three scheduled jobs run with node-cron in the local business timezone: on day 2 the lock records for the next forecast month are generated, on day 24 a reminder goes to managers who have not submitted, and on day 25 every still-open record is locked automatically.
• A lock has a status (open, partially locked or locked), a type and a scope, either a business unit or one sales manager. Only the active month and the previous month are valid periods.
• A partial lock leaves only cells that are neither submitted nor approved editable. A manager is locked automatically once none of their rows is left to edit.
• Unlocking an automatic lock needs an explicit force and a reason, is refused while submissions are pending, and can carry an expiry. An unlocked approved cell becomes editable again and is approved again afterwards.
• Which managers appear under a business unit depends on their user record: active, with a line manager and an employee code, and the unit among their sales groups.

Documented design

• Sign-in uses Microsoft Entra SSO through the authorization-code flow with PKCE. The state is an HMAC-signed payload with a short expiry, and the user must exist and be active in the application's own user table, which supplies the role and the scope.
• There are 5 roles and 16 permission keys. The defaults live in code; an editable matrix is stored in the database, kept in a process cache, and changed only by a system admin. The token carries the permission list and a version number: changing the matrix raises the version, so tokens issued earlier are rejected and the user signs in again.
• Data is scoped by services from the signed-in identity: a sales manager sees the customers mapped to them, and an executive sees the customers of the sales managers they are in charge of. Admin roles are scoped by division or sales group where the user record defines one.
• The audit log records about 40 action types across 18 modules, with before and after values and the client address. Rows are only inserted by the application, and there is no update or delete route.
• Notifications are in-app messages and email. Email goes through a queue with retries after 1, 5 and 15 minutes before it is marked failed, and a notification failure never fails the request.
• The interface and the API messages are available in Vietnamese and English; the language travels in a request header, and message keys are typed so that a missing key is a compile error.