PlantUML ER diagram conventions

The schema outgrew one picture, so the diagram is one file per group and an index over them. A group is a package: its tables and the keys it holds inside itself. A key that leaves the group is named in the group's note, with the group it belongs to, because its other end is drawn in another file.

Item	Value
Index	projects/ores.sql/modeling/ores_schema.puml — every group, its file, and its tables
Group	projects/ores.sql/modeling/ores_schema.<group>.puml — one per package, committed
Drawn	and one per group, ores_schema.<group>.svg — committed
CMakeLists	projects/ores.sql/modeling/CMakeLists.txt

The .puml files hold what the diagram says and the SVG images are what a reader opens, so both are committed. The drawing is SVG because the schema has no raster budget left: the single 463-table diagram overflowed a 32-bit pixel count before it was split.

Below is the index; every group's image is on the ores.sql component page.

The groups are the model's packages. A package holds its tables and the keys that stay inside it; a key that leaves is named in the note beside the group it reaches. The model's packages list is what names them, so a group cannot drift from the schema.

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:

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 files are committed because reviewers and readers benefit from seeing schema-shape diffs in PRs. The images are committed because the documentation site renders them: an image link under proj: exports as an image tag pointing at the file in the repository, so an image that is not in the tree is a broken page.

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.

• compass-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.