Database Lifecycle and Script Reference

Operational reference for database creation, population, and teardown in ORE Studio. For schema design patterns and table conventions, see ORE Studio SQL Schema. For step-by-step table creation instructions, see the SQL Schema Creator skill.

Quick Reference

The most common development operations:

Task	Command
Recreate everything	`./recreate_database.sh` (with args)
Recreate one environment	`./recreate_env.sh -e local1`
Validate schema consistency	`./utility/validate_schemas.sh`
Recreate a single entity	`./utility/recreate_entity.sh NAME`
Run tests	`./test/run_tests.sh`

All scripts are in projects/ores.sql/.

Directory Layout

projects/ores.sql/
  create/                   Schema creation (tables, triggers, functions)
    create.sql                Master orchestrator
    utility/                  Shared utility functions
    dq/                       Data governance tables
    refdata/                  Reference data tables (currencies, parties, ...)
    iam/                      Identity and access management
    variability/              Feature flags
    telemetry/                Monitoring and logging
    assets/                   Image and tag storage
    geo/                      IP geolocation
    rls/                      Row-level security policies
    seed/                     Seed/population functions

  drop/                     Schema teardown (mirrors create/ in reverse)
    drop.sql                  Master orchestrator

  populate/                 Data population scripts
    populate.sql              Master orchestrator (governance + catalogues)
    foundation/               Foundation layer (in template, run first)
    governance/               Governance layer (dimensions, methodologies)
    catalogues/               Catalogue layer (orchestrates all data sources)
    dq/                       DQ metadata population
    refdata/                  Refdata lookup population
    iam/                      IAM initial data
    variability/              Feature flag defaults
    iso/                      ISO 3166/4217 data
    flags/                    Country flag icons
    solvaris/                 Extended currency metadata
    fpml/                     Financial products reference data
    crypto/                   Cryptocurrency data
    lei/                      GLEIF LEI legal entities
    ip2country/               IP geolocation mappings

  test/                     pgTAP tests
  utility/                  Helper scripts (validation, entity recreation)
  instance/                 Instance-specific initialisation
  modeling/                 Documentation and diagrams (you are here)

Schema Creation Order

create/create.sql orchestrates schema creation with strict dependency ordering:

1. Utility Functions            create/utility/utility_create.sql
   (infinity timestamp, system tenant ID, validation helpers)

2. Data Governance Tables       create/dq/dq_create.sql
   (change reasons, coding schemes, dimensions, artefact tables)

3. Operational Tables           create/refdata/refdata_create.sql
   (in parallel)                create/iam/iam_create.sql
                                create/variability/variability_create.sql
                                create/telemetry/telemetry_create.sql
                                create/assets/assets_create.sql
                                create/geo/geo_create.sql

4. Row-Level Security           create/rls/rls_create.sql
   (depends on all tables + IAM functions)

5. Seed Functions               create/seed/seed_create.sql
   (data publication functions)

6. Summary Functions            create/utility/utility_summary_functions_create.sql

Refdata Creation Order

create/refdata/refdata_create.sql creates tables in dependency order:

1. Lookup tables (static)       rounding_types
2. Core tables                  currencies, countries
3. FPML tables                  account_types, asset_classes, benchmark_rates, ...
4. Party lookup tables          party_categories, party_types, party_statuses,
                                party_id_schemes, contact_types
5. Party/counterparty entities  parties, counterparties
6. Child entities               party_identifiers, counterparty_identifiers,
                                party_contact_informations,
                                counterparty_contact_informations

Each table has a matching *_notify_trigger_create.sql for change notifications.

Drop Order

drop/drop.sql reverses the creation order:

1. Operational tables (refdata, iam, variability, telemetry, assets, geo)
2. Data governance tables (dq)
3. Seed functions, utility functions

Within each component, child tables are dropped before parents.

Population Layers

Data is populated in layers, each building on the previous. The foundation layer runs first during setup_schema.sql, followed by governance and catalogues.

