Express and vanilla JavaScript application that fetches BrightSites orders and exports CSV reports or a linked Excel workbook.
-
Copy
.env.exampleto.envand configure eitherBRIGHTSITES_STORESor the legacyBRIGHTSITES_SUBDOMAINandBRIGHTSITES_API_TOKENvariables. -
Install dependencies and start the server:
npm install npm start
-
Open http://127.0.0.1:3000, select a store, table type and date range, choose any desired status/format options, then run and download the report.
Locally the server listens on 127.0.0.1 (override with HOST). On Heroku (or NODE_ENV=production) it listens on 0.0.0.0:$PORT.
The current password login remains the default (AUTH_MODE=password) until company email sign-in is configured. Production refuses to start without the required authentication configuration and redirects HTTP to HTTPS.
Set AUTH_MODE=email to replace the shared password with emailed one-time codes. Only verified addresses on the exact centricitynow.com domain are accepted; subdomains and lookalike domains are rejected. There is no password fallback in email mode, and existing shared-password cookies do not grant access.
The app stays at https://ftg-billing-e2fcc1ef48a1.herokuapp.com/login. No custom website domain is needed. Supabase manages email-code verification and users; Resend provides SMTP delivery.
-
Create a separate Supabase project in the Centricity organization on the Free plan. Account column defaults also require the table described below.
-
Create a Resend account and verify a sender domain you control using the DNS records Resend provides. This is email-sender verification only, not a website DNS change. Choose a verified From address, and create a sending-only Resend API key.
-
In Supabase Authentication → Email / SMTP settings, enable custom SMTP: host
smtp.resend.com, port465, usernameresend, and the Resend API key as the SMTP password. Set the sender email to your verified From address. Do not rely on Supabase's default email delivery: it is restricted and unsuitable for general production sign-ins. -
Enable the Email provider and allow new user sign-ups. In Email Templates → Magic Link, include the literal
{{ .Token }}in the email body so users receive a code rather than a link. Set email OTP expiry to 600 seconds, retain the resend cooldown, and configure Supabase email/verification rate limits for expected usage. Configure CAPTCHA or an Auth hook if needed to protect the public Supabase OTP endpoint from abuse; the app's rate limits apply only to requests through this server. -
Set Supabase's Site URL to
https://ftg-billing-e2fcc1ef48a1.herokuapp.com. Obtain the project URL and publishable key (a legacyanonkey also works). Authentication uses the publishable key; the separate server-only secret below is used only for account preferences. -
Configure the app through secure Heroku settings (or an untracked local
.env):AUTH_MODE=email SUPABASE_URL=https://your-project.supabase.co SUPABASE_PUBLISHABLE_KEY=your-project-publishable-key SESSION_SECRET=a-random-secret-at-least-32-characters SESSION_TTL_MS=28800000Generate
SESSION_SECRETlocally withopenssl rand -hex 32. Keep credentials out of source control and chat. Once configured, restart/deploy and verify a real company inbox can receive a code, complete sign-in, access reports, and sign out. Also verify a non-company email is rejected. Do not enable email mode before SMTP and templates are ready.
Codes are checked server-side with Supabase; the returned access token is used to fetch the authenticated user and confirm the email before access is granted. Supabase access/refresh tokens are not stored in the browser or localStorage. The app creates its own signed HttpOnly, SameSite=Strict session cookie (Secure in production), lasting 8 hours by default. Sign-out clears local cookies; deleting or disabling a Supabase user does not revoke an already-issued app cookie until it expires. Rotate SESSION_SECRET to invalidate all app sessions immediately.
Code-request cookies expire after 10 minutes. Each 15-minute window allows 3 code sends and 5 verification attempts per email, with additional IP limits of 20 sends and 50 verification attempts so colleagues can share an office connection. These bounded limits live in memory and reset on process restart; use a single dyno and Supabase's provider-side limits. Invalid/expired codes and email-service outages are shown explicitly rather than bypassing authentication.
Free-tier limits may change. At implementation, Supabase Free included 50,000 monthly active users and could pause after a week of inactivity; Resend Free included 3,000 emails/month, capped at 100/day. Check Supabase pricing and Resend pricing before rollout.
heroku config:set NODE_ENV=production \
APP_PASSWORD='a-long-random-passphrase' \
SESSION_SECRET=$(openssl rand -hex 32) \
BRIGHTSITES_STORES='{"key":{"subdomain":"foo","token":"...","label":"Foo"}}'- Every page and
/apiroute requires sign-in; sessions are signed HttpOnly cookies (default 8h,SESSION_TTL_MS). Logins are throttled to 5 failures per 15 minutes per IP. - With
AUTH_MODE=password, production requiresAPP_PASSWORDandSESSION_SECRET. - Heroku's filesystem is ephemeral:
data/usage.jsonandserver.logreset on restart, and in-memory report jobs are lost if the dyno restarts. Use a single dyno. - A shared password is simple but not per-user; rotate it when a teammate leaves.
For multiple stores, set BRIGHTSITES_STORES to a JSON object keyed by store key:
{
"shop-a": { "subdomain": "shop-a", "token": "API_TOKEN", "label": "Shop A", "role": "product" },
"shop-b": { "subdomain": "shop-b", "token": "API_TOKEN", "label": "Shop B", "role": "redemption" }
}role is optional and only used by the report formats (see below). The legacy single-store variables remain supported. Keep credentials in .env; do not commit it.
Use a plain store key (like shop-a) and put the API token in token. Store keys are shown in the browser, logs and support reports, so a store keyed by its own token exposes that token; the server prints a warning at startup when it sees this.
- Shipment Level (default): one row for each item in each shipment, with rows for unshipped items. An item split across shipments appears more than once. Downloads CSV.
- Order Level: one row per unique source order, including orders with no items or shipments. Financial totals come from the order once; quantities summarize original items and distinct shipments. Unknown quantities remain blank; real zero remains zero. Downloads CSV.
- Both tables (Excel): builds both tables from the same source fetch and enrichment pass. Switch between Order Level and Shipment Level previews and choose columns/split settings separately for each. Download Excel saves both tabs in one
.xlsxfile. - Reports require a selected store and both start and end dates; reversed ranges are rejected. Dates are whole days in
REPORT_TIMEZONE(defaultAmerica/New_York): the range runs from midnight on the start date to the end of the end date in that timezone, and order and ship dates are shown in that timezone too. Set it to the stores' business timezone, for exampleAmerica/Chicago. - Shipment Level and Both reports need UPS credentials unless the request sets
includeUps: false; without them the report is rejected before any BrightSites requests are made. - If BrightSites fails to return an order's details, line items or shipments (after retries), the report fails instead of silently leaving rows out.
- Jobs run asynchronously. The browser polls for progress and can request cancellation.
- At most two jobs run at once by default. Configure
REPORT_JOB_MAX_ACTIVEto change the limit. - Reports are limited to 50,000 rows by default; Both mode counts rows across both tables. Larger results fail with guidance to narrow the date range. Configure
REPORT_MAX_ROWSto change this limit. - Up to five completed report results are retained in memory by default. Older results are evicted when new reports complete. Configure
REPORT_JOB_MAX_RETAINED_RESULTSto change this limit. - Job records expire after four hours by default (
REPORT_JOB_TTL_MS); cleanup runs every ten minutes (REPORT_JOB_CLEANUP_INTERVAL_MS). - Jobs and report results are held in memory and are lost when the server restarts.
- Usage counts are stored in
data/usage.json, keyed by store key. The running process keeps counts in memory and saves them every few seconds and on shutdown. Older entries keyed by API tokens are ignored by/api/usage. There is no reset endpoint; edit the file while the server is stopped if a correction is needed.
Under Report format pick Custom report (the tables above) or one of the FTG preset formats. A preset chooses its own stores, columns, date ranges and calculations, so the store, table type, status filter and column controls are locked while one is selected. Run it with a start and end date, preview each sheet, then Download Excel. The file is named after the format and the run date, for example Summary Report 11092026.xlsx.
| Format | Stores | Dates used | Sheets |
|---|---|---|---|
| Product Store Report | Product | Orders placed from start to end | One row per line item, plus an Order Type column and live totals: total sales, corporate orders, net sales, needs review and canceled |
| Redemption Store Report | Redemption | Orders placed from start to end | One row per award, plus Award Type, Award Level and Redemption Code columns, with award counts by type and level |
| Summary Report | Product + Redemption | The start-to-end week, and year to date (Jan 1 of the end year to the end date) | Weekly and YTD award counts with totals; Brand Store sales less corporate orders (subtracted once); inventory input cells; a Corporate Orders sheet listing every corporate and needs-review order with the reason |
| Weekly FTG Tracking Reports | Redemption + Product + VLR (optional) | Year to date | Awards Redeemed YTD, and orders placed plus shipment tracking for each store |
Store roles. Presets find stores by role. Add "role": "product", "redemption" or "vlr" to an entry in BRIGHTSITES_STORES. Without one, a store whose label or subdomain contains "redemption", "vlr" or "shop" is recognised. A format is unavailable when a required store has no match. The VLR store is optional: when it is missing, its sheets are empty and a warning is shown.
Corporate orders. An order is Corporate when its payment method is "Purchase Order" AND its payment note mentions "FTG Accounting" (FTG Support enters "Attn: FTG Accounting"). Only Corporate orders are deducted. An order that matches only part of the rule, or that was placed with an @forbestravelguide.com email, is marked Needs review: it is listed with the reason but not deducted. The billing company is not used, because it can be wrong.
Tracking sheets list shipments whose ship date is in the year so far. They include orders placed up to PRESET_TRACKING_LOOKBACK_DAYS (default 120) days before January 1, so an order placed in December that ships in January is still listed.
Totals are live Excel formulas (for example SUMIFS over the Order Type and status columns), so they stay correct when rows are filtered or corrected by hand. Canceled orders are always excluded from totals.
Not available from the API. The template's MOAS Approver/Denier column is left blank. Inventory counts are typed into the yellow cells on the Summary sheet.
Caching. Full order details are cached in memory by store and order id, and a cached copy is used only while the order's updated_at is unchanged. Running the Summary Report and then the Weekly FTG Tracking Reports only fetches the order lists again, not every order. The cache holds up to ORDER_CACHE_MAX_ENTRIES orders (default 5000) for ORDER_CACHE_TTL_MS (default 24 hours). It is lost on restart, and ORDER_CACHE=off turns it off. Order data is never written to disk: storing customer data needs approval first. Downloads are built from the finished job result and make no API calls.
Year-to-date formats load every order placed this year (over a thousand by November 2026), so the first run after a restart takes a few minutes and makes that many API requests.
API clients can run a format with POST /api/run and the body { "preset": "summary-report", "start": "2026-11-02", "end": "2026-11-08" }. List the formats with GET /api/presets. Download with POST /api/run/:jobId/preset-workbook.
The Order Level and Shipment Level tabs contain native Excel tables named OrdersTable and ShipmentsTable, with filters and frozen headers. Both include a mandatory text Order Key, formed from the store subdomain and canonical order identifier. It links one order to all its detail rows even if the displayed Order # is hidden or reused.
View Shipments opens the first matching detail row. Filter the Shipment Level Order Key column to see every related item. Each detail row has View Order to return to its parent. Shipment Detail Rows counts related rows; Shipment Count counts distinct source shipments. For example, order 00100 with two items split across two shipments has one order row, three detail rows, an original/shipped quantity of five, and an order total of 125.00 counted once in Order Level.
Links and detail counts use key-based formulas and recalculate when either table is sorted. Keep Excel's calculation mode automatic, or recalculate after editing. The workbook is a snapshot of one completed job: it does not refresh BrightSites data, and source amounts/quantities do not recalculate when rows are edited. Order-level summaries contain distinct readable values, for example Matte; Glossy in Product Option: Finish. Use Shipment Level for each individual item's values.
Excel exports allow one build at a time and default to 10,000 combined data rows (WORKBOOK_MAX_ROWS) and 100,000 cells, including headers and relationship columns (WORKBOOK_MAX_CELLS). These checks run before allocating the expanded dynamic table. Exceeding a limit returns an error; select fewer columns or narrow the date range. Results are never truncated, and generated workbook buffers are not retained. A representative 97,776-cell export peaked at about 249 MiB in an isolated process; overall server memory also depends on retained jobs.
API clients can send reportLevel: "shipment", "order", or "both" to POST /api/run. Single modes keep the existing columns/rows result shape; Both returns tables.order and tables.shipment, each with aligned rowKeys. Download a completed Both job with POST /api/run/:jobId/workbook; the optional body is { "selections": { "order": { "columns": ["Order #"], "splitPairs": true }, "shipment": { "columns": ["Order #", "Product Options"], "splitPairs": true } } }. Omitted selections include all columns without splitting. The endpoint uses server-held rows and accepts selection settings only.
- The standard report column names are defined in
config/report.js; the misspelling "Shipping Landded Cost" is intentional. - "Split Product Personalization and Product Options" turns each key into its own column (e.g.
Product Personalization: Establishment Name,... Establishment Name Price,Product Option: Award Level). Rows with the same keys share columns; unique keys add columns. Not used with Danielle's Format. - Split columns are discovered dynamically from the actual keys across every report row, including newly added options. For example,
Finish: MatteaddsProduct Option: Finish; rows without that key have a blank cell. Structured options take priority over summary text. The confirmed four-part Version format (Plaque, 4 Star, 2026, Hotel) expands toProduct Option: Product Type,Product Option: Award Level,Product Option: Year, andProduct Option: Category. It requires four nonempty parts with a four-digit year in the third position. Other Version formats and comma-containing option values retain their original key and value. With splitting off, the original Version text remains available. - Danielle's Format is a separate client-side column mapping available only for standalone Shipment Level CSV reports.
- CSV values are escaped and prefixed when they could be interpreted as spreadsheet formulas.
- BrightSites API access uses v2.6.1; requests are paginated and rate-limited per token.
Run the automated tests with:
npm testClick Download support info (top right of the page) to save a plain-text support-report-*.txt file. It lists recent reports (store, dates, orders, rows, duration, errors), BrightSites API calls this month, web request counts, warnings/errors, and the last 100 log events. Tokens, passwords and cookies are hidden. Send that file when asking for help. Browser-side script errors are also logged. The data is in memory (last 500 log events, last 50 reports) and resets on restart; server.log keeps the full file log locally (with the same secrets hidden, rotated to server.log.1 past 5 MB at startup; tests do not write to it), and on Heroku use heroku logs --tail.
Create an app at developer.ups.com with the Tracking API enabled, then set UPS_CLIENT_ID and UPS_CLIENT_SECRET (on Heroku: heroku config:set ...). UPS tracking data is added automatically; no checkbox is needed. Each UPS tracking number (starting 1Z) is looked up once and appended as UPS Status, UPS Service, UPS Weight and Expected Delivery Date columns, plus Last Scan, Delivered Date/Time, Received By, Delivery Location, Origin, Destination, Reference Numbers, Package Count, Additional Services, Dimensions and Alternate Tracking # (all from the Tracking API response; blank when UPS doesn't return them). The Tracking API does not return the shipper/billing account number (it is only a request filter), so that needs the UPS invoice data; other carriers are left blank. Delivered results are cached for 24 hours, others for 15 minutes. A failed lookup shows Lookup failed and does not stop the report. UPS's Tracking API does not return billed shipping charges; those only come from UPS invoices.
UPS enrichment is enabled by default in Shipment Level and Both mode, and applies only to shipment rows. Missing UPS credentials cause these reports to fail with a configuration error. API clients can explicitly send includeUps: false to opt out. Order-only reports skip enrichment; order-only requests with includeUps: true are rejected.
Use Choose report columns to select fields, then the up/down buttons under Arrange selected columns to set their order. Selection, order and split settings save automatically to your signed-in account, separately for Order Level and Shipment Level. They are restored on another browser or device after sign-in. New accounts start with all columns; existing browser-only selections are not imported. "Core columns" restores the active level's core set.
The save indicator confirms when changes reach your account. If saving fails, use Retry before leaving the page; unsaved changes are retained for retry while the page remains open. If loading fails, retry before editing columns or running a report. Shared-password/local development mode keeps browser-local preferences instead of account storage.
Preview, CSV and Excel use the same selected-column order. Split personalization/option fields follow their parent field's position. Excel always retains its mandatory Order Key and relationship/navigation columns. Danielle's Format keeps its fixed schema. Extra fields include order sales tax, shipping tax, discounts, customer email, payment method/status, in-hands date, product/internal IDs, tax code, base/options price, logo charges and line-item notes. Tax and shipping come from BrightSites tax_price, shipment.cost and required_adjustments.
Before deploying account defaults:
- Execute the migration once in the same Supabase project's SQL Editor. It creates
report_column_preferences, enables row-level security, and denies direct browser access. - In Project Settings → API Keys → Secret keys, obtain a server secret (
sb_secret_...; a legacyservice_rolekey also works). - Set
SUPABASE_SECRET_KEYin the ignored local.envand secure Heroku config, alongsideSUPABASE_URL. Never commit this privileged key, expose it in browser JavaScript, or use it as the authentication publishable key. Restart the server after configuring it.
Preferences are keyed by the verified company email from the app session, not a client-supplied identity. GET /api/preferences/columns loads both layouts; PUT /api/preferences/columns/:level saves a validated layout for order or shipment. Missing storage configuration or failed database requests produce explicit load/save errors rather than silently reverting to browser storage.
Review these at the start of the next session and decide whether to fix them:
- Memory use on large reports. Each finished report keeps its full rows plus the personalization/option detail in memory (
REPORT_JOB_MAX_RETAINED_RESULTS, default 5, up to 50,000 rows each). On a 512 MB Basic dyno this can run out of memory. Suggested: lower the default to 2 or setREPORT_JOB_MAX_RETAINED_RESULTS=2on Heroku. - Multiple UPS tracking numbers in one cell. The UPS lookup only matches a cell containing exactly one
1Z...number, so a shipment with several tracking numbers gets blank UPS columns. Suggested: split the cell, look up each number, and join the results.