tools/ refactor

quietlight

May 4, 2026, 11:11 PM

AVQ66WO4R4KVXAVP4YPEF65CPHJJY55H7ZOVPZ2BHFMGEBTWRUQQC

Dependencies

[2] XO5DF6WR I tiedied up readme
[3] T2WZBTVF cyclo 22
[4] E27ZWCDP cyclo over 18
[5] WKQ7LFTP refactor of utils/
[6] RUVJ3V4N cyclo to 14 now
[7] KLUEQ6X5 cyclo 21+
[8] GPQSOVBP cyclo complexity over 25
[9] LQLC7S3A trying gemini: Inconsistent Standards in @utils/ refactoring
[10] 5FUZQJW5 readme
[11] KZKLAINJ run out of space on nest, cleaned out
[12] 2P27XV3D fixed cyclo over 30
[13] JAT3DXOL cyclo over 15

Change contents

replacement in tools/sql.go at line 76

[6.261553]→[3.2248:2388](∅→∅),[3.2388]→[6.261803:261806](∅→∅),[6.261803]→[6.261803:261806](∅→∅),[6.261806]→[3.2389:2413](∅→∅)

database, err := db.OpenReadOnlyDB(dbPath)
if err != nil {
return ExecuteSQLOutput{}, fmt.Errorf("database connection failed: %w", err)
}
defer database.Close()

[6.261553]

[6.261806]

var output ExecuteSQLOutput
err := db.WithReadDB(dbPath, func(database *sql.DB) error {
rows, rerr := executeSQLQuery(ctx, database, query, input.Parameters)
if rerr != nil {
return rerr
}
defer rows.Close()
replacement in tools/sql.go at line 84

[6.261807]→[3.2414:2534](∅→∅),[3.2534]→[6.262042:262045](∅→∅),[6.262042]→[6.262042:262045](∅→∅),[6.262045]→[3.2535:2555](∅→∅)

rows, err := executeSQLQuery(ctx, database, query, input.Parameters)
if err != nil {
return ExecuteSQLOutput{}, err
}
defer rows.Close()

[6.261807]

[6.262045]

columnInfo, columns, cerr := buildColumnInfo(rows)
if cerr != nil {
return cerr
}
replacement in tools/sql.go at line 89

[6.262046]→[3.2556:2657](∅→∅),[3.2657]→[6.262312:262315](∅→∅),[6.262312]→[6.262312:262315](∅→∅)

columnInfo, columns, err := buildColumnInfo(rows)
if err != nil {
return ExecuteSQLOutput{}, err
}

[6.262046]

[6.262315]

results, serr := scanResultRows(rows, columns)
if serr != nil {
return serr
}
replacement in tools/sql.go at line 94

[6.262316]→[3.2658:2705](∅→∅),[3.2705]→[6.262413:262430](∅→∅),[6.262413]→[6.262413:262430](∅→∅),[6.262430]→[3.2706:2739](∅→∅),[3.2739]→[6.262509:262512](∅→∅),[6.262509]→[6.262509:262512](∅→∅)

results, err := scanResultRows(rows, columns)
if err != nil {
return ExecuteSQLOutput{}, err
}

[6.262316]

[6.262562]

// Handle empty results (return empty array, not error)
if results == nil {
results = []map[string]any{}
}
replacement in tools/sql.go at line 99

[6.262563]→[3.2740:2849](∅→∅),[3.2849]→[6.262776:262779](∅→∅),[6.262776]→[6.262776:262779](∅→∅),[6.262779]→[3.2850:3036](∅→∅),[3.3036]→[6.262871:262874](∅→∅),[6.262871]→[6.262871:262874](∅→∅)

// Handle empty results (return empty array, not error)
if results == nil {
results = []map[string]any{}
}

// Detect truncation: if we auto-added limit+1 and got more than limit rows
limited := false
if autoAddedLimit && len(results) > limit {
limited = true
results = results[:limit]
}

[6.262563]

[3.3037]

// Detect truncation: if we auto-added limit+1 and got more than limit rows
limited := false
if autoAddedLimit && len(results) > limit {
limited = true
results = results[:limit]
}
replacement in tools/sql.go at line 106

[3.3038]→[3.3038:3111](∅→∅)

queryReported := buildQueryReported(input.Query, autoAddedLimit, limit)

[3.3038]

[3.3111]

queryReported := buildQueryReported(input.Query, autoAddedLimit, limit)
replacement in tools/sql.go at line 108

[3.3112]→[3.3112:3265](∅→∅)

return ExecuteSQLOutput{
Rows: results,
RowCount: len(results),
Columns: columnInfo,
Limited: limited,
Query: queryReported,
}, nil

[3.3112]

[3.3265]

output = ExecuteSQLOutput{
Rows: results,
RowCount: len(results),
Columns: columnInfo,
Limited: limited,
Query: queryReported,
}
return nil
})
return output, err
edit in tools/pattern.go at line 99

[6.282256]→[6.282256:282427](∅→∅)

}

