vikunja/pkg/db/db.go

// Vikunja is a to-do list application to facilitate your life.
// Copyright 2018-present Vikunja and contributors. All rights reserved.
//
// This program is free software: you can redistribute it and/or modify
// it under the terms of the GNU Affero General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Affero General Public License for more details.
//
// You should have received a copy of the GNU Affero General Public License
// along with this program.  If not, see <https://www.gnu.org/licenses/>.

package db

import (
	"errors"
	"fmt"
	"net/url"
	"os"
	"path/filepath"
	"regexp"
	"runtime"
	"strconv"
	"strings"
	"time"

	"code.vikunja.io/api/pkg/config"
	"code.vikunja.io/api/pkg/log"

	"github.com/go-sql-driver/mysql"
	"github.com/prometheus/client_golang/prometheus"
	"github.com/prometheus/client_golang/prometheus/collectors"
	"xorm.io/builder"
	"xorm.io/xorm"
	"xorm.io/xorm/names"
	"xorm.io/xorm/schemas"

	_ "github.com/jackc/pgx/v5/stdlib" // Because.
	_ "github.com/mattn/go-sqlite3"    // Because.
)

var (
	// We only want one instance of the engine, so we can create it once and reuse it
	x *xorm.Engine
	// paradedbInstalled marks whether the paradedb extension is available
	// and can be used for full text search.
	paradedbInstalled bool
	// unaccentAvailable makes ILIKE accent-insensitive on postgres.
	unaccentAvailable bool
)

var postgresConnectionCredentials = regexp.MustCompile(`(?i)(postgres(?:ql)?://)[^@\s"]+@`)

// DatabasePathMemory is the database.path value selecting an ephemeral database.
const DatabasePathMemory = "memory"

// registeredTables holds all table beans registered by Vikunja packages.
var registeredTables []interface{}

// RegisterTables registers table beans so that Dump and WipeEverything
// only operate on known Vikunja tables.
func RegisterTables(tables []interface{}) {
	registeredTables = append(registeredTables, tables...)
}

// RegisteredTableNames returns the table names of all registered Vikunja tables.
func RegisteredTableNames() []string {
	tableNames := make([]string, 0, len(registeredTables)+1)
	for _, bean := range registeredTables {
		tableInfo, err := x.TableInfo(bean)
		if err != nil {
			log.Fatalf("Could not get table info for bean: %v", err)
		}
		tableNames = append(tableNames, tableInfo.Name)
	}
	// The xormigrate migration tracking table is not registered via GetTables()
	tableNames = append(tableNames, "migration")
	return tableNames
}

// CreateDBEngine initializes a db engine from the config
func CreateDBEngine() (engine *xorm.Engine, err error) {

	if x != nil {
		return x, nil
	}

	// If the database type is not set, this likely means we need to initialize the config first
	if config.DatabaseType.GetString() == "" {
		config.InitConfig()
	}

	// Use Mysql if set
	switch config.DatabaseType.GetString() {
	case "mysql":
		engine, err = initMysqlEngine()
		if err != nil {
			return
		}
	case "postgres":
		engine, err = initPostgresEngine()
		if err != nil {
			return nil, sanitizePostgresConnectionError(
				err,
				config.DatabaseUser.GetString(),
				config.DatabasePassword.GetString(),
			)
		}
	case "sqlite":
		// Otherwise use sqlite
		engine, err = initSqliteEngine()
		if err != nil {
			return
		}
	default:
		log.Fatalf("Unknown database type %s", config.DatabaseType.GetString())
	}

	engine.SetTZLocation(config.GetTimeZone()) // Vikunja's timezone
	loc, err := time.LoadLocation("GMT")       // The db data timezone
	if err != nil {
		log.Fatalf("Error parsing time zone: %s", err)
	}
	engine.SetTZDatabase(loc)
	engine.SetMapper(names.GonicMapper{})
	logger := log.NewXormLogger(config.LogEnabled.GetBool(), config.LogDatabase.GetString(), config.LogDatabaseLevel.GetString(), config.LogFormat.GetString())
	engine.SetLogger(logger)

	// xorm connects lazily, so this is where an unreachable database first surfaces. Without it
	// the first real query reports it instead, which reads as a schema or extension bug (#3287).
	if err = engine.Ping(); err != nil {
		if config.DatabaseType.GetString() == "postgres" {
			err = sanitizePostgresConnectionError(
				err,
				config.DatabaseUser.GetString(),
				config.DatabasePassword.GetString(),
			)
		}
		return nil, err
	}

	engine.AddHook(writeInvalidationHook{})

	x = engine
	return
}

