OrchestrAD/backend/internal/db/restore.go

// Database restore.
//
// A running OrchestrAD holds an open connection pool that every repository
// shares, and closing it invalidates those handles process-wide — so the
// database file cannot be swapped underneath a live server. Restore is
// therefore a two-phase operation:
//
//	StageRestore   validates the backup and parks it next to the database
//	ApplyPending   runs before the pool is opened (from New) and moves it in
//
// The staged file is only ever applied by a process that has not yet opened the
// database, which makes the swap safe without any coordination. The CLI
// `restore` command performs both phases in one go, since it runs standalone.
package db

import (
	"database/sql"
	"fmt"
	"io"
	"os"
	"path/filepath"
	"time"

	"github.com/Grace-Solutions/OrchestrAD/internal/logging"
)

// pendingSuffix marks a validated backup staged for the next start.
const pendingSuffix = ".restore-pending"

// StageRestore validates backupPath and stages it to be applied to dbPath on
// the next start. It does not modify the live database.
func StageRestore(dbPath, backupPath string) error {
	if err := ValidateBackup(backupPath); err != nil {
		return err
	}
	pending := dbPath + pendingSuffix
	if err := copyFile(backupPath, pending); err != nil {
		return fmt.Errorf("staging restore: %w", err)
	}
	// Validate the staged copy too, so a truncated copy can never be applied.
	if err := ValidateBackup(pending); err != nil {
		_ = os.Remove(pending)
		return fmt.Errorf("staged copy failed validation: %w", err)
	}
	return nil
}

// PendingRestorePath returns the staged file for dbPath, or "" if none.
func PendingRestorePath(dbPath string) string {
	pending := dbPath + pendingSuffix
	if _, err := os.Stat(pending); err != nil {
		return ""
	}
	return pending
}

// CancelPendingRestore discards a staged restore.
func CancelPendingRestore(dbPath string) error {
	pending := dbPath + pendingSuffix
	if _, err := os.Stat(pending); err != nil {
		return nil
	}
	return os.Remove(pending)
}

// applyPendingRestore moves a staged restore into place. It must run before the
// database is opened. The database being replaced is preserved alongside as
// <db>.replaced-<timestamp> so a bad restore is recoverable, and the WAL/SHM
// sidecars of the old database are removed — leaving them would let SQLite
// replay a journal belonging to the previous file over the restored one.
func applyPendingRestore(dbPath string, logger *logging.Logger) {
	pending := PendingRestorePath(dbPath)
	if pending == "" {
		return
	}
	logf := func(format string, args ...any) {
		if logger != nil {
			logger.Info("Database", format, args...)
		}
	}
	warnf := func(format string, args ...any) {
		if logger != nil {
			logger.Warn("Database", format, args...)
		}
	}

	// Re-validate: the staged file may have sat on disk across a crash.
	if err := ValidateBackup(pending); err != nil {
		warnf("Discarding staged restore, it did not validate: %v", err)
		_ = os.Remove(pending)
		return
	}

	if _, err := os.Stat(dbPath); err == nil {
		aside := fmt.Sprintf("%s.replaced-%s", dbPath, time.Now().UTC().Format("20060102-150405"))
		if err := os.Rename(dbPath, aside); err != nil {
			warnf("Could not set the current database aside (%v); restore not applied", err)
			return
		}
		logf("Previous database preserved at %s", filepath.Base(aside))
	}
	for _, suffix := range []string{"-wal", "-shm"} {
		_ = os.Remove(dbPath + suffix)
	}

	if err := os.Rename(pending, dbPath); err != nil {
		warnf("Could not apply staged restore (%v)", err)
		return
	}
	logf("Applied staged restore from backup")
}

// ValidateBackup checks that path is a readable SQLite database that passes an
// integrity check and carries this application's schema. It is deliberately
// strict: applying an unrelated or corrupt file would destroy the install.
func ValidateBackup(path string) error {
	info, err := os.Stat(path)
	if err != nil {
		return fmt.Errorf("backup file not found: %s", path)
	}
	if info.IsDir() || info.Size() == 0 {
		return fmt.Errorf("not a usable backup file: %s", path)
	}

	// Open read-only so validation can never modify the candidate.
	conn, err := sql.Open("sqlite", fmt.Sprintf("file:%s?mode=ro&_pragma=busy_timeout(5000)", path))
	if err != nil {
		return fmt.Errorf("opening backup: %w", err)
	}
	defer conn.Close()

	var result string
	if err := conn.QueryRow(`PRAGMA integrity_check`).Scan(&result); err != nil {
		return fmt.Errorf("backup is not a readable SQLite database: %w", err)
	}
	if result != "ok" {
		return fmt.Errorf("backup failed integrity check: %s", result)
	}

	// It must be an OrchestrAD database, not just any SQLite file.
	var n int
	if err := conn.QueryRow(
		`SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='schema_migrations'`,
	).Scan(&n); err != nil || n == 0 {
		return fmt.Errorf("backup does not look like an OrchestrAD database (no schema_migrations table)")
	}
	if err := conn.QueryRow(
		`SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='users'`,
	).Scan(&n); err != nil || n == 0 {
		return fmt.Errorf("backup does not look like an OrchestrAD database (no users table)")
	}
	return nil
}

func copyFile(src, dst string) error {
	in, err := os.Open(src)
	if err != nil {
		return err
	}
	defer in.Close()
	out, err := os.OpenFile(dst, os.O_WRONLY|os.O_CREATE|os.O_TRUNC, 0o600)
	if err != nil {
		return err
	}
	if _, err := io.Copy(out, in); err != nil {
		out.Close()
		_ = os.Remove(dst)
		return err
	}
	if err := out.Sync(); err != nil {
		out.Close()
		return err
	}
	return out.Close()
}