DBModelView

Database Model Viewer

This page demonstrates rendering relational database schemas in PaperScape using Giraffe.js. The example below loads and visualizes the `example-database-model.json` file.

PaperScape Integration

Drag the link onto the PaperScape canvas to embed this viewer as a reusable scene component.

Database Model View PaperScape App (.giraffe.js) undefined

Within PaperScape, click a table card to explore its columns, constraints, and indexes. Foreign key arrows are rendered automatically from the JSON model and are clickable for editing.

Drop `.dbmv.json` files or URLs directly onto the PaperScape canvas to load models; choose _Import_ to merge or _Replace_ to swap the active scene.

PaperScape Controls

Save Schema – prompts for a `.dbmv.json` server path (defaulting under `/OpenForum/AddOn/PaperScape/DBModelView/`) the first time, then saves straight to that file on subsequent clicks.
Open Schema – launches a file picker for `.dbmv.json` files; choose _Import_ to merge or _Replace_ to overwrite the scene.
Download – saves the current schema JSON to disk with the suggested `.dbmv.json` name.
New Schema – scaffolds an empty database model with sensible defaults; the path helper proposes a valid `.dbmv.json` filename.
All file operations expect filenames ending in `.dbmv.json`; PaperScape warns if a different suffix is supplied.
*

PaperScape Editing Roadmap

Goal: evolve the Database Model viewer into a schema design studio that complements PaperScape's spatial layout tooling.

✅ Recently Completed

Inline Editors – Column and index editors replace JSON text areas, mirroring Object Modeler's authoring experience
Save Schema Control – `.dbmv.json` file management with defaulting and prompt workflow
Smart Foreign Key Arrows – Arrows originate/terminate at exact columns, adapt to nearest sides, with orange styling and endpoint markers
Multi-Column Unique Constraints – Full support for defining and managing unique constraints across multiple columns
FK Validation – Foreign key references validated against existing tables and columns with clear error messages
Visual Relationship Editing – Click FK arrows to view details and delete relationships directly from canvas
Drop Shadows – Table cards display elegant drop shadows for better visual hierarchy
JSON Schema – Canonical schema describing database model format, enabling validation and tooling integration
Extensions Model – Metadata (description, notes, datasource) moved to extensions object for tool interoperability
Table Header Colors – 6 color schemes (teal, blue, violet, amber, emerald, rose) to visually organize tables by category
Drag-and-Drop `.dbmv.json` – Recognizes `.dbmv.json` files dropped into PaperScape and offers import/replace flows automatically

🚧 In Progress - Enhanced constraint management (check constraints, exclusion constraints). 🗺️ Upcoming - Schema diffing utilities with SQL migration export. - Seed data attachments and quick prototyping helpers. - Collaborative enhancements (comments, change history, keyboard shortcuts, theming).

Roadmap Additions

- - Reverse Engineering** – Import schema definitions from PostgreSQL/MySQL/SQLite connections via reusable server-side actions.

- Performance Insights** – Highlight missing indexes for FK columns and flag wide tables or unused columns.

- Diagram Layout Modes** – Offer force-directed, layered, and alphabetical layouts tuned for large schemas.

- Export Targets** – Generate DBML, Mermaid, or PlantUML exports directly from the PaperScape scene.

- Validation Rules** – Pluggable rule engine to enforce naming conventions, versioning, and security annotations.

- Table Info Icon** – Surface an info glyph in the table header that opens the metadata dialog.

MCP Collaboration Plan

Goal: Give MCP agents a purpose-built interface for database modeling tasks so they no longer have to craft raw PaperScape queue envelopes.
Tooling: Build `/OpenForum/AddOn/ClaudeMCP/DBModelViewTool.sjs` exposing CRUD-style actions (`create_table`, `update_table`, `delete_table`, `move_table`, `list_schema`). Internally, the tool will call the DBModelView command bus to push history entries and mirror PaperScape’s collaboration queue.
Schema Access: Provide an optional `read_schema` action that returns the active `.dbmv.json` snapshot (falling back to `/OpenForum/AddOn/PaperScape/DBModelView/db-model-view-state.dbmv.json` if PaperScape isn’t open).
Session Detection: Add a lightweight status check (e.g., `/OpenForum/AddOn/PaperScape?action=status`) so the tool can warn when no PaperScape tab is connected and switch to headless file edits.
Live Sync: MCP actions push PaperScape history entries immediately, but `list_tables`/`read_schema` reflect whatever `.dbmv.json` file was last saved. Remind agents to trigger a save (or call `read_schema` with their own snapshot path) if they need JSON parity with the live canvas.
Testing: Extend `prompt-collaboration.md` with MCP usage examples and add queue-console logging to confirm inbound CRUD actions replay correctly in multiple browser sessions. Use `/web/content/default/dbmodelview-mcp-verification.prompt.txt` to script the full create/update/move/delete flow.

