PostgreSQL database schema design with strict naming conventions. Use when creating database schemas, tables, columns, or writing SQL for PostgreSQL. Enforces: (1) Snake_case for all table and column names, (2) Project prefix for all tables, (3) No reserved keywords, (4) PostgreSQL best practices. Includes schema validation and automated name correction.
Design PostgreSQL schemas with consistent, safe naming conventions.
All table and column names use snake_case (lowercase with underscores).
Bad:
userNameUser_NameUSERNAMEUserNameGood:
user_nameemail_addresscreated_atEvery table name starts with the project short name prefix.
Example with prefix app:
app_usersapp_user_profilesapp_login_sessionsNever use PostgreSQL reserved keywords as table or column names.
Common reserved keywords to avoid:
user, order, group, select, from, where, table, index, columndate, time, timestamp, intervalcomment, constraint, primary, foreign, keycheck, default, null, not, and, or, inInstead of:
user → app_users, app_user, usrorder → app_orders, purchase_orderdate → created_date, start_date, event_dateUse prefixes/suffixes for clarity:
| Purpose | Pattern | Examples |
|---|---|---|
| Foreign keys | {referenced_table}_id | user_id, organization_id |
| Timestamps | {action}_at | created_at, updated_at, deleted_at |
| Booleans | is_, has_, can_ | is_active, has_permission, can_edit |
| Counts | num_{entity} or {entity}_count | num_items, comment_count |
| URLs | {entity}_url | avatar_url, website_url |
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
);
Use the bundled script to validate schemas:
python3 scripts/schema_validator.py '<json_schema>' [project_prefix]
{
"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)"}
]
}
}
}
Always include these columns on every table:
created_at - When the record was createdupdated_at - When the record was last modifiedcreated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
Instead of deleting records, use soft deletes:
deleted_at TIMESTAMP DEFAULT NULL
Name foreign keys after the referenced table:
user_id UUID REFERENCES app_users(id),
organization_id UUID REFERENCES app_organizations(id)
For many-to-many relationships, name with both tables:
app_user_roles (user_id, role_id)
app_post_tags (post_id, tag_id)
Never use these as table or column names:
Common Traps:
user → use users, app_users, usrorder → use orders, purchase_ordersgroup → use groups, user_groupstable → use tables, data_tablescolumn → use columns, table_columnsindex → use indexes, search_indexkey → use keys, api_keyvalue → use values, setting_valuedate → use created_date, start_datetime → use created_time, start_timecomment → use comments, post_commentconstraint → use constraints, rule_constraint{table}_id{action}_atis_, has_, can_ prefixcreated_at and updated_at| Data Type | PostgreSQL Type | Example |
|---|---|---|
| Primary Key | UUID or BIGINT | id UUID PRIMARY KEY |
| Foreign Key | UUID or BIGINT | user_id UUID |
VARCHAR(255) | email VARCHAR(255) | |
| URLs | TEXT | avatar_url TEXT |
| JSON | JSONB | metadata JSONB |
| Money | NUMERIC(10,2) | price NUMERIC(10,2) |
| Timestamps | TIMESTAMP | created_at TIMESTAMP |
| Enumerations | TEXT or custom ENUM | status TEXT |
| Booleans | BOOLEAN | is_active BOOLEAN |