derekc
aa12648228
- Rename app from Eggtracker to Yolkbook throughout - Add JWT-based authentication (python-jose, passlib/bcrypt) - Add users table; all data tables gain user_id FK for full data isolation - Super admin credentials sourced from ADMIN_USERNAME/ADMIN_PASSWORD env vars, synced on every startup; orphaned rows auto-assigned to admin post-migration - Login page with self-registration; JWT stored in localStorage (30-day expiry) - Admin panel (/admin): list users, reset passwords, disable/enable, delete, and impersonate (Login As) with Return to Admin banner - Settings modal (gear icon in nav): timezone selector and change password - Timezone stored per-user; stats date windows computed in user's timezone; date input setToday() respects user timezone via Intl API - migrate_v2.sql for existing single-user installs - Auto-migration adds timezone column to users on startup - Updated README with full setup, auth, admin, and migration docs Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
51 lines
2.2 KiB
SQL
51 lines
2.2 KiB
SQL
-- Eggtracker v2 migration — adds multi-user support to an existing database.
|
|
-- Run this ONCE on an existing install BEFORE restarting with the new image:
|
|
--
|
|
-- docker compose exec db mysql -u root -p"${MYSQL_ROOT_PASSWORD}" eggtracker < mysql/migrate_v2.sql
|
|
--
|
|
-- After running this script, restart the stack (docker compose up -d --build).
|
|
-- The API will automatically create the admin user (from ADMIN_USERNAME /
|
|
-- ADMIN_PASSWORD in .env) and assign all existing records to that admin account.
|
|
--
|
|
-- NOTE: Run this script only ONCE. Running it again will fail on the ADD COLUMN
|
|
-- statements since the columns will already exist.
|
|
|
|
USE eggtracker;
|
|
|
|
-- ── Create users table ────────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
username VARCHAR(64) NOT NULL,
|
|
hashed_password VARCHAR(255) NOT NULL,
|
|
is_admin TINYINT(1) NOT NULL DEFAULT 0,
|
|
is_disabled TINYINT(1) NOT NULL DEFAULT 0,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uq_username (username)
|
|
) ENGINE=InnoDB;
|
|
|
|
-- ── Add user_id columns (nullable so existing rows remain valid) ───────────────
|
|
ALTER TABLE egg_collections
|
|
ADD COLUMN user_id INT UNSIGNED NULL AFTER id,
|
|
ADD INDEX idx_user_id (user_id);
|
|
|
|
ALTER TABLE flock_history
|
|
ADD COLUMN user_id INT UNSIGNED NULL AFTER id,
|
|
ADD INDEX idx_user_id (user_id);
|
|
|
|
ALTER TABLE feed_purchases
|
|
ADD COLUMN user_id INT UNSIGNED NULL AFTER id,
|
|
ADD INDEX idx_user_id (user_id);
|
|
|
|
ALTER TABLE other_purchases
|
|
ADD COLUMN user_id INT UNSIGNED NULL AFTER id,
|
|
ADD INDEX idx_user_id (user_id);
|
|
|
|
-- ── Remove old single-column unique index on egg_collections.date ─────────────
|
|
-- It will be replaced by (user_id, date) once the admin is seeded.
|
|
ALTER TABLE egg_collections DROP INDEX uq_date;
|
|
|
|
-- The API startup will:
|
|
-- 1. Create the admin user from ADMIN_USERNAME / ADMIN_PASSWORD in .env
|
|
-- 2. Set user_id = admin.id on all rows where user_id IS NULL
|