54 lines
2.1 KiB
MySQL
54 lines
2.1 KiB
MySQL
|
-- Migration 001: Admin Backend Schema
|
||
|
|
-- Creates tables for admin user, settings, integrations, and MQTT subscriptions.
|
||
|
|
|
||
|
|
-- Single admin user
|
||
|
|
CREATE TABLE IF NOT EXISTS admin_user (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
username VARCHAR(100) NOT NULL DEFAULT 'admin',
|
||
|
|
password_hash VARCHAR(255) NOT NULL,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- General key/value settings (cache TTLs, preferences, etc.)
|
||
|
|
CREATE TABLE IF NOT EXISTS app_settings (
|
||
|
|
key VARCHAR(100) PRIMARY KEY,
|
||
|
|
value TEXT NOT NULL DEFAULT '',
|
||
|
|
value_type VARCHAR(20) NOT NULL DEFAULT 'string',
|
||
|
|
category VARCHAR(50) NOT NULL DEFAULT 'general',
|
||
|
|
label VARCHAR(200) NOT NULL DEFAULT '',
|
||
|
|
description TEXT NOT NULL DEFAULT '',
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Integration configurations (one row per integration type)
|
||
|
|
CREATE TABLE IF NOT EXISTS integrations (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
type VARCHAR(50) NOT NULL UNIQUE,
|
||
|
|
name VARCHAR(200) NOT NULL,
|
||
|
|
config JSONB NOT NULL DEFAULT '{}',
|
||
|
|
enabled BOOLEAN NOT NULL DEFAULT true,
|
||
|
|
display_order INTEGER NOT NULL DEFAULT 0,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- MQTT subscription management
|
||
|
|
CREATE TABLE IF NOT EXISTS mqtt_subscriptions (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
topic_pattern VARCHAR(500) NOT NULL,
|
||
|
|
display_name VARCHAR(200) NOT NULL DEFAULT '',
|
||
|
|
category VARCHAR(100) NOT NULL DEFAULT 'other',
|
||
|
|
unit VARCHAR(50) NOT NULL DEFAULT '',
|
||
|
|
widget_type VARCHAR(50) NOT NULL DEFAULT 'value',
|
||
|
|
enabled BOOLEAN NOT NULL DEFAULT true,
|
||
|
|
display_order INTEGER NOT NULL DEFAULT 0,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Record this migration
|
||
|
|
INSERT INTO schema_version (version, description)
|
||
|
|
VALUES (1, 'Admin backend: admin_user, app_settings, integrations, mqtt_subscriptions')
|
||
|
|
ON CONFLICT (version) DO NOTHING;
|