# Apex Admins — Database Schema

**Version:** 1.0  
**Date:** 2026-06-27  
**Database:** `ronnie_apex`

---

## Migration Order

1. users, password_reset_tokens, sessions
2. roles, permissions, role_permission, user_role
3. settings
4. activity_log
5. media_folders, media, mediables
6. menus, menu_items
7. redirects
8. seo_meta, seo_audits
9. pages, page_templates
10. service_categories, services
11. hire_roles
12. industries
13. locations
14. blog_categories, blog_tags, blog_posts, blog_post_tag
15. testimonials
16. case_studies
17. careers, career_applications
18. academy_courses, academy_lessons
19. forms, form_fields, form_submissions, leads
20. analytics_events
21. sitemap_entries

---

## Table Definitions

### users
| Column | Type | Notes |
|--------|------|-------|
| id | bigint PK | |
| name | varchar(255) | |
| email | varchar(255) UNIQUE | |
| email_verified_at | timestamp NULL | |
| password | varchar(255) | |
| status | enum(active,inactive) | default active |
| last_login_at | timestamp NULL | |
| remember_token | varchar(100) NULL | |
| created_at, updated_at | timestamps | |

### roles
| Column | Type | Notes |
|--------|------|-------|
| id | bigint PK | |
| name | varchar(255) | |
| slug | varchar(255) UNIQUE | super-admin, admin, editor, seo-manager, sales |
| description | text NULL | |

### permissions
| Column | Type | Notes |
|--------|------|-------|
| id | bigint PK | |
| name | varchar(255) | |
| slug | varchar(255) UNIQUE | e.g. services.create |
| module | varchar(100) | |

### role_permission (pivot)
| role_id | permissions_id | composite PK |

### user_role (pivot)
| user_id | role_id | composite PK |

---

### settings
| Column | Type | Notes |
|--------|------|-------|
| id | bigint PK | |
| group | varchar(100) | branding, typography, contact, social, footer, seo_defaults, analytics, schema, email, cta |
| key | varchar(255) | |
| value | json | |
| type | varchar(50) | string, text, boolean, json, color, url |
| is_public | boolean | default false — exposed to frontend |
| UNIQUE(group, key) | | |

---

### media_folders
| Column | Type | Notes |
|--------|------|-------|
| id | bigint PK | |
| parent_id | bigint NULL FK → media_folders | |
| name | varchar(255) | |
| slug | varchar(255) | |

### media
| Column | Type | Notes |
|--------|------|-------|
| id | bigint PK | |
| folder_id | bigint NULL FK | |
| disk | varchar(50) | default local |
| path | varchar(500) | |
| filename | varchar(255) | |
| mime_type | varchar(100) | |
| size | bigint | bytes |
| width | int NULL | |
| height | int NULL | |
| alt | varchar(500) NULL | |
| title | varchar(255) NULL | |
| caption | text NULL | |
| description | text NULL | |
| focus_keyword | varchar(255) NULL | |
| webp_path | varchar(500) NULL | |
| avif_path | varchar(500) NULL | |
| optimized_at | timestamp NULL | |
| uploaded_by | bigint NULL FK → users | |

### mediables
| Column | Type | Notes |
|--------|------|-------|
| id | bigint PK | |
| media_id | bigint FK | |
| mediable_type | varchar(255) | |
| mediable_id | bigint | |
| collection | varchar(100) | default, gallery, hero, og |
| sort_order | int default 0 | |
| INDEX(mediable_type, mediable_id) | | |

---

### pages
| Column | Type | Notes |
|--------|------|-------|
| id | bigint PK | |
| title | varchar(255) | |
| slug | varchar(255) UNIQUE | |
| template | varchar(100) | default, homepage, about, contact, legal |
| status | enum(draft,published,archived) | |
| published_at | timestamp NULL | |
| sections | json | page builder blocks |
| parent_id | bigint NULL | |
| sort_order | int default 0 | |
| deleted_at | timestamp NULL | |

---

### service_categories
| id, name, slug UNIQUE, description text, icon varchar(100), sort_order int, timestamps

### services
| id, category_id FK, title, slug UNIQUE, excerpt text, content longtext, sections json, hero_media_id FK NULL, status enum, featured boolean, sort_order, published_at, deleted_at, timestamps

### hire_roles
| id, service_id FK NULL, title, slug UNIQUE, summary text, content longtext, sections json, faq json, hero_media_id FK NULL, status, featured, sort_order, published_at, deleted_at, timestamps

---

### industries
| id, title, slug UNIQUE, icon, excerpt, content longtext, sections json, hero_media_id FK NULL, status, sort_order, published_at, deleted_at, timestamps

