Benchmark → oxFlow Migration Map

Mapping of Benchmark Estimating entities, fields, and libraries to their oxFlow equivalents. Feeds:

Alpha rate-library migration (proposal A3.8 MUST) — one-time import of the resource library
Release full cutover (proposal C5.5 / C5.6 MUST) — migration of all active estimates, libraries, and historical data; Benchmark decommissioned

Status: 🟢 POPULATED — schema review complete against Benchmark_Editable.db (240 MB, 150 tables, 288 projects, 52K+ items, 8K+ resources).

Related docs: glossary.md · concept-map.md · workplan.md (Batch G)

Database details: SQLite 3.x, 150 tables, row counts: project (288), project_item (52,277), project_section (3,250), resource_library (8,135), resource_rate (40,670 with history), item_library (153 subitems), subcontractor_quote_item (83,528).

1. Benchmark schema overview

Benchmark’s schema is a mature, relational estimating database with ~150 tables. Below are the key entities relevant to oxFlow migration, organized by domain:

Project & Estimate containers

Benchmark Table	Rows	Primary Key	Purpose
project	288	project_id	Tender/Estimate container; 206 columns including cost, margin, spread, commercials, risk tracking
estimates	9	id	Editable estimate snapshots (newer schema); links to project via source_project_id
project_section	3,250	project_section_id	WBS/Heading structure; nests under project; tracks cost, duration, completion %
estimate_sections	267	id	Heading/section grouping in editable estimates (newer schema)

Line items & structure

Benchmark Table	Rows	Primary Key	Purpose
project_item	52,277	project_item_id	Line items; nests under project_section or parent project_item (self-referential); tracks qty, rate, cost, prod rate, is_overhead, is_subitem, provisional sum, rate-only flags
estimate_items	728	id	Line items in editable estimates (newer schema); flatter structure than project_item
item_variable	62,822	—	Variables within an Item’s Worksheet; manual definitions or calculations
project_variable	607	—	Project-level variables; mostly used for production rates and formulas

Resource library

Benchmark Table	Rows	Primary Key	Purpose
resource_library	8,135	resource_library_id	Resource master; labour, material, plant, subcontract; includes code, description, rate, unit, flagfall, cartage, plugged (placeholder) flag, carbon EPD, currency
resource_rate	40,670	resource_rate_id	Rate history table; tracks changes over time with region, effective date ranges, expiry dates (resource_rate_todate); all 8,134 unique resources have multiple historical rates
resource_type	2	resource_type_id	Resource classification: PLUG (placeholder), FIXED; NOTE: orthogonal to resource_category below
resource_category	5	resource_category_id	Resource domain: LABOUR, MATERIALS, PLANT, SUBCONTRACT, SUBITEM

Item library (Subitems / Recipes)

Benchmark Table	Rows	Primary Key	Purpose
item_library	153	item_library_id	Subitem/Recipe definitions; marked as subitem (Y/N); includes cost, rate, production rate, quantity, calculation, cost/rate ranges; 1 has prod_rate set
item_library_resource	1,002	itemlib_resource_id	Subitem composition; many-to-many join between subitem and resource; includes quantity, calculation, crew size, cost/qty ranges
item_library_structure	0	—	Unused in this DB — would support hierarchical subitems

Classifiers & codes

Benchmark Table	Rows	Primary Key	Purpose
activity	113	activity_id	Activity code (WBS classifier); e.g., “A-PG00 Management Staff”, “E-MS05 Risk”
cost_code	33	cost_code_id	Cost code classifier; e.g., “PL-EXCAVTE”, “PL-COMPACT”
resource_group	—	—	Grouping table for resources
item_group	—	—	Grouping table for items
unit	67	unit_id	Unit definitions; e.g., DAY, EACH, HOUR, m², m³, LS

Subcontracting

Benchmark Table	Rows	Primary Key	Purpose
subcontractor	185	subcontractor_id	Subcontractor company master; address, contact, phone, email, type, region, inactive flag
subcontractor_quote	542	subcontractor_quote_id	Quote/adjudication records; links project + subcontractor
subcontractor_quote_item	83,528	subconquote_item_id	Quote line items; rate per item, accepted/selected flags

Commercials & calculations

Benchmark Table	Rows	Primary Key	Purpose
project_resource	—	—	Join table linking resources into project items for estimate build-up
project_resource_prodrate	—	—	Production rate tracking per project resource

Audit & configuration

Benchmark Table	Rows	Primary Key	Purpose
resource_changes	3,048	—	Change log for resource updates
activity	113	activity_id	Also serves as audit log of activities performed
audit_data	—	—	General audit trail
configuration_ tables* (boolean, string, float, integer, text)	—	—	System configuration

Supporting reference data

