PostgreSQL: Database Architecture and Conventions

One row per pairing the corpus actually uses. A column outside this table is not forbidden, but it should be added here before it spreads.

A nullable column takes the optional of its C++ type, and only a nullable column does. An optional against a not null column claims an absence the database will never produce.

PostgreSQL accepts aliases; the corpus should not. Two are currently spelled both ways, and both should converge on the long form the table above gives:

• integer against int, 87 columns to 7.
• timestamp with time zone against timestamptz, 15 to 5.

The cost of two spellings is not aesthetic. Anything that groups or audits columns by type sees two populations where there is one, which is how the boolean inconsistency below went unnoticed.

A flag is boolean, not an integer. The schema has 42 boolean columns and a handful of integer flags that predate the rule; the integer ones are the exception being worked off, not a second valid style.

The reason to care is that the two do not read alike. Through the text protocol a boolean arrives as t or f, so code that parses it with std::stoi throws the moment the column is what it should have been, and a predicate written where is_active = 1 stops matching. Both were live in ores.scheduler until its column was corrected.

Read one through text_to_bool, which takes the optional a row holds and accepts t, true and 1, so a caller need not know whether its query casts. It exists because the same read had been spelled three ways: a bare t comparison, a three-way comparison, and std::stoi. A null column is false — a boolean that is absent is not true.

Model a flag as :type: boolean with :cpp_type: bool, and the same bool reaches the repository entity, so nothing converts at the mapper.

A financial quantity is numeric(28, 10): notionals, prices, rates, barriers, face values — 55 columns. numeric is chosen because it is exact, which is the whole reason not to store money in binary floating point.

The C++ side then declares double, which gives that guarantee back. A double holds about 15–17 significant decimal digits against numeric(28, 10)'s 28, and cannot represent most decimal fractions exactly, so a value can round on the way through C++ and be written back changed.

This is recorded rather than resolved. Nothing here relies on exact decimal arithmetic in C++ today — the values are carried and displayed rather than accumulated — but the pairing is a known weakness rather than a considered design, and anything that starts summing these in C++ should fix the representation first.

A calendar date is date paired with std::chrono::year_month_day.

Most of the corpus does not do this yet: 49 columns pair date with std::string against 4 with the typed form. A date held as a string is a date nothing validates, and every consumer reparses it or compares it lexically and hopes the format never changes.

New columns take the typed form. Converting the existing ones is worth doing and is not free, since each one's consumers parse the string today.

A tenant column is declared through has_tenant_id rather than as an ordinary uuid column, which is what gives it the utility::uuid::tenant_id wrapper instead of a bare UUID. The wrapper exists so a default-constructed nil UUID cannot be mistaken for the system tenant, which is the max UUID rather than a nil one.

nullable_tenant_id makes the column nullable in SQL. It does not currently make the C++ type optional, so a nullable tenant column and its domain type disagree; that is a known defect with its own task rather than a convention to copy.

Every service holds a small per-service connection pool (default 2 connections, tuned per service via --db-pool-size / ORES_<APP>_DB_POOL_SIZE). When every connection is in use, an acquire waits and retries instead of failing immediately:

• num_attempts (default 10) bounds the retries. After the last attempt the acquire fails with "No available connections in the pool."
• Between attempts the waiter sleeps. The default strategy is exponential: the base wait_time_in_seconds (default 1) doubles per attempt, capped at 10 seconds, plus a small deterministic per-thread jitter so waiting threads do not re-probe in lockstep. The linear strategy waits the fixed interval per attempt — the historical behavior, available for environments that need it.
• The strategy is chosen per service: --db-pool-backoff <exponential|linear> / ORES_<APP>_DB_POOL_BACKOFF, default exponential.

The wait policy lives in common code (ores.database, tenant_aware_pool::acquire()); the underlying sqlgen pool is configured to fail fast, and the project-side loop owns the backoff. Writes therefore serialize naturally on the pool under contention: waiters queue politely and each eventually acquires, rather than one failing while others proceed.

Extends GiST indexes to support btree-indexable data types (integers, timestamps, text). ORE Studio uses it for temporal exclusion constraints: a GiST index over (id, valid_from, valid_to) prevents two rows for the same entity from having overlapping valid-time ranges. Without btree_gist, the exclusion constraint on bitemporal tables cannot be created.

create extension if not exists btree_gist;

Provides a text-search dictionary that strips accents from characters (e.g. é → e, ñ → n). Used by the ores_utility_normalise_name_fn() helper to produce a lowercase, accent-free version of party and counterparty names for deduplication and search. Without unaccent, name normalisation falls back to a plain lower() which misses accented characters.

create extension if not exists unaccent;

A TAP-compliant unit-testing framework for SQL. Provides plan(), is(), ok(), throws_ok(), and similar functions for writing database-level tests under ores.sql/test/. If not installed, the SQL test suite cannot run; application functionality is unaffected.

Install on Debian/Ubuntu: apt install postgresql-NN-pgtap.

create extension if not exists pgtap;

A time-series database extension. ORE Studio uses it for the telemetry tables (ores_telemetry_*): hypertables partition telemetry logs by time, enabling efficient range queries and compression. If TimescaleDB is not installed, the telemetry tables fall back to regular PostgreSQL tables — all functionality works but performance at scale degrades.

TimescaleDB requires a system-level installation (apt install timescaledb-2-postgresql-NN) and shared_preload_libraries = 'timescaledb' in postgresql.conf before the extension can be created.

create extension if not exists timescaledb;

Full-CRUD entities with their own MDI windows, shell/CLI commands, and manual chapters (currency, country, party, book, …).

