Eve library

Demeter Database Schema

Guide

Revision 1

On this page

The canonical schema is declared in libs/demeter/core/src/db-schema.ts using Drizzle ORM, applied to PostgreSQL via the migrations in apps/demeter/api/drizzle/ (see the migration strategy). This page is a human-oriented map of the 29 demeter_* tables and the enum catalog; the TypeScript source is authoritative for exact column types.

Conventions#

  • Namespace: every table and enum is prefixed demeter_ (the Drizzle config tablesFilter enforces this so migrations never touch other domains' tables in the shared database).
  • Primary keys: UUID (gen_random_uuid()), surfaced as ISO strings at the API boundary.
  • Timestamps: created_at / updated_at on mutable entities; soft deletes use a nullable deleted_at where present (gardens, beds, plantings, sensors…).
  • Ownership: most rows carry a user_id FK to demeter_users, or derive ownership through a parent (e.g. sensors → garden → user).

Domains & tables#

Identity & authentication (5)#

Table Purpose Key FKs
demeter_users Accounts: email, display name, password hash, roles, permissions, OAuth identity, experience level — (root)
demeter_refresh_tokens Rotating refresh tokens with family for reuse-detection → users
demeter_magic_links Passwordless login tokens (email-scoped, single-use) — (email)
demeter_api_keys Hashed IoT/integration API keys with prefix + permissions → users
demeter_sessions Device sessions with per-user concurrency cap → users

Gardens (3)#

Table Purpose Key FKs
demeter_gardens A garden space: location, type, is_public → users
demeter_garden_beds Beds/zones within a garden: dimensions (cm), soil, orientation, irrigation → gardens
demeter_garden_members Household/sharing membership + role → gardens (+ users)

Plant reference catalog (2)#

Table Purpose Key FKs
demeter_plants Species encyclopedia: lifecycle, growing conditions, spacing, sun/soil needs — (reference)
demeter_companion_plants Companion/antagonist relationships between two species → plants ×2

Planting lifecycle (3)#

Table Purpose Key FKs
demeter_plantings A planted instance: bed, species, dates, current growth stage → garden_beds, → plants, → users
demeter_planting_stages Stage-transition history (germination → maturity) → plantings
demeter_harvests Harvest events: quantity, unit, usage, quality → plantings, → users

Operations & records (3)#

Table Purpose Key FKs
demeter_tasks Maintenance tasks: type, priority, due/completed → gardens, → users
demeter_observations Field notes & photos, optionally tied to a planting → gardens, → users, → plantings
demeter_soil_tests Soil test results per bed (pH, NPK, amendments) → garden_beds, → users

IoT & sensing (4)#

Table Purpose Key FKs
demeter_sensors Devices: type, calibration, battery, location → gardens, → garden_beds
demeter_sensor_readings Time-series readings (value, unit, recorded_at, metadata) → sensors
demeter_sensor_alert_configs Per-(sensor, metric) threshold + notify prefs → sensors
demeter_alerts Raised alerts (type, severity) → users, → gardens

Plant health (2)#

Table Purpose Key FKs
demeter_pest_reports Pest/disease reports: type, severity → gardens, → users, → plantings
demeter_treatments Treatments applied to a pest report + effectiveness → pest_reports, → users

Weather (2)#

Table Purpose Key FKs
demeter_weather_records Captured weather observations/forecasts per garden → gardens
demeter_weather_locations Saved lookup locations for weather → gardens

Community & social (5)#

Table Purpose Key FKs
demeter_community_posts User posts (with type/tags, public) → users
demeter_community_post_likes Likes (unique per user+post) → posts, → users
demeter_community_comments Comments on posts → posts, → users
demeter_community_challenges Seasonal challenges → users (creator)
demeter_community_challenge_participants Challenge enrolment + progress → challenges, → users

Enum catalog#

Postgres enums (all demeter_-prefixed) constrain domain columns:

plant_type, plant_lifecycle, growth_stage, soil_type, task_type, sensor_type, irrigation_type, garden_type, pest_type, treatment_type, task_priority, alert_type, alert_severity (info/warning/critical), experience_level, unit_system (metric/imperial), sun_exposure_category, harvest_usage, aspect (N/S/E/W/NE/NW/SE/SW), pest_severity, treatment_effectiveness.

Relationship overview#

text
users ─┬─< gardens ─┬─< garden_beds ─< plantings ─┬─< planting_stages
       │            │                  │           └─< harvests
       │            ├─< garden_members │
       │            ├─< tasks          ├──(observations)
       │            ├─< sensors ─< sensor_readings
       │            │        └─< sensor_alert_configs
       │            ├─< weather_records / weather_locations
       │            ├─< pest_reports ─< treatments
       │            └─< alerts
       ├─< soil_tests (→ garden_beds)
       ├─< refresh_tokens / sessions / api_keys
       └─< community_posts ─┬─< post_likes
                            └─< comments
       community_challenges ─< challenge_participants

plants ─< companion_plants (self-referential pairs)

Keeping this in sync#

The table count is asserted by apps/demeter/api/test/integration/server.integration.spec.ts (currently 29). When a migration adds or removes a table, update both that assertion and this document in the same change.