Database Schema

Relevant source files

The following files were used as context for generating this wiki page:

• database/migrations/2025_01_08_025012_create_stores_table.php
• database/migrations/2025_01_08_040104_create_products_table.php
• database/migrations/2025_01_08_040213_create_prices_table.php
• database/migrations/2025_01_12_140335_create_urls_table.php
• database/migrations/0001_01_01_000000_create_users_table.php
• database/migrations/0001_01_01_000002_create_jobs_table.php
• database/migrations/2025_09_07_143441_add_weight_to_tags_table.php
• app/Models/Product.php
• app/Filament/Resources/TagResource.php

The database schema for PriceBuddy is designed to support a multi-user price tracking application. It stores information about users, the products they track, the online stores those products are sold on, and the historical price data scraped from product URLs. The schema is built using Laravel's migration system, ensuring version control and easy setup.

The core of the schema revolves around the products table, which is linked to users, urls, prices, stores, and tags. This relational structure allows for flexible price comparisons, historical data visualization, and user-specific product organization. Background jobs for scraping prices are managed through standard Laravel jobs tables.

Entity Relationship Diagram

The following diagram illustrates the relationships between the main tables in the PriceBuddy database.

Core Application Tables

These tables are central to the application's functionality of tracking products and prices.

products Table

Stores the core information about a product being tracked by a user.

Column	Type	Constraints	Description
id	bigIncrements	Primary Key	Unique identifier for the product.
user_id	foreignId	Foreign Key (users.id)	The user who owns this product.
title	string	-	The name of the product.
image	string	Nullable	URL to the product's image.
status	string	Default: 'published'	The status of the product (e.g., published, archived).
notify_price	decimal(10, 2)	Nullable	The price threshold for sending a notification.
notify_percent	integer	Nullable	The percentage drop for sending a notification.
favourite	boolean	Default: false	Whether the product is marked as a favourite.
only_official	boolean	Default: false	A flag for filtering, though its specific use is not detailed in the schema.
weight	integer	Default: 0	Used for sorting products.
current_price	decimal(10, 2)	Nullable	The most recently scraped price for the product.
price_cache	json	Nullable	A JSON cache of recent price history for quick display.
ignored_urls	json	Nullable	A list of URLs to ignore during scraping or searching.
timestamps	timestamps	-	created_at and updated_at timestamps.

Sources: database/migrations/2025_01_08_040104_create_products_table.php, app/Models/Product.php:33-45

stores Table

Represents an online retailer from which product prices can be scraped. Each store is configured by a user.

Column	Type	Constraints	Description
id	bigIncrements	Primary Key	Unique identifier for the store.
user_id	foreignId	Foreign Key (users.id), Nullable	The user who created this store configuration.
name	string	-	The name of the store (e.g., "Amazon").
slug	string	Unique	A URL-friendly version of the store name.
initials	string	Nullable	Short initials for the store.
domains	json	Nullable	A JSON array of domains associated with the store.
scrape_strategy	json	Nullable	JSON object defining the CSS selectors or paths for scraping data.
settings	json	Nullable	JSON object for store-specific settings, like the scraper service to use.
timestamps	timestamps	-	created_at and updated_at timestamps.

Sources: database/migrations/2025_01_08_025012_create_stores_table.php, tests/Feature/Api/StoreApiTest.php:90-108

urls Table

Links a product to a specific URL on a store. This is the direct target for price scraping.

Column	Type	Constraints	Description
id	bigIncrements	Primary Key	Unique identifier for the URL.
product_id	foreignId	Foreign Key (products.id)	The product this URL belongs to.
store_id	foreignId	Foreign Key (stores.id)	The store this URL belongs to.
url	text	-	The full URL of the product page.
last_scraped_at	timestamp	Nullable	Timestamp of the last successful scrape.
timestamps	timestamps	-	created_at and updated_at timestamps.

Sources: database/migrations/2025_01_12_140335_create_urls_table.php, app/Models/Product.php:61-64

prices Table

Stores the historical price data for a given url.

Column	Type	Constraints	Description
id	bigIncrements	Primary Key	Unique identifier for the price entry.
url_id	foreignId	Foreign Key (urls.id)	The URL this price was scraped from.
price	decimal(10, 2)	-	The scraped price.
timestamps	timestamps	-	created_at and updated_at timestamps.

Sources: database/migrations/2025_01_08_040213_create_prices_table.php, app/Models/Product.php:80-90

tags and taggables Tables

Used for organizing products. tags stores the tag names, and taggables is a polymorphic pivot table linking tags to products.

tags Column	Type	Constraints	Description
id	bigIncrements	Primary Key	Unique identifier for the tag.
user_id	foreignId	Foreign Key (users.id)	The user who owns this tag.
name	string	-	The name of the tag.
weight	integer	Default: 0	Sort order for the tag.
timestamps	timestamps	-	created_at and updated_at timestamps.

Sources: database/migrations/2025_09_07_143441_add_weight_to_tags_table.php:13, app/Filament/Resources/TagResource.php:36-40, tests/Feature/Api/TagApiTest.php:104-108

taggables Column	Type	Description
tag_id	foreignId	Foreign key to the tags table.
taggable_id	morphs	The ID of the tagged model (e.g., product_id).
taggable_type	morphs	The class name of the tagged model (e.g., App\Models\Product).

Sources: app/Models/Product.php:100-103

System & Authentication Tables

These tables are standard in a Laravel application and handle user authentication, sessions, and background job queuing.

users Table

Stores user account information.

Column	Type	Constraints	Description
id	id	Primary Key	Unique identifier for the user.
name	string	-	User's name.
email	string	Unique	User's email address.
email_verified_at	timestamp	Nullable	Timestamp for email verification.
password	string	-	Hashed user password.
remember_token	rememberToken	-	Token for "remember me" functionality.
timestamps	timestamps	-	created_at and updated_at timestamps.

Sources: database/migrations/0001_01_01_000000_create_users_table.php:15-23

sessions Table

Stores user session data.

Column	Type	Description
id	string	Primary Key, Session ID.
user_id	foreignId	Foreign Key (users.id), Nullable.
ip_address	string(45)	User's IP address.
user_agent	text	User's browser user agent.
payload	longText	Serialized session data.
last_activity	integer	Timestamp of the last activity.

Sources: database/migrations/0001_01_01_000000_create_users_table.php:30-37

jobs Table

Manages the application's job queue for background tasks like price scraping.

Column	Type	Description
id	id	Primary Key
queue	string	The queue the job belongs to.
payload	longText	The serialized job payload.
attempts	unsignedTinyInteger	Number of attempts to run the job.
reserved_at	unsignedInteger	Timestamp when the job was reserved.
available_at	unsignedInteger	Timestamp when the job is available.
created_at	unsignedInteger	Timestamp when the job was created.

Sources: database/migrations/0001_01_01_000002_create_jobs_table.php:15-23

Summary

The PriceBuddy database schema is a well-structured relational model tailored for its price-tracking purpose. It effectively separates concerns between user data, product information, store configurations, and the granular price history. The use of foreign keys enforces data integrity, while JSON columns for strategies and settings provide flexibility. The schema's design is clearly user-centric, with most core entities being directly associated with a user_id, supporting the application's multi-tenancy model.