PostgreSQL Database Design

Design PostgreSQL schemas with consistent, safe naming conventions.

Core Naming Conventions

1. Snake_case for Everything

All table and column names use snake_case (lowercase with underscores).

Bad:

• •userName
• •User_Name
• •USERNAME
• •UserName

Good:

• •user_name
• •email_address
• •created_at

2. Project Prefix for Tables

Every table name starts with the project short name prefix.

Example with prefix app:

• •app_users
• •app_user_profiles
• •app_login_sessions

3. Avoid Reserved Keywords

Never use PostgreSQL reserved keywords as table or column names.

Common reserved keywords to avoid:

• •user, order, group, select, from, where, table, index, column
• •date, time, timestamp, interval
• •comment, constraint, primary, foreign, key
• •check, default, null, not, and, or, in

Instead of:

• •user → app_users, app_user, usr
• •order → app_orders, purchase_order
• •date → created_date, start_date, event_date

4. Name Prefixes and Suffixes

Use prefixes/suffixes for clarity:

Column Naming Examples

CREATE TABLE app_users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE,
    last_login_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE app_user_profiles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    avatar_url TEXT,
    bio TEXT,
    date_of_birth DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Schema Validator

Use the bundled script to validate schemas:

python3 scripts/schema_validator.py '<json_schema>' [project_prefix]

Example Usage

{
  "tables": {
    "app_users": {
      "columns": [
        {"name": "id", "type": "UUID", "constraints": "PRIMARY KEY"},
        {"name": "email", "type": "VARCHAR(255)", "constraints": "NOT NULL UNIQUE"},
        {"name": "created_at", "type": "TIMESTAMP", "constraints": "DEFAULT CURRENT_TIMESTAMP"}
      ]
    },
    "user_profiles": {
      "columns": [
        {"name": "user_id", "type": "UUID", "constraints": "REFERENCES app_users(id)"}
      ]
    }
  }
}

Common Patterns

Timestamps

Always include these columns on every table:

• •created_at - When the record was created
• •updated_at - When the record was last modified

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Soft Deletes

Instead of deleting records, use soft deletes:

deleted_at TIMESTAMP DEFAULT NULL

Foreign Keys

Name foreign keys after the referenced table:

user_id UUID REFERENCES app_users(id),
organization_id UUID REFERENCES app_organizations(id)

Junction Tables

For many-to-many relationships, name with both tables:

app_user_roles (user_id, role_id)
app_post_tags (post_id, tag_id)

Reserved Keywords Quick Reference

Never use these as table or column names:

Common Traps:

• •user → use users, app_users, usr
• •order → use orders, purchase_orders
• •group → use groups, user_groups
• •table → use tables, data_tables
• •column → use columns, table_columns
• •index → use indexes, search_index
• •key → use keys, api_key
• •value → use values, setting_value
• •date → use created_date, start_date
• •time → use created_time, start_time
• •comment → use comments, post_comment
• •constraint → use constraints, rule_constraint

Schema Design Checklist

• • All tables use project prefix
• • All names use snake_case
• • No reserved keywords used
• • Foreign keys named {table}_id
• • Timestamps named {action}_at
• • Booleans use is_, has_, can_ prefix
• • No double underscores in names
• • No trailing underscores
• • Names start with letters (not numbers)
• • All tables have created_at and updated_at

Data Type Best Practices