PSA/ee/docs/plans/2025-12-05-service-catalog-currency-plan.md

Service Catalog Multi-Currency Pricing Plan

Date: 2025-12-05 (Revised: 2025-12-06) Author: Claude (billing pod) Status: In Progress Related: 2025-11-17-multi-currency-billing-plan.md

Problem Statement

The current multi-currency implementation has a design conflict:

1. Contract Templates have currency_code - intended to be inherited by contracts
2. Clients have default_currency_code - the client's preferred billing currency
3. Services in the service catalog have default_rate but no currency context
4. Services are added to Contract Templates - but rates are currency-ambiguous

Solution: Multi-Currency Pricing Per Service

Design Principles

1. Services support multiple currency/price pairs - One service can have prices in USD, EUR, GBP, etc.
2. Templates are currency-neutral - Templates define structure (services, billing frequency) but not currency
3. Contracts inherit currency from Client - The contract's currency comes from clients.default_currency_code
4. Validation at contract creation - If a service doesn't have a price in the required currency, show which services need updating
5. Simple default case - Single-currency usage should be effortless (just add one price)

Mental Model

SERVICE: "Managed Workstation"
  └── Prices:
      ├── USD: $150.00
      ├── EUR: €140.00
      └── GBP: £120.00

When creating a contract for a EUR client:
  → System looks up EUR price for each service
  → If any service lacks EUR price → Error listing services that need EUR pricing

Currency Flow

SERVICE CATALOG
  └── service_name: "Managed Workstation"
  └── service_prices (1:many)
      ├── { currency_code: "USD", rate: 15000 }
      ├── { currency_code: "EUR", rate: 14000 }
      └── { currency_code: "GBP", rate: 12000 }

CONTRACT TEMPLATE (currency-neutral)
  └── template_lines
      └── services (references service catalog)
  └── NO currency_code

CLIENT
  └── default_currency_code: "EUR"

CONTRACT (created from template for client)
  └── currency_code: "EUR" (inherited from client)
  └── VALIDATION: All referenced services must have a EUR price
      └── If missing → Error: "The following services need EUR pricing: [list]"

Implementation Plan

Phase 1: Database Schema Changes

1.1 Create service_prices table

CREATE TABLE service_prices (
  price_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant UUID NOT NULL REFERENCES tenants(tenant),
  service_id UUID NOT NULL REFERENCES service_catalog(service_id),
  currency_code CHAR(3) NOT NULL,
  rate INTEGER NOT NULL,  -- Amount in minor units (cents)
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  -- Each service can only have one price per currency
  UNIQUE(tenant, service_id, currency_code)
);

-- Index for lookups
CREATE INDEX idx_service_prices_service ON service_prices(service_id);
CREATE INDEX idx_service_prices_currency ON service_prices(currency_code);

1.2 Migrate existing default_rate data

-- Move existing rates to service_prices table (assume USD)
INSERT INTO service_prices (tenant, service_id, currency_code, rate)
SELECT tenant, service_id, 'USD', default_rate
FROM service_catalog
WHERE default_rate IS NOT NULL AND default_rate > 0;

1.3 Keep default_rate as convenience field (optional)

For backward compatibility and simple queries, we can keep default_rate on service_catalog as a denormalized "primary" rate. Alternatively, remove it entirely and always join to service_prices.

Decision: Keep default_rate for now as a convenience, but treat service_prices as the source of truth for multi-currency scenarios.

1.4 Remove currency_code from contract_templates

ALTER TABLE contract_templates DROP COLUMN IF EXISTS currency_code;

Phase 2: Type/Interface Updates

2.1 Create IServicePrice interface

interface IServicePrice {
  price_id: string;
  tenant: string;
  service_id: string;
  currency_code: string;
  rate: number;
  created_at: Date;
  updated_at: Date;
}

2.2 Update IService interface

interface IService {
  service_id: string;
  service_name: string;
  // ... existing fields
  default_rate: number;      // Convenience field (primary rate)
  prices?: IServicePrice[];  // All currency/rate pairs
}

2.3 Remove currency_code from IContractTemplate

Phase 3: Service Model Updates

3.1 Update service queries to include prices

// When fetching a service, optionally include all prices
async function getServiceWithPrices(serviceId: string): Promise<IService> {
  const service = await getService(serviceId);
  const prices = await knex('service_prices')
    .where({ service_id: serviceId });
  return { ...service, prices };
}

// Get price for specific currency
async function getServicePrice(serviceId: string, currencyCode: string): Promise<number | null> {
  const price = await knex('service_prices')
    .where({ service_id: serviceId, currency_code: currencyCode })
    .first();
  return price?.rate ?? null;
}

3.2 Create service price CRUD operations

async function setServicePrice(
  serviceId: string,
  currencyCode: string,
  rate: number
): Promise<IServicePrice> {
  // Upsert: insert or update if exists
}

