Database Design
Owner: Backend
Reviewers: Architecture, QA
Status: Draft
Version: 0.1
Last Updated: 2026-09-16
Review Cycle: Per schema migration
Source Of Truth
The source of truth for the database schema is ../../backend/prisma/schema.prisma.
Confirmed Data Areas
| Area | Examples | Status |
|---|---|---|
| Identity and access | Users, roles, sessions/tokens, farm scope assignments. | PARTIAL |
| Farm structure | Farms, ponds, crops and related operational ownership. | PARTIAL |
| Operations | Farming logs, feeding, tickets, notifications, reports. | PARTIAL |
| Water and telemetry | Sensors, parameter thresholds, raw/staging telemetry and normalized readings. | PARTIAL |
| Knowledge | Handbook content and references. | PARTIAL |
| Audit | Activity logs and operational trace records. | PARTIAL |
Migration Policy
- Use Prisma migrations for schema changes.
- Review data migration impact before changing enums, role names, foreign keys or telemetry tables.
- Production changes must use
prisma migrate deploy, not ad hoc schema pushes.
Open Items
| Item | Status |
|---|---|
| Published ERD generated from current Prisma schema | TBD |
| Retention policy for telemetry and activity logs | TBD |
| Backup and restore test evidence | TBD |