func initMysqlEngine() (engine *xorm.Engine, err error) {
	// We're using utf8mb here instead of just utf8 because we want to use non-BMP characters.
	// See https://stackoverflow.com/a/30074553/10924593 for more info.
	host := fmt.Sprintf("tcp(%s)", config.DatabaseHost.GetString())
	if config.DatabaseHost.GetString()[0] == '/' { // looks like a unix socket
		host = fmt.Sprintf("unix(%s)", config.DatabaseHost.GetString())
	}

	connStr := fmt.Sprintf(
		"%s:%s@%s/%s?charset=utf8mb4&parseTime=true&tls=%s",
		config.DatabaseUser.GetString(),
		config.DatabasePassword.GetString(),
		host,
		config.DatabaseDatabase.GetString(),
		config.DatabaseTLS.GetString())
	// Xorm has no transaction options, so every pooled connection needs this default.
	engine, err = xorm.NewEngine("mysql", connStr+"&transaction_isolation=%27READ-COMMITTED%27")
	if err != nil {
		return
	}
	if err = engine.Ping(); err != nil {
		var mysqlErr *mysql.MySQLError
		if !errors.As(err, &mysqlErr) || mysqlErr.Number != 1193 {
			_ = engine.Close()
			return nil, err
		}
		if err = engine.Close(); err != nil {
			return nil, err
		}
		// Older MariaDB releases use the legacy variable name.
		engine, err = xorm.NewEngine("mysql", connStr+"&tx_isolation=%27READ-COMMITTED%27")
		if err != nil {
			return nil, err
		}
	}
	engine.SetMaxOpenConns(config.DatabaseMaxOpenConnections.GetInt())
	engine.SetMaxIdleConns(config.DatabaseMaxIdleConnections.GetInt())
	maxLifetime, err := time.ParseDuration(strconv.Itoa(config.DatabaseMaxConnectionLifetime.GetInt()) + `ms`)
	if err != nil {
		return
	}
	engine.SetConnMaxLifetime(maxLifetime)
	return
}

// parsePostgreSQLHostPort parses given input in various forms defined in
// https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-CONNSTRING
// and returns proper host and port number.
func parsePostgreSQLHostPort(info string) (string, string) {
	host, port := "127.0.0.1", "5432"
	if strings.Contains(info, ":") && !strings.HasSuffix(info, "]") {
		idx := strings.LastIndex(info, ":")
		host = info[:idx]
		port = info[idx+1:]
	} else if len(info) > 0 {
		host = info
	}
	return host, port
}

func sanitizePostgresConnectionError(err error, user, password string) error {
	if err == nil {
		return nil
	}

	message := err.Error()
	if user != "" || password != "" {
		userInfoCandidates := []string{
			user + ":" + password + "@",
			url.PathEscape(user) + ":" + url.PathEscape(password) + "@",
			url.QueryEscape(user) + ":" + url.QueryEscape(password) + "@",
		}
		for _, userInfo := range userInfoCandidates {
			message = strings.ReplaceAll(message, userInfo, "<redacted>@")
		}
	}
	message = postgresConnectionCredentials.ReplaceAllString(message, `${1}<redacted>@`)

	return errors.New(message)
}

