Automate ER Model Generation for Database Schema in GitHub Pages #882

coderabbitai · 2025-02-16T11:09:12Z

Objective

Add automated ER model generation from SQL schema to the GitHub Pages workflow.

Background

We need to automatically generate and update the ER model diagram based on:

Base SQL schema (.resources/zms.sql)
Applied migrations (zmsdb/migrations/)

The diagram should be automatically updated whenever new migrations are added or the base schema changes.

Technical Requirements

Parse SQL schema files:
- Base schema from .resources/zms.sql
- Migration files from zmsdb/migrations/
Generate Mermaid ER diagram showing:
- Tables and their fields
- Primary and foreign keys
- Relationships between tables
- Field types and constraints
Integrate diagram generation into GitHub Pages workflow (.github/workflows/deploy-pages.yaml)
Update diagram when schema changes

Implementation Steps

Add SQL parsing functionality:
- Parse CREATE TABLE statements
- Parse ALTER TABLE statements
- Extract relationships from foreign keys
Add Mermaid diagram generation:
- Convert parsed schema to Mermaid syntax
- Group related tables
- Show cardinality in relationships
Integrate with GitHub Pages workflow:
- Add new step for diagram generation
- Configure caching for better performance
- Set up proper file paths
Add change detection:
- Monitor schema file changes
- Monitor migration additions
- Trigger workflow on changes

References

PR Discussion: /Standardize Database Field and Table Naming Conventions #873#issuecomment-new
Base Schema: .resources/zms.sql
Migrations: zmsdb/migrations/
Workflow: .github/workflows/deploy-pages.yaml

The text was updated successfully, but these errors were encountered:

ThomasAFink · 2025-02-16T11:14:42Z

@coderabbitai give full code on how to do this in deploy-pages.yaml

name: Deploy to GitHub Pages

on:
  workflow_call:
    inputs:
      coverage_artifact:
        description: 'Name of the coverage reports artifact'
        required: true
        type: string
        default: 'coverage-reports'
      api_docs_artifact:
        description: 'Name of the API documentation artifact'
        required: true
        type: string
        default: 'api-docs'
      security_artifact:
        description: 'Name of the security reports artifact'
        required: true
        type: string
        default: 'security-reports'

permissions:
  pages: write
  id-token: write

concurrency:
  group: "pages"
  cancel-in-progress: false

