API startup requires /ref to be mounted read-write (cv_cmip7_aft.yaml missing + sqlite journal)

[lewisjared]

Deploying climate-ref-frontend:v0.2.3 against the climate-ref-aft Helm chart (0.1.0) against an existing REF state volume surfaces two issues that together force /ref to be mounted read-write in the API pod when a read-only mount should be sufficient.

1. cv_cmip7_aft.yaml missing from packaged climate_ref_core

On startup the API resolves the dimensions CV via climate_ref.config._dimensions_cv_factory:

filename = "cv_cmip7_aft.yaml"
return Path(str(importlib.resources.files("climate_ref_core.pycmec") / filename))

In the worker image (ghcr.io/climate-ref/climate-ref:v0.13.0) the file is present at:

/app/.venv/lib/python3.11/site-packages/climate_ref_core/pycmec/cv_cmip7_aft.yaml

In the frontend image (ghcr.io/climate-ref/climate-ref-frontend:v0.2.3) climate_ref_core is installed but that YAML is not in the wheel, so the API crashes with:

FileNotFoundError: [Errno 2] No such file or directory:
'/app/.venv/lib/python3.11/site-packages/climate_ref_core/pycmec/cv_cmip7_aft.yaml'

The image's build process appears to strip or omit package-data from climate_ref_core. Fixing the frontend image (or vendoring the file) so that the packaged wheel includes climate_ref_core/pycmec/*.yaml resolves this.

Current workaround: an init container copies the file from the worker image onto the shared /ref PVC and the API is pointed at it via REF_DIMENSIONS_CV_PATH.

2. SQLite DB opened read-write at startup

With REF_DATABASE_URL=sqlite:////ref/ref.db (or the default derived from REF_CONFIGURATION), the API opens the DB in read-write mode and SQLite tries to create a journal/WAL sidecar next to the DB file. When /ref is mounted readOnly: true this fails:

OperationalError: (sqlite3.OperationalError) unable to open database file

For a read-only consumer (API just reads the state written by workers), the DB should either be opened via a read-only URI (sqlite:///file:/ref/ref.db?mode=ro&immutable=1&uri=true) or the API should not require write access to /ref at all.

Current workaround: drop readOnly: true on the API's /ref mount.

Environment

• Chart: climate-ref-aft 0.1.0 (climate-ref-aft PR #7)
• Worker image: ghcr.io/climate-ref/climate-ref:v0.13.0
• Frontend image: ghcr.io/climate-ref/climate-ref-frontend:v0.2.3
• REF core: 0.13.0
• Kubernetes 1.x, Talos Linux

Suggested fixes

1. Frontend image build: include climate_ref_core/pycmec/*.yaml in the wheel / image.
2. API: open the configured SQLite DB with mode=ro&immutable=1 (or accept a URI-form URL) when the API is the sole reader, so /ref can stay read-only.

[lewisjared]

Additional finding: migration detection is stricter than the CLI

After working around (1) by seeding cv_cmip7_aft.yaml via an init container from the worker image, and (2) by mounting /ref read-write, the API now reaches ref_backend.core.ref.get_database and raises:

ValueError: The database migration has not been run.
Check the database URL in your config file and run the migration.

despite the climate-ref db migrate Job (running climate-ref 0.13.0) reporting Database is already up to date at revision a3b4c5d6e7f8 against the same SQLite file.

Direct check against the DB the API sees confirms the alembic version is present:

$ sqlite3 /ref/test-lazy/db/climate_ref.db \
    "SELECT * FROM alembic_version"
a3b4c5d6e7f8

So _get_database_revision(connection) in the API must be returning None for this DB, even though the row is there. The ref-app backend's uv.lock pins climate-ref==0.12.3:

# backend/pyproject.toml
dependencies = [
    ...
    "climate-ref[aft-providers,postgres]>=0.12.2",
    ...
]

but the Helm chart in climate-ref-aft 0.1.0 runs migrations with climate-ref 0.13.0. The alembic revision written by 0.13.0 (a3b4c5d6e7f8) appears to confuse the 0.12.3 bundled in the frontend image — either via schema drift or how Database.from_config constructs the engine.

Suggested changes

1. Bump climate-ref to >=0.13.0 in backend/pyproject.toml (and relock) so the API's alembic script directory understands the revisions the chart's migrate Job produces.
2. Expose an API surface that mirrors ref db status (the CLI command at climate-ref/packages/climate-ref/src/climate_ref/cli/db.py) so operators and the API itself can use the exact same code path as the CLI for migration-state checks. This removes the risk of the two diverging in the future.
3. Optionally, have the backend treat "DB revision is unknown but present" as "already migrated by a newer CLI" rather than raising — so the migration state is owned by the CLI/Job and the API trusts it.

Current local workaround

/ref mounted read-write so:

• the init container can drop cv_cmip7_aft.yaml
• SQLite can write its journal/WAL

Both go away once the frontend image ships cv_cmip7_aft.yaml in its climate_ref_core wheel, the backend pins 0.13.0, and (ideally) the DB is opened read-only.

[lewisjared]

v0.3.0 regression: provider.configure() writes to DB even when REF_READ_ONLY_DATABASE=true

Confirmed that ref-app v0.3.0 opens the DB via sqlite:///file:/ref/test-lazy/db/climate_ref.db?mode=ro&immutable=1&uri=true correctly (log: Connecting to database at sqlite:///file:...?mode=ro&immutable=1&uri=true), but then crashes during startup when the provider registry calls provider.configure(config):

DEBUG | climate_ref_core.providers:configure:82 - Configuring provider esmvaltool using ignore_datasets_file /ref/cache/climate_ref/default_ignore_datasets.yaml
...
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) attempt to write a readonly database
[SQL: INSERT INTO diagnostic (slug, name, provider_id, enabled) VALUES (?, ?, ?, ?) RETURNING id, created_at, updated_at]
[parameters: ('ozone-annual-cycle', 'Ozone Diagnostics', 1, 1)]

So REF_READ_ONLY_DATABASE=true currently only changes the DB URL; it doesn't stop the provider-registry code path from attempting writes. When the API is genuinely read-only (as the new flag intends), provider registration should either:

1. be skipped entirely (just read whatever the workers/CLI already registered), or
2. run inside a graceful "check-then-insert" path that tolerates a read-only session.

Workaround for deployment: unset REF_READ_ONLY_DATABASE and mount /ref rw, i.e. revert to the pre-v0.3.0 behavior. Leaves the original readOnly: true goal unfulfilled until this is fixed.