7ef0ec7594
- 003_add_server_tables.sql: server_orders, server_instances tables - 003_server_sessions.sql: KV-based session tracking - 004_add_terminated_at.sql: track instance termination time Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
55 lines
2.4 KiB
SQL
55 lines
2.4 KiB
SQL
-- Migration 003: Add server provisioning tables
|
|
-- Date: 2026-01-28
|
|
-- Description: Add server_orders and user_servers tables for Queue-based server provisioning
|
|
|
|
-- Server orders table (provisioning requests)
|
|
CREATE TABLE IF NOT EXISTS server_orders (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
telegram_user_id TEXT NOT NULL,
|
|
spec_id TEXT NOT NULL, -- 서버 스펙 ID (plan_name)
|
|
region TEXT NOT NULL, -- 리전 코드
|
|
label TEXT, -- 서버 라벨 (선택)
|
|
price_paid INTEGER NOT NULL, -- 지불 금액 (원)
|
|
status TEXT NOT NULL DEFAULT 'pending', -- pending, provisioning, completed, failed, refunded
|
|
provider TEXT NOT NULL, -- anvil
|
|
instance_id TEXT, -- 생성된 인스턴스 ID
|
|
ip_address TEXT, -- 할당된 IP
|
|
root_password TEXT, -- 초기 root 비밀번호 (암호화 권장)
|
|
error_message TEXT, -- 실패 시 에러 메시지
|
|
provisioned_at DATETIME, -- 프로비저닝 완료 시각
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
);
|
|
|
|
-- User servers table (active servers)
|
|
CREATE TABLE IF NOT EXISTS user_servers (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
order_id INTEGER NOT NULL UNIQUE,
|
|
provider TEXT NOT NULL,
|
|
instance_id TEXT NOT NULL,
|
|
label TEXT,
|
|
spec_id TEXT NOT NULL,
|
|
region TEXT NOT NULL,
|
|
ip_address TEXT,
|
|
status TEXT NOT NULL DEFAULT 'running', -- running, stopped, deleted
|
|
monthly_price INTEGER NOT NULL,
|
|
expires_at DATETIME, -- 다음 결제일
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id),
|
|
FOREIGN KEY (order_id) REFERENCES server_orders(id)
|
|
);
|
|
|
|
-- Indexes for server_orders
|
|
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_telegram ON server_orders(telegram_user_id);
|
|
|
|
-- Indexes for user_servers
|
|
CREATE INDEX IF NOT EXISTS idx_user_servers_user ON user_servers(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_user_servers_status ON user_servers(status);
|
|
CREATE INDEX IF NOT EXISTS idx_user_servers_instance ON user_servers(provider, instance_id);
|