// Copied and adopted from https://github.com/go-gitea/gitea/blob/f337c32e868381c6d2d948221aca0c59f8420c13/modules/setting/database.go#L176-L186
func getPostgreSQLConnectionString(dbHost, dbUser, dbPasswd, dbName, dbSchema, dbSslMode, dbSslCert, dbSslKey, dbSslRootCert, queryExecMode string) (connStr string) {
	dbParam := "?"
	if strings.Contains(dbName, dbParam) {
		dbParam = "&"
	}
	host, port := parsePostgreSQLHostPort(dbHost)
	// PathEscape leaves '@' and ':' alone, which pgx then reads as userinfo delimiters.
	userInfo := url.UserPassword(dbUser, dbPasswd).String()
	if host[0] == '/' { // looks like a unix socket
		connStr = fmt.Sprintf("postgres://%s@:%s/%s%ssslmode=%s&sslcert=%s&sslkey=%s&sslrootcert=%s&host=%s",
			userInfo, port, dbName, dbParam, dbSslMode, dbSslCert, dbSslKey, dbSslRootCert, host)
	} else {
		connStr = fmt.Sprintf("postgres://%s@%s:%s/%s%ssslmode=%s&sslcert=%s&sslkey=%s&sslrootcert=%s",
			userInfo, host, port, dbName, dbParam, dbSslMode, dbSslCert, dbSslKey, dbSslRootCert)
	}
	// Pin search_path so raw SQL resolves to the same schema as xorm-built statements (#3118).
	// Quoting preserves case; public stays so extension operators (e.g. ParadeDB's |||) keep resolving.
	if dbSchema != "" {
		searchPath := quoteIdentifier(dbSchema)
		if dbSchema != "public" {
			searchPath += ",public"
		}
		connStr += "&search_path=" + url.QueryEscape(searchPath)
	}
	if queryExecMode != "" {
		connStr += "&default_query_exec_mode=" + url.QueryEscape(queryExecMode)
	}
	return connStr
}

// Copied from github.com/lib/pq so that pq is not needed just for this.
func quoteIdentifier(name string) string {
	if end := strings.IndexRune(name, 0); end > -1 {
		name = name[:end]
	}
	return `"` + strings.ReplaceAll(name, `"`, `""`) + `"`
}

func initPostgresEngine() (engine *xorm.Engine, err error) {
	engine, err = newPostgresEngine("")
	if err != nil {
		return nil, err
	}

	// Ping first so an unreachable database still reports as a connection error
	// (#3287) instead of as a failed extension lookup.
	if err = engine.Ping(); err != nil {
		return nil, err
	}

	checkParadeDB(engine)
	checkUnaccent(engine)
	if !paradedbInstalled {
		return engine, nil
	}

	// ParadeDB's ||| operator needs the search term while the statement is planned,
	// which named prepared statements do not provide; unnamed ones are planned at
	// Bind. ParadeDB installations trade the statement cache for working search.
	// paradedb/paradedb#5907 fixed this for === only; revisit once ||| follows.
	if err = engine.Close(); err != nil {
		return nil, fmt.Errorf("could not close initial database connection: %w", err)
	}

	return newPostgresEngine("exec")
}

func newPostgresEngine(queryExecMode string) (engine *xorm.Engine, err error) {
	connStr := getPostgreSQLConnectionString(
		config.DatabaseHost.GetString(),
		config.DatabaseUser.GetString(),
		config.DatabasePassword.GetString(),
		config.DatabaseDatabase.GetString(),
		config.DatabaseSchema.GetString(),
		config.DatabaseSslMode.GetString(),
		config.DatabaseSslCert.GetString(),
		config.DatabaseSslKey.GetString(),
		config.DatabaseSslRootCert.GetString(),
		queryExecMode,
	)
	connStr += "&default_transaction_isolation=read%20committed"

	engine, err = xorm.NewEngine("pgx", connStr)
	if err != nil {
		return
	}
	engine.SetSchema(config.DatabaseSchema.GetString())
	engine.SetMaxOpenConns(config.DatabaseMaxOpenConnections.GetInt())
	engine.SetMaxIdleConns(config.DatabaseMaxIdleConnections.GetInt())
	maxLifetime, err := time.ParseDuration(strconv.Itoa(config.DatabaseMaxConnectionLifetime.GetInt()) + `ms`)
	if err != nil {
		return
	}
	engine.SetConnMaxLifetime(maxLifetime)

	return
}

