Database Schema Documentation

Schema Diagram Only Indexes and Relationships

erDiagram users { bigint id } admins { bigint id } creator_details { bigint id bigint user_id } photos { bigint id bigint creator_id } photo_variants { bigint id bigint photo_id } photo_packages { bigint id bigint creator_type bigint creator_id } photo_package_purchase_history { bigint id bigint user_id bigint photo_package_id } photo_package_photo_variant { bigint id bigint photo_package_id bigint photo_variant_id } categories { bigint id bigint parent_id } credit_logs { bigint id varchar related_model_type bigint related_model_id } transaction_logs { bigint id bigint user_id } download_histories { bigint id bigint user_id bigint variant_id bigint photo_package_purchase_history_id } number_of_upload_logs { bigint id bigint user_id } roles { bigint id varchar name } permissions { bigint id varchar name } model_has_roles { bigint role_id varchar model_type bigint model_id } model_has_permissions { bigint permission_id varchar model_type bigint model_id } role_has_permissions { bigint permission_id bigint role_id } notifications { uuid id varchar notifiable_type bigint notifiable_id } %% Notifications Relationships notifications }|--|| users : "notifiable" notifications }|--|| admins : "notifiable" %% User Relationships users ||--|| creator_details : "has one" users ||--o{ credit_logs : "has many" users ||--o{ download_histories : "has many" users ||--o{ number_of_upload_logs : "has many" users ||--o{ transaction_logs : "has many" users ||--o{ photos : "has many as creator" %% Permission Relationships admins }|--o{ model_has_roles : "has roles" admins }|--o{ model_has_permissions : "has permissions" roles ||--o{ model_has_roles : "has many" permissions ||--o{ model_has_permissions : "has many" permissions ||--o{ role_has_permissions : "has many" model_has_roles }|--|| roles : "belongs to" model_has_permissions }|--|| permissions : "belongs to" role_has_permissions }|--|| permissions : "belongs to" role_has_permissions }|--|| roles : "belongs to" %% Content Relationships creator_details ||--o{ number_of_upload_logs : "has many" photos ||--o{ photo_variants : "has many" photos ||--o{ download_histories : "has many" categories ||--o{ categories : "self-reference (parent)" categories ||--o{ photos : "has many" photo_variants ||--o{ download_histories : "has many" photo_variants ||--o{ photo_package_photo_variant : "has many" photo_packages ||--o{ photo_package_photo_variant : "has many" photo_packages ||--o{ photo_package_purchase_history : "has many" purchase_histories ||--o{ photo_package_photo_variant : "has many" purchase_histories ||--o{ download_histories : "has many" %% Financial Relationships credit_logs }|--|| transaction_logs : "from transaction" credit_logs }|--|| number_of_upload_logs : "from upload purchase" credit_logs }|--|| download_histories : "from download" transaction_logs ||--|| users : "by user" transaction_logs ||--o{ admins : "approved by" number_of_upload_logs ||--o{ admins : "changed by"

Detailed Table Structure

users

Column	Type	Constraints	Description
id	bigint	PRIMARY KEY, DEFAULT nextval('users_id_seq')	User identifier
name	varchar(255)	NOT NULL	User's full name
email	varchar(255)	NOT NULL, UNIQUE	User's email address
phone	varchar(255)		User's phone number
credit	bigint	DEFAULT 0	Available credits for purchases
avatar	varchar(255)		Path to avatar image
creator_detail_id	bigint	FOREIGN KEY (creator_details.id)	Reference to creator details if user is a creator
email_verified_at	timestamp(0)		When email was verified
password	varchar(255)	NOT NULL	Hashed password
is_admin	varchar(255)	DEFAULT '0'	Admin flag (0/1)
remember_token	varchar(100)		Remember me token
created_at	timestamp(0)		Creation timestamp
updated_at	timestamp(0)		Last update timestamp
deleted_at	timestamp(0)		Soft delete timestamp

Foreign Keys: creator_detail_id REFERENCES creator_details(id) ON DELETE CASCADE

Indexes: PRIMARY KEY (id), UNIQUE (email)

permissions

Column	Type	Constraints	Description
id	bigint	PRIMARY KEY, DEFAULT nextval('permissions_id_seq')	Permission identifier
name	varchar(255)	NOT NULL	Permission name
guard_name	varchar(255)	NOT NULL	Guard name (usually 'web')
created_at	timestamp(0)		Creation timestamp
updated_at	timestamp(0)		Last update timestamp

Constraints: UNIQUE (name, guard_name)

roles

Column	Type	Constraints	Description
id	bigint	PRIMARY KEY, DEFAULT nextval('roles_id_seq')	Role identifier
name	varchar(255)	NOT NULL	Role name
guard_name	varchar(255)	NOT NULL	Guard name (usually 'web')
created_at	timestamp(0)		Creation timestamp
updated_at	timestamp(0)		Last update timestamp

Constraints: UNIQUE (name, guard_name)

model_has_roles

Column	Type	Constraints	Description
role_id	bigint	PRIMARY KEY, FOREIGN KEY (roles.id)	Role identifier
model_type	varchar(255)	PRIMARY KEY, NOT NULL	Model class (typically 'App\Models\User')
model_id	bigint	PRIMARY KEY, NOT NULL	User identifier

Foreign Keys: role_id REFERENCES roles(id) ON DELETE CASCADE

Indexes: PRIMARY KEY (role_id, model_id, model_type), INDEX (model_id, model_type)

model_has_permissions

Column	Type	Constraints	Description
permission_id	bigint	PRIMARY KEY, FOREIGN KEY (permissions.id)	Permission identifier
model_type	varchar(255)	PRIMARY KEY, NOT NULL	Model class (typically 'App\Models\User')
model_id	bigint	PRIMARY KEY, NOT NULL	User identifier

Foreign Keys: permission_id REFERENCES permissions(id) ON DELETE CASCADE

Indexes: PRIMARY KEY (permission_id, model_id, model_type), INDEX (model_id, model_type)

role_has_permissions

Column	Type	Constraints	Description	Read From	Write From
permission_id	bigint	PRIMARY KEY, FOREIGN KEY (permissions.id)	Permission identifier
role_id	bigint	PRIMARY KEY, FOREIGN KEY (roles.id)	Role identifier

Foreign Keys: permission_id REFERENCES permissions(id) ON DELETE CASCADE, role_id REFERENCES roles(id) ON DELETE CASCADE

notifications

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY	Notification identifier
type	varchar(255)	NOT NULL	Notification type
notifiable_type	varchar(255)	NOT NULL	Type of the notifiable model
notifiable_id	bigint	NOT NULL	ID of the notifiable model
data	text	NOT NULL	Notification data
read_at	timestamp(0)	NULLABLE	Timestamp when the notification was read
created_at	timestamp(0)		Creation timestamp
updated_at	timestamp(0)		Last update timestamp

photos

Column	Type	Constraints	Description
id	bigint	PRIMARY KEY, DEFAULT nextval('photos_id_seq')	Photo identifier
creator_id	bigint	NOT NULL, FOREIGN KEY (users.id)	User who created this photo
name	varchar(255)	NOT NULL	Photo name/title
summary	varchar(255)		Short description
description	longText		Longer description
keywords	varchar(255)		Search keywords
slug	varchar(255)	NOT NULL, UNIQUE	URL-friendly identifier
category_id	bigint	NOT NULL, FOREIGN KEY (categories.id)	Category this photo belongs to
created_at	timestamp(0)		Creation timestamp
updated_at	timestamp(0)		Last update timestamp

Foreign Keys: creator_id REFERENCES users(id) ON DELETE CASCADE, category_id REFERENCES categories(id) ON DELETE CASCADE

Column	Type	Constraints	Description
id	bigint	PRIMARY KEY, DEFAULT nextval('categories_id_seq')	Category identifier
name	varchar(255)	NOT NULL	Category name
description	varchar(255)		Category description
slug	varchar(255)	NOT NULL, UNIQUE	URL-friendly identifier
image	varchar(255)		Category image path
parent_id	bigint	FOREIGN KEY (categories.id)	Parent category for hierarchy
created_at	timestamp(0)		Creation timestamp
updated_at	timestamp(0)		Last update timestamp

photo_variants

Column	Type	Constraints	Description
id	bigint	PRIMARY KEY, DEFAULT nextval('photo_variants_id_seq')	Variant identifier
photo_id	bigint	NOT NULL, FOREIGN KEY (photos.id)	Photo this variant belongs to
variant_type	varchar(255)	NOT NULL, CHECK (variant_type IN ('original', 'compressed', 'hd', 'preview'))	Type of variant
file_path	varchar(255)		Path to variant file
file_size	varchar(255)		Size of variant file
file_price	double precision		Price for this variant
file_discounted_price	varchar(255)		Discounted price
file_dimensions	varchar(255)		Image dimensions
created_at	timestamp(0)		Creation timestamp
updated_at	timestamp(0)		Last update timestamp

Foreign Keys: photo_id REFERENCES photos(id) ON DELETE CASCADE

creator_details

Column	Type	Constraints	Description
id	bigint	PRIMARY KEY, DEFAULT nextval('creator_details_id_seq')	Creator details identifier
user_id	bigint	NOT NULL, FOREIGN KEY (users.id)	User these details belong to
number_of_uploads_allowed	bigint	DEFAULT 0	How many uploads the creator is allowed
created_at	timestamp(0)		Creation timestamp
updated_at	timestamp(0)		Last update timestamp
deleted_at	timestamp(0)		Soft delete timestamp

Foreign Keys: user_id REFERENCES users(id) ON DELETE CASCADE

credit_logs

Column	Type	Constraints	Description
id	bigint	PRIMARY KEY, DEFAULT nextval('credit_logs_id_seq')	Log entry identifier
user_id	bigint	NOT NULL, FOREIGN KEY (users.id)	User affected by this change
change	double precision	DEFAULT 0	Amount of credit changed
reason	varchar(255)		Reason for change
by	bigint	FOREIGN KEY (admins.id)	User who initiated this change
related_model_type	varchar(255)		Related model type
related_model_id	bigint		Related model ID
new_balance	varchar(255)		New credit balance
created_at	timestamp(0)		Creation timestamp
updated_at	timestamp(0)		Last update timestamp

Foreign Keys: user_id REFERENCES users(id) ON DELETE CASCADE, by REFERENCES admins(id) ON DELETE CASCADE

Indexes: INDEX (related_model_type, related_model_id)

download_histories

Column	Type	Constraints	Description
id	bigint	PRIMARY KEY, DEFAULT nextval('download_histories_id_seq')	Download record identifier
user_id	bigint	NOT NULL, FOREIGN KEY (users.id)	User who downloaded
variant_id	bigint	NOT NULL, FOREIGN KEY (photo_variants.id)	Photo that was downloaded
photo_package_purchase_history_id	bigint	FOREIGN KEY (photo_package_purchase_history.id)	Purchase history for the photo package
credits_paid	bigint		Credits spent
commission	bigint		Commission taken
created_at	timestamp(0)		Creation timestamp
updated_at	timestamp(0)		Last update timestamp

Foreign Keys: user_id REFERENCES users(id) ON DELETE CASCADE, variant_id REFERENCES photo_variants(id) ON DELETE CASCADE, photo_package_purchase_history_id REFERENCES photo_package_purchase_history(id) ON DELETE CASCADE

photo_packages

Column	Type	Constraints	Description
id	bigint	PRIMARY KEY, AUTO_INCREMENT	Package identifier
creator_type	varchar(255)	NOT NULL	User type who created the package
creator_id	bigint	NOT NULL	User id who created the package
name	varchar(255)	NOT NULL	Name of the package
price	bigint	DEFAULT 0	Price of the package
summary	varchar(255)	DEFAULT 0	Number of photos in the package
description	varchar(255)	NULLABLE	Description of the package
image	varchar(255)	NULLABLE	Image of the package
status	enum('active', 'inactive')	DEFAULT 'active'	Status of the package
created_at	timestamp(0)		Creation timestamp
updated_at	timestamp(0)		Last update timestamp

Foreign Keys: created_by REFERENCES users(id) ON DELETE CASCADE Indexes: INDEX (creator_type, creator_id)

transaction_logs

Column	Type	Constraints	Description
id	bigint	PRIMARY KEY, DEFAULT nextval('transaction_logs_id_seq')	Transaction identifier
user_id	bigint	NOT NULL, FOREIGN KEY (users.id)	User making the transaction
amount	double precision		Transaction amount
credit_changed	varchar(255)		Credits changed
paid_via	varchar(255)		Payment method
request	varchar(255)		Request data
response	varchar(255)		Response data
approved_by	bigint	FOREIGN KEY (admins.id)	Admin who approved
status	varchar(255)	DEFAULT 'pending', CHECK (status IN ('pending', 'approved', 'rejected'))	Transaction status
remarks	varchar(255)		Admin remarks
created_at	timestamp(0)		Creation timestamp
updated_at	timestamp(0)		Last update timestamp

Foreign Keys: user_id REFERENCES users(id) ON DELETE CASCADE, approved_by REFERENCES admins(id) ON DELETE CASCADE

number_of_upload_logs

Column	Type	Constraints	Description
id	bigint	PRIMARY KEY, DEFAULT nextval('number_of_upload_logs_id_seq')	Log entry identifier
user_id	bigint	NOT NULL, FOREIGN KEY (users.id)	User affected by this change
change	bigint	DEFAULT 0	Number of uploads changed
reason	varchar(255)		Reason for change
by	bigint	FOREIGN KEY (admins.id)	User who initiated this change
number_of_uploads_allowed	bigint	DEFAULT 0	New total uploads allowed
created_at	timestamp(0)		Creation timestamp
updated_at	timestamp(0)		Last update timestamp

Foreign Keys: user_id REFERENCES users(id) ON DELETE CASCADE, by REFERENCES admins(id) ON DELETE CASCADE

photo_package_photo_variant

Column	Type	Constraints	Description
id	bigint	PRIMARY KEY, AUTO_INCREMENT	Record identifier
photo_package_id	bigint	NOT NULL, FOREIGN KEY (photo_packages.id) ON DELETE CASCADE	Photo package ID
photo_variant_id	bigint	NOT NULL, FOREIGN KEY (photo_variants.id) ON DELETE CASCADE	Photo variant ID
price	bigint	DEFAULT 0	Price of the photo variant in the package
creator_accepted	enum('yes', 'no', 'pending')	DEFAULT 'pending'	Whether the creator accepted the package
created_at	timestamp(0)		Creation timestamp
updated_at	timestamp(0)		Last update timestamp

Foreign Keys: photo_package_id REFERENCES photo_packages(id) ON DELETE CASCADE, photo_variant_id REFERENCES photo_variants(id) ON DELETE CASCADE

photo_package_purchase_history

Column	Type	Constraints	Description
id	bigint	PRIMARY KEY, AUTO_INCREMENT	Record identifier
photo_package_id	bigint	NOT NULL, FOREIGN KEY (photo_packages.id) ON DELETE CASCADE	Photo package ID
user_id	bigint	NOT NULL, FOREIGN KEY (users.id) ON DELETE CASCADE	User who purchased the package
credits_paid	bigint	NULLABLE	Credits paid for the package
commission	bigint	NULLABLE	Commission for the purchase
purchased_at	timestamp(0)	DEFAULT CURRENT_TIMESTAMP	Time of purchase
created_at	timestamp(0)		Creation timestamp
updated_at	timestamp(0)		Last update timestamp

Foreign Keys: photo_package_id REFERENCES photo_packages(id) ON DELETE CASCADE, user_id REFERENCES users(id) ON DELETE CASCADE

cache

Column	Type	Constraints	Description
key	varchar(255)	PRIMARY KEY, NOT NULL	Cache key
value	text	NOT NULL	Cached value
expiration	integer	NOT NULL	Expiration timestamp

cache_locks

Column	Type	Constraints	Description
key	varchar(255)	PRIMARY KEY, NOT NULL	Cache lock key
owner	varchar(255)	NOT NULL	Lock owner identifier
expiration	integer	NOT NULL	Expiration timestamp

jobs

Column	Type	Constraints	Description
id	bigint	PRIMARY KEY, DEFAULT nextval('jobs_id_seq')	Job identifier
queue	varchar(255)	NOT NULL	Queue name
payload	text	NOT NULL	Job payload
attempts	smallint	NOT NULL	Attempt count
reserved_at	integer		When job was reserved
available_at	integer	NOT NULL	When job becomes available
created_at	integer	NOT NULL	Creation timestamp

Indexes: INDEX (queue)

job_batches

Column	Type	Constraints	Description
id	varchar(255)	PRIMARY KEY, NOT NULL	Batch identifier
name	varchar(255)	NOT NULL	Batch name
total_jobs	integer	NOT NULL	Total jobs in batch
pending_jobs	integer	NOT NULL	Pending jobs count
failed_jobs	integer	NOT NULL	Failed jobs count
failed_job_ids	text	NOT NULL	Array of failed job IDs
options	text		Batch options
cancelled_at	integer		When batch was cancelled
created_at	integer	NOT NULL	Creation timestamp
finished_at	integer		Completion timestamp

failed_jobs

Column	Type	Constraints	Description
id	bigint	PRIMARY KEY, DEFAULT nextval('failed_jobs_id_seq')	Failed job identifier
uuid	varchar(255)	NOT NULL, UNIQUE	Unique job identifier
connection	text	NOT NULL	Queue connection
queue	text	NOT NULL	Queue name
payload	text	NOT NULL	Job payload
exception	text	NOT NULL	Exception details
failed_at	timestamp(0)	DEFAULT CURRENT_TIMESTAMP	Failure timestamp

sessions

Column	Type	Constraints	Description
id	varchar(255)	PRIMARY KEY, NOT NULL	Session identifier
user_id	bigint		User this session belongs to
ip_address	varchar(45)		IP address of user
user_agent	text		User agent string
payload	text	NOT NULL	Session data
last_activity	integer	NOT NULL	Last activity timestamp

Indexes: INDEX (user_id), INDEX (last_activity)

password_reset_tokens

Column	Type	Constraints	Description
email	varchar(255)	PRIMARY KEY, NOT NULL	User email
token	varchar(255)	NOT NULL	Reset token
created_at	timestamp(0)		Creation timestamp

admin_password_reset_tokens

Column	Type	Constraints	Description
email	varchar(255)	PRIMARY KEY, NOT NULL	User email
token	varchar(255)	NOT NULL	Reset token
created_at	timestamp(0)		Creation timestamp

migrations

Column	Type	Constraints	Description
id	integer	PRIMARY KEY, DEFAULT nextval('migrations_id_seq')	Migration identifier
migration	varchar(255)	NOT NULL	Migration class name
batch	integer	NOT NULL	Batch number