Benchmark Table	Rows	Primary Key	Purpose
organisation	0	organisation_id	Company master (unused in this DB)
client	—	client_id	Client/company data (may be separate from organisation)
region, state, depot	—	—	Geographic & operational scope
currency	0	currency_id	Currency definitions (unused)
variable_library	561	—	Reusable variable definitions
risk_library, risk_register	0	—	Risk management (unused)

2. Entity mapping table

High-level Benchmark → oxFlow mapping

Benchmark Entity	oxFlow Entity	Cardinality	Notes
project	Tender	1:1	Benchmark “project” = oxFlow “Tender” (outermost container); 288 projects map to 288 Tenders
project	Estimate	1:M	One Benchmark project can have multiple estimate variants (via estimate_sections, separate estimate_items logic)
project_section	Heading	1:1	Heading structure; nests up to 5 levels as oxFlow allows
project_item	Item	1:1	Line items; self-nesting supported in both
item_variable	Variable	1:1	Worksheet variables (calculations, manual entries)
item_library	Recipe	1:1	Subitem ≈ Recipe; has internal Worksheet (composition of resources)
item_library_resource	Worksheet Resource	1:1	Resource line within a Worksheet
resource_library	Resource	1:1	Atom; lives in a Price Book post-migration
resource_rate	Price Book (archived)	1:M	Each historical rate window becomes a Price Book with a date-range scope; superseded ones archived. See §4.2.
activity	Code “Workcentre” (on Items)	1:1	Applied to Items. Matches Workbench’s Workcentre dimension.
cost_code	Code “Activity Code” (on Resources)	1:1	Applied to Resources. Matches Workbench’s Activity Code dimension.
subcontractor	Company (Supplier/Subcontractor role)	1:1	External organisation; already sync’d from Xero in oxFlow
subcontractor_quote	Subcontract Package Adjudication	1:1	Adjudication round; outputs system-generated Price Book
subcontractor_quote_item	Adjudication result	—	Becomes Resources of type Subcontract in Price Book post-award

Known concept mappings

Benchmark concept	oxFlow mapping	Migration handling
Section > Item > Resource hierarchy	Heading > Item > Worksheet Resource (via Resource in Price Book)	Direct tree mapping; drop depth constraint if needed
Subitem (crew/composition)	Recipe with Input Parameters + Output Unit	Item library ≈ Recipe. Output Unit sourced directly from Benchmark’s item unit field (typically defined in Benchmark).
Resource rate + history	Current rate on Resource + archived Price Books for history	Each Benchmark rate window becomes a Price Book with a date-range scope (see §4.2). Archived Price Books retain full history and remain queryable by the Estimate Review Assistant (C4.4).
Project cost calculation	Item cost roll-up + Commercials Rules	Direct; calculations in Worksheet Variables
Spread/margin mechanics	Rule (Percentage / Lump Sum)	Benchmark’s spread system → oxFlow Rules
Provisional sum flag	Item Type (Provisional Item) or flag	Set item type or Provisional flag during migration
Rate-only item	Item Type (Rate-Only Item)	Preserve via Item Type
Plugged (placeholder) rate	Plug Rate flag on Resource	Mark in Resource metadata; flagged by Anomaly Review
Production rate	Variable (within Worksheet)	Migrate to Worksheet Variable with origin = manual
Overhead/Indirect cost	Item Flag (Indirect Cost) OR Item Type	Capture is_overhead from project_item; map structurally if not marked
Cost code + Activity code (Benchmark naming)	Two separate Codes in oxFlow: `Activity Code` (Resources) ← Benchmark `cost_code`; `Workcentre` (Items) ← Benchmark `activity`.	Naming intentionally re-aligned with Workbench conventions.

3. Field-level mapping per entity

3.1 PROJECT → TENDER / ESTIMATE

Benchmark: project (288 rows)

Benchmark field	oxFlow field	Transformation	Notes
project_id	tender.id / estimate.id	Use as external reference; generate new oxFlow ID	Benchmark ID stored in audit
project_quote_no	tender.number	Direct map	E.g., “22-077 R0”
project_desc	tender.name	Trim if >255 chars	Description of opportunity
principal_client_id	tender.client (Company)	Join to client; resolve Xero sync	Client role on Company
project_location	tender.location	Direct map	Site location
project_date_tenderclose	tender.tender_due_date	Direct map	Tender deadline
project_start_date	tender.contract_start_date	Direct map	When work begins
project_cost	estimate.total_cost	Computed from items; snapshot for validation
project_profit, project_margin_percent, project_profit_overhead	rule.value	Create commercials Rules for margin/overhead	Sequence matters; add in order
project_indirectcost_markup	rule.value (Indirect Cost Rule)	Create as Rule type Percentage	Markup % on indirect costs
project_qty, project_unit_id	tender.quantity / tender.unit	Direct map if present	May be empty for many-item tenders
project_comments, project_notes	tender.notes	Direct map	Free text
project_status, project_bid_status	tender.status	Map: bid_status (1=Active, 2=Submitted, 3=Won, 4=Lost) → tender.status	Provisional mapping
project_approved_by, project_approval_date	Not mapped	Dropped; not in oxFlow	Admin notes log instead
project_is_template	Not migrated	Template projects excluded from migration	Flag for manual review post-cutover
project_winner_id, project_winner_price	Not mapped	Informational; archive if needed	Historical data, no active use

