Open
Description
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/
- Base schema from
- 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