AriWeb-Suite/portal/core/model.mjs

/**
 * portal/core/model.mjs — 포털 중심 표(모든 영역이 공유). docs/ARCHITECTURE.md §4 의 데이터 모델.
 *
 * 각 영역은 자기 세부 표를 자기 model.mjs 에 두고, 여기 있는 중심 표에 열을 더해야 하면 `<영역>_NNNN` 마이그레이션에서 ALTER TABLE 로 더한다(여기를 고치지 않는다).
 * 규칙: id 는 lib/crypto.newId 접두어(u_ g_ s_ d_ ms_ o_ pv_ …), 시각은 ISO UTC, JSON 열은 *_json, 그룹 경계 열 group_id 는 항상 둔다.
 */
export const MIGRATIONS = [{ id: 'core_0001', sql: `
CREATE TABLE IF NOT EXISTS users (
  id TEXT PRIMARY KEY, email TEXT NOT NULL UNIQUE, email_verified_at TEXT, display_name TEXT NOT NULL, pass_hash TEXT, pass_salt TEXT,
  mfa_json TEXT, recovery_json TEXT, failed_logins INTEGER NOT NULL DEFAULT 0, lock_until TEXT, consents_json TEXT, is_operator INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'active', created_at TEXT NOT NULL, updated_at TEXT NOT NULL, last_login_at TEXT, session_gen INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS email_tokens (
  id TEXT PRIMARY KEY, user_id TEXT, email TEXT NOT NULL, kind TEXT NOT NULL, token_hash TEXT NOT NULL UNIQUE, created_at TEXT NOT NULL, expires_at TEXT NOT NULL, used_at TEXT, data_json TEXT
);
CREATE TABLE IF NOT EXISTS groups (
  id TEXT PRIMARY KEY, name TEXT NOT NULL, kind TEXT NOT NULL DEFAULT 'personal', owner_user_id TEXT NOT NULL, contact_email TEXT, contact_phone TEXT,
  billing_user_id TEXT, tech_user_id TEXT, status TEXT NOT NULL DEFAULT 'active', settings_json TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS memberships (
  group_id TEXT NOT NULL, user_id TEXT NOT NULL, role TEXT NOT NULL DEFAULT 'member', alias TEXT, status TEXT NOT NULL DEFAULT 'active', joined_at TEXT NOT NULL, suspended_at TEXT, suspended_by TEXT,
  PRIMARY KEY(group_id, user_id)
);
CREATE INDEX IF NOT EXISTS memberships_user ON memberships(user_id);
CREATE TABLE IF NOT EXISTS invites (
  id TEXT PRIMARY KEY, group_id TEXT NOT NULL, email TEXT NOT NULL, role TEXT NOT NULL DEFAULT 'member', token_hash TEXT NOT NULL UNIQUE, invited_by TEXT, created_at TEXT NOT NULL, expires_at TEXT NOT NULL, accepted_at TEXT, cancelled_at TEXT, resent_at TEXT, resend_count INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS site_permissions (
  site_id TEXT NOT NULL, user_id TEXT NOT NULL, perms_json TEXT NOT NULL, granted_by TEXT, updated_at TEXT NOT NULL, PRIMARY KEY(site_id, user_id)
);
CREATE TABLE IF NOT EXISTS product_versions (
  id TEXT PRIMARY KEY, product_id TEXT NOT NULL, version INTEGER NOT NULL, name TEXT NOT NULL, description TEXT, audience TEXT, kind TEXT NOT NULL DEFAULT 'base',
  billing_cycle TEXT NOT NULL DEFAULT 'monthly', price INTEGER NOT NULL, currency TEXT NOT NULL DEFAULT 'KRW', tax_rate REAL NOT NULL DEFAULT 0.1, limits_json TEXT NOT NULL,
  options_json TEXT, includes_json TEXT, excludes_json TEXT, policy_json TEXT, warn_levels_json TEXT NOT NULL DEFAULT '[70,80,90,95]', approval TEXT NOT NULL DEFAULT 'manual',
  effective_from TEXT NOT NULL, effective_to TEXT, published INTEGER NOT NULL DEFAULT 0, created_by TEXT, created_at TEXT NOT NULL, UNIQUE(product_id, version)
);
CREATE TABLE IF NOT EXISTS orders (
  id TEXT PRIMARY KEY, group_id TEXT NOT NULL, order_no TEXT NOT NULL UNIQUE, kind TEXT NOT NULL DEFAULT 'new', product_version_id TEXT, items_json TEXT NOT NULL, target_type TEXT, target_id TEXT,
  amount INTEGER NOT NULL, tax INTEGER NOT NULL, total INTEGER NOT NULL, currency TEXT NOT NULL DEFAULT 'KRW', status TEXT NOT NULL, payment_method TEXT NOT NULL DEFAULT 'manual_transfer',
  payment_instructions_json TEXT, due_at TEXT, paid_at TEXT, paid_ref TEXT, approved_at TEXT, approved_by TEXT, rejected_at TEXT, rejected_reason TEXT, expired_at TEXT, cancelled_at TEXT,
  idempotency_key TEXT UNIQUE, created_by TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, note TEXT
);
CREATE INDEX IF NOT EXISTS orders_group ON orders(group_id, created_at);
CREATE TABLE IF NOT EXISTS sites (
  id TEXT PRIMARY KEY, group_id TEXT NOT NULL, name TEXT NOT NULL, purpose TEXT, webmaker_project_id TEXT, template TEXT, lang TEXT NOT NULL DEFAULT 'ko', timezone TEXT NOT NULL DEFAULT 'Asia/Seoul',
  lifecycle TEXT NOT NULL DEFAULT 'draft', deploy_mode TEXT, primary_domain TEXT, site_count_state TEXT NOT NULL DEFAULT 'none', site_count_reservation_id TEXT, server_id TEXT, external_agent_id TEXT,
  current_version TEXT, current_deployment_id TEXT, deploy_verify TEXT, health TEXT NOT NULL DEFAULT 'unknown', health_checked_at TEXT, maintenance INTEGER NOT NULL DEFAULT 0, search_visible INTEGER NOT NULL DEFAULT 1,
  description TEXT, settings_json TEXT, paused_at TEXT, cancel_at TEXT, retention_until TEXT, delete_after TEXT, deleted_at TEXT, created_by TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS sites_group ON sites(group_id, lifecycle);
CREATE TABLE IF NOT EXISTS deployments (
  id TEXT PRIMARY KEY, site_id TEXT NOT NULL, group_id TEXT NOT NULL, version_ref TEXT, version_label TEXT, mode TEXT NOT NULL, target_id TEXT, job_id TEXT, hal_job_id TEXT,
  status TEXT NOT NULL, verify_state TEXT, previous_version TEXT, result_json TEXT, started_by TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, ended_at TEXT
);
CREATE INDEX IF NOT EXISTS deployments_site ON deployments(site_id, created_at);
CREATE TABLE IF NOT EXISTS domains (
  id TEXT PRIMARY KEY, group_id TEXT NOT NULL, name TEXT NOT NULL UNIQUE, purpose TEXT NOT NULL DEFAULT 'web', site_id TEXT, mail_service_id TEXT, is_primary INTEGER NOT NULL DEFAULT 0,
  ownership_state TEXT NOT NULL DEFAULT 'pending', ownership_token TEXT, dns_state TEXT NOT NULL DEFAULT 'pending', cert_state TEXT NOT NULL DEFAULT 'none', cert_expires_at TEXT, mail_dns_state TEXT, dns_mode TEXT,
  last_checked_at TEXT, check_json TEXT, job_id TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS domains_group ON domains(group_id);
CREATE TABLE IF NOT EXISTS mail_services (
  id TEXT PRIMARY KEY, group_id TEXT NOT NULL, kind TEXT NOT NULL, site_id TEXT, name TEXT NOT NULL, domain TEXT, arimail_server_id TEXT, status TEXT NOT NULL DEFAULT 'needs_setup',
  dns_state TEXT, send_state TEXT, receive_state TEXT, migrating INTEGER NOT NULL DEFAULT 0, pop_enabled INTEGER NOT NULL DEFAULT 1, imap_enabled INTEGER NOT NULL DEFAULT 1,
  settings_json TEXT, cancelled_at TEXT, retention_until TEXT, delete_after TEXT, deleted_at TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS mail_services_group ON mail_services(group_id);
CREATE TABLE IF NOT EXISTS mailboxes (
  id TEXT PRIMARY KEY, mail_service_id TEXT NOT NULL, group_id TEXT NOT NULL, address TEXT NOT NULL UNIQUE, display_name TEXT, status TEXT NOT NULL DEFAULT 'active', quota_bytes INTEGER,
  arimail_user_id TEXT, invite_state TEXT, must_change_password INTEGER NOT NULL DEFAULT 0, last_login_at TEXT, settings_json TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, deleted_at TEXT
);
CREATE TABLE IF NOT EXISTS backup_options (
  id TEXT PRIMARY KEY, site_id TEXT NOT NULL UNIQUE, group_id TEXT NOT NULL, product_version_id TEXT, order_id TEXT, status TEXT NOT NULL DEFAULT 'active', schedule_json TEXT NOT NULL, retention_json TEXT NOT NULL,
  storage_limit_bytes INTEGER, first_backup_due_at TEXT, next_run_at TEXT, last_success_at TEXT, last_failure_at TEXT, consecutive_failures INTEGER NOT NULL DEFAULT 0, paused_reason TEXT,
  ends_at TEXT, delete_after TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS backups (
  id TEXT PRIMARY KEY, site_id TEXT NOT NULL, group_id TEXT NOT NULL, option_id TEXT, kind TEXT NOT NULL DEFAULT 'scheduled', tier TEXT, created_at TEXT NOT NULL, completed_at TEXT, scope_json TEXT, size_bytes INTEGER,
  location TEXT, checksum TEXT, verify_state TEXT NOT NULL DEFAULT 'pending', verified_at TEXT, expires_at TEXT, status TEXT NOT NULL, job_id TEXT, error TEXT, deleted_at TEXT
);
CREATE INDEX IF NOT EXISTS backups_site ON backups(site_id, created_at);
CREATE TABLE IF NOT EXISTS restores (
  id TEXT PRIMARY KEY, site_id TEXT NOT NULL, group_id TEXT NOT NULL, backup_id TEXT NOT NULL, status TEXT NOT NULL, cost INTEGER NOT NULL DEFAULT 0, order_id TEXT, approval_state TEXT,
  protect_point_id TEXT, protect_until TEXT, revert_until TEXT, completion_mode TEXT NOT NULL DEFAULT 'user_confirm', job_id TEXT, requested_by TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, ended_at TEXT
);
CREATE TABLE IF NOT EXISTS relocations (
  id TEXT PRIMARY KEY, group_id TEXT NOT NULL, subject_type TEXT NOT NULL, subject_id TEXT NOT NULL, kind TEXT NOT NULL, reason TEXT, from_server_id TEXT, to_server_id TEXT, status TEXT NOT NULL,
  boundaries_json TEXT, approval_state TEXT, scheduled_at TEXT, cost INTEGER NOT NULL DEFAULT 0, job_id TEXT, requested_by TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, ended_at TEXT
);
CREATE TABLE IF NOT EXISTS ownership_transfers (
  id TEXT PRIMARY KEY, from_group_id TEXT NOT NULL, to_group_id TEXT, to_email TEXT, scope_json TEXT NOT NULL, status TEXT NOT NULL, token_hash TEXT, requested_by TEXT, accepted_by TEXT, reviewed_by TEXT,
  review_json TEXT, billing_cutover_at TEXT, expires_at TEXT NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, completed_at TEXT, dispute_note TEXT
);
CREATE TABLE IF NOT EXISTS usage_samples (
  id TEXT PRIMARY KEY, group_id TEXT NOT NULL, subject_type TEXT NOT NULL, subject_id TEXT NOT NULL, metric TEXT NOT NULL, value INTEGER NOT NULL, period TEXT, measured_at TEXT NOT NULL, source TEXT
);
CREATE INDEX IF NOT EXISTS usage_samples_subject ON usage_samples(subject_type, subject_id, metric, measured_at);
CREATE TABLE IF NOT EXISTS servers_cache (
  id TEXT PRIMARY KEY, name TEXT, role TEXT, location TEXT, status TEXT, accepts_new INTEGER NOT NULL DEFAULT 1, capacity_json TEXT, updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS settings (key TEXT PRIMARY KEY, value_json TEXT, updated_at TEXT NOT NULL, updated_by TEXT);
` }];