// DatabasePathConfig holds configuration for database path resolution.
// This struct allows the path resolution logic to be tested independently
// of the global config package.
type DatabasePathConfig struct {
	ConfiguredPath string // The database.path config value
	RootPath       string // The service.rootpath config value
	ExecutablePath string // Directory of the executable binary
}

// resolveDatabasePath resolves a database path configuration to an absolute path.
//
// Resolution rules:
//  1. If ConfiguredPath is DatabasePathMemory, returns it (ephemeral database)
//  2. If ConfiguredPath is already absolute, returns it as-is (cleaned)
//  3. If ConfiguredPath is relative:
//     a. If RootPath differs from ExecutablePath (explicitly configured),
//     joins with RootPath
//     b. Otherwise, joins with the user data directory, falling back to RootPath
//     when userDataDir reports the directory as unavailable
//
// Directory creation only ever happens inside userDataDir, so an operator-configured
// path never gets its parents created behind their back.
func resolveDatabasePath(cfg DatabasePathConfig, userDataDir func() (string, error)) (string, error) {
	if cfg.ConfiguredPath == DatabasePathMemory {
		return DatabasePathMemory, nil
	}

	var path string

	switch {
	case filepath.IsAbs(cfg.ConfiguredPath):
		path = filepath.Clean(cfg.ConfiguredPath)
	case cfg.RootPath != cfg.ExecutablePath:
		path = filepath.Join(cfg.RootPath, cfg.ConfiguredPath)
	default:
		dataDir, err := userDataDir()
		if err != nil {
			log.Warningf("Could not get user data directory, falling back to rootpath: %v", err)
			path = filepath.Join(cfg.RootPath, cfg.ConfiguredPath)
		} else {
			path = filepath.Join(dataDir, cfg.ConfiguredPath)
		}
	}

	return filepath.Abs(path)
}

func databasePathConfig() DatabasePathConfig {
	rootPath := config.ServiceRootpath.GetString()

	executablePath := rootPath
	if execPath, err := os.Executable(); err == nil {
		executablePath = filepath.Dir(execPath)
	}

	return DatabasePathConfig{
		ConfiguredPath: config.DatabasePath.GetString(),
		RootPath:       rootPath,
		ExecutablePath: executablePath,
	}
}

// ResolvedDatabasePath returns the absolute path of the SQLite database file,
// or DatabasePathMemory for the ephemeral database. Only meaningful when
// database.type is sqlite. It creates nothing — see ensureDatabasePath.
func ResolvedDatabasePath() (string, error) {
	return resolveDatabasePath(databasePathConfig(), existingUserDataDir)
}

// ensureDatabasePath returns the path ResolvedDatabasePath reports once the user data
// directory exists, creating that directory if it is the one selected.
func ensureDatabasePath() (string, error) {
	return resolveDatabasePath(databasePathConfig(), createdUserDataDir)
}

