Regression in db migrations

[ecasilla]

Preflight Checklist

• I have searched the issue tracker for an issue that matches the one I want to file, without success.
• I am not looking for support or already pursued the available support channels without success.

OpenMeter Version

v1.0.0-beta.217

Expected Behavior

the db migrations should succeed on boot up when autoMigrate: migration is in the config

Actual Behavior

it fails because of this change #2961 which has a db migration which references the public schema

Steps To Reproduce

1. update your config to use a different search_path other than the public schema to force

postgres:
  url: postgres://postgres:postgres@postgres:5432/postgres?sslmode=disable&search_path=billing
  autoMigrate: migration # Runs migrations as part of the service startup, valid values are: ent, migration, false

2. Start the container
3. You should see this error in the logs

{"time":"2025-07-28T03:22:24.180338051Z","level":"ERROR","msg":"failed to initialize database","deployment.environment.name":"unknown","service.name":"openmeter-backend","service.version":"main","telemetry.sdk.language":"go","telemetry.sdk.name":"opentelemetry","telemetry.sdk.version":"1.37.0","error":"failed to migrate db: migration failed: relation \"public.billing_invoice_split_line_groups\" does not exist (column 13) in line 4: \n-- Step 1: convert existing split lines into split line groups\n\nINSERT INTO public.billing_invoice_split_line_groups\n(\n    id,\n    namespace,\n    metadata,\n    created_at,\n    updated_at,\n    deleted_at,\n    name,\n    description,\n    service_period_start,\n    service_period_end,\n    currency,\n    tax_config,\n    unique_reference_id,\n    ratecard_discounts,\n    feature_key,\n    price,\n    subscription_id,\n    subscription_phase_id,\n    subscription_item_id\n)\n    SELECT\n      l.id,\n      l.namespace,\n      l.metadata,\n      l.created_at,\n      l.updated_at,\n      l.deleted_at,\n      l.name,\n      l.description,\n      l.period_start,\n      l.period_end,\n      l.currency,\n      l.tax_config,\n      l.child_unique_reference_id,\n      l.ratecard_discounts,\n      u.feature_key,\n      u.price,\n      l.subscription_id,\n      l.subscription_phase_id,\n      l.subscription_item_id\n    FROM\n        public.billing_invoice_lines l JOIN public.billing_invoice_usage_based_line_configs u ON (l.usage_based_line_config_id = u.id)\n    WHERE\n        l.type = 'usage_based' AND l.status = 'split';\n\n-- Step 2: Associate existing lines referencing the line lines to the split line group\n\nUPDATE public.billing_invoice_lines\nSET split_line_group_id = parent_line_id, parent_line_id = NULL\nWHERE \"type\" = 'usage_based' and \"status\" = 'valid' and \"parent_line_id\" IS NOT NULL;\n\n-- Step 3: delete the split lines\n\nDELETE FROM public.billing_invoice_lines\nWHERE \"type\" = 'usage_based' and \"status\" = 'split';\n (details: pq: relation \"public.billing_invoice_split_line_groups\" does not exist)"}

Additional Information

No response

[turip]

Thank you for the bug report. I am sorry for this regression. I have pushed a change that updates the migration to not to hardcode the schema name.

There are two ways to recover from this issue.

Manual apply

Verify that your migration state is in the expected dirty state:

SELECT * FROM schema_om;
-- you should see the following record:
    version     | dirty
----------------+-------
 20250609204117 | t
(1 row)

Apply the updated migration 20250609204117 from this PR: #3160

Then execute the following SQL statement:

UPDATE schema_om SET dirty = false;

This will cause the migrations to continue from the next migration.

Release-based

Wait for a new release with the updated migration.

Deploy the new release and make sure that versions with the problematic migrations are not running.

Validate that you are in the expected dirty state from the migrations point of view:

SELECT * FROM schema_om;
-- you should see the following record:
    version     | dirty
----------------+-------
 20250609204117 | t
(1 row)

Execute the following SQL command and restart the new version:

UPDATE schema_om SET version = '20250609172811', dirty = false;

This will cause the system to think that the database is on the previous migration and it's in healthy state. The next startup will execute the updated migration.

[turip]

A new release has been created with the fixed migrations: https://github.com/openmeterio/openmeter/releases/tag/v1.0.0-beta.218