database-conventionslisted

# Database Conventions Design and operational guidelines for relational databases (PostgreSQL primary dialect; MySQL/SQLite notes included where they differ). Apply these when creating schemas, reviewing migrations, or writing queries. --- ## Naming - **Tables:** `snake_case`, plural (`users`, `audit_logs`, `order_items`) - **Columns:** `snake_case`, singular (`user_id`, `created_at`, `is_active`) - **Primary keys:** `id` (serial/UUID) - **Foreign keys:** `<referenced_table_singular>_id` (`user_id`, `order_id`) - **Booleans:** `is_` or `has_` prefix (`is_active`, `has_verified_email`) - **Timestamps:** `created_at`, `updated_at`, `deleted_at` (UTC, with timezone) --- ## Schema design - Every table has a primary key. Prefer UUID (`gen_random_uuid()`) for distributed or externally-exposed IDs; serial/bigserial for internal join tables where IDs never leave the DB. - Always store timestamps with timezone (`TIMESTAMPTZ` in Postgres). - Soft-delete via `deleted_at IS NULL` filter, not `DELETE` — unless you have a clear data retention policy that requires hard deletes. - Avoid `TEXT` for constrained values — use an `ENUM` or a lookup table so the DB enforces the constraint. ```sql -- Good: constrained status field CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled'); CREATE TABLE orders ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL REFERENCES users(id), status order_status NOT NULL DEFAU