feat(server): support custom database schema

Summary

This stacks on top of #11408 and adds first-class support for running Tuist’s primary Postgres schema outside of public.

The new configuration surface is:

• TUIST_DATABASE_SCHEMA for the release/runtime environment, defaulting to public.
• postgresql.schema in the Helm chart, defaulting to public and rendered into the server, migration job, build processor, and xcresult processor pods.
• tuist_schema psql variable support in the CNPG operational grant scripts.

Motivation

Some self-hosted environments need Tuist to avoid the PostgreSQL public schema because their internal database security policies disallow application objects there. public is not automatically unsafe by itself, but relying on it creates a poor fit for stricter environments where teams want a dedicated application namespace, explicit schema ownership, and grants scoped to that namespace.

The role split in #11408 gives us separate connection identities: migrations can run as an owner role, while the web/process runtime can run through narrower roles. That is the right foundation for custom-schema support because schema creation, migration ownership, runtime DML, and processor grants all need to agree on the same boundary. This PR adds that schema boundary while preserving the current public behavior as the default path.

Approach

Runtime configuration

Tuist.Environment now exposes helpers for reading and validating TUIST_DATABASE_SCHEMA. The default remains public, so existing installations keep their current behavior and connection startup parameters.

When a non-public schema is configured, runtime.exs adds a quoted search_path PostgreSQL startup parameter. That keeps Ecto schemas and migrations unqualified in application code while placing objects in the configured schema. The quoting is centralized in Tuist.Environment.quote_postgres_identifier/1 so release SQL and runtime startup parameters do not drift apart.

Baseline and release migrations

Tuist.Release.migrate/0 now ensures the configured schema exists before running the Ecto migration chain.

The historical priv/repo/structure.sql baseline has been converted into the first Postgres migration: 20240301000000_create_legacy_baseline.exs. That migration intentionally preserves the legacy dump as its source SQL, minus the Ecto-owned schema_migrations table and the dump’s search_path reset.

At runtime, the baseline migration resolves the current Postgres schema from the connection. When that schema is public, it runs the old public.-qualified SQL unchanged so the resulting dump stays equivalent to the old structure-load path. When the schema is custom, it replaces the public. qualifiers with the quoted current schema before executing the baseline. A migration owner role can create the schema automatically through the release task; if a customer’s database policy disallows schema creation by the migration role, the schema can be pre-created and owned/granted to that migration role.

Existing databases that were already bootstrapped before this PR do not recreate the baseline. The root migration first checks whether the current schema already has the legacy accounts table. If it does, the migration returns successfully and Ecto only records 20240301000000 in schema_migrations, so later deploys consider the root migration applied.

Runtime role grants

The runtime-role grants introduced in #11408 now target TUIST_DATABASE_SCHEMA instead of hard-coding public. That includes:

• revoking schema/database create privileges from broad roles
• granting runtime DML on tables
• granting sequence usage
• granting function execution
• keeping schema_migrations read-only for the runtime role
• default privileges for future migrated objects

This keeps the web role least-privileged whether the application schema is public or a customer-specific schema such as tuist.

Release start scripts

The release start and start-preview scripts now query schema_migrations through the configured schema when they capture the before/after migration versions. Without this, failed migrations in a custom-schema install would look in public.schema_migrations and lose the rollback diagnostic.

Helm and CNPG runbooks

The Helm chart exposes postgresql.schema and injects it into all pods that connect to the primary Postgres database. The default is public, so the rendered manifests for existing installs are behaviorally unchanged apart from an explicit env var value.

The CNPG grant scripts now accept -v tuist_schema=<schema> and default to public when omitted. This keeps processor and ops read-only grants aligned with the same schema value used by the chart.

Rollout notes

This should land after #11408 because it builds on the migration/runtime role split and the centralized database URL parsing from that branch.

For existing Tuist-managed environments, keep postgresql.schema: public and no data movement is required.

For a customer that must avoid public:

1. Provision or select an empty database.
2. Set postgresql.schema to the customer-approved schema name, for example tuist.
3. Use the migration owner URL via TUIST_MIGRATION_DATABASE_URL so migrations can create/own the schema and objects.
4. Use the web runtime URL via DATABASE_URL so normal server traffic uses the narrower runtime role.
5. Set TUIST_DATABASE_RUNTIME_ROLE so migrations grant runtime privileges to the web role after each migration run.
6. Run the CNPG processor/ops grant scripts with -v tuist_schema=<schema> if the managed CNPG roles are in use.

