quietlight/skraak_mcp - Change 76377HW6NPWGB77BON4ZDDVMWD4QHQARAMUTJGZCIW7PE2MVTUAAC

i dropped 2 tables, ebird_v2024 and species_dataset, needed changes in tools/export tools/import_ml_selections mainly.

Created by quietlight on February 19, 2026

76377HW6NPWGB77BON4ZDDVMWD4QHQARAMUTJGZCIW7PE2MVTUAAC

Dependencies

In channels

main

Change contents

Deletion in tools/import_ml_selections.go at line 425 [4.22689]
B:BD[4.35030] → [4.35030:35081]
```
			JOIN species_dataset sd ON s.id = sd.species_id
```
Deletion in tools/import_ml_selections.go at line 426 [4.22689]
B:BD[4.35144] → [4.35144:35171]
```
			  AND sd.dataset_id = ?
```
Replacement in tools/import_ml_selections.go at line 428 [4.22689]
B:BD[4.35200] → [4.35200:35252]
```
		args := make([]interface{}, len(speciesLabels)+1)
```
[4.35200]
[4.35252]
```
		args := make([]interface{}, len(speciesLabels))
```
Deletion in tools/import_ml_selections.go at line 432 [4.22689]
B:BD[4.35315] → [4.35315:35354]
```
		args[len(speciesLabels)] = datasetID
```
Deletion in tools/import_ml_selections.go at line 786 [4.22689]
B:BD[4.45175] → [4.45175:45235]
```
		          JOIN species_dataset sd ON s.id = sd.species_id
```

Replacement in tools/import_ml_selections.go at line 787 [4.22689]

B:BD[4.45307] → [4.45307:45416]

		            AND sd.dataset_id = ? AND s.active = true`
		args := make([]interface{}, len(speciesLabels)+1)

[4.45307]

[4.45416]

		            AND s.active = true`
		args := make([]interface{}, len(speciesLabels))

Deletion in tools/import_ml_selections.go at line 792 [4.22689]
B:BD[4.45471] → [4.45471:45510]
```
		args[len(speciesLabels)] = datasetID
```

Replacement in tools/export.go at line 36 [5.1]

B:BD[5.1116] → [5.1116:1432]

	Table       string // table name
	Relation    string // "owned" | "owned-via" | "referenced" | "derived"
	FilterCol   string // column to filter on
	ViaTable    string // for referenced: junction table
	ViaColumn   string // for referenced: column in junction table
	SourceTable string // for derived: source table

[5.1116]

[5.1432]

	Table     string // table name
	Relation  string // "owned" | "owned-via" | "copy"
	FilterCol string // column to filter on
	ViaTable  string // for owned-via: table to join through

Deletion in tools/export.go at line 50 [5.1]

B:BD[5.1893] → [5.1893:1966]

	{Table: "species_dataset", Relation: "owned", FilterCol: "dataset_id"},

Replacement in tools/export.go at line 59 [5.1]

B:BD[5.2547] → [5.2547:2582]

B:BD[5.2828] → [5.2828:3054]

	// Referenced (subset extraction)
	{Table: "species", Relation: "referenced", FilterCol: "id", ViaTable: "species_dataset", ViaColumn: "species_id"},
	{Table: "call_type", Relation: "referenced", FilterCol: "species_id", ViaTable: "species", ViaColumn: "id"},

[5.2547]

[5.3179]

	// Referenced (subset extraction) - none remaining

Replacement in tools/export.go at line 63 [5.1]

∅:D[3.80] → [5.3212:3264]

B:BD[5.3212] → [5.3212:3264]

	{Table: "ebird_taxonomy_v2024", Relation: "copy"},

[3.80]

[3.81]

	{Table: "species", Relation: "copy"},
	{Table: "call_type", Relation: "copy"},

Replacement in tools/export.go at line 161 [5.1]
B:BD[5.6009] → [5.6009:6047]
```
		switch tr.Relation {
		case "copy":
```
[5.6009]
[5.6047]
```
		if tr.Relation == "copy" {
```

Replacement in tools/export.go at line 164 [5.1]

B:BD[5.6125] → [5.6125:6263]

		case "derived":
			// Derived tables are populated from already-copied data
			err = populateDerivedTable(ctx, outputDB, tr)
		default:

[5.6125]

