ashwin/nxtgauge-backend-rust
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions
nxtgauge-backend-rust/docs/target_schema.md
Tracewebstudio Dev 03376b9567 feat: Add database redesign documentation and Phase 1-2 migrations 
- Add schema_audit.md documenting current schema issues
- Add target_schema.md with complete target schema design
- Add old_to_new_mapping.md with table mapping
- Add migration_plan.md with phased migration strategy
- Add Phase 1 migrations (core infrastructure):
  - user_sessions table
  - users missing columns
  - departments updates
  - designations updates
  - employees updates
- Add Phase 2 migrations (profile domain - CRITICAL):
  - create user_role_profiles root table
  - backfill user_role_profiles from existing profiles
  - add user_role_profile_id to extension tables
  - remove forbidden external portfolio links
- Add user_role_profile Rust model
- Update photographer model to use user_role_profile_id
2026-04-12 23:21:11 +02:00

1052 lines
50 KiB
Markdown
Raw Permalink Blame History

# Target Schema — Nxtgauge Database
This document defines the **target state** for the Nxtgauge PostgreSQL schema based on the single source of truth.
---
## 1. Identity & Access Control
### users
| Column | Type | Notes |
| ------------- | --------- | -------------------------- |
| id | UUID | Primary key |
| email | TEXT | Unique |
| phone | TEXT | Unique, nullable |
| password_hash | TEXT | |
| account_type | TEXT | INDIVIDUAL, COMPANY |
| status | TEXT | ACTIVE, PENDING, SUSPENDED |
| last_login_at | TIMESTAMP | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### refresh_tokens
| Column | Type | Notes |
| ---------- | --------- | ----------- |
| id | UUID | Primary key |
| user_id | UUID | FK → users |
| token_hash | TEXT | Unique |
| expires_at | TIMESTAMP | |
| revoked | BOOLEAN | |
| created_at | TIMESTAMP | |
### user_sessions
| Column | Type | Notes |
| ------------- | --------- | ----------- |
| id | UUID | Primary key |
| user_id | UUID | FK → users |
| session_token | TEXT | Unique |
| ip_address | TEXT | |
| user_agent | TEXT | |
| expires_at | TIMESTAMP | |
| created_at | TIMESTAMP | |
### user_settings
| Column | Type | Notes |
| ------------- | --------- | ------------------ |
| id | UUID | Primary key |
| user_id | UUID | FK → users, Unique |
| settings_json | JSONB | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### roles
| Column | Type | Notes |
| -------------------------- | --------- | ------------------ |
| id | UUID | Primary key |
| key | TEXT | Unique |
| name | TEXT | |
| audience | TEXT | INTERNAL, EXTERNAL |
| description | TEXT | |
| department_id | UUID | FK → departments |
| can_approve_requests | BOOLEAN | |
| can_manage_system_settings | BOOLEAN | |
| is_active | BOOLEAN | |
| created_at | TIMESTAMP | |
### permissions
| Column | Type | Notes |
| ----------- | --------- | ----------- |
| id | UUID | Primary key |
| key | TEXT | Unique |
| name | TEXT | |
| description | TEXT | |
| created_at | TIMESTAMP | |
### role_permissions
| Column | Type | Notes |
| -------------- | --------- | ---------------- |
| id | UUID | Primary key |
| role_id | UUID | FK → roles |
| permission_key | TEXT | FK → permissions |
| created_at | TIMESTAMP | |
### user_roles
| Column | Type | Notes |
| ----------- | --------- | --------------------------- |
| id | UUID | Primary key |
| user_id | UUID | FK → users |
| role_id | UUID | FK → roles |
| status | TEXT | PENDING, APPROVED, REJECTED |
| approved_at | TIMESTAMP | |
| created_at | TIMESTAMP | |
### employees
| Column | Type | Notes |
| ------------------- | --------- | ---------------------------- |
| id | UUID | Primary key |
| user_id | UUID | FK → users, Unique |
| employee_code | TEXT | |
| department_id | UUID | FK → departments |
| designation_id | UUID | FK → designations |
| joining_date | DATE | |
| employment_status | TEXT | ACTIVE, INACTIVE, TERMINATED |
| manager_employee_id | UUID | Self-reference |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### departments
| Column | Type | Notes |
| ----------------- | --------- | ----------- |
| id | UUID | Primary key |
| name | TEXT | Unique |
| code | TEXT | Unique |
| description | TEXT | |
| department_head | TEXT | |
| department_email | TEXT | |
| is_active | BOOLEAN | |
| visibility | TEXT | INTERNAL |
| transfers_enabled | BOOLEAN | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### designations
| Column | Type | Notes |
| --------------- | --------- | ---------------- |
| id | UUID | Primary key |
| name | TEXT | Unique |
| code | TEXT | Unique |
| department_id | UUID | FK → departments |
| description | TEXT | |
| level | TEXT | |
| can_manage_team | BOOLEAN | |
| can_approve | BOOLEAN | |
| is_active | BOOLEAN | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
---
## 2. User Role Profiles (NEW ROOT)
### user_role_profiles
| Column | Type | Notes |
| ------------------- | --------- | ------------------------------------ |
| id | UUID | Primary key |
| user_id | UUID | FK → users |
| role_key | TEXT | photographer, tutor, developer, etc. |
| display_name | TEXT | |
| bio | TEXT | |
| location | TEXT | |
| avatar_url | TEXT | |
| phone | TEXT | |
| email | TEXT | |
| status | TEXT | DRAFT, ACTIVE, SUSPENDED |
| verification_status | TEXT | PENDING, VERIFIED, REJECTED |
| approval_status | TEXT | PENDING, APPROVED, REJECTED |
| rejection_reason | TEXT | |
| approved_at | TIMESTAMP | |
| verified_at | TIMESTAMP | |
| is_profile_public | BOOLEAN | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
**Indexes:**
- `UNIQUE(user_id, role_key)`
- `INDEX(status)`
- `INDEX(verification_status)`
- `INDEX(approval_status)`
---
## 3. Role Extension Tables
### photographer_profiles
| Column | Type | Notes |
| -------------------- | --------- | ------------------------------- |
| id | UUID | Primary key |
| user_role_profile_id | UUID | FK → user_role_profiles, Unique |
| specialties | TEXT[] | |
| camera_brands | TEXT[] | |
| studio_available | BOOLEAN | |
| outdoor_shoots | BOOLEAN | |
| travel_radius_km | INTEGER | |
| starting_price_inr | INTEGER | in paise |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### tutor_profiles
| Column | Type | Notes |
| -------------------- | --------- | ------------------------------- |
| id | UUID | Primary key |
| user_role_profile_id | UUID | FK → user_role_profiles, Unique |
| subjects | TEXT[] | |
| board_types | TEXT[] | |
| qualification | TEXT | |
| teaches_online | BOOLEAN | |
| teaches_offline | BOOLEAN | |
| experience_years | INTEGER | |
| hourly_rate_inr | INTEGER | in paise |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### makeup_artist_profiles
| Column | Type | Notes |
| -------------------- | --------- | ------------------------------- |
| id | UUID | Primary key |
| user_role_profile_id | UUID | FK → user_role_profiles, Unique |
| specializations | TEXT[] | |
| kit_brands | TEXT[] | |
| home_service | BOOLEAN | |
| studio_available | BOOLEAN | |
| starting_price_inr | INTEGER | in paise |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### developer_profiles
| Column | Type | Notes |
| -------------------- | --------- | ------------------------------- |
| id | UUID | Primary key |
| user_role_profile_id | UUID | FK → user_role_profiles, Unique |
| tech_stack | TEXT[] | |
| experience_years | INTEGER | |
| availability | TEXT | FULL_TIME, PART_TIME, FREELANCE |
| hourly_rate_inr | INTEGER | in paise |
| remote_ok | BOOLEAN | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
**NO external links (github_url, portfolio_url removed)**
### video_editor_profiles
| Column | Type | Notes |
| -------------------- | --------- | ------------------------------- |
| id | UUID | Primary key |
| user_role_profile_id | UUID | FK → user_role_profiles, Unique |
| software_skills | TEXT[] | |
| style_tags | TEXT[] | |
| turnaround_days | INTEGER | |
| starting_price_inr | INTEGER | in paise |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
**NO reel_url**
### graphic_designer_profiles
| Column | Type | Notes |
| -------------------- | --------- | ------------------------------- |
| id | UUID | Primary key |
| user_role_profile_id | UUID | FK → user_role_profiles, Unique |
| design_tools | TEXT[] | |
| style_tags | TEXT[] | |
| brand_experience | BOOLEAN | |
| starting_price_inr | INTEGER | in paise |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
**NO portfolio_url**
### social_media_manager_profiles
| Column | Type | Notes |
| ----------------------- | --------- | ------------------------------- |
| id | UUID | Primary key |
| user_role_profile_id | UUID | FK → user_role_profiles, Unique |
| platforms | TEXT[] | |
| industries | TEXT[] | |
| content_types | TEXT[] | |
| avg_follower_growth_pct | INTEGER | |
| starting_price_inr | INTEGER | in paise |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### fitness_trainer_profiles
| Column | Type | Notes |
| -------------------- | --------- | ------------------------------- |
| id | UUID | Primary key |
| user_role_profile_id | UUID | FK → user_role_profiles, Unique |
| disciplines | TEXT[] | |
| certifications | TEXT[] | |
| online_sessions | BOOLEAN | |
| home_visits | BOOLEAN | |
| gym_based | BOOLEAN | |
| per_session_rate_inr | INTEGER | in paise |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### catering_service_profiles
| Column | Type | Notes |
| -------------------- | --------- | ------------------------------- |
| id | UUID | Primary key |
| user_role_profile_id | UUID | FK → user_role_profiles, Unique |
| business_name | TEXT | |
| cuisine_types | TEXT[] | |
| event_types | TEXT[] | |
| min_guests | INTEGER | |
| max_guests | INTEGER | |
| has_setup_team | BOOLEAN | |
| has_serving_staff | BOOLEAN | |
| price_per_head_inr | INTEGER | in paise |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### ugc_content_creator_profiles
| Column | Type | Notes |
| -------------------- | --------- | ------------------------------- |
| id | UUID | Primary key |
| user_role_profile_id | UUID | FK → user_role_profiles, Unique |
| niche_tags | TEXT[] | |
| content_formats | TEXT[] | |
| platforms | TEXT[] | |
| turnaround_days | INTEGER | |
| starting_price_inr | INTEGER | in paise |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
---
## 4. Other Profiles
### company_profiles
| Column | Type | Notes |
| ------------------- | --------- | ------------------ |
| id | UUID | Primary key |
| user_id | UUID | FK → users, Unique |
| company_name | TEXT | |
| business_type | TEXT | |
| industry | TEXT | |
| contact_person_name | TEXT | |
| email | TEXT | |
| phone | TEXT | |
| location | TEXT | |
| bio | TEXT | |
| status | TEXT | |
| verification_status | TEXT | |
| approval_status | TEXT | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### customer_profiles
| Column | Type | Notes |
| ------------ | --------- | ------------------ |
| id | UUID | Primary key |
| user_id | UUID | FK → users, Unique |
| display_name | TEXT | |
| phone | TEXT | |
| email | TEXT | |
| location | TEXT | |
| status | TEXT | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### candidate_profiles
| Column | Type | Notes |
| ------------------- | --------- | ------------------ |
| id | UUID | Primary key |
| user_id | UUID | FK → users, Unique |
| display_name | TEXT | |
| bio | TEXT | |
| location | TEXT | |
| experience_years | INTEGER | |
| preferred_roles | TEXT[] | |
| expected_salary_inr | INTEGER | |
| resume_file_url | TEXT | |
| status | TEXT | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
---
## 5. Portfolio Domain
### portfolio_items
| Column | Type | Notes |
| -------------------- | --------- | ----------------------- |
| id | UUID | Primary key |
| user_role_profile_id | UUID | FK → user_role_profiles |
| title | TEXT | |
| description | TEXT | |
| tags | TEXT[] | |
| display_order | INTEGER | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
**NO external links**
### portfolio_images
| Column | Type | Notes |
| ----------------- | --------- | -------------------- |
| id | UUID | Primary key |
| portfolio_item_id | UUID | FK → portfolio_items |
| file_url | TEXT | |
| display_order | INTEGER | |
| created_at | TIMESTAMP | |
### services
| Column | Type | Notes |
| -------------------- | --------- | ----------------------- |
| id | UUID | Primary key |
| user_role_profile_id | UUID | FK → user_role_profiles |
| name | TEXT | |
| description | TEXT | |
| price | INTEGER | in paise |
| duration_minutes | INTEGER | |
| is_active | BOOLEAN | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
---
## 6. Verification Domain
### verification_requests
| Column | Type | Notes |
| -------------------- | --------- | ---------------------------- |
| id | UUID | Primary key |
| user_role_profile_id | UUID | FK → user_role_profiles |
| verification_type | TEXT | IDENTITY, BUSINESS, DOCUMENT |
| status | TEXT | PENDING, APPROVED, REJECTED |
| submitted_at | TIMESTAMP | |
| reviewed_at | TIMESTAMP | |
| reviewed_by_user_id | UUID | FK → users |
| remarks | TEXT | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### verification_documents
| Column | Type | Notes |
| ----------------------- | --------- | --------------------------- |
| id | UUID | Primary key |
| verification_request_id | UUID | FK → verification_requests |
| document_type | TEXT | |
| file_url | TEXT | |
| file_name | TEXT | |
| mime_type | TEXT | |
| status | TEXT | PENDING, APPROVED, REJECTED |
| uploaded_at | TIMESTAMP | |
| reviewed_at | TIMESTAMP | |
| reviewed_by_user_id | UUID | FK → users |
| remarks | TEXT | |
| created_at | TIMESTAMP | |
### verification_logs
| Column | Type | Notes |
| ----------------------- | --------- | ------------------------------------------------- |
| id | UUID | Primary key |
| verification_request_id | UUID | FK → verification_requests |
| action | TEXT | SUBMITTED, APPROVED, REJECTED, DOCUMENT_REQUESTED |
| old_status | TEXT | |
| new_status | TEXT | |
| acted_by_user_id | UUID | FK → users |
| remarks | TEXT | |
| created_at | TIMESTAMP | |
---
## 7. Approval Domain
### approval_requests
| Column | Type | Notes |
| -------------------- | --------- | ------------------------------ |
| id | UUID | Primary key |
| entity_type | TEXT | job, lead, profile, company |
| entity_id | UUID | |
| approval_type | TEXT | CONTENT, VERIFICATION, FEATURE |
| status | TEXT | PENDING, APPROVED, REJECTED |
| submitted_by_user_id | UUID | FK → users |
| reviewed_by_user_id | UUID | FK → users |
| submitted_at | TIMESTAMP | |
| reviewed_at | TIMESTAMP | |
| remarks | TEXT | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### approval_logs
| Column | Type | Notes |
| ------------------- | --------- | ----------------------------- |
| id | UUID | Primary key |
| approval_request_id | UUID | FK → approval_requests |
| action | TEXT | SUBMITTED, APPROVED, REJECTED |
| old_status | TEXT | |
| new_status | TEXT | |
| acted_by_user_id | UUID | FK → users |
| remarks | TEXT | |
| created_at | TIMESTAMP | |
---
## 8. Marketplace Domain
### jobs
| Column | Type | Notes |
| ------------------ | --------- | ------------------------------ |
| id | UUID | Primary key |
| company_profile_id | UUID | FK → company_profiles |
| posted_by_user_id | UUID | FK → users |
| title | TEXT | |
| description | TEXT | |
| location | TEXT | |
| employment_type | TEXT | FULL_TIME, PART_TIME, CONTRACT |
| mode_of_work | TEXT | ONSITE, REMOTE, HYBRID |
| budget_inr | INTEGER | |
| salary_range_json | JSONB | |
| status | TEXT | DRAFT, PENDING, LIVE, CLOSED |
| approved_at | TIMESTAMP | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### job_applications (RENAMED from applications)
| Column | Type | Notes |
| ----------------- | --------- | -------------------------------------------------- |
| id | UUID | Primary key |
| job_id | UUID | FK → jobs |
| applicant_user_id | UUID | FK → users |
| status | TEXT | APPLIED, SHORTLISTED, INTERVIEW, OFFERED, REJECTED |
| cover_note | TEXT | |
| applied_at | TIMESTAMP | |
| created_at | TIMESTAMP | |
### leads (RENAMED from requirements)
| Column | Type | Notes |
| ------------------ | --------- | ---------------------------- |
| id | UUID | Primary key |
| created_by_user_id | UUID | FK → users |
| profession_key | TEXT | |
| title | TEXT | |
| description | TEXT | |
| location | TEXT | |
| budget_inr | INTEGER | |
| required_date | DATE | |
| status | TEXT | DRAFT, PENDING, OPEN, CLOSED |
| approved_at | TIMESTAMP | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### lead_requests
| Column | Type | Notes |
| -------------------- | --------- | --------------------------- |
| id | UUID | Primary key |
| lead_id | UUID | FK → leads |
| user_role_profile_id | UUID | FK → user_role_profiles |
| status | TEXT | PENDING, ACCEPTED, REJECTED |
| remarks | TEXT | |
| requested_at | TIMESTAMP | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### reviews
| Column | Type | Notes |
| ---------------- | --------- | -------------------------- |
| id | UUID | Primary key |
| reviewer_user_id | UUID | FK → users |
| entity_type | TEXT | professional, company |
| entity_id | UUID | |
| rating | SMALLINT | 1-5 |
| review_text | TEXT | |
| status | TEXT | PENDING, PUBLISHED, HIDDEN |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
---
## 9. Finance Domain
### pricing_packages
| Column | Type | Notes |
| ------------------- | --------- | -------------------------------------------- |
| id | UUID | Primary key |
| name | TEXT | |
| description | TEXT | |
| package_type | TEXT | JOB_POSTING, CONTACT_VIEWS, TRACECOIN_BUNDLE |
| price_inr | INTEGER | in paise |
| tracecoins_included | INTEGER | |
| validity_days | INTEGER | |
| is_active | BOOLEAN | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### tracecoin_wallets
| Column | Type | Notes |
| --------------- | --------- | ------------------ |
| id | UUID | Primary key |
| user_id | UUID | FK → users, Unique |
| current_balance | INTEGER | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### tracecoin_ledger (IMMUTABLE)
| Column | Type | Notes |
| ---------------- | --------- | -------------------------- |
| id | UUID | Primary key |
| wallet_id | UUID | FK → tracecoin_wallets |
| transaction_type | TEXT | CREDIT, DEBIT |
| amount | INTEGER | |
| balance_after | INTEGER | |
| reference_type | TEXT | JOB, LEAD, PURCHASE, BONUS |
| reference_id | UUID | |
| remarks | TEXT | |
| created_at | TIMESTAMP | |
### orders
| Column | Type | Notes |
| ------------ | --------- | ----------------------------- |
| id | UUID | Primary key |
| user_id | UUID | FK → users |
| order_type | TEXT | PACKAGE, SERVICE |
| subtotal_inr | INTEGER | |
| discount_inr | INTEGER | |
| tax_inr | INTEGER | |
| total_inr | INTEGER | |
| status | TEXT | PENDING, COMPLETED, CANCELLED |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### order_items
| Column | Type | Notes |
| --------------- | --------- | ---------------- |
| id | UUID | Primary key |
| order_id | UUID | FK → orders |
| item_type | TEXT | PACKAGE, SERVICE |
| item_id | UUID | |
| item_name | TEXT | |
| quantity | INTEGER | |
| unit_price_inr | INTEGER | |
| total_price_inr | INTEGER | |
| created_at | TIMESTAMP | |
### invoices
| Column | Type | Notes |
| -------------- | --------- | ------------ |
| id | UUID | Primary key |
| order_id | UUID | FK → orders |
| user_id | UUID | FK → users |
| invoice_number | TEXT | Unique |
| subtotal_inr | INTEGER | |
| discount_inr | INTEGER | |
| tax_inr | INTEGER | |
| total_inr | INTEGER | |
| status | TEXT | ISSUED, PAID |
| issued_at | TIMESTAMP | |
| due_at | TIMESTAMP | |
| paid_at | TIMESTAMP | |
| created_at | TIMESTAMP | |
### payments
| Column | Type | Notes |
| ------------------------- | --------- | ---------------------------- |
| id | UUID | Primary key |
| order_id | UUID | FK → orders |
| invoice_id | UUID | FK → invoices |
| user_id | UUID | FK → users |
| payment_gateway_config_id | UUID | FK → payment_gateway_configs |
| payment_method | TEXT | |
| provider_payment_ref | TEXT | |
| amount_inr | INTEGER | |
| currency_code | TEXT | INR |
| status | TEXT | PENDING, SUCCESS, FAILED |
| initiated_at | TIMESTAMP | |
| completed_at | TIMESTAMP | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### payment_gateway_configs
| Column | Type | Notes |
| ------------ | --------- | ---------------- |
| id | UUID | Primary key |
| gateway_key | TEXT | RAZORPAY, STRIPE |
| display_name | TEXT | |
| config_json | JSONB | |
| is_active | BOOLEAN | |
| created_at | TIMESTAMP | |
### payment_transactions
| Column | Type | Notes |
| --------------------- | --------- | ---------------------------- |
| id | UUID | Primary key |
| payment_id | UUID | FK → payments |
| transaction_type | TEXT | INITIATED, COMPLETED, FAILED |
| provider_reference | TEXT | |
| request_payload_json | JSONB | |
| response_payload_json | JSONB | |
| status | TEXT | |
| created_at | TIMESTAMP | |
### coupons
| Column | Type | Notes |
| ------------------- | --------- | ---------------- |
| id | UUID | Primary key |
| code | TEXT | Unique |
| description | TEXT | |
| discount_type | TEXT | PERCENT, FLAT |
| discount_value | INTEGER | |
| max_discount_inr | INTEGER | |
| min_order_value_inr | INTEGER | |
| valid_from | TIMESTAMP | |
| valid_to | TIMESTAMP | |
| usage_limit | INTEGER | NULL = unlimited |
| is_active | BOOLEAN | |
| created_at | TIMESTAMP | |
### coupon_redemptions (RENAMED from coupon_uses)
| Column | Type | Notes |
| ------------------- | --------- | ------------ |
| id | UUID | Primary key |
| coupon_id | UUID | FK → coupons |
| user_id | UUID | FK → users |
| order_id | UUID | FK → orders |
| redeemed_at | TIMESTAMP | |
| discount_amount_inr | INTEGER | |
| created_at | TIMESTAMP | |
### discount_rules
| Column | Type | Notes |
| -------------- | --------- | ------------------------- |
| id | UUID | Primary key |
| name | TEXT | |
| scope_type | TEXT | GLOBAL, CATEGORY, SERVICE |
| scope_id | UUID | |
| discount_type | TEXT | PERCENT, FLAT |
| discount_value | INTEGER | |
| starts_at | TIMESTAMP | |
| ends_at | TIMESTAMP | |
| is_active | BOOLEAN | |
| created_at | TIMESTAMP | |
### tax_rules
| Column | Type | Notes |
| ---------- | --------- | ----------- |
| id | UUID | Primary key |
| name | TEXT | |
| tax_type | TEXT | GST, TCS |
| tax_rate | DECIMAL | |
| applies_to | TEXT | |
| is_active | BOOLEAN | |
| created_at | TIMESTAMP | |
---
## 10. Knowledge Base
### kb_categories
| Column | Type | Notes |
| ------------- | --------- | ----------- |
| id | UUID | Primary key |
| name | TEXT | |
| slug | TEXT | Unique |
| description | TEXT | |
| display_order | INTEGER | |
| is_active | BOOLEAN | |
| created_at | TIMESTAMP | |
### kb_sections
| Column | Type | Notes |
| ------------- | --------- | ------------------ |
| id | UUID | Primary key |
| category_id | UUID | FK → kb_categories |
| name | TEXT | |
| slug | TEXT | |
| description | TEXT | |
| display_order | INTEGER | |
| is_active | BOOLEAN | |
| created_at | TIMESTAMP | |
### kb_articles
| Column | Type | Notes |
| ------------------- | --------- | --------------------------------------------- |
| id | UUID | Primary key |
| category_id | UUID | FK → kb_categories |
| section_id | UUID | FK → kb_sections |
| title | TEXT | |
| slug | TEXT | Unique |
| summary | TEXT | |
| content_markdown | TEXT | |
| article_type | TEXT | HOW_TO, TROUBLESHOOTING, FAQ, FEATURE, POLICY |
| status | TEXT | DRAFT, PUBLISHED |
| audience_type | TEXT | INTERNAL, EXTERNAL, ALL |
| tags | TEXT[] | |
| author_user_id | UUID | FK → users |
| reviewed_by_user_id | UUID | FK → users |
| published_at | TIMESTAMP | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### kb_article_feedback
| Column | Type | Notes |
| ------------- | --------- | ---------------- |
| id | UUID | Primary key |
| article_id | UUID | FK → kb_articles |
| user_id | UUID | FK → users |
| is_helpful | BOOLEAN | |
| feedback_text | TEXT | |
| created_at | TIMESTAMP | |
---
## 11. Support System
### support_tickets
| Column | Type | Notes |
| ------------------- | --------- | ------------------------------------ |
| id | UUID | Primary key |
| created_by_user_id | UUID | FK → users |
| assigned_to_user_id | UUID | FK → users |
| category | TEXT | GENERAL, BILLING, TECHNICAL, ACCOUNT |
| priority | TEXT | LOW, NORMAL, HIGH, URGENT |
| status | TEXT | OPEN, IN_PROGRESS, RESOLVED, CLOSED |
| subject | TEXT | |
| description | TEXT | |
| related_entity_type | TEXT | |
| related_entity_id | UUID | |
| closed_at | TIMESTAMP | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### support_ticket_messages
| Column | Type | Notes |
| ----------------- | --------- | -------------------- |
| id | UUID | Primary key |
| support_ticket_id | UUID | FK → support_tickets |
| sender_user_id | UUID | FK → users |
| message_body | TEXT | |
| attachment_url | TEXT | |
| is_internal | BOOLEAN | |
| created_at | TIMESTAMP | |
---
## 12. Notifications & Communication
### notification_templates
| Column | Type | Notes |
| -------------- | --------- | ---------------- |
| id | UUID | Primary key |
| template_key | TEXT | Unique |
| channel | TEXT | EMAIL, SMS, PUSH |
| title_template | TEXT | |
| body_template | TEXT | |
| is_active | BOOLEAN | |
| created_at | TIMESTAMP | |
### notifications
| Column | Type | Notes |
| ------------------- | --------- | ------------------------ |
| id | UUID | Primary key |
| user_id | UUID | FK → users |
| channel | TEXT | EMAIL, SMS, PUSH, IN_APP |
| title | TEXT | |
| body | TEXT | |
| status | TEXT | PENDING, SENT, READ |
| related_entity_type | TEXT | |
| related_entity_id | UUID | |
| sent_at | TIMESTAMP | |
| read_at | TIMESTAMP | |
| created_at | TIMESTAMP | |
### email_logs
| Column | Type | Notes |
| ------------------------ | --------- | --------------------------- |
| id | UUID | Primary key |
| user_id | UUID | FK → users |
| notification_template_id | UUID | FK → notification_templates |
| to_email | TEXT | |
| subject | TEXT | |
| body_snapshot | TEXT | |
| status | TEXT | PENDING, SENT, FAILED |
| provider_reference | TEXT | |
| error_message | TEXT | |
| sent_at | TIMESTAMP | |
| created_at | TIMESTAMP | |
### smtp_configs
| Column | Type | Notes |
| --------------- | --------- | ----------- |
| id | UUID | Primary key |
| provider_name | TEXT | |
| host | TEXT | |
| port | INTEGER | |
| username | TEXT | |
| encryption_mode | TEXT | SSL, TLS |
| from_name | TEXT | |
| from_email | TEXT | |
| is_default | BOOLEAN | |
| is_active | BOOLEAN | |
| created_at | TIMESTAMP | |
---
## 13. Dashboard & Config
### dashboard_configs
| Column | Type | Notes |
| -------------- | --------- | ------------------ |
| id | UUID | Primary key |
| dashboard_type | TEXT | INTERNAL, EXTERNAL |
| owner_type | TEXT | ROLE, USER |
| owner_id | UUID | |
| name | TEXT | |
| layout_json | JSONB | |
| is_default | BOOLEAN | |
| is_active | BOOLEAN | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### dashboard_widgets
| Column | Type | Notes |
| ------------------- | --------- | ---------------------- |
| id | UUID | Primary key |
| dashboard_config_id | UUID | FK → dashboard_configs |
| widget_key | TEXT | |
| widget_title | TEXT | |
| config_json | JSONB | |
| display_order | INTEGER | |
| width_units | INTEGER | |
| height_units | INTEGER | |
| is_visible | BOOLEAN | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
### runtime_configs
| Column | Type | Notes |
| ----------------- | --------- | ----------- |
| id | UUID | Primary key |
| config_group | TEXT | |
| config_key | TEXT | |
| config_value_json | JSONB | |
| is_active | BOOLEAN | |
| description | TEXT | |
| created_at | TIMESTAMP | |
| updated_at | TIMESTAMP | |
---
## 14. Audit Management
### audit_logs
| Column | Type | Notes |
| ----------------- | --------- | ----------------------------------------- |
| id | UUID | Primary key |
| actor_user_id | UUID | FK → users |
| actor_employee_id | UUID | FK → employees |
| actor_type | TEXT | USER, EMPLOYEE, SYSTEM, CRON |
| action | TEXT | CREATE, UPDATE, DELETE, APPROVE, REJECT |
| entity_type | TEXT | |
| entity_id | UUID | |
| entity_label | TEXT | |
| module_key | TEXT | users, verification, jobs, leads, finance |
| source_type | TEXT | UI, API, WORKER, CRON |
| source_id | UUID | |
| request_id | UUID | |
| correlation_id | UUID | |
| ip_address | TEXT | |
| user_agent | TEXT | |
| status | TEXT | SUCCESS, FAILED |
| summary | TEXT | |
| metadata_json | JSONB | |
| created_at | TIMESTAMP | |
### audit_log_changes
| Column | Type | Notes |
| -------------- | --------- | --------------- |
| id | UUID | Primary key |
| audit_log_id | UUID | FK → audit_logs |
| field_name | TEXT | |
| old_value_text | TEXT | |
| new_value_text | TEXT | |
| created_at | TIMESTAMP | |
---
## 15. Summary of Table Counts
| Domain | Tables | Status |
| ------------------ | ------ | --------------------------------------- |
| Identity & Access | 10 | Existing + user_sessions, user_settings |
| User Role Profiles | 1 | NEW |
| Role Extensions | 10 | Updated FK |
| Other Profiles | 3 | company, customer, candidate |
| Portfolio | 3 | Updated FK |
| Verification | 3 | NEW structure |
| Approval | 2 | NEW structure |
| Marketplace | 5 | Renamed tables |
| Finance | 12 | Expanded |
| Knowledge Base | 4 | Added sections, feedback |
| Support | 2 | Enhanced |
| Notifications | 4 | Added templates, smtp |
| Dashboard | 3 | Added widgets |
| Audit | 2 | NEW |
| **TOTAL** | **64** | |
Reference in a new issue View git blame Copy permalink