ores.sql

1. Diagram
2. Summary
3. Inputs
4. Outputs
5. Entry points
6. Scripts
- 6.1. Lifecycle scripts
- 6.2. Utility scripts
7. Dependencies
8. See also

1. Diagram

The schema is one diagram per group, because 463 tables in a single picture overflowed PlantUML's raster budget. The index names every group, the file it is drawn in and the tables it holds, and each group is drawn on its own.

Figure 1: The schema index

Group	Tables	Diagram
iam	24
assets	3
refdata	153
dq_staging	57
dq_datasets	6
dq_methodology	4
dq_metadata	15
variability	1
telemetry	5
geo	1
trading	99
mq	4
scheduler	2
reporting	20
compute	12
workflow	4
database	1
marketdata	10
analytics	20
synthetic	9
inbox	13

2. Summary

ores.sql contains the PostgreSQL schema, role provisioning, and lifecycle management for the ORE Studio database. All tables use a single public schema with the ores_ prefix and domain sub-prefix (ores_iam_*, ores_refdata_*, ores_trading_*, etc.). Service database users (ores_<env>_<service>_service) hold DML only on their own domain tables, enforcing strict service table isolation. Helper scripts handle database creation, reset, teardown, and SQL execution.

Schema upgrade policy: a database is never migrated. A schema change edits the create/ scripts, which carry the canonical schema, and the database is destroyed and recreated with compass db recreate -y -k. There are no migration scripts and no backwards compatibility code: nothing keeps an old table, column, name or permission code alive.

Schema fingerprint: schema_fingerprint.cmake hashes the SQL a recreate reads (create/, populate/, instance/ and the top-level .sql files). compass db recreate stamps it into ores_database_info_tbl, and the build compiles the same value into ores.database. Every service and test run compares the two when it creates its database context, logs both, and refuses to start on a mismatch; compass services start checks first. compass_db.schema_fingerprint is the second implementation of the same definition, and a compass test keeps the two in agreement.

3. Inputs

PostgreSQL superuser credentials for initial setup.
SQL DDL files under create/.
Seed data files under populate/.

4. Outputs

A fully provisioned PostgreSQL database with all tables, roles, RLS policies, indexes, and seed data.
Per-service users with exactly scoped DML grants.

5. Entry points

compass db recreate — drop and recreate the database from scratch (the standard schema upgrade path).
compass db setup — create the database and apply DDL without dropping first.
compass db sql — execute SQL against the ORE database.
utility/validate_schemas.sh — validate schema consistency before push.

6. Scripts

All scripts live in projects/ores.sql/ and read credentials from .env at the repository root (or from exported CI environment variables). Run them from the repository root. service_vars.sh is auto-generated and not listed here.

6.1. Lifecycle scripts

Scripts that manage the full database lifecycle: creation, teardown, recreation, reset, and SQL execution. Used during development setup, CI provisioning, and environment refresh.

Script	Purpose	Notes
`compass db recreate`	Drop and fully recreate the database from scratch: roles, DDL, RLS, seed data.	Primary setup command; safe to re-run. Reads `.env` or CI env vars.
`compass db setup`	Create the database and apply DDL without dropping roles first.	Use when roles already exist but the database or schema needs creating.
`teardown_database.sh`	Tear down a named database — remove tables, revoke grants, drop roles.	Accepts `DATABASE_NAME` as argument. Checks for active connections before dropping.
`compass db drop`	Drop a named database, refusing if active connections are present unless `--kill` is passed.	Connection checks ported into compass.
`compass db sql`	Execute a SQL file or command against the development database.	Reads host/credentials from `.env`; remaining args pass through to psql after `--`.
`compass db reset-system`	Reset all system-level data (IAM, roles, permissions) without dropping the schema.	Non-destructive; preserves tenant data.
`compass db reset-tenant`	Reset all tenant-level data for the current environment.	Non-destructive; preserves system data.
`recreate_env.sh`	Tear down and recreate the current environment's database end-to-end.	Combines teardown + recreate for a named environment. Reads `.env`.
`teardown_env.sh`	Tear down the current environment's database without recreating it.	Use when decommissioning a local or CI environment. Reads `.env`.

6.2. Utility scripts

Scripts under utility/ for inspection, validation, and targeted maintenance operations. Safe to run against a live database.

Script	Purpose	Notes
`utility/validate_schemas.sh`	Validate SQL schema naming and convention invariants before pushing.	Requires Python 3. Run before committing schema changes.
`utility/validate_env_version.sh`	Verify the `.env` file's version is compatible with the current codebase.	Guards against running stale env configs after schema migrations.
`utility/dump_database_version.sh`	Print the schema version currently recorded in the database.	Quick sanity check after a migration.
`utility/check_db_connections.sh`	Report active connections to a database.	Logic ported into compass db; kept for standalone use.
`utility/kill_db_connections.sh`	Terminate all active connections to a named database.	Required before a `DROP DATABASE` when connections are open.
`utility/list_databases.sh`	List all ORE Studio databases visible on the PostgreSQL server.	Useful for auditing environments.
`utility/show_roles.sh`	Show all database roles provisioned for ORE Studio.	Useful for verifying role provisioning after setup.
`utility/recreate_entity.sh`	Re-run create/populate scripts for a single entity.	Accepts `<component>_<entity>` argument; useful during entity development.

7. Dependencies

PostgreSQL 15+ with pg_trgm, btree_gist, and pgcrypto extensions.

8. See also

ores.database — C++ access layer that consumes this schema.