The Quoting Service uses PostgreSQL with the bidding database and public schema. The schema contains 15+ application tables organized around bids, pricing configurations, and AI-generated bids. Line items, assets, and conversations are stored as JSONB arrays. Migrations are managed by DbUp.
erDiagram
%% ==========================================
%% Core Bid Entity
%% ==========================================
bids {
UUID bid_id PK
TEXT title "Bid title"
SMALLINT status "Bid status enum"
INTEGER bid_request_authorization_number "Work order reference"
INTEGER service_provider_id "Bidding SP"
TEXT service_provider_bid_number "SP's bid number"
INTEGER facility_id "Target facility"
TEXT scope_of_work "Scope description"
TEXT exclusions "Exclusions"
TEXT warranty "Warranty terms"
TEXT notes "Additional notes"
SMALLINT lead_time_days_minimum "Min lead time"
SMALLINT lead_time_days_maximum "Max lead time"
NUMERIC_19_4 total_amount "Total bid amount"
NUMERIC_19_4 tax_amount "Tax amount"
DATE expiration_date "Bid expiration"
TIMESTAMPTZ creation_date "Created timestamp"
TIMESTAMPTZ last_updated_date "Last modified"
TEXT created_who "Creator name"
INTEGER created_person_id "Creator person ID"
SMALLINT created_persona "Creator persona"
TEXT last_updated_who "Last updater name"
INTEGER last_updated_person_id "Last updater ID"
SMALLINT last_updated_persona "Updater persona"
INT optimistic_concurrency_id "Concurrency control"
SMALLINT work_type "0=Repair, 1=Install/Replace, 2=Non-equipment"
SMALLINT bid_fairness "Fairness state"
NUMERIC_11_10 fairness_score "AI fairness score"
NUMERIC_11_10 fairness_confidence "AI confidence"
NUMERIC_19_4 predicted_price "AI predicted price"
SMALLINT prediction_method "0=Guidance, 1=Prediction"
UUID generated_bid_from_pdf_id FK "FK to generated_bids (nullable)"
BIT work_requires_permit "Permit required flag"
}
%% ==========================================
%% Bid Detail Tables (1:1)
%% ==========================================
bid_location_details {
UUID bid_id PK_FK "FK to bids"
TEXT address_line1 "Street address"
TEXT address_line2 "Address line 2"
TEXT city "City"
VARCHAR_10 state "State code"
VARCHAR_10 postal_code "ZIP/postal code"
}
bid_labor_lines {
UUID bid_id PK_FK "FK to bids"
JSONB_ARRAY line_data "Labor line items as JSONB[]"
}
bid_equipment_lines {
UUID bid_id PK_FK "FK to bids"
JSONB_ARRAY line_data "Equipment line items as JSONB[]"
}
bid_miscellaneous_lines {
UUID bid_id PK_FK "FK to bids"
JSONB_ARRAY line_data "Misc line items as JSONB[]"
}
bid_existing_assets {
UUID bid_id PK_FK "FK to bids"
JSONB_ARRAY asset_data "Existing assets as JSONB[]"
}
bid_conversations {
UUID bid_id PK_FK "FK to bids"
JSONB_ARRAY conversations "Conversation threads as JSONB[]"
}
bid_external_references {
UUID bid_id PK_FK "FK to bids"
TEXT_ARRAY external_references "CompositeIdentifier refs (GIN indexed)"
}
%% ==========================================
%% Bid Lifecycle Tables
%% ==========================================
bids_history {
UUID bid_id PK_FK "FK to bids"
BIGINT history_id PK "Sequence-generated"
TEXT event_type "Event classification"
JSONB event_data "Event payload"
TIMESTAMPTZ creation_date "Event timestamp"
TEXT created_who "Who triggered event"
}
bid_relationships {
UUID bid_id PK_FK "FK to bids"
SMALLINT bid_relationship_type PK_FK "FK to bid_relationship_types"
UUID related_bid_id "Related bid"
}
bid_relationship_types {
SMALLINT bid_relationship_type PK
TEXT relationship_type_name "e.g., SupersededBy"
BIT bidirectional "Is bidirectional"
}
bid_revalidation_history {
UUID bid_id PK_FK "FK to bids"
DATE expiration_date PK "New expiration"
TEXT approved_by "Approver name"
TEXT contact_method "How approved"
TIMESTAMPTZ creation_date "Revalidation timestamp"
TEXT created_who "Who revalidated"
INTEGER created_person_id "Revalidator ID"
SMALLINT created_persona "Revalidator persona"
}
bid_partner_perceived_fairness {
UUID bid_id PK_FK "FK to bids"
BIT is_fair "SP's fairness assessment"
TEXT submitted_who_name "Submitter name"
INT submitted_who_person_id "Submitter ID"
TIMESTAMPTZ last_updated_date "Last update"
}
%% ==========================================
%% Relationships
%% ==========================================
bids ||--o| bid_location_details : "bid_id"
bids ||--o| bid_labor_lines : "bid_id"
bids ||--o| bid_equipment_lines : "bid_id"
bids ||--o| bid_miscellaneous_lines : "bid_id"
bids ||--o| bid_existing_assets : "bid_id"
bids ||--o| bid_conversations : "bid_id"
bids ||--o| bid_external_references : "bid_id"
bids ||--o{ bids_history : "bid_id"
bids ||--o{ bid_relationships : "bid_id"
bids ||--o{ bid_revalidation_history : "bid_id"
bids ||--o| bid_partner_perceived_fairness : "bid_id"
bid_relationship_types ||--o{ bid_relationships : "bid_relationship_type"
erDiagram
generated_bids {
UUID generated_bid_from_pdf_id PK
SMALLINT pdf_to_bid_generation_status "0=Submitted, 1=Completed, 2=Failed"
UUID source_file_id "Files Service reference"
TEXT created_who "Creator name"
TEXT title "Generated title"
TEXT scope_of_work "Generated scope"
TEXT exclusions "Generated exclusions"
TEXT warranty "Generated warranty"
TEXT notes "Generated notes"
SMALLINT work_type "Work type classification"
JSONB_ARRAY labor_lines "Generated labor items"
JSONB_ARRAY equipment_lines "Generated equipment items"
JSONB_ARRAY miscellaneous_lines "Generated misc items"
TEXT bidding_contract_version "Contract version used"
INT soonest_days_before_start "Min lead time days"
INT latest_days_before_start "Max lead time days"
TEXT llm_chain_of_thought "AI reasoning trace"
}
generated_bid_external_references {
UUID generated_bid_from_pdf_id PK_FK "FK to generated_bids"
TEXT_ARRAY external_references "CompositeIdentifier refs"
}
generated_bids ||--o| generated_bid_external_references : "generated_bid_from_pdf_id"
bids ||--o| generated_bids : "generated_bid_from_pdf_id (optional FK)"
erDiagram
owner_pricing_configurations {
INT id PK "Sequence-generated"
INT owner_id "Customer domain owner"
SMALLINT service_id "Service filter (nullable=all)"
INT capability_id "Capability filter (nullable)"
SMALLINT pricing_modifier_type "0=Markup, 1=Margin"
DECIMAL pricing_modifier_value "Target/limit value"
BOOLEAN is_strict "Strict enforcement (limits only)"
BOOLEAN is_limit "TRUE=limit, FALSE=target"
}
chain_pricing_configurations {
INT id PK "Sequence-generated"
INT chain_id "Chain/organization"
SMALLINT service_id "Service filter (nullable=all)"
INT capability_id "Capability filter (nullable)"
SMALLINT pricing_modifier_type "0=Markup, 1=Margin"
DECIMAL pricing_modifier_value "Target/limit value"
BOOLEAN is_strict "Strict enforcement"
BOOLEAN is_limit "TRUE=limit, FALSE=target"
}
service_pricing_configurations {
SMALLINT service_id PK
SMALLINT pricing_modifier_type "0=Markup, 1=Margin"
DECIMAL pricing_modifier_value "Default value"
}
price_elasticity_v2_configurations {
INT id PK "Sequence-generated"
NUMERIC_19_4 min_quote_value "Range minimum"
NUMERIC_19_4 max_quote_value "Range maximum"
NUMERIC_19_4 target_margin "Target margin rate"
BOOLEAN is_d2q "Dispatch-to-quote flag"
TIMESTAMPTZ creation_date "Created"
TIMESTAMPTZ retired_date "Retired (nullable)"
INTEGER who_person_id "Creator ID"
TEXT who_person_name "Creator name"
}
price_elasticity_v2_adjustments {
INT id PK "Sequence-generated"
INTEGER configuration_id FK "FK to v2_configurations"
NUMERIC_19_4 margin_percent_added "Margin adjustment"
NUMERIC_19_4 weight "Probability weight"
TIMESTAMPTZ creation_date "Created"
TIMESTAMPTZ retired_date "Retired (nullable)"
INTEGER who_person_id "Creator ID"
TEXT who_person_name "Creator name"
}
margin_outcomes {
INT id PK "Sequence-generated"
INTEGER bid_request_auth_num "Work order reference"
UUID bid_id "Bid reference"
UUID quote_id "Quote reference (nullable)"
INTEGER quote_version "Quote version (nullable)"
NUMERIC_19_4 target_margin "Applied target margin"
NUMERIC_19_4 margin_adjustment "Applied adjustment"
NUMERIC_19_4 decided_margin "Final margin"
INTEGER margin_source "0=OwnerLimit..5=PriceElasticityV1"
TIMESTAMPTZ quoted_date "When quoted"
TIMESTAMPTZ creation_date "Record created"
INTEGER who_person_id "Creator ID"
TEXT who_person_name "Creator name"
}
price_elasticity_v2_configurations ||--o{ price_elasticity_v2_adjustments : "configuration_id"
bids — Central entity with full lifecycle tracking, fairness scores, AI predictions, and optimistic concurrency.bid_location_details — Physical location for the bid work (1:1).bid_labor_lines, bid_equipment_lines, bid_miscellaneous_lines — Line items as JSONB arrays (1:1).bid_existing_assets — Current assets at location as JSONB (1:1).bid_conversations — Threaded discussions as JSONB (1:1).bid_external_references — CompositeIdentifier links stored as TEXT arrays with GIN index (1:1).bids_history — Event-sourced audit trail with typed events and JSONB payloads (1:many).bid_relationships — Bid-to-bid links (e.g., SupersededBy) with type-based routing (1:many).bid_revalidation_history — Revalidation approvals with expiration date tracking (1:many).bid_partner_perceived_fairness — Service provider's fairness assessment (1:0..1).generated_bids — PDF-to-bid generation results with LLM chain-of-thought reasoning.generated_bid_external_references — External references for generated bids.owner_pricing_configurations — Per-owner markup/margin targets and limits.chain_pricing_configurations — Per-chain markup/margin targets and limits.service_pricing_configurations — Default per-service pricing.price_elasticity_v2_configurations — Margin targets by quote value range and D2Q flag.price_elasticity_v2_adjustments — Probability-weighted margin adjustments per configuration.margin_outcomes — Recorded margin decisions for analytics (source tracking: Owner/Chain/Service/Elasticity).bids_history stores typed events with JSONB payloads — an event sourcing pattern for complete audit reconstruction."domain~~type~~id" text format with GIN index for efficient array containment queries.optimistic_concurrency_id on bids prevents lost updates in concurrent modification scenarios.retired_date rather than physical deletion, preserving historical configuration data.margin_outcomes.margin_source records which pricing rule determined the final margin (OwnerLimit=0 through PriceElasticityV1=5).