visual_dbml_code.md

B2B Pharma Marketplace DBML Code

This artifact contains the DBML (Database Markup Language) code representing our database schema. You can copy and paste this code directly into dbdiagram.io to view, edit, and export an interactive Entity-Relationship Diagram (ERD).

The raw DBML source file is also saved at: 👉 pharma_b2b.dbml

---

DBML Code Block

// DBML (Database Markup Language) for Chilean B2B Pharma Tender Marketplace
// Copy and paste this code into https://dbdiagram.io to visualize the database schema.

Project Chilean_B2B_Pharma_Tender_Marketplace {
  database_type: 'PostgreSQL'
  Note: 'Database schema for consolidating drugstore demand and laboratory bidding in Chile, including ISP registry references.'
}

// ---- ENUMS ----

enum org_type {
  laboratory
  importer
  drugstore
  distributor
}

enum contact_role {
  legal_representative
  general_manager
  commercial_manager
  marketing_manager
  kam_licitaciones
  pharmaceutical_chemist
}

enum tender_status {
  draft
  published
  closed
  under_review
  awarded
  cancelled
}

// ---- GEOGRAPHIC REFERENCE ----

Table regions {
  id int [pk, note: 'Official SUBDERE code (e.g. 13 for RM)']
  name varchar(100) [not null]
  roman_numeral varchar(10) [not null]
}

Table communes {
  id int [pk, note: 'Official SUBDERE code']
  region_id int [not null]
  name varchar(100) [not null]
}

// ---- COMPANY & PROFILE DIRECTORY ----

Table companies {
  id uuid [pk, default: `gen_random_uuid()`]
  rut varchar(12) [unique, not null, note: 'Chilean tax ID (e.g., 76.123.456-K)']
  legal_name varchar(255) [not null]
  trade_name varchar(255)
  registro_isp varchar(100) [unique, not null, note: 'ISP Establishment Registration Number (shared by both labs and pharmacies)']
  establishment_type varchar(100)
  company_type org_type [not null]
  phone varchar(50)
  is_active boolean [default: true]
  created_at timestamp
  updated_at timestamp
}

Table drugstore_profiles {
  company_id uuid [pk]
  sanitary_resolution_number varchar(100) [not null, note: 'Resolución Sanitaria issued by SEREMI de Salud']
  sanitary_resolution_date date
}

Table laboratory_profiles {
  company_id uuid [pk]
  product_categories "varchar[]" [note: 'Array of categories']
  is_retail_otc boolean [default: false]
  commercial_terms text
  geographic_coverage_type varchar(50) [default: 'nacional']
}

Table company_addresses {
  id uuid [pk, default: `gen_random_uuid()`]
  company_id uuid [not null]
  street_address text [not null]
  commune_id int [not null]
  is_billing_address boolean [default: false]
  is_shipping_address boolean [default: true]
}

Table company_contacts {
  id uuid [pk, default: `gen_random_uuid()`]
  company_id uuid [not null]
  role contact_role [not null]
  full_name varchar(255) [not null]
  email varchar(255) [not null]
  phone varchar(50)
  emails_updated_at timestamp
  created_at timestamp
}

Table company_documents {
  id uuid [pk, default: `gen_random_uuid()`]
  company_id uuid [not null]
  document_type varchar(100) [not null]
  file_url text [not null]
  uploaded_at timestamp
}

// ---- PRODUCT CATALOG (ISP) ----

Table product_skus {
  id uuid [pk, default: `gen_random_uuid()`]
  brand_name varchar(255) [not null]
  isp_registration_number varchar(50) [unique, not null, note: 'Reg ISP (e.g. F-1234/24)']
  active_ingredient varchar(255) [not null]
  concentration varchar(100) [not null]
  dosage_form varchar(100) [not null]
  presentation text [not null]
  created_at timestamp
}

// ---- BIDDING & TENDER ENGINE ----

Table tenders {
  id uuid [pk, default: `gen_random_uuid()`]
  title varchar(255) [not null]
  status tender_status [default: 'draft']
  distribution_method varchar(255) [not null]
  delivery_deadline_days int [not null]
  created_at timestamp
  published_at timestamp
  closed_at timestamp
  awarded_at timestamp
}

Table tender_coverage_regions {
  tender_id uuid [not null]
  region_id int [not null]
  
  indexes {
    (tender_id, region_id) [pk]
  }
}

Table tender_demands {
  id uuid [pk, default: `gen_random_uuid()`]
  tender_id uuid [not null]
  drugstore_id uuid [not null]
  product_sku_id uuid [not null]
  quantity_requested int [not null]
  min_adjudicable_quantity int [not null, default: 1]
  requested_expiry_date date
  pharmaceutical_contact_id uuid
  created_at timestamp
}

Table tender_bids {
  id uuid [pk, default: `gen_random_uuid()`]
  tender_id uuid [not null]
  bidder_id uuid [not null]
  product_sku_id uuid [not null]
  unit_price_offered decimal(15,2) [not null]
  quantity_offered int [not null]
  expiry_date_offered date [not null]
  delivery_lead_time_days int [not null]
  bid_status varchar(50) [default: 'submitted']
  submitted_at timestamp

  indexes {
    (tender_id, bidder_id, product_sku_id) [unique]
  }
}

// ---- RELATIONSHIPS ----

Ref: communes.region_id > regions.id
Ref: drugstore_profiles.company_id - companies.id [delete: cascade]
Ref: laboratory_profiles.company_id - companies.id [delete: cascade]
Ref: company_addresses.company_id > companies.id [delete: cascade]
Ref: company_addresses.commune_id > communes.id
Ref: company_contacts.company_id > companies.id [delete: cascade]
Ref: company_documents.company_id > companies.id [delete: cascade]
Ref: tender_coverage_regions.tender_id > tenders.id [delete: cascade]
Ref: tender_coverage_regions.region_id > regions.id
Ref: tender_demands.tender_id > tenders.id [delete: cascade]
Ref: tender_demands.drugstore_id > companies.id
Ref: tender_demands.product_sku_id > product_skus.id
Ref: tender_demands.pharmaceutical_contact_id > company_contacts.id
Ref: tender_bids.tender_id > tenders.id [delete: cascade]
Ref: tender_bids.bidder_id > companies.id
Ref: tender_bids.product_sku_id > product_skus.id