// Open writable database connection
database, err := db.OpenWriteableDB(dbPath)
if err != nil {
return output, fmt.Errorf("database connection failed: %w", err)
edit in tools/pattern.go at line 100

[6.282430]→[6.282430:282454](∅→∅)

defer database.Close()
replacement in tools/pattern.go at line 101

[6.282455]→[6.282455:282695](∅→∅)

// Begin logged transaction
tx, err := db.BeginLoggedTx(ctx, database, "create_or_update_pattern")
if err != nil {
return output, fmt.Errorf("failed to begin transaction: %w", err)
}
defer func() {
if err != nil {
tx.Rollback()

[6.282455]

[6.282695]

err := db.WithWriteTx(ctx, dbPath, "create_or_update_pattern", func(database *sql.DB, tx *db.LoggedTx) error {
// Check if pattern with same record_s/sleep_s already exists
existing, found, ferr := findExistingPattern(ctx, tx, *input.RecordSeconds, *input.SleepSeconds)
if ferr != nil {
return fmt.Errorf("failed to check for existing pattern: %w", ferr)
replacement in tools/pattern.go at line 107

[6.282699]→[6.282699:282768](∅→∅),[6.282768]→[6.7606:7816](∅→∅),[6.7816]→[6.283487:283594](∅→∅),[6.283487]→[6.283487:283594](∅→∅)

}()

// Check if pattern with same record_s/sleep_s already exists
if existing, found, err := findExistingPattern(ctx, tx, *input.RecordSeconds, *input.SleepSeconds); err != nil {
return output, fmt.Errorf("failed to check for existing pattern: %w", err)
} else if found {
if err = tx.Commit(); err != nil {
return output, fmt.Errorf("failed to commit transaction: %w", err)

[6.282699]

[6.283594]

if found {
output.Pattern = existing
output.Message = fmt.Sprintf("Pattern already exists with ID %s (record %ds, sleep %ds) - returning existing pattern",
existing.ID, existing.RecordS, existing.SleepS)
return nil // commit transaction
edit in tools/pattern.go at line 113

[6.283598]→[6.7817:7845](∅→∅),[6.7845]→[6.283626:283747](∅→∅),[6.283626]→[6.283626:283747](∅→∅),[6.283747]→[6.7846:7897](∅→∅),[6.7897]→[6.283796:283817](∅→∅),[6.283796]→[6.283796:283817](∅→∅),[6.283928]→[6.283928:284415](∅→∅)

output.Pattern = existing
output.Message = fmt.Sprintf("Pattern already exists with ID %s (record %ds, sleep %ds) - returning existing pattern",
existing.ID, existing.RecordS, existing.SleepS)
return output, nil
}

// Generate ID
id, err := utils.GenerateShortID()
if err != nil {
return output, fmt.Errorf("failed to generate ID: %w", err)
}

// Insert pattern
_, err = tx.ExecContext(ctx,
"INSERT INTO cyclic_recording_pattern (id, record_s, sleep_s, created_at, last_modified, active) VALUES (?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, TRUE)",
id, *input.RecordSeconds, *input.SleepSeconds,
)
if err != nil {
return output, fmt.Errorf("failed to create pattern: %w", err)
}
replacement in tools/pattern.go at line 114

[6.284416]→[6.284416:284842](∅→∅)

// Fetch the created pattern
var pattern db.CyclicRecordingPattern
err = tx.QueryRowContext(ctx,
"SELECT id, record_s, sleep_s, created_at, last_modified, active FROM cyclic_recording_pattern WHERE id = ?",
id,
).Scan(&pattern.ID, &pattern.RecordS, &pattern.SleepS, &pattern.CreatedAt, &pattern.LastModified, &pattern.Active)
if err != nil {
return output, fmt.Errorf("failed to fetch created pattern: %w", err)
}

[6.284416]

[6.284842]

// Generate ID
id, gerr := utils.GenerateShortID()
if gerr != nil {
return fmt.Errorf("failed to generate ID: %w", gerr)
}
replacement in tools/pattern.go at line 120

[6.284843]→[6.284843:284951](∅→∅)

if err = tx.Commit(); err != nil {
return output, fmt.Errorf("failed to commit transaction: %w", err)
}

[6.284843]

[6.284951]

// Insert pattern
if _, err := tx.ExecContext(ctx,
"INSERT INTO cyclic_recording_pattern (id, record_s, sleep_s, created_at, last_modified, active) VALUES (?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, TRUE)",
id, *input.RecordSeconds, *input.SleepSeconds,
); err != nil {
return fmt.Errorf("failed to create pattern: %w", err)
}
replacement in tools/pattern.go at line 128

[6.284952]→[6.284952:285139](∅→∅)

output.Pattern = pattern
output.Message = fmt.Sprintf("Successfully created cyclic recording pattern with ID %s (record %ds, sleep %ds)",
pattern.ID, pattern.RecordS, pattern.SleepS)

[6.284952]

[6.285139]

// Fetch the created pattern
var pattern db.CyclicRecordingPattern
if err := tx.QueryRowContext(ctx,
"SELECT id, record_s, sleep_s, created_at, last_modified, active FROM cyclic_recording_pattern WHERE id = ?",
id,
).Scan(&pattern.ID, &pattern.RecordS, &pattern.SleepS, &pattern.CreatedAt, &pattern.LastModified, &pattern.Active); err != nil {
return fmt.Errorf("failed to fetch created pattern: %w", err)
}
replacement in tools/pattern.go at line 137

[6.285140]→[6.285140:285160](∅→∅)

return output, nil

[6.285140]

[6.285160]

output.Pattern = pattern
output.Message = fmt.Sprintf("Successfully created cyclic recording pattern with ID %s (record %ds, sleep %ds)",
pattern.ID, pattern.RecordS, pattern.SleepS)
return nil
})
return output, err
replacement in tools/pattern.go at line 145

[6.285163]→[6.285163:285274](∅→∅),[6.285298]→[6.285298:285299](∅→∅),[6.285299]→[6.7898:7957](∅→∅),[6.7957]→[6.285394:285419](∅→∅),[6.285394]→[6.285394:285419](∅→∅),[6.285754]→[6.285754:285985](∅→∅)

func updatePattern(ctx context.Context, input PatternInput) (PatternOutput, error) {
var output PatternOutput

if err := validateUpdatePatternInput(input); err != nil {
return output, err
}

// Open writable database
database, err := db.OpenWriteableDB(dbPath)
if err != nil {
return output, fmt.Errorf("failed to open database: %w", err)
}
defer database.Close()

// Verify pattern exists and check active status

[6.285163]

[6.285985]

// verifyPatternExistsAndActive checks that a pattern exists and is active.
func verifyPatternExistsAndActive(database *sql.DB, patternID string) error {
replacement in tools/pattern.go at line 148

[6.286010]→[6.286010:286036](∅→∅)

err = database.QueryRow(

[6.286010]

[6.286036]

err := database.QueryRow(
replacement in tools/pattern.go at line 150

[6.286185]→[6.7958:7982](∅→∅)

*input.ID, *input.ID,

[6.286185]

[6.286209]

patternID, patternID,
replacement in tools/pattern.go at line 153

[6.286252]→[6.286252:286316](∅→∅)

return output, fmt.Errorf("failed to query pattern: %w", err)

[6.286252]

[6.286316]

return fmt.Errorf("failed to query pattern: %w", err)
replacement in tools/pattern.go at line 156

[6.286333]→[6.7983:8047](∅→∅)

return output, fmt.Errorf("pattern not found: %s", *input.ID)

[6.286333]

[6.286397]

return fmt.Errorf("pattern not found: %s", patternID)
replacement in tools/pattern.go at line 159

[6.286414]→[6.8048:8151](∅→∅)

return output, fmt.Errorf("pattern '%s' is not active (cannot update inactive patterns)", *input.ID)

[6.286414]

[6.286517]

return fmt.Errorf("pattern '%s' is not active (cannot update inactive patterns)", patternID)
edit in tools/pattern.go at line 161

[6.286520]

[6.286520]

return nil
}
replacement in tools/pattern.go at line 164

[6.286521]→[6.286521:286552](∅→∅)

// Build dynamic UPDATE query

[6.286521]

[6.286552]

// buildPatternUpdateQuery builds the dynamic UPDATE query and args for pattern fields.
func buildPatternUpdateQuery(input PatternInput) (string, []any, error) {
replacement in tools/pattern.go at line 179

[6.286863]→[6.286863:286923](∅→∅)

return output, fmt.Errorf("no fields provided to update")

[6.286863]

[6.286923]

return "", nil, fmt.Errorf("no fields provided to update")
edit in tools/pattern.go at line 182

[6.286927]→[6.286927:286959](∅→∅)

// Always update last_modified
edit in tools/pattern.go at line 186

[6.287150]

[6.287150]

return query, args, nil
}
replacement in tools/pattern.go at line 189

[6.287151]→[6.287151:287348](∅→∅)

// Begin logged transaction for update
tx, err := db.BeginLoggedTx(ctx, database, "create_or_update_pattern")
if err != nil {
return output, fmt.Errorf("failed to begin transaction: %w", err)

[6.287151]

[6.287348]

func updatePattern(ctx context.Context, input PatternInput) (PatternOutput, error) {
var output PatternOutput

if err := validateUpdatePatternInput(input); err != nil {
return output, err
replacement in tools/pattern.go at line 195

[6.287351]→[6.287351:287402](∅→∅)

defer func() {
if err != nil {
tx.Rollback()

[6.287351]

[6.287402]

err := db.WithWriteTx(ctx, dbPath, "create_or_update_pattern", func(database *sql.DB, tx *db.LoggedTx) error {
if err := verifyPatternExistsAndActive(database, *input.ID); err != nil {
return err
edit in tools/pattern.go at line 200

[6.287406]→[6.287406:287411](∅→∅)

}()
replacement in tools/pattern.go at line 201

[6.287412]→[6.287412:287531](∅→∅)

_, err = tx.Exec(query, args...)
if err != nil {
return output, fmt.Errorf("failed to update pattern: %w", err)
}

[6.287412]

[6.287531]

query, args, qerr := buildPatternUpdateQuery(input)
if qerr != nil {
return qerr
}
replacement in tools/pattern.go at line 206

[6.287532]→[6.287532:287733](∅→∅),[6.287733]→[6.8185:8198](∅→∅),[6.8198]→[6.287746:287954](∅→∅),[6.287746]→[6.287746:287954](∅→∅)

// Fetch the updated pattern
var pattern db.CyclicRecordingPattern
err = tx.QueryRow(
"SELECT id, record_s, sleep_s, created_at, last_modified, active FROM cyclic_recording_pattern WHERE id = ?",
*input.ID,
).Scan(&pattern.ID, &pattern.RecordS, &pattern.SleepS, &pattern.CreatedAt, &pattern.LastModified, &pattern.Active)
if err != nil {
return output, fmt.Errorf("failed to fetch updated pattern: %w", err)
}

[6.287532]

[6.287954]

if _, err := tx.Exec(query, args...); err != nil {
return fmt.Errorf("failed to update pattern: %w", err)
}
replacement in tools/pattern.go at line 210

[6.287955]→[6.287955:288063](∅→∅)

if err = tx.Commit(); err != nil {
return output, fmt.Errorf("failed to commit transaction: %w", err)
}

[6.287955]

[6.288063]

// Fetch the updated pattern
var pattern db.CyclicRecordingPattern
if err := tx.QueryRow(
"SELECT id, record_s, sleep_s, created_at, last_modified, active FROM cyclic_recording_pattern WHERE id = ?",
*input.ID,
).Scan(&pattern.ID, &pattern.RecordS, &pattern.SleepS, &pattern.CreatedAt, &pattern.LastModified, &pattern.Active); err != nil {
return fmt.Errorf("failed to fetch updated pattern: %w", err)
}
replacement in tools/pattern.go at line 219

[6.288064]→[6.288064:288252](∅→∅)

output.Pattern = pattern
output.Message = fmt.Sprintf("Successfully updated pattern (ID: %s, record %ds, sleep %ds)",
pattern.ID, pattern.RecordS, pattern.SleepS)

return output, nil

[6.288064]

[6.288252]

output.Pattern = pattern
output.Message = fmt.Sprintf("Successfully updated pattern (ID: %s, record %ds, sleep %ds)",
pattern.ID, pattern.RecordS, pattern.SleepS)
return nil
})
return output, err
replacement in tools/location.go at line 112

[6.291309]→[6.291309:291745](∅→∅)

// Open writable database connection
database, err := db.OpenWriteableDB(dbPath)
if err != nil {
return output, fmt.Errorf("database connection failed: %w", err)
}
defer database.Close()

// Begin logged transaction
tx, err := db.BeginLoggedTx(ctx, database, "create_or_update_location")
if err != nil {
return output, fmt.Errorf("failed to begin transaction: %w", err)
}
defer func() {
if err != nil {
tx.Rollback()

[6.291309]

[6.291745]

err := db.WithWriteTx(ctx, dbPath, "create_or_update_location", func(database *sql.DB, tx *db.LoggedTx) error {
if err := verifyDatasetExistsAndActive(ctx, tx, *input.DatasetID); err != nil {
return err
edit in tools/location.go at line 116

[6.291749]→[6.291749:291754](∅→∅)

}()
replacement in tools/location.go at line 117

[6.291755]→[3.7039:7141](∅→∅),[3.7141]→[6.292249:292252](∅→∅),[6.292249]→[6.292249:292252](∅→∅),[6.292356]→[6.292356:292642](∅→∅),[6.292642]→[3.7142:7203](∅→∅),[3.7203]→[6.293577:293580](∅→∅),[6.293577]→[6.293577:293580](∅→∅)

if err := verifyDatasetExistsAndActive(ctx, tx, *input.DatasetID); err != nil {
return output, err
}

// Check for existing location with same name in dataset (UNIQUE constraint)
var existingID string
err = tx.QueryRowContext(ctx,
"SELECT id FROM location WHERE dataset_id = ? AND name = ? AND active = true",
*input.DatasetID, *input.Name,
).Scan(&existingID)

if err == nil {
return returnExistingLocation(ctx, tx, existingID, output)
}

[6.291755]

[6.293580]

// Check for existing location with same name in dataset (UNIQUE constraint)
var existingID string
qerr := tx.QueryRowContext(ctx,
"SELECT id FROM location WHERE dataset_id = ? AND name = ? AND active = true",
*input.DatasetID, *input.Name,
).Scan(&existingID)
replacement in tools/location.go at line 124

[6.293581]→[6.293581:293715](∅→∅)

// Generate ID
id, err := utils.GenerateShortID()
if err != nil {
return output, fmt.Errorf("failed to generate ID: %w", err)
}

[6.293581]

[6.293715]

if qerr == nil {
result, rerr := returnExistingLocation(ctx, tx, existingID)
if rerr != nil {
return rerr
}
output = result
return nil
}
replacement in tools/location.go at line 133

[6.293716]→[6.293716:294169](∅→∅)

// Insert location
_, err = tx.ExecContext(ctx,
"INSERT INTO location (id, dataset_id, name, latitude, longitude, timezone_id, description, created_at, last_modified, active) VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, TRUE)",
id, *input.DatasetID, *input.Name, *input.Latitude, *input.Longitude, *input.TimezoneID, input.Description,
)
if err != nil {
return output, fmt.Errorf("failed to create location: %w", err)
}

[6.293716]

[6.294169]

// Generate ID
id, gerr := utils.GenerateShortID()
if gerr != nil {
return fmt.Errorf("failed to generate ID: %w", gerr)
}
replacement in tools/location.go at line 139

[6.294170]→[6.294170:294201](∅→∅),[6.294201]→[3.7204:7253](∅→∅),[3.7253]→[6.294616:294709](∅→∅),[6.294616]→[6.294616:294709](∅→∅)

// Fetch the created location
location, err := fetchLocationByID(ctx, tx, id)
if err != nil {
return output, fmt.Errorf("failed to fetch created location: %w", err)
}

[6.294170]

[6.294709]

// Insert location
if _, err := tx.ExecContext(ctx,
"INSERT INTO location (id, dataset_id, name, latitude, longitude, timezone_id, description, created_at, last_modified, active) VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, TRUE)",
id, *input.DatasetID, *input.Name, *input.Latitude, *input.Longitude, *input.TimezoneID, input.Description,
); err != nil {
return fmt.Errorf("failed to create location: %w", err)
}
replacement in tools/location.go at line 147

[6.294710]→[6.294710:294818](∅→∅)

if err = tx.Commit(); err != nil {
return output, fmt.Errorf("failed to commit transaction: %w", err)
}

[6.294710]

[6.294818]

// Fetch the created location
location, ferr := fetchLocationByID(ctx, tx, id)
if ferr != nil {
return fmt.Errorf("failed to fetch created location: %w", ferr)
}
replacement in tools/location.go at line 153

[6.294819]→[6.294819:295033](∅→∅),[6.295033]→[3.7254:7274](∅→∅)

output.Location = location
output.Message = fmt.Sprintf("Successfully created location '%s' with ID %s (%.6f, %.6f, %s)",
location.Name, location.ID, location.Latitude, location.Longitude, location.TimezoneID)
return output, nil

[6.294819]

[3.7274]

output.Location = location
output.Message = fmt.Sprintf("Successfully created location '%s' with ID %s (%.6f, %.6f, %s)",
location.Name, location.ID, location.Latitude, location.Longitude, location.TimezoneID)
return nil
})
return output, err
replacement in tools/location.go at line 162

[3.7368]→[3.7368:7502](∅→∅)

func returnExistingLocation(ctx context.Context, tx *db.LoggedTx, existingID string, output LocationOutput) (LocationOutput, error) {

[3.7368]

[3.7502]

// Caller is responsible for committing the transaction.
func returnExistingLocation(ctx context.Context, tx *db.LoggedTx, existingID string) (LocationOutput, error) {
var output LocationOutput
edit in tools/location.go at line 168

[3.7650]→[3.7650:7758](∅→∅)

}
if err = tx.Commit(); err != nil {
return output, fmt.Errorf("failed to commit transaction: %w", err)
replacement in tools/location.go at line 175

[3.8006]→[3.8006:8654](∅→∅)

func verifyDatasetExistsAndActive(ctx context.Context, queryer interface {
QueryRowContext(context.Context, string, ...any) *sql.Row
}, datasetID string) error {
var exists, active bool
err := queryer.QueryRowContext(ctx,
"SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ?), COALESCE((SELECT active FROM dataset WHERE id = ?), false)",
datasetID, datasetID,
).Scan(&exists, &active)
if err != nil {
return fmt.Errorf("failed to verify dataset: %w", err)
}
if !exists {
return fmt.Errorf("dataset with ID '%s' does not exist", datasetID)
}
if !active {
return fmt.Errorf("dataset (ID: %s) is not active", datasetID)
}
return nil

[3.8006]

[3.8654]

func verifyDatasetExistsAndActive(ctx context.Context, q db.Querier, datasetID string) error {
_, err := db.DatasetExistsAndActive(q, datasetID)
return err
replacement in tools/location.go at line 198

[6.295554]→[6.295554:295734](∅→∅)

// Open writable database
database, err := db.OpenWriteableDB(dbPath)
if err != nil {
return output, fmt.Errorf("failed to open database: %w", err)
}
defer database.Close()

[6.295554]

[6.295734]

err := db.WithWriteTx(ctx, dbPath, "create_or_update_location", func(database *sql.DB, tx *db.LoggedTx) error {
if err := verifyLocationExistsAndActive(database, locationID); err != nil {
return err
}
replacement in tools/location.go at line 203

[6.295735]→[3.8658:8756](∅→∅),[3.8756]→[6.296416:296419](∅→∅),[6.296416]→[6.296416:296419](∅→∅)

if err := verifyLocationExistsAndActive(database, locationID); err != nil {
return output, err
}

[6.295735]

[6.296419]

// Verify dataset exists if DatasetID provided (relationship consistency)
if input.DatasetID != nil {
if err := verifyDatasetExistsAndActive(ctx, database, *input.DatasetID); err != nil {
return err
}
}
replacement in tools/location.go at line 210

[6.296420]→[6.296420:296524](∅→∅),[6.296524]→[3.8757:8884](∅→∅)

// Verify dataset exists if DatasetID provided (relationship consistency)
if input.DatasetID != nil {
if err := verifyDatasetExistsAndActive(context.Background(), database, *input.DatasetID); err != nil {
return output, err

[6.296420]

[3.8884]

updates, args, uerr := buildLocationUpdates(input, locationID)
if uerr != nil {
return uerr
replacement in tools/location.go at line 214

[3.8888]→[3.8888:9086](∅→∅)

}

updates, args, err := buildLocationUpdates(input, locationID)
if err != nil {
return output, err
}
query := fmt.Sprintf("UPDATE location SET %s WHERE id = ?", strings.Join(updates, ", "))

[3.8888]

[3.9086]

query := fmt.Sprintf("UPDATE location SET %s WHERE id = ?", strings.Join(updates, ", "))
replacement in tools/location.go at line 216

[3.9087]→[3.9087:9304](∅→∅),[3.9304]→[6.296787:296805](∅→∅),[6.296787]→[6.296787:296805](∅→∅),[6.296805]→[3.9305:9322](∅→∅)

// Begin logged transaction for update
tx, err := db.BeginLoggedTx(ctx, database, "create_or_update_location")
if err != nil {
return output, fmt.Errorf("failed to begin transaction: %w", err)
}
defer func() {
if err != nil {
tx.Rollback()

[3.9087]

[6.296870]

if _, err := tx.ExecContext(ctx, query, args...); err != nil {
return fmt.Errorf("failed to update location: %w", err)
edit in tools/location.go at line 219

[6.296874]→[3.9323:9643](∅→∅)

}()

_, err = tx.ExecContext(ctx, query, args...)
if err != nil {
return output, fmt.Errorf("failed to update location: %w", err)
}

// Fetch the updated location
location, err := fetchLocationByID(ctx, tx, locationID)
if err != nil {
return output, fmt.Errorf("failed to fetch updated location: %w", err)
}
replacement in tools/location.go at line 220

[3.9644]→[3.9644:9752](∅→∅)

if err = tx.Commit(); err != nil {
return output, fmt.Errorf("failed to commit transaction: %w", err)
}

[3.9644]

[3.9752]

// Fetch the updated location
location, ferr := fetchLocationByID(ctx, tx, locationID)
if ferr != nil {
return fmt.Errorf("failed to fetch updated location: %w", ferr)
}
replacement in tools/location.go at line 226

[3.9753]→[3.9753:9906](∅→∅)

output.Location = location
output.Message = fmt.Sprintf("Successfully updated location '%s' (ID: %s)", location.Name, location.ID)
return output, nil

[3.9753]

[3.9906]

output.Location = location
output.Message = fmt.Sprintf("Successfully updated location '%s' (ID: %s)", location.Name, location.ID)
return nil
})
return output, err
edit in tools/import_unstructured.go at line 5

[6.307713]

[6.307713]

"database/sql"
replacement in tools/import_unstructured.go at line 56

[6.309320]→[6.309320:309515](∅→∅)

// Open database
database, err := db.OpenWriteableDB(dbPath)
if err != nil {
return output, fmt.Errorf("failed to open database: %w", err)
}
defer database.Close()

// Scan for WAV files

[6.309320]

[6.309515]

// Scan for WAV files (no DB needed)
replacement in tools/import_unstructured.go at line 66

[6.309770]→[6.309770:310014](∅→∅)

// Begin logged transaction
tx, err := db.BeginLoggedTx(ctx, database, "import_unstructured")
if err != nil {
return output, fmt.Errorf("failed to begin transaction: %w", err)
}
defer func() {
if err != nil {
tx.Rollback()
}
}()

[6.309770]

[6.310014]

err := db.WithWriteTx(ctx, dbPath, "import_unstructured", func(database *sql.DB, tx *db.LoggedTx) error {
// Process each file
for _, filePath := range files {
fileResult, procErr := processUnstructuredFile(tx, filePath, input.DatasetID)
replacement in tools/import_unstructured.go at line 71

[6.310015]→[6.310015:310151](∅→∅)

// Process each file
for _, filePath := range files {
fileResult, procErr := processUnstructuredFile(tx, filePath, input.DatasetID)

[6.310015]

[6.310151]

if procErr != nil {
output.FailedFiles++
output.Errors = append(output.Errors, utils.FileImportError{
FileName: filepath.Base(filePath),
Error: procErr.Error(),
Stage: utils.StageProcess,
})
continue
}
replacement in tools/import_unstructured.go at line 81

[6.310152]→[6.310152:310332](∅→∅),[6.310332]→[5.2792:2826](∅→∅),[5.2826]→[6.310357:310375](∅→∅),[6.310357]→[6.310357:310375](∅→∅)

if procErr != nil {
output.FailedFiles++
output.Errors = append(output.Errors, utils.FileImportError{
FileName: filepath.Base(filePath),
Error: procErr.Error(),
Stage: utils.StageProcess,
})
continue

[6.310152]

[6.310375]

if fileResult.Skipped {
output.SkippedFiles++
} else {
output.ImportedFiles++
output.TotalDuration += fileResult.Duration
}
replacement in tools/import_unstructured.go at line 88

[6.310379]→[6.310379:310519](∅→∅)

if fileResult.Skipped {
output.SkippedFiles++
} else {
output.ImportedFiles++
output.TotalDuration += fileResult.Duration
}

[6.310379]

[6.310519]

return nil
})
if err != nil {
return output, err
edit in tools/import_unstructured.go at line 94

[6.310523]→[6.310523:310655](∅→∅)

// Commit transaction
if err = tx.Commit(); err != nil {
return output, fmt.Errorf("failed to commit transaction: %w", err)
}
edit in tools/import_unstructured.go at line 188

[6.313402]→[6.313402:313556](∅→∅)

}

// Open database for validation
database, err := db.OpenReadOnlyDB(dbPath)
if err != nil {
return fmt.Errorf("failed to open database: %w", err)
edit in tools/import_unstructured.go at line 189

[6.313559]→[6.313559:313583](∅→∅)

defer database.Close()
replacement in tools/import_unstructured.go at line 190

[6.313584]→[6.313584:313964](∅→∅)

// Verify dataset exists and is active
var datasetExists bool
err = database.QueryRow(
"SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ? AND active = true)",
input.DatasetID,
).Scan(&datasetExists)
if err != nil {
return fmt.Errorf("failed to query dataset: %w", err)
}
if !datasetExists {
return fmt.Errorf("dataset not found or inactive: %s", input.DatasetID)
}

[6.313584]

[6.313964]

return db.WithReadDB(dbPath, func(database *sql.DB) error {
// Verify dataset exists and is active
if _, err := db.DatasetExistsAndActive(database, input.DatasetID); err != nil {
return err
}
replacement in tools/import_unstructured.go at line 196

[6.313965]→[6.313965:314007](∅→∅),[6.314007]→[6.5401:5488](∅→∅),[6.5488]→[6.314097:314113](∅→∅),[6.314097]→[6.314097:314113](∅→∅)

// Verify dataset is 'unstructured' type
if err := db.ValidateDatasetTypeUnstructured(database, input.DatasetID); err != nil {
return err
}

[6.313965]

[6.314113]

// Verify dataset is 'unstructured' type
if err := db.ValidateDatasetTypeUnstructured(database, input.DatasetID); err != nil {
return err
}
replacement in tools/import_unstructured.go at line 201

[6.314114]→[6.314114:314126](∅→∅)

return nil

[6.314114]

[6.314126]

return nil
})
replacement in tools/import_segments.go at line 243

[6.325503]→[6.325503:325799](∅→∅)

var datasetType string
err := dbConn.QueryRow(`SELECT type FROM dataset WHERE id = ? AND active = true`, datasetID).Scan(&datasetType)
if err == sql.ErrNoRows {
return fmt.Errorf("dataset not found: %s", datasetID)
}
if err != nil {
return fmt.Errorf("failed to query dataset: %w", err)

[6.325503]

[6.325799]

if err := db.ValidateDatasetTypeForImport(dbConn, datasetID); err != nil {
return err
edit in tools/import_segments.go at line 246

[6.325802]→[6.325802:325918](∅→∅)

if datasetType != "structured" {
return fmt.Errorf("dataset must be 'structured' type, got: %s", datasetType)
}
replacement in tools/import_segments.go at line 248

[6.325960]→[6.325960:326223](∅→∅)

var locationExists bool
err = dbConn.QueryRow(`
SELECT EXISTS(SELECT 1 FROM location WHERE id = ? AND dataset_id = ? AND active = true)
`, locationID, datasetID).Scan(&locationExists)
if err != nil {
return fmt.Errorf("failed to query location: %w", err)

[6.325960]

[6.326223]

if err := db.ValidateLocationBelongsToDataset(dbConn, locationID, datasetID); err != nil {
return err
edit in tools/import_segments.go at line 251

[6.326226]→[6.326226:326334](∅→∅)

if !locationExists {
return fmt.Errorf("location not found or not linked to dataset: %s", locationID)
}
replacement in tools/import_segments.go at line 253

[6.326376]→[6.326376:326742](∅→∅)

var clusterExists bool
err = dbConn.QueryRow(`
SELECT EXISTS(SELECT 1 FROM cluster WHERE id = ? AND location_id = ? AND active = true)
`, clusterID, locationID).Scan(&clusterExists)
if err != nil {
return fmt.Errorf("failed to query cluster: %w", err)
}
if !clusterExists {
return fmt.Errorf("cluster not found or not linked to location: %s", clusterID)

[6.326376]

[6.326742]

if err := db.ClusterBelongsToLocation(dbConn, clusterID, locationID); err != nil {
return err
replacement in tools/import_files.go at line 130

[6.349360]→[6.349360:349545](∅→∅)

// Open database for validation queries
database, err := db.OpenReadOnlyDB(dbPath)
if err != nil {
return fmt.Errorf("failed to open database: %w", err)
}
defer database.Close()

[6.349360]

[6.349545]

return db.WithReadDB(dbPath, func(database *sql.DB) error {
// Verify dataset exists, is active, and is 'structured' type
if err := db.ValidateDatasetTypeForImport(database, datasetID); err != nil {
return err
}
replacement in tools/import_files.go at line 136

[6.349546]→[6.349546:349906](∅→∅)

// Verify dataset exists and is active
var datasetExists bool
err = database.QueryRow("SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ? AND active = true)", datasetID).Scan(&datasetExists)
if err != nil {
return fmt.Errorf("failed to query dataset: %w", err)
}
if !datasetExists {
return fmt.Errorf("dataset not found or inactive: %s", datasetID)
}

[6.349546]

[6.349906]

// Verify location exists and belongs to dataset
if err := db.ValidateLocationBelongsToDataset(database, locationID, datasetID); err != nil {
return err
}
replacement in tools/import_files.go at line 141

[6.349907]→[6.349907:349995](∅→∅),[6.349995]→[6.5726:5804](∅→∅),[6.5804]→[6.350076:351117](∅→∅),[6.350076]→[6.350076:351117](∅→∅)

// Verify dataset is 'structured' type (file imports only support structured datasets)
if err := db.ValidateDatasetTypeForImport(database, datasetID); err != nil {
return err
}

// Verify location exists and belongs to dataset
var locationDatasetID string
err = database.QueryRow("SELECT dataset_id FROM location WHERE id = ? AND active = true", locationID).Scan(&locationDatasetID)
if err == sql.ErrNoRows {
return fmt.Errorf("location not found or inactive: %s", locationID)
}
if err != nil {
return fmt.Errorf("failed to query location: %w", err)
}
if locationDatasetID != datasetID {
return fmt.Errorf("location %s does not belong to dataset %s", locationID, datasetID)
}

// Verify cluster exists and belongs to location
var clusterLocationID string
err = database.QueryRow("SELECT location_id FROM cluster WHERE id = ? AND active = true", clusterID).Scan(&clusterLocationID)
if err == sql.ErrNoRows {
return fmt.Errorf("cluster not found or inactive: %s", clusterID)
}
if err != nil {
return fmt.Errorf("failed to query cluster: %w", err)
}
if clusterLocationID != locationID {
return fmt.Errorf("cluster %s does not belong to location %s", clusterID, locationID)
}

[6.349907]

[6.351117]

// Verify cluster exists and belongs to location
if err := db.ClusterBelongsToLocation(database, clusterID, locationID); err != nil {
return err
}
replacement in tools/import_files.go at line 146

[6.351118]→[6.351118:351130](∅→∅)

return nil

[6.351118]

[6.351130]

return nil
})
replacement in tools/dataset.go at line 59

[6.372650]→[6.372650:372844](∅→∅)

// Open writable database connection
database, err := db.OpenWriteableDB(dbPath)
if err != nil {
return output, fmt.Errorf("database connection failed: %w", err)
}
defer database.Close()

[6.372650]

[6.372844]

err = db.WithWriteTx(ctx, dbPath, "create_or_update_dataset", func(database *sql.DB, tx *db.LoggedTx) error {
// Check for existing dataset with same name (UNIQUE constraint)
var existingID string
qerr := tx.QueryRowContext(ctx,
"SELECT id FROM dataset WHERE name = ? AND active = true",
*input.Name,
).Scan(&existingID)
replacement in tools/dataset.go at line 67

[6.372845]→[6.372845:373085](∅→∅)

// Begin logged transaction
tx, err := db.BeginLoggedTx(ctx, database, "create_or_update_dataset")
if err != nil {
return output, fmt.Errorf("failed to begin transaction: %w", err)
}
defer func() {
if err != nil {
tx.Rollback()

[6.372845]

[6.373085]

if qerr == nil {
result, herr := handleExistingDataset(ctx, tx, existingID)
if herr != nil {
return herr
}
output = result
return nil
edit in tools/dataset.go at line 75

[6.373089]→[6.373089:373094](∅→∅)

}()
replacement in tools/dataset.go at line 76

[6.373095]→[6.373095:373330](∅→∅),[6.373330]→[4.3598:3733](∅→∅)

// Check for existing dataset with same name (UNIQUE constraint)
var existingID string
err = tx.QueryRowContext(ctx,
"SELECT id FROM dataset WHERE name = ? AND active = true",
*input.Name,
).Scan(&existingID)

if err == nil {
return handleExistingDataset(ctx, tx, existingID)
}

return insertNewDataset(ctx, tx, *input.Name, input.Description, datasetType)

[6.373095]

[4.3733]

result, insErr := insertNewDataset(ctx, tx, *input.Name, input.Description, datasetType)
if insErr != nil {
return insErr
}
output = result
return nil
})
return output, err
edit in tools/dataset.go at line 108

[4.4476]

[4.4476]

// Caller is responsible for committing the transaction.
edit in tools/dataset.go at line 117

[4.4988]→[4.4988:5106](∅→∅)

}

if err = tx.Commit(); err != nil {
return DatasetOutput{}, fmt.Errorf("failed to commit transaction: %w", err)
edit in tools/dataset.go at line 126

[4.5373]

[4.5373]

// Caller is responsible for committing the transaction.
edit in tools/dataset.go at line 148

[4.5796]→[6.375028:375068](∅→∅),[6.375028]→[6.375028:375068](∅→∅),[6.375068]→[4.5797:5875](∅→∅)

}

if err = tx.Commit(); err != nil {
return DatasetOutput{}, fmt.Errorf("failed to commit transaction: %w", err)
replacement in tools/dataset.go at line 226

[6.6540]→[6.6540:6720](∅→∅)

// Open writable database
database, err := db.OpenWriteableDB(dbPath)
if err != nil {
return output, fmt.Errorf("failed to open database: %w", err)
}
defer database.Close()

[6.6540]

[6.6720]

err := db.WithWriteTx(ctx, dbPath, "create_or_update_dataset", func(database *sql.DB, tx *db.LoggedTx) error {
// Verify dataset exists and check active status
if err := verifyDatasetActive(database, datasetID); err != nil {
return err
}
replacement in tools/dataset.go at line 232

[6.6721]→[6.6721:6861](∅→∅)

// Verify dataset exists and check active status
if err := verifyDatasetActive(database, datasetID); err != nil {
return output, err
}

[6.6721]

[6.6861]

// Build dynamic UPDATE query
query, args, qerr := buildUpdateQuery(input, datasetID)
if qerr != nil {
return qerr
}
replacement in tools/dataset.go at line 238

[6.6862]→[6.6862:6990](∅→∅)

// Build dynamic UPDATE query
query, args, err := buildUpdateQuery(input, datasetID)
if err != nil {
return output, err
}

[6.6862]

[6.6990]

if _, err := tx.Exec(query, args...); err != nil {
return fmt.Errorf("failed to update dataset: %w", err)
}
replacement in tools/dataset.go at line 242

[6.6991]→[6.377635:377851](∅→∅),[6.377635]→[6.377635:377851](∅→∅)

// Begin logged transaction for update
tx, err := db.BeginLoggedTx(ctx, database, "create_or_update_dataset")
if err != nil {
return output, fmt.Errorf("failed to begin transaction: %w", err)
}
defer func() {

[6.6991]

[6.377851]

// Fetch the updated dataset
var dataset db.Dataset
err := tx.QueryRow(
"SELECT id, name, description, created_at, last_modified, active, type FROM dataset WHERE id = ?",
datasetID,
).Scan(&dataset.ID, &dataset.Name, &dataset.Description, &dataset.CreatedAt, &dataset.LastModified, &dataset.Active, &dataset.Type)
replacement in tools/dataset.go at line 249

[6.377869]→[6.377869:377886](∅→∅)

tx.Rollback()

[6.377869]

[6.377886]

return fmt.Errorf("failed to fetch updated dataset: %w", err)
edit in tools/dataset.go at line 251

[6.377890]→[6.377890:378667](∅→∅)

}()

_, err = tx.Exec(query, args...)
if err != nil {
return output, fmt.Errorf("failed to update dataset: %w", err)
}

// Fetch the updated dataset
var dataset db.Dataset
err = tx.QueryRow(
"SELECT id, name, description, created_at, last_modified, active, type FROM dataset WHERE id = ?",
datasetID,
).Scan(&dataset.ID, &dataset.Name, &dataset.Description, &dataset.CreatedAt, &dataset.LastModified, &dataset.Active, &dataset.Type)
if err != nil {
return output, fmt.Errorf("failed to fetch updated dataset: %w", err)
}

if err = tx.Commit(); err != nil {
return output, fmt.Errorf("failed to commit transaction: %w", err)
}

output.Dataset = dataset
output.Message = fmt.Sprintf("Successfully updated dataset '%s' (ID: %s)", dataset.Name, dataset.ID)
replacement in tools/dataset.go at line 252

[6.378668]→[6.378668:378688](∅→∅)

return output, nil

[6.378668]

[6.6992]

output.Dataset = dataset
output.Message = fmt.Sprintf("Successfully updated dataset '%s' (ID: %s)", dataset.Name, dataset.ID)
return nil
})
return output, err
replacement in tools/cluster.go at line 85

[6.381890]→[6.381890:382325](∅→∅)

// Open writable database connection
database, err := db.OpenWriteableDB(dbPath)
if err != nil {
return output, fmt.Errorf("database connection failed: %w", err)
}
defer database.Close()

// Begin logged transaction
tx, err := db.BeginLoggedTx(ctx, database, "create_or_update_cluster")
if err != nil {
return output, fmt.Errorf("failed to begin transaction: %w", err)
}
defer func() {
if err != nil {
tx.Rollback()

[6.381890]

[6.382325]

err := db.WithWriteTx(ctx, dbPath, "create_or_update_cluster", func(database *sql.DB, tx *db.LoggedTx) error {
// Verify parent references exist and are active
datasetName, locationName, verr := verifyClusterParentRefs(ctx, tx, input)
if verr != nil {
return verr
edit in tools/cluster.go at line 91

[6.382329]→[6.382329:382334](∅→∅)

}()
replacement in tools/cluster.go at line 92

[6.383066]→[4.6055:6180](∅→∅),[6.2277]→[6.383659:383676](∅→∅),[4.6180]→[6.383659:383676](∅→∅),[6.383659]→[6.383659:383676](∅→∅),[6.383676]→[6.2278:2299](∅→∅),[6.385120]→[6.385120:385123](∅→∅)

// Verify parent references exist and are active
datasetName, locationName, err := verifyClusterParentRefs(ctx, tx, input)
if err != nil {
return output, err
}

[6.383066]

[6.385123]

// Check for existing cluster with same name in location (UNIQUE constraint)
existing, findErr := findExistingClusterInLocation(ctx, tx, *input.LocationID, *input.Name)
if findErr == nil {
output.Cluster = existing
output.Message = fmt.Sprintf("Cluster '%s' already exists in location '%s' (ID: %s) - returning existing cluster", existing.Name, locationName, existing.ID)
return nil // commit transaction
}
replacement in tools/cluster.go at line 100

[6.385124]→[6.385124:385202](∅→∅),[6.385202]→[6.2411:2500](∅→∅),[6.2500]→[6.385393:385410](∅→∅),[6.385393]→[6.385393:385410](∅→∅),[6.2562]→[6.386066:386173](∅→∅),[6.386066]→[6.386066:386173](∅→∅)

// Check for existing cluster with same name in location (UNIQUE constraint)
existing, err := findExistingClusterInLocation(ctx, tx, *input.LocationID, *input.Name)
if err == nil {
if err = tx.Commit(); err != nil {
return output, fmt.Errorf("failed to commit transaction: %w", err)

[6.385124]

[6.386173]

result, insErr := insertNewCluster(ctx, tx, input, datasetName, locationName)
if insErr != nil {
return insErr
replacement in tools/cluster.go at line 104

[6.386177]→[6.2563:2750](∅→∅),[6.2750]→[6.386362:386387](∅→∅),[6.386362]→[6.386362:386387](∅→∅),[6.386387]→[4.6181:6249](∅→∅)

output.Cluster = existing
output.Message = fmt.Sprintf("Cluster '%s' already exists in location '%s' (ID: %s) - returning existing cluster", existing.Name, locationName, existing.ID)
return output, nil
}

return insertNewCluster(ctx, tx, input, datasetName, locationName)

[6.386177]

[4.6249]

output = result
return nil // commit transaction
})
return output, err
replacement in tools/cluster.go at line 112

[4.6473]→[4.6473:6545](∅→∅)

datasetName, err := verifyDatasetForCluster(ctx, tx, *input.DatasetID)

[4.6473]

[4.6545]

datasetName, err := db.DatasetExistsAndActive(tx, *input.DatasetID)
replacement in tools/cluster.go at line 117

[4.6587]→[4.6587:6693](∅→∅)

locationName, err := verifyLocationForCluster(ctx, tx, *input.LocationID, *input.DatasetID, datasetName)

[4.6587]

[4.6693]

locationName, err := db.LocationBelongsToDataset(tx, *input.LocationID, *input.DatasetID)
edit in tools/cluster.go at line 132

[4.7082]

[4.7082]

// Caller is responsible for committing the transaction.
edit in tools/cluster.go at line 150

[4.7450]→[6.387569:387609](∅→∅),[6.387569]→[6.387569:387609](∅→∅),[6.387609]→[4.7451:7529](∅→∅)

}

if err = tx.Commit(); err != nil {
return ClusterOutput{}, fmt.Errorf("failed to commit transaction: %w", err)
edit in tools/cluster.go at line 184

[6.3790]→[6.3790:5735](∅→∅)

// verifyDatasetForCluster verifies dataset exists and is active within a transaction
func verifyDatasetForCluster(ctx context.Context, tx *db.LoggedTx, datasetID string) (string, error) {
var exists, active bool
var name string
err := tx.QueryRowContext(ctx,
"SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ?), COALESCE((SELECT active FROM dataset WHERE id = ?), false), COALESCE((SELECT name FROM dataset WHERE id = ?), '')",
datasetID, datasetID, datasetID,
).Scan(&exists, &active, &name)
if err != nil {
return "", fmt.Errorf("failed to verify dataset: %w", err)
}
if !exists {
return "", fmt.Errorf("dataset with ID '%s' does not exist", datasetID)
}
if !active {
return "", fmt.Errorf("dataset '%s' (ID: %s) is not active", name, datasetID)
}
return name, nil
}

// verifyLocationForCluster verifies location exists, is active, and belongs to the dataset
func verifyLocationForCluster(ctx context.Context, tx *db.LoggedTx, locationID, datasetID, datasetName string) (string, error) {
var exists, active bool
var name, locDatasetID string
err := tx.QueryRowContext(ctx,
"SELECT EXISTS(SELECT 1 FROM location WHERE id = ?), COALESCE((SELECT active FROM location WHERE id = ?), false), COALESCE((SELECT name FROM location WHERE id = ?), ''), COALESCE((SELECT dataset_id FROM location WHERE id = ?), '')",
locationID, locationID, locationID, locationID,
).Scan(&exists, &active, &name, &locDatasetID)
if err != nil {
return "", fmt.Errorf("failed to verify location: %w", err)
}
if !exists {
return "", fmt.Errorf("location with ID '%s' does not exist", locationID)
}
if !active {
return "", fmt.Errorf("location '%s' (ID: %s) is not active", name, locationID)
}
if locDatasetID != datasetID {
return "", fmt.Errorf("location '%s' (ID: %s) does not belong to dataset '%s' (ID: %s) - it belongs to dataset ID '%s'",
name, locationID, datasetName, datasetID, locDatasetID)
}
return name, nil
}
replacement in tools/cluster.go at line 348

[6.12965]→[6.12965:13212](∅→∅),[6.13212]→[6.9536:9628](∅→∅),[6.9628]→[6.13468:13576](∅→∅),[6.13468]→[6.13468:13576](∅→∅)

database, err := db.OpenWriteableDB(dbPath)
if err != nil {
return output, fmt.Errorf("failed to open database: %w", err)
}
defer database.Close()

if err := validateClusterActive(database, clusterID); err != nil {
return output, err
}

if err := validateClusterCyclicPattern(database, input); err != nil {
return output, err
}

query, args, err := buildClusterUpdateQuery(input, clusterID)
if err != nil {
return output, err
}

[6.12965]

[6.13576]

err = db.WithWriteTx(ctx, dbPath, "create_or_update_cluster", func(database *sql.DB, tx *db.LoggedTx) error {
if err := validateClusterActive(database, clusterID); err != nil {
return err
}
replacement in tools/cluster.go at line 353

[6.13577]→[6.391345:391556](∅→∅),[6.391345]→[6.391345:391556](∅→∅)

tx, err := db.BeginLoggedTx(ctx, database, "create_or_update_cluster")
if err != nil {
return output, fmt.Errorf("failed to begin transaction: %w", err)
}
defer func() {
if err != nil {
tx.Rollback()

[6.13577]

[6.391556]

if err := validateClusterCyclicPattern(database, input); err != nil {
return err
edit in tools/cluster.go at line 356

[6.391560]→[6.391560:391565](∅→∅)

}()
replacement in tools/cluster.go at line 357

[6.391566]→[6.13578:13629](∅→∅),[6.13629]→[6.391617:391685](∅→∅),[6.391617]→[6.391617:391685](∅→∅)

if _, err = tx.Exec(query, args...); err != nil {
return output, fmt.Errorf("failed to update cluster: %w", err)
}

[6.391566]

[6.391685]

query, args, qerr := buildClusterUpdateQuery(input, clusterID)
if qerr != nil {
return qerr
}
replacement in tools/cluster.go at line 362

[6.391686]→[6.13630:13684](∅→∅),[6.13684]→[6.392152:392244](∅→∅),[6.392152]→[6.392152:392244](∅→∅)

cluster, err := fetchClusterByID(ctx, tx, clusterID)
if err != nil {
return output, fmt.Errorf("failed to fetch updated cluster: %w", err)
}

[6.391686]

[6.392244]

if _, err := tx.Exec(query, args...); err != nil {
return fmt.Errorf("failed to update cluster: %w", err)
}
replacement in tools/cluster.go at line 366

[6.392245]→[6.392245:392353](∅→∅)

if err = tx.Commit(); err != nil {
return output, fmt.Errorf("failed to commit transaction: %w", err)
}

[6.392245]

[6.392353]

cluster, ferr := fetchClusterByID(ctx, tx, clusterID)
if ferr != nil {
return fmt.Errorf("failed to fetch updated cluster: %w", ferr)
}
replacement in tools/cluster.go at line 371

[6.392354]→[6.392354:392482](∅→∅),[6.392483]→[6.392483:392503](∅→∅)

output.Cluster = cluster
output.Message = fmt.Sprintf("Successfully updated cluster '%s' (ID: %s)", cluster.Name, cluster.ID)
return output, nil

[6.392354]

[6.392503]

output.Cluster = cluster
output.Message = fmt.Sprintf("Successfully updated cluster '%s' (ID: %s)", cluster.Name, cluster.ID)
return nil
})
return output, err
replacement in tools/calls_from_raven.go at line 8

[6.458297]→[6.458297:458305](∅→∅)

"sort"

[6.458297]

[6.458305]

"strconv"
edit in tools/calls_from_raven.go at line 10

[6.458316]→[6.458316:458339](∅→∅)

"sync"
"sync/atomic"
edit in tools/calls_from_raven.go at line 33

[6.459304]→[6.459304:459778](∅→∅),[6.459778]→[6.9638:9980](∅→∅),[6.9980]→[6.459778:460898](∅→∅),[6.459778]→[6.459778:460898](∅→∅)

}

// RavenSelection represents a single Raven selection
type RavenSelection struct {
StartTime float64
EndTime float64
FreqLow float64
FreqHigh float64
Species string
}

// ravenJob represents a single Raven file to process
type ravenJob struct {
ravenFile string
}

// ravenResult represents the result of processing a single Raven file
type ravenResult struct {
ravenFile string
calls []ClusteredCall
written bool
skipped bool
err error
}

func (r ravenResult) filePath() string { return r.ravenFile }
func (r ravenResult) getCalls() []ClusteredCall { return r.calls }
func (r ravenResult) wasWritten() bool { return r.written }
func (r ravenResult) wasSkipped() bool { return r.skipped }
func (r ravenResult) getError() error { return r.err }

// CallsFromRaven processes Raven selection files and writes .data files
func CallsFromRaven(input CallsFromRavenInput) (CallsFromRavenOutput, error) {
var output CallsFromRavenOutput
output.Filter = "Raven"

// Collect Raven files to process
var ravenFiles []string
if input.File != "" {
ravenFiles = []string{input.File}
} else if input.Folder != "" {
var err error
ravenFiles, err = findRavenFiles(input.Folder)
if err != nil {
errMsg := fmt.Sprintf("Failed to find Raven files: %v", err)
output.Error = &errMsg
return output, fmt.Errorf("%s", errMsg)
}
} else {
errMsg := "Either --folder or --file must be specified"
output.Error = &errMsg
return output, fmt.Errorf("%s", errMsg)
}

if len(ravenFiles) == 0 {
errMsg := "No Raven files found"
output.Error = &errMsg
return output, fmt.Errorf("%s", errMsg)
}

// Single file or small batch: process sequentially (avoid goroutine overhead)
if len(ravenFiles) < 10 {
return callsFromRavenSequential(input, ravenFiles)
}

// Large batch: parallel processing with DirCache
return callsFromRavenParallel(input, ravenFiles)
edit in tools/calls_from_raven.go at line 34

[6.460900]→[6.460900:462027](∅→∅)

// callsFromRavenSequential processes Raven files one at a time (for small batches)
func callsFromRavenSequential(input CallsFromRavenInput, ravenFiles []string) (CallsFromRavenOutput, error) {
var output CallsFromRavenOutput
output.Filter = "Raven"

// Build DirCache once for the folder (even sequential benefits from avoiding repeated dir scans)
dirCaches := make(map[string]*DirCache)
if input.Folder != "" {
dirCaches[input.Folder] = NewDirCache(input.Folder)
}

speciesCount := make(map[string]int)
var allCalls []ClusteredCall
dataFilesWritten := 0
dataFilesSkipped := 0
filesProcessed := 0
filesDeleted := 0

for _, ravenFile := range ravenFiles {
dir := filepath.Dir(ravenFile)
cache := dirCaches[dir]
if cache == nil {
cache = NewDirCache(dir)
dirCaches[dir] = cache
}

calls, written, skipped, err := processRavenFileCached(ravenFile, cache)
if err != nil {
errMsg := fmt.Sprintf("Error processing %s: %v", ravenFile, err)
output.Error = &errMsg
return output, fmt.Errorf("%s", errMsg)
}

if written {
dataFilesWritten++
}
if skipped {
dataFilesSkipped++
}
replacement in tools/calls_from_raven.go at line 35

[6.462028]→[6.462028:462134](∅→∅)

for _, call := range calls {
allCalls = append(allCalls, call)
speciesCount[call.EbirdCode]++
}

[6.462028]

[6.462134]

// ravenSource implements CallSource for Raven selection files
type ravenSource struct{}
replacement in tools/calls_from_raven.go at line 38

[6.462135]→[6.462135:462154](∅→∅)

filesProcessed++

[6.462135]

[6.462154]

func (ravenSource) Name() string { return "Raven" }
replacement in tools/calls_from_raven.go at line 40

[6.462155]→[6.462155:463660](∅→∅),[6.463660]→[6.9981:10026](∅→∅),[6.10026]→[6.463702:464093](∅→∅),[6.463702]→[6.463702:464093](∅→∅),[6.464093]→[6.10027:10119](∅→∅),[6.10119]→[6.464897:464898](∅→∅),[6.464897]→[6.464897:464898](∅→∅),[6.464898]→[6.10120:10183](∅→∅),[6.10183]→[6.465103:465128](∅→∅),[6.465103]→[6.465103:465128](∅→∅),[6.465128]→[6.10184:10216](∅→∅),[6.10216]→[6.465154:465158](∅→∅),[6.465154]→[6.465154:465158](∅→∅),[6.465158]→[6.10217:10254](∅→∅),[6.10254]→[6.465399:465400](∅→∅),[6.465399]→[6.465399:465400](∅→∅),[6.465400]→[6.10255:10551](∅→∅),[6.10551]→[6.465660:465743](∅→∅),[6.465660]→[6.465660:465743](∅→∅),[6.465743]→[6.10552:10665](∅→∅),[6.10665]→[6.465853:466500](∅→∅),[6.465853]→[6.465853:466500](∅→∅)

// Delete if requested and successfully processed
if input.Delete && written {
if err := os.Remove(ravenFile); err != nil {
errMsg := fmt.Sprintf("Failed to delete %s: %v", ravenFile, err)
output.Error = &errMsg
return output, fmt.Errorf("%s", errMsg)
}
filesDeleted++
}

if input.ProgressHandler != nil {
input.ProgressHandler(filesProcessed, len(ravenFiles), filepath.Base(ravenFile))
}
}

// Sort all calls by file, then start time
sort.Slice(allCalls, func(i, j int) bool {
if allCalls[i].File != allCalls[j].File {
return allCalls[i].File < allCalls[j].File
}
return allCalls[i].StartTime < allCalls[j].StartTime
})

output.Calls = allCalls
output.TotalCalls = len(allCalls)
output.SpeciesCount = speciesCount
output.DataFilesWritten = dataFilesWritten
output.DataFilesSkipped = dataFilesSkipped
output.FilesProcessed = filesProcessed
output.FilesDeleted = filesDeleted

return output, nil
}

// callsFromRavenParallel processes Raven files concurrently using a worker pool and DirCache
func callsFromRavenParallel(input CallsFromRavenInput, ravenFiles []string) (CallsFromRavenOutput, error) {
var output CallsFromRavenOutput
output.Filter = "Raven"

total := len(ravenFiles)
var processed atomic.Int32

// Build DirCache for the folder
dirCaches := &sync.Map{}
if input.Folder != "" {
cache := NewDirCache(input.Folder)
dirCaches.Store(input.Folder, cache)
}

// Create job and result channels
jobs := make(chan ravenJob, total)
results := make(chan parallelResult, total)

// Start workers
var wg sync.WaitGroup
for range DOT_DATA_WORKERS {
wg.Add(1)
go ravenWorker(dirCaches, jobs, results, &wg)
}

// Send jobs
for _, ravenFile := range ravenFiles {
jobs <- ravenJob{ravenFile: ravenFile}
}
close(jobs)

// Wait for workers to finish, then close results
go func() {
wg.Wait()
close(results)
}()

// Collect results with progress reporting
stats := aggregateResults(results, total, &processed, input.Delete, input.ProgressHandler)

if stats.firstErr != nil {
errMsg := stats.firstErr.Error()
output.Error = &errMsg
return output, stats.firstErr
}

sortCallsByFileAndTime(stats.calls)

output.Calls = stats.calls
output.TotalCalls = len(stats.calls)
output.SpeciesCount = stats.speciesCount
output.DataFilesWritten = stats.dataFilesWritten
output.DataFilesSkipped = stats.dataFilesSkipped
output.FilesProcessed = stats.filesProcessed
output.FilesDeleted = stats.filesDeleted

return output, nil
}

// ravenWorker processes Raven files from the jobs channel
func ravenWorker(dirCaches *sync.Map, jobs <-chan ravenJob, results chan<- parallelResult, wg *sync.WaitGroup) {
defer wg.Done()

for job := range jobs {
dir := filepath.Dir(job.ravenFile)

// Get or create DirCache for this directory
var cache *DirCache
if cached, ok := dirCaches.Load(dir); ok {
cache = cached.(*DirCache)
} else {
cache = NewDirCache(dir)
dirCaches.Store(dir, cache)
}

calls, written, skipped, err := processRavenFileCached(job.ravenFile, cache)
results <- ravenResult{
ravenFile: job.ravenFile,
calls: calls,
written: written,
skipped: skipped,
err: err,
}
}
}

// findRavenFiles finds all Raven selection files in a folder
func findRavenFiles(folder string) ([]string, error) {

[6.462155]

[6.466500]

func (ravenSource) FindFiles(folder string) ([]string, error) {
edit in tools/calls_from_raven.go at line 57

[6.466785]

[6.467244]

func (ravenSource) ProcessFile(ravenFile string, cache *DirCache) ([]ClusteredCall, bool, bool, error) {
return processRavenFileCached(ravenFile, cache)
}

// CallsFromRaven processes Raven selection files and writes .data files
func CallsFromRaven(input CallsFromRavenInput) (CallsFromRavenOutput, error) {
src := ravenSource{}
commonInput := CallsFromSourceInput(input)
edit in tools/calls_from_raven.go at line 67

[6.467245]

[6.16095]

commonOutput, err := callsFromSource(src, commonInput)

// Convert to Raven-specific output type
var output CallsFromRavenOutput
output.Calls = commonOutput.Calls
output.TotalCalls = commonOutput.TotalCalls
output.SpeciesCount = commonOutput.SpeciesCount
output.DataFilesWritten = commonOutput.DataFilesWritten
output.DataFilesSkipped = commonOutput.DataFilesSkipped
output.FilesProcessed = commonOutput.FilesProcessed
output.FilesDeleted = commonOutput.FilesDeleted
output.Filter = commonOutput.Filter
output.Error = commonOutput.Error
return output, err
}

// RavenSelection represents a single Raven selection
type RavenSelection struct {
StartTime float64
EndTime float64
FreqLow float64
FreqHigh float64
Species string
}
replacement in tools/calls_from_raven.go at line 153

[6.17411]→[6.17411:17498](∅→∅)

if _, err := fmt.Sscanf(fields[idx.beginTimeIdx], "%f", &sel.StartTime); err != nil {

[6.17411]

[6.17498]

startTime, err := strconv.ParseFloat(fields[idx.beginTimeIdx], 64)
if err != nil {
replacement in tools/calls_from_raven.go at line 157

[6.17594]→[6.17594:17677](∅→∅)

if _, err := fmt.Sscanf(fields[idx.endTimeIdx], "%f", &sel.EndTime); err != nil {

[6.17594]

[6.17677]

sel.StartTime = startTime

endTime, err := strconv.ParseFloat(fields[idx.endTimeIdx], 64)
if err != nil {
edit in tools/calls_from_raven.go at line 163

[6.17769]

[6.17769]

sel.EndTime = endTime
replacement in tools/calls_from_raven.go at line 166

[6.17827]→[6.17827:17911](∅→∅)

if _, err := fmt.Sscanf(fields[idx.lowFreqIdx], "%f", &sel.FreqLow); err != nil {

[6.17827]

[6.17911]

freqLow, err := strconv.ParseFloat(fields[idx.lowFreqIdx], 64)
if err != nil {
edit in tools/calls_from_raven.go at line 170

[6.18005]

[6.18005]

sel.FreqLow = freqLow
replacement in tools/calls_from_raven.go at line 173

[6.18068]→[6.18068:18154](∅→∅)

if _, err := fmt.Sscanf(fields[idx.highFreqIdx], "%f", &sel.FreqHigh); err != nil {

[6.18068]

[6.18154]

freqHigh, err := strconv.ParseFloat(fields[idx.highFreqIdx], 64)
if err != nil {
edit in tools/calls_from_raven.go at line 177

[6.18250]

[6.469308]

sel.FreqHigh = freqHigh
edit in tools/calls_from_preds.go at line 300

[6.24308]→[6.491817:492016](∅→∅),[6.491817]→[6.491817:492016](∅→∅)

}

// extractFilename extracts just the filename from a path
// "./C05/2025-11-08/20250518_210000.WAV" -> "20250518_210000.WAV"
func extractFilename(path string) string {
return filepath.Base(path)
replacement in tools/calls_from_preds.go at line 384

[6.495020]→[6.495020:495061](∅→∅)

filename := extractFilename(call.File)

[6.495020]

[6.495061]

filename := filepath.Base(call.File)
file addition: calls_from_common.go (----------)

[6.248737]

package tools

import (
"fmt"
"os"
"path/filepath"
"sort"
"sync"
"sync/atomic"
)

// CallsFromSourceInput defines the common input for calls-from-source tools
type CallsFromSourceInput struct {
Folder string `json:"folder"`
File string `json:"file"`
Delete bool `json:"delete"`
ProgressHandler ProgressHandler `json:"-"` // Optional progress callback
}

// CallsFromSourceOutput defines the common output for calls-from-source tools
type CallsFromSourceOutput struct {
Calls []ClusteredCall `json:"calls"`
TotalCalls int `json:"total_calls"`
SpeciesCount map[string]int `json:"species_count"`
DataFilesWritten int `json:"data_files_written"`
DataFilesSkipped int `json:"data_files_skipped"`
FilesProcessed int `json:"files_processed"`
FilesDeleted int `json:"files_deleted"`
Filter string `json:"filter"`
Error *string `json:"error,omitempty"`
}

// CallSource abstracts a source of bird call data (Raven, BirdNET, etc.)
type CallSource interface {
// Name returns the display name (e.g. "Raven", "BirdNET")
Name() string
// FindFiles discovers source files in the given folder
FindFiles(folder string) ([]string, error)
// ProcessFile processes a single source file and returns calls, write/skip status
ProcessFile(path string, cache *DirCache) (calls []ClusteredCall, written, skipped bool, err error)
}

// callsFromSource is the shared entry point for all call source tools.
func callsFromSource(src CallSource, input CallsFromSourceInput) (CallsFromSourceOutput, error) {
var output CallsFromSourceOutput
output.Filter = src.Name()

// Collect source files to process
var files []string
if input.File != "" {
files = []string{input.File}
} else if input.Folder != "" {
var err error
files, err = src.FindFiles(input.Folder)
if err != nil {
errMsg := fmt.Sprintf("Failed to find %s files: %v", src.Name(), err)
output.Error = &errMsg
return output, fmt.Errorf("%s", errMsg)
}
} else {
errMsg := "Either --folder or --file must be specified"
output.Error = &errMsg
return output, fmt.Errorf("%s", errMsg)
}

if len(files) == 0 {
errMsg := fmt.Sprintf("No %s files found", src.Name())
output.Error = &errMsg
return output, fmt.Errorf("%s", errMsg)
}

// Single file or small batch: process sequentially (avoid goroutine overhead)
if len(files) < 10 {
return callsFromSourceSequential(src, input, files)
}

// Large batch: parallel processing with DirCache
return callsFromSourceParallel(src, input, files)
}

// callsFromSourceSequential processes source files one at a time (for small batches)
func callsFromSourceSequential(src CallSource, input CallsFromSourceInput, files []string) (CallsFromSourceOutput, error) {
var output CallsFromSourceOutput
output.Filter = src.Name()

// Build DirCache once for the folder
dirCaches := make(map[string]*DirCache)
if input.Folder != "" {
dirCaches[input.Folder] = NewDirCache(input.Folder)
}

speciesCount := make(map[string]int)
var allCalls []ClusteredCall
dataFilesWritten := 0
dataFilesSkipped := 0
filesProcessed := 0
filesDeleted := 0

for _, file := range files {
dir := filepath.Dir(file)
cache := dirCaches[dir]
if cache == nil {
cache = NewDirCache(dir)
dirCaches[dir] = cache
}

calls, written, skipped, err := src.ProcessFile(file, cache)
if err != nil {
errMsg := fmt.Sprintf("Error processing %s: %v", file, err)
output.Error = &errMsg
return output, fmt.Errorf("%s", errMsg)
}

if written {
dataFilesWritten++
}
if skipped {
dataFilesSkipped++
}

for _, call := range calls {
allCalls = append(allCalls, call)
speciesCount[call.EbirdCode]++
}

filesProcessed++

// Delete if requested and successfully processed
if input.Delete && written {
if err := os.Remove(file); err != nil {
errMsg := fmt.Sprintf("Failed to delete %s: %v", file, err)
output.Error = &errMsg
return output, fmt.Errorf("%s", errMsg)
}
filesDeleted++
}

if input.ProgressHandler != nil {
input.ProgressHandler(filesProcessed, len(files), filepath.Base(file))
}
}

// Sort all calls by file, then start time
sort.Slice(allCalls, func(i, j int) bool {
if allCalls[i].File != allCalls[j].File {
return allCalls[i].File < allCalls[j].File
}
return allCalls[i].StartTime < allCalls[j].StartTime
})

output.Calls = allCalls
output.TotalCalls = len(allCalls)
output.SpeciesCount = speciesCount
output.DataFilesWritten = dataFilesWritten
output.DataFilesSkipped = dataFilesSkipped
output.FilesProcessed = filesProcessed
output.FilesDeleted = filesDeleted

return output, nil
}

// sourceJob represents a single file to process (generic over CallSource)
type sourceJob struct {
filePath string
}

// sourceResult represents the result of processing a single source file
type sourceResult struct {
path string
calls []ClusteredCall
written bool
skipped bool
err error
}

func (r sourceResult) filePath() string { return r.path }
func (r sourceResult) getCalls() []ClusteredCall { return r.calls }
func (r sourceResult) wasWritten() bool { return r.written }
func (r sourceResult) wasSkipped() bool { return r.skipped }
func (r sourceResult) getError() error { return r.err }

// callsFromSourceParallel processes source files concurrently using a worker pool and DirCache
func callsFromSourceParallel(src CallSource, input CallsFromSourceInput, files []string) (CallsFromSourceOutput, error) {
var output CallsFromSourceOutput
output.Filter = src.Name()

total := len(files)
var processed atomic.Int32

// Build DirCache for the folder
dirCaches := &sync.Map{}
if input.Folder != "" {
cache := NewDirCache(input.Folder)
dirCaches.Store(input.Folder, cache)
}

// Create job and result channels
jobs := make(chan sourceJob, total)
results := make(chan parallelResult, total)

// Start workers
var wg sync.WaitGroup
for range DOT_DATA_WORKERS {
wg.Add(1)
go sourceWorker(src, dirCaches, jobs, results, &wg)
}

// Send jobs
for _, file := range files {
jobs <- sourceJob{filePath: file}
}
close(jobs)

// Wait for workers to finish, then close results
go func() {
wg.Wait()
close(results)
}()

// Collect results with progress reporting
stats := aggregateResults(results, total, &processed, input.Delete, input.ProgressHandler)

if stats.firstErr != nil {
errMsg := stats.firstErr.Error()
output.Error = &errMsg
return output, stats.firstErr
}

sortCallsByFileAndTime(stats.calls)

output.Calls = stats.calls
output.TotalCalls = len(stats.calls)
output.SpeciesCount = stats.speciesCount
output.DataFilesWritten = stats.dataFilesWritten
output.DataFilesSkipped = stats.dataFilesSkipped
output.FilesProcessed = stats.filesProcessed
output.FilesDeleted = stats.filesDeleted

return output, nil
}

// sourceWorker processes source files from the jobs channel
func sourceWorker(src CallSource, dirCaches *sync.Map, jobs <-chan sourceJob, results chan<- parallelResult, wg *sync.WaitGroup) {
defer wg.Done()

for job := range jobs {
dir := filepath.Dir(job.filePath)

// Get or create DirCache for this directory
var cache *DirCache
if cached, ok := dirCaches.Load(dir); ok {
cache = cached.(*DirCache)
} else {
cache = NewDirCache(dir)
dirCaches.Store(dir, cache)
}

calls, written, skipped, err := src.ProcessFile(job.filePath, cache)
results <- sourceResult{
path: job.filePath,
calls: calls,
written: written,
skipped: skipped,
err: err,
}
}
}
replacement in tools/calls_from_birda.go at line 9

[6.516036]→[6.516036:516044](∅→∅)

"sort"

[6.516036]

[6.516044]

"strconv"
edit in tools/calls_from_birda.go at line 11

[6.516055]→[6.516055:516078](∅→∅)

"sync"
"sync/atomic"
edit in tools/calls_from_birda.go at line 34

[6.517043]→[6.517043:517572](∅→∅)

}

// BirdNETDetection represents a single BirdNET detection
type BirdNETDetection struct {
StartTime float64
EndTime float64
ScientificName string
CommonName string
Confidence float64
WAVPath string
}

// birdaJob represents a single BirdNET file to process
type birdaJob struct {
birdaFile string
}

// birdaResult represents the result of processing a single BirdNET file
type birdaResult struct {
birdaFile string
calls []ClusteredCall
written bool
skipped bool
err error
edit in tools/calls_from_birda.go at line 35

[6.517574]→[6.10670:11012](∅→∅),[6.11012]→[6.517574:518301](∅→∅),[6.517574]→[6.517574:518301](∅→∅)

func (r birdaResult) filePath() string { return r.birdaFile }
func (r birdaResult) getCalls() []ClusteredCall { return r.calls }
func (r birdaResult) wasWritten() bool { return r.written }
func (r birdaResult) wasSkipped() bool { return r.skipped }
func (r birdaResult) getError() error { return r.err }

// CallsFromBirda processes BirdNET results files and writes .data files
func CallsFromBirda(input CallsFromBirdaInput) (CallsFromBirdaOutput, error) {
var output CallsFromBirdaOutput
output.Filter = "BirdNET"

// Collect BirdNET files to process
var birdaFiles []string
if input.File != "" {
birdaFiles = []string{input.File}
} else if input.Folder != "" {
var err error
birdaFiles, err = findBirdaFiles(input.Folder)
if err != nil {
errMsg := fmt.Sprintf("Failed to find BirdNET files: %v", err)
output.Error = &errMsg
return output, fmt.Errorf("%s", errMsg)
}
} else {
errMsg := "Either --folder or --file must be specified"
output.Error = &errMsg
return output, fmt.Errorf("%s", errMsg)
}
replacement in tools/calls_from_birda.go at line 36

[6.518302]→[6.518302:518436](∅→∅)

if len(birdaFiles) == 0 {
errMsg := "No BirdNET files found"
output.Error = &errMsg
return output, fmt.Errorf("%s", errMsg)
}

[6.518302]

[6.518436]

// birdaSource implements CallSource for BirdNET results files
type birdaSource struct{}
replacement in tools/calls_from_birda.go at line 39

[6.518437]→[6.518437:518600](∅→∅)

// Single file or small batch: process sequentially (avoid goroutine overhead)
if len(birdaFiles) < 10 {
return callsFromBirdaSequential(input, birdaFiles)
}

[6.518437]

[6.518600]

func (birdaSource) Name() string { return "BirdNET" }
replacement in tools/calls_from_birda.go at line 41

[6.518601]→[6.518601:521412](∅→∅),[6.521412]→[6.11013:11058](∅→∅),[6.11058]→[6.521454:521845](∅→∅),[6.521454]→[6.521454:521845](∅→∅),[6.521845]→[6.11059:11151](∅→∅),[6.11151]→[6.522649:522650](∅→∅),[6.522649]→[6.522649:522650](∅→∅),[6.522650]→[6.11152:11215](∅→∅),[6.11215]→[6.522855:522880](∅→∅),[6.522855]→[6.522855:522880](∅→∅),[6.522880]→[6.11216:11248](∅→∅),[6.11248]→[6.522906:522910](∅→∅),[6.522906]→[6.522906:522910](∅→∅),[6.522910]→[6.11249:11286](∅→∅),[6.11286]→[6.523151:523152](∅→∅),[6.523151]→[6.523151:523152](∅→∅),[6.523152]→[6.11287:11583](∅→∅),[6.11583]→[6.523412:523497](∅→∅),[6.523412]→[6.523412:523497](∅→∅),[6.523497]→[6.11584:11697](∅→∅),[6.11697]→[6.523607:524254](∅→∅),[6.523607]→[6.523607:524254](∅→∅)

// Large batch: parallel processing with DirCache
return callsFromBirdaParallel(input, birdaFiles)
}

// callsFromBirdaSequential processes BirdNET files one at a time (for small batches)
func callsFromBirdaSequential(input CallsFromBirdaInput, birdaFiles []string) (CallsFromBirdaOutput, error) {
var output CallsFromBirdaOutput
output.Filter = "BirdNET"

// Build DirCache once for the folder
dirCaches := make(map[string]*DirCache)
if input.Folder != "" {
dirCaches[input.Folder] = NewDirCache(input.Folder)
}

speciesCount := make(map[string]int)
var allCalls []ClusteredCall
dataFilesWritten := 0
dataFilesSkipped := 0
filesProcessed := 0
filesDeleted := 0

for _, birdaFile := range birdaFiles {
dir := filepath.Dir(birdaFile)
cache := dirCaches[dir]
if cache == nil {
cache = NewDirCache(dir)
dirCaches[dir] = cache
}

calls, written, skipped, err := processBirdaFileCached(birdaFile, cache)
if err != nil {
errMsg := fmt.Sprintf("Error processing %s: %v", birdaFile, err)
output.Error = &errMsg
return output, fmt.Errorf("%s", errMsg)
}

if written {
dataFilesWritten++
}
if skipped {
dataFilesSkipped++
}

for _, call := range calls {
allCalls = append(allCalls, call)
speciesCount[call.EbirdCode]++
}

filesProcessed++

// Delete if requested and successfully processed
if input.Delete && written {
if err := os.Remove(birdaFile); err != nil {
errMsg := fmt.Sprintf("Failed to delete %s: %v", birdaFile, err)
output.Error = &errMsg
return output, fmt.Errorf("%s", errMsg)
}
filesDeleted++
}

if input.ProgressHandler != nil {
input.ProgressHandler(filesProcessed, len(birdaFiles), filepath.Base(birdaFile))
}
}

// Sort all calls by file, then start time
sort.Slice(allCalls, func(i, j int) bool {
if allCalls[i].File != allCalls[j].File {
return allCalls[i].File < allCalls[j].File
}
return allCalls[i].StartTime < allCalls[j].StartTime
})

output.Calls = allCalls
output.TotalCalls = len(allCalls)
output.SpeciesCount = speciesCount
output.DataFilesWritten = dataFilesWritten
output.DataFilesSkipped = dataFilesSkipped
output.FilesProcessed = filesProcessed
output.FilesDeleted = filesDeleted

return output, nil
}

// callsFromBirdaParallel processes BirdNET files concurrently using a worker pool and DirCache
func callsFromBirdaParallel(input CallsFromBirdaInput, birdaFiles []string) (CallsFromBirdaOutput, error) {
var output CallsFromBirdaOutput
output.Filter = "BirdNET"

total := len(birdaFiles)
var processed atomic.Int32

// Build DirCache for the folder
dirCaches := &sync.Map{}
if input.Folder != "" {
cache := NewDirCache(input.Folder)
dirCaches.Store(input.Folder, cache)
}

// Create job and result channels
jobs := make(chan birdaJob, total)
results := make(chan parallelResult, total)

// Start workers
var wg sync.WaitGroup
for range DOT_DATA_WORKERS {
wg.Add(1)
go birdaWorker(dirCaches, jobs, results, &wg)
}

// Send jobs
for _, birdaFile := range birdaFiles {
jobs <- birdaJob{birdaFile: birdaFile}
}
close(jobs)

// Wait for workers to finish, then close results
go func() {
wg.Wait()
close(results)
}()

// Collect results with progress reporting
stats := aggregateResults(results, total, &processed, input.Delete, input.ProgressHandler)

if stats.firstErr != nil {
errMsg := stats.firstErr.Error()
output.Error = &errMsg
return output, stats.firstErr
}

sortCallsByFileAndTime(stats.calls)

output.Calls = stats.calls
output.TotalCalls = len(stats.calls)
output.SpeciesCount = stats.speciesCount
output.DataFilesWritten = stats.dataFilesWritten
output.DataFilesSkipped = stats.dataFilesSkipped
output.FilesProcessed = stats.filesProcessed
output.FilesDeleted = stats.filesDeleted

return output, nil
}

// birdaWorker processes BirdNET files from the jobs channel
func birdaWorker(dirCaches *sync.Map, jobs <-chan birdaJob, results chan<- parallelResult, wg *sync.WaitGroup) {
defer wg.Done()

for job := range jobs {
dir := filepath.Dir(job.birdaFile)

// Get or create DirCache for this directory
var cache *DirCache
if cached, ok := dirCaches.Load(dir); ok {
cache = cached.(*DirCache)
} else {
cache = NewDirCache(dir)
dirCaches.Store(dir, cache)
}

calls, written, skipped, err := processBirdaFileCached(job.birdaFile, cache)
results <- birdaResult{
birdaFile: job.birdaFile,
calls: calls,
written: written,
skipped: skipped,
err: err,
}
}
}

// findBirdaFiles finds all BirdNET results files in a folder
func findBirdaFiles(folder string) ([]string, error) {

[6.518601]

[6.524254]

func (birdaSource) FindFiles(folder string) ([]string, error) {
edit in tools/calls_from_birda.go at line 57

[6.524542]

[6.524542]

}

func (birdaSource) ProcessFile(birdaFile string, cache *DirCache) ([]ClusteredCall, bool, bool, error) {
return processBirdaFileCached(birdaFile, cache)
edit in tools/calls_from_birda.go at line 62

[6.524544]

[6.524544]

// CallsFromBirda processes BirdNET results files and writes .data files
func CallsFromBirda(input CallsFromBirdaInput) (CallsFromBirdaOutput, error) {
src := birdaSource{}
commonInput := CallsFromSourceInput(input)
edit in tools/calls_from_birda.go at line 68

[6.524545]

[6.17035]

commonOutput, err := callsFromSource(src, commonInput)

// Convert to Birda-specific output type
var output CallsFromBirdaOutput
output.Calls = commonOutput.Calls
output.TotalCalls = commonOutput.TotalCalls
output.SpeciesCount = commonOutput.SpeciesCount
output.DataFilesWritten = commonOutput.DataFilesWritten
output.DataFilesSkipped = commonOutput.DataFilesSkipped
output.FilesProcessed = commonOutput.FilesProcessed
output.FilesDeleted = commonOutput.FilesDeleted
output.Filter = commonOutput.Filter
output.Error = commonOutput.Error
return output, err
}

// BirdNETDetection represents a single BirdNET detection
type BirdNETDetection struct {
StartTime float64
EndTime float64
ScientificName string
CommonName string
Confidence float64
WAVPath string
}
replacement in tools/calls_from_birda.go at line 146

[6.525982]→[6.18133:18307](∅→∅)

if _, err := fmt.Sscanf(record[idx.startIdx], "%f", &det.StartTime); err != nil {
return nil, fmt.Errorf("failed to parse start time %q: %w", record[idx.startIdx], err)

[6.525982]

[6.526162]

startTime, perr := strconv.ParseFloat(record[idx.startIdx], 64)
if perr != nil {
return nil, fmt.Errorf("failed to parse start time %q: %w", record[idx.startIdx], perr)
replacement in tools/calls_from_birda.go at line 150

[6.526166]→[6.18308:18474](∅→∅)

if _, err := fmt.Sscanf(record[idx.endIdx], "%f", &det.EndTime); err != nil {
return nil, fmt.Errorf("failed to parse end time %q: %w", record[idx.endIdx], err)

[6.526166]

[6.526338]

det.StartTime = startTime

endTime, perr := strconv.ParseFloat(record[idx.endIdx], 64)
if perr != nil {
return nil, fmt.Errorf("failed to parse end time %q: %w", record[idx.endIdx], perr)
edit in tools/calls_from_birda.go at line 156

[6.526342]

[6.18475]

det.EndTime = endTime
replacement in tools/calls_from_birda.go at line 159

[6.18520]→[6.18520:18705](∅→∅)

if _, err := fmt.Sscanf(record[idx.confidenceIdx], "%f", &det.Confidence); err != nil {
return nil, fmt.Errorf("failed to parse confidence %q: %w", record[idx.confidenceIdx], err)

[6.18520]

[6.526574]

confidence, perr := strconv.ParseFloat(record[idx.confidenceIdx], 64)
if perr != nil {
return nil, fmt.Errorf("failed to parse confidence %q: %w", record[idx.confidenceIdx], perr)
edit in tools/calls_from_birda.go at line 164

[6.526578]

[6.18706]

det.Confidence = confidence
edit in tools/bulk_file_import.go at line 205

[6.632253]→[6.632253:632626](∅→∅)

// Verify dataset exists and is active
var datasetExists bool
err = database.QueryRow("SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ? AND active = true)", input.DatasetID).Scan(&datasetExists)
if err != nil {
return fmt.Errorf("failed to query dataset: %w", err)
}
if !datasetExists {
return fmt.Errorf("dataset not found or inactive: %s", input.DatasetID)
}
replacement in tools/bulk_file_import.go at line 206

[6.632627]→[6.632627:632715](∅→∅)

// Verify dataset is 'structured' type (file imports only support structured datasets)

[6.632627]

[6.5833]

// Verify dataset exists and is structured
edit in db/validation.go at line 4

[6.23]

[6.23]

"context"
edit in db/validation.go at line 8

[6.48]

[6.48]

// Querier is the common interface for *sql.DB and *LoggedTx query operations.
type Querier interface {
QueryRow(query string, args ...any) *sql.Row
QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}
replacement in db/validation.go at line 17

[6.131]→[6.131:211](∅→∅)

func GetDatasetType(database *sql.DB, datasetID string) (string, bool, error) {

[6.131]

[6.211]

func GetDatasetType(q Querier, datasetID string) (string, bool, error) {
replacement in db/validation.go at line 19

[6.235]→[6.235:332](∅→∅)

err := database.QueryRow("SELECT type FROM dataset WHERE id = ?", datasetID).Scan(&datasetType)

[6.235]

[6.332]

err := q.QueryRow("SELECT type FROM dataset WHERE id = ?", datasetID).Scan(&datasetType)
replacement in db/validation.go at line 31

[6.621]→[6.621:764](∅→∅)

func ValidateDatasetTypeForImport(database *sql.DB, datasetID string) error {
datasetType, exists, err := GetDatasetType(database, datasetID)

[6.621]

[6.764]

func ValidateDatasetTypeForImport(q Querier, datasetID string) error {
datasetType, exists, err := GetDatasetType(q, datasetID)
replacement in db/validation.go at line 47

[6.1240]→[6.1240:1386](∅→∅)

func ValidateDatasetTypeUnstructured(database *sql.DB, datasetID string) error {
datasetType, exists, err := GetDatasetType(database, datasetID)

[6.1240]

[6.1386]

func ValidateDatasetTypeUnstructured(q Querier, datasetID string) error {
datasetType, exists, err := GetDatasetType(q, datasetID)
replacement in db/validation.go at line 63

[6.1886]→[6.1886:1980](∅→∅)

func ValidateLocationBelongsToDataset(database *sql.DB, locationID, datasetID string) error {

[6.1886]

[6.1980]

func ValidateLocationBelongsToDataset(q Querier, locationID, datasetID string) error {
replacement in db/validation.go at line 65

[6.2010]→[6.2010:2139](∅→∅)

err := database.QueryRow("SELECT dataset_id FROM location WHERE id = ? AND active = true", locationID).Scan(&locationDatasetID)

[6.2010]

[6.2139]

err := q.QueryRow("SELECT dataset_id FROM location WHERE id = ? AND active = true", locationID).Scan(&locationDatasetID)
edit in db/validation.go at line 77

[6.2458]

// DatasetExistsAndActive checks that a dataset exists and is active.
// Returns the dataset name if found.
func DatasetExistsAndActive(q Querier, datasetID string) (name string, err error) {
var exists, active bool
err = q.QueryRow(
"SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ?), COALESCE((SELECT active FROM dataset WHERE id = ?), false), COALESCE((SELECT name FROM dataset WHERE id = ?), '')",
datasetID, datasetID, datasetID,
).Scan(&exists, &active, &name)
if err != nil {
return "", fmt.Errorf("failed to verify dataset: %w", err)
}
if !exists {
return "", fmt.Errorf("dataset with ID '%s' does not exist", datasetID)
}
if !active {
return "", fmt.Errorf("dataset '%s' (ID: %s) is not active", name, datasetID)
}
return name, nil
}

// LocationBelongsToDataset checks that a location exists, is active, and belongs to the dataset.
// Returns the location name if found.
func LocationBelongsToDataset(q Querier, locationID, datasetID string) (name string, err error) {
var exists, active bool
var locDatasetID string
err = q.QueryRow(
"SELECT EXISTS(SELECT 1 FROM location WHERE id = ?), COALESCE((SELECT active FROM location WHERE id = ?), false), COALESCE((SELECT name FROM location WHERE id = ?), ''), COALESCE((SELECT dataset_id FROM location WHERE id = ?), '')",
locationID, locationID, locationID, locationID,
).Scan(&exists, &active, &name, &locDatasetID)
if err != nil {
return "", fmt.Errorf("failed to verify location: %w", err)
}
if !exists {
return "", fmt.Errorf("location with ID '%s' does not exist", locationID)
}
if !active {
return "", fmt.Errorf("location '%s' (ID: %s) is not active", name, locationID)
}
if locDatasetID != datasetID {
return "", fmt.Errorf("location '%s' (ID: %s) does not belong to dataset ID '%s'",
name, locationID, locDatasetID)
}
return name, nil
}

// ClusterBelongsToLocation checks that a cluster exists, is active, and belongs to the location.
func ClusterBelongsToLocation(q Querier, clusterID, locationID string) error {
var exists, active bool
var clusterLocationID string
err := q.QueryRow(
"SELECT EXISTS(SELECT 1 FROM cluster WHERE id = ?), COALESCE((SELECT active FROM cluster WHERE id = ?), false), COALESCE((SELECT location_id FROM cluster WHERE id = ?), '')",
clusterID, clusterID, clusterID,
).Scan(&exists, &active, &clusterLocationID)
if err != nil {
return fmt.Errorf("failed to verify cluster: %w", err)
}
if !exists {
return fmt.Errorf("cluster with ID '%s' does not exist", clusterID)
}
if !active {
return fmt.Errorf("cluster '%s' is not active", clusterID)
}
if clusterLocationID != locationID {
return fmt.Errorf("cluster '%s' does not belong to location '%s'", clusterID, locationID)
}
return nil
}
edit in db/db.go at line 4

[6.1028041]

[6.1028041]

"context"
edit in db/db.go at line 30

[6.1028772]

[6.1028772]

}

// WithReadDB opens a read-only DB connection, calls fn, and closes the connection.
// This is a convenience wrapper that ensures the connection is always closed.
func WithReadDB(dbPath string, fn func(*sql.DB) error) error {
database, err := OpenReadOnlyDB(dbPath)
if err != nil {
return fmt.Errorf("database connection failed: %w", err)
}
defer database.Close()
return fn(database)
edit in db/db.go at line 43

[6.1028775]

[6.1028775]

// WithWriteTx opens a writeable DB connection, begins a logged transaction, calls fn,
// and commits on success (or rollbacks on error). The connection is always closed.
// The fn callback receives both the *sql.DB (for pre-validation queries) and the
// *LoggedTx (for mutation operations).
func WithWriteTx(ctx context.Context, dbPath, toolName string, fn func(*sql.DB, *LoggedTx) error) error {
database, err := OpenWriteableDB(dbPath)
if err != nil {
return fmt.Errorf("database connection failed: %w", err)
}
defer database.Close()

tx, err := BeginLoggedTx(ctx, database, toolName)
if err != nil {
return fmt.Errorf("failed to begin transaction: %w", err)
}
defer tx.Rollback() // no-op after commit

if err := fn(database, tx); err != nil {
return err
}
return tx.Commit()
}
replacement in README.md at line 364

[2.1645]→[2.1645:1701](∅→∅)

# Lines of code
make count

# Go unit tests
make unit

[2.1645]

[2.1701]

```
edit in README.md at line 366

[2.1702]→[2.1702:1801](∅→∅)

# Shell script integration tests (make sure db/test.duckdb available and FK's applied)
make shell
edit in README.md at line 367

[2.1805]

[2.1805]

# Keep cyclomatic complexity low
gocyclo -over 10 .
```
edit in CHANGELOG.md at line 4

[6.1198010]

[5.4524]

## [2026-05-05] tools/ refactoring: WithWriteTx, CallSource interface, hierarchy primitives, strconv
edit in CHANGELOG.md at line 7

[5.4525]

[5.4525]

Four refactoring changes in tools/ and db/:

- **Added `db.WithWriteTx` and `db.WithReadDB` helpers** (db/db.go): Extracted the
open-DB→begin-tx→defer-rollback→commit→close-DB boilerplate that was repeated across
14+ tool entry points. `WithWriteTx(ctx, dbPath, name, fn)` opens a writeable DB, begins
a logged transaction, calls fn, and commits on success / rollbacks on error. `WithReadDB`
does the same for read-only connections. Applied to all create/update functions in
cluster, dataset, location, and pattern, plus import_unstructured, import_files (validation),
sql, and bulk_file_import (validation). Eliminates inconsistent rollback handling
(some used `defer tx.Rollback()`, others `defer func() { if err != nil { tx.Rollback() } }()`)
and removes ~100 lines of boilerplate.

- **Introduced `CallSource` interface** (tools/calls_from_common.go): Extracted shared
scaffolding from `calls_from_raven.go` and `calls_from_birda.go` into a `CallSource`
interface with `Name()`, `FindFiles()`, and `ProcessFile()` methods. Both files now
implement the interface and delegate to `callsFromSource()`, which handles the
sequential/parallel dispatch, DirCache management, worker pool, and result aggregation.
Public API (`CallsFromRaven`, `CallsFromBirda`) unchanged. ~100 lines saved.

- **Extracted hierarchy validation primitives** (db/validation.go): Added `Querier`
interface, `DatasetExistsAndActive()`, `LocationBelongsToDataset()`, and
`ClusterBelongsToLocation()` to db/validation.go. Replaced three near-duplicate
functions: `validateSegmentHierarchy` (import_segments.go, 14→7 cyclomatic),
`validateHierarchyIDs` (import_files.go, 14→7 cyclomatic), and
`verifyClusterParentRefs` plus its helpers `verifyDatasetForCluster` and
`verifyLocationForCluster` (cluster.go, deleted ~50 lines). Also replaced inline
dataset-exists-and-active checks in bulk_file_import.go and import_unstructured.go.

- **`fmt.Sscanf("%f", ...)→ strconv.ParseFloat` and deleted `extractFilename`**: Replaced
7 `fmt.Sscanf` calls in calls_from_raven.go and calls_from_birda.go with
`strconv.ParseFloat` (faster, idiomatic). Deleted `extractFilename()` from
calls_from_preds.go — it was a one-line wrapper over `filepath.Base`.

Functions at cyclomatic >13 reduced from 9 to 6.