Skip to content

System Database

Jump to bottom
mk7091 edited this page Apr 9, 2026 · 2 revisions

1. Introduction

1.1 Purpose

This document provides a comprehensive reference for the PostgreSQL database used in the Cut Flowers Monitoring System. It covers schema design, table definitions, relationships, data flow, setup procedures, and operational guidance for developers and system administrators.

1.2 Scope

This documentation covers:

  • Schema definitions and design decisions
  • Table structures, constraints, and indexes
  • Data flow between the IoT device, backend, and database
  • Installation, access, and maintenance procedures

1.3 Intended Audience

  • Backend developers working on API or database features
  • System administrators responsible for deployment and maintenance
  • Future contributors who need to understand the system architecture

2. System Overview

2.1 System Description

The Cut Flowers Monitoring System is an IoT-based application that tracks flower dehydration levels using field-deployed monitoring devices. Each device captures images at regular intervals and sends them to the backend, where an AI model computes a dehydration_score. The database is the central store for all application data.

The database stores:

  • User accounts — grower credentials and contact information
  • Monitoring devices — physical ESP32 units and their API keys
  • Time-series records — per-capture dehydration scores and image paths
  • Alert history — notifications triggered when thresholds are exceeded

2.2 Architecture Context

Frontend (Next.js/React)  →  Backend API (Node.js/Express)  →  PostgreSQL Database
IoT Device (ESP32)        →  Backend API                    →  PostgreSQL Database

3. Database Design

3.1 ER Diagram

ISTE501 ER Diagram (1)

3.2 Design Decisions

Decision Rationale
Many-to-many (users ↔ monitors) via users_monitors Supports flexible device ownership while enforcing single-tenancy at the application level
api_key on monitors with a unique index Enables secure, stateless device authentication without user credentials
dehydration_score instead of generic value Makes the column's meaning explicit and keeps alert threshold logic self-documenting (score > 0.20 → alert)
Separate alerts table linked to records Maintains full alert history and makes each alert traceable to the exact capture event
alert_method as an ENUM (email, sms) Enforces valid delivery methods at the database level
Cascading deletes on all foreign keys Ensures referential integrity when a user or device is removed
Unique constraint on (monitor_id, time) in records Prevents duplicate capture events from the same device

4. Schema Reference

4.1 Tables Overview

Table Description
users Registered growers with authentication credentials and contact info
monitors Physical IoT monitoring devices with unique API keys
users_monitors Junction table — links users to their assigned monitors
records Time-series capture events: dehydration score + image path
alerts Alert events triggered when dehydration_score drops below the health threshold

4.2 users

Stores registered application users (growers).

Column Type Constraints Description
user_id SERIAL PRIMARY KEY Auto-incrementing unique identifier
email TEXT NOT NULL, UNIQUE Login email address; also used for email notifications
username TEXT NOT NULL, UNIQUE Human-readable display name
password TEXT NOT NULL BCrypt password hash — never stored in plain text
first_name TEXT NOT NULL User's first name
last_name TEXT NOT NULL User's last name
phone_number TEXT nullable Phone number for SMS notifications

4.3 monitors

Represents a physical Cut Flowers monitoring device deployed in the field.

Column Type Constraints Description
monitor_id SERIAL PRIMARY KEY Auto-incrementing unique identifier
name TEXT NOT NULL Human-readable label (e.g., "Greenhouse A-1")
api_key TEXT NOT NULL, UNIQUE Secret key used by the device to authenticate API requests

Index: idx_monitors_api_key ON monitors(api_key) — speeds up device authentication lookups.

4.4 users_monitors

Junction table implementing the many-to-many relationship between users and monitors.

Column Type Constraints Description
monitor_id INTEGER NOT NULL, FK → monitors.monitor_id, CASCADE References the assigned monitor
user_id INTEGER NOT NULL, FK → users.user_id, CASCADE References the owning user