Trigger structure (order is mandatory):

1. tenant_id — ores_iam_validate_tenant_fn(new.tenant_id)
2. Optional soft-FK fields (coding_scheme_code inline; named attributes via ores_refdata_validate_*_fn / similar)
3. change_reason_code — ores_dq_validate_change_reason_fn(...)
4. Version management block (SELECT … FOR UPDATE, optimistic lock check, UPDATE … SET valid_to, version increment)
5. valid_from / valid_to set by trigger
6. modified_by — ores_iam_validate_account_username_fn(new.modified_by)
7. performed_by — coalesce(ores_iam_current_service_fn(), current_user)

Rule: all validation (steps 1–3) must precede the version management block (step 4). This ensures the trigger fails fast — before acquiring the row lock and before closing the previous temporal record — on any bad input.

Domain entity triggers should declare security definer and set search_path = public, pg_temp to prevent search-path injection.

Records that are data owned by a parent entity and carry their own temporal history: party_identifiers, party_contact_informations, business_units, …

Trigger structure follows Category 1, with these additions:

• Parent UUID FK (e.g. party_id) validated inline with if not exists (select 1 from ores_refdata_parties_tbl where id = NEW.party_id …). No separate validate function exists for UUID-keyed parent references; the inline check is the established pattern.
• Type/scheme attributes (e.g. id_scheme) validated via ores_refdata_validate_party_id_scheme_fn(...).

Sub-entities do not have an independent validate function (there is no ores_refdata_validate_party_identifier_fn); they are not referenced as soft-FKs by other entities.

Tables that control which entities a party can see, not data about the party itself: party_currencies, party_countries, party_counterparty.

These carry version management and audit columns but their entity references (currency_iso_code, country_alpha2_code, party_id, …) are not validated in the trigger. The validated fields are:

• tenant_id — ores_iam_validate_tenant_fn(...)
• modified_by, performed_by — same as Categories 1 & 2
• change_reason_code — ores_dq_validate_change_reason_fn(...)

Why no entity FK validation? Junction rows represent an access-control decision ("party X may see country Y"), not a business datum. An orphan junction row — pointing to a country that does not (yet) exist — is harmless: the join produces no rows and the UI shows nothing. Contrast this with a book.functional_currency field: if the currency disappears, the book's P&L calculation breaks. The semantic difference justifies the asymmetry.

In practice these junctions are populated by trusted internal operations (the provisioner, bulk seeders) where the application layer already guarantees entity existence.

Each refdata entity that may appear as a soft-FK attribute on another entity must define a validation function:

create or replace function ores_refdata_validate_<entity>_fn(
    p_tenant_id uuid,
    p_value     text
) returns text
security definer
set search_path = public, pg_temp
as $$
-- Raises 23502 if null/empty.
-- Returns p_value unchanged if no active rows exist yet (bootstrap pass-through).
-- Raises 23503 with a helpful list if the value is not found among active rows.
$$ language plpgsql;

These functions are:

• Called from Category 1 and 2 triggers, never from Category 3.
• Defined in the same DDL file as their entity's table (at the bottom, after the soft-delete rule).
• Dropped in the entity's drop/ file alongside the table and insert function.
• Declared security definer + set search_path = public, pg_temp unconditionally — see below.

Security: validate functions must carry security definer and set search_path = public, pg_temp independently of their callers. security definer prevents the function from executing as the calling service role (which may have a manipulated search path). set search_path is required alongside it to pin table resolution to public and pg_temp; security definer alone, without a pinned path, is still vulnerable to search-path injection. security definer does not propagate to callees — each function must declare it explicitly.

Bootstrap pass-through: the pass-through check must test for active rows (valid_to = ores_utility_infinity_timestamp_fn()), not any rows. A table with only soft-deleted historical rows is semantically empty for validation purposes; checking without the valid_to filter would skip the pass-through, then fail the active-row validation with a misleading "Must be one of: (empty list)" error. The limit 1 inside EXISTS is a no-op and is omitted.

Every pg_notify channel follows the rule:

ores_{component}_{entity_plural}

where component is the domain sub-prefix (iam, refdata, trading, analytics, compute, dq, reporting, variability, workspace, scheduler, mq, …) and entity_plural is the snake_case plural name of the table without its ores_{component}_ table prefix.

The component prefix is required. Without it, the same entity noun in two domains (e.g. parties in refdata and a hypothetical parties in iam) would share a single channel, causing incorrect cross-domain event delivery.

The codegen template sql_schema_notify_trigger.mustache generates conforming channel names automatically. Handwritten triggers must follow the same rule.

The application layer re-publishes each database event onto a NATS subject visible to shell and UI clients. The naming convention there is distinct from the PostgreSQL layer:

ores.{component}.{entity}_changed

The service registers the mapping explicitly; the two names are not derived from each other automatically.

ores.database.service.postgres_listener_service runs a dedicated connection and thread per service, polling every 100ms (consume_input() + get_notifications()) and invoking the notification callback for each event. Callbacks run outside the poll's mutex lock: the lock is held only long enough to drain pending notifications into a local batch, then released before the batch is processed. Under a heavy bulk import (e.g. the GLEIF party dataset — hundreds of INSERTs each firing a NOTIFY trigger), holding the lock across callback processing would block subscribe()=/=notify()=/=stop() for as long as the batch takes, and — because the same lock guards the listener's own next poll iteration — starve it, making the listener appear to have died under load. Reconnect backoff caps at 5s (not 30s): a local PostgreSQL reconnects in well under a second, so a longer ceiling only makes outages look worse than they are.