182 lines
3.6 KiB
Markdown
182 lines
3.6 KiB
Markdown
# 1️⃣ Final SQL – New Favorites Table (Artwork-only)
|
||
|
||
**Table name:** `user_favorites`
|
||
**Scope:** users ↔ artworks
|
||
**Engine:** InnoDB
|
||
**Charset:** utf8mb4
|
||
|
||
```sql
|
||
CREATE TABLE user_favorites (
|
||
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
|
||
user_id BIGINT UNSIGNED NOT NULL,
|
||
artwork_id BIGINT UNSIGNED NOT NULL,
|
||
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
||
UNIQUE KEY uniq_user_artwork (user_id, artwork_id),
|
||
KEY idx_artwork (artwork_id),
|
||
KEY idx_user_created (user_id, created_at),
|
||
|
||
CONSTRAINT fk_user_favorites_user
|
||
FOREIGN KEY (user_id) REFERENCES users(id)
|
||
ON DELETE CASCADE,
|
||
|
||
CONSTRAINT fk_user_favorites_artwork
|
||
FOREIGN KEY (artwork_id) REFERENCES artworks(id)
|
||
ON DELETE CASCADE
|
||
) ENGINE=InnoDB
|
||
DEFAULT CHARSET=utf8mb4
|
||
COLLATE=utf8mb4_unicode_ci;
|
||
```
|
||
|
||
This is **production-ready** and matches everything we discussed:
|
||
|
||
* no legacy fields
|
||
* no duplication
|
||
* proper constraints
|
||
* fast queries
|
||
|
||
---
|
||
|
||
# 2️⃣ VS Code Agent Markdown (Laravel 12 Migration Prompt)
|
||
|
||
Save this as for example:
|
||
|
||
```
|
||
.vscode/agents/laravel-user-favorites-migration.md
|
||
```
|
||
|
||
or
|
||
|
||
```
|
||
.cursor/rules/laravel-user-favorites.md
|
||
```
|
||
|
||
---
|
||
|
||
```markdown
|
||
# Laravel 12 – User Favorites Migration & Model
|
||
|
||
## Context
|
||
We are migrating legacy "favourites" functionality into a clean, modern Laravel 12 system.
|
||
Each user can add artworks to their favorites list.
|
||
This is a many-to-many relationship between users and artworks.
|
||
|
||
Legacy table MUST NOT be reused.
|
||
|
||
---
|
||
|
||
## Goal
|
||
Create a Laravel 12 database migration and Eloquent model for a new table named:
|
||
|
||
```
|
||
|
||
user_favorites
|
||
|
||
````
|
||
|
||
This table stores **which user favorited which artwork**, with a timestamp.
|
||
|
||
---
|
||
|
||
## Database Requirements
|
||
|
||
### Table: user_favorites
|
||
|
||
| Column | Type | Notes |
|
||
|------|------|------|
|
||
| id | BIGINT UNSIGNED | Primary key |
|
||
| user_id | BIGINT UNSIGNED | FK → users.id |
|
||
| artwork_id | BIGINT UNSIGNED | FK → artworks.id |
|
||
| created_at | TIMESTAMP | When artwork was favorited |
|
||
|
||
### Constraints & Indexes
|
||
|
||
- UNIQUE (user_id, artwork_id)
|
||
→ prevents duplicate favorites
|
||
|
||
- INDEX artwork_id
|
||
→ fast favorite count per artwork
|
||
|
||
- INDEX (user_id, created_at)
|
||
→ fast "my favorites" queries
|
||
|
||
### Foreign Keys
|
||
|
||
- user_id → users.id (ON DELETE CASCADE)
|
||
- artwork_id → artworks.id (ON DELETE CASCADE)
|
||
|
||
### Engine & Charset
|
||
|
||
- Engine: InnoDB
|
||
- Charset: utf8mb4
|
||
- Collation: utf8mb4_unicode_ci
|
||
|
||
---
|
||
|
||
## Laravel Migration Requirements
|
||
|
||
- Use `Schema::create`
|
||
- Use `foreignId()->constrained()->cascadeOnDelete()`
|
||
- Use `timestamps()` **ONLY if created_at is needed**
|
||
(do NOT add updated_at)
|
||
|
||
- Add explicit indexes and unique constraints
|
||
|
||
---
|
||
|
||
## Laravel Model Requirements
|
||
|
||
### Model: UserFavorite
|
||
|
||
- Table: `user_favorites`
|
||
- `$timestamps = false` (created_at handled manually or via DB default)
|
||
- Fillable:
|
||
- user_id
|
||
- artwork_id
|
||
- created_at
|
||
|
||
### Relationships
|
||
|
||
```php
|
||
UserFavorite belongsTo User
|
||
UserFavorite belongsTo Artwork
|
||
````
|
||
|
||
---
|
||
|
||
## Additional Notes
|
||
|
||
* This table is interaction-based, NOT content-based
|
||
* Do NOT store favorite counts here
|
||
* Favorite counts will be aggregated separately (Redis or statistics table)
|
||
* This table must be lean and write-optimized
|
||
|
||
---
|
||
|
||
## Deliverables
|
||
|
||
* Migration file for Laravel 12
|
||
* Eloquent model `UserFavorite`
|
||
* Proper naming and clean schema
|
||
* No legacy fields, no polymorphic logic
|
||
|
||
Generate clean, production-quality code.
|
||
|
||
````
|
||
|
||
---
|
||
|
||
## 3️⃣ How to Use This in VS Code (Quick Steps)
|
||
|
||
1. Paste markdown into `.vscode/agents/` or `.cursor/rules/`
|
||
2. Open VS Code
|
||
3. Ask your AI agent:
|
||
> “Create Laravel 12 migration and model based on this document”
|
||
4. Review generated migration
|
||
5. Run:
|
||
```bash
|
||
php artisan migrate
|
||
````
|