# Skill: Specify safe database schemas with correct types, indexes, and migration commands

> When asking an AI agent to create or modify a database, explicitly set column types, required indexes, safe migration commands, and bulk-operation guards upfront. Applies to any project that stores persistent data (Postgres, Prisma, or equivalent).

Source: sodigi·learn — vibecoding/bazy-dannyh-bez-boli · https://sodigi.io/learn/vibecoding/bazy-dannyh-bez-boli

## When to use
- Starting a new database schema or adding tables to an existing project.
- The agent is about to run a migration command -- verify it is the safe one.
- Writing any bulk delete or update operation.
- Performance is degrading as data grows (likely a missing index).

## Core rules
- Store money as `numeric` (exact), never as `float` -- floats produce fractional-cent drift in totals.
- Store dates and times as `timestamptz` (with time zone), never as plain text.
- Store boolean flags as `boolean`, never as "yes"/"no" strings or 0/1 integers.
- Index every column you frequently filter or sort by: foreign keys, email, creation date, status fields.
- `migrate dev` is for local development only; NEVER run it on production -- it can silently wipe tables that conflict with the schema. Use `migrate deploy` on production.
- `deleteMany` or equivalent bulk-delete with no WHERE condition erases the entire table. Always require a condition.
- `updateMany` returns a count, not the updated records. Fetch the updated rows with a separate query if you need them.
- Prefer cursor pagination ("give me 20 after cursor X") over offset pagination ("give me page 50") for large lists.

## Procedure
1. List all tables and for each column specify: name, type (use the safe types above), whether it is nullable, and any uniqueness or default constraints.
2. List all columns that will be searched, sorted, or used in joins -- add an index for each.
3. Instruct the agent to use `migrate deploy` for production and `migrate dev` for local only. Ask it to show the migration plan before applying.
4. For any bulk delete or update, require the agent to state the WHERE condition explicitly before running. If no condition is appropriate, stop.
5. For paginated feeds, specify cursor-based pagination.
6. Ask for a plain-language summary of every table created and what each one stores.

## Ready-to-use prompt
```
Design a database schema following these rules:

1. Store prices and amounts as exact numeric, NOT float. Store dates and times with time zone (timestamptz).
2. Add an index on every column we will search or sort by: user id, creation date, status fields, foreign keys.
3. Any bulk delete or update MUST include an explicit condition for exactly which records to change. No condition -- stop and ask.
4. Use migrate dev locally only. For the live server, prepare migrate deploy. Show the migration plan before applying anything.
5. Explain in plain words every table you created and what it is for.
```

## Pitfalls
- Storing money as float -- produces "$99.99999999" totals in accounting and carts.
- Forgetting indexes -- invisible on small datasets, causes 8-second page loads at scale.
- Running `migrate dev` on a production database -- can wipe real data if schema conflicts exist.
- Running `deleteMany` or `updateMany` with no condition -- silently erases or changes entire tables.
- Assuming `updateMany` returns the changed records -- it only returns a count; fetch records separately.
- Skipping a backup before any dangerous schema change -- a backup is the only way to recover data after a mistake.