SQL naming conventions
Table of Contents
Summary
All PostgreSQL objects generated by ores.sql follow a single naming
skeleton: <base>_<suffix>, where base is the entity's SQL name base
— the model's :tablename: without its trailing _tbl, or, when the
model does not set :tablename:, the conventional
<product>_<component>_<entity_plural> (product is always ores,
component is the domain (iam, refdata, marketdata, …), and
entity_plural is the snake_case plural of the entity). Tables carry
the _tbl suffix; triggers use _insert_trg / _delete_trg; the
soft-delete rule uses _delete_rule (not compatible with hypertables);
notify triggers use _notify_trg; functions mirror their trigger with
_fn; indexes use <entity_plural>_<purpose>_idx; NATS notify
channels are <product>.<component>.<entity_plural>. These patterns
are the canonical source of truth — the codegen SQL templates derive
every object name from them.
PostgreSQL truncates identifiers to 63 bytes, and the truncation keeps
the first 63 characters — so a long base silently collapses every
derived object onto one identical name (the last create or replace
wins; earlier objects vanish). When the composed base plus the longest
suffix the entity emits would exceed 63 characters, the model must set
an explicit shorter :tablename: (dropping the least specific leading
words; the parent scope usually names them). The codegen computes
sql_name_base from :tablename: and applies a truncation safety net
as a backstop, so a suffix always survives.
The pg_notify channel is not an identifier, but PostgreSQL enforces
the same 63-byte limit on channel names at runtime (longer ones raise
channel name too long), so every template derives the channel from
sql_name_base too — never from the full
<product>_<component>_<entity_plural> composition, which can exceed
the limit (e.g. ores.synthetic's process-parameter-value entity, whose
66-char conventional name fits neither an identifier nor a channel).
Detail
Skeleton
<product>_<component>_<entity_plural>_<suffix>
| Placeholder | Value | Example |
|---|---|---|
<product> |
Always ores |
ores |
<component> |
Domain slug (iam, refdata, marketdata, …) |
marketdata |
<entity_plural> |
Snake-case plural of the entity | market_series |
Tables
| Pattern | Example |
|---|---|
<product>_<component>_<entity_plural>_tbl |
ores_marketdata_market_series_tbl |
Triggers
Standard (version-based insert + soft-delete rule):
| Object | Pattern | Example |
|---|---|---|
| Insert trigger | <product>_<component>_<entity_plural>_insert_trg |
ores_marketdata_market_series_insert_trg |
| Insert function | <product>_<component>_<entity_plural>_insert_fn |
ores_marketdata_market_series_insert_fn |
| Delete rule | <product>_<component>_<entity_plural>_delete_rule |
ores_marketdata_market_series_delete_rule |
Bitemporal soft-update (hypertables or entities using #+bitemporal_trigger: soft_update_delete; DELETE RULEs are incompatible with hypertables):
| Object | Pattern | Example |
|---|---|---|
| Insert trigger | <product>_<component>_<entity_plural>_insert_trg |
ores_marketdata_market_observations_insert_trg |
| Insert function | <product>_<component>_<entity_plural>_insert_fn |
ores_marketdata_market_observations_insert_fn |
| Delete trigger | <product>_<component>_<entity_plural>_delete_trg |
ores_marketdata_market_observations_delete_trg |
| Delete function | <product>_<component>_<entity_plural>_delete_fn |
ores_marketdata_market_observations_delete_fn |
Notify trigger (NATS pg_notify, generated separately):
| Object | Pattern | Example |
|---|---|---|
| Notify trigger | <product>_<component>_<entity_plural>_notify_trg |
ores_marketdata_market_series_notify_trg |
| Notify function | <product>_<component>_<entity_plural>_notify_fn |
ores_marketdata_market_series_notify_fn |
All trigger functions use SECURITY DEFINER SET search_path = public, pg_temp.
Indexes
<index_name_prefix> is defined per entity (typically <entity_plural>):
| Purpose | Pattern | Example |
|---|---|---|
| Composite natural key | <prefix>_<nk_columns>_uniq_idx |
market_series_series_type_metric_qualifier_uniq_idx |
| Version uniqueness | <prefix>_version_uniq_idx |
market_series_version_uniq_idx |
| UUID current-row | <prefix>_id_uniq_idx |
market_series_id_uniq_idx |
| Tenant lookup | <prefix>_tenant_idx |
market_series_tenant_idx |
| Workspace lookup | <prefix>_workspace_idx |
market_series_workspace_idx |
| Custom (from model) | <prefix>_<name>_idx |
market_observations_observations_current_uniq_idx |
Validation functions
Entities that expose a validation helper (soft-FK target) follow:
| Pattern | Example |
|---|---|
<product>_<component>_validate_<entity_singular>_fn |
ores_refdata_validate_asset_class_fn |
NATS notify channels
| Pattern | Example |
|---|---|
<product>.<component>.<entity_plural> |
ores.marketdata.market_series |
Database roles
Service roles follow:
| Pattern | Example |
|---|---|
ores_<env>_<service>_service |
ores_local1_marketdata_service |
See also
- ores.sql.schema — the facet that groups all schema templates.
- ores.sql — component overview; schema upgrade policy and lifecycle scripts.