derekc
84e7b13575
- Track last_login_at on User model, updated on every successful login - Show last login date in admin panel user table - Fix admin/garden date display (datetime strings already contain T separator) - Fix My Garden Internal Server Error (MySQL does not support NULLS LAST syntax) - Fix Log Batch infinite loop when user has zero varieties - Auto-fill batch dates from today when creating a new batch, calculated from selected variety's week offsets (germination, greenhouse, garden) - Update README with new features and batch date auto-fill formula table Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
75 lines
2.7 KiB
SQL
75 lines
2.7 KiB
SQL
-- Sproutly Database Schema (multi-user)
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
hashed_password VARCHAR(255) NOT NULL,
|
|
is_admin BOOLEAN NOT NULL DEFAULT FALSE,
|
|
is_disabled BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
last_login_at TIMESTAMP NULL DEFAULT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS varieties (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
variety_name VARCHAR(100),
|
|
category ENUM('vegetable', 'herb', 'flower', 'fruit') DEFAULT 'vegetable',
|
|
weeks_to_start INT COMMENT 'Weeks before last frost to start seeds indoors',
|
|
weeks_to_greenhouse INT COMMENT 'Weeks before last frost to pot up or move to greenhouse',
|
|
weeks_to_garden INT COMMENT 'Weeks after last frost to transplant outdoors (negative = before frost)',
|
|
days_to_germinate INT DEFAULT 7,
|
|
direct_sow_ok BOOLEAN DEFAULT FALSE,
|
|
frost_tolerant BOOLEAN DEFAULT FALSE,
|
|
sun_requirement ENUM('full_sun', 'part_shade', 'full_shade') DEFAULT 'full_sun',
|
|
water_needs ENUM('low', 'medium', 'high') DEFAULT 'medium',
|
|
color VARCHAR(7) DEFAULT '#52b788',
|
|
notes TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS batches (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
variety_id INT NOT NULL,
|
|
label VARCHAR(100),
|
|
quantity INT DEFAULT 1,
|
|
sow_date DATE,
|
|
germination_date DATE,
|
|
greenhouse_date DATE,
|
|
garden_date DATE,
|
|
status ENUM('planned','germinating','seedling','potted_up','hardening','garden','harvested','failed') DEFAULT 'planned',
|
|
notes TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (variety_id) REFERENCES varieties(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL UNIQUE,
|
|
last_frost_date DATE,
|
|
first_frost_fall_date DATE,
|
|
ntfy_topic VARCHAR(200),
|
|
ntfy_server VARCHAR(200) DEFAULT 'https://ntfy.sh',
|
|
notification_time VARCHAR(5) DEFAULT '07:00',
|
|
timezone VARCHAR(50) DEFAULT 'UTC',
|
|
location_name VARCHAR(100),
|
|
ntfy_username VARCHAR(200),
|
|
ntfy_password VARCHAR(200),
|
|
ntfy_api_key VARCHAR(200),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS notification_log (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT,
|
|
sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
message TEXT,
|
|
status VARCHAR(20),
|
|
error TEXT,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
|
|
);
|