# Schema Audit — Nxtgauge Database

## Current State Overview

The Nxtgauge database contains **45+ tables** spread across migrations and init-db.sql. Below is the complete audit.

---

## 1. Current Table Inventory

### Identity & Access Control

| Table                       | Status   | Notes                                               |
| --------------------------- | -------- | --------------------------------------------------- |
| `users`                     | ✅ Good  | Has email, phone, password_hash, status, timestamps |
| `refresh_tokens`            | ✅ Good  | Token storage for auth                              |
| `roles`                     | ✅ Good  | Role definitions                                    |
| `role_permissions`          | ✅ Good  | Permission assignments                              |
| `user_roles`                | ✅ Good  | User-role associations                              |
| `employees`                 | ✅ Good  | Internal staff records                              |
| `departments`               | ✅ Good  | Organization structure                              |
| `designations`              | ✅ Good  | Job titles                                          |
| `user_settings`             | ✅ Added | User preferences                                    |
| `account_deletion_requests` | ✅ Added | Deletion tracking                                   |

### User Profiles — DUPLICATED STRUCTURE (PROBLEM)

| Table                           | Status        | Issue                                          |
| ------------------------------- | ------------- | ---------------------------------------------- |
| `photographer_profiles`         | ⚠️            | Has duplicated common fields                   |
| `tutor_profiles`                | ⚠️            | Has duplicated common fields                   |
| `makeup_artist_profiles`        | ⚠️            | Has duplicated common fields                   |
| `developer_profiles`            | ⚠️            | Has external links (github_url, portfolio_url) |
| `video_editor_profiles`         | ⚠️            | Has external links (reel_url)                  |
| `graphic_designer_profiles`     | ⚠️            | Has external links (portfolio_url)             |
| `social_media_manager_profiles` | ⚠️            | Has duplicated common fields                   |
| `fitness_trainer_profiles`      | ⚠️            | Has duplicated common fields                   |
| `catering_service_profiles`     | ⚠️            | Has duplicated common fields                   |
| `ugc_content_creator_profiles`  | ⚠️            | Has duplicated common fields                   |
| `company_profiles`              | ⚠️            | Has duplicated fields                          |
| `customer_profiles`             | ⚠️            | Has duplicated fields                          |
| `job_seeker_profiles`           | ⚠️            | Has duplicated fields                          |
| `professionals`                 | ❌ Deprecated | Old root table, references user_id             |

### Portfolio Domain

| Table              | Status  | Issue                                          |
| ------------------ | ------- | ---------------------------------------------- |
| `portfolio_items`  | ⚠️      | References `professionals` table, needs update |
| `portfolio_images` | ✅ Good | Simple image storage                           |
| `services`         | ⚠️      | References `professionals` table, needs update |

### Marketplace

| Table           | Status  | Notes                                   |
| --------------- | ------- | --------------------------------------- |
| `jobs`          | ✅ Good | Job postings                            |
| `applications`  | ⚠️      | Should be renamed to `job_applications` |
| `requirements`  | ⚠️      | Should be renamed to `leads`            |
| `lead_requests` | ⚠️      | References `professionals` table        |
| `reviews`       | ✅ Good | Reviews system                          |

### Verification & Approval (Mixed with Onboarding)

| Table                    | Status    | Issue                    |
| ------------------------ | --------- | ------------------------ |
| `onboarding_submissions` | ❌ Legacy | Tied to onboarding flow  |
| `submission_documents`   | ❌ Legacy | Tied to onboarding       |
| `onboarding_states`      | ❌ Legacy | Tied to onboarding       |
| `onboarding_configs`     | ❌ Legacy | Tied to onboarding       |
| `verifications`          | ⚠️        | Basic verification table |
| `verification_logs`      | ⚠️        | Basic verification logs  |

### Finance Domain

| Table               | Status  | Notes                          |
| ------------------- | ------- | ------------------------------ |
| `tracecoin_wallets` | ✅ Good | Wallet per user                |
| `tracecoin_ledger`  | ✅ Good | Immutable ledger               |
| `pricing_packages`  | ✅ Good | Package definitions            |
| `payments`          | ✅ Good | Payment records                |
| `invoices`          | ✅ Good | Invoice records                |
| `coupons`           | ✅ Good | Coupon system                  |
| `coupon_uses`       | ⚠️      | Should be `coupon_redemptions` |
| `discounts`         | ✅ Good | Discount rules                 |