### locations
| id, title, slug UNIQUE, type enum(country,region,city), parent_id FK NULL, address text, city, state, postcode, country default AU, latitude decimal(10,8), longitude decimal(11,8), phone, email, sections json, status, published_at, deleted_at, timestamps

---

### blog_categories
| id, name, slug UNIQUE, description, sort_order, timestamps

### blog_tags
| id, name, slug UNIQUE, timestamps

### blog_posts
| id, title, slug UNIQUE, excerpt, content longtext, featured_media_id FK NULL, category_id FK, author_id FK users, status, featured boolean, published_at, deleted_at, timestamps

### blog_post_tag (pivot)

---

### testimonials
| id, name, role, company, quote text, rating tinyint, media_id FK NULL, featured boolean, sort_order, status, timestamps

### case_studies
| id, title, slug UNIQUE, client_name, industry_id FK NULL, excerpt, content longtext, results json, testimonial_id FK NULL, sections json, featured_media_id FK NULL, status, featured, published_at, deleted_at, timestamps

---

### careers
| id, title, slug UNIQUE, department, location, employment_type enum(full-time,part-time,contract), description longtext, requirements longtext, benefits text, status, closes_at NULL, published_at, deleted_at, timestamps

### career_applications
| id, career_id FK, name, email, phone, cover_letter text, resume_media_id FK NULL, data json, status enum(new,reviewing,interview,offer,hired,rejected), pipeline_stage varchar(50), ip_hash, created_at, updated_at

---

### academy_courses
| id, title, slug UNIQUE, level enum(beginner,intermediate,advanced), description, sections json, featured_media_id FK NULL, status, sort_order, published_at, deleted_at, timestamps

### academy_lessons
| id, course_id FK, title, slug, content longtext, video_url NULL, sort_order, status, published_at, timestamps
| UNIQUE(course_id, slug) | |

---

### forms
| id, name, slug UNIQUE, type enum(contact,multistep,lead_magnet), settings json, notification_email, success_message, redirect_url NULL, status, timestamps

### form_fields
| id, form_id FK, type varchar(50), name, label, placeholder, options json, validation json, step int default 1, sort_order, required boolean

### form_submissions
| id, form_id FK, data json, source_url, utm json, ip_hash, user_agent, status enum(new,processed,spam), created_at

### leads
| id, form_submission_id FK NULL, name, email, phone NULL, source, score int default 0, assigned_to FK users NULL, pipeline_stage enum(new,contacted,qualified,proposal,won,lost), notes text, calendly_event_id NULL, created_at, updated_at

---

### seo_meta (polymorphic)
| Column | Type | Notes |
|--------|------|-------|
| id | bigint PK | |
| seo_metable_type | varchar(255) | |
| seo_metable_id | bigint | |
| title | varchar(255) NULL | |
| description | varchar(500) NULL | |
| focus_keyword | varchar(255) NULL | |
| secondary_keywords | json NULL | |
| canonical_url | varchar(500) NULL | |
| og_title, og_description | varchar NULL | |
| og_image_media_id | bigint FK NULL | |
| twitter_card | varchar(50) default summary_large_image | |
| robots | varchar(100) default index,follow | |
| schema_overrides | json NULL | |
| UNIQUE(seo_metable_type, seo_metable_id) | | |

### seo_audits (polymorphic)
| id, auditable_type, auditable_id, score tinyint, parameters json, audited_at, created_at

### redirects
| id, from_path varchar(500) UNIQUE, to_path varchar(500), status_code smallint default 301, is_active boolean, hits bigint default 0, timestamps

### menus
| id, name, slug UNIQUE (header, footer, mobile), timestamps

### menu_items
| id, menu_id FK, parent_id FK NULL, label, url, route_name NULL, route_params json, target enum(_self,_blank), sort_order, is_active

### analytics_events
| id, event_type varchar(100), payload json, session_id varchar(100), page_url, ip_hash, created_at
| INDEX(event_type, created_at) | |

### activity_log
| id, user_id FK NULL, action varchar(100), subject_type, subject_id, properties json, ip_address, created_at

### sitemap_entries
| id, loc varchar(500), type varchar(50), lastmod timestamp, changefreq, priority decimal(2,1), source_type, source_id

---

## Eloquent Morph Map

```php
'morphMap' => [
    'page' => Page::class,
    'service' => Service::class,
    'hire_role' => HireRole::class,
    'industry' => Industry::class,
    'location' => Location::class,
    'blog_post' => BlogPost::class,
    'case_study' => CaseStudy::class,
    'career' => Career::class,
    'academy_course' => AcademyCourse::class,
]
```

---

## Indexes Summary

- Unique slugs on all content tables
- `(status, published_at)` composite on publishable models
- Fulltext via Scout on: blog_posts, services, hire_roles, pages