async function removeServicePrice(
  serviceId: string,
  currencyCode: string
): Promise<void> {
  // Delete specific currency price
}

Phase 4: Service Catalog UI Updates

4.1 Design Goals

• Simple case is simple: Adding a single price should be as easy as before
• Multi-currency is accessible: Easy to add additional currency prices
• Clear display: Show all prices for a service at a glance

4.2 Service Form Updates

Option A: Inline Price List

Service Name: [Managed Workstation]
Service Type: [Recurring      v]

Pricing:
┌─────────────┬────────────────┬─────────┐
│ Currency    │ Rate           │         │
├─────────────┼────────────────┼─────────┤
│ USD      v  │ $150.00        │ [Remove]│
│ EUR      v  │ €140.00        │ [Remove]│
├─────────────┴────────────────┴─────────┤
│ [+ Add Currency]                       │
└────────────────────────────────────────┘

Option B: Primary + Additional

Service Name: [Managed Workstation]
Default Rate: [150.00] Currency: [USD v]

Additional Pricing (optional):
  EUR: [140.00]  [x]
  GBP: [120.00]  [x]
  [+ Add Currency]

Recommendation: Option A - Treats all currencies equally, cleaner mental model.

4.3 Service Catalog List View

Show primary currency with indicator if multiple currencies exist:

┌──────────────────────┬──────────┬───────────┬──────────┐
│ Service              │ Type     │ Rate      │ Actions  │
├──────────────────────┼──────────┼───────────┼──────────┤
│ Managed Workstation  │ Recurring│ $150 +2   │ Edit     │
│ Server Monitoring    │ Recurring│ $200      │ Edit     │
│ Consulting           │ Hourly   │ €175 +1   │ Edit     │
└──────────────────────┴──────────┴───────────┴──────────┘

"+2" indicates 2 additional currencies beyond the displayed one

Phase 5: Contract Creation Validation

5.1 Validation Logic

interface CurrencyValidationResult {
  valid: boolean;
  missingPrices: Array<{
    service_id: string;
    service_name: string;
    required_currency: string;
  }>;
}

function validateServicesHaveCurrency(
  services: IService[],
  requiredCurrency: string
): CurrencyValidationResult {
  const missing = services.filter(service => {
    const hasPrice = service.prices?.some(p => p.currency_code === requiredCurrency);
    return !hasPrice;
  });

  return {
    valid: missing.length === 0,
    missingPrices: missing.map(s => ({
      service_id: s.service_id,
      service_name: s.service_name,
      required_currency: requiredCurrency
    }))
  };
}

5.2 Error Message

When validation fails:

Cannot create contract in EUR. The following services do not have EUR pricing:

• Managed Workstation
• Server Monitoring
• Help Desk Support

Please add EUR prices to these services in the Service Catalog before creating this contract.

Phase 6: Testing

6.1 Unit Tests

• Test service price CRUD operations
• Test currency validation function
• Test getServicePrice returns correct currency

6.2 Integration Tests

• Test contract creation succeeds when all services have required currency
• Test contract creation fails with clear error when prices missing
• Test migration correctly moves existing rates to service_prices

Files to Modify

Database/Migrations

• 20251205130000_add_service_prices_table.cjs - Create service_prices, migrate data
• 20251205130001_remove_currency_from_contract_templates.cjs - Remove currency_code (keep as-is)

Types/Interfaces

• server/src/interfaces/billing.interfaces.ts - Add IServicePrice, update IService
• server/src/interfaces/contractTemplate.interfaces.ts - Remove currency_code (already done)

Server Models/Actions

• server/src/lib/models/service.ts - Add price queries
• server/src/lib/models/servicePrice.ts - New model for service_prices
• server/src/lib/actions/serviceActions.ts - Handle price CRUD
• server/src/lib/actions/contractWizardActions.ts - Update validation

UI Components

• server/src/components/settings/billing/ServiceCatalogManager.tsx - Multi-currency UI
• server/src/components/settings/billing/QuickAddService.tsx - Multi-currency support
• Service form component - Price list editor

Migration Strategy

Since this hasn't been deployed yet, we will:

1. Replace the existing migration 20251205130000_add_currency_to_service_catalog.cjs with new schema
2. Keep 20251205130001_remove_currency_from_contract_templates.cjs as-is
3. Update all code to use new service_prices model

Success Criteria

• Services can have multiple currency/price pairs
• Single-currency usage is simple (just add one price)
• Contract templates no longer have currency_code
• Contract creation validates services have required currency
• Clear error messages list services needing price updates
• UI displays all prices, easy to add/edit currencies
• Existing contracts continue to work (backward compatible)

Open Questions

1. Should we show available currencies in contract creation UI (filter services)?
2. Should we support "copy price from another currency" with exchange rate?
3. Should we track price history for auditing?