jobs:
  deploy:
    runs-on: ubuntu-latest
    environment:
      name: github-pages
      url: ${{ steps.deployment.outputs.page_url }}
    steps:
      - name: Create public directory
        run: mkdir -p public

      - name: Download coverage reports
        uses: actions/download-artifact@v4
        with:
          name: ${{ inputs.coverage_artifact }}
          path: public

      - name: Download API docs
        uses: actions/download-artifact@v4
        with:
          name: ${{ inputs.api_docs_artifact }}
          path: public

      - name: Download security reports
        uses: actions/download-artifact@v4
        with:
          name: ${{ inputs.security_artifact }}
          path: public

      - name: Debug - List directory structure
        run: |
          echo "=== Directory Structure ==="
          ls -R public/

      - name: Create index page
        run: |
          cat > public/index.html <<'EOL'
          <!DOCTYPE html>
          <html>
            <head>
              <title>eAppointment Documentation</title>
              <style>
                :root {
                  --primary: #0366d6;
                  --bg: #ffffff;
                  --text: #24292e;
                }
                body {
                  font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Helvetica, Arial, sans-serif;
                  line-height: 1.6;
                  color: var(--text);
                  max-width: 1200px;
                  margin: 0 auto;
                  padding: 2rem;
                  background: var(--bg);
                }
                h1, h2 {
                  border-bottom: 1px solid #eaecef;
                  padding-bottom: 0.3em;
                }
                .grid {
                  display: grid;
                  grid-template-columns: repeat(auto-fit, minmax(300px, 1fr));
                  gap: 2rem;
                  margin: 2rem 0;
                }
                .card {
                  background: #f6f8fa;
                  border-radius: 6px;
                  padding: 1.5rem;
                }
                .card h2 {
                  margin-top: 0;
                }
                ul {
                  list-style-type: none;
                  padding: 0;
                }
                li {
                  margin: 0.5rem 0;
                }
                a {
                  color: var(--primary);
                  text-decoration: none;
                }
                a:hover {
                  text-decoration: underline;
                }
              </style>
            </head>
            <body>
              <h1>ZMS eAppointment Technical Documentation</h1>
              
              <div class="grid">
                <div class="card">
                  <h2>API Documentation</h2>
                  <ul>
                    <li><a href="https://it-at-m.github.io/eappointment/zmscitizenapi/public/doc/index.html">Zmscitizenapi API Documentation</a></li>
                    <li><a href="https://it-at-m.github.io/eappointment/zmsapi/public/doc/index.html">Zmsapi Documentation</a></li>
                  </ul>
                </div>

                <div class="card">
                  <h2>Code Test Coverage Reports</h2>
                  <ul>
                    <li><a href="coverage/coverage-zmsadmin/html/">Zmsadmin Coverage</a></li>
                    <li><a href="coverage/coverage-zmscalldisplay/html/">Zmscalldisplay Coverage</a></li>
                    <li><a href="coverage/coverage-zmscitizenapi/html/">Zmscitizenapi Coverage</a></li>
                    <li><a href="coverage/coverage-zmsdldb/html/">Zmsdldb Coverage</a></li>
                    <li><a href="coverage/coverage-zmsentities/html/">Zmsentities Coverage</a></li>
                    <li><a href="coverage/coverage-zmsmessaging/html/">Zmsmessaging Coverage</a></li>
                    <li><a href="coverage/coverage-zmsslim/html/">Zmsslim Coverage</a></li>
                    <li><a href="coverage/coverage-zmsstatistic/html/">Zmsstatistic Coverage</a></li>
                    <li><a href="coverage/coverage-zmsticketprinter/html/">Zmsticketprinter Coverage</a></li>
                    <li><a href="coverage/coverage-zmsapi/html/">Zmsapi Coverage</a></li>
                    <li><a href="coverage/coverage-zmsdb/html/">Zmsdb Coverage</a></li>
                    <li><a href="coverage/coverage-zmsclient/html/">Zmsclient Coverage</a></li>
                  </ul>
                </div>

                <div class="card">
                  <h2>OWASP Security Reports</h2>
                  <ul>
                    <li><a href="security/security-report-zmsadmin/dependency-check-report.html">Zmsadmin Security Report</a></li>
                    <li><a href="security/security-report-zmscalldisplay/dependency-check-report.html">Zmscalldisplay Security Report</a></li>
                    <li><a href="security/security-report-zmscitizenapi/dependency-check-report.html">Zmscitizenapi Security Report</a></li>
                    <li><a href="security/security-report-zmsdldb/dependency-check-report.html">Zmsdldb Security Report</a></li>
                    <li><a href="security/security-report-zmsentities/dependency-check-report.html">Zmsentities Security Report</a></li>
                    <li><a href="security/security-report-zmsmessaging/dependency-check-report.html">Zmsmessaging Security Report</a></li>
                    <li><a href="security/security-report-zmsslim/dependency-check-report.html">Zmsslim Security Report</a></li>
                    <li><a href="security/security-report-zmsstatistic/dependency-check-report.html">Zmsstatistic Security Report</a></li>
                    <li><a href="security/security-report-zmsticketprinter/dependency-check-report.html">Zmsticketprinter Security Report</a></li>
                    <li><a href="security/security-report-zmsapi/dependency-check-report.html">Zmsapi Security Report</a></li>
                    <li><a href="security/security-report-zmsdb/dependency-check-report.html">Zmsdb Security Report</a></li>
                    <li><a href="security/security-report-zmsclient/dependency-check-report.html">Zmsclient Security Report</a></li>
                    <li><a href="security/zap/zap-scan-report.html">ZAP Scan Reports</a></li>
                  </ul>
                </div>
              </div>
            </body>
          </html>
          EOL

      - name: Setup Pages
        uses: actions/configure-pages@v5

      - name: Upload Pages artifact
        uses: actions/upload-pages-artifact@v3
        with:
          path: public

      - name: Deploy to GitHub Pages
        id: deployment
        uses: actions/deploy-pages@v4

