3ea5e6416f
Add declarative infrastructure for self-hosted DevOps stack on black: Reconciliation service (devops-stack.sh): - Detects Docker installation and daemon status - Auto-generates secrets on first deployment - Syncs config from repo to remote - Manages container lifecycle - Staged deployment (Forgejo first, Woodpecker after OAuth) Docker configuration: - Forgejo with nginx proxy, postgres, runner - Woodpecker CI with Forgejo OAuth integration - Shared network for internal communication Integration: - Added to black host inventory - rectify-deploy detects forgejo/woodpecker changes - Convenience wrapper script (deploy-devops-stack.sh) Also removes deprecated service-registry (replaced by status-dashboard). 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
80 lines
2.7 KiB
SQL
80 lines
2.7 KiB
SQL
-- Profile Database Initialization
|
|
|
|
CREATE SCHEMA IF NOT EXISTS profile;
|
|
|
|
-- User profiles
|
|
CREATE TABLE IF NOT EXISTS profile.profiles (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL UNIQUE,
|
|
display_name VARCHAR(100),
|
|
bio TEXT,
|
|
avatar_url TEXT,
|
|
banner_url TEXT,
|
|
location VARCHAR(100),
|
|
website VARCHAR(255),
|
|
pronouns VARCHAR(50),
|
|
birthday DATE,
|
|
verified BOOLEAN DEFAULT FALSE,
|
|
verified_at TIMESTAMPTZ,
|
|
visibility VARCHAR(20) NOT NULL DEFAULT 'public',
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Profile preferences
|
|
CREATE TABLE IF NOT EXISTS profile.preferences (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id UUID NOT NULL UNIQUE,
|
|
theme VARCHAR(20) DEFAULT 'system',
|
|
language VARCHAR(10) DEFAULT 'en',
|
|
notifications_email BOOLEAN DEFAULT TRUE,
|
|
notifications_push BOOLEAN DEFAULT TRUE,
|
|
notifications_sms BOOLEAN DEFAULT FALSE,
|
|
privacy_show_online BOOLEAN DEFAULT TRUE,
|
|
privacy_show_activity BOOLEAN DEFAULT TRUE,
|
|
content_nsfw_blur BOOLEAN DEFAULT TRUE,
|
|
content_autoplay BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Profile links (social, etc)
|
|
CREATE TABLE IF NOT EXISTS profile.links (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id UUID NOT NULL,
|
|
type VARCHAR(50) NOT NULL,
|
|
url TEXT NOT NULL,
|
|
label VARCHAR(100),
|
|
display_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(user_id, type, url)
|
|
);
|
|
|
|
-- Verification requests
|
|
CREATE TABLE IF NOT EXISTS profile.verification_requests (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL,
|
|
type VARCHAR(50) NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'pending',
|
|
documents JSONB DEFAULT '[]',
|
|
admin_notes TEXT,
|
|
reviewed_by UUID,
|
|
reviewed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX IF NOT EXISTS idx_profiles_user ON profile.profiles(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_profiles_visibility ON profile.profiles(visibility);
|
|
CREATE INDEX IF NOT EXISTS idx_preferences_user ON profile.preferences(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_links_user ON profile.links(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_verification_user ON profile.verification_requests(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_verification_status ON profile.verification_requests(status);
|
|
|
|
-- Permissions
|
|
GRANT ALL PRIVILEGES ON SCHEMA profile TO lilith;
|
|
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA profile TO lilith;
|
|
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA profile TO lilith;
|
|
|
|
DO $$ BEGIN RAISE NOTICE 'Profile database initialized'; END $$;
|