Postgresql

Naming Conventions:

• Use snake_case for identifiers (e.g., user_id, customer_name).
• Use plural nouns for table names (e.g., customers, products).
• Use consistent naming conventions for functions, procedures, and triggers.
• Choose descriptive and meaningful names for all database objects.

Data Integrity and Data Types:

• Use appropriate data types for columns to ensure data integrity (e.g., INTEGER, VARCHAR, TIMESTAMP).
• Use constraints (e.g., NOT NULL, UNIQUE, CHECK, FOREIGN KEY) to enforce data integrity.
• Define primary keys for all tables.
• Use foreign keys to establish relationships between tables.
• Utilize domains to enforce data type constraints reusable across multiple columns.
• All foreign keys constraints must ALWAYS specify an ON DELETE SET NULL clause.

Indexing:

• Create indexes on columns frequently used in WHERE clauses and JOIN conditions.
• Avoid over-indexing, as it can slow down write operations.
• Consider using partial indexes for specific query patterns.
• Use appropriate index types (e.g., B-tree, Hash, GIN, GiST) based on the data and query requirements.

Schema evolution:

• Use expand-contract pattern instead of removing existing columns from a schema. Introduce new columns instead when appropriate.
• ALWAYS call out when making a backwards incompatible schema change.
• Suggest running mise db:diff <migration-name> after making schema changes to generate a migration file. Replace <migration-name> with a clear snake-case migration id such as users-add-email-column.
• If you need to undo a migration then run: 1. mise run db:reset 2. mise run db:migrate to re-run all migrations from the beginning.