Benchmark: estimates (9 rows — newer schema)

Benchmark field	oxFlow field	Transformation	Notes
id	estimate.id	Use as external reference; generate new oxFlow ID
name	estimate.name	Direct map	Variant name, e.g., “Base”, “Alt A”
client	tender.client (from parent project)	Resolve via source_project_id
reference	estimate.number (or notes)	Store as estimate.notes if no dedicated field	Variant code
status	estimate.status	Map: “draft” → “In Progress”, “reviewed” → “Reviewed”

3.2 PROJECT_SECTION → HEADING

Benchmark: project_section (3,250 rows)

Benchmark field	oxFlow field	Transformation	Notes
project_section_id	heading.id	Generate new ID; store Benchmark ID in audit
project_id	heading → estimate (via parent project)	Join to parent project → estimate
project_section_number	heading.title	Direct; e.g., “01”, “02.1”	WBS number becomes title or separate code field
project_section_desc	heading.title (append to number if both needed)	Combine number + desc if display order important
project_section_overhead	Not directly mapped	May flag as Indirect Cost at item level instead	Overhead is a property of contained Items
project_section_cost, project_section_duration	Derived	Computed from contained items	No storage needed
project_section_actualcost, project_section_costtodate	Archived	Historical; not migrated to active oxFlow	Preserve in audit log
sort_order	heading.display_order	Preserve item order

Benchmark: estimate_sections (267 rows)

Benchmark field	oxFlow field	Transformation
id	heading.id	Generate new; store Benchmark ID
estimate_id	heading → estimate.id	Direct join
number	heading.title (or schedule_code equivalent)	Direct; e.g., “1”, “2”
description	heading.title	Direct
level	Depth calculated	Infer from parent chain; constrain to 5

3.3 PROJECT_ITEM → ITEM / WORKSHEET

Benchmark: project_item (52,277 rows)

Benchmark field	oxFlow field	Transformation	Notes
project_item_id	item.id	Generate new; store Benchmark ID
project_item_text	item.description	Direct; trim to 4000 chars if needed
project_item_qty	item.quantity	Direct; REAL → numeric
unit_id	item.unit (via unit table)	Map Benchmark unit_id → oxFlow Unit	All 67 Benchmark units must map to oxFlow Unit library
project_item_cost	Derived	Sum of Worksheet Resources × quantity
is_subitem	item.item_type	Map: ‘Y’ → sub-Item, ‘N’ → Item	self-nesting in Item
project_item_parent_id	item.parent (Item)	Self-reference to parent Item
project_item_isoverhead	item.item_flag (Indirect Cost)	‘Y’ → Indirect Cost flag = true	OR use Item Type (Indirect Item) if introducing
activity_id	item.code_option (Workcentre)	Applied to Items — maps to oxFlow’s “Workcentre” Code	Benchmark “Activity” → oxFlow “Workcentre” by design (Workbench alignment)
cost_code_id	Not applied to Items	—	Benchmark “Cost Code” is applied to Resources in oxFlow (Activity Code), not Items
project_item_prodrate, project_item_produnitid	variable.value / variable.unit	Create Variable(name=“prod_rate”, value=…, unit=…)	Variables live in Worksheet
project_item_isprovsum	item.item_type	’Y’ → Provisional Item type	Flag for review in Anomaly Dashboard
project_item_rateonly	item.item_type	’Y’ → Rate-Only Item type	Supply-only, no committed quantity
project_item_actualcost, project_item_costtodate	Archived	Not migrated; historical data	Keep in audit log
project_item_margin	rule.value (Item-specific Rule)	If set, create Rule scoped to this Item	Margin as Rule
project_item_noncalculation	Flag	Mark Item to not recalculate cost	Treated as fixed cost in oxFlow
project_item_calculation	worksheet.calculation_block	If present, migrate to Calculation Block inside Worksheet
project_item_spreadtype, project_item_isspread, project_item_donotspread	rule.scope	Spread type → Rule targeting	Classic Benchmark mechanics → oxFlow Rules
project_item_submitamount, project_item_submitrate	submission_value.value	Submission override if set	Client-facing amount post-Commercials
project_item_rateonly, project_item_rateonlyamount	item.plug_rate	If rateonly=‘Y’ and rateonlyamount is set, map to plug_rate (nullable number); otherwise null	Rate-plugged Item (direct entry, no Worksheet build-up)
project_item_inactive	item.item_flag (Inactive)	‘Y’ → Inactive flag	Soft-disable for history

Benchmark: estimate_items (728 rows)

