calminer-docs/userguide/data_import_export_field_inventory.md

Data Import/Export Field Inventory

This inventory captures the current data fields involved in profitability, capex, and opex workflows. It consolidates inputs accepted by the calculation services, derived outputs that should be available for export, and persisted snapshot columns. The goal is to ground the upcoming CSV/Excel template design in authoritative field definitions.

Profitability

Calculation Inputs (schemas/calculations.py::ProfitabilityCalculationRequest)

Field	Type	Constraints & Notes
metal	str	Required; trimmed lowercase; ore/metal identifier.
ore_tonnage	PositiveFloat	Required; tonnes processed (> 0).
head_grade_pct	float	Required; 0 < value ≤ 100.
recovery_pct	float	Required; 0 < value ≤ 100.
payable_pct	float | None	Optional; 0 < value ≤ 100; overrides metadata default.
reference_price	PositiveFloat	Required; price per unit in base currency.
treatment_charge	float	≥ 0.
smelting_charge	float	≥ 0.
moisture_pct	float	≥ 0 and ≤ 100.
moisture_threshold_pct	float | None	Optional; ≥ 0 and ≤ 100.
moisture_penalty_per_pct	float | None	Optional; penalty per excess moisture %.
premiums	float	Monetary premium adjustments (can be negative).
fx_rate	PositiveFloat	Multiplier to convert to scenario currency; defaults to 1.
currency_code	str | None	Optional ISO 4217 code; uppercased.
opex	float	≥ 0; feeds cost aggregation.
sustaining_capex	float	≥ 0.
capex	float	≥ 0.
discount_rate	float | None	Optional; 0 ≤ value ≤ 100.
periods	int	Evaluation periods; 1 ≤ value ≤ 120.
impurities	List[ImpurityInput]	Optional; see table below.

Impurity Rows (schemas/calculations.py::ImpurityInput)

Field	Type	Constraints & Notes
name	str	Required; trimmed.
value	float | None	Optional; ≥ 0; measured in ppm.
threshold	float | None	Optional; ≥ 0.
penalty	float | None	Optional; penalty factor applied beyond threshold.

Derived Outputs (selected)

Structure	Fields
PricingResult (services/pricing.py)	metal, ore_tonnage, head_grade_pct, recovery_pct, payable_metal_tonnes, reference_price, gross_revenue, moisture_penalty, impurity_penalty, treatment_smelt_charges, premiums, net_revenue, currency.
ProfitabilityCosts	opex_total, sustaining_capex_total, capex.
ProfitabilityMetrics	npv, irr, payback_period, margin.
CashFlowEntry	period, revenue, opex, sustaining_capex, net.

Snapshot Columns (models/profitability_snapshot.py)

Field	Type	Notes
project_id / scenario_id	int	Foreign key; context.
created_by_id	int | None	Author reference.
calculation_source	str | None	Identifier for calculation run.
calculated_at	datetime	Server timestamp.
currency_code	str | None	ISO code.
npv	Numeric(18, 2)	Net present value.
irr_pct	Numeric(12, 6)	Internal rate of return (%).
payback_period_years	Numeric(12, 4)	Years to payback.
margin_pct	Numeric(12, 6)	Profit margin %.
revenue_total	Numeric(18, 2)	Total revenue.
opex_total	Numeric(18, 2)	Linked opex cost.
sustaining_capex_total	Numeric(18, 2)	Sustaining capital spend.
capex	Numeric(18, 2)	Capex input.
net_cash_flow_total	Numeric(18, 2)	Sum of discounted cash flows.
payload	JSON	Serialized detail (cash flow arrays, parameters).
created_at, updated_at	datetime	Audit timestamps.

Capex

Component Inputs (schemas/calculations.py::CapexComponentInput)

Field	Type	Constraints & Notes
id	int | None	Optional persistent identifier; ≥ 1.
name	str	Required; trimmed.
category	str	Required; trimmed lowercase; category slug.
amount	float	Required; ≥ 0; nominal value.
currency	str | None	Optional ISO 4217 code; uppercased.
spend_year	int | None	Optional; 0 ≤ value ≤ 120; relative spend year.
notes	str | None	Optional; max length 500.

Capex Parameters & Options

Structure	Field	Type	Notes
CapexParameters	currency_code	str | None	Optional ISO code; uppercased.
	contingency_pct	float | None	0 ≤ value ≤ 100.
	discount_rate_pct	float | None	0 ≤ value ≤ 100.
	evaluation_horizon_years	int | None	1 ≤ value ≤ 100.
CapexCalculationOptions	persist	bool	Persist snapshot when true.

Derived Outputs (CapexCalculationResult)

