Price Import Tool

Overview

The Price Import Tool is a standalone web application for importing supplier pricing from CSV and Excel files into the Magic e-VERSE tenant databases. It replaces manual SQL scripts with a visual 4-step wizard that lets admins upload a file, map columns, preview every price change, and commit to any combination of the 9 tenant databases in one click.

Frontend

React + Vite + Tailwind CSS

Backend

Express + TypeScript + PostgreSQL

Hosting

Coolify on 159.195.68.41

Repo

midego1/Magic-Price-Import

Item	Value
URL	price-import.magicomniverse.online
Auth	HTTP Basic Auth (username: `admin`)
Coolify UUID	`q1170pybhb50tr775avja4p9`
Coolify Project	tools / production
Health Check	`GET /api/health` (public, no auth)
Database	All tenant DBs via `magic-postgres` on Coolify server

Architecture

Browser → https://price-import.magicomniverse.online
           ↓
       Coolify Proxy (Traefik + TLS)
           ↓
       Docker Container (Node.js)
           ├── Express API (port 3000)
           │   ├── POST /api/parse     → parse CSV/XLSX in memory
           │   ├── POST /api/preview   → diff against development DB
           │   ├── POST /api/commit    → batch UPDATE all selected tenants
           │   ├── POST /api/migrate   → create audit log table
           │   └── GET  /api/health    → health check (public)
           └── Static Frontend (Vite build)
               └── React SPA with Tailwind CSS

Data Flow

Step 1: Upload              Step 2: Map Columns         Step 3: Preview           Step 4: Commit
┌─────────────┐            ┌──────────────────┐        ┌────────────────┐        ┌─────────────────┐
│ Select file  │ ──POST──→ │ Auto-detect       │ ──→   │ Query dev DB   │ ──→   │ For each tenant: │
│ (.csv/.xlsx) │  /parse   │ delimiter, BOM    │       │ Compute diff   │       │   BEGIN          │
│              │           │ Parse rows        │       │ Show old→new   │       │   batch UPDATE   │
│ Select       │           │ Return columns +  │       │ Color-coded    │       │   COMMIT         │
│ template     │           │ sample data       │       │ Select tenants │       │ Promise.allSettled│
└─────────────┘            └──────────────────┘        └────────────────┘        │ Write audit log  │
                                                                                 └─────────────────┘

Wizard Steps

Step 1 — Upload

Select a template (Spranz VK, Langenberg, or Custom)
Click the upload zone to select a .csv, .xlsx, or .xls file (max 10MB)
File is parsed in memory — never written to disk
CSV: auto-detects ; vs , delimiter, strips BOM
XLSX: reads first sheet

Step 2 — Column Mapping

Choose price type: Verkoop (retail), Inkoop (buying), or Beide (both)
Map the variant code column (required)
Map up to 10 price columns to database fields:
- retail_price_100, retail_price_200, retail_price_300, retail_price_500, retail_price_1000
- buying_price_100, buying_price_200, buying_price_300, buying_price_500, buying_price_1000
If a template is selected, columns are auto-mapped
Preview of first 10 rows shown below the mapping

Step 3 — Preview & Confirm

Shows a diff table of every price change:
- Variant Code | Naam | Kolom | Oud | Nieuw | Verschil (%)
- Green rows = price decrease, Orange = increase, Red = not found in DB
Summary badges: X te wijzigen, X ongewijzigd, X niet gevonden
Tenant checkboxes: select which databases to update (default: Development only)
“Meer laden” pagination (50 rows at a time)

Step 4 — Result

Per-tenant result table: Tenant | Bijgewerkt | Status
Audit log ID for traceability
“Nieuwe Import” button to start over

Built-in Templates

Template	Supplier	Type	Variant Column	Price Columns
Spranz VK	Spranz	Verkoop	`Variantcode`	`VK Prijs > 100/200/300/500/1000`
Langenberg	Langenberg	Verkoop	`articlenumber`	`price1`, `price250`, `pric500`, `price1000`
Handmatig	Any	Any	Manual select	Manual select

Tenant Databases

The tool can write to any combination of these databases:

Database	Tenant	Description
`magic_b2b_development`	Development	Primary dev/test environment
`magic_b2b_brinxx`	Brinxx	Production tenant
`magic_b2b_default`	Default	Default tenant
`magic_b2b_demo`	Demo	Demo environment
`magic_b2b_spranz`	Spranz	Spranz tenant
`magic_b2b_jodasign`	Jodasign	Jodasign tenant
`magic_b2b_logohorloge`	Logohorloge	Logohorloge tenant
`magic_b2b_desluis`	De Sluis	De Sluis tenant
`magic_b2b_bovisales`	Bovisales	Bovisales tenant

All tenants are on the same PostgreSQL server (magic-postgres inside the Coolify Docker network).

API Reference

`POST /api/parse`

Upload and parse a CSV or XLSX file.

Request: multipart/form-data with a file field.

Response:

{
  "success": true,
  "columns": ["Variantcode", "VK Prijs > 100", ...],
  "sample": [{"Variantcode": "376-00.001", ...}],
  "rows": [...],
  "total_rows": 614,
  "file_format": "csv",
  "source_filename": "Spranz 260327.csv"
}

`POST /api/preview`

Compute price diff against the development database.

Request:

{
  "rows": [...],
  "column_mapping": {
    "variant_col": "Variantcode",
    "retail_100": "VK Prijs > 100",
    "retail_200": "VK Prijs > 200"
  },
  "price_type": "retail"
}

Response:

{
  "success": true,
  "diff": [
    {
      "variant_code": "376-00.001",
      "sku": "376-00",
      "name": "Kugelschreiber FALK",
      "price_col": "retail_price_100",
      "old_value": 3.45,
      "new_value": 3.60,
      "change_pct": 4.35,
      "status": "changed"
    }
  ],
  "summary": {
    "updated": 42,
    "no_change": 560,
    "not_found": 12,
    "total_rows": 614
  }
}

`POST /api/commit`

Apply price changes to selected tenant databases.

Request:

{
  "diff": [...],
  "tenant_dbs": ["magic_b2b_development", "magic_b2b_brinxx"],
  "price_type": "retail",
  "template_key": "spranz_retail",
  "template_name": "Spranz VK (verkoop)",
  "source_filename": "Spranz 260327.csv"
}

Response:

{
  "success": true,
  "results_per_tenant": [
    { "db": "magic_b2b_development", "success": true, "rows_updated": 42 },
    { "db": "magic_b2b_brinxx", "success": true, "rows_updated": 42 }
  ],
  "total_updated": 84,
  "total_changed_rows": 42,
  "log_id": 1
}

`POST /api/migrate`

Creates the aplt_price_import_log audit table on the development database. Run once after first deployment.

`GET /api/health`

Public health check endpoint (no auth required).

{ "status": "ok", "service": "magic-price-import" }

Database Schema

`aplt_price_import_log`

Audit trail for every import operation. Stored on magic_b2b_development.

CREATE TABLE aplt_price_import_log (
  id              SERIAL PRIMARY KEY,
  imported_at     TIMESTAMP DEFAULT NOW(),
  imported_by     VARCHAR(255) NOT NULL DEFAULT 'admin',
  template_key    VARCHAR(100),
  template_name   VARCHAR(100),
  tenant_dbs      TEXT[] NOT NULL,
  price_type      VARCHAR(10) NOT NULL,
  rows_updated    INTEGER NOT NULL DEFAULT 0,
  rows_skipped    INTEGER NOT NULL DEFAULT 0,
  rows_not_found  INTEGER NOT NULL DEFAULT 0,
  sample_changes  JSONB,
  source_filename VARCHAR(255)
);

Target Table: `aplt_products`

The tool updates these columns on the aplt_products table:

Column	Type	Description
`variant_code`	`VARCHAR(255)`	Unique identifier for matching (WHERE clause)
`retail_price_100`	`NUMERIC(10,2)`	Retail price for 100+ quantity
`retail_price_200`	`NUMERIC(10,2)`	Retail price for 200+ quantity
`retail_price_300`	`NUMERIC(10,2)`	Retail price for 300+ quantity
`retail_price_500`	`NUMERIC(10,2)`	Retail price for 500+ quantity
`retail_price_1000`	`NUMERIC(10,2)`	Retail price for 1000+ quantity
`buying_price_100`	`NUMERIC(10,2)`	Buying price for 100+ quantity
`buying_price_200`	`NUMERIC(10,2)`	Buying price for 200+ quantity
`buying_price_300`	`NUMERIC(10,2)`	Buying price for 300+ quantity
`buying_price_500`	`NUMERIC(10,2)`	Buying price for 500+ quantity
`buying_price_1000`	`NUMERIC(10,2)`	Buying price for 1000+ quantity

SQL Strategy

The commit step uses a batch UPDATE with VALUES pattern for performance:

UPDATE aplt_products AS p
SET retail_price_100 = CASE WHEN v.v_retail_price_100 IS NOT NULL
      THEN v.v_retail_price_100::numeric ELSE p.retail_price_100 END,
    retail_price_200 = ...,
    updated_at = NOW()
FROM (VALUES
  ('376-00.001', 3.60, 4.20, ...),
  ('376-00.002', 3.80, 4.40, ...),
  ...
) AS v(vc, v_retail_price_100, v_retail_price_200, ...)
WHERE p.variant_code = v.vc

This executes 1 query per tenant regardless of how many rows are updated (vs N individual UPDATEs). For 614 rows across 9 tenants, that’s 9 queries instead of 5,526.

Each tenant runs in its own transaction (BEGIN / COMMIT or ROLLBACK), and all tenants execute in parallel via Promise.allSettled — one failing tenant does not block the others.

Security

Layer	Protection
Authentication	HTTP Basic Auth via `AUTH_USER`/`AUTH_PASS` env vars
SQL Injection	Column names come from a hardcoded allowlist (`PRICE_COLUMNS`), never from user input
Tenant Validation	`tenant_dbs` validated against `TENANT_DATABASES` allowlist before any DB operation
File Handling	Files parsed in memory only, never written to disk
TLS	Traefik auto-provisions Let’s Encrypt certificates