Benchmark field	oxFlow field	Transformation	Notes
id	item.id	Generate new; store Benchmark ID
section_id	item.heading (parent Heading)	Join to estimate_sections → heading
description	item.description	Direct
quantity	item.quantity	Direct
unit	item.unit (string directly)	Map to oxFlow Unit library
is_client_item	item.item_type	’Y’ → Schedule Item, ‘N’ → Normal Item	Client-facing vs internal
is_priced	item.status	’Y’ → Priced, ‘N’ → unpriced (flag for review)

3.4 ITEM_LIBRARY → RECIPE

Benchmark: item_library (153 rows)

Benchmark field	oxFlow field	Transformation	Notes
item_library_id	recipe.id	Generate new; store Benchmark ID
item_library_issubitem	N/A	All rows are subitems (Y) or templates; treated as Recipes
unit_id	recipe.output_unit (via unit table)	Map Benchmark unit → oxFlow Unit	Declares what unit the Recipe outputs in
item_library_rate	Derived	Recipe’s Worksheet evaluates to a rate
item_library_prodrate, item_library_prodrate_unitid	variable (inside Recipe’s Worksheet)	Migrate as Variable	1 row has prod_rate set; most are 0
item_library_calculation	worksheet.calculation_block	Migrate as Calculation Block
item_library_qty, item_library_crewsize	input_parameter	If meaningful, create Input Parameter(name, unit, default_value)
cost_code_id, activity_id	resource.code (on child resources)	Propagate to Resource codes via item_library_resource join
item_library_code2, item_library_code3, item_library_code4	Extra codes	If multiple code dimensions, store all	Benchmark supports up to 4 codes

3.5 ITEM_LIBRARY_RESOURCE → WORKSHEET RESOURCE (inside Recipe)

Benchmark: item_library_resource (1,002 rows)

Benchmark field	oxFlow field	Transformation	Notes
itemlib_resource_id	worksheet_resource.id	Generate new
resource_id	worksheet_resource.resource (join to resource_library)	Direct; resolve to oxFlow Resource
itemlib_resource_qty	worksheet_resource.quantity	Direct; REAL → numeric or expression
itemlib_resource_calculation	worksheet_resource.quantity (if expression)	Store as expression if calculation present
itemlib_resource_crewsize	variable (Crew Size variable inside Recipe Worksheet)	Create as Variable if meaningful
itemlib_resource_prodrateid	variable (Production Rate variable)	Join to itemlib_resource_prodrate table
itemlib_resource_text	worksheet_resource.notes	Direct
range_id, distribution_type, cost/qty min/max	Discarded	Monte Carlo / uncertainty ranges not in scope for oxFlow v1	Not carried forward

3.6 RESOURCE_LIBRARY → RESOURCE (in Price Book)

Benchmark: resource_library (8,135 rows)

Benchmark field	oxFlow field	Transformation	Notes
resource_library_id	resource.id	Generate new; store Benchmark ID for audit
resource_library_code	resource.description (prefix) or code attribute	Benchmark code becomes Resource identifier	E.g., “Ti20H5”
resource_library_desc	resource.description	Full description; e.g., “Timber 200x50 H5”
resource_library_rate	resource.rate	Use current rate from resource_library; see resource_rate for history	Snapshot at migration
unit_id	resource.unit (via unit table)	Map Benchmark unit → oxFlow Unit	All resources must have Unit
resource_library_typeid	Not directly used	Benchmark types (PLUG, FIXED) are orthogonal; ignore	Use category instead
resource_category	resource.resource_type	Map: LABOUR → Labour, MATERIALS → Material, PLANT → Plant, SUBCONTRACT → Subcontract, SUBITEM → Recipe	SUBITEM goes to Recipes, not Resources
activity_id	Not applied to Resources	—	Benchmark “Activity” is applied to Items in oxFlow (as “Workcentre”), not Resources
cost_code_id	code_option (Activity Code) on Resource	Applied to Resources — maps to oxFlow’s “Activity Code” Code	Benchmark “Cost Code” → oxFlow “Activity Code” by design (Workbench alignment)
FLAGS → Flag Catalog
resource_library_isplugged	Flag: “Plug Rate“	‘Y’ → apply Plug Rate flag via Flag Catalog entry; flagged by Anomaly Review	Placeholder rates need review
resource_library_iscartage	Flag: “Cartage“	‘Y’ → apply Cartage flag	If cartage included in rate, document in Cartage Modifier instead
resource_library_isgst	Flag: “GST Applicable“	‘Y’ → apply flag; ‘N’ → not applied	Tax treatment flag
resource_library_iscurrency	Flag: “Multi-Currency“	‘Y’ → flag set; assumes AUD primary	Currency handling in v1
resource_library_isexchlocked	Flag: “Exchange Rate Locked“	‘Y’ → flag set	For locked FX rates
resource_library_donotinflate	Flag: “Do Not Inflate“	‘Y’ → flag set	Exclude from inflation adjustments
MODIFIERS → Modifier Catalog
resource_library_factor	Modifier: “Factor” (math: multiply)	Numeric multiplier; unit-agnostic	Scaling factor; default 1.0
resource_library_wastage	Modifier: “Wastage” (math: percentage add)	Percentage wastage/loss; e.g., 5%	Added to base cost as % increase
resource_library_flagfall	Modifier: “Flagfall” (math: add)	Fixed charge added to rate	E.g., callout fee, minimum charge
resource_library_mindistance	Modifier: “Minimum Distance” (math: add)	Distance-based minimum; unit: km	Cartage minimum threshold
resource_library_crewsize	Modifier: “Crew Size” (math: multiply)	Crew multiplier; e.g., 1.5 crew → 150% rate	Resource efficiency / crew scaling
DIRECT FIELDS
resource_library_notes	resource.notes	Direct
resource_library_code2, resource_library_code3, resource_library_code4	Extra codes	If multiple code dimensions, store in notes or create Code tags
currency_id	Currency handling	If multi-currency, store currency code on Resource; assume AUD default
subcontator_id	Not applicable	Subcontractor resources come from subcontractor_quote_item results	Don’t pre-link resources to subs
carbon EPD columns	resource.notes or separate flag	If carbon tracking needed, document in notes; can be added post-migration	15 carbon columns present
region_id, resource_library_regionscope	resource.categorization (optional)	If regionally scoped, tag with Region Categorization Option	Optional in v1

