Preface¶

When building backends with AI programming tools like Cursor, Claude Code, and Codex, business code is often written quickly, but database modeling often reveals flaws: no foreign keys between tables, many-to-many relationships directly stored as arrays, mixed use of TIMESTAMP and TIMESTAMPTZ, no indexes on foreign keys, and orphaned rows in child tables when parent records are deleted. These issues are not noticeable with small local datasets, but they will surface in production as slow queries, constraint conflicts, and dirty data.

The problem usually is not “whether you know how to write CREATE TABLE”, but rather the lack of a repeatable design workflow. The training data for AI models includes all kinds of styles, so agents easily produce schemas that “work but are non-standard”. database-design is a reusable SKILL.md that formalizes entity extraction, relationship selection, constraints, indexes, and ORM mapping, allowing assistants to design database tables step-by-step instead of guessing based on impression.

This article introduces what this skill is, what rules it covers, how to install it, and how to use it, after verifying against the official SKILL.md and the documentation of the awesome-cursor-skills repository.

What It Is¶

database-design is listed under the “Planning & Architecture” category in spencerpauly/awesome-cursor-skills, with a CC0-1.0 license. Currently, there is only one file in the directory: resources/database-design/SKILL.md.

The official frontmatter description is:

Design database schemas — tables, relationships, indexes, constraints, and ORM setup. Covers relational design, normalization, and common patterns.

That is: design relational database schemas based on requirements, covering tables, relationships, indexes, constraints, and ORM configuration; it also includes normalization and common modeling patterns.

It follows the universal Agent Skills format and can be used in tools that support this standard, such as Cursor, Claude Code, and Codex CLI. The first sentence of the skill body defines the task explicitly: Design a database schema based on requirements. The frontmatter also includes user-invocable: true, allowing explicit invocation with the /database-design slash command in tools that support it; Cursor’s official documentation notes that you can also search for the skill by name by typing / in an Agent conversation.

Repository address: https://github.com/spencerpauly/awesome-cursor-skills/tree/main/resources/database-design

Core Features: Six-Step Workflow + Constraint Checklist¶

The official SKILL.md splits the design process into six steps, followed by best practices, common patterns, and a few principles. Below is an explanation following the original structure.

1. Identify Entities¶

Extract core entities (nouns) from the requirements, such as Users, Teams, Projects, Tasks, Comments. Each entity corresponds to one table. This step sounds simple, but it prevents agents from combining “user and profile” into a single wide table, or storing comments as JSON fields right off the bat.

2. Define Relationships¶

Instead of just saying “link them together”, the skill uses a table to map four types of relationships to concrete implementations:

It is explicitly stated that many-to-many relationships must use a junction table. The common AI pattern of “storing project_ids UUID[] in the users table” is not included in this checklist.

3. Design Table Structures¶

The official example follows PostgreSQL style, using UUID for primary keys, TIMESTAMPTZ for timestamps, and ON DELETE clauses for foreign keys:

CREATE TABLE users (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email       TEXT NOT NULL UNIQUE,
  name        TEXT NOT NULL,
  avatar_url  TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE projects (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT NOT NULL,
  owner_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

This example demonstrates several things at once: only allow NULL for optional columns (avatar_url), add UNIQUE constraints on natural keys (email), and cascade delete projects when a user is deleted.

4. Apply Best Practices¶

Primary Keys:
- Use UUID for IDs in distributed systems or externally exposed IDs;
- Use SERIAL / BIGSERIAL for internal-only IDs (faster joins).

Timestamps:
- Always add created_at and updated_at to tables;
- Use timezone-aware TIMESTAMPTZ, not TIMESTAMP.

Naming:
- Table names: plural snake_case (e.g. users, project_members);
- Column names: singular snake_case (e.g. user_id, created_at);
- Indexes: idx_<table>_<columns> (e.g. idx_users_email).

Constraints:
- Use NOT NULL unless the column is truly optional;
- Add UNIQUE to natural keys (email, slug, external ID);
- Always specify ON DELETE behavior (CASCADE, SET NULL, RESTRICT) for foreign keys;
- Use CHECK for enumerated or value range constraints.

5. Add Indexes¶

The official example covers three types of indexes: common filter columns, unique lookups, and composite query patterns.

-- Columns frequently used for filtering or sorting
CREATE INDEX idx_projects_owner_id ON projects(owner_id);

-- Unique lookups
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Common query combinations
CREATE INDEX idx_tasks_project_status ON tasks(project_id, status);

When to add indexes: Foreign keys (almost always), columns in WHERE clauses, columns in ORDER BY clauses, and JOIN conditions.

When not to add indexes: Small tables (fewer than 1000 rows per the original skill text), low-cardinality columns (booleans, statuses with only three or four possible values), and columns that are rarely queried.

One easy-to-miss detail: the users.email column already has a UNIQUE constraint declared when the table is created, and the example also includes CREATE UNIQUE INDEX idx_users_email. In PostgreSQL, a UNIQUE constraint automatically creates a unique index, so if an agent writes both clauses verbatim, it will create duplicate indexes. You can verify against the constraint semantics of your target database when using this skill, and do not treat every line of the example as a mandatory migration to run.

6. ORM Configuration¶

The skill provides two side-by-side examples for Prisma and Drizzle, focusing on mapping snake_case database columns to camelCase in the application layer.

Prisma:

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String
  projects  Project[]
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
  @@map("users")
}

Drizzle:

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});

