ORDER BYの無いLIMIT 1が、複数テナントのstagingで別の校のadminを返す

結論

PostgreSQLは、ORDER BYの無いSELECTにLIMIT 1を付けても、どの行を返すかを保証しない。 staging限定のdev-loginツールで「既存のschool_adminを1件解決する」クエリがこの形で書かれていた。1テナントしかない間は毎回同じ行が返っているように見えるが、staging DBに複数テナントのテストデータが積み上がると、dev-loginを実行するたびに違う学校のadmin uidが返りかねない状態だった。レビューで指摘され、dev-login専用のテスト校を常に優先するCASE式のORDER BYを足して決定的にした。

発端

dev-loginは、staging限定でパスワードを一切保存・要求せずにschool_adminやteacherとしてログインできる開発者向けの機能で、複数の多層ゲート（isProdLikeEnv・APP_ENV==='staging'・ゲート鍵）を通過した経路からしか到達できない。目的は、実データ運用と同じアカウントで体験できるよう、staging DBに既にある教員・学校管理者のアカウントをそのまま使い回すことにある。

そのアカウント解決を担うのがfindExistingSchoolAdminUidで、users.role = 'school_admin'かつis_activeな行を1件だけ取得する。WHEREで絞ってLIMIT 1を付ければ「1件だけ返る」ことは保証されるが、その1件がどの行になるかは、ORDER BYを書かない限り保証されない。PostgreSQLは実行計画・ページのキャッシュ状態・並行更新の有無などによって、ORDER BYの無いSELECTが返す行順を変えてよい。

原因

この関数を使うstaging DBには、複数の学校（テナント）のテストデータが並行して存在しうる。school_adminかつis_activeな行が複数校にまたがって存在するとき、順序を明示しないSELECTのLIMIT 1が実際にどの校の行を返すかは、その時点のテーブルの物理的な状態に依存する。テストデータを増やしたり、他のバッチが同じテーブルを更新したりするたびに、返る行が変わりうる。

関数のdocstringは、この懸念を次のように明記している。

staging に複数テナントのテストデータが入った場合に「最初に作られた任意の校」へ落ちるのを避けるため、dev-login 専用テスト校（DEVLOGIN_TEST）配下の school_admin を最優先で選ぶ

dev-loginの用途に照らすと、これを放置した場合の症状は「今日はA校のadminとしてログインできたのに、同じ手順で明日はB校のadminとしてログインしてしまう」という形になる。dev-loginは開発者が動作確認に使う経路なので直接の顧客影響は無いが、テナント分離を前提にした検証を行う機能が、どのテナントのアカウントを返すか実行のたびに変わりうる前提の上に立っているのは危うい。この懸念はレビューで指摘され、staging に本番相当のデータが積み上がるより前、同じPR内で決定的な順序に直されている。

直し方

「どの校の行が返ってもよい」という前提をやめ、「dev-login専用のテスト校（DEVLOGIN_TEST）を常に最優先で選ぶ」という決定的な順序をORDER BYに持たせた。

// packages/db/src/queries/dev-login-accounts.ts:93-103
const rows = await tx
  .select({ id: users.id })
  .from(users)
  .where(and(eq(users.role, "school_admin"), eq(users.isActive, true)))
  // DEVLOGIN_TEST 校配下を最優先（0=テスト校 / 1=その他）。同位は createdAt/id で安定化。
  .orderBy(
    sql`case when ${users.schoolId} = ${DEVLOGIN_TEST_SCHOOL_ID} then 0 else 1 end`,
    asc(users.createdAt),
    asc(users.id),
  )
  .limit(1);

CASE式でschoolIdがdev-login専用のテスト校IDと一致する行を0、それ以外を1として並べ替えることで、テスト校のadminが存在する限り必ずそちらが選ばれる。テスト校のadminが無い場合のフォールバックとして残る同位（1同士）も、createdAtとidの昇順で安定させ、同じデータ状態なら常に同じ行を返すようにしている。特定の実在校のadminを使いたい場合は、呼び出し側がDEV_LOGIN_CONFIG.admin.uidヒントで明示的に指定する経路も別に用意されており、本修正はそのヒントが無いときの既定動作を決定的にするものになっている。

再発防止

ORDER BYの欠落は、テストが1テナント・少量データの状態で書かれ、実行されている限り顕在化しない。LIMIT句だけを見ても「1件に絞れている」ことしか分からず、「同じ1件が返り続けるか」は別の問題だと意識しないと見落とす。

一般化すると、SELECT ... LIMIT Nで「代表の1件」を取得するクエリを書くときは、ORDER BYを「何を優先して選ぶか」を明示する場所として扱う必要がある。 ORDER BYを省略したLIMITは、データ量が少なく単一のテナントしか無いテスト環境では偶然安定して見えるため、複数テナントや大量データを持つ環境で初めて症状が出る。dev-loginのような「代表の1件を決定的に選びたい」処理では、優先したい条件（この場合は専用テスト校かどうか）をCASE式で明示し、同順位の場合の安定化キー（createdAt・idなど）まで含めてORDER BYに書き切ることで、テナント数やデータ量に依存しない結果になる。