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. 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:
camelCasefield names (gardenId,batteryLevel,locationDescription).- Timestamps as ISO-8601 strings (
createdAt,updatedAt,recordedAt) — callers serialise them directly; the store convertsDate→ ISO at the boundary. - Optional vs null: absent values are surfaced as
undefined(omitted from JSON), notnull, so DTOs stay clean. - Nested objects where the DB uses flat columns — e.g. a garden's
locationis a structured object in the domain model but discrete columns in the row;soilTypeis JSONB ↔ a typed object. - Derived ownership: records that lack a
userIdcolumn (e.g. sensors) surfaceuserIdby 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). 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
404rather 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).
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) |