3.7 RESOURCE_RATE → Archived Price Books (historical rates via Price Book date scope)

Benchmark: resource_rate (40,670 rows)

Historical rates are migrated into oxFlow by grouping rates by their validity window and creating a Price Book per window with a date-range scope. Superseded Price Books are flagged as archived but remain queryable (useful for the Estimate Review Assistant, C4.4, and Historical Rate comparisons).

Benchmark field	oxFlow mapping	Transformation	Notes
resource_rate_id	resource.id (inside an archived Price Book)	Each rate window → a Resource entry in the corresponding archived Price Book
resource_library_id	resource.source_library_id	Pointer back to the canonical Resource ID so rate history can be walked per-resource
resource_rate	resource.rate	Direct
resource_rate_updated	price_book.scope_start	Earliest rate update in the window → start of Price Book scope
resource_rate_todate	price_book.scope_end	Expiry date → end of Price Book scope	When superseded, Price Book is flagged archived
region_id	price_book.scope_region (or Categorization on Resources within)	If regional scoping matters, applied at Price Book scope level

Archived Price Book semantics: read-only, not surfaced in the default Price Book list, queryable by Anomaly Review’s historical comparison layer (Release milestone) and by AI review assistant.

3.8 ACTIVITY → oxFlow Code “Workcentre” (on Items)

Benchmark: activity (113 rows)

Benchmark’s “Activity” table maps to oxFlow’s Workcentre Code, applied to Items. Naming deliberately aligned with Workbench conventions.

Benchmark field	oxFlow field	Transformation	Notes
activity_id	code_option.id	Generate new; store Benchmark ID
activity_desc	code_option.value	E.g., “A-PG00 Management Staff”	113 codes
—	code.name	”Workcentre”	Single Code named “Workcentre” with 113 options; scope: applied to Items

3.9 COST_CODE → oxFlow Code “Activity Code” (on Resources)

Benchmark: cost_code (33 rows)

Benchmark’s “Cost Code” table maps to oxFlow’s Activity Code Code, applied to Resources. Naming deliberately aligned with Workbench conventions.

Benchmark field	oxFlow field	Transformation	Notes
cost_code_id	code_option.id	Generate new; store Benchmark ID
cost_code_desc	code_option.value	E.g., “PL-EXCAVTE”	33 codes
—	code.name	”Activity Code”	Single Code named “Activity Code” with 33 options; scope: applied to Resources

3.10 UNIT → UNIT

Benchmark: unit (67 rows)

Benchmark field	oxFlow field	Transformation	Notes
unit_id	unit.id	Reuse Benchmark ID if no conflicts
unit_desc	unit.symbol or unit.display_name	Map: e.g., “DAY” → “day”, “EACH” → “ea”, “HOUR” → “hr”, “LS” → “LS”	oxFlow has built-in Units; may need to add custom ones

3.11 SUBCONTRACTOR → COMPANY

Benchmark: subcontractor (185 rows)

Benchmark field	oxFlow field	Transformation
subcontractor_id	company.id (already synced from Xero)	Match by name or create new; sync from Xero during cutover
subcontractor_company	company.name	Direct match with Xero sync
subcontractor_address, subcontractor_town, state_id, subcontractor_postcode	company.address	Map to Xero address fields
subcontractor_contact, subcontractor_phone, subcontractor_email	company contact info	Migrate if not in Xero; else Xero is source of truth
subcontractor_type_id	company.company_role (Subcontractor)	Ensure role is set to Subcontractor