Edge Case Handling

Scenario	How it’s handled
Dutch CSV delimiter `;`	Auto-detected by counting `;` vs `,` in header line
European decimal `12,50`	Parsed as `12.50` (comma → period conversion)
Thousand separators `1.234,50`	Parsed as `1234.50` (period removed, comma → period)
UTF-8 BOM `\uFEFF`	Stripped before parsing
Empty rows in XLSX	Skipped (rows where all values are empty)
XLSX with multiple sheets	Uses first sheet (`SheetNames[0]`)
Variant code not found	Shown as “niet gevonden” in preview, skipped during commit
All prices unchanged	Commit reports 0 rows updated
One tenant fails	Other tenants still succeed (`Promise.allSettled`)

Environment Variables

Variable	Default	Description
`PORT`	`3000`	Express server port
`DB_HOST`	`localhost`	PostgreSQL host (use `magic-postgres` on Coolify)
`DB_PORT`	`5432`	PostgreSQL port
`DB_USER`	`postgres`	PostgreSQL username
`DB_PASSWORD`	—	PostgreSQL password (required)
`AUTH_USER`	`admin`	Basic Auth username
`AUTH_PASS`	—	Basic Auth password (if empty, auth is disabled)

Deployment

The app deploys automatically via Coolify when code is pushed to main on midego1/Magic-Price-Import.

Manual Redeploy

Via Coolify API:

curl -X POST 'http://localhost:8000/api/v1/applications/q1170pybhb50tr775avja4p9/start' \
  -H 'Authorization: Bearer <COOLIFY_TOKEN>'

Or via the Coolify dashboard at https://app.magicomniverse.online.

First-time Setup

Deploy the app via Coolify (connect to midego1/Magic-Price-Import repo)
Set environment variables (DB_HOST, DB_PASSWORD, AUTH_PASS)
Run the migration: POST https://price-import.magicomniverse.online/api/migrate
Verify: GET https://price-import.magicomniverse.online/api/health

Project Structure

Magic-Price-Import/
├── Dockerfile                    # Multi-stage: frontend build → backend build → production
├── backend/
│   ├── package.json
│   ├── tsconfig.json
│   └── src/
│       ├── index.ts              # Express server + Basic Auth middleware
│       ├── routes/
│       │   ├── parse.ts          # POST /api/parse — file upload + parsing
│       │   ├── preview.ts        # POST /api/preview — diff computation
│       │   ├── commit.ts         # POST /api/commit — multi-tenant write
│       │   └── migrate.ts        # POST /api/migrate — create audit table
│       └── utils/
│           ├── tenant-db.ts      # Tenant DB config, createTenantClient()
│           └── parse-file.ts     # CSV/XLSX parsing, European decimal handling
└── frontend/
    ├── package.json
    ├── vite.config.ts
    ├── tailwind.config.js
    └── src/
        ├── main.tsx
        ├── index.css
        └── App.tsx               # 4-step wizard UI with templates

Adding a New Supplier Template

Edit frontend/src/App.tsx and add to the IMPORT_TEMPLATES object:

new_supplier: {
  name: "New Supplier (verkoop)",
  description: "Description of the CSV format",
  price_type: "retail",
  mapping: {
    variant_col: "article_number",    // column name in the CSV
    retail_100: "price_100",          // maps to retail_price_100
    retail_500: "price_500",          // maps to retail_price_500
    retail_1000: "price_1000",        // maps to retail_price_1000
  },
},

The mapping keys follow the pattern retail_XXX or buying_XXX where XXX is the quantity tier. The values are the column names as they appear in the supplier’s CSV/XLSX file.

Audit & Compliance

Every import is logged in the aplt_price_import_log table with:

Who imported (imported_by)
Which template was used
Which tenants were affected
How many rows were updated, skipped, or not found
A sample of the first 20 changes (as JSONB)
The source filename

Query the audit log:

SELECT id, imported_at, template_name, tenant_dbs, rows_updated, source_filename
FROM aplt_price_import_log
ORDER BY imported_at DESC
LIMIT 10;

Price Import Tool

Overview

Architecture

Data Flow

Wizard Steps

Step 1 — Upload

Step 2 — Column Mapping

Step 3 — Preview & Confirm

Step 4 — Result

Built-in Templates

Tenant Databases

API Reference

POST /api/parse

POST /api/preview

POST /api/commit

POST /api/migrate

GET /api/health

Database Schema

aplt_price_import_log

Target Table: aplt_products

SQL Strategy

Security

Edge Case Handling

Environment Variables

Deployment

Manual Redeploy

First-time Setup

Project Structure

Adding a New Supplier Template

Audit & Compliance

`POST /api/parse`

`POST /api/preview`

`POST /api/commit`

`POST /api/migrate`

`GET /api/health`

`aplt_price_import_log`

Target Table: `aplt_products`