Skip to content

Tables, columns & views

Tables

Singular form. A table represents one row's worth of concept: user_info, tenant, permission, user_group_member. Plural is reserved for views.

No tbl_ / t_ prefix. The schema name (auth.user_info) already disambiguates from functions.

Reserved-word collisions are resolved with a suffix, not abbreviations. user is reserved, so the table is auth.user_info, not auth.usr or auth.user_. The columns inside it are still user_id, username, email — only the table name carries the suffix.

Composite-concept tables use the hierarchy rule from the general guidelines. Primary noun first, secondary second:

  • auth.user_group_member — primary user_group, secondary member
  • auth.permission_assignment — primary permission, secondary assignment
  • auth.user_group_mapping — primary user_group, secondary mapping

The function operating on a secondary object names both unless the provider context makes it unambiguous: auth.create_user_group_member(...), not create_member(...).

Columns

Identifiers

  • Primary key column: <table-singular>_id, e.g. user_id in auth.user_info, tenant_id in auth.tenant. No bare id.
  • Type: bigint generated by default as identity for high-volume / app-facing rows (users, journal); integer generated always as identity for low-volume reference rows (tenants, permissions, const tables).
  • generated always blocks application-side inserts of an id value; generated by default allows them (necessary when seeding fixed IDs). Pick the stricter option unless you need otherwise.
  • Foreign-key column name equals the target's PK column name: user_id, tenant_id, provider_code. When a table has multiple FKs to the same target, prefix with the role: created_by_user_id, assigned_user_id.
  • Codes as identifiers: const.* lookups use code text primary key, and referencing columns are <concept>_code — user_type_code, token_type_code, provider_code.

Audit columns (universal — every table)

created_at timestamp with time zone default now()           not null,
created_by text                     default 'unknown'::text not null,
updated_at timestamp with time zone default now()           not null,
updated_by text                     default 'unknown'::text not null,
  • Always timestamp with time zone (timestamptz). Never naked timestamp.
  • created_by / updated_by are strings (username / system actor), not user-id FKs. This survives user deletion, allows 'system' / 'trigger' / 'unknown' sentinels, and keeps the audit trail readable.
  • 'unknown' default exists for backfills and trigger inserts that genuinely don't know the actor; production paths should always pass a real value.
  • A few link tables omit updated_by / updated_at because they have no mutable columns (tenant_user, permission_assignment, perm_set_perm, user_group_member, user_group_mapping). That is fine — every mutation deletes-and-reinserts the link.

Booleans — is_, has_, allows_, should_

Prefix Meaning Example
is_ Current state is_active, is_deleted, is_locked, is_verified, is_system
has_ Possession has_mfa, has_password
allows_ Configuration permission allows_group_sync, allows_group_mapping, allows_self_registration
should_ Policy / preference should_send_welcome_email

Do not name a boolean active, deleted, locked — the prefix carries information about how to read it.

Exception, for parity with HTML / external systems: if a column reflects a flag from an external system (LDAP, OAuth provider), keep the external name unprefixed (enabled, verified) so the mapping is obvious. This is the SQL equivalent of the JavaScript data-model-booleans rule — domain shapes follow the source, not the convention.

Normalized / computed columns — nrm_ prefix

When the same data needs a searchable (lowercased, accent-stripped) form, store it as a generated column with a nrm_ prefix:

nrm_username    text generated always as (lower(username)) stored not null,
nrm_search_data text,  -- updated by trigger from helpers.normalize_text(...)

nrm_search_data is typically too complex for generated always; it's filled by a before insert or update trigger calling a triggers.calculate_<table>_search_values(...) helper.

Index those columns with ix_trgm_<table>_search using gist (nrm_search_data gist_trgm_ops) for substring matching, or with ix_<table>_<col> for equality.

JSONB columns

Bare nouns: settings, preferences, custom_data, data_payload, keys, request_context. No _json or _jsonb suffix — the column type already says that.

For partial-merge semantics (pass only the keys you change; pass null to remove a key), document the behavior in the function that updates the column, not in the column name.

Views

Plural form — a view is "many rows of the concept": auth.active_user_groups, auth.user_group_members, auth.effective_permissions.

No v_ / view_ prefix.

Column aliases in views are bare snake_case (no __ prefix; __ is reserved for function return columns and locals).

Views used by notification triggers to resolve "who cares about this change" follow notify_<source>_<target> (e.g. auth.notify_group_users returns the user_ids affected by a group-permission change).