[5.6263]

		} else {
			// Owned or owned-via: filter by dataset

Replacement in tools/export.go at line 223 [5.1]

B:BD[5.7836] → [5.7836:7905]

		// For CREATE TABLE ... AS SELECT, create table structure manually

[5.7836]

[5.7905]

		// Skip CREATE TABLE AS SELECT statements - they don't work on empty database

Replacement in tools/export.go at line 225 [5.1]

B:BD[5.7943] → [5.7943:8481]

			createStmt := buildDerivedTableCreate(stmt.TableName)
			if createStmt != "" {
				if _, err := database.Exec(createStmt); err != nil {
					database.Close()
					return nil, fmt.Errorf("failed to create table %s: %w", stmt.TableName, err)
				}
			}
		} else {
			if _, err := database.Exec(stmt.SQL); err != nil {
				// Ignore "already exists" errors for types
				if !strings.Contains(err.Error(), "already exists") {
					database.Close()
					return nil, fmt.Errorf("failed to execute DDL for %s: %w", stmt.TableName, err)
				}

[5.7943]

[5.8481]

			continue
		}
		if _, err := database.Exec(stmt.SQL); err != nil {
			// Ignore "already exists" errors for types
			if !strings.Contains(err.Error(), "already exists") {
				database.Close()
				return nil, fmt.Errorf("failed to execute DDL for %s: %w", stmt.TableName, err)

Deletion in tools/export.go at line 239 [5.1]

B:BD[5.8519] → [5.8519:8966]

// buildDerivedTableCreate builds a CREATE TABLE statement for tables defined as CREATE TABLE AS SELECT
func buildDerivedTableCreate(tableName string) string {
	switch tableName {
	case "ebird_taxonomy_v2024":
		return `CREATE TABLE ebird_taxonomy_v2024 (
			id VARCHAR(12),
			species_code VARCHAR(15),
			primary_com_name VARCHAR(100),
			sci_name VARCHAR(100),
			bird_order VARCHAR(30),
			family VARCHAR(100)
		)`
	default:
		return ""
	}
}

Deletion in tools/export.go at line 262 [5.1]

B:BD[5.9859] → [5.9859:9957]


	case "referenced":
		// Subset via junction table
		query = buildReferencedQuery(tr, datasetID)

Deletion in tools/export.go at line 297 [5.1]

B:BD[5.11322] → [5.11322:13596]

	}
}
// buildReferencedQuery builds a query for referenced tables
func buildReferencedQuery(tr TableRelationship, datasetID string) string {
	switch tr.Table {
	case "ebird_taxonomy":
		// Join via species.ebird_code and filter by species_dataset
		return `INSERT INTO ebird_taxonomy SELECT DISTINCT t.* 
			FROM source.ebird_taxonomy t
			INNER JOIN source.species s ON t.species_code = s.ebird_code AND t.taxonomy_version = s.taxonomy_version
			WHERE s.id IN (SELECT species_id FROM source.species_dataset WHERE dataset_id = ?)`
	case "filter":
		// Filter via label -> selection -> dataset_id
		return `INSERT INTO filter SELECT DISTINCT f.* 
			FROM source.filter f
			WHERE f.id IN (
				SELECT l.filter_id FROM source.label l
				INNER JOIN source.selection s ON l.selection_id = s.id
				WHERE s.dataset_id = ?
			)`
	case "call_type":
		// Call types for species in this dataset
		return fmt.Sprintf(`INSERT INTO %s SELECT * FROM source.%s 
			WHERE %s IN (SELECT id FROM source.species WHERE id IN 
				(SELECT species_id FROM source.species_dataset WHERE dataset_id = ?))`,
			tr.Table, tr.Table, tr.FilterCol)
	case "cyclic_recording_pattern":
		// Patterns via cluster.dataset_id
		return `INSERT INTO cyclic_recording_pattern SELECT DISTINCT p.* 
			FROM source.cyclic_recording_pattern p
			WHERE p.id IN (
				SELECT c.cyclic_recording_pattern_id FROM source.cluster c
				WHERE c.dataset_id = ? AND c.cyclic_recording_pattern_id IS NOT NULL
			)`
	default:
		// Generic: filter via junction table
		return fmt.Sprintf(`INSERT INTO %s SELECT * FROM source.%s 
			WHERE %s IN (SELECT %s FROM source.%s WHERE dataset_id = ?)`,
			tr.Table, tr.Table, tr.FilterCol, tr.ViaColumn, tr.ViaTable)
	}
}
// populateDerivedTable populates a derived table from already-copied data
func populateDerivedTable(ctx context.Context, outputDB *sql.DB, tr TableRelationship) error {
	switch tr.Table {
	case "ebird_taxonomy_v2024":
		// Insert from already-copied ebird_taxonomy
		_, err := outputDB.ExecContext(ctx, `
			INSERT INTO ebird_taxonomy_v2024
			SELECT id, species_code, primary_com_name, sci_name, bird_order, family
			FROM ebird_taxonomy
			WHERE taxonomy_version = '2024'
		`)
		return err
	default:
		return fmt.Errorf("unknown derived table: %s", tr.Table)

Deletion in tools/export.go at line 316 [5.1]

B:BD[5.14148] → [5.14148:14584]

	case "referenced":
		query = buildCountReferencedQuery(tr)
	case "derived":
		// Count what would be derived based on already-referenced data
		if tr.Table == "ebird_taxonomy_v2024" {
			query = `SELECT COUNT(*) FROM ebird_taxonomy WHERE taxonomy_version = '2024' 
				AND species_code IN (SELECT ebird_code FROM species WHERE id IN 
					(SELECT species_id FROM species_dataset WHERE dataset_id = ?))`
		} else {
			return 0, nil
		}

Replacement in tools/export.go at line 350 [5.1]

B:BD[5.15861] → [5.15861:17102]

	switch tr.Table {
	case "ebird_taxonomy":
		return `SELECT COUNT(DISTINCT ebird_taxonomy.id) FROM ebird_taxonomy 
			INNER JOIN species ON ebird_taxonomy.species_code = species.ebird_code 
				AND ebird_taxonomy.taxonomy_version = species.taxonomy_version
			WHERE species.id IN (SELECT species_id FROM species_dataset WHERE dataset_id = ?)`
	case "filter":
		// Filter via label -> selection -> dataset_id
		return `SELECT COUNT(DISTINCT filter.id) FROM filter 
			INNER JOIN label ON filter.id = label.filter_id
			INNER JOIN selection ON label.selection_id = selection.id
			WHERE selection.dataset_id = ?`
	case "call_type":
		return fmt.Sprintf(`SELECT COUNT(*) FROM %s WHERE %s IN 
			(SELECT id FROM species WHERE id IN 
				(SELECT species_id FROM species_dataset WHERE dataset_id = ?))`, tr.Table, tr.FilterCol)
	case "cyclic_recording_pattern":
		return `SELECT COUNT(DISTINCT cyclic_recording_pattern.id) FROM cyclic_recording_pattern 
			INNER JOIN cluster ON cyclic_recording_pattern.id = cluster.cyclic_recording_pattern_id
			WHERE cluster.dataset_id = ?`
	default:
		return fmt.Sprintf(`SELECT COUNT(*) FROM %s WHERE %s IN 
			(SELECT %s FROM %s WHERE dataset_id = ?)`, tr.Table, tr.FilterCol, tr.ViaColumn, tr.ViaTable)
	}

[5.15861]

[5.17102]

	// Only filter table is referenced via label -> selection -> dataset_id
	return `SELECT COUNT(DISTINCT filter.id) FROM filter 
		INNER JOIN label ON filter.id = label.filter_id
		INNER JOIN selection ON label.selection_id = selection.id
		WHERE selection.dataset_id = ?`

Deletion in tools/export.go at line 371 [5.1]

B:BD[5.17582] → [5.17582:17583]

B:BD[5.17853] → [5.17853:18310]


		// Derived tables come after their source tables
		if ti.Relation == "derived" && tj.Relation != "derived" {
			return false
		}
		if tj.Relation == "derived" && ti.Relation != "derived" {
			return true
		}
		if ti.Relation == "derived" && tj.Relation == "derived" {
			// Order derived tables by their source table
			si, _ := orderMap[ti.SourceTable]
			sj, _ := orderMap[tj.SourceTable]
			return si < sj
		}
		// Use FK order for non-derived tables

Deletion in resources/schema.go at line 32 [6.114683]
B:BD[6.115137] → [6.115137:115182]
```
	"ebird_taxonomy_v2024",
	"species_dataset",
```

Replacement in resources/schema.go at line 55 [6.114683]

B:BD[6.115877] → [6.115877:116185]

		Description: "SQL schema for a specific table. Available tables: dataset, location, cyclic_recording_pattern, cluster, file, moth_metadata, file_metadata, file_dataset, selection, selection_metadata, ebird_taxonomy, species, call_type, filter, label, label_subtype, ebird_taxonomy_v2024, species_dataset",

[6.115877]

[6.116185]

		Description: "SQL schema for a specific table. Available tables: dataset, location, cyclic_recording_pattern, cluster, file, moth_metadata, file_metadata, file_dataset, selection, selection_metadata, ebird_taxonomy, species, call_type, filter, label, label_subtype",

Deletion in prompts/examples.go at line 130 [6.120572]
B:BD[4.64297] → [4.64297:64360]
```
- **ebird_taxonomy_v2024** - Materialized view for fast lookup
```

Deletion in db/schema_test.go at line 26 [5.22251]

B:BD[5.22847] → [5.22847:22955]

	}
	if !strings.Contains(schema, "ebird_taxonomy_v2024") {
		t.Error("schema missing ebird_taxonomy_v2024")

Deletion in db/schema_test.go at line 107 [5.22251]

B:BD[5.25167] → [5.25167:25418]

			name:      "CREATE TABLE AS SELECT",
			sql:       "CREATE TABLE ebird_taxonomy_v2024 AS SELECT id, species_code FROM ebird_taxonomy WHERE taxonomy_version = '2024';",
			wantType:  "CREATE_TABLE_AS",
			wantTable: "ebird_taxonomy_v2024",
		},
		{

Replacement in db/schema_test.go at line 179 [5.22251]

B:BD[5.27064] → [5.27064:27176]

			sql:  "CREATE TABLE ebird_taxonomy_v2024 AS SELECT id FROM ebird_taxonomy",
			want: "ebird_taxonomy_v2024",

[5.27064]

[5.27176]

			sql:  "CREATE TABLE my_table AS SELECT id FROM source",
			want: "my_table",

Deletion in db/schema.sql at line 221 [6.305331]

B:BD[7.148986] → [7.148986:149375]

∅:D[7.149375] → [6.318958:318961]

B:BD[6.318958] → [6.318958:318961]

B:BD[6.319341] → [6.319341:319342]

B:BD[6.319546] → [6.319546:319781]

CREATE TABLE species_dataset (
    species_id VARCHAR(12) NOT NULL,
    dataset_id VARCHAR(12) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (species_id, dataset_id),
    FOREIGN KEY (species_id) REFERENCES species(id),
    FOREIGN KEY (dataset_id) REFERENCES dataset(id)
);
CREATE TABLE ebird_taxonomy_v2024 AS
SELECT
    id,
    species_code,
    primary_com_name,
    sci_name,
    bird_order, -- AS "order",  order is reserved word in pgsql
    family
FROM ebird_taxonomy
WHERE taxonomy_version = '2024';

Deletion in db/schema.sql at line 240 [6.305331]
∅:D[7.150472] → [6.320621:320694]
B:BD[6.320621] → [6.320621:320694]
B:BD[6.320694] → [7.150473:150546]
```
CREATE INDEX idx_species_dataset_dataset ON species_dataset(dataset_id);
CREATE INDEX idx_species_dataset_species ON species_dataset(species_id);
```

Replacement in db/schema.sql at line 242 [6.305331]

B:BD[2.304] → [2.304:367]

CREATE UNIQUE INDEX idx_species_label_unique ON species(label);

[2.304]

CREATE UNIQUE INDEX idx_species_label_unique ON species(label);

Insertion in CHANGELOG.md at line 4 [8.1]

[8.81]

[5.44833]


## [2026-02-19] Schema Simplification - Remove species_dataset and ebird_taxonomy_v2024
**Database schema changes:**
- Dropped `species_dataset` table — all species now available across all datasets
- Dropped `ebird_taxonomy_v2024` table — use `WHERE taxonomy_version = '2024'` on `ebird_taxonomy` instead
**Rationale:**
- Simplifies species management (no duplicate species names across datasets)
- Reduces schema complexity (one fewer join for species lookups)
- `ebird_taxonomy_v2024` was redundant; filtering `ebird_taxonomy` directly is sufficient
**Code changes:**
- `tools/export.go` — Simplified manifest: `species` and `call_type` now "copy" (full table)
- `tools/export.go` — Removed `buildDerivedTableCreate()`, `populateDerivedTable()`, simplified `buildReferencedQuery()`
- `tools/import_ml_selections.go` — Species lookup no longer joins `species_dataset`
- `resources/schema.go` — Removed tables from list
- `db/schema_test.go` — Removed obsolete test cases
- `prompts/examples.go` — Updated taxonomy schema description
**Export manifest changes:**
- `species_dataset` → removed (no longer exists)
- `ebird_taxonomy_v2024` → removed (no longer exists)
- `species` → changed from "referenced" to "copy"
- `call_type` → changed from "referenced" to "copy"
- `filter` → changed from "referenced" to "copy"
- All "referenced" and "derived" handling code removed
---

Replacement in CHANGELOG.md at line 61 [8.1]

B:BD[5.46084] → [3.249:449]

- Subset of reference data (species, call_types used by dataset via species_dataset)
- Reference tables copied in full (`ebird_taxonomy`, `ebird_taxonomy_v2024`, `cyclic_recording_pattern`, `filter`)

[5.46084]

[5.46213]

- Reference tables copied in full (`ebird_taxonomy`, `species`, `call_type`, `cyclic_recording_pattern`, `filter`)

Insertion in .ignore at line 12 [10.1]
[9.27529]
```
*.parquet
```