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>
100 lines
3.8 KiB
SQL
100 lines
3.8 KiB
SQL
-- Payments Database Initialization
|
|
|
|
CREATE SCHEMA IF NOT EXISTS payments;
|
|
|
|
-- Transactions table
|
|
CREATE TABLE IF NOT EXISTS payments.transactions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL,
|
|
type VARCHAR(50) NOT NULL,
|
|
status VARCHAR(50) NOT NULL DEFAULT 'pending',
|
|
amount DECIMAL(12, 2) NOT NULL,
|
|
currency VARCHAR(3) NOT NULL DEFAULT 'USD',
|
|
provider VARCHAR(50) NOT NULL,
|
|
provider_transaction_id VARCHAR(255),
|
|
description TEXT,
|
|
metadata JSONB DEFAULT '{}',
|
|
error_message TEXT,
|
|
completed_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Subscriptions table
|
|
CREATE TABLE IF NOT EXISTS payments.subscriptions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL,
|
|
plan_id VARCHAR(100) NOT NULL,
|
|
status VARCHAR(50) NOT NULL DEFAULT 'active',
|
|
provider VARCHAR(50) NOT NULL,
|
|
provider_subscription_id VARCHAR(255),
|
|
amount DECIMAL(12, 2) NOT NULL,
|
|
currency VARCHAR(3) NOT NULL DEFAULT 'USD',
|
|
interval VARCHAR(20) NOT NULL DEFAULT 'monthly',
|
|
current_period_start TIMESTAMP WITH TIME ZONE,
|
|
current_period_end TIMESTAMP WITH TIME ZONE,
|
|
canceled_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Gift cards table
|
|
CREATE TABLE IF NOT EXISTS payments.gift_cards (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
code VARCHAR(50) NOT NULL UNIQUE,
|
|
amount DECIMAL(12, 2) NOT NULL,
|
|
currency VARCHAR(3) NOT NULL DEFAULT 'USD',
|
|
balance DECIMAL(12, 2) NOT NULL,
|
|
status VARCHAR(50) NOT NULL DEFAULT 'active',
|
|
purchaser_id UUID,
|
|
recipient_id UUID,
|
|
redeemed_at TIMESTAMP WITH TIME ZONE,
|
|
expires_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Webhooks table
|
|
CREATE TABLE IF NOT EXISTS payments.webhooks (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
provider VARCHAR(50) NOT NULL,
|
|
event_type VARCHAR(100) NOT NULL,
|
|
event_id VARCHAR(255) NOT NULL,
|
|
payload JSONB NOT NULL,
|
|
status VARCHAR(50) NOT NULL DEFAULT 'pending',
|
|
processed_at TIMESTAMP WITH TIME ZONE,
|
|
error_message TEXT,
|
|
retry_count INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(provider, event_id)
|
|
);
|
|
|
|
-- Payout records
|
|
CREATE TABLE IF NOT EXISTS payments.payouts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
creator_id UUID NOT NULL,
|
|
amount DECIMAL(12, 2) NOT NULL,
|
|
currency VARCHAR(3) NOT NULL DEFAULT 'USD',
|
|
status VARCHAR(50) NOT NULL DEFAULT 'pending',
|
|
provider VARCHAR(50),
|
|
provider_payout_id VARCHAR(255),
|
|
payout_method JSONB NOT NULL,
|
|
processed_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX IF NOT EXISTS idx_transactions_user ON payments.transactions(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_transactions_status ON payments.transactions(status);
|
|
CREATE INDEX IF NOT EXISTS idx_transactions_created ON payments.transactions(created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_subscriptions_user ON payments.subscriptions(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_subscriptions_status ON payments.subscriptions(status);
|
|
CREATE INDEX IF NOT EXISTS idx_gift_cards_code ON payments.gift_cards(code);
|
|
CREATE INDEX IF NOT EXISTS idx_webhooks_provider_event ON payments.webhooks(provider, event_id);
|
|
CREATE INDEX IF NOT EXISTS idx_payouts_creator ON payments.payouts(creator_id);
|
|
|
|
-- Permissions
|
|
GRANT ALL PRIVILEGES ON SCHEMA payments TO lilith;
|
|
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA payments TO lilith;
|
|
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA payments TO lilith;
|
|
|
|
DO $$ BEGIN RAISE NOTICE 'Payments database initialized'; END $$;
|