vibedns/internal/database/database.go

// Package database owns the SQLite connection, the migration runner and every
// SQL statement in the application. Higher layers talk to *DB and never build
// SQL themselves, which keeps parameterisation and transaction handling in one
// auditable place.
package database

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"net/url"
	"os"
	"path/filepath"
	"time"

	_ "modernc.org/sqlite" // pure-Go driver: no cgo, single static binary
)

// DB wraps the SQLite handle with the helpers the rest of the app needs.
type DB struct {
	*sql.DB
	path string
}

// Common storage errors surfaced to the HTTP layer as 404/409 responses.
var (
	ErrNotFound = errors.New("not found")
	ErrConflict = errors.New("already exists")
)

// Open opens (creating if necessary) the SQLite database at path and applies
// the connection pragmas the application relies on.
//
// The file is created with 0600 and its parent directory with 0750: the
// database holds the administrator password hash and API token hashes, so it
// must not be world readable.
func Open(path string) (*DB, error) {
	if path == "" {
		return nil, errors.New("database path is empty")
	}
	dir := filepath.Dir(path)
	if err := os.MkdirAll(dir, 0o750); err != nil {
		return nil, fmt.Errorf("create database directory %s: %w", dir, err)
	}

	// _txlock=immediate makes database/sql issue BEGIN IMMEDIATE, so SQLite's
	// busy handler can actually resolve writer contention instead of failing
	// with SQLITE_BUSY when a deferred transaction tries to upgrade.
	dsn := "file:" + url.PathEscape(path) + "?" +
		"_pragma=journal_mode(WAL)" +
		"&_pragma=foreign_keys(1)" +
		"&_pragma=busy_timeout(15000)" +
		"&_pragma=synchronous(NORMAL)" +
		"&_txlock=immediate"

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

	// SQLite serialises writes; a small pool avoids piling up blocked writers
	// while still allowing concurrent WAL readers.
	sqlDB.SetMaxOpenConns(8)
	sqlDB.SetMaxIdleConns(8)
	sqlDB.SetConnMaxLifetime(time.Hour)

	ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
	defer cancel()
	if err := sqlDB.PingContext(ctx); err != nil {
		sqlDB.Close()
		return nil, fmt.Errorf("connect to database: %w", err)
	}

	db := &DB{DB: sqlDB, path: path}
	if err := db.hardenPermissions(); err != nil {
		sqlDB.Close()
		return nil, err
	}
	return db, nil
}

// Path returns the on-disk location of the database.
func (db *DB) Path() string { return db.path }

// hardenPermissions restricts the database and its WAL sidecars to the owner.
func (db *DB) hardenPermissions() error {
	for _, suffix := range []string{"", "-wal", "-shm"} {
		p := db.path + suffix
		if _, err := os.Stat(p); err != nil {
			continue // sidecars may not exist yet
		}
		if err := os.Chmod(p, 0o600); err != nil {
			return fmt.Errorf("secure %s: %w", p, err)
		}
	}
	return nil
}

// Checkpoint flushes the write-ahead log into the main database file. It runs
// before backups so the copied file is complete.
func (db *DB) Checkpoint(ctx context.Context) error {
	_, err := db.ExecContext(ctx, `PRAGMA wal_checkpoint(TRUNCATE)`)
	return err
}

// Vacuum rebuilds the database, reclaiming space after large deletions.
func (db *DB) Vacuum(ctx context.Context) error {
	_, err := db.ExecContext(ctx, `VACUUM`)
	return err
}

// Stats describes database size and row counts for the settings UI.
type Stats struct {
	Path      string `json:"path"`
	SizeBytes int64  `json:"size_bytes"`
	WALBytes  int64  `json:"wal_bytes"`
	PageSize  int64  `json:"page_size"`
	PageCount int64  `json:"page_count"`
	FreePages int64  `json:"free_pages"`
	Zones     int64  `json:"zones"`
	Records   int64  `json:"records"`
	Domains   int64  `json:"domains"`
	QueryLogs int64  `json:"query_logs"`
	AuditLogs int64  `json:"audit_logs"`
	APITokens int64  `json:"api_tokens"`
	SchemaVer int    `json:"schema_version"`
}

// Stats collects database size and row-count information.
func (db *DB) Stats(ctx context.Context) (Stats, error) {
	s := Stats{Path: db.path}
	if fi, err := os.Stat(db.path); err == nil {
		s.SizeBytes = fi.Size()
	}
	if fi, err := os.Stat(db.path + "-wal"); err == nil {
		s.WALBytes = fi.Size()
	}
	_ = db.QueryRowContext(ctx, `PRAGMA page_size`).Scan(&s.PageSize)
	_ = db.QueryRowContext(ctx, `PRAGMA page_count`).Scan(&s.PageCount)
	_ = db.QueryRowContext(ctx, `PRAGMA freelist_count`).Scan(&s.FreePages)

	counts := []struct {
		table string
		dst   *int64
	}{
		{"zones", &s.Zones},
		{"records", &s.Records},
		{"domain_entries", &s.Domains},
		{"query_logs", &s.QueryLogs},
		{"audit_logs", &s.AuditLogs},
		{"api_tokens", &s.APITokens},
	}
	for _, c := range counts {
		if err := db.QueryRowContext(ctx, `SELECT COUNT(*) FROM `+c.table).Scan(c.dst); err != nil {
			return s, fmt.Errorf("count %s: %w", c.table, err)
		}
	}
	v, err := db.SchemaVersion(ctx)
	if err != nil {
		return s, err
	}
	s.SchemaVer = v
	return s, nil
}

// InTx runs fn inside a transaction, committing on success and rolling back on
// error or panic.
func (db *DB) InTx(ctx context.Context, fn func(*sql.Tx) error) error {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("begin transaction: %w", err)
	}
	defer func() {
		if p := recover(); p != nil {
			_ = tx.Rollback()
			panic(p)
		}
	}()
	if err := fn(tx); err != nil {
		_ = tx.Rollback()
		return err
	}
	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commit transaction: %w", err)
	}
	return nil
}

// unixPtr converts a nullable epoch-seconds column into a *time.Time.
func unixPtr(n sql.NullInt64) *time.Time {
	if !n.Valid {
		return nil
	}
	t := time.Unix(n.Int64, 0)
	return &t
}

// nullInt64 converts a *int64 into a driver-friendly nullable value.
func nullInt64(v *int64) any {
	if v == nil {
		return nil
	}
	return *v
}