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.

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.

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/

---

Child Pages

• DBModelViewEditor
• Version

---

Attachments

• /OpenForum/AddOn/PaperScape/DBModelView/example-database-model.json
• /OpenForum/AddOn/PaperScape/DBModelView/TableBox.js
• /OpenForum/AddOn/PaperScape/DBModelView/renderer.sjs
• /OpenForum/AddOn/PaperScape/DBModelView/db-model-view-state.dbmv.json
• /OpenForum/AddOn/PaperScape/DBModelView/page.content
• /OpenForum/AddOn/PaperScape/DBModelView/default-database-model.json
• /OpenForum/AddOn/PaperScape/DBModelView/db-model-view-core.giraffe.js
• /OpenForum/AddOn/PaperScape/DBModelView/db-model-viewer.js
• /OpenForum/AddOn/PaperScape/DBModelView/script.build.json
• /OpenForum/AddOn/PaperScape/DBModelView/my-test-schema.dbmv.json
• /OpenForum/AddOn/PaperScape/DBModelView/data.json
• /OpenForum/AddOn/PaperScape/DBModelView/example-database-model.dbmv.json
• /OpenForum/AddOn/PaperScape/DBModelView/page.js
• /OpenForum/AddOn/PaperScape/DBModelView/page.html.template.link
• /OpenForum/AddOn/PaperScape/DBModelView/db-model-view.giraffe.js
• /OpenForum/AddOn/PaperScape/DBModelView/page.html.fragment
• /OpenForum/AddOn/PaperScape/DBModelView/RelationshipArrow.js
• /OpenForum/AddOn/PaperScape/DBModelView/page.html
• /OpenForum/AddOn/PaperScape/DBModelView/shop-test-db.dbmv.json
• /OpenForum/AddOn/PaperScape/DBModelView/database-model-schema.json