The canvas renders database tables as cards showing columns, constraints, and indexes. Foreign key relationships draw clickable labels between tables. Update the JSON file and refresh the page to see changes—no manual markup edits required.

Interactive Features

Automatic Layout: Tables are arranged in dependency tiers derived from foreign keys
Constraint Awareness: Column badges reflect PK, uniqueness, nullability, and FK targets
Unique Constraints: Define multi-column unique constraints in the table editor
FK Validation: Foreign keys are validated against existing tables and columns
Clickable Relationships: Click FK arrow labels to view details or delete relationships
Index Summary: Index definitions render with names, uniqueness, and filter clauses
Color-Coded Tables: Choose from 6 header color schemes to visually organize your schema
Responsive Canvas: Scene resizes to fit the generated layout, including large schemas
Error Handling: Friendly messaging when the JSON payload cannot be parsed

Schema Definition

The visualization above renders the following schema structure from `example-database-model.json`: { "tables": [ { "id": "users", "name": "users", "columns": [ { "name": "id", "type": "uuid", "primaryKey": true, "nullable": false, "default": "uuid_generate_v4()" }, { "name": "email", "type": "text", "nullable": false, "unique": true }, { "name": "display_name", "type": "text", "nullable": false }, { "name": "created_at", "type": "timestamptz", "nullable": false, "default": "now()" }, { "name": "status", "type": "user_status", "nullable": false, "default": "'active'::user_status" } ], "indexes": [ { "name": "users_email_idx", "unique": true, "columns": ["email"] }, { "name": "users_status_created_at_idx", "columns": ["status", "created_at"], "method": "btree" } ], "extensions": { "description": "Registered platform members with authentication credentials and profile metadata.", "datasource": "PostgreSQL", "color": "teal" } }, { "id": "products", "name": "products", "columns": [ { "name": "id", "type": "uuid", "primaryKey": true, "nullable": false, "default": "uuid_generate_v4()" }, { "name": "sku", "type": "text", "nullable": false, "unique": true }, { "name": "name", "type": "text", "nullable": false }, { "name": "price_cents", "type": "integer", "nullable": false }, { "name": "active", "type": "boolean", "nullable": false, "default": "true" } ], "indexes": [ { "name": "products_active_idx", "columns": ["active"], "method": "btree" } ], "extensions": { "description": "Catalog entries that can appear on customer orders.", "datasource": "PostgreSQL", "color": "emerald" } }, { "id": "orders", "name": "orders", "columns": [ { "name": "id", "type": "uuid", "primaryKey": true, "nullable": false, "default": "uuid_generate_v4()" }, { "name": "user_id", "type": "uuid", "nullable": false, "foreignKey": { "targetTable": "users", "targetColumn": "id" } }, { "name": "ordered_at", "type": "timestamptz", "nullable": false, "default": "now()" }, { "name": "status", "type": "order_status", "nullable": false, "default": "'pending'::order_status" }, { "name": "total_cents", "type": "integer", "nullable": false } ], "indexes": [ { "name": "orders_user_id_idx", "columns": ["user_id"], "method": "btree" }, { "name": "orders_status_idx", "columns": ["status"], "method": "btree" } ], "extensions": { "description": "Order headers capturing who placed the order and its fulfilment status.", "datasource": "PostgreSQL", "color": "blue" } }, { "id": "order_items", "name": "order_items", "columns": [ { "name": "id", "type": "uuid", "primaryKey": true, "nullable": false, "default": "uuid_generate_v4()" }, { "name": "order_id", "type": "uuid", "nullable": false, "foreignKey": { "targetTable": "orders", "targetColumn": "id" } }, { "name": "product_id", "type": "uuid", "nullable": false, "foreignKey": { "targetTable": "products", "targetColumn": "id" } }, { "name": "quantity", "type": "integer", "nullable": false, "default": 1 }, { "name": "unit_price_cents", "type": "integer", "nullable": false } ], "indexes": [ { "name": "order_items_order_id_idx", "columns": ["order_id"], "method": "btree" }, { "name": "order_items_product_id_idx", "columns": ["product_id"], "method": "btree" } ], "extensions": { "description": "Line items associated with an order, connecting products and quantities.", "datasource": "PostgreSQL", "color": "violet" } } ], "relationships": [ { "type": "foreignKey", "sourceTable": "orders", "targetTable": "users" }, { "type": "foreignKey", "sourceTable": "order_items", "targetTable": "orders" }, { "type": "foreignKey", "sourceTable": "order_items", "targetTable": "products" } ] }

