# AUDIT_REPORT.md — Polleo Demand modular monolith

**Scope.** Only the new FastAPI + React + Postgres app — `backend/`, `frontend/`, `db/`, `deploy/`, `scripts/`, `docker-compose.yml`, root `requirements.txt`, `.env*`, `.gitignore`. The legacy Streamlit world (`app.py`, `forecast_engine.py`, `Demo/`, `PromoTool/`, `PromoCalendar/`, the ABC tactical scripts, root-level `build_*.py` / `analyze_*.py`) is **out of scope** here and is only mentioned where the new app inherits or depends on it.

**Codebase size in scope.** ~18,675 lines of Python across `backend/` (services 9.6k, repos 4.4k, routers 2.3k, auth/config/models 0.4k) + 667 lines of SQL DDL in `db/schema.sql` + 31 React/TS pages and ~12 shared components in `frontend/src/`. 2 git commits — almost all of this is uncommitted on `master`.

**Verdict for go-live.** Architecture and layering are clean (routers → services → repos → DB). Auth is solid where it's wired. Three classes of issues block a clean handover:

| # | Class | Where it shows up |
|---|---|---|
| 1 | **Secrets committed** | `.env` (Slack token), `deploy/smoke_test.sh` (default password) |
| 2 | **Backend authorization gap** | 73 endpoints in `/api/demand`, `/api/supply`, `/api/promo` rely only on "is logged in", not on role — the role permission matrix is enforced *only on the sidebar and on finance/executive/npl* |
| 3 | **Documented behaviour ≠ code** | `coverage_classifier` docstring claims to be the single source of truth; new-user "dev mode" comments contradict the no-bypass auth_service. Several stale comments and dangling YAML keys. |

Below: each finding with file:line, what's wrong, and what to do.

---

## 1. Architecture and layering (what's good)

The monolith follows a clean layering you can hand off:

```
HTTP                ── FastAPI routers (backend/api/routers/*.py)
                       Thin: parse query/body, return service result.

Business logic      ── Services (backend/services/*.py)
                       Pure Python where possible (scenario_service has a
                       "caller passes resolved data maps" contract — kept).
                       No SQL except in repo calls. No FastAPI imports.

Persistence         ── Repositories (backend/repositories/*.py)
                       The only place SQL lives.

Schema              ── db/schema.sql + db/migrations/*.sql
                       DROP-then-CREATE; idempotent via python -m db.init_db.

Auth                ── backend/api/middleware/auth.py +
                       backend/services/auth_service.py +
                       backend/services/permissions.py.

Config              ── backend/config.py (pydantic-settings + .env)
                       Production-mode startup validation refuses to launch
                       with the dev JWT secret or localhost CORS.

DB connection       ── backend/models/database.py (HTTP request pool) +
                       db/connection.py (CLI / migrator pool).

Frontend            ── React + Vite + TypeScript. /api proxied by Vite in
                       dev, by nginx in prod. SessionStorage JWT. Sidebar
                       filters items by /auth/me/sections.
```

Mermaid for the request path:

```mermaid
flowchart LR
  B[Browser] -->|HTTPS| N[nginx 443]
  N -->|static| FE[frontend/dist]
  N -->|/api/*| API[uvicorn 8000<br/>backend.main:app]
  API --> MW[middleware.auth<br/>get_current_user<br/>require_role/section]
  MW --> R[Routers]
  R --> S[Services]
  S --> P[Repositories]
  P --> PG[(Postgres 16)]
  S -. CSV fallback .-> CSV[(data/*.csv)]
  R --> Perm[permissions.role_can]
  Perm --> Y[role_permissions.yaml]
```

What I'm not flagging as a problem (because it's deliberate and documented):
- Two SQLAlchemy engines (`backend/models/database.py` for HTTP, `db/connection.py` for CLI). Both docstrings explain why.
- CSV-fallback layer (`db/csv_fallback.py`) — explicit, logs which path it took.
- Per-request `SessionLocal` via `Depends(get_db)` with `pool_pre_ping=True`.
- `BaseRepository.ping()` + `/api/health` separation.
- `_validate_production()` startup gate in `backend/config.py:48-73`.

---

## 2. Critical findings

### 2.1 🔴 Live Slack token committed to `.env`

```
$ cat .env
POLLEO_SLACK_TOKEN=xoxb-640320924208-10915260575845-7I4yubNlqXlMH9yUrhrSjCc9
```

`.gitignore:9` lists `.env`, but the file is **already tracked** (it's in `git ls-files`, and `git status` doesn't show it as untracked). Adding to `.gitignore` after the fact doesn't untrack the file — Git still publishes it on push.