Segment	Fields
Totals (CapexTotals)	overall, contingency_pct, contingency_amount, with_contingency, by_category[].
Timeline (CapexTimelineEntry)	year, spend, cumulative.
Echoed Inputs	Normalized components, resolved parameters, options, currency.

Snapshot Columns (models/capex_snapshot.py)

Field	Type	Notes
project_id / scenario_id	int	Foreign key; context.
created_by_id	int | None	Author reference.
calculation_source	str | None	Origin tag.
calculated_at	datetime	Server timestamp.
currency_code	str | None	ISO code.
total_capex	Numeric(18, 2)	Sum of component spend.
contingency_pct	Numeric(12, 6)	Applied contingency %.
contingency_amount	Numeric(18, 2)	Monetary contingency.
total_with_contingency	Numeric(18, 2)	All-in capex.
component_count	int | None	Count of components.
payload	JSON	Serialized breakdown (components, timeline).
created_at, updated_at	datetime	Audit timestamps.

Opex

Component Inputs (schemas/calculations.py::OpexComponentInput)

Field	Type	Constraints & Notes
id	int | None	Optional persistent identifier; ≥ 1.
name	str	Required; trimmed.
category	str	Required; trimmed lowercase; category slug.
unit_cost	float	Required; ≥ 0; base currency.
quantity	float	Required; ≥ 0.
frequency	str	Required; trimmed lowercase; e.g. annual, monthly.
currency	str | None	Optional ISO 4217 code; uppercased.
period_start	int | None	Optional; ≥ 0; evaluation period index.
period_end	int | None	Optional; ≥ 0; must be ≥ period_start.
notes	str | None	Optional; max length 500.

Opex Parameters & Options

Structure	Field	Type	Notes
OpexParameters	currency_code	str | None	Optional ISO code; uppercased.
	escalation_pct	float | None	0 ≤ value ≤ 100.
	discount_rate_pct	float | None	0 ≤ value ≤ 100.
	evaluation_horizon_years	int | None	1 ≤ value ≤ 100.
	apply_escalation	bool	Toggle escalation usage.
OpexOptions	persist	bool	Persist snapshot when true.
	snapshot_notes	str | None	Optional; max length 500.

Derived Outputs (OpexCalculationResult)

Segment	Fields
Totals (OpexTotals)	overall_annual, escalated_total, escalation_pct, by_category[].
Timeline (OpexTimelineEntry)	period, base_cost, escalated_cost.
Metrics (OpexMetrics)	annual_average, cost_per_ton.
Echoed Inputs	Normalized components, parameters, options, resolved currency.

Snapshot Columns (models/opex_snapshot.py)

Field	Type	Notes
project_id / scenario_id	int	Foreign key; context.
created_by_id	int | None	Author reference.
calculation_source	str | None	Origin tag.
calculated_at	datetime	Server timestamp.
currency_code	str | None	ISO code.
overall_annual	Numeric(18, 2)	Annual recurring cost.
escalated_total	Numeric(18, 2)	Total cost over horizon with escalation.
annual_average	Numeric(18, 2)	Average annual spend.
evaluation_horizon_years	Integer	Horizon length.
escalation_pct	Numeric(12, 6)	Escalation %.
apply_escalation	Boolean	Flag used for calculation.
component_count	Integer | None	Component count.
payload	JSON	Serialized breakdown (components, timeline, metrics).
created_at, updated_at	datetime	Audit timestamps.

Unified CSV Template Specification

Overview

The CSV template consolidates profitability, capex, and opex data into a single file. Each row declares its purpose via a record_type flag so that parsers can route data to the appropriate service. The format is UTF-8 with a header row and uses commas as delimiters. Empty string cells are interpreted as null. Monetary values must not include currency symbols; decimals use a period.

Shared Columns

Column	Required	Applies To	Validation & Notes
record_type	Yes	All rows	Lowercase slug describing the row (see record types below).
project_code	No	All rows	Optional external project identifier; trimmed; max length 64.
scenario_code	No	All rows	Optional external scenario identifier; trimmed; max length 64.
sequence	No	Component and impurity rows	Optional positive integer governing ordering in UI exports.
notes	No	Component and parameter rows	Free-form text ≤ 500 chars; trimmed; mirrors request notes fields when present.

Record Types

profitability_input

Single row per scenario capturing the fields required by ProfitabilityCalculationRequest.

