# Rolling Forecast — Platform Primitive Spike Notes

- **Date:** 2026-07-07 (platform findings) · **Status update 2026-08-05:** design consequences revised for PRD v0.4
- **Purpose:** pre-development feasibility verification of the four platform primitives the PRD assumes. Four parallel investigations over the upstream NexusCRM code, synced docs, and existing L2 pack precedents.
- **Net verdict: no red lights, no upstream issues required.**
- **v0.4 supersessions:** the platform findings (sections 1–4) stand as verified; the *design consequences* drawn from them have been partially superseded by PRD v0.4 — Starting ARR / ARRSnapshot are retired, the ops-CSV revenue path is replaced by the NetSuite voucher integration (idempotency now rides the voucher's unique `NetSuite_Source_Key__c`, so no `ImportKey__c` fields are added), and the summary object's columns are Q1–Q4 × {ARR, Revenue} × {Target, Forecast, Gap}. The Bulk-API/upsert findings in section 4 remain directly applicable to the Targets CSV load.

## 1. Scheduled jobs — SUPPORTED with constraints

- First-class scheduled-flow runtime: 1-minute scheduler tick, `TriggerType="Scheduled"`, durable run reservation (idempotent), per-flow exponential backoff (`backend/internal/features/automation/scheduler_service.go`).
- **Frequencies: Once / Daily / Weekly only — no Monthly.** Emulate monthly with a Daily flow gated on day-of-month == 1 (idiomatic; the flow no-ops on other days).
- **Per-run caps:** 1,000 matched source records, 50,000 scanned (`flow_executor.go:151-153`). The pre-fill flow must shard by Region/Pod so each run stays ≤ 1,000 matched; verify actual RevenueActual row counts after the first import.
- **Delivery: pack `.flow.json` only.** The Tooling API blocks `TriggerType`/`ProcessType`/version fields as pack-layer shadow authority (`metadata_objects.go:592-631`). Three Daily scheduled flows already ship in this pack (e.g. `Account_status_Flow.flow.json`) — follow that precedent.
- T-2 pre-fill timing: record-triggered scheduled path with `OffsetNumber:-2, OffsetUnit:Days` on ForecastCycle, or a durable Wait element (both present). Reminders: flow-native `sendEmail` action.

## 2. Row-level permissions — SUPPORTED except one composition to prototype

- **Two independent layers:** every decision = object-level (profile/permission-set or derived `AccessPolicy`) ∧ record-level (OWD / owner / hierarchy / sharing rules / manual shares). They take different inputs — `recordFieldEqualsCurrentUser` exists only in the AccessPolicy layer and is not compiled into list-query RLS.
- ✅ `LockRule` (formula-driven, read-only for everyone incl. admins, pack-defined, drift-verified): expresses `value_type == 'Actual'` read-only and worksheet `Status == 'Locked'` freeze directly. Because the rule is a live formula over the driving field, the PRD's privileged unlock is consistent with it: Sales Ops / System Administrator flip the worksheet Status (a permission-gated field on an unlocked object), the formula evaluates false, and the records reopen — no one ever edits a record while its LockRule holds. Approval-lock primitive also available for submit-locking.
- ✅ `recordFieldEqualsCurrentUser(field="filler")` in `accessPolicy.objectEdit`: per-record write gate on an arbitrary User field; read and edit policies separate.
- ✅ Region isolation: `sharingModel = ControlledByParent` on ForecastLine with the worksheet as sharing parent — cross-Region invisibility follows parent RLS.
- ⚠️ **To prototype before committing (first dev task):** the full composition "Region-wide read + filler-only edit + Leader edit" has no shipped production precedent (`recordFieldEqualsCurrentUser` is tested but unused by standard objects). Prototype in a local environment. **Fallback (pre-agreed):** Region-level Edit via one criteria sharing rule per Region + a validation rule that permits a mutation when `filler == $User.Id` **or the editor holds the Region/Sub-unit leader or Sales Ops permission** (leaders must be able to fill on behalf — an unconditional filler-only rule would reject legitimate leader edits), **with the `filler` field itself write-protected so only leaders/Sales Ops can reassign it** (field-level security or a `WriteProtectionPolicy` — otherwise a salesperson could set filler to themselves and edit a colleague's row) + field-history audit; keep the `LockRule` pieces regardless.
- Note: sharing-rule records, manual shares, and profile assignments are **runtime artifacts**, not pack metadata; `sharingModel`, `accessPolicy`, `lockRule` are pack-definable.

## 3. Reporting — PARTIAL; precompute-then-display

- Report engine reality: single-level GROUP BY with one aggregate server-side; multi-level grouping/subtotals are client-side rendering over ≤ 2,000 fetched rows; **matrix format declared but not implemented; no formula columns; no conditional formatting; no cell-level drill-down** (`report_service.go`, `reportUtils.ts`).
- Cross-object Custom Report Types are metadata-driven and pack-deliverable (`report_type` component kind; precedent: `report_types/standard_report_types.json`). Saved reports and dashboards are **runtime data**, not pack components — create the Region Summary report via deploy scripts/API post-install.
- **Design consequence (as revised for v0.4):** add a `Region_Forecast_Summary__c` object — one row per Sub-unit (plus a Region rollup row) with explicit `Q1..Q4 × {ARR, Revenue} × {Target, Forecast, Gap}` columns and a YT total, written by the calculation flow at save/lock time (no Starting ARR — retired in v0.4). All pivoting, quarter bucketing, GAP math, and ARR math happen on the write side; reports only display. Negative-red formatting deferred to the custom view.

## 4. Bulk import / upsert — SUPPORTED

- **Bulk API 2.0** (`POST /jobs/ingest` → CSV batch upload → close → poll): async, CSV-native, supports `upsert`, 100 MB/batch, per-record partial success with row-level errors (`bulk_handler.go`, `bulk_processor.go`).
- Upsert is keyed on **one** external-id field. Under v0.4 the revenue path needs no added key — the NetSuite voucher line's unique `NetSuite_Source_Key__c` already carries idempotency (RevenueActual/ARRSnapshot and their ImportKey fields are retired). The single-key finding still applies to the Targets CSV load, whose composite key (FY × Region × Sub-unit × Quarter × Type) is encoded in a text external-id field or resolved by the loader's read-then-write upsert.
- Built-in CSV import wizard is **insert-only** (no idempotency) — do not use.
- UI-read-only + script-writable: standard ObjectPermissions — end-user profiles Read-only; a dedicated **integration user** profile gets Create+Edit; the import script authenticates as it via OAuth2 password grant (established downstream pattern).
- Script responsibilities (platform does the rest natively): in-file duplicate-key pre-check, consolidated error report, post-import reconciliation summary (row count + SUM(revenue_usd) read-back).

## Resulting design deltas (as revised for PRD v0.4)

1. +1 object: `Region_Forecast_Summary__c` (per-Sub-unit summary row, write-side computed; columns Q1–Q4 × {ARR, Revenue} × {Target, Forecast, Gap} + YT total).
2. Idempotent ingestion rides the NetSuite voucher line's unique `NetSuite_Source_Key__c`; no ImportKey fields (the RevenueActual / ARRSnapshot objects of the v0.3 draft are retired).
3. Pre-fill scheduled flow sharded by Region/Sub-unit (≤ 1,000 matched per run); monthly cadence = Daily flow + day gate.
4. Region Summary saved report moves from pack metadata to post-install deploy scripting.
5. First development task: permission-composition prototype (filler edit gate) in a local environment; fallback pre-agreed.