Primary Key: (monitor_id, user_id) — composite key prevents duplicate assignments.
Index: idx_users_monitors_user_id ON users_monitors(user_id) — speeds up user-to-monitor lookups.

Note: The schema supports multiple users per monitor, but single-tenancy is enforced at the application level — in production, each monitor is assigned to exactly one grower.

4.5 records

Stores time-series data generated by monitors. Each row corresponds to a single image capture event.

Column Type Constraints Description
record_id SERIAL PRIMARY KEY Auto-incrementing unique identifier
monitor_id INTEGER NOT NULL, FK → monitors.monitor_id, CASCADE The device that produced this record
time TIMESTAMPTZ NOT NULL Timestamp of the capture event (timezone-aware)
dehydration_score DOUBLE PRECISION NOT NULL AI model output: 0.0 (fully dehydrated) to 1.0 (fully healthy). Alerts trigger when score < 0.80 (i.e., score > 0.20 risk). Only scores from predictions with >90% model confidence are stored.
file_path TEXT NOT NULL Server-side path to the stored image (e.g., /imgs/1/2026-01-15T07-00-00.000Z.jpg)

Unique constraint: (monitor_id, time) — prevents duplicate entries for the same device at the same timestamp.
Index: idx_records_time ON records(time) — improves performance on time-range queries for dashboard history views.

4.6 alerts

Stores alert events triggered when a record's dehydration_score falls below the health threshold.

Column Type Constraints Description
alert_id SERIAL PRIMARY KEY Auto-incrementing unique identifier
record_id INTEGER NOT NULL, FK → records.record_id, CASCADE The capture event that triggered this alert
alert_type TEXT NOT NULL Human-readable label for the alert (e.g., "Critical Dehydration", "Warning Dehydration")
alert_method alert_method NOT NULL Delivery channel — ENUM: 'email' or 'sms'
triggered_at TIMESTAMPTZ DEFAULT NOW() Timestamp when the alert was created

ENUM type: CREATE TYPE alert_method AS ENUM ('email', 'sms') — enforced at the database level.

5. Relationships & Constraints

users ──< users_monitors >── monitors ──< records ──< alerts
Relationship Cardinality Foreign Key
users → users_monitors One-to-many users_monitors.user_id → users.user_id
monitors → users_monitors One-to-many users_monitors.monitor_id → monitors.monitor_id
monitors → records One-to-many records.monitor_id → monitors.monitor_id
records → alerts One-to-many alerts.record_id → records.record_id

All foreign keys use ON DELETE CASCADE — deleting a parent row automatically removes all child rows.

6. Data Flow

6.1 Device Data Flow

1. ESP32 device captures an image (every 10 min, 6:00 AM – 8:00 PM EST)
2. AI model calculates dehydration_score (only stored if model confidence > 90%)
3. Device sends image + metadata to backend via HTTPS POST
4. Backend validates api_key from request body
5. Record is inserted into the records table
6. If dehydration_score < 0.80, an alert row is created in alerts
7. Notification is dispatched via email or SMS within 2–5 minutes

6.2 User Data Flow

1. User logs in — backend validates credentials, issues JWT (stored in HTTP-only cookie)
2. User requests monitor data
3. Backend checks users_monitors to confirm authorization
4. Records are fetched (up to 4-week history) and returned to the dashboard

6.3 Alert Flow

1. Record inserted with dehydration_score
2. Backend evaluates score against threshold (< 0.80)
3. If threshold exceeded → alert row created in alerts table
4. Notification sent via user's preferred method (email or SMS)

7. Installation & Setup

7.1 Requirements

  • PostgreSQL 13 or higher
  • Node.js (for backend integration)
  • psql command-line client

7.2 Database Setup

# 1. Create the database and apply the schema
psql -U postgres -f CutFlowerDb_F.sql

# 2. (Optional) Load sample data for development/testing
psql -U postgres -f SampleData.sql

