# Database Migration Analysis - Bikash Barta

This report evaluates the current database schema as defined in Laravel migrations and proposes improvements for scalability, performance, and data integrity.

---

## 1. Summary of Current Schema
The schema consists of 14 migration files covering:
- **Core:** Users, Password Resets, Failed Jobs, Personal Access Tokens.
- **Content:** Categories, Posts, Galleries.
- **Interactions:** Inquiries, Reporter Applications.
- **Configuration:** Settings, FAQs, Legal Pages.
- **Social:** Social Posts (Scraped).

---

## 2. Table-by-Table Evaluation & Improvements

### A. `users` Table
- **Current State:** Standard Laravel user table.
- **Improvements:**
    - Add `role` or `is_super_admin` column to manage different permission levels.
    - Add `avatar` column to support profile pictures in the admin panel.

### B. `categories` Table
- **Current State:** Supports self-referencing `parent_id` for hierarchies.
- **Improvements:**
    - Add an `order` column (Integer) to allow manual sorting of categories in the navigation menu.
    - Add an `is_active` boolean to easily hide categories without deleting them.

### C. `posts` Table
- **Current State:** Basic fields for news content and SEO.
- **Improvements:**
    - **Performance:** Add an index to `status` and `view_count` since these are frequently used for filtering and sorting (e.g., "Trending Stories").
    - **Integrity:** Change `view_count` to `unsignedInteger()`.
    - **Flexibility:** Add a `published_at` timestamp to support scheduled posts.
    - **Audit:** Add a `deleted_at` column for Soft Deletes, preventing accidental loss of news data.
    - **Authoring:** Add a `user_id` (foreign key) to track which admin created the post.

### D. `reporter_applications` Table
- **Current State:** Comprehensive collection of personal and professional details.
- **Improvements:**
    - **Consistency:** Use `string` instead of `json` for `skills` if the list is small, or define a structured enum for `applying_for` (e.g., Block, District).
    - **Indexing:** Add an index to the `status` column for faster filtering in the admin application list.

### E. `social_posts` Table
- **Current State:** Stores scraped embeds and metadata.
- **Improvements:**
    - **Indexing:** Add an index to the `platform` column (youtube, instagram, facebook).
    - **Lifecycle:** Add a `is_active` or `status` column to toggle visibility on the frontend.

### F. `settings` Table
- **Current State:** Simple key-value pair (`setting_key` is primary).
- **Improvements:**
    - **Type Casting:** Add a `type` column (e.g., 'string', 'boolean', 'file') to help the admin UI render the correct input field.

---

## 3. Global Schema Improvements

### 1. Soft Deletes
Implementing `SoftDeletes` across `posts`, `categories`, and `galleries` is highly recommended. News portals often need to recover accidentally deleted content.

### 2. Full-Text Search
For the "Search Logic" identified in the general project analysis, adding a Full-Text index to the `title` and `content` columns in the `posts` table would significantly improve search performance.
```php
$table->fullText(['title', 'content']); // MySQL 5.7+ / PostgreSQL
```

### 3. Missing Indexes
The following columns should be indexed for production performance:
- `posts`: `status`, `created_at`, `view_count`.
- `inquiries`: `status`, `created_at`.
- `reporter_applications`: `status`.

### 4. Foreign Key Constraints
Ensure all tables using `category_id` have `onDelete('set null')` or `onDelete('cascade')` defined correctly. Currently, `posts` uses `onDelete('cascade')`, which might be too aggressive (deleting a category would delete all its news articles). `onDelete('restrict')` or moving articles to a "Uncategorized" category is safer.

---

## 4. Proposed Migration Strategy
1. **New Migration:** Add `SoftDeletes` and missing indexes to `posts`.
2. **New Migration:** Add `role` and `avatar` to `users`.
3. **New Migration:** Add `order` and `is_active` to `categories`.
4. **Optimization:** Run `ANALYZE TABLE` on large tables after indexing.