ORE Studio SQL Schema

┌─────────────────────────────────────────────────────────────────┐
│  metadata schema                                                │
│  ─────────────────                                              │
│  Purpose: Define, classify, and stage data                      │
│                                                                 │
│  • Governance: dimensions, domains, change_reasons              │
│  • Registry: datasets, catalogs, bundles, methodologies         │
│  • Staging: *_artefact_tbl (source for publication)             │
│  • Audit: publications, bundle_publications                     │
│                                                                 │
│  Template: ships with standard definitions + reference data     │
│  Users: can extend with custom datasets, methodologies, etc.    │
└─────────────────────────────────────────────────────────────────┘
                              │
                              │ publish (controlled gate for data entry)
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│  production schema                                              │
│  ─────────────────                                              │
│  Purpose: Operational data for application consumption          │
│                                                                 │
│  • Reference data: countries, currencies, coding schemes, etc.  │
│  • Market data: rates, prices, stress scenarios (future)        │
│  • IAM: accounts, roles, permissions                            │
│  • Assets: images, tags                                         │
│                                                                 │
│  Template: empty (or minimal seed data)                         │
│  Population: via publish from metadata, plus operational writes │
└─────────────────────────────────────────────────────────────────┘

Production tables maintain foreign key references to metadata tables where appropriate. For example:

• production.refdata_countries_tbl.coding_scheme_code references metadata.dq_coding_schemes_tbl.code
• production.refdata_*.change_reason_code references metadata.dq_change_reasons_tbl.code

This enforces that production data references valid governance metadata.

• accounts (temporal): user accounts with authentication credentials
• login_info: security tracking for login attempts and lock status
• sessions (temporal): user session tracking with geolocation

• roles (temporal): named roles for grouping permissions
• permissions (temporal): fine-grained permission codes
• account_roles (junction): many-to-many mapping of accounts to roles
• role_permissions (junction): many-to-many mapping of roles to permissions

• images (temporal): SVG image storage with version tracking
• tags (temporal): image categories (flag, currency, commodity)
• image_tags (junction): many-to-many image-to-tag mapping
• currencies (temporal): ISO 4217 currency definitions with formatting and optional image_id for flags

• feature_flags (temporal): feature toggles for runtime configuration

Provides GiST index support for B-tree data types. Required for temporal exclusion constraints that prevent overlapping validity periods.

CREATE EXTENSION IF NOT EXISTS btree_gist;

Time-series database extension for efficient session analytics. If installed, the sessions table becomes a hypertable with automatic time-based partitioning.

Essential lookup and configuration data required for schema integrity. This data is populated in the template database and must be present before other metadata can be inserted.

Foundation layer is populated via populate/foundation/populate_foundation.sql.

Metadata that defines the rules and classifications for how data is organized. This layer sets out the landscape for data quality management.

Data governance layer is populated via populate/governance/populate_governance.sql.

Catalogued reference data organized according to governance rules. This layer contains the actual data awaiting publication to production tables.

Data sources include:

• Flag Icons: Visual assets for countries
• ISO Standards: Countries, currencies (ISO 3166, ISO 4217)
• Solvaris: Extended currency metadata
• IP to Country: IP geolocation mappings
• FPML: Financial products reference data
• Cryptocurrency: Digital currency data

Data catalogues layer is populated via populate/catalogues/populate_catalogues.sql.

Live reference data in production tables, published from the data catalogues layer via the "Publish Datasets" feature in the Data Librarian window.

Additional production tables populated via operational processes (not publication):

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 application user
psql -U postgres -v ores_password='YOUR_SECRET' -f setup_user.sql

# 3. Create admin database (whimsical name generation)
psql -U postgres -f admin/setup_admin.sql

Create the ores_template database containing the schema and foundation layer:

psql -U postgres -f setup_template.sql

The template includes:

• Complete database schema (tables, triggers, functions)
• Foundation layer data (change control, rounding types, data governance framework, IAM, feature flags)

Create a new database instance from the template with an auto-generated whimsical name (fast, uses PostgreSQL filesystem copy):

psql -U postgres -f create_instance.sql

Example output:

Generating whimsical database name...
Creating database: dancing_lemur_42
==========================================
Database created successfully!
==========================================
Database name: dancing_lemur_42

Connect with:
  psql -U ores -d dancing_lemur_42

To create an instance with a specific name:

psql -U postgres -v db_name='my_database' -f create_instance.sql

For environments without template support, create a standalone database:

psql -U postgres -v db_name='my_database' -f create_database_direct.sql