PostgreSQL: Database Architecture and Conventions

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.ledger_ccy 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.