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
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 Copy
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.