sql-review

Files (1)

• SKILL.md

SKILL.md (first lines)

# SQL Review

Hold all SQL — new or existing — to the standards below. One checklist drives
both modes: reviewing flags deviations, writing avoids them from the start.

## Modes

**Review mode** — the user hands over existing SQL.

1. If a file path, diff, or migration was named, read it. Otherwise ask the
   user to paste the SQL.
2. Work through every checklist section below.
3. Report findings in the output format at the end.
4. If the SQL is clean, say so explicitly. Do not invent findings.

**Write mode** — the user asks for new or changed SQL.

1. Write the SQL with the checklist as constraints.
2. Self-review the result against every section.
3. Show the SQL. Name any standard you bent and why.

---

## Checklist

### Naming conventions

**Tables**
- [ ] `snake_case` only
- [ ] Plural nouns (`users`, `lab_orders`, not `user` or `labOrder`)
- [ ] No version suffixes (`v2`, `new_`, `_old`)
- [ ] Max 50 characters
- [ ] Business terminology, not source-system terminology

**Columns**
- [ ] `snake_case` only
- [ ] Primary key **must** be named `{table_singular}_id` — `id` is never
      acceptable (e.g. `organizations.organization_id`, `orders.order_id`,
      `ticket_tiers.ticket_tier_id`). Every table using `id` is a HIGH finding.
- [ ] Foreign keys named `{referenced_table_singular}_id`
- [ ] Timestamps use `{event}_at` past-tense format (`created_at`,
      `updated_at`, `deleted_at`)
- [ ] Dates use `{event}_date` format (`order_date`, not `date_order`)
- [ ] Booleans prefixed with `is_` or `has_` (`is_active`, `has_results`)
- [ ] Price/currency fields: decimal format (`19.99`) unless using cents
      suffix (`price_in_cents`)
- [ ] No abbreviations (`uid` → `user_id`, `usr_demo` → `user_demographics`)
- [ ] No reserved words as column names
- [ ] Max 50 characters

**Indexes**
- [ ] Regular indexes: `idx_{table}_{columns}`
- [ ] Unique indexes: `uidx_{table}_{columns}`
- [ ] Max 63 characters total

**Views / Materialized views**
- [ ] Views prefixed `v_`
- [ ] Materialized views prefixed `mv_`

**Named queries** (sqlc `-- name:`, and any other place a query gets a name)
- [ ] `{Entity}{Action}`, entity first — `UsersList`, `UsersGet`, not
      `ListUsers`, not `GetUser`
- [ ] The entity is the table name, in the same plural the table uses:
      `UsersList`, not `UserList`
- [ ] The action is a verb, and may carry a qualifier: `UsersListActive`,
      `OrdersDeleteForCustomer`
- [ ] A query spanning tables takes the entity it reads from, or writes to
      when it writes

Entity first so every query against one table shares a prefix. That is what
makes them sort together in `queries.sql`, in the generated method set, and in
an editor's autocomplete — `Users` shows the whole surface for that table.
Verb-first scatters them: `ListUsers` sorts under L, next to `ListOrders`,
which is a neighbour it has nothing to do with.

---

### Column ordering

Columns appear in this order within a `CREATE TABLE`:

1. IDs (primary key first, then foreign keys)
2. Strings (text, enums, varchars)
3. Numerics (amounts, counts, measurements)
4. Booleans
5. Dates (date-only fields)
6. Timestamps (datetime fields)

---

### Data types

- [ ] All timestamps use `TIMESTAMPTZ`, never bare `TIMESTAMP`
- [ ] Primary keys are UUID (`gen_random_uuid()`), or SERIAL for internal
      lookup tables
- [ ] Categorical / constrained values use a custom `ENUM` type or a `CHECK`
      constraint — not bare `VARCHAR` with no validation
- [ ] ZIP codes: `VARCHAR(5)` with `CHECK (zip_code ~ '^[0-9]{5}$')`
- [ ] States: `us_state_type` enum
- [ ] Gender: `gender_type` enum
- [ ] Flexible structured data: `JSONB`, not `JSON` or `TEXT`

---

### Constraints and integrity

- [ ] Every table has a primary key
- [ ] Foreign keys are explicitly declared (or noted as analytics schema
      where omitted intentionally)
- [ ] NOT NULL applied to columns that must always have a value
- [ ] Default values provided where sensible (`DEFAULT NOW()`,
      `DEFAULT false`)
- [ ] JSON columns have a `CHECK (jsonb_typeof(...) = 'object')` constraint
      where structure matters

---

### Audit fields

From Esequiel378/skills, MIT.

More from Esequiel378/skills