api-resource-lifecycle

name	api-resource-lifecycle
description	Guides CRUD operations for API resources with cascading dependencies, descriptive validation, and orphan prevention. Use when adding delete/remove operations, creating validation logic, building resources that depend on other resources, or when the user mentions "cascade delete", "orphan records", "duplicate detection", "validation errors", "resource cleanup", or "rollback on failure".
metadata	{"internal":true}

API Resource Lifecycle

Patterns for building reliable CRUD operations in Tambo Cloud.

Gotchas

• The codebase uses CONFLICT for duplicate name errors in skills but BAD_REQUEST for the same pattern in MCP servers. Always use CONFLICT for duplicates.
• PostgreSQL unique violation code is 23505. Catch it and map to a domain-specific exception rather than letting the raw DB error propagate.
• When replacing a provider key, all skills with externalSkillMetadata for that provider must have their metadata cleared, or they'll reference a stale key.
• Skills are silently skipped (not errored) at runtime when the provider doesn't support them. This is intentional -- don't add validation that blocks skill creation for unsupported providers.
• Promise.allSettled (not Promise.all) for batch external API calls -- partial failures need cleanup, not an all-or-nothing abort.

Cascading Deletes

Default to onDelete: "cascade" in the schema. Only use manual transaction cascades when deletion requires external API calls, metadata cleanup, or cross-reference logic.

// packages/db/src/schema.ts
export const skills = pgTable("skills", {
  id: text("id").primaryKey(),
  projectId: text("project_id")
    .notNull()
    .references(() => projects.id, { onDelete: "cascade" }),
  // ...
});

Manual cascades in transactions

When deletion requires cleanup beyond FK cascades (external APIs, metadata, cross-references), wrap in a transaction:

// packages/db/src/operations/project.ts
export async function deleteProject(db: HydraDb, id: string): Promise<boolean> {
  return await db.transaction(async (tx) => {
    await tx
      .delete(schema.providerKeys)
      .where(eq(schema.providerKeys.projectId, id));
    await tx.delete(schema.apiKeys).where(eq(schema.apiKeys.projectId, id));
    await tx
      .delete(schema.projectMembers)
      .where(eq(schema.projectMembers.projectId, id));
    const deleted = await tx
      .delete(schema.projects)
      .where(eq(schema.projects.id, id))
      .returning();
    return deleted.length > 0;
  });
}

Reference: packages/db/src/operations/project.ts lines 255-278

Metadata cleanup on replacement

When a resource is replaced (not deleted), clear dependent metadata so dependents re-sync under the new resource:

// apps/web/server/api/routers/project.ts - addProviderKey
// When replacing a provider key, clear skill metadata for that provider
const skills = await operations.listSkillsForProject(ctx.db, projectId);
await Promise.all(
  skills
    .filter((s) => s.externalSkillMetadata?.[providerName])
    .map(async (s) => {
      const { [providerName]: _, ...remaining } = s.externalSkillMetadata ?? {};
      return operations.updateSkill(ctx.db, {
        projectId,
        skillId: s.id,
        externalSkillMetadata: remaining,
      });
    }),
);

Reference: apps/web/server/api/routers/project.ts lines 863-880

Rules

• If a child record has no meaning without its parent, use onDelete: "cascade" in the schema
• If deletion requires multi-step cleanup or external API calls, wrap in a transaction
• When replacing a resource, clear dependent metadata so dependents re-sync
• Always delete children before the parent in manual cascades
• Check for affected dependents and document the cascade chain in code comments

Validation Errors

Every error must say what went wrong and what to do instead. Include the specific value that failed and an example of what's valid.

// Zod: include format example
name: z.string().min(1, "Name is required").max(64)
  .regex(SKILL_NAME_PATTERN, "Name must be kebab-case (e.g. scheduling-assistant)"),

// tRPC: include the conflicting value
throw new TRPCError({
  code: "CONFLICT",
  message: `Server key "${serverKey}" is already in use by another MCP server in this project`,
});

Error code mapping

Situation	tRPC Code	When to use
Resource not found	NOT_FOUND	ID lookup returned null
Input validation failed	BAD_REQUEST	Zod schema or business rule violation
Duplicate resource	CONFLICT	Name/key already exists
Unexpected failure	INTERNAL_SERVER_ERROR	Catch-all for unhandled errors

Duplicate Detection

Default to database unique constraints with custom exception mapping. Only use pre-creation queries when no DB constraint exists.

// packages/db/src/operations/skills.ts
const PG_UNIQUE_VIOLATION = "23505";
const SKILLS_NAME_UNIQUE_CONSTRAINT = "skills_project_id_name_idx";

export class SkillNameConflictError extends Error {
  constructor(name: string) {
    super(`A skill named "${name}" already exists in this project`);
    this.name = "SkillNameConflictError";
  }
}

export async function createSkill(
  db: HydraDb,
  data: NewSkill,
): Promise<DBSkill> {
  try {
    const [skill] = await db.insert(schema.skills).values(data).returning();
    return skill;
  } catch (error) {
    if (isSkillNameConflict(error)) {
      throw new SkillNameConflictError(data.name);
    }
    throw error;
  }
}

Pre-creation queries (fallback)

When no unique DB constraint exists, query before inserting:

const existingKeys = await getExistingServerKeys(ctx.db, projectId);
if (existingKeys.includes(serverKey)) {
  throw new TRPCError({
    code: "CONFLICT",
    message: `Server key "${serverKey}" is already in use by another MCP server in this project`,
  });
}

Reference: apps/web/server/api/routers/tools.ts lines 119-126

Orphan Prevention

When creating a resource that syncs to an external API, roll back on failure:

// apps/web/server/api/routers/skills.ts
// Create in DB first, then sync to provider
const skill = await operations.createSkill(ctx.db, { ... });

// If provider sync fails, delete the DB record
try {
  await createSkillOnProviderAndPersist(ctx.db, input.projectId, skill);
} catch (error) {
  await operations.deleteSkill(ctx.db, input.projectId, skill.id);
  throw error;
}

Rules:

• Create the DB record first (gives you an ID for the external API call)
• If the external sync fails, delete the DB record to avoid orphans
• The user sees an error and can retry, rather than seeing a broken record
• For batch operations, use Promise.allSettled and clean up partial failures

Reference: apps/web/server/api/routers/skills.ts lines 195-204

Checklist

When building a new CRUD operation:

Resource Lifecycle Checklist:
- [ ] Delete cascades documented (which dependents are affected?)
- [ ] Database-level cascades set up for simple parent-child FK relationships
- [ ] Manual cascade logic wrapped in a transaction for complex cleanup
- [ ] All Zod validation messages are descriptive with examples
- [ ] TRPCError messages include the specific value that caused the error
- [ ] Duplicate detection before creation (custom exception or pre-query)
- [ ] Error codes are correct (CONFLICT for duplicates, NOT_FOUND for missing)
- [ ] External API sync failures roll back the DB record
- [ ] Resource replacement clears dependent metadata