AriWeb-Suite/hal/schema.mjs

/**
 * hal/schema.mjs — HAL DB 마이그레이션(hal.sqlite).
 * lib 공통 표(sessions·jobs·notifications·audit) 뒤에 HAL 고유 표를 둔다. docs/ARCHITECTURE.md §5·§10, Dev-Order §28·§32·§33·§34.
 */
import * as session from '../lib/session.mjs';
import * as jobs from '../lib/jobs.mjs';
import * as notify from '../lib/notify.mjs';
import * as audit from '../lib/audit.mjs';
import { MIGRATIONS as MAIL_MIGRATIONS } from './mailsvc.mjs';

export const MIGRATIONS = [...session.MIGRATIONS, ...jobs.MIGRATIONS, ...notify.MIGRATIONS, ...audit.MIGRATIONS, { id: 'hal_0001', sql: `
-- 관리 서버(§28). agent_id 는 그 서버에서 돌아가는 Node Agent. target/actual 모듈 버전은 module_targets 가 가진다.
CREATE TABLE IF NOT EXISTS servers (
  id TEXT PRIMARY KEY, name TEXT NOT NULL, role TEXT NOT NULL, location TEXT, status TEXT NOT NULL DEFAULT 'pending', accepts_new INTEGER NOT NULL DEFAULT 0,
  approved_at TEXT, approved_by TEXT, agent_id TEXT, last_seen_at TEXT, clock_skew_ms INTEGER, agent_version TEXT, metrics_json TEXT, capacity_json TEXT, facts_json TEXT,
  maintenance_note TEXT, isolated_reason TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL
);
-- Node Agent(관리형) 또는 External Pull Agent(고객 외부 서버, 사이트 하나에 귀속). 비밀은 보관소('service:agent:<id>')에, 여기엔 해시만.
CREATE TABLE IF NOT EXISTS agents (
  id TEXT PRIMARY KEY, server_id TEXT, kind TEXT NOT NULL DEFAULT 'managed', secret_hash TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', label TEXT,
  group_id TEXT, site_id TEXT, version TEXT, registered_at TEXT NOT NULL, last_seen_at TEXT, revoked_at TEXT, facts_json TEXT
);
-- 일회용 등록 토큰(10분, 1회). 외부 Agent 토큰은 group_id/site_id 가 있다.
CREATE TABLE IF NOT EXISTS registration_tokens (
  id TEXT PRIMARY KEY, token_hash TEXT NOT NULL UNIQUE, kind TEXT NOT NULL DEFAULT 'managed', server_id TEXT, group_id TEXT, site_id TEXT, label TEXT,
  created_by TEXT, created_at TEXT NOT NULL, expires_at TEXT NOT NULL, used_at TEXT, used_by_agent TEXT
);
-- 릴리스 등록부(§32.1). artifacts_json = { 모듈: { file, sha256, size } }
CREATE TABLE IF NOT EXISTS releases (
  version TEXT PRIMARY KEY, main_commit TEXT NOT NULL, built_at TEXT NOT NULL, artifacts_json TEXT NOT NULL, checks_json TEXT, status TEXT NOT NULL DEFAULT 'registered',
  approved_by TEXT, approved_at TEXT, hal_deployed_at TEXT, notes TEXT, registered_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS modules (name TEXT PRIMARY KEY, target_version TEXT, updated_at TEXT NOT NULL);
-- 모듈 × 서버 배치 상태(목표 버전·실제 버전·건강·마지막 확인)
CREATE TABLE IF NOT EXISTS module_targets (
  module TEXT NOT NULL, server_id TEXT NOT NULL, target_version TEXT, actual_version TEXT, health TEXT NOT NULL DEFAULT 'unknown', active INTEGER, last_check_at TEXT, last_error TEXT,
  last_job_id TEXT, updated_at TEXT NOT NULL, PRIMARY KEY(module, server_id)
);
-- Agent 에게 배정된 작업(HAL 작업의 자식). 상태: queued → running → done|failed|needs_user|cancelled
CREATE TABLE IF NOT EXISTS agent_jobs (
  id TEXT PRIMARY KEY, hal_job_id TEXT, agent_id TEXT NOT NULL, kind TEXT NOT NULL, payload_json TEXT, status TEXT NOT NULL DEFAULT 'queued', progress INTEGER NOT NULL DEFAULT 0,
  steps_json TEXT, log_json TEXT, result_json TEXT, error TEXT, error_code TEXT, user_message TEXT, cancel_requested INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL, assigned_at TEXT, started_at TEXT, ended_at TEXT, heartbeat_at TEXT
);
CREATE INDEX IF NOT EXISTS agent_jobs_agent ON agent_jobs(agent_id, status, created_at);
CREATE INDEX IF NOT EXISTS agent_jobs_hal ON agent_jobs(hal_job_id);
-- 서버 지표(90일 이상 보존, §28.1)
CREATE TABLE IF NOT EXISTS metrics_samples (
  server_id TEXT NOT NULL, at TEXT NOT NULL, cpu REAL, mem_used REAL, mem_total REAL, disk_used REAL, disk_total REAL, inode_used REAL, inode_total REAL, io_read REAL, io_write REAL, net_in REAL, net_out REAL, clock_skew_ms INTEGER, load1 REAL,
  PRIMARY KEY(server_id, at)
);
-- 사건(§31): 같은 원인은 key 로 묶는다
CREATE TABLE IF NOT EXISTS incidents (
  id TEXT PRIMARY KEY, key TEXT NOT NULL, severity TEXT NOT NULL, title TEXT NOT NULL, component TEXT, customer_impact TEXT, started_at TEXT NOT NULL, resolved_at TEXT, verified_at TEXT,
  assignee TEXT, public INTEGER NOT NULL DEFAULT 0, public_note TEXT, notes_json TEXT, updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS incidents_key ON incidents(key, resolved_at);
-- 운영자 계정(§25). 고객 계정과 완전히 분리. mfa_json = { totp: { secret(보관소 참조 id), enabledAt } }
CREATE TABLE IF NOT EXISTS ops_accounts (
  id TEXT PRIMARY KEY, login TEXT NOT NULL UNIQUE, display_name TEXT NOT NULL, pass_hash TEXT NOT NULL, pass_salt TEXT NOT NULL, mfa_json TEXT, status TEXT NOT NULL DEFAULT 'active',
  failed_logins INTEGER NOT NULL DEFAULT 0, lock_until TEXT, last_login_at TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, must_change_password INTEGER NOT NULL DEFAULT 0
);
-- 짧은 수명의 사용자·기기 귀속 접근권(§25: 허용 네트워크 밖에서는 이것 없이 로그인 화면도 열리지 않음)
CREATE TABLE IF NOT EXISTS ops_access_grants (
  id TEXT PRIMARY KEY, account_id TEXT NOT NULL, code_hash TEXT UNIQUE, cookie_hash TEXT UNIQUE, ua_hash TEXT, ip TEXT, created_by TEXT, created_at TEXT NOT NULL, expires_at TEXT NOT NULL, bound_at TEXT, revoked_at TEXT, reason TEXT
);
-- 긴급 복구 세션(§33)
CREATE TABLE IF NOT EXISTS emergency_sessions (
  id TEXT PRIMARY KEY, incident_key TEXT NOT NULL, reason TEXT NOT NULL, scope_json TEXT NOT NULL, status TEXT NOT NULL, declared_by TEXT NOT NULL, declared_at TEXT NOT NULL,
  approvals_json TEXT NOT NULL DEFAULT '[]', credential_hash TEXT, credential_issued_at TEXT, credential_expires_at TEXT, credential_revoked_at TEXT,
  actions_json TEXT NOT NULL DEFAULT '[]', verify_json TEXT, reconcile_json TEXT, closed_at TEXT, closed_by TEXT, hal_healthy_at_declare INTEGER, updated_at TEXT NOT NULL
);
-- 재해복구(§34)
CREATE TABLE IF NOT EXISTS dr_config (id INTEGER PRIMARY KEY CHECK(id=1), rto_minutes INTEGER, rpo_minutes INTEGER, snapshot_interval_minutes INTEGER, retention_count INTEGER, external_store TEXT, updated_at TEXT, updated_by TEXT);
CREATE TABLE IF NOT EXISTS dr_snapshots (id TEXT PRIMARY KEY, created_at TEXT NOT NULL, location TEXT NOT NULL, size_bytes INTEGER, sha256 TEXT, verify_state TEXT NOT NULL DEFAULT 'pending', verified_at TEXT, external_copied_at TEXT, external_location TEXT, kind TEXT NOT NULL DEFAULT 'scheduled', error TEXT, deleted_at TEXT);
CREATE TABLE IF NOT EXISTS dr_restores (id TEXT PRIMARY KEY, snapshot_id TEXT NOT NULL, requested_by TEXT NOT NULL, approved_by TEXT, approved_at TEXT, old_control_stopped_confirmed_at TEXT, started_at TEXT, finished_at TEXT, status TEXT NOT NULL, checks_json TEXT, diff_json TEXT, note TEXT);
CREATE TABLE IF NOT EXISTS dr_drills (id TEXT PRIMARY KEY, started_at TEXT NOT NULL, finished_at TEXT, snapshot_id TEXT, performed_by TEXT, steps_json TEXT NOT NULL, measured_rto_minutes REAL, measured_rpo_minutes REAL, result TEXT NOT NULL DEFAULT 'running', findings_json TEXT, next_due_at TEXT);
-- 부트스트랩·실행 상태(§32.2). 행 하나(id=1).
CREATE TABLE IF NOT EXISTS bootstrap_state (
  id INTEGER PRIMARY KEY CHECK(id=1), first_install INTEGER NOT NULL DEFAULT 1, release_version TEXT, main_commit TEXT, artifact_sha256 TEXT, hal_running_version TEXT, hal_started_at TEXT,
  first_agent_id TEXT, first_agent_registered_at TEXT, manual_install_locked INTEGER NOT NULL DEFAULT 0, live_instance_id TEXT, live_lease_at TEXT, completed_at TEXT, updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS transfers (id TEXT PRIMARY KEY, created_by_agent TEXT, path TEXT NOT NULL, size_bytes INTEGER, sha256 TEXT, created_at TEXT NOT NULL, expires_at TEXT NOT NULL, purpose TEXT, meta_json TEXT, deleted_at TEXT);
CREATE TABLE IF NOT EXISTS settings (key TEXT PRIMARY KEY, value_json TEXT, updated_at TEXT NOT NULL, updated_by TEXT);
CREATE TABLE IF NOT EXISTS portal_events_outbox (id TEXT PRIMARY KEY, payload_json TEXT NOT NULL, created_at TEXT NOT NULL, attempts INTEGER NOT NULL DEFAULT 0, next_at TEXT NOT NULL, delivered_at TEXT, last_error TEXT);
` },
  // [통합 보완] 같은 호스트 재시작 판정을 위한 임대 소유자 호스트·PID
  { id: 'hal_0002_live_host', sql: `ALTER TABLE bootstrap_state ADD COLUMN live_host TEXT; ALTER TABLE bootstrap_state ADD COLUMN live_pid TEXT;` },
  // [legacy 메일 서비스 가져오기] HAL 관리형 메일 서비스 등록부·가져오기 단위(hal/mailsvc.mjs). 표 추가만(기존 표 변경 없음).
  ...MAIL_MIGRATIONS,
  // Webmaker 전체 인벤토리는 heartbeat 2 MiB 한도를 넘을 수 있어 인증된 배치로 원자적으로 조립한다.
  { id: 'hal_0004_webmaker_inventory_stage', sql: `
CREATE TABLE IF NOT EXISTS agent_inventory_staging (
  agent_id TEXT NOT NULL REFERENCES agents(id) ON DELETE CASCADE, kind TEXT NOT NULL, scan_id TEXT NOT NULL, total INTEGER NOT NULL, next_offset INTEGER NOT NULL DEFAULT 0,
  meta_json TEXT NOT NULL, items_json TEXT NOT NULL DEFAULT '[]', updated_at TEXT NOT NULL,
  PRIMARY KEY(agent_id, kind)
);
` },
  // 사전 릴리스에서 FK 없이 같은 staging 표를 만든 경우에도 고아 row를 정리하고 이후 삭제를 보장한다.
  { id: 'hal_0005_webmaker_inventory_cleanup', sql: `
DELETE FROM agent_inventory_staging WHERE agent_id NOT IN (SELECT id FROM agents);
CREATE TRIGGER IF NOT EXISTS agent_inventory_staging_agent_cleanup
AFTER DELETE ON agents BEGIN
  DELETE FROM agent_inventory_staging WHERE agent_id=OLD.id;
END;
` },
];