policy_admin_erd.html

<style>
#erd { padding: 8px 0; }
#erd svg { width: 100%; }
</style>
<h2 class="sr-only">Entity-relationship diagram for the mock legacy policy administration system, showing 15 tables and their relationships</h2>
<div id="erd"></div>
<script type="module">
import mermaid from 'https://esm.sh/mermaid@11/dist/mermaid.esm.min.mjs';
const dark = matchMedia('(prefers-color-scheme: dark)').matches;
await document.fonts.ready;
mermaid.initialize({
  startOnLoad: false,
  theme: 'base',
  fontFamily: '"Anthropic Sans", sans-serif',
  themeVariables: {
    darkMode: dark,
    fontSize: '12px',
    fontFamily: '"Anthropic Sans", sans-serif',
    lineColor: dark ? '#9c9a92' : '#73726c',
    textColor: dark ? '#c2c0b6' : '#3d3d3a',
    primaryColor: dark ? '#2a2836' : '#EEEDFE',
    primaryBorderColor: dark ? '#534AB7' : '#AFA9EC',
    primaryTextColor: dark ? '#c2c0b6' : '#26215C',
    secondaryColor: dark ? '#1a2630' : '#E1F5EE',
    secondaryBorderColor: dark ? '#0F6E56' : '#5DCAA5',
    tertiaryColor: dark ? '#2a1e18' : '#FAECE7',
    tertiaryBorderColor: dark ? '#993C1D' : '#D85A30',
  },
});
const diagram = `erDiagram
    POLICY {
        string POLICY_NO PK
        string PLAN_CODE FK
        string AGENT_CODE FK
        string BASIS_CODE FK
        string STATUS_CODE
        date ISSUE_DATE
        date MATURITY_DATE
        numeric SUM_ASSURED
        string PREM_MODE
        numeric PREM_AMOUNT
        date NEXT_PREM_DUE_DATE
        string CURRENCY_CODE
        int ISSUE_AGE
        string AGE_CALC_METHOD
    }
    LIFE {
        int LIFE_ID PK
        string POLICY_NO FK
        string LIFE_TYPE
        string FULL_NAME
        date DOB
        string GENDER
        string SMOKING_STATUS
        string ID_NO
        string UW_DECISION
        numeric EXTRA_MORTALITY
    }
    PRODUCT {
        string PLAN_CODE PK
        string PLAN_NAME
        string PRODUCT_TYPE
        string PARTICIPATING_FLAG
        int MIN_ENTRY_AGE
        int MAX_ENTRY_AGE
        date EFFECTIVE_DATE
        string STATUS
    }
    PREMIUM_RATE {
        int RATE_ID PK
        string PLAN_CODE FK
        string GENDER
        string SMOKING_STATUS
        int ISSUE_AGE
        int POLICY_TERM
        numeric RATE_PER_1000
        date EFFECTIVE_DATE
        date EXPIRY_DATE
    }
    BENEFIT {
        int BENEFIT_ID PK
        string POLICY_NO FK
        string BENEFIT_TYPE
        numeric BENEFIT_AMOUNT
        date COMMENCEMENT_DATE
        date EXPIRY_DATE
        string STATUS
    }
    PREMIUM_HISTORY {
        int PREM_ID PK
        string POLICY_NO FK
        date DUE_DATE
        date PAID_DATE
        numeric PREMIUM_AMOUNT
        numeric TOTAL_COLLECTED
        string RECEIPT_NO
        string STATUS
    }
    TRANSACTION {
        int TXN_ID PK
        string POLICY_NO FK
        string TXN_TYPE
        date TXN_DATE
        numeric AMOUNT
        string STATUS
    }
    POLICY_BONUS {
        int PB_ID PK
        string POLICY_NO FK
        string BONUS_TYPE
        int BONUS_YEAR
        numeric BONUS_AMOUNT
        date DECLARATION_DATE
    }
    BONUS_DECLARATION {
        int DECL_ID PK
        string PLAN_CODE FK
        string BONUS_TYPE
        int POLICY_YEAR
        numeric BONUS_RATE
        date DECLARATION_DATE
    }
    MORTALITY_TABLE {
        int ROW_ID PK
        string TABLE_CODE
        string GENDER
        int AGE
        string SMOKING_STATUS
        numeric QX
        date EFFECTIVE_DATE
        date EXPIRY_DATE
    }
    VALUATION_BASIS {
        string BASIS_CODE PK
        string BASIS_NAME
        string MORT_TABLE_CODE FK
        numeric VAL_INT_RATE
        date EFFECTIVE_DATE
        date EXPIRY_DATE
    }
    SYSTEM_PARAMETER {
        int PARAM_ID PK
        string PARAM_CATEGORY
        string PARAM_CODE
        string PARAM_VALUE
        string PARAM_TYPE
        date LAST_UPD_DATE
        string LAST_UPD_BY
    }
    SURRENDER_VALUE_FACTOR {
        int SV_ID PK
        string PLAN_CODE FK
        int POLICY_YEAR
        numeric SV_FACTOR
        date EFFECTIVE_DATE
        date EXPIRY_DATE
    }
    AGENT {
        string AGENT_CODE PK
        string AGENT_NAME
        string AGENCY_CODE
        string STATUS
        date APPOINTMENT_DATE
        date TERMINATION_DATE
    }
    REINSURANCE {
        int RI_ID PK
        string POLICY_NO FK
        string RI_TREATY_CODE
        numeric RI_SUM_AT_RISK
        numeric RI_PREMIUM
        date EFFECTIVE_DATE
    }
    POLICY }o--|| PRODUCT : "issued under"
    POLICY ||--o{ LIFE : "covers"
    POLICY ||--o{ BENEFIT : "provides"
    POLICY ||--o{ PREMIUM_HISTORY : "tracks"
    POLICY ||--o{ TRANSACTION : "generates"
    POLICY ||--o{ POLICY_BONUS : "accrues"
    POLICY ||--o{ REINSURANCE : "ceded via"
    POLICY }o--o| AGENT : "sold by"
    POLICY }o--o| VALUATION_BASIS : "valued under"
    PRODUCT ||--o{ PREMIUM_RATE : "priced by"
    PRODUCT ||--o{ BONUS_DECLARATION : "declares"
    PRODUCT ||--o{ SURRENDER_VALUE_FACTOR : "defines"
    VALUATION_BASIS }o--o| MORTALITY_TABLE : "uses"
`;
const { svg } = await mermaid.render('erd-svg', diagram);
document.getElementById('erd').innerHTML = svg;
document.querySelectorAll('#erd svg .node').forEach(node => {
  const firstPath = node.querySelector('path[d]');
  if (!firstPath) return;
  const d = firstPath.getAttribute('d');
  const nums = d.match(/-?[\d.]+/g)?.map(Number);
  if (!nums || nums.length < 8) return;
  const xs = [nums[0], nums[2], nums[4], nums[6]];
  const ys = [nums[1], nums[3], nums[5], nums[7]];
  const x = Math.min(...xs), y = Math.min(...ys);
  const w = Math.max(...xs) - x, h = Math.max(...ys) - y;
  const rect = document.createElementNS('http://www.w3.org/2000/svg', 'rect');
  rect.setAttribute('x', x); rect.setAttribute('y', y);
  rect.setAttribute('width', w); rect.setAttribute('height', h);
  rect.setAttribute('rx', '8');
  for (const a of ['fill', 'stroke', 'stroke-width', 'class', 'style']) {
    if (firstPath.hasAttribute(a)) rect.setAttribute(a, firstPath.getAttribute(a));
  }
  firstPath.replaceWith(rect);
});
document.querySelectorAll('#erd svg .row-rect-odd path, #erd svg .row-rect-even path').forEach(p => {
  p.setAttribute('stroke', 'none');
});
</script>