prov/CLAUDE.md

CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

Project Overview

This is a Provisions-Tracker (Commission Tracker) application for managing employee commissions on telecommunications activations ("Schaltungen"). Built as a Node.js/Express backend with SQLite database and vanilla JavaScript frontend.

Purpose: Track commissions for telecom providers (Telekom, Vodafone, O2, 1&1), including tariffs, customer data, documents, and complex commission calculations.

Development Commands

Running the Application

# Production mode
npm start

# Development mode with auto-restart
npm run dev

# Install dependencies
npm install

The application runs on http://localhost:3000 by default (configurable via PORT environment variable).

Database

• SQLite database file: provision.db (auto-created on first run)
• Database initialization happens automatically via initDatabase() in database.js
• Backup file exists: provision.db.backup
• No migrations system - uses CREATE TABLE IF NOT EXISTS and ALTER TABLE with error handling

Architecture Overview

Three-Tier Structure

Backend (server.js - ~1987 lines)

• Express.js REST API with ~40+ endpoints
• Handles business logic, commission calculations, and database operations
• File upload handling via Multer (10MB limit)
• CORS enabled for development

Database (database.js - 288 lines)

• SQLite with 12+ tables
• Key tables: anbieter (providers), tarife (tariffs), schaltungen (connections/commissions), dokumente (documents)
• Complex commission system with multiple rule types (fixed/percentage)
• Includes soft-delete tracking (geloeschte_schaltungen)

Frontend (public/)

• Single-page app: index.html, script.js (~4000+ lines)
• Tab-based navigation (Erfassung, Übersicht, Provisionen, Admin, Gelöschte)
• Preloading strategy for fast tab switching
• Drag-and-drop file uploads

Key Database Tables and Relationships

anbieter (providers)
  ├─→ tarife (tariffs) - provision_euro, mitarbeiter_provision
  │    ├─→ provisionsregeln (tariff-specific commission rules)
  │    └─→ tarifoptionen_tarife (M2M link to options)
  ├─→ anbieter_provisionsregeln (global provider commission rules)
  └─→ mitarbeiter_prozentsaetze (employee percentage rates per provider)

schaltungen (main commissions table)
  ├─→ anbieter_id, tarif_id (foreign keys)
  ├─→ dokumente (uploaded documents)
  ├─→ schaltungen_tarifoptionen (M2M selected tariff options)
  └─→ hardware_ek, kundenverkaufspreis_netto (hardware pricing)

tarifoptionen_new (tariff options - new structure)
  └─→ tarifoptionen_tarife (M2M relationship to tarifs)

tarifoptionen (old structure - kept for migration compatibility)

Commission Calculation System

The app supports multiple commission calculation methods with complex fallback logic:

1. Tariff-specific rules (provisionsregeln table)

   • Fixed amount or percentage
   • Employee commission (verkaeufer_provision)

2. Global provider rules (anbieter_provisionsregeln table)

   • Applied when no tariff-specific rule exists

3. Direct tariff amounts (fallback)

   • tarife.provision_euro - company commission
   • tarife.mitarbeiter_provision - employee commission

4. Tariff options (tarifoptionen)

   • Additional commission adjustments per activation
   • Can be positive or negative (e.g., rebates)
   • Many-to-many relationship (options can apply to multiple tariffs)

API endpoint: GET /api/schaltungen/:id/provision - calculates total commission for a specific activation.

Migration Notes

The system includes an in-place migration from old tarifoptionen structure (one-to-many with tarifs) to new structure (many-to-many via tarifoptionen_new and tarifoptionen_tarife). Migration runs automatically on startup via migrateToNewTarifoptionen() in database.js:228-266.

API Endpoint Categories

Anbieter (Providers)

• GET /api/anbieter - List all providers
• POST /api/anbieter - Add provider
• DELETE /api/anbieter/:id - Delete provider (with dependency checks)
• GET /api/anbieter/:id/provisionsregeln - Get global commission rules
• POST /api/anbieter-provisionsregeln - Add global rule
• PUT /api/anbieter-provisionsregeln/:id - Update global rule

Tarife (Tariffs)

• GET /api/tarife - List all tariffs
• GET /api/tarife/:anbieter_id - List tariffs for provider
• GET /api/tarif/:id - Get single tariff
• POST /api/tarife - Add tariff
• PUT /api/tarife/:id - Update tariff
• DELETE /api/tarife/:id - Delete tariff (with dependency checks)
• GET /api/tarife/:id/provisionsregeln - Get tariff commission rules
• POST /api/provisionsregeln - Add tariff rule
• PUT /api/provisionsregeln/:id - Update tariff rule
• PUT /api/tarife/:id/mitarbeiter-provision - Update employee commission

