# Database Design

> Schema design, data modeling, migrations, and query correctness for relational databases (and when to use non-relational). Use when creating or altering tables, designing schemas, writing migrations, modeling money/inventory/ledgers, adding indexes, or when the user says "schema", "data model", "migration", "database design", or "normalize".

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

## Install

```sh
agentstack add skill-05-deepak-patidar-claude-skills-database-design
```

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

## About

# Database Design

The schema outlives every framework, every rewrite, and every AI model that touches the codebase. Code bugs ship and get fixed; schema mistakes and corrupted data are forever. Design the schema as if the application code were hostile — because one day, some version of it will be.

## Principle: the database defends its own invariants

Any rule the business cannot tolerate being violated goes **in the schema**, not only in application code:

- `NOT NULL` by default; nullable is an explicit decision meaning "absence is a valid state" (and every reader must handle it).
- `FOREIGN KEY` for every reference. `UNIQUE` for every natural key (mobile number, invoice number per tenant, SKU per account). `CHECK` for domains (quantity >= 0, status in allowed set, rate between 0 and 100).
- Application-level checks are UX; database constraints are truth. You need both, but only one of them holds under race conditions, bad deploys, and manual fixes.

## Modeling rules that prevent the classic disasters

- **Money and quantity are exact decimals** (`NUMERIC`), never float — no exceptions, including "it's just a percentage". Store currency explicitly if there could ever be more than one.
- **Timestamps**: store UTC (`timestamptz`), render in local zone. Name columns `*_at`. Every mutable table gets `created_at`/`updated_at`.
- **IDs**: surrogate primary key (UUID or bigint — pick per project and stay consistent); enforce natural keys with UNIQUE constraints, don't make them the PK. Never expose sequential IDs where enumeration leaks business volume (invoice counts, user counts) unless numbering is a product requirement.
- **State machines as data**: a `status` column needs its legal transitions written down (in code comments/docs) and enforced in exactly one service function. If history matters, an append-only events/audit table beats overwriting.
- **Financial and inventory data is append-only at heart**: model corrections as new compensating rows (credit notes, stock adjustments with reasons), not UPDATEs that destroy what happened. If a regulator, accountant, or angry customer could ask "what was it before?", keep the before.
- **Soft delete vs hard delete** is a product decision — but referencing rows must never dangle either way; decide `ON DELETE` behavior per FK deliberately (RESTRICT is the safe default).
- **Multi-tenant**: `account_id NOT NULL` on every tenant table, composite indexes leading with it, and row-level security or a mandatory scoping mechanism so a missing WHERE clause fails closed, not open.

## Normalization: the practical rule

Normalize until it hurts (one fact, one place — eliminate update anomalies), then denormalize only with a named owner for the copy and its update path. Storing `line_total = qty × rate` is fine for immutable invoice lines (they're history, frozen at sale time — that's not denormalization, that's a snapshot). Storing a customer's "current balance" is a cache — it needs either a recomputation job or transactional dual-writes, and a way to audit drift.

## Concurrency: assume two requests arrive at once

- Any read-modify-write (stock decrement, balance update, counter, "next invoice number") needs an explicit strategy: atomic single UPDATE (`SET stock = stock - :n WHERE stock >= :n`), `SELECT ... FOR UPDATE`, or a serializable/retry pattern. "We'll be small" doesn't prevent double-submits from one user's double-click.
- Uniqueness under concurrency is a constraint + handled violation, not check-then-insert.
- Multi-row financial writes (invoice + lines + stock + ledger) are **one transaction, all or nothing** — and keep transactions short; never call external APIs inside one.

## Indexes and queries

- Index every FK, every column in frequent WHERE/ORDER BY, and tenant-scoped composites `(account_id, x)`. But each index taxes every write — add them for observed query shapes, not superstition.
- Before shipping a list endpoint: it must paginate (keyset for large/growing sets), and its query must not be N+1 (verify by looking at emitted SQL once, not by assuming the ORM is smart).
- `EXPLAIN` any query you're about to optimize; never index-guess.

## Migration discipline

- Migrations are code-reviewed, hand-verified artifacts — whether hand-written or generated, read every line and know its lock behavior before it touches production data.
- Additive first; destructive only after code no longer reads the old shape (see deployment-safety: expand → migrate → contract).
- Every migration answers: is it idempotent/re-runnable? does it run safely while old code is live? what's the down-path or restore plan? does the backfill batch (not one giant UPDATE locking the table)?

## When someone says "let's use NoSQL / JSONB for this"

JSONB/document columns are right for genuinely schemaless payloads you don't query relationally (provider webhook dumps, user-defined custom fields). They are wrong for anything with relationships, constraints, or reporting needs — that's just a schema you've decided not to enforce. Default to relational; earn your way out.

## Source & license

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

- **Author:** [05-deepak-patidar](https://github.com/05-deepak-patidar)
- **Source:** [05-deepak-patidar/claude-skills](https://github.com/05-deepak-patidar/claude-skills)
- **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-05-deepak-patidar-claude-skills-database-design
- Seller: https://agentstack.voostack.com/s/05-deepak-patidar
- 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%.