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

// 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;
  });
}

// 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,
      });
    }),
);

// 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`,
});

// 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;
  }
}

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`,
  });
}

// 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;
}

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