The original skill does not cover other ORMs like SQLAlchemy, TypeORM, or Ent. If your project uses a different ORM, you can keep the SQL layer rules and rewrite the ORM layer according to your tech stack; do not expect this skill to automatically generate model files for other frameworks.

Common Patterns and Principles¶

The skill also lists five high-frequency patterns:
- Soft Deletes: Add a deleted_at TIMESTAMPTZ column instead of physically deleting rows;
- Audit Logs: A separate audit_events table with fields including entity_type, entity_id, action, actor_id, and payload;
- Tags: A junction table (e.g. task_tags) using task_id + tag_id;
- Trees / Hierarchies: Self-referential parent_id or materialized paths (/1/4/7/);
- Polymorphic Associations: Use entity_type + entity_id; the original text explicitly states avoid this when possible, prefer separate foreign keys instead.

The final principles are equally specific:
- Design following Third Normal Form (3NF) first, only denormalize after measuring performance issues;
- Do not store derived data unless you have a caching and invalidation strategy;
- Use database enums or CHECK constraints for status fields, not free text;
- Always consider “what happens when a parent record is deleted” during design.

Installation and Activation¶

This skill only has one SKILL.md file, and the installation method is to place the file in the directory that the Agent scans for skills. The awesome-cursor-skills repository states: just copy the existing SKILL.md into .cursor/skills/.

Cursor¶

According to Cursor’s official documentation, skills will be automatically discovered from the following paths:

To be compatible with Claude and Codex, Cursor will also load .claude/skills/, .codex/skills/, and their corresponding user directories. The recommended project-level installation method is:

cd your-project

mkdir -p .cursor/skills/database-design
curl -o .cursor/skills/database-design/SKILL.md \
  https://raw.githubusercontent.com/spencerpauly/awesome-cursor-skills/main/resources/database-design/SKILL.md

You can also directly open the directory and copy the files manually:

https://github.com/spencerpauly/awesome-cursor-skills/tree/main/resources/database-design

After installation, open Cursor’s sidebar Customize → Skills, and you should see database-design in the Agent Decides section. For manual triggering, type / in an Agent conversation and search for the skill name.

Claude Code¶

Refer to the official documentation for Claude Code’s skill directory:

mkdir -p .claude/skills/database-design
curl -o .claude/skills/database-design/SKILL.md \
  https://raw.githubusercontent.com/spencerpauly/awesome-cursor-skills/main/resources/database-design/SKILL.md

You can then invoke it explicitly with /database-design, or have Claude match it automatically for requests like “design table structures / write Prisma schema”.

Codex CLI¶

Codex scans .agents/skills in the repository (from the current working directory up to the repository root) and the user directory $HOME/.agents/skills. The project-level installation method is:

mkdir -p .agents/skills/database-design
curl -o .agents/skills/database-design/SKILL.md \
  https://raw.githubusercontent.com/spencerpauly/awesome-cursor-skills/main/resources/database-design/SKILL.md

Codex’s documentation states that you can mention skills with $, or run /skills to view the list of discovered skills. If the new file does not appear, restart Codex.

Typical Usage Examples¶

The original skill text does not include a separate prompt template, but the task definition is clear: design a schema based on requirements. The following types of requests directly correspond to the six-step workflow; the Agent should produce SQL following the six steps instead of just throwing out a “table-looking” draft.

