SQL Recipes

\ir '$project_sql_dir'/create_instance.sql

CREATE DATABASE
name	enabled	description
database_created
ores_frosty_leaf

List all databases created from the ORES template:

select * from admin_ores_databases_view;

database_name
ores_nameless_sea

List databases created by automated tests (ores_test_*, oresdb_test_*):

select * from admin_test_databases_view;

database_name

List instance databases only (excludes test and template):

select * from ores_instance_databases;

database_name
ores_dark_wildflower

Delete all test databases. Uses \gexec because DROP DATABASE cannot run inside a transaction block:

select format('DROP DATABASE IF EXISTS %I;', database_name)
from test_databases \gexec

WARNING: This deletes ALL ORES databases (instances, templates, test). Use with caution!

select format('DROP DATABASE IF EXISTS %I;', database_name)
from admin_ores_databases_view \gexec

DROP DATABASE

Delete all instance databases (preserves templates and test databases):

select format('DROP DATABASE IF EXISTS %I;', database_name)
from admin_ores_instance_databases_view \gexec

DROP DATABASE

Generate cleanup SQL without executing (for review):

select generate_cleanup_test_databases_sql();

generate_cleanup_test_databases_sql
-- No test databases found to clean up.

Generate a unique whimsical name for a new database:

select admin_generate_unique_database_name_from_server_fn();

admin_generate_unique_database_name_from_server_fn
ores_little_morning

Get all currently active accounts (where valid_to is infinity):

select id, username, email, version, valid_from
from ores.accounts
where valid_to = '9999-12-31 23:59:59'::timestamptz
order by username;

id	username	email	version	valid_from

View the complete temporal history of an account:

select id, username, email, version, valid_from, valid_to
from ores.accounts
where username = 'admin'
order by valid_from;

id	username	email	version	valid_from	valid_to

select id, username, email, version, valid_from
from ores.accounts
where username = 'admin'
  and valid_to = '9999-12-31 23:59:59'::timestamptz;

id	username	email	version	valid_from

View login tracking for all accounts:

select li.account_id, a.username, li.last_login, li.failed_logins,
       li.locked, li.online, li.last_ip
from ores.login_info li
join ores.accounts a on a.id = li.account_id
  and a.valid_to = '9999-12-31 23:59:59'::timestamptz
order by li.last_login desc nulls last;

List all permissions seeded by permissions_populate.sql:

select code, description
from ores.permissions
where valid_to = '9999-12-31 23:59:59'::timestamptz
order by code;

List all roles seeded by roles_populate.sql:

select name, description
from ores.roles
where valid_to = '9999-12-31 23:59:59'::timestamptz
order by name;

List which permissions are assigned to each role:

select r.name as role_name, p.code as permission
from ores.role_permissions rp
join ores.roles r on r.id = rp.role_id
  and r.valid_to = '9999-12-31 23:59:59'::timestamptz
join ores.permissions p on p.id = rp.permission_id
  and p.valid_to = '9999-12-31 23:59:59'::timestamptz
where rp.valid_to = '9999-12-31 23:59:59'::timestamptz
order by r.name, p.code;

List all system flags seeded by system_flags_populate.sql:

select name, enabled, description
from ores.feature_flags
where name like 'system.%'
  and valid_to = '9999-12-31 23:59:59'::timestamptz
order by name;

