pivot_forecast/AGENTS.md

pivot_forecast

Sales forecasting API using Node.js/Express + PostgreSQL. Takes historical sales data and generates forecast baselines, with the ability to scale/adjust forecasts for different scenarios.

Project Structure

pivot_forecast/
├── index.js                 # Express server (2 routes: /baseline, /scale)
├── package.json
├── .env                     # DB connection (host, port, user, password, database)
├── routes/
│   ├── baseline/
│   │   ├── baseline.sql     # SQL for baseline generation
│   │   ├── generate_route_sql.sh
│   │   └── req.json         # Example request
│   └── scale/
│       ├── scale.sql        # SQL for scaling
│       ├── generate_route_sql.sh
│       └── req.json
├── setup_sql/
│   ├── 01_schema.sql        # Create fc schema, tables, period data
│   ├── 02_target_info.sql   # Populate target_meta from dcard columns
│   └── 03_build_master_tables.sql
└── readme.md

Database Schema (fc schema)

Tables

Table	Purpose
fc.dcard	Main sales data (copied from source at setup)
fc.target_meta	Column metadata for dcard
fc.appcols	App column definitions (value, cost, units, dates, status)
fc.log	Forecast change log (JSONB)
fc.sql	Stored SQL commands
fc.perd	Fiscal period/calendar (2008-2023)

target_meta Column Definitions

Field	Description
schema	Schema of target table
tname	Table name
cname	Column name
opos	Ordinal position
func	Functional entity (e.g., "cost", "scust") - creates master table
fkey	Primary key for functional entity
fcol	Associated field in master table if different
dtype	Data type (numeric, text, date)
appcol	App-facing column name (e.g., "customer", "item")
pretty	User-friendly display name

Example target_meta row:

| fc      | live    | fb_cst_loc     | 91   | cost         |              | fb_cst_loc     | numeric |              |        |

appcols Requirements

Column	Type	Required	Default
value	numeric	yes	-
cost	numeric	yes	0
units	numeric	yes	0
order_date	date	yes	-
ship_date	date	no	-
order_status	text	yes	CLOSED
version	text	yes	ACTUALS
iter	text	yes	ACTUALS
logid	bigint	yes	-
tag	text	yes	-
comment	text	yes	-
customer	text	no	-
item	text	no	-

Setup

1. Configure Environment

cp .env-sample .env
# Edit .env with your PostgreSQL credentials

2. Run Database Setup

psql -h $host -U $user -d $database -f setup_sql/01_schema.sql
psql -h $host -U $user -d $database -f setup_sql/02_target_info.sql
psql -h $host -U $user -d $database -f setup_sql/03_build_master_tables.sql

3. Populate target_meta

Edit target_meta with the column mappings for your data:

UPDATE fc.target_meta SET func = 'cost', fkey = '', appcol = 'cost' WHERE cname = 'fb_cst_loc';
UPDATE fc.target_meta SET func = 'scust', fkey = 'ship_cust', appcol = 'customer' WHERE cname = 'ship_cust';
-- etc.

4. Generate Route SQL

./routes/baseline/generate_route_sql.sh
./routes/scale/generate_route_sql.sh

5. Start Server

node index.js
# Server runs on port in .env (default 3000)

API Routes

GET /baseline

Generate forecast baseline - mirrors historical data for forecast periods.

Request body:

{
  "app_forecast_name": "Q1_2024",
  "app_forecast_version": "v1",
  "app_scenario": {
    "drange": "[2024-01-01,2024-03-31)"
  }
}

Example curl:

curl -X GET http://localhost:3000/baseline \
  -H "Content-Type: application/json" \
  -d '{"app_forecast_name":"Q1_2024","app_forecast_version":"v1","app_scenario":{"drange":"[2024-01-01,2024-03-31)"}}'

GET /scale

Scale a selected data slice by specified amounts.

Request body:

{
  "app_forecast_name": "Q1_2024_scaled",
  "app_where": "part = 'XFRM500'",
  "app_scenario": {
    "scust": "Sanford and Son"
  }
}

Example curl:

curl -X GET http://localhost:3000/scale \
  -H "Content-Type: application/json" \
  -d '{"app_forecast_name":"Q1_2024_scaled","app_scenario":{"scust":"Sanford and Son"}}'

Common Tasks

Add a new forecast dimension

1. Add column mapping to fc.target_meta:

   INSERT INTO fc.target_meta (schema, tname, cname, opos, func, fkey, fcol, dtype, appcol)
   VALUES ('fc', 'dcard', 'new_column', 99, 'new_func', 'new_column', 'new_column', 'text', 'new_dim');
   

2. Run ./routes/baseline/generate_route_sql.sh
3. Run ./routes/scale/generate_route_sql.sh

Adjust forecast for a specific customer

curl -X GET http://localhost:3000/scale \
  -H "Content-Type: application/json" \
  -d '{
    "app_forecast_name": "Adjusted_Forecast",
    "app_scenario": {
      "scust": ["Customer A", "Customer B"]
    }
  }'

View forecast logs

SELECT * FROM fc.log ORDER BY id DESC LIMIT 10;

Add new fiscal periods

Insert into fc.perd with columns: comp, fsyr, perds, perd, fspr, sdat, edat, drange, capr, ndays, ssyr, sspr

Known Issues

• SQL stored in files instead of database table
• Null handling in func tables (orders without quotes)
• Multi-table target_meta support
• Currency adjustment handling (which currency?)

Testing

No automated test suite. Manual testing via curl commands in routes/*/req.json examples.