Column	Required	Validation Notes
metal	Yes	Lowercase slug; 1–32 chars.
ore_tonnage	Yes	Decimal > 0.
head_grade_pct	Yes	Decimal > 0 and ≤ 100.
recovery_pct	Yes	Decimal > 0 and ≤ 100.
payable_pct	No	Decimal > 0 and ≤ 100.
reference_price	Yes	Decimal > 0.
treatment_charge	No	Decimal ≥ 0.
smelting_charge	No	Decimal ≥ 0.
moisture_pct	No	Decimal ≥ 0 and ≤ 100.
moisture_threshold_pct	No	Decimal ≥ 0 and ≤ 100.
moisture_penalty_per_pct	No	Decimal; allow negative for credits.
premiums	No	Decimal; allow negative.
fx_rate	No	Decimal > 0; defaults to 1 when blank.
currency_code	No	ISO 4217 code; uppercase; length 3.
opex	No	Decimal ≥ 0.
sustaining_capex	No	Decimal ≥ 0.
capex	No	Decimal ≥ 0.
discount_rate	No	Decimal ≥ 0 and ≤ 100.
periods	No	Integer 1–120.

profitability_impurity

Multiple rows permitted per scenario; maps to ImpurityInput.

Column	Required	Validation Notes
name	Yes	Trimmed string; 1–64 chars.
value	No	Decimal ≥ 0.
threshold	No	Decimal ≥ 0.
penalty	No	Decimal; can be negative for credits.

capex_component

One row per component feeding CapexComponentInput.

Column	Required	Validation Notes
component_id	No	Integer ≥ 1; references existing record for updates.
name	Yes	Trimmed string; 1–128 chars.
category	Yes	Lowercase slug; matches allowed UI categories.
amount	Yes	Decimal ≥ 0.
currency	No	ISO 4217 code; uppercase; length 3.
spend_year	No	Integer 0–120.

capex_parameters

At most one row per scenario; populates CapexParameters and CapexCalculationOptions.

Column	Required	Validation Notes
currency_code	No	ISO 4217 code; uppercase.
contingency_pct	No	Decimal ≥ 0 and ≤ 100.
discount_rate_pct	No	Decimal ≥ 0 and ≤ 100.
evaluation_horizon_years	No	Integer 1–100.
persist_snapshot	No	true/false case-insensitive; defaults false.

opex_component

Maps to OpexComponentInput; multiple rows allowed.

Column	Required	Validation Notes
component_id	No	Integer ≥ 1; references existing record for updates.
name	Yes	Trimmed string; 1–128 chars.
category	Yes	Lowercase slug; matches enum used in UI (e.g. energy, labor).
unit_cost	Yes	Decimal ≥ 0.
quantity	Yes	Decimal ≥ 0.
frequency	Yes	Lowercase slug; allowed values: annual, monthly, quarterly, once.
currency	No	ISO 4217 code; uppercase; length 3.
period_start	No	Integer ≥ 0; must be ≤ period_end when provided.
period_end	No	Integer ≥ 0; defaults to period_start when blank.

opex_parameters

At most one row per scenario; maps to OpexParameters and options.

Column	Required	Validation Notes
currency_code	No	ISO 4217 code; uppercase.
escalation_pct	No	Decimal ≥ 0 and ≤ 100.
discount_rate_pct	No	Decimal ≥ 0 and ≤ 100.
evaluation_horizon_years	No	Integer 1–100.
apply_escalation	No	true/false; defaults true.
persist_snapshot	No	true/false; defaults false.
snapshot_notes	No	Free-form text ≤ 500 chars.

Validation Rules Summary

• Parsers must group rows by project_code + scenario_code; missing codes fall back to payload metadata supplied during import.
• record_type values outside the table must raise a validation error.
• Component identifiers (component_id) are optional for inserts but required to overwrite existing records.
• Decimal columns should accept up to two fractional places for currency-aligned fields (amount, overall, etc.) and up to six for percentage columns.
• Boolean columns accept true, false, 1, 0, yes, no (case-insensitive); exporters should emit true/false.

Excel Workbook Layout & Validation Rules

Workbook Structure

Sheet Name	Purpose
Summary	Capture project/scenario metadata and import scope details.
Profitability_Input	Tabular input for ProfitabilityCalculationRequest.
Profitability_Impurities	Optional impurity rows linked to a scenario.
Capex_Components	Component-level spend records for the capex planner.
Capex_Parameters	Global capex parameters/options for a scenario.
Opex	Component-level recurring cost records for opex.
Opex_Parameters	Global opex parameters/options for a scenario.
Lookups	Controlled vocabulary lists consumed by data validation (categories, booleans, etc.).

All sheets except Lookups start with a frozen header row and are formatted as Excel Tables (e.g., tbl_profitability). Tables enforce consistent column names and simplify import parsing.

Summary Sheet