3.12 SUBCONTRACTOR_QUOTE → SUBCONTRACT PACKAGE ADJUDICATION

Benchmark: subcontractor_quote (542 rows)

Benchmark field	oxFlow field	Transformation	Notes
subcontractor_quote_id	subcontract_package_adjudication.id	Generate new; store Benchmark ID
project_id	subcontract_package_adjudication.subcontract_package.estimate.id	Join to parent project → estimate
subcontractor_id	subcontract_package_adjudication.company	Link to Company with Subcontractor role
subcontractor_quote_timestamp	subcontract_package_adjudication.created_date	Direct
—	subcontract_package_adjudication.round_number	Extract from Benchmark; default to 1 if absent	Round of adjudication

3.13 SUBCONTRACTOR_QUOTE_ITEM → SUBCONTRACT PACKAGE ADJUDICATION RESULTS

Benchmark: subcontractor_quote_item (83,528 rows)

Benchmark field	oxFlow field	Transformation
subcontractor_quote_item_id	Adjudication working data	Transient; used to populate system-generated Price Book on award
project_item_id	subcontract_package_adjudication.item (within package scope)	Link back to Item being priced
subconquote_item_rate	Price Book Resource rate	On award, create Resource (Type=Subcontract) with this rate in system-generated Price Book
subconquote_item_accepted	Award indicator	If true, this subcontractor’s quote was accepted for this item
subconquote_item_selected	Selection flag	Marks selected quotes during adjudication

4. Lost concepts

Benchmark features with no oxFlow equivalent or intentionally dropped:

Benchmark concept	Why dropped	Migration handling	Fallback for users
Resource types (PLUG, FIXED)	Orthogonal to oxFlow’s Resource Type taxonomy; not needed	Drop; use resource_category instead	Resources migrate via category (Labour, Material, etc.); plug rates flagged separately
Spread types & spread mechanics	Replaced by oxFlow’s Rule system (more flexible)	Convert each spread to a Rule with appropriate scope (direct-only, percentage, etc.)	Rules provide more granular control; training needed on new mechanics
Uncertainty ranges (min/max/distribution)	Monte Carlo ranges not in oxFlow scope	Discarded — not carried forward	Feature consciously dropped; not on roadmap
Regional resource variants	oxFlow scopes via Categorization, not separate Resource copies	Merge regional variants into single Resource; use Categorization tags	Less granular than Benchmark; clarify acceptable precision loss with users
Resource rates with multiple currencies	oxFlow v1 assumes single currency per Price Book	Migrate to AUD; flag if currency conversions needed	Multi-currency Price Books deferred to post-launch; convert at migration time
Rate history (full audit trail)	Not lost	Historical rates migrated into archived Price Books (date-range scoped). Remain queryable — feeds Anomaly Review historical comparison and AI review assistant.	See §3.7
Cartage flagfall, mindistance, factor, wastage, crewsize	Not lost — mapped to Modifier Catalog	Migrated as Modifier Catalog entries with math operations (flagfall=add, mindistance=add, factor=multiply, wastage=percentage, crewsize=multiply). See §3.6 & §5.	Modifier Catalog provides explicit, auditable application.
Overhead/Indirect cost inheritance	oxFlow derives indirect cost structurally; Benchmark flag allowed override	Capture is_overhead in Item flag; recommend structural review	May need post-migration review of edge cases
Provisional sum amount override	oxFlow Item Types capture intent; amount is computed	Migrate provisional flag; preserve amount in notes for validation	Users can re-enter amounts if needed
Claim / contract cost tracking	Not in v1 scope	Archive claim_* tables; not migrated	Post-launch feature; can import later
Risk register & risk library	Unused in this Benchmark DB (0 rows)	Skip migration	Risk management in oxFlow deferred
Templates	Benchmark has template projects (is_template = Y); oxFlow v1 has no template mechanism	Flag for manual recreation; exclude from active migration	Admins can clone completed estimates as templates post-launch
Multi-estimate variants per project	Benchmark mixes project + estimates schemas; oxFlow unifies under Estimate	Flatten: migrate all estimate_items + estimate_sections per variant as separate Estimates under same Tender	One Tender → many Estimates (base, alt a, alt b, etc.)
Custom fields (custom_ tables)*	Benchmark has 10 custom field tables; oxFlow has no custom field framework in v1	Audit custom fields; migrate non-empty ones to notes/categorization; flag for admin review	v2 may add custom field support; data not lost
Variable library	561 pre-defined Variables; oxFlow Variables are per-Worksheet	Migrate commonly-used ones as Variable patterns in Recipes; rest as templates in notes	Users recreate custom Variables per Worksheet as needed
Project composition / BOM	project_composition table links projects (hierarchies)	Flatten to individual projects; preserve parent_proj_id in audit	No parent-child Tender concept in oxFlow v1

