Database Schema

WardrobeAI MVP uses SQLite with 6 tables.

Database file: backend/database/wardrobe-ai.db

Initialization: backend/src/db/init.js (creates all tables on server start)

Tables

`style_profiles`

Stores style preference profiles per user. Acts as the "brief" the matching algorithm uses to select items.

Column	Type	Description
`id`	TEXT PK	Unique identifier (e.g., `profile_001`)
`name`	TEXT NOT NULL	Human-readable name (e.g., "Work Professional")
`description`	TEXT	Free-text description of the style profile
`budget`	REAL	Monthly budget in USD
`must_have_categories`	TEXT	JSON array of required categories (e.g., `["tops", "bottoms"]`)
`preferred_styles`	TEXT	JSON array of style tags (e.g., `["professional", "classic"]`)
`excluded_styles`	TEXT	JSON array of excluded style tags
`max_price`	REAL	Maximum price per item
`preferred_colors`	TEXT	JSON array of color preferences
`seasons`	TEXT	JSON array of applicable seasons
`created_at`	DATETIME	Default `CURRENT_TIMESTAMP`

Sample data (5 profiles):

ID	Name	Budget	Key Styles
profile_001	Work Professional	$150	professional, business formal, classic
profile_002	Casual Weekend	$80	casual, bohemian, preppy
profile_003	Formal Events	$200	elegant, evening, formal
profile_004	Summer Garden Party	$100	casual, bohemian, summer
profile_005	Winter Professional	$180	professional, classic, business casual

`inventory_items`

Catalog of clothing items available for curation.

Column	Type	Description
`id`	TEXT PK	Unique identifier (e.g., `item_001`)
`name`	TEXT NOT NULL	Product name
`price`	REAL NOT NULL	Price in USD
`category`	TEXT	Category: tops, bottoms, dresses, outerwear
`colors`	TEXT	JSON array of colors (e.g., `["white", "black"]`)
`styles`	TEXT	JSON array of style tags (e.g., `["classic", "casual"]`)
`brand`	TEXT	Brand name
`description`	TEXT	Product description
`image_path`	TEXT	Path to product image
`created_at`	DATETIME	Default `CURRENT_TIMESTAMP`

Sample data (20 items):

ID	Name	Price	Category
item_001	Classic White Blouse	$49.99	tops
item_002	Denim A-Line Skirt	$59.99	bottoms
item_003	Silk Cocktail Dress	$129.99	dresses
item_004	Cashmere Sweater	$89.99	tops
item_005	Floral Summer Dress	$69.99	dresses
...	...	...	...

`user_profiles`

User account information and physical measurements.

Column	Type	Description
`id`	TEXT PK	Unique identifier
`name`	TEXT NOT NULL	User's display name
`body_type`	TEXT	Hourglass, pear, apple, rectangle
`height`	TEXT	Height (e.g., `5'7"`)
`size`	TEXT	Clothing size
`style_preferences`	TEXT	JSON string of preferences
`budget_range`	TEXT	Budget range description
`created_at`	DATETIME	Default `CURRENT_TIMESTAMP`
`updated_at`	DATETIME	Default `CURRENT_TIMESTAMP`

`curated_boxes`

Generated box shipments linking users to their curated items.

Column	Type	Description
`id`	TEXT PK	Auto-generated (e.g., `box_1714672800000_abc123xyz`)
`user_profile_id`	TEXT NOT NULL	FK → `user_profiles(id)`
`style_profile_id`	TEXT NOT NULL	FK → `style_profiles(id)`
`items`	TEXT	JSON array of item objects with scores
`total_price`	REAL	Sum of all item prices in the box
`created_at`	DATETIME	Default `CURRENT_TIMESTAMP`

`feedback`

User feedback on individual items within a curated box.

Column	Type	Description
`id`	TEXT PK	Auto-generated
`box_id`	TEXT NOT NULL	FK → `curated_boxes(id)`
`item_id`	TEXT NOT NULL	Item being reviewed
`rating`	INTEGER	1-5 rating (CHECK constraint)
`kept`	BOOLEAN	Whether the item was kept
`returned`	BOOLEAN	Whether the item was returned
`notes`	TEXT	Free-text comments
`created_at`	DATETIME	Default `CURRENT_TIMESTAMP`

`matching_history`

Tracks each matching decision for analytics and algorithm improvement.

Column	Type	Description
`id`	TEXT PK	Auto-generated
`user_profile_id`	TEXT NOT NULL	FK → `user_profiles(id)`
`item_id`	TEXT NOT NULL	Item that was matched
`match_score`	INTEGER	Score 0-100
`match_reasons`	TEXT	JSON array of reason strings
`created_at`	DATETIME	Default `CURRENT_TIMESTAMP`

Entity Relationship Diagram

user_profiles ──1:N── curated_boxes ──1:N── feedback
                      │                      │
                      │                      └── item_id → inventory_items
                      │
                      └── style_profile_id → style_profiles

matching_history ── user_profile_id → user_profiles
                 └── item_id → inventory_items

Example Queries

Get all items in a user's most recent box

SELECT cb.id AS box_id, ii.name, ii.price, cb.total_price
FROM curated_boxes cb
JOIN inventory_items ii ON ii.id IN (
  SELECT value FROM json_each(cb.items, '$[*].id')
)
WHERE cb.user_profile_id = 'user_001'
ORDER BY cb.created_at DESC
LIMIT 1;

Get average ratings per category

SELECT ii.category, AVG(f.rating) as avg_rating, COUNT(*) as feedback_count
FROM feedback f
JOIN inventory_items ii ON f.item_id = ii.id
GROUP BY ii.category
ORDER BY avg_rating DESC;

Find items with highest keep rate

SELECT 
  ii.name,
  SUM(CASE WHEN f.kept = 1 THEN 1 ELSE 0 END) * 1.0 / COUNT(*) as keep_rate,
  AVG(f.rating) as avg_rating
FROM feedback f
JOIN inventory_items ii ON f.item_id = ii.id
GROUP BY f.item_id
HAVING COUNT(*) >= 2
ORDER BY keep_rate DESC;

Show recent boxes by profile

SELECT 
  sp.name as profile_name,
  cb.id as box_id,
  cb.total_price,
  cb.created_at
FROM curated_boxes cb
JOIN style_profiles sp ON cb.style_profile_id = sp.id
ORDER BY cb.created_at DESC;

Indexing Notes

Primary keys are used on all id columns
Foreign keys: curated_boxes(user_profile_id), curated_boxes(style_profile_id), feedback(box_id), matching_history(user_profile_id)
For production scaling, add indexes on curated_boxes.created_at, feedback.item_id, and matching_history.created_at
JSON fields (items, preferred_styles, colors, etc.) are stored as TEXT and parsed at the application layer via JSON.parse()