Database Model Schema

JSON Schema Specification

DBModelView uses a canonical JSON Schema (Draft 2020-12) that defines the structure and validation rules for database models. The schema focuses purely on database structure elements (tables, columns, indexes, constraints, relationships) and uses an extensions model for metadata, similar to Object Model Viewer.

database-model-schema.json

The schema provides:

Type validation for all properties (tables, columns, indexes, constraints, relationships)
Required field enforcement ensuring all essential properties are present
Enumerated values for constraint types, relationship types, and actions (CASCADE, SET NULL, etc.)
Documentation with descriptions for every field
Tool integration enabling IDE autocomplete, linters, and validation tools
Interoperability allowing exchange between different database modeling tools via standardized structure
Extensions model for tool-specific metadata (description, notes, datasource, position, color) following Object Model Viewer pattern

Schema Properties

Tables

`id` - Unique identifier (defaults to the table name)
`name` - Table name as it appears in the database (required)
`columns` - Array of column definitions with `name`, `type`, constraint flags, defaults, and descriptions (required)
`indexes` - Array of index definitions with name, uniqueness flag, columns, optional method, and optional filter
`constraints` - Array of constraint definitions (e.g., unique constraints) with name, type, and columns
`primaryKey` - Optional object with a `columns` array to surface composite PKs
`extensions` - Optional tool-specific metadata object

Table Extensions

The `extensions` object can contain tool-specific metadata that is not part of the core database schema:

`description` - Human-readable description of the table's purpose
`notes` - Additional notes or documentation (string or array of strings)
`datasource` - Database engine or deployment environment (e.g., "PostgreSQL", "MySQL", "BigQuery")
`color` - Table header color scheme: "teal" (default), "blue", "violet", "amber", "emerald", or "rose"
`position` - Canvas position for graphical layout (e.g., `{x: 100, y: 50}`)

Columns

`name` - Column name (required)
`type` - Data type: uuid, text, integer, boolean, timestamptz, etc. (required)
`primaryKey` - Boolean flag for primary key columns
`unique` - Boolean flag for unique constraint
`nullable` - Boolean controlling NULL acceptance (default: true)
`required` - Alias for nullable=false
`notNull` - Alias for nullable=false
`default` - Default value expression
`foreignKey` - Foreign key reference (string as "table.column" or object with targetTable/targetColumn)
`description` - Human-readable column documentation
`generated` - Generated column spec ("ALWAYS" or "BY DEFAULT")

Indexes

`name` - Index name (required)
`columns` - Array of column names
`expression` - Expression for functional indexes (e.g., "lower(email)")
`unique` - Boolean for unique indexes
`method` - Index method (btree, hash, gin, gist, brin)
`where` - Partial index filter condition

Constraints

`name` - Constraint name (required)
`type` - "unique", "check", or "exclusion" (required)
`columns` - Array of column names (required, minimum 1)
`expression` - Check constraint expression (for type="check")

Foreign Keys

`targetTable` (or `table`) - Referenced table name
`targetColumn` (or `column`) - Referenced column name
`onDelete` - CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION
`onUpdate` - CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION
`name` - Foreign key constraint name

Relationships

`type` - Relationship type (`foreignKey`, `replication`, `materializes`, `view`, `inheritance`)
`sourceTable` - Table that owns the relationship (required)
`targetTable` - Table that the relationship points to (required)
`sourceColumn` - Source column name (for foreign keys)
`targetColumn` - Target column name (for foreign keys)
`fkName` - Foreign key constraint name

Note: The schema structure follows the Object Model Viewer pattern, using an extensions object for tool-specific metadata separate from core database structure. This ensures portability between tools while supporting rich metadata for documentation and visualization.

Application Architecture

The Database Model Viewer uses a modular component-based architecture assembled via ServiceBuilder:

Build Configuration

The application is built from multiple source files using `script.build.json`: { "version": "0.0.3", "targetFile": "/OpenForum/AddOn/PaperScape/DBModelView/db-model-view.giraffe.js", "versionFile": "/OpenForum/AddOn/PaperScape/DBModelView/Version/db-model-view.giraffe.js", "steps": [ { "action": "append", "file": "/OpenForum/AddOn/PaperScape/DBModelView/db-model-view-core.giraffe.js" }, { "action": "insert", "searchFor": "// insert TableBox", "file": "/OpenForum/AddOn/PaperScape/DBModelView/TableBox.js" }, { "action": "insert", "searchFor": "// insert RelationshipArrow", "file": "/OpenForum/AddOn/PaperScape/DBModelView/RelationshipArrow.js" } ] }