The schema file drops and recreates the flowers database, so run it only once or in a clean environment.

7.3 Environment Variables

Add the following to your .env file:

DB_NAME=flowers
DB_USER=flowers
DB_PASSWORD=yourpassword
DB_HOST=localhost
DB_PORT=5432

8. Database Access Layer

8.1 Overview

All database access is centralized in data_access.js. It uses a pg connection pool (max 20 clients, 30s idle timeout) and exposes named functions for every database operation. All functions are async and return null or [] on error rather than throwing — errors are logged to console.error.

8.2 Core Utilities

These two functions underpin all other operations:

Function Signature Description
getData (table, columns, criteria, limit, orderBy) Flexible parameterized SELECT — supports column lists, WHERE, ORDER BY, and LIMIT
insertData (table, data) Dynamic parameterized INSERT ... RETURNING * — builds column/value lists from an object

8.3 Record Functions

Function Signature Description
addRecord (monitorID, value, imgName) Inserts a new capture record; clamps dehydration_score to 0.0–1.0 and validates it is finite
getPastRecords (monitorID, limit) Returns the most recent N records for a monitor, reversed to chronological order for chart display
getRecordById (recordID) Returns a single record row by primary key
getRecordsInRange (monitorID, startTime, endTime) Returns all records for a monitor within a time range, ordered chronologically
countRecordsInRange (monitorID, startTime, endTime) Returns the count of records for a monitor within a time range
getHourlyAverageRecordsInRange (monitorID, startTime, endTime) Returns hourly aggregates (avg, min, max, sample count) for a monitor within a time range; excludes zero scores from averages

8.4 Alert Functions

Function Signature Description
addAlert (recordID, alertType, alertMethod) Inserts a new alert row linked to a record
hasRecentAlert (monitorID, alertType, alertMethod, cooldownHours) Returns true if an alert of the same type/method was already sent within the cooldown window (default 24h) — used to prevent duplicate notifications

8.5 Monitor Functions

Function Signature Description
monitorExists (monitorID) Returns true if the monitor ID exists in the monitors table
getMonitorByApiKey (apiKey) Returns the monitor row matching a device API key; used to authenticate incoming device uploads
createMonitor (name, apiKey) Inserts a new monitor with a provided API key
getMonitors (userID) Returns all monitors associated with a user via users_monitors
associateUserToMonitor (userID, monitorID) Links a user to a monitor in users_monitors; silently skips if the association already exists

8.6 User Functions

Function Signature Description
createUser (email, username, passwordHash, firstName, lastName, phoneNumber) Inserts a new user; phone_number is omitted from the insert if not provided
getUserByUsername (username) Returns a user row by username; used during login
getUserByEmail (email) Returns a user row by email address
getUserById (userId) Returns a user row by ID — excludes password for security
updateUserSettings (userId, settingsPatch) Merges a partial settings object into the user's settings JSONB column using PostgreSQL `
userCanAccessMonitor (userID, monitorID) Returns true if a users_monitors row exists for the given user/monitor pair

8.7 Notification Functions

Function Signature Description
getMonitorUserEmails (monitorID) Returns all email addresses of users assigned to a monitor where notifications.enabled is true (defaults to true if unset)
isMonitorEmailNotificationEnabled (monitorID, email) Returns true if a specific recipient has notifications enabled for a given monitor
getRecentAlertsForUser (userID, limit) Returns the most recent alerts (up to 100) for all monitors a user has access to, joined with record and monitor details

9. Security

9.1 User Authentication

  • Passwords are hashed using BCrypt — no plain-text passwords are stored at any point
  • Sessions are managed via JWT tokens stored in HTTP-only cookies
  • Tokens are validated on every protected API request

9.2 Device Authentication

  • Each monitor has a unique api_key stored in the monitors table
  • The backend validates the api_key from the request body before accepting any data upload
  • The idx_monitors_api_key index ensures fast key lookups under load

