SQLAlchemy DBURI Configuration

Soliplex uses SQLAlchemy to store persistent data in two separate databases:

• One database holds history for AG-UI threads and runs, created by clients interacting with its AG-UI endpoints. See below.

• Another database holds authorization information: a list of administrative users, and a list of room authorization policies and the access control list (ACL) entries they contain. See below.

Configuration for these databases uses SQLAlchemy database URLs, in two flavors:

• Synchronous URLs, used by code which is not written to run using Python's async mechanisms (e.g., the CLI and TUI modules). This style of database URLs is the default described in the SQLAlchemy docs.

• Asynchronous URLs, used by code which does use Python's async mechanisms (e.g., FastAPI endpoint functions). See this SQLAlchemy page for details on the extension which adds async support to SQLAlchemy.

Because of the requirement for async support, Soliplex cannot use all possible SQLAlchemy engines. Known to work:

• SQLite via the aiosqlite async dialect

• Postgres via psycopg, whose postgresql+psycopg:// DBURIs serve both the sync and the async engines (see below)

• Postgres via the asyncpg async dialect (postgresql+asyncpg://), installed separately

Untested:

• MySQL via the asyncmy async dialect

• MySQL via the aiomysql async dialect

• Oracle via its built-in async dialect

• Microsolt SQL Server via the aioodbc async dialect

Installing the PostgreSQL driver

Soliplex installs only the SQLite drivers. To use PostgreSQL, install one of two extras, each providing psycopg:

Use postgres for container images and FIPS hosts, where the OS's package updates and FIPS configuration cover libpq and OpenSSL; setting PSYCOPG_IMPL=python there keeps psycopg on the system libpq even if a psycopg-binary wheel is installed alongside. Use postgres-binary for development, and for hosts without libpq.

The same postgresql+psycopg:// DBURI serves both sync_dburi and async_dburi. Name the driver explicitly: a bare postgresql:// means psycopg2, which neither extra installs, and a DBURI naming a driver that is not installed fails with ModuleNotFoundError when its engine is created.

On Windows, psycopg's async mode cannot run on the event loop Python uses there by default. Use postgresql+asyncpg:// for the async_dburi instead, installing asyncpg yourself.

thread_persistence_db

Soliplex uses this pair of URLs to record and query information about AG-UI threads and runs initiated by clients. If this sections is not configured, Soliplex uses an in-memory Sqlite database, e.g.:

thread_persistence_db:
  sync_dburi: "sqlite://"
  async_dburi: "sqlite+aiosqlite://"

To use an on-disk SQLite database for thread persistence (note the four forward-slashes!):

thread_persistence_db:
  sync_dburi: "sqlite:////path/to/thread_persistence.sqlite"
  async_dburi: "sqlite+aiosqlite:////path/to/thread_persistence.sqlite"

To use a Postgres server, assuming the login is "soliplex" and the password is defined as a secret named "POSTGRES_PASSWORD":

thread_persistence_db:
  sync_dburi: "postgresql+psycopg://soliplex:secret:POSTGRES_PASSWORD@/soliplex_threads"
  async_dburi: "postgresql+psycopg://soliplex:secret:POSTGRES_PASSWORD@/soliplex_threads"

authorization_db

Soliplex uses this pair of URLs to record and query authorization information: a list of administrative users, and a list of room authorization policies and the access control list (ACL) entries they contain. If this sections is not configured, Soliplex uses an in-memory Sqlite database, e.g.:

authorization_db:
  sync_dburi: "sqlite://"
  async_dburi: "sqlite+aiosqlite://"

To use an on-disk SQLite database for thread persistence (note the four forward-slashes!):

authorization_db:
  sync_dburi: "sqlite:////path/to/authorization.sqlite"
  async_dburi: "sqlite+aiosqlite:////path/to/authorization.sqlite"

To use a Postgres server, assuming the login is "soliplex" and the password is defined as a secret named "POSTGRES_PASSWORD":

authorization_db:
  sync_dburi: "postgresql+psycopg://soliplex:secret:POSTGRES_PASSWORD@/soliplex_authorization"
  async_dburi: "postgresql+psycopg://soliplex:secret:POSTGRES_PASSWORD@/soliplex_authorization"

Migrations

Soliplex keeps both databases at the schema revision its release expects, and by default does that itself, on the first writable open (see Database Migrations). That is the right arrangement when the application role also owns its schema, which is the case for SQLite and for a default PostgreSQL stack.

It does not work for a deployment which deliberately runs the application as a least-privilege role -- every object owned by an administrative role, the application granted only SELECT, INSERT, UPDATE, DELETE. Such a role is refused CREATE TABLE and ALTER TABLE, correctly. Two optional sub-keys, available on both stanzas, hand the job to somebody else.

migration_dburi

A synchronous DBURI naming the role which owns the schema. Only a sync URL is needed, because Alembic's online mode is synchronous.

authorization_db:
  sync_dburi: "postgresql+psycopg://soliplex:secret:APP_PASSWORD@/soliplex_authz"
  async_dburi: "postgresql+psycopg://soliplex:secret:APP_PASSWORD@/soliplex_authz"
  migration_dburi: "postgresql+psycopg://owner:secret:OWNER_PASSWORD@/soliplex_authz"

When it is absent, migrations use sync_dburi, which is exactly what every release before this key existed did. A SQLite deployment, a soliplex-template stack, or a PostgreSQL deployment which has not separated owner from application role needs no change and sees no difference.

Like the other two URLs, it interpolates both secret: and env: markers.

migration_policy

Two named values; absence is the third state:

Configuring migration_dburi without a migration_policy implies explicit.

Configuring disabled allows sharing a single installation.yaml between services running as different roles: one service resolves disabled, while another (the one which runs the migration) resolves explicit.

The whole value may be a single env: marker instead of a literal, wired via the compose service environment and the installation config:

authorization_db:
  sync_dburi: "postgresql+psycopg://soliplex:secret:APP_PASSWORD@/soliplex_authz"
  async_dburi: "postgresql+psycopg://soliplex:secret:APP_PASSWORD@/soliplex_authz"
  migration_dburi: "postgresql+psycopg://owner:secret:OWNER_PASSWORD@/soliplex_authz"
  migration_policy: "env:SOLIPLEX_MIGRATION_POLICY"

Both the ordinary server container and a special-purpose migrations container read that one stanza, and they differ only in what SOLIPLEX_MIGRATION_POLICY says:

Which is why the stanza names two credentials. soliplex / APP_PASSWORD is the least-privilege role the application runs as, granted only DML; owner / OWNER_PASSWORD owns the schema, and nothing but the migrations container's soliplex-cli database invocation ever connects with it.

A value which is neither policy name is an error, reported when the policy is read. Absence means automatic migration, so an unrecognized value must not fall through to it.

What a policy refuses

Both values are enforced wherever soliplex would otherwise migrate on its own: the server's startup, and every soliplex-cli command which opens a database for writing. The refusal names the remedy under explicit (soliplex-cli database upgrade) and deliberately names no command under disabled, where migrating belongs to another service entirely.

A policy bites only when a migration is actually owed. A database already at the revision this release expects starts normally under any policy, which is what lets a service configured disabled run against a current database without knowing or caring.

One consequence is worth planning for: a database under a policy does not get its schema built on first use. Ordinarily the first writable open creates the tables; under explicit or disabled it refuses instead, so a newly created database has to be initialized with soliplex-cli database upgrade before the server is started against it. Reading is unaffected -- soliplex-cli audit databases creates and migrates nothing, so it keeps working, and reporting that a migration is owed is exactly its job.

Interpolation

Each of the DBURI values can include values interpolated from the installation configuration's environment. E.g:

authorization_db:
  sync_dburi: "postgresql+psycopg://env:SOLIPLEX_AUTHZ_USER:secret:POSTGRES_PASSWORD@/env:SOLIPLEX_AUTHZ_DBNAME"
  async_dburi: "postgresql+psycopg://env:SOLIPLEX_AUTHZ_USER:secret:POSTGRES_PASSWORD@/env:SOLIPLEX_AUTHZ_DBNAME"

Deprecated spelling

Through Soliplex v0.81, these two stanzas named the URL flavor as the sub-key, with the word dburi in the stanza name instead:

thread_persistence_dburi:
  sync: "<sync dburi>"
  async: "<async dburi>"

authorization_dburi:
  sync: "<sync dburi>"
  async: "<async dburi>"

That spelling is still read, but is deprecated and will be removed after Soliplex v0.84. Loading a configuration which uses it emits a DeprecationWarning naming the stanza and the configuration file.

The mapping to the current spelling is mechanical:

A configuration which somehow sets both spellings for the same database uses the current one, ignoring the deprecated one.

soliplex-cli config <installation-path> exports the resolved configuration using the current spelling, which makes it a convenient way to derive the replacement text: secret: and env: markers within the DBURIs are exported as written, not resolved, so the exported stanzas can be pasted back into installation.yaml.