Database Design

03 — Database Design

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

Overview

The database is split into two layers:

  • Core tables (prefixed schemacraft_) — Ship with the open-source 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 schemacraft_ 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 5 tables ship with the open-source package.

schemacraft_projects

The top-level container for a schema design.

Column Type Constraints Description
id bigIncrements PK Auto-incrementing primary key
user_id foreignId nullable, index Owner (null in package mode, set in SaaS)
team_id foreignId nullable, index Team workspace (SaaS only)
name string(255) required Project name (e.g., "E-Commerce App")
description text nullable Optional project description
canvas_settings json nullable Zoom level, pan position, grid settings
is_public boolean default: false Whether schema is publicly viewable
share_token string(64) nullable, unique Token for public share URL
created_at timestamp
updated_at timestamp
deleted_at timestamp nullable Soft delete support

schemacraft_tables

Each visual table block on the canvas.

Column Type Constraints Description
id bigIncrements PK
project_id foreignId required, index, cascade on delete Parent project
name string(255) required Table name (e.g., "users", "posts")
position_x decimal(10,2) default: 0 X position on canvas
position_y decimal(10,2) default: 0 Y position on canvas
width decimal(10,2) default: 250 Table card width on canvas
color string(7) nullable Hex color for header (e.g., "#3B82F6")
use_id boolean default: true Auto-include $table->id()
use_timestamps boolean default: true Auto-include $table->timestamps()
use_soft_deletes boolean default: false Auto-include $table->softDeletes()
sort_order integer default: 0 Display order in sidebar/list
created_at timestamp
updated_at timestamp

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

schemacraft_columns

Individual columns within a table.

Column Type Constraints Description
id bigIncrements PK
table_id foreignId required, index, cascade on delete Parent table
name string(255) required Column name (e.g., "email", "title")
type string(50) required Laravel column type (see Supported Column Types)
length integer nullable Character length (e.g., 255 for string)
precision integer nullable Decimal precision (e.g., 8 in decimal(8,2))
scale integer nullable Decimal scale (e.g., 2 in decimal(8,2))
nullable boolean default: false Whether column allows NULL
default_value string(255) nullable Default value expression
is_unsigned boolean default: false Unsigned integer
is_unique boolean default: false Has unique constraint
is_index boolean default: false Has regular index
is_primary boolean default: false Is part of primary key
is_auto_increment boolean default: false Auto-incrementing
is_foreign boolean default: false Is a foreign key column
enum_values json nullable Values for enum/set types
comment string(255) nullable Column comment
sort_order integer default: 0 Display order within table
created_at timestamp
updated_at timestamp

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

schemacraft_relationships

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

Column Type Constraints Description
id bigIncrements PK
project_id foreignId required, index, cascade on delete Parent project
name string(255) nullable Custom relationship method name
type string(30) required Eloquent relationship type (see Supported Relationship Types)
source_table_id foreignId required, index The "from" table
target_table_id foreignId required, index The "to" table
source_column_id foreignId nullable Foreign key column on source
target_column_id foreignId nullable Referenced column on target
pivot_table_name string(255) nullable Pivot table name (belongsToMany)
pivot_source_key string(255) nullable Source FK on pivot table
pivot_target_key string(255) nullable Target FK on pivot table
on_delete string(20) default: "cascade" cascade, restrict, set null, no action
on_update string(20) default: "cascade" cascade, restrict, set null, no action
line_points json nullable Canvas line anchor/bend points for SVG
created_at timestamp
updated_at timestamp

schemacraft_indexes

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

Column Type Constraints Description
id bigIncrements PK
table_id foreignId required, index, cascade on delete Parent table
name string(255) nullable Index name (auto-generated if null)
type string(20) required index, unique, primary, fulltext, spatial
column_ids json required Ordered array of column IDs in the index
created_at timestamp
updated_at timestamp

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)

Category Types Requires
Requires length char, string length parameter
Requires precision decimal, double, float precision and scale parameters
Requires values enum, set enum_values JSON array
No parameters All others Nothing extra

Supported Relationship Types

The relationships.type field accepts:

Type Description FK Location
hasOne One-to-one FK on target table
hasMany One-to-many FK on target table
belongsTo Inverse one-to-one/many FK on source table
belongsToMany Many-to-many FK on pivot table
hasOneThrough Has one through intermediate Through intermediate table
hasManyThrough Has many through intermediate Through intermediate table
morphOne Polymorphic one-to-one morphs columns on target
morphMany Polymorphic one-to-many morphs columns on target
morphToMany Polymorphic many-to-many Pivot with morphs
morphedByMany Inverse polymorphic many-to-many Pivot with morphs

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 pivot table entry (no FK on either table directly)

SaaS-Only Tables

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

users

Standard Laravel users table (via Laravel Breeze).

Column Type Constraints Description
id bigIncrements PK
name string(255) required
email string(255) required, unique
email_verified_at timestamp nullable
password string(255) required
avatar_url string(255) nullable Profile picture
plan string(20) default: "free" free, pro, team
remember_token string(100) nullable
created_at timestamp
updated_at timestamp

teams

Column Type Constraints Description
id bigIncrements PK
name string(255) required Team name
owner_id foreignId required, index Team creator/owner
created_at timestamp
updated_at timestamp

team_user (pivot)

Column Type Constraints Description
id bigIncrements PK
team_id foreignId required, cascade on delete
user_id foreignId required, cascade on delete
role string(20) default: "member" owner, admin, member
created_at timestamp
updated_at timestamp

Unique constraint: [team_id, user_id]

schema_versions

Version history snapshots for projects.

Column Type Constraints Description
id bigIncrements PK
project_id foreignId required, index, cascade on delete
version_number integer required Auto-incrementing per project
snapshot json required Full schema state at point in time
description string(255) nullable Version description / commit message
created_by foreignId nullable User who created this version
created_at timestamp

Unique constraint: [project_id, version_number]

project_shares

Granular sharing permissions.

Column Type Constraints Description
id bigIncrements PK
project_id foreignId required, index, cascade on delete
user_id foreignId nullable, cascade on delete Shared with specific user
email string(255) nullable Shared via email (user may not exist yet)
permission string(20) default: "view" view, edit, admin
token string(64) unique Share link token
expires_at timestamp nullable Optional expiration
created_at timestamp
updated_at timestamp

Design Decisions

Decision Rationale
schemacraft_ table prefix Avoids collisions when package is installed in existing Laravel apps
JSON for canvas_settings Flexible storage for zoom, pan, grid — schema may evolve without new migrations
JSON for line_points Relationship lines can have variable bend points; array is simplest
JSON for column_ids in indexes Composite indexes have ordered columns; array preserves order
Soft deletes on projects only Allow project recovery; tables/columns cascade-delete with project
share_token on projects Simple public sharing without full share management infrastructure
Separate indexes table Single-column indexes use column flags; composite indexes need their own storage
sort_order on tables and columns Preserves user's preferred ordering in sidebar and within table cards
user_id nullable on projects Package mode has no users; SaaS mode always sets user_id
snapshot as JSON in schema_versions Captures complete state for easy point-in-time restoration

Next: Phase 1 Implementation

Loading...