Aditya Pulipaka
576c4acc01
All checks were successful
Deploy to Server / deploy (push) Successful in 17s
192 lines
9.1 KiB
PL/PgSQL
192 lines
9.1 KiB
PL/PgSQL
-- BlindMaster schema. Idempotent: safe on fresh DB and replays cleanly against
|
|
-- an existing one. Source of truth for table structure, constraints, indexes,
|
|
-- and triggers. Restored from oldVersion.sql backup + newer additions.
|
|
|
|
-- ── Trigger: auto-delete empty groups ───────────────────────────────────────
|
|
CREATE OR REPLACE FUNCTION public.delete_group_if_empty() RETURNS trigger
|
|
LANGUAGE plpgsql AS $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM group_peripherals WHERE group_id = OLD.group_id) THEN
|
|
DELETE FROM groups WHERE id = OLD.group_id;
|
|
END IF;
|
|
RETURN OLD;
|
|
END;
|
|
$$;
|
|
|
|
-- ── Core auth ────────────────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
email TEXT NOT NULL UNIQUE,
|
|
password_hash_string TEXT NOT NULL,
|
|
name TEXT,
|
|
verification_token TEXT,
|
|
is_verified BOOLEAN NOT NULL DEFAULT FALSE,
|
|
timezone TEXT DEFAULT 'America/Chicago',
|
|
apns_token TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS users_id_idx ON users(id);
|
|
|
|
-- UNLOGGED: ephemeral session state — no WAL writes, cleared on crash (correct
|
|
-- behaviour; clients reconnect anyway). One row per user enforced by PK.
|
|
CREATE UNLOGGED TABLE IF NOT EXISTS user_tokens (
|
|
user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
|
|
token TEXT NOT NULL,
|
|
connected BOOLEAN DEFAULT FALSE,
|
|
socket VARCHAR(255)
|
|
);
|
|
|
|
-- Convert to UNLOGGED if the table already exists as a logged table.
|
|
-- user_tokens holds only live socket state; clearing it on restart is correct.
|
|
DO $$ BEGIN
|
|
IF EXISTS (SELECT 1 FROM pg_class WHERE relname = 'user_tokens' AND relpersistence = 'p') THEN
|
|
ALTER TABLE user_tokens SET UNLOGGED;
|
|
END IF;
|
|
END $$;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_user_tokens_token ON user_tokens(token);
|
|
CREATE INDEX IF NOT EXISTS idx_user_tokens_active_socket ON user_tokens(user_id, socket) WHERE connected = true;
|
|
CREATE INDEX IF NOT EXISTS idx_user_tokens_socket_connected ON user_tokens(socket) WHERE connected = true;
|
|
|
|
CREATE TABLE IF NOT EXISTS password_reset_tokens (
|
|
email TEXT PRIMARY KEY,
|
|
token TEXT NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_pending_emails (
|
|
user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
|
|
pending_email TEXT NOT NULL,
|
|
token TEXT NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
|
|
-- ── Hardware: hubs (devices) and their controlled blinds (peripherals) ───────
|
|
CREATE TABLE IF NOT EXISTS devices (
|
|
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
device_name TEXT NOT NULL,
|
|
max_ports INTEGER NOT NULL DEFAULT 4,
|
|
battery_soc SMALLINT,
|
|
timezone TEXT,
|
|
UNIQUE (user_id, device_name)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_devices_user_id ON devices(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_devices_max_ports ON devices(max_ports);
|
|
|
|
-- UNLOGGED: ephemeral device connection state, same rationale as user_tokens.
|
|
CREATE UNLOGGED TABLE IF NOT EXISTS device_tokens (
|
|
device_id INTEGER NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
|
|
token TEXT NOT NULL,
|
|
connected BOOLEAN DEFAULT FALSE,
|
|
socket VARCHAR(255)
|
|
);
|
|
|
|
DO $$ BEGIN
|
|
IF EXISTS (SELECT 1 FROM pg_class WHERE relname = 'device_tokens' AND relpersistence = 'p') THEN
|
|
ALTER TABLE device_tokens SET UNLOGGED;
|
|
END IF;
|
|
END $$;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_device_tokens_token ON device_tokens(token);
|
|
CREATE INDEX IF NOT EXISTS idx_device_tokens_device_id_connected ON device_tokens(device_id) WHERE connected = true;
|
|
CREATE INDEX IF NOT EXISTS idx_device_tokens_active_socket ON device_tokens(device_id, socket) WHERE connected = true;
|
|
CREATE INDEX IF NOT EXISTS idx_device_tokens_socket_connected ON device_tokens(socket) WHERE connected = true;
|
|
|
|
-- autovacuum tuned aggressively: last_pos is updated on every blind movement.
|
|
CREATE TABLE IF NOT EXISTS peripherals (
|
|
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
device_id INTEGER NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
peripheral_number INTEGER NOT NULL,
|
|
peripheral_name TEXT NOT NULL,
|
|
calibrated BOOLEAN DEFAULT FALSE,
|
|
await_calib BOOLEAN DEFAULT FALSE,
|
|
last_pos INTEGER DEFAULT 0 CHECK (last_pos >= 0 AND last_pos <= 10),
|
|
last_set TIMESTAMPTZ,
|
|
UNIQUE (device_id, peripheral_number),
|
|
UNIQUE (device_id, peripheral_name)
|
|
) WITH (autovacuum_vacuum_scale_factor = 0.02);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_peripherals_device_id ON peripherals(device_id);
|
|
CREATE INDEX IF NOT EXISTS idx_peripherals_id_user ON peripherals(id, user_id);
|
|
|
|
-- ── Multi-blind groups ───────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS groups (
|
|
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
timezone TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE (user_id, name)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_groups_user_id ON groups(user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS group_peripherals (
|
|
group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
|
|
peripheral_id INTEGER NOT NULL REFERENCES peripherals(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (group_id, peripheral_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_group_peripherals_group_id ON group_peripherals(group_id);
|
|
CREATE INDEX IF NOT EXISTS idx_group_peripherals_peripheral_id ON group_peripherals(peripheral_id);
|
|
|
|
-- Trigger fires after any peripheral is removed from a group; deletes the group
|
|
-- itself if it is now empty.
|
|
DO $$ BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_trigger WHERE tgname = 'trigger_cleanup_empty_groups'
|
|
) THEN
|
|
CREATE TRIGGER trigger_cleanup_empty_groups
|
|
AFTER DELETE ON group_peripherals
|
|
FOR EACH ROW EXECUTE FUNCTION public.delete_group_if_empty();
|
|
END IF;
|
|
END $$;
|
|
|
|
-- ── Schedules ────────────────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS schedules (
|
|
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
schedule_type VARCHAR(50) NOT NULL CHECK (schedule_type IN ('peripheral', 'group')),
|
|
peripheral_id INTEGER REFERENCES peripherals(id) ON DELETE CASCADE,
|
|
device_id INTEGER REFERENCES devices(id) ON DELETE CASCADE,
|
|
peripheral_number INTEGER,
|
|
group_id INTEGER REFERENCES groups(id) ON DELETE CASCADE,
|
|
target_position INTEGER NOT NULL,
|
|
cron_expression VARCHAR(100) NOT NULL,
|
|
cron_minute VARCHAR(50) NOT NULL,
|
|
cron_hour VARCHAR(50) NOT NULL,
|
|
cron_days VARCHAR(50) NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT peripheral_or_group CHECK (
|
|
(schedule_type = 'peripheral' AND peripheral_id IS NOT NULL AND group_id IS NULL) OR
|
|
(schedule_type = 'group' AND group_id IS NOT NULL AND peripheral_id IS NULL)
|
|
)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_schedules_user_id ON schedules(user_id);
|
|
|
|
-- Prevent duplicate cron times for the same target.
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_unique_peripheral_schedule
|
|
ON schedules(peripheral_id, cron_expression)
|
|
WHERE schedule_type = 'peripheral';
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_unique_group_schedule
|
|
ON schedules(group_id, cron_expression)
|
|
WHERE schedule_type = 'group';
|
|
|
|
-- ── Idempotent column additions for older DBs ────────────────────────────────
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS apns_token TEXT;
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS timezone TEXT DEFAULT 'America/Chicago';
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS verification_token TEXT;
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS is_verified BOOLEAN NOT NULL DEFAULT FALSE;
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ DEFAULT NOW();
|
|
ALTER TABLE devices ADD COLUMN IF NOT EXISTS battery_soc SMALLINT;
|
|
ALTER TABLE devices ADD COLUMN IF NOT EXISTS timezone TEXT;
|
|
ALTER TABLE groups ADD COLUMN IF NOT EXISTS timezone TEXT;
|