Column	Required	Validation & Notes
import_version	Yes	Static text v1; used to detect template drift.
project_code	No	Matches shared project_code; max 64 chars; trimmed.
scenario_code	Yes	Identifier tying all sheets together; max 64 chars; duplicates allowed for batch.
prepared_by	No	Free-form text ≤ 128 chars.
prepared_on	No	Excel date; data validation restricts to dates ≥ TODAY()-365.
notes	No	Free-form text ≤ 500 chars; carry-over to import metadata.

Profitability_Input

Columns mirror the CSV specification; data validation rules apply per cell:

• Numeric fields (ore_tonnage, reference_price, etc.) use decimal validation with explicit min/max aligned to service constraints.
• Percentage fields (head_grade_pct, discount_rate) use decimals with bounds (e.g., 0–100). Apply Data Validation → Decimal settings.
• currency_code validation references Lookups!$A:$A (ISO codes list).
• Table default rows include scenario code reference via structured formula: =[@Scenario_Code] autocompletes when set.

Profitability_Impurities

Column	Required	Validation & Notes
scenario_code	Yes	Drop-down referencing Summary!C:C; ensures linkage.
name	Yes	Text ≤ 64 chars; duplicates allowed.
value	No	Decimal ≥ 0.
threshold	No	Decimal ≥ 0.
penalty	No	Decimal; allow negatives.

Capex_Components

Column	Required	Validation & Notes
scenario_code	Yes	Drop-down referencing Summary!C:C.
component_id	No	Whole number ≥ 1; optional when inserting new records.
name	Yes	Text ≤ 128 chars.
category	Yes	Drop-down referencing Lookups!category_values.
amount	Yes	Decimal ≥ 0; formatted as currency (no symbol).
currency	No	Drop-down referencing Lookups!currency_codes.
spend_year	No	Whole number 0–120.
notes	No	Text ≤ 500 chars.

Capex_Parameters

Single-row table per scenario with structured references:

Column	Required	Validation & Notes
scenario_code	Yes	Drop-down referencing Summary!C:C.
currency_code	No	Drop-down referencing Lookups!currency_codes.
contingency_pct	No	Decimal 0–100 with two decimal places.
discount_rate_pct	No	Decimal 0–100.
evaluation_horizon_years	No	Whole number 1–100.
persist_snapshot	No	Drop-down referencing Lookups!boolean_values (True/False).
notes	No	Text ≤ 500 chars; maps to request options metadata.

Opex

Column	Required	Validation & Notes
scenario_code	Yes	Drop-down referencing Summary!C:C.
component_id	No	Whole number ≥ 1; optional for inserts.
name	Yes	Text ≤ 128 chars.
category	Yes	Drop-down referencing Lookups!opex_categories.
unit_cost	Yes	Decimal ≥ 0.
quantity	Yes	Decimal ≥ 0.
frequency	Yes	Drop-down referencing Lookups!frequency_values (annual, monthly, quarterly, once).
currency	No	Drop-down referencing Lookups!currency_codes.
period_start	No	Whole number ≥ 0; additional rule ensures period_end ≥ period_start via custom formula =IF(ISBLANK(H2),TRUE,H2<=I2).
period_end	No	Whole number ≥ 0.
notes	No	Text ≤ 500 chars.

Opex_Parameters

Column	Required	Validation & Notes
scenario_code	Yes	Drop-down referencing Summary!C:C.
currency_code	No	Drop-down referencing Lookups!currency_codes.
escalation_pct	No	Decimal 0–100 with up to two decimals.
discount_rate_pct	No	Decimal 0–100.
evaluation_horizon_years	No	Whole number 1–100.
apply_escalation	No	Drop-down referencing Lookups!boolean_values.
persist_snapshot	No	Drop-down referencing Lookups!boolean_values.
snapshot_notes	No	Text ≤ 500 chars.

Lookups Sheet

Contains named ranges used by validation rules:

Named Range	Column Contents
currency_codes	ISO 4217 codes supported by the platform.
category_values	Allowed capex categories (e.g., engineering).
opex_categories	Allowed opex categories (e.g., energy, labor).
frequency_values	annual, monthly, quarterly, once.
boolean_values	TRUE, FALSE.

The sheet is hidden by default to avoid accidental edits. Import logic should bundle the lookup dictionary alongside the workbook to verify user-supplied values.

Additional Validation Guidance

• Protect header rows to prevent renaming; enable Allow Users to Edit Ranges for data sections only.
• Apply conditional formatting to highlight missing required fields (ISBLANK) and out-of-range values.
• Provide data validation error messages explaining expected ranges to reduce back-and-forth with users.
• Recommend enabling the Excel Table totals row for quick sanity checks (sum of amounts, counts of components).

Next Use

The consolidated tables above provide the authoritative field inventory required to draft CSV column layouts and Excel worksheet structures. They also surface validation ranges and metadata that must be preserved during import/export.