Quote data model

Audience: developers & AI agents · Scope: the tables behind a quote and its line items · Last reviewed: 2026-07-20

TL;DR — Four tables carry a quote: the quote, its positions (line items), each position's selections (configurator answers) and its materials (BOM). Deletes cascade down that chain. Several columns are named misleadingly — read the traps section before trusting a column name.

Tables use the #__ prefix placeholder. PHP mirrors are BcQuote and BcQuotePosition, both well annotated — though see the traps below, where the annotations are wrong.

#__configbox_external_user_quotes

Created as ..._user_projects and renamed later, which is why older code and some column names still say "project".

Group	Columns
Identity	id (the real one), serial (human, not unique — shared across revisions), name (project name)
Ownership	user_id — the user the quote is for. If a quote maker built it, this is still the agent, not the maker.
Grouping	opportunity_id → opportunities. Not crm_opportunity_id, which is unused legacy.
Revision	is_latest_revision, revision_number
State	status_name (CURRENT/EXPIRED), deleted, deleted_on
Dates (UTC)	created_on, modified_on, expires_on, last_viewed_on, quote_requested_on
Commercial	sales_manager_id, account_manager_id, override_currency_group_id, tax_rate, payment_terms, toc, notes
Display gates	can_show_price, show_commission_in_pdf
Search helpers	product_titles, configuration_codes — denormalised, rebuilt on change
Out of territory	eight oot_* columns including oot_status
Pipedrive	~20 pipedrive_* columns — see the gateway note

What is not on the quote

• No branch column. Branch is derived: quote.user_id → the user's custom_2 → the branches table. Yes — the branch id lives in a column called custom_2, a Kenedo custom user property.
• No customer column. The customer is the user at user_id.
• No currency column. Currency comes from override_currency_group_id if set, otherwise the owner's customer group. It throws if the group's currency doesn't resolve.
• No totals. See lifecycle & pricing.

⚠️ The user_id foreign key was dropped, so orphaned quotes (pointing at a user row that no longer exists) are possible. Code that derives branch, customer or currency from the owner must tolerate that.

#__configbox_external_user_positions

A line item. quote_id cascades on delete. parent_position_id points at itself for sub-positions.

Group	Columns
Links	id, quote_id, parent_position_id, discount_group_id, family_id
Kind flags	is_custom, is_alternative, is_setup_charge, has_custom_selection
Product	product_id (nullable — ON DELETE SET NULL), original_product_id, product_title, product_name_override, main_listing_id/_title, image fields
Configuration	configuration_code, configuration_code_override, description_override, type_code, position_notes, show_selections
Quantity / length	quantity, fixture_length_feet, fixture_length_feet_override
Pricing	unit_price_net (+ _override), volume_discount, unit_price_subtotal, discount_percent (+ _override), user_picked_discount_percent, user_picked_commission_percent, commission_mode, commission_percentage, overage_percent (+ _override)
Post-calc snapshot	six post_calc_* columns — BI only, nothing in PHP reads them
BOM / Infor	bom_status, bom_calc_date, bom_fetch_method, bom_is_exact_match, bom_match_percentage, bom_failure_keyword, bom_failure_message, bom_parameters, bom_overrides, bom_configuration_changed
Analytics	product/family GA4 name and variant fields
Audit	created_on, modified_on, row_updated_on, deleted, deleted_on

Sub-position fields (sub_position_type_name, cat_ref, code, question_id, answer_id) are only meaningful when parent_position_id is set. They are re-derived rather than copied when a quote is revised.

Setup charges are synthetic positions flagged is_setup_charge. They are hard-deleted and regenerated whenever pricing is recalculated, driven by a settings-configured list of code segments minus any the product ignores.

#__configbox_external_user_selections

One row per configurator answer on a position: question_id, question_title, selection, output_value, code_segment, metric/imperial values, unit_price_net, setup_price_net, and flags describing what the answer does (is_luminaire, is_qty_answer, is_catref_appender, is_pos_creator, …).

