Database design

Skill vignesh2027/AI-AGENT-SKILLS/skills/database-design

Turn your ai agent into senior engineer..The result is fast code that fails slowly. AI Agent Skills solves this by giving agents the same disciplined workflows senior engineers use

Install

npx -y skills add vignesh2027/AI-AGENT-SKILLS --skill database-design

Assembled from the repository path, not quoted from the project. Check it against their README if it does not work.

One thing to look at

2 stars2 stars. Stars are a popularity signal and not a quality one, but at this level it is likely that nobody has read this closely except its author, and you would be relying on your own review.

What its author says it does

Copied from the file, not written here

Schema design, migrations, indexing, and query patterns for maintainable and performant databases

SKILL.md

2.7 KB, as published. Nobody here has run it

Overview

Database schemas are among the hardest things to change in a production system. Migrations run during live traffic. Indexes affect every query. Schema choices made today constrain options for years. This skill gets them right from the start.

When to Use

Before designing a new table or collection
Before writing a database migration
When queries are slow and the cause is suspected to be the schema or indexes
When designing a new service's data layer

Process

Step 1: Design for the queries, not just the data

Understand the access patterns before normalizing. Which queries are in the critical path? What are the read/write ratios? This drives index and schema decisions.

Step 2: Normalize first, denormalize deliberately

Start with a normalized design. Denormalize only when profiling shows it's necessary, and document why.

Step 3: Choose IDs carefully

Use UUIDs or ULIDs for globally unique IDs (not auto-increment integers for externally visible IDs)
Never expose integer sequence IDs to users (enumeration attack)
Ensure IDs are indexed

Step 4: Migrations — backward compatible first

Every migration must be backward compatible with the current code:

Deploy migration (add new column, add new table)
Deploy code that uses the new column
Deploy cleanup migration (drop old column) — only after old code is gone

Never drop a column in the same deploy that stops using it.

Step 5: Index strategy

Index columns that appear in WHERE clauses, JOIN conditions, and ORDER BY of hot queries. Don't over-index — each index slows writes.

Run EXPLAIN on every hot query before deploying.

Step 6: Soft deletes vs hard deletes

For audit trails, compliance, or reference integrity: use soft deletes (deleted_at timestamp). For data that must be truly erased (GDPR): implement hard delete + audit log.

Step 7: Timestamps and audit columns

Every table should have: created_at, updated_at. Tables with audit requirements: created_by, updated_by.

Step 8: Test migrations

Test every migration against a production-size dataset:

Does it run in an acceptable time window?
Does it lock tables in ways that will cause timeouts?
Can it be rolled back?

Verification Requirements

Access patterns identified before schema designed
Migrations are backward compatible
Hot queries have EXPLAIN run
Indexes added for WHERE/JOIN/ORDER BY columns
Migration tested against production-size data
Rollback for migration documented