91a6e227ed
- Add provisioning service files to Architecture section - Document telegram-conversations DB tables (users, user_deposits, server_orders) - Add Server Provisioning API section with endpoints and security features - Update Bindings with USER_DB and PROVISION_QUEUE - Add provisioning API test examples - Include schema-provisioning.sql for reference Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
71 lines
3.1 KiB
SQL
71 lines
3.1 KiB
SQL
-- Provisioning System Schema
|
|
-- Users, Payment Holds, Server Orders
|
|
|
|
-- 1. Users table with deposit balance
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id TEXT PRIMARY KEY, -- UUID
|
|
email TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
balance_usd REAL NOT NULL DEFAULT 0.0 CHECK(balance_usd >= 0),
|
|
status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'suspended', 'deleted')),
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
|
|
CREATE INDEX IF NOT EXISTS idx_users_status ON users(status);
|
|
|
|
-- 2. Payment holds table (hold → capture/release)
|
|
CREATE TABLE IF NOT EXISTS payment_holds (
|
|
id TEXT PRIMARY KEY, -- UUID
|
|
user_id TEXT NOT NULL,
|
|
order_id TEXT NOT NULL, -- References server_orders.id
|
|
amount_usd REAL NOT NULL CHECK(amount_usd > 0),
|
|
status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'captured', 'released')),
|
|
reason TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
resolved_at TEXT, -- When captured or released
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_payment_holds_user ON payment_holds(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_payment_holds_order ON payment_holds(order_id);
|
|
CREATE INDEX IF NOT EXISTS idx_payment_holds_status ON payment_holds(status);
|
|
|
|
-- 3. Server orders table
|
|
CREATE TABLE IF NOT EXISTS server_orders (
|
|
id TEXT PRIMARY KEY, -- UUID
|
|
user_id TEXT NOT NULL,
|
|
pricing_id INTEGER NOT NULL, -- References pricing.id (instance_type + region)
|
|
provider_name TEXT NOT NULL, -- linode, vultr, etc.
|
|
status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN (
|
|
'pending', -- Order created, hold placed
|
|
'provisioning', -- API call in progress
|
|
'active', -- Server running
|
|
'failed', -- Provisioning failed
|
|
'deleted' -- Server terminated
|
|
)),
|
|
-- Provider response data
|
|
provider_instance_id TEXT, -- Linode/Vultr instance ID
|
|
server_ip TEXT, -- IPv4 address
|
|
server_ipv6 TEXT, -- IPv6 address
|
|
root_password TEXT, -- Encrypted/hashed
|
|
-- Cost tracking
|
|
monthly_cost_usd REAL NOT NULL,
|
|
-- Metadata
|
|
label TEXT, -- User-defined server label
|
|
os_image TEXT NOT NULL DEFAULT 'ubuntu_22_04',
|
|
error_message TEXT,
|
|
-- Timestamps
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
provisioned_at TEXT,
|
|
deleted_at TEXT,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (pricing_id) REFERENCES pricing(id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_server_orders_user ON server_orders(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_server_orders_status ON server_orders(status);
|
|
CREATE INDEX IF NOT EXISTS idx_server_orders_provider ON server_orders(provider_name, provider_instance_id);
|
|
CREATE INDEX IF NOT EXISTS idx_server_orders_created ON server_orders(created_at);
|