/**
* Worker API implementation for Skraak
*
* This file implements the server-side API using Hono on Cloudflare Workers.
* It handles:
* - JWT-based authentication via Kinde identity provider
* - API endpoints for retrieving acoustic datasets, locations, clusters
* - File and selection management with filtering capabilities
* - Species and call type information
*
* The API connects to a Neon PostgreSQL database for data storage and retrieval.
* All endpoints except the health check (/api) require authentication.
*/
import { Context, Hono } from "hono";
import { drizzle } from "drizzle-orm/neon-http";
import { eq, and, sql as sqlExpr } from "drizzle-orm";
import { neon } from "@neondatabase/serverless";
import {
dataset,
location,
cluster,
cyclicRecordingPattern,
file,
fileMetadata,
mothMetadata,
species,
speciesDataset,
callType,
selection,
label,
accessGrant,
userRole
} from "../../db/schema";
import * as jose from 'jose';
import { nanoid } from 'nanoid';
/**
* Environment variables required for the Worker
*
* @interface Env
* @description Defines the environment variables that must be configured in the Cloudflare Workers environment
* - DATABASE_URL: Connection string for the Neon PostgreSQL database
* - KINDE_ISSUER_URL: URL of the Kinde identity provider for JWT verification
*/
export type Env = {
/** Connection string for the Neon PostgreSQL database */
DATABASE_URL: string;
/** URL of the Kinde identity provider (for JWT verification) */
KINDE_ISSUER_URL: string;
};
/**
* Structure of the JWT token payload from Kinde identity provider
*
* @interface JWTPayload
* @description Defines the structure of the JWT token payload used for authentication
* Contains standard JWT claims (sub, iat, exp, aud, iss) plus any additional custom claims
* Used for extracting user information after successful token verification
*/
interface JWTPayload {
sub: string; // Subject (user ID)
iat: number; // Issued at
exp: number; // Expiration time
aud: string[]; // Audience
iss: string; // Issuer
[key: string]: unknown; // Additional claims
}
/**
* Main Hono application instance with environment bindings
*
* @type {Hono<{Bindings: Env}>}
* @description The core Hono application instance that handles all API routes
* Uses generic typing to bind the Env interface for environment variable access
*/
const app = new Hono<{ Bindings: Env }>();
/**
* Helper function to check if a user has a specific permission for a dataset
*
* @param db - Database connection
* @param userId - User ID to check permissions for
* @param datasetId - Dataset ID to check permissions for
* @param permission - Permission to check (READ, EDIT, DELETE, etc.)
* @returns Promise<boolean> - True if user has permission, false otherwise
*/
async function checkUserPermission(
// eslint-disable-next-line @typescript-eslint/no-explicit-any
db: any,
userId: string,
datasetId: string,
permission: string
): Promise<boolean> {
try {
// First check if user is the owner (owners have all permissions)
const ownerCheck = await db
.select({ owner: dataset.owner })
.from(dataset)
.where(eq(dataset.id, datasetId))
.limit(1);
if (ownerCheck.length > 0 && ownerCheck[0].owner === userId) {
return true;
}
// Get user's role
const userRoleResult = await db
.select({ role: userRole.role })
.from(userRole)
.where(eq(userRole.userId, userId))
.limit(1);
const userRoleName = userRoleResult.length > 0 ? userRoleResult[0].role : 'USER';
// Check access grants
const grants = await db
.select({ permission: accessGrant.permission })
.from(accessGrant)
.where(sqlExpr`
${accessGrant.datasetId} = ${datasetId} AND
${accessGrant.permission} = ${permission} AND
${accessGrant.active} = true AND
(
(${accessGrant.userId} = ${userId}) OR
(${accessGrant.userId} IS NULL AND ${accessGrant.role} = ${userRoleName})
)
`)
.limit(1);
return grants.length > 0;
} catch (error) {
console.error('Error checking user permission:', error);
return false;
}
}
/**
* Public API health check endpoint
*
* @route GET /api
* @authentication None - publicly accessible
* @returns {Object} Simple status object with "ok" status
* @description Simple endpoint to verify API is running and responding
* Can be used for health checks and monitoring
*/
app.get("/api", (c) => c.json({ status: "ok" }));
/**
* Authentication middleware for protected routes
*
* Verifies JWT tokens from the Kinde identity provider using JWKS.
* On successful verification, adds the JWT payload to the context
* for use in route handlers.
*
* @param c - Hono context containing request, response, and environment bindings
* @param next - Next middleware in the chain
* @returns Response with 401 status on auth failure, or passes to next middleware
*/
const authenticate = async (c: Context<{ Bindings: Env }>, next: () => Promise<void>): Promise<Response | void> => {
// Get the authorization header
const authHeader = c.req.header("Authorization");
if (!authHeader || !authHeader.startsWith("Bearer ")) {
return c.json({ error: "Unauthorized: Missing or invalid token" }, 401);
}
try {
// Extract token
const token = authHeader.split(" ")[1];
console.log("Received token:", token.substring(0, 10) + "...");
// Get the JWKS endpoint from the Kinde issuer
const issuerUrl = c.env.KINDE_ISSUER_URL;
const jwksUrl = `${issuerUrl}/.well-known/jwks.json`;
console.log("JWKS URL:", jwksUrl);
// Fetch the JWKS
console.log("Fetching JWKS...");
const jwksResponse = await fetch(jwksUrl);
console.log("JWKS response status:", jwksResponse.status);
if (!jwksResponse.ok) {
throw new Error(`Failed to fetch JWKS: ${jwksResponse.status}`);
}
await jwksResponse.json(); // Parse JSON to validate response
console.log("JWKS keys received");
// Create JWKS from the fetched keys
const keyStore = jose.createRemoteJWKSet(new URL(jwksUrl));
console.log("Verifying token...");
console.log("Using issuer:", issuerUrl);
try {
// Verify the token with issuer only (no audience validation)
const { payload } = await jose.jwtVerify(token, keyStore, {
issuer: issuerUrl,
// No audience validation - Kinde tokens may have empty audience array
});
console.log("Token verified successfully!");
// Store the payload in the context for use in the route handler
// Use unknown-casting to work around Hono's type limitations
(c as unknown as { jwtPayload: JWTPayload }).jwtPayload = payload as JWTPayload;
} catch (verifyError) {
console.error("Token verification failed:", verifyError);
throw verifyError;
}
// If we get here, the token is valid
await next();
} catch (error) {
console.error("JWT validation error:", error);
return c.json({ error: "Unauthorized: Invalid token" }, 401);
}
};
// ============================================================================
// API ROUTES - Organized by resource
// ============================================================================
// ============================================================================
// DATASETS
// ============================================================================
/**
* Protected API route to fetch datasets with role-based access control
*
* @route GET /api/datasets
* @authentication Required
* @returns {Object} Response containing:
* - data: Array of dataset objects with permissions for the authenticated user
* - userId: The authenticated user's ID (for debugging)
* @description Returns datasets the user has READ access to, along with their specific permissions
* @example Response:
* {
* "data": [
* {
* "id": "123",
* "name": "Bird Sounds 2024",
* "description": "Costa Rica recordings",
* "public": true,
* "permissions": ["READ", "EDIT", "DELETE"]
* }
* ],
* "userId": "user_123abc"
* }
*/
app.get("/api/datasets", authenticate, async (c) => {
try {
// Get the JWT payload (user info)
const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
const userId = jwtPayload.sub; // User ID from JWT
// Connect to the database
const sql = neon(c.env.DATABASE_URL);
const db = drizzle(sql);
// First, get the user's role
const userRoleResult = await db
.select({ role: userRole.role })
.from(userRole)
.where(eq(userRole.userId, userId))
.limit(1);
const userRoleName = userRoleResult.length > 0 ? userRoleResult[0].role : 'USER';
// Query to get datasets with permissions
// This complex query gets datasets where the user has READ access either through:
// 1. Direct user-specific grants
// 2. Role-based grants for their role
// 3. Being the owner of the dataset
const results = await db
.select({
id: dataset.id,
name: dataset.name,
description: dataset.description,
public: dataset.public,
createdAt: dataset.createdAt,
owner: dataset.owner,
type: dataset.type,
permission: accessGrant.permission,
userId: accessGrant.userId
})
.from(dataset)
.leftJoin(
accessGrant,
sqlExpr`${accessGrant.datasetId} = ${dataset.id} AND ${accessGrant.active} = true AND (
(${accessGrant.userId} = ${userId}) OR
(${accessGrant.userId} IS NULL AND ${accessGrant.role} = ${userRoleName})
)`
)
.where(sqlExpr`
${dataset.active} = true AND (
${dataset.owner} = ${userId} OR
${accessGrant.permission} IS NOT NULL
)
`)
.orderBy(dataset.name);
// Group results by dataset and collect permissions
const datasetMap = new Map<string, {
id: string;
name: string;
description: string | null;
public: boolean;
createdAt: string;
owner: string;
type: string;
permissions: string[];
}>();
results.forEach(row => {
if (!datasetMap.has(row.id)) {
// If user is owner, they have all permissions
const isOwner = row.owner === userId;
const basePermissions = isOwner ? ['READ', 'UPLOAD', 'DOWNLOAD', 'EDIT', 'DELETE'] : [];
datasetMap.set(row.id, {
id: row.id,
name: row.name,
description: row.description,
public: row.public ?? false,
createdAt: row.createdAt?.toISOString() ?? new Date().toISOString(),
owner: row.owner,
type: row.type,
permissions: basePermissions
});
}
// Add permission from access grants (avoid duplicates)
if (row.permission && !datasetMap.get(row.id)!.permissions.includes(row.permission)) {
datasetMap.get(row.id)!.permissions.push(row.permission);
}
});
// Filter datasets to only include those with READ permission
const datasetsWithReadAccess = Array.from(datasetMap.values())
.filter(dataset => dataset.permissions.includes('READ'))
.slice(0, 20); // Limit to 20 datasets
return c.json({
data: datasetsWithReadAccess,
userId: userId,
userRole: userRoleName
});
} catch (error) {
console.error("Error fetching datasets:", error);
return c.json(
{
error: "Failed to fetch datasets",
details: error instanceof Error ? error.message : String(error),
},
500
);
}
});
/**
* Protected API route to create a new dataset
*
* @route POST /api/datasets
* @authentication Required
* @body {Object} Dataset creation payload:
* - id: string (nanoid(12) - user generated)
* - name: string (required, max 255 chars)
* - description?: string (optional, max 255 chars)
* - public?: boolean (optional, defaults to false)
* - type?: string (optional, defaults to 'organise')
* @returns {Object} Response containing:
* - data: The created dataset object
* @error 400 - If required fields are missing or invalid
* @error 500 - If database operation fails
* @description Creates a new dataset for the authenticated user
* The user becomes the owner, creator, and modifier of the dataset
*/
app.post("/api/datasets", authenticate, async (c) => {
try {
// Get the JWT payload (user info)
const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
const userId = jwtPayload.sub; // User ID from JWT
// Connect to the database first to check permissions
const sql = neon(c.env.DATABASE_URL);
const db = drizzle(sql);
// Check if user has permission to create datasets (ADMIN or CURATOR roles)
const userRoleResult = await db
.select({ role: userRole.role })
.from(userRole)
.where(eq(userRole.userId, userId))
.limit(1);
const userRoleName = userRoleResult.length > 0 ? userRoleResult[0].role : 'USER';
if (userRoleName !== 'ADMIN' && userRoleName !== 'CURATOR') {
return c.json({
error: "You don't have permission to create datasets"
}, 403);
}
// Parse request body
const body = await c.req.json();
const { id, name, description, public: isPublic, type } = body;
// Validate required fields
if (!id || typeof id !== 'string') {
return c.json({
error: "Missing or invalid required field: id"
}, 400);
}
if (!name || typeof name !== 'string' || name.trim().length === 0) {
return c.json({
error: "Missing or invalid required field: name"
}, 400);
}
// Validate field lengths
if (id.length !== 12) {
return c.json({
error: "Field 'id' must be exactly 12 characters (nanoid)"
}, 400);
}
if (name.length > 255) {
return c.json({
error: "Field 'name' must be 255 characters or less"
}, 400);
}
if (description && description.length > 255) {
return c.json({
error: "Field 'description' must be 255 characters or less"
}, 400);
}
// Validate type if provided
const validTypes = ['organise', 'test', 'train'];
const datasetType = type || 'organise';
if (!validTypes.includes(datasetType)) {
return c.json({
error: `Field 'type' must be one of: ${validTypes.join(', ')}`
}, 400);
}
// Create the dataset
const now = new Date();
const newDataset = {
id: id.trim(),
name: name.trim(),
description: description?.trim() || null,
public: Boolean(isPublic),
type: datasetType,
createdBy: userId,
createdAt: now,
lastModified: now,
modifiedBy: userId,
owner: userId,
active: true,
};
// Insert the dataset
const result = await db.insert(dataset).values(newDataset).returning({
id: dataset.id,
name: dataset.name,
description: dataset.description,
public: dataset.public,
type: dataset.type,
createdAt: dataset.createdAt,
owner: dataset.owner,
});
console.log("Created dataset:", result[0].id, "for user:", userId);
return c.json({
data: result[0]
}, 201);
} catch (error) {
console.error("Error creating dataset:", error);
// Handle unique constraint violations
if (error instanceof Error && error.message.includes('duplicate key')) {
return c.json({
error: "A dataset with this ID already exists"
}, 400);
}
return c.json(
{
error: "Failed to create dataset",
details: error instanceof Error ? error.message : String(error),
},
500
);
}
});
/**
* Protected API route to update an existing dataset
*
* @route PUT /api/datasets/:id
* @authentication Required
* @param {string} id - Dataset ID in URL path
* @body {Object} Dataset update payload:
* - name?: string (optional, max 255 chars)
* - description?: string (optional, max 255 chars)
* - public?: boolean (optional)
* - type?: string (optional)
* - active?: boolean (optional, for soft delete)
* @returns {Object} Response containing:
* - data: The updated dataset object
* @error 400 - If fields are invalid or dataset not found
* @error 403 - If user doesn't own the dataset
* @error 500 - If database operation fails
* @description Updates an existing dataset owned by the authenticated user
* Only the dataset owner can modify it
*/
app.put("/api/datasets/:id", authenticate, async (c) => {
try {
// Get the JWT payload (user info)
const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
const userId = jwtPayload.sub; // User ID from JWT
// Get dataset ID from URL parameters
const datasetId = c.req.param("id");
if (!datasetId) {
return c.json({
error: "Missing dataset ID in URL"
}, 400);
}
// Parse request body
const body = await c.req.json();
const { name, description, public: isPublic, type, active } = body;
// Connect to the database
const sql = neon(c.env.DATABASE_URL);
const db = drizzle(sql);
// First, check if the dataset exists and if the user owns it
const existingDataset = await db
.select({
id: dataset.id,
owner: dataset.owner,
active: dataset.active
})
.from(dataset)
.where(eq(dataset.id, datasetId))
.limit(1);
if (existingDataset.length === 0) {
return c.json({
error: "Dataset not found"
}, 404);
}
// Check if user has permission to edit this dataset
const hasEditPermission = await checkUserPermission(db, userId, datasetId, 'EDIT');
if (!hasEditPermission) {
return c.json({
error: "You don't have permission to modify this dataset"
}, 403);
}
// Validate fields if provided
if (name !== undefined) {
if (typeof name !== 'string' || name.trim().length === 0) {
return c.json({
error: "Invalid field: name must be a non-empty string"
}, 400);
}
if (name.length > 255) {
return c.json({
error: "Field 'name' must be 255 characters or less"
}, 400);
}
}
if (description !== undefined && description !== null && description.length > 255) {
return c.json({
error: "Field 'description' must be 255 characters or less"
}, 400);
}
if (type !== undefined) {
const validTypes = ['organise', 'test', 'train'];
if (!validTypes.includes(type)) {
return c.json({
error: `Field 'type' must be one of: ${validTypes.join(', ')}`
}, 400);
}
}
// Build update object with only provided fields
const updateData: Record<string, unknown> = {
lastModified: new Date(),
modifiedBy: userId,
};
if (name !== undefined) {
updateData.name = name.trim();
}
if (description !== undefined) {
updateData.description = description?.trim() || null;
}
if (isPublic !== undefined) {
updateData.public = Boolean(isPublic);
}
if (type !== undefined) {
updateData.type = type;
}
if (active !== undefined) {
updateData.active = Boolean(active);
}
// Update the dataset
const result = await db
.update(dataset)
.set(updateData)
.where(eq(dataset.id, datasetId))
.returning({
id: dataset.id,
name: dataset.name,
description: dataset.description,
public: dataset.public,
type: dataset.type,
createdAt: dataset.createdAt,
lastModified: dataset.lastModified,
owner: dataset.owner,
active: dataset.active,
});
if (result.length === 0) {
return c.json({
error: "Failed to update dataset"
}, 500);
}
console.log("Updated dataset:", result[0].id, "for user:", userId);
return c.json({
data: result[0]
});
} catch (error) {
console.error("Error updating dataset:", error);
return c.json(
{
error: "Failed to update dataset",
details: error instanceof Error ? error.message : String(error),
},
500
);
}
});
// ============================================================================
// LOCATIONS
// ============================================================================
/**
* Protected API route to fetch locations for a specific dataset
*
* @route GET /api/locations
* @authentication Required
* @param {string} datasetId - Required query parameter specifying the dataset to fetch locations from
* @param {number} [page=1] - Optional page number for pagination (starts at 1)
* @param {number} [pageSize=100] - Optional page size (1-100, defaults to 100)
* @returns {Object} Response containing:
* - data: Array of location objects with id, name, latitude, longitude, description
* - pagination: Object with pagination metadata (currentPage, pageSize, totalPages, totalItems, etc.)
* @error 400 - If datasetId is missing or page is invalid
* @description Returns active locations for the specified dataset with pagination support
*/
app.get("/api/locations", authenticate, async (c) => {
try {
// Get the JWT payload (user info)
const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
const userId = jwtPayload.sub; // User ID from JWT // Subject claim usually contains the user ID
// Get the dataset ID from query parameter
const datasetId = c.req.query("datasetId");
const page = parseInt(c.req.query("page") || "1", 10);
const pageSize = parseInt(c.req.query("pageSize") || "100", 10);
console.log("Locations API called with datasetId:", datasetId, "userId:", userId, "page:", page);
if (!datasetId) {
console.log("Missing datasetId in request");
return c.json({
error: "Missing required query parameter: datasetId"
}, 400);
}
// Validate page number
if (page < 1) {
return c.json({
error: "Invalid page parameter: must be greater than 0"
}, 400);
}
// Limit page size between 1 and 100
const limitedPageSize = Math.min(Math.max(pageSize, 1), 100);
const offset = (page - 1) * limitedPageSize;
// Connect to the database
const sql = neon(c.env.DATABASE_URL);
const db = drizzle(sql);
// First, get total count for pagination
console.log("Counting locations for datasetId:", datasetId);
const countResult = await db
.select({
count: sqlExpr<number>`COUNT(*)`
})
.from(location)
.where(sqlExpr`${location.datasetId} = ${datasetId} AND ${location.active} = true`);
const totalLocations = Number(countResult[0].count);
const totalPages = Math.ceil(totalLocations / limitedPageSize);
// Query locations for the specified dataset with pagination (using 100 as default limit)
console.log("Querying locations for datasetId:", datasetId, "page:", page);
const results = await db.select({
id: location.id,
name: location.name,
latitude: location.latitude,
longitude: location.longitude,
description: location.description,
}).from(location)
.where(sqlExpr`${location.datasetId} = ${datasetId} AND ${location.active} = true`)
.orderBy(location.name)
.limit(limitedPageSize)
.offset(offset);
console.log("Found", results.length, "locations for dataset", datasetId, "page:", page);
return c.json({
data: results,
pagination: {
currentPage: page,
pageSize: limitedPageSize,
totalPages: totalPages,
totalItems: totalLocations,
hasNextPage: page < totalPages,
hasPreviousPage: page > 1,
}
});
} catch (error) {
console.error("Error fetching locations:", error);
return c.json(
{
error: "Failed to fetch locations",
details: error instanceof Error ? error.message : String(error),
},
500
);
}
});
/**
* Protected API route to create a new location
*
* @route POST /api/locations
* @authentication Required
* @param {Object} body - Location data including datasetId, name, description, latitude, longitude
* @returns {Object} Response containing:
* - data: Created location object
* @description Creates a new location with the authenticated user as owner.
* Requires EDIT permission on the dataset to create locations within it.
*/
app.post("/api/locations", authenticate, async (c) => {
try {
// Get user ID from JWT
const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
const userId = jwtPayload.sub;
// Database connection
const sql = neon(c.env.DATABASE_URL);
const db = drizzle(sql);
// Parse and validate request body
const body = await c.req.json();
const { id, datasetId, name, description, latitude, longitude } = body;
// Field validation
if (!id || typeof id !== 'string') {
return c.json({
error: "Missing or invalid required field: id"
}, 400);
}
if (!datasetId || typeof datasetId !== 'string') {
return c.json({
error: "Missing or invalid required field: datasetId"
}, 400);
}
if (!name || typeof name !== 'string' || name.trim().length === 0) {
return c.json({
error: "Missing or invalid required field: name"
}, 400);
}
// Length validation
if (id.length !== 12) {
return c.json({
error: "Field 'id' must be exactly 12 characters (nanoid)"
}, 400);
}
if (name.length > 140) {
return c.json({
error: "Field 'name' must be 140 characters or less"
}, 400);
}
if (description && description.length > 255) {
return c.json({
error: "Field 'description' must be 255 characters or less"
}, 400);
}
// Validate latitude and longitude if provided
if (latitude !== null && latitude !== undefined) {
const lat = Number(latitude);
if (isNaN(lat) || lat < -90 || lat > 90) {
return c.json({
error: "Field 'latitude' must be a valid number between -90 and 90"
}, 400);
}
}
if (longitude !== null && longitude !== undefined) {
const lng = Number(longitude);
if (isNaN(lng) || lng < -180 || lng > 180) {
return c.json({
error: "Field 'longitude' must be a valid number between -180 and 180"
}, 400);
}
}
// Check if user has EDIT permission on the dataset
const hasPermission = await checkUserPermission(db, userId, datasetId.trim(), 'EDIT');
if (!hasPermission) {
return c.json({
error: "You don't have permission to create locations in this dataset"
}, 403);
}
// Create location object
const now = new Date();
const newLocation = {
id: id.trim(),
datasetId: datasetId.trim(),
name: name.trim(),
description: description?.trim() || null,
latitude: latitude !== null && latitude !== undefined ? String(Number(latitude)) : null,
longitude: longitude !== null && longitude !== undefined ? String(Number(longitude)) : null,
createdBy: userId,
createdAt: now,
lastModified: now,
modifiedBy: userId,
active: true,
};
// Insert the location
const result = await db.insert(location).values(newLocation).returning({
id: location.id,
datasetId: location.datasetId,
name: location.name,
description: location.description,
latitude: location.latitude,
longitude: location.longitude,
createdAt: location.createdAt,
createdBy: location.createdBy,
lastModified: location.lastModified,
modifiedBy: location.modifiedBy,
active: location.active,
});
console.log("Created location:", result[0].id, "for dataset:", datasetId, "by user:", userId);
return c.json({
data: result[0]
}, 201);
} catch (error) {
console.error("Error creating location:", error);
// Handle unique constraint violations
if (error instanceof Error && error.message.includes('duplicate key')) {
return c.json({
error: "A location with this ID already exists"
}, 400);
}
return c.json(
{
error: "Failed to create location",
details: error instanceof Error ? error.message : String(error),
},
500
);
}
});
/**
* Protected API route to update an existing location
*
* @route PUT /api/locations/:id
* @authentication Required
* @param {string} id - Location ID in URL path
* @param {Object} body - Location data to update (name, description, latitude, longitude, active)
* @returns {Object} Response containing:
* - data: Updated location object
* @description Updates an existing location. Requires EDIT permission on the dataset.
*/
app.put("/api/locations/:id", authenticate, async (c) => {
try {
// Get user from JWT
const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
const userId = jwtPayload.sub;
// Get location ID from URL parameters
const locationId = c.req.param("id");
// Parse request body
const body = await c.req.json();
const { name, description, latitude, longitude, active } = body;
// Connect to database
const sql = neon(c.env.DATABASE_URL);
const db = drizzle(sql);
// Check if location exists and get its dataset ID
const existingLocation = await db
.select({
id: location.id,
datasetId: location.datasetId,
active: location.active
})
.from(location)
.where(eq(location.id, locationId))
.limit(1);
if (existingLocation.length === 0) {
return c.json({
error: "Location not found"
}, 404);
}
const locationRecord = existingLocation[0];
// Check if user has EDIT permission on the dataset
const hasEditPermission = await checkUserPermission(db, userId, locationRecord.datasetId, 'EDIT');
if (!hasEditPermission) {
return c.json({
error: "You don't have permission to edit this location"
}, 403);
}
// Build update object with only provided fields
const updateData: Record<string, unknown> = {
lastModified: new Date(),
modifiedBy: userId,
};
// Validate and add name if provided
if (name !== undefined) {
if (typeof name !== 'string' || name.trim().length === 0) {
return c.json({
error: "Field 'name' must be a non-empty string"
}, 400);
}
if (name.length > 140) {
return c.json({
error: "Field 'name' must be 140 characters or less"
}, 400);
}
updateData.name = name.trim();
}
// Validate and add description if provided
if (description !== undefined) {
if (description !== null && typeof description !== 'string') {
return c.json({
error: "Field 'description' must be a string or null"
}, 400);
}
if (description && description.length > 255) {
return c.json({
error: "Field 'description' must be 255 characters or less"
}, 400);
}
updateData.description = description?.trim() || null;
}
// Validate and add latitude if provided
if (latitude !== undefined) {
if (latitude !== null) {
const lat = Number(latitude);
if (isNaN(lat) || lat < -90 || lat > 90) {
return c.json({
error: "Field 'latitude' must be a valid number between -90 and 90"
}, 400);
}
updateData.latitude = String(lat);
} else {
updateData.latitude = null;
}
}
// Validate and add longitude if provided
if (longitude !== undefined) {
if (longitude !== null) {
const lng = Number(longitude);
if (isNaN(lng) || lng < -180 || lng > 180) {
return c.json({
error: "Field 'longitude' must be a valid number between -180 and 180"
}, 400);
}
updateData.longitude = String(lng);
} else {
updateData.longitude = null;
}
}
// Add active status if provided (for soft delete)
if (active !== undefined) {
if (typeof active !== 'boolean') {
return c.json({
error: "Field 'active' must be a boolean"
}, 400);
}
updateData.active = active;
}
// Update the location
const result = await db
.update(location)
.set(updateData)
.where(eq(location.id, locationId))
.returning({
id: location.id,
datasetId: location.datasetId,
name: location.name,
description: location.description,
latitude: location.latitude,
longitude: location.longitude,
createdAt: location.createdAt,
createdBy: location.createdBy,
lastModified: location.lastModified,
modifiedBy: location.modifiedBy,
active: location.active,
});
if (result.length === 0) {
return c.json({
error: "Failed to update location"
}, 500);
}
console.log("Updated location:", result[0].id, "by user:", userId);
return c.json({
data: result[0]
});
} catch (error) {
console.error("Error updating location:", error);
return c.json(
{
error: "Failed to update location",
details: error instanceof Error ? error.message : String(error),
},
500
);
}
});
// ============================================================================
// CLUSTERS
// ============================================================================
/**
* Protected API route to fetch clusters for a specific location
*
* @route GET /api/clusters
* @authentication Required
* @param {string} locationId - Required query parameter specifying the location to fetch clusters from
* @returns {Object} Response containing:
* - data: Array of cluster objects with recording pattern information
* @error 400 - If locationId is missing
* @description Returns clusters for the specified location along with their recording patterns
* Performs a LEFT JOIN with the cyclicRecordingPattern table to include recording duration data
* Results are returned with recording pattern information embedded in each cluster object
*/
app.get("/api/clusters", authenticate, async (c) => {
try {
// Get the JWT payload (user info)
const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
const userId = jwtPayload.sub; // User ID from JWT // Subject claim usually contains the user ID
// Get the location ID from query parameter
const locationId = c.req.query("locationId");
console.log("Cluster API called with locationId:", locationId, "userId:", userId);
if (!locationId) {
console.log("Missing locationId in request");
return c.json({
error: "Missing required query parameter: locationId"
}, 400);
}
// Connect to the database
const sql = neon(c.env.DATABASE_URL);
const db = drizzle(sql);
// Query clusters with a LEFT JOIN on recording patterns to avoid N+1 queries
console.log("Querying clusters for locationId:", locationId);
const joinedResults = await db.select({
// Cluster fields
id: cluster.id,
datasetId: cluster.datasetId,
locationId: cluster.locationId,
name: cluster.name,
description: cluster.description,
createdBy: cluster.createdBy,
createdAt: cluster.createdAt,
lastModified: cluster.lastModified,
modifiedBy: cluster.modifiedBy,
active: cluster.active,
timezoneId: cluster.timezoneId,
cyclicRecordingPatternId: cluster.cyclicRecordingPatternId,
sampleRate: cluster.sampleRate,
// Recording pattern fields
recordS: cyclicRecordingPattern.recordS,
sleepS: cyclicRecordingPattern.sleepS
})
.from(cluster)
.leftJoin(
cyclicRecordingPattern,
eq(cluster.cyclicRecordingPatternId, cyclicRecordingPattern.id)
)
.where(eq(cluster.locationId, locationId))
.orderBy(cluster.name);
console.log("Found", joinedResults.length, "clusters for location", locationId);
// Transform results to match the expected structure
const enrichedResults = joinedResults.map(row => ({
id: row.id,
datasetId: row.datasetId,
locationId: row.locationId,
name: row.name,
description: row.description,
createdBy: row.createdBy,
createdAt: row.createdAt,
lastModified: row.lastModified,
modifiedBy: row.modifiedBy,
active: row.active,
timezoneId: row.timezoneId,
cyclicRecordingPatternId: row.cyclicRecordingPatternId,
sampleRate: row.sampleRate,
recordingPattern: (row.recordS !== null && row.sleepS !== null) ? {
recordS: row.recordS,
sleepS: row.sleepS
} : null
}));
return c.json({
data: enrichedResults
});
} catch (error) {
console.error("Error fetching clusters:", error);
return c.json(
{
error: "Failed to fetch clusters",
details: error instanceof Error ? error.message : String(error),
},
500
);
}
});
/**
* Protected API route to create a new cluster
*
* @route POST /api/clusters
* @authentication Required
* @param {Object} body - Cluster data including datasetId, locationId, name, description, timezoneId, sampleRate, recordingPattern
* - recordingPattern?: Object (optional) - { recordS: number, sleepS: number }
* @returns {Object} Response containing:
* - data: Created cluster object with optional recordingPattern
* @description Creates a new cluster with the authenticated user as owner.
* Requires EDIT permission on the dataset to create clusters within it.
* If recordingPattern is provided, creates a new cyclic recording pattern record.
*/
app.post("/api/clusters", authenticate, async (c) => {
try {
// Get user ID from JWT
const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
const userId = jwtPayload.sub;
// Database connection
const sql = neon(c.env.DATABASE_URL);
const db = drizzle(sql);
// Parse and validate request body
const body = await c.req.json();
const { id, datasetId, locationId, name, description, timezoneId, sampleRate, recordingPattern } = body;
// Field validation
if (!id || typeof id !== 'string') {
return c.json({
error: "Missing or invalid required field: id"
}, 400);
}
if (!datasetId || typeof datasetId !== 'string') {
return c.json({
error: "Missing or invalid required field: datasetId"
}, 400);
}
if (!locationId || typeof locationId !== 'string') {
return c.json({
error: "Missing or invalid required field: locationId"
}, 400);
}
if (!name || typeof name !== 'string' || name.trim().length === 0) {
return c.json({
error: "Missing or invalid required field: name"
}, 400);
}
if (!sampleRate || typeof sampleRate !== 'number' || sampleRate <= 0) {
return c.json({
error: "Missing or invalid required field: sampleRate"
}, 400);
}
// Length validation
if (id.length !== 12) {
return c.json({
error: "Field 'id' must be exactly 12 characters (nanoid)"
}, 400);
}
if (name.length > 140) {
return c.json({
error: "Field 'name' must be 140 characters or less"
}, 400);
}
if (description && description.length > 255) {
return c.json({
error: "Field 'description' must be 255 characters or less"
}, 400);
}
if (timezoneId && timezoneId.length > 40) {
return c.json({
error: "Field 'timezoneId' must be 40 characters or less"
}, 400);
}
// Validate recording pattern if provided
if (recordingPattern) {
if (typeof recordingPattern !== 'object' || recordingPattern === null) {
return c.json({
error: "Field 'recordingPattern' must be an object"
}, 400);
}
const { recordS, sleepS } = recordingPattern;
if (typeof recordS !== 'number' || recordS <= 0 || !Number.isInteger(recordS)) {
return c.json({
error: "Field 'recordingPattern.recordS' must be a positive integer"
}, 400);
}
if (typeof sleepS !== 'number' || sleepS <= 0 || !Number.isInteger(sleepS)) {
return c.json({
error: "Field 'recordingPattern.sleepS' must be a positive integer"
}, 400);
}
}
// Check if user has EDIT permission on the dataset
const hasPermission = await checkUserPermission(db, userId, datasetId.trim(), 'EDIT');
if (!hasPermission) {
return c.json({
error: "You don't have permission to create clusters in this dataset"
}, 403);
}
// Create cluster with optional recording pattern in transaction
const result = await db.transaction(async (tx) => {
const now = new Date();
let cyclicRecordingPatternId = null;
// Create cyclic recording pattern if provided
if (recordingPattern) {
const patternId = nanoid(12);
const [createdPattern] = await tx.insert(cyclicRecordingPattern).values({
id: patternId,
recordS: recordingPattern.recordS,
sleepS: recordingPattern.sleepS,
createdBy: userId,
createdAt: now,
lastModified: now,
modifiedBy: userId,
}).returning({
id: cyclicRecordingPattern.id,
});
cyclicRecordingPatternId = createdPattern.id;
}
// Create cluster object
const newCluster = {
id: id.trim(),
datasetId: datasetId.trim(),
locationId: locationId.trim(),
name: name.trim(),
description: description?.trim() || null,
timezoneId: timezoneId?.trim() || null,
cyclicRecordingPatternId: cyclicRecordingPatternId,
sampleRate: sampleRate,
createdBy: userId,
createdAt: now,
lastModified: now,
modifiedBy: userId,
active: true,
};
// Insert the cluster
const [createdCluster] = await tx.insert(cluster).values(newCluster).returning({
id: cluster.id,
datasetId: cluster.datasetId,
locationId: cluster.locationId,
name: cluster.name,
description: cluster.description,
timezoneId: cluster.timezoneId,
cyclicRecordingPatternId: cluster.cyclicRecordingPatternId,
sampleRate: cluster.sampleRate,
createdAt: cluster.createdAt,
createdBy: cluster.createdBy,
lastModified: cluster.lastModified,
modifiedBy: cluster.modifiedBy,
active: cluster.active,
});
// If recording pattern was created, include it in the response
const recordingPatternData = recordingPattern ? {
recordS: recordingPattern.recordS,
sleepS: recordingPattern.sleepS
} : null;
return {
...createdCluster,
recordingPattern: recordingPatternData
};
});
console.log("Created cluster:", result.id, "for location:", locationId, "by user:", userId);
return c.json({
data: result
}, 201);
} catch (error) {
console.error("Error creating cluster:", error);
// Handle unique constraint violations
if (error instanceof Error && error.message.includes('duplicate key')) {
return c.json({
error: "A cluster with this ID already exists"
}, 400);
}
return c.json(
{
error: "Failed to create cluster",
details: error instanceof Error ? error.message : String(error),
},
500
);
}
});
/**
* Protected API route to update an existing cluster
*
* @route PUT /api/clusters/:id
* @authentication Required
* @param {string} id - Cluster ID in URL path
* @param {Object} body - Cluster data to update (name, description, timezoneId, sampleRate, active, recordingPattern)
* - recordingPattern?: Object | null (optional) - { recordS: number, sleepS: number } or null to remove
* @returns {Object} Response containing:
* - data: Updated cluster object with recordingPattern if present
* @description Updates an existing cluster. Requires EDIT permission on the dataset.
* recordingPattern can be provided to add/update or set to null to remove recording pattern.
*/
app.put("/api/clusters/:id", authenticate, async (c) => {
try {
// Get user from JWT
const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
const userId = jwtPayload.sub;
// Get cluster ID from URL parameters
const clusterId = c.req.param("id");
// Parse request body
const body = await c.req.json();
const { name, description, timezoneId, sampleRate, active, recordingPattern } = body;
// Connect to database
const sql = neon(c.env.DATABASE_URL);
const db = drizzle(sql);
// Check if cluster exists and get its dataset ID and current recording pattern
const existingCluster = await db
.select({
id: cluster.id,
datasetId: cluster.datasetId,
active: cluster.active,
cyclicRecordingPatternId: cluster.cyclicRecordingPatternId
})
.from(cluster)
.where(eq(cluster.id, clusterId))
.limit(1);
if (existingCluster.length === 0) {
return c.json({
error: "Cluster not found"
}, 404);
}
const clusterRecord = existingCluster[0];
// Check if user has EDIT permission on the dataset
const hasEditPermission = await checkUserPermission(db, userId, clusterRecord.datasetId, 'EDIT');
if (!hasEditPermission) {
return c.json({
error: "You don't have permission to edit this cluster"
}, 403);
}
// Build update object with only provided fields
const updateData: Record<string, unknown> = {
lastModified: new Date(),
modifiedBy: userId,
};
// Validate and add name if provided
if (name !== undefined) {
if (typeof name !== 'string' || name.trim().length === 0) {
return c.json({
error: "Field 'name' must be a non-empty string"
}, 400);
}
if (name.length > 140) {
return c.json({
error: "Field 'name' must be 140 characters or less"
}, 400);
}
updateData.name = name.trim();
}
// Validate and add description if provided
if (description !== undefined) {
if (description !== null && typeof description !== 'string') {
return c.json({
error: "Field 'description' must be a string or null"
}, 400);
}
if (description && description.length > 255) {
return c.json({
error: "Field 'description' must be 255 characters or less"
}, 400);
}
updateData.description = description?.trim() || null;
}
// Validate and add timezoneId if provided
if (timezoneId !== undefined) {
if (timezoneId !== null && typeof timezoneId !== 'string') {
return c.json({
error: "Field 'timezoneId' must be a string or null"
}, 400);
}
if (timezoneId && timezoneId.length > 40) {
return c.json({
error: "Field 'timezoneId' must be 40 characters or less"
}, 400);
}
updateData.timezoneId = timezoneId?.trim() || null;
}
// Validate and add sampleRate if provided
if (sampleRate !== undefined) {
if (typeof sampleRate !== 'number' || sampleRate <= 0) {
return c.json({
error: "Field 'sampleRate' must be a positive number"
}, 400);
}
updateData.sampleRate = sampleRate;
}
// Validate recording pattern if provided
if (recordingPattern !== undefined) {
if (recordingPattern !== null && typeof recordingPattern !== 'object') {
return c.json({
error: "Field 'recordingPattern' must be an object or null"
}, 400);
}
if (recordingPattern) {
const { recordS, sleepS } = recordingPattern;
if (typeof recordS !== 'number' || recordS <= 0 || !Number.isInteger(recordS)) {
return c.json({
error: "Field 'recordingPattern.recordS' must be a positive integer"
}, 400);
}
if (typeof sleepS !== 'number' || sleepS <= 0 || !Number.isInteger(sleepS)) {
return c.json({
error: "Field 'recordingPattern.sleepS' must be a positive integer"
}, 400);
}
}
}
// Add active status if provided (for soft delete)
if (active !== undefined) {
if (typeof active !== 'boolean') {
return c.json({
error: "Field 'active' must be a boolean"
}, 400);
}
updateData.active = active;
}
// Update cluster with optional recording pattern changes in transaction
const result = await db.transaction(async (tx) => {
const currentPatternId = clusterRecord.cyclicRecordingPatternId;
// Handle recording pattern updates
if (recordingPattern !== undefined) {
if (recordingPattern === null) {
// Remove recording pattern
if (currentPatternId) {
await tx.delete(cyclicRecordingPattern).where(eq(cyclicRecordingPattern.id, currentPatternId));
updateData.cyclicRecordingPatternId = null;
}
} else {
// Add or update recording pattern
if (currentPatternId) {
// Update existing pattern
await tx
.update(cyclicRecordingPattern)
.set({
recordS: recordingPattern.recordS,
sleepS: recordingPattern.sleepS,
lastModified: new Date(),
modifiedBy: userId,
})
.where(eq(cyclicRecordingPattern.id, currentPatternId));
} else {
// Create new pattern
const patternId = nanoid(12);
await tx.insert(cyclicRecordingPattern).values({
id: patternId,
recordS: recordingPattern.recordS,
sleepS: recordingPattern.sleepS,
createdBy: userId,
createdAt: new Date(),
lastModified: new Date(),
modifiedBy: userId,
});
updateData.cyclicRecordingPatternId = patternId;
}
}
}
// Update the cluster
const [updatedCluster] = await tx
.update(cluster)
.set(updateData)
.where(eq(cluster.id, clusterId))
.returning({
id: cluster.id,
datasetId: cluster.datasetId,
locationId: cluster.locationId,
name: cluster.name,
description: cluster.description,
timezoneId: cluster.timezoneId,
cyclicRecordingPatternId: cluster.cyclicRecordingPatternId,
sampleRate: cluster.sampleRate,
createdAt: cluster.createdAt,
createdBy: cluster.createdBy,
lastModified: cluster.lastModified,
modifiedBy: cluster.modifiedBy,
active: cluster.active,
});
// Include recording pattern data in response if it exists
let recordingPatternData = null;
if (updatedCluster.cyclicRecordingPatternId) {
if (recordingPattern !== undefined && recordingPattern !== null) {
// Use the provided pattern data
recordingPatternData = {
recordS: recordingPattern.recordS,
sleepS: recordingPattern.sleepS
};
} else {
// Fetch existing pattern data
const existingPattern = await tx
.select({
recordS: cyclicRecordingPattern.recordS,
sleepS: cyclicRecordingPattern.sleepS
})
.from(cyclicRecordingPattern)
.where(eq(cyclicRecordingPattern.id, updatedCluster.cyclicRecordingPatternId))
.limit(1);
if (existingPattern.length > 0) {
recordingPatternData = existingPattern[0];
}
}
}
return {
...updatedCluster,
recordingPattern: recordingPatternData
};
});
if (!result) {
return c.json({
error: "Failed to update cluster"
}, 500);
}
console.log("Updated cluster:", result.id, "by user:", userId);
return c.json({
data: result
});
} catch (error) {
console.error("Error updating cluster:", error);
return c.json(
{
error: "Failed to update cluster",
details: error instanceof Error ? error.message : String(error),
},
500
);
}
});
/**
* Protected API route to delete a cluster
*
* @route DELETE /api/clusters/:id
* @authentication Required
* @param {string} id - Cluster ID in URL path
* @returns {Object} Response containing:
* - message: Success message
* @description Soft deletes a cluster (sets active=false) and hard deletes its associated cyclic recording pattern if present.
* Requires EDIT permission on the dataset.
*/
app.delete("/api/clusters/:id", authenticate, async (c) => {
try {
// Get user from JWT
const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
const userId = jwtPayload.sub;
// Get cluster ID from URL parameters
const clusterId = c.req.param("id");
// Connect to database
const sql = neon(c.env.DATABASE_URL);
const db = drizzle(sql);
// Check if cluster exists and get its dataset ID and recording pattern
const existingCluster = await db
.select({
id: cluster.id,
datasetId: cluster.datasetId,
cyclicRecordingPatternId: cluster.cyclicRecordingPatternId
})
.from(cluster)
.where(eq(cluster.id, clusterId))
.limit(1);
if (existingCluster.length === 0) {
return c.json({
error: "Cluster not found"
}, 404);
}
const clusterRecord = existingCluster[0];
// Check if user has EDIT permission on the dataset
const hasEditPermission = await checkUserPermission(db, userId, clusterRecord.datasetId, 'EDIT');
if (!hasEditPermission) {
return c.json({
error: "You don't have permission to delete this cluster"
}, 403);
}
// Soft delete cluster and hard delete associated recording pattern in transaction
await db.transaction(async (tx) => {
// Soft delete the cluster (set active = false)
await tx
.update(cluster)
.set({
active: false,
lastModified: new Date(),
modifiedBy: userId
})
.where(eq(cluster.id, clusterId));
// Hard delete associated cyclic recording pattern if it exists (no active field in this table)
if (clusterRecord.cyclicRecordingPatternId) {
await tx.delete(cyclicRecordingPattern).where(eq(cyclicRecordingPattern.id, clusterRecord.cyclicRecordingPatternId));
}
});
console.log("Soft deleted cluster:", clusterId, "and hard deleted associated recording pattern by user:", userId);
return c.json({
message: "Cluster deleted successfully"
});
} catch (error) {
console.error("Error deleting cluster:", error);
return c.json(
{
error: "Failed to delete cluster",
details: error instanceof Error ? error.message : String(error),
},
500
);
}
});
// ============================================================================
// FILES
// ============================================================================
/**
* Protected API route to fetch audio files for a specific cluster
*
* @route GET /api/files
* @authentication Required
* @param {string} clusterId - Required query parameter specifying the cluster to fetch files from
* @param {number} [page=1] - Optional page number for pagination (starts at 1)
* @param {number} [pageSize=100] - Optional page size (10-500, defaults to 100)
* @param {string} [solarNight] - Optional filter for files recorded during solar night ('true'/'false')
* @param {string} [civilNight] - Optional filter for files recorded during civil night ('true'/'false')
* @param {string} [speciesId] - Optional filter for files with selections labeled with specific species
* @returns {Object} Response containing:
* - data: Array of file objects with metadata, mothMetadata, and species information
* - pagination: Object with pagination metadata
* - filters: Object showing the filters that were applied
* @error 400 - If clusterId is missing or page is invalid
* @description Returns audio files for the specified cluster with comprehensive metadata:
* - Basic file information (name, path, timestamp, duration, etc.)
* - File metadata (JSON format)
* - Recording device metadata (gain, battery voltage, temperature)
* - Species found in each file
*
* When speciesId is provided, only returns files that have at least one
* selection labeled with the specified species.
*/
app.get("/api/files", authenticate, async (c) => {
try {
// Get query parameters
const clusterId = c.req.query("clusterId");
const page = parseInt(c.req.query("page") || "1", 10);
const pageSize = parseInt(c.req.query("pageSize") || "100", 10);
const solarNight = c.req.query("solarNight");
const civilNight = c.req.query("civilNight");
const speciesId = c.req.query("speciesId"); // Optional filter for species
// Validate parameters
if (!clusterId) {
console.log("Missing clusterId in request");
return c.json({
error: "Missing required query parameter: clusterId"
}, 400);
}
// Validate and limit page size to prevent excessive queries
const limitedPageSize = Math.min(Math.max(pageSize, 10), 500);
const offset = (page - 1) * limitedPageSize;
// Validate page number
if (page < 1) {
console.log("Invalid page number in request:", page);
return c.json({
error: "Invalid page parameter: must be greater than 0"
}, 400);
}
// Connect to the database
const sql = neon(c.env.DATABASE_URL);
const db = drizzle(sql);
// Build filter conditions
let whereConditions = sqlExpr`${file.clusterId} = ${clusterId} AND ${file.active} = true`;
// Add filters for solarNight if specified
if (solarNight === 'true') {
whereConditions = sqlExpr`${whereConditions} AND ${file.maybeSolarNight} = true`;
} else if (solarNight === 'false') {
whereConditions = sqlExpr`${whereConditions} AND (${file.maybeSolarNight} = false OR ${file.maybeSolarNight} IS NULL)`;
}
// Add filters for civilNight if specified
if (civilNight === 'true') {
whereConditions = sqlExpr`${whereConditions} AND ${file.maybeCivilNight} = true`;
} else if (civilNight === 'false') {
whereConditions = sqlExpr`${whereConditions} AND (${file.maybeCivilNight} = false OR ${file.maybeCivilNight} IS NULL)`;
}
// First, get the total count of files for pagination metadata
let countResult;
if (speciesId) {
// Count only files that have at least one selection labeled with the specified species
countResult = await db
.select({
count: sqlExpr<number>`COUNT(DISTINCT ${file.id})`
})
.from(file)
.innerJoin(selection, eq(selection.fileId, file.id))
.innerJoin(label, eq(label.selectionId, selection.id))
.where(sqlExpr`${whereConditions} AND ${label.speciesId} = ${speciesId} AND ${label.active} = true`);
} else {
// Standard count without species filter
countResult = await db
.select({
count: sqlExpr<number>`COUNT(1)`
})
.from(file)
.where(whereConditions);
}
const totalFiles = Number(countResult[0].count);
const totalPages = Math.ceil(totalFiles / limitedPageSize);
// Query files for the specified cluster with pagination
let filesResult;
if (speciesId) {
// Get only files that have at least one selection labeled with the specified species
filesResult = await db
.select({
id: file.id,
fileName: file.fileName,
path: file.path,
timestampLocal: file.timestampLocal,
duration: file.duration,
sampleRate: file.sampleRate,
locationId: file.locationId,
description: file.description,
maybeSolarNight: file.maybeSolarNight,
maybeCivilNight: file.maybeCivilNight,
moonPhase: file.moonPhase,
})
.from(file)
.innerJoin(selection, eq(selection.fileId, file.id))
.innerJoin(label, eq(label.selectionId, selection.id))
.where(sqlExpr`${whereConditions} AND ${label.speciesId} = ${speciesId} AND ${label.active} = true`)
.orderBy(file.timestampLocal)
.groupBy(file.id, file.fileName, file.path, file.timestampLocal, file.duration,
file.sampleRate, file.locationId, file.description, file.maybeSolarNight,
file.maybeCivilNight, file.moonPhase)
.limit(limitedPageSize)
.offset(offset);
} else {
// Standard query without species filter
filesResult = await db
.select({
id: file.id,
fileName: file.fileName,
path: file.path,
timestampLocal: file.timestampLocal,
duration: file.duration,
sampleRate: file.sampleRate,
locationId: file.locationId,
description: file.description,
maybeSolarNight: file.maybeSolarNight,
maybeCivilNight: file.maybeCivilNight,
moonPhase: file.moonPhase,
})
.from(file)
.where(whereConditions)
.orderBy(file.timestampLocal)
.limit(limitedPageSize)
.offset(offset);
}
// Early return if no files found
if (filesResult.length === 0) {
return c.json({
data: [],
pagination: {
currentPage: page,
pageSize: limitedPageSize,
totalPages: totalPages,
totalItems: totalFiles,
hasNextPage: page < totalPages,
hasPreviousPage: page > 1,
},
filters: {
solarNight: solarNight === 'true' ? true : solarNight === 'false' ? false : null,
civilNight: civilNight === 'true' ? true : civilNight === 'false' ? false : null,
speciesId: speciesId || null,
}
});
}
// Get all file IDs for metadata queries
const fileIds = filesResult.map(f => f.id);
// Safety check - if no files found, return empty results
if (fileIds.length === 0) {
return c.json({
data: [],
pagination: {
currentPage: page,
pageSize: limitedPageSize,
totalPages: totalPages,
totalItems: totalFiles,
hasNextPage: page < totalPages,
hasPreviousPage: page > 1,
},
filters: {
solarNight: solarNight === 'true' ? true : solarNight === 'false' ? false : null,
civilNight: civilNight === 'true' ? true : civilNight === 'false' ? false : null,
speciesId: speciesId || null,
}
});
}
// Convert array to proper SQL format for IN clause
const fileIdsQuoted = fileIds.map(id => `'${id}'`).join(',');
// Execute metadata queries in parallel using Promise.all
const [metadataResults, mothMetadataResults, speciesResults] = await Promise.all([
// Fetch file metadata
db.select({
fileId: fileMetadata.fileId,
json: fileMetadata.json
})
.from(fileMetadata)
.where(sqlExpr`${fileMetadata.fileId} IN (${sqlExpr.raw(fileIdsQuoted)})`),
// Fetch moth metadata
db.select({
fileId: mothMetadata.fileId,
gain: mothMetadata.gain,
batteryV: mothMetadata.batteryV,
tempC: mothMetadata.tempC
})
.from(mothMetadata)
.where(sqlExpr`${mothMetadata.fileId} IN (${sqlExpr.raw(fileIdsQuoted)})`),
// Fetch species data via selections and labels
db.select({
fileId: selection.fileId,
speciesId: species.id,
speciesLabel: species.label,
ebirdCode: species.ebirdCode,
description: species.description
})
.from(selection)
.innerJoin(label, eq(label.selectionId, selection.id))
.innerJoin(species, eq(species.id, label.speciesId))
.where(
speciesId
? sqlExpr`${selection.fileId} IN (${sqlExpr.raw(fileIdsQuoted)}) AND ${label.active} = true AND ${label.speciesId} = ${speciesId}`
: sqlExpr`${selection.fileId} IN (${sqlExpr.raw(fileIdsQuoted)}) AND ${label.active} = true`
)
]);
// Process metadata results
const metadataMap = metadataResults.reduce((acc, item) => {
let processedJson = item.json;
try {
if (typeof item.json === 'string' && (item.json.startsWith('{') || item.json.startsWith('['))) {
processedJson = JSON.parse(item.json);
} else if (typeof item.json === 'string' && item.json.includes('\\"')) {
const unescaped = item.json.replace(/\\"/g, '"');
processedJson = JSON.parse(unescaped);
}
} catch (e) {
console.error("Error processing metadata JSON:", e);
processedJson = item.json;
}
acc[item.fileId] = processedJson;
return acc;
}, {} as Record<string, unknown>);
// Process moth metadata
const mothMetadataMap = mothMetadataResults.reduce((acc, item) => {
acc[item.fileId] = {
gain: item.gain,
batteryV: item.batteryV !== null ? Number(item.batteryV) : null,
tempC: item.tempC !== null ? Number(item.tempC) : null
};
return acc;
}, {} as Record<string, {
gain: string | null;
batteryV: number | null;
tempC: number | null;
}>);
// Process species data
const speciesMap = speciesResults.reduce((acc, item) => {
if (!acc[item.fileId]) {
acc[item.fileId] = [];
}
// Deduplicate species entries
const existingSpecies = acc[item.fileId].find(s => s.id === item.speciesId);
if (!existingSpecies) {
acc[item.fileId].push({
id: item.speciesId,
label: item.speciesLabel,
ebirdCode: item.ebirdCode,
description: item.description
});
}
return acc;
}, {} as Record<string, Array<{
id: string;
label: string;
ebirdCode: string | null;
description: string | null;
}>>);
// Combine file data with metadata
const files = filesResult.map(fileData => ({
...fileData,
metadata: metadataMap[fileData.id] || null,
mothMetadata: mothMetadataMap[fileData.id] || null,
species: speciesMap[fileData.id] || []
}));
// Return paginated results with metadata and filters
return c.json({
data: files,
pagination: {
currentPage: page,
pageSize: limitedPageSize,
totalPages: totalPages,
totalItems: totalFiles,
hasNextPage: page < totalPages,
hasPreviousPage: page > 1,
},
filters: {
solarNight: solarNight === 'true' ? true : solarNight === 'false' ? false : null,
civilNight: civilNight === 'true' ? true : civilNight === 'false' ? false : null,
speciesId: speciesId || null,
}
});
} catch (error) {
console.error("Error fetching files:", error);
return c.json(
{
error: "Failed to fetch files",
details: error instanceof Error ? error.message : String(error),
},
500
);
}
});
// ============================================================================
// SPECIES
// ============================================================================
/**
* Protected API route to fetch species and call types for a dataset
*
* @route GET /api/species
* @authentication Required
* @param {string} datasetId - Required query parameter specifying the dataset to fetch species from
* @returns {Object} Response containing:
* - data: Array of species objects, each with an array of associated call types
* @error 400 - If datasetId is missing
* @description Returns species associated with the specified dataset along with their call types
* Each species object includes:
* - id, label, ebirdCode, description
* - callTypes: Array of call type objects with id and label
*
* Uses an efficient JOIN approach to fetch data in a single query to avoid N+1 query problems
* Results are transformed to provide a nested structure with call types inside species objects
*/
app.get("/api/species", authenticate, async (c) => {
try {
// Get query parameter for datasetId
const datasetId = c.req.query("datasetId");
// Validate parameters
if (!datasetId) {
return c.json({
error: "Missing required query parameter: datasetId"
}, 400);
}
// Connect to the database
const sql = neon(c.env.DATABASE_URL);
const db = drizzle(sql);
// Use JOIN approach to get species and call types in a single query
// This eliminates the N+1 query problem and reduces round trips to the database
const queryResults = await db
.select({
id: species.id,
label: species.label,
ebirdCode: species.ebirdCode,
description: species.description,
callTypeId: callType.id,
callTypeLabel: callType.label
})
.from(species)
.innerJoin(
speciesDataset,
eq(species.id, speciesDataset.speciesId)
)
.leftJoin(
callType,
sqlExpr`${callType.speciesId} = ${species.id} AND ${callType.active} = true`
)
.where(eq(speciesDataset.datasetId, datasetId))
.orderBy(species.label);
// If no results are found, return an empty array
if (queryResults.length === 0) {
return c.json({
data: []
});
}
// Transform the flat results into nested objects
// Using a Map for better performance with large datasets
const speciesMap = new Map();
queryResults.forEach(row => {
if (!speciesMap.has(row.id)) {
// Create a new species entry if not already in the map
speciesMap.set(row.id, {
id: row.id,
label: row.label,
ebirdCode: row.ebirdCode,
description: row.description,
callTypes: []
});
}
// Add the call type if it exists and isn't already in the array
if (row.callTypeId) {
const species = speciesMap.get(row.id);
const existingCallType = species.callTypes.find((ct: { id: string }) => ct.id === row.callTypeId);
if (!existingCallType) {
species.callTypes.push({
id: row.callTypeId,
label: row.callTypeLabel
});
}
}
});
// Convert map to array
const enrichedSpecies = Array.from(speciesMap.values());
// Return the enriched species data
return c.json({
data: enrichedSpecies
});
} catch (error) {
console.error("Error fetching species:", error);
return c.json(
{
error: "Failed to fetch species",
details: error instanceof Error ? error.message : String(error),
},
500
);
}
});
/**
* Protected API route to create a new species
*
* @route POST /api/species
* @authentication Required
* @param {Object} body - Species data including datasetId, label, description, ebirdCode, callTypes
* @returns {Object} Response containing:
* - data: Created species object with call types
* @description Creates a new species with optional eBird integration and call types.
* Requires EDIT permission on the dataset. Creates entries in species, species_dataset,
* and optionally call_type tables in a single transaction.
*/
app.post("/api/species", authenticate, async (c) => {
try {
// Get user ID from JWT
const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
const userId = jwtPayload.sub;
// Database connection
const sql = neon(c.env.DATABASE_URL);
const db = drizzle(sql);
// Parse and validate request body
const body = await c.req.json();
const { id, datasetId, label, description, ebirdCode, callTypes } = body;
// Field validation
if (!id || typeof id !== 'string') {
return c.json({
error: "Missing or invalid required field: id"
}, 400);
}
if (!datasetId || typeof datasetId !== 'string') {
return c.json({
error: "Missing or invalid required field: datasetId"
}, 400);
}
if (!label || typeof label !== 'string' || label.trim().length === 0) {
return c.json({
error: "Missing or invalid required field: label"
}, 400);
}
// Length validation
if (id.length !== 12) {
return c.json({
error: "Field 'id' must be exactly 12 characters (nanoid)"
}, 400);
}
if (label.length > 100) {
return c.json({
error: "Field 'label' must be 100 characters or less"
}, 400);
}
if (description && description.length > 255) {
return c.json({
error: "Field 'description' must be 255 characters or less"
}, 400);
}
if (ebirdCode && ebirdCode.length > 12) {
return c.json({
error: "Field 'ebirdCode' must be 12 characters or less"
}, 400);
}
// Validate call types if provided
if (callTypes && (!Array.isArray(callTypes) || callTypes.some(ct => !ct.label || ct.label.length > 100))) {
return c.json({
error: "Field 'callTypes' must be an array of objects with 'label' field (max 100 chars)"
}, 400);
}
// Check if user has EDIT permission on the dataset
const hasPermission = await checkUserPermission(db, userId, datasetId.trim(), 'EDIT');
if (!hasPermission) {
return c.json({
error: "You don't have permission to create species in this dataset"
}, 403);
}
// Start transaction for atomic creation
const result = await db.transaction(async (tx) => {
const now = new Date();
// Create species record
const newSpecies = {
id: id.trim(),
label: label.trim(),
ebirdCode: ebirdCode?.trim() || null,
taxonomyVersion: ebirdCode ? '2024' : null,
description: description?.trim() || null,
createdBy: userId,
createdAt: now,
lastModified: now,
modifiedBy: userId,
owner: userId,
active: true,
};
const [createdSpecies] = await tx.insert(species).values(newSpecies).returning({
id: species.id,
label: species.label,
ebirdCode: species.ebirdCode,
taxonomyVersion: species.taxonomyVersion,
description: species.description,
createdAt: species.createdAt,
createdBy: species.createdBy,
lastModified: species.lastModified,
modifiedBy: species.modifiedBy,
active: species.active,
});
// Create species-dataset association
await tx.insert(speciesDataset).values({
speciesId: id.trim(),
datasetId: datasetId.trim(),
createdAt: now,
createdBy: userId,
lastModified: now,
modifiedBy: userId,
});
// Create call types if provided
const createdCallTypes = [];
if (callTypes && callTypes.length > 0) {
for (const callTypeData of callTypes) {
const callTypeId = nanoid(12);
const [createdCallType] = await tx.insert(callType).values({
id: callTypeId,
speciesId: id.trim(),
label: callTypeData.label.trim(),
createdBy: userId,
createdAt: now,
lastModified: now,
modifiedBy: userId,
active: true,
}).returning({
id: callType.id,
label: callType.label,
});
createdCallTypes.push(createdCallType);
}
}
return {
...createdSpecies,
callTypes: createdCallTypes
};
});
console.log("Created species:", result.id, "for dataset:", datasetId, "by user:", userId);
return c.json({
data: result
}, 201);
} catch (error) {
console.error("Error creating species:", error);
// Handle unique constraint violations
if (error instanceof Error && error.message.includes('duplicate key')) {
return c.json({
error: "A species with this ID already exists"
}, 400);
}
return c.json(
{
error: "Failed to create species",
details: error instanceof Error ? error.message : String(error),
},
500
);
}
});
/**
* Protected API route to update an existing species
*
* @route PUT /api/species/:id
* @authentication Required
* @param {string} id - Species ID in URL path
* @param {Object} body - Species data to update (label, description, ebirdCode, active)
* @returns {Object} Response containing:
* - data: Updated species object with call types
* @description Updates an existing species. Requires EDIT permission on the dataset.
* When soft deleting (active=false), also soft deletes all related call types.
*/
app.put("/api/species/:id", authenticate, async (c) => {
try {
// Get user from JWT
const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
const userId = jwtPayload.sub;
// Get species ID from URL parameters
const speciesId = c.req.param("id");
// Parse request body
const body = await c.req.json();
const { label, description, ebirdCode, active } = body;
// Connect to database
const sql = neon(c.env.DATABASE_URL);
const db = drizzle(sql);
// Check if species exists and get its associated datasets
const existingSpecies = await db
.select({
id: species.id,
active: species.active
})
.from(species)
.where(eq(species.id, speciesId))
.limit(1);
if (existingSpecies.length === 0) {
return c.json({
error: "Species not found"
}, 404);
}
// Get datasets associated with this species to check permissions
const associatedDatasets = await db
.select({ datasetId: speciesDataset.datasetId })
.from(speciesDataset)
.where(eq(speciesDataset.speciesId, speciesId));
if (associatedDatasets.length === 0) {
return c.json({
error: "Species not associated with any dataset"
}, 400);
}
// Check if user has EDIT permission on at least one associated dataset
let hasPermission = false;
for (const assoc of associatedDatasets) {
if (await checkUserPermission(db, userId, assoc.datasetId, 'EDIT')) {
hasPermission = true;
break;
}
}
if (!hasPermission) {
return c.json({
error: "You don't have permission to edit this species"
}, 403);
}
// Start transaction for atomic update
const result = await db.transaction(async (tx) => {
// Build update object with only provided fields
const updateData: Record<string, unknown> = {
lastModified: new Date(),
modifiedBy: userId,
};
// Validate and add label if provided
if (label !== undefined) {
if (typeof label !== 'string' || label.trim().length === 0) {
return c.json({
error: "Field 'label' must be a non-empty string"
}, 400);
}
if (label.length > 100) {
return c.json({
error: "Field 'label' must be 100 characters or less"
}, 400);
}
updateData.label = label.trim();
}
// Validate and add description if provided
if (description !== undefined) {
if (description !== null && typeof description !== 'string') {
return c.json({
error: "Field 'description' must be a string or null"
}, 400);
}
if (description && description.length > 255) {
return c.json({
error: "Field 'description' must be 255 characters or less"
}, 400);
}
updateData.description = description?.trim() || null;
}
// Validate and add eBird code if provided
if (ebirdCode !== undefined) {
if (ebirdCode !== null && typeof ebirdCode !== 'string') {
return c.json({
error: "Field 'ebirdCode' must be a string or null"
}, 400);
}
if (ebirdCode && ebirdCode.length > 12) {
return c.json({
error: "Field 'ebirdCode' must be 12 characters or less"
}, 400);
}
updateData.ebirdCode = ebirdCode?.trim() || null;
updateData.taxonomyVersion = ebirdCode ? '2024' : null;
}
// Add active status if provided (for soft delete)
if (active !== undefined) {
if (typeof active !== 'boolean') {
return c.json({
error: "Field 'active' must be a boolean"
}, 400);
}
updateData.active = active;
// If soft deleting species, also soft delete related records
if (!active) {
// Soft delete all call types for this species
await tx
.update(callType)
.set({
active: false,
lastModified: new Date(),
modifiedBy: userId
})
.where(eq(callType.speciesId, speciesId));
// Delete all species-dataset associations for this species (hard delete since no active field)
await tx
.delete(speciesDataset)
.where(eq(speciesDataset.speciesId, speciesId));
}
}
// Update the species
const [updatedSpecies] = await tx
.update(species)
.set(updateData)
.where(eq(species.id, speciesId))
.returning({
id: species.id,
label: species.label,
ebirdCode: species.ebirdCode,
taxonomyVersion: species.taxonomyVersion,
description: species.description,
createdAt: species.createdAt,
createdBy: species.createdBy,
lastModified: species.lastModified,
modifiedBy: species.modifiedBy,
active: species.active,
});
// Get updated call types
const callTypes = await tx
.select({
id: callType.id,
label: callType.label,
})
.from(callType)
.where(and(eq(callType.speciesId, speciesId), eq(callType.active, true)));
return {
...updatedSpecies,
callTypes
};
});
console.log("Updated species:", speciesId, "by user:", userId);
return c.json({
data: result
});
} catch (error) {
console.error("Error updating species:", error);
return c.json(
{
error: "Failed to update species",
details: error instanceof Error ? error.message : String(error),
},
500
);
}
});
// ============================================================================
// CALL-TYPES
// ============================================================================
/**
* Protected API route to create a new call type
*
* @route POST /api/call-types
* @authentication Required
* @param {Object} body - Call type data including id, speciesId, label
* @returns {Object} Response containing:
* - data: Created call type object
* @description Creates a new call type for a species. Requires EDIT permission on any dataset containing the species.
*/
app.post("/api/call-types", authenticate, async (c) => {
try {
// Get user ID from JWT
const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
const userId = jwtPayload.sub;
// Database connection
const sql = neon(c.env.DATABASE_URL);
const db = drizzle(sql);
// Parse and validate request body
const body = await c.req.json();
const { id, speciesId, label } = body;
// Field validation
if (!id || typeof id !== 'string' || id.length !== 12) {
return c.json({
error: "Field 'id' must be exactly 12 characters (nanoid)"
}, 400);
}
if (!speciesId || typeof speciesId !== 'string') {
return c.json({
error: "Missing or invalid required field: speciesId"
}, 400);
}
if (!label || typeof label !== 'string' || label.trim().length === 0) {
return c.json({
error: "Missing or invalid required field: label"
}, 400);
}
if (label.length > 100) {
return c.json({
error: "Field 'label' must be 100 characters or less"
}, 400);
}
// Check if species exists and get its associated datasets
const associatedDatasets = await db
.select({ datasetId: speciesDataset.datasetId })
.from(speciesDataset)
.where(eq(speciesDataset.speciesId, speciesId));
if (associatedDatasets.length === 0) {
return c.json({
error: "Species not found or not associated with any dataset"
}, 404);
}
// Check if user has EDIT permission on at least one associated dataset
let hasPermission = false;
for (const assoc of associatedDatasets) {
if (await checkUserPermission(db, userId, assoc.datasetId, 'EDIT')) {
hasPermission = true;
break;
}
}
if (!hasPermission) {
return c.json({
error: "You don't have permission to create call types for this species"
}, 403);
}
// Create call type
const now = new Date();
const [createdCallType] = await db.insert(callType).values({
id: id.trim(),
speciesId: speciesId.trim(),
label: label.trim(),
createdBy: userId,
createdAt: now,
lastModified: now,
modifiedBy: userId,
active: true,
}).returning({
id: callType.id,
speciesId: callType.speciesId,
label: callType.label,
createdAt: callType.createdAt,
active: callType.active,
});
console.log("Created call type:", createdCallType.id, "for species:", speciesId, "by user:", userId);
return c.json({
data: createdCallType
}, 201);
} catch (error) {
console.error("Error creating call type:", error);
if (error instanceof Error && error.message.includes('duplicate key')) {
return c.json({
error: "A call type with this ID already exists"
}, 400);
}
return c.json(
{
error: "Failed to create call type",
details: error instanceof Error ? error.message : String(error),
},
500
);
}
});
/**
* Protected API route to update an existing call type
*
* @route PUT /api/call-types/:id
* @authentication Required
* @param {string} id - Call type ID in URL path
* @param {Object} body - Call type data to update (label, active)
* @returns {Object} Response containing:
* - data: Updated call type object
* @description Updates an existing call type. Requires EDIT permission on any dataset containing the parent species.
*/
app.put("/api/call-types/:id", authenticate, async (c) => {
try {
// Get user from JWT
const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
const userId = jwtPayload.sub;
// Get call type ID from URL parameters
const callTypeId = c.req.param("id");
// Parse request body
const body = await c.req.json();
const { label, active } = body;
// Connect to database
const sql = neon(c.env.DATABASE_URL);
const db = drizzle(sql);
// Check if call type exists and get its species
const existingCallType = await db
.select({
id: callType.id,
speciesId: callType.speciesId,
active: callType.active
})
.from(callType)
.where(eq(callType.id, callTypeId))
.limit(1);
if (existingCallType.length === 0) {
return c.json({
error: "Call type not found"
}, 404);
}
const speciesId = existingCallType[0].speciesId;
// Get datasets associated with the species to check permissions
const associatedDatasets = await db
.select({ datasetId: speciesDataset.datasetId })
.from(speciesDataset)
.where(eq(speciesDataset.speciesId, speciesId));
// Check if user has EDIT permission on at least one associated dataset
let hasPermission = false;
for (const assoc of associatedDatasets) {
if (await checkUserPermission(db, userId, assoc.datasetId, 'EDIT')) {
hasPermission = true;
break;
}
}
if (!hasPermission) {
return c.json({
error: "You don't have permission to edit this call type"
}, 403);
}
// Build update object with only provided fields
const updateData: Record<string, unknown> = {
lastModified: new Date(),
modifiedBy: userId,
};
// Validate and add label if provided
if (label !== undefined) {
if (typeof label !== 'string' || label.trim().length === 0) {
return c.json({
error: "Field 'label' must be a non-empty string"
}, 400);
}
if (label.length > 100) {
return c.json({
error: "Field 'label' must be 100 characters or less"
}, 400);
}
updateData.label = label.trim();
}
// Add active status if provided (for soft delete)
if (active !== undefined) {
if (typeof active !== 'boolean') {
return c.json({
error: "Field 'active' must be a boolean"
}, 400);
}
updateData.active = active;
}
// Update the call type
const [updatedCallType] = await db
.update(callType)
.set(updateData)
.where(eq(callType.id, callTypeId))
.returning({
id: callType.id,
speciesId: callType.speciesId,
label: callType.label,
createdAt: callType.createdAt,
lastModified: callType.lastModified,
active: callType.active,
});
console.log("Updated call type:", callTypeId, "by user:", userId);
return c.json({
data: updatedCallType
});
} catch (error) {
console.error("Error updating call type:", error);
return c.json(
{
error: "Failed to update call type",
details: error instanceof Error ? error.message : String(error),
},
500
);
}
});
// ============================================================================
// EBIRD SEARCH
// ============================================================================
/**
* Protected API route to search eBird taxonomy
*
* @route GET /api/ebird/search
* @authentication Required
* @param {string} q - Search query (common name, scientific name, family, or species code)
* @returns {Object} Response containing:
* - data: Array of matching eBird taxonomy entries
* @description Searches the eBird taxonomy v2024 materialized view for species matching the query.
* Searches across primary_com_name, sci_name, family, and species_code fields.
*/
app.get("/api/ebird/search", authenticate, async (c) => {
try {
const query = c.req.query("q");
if (!query || query.trim().length === 0) {
return c.json({
error: "Missing or empty query parameter 'q'"
}, 400);
}
if (query.length < 2) {
return c.json({
error: "Query must be at least 2 characters long"
}, 400);
}
// Database connection
const sql = neon(c.env.DATABASE_URL);
const db = drizzle(sql);
// Search across multiple fields with case-insensitive partial matching
const searchTerm = `%${query.trim().toLowerCase()}%`;
// Use raw SQL query for materialized view until Drizzle typing issue is resolved
const results = await db.execute(
sqlExpr`
SELECT
id,
species_code as "speciesCode",
primary_com_name as "primaryComName",
sci_name as "sciName",
bird_order as "birdOrder",
family
FROM ebird_taxonomy_v2024
WHERE
LOWER(primary_com_name) LIKE ${searchTerm} OR
LOWER(sci_name) LIKE ${searchTerm} OR
LOWER(family) LIKE ${searchTerm} OR
LOWER(species_code) LIKE ${searchTerm}
ORDER BY primary_com_name
LIMIT 20
`
);
return c.json({
data: results.rows || results
});
} catch (error) {
console.error("Error searching eBird taxonomy:", error);
return c.json(
{
error: "Failed to search eBird taxonomy",
details: error instanceof Error ? error.message : String(error),
},
500
);
}
});
// ============================================================================
// SELECTION
// ============================================================================
/**
* Protected API route to fetch files with selections for a specific dataset and species
*
* @route GET /api/selection
* @authentication Required
* @param {string} datasetId - Required query parameter specifying the dataset
* @param {string} speciesId - Required query parameter specifying the species to filter by
* @param {number} [page=1] - Optional page number for pagination (starts at 1)
* @param {number} [pageSize=100] - Optional page size (10-500, defaults to 100)
* @param {string} [solarNight] - Optional filter for files recorded during solar night ('true'/'false')
* @param {string} [civilNight] - Optional filter for files recorded during civil night ('true'/'false')
* @returns {Object} Response containing:
* - data: Array of file objects with metadata, mothMetadata, and species information
* - pagination: Object with pagination metadata
* - filters: Object showing the filters that were applied
* @error 400 - If datasetId or speciesId is missing or page is invalid
* @description Returns files that contain selections labeled with the specified species
* Designed for cross-cluster searches within a dataset
*
* Each file object includes:
* - Basic file information (name, path, timestamp, duration, etc.)
* - File metadata (JSON format)
* - Recording device metadata (gain, battery voltage, temperature)
* - Species information
*
* Uses efficient query optimization with:
* - Parallel Promise.all for metadata queries
* - Proper SQL JOINs to avoid N+1 query problems
* - Data transformation for optimal client-side consumption
*/
app.get("/api/selection", authenticate, async (c) => {
try {
// Get query parameters
const datasetId = c.req.query("datasetId");
const speciesId = c.req.query("speciesId");
const page = parseInt(c.req.query("page") || "1", 10);
const pageSize = parseInt(c.req.query("pageSize") || "100", 10);
const solarNight = c.req.query("solarNight");
const civilNight = c.req.query("civilNight");
// Validate parameters
if (!datasetId) {
return c.json({
error: "Missing required query parameter: datasetId"
}, 400);
}
if (!speciesId) {
return c.json({
error: "Missing required query parameter: speciesId"
}, 400);
}
// Validate and limit page size to prevent excessive queries
const limitedPageSize = Math.min(Math.max(pageSize, 10), 500);
const offset = (page - 1) * limitedPageSize;
// Validate page number
if (page < 1) {
return c.json({
error: "Invalid page parameter: must be greater than 0"
}, 400);
}
// Connect to the database
const sql = neon(c.env.DATABASE_URL);
const db = drizzle(sql);
// Build base filter condition - active files with selections of the specified species
// This forms the core of our WHERE clause for both queries
let baseCondition = sqlExpr`
${file.active} = true
AND ${location.datasetId} = ${datasetId}
AND ${label.speciesId} = ${speciesId}
AND ${label.active} = true
`;
// Add filters for day/night if specified
if (solarNight === 'true') {
baseCondition = sqlExpr`${baseCondition} AND ${file.maybeSolarNight} = true`;
} else if (solarNight === 'false') {
baseCondition = sqlExpr`${baseCondition} AND (${file.maybeSolarNight} = false OR ${file.maybeSolarNight} IS NULL)`;
}
if (civilNight === 'true') {
baseCondition = sqlExpr`${baseCondition} AND ${file.maybeCivilNight} = true`;
} else if (civilNight === 'false') {
baseCondition = sqlExpr`${baseCondition} AND (${file.maybeCivilNight} = false OR ${file.maybeCivilNight} IS NULL)`;
}
// Get total count for pagination using a more efficient COUNT(1)
const countResult = await db
.select({
count: sqlExpr<number>`COUNT(DISTINCT ${file.id})`
})
.from(file)
.innerJoin(cluster, eq(file.clusterId, cluster.id))
.innerJoin(location, eq(cluster.locationId, location.id))
.innerJoin(selection, eq(selection.fileId, file.id))
.innerJoin(label, eq(label.selectionId, selection.id))
.where(baseCondition);
const totalFiles = Number(countResult[0].count);
const totalPages = Math.ceil(totalFiles / limitedPageSize);
// Early return if there are no matching files
if (totalFiles === 0) {
return c.json({
data: [],
pagination: {
currentPage: page,
pageSize: limitedPageSize,
totalPages: 0,
totalItems: 0,
hasNextPage: false,
hasPreviousPage: false,
},
filters: {
datasetId,
speciesId,
solarNight: solarNight === 'true' ? true : solarNight === 'false' ? false : null,
civilNight: civilNight === 'true' ? true : civilNight === 'false' ? false : null,
}
});
}
// Fetch files with pagination
const filesResult = await db
.select({
id: file.id,
fileName: file.fileName,
path: file.path,
timestampLocal: file.timestampLocal,
duration: file.duration,
sampleRate: file.sampleRate,
locationId: file.locationId,
clusterId: file.clusterId,
description: file.description,
maybeSolarNight: file.maybeSolarNight,
maybeCivilNight: file.maybeCivilNight,
moonPhase: file.moonPhase,
})
.from(file)
.innerJoin(cluster, eq(file.clusterId, cluster.id))
.innerJoin(location, eq(cluster.locationId, location.id))
.innerJoin(selection, eq(selection.fileId, file.id))
.innerJoin(label, eq(label.selectionId, selection.id))
.where(baseCondition)
.orderBy(file.timestampLocal)
.groupBy(file.id, file.fileName, file.path, file.timestampLocal, file.duration,
file.sampleRate, file.locationId, file.clusterId, file.description, file.maybeSolarNight,
file.maybeCivilNight, file.moonPhase)
.limit(limitedPageSize)
.offset(offset);
// Get all file IDs for metadata queries
const fileIds = filesResult.map(f => f.id);
// Safety check - shouldn't happen with our early return, but just in case
if (fileIds.length === 0) {
return c.json({
data: [],
pagination: {
currentPage: page,
pageSize: limitedPageSize,
totalPages: totalPages,
totalItems: totalFiles,
hasNextPage: page < totalPages,
hasPreviousPage: page > 1,
},
filters: {
datasetId,
speciesId,
solarNight: solarNight === 'true' ? true : solarNight === 'false' ? false : null,
civilNight: civilNight === 'true' ? true : civilNight === 'false' ? false : null,
}
});
}
// Convert file IDs to a properly formatted SQL string
const fileIdsQuoted = fileIds.map(id => `'${id}'`).join(',');
// Execute metadata queries in parallel using Promise.all for better performance
const [metadataResults, mothMetadataResults, speciesResults] = await Promise.all([
// Fetch file metadata
db.select({
fileId: fileMetadata.fileId,
json: fileMetadata.json
})
.from(fileMetadata)
.where(sqlExpr`${fileMetadata.fileId} IN (${sqlExpr.raw(fileIdsQuoted)})`),
// Fetch moth metadata
db.select({
fileId: mothMetadata.fileId,
gain: mothMetadata.gain,
batteryV: mothMetadata.batteryV,
tempC: mothMetadata.tempC
})
.from(mothMetadata)
.where(sqlExpr`${mothMetadata.fileId} IN (${sqlExpr.raw(fileIdsQuoted)})`),
// Fetch species data - pre-filtered to just get the relevant species
db.select({
fileId: selection.fileId,
speciesId: species.id,
speciesLabel: species.label,
ebirdCode: species.ebirdCode,
description: species.description
})
.from(selection)
.innerJoin(label, eq(label.selectionId, selection.id))
.innerJoin(species, eq(species.id, label.speciesId))
.where(sqlExpr`
${selection.fileId} IN (${sqlExpr.raw(fileIdsQuoted)})
AND ${label.speciesId} = ${speciesId}
AND ${label.active} = true
`)
]);
// Process metadata results into maps for efficient lookups
const metadataMap = metadataResults.reduce((acc, item) => {
let processedJson = item.json;
try {
if (typeof item.json === 'string' && (item.json.startsWith('{') || item.json.startsWith('['))) {
processedJson = JSON.parse(item.json);
} else if (typeof item.json === 'string' && item.json.includes('\\"')) {
const unescaped = item.json.replace(/\\"/g, '"');
processedJson = JSON.parse(unescaped);
}
} catch (e) {
console.error("Error processing metadata JSON:", e);
processedJson = item.json;
}
acc[item.fileId] = processedJson;
return acc;
}, {} as Record<string, unknown>);
// Process moth metadata
const mothMetadataMap = mothMetadataResults.reduce((acc, item) => {
acc[item.fileId] = {
gain: item.gain,
batteryV: item.batteryV !== null ? Number(item.batteryV) : null,
tempC: item.tempC !== null ? Number(item.tempC) : null
};
return acc;
}, {} as Record<string, {
gain: string | null;
batteryV: number | null;
tempC: number | null;
}>);
// Process species data with Map for better performance
const speciesMap = new Map<string, Array<{
id: string;
label: string;
ebirdCode: string | null;
description: string | null;
}>>();
speciesResults.forEach(item => {
if (!speciesMap.has(item.fileId)) {
speciesMap.set(item.fileId, []);
}
// Get the current species array for this file
const fileSpecies = speciesMap.get(item.fileId)!;
// Check if we already have this species (deduplication)
const existingSpeciesIndex = fileSpecies.findIndex(s => s.id === item.speciesId);
if (existingSpeciesIndex === -1) {
// Add species if it doesn't already exist for this file
fileSpecies.push({
id: item.speciesId,
label: item.speciesLabel,
ebirdCode: item.ebirdCode,
description: item.description
});
}
});
// Combine file data with metadata in a single operation
const files = filesResult.map(fileData => ({
...fileData,
metadata: metadataMap[fileData.id] || null,
mothMetadata: mothMetadataMap[fileData.id] || null,
species: speciesMap.get(fileData.id) || []
}));
// Return paginated results with metadata and filters
return c.json({
data: files,
pagination: {
currentPage: page,
pageSize: limitedPageSize,
totalPages: totalPages,
totalItems: totalFiles,
hasNextPage: page < totalPages,
hasPreviousPage: page > 1,
},
filters: {
datasetId,
speciesId,
solarNight: solarNight === 'true' ? true : solarNight === 'false' ? false : null,
civilNight: civilNight === 'true' ? true : civilNight === 'false' ? false : null,
}
});
} catch (error) {
console.error("Error fetching files by dataset and species:", error);
return c.json(
{
error: "Failed to fetch files by dataset and species",
details: error instanceof Error ? error.message : String(error),
},
500
);
}
});
/**
* Export the Hono app to be used by the Cloudflare Workers runtime
*
* @default app
* @description The main Hono application instance exported as the default
* This is the entry point that Cloudflare Workers will use when handling requests
*/
export default app;