5. New concepts

oxFlow features with no Benchmark source; require defaults or admin setup:

oxFlow concept	Benchmark equivalent	Migration default	Post-migration admin setup
Recipe Output Unit (BR-030)	Benchmark item_library typically has a unit field (confirm during migration — likely `unit_id` or similar on the item_library table)	Direct map from Benchmark’s item unit field to oxFlow’s Output Unit	If the field is missing for a given Recipe, flag for admin review
Flag Catalog	Benchmark boolean/enum Resource fields (isplugged, iscartage, isgst, iscurrency, isexchlocked, donotinflate)	Locked: Benchmark Resource-level boolean/enum fields map to Flag Catalog entries. Admin creates catalog entries at migration using field names as seeds (e.g., “Plug Rate”, “Cartage”, “GST Applicable”). Flag values applied to Resources via catalog. Lead Estimator can curate flags post-migration.	See §3.6 Flag → Catalog mapping. All 6 boolean fields seeded as flags.
Modifier Catalog	Benchmark numeric Resource fields (factor, wastage, flagfall, mindistance, crewsize)	Locked: Benchmark numeric/additive fields map to Modifier Catalog entries with math operations (multiply, add, percentage add). Admin creates catalog at migration with field names and default operations (e.g., Factor=multiply, Flagfall=add, Wastage=percentage). Modifier values applied to Resources via catalog with math operation. Lead Estimator can curate modifiers post-migration.	See §3.6 Modifier → Catalog mapping. All 5 numeric fields seeded with math operation.
Codes (BR-125–128) — Workcentre + Activity Code	activity (113) + cost_code (33) tables exist in Benchmark	Locked: Benchmark `activity` → oxFlow Workcentre Code (applied to Items). Benchmark `cost_code` → oxFlow Activity Code Code (applied to Resources). Naming aligned with Workbench.	Two separate Code dimensions; see §3.8 / §3.9
Reference Rates (BR-144)	No Benchmark equivalent	None; admin creates post-migration	Required for Anomaly Review Layer 2; admin defines expected unit rates per category
Real-time collaboration	No explicit support in Benchmark	Not applicable; Benchmark is single-session	Users learn oxFlow’s explicit per-Item locking model
Anomaly Review layers 2 & 3	Benchmark has no anomaly dashboard	Layer 1 rules (deterministic) can be sourced from data (e.g., Plug Rate flags); Layers 2 & 3 are AI-driven	Admin populates Reference Rates for Layer 2 (rules-first); Layer 3 (AI) enabled in Release
Price Book Adjudication	Not a standalone concept in Benchmark (subadjudication only)	None; requires project data	Estimators learn to create adjudications for resource pricing rounds
Submission Value overrides	Benchmark project_item_submitamount field exists	Migrate if set; else compute from Commercials	Estimators can override final Submission Values per schedule item
Item Types (Schedule, Normal, Provisional, Rate-Only)	Implicit in item flags (is_client_item, is_priced, is_rateonly, is_provsum)	Map Benchmark flags → oxFlow Item Types	Admin & user training on when to use each type
Categorization (optional tagging)	Benchmark has resource_category, item_category	Optionally migrate as Categorization Options; flag for admin review	Admin defines Categorization taxonomies post-migration (e.g., trade, location, discipline)

6. Data cleaning & transformation rules

Unit mapping

Benchmark’s 67 units must map to oxFlow’s Unit library. Priority mapping:

Benchmark unit	oxFlow mapping	Notes
DAY	day	Built-in
HOUR	hr	Built-in
EACH, NO	ea	Built-in
m, m², m³	m, m², m³	Built-in
LS	LS	Built-in
KG, T	kg, t	Built-in
WK, MONTH	wk, mth	Custom add if missing
CARTAGE - KM	km (or cartage-specific)	Custom; map logic needed
others	Audit case-by-case	~67 total; bulk map to built-in where possible; document custom ones

Action: Audit Benchmark unit table; generate mapping script; test on sample data.

Resource duplicates & merging

Check for duplicate resources (same description, different rates):

SELECT resource_library_desc, COUNT(*) as cnt, COUNT(DISTINCT resource_library_rate) as rate_variants
FROM resource_library
GROUP BY resource_library_desc
HAVING cnt > 1

Action: For duplicates, pick the most recent rate (resource_library_dateupdate max); retire others. Document in audit log.

Stale / expired rates

All 40,670 resource_rate records have expiry dates (resource_rate_todate). Filter:

Rates with resource_rate_todate < today: ARCHIVE — do not migrate to active Price Book
Rates with resource_rate_todate >= today: MIGRATE

Threshold: Rates expiring within 30 days post-cutover should be reviewed and refreshed.

Plugged (placeholder) rates

1,307 resources marked with resource_library_isplugged = ‘Y’.