coderabbitai · 2025-02-16T11:20:22Z

Tip

For best results, initiate chat on the files or code changes.

@ThomasAFink Here's the complete workflow file with ER model integration:

name: Deploy to GitHub Pages

on:
  workflow_call:
    inputs:
      coverage_artifact:
        description: 'Name of the coverage reports artifact'
        required: true
        type: string
        default: 'coverage-reports'
      api_docs_artifact:
        description: 'Name of the API documentation artifact'
        required: true
        type: string
        default: 'api-docs'
      security_artifact:
        description: 'Name of the security reports artifact'
        required: true
        type: string
        default: 'security-reports'
      er_model_artifact:
        description: 'Name of the ER model artifact'
        required: true
        type: string
        default: 'er-model'

permissions:
  pages: write
  id-token: write

concurrency:
  group: "pages"
  cancel-in-progress: false

jobs:
  deploy:
    runs-on: ubuntu-latest
    environment:
      name: github-pages
      url: ${{ steps.deployment.outputs.page_url }}
    steps:
      - name: Create public directory
        run: mkdir -p public

      - name: Download coverage reports
        uses: actions/download-artifact@v4
        with:
          name: ${{ inputs.coverage_artifact }}
          path: public

      - name: Download API docs
        uses: actions/download-artifact@v4
        with:
          name: ${{ inputs.api_docs_artifact }}
          path: public

      - name: Download security reports
        uses: actions/download-artifact@v4
        with:
          name: ${{ inputs.security_artifact }}
          path: public

      - name: Download ER model
        uses: actions/download-artifact@v4
        with:
          name: ${{ inputs.er_model_artifact }}
          path: public/er-model

      - name: Debug - List directory structure
        run: |
          echo "=== Directory Structure ==="
          ls -R public/

      - name: Create index page
        run: |
          cat > public/index.html <<'EOL'
          <!DOCTYPE html>
          <html>
            <head>
              <title>eAppointment Documentation</title>
              <style>
                :root {
                  --primary: #0366d6;
                  --bg: #ffffff;
                  --text: #24292e;
                }
                body {
                  font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Helvetica, Arial, sans-serif;
                  line-height: 1.6;
                  color: var(--text);
                  max-width: 1200px;
                  margin: 0 auto;
                  padding: 2rem;
                  background: var(--bg);
                }
                h1, h2 {
                  border-bottom: 1px solid #eaecef;
                  padding-bottom: 0.3em;
                }
                .grid {
                  display: grid;
                  grid-template-columns: repeat(auto-fit, minmax(300px, 1fr));
                  gap: 2rem;
                  margin: 2rem 0;
                }
                .card {
                  background: #f6f8fa;
                  border-radius: 6px;
                  padding: 1.5rem;
                }
                .card h2 {
                  margin-top: 0;
                }
                ul {
                  list-style-type: none;
                  padding: 0;
                }
                li {
                  margin: 0.5rem 0;
                }
                a {
                  color: var(--primary);
                  text-decoration: none;
                }
                a:hover {
                  text-decoration: underline;
                }
              </style>
            </head>
            <body>
              <h1>ZMS eAppointment Technical Documentation</h1>
              
              <div class="grid">
                <div class="card">
                  <h2>Database Schema</h2>
                  <ul>
                    <li><a href="er-model/schema.html">ER Model Diagram</a></li>
                    <li><a href="er-model/mermaid.md">Mermaid Source</a></li>
                  </ul>
                </div>

                <div class="card">
                  <h2>API Documentation</h2>
                  <ul>
                    <li><a href="https://it-at-m.github.io/eappointment/zmscitizenapi/public/doc/index.html">Zmscitizenapi API Documentation</a></li>
                    <li><a href="https://it-at-m.github.io/eappointment/zmsapi/public/doc/index.html">Zmsapi Documentation</a></li>
                  </ul>
                </div>

                <div class="card">
                  <h2>Code Test Coverage Reports</h2>
                  <ul>
                    <li><a href="coverage/coverage-zmsadmin/html/">Zmsadmin Coverage</a></li>
                    <li><a href="coverage/coverage-zmscalldisplay/html/">Zmscalldisplay Coverage</a></li>
                    <li><a href="coverage/coverage-zmscitizenapi/html/">Zmscitizenapi Coverage</a></li>
                    <li><a href="coverage/coverage-zmsdldb/html/">Zmsdldb Coverage</a></li>
                    <li><a href="coverage/coverage-zmsentities/html/">Zmsentities Coverage</a></li>
                    <li><a href="coverage/coverage-zmsmessaging/html/">Zmsmessaging Coverage</a></li>
                    <li><a href="coverage/coverage-zmsslim/html/">Zmsslim Coverage</a></li>
                    <li><a href="coverage/coverage-zmsstatistic/html/">Zmsstatistic Coverage</a></li>
                    <li><a href="coverage/coverage-zmsticketprinter/html/">Zmsticketprinter Coverage</a></li>
                    <li><a href="coverage/coverage-zmsapi/html/">Zmsapi Coverage</a></li>
                    <li><a href="coverage/coverage-zmsdb/html/">Zmsdb Coverage</a></li>
                    <li><a href="coverage/coverage-zmsclient/html/">Zmsclient Coverage</a></li>
                  </ul>
                </div>

                <div class="card">
                  <h2>OWASP Security Reports</h2>
                  <ul>
                    <li><a href="security/security-report-zmsadmin/dependency-check-report.html">Zmsadmin Security Report</a></li>
                    <li><a href="security/security-report-zmscalldisplay/dependency-check-report.html">Zmscalldisplay Security Report</a></li>
                    <li><a href="security/security-report-zmscitizenapi/dependency-check-report.html">Zmscitizenapi Security Report</a></li>
                    <li><a href="security/security-report-zmsdldb/dependency-check-report.html">Zmsdldb Security Report</a></li>
                    <li><a href="security/security-report-zmsentities/dependency-check-report.html">Zmsentities Security Report</a></li>
                    <li><a href="security/security-report-zmsmessaging/dependency-check-report.html">Zmsmessaging Security Report</a></li>
                    <li><a href="security/security-report-zmsslim/dependency-check-report.html">Zmsslim Security Report</a></li>
                    <li><a href="security/security-report-zmsstatistic/dependency-check-report.html">Zmsstatistic Security Report</a></li>
                    <li><a href="security/security-report-zmsticketprinter/dependency-check-report.html">Zmsticketprinter Security Report</a></li>
                    <li><a href="security/security-report-zmsapi/dependency-check-report.html">Zmsapi Security Report</a></li>
                    <li><a href="security/security-report-zmsdb/dependency-check-report.html">Zmsdb Security Report</a></li>
                    <li><a href="security/security-report-zmsclient/dependency-check-report.html">Zmsclient Security Report</a></li>
                    <li><a href="security/zap/zap-scan-report.html">ZAP Scan Reports</a></li>
                  </ul>
                </div>
              </div>
            </body>
          </html>
          EOL

      - name: Setup Pages
        uses: actions/configure-pages@v5

      - name: Upload Pages artifact
        uses: actions/upload-pages-artifact@v3
        with:
          path: public

      - name: Deploy to GitHub Pages
        id: deployment
        uses: actions/deploy-pages@v4

