MistralMapper

MistralMapper is a React and Vite application with a Node and Express API for generating, reviewing, and storing mappings between WildApricot invoice data and QuickBooks product and class data. PostgreSQL stores user accounts and login sessions, while MongoDB stores approved mapping records.

The application supports account-based test data discovery, a guided 5-step mapping workflow, Mistral-powered mapping suggestions, manual confirmation, and MongoDB persistence for approved mappings.

Application Flow

The main UI lives in src/components/MappingConfiguration.jsx and orchestrates this sequence:

1. Load WildApricot invoice JSON from a file upload or account-specific endpoint.
2. Load QuickBooks product and class JSON from a file upload or account-specific endpoint.
3. Build a prompt and request suggested mappings from Mistral through a server-side route.
4. Let the user confirm or adjust the generated mapping.
5. Store the confirmed mapping in MongoDB.

Architecture

The project has two runtime parts:

1. Frontend: React app served by Vite.
2. Backend: Express API in server/index.js for MongoDB access, health checks, and optional server-side Mistral access.

During local development, Vite also exposes two development-only middleware routes:

1. /dev-api/test-accounts
2. /dev-api/mistral-mapping

These routes let the frontend discover account folders and call Mistral without exposing the API key in browser code.

Project Structure

Key folders and files:

1. src/components/MappingConfiguration.jsx: main workflow UI.
2. src/services/mappingSequence.js: orchestration for loading data, generating prompts, calling mapping endpoints, and saving results.
3. server/index.js: Express API for health, history, latest mappings, persistence, and backend Mistral calls.
4. vite.config.js: Vite configuration plus development middleware for account discovery and Mistral proxying.
5. public/: static assets and test data folders.
6. dist/: production build output generated by Vite.

Environment Variables

Copy .env.example to .env for local development.

Required variables:

1. MISTRAL_API_KEY: used by the dev server and backend to call Mistral.
2. MONGODB_URI: MongoDB connection string for mapping persistence.
3. DATABASE_URL: PostgreSQL connection string for users and sessions.
4. SESSION_SECRET: random secret with at least 32 bytes of entropy.

Optional variables:

1. PORT: backend port. Default is 4000.
2. ALLOWED_ORIGIN: allowed frontend origin for CORS. Default is http://localhost:5173.
3. MONGODB_DB: Mongo database name. Default is MistralMapper.
4. MONGODB_COLLECTION: Mongo collection name. Default is Mappings.
5. MONGO_API_KEY: optional bearer token required by POST /api/mappings when configured.
6. DATABASE_POOL_SIZE: PostgreSQL connection pool size. Default is 10.

Security Notes

Do not expose Mistral credentials to the frontend bundle.

Use this pattern:

1. Store MISTRAL_API_KEY only in server-side environment configuration.
2. Let the frontend call /dev-api/mistral-mapping in development or /api/mistral-mapping in backend-driven deployments.
3. Do not rely on VITE_MISTRAL_API_KEY in production.

Also make sure:

1. .env is not committed.
2. .env.example contains placeholders only.
3. Production secrets are injected by the hosting platform or secret manager.

Installation

Install dependencies:

npm install

Running Locally

Prepare the PostgreSQL authentication tables after configuring DATABASE_URL:

npm run db:migrate

Run frontend and backend together:

npm run dev:full

Or run them separately:

npm run dev

npm run dev:server

Default local URLs:

1. Frontend: http://localhost:5173
2. Backend: http://localhost:4000

Build

Create a production build:

npm run build

Preview the production build locally:

npm run preview

REST API

Authentication

1. POST /api/auth/register: creates an organization and user, then starts a session.
2. POST /api/auth/login: verifies the password and starts a session.
3. GET /api/auth/me: returns the currently signed-in user or null.
4. POST /api/auth/logout: destroys the current session.
5. POST /api/auth/forgot-password: sends a single-use password reset link.
6. POST /api/auth/reset-password: verifies the reset token and stores a new password hash.

