PlantUML ER diagram conventions

The ER diagram is generated, not hand-written. It's the canonical example in the project of the automated codegen route (see MASD for the routes architecture): codegen parses already-generated artefacts, derives a model from them, and renders new artefacts from that model.

The pipeline has three stages, all driven by projects/ores.codegen/plantuml_er_generate.sh:

Stage	Reads	Tool	Writes
Parse	projects/ores.sql/create/**/*.sql	projects/ores.codegen/src/plantuml_er_parse_sql.py	build/output/codegen/plantuml_er_model.json
Render	the intermediate JSON + a mustache template	projects/ores.codegen/src/plantuml_er_generate.py (pystache)	projects/ores.sql/modeling/ores_schema.puml
Diagram	the .puml	PlantUML (system plantuml, or plantuml.jar)

The intermediate model file is not committed and not literate. It lives under build/output/ (gitignored) because it is regenerated on every run and carries no information a human is expected to read or edit. The format is JSON for the same reason: a generated artefact doesn't benefit from org-mode literate prose, drawers, or block-paste mechanisms — those are reserved for the authored models humans maintain (under each component's modeling/ directory).

The .puml and .png are committed because reviewers and readers benefit from seeing schema-shape diffs in PRs and viewing the diagram without rebuilding.

Use the entity keyword with the appropriate stereotype:

Example:

entity accounts <<temporal>> #C6F0D8 {
    * id : uuid <<PK>>
    --
    * username : text <<unique>>
    * email : text <<unique>>
    * valid_from : timestamptz
    * valid_to : timestamptz
}

• * prefix marks NOT NULL fields.
• <<PK>> for primary keys, <<FK>> for foreign keys, <<unique>> for unique constraints.
• A -- separator divides the primary key block from the rest of the columns.

Group related entities into packages by domain:

A package looks like:

package "IAM (Identity & Access Management)" #E8F4FD {
    entity accounts <<temporal>> #C6F0D8 {
        ...
    }
}

Standard ER cardinality:

Always label the arrow with descriptive text:

accounts ||--o{ sessions : "creates"
roles ||--o{ role_permissions : "has"

Add a note next to any entity that benefits from explanation:

note right of accounts
User accounts with authentication credentials.
Supports optimistic locking via the version field.
end note

ORE Studio uses bitemporal data management. Most tables have:

• valid_from / valid_to — validity period (temporal support).
• version — optimistic locking.
• modified_by — audit trail.

Junction tables typically carry:

• assigned_by — who created the association.
• assigned_at — when the association was created.

SQL schema lives at projects/ores.sql/create/. Two file kinds:

• *_create.sql — table definitions with triggers.
• *_notify_trigger.sql — PostgreSQL NOTIFY triggers for change events.

Read these to confirm:

• Column types, constraints, primary key shape (incl. composite).
• Indexes (incl. partial indexes for "current" records).
• Temporal trigger presence.

• doc-add-er-diagram skill — drives the authoring.
• PlantUML class diagram conventions — sibling, for components.
• How do I generate PlantUML diagrams? — runs the build.
• ores.sql — the schema this diagram documents.