This token belongs to the legacy `slack_agent.py` Streamlit integration; the FastAPI backend doesn't read it. But it's a real bot token that anyone with repo read access can use to read/post in Polleo Slack channels.

**Fix (do in this order):**
1. **Rotate the token in Slack first** (https://api.slack.com/apps → reissue). Do this before pushing, because the next bullet makes the leak visible in history.
2. `git rm --cached .env` and commit. `.env` then stays on disk but is no longer tracked.
3. Optional: `git filter-repo` to scrub the secret from history. Worth doing before any external party clones the repo. (With only 2 commits the rewrite is cheap.)
4. Move the token into the production `.env` on the server (the systemd unit at `deploy/polleo-demand-api.service:19` reads `/opt/polleo-demand/.env`).
5. Verify `deploy/DEPLOY.md:75` (`chmod 600 .env`) is followed.

### 2.2 🔴 Default smoke-test password committed

`deploy/smoke_test.sh:14`:
```bash
PASSWORD="${SMOKE_PW:-Lovro2575}"
```

Either `Lovro2575` is Lovro's real production password (= a secret leak), or it's a placeholder that anyone running the smoke test will use to log in (= a credential collision). Either way, this is committed and visible.

**Fix.** Drop the default (`PASSWORD="${SMOKE_PW:?set SMOKE_PW env}"`), and rotate Lovro's password if `Lovro2575` was real. The login lockout (`backend/api/routers/auth.py:29-31`, 5 fails/60s) limits brute force, but an attacker with the file knows the password.

### 2.3 🔴 Backend role enforcement is incomplete

`backend/main.py:51-56` mounts demand/supply/promo/npl/finance/executive routers with `dependencies=[Depends(get_current_user)]`. That gate only verifies *the JWT is valid*, not *what role the user has*.

Of the seven feature routers:

| Router | Endpoints | Per-endpoint role gate? |
|---|---|---|
| `auth.py` | 4 | Public login + per-endpoint `get_current_user` ✓ |
| `admin.py` | 4 | Router-level `require_role("admin")` ✓ at line 19 |
| `finance.py` | 13 | `require_section("finance_*")` per endpoint ✓ at lines 27-32 |
| `executive.py` | 7 | `require_section("executive_dashboard")` per endpoint ✓ at line 24 |
| `npl.py` | 23 | `require_section("npl_module"/"npl_report"/"npl_admin_enum")` ✓ at lines 67-69 |
| `demand.py` | **31** | ❌ NONE — only `get_current_user` |
| `supply.py` | **21** | ❌ NONE — only `get_current_user` |
| `promo.py` | **21** | ❌ NONE — only `get_current_user` |

That's **73 endpoints** any authenticated user can call, regardless of role.

Concretely, `role_permissions.yaml` says (lines 33-67):

```
upload_sales:      [Admin]                          # ← only Admin should call POST /demand/upload-sales
run_forecast:      [Admin]                          # ← only Admin should call POST /demand/run-forecast
demand_planning:   [Admin]                          # ← only Admin
kam_cm_input:      [Admin, Veleprodaja, Nabava]     # ← Maloprodaja/Marketing/Uprava locked out
order_entry:       [Admin, Nabava]                  # ← only Admin + Nabava
costs_margins:     [Admin]                          # ← only Admin
moq_analysis:      [Admin]
store_overstock:   [Admin]
```

The frontend correctly hides these from the sidebar via `Sidebar.tsx:235-237` (filter by `/auth/me/sections`). But sidebar filtering is cosmetic — the URLs are predictable and a logged-in `Maloprodaja` user can hit `GET /api/supply/order-suggestions` or `POST /api/demand/run-forecast` directly with `curl -H "Authorization: Bearer ..."`.

**Fix (small, mechanical, do before go-live).** Add `dependencies=[Depends(require_section("…"))]` per endpoint, or a module-level `dependencies` list per router. The finance router (lines 27-32) shows the pattern:

```python
# backend/api/routers/finance.py:27-32
_REQ_DASH    = Depends(require_section("finance_dashboard"))
_REQ_LOST    = Depends(require_section("finance_lost_sales"))
# …

@router.get("/dashboard", response_model=FinanceDashboard)
def get_dashboard(db: Session = Depends(get_db),
                  _: CurrentUser = _REQ_DASH) -> FinanceDashboard:
    …
```

Apply that to:
- `demand.py`: 31 endpoints, mapped against `upload_sales` / `upload_stock` / `run_forecast` / `kam_cm_input` / `demand_planning` / `input_status` / `sop_meeting` / `consensus_plan` / `sales_weekly` / `sales_history` / `revenue_forecast` / `forecast_accuracy` / `watchlist` / `sku_list` / `sku_detail`.
- `supply.py`: 21 endpoints, against `order_entry` / `settings` / `supply_dashboard` / `inventory_health` / `stock_projection` / `coverage_workbook` / `reorder_alerts` / `scenario_planner` / `store_overstock` / `moq_analysis` / `logistics_master` / `costs_margins`.
- `promo.py`: 21 endpoints, against `promo_planner` / `promo_my_proposals` / `promo_approvals` / `promo_overview` / `promo_calendar` / `promo_forecaster` / `promo_past` / `promo_performance` / `promo_past_web`.

This is the single biggest go-live blocker. The infrastructure is in place — `require_section` exists, the YAML matrix is correct, the helper is solid (`backend/services/permissions.py:59-67`) — just not wired into 3 routers.

### 2.4 🟠 `coverage_classifier` docstring claims single source — false

`backend/services/coverage_classifier.py:3-8`:
> Every module that classifies inventory health now goes through `classify_coverage()` so the same SKU has the same status on every page: Executive Stockout, Supply Alerts, Finance Locked Cash + Lost Sales, and Inventory Health.

Reality (`grep classify_coverage backend/`):

```
backend/services/executive_service.py:21:from backend.services.coverage_classifier import classify_coverage
backend/services/finance_service.py:25:  from backend.services.coverage_classifier import classify_coverage
```

Only **executive_service** and **finance_service** import it. `supply_service` uses its own `_classify` at `backend/services/supply_service.py:51-60`:

```python
def _classify(coverage: Optional[float]) -> str:
    if coverage is None: return _STATUS_OK
    if coverage < 2:  return _STATUS_ORDER_NOW
    if coverage < 4:  return _STATUS_ORDER_SOON
    if coverage > 13: return _STATUS_PULL_IN
    return _STATUS_OK
```

This is **fixed absolute weeks** — entirely different math from `classify_coverage`, which is LT-relative (`≤LT critical`, `≤1.5×LT order_soon`, `≤2×LT healthy`, `>2×LT overstock` plus `MISSING_LT` / `NO_DEMAND`).

So a single SKU can show:
- **Supply / Dashboard / Stock-projection / Reorder-alerts** → "Order Now" because `coverage < 2`
- **Executive Stockout Risk / Finance Locked Cash / Finance Lost Sales** → "HEALTHY" because `coverage = 1.8 weeks` but `LT = 1 week` → `coverage > LT` (i.e. eff_cover ≤ 2×LT)

Same SKU, same data, two different status labels depending on which page you opened.

The Supply Alerts page does use a third, lead-time-aware classifier in `_compute_suggestions` (referenced by `get_alerts` at line 529), which produces "Order Now / Order Soon / Pull in" with its own thresholds — and that one *is* aware of LT and incoming-in-LT. So the Alerts page and the Dashboard/Projection page within Supply itself disagree with each other on what "Order Now" means.

**Fix options (pick one and stick to it):**
1. Migrate `supply_service.get_supply_dashboard` and `.get_stock_projection_data` to use `coverage_classifier.classify_coverage` (matches the docstring claim).
2. Update `coverage_classifier.py:3-8` to honestly say "Used by Executive + Finance only".

Option 1 is the right answer for consistency, but it changes the headline numbers a long-time Streamlit user is used to. Make the choice explicitly before deploy.

### 2.5 🟠 `auth_service.change_password` docstring lies; admin create-user comment lies

`backend/services/auth_service.py:117`:
```python
def change_password(db, user_id, old_password, new_password):
    """Verify old password before setting new. Dev-mode (NULL hash)
    bypasses the old-password check so first-time setup works."""
```

This claim doesn't match the code. `verify_password` at line 31-40 returns `False` for an empty/NULL hash:
```python
def verify_password(plain, hashed):
    if not hashed:
        return False
    …
```

So a user with `password_hash = NULL` calls `/api/auth/change-password` → `verify_password(old, None) → False` → `change_password → False` → 400.

The top-of-module docstring (lines 4-9) is correct: "every user MUST have a bcrypt password_hash … no longer a 'dev backdoor'." The `change_password` function-level docstring at line 117 still describes the removed behaviour.

Same issue in `backend/api/routers/admin.py:47`:
```python
class UserCreate(BaseModel):
    …
    password: Optional[str] = None  # if None, user enters dev-mode (any pw works)
```

After `auth_service` removed the dev backdoor, creating a user without a password creates one who **cannot log in**. Admin must call `/api/admin/users/{id}/reset-password` afterwards. The comment is misleading and will trip up whoever creates the first batch of users.

**Fix.** Two doc edits — one line each:
- `backend/services/auth_service.py:117-118` → remove the dev-mode sentence.
- `backend/api/routers/admin.py:47` → change to `# if None, user has no password and must be set via /api/admin/users/{id}/reset-password before they can log in.`

### 2.6 🟠 Dangling permission keys (sidebar ↔ YAML drift)

`role_permissions.yaml` lists these section IDs that **no Sidebar.tsx item references** and **no `require_section()` call uses**:

| Section ID | Defined in YAML at | Used by Sidebar? | Used by backend? |
|---|---|---|---|
| `sku_detail` | line 49 | ❌ (App.tsx routes `/demand/sku/:sku` but Sidebar doesn't list it — users reach SkuDetail from SkuList) | ❌ |
| `logistics_by_supplier` | line 54 | ❌ | ❌ |
| `logistics_detail` | line 55 | ❌ | ❌ |
| `logistics_by_week` | line 68 | ❌ | ❌ |
| `logistics_pallet_flow` | line 69 | ❌ | ❌ |
| `logistics_truck_plan` | line 70 | ❌ | ❌ |
| `logistics_moq_audit` | line 71 | ❌ | ❌ |
| `npl_dashboard` | line 90 | ❌ (only `npl_module` and `npl_report` are sidebar items) | ❌ |
| `npl_admin_enum` | line 103 | ❌ (it's referenced by `backend/api/routers/npl.py:69`) | ✓ |

The "logistics_*" sub-sections look like granular splits that were planned but the sidebar consolidated under a single `logistics_master` entry (`Sidebar.tsx:118`). Same story for `npl_dashboard` — there's a `NplDashboardWidget.tsx` component imported on the demand Dashboard, but no top-level page that gates on it.

`npl_admin_enum` is the opposite — used by the backend (`/api/npl/enum-values` is admin-only) but never appears in the frontend.

**Fix.** Either delete the unused YAML keys, or wire them up. Leaving them in invites confusion: someone editing YAML to grant a role won't know which keys actually do anything.

### 2.7 🟡 Stale "single-user Streamlit" comment on the FastAPI engine path

`db/connection.py:55-59`:
```python
def get_engine() -> Engine:
    """Return a process-wide SQLAlchemy engine with a small connection pool.

    pool_size=5 / max_overflow=10 is sized for a single-user Streamlit
    session running pandas read_sql calls. Bump if multiple concurrent
    page renders start blocking on connection checkout.
    """
```

This engine is now shared by:
- `scripts/cfo_audit.py` (long-running, batch)
- `scripts/recompute_ruc.py`, `cfo_audit_step0.py`, `refill_names_and_datalink.py`, `reload_costs_suppliers.py`, `reload_incoming_supply.py`
- `ABC analiza PO/build_reinvest_plan.py`

…and the FastAPI app has its OWN engine in `backend/models/database.py:17-23` (same pool size, separate pool). So total concurrent connections capped by Postgres = `pool_size + max_overflow` × 2 = 30. The systemd unit at `deploy/polleo-demand-api.service:25` runs `--workers 2` uvicorn, so HTTP capacity is `2 × 15 = 30` connections.

Postgres 16 default `max_connections` is 100, so there's headroom. Just update the comment to reflect that this engine is "CLI / migrator / scripts" path, used by `db/init_db.py` and `scripts/*.py`. The HTTP engine is documented correctly.

### 2.8 🟡 Scratch / one-shot scripts checked into `db/`

`db/` contains ~30 one-off helpers that aren't imported anywhere production:

```
_apply_npl.py            _bridge_step0_discovery.py   _bridge_v2_investigation.py
_check_12929.py          _check_avg_sell_price.py     _check_bridge_coverage.py
_check_channel_split.py  _check_cogs_sources.py       _check_cost_vs_pv.py
_check_exec_perms.py     _check_incoming_load.py      _check_new_listing.py
_check_plan_xlsx.py      _check_pol12887_ruc.py       _check_policy_fields.py
_check_rabatne_politike.py _check_rekap_columns.py    _check_revenue_3ways.py
_compare_eur.py          _create_executives.py        _create_iklaric.py
_debug_promo_match.py    _debug_revenue_forecast.py   _debug_stockout.py
_explain_prices.py       _find_mci.py                 _inspect_cost.py
_inspect_nabavne.py      _inspect_names.py            _inspect_plan_qty.py
_inspect_ruc.py          _inspect_sales_for_npd.py    _mci_full.py
_move_mci.py             _reload_forecast_tables.py   _sales_coverage.py
_smoke_npl.py            _verify_channel_ruc.py       _verify_new_excluded.py
_verify_reload.py        _who_inflated_cw21.py
```

None of these are imported by `backend/` or by the production scripts. Most are recent (May-21+) and untracked. They're investigation breadcrumbs from migrating data into the new schema.

**Fix.** Move to `db/archive/` (or just `data/_audits/`) and add to `.gitignore`. If you need them again you'll find them in branches; cluttering the production `db/` package makes it hard to tell migrations apart from scratch work, and `from db.foo` autocomplete is noisy.

### 2.9 🟡 `scenario_service` classifier diverges from the ABC scripts it's named after

`backend/services/scenario_service.py:82-92`:
```python
def classify(real_cover_now, real_post_delivery_cover, cancel_threshold, postpone_trigger=4.0):
    if cancel_threshold is not None and real_cover_now >= cancel_threshold:
        return "CANCEL"
    if real_cover_now >= postpone_trigger:
        return "POSTPONE"
    return "PRODUCE"
```

The file claims (line 4-5) to be a "port of Streamlit `page_supply_scenarios` … mirrors app.py:7060-7229 verbatim so the React + Streamlit versions stay numerically identical." But the offline cousin (`_abc_3scenarios.py:106-113`, the script that produced the supplier-facing CSVs) does:

```python
def classify(real_cover_now, real_post_delivery_cover, cancel_threshold):
    if cancel_threshold is not None and real_post_delivery_cover > cancel_threshold:  # post-delivery, not now
        return "CANCEL"
    if real_cover_now >= POSTPONE_COVER_TRIGGER:  # 4
        return "POSTPONE"
    if real_cover_now >= 2:                       # ← REVIEW state exists
        return "REVIEW"
    return "PRODUCE"
```

Two real differences:
1. `scenario_service.classify` uses `real_cover_now >= cancel_threshold` (current cover today). The ABC script uses `real_post_delivery_cover > cancel_threshold` (cover *after* the PO would arrive). For a SKU with `stock_now = 0` but a huge PO landing in CW22, the script CANCELS, the service PRODUCES. Opposite recommendation.
2. `scenario_service.classify` has **3 states**: CANCEL / POSTPONE / PRODUCE. The ABC script has **4**: + REVIEW (for `2 ≤ cover < 4`). The intermediate REVIEW state, on the React page, becomes PRODUCE.

I can't tell from the code whether the React page is intentionally simpler or whether the port lost the REVIEW state. The Streamlit version (`app.py:7060-7229`) is the cited source; verify which behaviour the React page is *supposed* to mirror. If the answer is "match the offline script the user sent to the supplier", the service is wrong.

Either way, the docstring at scenario_service.py:1-13 should call this out — the file currently presents itself as a "verbatim mirror" of something it doesn't actually mirror verbatim.

### 2.10 🟡 Login lockout is in-process — survives one worker, not two

`backend/api/routers/auth.py:27-31`:
```python
_LOGIN_FAILS: dict[str, deque] = defaultdict(deque)
_LOGIN_LOCK: dict[str, float] = {}
_MAX_FAILS = 5
_WINDOW_SEC = 60
_LOCKOUT_SEC = 60
```

`deploy/polleo-demand-api.service:25` runs `--workers 2`. With 2 uvicorn workers, an attacker gets effectively **10 attempts per minute per IP** (5 per worker), not 5. Each worker has its own `_LOGIN_FAILS` dict. If you restart workers (`systemctl restart`), all counters reset.

For an internal-only intranet app with ~6 users this is fine. Worth noting in `deploy/DEPLOY.md:217-235` so whoever scales the deployment doesn't expect rate limiting to scale.

**Fix (optional).** Store in Redis or in a PG table if rate-limiting accuracy matters. For now, document the limitation.

### 2.11 🟡 `requirements.txt` mixes Streamlit and FastAPI deps

Root `requirements.txt:1-8` carries the Streamlit stack (`streamlit`, `pandas`, `numpy`, `scipy`, `scikit-learn`, `openpyxl`, `statsforecast`, `plotly`) alongside the FastAPI stack (lines 11-24: `psycopg2-binary`, `sqlalchemy`, `fastapi`, `uvicorn`, `pydantic-settings`, `python-jose`, `bcrypt`, `pyyaml`).

If the goal is to deploy *only* the FastAPI backend (`deploy/DEPLOY.md` describes exactly that), the Streamlit / statsforecast / scipy footprint is wasted disk + install time on the server (~300 MB and ~2 min). Worse: `statsforecast` pulls in numba which has had wheel-availability problems on python 3.12 in the past.

**Fix.** Split into `requirements-backend.txt` (FastAPI + auth + sqlalchemy + pandas + openpyxl + pyyaml) and `requirements-streamlit.txt` (the rest). Update `deploy/DEPLOY.md:31` to install only the backend file.

### 2.12 🟢 Two `WARNING`-style notes worth landing in code

These are not bugs, but the codebase already documents them inline — keep an eye on them:

- `backend/config.py:10-12` warns that `forecast_engine.py` hardcodes its own thresholds and doesn't read from `backend.config` or `constants.py`. The new app doesn't run the engine yet (no `RunForecast` endpoint actually invokes it server-side — it currently calls subprocess via `forecast_service.py`); when it does, the dual-source-of-truth will bite.
- `backend/services/finance_service.py:36-37` notes that "we only started running a real demand forecast in April 2026 — before that the 'plan' is a reconstructed trailing-avg proxy that doesn't reflect any actual planning decision. Bridge results before this cutoff are misleading, so the monthly bridge filters them out." That cutoff (`FORECAST_START_YEAR = 2026, FORECAST_START_MONTH = 4`) is hardcoded — when April 2027 rolls around, revisit whether you want the bridge to start using a different cutoff or roll forward.

---

## 3. Cross-layer consistency snapshot

| Concern | Status |
|---|---|
| Router endpoints ↔ Pydantic response models | Aligned: every router imports its `response_model=` from the matching `backend/schemas/*.py`. Spot-checked all routers — no missing imports. |
| Service signatures ↔ Repository signatures | Aligned. Services don't talk to SQLAlchemy directly. `permissions.py` is the one exception (loads YAML, no DB). |
| Repositories ↔ `db/schema.sql` | Spot-checked: `dim_products`, `dim_categories`, `erp_stock_current`, `forecasts`, `on_top_inputs`, `sku_planning`, `supply_master`, `incoming_supply` all referenced consistently between repo SQL and schema. No schema-drift errors found in this audit; recommend running `python -m db.init_db` against a fresh PG to confirm schema applies cleanly. |
| Frontend `types/*.ts` ↔ Backend `schemas/*.py` | Not audited row-by-row. Worth a `tsc --noEmit` + a smoke pass against a live backend to ensure response shapes match. Recommend adding `openapi-typescript` to generate the TS types from FastAPI's OpenAPI schema automatically. |
| Sidebar `section` IDs ↔ YAML keys ↔ `require_section()` calls | See §2.6 — 8 dangling YAML keys; 73 endpoints with no `require_section` gate (§2.3). |
| API route URLs (frontend `client.ts`) ↔ FastAPI router paths | Spot-checked: `/demand/sales-weekly`, `/demand/revenue`, `/demand/categories`, `/demand/forecast-accuracy` all match. Recommend an integration test that hits each endpoint once. |
| schema.sql DROPs match CREATEs | ✓ — verified by grep at `db/schema.sql:27-69` (DROPs) vs lines 75-626 (CREATEs). All 28 tables and 8 views drop in reverse-dependency order; CASCADE handles edge cases. Re-running is safe. |

---

## 4. Security audit checklist

| Item | Status | Notes |
|---|---|---|
| JWT secret protected | ⚠ | Production gate exists (`backend/config.py:48-73`); fine if `.env` is `chmod 600`. **But `.env` is in git** (§2.1). |
| Password storage | ✓ | bcrypt, 72-byte truncation noted. No dev backdoor in code. |
| Token expiry | ✓ | 7 days, stateless. Acceptable for intranet internal app. |
| Token storage in browser | ✓ | sessionStorage (cleared when tab closes). |
| CORS | ✓ | Production gate refuses to start with localhost in origins. |
| HTTPS | ✓ | nginx HSTS, TLSv1.2+, certbot-ready (`deploy/nginx.conf.example:28-33`). |
| Login rate limit | ⚠ | Per-IP, in-process. See §2.10 — `--workers 2` halves the effectiveness. |
| Role checks (admin) | ✓ | `require_role("admin")` at `backend/api/routers/admin.py:19`. |
| Role checks (sections) | ❌ | Only finance/executive/npl. See §2.3 (73 unprotected endpoints). |
| Secrets in repo | ❌ | `.env` Slack token + `deploy/smoke_test.sh` password. See §2.1, §2.2. |
| nginx security headers | ✓ | HSTS / X-Frame-Options / X-Content-Type-Options / Referrer-Policy all set. |
| systemd hardening | ✓ | `NoNewPrivileges`, `PrivateTmp`, `ProtectSystem=full`, `ProtectHome`, explicit `ReadWritePaths`. |
| Audit logging | ✗ | `audit_log` table exists in schema (line 522) but I didn't find a service that writes to it. Worth confirming intent. |
| Default credentials | ⚠ | `polleo:polleo_dev` baked into `docker-compose.yml:24` (dev only — DEPLOY.md tells you to use a strong one in prod). |

---

## 5. Logic correctness — what the code actually computes vs what the docs say

### 5.1 FA per-row + per-week-avg rule
`backend/services/demand_service.py:8-46` documents the FA formulas verbatim against `app.py:4172-4181`. Inputs spot-checked OK:

```
error      = |F - A|
fa         = max(0, 1 - |F-A|/A)         per-row 0..1, ×100 at display
fa_signed  = F / A                       1.0 = perfect
bias       = (F - A) / A
hit        = (|F-A| / max(A, 1)) <= 0.30
filter:    drop rows where A <= 0
```

The headline aggregation rule (per-week average, sum-then-divide for monthly) is implemented in `demand_service._per_week_avg_metrics` and `_agg`. The docstring (lines 19-36) flags one deliberate divergence from `app.py`: grouping by `(year, week)` instead of `week` alone — semantically more correct, only differs when the dataset straddles years (currently single-year). Documented and acceptable.

### 5.2 Supply roll-forward
`backend/services/supply_service.py:8-12`:
```
closing_stock_wN = max(0, opening_stock_wN - demand_wN + incoming_wN)
```

Verified in `get_supply_dashboard` line 129 and `get_stock_projection_data` line 242. Consistent. The `max(0, …)` floor matters: a SKU that stocks out contributes 0 to value from that week on, AND stops absorbing further demand. `scenario_service.company_eur_projection:191-202` explicitly explains this same floor was added to fix a per-SKU vs aggregate discrepancy of ~€700k by week 13 vs Stock Projection.

So the per-SKU floor is consistent between Supply and Scenario services. Good.

### 5.3 Coverage classification
See §2.4 — **three coexisting implementations**:

| Implementation | Threshold | Used by |
|---|---|---|
| `supply_service._classify` (abs weeks) | `<2 / <4 / >13` | Dashboard, Stock Projection |
| `supply_service.get_alerts` (LT-aware) | `<LT Order Now / <2×LT Order Soon / >13 + incoming Pull in` | Reorder Alerts |
| `coverage_classifier.classify_coverage` (LT-multiples) | `≤LT / ≤1.5×LT / ≤2×LT / >2×LT + MISSING_LT + NO_DEMAND` | Executive, Finance |

The `_compute_suggestions` private helper in supply_service drives Alerts and Order Suggestions — its math is in the same file but doesn't share `coverage_classifier`. That's three pages saying three different things about "stock urgency". For an internal user this is bewildering. Pick one and migrate.

### 5.4 Order suggestion
`backend/services/supply_service.py:26-29`:
```
target_stock  = 2 × LT × avg_weekly_demand
gap           = target_stock - current_stock - incoming_in_LT
suggested_qty = ceil(gap / MOQ) × MOQ      when gap > 0, else 0
```

Verified in `_compute_suggestions`. Matches docstring.

### 5.5 KAM exclusion math
Docstring at `backend/services/supply_service.py:31-34` says exclusion subtracts on-top quantities for excluded (submitter, buyer) pairs from demand per (sku, year, week). The implementation at `supply_service.py:213-219` queries `repo.get_on_top_quantities` with the lists and subtracts in line 240: `demand = max(0.0, demand_raw - ex)`. Verified.

### 5.6 Schema vs ingest paths
`db/schema.sql:150-158` defines `lookup_channel_map` with `doc_type → channel` and notes the channel-map function in `update_sales.py:36-42` (Streamlit world) **does not** read this table yet, just hard-codes. In the new app, repositories must use `lookup_channel_map` for the mapping. Spot-checked `demand_repo.py` joins against `channel_map_id` in `erp_transactions` — consistent with the schema.

The materialized view `v_sales_weekly` (line 544) is the canonical aggregate for sales by `(product_id, year, week, channel)`. Wherever services compute "weekly sales", they should be hitting this view; not auditing every site here.

---

## 6. Dead / scratch / superseded inside scope

**Move out of `db/` (already untracked, just clean up):**
- All `db/_check_*.py`, `db/_inspect_*.py`, `db/_debug_*.py`, `db/_verify_*.py`, `db/_compare_eur.py`, `db/_bridge_*.py`, `db/_create_*.py`, `db/_find_mci.py`, `db/_mci_full.py`, `db/_move_mci.py`, `db/_apply_npl.py`, `db/_explain_prices.py`, `db/_reload_forecast_tables.py`, `db/_sales_coverage.py`, `db/_smoke_npl.py`, `db/_who_inflated_cw21.py` (~30 files). Move to `db/_audits/` and exclude in `.gitignore`.

**Inside `data/` (touched by the new app's data ingestion, mostly out of scope but flagged):**
- `data/_backend_*.log`, `data/_streamlit_*.txt`, `data/_vite_*.txt`, `data/_backend_test*.log`, `data/_cfo_audit_v2.log`, `data/_audit_step0.txt` — all uncommitted log noise that should be in `.gitignore`. Some already are.

**Files in scope that look dead:**
- `backend/services/forecast_service.py` (323 lines) — I didn't trace whether it's called. The router uses it (`demand.py:46` imports `ForecastService`), so it's alive.
- `db/migrate_dimensions.py` vs `db/migrate_remaining.py` vs `db/migrate_remaining_gaps.py` — three migration helpers. Worth a 1-line comment in each saying when to use which (or consolidating).

---

## 7. What breaks next month (or when CW20 rolls past)

The new app has **no hardcoded `CURRENT_YEAR/CURRENT_WEEK`** that I found. Time semantics are driven by:
- `backend/services/time_utils.py` (45 lines, untracked) for ISO-week ↔ month conversions.
- Repos use `v_sales_weekly_full` to find the "latest week" (`supply_service.py:54-57` docstring confirms this).
- `forecast_service.py` invokes the engine, which reads `sales_clean.csv` and infers the current week itself.

The exceptions are:
1. `backend/services/finance_service.py:36-37` — `FORECAST_START_YEAR=2026, FORECAST_START_MONTH=4` (filters bridge analysis to post-April-2026 data). Hardcoded; revisit when 2027 rolls in.
2. `backend/services/finance_service.py:30-31` — `CONTEST_START_CW=27, CONTEST_END_CW=31`. This is the July contest window. Will silently produce stale data after CW31 unless updated annually. Add a `TODO` comment with a renewal date, or move to a `config` table.

The DB-fallback layer (`db/csv_fallback.py`) is OK because `is_db_available()` is dynamic; CSVs are date-stamped by file mtime, not by parsing.

---

## 8. Action list — recommended order

Tier 1 (must do before going live to real users):

1. **Rotate the Slack token** (Slack admin), then `git rm --cached .env`, commit, push. (§2.1)
2. **Remove the default password from smoke_test.sh** or make it required (`${SMOKE_PW:?}`). Rotate Lovro's password if `Lovro2575` was real. (§2.2)
3. **Wire `require_section()` into demand / supply / promo routers** for all 73 endpoints. Pattern is in `finance.py:27-32`. (§2.3)
4. **Decide on one coverage classifier**. Either migrate `supply_service` to use `classify_coverage`, or update the docstring at `coverage_classifier.py:3-8`. (§2.4)

Tier 2 (do during pre-deploy cleanup):

5. Fix the two false docstrings (`auth_service.change_password`, `admin.UserCreate.password`). (§2.5)
6. Remove or wire up the 8 dangling permission keys in `role_permissions.yaml`. (§2.6)
7. Update the "single-user Streamlit" comment on `db/connection.py:55-59`. (§2.7)
8. Move `db/_*.py` scratch scripts to `db/_audits/` and gitignore. (§2.8)
9. Reconcile `scenario_service.classify` with the spec it claims to mirror — or update the docstring. (§2.9)
10. Split `requirements.txt` into backend-only + streamlit-only. (§2.11)

Tier 3 (nice to have):

11. Note login-rate-limit per-worker behaviour in `DEPLOY.md`. (§2.10)
12. Add `openapi-typescript` to keep frontend types in sync with backend schemas.
13. Add a "first login" health check — confirm a newly created user (without `password` field) cannot log in until admin resets, document this in the UI.
14. Confirm whether `audit_log` table is intended to be written by services or only by triggers — currently nothing writes to it.
15. Add a date check / renewal TODO to `CONTEST_*` constants and `FORECAST_START_*` constants in `finance_service.py`.

---

## 9. Out of scope (per user direction)

Not audited here, deliberately:
- `app.py`, `forecast_engine.py`, `forecast_engine_backup.py`, `forecast_db.py`, `run_backtest.py`, `update_sales.py`, `recalc_uplift_erp.py`, `build_*.py`, `analyze_*.py`, `equipment_promo_analysis.py`, `promo_calc_w22_w26*.py`, `compute_xyz.py`, `slack_agent.py`, `validate_promo_model.py`, `generate_docs.py`, `cm_action_history_build.py`, `build_isporucivost_*.py`, `build_scm_action_plan.py`, `build_cap_compliance.py`, `analyze_ccc_stock.py` — Streamlit-era pipeline.
- `_abc_*` scripts in root and `ABC analiza PO/` folder — tactical, gitignored.
- `Demo/`, `PromoTool/`, `PromoCalendar/` — independent Streamlit apps.
- Docs in `docs/` and root-level `CLAUDE.md` / `DEMAND_PLANNING_BRIEF.md` / `PROJECT_OVERVIEW.md` / `PROJECT_SNAPSHOT.md` / `VERIFICATION_INVENTORY.md` / `Tjedni_tok_Polleo_Demand.docx` / `sop_cycle_flowchart.svg` — reference / catalogued only.

These are catalogued in `PROJECT_MAP.md` for context but their internal correctness is out of scope for this audit.

---

*End of audit. See `PROJECT_MAP.md` for the full catalog.*