Postgresql Db

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