Database Schema

Complete documentation of the MyWarranties database entities and relationships.

Entity Relationship Diagram

┌─────────────┐       ┌─────────────────┐       ┌──────────────┐
│    User     │──────<│     Product     │──────<│   Receipt    │
└─────────────┘       └─────────────────┘       └──────────────┘
      │                       │
      │                       │
      ▼                       ▼
┌─────────────┐       ┌─────────────────┐
│ DeviceToken │       │ WarrantyClaim   │──────<┌──────────────┐
└─────────────┘       └─────────────────┘       │ ClaimMessage │
                              │                 └──────────────┘
                              │
                              ▼
                      ┌─────────────────────────┐
                      │ WarrantyNotificationLog │
                      └─────────────────────────┘

Entities

User

Stores user accounts including customers, suppliers, and administrators.

Table: users

Column	Type	Nullable	Description
id	INT	No	Primary key (auto-increment)
email	VARCHAR(180)	No	Email address (unique, lowercase)
roles	JSON	No	Array of user roles
password	VARCHAR(255)	Yes	Hashed password
name	VARCHAR(255)	Yes	Full name
street	VARCHAR(255)	Yes	Street address
postal_code	VARCHAR(20)	Yes	Postal code
city	VARCHAR(100)	Yes	City
country	VARCHAR(100)	Yes	Country
notifications_enabled	BOOLEAN	No	Push notifications (default: true)
email_notifications_enabled	BOOLEAN	No	Email notifications (default: true)
profile_picture	VARCHAR(255)	Yes	Profile picture filename
created_at	DATETIME	No	Creation timestamp

Indexes:

• UNIQUE on email

Available Roles:

• ROLE_USER - Default role for all users
• ROLE_SUPPLIER - Can manage warranty claims
• ROLE_ADMIN - Full system access

---

Product

Stores product information with warranty details.

Table: products

Column	Type	Nullable	Description
id	INT	No	Primary key
user_id	INT	No	Owner user (FK)
name	VARCHAR(255)	No	Product name
brand	VARCHAR(255)	No	Brand name
category	VARCHAR(100)	No	Product category
purchase_date	DATE	No	Purchase date
store	VARCHAR(255)	No	Store name
warranty_period_months	INT	No	Warranty duration
warranty_end_date	DATE	Yes	Calculated end date
serial_number	VARCHAR(255)	Yes	Serial number
serial_number_photo	TEXT	Yes	Base64 encoded photo
notes	TEXT	Yes	Additional notes
supplier_email	VARCHAR(255)	Yes	Supplier contact
created_at	DATETIME	No	Creation timestamp
updated_at	DATETIME	No	Last update

Relationships:

• Many-to-One → User (owner)
• One-to-Many → Receipt
• One-to-Many → WarrantyClaim
• One-to-One → ProductImage

Warranty Status Calculation:

// Returns: 'expired', 'expiring-soon', or 'active'
$status = $product->getWarrantyStatus();

---

WarrantyClaim

Stores warranty claims with status tracking.

Table: warranty_claims

Column	Type	Nullable	Description
id	INT	No	Primary key
product_id	INT	No	Related product (FK)
customer_id	INT	Yes	Customer user (FK)
supplier_id	INT	Yes	Assigned supplier (FK)
status	VARCHAR(50)	No	Claim status
description	TEXT	Yes	Issue description
created_at	DATETIME	No	Creation timestamp
updated_at	DATETIME	No	Last update

Status Values:

Status	Description
open	New claim, awaiting supplier
in_progress	Supplier is handling
resolved	Issue resolved
closed	Claim closed (final)

Relationships:

• Many-to-One → Product
• Many-to-One → User (customer)
• Many-to-One → User (supplier)
• One-to-Many → ClaimMessage

---

ClaimMessage

Stores messages in claim conversations.

Table: claim_messages

Column	Type	Nullable	Description
id	INT	No	Primary key
claim_id	INT	No	Parent claim (FK)
sender_id	INT	Yes	Sender user (FK)
message	TEXT	Yes	Message content
attachment_filename	VARCHAR(255)	Yes	Attachment filename
attachment_mime_type	VARCHAR(100)	Yes	MIME type
attachment_size	INT	Yes	File size (bytes)
is_read	BOOLEAN	No	Read status
read_at	DATETIME	Yes	Read timestamp
created_at	DATETIME	No	Creation timestamp

Cascade: Messages are deleted when claim is deleted.

---

Receipt

Stores purchase receipts for products.

Table: receipts

Column	Type	Nullable	Description
id	INT	No	Primary key
product_id	INT	No	Related product (FK)
file_name	VARCHAR(255)	Yes	Original filename
file_type	VARCHAR(20)	Yes	File extension
data_url	TEXT	Yes	Base64 encoded data
uploaded_at	DATETIME	No	Upload timestamp

---

DeviceToken

Stores push notification device tokens.

Table: device_tokens

Column	Type	Nullable	Description
id	INT	No	Primary key
user_id	INT	No	Owner user (FK)
token	VARCHAR(500)	No	Device token
platform	VARCHAR(20)	No	'ios' or 'android'
created_at	DATETIME	No	Creation timestamp
updated_at	DATETIME	No	Last update

Cascade: Tokens are deleted when user is deleted.

---

VerificationCode

Stores email verification codes for passwordless login.

Table: verification_codes

Column	Type	Nullable	Description
id	INT	No	Primary key
email	VARCHAR(180)	Yes	Email (lowercase)
code	VARCHAR(8)	Yes	Verification code
expires_at	DATETIME	Yes	Expiration time
created_at	DATETIME	No	Creation timestamp
used	BOOLEAN	No	Whether used

Validation Methods:

$code->isExpired(); // Check if expired
$code->isValid();   // Check if valid (!used && !expired)

---

WarrantyNotificationLog

Tracks warranty expiration notifications to prevent duplicates.

Table: warranty_notification_logs

Column	Type	Nullable	Description
id	INT	No	Primary key
product_id	INT	No	Related product (FK)
days_threshold	INT	No	Days before expiration
sent_at	DATETIME	Yes	Send timestamp

Unique Constraint: product_id + days_threshold

This prevents sending duplicate notifications for the same threshold (e.g., 30 days, 7 days, 1 day).

---

ProductImage

Stores product images.

Table: product_images

Column	Type	Nullable	Description
id	INT	No	Primary key
product_id	INT	Yes	Related product (FK)
filename	VARCHAR(255)	Yes	Filename
filepath	VARCHAR(500)	Yes	Storage path
mime_type	VARCHAR(100)	Yes	MIME type
file_size	INT	Yes	Size in bytes
uploaded_at	DATETIME	No	Upload timestamp

---

Migrations

Run migrations to set up the database:

# Create database
php bin/console doctrine:database:create

# Run all migrations
php bin/console doctrine:migrations:migrate

# Check migration status
php bin/console doctrine:migrations:status

Indexes & Performance

Key indexes for query optimization:

• users.email - Unique index for login lookups
• products.user_id - Foreign key index
• products.warranty_end_date - For expiring warranty queries
• warranty_claims.product_id - Foreign key index
• warranty_claims.status - For status filtering
• claim_messages.claim_id - Foreign key index
• device_tokens.user_id - Foreign key index