Rate Management

Rate management is mainly used to maintain language service provider rates and provide rate query interfaces.

Service Items

Service items represent the services that language service providers can offer. It contains two dimensions: one is the service item type, and the other is the language pairs that the service item can cover. Whether a language pair can be covered is determined by whether a rate has been configured for it.

Service Item Types

Currently, the system supports the following service item types:

• Translation (Translation)
• Machine Translation + Proofreading (MTPE) (not implemented in this phase)
• Expedited Service (Expedited Service) (not implemented in this phase)
• Management Fee (Management Fee)

Service items can be set as Required Service, indicating that it is a necessary additional service. If set as Required Service, it means that this service item will definitely be added when requesting service items through the pricing interface. When an order requests available suppliers (Workspaces), the system only considers whether the service items provided by the order can cover the service items of the workspace. During pricing, if the returned service item does not have a rate for the corresponding language pair, a value of 0 is used for calculation.

Scenario Simulation

When creating an order in the current system, a service item (Translation) is set by default. In the future, the selection of service items will be gradually opened up. At this point, the pricing service will use the Translation service item and the language pairs in the order to call the TMS to obtain suppliers that meet the criteria. The TMS service can determine the list of available suppliers based on which suppliers (Workspaces) have configured rates for the corresponding service items and language pairs. If a supplier has set Management Fee as a Required Service, then when the pricing service retrieves rates, it should add the Required Service rates on top of the Translation-related rates. In this case, there may be a situation where, because our service items are all associated with language pairs, but Required Service is not used as a filtering condition when selecting available suppliers, the rates for some target languages of the Required Service might not be found, in which case they are calculated as 0.

Therefore, the rate calculation method is as follows:

1. First calculate the Translation rates for each target language
2. Apply discount calculations
3. Add service fees according to the Required Service logic, for example, if the calculation unit for the management fee is a percentage of the total price, then after calculating each language pair, multiply by a percentage, which is the management fee.
4. Then display the Translation details in a table format, and the costs generated by other service items are displayed in a service item---price format.
5. Display the Total price

Pricing Units

• Word Count (Word)
• Hour (Hour)
• Fixed Price (Fixed)
• Total Price Percentage (Factors)

Currently, only word count and total price percentage are supported. For word count service items, the price is calculated as: price = word count * unit price * discount (or range price). For total price percentage service items, the price is calculated as: price = total price * percentage.

Database Values and Frontend Input Requirements for Pricing Units

• Word Count: Two decimal places floating point number, frontend 1.23
• Hour: Two decimal places floating point number, frontend 1.23
• Fixed Price: Two decimal places floating point number, frontend 1.23
• Total Price Percentage: Two decimal places floating point number, frontend 12%

Database Table Design

1. Service Items Table (service_items)

Used to manage the service items supported by language service providers.

