foodnote

Tables (6)

product_rankings

  • product_ranking_id — BIGINT — Primary key — NOT NULL
  • ranking_date — DATE — NOT NULL
  • ranking_category — VARCHAR(100) — NOT NULL
  • product_id — BIGINT — Foreign key → products.product_id — NOT NULL
  • rank_position — INT — NOT NULL
  • score — DECIMAL(10,2) — NOT NULL
  • created_at — DATETIME — NOT NULL

product_review_summaries

  • product_id — BIGINT — Foreign key → products.product_id — NOT NULL
  • average_rating — DECIMAL(3,2)
  • review_count — INT — NOT NULL
  • summary_text — TEXT
  • pros_json — JSON
  • cons_json — JSON
  • health_comments_json — JSON
  • updated_at — DATETIME — NOT NULL

products

  • product_id — BIGINT — Primary key — NOT NULL
  • external_product_id — VARCHAR(100)
  • barcode — VARCHAR(50)
  • name — VARCHAR(150) — NOT NULL
  • brand — VARCHAR(100)
  • category — VARCHAR(100)
  • image_url — VARCHAR(500)
  • ingredients_text — TEXT
  • allergens_text — TEXT
  • nutrition_json — JSON
  • source_name — VARCHAR(100) — NOT NULL
  • fetched_at — DATETIME — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

user_favorites

  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • product_id — BIGINT — Foreign key → products.product_id — NOT NULL
  • created_at — DATETIME — NOT NULL

user_profiles

  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • diet_type — VARCHAR(50) — NOT NULL
  • allergies_json — JSON
  • avoided_ingredients_json — JSON
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

users

  • user_id — BIGINT — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL