Pocketbase API Rules

While learning PocketBase, many times there are patterns and helpful things to know that are either not in the docs, are not obvious.

Namely, API rules are hard. People have built websites to help you build API rules for example.

One thing I recently learned, is that an API rule's are translated to SQL and can create joins in unexpected (but needed ways). For example:

Client call: pb.collection('login_bonus_earned').create({ user: 'abc124', loginBonus: 'hij876' });

API rule:

@request.auth.id != "" &&
@request.body.user = @request.auth.id &&
@request.body.loginBonus.day >= @todayStart &&
@request.body.loginBonus.day <= @todayEnd

@request.body.loginBonus.day at first glance, day is NOT in the body of the request. It automatically triggers a join due to loginBonus being a relation, which matches the foreign key provided by the client. Resulting in this join:

WITH login_bonus_earned__pb_create__OKR3fS AS (
  SELECT
    '__temp_id____pb_create__OKR3fS' AS id,
    'qa5d6btux2iqwwb' AS user,
    '38ivyu8qk4tenot' AS loginBonus,
    '' AS created,
    '' AS updated
)
SELECT DISTINCT (1)
FROM login_bonus_earned__pb_create__OKR3fS
LEFT JOIN login_bonus AS __data_login_bonus_loginBonus
  ON __data_login_bonus_loginBonus.id = '38ivyu8qk4tenot'
WHERE (
  'qa5d6btux2iqwwb' IS NOT '' AND
  'qa5d6btux2iqwwb' = 'qa5d6btux2iqwwb' AND
  __data_login_bonus_loginBonus.day >= '2025-07-20 00:00:00.000Z' AND
  __data_login_bonus_loginBonus.day <= '2025-07-20 23:59:59.999Z'
)
LIMIT 1