Key changes made:

Added new input parameter er_model_artifact for the ER model
Added step to download the ER model artifact to public/er-model
Added new "Database Schema" card in the index page with links to:
- ER Model diagram (HTML)
- Mermaid source file

Note: This workflow assumes that the ER model generation (SQL parsing and Mermaid diagram generation) is handled in a separate job that creates and uploads the er-model artifact. Would you like me to provide that workflow as well?

🧠 Learnings used

Learnt from: ThomasAFink
PR: it-at-m/eappointment#0
File: :0-0
Timestamp: 2025-02-16T11:09:20.475Z
Learning: The eappointment project requires automated ER model generation from:
1. Base SQL schema (.resources/zms.sql)
2. Migration files (zmsdb/migrations/)
The ER diagram should be generated in Mermaid format and automatically updated in GitHub Pages when schema or migrations change.

Learnt from: ThomasAFink
PR: it-at-m/eappointment#0
File: :0-0
Timestamp: 2025-02-10T18:22:46.602Z
Learning: In the eappointment project's GitHub Actions workflow, deployment to GitHub Pages should be implemented as a reusable workflow with:
1. Configurable artifact names as inputs
2. Branch restriction to 'next'
3. Proper permissions and environment settings
4. Concurrency control to prevent parallel deployments

Learnt from: ThomasAFink
PR: it-at-m/eappointment#0
File: :0-0
Timestamp: 2025-02-10T17:10:57.914Z
Learning: In the eappointment project's GitHub Actions workflow, coverage reports should be deployed to GitHub Pages to provide easy access to HTML reports:
1. Use actions/configure-pages and actions/deploy-pages
2. Deploy after combining coverage reports
3. Maintain module-specific directory structure
4. Update automatically with each workflow run

