lvl0/fedi-feed-router

Fork 0

Migration 000015 authenticates against Lemmy and blocks every migration behind it #150

New issue

Closed

opened 2026-08-15 01:59:33 +02:00 by myrmidex · 1 comment

myrmidex commented

2026-08-15 01:59:33 +02:00

Owner

Symptom

Production is broken on three fronts:

Logging in returns a 500. The dashboard's approval-rate chart queries
decided_at, which does not exist:
```
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'decided_at' in 'SELECT'
```
Approving an article returns a 500, same missing column in an UPDATE
(RouteArticle.php:107 via Articles.php:66)
Publishing fails with couldnt_find_community, because channel community ids
were never resolved (#145)

Root cause

All three follow from the same thing: migrations have not run since v1.3.7.
migrate:status shows nine pending, 000015 through 000023. Everything is
blocked behind the first one, which fails:

2024_01_01_000015_store_numeric_community_id_on_platform_channels .. 1s FAIL
Could not authenticate against https://belgae.social to resolve community 'news'.

000015 converts platform_channels.channel_id from a community slug to
Lemmy's numeric id. To do that it logs into the live instance and calls
getCommunityId() — once per channel.

There are three channels, all on the same instance and account:

1 ch=newsbottest inst=https://belgae.social acct=newsbot
2 ch=news        inst=https://belgae.social acct=newsbot
3 ch=nieuws      inst=https://belgae.social acct=newsbot

Lemmy rate-limits authentication, and three logins in immediate succession is
over the limit. Reproduced directly against production:

1 => OK
2 => OK
3 => FAILED

So the migration fails deterministically on the third channel. Credentials and
connectivity are both fine — a single login returns a valid JWT, and
curl https://belgae.social/api/v3/site from inside the container returns 200.

The actual problem

The rate limit is the trigger. The defect is that a schema migration performs
network calls against a third-party service at all.

Any outage, expired credential, rate limit, or DNS failure at deploy time then
blocks not just this migration but every migration behind it, indefinitely and
silently. That is what happened: eight unrelated schema changes have been stuck
for several releases because one of them wanted to talk to Lemmy.

Fix

Strip the authentication out of 000015 entirely. The migration should change
the schema and nothing else:

Convert channel_id to hold a numeric community id
Keep any value that is already numeric
Null out anything that is not, rather than resolving it
Leave the column nullable, since "not yet resolved" is now a real state that
the application has to represent

Wiping is deliberately chosen over resolving. A migration that cannot fail on a
network call cannot block a deploy. Unresolved channels are a visible,
repairable state; a stuck migration chain is neither.

channel_id currently ends up nullable(false) with a unique constraint on
(platform_instance_id, channel_id). Nulls have to be permitted, and the
constraint reviewed accordingly.

Depends on

Wiping the ids leaves affected channels unable to publish until they are
repaired, so #145's repair action becomes required rather than optional. In
practice these two tickets ship together, or #145 ships first.

Without a repair path, the only recovery is deleting and recreating the channel,
which cascades away its routes, keywords, route articles and publications.

Principle to carry forward

No migration may depend on a remote service. Data that can only be obtained from
an external API belongs in a job or a user-triggered action, where failure is
visible and retryable, not in a migration where failure is silent and blocking.

Worth checking the other migrations for the same shape while this is open.

Acceptance criteria

000015 performs no network calls and imports no API service
Non-numeric channel_id values are nulled rather than resolved
The column permits null, and the unique constraint still behaves correctly
migrate --force completes on production, applying all nine pending
migrations
Logging in and approving an article no longer 500
Channels with a null channel_id are visible as needing repair (#145)
No other migration performs network calls

State of the database

Nothing is half-applied. resolve() runs entirely before any DDL, which was
deliberate, so each failed attempt left the schema untouched.
platform_channels still has its original columns.

#145 channel health and repair action, now a dependency rather than a
follow-up
PR #151 contains a narrower fix (authenticate once per instance instead of
once per channel). That approach is superseded by this one and should be
closed or repurposed — it reduces the number of calls but keeps the migration
dependent on Lemmy being reachable.

## Symptom Production is broken on three fronts: - Logging in returns a 500. The dashboard's approval-rate chart queries `decided_at`, which does not exist: ``` SQLSTATE[42S22]: Column not found: 1054 Unknown column 'decided_at' in 'SELECT' ``` - Approving an article returns a 500, same missing column in an `UPDATE` (`RouteArticle.php:107` via `Articles.php:66`) - Publishing fails with `couldnt_find_community`, because channel community ids were never resolved (#145) ## Root cause All three follow from the same thing: **migrations have not run since v1.3.7.** `migrate:status` shows nine pending, `000015` through `000023`. Everything is blocked behind the first one, which fails: ``` 2024_01_01_000015_store_numeric_community_id_on_platform_channels .. 1s FAIL Could not authenticate against https://belgae.social to resolve community 'news'. ``` `000015` converts `platform_channels.channel_id` from a community slug to Lemmy's numeric id. To do that it logs into the live instance and calls `getCommunityId()` — **once per channel**. There are three channels, all on the same instance and account: ``` 1 ch=newsbottest inst=https://belgae.social acct=newsbot 2 ch=news inst=https://belgae.social acct=newsbot 3 ch=nieuws inst=https://belgae.social acct=newsbot ``` Lemmy rate-limits authentication, and three logins in immediate succession is over the limit. Reproduced directly against production: ``` 1 => OK 2 => OK 3 => FAILED ``` So the migration fails deterministically on the third channel. Credentials and connectivity are both fine — a single login returns a valid JWT, and `curl https://belgae.social/api/v3/site` from inside the container returns 200. ## The actual problem The rate limit is the trigger. The defect is that **a schema migration performs network calls against a third-party service at all.** Any outage, expired credential, rate limit, or DNS failure at deploy time then blocks not just this migration but every migration behind it, indefinitely and silently. That is what happened: eight unrelated schema changes have been stuck for several releases because one of them wanted to talk to Lemmy. ## Fix Strip the authentication out of `000015` entirely. The migration should change the schema and nothing else: - Convert `channel_id` to hold a numeric community id - Keep any value that is already numeric - **Null out anything that is not**, rather than resolving it - Leave the column nullable, since "not yet resolved" is now a real state that the application has to represent Wiping is deliberately chosen over resolving. A migration that cannot fail on a network call cannot block a deploy. Unresolved channels are a visible, repairable state; a stuck migration chain is neither. `channel_id` currently ends up `nullable(false)` with a unique constraint on `(platform_instance_id, channel_id)`. Nulls have to be permitted, and the constraint reviewed accordingly. ## Depends on Wiping the ids leaves affected channels unable to publish until they are repaired, so **#145's repair action becomes required rather than optional**. In practice these two tickets ship together, or #145 ships first. Without a repair path, the only recovery is deleting and recreating the channel, which cascades away its routes, keywords, route articles and publications. ## Principle to carry forward No migration may depend on a remote service. Data that can only be obtained from an external API belongs in a job or a user-triggered action, where failure is visible and retryable, not in a migration where failure is silent and blocking. Worth checking the other migrations for the same shape while this is open. ## Acceptance criteria - [ ] `000015` performs no network calls and imports no API service - [ ] Non-numeric `channel_id` values are nulled rather than resolved - [ ] The column permits null, and the unique constraint still behaves correctly - [ ] `migrate --force` completes on production, applying all nine pending migrations - [ ] Logging in and approving an article no longer 500 - [ ] Channels with a null `channel_id` are visible as needing repair (#145) - [ ] No other migration performs network calls ## State of the database Nothing is half-applied. `resolve()` runs entirely before any DDL, which was deliberate, so each failed attempt left the schema untouched. `platform_channels` still has its original columns. ## Related - #145 channel health and repair action, now a dependency rather than a follow-up - PR #151 contains a narrower fix (authenticate once per instance instead of once per channel). That approach is superseded by this one and should be closed or repurposed — it reduces the number of calls but keeps the migration dependent on Lemmy being reachable.

myrmidex added this to the v1.4.1 milestone 2026-08-15 01:59:33 +02:00

myrmidex added the

bug

label 2026-08-15 01:59:33 +02:00

myrmidex changed title from ~~Migration 000015 authenticates once per channel and trips Lemmy's rate limit~~ to Migration 000015 authenticates against Lemmy and blocks every migration behind it

2026-08-15 09:44:23 +02:00

myrmidex referenced this issue

2026-08-15 10:35:50 +02:00

Channel health status and a repair action for stale community ids #145

myrmidex commented

2026-08-15 11:56:37 +02:00

Author

Owner

Verified on production

Deployed with v1.4.1. All nine pending migrations applied as batch 5:

2024_01_01_000015_store_numeric_community_id_on_platform_channels .. [5] Ran
2024_01_01_000016_add_color_to_feeds_table ......................... [5] Ran
2024_01_01_000017_widen_articles_image_url ......................... [5] Ran
2024_01_01_000018_add_publish_retry_tracking_to_route_articles ..... [5] Ran
2024_01_01_000019_create_activity_logs_table ....................... [5] Ran
2024_01_01_000020_add_credential_health_to_platform_accounts ....... [5] Ran
2024_01_01_000021_add_decided_at_to_route_articles ................. [5] Ran
2024_01_01_000022_replace_publish_retry_with_failure_reason ........ [5] Ran
2024_01_01_000023_add_channel_foreign_key_to_article_publications .. [5] Ran

They ran automatically on container startup, so migrate --force afterwards
reported "Nothing to migrate".

route_articles now has decided_at, and the three channels holding slugs were
deleted along with their routes, as intended.

Channels and routes were recreated through the UI. A Dutch VRT article was then
approved and published to Lemmy successfully, with no duplicate — the full
fetch → approve → publish path works.

That covers the acceptance criteria: the migration performs no network calls,
unconvertible channels are deleted rather than resolved, migrate completes,
and the login and approval 500s are gone.

## Verified on production Deployed with v1.4.1. All nine pending migrations applied as batch 5: ``` 2024_01_01_000015_store_numeric_community_id_on_platform_channels .. [5] Ran 2024_01_01_000016_add_color_to_feeds_table ......................... [5] Ran 2024_01_01_000017_widen_articles_image_url ......................... [5] Ran 2024_01_01_000018_add_publish_retry_tracking_to_route_articles ..... [5] Ran 2024_01_01_000019_create_activity_logs_table ....................... [5] Ran 2024_01_01_000020_add_credential_health_to_platform_accounts ....... [5] Ran 2024_01_01_000021_add_decided_at_to_route_articles ................. [5] Ran 2024_01_01_000022_replace_publish_retry_with_failure_reason ........ [5] Ran 2024_01_01_000023_add_channel_foreign_key_to_article_publications .. [5] Ran ``` They ran automatically on container startup, so `migrate --force` afterwards reported "Nothing to migrate". `route_articles` now has `decided_at`, and the three channels holding slugs were deleted along with their routes, as intended. Channels and routes were recreated through the UI. A Dutch VRT article was then approved and published to Lemmy successfully, with no duplicate — the full fetch → approve → publish path works. That covers the acceptance criteria: the migration performs no network calls, unconvertible channels are deleted rather than resolved, `migrate` completes, and the login and approval 500s are gone.

myrmidex closed this issue

2026-08-15 11:56:44 +02:00

myrmidex referenced this issue

2026-08-16 17:35:28 +02:00

Belga discovery skips the newest article — offset=1 should be offset=0 #158