From aai-dev-database
Provides patterns for designing normalized database schemas with relationships, primary keys, data types, and standard fields using SQL and Prisma. Useful for initial schema planning.
How this skill is triggered — by the user, by Claude, or both
Slash command
/aai-dev-database:schema-designThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Patterns for designing effective database schemas.
Patterns for designing effective database schemas.
-- UUID (recommended for distributed systems)
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
...
);
-- ULID (sortable, URL-safe)
CREATE TABLE posts (
id CHAR(26) PRIMARY KEY,
...
);
-- Auto-increment (simple, sequential)
CREATE TABLE logs (
id SERIAL PRIMARY KEY,
...
);
// One-to-One
model User {
id String @id @default(uuid())
profile Profile?
}
model Profile {
id String @id @default(uuid())
userId String @unique
user User @relation(fields: [userId], references: [id])
}
// One-to-Many
model User {
id String @id @default(uuid())
posts Post[]
}
model Post {
id String @id @default(uuid())
authorId String
author User @relation(fields: [authorId], references: [id])
}
// Many-to-Many
model Post {
id String @id @default(uuid())
tags Tag[]
}
model Tag {
id String @id @default(uuid())
posts Post[]
}
// Explicit join table (when you need extra fields)
model PostTag {
postId String
tagId String
createdAt DateTime @default(now())
post Post @relation(fields: [postId], references: [id])
tag Tag @relation(fields: [tagId], references: [id])
@@id([postId, tagId])
}
-- Text
VARCHAR(255) -- Names, emails, short text
TEXT -- Long content, descriptions
CHAR(2) -- Country codes, fixed-length
-- Numbers
INTEGER -- Counts, IDs
BIGINT -- Large numbers, timestamps
DECIMAL(10,2) -- Money, precise decimals
REAL/FLOAT -- Scientific (avoid for money!)
-- Date/Time
TIMESTAMP -- Date + time with timezone
DATE -- Date only
INTERVAL -- Time periods
-- Binary
BYTEA -- Binary data, files
UUID -- Universally unique identifiers
-- JSON
JSONB -- Flexible data, preferences
model BaseEntity {
// Primary key
id String @id @default(uuid())
// Timestamps
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
// Soft delete
deletedAt DateTime?
// Audit (optional)
createdBy String?
updatedBy String?
}
-- Bad: Multiple values in one column
CREATE TABLE users (
id INT,
phone_numbers VARCHAR(255) -- "123-456, 789-012"
);
-- Good: Separate table
CREATE TABLE users (
id INT PRIMARY KEY
);
CREATE TABLE user_phones (
id INT PRIMARY KEY,
user_id INT REFERENCES users(id),
phone_number VARCHAR(20)
);
-- Bad: Partial dependency
CREATE TABLE order_items (
order_id INT,
product_id INT,
product_name VARCHAR(255), -- Depends only on product_id
quantity INT,
PRIMARY KEY (order_id, product_id)
);
-- Good: Separate tables
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE order_items (
order_id INT,
product_id INT REFERENCES products(id),
quantity INT,
PRIMARY KEY (order_id, product_id)
);
-- Bad: Transitive dependency
CREATE TABLE employees (
id INT PRIMARY KEY,
department_id INT,
department_name VARCHAR(255) -- Depends on department_id, not employee
);
-- Good: Separate tables
CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE employees (
id INT PRIMARY KEY,
department_id INT REFERENCES departments(id)
);
-- Denormalized for performance
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
-- Snapshot at order time
user_email VARCHAR(255),
user_name VARCHAR(255),
-- Cached totals
item_count INT,
total_amount DECIMAL(10,2)
);
// Option 1: Separate tables
model Comment {
id String @id @default(uuid())
content String
postId String?
articleId String?
post Post? @relation(fields: [postId], references: [id])
article Article? @relation(fields: [articleId], references: [id])
}
// Option 2: Type column + ID
model Comment {
id String @id @default(uuid())
content String
targetType String // "post" | "article"
targetId String
@@index([targetType, targetId])
}
// Hierarchical data (categories, org chart)
model Category {
id String @id @default(uuid())
name String
parentId String?
parent Category? @relation("CategoryHierarchy", fields: [parentId], references: [id])
children Category[] @relation("CategoryHierarchy")
}
// User relationships (followers)
model User {
id String @id @default(uuid())
following Follow[] @relation("Following")
followers Follow[] @relation("Followers")
}
model Follow {
followerId String
followingId String
createdAt DateTime @default(now())
follower User @relation("Following", fields: [followerId], references: [id])
following User @relation("Followers", fields: [followingId], references: [id])
@@id([followerId, followingId])
}
// Enum (fixed, small set)
enum Role {
USER
ADMIN
MODERATOR
}
model User {
role Role @default(USER)
}
// Lookup table (dynamic, many values)
model Status {
id Int @id @default(autoincrement())
name String @unique
orders Order[]
}
model Order {
statusId Int
status Status @relation(fields: [statusId], references: [id])
}
model Tenant {
id String @id @default(uuid())
name String
}
model User {
id String @id @default(uuid())
tenantId String
tenant Tenant @relation(fields: [tenantId], references: [id])
@@index([tenantId])
}
// Always filter by tenant
const users = await prisma.user.findMany({
where: { tenantId: currentTenant.id },
});
-- One schema per tenant
CREATE SCHEMA tenant_123;
CREATE TABLE tenant_123.users (...);
-- Dynamic schema selection
SET search_path TO tenant_123;
SELECT * FROM users;
Used by:
database-developer agentnpx claudepluginhub bradtaylorsf/alphaagent-teamProvides behavioral guidelines to reduce common LLM coding mistakes, focusing on simplicity, surgical changes, assumption surfacing, and verifiable success criteria.
Searches, retrieves, and installs Agent Skills from prompts.chat registry using MCP tools like search_skills and get_skill. Activates for finding skills, browsing catalogs, or extending Claude.
Creates, edits, and optimizes skills for Claude Code, including drafting, evaluating with test prompts, iterating on performance, and improving skill descriptions for better triggering accuracy.