quietlight/skraak_mcp - Change X3K56A54LNNXODOH6MK22NTSEUQ54BUEZ3EL6ANKXYNL4RROL73QC

added validation to tools, not sure I will leave it in tools though, maybe a util

Created by quietlight on February 13, 2026

X3K56A54LNNXODOH6MK22NTSEUQ54BUEZ3EL6ANKXYNL4RROL73QC

Dependencies

In channels

main

Change contents

File addition: validation_test.go (----------)

[3.22715]

package tools
import (
	"testing"
)
func TestValidateShortID(t *testing.T) {
	tests := []struct {
		name      string
		id        string
		fieldName string
		wantErr   bool
	}{
		{"valid 12-char ID", "abc123XYZ789", "test_id", false},
		{"valid with underscore", "abc_123_XYZ_", "test_id", false},
		{"valid with dash", "abc-123-XYZ-", "test_id", false},
		{"empty string", "", "test_id", true},
		{"too short", "abc123", "test_id", true},
		{"too long", "abc123XYZ789toolong", "test_id", true},
		{"invalid chars", "abc@123#XYZ$", "test_id", true},
	}
	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			err := ValidateShortID(tt.id, tt.fieldName)
			if (err != nil) != tt.wantErr {
				t.Errorf("ValidateShortID() error = %v, wantErr %v", err, tt.wantErr)
			}
		})
	}
}
func TestValidateLongID(t *testing.T) {
	tests := []struct {
		name      string
		id        string
		fieldName string
		wantErr   bool
	}{
		{"valid 21-char ID", "abc123XYZ789abc123XYZ", "test_id", false}, // exactly 21 chars
		{"valid with underscore", "abc_123_XYZ_789_abc_X", "test_id", false}, // exactly 21 chars
		{"empty string", "", "test_id", true},
		{"too short", "abc123XYZ789", "test_id", true}, // 12 chars
		{"too long", "abc123XYZ789abc123XYZ789ex", "test_id", true}, // 24 chars
		{"invalid chars", "abc@123#XYZ$789%abc^XY", "test_id", true},
	}
	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			err := ValidateLongID(tt.id, tt.fieldName)
			if (err != nil) != tt.wantErr {
				t.Errorf("ValidateLongID() error = %v, wantErr %v", err, tt.wantErr)
			}
		})
	}
}
func TestValidateStringLength(t *testing.T) {
	tests := []struct {
		name     string
		value    string
		field    string
		maxLen   int
		wantErr  bool
	}{
		{"within limit", "hello", "test", 10, false},
		{"at limit", "1234567890", "test", 10, false},
		{"empty string", "", "test", 10, false},
		{"over limit", "12345678901", "test", 10, true},
		{"zero max", "a", "test", 0, true},
	}
	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			err := ValidateStringLength(tt.value, tt.field, tt.maxLen)
			if (err != nil) != tt.wantErr {
				t.Errorf("ValidateStringLength() error = %v, wantErr %v", err, tt.wantErr)
			}
		})
	}
}
func TestValidateRange(t *testing.T) {
	t.Run("int range", func(t *testing.T) {
		tests := []struct {
			name    string
			value   int
			min     int
			max     int
			wantErr bool
		}{
			{"within range", 50, 0, 100, false},
			{"at min", 0, 0, 100, false},
			{"at max", 100, 0, 100, false},
			{"below min", -1, 0, 100, true},
			{"above max", 101, 0, 100, true},
		}
		for _, tt := range tests {
			t.Run(tt.name, func(t *testing.T) {
				err := ValidateRange(tt.value, "test", tt.min, tt.max)
				if (err != nil) != tt.wantErr {
					t.Errorf("ValidateRange() error = %v, wantErr %v", err, tt.wantErr)
				}
			})
		}
	})
	t.Run("float64 range", func(t *testing.T) {
		tests := []struct {
			name    string
			value   float64
			min     float64
			max     float64
			wantErr bool
		}{
			{"within range", 45.5, -90.0, 90.0, false},
			{"at min", -90.0, -90.0, 90.0, false},
			{"at max", 90.0, -90.0, 90.0, false},
			{"below min", -90.1, -90.0, 90.0, true},
			{"above max", 90.1, -90.0, 90.0, true},
		}
		for _, tt := range tests {
			t.Run(tt.name, func(t *testing.T) {
				err := ValidateRange(tt.value, "test", tt.min, tt.max)
				if (err != nil) != tt.wantErr {
					t.Errorf("ValidateRange() error = %v, wantErr %v", err, tt.wantErr)
				}
			})
		}
	})
}
func TestValidatePositive(t *testing.T) {
	tests := []struct {
		name    string
		value   int
		wantErr bool
	}{
		{"positive", 1, false},
		{"large positive", 1000000, false},
		{"zero", 0, true},
		{"negative", -1, true},
	}
	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			err := ValidatePositive(tt.value, "test")
			if (err != nil) != tt.wantErr {
				t.Errorf("ValidatePositive() error = %v, wantErr %v", err, tt.wantErr)
			}
		})
	}
}
func TestValidateSampleRate(t *testing.T) {
	tests := []struct {
		name    string
		rate    int
		wantErr bool
	}{
		{"valid low", 1000, false},
		{"valid typical", 48000, false},
		{"valid high", 250000, false},
		{"valid max", 500000, false},
		{"too low", 999, true},
		{"too high", 500001, true},
		{"zero", 0, true},
		{"negative", -1000, true},
	}
	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			err := ValidateSampleRate(tt.rate)
			if (err != nil) != tt.wantErr {
				t.Errorf("ValidateSampleRate() error = %v, wantErr %v", err, tt.wantErr)
			}
		})
	}
}
func TestValidateHash(t *testing.T) {
	tests := []struct {
		name    string
		hash    string
		wantErr bool
	}{
		{"valid hash", "0123456789abcdef", false},
		{"valid all letters", "abcdefabcdefabcd", false},
		{"valid all numbers", "1234567890123456", false},
		{"too short", "0123456789abcde", true},
		{"too long", "0123456789abcdef0", true},
		{"invalid chars", "ghijklmnopqrstuv", true},
		{"uppercase", "ABCDEF1234567890", true},
		{"mixed case", "aBcDeF1234567890", true},
		{"empty", "", true},
	}
	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			err := ValidateHash(tt.hash, "test_hash")
			if (err != nil) != tt.wantErr {
				t.Errorf("ValidateHash() error = %v, wantErr %v", err, tt.wantErr)
			}
		})
	}
}
func TestValidateFrequencyRange(t *testing.T) {
	low := func(v float64) *float64 { return &v }
	high := func(v float64) *float64 { return &v }
	tests := []struct {
		name    string
		freqLow *float64
		freqHigh *float64
		wantErr bool
	}{
		{"both nil", nil, nil, false},
		{"valid range", low(100.0), high(1000.0), false},
		{"valid low only", low(100.0), nil, false},
		{"valid high only", nil, high(1000.0), false},
		{"at bounds", low(0.0), high(299999.0), false},
		{"low negative", low(-1.0), high(1000.0), true},
		{"high too high", low(100.0), high(300000.0), true},
		{"low equals high", low(500.0), high(500.0), true},
		{"low greater than high", low(1000.0), high(100.0), true},
	}
	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			err := ValidateFrequencyRange(tt.freqLow, tt.freqHigh)
			if (err != nil) != tt.wantErr {
				t.Errorf("ValidateFrequencyRange() error = %v, wantErr %v", err, tt.wantErr)
			}
		})
	}
}
func TestValidateCertainty(t *testing.T) {
	val := func(v float64) *float64 { return &v }
	tests := []struct {
		name      string
		certainty *float64
		wantErr   bool
	}{
		{"nil", nil, false},
		{"zero", val(0.0), false},
		{"mid range", val(50.0), false},
		{"100", val(100.0), false},
		{"negative", val(-0.1), true},
		{"over 100", val(100.1), true},
		{"much over", val(200.0), true},
	}
	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			err := ValidateCertainty(tt.certainty)
			if (err != nil) != tt.wantErr {
				t.Errorf("ValidateCertainty() error = %v, wantErr %v", err, tt.wantErr)
			}
		})
	}
}
func TestValidateTimezone(t *testing.T) {
	tests := []struct {
		name    string
		tz      string
		wantErr bool
	}{
		{"valid Auckland", "Pacific/Auckland", false},
		{"valid UTC", "UTC", false},
		{"valid America/New_York", "America/New_York", false},
		{"valid Europe/London", "Europe/London", false},
		{"invalid", "Invalid/Timezone", true},
		{"garbage", "not-a-timezone", true},
	}
	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			err := ValidateTimezone(tt.tz)
			if (err != nil) != tt.wantErr {
				t.Errorf("ValidateTimezone() error = %v, wantErr %v", err, tt.wantErr)
			}
		})
	}
}
func TestValidateNonNegative(t *testing.T) {
	tests := []struct {
		name    string
		value   int
		wantErr bool
	}{
		{"positive", 1, false},
		{"zero", 0, false},
		{"negative", -1, true},
	}
	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			err := ValidateNonNegative(tt.value, "test")
			if (err != nil) != tt.wantErr {
				t.Errorf("ValidateNonNegative() error = %v, wantErr %v", err, tt.wantErr)
			}
		})
	}
}