There is intentionally no automatic migration of existing objects from public into a custom schema in this PR. That should be a separate, explicit data migration plan if we ever need to move a live database between schemas.

structure.sql removal

The primary Postgres priv/repo/structure.sql file is removed. Fresh Postgres databases now bootstrap exclusively through migrations, both locally and in releases.

To make that safe, the old dump is now represented as a root migration and validated against the previous structure-load path with a pg_dump --schema-only comparison. The obsolete mise run db:load task was removed, and the local ecto.reset, mise run db:setup, install bootstrap, and Dockerfile references were updated so they no longer expect the primary structure.sql file to exist. The ClickHouse/IngestRepo structure file is left alone because it is a separate repository.

Validation

Ran successfully:

• mise exec -- elixir -e 'for path <- System.argv(), do: path |> File.read!() |> Code.string_to_quoted!(); IO.puts("syntax ok")' server/lib/tuist/environment.ex server/lib/tuist/release.ex server/config/runtime.exs server/test/tuist/environment_test.exs server/priv/repo/migrations/20240301000000_create_legacy_baseline.exs server/mix.exs
• mise exec -- elixir -e 'Application.ensure_all_started(:mix); Code.compile_file("server/lib/tuist/environment.ex"); unless Tuist.Environment.database_schema(nil) == "public", do: raise("nil default failed"); unless Tuist.Environment.database_schema("tuist") == "tuist", do: raise("custom schema failed"); unless Tuist.Environment.quote_postgres_identifier("Tuist") == "\"Tuist\"", do: raise("quote failed"); try do Tuist.Environment.database_schema("tuist-prod"); raise("invalid schema accepted") rescue RuntimeError -> :ok end; IO.puts("environment helper checks ok")'
• sh -n server/rel/overlays/bin/start
• sh -n server/rel/overlays/bin/start-preview
• sh -n server/mise/tasks/db/reset.sh server/mise/tasks/db/migrate.sh server/mise/tasks/db/setup.sh server/mise/tasks/install.sh
• git diff --check
• helm lint infra/helm/tuist
• helm template tuist infra/helm/tuist --set postgresql.schema=tuist --show-only templates/server-deployment.yaml
• helm template tuist infra/helm/tuist --set postgresql.schema=tuist --show-only templates/server-migration-job.yaml
• helm template tuist infra/helm/tuist --set postgresql.schema=tuist --set processor.enabled=true --show-only templates/processor-deployment.yaml
• helm template tuist infra/helm/tuist --set postgresql.schema=tuist --set xcresultProcessor.enabled=true --show-only templates/xcresult-processor-deployment.yaml
• Created a temporary PostgreSQL 17 cluster under /private/tmp, loaded the previous structure.sql path into one database, loaded the new baseline migration SQL into another database, and verified pg_dump --schema-only --no-owner --no-privileges --exclude-table=schema_migrations produced identical dumps.
• Created a temporary PostgreSQL 17 cluster under /private/tmp, dropped the public schema, created "tuist", applied the baseline migration’s custom-schema SQL transformation, and verified accounts + schema_migrations existed in "tuist" with zero tables in public.
• Created a temporary PostgreSQL 17 cluster under /private/tmp and verified the baseline guard sees an existing public.accounts table as already loaded, returns false before a custom-schema baseline exists, and returns true once accounts exists in the custom schema search path.

Attempted but blocked by the local checkout missing dependencies:

• mise exec -- mix format config/runtime.exs lib/tuist/environment.ex lib/tuist/release.ex test/tuist/environment_test.exs priv/repo/migrations/20240301000000_create_legacy_baseline.exs mix.exs failed because .formatter.exs imports unavailable deps such as ecto_sql.
• mise exec -- mix test test/tuist/environment_test.exs failed because the checkout has not run mix deps.get and Hex/Git dependencies are unavailable.
• A full mix ecto.migrate -r Tuist.Repo comparison was also attempted by pointing this worktree at the main checkout’s dependency cache, but Mix still could not load the ecto.migrate task from this worktree without installing dependencies locally.

No GitHub comments yet.