ores.sql

Diagram
Summary
Inputs
Outputs
Entry points
Scripts
- Lifecycle scripts
- Utility scripts
Dependencies
See also

Diagram

Figure 1: ores.sql

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: there is no incremental migration runner. All schema changes are applied by destroying and recreating the database from scratch using compass db recreate -y -k. This is the only supported schema upgrade path. Do not add ALTER TABLE scripts — they will not be executed by any tool and their presence implies a migration workflow that does not exist.

Inputs

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

Outputs

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

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.

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.

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`.

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.

Dependencies

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