### Communication & Support

| Table                     | Status  | Notes                |
| ------------------------- | ------- | -------------------- |
| `notifications`           | ✅ Good | In-app notifications |
| `email_logs`              | ✅ Good | Email audit trail    |
| `support_tickets`         | ✅ Good | Support system       |
| `support_ticket_messages` | ✅ Good | Ticket messages      |

### Knowledge Base

| Table           | Status  | Notes                                       |
| --------------- | ------- | ------------------------------------------- |
| `kb_categories` | ✅ Good | KB categories                               |
| `kb_articles`   | ⚠️      | Missing `section_id`, `kb_article_feedback` |

### Dashboard & Config

| Table               | Status  | Notes                    |
| ------------------- | ------- | ------------------------ |
| `dashboard_configs` | ✅ Good | Dashboard configurations |
| `runtime_configs`   | ✅ Good | Runtime feature flags    |

### Audit & Logging

| Table               | Status     | Issue                     |
| ------------------- | ---------- | ------------------------- |
| `activity_logs`     | ⚠️         | Basic logging, incomplete |
| `audit_logs`        | ❌ Missing | Not implemented           |
| `audit_log_changes` | ❌ Missing | Not implemented           |

---

## 2. Problems Identified

### Problem 1: Duplicated Profile Fields

Every profile table has these duplicated fields:

- `display_name`, `bio`, `location`, `status`, `rejection_reason`, `approved_at`

**Impact**: Data redundancy, inconsistent updates, maintenance burden.

### Problem 2: Extension Tables Reference `user_id` Instead of Root Profile

Current: `photographer_profiles.user_id` → `users.id`
Target: `photographer_profiles.user_role_profile_id` → `user_role_profiles.id`

**Impact**: Cannot support multiple role profiles per user properly.

### Problem 3: External Portfolio Links

Tables with forbidden external links:

- `developer_profiles`: `github_url`, `portfolio_url`
- `video_editor_profiles`: `reel_url`
- `graphic_designer_profiles`: `portfolio_url`

**Impact**: Violates platform-native portfolio requirement.

### Problem 4: Table Names Not Aligned with Target

| Current        | Target               |
| -------------- | -------------------- |
| `applications` | `job_applications`   |
| `requirements` | `leads`              |
| `coupon_uses`  | `coupon_redemptions` |

### Problem 5: Missing Root Profile Table

`user_role_profiles` does not exist. Users cannot have multiple role profiles properly.

### Problem 6: Missing Audit Infrastructure

- No `audit_logs` table
- No `audit_log_changes` table
- Current `activity_logs` is incomplete

### Problem 7: Verification/Approval Tied to Onboarding

- `onboarding_submissions`, `onboarding_configs`, `onboarding_states` are legacy
- Need separate `verification_requests`, `approval_requests`

### Problem 8: Incomplete Knowledge Base

- Missing `kb_sections`
- Missing `kb_article_feedback`
- Missing `kb_article_related`

### Problem 9: Missing Finance Tables

- No `orders` / `order_items`
- No `tax_rules`
- No `payment_transactions`
- No `payment_gateway_configs`

---

## 3. Page-Based Anti-Patterns

The schema was influenced by admin pages rather than domain entities:

| Page                  | Problem Table(s)                                                    |
| --------------------- | ------------------------------------------------------------------- |
| Onboarding Management | `onboarding_submissions`, `onboarding_configs`, `onboarding_states` |
| Account Settings      | Should be in `user_settings`                                        |
| Reviews               | Mix of UI and domain                                                |

---

## 4. Inconsistent Naming

| Issue                   | Examples                                                    |
| ----------------------- | ----------------------------------------------------------- |
| Mixed naming            | `job_type` vs `employment_type`                             |
| Inconsistent timestamps | Some tables have `created_at`, some don't have `updated_at` |
| UUID vs text            | Some IDs are UUID, some referenced tables use text          |

---

## 5. Recommendations

1. **Create `user_role_profiles`** as the root profile table
2. **Update extension tables** to reference `user_role_profile_id`
3. **Remove external links** from profile tables
4. **Rename tables** to match target schema
5. **Create audit tables** for compliance
6. **Separate verification from approval** domains
7. **Add missing finance tables** for proper order management
8. **Complete KB structure** with sections and feedback