Component Files

Core Application (`db-model-view-core.giraffe.js`)

Contains the main `DBModelViewApp` constructor
Defines the Integration module for drag-and-drop `.dbmv.json` support
Provides schema management, file I/O, and autosave functionality
Includes placeholder comments for component insertion
Defines table color constants with `typeof` guards for reusability

Table Rendering (`TableBox.js`)

Renders individual table cards with columns, indexes, and constraints
Displays primary keys, foreign keys, unique constraints, and nullability
Supports 6 color schemes for visual organization (teal, blue, violet, amber, emerald, rose)
Can be used independently on pages outside PaperScape
Self-contained with proper dependency isolation

Relationship Rendering (`RelationshipArrow.js`)

Draws foreign key arrows between tables
Smart anchor positioning - connects at exact column locations
Orange styling with endpoint markers for visibility
Interactive - click to view/edit/delete relationships
Adapts to nearest table edges automatically

Standalone Component Pattern

Components use defensive programming to work in multiple contexts: // Color constants defined with guards in db-model-view-core.giraffe.js var TABLE_COLOR_OPTIONS = [ { id: "teal", headerFill: "#0f766e", headerBorder: "#0d9488", titleColor: "#ecfeff" }, { id: "blue", headerFill: "#2563eb", headerBorder: "#1d4ed8", titleColor: "#f8fafc" }, // ... more colors ]; var DEFAULT_TABLE_COLOR = "teal"; // Components receive colorTokens as options var colorTokens = getTableColorTokens(table.extensions.color || DEFAULT_TABLE_COLOR); var box = new TableBox(table, metrics, position, { colorTokens: colorTokens }); This design enables:

- - Standalone Pages** - TableBox works when included directly in HTML pages

- PaperScape Integration** - Full application assembled for interactive editing

- Reusability** - Components can be shared across different database tools

- Flexibility** - Easy to modify individual components without rebuilding everything

Building the Application

To rebuild after modifying source files: http://localhost:8888/OpenForum/AddOn/ServiceBuilder?action=buildJavascript&pageName=/OpenForum/AddOn/PaperScape/DBModelView&fileName=script.build.json The build process: 1. **Appends** the core file as the base (includes Integration module, color system) 2. **Inserts** TableBox at `// insert TableBox` marker 3. **Inserts** RelationshipArrow at `// insert RelationshipArrow` marker 4. **Outputs** the complete `.giraffe.js` file ready for PaperScape

Drag-and-Drop Integration

The Integration module handles `.dbmv.json` file drops:

- - Instance Management** - Tracks active DBModelView instances in PaperScape

- File Processing** - Validates and decodes dropped files (supports data URIs)

- Import/Replace Dialog** - User chooses to merge or replace current schema

- Auto-spawning** - Creates new instance if no active view exists

- Path Normalization** - Strips URLs to store clean server paths (e.g., `/OpenForum/AddOn/PaperScape/DBModelView/schema.dbmv.json`)

Extending the Viewer

Custom Styling

Adjust `TableBox.js` colours and fonts to match your product design system or highlight regulated data sets.

Additional Relationship Types

Add support for replication or materialized view dependencies with alternate arrow styling and legends.

Interactive Features

Introduce click handlers or overlays to edit columns, constraints, and indexes directly within PaperScape.

Export Capabilities

Integrate export buttons that emit DBML, Mermaid, or SQL migration scripts using the PaperScape scene data.

Schema Validation

Use the JSON Schema file with validation tools: # Using AJV (Node.js) npm install ajv const Ajv = require('ajv'); const ajv = new Ajv(); const validate = ajv.compile(schemaJson); const valid = validate(modelJson); # Using online validators https://www.jsonschemavalidator.net/

Database Model Viewer

PaperScape Controls

PaperScape Editing Roadmap

Roadmap Additions

MCP Collaboration Plan

Interactive Features

Schema Definition

Database Model Schema

JSON Schema Specification

Schema Properties

Tables

Table Extensions

Columns

Indexes

Constraints

Foreign Keys

Relationships

Application Architecture

Build Configuration

Component Files

Standalone Component Pattern

Building the Application

Drag-and-Drop Integration

Extending the Viewer

Custom Styling

Additional Relationship Types

Interactive Features

Export Capabilities

Schema Validation

Child Pages

Attachments