func initSqliteEngine() (engine *xorm.Engine, err error) {
	path, err := ensureDatabasePath()
	if err != nil {
		return nil, fmt.Errorf("could not prepare database path: %w", err)
	}

	if path == DatabasePathMemory {
		// Use a temp file with WAL mode instead of in-memory shared cache.
		// Shared cache (file::memory:?cache=shared) uses table-level locking
		// where _busy_timeout is ineffective (returns SQLITE_LOCKED, not
		// SQLITE_BUSY) and concurrent connections deadlock. A temp file with
		// WAL mode provides proper concurrency: readers never block writers,
		// and _busy_timeout handles write-write contention.
		tmpDir, mkErr := os.MkdirTemp("", "vikunja-*")
		if mkErr != nil {
			return nil, fmt.Errorf("could not create temp directory for ephemeral database: %w", mkErr)
		}
		dbPath := filepath.Join(tmpDir, "vikunja.db")
		engine, err = xorm.NewEngine("sqlite3", dbPath+"?_busy_timeout=5000&_journal_mode=WAL")
		if err != nil {
			return
		}
		log.Infof("Using ephemeral SQLite database at: %s", dbPath)
		return
	}

	// Log the resolved database path
	log.Infof("Using SQLite database at: %s", path)

	// Warn if the database is in a potentially problematic location
	if isSystemDirectory(path) {
		log.Warningf("Database path (%s) appears to be in a system directory. This may cause issues. Please use an absolute path or configure the database path to a user data directory.", path)
	}

	// Try opening the db file to return a better error message if that does not work
	var exists = true
	if _, err := os.Stat(path); err != nil {
		exists = !os.IsNotExist(err)
	}
	file, err := os.OpenFile(path, os.O_RDWR|os.O_CREATE, 0)
	if err != nil {
		return nil, fmt.Errorf("could not open database file [uid=%d, gid=%d]: %w", os.Getuid(), os.Getgid(), err)
	}
	_ = file.Close() // We directly close the file because we only want to check if it is writable. It will be reopened lazily later by xorm.

	if !exists {
		_ = os.Remove(path) // Remove the file to not prevent the db from creating another one
	}

	// WAL mode allows concurrent readers alongside a single writer without
	// blocking each other. busy_timeout makes concurrent writers wait (up to
	// 5 s) instead of failing immediately with SQLITE_BUSY.
	engine, err = xorm.NewEngine("sqlite3", path+"?_busy_timeout=5000&_journal_mode=WAL")
	return
}

// existingUserDataDir returns the user data directory only when it is already there.
// Treating an absent directory as unavailable keeps resolution free of side effects
// while still reporting the path ensureDatabasePath uses, once it has run.
func existingUserDataDir() (string, error) {
	dir, err := resolveUserDataDir()
	if err != nil {
		return "", err
	}

	info, err := os.Stat(dir)
	if err != nil {
		return "", fmt.Errorf("could not stat data directory %s: %w", dir, err)
	}
	if !info.IsDir() {
		return "", fmt.Errorf("data directory %s is not a directory", dir)
	}

	return dir, nil
}

// createdUserDataDir creates the user data directory. Creation lives here so a failure
// surfaces as the rootpath fallback instead of a hard startup error.
func createdUserDataDir() (string, error) {
	dir, err := resolveUserDataDir()
	if err != nil {
		return "", err
	}

	if err := os.MkdirAll(dir, 0o700); err != nil { // #nosec G703 -- dir is from XDG standard paths
		return "", fmt.Errorf("could not create data directory %s: %w", dir, err)
	}

	return dir, nil
}

func resolveUserDataDir() (string, error) {
	var dataDir string

	switch runtime.GOOS {
	case "windows":
		// On Windows, use %LOCALAPPDATA%\Vikunja
		localAppData := os.Getenv("LOCALAPPDATA")
		if localAppData == "" {
			// Fallback to %USERPROFILE%\AppData\Local if LOCALAPPDATA is not set
			userProfile := os.Getenv("USERPROFILE")
			if userProfile == "" {
				return "", fmt.Errorf("neither LOCALAPPDATA nor USERPROFILE environment variables are set")
			}
			localAppData = filepath.Join(userProfile, "AppData", "Local")
		}
		dataDir = filepath.Join(localAppData, "Vikunja")
	case "darwin":
		// On macOS, use ~/Library/Application Support/Vikunja
		home, err := os.UserHomeDir()
		if err != nil {
			return "", err
		}
		dataDir = filepath.Join(home, "Library", "Application Support", "Vikunja")
	default:
		// On Linux and other Unix-like systems, use XDG_DATA_HOME or ~/.local/share/vikunja
		xdgDataHome := os.Getenv("XDG_DATA_HOME")
		if xdgDataHome != "" {
			dataDir = filepath.Join(xdgDataHome, "vikunja")
		} else {
			home, err := os.UserHomeDir()
			if err != nil {
				return "", err
			}
			dataDir = filepath.Join(home, ".local", "share", "vikunja")
		}
	}

	return dataDir, nil
}

