nxtgauge-backend-rust/docs/schema_audit.md

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