Action: Flag all Plug Rate Resources in Anomaly Review; require estimator confirmation before estimate submit.

Orphaned records

Orphaned resources: resource_library rows not used in any project_item (via project_resource join)
- Action: Migrate all; they’re part of the library. Track unused for potential clean-up.
Orphaned subitems: item_library rows not used in any project_item
- Action: Migrate; may be deprecated library items.

Inconsistent units

Detect Items where unit_id differs from contained Resource unit_id:

SELECT pi.project_item_id, pi.unit_id, pr.unit_id as res_unit_id
FROM project_item pi
JOIN project_resource pr ON pi.project_item_id = pr.project_item_id
WHERE pi.unit_id != pr.unit_id

Action: Flag for manual review; estimator must confirm conversion logic in Worksheet Variable.

Activity & Cost Code assignments

Resources not assigned activity_id or cost_code_id:

SELECT COUNT(*) FROM resource_library WHERE activity_id IS NULL
SELECT COUNT(*) FROM resource_library WHERE cost_code_id IS NULL

Action: Set sensible defaults or flag for admin assignment post-migration. Required by Workbench integration (BR-127).

Calculation expressions

10,072 project_items and 9 item_library rows have calculation expressions.

Action: Migrate to Worksheet Calculation Blocks; parse & validate syntax; flag non-standard formulas for review.

Variables

62,822 item_variables; 607 project_variables — mostly calculations and production rates.

Action: Migrate to Worksheet Variables with origin = manual (not sourced from Input Parameters or Program Tasks).

Provisional sums & rate-only items

1,272 items marked is_provsum = ‘Y’; 237 marked is_rateonly = ‘Y’.

Action: Map to Item Types; flag in Anomaly Review for pre-submission verification.

Subcontractor quote integration

83,528 quote items across 542 quotes; clarify which quotes are “active” (accepted/selected):

SELECT COUNT(*) FROM subcontractor_quote_item WHERE subconquote_item_accepted = 'Y'
SELECT COUNT(*) FROM subcontractor_quote_item WHERE subconquote_item_selected = 'Y'

Action: Only accept selected/accepted quotes during migration; others archived.

Margin & spread verification

39,369 items have spread set (is_spread = ‘Y’). Verify spread amounts match cost + margin:

SELECT project_item_id, project_item_cost, project_item_margin, 
       project_item_spreadamount
FROM project_item
WHERE ABS(project_item_spreadamount - (project_item_cost + project_item_margin)) > 0.01

Action: Reconcile; flag discrepancies for estimator review.

7. Cutover plan

7.1 Alpha rate-library migration

Scope: Benchmark Resource Library only → oxFlow Price Book (Internal type).

Steps:

Extract Benchmark resource library:
- Tables: resource_library (8,135 rows) + resource_type (2 rows) + resource_category (5 rows) + activity (113) + cost_code (33) + unit (67)
- Filter: Exclude inactive resources (resource_library_isActive != ‘N’ if present; else include all)
- Apply unit mapping rules (§6)
Transform to oxFlow structure:
- For each resource_library row, create oxFlow Resource:
  - description ← resource_library_code + ” ” + resource_library_desc (composite)
  - rate ← resource_library_rate
  - unit ← mapped unit (via unit_id)
  - resource_type ← resource_category (Labour / Material / Plant / Subcontract; SUBITEM → handled in recipes phase)
  - code_option (Activity Code) ← Benchmark cost_code (link via cost_code_id; required on Resources)
  - code_option (Workcentre) ← N/A on Resources (Workcentre is applied to Items, not Resources)
  - flags ← set Plug Rate if resource_library_isplugged = ‘Y’
  - notes ← resource_library_notes (preserve)
  - categorization ← resource_category_id (optional; for grouping)
Create oxFlow Price Book (Internal type):
- Name: “Benchmark Migration - [date]”
- Type: Internal (no supplier required)
- Source type: System-generated (lineage to migration)
- Add all transformed Resources
Validate:
- Row count: 8,135 Resources in Price Book
- Unit coverage: All 67 Benchmark units mapped; spot-check 10 Resources
- Code coverage: All non-null activity_ids assigned (required); cost_codes assigned where appropriate
- Plug Rate flags: 1,307 flagged; visible in admin UI
- Rate ranges: Min, max rates sanity-checked (e.g., labour > 0, plant > 0)
Admin review:
- Confirm Code assignments (Activity Code on Resources, Workcentre on Items)
- Flag ambiguous conversions (e.g., multi-activity resources → which code?)
- Review Output Units on migrated Recipes for correctness
Acceptance:
- Estimators in Alpha can drag Resources from Migrated Benchmark library into Worksheets
- Library behaves identically to a hand-curated Price Book
- Rate anomalies flagged by Anomaly Review

7.2 Release full migration

Scope: All remaining entities — active estimates, historical estimates, subitems/recipes, subcontract packages, variable definitions, commercials rules.