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

50 KiB

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

50 KiB Raw Permalink Blame History