base_datos.md

🗄️ ECOA Database

This document describes the PostgreSQL database structure hosted on Supabase.

References

• Backups and Schemas: You can find the complete SQL schemas and backup CSV files in the docs/backups folder.
• Configuration: For instructions on how to connect and configure the database, refer to the Backend README.

Main Tables

1. Users and Devices

• users: Stores registered user information.

  • id (UUID, PK)
  • name, email, rol
  • level, experience_points (Gamification)

• devices: Registry of authorized hardware (Raspberry Pi).

  • id (UUID, PK)
  • serial_number (Unique)
  • model, location

2. Plant Management

• plants: Plants registered in the system.

  • id (UUID, PK)
  • user_id (FK -> users)
  • device_id (FK -> devices)
  • name, species, health_status

• plant_stats: Historical sensor measurements.

  • id (UUID, PK)
  • plant_id (FK -> plants)
  • temperature, light, soil_moisture
  • recorded_at (Timestamp)

• plant_status: Calculated status and "mood" of the plant.

  • plant_id (FK -> plants)
  • status ('healthy', 'bad', 'recovering')
  • mood_face (Emoji/Icon)

3. Alert System

• alerts: Notifications generated by the system.
  • id (UUID, PK)
  • plant_id, user_id
  • title, message, priority
  • status ('active', 'resolved')

4. Community and Gamification

• donations: Record of donations to foundations.
• foundations: Beneficiary foundations.
• accessories: Catalog of virtual accessories for plants.
• plants_accessories: Accessories acquired by a plant.

Key Relationships

• A User can have multiple Plants.
• A Plant can be associated with a single Device (Raspberry Pi).
• A Plant has multiple records of Statistics (plant_stats) and Alerts.

To see the exact SQL schema for each table, check the files in docs/backups/README.md.