File addition: validation.go (----------)

[3.22715]

package tools
import (
	"database/sql"
	"fmt"
	"regexp"
	"time"
)
// ID length constants matching nanoid generation
const (
	ShortIDLen = 12 // dataset, location, cluster, pattern, species, filter, call_type
	LongIDLen  = 21 // file, selection, label, label_subtype
)
// Sample rate reasonable bounds for audio recording
const (
	MinSampleRate = 1000   // 1 kHz - below this is unlikely to be real audio
	MaxSampleRate = 500000 // 500 kHz - well above bat detectors (~250kHz)
)
// Max string lengths from schema
const (
	MaxNameLen        = 140  // location.name, cluster.name
	MaxDatasetNameLen = 255  // dataset.name
	MaxDescriptionLen = 255  // all description fields
	MaxPathLen        = 255  // cluster.path
	MaxFileNameLen    = 255  // file.file_name
	MaxTimezoneLen    = 40   // location.timezone_id
	HashLen           = 16   // xxh64_hash
)
// ID format regex - alphanumeric characters (nanoid uses A-Za-z0-9_)
var shortIDRegex = regexp.MustCompile(`^[A-Za-z0-9_-]{12}$`)
var longIDRegex = regexp.MustCompile(`^[A-Za-z0-9_-]{21}$`)
var hashRegex = regexp.MustCompile(`^[0-9a-f]{16}$`)
// ValidateShortID validates 12-character nanoid format
func ValidateShortID(id, fieldName string) error {
	if id == "" {
		return fmt.Errorf("%s cannot be empty", fieldName)
	}
	if len(id) != ShortIDLen {
		return fmt.Errorf("%s must be exactly %d characters (got %d)", fieldName, ShortIDLen, len(id))
	}
	if !shortIDRegex.MatchString(id) {
		return fmt.Errorf("%s has invalid format (expected alphanumeric nanoid)", fieldName)
	}
	return nil
}
// ValidateLongID validates 21-character nanoid format
func ValidateLongID(id, fieldName string) error {
	if id == "" {
		return fmt.Errorf("%s cannot be empty", fieldName)
	}
	if len(id) != LongIDLen {
		return fmt.Errorf("%s must be exactly %d characters (got %d)", fieldName, LongIDLen, len(id))
	}
	if !longIDRegex.MatchString(id) {
		return fmt.Errorf("%s has invalid format (expected alphanumeric nanoid)", fieldName)
	}
	return nil
}
// ValidateOptionalShortID validates short ID if provided (non-empty)
func ValidateOptionalShortID(id *string, fieldName string) error {
	if id == nil || *id == "" {
		return nil
	}
	return ValidateShortID(*id, fieldName)
}
// ValidateOptionalLongID validates long ID if provided (non-empty)
func ValidateOptionalLongID(id *string, fieldName string) error {
	if id == nil || *id == "" {
		return nil
	}
	return ValidateLongID(*id, fieldName)
}
// ValidateStringLength validates string length constraint
func ValidateStringLength(value, fieldName string, maxLen int) error {
	if len(value) > maxLen {
		return fmt.Errorf("%s must be %d characters or less (got %d)", fieldName, maxLen, len(value))
	}
	return nil
}
// ValidateOptionalStringLength validates string length if provided
func ValidateOptionalStringLength(value *string, fieldName string, maxLen int) error {
	if value == nil || *value == "" {
		return nil
	}
	return ValidateStringLength(*value, fieldName, maxLen)
}
// ValidateRange validates numeric range constraint (inclusive)
func ValidateRange[T int | float64](value T, fieldName string, min, max T) error {
	if value < min || value > max {
		return fmt.Errorf("%s must be between %v and %v (got %v)", fieldName, min, max, value)
	}
	return nil
}
// ValidatePositive validates positive number (> 0)
func ValidatePositive[T int | float64](value T, fieldName string) error {
	if value <= 0 {
		return fmt.Errorf("%s must be positive (got %v)", fieldName, value)
	}
	return nil
}
// ValidateNonNegative validates non-negative number (>= 0)
func ValidateNonNegative[T int | float64](value T, fieldName string) error {
	if value < 0 {
		return fmt.Errorf("%s must be non-negative (got %v)", fieldName, value)
	}
	return nil
}
// ValidateSampleRate validates audio sample rate is in reasonable range
func ValidateSampleRate(rate int) error {
	return ValidateRange(rate, "sample_rate", MinSampleRate, MaxSampleRate)
}
// ValidateTimezone validates IANA timezone ID
func ValidateTimezone(tzID string) error {
	if _, err := time.LoadLocation(tzID); err != nil {
		return fmt.Errorf("invalid timezone_id '%s': %w", tzID, err)
	}
	return nil
}
// ValidateHash validates XXH64 hash format (16 hex characters)
func ValidateHash(hash, fieldName string) error {
	if len(hash) != HashLen {
		return fmt.Errorf("%s must be exactly %d characters (got %d)", fieldName, HashLen, len(hash))
	}
	if !hashRegex.MatchString(hash) {
		return fmt.Errorf("%s has invalid format (expected 16 hex characters)", fieldName)
	}
	return nil
}
// ValidateFrequencyRange validates frequency bounds for selections
func ValidateFrequencyRange(freqLow, freqHigh *float64) error {
	if freqLow == nil && freqHigh == nil {
		return nil
	}
	// Schema limit is 300000 Hz
	const maxFreq = 300000.0
	if freqLow != nil {
		if *freqLow < 0 {
			return fmt.Errorf("freq_low must be non-negative (got %v)", *freqLow)
		}
		if *freqLow >= maxFreq {
			return fmt.Errorf("freq_low must be less than %v Hz (got %v)", maxFreq, *freqLow)
		}
	}
	if freqHigh != nil {
		if *freqHigh < 0 {
			return fmt.Errorf("freq_high must be non-negative (got %v)", *freqHigh)
		}
		if *freqHigh >= maxFreq {
			return fmt.Errorf("freq_high must be less than %v Hz (got %v)", maxFreq, *freqHigh)
		}
	}
	// If both provided, low must be less than high
	if freqLow != nil && freqHigh != nil && *freqLow >= *freqHigh {
		return fmt.Errorf("freq_low (%v) must be less than freq_high (%v)", *freqLow, *freqHigh)
	}
	return nil
}
// ValidateCertainty validates certainty percentage (0-100)
func ValidateCertainty(certainty *float64) error {
	if certainty == nil {
		return nil
	}
	if *certainty < 0 || *certainty > 100 {
		return fmt.Errorf("certainty must be between 0 and 100 (got %v)", *certainty)
	}
	return nil
}
// EntityExists checks if an entity exists in the database
func EntityExists(db *sql.DB, table, id string) (bool, error) {
	var exists bool
	query := fmt.Sprintf("SELECT EXISTS(SELECT 1 FROM %s WHERE id = ?)", table)
	err := db.QueryRow(query, id).Scan(&exists)
	return exists, err
}
// EntityExistsAndActive checks if an entity exists and is active
func EntityExistsAndActive(db *sql.DB, table, id string) (bool, error) {
	var exists bool
	query := fmt.Sprintf("SELECT EXISTS(SELECT 1 FROM %s WHERE id = ? AND active = true)", table)
	err := db.QueryRow(query, id).Scan(&exists)
	return exists, err
}
// GetEntityActiveStatus returns whether an entity is active (and exists)
// Returns: (exists, active, error)
func GetEntityActiveStatus(db *sql.DB, table, id string) (bool, bool, error) {
	var exists bool
	var active bool
	query := fmt.Sprintf("SELECT EXISTS(SELECT 1 FROM %s WHERE id = ?), COALESCE((SELECT active FROM %s WHERE id = ?), false)", table, table)
	err := db.QueryRow(query, id, id).Scan(&exists, &active)
	return exists, active, err
}

