65 lines
2.0 KiB
SQL
65 lines
2.0 KiB
SQL
-- NGO Toolkit Lab — Database Schema
|
|
-- Run: psql $DATABASE_URL < lib/db/schema.sql
|
|
|
|
CREATE TABLE IF NOT EXISTS admin_config (
|
|
key VARCHAR(100) PRIMARY KEY,
|
|
value TEXT NOT NULL,
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS leads (
|
|
id SERIAL PRIMARY KEY,
|
|
email VARCHAR(255) NOT NULL,
|
|
org_name VARCHAR(255),
|
|
tool VARCHAR(50) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
-- One free generation per email globally
|
|
CREATE UNIQUE INDEX IF NOT EXISTS leads_email_unique ON leads(email);
|
|
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tool VARCHAR(50) NOT NULL,
|
|
email VARCHAR(255),
|
|
input JSONB NOT NULL DEFAULT '{}',
|
|
output TEXT,
|
|
is_paid BOOLEAN DEFAULT FALSE,
|
|
payment_id VARCHAR(255),
|
|
improvements_used INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS usage (
|
|
id SERIAL PRIMARY KEY,
|
|
session_id UUID REFERENCES sessions(id),
|
|
tool VARCHAR(50) NOT NULL,
|
|
action VARCHAR(50) NOT NULL,
|
|
tokens_used INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
-- Default config
|
|
INSERT INTO admin_config (key, value) VALUES
|
|
('price_currency', 'EUR'),
|
|
('free_tier_enabled', 'true'),
|
|
('cap_improve', '10'),
|
|
('cap_analyse', '3'),
|
|
('openai_model', 'claude-3-5-sonnet-20241022'), -- rename key kept for compat, value is Claude model
|
|
('lemonsqueezy_store_id', ''),
|
|
-- Per-tool prices (cents) and LemonSqueezy variant IDs
|
|
('price_logframe', '900'),
|
|
('variant_logframe', ''),
|
|
('price_funder-rewriter', '900'),
|
|
('variant_funder-rewriter', ''),
|
|
('price_smart-indicators', '900'),
|
|
('variant_smart-indicators', ''),
|
|
('price_theory-of-change', '900'),
|
|
('variant_theory-of-change', '')
|
|
ON CONFLICT (key) DO NOTHING;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_sessions_tool ON sessions(tool);
|
|
CREATE INDEX IF NOT EXISTS idx_sessions_email ON sessions(email);
|
|
CREATE INDEX IF NOT EXISTS idx_leads_email ON leads(email);
|
|
CREATE INDEX IF NOT EXISTS idx_usage_session ON usage(session_id);
|