Data model

"PREVOYANCE_TURING_PRODUCT_CHANGE" {
  shape: sql_table
  "id": UUID {constraint: primary_key}
  "account_id": UUID 
  "campaign_name": VARCHAR 
  "internal_description": TEXT 
  "internal_display_name": VARCHAR 
  "is_default": BOOLEAN 
  "name": VARCHAR 
  "next_product_id": VARCHAR 
  "price_increase": FLOAT 
  "product_id": VARCHAR 
  "product_name": VARCHAR 
  "targets": JSONB 
}
"TURING_PRODUCT_CHANGE" {
  shape: sql_table
  "id": UUID {constraint: primary_key}
  "account_id": UUID 
  "builder_product_id": INTEGER 
  "builder_product_version_id": INTEGER 
  "campaign_name": VARCHAR 
  "country": VARCHAR(2) 
  "internal_description": TEXT 
  "internal_display_name": VARCHAR 
  "is_default": BOOLEAN 
  "is_self_serve_eligible": BOOLEAN 
  "name": VARCHAR 
  "product_change_pricing_id": UUID 
  "product_id": VARCHAR 
  "product_name": VARCHAR 
}
"TURING_PRODUCT_CHANGE_COVERAGE_RULE" {
  shape: sql_table
  "id": UUID {constraint: primary_key}
  "bundle_choice_id": VARCHAR 
  "bundle_id": VARCHAR 
  "category_id": VARCHAR 
  "coverage_index": INTEGER 
  "coverage_type": VARCHAR 
  "eligibility_item_ids": ARRAY 
  "expression_type": VARCHAR 
  "guarantee_id": VARCHAR 
  "parameter_type": VARCHAR 
  "parameter_value": VARCHAR 
  "product_change_id": UUID 
}
"TURING_PRODUCT_CHANGE_PRICING" {
  shape: sql_table
  "id": UUID {constraint: primary_key}
  "membership_fee_ratio": FLOAT 
  "min_price_increase": FLOAT 
  "price_increase": FLOAT 
  "projected_loss_ratio": FLOAT 
}

Hold "Alt" / "Option" to enable pan & zoom

[!NOTE] The offer_builder doesn't yet follow the bootstrap pattern: mkdocs can't automatically generate the datamodel diagram, so for now we keep it here.

The datamodel has been generated using dbdiagram ⧉:

You can visualise/update/export the datamodel using dbdiagram ⧉ from the following:

click to expand DB diagram text

// Use DBML to define your database structure // Docs: https://dbml.dbdiagram.io/docs

Table SignatureCoverage {
id uuid [primary key]
ccn_id optional[int]
franchise_id optional[uuid]
coverage_level enum
note: "unique constraint [ccn_id, coverage_level]"
}

Table BuilderProduct {
id int [primary key]
company_id optional[int]
is_template bool
signature_coverage_id optional[uuid] [ref: - SignatureCoverage.id]
}

Table BuilderProductVersion {
id integer [primary key]
builder_product_id int [ref: > BuilderProduct.id]
version int
was_generated_by_algorithm bool
}

Table CoveragePricingConfiguration {
id uuid [primary key]
price_structure PriceStructureType
past_results_correction_factor float
coverage_level enum
}

Table BuilderTarget {
builder_product_version_id int [ref: > BuilderProductVersion.id]
id uuid
ape_code string
ccn_code string
postal_code string
professional_category enum
company_creation_year int
number_of_primaries int
note: "Can be company or industry"
}

Table BuilderCoverage {
builder_product_version_id int [ref: > BuilderProductVersion.id]
id int [primary key]
option_number optional[int]
pricing_configuration_id uuid [ref: > CoveragePricingConfiguration.id]
note: "unique constraint [builder_product_version_id, option_number]"
}

Table PricedCoverage {
id uuid [primary key]
// Config
builder_coverage_id int [ref: > BuilderCoverage.id]
builder_target_id Optional[uuid] [ref: > BuilderTarget.id]
pricing_configuration_id uuid [ref: > CoveragePricingConfiguration.id]
ccn_id Optional[uuid]
pricer_version enum
// Output
premiumness_rating Decimal
computed_demographics_id int [ref: - ComputedDemographics.id]
original_pure_premium Optional[JSONB]
note: "unique constraint [builder_coverage_id, offer_target_id, price_structure]"
}

Table ComputedDemographics {
id uuid [primary key]
average_age float
male_ratio float
target_population_ratios JSONB
non_paying_ratio float
partner_ratio float
child_ratio float
single_ratio float
first_child_ratio float
first_two_children_ratio float
}

Table BuilderPricing {
id int [primary key]
builder_coverage_id int [ref: - BuilderCoverage.id]
pricing_configuration_id uuid [ref: > CoveragePricingConfiguration.id]
effective_price property[Prices]
effective_price_alsace_moselle property[Prices]
effective_surco_price property[Prices]
}

Table BuilderPrice {
id uuid [primary key]
priced_coverage_id Optional[uuid] [ref: > PricedCoverage.id]
pricing_id Optional[uuid] [ref: > BuilderPricing.id]
is_alsace_moselle bool
primary_cents int
partner_cents int
child_cents int
family_cents int
membership_fee_ratio Decimal
}

Table HealthCoverageModule {
id uuid
builder_pricing_id uuid [ref: > BuilderPricing.id]
}