Building a Persistent Diagram Editor - Database Design

Our diagram editor was working well, but every refresh meant losing all progress. We needed:

• Automatic saving as users draw

• Loading the last diagram when they return

• Version history to track changes

• Preventing duplicate empty diagrams from cluttering the database

Database Schema Design We're using MongoDB with Prisma, so our schema looks like this:

1model Diagram {
2  id          String   @id @default(auto()) @map("_id") @db.ObjectId
3  userId      String   @db.ObjectId
4  user        User     @relation(fields: [userId], references: [id])
5  title       String   @default("Untitled Diagram")
6  description String?
7  data        Json     // Stores the full Excalidraw data
8  isPublic    Boolean  @default(false)
9  version     Int      @default(1)
10  createdAt   DateTime @default(now())
11  updatedAt   DateTime @updatedAt
12  versions    DiagramVersion[]
13}
14model DiagramVersion {
15  id        String   @id @default(auto()) @map("_id") @db.ObjectId
16  diagramId String   @db.ObjectId
17  diagram   Diagram  @relation(fields: [diagramId], references: [id])
18  data      Json     // Full state snapshot
19  version   Int
20  changeType String? // "create" | "update" | "delete"
21  changes    Json?   // Optional diff metadata
22  createdAt  DateTime @default(now())
23}

Key Design Decisions

Why JSON storage? Excalidraw diagrams contain complex nested structures (elements, app state, files). MongoDB's native JSON support makes this straightforward - we store everything as-is without complex relationships.

Version tracking: Every change creates a new `DiagramVersion` record. This gives us:

• Full history of changes

• Ability to see what changed (via `changeType` and `changes`)

• Potential for undo/redo or version comparison later

Smart title generation: Instead of generic "Untitled Diagram" titles, we generate unique ones using Unix timestamps:

• Format: `Diagram-1730575505000`

• Ensures every diagram has a unique identifier

• Easy to parse and sort chronologically

What's Next?

In Part 2, we'll dive into the API routes that handle creating, updating, and fetching diagrams. We'll also look at how we implemented the smart "reuse empty diagrams" feature to keep the database clean.