Passwords are hashed with Argon2id before they are stored. Browser sessions use an HTTP-only cookie and are stored in PostgreSQL.

Password reset emails use Resend. Configure RESEND_API_KEY, RESET_EMAIL_FROM, and APP_URL to deliver email. During local development without a Resend key, the backend prints the reset link to its terminal instead of sending it.

GET /api/health

Returns backend and MongoDB status.

Example response:

{
	"ok": true,
	"mongodb": "connected",
	"database": "MistralMapper",
	"collection": "Mappings"
}

POST /api/mistral-mapping

Calls Mistral from the backend using the server-side API key.

Example request body:

{
	"model": "mistral-large-latest",
	"prompt": "Map WA order types to QuickBooks products and classes.",
	"wildApricotItems": ["Adult Membership", "Student Membership"],
	"quickBooksProducts": [
		{ "Id": "101", "Name": "Adult Member", "Classification": "Membership" }
	],
	"quickBooksClasses": ["Membership"],
	"isAccountant": false
}

GET /api/mappings/history

Returns the most recent stored mappings used as context for repeat runs.

Supported query parameters:

1. accountId: optional numeric account identifier.
2. isAccountant: true or false.

GET /api/mappings/latest

Returns the latest n mappings for a specific account.

Supported query parameters:

1. accountId: required numeric account identifier.
2. n: optional number of records to return. Default is 10, capped at 100.

Example:

/api/mappings/latest?accountId=1001&n=25

POST /api/mappings

Stores a confirmed mapping in MongoDB.

Example request body:

{
	"accountId": 1001,
	"isAccountant": false,
	"prompt": "Map WA order types to QB products and classes.",
	"sourceData": { "items": [] },
	"targetData": { "QueryResponse": {} },
	"mapping": [
		{
			"WAFieldName": "Adult Membership",
			"QBProduct": "Adult Member",
			"QBProductId": "101",
			"QBClassification": "Membership"
		}
	]
}

Example success response:

{
	"ok": true,
	"insertedId": "...",
	"storedAt": "..."
}

Account Test Data

In development, the Vite middleware scans public/ and public/test-data/ for folders whose names end in digits. Those digits are treated as the account ID.

Example folder names:

1. Account 1001
2. Customer account 2004

For each matched folder, the middleware attempts to locate:

1. a WildApricot JSON file
2. a QuickBooks JSON file

These are then exposed through /dev-api/test-accounts so the UI can populate the account dropdown and auto-bind source and target endpoints.

Frontend Behavior

The UI supports:

1. Switching between file-based and endpoint-based JSON inputs.
2. Loading account-specific data automatically from discovered folders.
3. Parsing QuickBooks products from QueryResponse.Item data.
4. Preselecting product and class values from returned mapping data.
5. Adding custom product and class entries during confirmation.

MongoDB Persistence Model

Saved mapping records include fields such as:

1. accountId
2. isAccountant
3. prompt
4. sourceData
5. targetData
6. mapping
7. createdAt

An index is created on createdAt to support recent-history queries.

Troubleshooting

Mistral calls fail

Check:

1. MISTRAL_API_KEY is present in the environment.
2. The frontend is calling the dev or backend route, not Mistral directly.
3. The development server was restarted after env changes.

MongoDB is disconnected

Check:

1. MONGODB_URI is valid.
2. Your MongoDB server or Atlas cluster is reachable.
3. The backend logs for connection or TLS errors.

Account dropdown is empty

Check:

1. Test-data folders exist under public/ or public/test-data/.
2. Folder names end with digits so an account ID can be parsed.
3. Each folder contains JSON files for WildApricot and QuickBooks data.

Deployment Notes

For production:

1. Build the frontend with npm run build.
2. Serve dist/ from your static hosting layer.
3. Run the Express server separately for API and MongoDB operations.
4. Inject secrets through platform-managed environment variables.
5. Keep Mistral and MongoDB credentials server-side only.