03 — Database Design

Back to README | Prev: Architecture | Next: Phase 1 Implementation

Overview

The database is split into two layers:

• Core tables (prefixed laraschema_) — Ship with the core package. Required for the schema designer to function.
• SaaS tables — Only exist in the hosted version. Handle auth, teams, billing, and versioning.

All core tables use the laraschema_ prefix to avoid collisions when the package is installed in an existing Laravel app.

Entity Relationship Diagram

users (SaaS only)
  │
  └── has many ──> projects
                     │
                     ├── has many ──> tables
                     │                  │
                     │                  ├── has many ──> columns
                     │                  └── has many ──> indexes
                     │
                     └── has many ──> relationships
                                        │
                                        ├── belongs to ──> source_table (tables)
                                        └── belongs to ──> target_table (tables)

teams (SaaS only)
  │
  ├── has many ──> users (via team_user pivot)
  └── has many ──> projects

Visual Diagram

+---------------------+       +---------------------+
|      projects       |       |       users         |
+---------------------+       +---------------------+
| id              PK  |<──────| id              PK  |
| user_id         FK  |       | name                |
| team_id         FK  |       | email               |
| name                |       | password            |
| description         |       | plan                |
| canvas_settings  J  |       +---------------------+
| is_public           |               │
| share_token         |               │
+---------------------+               │
    │          │                       │
    │          │               +───────┼───────+
    │          │               │               │
    ▼          ▼               ▼               ▼
+--------+ +---------------+ +-----+ +----------------+
| tables | | relationships | |teams| |  team_user     |
+--------+ +---------------+ +-----+ +----------------+
| id  PK | | id         PK | |id PK| | team_id    FK  |
|proj  FK| | project_id FK | |name | | user_id    FK  |
| name   | | type          | |owner| | role           |
| pos_x  | | source_tbl FK | +-----+ +----------------+
| pos_y  | | target_tbl FK |
| width  | | source_col FK |
| color  | | target_col FK |
| flags  | | pivot_table   |
+--------+ | on_delete     |
    │      | line_points J |
    │      +---------------+
    │
    ├──────────┬──────────┐
    │                     │
    ▼                     ▼
+-----------+      +-----------+
|  columns  |      |  indexes  |
+-----------+      +-----------+
| id     PK |      | id     PK |
| table  FK |      | table  FK |
| name      |      | name      |
| type      |      | type      |
| length    |      | col_ids J |
| nullable  |      +-----------+
| default   |
| is_unique |
| is_index  |
| enum_vals |
| sort_order|
+-----------+

Legend: PK = Primary Key, FK = Foreign Key, J = JSON column

Core Package Tables

These 8 tables ship with the core package.

laraschema_categories

Categories for organizing template / starter projects. Seeded by the package.

laraschema_projects

The top-level container for a schema design.

laraschema_tables

Each visual table block on the canvas.

Unique constraint: [project_id, name] — no duplicate table names within a project.

Pivot tables: When a belongsToMany relationship is created, a real laraschema_tables row is inserted with is_pivot=true and linked from the relationship via pivot_table_id. The canvas renders these as dedicated pivot nodes.

laraschema_columns

Individual columns within a table.

Unique constraint: [table_id, name] — no duplicate column names within a table.

laraschema_relationships

Connections between tables, representing both foreign keys and Eloquent relationships.

laraschema_indexes

Composite and special indexes on tables. Single-column indexes are handled via flags on the columns table.

laraschema_export_jobs

Tracks queued export jobs (ZIP/code generation) so the UI can show progress and the admin panel can monitor queue health.

laraschema_feedback

In-app user feedback submitted via the feedback widget.

Supported Laravel Column Types

The columns.type field accepts all Laravel migration column types:

Numeric

tinyInteger, smallInteger, mediumInteger, integer, bigInteger, unsignedTinyInteger, unsignedSmallInteger, unsignedMediumInteger, unsignedInteger, unsignedBigInteger, decimal, double, float

String

char, string, tinyText, text, mediumText, longText

Date & Time

date, dateTime, dateTimeTz, time, timeTz, timestamp, timestampTz, year

Binary & Boolean

binary, boolean

JSON

json, jsonb

UUID & ULID

uuid, ulid

Network

ipAddress, macAddress

Special

enum, set, foreignId, foreignUlid, foreignUuid, morphs, nullableMorphs, ulidMorphs, uuidMorphs, rememberToken, geography, geometry

Type Categories (used in code generation)

Supported Relationship Types

The relationships.type field accepts:

FK Auto-Creation Rules

When a relationship is drawn between two tables, the system automatically creates the foreign key column:

• belongsTo — FK on source table (e.g., posts.user_id)
• hasOne / hasMany — FK on target table (e.g., phones.user_id)
• belongsToMany — Creates a real pivot Table (is_pivot = true) holding both FKs; neither parent gets a column. A self-referential pivot uses parent_id / child_id, since deriving both keys from one table name would collapse them into a single column.
• morphOne / morphMany — Creates the {morph}_type / {morph}_id pair on the target (e.g. comments.postable_type, comments.postable_id). Neither column is marked is_foreign: a morph id deliberately points at more than one table, so it carries no FK constraint.
• morphToMany / morphedByMany — Creates a pivot Table holding the morph pair plus one plain FK. morphedByMany is the inverse side, so its morph columns are named after the target table. By default the pivot name is derived from the morphable table (posts → postables); pass an explicit pivot name to use the shared Laravel convention instead, where taggables implies the morph name taggable.

hasOneThrough and hasManyThrough are the two types in the table above that are not creatable — there is nowhere in the data model to record the intermediate table. RelationshipType::mvpTypes() is the authoritative list of the eight that are, and it drives both the canvas editor's picker and the MCP create_relationship schema.

These rules are applied by LaraSchema\Services\SchemaMutator, which is the single write path behind the MCP tools and the AI assistant's apply(). The canvas editor (RelationshipManager) mirrors them.

SaaS-Only Tables

These tables only exist in the hosted SaaS application — they are not part of the core package.

users

Standard Laravel users table (via Laravel Breeze).

teams

team_user (pivot)

Unique constraint: [team_id, user_id]

schema_versions

Version history snapshots for projects.

Unique constraint: [project_id, version_number]

project_shares

Granular sharing permissions.

Design Decisions

Next: Phase 1 Implementation