// isSystemDirectory checks if a path appears to be in a system directory
// where users should not typically store application data
func isSystemDirectory(path string) bool {
	// Clean and normalize the path
	path = filepath.Clean(path)
	lowerPath := strings.ToLower(path)

	// Windows system directories
	if runtime.GOOS == "windows" {
		// Convert to absolute path if possible for more accurate checking
		absPath := lowerPath
		if abs, err := filepath.Abs(path); err == nil {
			absPath = strings.ToLower(filepath.Clean(abs))
		}

		// Check common Windows system directories using prefix matching
		// This prevents false positives like C:\myapp\windows\data
		windowsSystemPrefixes := []string{
			"c:\\windows\\system32",
			"c:\\windows\\syswow64",
			"c:\\windows\\winsxs",
			"c:\\windows\\servicing",
		}

		for _, prefix := range windowsSystemPrefixes {
			if strings.HasPrefix(absPath, prefix) {
				return true
			}
		}

		// Also check for direct C:\Windows (not subdirectories like C:\myapp\windows)
		// by ensuring it starts with the drive and windows directory
		if absPath == "c:\\windows" || strings.HasPrefix(absPath, "c:\\windows\\") {
			// Exclude some safe subdirectories under C:\Windows
			safeDirs := []string{
				"c:\\windows\\temp",
			}
			for _, safeDir := range safeDirs {
				if strings.HasPrefix(absPath, safeDir) {
					return false
				}
			}
			return true
		}
	}

	// Unix-like system directories - use prefix matching
	systemDirs := []string{
		"/bin", "/sbin", "/usr/bin", "/usr/sbin",
		"/etc", "/sys", "/proc", "/dev",
	}
	for _, sysDir := range systemDirs {
		// Ensure we match exact directory boundaries
		if lowerPath == sysDir || strings.HasPrefix(lowerPath, sysDir+"/") {
			return true
		}
	}

	return false
}

// WipeEverything wipes all Vikunja tables and their data. Use with caution...
func WipeEverything() error {
	for _, name := range RegisteredTableNames() {
		if err := x.DropTables(name); err != nil {
			return err
		}
	}

	return nil
}

// NewSession creates an active transaction (READ COMMITTED on MySQL and PostgreSQL).
// The caller must call s.Commit() on success or s.Rollback() on error.
// s.Close() will auto-rollback any uncommitted transaction.
func NewSession() *xorm.Session {
	s := x.NewSession()
	attachSessionCache(s)
	if err := s.Begin(); err != nil {
		log.Fatalf("Failed to begin database transaction: %s", err)
	}
	return s
}

// NewAutocommitSession has no transaction: writes are durable immediately and Commit/Rollback are no-ops.
func NewAutocommitSession() *xorm.Session {
	s := x.NewSession()
	attachSessionCache(s)
	return s
}

// NewReadSession creates a session without a also starting a transaction, so that
// read only statements don't block a pool connection on statements that can be parallelized.
func NewReadSession() *xorm.Session {
	return NewAutocommitSession()
}

// Type returns the db type of the currently configured db
func Type() schemas.DBType {
	return x.Dialect().URI().DBType
}

// RegisterConnectionPoolMetrics exposes pool stats so exhaustion is visible before
// the instance stops responding. Label is the db type only — never a path or name.
func RegisterConnectionPoolMetrics(registry *prometheus.Registry) {
	if x == nil {
		log.Warningf("Database not initialized, skipping connection pool metrics")
		return
	}

	err := registry.Register(collectors.NewDBStatsCollector(x.DB().DB, string(Type())))
	if err != nil {
		log.Criticalf("Could not register db stats metrics: %s", err)
	}
}