CREATE TABLE tms_services (
  service_id VARCHAR(255) PRIMARY KEY,
  service_name VARCHAR(255) NOT NULL,
  status VARCHAR(255) NOT NULL,
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE service_items (
  service_item_id UUID PRIMARY KEY,
  service_id VARCHAR(255) NOT NULL,
  description TEXT,
  workspace_id UUID NOT NULL,  -- Associated with Workspace
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_workspace FOREIGN KEY (workspace_id) REFERENCES workspaces(workspace_id)
);

2. Pricing Units Table (pricing_units)

Used to maintain the units for calculating prices.

CREATE TABLE pricing_units (
  unit_id UUID PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3. Discount Range Table (discounts)

Used to maintain the discount rates corresponding to TM match percentages.

CREATE TABLE discounts (
  discount_id UUID PRIMARY KEY,
  match_percentage INT NOT NULL,
  discount_rate DECIMAL(5, 2) NOT NULL, -- For example: 0.10 represents a 10% discount
  workspace_id UUID NOT NULL,  -- Associated with Workspace
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_workspace FOREIGN KEY (workspace_id) REFERENCES workspaces(workspace_id)
);

4. Rates Table (rates)

Used to record the rate information for service items.

CREATE TABLE rates (
  rate_id UUID PRIMARY KEY,
  service_item_id UUID NOT NULL,
  source_language_id UUID NOT NULL,
  target_language_id UUID NOT NULL,
  pricing_unit_id UUID NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL,
  workspace_id UUID NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_service_item FOREIGN KEY (service_item_id) REFERENCES service_items(service_item_id),
  CONSTRAINT fk_source_language FOREIGN KEY (source_language_id) REFERENCES languages(language_id),
  CONSTRAINT fk_target_language FOREIGN KEY (target_language_id) REFERENCES languages(language_id),
  CONSTRAINT fk_pricing_unit FOREIGN KEY (pricing_unit_id) REFERENCES pricing_units(unit_id),
  CONSTRAINT fk_workspace FOREIGN KEY (workspace_id) REFERENCES workspaces(workspace_id)
);

4. Custom Rates Table (custom_rates)

Used to directly override the price for a single range.

CREATE TABLE custom_rates (
  custom_rate_id UUID PRIMARY KEY,
  service_item_id UUID NOT NULL,
  source_language_id UUID NOT NULL,
  target_language_id UUID NOT NULL,
  match_percentage_range INT NOT NULL, -- Match range
  pricing_unit_id UUID NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL,
  status VARCHAR(255) NOT NULL, -- Status: overwrite/not overwrite
  workspace_id UUID NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_service_item FOREIGN KEY (service_item_id) REFERENCES service_items(service_item_id),
  CONSTRAINT fk_source_language FOREIGN KEY (source_language_id) REFERENCES languages(language_id),
  CONSTRAINT fk_target_language FOREIGN KEY (target_language_id) REFERENCES languages(language_id),
  CONSTRAINT fk_pricing_unit FOREIGN KEY (pricing_unit_id) REFERENCES pricing_units(unit_id),
  CONSTRAINT fk_workspace FOREIGN KEY (workspace_id) REFERENCES workspaces(workspace_id)
);

API Interface Design

1. Service Item Management

1.1 Get Service Item List

• Method: GET
• URL: /api/v1/workspaces/{workspace_id}/service-items
• Response Example:

  {
    "code": 200,
    "message": "Service items retrieved successfully",
    "data": [
      {
        "service_item_id": "UUID",
        "name": "Translation",
        "description": "Translation service"
      }
    ]
  }

1.2 Create Service Item

• Method: POST
• URL: /api/v1/workspaces/{workspace_id}/service-items
• Request Body:

  {
    "name": "Translation",
    "description": "Translation service"
  }

1.3 Update Service Item

• Method: PUT
• URL: /api/v1/workspaces/{workspace_id}/service-items/{service_item_id}
• Request Body:

  {
    "name": "Translation",
    "description": "Translation service"
  }

1.4 Delete Service Item

• Method: DELETE
• URL: /api/v1/workspaces/{workspace_id}/service-items/{service_item_id}

2. Pricing Unit Management

2.1 Get Pricing Unit List

• Method: GET
• URL: /api/v1/workspaces/{workspace_id}/pricing-units
• Response Example:

  {
    "code": 200,
    "message": "Pricing units retrieved successfully",
    "data": [
      {
        "unit_id": "UUID",
        "name": "Per Word",
        "description": "Pricing per word"
      }
    ]
  }

2.2 Create Pricing Unit

• Method: POST
• URL: /api/v1/workspaces/{workspace_id}/pricing-units
• Request Body:

  {
    "name": "Per Word",
    "description": "Pricing per word"
  }

2.3 Update Pricing Unit

• Method: PUT
• URL: /api/v1/workspaces/{workspace_id}/pricing-units/{unit_id}
• Request Body:

  {
    "name": "Per Word",
    "description": "Pricing per word"
  }

2.4 Delete Pricing Unit

• Method: DELETE
• URL: /api/v1/workspaces/{workspace_id}/pricing-units/{unit_id}

3. Discount Range Management

3.1 Get Discount List

• Method: GET
• URL: /api/v1/workspaces/{workspace_id}/discounts
• Response Example:

  {
    "code": 200,
    "message": "Discounts retrieved successfully",
    "data": [
      {
        "discount_id": "UUID",
        "match_percentage": 100,
        "discount_rate": 0.10
      }
    ]
  }

3.2 Create Discount

• Method: POST
• URL: /api/v1/workspaces/{workspace_id}/discounts
• Request Body:

  {
    "match_percentage": 100,
    "discount_rate": 0.10
  }

3.3 Update Discount

• Method: PUT
• URL: /api/v1/workspaces/{workspace_id}/discounts/{discount_id}
• Request Body:

  {
    "match_percentage": 100,
    "discount_rate": 0.10
  }

3.4 Delete Discount

• Method: DELETE
• URL: /api/v1/workspaces/{workspace_id}/discounts/{discount_id}

4. Rate Management

4.1 Get Rate List

• Method: GET
• URL: /api/v1/workspaces/{workspace_id}/rates
• Response Example:

  {
    "code": 200,
    "message": "Rates retrieved successfully",
    "data": [
      {
        "rate_id": "UUID",
        "service_item_id": "UUID",
        "source_language_id": "UUID",
        "target_language_id": "UUID",
        "pricing_unit_id": "UUID",
        "unit_price": 0.15,
        "workspace_id": "UUID"
      }
    ]
  }

4.2 Create Rate

• Method: POST
• URL: /api/v1/workspaces/{workspace_id}/rates
• Request Body:

  {
    "service_item_id": "UUID",
    "source_language_id": "UUID",
    "target_language_id": "UUID",
    "pricing_unit_id": "UUID",
    "unit_price": 0.15
  }

4.3 Update Rate

• Method: PUT
• URL: /api/v1/workspaces/{workspace_id}/rates/{rate_id}
• Request Body:

  {
    "service_item_id": "UUID",
    "source_language_id": "UUID",
    "target_language_id": "UUID",
    "pricing_unit_id": "UUID",
    "unit_price": 0.15
  }

4.4 Delete Rate

• Method: DELETE
• URL: /api/v1/workspaces/{workspace_id}/rates/{rate_id}

5. Price Table Query

• Method: GET
• URL: /api/v1/workspaces/{workspace_id}/price-table
• Query Parameters:
  • source_language_id: UUID (optional)
  • target_language_id: UUID (optional)
  • service_item_id: UUID (optional)
  • currency_code: string (optional, defaults to the workspace's default currency)
• Response Example:

  {
    "code": 200,
    "message": "Price table retrieved successfully",
    "data": {
      "price_table": [
        {
          "source_language": {
            "id": "UUID",
            "name": "English",
            "code": "en"
          },
          "target_language": {
            "id": "UUID",
            "name": "Chinese",
            "code": "zh"
          },
          "service_item": {
            "id": "UUID",
            "name": "Translation"
          },
          "pricing_unit": {
            "id": "UUID",
            "name": "Per Word"
          },
          "unit_price": 0.15,
        }
      ]
    }
  }