This is where a configured product actually lives. The position's configuration_code is assembled from these rows' code segments.

#__configbox_external_user_materials

The BOM for a position, fetched from Infor: position_id, item, pmt_code, qty, path. Cascades from the position. Failures are recorded separately in the CPQ BOM fails table, surfaced by the admin BOM Calc Reports screen.

Naming traps

These have bitten people. Read them before trusting a column name.

overage_percent is a foreign key, not a percentage

overage_percent and overage_percent_override are int unsigned foreign keys into the overages table. They began life as DECIMAL(5,2) and were converted to id columns without being renamed. The BcQuotePosition docblocks still describe them as percentages and are wrong. Code that sets "chosen_overage_percent = 1" is selecting the "No Overage" record by id, not a 1% overage.

crm_opportunity_id is not the opportunity link

opportunity_id is the real link. crm_opportunity_id is unused legacy that happens to carry a UNIQUE index.

custom_2 is the branch

On the users table, not the quote. There is no branch_id anywhere on a quote.

post_calc_* is not a pricing source

Six snapshot columns written by a cron for downstream BI. No PHP reads them. Do not treat them as the current price of anything — see lifecycle & pricing.

serial is not unique

By design, so revisions can share one. The UNIQUE index was deliberately dropped.

A BcQuote property is a column, for writes

KenedoDatabase::insertObject() builds its column list with get_object_vars() and includes NULL-valued declared properties. So a var $foo; on BcQuote with no matching column makes every quote INSERT (copy, revision) fail with Unknown column. When dropping a column, remove its BcQuote property in the same change; when adding one, declaring the property is what makes it persist.

Dropped columns

0.5.87 dropped four columns that no code read or wrote. Recorded here so they aren't re-added by mistake:

Column	Was
date_low_qty_popup	added by 0.3.69 next to date_low_qty_notification (which is used, by the low-quantity notification cron); never referenced, never held a value
oot_agency_contact_name / _email	part of the 0.5.38 OOT block, but the feature never captured or showed them; never even declared on BcQuote
pipedrive_agent_feedback_stage	the legacy free-text stage (0.5.44, defaulted by 0.5.45), superseded by the native pipedrive_stage_id in 0.5.65

Still obsolete but not dropped: crm_opportunity_id (unused since 0.0.12, still carries a UNIQUE index) and quote_requested_on (stamped by the quotation form, read by nothing).

Migrations

The schema was built up over ~120 migrations under data/customization/updates/. The ones worth knowing:

Migration	What
0.0.12	creates the quotes (as _user_projects), positions and selections tables
0.0.16	creates the serial-counter table
0.0.134 / 0.0.135 / 0.0.136	the big rename: projects → quotes, project_id → quote_id, and the counter table
0.0.140	the commercial block (managers, currency override, tax rate, terms, is_latest_revision)
0.0.143	drops the UNIQUE index on serial so revisions can share it
0.0.144	revision_number
0.0.178	expires_on + status_name re-added
0.2.10	drops the user_id foreign key
0.3.1 / 0.3.11	materials table; CPQ BOM fails
0.4.17	the six post_calc_* columns
0.4.49	is_alternative
0.5.38	the oot_* columns (ten; two dropped again by 0.5.87)
0.5.66	the stage-sync log table (its inline comments are the best reference for it)
0.5.87	drops four dead columns — see Dropped columns
0.3.78 onwards	the pipedrive_* blocks, added incrementally

0.3.38.php and 0.3.40.php are byte-identical duplicates; the second is a no-op only because of its tableFieldExists guard.

A full schema dump exists at .idea/queries/Query_5.sql. It is stale (pre-0.5.x) — useful for types and foreign keys, not for the current column set.

Related docs

• Overview · Lifecycle & pricing
• Migrations — how to add a column