# Demeter Data Models

Demeter data flows through **three representations**. This document explains
each layer and the mapping conventions between them, so you know which shape you
are holding and where it gets transformed.

```
PostgreSQL row            Store record (domain object)        API DTO
(snake_case, native     →  (camelCase, ISO strings,        →  (Zod-validated request/
 pg types, @demeter/core)  nested objects; src/db/*Store)      response; OpenAPI-described)
```

## 1. Storage model (PostgreSQL rows)

Declared in `@demeter/core` (`db-schema.ts`); see the
[database schema](database/schema.md). Characteristics: `snake_case` columns,
UUID primary keys, native Postgres enums, `created_at`/`updated_at` timestamps,
nullable `deleted_at` for soft deletes, and physical units stored in their
canonical column form (e.g. bed dimensions in **centimetres**).

## 2. Domain model (store records)

Each resource has a `*Store` in `apps/demeter/api/src/db/` that returns a
**domain object** — the canonical in-app shape. Conventions:

- **`camelCase`** field names (`gardenId`, `batteryLevel`,
  `locationDescription`).
- **Timestamps as ISO-8601 strings** (`createdAt`, `updatedAt`, `recordedAt`) —
  callers serialise them directly; the store converts `Date` → ISO at the
  boundary.
- **Optional vs null:** absent values are surfaced as `undefined` (omitted from
  JSON), not `null`, so DTOs stay clean.
- **Nested objects** where the DB uses flat columns — e.g. a garden's `location`
  is a structured object in the domain model but discrete columns in the row;
  `soilType` is JSONB ↔ a typed object.
- **Derived ownership:** records that lack a `userId` column (e.g. sensors)
  surface `userId` by joining through their parent (sensor → garden → user).

Example (`SensorRecord`):
`{ id, gardenId, gardenBedId?, userId, type, name, manufacturer?, model?, serialNumber?, calibratedAt?, batteryLevel?, isActive, locationDescription, createdAt, updatedAt, deletedAt? }`.

## 3. API model (request/response DTOs)

At the HTTP boundary, routes validate input with **Zod** and describe
request/response shapes in their Fastify `schema` (which feeds OpenAPI — see the
[API reference](api/README.md)). The DTO is usually the domain object minus
internal fields, plus pagination envelopes for list endpoints. Validation
rejects malformed input before it reaches a store, and NUL bytes / over-long
strings are stripped/bounded (`text-utils.ts`).

## Cross-layer mapping conventions

| Concern        | Row                          | Domain / API                                                                 |
| -------------- | ---------------------------- | ---------------------------------------------------------------------------- |
| Naming         | `snake_case`                 | `camelCase`                                                                  |
| Timestamps     | `timestamptz` (`Date`)       | ISO-8601 string                                                              |
| Absent value   | `NULL`                       | `undefined` (omitted)                                                        |
| Bed dimensions | `width_cm`, `length_cm` (cm) | metres at the API where the client expects metres; converted at the boundary |
| Soil type      | JSONB column                 | typed object                                                                 |
| Location       | discrete columns             | nested `location` object                                                     |
| IDs            | UUID                         | UUID string (JWT `sub` = `demeter_users.id`)                                 |

## Validation & integrity

- **Boundary validation:** Zod schemas on every route; invalid UUIDs resolve to
  `404` rather than leaking a DB error.
- **Referential integrity:** enforced by foreign keys (see the schema doc);
  ownership is checked in the route layer against the authenticated user.
- **Secrets:** never modelled in plaintext — passwords, refresh tokens, and API
  keys exist only as hashes (see
  [ADR-0004](architecture/adr/0004-jwt-refresh-rotation-auth.md)).

## Where each lives

| Layer                 | Location                                                  |
| --------------------- | --------------------------------------------------------- |
| Storage schema        | `libs/demeter/core/src/db-schema.ts`                      |
| Domain records        | `apps/demeter/api/src/db/*-store.ts`                      |
| API DTOs / validation | `apps/demeter/api/src/routes/*.ts` (Zod + Fastify schema) |
| Shared domain types   | `libs/demeter/*` (per-domain libraries)                   |
