# Demeter Database Schema

The canonical schema is declared in
[`libs/demeter/core/src/db-schema.ts`](../../../../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](../../api/docs/operations/migration-strategy.md)). 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

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