OrchestrAD/backend/internal/db/db.go

// Package db provides database initialization, migrations, and connection management
package db

import (
	"database/sql"
	"embed"
	"fmt"
	"os"
	"path/filepath"

	"github.com/Grace-Solutions/OrchestrAD/internal/config"
	"github.com/Grace-Solutions/OrchestrAD/internal/logging"
	"github.com/golang-migrate/migrate/v4"
	"github.com/golang-migrate/migrate/v4/database/sqlite"
	"github.com/golang-migrate/migrate/v4/source/iofs"
	_ "modernc.org/sqlite"
)

//go:embed migrations/*.sql
var migrationsFS embed.FS

// DB wraps the database connection and provides migration support
type DB struct {
	conn   *sql.DB
	logger *logging.Logger
	config config.DatabaseConfig
}

// New creates a new database connection with the given configuration
func New(cfg config.DatabaseConfig, logger *logging.Logger) (*DB, error) {
	// Ensure the database directory exists (SQLite will not create it), and
	// migrate a legacy database from the data-root into the db/ subdirectory so
	// existing installs keep their data after the path change.
	if dir := filepath.Dir(cfg.Path); dir != "" && dir != "." {
		if err := os.MkdirAll(dir, 0755); err != nil {
			return nil, fmt.Errorf("creating database directory: %w", err)
		}
		migrateLegacyDBLocation(cfg.Path, logger)
	}

	// A restore staged by the API is applied here, before the pool is opened —
	// the only point at which swapping the file is safe.
	applyPendingRestore(cfg.Path, logger)

	logger.Info("Database", "Opening database at %s", cfg.Path)

	// Build connection string with pragmas. modernc.org/sqlite applies these
	// _pragma directives on every connection the pool opens, so WAL mode,
	// foreign-key enforcement, and the busy timeout hold for all connections.
	dsn := fmt.Sprintf("file:%s?_pragma=journal_mode(WAL)&_pragma=foreign_keys(on)&_pragma=busy_timeout(5000)", cfg.Path)

	conn, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, fmt.Errorf("opening database: %w", err)
	}

	// Configure connection pool
	conn.SetMaxOpenConns(cfg.MaxOpenConns)
	conn.SetMaxIdleConns(cfg.MaxIdleConns)

	// Verify connection
	if err := conn.Ping(); err != nil {
		conn.Close()
		return nil, fmt.Errorf("pinging database: %w", err)
	}

	logger.Info("Database", "Database connection established (WAL mode, foreign keys enabled)")

	return &DB{
		conn:   conn,
		logger: logger,
		config: cfg,
	}, nil
}

// migrateLegacyDBLocation moves a database (and its -wal/-shm sidecars) from the
// old data-root location into the new db/ subdirectory, once, when the new file
// does not yet exist. It is best-effort: failures are logged, not fatal.
func migrateLegacyDBLocation(newPath string, logger *logging.Logger) {
	if _, err := os.Stat(newPath); err == nil {
		return // new database already present
	}
	dbDir := filepath.Dir(newPath)
	legacy := filepath.Join(filepath.Dir(dbDir), filepath.Base(newPath))
	if _, err := os.Stat(legacy); err != nil {
		return // nothing to migrate
	}
	for _, suffix := range []string{"", "-wal", "-shm"} {
		from, to := legacy+suffix, newPath+suffix
		if _, err := os.Stat(from); err != nil {
			continue
		}
		if err := os.Rename(from, to); err != nil {
			logger.Warn("Database", "Could not move legacy database file %s: %v", from, err)
		}
	}
	logger.Info("Database", "Migrated existing database into %s", dbDir)
}

// Conn returns the underlying sql.DB connection
func (db *DB) Conn() *sql.DB {
	return db.conn
}

// Path returns the database file path.
func (db *DB) Path() string {
	return db.config.Path
}

// Close closes the database connection
func (db *DB) Close() error {
	db.logger.Info("Database", "Closing database connection")
	return db.conn.Close()
}

// Migrate runs database migrations
func (db *DB) Migrate() error {
	db.logger.Info("Database", "Running database migrations...")

	// Create migration source from embedded files
	source, err := iofs.New(migrationsFS, "migrations")
	if err != nil {
		return fmt.Errorf("creating migration source: %w", err)
	}

	// Create migration driver
	driver, err := sqlite.WithInstance(db.conn, &sqlite.Config{})
	if err != nil {
		return fmt.Errorf("creating migration driver: %w", err)
	}

	// Create migrator
	m, err := migrate.NewWithInstance("iofs", source, "sqlite", driver)
	if err != nil {
		return fmt.Errorf("creating migrator: %w", err)
	}

	// Run migrations
	if err := m.Up(); err != nil && err != migrate.ErrNoChange {
		return fmt.Errorf("running migrations: %w", err)
	}

	version, dirty, err := m.Version()
	if err != nil && err != migrate.ErrNilVersion {
		return fmt.Errorf("getting migration version: %w", err)
	}

	if dirty {
		db.logger.Warn("Database", "Migration state is dirty at version %d", version)
	} else {
		db.logger.Info("Database", "Database schema at version %d", version)
	}

	return nil
}

// Backup creates a backup of the database to the specified path
func (db *DB) Backup(destPath string) error {
	db.logger.Info("Database", "Creating backup to %s", destPath)

	// Use SQLite backup API via VACUUM INTO
	_, err := db.conn.Exec(fmt.Sprintf("VACUUM INTO '%s'", destPath))
	if err != nil {
		return fmt.Errorf("creating backup: %w", err)
	}

	db.logger.Info("Database", "Backup completed successfully")
	return nil
}