db-naming-conventions

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

Skill	Purpose	Path
go-repository-pattern	Migration files, sqlc queries follow these naming rules	../go-repository-pattern/SKILL.md
mermaid-diagrams	ER diagrams must use the same naming as SQL	../mermaid-diagrams/SKILL.md

---

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

• Table names are snake_case and plural
• Columns are snake_case and singular
• Primary key is id (UUID)
• Foreign keys follow {singular_table}_id pattern
• Booleans have is_ or has_ prefix
• Timestamps end in _at
• Indexes follow idx_{table}_{columns} pattern
• Constraints follow {type}_{table}_{detail} pattern
• ER diagram entity names match SQL table names exactly
• ER diagram column names match SQL column names exactly
• Monetary values stored as integer cents with _cents suffix