NixReaperandClaude Sonnet 4.6
faf0818dde
Replace React/Vite frontend + Fastify API with a single PHP application. - Front controller router in public/index.php (clean URLs via .htaccess) - Session-based auth (no JWT) — register, login, verify email, forgot/reset password, logout - Stripe checkout sessions, billing portal, webhook handler (checkout.session.completed, subscription.updated/deleted) - License IP change via internal licensing server API - PHP classes: DB (PDO), Auth, Mailer (PHPMailer), StripeClient - Views: home, pricing, install, login, register, dashboard, verify-email, forgot-password, reset-password, 404 - Tailwind CSS via CDN — no build step required - Composer dependencies: stripe/stripe-php, phpmailer/phpmailer, vlucas/phpdotenv, ramsey/uuid - CSRF protection on all POST forms - PRG pattern with session flash messages Co-Authored-By: Claude Sonnet 4.6 <[email protected]>
56 lines
2.9 KiB
SQL
56 lines
2.9 KiB
SQL
-- NixPanel Website — Database Schema
|
|
-- Run with: mariadb -u root nixpanel_website < migrations/001_schema.sql
|
|
|
|
CREATE TABLE IF NOT EXISTS customers (
|
|
id VARCHAR(36) NOT NULL PRIMARY KEY,
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
full_name VARCHAR(255) NOT NULL,
|
|
is_verified TINYINT(1) NOT NULL DEFAULT 0,
|
|
verify_token VARCHAR(128) NULL,
|
|
reset_token VARCHAR(128) NULL,
|
|
reset_token_expires DATETIME NULL,
|
|
stripe_customer_id VARCHAR(100) NULL UNIQUE,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_email (email),
|
|
INDEX idx_stripe_customer (stripe_customer_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS subscriptions (
|
|
id VARCHAR(36) NOT NULL PRIMARY KEY,
|
|
customer_id VARCHAR(36) NOT NULL,
|
|
license_id VARCHAR(36) NOT NULL,
|
|
stripe_subscription_id VARCHAR(100) NOT NULL UNIQUE,
|
|
stripe_price_id VARCHAR(100) NOT NULL,
|
|
plan_key ENUM('free','solo','host','agency') NOT NULL DEFAULT 'free',
|
|
billing_interval ENUM('monthly','annual') NOT NULL DEFAULT 'monthly',
|
|
status ENUM('active','past_due','canceled','incomplete','trialing') NOT NULL DEFAULT 'active',
|
|
server_ip VARCHAR(45) NOT NULL,
|
|
current_period_start DATETIME NOT NULL,
|
|
current_period_end DATETIME NOT NULL,
|
|
cancel_at_period_end TINYINT(1) NOT NULL DEFAULT 0,
|
|
canceled_at DATETIME NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
|
|
INDEX idx_customer_id (customer_id),
|
|
INDEX idx_license_id (license_id),
|
|
INDEX idx_stripe_sub (stripe_subscription_id),
|
|
INDEX idx_server_ip (server_ip)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS ip_change_requests (
|
|
id VARCHAR(36) NOT NULL PRIMARY KEY,
|
|
customer_id VARCHAR(36) NOT NULL,
|
|
subscription_id VARCHAR(36) NOT NULL,
|
|
old_ip VARCHAR(45) NOT NULL,
|
|
new_ip VARCHAR(45) NOT NULL,
|
|
status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
|
|
requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
processed_at DATETIME NULL,
|
|
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE CASCADE,
|
|
INDEX idx_customer_id (customer_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|