Scenario 1: Generate the first version of database tables from requirements¶

/database-design

Build a team project management tool: users can create projects, each project has tasks, and tasks can have comments and tags.
First identify the entities and relationships, then provide PostgreSQL CREATE TABLE statements with ON DELETE clauses for foreign keys.

Following the skill’s rules, this should at least include users, projects, tasks, and comments, with a task_tags junction table for tags instead of storing a text array in the tasks table.

Scenario 2: Add indexes and constraints¶

Please check the existing projects / tasks tables according to the database-design index rules:
Which foreign keys and WHERE columns should have indexes, and which low-cardinality columns should not have indexes.

The skill requires that almost all foreign keys should have indexes, while avoiding over-indexing boolean columns and statuses with few possible values. The composite index example idx_tasks_project_status ON tasks(project_id, status) corresponds to common queries like “filter by project then filter by status”.

Scenario 3: Map SQL to ORM¶

Convert the previously generated users / projects schema to Prisma and Drizzle.
Keep snake_case for column names in the database, use camelCase in the application layer.

Following the official snippet, the Prisma code should include @map("created_at") and @@map("users"), and the Drizzle code should include { withTimezone: true } for timestamps, aligning with the rule of “only use TIMESTAMPTZ”.

Scenario 4: Delete policies and soft deletes¶

When a user deletes their account, their projects should be deleted together, but task comments should be retained for audit trails.
Please provide a solution following the ON DELETE and soft delete patterns in the skill.

The original skill requires that you always consider what happens when a parent record is deleted; use deleted_at for soft deletes, and create a separate audit_events table for auditing. The Agent should include CASCADE / SET NULL / RESTRICT in foreign keys, instead of just implementing deletions in application code with DELETE FROM ....

Applicable Scenarios and Notes¶

Who It Is For¶

• Developers building web/SaaS backends from scratch with AI assistants, who need a first version relational model;
• Teams with existing draft schemas who want their Agent to add foreign keys, indexes, naming conventions, and timestamps according to a checklist;
• Projects using PostgreSQL + Prisma or Drizzle (the closest match to the official examples);
• Teams that want to formalize “first 3NF, then denormalize on demand” as a team convention instead of reminding everyone verbally every time.

Usage Limitations¶

1. This is an instruction pack, not a migration tool. There is no scripts/ directory, it will not connect to a database or run prisma migrate. It only constrains how the Agent “designs” the schema; you still need to confirm and execute the final code.
2. Examples are biased towards PostgreSQL. UUID, gen_random_uuid(), and TIMESTAMPTZ are all PostgreSQL-specific syntax. MySQL / SQLite users need to replace types and functions themselves; the skill does not provide templates for other dialects.
3. Only Prisma and Drizzle are covered for ORMs. Other frameworks are not officially supported.
4. It covers relational modeling, not operational tuning. Connection pooling, RLS, and slow query diagnostics are not included in this skill. If your project runs on Supabase / Neon, you will need to install the corresponding database best practice skill separately.
5. “Fewer than 1000 rows does not need indexes” is a heuristic rule. Production tables grow quickly, so do not interpret this as never adding foreign key indexes to small tables.
6. Polymorphic associations are explicitly downgraded. When you need “comments can be attached to both tasks and documents”, prefer separate foreign keys instead of using entity_type + entity_id right away.
7. Output still requires manual review. For example, ON DELETE CASCADE will physically delete child rows, which may conflict with soft deletes and audit logs; after the Agent generates code according to the rules, you still need to adjust the deletion policy to match the product semantics.

Summary¶

AI can write business code “make it work first”, but database schemas usually do not have this luxury. Missing a foreign key, using the wrong delete behavior, or forgetting a set of indexes will make the cost of fixing migrations later much higher. database-design collects entity identification, relationship implementation, constraints, indexes, and ORM mapping into a short SKILL.md, with an installation cost of just copying one file.

It does not solve the problem of “whether the Agent can write SQL”, but rather “whether the generated database tables follow a unified standard”. For developers who already use Cursor / Claude Code / Codex in their daily work, installing this skill first when asked “help me design database tables” will save more trouble than fixing production slow queries afterwards.

Official skill address:

https://github.com/spencerpauly/awesome-cursor-skills/tree/main/resources/database-design