Deletion in tools/pattern.go at line 25 [4.4745]

B:BD[4.5756] → [5.13745:13829]

// CreateOrUpdatePattern creates a new recording pattern or updates an existing one

Replacement in tools/pattern.go at line 46 [4.4745]

B:BD[4.6532] → [4.6532:6564]

B:BD[4.6564] → [5.14109:14203]

	if *input.RecordSeconds <= 0 {
		return output, fmt.Errorf("record_seconds must be positive (got %d)", *input.RecordSeconds)

[4.6532]

[4.6663]

	if err := ValidatePositive(*input.RecordSeconds, "record_seconds"); err != nil {
		return output, err

Replacement in tools/pattern.go at line 49 [4.4745]

B:BD[4.6666] → [4.6666:6697]

B:BD[4.6697] → [5.14204:14296]

	if *input.SleepSeconds <= 0 {
		return output, fmt.Errorf("sleep_seconds must be positive (got %d)", *input.SleepSeconds)

[4.6666]

[4.6794]

	if err := ValidatePositive(*input.SleepSeconds, "sleep_seconds"); err != nil {
		return output, err

Insertion in tools/pattern.go at line 142 [4.4745]

[4.9973]

	// Validate ID format
	if err := ValidateShortID(patternID, "pattern_id"); err != nil {
		return output, err
	}

Replacement in tools/pattern.go at line 148 [4.4745]

B:BD[4.10005] → [4.10005:10067]

B:BD[4.10067] → [5.15059:15154]

	if input.RecordSeconds != nil && *input.RecordSeconds <= 0 {
		return output, fmt.Errorf("record_seconds must be greater than 0: %d", *input.RecordSeconds)

[4.10005]

[4.10167]

	if input.RecordSeconds != nil {
		if err := ValidatePositive(*input.RecordSeconds, "record_seconds"); err != nil {
			return output, err
		}

Replacement in tools/pattern.go at line 153 [4.4745]

B:BD[4.10170] → [4.10170:10229]

B:BD[4.10229] → [5.15155:15260]

	if input.SleepSeconds != nil && *input.SleepSeconds < 0 {
		return output, fmt.Errorf("sleep_seconds must be greater than or equal to 0: %d", *input.SleepSeconds)

[4.10170]

[4.10339]

	if input.SleepSeconds != nil {
		if err := ValidateNonNegative(*input.SleepSeconds, "sleep_seconds"); err != nil {
			return output, err
		}

Replacement in tools/pattern.go at line 166 [4.4745]

B:BD[4.10529] → [4.10529:10692]

	// Verify pattern exists
	var exists bool
	err = database.QueryRow("SELECT EXISTS(SELECT 1 FROM cyclic_recording_pattern WHERE id = ?)", patternID).Scan(&exists)

[4.10529]

[4.10692]

	// Verify pattern exists and check active status
	var exists, active bool
	err = database.QueryRow(
		"SELECT EXISTS(SELECT 1 FROM cyclic_recording_pattern WHERE id = ?), COALESCE((SELECT active FROM cyclic_recording_pattern WHERE id = ?), false)",
		patternID, patternID,
	).Scan(&exists, &active)

Insertion in tools/pattern.go at line 177 [4.4745]

[5.15455]

[4.10864]

	}
	if !active {
		return output, fmt.Errorf("pattern '%s' is not active (cannot update inactive patterns)", patternID)

Deletion in tools/location.go at line 29 [4.12317]
B:BD[4.13742] → [5.15678:15754]
```
// CreateOrUpdateLocation creates a new location or updates an existing one
```

Replacement in tools/location.go at line 42 [4.12317]

B:BD[4.14241] → [4.14241:14377]

	if input.Name != nil && len(*input.Name) > 140 {
		return fmt.Errorf("name must be 140 characters or less (got %d)", len(*input.Name))

[4.14241]

[4.14377]

	if err := ValidateOptionalStringLength(input.Name, "name", MaxNameLen); err != nil {
		return err

Replacement in tools/location.go at line 45 [4.12317]

B:BD[4.14380] → [4.14380:14544]

	if input.Description != nil && len(*input.Description) > 255 {
		return fmt.Errorf("description must be 255 characters or less (got %d)", len(*input.Description))

[4.14380]

[4.14544]

	if err := ValidateOptionalStringLength(input.Description, "description", MaxDescriptionLen); err != nil {
		return err

Replacement in tools/location.go at line 48 [4.12317]

B:BD[4.14547] → [4.14547:14711]

	if input.Latitude != nil && (*input.Latitude < -90 || *input.Latitude > 90) {
		return fmt.Errorf("latitude must be between -90 and 90 (got %f)", *input.Latitude)

[4.14547]

[4.14711]

	if input.Latitude != nil {
		if err := ValidateRange(*input.Latitude, "latitude", -90.0, 90.0); err != nil {
			return err
		}

Replacement in tools/location.go at line 53 [4.12317]

B:BD[4.14714] → [4.14714:14887]

	if input.Longitude != nil && (*input.Longitude < -180 || *input.Longitude > 180) {
		return fmt.Errorf("longitude must be between -180 and 180 (got %f)", *input.Longitude)

[4.14714]

[4.14887]

	if input.Longitude != nil {
		if err := ValidateRange(*input.Longitude, "longitude", -180.0, 180.0); err != nil {
			return err
		}

Replacement in tools/location.go at line 59 [4.12317]

B:BD[4.14920] → [4.14920:15054]

		if len(*input.TimezoneID) > 40 {
			return fmt.Errorf("timezone_id must be 40 characters or less (got %d)", len(*input.TimezoneID))

[4.14920]

[4.15054]

		if err := ValidateStringLength(*input.TimezoneID, "timezone_id", MaxTimezoneLen); err != nil {
			return err

Insertion in tools/location.go at line 87 [4.12317]

[5.16264]

[4.16095]

	}
	// Validate ID format for dataset_id
	if err := ValidateShortID(*input.DatasetID, "dataset_id"); err != nil {
		return output, err

Replacement in tools/location.go at line 117 [4.12317]

B:BD[4.16639] → [4.16639:16711]

	var datasetExists bool
	var datasetActive bool
	var datasetName string

[4.16639]

[4.16711]

	var datasetExists, datasetActive bool

Replacement in tools/location.go at line 119 [4.12317]

B:BD[4.16742] → [4.16742:16837]

		"SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ?), active, name FROM dataset WHERE id = ?",

[4.16742]

[4.16837]

		"SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ?), COALESCE((SELECT active FROM dataset WHERE id = ?), false)",

Replacement in tools/location.go at line 121 [4.12317]
B:BD[4.16875] → [4.16875:16929]
```
	).Scan(&datasetExists, &datasetActive, &datasetName)
```
[4.16875]
[4.16929]
```
	).Scan(&datasetExists, &datasetActive)
```

Replacement in tools/location.go at line 129 [4.12317]

B:BD[4.17154] → [5.16576:16674]

		return output, fmt.Errorf("dataset '%s' (ID: %s) is not active", datasetName, *input.DatasetID)

[4.17154]

[4.17257]

		return output, fmt.Errorf("dataset (ID: %s) is not active", *input.DatasetID)
	}
	// 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 {
		// Location with this name already exists in dataset - return existing (consistent duplicate handling)
		var location db.Location
		err = tx.QueryRowContext(ctx,
			"SELECT id, dataset_id, name, latitude, longitude, description, created_at, last_modified, active, timezone_id FROM location WHERE id = ?",
			existingID,
		).Scan(&location.ID, &location.DatasetID, &location.Name, &location.Latitude, &location.Longitude,
			&location.Description, &location.CreatedAt, &location.LastModified, &location.Active, &location.TimezoneID)
		if err != nil {
			return output, fmt.Errorf("failed to fetch existing location: %w", err)
		}
		if err = tx.Commit(); err != nil {
			return output, fmt.Errorf("failed to commit transaction: %w", err)
		}
		output.Location = location
		output.Message = fmt.Sprintf("Location '%s' already exists in dataset (ID: %s) - returning existing location", location.Name, location.ID)
		return output, nil

Replacement in tools/location.go at line 191 [4.12317]

B:BD[4.18611] → [4.18611:18826]

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

[4.18611]

[4.18826]

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

Insertion in tools/location.go at line 201 [4.12317]

[4.19035]

	// Validate ID format
	if err := ValidateShortID(locationID, "location_id"); err != nil {
		return output, err
	}

Insertion in tools/location.go at line 210 [4.12317]

[4.19120]

	// Validate dataset_id format if provided
	if err := ValidateOptionalShortID(input.DatasetID, "dataset_id"); err != nil {
		return output, err
	}

Replacement in tools/location.go at line 222 [4.12317]

B:BD[4.19306] → [4.19306:19455]

	// Verify location exists
	var exists bool
	err = database.QueryRow("SELECT EXISTS(SELECT 1 FROM location WHERE id = ?)", locationID).Scan(&exists)

[4.19306]

[4.19455]

	// Verify location exists and check active status
	var exists, active bool
	var currentDatasetID string
	err = database.QueryRow(
		"SELECT EXISTS(SELECT 1 FROM location WHERE id = ?), COALESCE((SELECT active FROM location WHERE id = ?), false), COALESCE((SELECT dataset_id FROM location WHERE id = ?), '')",
		locationID, locationID, locationID,
	).Scan(&exists, &active, &currentDatasetID)

Insertion in tools/location.go at line 234 [4.12317]

[5.17278]

[4.19630]

	}
	if !active {
		return output, fmt.Errorf("location '%s' is not active (cannot update inactive locations)", locationID)

Replacement in tools/location.go at line 239 [4.12317]

B:BD[4.19634] → [4.19634:19682]

	// Verify dataset exists if DatasetID provided

[4.19634]

[4.19682]

	// Verify dataset exists if DatasetID provided (relationship consistency)

Replacement in tools/location.go at line 241 [4.12317]

B:BD[4.19711] → [4.19711:19854]

		var datasetExists bool
		err = database.QueryRow("SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ?)", *input.DatasetID).Scan(&datasetExists)

[4.19711]

[4.19854]

		var datasetExists, datasetActive bool
		err = database.QueryRow(
			"SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ?), COALESCE((SELECT active FROM dataset WHERE id = ?), false)",
			*input.DatasetID, *input.DatasetID,
		).Scan(&datasetExists, &datasetActive)

Insertion in tools/location.go at line 252 [4.12317]

[4.20049]

		if !datasetActive {
			return output, fmt.Errorf("dataset '%s' is not active", *input.DatasetID)
		}

Insertion in tools/import_ml_selections.go at line 153 [6.22689]

[6.27660]

	// Validate ID formats first (fast fail before DB queries)
	if err := ValidateShortID(datasetID, "dataset_id"); err != nil {
		return err
	}
	if err := ValidateShortID(clusterID, "cluster_id"); err != nil {
		return err
	}

Replacement in tools/import_ml_selections.go at line 167 [6.22689]
B:BD[6.27812] → [6.27812:27838]
```
	// Verify dataset exists
```
[6.27812]
[6.27838]
```
	// Verify dataset exists and is active
```

Insertion in tools/import_files.go at line 106 [3.50297]

[3.55191]

	// Validate ID formats first (fast fail before DB queries)
	if err := ValidateShortID(input.DatasetID, "dataset_id"); err != nil {
		return err
	}
	if err := ValidateShortID(input.LocationID, "location_id"); err != nil {
		return err
	}
	if err := ValidateShortID(input.ClusterID, "cluster_id"); err != nil {
		return err
	}

Replacement in tools/import_files.go at line 133 [3.50297]
B:BD[3.55609] → [3.55609:55635]
```
	// Verify dataset exists
```
[3.55609]
[3.55635]
```
	// Verify dataset exists and is active
```

Replacement in tools/dataset.go at line 16 [4.22019]

B:BD[4.22672] → [4.22672:22805]

	Type        *string `json:"type,omitempty" jsonschema:"Dataset type: 'organise'/'test'/'train' (defaults to 'organise' on create)"`

[4.22672]

[4.22805]

	Type        *string `json:"type,omitempty" jsonschema:"Dataset type: 'structured'/'unstructured'/'test'/'train' (defaults to 'structured' on create)"`

Deletion in tools/dataset.go at line 26 [4.22019]

B:BD[5.19319] → [2.4359:4457]

// CreateOrUpdateDataset creates a new dataset or updates an existing one based on presence of ID

Replacement in tools/dataset.go at line 43 [4.22019]

B:BD[4.23708] → [4.23708:23737]

B:BD[4.23737] → [5.19507:19601]

	if len(*input.Name) > 255 {
		return output, fmt.Errorf("name must be 255 characters or less (got %d)", len(*input.Name))

[4.23708]

[4.23836]

	if err := ValidateStringLength(*input.Name, "name", MaxDatasetNameLen); err != nil {
		return output, err

Replacement in tools/dataset.go at line 48 [4.22019]

B:BD[4.23884] → [4.23884:23948]

B:BD[4.23948] → [5.19602:19710]

	if input.Description != nil && len(*input.Description) > 255 {
		return output, fmt.Errorf("description must be 255 characters or less (got %d)", len(*input.Description))

[4.23884]

[4.24061]

	if err := ValidateOptionalStringLength(input.Description, "description", MaxDescriptionLen); err != nil {
		return output, err

Insertion in tools/dataset.go at line 87 [4.22019]

[4.24949]


	// 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 {
		// Dataset with this name already exists - return existing (consistent duplicate handling)
		var dataset db.Dataset
		err = tx.QueryRowContext(ctx,
			"SELECT id, name, description, created_at, last_modified, active, type FROM dataset WHERE id = ?",
			existingID,
		).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 existing 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("Dataset with name '%s' already exists (ID: %s) - returning existing dataset", dataset.Name, dataset.ID)
		return output, nil
	}

Insertion in tools/dataset.go at line 155 [4.22019]

[4.26408]

	// Validate ID format
	if err := ValidateShortID(datasetID, "dataset_id"); err != nil {
		return output, err
	}

Replacement in tools/dataset.go at line 161 [4.22019]

B:BD[4.26440] → [4.26440:26490]

B:BD[4.26490] → [5.20331:20425]

	if input.Name != nil && len(*input.Name) > 255 {
		return output, fmt.Errorf("name must be 255 characters or less (got %d)", len(*input.Name))

[4.26440]

[4.26589]

	if err := ValidateOptionalStringLength(input.Name, "name", MaxDatasetNameLen); err != nil {
		return output, err

Replacement in tools/dataset.go at line 164 [4.22019]

B:BD[4.26592] → [4.26592:26656]

B:BD[4.26656] → [5.20426:20534]

	if input.Description != nil && len(*input.Description) > 255 {
		return output, fmt.Errorf("description must be 255 characters or less (got %d)", len(*input.Description))

[4.26592]

[4.26769]

	if err := ValidateOptionalStringLength(input.Description, "description", MaxDescriptionLen); err != nil {
		return output, err

Replacement in tools/dataset.go at line 181 [4.22019]

B:BD[4.27228] → [4.27228:27374]

	// Verify dataset exists
	var exists bool
	err = database.QueryRow("SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ?)", datasetID).Scan(&exists)

[4.27228]

[4.27374]

	// Verify dataset exists and check active status
	var exists, active bool
	err = database.QueryRow("SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ?), COALESCE((SELECT active FROM dataset WHERE id = ?), false)", datasetID, datasetID).Scan(&exists, &active)

Insertion in tools/dataset.go at line 190 [4.22019]

[4.27549]

	if !active {
		return output, fmt.Errorf("dataset '%s' is not active (cannot update inactive datasets)", datasetID)
	}

Deletion in tools/cluster.go at line 29 [4.29033]
B:BD[4.30685] → [5.21064:21138]
```
// CreateOrUpdateCluster creates a new cluster or updates an existing one
```

Replacement in tools/cluster.go at line 42 [4.29033]

B:BD[4.31174] → [4.31174:31310]

	if input.Name != nil && len(*input.Name) > 140 {
		return fmt.Errorf("name must be 140 characters or less (got %d)", len(*input.Name))

[4.31174]

[4.31310]

	if err := ValidateOptionalStringLength(input.Name, "name", MaxNameLen); err != nil {
		return err
	}
	if err := ValidateOptionalStringLength(input.Description, "description", MaxDescriptionLen); err != nil {
		return err

Replacement in tools/cluster.go at line 48 [4.29033]

B:BD[4.31313] → [4.31313:31477]

	if input.Description != nil && len(*input.Description) > 255 {
		return fmt.Errorf("description must be 255 characters or less (got %d)", len(*input.Description))

[4.31313]

[4.31477]

	if err := ValidateOptionalStringLength(input.Path, "path", MaxPathLen); err != nil {
		return err

Replacement in tools/cluster.go at line 51 [4.29033]

B:BD[4.31480] → [4.31480:31616]

	if input.SampleRate != nil && *input.SampleRate <= 0 {
		return fmt.Errorf("sample_rate must be positive (got %d)", *input.SampleRate)

[4.31480]

[4.31616]

	if input.SampleRate != nil {
		if err := ValidatePositive(*input.SampleRate, "sample_rate"); err != nil {
			return err
		}
		// Also check reasonable bounds
		if err := ValidateSampleRate(*input.SampleRate); err != nil {
			return err
		}

Insertion in tools/cluster.go at line 78 [4.29033]

[5.21564]

[4.32388]

	}
	// Validate ID formats
	if err := ValidateShortID(*input.DatasetID, "dataset_id"); err != nil {
		return output, err
	}
	if err := ValidateShortID(*input.LocationID, "location_id"); err != nil {
		return output, err

Insertion in tools/cluster.go at line 89 [4.29033]

[4.32446]

[5.21565]

		return output, err
	}
	// Validate optional pattern ID format
	if err := ValidateOptionalShortID(input.CyclicRecordingPatternID, "cyclic_recording_pattern_id"); err != nil {

Replacement in tools/cluster.go at line 116 [4.29033]
B:BD[4.32931] → [4.32931:32979]
```
	var datasetExists bool
	var datasetActive bool
```
[4.32931]
[4.32979]
```
	var datasetExists, datasetActive bool
```

Replacement in tools/cluster.go at line 119 [4.29033]

B:BD[4.33034] → [4.33034:33167]

		"SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ?), active, name FROM dataset WHERE id = ?",
		*input.DatasetID, *input.DatasetID,

[4.33034]

[4.33167]

		"SELECT EXISTS(SELECT 1 FROM dataset WHERE id = ?), COALESCE((SELECT active FROM dataset WHERE id = ?), false), COALESCE((SELECT name FROM dataset WHERE id = ?), '')",
		*input.DatasetID, *input.DatasetID, *input.DatasetID,

Replacement in tools/cluster.go at line 133 [4.29033]
B:BD[4.33629] → [4.33629:33679]
```
	var locationExists bool
	var locationActive bool
```
[4.33629]
[4.33679]
```
	var locationExists, locationActive bool
```

Replacement in tools/cluster.go at line 137 [4.29033]

B:BD[4.33765] → [4.33765:33914]

		"SELECT EXISTS(SELECT 1 FROM location WHERE id = ?), active, name, dataset_id FROM location WHERE id = ?",
		*input.LocationID, *input.LocationID,

[4.33765]

[4.33914]

		"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 = ?), '')",
		*input.LocationID, *input.LocationID, *input.LocationID, *input.LocationID,

Replacement in tools/cluster.go at line 156 [4.29033]
B:BD[4.34748] → [4.34748:34798]
```
		var patternExists bool
		var patternActive bool
```
[4.34748]
[4.34798]
```
		var patternExists, patternActive bool
```

Replacement in tools/cluster.go at line 158 [4.29033]

B:BD[4.34830] → [4.34830:34954]

			"SELECT EXISTS(SELECT 1 FROM cyclic_recording_pattern WHERE id = ?), active FROM cyclic_recording_pattern WHERE id = ?",

[4.34830]

[4.34954]

			"SELECT EXISTS(SELECT 1 FROM cyclic_recording_pattern WHERE id = ?), COALESCE((SELECT active FROM cyclic_recording_pattern WHERE id = ?), false)",

Insertion in tools/cluster.go at line 172 [4.29033]

[4.35475]

	// Check for existing cluster with same name in location (UNIQUE constraint)
	var existingID string
	err = tx.QueryRowContext(ctx,
		"SELECT id FROM cluster WHERE location_id = ? AND name = ? AND active = true",
		*input.LocationID, *input.Name,
	).Scan(&existingID)
	if err == nil {
		// Cluster with this name already exists in location - return existing (consistent duplicate handling)
		var cluster db.Cluster
		err = tx.QueryRowContext(ctx,
			"SELECT id, dataset_id, location_id, name, description, created_at, last_modified, active, cyclic_recording_pattern_id, sample_rate FROM cluster WHERE id = ?",
			existingID,
		).Scan(&cluster.ID, &cluster.DatasetID, &cluster.LocationID, &cluster.Name, &cluster.Description,
			&cluster.CreatedAt, &cluster.LastModified, &cluster.Active, &cluster.CyclicRecordingPatternID, &cluster.SampleRate)
		if err != nil {
			return output, fmt.Errorf("failed to fetch existing cluster: %w", err)
		}
		if err = tx.Commit(); err != nil {
			return output, fmt.Errorf("failed to commit transaction: %w", err)
		}
		output.Cluster = cluster
		output.Message = fmt.Sprintf("Cluster '%s' already exists in location '%s' (ID: %s) - returning existing cluster", cluster.Name, locationName, cluster.ID)
		return output, nil
	}

Insertion in tools/cluster.go at line 241 [4.29033]

[4.37291]

	// Validate ID format
	if err := ValidateShortID(clusterID, "cluster_id"); err != nil {
		return output, err
	}

Insertion in tools/cluster.go at line 250 [4.29033]

[4.37375]

	// Validate optional pattern ID format
	if input.CyclicRecordingPatternID != nil && strings.TrimSpace(*input.CyclicRecordingPatternID) != "" {
		if err := ValidateShortID(*input.CyclicRecordingPatternID, "cyclic_recording_pattern_id"); err != nil {
			return output, err
		}
	}

Replacement in tools/cluster.go at line 264 [4.29033]

B:BD[4.37561] → [4.37561:37707]

	// Verify cluster exists
	var exists bool
	err = database.QueryRow("SELECT EXISTS(SELECT 1 FROM cluster WHERE id = ?)", clusterID).Scan(&exists)

[4.37561]

[4.37707]

	// Verify cluster exists and check active status
	var exists, active bool
	err = database.QueryRow(
		"SELECT EXISTS(SELECT 1 FROM cluster WHERE id = ?), COALESCE((SELECT active FROM cluster WHERE id = ?), false)",
		clusterID, clusterID,
	).Scan(&exists, &active)

Insertion in tools/cluster.go at line 276 [4.29033]

[4.37882]

	if !active {
		return output, fmt.Errorf("cluster '%s' is not active (cannot update inactive clusters)", clusterID)
	}

Replacement in tools/cluster.go at line 284 [4.29033]
B:BD[4.38083] → [4.38083:38109]
```
			var patternExists bool
```
[4.38083]
[4.38109]
```
			var patternExists, patternActive bool
```

Replacement in tools/cluster.go at line 286 [4.29033]

B:BD[4.38137] → [4.38137:38277]

				"SELECT EXISTS(SELECT 1 FROM cyclic_recording_pattern WHERE id = ? AND active = true)",
				trimmedPatternID,
			).Scan(&patternExists)

[4.38137]

[4.38277]

				"SELECT EXISTS(SELECT 1 FROM cyclic_recording_pattern WHERE id = ?), COALESCE((SELECT active FROM cyclic_recording_pattern WHERE id = ?), false)",
				trimmedPatternID, trimmedPatternID,
			).Scan(&patternExists, &patternActive)

Replacement in tools/cluster.go at line 293 [4.29033]

B:BD[4.38413] → [5.23362:23466]

				return output, fmt.Errorf("cyclic recording pattern not found or not active: %s", trimmedPatternID)

[4.38413]

[4.38522]

				return output, fmt.Errorf("cyclic recording pattern not found: %s", trimmedPatternID)
			}
			if !patternActive {
				return output, fmt.Errorf("cyclic recording pattern '%s' is not active", trimmedPatternID)

Insertion in tools/bulk_file_import.go at line 240 [7.1]

[7.8068]

	// Validate ID format first (fast fail before DB queries)
	if err := ValidateShortID(input.DatasetID, "dataset_id"); err != nil {
		return err
	}

Replacement in tools/bulk_file_import.go at line 263 [7.1]
B:BD[7.8586] → [7.8586:8612]
```
	// Verify dataset exists
```
[7.8586]
[7.8612]
```
	// Verify dataset exists and is active
```

Insertion in tools/bulk_file_import.go at line 302 [7.1]

[7.9571]

		}
		// Validate location_id format
		locationID := record[1]
		if err := ValidateShortID(locationID, "location_id"); err != nil {
			return nil, fmt.Errorf("invalid location_id in row %d: %v", i+1, err)

Insertion in tools/bulk_file_import.go at line 312 [7.1]

[7.9639]

			return nil, fmt.Errorf("invalid sample_rate in row %d: %v", i+1, err)
		}
		// Validate sample rate is in reasonable range
		if err := ValidateSampleRate(sampleRate); err != nil {

Replacement in tools/bulk_file_import.go at line 327 [7.1]
B:BD[7.9935] → [7.9935:9964]
```
			LocationID:    record[1],
```
[7.9935]
[7.9964]
```
			LocationID:    locationID,
```

Insertion in CLAUDE.md at line 172 [3.363912]

[5.26198]

[6.68446]

│   ├── validation.go             # Application-level guardrails

Insertion in CLAUDE.md at line 290 [3.363912]

[3.368974]


### Application-Level Validation
**tools/validation.go** provides application-level guardrails for all write operations:
**ID Format Validation:**
- Short IDs (12 chars): dataset, location, cluster, pattern, species, filter
- Long IDs (21 chars): file, selection, label
- Validates alphanumeric format (nanoid compatible)

Insertion in CLAUDE.md at line 300 [3.363912]

[3.368975]

**Numeric Bounds:**
- Sample rate: 1,000 - 500,000 Hz (reasonable audio range)
- Latitude: -90 to 90 degrees
- Longitude: -180 to 180 degrees
- Frequency: 0 to 300,000 Hz (for ML selections)
- Certainty: 0 to 100 percent
**String Length Validation:**
- Names: max 140 characters (location, cluster)
- Dataset names: max 255 characters
- Descriptions: max 255 characters
- Paths: max 255 characters
- Timezone IDs: max 40 characters
**Entity Validation:**
- Existence checks before foreign key references
- Active status validation (cannot update inactive entities)
- Hierarchy consistency (location must belong to dataset, cluster to location)
**Duplicate Handling:**
- Pattern: Returns existing if same record_s/sleep_s
- Dataset: Returns existing if same name
- Location: Returns existing if same name in dataset
- Cluster: Returns existing if same name in location