31 lines
1.2 KiB
MySQL
31 lines
1.2 KiB
MySQL
|
BEGIN;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS user_profiles (
|
||
|
|
user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
full_name VARCHAR(150) NOT NULL,
|
||
|
|
display_name VARCHAR(100),
|
||
|
|
phone VARCHAR(50),
|
||
|
|
avatar_url TEXT,
|
||
|
|
job_title VARCHAR(120),
|
||
|
|
employee_no VARCHAR(60),
|
||
|
|
bio TEXT,
|
||
|
|
timezone VARCHAR(64) DEFAULT 'Asia/Jakarta',
|
||
|
|
locale VARCHAR(16) DEFAULT 'id-ID',
|
||
|
|
preferences JSONB NOT NULL DEFAULT '{}'::jsonb,
|
||
|
|
notification_prefs JSONB NOT NULL DEFAULT '{}'::jsonb,
|
||
|
|
last_seen_at TIMESTAMP WITHOUT TIME ZONE,
|
||
|
|
created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_user_profiles_phone ON user_profiles(phone);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_user_profiles_employee_no ON user_profiles(employee_no);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_user_profiles_prefs_gin ON user_profiles USING GIN (preferences);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_user_profiles_notif_gin ON user_profiles USING GIN (notification_prefs);
|
||
|
|
|
||
|
|
CREATE TRIGGER trg_user_profiles_updated_at
|
||
|
|
BEFORE UPDATE ON user_profiles
|
||
|
|
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
||
|
|
|
||
|
|
COMMIT;
|