Schaltungen (Commissions/Activations)

• GET /api/schaltungen - List all activations
• POST /api/schaltungen - Create activation
• PUT /api/schaltungen/:id - Update activation
• DELETE /api/schaltungen/:id - Delete activation (soft delete with reason tracking)
• GET /api/schaltungen/:id/provision - Calculate commission for activation
• GET /api/provisionen - Get commission overview/report
• GET /api/geloeschte-schaltungen - List deleted activations

Dokumente (Documents)

• POST /api/schaltungen/:id/dokumente - Upload document (multipart/form-data)
• GET /api/schaltungen/:id/dokumente - List documents for activation
• GET /api/dokumente/:id/download - Download document
• DELETE /api/dokumente/:id - Delete document (removes file from disk)

Tarifoptionen (Tariff Options)

• GET /api/tarifoptionen - List all options (old structure)
• GET /api/tarifoptionen/multi - List all options (new structure)
• POST /api/tarifoptionen/multi - Add option (new structure)
• PUT /api/tarifoptionen/multi/:id - Update option (new structure)
• DELETE /api/tarifoptionen/multi/:id - Delete option (new structure)
• GET /api/schaltungen/:id/tarifoptionen - Get options for activation
• POST /api/schaltungen/:id/tarifoptionen - Link option to activation
• DELETE /api/schaltungen/:schaltung_id/tarifoptionen/:tarifoption_id - Unlink option

Mitarbeiter (Employee Settings)

• GET /api/anbieter/:id/mitarbeiter-prozentsaetze - Get employee percentages for provider
• POST /api/mitarbeiter-prozentsaetze - Add percentage rate
• DELETE /api/mitarbeiter-prozentsaetze/:id - Delete percentage rate

Important Implementation Details

File Uploads

• Storage: uploads/ directory (created automatically)
• Naming: {timestamp}-{random}.{extension}
• Limit: 10MB per file
• Supported types: PDF, JPG, JPEG, PNG, DOC, DOCX (enforced client-side)
• Files are physically deleted when document is removed

Frontend State Management

• Preloading: App preloads all tab data in background after 1 second delay for faster navigation
• Tab switching: Uses cached data when available (showTab() checks if data exists)
• Global variables: currentSchaltungId, uploadedFiles
• Message system: Toast notifications via showMessage() overlay

Database Connection Pattern

Singleton Pattern - A single persistent database connection is used throughout the application lifecycle:

const db = getDatabase();  // Returns singleton instance
// ... do work ...
// NO db.close() - connection persists

• Connection is opened once on startup and reused
• Foreign keys are enabled after initialization (PRAGMA foreign_keys = ON)
• Busy timeout set to 3000ms for concurrent access handling
• Graceful shutdown closes connection on SIGINT/SIGTERM

Delete Operations

• Soft delete: Schaltungen are moved to geloeschte_schaltungen table with reason and timestamp
• CASCADE delete: Deleting a Schaltung automatically removes:
  • Associated documents from database (via ON DELETE CASCADE)
  • Document files from filesystem (handled in endpoint)
  • Linked tarifoptionen (via ON DELETE CASCADE)
• Hard delete: Individual documents physically removed from filesystem when deleted
• Dependency checks: Providers and Tariffs check for usage before deletion

Language

• UI/Database: German (Anbieter, Tarife, Schaltungen, etc.)
• Code: Mixed German/English
• Comments: Mostly English in code, German in UI strings

Testing Data

Default data seeded on first run:

• Providers: Telekom, Vodafone, O2, 1&1
• Tariffs: 10 example tariffs with commission amounts
• Employee percentages: 8-10% per provider
• Tariff options: 5 example options including rebates
• Global rules: Example O2 (fixed 20€) and 1&1 (12% percentage) rules

Common Development Patterns

Adding a New API Endpoint

1. Add route in server.js following existing patterns
2. Use getDatabase() to open connection
3. Always call db.close() in callbacks
4. Return JSON with proper error handling
5. Update frontend in script.js with corresponding API call

Adding a Database Table

1. Add CREATE TABLE IF NOT EXISTS in database.js initDatabase()
2. Add within db.serialize() block
3. Consider foreign key constraints
4. Add seed data with INSERT OR IGNORE if needed

Modifying Commission Logic

• Main calculation logic is in GET /api/schaltungen/:id/provision
• Fallback order: provisionsregeln → anbieter_provisionsregeln → direct tariff amounts
• Always recalculate when tariff/provider/options change
• Employee commission calculated separately from company commission