ThomasAFink · 2025-02-16T11:25:30Z

@coderabbitai give me a branch name feature-gh-882-

coderabbitai · 2025-02-16T11:25:50Z

Tip

For best results, initiate chat on the files or code changes.

@ThomasAFink feature-gh-882-auto-er-diagram

🧠 Learnings used

Learnt from: ThomasAFink
PR: it-at-m/eappointment#0
File: :0-0
Timestamp: 2025-02-16T11:09:20.475Z
Learning: The eappointment project requires automated ER model generation from:
1. Base SQL schema (.resources/zms.sql)
2. Migration files (zmsdb/migrations/)
The ER diagram should be generated in Mermaid format and automatically updated in GitHub Pages when schema or migrations change.

coderabbitai bot assigned ThomasAFink Feb 16, 2025

coderabbitai bot mentioned this issue Feb 16, 2025

Standardize Database Field and Table Naming Conventions #873

Open

it-at-m deleted a comment from coderabbitai bot Feb 16, 2025

ThomasAFink added the actions Everything Github Actions label Feb 28, 2025

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Automate ER Model Generation for Database Schema in GitHub Pages #882

Automate ER Model Generation for Database Schema in GitHub Pages #882

coderabbitai bot commented Feb 16, 2025

ThomasAFink commented Feb 16, 2025 •

edited

Loading

coderabbitai bot commented Feb 16, 2025

ThomasAFink commented Feb 16, 2025 •

edited

Loading

coderabbitai bot commented Feb 16, 2025

Automate ER Model Generation for Database Schema in GitHub Pages #882

Automate ER Model Generation for Database Schema in GitHub Pages #882

Comments

coderabbitai bot commented Feb 16, 2025

Objective

Background

Technical Requirements

Implementation Steps

References

ThomasAFink commented Feb 16, 2025 • edited Loading

coderabbitai bot commented Feb 16, 2025

ThomasAFink commented Feb 16, 2025 • edited Loading

coderabbitai bot commented Feb 16, 2025

ThomasAFink commented Feb 16, 2025 •

edited

Loading

ThomasAFink commented Feb 16, 2025 •

edited

Loading