func GetDialect() string {
	switch config.DatabaseType.GetString() {
	case "mysql":
		return builder.MYSQL
	case "postgres":
		return builder.POSTGRES
	default:
		return builder.SQLITE
	}
}

func checkParadeDB(engine *xorm.Engine) {
	if engine.Dialect().URI().DBType != schemas.POSTGRES {
		return
	}

	exists := false
	if _, err := engine.SQL("SELECT EXISTS (SELECT 1 FROM pg_extension WHERE extname='pg_search')").Get(&exists); err != nil {
		log.Errorf("could not check for paradedb extension: %v", err)
		return
	}

	if !exists {
		return
	}

	paradedbInstalled = true
	log.Debug("ParadeDB extension detected, using @@@ search operator")
}

// checkUnaccent calls unaccent() instead of querying pg_extension so an
// extension outside the search_path counts as unavailable.
func checkUnaccent(engine *xorm.Engine) {
	if engine.Dialect().URI().DBType != schemas.POSTGRES {
		return
	}

	var result string
	if _, err := engine.SQL("SELECT unaccent('a')").Get(&result); err != nil {
		log.Debugf("unaccent extension not available, search stays accent-sensitive: %v", err)
		return
	}

	unaccentAvailable = true
	log.Info("unaccent extension detected, search is accent-insensitive")
}

func CreateParadeDBIndexes() error {
	if !paradedbInstalled {
		return nil
	}
	// ParadeDB only allows one bm25 index per table, so we create a single index covering both fields.
	// Fast fields speed up scoring; no field boosting is configured, title and description weigh the same.
	indexSQL := `CREATE INDEX IF NOT EXISTS idx_tasks_paradedb ON tasks USING bm25 (id, title, description, project_id, done) 
	WITH (
		key_field='id',
		text_fields='{
			"title": {"fast": true, "record": "freq"}, 
			"description": {"fast": true, "record": "freq"}
		}',
		numeric_fields='{
			"project_id": {"fast": true}
		}',
		boolean_fields='{
			"done": {"fast": true}
		}'
	)`
	if _, err := x.Exec(indexSQL); err != nil {
		return fmt.Errorf("could not ensure paradedb task index: %w", err)
	}

	// Create ParadeDB index for projects table
	projectIndexSQL := `CREATE INDEX IF NOT EXISTS idx_projects_paradedb ON projects USING bm25 (id, title, description, identifier) 
	WITH (
		key_field='id',
		text_fields='{
			"title": {"fast": true, "record": "freq"}, 
			"description": {"fast": true, "record": "freq"},
			"identifier": {"fast": true, "record": "freq"}
		}'
	)`
	if _, err := x.Exec(projectIndexSQL); err != nil {
		return fmt.Errorf("could not ensure paradedb project index: %w", err)
	}

	// Create ParadeDB index for time_entries (comment search via MultiFieldSearch)
	timeEntriesIndexSQL := `CREATE INDEX IF NOT EXISTS idx_time_entries_paradedb ON time_entries USING bm25 (id, comment)
	WITH (
		key_field='id',
		text_fields='{
			"comment": {"fast": true, "record": "freq"}
		}'
	)`
	if _, err := x.Exec(timeEntriesIndexSQL); err != nil {
		return fmt.Errorf("could not ensure paradedb time entry index: %w", err)
	}

	// Create ParadeDB index for labels (title/description search via MultiFieldSearch)
	labelsIndexSQL := `CREATE INDEX IF NOT EXISTS idx_labels_paradedb ON labels USING bm25 (id, title, description)
	WITH (
		key_field='id',
		text_fields='{
			"title": {"fast": true, "record": "freq"},
			"description": {"fast": true, "record": "freq"}
		}'
	)`
	if _, err := x.Exec(labelsIndexSQL); err != nil {
		return fmt.Errorf("could not ensure paradedb label index: %w", err)
	}

	return nil
}