!!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) [{PaperScapeQueueButton url="/OpenForum/AddOn/PaperScape/DBModelView/db-model-view.giraffe.js" text="Send via PaperScape Queue" name="DB Model View"}]

((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 [{ChildPagesList pageName="&pageName;"}] ---- !!Attachments [{AttachmentsList pageName="&pageName;" matching=".*"}]