Foundation Layer

Script: populate/foundation/foundation_populate.sql

Populated during setup_schema.sql. Must be present before any application data.

Order    Script                                   What it seeds
-----    ------                                   -------------
1        iam/iam_tenant_types_populate.sql         system, production, evaluation, automation
2        iam/iam_tenant_statuses_populate.sql       active, suspended, deactivated
3        iam/iam_system_tenant_populate.sql         The system tenant (UUID max sentinel)

4        dq/dq_change_reasons_populate.sql          Audit trail change reasons + categories

5        dq/dq_data_domain_populate.sql             Data domain classification
6        dq/dq_subject_area_populate.sql            Subject area classification
7        dq/dq_coding_scheme_authority_type_...     Coding scheme authority types
8        dq/dq_coding_scheme_populate.sql           Coding schemes

9        refdata/refdata_rounding_types_...         Rounding type lookup
10       refdata/refdata_party_categories_...       system, operational
11       refdata/refdata_party_types_populate.sql   Internal, Bank, Corporate, HedgeFund, ...
12       refdata/refdata_party_statuses_...         Active, Inactive, Suspended
13       refdata/refdata_party_id_schemes_...       LEI, BIC, etc.
14       refdata/refdata_contact_types_...          Email, Phone, etc.
14.5     refdata/refdata_system_business_...       WRLD business centre for system tenant
15       refdata/refdata_system_party_populate.sql  System party for system tenant

16       iam/iam_populate.sql                       Permissions and roles
17       variability/variability_populate.sql       Feature flags (bootstrap mode, etc.)

Ordering Constraints

The most important ordering constraint: the system tenant (step 3) must exist before any data that has tenant_id columns, because insert triggers call ores_iam_validate_tenant_fn() which checks the tenant exists.

The system party (step 15) must come after party lookup tables (steps 10-14) and change reasons (step 4), because the parties insert trigger validates party_category, party_type, status, and change_reason_code against those lookup tables.

Governance Layer

Script: populate/governance/governance_populate.sql

- Origin, nature, treatment dimensions
- Methodologies
- Artefact types
- Dataset bundles

Catalogues Layer

Script: populate/catalogues/catalogues_populate.sql

Populates artefact staging tables from various data sources:

Source          What                                Artefact Table
------          ----                                --------------
Flag Icons      Country flag SVGs                   ores_dq_images_artefact_tbl
ISO 3166        Countries                           ores_dq_countries_artefact_tbl
ISO 4217        Currencies                          ores_dq_currencies_artefact_tbl
Solvaris        Extended currency metadata           (merged into currencies artefact)
FPML            Financial products reference data   ores_dq_*_artefact_tbl (many)
Crypto          Cryptocurrency data                 (merged into currencies artefact)
GLEIF LEI       Legal Entity Identifiers            ores_dq_lei_*_artefact_tbl
IP2Country      IP geolocation ranges               ores_dq_ip2country_artefact_tbl

Artefact data is NOT live reference data. It must be published to production tables via the Data Librarian's "Publish Datasets" feature.

Multi-Environment Architecture

ORE Studio supports multiple isolated development environments, allowing concurrent work across different checkouts without database conflicts.

Database Layout

ores_dev_local1     # Environment-specific database
ores_dev_local2     # Environment-specific database
ores_dev_local3     # etc.
ores_test_*         # Temporary test databases (created/dropped by tests)
ores_frosty_leaf    # Whimsical-named instances

Naming Conventions

Database Type	Pattern	Example
Environment	`ores_dev_<env>`	`ores_dev_local2`
Test (temporary)	`ores_test_<pid>_*`	`ores_test_1234_5678`
Whimsical instances	`ores_<adjective>_<noun>`	`ores_frosty_leaf`

Environment Isolation

Environment databases are created from scratch using two-phase creation
Recreating one environment does NOT affect others
Tests create temporary databases with full schema via setup_schema.sql
Each checkout (local1, local2, etc.) auto-detects its database name