To re-seed an existing database instance (idempotent - won't duplicate data):

-- System data (RBAC + system flags)
\ir '$project_sql_dir'/populate/populate.sql

-- Reference data (currencies, flags, images)
\ir '$project_sql_dir'/populate/reference_data.sql

To re-run only specific population scripts:

-- RBAC only
\ir '$project_sql_dir'/populate/permissions_populate.sql
\ir '$project_sql_dir'/populate/roles_populate.sql

-- System flags only
\ir '$project_sql_dir'/populate/system_flags_populate.sql

-- All reference data (currencies, flags, images)
\ir '$project_sql_dir'/populate/reference_data.sql

-- Or individual reference data scripts
\ir '$project_sql_dir'/populate/currencies_populate.sql
\ir '$project_sql_dir'/populate/load_flags.sql
\ir '$project_sql_dir'/populate/flags_populate.sql
\ir '$project_sql_dir'/populate/currency_images_populate.sql

select id, name, description, version
from ores.roles
where valid_to = '9999-12-31 23:59:59'::timestamptz
order by name;

| id                                   | name       | description                                                   | version |
|--------------------------------------+------------+---------------------------------------------------------------+---------|
| dd2ecaec-138b-453c-a110-77441a60583e | Admin      | Full administrative access to all system functions            |       1 |
| b898350f-f704-4e97-b8ec-36c3481af052 | Operations | Operations - currency management and account viewing          |       1 |
| d48b608d-c894-4346-887d-228e342d36c6 | Sales      | Sales operations - read-only currency access                  |       1 |
| d533c93f-bf3c-4d1f-bba8-4e949b976a8a | Support    | Support - read-only access to all resources and admin screens |       1 |
| a74f8a4e-f331-4179-aecb-cda61e472805 | Trading    | Trading operations - currency read access                     |       1 |
| 6e4ee358-1970-400f-a3cb-fbcfe8296f52 | Viewer     | Viewer - basic read-only access to domain data                |       1 |

select id, code, description
from ores.permissions
where valid_to = '9999-12-31 23:59:59'::timestamptz
order by code;

| id                                   | code                    | description                           |
|--------------------------------------+-------------------------+---------------------------------------|
| 8f827c95-2af4-4d78-9ccb-22f5d9f4df99 | *                       | Full access to all operations         |
| 9e337a58-2ee6-4de4-8946-76015de6e928 | accounts:create         | Create new user accounts              |
| 7151df18-e4c9-4522-bb65-c1055a3e97c1 | accounts:delete         | Delete user accounts                  |
| c225162e-2b04-4685-b1be-0bacbe367e45 | accounts:lock           | Lock user accounts                    |
| 4956001c-5ece-4dc8-b437-c834045c25eb | accounts:read           | View user account details             |
| 5f5f8909-3820-411f-a9f2-d5efb5ad351a | accounts:reset_password | Force password reset on user accounts |
| fc5587f3-72ad-4706-a1ac-ca735af5a919 | accounts:unlock         | Unlock user accounts                  |
| cf4ac1a8-b39e-4faa-b6d3-f9ed3d1ef41a | accounts:update         | Modify user account settings          |
| 43ce84fb-b14a-490a-92d1-4eade25eda37 | currencies:create       | Create new currencies                 |
| 7fb3f2e6-9b0d-4996-9c02-14e39a732acd | currencies:delete       | Delete currencies                     |
| 5472a25a-40cf-4bac-9c95-dda2589f12f7 | currencies:history      | View currency version history         |
| 17f8d400-47fa-4648-8b47-48ab3c1ec82b | currencies:read         | View currency details                 |
| e1bca8df-8133-4c08-9365-160baf99125e | currencies:update       | Modify currency settings              |
| 62e6e9ea-5258-41d2-9107-0602512801e6 | flags:create            | Create new feature flags              |
| 8e42e330-5467-48c2-9a26-5af5b5fcb10e | flags:delete            | Delete feature flags                  |
| 3b26954f-6368-4c12-b2e2-67797d442714 | flags:read              | View feature flag status              |
| 5f72cfef-c8a9-4926-a1a3-f51c955595f7 | flags:update            | Modify feature flag settings          |
| 12e8300a-e76c-4cd6-a439-2098e45393a2 | login_info:read         | View login history and info           |
| 95d69095-1223-4dbb-970e-8042d90d61de | roles:assign            | Assign roles to accounts              |
| 2301fe57-f568-4930-b3ec-4a9fba903932 | roles:create            | Create new roles                      |
| e12a56d4-64a8-436d-8ad8-1909b2439369 | roles:delete            | Delete roles                          |
| eba37c8c-79e2-4193-9a54-ceb555b1cdda | roles:read              | View role details                     |
| cd69a420-e93b-4e41-9bab-ee8d6dcd0b7a | roles:revoke            | Revoke roles from accounts            |
| af5743da-3042-469d-8487-ed859b9de7f5 | roles:update            | Modify role permissions               |

select r.name as role_name, p.code as permission_code, p.description
from ores.role_permissions rp
join ores.roles r on r.id = rp.role_id
  and r.valid_to = '9999-12-31 23:59:59'::timestamptz
join ores.permissions p on p.id = rp.permission_id
  and p.valid_to = '9999-12-31 23:59:59'::timestamptz
where rp.valid_to = '9999-12-31 23:59:59'::timestamptz
  and r.name = 'Admin'
order by p.code;

| role_name | permission_code | description                   |
|-----------+-----------------+-------------------------------|
| Admin     | *               | Full access to all operations |

select a.username, r.name as role_name, ar.assigned_at, ar.assigned_by
from ores.account_roles ar
join ores.accounts a on a.id = ar.account_id
  and a.valid_to = '9999-12-31 23:59:59'::timestamptz
join ores.roles r on r.id = ar.role_id
  and r.valid_to = '9999-12-31 23:59:59'::timestamptz
where ar.valid_to = '9999-12-31 23:59:59'::timestamptz
  and a.username = 'admin'
order by r.name;

| username | role_name | assigned_at | assigned_by |
|----------+-----------+-------------+-------------|

select distinct a.username, p.code as permission_code
from ores.accounts a
join ores.account_roles ar on ar.account_id = a.id
  and ar.valid_to = '9999-12-31 23:59:59'::timestamptz
join ores.role_permissions rp on rp.role_id = ar.role_id
  and rp.valid_to = '9999-12-31 23:59:59'::timestamptz
join ores.permissions p on p.id = rp.permission_id
  and p.valid_to = '9999-12-31 23:59:59'::timestamptz
where a.valid_to = '9999-12-31 23:59:59'::timestamptz
  and a.username = 'admin'
order by p.code;

| username | permission_code |
|----------+-----------------|

Uses the built-in RBAC function:

select ores.account_has_permission('admin', 'accounts:create') as has_permission;

|---|

select iso_code, name, symbol, numeric_code, currency_type
from ores.currencies
where valid_to = '9999-12-31 23:59:59'::timestamptz
order by iso_code
limit 10;

| iso_code | name                          | symbol | numeric_code | currency_type |
|----------+-------------------------------+--------+--------------+---------------|
| AED      | UAE Dirham                    | د.إ    |          784 | fiat          |
| AFN      | Afghan Afghani                | ؋      |          971 | fiat          |
| ALL      | Albanian Lek                  | L      |          008 | fiat          |
| AMD      | Armenian Dram                 | ֏     |          051 | fiat          |
| ANG      | Netherlands Antillean Guilder | ƒ      |          532 | fiat          |
| AOA      | Angolan Kwanza                | Kz     |          973 | fiat          |
| ARS      | Argentine Peso                | $      |          032 | fiat          |
| AUD      | Australian Dollar             | A$     |          036 | fiat          |
| AWG      | Aruban Florin                 | ƒ      |          533 | fiat          |
| AZN      | Azerbaijani Manat             | ₼     |          944 | fiat          |

select iso_code, name, symbol, fraction_symbol,
       fractions_per_unit, rounding_type, rounding_precision, format
from ores.currencies
where iso_code = 'USD'
      and valid_to = '9999-12-31 23:59:59'::timestamptz;

| iso_code | name      | symbol | fraction_symbol | fractions_per_unit | rounding_type | rounding_precision | format    |
|----------+-----------+--------+-----------------+--------------------+---------------+--------------------+-----------|
| USD      | US Dollar | $      | ¢               |                100 | standard      |                  2 | $#,##0.00 |

select currency_type, count(*) as count
from ores.currencies
where valid_to = '9999-12-31 23:59:59'::timestamptz
group by currency_type
order by count desc;

| currency_type | count |
|---------------+-------|
| fiat          |   153 |
| commodity     |     4 |
| supranational |     1 |

select c.iso_code, c.name, c.symbol, i.key as flag_key
from ores.currencies c
left join ores.currency_images ci on ci.iso_code = c.iso_code
  and ci.valid_to = '9999-12-31 23:59:59'::timestamptz
left join ores.images i on i.image_id = ci.image_id
  and i.valid_to = '9999-12-31 23:59:59'::timestamptz
where c.valid_to = '9999-12-31 23:59:59'::timestamptz
  and c.iso_code = 'GBP';

| iso_code | name                   | symbol | flag_key |
|----------+------------------------+--------+----------|
| GBP      | British Pound Sterling | £      | gb       |

select name, enabled, description, version
from ores.feature_flags
where valid_to = '9999-12-31 23:59:59'::timestamptz
order by name;

| name                                 | enabled | description                                                                                                         | version |
|--------------------------------------+---------+---------------------------------------------------------------------------------------------------------------------+---------|
| system.bootstrap_mode                |       1 | Indicates whether the system is in bootstrap mode (waiting for initial admin account).                              |       1 |
| system.disable_password_validation   |       0 | When enabled, disables strict password validation. FOR TESTING/DEVELOPMENT ONLY.                                    |       1 |
| system.signup_requires_authorization |       0 | Controls whether new signups require admin authorization. NOT YET IMPLEMENTED - enabling will cause signup to fail. |       1 |
| system.user_signups                  |       0 | Controls whether user self-registration is allowed.                                                                 |       1 |

select name, enabled
from ores.feature_flags
where name like '%bootstrap_mode%'
  and valid_to = '9999-12-31 23:59:59'::timestamptz;

| name                  | enabled |
|-----------------------+---------|
| system.bootstrap_mode |       1 |

select name, description
from ores.feature_flags
where enabled = 1
  and valid_to = '9999-12-31 23:59:59'::timestamptz
order by name;

| name                  | description                                                                            |
|-----------------------+----------------------------------------------------------------------------------------|
| system.bootstrap_mode | Indicates whether the system is in bootstrap mode (waiting for initial admin account). |

select image_id, key, description, version
from ores.images
where valid_to = '9999-12-31 23:59:59'::timestamptz
order by key
limit 20;

| image_id                             | key   | description         | version |
|--------------------------------------+-------+---------------------+---------|
| a6ac20cb-f45f-4375-9082-4673bb1f453a | ad    | Flag for code AD    |       1 |
| 31cafad9-db0c-4359-ada8-da331f08949e | ae    | Flag for code AE    |       1 |
| 1116a6b0-7511-4fda-869f-b4f9946c15b4 | af    | Flag for code AF    |       1 |
| c056bd53-7c7b-4313-990c-7b9d1d80828d | ag    | Flag for code AG    |       1 |
| 31959ae1-6f64-4534-9e1a-fd3dd34ef84d | ai    | Flag for code AI    |       1 |
| 458d56d5-e535-477c-8af5-32bc6097c24b | al    | Flag for code AL    |       1 |
| 9e01af37-ac88-4488-a857-81843736cf21 | am    | Flag for code AM    |       1 |
| fcdb0cd7-6184-4bfa-bad0-180cebeb2224 | ao    | Flag for code AO    |       1 |
| 762567e8-d165-4a57-b818-63d21fbe5748 | aq    | Flag for code AQ    |       1 |
| 85de14d6-5556-410b-83dc-c0c92098d5db | ar    | Flag for code AR    |       1 |
| 495b0456-03da-4c9b-b1b9-110a0d8e06f8 | arab  | Flag for code ARAB  |       1 |
| 04bf011e-5cd2-4715-9707-31ce93a9cb2a | as    | Flag for code AS    |       1 |
| 81cf090b-2cdb-4b95-b530-9144bb3246c3 | asean | Flag for code ASEAN |       1 |
| f3d3f779-ed01-4bf2-85fe-99ba62596647 | at    | Flag for code AT    |       1 |
| beb9ef9e-ba7c-438d-9282-85298a083347 | au    | Flag for code AU    |       1 |
| 9dfc69aa-fe57-4518-a988-0e089f58e390 | aw    | Flag for code AW    |       1 |
| e866518b-5625-433d-b2dc-f107af7a222b | ax    | Flag for code AX    |       1 |
| 5e0b55f6-83df-4fda-862a-0d1ab3d5139e | az    | Flag for code AZ    |       1 |
| aa21e9e2-eac2-44dd-a927-7eef3c9ffa30 | ba    | Flag for code BA    |       1 |
| 05f4a87e-b481-4953-b6cd-9e9b5d3fc7a6 | bb    | Flag for code BB    |       1 |

select tag_id, name, description
from ores.tags
where valid_to = '9999-12-31 23:59:59'::timestamptz
order by name;

| tag_id                               | name | description                    |
|--------------------------------------+------+--------------------------------|
| d932c1f5-37fd-48fd-b592-7e7450afded0 | flag | Country and region flag images |

select i.key, i.description
from ores.images i
join ores.image_tags it on it.image_id = i.image_id
  and it.valid_to = '9999-12-31 23:59:59'::timestamptz
join ores.tags t on t.tag_id = it.tag_id
  and t.valid_to = '9999-12-31 23:59:59'::timestamptz
where i.valid_to = '9999-12-31 23:59:59'::timestamptz
  and t.name = 'flag'
order by i.key
limit 20;

| key   | description         |
|-------+---------------------|
| ad    | Flag for code AD    |
| ae    | Flag for code AE    |
| af    | Flag for code AF    |
| ag    | Flag for code AG    |
| ai    | Flag for code AI    |
| al    | Flag for code AL    |
| am    | Flag for code AM    |
| ao    | Flag for code AO    |
| aq    | Flag for code AQ    |
| ar    | Flag for code AR    |
| arab  | Flag for code ARAB  |
| as    | Flag for code AS    |
| asean | Flag for code ASEAN |
| at    | Flag for code AT    |
| au    | Flag for code AU    |
| aw    | Flag for code AW    |
| ax    | Flag for code AX    |
| az    | Flag for code AZ    |
| ba    | Flag for code BA    |
| bb    | Flag for code BB    |

See all versions of an entity over time:

select id, username, email, version, valid_from, valid_to,
       case when valid_to = '9999-12-31 23:59:59'::timestamptz
            then 'current' else 'historical' end as status
from ores.accounts
where username = 'admin'
order by valid_from;

| id | username | email | version | valid_from | valid_to | status |
|----+----------+-------+---------+------------+----------+--------|

Get the state of an entity at a specific point in time:

select id, username, email, version
from ores.accounts
where username = 'admin'
  and valid_from <= '2025-01-15 12:00:00'::timestamptz
  and valid_to > '2025-01-15 12:00:00'::timestamptz;

select pg_size_pretty(pg_database_size(current_database())) as database_size;

| database_size |
|---------------|
| 12 MB         |

select schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as size
from pg_tables
where schemaname = 'ores'
order by pg_total_relation_size(schemaname || '.' || tablename) desc;

| schemaname | tablename        | size    |
|------------+------------------+---------|
| ores       | images           | 1232 kB |
| ores       | image_tags       | 176 kB  |
| ores       | currencies       | 96 kB   |
| ores       | currency_images  | 88 kB   |
| ores       | roles            | 72 kB   |
| ores       | tags             | 72 kB   |
| ores       | role_permissions | 64 kB   |
| ores       | permissions      | 56 kB   |
| ores       | feature_flags    | 56 kB   |
| ores       | sessions         | 48 kB   |
| ores       | accounts         | 48 kB   |
| ores       | account_roles    | 40 kB   |
| ores       | login_info       | 32 kB   |
| ores       | ip2country       | 16 kB   |

Uses PostgreSQL statistics for all tables in the schema:

select relname as table_name, n_live_tup as rows
from pg_stat_user_tables
where schemaname = 'ores'
order by n_live_tup desc;

| table_name       | rows |
|------------------+------|
| images           |  276 |
| image_tags       |  276 |
| currency_images  |  158 |
| currencies       |  158 |
| permissions      |   24 |
| role_permissions |   21 |
| roles            |    6 |
| feature_flags    |    4 |
| tags             |    1 |
| ip2country       |    0 |
| account_roles    |    0 |
| sessions         |    0 |
| accounts         |    0 |
| login_info       |    0 |