Database Schema
35 tables, all UUID-keyed, with JSON columns where flexible schema is needed. Migrations live in database/migrations/.
Conventions
- UUID primary keys — every domain table uses
char(36) primary keys via the HasUuid trait.
- Soft deletes — most domain tables include
deleted_at.
- JSON columns — flexible per-record schema for entry data, settings, scopes, computed fields.
- Audit columns —
created_by / updated_by FK to users.id, nulled on user delete.
- Functional indexes — MySQL 8 functional indexes on hot JSON paths (e.g.
data->>'$.title').
Tables by domain
Identity & access
| Table | Purpose |
|---|
users | Admin users. Email, hashed password, avatar, last login. |
roles | Named roles with a JSON permissions map. |
user_roles | Pivot — many-to-many between users and roles. |
password_reset_tokens | Standard Laravel reset tokens. |
sessions | Laravel session table (DB-backed sessions). |
Content engine
| Table | Purpose |
|---|
content_models | Definition of a content type (collection or single). Holds slug, naming, flags (is_localized, is_single, has_seo), JSON settings, computed_fields, and api_config. |
content_fields | Field definitions for each model: type, validation, default, ordering. |
content_entries | Records belonging to a content model. Stores per-entry values in a JSON data column with locale, status, slug, seo, scheduled/published timestamps. |
content_revisions | Immutable snapshot of an entry on each save; supports diff and restore. |
content_relations | Cross-entry relationships (one-to-many, many-to-many, etc.). |
components | Reusable field groups that can be embedded into models or dynamic zones. |
Media
| Table | Purpose |
|---|
media_folders | Hierarchical folder tree (self-referential). |
media_files | Uploaded file metadata: path, mime, size, dimensions, alt text. |
Forms
| Table | Purpose |
|---|
forms | Form definitions with field schema, notification settings, slug for public endpoint. |
form_submissions | Per-submission payload, status (new/read/spam), IP, user agent, internal notes. |
API & integrations
| Table | Purpose |
|---|
api_keys | Public key + hashed secret, JSON scopes, JSON allowed_origins, rate limit, expiry, usage counters. |
api_request_logs | Per-request audit row: API key, endpoint, status, duration, IP. |
webhooks | Outbound webhook subscriptions: URL, events, secret, retry policy. |
webhook_deliveries | Delivery attempts with payload, response code/body, retry count. |
Localization & site config
| Table | Purpose |
|---|
languages | Supported locales. One default, others toggleable; supports is_translatable on settings. |
settings | Key/value site settings, scoped by group, with optional translations. |
Observability & messaging
| Table | Purpose |
|---|
activity_logs | Polymorphic audit log: actor, action, subject, diff, IP, user agent. |
notifications | Laravel notifications. |
jobs | Queue jobs. |
job_batches | Batched job metadata. |
failed_jobs | Failed queue jobs. |
cache | Cache store (DB driver). |
cache_locks | Cache lock keys. |
Key relationships
content_models 1 ── ∞ content_fields (model defines fields)
content_models 1 ── ∞ content_entries (entries belong to a model)
content_entries 1 ── ∞ content_revisions (every save → 1 revision)
content_entries ∞ ── ∞ content_entries (via content_relations)
users 1 ── ∞ content_entries (created_by / updated_by)
users ∞ ── ∞ roles (via user_roles)
media_folders 1 ── ∞ media_files
media_folders 1 ── ∞ media_folders (self-referential tree)
forms 1 ── ∞ form_submissions
api_keys 1 ── ∞ api_request_logs
webhooks 1 ── ∞ webhook_deliveries
Example: content_entries
id char(36) PK
content_model_id char(36) FK → content_models.id (cascade)
title varchar(500) nullable
slug varchar(500) nullable
status varchar(20) default 'draft' — draft|published|scheduled|archived
locale varchar(10) default 'en'
data json — field values keyed by field slug
seo json nullable
computed_data json nullable — server-evaluated computed fields
published_at timestamp nullable
scheduled_at timestamp nullable
created_by char(36) FK → users.id (null on delete)
updated_by char(36) FK → users.id (null on delete)
created_at, updated_at, deleted_at
UNIQUE (content_model_id, slug, locale)
INDEX (content_model_id, status, updated_at)
INDEX (content_model_id, locale, status)
INDEX (status, scheduled_at)
INDEX on CAST(data->>'$.title' AS CHAR(255)) — MySQL 8 functional index
Example: api_keys
id char(36) PK
name varchar(255)
key varchar(64) UNIQUE — public identifier (sent in header)
secret_hash varchar(255) — bcrypt-hashed secret
scopes json — ["read", "write", "delete"]
allowed_origins json nullable — CORS allowlist
rate_limit int unsigned default 60 — requests per minute
usage_count bigint unsigned default 0
last_used_at timestamp nullable
last_used_ip varchar(45) nullable
expires_at timestamp nullable
is_active bool default true
created_by char(36) FK → users.id (null on delete)
Migrations live with the codeSee database/migrations/ for the authoritative schema. The seeded dm_editor.sql file at the project root is a one-shot SQL dump if you want to bootstrap without running Laravel.