Database Roles and Users

ORE Studio uses a role-based access control system for database connections. This is separate from the application-level RBAC stored in the ores_iam_* tables.

Role Hierarchy

Group roles (no login) act as permission templates:

Role	Purpose
`ores_owner`	DDL operations (schema changes)
`ores_rw`	Read-write operations (application)
`ores_ro`	Read-only operations (analytics/BI)

Service Users

User	Inherits From	Purpose
`ores_ddl_user`	`ores_owner`	Migrations, schema changes
`ores_cli_user`	`ores_rw`	Command-line interface
`ores_wt_user`	`ores_rw`	Web toolkit service
`ores_shell_user`	`ores_rw`	Interactive shell
`ores_http_user`	`ores_rw`	HTTP REST service
`ores_readonly_user`	`ores_ro`	Analytics, BI tools

Test Users

User	Inherits From	Purpose
`ores_test_ddl_user`	`ores_owner`	Create/drop test databases
`ores_test_dml_user`	`ores_rw`	Run tests

Credential Management

pg_service.conf (Recommended)

Create ~/.pg_service.conf for convenient access:

[ores_cli]
host=localhost
port=5432
dbname=ores_dev_local2
user=ores_cli_user
password=your_password

[ores_ddl]
host=localhost
port=5432
dbname=ores_dev_local2
user=ores_ddl_user
password=your_password

Set permissions and use:

chmod 600 ~/.pg_service.conf
psql service=ores_cli

Environment Variables

For development and CI/CD:

# Application-specific password variables
export ORES_DB_CLI_PASSWORD=your_password
export ORES_DB_DDL_PASSWORD=your_password
export ORES_DB_WT_PASSWORD=your_password
export ORES_DB_HTTP_PASSWORD=your_password
export ORES_DB_SHELL_PASSWORD=your_password

# Test infrastructure
export ORES_TEST_DB_DDL_PASSWORD=your_password
export ORES_TEST_DB_PASSWORD=your_password

Database Types and Creation Paths

Three types of databases, two creation paths.

Prerequisites

Run initial setup scripts as postgres superuser in order:

# 1. Install extensions (btree_gist required, timescaledb optional)
psql -U postgres -f setup_extensions.sql

# 2. Create roles and users (provide passwords for all users)
psql -U postgres \
  -v ddl_password='DDL_PASS' \
  -v cli_password='CLI_PASS' \
  -v wt_password='WT_PASS' \
  -v shell_password='SHELL_PASS' \
  -v http_password='HTTP_PASS' \
  -v test_ddl_password='TEST_DDL_PASS' \
  -v test_dml_password='TEST_DML_PASS' \
  -v ro_password='RO_PASS' \
  -f setup_user.sql

Two-Phase Database Creation

All databases are created via a two-phase process:

Phase 1: create_database.sql (postgres superuser)
    create database <name> from template0
    grant connect, create on database
    install extensions (btree_gist)
    grant usage on schema public

Phase 2: setup_schema.sql (ores_ddl_user)
    run create/create.sql          (schema)
    run foundation_populate.sql    (foundation data)
    run populate/populate.sql      (governance + catalogues)
    grant on tables and sequences
    run instance/init_instance.sql

Creating Databases

Database with Default Name

# Phase 1: Create empty database (postgres)
psql -U postgres -f create_database.sql

# Phase 2: Setup schema and populate (ores_ddl_user)
PGPASSWORD='DDL_PASS' psql -U ores_ddl_user -d ores -f setup_schema.sql

Database with Specific Name

# Phase 1: Create empty database (postgres)
psql -U postgres -v db_name='my_database' -f create_database.sql

# Phase 2: Setup schema and populate (ores_ddl_user)
PGPASSWORD='DDL_PASS' psql -U ores_ddl_user -d my_database -f setup_schema.sql

When to Use Which

Scenario	Script
Full cluster recreation	`recreate_database.sh`
Recreate one dev environment	`recreate_env.sh`

