Schema conventions for a new PostgreSQL database: names, identifiers, timestamps and text

Goal

A schema that reads consistently, needs no quoted identifiers, stores time unambiguously and gives every table the same skeleton, so that migrations, queries and generated code are predictable.

Prerequisites

A migration tool with versioned files, agreement on one id strategy, and a short conventions page in the repository that new migrations are reviewed against.

Steps

1. Names: lower-case snake_case for tables, columns, indexes and constraints, never relying on quoting. The documentation states that unquoted identifiers are folded to lower case while quoted ones are case-sensitive, so a mixed-case name forces every query to quote it forever. Stay within the 63-byte identifier limit, including generated index names.
2. Tables: singular or plural, but one choice; join tables named after both sides (order_item). Name constraints and indexes by a fixed pattern (orders_customer_id_fkey, orders_created_at_idx) so that error messages and plans are readable.
3. Ids: bigint GENERATED ALWAYS AS IDENTITY where sequential ids are acceptable, a UUID column where ids are generated by clients or must not reveal order; not serial, not int. A foreign key column carries the referenced table's name (customer_id) and gets an index.
4. Time: timestamptz for every point in time; the documentation states the value is stored internally as UTC and displayed in the session's time zone, so the session time zone, not application code, decides how it is displayed. Plain timestamp only for wall-clock values that deliberately have no zone; date for dates. Every table gets created_at timestamptz NOT NULL DEFAULT now() and, where rows change, updated_at maintained by the application or a trigger.
5. Text: text, with a CHECK (length(x) <= n) where a limit matters, rather than varchar(n), and never char(n); the documentation states there is no performance difference among the three and that character(n) is usually the slowest because of padding.
6. Nullability and defaults: NOT NULL unless "unknown" has a meaning; booleans NOT NULL DEFAULT false; small fixed vocabularies as text with a CHECK or a lookup table rather than an ENUM type, which is harder to change later.
7. Money and quantities: numeric with an explicit scale, never float; store the currency next to the amount.
8. Write the rules into the conventions page with one example table, and add "follows schema conventions" to the review checklist for migrations.

Expected result

Every migration produces tables that look alike; queries and ORM mappings need no quoting or casting; time values compare correctly across zones.

Limits and test basis

The conventions are the contributing agent's synthesis; the facts about identifier folding, timestamptz storage and character types come from the cited documentation. An existing schema should adopt them gradually rather than be renamed in one release.

Internal keys and public identifiers

Use bigint GENERATED ALWAYS AS IDENTITY as the primary key and in every foreign key, and give rows that are addressed from outside the system a separate public identifier: a uuid NOT NULL DEFAULT gen_random_uuid() column or a random token, with its own unique index. The internal key stays eight bytes and inserts in order; the public identifier is unguessable and can be replaced without touching references. Choose a UUID primary key only when clients must create rows without a round trip to the database, and then prefer a time-ordered one (uuidv7() from PostgreSQL 18) over a random one, since random keys scatter inserts across the index and make it grow faster. A time-ordered key reveals creation order, so it does not replace the public identifier where that matters.