In B2B e-commerce platforms, managing customer account budgets is critical for controlling organizational spend across departments and Ship-To locations. However, out of the box, Optimizely Configured Commerce does not support bulk budget uploads via spreadsheets.
Prior to this custom implementation, administrators and customer managers had to navigate to the Budget Management page and manually enter budget amounts one by one across fiscal periods, customer accounts, and ship-to locations. For enterprise accounts with dozens of locations and 12-month fiscal calendars, this manual data entry was tedious, error-prone, and inefficient.
To eliminate this operational friction, I engineered an asynchronous bulk budget ingestion customization. This solution allows users to upload a single spreadsheet containing budget allocations across multiple customers and ship-tos in a single API call, leveraging an event-driven background post-processor job to execute the updates cleanly.
This post breaks down the end-to-end flow, reviews the backend engineering (Web API, Handlers, Sylvan Excel Generation, and Integration Post-Processors), and demonstrates how to implement the front end using Optimizely's Spire Architecture (React/Redux).
1. High-Level Architecture Overview
Out of the box, Optimizely Configured Commerce requires users to manually enter budget allocations row-by-row through the budget management page. To replace this manual process with a single-action spreadsheet upload while avoiding HTTP timeouts and database locking during large updates, the solution separates the workflow into two distinct phases: Staging & Queueing and Asynchronous Batch Processing.
[ Front-End UI ] ---> (PATCH /api/v1/custombudget) ---> [ CustomBudgetsV1Controller ]
|
v
[ CustomBudgetService & Handler ]
- Serializes (Sylvan.Data.Excel)
- Persists CustomerBudget.xlsx
- Schedules Integration Job
|
v
[ Background Integration Engine ]
- JobPostprocessorCustomerBudget
- Mocks SiteContext per Bill-To
- Calls IBudgetService.UpdateBudget()- Frontend Request: The process begins in the Spire Front-End UI, where the user uploads an Excel or CSV spreadsheet. The Upload Widget parses the spreadsheet and sends the budget data to the backend as a single API payload through PATCH /api/v1/custombudget.
- Staging & Job Scheduling: The CustomBudgetsV1Controller receives the request and delegates processing to the CustomBudgetService & Handler. This component stages the uploaded data by constructing a System.Data.DataTable named CustomerBudget, serializing the data into an Excel file using Sylvan.Data.Excel, and persisting CustomerBudget.xlsx in the backend userfiles folder. Instead of processing all budget updates within the HTTP request, the service schedules the "Budget: Import Customers" batch integration job through IIntegrationJobSchedulingService.
- Background Execution: The Background Integration Engine subsequently executes the scheduled job asynchronously. Its JobPostprocessorCustomerBudget implementation reads the staged Excel data from the userfiles folder, dynamically establishes the appropriate SiteContext for each Bill-To Customer, merges the uploaded values with the customer's existing budget lines while preserving non-sentinel period values, and ultimately applies the changes through IBudgetService.UpdateBudget().
This architecture keeps the initial API request lightweight and moves the potentially long-running budget update operation into the background integration pipeline. As a result, large spreadsheet uploads can be processed asynchronously without requiring the front-end request to remain open for the duration of the database update.
2. Deep Dive: Backend Engineering Breakdown
A. The Custom Web API Endpoint (CustomBudgetsV1Controller)
The custom REST controller exposes a PATCH endpoint at /api/v1/custombudget. It accepts the structured array of customer budget lines parsed from the uploaded spreadsheet and delegates execution through Optimizely's ExecuteAsync pipeline.
[RoutePrefix("api/v1/custombudget")]
public class CustomBudgetsV1Controller : BaseApiController
{
private readonly ICustomBudgetService customBudgetService;
private readonly IPatchCustomBudgetMapper patchCustomBudgetMapper;
public CustomBudgetsV1Controller(
ICookieManager cookieManager,
IUnitOfWorkFactory unitOfWorkFactory,
ICustomBudgetService customBudgetService,
IPatchCustomBudgetMapper patchCustomBudgetMapper) : base(cookieManager)
{
this.customBudgetService = customBudgetService;
this.patchCustomBudgetMapper = patchCustomBudgetMapper;
}
/// <summary>Updates the budget for specified fiscal year.</summary>
[Route("", Name = "UploadBudgets")]
[ResponseType(typeof(CustomBudgetModel))]
[HttpPatch]
public Task<IHttpActionResult> PatchAsync([FromBody] CustomBudgetModel parameter)
{
return this.ExecuteAsync<
IPatchCustomBudgetMapper,
CustomBudgetModel,
CustomUpdateBudgetParameter,
UpdateBudgetResult,
CustomBudgetResponseModel
>(patchCustomBudgetMapper, customBudgetService.UpdateBudgets, parameter);
}
}B. Handler Execution & Excel Generation (UpdateBudgets)
Inside the Handler, the payload is converted into an in-memory DataTable with 12 period columns (PeriodBudget_1 through PeriodBudget_12).
Key engineering highlights in this step:
- High-Performance Excel Streaming (Sylvan.Data.Excel): Rather than using heavy dependencies like EPPlus or Interop, the implementation uses Sylvan.Data.Excel to write the DataTableReader directly into an Excel XML stream with minimal memory allocations.
- Abstract File Storage (IUserFilesStorageProvider): The generated file is saved to the backend userfiles directory (/budgetmanagement/CustomerBudget.xlsx). This abstracts physical disk access, keeping the implementation compatible with local storage, AWS S3, or Azure Blob Storage.
- Decoupled Job Triggering (IIntegrationJobSchedulingService): The handler enqueues the batch job "Budget: Import Customers" and immediately returns a successful result code to the UI.
public override int Order => 500;
public override UpdateBudgetResult Execute(
IUnitOfWork unitOfWork,
CustomUpdateBudgetParameter parameter,
UpdateBudgetResult result)
{
var connection = unitOfWork.GetRepository<integrationconnection>().GetTableAsNoTracking().FirstOrDefault(x => x.Name == "Internal - Budget Connection");
if (connection != null)
{
var budgetTable = new DataTable("CustomerBudget");
budgetTable.Columns.Add("FiscalYear", typeof(int));
budgetTable.Columns.Add("CustomerNumber", typeof(string));
budgetTable.Columns.Add("ShipToNumber", typeof(string));
for (int i = 0; i < 12; i++)
{
budgetTable.Columns.Add($"PeriodBudget_{i + 1}", typeof(decimal));
}
if (parameter.Budgets != null)
{
foreach (var budget in parameter.Budgets)
{
var row = budgetTable.NewRow();
row["FiscalYear"] = budget.FiscalYear;
row["CustomerNumber"] = budget.CustomerNumber ?? string.Empty;
row["ShipToNumber"] = budget.ShipToNumber ?? string.Empty;
if (budget.Budgets != null && budget.Budgets.Length > 0)
{
for (int i = 0; i < budget.Budgets.Length && i < 12; i++)
{
row[$"PeriodBudget_{i + 1}"] = budget.Budgets[i];
}
for (int i = budget.Budgets.Length; i < 12; i++)
{
row[$"PeriodBudget_{i + 1}"] = DBNull.Value;
}
}
else
{
for (int i = 0; i < 12; i++)
{
row[$"PeriodBudget_{i + 1}"] = DBNull.Value;
}
}
budgetTable.Rows.Add(row);
}
}
// =========================
// Write Excel
// =========================
using (var stream = new MemoryStream())
{
using (var writer = ExcelDataWriter.Create(
stream,
ExcelWorkbookType.ExcelXml,
new ExcelDataWriterOptions { TruncateStrings = true }
))
{
writer.Write(budgetTable.CreateDataReader(), budgetTable.TableName);
}
stream.Position = 0;
var destinationDirectory = !string.IsNullOrEmpty(connection.Url) ? connection.Url : "budgetmanagement/";
var destinationFilePath = $"{destinationDirectory}CustomerBudget.xlsx";
var filePath = destinationFilePath.Replace("\\", "/");
this.userFileStorageProvider.SaveStream(filePath, stream, true);
var jobName = "Budget: Import Customers";
this.integrationJobSchedulingService.ScheduleBatchIntegrationJob(jobName);
}
}
result.ResultCode = Insite.Core.Services.ResultCode.Success;
return result;
}C. Asynchronous Processing (JobPostprocessorCustomerBudget)
When the background integration job executes, the post-processor JobPostprocessorCustomerBudget processes the staged spreadsheet dataset:
- Context Mocking (ISiteContext): Optimizely's core services depend heavily on SiteContext.Current (such as active customer/bill-to context). The post-processor instantiates a custom implementation (BudgetCalendarSiteContext) and sets SiteContext.SetSiteContext(...) on each iteration to evaluate permissions and rules per customer account.
- Sentinel Value Handling (-1): A value of -1 in the spreadsheet denotes "do not update this period". The PopulateBudgetPeriods method ignores -1 entries, allowing selective budget overrides without clearing pre-existing period values.
- Budget Merging: Existing budgets are retrieved via IBudgetService.GetBudget(), updated with non-sentinel changes in MergeBudgets(), and written back via IBudgetService.UpdateBudget().
public virtual void Execute(DataSet dataSet, CancellationToken cancellationToken)
{
var stopwatch = Stopwatch.StartNew();
JobLogger.Info("Starting Customer Budget assignment...");
try
{
AssignCustomerBudget(dataSet);
}
catch (Exception ex)
{
JobLogger.Error($"Error while assigning Customer Budget: {ex}");
}
finally
{
_unitOfWork.Clear();
stopwatch.Stop();
IntegrationJob.RecordsAdded = RecordsAdded;
IntegrationJob.RecordsModified = RecordsModified;
IntegrationJob.RecordsDeleted = RecordsDeleted;
JobLogger.Info($"Customer Budget assignment completed in {stopwatch.ElapsedMilliseconds} ms.");
}
}
private void AssignCustomerBudget(DataSet dataSet)
{
var budgetCalendars = GetBudgetCalendarInfo(dataSet);
foreach (var bc in budgetCalendars)
{
var siteContext = new BudgetCalendarSiteContext()
{
BillTo = _unitOfWork.GetRepository<customer>().GetTableAsNoTracking()
.FirstOrDefault(c => c.CustomerNumber.Equals(bc.CustomerNumber, StringComparison.OrdinalIgnoreCase)),
};
SiteContext.SetSiteContext(siteContext);
var existingBudget = _budgetService.GetBudget(new GetBudgetParameter(bc.FiscalYear)
{
FiscalYear = bc.FiscalYear,
ShipToId = bc.ShipToId.HasValue ? bc.ShipToId.Value : Guid.Empty,
UserProfileId = bc.UserProfileId.HasValue ? bc.UserProfileId.Value : Guid.Empty,
});
var existingBudgets = existingBudget.BudgetLineCollectionDto.Select(x => x.CurrentFiscalYearBudget) ?? new decimal[12];
var mergedBudgets = MergeBudgets(
existingBudgets.ToArray(),
bc.PeriodUpdates
);
var parameter = new UpdateBudgetParameter(bc.FiscalYear)
{
Budgets = mergedBudgets,
UserProfileId = bc.UserProfileId ?? Guid.Empty,
ShipToId = bc.ShipToId ?? Guid.Empty
};
var result = _budgetService.UpdateBudget(parameter);
if (result.ResultCode == Insite.Core.Services.ResultCode.Success)
{
RecordsModified++;
JobLogger.Info($"Updated Customer Budget for Fiscal Year {bc.FiscalYear}.");
}
else
{
JobLogger.Error($"Failed to update Customer Budget Calendar for Fiscal Year {bc.FiscalYear}: {string.Join(", ", result.Messages)}");
}
}
}
private decimal[] MergeBudgets(
decimal[] existing,
Dictionary<int, decimal> updates)
{
var merged = existing.ToArray();
foreach (var kvp in updates)
{
if (kvp.Key >= 0 && kvp.Key < merged.Length)
{
merged[kvp.Key] = kvp.Value;
}
}
return merged;
}
private List<customerbudgetinfo> GetBudgetCalendarInfo(DataSet dataSet)
{
var table = dataSet?.Tables.Cast<datatable>().FirstOrDefault();
if (table == null)
{
JobLogger.Error("Dataset does not contain a valid table.");
return new List<customerbudgetinfo>();
}
return table.Rows.Cast<datarow>()
.Select(row =>
{
int.TryParse(row["FiscalYear"]?.ToString(), out var fiscalYear);
DateTimeOffset? fiscalYearEndDate = null;
if (table.Columns.Contains("FiscalYearEndDate") &&
DateTimeOffset.TryParse(row["FiscalYearEndDate"]?.ToString(), out var parsedDate))
{
fiscalYearEndDate = parsedDate;
}
var customerNumber = row.Table.Columns.Contains("CustomerNumber") ? row["CustomerNumber"]?.ToString() : string.Empty;
var userName = row.Table.Columns.Contains("Username") ? row["Username"]?.ToString() : string.Empty;
var shipToCustomerNumber = row.Table.Columns.Contains("ShipToNumber") ? row["ShipToNumber"]?.ToString() : string.Empty;
return new CustomerBudgetInfo
{
FiscalYear = fiscalYear,
PeriodUpdates = PopulateBudgetPeriods(row),
CustomerNumber = customerNumber,
UserProfileId = GetUserProfileId(userName),
ShipToId = GetShipToCustomerId(shipToCustomerNumber, customerNumber)
};
})
.ToList();
}
private Guid? GetUserProfileId(string userName)
{
var userProfileId = _unitOfWork.GetRepository<userprofile>().GetTableAsNoTracking()
.FirstOrDefault(up => up.UserName.Equals(userName, StringComparison.OrdinalIgnoreCase))?.Id;
return userProfileId;
}
private Guid? GetShipToCustomerId(string shipToCustomer, string customerNumber)
{
if(string.IsNullOrWhiteSpace(shipToCustomer))
return null;
var shipToCustomerId = _unitOfWork.GetRepository<customer>().GetTableAsNoTracking()
.FirstOrDefault(c => c.CustomerNumber.Equals(customerNumber, StringComparison.OrdinalIgnoreCase) && c.CustomerSequence.Equals(shipToCustomer, StringComparison.OrdinalIgnoreCase))?.Id;
return shipToCustomerId;
}
private Dictionary<int, decimal> PopulateBudgetPeriods(DataRow row)
{
var result = new Dictionary<int, decimal>();
foreach (DataColumn col in row.Table.Columns)
{
if (!col.ColumnName.StartsWith("PeriodBudget_", StringComparison.OrdinalIgnoreCase))
continue;
var suffix = col.ColumnName.Replace("PeriodBudget_", "");
if (!int.TryParse(suffix, out var periodNumber))
continue;
if (!decimal.TryParse(row[col]?.ToString(), out var amount))
continue;
// ❗ Skip sentinel value (-1 means "do not update")
if (amount == -1)
continue;
// PeriodBudget_1 => index 0
result[periodNumber - 1] = amount;
}
return result;
}
3. Front-End Evolution: Classic (AngularJS) vs. Spire (React / TypeScript)
While legacy implementations relied on AngularJS services and controllers extending BaseUploadController, Optimizely's modern Spire framework uses React, TypeScript, and Redux architecture.
In Spire, we structure the upload mechanism across three key layers:
- API Client Request Function: Encapsulates the HTTP request using Spire's built-in REST client infrastructure.
- Redux Async Thunk: Manages asynchronous execution and dispatch state.
- React Widget Component: Handles file parsing (using libraries like xlsx) and user interaction via Spire Mobius UI components.
Step 1: Spire API Service Call (CustomBudgetService.ts)
import isApiError from "@insite/client-framework/Common/isApiError";
import {
ApiParameter,
patch,
ServiceResult,
} from "@insite/client-framework/Services/ApiService";
import { BaseModel } from "@insite/client-framework/Types/ApiModels";
export interface CustomBudgetUploadItem {
customerNumber: string;
fiscalYear: number;
shipToNumber: string;
budgets: number[];
}
export interface CustomBudgetUploadRequest {
budgets: CustomBudgetUploadItem[];
}
export interface CustomBudgetResponseModel extends BaseModel {
success: boolean;
}
export interface UploadCustomBudgetsApiParameter extends ApiParameter {
budgets: CustomBudgetUploadItem[];
}
const customBudgetUrl = "/api/v1/custombudget";
export async function uploadCustomBudgets(
parameter: UploadCustomBudgetsApiParameter,
): Promise<ServiceResult<CustomBudgetResponseModel>> {
try {
const response = await patch<CustomBudgetResponseModel>(customBudgetUrl, parameter as any);
return {
successful: true,
result: response,
};
} catch (error) {
if (isApiError(error) && (error.status === 400 || error.status === 403 || error.status === 404)) {
return {
successful: false,
errorMessage: error.errorJson?.message || error.errorMessage || "An error occurred uploading budgets.",
};
}
throw error;
}
}Step 2: Spire Redux Async Action (UploadBudgetsThunk.ts)
import { createAsyncThunk } from "@insite/client-framework/Redux/Thunk";
import { uploadCustomBudgets, CustomBudgetUploadRequest } from "./CustomBudgetService";
export const uploadBudgetsThunk = (requestData: CustomBudgetUploadRequest) =>
createAsyncThunk(async (dispatch, getState) => {
try {
const response = await uploadCustomBudgets(requestData as any);
if (response.successful) {
// Dispatch notifications or refresh store state as needed
}
return response;
} catch (error) {
throw error;
}
});Step 3: Spire React Upload Component (BudgetUploadWidget.tsx)
import React, { useState } from "react";
import { useDispatch } from "react-redux";
import FileUpload from "@insite/mobius/FileUpload";
import Typography from "@insite/mobius/Typography";
import LoadingSpinner from "@insite/mobius/LoadingSpinner";
import { uploadBudgetsThunk } from "./UploadBudgetsThunk";
import { CustomBudgetUploadItem } from "./CustomBudgetService";
import * as XLSX from "xlsx";
export const BudgetUploadWidget: React.FC = () => {
const dispatch = useDispatch();
const [isUploading, setIsUploading] = useState(false);
const [statusMessage, setStatusMessage] = useState<string | null>(null);
const handleFileChange = (event: React.ChangeEvent<HTMLInputElement>) => {
const file = event.target.files?.[0];
if (!file) return;
setIsUploading(true);
setStatusMessage(null);
const reader = new FileReader();
reader.onload = (e) => {
try {
const data = new Uint8Array(e.target?.result as ArrayBuffer);
const workbook = XLSX.read(data, { type: "array" });
const firstSheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[firstSheetName];
const rows: any[][] = XLSX.utils.sheet_to_json(worksheet, { header: 1 });
const items: CustomBudgetUploadItem[] = [];
for (let i = 1; i < rows.length; i++) {
const row = rows[i];
if (!row || row.length === 0) continue;
const budgets: number[] = [];
for (let b = 1; b <= 12; b++) {
const val = Number(row[2 + b]);
budgets.push(Number.isNaN(val) ? -1 : val); // -1 sentinel for unchanged period
}
items.push({
fiscalYear: Number(row[0]),
customerNumber: String(row[1] || ""),
shipToNumber: String(row[2] || "0"),
budgets,
});
}
dispatch(
uploadBudgetsThunk({ budgets: items })
).then(() => {
setStatusMessage("Budget file successfully submitted for background processing.");
}).catch(() => {
setStatusMessage("An error occurred during upload. Please check your file format.");
}).finally(() => {
setIsUploading(false);
});
} catch (err) {
setStatusMessage("Failed to parse spreadsheet file.");
setIsUploading(false);
}
};
reader.readAsArrayBuffer(file);
};
return (
<div>
<Typography variant="h3">Customer Budget Bulk Upload</Typography>
<FileUpload
accept=".xlsx, .xls, .csv"
onChange={handleFileChange}
disabled={isUploading}
/>
{isUploading && <LoadingSpinner />}
{statusMessage && <Typography variant="p">{statusMessage}</Typography>}
</div>
);
};
export default BudgetUploadWidget;Extending enterprise e-commerce platforms like Optimizely Configured Commerce often requires bridging the gap between out-of-the-box UI limitations and complex B2B operational demands. By replacing manual, single-item budget entries with an asynchronous bulk ingestion pipeline, this architecture delivers significant business and technical advantages:
- Bridging Platform Gaps: Native Optimizely Configured Commerce only allows manual, single-item budget creation through the UI. Designing a custom spreadsheet ingestion endpoint transforms a repetitive administrative task into a single-click action.
- Decoupling Heavy Workloads: Never process large multi-row updates synchronously inside HTTP request threads. Staging the uploaded data to the backend user files directory and delegating updating logic to an IJobPostprocessor keeps API responses instant and immune to gateway timeouts.
- Memory-Efficient Data Formatting: Utilizing low-allocation stream writers like Sylvan.Data.Excel avoids memory pressure when preparing files for background processing.