# Db Schema

> Apply when designing database schemas, writing migrations, or reviewing table structure. Covers naming, keys, indexes, constraints, nullability, and migration safety.

- **Type:** Skill
- **Install:** `agentstack add skill-sordi-ai-skill-everything-db-schema`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [sordi-ai](https://agentstack.voostack.com/s/sordi-ai)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [sordi-ai](https://github.com/sordi-ai)
- **Source:** https://github.com/sordi-ai/skill-everything/tree/main/skills/db-schema

## Install

```sh
agentstack add skill-sordi-ai-skill-everything-db-schema
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# Sub-Skill: Database Schema Design

**Purpose:** Prevent data-integrity bugs, race conditions, and irreversible migrations by enforcing database-level constraints and safe migration patterns before code reaches production.

---

## Rules

### Naming Conventions

1. **Snake case everywhere.** Always use `snake_case` for table names, column names, and index names; never use camelCase or PascalCase in SQL identifiers.
2. **Plural table names.** Always name tables in the plural (`users`, `orders`, `line_items`); singular names cause inconsistency across ORMs and query builders.
3. **Consistent FK column naming.** Always name foreign-key columns `_id` (e.g., `user_id`, `order_id`) so the reference is self-documenting.

### Primary Keys

4. **Surrogate primary key.** Always define an explicit surrogate primary key (`id`) on every table; never rely on natural keys as the sole PK unless the domain guarantees immutability.
5. **UUID vs serial.** Prefer `uuid` PKs for distributed systems or public-facing IDs; prefer `bigserial` / `bigint GENERATED ALWAYS AS IDENTITY` for high-insert internal tables where sort order matters.

### Foreign Keys & Referential Integrity

6. **Declare FK constraints.** Always declare `FOREIGN KEY` constraints in the schema; never rely on application-layer join logic to enforce referential integrity.
7. **Explicit ON DELETE behaviour.** Always specify `ON DELETE` behaviour (`CASCADE`, `SET NULL`, or `RESTRICT`) on every FK; omitting it defaults to `RESTRICT`, which can cause surprising errors at runtime.
8. **Missing FK is a data-integrity bug.** Never ship a column that references another table without a corresponding FK constraint — orphaned rows accumulate silently. Reference: ERR-2026-022

### Indexes

9. **Index every FK column.** Always add an index on every foreign-key column; unindexed FKs cause full-table scans on joins and cascade operations.
10. **Covering indexes for hot queries.** Use covering indexes (include all columns in the `SELECT`) for read-heavy queries rather than adding redundant columns to the base table.
11. **Unique constraints at DB level.** Always enforce uniqueness at the database level rather than relying on application-layer deduplication; race conditions between concurrent writes bypass app checks. Reference: ERR-2026-022

### Nullability & Defaults

12. **Explicit NOT NULL.** Always mark columns `NOT NULL` unless `NULL` carries distinct semantic meaning (unknown vs. absent); nullable columns complicate query logic and ORM mapping.
13. **Meaningful defaults.** Always supply a `DEFAULT` for columns that have a sensible zero-value (`0`, `''`, `false`, `now()`); avoid forcing callers to supply values the DB can compute.

### Timestamps & Soft Delete

14. **Standard audit columns.** Always include `created_at TIMESTAMPTZ NOT NULL DEFAULT now()` and `updated_at TIMESTAMPTZ NOT NULL DEFAULT now()` on every table; omitting them makes debugging and auditing impossible.
15. **Soft delete with deleted_at.** Prefer `deleted_at TIMESTAMPTZ` over hard deletes for user-facing entities; ensure all queries filter `WHERE deleted_at IS NULL` or use a view/partial index.

### Enum Columns

16. **DB-native enum or check constraint.** Always enforce enum-like columns with a `CHECK` constraint or a native `ENUM` type; never store unconstrained strings and validate only in the application.

### Migrations

17. **Up and down migrations.** Always write both an `up` and a `down` migration; a rollback path is mandatory before any migration reaches production.
18. **Idempotent migrations.** Always guard DDL statements with `IF NOT EXISTS` / `IF EXISTS` so re-running a migration does not error; this is required for blue-green and multi-replica deployments.
19. **Non-blocking index creation.** Use `CREATE INDEX CONCURRENTLY` (Postgres) or equivalent for indexes on large tables; blocking index creation causes downtime.

### Constraints Over App Logic

20. **Check constraints for invariants.** Always encode business invariants (e.g., `price > 0`, `quantity >= 0`) as `CHECK` constraints; application-layer validation is bypassed by direct DB writes, scripts, and migrations.

---

## See also

- `skills/code-quality/SKILL.md` — general code-quality rules that apply to migration scripts
- `skills/review-deployment/SKILL.md` — deployment checklist that includes migration safety gates

---

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [sordi-ai](https://github.com/sordi-ai)
- **Source:** [sordi-ai/skill-everything](https://github.com/sordi-ai/skill-everything)
- **License:** MIT

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-sordi-ai-skill-everything-db-schema
- Seller: https://agentstack.voostack.com/s/sordi-ai
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.