# Database — Said Yaqub Sadat Personal Portfolio

Engine: **MySQL 8** (InnoDB, `utf8mb4_unicode_ci`).
All schema changes are Laravel migrations in `backend/database/migrations`.
The development database is `portfolio_db`; the automated test suite uses a separate
`portfolio_test` database (see [testing.md](./testing.md#2-running-the-suite)).

## 1. Entity relationship overview

```
users ──┬── personal_access_tokens
        └── activity_logs

projects ──< project_images          (cascade on delete)

settings        (key/value site configuration)
skills          (self-contained)
experiences     (self-contained)
education       (self-contained)
certificates    (self-contained)
messages        (contact submissions)
```

`project_images` is the only table with a foreign key besides the two that point at
`users`. The content tables (skills, experiences, education, certificates) are
independent documents ordered by `sort_order`.

## 2. Tables

### 2.1 `users`

| Column | Type | Notes |
|---|---|---|
| `id` | bigint unsigned PK | |
| `name` | varchar(255) | |
| `email` | varchar(255) unique | sign-in identifier |
| `email_verified_at` | timestamp null | |
| `password` | varchar(255) | bcrypt hash (Laravel `hashed` cast) — never plain text |
| `remember_token` | varchar(100) null | |
| `is_admin` | tinyint(1) default 0 | gates the dashboard |
| `created_at`, `updated_at` | timestamp null | |

### 2.2 `personal_access_tokens` (Laravel Sanctum)

Standard Sanctum table: `tokenable_type`, `tokenable_id`, `name`, hashed `token`,
`abilities`, `last_used_at`, `expires_at`, timestamps. `token` is unique and indexed.

### 2.3 `projects`

| Column | Type | Notes |
|---|---|---|
| `id` | bigint unsigned PK | |
| `title` | varchar(255) | |
| `status` | varchar(255) default `published` | indexed; `draft` \| `published` \| `archived` |
| `slug` | varchar(255) **unique** | public detail URL |
| `description` | text null | long description |
| `short_description` | varchar(500) null | used on cards |
| `problem` | text null | case study — problem statement |
| `solution` | text null | case study — solution |
| `challenges` | text null | case study — challenges |
| `results` | text null | case study — verified results |
| `start_date`, `end_date` | date null | |
| `published_at` | timestamp null | set when a project goes live |
| `category` | varchar(255) null | free text, seeded from the CV |
| `role` | varchar(255) null | role on the project |
| `tech_stack` | longtext (JSON) null | `["Laravel","React","MySQL"]`, `json_valid` check |
| `image` | varchar(255) null | cover image path on the public disk |
| `live_url`, `repo_url` | varchar(255) null | optional; left empty when not available |
| `featured` | tinyint(1) default 0 | highlighted on the home page |
| `sort_order` | int unsigned default 0 | manual ordering |
| `created_at`, `updated_at` | timestamp null | |

Indexes: unique `slug`, index `status`.

### 2.4 `project_images`

| Column | Type | Notes |
|---|---|---|
| `id` | bigint unsigned PK | |
| `project_id` | bigint unsigned | FK → `projects.id`, `ON DELETE CASCADE` |
| `path` | varchar(255) | path on the public disk, never a binary |
| `caption` | varchar(255) null | |
| `alt_text` | varchar(255) null | accessibility |
| `sort_order` | int unsigned default 0 | gallery order |
| `created_at`, `updated_at` | timestamp null | |

Index: `(project_id, sort_order)`.

### 2.5 `skills`

| Column | Type | Notes |
|---|---|---|
| `id` | bigint unsigned PK | |
| `name` | varchar(255) | |
| `category` | varchar(255) default `General` | Backend / Frontend / Database / Workflow |
| `level` | tinyint unsigned default 80 | 0–100, used for the progress bar |
| `proficiency` | varchar(40) default `Proficient` | honest text label |
| `description` | text null | |
| `sort_order` | int unsigned default 0 | |
| `created_at`, `updated_at` | timestamp null | |

### 2.6 `experiences`

`role`, `company`, `location`, `start_date` (varchar, e.g. "2023"), `end_date`
(nullable for a current role), `current` (boolean), `highlights` (JSON array of
responsibility bullets), `sort_order`, timestamps.

### 2.7 `education`

`institution`, `degree`, `period`, `description`, `sort_order`, timestamps.

### 2.8 `certificates`

| Column | Type | Notes |
|---|---|---|
| `id` | bigint unsigned PK | |
| `title` | varchar(255) | credential title |
| `issuer` | varchar(255) | indexed |
| `issue_date` | date null | |
| `credential_id` | varchar(255) null | |
| `url` | varchar(255) null | verification link (only when it exists) |
| `file_path` | varchar(255) null | optional uploaded document |
| `description` | text null | |
| `sort_order` | int unsigned default 0 | |
| `created_at`, `updated_at` | timestamp null | |

### 2.9 `messages`

`name`, `email`, `subject` (nullable), `message` (text), `read` (boolean),
`status` (`new`, `read`, `replied`, `archived`), `replied_at` (timestamp null),
`notes` (text null, internal only), timestamps. Index on `status`.

### 2.10 `activity_logs`

`user_id` (nullable FK → `users.id`, `ON DELETE SET NULL`), `action`
(e.g. `project.created`, `auth.login`, `media.uploaded`), `subject_type`,
`subject_id`, `description`, `ip_address`, `created_at`. Indexes on
`(subject_type, subject_id)` and `created_at`. The table has no `updated_at`
because audit rows are immutable.

### 2.11 `settings`

`key` (unique), `value` (text), timestamps. Holds the editable site configuration:
name, title, tagline, summary, bio, focus, values, location, email, phone,
nationality, photo, `cv_path`, `social_github`, `social_linkedin`,
`social_portfolio`. Writes through the dashboard are restricted to this whitelist
(`AdminSettingController::ALLOWED`).

### 2.12 Framework tables

`cache`, `cache_locks`, `jobs`, `failed_jobs` — created by the default Laravel
migrations and used by the cache, queue and session drivers.

## 3. Migrations

| Migration | Purpose |
|---|---|
| `0001_01_01_000000_create_users_table` | users, password resets, sessions |
| `0001_01_01_000001_create_cache_table` | cache + cache locks |
| `0001_01_01_000002_create_jobs_table` | queue tables |
| `2026_09_08_090227_create_personal_access_tokens_table` | Sanctum |
| `2026_09_08_100104_create_skills_table` | skills |
| `2026_09_08_100105_create_projects_table` | projects (initial) |
| `2026_09_08_100106_create_experiences_table` | experiences |
| `2026_09_08_100107_create_education_table` | education |
| `2026_09_08_100108_create_messages_table` | contact messages |
| `2026_09_08_100109_create_settings_table` | settings |
| `2026_09_08_101103_add_is_admin_to_users_table` | administrator flag |
| `2026_10_22_100000_add_publication_fields_to_projects_table` | status, slug, case study, dates, featured… |
| `2026_10_22_100001_create_certificates_table` | certificates |
| `2026_10_22_100002_create_project_images_table` | screenshots |
| `2026_10_22_100003_create_activity_logs_table` | audit trail |
| `2026_10_22_100004_add_metadata_to_skills_and_messages_tables` | proficiency/description, message status/notes/replied_at |

Apply or roll back with:

```bash
php artisan migrate            # apply
php artisan migrate:rollback   # undo the last batch
php artisan migrate:fresh --seed   # rebuild from scratch with seeded content
```

## 4. Seeders

| Seeder | Content |
|---|---|
| `AdminUserSeeder` | Administrator account, created from `ADMIN_EMAIL` / `ADMIN_PASSWORD`. Skipped with a warning when the values are empty |
| `SettingsSeeder` | Profile, contact and social settings |
| `SkillSeeder` | Skill categories and proficiency taken from the CV |
| `ProjectSeeder` | The eight projects described in `Cv.pdf`, with case-study text, roles, categories, technology stacks and the live URL of the Comrade Sadat system |
| `ExperienceSeeder` | Employment history from the CV |
| `EducationSeeder` | Education entries from the CV |
| `CertificateSeeder` | Certificates from the CV |

`DatabaseSeeder` calls all of them. After re-seeding run `php artisan cache:clear`,
because `Setting::get()` caches values indefinitely.

## 5. Specification mapping

| Specification §11 table | Implementation | Reason |
|---|---|---|
| `users` | `users` | identical purpose |
| `profile_settings` | `settings` (key/value) | the profile is a small set of editable values; a dedicated table would only add boilerplate |
| `skills` | `skills` | identical |
| `experiences` | `experiences` | identical |
| `educations` | `education` | singular table name, same content |
| `certificates` | `certificates` | identical |
| `projects` | `projects` | extended with status, slug, case-study sections, featured flag |
| `project_categories` + `project_category` | `projects.category` (string) | one category per project; a separate definition table was not needed for three categories |
| `project_technologies` | `projects.tech_stack` (JSON array) | technologies are a short, non-relational tag list |
| `project_images` | `project_images` | identical |
| `contacts` | `messages` | same content plus a status workflow |
| `site_settings` | `settings` | key/value configuration |
| `activity_logs` | `activity_logs` | identical |
| `blog_posts`, `blog_categories` | — | optional feature, not implemented |

## 6. Media storage

Binary files are never stored in MySQL.

| Kind | Location | Public URL |
|---|---|---|
| Project screenshots / cover images | `backend/storage/app/public/projects/` | `https://<api-host>/storage/projects/<file>` |
| Certificates | `backend/storage/app/public/certificates/` | `https://<api-host>/storage/certificates/<file>` |
| CV | `backend/storage/app/public/cv/` | `https://<api-host>/cv` (stable download route) or `https://<api-host>/api/cv` (API alias used by the SPA) |
| Profile photo | `backend/storage/app/public/profile/` | `https://<api-host>/storage/profile/<file>` |

`php artisan storage:link` publishes `storage/app/public` as `public/storage`.
On Windows the link already exists as a directory junction — `storage:link` reports
"link already exists", which is harmless.

## 7. Backup and restore

```bash
# Database
mysqldump -u root -p --single-transaction portfolio_db > backup_$(date +%F).sql

# Media
tar -czf media_$(date +%F).tar.gz backend/storage/app/public

# Restore
mysql -u root -p portfolio_db < backup_2026-01-01.sql
tar -xzf media_2026-01-01.tar.gz -C backend/storage/app/public
```

The full procedure, including scheduling and a restore drill, is in
[deployment.md](./deployment.md#7-backup-and-restore).