Add GitHub handles to backend database (associated with profiles)

[joelamouche]

Add GitHub handle to profiles via existing profile CRUD

• Scope

  • Store a single GitHub handle per profile in the existing profiles table.
  • Enforce global, case-insensitive uniqueness.
  • First-come-first-serve: once claimed, cannot be claimed by another profile unless changed by admin tooling later.
  • No ownership verification for now.

• Data Model

  • Add column github_login TEXT NULL.
  • Enforce case-insensitive uniqueness via functional unique index; keep stored value as entered (original casing).

• Migration

  • Add column and index (suggestion, don't think we need index):

    ALTER TABLE profiles ADD COLUMN github_login TEXT;
    CREATE UNIQUE INDEX IF NOT EXISTS unique_github_login_lower ON profiles (LOWER(github_login));

  • Backfill: none.

• API

  • Reuse existing profile endpoints.
  • PUT /profile (existing): accept optional github_login field in the payload.
    • Normalize input for checks: trim and to_lowercase for uniqueness evaluation only.
    • Persist original input (with original casing).
    • On conflict with another profile’s handle, return 409.
    • On invalid format, return 400.
  • GET profile endpoints (existing): include github_login in returned profile DTOs (subject to current visibility rules).

• Validation

  • github_login must match ^[a-zA-Z0-9-]{1,39}$.
  • Treat input case-insensitively for uniqueness. Empty or whitespace-only is invalid.

• Behavior

  • Users can set or update their own github_login via existing PUT /profile.
  • If the normalized handle is already taken by another profile, return 409.
  • Store as entered; rely on the unique index LOWER(github_login) for uniqueness.
  • Update updated_at as per existing mutation behavior.

• Errors

  • 400: invalid handle format.
  • 409: handle already taken.
  • 401/403: unauthenticated/unauthorized as per existing rules.

• Logging & Metrics

  • Log claims/updates with profile_id and lower(github_login).
  • Metric counter: github_handle_claim_conflicts.

• Security

  • Only the authenticated user can set their own github_login via /profile.
  • Admin override flows are out of scope for now.

• Acceptance Criteria

  • DB schema updated with github_login and a unique index on LOWER(github_login) (?).
  • PUT /profile accepts github_login and enforces case-insensitive uniqueness.
  • Conflicting claims return 409 with a clear message.
  • Stored value preserves user casing; uniqueness evaluated case-insensitively.
  • GET profile responses include github_login per current visibility rules.
  • Unit tests: validation, uniqueness conflict, successful update.
  • Integration test: two profiles racing for same handle → one 200, one 409.

[rainwaters11]

I’ll implement Option A (simple) with a case-insensitive unique index on LOWER(profiles.github_login) so handles are globally unique regardless of casing. I’ll add PUT /me/github-handle and GET /me/github-handle plus GET /profiles/:id/github-handle using Axum + SQLx. Input is trimmed + lowercased for uniqueness checks; the original casing is stored as entered. Conflicts surface as HTTP 409; invalid input as 400. I’ll add unit tests for validation/normalization and an integration test where two users race for the same handle — one succeeds, one gets 409. Finally, I’ll update API docs. This follows the spec and uses Postgres as the source of truth via a functional UNIQUE index on LOWER(...)

[oscarwroche]

@joelamouche

• I don't see why add another CRUD endpoint for github profiles since we're only editing the profile. This should be done via the existing /profile PUT route.
• The profile table already has an updated_at column and I don't see why add two columns including the normalized handle

@rainwaters11 any more thoughts on the spec ?

[joelamouche]

@oscarwroche agreed, please update the issue description

[joelamouche]

@rainwaters11 still interested?

[rainwaters11]

Yes. With the new info that you've stated, maybe instead, I could try to fold into /profile to avoid new CRUD and keep parity with the rest of the API a better resolution?

[joelamouche]

Great @rainwaters11 ! I updated the specs

[rainwaters11]

Sending PR shortly - Thanks!