Bash Wrapper Scripts

recreate_database.sh

Full teardown and recreation of the entire cluster. Requires passwords for all database users. Executes:

teardown_all.sql - drops all ORES databases and users
setup_user.sql - creates roles and users
create_database.sql - creates empty database (postgres)
setup_schema.sql - creates schema and populates data (ores_ddl_user)

recreate_env.sh

Recreates a single environment database (ores_dev_local1, etc.) without affecting other environments. Uses two-phase creation: create_database.sql (postgres) then setup_schema.sql (ores_ddl_user).

drop_database.sh

Safely drops a single database. Checks for active connections and refuses to drop protected databases.

Utility Scripts

Located in utility/:

Script	Purpose
`validate_schemas.sh`	Parses create/drop scripts for consistency
`recreate_entity.sh`	Drop + recreate a single table (fast iteration)
`check_db_connections.sh`	Shows active connections to a database
`kill_db_connections.sh`	Terminates all connections to a database
`list_databases.sh`	Lists all ORES databases in the cluster

validate_schemas.sh

Run this after any schema change. Checks that every create script has a matching drop script and vice versa. Reports warnings for mismatches.

recreate_entity.sh

For fast iteration when modifying a single table:

./utility/recreate_entity.sh refdata_parties

This drops and recreates just that entity's table, triggers, and functions without touching anything else. Useful during development but does not repopulate data.

Test Infrastructure

Tests use pgTAP (PostgreSQL unit testing framework).

File	What it tests
`iam_tenant_type_test.sql`	Tenant type validation function
`iam_tenant_status_test.sql`	Tenant status validation function
`refdata_party_test.sql`	Party insert trigger (category, type, status, uniqueness)
`refdata_counterparty_test.sql`	Counterparty insert trigger
`refdata_currencies_test.sql`	Currency validation
`refdata_validation_test.sql`	General refdata validation functions
`refdata_party_reference_data_test.sql`	Party reference data lookups
`dq_artefact_type_test.sql`	Artefact type validation

Run all tests:

./test/run_tests.sh

Adding a New Entity: Checklist

When adding a new table or lookup to the schema:

Create scripts: Add create/COMPONENT/ENTITY_create.sql and optionally ENTITY_notify_trigger_create.sql. Wire into the component's master create script (e.g., create/refdata/refdata_create.sql).
Drop scripts: Add matching drop scripts. Wire into the component's master drop script in reverse order.
Populate scripts (if seeded): Add populate/COMPONENT/ENTITY_populate.sql. Wire into the appropriate layer:
- Foundation data (lookup tables, system config) goes in foundation_populate.sql
- Governance metadata goes in governance_populate.sql
- Catalogue/artefact data goes in catalogues_populate.sql
Validate: Run ./utility/validate_schemas.sh to check create/drop consistency.
Test (optional): Add a pgTAP test in test/ for validation functions.

Ordering Pitfalls

A table's insert trigger may validate foreign keys against system tenant data in lookup tables. Ensure the lookup table is populated before any INSERT into the dependent table.
The system tenant must exist before any table with a tenant_id column can be populated.
RLS policies depend on IAM functions; the rls/ scripts must run after iam/.

Tenant Provisioning

When a new tenant is onboarded (via the SystemProvisionerWizard or the ores_iam_provision_tenant_fn SQL function), the following happens:

Tenant record created in ores_iam_tenants_tbl
IAM data copied from system tenant: permissions, roles, role_permissions
WRLD business centre seeded in ores_refdata_business_centres_tbl
System party created in ores_refdata_parties_tbl (party_category='System', party_type='Internal', business_center_code='WRLD', root of party hierarchy)

Reference data (currencies, countries, etc.) is NOT copied during provisioning. Tenants populate refdata via the Data Librarian's "Publish Datasets" feature.

Top: Documentation

Schema: ORE Studio SQL Schema

Skills: SQL Schema Creator