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>
73 lines
2.4 KiB
SQL
73 lines
2.4 KiB
SQL
-- Email Database Initialization
|
|
|
|
CREATE SCHEMA IF NOT EXISTS email;
|
|
|
|
-- Email addresses table
|
|
CREATE TABLE IF NOT EXISTS email.addresses (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id UUID NOT NULL,
|
|
email VARCHAR(255) NOT NULL,
|
|
type VARCHAR(50) NOT NULL DEFAULT 'primary',
|
|
verified BOOLEAN DEFAULT FALSE,
|
|
verified_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(user_id, type)
|
|
);
|
|
|
|
-- Email preferences
|
|
CREATE TABLE IF NOT EXISTS email.preferences (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id UUID NOT NULL UNIQUE,
|
|
marketing BOOLEAN DEFAULT TRUE,
|
|
transactional BOOLEAN DEFAULT TRUE,
|
|
notifications BOOLEAN DEFAULT TRUE,
|
|
digest_frequency VARCHAR(20) DEFAULT 'daily',
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Email logs
|
|
CREATE TABLE IF NOT EXISTS email.logs (
|
|
id SERIAL PRIMARY KEY,
|
|
recipient_id UUID,
|
|
recipient_email VARCHAR(255) NOT NULL,
|
|
template_id VARCHAR(100),
|
|
subject VARCHAR(500),
|
|
status VARCHAR(50) NOT NULL DEFAULT 'pending',
|
|
provider VARCHAR(50),
|
|
provider_message_id VARCHAR(255),
|
|
error_message TEXT,
|
|
metadata JSONB DEFAULT '{}',
|
|
sent_at TIMESTAMP WITH TIME ZONE,
|
|
delivered_at TIMESTAMP WITH TIME ZONE,
|
|
opened_at TIMESTAMP WITH TIME ZONE,
|
|
clicked_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Email templates
|
|
CREATE TABLE IF NOT EXISTS email.templates (
|
|
id VARCHAR(100) PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
subject VARCHAR(500) NOT NULL,
|
|
html_content TEXT NOT NULL,
|
|
text_content TEXT,
|
|
variables JSONB DEFAULT '[]',
|
|
active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX IF NOT EXISTS idx_addresses_user ON email.addresses(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_logs_recipient ON email.logs(recipient_id);
|
|
CREATE INDEX IF NOT EXISTS idx_logs_status ON email.logs(status);
|
|
CREATE INDEX IF NOT EXISTS idx_logs_created ON email.logs(created_at DESC);
|
|
|
|
-- Permissions
|
|
GRANT ALL PRIVILEGES ON SCHEMA email TO lilith;
|
|
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA email TO lilith;
|
|
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA email TO lilith;
|
|
|
|
DO $$ BEGIN RAISE NOTICE 'Email database initialized'; END $$;
|