When to Use
- Creating new database tables or modifying existing schemas
- Writing SQL migrations (
golang-migrate, Flyway, etc.)
- Designing ER diagrams in Mermaid (
.mmd files)
- Reviewing naming consistency between diagrams and migrations
- Adding indexes, constraints, or foreign keys
Dependencies
Core Principle
One naming convention governs everything: ER diagrams, SQL migrations, sqlc queries, and application code mappings. Zero translation, zero ambiguity. What the diagram says is what the migration creates.
Critical Rules
Databases
| Rule | Convention | Example |
|---|
| Format | snake_case | bastet, bastet_production |
| Environment suffix | Only when multiple DBs on same server | bastet_dev, bastet_staging |
Tables
| Rule | Convention | Example |
|---|
| Format | snake_case | users, pet_sitters |
| Plurality | Always plural | bookings, NOT booking |
| No prefixes | Never tbl_, t_ | users, NOT tbl_users |
| Join tables | Alphabetical order or semantic name | pets_services or pet_sitter_certifications |
Columns
| Rule | Convention | Example |
|---|
| Format | snake_case | first_name, phone_number |
| Primary key | Always id | id UUID PRIMARY KEY |
| Foreign key | {singular_table}_id | user_id, pet_sitter_id |
| Booleans | Prefix is_ or has_ | is_active, has_premium |
| Timestamps | Suffix _at | created_at, updated_at, deleted_at |
| Counts | Suffix _count | booking_count, review_count |
| Monetary | Suffix _cents (store as integer) | price_cents, fee_cents |
Indexes
| Type | Pattern | Example |
|---|
| Regular index | idx_{table}_{columns} | idx_users_email |
| Unique index | uq_{table}_{columns} | uq_users_email |
| Composite | idx_{table}_{col1}_{col2} | idx_bookings_user_id_status |
Constraints
| Type | Pattern | Example |
|---|
| Primary key | pk_{table} | pk_users |
| Foreign key | fk_{source}_{target} | fk_bookings_users |
| Check | ck_{table}_{column} | ck_bookings_status |
| Unique | uq_{table}_{columns} | uq_users_email |
Enum / Status Values
| Rule | Convention | Example |
|---|
| Type name | snake_case | booking_status, payment_status |
| Values | UPPER_SNAKE_CASE | PENDING, IN_PROGRESS, COMPLETED |
ER Diagrams in Mermaid
ER diagrams MUST mirror SQL naming exactly. This means:
| Element | In SQL | In Mermaid ER | Match? |
|---|
| Table name | users | users | Yes |
| Column name | first_name | first_name | Yes |
| Foreign key | user_id | user_id FK | Yes |
| Primary key | id | id PK | Yes |
| Data type | UUID | uuid | Lowercase in Mermaid |
See assets/example-er.mmd for a complete ER diagram following these conventions.
See assets/example-migration.sql for the matching SQL migration.
Anti-Patterns
| Don't | Do | Why |
|---|
UPPER_CASE entity names in ER | snake_case plural matching SQL | Diagram must match implementation |
camelCase columns in ER diagrams | snake_case matching SQL | Zero translation between diagram and migration |
userId as foreign key | user_id | Consistent snake_case |
tbl_users, t_users | users | Prefixes add noise, zero value |
booking (singular table) | bookings (plural) | Table holds a collection |
active (boolean without prefix) | is_active | Prefix makes intent obvious |
price DECIMAL | price_cents INTEGER | Integer cents avoid float rounding |
data, info, details columns | Specific names | Vague names hide meaning |
id INTEGER AUTO_INCREMENT | id UUID | UUIDs are safer for distributed systems |
Commands
# Create a migration with proper naming
migrate create -ext sql -dir migrations -seq create_bookings
# Validate table naming in existing migrations
rg "CREATE TABLE" migrations/ --no-heading
Checklist
