P3009

[jackthenewbie]

I don't want to populate #15240 with troubleshooting, so I'm starting a discussion here:

[migration] starting AFFiNE database migration
fixing failed migrations.
running prisma migrations.
yarn run v1.22.22
$ /app/node_modules/.bin/prisma migrate deploy
Prisma schema loaded from schema.prisma
Datasource "db": PostgreSQL database "affine", schema "public" at "affine-postgres:5432"
115 migrations found in prisma/migrations
Error: P3009
migrate found failed migrations in the target database, new migrations will not be applied. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve
The `20260618000000_backend_runtime_tables` migration started at 2026-07-09 22:10:18.988066 UTC failed
error Command failed with exit code 1.
info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.
node:internal/errors:983
  const err = new Error(message);
              ^
Error: Command failed: yarn prisma migrate deploy
    at genericNodeError (node:internal/errors:983:15)
    at wrappedFn (node:internal/errors:537:14)
    at checkExecSyncError (node:child_process:916:11)
    at execSync (node:child_process:988:15)
    at runPrismaMigrations (file:///app/scripts/self-host-predeploy.js:43:3)
    at file:///app/scripts/self-host-predeploy.js:111:1
    at ModuleJob.run (node:internal/modules/esm/module_job:343:25)
    at async onImport.tracePromise.__proto__ (node:internal/modules/esm/loader:681:26)
    at async asyncRunEntryPointWithESMLoader (node:internal/modules/run_main:117:5) {
  status: 1,
  signal: null,
  output: [ null, null, null ],
  pid: 87,
  stdout: null,
  stderr: null
}
Node.js v22.23.1

@darkskygit since you said the new image rollout, i tried to remove the old image, redownload the image, however the issue is still there. Note that i haven't run /app/node_modules/.bin/prisma migrate resolve --applied 20260618000000_backend_runtime_tables yet. Can i have you test it out since you got same issue @dyonng ?

I'm positive that I downloaded the new image, here's my digest:

me@pc:~/repo/affine/deployments$ echo $EXPECTED
sha256:cc309913620e153f7aaea47cd40d1eff993030cf2b2df8f88270663459db37c9
me@pc:~/repo/affine/deployments$ kubectl -n affine get pods -l app=affine   -o jsonpath='{range .items[*].status.initContainerStatuses[*]}{.name}{" "}{.imageID}{"\n"}{end}{range .items[*].status.containerStatuses[*]}{.name}{" "}{.imageID}{"\n"}{end}' | grep "$EXPECTED"
affine-migration ghcr.io/toeverything/affine@sha256:cc309913620e153f7aaea47cd40d1eff993030cf2b2df8f88270663459db37c9

[darkskygit]

Containers that have already triggered this issue need to run prisma migrate resolve to fix it.
Containers that have not triggered the issue can be upgraded smoothly using the retagged container.

[jackthenewbie]

I've tried it but fortunately, didn't work. Is this where I should nuke everything and start over? I didn't create a backup of db (that's on me), but I've backup workspace via setting. If you have any other idea let me know

root@affine-6dd46f5dcd-g2dgn:/app# /app/node_modules/.bin/prisma migrate resolve --applied 20260618000000_backend_runtime_tables
Prisma schema loaded from schema.prisma
Datasource "db": PostgreSQL database "affine", schema "public" at "affine-postgres:5432"
Error: P3017

The migration 20260618000000_backend_runtime_tables could not be found. Please make sure that the migration exists, and that you included the whole name of the directory. (example: "20201207184859_initial_migration")

root@affine-6dd46f5dcd-g2dgn:/app# 

[darkskygit]

run this: AFFINE_REVISION=0.27.0-beta.6 docker compose run --rm --no-deps affine_migration /app/node_modules/.bin/prisma migrate resolve --applied 20260618000000_backend_runtime_tables

[jackthenewbie]

thank you, appreciate spending your time for this :) workspace is back seems like everything working now, ig the 20260618000000_backend_runtime_tables is gone in 0.27.0

[dyonng]

@jackthenewbie yes, ran into the same issue as you when pulling the new images and redeploying. this time i had to run /app/node_modules/.bin/prisma migrate resolve --applied 20260714000000_backfill_legacy_permission_and_subscription on the affine_server container to fix it. my migration and db state might be messed up since i ran the previous prisma migration from the original issue thread.

[jackthenewbie]

May i ask where is this 20260714000000_backfill_legacy_permission_and_subscription come from? just before trying to run it on my server:)

Edit: nvm that your message just loaded

[darkskygit]

try this to fix dirty data: docker compose exec -T postgres sh -lc 'psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "${POSTGRES_DB:-affine}" -c "WITH ranked AS (SELECT workspace_id, page_id, user_id, ROW_NUMBER() OVER (PARTITION BY workspace_id, page_id ORDER BY created_at DESC, user_id DESC) AS rn FROM workspace_page_user_permissions WHERE type = 99) UPDATE workspace_page_user_permissions AS p SET type = 1 FROM ranked AS r WHERE r.rn > 1 AND p.workspace_id = r.workspace_id AND p.page_id = r.page_id AND p.user_id = r.user_id AND p.type = 99 RETURNING p.workspace_id, p.page_id, p.user_id, p.type;"'

[dyonng]

got this error trying to run that permission update command:

ERROR:  permission_projection_error:invalid_legacy_role:Cannot project unknown doc role 1 for 79cc7481-d592-4acb-8810-3c85b1aeec53.sAr-_wBlwFVV5ZoqZ9cDT user 87937e4c-c26d-4e85-8bf5-ef89cc25adf5
CONTEXT:  PL/pgSQL function affine_permission_project_workspace_page_user_permission() line 78 at RAISE

[darkskygit]

previous query had a typo, try this: docker compose exec -T postgres sh -lc 'psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "${POSTGRES_DB:-affine}" -c "WITH ranked AS (SELECT workspace_id, page_id, user_id, ROW_NUMBER() OVER (PARTITION BY workspace_id, page_id ORDER BY created_at DESC, user_id DESC) AS rn FROM workspace_page_user_permissions WHERE type = 99) UPDATE workspace_page_user_permissions AS p SET type = 30 FROM ranked AS r WHERE r.rn > 1 AND p.workspace_id = r.workspace_id AND p.page_id = r.page_id AND p.user_id = r.user_id AND p.type = 99 RETURNING p.workspace_id, p.page_id, p.user_id, p.type;"'

[dyonng]

Got this as the output, which looks promising:

             workspace_id             |        page_id        |               user_id                | type
--------------------------------------+-----------------------+--------------------------------------+------
 79cc7481-d592-4acb-8810-3c85b1aeec53 | sAr-_wBlwFVV5ZoqZ9cDT | 87937e4c-c26d-4e85-8bf5-ef89cc25adf5 |   30
 79cc7481-d592-4acb-8810-3c85b1aeec53 | yAZMStgCK-Qo7aXbIA-1v | 87937e4c-c26d-4e85-8bf5-ef89cc25adf5 |   30
(2 rows)

UPDATE 2

thank you for all your hard work