Shopmaster Product Upsert API

Express + Node.js + SQL Server integration for receiving Shopmaster product and inventory updates.

It implements:

• GET /rest/V1/integrations/shopmaster/products for listing products
• POST /rest/V1/integrations/shopmaster/products/upsert for one product
• POST /rest/V1/integrations/shopmaster/products/bulk-upsert for up to 15 products
• GET /health for a basic unauthenticated health check
• GET /api-docs for Swagger UI
• GET /storefront-api-docs for storefront/integration-only Swagger UI
• GET /admin/ for the responsive store administration dashboard
• GET /rest/V1/admin/* for authenticated dashboard reads and reports
• GET /rest/V1/store/products and GET /rest/V1/store/products/:productId for the public, active-only storefront catalog
• Authenticated customer/supplier/sales/purchase reads under /rest/V1/integrations/shopmaster/*

Admin Dashboard

Open http://localhost:3000/admin/ and enter the same integration bearer token used by API clients. The token is retained only for the current browser tab.

The dashboard reads the existing SQL Server operational tables used by this application: ProductsTable, Customers, Suppliers, SalesInvoices, and PurchaseInvoices. It provides overview metrics, sales trends, top products, stock status and valuation, product groups, customer/supplier directories, sales and purchase entry, and operational reports.

Production workflow:

• Products is a catalogue of products established through purchases; it does not provide standalone product creation.
• Purchases replenishes an existing product or creates a new product and its opening stock inside the same supplier-validated SQL transaction.
• Customers is a read-only directory. Sales accepts optional customer details; blank details use the explicit WALK-IN customer record, while unambiguous phone/email details match an existing customer or create one transactionally.
• Product groups are ProductsTable.GroupID category labels used for organization and reporting, not separate stock records.
• The dashboard Documentation tab links directly to the storefront Swagger and explains catalogue reads, sale submission, reconciliation, and token safety.

The production dashboard uses static ShopMaster branding. It does not present editable store settings or navigation for roles, stocktakes, payment modes, expenses, chart of accounts, or full financial statements because no verified source tables are connected for those capabilities.

GET /rest/V1/admin/capabilities returns the same supported/unsupported map for clients. All /rest/V1/admin routes require bearer authentication.

The public storefront product projection intentionally excludes cost price and inactive products. Management and integration writes remain bearer-protected. Standalone customer/upsert and supplier/upsert endpoints establish party records before related commerce operations; purchases reject unknown suppliers, and sales reject unknown products or insufficient stock.

Product, supplier, customer, sales-invoice, and purchase-invoice identifiers are generated transactionally when omitted. Existing callers may continue supplying identifiers for edits/import compatibility. The legacy PurchaseInvoices schema has no verified separate supplier-reference column, so the dashboard labels a supplier reference as session-only and does not claim to persist it.

Normal sales never accept cost authority from staff or the storefront. The sale transaction locks the product row, reads ProductsTable.Costprice, and stores that value in SalesInvoices.CostPrice; a legacy costprice request field is accepted but ignored. Product administration maintains cost and sale prices. Purchases require unit cost and unit sell price and update both existing product price columns while adding stock.

Use /storefront-api-docs when integrating a customer-facing storefront. Its intentional allowlist contains public product list/detail reads, authenticated sale recording and retrieval, and the optional authenticated order-notification operation. Customer updates, suppliers, purchases, product management, dashboard-admin routes, and credential values are excluded. Sales recorded by the storefront appear in the admin dashboard. The matching machine-readable document is /storefront-openapi.json. For this local handoff, the storefront document lists only http://localhost:3000; the unavailable hosted deployment is deliberately excluded from Swagger's server selector.

Prerequisites

• Node.js 18+; this project was built against modern Node versions
• SQL Server 2017 or newer
• A .env file with your real database details and integration token

Install

npm.cmd install
copy .env.example .env

Then edit .env.

Important variables:

Var	Purpose
INTEGRATION_TOKEN	Bearer token Shopmaster sends in the Authorization header
DB_SERVER	SQL Server host, or host\instance for a named instance
DB_INSTANCE	Optional named instance if you do not include it in DB_SERVER
DB_PORT	SQL Server port; use 1433 for normal TCP connections
DB_DATABASE / DB_USER / DB_PASSWORD	Database credentials
PORT	HTTP port, defaults to 3000

If DB_PORT is set, the app connects by host and port. If DB_PORT is blank and DB_SERVER contains host\instance, the app connects through the named instance.

Optional Historical Schema

Run:

npm.cmd run db:init

db/schema.sql describes an earlier normalized product model. The current runtime uses the existing legacy operational tables and the dashboard does not require or create any new tables. Do not run this script against a production store unless that older schema is explicitly required and reviewed.

Running the script reads db/schema.sql, creates missing tables, and seeds:

• The six supported skin types
• Lagos Flagship Store
• Cleanser, so the bundled samples work

The script is idempotent, so it is safe to run again.

Run

Production-style:

npm.cmd start

Development with auto-restart:

npm.cmd run dev

Expected startup:

[db] connected to your_database
[server] Shopmaster Product API listening on :3000

Test

Swagger UI:

http://localhost:3000/api-docs

Health check:

curl.exe http://localhost:3000/health

Single upsert:

curl.exe -X POST http://localhost:3000/rest/V1/integrations/shopmaster/products/upsert `
  -H "Authorization: Bearer YOUR_INTEGRATION_TOKEN" `
  -H "Content-Type: application/json" `
  -H "Idempotency-Key: evt_20260616_0001" `
  --data-binary "@samples/single-upsert.json"

Bulk upsert:

curl.exe -X POST http://localhost:3000/rest/V1/integrations/shopmaster/products/bulk-upsert `
  -H "Authorization: Bearer YOUR_INTEGRATION_TOKEN" `
  -H "Content-Type: application/json" `
  -H "Idempotency-Key: batch_20260616_001" `
  --data-binary "@samples/bulk-upsert.json"

Replace YOUR_INTEGRATION_TOKEN with the value in your .env.

Dashboard access and security

Open /admin/ and sign in with INTEGRATION_TOKEN. The dashboard validates the token before loading protected store data, keeps it only in sessionStorage, and removes it on Logout or when the server returns 401. Protected admin and integration requests always use Authorization: Bearer and are returned with Cache-Control: no-store.

This is one shared integration token, not multi-user authentication or RBAC. Anyone who knows the token has the same dashboard/API authority, so distribute and rotate it as a production secret and never embed it in storefront browser code.

Authenticated dashboard drill-down reads are available at:

• GET /rest/V1/admin/sales/{invoiceNo}
• GET /rest/V1/admin/purchases/{invoiceNo}
• GET /rest/V1/admin/products/{productId} (including sales/purchase history)
• GET /rest/V1/admin/suppliers/{supplierId} (including purchase evidence)

These endpoints join only the verified legacy tables. Purchase history proves that a supplier appeared on those invoice rows; it does not define a permanent supplier-product ownership relationship. Current stock always comes from the present ProductsTable.QtyInStock, not from an invoice’s historical quantity.

Storefront sales

Use POST /rest/V1/integrations/shopmaster/sales for new storefront sales. The strict JSON body requires InvoiceNo, InvoiceDate, ProductID, Quantity, SellPrice, and customer containing name, phone, and address (email is optional). The storefront must generate a unique InvoiceNo; duplicates return HTTP 409 and cannot update the existing sale. Cost price is read from the product and cannot be supplied by the storefront. The transaction validates the product, customer and stock, then writes the same dbo.SalesInvoices source read by both GET .../sales and the admin Sales page.

The older POST .../sales/upsert route remains available for existing staff or import clients but is deprecated and intentionally absent from Storefront Swagger. New storefronts must use the immutable route.

Render deployment

The included render.yaml hosts the API, /admin/ dashboard, public catalog, and Swagger UI from one Node web service. Create a Render Blueprint from the repository, then provide these secret environment values in Render:

• INTEGRATION_TOKEN
• DB_SERVER, DB_DATABASE, DB_USER, DB_PASSWORD
• either DB_PORT or DB_INSTANCE, according to the SQL Server endpoint
• DB_ENCRYPT and DB_TRUST_SERVER_CERTIFICATE as required by the server

Render supplies PORT; do not set it manually. Verify /health, then open /admin/ and /storefront-api-docs. Cross-origin storefronts are allowed to send Authorization, Content-Type, and Idempotency-Key; all writes and sales reads still require the bearer token, while catalog reads remain public.

The SQL Server must accept network connections from Render and the configured login must be mapped to the database with the existing table permissions. A successful Render build cannot compensate for a blocked firewall, unreachable named instance, or rejected SQL login.

In Storefront Swagger, click Authorize and enter the raw integration token without writing Bearer yourself. If you paste a dotenv assignment such as INTEGRATION_TOKEN=value (including a single- or double-quoted value), the Storefront Swagger request interceptor removes the variable name and quotes before sending the bearer header. This convenience exists only in Swagger UI; direct API callers must always send Authorization: Bearer value. Swagger retains authorization for the tab and executes against the same origin and port that served the documentation.

Fetch all products:

curl.exe "http://localhost:3000/rest/V1/integrations/shopmaster/products?page=1&limit=50" `
  -H "Authorization: Bearer YOUR_INTEGRATION_TOKEN"

How It Works

Request flow:

request
  -> auth middleware
  -> idempotency middleware
  -> route validation
  -> transactional product upsert
  -> JSON response

The upsert service:

• Inserts or updates dbo.ProductsTable by external_product_id
• Maintains cost, sale price, stock, reorder threshold, POS name, and status
• Leaves unsupported catalog metadata untouched/unpersisted
• Handles the optional primary image separately through dbo.ProductImage

Product images

The dashboard stores one primary product image only in the verified legacy dbo.ProductImage table (ProductID, binary ProductImg). Uploads are authenticated raw JPEG or PNG bodies, limited to 10 MB, 4096×4096, and 16 million pixels. The server validates actual signatures, structure, declared MIME type and dimensions and strips optional metadata/comment segments.

The table can technically contain multiple rows, but it has no verified image identifier or display-order column. ShopMaster therefore intentionally uses a single-primary-image policy: PUT /rest/V1/admin/products/{productId}/image deletes any duplicate legacy rows for that product and stores one normalized binary. Public and admin product JSON retain an images array for storefront contract consistency, but it contains at most one item. The public delivery URL is GET /rest/V1/store/products/{productId}/image; the authenticated admin URL is GET /rest/V1/admin/products/{productId}/image.

New-product purchases require an image in the dashboard. The browser validates the file before recording the purchase, creates the product inactive, uploads the image, then applies the requested status. These are two database operations because the legacy purchase JSON transaction and binary image table cannot be made one atomic request without a schema/protocol change. If image upload or activation fails, the purchase remains recorded and the new product remains inactive for safe recovery. Existing image-less legacy products remain visible to admins but are omitted from public catalog/detail responses even if their status field is active. This prevents a broken storefront image requirement.

The incompatible dbo.product_images model is never used.

Project Structure

src/
  config/        env.js, db.js
  db/            resolvers.js
  middleware/    auth.js, idempotency.js, errorHandler.js
  routes/        products.routes.js
  schemas/       product.schema.js
  scripts/       initDb.js
  services/      productUpsert.service.js
  workers/       imageDownloader.js
  server.js
db/
  schema.sql
samples/
  single-upsert.json
  bulk-upsert.json

Security Notes

• Do not commit .env.
• Use a strong random INTEGRATION_TOKEN.
• Rotate any database password that was previously shared in docs or examples.
• Add rate limiting or IP allow-listing before exposing this publicly.