npl-update-schema-doc

PROJ-SCHEMA.md — Maintenance Guide

Diagram Formats

Include both Mermaid and PlantUML ERD diagrams in docs/PROJ-SCHEMA.md. The summary file (docs/PROJ-SCHEMA.summary.md) uses Mermaid only (for brevity).

Mermaid Conventions

• Use ```mermaid fenced blocks with erDiagram
• Underscores replace parentheses in types (e.g. VARCHAR_255 not VARCHAR(255))
• Mark PK/FK after type
• Relationship cardinality: ||--o{ (one-to-many), ||--|| (one-to-one), }o--o{ (many-to-many)

PlantUML Conventions

• Use ```plantuml fenced blocks with @startuml / @enduml
• Use * prefix for NOT NULL columns
• Mark <<PK>> and <<FK>> inline
• Use -- separator between PK section and other columns
• Use skinparam linetype ortho for clean routing
• Group related tables with package "Domain Name" { } blocks

Mermaid ERD Template

erDiagram
    parent ||--o{ child : "has many"

    parent {
        UUID id PK
        VARCHAR_255 name
    }

    child {
        UUID id PK
        UUID parent_id FK
    }

PlantUML ERD Template

@startuml
!define TABLE(name) entity name <<(T,#FFAAAA)>>
skinparam linetype ortho

TABLE(parent) {
  * id : UUID <<PK>>
  --
  * name : VARCHAR(255)
}

TABLE(child) {
  * id : UUID <<PK>>
  --
  * parent_id : UUID <<FK>>
}

parent ||--o{ child : "has many"
@enduml

Timestamp Conventions

All tables use TIMESTAMP WITHOUT TIME ZONE with NOW() defaults. Application code manages UTC.

All tables use updated_at as the modification timestamp column. This is the standardized convention — do not use modified_at.

Purpose

docs/PROJ-SCHEMA.md provides a database schema reference with ERD diagrams, table details, and relationship documentation. It should remain the single source of truth for understanding the project's data model.

Structure

docs/
├── PROJ-SCHEMA.md          # Main schema reference (keep small)
├── PROJ-SCHEMA.summary.md  # Condensed quick-reference (Mermaid only)
└── schema/
    ├── npl-content.md      # Detailed breakdowns by domain
    ├── sessions.md
    └── ...