-- migrations/001_initial.sql -- Full Content Machine SaaS schema -- ─── PLANS ──────────────────────────────────────────────────────────────── CREATE TABLE plans ( id TEXT PRIMARY KEY, name TEXT NOT NULL, name_ro TEXT NOT NULL, price_usd INTEGER NOT NULL, -- cents stripe_price_id TEXT, scripts_per_day INTEGER NOT NULL DEFAULT 3, posts_per_day INTEGER NOT NULL DEFAULT 3, byok BOOLEAN NOT NULL DEFAULT false, -- bring your own key white_label BOOLEAN NOT NULL DEFAULT false, max_sub_clients INTEGER NOT NULL DEFAULT 0, features JSONB NOT NULL DEFAULT '[]', features_ro JSONB NOT NULL DEFAULT '[]', active BOOLEAN NOT NULL DEFAULT true, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); INSERT INTO plans VALUES ('starter', 'Starter', 'Starter', 2900, NULL, 3, 3, false, false, 0, '["3 video scripts/day","3 X posts/day","Blog + Newsletter","All 10 agents","14-day trial"]', '["3 scripturi video/zi","3 postări X/zi","Blog + Newsletter","Toți 10 agenții","Trial 14 zile"]', true, NOW()), ('pro', 'Pro', 'Pro', 7900, NULL, 5, 5, true, false, 0, '["5 video scripts/day","5 X posts/day","Blog + Newsletter","All 10 agents","Your own API key","Unlimited runs","14-day trial"]', '["5 scripturi video/zi","5 postări X/zi","Blog + Newsletter","Toți 10 agenții","Cheia ta API","Rulări nelimitate","Trial 14 zile"]', true, NOW()), ('agency', 'Agency', 'Agency', 19900, NULL, 10, 10, true, true, 10, '["10 video scripts/day","10 X posts/day","Blog + Newsletter","All 10 agents","Your own API key","White label","Up to 10 sub-clients","14-day trial"]', '["10 scripturi video/zi","10 postări X/zi","Blog + Newsletter","Toți 10 agenții","Cheia ta API","White label","Până la 10 sub-clienți","Trial 14 zile"]', true, NOW()); -- ─── USERS ──────────────────────────────────────────────────────────────── CREATE TABLE users ( id UUID PRIMARY KEY, email TEXT UNIQUE NOT NULL, password_hash TEXT NOT NULL, full_name TEXT, role TEXT NOT NULL DEFAULT 'client' CHECK (role IN ('client','admin','superadmin')), plan_id TEXT REFERENCES plans(id), parent_user_id UUID REFERENCES users(id), -- for Agency sub-clients status TEXT NOT NULL DEFAULT 'trial' CHECK (status IN ('trial','active','past_due','canceled','banned')), trial_ends_at TIMESTAMPTZ, locale TEXT NOT NULL DEFAULT 'en' CHECK (locale IN ('en','ro')), email_verified BOOLEAN NOT NULL DEFAULT false, email_verify_token TEXT, reset_token TEXT, reset_token_expires TIMESTAMPTZ, stripe_customer_id TEXT UNIQUE, stripe_subscription_id TEXT UNIQUE, stripe_subscription_status TEXT, current_period_end TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ─── USER PROFILES ──────────────────────────────────────────────────────── CREATE TABLE user_profiles ( user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE, niche TEXT, brand_name TEXT, target_audience TEXT, tone TEXT, writing_style TEXT, personality TEXT, avoid_words TEXT[] DEFAULT '{}', cta TEXT, example_phrases TEXT[] DEFAULT '{}', scripts_per_day INTEGER, posts_per_day INTEGER, newsletter_freq TEXT DEFAULT 'daily', schedule_intel TEXT DEFAULT '0 5 * * *', schedule_run TEXT DEFAULT '0 6 * * *', output_timezone TEXT DEFAULT 'UTC', updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ─── API KEYS (encrypted) ───────────────────────────────────────────────── CREATE TABLE user_api_keys ( id UUID PRIMARY KEY, user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, provider TEXT NOT NULL CHECK (provider IN ('claude','openai','gemini')), encrypted_key TEXT NOT NULL, model TEXT, is_valid BOOLEAN DEFAULT NULL, last_tested_at TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), UNIQUE(user_id, provider) ); -- ─── PIPELINE JOBS ──────────────────────────────────────────────────────── CREATE TABLE pipeline_runs ( id UUID PRIMARY KEY, user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, status TEXT NOT NULL DEFAULT 'queued' CHECK (status IN ('queued','running','completed','failed')), triggered_by TEXT NOT NULL DEFAULT 'schedule' CHECK (triggered_by IN ('schedule','manual')), provider_used TEXT, agents_run TEXT[] DEFAULT '{}', scripts_count INTEGER DEFAULT 0, error_message TEXT, started_at TIMESTAMPTZ, completed_at TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ─── GENERATED CONTENT ──────────────────────────────────────────────────── CREATE TABLE generated_content ( id UUID PRIMARY KEY, user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, run_id UUID REFERENCES pipeline_runs(id) ON DELETE SET NULL, content_type TEXT NOT NULL CHECK (content_type IN ('script','post','blog','newsletter','thumbnail','clip','intel','outreach','performance')), title TEXT, body TEXT NOT NULL, metadata JSONB DEFAULT '{}', created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ─── USAGE TRACKING ─────────────────────────────────────────────────────── CREATE TABLE usage_logs ( id UUID PRIMARY KEY, user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, action TEXT NOT NULL, tokens_used INTEGER DEFAULT 0, provider TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ─── PERFORMANCE DATA ───────────────────────────────────────────────────── CREATE TABLE performance_data ( id UUID PRIMARY KEY, user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, content_type TEXT NOT NULL, title TEXT, metric_name TEXT NOT NULL, metric_value NUMERIC, recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ─── AUDIT LOG ──────────────────────────────────────────────────────────── CREATE TABLE audit_logs ( id UUID PRIMARY KEY, actor_id UUID REFERENCES users(id), target_user_id UUID REFERENCES users(id), action TEXT NOT NULL, details JSONB DEFAULT '{}', ip_address TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ─── INDEXES ───────────────────────────────────────────────────────────── CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_users_stripe_customer ON users(stripe_customer_id); CREATE INDEX idx_pipeline_runs_user ON pipeline_runs(user_id, created_at DESC); CREATE INDEX idx_generated_content_user ON generated_content(user_id, created_at DESC); CREATE INDEX idx_generated_content_type ON generated_content(user_id, content_type, created_at DESC); CREATE INDEX idx_usage_logs_user ON usage_logs(user_id, created_at DESC); -- ─── UPDATED_AT TRIGGER ────────────────────────────────────────────────── CREATE OR REPLACE FUNCTION update_updated_at() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER users_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE update_updated_at(); CREATE TRIGGER profiles_updated_at BEFORE UPDATE ON user_profiles FOR EACH ROW EXECUTE PROCEDURE update_updated_at();