445 lines
14 KiB
PL/PgSQL
445 lines
14 KiB
PL/PgSQL
-- =====================================================
|
|
-- BotKonzept - Database Schema for Customer Management
|
|
-- =====================================================
|
|
-- This schema manages customers, instances, emails, and payments
|
|
-- for the BotKonzept SaaS platform
|
|
|
|
-- Enable UUID extension
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
-- =====================================================
|
|
-- Table: customers
|
|
-- =====================================================
|
|
-- Stores customer information and trial status
|
|
CREATE TABLE IF NOT EXISTS customers (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|
first_name VARCHAR(100) NOT NULL,
|
|
last_name VARCHAR(100) NOT NULL,
|
|
company VARCHAR(255),
|
|
phone VARCHAR(50),
|
|
|
|
-- Status tracking
|
|
status VARCHAR(50) DEFAULT 'trial' CHECK (status IN ('trial', 'active', 'cancelled', 'suspended', 'deleted')),
|
|
|
|
-- Timestamps
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
trial_end_date TIMESTAMPTZ,
|
|
subscription_start_date TIMESTAMPTZ,
|
|
subscription_end_date TIMESTAMPTZ,
|
|
|
|
-- Marketing tracking
|
|
utm_source VARCHAR(100),
|
|
utm_medium VARCHAR(100),
|
|
utm_campaign VARCHAR(100),
|
|
referral_code VARCHAR(50),
|
|
|
|
-- Metadata
|
|
metadata JSONB DEFAULT '{}'::jsonb,
|
|
|
|
-- Indexes
|
|
CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
|
|
);
|
|
|
|
-- Create indexes for customers
|
|
CREATE INDEX idx_customers_email ON customers(email);
|
|
CREATE INDEX idx_customers_status ON customers(status);
|
|
CREATE INDEX idx_customers_created_at ON customers(created_at);
|
|
CREATE INDEX idx_customers_trial_end_date ON customers(trial_end_date);
|
|
|
|
-- =====================================================
|
|
-- Table: instances
|
|
-- =====================================================
|
|
-- Stores LXC instance information for each customer
|
|
CREATE TABLE IF NOT EXISTS instances (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
|
|
|
|
-- Instance details
|
|
lxc_id BIGINT NOT NULL UNIQUE,
|
|
hostname VARCHAR(255) NOT NULL,
|
|
ip VARCHAR(50) NOT NULL,
|
|
fqdn VARCHAR(255) NOT NULL,
|
|
vlan INTEGER,
|
|
|
|
-- Status
|
|
status VARCHAR(50) DEFAULT 'active' CHECK (status IN ('creating', 'active', 'suspended', 'deleted', 'error')),
|
|
|
|
-- Credentials (encrypted JSON)
|
|
credentials JSONB NOT NULL,
|
|
|
|
-- Timestamps
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
deleted_at TIMESTAMPTZ,
|
|
trial_end_date TIMESTAMPTZ,
|
|
|
|
-- Resource usage
|
|
disk_usage_gb DECIMAL(10,2),
|
|
memory_usage_mb INTEGER,
|
|
cpu_usage_percent DECIMAL(5,2),
|
|
|
|
-- Metadata
|
|
metadata JSONB DEFAULT '{}'::jsonb
|
|
);
|
|
|
|
-- Create indexes for instances
|
|
CREATE INDEX idx_instances_customer_id ON instances(customer_id);
|
|
CREATE INDEX idx_instances_lxc_id ON instances(lxc_id);
|
|
CREATE INDEX idx_instances_status ON instances(status);
|
|
CREATE INDEX idx_instances_hostname ON instances(hostname);
|
|
|
|
-- =====================================================
|
|
-- Table: emails_sent
|
|
-- =====================================================
|
|
-- Tracks all emails sent to customers
|
|
CREATE TABLE IF NOT EXISTS emails_sent (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
|
|
|
|
-- Email details
|
|
email_type VARCHAR(50) NOT NULL CHECK (email_type IN (
|
|
'welcome',
|
|
'day3_upgrade',
|
|
'day5_reminder',
|
|
'day7_last_chance',
|
|
'day8_goodbye',
|
|
'payment_confirm',
|
|
'payment_failed',
|
|
'instance_created',
|
|
'instance_deleted',
|
|
'password_reset',
|
|
'newsletter'
|
|
)),
|
|
|
|
subject VARCHAR(255),
|
|
recipient_email VARCHAR(255) NOT NULL,
|
|
|
|
-- Status
|
|
status VARCHAR(50) DEFAULT 'sent' CHECK (status IN ('sent', 'delivered', 'opened', 'clicked', 'bounced', 'failed')),
|
|
|
|
-- Timestamps
|
|
sent_at TIMESTAMPTZ DEFAULT NOW(),
|
|
delivered_at TIMESTAMPTZ,
|
|
opened_at TIMESTAMPTZ,
|
|
clicked_at TIMESTAMPTZ,
|
|
|
|
-- Metadata
|
|
metadata JSONB DEFAULT '{}'::jsonb
|
|
);
|
|
|
|
-- Create indexes for emails_sent
|
|
CREATE INDEX idx_emails_customer_id ON emails_sent(customer_id);
|
|
CREATE INDEX idx_emails_type ON emails_sent(email_type);
|
|
CREATE INDEX idx_emails_sent_at ON emails_sent(sent_at);
|
|
CREATE INDEX idx_emails_status ON emails_sent(status);
|
|
|
|
-- =====================================================
|
|
-- Table: subscriptions
|
|
-- =====================================================
|
|
-- Stores subscription and payment information
|
|
CREATE TABLE IF NOT EXISTS subscriptions (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
|
|
|
|
-- Plan details
|
|
plan_name VARCHAR(50) NOT NULL CHECK (plan_name IN ('trial', 'starter', 'business', 'enterprise')),
|
|
plan_price DECIMAL(10,2) NOT NULL,
|
|
billing_cycle VARCHAR(20) DEFAULT 'monthly' CHECK (billing_cycle IN ('monthly', 'yearly')),
|
|
|
|
-- Discount
|
|
discount_percent DECIMAL(5,2) DEFAULT 0,
|
|
discount_code VARCHAR(50),
|
|
discount_end_date TIMESTAMPTZ,
|
|
|
|
-- Status
|
|
status VARCHAR(50) DEFAULT 'active' CHECK (status IN ('active', 'cancelled', 'past_due', 'suspended')),
|
|
|
|
-- Payment provider
|
|
payment_provider VARCHAR(50) CHECK (payment_provider IN ('stripe', 'paypal', 'manual')),
|
|
payment_provider_id VARCHAR(255),
|
|
|
|
-- Timestamps
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
current_period_start TIMESTAMPTZ,
|
|
current_period_end TIMESTAMPTZ,
|
|
cancelled_at TIMESTAMPTZ,
|
|
|
|
-- Metadata
|
|
metadata JSONB DEFAULT '{}'::jsonb
|
|
);
|
|
|
|
-- Create indexes for subscriptions
|
|
CREATE INDEX idx_subscriptions_customer_id ON subscriptions(customer_id);
|
|
CREATE INDEX idx_subscriptions_status ON subscriptions(status);
|
|
CREATE INDEX idx_subscriptions_plan_name ON subscriptions(plan_name);
|
|
|
|
-- =====================================================
|
|
-- Table: payments
|
|
-- =====================================================
|
|
-- Stores payment transaction history
|
|
CREATE TABLE IF NOT EXISTS payments (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
|
|
subscription_id UUID REFERENCES subscriptions(id) ON DELETE SET NULL,
|
|
|
|
-- Payment details
|
|
amount DECIMAL(10,2) NOT NULL,
|
|
currency VARCHAR(3) DEFAULT 'EUR',
|
|
|
|
-- Status
|
|
status VARCHAR(50) DEFAULT 'pending' CHECK (status IN ('pending', 'succeeded', 'failed', 'refunded', 'cancelled')),
|
|
|
|
-- Payment provider
|
|
payment_provider VARCHAR(50) CHECK (payment_provider IN ('stripe', 'paypal', 'manual')),
|
|
payment_provider_id VARCHAR(255),
|
|
payment_method VARCHAR(50),
|
|
|
|
-- Timestamps
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
paid_at TIMESTAMPTZ,
|
|
refunded_at TIMESTAMPTZ,
|
|
|
|
-- Invoice
|
|
invoice_number VARCHAR(50),
|
|
invoice_url TEXT,
|
|
|
|
-- Metadata
|
|
metadata JSONB DEFAULT '{}'::jsonb
|
|
);
|
|
|
|
-- Create indexes for payments
|
|
CREATE INDEX idx_payments_customer_id ON payments(customer_id);
|
|
CREATE INDEX idx_payments_subscription_id ON payments(subscription_id);
|
|
CREATE INDEX idx_payments_status ON payments(status);
|
|
CREATE INDEX idx_payments_created_at ON payments(created_at);
|
|
|
|
-- =====================================================
|
|
-- Table: usage_stats
|
|
-- =====================================================
|
|
-- Tracks usage statistics for each instance
|
|
CREATE TABLE IF NOT EXISTS usage_stats (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
instance_id UUID NOT NULL REFERENCES instances(id) ON DELETE CASCADE,
|
|
|
|
-- Usage metrics
|
|
date DATE NOT NULL,
|
|
messages_count INTEGER DEFAULT 0,
|
|
documents_count INTEGER DEFAULT 0,
|
|
api_calls_count INTEGER DEFAULT 0,
|
|
storage_used_mb DECIMAL(10,2) DEFAULT 0,
|
|
|
|
-- Timestamps
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
-- Unique constraint: one record per instance per day
|
|
UNIQUE(instance_id, date)
|
|
);
|
|
|
|
-- Create indexes for usage_stats
|
|
CREATE INDEX idx_usage_instance_id ON usage_stats(instance_id);
|
|
CREATE INDEX idx_usage_date ON usage_stats(date);
|
|
|
|
-- =====================================================
|
|
-- Table: audit_log
|
|
-- =====================================================
|
|
-- Audit trail for important actions
|
|
CREATE TABLE IF NOT EXISTS audit_log (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
customer_id UUID REFERENCES customers(id) ON DELETE SET NULL,
|
|
instance_id UUID REFERENCES instances(id) ON DELETE SET NULL,
|
|
|
|
-- Action details
|
|
action VARCHAR(100) NOT NULL,
|
|
entity_type VARCHAR(50),
|
|
entity_id UUID,
|
|
|
|
-- User/system that performed the action
|
|
performed_by VARCHAR(100),
|
|
ip_address INET,
|
|
user_agent TEXT,
|
|
|
|
-- Changes
|
|
old_values JSONB,
|
|
new_values JSONB,
|
|
|
|
-- Timestamp
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
-- Metadata
|
|
metadata JSONB DEFAULT '{}'::jsonb
|
|
);
|
|
|
|
-- Create indexes for audit_log
|
|
CREATE INDEX idx_audit_customer_id ON audit_log(customer_id);
|
|
CREATE INDEX idx_audit_instance_id ON audit_log(instance_id);
|
|
CREATE INDEX idx_audit_action ON audit_log(action);
|
|
CREATE INDEX idx_audit_created_at ON audit_log(created_at);
|
|
|
|
-- =====================================================
|
|
-- Functions & Triggers
|
|
-- =====================================================
|
|
|
|
-- Function to update updated_at timestamp
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Triggers for updated_at
|
|
CREATE TRIGGER update_customers_updated_at BEFORE UPDATE ON customers
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_instances_updated_at BEFORE UPDATE ON instances
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_subscriptions_updated_at BEFORE UPDATE ON subscriptions
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- Function to calculate trial end date
|
|
CREATE OR REPLACE FUNCTION set_trial_end_date()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
IF NEW.trial_end_date IS NULL THEN
|
|
NEW.trial_end_date = NEW.created_at + INTERVAL '7 days';
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Trigger for trial end date
|
|
CREATE TRIGGER set_customer_trial_end_date BEFORE INSERT ON customers
|
|
FOR EACH ROW EXECUTE FUNCTION set_trial_end_date();
|
|
|
|
-- =====================================================
|
|
-- Views
|
|
-- =====================================================
|
|
|
|
-- View: Active trials expiring soon
|
|
CREATE OR REPLACE VIEW trials_expiring_soon AS
|
|
SELECT
|
|
c.id,
|
|
c.email,
|
|
c.first_name,
|
|
c.last_name,
|
|
c.created_at,
|
|
c.trial_end_date,
|
|
EXTRACT(DAY FROM (c.trial_end_date - NOW())) as days_remaining,
|
|
i.lxc_id,
|
|
i.hostname,
|
|
i.fqdn
|
|
FROM customers c
|
|
JOIN instances i ON c.id = i.customer_id
|
|
WHERE c.status = 'trial'
|
|
AND i.status = 'active'
|
|
AND c.trial_end_date > NOW()
|
|
AND c.trial_end_date <= NOW() + INTERVAL '3 days';
|
|
|
|
-- View: Customer overview with instance info
|
|
CREATE OR REPLACE VIEW customer_overview AS
|
|
SELECT
|
|
c.id,
|
|
c.email,
|
|
c.first_name,
|
|
c.last_name,
|
|
c.company,
|
|
c.status,
|
|
c.created_at,
|
|
c.trial_end_date,
|
|
i.lxc_id,
|
|
i.hostname,
|
|
i.fqdn,
|
|
i.ip,
|
|
i.status as instance_status,
|
|
s.plan_name,
|
|
s.plan_price,
|
|
s.status as subscription_status
|
|
FROM customers c
|
|
LEFT JOIN instances i ON c.id = i.customer_id AND i.status = 'active'
|
|
LEFT JOIN subscriptions s ON c.id = s.customer_id AND s.status = 'active';
|
|
|
|
-- View: Revenue metrics
|
|
CREATE OR REPLACE VIEW revenue_metrics AS
|
|
SELECT
|
|
DATE_TRUNC('month', paid_at) as month,
|
|
COUNT(*) as payment_count,
|
|
SUM(amount) as total_revenue,
|
|
AVG(amount) as average_payment,
|
|
COUNT(DISTINCT customer_id) as unique_customers
|
|
FROM payments
|
|
WHERE status = 'succeeded'
|
|
AND paid_at IS NOT NULL
|
|
GROUP BY DATE_TRUNC('month', paid_at)
|
|
ORDER BY month DESC;
|
|
|
|
-- =====================================================
|
|
-- Row Level Security (RLS) Policies
|
|
-- =====================================================
|
|
|
|
-- Enable RLS on tables
|
|
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE instances ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE subscriptions ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE payments ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Policy: Customers can only see their own data
|
|
CREATE POLICY customers_select_own ON customers
|
|
FOR SELECT
|
|
USING (auth.uid()::text = id::text);
|
|
|
|
CREATE POLICY instances_select_own ON instances
|
|
FOR SELECT
|
|
USING (customer_id::text = auth.uid()::text);
|
|
|
|
CREATE POLICY subscriptions_select_own ON subscriptions
|
|
FOR SELECT
|
|
USING (customer_id::text = auth.uid()::text);
|
|
|
|
CREATE POLICY payments_select_own ON payments
|
|
FOR SELECT
|
|
USING (customer_id::text = auth.uid()::text);
|
|
|
|
-- =====================================================
|
|
-- Sample Data (for testing)
|
|
-- =====================================================
|
|
|
|
-- Insert sample customer (commented out for production)
|
|
-- INSERT INTO customers (email, first_name, last_name, company, status)
|
|
-- VALUES ('test@example.com', 'Max', 'Mustermann', 'Test GmbH', 'trial');
|
|
|
|
-- =====================================================
|
|
-- Grants
|
|
-- =====================================================
|
|
|
|
-- Grant permissions to authenticated users
|
|
GRANT SELECT, INSERT, UPDATE ON customers TO authenticated;
|
|
GRANT SELECT ON instances TO authenticated;
|
|
GRANT SELECT ON subscriptions TO authenticated;
|
|
GRANT SELECT ON payments TO authenticated;
|
|
GRANT SELECT ON usage_stats TO authenticated;
|
|
|
|
-- Grant all permissions to service role (for n8n workflows)
|
|
GRANT ALL ON ALL TABLES IN SCHEMA public TO service_role;
|
|
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO service_role;
|
|
|
|
-- =====================================================
|
|
-- Comments
|
|
-- =====================================================
|
|
|
|
COMMENT ON TABLE customers IS 'Stores customer information and trial status';
|
|
COMMENT ON TABLE instances IS 'Stores LXC instance information for each customer';
|
|
COMMENT ON TABLE emails_sent IS 'Tracks all emails sent to customers';
|
|
COMMENT ON TABLE subscriptions IS 'Stores subscription and payment information';
|
|
COMMENT ON TABLE payments IS 'Stores payment transaction history';
|
|
COMMENT ON TABLE usage_stats IS 'Tracks usage statistics for each instance';
|
|
COMMENT ON TABLE audit_log IS 'Audit trail for important actions';
|
|
|
|
-- =====================================================
|
|
-- End of Schema
|
|
-- =====================================================
|