SQL facet

Table of Contents

Every ORE Studio SQL entity lives in the public schema with the ores_{component}_ prefix, ends with a type suffix (_tbl, _idx, _fn, …), and is created via codegen from a JSON model. All DDL scripts must be idempotent: use IF NOT EXISTS / IF EXISTS consistently so re-runs produce no NOTICE output. The naming convention ensures that searching for a table name also finds all its associated functions and indexes. Return to Knowledge.

Component prefixes

Full prefix C++ component Description
ores_dq_ ores.dq Data quality, governance, staging
ores_iam_ ores.iam Identity and Access Management
ores_refdata_ ores.refdata Reference data (currencies, countries, etc.)
ores_assets_ ores.assets Digital assets (images, tags)
ores_variability_ ores.variability Feature flags and configuration
ores_telemetry_ ores.telemetry Telemetry and logging
ores_geo_ ores.geo Geolocation services
ores_utility_ (shared) Shared utility functions
ores_seed_ (shared) Validation helpers for population

Entity suffixes

Entity names use the plural form in table names (ores_iam_accounts_tbl, not ores_iam_account_tbl).

Object type Suffix Example
Table _tbl ores_iam_accounts_tbl
View _vw ores_telemetry_stats_daily_vw
Insert trigger _insert_trg ores_iam_accounts_insert_trg
Notify trigger _notify_trg ores_iam_accounts_notify_trg
Insert function _insert_fn ores_iam_accounts_insert_fn()
Notify function _notify_fn ores_iam_accounts_notify_fn()
Upsert function _upsert_fn ores_dq_catalogs_upsert_fn()
Publish function _publish_fn ores_dq_currencies_publish_fn()
Preview function _preview_fn (singular) ores_dq_currency_preview_fn()
Assign function _assign_fn ores_iam_role_permissions_assign_fn()
Other functions _fn ores_utility_infinity_timestamp_fn()
Delete rule _delete_rule ores_iam_accounts_delete_rule
Regular index _idx ores_iam_accounts_username_idx
Unique index _uniq_idx ores_iam_accounts_username_uniq_idx
GiST index _gist_idx ores_iam_accounts_validity_gist_idx
RLS policy _policy ores_iam_accounts_tenant_isolation_policy

The key principle: function names preserve the table name for grep-ability. The table ores_dq_catalogs_tbl has upsert function ores_dq_catalogs_upsert_fn, so grep ores_dq_catalogs finds both.

Full naming patterns

Entity Pattern
Table ores_{component}_{entities}_tbl (plural)
View ores_{component}_{entities}_{qualifier}_vw
Insert trigger ores_{component}_{entities}_insert_trg
Notify trigger ores_{component}_{entities}_notify_trg
Column index ores_{component}_{entities}_{column(s)}_idx
Unique index ores_{component}_{entities}_{column(s)}_uniq_idx
Tenant RLS policy ores_{component}_{entities}_tenant_isolation_policy
Schema file ores_{component}_{entity}_create.sql (singular)
Notify file ores_{component}_{entity}_notify_trigger.sql
Drop file ores_{component}_{entity}_drop.sql
Populate file ores_{component}_{entity}_populate.sql
RLS create file {component}_rls_policies_create.sql
RLS drop file {component}_rls_policies_drop.sql

Row-Level Security

Every entity table with a tenant_id column must have a tenant isolation policy. Entities that also carry a party_id column must additionally have a party isolation policy. Missing RLS is a silent gap — the service layer's WHERE tenant_id = ... clause provides equivalent filtering today, but RLS is the defence-in-depth layer that prevents cross-tenant leaks if a query is ever written without that clause.

Tenant isolation (permissive, FOR ALL)

alter table ores_{component}_{entities}_tbl enable row level security;

create policy ores_{component}_{entities}_tenant_isolation_policy
on ores_{component}_{entities}_tbl
for all using (
    tenant_id = ores_iam_current_tenant_id_fn()
)
with check (
    tenant_id = ores_iam_current_tenant_id_fn()
);

Party isolation (restrictive, FOR SELECT)

Added when the entity also has a party_id column — enforces that reads are scoped to parties visible in the current session. AS RESTRICTIVE means this policy intersects with the tenant policy rather than extending it. Applied to SELECT only: write-path party validation is the responsibility of the insert trigger. 

create policy ores_{component}_{entities}_party_isolation_policy
on ores_{component}_{entities}_tbl
as restrictive
for select using (
    party_id = ANY(ores_iam_visible_party_ids_fn())
);

If the entity has a sentinel value that must be readable by all parties in the tenant (analogous to the Live workspace), add an explicit exemption to the USING clause rather than widening the policy unconditionally.

File placement and registration

Place policies in projects/ores.sql/create/{component}/{component}_rls_policies_create.sql. If the component already has an RLS file, append to it. If not, create it and add a \ir line to projects/ores.sql/create/rls/rls_create.sql.

See PostgreSQL Row-Level Security for background on the PostgreSQL feature.

Idempotency patterns

All create/drop scripts must be safe to re-run without NOTICE output. The mustache templates enforce these rules automatically — read the template directly for the authoritative implementation.

Drop functions without parameter signatures

Omit the parameter signature in DROP FUNCTION IF EXISTS so all overloads are dropped silently and no NOTICE is emitted when the function is absent.

Unique constraints via index, not ALTER TABLE

Create unique constraints as CREATE UNIQUE INDEX IF NOT EXISTS, not via ALTER TABLE ADD CONSTRAINT / DROP CONSTRAINT. The ALTER TABLE pattern emits a NOTICE on re-runs; the index form is fully idempotent.

Tables and indexes

Use CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS. Drop with DROP TABLE IF EXISTS (indexes cascade automatically).

Templates

Generated by --profile sql. Read the template for the authoritative implementation — do not reproduce it here.

Template Output path
sql_schema_domain_entity_create.mustache projects/ores.sql/create/{component}/{component}_{entity_plural}_create.sql
sql_schema_notify_trigger.mustache projects/ores.sql/create/{component}/{component}_{entity_plural}_notify_trigger_create.sql
sql_schema_domain_entity_drop.mustache projects/ores.sql/drop/{component}/{component}_{entity_plural}_drop.sql
sql_schema_notify_trigger_drop.mustache projects/ores.sql/drop/{component}/{component}_{entity_plural}_notify_trigger_drop.sql

See also

Emacs 29.1 (Org mode 9.6.6)