9.3 Data Protection

  • Images are stored in a non-publicly served directory with restricted UNIX permissions
  • All traffic is secured via HTTPS/TLS
  • The system does not interface with the database root user

10. Maintenance & Operations

10.1 Backup

pg_dump flowers > backup_$(date +%Y%m%d).sql

Run this on a regular schedule (e.g., via cron). Store backups off-server.

10.2 Restore

psql -U postgres -f backup_YYYYMMDD.sql

10.3 Data Integrity

  • PRIMARY KEY constraints ensure row uniqueness across all tables
  • FOREIGN KEY constraints with CASCADE enforce relational integrity
  • UNIQUE (monitor_id, time) on records prevents duplicate capture entries
  • UNIQUE on monitors.api_key prevents key collisions

10.4 Performance Considerations

Index Table Purpose
idx_records_time records Speeds up 4-week history queries on the dashboard
idx_monitors_api_key monitors Speeds up device authentication on every upload
idx_users_monitors_user_id users_monitors Speeds up authorization checks per user

11. Future Improvements

  • Real-time data streaming — WebSocket or SSE support for live dashboard updates
  • Advanced alerting rules — configurable per-user thresholds and quiet hours
  • Data archiving — automated archival of records older than the 4-week display window
  • Role-based access control — Admin vs. Grower role enforcement at the database level
  • Multi-tenancy — shared dashboards or organization-level device grouping

12. Appendix

12.1 SQL Schema — CutFlowerDb_F.sql 

DROP DATABASE IF EXISTS flowers WITH (FORCE);

CREATE DATABASE flowers;
ALTER DATABASE flowers OWNER TO flowers;
\c flowers;

-- USERS
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    phone_number TEXT
);

-- MONITORS
CREATE TABLE monitors (
    monitor_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    api_key TEXT NOT NULL UNIQUE
);

CREATE INDEX idx_monitors_api_key ON monitors(api_key);

-- USERS_MONITORS (Many-to-Many)
CREATE TABLE users_monitors (
    monitor_id INTEGER NOT NULL REFERENCES monitors(monitor_id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    PRIMARY KEY (monitor_id, user_id)
);

CREATE INDEX idx_users_monitors_user_id ON users_monitors(user_id);

-- RECORDS
CREATE TABLE records (
    record_id SERIAL PRIMARY KEY,
    monitor_id INTEGER NOT NULL REFERENCES monitors(monitor_id) ON DELETE CASCADE,
    time TIMESTAMPTZ NOT NULL,
    dehydration_score DOUBLE PRECISION NOT NULL,
    file_path TEXT NOT NULL,
    UNIQUE (monitor_id, time)
);

CREATE INDEX idx_records_time ON records(time);

-- ALERT METHOD ENUM
CREATE TYPE alert_method AS ENUM ('email', 'sms');

-- ALERTS
CREATE TABLE alerts (
    alert_id SERIAL PRIMARY KEY,
    record_id INTEGER NOT NULL REFERENCES records(record_id) ON DELETE CASCADE,
    alert_type TEXT NOT NULL,
    alert_method alert_method NOT NULL,
    triggered_at TIMESTAMPTZ DEFAULT NOW()
);

12.2 Sample Data — SampleData.sql

The sample data file seeds three devices with distinct behavior profiles for development and testing:

Device monitor_id Behavior Score Range
healthy_device 1 Consistently healthy 0.88 – 0.94
dehydrating_device 2 Progressive dehydration 0.03 – 0.45
warning_device 3 Concerning decline 0.29 – 0.65

Two users are seeded (green_valley, sunrise_farms). Two sample alerts are included — one critical, one warning — to verify the alerting pipeline.

# Load sample data (after schema setup)
psql -U postgres -f SampleData.sql

Passwords in the sample data are BCrypt hashes. Do not use sample credentials in production.

Last updated: April 2026 | Team Thunder Bay | ISTE 501

Clone this wiki locally