FridgeRadar Database Setup, Schema, and Backups

FridgeRadar uses MySQL 8 as its primary data store, accessed asynchronously from the FastAPI backend via SQLAlchemy 2.0 and aiomysql. The entire schema — including all tables, indexes, triggers, scheduled events, views, stored procedures, and seed data — is defined in a single SQL script at backend/infra/mysql/fridgeradar_db.sql. Running that script against a fresh MySQL instance is all that is required to reach a working database state.

Requirements

MySQL 8+

The schema relies on utf8mb4 collation, MySQL Events (event_scheduler), window functions, and DELIMITER blocks — all MySQL 8 features. Earlier versions are not supported.

MySQL Event Scheduler

The SQL script issues SET GLOBAL event_scheduler = ON automatically. Your MySQL user must have SUPER or SYSTEM_VARIABLES_ADMIN privileges for this to take effect.

Initializing the Schema

Connect to MySQL and run the SQL script

Execute the bundled script from the repository root. It drops and recreates the fridgeradar_db database from scratch on every run:

mysql -u root -p < backend/infra/mysql/fridgeradar_db.sql

You will be prompted for your MySQL root password. For a remote host:

mysql -h db.example.com -u root -p < backend/infra/mysql/fridgeradar_db.sql

The script begins with DROP DATABASE IF EXISTS fridgeradar_db. Running it against an existing database destroys all data. Only use it for fresh installations or development resets — never against a production database that contains live data.

Verify the schema was created

Connect to MySQL and confirm the tables exist:

USE fridgeradar_db;
SHOW TABLES;

You should see all 19 tables plus the views (v_inventario_completo, v_recetas_posibles, v_desperdicio_por_hogar).

Connection String

The FastAPI backend constructs its async database URL from the environment variables defined in app/core/config.py:

mysql+aiomysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}

With default values this resolves to:

mysql+aiomysql://root:admin@localhost:3306/fridgeradar_db

Set the corresponding variables in your .env file before starting the backend. See Backend Deployment for the full environment variable reference.

Schema Overview

The SQL script creates the following objects inside fridgeradar_db (charset utf8mb4, collation utf8mb4_unicode_ci):

Tables

All 19 tables use InnoDB with enforced foreign key constraints:

Table	Description
`usuario`	User accounts with hashed passwords and account state
`hogar`	Household groups with unique invitation codes
`usuario_hogar`	Many-to-many join: users ↔ households with `owner / admin / miembro` roles
`zona`	Named storage zones (refrigerator, freezer, pantry, etc.) scoped to a household
`estante`	Visual shelves within a zone, with vertical position and UI color
`categoria_producto`	Global product category catalog (seeded with 10 categories)
`producto`	Global product catalog with barcode, unit of measure, and average shelf life
`inventario`	Real per-household inventory items with quantity, purchase date, expiry date, and semáforo state
`movimiento_inventario`	Audit log of every add / move / consume / delete / expire action
`receta`	Recipes, either system-provided (`id_usuario_creador IS NULL`) or user-created
`tag_receta`	Recipe tag definitions (seeded with 8 tags)
`receta_tag`	Many-to-many join: recipes ↔ tags
`receta_ingrediente`	Ingredients per recipe with quantity, unit, and an `obligatorio` flag
`receta_favorita`	Per-user saved recipe favorites
`alerta`	System-generated and event-generated alerts linked to a specific inventory item
`lista_compra`	Per-household shopping lists with `activa / completada` states
`lista_compra_detalle`	Line items in a shopping list with priority and `comprado` flag
`desperdicio`	Waste log recording discarded items, quantity, and reason
`sugerencia_receta`	Persisted “Tengo Hambre” suggestions with match percentage and critical-item flag

Performance Indexes

Ten dedicated indexes are created to accelerate the queries most common in the backend:

CREATE INDEX idx_inventario_vencimiento  ON inventario(fecha_vencimiento);
CREATE INDEX idx_inventario_estado       ON inventario(estado_caducidad);
CREATE INDEX idx_inventario_hogar        ON inventario(id_hogar);
CREATE INDEX idx_producto_nombre         ON producto(nombre);
CREATE INDEX idx_producto_codigo_barras  ON producto(codigo_barras);
CREATE INDEX idx_alerta_usuario          ON alerta(id_usuario);
CREATE INDEX idx_alerta_leida            ON alerta(leida);
CREATE INDEX idx_receta_nombre           ON receta(nombre);
CREATE INDEX idx_movimiento_inventario   ON movimiento_inventario(id_inventario);
CREATE INDEX idx_zona_hogar              ON zona(id_hogar);

Triggers

Three BEFORE / AFTER triggers on the inventario table automate semáforo state management and movement logging without any application-layer code:

Trigger	Event	Behaviour
`trg_semaforo_insert`	`BEFORE INSERT`	Calculates `estado_caducidad` (`verde` / `amarillo` / `rojo` / `vencido`) from `fecha_vencimiento` at insert time
`trg_semaforo_update`	`BEFORE UPDATE`	Recalculates `estado_caducidad` whenever `fecha_vencimiento` changes
`trg_log_movimiento`	`AFTER UPDATE`	Automatically inserts a `consumido` row into `movimiento_inventario` whenever `cantidad` decreases

Thresholds applied by all three triggers:

State	Days until expiry
`verde`	More than 7 days
`amarillo`	3 – 7 days
`rojo`	0 – 2 days
`vencido`	Past expiry date

MySQL Events (Scheduled)

Two daily MySQL Events handle work that cannot be done at request time:

Event	Schedule	Purpose
`evt_actualizar_semaforo`	Every 24 hours from startup	Bulk-updates `estado_caducidad` for every item in `inventario` — triggers only fire on write, so this event keeps states accurate as calendar days pass
`evt_generar_alertas_vencimiento`	Daily at 08:00	Inserts rows into `alerta` for any `rojo` or `amarillo` item that has not already received an alert today; notifies every member of the owning household

These events require event_scheduler = ON in MySQL. The script sets this globally. If your MySQL instance resets event_scheduler to OFF on restart, add event_scheduler=ON to your my.cnf / my.ini file under [mysqld].

Views

Three views simplify complex joins for the backend:

View	Description
`v_inventario_completo`	Full inventory with product name, category, zone, shelf, quantity, days remaining, and semáforo state; ordered by `fecha_vencimiento ASC`
`v_recetas_posibles`	All recipes annotated with ingredient availability percentage and a `usa_criticos` flag indicating whether the recipe uses near-expiry items
`v_desperdicio_por_hogar`	Aggregated waste metrics per household, product, and discard reason — used by the `/api/v1/desperdicio` endpoint

Stored Procedures

Procedure	Parameters	Description
`sp_tengo_hambre`	`p_id_hogar INT`, `solo_criticos BOOLEAN`, `limite INT`	Returns recipes with ≥ 70% ingredient availability for the given household; when `solo_criticos = TRUE` only counts `amarillo` / `rojo` inventory items; results ordered by critical-item usage then match percentage
`sp_agregar_inventario`	`p_id_hogar`, `p_id_producto`, `p_id_estante`, `p_id_usuario`, `p_cantidad`, `p_fecha_compra`, `p_fecha_vencimiento`, `p_observaciones`	Inserts an inventory item and automatically records the corresponding `agregado` entry in `movimiento_inventario` in a single transaction

Seed Data

The script inserts initial reference data so the application is usable immediately after schema creation: 10 product categories with emoji icons and UI colors: Lácteos, Carnes, Verduras, Frutas, Bebidas, Snacks, Condimentos, Granos, Congelados, Panadería 8 recipe tags with display colors: Vegetariano, Vegano, Sin gluten, Sin lactosa, Rápido, Económico, Alto proteico, Bajo en calorías

Resetting the Development Database

To wipe all data and reload the schema from scratch during development, re-run the initialization script:

mysql -u root -p < backend/infra/mysql/fridgeradar_db.sql

A helper script exists at backend/infra/scripts/reset_dev.sh, but it no longer performs any automated steps — Docker was removed from the project. The script will print a reminder to reset the database manually from your MySQL client:

bash backend/infra/scripts/reset_dev.sh
# ❌ Docker ha sido eliminado del proyecto.
# Para reiniciar la base de datos, hazlo manualmente desde tu cliente MySQL.

Never run the schema initialization script on a production database. The DROP DATABASE IF EXISTS fridgeradar_db statement at the top of fridgeradar_db.sql will permanently delete all data with no recovery path. Protect production by revoking DROP privileges from the application database user.

Backing Up the Database

Use mysqldump to create a portable backup of the database at any point:

mysqldump -u root -p fridgeradar_db > fridgeradar_backup_$(date +%Y%m%d_%H%M%S).sql

To restore from a backup:

mysql -u root -p fridgeradar_db < fridgeradar_backup_20250101_120000.sql

The file backend/infra/scripts/backup_db.sh is present in the repository but is currently empty. Add your mysqldump command there and schedule it with cron for automated nightly backups in production.

Useful Reference Queries

The SQL script includes commented reference queries that mirror what the backend endpoints execute:

-- Products expiring soonest for a household
SELECT producto, zona, estante, cantidad, unidad_medida,
       fecha_vencimiento, dias_restantes, estado_caducidad
FROM v_inventario_completo
WHERE id_hogar = :hogar_id
  AND estado_caducidad IN ('amarillo','rojo','vencido')
ORDER BY dias_restantes ASC;

-- "Tengo Hambre" — recipes using only near-expiry items
CALL sp_tengo_hambre(:hogar_id, TRUE, 10);

-- "Tengo Hambre" — recipes using full inventory
CALL sp_tengo_hambre(:hogar_id, FALSE, 10);

-- Unread alerts for a user
SELECT titulo, mensaje, tipo, fecha_alerta
FROM alerta
WHERE id_usuario = :usuario_id AND leida = FALSE
ORDER BY fecha_alerta DESC;

-- Waste metrics for a household
SELECT * FROM v_desperdicio_por_hogar
WHERE id_hogar = :hogar_id;

Getting Started

Core Concepts

Guides

Deployment

FridgeRadar Database Setup, Schema, and Backups

Requirements

MySQL 8+

MySQL Event Scheduler

Initializing the Schema

Connection String

Schema Overview

Tables

Performance Indexes

Triggers

MySQL Events (Scheduled)

Views

Stored Procedures

Seed Data

Resetting the Development Database

Backing Up the Database

Useful Reference Queries

Build docs developers (and LLMs) love

Getting Started

Core Concepts

Guides

Deployment

Documentation Index

​Requirements

MySQL 8+

MySQL Event Scheduler

​Initializing the Schema

​Connection String

​Schema Overview

​Tables

​Performance Indexes

​Triggers

​MySQL Events (Scheduled)

​Views

​Stored Procedures

​Seed Data

​Resetting the Development Database

​Backing Up the Database

​Useful Reference Queries

Build docs developers (and LLMs) love

Requirements

Initializing the Schema

Connection String

Schema Overview

Tables

Performance Indexes

Triggers

MySQL Events (Scheduled)

Views

Stored Procedures

Seed Data

Resetting the Development Database

Backing Up the Database

Useful Reference Queries