Trim last_used_ips reliably regardless of replica lag

What does this MR do and why?

The write-path trim in PersonalAccessTokens::LastUsedService was silently skipping, letting tokens accumulate more than the documented five last_used_ips. About 14% of tokens exceed the cap in production, and 88k of them appended a new IP within the last week yet stayed over it, which a working trim would have prevented.

Root cause (verified): the DELETE was correct, but the ip_count read that guarded it ran inside without_sticky_writes. In Gitlab::Database::LoadBalancing::Session, without_sticky_writes aliases ignore_writes, and write! early-returns on @ignore_writes before calling use_primary!. So after the append INSERT, the .count is served by a replica that can lag the row just written; it undercounts, the > NUM_IPS_TO_STORE guard is false, and the trim is skipped. This only manifests with a real replica, which is why it passed local single-database checks.

The fix drops the count and its guard, and trims with a single DELETE that keeps the five most recent distinct IPs, via a relation subquery so it is one statement executed on the primary and cannot be misled by replica lag. A DISTINCT ON (ip_address) collapses duplicate IPs to their newest row before the five-most-recent cut, so a token that had accumulated repeat IPs self-heals down to unique addresses on its next new-IP append:

newest_per_ip = @personal_access_token.last_used_ips
  .select('DISTINCT ON (ip_address) ' \
    'personal_access_token_last_used_ips.id, personal_access_token_last_used_ips.created_at')
  .order('ip_address ASC, created_at DESC, id DESC')

ids_to_keep = Authn::PersonalAccessTokenLastUsedIp
  .from(newest_per_ip, :personal_access_token_last_used_ips)
  .order('created_at DESC, id DESC')
  .limit(NUM_IPS_TO_STORE)
  .select(:id)

@personal_access_token.last_used_ips.where.not(id: ids_to_keep).delete_all

It runs only on the new-IP append path (dedup-gated and lease-throttled), so the cost is bounded, and it trims any pre-existing excess and duplicate IPs to five unique addresses in one shot.

This is step 2 of #616954 (closed). It is independent of the read-side cap (!250686 (merged)) and can merge on its own. The batched background migration that cleans up existing excess (step 3) must merge after this, otherwise the table re-accumulates through the old path.

Database review

The trim issues one DELETE per new-IP append, scoped to a single token:

DELETE FROM personal_access_token_last_used_ips
WHERE personal_access_token_id = $1
  AND id NOT IN (
    SELECT id FROM (
      SELECT DISTINCT ON (ip_address)
        personal_access_token_last_used_ips.id,
        personal_access_token_last_used_ips.created_at
      FROM personal_access_token_last_used_ips
      WHERE personal_access_token_id = $1
      ORDER BY ip_address ASC, created_at DESC, id DESC
    ) personal_access_token_last_used_ips
    ORDER BY created_at DESC, id DESC
    LIMIT 5
  );

The subquery selects, sorts, and de-duplicates within a single token's rows. idx_pat_last_used_ips_on_pat_id covers personal_access_token_id, so the row set is fetched by that index; per-token row counts are small (five in steady state, low hundreds in the tail), so the extra ip_address, created_at sort for the DISTINCT ON is cheap and no (personal_access_token_id, ip_address, created_at) index is warranted. Flagging for a reviewer to confirm.

EXPLAIN query plan: to be captured on Database Lab or a read replica against a real over-cap token (substitute its personal_access_token_id for $1), reading the actual ... rows= values, not the cost=... rows= estimates.

How to set up and validate locally

The bug needs a lagging replica, so it is not reproducible on a single-database GDK. The spec drives it by stubbing a stale count: on the old code the token stays above five, on the new code it is trimmed to five. To sanity-check the fix itself:

1. Give a token more than five IPs in the Rails console:

   t = PersonalAccessToken.last
   7.times { |i| t.last_used_ips.create!(organization: t.organization, ip_address: "192.0.2.#{i}") }

2. Trigger the service with a new IP (or run the spec) and confirm the token is left with exactly its five most recent unique IPs.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist.

Post-deploy validation

Seven days after this is in production, this query (on Database Lab or a read replica) should return ~0 rows: proof that no token exceeds the cap or holds duplicate IPs on the write path any more.

SELECT personal_access_token_id
FROM personal_access_token_last_used_ips
GROUP BY personal_access_token_id
HAVING (count(*) > 5 OR count(*) > count(DISTINCT ip_address))
  AND max(created_at) > now() - interval '7 days';

The count(*) > count(DISTINCT ip_address) clause also flags any token still holding duplicate rows for the same IP, which the DISTINCT ON trim removes. The total over-cap and duplicated backlog is cleaned by the follow-up batched background migration (!250697 (merged)), not this MR.